<?php

function add_bond($name, $description, $credit_facility_id, $type_id, $payable, $customer_id, $supplier_id,
                   $bank_id, $branch_id, $bank_account_id, 
                   $value, $bank_charge,
                   $start_date, $expiry_date, $project_id, $comments, $expired, $original_bond_id = 0)    
               
{
        $id_user = $_SESSION['wa_current_user']->user;
        $datetime = date('Y-m-d H:i:s');        

        $sql = "INSERT INTO ".TB_PREF."bonds
                        (name, description, 
                        credit_facility_id, type_id, 
                        payable, customer_id, 
                        supplier_id, bank_id,
                        branch_id, bank_account_id, 
                        value, bank_charge, 
                        start_date, expiry_date, 
                        project_id, comments, 
                        expired, original_bond_id,
                        created_by, created_on)                                                
                VALUES (".db_escape($name).",".db_escape($description).","
                        .db_escape($credit_facility_id).",".db_escape($type_id).","
                        .db_escape($payable).",".db_escape($customer_id).","
                        .db_escape($supplier_id).",".db_escape($bank_id).","
                        .db_escape($branch_id).",".db_escape($bank_account_id).","
                        .db_escape($value).",".db_escape($bank_charge).","
                        .db_escape(date2sql($start_date)).",".db_escape(date2sql($expiry_date)).","
                        .db_escape($project_id).",".db_escape($comments).","
                        .db_escape($expired).",".db_escape($original_bond_id).","        
                        .db_escape($id_user).",".db_escape($datetime).")";     
        
    db_query($sql, "could not add Bonds.");
         //return ();
    $n = db_insert_id();
    if($n > 0){
        return array("success" => true, "trans_no" => $n, "message" => "New Bond $n has been created.");
    }
    else{
        return array("success" => false, "message" => "Could not create a new Bond.");
    }
    
    
}

function update_bond($selected_id, $name, $description, $credit_facility_id,  
                    $customer_id, $supplier_id, $bank_id, $branch_id, 
                    $bank_account_id, $value, $bank_charge, $from_date, 
                    $expiry_date, $project_id, $comments, $expired)
{
    $id_user = $_SESSION['wa_current_user']->user;
    $datetime = date('Y-m-d H:i:s');
    
	$sql = "UPDATE ".TB_PREF."bonds 
                             SET name=".db_escape($name).
                              ", description=".db_escape($description).                                   
                              ", start_date=".db_escape(date2sql($from_date)).  
                              ", expiry_date=".db_escape(date2sql($expiry_date)). 
                              ", credit_facility_id=".db_escape($credit_facility_id).
                              ", customer_id=".db_escape($customer_id).
                              ", supplier_id=".db_escape($supplier_id).
                              ", bank_id=".db_escape($bank_id).
                              ", branch_id=".db_escape($branch_id).
                              ", bank_account_id=".db_escape($bank_account_id).
                              ", project_id=".db_escape($project_id).
                              ", value=".db_escape($value).
                              ", bank_charge=".db_escape($bank_charge).
                              ", comments=".db_escape($comments).
                              ", updated_by=".db_escape($id_user).
                              ", updated_on=".db_escape($datetime)."                
                                    
			WHERE id=".db_escape($selected_id)." 
                    AND expired = 0";
                 
    db_query($sql, "could not update Bonds");
    
//    $n = db_num_affected_rows();
//    if(!($n == 1)){
//        return array("success" => false, "message" => "Another user has updated this bond.");
//    }
//    
    if(!$expired){
        return array("success" => true, "trans_no" => $selected_id, "message" => "Bond $selected_id has been updated.");
    }
    else{
        return expire_bond($selected_id);
    }
}

function get_bonds_for_update($selected_id)
{
	$sql="SELECT * FROM ".TB_PREF."bonds WHERE id=".db_escape($selected_id);

	$result = db_query($sql, "could not retrive Bonds");
        return db_fetch($result);
}

function delete_bonds($selected_id)
{
	$sql="DELETE FROM ".TB_PREF."bonds WHERE id=".db_escape($selected_id);

	db_query($sql, "Selected bond could not delete.");
}

function get_bonds()
{
    $sql = "SELECT bo.*, 
                    dm.name guarantor,
                    lv.value_name,
                    pm.description project_description                   
        
            FROM ".TB_PREF."bonds AS bo
                LEFT JOIN ".TB_PREF."debtors_master AS dm ON
                        bo.guarantor = dm.debtor_no
                LEFT JOIN ".TB_PREF."projects_master AS pm ON
                        bo.project_id = pm.project_id
                LEFT JOIN ".TB_PREF."list_values AS lv ON
                        bo.type_id = lv.id 
                      
            ORDER BY bo.id"; 
    
    return db_query($sql, "Could not get bonds");    
    
}

function get_bond($selected_id)
{
    $sql = "SELECT b.*,                         
                    tn.value_name bond_type_name,                        
                    sup.supp_name supplier_name,
                    dm.name customer_name,                        
                    cf.name credit_facility_name,
                    bnk.value_name bank_name,
                    br.branch_name,
                    ba.bank_account_number 
            FROM ".TB_PREF."bonds b 
                    LEFT JOIN ".TB_PREF."list_values tn ON
                            b.type_id = tn.id 
                    LEFT JOIN ".TB_PREF."debtors_master dm ON 
                            b.customer_id = dm.debtor_no 
                    LEFT JOIN ".TB_PREF."suppliers sup ON
                            b.supplier_id = sup.supplier_id 
                    LEFT JOIN ".TB_PREF."credit_facilities cf ON
                            b.credit_facility_id = cf.id 
                    LEFT JOIN ".TB_PREF."list_values bnk ON
                            b.bank_id = bnk.id 
                    LEFT JOIN ".TB_PREF."bank_branches br ON
                            b.branch_id = br.id 
                    LEFT JOIN ".TB_PREF."bank_accounts ba ON
                            b.bank_account_id = ba.id  
            WHERE b.id=".db_escape($selected_id);
            
    $result = db_query($sql, 'Could not get_bond');
    return db_fetch($result);
    
}


function get_credit_facilities_for_bonds($selected_id)
{
    $sql = "SELECT cf.*,
                    lv.value_name bank_name,
                    bb.branch_name,
                    ba.bank_account_name

            FROM ".TB_PREF."credit_facilities cf,
                    ".TB_PREF."list_values lv,
                    ".TB_PREF."bank_branches bb,
                    ".TB_PREF."bank_accounts ba

            WHERE lv.id = cf.bank_id AND
                    bb.id = cf.branch_id AND
                    ba.id = cf.bank_account_id AND
                    cf.id=".db_escape($selected_id);

        $result = db_query($sql, 'Could not get_credit_facilities_for_bonds');
        return db_fetch($result);    
}

function expire_bond($bond_id)
{
    $id_user = $_SESSION['wa_current_user']->user;
    $datetime = date('Y-m-d H:i:s');
    
	$sql = "UPDATE ".TB_PREF."bonds 
                SET expired= 1, 
                updated_by=".db_escape($id_user).", 
                updated_on=".db_escape($datetime)."                
                                    
			WHERE  id=".db_escape($bond_id)." AND expired = 0";
                             
	db_query($sql, "Could not expire_bond");
    
    $n = db_num_affected_rows();
    if(!($n == 1)){
        return array("success" => false, "message" => "Could not expire this bond.");
    }
    
    return array("success" => true, "trans_no" => $bond_id, "message" => "Bond $bond_id has been expired.");
}

function extend_bond($bond_id, $extend_bond_name, $extend_bond_description, 
                $extend_bond_value, $extend_bank_charge,
                $extend_start_date, $extend_expiry_date, $extend_comment)
{
    $msg = expire_bond($bond_id);
    if($msg['success'] !== true){
        return $msg;
    }

    $old_bond = get_bond($bond_id);
    add_bond($extend_bond_name, $extend_bond_description, $old_bond['credit_facility_id'], 
                    $old_bond['type_id'], $old_bond['payable'], $old_bond['customer_id'], $old_bond['supplier_id'],
                    $old_bond['bank_id'], $old_bond['branch_id'], $old_bond['bank_account_id'], 
                    $extend_bond_value, $extend_bank_charge,
                    $extend_start_date, $extend_expiry_date, $old_bond['project_id'], $extend_comment, 0,
                    $bond_id);
    $new_bond_id  = db_insert_id();
    if(!($new_bond_id > 0)){
        return array("success" => false, "message" => "Could not create a new bond for expired bond.");
    }
    
    return array("success" => true, "trans_no" => $new_bond_id, "message" => "Bond $bond_id has been expired and new Bond $new_bond_id created.");
    
}


function get_bond_record($selected_id)
{
        $sql = "SELECT b.* 
            FROM ".TB_PREF."bonds b 
            WHERE b.id=".db_escape($selected_id);
            
    $result = db_query($sql, 'Could not get_bond_record');
    return db_fetch($result);
}


function view_bonds($bond_id)
{ 
    $sql = "SELECT bo.*,
                    lv.value_name type,
                    li.value_name bank_name,
                    bb.branch_name,
                    pm.project_name                    

            FROM ".TB_PREF."bonds bo           
                LEFT JOIN ".TB_PREF."list_values lv ON
                    lv.id = bo.type_id                     

                LEFT JOIN ".TB_PREF."list_values li ON
                    li.id = bo.bank_id

                LEFT JOIN  ".TB_PREF."bank_branches bb ON
                    bb.id = bo.branch_id

                 LEFT JOIN ".TB_PREF."projects_master pm ON
                    pm.project_id = bo.project_id
                    
            WHERE  bo.id=".db_escape($bond_id);
                 
    $result = db_query($sql);
    return db_fetch($result);
    
}


function get_original_bond($selected_id)
{
    $sql = "SELECT *
            FROM ".TB_PREF."bonds 
            WHERE original_bond_id = ".db_escape($selected_id);
    
   $result = db_query($sql);
   return db_fetch($result);
    
}