//----------------------------------------------------------------------------------------------------
-// trial_inquiry_controls();
print_tax_report();
+function getTax($tno, $tpe)
+{
+ // GROUP BY debtor_trans_type, debtor_trans_no";
+ $sql = "SELECT SUM(amount) AS Amount, MAX(included_in_price) AS Included FROM ".TB_PREF."debtor_trans_tax_details
+ WHERE debtor_trans_no=$tno
+ AND debtor_trans_type=$tpe
+ AND amount <> 0";
+
+ $result = db_query($sql,"No transactions were returned");
+ return db_fetch($result);
+}
+
function getCustTransactions($from, $to)
{
$fromdate = date2sql($from);
$todate = date2sql($to);
-
+
+ $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";
+
$sql = "SELECT ".TB_PREF."debtor_trans.reference,
+ ".TB_PREF."debtor_trans.trans_no,
".TB_PREF."debtor_trans.type,
+ ".TB_PREF."debtor_trans.rate,
".TB_PREF."sys_types.type_name,
".TB_PREF."debtor_trans.tran_date,
".TB_PREF."debtor_trans.debtor_no,
".TB_PREF."debtors_master.curr_code,
".TB_PREF."debtor_trans.branch_code,
".TB_PREF."debtor_trans.order_,
- (ov_amount+ov_freight)*rate AS NetAmount,
- ov_freight*rate AS FreightAmount,
- ov_gst*rate AS Tax
+ $netamount AS NetAmount
FROM ".TB_PREF."debtor_trans
INNER JOIN ".TB_PREF."debtors_master ON ".TB_PREF."debtor_trans.debtor_no=".TB_PREF."debtors_master.debtor_no
INNER JOIN ".TB_PREF."sys_types ON ".TB_PREF."debtor_trans.type=".TB_PREF."sys_types.type_id
{
$fromdate = date2sql($from);
$todate = date2sql($to);
-
+
$sql = "SELECT ".TB_PREF."supp_trans.supp_reference,
".TB_PREF."supp_trans.type,
".TB_PREF."sys_types.type_name,
".TB_PREF."suppliers.supp_name,
".TB_PREF."suppliers.curr_code,
".TB_PREF."supp_trans.rate,
- ov_amount*rate AS NetAmount,
+ (ov_amount+ov_discount)*rate AS NetAmount,
ov_gst*rate AS Tax
FROM ".TB_PREF."supp_trans
INNER JOIN ".TB_PREF."suppliers ON ".TB_PREF."supp_trans.supplier_id=".TB_PREF."suppliers.supplier_id
{
$fromdate = date2sql($from);
$todate = date2sql($to);
-
- $sql = "SELECT SUM(unit_price * quantity*".TB_PREF."debtor_trans.rate), SUM(amount*".TB_PREF."debtor_trans.rate)
+ $amount = "IF(".TB_PREF."debtor_trans_tax_details.debtor_trans_type=11,-amount,amount)*".TB_PREF."debtor_trans.rate";
+ $mamount = "IF(".TB_PREF."debtor_trans_tax_details.included_in_price=0, 0, $amount)";
+ $netamount = "IF(".TB_PREF."debtor_trans.type=11,-quantity,quantity)*unit_price*".TB_PREF."debtor_trans.rate-$mamount";
+
+ $sql = "SELECT SUM($netamount),
+ SUM($amount)
FROM ".TB_PREF."debtor_trans_details, ".TB_PREF."debtor_trans_tax_details, ".TB_PREF."debtor_trans
- WHERE ".TB_PREF."debtor_trans_details.debtor_trans_type>=10
- AND ".TB_PREF."debtor_trans_details.debtor_trans_type<=11
+ WHERE ".TB_PREF."debtor_trans.type>=10
+ AND ".TB_PREF."debtor_trans.type<=11
AND ".TB_PREF."debtor_trans_details.debtor_trans_no=".TB_PREF."debtor_trans.trans_no
AND ".TB_PREF."debtor_trans_details.debtor_trans_type=".TB_PREF."debtor_trans.type
- AND ".TB_PREF."debtor_trans_details.debtor_trans_no=".TB_PREF."debtor_trans_tax_details.debtor_trans_no
- AND ".TB_PREF."debtor_trans_details.debtor_trans_type=".TB_PREF."debtor_trans_tax_details.debtor_trans_type
+ AND ".TB_PREF."debtor_trans.trans_no=".TB_PREF."debtor_trans_tax_details.debtor_trans_no
+ AND ".TB_PREF."debtor_trans.type=".TB_PREF."debtor_trans_tax_details.debtor_trans_type
+ AND ".TB_PREF."debtor_trans_tax_details.amount <> 0
AND ".TB_PREF."debtor_trans_tax_details.tax_type_id=$taxtype
AND ".TB_PREF."debtor_trans.tran_date >= '$fromdate'
AND ".TB_PREF."debtor_trans.tran_date <= '$todate'";
{
$fromdate = date2sql($from);
$todate = date2sql($to);
-
- $sql = "SELECT SUM(unit_price * quantity * ".TB_PREF."supp_trans.rate), SUM(amount*".TB_PREF."supp_trans.rate)
+ $sql = "SELECT SUM(unit_price * quantity * ".TB_PREF."supp_trans.rate), SUM(amount*".TB_PREF."supp_trans.rate)
FROM ".TB_PREF."supp_invoice_items, ".TB_PREF."supp_invoice_tax_items, ".TB_PREF."supp_trans
- WHERE ".TB_PREF."supp_invoice_items.supp_trans_type>=20
- AND ".TB_PREF."supp_invoice_items.supp_trans_type<=21
- AND ".TB_PREF."supp_invoice_items.supp_trans_no=".TB_PREF."supp_invoice_tax_items.supp_trans_no
- AND ".TB_PREF."supp_invoice_items.supp_trans_type=".TB_PREF."supp_invoice_tax_items.supp_trans_type
+ WHERE ".TB_PREF."supp_trans.type>=20
+ AND ".TB_PREF."supp_trans.type<=21
+ AND ".TB_PREF."supp_trans.trans_no=".TB_PREF."supp_invoice_tax_items.supp_trans_no
+ AND ".TB_PREF."supp_trans.type=".TB_PREF."supp_invoice_tax_items.supp_trans_type
AND ".TB_PREF."supp_invoice_items.supp_trans_no=".TB_PREF."supp_trans.trans_no
AND ".TB_PREF."supp_invoice_items.supp_trans_type=".TB_PREF."supp_trans.type
AND ".TB_PREF."supp_invoice_tax_items.tax_type_id=$taxtype
+ AND ".TB_PREF."supp_invoice_tax_items.amount <> 0
AND ".TB_PREF."supp_trans.tran_date >= '$fromdate'
AND ".TB_PREF."supp_trans.tran_date <= '$todate'";
include_once($path_to_root . "reporting/includes/pdf_report.inc");
$rep = new FrontReport(_('Tax Report'), "TaxReport.pdf", user_pagesize());
-
+
$from = $_POST['PARAM_0'];
$to = $_POST['PARAM_1'];
$summaryOnly = $_POST['PARAM_2'];
$comments = $_POST['PARAM_3'];
$dec = user_price_dec();
-
+
if ($summaryOnly == 1)
$summary = _('Summary Only');
else
$summary = _('Detailed Report');
-
-
+
+
$res = getTaxTypes();
$taxes = array();
while ($tax=db_fetch($res))
$taxes[$i++] = $tax['id'];
$idcounter = count($taxes);
-
- $totalinvout = array();
- $totaltaxout = array();
- $totalinvin = array();
- $totaltaxin = array();
-
+
+ $totalinvout = array(0,0,0,0,0,0,0,0,0,0);
+ $totaltaxout = array(0,0,0,0,0,0,0,0,0,0);
+ $totalinvin = array(0,0,0,0,0,0,0,0,0,0);
+ $totaltaxin = array(0,0,0,0,0,0,0,0,0,0);
+
if (!$summaryOnly)
{
$cols = array(0, 80, 130, 190, 290, 370, 435, 500, 565);
-
+
$headers = array(_('Trans Type'), _('#'), _('Date'), _('Name'), _('Branch Name'),
_('Net'), _('Tax'));
-
+
$aligns = array('left', 'left', 'left', 'left', 'left', 'right', 'right');
-
+
$params = array( 0 => $comments,
1 => array('text' => _('Period'), 'from' => $from, 'to' => $to),
2 => array('text' => _('Type'), 'from' => $summary, 'to' => ''));
while ($trans=db_fetch($transactions))
{
+ $tx = getTax($trans['trans_no'], $trans['type']);
+ if ($tx === false)
+ $tax_amt = 0;
+ else
+ {
+ $tx['Amount'] *= $trans['rate'];
+ if ($trans['type'] == 11)
+ $tx['Amount'] *= -1;
+ if ($tx['Included'] > 0)
+ $trans['NetAmount'] -= $tx['Amount'];
+ $tax_amt = $tx['Amount'];
+ }
if (!$summaryOnly)
{
$rep->TextCol(0, 1, $trans['type_name']);
$rep->TextCol(4, 5, get_branch_name($trans["branch_code"]));
$rep->TextCol(5, 6, number_format2($trans['NetAmount'], $dec));
- $rep->TextCol(6, 7, number_format2($trans['Tax'], $dec));
+ $rep->TextCol(6, 7, number_format2($tax_amt, $dec));
$rep->NewLine();
-
+
if ($rep->row < $rep->bottomMargin + $rep->lineHeight)
{
$rep->Line($rep->row - 2);
}
}
$totalnet += $trans['NetAmount'];
- $totaltax += $trans['Tax'];
+ $totaltax += $tax_amt;
}
if (!$summaryOnly)
}
$totalinnet = 0.0;
$totalintax = 0.0;
-
+
$transactions = getSuppTransactions($from, $to);
while ($trans=db_fetch($transactions))
$rep->Line($rep->row - 5);
}
$cols2 = array(0, 100, 200, 300, 400, 500, 600);
-
- $headers2 = array(_('Tax Rate'), _('Outputs'), _('Output Tax'), _('Inputs'), _('Input Tax'));
-
- $aligns2 = array('left', 'right', 'right', 'right', 'right');
-
- $invamount = 0.0;
+
+ $headers2 = array(_('Tax Rate'), _('Outputs'), _('Output Tax'), _('Inputs'), _('Input Tax'), '', '');
+
+ $aligns2 = array('left', 'right', 'right', 'right', 'right', 'right', 'right');
+
+ $invamount = 0.0;
for ($i = 0; $i < $idcounter; $i++)
{
$amt = getCustInvTax($taxes[$i], $from, $to);
$invamount += $amt[0];
}
if ($totalnet != $invamount)
- $totalinvout[$idcounter] = ($invamount - $totalnet);
+ {
+ $totalinvout[$idcounter] = ($totalnet - $invamount);
+ $totaltaxout[$idcounter] = 0.0;
+ }
+ $invamount2 = 0.0;
for ($i = 0; $i < $idcounter; $i++)
{
$amt = getSuppInvTax($taxes[$i], $from, $to);
$totalinvin[$i] += $amt[0];
$totaltaxin[$i] += $amt[1];
- $invamount += $amt[0];
+ $invamount2 += $amt[0];
}
- if ($totalinnet != $invamount)
- $totalinvin[$idcounter] = ($totalinnet - $invamount);
-
+ if ($totalinnet != $invamount2)
+ {
+ $totalinvin[$idcounter] = ($totalinnet - $invamount2);
+ $totaltaxin[$idcounter] = 0.0;
+ }
+ if ($totalnet != $invamount || $totalinnet != $invamount2)
+ $idcounter++;
for ($i = 0; $i < count($cols2); $i++)
{
$rep->cols[$i] = $rep->leftMargin + $cols2[$i];
$rep->aligns[$i] = $aligns2[$i];
}
$rep->Header();
- $counter = count($totalinvout);
- $counter = max($counter, $idcounter);
+ //$counter = count($totalinvout);
+ //$counter = max($counter, $idcounter);
+
$trow = $rep->row;
$i = 0;
- for ($j = 0; $j < $counter; $j++)
+ for ($j = 0; $j < $idcounter; $j++)
{
if (isset($taxes[$j]) && $taxes[$j] > 0)
{
}
$i++;
$rep->row = $trow;
- for ($j = 0; $j < $counter; $j++)
+ for ($j = 0; $j < $idcounter; $j++)
{
$rep->TextCol($i, $i + 1, number_format2($totalinvout[$j], $dec));
$rep->NewLine();
}
$i++;
$rep->row = $trow;
- for ($j = 0; $j < $counter; $j++)
+ for ($j = 0; $j < $idcounter; $j++)
{
$rep->TextCol($i, $i + 1,number_format2($totaltaxout[$j], $dec));
$rep->NewLine();
}
$i++;
$rep->row = $trow;
- for ($j = 0; $j < $counter; $j++)
+ for ($j = 0; $j < $idcounter; $j++)
{
$rep->TextCol($i, $i + 1, number_format2($totalinvin[$j], $dec));
$rep->NewLine();
}
$i++;
$rep->row = $trow;
- for ($j = 0; $j < $counter; $j++)
+ for ($j = 0; $j < $idcounter; $j++)
{
$rep->TextCol($i, $i + 1, number_format2($totaltaxin[$j], $dec));
$rep->NewLine();
}
$rep->Line($rep->row - 4);
-
+
+ $rep->row -= 16;
+ $rep->TextCol(0, 5, _("General Ledger"));
+ $rep->Line($rep->row - 6);
+
+ $rep->row -= 22;
+
+ $trow = $rep->row;
+
+ $idcounter = count($taxes);
+ $i = 0;
+ for ($j = 0; $j < $idcounter; $j++)
+ {
+ $tx = getTaxInfo($taxes[$j]);
+ $str = $tx['name'] . " " . number_format2($tx['rate'], $dec) . "%";
+ $rep->TextCol($i, $i + 1, $str);
+ $rep->NewLine();
+ }
+ $i++;
+ $rep->row = $trow;
+ for ($j = 0; $j < $idcounter; $j++)
+ {
+ $tx = getTaxInfo($taxes[$j]);
+ $acc = get_gl_account($tx['sales_gl_code']);
+ $rep->TextCol($i, $i + 1, $acc['account_code']." ".$acc['account_name']);
+ $rep->NewLine();
+ }
+ $i++;
+ $rep->row = $trow;
+ for ($j = 0; $j < $idcounter; $j++)
+ {
+ $tx = getTaxInfo($taxes[$j]);
+ $amount = get_gl_trans_from_to($from, $to, $tx['sales_gl_code']);
+ $rep->TextCol($i, $i + 1,number_format2(-$amount, $dec));
+ $rep->NewLine();
+ }
+ $i++;
+ $rep->row = $trow;
+ for ($j = 0; $j < $idcounter; $j++)
+ {
+ $tx = getTaxInfo($taxes[$j]);
+ $acc = get_gl_account($tx['purchasing_gl_code']);
+ $rep->TextCol($i, $i + 1, $acc['account_code']." ".$acc['account_name']);
+ $rep->NewLine();
+ }
+ $i++;
+ $rep->row = $trow;
+ for ($j = 0; $j < $idcounter; $j++)
+ {
+ $tx = getTaxInfo($taxes[$j]);
+ $amount = get_gl_trans_from_to($from, $to, $tx['purchasing_gl_code']);
+ $rep->TextCol($i, $i + 1,number_format2($amount, $dec));
+ $rep->NewLine();
+ }
+ $rep->Line($rep->row - 4);
+
$locale = $path_to_root . "lang/" . $_SESSION['language']->code . "/locale.inc";
if (file_exists($locale))
{
/*
if (function_exists("TaxFunction"))
TaxFunction();
- */
+ */
}
$rep->End();
}