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

function write_work_done_entry_detail($boq_item_id, $site_id, $tran_date_, $quantity, $unit, $comment, $completed=0) {
    $tran_date = date2sql($tran_date_);
    
    begin_transaction();
    
        $existing_record = get_work_done_detail($boq_item_id, $site_id, $tran_date);
            if ($existing_record) {
                $af_row = update_work_done_entry_details($existing_record['id'], $quantity, $unit, $comment, $completed);  
                    if ($af_row != 1) {
                        cancel_transaction();
                        return array('success' => false, 'message' => "BOQ item #$boq_item_id, could not update work done record.");
                    }
            } else {      
                $insert_id = add_work_done_entry_details($boq_item_id, $site_id, $tran_date, $quantity, $unit, $comment, $completed); 
                    if (! ($insert_id > 0)) {
                        cancel_transaction();
                        return array('success' => false, 'message' => "BOQ item #$boq_item_id, could not add new work done record.");
                    }
            }  
        
        $item_site_total_qty = get_boq_item_site_total_work_done($boq_item_id, $site_id);
        $af_row_1 = update_boq_item_site_balances($boq_item_id, $site_id, $item_site_total_qty, $tran_date);
            if ($af_row_1 != 1) {
                cancel_transaction();
                return array('success' => false, 'message' => "BOQ item #$boq_item_id, could not update BOQ item site completed quantity.");
            }
        
        $total_qty = get_boq_item_total_work_done($boq_item_id);   
        $last_wd = get_boq_item_last_work_done_entry_date_from_details($boq_item_id);
        $af_row_2 = update_boq_item_balances($boq_item_id, $total_qty, $last_wd['tran_date']);
            if ($af_row_2 != 1) {
                cancel_transaction();
                return array('success' => false, 'message' => "BOQ item #$boq_item_id, could not update BOQ item completed quantity.");
            }
            
        $af_row_3 = update_boq_item_started_status($boq_item_id);
            if ($af_row_3 != 1) {
                cancel_transaction();
                return array('success' => false, 'message' => "BOQ item #$boq_item_id, could not update BOQ item started status.");
            }
          
        if ($completed == 1) {
            $af_row_4 = update_boq_item_completed_status($boq_item_id, $completed, $last_wd['tran_date']);
                if ($af_row_4 != 1) {
                    cancel_transaction();
                    return array('success' => false, 'message' => "BOQ item #$boq_item_id, could not update BOQ item completed status.");
                }
        }
    
    commit_transaction();
    
    return array('success' => true, 'message' => '');
}


function add_work_done_entry_details($boq_item_id, $site_id, $tran_date, $quantity, $unit, $comment, $completed=0)
{
    $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, completed, 
                    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($completed).", "
                    .db_escape($created_by).", ".db_escape($created_on).")";                      

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


function update_work_done_entry_details($id, $quantity, $unit, $comment, $completed=0)
{
    $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).",
               completed=".db_escape($completed).",
               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");  
     return db_num_affected_rows();
}


function get_work_done_detail($boq_group_id, $project_site_id, $tran_date)
{
   $sql = "SELECT id, tran_date, quantity, comment, completed 
            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 == 1) {      
                $last_wd = get_boq_item_last_work_done_entry_date_from_details($boq_item_id);
                if (isset($last_wd["id"]) && $last_wd["id"] > 0) {
                    $last_entry_date = $last_wd["tran_date"];
                } else {
                    $last_entry_date = INFINITE_START_DATE;
                }
                
                $af_row_3 = update_boq_item_started_status($boq_item_id);
                    if ($af_row_3 != 1) {
                        cancel_transaction();
                        return array('success' => false, 'message' => "BOQ item #$boq_item_id, could not update BOQ item started status.");
                    }

                $wd_result = view_boq_item_site_work_done_history($boq_item_id, $site_id);
                    if (db_num_rows($wd_result) > 0) {
                        $item_site_total_qty = get_boq_item_site_total_work_done($boq_item_id, $site_id);
                        $af_row = update_boq_item_site_balances($boq_item_id, $site_id, $item_site_total_qty, $last_entry_date);
                            if ($af_row != 1) {
                                cancel_transaction();
                                return array('success' => false, 'message' => "BOQ item #$boq_item_id, could not update BOQ item site completed quantity.");
                            }
                    } else {
                        $af_row = delete_project_boq_item_site($boq_item_id, $site_id);
                            if ($af_row != 1) {
                                cancel_transaction();
                                return array('success' => false, 'message' => "BOQ item #$boq_item_id, could not delete BOQ item site record.");
                            }
                    }

                $total_qty = get_boq_item_total_work_done($boq_item_id);
                $af_row_1 = update_boq_item_balances($boq_item_id, $total_qty, $last_entry_date);
                    if ($af_row_1 != 1) {
                        cancel_transaction();
                        return array('success' => false, 'message' => "BOQ item #$boq_item_id, could not update BOQ item completed quantity.");
                    }
            } else {
                cancel_transaction();
                return array('success' => false, 'message' => "BOQ item #$boq_item_id, could not delete work done record.");
            }
    
    commit_transaction();    
    
        return array('success' => true, 'message' => "");
}

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);
                
        db_query($sql, 'delete_boq_work_done_entry_details');    
        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;
//}

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

function get_work_done_entry_details_previous_and_next_records($boq_item_id, $site_id, $tran_date) {
    $pre_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)." 
                    AND tran_date < ".db_escape($tran_date)." 
                ORDER BY tran_date DESC LIMIT 1";
    
        $pre_result = db_query($pre_sql, 'get_work_done_entry_details_previous_and_next_records');
        $pre_row = db_fetch($pre_result);
        
    $nxt_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)." 
                    AND tran_date > ".db_escape($tran_date)." 
                ORDER BY tran_date ASC LIMIT 1";
    
        $nxt_result = db_query($nxt_sql, 'get_work_done_entry_details_previous_and_next_records');
        $nxt_row = db_fetch($nxt_result);
        
        return array('pre_row' => $pre_row, 'nxt_row' => $nxt_row);
}

function get_work_done_entry_detail_completed_boq_items($boq_item_id=0, $fetch=false) {
    $sql = "SELECT * 
            FROM ".TB_PREF."work_done_entry_details
            WHERE completed = 1";
    
        if ($boq_item_id > 0) {
            $sql .= " AND boq_item_id = ".db_escape($boq_item_id);
        }
    
        $result = db_query($sql, 'get_work_done_entry_detail_completed_boq_items');
            if ($fetch === false) {
                return $result;
            } else {
                return db_fetch($result);
            }
}

function get_first_work_done_entry_detail_boq_item_record($boq_item_id) {
    $sql = "SELECT * 
            FROM ".TB_PREF."work_done_entry_details
            WHERE boq_item_id = ".db_escape($boq_item_id)." 
            ORDER BY tran_date ASC LIMIT 1";
    
        $result = db_query($sql, 'get_first_work_done_entry_detail_boq_item_record');
        return db_fetch($result);
}

function update_work_done_entry_details_completed_status($boq_item_id, $completed) {
    $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 completed = ".db_escape($completed).",
                row_state = row_state + 1,
                updated_by = ".db_escape($updated_by).",
                updated_on = ".db_escape($updated_on)."
            WHERE boq_item_id = ".db_escape($boq_item_id);

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

function get_last_site_work_done_detail_record_not_tran_date($boq_item_id, $site_id, $not_tran_date) {
    $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)." 
                AND tran_date <> ".db_escape($not_tran_date)." 
            ORDER BY tran_date DESC LIMIT 1";
    
        $result = db_query($sql, 'get_last_site_work_done_detail_record_not_tran_date');
        return db_fetch($result);
}

?>