public static List<SelectListItem> fnBulkStatusIDs() { using (var db = new MvcPhoenix.EF.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 = "", Text = "All" }); return mylist; } }
public static List<SelectListItem> fnClientIDs() { using (var db = new MvcPhoenix.EF.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 int fnCreateBulkOrders() { using (var db = new MvcPhoenix.EF.CMCSQL03Entities()) { string username = HttpContext.Current.User.Identity.Name; string fnTempTable = "tblSuggestedBulk"; string s = String.Format("Update {0} set SupplyID='n/a' where Supplyid is null and UserName='******'", fnTempTable, username); db.Database.ExecuteSqlCommand(s); var qry = (from t in db.tblSuggestedBulk where t.UserName == username select new { t.ClientID, t.SupplyID }).Distinct(); int SupplyIDCount = qry.Count(); DateTime myOrderDate = DateTime.Now; string BatchNumber = myOrderDate.ToString(); foreach (var item in qry) { using (var db1 = new MvcPhoenix.EF.CMCSQL03Entities()) { var newitem = new EF.tblBulkOrder { ClientID = item.ClientID, OrderDate = myOrderDate, Status = "OP", SupplyID = item.SupplyID }; db1.tblBulkOrder.Add(newitem); db1.SaveChanges(); int newpk = newitem.BulkOrderID; //// now create order items records s = String.Format("Insert into tblBulkOrderItem (BulkOrderID,ProductMasterID,Qty,Weight,Status,SupplyID,ItemNotes) Select {0},ProductMasterID,1,ReorderWeight,'OP',SupplyID,ReorderNotes from {1} where SupplyID='{2}'", newpk, fnTempTable, item.SupplyID); db1.Database.ExecuteSqlCommand(s); } } int OrdersCount = qry.Count(); s = String.Format("Delete From {0} where UserName='******'", fnTempTable, username); db.Database.ExecuteSqlCommand(s); return OrdersCount; } }
private static int fnNewBulkOrderItemID() { using (var db = new MvcPhoenix.EF.CMCSQL03Entities()) { var newrec = new EF.tblBulkOrderItem(); db.tblBulkOrderItem.Add(newrec); db.SaveChanges(); return newrec.BulkOrderItemID; } }
public static List<SuggestedBulkOrderItem> fnSuggestedItemsList() { using (var db = new MvcPhoenix.EF.CMCSQL03Entities()) { string username = HttpContext.Current.User.Identity.Name; var mylist = (from t in db.tblSuggestedBulk join dv in db.tblDivision on t.DivisionID equals dv.DivisionID into m from x in m.DefaultIfEmpty() join c in db.tblClient on t.ClientID equals c.ClientID join pm in db.tblProductMaster on t.ProductMasterID equals pm.ProductMasterID where t.UserName == username select new SuggestedBulkOrderItem { id = t.id, clientid = t.ClientID, productmasterid = t.ProductMasterID, divisionid = t.DivisionID, supplyid = t.SupplyID, reorderweight = t.ReorderWeight, reordernotes = t.ReorderNotes, bulkshippedperday = t.BulkShippedPerDay, shelfshippedperday = t.ShelfShippedPerDay, usethisdaystilexpiration = t.UseThisDaysTilExpiration, averageleadtime = t.AverageLeadTime, username = t.UserName, clientname = c.ClientName, logofilename = c.LogoFileName, mastercode = pm.MasterCode, mastername = pm.MasterName, division = x.Division }).ToList(); mylist = (from t in mylist orderby t.mastercode, t.supplyid select t).ToList(); return mylist; } }
public static List<BulkOrder> fnSearchResults(FormCollection fc, string mode) { using (var db = new MvcPhoenix.EF.CMCSQL03Entities()) { var mylist = (from t in db.tblBulkOrder join t2 in db.tblClient on t.ClientID equals t2.ClientID let itemscount = (from items in db.tblBulkOrderItem where (items.BulkOrderID == t.BulkOrderID) select items).Count() let opencount = (from items in db.tblBulkOrderItem where (items.BulkOrderID == t.BulkOrderID) && (items.Status == "OP") select items).Count() orderby t.BulkOrderID descending select new MvcPhoenix.Models.BulkOrder { bulkorderid = t.BulkOrderID, clientid = t.ClientID, supplyid = t.SupplyID, orderdate = t.OrderDate, ordercomment = t.Comment, emailsent = t.EmailSent, clientname = t2.ClientName, itemcount = itemscount, }).ToList(); if (mode == "User") { int myordercount = Convert.ToInt32(fc["ordercount"]); int myclientid = Convert.ToInt32(fc["clientid"]); if (!String.IsNullOrEmpty(fc["ordercount"])) { mylist = (from t in mylist where t.clientid == myclientid select t).ToList(); } if (!String.IsNullOrEmpty(fc["clientid"])) { mylist = (from t in mylist select t).Take(myordercount).ToList(); } } // preset request string sResultsMessage = null; switch (mode) { case "Initial": mylist = mylist.Take(10).ToList(); sResultsMessage = "Initial"; break; case "LastTen": mylist = mylist.Take(10).ToList(); sResultsMessage = "Last Ten"; break; case "UnConfirmed": mylist = (from t in mylist where t.emailsent == null select t).ToList(); sResultsMessage = "Un-confirmed"; break; case "OpenOnly": mylist = (from t in mylist where t.opencount > 0 select t).ToList(); sResultsMessage = "Open Items"; break; default: break; } foreach (var item in mylist) { item.ResultsMessage = sResultsMessage; } return mylist; } }
public static int fnSaveSuggestedItem(SuggestedBulkOrderItem obj) { using (var db = new MvcPhoenix.EF.CMCSQL03Entities()) { if (obj.id == -1) { obj.id = fnNewSuggestedOrderItemID(); } var dbrow = db.tblSuggestedBulk.Find(obj.id); if (obj.reorderweight == null) { obj.reorderweight = 0; } dbrow.ClientID = obj.clientid; dbrow.ProductMasterID = obj.productmasterid; dbrow.ReorderWeight = obj.reorderweight; dbrow.ReorderNotes = obj.reordernotes; var dbMaster = db.tblProductMaster.Find(obj.productmasterid); dbrow.SupplyID = dbMaster.SUPPLYID; dbrow.UserName = HttpContext.Current.User.Identity.Name; db.SaveChanges(); return obj.id; } }
public static int fnSaveItem(BulkOrderItem obj) { System.Threading.Thread.Sleep(1500); using (var db = new MvcPhoenix.EF.CMCSQL03Entities()) { if (obj.bulkorderitemid == -1) { obj.bulkorderitemid = fnNewBulkOrderItemID(); } var dbrow = (from t in db.tblBulkOrderItem where t.BulkOrderItemID == obj.bulkorderitemid select t).FirstOrDefault(); dbrow.BulkOrderID = obj.bulkorderid; dbrow.ProductMasterID = obj.productmasterid; dbrow.Weight = obj.weight; dbrow.Status = obj.itemstatus; dbrow.ETA = obj.eta; dbrow.DateReceived = obj.datereceived; dbrow.ItemNotes = obj.itemnotes; db.SaveChanges(); return obj.bulkorderitemid; } }
public static List<BulkOrderItem> fnItemsList(int id) { using (var db = new MvcPhoenix.EF.CMCSQL03Entities()) { var mylist = (from t in db.tblBulkOrderItem join pm in db.tblProductMaster on t.ProductMasterID equals pm.ProductMasterID where t.BulkOrderID == id select new BulkOrderItem { mastercode = pm.MasterCode, mastername = pm.MasterName, bulkorderitemid = t.BulkOrderItemID, bulkorderid = t.BulkOrderID, productmasterid = t.ProductMasterID, weight = t.Weight, itemstatus = t.Status, eta = t.ETA, datereceived = t.DateReceived, itemnotes = t.ItemNotes }).ToList(); return mylist; } }
public static BulkOrderItem fnFillItemFromDB(int id) { // build object and return using (var db = new MvcPhoenix.EF.CMCSQL03Entities()) { BulkOrderItem obj = new BulkOrderItem(); obj = (from t in db.tblBulkOrderItem where t.BulkOrderItemID == id select new BulkOrderItem { bulkorderitemid = t.BulkOrderItemID, bulkorderid = t.BulkOrderID, productmasterid = t.ProductMasterID, weight = t.Weight, itemstatus = t.Status, eta = t.ETA, datereceived = t.DateReceived, itemnotes = t.ItemNotes, }).FirstOrDefault(); var dbBulkOrder = db.tblBulkOrder.Find(obj.bulkorderid); obj.ListOfProductMasters = fnProductMasterIDs(Convert.ToInt32(dbBulkOrder.ClientID)); obj.ListOfItemStatusIDs = fnOrderItemStatusIDs(); return obj; } }
public static BulkOrder fnFillBulkOrderFromDB(int id) { using (var db = new MvcPhoenix.EF.CMCSQL03Entities()) { BulkOrder obj = new BulkOrder(); obj = (from t in db.tblBulkOrder join c in db.tblClient on t.ClientID equals c.ClientID where t.BulkOrderID == id select new BulkOrder { bulkorderid = t.BulkOrderID, clientid = t.ClientID, clientname = c.ClientName, logofilename = c.LogoFileName, orderdate = t.OrderDate, ordercomment = t.Comment, supplyid = t.SupplyID, bulksupplieremail = t.BulkSupplierEmail, emailsent = t.EmailSent, }).FirstOrDefault(); obj.ListOfSupplyIDs = fnListOfSupplyIDs(obj.clientid); obj.ListOfBulkOrderItem = (from oi in db.tblBulkOrderItem join pm in db.tblProductMaster on oi.ProductMasterID equals pm.ProductMasterID where oi.BulkOrderID == id select new BulkOrderItem { bulkorderitemid = oi.BulkOrderItemID, bulkorderid = oi.BulkOrderID, productmasterid = oi.ProductMasterID, mastercode = pm.MasterCode, mastername = pm.MasterName, weight = oi.Weight, itemstatus = oi.Status, eta = oi.ETA, datereceived = oi.DateReceived, itemnotes = oi.ItemNotes }).ToList(); return obj; } }
public static void fnDeleteSuggestedItem(int id) { using (var db = new MvcPhoenix.EF.CMCSQL03Entities()) { db.Database.ExecuteSqlCommand("Delete From tblSuggestedBulk Where ID=" + id); } }
public static int fnDeleteItem(int id) { using (var db = new MvcPhoenix.EF.CMCSQL03Entities()) { var dbBulkOrder = db.tblBulkOrderItem.Find(id); int pk = Convert.ToInt32(dbBulkOrder.BulkOrderID); string s = "Delete from tblBulkOrderItem where BulkOrderItemID=" + id; db.Database.ExecuteSqlCommand(s); db.Dispose(); return pk; } }
public static BulkOrderItem fnCreateItem(int id) { using (var db = new MvcPhoenix.EF.CMCSQL03Entities()) { BulkOrderItem obj = new BulkOrderItem(); var dbBulkOrder = db.tblBulkOrder.Find(id); obj.bulkorderitemid = -1; obj.bulkorderid = id; obj.productmasterid = null; obj.weight = null; obj.itemstatus = "OP"; obj.eta = null; obj.datereceived = null; obj.itemnotes = null; obj.ListOfProductMasters = fnProductMasterIDs(Convert.ToInt32(dbBulkOrder.ClientID)); obj.ListOfItemStatusIDs = fnOrderItemStatusIDs(); return obj; } }
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; } }
// id=productdetailid public static List<SelectListItem> fnListOfBulkIDs(int? id) { using (MvcPhoenix.EF.CMCSQL03Entities db = new MvcPhoenix.EF.CMCSQL03Entities()) { List<SelectListItem> mylist = new List<SelectListItem>(); mylist = (from t in db.tblBulk join pm in db.tblProductMaster on t.ProductMasterID equals pm.ProductMasterID join pd in db.tblProductDetail on pm.ProductMasterID equals pd.ProductMasterID join sm in db.tblShelfMaster on pd.ProductDetailID equals sm.ProductDetailID where sm.ShelfID == id select new SelectListItem { Value = t.BulkID.ToString(), Text = t.LotNumber }).ToList(); mylist.Insert(0, new SelectListItem { Value = "", Text = "Select Bulk Container" }); return mylist; } }
public static int fnSaveBulkOrder(BulkOrder obj) { System.Threading.Thread.Sleep(1500); using (var db = new MvcPhoenix.EF.CMCSQL03Entities()) { if (obj.bulkorderid == -1) { obj.bulkorderid = fnNewBulkOrderID(); } var dbrow = db.tblBulkOrder.Find(obj.bulkorderid); dbrow.ClientID = obj.clientid; dbrow.OrderDate = obj.orderdate; dbrow.Comment = obj.ordercomment; dbrow.SupplyID = obj.supplyid; dbrow.BulkSupplierEmail = obj.bulksupplieremail; dbrow.EmailSent = obj.emailsent; db.SaveChanges(); return obj.bulkorderid; } }