private void GenerateConsignmentResults(object sender, EventArgs e)
{
//change all local items that have status "unsold" to "returned" and save to database
foreach (var item in context.Items.Local.Where(i =>
i.ItemStatus.Trim() == ConsignmentStoreBusinessLogic.ITEM_STATUS_UNSOLD))
{
item.ItemStatus = ConsignmentStoreBusinessLogic.ITEM_STATUS_RETURNED;
}
context.SaveChanges();//save to database
//generate consignment results
if (ConsignmentStoreBusinessLogic.GenerateConsignmentResultsForPeriod(consignmentPeriod))
{
FormHelper.ShowInfoMessageBox("Consignment Results for Period " +
ConsignmentStoreBusinessLogic.GetConsignmentPeriodFromInt(consignmentPeriod) + " are creared!");
//success then close the form
Close();
}
else
{
FormHelper.ShowErrorMessageBox("Consignment Results for Period " +
ConsignmentStoreBusinessLogic.GetConsignmentPeriodFromInt(consignmentPeriod) + " cannot creared. Please try again!");
}
}
private void buttonLogin_Click(object sender, EventArgs e)
{
if (textBoxEmail.Text == "")
{
MessageBox.Show("Please enter Email!");
}
else if (textBoxPassword.Text == "")
{
MessageBox.Show("Please enter Password!");
}
else
{
switch (ConsignmentStoreBusinessLogic.Authenticate(textBoxEmail.Text, textBoxPassword.Text))
{
case ConsignmentStoreBusinessLogic.INVALID_EMAIL:
MessageBox.Show("Please enter INVALID_EMAIL!");
break;
case ConsignmentStoreBusinessLogic.INVALID_PASSWORD:
MessageBox.Show("Please enter INVALID_PASSWORD!");
break;
case ConsignmentStoreBusinessLogic.VALID:
this.DialogResult = DialogResult.OK;
this.Dispose();
break;
}
}
}
private void GenerateConsignmentResultsForm_Load(object sender, EventArgs e)
{
//set title
labelTitleDisplay.Text = "Checking Lost Items for Consignment Period " +
ConsignmentStoreBusinessLogic.GetConsignmentPeriodFromInt(consignmentPeriod);
//load unsold - items data from the period
context.Items.Where(item =>
item.ItemStatus == ConsignmentStoreBusinessLogic.ITEM_STATUS_UNSOLD &&
item.ConsignedDate.Month == consignmentPeriod % 100 &&
item.ConsignedDate.Year == consignmentPeriod / 100).Load();
//setup datagridview
FormHelper.InitializeDataGridViewSettings(dataGridViewItems);
//binding to dataGridView
dataGridViewItems.DataSource = context.Items.Local.ToBindingList();
dataGridViewItems.Columns["SoldItem"].Visible = false;
dataGridViewItems.Columns["Employee"].Visible = false;
dataGridViewItems.Columns["Consignor"].Visible = false;
//toggle status of item by double-click
dataGridViewItems.CellDoubleClick += ToggleItemStatusLostOrUnsold;
//filter by consignorId
textBoxSearch.TextChanged += SearchByConsignorId;
//button finish is clicked
buttonFinishGenerateConsignmentResults.Click += GenerateConsignmentResults;
}
private string GetConsignmentResultDetails(ConsignmentResult result)
{
if (result == null)
{
return("");
}
stringBuilder.Clear();
stringBuilder.AppendLine(string.Format("{0,-20} {1}", "Consignor Id",
result.Consignor.ConsignorId));
stringBuilder.AppendLine(string.Format("{0,-20} {1}", "Consignor Name",
result.Consignor.ConsignorName.Trim()));
stringBuilder.AppendLine(string.Format("{0,-20} {1}", "Consignor DOB",
((DateTime)result.Consignor.ConsignorDOB).ToString("dd MMMM yyyy")));
stringBuilder.AppendLine(string.Format("{0,-20} {1}", "Consignor Phone",
result.Consignor.ConsignorPhone.Trim()));
stringBuilder.AppendLine(string.Format("{0,-20} {1}", "Consignor Email",
result.Consignor.ConsignorEmail.Trim()));
stringBuilder.AppendLine(string.Format("{0,-20} {1}", "Consignor Period",
ConsignmentStoreBusinessLogic.GetConsignmentPeriodFromInt(result.ConsignmentPeriod)));
stringBuilder.AppendLine(string.Format("{0,-20} {1:n0}", "Returned Items",
result.NumberOfReturnedItems));
stringBuilder.AppendLine(string.Format("{0,-20} {1:c}", "Value Items",
result.TotalValueOfReturnedItems));
stringBuilder.AppendLine(string.Format("{0,-20} {1:c}", "Consignment Result",
result.TotalValueReceivedByConsignor));
return(stringBuilder.ToString());
}
private void UserControlReports_Load(object sender, EventArgs e)
{
//run update overview
ConsignmentStoreBusinessLogic.UpdateOverview();
DisplayOverview();
//Backup database
buttonBackupData.Click += ButtonBackupData_Click;
//Restore data from xml files
buttonRestore.Click += ButtonRestore_Click;
}
private void UpdateConsignmentPeriod(object sender, EventArgs e)
{
//convert selected item in combobox to consignmentPeriod
int consignmentPeriod = ConsignmentStoreBusinessLogic.GetConsignmentPeriodFromString(
comboBoxConsignmentPeriods.SelectedItem.ToString());
//update consignment results and summaries data gridviews
dataGridViewConsignmentResults.DataSource = context.ConsignmentResults.Local.Where(
result => result.ConsignmentPeriod == consignmentPeriod).ToList();
dataGridViewConsignmentSummaries.DataSource = context.ConsignmentSummaries.Local.Where(
summary => summary.ConsignmentPeriod == consignmentPeriod).ToList();
}
private void UserControlManageConsignmentResults_Load(object sender, EventArgs e)
{
//add consignment periods to combox box (format: month/year)
foreach (var period in context.ConsignmentSummaries.Local.ToList())
{
comboBoxConsignmentPeriods.Items.Add(
ConsignmentStoreBusinessLogic.GetConsignmentPeriodFromInt(period.ConsignmentPeriod));
}
//binding datagridview
FormHelper.InitializeDataGridViewSettings(dataGridViewConsignmentResults);
FormHelper.InitializeDataGridViewSettings(dataGridViewConsignmentSummaries);
dataGridViewConsignmentResults.DataSource = context.ConsignmentResults.Local.ToBindingList();//binding to dataGridView
dataGridViewConsignmentResults.Columns["ConsignmentSummary"].Visible = false;
dataGridViewConsignmentResults.Columns["Consignor"].Visible = false;
dataGridViewConsignmentResults.Columns["Employee"].Visible = false;
foreach (int colNum in new int[] { 3, 5, 7, 8 })// format money
{
dataGridViewConsignmentResults.Columns[colNum].DefaultCellStyle.Format = "N2";
dataGridViewConsignmentResults.Columns[colNum].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
}
dataGridViewConsignmentSummaries.DataSource = context.ConsignmentSummaries.Local.ToBindingList();//binding to dataGridView
dataGridViewConsignmentSummaries.Columns["ConsignmentResults"].Visible = false;
foreach (int colNum in new int[] { 1, 2, 4, 7, 9 })// format number
{
dataGridViewConsignmentSummaries.Columns[colNum].DefaultCellStyle.Format = "N0";
}
foreach (int colNum in new int[] { 3, 5, 6, 8, 10, 11 })// format money
{
dataGridViewConsignmentSummaries.Columns[colNum].DefaultCellStyle.Format = "N2";
dataGridViewConsignmentSummaries.Columns[colNum].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
}
//combobox
comboBoxConsignmentPeriods.SelectedIndexChanged += UpdateConsignmentPeriod;
//button generate consignment results
buttonGenerateConsignmentResults.Click += GenerateConsignmentResults;
}
private void GenerateConsignmentResults(object sender, EventArgs e)
{
//consignment period must
//1. must be greater than the largest consignmentPeriod (e.g. current is 201901)
//2. must less than current month (e.g. 201904)
//valid consignmentPeriods are 201902, 201903
//convert selected item in combobox to consignmentPeriod
int consignmentPeriod = ConsignmentStoreBusinessLogic.GetConsignmentPeriodFromString(
(int)numericUpDownMonth.Value + "/" + (int)numericUpDownYear.Value);
int upLimitConsignmentPeriod = ConsignmentStoreBusinessLogic.GetConsignmentPeriodFromString(
DateTime.Now.Month + "/" + DateTime.Now.Year);
int downLimitConsignmentPeriod = context.ConsignmentSummaries.Max(summary => summary.ConsignmentPeriod);
if (!ConsignmentStoreBusinessLogic.ValidateConsignmentPeriod(consignmentPeriod))
{
FormHelper.ShowErrorMessageBox("Period must greater than " +
ConsignmentStoreBusinessLogic.GetConsignmentPeriodFromInt(downLimitConsignmentPeriod) + " and less than " +
ConsignmentStoreBusinessLogic.GetConsignmentPeriodFromInt(upLimitConsignmentPeriod));
return;
}
//Checking lost items
GenerateConsignmentResultsForm generateConsignmentResultsForm = new GenerateConsignmentResultsForm(consignmentPeriod);
generateConsignmentResultsForm.ShowDialog();
generateConsignmentResultsForm.Dispose();
//Load combobox again
comboBoxConsignmentPeriods.Items.Clear();
//add consignment periods to combox box (format: month/year)
foreach (var period in context.ConsignmentSummaries.Local.ToList())
{
comboBoxConsignmentPeriods.Items.Add(
ConsignmentStoreBusinessLogic.GetConsignmentPeriodFromInt(period.ConsignmentPeriod));
}
}
public void RefreshView()
{
//run update overview
ConsignmentStoreBusinessLogic.UpdateOverview();
DisplayOverview();
}
public static void SeedDatabase(ConsignmentStoreEntities context)
{
string fileName,
// path point to SeedData folder
path = Environment.CurrentDirectory + "\\..\\..\\SeedData\\";
Random random = new Random();
StringBuilder sqlCommandBuilder = new StringBuilder("");
//if employees table has no data, then seed data (1 admin, 9 employees)
if (!context.Employees.Any())
{
//reseed table Employees, make sure Employees id start at 1
//insert and delete a dummy record, then reseed database
context.Database.ExecuteSqlCommand(
"insert into Employees " +
"(EmployeeName, EmployeePhone, EmployeePassword, EmployeeEmail, EmployeeType) " +
"values ('', '', '', '', '');" +
"delete from Employees;" +
"DBCC CHECKIDENT('Employees', RESEED, 0)");
//seed database by from sql file
fileName = "seed-employees.sql";
ExecuteSqlFile(context, path + fileName);
}
//if consignors table has no data, then seed data (100 consignors)
if (!context.Consignors.Any())
{
//reseed table Consignors, make sure Consignors id start at 1
//insert and delete a dummy record, then reseed database
context.Database.ExecuteSqlCommand(
"insert into Consignors " +
"(ConsignorName, ConsignorDOB, ConsignorPhone, ConsignorEmail) " +
"values ('', '19990909', '', '');" +
"delete from Consignors;" +
"DBCC CHECKIDENT('Consignors', RESEED, 0)");
//seed database by from sql file
fileName = "seed-consignors.sql";
ExecuteSqlFile(context, path + fileName);
}
//if items table has no data, then seed data
//(Jan/2019: 1000 items; Feb/2019: 1100 items; Mar/2019: 1200 items; Apr/1/2019 - Apr/7/2019: 500 items;)
if (!context.Items.Any())
{
//reseed table Items, make sure Items id start at 1
//insert and delete a dummy record, then reseed database
context.Database.ExecuteSqlCommand(
"insert into Items " +
"(ConsignorId, ConsignedDate, Price, ItemStatus, ConsignedBy) " +
"values (1, '19990909', 1, '', 1);" +
"delete from Items;" +
"DBCC CHECKIDENT('Items', RESEED, 0)");
//seed database by from sql file
fileName = "seed-items.sql";
ExecuteSqlFile(context, path + fileName);
}
//if SoldItems table has no data, then update it by loop through items that has status = "sold"
if (!context.SoldItems.Any())
{
//update sold_items table
var soldItems = context.Items.Where(x => x.ItemStatus == "sold").ToList();
//build insert sql commands for each sold item
foreach (var item in soldItems)
{
sqlCommandBuilder.AppendLine(
string.Format("insert into SoldItems (ItemId, SoldBy, SoldDate, DiscountAmount) " +
"values ({0}, {1}, '{2}', {3});",
item.ItemId,
random.Next(1, 10), //randomly pick an employee
//randomly pick soldDate between its ConsignedDate and 2 days after its ConsignedDate
item.ConsignedDate.AddDays(random.Next(0, 2)).ToString("MM/dd/yyyy"),
//about 20% sold items get DiscountAmount $5
random.Next(0, 9) > 7 ? 5 : 0
));
}
//execute sql commands
context.Database.ExecuteSqlCommand(sqlCommandBuilder.ToString());
sqlCommandBuilder.Clear();//clear string builder
}
decimal consignorConsignmentRatio = ConsignmentStoreBusinessLogic.GetConsignorRatio(); //70%
int consignmentPeriod = 201901; //Jan/2019 an interger of string month,year format "yyyyMM"
//all consigned items from Jan/2019 have consignment result at Apr,01,2019 (about 2-3 months)
DateTime startReturningConsignmentResultDate = new DateTime(2019, 04, 01);
//if ConsignmentSummaries table has no data
//then update with ConsignmentSummaries of Jan/2019
if (!context.ConsignmentSummaries.Any())
{
//get items that have ConsignedDate in Jan/2019 group by ConsignorId
var summaryItems = context.Items.Where(item =>
item.ConsignedDate > new DateTime(2018, 12, 31) &&
item.ConsignedDate < new DateTime(2019, 02, 01)
).GroupBy(item => item.ConsignorId)
.Select(group =>
new {
ConsignorId = group.Key,
//number and total value of returned items of a consignor
NumberOfReturnedItems = group.Count(item => item.ItemStatus.Trim() == "returned"),
TotalValueOfReturnedItems = group.Sum(item => item.ItemStatus.Trim() == "returned" ? item.Price : 0),
//number and total value of lost items of a consignor
NumberOfLostItems = group.Count(item => item.ItemStatus.Trim() == "lost"),
TotalValueOfLostItems = group.Sum(item => item.ItemStatus.Trim() == "lost" ? item.Price : 0),
//number and total value of sold items of a consignor
NumberOfSoldItems = group.Count(item => item.ItemStatus.Trim() == "sold"),
TotalValueOfSoldItems = group.Sum(item => item.ItemStatus.Trim() == "sold" ? item.Price : 0),
//total value of discount when selling items of a consignor
TotalValueOfDiscount = group.Sum(item => item.ItemStatus.Trim() == "sold" ? item.SoldItem.DiscountAmount : 0),
//total value of a consignment result of a consignor (70% consigned prices of items that have status "sold" or "lost")
TotalValueReceivedByConsignor = group.Sum(item => (item.ItemStatus.Trim() == "sold" ||
item.ItemStatus.Trim() == "lost" ? Math.Floor(item.Price * consignorConsignmentRatio) : 0))
}).ToList();
//add ConsignmentSummary for Jan/2019
context.ConsignmentSummaries.Add(new ConsignmentSummary
{
ConsignmentPeriod = consignmentPeriod,
NumberOfConsingors = summaryItems.Count,
//total number and value of consigned items in Jan,2019
NumberOfConsignedItems = summaryItems.Sum(group => group.NumberOfLostItems
+ group.NumberOfReturnedItems + group.NumberOfSoldItems),
TotalValueOfConsignedItems = summaryItems.Sum(group => group.TotalValueOfLostItems
+ group.TotalValueOfReturnedItems + group.TotalValueOfSoldItems),
//total number and value of sold items in Jan,2019
NumberOfSoldItems = summaryItems.Sum(group => group.NumberOfSoldItems),
TotalValueOfSoldItems = summaryItems.Sum(group => group.TotalValueOfSoldItems),
//total value of actual sold items in Jan,2019 (Price - DiscountAmount)
TotalValueOfActualSoldValue = summaryItems.Sum(group => group.TotalValueOfSoldItems - group.TotalValueOfDiscount),
//total number and value of returned items in Jan,2019
NumberOfReturnedItems = summaryItems.Sum(group => group.NumberOfReturnedItems),
TotalValueOfReturnedItems = summaryItems.Sum(group => group.TotalValueOfReturnedItems),
//total number and value of lost items in Jan,2019
NumberOfLostItems = summaryItems.Sum(group => group.NumberOfLostItems),
TotalValueOfLostItems = summaryItems.Sum(group => group.TotalValueOfLostItems),
//total value of money that consignors reiceved for their consignment in Jan,2019
TotalValueReceivedByConsignors = summaryItems.Sum(group => group.TotalValueReceivedByConsignor)
});
//clear ConsignmentResults table
context.Database.ExecuteSqlCommand("delete from ConsignmentResults;");
foreach (var group in summaryItems)
{//add consignment result for each consignor to ConsignmentResults for Jan/2019
context.ConsignmentResults.Add(new ConsignmentResult
{
ConsignmentPeriod = consignmentPeriod,
ConsignorId = group.ConsignorId,
//number of lost, returned, sold items of a consignor in consignment period Jan,2019
NumberOfLostItems = group.NumberOfLostItems,
NumberOfReturnedItems = group.NumberOfReturnedItems,
NumberOfSoldItems = group.NumberOfSoldItems,
//total value of lost, returned, sold items of a consignor in consignment period Jan,2019
TotalValueOfLostItems = group.TotalValueOfLostItems,
TotalValueOfReturnedItems = group.TotalValueOfReturnedItems,
TotalValueOfSoldItems = group.TotalValueOfSoldItems,
//total value a consignor received for his/her consignment in Jan,2019
TotalValueReceivedByConsignor = group.TotalValueReceivedByConsignor,
//return consignment result date randomly in range (Apr,1,2019 - Apr,7,2019)
ReturnedDate = startReturningConsignmentResultDate.AddDays(random.Next(0, 6)),
ReturnedBy = random.Next(1, 10)//employee return the consignment result
});
}
context.SaveChanges();
}
//if ExpenseCategories table has no data
//then add some default category
if (!context.ExpenseCategories.Any())
{
context.ExpenseCategories.AddRange(new List <ExpenseCategory> {
new ExpenseCategory {
Category = "Salary"
},
new ExpenseCategory {
Category = "Rent"
},
new ExpenseCategory {
Category = "Utilities", Description = "Electricity, Heat (gas), Water"
},
new ExpenseCategory {
Category = "Advertising"
},
new ExpenseCategory {
Category = "Supplies", Description = "label, bags, pen ..."
},
new ExpenseCategory {
Category = "Discount", Description = "Discount amount when selling items"
},
new ExpenseCategory {
Category = "ConsignmentResult", Description = "Money returns to consignors"
},
new ExpenseCategory {
Category = "Others", Description = "Not common expense e.g. "
},
});
context.SaveChanges();
}
//if Expenses table has no data
//then add some default Expenses
if (!context.Expenses.Any())
{
//Populate Discount Expenses from SoldItems table
foreach (var soldItem in context.SoldItems
.Where(item => item.DiscountAmount > 0)//filter only DiscountAmount > 0
.OrderBy(item => item.SoldDate))
{
context.Expenses.Add(new Expens
{
Date = soldItem.SoldDate,//set Discount expense date = SoldDate
Amount = soldItem.DiscountAmount,
Category = "Discount"
});
}
//Populate ConsignmentResult Expenses from ConsignmentResults table
foreach (var consignmentResult in context.ConsignmentResults
.Where(result => result.ReturnedDate != null)//if the consignment result had been returned
.OrderBy(result => result.ReturnedDate))
{
context.Expenses.Add(new Expens
{//set ConsignmentResult expense date = ReturnedDate
Date = (DateTime)consignmentResult.ReturnedDate,
Amount = consignmentResult.TotalValueReceivedByConsignor,
Category = "ConsignmentResult"
});
}
//Monthly Expenses from Jan/2019 to End of Mar/2019
//For seed data, simply add Expenses at date 28th of each month
for (int month = 1; month < 4; month++)
{
context.Expenses.AddRange(new List <Expens>
{
new Expens {
Date = new DateTime(2019, month, 28), Category = "Salary", Amount = 3000
},
new Expens {
Date = new DateTime(2019, month, 28), Category = "Rent", Amount = 2000
},
new Expens {
Date = new DateTime(2019, month, 28), Category = "Utilities", Amount = random.Next(100, 300)
},
new Expens {
Date = new DateTime(2019, month, 28), Category = "Advertising", Amount = random.Next(100, 200)
},
new Expens {
Date = new DateTime(2019, month, 28), Category = "Supplies", Amount = random.Next(50, 200)
},
new Expens {
Date = new DateTime(2019, month, 28), Category = "Others", Amount = random.Next(50, 200)
},
});
}
context.SaveChanges();
}
//if Overviews table has no data
//populate Overviews data for each date
if (!context.Overviews.Any())
{
//Starting point Dec,31,2018 starting budget $5000
Overview previousOverview = new Overview
{
Date = new DateTime(2018, 12, 31),
Budget = 5000,
ClientAsset = 0,
UsableBudget = 5000,
TotalIncome = 0,
TotalExpense = 0,
IncomeOfDate = 0,
ExpenseOfDate = 0
};
context.Overviews.Add(previousOverview);
//Business begin at Jan,1,2019
DateTime businessDate = new DateTime(2019, 1, 1);
decimal incomeOfDate, expenseOfDate, incomeClientAssetOfDate, expenseClientAssetOfDate, budget, clientAsset;
while (businessDate <= DateTime.Now.Date)
{
//compute total income and expense of the businessDate
incomeOfDate = context.SoldItems.Sum(soldItem => soldItem.SoldDate == businessDate ? soldItem.Item.Price : 0);
expenseOfDate = context.Expenses.Sum(expense => expense.Date == businessDate ? expense.Amount : 0);
//compute total incomeClientAsset and expenseClientAsset of the businessDate
//when an item was sold 70% of its price go to clientAsset
//when a consignor get his/her consignment result, then the value of the consignment result go out of clientAssetOfDate
incomeClientAssetOfDate = context.SoldItems.Sum(soldItem => soldItem.SoldDate == businessDate ?
Math.Floor(soldItem.Item.Price * consignorConsignmentRatio) : 0);
expenseClientAssetOfDate = context.Expenses.Sum(expense =>
expense.Category.Trim() == "ConsignmentResult" &&
expense.Date == businessDate ? expense.Amount : 0);
budget = previousOverview.Budget + incomeOfDate - expenseOfDate;
//compute current clientAsset
clientAsset = previousOverview.ClientAsset + incomeClientAssetOfDate - expenseClientAssetOfDate;
Overview todayOverview = new Overview
{
Date = businessDate, //the date
Budget = budget, //current budget of the date
ClientAsset = clientAsset, //current clientAsset of the date
UsableBudget = budget - clientAsset, //compute UsableBudget that is budget - clientAsset
TotalIncome = previousOverview.TotalIncome + incomeOfDate, //total income at the beginning
TotalExpense = previousOverview.TotalExpense + expenseOfDate, //total expense at the beginning
IncomeOfDate = incomeOfDate, //total income of the date
ExpenseOfDate = expenseOfDate //total expense of the date
};
context.Overviews.Add(todayOverview); //add todayOverview
previousOverview = todayOverview; //save previousOverview as todayOverview
businessDate = businessDate.AddDays(1); //next day
}
context.SaveChanges();
}
}
