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