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

function add_salary_advance($employee_code, $type_id,
                            $salary_advance, 
                            $due_date, $comments, $status_code)
{
        $created_by = $_SESSION['wa_current_user']->user;
        $created_on = date('Y-m-d H:i:s');
        
        
	$sql = "INSERT INTO ".TB_PREF."salary_advances (
                    employee_code, type_id,
                    salary_advance,  
                    due_date, comments, 
                    status_code,
                    created_by, created_on) 
                VALUES ("
                    .db_escape($employee_code).", ".db_escape($type_id).", "
                    .db_escape($salary_advance).", " 
                    .db_escape(date2sql($due_date)). ", ".db_escape($comments).", "
                    .db_escape($status_code).", "
                    .db_escape($created_by).", ".db_escape($created_on).")";
        
        db_query($sql,"Salary advance could not be added");
        
        return db_insert_id();

}


function update_salary_advance($rec_id, $employee_code, 
                            $type_id, $salary_advance, 
                            $due_date, $comments, $status_code)
{

         $updated_by = $_SESSION['wa_current_user']->user;
         $updated_on = date('Y-m-d H:i:s');

        $sql = "UPDATE ".TB_PREF."salary_advances 
                SET 
                    type_id=".db_escape($type_id).",
                    salary_advance=".db_escape($salary_advance).",
                    due_date =".db_escape(date2sql($due_date)).",
                    comments =".db_escape($comments).",
                    status_code = ".db_escape($status_code).",
                    updated_by=".db_escape($updated_by).",
                    updated_on=".db_escape($updated_on)." 
                WHERE id=".db_escape($rec_id)." AND 
                    employee_code =".db_escape($employee_code);

        db_query($sql,"Salary advance could not be updated");
        
        return db_num_affected_rows();
}

function delete_salary_advance($rec_id, $employee_code)
{
        $sql = "DELETE FROM ".TB_PREF."salary_advances  
                WHERE id=".db_escape($rec_id)." AND 
                    employee_code =".db_escape($employee_code);

        db_query($sql,"Salary advance could not be deleted");
        
        return db_num_affected_rows();
}

function get_salary_advance_details($selected_id)
{
    $sql = "SELECT s.*  
            FROM " 
                .TB_PREF."salary_advances s 
            WHERE s.id = ".db_escape($selected_id);
  

    $result = db_query($sql, "Shift could not be retrieved");
    
    return db_fetch($result);
}


function get_salary_advances($employee_code)
{
    $sql = "SELECT s.*, 
                st.value_name status_name,
                ty.value_name type_name 
            FROM " 
                .TB_PREF."salary_advances s, "
                .TB_PREF."list_values st, "
                .TB_PREF."list_values ty 
            WHERE s.employee_code = ".db_escape($employee_code)." AND 
                s.status_code = st.value_code AND st.list_code = 'ATTENDANCE_STATUS' AND 
                s.type_id = ty.id 
            ORDER BY due_date";
  

    return db_query($sql, "Salary advances could not be retrieved");
}

function fill_salary_advance_accounts_data(&$data, $amount, $debit_code, $credit_code)
{            
            $points = abs($amount);

            $debit['code_id'] = $debit_code;
            $credit['code_id'] = $credit_code;  

            $debit['amount_debit'] = $amount;
            $debit['amount_credit'] = "0.00";
                
            $credit['amount_debit'] = "0.00";
            $credit['amount_credit'] = $amount;
                
            $debit['dimension_id'] = "0";
            $debit['dimension2_id'] = "0";
            $debit['LineMemo'] = "";
    
            $data['items'][] = $debit;
            
            $credit['dimension_id'] = "0";
            $credit['dimension2_id'] = "0";
            $credit['LineMemo'] = "";

            $data['items'][] = $credit;               
            
}
function pay_salary_advance($id, $employee_code, $amount, 
                        $debit_code, $credit_code)
{
        global $Refs;
        
        $updated_by = $_SESSION['wa_current_user']->user;
        $updated_on = date('Y-m-d H:i:s');        

        if($debit_code != "" && $credit_code != ""){
            global $path_to_root; // = "../..";
            include_once($path_to_root . "/includes/ui/items_cart.inc");
            include_once($path_to_root . "/gl/includes/gl_db.inc");
            include_once($path_to_root . "/gl/includes/add_journal.inc");
            
            $cart = null;
            
            fill_salary_advance_accounts_data($data, $amount, $debit_code, $credit_code);
        
            $data['trans_type'] = ST_SALARY_ADVANCE_PAYMENT;
            $data['order_date'] = sql2date($updated_on);
            
            $data['comment'] = "Employee - [".$employee_code."]";

            $journal_check_result = journal_check($data, $cart);
            if($journal_check_result["passed"] == false){
                return $journal_check_result;
            }
        }
        
        if($debit_code != "" && $credit_code != ""){
            $cart->order_id = $id;
            $tran_reference = $id;
            
            begin_transaction();
            
            $journal_add_result = journal_add($cart);
            if($journal_add_result["passed"] == false){
                cancel_transaction();
                return $journal_add_result;
            }
            
            $is_debit_code_bank_to = is_bank_account($debit_code);
            if ($is_debit_code_bank_to)
            {
                    add_bank_trans(ST_SALARY_ADVANCE_PAYMENT, $cart->order_id, $is_debit_code_bank_to, $tran_reference,
                            $cart->tran_date, $amount,	0, "", get_company_currency(),
                            "Could not insert destination debit bank transaction");
            }
            
            $is_credit_code_bank_to = is_bank_account($credit_code);
            if ($is_credit_code_bank_to)
            {
                    add_bank_trans(ST_SALARY_ADVANCE_PAYMENT, $cart->order_id, $is_credit_code_bank_to, $tran_reference,
                            $cart->tran_date, -$amount,	0, "", get_company_currency(),
                            "Could not insert destination creadt bank transaction");
            }
            
            $Refs->save($cart->trans_type, $cart->order_id, $tran_reference);
        
            add_comments($cart->trans_type, $cart->order_id, $cart->tran_date, $cart->memo_);

            add_audit_trail($cart->trans_type, $cart->order_id, $cart->tran_date);
            
            $num_recs_affected = update_salary_advance_paid_status($id, $employee_code);
            if($num_recs_affected == 1){
                commit_transaction();
            }
            else{
                cancel_transaction();
            }
        }

        
}


function update_salary_advance_paid_status($id, $employee_code)
{
        $updated_by = $_SESSION['wa_current_user']->user;
        $updated_on = date('Y-m-d H:i:s');  
        
        $sql = "UPDATE ".TB_PREF."salary_advances 
                SET status_code = 'D',
                    updated_by=".db_escape($updated_by).",
                    updated_on=".db_escape($updated_on)." 
                WHERE id=".db_escape($id)." AND 
                    employee_code =".db_escape($employee_code)." AND 
                    status_code = 'A'";

        $errmsg = "Salary advance paid status could not be updated";
        db_query($sql, $errmsg);
        
        return db_num_affected_rows();
}


function get_view_salary_advance($id)
{
    $sql = "SELECT 
                    sd.employee_code, 
                    sd.salary_advance, 
                    sd.approved_on, 
                    sd.payroll_run_id,
                    sd.comments,
                    em.first_name,
                    em.middle_name,
                    em.last_name,
                    lv.value_name salary_advance_type,
                    u.real_name approver_name 
            FROM 
                    ".TB_PREF."salary_advances sd 
                    LEFT JOIN ".TB_PREF."employee_master em 
                                ON sd.employee_code = em.employee_code
                    LEFT JOIN ".TB_PREF."list_values lv 
                                ON sd.type_id = lv.id  
                    LEFT JOIN ".TB_PREF."users u 
                                ON sd.approved_by = u.id 
            WHERE sd.id = ".db_escape($id);
    
    $result = db_query($sql, "Salary advance could not be retrieved");
    
    return db_fetch($result);
    
        
}

function get_salary_advance_existing_record($employee_code, $type_id, $salary_advance, $due_date)
{
    $sql = "SELECT *  
            FROM ".TB_PREF."salary_advances  
            WHERE employee_code = ".db_escape($employee_code)." 
                    AND type_id = ".db_escape($type_id)." 
                    AND salary_advance = ".db_escape($salary_advance)." 
                    AND due_date = ".db_escape(date2sql($due_date));  

    $result = db_query($sql, "Could not get_salary_advance_existing_record");
    
    return db_fetch($result);
}

?>