<?php //
/**********************************************************************
***********************************************************************/
function add_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."project_boq_items 
                            (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,"Project BOQ Item could not be added");	
        
        return db_insert_id();
}

function update_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."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,"project BOQ item could not be updated");	
        
        return db_num_affected_rows();
}

function delete_boq_item($selected_id)
{
	$sql="DELETE FROM ".TB_PREF."project_boq_items WHERE id= ".db_escape($selected_id);
	db_query($sql,"Project BOQ item could not be deleted");			
}

function get_project_boq_items($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."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.project_id=".db_escape($project_id)." 
                ORDER BY display_order, type, code";
                     
	return db_query($sql,"Project BOQ items could not be retreived");
        
}

function get_project_group_boq_items($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."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.project_id=".db_escape($project_id)." AND 
                        pbi.group_id=".  db_escape($boq_group_id)." 
                ORDER BY display_order, billing_type, code";
                     
	return db_query($sql,"Project BOQ items could not be retreived");
        
}

function get_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_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."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 
                        LEFT JOIN ".TB_PREF."item_units u ON
                                pbi.unit = u.abbr 
                    
                WHERE pbi.id=".db_escape($id);
	
	$result = db_query($sql,"Selected project BOQ detail could not be retreived");	
	return db_fetch($result);
}

function get_project_boq_item_record($id)
{
	$sql = "SELECT pbi.*
                FROM ".TB_PREF."project_boq_items pbi                     
                WHERE pbi.id=".db_escape($id);
	
	$result = db_query($sql,"Could not get_project_boq_item_record");	
	return db_fetch($result);
}

function get_top_level_billing_items($project_id)
{
    $sql = "SELECT * 
                     
                FROM ".TB_PREF."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_child_billing_items($group_id)
{
    $sql = "SELECT * 
                FROM ".TB_PREF."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_project_boq_items($project_id, $code, $selected_id)
{
    $sql = "SELECT id, code
            FROM ".TB_PREF."project_boq_items
            WHERE 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_boq_item_balances($boq_item_id, $total_qty, $last_entry_date)
{
    $sql = "UPDATE ".TB_PREF."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_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."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 create_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."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_boq_item_component_record");  
   
   return db_insert_id();
}

function recalculate_boq_item($boq_item_id)
{
    $total_cost = get_boq_item_components_total_cost($boq_item_id);
    return update_boq_item_total_cost($boq_item_id, $total_cost);
}

function update_boq_item_total_cost($boq_item_id, $total_cost)
{
    $sql = "UPDATE ".TB_PREF."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_boq_item_total_cost"); 
    
    return db_num_affected_rows();
}


function delete_boq_item_component($boq_item_id, $boq_item_component_id)
{
    $boq_item_component = get_boq_item_component_record_by_id($boq_item_component_id);
    delete_boq_item_component_primary_types($boq_item_id, $boq_item_component['detail_type_code']);
            
    $n1 = delete_boq_item_component_record($boq_item_component_id);
    if(!($n1 == 1)){
        return array('success' => false);
    }
    
    $n2 = recalculate_boq_item($boq_item_id);
    if(!($n2 == 1)){
        return array('success' => false);
    }
    else{
        return array('success' => true);
    }
}

function get_project_boq_item_record_by_code($project_id, $code, $boq_item_group_id=-99)
{
    $sql = "SELECT pbi.*
                FROM ".TB_PREF."project_boq_items pbi                     
                WHERE pbi.project_id=".db_escape($project_id)." 
                    AND pbi.code=".db_escape($code);
    
        if($boq_item_group_id > -5){
            $sql .= " AND pbi.group_id=".db_escape($boq_item_group_id);
        }
                        
	
	$result = db_query($sql,"Could not get_project_boq_item_record");	
	return db_fetch($result);
}


function update_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);    
}



?>