public static long SaleItems(string dateOfPurchase, List <Sale> saleItems)
{
OleDbConnection cnInsert = new OleDbConnection(DBUtil.GetConnectionString());
long lastInsertId = -1;
OleDbTransaction saleTransaction;
string query = "INSERT INTO Sales(Sale_Date,Type_ID,Quantity, Sale_Price) VALUES(@saleDate,@typeId, @quantity, @price)";
int iSqlStatus = 0;
try
{
DBUtil.HandleConnection(cnInsert);
saleTransaction = cnInsert.BeginTransaction();
foreach (Sale item in saleItems)
{
OleDbCommand cmdInsert = new OleDbCommand();
cmdInsert.CommandText = query;
cmdInsert.CommandType = CommandType.Text;
cmdInsert.Connection = cnInsert;
cmdInsert.Transaction = saleTransaction;
cmdInsert.Parameters.Clear();
cmdInsert.Parameters.AddWithValue("@saleDate", dateOfPurchase);
cmdInsert.Parameters.AddWithValue("@typeId", item.ItemTypeId);
cmdInsert.Parameters.AddWithValue("@quantity", item.Quantity);
cmdInsert.Parameters.AddWithValue("@price", item.SalePrice);
float avaliableQuantity = ShopDAL.getStockInHandQuantity(item.ItemTypeId);
if (avaliableQuantity < item.Quantity)
{
saleTransaction.Rollback();
throw new Exception("Error: Low available quantity " + item.ItemName + " has only " + avaliableQuantity);
}
float newQuantity = avaliableQuantity - item.Quantity;
ShopDAL.updateStockQuantityTransaction(cnInsert, saleTransaction, item.ItemTypeId, newQuantity);
iSqlStatus = cmdInsert.ExecuteNonQuery();
if (iSqlStatus == 0)
{
saleTransaction.Rollback();
throw new Exception("Error: Inserting value");
}
}
saleTransaction.Commit();
lastInsertId = DBUtil.GetMaxID("Sale_ID", "Sales");
return(lastInsertId);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
//Now close the connection
DBUtil.HandleConnection(cnInsert);
}
}
public static bool AddPurchasedItem(string dateOfPurchase, long itemTypeId, float quantity, float contractorId)
{
//Create the objects we need to insert a new record
OleDbConnection cnInsert = new OleDbConnection(DBUtil.GetConnectionString());
OleDbCommand cmdInsert = new OleDbCommand();
string query = " INSERT INTO Purchase( Purchase_Date, Type_ID, Quantity, Contractor_ID ) ";
query += " VALUES( @purcahseDate, @typeId, @quantity, @contractorId ) ";
int iSqlStatus;
cmdInsert.Parameters.Clear();
try
{
cmdInsert.CommandText = query;
cmdInsert.CommandType = CommandType.Text;
cmdInsert.Parameters.AddWithValue("@purcahseDate", dateOfPurchase);
cmdInsert.Parameters.AddWithValue("@typeId", itemTypeId);
cmdInsert.Parameters.AddWithValue("@quantity", quantity);
cmdInsert.Parameters.AddWithValue("@contractorId", contractorId);
cmdInsert.Connection = cnInsert;
//Now take care of the connection
DBUtil.HandleConnection(cnInsert);
//Set the iSqlStatus to the ExecuteNonQuery
//status of the insert (0 = failed, 1 = success)
iSqlStatus = cmdInsert.ExecuteNonQuery();
//Now check the status
if (iSqlStatus == 0)
{
return(false);
}
else
{
return(true);
}
}
catch (Exception ex)
{
return(false);
}
finally
{
//Now close the connection
DBUtil.HandleConnection(cnInsert);
float currentQuantity = ShopDAL.getStockInHandQuantity(itemTypeId);
float newQuantity = currentQuantity + quantity;
ShopDAL.updateStockQuantity(cnInsert, itemTypeId, newQuantity);
}
}