<?php
/**********************************************************************
***********************************************************************/




function get_sql_for_financial_integration_inquiry($filter, $batch_no, $trans_no, $from, $to)
{
    $sql = "SELECT ft.batch_no, ft.type, ft.type_no, ft.tran_date 

            FROM ".TB_PREF."fi_batch_transactions ft ";

    if ($trans_no > 0) {
            $sql .= " WHERE ft.type_no=".db_escape($trans_no);
    }else{
            $sql .= " WHERE ft.tran_date >=".db_escape(date2sql($from))." AND ft.tran_date <=".db_escape(date2sql($to));           

            if ($batch_no > 0) {
                    $sql .= " AND ft.batch_no=".db_escape($batch_no);
            }
            if ($filter != -1) {
                    $sql .= " AND ft.type=".db_escape($filter);
            }
//    if ($account_no > 0) {
//            $sql .= " AND ft.account=".db_escape($account_no);
//    }
    }
    
    $sql .= " ORDER BY ft.batch_no, ft.tran_date, ft.type, ft.type_no ";
        
    return $sql;
}


function get_sql_for_fi_batches_inquiry($filter, $batch_no, $from, $to, $status)
{
    $sql = "SELECT DISTINCT fb.id batch_no, fb.cutoff_date, fb.created_by, fb.created_on, fb.status, fb.gl_batch_no,  
                    cu.real_name created_user,
                    gb.created_by processed_by, gb.created_on processed_on, gb.gl_batch_type, 
                    pu.real_name processed_user,
                    fb.trans_type batch_trans_type 
            FROM ".TB_PREF."fi_batches fb 
                JOIN ".TB_PREF."users cu 
                    ON cu.id = fb.created_by 
                LEFT JOIN ".TB_PREF."gl_batches gb 
                    ON gb.id = fb.gl_batch_no 
                LEFT JOIN ".TB_PREF."users pu 
                    ON pu.id = gb.created_by ";
    
    if ($batch_no > 0) {
            $sql .= " WHERE fb.id=".db_escape($batch_no);
    }else{     
        
        $sql .= " WHERE fb.cutoff_date >=".db_escape(date2sql($from))." AND fb.cutoff_date <=".db_escape(date2sql($to));

        if ($filter > 0) {
                $sql .= " AND fb.trans_type=".db_escape($filter);
        }
        
        if ($status == AS_POSTED) {
            $sql .= " AND fb.status = ".db_escape($status);
        } elseif ($status == 'NOT_PST') {
            $sql .= " AND fb.status <> ".db_escape(AS_POSTED);
        }
    }

    $sql .= " ORDER BY fb.id ";
        
    return $sql;
}

function get_sql_for_gl_batches_inquiry($user_id, $batch_no, $filterType, $from, $to)
{
    $sql = "SELECT gb.id batch_no, gb.tran_date, gb.created_by, gb.created_on,   
                    cu.real_name created_user, gb.deleted_by, gb.gl_batch_type 
            FROM ".TB_PREF."gl_batches gb 
                JOIN ".TB_PREF."users cu 
                    ON cu.id = gb.created_by  

            WHERE gb.tran_date >=".db_escape(date2sql($from))." AND gb.tran_date <=".db_escape(date2sql($to));

    if ($batch_no > 0) {
            $sql .= " AND gb.id=".db_escape($batch_no);
    }
    
    if ($filterType > 0) {
            $sql .= " AND gb.gl_batch_type=".db_escape($filterType);
    }
    
    if ($user_id > 0) {
            $sql .= " AND gb.created_by=".db_escape($user_id);
    }
    
//    if ($account_no > 0) {
//            $sql .= " AND ft.account=".db_escape($account_no);
//    }

    $sql .= " ORDER BY gb.id ";
        
    return $sql;
}


function get_gl_batch_record($batch_no)
{
    $sql = "SELECT gb.* 
            FROM ".TB_PREF."gl_batches gb  
            WHERE gb.id =".db_escape($batch_no);
    
    $result = db_query($sql, "get_gl_batch_record");
    
    return db_fetch($result);
    
}

function view_fi_batches($batch_no)
{
    $sql = "SELECT ft.*,
                    cm.account_name,
                    cm.account_code,        
                    pm.project_code 
            FROM ".TB_PREF."fi_trans ft
                LEFT JOIN ".TB_PREF."chart_master cm ON
                    ft.account = cm.account_code
                LEFT JOIN ".TB_PREF."projects_master pm ON
                    pm.project_id = ft.dimension_id 
                
            WHERE ft.batch_no=".db_escape($batch_no)." 
            ORDER BY ft.tran_date, ft.type, ft.type_no, ft.counter ";
    
    return db_query($sql);
}

function view_fi_batch_summary($batch_no)
{
    $sql = "SELECT SUM(ft.amount) account_sum,
                    SUM(IF(ft.amount > 0, ft.amount, 0)) AS debits_sum,
                    SUM(IF(ft.amount < 0, ft.amount, 0)) AS credits_sum,
                    ft.type,
                    cm.account_name,
                    cm.account_code        

            FROM ".TB_PREF."fi_trans ft
                LEFT JOIN ".TB_PREF."chart_master cm ON
                    ft.account = cm.account_code
                
            WHERE ft.batch_no=".db_escape($batch_no)." 
            GROUP BY ft.type, ft.account ";
    
    return db_query($sql);
}


function view_fi_batch_header($batch_no)
{
    $sql = "SELECT fib.*,
                    us.real_name
        
            FROM ".TB_PREF."fi_batches fib
                JOIN ".TB_PREF."users us ON
                    fib.created_by = us.id
                                  
            WHERE fib.id=".db_escape($batch_no);
    
    $result = db_query($sql);
    return db_fetch($result);
}
 

function get_fi_trans($type, $trans_id)
{
	$sql = "SELECT gl.*,
                    cm.account_name 
            

		FROM ".TB_PREF."fi_trans as gl
		LEFT JOIN ".TB_PREF."chart_master as cm ON gl.account = cm.account_code"
               
                
		." WHERE gl.type= ".db_escape($type) 
		." AND gl.type_no = ".db_escape($trans_id)
		." AND gl.amount <> 0"
		." ORDER BY counter";
	return db_query($sql, "The gl transactions could not be retrieved");
}


function get_created_by_name($batch_no)
{
    $sql = "SELECT fib.*,
                    us.real_name 

            FROM ".TB_PREF."fi_batches fib
                LEFT JOIN ".TB_PREF."users us ON
                    fib.created_by = us.id
            WHERE fib.id=".db_escape($batch_no);
    
    $result = db_query($sql);
    return db_fetch($result);

}


 

function view_gl_batch_header($batch_no)
{
    $sql = "SELECT glb.*,
                    cu.real_name created_user_name,         
                    du.real_name deleted_user_name          
            FROM ".TB_PREF."gl_batches glb
                LEFT JOIN ".TB_PREF."users cu ON
                    glb.created_by = cu.id
                LEFT JOIN ".TB_PREF."users du ON
                    glb.deleted_by = du.id
                                  
            WHERE glb.id=".db_escape($batch_no);
    
    $result = db_query($sql);
    return db_fetch($result);
}
 

function view_gl_batch_details($gl_batch_no)
{
    $sql = "SELECT fib.*,
                    us.real_name      

            FROM ".TB_PREF."fi_batches fib 
                JOIN ".TB_PREF."users us ON
                    fib.created_by = us.id
                
            WHERE fib.gl_batch_no=".db_escape($gl_batch_no);
    
    return db_query($sql, "view_gl_batch_details");
}

//--------------------------------------------------------------------------------

function get_fi_account_transactions($from_date, $to_date, $trans_no=0, $account=null, 
        $filter_type=null, $amount_min=null, $amount_max=null) 
{
	global $show_voided_gl_trans;
	
	$from = date2sql($from_date);
	$to = date2sql($to_date);
        

	$sql = "SELECT gt.*, 
		cm.account_name 
                
                FROM ".TB_PREF."fi_trans gt 
                    JOIN ".TB_PREF."chart_master cm 
                        ON cm.account_code=gt.account 
                WHERE 1 = 1 ";
	
        if ($account != null){
		$sql .= " AND gt.account = ".db_escape($account);
        }
        
                $sql .= " AND tran_date >= '$from'
                            AND tran_date <= '$to'";
        
                if ($trans_no > 0){
                        $sql .= " AND gt.type_no LIKE ".db_escape('%'.$trans_no);
                } 
                
//                if (isset($show_voided_gl_trans) && $show_voided_gl_trans == 0)
//                        $sql .= " AND gt.amount <> 0"; 	


                if ($filter_type > -1){
                        $sql .= " AND gt.type= ".db_escape($filter_type);
                }

                if ($amount_min != null){
                        $sql .= " AND ABS(gt.amount) >= ABS(".db_escape($amount_min).")";
                }

                if ($amount_max != null){
                        $sql .= " AND ABS(gt.amount) <= ABS(".db_escape($amount_max).")";
                }
        

	$sql .= " ORDER BY tran_date, counter";

	return db_query($sql, "The transactions for could not be retrieved");
}
