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


function write_tool_request_header($ar_id, $reference, $project_id, 
                                        $company_code, $employee_code,
                                        $location, $comment, $date) 
{

    global $Refs;
     
    if($ar_id > 0){
        update_tool_request($ar_id, $reference, $project_id, $company_code, $employee_code,
                                $location, $comment, $date);
    }else{
        $ar_id = insert_tool_request($reference, $project_id, $company_code, $employee_code,
                                        $location, $comment, $date);
    }
    
    $Refs->save(ST_TOOL_REQUEST, $ar_id, $reference);
      
    if(!($ar_id>0)){
        return;
    }
 
    return $ar_id;
    
}


function update_tool_request($ar_id, $reference, $project_id, $company_code,
                                $employee_code, $location, $comment, $date)
{    
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
    $sql = "UPDATE ".TB_PREF."tool_requests
              
            SET  reference=".db_escape($reference).",
                    project_id=".db_escape($project_id).",
                    location=".db_escape($location).",                          
                    date=".db_escape(date2sql($date)).",
                    comment=".db_escape($comment).", 
                    company_code=".db_escape($company_code).", 
                    employee_code=".db_escape($employee_code).", 
                    updated_by=".db_escape($updated_by).", 
                    updated_on=".db_escape($updated_on)." 

            WHERE  id = ".db_escape($ar_id);    
          
        db_query($sql, "Tool Request cannot be updated.");
         
}
     
     
function insert_tool_request($reference, $project_id, $company_code, $employee_code,
                                $location, $comment, $date)
{
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
    $sql = "INSERT INTO ".TB_PREF."tool_requests
                  ( reference, project_id, 
                    company_code, employee_code, location,
                    comment, date, 
                    created_by, created_on )         
            VALUES( ".db_escape($reference).", ".db_escape($project_id).", 
                        ".db_escape($company_code).", ".db_escape($employee_code).", ".db_escape($location).",
                        ".db_escape($comment).", ".db_escape(date2sql($date)).",                   
                        ".db_escape($created_by).", ".db_escape($created_on).")";
     
    db_query($sql);
    return db_insert_id();        
}
    
    
function write_tool_request_line($ar_no, $ar_line_id, $asset_id, $rate_category_id, 
                                    $rate, $quantity, $task_id, $site_id,  
                                    $from_date, $to_date, $project_asset, $line_comment, $no_of_days)
{
    if($ar_line_id > 0){
        update_tool_request_line($ar_line_id, $rate_category_id, 
                                    $rate, $quantity, $task_id, $site_id,  
                                    $from_date, $to_date, $project_asset, $line_comment, $no_of_days);
    }else{
        insert_tool_request_line($ar_no, $asset_id, $rate_category_id, 
                                    $rate, $quantity, $task_id, $site_id,  
                                    $from_date, $to_date, $project_asset, $line_comment, $no_of_days);
    }
    
    $ar_total = get_tool_request_lines_total($ar_no);
    
    update_tool_request_header_total($ar_no, $ar_total);
}

function get_tool_request_lines_total($ar_no)
{
    $sql = "SELECT SUM(no_of_days * quantity * rate) total
            FROM ".TB_PREF."tool_request_details
            WHERE ar_id=".db_escape($ar_no);
    
    $result = db_query($sql);
    $count = db_fetch($result);    
    
    if(isset($count['total']) && $count['total'] > 0){
        return $count['total'];
    }else{
        return 0;
    }
}

function update_tool_request_header_total($ar_no, $ar_total)
{
    $sql = "UPDATE ".TB_PREF."tool_requests
            SET total=".db_escape($ar_total)."
                WHERE id=".db_escape($ar_no);
            
    db_query($sql);
}

function update_tool_request_line($ar_line_id, $rate_category_id, 
                                    $rate, $quantity, $task_id, $site_id,  
                                    $from_date, $to_date, $project_asset, $line_comment, $no_of_days)
{
    $sql = "UPDATE ".TB_PREF."tool_request_details
            SET rate_category_id=".db_escape($rate_category_id).",
                rate=".db_escape($rate).",
                quantity=".db_escape($quantity).", 
                task_id=".db_escape($task_id).",
                site_id=".db_escape($site_id).",
                from_date=".db_escape(date2sql($from_date)).",
                to_date=".db_escape(date2sql($to_date)).",
                project_asset=".db_escape($project_asset).",
                line_comment=".db_escape($line_comment).",  
                no_of_days=".db_escape($no_of_days)." 
                
            WHERE id=".db_escape($ar_line_id);
    
     db_query($sql);
     
}
        

function insert_tool_request_line($ar_no, $asset_id, $rate_category_id, 
                                    $rate, $quantity, $task_id, $site_id,  
                                    $from_date, $to_date, $project_asset, $line_comment, $no_of_days)
{
    $sql = "INSERT INTO ".TB_PREF."tool_request_details
        
            ( ar_id, asset_id, 
                rate_category_id, rate, quantity,
                task_id, site_id, 
                from_date, to_date, 
                project_asset, line_comment, no_of_days)        
            VALUE
            
            ( ".db_escape($ar_no).", ".db_escape($asset_id).",
                ".db_escape($rate_category_id).", ".db_escape($rate).", ".db_escape($quantity).",  
                ".db_escape($task_id).", ".db_escape($site_id).", 
                ".db_escape(date2sql($from_date)).", ".db_escape(date2sql($to_date)).",  
                ".db_escape($project_asset).", ".db_escape($line_comment).", ".db_escape($no_of_days).")";      
           
    db_query($sql);
}


 
function get_tool_request_header($ar_no)
{
    $sql = "SELECT ar.*,
                    pm.description project_description,
                    em.last_name
        
            FROM ".TB_PREF."tool_requests ar
                LEFT JOIN ".TB_PREF."projects_master pm ON
                    ar.project_id = pm.project_id
                    
                LEFT JOIN ".TB_PREF."employee_master em ON
                    ar.employee_code = em.employee_code
                
            WHERE ar.id=".db_escape($ar_no);
    
    $result = db_query($sql, "Could not get_tool_request_header");
    return db_fetch($result);
}
 

function get_tool_request_detail_line($selected_id)
{
    $sql = "SELECT ard.*,
                    ar.id rate_id 
        
            FROM ".TB_PREF."tool_request_details ard
                LEFT JOIN ".TB_PREF."tool_rates ar ON
                    ard.rate_category_id = ar.id
              
            WHERE ard.id=".db_escape($selected_id);
    
    $result = db_query($sql);
    return db_fetch($result);
    
}


function save_delete_tool_request_line($ar_no, $selected_id)
{
    begin_transaction();
      
    delete_tool_request_detail($selected_id);
    
    $ar_total = get_tool_request_lines_total($ar_no);
    
    update_tool_request_header_total($ar_no, $ar_total);
   
    commit_transaction();


}


function delete_tool_request_detail($selected_id)
{
      
    $sql = "DELETE FROM ".TB_PREF."tool_request_details WHERE id=".db_escape($selected_id);
    
    db_query($sql);
    
    
    
}


function get_tool_request($ar_id)
{    
    $sql = "SELECT ar.*,
                    pm.project_name,
                    pt.description,
                    ag.description asset_group_name        

            FROM ".TB_PREF."tool_requests ar,
                    ".TB_PREF."projects_master pm,
                    ".TB_PREF."project_tasks pt,
                    ".TB_PREF."asset_groups ag

            WHERE ag.id = ar.asset_group_id AND 
                pt.task_id = ar.task_id AND 
                ar.project_id = pm.project_id AND
                ar.id=".db_escape($ar_id);
    
    $result = db_query($sql);
    return db_fetch($result);
    
} 
 



function get_tool_request_details($ar_id)
{
    $sql = "SELECT ard.*,
                    am.asset_code,
                    am.description asset_description,
                    am.asset_id,
                    ar.description asset_rate_description,
                    pt.task_code,
                    pt.description task_description,
                    ps.description site_description
        

            FROM ".TB_PREF."tool_request_details ard 
                LEFT JOIN ".TB_PREF."asset_master am ON 
                    ard.asset_id = am.asset_id
                 
                LEFT JOIN ".TB_PREF."tool_rates ar ON
                    ard.rate_category_id = ar.id
                    
                LEFT JOIN ".TB_PREF."project_tasks pt ON
                    pt.task_id = ard.task_id
                    
                LEFT JOIN ".TB_PREF."project_sites ps ON
                    ard.site_id = ps.id
                    
                
            WHERE ard.ar_id=".db_escape($ar_id);
    
    return db_query($sql, "Could not get_tool_request_details");
    
}


function get_tool_request_detail_records($ar_id)
{
    $sql = "SELECT ard.*
            FROM ".TB_PREF."tool_request_details ard 
            WHERE ard.ar_id=".db_escape($ar_id);
    
    return db_query($sql, "Could not get_tool_request_detail_records");
    
}




function update_tool_request_line_issued_qty($ar_line_id, $issued_qty)
{
    $sql = "UPDATE ".TB_PREF."tool_request_details
            SET issued_qty = ".db_escape($issued_qty)." 
            
            WHERE id=".db_escape($ar_line_id);
   
    db_query($sql);   
    return db_num_affected_rows();
}


function get_tool_request_line_count($ar_no)
{
    $sql = "SELECT COUNT(id) line_count 
            FROM ".TB_PREF."tool_request_details 
            WHERE ar_id = ".db_escape($ar_no);
    
    $result = db_query($sql);
    $count =  db_fetch($result);   
    if(isset($count['line_count']) && $count['line_count'] > 0){
        return $count['line_count'];
    }    
        return 0;
}

function get_tool_request_task_remaining_totals($ar_no)
{
    $sql = "SELECT task_id, SUM((quantity - issued_qty) * no_of_days * rate) task_cost 
            FROM ".TB_PREF."tool_request_details 
            WHERE ar_id = ".db_escape($ar_no)." 
                    AND project_asset = 0 
            GROUP BY task_id";
    
    return db_query($sql, "get_tool_request_task_remaining_totals");
}

function get_tool_request_tasks($ar_no)
{
    $tasks = array();
    
    $sql = "SELECT task_id 
            FROM ".TB_PREF."tool_request_details 
            WHERE ar_id = ".db_escape($ar_no)." 
                    AND project_asset = 0 ";
    
    $result = db_query($sql, "get_tool_request_tasks");
    while ($row = db_fetch($result)) {
        $tasks[] = $row['task_id'];        
    }
    
    get_task_cost_details($tasks);
    
    return $tasks;
}


function calculate_tool_request_task_remaining_totals($ar_no)
{
    $task_costs = array();

    $result = get_tool_request_task_remaining_totals($ar_no);

    while($row = db_fetch($result))
    {            
        add_to_task_cost($task_costs, $row['task_id'], $row['task_cost'], $row['task_cost'], $row['task_cost']);
    }

    get_task_cost_details($task_costs);

    return $task_costs;
}


   
function release_tool_request($ar_no, $row_state)
{
    $current_user_id = $_SESSION['wa_current_user']->user;
    
    $arh = get_tool_request_header($ar_no);
    
//    $scpoh_total_with_taxes = $scpoh['total'] + $scpoh['taxes'];
    
    $tasks_summary = calculate_tool_request_task_remaining_totals($ar_no); 
    
    $info = approve_document(ST_TOOL_REQUEST, $ar_no, $arh['project_id'], 
                        $tasks_summary, $arh['next_approval_group_id'], 
                        $current_user_id, $row_state, $arh['created_by']);

    if($info['success'] !== true){
        return array("success" => $info['success'],"tran_no" => $ar_no, "message" => $info['message']);
    }

    if($info['approval_status'] != AS_APPROVED)
    {            
        commit_transaction();
        return array("success" => true, "tran_no" => $ar_no, "message" => $info['message']);
    }
    
    //Document approved
    
    $c = count($tasks_summary); // No costs to allocate. One possibility is when all requested asstes are Project Assets
    if(!($c > 0)){
        return array("success" => true, "tran_no" => $ar_no, "message" => $info['message']);
    }
    
    $apc_msg = allocate_to_tasks(ST_TOOL_REQUEST, $ar_no, 
                                $tasks_summary, $current_user_id);
    if($apc_msg['success'] !== true)
    {
        return array("success" => $apc_msg['success'], "tran_no" => $ar_no, "message" => $info['message']);
    }
    
    return array("success" => true, "tran_no" => $ar_no, "message" => $info['message']);
        
}


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

function update_tool_request_status($ar_no, $new_status_id, $row_state)
{
    $sql = "UPDATE ".TB_PREF."tool_requests
            SET approval_status = ".db_escape($new_status_id).",
                row_state = ". db_new_row_state($row_state)." 
            WHERE id=".db_escape($ar_no)." AND row_state = ".db_escape($row_state);
            
    db_query($sql, "update_tool_request_fully_returned");
    
    return db_num_affected_rows();
}

function update_tool_request_charges_calculation_summary($areq_id, $charged_upto_date, $fully_charged, $row_state)
{
    $sql = "UPDATE ".TB_PREF."tool_requests
            SET charged_upto_date = ".db_escape($charged_upto_date).",
                fully_charged = ".db_escape($fully_charged).",
                row_state = ". db_new_row_state($row_state)."
            WHERE id=".db_escape($areq_id)." 
                AND row_state = ".db_escape($row_state);
            
    db_query($sql, "update_tool_request_charges_calculation_summary");
    
    return db_num_affected_rows();
}

function update_tool_request_detail_charges($tool_request_detail_id)
{
    $charged_units = get_tool_request_detail_charged_units($tool_request_detail_id);
    
    $sql = "UPDATE ".TB_PREF."tool_request_details
            SET units_charged =".db_escape($charged_units)."             
            WHERE id=".db_escape($tool_request_detail_id);               
   
    db_query($sql);

    
}

function check_tool_request_fully_returned($tool_request_id)
{    
    $sql = "SELECT id num_recs  
            FROM ".TB_PREF."tool_request_details  
            WHERE quantity > returned_qty   
                    AND ai_id = ".db_escape($tool_request_id);
    
    $result = db_query($sql, "check_tool_request_fully_returned");
    $num_recs = db_num_rows($result);
    
    if($num_recs > 0){
        return false;
    }
    else{
        return true;
    }
}

function check_all_tool_request_lines_fully_issued($asset_issue_id)
{
    $sql = "SELECT id num_recs  
            FROM ".TB_PREF."tool_request_details  
            WHERE quantity > issued_qty   
                    AND ar_id = ".db_escape($asset_issue_id);
    
    $result = db_query($sql, "check_all_tool_request_lines_fully_issued");
    $num_recs = db_num_rows($result);
    
    if($num_recs > 0){
        return false;
    }
    else{
        return true;
    }
}
?>