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