private async void button2_Click(object sender, EventArgs e)
{
var ds = new MonthlyReportDS();
var cmd = DBConn.CreateCommand();
var i = 1;
decimal income = 0;
decimal cost = 0;
decimal rate = 0;
cmd.CommandText = "SELECT date, name, SUM(size*qty/1000), SUM(size*qty*price/1000), SUM(size*qty*buyingPrice/1000) FROM (receipt INNER JOIN resorder ON receipt.resID = resorder.resID) INNER JOIN cashew ON resorder.cashID = cashew.cashID WHERE MONTH(date) = @month AND YEAR(date) = @year AND goodStatus = 2 GROUP BY date, name";
cmd.Parameters.AddWithValue("@month", dtpMonth.Value.Month);
cmd.Parameters.AddWithValue("@year", dtpMonth.Value.Year);
var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
if (reader.GetValue(2) is DBNull)
{
MessageBox.Show("No records found for this month", "No data", MessageBoxButtons.OK,
MessageBoxIcon.Error);
reader.Dispose();
return;
}
DataRow dr = ds.Tables["Monthly Bank Cashew"].NewRow();
dr["Index"] = i++;
dr["Date"] = reader.GetFieldValue <DateTime>(0).ToString("dd dddd");
dr["Cashew"] = reader.GetFieldValue <string>(1);
dr["QTY"] = Math.Round(reader.GetFieldValue <decimal>(2), 2) + "Kg";
dr["Income"] = "Rs. " + Math.Round(reader.GetFieldValue <decimal>(3), 2);
dr["Cost"] = "Rs. " + Math.Round(reader.GetFieldValue <decimal>(4), 2);
dr["Profit_Rate"] = Math.Round(((reader.GetFieldValue <decimal>(3) - reader.GetFieldValue <decimal>(4)) / reader.GetFieldValue <decimal>(3)) * 100) + "%";
ds.Tables["Monthly Bank Cashew"].Rows.Add(dr);
income += reader.GetFieldValue <decimal>(3);
cost += reader.GetFieldValue <decimal>(4);
}
rate = ((income - cost) / income) * 100;
var model = new MonthlyBankCashewModel()
{
date = dtpDate.Value,
income = Math.Round(income, 2),
cost = Math.Round(cost, 2),
profit = Math.Round(income - cost, 2),
rate = Math.Round(rate, 2)
};
reader.Dispose();
dataGridView1.DataSource = ds.Tables["Monthly Bank Cashew"];
var frm = new Monthly_Bank_Cashew(ds, model);
frm.Show();
}
public Monthly_Bank_Cashew(MonthlyReportDS ds, MonthlyBankCashewModel model)
{
this.ds = ds;
this.model = model;
InitializeComponent();
}
