public static void fnGenerateOrderTransactions(int id) { using (var db = new EF.CMCSQL03Entities()) { var oi = (from t in db.tblOrderItem where t.ItemID == id select t).FirstOrDefault(); var o = (from t in db.tblOrderMaster where t.OrderID == oi.OrderID select t).FirstOrDefault(); var clt = (from t in db.tblClient where t.ClientID == o.ClientID select t).FirstOrDefault(); string s = ""; // Tier 1 sample charge s = "Delete from tblOrderTrans where OrderItemID=" + oi.ItemID + " and Transtype = 'SAMP' And CreateUser='******'"; db.Database.ExecuteSqlCommand(s); var tierSize = (from t in db.tblTier where t.ClientID == o.ClientID && t.Size == oi.Size && t.Tier == "1" select t).FirstOrDefault(); if (tierSize != null) { EF.tblOrderTrans newrec = new EF.tblOrderTrans(); newrec.TransDate = System.DateTime.Now; newrec.OrderItemID = oi.ItemID; newrec.OrderID = oi.OrderID; newrec.ClientID = o.ClientID; newrec.TransType = "SAMP"; newrec.TransQty = oi.Qty; newrec.TransAmount = tierSize.Price; newrec.CreateDate = System.DateTime.Now; //newrec.CreateDate = System.DateTimeOffset.UtcNow; newrec.CreateUser = "******"; newrec.BillingGroup = o.BillingGroup; db.tblOrderTrans.Add(newrec); db.SaveChanges(); } else { // Assume this is a SR size ?????????????????????? var tierSpecialRequest = (from t in db.tblTier where t.ClientID == o.ClientID && t.Size == "1SR" && t.Tier == "1" select t).FirstOrDefault(); if (tierSpecialRequest != null) { EF.tblOrderTrans newrec = new EF.tblOrderTrans(); newrec.TransDate = System.DateTime.Now; newrec.OrderItemID = oi.ItemID; newrec.OrderID = oi.OrderID; newrec.ClientID = o.ClientID; newrec.TransType = "SAMP"; newrec.TransQty = oi.Qty; newrec.TransAmount = tierSpecialRequest.Price; newrec.Comments = "Special Request"; newrec.CreateDate = System.DateTime.Now; newrec.CreateUser = "******"; newrec.BillingGroup = o.BillingGroup; db.tblOrderTrans.Add(newrec); db.SaveChanges(); } } // Other charges from shelfmaster // pc 10/27/16 Need to know if we are trying to price a Bulk or Stock item // assume a shelfmaster, then change to bulk if necessary var sm = (from t in db.tblShelfMaster where t.ShelfID == oi.ShelfID select t).FirstOrDefault(); if (oi.BulkID != null) { // find a SM record to use to surcharge this order item var bl = (from t in db.tblBulk where t.BulkID == oi.BulkID select t).FirstOrDefault(); var pm = (from t in db.tblProductMaster where t.ProductMasterID == bl.ProductMasterID select t).FirstOrDefault(); var pd = (from t in db.tblProductDetail where t.ProductMasterID == bl.ProductMasterID && t.ProductCode == pm.MasterCode select t).FirstOrDefault(); sm = (from t in db.tblShelfMaster where t.ProductDetailID == pd.ProductDetailID && t.Size == oi.Size select t).FirstOrDefault(); if (sm == null) { // go no further return; } // this should rest me on a shelfmaster that is really the bulk size sample } var qrySurcharges = (from t in db.tblSurcharge where t.ClientID == o.ClientID select t).FirstOrDefault(); if (sm.HazardSurcharge == true) { fnInsertOrderTrans(oi.ItemID, "HAZD", qrySurcharges.Haz); } if (sm.FlammableSurcharge == true) { fnInsertOrderTrans(oi.ItemID, "FLAM", qrySurcharges.Flam); } if (sm.HeatSurcharge == true) { fnInsertOrderTrans(oi.ItemID, "HEAT", qrySurcharges.Heat); } if (sm.RefrigSurcharge == true) { fnInsertOrderTrans(oi.ItemID, "REFR", qrySurcharges.Refrig); } if (sm.FreezerSurcharge == true) { fnInsertOrderTrans(oi.ItemID, "FREZ", qrySurcharges.Freezer); } if (sm.CleanSurcharge == true) { fnInsertOrderTrans(oi.ItemID, "CLEN", qrySurcharges.Clean); } if (sm.BlendSurcharge == true) { fnInsertOrderTrans(oi.ItemID, "BLND", 0); } if (sm.NalgeneSurcharge == true) { fnInsertOrderTrans(oi.ItemID, "NALG", qrySurcharges.Nalgene); } if (sm.NitrogenSurcharge == true) { fnInsertOrderTrans(oi.ItemID, "NITR", 0); } if (sm.BiocideSurcharge == true) { fnInsertOrderTrans(oi.ItemID, "BIOC", 0); } if (sm.KosherSurcharge == true) { fnInsertOrderTrans(oi.ItemID, "KOSH", 0); } if (sm.LabelSurcharge == true) { fnInsertOrderTrans(oi.ItemID, "LABL", qrySurcharges.LabelFee); } if (sm.OtherSurcharge == true) { fnInsertOrderTrans(oi.ItemID, "OTHR", sm.OtherSurchargeAmt); } } }
public static void fnInsertOrderTrans(int? ItemID, string TransType, decimal? TransAmount) { using (var db = new EF.CMCSQL03Entities()) { string s = String.Format("Delete from tblOrderTrans where OrderItemID={0} and Transtype = '{1}' And CreateUser='******'", ItemID, TransType); db.Database.ExecuteSqlCommand(s); var oi = (from t in db.tblOrderItem where t.ItemID == ItemID select t).FirstOrDefault(); var o = (from t in db.tblOrderMaster where t.OrderID == oi.OrderID select t).FirstOrDefault(); EF.tblOrderTrans newrec = new EF.tblOrderTrans(); newrec.TransDate = System.DateTime.Now; newrec.OrderItemID = ItemID; newrec.OrderID = oi.OrderID; newrec.ClientID = o.ClientID; newrec.BillingGroup = o.BillingGroup; newrec.TransDate = System.DateTime.Now; newrec.TransType = TransType; newrec.TransQty = oi.Qty; newrec.TransAmount = TransAmount; newrec.CreateDate = System.DateTime.Now; newrec.CreateUser = "******"; db.tblOrderTrans.Add(newrec); db.SaveChanges(); } }
public static int fnSaveTrans(OrderTrans vm) { using (var db = new EF.CMCSQL03Entities()) { if (vm.ordertransid == -1) { EF.tblOrderTrans newrec = new EF.tblOrderTrans(); db.tblOrderTrans.Add(newrec); vm.createdate = System.DateTime.Now; vm.createuser = HttpContext.Current.User.Identity.Name; db.SaveChanges(); vm.ordertransid = newrec.OrderTransID; } vm.updatedate = System.DateTime.Now; vm.updateuser = HttpContext.Current.User.Identity.Name; var d = (from t in db.tblOrderTrans where t.OrderTransID == vm.ordertransid select t).FirstOrDefault(); d.OrderID = vm.orderid; d.OrderItemID = vm.orderitemid; d.ClientID = vm.clientid; d.TransDate = vm.transdate; d.TransType = vm.transtype; d.TransQty = vm.transqty; d.TransAmount = vm.transamount; d.Comments = vm.comments; d.CreateDate = vm.createdate; d.CreateUser = vm.createuser; d.UpdateDate = vm.updatedate; d.UpdateUser = vm.updateuser; db.SaveChanges(); return vm.ordertransid; } }