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