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

function write_work_done_entry_detail($boq_item_id, $site_id, $tran_date, $quantity, $unit, $comment)
{
    $tran_date = date2sql($tran_date);
    begin_transaction();
    $existing_record = get_work_done_detail($boq_item_id, $site_id, $tran_date);
    if($existing_record){
        update_work_done_entry_details($existing_record['id'], $quantity, $unit, $comment);  
        
        $item_site_total_qty = get_boq_item_site_total_work_done($boq_item_id, $site_id);
        update_boq_item_site_balances($boq_item_id, $site_id, $item_site_total_qty, $tran_date);
        
        $total_qty = get_boq_item_total_work_done($boq_item_id);              
        update_boq_item_balances($boq_item_id, $total_qty, $tran_date);
    }
    else{      
        add_work_done_entry_details($boq_item_id, $site_id, $tran_date, $quantity, $unit, $comment); 
        
        $item_site_total_qty = get_boq_item_site_total_work_done($boq_item_id, $site_id);
        update_boq_item_site_balances($boq_item_id, $site_id, $item_site_total_qty, $tran_date);
        
        $total_qty = get_boq_item_total_work_done($boq_item_id);
        update_boq_item_site_balances($boq_item_id, $site_id, $total_qty, $tran_date);
        
        update_boq_item_balances($boq_item_id, $total_qty, $tran_date);
    }  
    commit_transaction();
    
    return $boq_item_id;
}


function add_work_done_entry_details($boq_item_id, $site_id, $tran_date, $quantity, $unit, $comment)
{
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');

    $sql = "INSERT INTO ".TB_PREF."work_done_entry_details
                    (boq_item_id, site_id, 
                    tran_date, quantity, 
                    unit, comment, 
                    created_by, created_on)
            VALUES (".db_escape($boq_item_id).", ".db_escape($site_id).", "
                    .db_escape($tran_date).", ".db_escape($quantity).", "
                    .db_escape($unit).", ".db_escape($comment).", "
                    .db_escape($created_by).", ".db_escape($created_on).")";                      

    db_query($sql,"Could not save_work_done_entry_details");       
}


function update_work_done_entry_details($id, $quantity, $unit, $comment)
{
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
      
    $sql =  "UPDATE ".TB_PREF."work_done_entry_details
            SET 
               quantity=".db_escape($quantity).",
               unit=".db_escape($unit).",
               comment=".db_escape($comment).",
               updated_by=".db_escape($updated_by).",
               updated_on=".db_escape($updated_on)."
            WHERE id=".db_escape($id);

     db_query($sql, "Work done entry details could not be updated");             
}


function get_work_done_detail($boq_group_id, $project_site_id, $tran_date)
{
   $sql = "SELECT id, tran_date, quantity, comment 
            FROM ".TB_PREF."work_done_entry_details
            WHERE boq_item_id = ".db_escape($boq_group_id)." 
                    AND site_id =".db_escape($project_site_id)." 
                    AND tran_date =".db_escape($tran_date); 
                 

   $result = db_query($sql, "Could not get tran date");
   return db_fetch($result);     
}


function get_entered_date_from_work_done_details($id)
{
    $sql = "SELECT *
            FROM ".TB_PREF."work_done_entry_details
            WHERE id=".db_escape($id); 
    
    $result = db_query($sql);
    return db_fetch($result);     
}



function delete_work_done_details($boq_item_id, $site_id, $tran_date)
{
    $tran_date = date2sql($tran_date);
    begin_transaction();

    $n = delete_boq_work_done_entry_details($boq_item_id, $site_id, $tran_date); 
    if($n > 0){      
        $last_wd = get_last_work_done_detail_entry($boq_item_id, $site_id);
        if(isset($last_wd["quantity"]) && $last_wd["quantity"] > 0){
            $last_entry_date = $last_wd["tran_date"];
        }
        else{
            $last_entry_date = "";
        }
        
        $item_site_total_qty = get_boq_item_site_total_work_done($boq_item_id, $site_id);
        update_boq_item_site_balances($boq_item_id, $site_id, $item_site_total_qty, $last_entry_date);
        
        $total_qty = get_boq_item_total_work_done($boq_item_id);
        update_boq_item_balances($boq_item_id, $total_qty, $last_entry_date);
    }
    
    commit_transaction();    
    return $boq_item_id;
}


function delete_boq_work_done_entry_details($boq_item_id, $site_id, $tran_date)
{
    $sql = "DELETE FROM ".TB_PREF."work_done_entry_details
            WHERE boq_item_id=".db_escape($boq_item_id)." AND 
                    site_id=".db_escape($site_id)." AND 
                    tran_date >=".db_escape($tran_date);
                
    $result = db_query($sql);    
    return db_num_affected_rows();   
}

function get_last_work_done_detail_entry($boq_item_id, $site_id)
{
    $sql = "SELECT * 
            FROM ".TB_PREF."work_done_entry_details
            WHERE boq_item_id=".db_escape($boq_item_id)." AND 
                    site_id=".db_escape($site_id)." 

            ORDER BY tran_date DESC LIMIT 1";
    
    $result = db_query($sql);
    return db_fetch($result);

}

function get_last_site_work_done_detail_record($boq_item_id, $site_id)
{
    $sql = "SELECT * 
            FROM ".TB_PREF."work_done_entry_details
            WHERE boq_item_id=".db_escape($boq_item_id)." AND 
                    site_id=".db_escape($site_id)." 
            ORDER BY tran_date DESC LIMIT 1";
    
    $result = db_query($sql);
    return db_fetch($result);

}


function get_boq_item_site_total_work_done($boq_item_id, $site_id)
{
    $sql = "SELECT quantity total_work_done
            FROM ".TB_PREF."work_done_entry_details
            WHERE boq_item_id=".db_escape($boq_item_id)." AND 
                    site_id =".db_escape($site_id)." 
            ORDER BY tran_date DESC, id DESC 
                    LIMIT 1";
    
    $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 view_project_boq_group_work_done($boq_group_id)
{
    $sql = "SELECT boqi.* 
                    
            FROM ".TB_PREF."project_boq_items boqi 
                    INNER JOIN ".TB_PREF."project_boq_item_groups boqg ON 
                            boqi.group_id = boqg.id 
            WHERE boqg.id =".db_escape($boq_group_id)." 
            ORDER BY boqi.id ";
    
    return db_query($sql);
}


function view_project_no_group_work_done($project_id)
{
    $sql = "SELECT boqi.* 
                    
            FROM ".TB_PREF."project_boq_items boqi 
            WHERE boqi.project_id =".db_escape($project_id)." AND 
                    boqi.group_id =0 
            ORDER BY boqi.id ";
    
    return db_query($sql);
}

function view_boq_item_site_work_done_history($boq_item_id, $site_id)
{
    $sql = "SELECT * 
            FROM ".TB_PREF."work_done_entry_details
            WHERE boq_item_id=".db_escape($boq_item_id)." AND 
                    site_id =".db_escape($site_id);
    
    return db_query($sql);

}

//function get_boq_item_total_work_done($boq_item_id)
//{
//    $sql = "SELECT SUM(quantity) total_work_done
//            FROM ".TB_PREF."work_done_entry_details
//            WHERE boq_item_id=".db_escape($boq_item_id);
//    
//    $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;
//}

?>