private async void button3_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;
decimal expenses = 0;
cmd.CommandText = "SELECT date, place, IF(type = 2, description, name) AS name, SUM(qty), SUM(IF(type=1,qty*tripdetails.selling,0)), SUM(IF(type = 2, tripdetails.selling,qty*tripdetails.buying)), type FROM (trip INNER JOIN tripdetails ON trip.tripID = tripdetails.tripID) LEFT JOIN cashew ON tripdetails.cashID = cashew.cashID WHERE MONTH(date) = @month AND YEAR(date) = @year GROUP BY date, place, name ORDER BY type, date";
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 Trip Cashew"].NewRow();
dr["Index"] = i++;
dr["Date"] = reader.GetFieldValue <DateTime>(0).ToString("dd dddd");
dr["Trip"] = reader.GetFieldValue <string>(1);
dr["Cashew"] = reader.GetFieldValue <string>(2);
dr["QTY"] = reader.GetFieldValue <int>(6) == 1 ? Math.Round(reader.GetFieldValue <decimal>(3), 2) + "Kg" : "-";
dr["Income"] = reader.GetFieldValue <int>(6) == 1 ? "Rs. " + Math.Round(reader.GetFieldValue <decimal>(4), 2) : "-";
dr["Cost"] = "Rs. " + Math.Round(reader.GetFieldValue <decimal>(5), 2);
dr["Profit_Rate"] = reader.GetFieldValue <int>(6) == 1 ? Math.Round(((reader.GetFieldValue <decimal>(4) - reader.GetFieldValue <decimal>(5)) / reader.GetFieldValue <decimal>(4)) * 100) + "%" : "-";
ds.Tables["Monthly Trip Cashew"].Rows.Add(dr);
income += reader.GetFieldValue <decimal>(4);
cost += reader.GetFieldValue <int>(6) == 1 ? reader.GetFieldValue <decimal>(5) : 0;
expenses += reader.GetFieldValue <int>(6) == 2 ? reader.GetFieldValue <decimal>(5) : 0;
}
rate = ((income - (cost + expenses)) / income) * 100;
var model = new MonthlyTripCashewModel()
{
date = dtpDate.Value,
income = Math.Round(income, 2),
cost = Math.Round(cost, 2),
profit = Math.Round(income - (cost + expenses), 2),
expenses = Math.Round(expenses, 2),
rate = Math.Round(rate, 2)
};
reader.Dispose();
dataGridView1.DataSource = ds.Tables["Monthly Trip Cashew"];
var frm = new Monthly_Trip_Cashew(ds, model);
frm.Show();
}
public Monthly_Trip_Cashew(MonthlyReportDS ds, MonthlyTripCashewModel model)
{
this.model = model;
this.ds = ds;
InitializeComponent();
}
