public void getInv(List <Transucc1> x, String query)
{
if (this.OpenConnection() == true)
{
MySqlCommand cmd = new MySqlCommand(query, connection);
MySqlDataReader dataReader = cmd.ExecuteReader();
Transucc1 local = new Transucc1();
while (dataReader.Read())
{
x.Add(new Transucc1
{
ID = Convert.ToInt32(dataReader["ID"]),
itemName = dataReader["product_name"].ToString(),
prodDesc = dataReader["product_description"].ToString(),
qty = Convert.ToInt32(dataReader["product_qty"]),
price = Convert.ToDouble(dataReader["product_price"]),
category = Convert.ToInt32(dataReader["category_id"]),
barCode = Convert.ToInt32(dataReader["bar_code"]),
sales = Convert.ToInt32(dataReader["sales"])
});
}
dataReader.Close();
this.CloseConnection();
}
}
private void button2_Click(object sender, EventArgs e)
{
DBConnect peepee = new DBConnect();
Transucc1 local = new Transucc1();
int check = Convert.ToInt32(qtyField.Value);
String query;
query = "SELECT*FROM product WHERE bar_code='" + IDfield.Text + "'"; //GETS BARCODE FROM DB AND STORES IN LOCAL CLASS
peepee.getItem(local, query);
String log = "INSERT INTO purchaselog(itemID,purchaseDate,qty,transactionID) VALUES(" + local.ID + ",curdate()," + check + "," + data.transID + ")";
if (local.qty - check >= 0)
{
String purchase = IDfield.Text;
int qty = (int)qtyField.Value;
double price = local.price * qty; //variable computes price times qty
data.sum += price; //updates global price
local.qty = check; //assigns the item holder qty to it's right value
local.price = price; //assigns the item prices to it's right value
peepee.transaction(this.trans, purchase, qty, local); //changes quantity then puts item in transaction global
totalField.Text = data.sum.ToString();
//reset fields
IDfield.Clear();
qtyField.Value = 1;
IDlabel.Text = local.ID.ToString();
itemLabel.Text = local.itemName.ToString();
itemNamefield.Clear();
int holder = local.barCode;
int i;
for (i = 0; i < copy.Count && holder != copy[i].barCode; i++)
{
} //finds item in inventory copy
if (i < copy.Count)
{
int x;
for (x = listView1.Items.Count - 1; x >= 0 && listView1.Items[x].SubItems[1].Text != copy[i].barCode.ToString(); x--)
{
} //finds item in list view
if (x >= 0)
{
incrementSubItem(x, local);//increments list view and purchase log when item already exists
}
else
{
updateList(local.itemName.ToString(), price, qty, local.barCode); //FUNCTION ADDS TO LISTVIEW
peepee.insertLog(log); //inserts item into log
}
}
}
else if (String.IsNullOrEmpty(IDfield.Text))
{
MessageBox.Show("Please enter barcode/name");
}
else
{
MessageBox.Show("Insufficient stocks");
}
}
public void transaction(List <Transucc1> x, String prodId, int qty, Transucc1 item)
{
if (this.OpenConnection() == true)
{
String sqlMod = "UPDATE product SET product_qty=product_qty-'" + qty + "'WHERE bar_code='" + item.barCode + "'";
//Check if stocks are enough
MySqlCommand cmd1 = new MySqlCommand(sqlMod, connection); //modify db product quantity
cmd1.ExecuteNonQuery();
x.Add(item); //ITEM GETS ADDED TO THE LIST
this.CloseConnection();
}
}
private void incrementSubItem(int indx, Transucc1 x)
{
DBConnect update = new DBConnect();
int qty = int.Parse(listView1.Items[indx].SubItems[2].Text);
String query = "UPDATE purchaselog SET qty=qty+'" + qty + "'WHERE itemID='" + x.ID + "' AND transactionID='" + data.transID + "'";
double total = Double.Parse(listView1.Items[indx].SubItems[3].Text);
qty += x.qty;
total += x.price;
listView1.Items[indx].SubItems[2].Text = qty.ToString();
listView1.Items[indx].SubItems[3].Text = total.ToString();
update.insertLog(query);//increments purchase log quantity
}
public void getItem(Transucc1 x, String query)
{
if (this.OpenConnection() == true)
{
MySqlCommand cmd = new MySqlCommand(query, connection);
MySqlDataReader dataReader = cmd.ExecuteReader();
dataReader.Read();
if (dataReader.HasRows)
{
if ((int)dataReader["product_qty"] > 0)
{
x.ID = Convert.ToInt32(dataReader["ID"]);
x.itemName = dataReader["product_name"].ToString();
x.qty = Convert.ToInt32(dataReader["product_qty"]);
x.price = Convert.ToDouble(dataReader["product_price"]);
x.barCode = Convert.ToInt32(dataReader["bar_code"]);
dataReader.Close();
}
}
this.connection.Close();
}
}