<?php

function dashboard_get_sql_for_inventory_issues_receipts_items($from_date, $to_date, $project_id=-1, $item_code='', $linked_field_type_id=0, $linked_field_id=-1) {
    
    $sql = "SELECT d.trans_id rec_id, h.type trans_type, h.id trans_no, h.ai_date tran_date, h.project_id, h.movement_type movement_type_id, 
                (d.qty*-1) qty, d.standard_cost unit_cost, d.stock_id item_code, h.linked_field_id, 
                '' item_trans_description, d.tran_qty, d.tran_unit, d.convertion_factor conversion_factor    
            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);
    
    if($project_id > 0){
        $sql .= " AND h.project_id = ".db_escape($project_id);
    }
    
    if($linked_field_type_id > 0){
        $sql .= " AND h.linked_field_type = ".db_escape($linked_field_type_id);   
    }
    
    if($linked_field_id > 0){
        $sql .= " AND h.linked_field_id = ".db_escape($linked_field_id);
    }
    
    if(strlen($item_code) > 0){
        $sql .= " AND d.stock_id = ".db_escape($item_code);
    }
  
    
    return  $sql;
    
}

function dashboard_get_sql_for_inventory_daily_item_issues_items($from_date, $to_date, $project_id=-1, $item_code='', $linked_field_type_id=0, $linked_field_id=-1) {
    
    $sql = "SELECT d.trans_id rec_id, h.type trans_type, h.trans_no, h.tran_date, h.project_id, h.movement_type movement_type_id, 
                d.qty, d.standard_cost unit_cost, d.stock_id item_code, d.linked_field_id, 
                d.item_description item_trans_description, d.tran_qty, d.tran_unit, d.conversion_factor     
            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);
    
    if($project_id > 0){
        $sql .= " AND h.project_id = ".db_escape($project_id);
    }
    
    if($linked_field_type_id > 0){
        $sql .= " AND h.linked_field_type = ".db_escape($linked_field_type_id);
    }
    
    if($linked_field_id > 0){
        $sql .= " AND d.linked_field_id = ".db_escape($linked_field_id);
    }
    
    if(strlen($item_code) > 0){
        $sql .= " AND d.stock_id = ".db_escape($item_code);
    }
  
    
    return  $sql;
    
}

function dashboard_get_sql_for_inventory_pr_issue_items($from_date, $to_date, $project_id=-1, $item_code='',  $linked_field_type_id=0, $linked_field_id=-1) {
    
    $sql = "SELECT d.id rec_id, ".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 qty, d.unit_cost, d.item_code, h.linked_field_id, 
                d.description item_trans_description, d.tran_qty, d.tran_unit, d.conversion_factor          
            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);
    
    if($project_id > 0){
        $sql .= " AND h.project_id = ".db_escape($project_id);
    }
    
    if($linked_field_type_id > 0){
        $sql .= " AND h.linked_field_type = ".db_escape($linked_field_type_id);
    }
    
    if($linked_field_id > 0){
        $sql .= " AND h.linked_field_id = ".db_escape($linked_field_id);
    }
    
    if(strlen($item_code) > 0){
        $sql .= " AND d.item_code = ".db_escape($item_code);
    }
  
    
    return  $sql;
    
}

function dashboard_get_sql_for_inventory_pr_return_items($from_date, $to_date, $project_id=-1, $item_code='',  $linked_field_type_id=0, $linked_field_id=-1) {
    
    $sql = "SELECT d.id rec_id, ".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 qty, d.unit_cost, d.item_code, i.linked_field_id, 
                d.description item_trans_description, d.tran_qty, d.tran_unit, d.conversion_factor      
            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);
    
    if($project_id > 0){
        $sql .= " AND h.project_id = ".db_escape($project_id);
    }
    
    if($linked_field_type_id > 0){
        $sql .= " AND i.linked_field_type = ".db_escape($linked_field_type_id);
    }
    
    if($linked_field_id > 0){
        $sql .= " AND i.linked_field_id = ".db_escape($linked_field_id);
    }
    
    if(strlen($item_code) > 0){
        $sql .= " AND d.item_code = ".db_escape($item_code);
    }
  
    
    return  $sql;
    
}


function dashboard_get_sql_for_inventory_mr_issue_items($from_date, $to_date, $project_id=-1, $item_code='',  $linked_field_type_id=0, $linked_field_id=-1) {
    
    $sql = "SELECT d.id rec_id, ".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 qty, d.unit_cost,  d.item_code, mr.linked_field_id, 
                d.item_description item_trans_description, d.tran_qty, d.tran_unit, d.conversion_factor       
            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($project_id > 0){
        $sql .= " AND h.project_id = ".db_escape($project_id);
    }
    
    if($linked_field_type_id > 0){
        $sql .= " AND mr.linked_field_type = ".db_escape($linked_field_type_id);
    }
    
    if($linked_field_id > 0){
        $sql .= " AND mr.linked_field_id = ".db_escape($linked_field_id);
    }
    
    if(strlen($item_code) > 0){
        $sql .= " AND d.item_code = ".db_escape($item_code);
    }
  
    
    return  $sql;
    
}


function dashboard_get_sql_for_inventory_mr_return_items($from_date, $to_date, $project_id=-1, $item_code='',  $linked_field_type_id=0, $linked_field_id=-1) {
    
    $sql = "SELECT d.id rec_id, ".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 qty, d.unit_cost, d.item_code, mr.linked_field_id, 
                d.item_description item_trans_description, d.tran_qty, d.tran_unit, d.conversion_factor   
            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);
    
    if($project_id > 0){
        $sql .= " AND h.project_id = ".db_escape($project_id);
    }
    
    if($linked_field_type_id > 0){
        $sql .= " AND mr.linked_field_type = ".db_escape($linked_field_type_id);
    }
    
    if($linked_field_id > 0){
        $sql .= " AND mr.linked_field_id = ".db_escape($linked_field_id);
    }
    
    if(strlen($item_code) > 0){
        $sql .= " AND d.item_code = ".db_escape($item_code);
    }
  
    
    return  $sql;
    
}

function dashboard_get_sql_for_inventory_petty_cash_item_returns($from_date, $to_date, $project_id=-1, $item_code='',  $linked_field_type_id=0, $linked_field_id=-1) {
    
    $sql = "SELECT d.id rec_id, d.type trans_type, d.trans_no, d.tran_date, d.project_id, 0 movement_type_id, 
                -d.qty qty, (d.tran_unit_cost / d.conversion_factor) unit_cost, d.stock_id item_code, 0 linked_field_id, 
                d.item_description item_trans_description, d.tran_qty, d.tran_unit, d.conversion_factor   
            FROM ".TB_PREF."petty_cash_stock_item_receipt_details d   
            WHERE d.tran_date >= ".db_escape($from_date)." 
                AND d.tran_date <= ".db_escape($to_date);
    
    if($project_id > 0){
        $sql .= " AND d.project_id = ".db_escape($project_id);
    }
    
    if($linked_field_type_id > 0){
//        $sql .= " AND mr.linked_field_type = ".db_escape($linked_field_type_id);
    }
    
    if($linked_field_id > 0){
//        $sql .= " AND mr.linked_field_id = ".db_escape($linked_field_id);
    }
    
    if(strlen($item_code) > 0){
        $sql .= " AND d.stock_id = ".db_escape($item_code);
    }
  
    
    return  $sql;
    
}


function dashboard_get_sql_for_inventory_item_consuption_items($from_date, $to_date, $project_id=-1, $item_code='',  $linked_field_type=0, $linked_field_id=-1) {
    
    $sql_issues_receipts = dashboard_get_sql_for_inventory_issues_receipts_items($from_date, $to_date, $project_id, $item_code, $linked_field_type, $linked_field_id);
    $sql_daily_item_issues = dashboard_get_sql_for_inventory_daily_item_issues_items($from_date, $to_date, $project_id, $item_code, $linked_field_type, $linked_field_id);
    $sql_pr_issues = dashboard_get_sql_for_inventory_pr_issue_items($from_date, $to_date, $project_id, $item_code, $linked_field_type, $linked_field_id);
    $sql_pr_returns = dashboard_get_sql_for_inventory_pr_return_items($from_date, $to_date, $project_id, $item_code, $linked_field_type, $linked_field_id);
    $sql_mr_issues = dashboard_get_sql_for_inventory_mr_issue_items($from_date, $to_date, $project_id, $item_code, $linked_field_type, $linked_field_id);
    $sql_mr_returns = dashboard_get_sql_for_inventory_mr_return_items($from_date, $to_date, $project_id, $item_code, $linked_field_type, $linked_field_id);
    $sql_pc_item_receipts = dashboard_get_sql_for_inventory_petty_cash_item_returns($from_date, $to_date, $project_id, $item_code, $linked_field_type, $linked_field_id);

    
    $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  
        ." UNION ".$sql_pc_item_receipts  
    ;   
    
//    return $sql_pc_item_receipts;
    
    return $sql;
}
    
function dashboard_get_inventory_item_consuption_items_value($from_date, $to_date) {
    
    $sql = dashboard_get_sql_for_inventory_item_consuption_items($from_date, $to_date);
    
    $sql = "SELECT item_code, SUM(qty * unit_cost) grand_total,
                    sm.description item_description, 
                    u.name unit_name 
            FROM ($sql) tmp  
                JOIN ".TB_PREF."stock_master sm  
                    ON sm.stock_id = tmp.item_code  
                JOIN ".TB_PREF."item_units u 
                    ON u.abbr = sm.units 
            GROUP BY item_code   
            ORDER BY grand_total DESC";
    
    return db_query($sql); 
                
}
    
function dashboard_get_inventory_item_consuption_items_qty($from_date, $to_date) {
    
    $sql = dashboard_get_sql_for_inventory_item_consuption_items($from_date, $to_date);
    
    $sql = "SELECT item_code, SUM(qty) grand_total,
                    sm.description item_description, 
                    u.name unit_name 
            FROM ($sql) tmp  
                JOIN ".TB_PREF."stock_master sm  
                    ON sm.stock_id = tmp.item_code  
                JOIN ".TB_PREF."item_units u 
                    ON u.abbr = sm.units 
            GROUP BY item_code   
            ORDER BY grand_total DESC";
    
    return db_query($sql); 
                
}

function dashboard_get_period_inventory_item_consuption_total($item_code, $from_date, $to_date) {
    
    $sql = dashboard_get_sql_for_inventory_item_consuption_items($from_date, $to_date, -1, $item_code);
    
    $sql = "SELECT SUM(qty * unit_cost) total_cost  FROM ($sql) tmp_table ";

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


function dashboard_get_period_inventory_item_consuption_qty($item_code, $from_date, $to_date) {
    
    $sql = dashboard_get_sql_for_inventory_item_consuption_items($from_date, $to_date, -1, $item_code);
    
    $sql = "SELECT SUM(qty) total_qty  FROM ($sql) tmp_table ";

    $result = db_query($sql, "dashboard_get_period_inventory_item_consuption_total"); 
    
    return db_result_value($result, "total_qty");
    
}

