<?php

function get_asset_verification_count_details($asset_verification_count_no, $sheet_no) {
    $sql = "SELECT scd.*,
                    sm.description,                  
                    sm.asset_code,                   
                    sm.current_value,                  
                    sm.multiple_items                  
            FROM ".TB_PREF."asset_verification_count_details scd 
                INNER JOIN ".TB_PREF."asset_master sm 
                    ON sm.asset_id = scd.asset_id  
            WHERE scd.asset_verification_count_no = ".db_escape($asset_verification_count_no);
    
        if ($sheet_no > 0) {
            $sql .= " AND scd.sheet_no = ".db_escape($sheet_no);
        }

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

function get_asset_verification_counted_details($asset_verification_count_no, $sheet_no) {
    $sql = "SELECT * 
            FROM ".TB_PREF."asset_verification_count_details  
            WHERE asset_verification_count_no = ".db_escape($asset_verification_count_no)." 
                AND sheet_no = ".db_escape($sheet_no);               

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

function get_asset_verification_count_detail($id) {
    $sql = "SELECT scd.*,
                sm.description asset_description,                    
                sm.asset_code,                     
                sm.multiple_items                     
            FROM ".TB_PREF."asset_verification_count_details scd 
                INNER JOIN ".TB_PREF."asset_master sm 
                    ON sm.asset_id = scd.asset_id 
            WHERE scd.id = ".db_escape($id);  

        $result = db_query($sql, "get_asset_verification_detail"); 
        return db_fetch($result);
}

function get_asset_verification_count_header($asset_verification_count_no) {
    $sql = "SELECT av.*,
                loc.location_name,
                pm.description project_description,
                pt.description task_description 
            FROM ".TB_PREF."asset_verification_counts av 
                INNER JOIN ".TB_PREF."asset_locations loc 
                    ON av.loc_code = loc.loc_code 
                LEFT JOIN ".TB_PREF."projects_master pm 
                    ON av.project_id = pm.project_id 
                LEFT JOIN ".TB_PREF."project_tasks pt 
                    ON av.task_id = pt.task_id 
             WHERE av.asset_verification_count_no = ".db_escape($asset_verification_count_no);	
                   
       $result = db_query($sql, "get_asset_verification_count_header");
       return db_fetch($result);
}

function get_asset_verification_count_header_record($asset_verification_count_no) {
    $sql = "SELECT *
            FROM ".TB_PREF."asset_verification_counts                         
            WHERE asset_verification_count_no = ".db_escape($asset_verification_count_no);	

       $result = db_query($sql, "get_asset_verification_count_header_record");
       return db_fetch($result);
}

function add_asset_verification_count_header($loc_code, $reference, $status_id, $tran_date, $comments, $project_id, $task_id) {
    $id_user = $_SESSION['wa_current_user']->user;
    $datetime = date('Y-m-d H:i:s');        

        $sql = "INSERT INTO ".TB_PREF."asset_verification_counts 
                       (loc_code, reference,  
                        status_id, tran_date,
                        comments, project_id, task_id,
                        created_by, created_on)

                VALUES (".db_escape($loc_code).", ".db_escape($reference).", " 
                         .db_escape($status_id).", ".db_escape(date2sql($tran_date)).", " 
                         .db_escape($comments).", ".db_escape($project_id).", ".db_escape($task_id).", "
                         .db_escape($id_user).", ".db_escape($datetime).")";

        db_query($sql, "add_asset_verification_count_header");        
        return db_insert_id();
}

function add_asset_verification_count_detail($asset_verification_count_no, $sheet_no, $asset_id, $good_qty, $damaged_qty, $system_qty, $current_value, $status_id) {
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date("Y-m-d H:i:s");
        
        $sql = "INSERT INTO ".TB_PREF."asset_verification_count_details (
                        asset_verification_count_no, sheet_no, 
                        asset_id, status_id, 
                        good_qty, damaged_qty,
                        system_qty, current_value,
                        created_by, created_on) 
                VALUES (".db_escape($asset_verification_count_no).", ".db_escape($sheet_no).", "
                         .db_escape($asset_id).", " .db_escape($status_id).", "
                         .db_escape($good_qty).", ".db_escape($damaged_qty).", "
                         .db_escape($system_qty).", ".db_escape($current_value).", "
                         .db_escape($created_by).", ".db_escape($created_on).")";
        
        db_query($sql, "add_asset_verification_count_detail");

}

function update_asset_verification_count_detail($id, $sheet_no, $good_qty, $damaged_qty, $status_id) {
    $user_id = $_SESSION['wa_current_user']->user;
    $time = date("Y-m-d H:i:s");
        
        $sql = "UPDATE ".TB_PREF."asset_verification_count_details 
                    SET sheet_no = ".db_escape($sheet_no).",
                        good_qty = ".db_escape($good_qty).",
                        damaged_qty = ".db_escape($damaged_qty).",
                        status_id = ".db_escape($status_id).",
                        updated_by = ".db_escape($user_id).",
                        updated_on = ".db_escape($time)." 
                WHERE id = ".db_escape($id)." 
                    AND status_id IN(".SC_NEW.", ".SC_COUNTING.")"; 

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

function delete_asset_verification_count_detail($id) {
    $sql = "DELETE 
            FROM ".TB_PREF."asset_verification_count_details 
            WHERE id = ".$id." AND status_id IN(0, ".SC_NEW.",".SC_COUNTING.")"; 

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

function update_asset_verification_count_header($asset_verification_count_no, $loc_code, $reference, $status_id, $tran_date, $comments, $project_id, $task_id) {
    $id_user = $_SESSION['wa_current_user']->user;
    $datetime = date('Y-m-d H:i:s');     
    
        $sql = "UPDATE ".TB_PREF."asset_verification_counts 
                SET reference = ".db_escape($reference).",
                    loc_code =  ".db_escape($loc_code).",
                    tran_date = ".db_escape(date2sql($tran_date)).",
                    comments = ".db_escape($comments).",
                    status_id = ".db_escape($status_id).",                      
                    project_id = ".db_escape($project_id).",
                    task_id = ".db_escape($task_id).",
                    updated_by = ".db_escape($id_user).",   
                    updated_on = ".db_escape($datetime)."
                WHERE asset_verification_count_no = ".$asset_verification_count_no." 
                    AND status_id = ".SC_NEW;

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

function update_asset_verification_count_header_status($asset_verification_count_no, $new_status_id, $old_status_id) {
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date("Y-m-d H:i:s");
        
        $sql = "UPDATE ".TB_PREF."asset_verification_counts 
                    SET status_id = ".db_escape($new_status_id).",
                        updated_by = ".db_escape($updated_by).",
                        updated_on = ".db_escape($updated_on)." 
                WHERE asset_verification_count_no = ".$asset_verification_count_no." 
                    AND status_id = ".$old_status_id; 

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

function update_asset_verification_count_completed($asset_verification_count_no, $new_status_id, $old_status_id, $total_adjustment_value) {
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date("Y-m-d H:i:s");
        
        $sql = "UPDATE ".TB_PREF."asset_verification_counts 
                SET status_id = ".db_escape($new_status_id).",
                    total_adj_value = ".db_escape($total_adjustment_value).",
                    updated_by = ".db_escape($updated_by).",
                    updated_on = ".db_escape($updated_on)." 
                WHERE asset_verification_count_no = ".db_escape($asset_verification_count_no)." 
                    AND status_id = ".db_escape($old_status_id); 

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

function update_asset_verification_count_details_all_status($asset_verification_count_no, $new_status_id, $old_status_id) {
    $sql = "UPDATE ".TB_PREF."asset_verification_count_details 
                SET status_id = ".db_escape($new_status_id)." 
            WHERE asset_verification_count_no = ".$asset_verification_count_no." 
                AND status_id = ".$old_status_id; 

        db_query($sql, "update_asset_verification_count_details_all_status");
}

function get_asset_verification_count_sheets($asset_verification_count_no) {
    $sql = "SELECT DISTINCT sheet_no 
            FROM ".TB_PREF."asset_verification_count_details  
            WHERE asset_verification_count_no = ".db_escape($asset_verification_count_no);
               
        return db_query($sql, "get_asset_verification_count_sheets"); 
}

function check_asset_verification_count_item_exists($asset_verification_count_no, $asset_id) {
    $sql = "SELECT sheet_no 
            FROM ".TB_PREF."asset_verification_count_details 
            WHERE asset_verification_count_no = $asset_verification_count_no 
                AND asset_id = ".db_escape($asset_id);

        $result = db_query($sql, "check_asset_verification_count_item_exists"); 
        return db_fetch($result);
}

function get_asset_verification_count_duplicate_items($asset_verification_count_no) {
    $sql = "SELECT count(asset_id) cnt , asset_id 
            FROM ".TB_PREF."asset_verification_count_details  
            WHERE asset_verification_count_no = ".db_escape($asset_verification_count_no)." 
            GROUP BY asset_id 
            HAVING cnt > 1 
            LIMIT 20 ";
    
    return db_query($sql, "get_asset_verification_count_duplicate_items"); 
}

function get_asset_verification_count_duplicate_item_details($asset_verification_count_no, $asset_id) {
    $sql = "SELECT scd.asset_id, scd.sheet_no, sm.description asset_description    
            FROM ".TB_PREF."asset_verification_count_details scd 
                JOIN ".TB_PREF."asset_master sm 
                    ON scd.asset_id = sm.asset_id 
            WHERE scd.asset_verification_count_no = $asset_verification_count_no 
                AND scd.asset_id = ".db_escape($asset_id);
    
    return db_query($sql, "get_asset_verification_count_duplicate_item_details"); 
}

function get_asset_verification_count_sheet_item($asset_verification_count_no, $sheet_no, $asset_id) {
    $sql = "SELECT * 
            FROM ".TB_PREF."asset_verification_count_details 
            WHERE asset_verification_count_no = ".db_escape($asset_verification_count_no)."  
                AND sheet_no = ".db_escape($sheet_no)." 
                AND asset_id = ".db_escape($asset_id);

        $result = db_query($sql, "get_asset_verification_count_sheet_item"); 
        return db_fetch($result);
}

?>