X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=reporting%2Frep709.php;h=6f0139a1ff769f94b0bee02f5bbd34c5a3091515;hb=0ad69edb5561aac03a5af49286c37b3350307719;hp=1321d1ead5112b920303bcb4ac4d0fd3d9c5252c;hpb=6885f276e8e8bcedf735f8f4e46412044d73bf36;p=fa-stable.git

diff --git a/reporting/rep709.php b/reporting/rep709.php
index 1321d1ea..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
@@ -51,7 +64,7 @@ function getSuppTransactions($from, $to)
 {
 	$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,
@@ -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);
-	
-	$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'";
@@ -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)  
+	$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'";
 
@@ -138,19 +156,19 @@ function print_tax_report()
 	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();
@@ -158,21 +176,21 @@ function print_tax_report()
 	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' => ''));
@@ -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,10 +228,10 @@ 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();
-						
+
 			if ($rep->row < $rep->bottomMargin + $rep->lineHeight)
 			{
 				$rep->Line($rep->row - 2);
@@ -209,7 +239,7 @@ function print_tax_report()
 			}
 		}
 		$totalnet += $trans['NetAmount'];
-		$totaltax += $trans['Tax'];
+		$totaltax += $tax_amt;
 
 	}
 	if (!$summaryOnly)
@@ -230,7 +260,7 @@ function print_tax_report()
 	}
 	$totalinnet = 0.0;
 	$totalintax = 0.0;
-	
+
 	$transactions = getSuppTransactions($from, $to);
 
 	while ($trans=db_fetch($transactions))
@@ -271,12 +301,12 @@ function print_tax_report()
 		$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);
@@ -285,17 +315,25 @@ 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);
-
+	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];
@@ -303,11 +341,12 @@ function print_tax_report()
 		$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))
 	{
@@ -357,7 +451,7 @@ function print_tax_report()
 		/*
 		if (function_exists("TaxFunction"))
 			TaxFunction();
-		*/	
+		*/
 	}
 	$rep->End();
 }