<?php //
/**********************************************************************
***********************************************************************/


function add_est_boq_item_material($boq_item_id, $detail_type_code, $stock_id, 
                                   $tran_unit, $unit_cost, $tran_qty, 
                                   $comment, $item_type) 
{
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
	$sql = "INSERT INTO ".TB_PREF."est_project_boq_item_material 
                    (boq_item_id, detail_type_code, 
                     stock_id, tran_unit, 
                     tran_unit_cost, tran_qty, 
                     comment, item_type, 
                     created_by, created_on)
            
		VALUES 
                   (".db_escape($boq_item_id).", ".db_escape($detail_type_code).",
                    ".db_escape($stock_id).",  ".db_escape($tran_unit). ",
                    ".db_escape($unit_cost).",  ".db_escape($tran_qty).", 
                    ".db_escape($comment).", ".db_escape($item_type).",    
                    ".db_escape($created_by).", ".db_escape($created_on).")"; 

    db_query($sql, "Could not add estimation boq material");
    
    $result = recalcualte_est_boq_item_total_cost($boq_item_id, $detail_type_code, BOQPT_MATERIAL);
    if($result){
        return array("success" => true); 
    }
    else{
        return array("success" => false); 
    } 
}


function update_est_boq_item_material($selected_id, $boq_item_id, $detail_type_code, 
                                $tran_unit, $unit_cost, $tran_qty, 
                                $comment, $item_type)
{
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
    $sql = "UPDATE ".TB_PREF."est_project_boq_item_material
            SET             
                detail_type_code=".db_escape($detail_type_code).",
                tran_unit=".db_escape($tran_unit).", 
                tran_unit_cost=".db_escape($unit_cost).", 
                tran_qty=".db_escape($tran_qty).", 
                comment=".db_escape($comment).",
                item_type=".db_escape($item_type).",              
                updated_by=".db_escape($updated_by).", 
                updated_on=".db_escape($updated_on)." 
                    
            WHERE id=".db_escape($selected_id);        
        
    db_query($sql, "Could not update estimation boq material");
    
    $result = recalcualte_est_boq_item_total_cost($boq_item_id, $detail_type_code, BOQPT_MATERIAL);
    if($result){
        return array("success" => true); 
    }
    else{
        return array("success" => false); 
    } 
}


function get_est_boq_item_material($boq_item_id, $detail_type_code)
{
	$sql = "SELECT bim.id,
                        bim.item_type,
                        bim.stock_id,                 
                        iu.name tran_unit_name,
                        bim.tran_qty,
                        bim.tran_unit,
                        bim.tran_unit_cost,
                        bim.comment,
                        bim.item_type,
                        bim.tran_unit  
                FROM ".TB_PREF."est_project_boq_item_material bim,                                         
                        ".TB_PREF."item_units iu      
                WHERE   iu.abbr = bim.tran_unit 
                    AND bim.boq_item_id=".db_escape($boq_item_id)." 
                    AND bim.detail_type_code=".db_escape($detail_type_code);        
  
	return db_query($sql, "Could not get_est_boq_item_material");
}


function get_est_boq_item_material_record($boq_item_id, $detail_type_code)
{
	$sql = "SELECT bim.id, 
                        bim.item_type,
                        bim.stock_id,                      
                        bim.tran_qty,
                        bim.tran_unit,
                        bim.tran_unit_cost,
                        bim.comment
                                              
                FROM ".TB_PREF."project_boq_item_material bim 
                          
                WHERE bim.boq_item_id=".db_escape($boq_item_id)." AND       
                        bim.detail_type_code=".db_escape($detail_type_code);        
  
	return db_query($sql, "Could not get_boq_item_material_record");
}

function get_est_boq_item_material_record_by_id($id)
{
    $sql = "SELECT bim.id, 
                    bim.item_type,
                    bim.stock_id,                      
                    bim.tran_qty,
                    bim.tran_unit,
                    bim.tran_unit_cost,
                    bim.comment
                    
            FROM ".TB_PREF."est_project_boq_item_material bim 

            WHERE bim.id=".db_escape($id);        

    $result = db_query($sql, "Could not get estimation boq item material record");
    return db_fetch($result);
}

function delete_est_boq_item_material($id, $boq_item_id, $detail_type_code)
{
	$sql = "DELETE FROM ".TB_PREF."est_project_boq_item_material WHERE id=".db_escape($id);
	db_query($sql,"Could not delete Estimation BOQ item material");
    
    $result = recalcualte_est_boq_item_total_cost($boq_item_id, $detail_type_code, BOQPT_MATERIAL);
    if($result){
        return array("success" => true); 
    }
    else{
        return array("success" => false); 
    } 
}


function get_est_component_from_boq_item_material($selected_id)
{
    $sql = "SELECT *
            FROM ".TB_PREF."boq_item_material
            WHERE id=".db_escape($selected_id);
    
    $result = db_query($sql);
    return db_fetch($result);    
}


function get_est_boq_item_material_id_for_update($boq_item_id)
{
	$sql = "SELECT *                   
                FROM ".TB_PREF."boq_item_material               
                WHERE  boq_item_id=".db_escape($boq_item_id);   
                               
  
	$result = db_query($sql, "The boq_item_material could not be retrieved");
        return db_fetch($result);
}


function get_est_boq_item_material_details($boq_item_id, $detail_type_code)
{
    $sql = "SELECT boqm.*, u.name unit_name 
            FROM ".TB_PREF."est_project_boq_item_material boqm,
                 ".TB_PREF."item_units u  
            WHERE boqm.tran_unit = u.abbr 
                AND boqm.boq_item_id=".db_escape($boq_item_id)."  
                AND boqm.detail_type_code=".db_escape($detail_type_code);     

        return db_query($sql);
  
}

function recalculate_est_boq_item_component_material($boq_item_id, $detail_type_code, $primary_type)
{
    $primary_cost_total = recalculate_est_boq_item_component_primary_material_sum($boq_item_id, $detail_type_code);
    return update_est_boq_item_component_total_cost($boq_item_id, $detail_type_code, $primary_type, $primary_cost_total);
}

function recalculate_est_boq_item_component_primary_material_sum($boq_item_id, $detail_type_code)
{
    $sql = "SELECT SUM(tran_unit_cost * tran_qty) total_cost 
            FROM ".TB_PREF."est_project_boq_item_material 
            WHERE boq_item_id=".db_escape($boq_item_id)." AND 
                    detail_type_code=".db_escape($detail_type_code);
    
    $result = db_query($sql);
    $total_cost = db_fetch($result); 
    if(isset($total_cost['total_cost']) && $total_cost['total_cost'] > 0){
        return $total_cost['total_cost'];
    }
    else{
        return 0;
    }
    
}

function delete_est_boq_item_component_primary_material($boq_item_id, $detail_type_code)
{
    $sql="DELETE FROM ".TB_PREF."est_project_boq_item_material 
          WHERE boq_item_id = ".db_escape($boq_item_id)." 
            AND detail_type_code = ".db_escape($detail_type_code);
    
    db_query($sql,"Could not delete_est_boq_item_component_primary_material");		
}

?>