<?php
/**********************************************************************
***********************************************************************/
$page_security = 'SA_TAXREP';
// ----------------------------------------------------------------
// $ Revision:	2.0 $
// Creator:	Joe Hunt
// date_:	2005-05-19
// Title:	Tax Report
// ----------------------------------------------------------------
$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 . "/gl/includes/gl_db.inc");

print_non_tax_report();

function rep_get_non_tax_transactions($from_date_, $to_date_, $trans_type, $non_tax_group_ids_array) {
    
    $supp_tran_types_array = array(ST_SUPPCREDIT, ST_SUBCONTRACTOR_CREDIT_NOTE, ST_UTILITY_BILL_CREDIT_NOTE, ST_SUPPINVOICE, 
                                   ST_SUPPLIER_TRANSPORT_INVOICES, ST_SUBCONTRACTOR_INVOICE, ST_UTILITY_BILLS);

    $cust_tran_types_array = array(ST_SALESINVOICE, ST_PROJECT_INVOICE, ST_CUSTCREDIT, ST_PROJECT_CREDIT_NOTE);
    
    $from_date = date2sql($from_date_);
    $to_date = date2sql($to_date_);
    
	$sql_1 = "SELECT strans.type,
                    strans.trans_no,
                    strans.reference,
                    CONCAT(supp.supp_ref, ' - ', supp.supp_name) name, 
                    strans.tran_date, 
                    strans.ov_amount net_amount,
                    strans.tax_group_id
		FROM ".TB_PREF."supp_trans strans
                    JOIN ".TB_PREF."suppliers as supp 
                        ON strans.supplier_id = supp.supplier_id
		WHERE strans.tax_group_id IN (".implode(',', $non_tax_group_ids_array).") 
                    AND strans.tran_date >= ".db_escape($from_date)."
                    AND strans.tran_date <= ".db_escape($to_date);
        
            if ($trans_type > -1) {
                $sql_1 .= " AND strans.type = ".db_escape($trans_type);
            } else {
                $sql_1 .= " AND strans.type IN (".implode(',', $supp_tran_types_array).") ";
            }

            $sql_1 .= " ORDER BY strans.type, strans.tran_date, strans.trans_no";
        
    
	$sql_2 = "SELECT dtrans.type,
                    dtrans.trans_no,
                    dtrans.reference,
                    CONCAT(debt.debtor_ref, ' - ', debt.debtor_ref) name, 
                    dtrans.tran_date, 
                    dtrans.ov_amount net_amount,
                    dtrans.trans_tax_group_id tax_group_id
		FROM ".TB_PREF."debtor_trans dtrans
                    JOIN ".TB_PREF."debtors_master as debt 
                        ON dtrans.debtor_no = debt.debtor_no
                    JOIN ".TB_PREF."cust_branch as branch 
                        ON dtrans.branch_code = branch.branch_code
		WHERE dtrans.trans_tax_group_id IN (".implode(',', $non_tax_group_ids_array).") 
                    AND dtrans.tran_date >= ".db_escape($from_date)."
                    AND dtrans.tran_date <= ".db_escape($to_date);
        
            if ($trans_type > -1) {
                $sql_2 .= " AND dtrans.type = ".db_escape($trans_type);
            } else {
                $sql_2 .= " AND dtrans.type IN (".implode(',', $cust_tran_types_array).") ";
            }

            $sql_2 .= " ORDER BY dtrans.type, dtrans.tran_date, dtrans.trans_no";
        
        $sql = " ($sql_1) UNION ($sql_2)";
        
        return db_query($sql, "rep_get_non_tax_transactions");
}

function rep_get_non_tax_group_ids_array(&$tax_group_totals_array=array()) {
    $sql = "SELECT DISTINCT id, name    
            FROM ".TB_PREF."tax_groups tg 
                LEFT JOIN ".TB_PREF."tax_group_items it
                    ON tg.id = it.tax_group_id 
            WHERE it.tax_group_id IS NULL";
        
    $result = db_query($sql, "get_non_tax_group_ids");
        
        $non_tax_group_ids_array = array(0); // there has transactions without group id 
        
            while ($row = db_fetch($result)) {
                $non_tax_group_ids_array[] = $row['id'];
                $tax_group_totals_array[$row['id']] = array('group_name' => $row['name'], 'group_total' => 0);
            }
                $tax_group_totals_array[0] = array('group_name' => 'No Tax Group', 'group_total' => 0);

                return $non_tax_group_ids_array;
}

function print_non_tax_report_no_records_found(&$rep, $result) {
    if (! (db_num_rows($result) > 0)) {
        
        $rep->NewLine(2);
        $rep->Font('bold');
            $rep->Text($rep->leftMargin + 160, "No any record found for selected parameters.");
        $rep->Font();
        $rep->End();
        
        return true;
    } 
        
        return false;
}

function print_non_tax_report() {
    global $path_to_root, $systypes_array, $customer_debit_types_array, $customer_credit_types_array, $supplier_credit_types_array, 
           $supplier_debit_types_array;
	
	$from_date_ = $_POST['PARAM_0'];
	$to_date_ = $_POST['PARAM_1'];
	$trans_type = $_POST['PARAM_2'];
	$destination = $_POST['PARAM_3'];

	if ($destination) {
            include_once($path_to_root . "/reporting/includes/excel_report.inc");
        } else {
            include_once($path_to_root . "/reporting/includes/pdf_report.inc");
        }

	$orientation = 'P';
	$dec = user_price_dec();
        
        $trans_type_name = 'All Type';
        if ($trans_type > -1) {
            $trans_type_name = $systypes_array[$trans_type];
        } 

	$rep = new FrontReport(_('Non Tax Report'), "NonTaxReport", user_pagesize(), 9, $orientation);
        $rep->SetHeaderType('form_981');

	$params =   array( 	0 => array('text' => _('Period'), 'from' => $from_date_, 'to' => $to_date_),
                                1 => array('text' => _('Type'), 'trans_type' => $trans_type_name));

	$cols = array($x=0, $x+=80, $x+=50, $x+=50, $x+=55, $x+=170, $x+=5, $x+=40, $x+=73);

	$headers = array(_('Trans Type'), _('#'), _('Ref'), _('Date'), _('Name'), _(''), _('Tax Group'), _('Net'));
	$aligns = array('left', 'center', 'left', 'left', 'left', 'left', 'left', 'right');
        
        if ($orientation == 'L') {
            recalculate_cols($cols);
        }

	$rep->Font();
	$rep->Info($params, $cols, $headers, $aligns);  
        $rep->NewPage();
	
        $tax_group_totals_array = array();
        $non_tax_group_ids_array = rep_get_non_tax_group_ids_array($tax_group_totals_array);
        
	$result = rep_get_non_tax_transactions($from_date_, $to_date_, $trans_type, $non_tax_group_ids_array);
            if (print_non_tax_report_no_records_found($rep, $result)) {
                return;
            }

            while ($row = db_fetch($result)) {
                
		if (in_array($row['type'], array(ST_CUSTCREDIT, ST_SUPPINVOICE, ST_JOURNAL))) {
                    $row['net_amount'] *= -1;
		}
		
                $trans_type = $row['type'];
                $net_amount = $row['net_amount'];
                
                $rep->TextCol(0, 1, $systypes_array[$trans_type]);
                
                if (in_array($trans_type, $customer_debit_types_array) || in_array($trans_type, $supplier_debit_types_array) 
                    || in_array($trans_type, array(ST_BANKDEPOSIT))) {
                    $net_amount1 = abs($net_amount);
                } elseif (in_array($trans_type, $customer_credit_types_array) || in_array($trans_type, $supplier_credit_types_array) 
                          || in_array($trans_type, array(ST_BANKPAYMENT))) {
                    $net_amount1 = abs($net_amount) * -1;
                } elseif (in_array($trans_type, array(ST_JOURNAL))) {
                    $net_amount1 = ($net_amount);
                } else {
                    display_error("Unsupported transaction type ".$systypes_array[$trans_type]);
                    return;
                }

                $rep->TextCol(1, 2, $row['trans_no']);
                $rep->TextCol(2, 3, $row['reference']);
                $rep->DateCol(3, 4, $row['tran_date'], true);
                $rep->TextCol(4, 5, $row['name']);                
                $rep->TextCol(6, 7, $tax_group_totals_array[$row['tax_group_id']]['group_name']);                
                $rep->AmountCol(7, 8, $net_amount1, $dec);

                $rep->NewLine();

                if ($rep->row < $rep->bottomMargin + $rep->lineHeight) {
                    $rep->Line($rep->row - 2);
                    $rep->NewPage();
                }
                
                $tax_group_totals_array[$row['tax_group_id']]['group_total'] += $net_amount1;
            }
	
            $rep->NewLine();
            $rep->Line($rep->row + $rep->lineHeight);
            $rep->NewLine();
            
            if ($rep->row < $rep->bottomMargin + ($rep->lineHeight * 5)) {
                $rep->NewPage();
            }
            
            $rep->Font('bold');
                foreach ($tax_group_totals_array as $key => $value) {
                    $rep->TextCol(5, 7, $value['group_name']);                
                    $rep->AmountCol(7, 8, $value['group_total'], $dec);

                    $rep->NewLine(2);
                }
            $rep->Font();

            
	hook_tax_report_done();

	$rep->End();
}

?>