public void loadChart()
{
try
{
Model.ConnectionSql connect = new Model.ConnectionSql();
MySqlConnection conn = new MySqlConnection(connect.connection());
conn.Open();
MySqlDataAdapter sda = new MySqlDataAdapter("SELECT YEAR(date_created) as year, SUM(TOTAL) as total FROM transaction GROUP BY YEAR(date_created)", conn);
DataSet data = new DataSet();
sda.Fill(data, "Sales");
chart1.DataSource = data.Tables["Sales"];
Series Series1 = chart1.Series["Series1"];
Series1.ChartType = SeriesChartType.Doughnut;
Series1.LabelForeColor = Color.White;
Series1.Name = "Sales";
var chart = chart1;
chart1.Series[Series1.Name].XValueMember = "year";
chart1.Series[Series1.Name].YValueMembers = "total";
chart.Series[0].IsValueShownAsLabel = true;
chart.Series[0].LabelFormat = "Php{#,##0.00}";
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void loadCancelSales()
{
try
{
Model.ConnectionSql connect = new Model.ConnectionSql();
this.reportViewer1.LocalReport.ReportPath = Application.StartupPath + @"\Reports\Report6.rdlc";
this.reportViewer1.LocalReport.DataSources.Clear();
MySqlConnection conn = new MySqlConnection(connect.connection());
conn.Open();
MySqlDataAdapter sda = new MySqlDataAdapter();
DataSet1 data = new DataSet1();
ReportDataSource reportViewer;
using (sda.SelectCommand = new MySqlCommand("SELECT * FROM viewcance_sales WHERE date_cancel BETWEEN '" + cancel_Sales.dateCancel1.Value.ToShortDateString() + "' AND '" + cancel_Sales.dateCancel2.Value.ToShortDateString() + "'", conn))
{
sda.Fill(data.Tables["CancelSales"]);
}
conn.Close();
reportViewer = new ReportDataSource("DataSet1", data.Tables["CancelSales"]);
this.reportViewer1.LocalReport.DataSources.Add(reportViewer);
this.reportViewer1.SetDisplayMode(Microsoft.Reporting.WinForms.DisplayMode.PrintLayout);
this.reportViewer1.ZoomMode = ZoomMode.Percent;
this.reportViewer1.ZoomPercent = 100;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void loadStochInHistory()
{
try
{
this.reportViewer1.LocalReport.ReportPath = Application.StartupPath + @"\Reports\Report5.rdlc";
this.reportViewer1.LocalReport.DataSources.Clear();
Model.ConnectionSql connect = new Model.ConnectionSql();
MySqlConnection conn = new MySqlConnection(connect.connection());
conn.Open();
MySqlDataAdapter sda = new MySqlDataAdapter();
ReportDataSource reportViewer;
DataSet1 dataset = new DataSet1();
using (sda.SelectCommand = new MySqlCommand("SELECT stock_id, referenceNo, product.products_id, product_name, description, quantity, stockDate, stockInBy, status FROM stocks INNER JOIN product ON product.products_id = stocks.products_id WHERE stockDate BETWEEN '" + history.dateTimePicker6.Value.ToShortDateString() + "' AND '" + history.dateTimePicker5.Value.ToShortDateString() + "' AND status LIKE '" + history.status.Text + "'", conn))
{
sda.Fill(dataset.Tables["StockInHistory"]);
}
reportViewer = new ReportDataSource("DataSet1", dataset.Tables["StockInHistory"]);
this.reportViewer1.LocalReport.DataSources.Add(reportViewer);
this.reportViewer1.SetDisplayMode(Microsoft.Reporting.WinForms.DisplayMode.PrintLayout);
this.reportViewer1.ZoomMode = ZoomMode.Percent;
this.reportViewer1.ZoomPercent = 100;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void loadInvetory()
{
try
{
Model.ConnectionSql connect = new Model.ConnectionSql();
MySqlConnection conn = new MySqlConnection(connect.connection());
this.reportViewer1.LocalReport.ReportPath = Application.StartupPath + @"\Reports\Report4.rdlc";
this.reportViewer1.LocalReport.DataSources.Clear();
ReportDataSource reportViewer;
MySqlDataAdapter sda = new MySqlDataAdapter();
DataSet1 data = new DataSet1();
conn.Open();
using (sda.SelectCommand = new MySqlCommand("SELECT * FROM viewproductinventory", conn))
{
sda.Fill(data.Tables["Inventory"]);
}
conn.Close();
reportViewer = new ReportDataSource("DataSet1", data.Tables["Inventory"]);
this.reportViewer1.LocalReport.DataSources.Add(reportViewer);
this.reportViewer1.SetDisplayMode(Microsoft.Reporting.WinForms.DisplayMode.PrintLayout);
this.reportViewer1.ZoomMode = ZoomMode.Percent;
this.reportViewer1.ZoomPercent = 100;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void loadSoldItems()
{
try
{
this.reportViewer1.LocalReport.ReportPath = Application.StartupPath + @"\Reports\Report8.rdlc";
this.reportViewer1.LocalReport.DataSources.Clear();
Model.ConnectionSql connect = new Model.ConnectionSql();
MySqlConnection conn = new MySqlConnection(connect.connection());
conn.Open();
ReportDataSource reportViewer;
MySqlDataAdapter sda = new MySqlDataAdapter();
DataSet1 data = new DataSet1();
using (sda.SelectCommand = new MySqlCommand("SELECT t.products_id, p.description, price, SUM(t.quantity) as qty, SUM(discount) as disc, SUM(total) as total FROM transaction AS t INNER JOIN product as p ON p.products_id = t.products_id WHERE t.date_created BETWEEN '" + soldItems.dateTimePicker4.Value.ToString("yyyy-MM-dd") + "' AND '" + soldItems.dateTimePicker3.Value.ToString("yyyy-MM-dd") + "' AND t.quantity != 0 GROUP BY t.products_id", conn))
{
sda.Fill(data.Tables["_soldItems"]);
}
conn.Close();
ReportParameter date = new ReportParameter("tDate", soldItems.dateTimePicker4.Value.ToLongDateString() + " to " + soldItems.dateTimePicker3.Value.ToLongDateString());
this.reportViewer1.LocalReport.SetParameters(date);
reportViewer = new ReportDataSource("DataSet1", data.Tables["_soldItems"]);
this.reportViewer1.LocalReport.DataSources.Add(reportViewer);
this.reportViewer1.SetDisplayMode(Microsoft.Reporting.WinForms.DisplayMode.PrintLayout);
this.reportViewer1.ZoomMode = ZoomMode.Percent;
this.reportViewer1.ZoomPercent = 100;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void loadTopSelling()
{
try
{
Model.ConnectionSql connect = new Model.ConnectionSql();
MySqlConnection conn = new MySqlConnection(connect.connection());
this.reportViewer1.LocalReport.ReportPath = Application.StartupPath + @"\Reports\Report7.rdlc";
this.reportViewer1.LocalReport.DataSources.Clear();
conn.Open();
ReportDataSource reportViewer;
DataSet1 data = new DataSet1();
MySqlDataAdapter sda = new MySqlDataAdapter();
if (topsell.topSalesSorting.Text == "SORT BY QTY")
{
using (sda.SelectCommand = new MySqlCommand("SELECT transaction_id, t.products_id, p.description, sum(t.quantity) as quantity, SUM(total) as total FROM transaction as t INNER JOIN product as p ON p.products_id = t.products_id WHERE t.date_created BETWEEN '" + topsell.dateTimePicker1.Value.ToString("yyyy-MM-dd") + "' AND '" + topsell.dateTimePicker2.Value.ToString("yyyy-MM-dd") + "' AND t.quantity != 0 GROUP BY products_id, discount ORDER BY quantity DESC LIMIT 10 ", conn))
{
sda.Fill(data.Tables["transaction"]);
}
}
else
{
using (sda.SelectCommand = new MySqlCommand("SELECT transaction_id, t.products_id, p.description, sum(t.quantity) as quantity, SUM(total) as total FROM transaction as t INNER JOIN product as p ON p.products_id = t.products_id WHERE t.date_created BETWEEN '" + topsell.dateTimePicker1.Value.ToString("yyyy-MM-dd") + "' AND '" + topsell.dateTimePicker2.Value.ToString("yyyy-MM-dd") + "' AND t.quantity != 0 GROUP BY products_id, discount ORDER BY total DESC LIMIT 10 ", conn))
{
sda.Fill(data.Tables["transaction"]);
}
}
reportViewer = new ReportDataSource("DataSet1", data.Tables["transaction"]);
this.reportViewer1.LocalReport.DataSources.Add(reportViewer);
this.reportViewer1.SetDisplayMode(Microsoft.Reporting.WinForms.DisplayMode.PrintLayout);
this.reportViewer1.ZoomMode = ZoomMode.Percent;
this.reportViewer1.ZoomPercent = 100;
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
