//// End Contact Tracing //////
private void button1_Click(object sender, EventArgs e)
{
try
{
//dataGridView1.DataSource = null;
//dataGridView1.Rows.Clear();
//dataGridView2.ColumnCount = 4;
//dataGridView1.Columns[0].Name = "Date";
//dataGridView1.Columns[1].Name = "Year";
//dataGridView1.Columns[2].Name = "Month";
//dataGridView1.Columns[3].Name = "Ammount";
//DateTime dt = DateTime.Today;
Database db = new Database();
// Group dapat
//String query1 = "SELECT Date , YEAR(Date) ,MONTH(Date) ,DAY(Date) FROM bookingdb.tblbookedseats ";
String query1 = "SELECT Date,YEAR(Date) AS Year, MONTH(Date) AS Month, SUM(Ammount) AS 'Total Ammount' FROM tbltransaction GROUP BY MONTH(Date) ORDER BY Date DESC";
db.conn.Open();
ArrayList AL = new ArrayList();
MySqlCommand command1 = new MySqlCommand(query1, db.conn);
MySqlDataAdapter da = new MySqlDataAdapter(command1);
DataTable tb = new DataTable();
da.Fill(tb);
DataTable dtCloned = tb.Clone();
dtCloned.Columns[0].DataType = typeof(string);
dtCloned.Columns[1].DataType = typeof(Int32);
dtCloned.Columns[2].DataType = typeof(Int32);
dtCloned.Columns[3].DataType = typeof(Int32);
foreach (DataRow row in tb.Rows)
{
dtCloned.ImportRow(row);
}
dtCloned.Columns[1].ColumnName = "Year";
dtCloned.Columns[2].ColumnName = "Month";
dtCloned.Columns[3].ColumnName = "Ammount";
list.Clear();
for (int i = 1; i < dtCloned.Rows.Count; i++)
{
lvSalesReport sales = new lvSalesReport();
sales.Date = dtCloned.Rows[i][0].ToString();
sales.Year = int.Parse(dtCloned.Rows[i][1].ToString());
sales.Month = int.Parse(dtCloned.Rows[i][2].ToString());
sales.Ammount = int.Parse(dtCloned.Rows[i][3].ToString());
list.Add(sales);
}
dataGridView2.DataSource = list;
dataGridView2.Refresh();
//list = (from DataRow dr in dtCloned.Rows
// select new lvSalesReport()
// {
// Date = dr["Date"].ToString(),
// Year = Convert.ToInt32(dr["Year"]),
// Month = Convert.ToInt32(dr["Month"]),
// Day = Convert.ToInt32(dr["Day"])
// }).ToList();
//MySqlDataReader reader = command1.ExecuteReader();
//while (reader.Read())
//{
// AL = new ArrayList();
// AL.Add(reader[0].ToString());
// AL.Add(int.Parse(reader[1].ToString()));
// AL.Add(int.Parse(reader[2].ToString()));
// AL.Add(int.Parse(reader[3].ToString()));
// dataGridView2.Rows.Add(AL.ToArray());
//}
//reader.Close();
command1.Dispose();
//dataGridView2.AutoResizeColumns();
//dataGridView2.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
db.conn.Close();
if (dataGridView2.Rows.Count == 0 || dataGridView2.Rows == null)
{
MessageBox.Show("No Result Found");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void LoadChart()
{
lvSalesReportBindingSource.DataSource = list;
cartesianChart1.AxisX.Add(new LiveCharts.Wpf.Axis
{
Title = "Month",
Labels = new[] { "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December" }
});
cartesianChart1.AxisY.Add(new LiveCharts.Wpf.Axis
{
Title = "Revenue",
LabelFormatter = value => value.ToString("C")
});
cartesianChart1.LegendLocation = LiveCharts.LegendLocation.Right;
///////////////////
try
{
Database db = new Database();
String query1 = "SELECT Date,YEAR(Date) AS Year, MONTH(Date) AS Month, SUM(Ammount) AS 'Total Ammount' FROM tbltransaction GROUP BY DATE_FORMAT(Date, '%m.%Y') ORDER BY Date DESC";
db.conn.Open();
ArrayList AL = new ArrayList();
MySqlCommand command1 = new MySqlCommand(query1, db.conn);
MySqlDataAdapter da = new MySqlDataAdapter(command1);
DataTable tb = new DataTable();
da.Fill(tb);
DataTable dtCloned = tb.Clone();
dtCloned.Columns[0].DataType = typeof(string);
dtCloned.Columns[1].DataType = typeof(Int32);
dtCloned.Columns[2].DataType = typeof(Int32);
dtCloned.Columns[3].DataType = typeof(Int32);
foreach (DataRow row in tb.Rows)
{
dtCloned.ImportRow(row);
}
dtCloned.Columns[1].ColumnName = "Year";
dtCloned.Columns[2].ColumnName = "Month";
dtCloned.Columns[3].ColumnName = "Ammount";
list.Clear();
for (int i = 0; i < dtCloned.Rows.Count; i++)
{
lvSalesReport sales = new lvSalesReport();
sales.Date = dtCloned.Rows[i][0].ToString();
sales.Year = int.Parse(dtCloned.Rows[i][1].ToString());
sales.Month = int.Parse(dtCloned.Rows[i][2].ToString());
sales.Ammount = int.Parse(dtCloned.Rows[i][3].ToString());
list.Add(sales);
}
command1.Dispose();
db.conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
//////////////////////////////////
cartesianChart1.Series.Clear();
SeriesCollection series = new SeriesCollection();
var years = (from o in lvSalesReportBindingSource.DataSource as List <lvSalesReport>
select new { Year = o.Year }).Distinct();
foreach (var year in years)
{
List <double> values = new List <double>();
for (int month = 1; month <= 12; month++)
{
double value = 0;
var data = from o in lvSalesReportBindingSource.DataSource as List <lvSalesReport>
where o.Year.Equals(year.Year) && o.Month.Equals(month)
orderby o.Month ascending
select new { o.Ammount, o.Month };
if (data.SingleOrDefault() != null)
{
value = data.SingleOrDefault().Ammount;
}
values.Add(value);
}
series.Add(new LineSeries()
{
Title = year.Year.ToString(), Values = new ChartValues <double>(values)
});
}
cartesianChart1.Series = series;
}
