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