<?php

function get_items_for_item_transaction_summary($from_date, $to_date, $loc_code='') {
    
    $sql = "SELECT DISTINCT sv.stock_id, sm.description, iu.name unit_name   
            FROM ".TB_PREF."stock_moves sv  
                JOIN ".TB_PREF."stock_master sm 
                    ON sm.stock_id = sv.stock_id 
                JOIN ".TB_PREF."item_units iu  
                    ON iu.abbr = sm.units  
            WHERE sv.tran_date >= ".db_escape($from_date)." 
                AND sv.tran_date <= ".db_escape($to_date);
    
    if(strlen($loc_code) > 1){
        $sql .= " AND sv.loc_code = ".db_escape($loc_code);
    }

    $sql .= " ORDER BY sm.stock_id ";
    
    return db_query($sql, "get_items_for_item_transaction_summary"); 
    
}
function get_type_item_transaction_summary($from_date, $to_date, $stock_id, $type, $loc_code='') {
    
    $sql = "SELECT SUM(qty) total_qty    
            FROM ".TB_PREF."stock_moves    
            WHERE tran_date >= ".db_escape($from_date)." 
                AND tran_date <= ".db_escape($to_date)." 
                AND stock_id = ".db_escape($stock_id)." 
                AND type = ".db_escape($type);
    
    if(strlen($loc_code) > 1){
        $sql .= " AND loc_code = ".db_escape($loc_code);
    }
    
    $result = db_query($sql, "get_items_for_item_transaction_summary"); 
    
    return db_result_value($result, "total_qty");
    
}


function get_sql_for_items_for_item_transaction_summary($from_date, $to_date, $loc_code='') {
    
    $sql = "SELECT sv.stock_id, sm.description, iu.name unit_name, SUM(sv.qty) total_qty    
            FROM ".TB_PREF."stock_moves sv  
                JOIN ".TB_PREF."stock_master sm 
                    ON sm.stock_id = sv.stock_id 
                JOIN ".TB_PREF."item_units iu  
                    ON iu.abbr = sm.units  
            WHERE sv.tran_date >= ".db_escape($from_date)." 
                AND sv.tran_date <= ".db_escape($to_date);
    
    if(strlen($loc_code) > 1){
        $sql .= " AND sv.loc_code = ".db_escape($loc_code);
    }

    $sql .= " GROUP BY sv.stock_id ORDER BY total_qty, sm.stock_id ";
    
    return $sql;
    
//    return db_query($sql, "get_items_for_item_transaction_summary"); 
    
}