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);
}
}
/// <summary>
/// SELECT transaction_id, transactionNo, p.products_id, p.description, p.price, discount, total, t.date_created FROM transaction as t INNER JOIN product as p ON p.products_id = t.products_id
/// </summary>
public void loadData(String cash, String Change)
{
try
{
String store = "HD Supply";
String address = "Sta. Barbara, Iloilo City";
ReportDataSource reportDataSource;
this.reportViewer1.LocalReport.ReportPath = Application.StartupPath + @"\Reports\Report1.rdlc";
this.reportViewer1.LocalReport.DataSources.Clear();
MySqlConnection conn = new MySqlConnection(connect.connection());
DataSet1 transact = new DataSet1();
MySqlDataAdapter sda = new MySqlDataAdapter();
conn.Open();
using (sda.SelectCommand = new MySqlCommand("SELECT transactionNo, p.description, p.price, t.quantity, discount, total, t.date_created FROM transaction as t INNER JOIN product as p ON p.products_id = t.products_id WHERE transactionNo = '" + pointOfSale.Transactionlbl.Text + "'", conn))
{
sda.Fill(transact.Tables["transaction"]);
}
conn.Close();
ReportParameter pvatable = new ReportParameter("pVatable", pointOfSale.lblvatable.Text);
ReportParameter pvat = new ReportParameter("pVat", pointOfSale.vatlbl.Text);
ReportParameter pdiscount = new ReportParameter("pDiscount", pointOfSale.txtdiscount.Text);
ReportParameter ptotalSales = new ReportParameter("pTotal", pointOfSale.total_sales.Text);
ReportParameter pCash = new ReportParameter("pCash", cash);
ReportParameter pchange = new ReportParameter("pChange", Change);
ReportParameter pstore = new ReportParameter("pStore", store);
ReportParameter paddress = new ReportParameter("pAddress", address);
ReportParameter ptransaction = new ReportParameter("pTransaction", "Invoice #: " + pointOfSale.Transactionlbl.Text);
ReportParameter pCashier = new ReportParameter("pCashier", pointOfSale.label1.Text);
reportViewer1.LocalReport.SetParameters(pvatable);
reportViewer1.LocalReport.SetParameters(pvat);
reportViewer1.LocalReport.SetParameters(pdiscount);
reportViewer1.LocalReport.SetParameters(ptotalSales);
reportViewer1.LocalReport.SetParameters(pCash);
reportViewer1.LocalReport.SetParameters(pchange);
reportViewer1.LocalReport.SetParameters(pstore);
reportViewer1.LocalReport.SetParameters(paddress);
reportViewer1.LocalReport.SetParameters(ptransaction);
reportViewer1.LocalReport.SetParameters(pCashier);
reportDataSource = new ReportDataSource("DataSet1", transact.Tables["transaction"]);
this.reportViewer1.LocalReport.DataSources.Add(reportDataSource);
this.reportViewer1.SetDisplayMode(Microsoft.Reporting.WinForms.DisplayMode.PrintLayout);
this.reportViewer1.ZoomMode = ZoomMode.Percent;
this.reportViewer1.ZoomPercent = 150;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
//MessageBox.Show(pointOfSale.Transactionlbl.Text);
}
public void loadPrintSaleData()
{
try
{
String store = "HD Supply";
String address = "Sta. Barbara, Iloilo City";
ReportDataSource reportViewer;
this.reportViewer1.LocalReport.ReportPath = Application.StartupPath + @"\Reports\Report2.rdlc";
this.reportViewer1.LocalReport.DataSources.Clear();
MySqlConnection conn = new MySqlConnection(connect.connection());
conn.Open();
DataSet1 printSale = new DataSet1();
MySqlDataAdapter sda = new MySqlDataAdapter();
if (sold.username.Text == "all cashier sales")
{
using (sda.SelectCommand = new MySqlCommand("SELECT transaction_id, transactionNo, t.products_id, p.description, p.price, t.quantity, discount, total FROM transaction as t INNER JOIN product as p ON p.products_id = t.products_id WHERE t.date_created BETWEEN '" + sold.dateTimePicker1.Value.ToString("yyyy-MM-dd") + "' AND '" + sold.dateTimePicker2.Value.ToString("yyyy-MM-dd") + "'", conn))
{
sda.Fill(printSale.Tables["soldItems"]);
}
}
else
{
using (sda.SelectCommand = new MySqlCommand("SELECT transaction_id, transactionNo, t.products_id, p.description, p.price, t.quantity, discount, total FROM transaction as t INNER JOIN product as p ON p.products_id = t.products_id WHERE t.date_created BETWEEN '" + sold.dateTimePicker1.Value.ToString("yyyy-MM-dd") + "' AND '" + sold.dateTimePicker2.Value.ToString("yyyy-MM-dd") + "' AND cashier = '" + sold.username.Text + "'", conn))
{
sda.Fill(printSale.Tables["soldItems"]);
}
}
conn.Close();
ReportParameter tstore = new ReportParameter("tStore", store);
ReportParameter taddress = new ReportParameter("tAddress", address);
ReportParameter tdate = new ReportParameter("tDate", "Date From : " + sold.dateTimePicker1.Value.ToLongDateString() + " To " + sold.dateTimePicker1.Value.ToLongDateString());
ReportParameter tcashier = new ReportParameter("tCashier", sold.username.Text);
this.reportViewer1.LocalReport.SetParameters(tstore);
this.reportViewer1.LocalReport.SetParameters(taddress);
this.reportViewer1.LocalReport.SetParameters(tdate);
this.reportViewer1.LocalReport.SetParameters(tcashier);
reportViewer = new ReportDataSource("DataSet1", printSale.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;
this.reportViewer1.RefreshReport();
}
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);
}
}