reporting/rep709.php

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