//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); }