public static List<SelectListItem> fnBulkStatusIDs()
{
using (var db = new MvcPhoenix.EF.CMCSQL03Entities())
{
List<SelectListItem> mylist = new List<SelectListItem>();
mylist = (from t in db.tblBulk
orderby t.BulkStatus
select new SelectListItem { Value = t.BulkStatus, Text = t.BulkStatus }).Distinct().ToList();
mylist.Insert(0, new SelectListItem { Value = "", Text = "All" });
return mylist;
}
}
public static List<SelectListItem> fnClientIDs()
{
using (var db = new MvcPhoenix.EF.CMCSQL03Entities())
{
List<SelectListItem> mylist = new List<SelectListItem>();
mylist = (from t in db.tblClient
orderby t.ClientName
select new SelectListItem { Value = t.ClientID.ToString(), Text = t.ClientName }).ToList();
mylist.Insert(0, new SelectListItem { Value = "0", Text = "Select Client" });
return mylist;
}
}
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;
}
}
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 List<SuggestedBulkOrderItem> fnSuggestedItemsList()
{
using (var db = new MvcPhoenix.EF.CMCSQL03Entities())
{
string username = HttpContext.Current.User.Identity.Name;
var mylist = (from t in db.tblSuggestedBulk
join dv in db.tblDivision on t.DivisionID equals dv.DivisionID into m
from x in m.DefaultIfEmpty()
join c in db.tblClient on t.ClientID equals c.ClientID
join pm in db.tblProductMaster on t.ProductMasterID equals pm.ProductMasterID
where t.UserName == username
select new SuggestedBulkOrderItem
{
id = t.id,
clientid = t.ClientID,
productmasterid = t.ProductMasterID,
divisionid = t.DivisionID,
supplyid = t.SupplyID,
reorderweight = t.ReorderWeight,
reordernotes = t.ReorderNotes,
bulkshippedperday = t.BulkShippedPerDay,
shelfshippedperday = t.ShelfShippedPerDay,
usethisdaystilexpiration = t.UseThisDaysTilExpiration,
averageleadtime = t.AverageLeadTime,
username = t.UserName,
clientname = c.ClientName,
logofilename = c.LogoFileName,
mastercode = pm.MasterCode,
mastername = pm.MasterName,
division = x.Division
}).ToList();
mylist = (from t in mylist
orderby t.mastercode, t.supplyid
select t).ToList();
return mylist;
}
}
public static List<BulkOrder> fnSearchResults(FormCollection fc, string mode)
{
using (var db = new MvcPhoenix.EF.CMCSQL03Entities())
{
var mylist = (from t in db.tblBulkOrder
join t2 in db.tblClient on t.ClientID equals t2.ClientID
let itemscount = (from items in db.tblBulkOrderItem where (items.BulkOrderID == t.BulkOrderID) select items).Count()
let opencount = (from items in db.tblBulkOrderItem where (items.BulkOrderID == t.BulkOrderID) && (items.Status == "OP") select items).Count()
orderby t.BulkOrderID descending
select new MvcPhoenix.Models.BulkOrder
{
bulkorderid = t.BulkOrderID,
clientid = t.ClientID,
supplyid = t.SupplyID,
orderdate = t.OrderDate,
ordercomment = t.Comment,
emailsent = t.EmailSent,
clientname = t2.ClientName,
itemcount = itemscount,
}).ToList();
if (mode == "User")
{
int myordercount = Convert.ToInt32(fc["ordercount"]);
int myclientid = Convert.ToInt32(fc["clientid"]);
if (!String.IsNullOrEmpty(fc["ordercount"]))
{
mylist = (from t in mylist
where t.clientid == myclientid
select t).ToList();
}
if (!String.IsNullOrEmpty(fc["clientid"]))
{
mylist = (from t in mylist
select t).Take(myordercount).ToList();
}
}
// preset request
string sResultsMessage = null;
switch (mode)
{
case "Initial":
mylist = mylist.Take(10).ToList();
sResultsMessage = "Initial";
break;
case "LastTen":
mylist = mylist.Take(10).ToList();
sResultsMessage = "Last Ten";
break;
case "UnConfirmed":
mylist = (from t in mylist
where t.emailsent == null
select t).ToList();
sResultsMessage = "Un-confirmed";
break;
case "OpenOnly":
mylist = (from t in mylist
where t.opencount > 0
select t).ToList();
sResultsMessage = "Open Items";
break;
default:
break;
}
foreach (var item in mylist)
{
item.ResultsMessage = sResultsMessage;
}
return mylist;
}
}
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 List<BulkOrderItem> fnItemsList(int id)
{
using (var db = new MvcPhoenix.EF.CMCSQL03Entities())
{
var mylist = (from t in db.tblBulkOrderItem
join pm in db.tblProductMaster on t.ProductMasterID equals pm.ProductMasterID
where t.BulkOrderID == id
select new BulkOrderItem
{
mastercode = pm.MasterCode,
mastername = pm.MasterName,
bulkorderitemid = t.BulkOrderItemID,
bulkorderid = t.BulkOrderID,
productmasterid = t.ProductMasterID,
weight = t.Weight,
itemstatus = t.Status,
eta = t.ETA,
datereceived = t.DateReceived,
itemnotes = t.ItemNotes
}).ToList();
return mylist;
}
}
public static BulkOrderItem fnFillItemFromDB(int id)
{
// build object and return
using (var db = new MvcPhoenix.EF.CMCSQL03Entities())
{
BulkOrderItem obj = new BulkOrderItem();
obj = (from t in db.tblBulkOrderItem
where t.BulkOrderItemID == id
select new BulkOrderItem
{
bulkorderitemid = t.BulkOrderItemID,
bulkorderid = t.BulkOrderID,
productmasterid = t.ProductMasterID,
weight = t.Weight,
itemstatus = t.Status,
eta = t.ETA,
datereceived = t.DateReceived,
itemnotes = t.ItemNotes,
}).FirstOrDefault();
var dbBulkOrder = db.tblBulkOrder.Find(obj.bulkorderid);
obj.ListOfProductMasters = fnProductMasterIDs(Convert.ToInt32(dbBulkOrder.ClientID));
obj.ListOfItemStatusIDs = fnOrderItemStatusIDs();
return obj;
}
}
public static BulkOrder fnFillBulkOrderFromDB(int id)
{
using (var db = new MvcPhoenix.EF.CMCSQL03Entities())
{
BulkOrder obj = new BulkOrder();
obj = (from t in db.tblBulkOrder
join c in db.tblClient on t.ClientID equals c.ClientID
where t.BulkOrderID == id
select new BulkOrder
{
bulkorderid = t.BulkOrderID,
clientid = t.ClientID,
clientname = c.ClientName,
logofilename = c.LogoFileName,
orderdate = t.OrderDate,
ordercomment = t.Comment,
supplyid = t.SupplyID,
bulksupplieremail = t.BulkSupplierEmail,
emailsent = t.EmailSent,
}).FirstOrDefault();
obj.ListOfSupplyIDs = fnListOfSupplyIDs(obj.clientid);
obj.ListOfBulkOrderItem = (from oi in db.tblBulkOrderItem
join pm in db.tblProductMaster on oi.ProductMasterID equals pm.ProductMasterID
where oi.BulkOrderID == id
select new BulkOrderItem
{
bulkorderitemid = oi.BulkOrderItemID,
bulkorderid = oi.BulkOrderID,
productmasterid = oi.ProductMasterID,
mastercode = pm.MasterCode,
mastername = pm.MasterName,
weight = oi.Weight,
itemstatus = oi.Status,
eta = oi.ETA,
datereceived = oi.DateReceived,
itemnotes = oi.ItemNotes
}).ToList();
return obj;
}
}
public static void fnDeleteSuggestedItem(int id)
{
using (var db = new MvcPhoenix.EF.CMCSQL03Entities())
{
db.Database.ExecuteSqlCommand("Delete From tblSuggestedBulk Where ID=" + id);
}
}
public static int fnDeleteItem(int id)
{
using (var db = new MvcPhoenix.EF.CMCSQL03Entities())
{
var dbBulkOrder = db.tblBulkOrderItem.Find(id);
int pk = Convert.ToInt32(dbBulkOrder.BulkOrderID);
string s = "Delete from tblBulkOrderItem where BulkOrderItemID=" + id;
db.Database.ExecuteSqlCommand(s);
db.Dispose();
return pk;
}
}
public static BulkOrderItem fnCreateItem(int id)
{
using (var db = new MvcPhoenix.EF.CMCSQL03Entities())
{
BulkOrderItem obj = new BulkOrderItem();
var dbBulkOrder = db.tblBulkOrder.Find(id);
obj.bulkorderitemid = -1;
obj.bulkorderid = id;
obj.productmasterid = null;
obj.weight = null;
obj.itemstatus = "OP";
obj.eta = null;
obj.datereceived = null;
obj.itemnotes = null;
obj.ListOfProductMasters = fnProductMasterIDs(Convert.ToInt32(dbBulkOrder.ClientID));
obj.ListOfItemStatusIDs = fnOrderItemStatusIDs();
return obj;
}
}
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;
}
}
// id=productdetailid
public static List<SelectListItem> fnListOfBulkIDs(int? id)
{
using (MvcPhoenix.EF.CMCSQL03Entities db = new MvcPhoenix.EF.CMCSQL03Entities())
{
List<SelectListItem> mylist = new List<SelectListItem>();
mylist = (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 sm in db.tblShelfMaster on pd.ProductDetailID equals sm.ProductDetailID
where sm.ShelfID == id
select new SelectListItem { Value = t.BulkID.ToString(), Text = t.LotNumber }).ToList();
mylist.Insert(0, new SelectListItem { Value = "", Text = "Select Bulk Container" });
return mylist;
}
}
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;
}
}
