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


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


function get_bank_trans_for_cheque_printing($bank_account, $from, $to, $printed =0, $type_id=0)
{
	$from = date2sql($from);
	$to = date2sql($to);
    
	$sql = "SELECT cpt.crossing_id , t.*,
                    ba.bank_account_name,
                    ba.bank_account_number,
                    bn.value_name bank_name,
                    crsn.value_name crossing_name 
                    
            FROM ".TB_PREF."bank_trans t 
                    INNER JOIN ".TB_PREF."bank_accounts ba ON 
                            t.bank_act = ba.id 
                    INNER JOIN ".TB_PREF."list_values bn ON 
                            ba.bank_id = bn.id 
                    LEFT JOIN ".TB_PREF."cheque_printing_templates cpt ON 
                            ba.bank_id = cpt.bank_id 
                    LEFT JOIN ".TB_PREF."list_values crsn ON 
                            cpt.crossing_id = crsn.id  
        
            WHERE ba.account_type =  1 
                AND type IN (".db_escape(ST_BANKPAYMENT).",".db_escape(ST_BANKTRANSFER).",".db_escape(ST_SUPPAYMENT).",".db_escape(ST_PETTY_CASH_FLOAT_REIMBUR).") ";
            
            if($type_id > 0){
                $sql .=" AND type=".db_escape($type_id);
            }        
        
//            WHERE t.type IN(".ST_SUPPAYMENT.",".ST_BANKPAYMENT.", ".ST_PETTY_CASH_FLOAT_REIMBUR.") ";
        
            if($bank_account > 0){
                $sql .= "AND t.bank_act = ".db_escape($bank_account);
            }
    
            $sql .= " AND trans_date >= '$from'
                            AND trans_date <= '$to' ";
            
            if($printed == 0){
                $sql .= " AND t.cheque_prints = 0";
            }
            
            $sql .= " ORDER BY trans_date, t.id";

	return db_query($sql,"Could not get_bank_trans_for_cheque_printing");
}


//function get_sql_for_print_cheques($filter, $from, $to, $ref='', $memo='', $alsoclosed=false)
//{
//
//	$sql = "SELECT	IF(ISNULL(a.gl_seq),0,a.gl_seq) as gl_seq,
//		gl.tran_date,
//		gl.type,
//		gl.type_no,
//		refs.reference,
//		SUM(IF(gl.amount>0, gl.amount,0)) as amount,
//		com.memo_ 
//		FROM ".TB_PREF."gl_trans as gl
//                LEFT JOIN ".TB_PREF."audit_trail as a ON
//                        (gl.type=a.type AND gl.type_no=a.trans_no)
//                LEFT JOIN ".TB_PREF."comments as com ON
//                        (gl.type=com.type AND gl.type_no=com.id)
//                LEFT JOIN ".TB_PREF."refs as refs ON
//                        (gl.type=refs.type AND gl.type_no=refs.id) 
//		WHERE gl.tran_date >= '" . date2sql($from) . "'
//                AND gl.tran_date <= '" . date2sql($to) . "'
//                AND gl.amount!=0";
//	if ($ref) {
//		$sql .= " AND reference LIKE ". db_escape("%$ref%");
//	}
//	if ($memo) {
//		$sql .= " AND com.memo_ LIKE ". db_escape("%$memo%");
//	}
//	
//    if ($filter > 0) {
//		$sql .= " AND gl.type=".db_escape($filter);
//	}
//    else{
//        $sql .= " AND gl.type IN (".ST_SUPPAYMENT.",".ST_BANKPAYMENT.")";
//    }
//    
//	if (!$alsoclosed) {
//		$sql .= " AND gl_seq=0";
//	}
//	$sql .= " GROUP BY gl.tran_date, a.gl_seq, gl.type, gl.type_no";
//	return $sql;
//}

function update_cheque_printed($type,$trans_no, $cheque_no)
{
    $sql = "UPDATE ".TB_PREF."bank_trans 
                    SET cheque_prints = cheque_prints + 1,
                    cheque_no = ".db_escape($cheque_no)."  
            WHERE type = ".db_escape($type)." AND 
                    trans_no = ".db_escape($trans_no);

	db_query($sql,"Could not updated_cheque_printed");
}


function add_to_cheque_printing_log($type, $trans_no, $bank_account, $account_name, $crossing_id, $cheque_date, $cheque_no, $manual, $payment_type=0)
{
    $id_user = $_SESSION['wa_current_user']->user;
	$datetime = date('Y-m-d H:i:s');
    
	$sql = "INSERT INTO ".TB_PREF."cheque_printing_log
                    (type, trans_no,  
                    bank_account, account_name, 
                    crossing_id, cheque_date,
                    cheque_no, manual, payment_type, 
                    created_by, created_on) 
			VALUES( ".db_escape($type).",".db_escape($trans_no).","
                    .db_escape($bank_account).",".db_escape($account_name).","
                    .db_escape($crossing_id).",".db_escape($cheque_date).","
                    .db_escape($cheque_no).",".db_escape($manual).",".db_escape($payment_type).","
                    .db_escape($id_user).",".db_escape($datetime).")";
    
    db_query($sql,"Could not add_to_cheque_printing_log");

    return db_insert_id();
}

function get_cheque_printed_date($trans_type, $trans_no, $cheque_no)
{
    $sql = "SELECT * 
            FROM ".TB_PREF."cheque_printing_log
            WHERE type =  ".db_escape($trans_type)." 
                    AND trans_no = ".db_escape($trans_no)." 
                    AND cheque_no = ".db_escape($cheque_no)." 
            ORDER BY id DESC LIMIT 1";
    
    $result = db_query($sql,"get_cheque_printed_date");
    if(db_num_rows($result) > 0){
        $row = db_fetch($result);
        return $row['cheque_date'];
    }
    else{
        return "";
    }
    
}

function get_printed_cheque_details($trans_type, $trans_no)
{
    $sql = "SELECT * 
            FROM ".TB_PREF."bank_trans bt 
                JOIN ".TB_PREF."cheque_printing_log cpl ON 
                    cpl.type = bt.type AND 
                    cpl.trans_no = bt.trans_no AND 
                    cpl.cheque_no = bt.cheque_no  
            WHERE cpl.type =  ".db_escape($trans_type)." 
                    AND cpl.trans_no = ".db_escape($trans_no)." 
            ORDER BY cpl.id DESC LIMIT 1";
    
    $result = db_query($sql,"get_printed_cheque_details");
    return db_fetch($result);
        
}

function can_process_online_payment($cheque_date, $cheque_no) {
    if (! is_date($cheque_date)) {
        return array('success' => false, 'message' => 'Entered date is not a valid date.');
    }
    
    if (! (strlen($cheque_no) > 0)) {
        return array('success' => false, 'message' => 'Online payment reference number cannot be empty.');
    }
    
        return array('success' => true, 'message' => '');
}

function save_online_payment($id) {
    global $systypes_array;
    
    $cheque_date_ = $_POST["cheque_date_$id"];
    $cheque_no = $_POST["cheque_no_$id"];
    
    $msg = can_process_online_payment($cheque_date_, $cheque_no);
        if ($msg['success'] !== true) {
            return $msg;
        }
        
        $br_row = get_bank_transaction_record_by_id($id);
            $type = $br_row['type'];
            $trans_no = $br_row['trans_no'];
            $bank_account = $br_row['bank_act'];
            $bank_acc = get_bank_account($bank_account);
            $account_name = $bank_acc['bank_account_name'];
            $cheque_date = date2sql($cheque_date_);
        
        begin_transaction();
        
            $insert_id = add_to_cheque_printing_log($type, $trans_no, $bank_account, $account_name, 0, $cheque_date, $cheque_no, 0, 1);

                if ($insert_id > 0) {
                    update_cheque_printed($type, $trans_no, $cheque_no);
                    commit_transaction();

                    return array('success' => true, 'message' => "Online transaction reference $cheque_no has been saved for the "
                                                                  .$systypes_array[$type]." #$trans_no.");
                } else {
                    cancel_transaction();

                    return array('success' => false, 'message' => "Online transaction reference $cheque_no could not be saved for the "
                                                                   .$systypes_array[$type]." #$trans_no.");
                }
}

function get_sql_for_cheque_printing_log_inquiry($trans_no, $type, $from_date_, $to_date_, $bank_account, $user_id) {
    $from_date = date2sql($from_date_);
    $to_date = date2sql($to_date_);
    
    $sql = "SELECT lg.*,
                bk.bank_account_number,
                cr.value_name crossing_name,
                u.real_name 
            FROM ".TB_PREF."cheque_printing_log lg 
                JOIN ".TB_PREF."bank_accounts bk   
                    ON lg.bank_account = bk.id 
                LEFT JOIN ".TB_PREF."list_values cr  
                    ON lg.crossing_id = cr.id 
                JOIN ".TB_PREF."users u   
                    ON lg.created_by = u.id 
            WHERE lg.cheque_date >= ".db_escape($from_date)." 
                AND lg.cheque_date <= ".db_escape($to_date);
    
        if ($type > 0) {
            $sql .= " AND lg.type = ".db_escape($type);
        }
        if (strlen($trans_no) > 0) {
            $sql .= " AND lg.trans_no = ".db_escape($trans_no);
        }
        if ($bank_account > 0) {
            $sql .= " AND lg.bank_account = ".db_escape($bank_account);
        }
        if ($user_id > 0) {
            $sql .= " AND lg.created_by = ".db_escape($user_id);
        }
    
        $sql .= " ORDER BY lg.id";
        
            return $sql;
}

?>