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