/** * 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++; } }