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

function add_site_material_return_header($date_, $reference, $to_location, $from_project_id, $to_project_id, $comment, $company_code, $employee_code){
    $ia_date = date2sql($date_);
    $created_on = date('Y-m-d H:i:s');
    $created_by = $_SESSION['wa_current_user']->user;
        
	$sql = "INSERT INTO ".TB_PREF."site_material_returns(tran_date, reference,
                        to_location,   
                        from_project_id, to_project_id,   
                        comments, company_code, employee_code, 
                        created_by, created_on) 
                VALUES (".db_escape($ia_date).", ".db_escape($reference).", "
                        .db_escape($to_location).", "
                        .db_escape($from_project_id).", ".db_escape($to_project_id).", "
                        .db_escape($comment).", ".db_escape($company_code).", ".db_escape($employee_code).", "
                        .db_escape($created_by).", ".db_escape($created_on).")";

    db_query($sql, "could not add_site_material_return_header");
    
    if(!(db_insert_id() > 0)){
        return array("success" => false, "message" => "Could not Add Site Material Return header.", "trans_no" => 0);
    }else{
        return array("success" => true, "message" => "", "trans_no" => db_insert_id());
    }
}

function update_site_material_return_header($heder_id, $date_, $reference, $to_location, $from_project_id, $to_project_id, $comment, $row_state, 
                                            $company_code, $employee_code){
    $ia_date = date2sql($date_);
    $updated_on = date('Y-m-d H:i:s');
    $updated_by = $_SESSION['wa_current_user']->user;
    
    $sql = "UPDATE ".TB_PREF."site_material_returns  
            SET tran_date = ".db_escape($ia_date).", 
                reference = ".db_escape($reference).",
                to_location = ".db_escape($to_location).",
                from_project_id = ".db_escape($from_project_id).",
                to_project_id = ".db_escape($to_project_id).",
                comments = ".db_escape($comment).",
                updated_by = ".db_escape($updated_by).",
                updated_on = ".db_escape($updated_on).",
                row_state = ".db_new_row_state($row_state).", 
                company_code = ".db_escape($company_code).", 
                employee_code = ".db_escape($employee_code)."
                        
            WHERE id = ".db_escape($heder_id)." 
                AND row_state = ".db_escape($row_state)." 
                AND status <> ".db_escape(AS_PROCESSED);

    db_query($sql, "could not update_site_material_return_header");
    
    if(!(db_num_affected_rows() > 0)){
        return array("success" => false, "message" => "Could not Update Site Material Return header.", "trans_no" => $heder_id);
    }else{
        return array("success" => true, "message" => "", "trans_no" => $heder_id);
    }
}

function update_existing_site_material_return_details($row_id, $trans_no, $quantity, $std_cost, $tran_unit_cost, $tran_unit = "", $tran_qty=0, $conversion_factor=1, 
                                                      $from_task_id=0, $to_task_id=0, $from_project_site_id=0, $to_project_site_id=0, $pri_line_id=0){
    $updated_on = date('Y-m-d H:i:s');
    $updated_by = $_SESSION['wa_current_user']->user;
        
        $sql = "UPDATE ".TB_PREF."site_material_return_details   
                SET qty = ".db_escape($quantity).",
                    standard_cost = ".db_escape($std_cost).",
                    tran_unit_cost = ".db_escape($tran_unit_cost).",
                    tran_unit = ".db_escape($tran_unit).",
                    tran_qty = ".db_escape($tran_qty).",
                    convertion_factor = ".db_escape($conversion_factor).",
                    from_task_id = ".db_escape($from_task_id).",
                    to_task_id = ".db_escape($to_task_id).",
                    from_project_site_id = ".db_escape($from_project_site_id).",
                    to_project_site_id = ".db_escape($to_project_site_id).",
                    updated_by = ".db_escape($updated_by).",
                    updated_on = ".db_escape($updated_on).",
                    pri_line_id = ".db_escape($pri_line_id)."

                WHERE id = ".db_escape($row_id)." 
                    AND trans_no = ".db_escape($trans_no);

    db_query($sql, "could not add_existing_site_material_return_details");
    if(!(db_num_affected_rows() > 0)){
        return array("success" => false, "message" => "Could not Add Site Material Return details.");
    }else{
        return array("success" => true, "message" => "");
    }
}

function add_site_material_return_detail($trans_no, $stock_id, $quantity, $std_cost, $tran_unit_cost=0, $tran_unit = "", $tran_qty = 0, $conversion_factor = 1, $from_task_id=0, 
                                         $to_task_id = 0, $from_project_site_id = 0, $to_project_site_id = 0, $pri_line_id=0){
    $created_on = date('Y-m-d H:i:s');
    $created_by = $_SESSION['wa_current_user']->user;

    $sql = "INSERT INTO ".TB_PREF."site_material_return_details(trans_no, 
                stock_id, 
                pri_line_id, 
                qty, 
                standard_cost,
                tran_unit_cost, 
                tran_unit, tran_qty, 
                convertion_factor, from_task_id, to_task_id, 
                from_project_site_id, to_project_site_id, 
                created_by, created_on)

            VALUES (".db_escape($trans_no).", "
                     .db_escape($stock_id).", "
                     .db_escape($pri_line_id).", "
                     .db_escape($quantity).", "
                     .db_escape($std_cost).","
                     .db_escape($tran_unit_cost).", "
                     .db_escape($tran_unit).", ".db_escape($tran_qty).","
                     .db_escape($conversion_factor).", "
                     .db_escape($from_task_id).", ".db_escape($to_task_id).", "
                     .db_escape($from_project_site_id).", ".db_escape($to_project_site_id).", "
                     .db_escape($created_by).", ".db_escape($created_on).")";

    db_query($sql, "could not add_site_material_return_detail");
    
    if(!(db_insert_id() > 0)){
        return array("success" => false, "message" => "Could not Add Site Material Return details.");
    }else{
        return array("success" => true, "message" => "");
    }
}

function get_site_material_return_header($trans_no){
    $sql = "SELECT smr.*,
                l2.location_name to_location_name,
                pm.description from_project_description,
                pm2.description to_project_description 
               
            FROM ".TB_PREF."site_material_returns smr 
                INNER JOIN ".TB_PREF."projects_master pm 
                    ON smr.from_project_id = pm.project_id 
                INNER JOIN ".TB_PREF."projects_master pm2 
                    ON smr.to_project_id = pm2.project_id 
                INNER JOIN ".TB_PREF."locations l2  
                    ON smr.to_location = l2.loc_code 
   
            WHERE smr.id = ".db_escape($trans_no);
    
    $result = db_query($sql, "could not get_site_material_return_header");
    return db_fetch($result);
}

function delete_site_material_return_details_record($trans_no, $row_id){
    
    $smrh = get_site_material_return_header_record($trans_no);
    $sql = "DELETE FROM ".TB_PREF."site_material_return_details 
            WHERE id = ".db_escape($row_id);
    db_query($sql, "Could not delete_site_material_return_details_record");
    
    if(!(db_num_affected_rows() > 0)){
        return array("success" => false, "message" => "Could not Delete Site Material Return record #$row_id");
    }else{
        update_site_material_return_header_total($trans_no, $smrh['row_state']);
        return array("success" => true, "message" => "Site Material Return record #$row_id has been deleted.");
    }
}

function get_site_material_return_details($trans_no){
    $sql = "SELECT sad.*,
                    pt.description from_task_description,
                    pt2.description to_task_description,
                    ps.description from_project_site_description,
                    ps2.description to_project_site_description 
                    
            FROM ".TB_PREF."site_material_return_details sad 
                JOIN ".TB_PREF."site_material_returns sa  
                    ON sad.trans_no = sa.id  
                LEFT JOIN ".TB_PREF."project_tasks pt 
                    ON sad.from_task_id = pt.task_id 
                LEFT JOIN ".TB_PREF."project_tasks pt2 
                    ON sad.to_task_id = pt2.task_id 
                LEFT JOIN ".TB_PREF."project_sites ps  
                    ON sad.from_project_site_id = ps.id 
                LEFT JOIN ".TB_PREF."project_sites ps2   
                    ON sad.to_project_site_id = ps2.id 
                    
            WHERE sad.trans_no = ".db_escape($trans_no)."
            ORDER BY sad.id";
    
    return db_query($sql, "could not get_site_material_return_details");
}

function get_site_material_return_header_record($trans_no){
    $sql = "SELECT sa.* 
            FROM ".TB_PREF."site_material_returns sa 
            WHERE sa.id = ".  db_escape($trans_no);
    
    $result = db_query($sql, "could not get_site_material_return_header_record");
    return db_fetch($result);
}

function update_site_material_return_header_status($trans_no, $current_status, $next_status){
    global $approval_statuses;
    $sql = "UPDATE ".TB_PREF."site_material_returns  
                SET status = ".db_escape($next_status)." 
            WHERE id = ".db_escape($trans_no)." 
                AND status = ".db_escape($current_status);
    
    db_query($sql, "could not update_site_material_return_header_status");
    if(!(db_num_affected_rows() > 0)){
        return array("success" => false, "message" => "Could not Update Site Material Return Header #$trans_no Status to $approval_statuses[$next_status].");
    }else{
        return array("success" => true, "message" => "");
    }
}

function update_site_material_return_header_total($trans_no, $row_state){
    $lines_total = get_site_material_returns_details_lines_total($trans_no);
    $sql = "UPDATE ".TB_PREF."site_material_returns  
                SET total = ".db_escape($lines_total).",
                    row_state = ".db_new_row_state($row_state)." 
            WHERE id = ".db_escape($trans_no)." 
                AND row_state = ".db_escape($row_state);
    
    db_query($sql, "could not update_site_material_return_header_total");
    if(!(db_num_affected_rows() > 0)){
        return array("success" => false, "message" => "Could not Update Site Material Return Header #$trans_no Total.");
    }else{
        return array("success" => true, "message" => "");
    }
}

function get_site_material_returns_details_lines_total($trans_no){
    $sql = "SELECT SUM(tran_unit_cost * tran_qty) lines_total 
            FROM ".TB_PREF."site_material_return_details  
            WHERE trans_no = ".  db_escape($trans_no);
    
    $result = db_query($sql, "could not get_site_material_returns_details_lines_total");
    return db_result_value($result, "lines_total");    
}

?>