<?php

//function dashboard_get_sql_for_transport_inventory_issues_receipts_consumption($from_date, $to_date, $vehicle_id=0, $ietm_code='') {
//    
//    
//    $sql = "SELECT h.type trans_type, h.id trans_no, h.reference, h.ai_date tran_date, h.project_id, 
//        h.linked_field_id vehicle_id, (d.qty*d.standard_cost*-1) cost, h.movement_type movement_type_id 
//            FROM ".TB_PREF."stock_moves d   
//                JOIN ".TB_PREF."stock_adjustments  h  
//                    ON h.type =d.type 
//                    AND h.id = d.trans_no  
//            WHERE h.ai_date >= ".db_escape($from_date)." 
//                AND h.ai_date <= ".db_escape($to_date)."    
//                AND h.linked_field_type = ".db_escape(IMLF_VEHICLE);   
//    
//    if($vehicle_id > 0){
//        $sql .= " AND h.linked_field_id = ".db_escape($vehicle_id);
//    }
//    if(strlen($ietm_code) > 0){
//        $sql .= " AND d.stock_id = ".db_escape($ietm_code);
//    }
//  
//    
//    return  $sql;
//  
//     
//    
//}
//
//function dashboard_get_sql_for_transport_daily_item_issues_consumption($from_date, $to_date, $vehicle_id=0, $ietm_code='') {
//    
//    $sql = "SELECT h.type trans_type, h.trans_no, h.reference, h.tran_date, h.project_id, d.linked_field_id vehicle_id, (d.qty * d.standard_cost) cost, h.movement_type movement_type_id   
//            FROM ".TB_PREF."daily_item_issue_details d   
//                JOIN ".TB_PREF."daily_item_issues  h  
//                    ON h.type =d.type 
//                    AND h.trans_no = d.trans_no  
//            WHERE h.tran_date >= ".db_escape($from_date)." 
//                AND h.tran_date <= ".db_escape($to_date)."    
//                AND h.linked_field_type = ".db_escape(IMLF_VEHICLE);   
//    if($vehicle_id > 0){
//        $sql .= " AND d.linked_field_id = ".db_escape($vehicle_id);
//    }
//    if(strlen($ietm_code) > 0){
//        $sql .= " AND d.stock_id = ".db_escape($ietm_code);
//    }
//  
//    
//    return  $sql;
//    
//}
//
//function dashboard_get_sql_for_transport_pr_issue_consumption($from_date, $to_date, $vehicle_id=0, $ietm_code='') {
//    
//    $sql = "SELECT ".db_escape(ST_PR_ISSUE)." trans_type, h.pri_no trans_no, h.reference, h.pri_date tran_date, h.project_id, h.linked_field_id vehicle_id, (d.qty_issued * d.unit_cost) cost, h.movement_type movement_type_id    
//            FROM ".TB_PREF."purchase_requistion_issue_details d    
//                JOIN ".TB_PREF."purchase_requisition_issues h    
//                    ON h.pri_no = d.pri_no  
//            WHERE h.pri_date >= ".db_escape($from_date)." 
//                AND h.pri_date <= ".db_escape($to_date)."    
//                AND h.linked_field_type = ".db_escape(IMLF_VEHICLE);   
//    if($vehicle_id > 0){
//        $sql .= " AND h.linked_field_id = ".db_escape($vehicle_id);
//    }
//    if(strlen($ietm_code) > 0){
//        $sql .= " AND d.item_code = ".db_escape($ietm_code);
//    }  
//    
//    return  $sql;
//    
//}
//
//function dashboard_get_sql_for_transport_pr_return_consumption($from_date, $to_date, $vehicle_id=0, $ietm_code='') {
//    
//    $sql = "SELECT ".db_escape(ST_PR_RETURN)." trans_type, h.prr_no trans_no, h.reference, h.prr_date tran_date, h.project_id, i.linked_field_id vehicle_id, (d.qty * d.unit_cost) cost, i.movement_type movement_type_id     
//            FROM ".TB_PREF."purchase_requistion_return_details d   
//                JOIN ".TB_PREF."purchase_requisition_returns  h  
//                    ON h.prr_no = d.prr_no  
//                JOIN ".TB_PREF."purchase_requisition_issues i   
//                    ON h.pri_no = i.pri_no    
//            WHERE h.prr_date >= ".db_escape($from_date)." 
//                AND h.prr_date <= ".db_escape($to_date)."    
//                AND i.linked_field_type = ".db_escape(IMLF_VEHICLE);   
//    if($vehicle_id > 0){
//        $sql .= " AND i.linked_field_id = ".db_escape($vehicle_id);
//    }
//    if(strlen($ietm_code) > 0){
//        $sql .= " AND d.item_code = ".db_escape($ietm_code);
//    }  
//  
//    
//    return  $sql;
//    
//}
//
//
//function dashboard_get_sql_for_transport_mr_issue_consumption($from_date, $to_date, $vehicle_id=0, $ietm_code='') {
//    
//    $sql = "SELECT ".db_escape(ST_MATERIAL_ISSUE)." trans_type, h.im_no trans_no, h.reference, h.im_date tran_date, h.project_id, mr.linked_field_id vehicle_id, (d.qty_issued * d.unit_cost) cost, mr.movement_type movement_type_id   
//            FROM ".TB_PREF."issue_material_details d    
//                JOIN ".TB_PREF."issue_materials h    
//                    ON h.im_no = d.im_no    
//                JOIN ".TB_PREF."material_requisitions mr     
//                    ON mr.mr_no = h.source_mr_no   
//            WHERE h.im_date >= ".db_escape($from_date)." 
//                AND h.im_date <= ".db_escape($to_date)."    
//                AND mr.linked_field_type = ".db_escape(IMLF_VEHICLE);   
//    if($vehicle_id > 0){
//        $sql .= " AND mr.linked_field_id = ".db_escape($vehicle_id);
//    }
//    if(strlen($ietm_code) > 0){
//        $sql .= " AND d.item_code = ".db_escape($ietm_code);
//    }  
//  
//    
//    return  $sql;
//    
//}
//
//
//function dashboard_get_sql_for_transport_mr_return_consumption($from_date, $to_date, $vehicle_id=0, $ietm_code='') {
//    
//    $sql = "SELECT ".db_escape(ST_MATERIAL_ISSUE)." trans_type, h.rm_no trans_no, h.reference, h.rm_date tran_date, h.project_id, mr.linked_field_id vehicle_id, (d.qty_returned * d.unit_cost) cost, mr.movement_type movement_type_id  
//            FROM ".TB_PREF."return_material_details d    
//                JOIN ".TB_PREF."return_materials h    
//                    ON h.rm_no = d.rm_no     
//                JOIN ".TB_PREF."material_requisitions mr     
//                    ON mr.mr_no = h.source_mr_no  
//            WHERE h.rm_date >= ".db_escape($from_date)." 
//                AND h.rm_date <= ".db_escape($to_date)."    
//                AND mr.linked_field_type = ".db_escape(IMLF_VEHICLE);   
//    if($vehicle_id > 0){
//        $sql .= " AND mr.linked_field_id = ".db_escape($vehicle_id);
//    }
//    if(strlen($ietm_code) > 0){
//        $sql .= " AND d.item_code = ".db_escape($ietm_code);
//    }  
//  
//    
//    return  $sql;
//    
//}
//
//
//function dashboard_get_sql_for_transport_item_consumption($from_date, $to_date, $vehicle_id=0, $ietm_code='') {
//    
//    $sql_issues_receipts = dashboard_get_sql_for_transport_inventory_issues_receipts_consumption($from_date, $to_date, $vehicle_id, $ietm_code);
//    $sql_daily_item_issues = dashboard_get_sql_for_transport_daily_item_issues_consumption($from_date, $to_date, $vehicle_id, $ietm_code);
//    $sql_pr_issues = dashboard_get_sql_for_transport_pr_issue_consumption($from_date, $to_date, $vehicle_id, $ietm_code);
//    $sql_pr_returns = dashboard_get_sql_for_transport_pr_return_consumption($from_date, $to_date, $vehicle_id, $ietm_code);
//    $sql_mr_issues = dashboard_get_sql_for_transport_mr_issue_consumption($from_date, $to_date, $ietm_code);
//    $sql_mr_returns = dashboard_get_sql_for_transport_mr_return_consumption($from_date, $to_date, $vehicle_id, $ietm_code);
//
//    
//    $sql = $sql_issues_receipts 
//        ." UNION ".$sql_daily_item_issues 
//        ." UNION ".$sql_pr_issues 
//        ." UNION ".$sql_pr_returns 
//        ." UNION ".$sql_mr_issues 
//        ." UNION ".$sql_mr_returns  
//            ;   
//    
//    return $sql;
//}

function dashboard_get_asset_consumption_assets($from_date, $to_date, $ietm_code='') {
    
//    $sql = dashboard_get_sql_for_transport_item_consumption($from_date, $to_date, $ietm_code);  
    $sql = dashboard_get_sql_for_inventory_item_consuption_items($from_date, $to_date, -1, $ietm_code, IMLF_ASSET);
    
    $sql = "SELECT linked_field_id asset_id, SUM(qty * unit_cost) grand_total  
            FROM ($sql) tmp_table 
            GROUP BY linked_field_id    
            ORDER BY grand_total DESC";
    
    return db_query($sql); 
                
}


function dashboard_get_period_item_asset_consumption_total($from_date, $to_date, $asset_id, $ietm_code='') {
    
//    $sql = dashboard_get_sql_for_transport_item_consumption($from_date, $to_date, $vehicle_id, $ietm_code);
    $sql = dashboard_get_sql_for_inventory_item_consuption_items($from_date, $to_date, -1, $ietm_code, IMLF_ASSET, $asset_id);
    
    $sql = "SELECT SUM(qty * unit_cost) total_cost  
            FROM ($sql) tmp_table ";
    
    $result = db_query($sql, "dashboard_get_period_item_asset_consumption_total"); 
    
    return db_result_value($result, "total_cost");
                
}
