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