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;
}
}
}