public POSui(int x) { //CONSTRUCTOR FOR HOLD InitializeComponent(); data.sum = 0; data.check = x; int holder; DBConnect initial = new DBConnect(); String query = "SELECT *,(product_qty*product_price)as sales FROM product GROUP BY product_name"; String trans = "INSERT INTO transactionlog(transactionDate) VALUES(NOW()); SELECT transactionID,transactionDate FROM transactionlog WHERE transactionDate=NOW();"; initial.getTransID(trans, out holder);//retrieves ID for foreign key assignment and deletion initial.getInv(copy, query); this.ActiveControl = IDfield; data.transID = holder; holdCust.Hide(); }
public void fetch() { String query1 = "SELECT p.*,SUM(L.qty)as sales FROM product p,purchaselog L WHERE p.id=l.itemID GROUP BY p.product_name";//default DBConnect local = new DBConnect(); List <Transucc1> x = new List <Transucc1>(); local.getInv(x, query1); for (int i = 0; i < x.Count; i++) { ListViewItem item = new ListViewItem(new String[] { x[i].ID.ToString(), x[i].itemName.ToString(), x[i].prodDesc.ToString(), x[i].qty.ToString(), x[i].price.ToString(), x[i].category.ToString(), x[i].barCode.ToString(), x[i].sales.ToString() }); listView1.Items.Add(item); if (x[i].qty < 50) { item.ForeColor = Color.Red; } } }
globals data = new globals(); //total price, transaction ID, and cashier ID put here public POSui() { InitializeComponent(); data.sum = 0; data.check = 0; int holder; DBConnect initial = new DBConnect(); String query = "SELECT *,(product_qty*product_price)as sales FROM product GROUP BY product_name"; String trans = "INSERT INTO transactionlog(transactionDate) VALUES(NOW()); SELECT transactionID,transactionDate FROM transactionlog WHERE transactionDate=NOW();"; initial.getTransID(trans, out holder);//retrieves transID for foreign key assignment and deletion initial.getInv(copy, query); this.ActiveControl = IDfield; data.transID = holder; AutoCompleteStringCollection col = new AutoCompleteStringCollection(); for (int i = copy.Count - 1; i >= 0; i--) { col.Add(copy[i].itemName); } itemNamefield.AutoCompleteCustomSource = col; }
private void filterButton_MouseClick(object sender, MouseEventArgs e) { listView1.Items.Clear(); DBConnect local = new DBConnect(); List <Transucc1> x = new List <Transucc1>(); if (orderField.Text == "Ascending by sales") { if (!String.IsNullOrEmpty(countField.Text) && rangeCheck.Checked) // has count and has date { int count = int.Parse(countField.Text); String query6 = "SELECT p.*,SUM(L.qty)as sales FROM product p,purchaseLog L WHERE L.purchaseDate BETWEEN DATE'" + startDate.Value.Date.ToString("yyyy-MM-dd") + "' AND DATE'" + endDate.Value.Date.ToString("yyyy-MM-dd") + "' AND p.id=l.itemID GROUP BY p.product_name ORDER BY sales ASC LIMIT " + " " + " " + count + "";//ascendnig with count and date local.getInv(x, query6); } else if (String.IsNullOrEmpty(countField.Text) && rangeCheck.Checked) // no count and has date { String query8 = "SELECT p.*,SUM(L.qty)as sales FROM product p,purchaseLog L WHERE L.purchaseDate BETWEEN DATE'" + startDate.Value.Date.ToString("yyyy-MM-dd") + "' AND DATE'" + endDate.Value.Date.ToString("yyyy-MM-dd") + "' AND p.id=l.itemID GROUP BY p.product_name ORDER BY sales ASC "; //ASCENDING WITH DATE AND NO COUNT local.getInv(x, query8); } else if (!String.IsNullOrEmpty(countField.Text) && !rangeCheck.Checked)//has count and no date { int count = int.Parse(countField.Text); String query4 = "SELECT p.*,SUM(L.qty)as sales FROM product p,purchaseLog L WHERE p.id=l.itemID GROUP BY p.product_name ORDER BY sales ASC LIMIT " + " " + " " + count + "";// ascending with count no date local.getInv(x, query4); } else// no count and no date { String query2 = "SELECT p.*,SUM(L.qty)as sales FROM product p,purchaseLog L WHERE p.id=l.itemID GROUP BY p.product_name ORDER BY sales ASC";//NO COUNT AND NO DATE local.getInv(x, query2); } } else if (orderField.Text == "Descending by sales") { if (!String.IsNullOrEmpty(countField.Text) && rangeCheck.Checked) { // count and date int count = int.Parse(countField.Text); String query7 = "SELECT p.*,SUM(L.qty)as sales FROM product p,purchaseLog L WHERE L.purchaseDate BETWEEN DATE'" + startDate.Value.Date.ToString("yyyy-MM-dd") + "' AND DATE'" + endDate.Value.Date.ToString("yyyy-MM-dd") + "' AND p.id=l.itemID GROUP BY p.product_name ORDER BY sales DESC LIMIT " + " " + " " + count + ""; //descendnig with count and date local.getInv(x, query7); } else if (String.IsNullOrEmpty(countField.Text) && rangeCheck.Checked) // no count and date { String query9 = "SELECT p.*,SUM(L.qty)as sales FROM product p,purchaseLog L WHERE L.purchaseDate BETWEEN DATE'" + startDate.Value.Date.ToString("yyyy-MM-dd") + "' AND DATE'" + endDate.Value.Date.ToString("yyyy-MM-dd") + "' AND p.id=l.itemID GROUP BY p.product_name ORDER BY sales DESC "; //DESCENDNIG WITH DATE AND NO COUNT local.getInv(x, query9); } else if (!String.IsNullOrEmpty(countField.Text) && !rangeCheck.Checked)//count and no date { int count = int.Parse(countField.Text); String query5 = "SELECT p.*,SUM(L.qty)as sales FROM product p,purchaseLog L WHERE p.id=l.itemID GROUP BY p.product_name ORDER BY sales DESC LIMIT " + " " + " " + count + "";//descending with count no date local.getInv(x, query5); } else// no count and no date { String query3 = "SELECT p.*,SUM(L.qty)as sales FROM product p,purchaseLog L WHERE p.id=l.itemID GROUP BY p.product_name ORDER BY sales DESC"; //NO COUNT AND NO DATE local.getInv(x, query3); } } else { String query1 = "SELECT p.*,SUM(L.qty)as sales FROM product p,purchaseLog L WHERE p.id=l.itemID GROUP BY p.product_name";//default local.getInv(x, query1); } for (int i = 0; i < x.Count; i++) { ListViewItem item = new ListViewItem(new String[] { x[i].ID.ToString(), x[i].itemName.ToString(), x[i].prodDesc.ToString(), x[i].qty.ToString(), x[i].price.ToString(), x[i].category.ToString(), x[i].barCode.ToString(), x[i].sales.ToString(), x[i].sales.ToString() }); listView1.Items.Add(item); if (x[i].qty < 50) { item.ForeColor = Color.Red; } } }