//Get replenishment table
public static List <ModelZoneMap> GetAllModelsQty()
{
var updateModelZone = GetDailyReportByModelsAndLocation();
//Updated which zone 1 location for different model
UpdateInventory(updateModelZone);
var query = from model in db.ModelZoneMaps
orderby model.FG ascending
select model;
var result = new List <ModelZoneMap>();
foreach (ModelZoneMap i in query)
{
if (!DailyModelHelper.isShowModel(i.Model) || i.Zone2Code.Equals("111"))
{
continue;
}
//Query Zone 2 Quantity
var zone2 = (from inventory in db.InventoryIns
where inventory.Location.Equals(i.Zone2Code) && inventory.ModelNo.Equals(i.Model)
select inventory).ToList();
if (zone2.Count < i.Z2MinQty || (zone2.Count() == 0 && DailyModelHelper.isShowModel(i.Model)))
{
//Query Zone 1 Quantity
var zone1 = (from inventory in db.InventoryIns
where inventory.Location.Equals(i.Zone1Code) && inventory.ModelNo.Equals(i.Model)
select inventory).ToList();
if (zone1.Count <= 0)
{
i.Zone1Code = "N/A";
}
else
{
i.Z2CurtQty = (i.Z2MaxQty - zone2.Count);
}
result.Add(i);
}
}
return(result);
}
//Get all model daily report from DB
public static List <FGDailyReportDto> GetDailyReportByModels(DateTime date)
{
var query = (from model in db.ModelZoneMaps
orderby model.Brand ascending, model.Category
select model);
var result = new List <FGDailyReportDto>();
int modelTotal = 0;
int zone1Count = 0;
int zone2Count = 0;
int zone1Received = 0;
var yesterday = date.AddDays(-1);
var tomorrow = date.AddDays(1);
var modelDailyTotal = new List <DailyTotal>();
foreach (ModelZoneMap i in query)
{
zone1Received = 0;
FGDailyReportDto m = new FGDailyReportDto();
m.ModelNo = i.Model;
m.ModelFG = i.FG;
m.Brand = i.Brand;
m.Category = i.Category;
var shipped = (from ship in db.Histories
where ship.ModelNo.Equals(i.Model) && ship.ShippedDate >= date && ship.ShippedDate < tomorrow
select ship).ToList();
var shippedA = new List <Pallet>();
if (m.ModelNo.Equals("135416"))
{
shippedA = (from ship in db.Pallets
where ship.ItemID.Equals("135416A") && ship.ShippedDate >= date && ship.ShippedDate < tomorrow
select ship).ToList();
}
var afterShipped = (from ship in db.Histories
where ship.ModelNo.Equals(i.Model) && ship.ShippedDate >= tomorrow
select ship).ToList();
m.Shipped = shipped.Count + shippedA.Count;;
int receivedShip = 0;
int afterShippingScanItem = afterShipped.Count;
foreach (var ship in afterShipped)
{
if (ship.ScanDate != null && ship.ScanDate.Date == date.Date)
{
receivedShip++;
}
else if (ship.ScanDate.Date > date.Date)
{
afterShippingScanItem--;
}
}
var receivedItemByDate = (from inventory in db.InventoryIns
where inventory.ModelNo.Equals(i.Model) && !inventory.Location.Equals(Constants.ZONE_CODE_RETURN.ToString()) && inventory.Date >= date && inventory.Date < tomorrow
select inventory
).ToList();
zone1Received = receivedShip + receivedItemByDate.Count();
int rts = 0;
int showroom = 0;
int unshippable = 0;
foreach (var inventory in receivedItemByDate)
{
if (inventory.Location.Equals(Constants.ZONE_CODE_3_A.ToString()) || inventory.Location.Equals(Constants.ZONE_CODE_3_B.ToString()) || inventory.Location.Equals(Constants.ZONE_CODE_3_B.ToString()) || inventory.Location.Equals(Constants.ZONE_CODE_3_C.ToString()))
{
rts++;
}
else if (inventory.Location.Equals(Constants.ZONE_CODE_5_ONE.ToString()))
{
unshippable++;
}
else if (inventory.Location.Equals(Constants.ZONE_CODE_4_ONE.ToString()))
{
showroom++;
}
}
var receivedCurrent = (from inventory in db.InventoryIns
where inventory.ModelNo.Equals(i.Model) && inventory.Date < date
join code in db.Locations on inventory.Location equals code.Code
orderby code.ZoneCode ascending, inventory.SN.Substring(6, 10) ascending
group inventory by code.ZoneCode);
modelTotal = 0;
zone1Count = 0;
zone2Count = 0;
int count = receivedCurrent.Count();
foreach (var inventory in receivedCurrent)
{
Console.WriteLine(inventory.Count());
//mapped all values
if (inventory.Key == 1)
{
zone1Count = inventory.Count();
}
else if (inventory.Key == 2)
{
zone2Count = inventory.Count();
}
else if (inventory.Key == 3)
{
m.ReturnItem = inventory.Count();
}
else if (inventory.Key == 4)
{
m.ShowRoom = inventory.Count();
}
else if (inventory.Key == 5)
{
m.Rework = inventory.Count();
}
else if (inventory.Key == 6)
{
m.QC = inventory.Count();
}
else if (inventory.Key == 7)
{
m.Scrapped = inventory.Count();
}
else if (inventory.Key == 9)
{
continue;
}
modelTotal += inventory.Count();
}
if (showroom != 0)
{
m.ShowRoom += showroom;
}
if (unshippable != 0)
{
m.Rework += unshippable;
}
int afterSippedCnt = afterShippingScanItem - receivedShip;
m.Received = zone1Received;
m.OnHand = m.Received + zone1Count + zone2Count + afterSippedCnt - showroom - unshippable;
m.Total = modelTotal + m.Received + afterSippedCnt;
m.Previous = m.Total + m.Shipped - m.Received;
if (m.Total > 0 || DailyModelHelper.isShowModel(m.ModelNo))
{
result.Add(m);
DailyTotal dailyTotal = new DailyTotal();
dailyTotal.ModelNo = m.ModelNo;
dailyTotal.Date = date;
dailyTotal.Total = modelTotal;
modelDailyTotal.Add(dailyTotal);
}
}
List <FGDailyReportDto> SortedList = result.OrderBy(o => o.Brand).ToList();
ModelDailyTotal.InsertInventory(modelDailyTotal);
return(SortedList);
}