<?php //
/**********************************************************************
***********************************************************************/
function add_est_project_boq_item($project_id, $type_id, $display_order, $code, $description, $billing_type, $unit, $rate,  $max_quantity, 
                                  $long_description, $print_long_description, $tax_group_id,  $group_id)                                    
{
                $id_user = $_SESSION['wa_current_user']->user;
                $datetime = date('Y-m-d H:i:s');
                
        $sql = "INSERT INTO ".TB_PREF."est_project_boq_items 
                            (est_project_id, type_id, display_order, 
                             code, description, 
                             billing_type, unit, rate, 
                             max_quantity, long_description, 
                             print_long_description, tax_group_id, 
                             group_id, 
                             created_by, created_on) 
                  
                VALUES (".db_escape($project_id).", ".db_escape($type_id).", ".db_escape($display_order).", "
                         .db_escape($code).", ".db_escape($description).", "
                         .db_escape($billing_type).", ".db_escape($unit).", ".db_escape($rate).", "
                         .db_escape($max_quantity).", ".db_escape($long_description).", "
                         .db_escape($print_long_description).", ".db_escape($tax_group_id).", "
                         .db_escape($group_id).", "
                         .db_escape($id_user).", ".db_escape($datetime).")";

        db_query($sql,"Estimation Project BOQ Item could not be added");	
        
        return db_insert_id();
}

function update_est_project_boq_item($selected_id, $display_order, $type_id, $code, $description, $billing_type, $unit, $rate, $max_quantity, 
                                     $long_description, $print_long_description, $tax_group_id, $group_id)
{
                $id_user = $_SESSION['wa_current_user']->user;
                $datetime = date('Y-m-d H:i:s');
        $sql = "UPDATE ".TB_PREF."est_project_boq_items
                SET
                    type_id=".db_escape($type_id).", 
                    display_order=".db_escape($display_order).",
                    code=".db_escape($code).", 
                    description=".db_escape($description).",  
                    billing_type=".db_escape($billing_type).", 
                    unit=".db_escape($unit).", 
                    rate=".db_escape($rate).",                      
                    max_quantity=".db_escape($max_quantity).", 
                    long_description=".db_escape($long_description).",                  
                    print_long_description=".db_escape($print_long_description).",  
                    tax_group_id=".db_escape($tax_group_id).",                   
                    group_id=".db_escape($group_id).",
                    updated_by=".db_escape($id_user).",
                    updated_on=".db_escape($datetime)."
                        
                WHERE id=".db_escape($selected_id);

        db_query($sql,"Estimation project BOQ item could not be updated");	
        
        return db_num_affected_rows();
}

function delete_est_boq_item($selected_id)
{
	$sql="DELETE FROM ".TB_PREF."est_project_boq_items WHERE id= ".db_escape($selected_id);
	db_query($sql,"Estimation Project BOQ item could not be deleted");			
}

function get_est_project_boq_items($est_project_id)
{
	$sql = "SELECT pbi.*,
                       pt.description parent_description,
                       tg.name tax_name,
                       pbig.code billing_item_group_code,
                       pbig.description billing_item_group_description                       
                       
                FROM ".TB_PREF."est_project_boq_items pbi 
                    LEFT JOIN ".TB_PREF."tax_groups tg ON 
                	pbi.tax_group_id = tg.id 
                    LEFT JOIN ".TB_PREF."est_project_boq_items pt ON 
                        pt.id = pbi.group_id
                    LEFT JOIN ".TB_PREF."est_project_boq_item_groups pbig ON
                        pbi.group_id = pbig.id 
                    
                WHERE pbi.est_project_id=".db_escape($est_project_id)." 
                ORDER BY display_order, type, code";
                     
	return db_query($sql,"Estimation Project BOQ items could not be retreived");
        
}

function get_est_project_group_boq_items($est_project_id, $boq_group_id)
{
    if(!($boq_group_id > 0)){
        $boq_group_id = 0;
    }
        
	$sql = "SELECT pbi.*,
                       pt.description parent_description,
                       tg.name tax_name,
                       pbig.code billing_item_group_code,
                       pbig.description billing_item_group_description                       
                       
                FROM ".TB_PREF."est_project_boq_items pbi 
                    LEFT JOIN ".TB_PREF."tax_groups tg 
                        ON pbi.tax_group_id = tg.id 
                    LEFT JOIN ".TB_PREF."est_project_boq_items pt 
                        ON pt.id = pbi.group_id
                    LEFT JOIN ".TB_PREF."est_project_boq_item_groups pbig 
                        ON pbi.group_id = pbig.id 
                    
                WHERE pbi.est_project_id = ".db_escape($est_project_id)." 
                    AND pbi.group_id = ".  db_escape($boq_group_id)." 
                ORDER BY display_order, billing_type, code";
                     
	return db_query($sql,"Estimation Project BOQ items could not be retreived");
        
}

function get_project_and_est_project_group_boq_items($est_project_id, $sys_project_id, $sys_boq_group_id, $est_boq_group_id)
{
       
    $sql = "SELECT esti.*,
                    sysi.id sys_project_boq_item_id,
                    esti.id est_project_boq_item_id 

            FROM ".TB_PREF."est_project_boq_items esti
                JOIN ".TB_PREF."project_boq_items sysi 
                    ON esti.code = sysi.code 
                    AND esti.group_id = sysi.group_id 

            WHERE esti.group_id = ".db_escape($est_boq_group_id)." 
                AND sysi.group_id = ".db_escape($sys_boq_group_id)." 
                AND sysi.project_id = ".db_escape($sys_project_id)." 
                AND esti.est_project_id = ".db_escape($est_project_id)." 
            ORDER BY esti.display_order";      

                     
	return db_query($sql,"get_project_and_est_project_group_boq_items could not be retreived");
        
}

function get_est_group_boq_items($boq_group_id)
{
	$sql = "SELECT pbi.*,
                    pt.description parent_description,
                    tg.name tax_name,
                    pbig.code billing_item_group_code,
                    pbig.description billing_item_group_description                       
            FROM ".TB_PREF."project_boq_items pbi 
                    LEFT JOIN ".TB_PREF."tax_groups tg ON 
                        pbi.tax_group_id = tg.id 
                    LEFT JOIN ".TB_PREF."project_boq_items pt ON 
                        pt.id = pbi.group_id
                    LEFT JOIN ".TB_PREF."project_boq_item_groups pbig ON
                        pbi.group_id = pbig.id 
            WHERE pbi.group_id=".db_escape($boq_group_id)." 
                    ORDER BY display_order, billing_type, code";
                     
	return db_query($sql,"Project billing items could not be retreived");
}

function get_est_project_boq_item_details($id)
{
	$sql = "SELECT pbi.*,
                       pt.description parent_description,
                       tg.name tax_name,
                       pbig.code billing_item_group_code,
                       pbig.description billing_item_group_description,
                       u.name unit_name 
                       
                FROM ".TB_PREF."est_project_boq_items pbi 
                        LEFT JOIN ".TB_PREF."tax_groups tg ON 
                                pbi.tax_group_id = tg.id 
                        LEFT JOIN ".TB_PREF."est_project_boq_items pt ON 
                                pt.id = pbi.group_id
                        LEFT JOIN ".TB_PREF."est_project_boq_item_groups pbig ON
                                pbi.group_id = pbig.id 
                        LEFT JOIN ".TB_PREF."item_units u ON
                                pbi.unit = u.abbr 
                    
                WHERE pbi.id=".db_escape($id);
	
	$result = db_query($sql,"Selected Estimation project BOQ detail could not be retreived");	
	return db_fetch($result);
}

function get_est_project_boq_item_record($id)
{
	$sql = "SELECT epbi.*
                FROM ".TB_PREF."est_project_boq_items epbi                     
                WHERE epbi.id=".db_escape($id);
	
	$result = db_query($sql,"Could not get estimation project boq item record");	
	return db_fetch($result);
}

function get_est_top_level_billing_items($project_id)
{
    $sql = "SELECT * 
                     
                FROM ".TB_PREF."est_project_billing_items  
                WHERE  project_id=".db_escape($project_id)." 
                    AND group_id = 0 
                ORDER BY display_order";
	
	return db_query($sql,"Could not get_top_level_billing_items");
}

function get_est_child_billing_items($group_id)
{
    $sql = "SELECT * 
                FROM ".TB_PREF."est_project_billing_items  
                WHERE  group_id=".db_escape($group_id)." 
                ORDER BY display_order";
	
	return db_query($sql,"Could not get_child_billing_items");
}

function get_existing_code_from_est_project_boq_items($project_id, $code, $selected_id)
{
    $sql = "SELECT id, code
            FROM ".TB_PREF."est_project_boq_items
            WHERE est_project_id = ".db_escape($project_id)."
                AND code = ".db_escape($code)." 
                AND id <> ".db_escape($selected_id);
    
    $result = db_query($sql, "Could not get existing code");
    return db_num_rows($result);    
}

function update_est_boq_item_balances($boq_item_id, $total_qty, $last_entry_date)
{
    $sql = "UPDATE ".TB_PREF."est_project_boq_items
                SET                      
                    completed_qty = ".db_escape($total_qty).", 
                    last_entry_date=".db_escape($last_entry_date)." 
                WHERE id=".db_escape($boq_item_id);

        db_query($sql,"Could not update_boq_item_balances");		
}

function get_est_boq_item_components($boq_item_id)
{

    $sql = "SELECT dt.*,
                    dtn.value_name detail_type_name,
                    dtn.value_code,
                    cn.value_name category_name 
            FROM ".TB_PREF."list_values dtn  
                    LEFT JOIN ".TB_PREF."est_project_boq_item_components dt 
                        ON dt.detail_type_code = dtn.value_code 
                        AND dt.boq_item_id = ".db_escape($boq_item_id)."                                 
                    LEFT JOIN ".TB_PREF."project_task_detail_types ptdt ON 
                            ptdt.detail_type_code = dtn.value_code 
                    LEFT JOIN ".TB_PREF."list_values cn ON 
                            ptdt.category_id = cn.id 
            WHERE dtn.list_code = 'PROJECT_TASK_DETAIL_TYPES'"; 

        
	return db_query($sql,"Could not get_boq_item_components");
    
}

function get_est_boq_item_active_components($boq_item_id)
{

    $sql = "SELECT dt.*,
                    dtn.value_name detail_type_name,
                    dtn.value_code 
            FROM ".TB_PREF."list_values dtn  
                    JOIN ".TB_PREF."est_project_boq_item_components dt 
                        ON dt.detail_type_code = dtn.value_code 
                        AND dt.boq_item_id = ".db_escape($boq_item_id)."                             
            WHERE dtn.list_code = 'PROJECT_TASK_DETAIL_TYPES'"; 

        
	return db_query($sql,"get_est_boq_item_active_components");
    
}

function get_project_and_est_project_boq_item_groups_components($sys_project_boq_item_id, $est_project_boq_item_id)
{

    $sql = "SELECT estic.*,
                    sysic.id sys_project_boq_component_id,
                    estic.id est_project_boq_component_id 
                        
            FROM ".TB_PREF."est_project_boq_item_components estic
                JOIN ".TB_PREF."project_boq_item_components sysic 
                    ON esti.boq_item_id = sysi.boq_item_id 

            WHERE estic.boq_item_id = ".db_escape($est_project_boq_item_id)." 
                AND sysic.boq_item_id = ".db_escape($sys_project_boq_item_id)." 
            ORDER BY estic.boq_item_id";

        
	return db_query($sql,"Could not get_project_and_est_project_boq_item_groups_components");

}

function create_est_boq_item_component_record($boq_item_id, $detail_type_code, $promary_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_components 
                    (boq_item_id, detail_type_code, primary_type)                     
            VALUES(".db_escape($boq_item_id).", ".db_escape($detail_type_code).", ".db_escape($promary_type).")"; 
   
   db_query($sql, "Could not create estimation boq item component record");    
}

function recalculate_est_boq_item($boq_item_id)
{
    $total_cost = get_est_boq_item_components_total_cost($boq_item_id);
    return update_est_boq_item_total_cost($boq_item_id, $total_cost);
}

function update_est_boq_item_total_cost($boq_item_id, $total_cost)
{
    $sql = "UPDATE ".TB_PREF."est_project_boq_items  
            SET calculated_cost=".db_escape($total_cost).",
                row_state = row_state + 1 
            WHERE id=".db_escape($boq_item_id);
                    
    db_query($sql, "Could not update estimation boq item total cost"); 
    
    return db_num_affected_rows();
}


function delete_est_boq_item_component($boq_item_id, $boq_item_component_id)
{
    $boq_item_component = get_est_boq_item_component_record_by_id($boq_item_component_id);
    delete_est_boq_item_component_primary_types($boq_item_id, $boq_item_component['detail_type_code']);
            
    $n1 = delete_est_boq_item_component_record($boq_item_component_id);
    if(!($n1 == 1)){
        return array('success' => false);
    }
    
    $n2 = recalculate_est_boq_item($boq_item_id);
    if(!($n2 == 1)){
        return array('success' => false);
    }
    else{
        return array('success' => true);
    }
}

function get_est_project_boq_item_record_by_code($est_project_id, $code)
{
    $sql = "SELECT pbi.*
                FROM ".TB_PREF."est_project_boq_items pbi                     
                WHERE pbi.est_project_id = ".db_escape($est_project_id)." AND pbi.code=".db_escape($code);
	
	$result = db_query($sql,"Could not get_est_project_boq_item_record_by_code");	
	return db_fetch($result);
}


function update_est_project_boq_item_pa_quntities($id, $cumulative_qty, $certi_cuml_qty)
{
    $sql = "UPDATE ".TB_PREF."project_boq_items
            SET cumulative_qty=".db_escape($cumulative_qty).", 
                certi_cuml_qty=".db_escape($certi_cuml_qty)."
                    
            WHERE id=".db_escape($id);
                
    db_query($sql);    
}


function get_est_boq_group_boq_items_summary($boq_group_id, $est_project_id)
{
    $sql = "SELECT SUM(calculated_cost) total 
            FROM ".TB_PREF."est_project_boq_items  
            WHERE 1 = 1 "; 
    
    if($boq_group_id > 0){
        $sql .= " AND group_id=".  db_escape($boq_group_id);
    }
    else{
        $sql .= " AND est_project_id=".  db_escape($est_project_id)." AND group_id=".  db_escape($boq_group_id);
    }

    $result = db_query($sql,"get_est_boq_group_boq_items_summary");
    return db_fetch($result);
    
}


function add_est_project_boq_item_component($boq_item_id, $detail_type_code, $primary_type, $cost, $row_state)                                    
{
    $id_user = $_SESSION['wa_current_user']->user;
    $datetime = date('Y-m-d H:i:s');
                
        $sql = "INSERT INTO ".TB_PREF."est_project_boq_item_components  
                            ( boq_item_id, detail_type_code, 
                              primary_type, cost, 
                              row_state, 
                              created_by, created_on ) 
                  
                VALUES (".db_escape($boq_item_id).", ".db_escape($detail_type_code).", "
                         .db_escape($primary_type).", ".db_escape($cost).", "
                         .db_escape($row_state).", " 
                         .db_escape($id_user).", ".db_escape($datetime).")";

        db_query($sql,"Estimation Project BOQ Item Component could not be added");	
        
        return db_insert_id();
}


function update_est_project_boq_item_component($id, $boq_item_id, $detail_type_code, $primary_type, $cost, $row_state)
{
    $id_user = $_SESSION['wa_current_user']->user;
    $datetime = date('Y-m-d H:i:s');
    
        $sql = "UPDATE ".TB_PREF."est_project_boq_item_components 
                SET
                    boq_item_id = ".db_escape($boq_item_id).", 
                    detail_type_code = ".db_escape($detail_type_code).",
                    primary_type = ".db_escape($primary_type).",
                    cost = ".db_escape($cost).",
                    row_state = ".db_escape($row_state).",
                    updated_by = ".db_escape($id_user).",
                    updated_on = ".db_escape($datetime)."
                        
                WHERE id = ".db_escape($id);

        db_query($sql,"Estimation project BOQ item component could not be updated");	
        
        return db_num_affected_rows();
}


function get_est_project_boq_items_cost($project_id)
{
    $sql = "SELECT SUM(calculated_cost * max_quantity) total_cost  
            FROM ".TB_PREF."est_project_boq_items
                
            WHERE est_project_id=".db_escape($project_id)." 
            GROUP BY est_project_id";
    
    $result = db_query($sql);
    return db_result_value($result, "total_cost"); 
    
    
}


function get_est_project_boq_items_cost_components_total($project_id)
{
    $sql = "SELECT SUM(boqic.cost * boqi.max_quantity) total_cost 
                    
            FROM ".TB_PREF."est_project_boq_items boqi 
                JOIN ".TB_PREF."est_project_boq_item_components boqic ON 
                    boqic.boq_item_id = boqi.id 
                JOIN ".TB_PREF."project_task_detail_types dt ON 
                    boqic.detail_type_code = dt.detail_type_code 
                JOIN ".TB_PREF."list_values cat ON 
                    dt.category_id = cat.id AND 
                    cat.value_code <> 'OVERHEAD'                 
            WHERE boqi.est_project_id=".db_escape($project_id);
    
    $result = db_query($sql);
    return db_result_value($result, "total_cost"); 
}

function get_est_project_boq_items_overhead_components_total($project_id)
{
    $sql = "SELECT SUM(boqic.cost * boqi.max_quantity) total_cost 
                    
            FROM ".TB_PREF."est_project_boq_items boqi 
                JOIN ".TB_PREF."est_project_boq_item_components boqic ON 
                    boqic.boq_item_id = boqi.id 
                JOIN ".TB_PREF."project_task_detail_types dt ON 
                    boqic.detail_type_code = dt.detail_type_code 
                JOIN ".TB_PREF."list_values cat ON 
                    dt.category_id = cat.id AND 
                    cat.value_code = 'OVERHEAD'                 
            WHERE boqi.est_project_id=".db_escape($project_id);
    
    $result = db_query($sql);
    return db_result_value($result, "total_cost"); 
}

function get_est_project_boq_items_components_category_total($Project_id, $category_id)
{
    $sql = "SELECT SUM(boqic.cost * boqi.max_quantity) total_cost, 
                    cn.value_name category_name 
            FROM ".TB_PREF."list_values cn 
                    JOIN ".TB_PREF."project_task_detail_types dt 
                            ON cn.id = dt.category_id  
                    JOIN ".TB_PREF."est_project_boq_item_components boqic  
                            ON boqic.detail_type_code = dt.detail_type_code 
                    JOIN ".TB_PREF."est_project_boq_items boqi  
                            ON boqi.id = boqic.boq_item_id  
            WHERE boqi.est_project_id = ". db_escape($Project_id)." 
                    AND dt.category_id = ". db_escape($category_id);
    
    $result = db_query($sql,"get_est_project_boq_items_components_category_total");
    return db_result_value($result, 'total_cost');
    
}


function get_est_boq_group_boq_item_codes($project_id, $boq_group_id)
{
    $sql = "SELECT code 
            FROM ".TB_PREF."est_project_boq_items 
            WHERE est_project_id = ".db_escape($project_id)." AND 
                    group_id = ".db_escape($boq_group_id)." 
            ORDER BY code";
    
    return db_query($sql,"Could not write_approval_group");
}

?>