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