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


function get_i_owe_you_payments($iou_id)
{
        $sql= "SELECT p.* 
               FROM ".TB_PREF."i_owe_you_payments p 
               WHERE p.iou_id=".db_escape($iou_id)." AND 
                        (p.expense_source = 0 OR p.expense_source = ".db_escape(ST_I_OWE_YOU).")  
               ORDER BY p.payment_date";
        
	return $result=db_query($sql,"Could not get_i_owe_you_payments");	
       //  db_fetch($result);
    
}

function get_petty_cash_payments($expense_source, $expense_id)
{
        $sql= "SELECT p.* 
               FROM ".TB_PREF."i_owe_you_payments p 
               WHERE p.iou_id=".db_escape($expense_id)." AND 
                        p.expense_source = ".db_escape($expense_source)."  
               ORDER BY p.payment_date";
        
	return $result=db_query($sql,"Could not get_petty_cash_payments");	
       //  db_fetch($result);
    
}

function get_i_owe_you_payment($payment_id)
{
        $sql= "SELECT * 
               FROM " .TB_PREF."i_owe_you_payments 
               WHERE id=". db_escape($payment_id);
        
        $result = db_query($sql,"Could not get_i_owe_you_payment");	
        return db_fetch($result);
    
}


function release_i_owe_you_payment($iou, $account_codes, $payment_id)
{
    $paid_by = $_SESSION['wa_current_user']->user;
    $paid_on = date('Y-m-d H:i:s');

	$sql = "UPDATE ".TB_PREF."i_owe_you_payments 
            SET paid = 1 , 
                    paid_by = ".db_escape($paid_by).", 
                    paid_on = ".db_escape($paid_on)."   
            WHERE id = ".$payment_id." AND paid = 0";
        
    db_query($sql,"Could not release_i_owe_you_payment");
    
    return db_num_affected_rows();
        
}


function add_i_owe_you_payment($iou_id, $payment_date, $amount, $payment_comment, 
                                $is_return, $float_id, $expense_source,
                                $receiver_name = '', $receiver_nic = '', $receiver_contact_nos = '')
{
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');        
        
	$sql = "INSERT INTO ".TB_PREF."i_owe_you_payments (
                    iou_id, payment_date, 
                    amount, comment,
                    is_return, float_id,
                    expense_source,
                    receiver_name, receiver_nic, receiver_contact_nos,
                    created_by, created_on) 
            VALUES ("
                    .db_escape($iou_id).", ".db_escape(date2sql($payment_date)).", "
                    .db_escape($amount).", ".db_escape($payment_comment).", " 
                    .db_escape($is_return).", ".db_escape($float_id).", " 
                    .db_escape($expense_source).", "
                    .db_escape($receiver_name).", ".db_escape($receiver_nic).", ".db_escape($receiver_contact_nos).", " 
                    .db_escape($created_by).", ".db_escape($created_on).")";
        
    db_query($sql,"Could not add_i_owe_you_payment");
        
    return db_insert_id();

}


function update_i_owe_you_payment($payment_id, $payment_date, $amount, $payment_comment)
{
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
        
        
	$sql = "UPDATE ".TB_PREF."i_owe_you_payments 
            SET payment_date = ".db_escape(date2sql($payment_date)).", 
                    amount = ".db_escape($amount).", 
                    comment = ".db_escape($payment_comment).",
                    updated_by = ".db_escape($updated_by).", 
                    updated_on = ".db_escape($updated_on)."   
            WHERE id = ".$payment_id;
        
    db_query($sql,"Could not update_i_owe_you_payment");
        
    return db_num_affected_rows();
  
}

function get_i_owe_you_payments_total($iou_id, $payment_id, $paid_only=0) {
    $sql = "SELECT SUM(amount) payment_total  
            FROM ".TB_PREF."i_owe_you_payments  
            WHERE iou_id = ".db_escape($iou_id)." 
                AND expense_source = ".db_escape(ST_I_OWE_YOU)."  
                AND id <> ".db_escape($payment_id);
    
    if ($paid_only == 1) {
        $sql .= " AND paid = 1";
    }
        
    $result = db_query($sql, "get_i_owe_you_payments_total");	
    return db_result_value($result, 'payment_total');
}

function delete_petty_cash_payment_entry($rec_id)
{
        $sql = "DELETE FROM ".TB_PREF."i_owe_you_payments    
                WHERE id=".db_escape($rec_id)." AND paid = 0";

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

function get_unreleased_i_owe_you_batch_payments($batch_no)
{
    
    $sql= "SELECT p.*   
           FROM ".TB_PREF."i_owe_you_payments p,
                ".TB_PREF."i_owe_you ou   
           WHERE p.iou_id = ou.id AND 
                p.expense_source = ".ST_I_OWE_YOU." AND 
                p.paid = 0 AND 
                ou.batch_no =".db_escape($batch_no);  
    
    return db_query($sql, "Could not get_unreleased_i_owe_you_batch_payments");
    
}

function get_unreleased_petty_cash_expense_batch_payments($batch_no)
{
    
    $sql= "SELECT p.*   
           FROM ".TB_PREF."i_owe_you_payments p,
                ".TB_PREF."petty_cash_header ph    
           WHERE p.iou_id = ph.id AND 
                p.expense_source = ".ST_PETTY_CASH_PAYMENT." AND 
                p.paid = 0 AND 
                ph.batch_no =".db_escape($batch_no);  
    
    return db_query($sql, "Could not get_unreleased_petty_cash_expense_batch_payments");
    
}


function get_released_i_owe_you_batch_payments_total($batch_no)
{
    $sql= "SELECT SUM(p.amount) batch_payments_total  
           FROM ".TB_PREF."i_owe_you_payments p,
                ".TB_PREF."i_owe_you ou   
           WHERE p.iou_id = ou.id AND 
                p.expense_source = ".ST_I_OWE_YOU." AND 
                p.paid = 1 AND 
                ou.batch_no =".db_escape($batch_no);  
    
    $result = db_query($sql, "Could not get_released_i_owe_you_batch_payments_total");
    $batch_ex = db_fetch($result);  
    
    if($batch_ex){
        return $batch_ex['batch_payments_total'];
    }
    else{
        return 0;
    }
}


function get_i_owe_you_batch_bank_payments_total($batch_no)
{
    $sql= "SELECT SUM(bt.amount) batch_payments_total  
           FROM ".TB_PREF."i_owe_you ou 
               JOIN ".TB_PREF."bank_trans bt 
                   ON bt.person_type_id = ".db_escape(PT_IOU)." 
                   AND bt.person_id = ou.id 
           WHERE ou.batch_no =".db_escape($batch_no);  
    
    $result = db_query($sql, "get_i_owe_you_batch_bank_payments_total");
    $batch_ex = db_fetch($result);  
    
    if($batch_ex){
        return $batch_ex['batch_payments_total'];
    }
    else{
        return 0;
    }
}


function get_released_petty_cash_expenses_batch_payments_total($batch_no)
{
    $sql= "SELECT SUM(p.amount) batch_payments_total  
           FROM ".TB_PREF."i_owe_you_payments p,
                ".TB_PREF."petty_cash_header ph    
           WHERE p.iou_id = ph.id AND 
                p.expense_source = ".ST_PETTY_CASH." AND 
                p.paid = 1 AND 
                ph.batch_no =".db_escape($batch_no);  
    
    $result = db_query($sql, "Could not get_released_petty_cash_expenses_batch_payments_total");
    $batch_ex = db_fetch($result);  
    
    if($batch_ex){
        return $batch_ex['batch_payments_total'];
    }
    else{
        return 0;
    }
}

function update_payment_float_reimbursement_amount($id, $reimbursed_amount, $row_state)
{
    $sql = "UPDATE ".TB_PREF."i_owe_you_payments
            SET reimbursed_amount = $reimbursed_amount ,
                row_state =". db_new_row_state($row_state)."  
            WHERE ABS(amount) >= ".db_escape($reimbursed_amount)." AND 
                    id =".db_escape($id)." AND 
                    row_state =".db_escape($row_state);
    
    db_query($sql, "Could not update selected");  
    return db_num_affected_rows();    
    
}

function view_petty_cash_payment($id)
{
        $sql= "SELECT py.*,
                    pf.name float_name,
                    u.real_name user_name
               FROM ".TB_PREF."i_owe_you_payments py 
                    INNER JOIN ".TB_PREF."i_owe_you ou ON
                            py.iou_id = ou.id 
                    INNER JOIN ".TB_PREF."petty_cash_floats pf ON
                            ou.float_id = pf.id 
                    INNER JOIN ".TB_PREF."users u ON
                            py.created_by = u.id 
               WHERE py.id=".db_escape($id);             
        
        $sql= "SELECT py.*,
                    pf.name float_name,
                    u.real_name user_name
               FROM ".TB_PREF."i_owe_you_payments py 
                    INNER JOIN ".TB_PREF."petty_cash_floats pf ON
                            py.float_id = pf.id 
                    INNER JOIN ".TB_PREF."users u ON
                            py.created_by = u.id 
               WHERE py.id=".db_escape($id);             
        
        $result = db_query($sql,"Could not view_petty_cash_payment");	
        return db_fetch($result);    
}


function view_petty_cash_direct_payment($id)
{
        $sql= "SELECT py.*,
                    pf.name float_name,
                    u.real_name user_name
               FROM ".TB_PREF."i_owe_you_payments py 
                    INNER JOIN ".TB_PREF."petty_cash_floats pf ON
                            py.float_id = pf.id 
                    INNER JOIN ".TB_PREF."users u ON
                            py.created_by = u.id 
               WHERE py.id=".db_escape($id);             
        
        $result = db_query($sql,"Could not view_petty_cash_payment");	
        return db_fetch($result);    
}


function get_unreleased_i_owe_you_payments($iou_id)
{
    
    $sql= "SELECT p.*   
           FROM ".TB_PREF."i_owe_you_payments p,
                ".TB_PREF."i_owe_you ou   
           WHERE p.iou_id = ou.id AND 
                p.paid = 0 AND 
                ou.id =".db_escape($iou_id);  
    
    return db_query($sql, "Could not get_unreleased_i_owe_you_payments");
    
}

function get_released_i_owe_you_payments_total($iou_id)
{
    $sql= "SELECT SUM(p.amount) iou_payments_total  
           FROM ".TB_PREF."i_owe_you_payments p 
           WHERE p.paid = 1 AND 
                p.iou_id =".db_escape($iou_id);  
    
    $result = db_query($sql, "Could not get_released_i_owe_you_payments_total");
    $batch_ex = db_fetch($result);  
    
    if(isset($batch_ex['iou_payments_total']) && $batch_ex['iou_payments_total'] > 0){
        return $batch_ex['iou_payments_total'];
    }
    else{
        return 0;
    }
}


function get_petty_cash_Payments_total($expense_source, $expense_id, $payment_id)
{
    $sql= "SELECT SUM(amount) payment_total  
           FROM ".TB_PREF."i_owe_you_payments  
           WHERE expense_source =".db_escape($expense_source)." AND 
                    iou_id=".db_escape($expense_id)." AND 
                    id <> ".db_escape($payment_id);
        
	$result=db_query($sql,"Could not get_petty_cash_Payments_total");	
    
    $total =  db_fetch($result);
    if($total['payment_total'] > 0){
        return $total['payment_total'];
    }
    else{
        return 0;
    }
    
}


?>