function ShowTransactions() { $OB_Debit = 0; $OB_Credit = 0; $Grand_Total = 0; $Grand_Total_Debit = 0; $Grand_Total_Credit = 0; $Grand_TR_Total_Debit = 0; $Grand_TR_Total_Credit = 0; $Grand_Total_Closing = 0; $nResult = DB::table('coa')->where('parent_id', '=', 0)->orderBy('coa_code', 'asc')->get(); foreach ($nResult as $rstRow) { // Show Main Head echo "\t<tr>"; echo "\t\t<td >" . AccountName($rstRow->coa_code, $rstRow->coa_account) . "</td>"; echo "\t\t<td></td>"; echo "\t\t<td></td>"; echo "\t\t<td></td>"; echo "\t</tr>"; $nResultDetail = DB::table('coa')->where('parent_id', '=', $rstRow->coa_id)->orderBy('coa_code', 'asc')->get(); $Total_OB_Debit = 0; $Total_OB_Credit = 0; $Total_TR_Debit = 0; $Total_TR_Credit = 0; $Total_Closing = 0; $OpBalance = 0; $sub_Total_Debit = 0; $sub_Total_Credit = 0; $sub_Total_Closing = 0; foreach ($nResultDetail as $key => $value) { $OB_Balance = get_opening_balance($value->coa_code); $is_Debit = ""; if (!empty($OB_Balance)) { if ($OB_Balance[0]->coa_debit != 0) { $is_Debit = "Dr"; } elseif ($OB_Balance[0]->coa_credit != 0) { $is_Debit = "Cr"; } } $OB_Debit = $OB_Balance[0]->coa_debit; $OB_Credit = $OB_Balance[0]->coa_credit; $OpBalance = $OB_Debit + $OB_Credit; $start_date = "2016-04-01"; $end_date = date("Y-m-d"); // Get all debit credit sum with opening balance $allData = search_vouchers($value->coa_code, $start_date, $end_date, $OpBalance); $Total_TR_Debit = $allData['Debit']; $Total_TR_Credit = $allData['Credit']; //$Total_Closing = ($OpBalance + $Total_TR_Debit) - $Total_TR_Credit; if ($is_Debit == "Dr") { $Total_Closing = $OpBalance + $Total_TR_Debit - $Total_TR_Credit; } else { $Total_Closing = $OpBalance - $Total_TR_Debit + $Total_TR_Credit; } $sub_Total_Debit += $Total_TR_Debit; $sub_Total_Credit += $Total_TR_Credit; echo "\t<tr>"; echo "\t\t<td >" . AccountName($value->coa_code, $value->coa_account) . "</td>"; echo "\t\t<td><span style='text-align:left; float:left; padding-left:20px'>" . number_format($OB_Debit) . "</span><span style='text-align:right; float:right;'>" . number_format($OB_Credit) . "</span></td>"; echo "\t\t<td><span style='text-align:left; float:left; padding-left:20px'>" . number_format($Total_TR_Debit) . "</span><span style='text-align:right; float:right;'>" . number_format($Total_TR_Credit) . "</span></td>"; echo "\t\t<td><span style='text-align:left; float:left; padding-left:20px'>0</span><span style='text-align:right; float:right;'>" . number_format($Total_Closing) . "</span></td>"; echo "\t</tr>"; $Total_OB_Debit += $OB_Debit; $Total_OB_Credit += $OB_Credit; $sub_Total_Closing += $Total_Closing; } // Sub total head echo "\t<tr>"; echo "\t\t<td align='right' style='font-weight:bold;'>Sub Head Total:</td>"; echo "\t\t<td><span style='text-align:left; float:left; padding-left:20px; font-weight:bold'>" . number_format($Total_OB_Debit) . "</span><span style='text-align:right; float:right; font-weight:bold'>" . number_format($Total_OB_Credit) . "</span></td>"; echo "\t\t<td><span style='text-align:left; float:left; padding-left:20px; font-weight:bold'>" . number_format($sub_Total_Debit) . "</span><span style='text-align:right; float:right; font-weight:bold'>" . number_format($sub_Total_Credit) . "</span></td>"; echo "\t\t<td><span style='text-align:left; float:left; padding-left:20px; font-weight:bold'>0</span><span style='text-align:right; float:right; font-weight:bold'>" . number_format($sub_Total_Closing) . "</span></td>"; echo "\t</tr>"; $Grand_Total_Debit += $Total_OB_Debit; $Grand_Total_Credit += $Total_OB_Credit; $Grand_TR_Total_Debit += $sub_Total_Debit; $Grand_TR_Total_Credit += $sub_Total_Credit; $Grand_Total_Closing += $sub_Total_Closing; } // Grand total head echo "\t<tr>"; echo "\t\t<td align='right' style='font-weight:bold;'>Grand Total:</td>"; echo "\t\t<td><span style='text-align:left; float:left; padding-left:20px; font-weight:bold'>" . number_format($Grand_Total_Debit) . "</span><span style='text-align:right; float:right; font-weight:bold'>" . number_format($Grand_Total_Credit) . "</span></td>"; echo "\t\t<td><span style='text-align:left; float:left; padding-left:20px; font-weight:bold'>" . number_format($Grand_TR_Total_Debit) . "</span><span style='text-align:right; float:right; font-weight:bold'>" . number_format($Grand_TR_Total_Credit) . "</span></td>"; echo "\t\t<td><span style='text-align:left; float:left; padding-left:20px; font-weight:bold'>0</span><span style='text-align:right; float:right; font-weight:bold'>" . number_format($Grand_Total_Closing) . "</span></td>"; echo "\t</tr>"; }
# lists all transactions on a specific Account require "accrp.php"; session_start(); require "security/secure.php"; pt_register('POST', 'ac_name', 'startdate', 'enddate'); beginDocument("list Ledger Sheet", $sess_user); if ($ac_name) { print_ledger_sheet_head($startdate, $enddate, $ac_name); $startdate_srd = conv_to_srd($startdate); $enddate_srd = conv_to_srd($enddate); $db = getDBConnection(); $result = mysql_query("SELECT VR_NO, VR_DT, VR_TP, REMARKS, AMOUNT, DR_CR FROM TRANS WHERE AC_ID5='{$ac_name}' AND VR_DT >= '{$startdate_srd}' AND VR_DT <= '{$enddate_srd}'", $db); checkMySQLError(); beginPrettyTable("4", "From {$startdate} to {$enddate}<nbs> Code{$ac_name}"); printRow(array("opening Balance: " . get_opening_balance("AC_ID5={$ac_name}", $startdate_srd)), "", "1,3"); printRow(array("<hr>"), "", "1,6"); printRow(array("VR No", "VR Date", "Vr Type", "Particulars", "Debit", "Credit")); while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { $row["VR_DT"] = conv_to_hrd($row["VR_DT"]); if ($row["DR_CR"] == "D") { $row["DR_CR"] = $row["AMOUNT"]; $row["AMOUNT"] = " "; } else { $row["DR_CR"] = " "; } printRow($row, "fluct"); } printRow(array("", "", "DebitSum:", get_debit_sum("AC_ID5={$ac_name}", $enddate_srd, $startdate_srd)), "", "1,2"); printRow(array("", "", "CreditSum:", get_credit_sum("AC_ID5={$ac_name}", $enddate_srd, $startdate_srd)), "", "1,3"); printRow(array("<hr>"), "", "1,6");
function getNext() { if (!($this->row = mysql_fetch_array($this->result, MYSQL_ASSOC))) { $this->finish_flag = 1; return NULL; } $ac5 = $this->row["AC_ID5"]; if ($this->open_or_close_bal == "c") { $this->balance = get_closing_balance("AC_ID5={$ac5}", $this->enddate); } else { $this->balance = get_opening_balance("AC_ID5={$ac5}", $this->enddate); } $this->sum += $this->balance; return $this->row; }
// $OPeninBalance = $coa_debit; function GetDateWiseExpense($date) { $arrayDetail = DB::table('vouchermaster')->join('voucherdetail', 'voucherdetail.vd_vm_id', '=', 'vouchermaster.vm_id')->select(DB::raw('SUM(vd_debit) AS TotalExpense'))->whereRaw('vm_date = "' . $date . '" AND vd_coa_code != 0')->orderBy('vm_date', 'asc')->get(); return $arrayDetail[0]->TotalExpense; } $coa_code = !empty($arrayLedeger[0]->coa_code) ? $arrayLedeger[0]->coa_code : ''; $coa_account = !empty($arrayLedeger[0]->coa_account) ? $arrayLedeger[0]->coa_account : ''; // Check OPBalance is Credit OR Debit function get_opening_balance($coa) { $arrayOpBalance = array(); $arrayOpBalance = DB::table('coa')->select('coa_debit', 'coa_credit')->whereRaw('coa_code = "' . $coa . '"')->get(); return $arrayOpBalance; } $CheckDebitCreditOP = get_opening_balance($coa_code); //print_r($CheckDebitCreditOP); die; $is_Debit = ""; if (!empty($CheckDebitCreditOP)) { if ($CheckDebitCreditOP[0]->coa_debit != 0) { $is_Debit = "Dr"; } elseif ($CheckDebitCreditOP[0]->coa_credit != 0) { $is_Debit = "Cr"; } } ?> <td width="7%" valign="top" align="center"><strong>{{ $coa_code }}</strong></td> <td width="20%" valign="top" align="left"><strong>{{ $coa_account }}</strong></td> <td width="11%"><strong>Date From</strong></td> <td width="14%" align="left">{{ $start_date }}</td> <td width="8%" align="left"><strong>Date To</strong></td>