public static string fnBuildDivisionDropDown(int id)
{
using (var db = new CMCSQL03Entities())
{
var qry = (from t in db.tblDivision
where t.ClientID == id
orderby t.Division, t.BusinessUnit
select t);
string s = "<option value='0'>All Divisions</option>";
if (qry.Count() > 0)
{
foreach (var item in qry)
{
s = s + "<option value=" + item.DivisionID.ToString() + ">" + item.Division + " - " + item.BusinessUnit + "</option>";
}
}
else
{
s = s + "<option value=0>No Divisions Found</option>";
}
s = s + "</select>";
return s;
}
}
public static List<SelectListItem> fnCarriers()
{
using (var db = new CMCSQL03Entities())
{
List<SelectListItem> mylist = new List<SelectListItem>();
mylist = (from t in db.tblCarrier
orderby t.CarrierName
select new SelectListItem { Value = t.CarrierName, Text = t.CarrierName }).ToList();
mylist.Insert(0, new SelectListItem { Value = "0", Text = "Select Carrier" });
return mylist;
}
}
public static List<SelectListItem> fnBulkStatusIDs()
{
using (var db = new 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 = "0", Text = "Please Select" });
return mylist;
}
}
public static List<SelectListItem> fnClientIDs()
{
using (var db = new 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 List<SelectListItem> fnBulkSuppliers(int id)
{
using (var db = new CMCSQL03Entities())
{
List<SelectListItem> mylist = new List<SelectListItem>();
mylist = (from t in db.tblBulkSupplier
where t.ClientID == id
orderby t.SupplyID
select new SelectListItem { Value = t.BulkSupplierID.ToString(), Text = t.SupplyID }).ToList();
mylist.Insert(0, new SelectListItem { Value = "0", Text = "Select Supplier" });
return mylist;
}
}
public static string fnBuildProductMasterDropDown(int clientid)
{
// This returns ONLY the <option> portion of the <select> tag, thus allowing the <select> tag to
// be propering decorated with onchange= etc..
using (var db = new CMCSQL03Entities())
{
var qry = (from t in db.tblProductMaster where t.ClientID == clientid orderby t.MasterCode, t.MasterName select t);
string s = "<option value='0' selected=true>Select Master Code</option>";
if (qry.Count() > 0)
{
foreach (var item in qry)
{ s = s + "<option value=" + item.ProductMasterID.ToString() + ">" + item.MasterCode + " - " + item.MasterName + "</option>"; }
}
else
{ s = s + "<option value=0>No Products Found</option>"; }
s = s + "</select>";
return s;
}
}
public static List<BulkContainerViewModel> fnBulkContainerList()
{
// full list
using (var db = new CMCSQL03Entities())
{
var mylist = (from t in db.tblBulk
join pm in db.tblProductMaster on t.ProductMasterID equals pm.ProductMasterID
join cl in db.tblClient on pm.ClientID equals cl.ClientID
orderby cl.ClientName, pm.MasterCode
select new BulkContainerViewModel
{
bulkid = t.BulkID,
warehouse = t.Warehouse,
bin = t.Bin,
clientname = cl.ClientName,
MasterCode = pm.MasterCode,
MasterName = pm.MasterName,
bulkstatus = t.BulkStatus,
productmasterid = t.ProductMasterID,
clientid = cl.ClientID
}).ToList();
return mylist;
}
}
public static List<SelectListItem> fnOrderTypes()
{
using (var db = new CMCSQL03Entities())
{
List<SelectListItem> mylist = new List<SelectListItem>();
mylist = (from t in db.tblOrderType
orderby t.Description
select new SelectListItem { Value = t.OrderType, Text = t.Description }).ToList();
mylist.Insert(0, new SelectListItem { Value = "0", Text = "Select Order Type" });
return mylist;
}
}
public static List<SelectListItem> fnHSCodes()
{
using (var db = new CMCSQL03Entities())
{
List<SelectListItem> mylist = new List<SelectListItem>();
mylist = (from t in db.tblHSCode
orderby t.HarmonizedCode
select new SelectListItem { Value = t.HarmonizedCode, Text = t.HarmonizedCode }).ToList();
mylist.Insert(0, new SelectListItem { Value = "0", Text = "Select Harmonized Code" });
return mylist;
}
}
public static PrePackViewModel fnNewBulkContainerForPrePack(int clientid, int productdetailid)
{
PrePackViewModel obj = new PrePackViewModel();
using (var db = new CMCSQL03Entities())
{
var dbClient = db.tblClient.Find(clientid);
var dbProductDetail = db.tblProductDetail.Find(productdetailid);
obj.productmasterid = dbProductDetail.ProductMasterID;
obj.isknownmaterial = true;
obj.clientid = clientid;
obj.clientname = dbClient.ClientName;
obj.logofilename = dbClient.LogoFileName;
obj.bulkid = -1; // for insert later
obj.receivedate = DateTime.Now;
obj.carrier = null;
obj.ListOfCarriers = fnCarriers();
obj.warehouse = null;
obj.ListOfWareHouses = fnWarehouseIDs();
obj.enteredby = null;
obj.lotnumber = null;
obj.receivedby = null;
obj.mfgdate = null;
obj.expirationdate = null;
obj.ceaseshipdate = null;
obj.qcdate = null;
obj.msdsincluded = null;
obj.coaincluded = null;
obj.productcode = dbProductDetail.ProductCode;
obj.productname = dbProductDetail.ProductName;
obj.ListOfShelfMasters = (from t in db.tblShelfMaster
orderby t.ShelfID
where t.ProductDetailID == productdetailid
select new ItemForPrePackViewModel { shelfid = t.ShelfID, size = t.Size, bin = t.Bin }).ToList();
obj.ItemsCount = obj.ListOfShelfMasters.Count();
return obj;
}
}
public static void fnSaveStock(StockViewModel vm)
{
using (var db = new CMCSQL03Entities())
{
var q = (from t in db.tblStock
where t.StockID == vm.StockID
select t).FirstOrDefault();
q.Warehouse = vm.Warehouse;
q.QtyOnHand = vm.QtyOnHand;
q.Bin = vm.Bin;
q.ShelfStatus = vm.ShelfStatus;
q.WasteAccumStartDate = vm.WasteAccumStartDate;
q.UpdateDate = System.DateTime.Now;
q.UpdateUser = HttpContext.Current.User.Identity.Name;
db.SaveChanges();
}
}
public static List<SelectListItem> fnListOfShelfStatusIDs()
{
using (var db = new CMCSQL03Entities())
{
List<SelectListItem> mylist = new List<SelectListItem>();
mylist.Add(new SelectListItem { Text = "AVAIL", Value = "AVAIL" });
mylist.Add(new SelectListItem { Text = "TEST", Value = "TEST" });
mylist.Add(new SelectListItem { Text = "HOLD", Value = "HOLD" });
mylist.Add(new SelectListItem { Text = "QC", Value = "QC" });
mylist.Add(new SelectListItem { Text = "RETURN", Value = "RETURN" });
mylist.Insert(0, new SelectListItem { Value = "0", Text = "" });
return mylist;
}
}
public static StockViewModel fnFillStockViewModel(int id)
{
using (var db = new 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 bk in db.tblBulk on t.BulkID equals bk.BulkID
where t.StockID == id
select new StockViewModel
{
StockID = t.StockID,
ShelfID = t.ShelfID,
BulkID = t.BulkID,
Warehouse = t.Warehouse,
QtyOnHand = t.QtyOnHand,
QtyAllocated = t.QtyAllocated,
Bin = t.Bin,
ShelfStatus = t.ShelfStatus,
WasteAccumStartDate = t.WasteAccumStartDate,
CreateDate = t.CreateDate,
CreateUser = t.CreateUser,
ProductDetailID = pd.ProductDetailID,
ProductCode = pd.ProductCode,
ProductName = pd.ProductName,
LotNumber = bk.LotNumber,
ExpirationDate = bk.ExpirationDate,
MfgDate = bk.MfgDate,
QCDate = bk.QCDate,
CeaseShipDate = bk.CeaseShipDate,
Size = sm.Size,
UnitWeight = sm.UnitWeight,
UpdateDate = t.UpdateDate,
UpdateUser = t.UpdateUser
}).FirstOrDefault();
vm.ListOfShelfStatusIDs = Services.InventoryService.fnListOfShelfStatusIDs();
vm.ListOfShelfMasterIDs = Services.InventoryService.fnListOfShelfMasterIDs(vm.ProductDetailID);
vm.ListOfBulkIDs = Services.InventoryService.fnListOfBulkIDs(vm.ShelfID);
vm.ListOfWareHouseIDs = Services.InventoryService.fnListOfWarehouseIDs();
return vm;
}
}
public static List<SelectListItem> fnProductMasterIDs(int? id, int? PmID = null)
{
using (var db = new CMCSQL03Entities())
{
List<SelectListItem> mylist = new List<SelectListItem>();
if (PmID == null)
{
mylist = (from t in db.tblProductMaster
where t.ClientID == id
orderby t.MasterCode, t.MasterName
select new SelectListItem { Value = t.ProductMasterID.ToString(), Text = t.MasterCode + " - " + t.MasterName.Substring(0, 25) }).ToList();
mylist.Insert(0, new SelectListItem { Value = "0", Text = "Please Select" });
}
else
{
mylist = (from t in db.tblProductMaster
where t.ClientID == id && t.ProductMasterID == PmID
orderby t.MasterCode, t.MasterName
select new SelectListItem { Value = t.ProductMasterID.ToString(), Text = t.MasterCode + " - " + t.MasterName.Substring(0, 25) }).ToList();
}
return mylist;
}
}
public static BulkContainerViewModel fnFillBulkContainerFromDB(int id)
{
// id=bulkid
using (var db = new CMCSQL03Entities())
{
var obj = (from t in db.tblBulk
where t.BulkID == id
select new BulkContainerViewModel
{
isknownmaterial = true,
bulkid = t.BulkID,
productmasterid = t.ProductMasterID,
warehouse = t.Warehouse,
receivedate = t.ReceiveDate,
carrier = t.Carrier,
receivedby = t.ReceivedBy,
enteredby = t.EnteredBy,
receiveweight = t.ReceiveWeight,
lotnumber = t.LotNumber,
mfgdate = t.MfgDate,
expirationdate = t.ExpirationDate,
ceaseshipdate = t.CeaseShipDate,
bulkstatus = t.BulkStatus,
qty = "1",
um = t.UM,
containercolor = t.ContainerColor,
bin = t.Bin,
containertype = t.ContainerType,
coaincluded = t.COAIncluded,
msdsincluded = t.MSDSIncluded,
currentweight = t.CurrentWeight,
qcdate = t.QCDate,
returnlocation = t.ReturnLocation,
noticedate = t.NoticeDate,
bulklabelnote = t.BulkLabelNote,
receivedascode = t.ReceivedAsCode,
receivedasname = t.ReceivedAsName,
containernotes = t.ContainerNotes,
otherstorage = t.OtherStorage
}).FirstOrDefault();
var qPM = (from t in db.tblProductMaster where t.ProductMasterID == obj.productmasterid select t).FirstOrDefault();
var qCL = (from t in db.tblClient where t.ClientID == qPM.ClientID select t).FirstOrDefault();
obj.clientid = qPM.ClientID;
obj.clientname = qCL.ClientName;
obj.logofilename = qCL.LogoFileName;
obj.ListOfWareHouses = fnWarehouseIDs();
obj.ListOfBulkStatusIDs = fnBulkStatusIDs();
obj.ListOfContainerTypeIDs = ReceivingService.fnContainerTypeIDs();
obj.ListOfCarriers = ReceivingService.fnCarriers();
obj.MasterCode = qPM.MasterCode;
obj.MasterName = qPM.MasterName;
return obj;
}
}
public static List<SelectListItem> fnWarehouseIDs()
{
using (var db = new CMCSQL03Entities())
{
List<SelectListItem> mylist = new List<SelectListItem>();
mylist.Add(new SelectListItem { Value = "CO", Text = "CO" });
mylist.Add(new SelectListItem { Value = "CT", Text = "CT" });
mylist.Add(new SelectListItem { Value = "EU", Text = "EU" });
mylist.Add(new SelectListItem { Value = "AP", Text = "AP" });
mylist.Insert(0, new SelectListItem { Value = "", Text = "All" });
return mylist;
}
}
public static void fnTagItemToBeClosed(int id, bool ischecked)
{
// update the ToBeClosed flag on the bulk order item record
string s = "";
using (var db = new CMCSQL03Entities())
{
// clear it always
s = "Update tblBulkOrderItem set ToBeClosed=0 where BulkOrderItemID=" + id;
db.Database.ExecuteSqlCommand(s);
}
if (ischecked == true)
{
using (var db = new CMCSQL03Entities())
{
// tag it
s = "Update tblBulkOrderItem set ToBeClosed=1 where BulkOrderItemID=" + id;
db.Database.ExecuteSqlCommand(s);
}
}
}
public static List<SelectListItem> fnReportCriterias()
{
using (var db = new CMCSQL03Entities())
{
List<SelectListItem> mylist = new List<SelectListItem>();
mylist = (from t in db.tblReportCriteria
orderby t.Display
select new SelectListItem { Value = t.Display, Text = t.ReportName }).ToList();
mylist.Insert(0, new SelectListItem { Value = "0", Text = "Select Report Criteria" });
return mylist;
}
}
public static List<SelectListItem> fnStatusNotes()
{
using (var db = new CMCSQL03Entities())
{
List<SelectListItem> mylist = new List<SelectListItem>();
mylist = (from t in db.tblStatusNotes
orderby t.Note
select new SelectListItem { Value = t.StatusNotesID.ToString(), Text = t.Note }).ToList();
mylist.Insert(0, new SelectListItem { Value = "0", Text = "Select Status Note" });
return mylist;
}
}
public static BulkContainerViewModel fnNewBulkContainerUnKnown()
{
// id=productmasterid
BulkContainerViewModel obj = new BulkContainerViewModel();
using (var db = new CMCSQL03Entities())
{
obj.isknownmaterial = false;
obj.productmasterid = null;
obj.bulkid = -1; // for insert later
obj.clientid = null;
obj.clientname = null;
obj.logofilename = null;
obj.productmasterid = null;
obj.receivedate = DateTime.Now;
obj.bulkstatus = "RECD";
obj.MasterCode = null;
obj.MasterName = null;
obj.ListOfWareHouses = fnWarehouseIDs();
obj.ListOfProductMasters = fnProductMasterIDs(obj.clientid, null);
obj.ListOfBulkStatusIDs = fnBulkStatusIDs();
obj.ListOfContainerTypeIDs = fnContainerTypeIDs();
obj.ListOfCarriers = fnCarriers();
return obj;
}
}
public static List<SelectListItem> fnDivisionIDs(int clientid)
{
using (var db = new CMCSQL03Entities())
{
List<SelectListItem> mylist = new List<SelectListItem>();
mylist = (from c in db.tblDivision
where c.ClientID == clientid
select new SelectListItem { Value = c.DivisionID.ToString(), Text = c.Division }).ToList();
mylist.Insert(0, new SelectListItem { Value = "0", Text = "Division" });
return mylist;
}
}
public static int fnGenerateSuggestedOrder(int clientid, int divisionid)
{
// return the number of items created
int itemscount = 0;
string username = HttpContext.Current.User.Identity.Name;
using (var db = new CMCSQL03Entities())
{
string s;
string fnTempTable = "tblSuggestedBulk";
// Clear the work table of my records
s = String.Format("Delete from {0} where UserName='******'", fnTempTable, username);
db.Database.ExecuteSqlCommand(s);
// Build a list of ProductMasters to analyze
var q1 = (from pd in db.tblProductDetail
join pm in db.tblProductMaster on pd.ProductMasterID equals pm.ProductMasterID
where pm.ClientID == clientid
select new
{
pm.ClientID,
pm.ProductMasterID,
pm.SUPPLYID,
pm.ShlfLife,
pm.ProductSetupDate,
pd.DivisionID,
}).ToList();
// Restrict list to a PD.DivisionID if user requested
if (divisionid > 0)
{
q1 = (from t in q1
where t.DivisionID == divisionid
select t).ToList();
}
// Insert new records into the work table
foreach (var row in q1)
{
var newrec = new EF.tblSuggestedBulk();
newrec.ClientID = row.ClientID;
newrec.UserName = username;
newrec.ProductMasterID = row.ProductMasterID;
newrec.SupplyID = row.SUPPLYID;
newrec.ShelfLife = row.ShlfLife;
newrec.ProductSetupDate = row.ProductSetupDate;
newrec.DivisionID = row.DivisionID;
newrec.BulkCurrentAvailable = 0;
newrec.ShelfCurrentAvailable = 0;
newrec.BulkShippedPastYear = 0;
newrec.BulkShippedPerDay = 0;
newrec.ShelfShippedPastYear = 0;
newrec.ShelfShippedPerDay = 0;
newrec.UseThisExpirationDate = null;
newrec.AverageLeadTime = 0;
newrec.ReorderThis = false;
newrec.ReorderWeight = 0;
newrec.BulkOnOrder = false;
newrec.ProductMasterAge = (System.DateTime.Now.Date - row.ProductSetupDate.Value).Days;
// get the bulk containers and setup critieria
string[] sBulkStatus = { "QC", "TEST", "WASTE" };
var qBulk = (from t in db.tblBulk
where t.ProductMasterID == row.ProductMasterID
select t).ToList();
newrec.BulkCurrentAvailable = (from t in qBulk
where !sBulkStatus.Contains(t.BulkStatus)
select ((t.Qty == null ? 1 : t.Qty) * t.CurrentWeight)).Sum();
string[] sStockStatus = { "QC", "TEST", "WASTE" };
var qStock = (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
where pm.ProductMasterID == row.ProductMasterID
select new { t.ShelfStatus, t.QtyOnHand, sm.UnitWeight }).ToList();
newrec.ShelfCurrentAvailable = (from t in qStock
where !sStockStatus.Contains(t.ShelfStatus)
select (t.QtyOnHand * t.UnitWeight)).Sum();
newrec.CurrentAvailable = newrec.BulkCurrentAvailable + newrec.ShelfCurrentAvailable;
// Set BulkShippedPastYear=(Select sum(LogQty*LogAmount) from vwBulkTransForReplenishment where logdate>DateAdd(day,-365,getdate()) and ProductMasterID={0}.ProductMasterID)
// may not be working because missing BulkIDs in log
var qBulkLog = (from t in db.tblInvLog
join bl in db.tblBulk on t.BulkID equals bl.BulkID
where (bl.ProductMasterID == row.ProductMasterID) && (t.LogType == "BS-SHP")
select new { t.LogType, t.LogDate, t.LogQty, t.LogAmount, bl.BulkID, bl.BulkStatus, }).ToList();
qBulkLog = (from t in qBulkLog
where !sBulkStatus.Contains(t.BulkStatus)
select t).ToList();
qBulkLog = (from t in qBulkLog
where t.LogDate > DateTime.Now.AddDays(-365)
select t).ToList();
newrec.BulkShippedPastYear = (from t in qBulkLog
select (t.LogQty * t.LogAmount)).Sum();
if (newrec.BulkShippedPastYear > 0 && newrec.ProductMasterAge > 365)
{
newrec.BulkShippedPerDay = newrec.BulkShippedPastYear / 365;
}
if (newrec.BulkShippedPastYear > 0 && newrec.ProductMasterAge <= 365)
{
newrec.BulkShippedPerDay = newrec.BulkShippedPastYear / newrec.ProductMasterAge;
}
var qShelfLog = (from t in db.tblInvLog
join st in db.tblStock on t.StockID equals st.StockID
join sm in db.tblShelfMaster on st.ShelfID equals sm.ShelfID
join pd in db.tblProductDetail on sm.ProductDetailID equals pd.ProductDetailID
where (pd.ProductMasterID == row.ProductMasterID) && (t.LogType == "SS-SHP")
select new { t.LogType, t.LogDate, t.LogQty, t.LogAmount, t.Status }).ToList();
qShelfLog = (from t in qShelfLog
where !sStockStatus.Contains(t.Status)
select t).ToList();
qShelfLog = (from t in qShelfLog
where t.LogDate > DateTime.Now.AddDays(-365)
select t).ToList();
newrec.ShelfShippedPastYear = (from t in qShelfLog
select (t.LogQty * t.LogAmount)).Sum();
if (newrec.ShelfShippedPastYear > 0 && newrec.ProductMasterAge > 365)
{
newrec.ShelfShippedPerDay = newrec.ShelfShippedPastYear / 365;
}
if (newrec.ShelfShippedPastYear > 0 && newrec.ProductMasterAge <= 365)
{
newrec.ShelfShippedPerDay = newrec.ProductMasterAge / 365;
}
// Set BulkLatestExpirationDate=(Select top 1 ExpirationDate from tblBulk where ProductMasterID={0}.ProductMasterID order by ExpirationDate Desc)
// assumes always at least one bulk container
var qBulkExpiration = (from t in db.tblBulk
where t.ProductMasterID == row.ProductMasterID
orderby t.CeaseShipDate descending
select t).Take(1).FirstOrDefault();
// set initial value to null, then try to update it, if cannot update set to a future date (below)
if (qBulkExpiration != null && qBulkExpiration.CeaseShipDate != null)
{
newrec.BulkLatestExpirationDate = qBulkExpiration.CeaseShipDate;
newrec.BulkDaysTilExpiration = (newrec.BulkLatestExpirationDate.Value - System.DateTime.Now.Date).Days;
newrec.ShelfLatestExpirationDate = qBulkExpiration.CeaseShipDate;
newrec.ShelfDaysTilExpiration = (newrec.ShelfLatestExpirationDate.Value - System.DateTime.Now.Date).Days;
newrec.UseThisExpirationDate = newrec.BulkLatestExpirationDate;
}
if (newrec.UseThisExpirationDate == null)
{
newrec.UseThisExpirationDate = new DateTime(2099, 01, 01, 0, 0, 0);
}
newrec.UseThisDaysTilExpiration = (newrec.UseThisExpirationDate.Value - System.DateTime.Now.Date).Days;
if (newrec.UseThisDaysTilExpiration > 998)
{
newrec.UseThisDaysTilExpiration = 999;
}
// Set DaysSupplyLeft= CurrentAvailable / (ShelfShippedPerDay+BulkShippedPerDay) Where (ShelfShippedPerDay+BulkShippedPerDay>0)
if ((newrec.ShelfShippedPerDay + newrec.BulkShippedPerDay) > 0)
{
newrec.DaysSupplyLeft = Convert.ToInt32(newrec.CurrentAvailable / (newrec.ShelfShippedPerDay + newrec.BulkShippedPerDay));
}
// Set DaysSupplyLeft=0 Where DaysSupplyLeft Is Null
if (newrec.DaysSupplyLeft == null)
{
newrec.DaysSupplyLeft = 0;
}
var qBulkOrders = (from t in db.tblBulkOrderItem
where t.ProductMasterID == row.ProductMasterID && (t.Status == "OP" || t.Status == "OPEN")
select t.BulkOrderItemID).ToList();
if (qBulkOrders.Count() > 0)
{
newrec.BulkOnOrder = true;
}
else
{
newrec.BulkOnOrder = false;
}
if (newrec.DaysSupplyLeft < 65)
{
newrec.ReorderThis = true;
}
if (newrec.UseThisDaysTilExpiration < 65)
{
newrec.ReorderThis = true;
}
if (newrec.ShelfShippedPerDay + newrec.BulkShippedPerDay == 0)
{
newrec.ReorderThis = false;
}
if (newrec.BulkOnOrder == true)
{
newrec.ReorderThis = false;
}
// Set ReorderWeight=Round((ShelfShippedPerDay+BulkShippedPerDay) * 120,0) Where ShelfLife<13 and ReorderThis=1
if (newrec.ShelfLife < 13 && newrec.ReorderThis == true)
{
newrec.ReorderWeight = Convert.ToInt32((newrec.ShelfShippedPerDay + newrec.BulkShippedPerDay) * 120);
}
// Set ReorderWeight=Round((ShelfShippedPerDay+BulkShippedPerDay) * 180,0) Where (ShelfLife>=13 or ShelfLife is null) and (ReorderThis=1)
if (newrec.ShelfLife >= 13 || newrec.ShelfLife == null)
{
newrec.ReorderWeight = Convert.ToInt32((newrec.ShelfShippedPerDay + newrec.BulkShippedPerDay)) * 180;
}
// Set ReorderWeight=1 Where (ReorderWeight<1) and (ReorderThis=1)
if (newrec.ReorderWeight < 1 && newrec.ReorderThis == true)
{
newrec.ReorderWeight = 1;
}
db.tblSuggestedBulk.Add(newrec);
db.SaveChanges();
}
s = String.Format("Delete from {0} where ReorderThis=0 and Username='******'", fnTempTable, username);
db.Database.ExecuteSqlCommand(s);
itemscount = (from t in db.tblSuggestedBulk
where t.UserName == username
select t).Count();
return itemscount;
}
}
public static List<SelectListItem> fnClientsContacts(int id, string sContactType)
{
using (var db = new CMCSQL03Entities())
{
List<SelectListItem> mylist = new List<SelectListItem>();
mylist = (from t in db.tblClientContact
where t.ClientID == id && t.ContactType == sContactType
orderby t.FullName
select new SelectListItem { Value = t.ClientContactID.ToString(), Text = t.FullName }).ToList();
mylist.Insert(0, new SelectListItem { Value = "0", Text = "Select Supplier" });
return mylist;
}
}
public static List<SelectListItem> fnProductMasterIDs(int? clientid)
{
// 06/13/2016 This now is a list of PD-PN records
using (var db = new CMCSQL03Entities())
{
List<SelectListItem> mylist = new List<SelectListItem>();
mylist = (from t in db.tblProductMaster
where t.ClientID == clientid
orderby t.MasterCode
select new SelectListItem { Value = t.ProductMasterID.ToString(), Text = t.MasterCode + " - " + t.MasterName.Substring(0, 25) }).ToList();
mylist.Insert(0, new SelectListItem { Value = "0", Text = "Master Code" });
return mylist;
}
}
public static List<SelectListItem> fnListOfShelfMasterIDs(int? id)
{
using (var db = new CMCSQL03Entities())
{
List<SelectListItem> mylist = new List<SelectListItem>();
mylist = (from t in db.tblShelfMaster
join pd in db.tblProductDetail on t.ProductDetailID equals pd.ProductDetailID
where t.ProductDetailID == id
select new SelectListItem { Value = t.ShelfID.ToString(), Text = t.Size }).ToList();
mylist.Insert(0, new SelectListItem { Value = "", Text = "Select Size" });
return mylist;
}
}
private static List<SelectListItem> fnListOfSupplyIDs(int? clientid)
{
using (var db = new CMCSQL03Entities())
{
List<SelectListItem> mylist = new List<SelectListItem>();
mylist = (from t in db.tblProductMaster
where t.ClientID == clientid
orderby t.SUPPLYID
select
new SelectListItem { Value = t.SUPPLYID, Text = t.SUPPLYID }).Distinct().ToList();
mylist.Insert(0, new SelectListItem { Value = "0", Text = "" });
return mylist;
}
}
public static void fnSavePrePackStock(PrePackStock vm, FormCollection fc)
{
using (var db = new CMCSQL03Entities())
{
// for each sm, insert tblStock
vm.BulkContainer.bulkid = fnNewBulkID();
var dbBulk = db.tblBulk.Find(vm.BulkContainer.bulkid);
var dbPD = db.tblProductDetail.Find(vm.ProductDetailID);
dbBulk.ProductMasterID = dbPD.ProductMasterID;
dbBulk.Qty = 1;
dbBulk.ReceiveDate = vm.BulkContainer.receivedate;
dbBulk.LotNumber = vm.BulkContainer.lotnumber;
dbBulk.MfgDate = vm.BulkContainer.mfgdate;
dbBulk.ExpirationDate = vm.BulkContainer.expirationdate;
dbBulk.CeaseShipDate = vm.BulkContainer.ceaseshipdate;
dbBulk.QCDate = vm.BulkContainer.qcdate;
dbBulk.BulkStatus = vm.BulkContainer.bulkstatus;
dbBulk.Bin = vm.BulkContainer.bin;
dbBulk.Warehouse = vm.BulkContainer.warehouse;
dbBulk.CreateDate = System.DateTime.Now;
dbBulk.CreateUser = System.Web.HttpContext.Current.User.Identity.Name;
dbBulk.ReceiveWeight = 0;
dbBulk.CurrentWeight = 0;
for (int i = 1; i <= vm.ShelfMasterCount; i++)
{
// this will always have a value
string sThisShelfID = fc["Key" + i.ToString()];
Int32 ThisShelfID = Convert.ToInt32(sThisShelfID);
string sThisQty = fc["Value" + i.ToString()];
if (!String.IsNullOrEmpty(sThisQty))
{
Int32 ThisQty = Convert.ToInt32(sThisQty);
var newstock = new EF.tblStock();
newstock.ShelfID = ThisShelfID;
newstock.BulkID = vm.BulkContainer.bulkid;
newstock.Warehouse = vm.BulkContainer.warehouse;
newstock.QtyOnHand = ThisQty;
var sm = (from t in db.tblShelfMaster
where t.ShelfID == ThisShelfID
select t).FirstOrDefault(); // needed for default bin
dbBulk.ReceiveWeight = dbBulk.ReceiveWeight + (ThisQty * sm.UnitWeight);
newstock.Bin = sm.Bin;
newstock.ShelfStatus = "AVAIL";
newstock.CreateDate = DateTime.Now;
newstock.CreateUser = HttpContext.Current.User.Identity.Name;
db.tblStock.Add(newstock);
db.SaveChanges();
}
}
}
}
public static List<SelectListItem> fnEndUses(int id)
{
using (var db = new CMCSQL03Entities())
{
List<SelectListItem> mylist = new List<SelectListItem>();
mylist = (from t in db.tblEndUse
where t.ClientID == id
orderby t.EndUse
select new SelectListItem { Value = t.EndUseID.ToString(), Text = t.EndUse }).ToList();
mylist.Insert(0, new SelectListItem { Value = "0", Text = "Select End Use" });
return mylist;
}
}
public static Inventory fnFillInventoryVM(int id)
{
using (var db = new CMCSQL03Entities())
{
Inventory vm = new Inventory();
ProductProfile PP = new ProductProfile();
PP.productdetailid = id;
vm.PP = ProductsService.FillFromPD(PP);
PP = ProductsService.FillFromPM(PP);
PP = ProductsService.fnFillOtherPMProps(PP);
//vm.vmMasterNotesAlert = PP.masternotesalert; // cannot get View to properly handle this when buried in PP
vm.ClientCode = (from t in db.tblClient
where t.ClientID == PP.clientid
select t.ClientCode).FirstOrDefault();
vm.ClientUM = (from t in db.tblClient
where t.ClientID == id
select t.ClientUM).FirstOrDefault();
vm.Division = (from t in db.tblDivision
where t.DivisionID == PP.divisionid
select t.Division).FirstOrDefault();
var q = (from t in db.tblBulkOrderItem
where t.ProductMasterID == id && t.Status == "OP"
select t).FirstOrDefault();
vm.BackOrderPending = q == null ? false : true;
var q1 = (from t in db.tblBulkOrderItem
where t.ProductMasterID == id && t.Status == "OP"
select new { tot = t.Qty * t.Weight }).ToList();
var q2 = (from x in q1 select x.tot).Sum();
vm.BulkWeightCurrentlyOnOrder = Convert.ToDecimal(q2);
vm.ShelfLevelTotal = StatusLevelShelf(id, "TOTAL");
vm.ShelfLevelAvail = StatusLevelShelf(id, "AVAIL");
vm.ShelfLevelTest = StatusLevelShelf(id, "TEST");
vm.ShelfLevelHold = StatusLevelShelf(id, "HOLD");
vm.ShelfLevelQC = StatusLevelShelf(id, "QC");
vm.ShelfLevelReturn = StatusLevelShelf(id, "RETURN");
vm.ShelfLevelRecd = StatusLevelShelf(id, "RECD");
vm.ShelfLevelOther = StatusLevelShelf(id, "OTHER");
vm.BulkLevelTotal = StatusLevelBulk(id, "TOTAL");
vm.BulkLevelAvail = StatusLevelBulk(id, "AVAIL");
vm.BulkLevelTest = StatusLevelBulk(id, "TEST");
vm.BulkLevelHold = StatusLevelBulk(id, "HOLD");
vm.BulkLevelQC = StatusLevelBulk(id, "QC");
vm.BulkLevelReturn = StatusLevelBulk(id, "RETURN");
vm.BulkLevelRecd = StatusLevelBulk(id, "RECD");
vm.BulkLevelOther = StatusLevelBulk(id, "OTHER");
vm.TotalLevelTotal = StatusLevelTotal(id, "TOTAL");
vm.TotalLevelAvail = StatusLevelTotal(id, "AVAIL");
vm.TotalLevelTest = StatusLevelTotal(id, "TEST");
vm.TotalLevelHold = StatusLevelTotal(id, "HOLD");
vm.TotalLevelQC = StatusLevelTotal(id, "QC");
vm.TotalLevelReturn = StatusLevelTotal(id, "RETURN");
vm.TotalLevelReturn = StatusLevelTotal(id, "RECD");
vm.TotalLevelOther = StatusLevelTotal(id, "OTHER");
return vm;
}
}
// ************** TODO ***************************
// Finish adding all properties to next 2
// ************** TODO ***************************
public static BulkContainerViewModel fnNewBulkContainer(int id)
{
// id=productmasterid
BulkContainerViewModel obj = new BulkContainerViewModel();
using (var db = new CMCSQL03Entities())
{
obj.isknownmaterial = true;
var x = (from t in db.tblBulk where t.ProductMasterID == id select t).ToList();
obj.pm_sumofcurrentweight = 0;
foreach (var row in x)
{
obj.pm_sumofcurrentweight = obj.pm_sumofcurrentweight + row.CurrentWeight;
}
var dbPM = db.tblProductMaster.Find(id);
// assign ProductMaster fields for R/O
obj.pm_MasterNotes = dbPM.MasterNotes;
obj.pm_HandlingOther = dbPM.HandlingOther;
obj.pm_OtherHandlingInstr = dbPM.OtherHandlingInstr;
obj.pm_refrigerate = dbPM.Refrigerate;
obj.pm_flammablestorageroom = dbPM.FlammableStorageRoom;
obj.pm_freezablelist = dbPM.FreezableList;
obj.pm_refrigeratedlist = dbPM.RefrigeratedList;
//var qCL = (from t in db.tblClient where t.ClientID == qPM.ClientID select t).FirstOrDefault();
var dbClient = db.tblClient.Find(dbPM.ClientID);
obj.bulkid = -1; // for insert later
obj.clientid = dbClient.ClientID;
obj.warehouse = dbClient.CMCLocation;
obj.clientname = dbClient.ClientName;
obj.logofilename = dbClient.LogoFileName;
obj.productmasterid = id;
obj.receivedate = DateTime.Now;
obj.bulkstatus = "RECD";
obj.ListOfWareHouses = fnWarehouseIDs();
obj.ListOfProductMasters = fnProductMasterIDs(obj.clientid, id);
obj.ListOfBulkStatusIDs = fnBulkStatusIDs();
obj.ListOfContainerTypeIDs = fnContainerTypeIDs();
obj.ListOfCarriers = fnCarriers();
// R/O fields from PM
obj.MasterCode = dbPM.MasterCode;
obj.MasterName = dbPM.MasterName;
obj.flammable = dbPM.Flammable;
obj.freezer = dbPM.FREEZERSTORAGE;
obj.refrigerated = dbPM.Refrigerate;
obj.packout = dbPM.PackOutOnReceipt;
return obj;
}
}
