private static int fnNewSuggestedOrderItemID() { using (var db = new MvcPhoenix.EF.CMCSQL03Entities()) { var newrec = new EF.tblSuggestedBulk(); db.tblSuggestedBulk.Add(newrec); db.SaveChanges(); return newrec.id; } }
public static int fnGenerateSuggestedOrder(int clientid, int divisionid) { // return the number of items created int itemscount = 0; string username = HttpContext.Current.User.Identity.Name; using (var db = new CMCSQL03Entities()) { string s; string fnTempTable = "tblSuggestedBulk"; // Clear the work table of my records s = String.Format("Delete from {0} where UserName='******'", fnTempTable, username); db.Database.ExecuteSqlCommand(s); // Build a list of ProductMasters to analyze var q1 = (from pd in db.tblProductDetail join pm in db.tblProductMaster on pd.ProductMasterID equals pm.ProductMasterID where pm.ClientID == clientid select new { pm.ClientID, pm.ProductMasterID, pm.SUPPLYID, pm.ShlfLife, pm.ProductSetupDate, pd.DivisionID, }).ToList(); // Restrict list to a PD.DivisionID if user requested if (divisionid > 0) { q1 = (from t in q1 where t.DivisionID == divisionid select t).ToList(); } // Insert new records into the work table foreach (var row in q1) { var newrec = new EF.tblSuggestedBulk(); newrec.ClientID = row.ClientID; newrec.UserName = username; newrec.ProductMasterID = row.ProductMasterID; newrec.SupplyID = row.SUPPLYID; newrec.ShelfLife = row.ShlfLife; newrec.ProductSetupDate = row.ProductSetupDate; newrec.DivisionID = row.DivisionID; newrec.BulkCurrentAvailable = 0; newrec.ShelfCurrentAvailable = 0; newrec.BulkShippedPastYear = 0; newrec.BulkShippedPerDay = 0; newrec.ShelfShippedPastYear = 0; newrec.ShelfShippedPerDay = 0; newrec.UseThisExpirationDate = null; newrec.AverageLeadTime = 0; newrec.ReorderThis = false; newrec.ReorderWeight = 0; newrec.BulkOnOrder = false; newrec.ProductMasterAge = (System.DateTime.Now.Date - row.ProductSetupDate.Value).Days; // get the bulk containers and setup critieria string[] sBulkStatus = { "QC", "TEST", "WASTE" }; var qBulk = (from t in db.tblBulk where t.ProductMasterID == row.ProductMasterID select t).ToList(); newrec.BulkCurrentAvailable = (from t in qBulk where !sBulkStatus.Contains(t.BulkStatus) select ((t.Qty == null ? 1 : t.Qty) * t.CurrentWeight)).Sum(); string[] sStockStatus = { "QC", "TEST", "WASTE" }; var qStock = (from t in db.tblStock join sm in db.tblShelfMaster on t.ShelfID equals sm.ShelfID join pd in db.tblProductDetail on sm.ProductDetailID equals pd.ProductDetailID join pm in db.tblProductMaster on pd.ProductMasterID equals pm.ProductMasterID where pm.ProductMasterID == row.ProductMasterID select new { t.ShelfStatus, t.QtyOnHand, sm.UnitWeight }).ToList(); newrec.ShelfCurrentAvailable = (from t in qStock where !sStockStatus.Contains(t.ShelfStatus) select (t.QtyOnHand * t.UnitWeight)).Sum(); newrec.CurrentAvailable = newrec.BulkCurrentAvailable + newrec.ShelfCurrentAvailable; // Set BulkShippedPastYear=(Select sum(LogQty*LogAmount) from vwBulkTransForReplenishment where logdate>DateAdd(day,-365,getdate()) and ProductMasterID={0}.ProductMasterID) // may not be working because missing BulkIDs in log var qBulkLog = (from t in db.tblInvLog join bl in db.tblBulk on t.BulkID equals bl.BulkID where (bl.ProductMasterID == row.ProductMasterID) && (t.LogType == "BS-SHP") select new { t.LogType, t.LogDate, t.LogQty, t.LogAmount, bl.BulkID, bl.BulkStatus, }).ToList(); qBulkLog = (from t in qBulkLog where !sBulkStatus.Contains(t.BulkStatus) select t).ToList(); qBulkLog = (from t in qBulkLog where t.LogDate > DateTime.Now.AddDays(-365) select t).ToList(); newrec.BulkShippedPastYear = (from t in qBulkLog select (t.LogQty * t.LogAmount)).Sum(); if (newrec.BulkShippedPastYear > 0 && newrec.ProductMasterAge > 365) { newrec.BulkShippedPerDay = newrec.BulkShippedPastYear / 365; } if (newrec.BulkShippedPastYear > 0 && newrec.ProductMasterAge <= 365) { newrec.BulkShippedPerDay = newrec.BulkShippedPastYear / newrec.ProductMasterAge; } var qShelfLog = (from t in db.tblInvLog join st in db.tblStock on t.StockID equals st.StockID join sm in db.tblShelfMaster on st.ShelfID equals sm.ShelfID join pd in db.tblProductDetail on sm.ProductDetailID equals pd.ProductDetailID where (pd.ProductMasterID == row.ProductMasterID) && (t.LogType == "SS-SHP") select new { t.LogType, t.LogDate, t.LogQty, t.LogAmount, t.Status }).ToList(); qShelfLog = (from t in qShelfLog where !sStockStatus.Contains(t.Status) select t).ToList(); qShelfLog = (from t in qShelfLog where t.LogDate > DateTime.Now.AddDays(-365) select t).ToList(); newrec.ShelfShippedPastYear = (from t in qShelfLog select (t.LogQty * t.LogAmount)).Sum(); if (newrec.ShelfShippedPastYear > 0 && newrec.ProductMasterAge > 365) { newrec.ShelfShippedPerDay = newrec.ShelfShippedPastYear / 365; } if (newrec.ShelfShippedPastYear > 0 && newrec.ProductMasterAge <= 365) { newrec.ShelfShippedPerDay = newrec.ProductMasterAge / 365; } // Set BulkLatestExpirationDate=(Select top 1 ExpirationDate from tblBulk where ProductMasterID={0}.ProductMasterID order by ExpirationDate Desc) // assumes always at least one bulk container var qBulkExpiration = (from t in db.tblBulk where t.ProductMasterID == row.ProductMasterID orderby t.CeaseShipDate descending select t).Take(1).FirstOrDefault(); // set initial value to null, then try to update it, if cannot update set to a future date (below) if (qBulkExpiration != null && qBulkExpiration.CeaseShipDate != null) { newrec.BulkLatestExpirationDate = qBulkExpiration.CeaseShipDate; newrec.BulkDaysTilExpiration = (newrec.BulkLatestExpirationDate.Value - System.DateTime.Now.Date).Days; newrec.ShelfLatestExpirationDate = qBulkExpiration.CeaseShipDate; newrec.ShelfDaysTilExpiration = (newrec.ShelfLatestExpirationDate.Value - System.DateTime.Now.Date).Days; newrec.UseThisExpirationDate = newrec.BulkLatestExpirationDate; } if (newrec.UseThisExpirationDate == null) { newrec.UseThisExpirationDate = new DateTime(2099, 01, 01, 0, 0, 0); } newrec.UseThisDaysTilExpiration = (newrec.UseThisExpirationDate.Value - System.DateTime.Now.Date).Days; if (newrec.UseThisDaysTilExpiration > 998) { newrec.UseThisDaysTilExpiration = 999; } // Set DaysSupplyLeft= CurrentAvailable / (ShelfShippedPerDay+BulkShippedPerDay) Where (ShelfShippedPerDay+BulkShippedPerDay>0) if ((newrec.ShelfShippedPerDay + newrec.BulkShippedPerDay) > 0) { newrec.DaysSupplyLeft = Convert.ToInt32(newrec.CurrentAvailable / (newrec.ShelfShippedPerDay + newrec.BulkShippedPerDay)); } // Set DaysSupplyLeft=0 Where DaysSupplyLeft Is Null if (newrec.DaysSupplyLeft == null) { newrec.DaysSupplyLeft = 0; } var qBulkOrders = (from t in db.tblBulkOrderItem where t.ProductMasterID == row.ProductMasterID && (t.Status == "OP" || t.Status == "OPEN") select t.BulkOrderItemID).ToList(); if (qBulkOrders.Count() > 0) { newrec.BulkOnOrder = true; } else { newrec.BulkOnOrder = false; } if (newrec.DaysSupplyLeft < 65) { newrec.ReorderThis = true; } if (newrec.UseThisDaysTilExpiration < 65) { newrec.ReorderThis = true; } if (newrec.ShelfShippedPerDay + newrec.BulkShippedPerDay == 0) { newrec.ReorderThis = false; } if (newrec.BulkOnOrder == true) { newrec.ReorderThis = false; } // Set ReorderWeight=Round((ShelfShippedPerDay+BulkShippedPerDay) * 120,0) Where ShelfLife<13 and ReorderThis=1 if (newrec.ShelfLife < 13 && newrec.ReorderThis == true) { newrec.ReorderWeight = Convert.ToInt32((newrec.ShelfShippedPerDay + newrec.BulkShippedPerDay) * 120); } // Set ReorderWeight=Round((ShelfShippedPerDay+BulkShippedPerDay) * 180,0) Where (ShelfLife>=13 or ShelfLife is null) and (ReorderThis=1) if (newrec.ShelfLife >= 13 || newrec.ShelfLife == null) { newrec.ReorderWeight = Convert.ToInt32((newrec.ShelfShippedPerDay + newrec.BulkShippedPerDay)) * 180; } // Set ReorderWeight=1 Where (ReorderWeight<1) and (ReorderThis=1) if (newrec.ReorderWeight < 1 && newrec.ReorderThis == true) { newrec.ReorderWeight = 1; } db.tblSuggestedBulk.Add(newrec); db.SaveChanges(); } s = String.Format("Delete from {0} where ReorderThis=0 and Username='******'", fnTempTable, username); db.Database.ExecuteSqlCommand(s); itemscount = (from t in db.tblSuggestedBulk where t.UserName == username select t).Count(); return itemscount; } }