reporting/rep709.php

   1 <?php
   2
   3 $page_security = 2;
   4 // ----------------------------------------------------------------
   5 // $ Revision:  2.0 $
   6 // Creator:     Joe Hunt
   7 // date_:       2005-05-19
   8 // Title:       Tax Report
   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_tax_report();
  21
  22 function getCustTransactions($from, $to)
  23 {
  24         $fromdate = date2sql($from);
  25         $todate = date2sql($to);
  26
  27         $sql = "SELECT ".TB_PREF."debtor_trans.reference,
  28                         ".TB_PREF."debtor_trans.type,
  29                         ".TB_PREF."sys_types.type_name,
  30                         ".TB_PREF."debtor_trans.tran_date,
  31                         ".TB_PREF."debtor_trans.debtor_no,
  32                         ".TB_PREF."debtors_master.name,
  33                         ".TB_PREF."debtors_master.curr_code,
  34                         ".TB_PREF."debtor_trans.branch_code,
  35                         ".TB_PREF."debtor_trans.order_,
  36                         (ov_amount+ov_freight)*rate AS NetAmount,
  37                         ov_freight*rate AS FreightAmount,
  38                         ov_gst*rate AS Tax
  39                 FROM ".TB_PREF."debtor_trans
  40                 INNER JOIN ".TB_PREF."debtors_master ON ".TB_PREF."debtor_trans.debtor_no=".TB_PREF."debtors_master.debtor_no
  41                 INNER JOIN ".TB_PREF."sys_types ON ".TB_PREF."debtor_trans.type=".TB_PREF."sys_types.type_id
  42                 WHERE ".TB_PREF."debtor_trans.tran_date >= '$fromdate'
  43                         AND ".TB_PREF."debtor_trans.tran_date <= '$todate'
  44                         AND (".TB_PREF."debtor_trans.type=10 OR ".TB_PREF."debtor_trans.type=11)
  45                 ORDER BY ".TB_PREF."debtor_trans.tran_date";
  46
  47     return db_query($sql,"No transactions were returned");
  48 }
  49
  50 function getSuppTransactions($from, $to)
  51 {
  52         $fromdate = date2sql($from);
  53         $todate = date2sql($to);
  54
  55         $sql = "SELECT ".TB_PREF."supp_trans.supp_reference,
  56                         ".TB_PREF."supp_trans.type,
  57                         ".TB_PREF."sys_types.type_name,
  58                         ".TB_PREF."supp_trans.tran_date,
  59                         ".TB_PREF."supp_trans.supplier_id,
  60                         ".TB_PREF."supp_trans.rate,
  61                         ".TB_PREF."suppliers.supp_name,
  62                         ".TB_PREF."suppliers.curr_code,
  63                         ".TB_PREF."supp_trans.rate,
  64                         ov_amount*rate AS NetAmount,
  65                         ov_gst*rate AS Tax
  66                 FROM ".TB_PREF."supp_trans
  67                 INNER JOIN ".TB_PREF."suppliers ON ".TB_PREF."supp_trans.supplier_id=".TB_PREF."suppliers.supplier_id
  68                 INNER JOIN ".TB_PREF."sys_types ON ".TB_PREF."supp_trans.type=".TB_PREF."sys_types.type_id
  69                 WHERE ".TB_PREF."supp_trans.tran_date >= '$fromdate'
  70                         AND ".TB_PREF."supp_trans.tran_date <= '$todate'
  71                         AND (".TB_PREF."supp_trans.type=20 OR ".TB_PREF."supp_trans.type=21)
  72                 ORDER BY ".TB_PREF."supp_trans.tran_date";
  73
  74     return db_query($sql,"No transactions were returned");
  75 }
  76
  77 function getTaxTypes()
  78 {
  79         $sql = "SELECT id FROM ".TB_PREF."tax_types ORDER BY id";
  80     return db_query($sql,"No transactions were returned");
  81 }
  82
  83 function getTaxInfo($id)
  84 {
  85         $sql = "SELECT * FROM ".TB_PREF."tax_types WHERE id=$id";
  86     $result = db_query($sql,"No transactions were returned");
  87     return db_fetch($result);
  88 }
  89
  90 function getCustInvTax($taxtype, $from, $to)
  91 {
  92         $fromdate = date2sql($from);
  93         $todate = date2sql($to);
  94
  95         $sql = "SELECT SUM(unit_price * -quantity*".TB_PREF."debtor_trans.rate), SUM(amount*".TB_PREF."debtor_trans.rate)
  96                 FROM ".TB_PREF."debtor_trans_details, ".TB_PREF."debtor_trans_tax_details, ".TB_PREF."debtor_trans
  97                                 WHERE ".TB_PREF."debtor_trans_details.debtor_trans_type>=10
  98                                         AND ".TB_PREF."debtor_trans_details.debtor_trans_type<=11
  99                                         AND ".TB_PREF."debtor_trans_details.debtor_trans_no=".TB_PREF."debtor_trans.trans_no
 100                                         AND ".TB_PREF."debtor_trans_details.debtor_trans_type=".TB_PREF."debtor_trans.type
 101                                         AND ".TB_PREF."debtor_trans_details.debtor_trans_no=".TB_PREF."debtor_trans_tax_details.debtor_trans_no
 102                                         AND ".TB_PREF."debtor_trans_details.debtor_trans_type=".TB_PREF."debtor_trans_tax_details.debtor_trans_type
 103                                         AND ".TB_PREF."debtor_trans_tax_details.tax_type_id=$taxtype
 104                                         AND ".TB_PREF."debtor_trans.tran_date >= '$fromdate'
 105                                         AND ".TB_PREF."debtor_trans.tran_date <= '$todate'";
 106
 107     $result = db_query($sql,"No transactions were returned");
 108     return db_fetch_row($result);
 109 }
 110
 111 function getSuppInvTax($taxtype, $from, $to)
 112 {
 113         $fromdate = date2sql($from);
 114         $todate = date2sql($to);
 115
 116         $sql = "SELECT SUM(unit_price * quantity * ".TB_PREF."supp_trans.rate), SUM(amount*".TB_PREF."supp_trans.rate)
 117                 FROM ".TB_PREF."supp_invoice_items, ".TB_PREF."supp_invoice_tax_items, ".TB_PREF."supp_trans
 118                                 WHERE ".TB_PREF."supp_invoice_items.supp_trans_type>=20
 119                                         AND ".TB_PREF."supp_invoice_items.supp_trans_type<=21
 120                                         AND ".TB_PREF."supp_invoice_items.supp_trans_no=".TB_PREF."supp_invoice_tax_items.supp_trans_no
 121                                         AND ".TB_PREF."supp_invoice_items.supp_trans_type=".TB_PREF."supp_invoice_tax_items.supp_trans_type
 122                                         AND ".TB_PREF."supp_invoice_items.supp_trans_no=".TB_PREF."supp_trans.trans_no
 123                                         AND ".TB_PREF."supp_invoice_items.supp_trans_type=".TB_PREF."supp_trans.type
 124                                         AND ".TB_PREF."supp_invoice_tax_items.tax_type_id=$taxtype
 125                                         AND ".TB_PREF."supp_trans.tran_date >= '$fromdate'
 126                                         AND ".TB_PREF."supp_trans.tran_date <= '$todate'";
 127
 128     $result = db_query($sql,"No transactions were returned");
 129     return db_fetch_row($result);
 130 }
 131
 132 //----------------------------------------------------------------------------------------------------
 133
 134 function print_tax_report()
 135 {
 136         global $path_to_root;
 137
 138         include_once($path_to_root . "reporting/includes/pdf_report.inc");
 139
 140         $rep = new FrontReport(_('Tax Report'), "TaxReport.pdf", user_pagesize());
 141
 142         $from = $_POST['PARAM_0'];
 143         $to = $_POST['PARAM_1'];
 144         $summaryOnly = $_POST['PARAM_2'];
 145         $comments = $_POST['PARAM_3'];
 146         $dec = user_price_dec();
 147
 148         if ($summaryOnly == 1)
 149                 $summary = _('Summary Only');
 150         else
 151                 $summary = _('Detailed Report');
 152
 153
 154         $res = getTaxTypes();
 155
 156         $taxes = array();
 157         $i = 0;
 158         while ($tax=db_fetch($res))
 159                 $taxes[$i++] = $tax['id'];
 160         $idcounter = count($taxes);
 161
 162         $totalinvout = array();
 163         $totaltaxout = array();
 164         $totalinvin = array();
 165         $totaltaxin = array();
 166
 167         if (!$summaryOnly)
 168         {
 169                 $cols = array(0, 80, 130, 190, 290, 370, 435, 500, 565);
 170
 171                 $headers = array(_('Trans Type'), _('#'), _('Date'), _('Name'), _('Branch Name'),
 172                         _('Net'), _('Tax'));
 173
 174                 $aligns = array('left', 'left', 'left', 'left', 'left', 'right', 'right');
 175
 176                 $params =   array(      0 => $comments,
 177                                                         1 => array('text' => _('Period'), 'from' => $from, 'to' => $to),
 178                                                         2 => array('text' => _('Type'), 'from' => $summary, 'to' => ''));
 179
 180                 $rep->Font();
 181                 $rep->Info($params, $cols, $headers, $aligns);
 182                 $rep->Header();
 183         }
 184         $totalnet = 0.0;
 185         $totaltax = 0.0;
 186
 187         $transactions = getCustTransactions($from, $to);
 188
 189         while ($trans=db_fetch($transactions))
 190         {
 191                 if (!$summaryOnly)
 192                 {
 193                         $rep->TextCol(0, 1,     $trans['type_name']);
 194                         $rep->TextCol(1, 2,     $trans['reference']);
 195                         $rep->TextCol(2, 3,     sql2date($trans['tran_date']));
 196                         $rep->TextCol(3, 4,     $trans['name']);
 197                         if ($trans["branch_code"] > 0)
 198                                 $rep->TextCol(4, 5,     get_branch_name($trans["branch_code"]));
 199
 200                         $rep->TextCol(5, 6,     number_format2($trans['NetAmount'], $dec));
 201                         $rep->TextCol(6, 7,     number_format2($trans['Tax'], $dec));
 202
 203                         $rep->NewLine();
 204
 205                         if ($rep->row < $rep->bottomMargin + $rep->lineHeight)
 206                         {
 207                                 $rep->Line($rep->row - 2);
 208                                 $rep->Header();
 209                         }
 210                 }
 211                 $totalnet += $trans['NetAmount'];
 212                 $totaltax += $trans['Tax'];
 213
 214         }
 215         if (!$summaryOnly)
 216         {
 217                 $rep->NewLine();
 218
 219                 if ($rep->row < $rep->bottomMargin + $rep->lineHeight)
 220                 {
 221                         $rep->Line($rep->row - 2);
 222                         $rep->Header();
 223                 }
 224                 $rep->Line($rep->row + $rep->lineHeight);
 225                 $rep->TextCol(3, 5,     _('Total Outputs'));
 226                 $rep->TextCol(5, 6,     number_format2($totalnet, $dec));
 227                 $rep->TextCol(6, 7,     number_format2($totaltax, $dec));
 228                 $rep->Line($rep->row - 5);
 229                 $rep->Header();
 230         }
 231         $totalinnet = 0.0;
 232         $totalintax = 0.0;
 233
 234         $transactions = getSuppTransactions($from, $to);
 235
 236         while ($trans=db_fetch($transactions))
 237         {
 238                 if (!$summaryOnly)
 239                 {
 240                         $rep->TextCol(0, 1,     $trans['type_name']);
 241                         $rep->TextCol(1, 2,     $trans['supp_reference']);
 242                         $rep->TextCol(2, 3,     sql2date($trans['tran_date']));
 243                         $rep->TextCol(3, 5,     $trans['supp_name']);
 244                         $rep->TextCol(5, 6,     number_format2($trans['NetAmount'], $dec));
 245                         $rep->TextCol(6, 7,     number_format2($trans['Tax'], $dec));
 246
 247                         $rep->NewLine();
 248                         if ($rep->row < $rep->bottomMargin + $rep->lineHeight)
 249                         {
 250                                 $rep->Line($rep->row - 2);
 251                                 $rep->Header();
 252                         }
 253                 }
 254                 $totalinnet += $trans['NetAmount'];
 255                 $totalintax += $trans['Tax'];
 256
 257         }
 258         if (!$summaryOnly)
 259         {
 260                 $rep->NewLine();
 261
 262                 if ($rep->row < $rep->bottomMargin + $rep->lineHeight)
 263                 {
 264                         $rep->Line($rep->row - 2);
 265                         $rep->Header();
 266                 }
 267                 $rep->Line($rep->row + $rep->lineHeight);
 268                 $rep->TextCol(3, 5,     _('Total Inputs'));
 269                 $rep->TextCol(5, 6,     number_format2($totalinnet, $dec));
 270                 $rep->TextCol(6, 7,     number_format2($totalintax, $dec));
 271                 $rep->Line($rep->row - 5);
 272         }
 273         $cols2 = array(0, 100, 200,     300, 400, 500, 600);
 274
 275         $headers2 = array(_('Tax Rate'), _('Outputs'), _('Output Tax'), _('Inputs'), _('Input Tax'));
 276
 277         $aligns2 = array('left', 'right', 'right', 'right',     'right');
 278
 279         $invamount = 0.0;
 280         for ($i = 0; $i < $idcounter; $i++)
 281         {
 282                 $amt = getCustInvTax($taxes[$i], $from, $to);
 283                 $totalinvout[$i] += $amt[0];
 284                 $totaltaxout[$i] += $amt[1];
 285                 $invamount += $amt[0];
 286         }
 287         if ($totalnet != $invamount)
 288                 $totalinvout[$idcounter] = ($invamount - $totalnet);
 289         for ($i = 0; $i < $idcounter; $i++)
 290         {
 291                 $amt = getSuppInvTax($taxes[$i], $from, $to);
 292                 $totalinvin[$i] += $amt[0];
 293                 $totaltaxin[$i] += $amt[1];
 294                 $invamount += $amt[0];
 295         }
 296         if ($totalinnet != $invamount)
 297                 $totalinvin[$idcounter] = ($totalinnet - $invamount);
 298
 299         for ($i = 0; $i < count($cols2); $i++)
 300         {
 301                 $rep->cols[$i] = $rep->leftMargin + $cols2[$i];
 302                 $rep->headers[$i] = $headers2[$i];
 303                 $rep->aligns[$i] = $aligns2[$i];
 304         }
 305         $rep->Header();
 306         $counter = count($totalinvout);
 307         $counter = max($counter, $idcounter);
 308         $trow = $rep->row;
 309         $i = 0;
 310         for ($j = 0; $j < $counter; $j++)
 311         {
 312                 if (isset($taxes[$j]) && $taxes[$j] > 0)
 313                 {
 314                         $tx = getTaxInfo($taxes[$j]);
 315                         $str = $tx['name'] . " " . number_format2($tx['rate'], $dec) . "%";
 316                 }
 317                 else
 318                         $str = _('No tax specified');
 319                 $rep->TextCol($i, $i + 1, $str);
 320                 $rep->NewLine();
 321         }
 322         $i++;
 323         $rep->row = $trow;
 324         for ($j = 0; $j < $counter; $j++)
 325         {
 326                 $rep->TextCol($i, $i + 1, number_format2($totalinvout[$j], $dec));
 327                 $rep->NewLine();
 328         }
 329         $i++;
 330         $rep->row = $trow;
 331         for ($j = 0; $j < $counter; $j++)
 332         {
 333                 $rep->TextCol($i, $i + 1,number_format2($totaltaxout[$j], $dec));
 334                 $rep->NewLine();
 335         }
 336         $i++;
 337         $rep->row = $trow;
 338         for ($j = 0; $j < $counter; $j++)
 339         {
 340                 $rep->TextCol($i, $i + 1, number_format2($totalinvin[$j], $dec));
 341                 $rep->NewLine();
 342         }
 343         $i++;
 344         $rep->row = $trow;
 345         for ($j = 0; $j < $counter; $j++)
 346         {
 347                 $rep->TextCol($i, $i + 1, number_format2($totaltaxin[$j], $dec));
 348                 $rep->NewLine();
 349         }
 350         $rep->Line($rep->row - 4);
 351
 352         $locale = $path_to_root . "lang/" . $_SESSION['language']->code . "/locale.inc";
 353         if (file_exists($locale))
 354         {
 355                 $taxinclude = true;
 356                 include($locale);
 357                 /*
 358                 if (function_exists("TaxFunction"))
 359                         TaxFunction();
 360                 */
 361         }
 362         $rep->End();
 363 }
 364
 365 ?>