<?php 

function get_edit_gl_transaction_type_details($type_id, $from, $to, $ref='', $trans_no=null) {
    $types_array = array(ST_JOURNAL, ST_BANKPAYMENT, ST_BANKDEPOSIT, ST_CASH_PAYMENT, ST_CASH_RECEIPT, ST_GL_DEBIT, ST_GL_CREDIT);
    
    $sql = "SELECT IF(ISNULL(a.gl_seq),0,a.gl_seq) as gl_seq,
                gl.*,
                refs.reference,
                com.memo_,
                u.real_name,
                bt.gl_trans_type_id bt_gl_trans_type_id,
                pj.gl_trans_type_id jo_gl_trans_type_id,
                gtp_gl.value_name gl_trans_type_name,
                gtp_bt.value_name bt_gl_trans_type_name,
                gtp_jo.value_name jo_gl_trans_type_name 
            FROM ".TB_PREF."gl_trans AS gl
                LEFT JOIN ".TB_PREF."bank_trans bt 
                    ON (gl.type = bt.type AND gl.type_no = bt.trans_no)
                LEFT JOIN ".TB_PREF."pre_journal pj  
                    ON (gl.type = pj.type AND gl.type_no = pj.trans_no) 
                    
                LEFT JOIN ".TB_PREF."list_values gtp_gl   
                    ON gl.gl_trans_type_id = gtp_gl.id 
                LEFT JOIN ".TB_PREF."list_values gtp_bt    
                    ON bt.gl_trans_type_id = gtp_bt.id 
                LEFT JOIN ".TB_PREF."list_values gtp_jo    
                    ON pj.gl_trans_type_id = gtp_jo.id 
                    
                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)
                LEFT JOIN ".TB_PREF."users AS u 
                    ON a.user = u.id
            WHERE gl.tran_date >= '".date2sql($from)."'
                AND gl.tran_date <= '".date2sql($to)."'
                AND gl.amount <> 0";
    
	if (strlen($ref) > 0) {
            $sql .= " AND reference LIKE ".db_escape("%$ref%");
	}
	if ($type_id > -1) {
            $sql .= " AND gl.type = ".db_escape($type_id);
	} else {
            $sql .= " AND gl.type IN (".implode(",", $types_array).")";
        }
        if ($trans_no > 0) {
            $sql .= " AND gl.type_no = ".db_escape($trans_no);
	}
        
	$sql .= " GROUP BY gl.type, gl.type_no";
//	$sql .= " GROUP BY gl.tran_date, a.gl_seq, gl.type, gl.type_no";
        
	return db_query($sql, "get_edit_gl_transaction_type_details");
}

function update_bank_trans_gl_trans_type($type, $type_no, $gl_trans_type_id) {
    $sql = "UPDATE ".TB_PREF."bank_trans 
            SET gl_trans_type_id = ".db_escape($gl_trans_type_id)." 
            WHERE type = ".db_escape($type)." 
                AND trans_no = ".db_escape($type_no);
    
        return db_query($sql, "update_bank_trans_gl_trans_type");
}

function update_pre_journal_gl_trans_type($type, $type_no, $gl_trans_type_id) {
    $sql = "UPDATE ".TB_PREF."pre_journal  
            SET gl_trans_type_id = ".db_escape($gl_trans_type_id)." 
            WHERE type = ".db_escape($type)." 
                AND trans_no = ".db_escape($type_no);
    
        return db_query($sql, "update_pre_journal_gl_trans_type");
}

function update_gl_trans_gl_trans_type($type, $type_no, $gl_trans_type_id) {
    $sql = "UPDATE ".TB_PREF."gl_trans   
            SET gl_trans_type_id = ".db_escape($gl_trans_type_id)." 
            WHERE type = ".db_escape($type)." 
                AND type_no = ".db_escape($type_no);
    
        return db_query($sql, "update_gl_trans_gl_trans_type");
}

function add_to_gl_trans_type_updated_log($type, $type_no, $old_gl_trans_type_id, $new_gl_trans_type_id)
{
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
    $sql = "INSERT INTO ".TB_PREF."gl_trans_type_updated_log (
                    type, type_no, 
                    old_gl_trans_type_id, new_gl_trans_type_id,
                    updated_by, updated_on)
            VALUES (".db_escape($type).", ".db_escape($type_no).", "
                    .db_escape($old_gl_trans_type_id).", ".db_escape($new_gl_trans_type_id).", "
                    .db_escape($updated_by).", ".db_escape($updated_on).")";

	return db_query($sql);
}