<?php

function dashboard_get_sql_for_inventory_daily_item_issues_movement_types($from_date, $to_date, $movement_type_id=0) {
    
    $sql = "SELECT h.type trans_type, h.trans_no, h.tran_date, h.project_id, h.movement_type movement_type_id, (d.qty * d.standard_cost) cost, m.name  movement_type_name 
            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 
                JOIN ".TB_PREF."movement_types m    
                    ON m.id = h.movement_type  
            WHERE h.tran_date >= ".db_escape($from_date)." 
                AND h.tran_date <= ".db_escape($to_date);
    
    if($movement_type_id > 0){
        $sql .= " AND h.movement_type = ".db_escape($movement_type_id);
    }
  
    
    return  $sql;
    
}

function dashboard_get_sql_for_inventory_pr_issue_movement_types($from_date, $to_date, $movement_type_id=0) {
    
    $sql = "SELECT ".db_escape(ST_PR_ISSUE)." trans_type, h.pri_no trans_no, h.pri_date tran_date, h.project_id, h.movement_type movement_type_id, (d.qty_issued * d.unit_cost) cost, m.name  movement_type_name 
            FROM ".TB_PREF."purchase_requistion_issue_details d    
                JOIN ".TB_PREF."purchase_requisition_issues h    
                    ON h.pri_no = d.pri_no   
                JOIN ".TB_PREF."movement_types m    
                    ON m.id = h.movement_type  
            WHERE h.pri_date >= ".db_escape($from_date)." 
                AND h.pri_date <= ".db_escape($to_date);
    
    if($movement_type_id > 0){
        $sql .= " AND h.movement_type = ".db_escape($movement_type_id);
    }
  
    
    return  $sql;
    
}

function dashboard_get_sql_for_inventory_pr_return_movement_types($from_date, $to_date, $movement_type_id=0) {
    
    $sql = "SELECT ".db_escape(ST_PR_RETURN)." trans_type, h.prr_no trans_no, h.prr_date tran_date, h.project_id, i.movement_type movement_type_id, (d.qty * d.unit_cost) cost, m.name  movement_type_name 
            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   
                JOIN ".TB_PREF."movement_types m    
                    ON m.id = i.movement_type  
            WHERE h.prr_date >= ".db_escape($from_date)." 
                AND h.prr_date <= ".db_escape($to_date);
    
    if($movement_type_id > 0){
        $sql .= " AND i.movement_type = ".db_escape($movement_type_id);
    }
  
    
    return  $sql;
    
}


function dashboard_get_sql_for_inventory_mr_issue_movement_types($from_date, $to_date, $movement_type_id=0) {
    
    $sql = "SELECT ".db_escape(ST_MATERIAL_ISSUE)." trans_type, h.im_no trans_no, h.im_date tran_date, h.project_id, mr.movement_type movement_type_id, (d.qty_issued * d.unit_cost) cost, m.name  movement_type_name 
            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     
                JOIN ".TB_PREF."movement_types m    
                    ON m.id = mr.movement_type  
            WHERE h.im_date >= ".db_escape($from_date)." 
                AND h.im_date <= ".db_escape($to_date);
    
    if($movement_type_id > 0){
        $sql .= " AND mr.movement_type = ".db_escape($movement_type_id);
    }
  
    
    return  $sql;
    
}


function dashboard_get_sql_for_inventory_mr_return_movement_types($from_date, $to_date, $movement_type_id=0) {
    
    $sql = "SELECT ".db_escape(ST_MATERIAL_ISSUE)." trans_type, h.rm_no trans_no, h.rm_date tran_date, h.project_id, mr.movement_type movement_type_id, (d.qty_returned * d.unit_cost) cost, m.name  movement_type_name 
            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     
                JOIN ".TB_PREF."movement_types m    
                    ON m.id = mr.movement_type  
            WHERE h.rm_date >= ".db_escape($from_date)." 
                AND h.rm_date <= ".db_escape($to_date);
    
    if($movement_type_id > 0){
        $sql .= " AND mr.movement_type = ".db_escape($movement_type_id);
    }
  
    
    return  $sql;
    
}


function dashboard_get_inventory_movement_types($from_date, $to_date) {
    
    $sql_daily_item_issues = dashboard_get_sql_for_inventory_daily_item_issues_movement_types($from_date, $to_date);
    $sql_pr_issues = dashboard_get_sql_for_inventory_pr_issue_movement_types($from_date, $to_date);
    $sql_pr_returns = dashboard_get_sql_for_inventory_pr_return_movement_types($from_date, $to_date);
    $sql_mr_issues = dashboard_get_sql_for_inventory_mr_issue_movement_types($from_date, $to_date);
    $sql_mr_returns = dashboard_get_sql_for_inventory_mr_return_movement_types($from_date, $to_date);

    
    $sql = $sql_daily_item_issues 
        ." UNION ".$sql_pr_issues 
        ." UNION ".$sql_pr_returns 
        ." UNION ".$sql_mr_issues 
        ." UNION ".$sql_mr_returns  
            ;   
    
    $sql = "SELECT trans_type, movement_type_id, movement_type_name, SUM(cost) grand_total  
            FROM ($sql) tmp_table 
            GROUP BY trans_type, movement_type_id  
            ORDER BY grand_total DESC";
    
    return db_query($sql); 
                
}

function dashboard_get_period_movement_type_total($trans_type, $move_type_id, $from_date, $to_date) {
    
    switch ($trans_type) {
        case ST_DAILY_ITEM_ISSUE:
        case ST_DAILY_ITEM_RETURN:
            $sql = dashboard_get_sql_for_inventory_daily_item_issues_movement_types($from_date, $to_date, $move_type_id);
            break;
        case ST_PR_ISSUE:
            $sql = dashboard_get_sql_for_inventory_pr_issue_movement_types($from_date, $to_date, $move_type_id);
            break;
        case ST_PR_RETURN:
            $sql = dashboard_get_sql_for_inventory_pr_return_movement_types($from_date, $to_date, $move_type_id);
            break;
        case ST_MATERIAL_ISSUE:
            $sql = dashboard_get_sql_for_inventory_mr_issue_movement_types($from_date, $to_date, $move_type_id);
            break;
        case ST_MATERIAL_RETURN:
            $sql = dashboard_get_sql_for_inventory_mr_return_movement_types($from_date, $to_date, $move_type_id);
            break;

        default:
            $sql = "";
            break;
    }

    
    $sql = "SELECT SUM(cost) total_cost  FROM ($sql) tmp_table ";

    $result = db_query($sql, "dashboard_get_period_movement_type_total"); 
    
    return db_result_value($result, "total_cost");
                
}
