public MonthlySummary(MonthlySummaryModel model) { this.model = model; InitializeComponent(); }
private async void button4_Click(object sender, EventArgs e) { var model = new MonthlySummaryModel(); var cmd = DBConn.CreateCommand(); cmd.CommandText = "SELECT SUM(qty*size*price/1000), SUM(qty*size*buyingPrice/1000) FROM receipt INNER JOIN resorder WHERE MONTH(date) = @month AND YEAR(date) = @year"; cmd.Parameters.AddWithValue("@month", dtpMonth.Value.Month); cmd.Parameters.AddWithValue("@year", dtpMonth.Value.Year); var reader = await cmd.ExecuteReaderAsync(); if (await reader.ReadAsync()) { if (!(reader.GetValue(0) is DBNull && reader.GetValue(1) is DBNull)) { model.bIncome = reader.GetFieldValue <decimal>(0); model.bCost = reader.GetFieldValue <decimal>(1); } } reader.Dispose(); cmd.CommandText = "SELECT SUM(IF(type = 1, selling*qty, 0)), SUM(IF(description = 'Transport', selling, 0)), SUM(buying*qty), SUM(IF(type = 2, selling, 0)) FROM tripdetails INNER JOIN trip ON tripdetails.tripID = trip.tripID WHERE MONTH(date) = @month AND YEAR(date) = @year"; reader = await cmd.ExecuteReaderAsync(); if (await reader.ReadAsync()) { if (!(reader.GetValue(0) is DBNull && reader.GetValue(1) is DBNull && reader.GetValue(2) is DBNull)) { model.tIncome = reader.GetFieldValue <decimal>(0); model.tTransport = reader.GetFieldValue <decimal>(1); model.tCost = reader.GetFieldValue <decimal>(2); model.totExpenses = reader.GetFieldValue <decimal>(3); } } reader.Dispose(); cmd.CommandText = "SELECT SUM(amount), SUM(IF(description = 'Bank Transport', amount, 0)), SUM(IF(description = 'Buying Transport', amount, 0)) FROM expenses WHERE MONTH(date) = @month AND YEAR(date) = @year"; reader = await cmd.ExecuteReaderAsync(); if (await reader.ReadAsync()) { if (!(reader.GetValue(0) is DBNull && reader.GetValue(1) is DBNull && reader.GetValue(2) is DBNull)) { model.totExpenses += reader.GetFieldValue <decimal>(0); model.bTransport = reader.GetFieldValue <decimal>(1); model.BuyingTransport = reader.GetFieldValue <decimal>(2); } } reader.Dispose(); model.date = dtpMonth.Value; model.totCost = model.bCost + model.tCost; model.totIncome = model.bIncome + model.tIncome; model.netProfit = model.totIncome - (model.totCost + model.totExpenses); reader.Dispose(); var frm = new MonthlySummary(model); frm.Show(); }