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 //----------------------------------------------------------------------------------------------------
19 // trial_inquiry_controls();
22 function getCustTransactions($from, $to)
24 $fromdate = date2sql($from);
25 $todate = date2sql($to);
27 $sql = "SELECT ".TB_PREF."debtor_trans.reference,
28 ".TB_PREF."debtor_trans.type,
29 ".TB_PREF."sys_types.type_name,
30 ".TB_PREF."debtor_trans.tran_date,
31 ".TB_PREF."debtor_trans.debtor_no,
32 ".TB_PREF."debtors_master.name,
33 ".TB_PREF."debtors_master.curr_code,
34 ".TB_PREF."debtor_trans.branch_code,
35 ".TB_PREF."debtor_trans.order_,
36 (ov_amount+ov_freight)*rate AS NetAmount,
37 ov_freight*rate AS FreightAmount,
39 FROM ".TB_PREF."debtor_trans
40 INNER JOIN ".TB_PREF."debtors_master ON ".TB_PREF."debtor_trans.debtor_no=".TB_PREF."debtors_master.debtor_no
41 INNER JOIN ".TB_PREF."sys_types ON ".TB_PREF."debtor_trans.type=".TB_PREF."sys_types.type_id
42 WHERE ".TB_PREF."debtor_trans.tran_date >= '$fromdate'
43 AND ".TB_PREF."debtor_trans.tran_date <= '$todate'
44 AND (".TB_PREF."debtor_trans.type=10 OR ".TB_PREF."debtor_trans.type=11)
45 ORDER BY ".TB_PREF."debtor_trans.tran_date";
47 return db_query($sql,"No transactions were returned");
50 function getSuppTransactions($from, $to)
52 $fromdate = date2sql($from);
53 $todate = date2sql($to);
55 $sql = "SELECT ".TB_PREF."supp_trans.supp_reference,
56 ".TB_PREF."supp_trans.type,
57 ".TB_PREF."sys_types.type_name,
58 ".TB_PREF."supp_trans.tran_date,
59 ".TB_PREF."supp_trans.supplier_id,
60 ".TB_PREF."supp_trans.rate,
61 ".TB_PREF."suppliers.supp_name,
62 ".TB_PREF."suppliers.curr_code,
63 ".TB_PREF."supp_trans.rate,
64 ov_amount*rate AS NetAmount,
66 FROM ".TB_PREF."supp_trans
67 INNER JOIN ".TB_PREF."suppliers ON ".TB_PREF."supp_trans.supplier_id=".TB_PREF."suppliers.supplier_id
68 INNER JOIN ".TB_PREF."sys_types ON ".TB_PREF."supp_trans.type=".TB_PREF."sys_types.type_id
69 WHERE ".TB_PREF."supp_trans.tran_date >= '$fromdate'
70 AND ".TB_PREF."supp_trans.tran_date <= '$todate'
71 AND (".TB_PREF."supp_trans.type=20 OR ".TB_PREF."supp_trans.type=21)
72 ORDER BY ".TB_PREF."supp_trans.tran_date";
74 return db_query($sql,"No transactions were returned");
77 function getTaxTypes()
79 $sql = "SELECT id FROM ".TB_PREF."tax_types ORDER BY id";
80 return db_query($sql,"No transactions were returned");
83 function getTaxInfo($id)
85 $sql = "SELECT * FROM ".TB_PREF."tax_types WHERE id=$id";
86 $result = db_query($sql,"No transactions were returned");
87 return db_fetch($result);
90 function getCustInvTax($taxtype, $from, $to)
92 $fromdate = date2sql($from);
93 $todate = date2sql($to);
95 $sql = "SELECT SUM(unit_price * quantity*".TB_PREF."debtor_trans.rate), SUM(amount*".TB_PREF."debtor_trans.rate)
96 FROM ".TB_PREF."debtor_trans_details, ".TB_PREF."debtor_trans_tax_details, ".TB_PREF."debtor_trans
97 WHERE ".TB_PREF."debtor_trans_details.debtor_trans_type>=10
98 AND ".TB_PREF."debtor_trans_details.debtor_trans_type<=11
99 AND ".TB_PREF."debtor_trans_details.debtor_trans_no=".TB_PREF."debtor_trans.trans_no
100 AND ".TB_PREF."debtor_trans_details.debtor_trans_type=".TB_PREF."debtor_trans.type
101 AND ".TB_PREF."debtor_trans_details.debtor_trans_no=".TB_PREF."debtor_trans_tax_details.debtor_trans_no
102 AND ".TB_PREF."debtor_trans_details.debtor_trans_type=".TB_PREF."debtor_trans_tax_details.debtor_trans_type
103 AND ".TB_PREF."debtor_trans_tax_details.tax_type_id=$taxtype
104 AND ".TB_PREF."debtor_trans.tran_date >= '$fromdate'
105 AND ".TB_PREF."debtor_trans.tran_date <= '$todate'";
107 $result = db_query($sql,"No transactions were returned");
108 return db_fetch_row($result);
111 function getSuppInvTax($taxtype, $from, $to)
113 $fromdate = date2sql($from);
114 $todate = date2sql($to);
116 $sql = "SELECT SUM(unit_price * quantity * ".TB_PREF."supp_trans.rate), SUM(amount*".TB_PREF."supp_trans.rate)
117 FROM ".TB_PREF."supp_invoice_items, ".TB_PREF."supp_invoice_tax_items, ".TB_PREF."supp_trans
118 WHERE ".TB_PREF."supp_invoice_items.supp_trans_type>=20
119 AND ".TB_PREF."supp_invoice_items.supp_trans_type<=21
120 AND ".TB_PREF."supp_invoice_items.supp_trans_no=".TB_PREF."supp_invoice_tax_items.supp_trans_no
121 AND ".TB_PREF."supp_invoice_items.supp_trans_type=".TB_PREF."supp_invoice_tax_items.supp_trans_type
122 AND ".TB_PREF."supp_invoice_items.supp_trans_no=".TB_PREF."supp_trans.trans_no
123 AND ".TB_PREF."supp_invoice_items.supp_trans_type=".TB_PREF."supp_trans.type
124 AND ".TB_PREF."supp_invoice_tax_items.tax_type_id=$taxtype
125 AND ".TB_PREF."supp_trans.tran_date >= '$fromdate'
126 AND ".TB_PREF."supp_trans.tran_date <= '$todate'";
128 $result = db_query($sql,"No transactions were returned");
129 return db_fetch_row($result);
132 //----------------------------------------------------------------------------------------------------
134 function print_tax_report()
136 global $path_to_root;
138 include_once($path_to_root . "reporting/includes/pdf_report.inc");
140 $rep = new FrontReport(_('Tax Report'), "TaxReport.pdf", user_pagesize());
142 $from = $_POST['PARAM_0'];
143 $to = $_POST['PARAM_1'];
144 $summaryOnly = $_POST['PARAM_2'];
145 $comments = $_POST['PARAM_3'];
146 $dec = user_price_dec();
148 if ($summaryOnly == 1)
149 $summary = _('Summary Only');
151 $summary = _('Detailed Report');
154 $res = getTaxTypes();
158 while ($tax=db_fetch($res))
159 $taxes[$i++] = $tax['id'];
160 $idcounter = count($taxes);
162 $totalinvout = array(0,0,0,0,0,0,0,0,0,0);
163 $totaltaxout = array(0,0,0,0,0,0,0,0,0,0);
164 $totalinvin = array(0,0,0,0,0,0,0,0,0,0);
165 $totaltaxin = array(0,0,0,0,0,0,0,0,0,0);
169 $cols = array(0, 80, 130, 190, 290, 370, 435, 500, 565);
171 $headers = array(_('Trans Type'), _('#'), _('Date'), _('Name'), _('Branch Name'),
174 $aligns = array('left', 'left', 'left', 'left', 'left', 'right', 'right');
176 $params = array( 0 => $comments,
177 1 => array('text' => _('Period'), 'from' => $from, 'to' => $to),
178 2 => array('text' => _('Type'), 'from' => $summary, 'to' => ''));
181 $rep->Info($params, $cols, $headers, $aligns);
187 $transactions = getCustTransactions($from, $to);
189 while ($trans=db_fetch($transactions))
193 $rep->TextCol(0, 1, $trans['type_name']);
194 $rep->TextCol(1, 2, $trans['reference']);
195 $rep->TextCol(2, 3, sql2date($trans['tran_date']));
196 $rep->TextCol(3, 4, $trans['name']);
197 if ($trans["branch_code"] > 0)
198 $rep->TextCol(4, 5, get_branch_name($trans["branch_code"]));
200 $rep->TextCol(5, 6, number_format2($trans['NetAmount'], $dec));
201 $rep->TextCol(6, 7, number_format2($trans['Tax'], $dec));
205 if ($rep->row < $rep->bottomMargin + $rep->lineHeight)
207 $rep->Line($rep->row - 2);
211 $totalnet += $trans['NetAmount'];
212 $totaltax += $trans['Tax'];
219 if ($rep->row < $rep->bottomMargin + $rep->lineHeight)
221 $rep->Line($rep->row - 2);
224 $rep->Line($rep->row + $rep->lineHeight);
225 $rep->TextCol(3, 5, _('Total Outputs'));
226 $rep->TextCol(5, 6, number_format2($totalnet, $dec));
227 $rep->TextCol(6, 7, number_format2($totaltax, $dec));
228 $rep->Line($rep->row - 5);
234 $transactions = getSuppTransactions($from, $to);
236 while ($trans=db_fetch($transactions))
240 $rep->TextCol(0, 1, $trans['type_name']);
241 $rep->TextCol(1, 2, $trans['supp_reference']);
242 $rep->TextCol(2, 3, sql2date($trans['tran_date']));
243 $rep->TextCol(3, 5, $trans['supp_name']);
244 $rep->TextCol(5, 6, number_format2($trans['NetAmount'], $dec));
245 $rep->TextCol(6, 7, number_format2($trans['Tax'], $dec));
248 if ($rep->row < $rep->bottomMargin + $rep->lineHeight)
250 $rep->Line($rep->row - 2);
254 $totalinnet += $trans['NetAmount'];
255 $totalintax += $trans['Tax'];
262 if ($rep->row < $rep->bottomMargin + $rep->lineHeight)
264 $rep->Line($rep->row - 2);
267 $rep->Line($rep->row + $rep->lineHeight);
268 $rep->TextCol(3, 5, _('Total Inputs'));
269 $rep->TextCol(5, 6, number_format2($totalinnet, $dec));
270 $rep->TextCol(6, 7, number_format2($totalintax, $dec));
271 $rep->Line($rep->row - 5);
273 $cols2 = array(0, 100, 200, 300, 400, 500, 600);
275 $headers2 = array(_('Tax Rate'), _('Outputs'), _('Output Tax'), _('Inputs'), _('Input Tax'));
277 $aligns2 = array('left', 'right', 'right', 'right', 'right');
280 for ($i = 0; $i < $idcounter; $i++)
282 $amt = getCustInvTax($taxes[$i], $from, $to);
283 $totalinvout[$i] += $amt[0];
284 $totaltaxout[$i] += $amt[1];
285 $invamount += $amt[0];
287 if ($totalnet != $invamount)
288 $totalinvout[$idcounter] = ($invamount - $totalnet);
289 for ($i = 0; $i < $idcounter; $i++)
291 $amt = getSuppInvTax($taxes[$i], $from, $to);
292 $totalinvin[$i] += $amt[0];
293 $totaltaxin[$i] += $amt[1];
294 $invamount += $amt[0];
296 if ($totalinnet != $invamount)
297 $totalinvin[$idcounter] = ($totalinnet - $invamount);
299 for ($i = 0; $i < count($cols2) - 2; $i++)
301 $rep->cols[$i] = $rep->leftMargin + $cols2[$i];
302 $rep->headers[$i] = $headers2[$i];
303 $rep->aligns[$i] = $aligns2[$i];
306 $counter = count($totalinvout);
307 $counter = max($counter, $idcounter);
310 for ($j = 0; $j < $counter; $j++)
312 if (isset($taxes[$j]) && $taxes[$j] > 0)
314 $tx = getTaxInfo($taxes[$j]);
315 $str = $tx['name'] . " " . number_format2($tx['rate'], $dec) . "%";
318 $str = _('No tax specified');
319 $rep->TextCol($i, $i + 1, $str);
324 for ($j = 0; $j < $counter; $j++)
326 $rep->TextCol($i, $i + 1, number_format2($totalinvout[$j], $dec));
331 for ($j = 0; $j < $counter; $j++)
333 $rep->TextCol($i, $i + 1,number_format2($totaltaxout[$j], $dec));
338 for ($j = 0; $j < $counter; $j++)
340 $rep->TextCol($i, $i + 1, number_format2($totalinvin[$j], $dec));
345 for ($j = 0; $j < $counter; $j++)
347 $rep->TextCol($i, $i + 1, number_format2($totaltaxin[$j], $dec));
350 $rep->Line($rep->row - 4);
352 $locale = $path_to_root . "lang/" . $_SESSION['language']->code . "/locale.inc";
353 if (file_exists($locale))
358 if (function_exists("TaxFunction"))