public static double CachedAMC(int itemID, int storeID)
{
StockoutEntities context= new StockoutEntities();
var amc = ((from v in context.AmcReports
where v.ItemID == itemID && v.StoreID == storeID
select v).FirstOrDefault() ?? new AmcReport()).AmcWithDOS;
return amc;
}
public static void BuildIndex(int itemID)
{
var db = new StockoutEntities();
var repository = new StockoutRepository();
foreach (var storeId in db.Stores.Select(s => s.ID))
{
var stockouts = GetStockOutHistory(itemID, storeId);
if (stockouts.Count > 0)
repository.AddRange(stockouts);
}
}
static List<Transaction> TransactionDates(int itemID,int storeID)
{
var db = new StockoutEntities();
List<Transaction> dates = new List<Transaction>();
dates.AddRange((from receipt in db.ReceiveDocs.Where(m => m.ItemID == itemID && m.StoreID==storeID).OrderBy(m => m.Date) select new Transaction { TransactionID = receipt.ID, Date = receipt.Date, TransactionTypeCode = (int)TransactionType.Receipt }).ToList()); // Receipts
dates.AddRange((from issue in db.IssueDocs.Where(m => m.ItemID == itemID && m.StoreID ==storeID).OrderBy(m => m.Date) select new Transaction { TransactionID = issue.ID, Date = issue.Date, TransactionTypeCode = (int)TransactionType.Issue }).ToList()); // Issues
dates.AddRange((from disposal in db.Disposals.Where(m => m.ItemID == itemID && m.StoreID ==storeID && (bool) m.IsLoss).OrderBy(m => m.Date) select new Transaction { TransactionID = disposal.ID, Date = disposal.Date, TransactionTypeCode = (int)TransactionType.Disposal }).ToList()); // Disposals
dates.AddRange((from disposal in db.Disposals.Where(m => m.ItemID == itemID && m.StoreID == storeID && !(bool)m.IsLoss).OrderBy(m => m.Date) select new Transaction { TransactionID = disposal.ID, Date = disposal.Date, TransactionTypeCode = (int)TransactionType.Adjustment }).ToList()); // Adjustments
return dates.OrderBy(m => m.Date).ToList();
}
static List<Transaction> ReceiptsByItem(int itemID ,int storeID)
{
var db = new StockoutEntities();
var receipts = from receipt in db.ReceiveDocs
where receipt.ItemID == itemID && receipt.StoreID == storeID
select new Transaction { TransactionID = receipt.ID, TransactionTypeCode = (int)TransactionType.Receipt, Date = receipt.Date, Quantity = receipt.Quantity };
return receipts.ToList();
}
static List<Transaction> IssuesByItem(int itemID, int storeID)
{
var db = new StockoutEntities();
var issues = from issue in db.IssueDocs
where issue.ItemID == itemID && issue.StoreID == storeID
select new Transaction { TransactionID = issue.ID, TransactionTypeCode = (int)TransactionType.Issue, Date = issue.Date, Quantity = issue.Quantity };
return issues.OrderBy(m => m.Date).ToList();
}
static List<Transaction> DisposalsByItem(int itemID ,int storeID)
{
var db = new StockoutEntities();
var receipts = from disposal in db.Disposals
where disposal.ItemID == itemID && disposal.StoreID ==storeID && (bool)disposal.IsLoss
select new Transaction { TransactionID = disposal.ID, TransactionTypeCode = (int)TransactionType.Disposal, Date = disposal.Date, Quantity = disposal.Quantity };
return receipts.ToList();
}
public static void RefreshAMCValues(int storeId, Dictionary<int,long> items,int unitId)
{
var context = new StockoutEntities();
var genaralinfo = context.GenralInfos.First();
var endDate = DateTime.Now;
var startDate = endDate.Subtract(TimeSpan.FromDays(genaralinfo.AMCRange * 30));
try
{
foreach (var row in items)
{
if (row.Value == 0)
{
var stockOut = new Stockout()
{
ItemID = row.Key,
StoreID = storeId,
StartDate = DateTime.Now,
EndDate = null,
LastIndexedTime = DateTime.Now
};
context.Stockouts.Add(stockOut);
context.SaveChanges();
}
var allItemIds = context.AmcReports.SingleOrDefault(m => m.ItemID == row.Key && m.StoreID == storeId && m.UnitID ==unitId);
if(allItemIds==null)
{
var amcreport = new AmcReport
{
ItemID = row.Key,
StoreID = storeId,
AmcRange = genaralinfo.AMCRange,
IssueInAmcRange =
CalculateTotalConsumptionWithoutDOS(row.Key, storeId, startDate,
DateTime.Today),
DaysOutOfStock =
CalculateStockoutDays(row.Key, storeId, startDate, endDate),
AmcWithDOS =
CalculateAverageConsumption(row.Key, storeId, startDate,endDate,CalculationOptions.Monthly),
AmcWithOutDOS = CalculateTotalConsumptionWithoutDOS(row.Key, storeId, startDate, endDate) / Convert.ToDouble(genaralinfo.AMCRange),
LastIndexedTime = DateTime.Now,
IssueWithDOS =Builder.CalculateTotalConsumption(row.Key, storeId, startDate, DateTime.Now),
UnitID = unitId
};
context.AmcReports.Add(amcreport);
}
// Update AMC value
else
{
allItemIds.IssueInAmcRange = CalculateTotalConsumptionWithoutDOS(row.Key, storeId, startDate,
DateTime.Now);
allItemIds.DaysOutOfStock = CalculateStockoutDays(row.Key, storeId, startDate, DateTime.Now);
allItemIds.AmcWithDOS = CalculateAverageConsumption(row.Key, storeId, startDate, endDate,CalculationOptions.Monthly);
allItemIds.AmcWithOutDOS =CalculateTotalConsumptionWithoutDOS(row.Key, storeId, startDate, endDate)/Convert.ToDouble(genaralinfo.AMCRange);
allItemIds.IssueWithDOS = Builder.CalculateTotalConsumption(row.Key, storeId, startDate, DateTime.Now);
allItemIds.LastIndexedTime = DateTime.Now;
allItemIds.UnitID = unitId;
}
context.SaveChanges();
}
}
catch (DbUpdateException ex)
{
throw;
}
}
public static long CalculateTotalConsumptionWithoutDOS(int itemId,int storeId, DateTime startDate, DateTime endDate)
{
var db = new StockoutEntities();
var allIssues = db.IssueDocs.Where(m => m.ItemID == itemId && m.StoreID == storeId).Where(issue => issue.Date >= startDate && issue.Date < endDate);
return Enumerable.Sum(allIssues, issue => issue.Quantity);
}
public static long CalculateTotalConsumptionForMOS(int itemId, int storeId, DateTime startDate, DateTime endDate)
{
var db = new StockoutEntities();
var stockoutDays = CalculateStockoutDays(itemId, storeId, startDate, endDate);
var allIssues = db.IssueDocs.Where(m => m.ItemID == itemId && m.StoreID == storeId || m.Date >= startDate && m.Date < endDate);//.Where(m => m.Date >= startDate && m.Date < endDate);
//var allIssues = db.IssueDocs.Where(m => m.ItemID == itemId && m.StoreID == storeId && m.Date >= startDate && m.Date < endDate);//.Where(m => m.Date >= startDate && m.Date < endDate);
if (!allIssues.Any())
return 0;
var totalConsumption = Enumerable.Sum(allIssues, issue => issue.Quantity);
if (stockoutDays == 0)
return totalConsumption;
return totalConsumption + CalculateTotalConsumption(itemId, storeId, startDate.Subtract(TimeSpan.FromDays(stockoutDays)), startDate); ;
}
public static long CalculateTotalConsumptionAMC(int itemId, int storeId, DateTime startDate, DateTime endDate, int amcrange = 0, int stockoutDays = 0)
{
var db = new StockoutEntities();
if (amcrange == 0)
{
amcrange = db.GenralInfos.First().AMCRange;
}
var amcdays = amcrange * 30;
if (stockoutDays == 0)
{
stockoutDays = CalculateStockoutDays(itemId, storeId, startDate, endDate);
}
var allIssues = db.IssueDocs.Where(m => m.ItemID == itemId && m.StoreID == storeId).Where(issue => issue.Date >= startDate && issue.Date < endDate);
if (!allIssues.Any())
return 0;
var totalConsumption = Enumerable.Sum(allIssues, issue => issue.Quantity) * amcdays;
var daydiff = amcdays - stockoutDays;
if (daydiff != 0)
{
return totalConsumption / daydiff;
}
return 0;
//return 1;
}
public static long CalculateTotalConsumption(int itemId,int storeId, DateTime startDate, DateTime endDate)
{
var db = new StockoutEntities();
var amcrange = db.GenralInfos.First();
var amcdays = amcrange.AMCRange*30;
var stockoutDays = CalculateStockoutDays(itemId, storeId, startDate, endDate);
var allIssues =db.IssueDocs.Where(m => m.ItemID == itemId && m.StoreID == storeId).Where(issue => issue.Date >= startDate && issue.Date < endDate);
if (!allIssues.Any())
return 0;
var totalConsumption = Enumerable.Sum(allIssues, issue => issue.Quantity);
if (stockoutDays == 0)
return totalConsumption;
return (totalConsumption + CalculateTotalConsumption(itemId, storeId, startDate.Subtract(TimeSpan.FromDays(stockoutDays)),startDate));
}
