reporting/rep708.php

   1 <?php
   2
   3 $page_security = 2;
   4 // ----------------------------------------------------------------
   5 // $ Revision:  2.0 $
   6 // Creator:     Joe Hunt
   7 // date_:       2005-05-19
   8 // Title:       Trial Balance
   9 // ----------------------------------------------------------------
  10 $path_to_root="../";
  11
  12 include_once($path_to_root . "includes/session.inc");
  13 include_once($path_to_root . "includes/date_functions.inc");
  14 include_once($path_to_root . "includes/data_checks.inc");
  15 include_once($path_to_root . "gl/includes/gl_db.inc");
  16
  17 //----------------------------------------------------------------------------------------------------
  18
  19 // trial_inquiry_controls();
  20 print_trial_balance();
  21
  22 //----------------------------------------------------------------------------------------------------
  23 function get_balance($account, $dimension, $dimension2, $from, $to, $from_incl=true, $to_incl=true)
  24 {
  25         $sql = "SELECT SUM(IF(amount >= 0, amount, 0)) as debit, SUM(IF(amount < 0, -amount, 0)) as credit, SUM(amount) as balance
  26                 FROM ".TB_PREF."gl_trans,".TB_PREF."chart_master,".TB_PREF."chart_types, ".TB_PREF."chart_class
  27                 WHERE ".TB_PREF."gl_trans.account=".TB_PREF."chart_master.account_code AND ".TB_PREF."chart_master.account_type=".TB_PREF."chart_types.id
  28                 AND ".TB_PREF."chart_types.class_id=".TB_PREF."chart_class.cid AND";
  29
  30         if ($account != null)
  31                 $sql .= " account='$account' AND";
  32         if ($dimension > 0)
  33                 $sql .= " dimension_id=$dimension AND";
  34         if ($dimension2 > 0)
  35                 $sql .= " dimension2_id=$dimension2 AND";
  36         $from_date = date2sql($from);
  37         if ($from_incl)
  38                 $sql .= " tran_date >= '$from_date'  AND";
  39         else
  40                 $sql .= " tran_date > IF(".TB_PREF."chart_class.balance_sheet=1, '0000-00-00', '$from_date') AND";
  41         $to_date = date2sql($to);
  42         if ($to_incl)
  43                 $sql .= " tran_date <= '$to_date' ";
  44         else
  45                 $sql .= " tran_date < '$to_date' ";
  46
  47         $result = db_query($sql,"No general ledger accounts were returned");
  48
  49         return db_fetch($result);
  50 }
  51
  52 //----------------------------------------------------------------------------------------------------
  53
  54 function print_trial_balance()
  55 {
  56         global $path_to_root;
  57
  58         include_once($path_to_root . "reporting/includes/pdf_report.inc");
  59         $dim = get_company_pref('use_dimension');
  60         $dimension = $dimension2 = 0;
  61
  62         $from = $_POST['PARAM_0'];
  63         $to = $_POST['PARAM_1'];
  64         $zero = $_POST['PARAM_2'];
  65         $balances = $_POST['PARAM_3'];
  66         if ($dim == 2)
  67         {
  68                 $dimension = $_POST['PARAM_4'];
  69                 $dimension2 = $_POST['PARAM_5'];
  70                 $comments = $_POST['PARAM_6'];
  71         }
  72         else if ($dim == 1)
  73         {
  74                 $dimension = $_POST['PARAM_4'];
  75                 $comments = $_POST['PARAM_5'];
  76         }
  77         else
  78         {
  79                 $comments = $_POST['PARAM_4'];
  80         }
  81         $dec = user_price_dec();
  82
  83         //$cols2 = array(0, 50, 230, 330, 430, 530);
  84         $cols2 = array(0, 50, 190, 310, 430, 530);
  85         //-------------0--1---2----3----4----5--
  86
  87         $headers2 = array('', '', _('Brought Forward'), _('This Period'), _('Balance'));
  88
  89         $aligns2 = array('left', 'left', 'left', 'left', 'left');
  90
  91         //$cols = array(0, 50, 200, 250, 300,   350, 400, 450, 500,     550);
  92         $cols = array(0, 50, 150, 210, 270,     330, 390, 450, 510,     570);
  93         //------------0--1---2----3----4----5----6----7----8----9--
  94
  95         $headers = array(_('Account'), _('Account Name'), _('Debit'), _('Credit'), _('Debit'),
  96                 _('Credit'), _('Debit'), _('Credit'));
  97
  98         $aligns = array('left', 'left', 'right', 'right', 'right', 'right',     'right', 'right');
  99
 100     if ($dim == 2)
 101     {
 102         $params =   array(      0 => $comments,
 103                                     1 => array('text' => _('Period'),'from' => $from, 'to' => $to),
 104                         2 => array('text' => _('Dimension')." 1",
 105                             'from' => get_dimension_string($dimension), 'to' => ''),
 106                         3 => array('text' => _('Dimension')." 2",
 107                             'from' => get_dimension_string($dimension2), 'to' => ''));
 108     }
 109     else if ($dim == 1)
 110     {
 111         $params =   array(      0 => $comments,
 112                                     1 => array('text' => _('Period'),'from' => $from, 'to' => $to),
 113                         2 => array('text' => _('Dimension'),
 114                             'from' => get_dimension_string($dimension), 'to' => ''));
 115     }
 116     else
 117     {
 118         $params =   array(      0 => $comments,
 119                                     1 => array('text' => _('Period'),'from' => $from, 'to' => $to));
 120     }
 121
 122         $rep = new FrontReport(_('Trial Balance'), "TrialBalance.pdf", user_pagesize());
 123
 124         $rep->Font();
 125         $rep->Info($params, $cols, $headers, $aligns, $cols2, $headers2, $aligns2);
 126         $rep->Header();
 127
 128         $accounts = get_gl_accounts();
 129
 130         $pdeb = $pcre = $cdeb = $ccre = $tdeb = $tcre = $pbal = $cbal = $tbal = 0;
 131         $begin = begin_fiscalyear();
 132         if (date1_greater_date2($begin, $from))
 133                 $begin = $from;
 134         $begin = add_days($begin, -1);
 135         while ($account=db_fetch($accounts))
 136         {
 137                 $prev = get_balance($account["account_code"], $dimension, $dimension2, $begin, $from, false, false);
 138                 $curr = get_balance($account["account_code"], $dimension, $dimension2, $from, $to, true, true);
 139                 $tot = get_balance($account["account_code"], $dimension, $dimension2, $begin, $to, false, true);
 140
 141                 if ($zero == 0 && !$prev['balance'] && !$curr['balance'] && !$tot['balance'])
 142                         continue;
 143                 $rep->TextCol(0, 1, $account['account_code']);
 144                 $rep->TextCol(1, 2,     $account['account_name']);
 145                 if ($balances != 0)
 146                 {
 147                         if ($prev['balance'] >= 0.0)
 148                                 $rep->TextCol(2, 3,     number_format2($prev['balance'], $dec));
 149                         else
 150                                 $rep->TextCol(3, 4,     number_format2(abs($prev['balance']), $dec));
 151                         if ($curr['balance'] >= 0.0)
 152                                 $rep->TextCol(4, 5,     number_format2($curr['balance'], $dec));
 153                         else
 154                                 $rep->TextCol(5, 6,     number_format2(abs($curr['balance']), $dec));
 155                         if ($tot['balance'] >= 0.0)
 156                                 $rep->TextCol(6, 7,     number_format2($tot['balance'], $dec));
 157                         else
 158                                 $rep->TextCol(7, 8,     number_format2(abs($tot['balance']), $dec));
 159                 }
 160                 else
 161                 {
 162                         $rep->TextCol(2, 3,     number_format2($prev['debit'], $dec));
 163                         $rep->TextCol(3, 4,     number_format2($prev['credit'], $dec));
 164                         $rep->TextCol(4, 5,     number_format2($curr['debit'], $dec));
 165                         $rep->TextCol(5, 6,     number_format2($curr['credit'], $dec));
 166                         $rep->TextCol(6, 7,     number_format2($tot['debit'], $dec));
 167                         $rep->TextCol(7, 8,     number_format2($tot['credit'], $dec));
 168                         $pdeb += $prev['debit'];
 169                         $pcre += $prev['credit'];
 170                         $cdeb += $curr['debit'];
 171                         $ccre += $curr['credit'];
 172                         $tdeb += $tot['debit'];
 173                         $tcre += $tot['credit'];
 174
 175                 }
 176                 $pbal += $prev['balance'];
 177                 $cbal += $curr['balance'];
 178                 $tbal += $tot['balance'];
 179                 $rep->NewLine();
 180
 181                 if ($rep->row < $rep->bottomMargin + $rep->lineHeight)
 182                 {
 183                         $rep->Line($rep->row - 2);
 184                         $rep->Header();
 185                 }
 186         }
 187         $rep->Line($rep->row);
 188         $rep->NewLine();
 189         $rep->Font('bold');
 190
 191         //$prev = get_balance(null, $dimension, $dimension2, $begin, $from, false, false);
 192         //$curr = get_balance(null, $dimension, $dimension2, $from, $to, true, true);
 193         //$tot = get_balance(null, $dimension, $dimension2, $begin, $to, false, true);
 194
 195         if ($balances == 0)
 196         {
 197                 $rep->TextCol(0, 2, _("Total"));
 198                 $rep->TextCol(2, 3,     number_format2($pdeb, $dec));
 199                 $rep->TextCol(3, 4,     number_format2($pcre, $dec));
 200                 $rep->TextCol(4, 5,     number_format2($cdeb, $dec));
 201                 $rep->TextCol(5, 6,     number_format2($ccre, $dec));
 202                 $rep->TextCol(6, 7,     number_format2($tdeb, $dec));
 203                 $rep->TextCol(7, 8,     number_format2($tcre, $dec));
 204                 $rep->NewLine();
 205         }
 206         $rep->TextCol(0, 2, _("Ending Balance"));
 207
 208         if ($pbal >= 0.0)
 209                 $rep->TextCol(2, 3,     number_format2($pbal, $dec));
 210         else
 211                 $rep->TextCol(3, 4,     number_format2(abs($pbal), $dec));
 212         if ($cbal >= 0.0)
 213                 $rep->TextCol(4, 5,     number_format2($cbal, $dec));
 214         else
 215                 $rep->TextCol(5, 6,     number_format2(abs($cbal), $dec));
 216         if ($tbal >= 0.0)
 217                 $rep->TextCol(6, 7,     number_format2($tbal, $dec));
 218         else
 219                 $rep->TextCol(7, 8,     number_format2(abs($tbal), $dec));
 220
 221         $rep->Line($rep->row - 6);
 222
 223         $rep->End();
 224 }
 225
 226 ?>