gl/inquiry/gl_trial_balance.php

   1 <?php
   2
   3 $page_security = 8;
   4 $path_to_root="../..";
   5
   6 include_once($path_to_root . "/includes/session.inc");
   7
   8 include_once($path_to_root . "/includes/date_functions.inc");
   9 include_once($path_to_root . "/includes/ui.inc");
  10 include_once($path_to_root . "/includes/data_checks.inc");
  11
  12 include_once($path_to_root . "/gl/includes/gl_db.inc");
  13
  14 $js = "";
  15 if ($use_date_picker)
  16         $js = get_js_date_picker();
  17
  18 page(_("Trial Balance"), false, false, "", $js);
  19
  20 //----------------------------------------------------------------------------------------------------
  21 // Ajax updates
  22 //
  23 if (get_post('Show'))
  24 {
  25         $Ajax->activate('balance_tbl');
  26 }
  27
  28
  29 function gl_inquiry_controls()
  30 {
  31     start_form();
  32
  33     start_table("class='tablestyle_noborder'");
  34
  35     date_cells(_("From:"), 'TransFromDate', '', null, -30);
  36         date_cells(_("To:"), 'TransToDate');
  37         check_cells(_("No zero values"), 'NoZero', null);
  38         check_cells(_("Only balances"), 'Balance', null);
  39
  40         submit_cells('Show',_("Show"),'','', true);
  41     end_table();
  42     end_form();
  43 }
  44
  45 //----------------------------------------------------------------------------------------------------
  46
  47 function get_balance($account, $from, $to, $from_incl=true, $to_incl=true)
  48 {
  49         $sql = "SELECT SUM(IF(amount >= 0, amount, 0)) as debit, SUM(IF(amount < 0, -amount, 0)) as credit, SUM(amount) as balance
  50                 FROM ".TB_PREF."gl_trans,".TB_PREF."chart_master,".TB_PREF."chart_types, ".TB_PREF."chart_class
  51                 WHERE ".TB_PREF."gl_trans.account=".TB_PREF."chart_master.account_code AND ".TB_PREF."chart_master.account_type=".TB_PREF."chart_types.id
  52                 AND ".TB_PREF."chart_types.class_id=".TB_PREF."chart_class.cid AND";
  53
  54         if ($account != null)
  55                 $sql .= " account='$account' AND";
  56         $from_date = date2sql($from);
  57         if ($from_incl)
  58                 $sql .= " tran_date >= '$from_date'  AND";
  59         else
  60                 $sql .= " tran_date > IF(".TB_PREF."chart_class.balance_sheet=1, '0000-00-00', '$from_date') AND";
  61         $to_date = date2sql($to);
  62         if ($to_incl)
  63                 $sql .= " tran_date <= '$to_date' ";
  64         else
  65                 $sql .= " tran_date < '$to_date' ";
  66
  67         $result = db_query($sql,"No general ledger accounts were returned");
  68
  69         return db_fetch($result);
  70 }
  71
  72 //----------------------------------------------------------------------------------------------------
  73
  74 function display_trial_balance()
  75 {
  76         global $table_style, $path_to_root;
  77
  78         div_start('balance_tbl');
  79         start_table($table_style);
  80         $tableheader =  "<tr>
  81         <td rowspan=2 class='tableheader'>" . _("Account") . "</td>
  82         <td rowspan=2 class='tableheader'>" . _("Account Name") . "</td>
  83                 <td colspan=2 class='tableheader'>" . _("Brought Forward") . "</td>
  84                 <td colspan=2 class='tableheader'>" . _("This Period") . "</td>
  85                 <td colspan=2 class='tableheader'>" . _("Balance") . "</td>
  86                 </tr><tr>
  87                 <td class='tableheader'>" . _("Debit") . "</td>
  88         <td class='tableheader'>" . _("Credit") . "</td>
  89                 <td class='tableheader'>" . _("Debit") . "</td>
  90                 <td class='tableheader'>" . _("Credit") . "</td>
  91         <td class='tableheader'>" . _("Debit") . "</td>
  92         <td class='tableheader'>" . _("Credit") . "</td>
  93         </tr>";
  94
  95     echo $tableheader;
  96
  97         $k = 0;
  98
  99         $accounts = get_gl_accounts();
 100         $pdeb = $pcre = $cdeb = $ccre = $tdeb = $tcre = $pbal = $cbal = $tbal = 0;
 101         $begin = begin_fiscalyear();
 102         if (date1_greater_date2($begin, $_POST['TransFromDate']))
 103                 $begin = $_POST['TransFromDate'];
 104         $begin = add_days($begin, -1);
 105
 106         while ($account = db_fetch($accounts))
 107         {
 108                 $prev = get_balance($account["account_code"], $begin, $_POST['TransFromDate'], false, false);
 109                 $curr = get_balance($account["account_code"], $_POST['TransFromDate'], $_POST['TransToDate'], true, true);
 110                 $tot = get_balance($account["account_code"], $begin, $_POST['TransToDate'], false, true);
 111                 if (check_value("NoZero") && !$prev['balance'] && !$curr['balance'] && !$tot['balance'])
 112                         continue;
 113                 alt_table_row_color($k);
 114
 115                 $url = "<a href='$path_to_root/gl/inquiry/gl_account_inquiry.php?" . SID . "TransFromDate=" . $_POST["TransFromDate"] . "&TransToDate=" . $_POST["TransToDate"] . "&account=" . $account["account_code"] . "'>" . $account["account_code"] . "</a>";
 116
 117                 label_cell($url);
 118                 label_cell($account["account_name"]);
 119                 if (check_value('Balance'))
 120                 {
 121                         display_debit_or_credit_cells($prev['balance']);
 122                         display_debit_or_credit_cells($curr['balance']);
 123                         display_debit_or_credit_cells($tot['balance']);
 124
 125                 }
 126                 else
 127                 {
 128                         amount_cell($prev['debit']);
 129                         amount_cell($prev['credit']);
 130                         amount_cell($curr['debit']);
 131                         amount_cell($curr['credit']);
 132                         amount_cell($tot['debit']);
 133                         amount_cell($tot['credit']);
 134                         $pdeb += $prev['debit'];
 135                         $pcre += $prev['credit'];
 136                         $cdeb += $curr['debit'];
 137                         $ccre += $curr['credit'];
 138                         $tdeb += $tot['debit'];
 139                         $tcre += $tot['credit'];
 140                 }
 141                 $pbal += $prev['balance'];
 142                 $cbal += $curr['balance'];
 143                 $tbal += $tot['balance'];
 144                 end_row();
 145         }
 146
 147         //$prev = get_balance(null, $begin, $_POST['TransFromDate'], false, false);
 148         //$curr = get_balance(null, $_POST['TransFromDate'], $_POST['TransToDate'], true, true);
 149         //$tot = get_balance(null, $begin, $_POST['TransToDate'], false, true);
 150         if (!check_value('Balance'))
 151         {
 152                 start_row("class='inquirybg' style='font-weight:bold'");
 153                 label_cell(_("Total") ." - ".$_POST['TransToDate'], "colspan=2");
 154                 amount_cell($pdeb);
 155                 amount_cell($pcre);
 156                 amount_cell($cdeb);
 157                 amount_cell($ccre);
 158                 amount_cell($tdeb);
 159                 amount_cell($tcre);
 160                 end_row();
 161         }
 162         start_row("class='inquirybg' style='font-weight:bold'");
 163         label_cell(_("Ending Balance") ." - ".$_POST['TransToDate'], "colspan=2");
 164         display_debit_or_credit_cells($pbal);
 165         display_debit_or_credit_cells($cbal);
 166         display_debit_or_credit_cells($tbal);
 167         end_row();
 168
 169         end_table(1);
 170         div_end();
 171 }
 172
 173 //----------------------------------------------------------------------------------------------------
 174
 175 gl_inquiry_controls();
 176
 177 display_trial_balance();
 178
 179 //----------------------------------------------------------------------------------------------------
 180
 181 end_page();
 182
 183 ?>
 184