<?php
/*-------------------------------------------------------+
| TamilAccounting
| http://tamilaccounting.com/
+--------------------------------------------------------+
| Author: Kvvaradha  
| Email: admin@tamilaccounting.com
| Title:	Tax Report
|----------------------------------------------------------------*/

$page_security = 'SA_TAXREP';
$path_to_root="..";

include_once($path_to_root . "/includes/session.inc");
include_once($path_to_root . "/includes/date_functions.inc");
include_once($path_to_root . "/includes/data_checks.inc");
include_once($path_to_root . "/gl/includes/gl_db.inc");

//------------------------------------------------------------------
print_tax_report();

function getTaxTransactions($from, $to,$tax_type=''){
	$fromdate = date2sql($from);
	$todate = date2sql($to);

	$sql = "SELECT tt.name as taxname, taxrec.*, taxrec.amount*ex_rate AS amount,
	            taxrec.net_amount*ex_rate AS net_amount,
				IF(taxrec.trans_type=".ST_BANKPAYMENT." OR taxrec.trans_type=".ST_BANKDEPOSIT.", 
					IF(gl.person_type_id<>".PT_MISC.", gl.memo_, gl.person_id), 
					IF(ISNULL(supp.supp_name), debt.name, supp.supp_name)) as name,
					debt.tax_id,
					supp.gst_no,
				branch.br_name
		FROM ".TB_PREF."trans_tax_details taxrec
		LEFT JOIN ".TB_PREF."tax_types tt	ON taxrec.tax_type_id=tt.id
		LEFT JOIN ".TB_PREF."gl_trans gl ON taxrec.trans_type=gl.type AND taxrec.trans_no=gl.type_no AND gl.amount<>0 AND (tt.purchasing_gl_code=gl.account OR tt.sales_gl_code=gl.account)
		LEFT JOIN ".TB_PREF."supp_trans strans	ON taxrec.trans_no=strans.trans_no AND taxrec.trans_type=strans.type
		LEFT JOIN ".TB_PREF."suppliers as supp ON strans.supplier_id=supp.supplier_id
		LEFT JOIN ".TB_PREF."debtor_trans dtrans	ON taxrec.trans_no=dtrans.trans_no AND taxrec.trans_type=dtrans.type
		LEFT JOIN ".TB_PREF."debtors_master as debt ON dtrans.debtor_no=debt.debtor_no
		LEFT JOIN ".TB_PREF."cust_branch as branch ON dtrans.branch_code=branch.branch_code
		WHERE (taxrec.amount <> 0 OR taxrec.net_amount <> 0)
			AND !ISNULL(taxrec.reg_type)
			AND taxrec.tran_date >= '$fromdate'
			AND taxrec.tran_date <= '$todate'";
		if($tax_type)
			$sql .=" AND tt.id = '$tax_type' ";
		$sql .=" AND taxrec.trans_type !=".ST_CUSTDELIVERY." AND taxrec.trans_type !=".ST_SUPPRECEIVE." ";
		$sql .="ORDER BY taxrec.trans_type, taxrec.tran_date, taxrec.trans_no, taxrec.ex_rate";

    return db_query($sql,"No transactions were returned");
}

function getTaxTypes()
{
	$sql = "SELECT * FROM ".TB_PREF."tax_types ORDER BY id";
    return db_query($sql,"No transactions were returned");
}

function getTaxInfo($id)
{
	$sql = "SELECT * FROM ".TB_PREF."tax_types WHERE id=$id";
    $result = db_query($sql,"No transactions were returned");
    return db_fetch($result);
}

//----------------------------------------------------------------------------------------------------
function print_tax_report(){
	global $path_to_root, $systypes_array;

	$from = $_POST['PARAM_0'];
    $to = $_POST['PARAM_1'];
	$net_io_amount = $_POST['PARAM_2'];
	$comments = $_POST['PARAM_3'];
	$orientation = $_POST['PARAM_4'];
	$destination = $_POST['PARAM_5'];
	$summaryOnly =0;
	if ($destination)
		include_once($path_to_root . "/reporting/includes/excel_report.inc");
	else
		include_once($path_to_root . "/reporting/includes/pdf_report.inc");

	$orientation = ($orientation ? 'L' : 'P');
	$dec = user_price_dec();
	if ($summaryOnly == 1)
		$summary = _('VAT Summary');
	else
		$summary = _('VAT Detailed Report');
	$rep = new FrontReport($summary, "TaxReport", user_pagesize(), 9, $orientation);
	
	$res = getTaxTypes();

	$taxes[0] = array('in'=>0, 'out'=>0, 'taxin'=>0, 'taxout'=>0);
	$taxes= array();
	while ($tax=db_fetch($res))
		$taxes[$tax['id']] = array('in'=>0, 'out'=>0, 'taxin'=>0, 'taxout'=>0);

	$params =   array( 	0 => $comments,
						1 => array('text' => _('Period'), 'from' => $from, 'to' => $to),
						2 => array('text' => _('Type'), 'from' => $summary, 'to' => ''));
	$company_vat_id = get_company_pref('gst_no');
	$cols = array(0, 40, 100, 140, 180, 220,280, 340, 400, 440, 490, 520, 520);

	$headers = array(_('Date'),_('Transaction Type'), _('No'), _('VAT Customer'), _('VAT Supplier'),_('Inv Reference'), _('Name'), _('Tax Name'),_('TAX Rate'),	_('Taxable Amount'), _('Amount'), _('Balance'));
	$aligns = array('left', 'left', 'left', 'left', 'left','left', 'left', 'left', 'right', 'right', 'right', 'right', 'right');
    if ($orientation == 'L')
    	recalculate_cols($cols);

	$rep->Font();
	$rep->Info($params, $cols, $headers, $aligns);
	$rep->fontSize -=1;
	if (!$summaryOnly){
		$rep->NewPage();
	}
	$trans_sales = $trans_purchase = [];
	//$sales_totalnet =	$sales_totaltax = $pr_totalnet = $pr_totaltax = $totalnet = $totaltax = 0.0;
	foreach ($taxes as $tax_id => $sum) {
		$transactions = getTaxTransactions($from, $to,$tax_id);
		$tx = getTaxInfo($tax_id);
		while ($trans=db_fetch($transactions))	{
			if (in_array($trans['trans_type'], array(ST_CUSTCREDIT,ST_SUPPINVOICE,ST_JOURNAL))) {
				$trans['net_amount'] *= -1;
				$trans['amount'] *= -1;
			}
			if($trans['reg_type']==TR_INPUT){ //purchase
				$trans['tax_id'] = $company_vat_id;
				$trans_purchase[] = $trans;
			}elseif($trans['reg_type'] == TR_OUTPUT){ //sales
				$trans['gst_no'] = $company_vat_id;
				$trans_sales[] = $trans;
			}
		}
	}

	if(!empty($trans_sales)){
		$totalnet = $totaltax = $linebalance = 0.0;
		foreach($trans_sales as $trans){
			if (!$summaryOnly)	{
				$c=0;
				$rep->DateCol($c++, $c,	$trans['tran_date'], true);
				$rep->TextCol($c++, $c, isset($systypes_array[$trans['trans_type']]) ? $systypes_array[$trans['trans_type']] : '');
				// if ($trans['memo'] == '')
				// 	$trans['memo'] = get_reference($trans['trans_type'], $trans['trans_no']);
				$rep->TextCol($c++, $c,	get_reference($trans['trans_type'], $trans['trans_no']));
				$rep->TextCol($c++, $c,	$trans['tax_id']);
				$rep->TextCol($c++, $c,	$trans['gst_no']);
				$rep->TextCol($c++, $c, $trans['memo']);
				$rep->TextCol($c++, $c,	$trans['name']);

				// $rep->TextCol($c++, $c,	$trans['br_name']);
				
				$rep->TextCol($c++, $c, $trans['taxname']);
				$rep->AmountCol($c++, $c, $trans['rate'], $dec);
				$rep->AmountCol($c++, $c, $trans['net_amount'], $dec);
				$rep->AmountCol($c++, $c, $trans['amount'], $dec);
				$linebalance += $trans['amount'];
				$rep->AmountCol($c++, $c, $linebalance, $dec);

				$rep->NewLine();

				if ($rep->row < $rep->bottomMargin + $rep->lineHeight)	{
					$rep->Line($rep->row - 2);
					$rep->NewPage();
				}
			}
			$tax_type = $trans['tax_type_id'];
			if ($trans['trans_type']==ST_JOURNAL && $trans['reg_type']==TR_INPUT) {
				$taxes[$tax_type]['taxin'] += $trans['amount'];
				$taxes[$tax_type]['in'] += $trans['net_amount'];
			}
			elseif ($trans['trans_type']==ST_JOURNAL && $trans['reg_type']==TR_OUTPUT) {
				$taxes[$tax_type]['taxout'] += $trans['amount'];
				$taxes[$tax_type]['out'] += $trans['net_amount'];
			}
			elseif (in_array($trans['trans_type'], array(ST_BANKDEPOSIT,ST_SALESINVOICE,ST_CUSTCREDIT))) {
				$taxes[$tax_type]['taxout'] += $trans['amount'];
				$taxes[$tax_type]['out'] += $trans['net_amount'];
			} elseif ($trans['reg_type'] !== NULL) {
				$taxes[$tax_type]['taxin'] += $trans['amount'];
				$taxes[$tax_type]['in'] += $trans['net_amount'];
			}
			$totalnet += $trans['net_amount'];
			$totaltax += $trans['amount'];
		}
		$rep->Line($rep->row);
			$rep->NewLine();
			$rep->Font('bold');
			$rep->TextCol(0,5,'Total for Sales ' );
			$rep->AmountCol(9, 10, $totalnet, $dec);
			$rep->AmountCol(10, 11, $totaltax, $dec);
			$rep->Font();
			$rep->Line($rep->row-5);
			$rep->NewLine(2);
	}

	if(!empty($trans_purchase)){
		$totalnet = $totaltax = $linebalance = 0.0;
		foreach($trans_purchase as $trans){
			if (!$summaryOnly)	{
				$c=0;
				$rep->DateCol($c++, $c,	$trans['tran_date'], true);
				$rep->TextCol($c++, $c, isset($systypes_array[$trans['trans_type']]) ? $systypes_array[$trans['trans_type']] : '');
				// if ($trans['memo'] == '')
				// 	$trans['memo'] = get_reference($trans['trans_type'], $trans['trans_no']);
				$rep->TextCol($c++, $c,	get_reference($trans['trans_type'], $trans['trans_no']));
				$rep->TextCol($c++, $c,	$trans['tax_id']);
				$rep->TextCol($c++, $c,	$trans['gst_no']);
				$rep->TextCol($c++, $c, $trans['memo']);
				$rep->TextCol($c++, $c,	$trans['name']);

				// $rep->TextCol($c++, $c,	$trans['br_name']);
				
				$rep->TextCol($c++, $c, $trans['taxname']);
				$rep->AmountCol($c++, $c, $trans['rate'], $dec);
				$rep->AmountCol($c++, $c, $trans['net_amount'], $dec);
				$rep->AmountCol($c++, $c, $trans['amount'], $dec);
				$linebalance += $trans['amount'];
				$rep->AmountCol($c++, $c, $linebalance, $dec);

				$rep->NewLine();

				if ($rep->row < $rep->bottomMargin + $rep->lineHeight)	{
					$rep->Line($rep->row - 2);
					$rep->NewPage();
				}
			}
			$tax_type = $trans['tax_type_id'];
			if ($trans['trans_type']==ST_JOURNAL && $trans['reg_type']==TR_INPUT) {
				$taxes[$tax_type]['taxin'] += $trans['amount'];
				$taxes[$tax_type]['in'] += $trans['net_amount'];
			}
			elseif ($trans['trans_type']==ST_JOURNAL && $trans['reg_type']==TR_OUTPUT) {
				$taxes[$tax_type]['taxout'] += $trans['amount'];
				$taxes[$tax_type]['out'] += $trans['net_amount'];
			}
			elseif (in_array($trans['trans_type'], array(ST_BANKDEPOSIT,ST_SALESINVOICE,ST_CUSTCREDIT))) {
				$taxes[$tax_type]['taxout'] += $trans['amount'];
				$taxes[$tax_type]['out'] += $trans['net_amount'];
			} elseif ($trans['reg_type'] !== NULL) {
				$taxes[$tax_type]['taxin'] += $trans['amount'];
				$taxes[$tax_type]['in'] += $trans['net_amount'];
			}
			$totalnet += $trans['net_amount'];
			$totaltax += $trans['amount'];
		}
		//if($taxes[$tax_id]['taxout']!=0 || $taxes[$tax_id]['taxout']!=0){
			$rep->Line($rep->row);
			$rep->NewLine();
			$rep->Font('bold');
			$rep->TextCol(0,5,'Total for Purchase ' );
			$rep->AmountCol(9, 10, $totalnet, $dec);
			$rep->AmountCol(10, 11, $totaltax, $dec);
			$rep->Font();
			$rep->Line($rep->row-5);
			$rep->NewLine();
		//}	
	}
	
	
	hook_tax_report_done();
	$rep->End();
}