private static int fnNewBulkOrderID()
{
using (var db = new MvcPhoenix.EF.CMCSQL03Entities())
{
var newrec = new EF.tblBulkOrder();
db.tblBulkOrder.Add(newrec);
db.SaveChanges();
return newrec.BulkOrderID;
}
}
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;
}
}
