4 // ----------------------------------------------------------------
9 // ----------------------------------------------------------------
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");
17 //----------------------------------------------------------------------------------------------------
21 function getTax($tno, $tpe)
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
29 $result = db_query($sql,"No transactions were returned");
30 return db_fetch($result);
33 function getCustTransactions($from, $to)
35 $fromdate = date2sql($from);
36 $todate = date2sql($to);
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";
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";
60 return db_query($sql,"No transactions were returned");
63 function getSuppTransactions($from, $to)
65 $fromdate = date2sql($from);
66 $todate = date2sql($to);
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,
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";
87 return db_query($sql,"No transactions were returned");
90 function getTaxTypes()
92 $sql = "SELECT id FROM ".TB_PREF."tax_types ORDER BY id";
93 return db_query($sql,"No transactions were returned");
96 function getTaxInfo($id)
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);
103 function getCustInvTax($taxtype, $from, $to)
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";
111 $sql = "SELECT SUM($netamount),
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'";
125 $result = db_query($sql,"No transactions were returned");
126 return db_fetch_row($result);
129 function getSuppInvTax($taxtype, $from, $to)
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'";
146 $result = db_query($sql,"No transactions were returned");
147 return db_fetch_row($result);
150 //----------------------------------------------------------------------------------------------------
152 function print_tax_report()
154 global $path_to_root;
156 include_once($path_to_root . "reporting/includes/pdf_report.inc");
158 $rep = new FrontReport(_('Tax Report'), "TaxReport.pdf", user_pagesize());
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();
166 if ($summaryOnly == 1)
167 $summary = _('Summary Only');
169 $summary = _('Detailed Report');
172 $res = getTaxTypes();
176 while ($tax=db_fetch($res))
177 $taxes[$i++] = $tax['id'];
178 $idcounter = count($taxes);
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);
187 $cols = array(0, 80, 130, 190, 290, 370, 435, 500, 565);
189 $headers = array(_('Trans Type'), _('#'), _('Date'), _('Name'), _('Branch Name'),
192 $aligns = array('left', 'left', 'left', 'left', 'left', 'right', 'right');
194 $params = array( 0 => $comments,
195 1 => array('text' => _('Period'), 'from' => $from, 'to' => $to),
196 2 => array('text' => _('Type'), 'from' => $summary, 'to' => ''));
199 $rep->Info($params, $cols, $headers, $aligns);
205 $transactions = getCustTransactions($from, $to);
207 while ($trans=db_fetch($transactions))
209 $tx = getTax($trans['trans_no'], $trans['type']);
214 $tx['Amount'] *= $trans['rate'];
215 if ($trans['type'] == 11)
217 if ($tx['Included'] > 0)
218 $trans['NetAmount'] -= $tx['Amount'];
219 $tax_amt = $tx['Amount'];
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"]));
230 $rep->TextCol(5, 6, number_format2($trans['NetAmount'], $dec));
231 $rep->TextCol(6, 7, number_format2($tax_amt, $dec));
235 if ($rep->row < $rep->bottomMargin + $rep->lineHeight)
237 $rep->Line($rep->row - 2);
241 $totalnet += $trans['NetAmount'];
242 $totaltax += $tax_amt;
249 if ($rep->row < $rep->bottomMargin + $rep->lineHeight)
251 $rep->Line($rep->row - 2);
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);
264 $transactions = getSuppTransactions($from, $to);
266 while ($trans=db_fetch($transactions))
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));
278 if ($rep->row < $rep->bottomMargin + $rep->lineHeight)
280 $rep->Line($rep->row - 2);
284 $totalinnet += $trans['NetAmount'];
285 $totalintax += $trans['Tax'];
292 if ($rep->row < $rep->bottomMargin + $rep->lineHeight)
294 $rep->Line($rep->row - 2);
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);
303 $cols2 = array(0, 100, 200, 300, 400, 500, 600);
305 $headers2 = array(_('Tax Rate'), _('Outputs'), _('Output Tax'), _('Inputs'), _('Input Tax'), '', '');
307 $aligns2 = array('left', 'right', 'right', 'right', 'right', 'right', 'right');
310 for ($i = 0; $i < $idcounter; $i++)
312 $amt = getCustInvTax($taxes[$i], $from, $to);
313 $totalinvout[$i] += $amt[0];
314 $totaltaxout[$i] += $amt[1];
315 $invamount += $amt[0];
317 if ($totalnet != $invamount)
319 $totalinvout[$idcounter] = ($totalnet - $invamount);
320 $totaltaxout[$idcounter] = 0.0;
323 for ($i = 0; $i < $idcounter; $i++)
325 $amt = getSuppInvTax($taxes[$i], $from, $to);
326 $totalinvin[$i] += $amt[0];
327 $totaltaxin[$i] += $amt[1];
328 $invamount2 += $amt[0];
330 if ($totalinnet != $invamount2)
332 $totalinvin[$idcounter] = ($totalinnet - $invamount2);
333 $totaltaxin[$idcounter] = 0.0;
335 if ($totalnet != $invamount || $totalinnet != $invamount2)
337 for ($i = 0; $i < count($cols2); $i++)
339 $rep->cols[$i] = $rep->leftMargin + $cols2[$i];
340 $rep->headers[$i] = $headers2[$i];
341 $rep->aligns[$i] = $aligns2[$i];
344 //$counter = count($totalinvout);
345 //$counter = max($counter, $idcounter);
349 for ($j = 0; $j < $idcounter; $j++)
351 if (isset($taxes[$j]) && $taxes[$j] > 0)
353 $tx = getTaxInfo($taxes[$j]);
354 $str = $tx['name'] . " " . number_format2($tx['rate'], $dec) . "%";
357 $str = _('No tax specified');
358 $rep->TextCol($i, $i + 1, $str);
363 for ($j = 0; $j < $idcounter; $j++)
365 $rep->TextCol($i, $i + 1, number_format2($totalinvout[$j], $dec));
370 for ($j = 0; $j < $idcounter; $j++)
372 $rep->TextCol($i, $i + 1,number_format2($totaltaxout[$j], $dec));
377 for ($j = 0; $j < $idcounter; $j++)
379 $rep->TextCol($i, $i + 1, number_format2($totalinvin[$j], $dec));
384 for ($j = 0; $j < $idcounter; $j++)
386 $rep->TextCol($i, $i + 1, number_format2($totaltaxin[$j], $dec));
389 $rep->Line($rep->row - 4);
392 $rep->TextCol(0, 5, _("General Ledger"));
393 $rep->Line($rep->row - 6);
399 $idcounter = count($taxes);
401 for ($j = 0; $j < $idcounter; $j++)
403 $tx = getTaxInfo($taxes[$j]);
404 $str = $tx['name'] . " " . number_format2($tx['rate'], $dec) . "%";
405 $rep->TextCol($i, $i + 1, $str);
410 for ($j = 0; $j < $idcounter; $j++)
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']);
419 for ($j = 0; $j < $idcounter; $j++)
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));
428 for ($j = 0; $j < $idcounter; $j++)
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']);
437 for ($j = 0; $j < $idcounter; $j++)
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));
444 $rep->Line($rep->row - 4);
446 $locale = $path_to_root . "lang/" . $_SESSION['language']->code . "/locale.inc";
447 if (file_exists($locale))
452 if (function_exists("TaxFunction"))