/**
* ledgerstatement method
*
* @return void
*/
public function ledgerstatement()
{
$this->set('title_for_layout', __d('webzash', 'Ledger Statement'));
/* Create list of ledgers to pass to view */
$ledgers = new LedgerTree();
$ledgers->Group =& $this->Group;
$ledgers->Ledger =& $this->Ledger;
$ledgers->current_id = -1;
$ledgers->restriction_bankcash = 1;
$ledgers->build(0);
$ledgers->toList($ledgers, -1);
$ledgers_disabled = array();
foreach ($ledgers->ledgerList as $row => $data) {
if ($row < 0) {
$ledgers_disabled[] = $row;
}
}
$this->set('ledgers', $ledgers->ledgerList);
$this->set('ledgers_disabled', $ledgers_disabled);
if ($this->request->is('post')) {
/* If valid data then redirect with POST values are URL parameters so that pagination works */
if (empty($this->request->data['Report']['ledger_id'])) {
$this->Session->setFlash(__d('webzash', 'Invalid ledger.'), 'danger');
return $this->redirect(array('plugin' => 'webzash', 'controller' => 'reports', 'action' => 'ledgerstatement'));
}
if (!empty($this->request->data['Report']['startdate']) || !empty($this->request->data['Report']['enddate'])) {
return $this->redirect(array('plugin' => 'webzash', 'controller' => 'reports', 'action' => 'ledgerstatement', 'ledgerid' => $this->request->data['Report']['ledger_id'], 'options' => 1, 'startdate' => $this->request->data['Report']['startdate'], 'enddate' => $this->request->data['Report']['enddate']));
} else {
return $this->redirect(array('plugin' => 'webzash', 'controller' => 'reports', 'action' => 'ledgerstatement', 'ledgerid' => $this->request->data['Report']['ledger_id']));
}
}
$this->set('showEntries', false);
$this->set('options', false);
/* Check if ledger id is set in parameters, if not return and end view here */
if (empty($this->passedArgs['ledgerid'])) {
return;
}
$ledgerId = $this->passedArgs['ledgerid'];
/* Check if ledger exists */
$ledger = $this->Ledger->findById($ledgerId);
if (!$ledger) {
$this->Session->setFlash(__d('webzash', 'Ledger not found.'), 'danger');
return $this->redirect(array('plugin' => 'webzash', 'controller' => 'reports', 'action' => 'ledgerstatement'));
}
$this->set('ledger', $ledger);
$this->request->data['Report']['ledger_id'] = $ledgerId;
/* Set the approprite search conditions */
$conditions = array();
$conditions['Entryitem.ledger_id'] = $ledgerId;
/* Set the approprite search conditions if custom date is selected */
$startdate = null;
$enddate = null;
if (empty($this->passedArgs['options'])) {
$this->set('options', false);
/* Sub-title*/
$this->set('subtitle', __d('webzash', 'Ledger statement for %s from %s to %s', h(toCodeWithName($ledger['Ledger']['code'], $ledger['Ledger']['name'])), dateFromSql(Configure::read('Account.startdate')), dateFromSql(Configure::read('Account.enddate'))));
} else {
$this->set('options', true);
if (!empty($this->passedArgs['startdate'])) {
/* TODO : Validate date */
$startdate = dateToSql($this->passedArgs['startdate']);
$this->request->data['Report']['startdate'] = $this->passedArgs['startdate'];
$conditions['Entry.date >='] = $startdate;
}
if (!empty($this->passedArgs['enddate'])) {
/* TODO : Validate date */
$enddate = dateToSql($this->passedArgs['enddate']);
$this->request->data['Report']['enddate'] = $this->passedArgs['enddate'];
$conditions['Entry.date <='] = $enddate;
}
/* Sub-title*/
if (!empty($this->passedArgs['startdate']) && !empty($this->passedArgs['enddate'])) {
$this->set('subtitle', __d('webzash', 'Ledger statement for %s from %s to %s', h($ledger['Ledger']['name']), dateFromSql(dateToSQL($this->passedArgs['startdate'])), dateFromSql(dateToSQL($this->passedArgs['enddate']))));
} else {
if (!empty($this->passedArgs['startdate'])) {
$this->set('subtitle', __d('webzash', 'Ledger statement for %s from %s to %s', h($ledger['Ledger']['name']), dateFromSql(dateToSQL($this->passedArgs['startdate'])), dateFromSql(Configure::read('Account.enddate'))));
} else {
if (!empty($this->passedArgs['enddate'])) {
$this->set('subtitle', __d('webzash', 'Ledger statement for %s from %s to %s', h($ledger['Ledger']['name']), dateFromSql(Configure::read('Account.startdate')), dateFromSql(dateToSQL($this->passedArgs['enddate']))));
}
}
}
}
/* Opening and closing titles */
if (is_null($startdate)) {
$this->set('opening_title', __d('webzash', 'Opening balance as on %s', dateFromSql(Configure::read('Account.startdate'))));
} else {
$this->set('opening_title', __d('webzash', 'Opening balance as on %s', dateFromSql($startdate)));
}
if (is_null($enddate)) {
$this->set('closing_title', __d('webzash', 'Closing balance as on %s', dateFromSql(Configure::read('Account.enddate'))));
} else {
$this->set('closing_title', __d('webzash', 'Closing balance as on %s', dateFromSql($enddate)));
}
/* Calculating opening balance */
$op = $this->Ledger->openingBalance($ledgerId, $startdate);
$this->set('op', $op);
/* Calculating closing balance */
$cl = $this->Ledger->closingBalance($ledgerId, null, $enddate);
$this->set('cl', $cl);
/* Calculate current page opening balance */
if (!isset($this->passedArgs['page']) || $this->passedArgs['page'] <= 1) {
/* If 1st page then current page opening balance is opening balance */
$current_op = $op;
} else {
/* Setup limit that selects all previous entryitems */
$cur_limit = ($this->passedArgs['page'] - 1) * $this->Session->read('Wzsetting.row_count');
/* Find all previous entryitems */
$prev_entries = $this->Entry->find('all', array('fields' => array('Entry.id', 'Entry.tag_id', 'Entry.entrytype_id', 'Entry.number', 'Entry.date', 'Entry.dr_total', 'Entry.cr_total', 'Entry.narration', 'Entryitem.id', 'Entryitem.entry_id', 'Entryitem.ledger_id', 'Entryitem.amount', 'Entryitem.dc', 'Entryitem.reconciliation_date'), 'limit' => $cur_limit, 'order' => array('Entry.date' => 'asc'), 'conditions' => $conditions, 'joins' => array(array('table' => 'entryitems', 'alias' => 'Entryitem', 'conditions' => array('Entry.id = Entryitem.entry_id')))));
/* Initially set as opening balance */
$temp['amount'] = $op['amount'];
$temp['dc'] = $op['dc'];
/* Loop through each previous entryitem and add the amount */
foreach ($prev_entries as $prev_entry) {
$temp = calculate_withdc($temp['amount'], $temp['dc'], $prev_entry['Entryitem']['amount'], $prev_entry['Entryitem']['dc']);
}
$current_op['amount'] = $temp['amount'];
$current_op['dc'] = $temp['dc'];
}
/* Set the current page opening balance */
$this->set('current_op', $current_op);
/* Setup pagination */
if (isset($this->passedArgs['downloadcsv']) || isset($this->passedArgs['downloadxls']) || isset($this->passedArgs['print'])) {
$this->CustomPaginator->settings = array('Entry' => array('fields' => array('Entry.id', 'Entry.tag_id', 'Entry.entrytype_id', 'Entry.number', 'Entry.date', 'Entry.dr_total', 'Entry.cr_total', 'Entry.narration', 'Entryitem.id', 'Entryitem.entry_id', 'Entryitem.ledger_id', 'Entryitem.amount', 'Entryitem.dc', 'Entryitem.reconciliation_date'), 'limit' => 100000000000, 'order' => array('Entry.date' => 'asc'), 'conditions' => $conditions, 'joins' => array(array('table' => 'entryitems', 'alias' => 'Entryitem', 'conditions' => array('Entry.id = Entryitem.entry_id')))));
} else {
$this->CustomPaginator->settings = array('Entry' => array('fields' => array('Entry.id', 'Entry.tag_id', 'Entry.entrytype_id', 'Entry.number', 'Entry.date', 'Entry.dr_total', 'Entry.cr_total', 'Entry.narration', 'Entryitem.id', 'Entryitem.entry_id', 'Entryitem.ledger_id', 'Entryitem.amount', 'Entryitem.dc', 'Entryitem.reconciliation_date'), 'limit' => $this->Session->read('Wzsetting.row_count'), 'order' => array('Entry.date' => 'asc'), 'conditions' => $conditions, 'joins' => array(array('table' => 'entryitems', 'alias' => 'Entryitem', 'conditions' => array('Entry.id = Entryitem.entry_id')))));
}
/* Pass varaibles to view which are used in Helpers */
$this->set('allTags', $this->Tag->fetchAll());
$this->set('entries', $this->CustomPaginator->paginate('Entry'));
$this->set('showEntries', true);
/* Download report */
if (isset($this->passedArgs['downloadcsv'])) {
Configure::write('Account.currency_format', 'none');
$this->layout = false;
$view = new View($this, false);
$response = $view->render('Reports/downloadcsv/ledgerstatement');
$this->response->body($response);
$this->response->type('text/csv');
$this->response->download('ledgerstatement.csv');
return $this->response;
}
/* Download report */
if (isset($this->passedArgs['downloadxls'])) {
Configure::write('Account.currency_format', 'none');
$this->layout = 'xls';
$view = new View($this, false);
$response = $view->render('Reports/downloadxls/ledgerstatement');
$this->response->body($response);
$this->response->type('application/vnd.ms-excel');
$this->response->download('ledgerstatement.xls');
return $this->response;
}
/* Print report */
if (isset($this->passedArgs['print'])) {
$this->layout = 'print';
$view = new View($this, false);
$response = $view->render('Reports/print/ledgerstatement');
$this->response->body($response);
return $this->response;
}
return;
}
/**
* Find and add subledgers as array items
*/
function add_sub_ledgers()
{
$child_ledger_q = $this->Ledger->find('all', array('conditions' => array('Ledger.group_id' => $this->id), 'order' => array('Ledger.name')));
$counter = 0;
foreach ($child_ledger_q as $row) {
$this->children_ledgers[$counter]['id'] = $row['Ledger']['id'];
$this->children_ledgers[$counter]['name'] = $row['Ledger']['name'];
$this->children_ledgers[$counter]['code'] = $row['Ledger']['code'];
$this->children_ledgers[$counter]['l_group_id'] = $row['Ledger']['group_id'];
$this->children_ledgers[$counter]['l_type'] = $row['Ledger']['type'];
$this->children_ledgers[$counter]['l_reconciliation'] = $row['Ledger']['reconciliation'];
$this->children_ledgers[$counter]['l_notes'] = $row['Ledger']['notes'];
/* If start date is specified dont use the opening balance since its not applicable */
if (is_null($this->start_date)) {
$this->children_ledgers[$counter]['op_total'] = $row['Ledger']['op_balance'];
$this->children_ledgers[$counter]['op_total_dc'] = $row['Ledger']['op_balance_dc'];
} else {
$this->children_ledgers[$counter]['op_total'] = 0.0;
$this->children_ledgers[$counter]['op_total_dc'] = $row['Ledger']['op_balance_dc'];
}
/* Calculating current group opening balance total */
$temp3 = calculate_withdc($this->op_total, $this->op_total_dc, $this->children_ledgers[$counter]['op_total'], $this->children_ledgers[$counter]['op_total_dc']);
$this->op_total = $temp3['amount'];
$this->op_total_dc = $temp3['dc'];
if ($this->only_opening == true) {
/* If calculating only opening balance */
$this->children_ledgers[$counter]['dr_total'] = 0;
$this->children_ledgers[$counter]['cr_total'] = 0;
$this->children_ledgers[$counter]['cl_total'] = $this->children_ledgers[$counter]['op_total'];
$this->children_ledgers[$counter]['cl_total_dc'] = $this->children_ledgers[$counter]['op_total_dc'];
} else {
$cl = $this->Ledger->closingBalance($row['Ledger']['id'], $this->start_date, $this->end_date);
$this->children_ledgers[$counter]['dr_total'] = $cl['dr_total'];
$this->children_ledgers[$counter]['cr_total'] = $cl['cr_total'];
$this->children_ledgers[$counter]['cl_total'] = $cl['amount'];
$this->children_ledgers[$counter]['cl_total_dc'] = $cl['dc'];
}
/* Calculating current group closing balance total */
$temp4 = calculate_withdc($this->cl_total, $this->cl_total_dc, $this->children_ledgers[$counter]['cl_total'], $this->children_ledgers[$counter]['cl_total_dc']);
$this->cl_total = $temp4['amount'];
$this->cl_total_dc = $temp4['dc'];
/* Calculate Dr and Cr total */
$this->dr_total = calculate($this->dr_total, $this->children_ledgers[$counter]['dr_total'], '+');
$this->cr_total = calculate($this->cr_total, $this->children_ledgers[$counter]['cr_total'], '+');
$counter++;
}
}
