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





function get_target_boq_item_group_boq_item_component_details($project_id, $boq_item_code, $primary_type, $detail_type_code,
                                                                $item_id, $item_code, $item_description, $unit, $item_qty, 
                                                                $item_tran_unit_cost, $rate_id)
{
 
    switch ($primary_type) {
        case BOQPT_MATERIAL:
            $sql = get_target_boq_item_group_boq_item_component_BOQPT_MATERIAL($project_id, $boq_item_code, $detail_type_code,
                                                                    $item_code, $unit, $item_qty, $item_tran_unit_cost);
            break;
        case BOQPT_COST:
            $sql = get_target_boq_item_group_boq_item_component_BOQPT_COST($project_id, $boq_item_code, $detail_type_code,
                                                                    $item_description, $unit, $item_qty, $item_tran_unit_cost);
            break;
        case BOQPT_LABOUR:
            $sql = get_target_boq_item_group_boq_item_component_BOQPT_LABOUR($project_id, $boq_item_code, $detail_type_code,
                                                                    $item_id, $item_tran_unit_cost, $item_qty);
            break;
        case BOQPT_ASSET:
            $sql = get_target_boq_item_group_boq_item_component_BOQPT_ASSET($project_id, $boq_item_code, $detail_type_code,
                                                                    $item_id, $rate_id, $item_qty, $item_tran_unit_cost);
            break;
        case BOQPT_TRANSPORT:
            $sql = get_target_boq_item_group_boq_item_component_BOQPT_TRANSPORT($project_id, $boq_item_code, $detail_type_code,
                                                                    $item_id, $item_qty, $item_tran_unit_cost);
            break;

        default:
            break;
    }        
        

    return $sql;
    
}

function get_target_boq_item_group_boq_item_component_BOQPT_TRANSPORT($project_id, $boq_item_code, $detail_type_code,
                                                                    $item_id, $item_qty, $item_tran_unit_cost)
{
 
        $sql = "SELECT  boq.id AS boq_item_id,
                        boq.code AS boq_item_code,                           
                        
                        ct.id AS detail_id,
                        0 AS item_type,
                        ct.vehicle_type_id AS item_id,
                        lv.value_code AS item_code,
                        '' AS unit,
                        lv.value_name AS item_description,
                        ct.days AS item_qty,
                        ct.rate AS item_tran_unit_cost,
                        ct.comment AS item_comment,
                        
                        boqc.id component_id,
                        boqc.detail_type_code,
                        boqc.primary_type 
                
                FROM ".TB_PREF."project_boq_items boq 
                    JOIN ".TB_PREF."project_boq_item_components boqc 
                        ON boqc.boq_item_id = boq.id 
                        AND boqc.detail_type_code = ".  db_escape($detail_type_code)."  

                    JOIN ".TB_PREF."project_boq_item_transport ct 
                        ON ct.boq_item_id = boqc.boq_item_id
                        AND ct.detail_type_code = boqc.detail_type_code
                    JOIN ".TB_PREF."list_values lv 
                        ON lv.id = ct.vehicle_type_id  
    
                WHERE boq.project_id = ".db_escape($project_id)."                         
                        AND boq.code = ".  db_escape($boq_item_code)." 
                            
                        AND ct.vehicle_type_id = ".db_escape($item_id)."   
                        AND ct.rate = ".db_escape($item_tran_unit_cost)." 
                        AND ct.days = ".db_escape($item_qty);             

	return db_query($sql,"get_target_boq_item_group_boq_item_component_BOQPT_ASSET");
    
}

function get_target_boq_item_group_boq_item_component_BOQPT_ASSET($project_id, $boq_item_code, $detail_type_code,
                                                                    $item_id, $rate_id, $item_qty, $item_tran_unit_cost)
{
 
        $sql = "SELECT  boq.id AS boq_item_id,
                        boq.code AS boq_item_code,                           
                        
                        ca.id AS detail_id,
                        0 AS item_type,
                        ca.asset_id AS item_id,
                        am.asset_code AS item_code,
                        '' AS unit,
                        am.description AS item_description,
                        ca.days AS item_qty,
                        ca.rate AS item_tran_unit_cost,
                        ca.comment AS item_comment,
                        
                        boqc.id component_id,
                        boqc.detail_type_code,
                        boqc.primary_type  
                
                FROM ".TB_PREF."project_boq_items boq  
                    JOIN ".TB_PREF."project_boq_item_components boqc 
                        ON boqc.boq_item_id = boq.id 
                        AND boqc.detail_type_code = ".  db_escape($detail_type_code)."  
                        
                    JOIN ".TB_PREF."project_boq_item_assets ca 
                        ON ca.boq_item_id = boqc.boq_item_id 
                        AND ca.detail_type_code = boqc.detail_type_code 
                    JOIN ".TB_PREF."asset_master am 
                        ON am.asset_id = ca.asset_id  

                WHERE boq.project_id = ".db_escape($project_id)."                         
                        AND boq.code = ".  db_escape($boq_item_code)."   
                            
                        AND ca.asset_id = ".db_escape($item_id)."  
                        AND ca.rate_category_id = ".db_escape($rate_id)." 
                        AND ca.rate = ".db_escape($item_tran_unit_cost)." 
                        AND ca.days = ".db_escape($item_qty);        

	return db_query($sql,"get_target_boq_item_group_boq_item_component_BOQPT_ASSET");
    
}


function get_target_boq_item_group_boq_item_component_BOQPT_LABOUR($project_id, $boq_item_code, $detail_type_code,
                                                                    $item_id, $item_tran_unit_cost, $item_qty)
{
 
        $sql = "SELECT  boq.id AS boq_item_id,
                        boq.code AS boq_item_code,                           
                        
                        cl.id AS detail_id,
                        0 AS item_type,
                        cl.rate_category_id AS item_id,
                        '' AS item_code,
                        '' AS unit,
                        er.description AS item_description,
                        cl.days AS item_qty,
                        cl.rate AS item_tran_unit_cost,
                        cl.comment AS item_comment,
                        
                        boqc.id component_id,
                        boqc.detail_type_code,
                        boqc.primary_type  
                
                FROM ".TB_PREF."project_boq_items boq  
                    JOIN ".TB_PREF."project_boq_item_components boqc 
                        ON boqc.boq_item_id = boq.id 
                        AND boqc.detail_type_code = ".  db_escape($detail_type_code)."  
                            
                    JOIN ".TB_PREF."project_boq_item_labour cl 
                        ON cl.boq_item_id = boqc.boq_item_id
                        AND cl.detail_type_code = boqc.detail_type_code 
                    LEFT JOIN ".TB_PREF."employee_rates er 
                        ON er.id = cl.rate_category_id 

                WHERE boq.project_id = ".db_escape($project_id)."                         
                        AND boq.code = ".  db_escape($boq_item_code)." 
                        AND cl.rate_category_id = ".db_escape($item_id)."  
                        AND cl.rate = ".db_escape($item_tran_unit_cost)." 
                        AND cl.days = ".db_escape($item_qty);         

	return db_query($sql,"get_target_boq_item_group_boq_item_component_BOQPT_LABOUR");
    
}


function get_target_boq_item_group_boq_item_component_BOQPT_COST($project_id, $boq_item_code, $detail_type_code,
                                                                    $item_description, $unit, $item_qty, $item_tran_unit_cost)
{
 
        $sql = "SELECT boq.id AS boq_item_id,
                        boq.code AS boq_item_code,   
                        
                        cc.id AS detail_id,
                        0 AS item_type,
                        0 AS item_id,
                        '' AS item_code,
                        cc.unit_name AS unit,
                        cc.description AS item_description,
                        cc.quantity AS item_qty,
                        cc.rate AS item_tran_unit_cost,
                        cc.comment AS item_comment,
                        
                        boqc.id component_id,
                        boqc.detail_type_code,
                        boqc.primary_type  
                
                FROM ".TB_PREF."project_boq_items boq  
                    JOIN ".TB_PREF."project_boq_item_components boqc 
                        ON boqc.boq_item_id = boq.id 
                        AND boqc.detail_type_code = ".  db_escape($detail_type_code)."  
                    JOIN ".TB_PREF."project_boq_item_costs cc 
                        ON cc.boq_item_id = boqc.boq_item_id 
                        AND cc.detail_type_code = boqc.detail_type_code 
                WHERE boq.project_id = ".db_escape($project_id)."                         
                        AND boq.code = ".  db_escape($boq_item_code)." 
                        
                        AND cc.description = ".db_escape($item_description)."  
                        AND cc.unit_name = ".db_escape($unit)." 
                        AND cc.quantity = ".db_escape($item_qty)." 
                        AND cc.rate = ".db_escape($item_tran_unit_cost);  
                     

	return db_query($sql,"get_target_boq_item_group_boq_item_component_BOQPT_COST");
    
}


function get_target_boq_item_group_boq_item_component_BOQPT_MATERIAL($project_id, $boq_item_code, $detail_type_code,
                                                                    $item_code, $unit, $item_qty, $item_tran_unit_cost)
{
 
        $sql = "SELECT boq.id AS boq_item_id,
                        boq.code AS boq_item_code,   
                        
                        cm.id AS detail_id,
                        cm.item_type AS item_type,
                        '' AS item_id,
                        cm.stock_id AS item_code,
                        cm.tran_unit AS unit,
                        '' AS item_description,
                        cm.tran_qty AS item_qty,
                        cm.tran_unit_cost AS item_tran_unit_cost,
                        cm.comment AS item_comment,
                        
                        boqc.id component_id,
                        boqc.detail_type_code,
                        boqc.primary_type  
                
                FROM ".TB_PREF."project_boq_items boq  
                    JOIN ".TB_PREF."project_boq_item_components boqc 
                        ON boqc.boq_item_id = boq.id 
                        AND boqc.detail_type_code = ".  db_escape($detail_type_code)."  
                    JOIN ".TB_PREF."project_boq_item_material cm 
                        ON cm.boq_item_id = boqc.boq_item_id 
                        AND cm.detail_type_code = boqc.detail_type_code 
                WHERE boq.project_id = ".db_escape($project_id)."                        
                        AND boq.code = ".  db_escape($boq_item_code)."                   
                            
                        AND cm.stock_id = ".db_escape($item_code)."  
                        AND cm.tran_unit = ".db_escape($unit)." 
                        AND cm.tran_qty = ".db_escape($item_qty)." 
                        AND cm.tran_unit_cost = ".db_escape($item_tran_unit_cost);                       
                                             
        
	return db_query($sql,"get_target_boq_item_group_boq_item_component_BOQPT_MATERIAL");
    
}





function get_target_no_boq_item_group_boq_item_component_details($project_id, $boq_item_code, $primary_type, $detail_type_code,
                                                                    $item_id, $item_code, $item_description, $unit, $item_qty, $item_tran_unit_cost, $rate_id)
{
 
    switch ($primary_type) {
        case BOQPT_MATERIAL:
            $sql = get_target_no_boq_item_group_boq_item_component_BOQPT_MATERIAL($project_id, $boq_item_code, $detail_type_code,
                                                                    $item_code, $unit, $item_qty, $item_tran_unit_cost);
            break;
        case BOQPT_COST:
            $sql = get_target_no_boq_item_group_boq_item_component_BOQPT_COST($project_id, $boq_item_code, $detail_type_code,
                                                                    $item_description, $unit, $item_qty, $item_tran_unit_cost);
            break;
        case BOQPT_LABOUR:
            $sql = get_target_no_boq_item_group_boq_item_component_BOQPT_LABOUR($project_id, $boq_item_code, $detail_type_code,
                                                                    $item_id, $item_tran_unit_cost, $item_qty);
            break;
        case BOQPT_ASSET:
            $sql = get_target_no_boq_item_group_boq_item_component_BOQPT_ASSET($project_id, $boq_item_code, $detail_type_code,
                                                                    $item_id, $rate_id, $item_qty, $item_tran_unit_cost);
            break;
        case BOQPT_TRANSPORT:
            $sql = get_target_no_boq_item_group_boq_item_component_BOQPT_TRANSPORT($project_id, $boq_item_code, $detail_type_code,
                                                                    $item_id, $item_qty, $item_tran_unit_cost);
            break;

        default:
            break;
    }        
        

    return $sql;
    
}


function get_target_no_boq_item_group_boq_item_component_BOQPT_TRANSPORT($project_id, $boq_item_code, $detail_type_code,
                                                                    $item_id, $item_qty, $item_tran_unit_cost)
{
 
        $sql = "SELECT 0 AS boq_group_id,
                        boq.project_id AS project_id,
                        boq.id AS id,
                        boq.code AS code,
                        boq.description AS description,
                        boq.group_id AS group_id,
                        boqc.detail_type_code AS detail_type_code,
                        boqc.primary_type AS primary_type,  
                        
                        ct.id AS detail_id,
                        0 AS item_type,
                        ct.vehicle_type_id AS item_id,
                        lv.value_code AS item_code,
                        '' AS unit,
                        lv.value_name AS item_description,
                        ct.days AS item_qty,
                        ct.rate AS item_tran_unit_cost,
                        ct.comment AS item_comment 

                
                FROM ".TB_PREF."project_boq_items boq  
                    LEFT JOIN ".TB_PREF."project_boq_item_components boqc 
                        ON boqc.boq_item_id = boq.id 
                        AND boqc.detail_type_code = ".  db_escape($detail_type_code)."  

                    LEFT JOIN ".TB_PREF."project_boq_item_transport ct 
                        ON ct.boq_item_id = boqc.boq_item_id
                        AND ct.detail_type_code = boqc.detail_type_code
                        AND ct.vehicle_type_id = ".db_escape($item_id)."   
                        AND ct.rate = ".db_escape($item_tran_unit_cost)." 
                        AND ct.days = ".db_escape($item_qty)."    
                    LEFT JOIN ".TB_PREF."list_values lv 
                        ON lv.id = ct.vehicle_type_id  
    
                WHERE boq.project_id = ".db_escape($project_id)."          
                        AND boq.code = ".  db_escape($boq_item_code)."  
                        AND boq.group_id = 0";              

	return db_query($sql,"get_target_boq_item_group_boq_item_component_BOQPT_ASSET");
    
}

function get_target_no_boq_item_group_boq_item_component_BOQPT_ASSET($project_id, $boq_item_code, $detail_type_code,
                                                                    $item_id, $rate_id, $item_qty, $item_tran_unit_cost)
{
 
        $sql = "SELECT 0 AS boq_group_id,
                        boq.project_id AS project_id,
                        boq.id AS id,
                        boq.code AS code,
                        boq.description AS description,
                        boq.group_id AS group_id,
                        boqc.detail_type_code AS detail_type_code,
                        boqc.primary_type AS primary_type,  
                        
                        ca.id AS detail_id,
                        0 AS item_type,
                        ca.asset_id AS item_id,
                        am.asset_code AS item_code,
                        '' AS unit,
                        am.description AS item_description,
                        ca.days AS item_qty,
                        ca.rate AS item_tran_unit_cost,
                        ca.comment AS item_comment 
                
                FROM ".TB_PREF."project_boq_items boq  
                    LEFT JOIN ".TB_PREF."project_boq_item_components boqc 
                        ON boqc.boq_item_id = boq.id 
                        AND boqc.detail_type_code = ".  db_escape($detail_type_code)."  
                        
                    LEFT JOIN ".TB_PREF."project_boq_item_assets ca 
                        ON ca.boq_item_id = boqc.boq_item_id 
                        AND ca.detail_type_code = boqc.detail_type_code  
                        AND ca.asset_id = ".db_escape($item_id)."  
                        AND ca.rate_category_id = ".db_escape($rate_id)." 
                        AND ca.rate = ".db_escape($item_tran_unit_cost)." 
                        AND ca.days = ".db_escape($item_qty)." 
                    LEFT JOIN ".TB_PREF."asset_master am 
                        ON am.asset_id = ca.asset_id  

                WHERE boq.project_id = ".db_escape($project_id)."          
                        AND boq.code = ".  db_escape($boq_item_code)."  
                        AND boq.group_id = 0";              

	return db_query($sql,"get_target_boq_item_group_boq_item_component_BOQPT_ASSET");
    
}


function get_target_no_boq_item_group_boq_item_component_BOQPT_LABOUR($project_id, $boq_item_code, $detail_type_code,
                                                                    $item_id, $item_tran_unit_cost, $item_qty)
{
 
        $sql = "SELECT 0 AS boq_group_id,
                        boq.project_id AS project_id,
                        boq.id AS id,
                        boq.code AS code,
                        boq.description AS description,
                        boq.group_id AS group_id,
                        boqc.detail_type_code AS detail_type_code,
                        boqc.primary_type AS primary_type, 
                        
                        cl.id AS detail_id,
                        0 AS item_type,
                        cl.rate_category_id AS item_id,
                        '' AS item_code,
                        '' AS unit,
                        er.description AS item_description,
                        cl.days AS item_qty,
                        cl.rate AS item_tran_unit_cost,
                        cl.comment AS item_comment 
                
                FROM ".TB_PREF."project_boq_items boq  
                    LEFT JOIN ".TB_PREF."project_boq_item_components boqc 
                        ON boqc.boq_item_id = boq.id 
                        AND boqc.detail_type_code = ".  db_escape($detail_type_code)."  
                            
                    LEFT JOIN ".TB_PREF."project_boq_item_labour cl 
                        ON cl.boq_item_id = boqc.boq_item_id
                        AND cl.detail_type_code = boqc.detail_type_code 
                        AND cl.rate_category_id = ".db_escape($item_id)."  
                        AND cl.rate = ".db_escape($item_tran_unit_cost)." 
                        AND cl.days = ".db_escape($item_qty)."  
                    LEFT JOIN ".TB_PREF."employee_rates er 
                        ON er.id = cl.rate_category_id 

                WHERE boq.project_id = ".db_escape($project_id)."          
                        AND boq.code = ".  db_escape($boq_item_code)."  
                        AND boq.group_id = 0";              

	return db_query($sql,"get_target_boq_item_group_boq_item_component_BOQPT_LABOUR");
    
}


function get_target_no_boq_item_group_boq_item_component_BOQPT_COST($project_id, $boq_item_code, $detail_type_code,
                                                                    $item_description, $unit, $item_qty, $item_tran_unit_cost)
{
 
        $sql = "SELECT 0 AS boq_group_id,
                        boq.project_id AS project_id,
                        boq.id AS id,
                        boq.code AS code,
                        boq.description AS description,
                        boq.group_id AS group_id,
                        boqc.detail_type_code AS detail_type_code,
                        boqc.primary_type AS primary_type,
                        cc.id AS detail_id,
                        0 AS item_type,
                        0 AS item_id,
                        '' AS item_code,
                        cc.unit_name AS unit,
                        cc.description AS item_description,
                        cc.quantity AS item_qty,
                        cc.rate AS item_tran_unit_cost,
                        cc.comment AS item_comment 
                
                FROM ".TB_PREF."project_boq_items boq    
                    LEFT JOIN ".TB_PREF."project_boq_item_components boqc 
                        ON boqc.boq_item_id = boq.id 
                        AND boqc.detail_type_code = ".  db_escape($detail_type_code)."  
                    LEFT JOIN ".TB_PREF."project_boq_item_costs cc 
                        ON cc.boq_item_id = boqc.boq_item_id 
                        AND cc.detail_type_code = boqc.detail_type_code 
                        AND cc.description = ".db_escape($item_description)."  
                        AND cc.unit_name = ".db_escape($unit)." 
                        AND cc.quantity = ".db_escape($item_qty)." 
                        AND cc.rate = ".db_escape($item_tran_unit_cost)."  

                WHERE boq.project_id = ".db_escape($project_id)."          
                        AND boq.code = ".  db_escape($boq_item_code)."  
                        AND boq.group_id = 0";              

	return db_query($sql,"get_target_boq_item_group_boq_item_component_BOQPT_COST");
    
}


function get_target_no_boq_item_group_boq_item_component_BOQPT_MATERIAL($project_id, $boq_item_code, $detail_type_code,
                                                                    $item_code, $unit, $item_qty, $item_tran_unit_cost)
{
 
        $sql = "SELECT 0 AS boq_group_id,
                        boq.project_id AS project_id,
                        boq.id AS id,
                        boq.code AS code,
                        boq.description AS description,
                        boq.group_id AS group_id,
                        boqc.detail_type_code AS detail_type_code,
                        boqc.primary_type AS primary_type,
                        cm.id AS detail_id,
                        cm.item_type AS item_type,
                        '' AS item_id,
                        cm.stock_id AS item_code,
                        cm.tran_unit AS unit,'' AS item_description,
                        cm.tran_qty AS item_qty,
                        cm.tran_unit_cost AS item_tran_unit_cost,
                        cm.comment AS item_comment 
                
                FROM ".TB_PREF."project_boq_items boq  
                    LEFT JOIN ".TB_PREF."project_boq_item_components boqc 
                        ON boqc.boq_item_id = boq.id 
                        AND boqc.detail_type_code = ".  db_escape($detail_type_code)."  
                    LEFT JOIN ".TB_PREF."project_boq_item_material cm 
                        ON cm.boq_item_id = boqc.boq_item_id 
                        AND cm.detail_type_code = boqc.detail_type_code   
                        AND cm.stock_id = ".db_escape($item_code)."  
                        AND cm.tran_unit = ".db_escape($unit)." 
                        AND cm.tran_qty = ".db_escape($item_qty)." 
                        AND cm.tran_unit_cost = ".db_escape($item_tran_unit_cost)." 
                WHERE boq.project_id = ".db_escape($project_id)."          
                        AND boq.code = ".  db_escape($boq_item_code)."  
                        AND boq.group_id = 0";              
                        
                                             
        
	return db_query($sql,"get_target_boq_item_group_boq_item_component_BOQPT_MATERIAL");
    
}

