internal IDictionary <string, IEnumerable <InventoryArea> > GetGroupedAreas(InventoryAreaFilters filters)
{
string groupText;
string emptyGroupText;
if (filters.HasFlag(InventoryAreaFilters.GroupByPalletRequirement))
{
groupText = "Pallet Areas";
emptyGroupText = "Non Pallet Areas";
}
else if (filters.HasFlag(InventoryAreaFilters.GroupByUsability))
{
groupText = "Unusable Areas";
emptyGroupText = "Usable Areas";
}
else
{
groupText = "";
emptyGroupText = "";
}
var areas = _repos.GetInventoryAreas(filters);
var dict = (from area in areas
group area by area.GroupingColumn into g
select new
{
Group = string.IsNullOrEmpty(g.Key) ? emptyGroupText : groupText,
Items = g.AsEnumerable()
}).ToDictionary(p => p.Group, q => q.Items);
return(dict);
}
/// <summary>
/// Sharad 19 Dec 2011: Using tia.location_numbering_flag to determine whether location is numbered.
/// Earlier we were checking for existence of locations in master_storage_location.
/// For cencelled area, we were getting area from Ia table.
/// </summary>
/// <param name="filters"></param>
/// <returns></returns>
internal IList <InventoryArea> GetInventoryAreas(InventoryAreaFilters filters)
{
const string QUERY = @"
SELECT inventory_storage_area AS Inventory_Storage_Area,
short_name AS short_name,
description AS description,
<if c='$GroupBy = ""P""'>tia.is_pallet_required</if>
<else c='$GroupBy = ""U""'>tia.unusable_inventory</else>
<else>NULL</else>
AS grouping_column,
NULL AS CAN_AREA
FROM <proxy />tab_inventory_area tia
WHERE 1 = 1
<if c='not($IncludeConsolidatedUpcAreas)'>
AND tia.consolidated_upc_code IS NULL
</if>
<if c='$RepackForStorage'>
And tia.is_repack_area='Y'
</if>
<if>AND tia.stores_what = :stores_what</if>
<if c='$stores_what=""CTN""'>AND (tia.track_bundle_flag != 'M' OR tia.track_bundle_flag IS NULL)</if>
<if c='$UsableAreas'>AND tia.unusable_inventory IS NULL</if>
<if c='$UnusableAreas'>AND tia.unusable_inventory IS NOT NULL</if>
<if c='$NoPallet'>AND tia.is_pallet_required IS NOT NULL</if>
<if c='$Unnumbered'>AND tia.location_numbering_flag is null</if>
<if c='$Conversion=""Y""'>AND tia.is_conversion_area is not null</if>
<else c='$Conversion=""N""'>AND tia.is_conversion_area is null</else>
<if c='$ReceivingArea=""Y""'>AND tia.is_receiving_area is not null</if>
<else c='$ReceivingArea=""N""'>AND tia.is_receiving_area is null</else>
<if c='$CancelledArea'>
UNION ALL
SELECT T.IA_ID AS INVENTORY_STORAGE_AREA,
T.IA_ID AS SHORT_NAME,
T.SHORT_DESCRIPTION AS DESCRIPTION,
NULL AS GROUPING_COLUMN,
'Y' AS CAN_AREA
FROM <proxy />IA T
WHERE T.IA_ID IN (SELECT DISTINCT (IA_ID)
FROM <proxy />IALOC_CONTENT)
AND T.DEFAULT_IA_LOCATION IS NOT NULL
</if>
ORDER BY GROUPING_COLUMN DESC, INVENTORY_STORAGE_AREA
";
Contract.Assert(_db != null);
var binder = SqlBinder.Create(row => new InventoryArea
{
IaId = row.GetString("Inventory_Storage_Area"),
ShortName = row.GetString("short_name"),
Description = row.GetString("description"),
GroupingColumn = row.GetString("grouping_column"),
IsCancelArea = !string.IsNullOrEmpty(row.GetString("CAN_AREA"))
});
if (filters.HasFlag(InventoryAreaFilters.StoresSku))
{
binder.Parameter("stores_what", "SKU");
}
// For destination areas pass CTN
else if (filters.HasFlag(InventoryAreaFilters.StoresCarton))
{
binder.Parameter("stores_what", "CTN");
}
else
{
binder.Parameter("stores_what", "");
}
if (filters.HasFlag(InventoryAreaFilters.Usable))
{
binder.Parameter("UsableAreas", "1");
}
else
{
binder.Parameter("UsableAreas", "");
}
if (filters.HasFlag(InventoryAreaFilters.Unusable))
{
binder.Parameter("UnusableAreas", "1");
}
else
{
binder.Parameter("UnusableAreas", "");
}
if (filters.HasFlag(InventoryAreaFilters.Unnumbered))
{
binder.Parameter("Unnumbered", "1");
}
else
{
binder.Parameter("Unnumbered", "");
}
if (filters.HasFlag(InventoryAreaFilters.NoPallet))
{
binder.Parameter("NoPallet", "1");
}
else
{
binder.Parameter("NoPallet", "");
}
if (filters.HasFlag(InventoryAreaFilters.GroupByPalletRequirement))
{
binder.Parameter("GroupBy", "P");
}
else if (filters.HasFlag(InventoryAreaFilters.GroupByUsability))
{
binder.Parameter("GroupBy", "U");
}
else
{
binder.Parameter("GroupBy", "");
}
if (filters.HasFlag(InventoryAreaFilters.RepackForStorage))
{
binder.Parameter("RepackForStorage", "S");
}
else
{
binder.Parameter("RepackForStorage", "");
}
if (filters.HasFlag(InventoryAreaFilters.IncludeConsolidatedUpcAreas))
{
binder.Parameter("IncludeConsolidatedUpcAreas", "Y");
}
else
{
binder.Parameter("IncludeConsolidatedUpcAreas", "");
}
//For cancelled area.
if (filters.HasFlag(InventoryAreaFilters.CancelledArea))
{
binder.Parameter("CancelledArea", "CAN");
}
else
{
binder.Parameter("CancelledArea", "");
}
//for conversion area
if (filters.HasFlag(InventoryAreaFilters.ConversionAreasOnly))
{
binder.Parameter("Conversion", "Y");
}
else if (filters.HasFlag(InventoryAreaFilters.ExcludeConversionAreas))
{
binder.Parameter("Conversion", "N");
}
else
{
binder.Parameter("Conversion", "");
}
if (filters.HasFlag(InventoryAreaFilters.ReceivingAreas))
{
binder.Parameter("ReceivingArea", "Y");
}
else if (filters.HasFlag(InventoryAreaFilters.ExcludeReceivingAreas))
{
binder.Parameter("ReceivingArea", "N");
}
else
{
binder.Parameter("ReceivingArea", "");
}
var result = _db.ExecuteReader(QUERY, binder);
return(result);
}