private void menu_allPurchaseOrders_Click(object sender, EventArgs e)
{
PurchaseOrders pos = new PurchaseOrders();
pos.Show();
}
//new purchase order
private void btn_order_Click(object sender, EventArgs e)
{
if (supplier_id == 0)
{
MessageBox.Show("Please select a supplier in the supplier list then continue", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (lst_cart.Items.Count == 0)
{
MessageBox.Show("Without adding products to the cart you cannot place an purchase order", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
DateTime order_date = Convert.ToDateTime(txt_order_date.Text);
try
{
//set stock status
bool stockStatus = false;
if (cb_stock_recived.Checked)
{
stockStatus = true;
}
//Open DB Connection
DB.connection.Open();
//Insert Order
//1st query string
String sqlString = "INSERT into purchases(number, date, total, supplier_id, stock) OUTPUT INSERTED.ID VALUES (@number, @date, @total, @supplier, @stockStatus)";
//1st sql command
SqlCommand cmmnd = new SqlCommand(sqlString, DB.connection);
//create a new purchase order
Classess.PurchaseOrder order = new Classess.PurchaseOrder();
//1st data binding
cmmnd.Parameters.Add("@number", SqlDbType.VarChar).Value = order.generateOrderNumber();
cmmnd.Parameters.Add("@date", SqlDbType.DateTime).Value = order_date;
cmmnd.Parameters.Add("@total", SqlDbType.Float).Value = total;
cmmnd.Parameters.Add("@supplier", SqlDbType.Int).Value = supplier_id;
cmmnd.Parameters.Add("@stockStatus", SqlDbType.TinyInt).Value = stockStatus;
//Executing 1st SQL Query
int order_id = (Int32)cmmnd.ExecuteScalar();
//Insert order products and update product quantity
for (int i = 0; i < lst_cart.Items.Count; i++)
{
//2nd query string
String sqlString2 = "INSERT into purchase_products(purchase_id, product_id, unit_price, quantity) VALUES (@order, @product, @unitprice, @quantity);";
if (stockStatus)
{
sqlString2 = sqlString2 + "UPDATE products SET quantity = quantity + @quantity WHERE id=@product;";
}
//2nd sql command
SqlCommand cmmnd2 = new SqlCommand(sqlString2, DB.connection);
//2nd data binding
cmmnd2.Parameters.Add("@order", SqlDbType.Int).Value = order_id;
cmmnd2.Parameters.Add("@product", SqlDbType.Int).Value = Int32.Parse(lst_cart.Items[i].SubItems[0].Text);
cmmnd2.Parameters.Add("@unitprice", SqlDbType.Float).Value = Convert.ToDouble(lst_cart.Items[i].SubItems[3].Text);
cmmnd2.Parameters.Add("@quantity", SqlDbType.Int).Value = Int32.Parse(lst_cart.Items[i].SubItems[4].Text);
//Executing 2nd SQL Query
cmmnd2.ExecuteScalar();
}
MessageBox.Show("Purchase Order Placed Successful", "Success", MessageBoxButtons.OK, MessageBoxIcon.None);
}
catch (SqlException ex)
{
throw new Exception(ex.Message);
}
finally
{
DB.connection.Close();
this.Close();
PurchaseOrders pos = new PurchaseOrders();
pos.Show();
}
}
}
