<?php

function write_asset_transfer_header($atr_id, $reference, $company_code, $employee_code, $tran_date, $comment, $total, $trans_type, $trans_no, 
                                     $approval_status, $from_project_id, $to_project_id, $from_asset_location, $to_asset_location, $id=0) {
    global $Refs;

    if ($id > 0) {
        $msg = update_asset_transfer($id, $reference, $company_code, $employee_code, $tran_date, $comment, $total, $approval_status, $from_project_id, 
                                     $to_project_id, $from_asset_location, $to_asset_location);
    } else {
        $msg = insert_asset_transfer($atr_id, $reference, $company_code, $employee_code, $tran_date, $comment, $total, $trans_type, $trans_no, 
                                     $approval_status, $from_project_id, $to_project_id, $from_asset_location, $to_asset_location);
    }
    
    if ($msg['success'] !== true) {
        return $msg;
    }

    $Refs->save($trans_type, $trans_no, $reference);
        
    return array_merge($msg, array("trans_type" => $trans_type, "trans_no" => $trans_no));
}
         
function insert_asset_transfer($atr_id, $reference, $company_code, $employee_code, $tran_date, $comment, $lines_total, $trans_type, $trans_no, 
                               $approval_status, $from_project_id, $to_project_id, $from_asset_location, $to_asset_location) {
    global $systypes_array;
    
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
        $sql = "INSERT INTO ".TB_PREF."asset_transfers 
                      ( atr_id, reference, 
                        type, trans_no, 
                        company_code, employee_code, 
                        tran_date, approval_status, 
                        comment, total, 
                        from_project_id, to_project_id, 
                        from_asset_location, to_asset_location, 
                        created_by, created_on )         
                VALUES( ".db_escape($atr_id).", ".db_escape($reference).", 
                        ".db_escape($trans_type).", ".db_escape($trans_no).", 
                        ".db_escape($company_code).", ".db_escape($employee_code).", 
                        ".db_escape(date2sql($tran_date)).", ".db_escape($approval_status).",  
                        ".db_escape($comment).", ".db_escape($lines_total).",                  
                        ".db_escape($from_project_id).", ".db_escape($to_project_id).",                  
                        ".db_escape($from_asset_location).", ".db_escape($to_asset_location).",                  
                        ".db_escape($created_by).", ".db_escape($created_on).")";
        
        db_query($sql, "could not insert_asset_requisition_transfer");
        
     
    if (! (db_insert_id() > 0)) {
        cancel_transaction();
        return array("success" => false, "message" => "Could not add a new ".$systypes_array[$trans_type]." header.", "atr_id" => $atr_id);
    } else {
        return array("success" => true, "message" => "Add a new ".$systypes_array[$trans_type]." header.", "at_id" => db_insert_id());
    }
}

function update_asset_transfer($id, $reference, $company_code, $employee_code, $tran_date, $comment, $total, $approval_status, $from_project_id, 
                               $to_project_id, $from_asset_location, $to_asset_location) {
    
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
    $sql = "UPDATE ".TB_PREF."asset_transfers  
                SET reference = ".db_escape($reference).", 
                    company_code = ".db_escape($company_code).",
                    employee_code = ".db_escape($employee_code).",
                    tran_date = ".db_escape(date2sql($tran_date)).",
                    comment = ".db_escape($comment).",
                    approval_status = ".db_escape($approval_status).",
                    total = ".db_escape($total).",
                    from_project_id = ".db_escape($from_project_id).",
                    to_project_id = ".db_escape($to_project_id).",
                    from_asset_location = ".db_escape($from_asset_location).",
                    to_asset_location = ".db_escape($to_asset_location).",
                    updated_by = ".db_escape($updated_by).",
                    updated_on = ".db_escape($updated_on)." 
            WHERE id = ".db_escape($id);
    
    db_query($sql, "update_asset_transfer");
    
        if (db_num_affected_rows() != 1) {
            cancel_transaction();
            return array("success" => false, "message" => "Could not update Asset Transfer #$id");
        } else {
            return array("success" => true, "message" => "Asset Transfer Record #$id has been updated.", "at_id" => $id);
        }
}

function write_asset_transfer_line($at_id, $at_line_id, $asset_id, $quantity, $current_value, $from_site_id, $to_site_id, $line_comment, $trans_type, 
                                   $trans_no, $row_state) {
    if ($at_line_id > 0) {
        $msg = update_asset_transfer_line($at_line_id, $asset_id, $quantity, $current_value, $from_site_id, $to_site_id, $line_comment, $row_state);
        
            return $msg;
    } else {
        $msg = insert_asset_transfer_line($at_id, $at_line_id, $asset_id, $quantity, $current_value, $line_comment, $trans_type, $trans_no, $from_site_id, 
                                          $to_site_id);
            return $msg;
    }
}
        
function add_asset_transfer_line($at_id, $at_line_id, $asset_id, $quantity, $asset_value, $line_comment, $trans_type, $trans_no, $from_site_id, 
                                 $to_site_id) {

    $msg = insert_asset_transfer_line($at_id, $at_line_id, $asset_id, $quantity, $asset_value, $line_comment, $trans_type, $trans_no, $from_site_id, 
                                      $to_site_id);

    return $msg;
}

function update_asset_transfer_line($at_line_id, $asset_id, $quantity, $current_value, $from_site_id, $to_site_id, $line_comment, $row_state) {
    
    $sql = "UPDATE ".TB_PREF."asset_transfer_details 
                SET quantity = ".db_escape($quantity).", 
                    asset_id = ".db_escape($asset_id).",
                    asset_value = ".db_escape($current_value).",
                    from_site_id = ".db_escape($from_site_id).",
                    to_site_id = ".db_escape($to_site_id).",
                    line_comment = ".db_escape($line_comment).",
                    row_state = ".db_escape(db_new_row_state($row_state))."
                
            WHERE id = ".db_escape($at_line_id);
    
    db_query($sql, "Could not update_asset_transfer_line");
    
    if(db_num_affected_rows() != 1){
        cancel_transaction();
        return array("success" => false, "message" => "Could not update Asset Transfer Request Details Line #$at_line_id");
    }else{
        return array("success" => true, "message" => "Asset Transfer Request Details Line #$at_line_id has been updated", "at_line_id" => $at_line_id);
    }
}        

function insert_asset_transfer_line($at_id, $at_line_id, $asset_id, $quantity, $asset_value, $line_comment, $trans_type, $trans_no, $from_site_id, 
                                    $to_site_id) {
    global $systypes_array;
    
    $sql = "INSERT INTO ".TB_PREF."asset_transfer_details 
                        ( type, trans_no, at_id, 
                          atr_line_id, asset_id, 
                          quantity, asset_value, 
                          from_site_id, to_site_id, 
                          line_comment )        
            VALUE       ( ".db_escape($trans_type).", ".db_escape($trans_no).", ".db_escape($at_id).",
                          ".db_escape($at_line_id).", ".db_escape($asset_id).",
                          ".db_escape($quantity).", ".db_escape($asset_value).",  
                          ".db_escape($from_site_id).", ".db_escape($to_site_id).",  
                          ".db_escape($line_comment).")";    
    
    db_query($sql, "could not insert_asset_transfer_line");
    
    $n = db_insert_id();
        if (! ($n > 0)) {
            cancel_transaction();
            return array("success" => false, "message" => "Could not add a new ".$systypes_array[$trans_type]." detail Line.");
        } else {
            return array("success" => true, "message" => "Add a new ".$systypes_array[$trans_type]." detail Line.", "at_line_id" => $n);
        }
}

function get_asset_transfer_header($asset_tran_req_no, $status=null){
    $sql = "SELECT atr.*,
                    at.reference at_reference, 
                    at.id at_id, 
                    at.tran_date at_tran_date, 
                    at.comment at_comment, 
                    pm.description from_project_description,  
                    pm2.description to_project_description,
                    aloc.location_name from_location_name,
                    aloc2.location_name to_location_name 
                    
            FROM ".TB_PREF."asset_transfer_requests atr 
                LEFT JOIN ".TB_PREF."projects_master pm 
                    ON atr.from_project_id = pm.project_id
                LEFT JOIN ".TB_PREF."projects_master pm2 
                    ON atr.to_project_id = pm2.project_id
                LEFT JOIN ".TB_PREF."asset_transfers at  
                    ON at.atr_id = atr.id  
                LEFT JOIN ".TB_PREF."asset_locations aloc  
                    ON atr.from_asset_location = aloc.loc_code   
                LEFT JOIN ".TB_PREF."asset_locations aloc2   
                    ON atr.to_asset_location = aloc2.loc_code   
            WHERE atr.id = ".db_escape($asset_tran_req_no);
    
    if($status !== null){
        $sql .= " AND atr.approval_status = ".db_escape($status);
    }
    
    $result = db_query($sql, "Could not get_asset_transfer_header");
    return db_fetch($result);
}
 
function get_asset_transfer_detail_line($selected_id){
    $sql = "SELECT atd.*         
            FROM ".TB_PREF."asset_transfer_details atd
            WHERE atd.id = ".db_escape($selected_id);
    
    $result = db_query($sql, "could not get_asset_transfer_detail_line");
    return db_fetch($result);
}

function delete_asset_transfer_detail($selected_id){
    $sql = "DELETE FROM ".TB_PREF."asset_transfer_details WHERE id=".db_escape($selected_id);
    db_query($sql, "could not delete_asset_transfer_detail");
    return db_num_affected_rows();
}

function get_asset_transfer_details($asset_tran_req_no){
    $sql = "SELECT atrd.*,
                    atrd.quantity req_quantity, 
                    atrd.transferred_qty, 
                    atrd.row_state, 
                    am.asset_code,
                    am.description asset_description,
                    am.asset_id,
                    am.current_value,
                    am.multiple_items,
                    ps.description from_site_description,
                    ps2.description to_site_description, 
                    atd.quantity at_quantity, 
                    atd.line_comment at_line_comment 

            FROM ".TB_PREF."asset_transfer_request_details atrd 
                LEFT JOIN ".TB_PREF."asset_master am 
                    ON atrd.asset_id = am.asset_id 
                LEFT JOIN ".TB_PREF."project_sites ps 
                    ON atrd.from_site_id = ps.id 
                LEFT JOIN ".TB_PREF."project_sites ps2 
                    ON atrd.to_site_id = ps2.id 
                LEFT JOIN ".TB_PREF."asset_transfer_details atd 
                    ON atrd.id = atd.atr_line_id  
            WHERE atrd.atr_id = ".db_escape($asset_tran_req_no)." 
            GROUP BY atrd.id";
    
    return db_query($sql, "Could not get_asset_transfer_details");
}

function get_asset_transfer_details_line_count($asset_tran_no){
    $sql = "SELECT COUNT(id) line_count 
            FROM ".TB_PREF."asset_transfer_details  
            WHERE at_id = ".db_escape($asset_tran_no);
    
    $result = db_query($sql, "could not get_asset_transfer_details_line_count");
    $count =  db_fetch($result);   
    if(isset($count['line_count']) && $count['line_count'] > 0){
        return $count['line_count'];
    }    
    return 0;
}
   
function release_asset_transfer_for_approval($asset_tran_no){
    $current_user_id = $_SESSION['wa_current_user']->user;
    $ath = get_asset_transfer_header($asset_tran_no);
    
    $info = approve_document(ST_ASSET_TRANSFER, $asset_tran_no, $ath['to_project_id'], $ath['total'], $ath['next_approval_group_id'], 
                             $current_user_id, $ath['row_state']);

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

    return array("success" => true, "at_id" => $asset_tran_no, "message" => $info['message']);  
}

function update_asset_transfer_detail_line_total($asset_tran_no, $row_state, $trans_type) {
    
    $total_quantity = get_asset_transfer_lines_total($asset_tran_no, $trans_type);
    
    $sql = "UPDATE ".TB_PREF."asset_transfer_request_details   
            SET row_state = ".db_escape(db_new_row_state($row_state)).", 
                transferred_qty = ".db_escape($total_quantity)." 
            WHERE id = ".db_escape($asset_tran_no)." 
                AND transferred_qty <= ".db_escape($total_quantity)." 
                AND row_state = ".db_escape($row_state);
    
    db_query($sql, "could not update_asset_transfer_detail_line_total");
    
    $n = db_num_affected_rows();
    if($n != 1){
        cancel_transaction();
        return array("success" => false, "message" => "Could not update Asset Transfer Request Detail Line Total #$asset_tran_no");
    }else{
        return array("success" => true, "message" => "", "at_line_id" => $asset_tran_no);
    }
}

function get_asset_transfer_lines_total($atr_line_id, $trans_type) {
    $sql = "SELECT SUM(quantity) total_quantity  
            FROM ".TB_PREF."asset_transfer_details 
            WHERE atr_line_id = ".db_escape($atr_line_id)."    
                AND type = ".db_escape($trans_type);   
    
    $result = db_query($sql, "could not get_asset_transfer_lines_total");
    
    return db_result_value($result, 'total_quantity');
}

function update_asset_requisition_transfer_detail_line_total($asset_req_tran_line_no, $row_state, $trans_type) {
    
    $total_quantity = get_asset_transfer_lines_total($asset_req_tran_line_no, $trans_type);
    
    $sql = "UPDATE ".TB_PREF."asset_transfer_details   
            SET row_state = ".db_escape(db_new_row_state($row_state)).", 
                transferred_qty = ".db_escape($total_quantity)." 
            WHERE id = ".db_escape($asset_req_tran_line_no)." 
                AND transferred_qty <= ".db_escape($total_quantity)." 
                AND row_state = ".db_escape($row_state);
    
    db_query($sql, "update_asset_requisition_transfer_detail_line_total");
    
    $n = db_num_affected_rows();
        if ($n != 1) {
            cancel_transaction();
            return array("success" => false, "message" => "Could not update Asset Transfer Detail Line Total #$asset_req_tran_line_no");
        } else {
            return array("success" => true, "message" => "", "at_line_id" => $asset_req_tran_line_no);
        }
}

function get_asset_requisition_transfer_with_asset_transfer_request_header_by_art_no($asset_req_trn_id) {
    $sql = "SELECT art.id asset_req_tran_no, art.trans_no, atr.*,
                pm.description from_project_description,  
                pm2.description to_project_description,
                falo.location_name from_location_name,
                talo.location_name to_location_name,
                art.to_asset_location art_to_asset_location,  
                atr.to_asset_location atr_to_asset_location 
            FROM ".TB_PREF."asset_transfers art 
                JOIN ".TB_PREF."asset_transfer_requests atr
                    ON art.atr_id = atr.id 
                LEFT JOIN ".TB_PREF."projects_master pm 
                    ON atr.from_project_id = pm.project_id
                LEFT JOIN ".TB_PREF."projects_master pm2 
                    ON atr.to_project_id = pm2.project_id
                LEFT JOIN ".TB_PREF."asset_locations falo  
                    ON atr.from_asset_location = falo.loc_code  
                LEFT JOIN ".TB_PREF."asset_locations talo  
                    ON atr.to_asset_location = talo.loc_code  
            WHERE art.id = ".db_escape($asset_req_trn_id);
    
    $result = db_query($sql, "get_asset_requisition_transfer_with_asset_transfer_request_header_by_art_no");
    return db_fetch($result);
}

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

function get_asset_transfer_details_by_filters($id=0, $at_id=0, $trans_type=0, $trans_no=0, $atr_line_id=0, $asset_id=0) {
    $sql = "SELECT atd.*,  
                am.asset_code,
                am.description asset_description,
                am.asset_id,
                am.current_value,
                am.multiple_items,
                ps.description from_site_description,
                ps2.description to_site_description 
            FROM ".TB_PREF."asset_transfer_details atd 
                JOIN ".TB_PREF."asset_master am 
                    ON atd.asset_id = am.asset_id 
                LEFT JOIN ".TB_PREF."project_sites ps 
                    ON atd.from_site_id = ps.id 
                LEFT JOIN ".TB_PREF."project_sites ps2 
                    ON atd.to_site_id = ps2.id 
            WHERE 1 = 1 ";
    
        if ($id > 0) {
            $sql .= " AND atd.id = ".db_escape($id);
        }
        
        if ($at_id > 0) {
            $sql .= " AND atd.at_id = ".db_escape($at_id);
        }

        if ($trans_type > 0) {
            $sql .= " AND atd.type = ".db_escape($trans_type);
        }

        if ($trans_no > 0) {
            $sql .= " AND atd.trans_no = ".db_escape($trans_no);
        }

        if ($atr_line_id > 0) {
            $sql .= " AND atd.atr_line_id = ".db_escape($atr_line_id);
        }

        if ($asset_id > 0) {
            $sql .= " AND atd.asset_id = ".db_escape($asset_id);
        }

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

function get_asset_requisition_transfer_details_with_asset_transfer_receipt_count($id=0, $at_id=0, $trans_no=0, $atr_line_id=0, $asset_id=0) {
    $sql = "SELECT artd.id, artd.type, artd.trans_no, artd.atr_line_id, artd.line_comment, artd.quantity requisted_qty, artd.row_state, 
                am.asset_code,
                am.description asset_description,
                am.asset_id,
                am.current_value,
                am.multiple_items,
                req_d.asset_value,
                req_d.from_site_id,
                req_d.to_site_id,
                ps.description from_site_description,
                ps2.description to_site_description, 
                SUM(rcptd.quantity) received_qty 
            FROM ".TB_PREF."asset_transfer_details artd 
                JOIN ".TB_PREF."asset_master am 
                    ON artd.asset_id = am.asset_id 
                LEFT JOIN ".TB_PREF."asset_transfer_details rcptd 
                    ON artd.id = rcptd.atr_line_id  
                        AND rcptd.type = ".db_escape(ST_ASSET_TRANSFER_RECEIPT)." 
                JOIN ".TB_PREF."asset_transfer_request_details req_d   
                    ON artd.atr_line_id = req_d.id  
                LEFT JOIN ".TB_PREF."project_sites ps 
                    ON req_d.from_site_id = ps.id 
                LEFT JOIN ".TB_PREF."project_sites ps2 
                    ON req_d.to_site_id = ps2.id 
            WHERE artd.type = ".db_escape(ST_ASSET_REQUISITION_TRANSFER);
    
        if ($id > 0) {
            $sql .= " AND artd.id = ".db_escape($id);
        }
        
        if ($at_id > 0) {
            $sql .= " AND artd.at_id = ".db_escape($at_id);
        }

        if ($trans_no > 0) {
            $sql .= " AND artd.trans_no = ".db_escape($trans_no);
        }

        if ($atr_line_id > 0) {
            $sql .= " AND artd.atr_line_id = ".db_escape($atr_line_id);
        }

        if ($asset_id > 0) {
            $sql .= " AND artd.asset_id = ".db_escape($asset_id);
        }

            $sql .= " GROUP BY artd.id ORDER BY artd.id";
            
    return db_query($sql, "get_asset_requisition_transfer_details_with_asset_transfer_receipt_count");
}

function check_all_asset_requisition_transfer_lines_fully_transferred($asset_req_trn_id){
    $sql = "SELECT id   
            FROM ".TB_PREF."asset_transfer_details    
            WHERE quantity > transferred_qty    
                AND at_id = ".db_escape($asset_req_trn_id);
    
    $result = db_query($sql, "check_all_asset_requisition_transfer_lines_fully_transferred");
    $num_recs = db_num_rows($result);
    
        if ($num_recs > 0) {
            return false;
        } else {
            return true;
        }
}

function get_asset_transfer_header_details($asset_tran_req_no, $status=null){
    $sql = "SELECT at.*,
                at.reference at_reference, 
                at.id at_id, 
                at.tran_date at_tran_date, 
                at.comment at_comment, 
                pm.description from_project_description,  
                pm2.description to_project_description,
                aloc.location_name from_location_name,
                aloc2.location_name to_location_name 
            FROM ".TB_PREF."asset_transfers at 
                LEFT JOIN ".TB_PREF."projects_master pm 
                    ON at.from_project_id = pm.project_id
                LEFT JOIN ".TB_PREF."projects_master pm2 
                    ON at.to_project_id = pm2.project_id
                LEFT JOIN ".TB_PREF."asset_locations aloc  
                    ON at.from_asset_location = aloc.loc_code   
                LEFT JOIN ".TB_PREF."asset_locations aloc2   
                    ON at.to_asset_location = aloc2.loc_code   
            WHERE at.id = ".db_escape($asset_tran_req_no);
    
    if ($status !== null) {
        $sql .= " AND at.approval_status = ".db_escape($status);
    }
    
    $result = db_query($sql, "get_asset_transfer_header_details");
    return db_fetch($result);
}

function get_asset_transfer_details_by_at_id($at_id) {
    $sql = "SELECT atd.*,  
                am.asset_code,
                am.description asset_description,
                am.asset_id,
                am.current_value,
                am.multiple_items,
                ps.description from_site_description,
                ps2.description to_site_description 
            FROM ".TB_PREF."asset_transfer_details atd 
                JOIN ".TB_PREF."asset_master am 
                    ON atd.asset_id = am.asset_id 
                LEFT JOIN ".TB_PREF."project_sites ps 
                    ON atd.from_site_id = ps.id 
                LEFT JOIN ".TB_PREF."project_sites ps2 
                    ON atd.to_site_id = ps2.id 
            WHERE atd.at_id = ".db_escape($at_id);
    
        return db_query($sql, "get_asset_transfer_details_by_at_id");
}

function update_asset_transfer_header_total($at_id) {
    $at_hd = get_asset_transfer_header_record($at_id);
    
    $total = get_asset_transfer_detail_lines_total($at_hd['type'], $at_hd['trans_no']);
    
    if($at_hd['total'] == $total){
        return array("success" => true, "message" => "", "at_id" => $at_id);
    }
    
    $sql = "UPDATE ".TB_PREF."asset_transfers   
                SET total = ".db_escape($total)." 
            WHERE id = ".db_escape($at_id);
    
    db_query($sql, "update_asset_transfer_header_total");
    
        $n = db_num_affected_rows();
            if ($n != 1) {
                cancel_transaction();
                return array("success" => false, "message" => "Could not update Asset Transfer Header Total #$at_id.");
            } else {
                return array("success" => true, "message" => "", "at_id" => $at_id);
            }
}

function get_asset_transfer_detail_lines_total($trans_type, $trans_no) {
    $sql = "SELECT SUM(asset_value * quantity) total 
            FROM ".TB_PREF."asset_transfer_details 
            WHERE type = ".db_escape($trans_type)."   
                AND trans_no = ".db_escape($trans_no);   
    
        $result = db_query($sql, "get_asset_transfer_detail_lines_total");
        return db_result_value($result, "total");
}

function update_asset_transfer_approval_status($id, $previous_ap_status, $new_ap_status) {
    $sql = "UPDATE ".TB_PREF."asset_transfers  
                SET approval_status = ".db_escape($new_ap_status)."
            WHERE id = ".db_escape($id)." 
                AND approval_status = ".db_escape($previous_ap_status);
    
        db_query($sql, "update_asset_transfer_approval_status");
        return db_num_affected_rows();
}

function update_asset_transfer_row_status($id, $row_state) {
    $sql = "UPDATE ".TB_PREF."asset_transfers 
                SET row_state = ".db_escape(db_new_row_state($row_state))." 
            WHERE id = ".db_escape($id)." 
                AND row_state = ".db_escape($row_state);
                
    db_query($sql, "update_asset_transfer_row_status");
    return db_num_affected_rows();
}

?>