private void PurchaseOrders_Load(object sender, EventArgs e)
{
//sql select patient query
String sqlString = "SELECT * FROM purchases ORDER BY id DESC";
using (SqlCommand cmmnd = new SqlCommand(sqlString, DB.connection))
{
try
{
//Open DB Connection
DB.connection.Open();
//sql data reader
using (SqlDataReader rd = cmmnd.ExecuteReader())
{
if (rd.HasRows)
{
while (rd.Read())
{
Classess.PurchaseOrder order = new Classess.PurchaseOrder();
order.Id = Int32.Parse(rd["id"].ToString());
order.Number = rd["number"].ToString();
order.Total = double.Parse(rd["total"].ToString());
DateTime parsedDate;
if (DateTime.TryParse(rd["date"].ToString(), out parsedDate))
{
order.Date = parsedDate;
}
if (rd["stock"].ToString() == "1")
{
order.StockStatus = true;
}
else
{
order.StockStatus = false;
}
orderList.Add(order);
}
invoiceSource.DataSource = orderList;
data_order_list.DataSource = invoiceSource;
rd.Close();
data_order_list.Columns["number"].HeaderText = "Purchase Order Number";
data_order_list.Columns["total"].DefaultCellStyle.Format = "c";
data_order_list.Columns["total"].DefaultCellStyle.FormatProvider = new CultureInfo("si-LK");
data_order_list.Columns["id"].Visible = false;
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
//Close DB Connection
DB.connection.Close();
}
}
}
//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();
}
}
}
