X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=reporting%2Frep709.php;h=6f0139a1ff769f94b0bee02f5bbd34c5a3091515;hb=e335b7bff88d068f584da89c61bbfad38632dfe9;hp=f0ebaf93254d6e5400c55460d61684f9b1053822;hpb=340b94e61a41673eddf12bba3815c3d8dfe68408;p=fa-stable.git

diff --git a/reporting/rep709.php b/reporting/rep709.php
index f0ebaf93..6f0139a1 100644
--- a/reporting/rep709.php
+++ b/reporting/rep709.php
@@ -16,16 +16,31 @@ include_once($path_to_root . "gl/includes/gl_db.inc");
 
 //----------------------------------------------------------------------------------------------------
 
-// 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,
@@ -33,9 +48,7 @@ function getCustTransactions($from, $to)
 			".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
@@ -61,7 +74,7 @@ function getSuppTransactions($from, $to)
 			".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
@@ -91,15 +104,20 @@ function getCustInvTax($taxtype, $from, $to)
 {
 	$fromdate = date2sql($from);
 	$todate = date2sql($to);
+	$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(unit_price * quantity*".TB_PREF."debtor_trans.rate), SUM(amount*".TB_PREF."debtor_trans.rate)
+	$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'";
@@ -112,16 +130,16 @@ function getSuppInvTax($taxtype, $from, $to)
 {
 	$fromdate = date2sql($from);
 	$todate = date2sql($to);
-
 	$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'";
 
@@ -188,6 +206,18 @@ function print_tax_report()
 
 	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']);
@@ -198,7 +228,7 @@ function print_tax_report()
 				$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();
 
@@ -209,7 +239,7 @@ function print_tax_report()
 			}
 		}
 		$totalnet += $trans['NetAmount'];
-		$totaltax += $trans['Tax'];
+		$totaltax += $tax_amt;
 
 	}
 	if (!$summaryOnly)
@@ -272,9 +302,9 @@ function print_tax_report()
 	}
 	$cols2 = array(0, 100, 200,	300, 400, 500, 600);
 
-	$headers2 = array(_('Tax Rate'), _('Outputs'), _('Output Tax'),	_('Inputs'), _('Input Tax'));
+	$headers2 = array(_('Tax Rate'), _('Outputs'), _('Output Tax'),	_('Inputs'), _('Input Tax'), '', '');
 
-	$aligns2 = array('left', 'right', 'right', 'right',	'right');
+	$aligns2 = array('left', 'right', 'right', 'right',	'right', 'right', 'right');
 
 	$invamount = 0.0;
 	for ($i = 0; $i < $idcounter; $i++)
@@ -285,29 +315,38 @@ function print_tax_report()
 		$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);
-
-	for ($i = 0; $i < count($cols2) - 2; $i++)
+	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->headers[$i] = $headers2[$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)
 		{
@@ -321,34 +360,89 @@ function print_tax_report()
 	}
 	$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))
 	{