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



function add_insured_item($name, $description, $insurer_type, 
                            $insurer_id, $insuree_type, $company_code, $insuree,
                            $policy_type_id, $insured_amount, $premium, 
                            $start_date, $renewal_date,$last_renewed_on, 
                            $comment, $inactive, $expired)
        
{
        $created_by = $_SESSION['wa_current_user']->user;
        $created_on = date('Y-m-d H:i:s');        
        
	$sql = "INSERT INTO ".TB_PREF."insured_items 
                       (name, description, insurer_type, 
                        insurer_id, insuree_type, 
                        company_code, insuree, policy_type_id,
                        insured_amount, premium, 
                        start_date, renewal_date,last_renewed_on, 
                        comment, 
                        inactive, expired,
                        created_by, created_on) 
                VALUES (".db_escape($name).", ".db_escape($description).", ".db_escape($insurer_type).", "
                        .db_escape($insurer_id).", ".db_escape($insuree_type).","
                        .db_escape($company_code).", ".db_escape($insuree). ",  ".db_escape($policy_type_id). ", "
                        .db_escape($insured_amount).", ".db_escape($premium). ", "
                        .db_escape($start_date).", ".db_escape($renewal_date). ", ".db_escape($last_renewed_on).", "
                        .db_escape($comment). ", "
                        .db_escape($inactive).", ".db_escape($expired).", "                        
                        .db_escape($created_by).", ".db_escape($created_on).")";

	db_query($sql,"New Company Loan could not be added");
        
    return db_insert_id();
}



 function update_renewal_dates($insured_item_id, $next_renewal_date, $renewed_on)
 {
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');        
        
    $sql = "UPDATE ".TB_PREF."insured_items
                SET renewal_date=".db_escape(date2sql($next_renewal_date)).",
                    last_renewed_on=".db_escape(date2sql($renewed_on)).",
                    created_by=".db_escape($created_by).",
                    created_on=".db_escape($created_on)."
                        
                WHERE id=".db_escape($insured_item_id)." AND inactive = 0 and expired = 0";

    db_query($sql, "Could not update dates.");
          
    return db_num_affected_rows(); 
 }
  

function add_insured_item_renewal($insured_item_id, $next_renewal_date, $renewed_on, 
                                    $amount, $reference, $comment)
{
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');        
        
    $sql = "INSERT INTO ".TB_PREF."insured_item_renewals (
                        insured_item_id, next_renewal_date, 
                        renewed_on, amount, 
                        reference, comment, 
                        created_by, created_on)
            VALUES (".db_escape($insured_item_id).", ".db_escape(date2sql($next_renewal_date)).", "
                        .db_escape(date2sql($renewed_on)).",".db_escape($amount).", "
                        .db_escape($reference).", ".db_escape($comment).", "
                        .db_escape($created_by).", ".db_escape($created_on).")";
    
    db_query($sql, "Insured items renewals could not be added.");       
    
}


function update_insured_item($insured_item_id, $name, 
                                $description, $insurer_type, 
                                $insurer_id, $insuree_type, $company_code, $insuree,
                                $policy_type_id, $insured_amount, $premium, 
                                $start_date, $renewal_date, $last_renewed_on, 
                                $comment, $inactive, $expired)
{
        $updated_by = $_SESSION['wa_current_user']->user;
        $updated_on = date('Y-m-d H:i:s');
     
        $sql = "UPDATE ".TB_PREF."insured_items              
                SET name=".db_escape($name).",
                    description=".db_escape($description).",
                    insurer_type=".db_escape($insurer_type).",  
                    insurer_id=".db_escape($insurer_id).",  
                    insuree_type=".db_escape($insuree_type).",
                    company_code =".db_escape($company_code).",
                    insuree =".db_escape($insuree).",
                    policy_type_id =".db_escape($policy_type_id).",  
                    insured_amount =".db_escape($insured_amount).",
                    premium=".db_escape($premium).",
                    start_date =".db_escape($start_date).",
                    renewal_date =".db_escape($renewal_date).",
                    last_renewed_on =".db_escape($last_renewed_on).",
                    comment =".db_escape($comment).",
                    inactive=".db_escape($inactive).",
                    expired=".db_escape($expired).",
                    updated_by=".db_escape($updated_by).",
                    updated_on=".db_escape($updated_on)." 
                        
		WHERE id=".db_escape($insured_item_id);                     
        
	db_query($sql,"New Company Loan could not be updated");
    
    return db_num_affected_rows();
}


function update_insured_item_expired($loan_id)
{
        $sql = "UPDATE ".TB_PREF."letters_of_credit             
                SET expired= 1   
                WHERE id=".db_escape($loan_id);                     
        
	db_query($sql,"New Could not update_company_loan_completed");
}


function delete_insured_item($selected_id)
{
	$sql = "DELETE FROM ".TB_PREF."letters_of_credit WHERE id=".db_escape($selected_id);
        
	db_query($sql,"could not delete selected company loan");
}

function get_company_insured_items()
{
    $sql = "SELECT cl.* , 
                    cf.name  credit_facility_name,
                    lv.value_name bank_name,
                    bb.branch_name 
        
            FROM ".TB_PREF."letters_of_credit cl 
                    LEFT JOIN ".TB_PREF."credit_facilities cf ON 
                            cl.credit_facility_id = cf.id 
                    LEFT JOIN ".TB_PREF."list_values lv ON
                            lv.id = cl.bank_id
                    LEFT JOIN ".TB_PREF."bank_branches bb ON
                            bb.id = cl.branch_id ";
    
    return db_query($sql, "Company Loans could not be retrieved");
}


function get_insured_item($selected_id)
{
    $sql = "SELECT ii.* 
            FROM ".TB_PREF."insured_items ii  
                    
           WHERE ii.id = ".db_escape($selected_id); 

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

//function get_insured_item_header($company_loan_id)
//{
//    $sql = "SELECT cl.*,
//                    cf.name credit_facility_name
//        
//            FROM ".TB_PREF."company_loans cl 
//                    ".TB_PREF."credit_facilities cf
//
//
//            WHERE   cf.id = cl.credit_facility_id AND 
//                    cl.id=".db_escape($company_loan_id);
//
//        $result = db_query($sql);
//        return db_fetch($result);    
//}

function get_insured_item_renewal_details($selected_id)
{
    $sql = "SELECT iir.* 
            FROM ".TB_PREF."insured_item_renewals iir  
                    
           WHERE iir.insured_item_id = ".db_escape($selected_id); 

    return db_query($sql,"Could not get_insured_item_renewal_details");
   
}
?>