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 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 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);
}
static List <Transaction> AdjustmentsByItem(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.Adjustment, Date = disposal.Date, Quantity = disposal.Quantity
};
return(receipts.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());
}
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);
}
}
}
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));;
}
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());
}
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;
}
}
