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