public static List<ReturnStockViewModel> fnListOfStockRecords()
{
using (var db = new MvcPhoenix.EF.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 pm in db.tblProductMaster on pd.ProductMasterID equals pm.ProductMasterID
join bl in db.tblBulk on t.BulkID equals bl.BulkID
join dv in db.tblDivision on pd.DivisionID equals dv.DivisionID
orderby pd.ProductCode
select new ReturnStockViewModel
{
ClientID = pm.ClientID,
StockID = t.StockID,
LotNumber = bl.LotNumber,
ProductCode = pd.ProductCode,
ProductName = pd.ProductName,
Warehouse = t.Warehouse,
Bin = t.Bin,
QtyOnHand = t.QtyOnHand,
Size = sm.Size,
ExpirationDate = bl.ExpirationDate,
ShelfStatus = t.ShelfStatus,
markedforreturn = t.MarkedForReturn,
divisionid = pd.DivisionID,
divisionname = dv.Division
}).ToList();
return vm;
}
}
public static List<ReturnBulkViewModel> fnListOfBulkRecords()
{
using (var db = new MvcPhoenix.EF.CMCSQL03Entities())
{
// make this generic and further filter elsewhere
var vm = (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 dv in db.tblDivision on pd.DivisionID equals dv.DivisionID
join cl in db.tblClient on pm.ClientID equals cl.ClientID
orderby pm.MasterCode
select new Models.ReturnBulkViewModel
{
bulkid = t.BulkID,
lotnumber = t.LotNumber,
clientid = cl.ClientID,
clientname = cl.ClientName,
warehouse = t.Warehouse,
MasterCode = pm.MasterCode,
MasterName = pm.MasterName,
um = t.UM,
receiveweight = t.ReceiveWeight,
currentweight = t.CurrentWeight,
expirationdate = t.ExpirationDate,
ceaseshipdate = t.CeaseShipDate,
bulkstatus = t.BulkStatus,
bin = t.Bin,
markedforreturn = t.MarkedForReturn,
divisionid = pd.DivisionID,
divisionname = dv.Division
}).ToList();
return vm;
}
}
public static int fnSaveOrder(OrderMasterFull vm)
{
fnArchiveOrderMaster(vm.orderid);
// Take a ViewModel and Insert/Update DB / Return the PK
using (var db = new MvcPhoenix.EF.CMCSQL03Entities())
{
if (vm.orderid == -1)
{
vm.orderid = fnNewOrderID();
}
// update time stamps
vm.UpdateUser = HttpContext.Current.User.Identity.Name;
vm.UpdateDate = System.DateTime.Now;
var q = (from t in db.tblOrderMaster where t.OrderID == vm.orderid select t).FirstOrDefault();
q.OrderDate = vm.orderdate;
q.ClientID = vm.clientid;
q.Customer = vm.customer;
q.CMCOrder = vm.cmcorder;
q.WebOrderID = vm.weborderid;
q.CMCLegacyNum = vm.cmclegacynumber;
q.CustOrdNum = vm.custordnum;
q.CustSapNum = vm.custsapnum;
q.CustRefNum = vm.custrefnum;
q.OrderType = vm.ordertype;
q.OrderDate = vm.orderdate;
q.Company = vm.company;
q.Street = vm.street;
q.Street2 = vm.street2;
q.Street3 = vm.street3;
q.City = vm.city;
q.State = vm.state;
q.Zip = vm.Zip;
q.Country = vm.country;
q.Attention = vm.attention;
q.Email = vm.email;
q.SalesRep = vm.salesrep;
q.SalesEmail = vm.sales_email;
q.Req = vm.req;
q.ReqPhone = vm.reqphone;
q.ReqFax = vm.reqfax;
q.ReqEmail = vm.reqemail;
q.EndUse = vm.enduse;
q.ShipVia = vm.shipvia;
q.ShipAcct = vm.shipacct;
q.Phone = vm.phone;
q.Source = vm.source;
q.Fax = vm.fax;
q.Tracking = vm.tracking;
q.Special = vm.special;
q.SpecialInternal = vm.specialinternal;
q.Lit = Convert.ToBoolean(vm.lit);
q.Region = vm.region;
q.COA = vm.coa;
q.TDS = vm.tds;
q.CID = vm.cid;
q.CustAcct = vm.custacct;
q.ACode = vm.acode;
q.ImportFile = vm.importfile;
q.ImportDateLine = vm.importdateline;
q.Timing = vm.timing;
q.Volume = vm.volume;
q.SampleRack = Convert.ToBoolean(vm.samplerack);
q.CMCUser = vm.cmcuser;
q.CustomerReference = vm.customerreference;
q.TotalOrderWeight = (vm.totalorderweight);
q.SPSTaxID = vm.spstaxid;
q.SPSCurrency = vm.spscurrency;
q.SPSShippedWt = vm.spsshippedwt;
q.SPSFreightCost = vm.spsfreightcost;
q.InvoiceCompany = vm.invoicecompany;
q.InvoiceTitle = vm.invoicetitle;
q.InvoiceFirstName = vm.invoicefirstname;
q.InvoiceLastName = vm.invoicelastname;
q.InvoiceAddress1 = vm.invoiceaddress1;
q.InvoiceAddress2 = vm.invoiceaddress2;
q.InvoiceAddress3 = vm.invoiceaddress3;
q.InvoiceCity = vm.invoicecity;
q.InvoiceStateProv = vm.invoicestateprov;
q.InvoicePostalCode = vm.invoicepostalcode;
q.InvoiceCountry = vm.invoicecountry;
q.InvoicePhone = vm.invoicephone;
q.CustOrderType = vm.custordertype;
q.CustRequestDate = vm.custrequestdate;
q.ApprovalDate = vm.approvaldate;
q.RequestedDeliveryDate = vm.requesteddeliverydate;
q.CustTotalItems = vm.custtotalitems;
q.CustRequestedCarrier = vm.custrequestedcarrier;
q.LegacyID = (vm.legacyid);
q.SalesRepPhone = vm.salesrepphone;
q.SalesRepTerritory = vm.salesrepterritory;
q.MarketingRep = vm.marketingrep;
q.MarketingRepEmail = vm.marketingrepemail;
q.Distributor = vm.distributor;
q.PreferredCarrier = vm.preferredcarrier;
q.ApprovalNeeded = vm.approvalneeded;
q.UpdateUser = vm.UpdateUser;
q.UpdateDate = vm.UpdateDate;
q.CreateUser = vm.CreateUser;
q.CreateDate = vm.CreateDate;
q.BillingGroup = vm.billinggroup;
// reset the value
q.IsSDN = false;
db.SaveChanges();
fnSaveOrderPostUpdate(vm);
return vm.orderid;
}
}
public static int fnNewOrderID()
{
using (var db = new MvcPhoenix.EF.CMCSQL03Entities())
{
MvcPhoenix.EF.tblOrderMaster newrec = new MvcPhoenix.EF.tblOrderMaster();
db.tblOrderMaster.Add(newrec);
db.SaveChanges();
return newrec.OrderID;
}
}
public static Client fnFillClient(int id)
{
MvcPhoenix.EF.CMCSQL03Entities db = new MvcPhoenix.EF.CMCSQL03Entities();
Client obj = new Client();
var qry = (from t in db.tblClient
where t.ClientID == id
select t).FirstOrDefault();
obj.ClientID = qry.ClientID;
// Cheat this value until SQL changes flush
obj.LogoFileName = qry.LogoFileName;
obj.LegacyID = qry.LegacyID; obj.GlobalClientID = qry.GlobalClientID; obj.ClientCode = qry.ClientCode; obj.ClientName = qry.ClientName;
obj.CMCLocation = qry.CMCLocation; obj.CustomerReference = qry.CustomerReference; obj.CMCLongCustomer = qry.CMCLongCustomer;
obj.MSDS = qry.MSDS; obj.TDS = qry.TDS; obj.COA = qry.COA; obj.DocumentDirectory = qry.DocumentDirectory;
obj.MSDS_Folder = qry.MSDS_Folder; obj.TDS_Folder = qry.TDS_Folder; obj.COA_Folder = qry.COA_Folder; obj.SP_ServiceSummary = qry.SP_ServiceSummary;
obj.SP_Detail1 = qry.SP_Detail1; obj.SP_Detail2 = qry.SP_Detail2; obj.SP_Detail3 = qry.SP_Detail3; obj.SP_Detail4 = qry.SP_Detail4; obj.SP_Detail5 = qry.SP_Detail5;
obj.SP_RevDate = qry.SP_RevDate; obj.SP_CommValue = qry.SP_CommValue; obj.SP_COARequired = qry.SP_COARequired; obj.SP_TDSRequired = qry.SP_TDSRequired;
obj.SP_MSDSLanguage = qry.SP_MSDSLanguage; obj.SP_Freezable = qry.SP_Freezable; obj.SP_LabelLanguage = qry.SP_LabelLanguage; obj.SP_Expedite = qry.SP_Expedite;
obj.BOLComment = qry.BOLComment;
db.Dispose();
return obj;
}
public ActionResult CreateReturnOrderStockItems(int orderid)
{
using (var db = new MvcPhoenix.EF.CMCSQL03Entities())
{
var stock = (from t in db.tblStock where t.MarkedForReturn == true select t).ToList();
foreach (var row in stock)
{
var bl = db.tblBulk.Find(row.BulkID);
var sm = db.tblShelfMaster.Find(row.ShelfID);
var pd = db.tblProductDetail.Find(sm.ProductDetailID);
var pm = db.tblProductMaster.Find(bl.ProductMasterID);
int newItemId = Services.OrderService.fnNewItemID();
var newitem = (from t in db.tblOrderItem where t.ItemID == newItemId select t).FirstOrDefault();
newitem.OrderID = orderid;
newitem.ShelfID = sm.ShelfID;
newitem.ProductCode = pd.ProductCode;
newitem.ProductName = pd.ProductName;
newitem.LotNumber = bl.LotNumber;
newitem.Qty = row.QtyOnHand;
newitem.Size = sm.Size;
newitem.Weight = sm.UnitWeight;
newitem.AllocateStatus = "A";
newitem.Bin = row.Bin;
newitem.ItemNotes = "Return Shelf Order Item";
// Insert log record
Services.OrderService.fnInsertLogRecord("SS-RTN", System.DateTime.Now, row.StockID, null, 1, sm.UnitWeight, System.DateTime.Now, User.Identity.Name, null, null);
//update bulk record
row.QtyOnHand = 0;
row.ShelfStatus = "RETURN";
row.MarkedForReturn = null;
db.SaveChanges();
Services.OrderService.fnGenerateOrderTransactions(newItemId);
// Should we DELETE the tblStock record???????????????????????????????????
}
}
return null;
}
public ActionResult CreateReturnOrderBulkItems(int orderid)
{
using (var db = new MvcPhoenix.EF.CMCSQL03Entities())
{
var bulk = (from t in db.tblBulk where t.MarkedForReturn == true select t).ToList();
foreach (var row in bulk)
{
var pm = (from t in db.tblProductMaster where t.ProductMasterID == row.ProductMasterID select t).FirstOrDefault();
int newItemId = Services.OrderService.fnNewItemID();
var newitem = (from t in db.tblOrderItem where t.ItemID == newItemId select t).FirstOrDefault();
newitem.OrderID = orderid;
newitem.BulkID = row.BulkID;
newitem.ProductCode = pm.MasterCode;
newitem.ProductName = pm.MasterName;
newitem.LotNumber = row.LotNumber;
newitem.Qty = 1;
newitem.Size = row.UM;
newitem.Weight = row.CurrentWeight;
newitem.AllocateStatus = "A";
newitem.Bin = row.Bin;
newitem.ItemNotes = "Return Bulk Order Item";
// Insert log record
Services.OrderService.fnInsertLogRecord("BS-RTN", System.DateTime.Now, null, row.BulkID, 1, row.CurrentWeight, System.DateTime.Now, User.Identity.Name, null, null);
//update bulk record
row.CurrentWeight = 0;
row.BulkStatus = "RETURN";
row.MarkedForReturn = null;
db.SaveChanges();
Services.OrderService.fnGenerateOrderTransactions(newItemId);
}
}
return null;
}
public static int SaveInvoice(InvoiceViewModel vm)
{
using (var db = new MvcPhoenix.EF.CMCSQL03Entities())
{
if (vm.invoiceid == -1)
{
vm.invoiceid = NewInvoiceID();
vm.createdate = System.DateTime.Now;
vm.createdby = HttpContext.Current.User.Identity.Name;
vm.invoicedate = System.DateTimeOffset.UtcNow;
vm.status = "NEW";
}
var confirmVerify = false;
if (vm.verifiedaccuracy == true)
{
///Add logic to check invoice status if not new or verified already
///check if verified by user == created user. should be different
///capture verified datetime and user identity and save to db
///verified invoices should be locked from further edits..
vm.status = "VERIFIED";
confirmVerify = true;
}
// Capture user info in viewmodel
vm.updatedby = HttpContext.Current.User.Identity.Name;
vm.updatedate = System.DateTime.Now;
var q = (from t in db.tblInvoice
where t.InvoiceID == vm.invoiceid
select t).FirstOrDefault();
q.InvoiceNumber = vm.invoiceid; //there can be multiple invoice numbers which can be used to group different order types for an invoice
q.BillingGroup = vm.billinggroup;
q.WarehouseLocation = vm.warehouselocation;
q.ClientID = vm.clientid;
q.ClientName = vm.clientname;
q.CreatedBy = vm.createdby;
q.CreateDate = vm.createdate;
q.UpdatedBy = vm.updatedby;
q.UpdateDate = vm.updatedate;
q.InvoiceStartDate = vm.invoicestartdate;
q.InvoiceEndDate = vm.invoiceenddate;
if (confirmVerify == true)
{
q.VerifiedAccuracy = vm.verifiedaccuracy;
q.VerifiedBy = HttpContext.Current.User.Identity.Name;
q.VerifyDate = System.DateTimeOffset.UtcNow;
}
q.Status = vm.status;
q.InvoiceDate = vm.invoicedate;
q.InvoicePeriod = vm.invoiceperiod;
q.PONumber = vm.ponumber;
q.NetTerm = vm.netterm;
q.BillTo = vm.billto;
q.RemitTo = vm.remitto;
q.Currency = vm.currency;
q.Tier = vm.tier;
q.OrderType = vm.ordertype;
q.SampleShipSameDay = vm.sampleshipsameday;
q.SampleShipNextDay = vm.sampleshipnextday;
q.SampleShipSecondDay = vm.sampleshipsecondday;
q.SampleShipOther = vm.sampleshipother;
q.TotalSamples = vm.totalsamples;
q.TotalCostSamples = vm.totalcostsamples;
q.TotalFreight = vm.totalfreight;
q.TotalFrtHzdSchg = vm.totalfrtHzdSchg;
q.TotalAdminCharge = vm.totaladmincharge;
q.TotalDue = vm.totaldue;
q.GrandTotal = vm.grandtotal;
q.SamplesQty = vm.samplesqty;
q.SamplesRate = vm.samplesrate;
q.SamplesCharge = vm.samplescharge;
q.RevenueQty = vm.revenueqty;
q.RevenueRate = vm.revenuerate;
q.RevenueCharge = vm.revenuecharge;
q.NonRevenueQty = vm.nonrevenueqty;
q.NonRevenueRate = vm.nonrevenuerate;
q.NonRevenueCharge = vm.nonrevenuecharge;
q.ManualEntryQty = vm.manualentryqty;
q.ManualEntryRate = vm.manualentryrate;
q.ManualEntryCharge = vm.manualentrycharge;
q.FollowUpQty = vm.followupqty;
q.FollowUpRate = vm.followuprate;
q.FollowUpCharge = vm.followupcharge;
q.LabelPrtQty = vm.labelprtqty;
q.LabelPrtRate = vm.labelprtrate;
q.LabelPrtCharge = vm.labelprtcharge;
q.LabelStockCharge = vm.labelstock;
q.ReLabelPrtQty = vm.relabelprtqty;
q.ReLabelPrtRate = vm.relabelprtrate;
q.ReLabelPrtCharge = vm.relabelprtcharge;
q.ReLabelFeeQty = vm.relabelfeeqty;
q.ReLabelFeeRate = vm.relabelfeerate;
q.ReLabelFeeCharge = vm.relabelfeecharge;
q.ProductSetupQty = vm.productsetupqty;
q.ProductSetupRate = vm.productsetuprate;
q.ProductSetupCharge = vm.productsetupcharge;
q.CCProcessQty = vm.ccprocessqty;
q.CCProcessRate = vm.ccprocessrate;
q.CCProcessCharge = vm.ccprocesscharge;
q.CCCredit = vm.cccredit;
q.GlobalProcessCharge = vm.globalprocesscharge;
q.MiscCreditCharge = vm.misccreditcharge;
q.ITSupportCharge = vm.itsupportcharge;
q.EmptyDrumCharge = vm.emptydrumcharge;
q.RushShipQty = vm.rushshipqty;
q.RushShipRate = vm.rushshiprate;
q.RushShipCharge = vm.rushshipcharge;
q.EmptyPailsQty = vm.emptypailsqty;
q.EmptyPailsRate = vm.emptypailsrate;
q.EmptyPailsCharge = vm.emptypailscharge;
q.EmptyPailsFgtCharge = vm.emptypailsfgtcharge;
q.InactiveStockQty = vm.inactivestockqty;
q.InactiveStockRate = vm.inactivestockrate;
q.InactiveStockCharge = vm.inactivestockcharge;
q.HM181PkgCharge = vm.hm181pkgcharge;
q.DocHandlingCharge = vm.dochandlingcharge;
q.MinimalSampleCharge = vm.minimalsamplecharge;
db.SaveChanges();
return vm.invoiceid;
}
}
public static int NewInvoiceID()
{
using (var db = new MvcPhoenix.EF.CMCSQL03Entities())
{
MvcPhoenix.EF.tblInvoice nr = new MvcPhoenix.EF.tblInvoice();
db.tblInvoice.Add(nr);
db.SaveChanges();
return nr.InvoiceID;
}
}