<?php
function add_fi_trans($batch_no, $type, $trans_id, $detail_line_id, $date_, $account, $dimension, $dimension2, $memo_,
	$amount, $currency=null, $person_type_id=null, $person_id=null,	$err_msg="", $rate=0)
{
        if(!(strlen($account) > 1)){
                $account = get_company_pref('fi_balances_act');
                $memo_ = "Empty account code";
        }

	global $use_audit_trail;

	$date = date2sql($date_);
	if ($currency != null)
	{
		if ($rate == 0){
			$amount_in_home_currency = to_home_currency($amount, $currency, $date_, true);
                }
		else{
			$amount_in_home_currency = round2($amount * $rate,  user_price_dec());
                }
	}		
	else{
		$amount_in_home_currency = round2($amount, user_price_dec());
                $currency = get_company_pref('curr_default');
                $rate = 1;
        }
        
	if ($dimension == null || $dimension < 0){
		$dimension = 0;
        }
        
	if ($dimension2 == null || $dimension2 < 0){
		$dimension2 = 0;
        }
        
	if (isset($use_audit_trail) && $use_audit_trail)
	{
		if ($memo_ == "" || $memo_ == null){
			$memo_ = $_SESSION["wa_current_user"]->username;
                }
		else{
			$memo_ = $_SESSION["wa_current_user"]->username . " - " . $memo_;
                }
	}
        
	$sql = "INSERT INTO ".TB_PREF."fi_trans (batch_no, type, 
                                        type_no, detail_line_id, tran_date,
                                        account, dimension_id, 
                                        dimension2_id, memo_, 
                                        amount, 
                                        source_curr_code, ex_rate";

	if ($person_type_id != null){
		$sql .= ", person_type_id, person_id";
        }

	$sql .= ") ";

	$sql .= "VALUES (".db_escape($batch_no).", ".db_escape($type).", "
                .db_escape($trans_id).", ".db_escape($detail_line_id).", '$date',"
                .db_escape($account).", ".db_escape($dimension).", "
		.db_escape($dimension2).", ".db_escape($memo_).", "
		.db_escape($amount_in_home_currency).", "
		.db_escape($currency).", ".db_escape($rate);

	if ($person_type_id != null){
		$sql .= ", ".db_escape($person_type_id).", ". db_escape($person_id);
        }

	$sql .= ") ";

	if ($err_msg == ""){
		$err_msg = "The FI transaction could not be inserted";
        }

	db_query($sql, $err_msg);
	return $amount_in_home_currency;
}


function update_source_transaction_fi_batch_no($tran_type, $trans_no, $fi_batch_no)
{
    switch ($tran_type) {
        // Inventory
        case ST_MATERIAL_ISSUE:
            $sql = "UPDATE ".TB_PREF."issue_materials SET fi_batch_no =".db_escape($fi_batch_no)." WHERE im_no = ".db_escape($trans_no)." AND fi_batch_no = 0";
            break;
        case ST_MATERIAL_RETURN:
            $sql = "UPDATE ".TB_PREF."return_materials SET fi_batch_no =".db_escape($fi_batch_no)." WHERE rm_no = ".db_escape($trans_no)." AND fi_batch_no = 0";
            break;
        case ST_SITE_MATERIAL_RETURN:
            $sql = "UPDATE ".TB_PREF."site_material_returns SET fi_batch_no =".db_escape($fi_batch_no)." WHERE id = ".db_escape($trans_no)." AND fi_batch_no = 0";
            break;
        case ST_INVENTORY_RECEIPT:
        case ST_INVENTORY_ISSUE:
        case ST_INVADJUST:
            $sql = "UPDATE ".TB_PREF."stock_adjustments SET fi_batch_no =".db_escape($fi_batch_no)." WHERE type = ".db_escape($tran_type)." AND id = ".db_escape($trans_no)." AND fi_batch_no = 0";
            break;
        case ST_STOCK_COUNT:
            $sql = "UPDATE ".TB_PREF."stock_counts SET fi_batch_no =".db_escape($fi_batch_no)." WHERE stock_count_no = ".db_escape($trans_no)." AND fi_batch_no = 0";
            break;
        case ST_ITEM_COSTUPDATE:
            $sql = "UPDATE ".TB_PREF."stock_item_cost_amendments SET fi_batch_no =".db_escape($fi_batch_no)." WHERE trans_type = ".db_escape($tran_type)." AND trans_no = ".db_escape($trans_no)." AND fi_batch_no = 0";
            break;
        case ST_DAILY_ITEM_ISSUE:
        case ST_DAILY_ITEM_RETURN:
            $sql = "UPDATE ".TB_PREF."daily_item_issues SET fi_batch_no =".db_escape($fi_batch_no)." WHERE type = ".db_escape($tran_type)." AND trans_no = ".db_escape($trans_no)." AND fi_batch_no = 0";
            break;        
        case ST_PETTY_CASH_ITEM_RECEIPT:
            $sql = "UPDATE ".TB_PREF."petty_cash_stock_item_receipt_details SET fi_batch_no =".db_escape($fi_batch_no)." WHERE type = ".db_escape($tran_type)." AND trans_no = ".db_escape($trans_no)." AND fi_batch_no = 0";
            break;
        
        
        // GL
        case ST_UTILITY_BILLS:
            $sql = "UPDATE ".TB_PREF."utility_bills SET fi_batch_no =".db_escape($fi_batch_no)." WHERE id = ".db_escape($trans_no)." AND fi_batch_no = 0";
            break;
        case ST_UTILITY_BILL_CREDIT_NOTE:
            $sql = "UPDATE ".TB_PREF."utility_bill_credit_notes SET fi_batch_no =".db_escape($fi_batch_no)." WHERE id = ".db_escape($trans_no)." AND fi_batch_no = 0";
            break;
        
        //Projects
        case ST_PROJECT_INVOICE:
            $sql = "UPDATE ".TB_PREF."project_invoices SET fi_batch_no =".db_escape($fi_batch_no)." WHERE order_no = ".db_escape($trans_no)." AND fi_batch_no = 0";
            break;
        case ST_PROJECT_CREDIT_NOTE:
            $sql = "UPDATE ".TB_PREF."project_credit_notes SET fi_batch_no =".db_escape($fi_batch_no)." WHERE order_no = ".db_escape($trans_no)." AND fi_batch_no = 0";
            break;
        case ST_SUBCONTRACTOR_INVOICE:
            $sql = "UPDATE ".TB_PREF."subcontractor_invoices SET fi_batch_no =".db_escape($fi_batch_no)." WHERE order_no = ".db_escape($trans_no)." AND fi_batch_no = 0";
            break;
        case ST_SUBCONTRACTOR_CREDIT_NOTE:
            $sql = "UPDATE ".TB_PREF."subcontractor_credit_notes SET fi_batch_no =".db_escape($fi_batch_no)." WHERE order_no = ".db_escape($trans_no)." AND fi_batch_no = 0";
            break;
        case ST_MISCELLANEOUS_PROJECT_COST:
            $sql = "UPDATE ".TB_PREF."miscellaneous_project_costs SET fi_batch_no =".db_escape($fi_batch_no)." WHERE id = ".db_escape($trans_no)." AND fi_batch_no = 0";
            break;
        
        
        // Sales
        case ST_SALESINVOICE:
        case ST_CUSTDELIVERY:
        case ST_CUSTOMER_PAYMENT_RETURN:
        case ST_CUSTCREDIT:
        case ST_CUSTPAYMENT:
            $sql = "UPDATE ".TB_PREF."debtor_trans SET fi_batch_no =".db_escape($fi_batch_no)." WHERE type = ".db_escape($tran_type)." AND trans_no = ".db_escape($trans_no)." AND fi_batch_no = 0";
            break;
        case ST_PR_ISSUE:
            $sql = "UPDATE ".TB_PREF."purchase_requisition_issues SET fi_batch_no =".db_escape($fi_batch_no)." WHERE pri_no = ".db_escape($trans_no)." AND fi_batch_no = 0";
            break;
        case ST_PR_RETURN:
            $sql = "UPDATE ".TB_PREF."purchase_requisition_returns SET fi_batch_no =".db_escape($fi_batch_no)." WHERE prr_no = ".db_escape($trans_no)." AND fi_batch_no = 0";
            break;
        
        // Purchasing
        case ST_SUPPRECEIVE:
            $sql = "UPDATE ".TB_PREF."grn_batch SET fi_batch_no =".db_escape($fi_batch_no)." WHERE id = ".db_escape($trans_no)." AND fi_batch_no = 0";
            break;
        case ST_SUPPINVOICE:
        case ST_SUPPCREDIT:
        case ST_SUPPAYMENT:
            $sql = "UPDATE ".TB_PREF."supp_trans SET fi_batch_no =".db_escape($fi_batch_no)." WHERE type = ".db_escape($tran_type)." AND trans_no = ".db_escape($trans_no)." AND fi_batch_no = 0";
            break;   
        
        // Petty Cash
        case ST_PETTY_CASH_FLOAT_ADJ:
            $sql = "UPDATE ".TB_PREF."petty_cash_float_adjustments SET fi_batch_no =".db_escape($fi_batch_no)." WHERE id = ".db_escape($trans_no)." AND fi_batch_no = 0";
            break;
        case ST_PETTY_CASH_FLOAT_REIMBUR:
            $sql = "UPDATE ".TB_PREF."i_owe_you_float_reimbursements SET fi_batch_no =".db_escape($fi_batch_no)." WHERE id = ".db_escape($trans_no)." AND fi_batch_no = 0";
            break;
        case ST_I_OWE_YOU_BATCH:
            $sql = "UPDATE ".TB_PREF."i_owe_you_batches SET fi_batch_no =".db_escape($fi_batch_no)." WHERE batch_no = ".db_escape($trans_no)." AND fi_batch_no = 0";
            break;
        case ST_PETTY_CASH_DIRECT_PAYMENTS:
        case ST_I_OWE_YOU_PAYMENT:
        case ST_PETTY_CASH_PAYMENT:
            $sql = "UPDATE ".TB_PREF."i_owe_you_payments SET fi_batch_no =".db_escape($fi_batch_no)." WHERE id = ".db_escape($trans_no)." AND fi_batch_no = 0";
            break;
        case ST_I_OWE_YOU_ADVANCES:
            $sql = "UPDATE ".TB_PREF."i_owe_you_advances_details SET fi_batch_no =".db_escape($fi_batch_no)." WHERE id = ".db_escape($trans_no)." AND fi_batch_no = 0";
            break;
        
        
        // Transport
        case ST_SUPPLIER_TRANSPORT_INVOICES:
            $sql = "UPDATE ".TB_PREF."supp_trans SET fi_batch_no =".db_escape($fi_batch_no)." WHERE type = ".db_escape($tran_type)." AND trans_no = ".db_escape($trans_no)." AND fi_batch_no = 0";
            break;
        
        // Finance
        case ST_COMP_LOAN_REPAYMENT:
            $sql = "UPDATE ".TB_PREF."company_loan_payments SET fi_batch_no =".db_escape($fi_batch_no)." WHERE id = ".db_escape($trans_no)." AND fi_batch_no = 0";
            break;
        
        // Assets
        case ST_ASSET_QUANTITY_ADJUSTMENT:
            $sql = "UPDATE ".TB_PREF."asset_quantity_adjustments SET fi_batch_no =".db_escape($fi_batch_no)." WHERE id = ".db_escape($trans_no)." AND fi_batch_no = 0";
            break;
        case ST_STOCK_ASSET_TRANSFER:
            $sql = "UPDATE ".TB_PREF."stock_asset_transfer_header SET fi_batch_no =".db_escape($fi_batch_no)." WHERE id = ".db_escape($trans_no)." AND fi_batch_no = 0";
            break;
        
        default:
            break;
    }
    
    db_query($sql, "could not update_source_transaction_fi_batch_no");
    
    return db_num_affected_rows();
    
}


function update_group_company_source_transaction_fi_batch_no($db_name, $tran_type, $trans_no, $fi_batch_no)
{

    $sql = "UPDATE ".$db_name.".".TB_PREF."payroll_runs SET fi_batch_no = ".db_escape($fi_batch_no)." WHERE id = ".db_escape($trans_no)." AND fi_batch_no = 0";
            
    db_query($sql, "update_group_compant_source_transaction_fi_batch_no");
    
    return db_num_affected_rows();
}



function create_next_fi_batch_no($cutoff_date_, $group_id, $trans_type)
{
    $id_user = $_SESSION['wa_current_user']->user;
    $datetime = date('Y-m-d H:i:s');
    
    $sql = "INSERT INTO ".TB_PREF."fi_batches(
                    cutoff_date, group_id, trans_type, 
                    created_by, created_on)

            VALUES (".db_escape(date2sql($cutoff_date_)).", ".db_escape($group_id).", ".db_escape($trans_type).", ".
                        db_escape($id_user).", ".db_escape($datetime).") ";

    db_query($sql, "Could not create_next_fi_batch_no");

    return db_insert_id();
}

function add_fi_batch_transaction($batch_no, $trans_type, $trans_no, $tran_date, $memo="", $db_name="")
{
    $sql = "INSERT INTO ".TB_PREF."fi_batch_transactions(
                        batch_no, type, 
                        type_no, tran_date, 
                        db_name, memo)
            VALUES (".db_escape($batch_no).", ".db_escape($trans_type).", "
                        .db_escape($trans_no).", ".db_escape($tran_date).", "
                        .db_escape($db_name).", ".db_escape($memo).") ";
    
    db_query($sql, "add_fi_batch_detail");
}

function reopen_fi_batch_source_transactions($fi_batch_no)
{

//============Inventory
        //case ST_MATERIAL_ISSUE:
            $sql = "UPDATE ".TB_PREF."issue_materials SET fi_batch_no = 0 WHERE fi_batch_no = ".db_escape($fi_batch_no);
            db_query($sql, "reopen_fi_source_transaction");

        //case ST_MATERIAL_RETURN:
            $sql = "UPDATE ".TB_PREF."return_materials SET fi_batch_no = 0 WHERE fi_batch_no = ".db_escape($fi_batch_no);
            db_query($sql, "reopen_fi_source_transaction");
   
        //case ST_SITE_MATERIAL_RETURN:
            $sql = "UPDATE ".TB_PREF."site_material_returns SET fi_batch_no = 0 WHERE fi_batch_no = ".db_escape($fi_batch_no);
            db_query($sql, "reopen_fi_source_transaction");
            
        //case ST_INVENTORY_RECEIPT:
        //case ST_INVENTORY_ISSUE:
        //case ST_INVADJUST:
            $sql = "UPDATE ".TB_PREF."stock_adjustments SET fi_batch_no = 0 WHERE fi_batch_no = ".db_escape($fi_batch_no);
            db_query($sql, "reopen_fi_source_transaction");
            
        //case ST_STOCK_COUNT:
            $sql = "UPDATE ".TB_PREF."stock_counts SET fi_batch_no = 0 WHERE fi_batch_no = ".db_escape($fi_batch_no);
            db_query($sql, "reopen_fi_source_transaction ST_STOCK_COUNT");
            
        //case ST_ITEM_COSTUPDATE:
            $sql = "UPDATE ".TB_PREF."stock_item_cost_amendments SET fi_batch_no = 0 WHERE fi_batch_no = ".db_escape($fi_batch_no);
            db_query($sql, "reopen_fi_source_transaction ST_ITEM_COSTUPDATE");
            
        //case ST_DAILY_ITEM_ISSUE:
            $sql = "UPDATE ".TB_PREF."daily_item_issues SET fi_batch_no = 0 WHERE fi_batch_no = ".db_escape($fi_batch_no);
            db_query($sql, "reopen_fi_source_transaction ST_DAILY_ITEM_ISSUE");            
            
        //case ST_PETTY_CASH_ITEM_RECEIPT:
            $sql = "UPDATE ".TB_PREF."petty_cash_stock_item_receipt_details SET fi_batch_no = 0 WHERE fi_batch_no = ".db_escape($fi_batch_no);
            db_query($sql, "reopen_fi_source_transaction 0_petty_cash_stock_item_receipt_details");
            
        
//==========GL        
        //case ST_UTILITY_BILLS:
            $sql = "UPDATE ".TB_PREF."utility_bills SET fi_batch_no = 0 WHERE fi_batch_no = ".db_escape($fi_batch_no);
            db_query($sql, "reopen_fi_source_transaction");
            
        //case ST_UTILITY_BILL_CREDIT_NOTE:
            $sql = "UPDATE ".TB_PREF."utility_bill_credit_notes SET fi_batch_no = 0 WHERE fi_batch_no = ".db_escape($fi_batch_no);
            db_query($sql, "reopen_fi_source_transaction");
            
        
        
//        case ST_PROJECT_INVOICE:
            $sql = "UPDATE ".TB_PREF."project_invoices SET fi_batch_no = 0 WHERE fi_batch_no = ".db_escape($fi_batch_no);
            db_query($sql, "reopen_fi_source_transaction");
            
//        case ST_PROJECT_CREDIT_NOTE:
            $sql = "UPDATE ".TB_PREF."project_credit_notes SET fi_batch_no = 0 WHERE fi_batch_no = ".db_escape($fi_batch_no);
            db_query($sql, "reopen_fi_source_transaction");
            
//        case ST_SUBCONTRACTOR_INVOICE:
            $sql = "UPDATE ".TB_PREF."subcontractor_invoices SET fi_batch_no = 0 WHERE fi_batch_no = ".db_escape($fi_batch_no);
            db_query($sql, "reopen_fi_source_transaction");
            
//        case ST_SUBCONTRACTOR_CREDIT_NOTE:
            $sql = "UPDATE ".TB_PREF."subcontractor_credit_notes SET fi_batch_no = 0 WHERE fi_batch_no = ".db_escape($fi_batch_no);
            db_query($sql, "reopen_fi_source_transaction");
            
//        case ST_MISCELLANEOUS_PROJECT_COST:
            $sql = "UPDATE ".TB_PREF."miscellaneous_project_costs SET fi_batch_no = 0 WHERE fi_batch_no = ".db_escape($fi_batch_no);
            db_query($sql, "reopen_fi_source_transaction");          
        
        
        
//        case ST_SALESINVOICE:
//        case ST_CUSTDELIVERY:
//        case ST_CUSTOMER_PAYMENT_RETURN:
//        case ST_CUSTCREDIT:
//        case ST_CUSTPAYMENT:
            $sql = "UPDATE ".TB_PREF."debtor_trans SET fi_batch_no = 0 WHERE fi_batch_no = ".db_escape($fi_batch_no);
            db_query($sql, "reopen_fi_source_transaction"); 
        
        
//        case ST_SUPPRECEIVE:
            $sql = "UPDATE ".TB_PREF."grn_batch SET fi_batch_no = 0 WHERE fi_batch_no = ".db_escape($fi_batch_no);
            db_query($sql, "reopen_fi_source_transaction"); 
            
//        case ST_SUPPINVOICE:
//        case ST_SUPPCREDIT:
//        case ST_SUPPAYMENT:
            $sql = "UPDATE ".TB_PREF."supp_trans SET fi_batch_no = 0 WHERE fi_batch_no = ".db_escape($fi_batch_no);
            db_query($sql, "reopen_fi_source_transaction");    
//        case ST_PR_ISSUE:
            $sql = "UPDATE ".TB_PREF."purchase_requisition_issues SET fi_batch_no = 0 WHERE fi_batch_no = ".db_escape($fi_batch_no);
            db_query($sql, "reopen_fi_source_transaction");
        
//        case ST_PETTY_CASH_FLOAT_ADJ:
            $sql = "UPDATE ".TB_PREF."petty_cash_float_adjustments SET fi_batch_no = 0 WHERE fi_batch_no = ".db_escape($fi_batch_no);
            db_query($sql, "reopen_fi_source_transaction"); 
            
//        case ST_PETTY_CASH_FLOAT_REIMBUR:
            $sql = "UPDATE ".TB_PREF."i_owe_you_float_reimbursements SET fi_batch_no = 0 WHERE fi_batch_no = ".db_escape($fi_batch_no);
            db_query($sql, "reopen_fi_source_transaction"); 
            
//        case ST_I_OWE_YOU_BATCH:
            $sql = "UPDATE ".TB_PREF."i_owe_you_batches SET fi_batch_no = 0 WHERE fi_batch_no = ".db_escape($fi_batch_no);
            db_query($sql, "reopen_fi_source_transaction"); 
            
//        case ST_PETTY_CASH_DIRECT_PAYMENTS:
//        case ST_I_OWE_YOU_PAYMENT:
//        case ST_PETTY_CASH_PAYMENT:
            $sql = "UPDATE ".TB_PREF."i_owe_you_payments SET fi_batch_no = 0 WHERE fi_batch_no = ".db_escape($fi_batch_no);
            db_query($sql, "reopen_fi_source_transaction"); 
            
//        case ST_I_OWE_YOU_ADVANCES:        
            $sql = "UPDATE ".TB_PREF."i_owe_you_advances_details SET fi_batch_no = 0 WHERE fi_batch_no = ".db_escape($fi_batch_no);
            db_query($sql, "reopen_fi_source_transaction"); 
            
//        case ST_SUPPLIER_TRANSPORT_INVOICES:
            $sql = "UPDATE ".TB_PREF."supp_trans SET fi_batch_no = 0 WHERE fi_batch_no = ".db_escape($fi_batch_no);
            db_query($sql, "reopen_fi_source_transaction"); 
            
        
        
//        case ST_COMP_LOAN_REPAYMENT:
            $sql = "UPDATE ".TB_PREF."company_loan_payments SET fi_batch_no = 0 WHERE fi_batch_no = ".db_escape($fi_batch_no);
            db_query($sql, "reopen_fi_source_transaction"); 

            
//========Assets============
//        case ST_ASSET_QUANTITY_ADJUSTMENT:
            $sql = "UPDATE ".TB_PREF."asset_quantity_adjustments SET fi_batch_no = 0 WHERE fi_batch_no = ".db_escape($fi_batch_no);
            db_query($sql, "reopen_fi_source_transaction"); 
            
//        case ST_STOCK_ASSET_TRANSFER:
            $sql = "UPDATE ".TB_PREF."stock_asset_transfer_header SET fi_batch_no = 0 WHERE fi_batch_no = ".db_escape($fi_batch_no);
            db_query($sql, "reopen_fi_source_transaction"); 
            
}


function reopen_group_company_source_transactions($db_name, $fi_batch_no)
{

    $sql = "UPDATE ".$db_name.".".TB_PREF."payroll_runs SET fi_batch_no = 0 WHERE fi_batch_no = ".db_escape($fi_batch_no);            
    db_query($sql, "reopen_group_company_source_transactions");
    
}



function get_fi_batch_header_record($fi_batch_no)
{
    $sql = "SELECT * FROM ".TB_PREF."fi_batches WHERE id = ".db_escape($fi_batch_no);
    
    $result = db_query($sql, "get_fi_batch_header_record");
    return db_fetch($result);
    
}

function delete_fi_accounting_entries($batch_no)
{
    $sql = "DELETE FROM ".TB_PREF."fi_trans 
            WHERE batch_no = ".db_escape($batch_no);
    
    db_query($sql, "delete_fi_accounting_entries");
}

function get_fi_batch_processed_entries_count($batch_no)
{
    $sql = "SELECT COUNT(counter) entries_count 
            FROM ".TB_PREF."fi_trans 
            WHERE batch_no = ".db_escape($batch_no)." 
                AND gl_counter > 0 ";
    
    $result = db_query($sql, "delete_fi_accounting_entries");
    
    return db_result_value($result, 'entries_count');
}

function get_fi_accounting_entries($batch_no)
{
    $sql = "DELETE FROM ".TB_PREF."fi_trans WHERE batch_no = ".db_escape($batch_no);
    
    db_query($sql, "delete_fi_accounting_entries");
}

function delete_fi_batch_transactions($batch_no)
{
    $sql = "DELETE FROM ".TB_PREF."fi_batch_transactions WHERE batch_no = ".db_escape($batch_no);
    
    db_query($sql, "delete_fi_batch_transactions");
}

function delete_fi_batch_header_record($batch_no, $row_state)
{
    $sql = "DELETE FROM ".TB_PREF."fi_batches  
            WHERE id = ".db_escape($batch_no)."  
                AND row_state = ".db_escape($row_state);
    
    db_query($sql, "delete_fi_batch_header_record");
    
    return db_num_affected_rows();
}

function get_unposted_fi_batches($tran_date)
{
    $sql = "SELECT fi.*,
                u.real_name user_name 
            FROM ".TB_PREF."fi_batches fi 
                JOIN ".TB_PREF."users u 
                    ON u.id = fi.created_by 
            WHERE fi.status = ".db_escape(AS_NEW)."  
                    AND fi.gl_batch_no = 0 
                    AND fi.cutoff_date <= ".db_escape($tran_date)." 
            ORDER BY fi.trans_type, fi.cutoff_date, fi.id";
    
    return db_query($sql, "get_unposted_fi_batches");
}

function get_fi_batch_transaction_count($fi_batch_no)
{
    $sql = "SELECT COUNT(id) tran_count FROM ".TB_PREF."fi_batch_transactions  

            WHERE batch_no = ".db_escape($fi_batch_no);
    
    $result = db_query($sql, "get_fi_batch_transaction_count");
    
    return db_result_value($result, 'tran_count');
    
}

function get_fi_batch_transactions($fi_batch_no)
{
    $sql = "SELECT * FROM ".TB_PREF."fi_batch_transactions  

            WHERE batch_no = ".db_escape($fi_batch_no);
    
    return db_query($sql, "get_fi_batch_transactions"); 
    
}

function get_fi_batch_transaction_record($batch_no, $type, $type_no)
{
    $sql = "SELECT * FROM ".TB_PREF."fi_batch_transactions  

            WHERE batch_no = ".db_escape($batch_no)." 
                AND type = ".db_escape($type)." 
                AND type_no = ".db_escape($type_no);
    
    $result = db_query($sql, "get_fi_batch_transaction_record");
    
    return db_fetch($result);
    
}

function get_fi_batch_total($fi_batch_no)
{
    $sql = "SELECT SUM(amount) total FROM ".TB_PREF."fi_trans   

            WHERE batch_no = ".db_escape($fi_batch_no)." 
                AND amount > 0" ;
    
    $result = db_query($sql, "get_fi_batch_total");
    
    return db_result_value($result, 'total');
    
}



function get_unposted_fi_accounts_summary($fi_batches)
{
    $sql = "SELECT ft.account, cm.account_name, SUM(ft.amount) total, COUNT(ft.counter) records_count, pm.project_id, pm.project_code  
            FROM ".TB_PREF."fi_trans ft  
                JOIN ".TB_PREF."chart_master cm 
                    ON cm.account_code = ft.account 
                LEFT JOIN ".TB_PREF."projects_master pm 
                    ON pm.project_id = ft.dimension_id  
            WHERE ft.batch_no IN (".implode(",", $fi_batches).")  
            GROUP BY ft.account, pm.project_id 
            ORDER BY ft.account, pm.project_code";
    
    return db_query($sql, "get_unposted_fi_accounts_summary");
}


function reopen_fi_batch_header_record($fi_batch_no, $row_state)
{
    $sql = "UPDATE ".TB_PREF."fi_batches  
            SET gl_batch_no = 0,
                status = ". db_escape(AS_NEW).", 
                row_state = ". db_new_row_state($row_state)." 
            WHERE id = ".db_escape($fi_batch_no)." 
                AND gl_batch_no <> 0  
                AND row_state = ".db_escape($row_state);
    
    db_query($sql, "reopen_fi_batch_header_record");
    
    return db_num_affected_rows();
}

function reopen_fi_batch_details($fi_batch_no)
{
    $sql = "UPDATE ".TB_PREF."fi_trans  
            SET gl_counter = 0 
            WHERE batch_no = ".db_escape($fi_batch_no);
    
    db_query($sql, "reopen_fi_batch_header_record");
    
    return db_num_affected_rows();
}

function fi_get_group_summary($group_id)
{
    $sql = "SELECT MIN(id) min_batch_no, MAX(id) max_batch_no, COUNT(id) no_of_batches 
            FROM ".TB_PREF."fi_batches 
            WHERE group_id = ".db_escape($group_id);
    
    $result = db_query($sql, "fi_get_group_summary");
    
    return db_fetch($result);
}

function check_fi_transactions_outside_fiscal_year($begin, $end, $fi_batches)
{
    $sql = "SELECT COUNT(DISTINCT type, type_no) no_of_records 
            FROM ".TB_PREF."fi_trans 
            WHERE batch_no IN (".implode(",", $fi_batches).")  
                AND (tran_date < ".db_escape($begin)." OR tran_date > ".db_escape($end).")";                
    
    $result = db_query($sql, "check_fi_transactions_outside_fiscal_year");
    
    $n = db_result_value($result, "no_of_records");
    
    if($n != 0){
        return array("success"=>false, "message"=> "Selected batches have ".$n." transactions outside the fiscal year of the posting date");
    }else{
        return array("success"=>true, "message"=> "");        
    }
}


function get_fi_transaction($fi_batch_no, $type, $type_no)
{
    $sql = "SELECT * FROM ".TB_PREF."fi_trans   
            WHERE batch_no = ".db_escape($fi_batch_no)." 
                AND type = ".db_escape($type)." 
                AND type_no = ".db_escape($type_no); 
    
    return db_query($sql, "get_fi_transaction"); 
    
}