private void view()
{
db.openConnection();
query = "SELECT p.P_Name as 'Product Name',st.P_Quantity as 'Product Quantity',b.Location as 'Branch' FROM stores_in as st INNER JOIN product AS p ON st.P_ID = p.P_ID INNER JOIN branch as b ON st.Branch_ID = b.Branch_ID";
try
{
MySqlCommand cmd = new MySqlCommand(query, db.getmyConn());
MySqlDataAdapter myAdapter = new MySqlDataAdapter();
myAdapter.SelectCommand = cmd;
DataTable dt = new DataTable();
myAdapter.Fill(dt);
sales_distributeView.DataSource = dt;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
db.closeConnection();
}
private void id_search_bttn_Click(object sender, EventArgs e)
{
id = Convert.ToInt32(c_id_value.Text);
db = new Database.DatabaseHandler();
db.openConnection();
query = "select M_ID from customer where C_ID=" + id;
try
{
MySqlCommand cmd = new MySqlCommand(query, db.getmyConn());
cmd.ExecuteNonQuery();
mid = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (Exception ev)
{
MessageBox.Show(ev.ToString());
}
db.closeConnection();
sv.set_m_ID(id, mid);
this.Close();
sv.Visible = true;
}
private void ok_bttn_Click(object sender, EventArgs e)
{
db.openConnection();
dateTimefrom = dateTimePicker1.Value.ToString("yyyy-MM-dd");
dateTimeto = dateTimePicker2.Value.ToString("yyyy-MM-dd");
String[] columns = new[] { "Total_Cost", "Total_Sold", "report" };
List <List <String> > list = new List <List <string> >();
query = "SELECT SUM(warehouse.Price) as Total_Cost,SUM(transaction.Total_Price) as Total_Sold,( SUM(warehouse.Price) - SUM(transaction.Total_Price) ) as report " +
"FROM transaction, warehouse " +
"WHERE warehouse.S_Date between '" + dateTimefrom + "' AND '" + dateTimeto +
"' AND transaction.Date between '" + dateTimefrom + "' AND '" + dateTimeto + "'";
try
{
MySqlCommand cmd = new MySqlCommand(query, db.getmyConn());
MySqlDataReader dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
List <String> arr = new List <string>();
for (int i = 0; i < 3; i++)
{
arr.Add(dataReader[columns[i]].ToString());
}
list.Add(arr);
}
dataReader.Close();
dataReader = cmd.ExecuteReader();
if (dataReader.Read())
{
String report = null;
report = dataReader.GetString(2);
int rep = Convert.ToInt32(report);
if (rep < 0)
{
columns[2] = "Loss";
rep = Math.Abs(rep);
list[0][2] = Convert.ToString(rep);
}
else
{
columns[2] = "Benefit";
}
}
dataReader.Close();
DataTable dt = new DataTable();
for (int j = 0; j < 3; j++)
{
dt.Columns.Add(columns[j]);
}
foreach (var array in list)
{
dt.Rows.Add(array.ToArray());
}
salesReportView.DataSource = dt;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
db.closeConnection();
}
