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

function update_boq_item_site_balances($boq_item_id, $site_id, $item_site_total_qty, $last_entry_date) {
    $existing_record = get_boq_item_site_record($boq_item_id, $site_id);
    if ($existing_record) {
        return update_boq_item_site_total_work_done($existing_record['id'], $item_site_total_qty, $last_entry_date);
    } else {      
        $insert_id = add_boq_item_site_record($boq_item_id, $site_id, $item_site_total_qty, $last_entry_date);
            if ($insert_id > 0) {
                return 1;
            } else {
                return 0;
            }
    }  
}

function add_boq_item_site_record($boq_item_id, $site_id, $total_qty, $last_entry_date)
{
    $id_user = $_SESSION['wa_current_user']->user;
    $datetime = date('Y-m-d H:i:s');
                
    $sql = "INSERT INTO ".TB_PREF."project_boq_item_sites  
                        (boq_item_id, site_id,  
                        completed_qty, last_entry_date, 
                        created_by, created_on) 

            VALUES (".db_escape($boq_item_id).", ".db_escape($site_id).", "
                    .db_escape($total_qty).", ".db_escape($last_entry_date).", "
                    .db_escape($id_user).", ".db_escape($datetime).")";

        db_query($sql,"Could not add_boq_item_site_record");	
        return db_insert_id();
}

function update_boq_item_site_total_work_done($id, $total_qty, $last_entry_date) {
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
    $sql = "UPDATE ".TB_PREF."project_boq_item_sites 
                SET completed_qty = ".db_escape($total_qty).", 
                    last_entry_date=".db_escape($last_entry_date).", 
                    row_state = row_state + 1,  
                    updated_by = ".db_escape($updated_by).", 
                    updated_on = ".db_escape($updated_on)."  
                WHERE id=".db_escape($id);

        db_query($sql, "update_boq_item_site_total_work_done");
        return db_num_affected_rows();
}



function get_boq_item_site_record($boq_group_id, $project_site_id)
{
   $sql = "SELECT * 
            FROM ".TB_PREF."project_boq_item_sites 
            WHERE boq_item_id = ".db_escape($boq_group_id)." 
                    AND site_id =".db_escape($project_site_id);
                 

   $result = db_query($sql, "Could not get_boq_item_site_record");
   return db_fetch($result);     
}



function get_boq_item_total_work_done($boq_item_id)
{
    $sql = "SELECT SUM(completed_qty) total_work_done  
            FROM ".TB_PREF."project_boq_item_sites 
            WHERE boq_item_id=".db_escape($boq_item_id); //." AND 
                  //tran_date <=".db_escape($tran_date);
    
    $result = db_query($sql);
    $twd = db_fetch($result);
    
    $total_work_done = 0;
    if(isset($twd['total_work_done']) &&  $twd['total_work_done'] > 0){
        $total_work_done =  $twd['total_work_done'];
    }    
    return $total_work_done;
}

function get_boq_item_all_sites_total_work_done($boq_item_id, $not_site_id=null) {
    $sql = "SELECT SUM(completed_qty) total_work_done 
            FROM ".TB_PREF."project_boq_item_sites 
            WHERE boq_item_id = ".db_escape($boq_item_id);
    
        if ($not_site_id !== null) {
            $sql .= " AND site_id <> ".db_escape($not_site_id);
        }

        $result = db_query($sql, "get_boq_item_all_sites_total_work_done");
        $twd = db_fetch($result);

        $total_work_done = 0;
        if (isset($twd['total_work_done']) && $twd['total_work_done'] > 0) {
            $total_work_done = $twd['total_work_done'];
        } 

        return $total_work_done;
}

function delete_project_boq_item_site($boq_item_id, $site_id) {
    $sql = "DELETE FROM ".TB_PREF."project_boq_item_sites 
            WHERE boq_item_id = ".db_escape($boq_item_id)." 
                AND site_id = ".db_escape($site_id);
                
        db_query($sql, 'delete_project_boq_item_site');    
        return db_num_affected_rows();   
}

function get_boq_item_site_with_work_done_entry_details($boq_group_id) {
    $sql = "SELECT bq.*, st.description site_name, wd.unit, wd.comment   
            FROM ".TB_PREF."project_boq_item_sites bq 
                JOIN ".TB_PREF."work_done_entry_details wd 
                    ON bq.boq_item_id = wd.boq_item_id 
                        AND bq.site_id = wd.site_id 
                        AND bq.last_entry_date = wd.tran_date
                LEFT JOIN ".TB_PREF."project_sites st 
                    ON bq.site_id = st.id 
            WHERE bq.boq_item_id = ".db_escape($boq_group_id)." 
            GROUP BY bq.id
            ORDER BY bq.last_entry_date";

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

?>