public static string fnBuildDivisionDropDown(int id) { using (var db = new CMCSQL03Entities()) { var qry = (from t in db.tblDivision where t.ClientID == id orderby t.Division, t.BusinessUnit select t); string s = "<option value='0'>All Divisions</option>"; if (qry.Count() > 0) { foreach (var item in qry) { s = s + "<option value=" + item.DivisionID.ToString() + ">" + item.Division + " - " + item.BusinessUnit + "</option>"; } } else { s = s + "<option value=0>No Divisions Found</option>"; } s = s + "</select>"; return s; } }
public static List<SelectListItem> fnCarriers() { using (var db = new CMCSQL03Entities()) { List<SelectListItem> mylist = new List<SelectListItem>(); mylist = (from t in db.tblCarrier orderby t.CarrierName select new SelectListItem { Value = t.CarrierName, Text = t.CarrierName }).ToList(); mylist.Insert(0, new SelectListItem { Value = "0", Text = "Select Carrier" }); return mylist; } }
public static List<SelectListItem> fnBulkStatusIDs() { using (var db = new CMCSQL03Entities()) { List<SelectListItem> mylist = new List<SelectListItem>(); mylist = (from t in db.tblBulk orderby t.BulkStatus select new SelectListItem { Value = t.BulkStatus, Text = t.BulkStatus }).Distinct().ToList(); mylist.Insert(0, new SelectListItem { Value = "0", Text = "Please Select" }); return mylist; } }
public static List<SelectListItem> fnClientIDs() { using (var db = new CMCSQL03Entities()) { List<SelectListItem> mylist = new List<SelectListItem>(); mylist = (from t in db.tblClient orderby t.ClientName select new SelectListItem { Value = t.ClientID.ToString(), Text = t.ClientName }).ToList(); mylist.Insert(0, new SelectListItem { Value = "0", Text = "Select Client" }); return mylist; } }
public static List<SelectListItem> fnBulkSuppliers(int id) { using (var db = new CMCSQL03Entities()) { List<SelectListItem> mylist = new List<SelectListItem>(); mylist = (from t in db.tblBulkSupplier where t.ClientID == id orderby t.SupplyID select new SelectListItem { Value = t.BulkSupplierID.ToString(), Text = t.SupplyID }).ToList(); mylist.Insert(0, new SelectListItem { Value = "0", Text = "Select Supplier" }); return mylist; } }
public static string fnBuildProductMasterDropDown(int clientid) { // This returns ONLY the <option> portion of the <select> tag, thus allowing the <select> tag to // be propering decorated with onchange= etc.. using (var db = new CMCSQL03Entities()) { var qry = (from t in db.tblProductMaster where t.ClientID == clientid orderby t.MasterCode, t.MasterName select t); string s = "<option value='0' selected=true>Select Master Code</option>"; if (qry.Count() > 0) { foreach (var item in qry) { s = s + "<option value=" + item.ProductMasterID.ToString() + ">" + item.MasterCode + " - " + item.MasterName + "</option>"; } } else { s = s + "<option value=0>No Products Found</option>"; } s = s + "</select>"; return s; } }
public static List<BulkContainerViewModel> fnBulkContainerList() { // full list using (var db = new CMCSQL03Entities()) { var mylist = (from t in db.tblBulk join pm in db.tblProductMaster on t.ProductMasterID equals pm.ProductMasterID join cl in db.tblClient on pm.ClientID equals cl.ClientID orderby cl.ClientName, pm.MasterCode select new BulkContainerViewModel { bulkid = t.BulkID, warehouse = t.Warehouse, bin = t.Bin, clientname = cl.ClientName, MasterCode = pm.MasterCode, MasterName = pm.MasterName, bulkstatus = t.BulkStatus, productmasterid = t.ProductMasterID, clientid = cl.ClientID }).ToList(); return mylist; } }
public static List<SelectListItem> fnOrderTypes() { using (var db = new CMCSQL03Entities()) { List<SelectListItem> mylist = new List<SelectListItem>(); mylist = (from t in db.tblOrderType orderby t.Description select new SelectListItem { Value = t.OrderType, Text = t.Description }).ToList(); mylist.Insert(0, new SelectListItem { Value = "0", Text = "Select Order Type" }); return mylist; } }
public static List<SelectListItem> fnHSCodes() { using (var db = new CMCSQL03Entities()) { List<SelectListItem> mylist = new List<SelectListItem>(); mylist = (from t in db.tblHSCode orderby t.HarmonizedCode select new SelectListItem { Value = t.HarmonizedCode, Text = t.HarmonizedCode }).ToList(); mylist.Insert(0, new SelectListItem { Value = "0", Text = "Select Harmonized Code" }); return mylist; } }
public static PrePackViewModel fnNewBulkContainerForPrePack(int clientid, int productdetailid) { PrePackViewModel obj = new PrePackViewModel(); using (var db = new CMCSQL03Entities()) { var dbClient = db.tblClient.Find(clientid); var dbProductDetail = db.tblProductDetail.Find(productdetailid); obj.productmasterid = dbProductDetail.ProductMasterID; obj.isknownmaterial = true; obj.clientid = clientid; obj.clientname = dbClient.ClientName; obj.logofilename = dbClient.LogoFileName; obj.bulkid = -1; // for insert later obj.receivedate = DateTime.Now; obj.carrier = null; obj.ListOfCarriers = fnCarriers(); obj.warehouse = null; obj.ListOfWareHouses = fnWarehouseIDs(); obj.enteredby = null; obj.lotnumber = null; obj.receivedby = null; obj.mfgdate = null; obj.expirationdate = null; obj.ceaseshipdate = null; obj.qcdate = null; obj.msdsincluded = null; obj.coaincluded = null; obj.productcode = dbProductDetail.ProductCode; obj.productname = dbProductDetail.ProductName; obj.ListOfShelfMasters = (from t in db.tblShelfMaster orderby t.ShelfID where t.ProductDetailID == productdetailid select new ItemForPrePackViewModel { shelfid = t.ShelfID, size = t.Size, bin = t.Bin }).ToList(); obj.ItemsCount = obj.ListOfShelfMasters.Count(); return obj; } }
public static void fnSaveStock(StockViewModel vm) { using (var db = new CMCSQL03Entities()) { var q = (from t in db.tblStock where t.StockID == vm.StockID select t).FirstOrDefault(); q.Warehouse = vm.Warehouse; q.QtyOnHand = vm.QtyOnHand; q.Bin = vm.Bin; q.ShelfStatus = vm.ShelfStatus; q.WasteAccumStartDate = vm.WasteAccumStartDate; q.UpdateDate = System.DateTime.Now; q.UpdateUser = HttpContext.Current.User.Identity.Name; db.SaveChanges(); } }
public static List<SelectListItem> fnListOfShelfStatusIDs() { using (var db = new CMCSQL03Entities()) { List<SelectListItem> mylist = new List<SelectListItem>(); mylist.Add(new SelectListItem { Text = "AVAIL", Value = "AVAIL" }); mylist.Add(new SelectListItem { Text = "TEST", Value = "TEST" }); mylist.Add(new SelectListItem { Text = "HOLD", Value = "HOLD" }); mylist.Add(new SelectListItem { Text = "QC", Value = "QC" }); mylist.Add(new SelectListItem { Text = "RETURN", Value = "RETURN" }); mylist.Insert(0, new SelectListItem { Value = "0", Text = "" }); return mylist; } }
public static StockViewModel fnFillStockViewModel(int id) { using (var db = new CMCSQL03Entities()) { var vm = (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 bk in db.tblBulk on t.BulkID equals bk.BulkID where t.StockID == id select new StockViewModel { StockID = t.StockID, ShelfID = t.ShelfID, BulkID = t.BulkID, Warehouse = t.Warehouse, QtyOnHand = t.QtyOnHand, QtyAllocated = t.QtyAllocated, Bin = t.Bin, ShelfStatus = t.ShelfStatus, WasteAccumStartDate = t.WasteAccumStartDate, CreateDate = t.CreateDate, CreateUser = t.CreateUser, ProductDetailID = pd.ProductDetailID, ProductCode = pd.ProductCode, ProductName = pd.ProductName, LotNumber = bk.LotNumber, ExpirationDate = bk.ExpirationDate, MfgDate = bk.MfgDate, QCDate = bk.QCDate, CeaseShipDate = bk.CeaseShipDate, Size = sm.Size, UnitWeight = sm.UnitWeight, UpdateDate = t.UpdateDate, UpdateUser = t.UpdateUser }).FirstOrDefault(); vm.ListOfShelfStatusIDs = Services.InventoryService.fnListOfShelfStatusIDs(); vm.ListOfShelfMasterIDs = Services.InventoryService.fnListOfShelfMasterIDs(vm.ProductDetailID); vm.ListOfBulkIDs = Services.InventoryService.fnListOfBulkIDs(vm.ShelfID); vm.ListOfWareHouseIDs = Services.InventoryService.fnListOfWarehouseIDs(); return vm; } }
public static List<SelectListItem> fnProductMasterIDs(int? id, int? PmID = null) { using (var db = new CMCSQL03Entities()) { List<SelectListItem> mylist = new List<SelectListItem>(); if (PmID == null) { mylist = (from t in db.tblProductMaster where t.ClientID == id orderby t.MasterCode, t.MasterName select new SelectListItem { Value = t.ProductMasterID.ToString(), Text = t.MasterCode + " - " + t.MasterName.Substring(0, 25) }).ToList(); mylist.Insert(0, new SelectListItem { Value = "0", Text = "Please Select" }); } else { mylist = (from t in db.tblProductMaster where t.ClientID == id && t.ProductMasterID == PmID orderby t.MasterCode, t.MasterName select new SelectListItem { Value = t.ProductMasterID.ToString(), Text = t.MasterCode + " - " + t.MasterName.Substring(0, 25) }).ToList(); } return mylist; } }
public static BulkContainerViewModel fnFillBulkContainerFromDB(int id) { // id=bulkid using (var db = new CMCSQL03Entities()) { var obj = (from t in db.tblBulk where t.BulkID == id select new BulkContainerViewModel { isknownmaterial = true, bulkid = t.BulkID, productmasterid = t.ProductMasterID, warehouse = t.Warehouse, receivedate = t.ReceiveDate, carrier = t.Carrier, receivedby = t.ReceivedBy, enteredby = t.EnteredBy, receiveweight = t.ReceiveWeight, lotnumber = t.LotNumber, mfgdate = t.MfgDate, expirationdate = t.ExpirationDate, ceaseshipdate = t.CeaseShipDate, bulkstatus = t.BulkStatus, qty = "1", um = t.UM, containercolor = t.ContainerColor, bin = t.Bin, containertype = t.ContainerType, coaincluded = t.COAIncluded, msdsincluded = t.MSDSIncluded, currentweight = t.CurrentWeight, qcdate = t.QCDate, returnlocation = t.ReturnLocation, noticedate = t.NoticeDate, bulklabelnote = t.BulkLabelNote, receivedascode = t.ReceivedAsCode, receivedasname = t.ReceivedAsName, containernotes = t.ContainerNotes, otherstorage = t.OtherStorage }).FirstOrDefault(); var qPM = (from t in db.tblProductMaster where t.ProductMasterID == obj.productmasterid select t).FirstOrDefault(); var qCL = (from t in db.tblClient where t.ClientID == qPM.ClientID select t).FirstOrDefault(); obj.clientid = qPM.ClientID; obj.clientname = qCL.ClientName; obj.logofilename = qCL.LogoFileName; obj.ListOfWareHouses = fnWarehouseIDs(); obj.ListOfBulkStatusIDs = fnBulkStatusIDs(); obj.ListOfContainerTypeIDs = ReceivingService.fnContainerTypeIDs(); obj.ListOfCarriers = ReceivingService.fnCarriers(); obj.MasterCode = qPM.MasterCode; obj.MasterName = qPM.MasterName; return obj; } }
public static List<SelectListItem> fnWarehouseIDs() { using (var db = new CMCSQL03Entities()) { List<SelectListItem> mylist = new List<SelectListItem>(); mylist.Add(new SelectListItem { Value = "CO", Text = "CO" }); mylist.Add(new SelectListItem { Value = "CT", Text = "CT" }); mylist.Add(new SelectListItem { Value = "EU", Text = "EU" }); mylist.Add(new SelectListItem { Value = "AP", Text = "AP" }); mylist.Insert(0, new SelectListItem { Value = "", Text = "All" }); return mylist; } }
public static void fnTagItemToBeClosed(int id, bool ischecked) { // update the ToBeClosed flag on the bulk order item record string s = ""; using (var db = new CMCSQL03Entities()) { // clear it always s = "Update tblBulkOrderItem set ToBeClosed=0 where BulkOrderItemID=" + id; db.Database.ExecuteSqlCommand(s); } if (ischecked == true) { using (var db = new CMCSQL03Entities()) { // tag it s = "Update tblBulkOrderItem set ToBeClosed=1 where BulkOrderItemID=" + id; db.Database.ExecuteSqlCommand(s); } } }
public static List<SelectListItem> fnReportCriterias() { using (var db = new CMCSQL03Entities()) { List<SelectListItem> mylist = new List<SelectListItem>(); mylist = (from t in db.tblReportCriteria orderby t.Display select new SelectListItem { Value = t.Display, Text = t.ReportName }).ToList(); mylist.Insert(0, new SelectListItem { Value = "0", Text = "Select Report Criteria" }); return mylist; } }
public static List<SelectListItem> fnStatusNotes() { using (var db = new CMCSQL03Entities()) { List<SelectListItem> mylist = new List<SelectListItem>(); mylist = (from t in db.tblStatusNotes orderby t.Note select new SelectListItem { Value = t.StatusNotesID.ToString(), Text = t.Note }).ToList(); mylist.Insert(0, new SelectListItem { Value = "0", Text = "Select Status Note" }); return mylist; } }
public static BulkContainerViewModel fnNewBulkContainerUnKnown() { // id=productmasterid BulkContainerViewModel obj = new BulkContainerViewModel(); using (var db = new CMCSQL03Entities()) { obj.isknownmaterial = false; obj.productmasterid = null; obj.bulkid = -1; // for insert later obj.clientid = null; obj.clientname = null; obj.logofilename = null; obj.productmasterid = null; obj.receivedate = DateTime.Now; obj.bulkstatus = "RECD"; obj.MasterCode = null; obj.MasterName = null; obj.ListOfWareHouses = fnWarehouseIDs(); obj.ListOfProductMasters = fnProductMasterIDs(obj.clientid, null); obj.ListOfBulkStatusIDs = fnBulkStatusIDs(); obj.ListOfContainerTypeIDs = fnContainerTypeIDs(); obj.ListOfCarriers = fnCarriers(); return obj; } }
public static List<SelectListItem> fnDivisionIDs(int clientid) { using (var db = new CMCSQL03Entities()) { List<SelectListItem> mylist = new List<SelectListItem>(); mylist = (from c in db.tblDivision where c.ClientID == clientid select new SelectListItem { Value = c.DivisionID.ToString(), Text = c.Division }).ToList(); mylist.Insert(0, new SelectListItem { Value = "0", Text = "Division" }); return mylist; } }
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; } }
public static List<SelectListItem> fnClientsContacts(int id, string sContactType) { using (var db = new CMCSQL03Entities()) { List<SelectListItem> mylist = new List<SelectListItem>(); mylist = (from t in db.tblClientContact where t.ClientID == id && t.ContactType == sContactType orderby t.FullName select new SelectListItem { Value = t.ClientContactID.ToString(), Text = t.FullName }).ToList(); mylist.Insert(0, new SelectListItem { Value = "0", Text = "Select Supplier" }); return mylist; } }
public static List<SelectListItem> fnProductMasterIDs(int? clientid) { // 06/13/2016 This now is a list of PD-PN records using (var db = new CMCSQL03Entities()) { List<SelectListItem> mylist = new List<SelectListItem>(); mylist = (from t in db.tblProductMaster where t.ClientID == clientid orderby t.MasterCode select new SelectListItem { Value = t.ProductMasterID.ToString(), Text = t.MasterCode + " - " + t.MasterName.Substring(0, 25) }).ToList(); mylist.Insert(0, new SelectListItem { Value = "0", Text = "Master Code" }); return mylist; } }
public static List<SelectListItem> fnListOfShelfMasterIDs(int? id) { using (var db = new CMCSQL03Entities()) { List<SelectListItem> mylist = new List<SelectListItem>(); mylist = (from t in db.tblShelfMaster join pd in db.tblProductDetail on t.ProductDetailID equals pd.ProductDetailID where t.ProductDetailID == id select new SelectListItem { Value = t.ShelfID.ToString(), Text = t.Size }).ToList(); mylist.Insert(0, new SelectListItem { Value = "", Text = "Select Size" }); return mylist; } }
private static List<SelectListItem> fnListOfSupplyIDs(int? clientid) { using (var db = new CMCSQL03Entities()) { List<SelectListItem> mylist = new List<SelectListItem>(); mylist = (from t in db.tblProductMaster where t.ClientID == clientid orderby t.SUPPLYID select new SelectListItem { Value = t.SUPPLYID, Text = t.SUPPLYID }).Distinct().ToList(); mylist.Insert(0, new SelectListItem { Value = "0", Text = "" }); return mylist; } }
public static void fnSavePrePackStock(PrePackStock vm, FormCollection fc) { using (var db = new CMCSQL03Entities()) { // for each sm, insert tblStock vm.BulkContainer.bulkid = fnNewBulkID(); var dbBulk = db.tblBulk.Find(vm.BulkContainer.bulkid); var dbPD = db.tblProductDetail.Find(vm.ProductDetailID); dbBulk.ProductMasterID = dbPD.ProductMasterID; dbBulk.Qty = 1; dbBulk.ReceiveDate = vm.BulkContainer.receivedate; dbBulk.LotNumber = vm.BulkContainer.lotnumber; dbBulk.MfgDate = vm.BulkContainer.mfgdate; dbBulk.ExpirationDate = vm.BulkContainer.expirationdate; dbBulk.CeaseShipDate = vm.BulkContainer.ceaseshipdate; dbBulk.QCDate = vm.BulkContainer.qcdate; dbBulk.BulkStatus = vm.BulkContainer.bulkstatus; dbBulk.Bin = vm.BulkContainer.bin; dbBulk.Warehouse = vm.BulkContainer.warehouse; dbBulk.CreateDate = System.DateTime.Now; dbBulk.CreateUser = System.Web.HttpContext.Current.User.Identity.Name; dbBulk.ReceiveWeight = 0; dbBulk.CurrentWeight = 0; for (int i = 1; i <= vm.ShelfMasterCount; i++) { // this will always have a value string sThisShelfID = fc["Key" + i.ToString()]; Int32 ThisShelfID = Convert.ToInt32(sThisShelfID); string sThisQty = fc["Value" + i.ToString()]; if (!String.IsNullOrEmpty(sThisQty)) { Int32 ThisQty = Convert.ToInt32(sThisQty); var newstock = new EF.tblStock(); newstock.ShelfID = ThisShelfID; newstock.BulkID = vm.BulkContainer.bulkid; newstock.Warehouse = vm.BulkContainer.warehouse; newstock.QtyOnHand = ThisQty; var sm = (from t in db.tblShelfMaster where t.ShelfID == ThisShelfID select t).FirstOrDefault(); // needed for default bin dbBulk.ReceiveWeight = dbBulk.ReceiveWeight + (ThisQty * sm.UnitWeight); newstock.Bin = sm.Bin; newstock.ShelfStatus = "AVAIL"; newstock.CreateDate = DateTime.Now; newstock.CreateUser = HttpContext.Current.User.Identity.Name; db.tblStock.Add(newstock); db.SaveChanges(); } } } }
public static List<SelectListItem> fnEndUses(int id) { using (var db = new CMCSQL03Entities()) { List<SelectListItem> mylist = new List<SelectListItem>(); mylist = (from t in db.tblEndUse where t.ClientID == id orderby t.EndUse select new SelectListItem { Value = t.EndUseID.ToString(), Text = t.EndUse }).ToList(); mylist.Insert(0, new SelectListItem { Value = "0", Text = "Select End Use" }); return mylist; } }
public static Inventory fnFillInventoryVM(int id) { using (var db = new CMCSQL03Entities()) { Inventory vm = new Inventory(); ProductProfile PP = new ProductProfile(); PP.productdetailid = id; vm.PP = ProductsService.FillFromPD(PP); PP = ProductsService.FillFromPM(PP); PP = ProductsService.fnFillOtherPMProps(PP); //vm.vmMasterNotesAlert = PP.masternotesalert; // cannot get View to properly handle this when buried in PP vm.ClientCode = (from t in db.tblClient where t.ClientID == PP.clientid select t.ClientCode).FirstOrDefault(); vm.ClientUM = (from t in db.tblClient where t.ClientID == id select t.ClientUM).FirstOrDefault(); vm.Division = (from t in db.tblDivision where t.DivisionID == PP.divisionid select t.Division).FirstOrDefault(); var q = (from t in db.tblBulkOrderItem where t.ProductMasterID == id && t.Status == "OP" select t).FirstOrDefault(); vm.BackOrderPending = q == null ? false : true; var q1 = (from t in db.tblBulkOrderItem where t.ProductMasterID == id && t.Status == "OP" select new { tot = t.Qty * t.Weight }).ToList(); var q2 = (from x in q1 select x.tot).Sum(); vm.BulkWeightCurrentlyOnOrder = Convert.ToDecimal(q2); vm.ShelfLevelTotal = StatusLevelShelf(id, "TOTAL"); vm.ShelfLevelAvail = StatusLevelShelf(id, "AVAIL"); vm.ShelfLevelTest = StatusLevelShelf(id, "TEST"); vm.ShelfLevelHold = StatusLevelShelf(id, "HOLD"); vm.ShelfLevelQC = StatusLevelShelf(id, "QC"); vm.ShelfLevelReturn = StatusLevelShelf(id, "RETURN"); vm.ShelfLevelRecd = StatusLevelShelf(id, "RECD"); vm.ShelfLevelOther = StatusLevelShelf(id, "OTHER"); vm.BulkLevelTotal = StatusLevelBulk(id, "TOTAL"); vm.BulkLevelAvail = StatusLevelBulk(id, "AVAIL"); vm.BulkLevelTest = StatusLevelBulk(id, "TEST"); vm.BulkLevelHold = StatusLevelBulk(id, "HOLD"); vm.BulkLevelQC = StatusLevelBulk(id, "QC"); vm.BulkLevelReturn = StatusLevelBulk(id, "RETURN"); vm.BulkLevelRecd = StatusLevelBulk(id, "RECD"); vm.BulkLevelOther = StatusLevelBulk(id, "OTHER"); vm.TotalLevelTotal = StatusLevelTotal(id, "TOTAL"); vm.TotalLevelAvail = StatusLevelTotal(id, "AVAIL"); vm.TotalLevelTest = StatusLevelTotal(id, "TEST"); vm.TotalLevelHold = StatusLevelTotal(id, "HOLD"); vm.TotalLevelQC = StatusLevelTotal(id, "QC"); vm.TotalLevelReturn = StatusLevelTotal(id, "RETURN"); vm.TotalLevelReturn = StatusLevelTotal(id, "RECD"); vm.TotalLevelOther = StatusLevelTotal(id, "OTHER"); return vm; } }
// ************** TODO *************************** // Finish adding all properties to next 2 // ************** TODO *************************** public static BulkContainerViewModel fnNewBulkContainer(int id) { // id=productmasterid BulkContainerViewModel obj = new BulkContainerViewModel(); using (var db = new CMCSQL03Entities()) { obj.isknownmaterial = true; var x = (from t in db.tblBulk where t.ProductMasterID == id select t).ToList(); obj.pm_sumofcurrentweight = 0; foreach (var row in x) { obj.pm_sumofcurrentweight = obj.pm_sumofcurrentweight + row.CurrentWeight; } var dbPM = db.tblProductMaster.Find(id); // assign ProductMaster fields for R/O obj.pm_MasterNotes = dbPM.MasterNotes; obj.pm_HandlingOther = dbPM.HandlingOther; obj.pm_OtherHandlingInstr = dbPM.OtherHandlingInstr; obj.pm_refrigerate = dbPM.Refrigerate; obj.pm_flammablestorageroom = dbPM.FlammableStorageRoom; obj.pm_freezablelist = dbPM.FreezableList; obj.pm_refrigeratedlist = dbPM.RefrigeratedList; //var qCL = (from t in db.tblClient where t.ClientID == qPM.ClientID select t).FirstOrDefault(); var dbClient = db.tblClient.Find(dbPM.ClientID); obj.bulkid = -1; // for insert later obj.clientid = dbClient.ClientID; obj.warehouse = dbClient.CMCLocation; obj.clientname = dbClient.ClientName; obj.logofilename = dbClient.LogoFileName; obj.productmasterid = id; obj.receivedate = DateTime.Now; obj.bulkstatus = "RECD"; obj.ListOfWareHouses = fnWarehouseIDs(); obj.ListOfProductMasters = fnProductMasterIDs(obj.clientid, id); obj.ListOfBulkStatusIDs = fnBulkStatusIDs(); obj.ListOfContainerTypeIDs = fnContainerTypeIDs(); obj.ListOfCarriers = fnCarriers(); // R/O fields from PM obj.MasterCode = dbPM.MasterCode; obj.MasterName = dbPM.MasterName; obj.flammable = dbPM.Flammable; obj.freezer = dbPM.FREEZERSTORAGE; obj.refrigerated = dbPM.Refrigerate; obj.packout = dbPM.PackOutOnReceipt; return obj; } }