static void Main(string[] args)
{
foreach (string filepath in System.IO.Directory.EnumerateFiles(@"C:\Users\wildbillcat\Sync\WebSpecs\SKUs", "*.csv"))
{
using (var csv = new CsvReader(System.IO.File.OpenText(filepath)))
{
string[] datePieces = filepath.Split('\\').Last().Split('.')[0].Split('-');
DateTime SalesDate = new DateTime(int.Parse(datePieces[2]), int.Parse(datePieces[0]), int.Parse(datePieces[1]));
SalesDate.AddDays(-1 * (SalesDate.Day - 1));//Ensures Day of the first of the month
int MonthID = (12 * SalesDate.Year) + SalesDate.Month;
while (csv.Read())
{
using (var ctx = new InventoryForcast.Models.ApplicationDbContext())
{
int _sku = csv.GetField <int>(0);
System.Console.WriteLine(string.Concat("SKU: ", _sku, " Date: ", SalesDate));
int _qty_sold = csv.GetField <int>(1);
ctx.MonthlyTotals.Add(new MonthlyTotal()
{
SKU = _sku,
Quantity_Sold = _qty_sold,
Absolute_Quantity_Sold = MonthlyTotal.RemoveSeasonality(_qty_sold, SalesDate.Month),
Date = SalesDate,
Month_Id = MonthID
}
);
ctx.SaveChanges();
}
}
}
}
}
public IHttpActionResult PostMonthlyTotal(MonthlyTotal monthlyTotal)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
//Ensure that absolute value has been calculated
monthlyTotal.Absolute_Quantity_Sold = MonthlyTotal.RemoveSeasonality(monthlyTotal.Quantity_Sold, monthlyTotal.Date.Month);
db.MonthlyTotals.Add(monthlyTotal);
try
{
db.SaveChanges();
}
catch (DbUpdateException)
{
if (MonthlyTotalExists(monthlyTotal.SKU))
{
return(Conflict());
}
else
{
throw;
}
}
return(CreatedAtRoute("DefaultApi", new { id = monthlyTotal.SKU }, monthlyTotal));
}
public ActionResult ImportCommit(string CsvFile, DateTime CsvDate)
{
string saveAsDirectory = string.Concat(AppDomain.CurrentDomain.GetData("DataDirectory"), "\\csvimport");
if (System.IO.File.Exists(string.Concat(saveAsDirectory, "\\", CsvFile)))
{
DateTime SalesDate = CsvDate.Date;
SalesDate.AddDays(-1 * (SalesDate.Day - 1));//Ensures Day of the first of the month
int MonthID = 12 * SalesDate.Month * SalesDate.Year;
using (var csv = new CsvReader(System.IO.File.OpenText(string.Concat(saveAsDirectory, "\\", CsvFile))))
{
while (csv.Read())
{
ApplicationDbContext ctx = new ApplicationDbContext();
int _qty_sold = csv.GetField <int>(1);
ctx.MonthlyTotals.Add(new MonthlyTotal()
{
SKU = csv.GetField <int>(0),
Quantity_Sold = _qty_sold,
Absolute_Quantity_Sold = MonthlyTotal.RemoveSeasonality(_qty_sold, SalesDate.Month),
Date = SalesDate,
Month_Id = MonthID
}
);
ctx.SaveChanges();
}
}
}
return(RedirectToAction("Index"));
}
public IHttpActionResult PutMonthlyTotal(int id, MonthlyTotal monthlyTotal)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
if (id != monthlyTotal.SKU)
{
return(BadRequest());
}
db.Entry(monthlyTotal).State = EntityState.Modified;
try
{
db.SaveChanges();
}
catch (DbUpdateConcurrencyException)
{
if (!MonthlyTotalExists(id))
{
return(NotFound());
}
else
{
throw;
}
}
return(StatusCode(HttpStatusCode.NoContent));
}
public ActionResult DeleteConfirmed(int id)
{
MonthlyTotal monthlyTotal = db.MonthlyTotals.Find(id);
db.MonthlyTotals.Remove(monthlyTotal);
db.SaveChanges();
return(RedirectToAction("Index"));
}
public dashboard()
{
this.InitializeComponent();
MonthlyTotal monthlyTotal = FinAppService.Service.Instance.GetMonthlyTotal(DateTime.Now.Date);
this.TxtBlockTotalMonthlyExpenses.Text = monthlyTotal.MonthlyExpenses.ToString("C");
this.TxtBlockTotalMonthlyIncome.Text = monthlyTotal.MontlyIncome.ToString("C");
}
public ActionResult Edit([Bind(Include = "SKU,Month_Id,Quantity_Sold,Absolute_Quantity_Sold,Date")] MonthlyTotal monthlyTotal)
{
if (ModelState.IsValid)
{
db.Entry(monthlyTotal).State = EntityState.Modified;
db.SaveChanges();
return(RedirectToAction("Index"));
}
return(View(monthlyTotal));
}
public ActionResult Create([Bind(Include = "SKU,Month_Id,Quantity_Sold,Absolute_Quantity_Sold,Date")] MonthlyTotal monthlyTotal)
{
if (ModelState.IsValid)
{
db.MonthlyTotals.Add(monthlyTotal);
db.SaveChanges();
return(RedirectToAction("Index"));
}
return(View(monthlyTotal));
}
public IHttpActionResult GetMonthlyTotal(int id)
{
MonthlyTotal monthlyTotal = db.MonthlyTotals.Find(id);
if (monthlyTotal == null)
{
return(NotFound());
}
return(Ok(monthlyTotal));
}
// GET: MonthlyTotals/Details/5
public ActionResult Details(int?id)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
MonthlyTotal monthlyTotal = db.MonthlyTotals.Find(id);
if (monthlyTotal == null)
{
return(HttpNotFound());
}
return(View(monthlyTotal));
}
public IHttpActionResult DeleteMonthlyTotal(int id)
{
MonthlyTotal monthlyTotal = db.MonthlyTotals.Find(id);
if (monthlyTotal == null)
{
return(NotFound());
}
db.MonthlyTotals.Remove(monthlyTotal);
db.SaveChanges();
return(Ok(monthlyTotal));
}
//NewLoan ReadData(SqlDataReader reader) {
// NewLoan x = new NewLoan();
// x.id = reader.GetValue(0) == DBNull.Value ? null : reader.GetString(0);
// x.user = new User.NewUser();
// x.user.id = reader.GetValue(1) == DBNull.Value ? null : reader.GetString(1);
// x.loan = reader.GetValue(2) == DBNull.Value ? 0 : Convert.ToDouble(reader.GetString(2));
// x.loanDate = reader.GetValue(3) == DBNull.Value ? null : reader.GetString(3);
// x.repayment = reader.GetValue(4) == DBNull.Value ? 0 : Convert.ToDouble(reader.GetString(4));
// x.manipulativeCosts = reader.GetValue(5) == DBNull.Value ? 0 : Convert.ToDouble(reader.GetString(5));
// x.manipulativeCostsCoeff = s.Data().manipulativeCostsCoeff;
// x.actualLoan = x.loan - x.manipulativeCosts;
// x.withdraw = reader.GetValue(6) == DBNull.Value ? 0 : Convert.ToDouble(reader.GetString(6));
// x.lastLoanToRepaid = x.actualLoan - x.withdraw;
// User u = new User();
// x.restToRepayment = GetTotalLoan(x) - GetTotalLoanRepayed(x); // TOOD: provjeriti
// x.dedline = reader.GetValue(7) == DBNull.Value ? s.Data().defaultDedline : Convert.ToDouble(reader.GetString(7));
// x.isRepaid = reader.GetValue(8) == DBNull.Value ? 0 : reader.GetInt32(8);
// x.note = reader.GetValue(9) == DBNull.Value ? null : reader.GetString(9);
// x.user.firstName = reader.GetValue(10) == DBNull.Value ? null : reader.GetString(10);
// x.user.lastName = reader.GetValue(11) == DBNull.Value ? null : reader.GetString(11);
// x.user.buisinessUnit = new BuisinessUnit.NewUnit();
// x.user.buisinessUnit.code = reader.GetValue(12) == DBNull.Value ? null : reader.GetString(12);
// x.user.buisinessUnit.title = reader.GetValue(13) == DBNull.Value ? null : reader.GetString(13);
// return x;
//}
private List <MonthlyTotal> GetMonthlyTotal(List <NewLoan> data)
{
List <MonthlyTotal> xx = new List <MonthlyTotal>();
for (int i = 1; i <= 12; i++)
{
MonthlyTotal x = new MonthlyTotal();
x.month = g.Month(i);
var aa = data.Where(a => a.loanDate.Substring(5, 2) == x.month);
x.total = new Total();
x.total.loan = aa.Sum(a => a.loan);
x.total.repayment = aa.Sum(a => a.repayment);
x.total.manipulativeCosts = aa.Sum(a => a.manipulativeCosts);
x.total.actualLoan = aa.Sum(a => a.actualLoan);
x.total.withdraw = aa.Sum(a => a.withdraw);
x.total.lastLoanToRepaid = aa.Sum(a => a.lastLoanToRepaid);
x.total.restToRepayment = aa.Sum(a => a.loan) - aa.Sum(a => a.repayment); // aa.Sum(a => a.restToRepayment);
xx.Add(x);
}
return(xx);
}
public TotalsWindow(DateTime week)
{
Week = week;
InitializeComponent();
Dispatcher.BeginInvoke(DispatcherPriority.Background, new ThreadStart(() =>
{
GatherTotals();
TotalHoursLabel.Inlines.Clear();
TotalHoursLabel.Inlines.Add("Hours charged in ");
TotalHoursLabel.Inlines.Add(Week.ToString("MMMM"));
TotalHoursLabel.Inlines.Add(": ");
TotalHoursLabel.Inlines.Add(new Bold(new Run(MonthlyTotal.ToString(CultureInfo.CurrentCulture)))
{
FontSize = 16
});
TotalHoursLabel.Inlines.Add(" / ");
TotalHoursLabel.Inlines.Add(new Bold(new Run(TotalHoursInMonth.ToString(CultureInfo.CurrentCulture)))
{
FontSize = 14
});
}));
}
public static void GenerateSingleLinearForcast(int id)
{
ApplicationDbContext db = new ApplicationDbContext();
MonthlyTotal MT = db.MonthlyTotals.Where(p => p.SKU == id).OrderBy(g => g.Date).ToArray().Last();
DateTime ForcastDate = MT.Date.AddMonths(1);
int forcast_month_id = (12 * ForcastDate.Year) + ForcastDate.Month;
List <MonthlyTotal> Totals = db.MonthlyTotals.Where(P => P.SKU == id && P.Date < ForcastDate).OrderByDescending(O => O.Date).Take(12).ToList();
List <LinearDataSet> DS = new List <LinearDataSet>();
double Intercept = 0;
double Slope = 0;
double Absolute_Quantity_Forcast = 0;
double Quantity_Forcast = 0;
string JSON_MonthlyTotals = JsonConvert.SerializeObject(DS.ToArray());
int Sample_Size = Totals.Count();
string SkuClass = "Unknown";
bool Valid = false;
if (Sample_Size > 3)
{
Valid = true;
double[] YTotals = Totals.Select(P => P.Absolute_Quantity_Sold).ToArray();
double[] YTotalsR = Totals.Select(P => P.Quantity_Sold).ToArray();
double[] XMonth = Totals.Select(P => (double)(P.Date.Year * 12) + P.Date.Month).ToArray();
double[] t = Linear.Forcast(forcast_month_id, XMonth, YTotals);
DS.Add(new LinearDataSet()
{
label = "Actual Sales", y = YTotalsR, x = XMonth
});
DS.Add(new LinearDataSet()
{
label = "Adjusted Sales", y = YTotals, x = XMonth
});
List <double> TrendYVals = new List <double>();
List <double> TrendYValsSeasonal = new List <double>();
foreach (double x in XMonth)
{
double val = x * t[1] + t[0];
TrendYVals.Add(val);
}
DS.Add(new LinearDataSet()
{
label = "Trend Sales",
y = TrendYVals.ToArray(),
x = XMonth
});
JSON_MonthlyTotals = JsonConvert.SerializeObject(DS.ToArray());
Intercept = t[0];
Slope = t[1];
Absolute_Quantity_Forcast = t[2];
Quantity_Forcast = MonthlyTotal.AddSeasonality(Absolute_Quantity_Forcast, ForcastDate.Month);
SkuClass = SingleLinearForcast.GetSkuClass((int)Totals.Select(P => P.Quantity_Sold).ToArray().Sum());
}
SingleLinearForcast Forcast = new SingleLinearForcast()
{
SKU = id,
Month_Id = (12 * ForcastDate.Year) + ForcastDate.Month, //Month * Year
Date = ForcastDate, //Forcasted Date
Quantity_Forcast = Quantity_Forcast,
Absolute_Quantity_Forcast = Absolute_Quantity_Forcast,
Slope = Slope,
Intercept = Intercept,
JSON_MonthlyTotals = JSON_MonthlyTotals,
Sample_Size = Sample_Size,
SkuClass = SkuClass,
Valid = Valid,
};
db.SingleLinearForcasts.Add(Forcast);
db.SaveChanges();
}
public async Task CalculateStatistics()
{
// todo loop all users.
var allUsersQuery = DocumentDbBase.DocumentStore.GetItems <ApplicationUser>(x => x.Type == DocumentDbType.User);
var allUsers = allUsersQuery.ToList();
foreach (var applicationUser in allUsers)
{
//start with a clean slate for each user
applicationUser.Statistics = new Statistics();
var userOrderHistoriesQuery = DocumentDbBase.DocumentStore.GetItems <UserOrderHistory>(x => x.UserId == applicationUser.UserId && x.Type == DocumentDbType.UserHistory);
var userOrderHistories = userOrderHistoriesQuery.ToList();
foreach (var userOrderHistory in userOrderHistories)
{
var monthlyTotal = applicationUser.Statistics.MonthlyTotals.FirstOrDefault(x => x.MonthDate == x.ParseMonth(userOrderHistory.OrderTime));
if (monthlyTotal == null)
{
monthlyTotal = new MonthlyTotal(userOrderHistory.OrderTime);
applicationUser.Statistics.MonthlyTotals.Add(monthlyTotal);
}
var yearlyTotal = applicationUser.Statistics.YearlyTotals.FirstOrDefault(x => x.YearDate == x.ParseYear(userOrderHistory.OrderTime));
if (yearlyTotal == null)
{
yearlyTotal = new YearlyTotal(userOrderHistory.OrderTime);
applicationUser.Statistics.YearlyTotals.Add(yearlyTotal);
}
var weeklyTotal = applicationUser.Statistics.WeeklyTotals.FirstOrDefault(x => x.WeekDate == x.ParseWeek(userOrderHistory.OrderTime));
if (weeklyTotal == null)
{
weeklyTotal = new WeeklyTotal(userOrderHistory.OrderTime);
applicationUser.Statistics.WeeklyTotals.Add(weeklyTotal);
}
var dailyTotal = applicationUser.Statistics.DayTotals.FirstOrDefault(x => x.DayDate == x.ParseDay(userOrderHistory.OrderTime));
if (dailyTotal == null)
{
dailyTotal = new DayTotal(userOrderHistory.OrderTime);
applicationUser.Statistics.DayTotals.Add(dailyTotal);
}
foreach (var entry in userOrderHistory.Entries)
{
if (_pastaEntriesList.Contains(entry.Id))
{
yearlyTotal.PastaOrderCount += 1;
}
if (_healtyEntriesList.Contains(entry.Id))
{
weeklyTotal.HealthyOrderCount += 1;
}
yearlyTotal.OrderCount += 1;
yearlyTotal.Amount += entry.Price;
weeklyTotal.OrderCount += 1;
weeklyTotal.Amount += entry.Price;
monthlyTotal.OrderCount += 1;
monthlyTotal.Amount += entry.Price;
dailyTotal.Amount += entry.Price;
dailyTotal.OrderCount += 1;
// calculate badges
DateTime orderTime = DateTime.Parse("01/01/2001 10:00:00");
if (userOrderHistory.OrderTime < DateTime.Parse("12/06/2017 10:00:00"))
{
orderTime = DateTime.Parse("01/01/2001 9:00:00");
}
BadgeService.ExtractOrderBadges(applicationUser, userOrderHistory, orderTime);
}
applicationUser.Statistics.AppTotalSpend += userOrderHistory.FinalPrice;
await DocumentDbBase.DocumentStore.UpsertDocument(userOrderHistory);
}
var userPrepaysQuery = DocumentDbBase.DocumentStore.GetItems <UserBalanceAudit>(x => x.UserId == applicationUser.UserId && x.Type == DocumentDbType.UserBalanceAudit);
var userPrepays = userPrepaysQuery.ToList();
foreach (var prepay in userPrepays)
{
foreach (var audit in prepay.Audits)
{
// there might be a difference in prepayedTotal as in first versions this was not been audited.
applicationUser.Statistics.PrepayedTotal += audit.Amount;
}
}
// remove historical data
var daysToDelete = applicationUser.Statistics.DayTotals.Where(x => x.DayDate != x.ParseDay(DateTime.UtcNow)).ToList();
foreach (var dayToDelete in daysToDelete)
{
applicationUser.Statistics.DayTotals.Remove(dayToDelete);
}
var weeksToDelete = applicationUser.Statistics.WeeklyTotals.Where(x => x.WeekDate != x.ParseWeek(DateTime.UtcNow)).ToList();
foreach (var weekToDelete in weeksToDelete)
{
applicationUser.Statistics.WeeklyTotals.Remove(weekToDelete);
}
var monthsToDelete = applicationUser.Statistics.MonthlyTotals.Where(x => x.MonthDate != x.ParseMonth(DateTime.UtcNow)).ToList();
foreach (var monthToDelete in monthsToDelete)
{
applicationUser.Statistics.MonthlyTotals.Remove(monthToDelete);
}
var yearsToDelete = applicationUser.Statistics.YearlyTotals.Where(x => x.YearDate != x.ParseYear(DateTime.UtcNow)).ToList();
foreach (var yearToDelete in yearsToDelete)
{
applicationUser.Statistics.YearlyTotals.Remove(yearToDelete);
}
// save document
await DocumentDbBase.DocumentStore.UpsertDocument(applicationUser);
}
}
private void CalculateStatistics(ApplicationUser applicationUser, Domain.Entities.DocumentDb.UserOrderHistory userOrderHistory)
{
if (!userOrderHistory.StatisticsProcessed)
{
var monthlyTotal =
applicationUser.Statistics.MonthlyTotals.FirstOrDefault(x =>
x.MonthDate == x.ParseMonth(userOrderHistory.OrderTime));
if (monthlyTotal == null)
{
monthlyTotal = new MonthlyTotal(userOrderHistory.OrderTime);
applicationUser.Statistics.MonthlyTotals.Add(monthlyTotal);
}
var yearlyTotal =
applicationUser.Statistics.YearlyTotals.FirstOrDefault(x =>
x.YearDate == x.ParseYear(userOrderHistory.OrderTime));
if (yearlyTotal == null)
{
yearlyTotal = new YearlyTotal(userOrderHistory.OrderTime);
applicationUser.Statistics.YearlyTotals.Add(yearlyTotal);
}
var weeklyTotal =
applicationUser.Statistics.WeeklyTotals.FirstOrDefault(x =>
x.WeekDate == x.ParseWeek(userOrderHistory.OrderTime));
if (weeklyTotal == null)
{
weeklyTotal = new WeeklyTotal(userOrderHistory.OrderTime);
applicationUser.Statistics.WeeklyTotals.Add(weeklyTotal);
}
var dailyTotal =
applicationUser.Statistics.DayTotals.FirstOrDefault(x =>
x.DayDate == x.ParseDay(userOrderHistory.OrderTime));
if (dailyTotal == null)
{
dailyTotal = new DayTotal(userOrderHistory.OrderTime);
applicationUser.Statistics.DayTotals.Add(dailyTotal);
}
foreach (var entry in userOrderHistory.Entries)
{
if (entry.Pasta)
{
yearlyTotal.PastaOrderCount += 1;
}
if (entry.Healthy)
{
weeklyTotal.HealthyOrderCount += 1;
}
yearlyTotal.OrderCount += 1;
yearlyTotal.Amount += entry.FinalPrice;
weeklyTotal.OrderCount += 1;
weeklyTotal.Amount += entry.FinalPrice;
monthlyTotal.OrderCount += 1;
monthlyTotal.Amount += entry.FinalPrice;
dailyTotal.Amount += entry.FinalPrice;
dailyTotal.OrderCount += 1;
}
applicationUser.Statistics.AppTotalSpend += userOrderHistory.FinalPrice;
userOrderHistory.StatisticsProcessed = true;
// cleanup old data
var daysToDelete = applicationUser.Statistics.DayTotals.Where(x => x.DayDate != x.ParseDay(DateTime.UtcNow)).ToList();
foreach (var dayToDelete in daysToDelete)
{
applicationUser.Statistics.DayTotals.Remove(dayToDelete);
}
var weeksToDelete = applicationUser.Statistics.WeeklyTotals.Where(x => x.WeekDate != x.ParseWeek(DateTime.UtcNow)).ToList();
foreach (var weekToDelete in weeksToDelete)
{
applicationUser.Statistics.WeeklyTotals.Remove(weekToDelete);
}
var monthsToDelete = applicationUser.Statistics.MonthlyTotals.Where(x => x.MonthDate != x.ParseMonth(DateTime.UtcNow)).ToList();
foreach (var monthToDelete in monthsToDelete)
{
applicationUser.Statistics.MonthlyTotals.Remove(monthToDelete);
}
var yearsToDelete = applicationUser.Statistics.YearlyTotals.Where(x => x.YearDate != x.ParseYear(DateTime.UtcNow)).ToList();
foreach (var yearToDelete in yearsToDelete)
{
applicationUser.Statistics.YearlyTotals.Remove(yearToDelete);
}
}
}
