public void AddPatient(PatientModel patientData)
{
SqlDataAccess sql = new SqlDataAccess();
try
{
sql.StartTransaction("PrescriptionDataManagerUpdated");
sql.SaveDataInTransaction("dbo.spPatient_Add", new
{
FirstName = patientData.FirstName,
LastName = patientData.LastName,
DOB = patientData.DOB,
Email = patientData.Email,
PhoneNumber = patientData.PhoneNumber
});
sql.CommitTransaction();
}
catch (Exception)
{
sql.RollbackTransation();
throw;
}
}
public void SaveSale(SaleModel sale, string cashierId)
{
ProductData productData = new ProductData();
List <SaleDetailDbModel> details = new List <SaleDetailDbModel>();
//Create a model for insertion in DB for each sale detail model that was posted to the api
foreach (SaleDetailModel item in sale.saleDetails)
{
ProductModel productInfo = productData.GetProductById(item.Id);
if (productInfo == null)
{
throw new System.Exception($"The product with id {item.Id} could not be found in database");
}
decimal tax = 0;
decimal purchasePrice = productInfo.RetailPrice * item.Quantity;
if (productInfo.IsTaxable)
{
tax = purchasePrice * Convert.ToDecimal(ConfigHelper.GetTaxRate());
}
details.Add(new SaleDetailDbModel
{
ProductId = item.Id,
Quantity = item.Quantity,
PurchasePrice = purchasePrice,
Tax = tax,
});
}
//create the sale model
SaleDbModel saleDbModel = new SaleDbModel
{
SubTotal = details.Sum(x => x.PurchasePrice),
Tax = details.Sum(x => x.Tax),
CashierId = cashierId,
};
saleDbModel.Total = saleDbModel.SubTotal + saleDbModel.Tax;
using (SqlDataAccess dataAccess = new SqlDataAccess())
{
try
{
dataAccess.StartTransaction("TRMData");
dataAccess.SaveDataInTransaction("dbo.spSale_Insert", saleDbModel);
saleDbModel.Id = dataAccess.LoadDataInTransaction <int, dynamic>("dbo.spLookUp", new { CashierId = cashierId, saleDbModel.SaleDate }).FirstOrDefault();
//Finish filling in the sale detail models
foreach (SaleDetailDbModel item in details)
{
item.SaleId = saleDbModel.Id;
dataAccess.SaveDataInTransaction("dbo.spSaleDetail_Insert", item);
}
;
dataAccess.CommitTransaction();
}
catch (Exception e)
{
dataAccess.RollBackTransaction();
throw;
}
}
}
public void AddPrescription(PrescriptionModel prescription, string presciberId)
{
SqlDataAccess sql = new SqlDataAccess();
try
{
sql.StartTransaction("PrescriptionDataManagerUpdated");
sql.SaveDataInTransaction("dbo.spPrescription_Add", new
{
PatientId = prescription.PatientID.ToString(),
DrugName = prescription.DrugName,
Dosage = prescription.Dosage,
PrescriberId = presciberId,
RenewalDate = prescription.RenewalDate
});
sql.CommitTransaction();
}
catch (Exception)
{
sql.RollbackTransation();
throw;
}
/* var output = sql.SaveData<PrescriptionModel>("dbo.spPrescription_Add", new { PatientId = prescription.PatientID.ToString(), DrugName = prescription.DrugName, Dosage = prescription.Dosage,
* PrescriberId = prescription.PrescriberId, RenewalDate = prescription.RenewalDate}, "PrescriptionDataMangerUpdated");*/
}
public void AddNote(PatientNoteModel note)
{
SqlDataAccess sql = new SqlDataAccess();
try
{
sql.StartTransaction("PrescriptionDataManagerUpdated");
sql.SaveDataInTransaction("dbo.spNote_Add",
new
{
note.PatientId,
note.Title,
note.Text,
note.AuthorId
});
sql.CommitTransaction();
}
catch
{
sql.RollbackTransation();
throw;
}
}
public void EditPrescription(PrescriptionModel prescription, string presciberId)
{
SqlDataAccess sql = new SqlDataAccess();
try
{
sql.StartTransaction("PrescriptionDataManagerUpdated");
sql.SaveDataInTransaction("dbo.spPrescription_Edit", new
{
PrescriptionId = prescription.ID,
Dosage = prescription.Dosage,
PrescriberId = presciberId,
RenewalDate = prescription.RenewalDate
});
sql.CommitTransaction();
}
catch (Exception)
{
sql.RollbackTransation();
throw;
}
}
public void RegisterUserDetails(UserAddModel userDetails)
{
SqlDataAccess sql = new SqlDataAccess();
try
{
sql.StartTransaction("PrescriptionDataManagerUpdated");
sql.SaveDataInTransaction("dbo.spUser_Add", new
{
id = userDetails.Id,
firstName = userDetails.FirstName,
lastName = userDetails.LastName,
email = userDetails.Email
});
sql.CommitTransaction();
}
catch (Exception)
{
sql.RollbackTransation();
throw;
}
}
public void SaveSale(SaleModel saleInfo, string cashierId)
{
List <SaleDetailDBModel> details = new List <SaleDetailDBModel>();
ProductData products = new ProductData(_config);
var taxRate = ConfigHelper.GetTaxRate();
foreach (var item in saleInfo.SaleDetails)
{
var detail = new SaleDetailDBModel
{
ProductId = item.ProductId,
Quantity = item.Quantity
};
var productInfo = products.GetProductById(detail.ProductId);
if (productInfo == null)
{
throw new Exception($"The product id of {detail.ProductId} could not be found in the database.");
}
detail.PurchasePrice = productInfo.RetailPrice * detail.Quantity;
if (productInfo.IsTaxable)
{
detail.Tax = detail.PurchasePrice * taxRate / 100;
}
details.Add(detail);
}
SaleDBModel sale = new SaleDBModel
{
SubTotal = details.Sum(x => x.PurchasePrice),
Tax = details.Sum(x => x.Tax),
CashierId = cashierId,
};
sale.Total = sale.SubTotal + sale.Tax;
using (SqlDataAccess sql = new SqlDataAccess(_config))
{
try
{
sql.StartTransaction("TRMData");
sql.SaveDataInTransaction("dbo.spSale_Insert", sale);
sale.Id = sql.LoadDataInTransaction <int, dynamic>("spSale_Lookup", new { sale.CashierId, sale.SaleDate }).FirstOrDefault();
foreach (var item in details)
{
item.SaleId = sale.Id;
sql.SaveDataInTransaction("dbo.spSaleDetail_Insert", item);
}
sql.CommitTransaction();
}
catch
{
sql.RollbackTransaction();
throw;
}
}
}
public async Task <int> CreateSaleAsync(string userId, SaleModel saleInfo)
{
decimal taxRate = 0.0875M;//new ConfigHelper().GetTaxRate();
var saleDetails = new List <SaleDetailDbModel>();
foreach (var item in saleInfo.SaleDetails)
{
var detail = new SaleDetailDbModel()
{
ProductId = item.ProductId,
Quantity = item.Quantity
};
var productInfo = await _productRepository.GetProductByIdAsync(item.ProductId);
if (productInfo == null)
{
throw new Exception($"The product Id {item.ProductId} could not be found");
}
detail.PurchasePrice = (decimal)(productInfo.RetailPrice * item.Quantity);
if (productInfo.IsTaxable)
{
detail.Tax = (detail.PurchasePrice * taxRate);
}
saleDetails.Add(detail);
}
var subtotal = saleDetails.Sum(d => d.PurchasePrice);
var tax = saleDetails.Sum(d => d.Tax);
var total = subtotal + tax;
var saleModel = new SaleDbModel()
{
Subtotal = subtotal,
Tax = tax,
Total = total,
UserId = userId
};
var saleId = -1;
using (var sql = new SqlDataAccess())
{
try
{
sql.StartTransaction(_connectionStringName);
saleId = await sql.ExecuteWithOutputInTransactionAsync(
$@"
INSERT INTO [dbo].[Sale](UserId, Subtotal, Tax, Total)
OUTPUT INSERTED.[Id]
VALUES(
@{nameof(saleModel.UserId)},
@{nameof(saleModel.Subtotal)},
@{nameof(saleModel.Tax)},
@{nameof(saleModel.Total)})
",
new
{
saleModel.UserId,
saleModel.Subtotal,
saleModel.Tax,
saleModel.Total
});
foreach (var detail in saleDetails)
{
detail.SaleId = saleId;
// TODO: Single Call? TVP
await sql.ExecuteInTransactionAsync(
$@"
INSERT INTO [dbo].[SaleDetail](SaleId, ProductId, Quantity, PurchasePrice, Tax)
VALUES(
@{nameof(detail.SaleId)},
@{nameof(detail.ProductId)},
@{nameof(detail.Quantity)},
@{nameof(detail.PurchasePrice)},
@{nameof(detail.Tax)})
",
new
{
detail.SaleId,
detail.ProductId,
detail.Quantity,
detail.PurchasePrice,
detail.Tax
});
}
sql.CommitTransaction();
}
catch
{
sql.RollbackTransaction();
throw;
}
}
return(saleId);
}
public async Task SaveSale(SaleModel saleInfo, string cashierId)
{
// TODO: Make it Solid/DRY/Better
// Start filling in the models we will save to the database
// Fill in the available info
List <SaleDetailDBModel> saleDetails = new List <SaleDetailDBModel>();
ProductData products = new ProductData();
var taxRate = ConfigHelper.GetTaxRate() / 100;
foreach (var item in saleInfo.SaleDetails)
{
var detail = new SaleDetailDBModel
{
ProductId = item.ProductId,
Quantity = item.Quantity
};
// Get the information about this product
var productInfo = await products.GetProductById(detail.ProductId).ConfigureAwait(false);
if (productInfo == null)
{
throw new Exception($"the product id of {detail.ProductId} couldn't found in the database.");
}
detail.PurchasePrice = (productInfo.RetailPrice * detail.Quantity);
if (productInfo.IsTaxable)
{
detail.Tax = (detail.PurchasePrice * taxRate);
}
saleDetails.Add(detail);
}
// Create the sale model
SaleDBModel sale = new SaleDBModel
{
SubTotal = saleDetails.Sum(x => x.PurchasePrice),
Tax = saleDetails.Sum(x => x.Tax),
CashierId = cashierId
};
sale.Total = sale.SubTotal + sale.Tax;
using (SqlDataAccess sql = new SqlDataAccess())
{
try
{
sql.StartTransaction("TRMData");
// Save the sale model
await sql.SaveDataInTransaction("dbo.spSale_Insert", sale).ConfigureAwait(false);
// Get the ID from the sale model
sale.Id = (await sql.LoadDataInTransaction <int, dynamic>("dbo.spSale_Lookup",
new { CashierId = sale.CashierId, SaleDate = sale.SaleDate }).ConfigureAwait(false))
.FirstOrDefault();
// Finish filling in the sale detail models
foreach (var item in saleDetails)
{
item.SaleId = sale.Id;
// Save the sale detail models
await sql.SaveDataInTransaction("dbo.spSaleDetail_Insert", item).ConfigureAwait(false);
}
sql.CommitTransaction();
}
catch
{
sql.RollBackTransaction();
throw;
}
}
}
public void SaveSale(SaleModel saleAPIInput, string cashierId)
{
System.Diagnostics.Debug.WriteLine("Start SaveSale");
// Filling in the sale deal models we'll save to the db
List <SaleDetailDBModel> details = new List <SaleDetailDBModel>();
ProductData productDataAccess = new ProductData(config);
foreach (var item in saleAPIInput.SaleDetails)
{
// Fill in the available info
System.Diagnostics.Debug.WriteLine("item.ProductId " + item.ProductId);
System.Diagnostics.Debug.WriteLine(" item.Quantity " + item.Quantity);
System.Diagnostics.Debug.WriteLine(" cashierId " + cashierId);
var detail = new SaleDetailDBModel
{
ProductId = item.ProductId,
Quantity = item.Quantity
};
var productInfo = productDataAccess.GetProductById(item.ProductId);
System.Diagnostics.Debug.WriteLine("productInfo " + productInfo);
System.Diagnostics.Debug.WriteLine("productInfo RetailPrice " + productInfo.RetailPrice);
if (productInfo == null)
{
throw new Exception($"The product id of {detail.ProductId} is not found in db");
}
detail.PurchasePrice = (productInfo.RetailPrice * detail.Quantity);
details.Add(detail);
}
// create the sale model
SaleDBModel sale = new SaleDBModel
{
SubTotal = details.Sum(x => x.PurchasePrice),
Tax = 0,
CashierId = cashierId
};
sale.Total = sale.SubTotal;
// save the model
using (SqlDataAccess sql = new SqlDataAccess(config))
{
try
{
sql.StartTransaction("KelvinData");
sql.SaveDataInTransaction <SaleDBModel>("dbo.spSale_Insert", sale);
// Get the id from sale model
int saleId = sql.LoadDataInTransaction <int, dynamic>("spSale_Lookup", new { CashierId = sale.CashierId, SaleDate = sale.SaleDate }).FirstOrDefault();
// Finish filling in sale detail models.
foreach (var item in details)
{
item.SaleId = saleId;
// save the sale detail models
sql.SaveDataInTransaction("dbo.spSaleDetail_Insert", item);
}
sql.CommitTransaction();
} catch
{
sql.RollbackTransaction();
throw;
}
}
}
public void SaveSale(SaleModel saleInfo, string cashierId)
{
//TODO: make a SOLID/DRY/better
// start filling in the models which we need to save in the database.
//fill in the available information
List <SaleDetailDBModel> details = new List <SaleDetailDBModel>();
ProductData products = new ProductData();
var taxRate = ConfigHelper.GetTaxRate() / 100;
foreach (var item in saleInfo.SaleDetails)
{
var detail = new SaleDetailDBModel
{
ProductId = item.ProductId,
Quantity = item.Quantity
};
//Get the information about this product
var productInfo = products.GetProductById(item.ProductId);
if (productInfo == null)
{
throw new Exception($"The product Id of {item.ProductId} could not be found in the database.");
}
detail.PurchasePrice = (productInfo.RetailPrice * detail.Quantity);
if (productInfo.IsTaxable)
{
detail.Tax = (detail.PurchasePrice * taxRate);
}
details.Add(detail);
}
//Create the sale model
SaleDBModel sale = new SaleDBModel
{
SubTotal = details.Sum(x => x.PurchasePrice),
Tax = details.Sum(x => x.Tax),
CashierId = cashierId
};
sale.Total = sale.SubTotal + sale.Tax;
using (SqlDataAccess sql = new SqlDataAccess())
{
try
{
sql.StartTransaction("TRMData");
//Save the sale model
sql.SaveDataInTransaction <SaleDBModel>("dbo.spSale_Insert", sale);
//getting ID from the sale mode
sale.Id = sql.LoadDataInTransaction <int, dynamic>("spSale_Lookup", new { sale.CashierId, sale.SaleDate }).FirstOrDefault();
//finish filling in the sale detail models.
//for 1000s of calls. use advanced dapper. where you transfter a table .
foreach (var item in details)
{
item.SaleId = sale.Id;
//save the sale detail models
sql.SaveDataInTransaction("dbo.spSaleDetail_Insert", item);
}
sql.CommitTransaction();
}
catch
{
sql.RollbackTransaction();
throw;
}
}
}
public void SaveSale(SaleModel sale, string userId)
{
// TODO: Make this SOLID/DRY/Better
// Start in filling in the sale detail models we will save to the database
List <SaleDetailDBModel> saleDetails = new List <SaleDetailDBModel>();
ProductData productData = new ProductData();
var taxRate = ConfigHelper.GetTaxRate() / 100;
foreach (var item in sale.SaleDetails)
{
var detail = new SaleDetailDBModel
{
ProductId = item.ProductId,
Quantity = item.Quantity
};
// Get the information about this product
var productInfo = productData.GetProductById(detail.ProductId);
if (productInfo == null)
{
throw new Exception($"The product Id of {detail.ProductId} could not be found it the database.");
}
detail.PurchasePrice = (productInfo.RetailPrice * detail.Quantity);
if (productInfo.IsTaxable)
{
detail.Tax = (detail.PurchasePrice * taxRate);
}
saleDetails.Add(detail);
}
// Create the sale model
SaleDBModel saleToSave = new SaleDBModel
{
SubTotal = saleDetails.Sum(x => x.PurchasePrice),
Tax = saleDetails.Sum(x => x.Tax),
CachierId = userId
// Sale date is automaticaly added in db
};
saleToSave.Total = saleToSave.SubTotal + saleToSave.Tax;
#region Old - Replaced with transaction
//// Save sale model
//SqlDataAccess sql = new SqlDataAccess();
//sql.SaveData<SaleDBModel>("dbo.spSaleInsert", saleToSave, "TRMData");
////Get the ID from sale model // i dont want to have id in model because of identity increment
//var saleToSave_Id = sql.LoadData<int, dynamic>("spSaleLookup", new { CachierId = saleToSave.CachierId, SaleDate = saleToSave.SaleDate }, "TRMData").FirstOrDefault();
//// Finish filling in the sale detail models
//foreach (var item in saleDetails)
//{
// item.SaleId = saleToSave_Id;
// // Save the sale detail model
// sql.SaveData("dbo.spSaleDetailInsert", item, "TRMData");
//}
#endregion
using (SqlDataAccess sql = new SqlDataAccess()) // ok
{
try
{
sql.StartTransaction("TRMData");
// Save the sale model
sql.SaveDataInTransaction("dbo.spSaleInsert", saleToSave);
//Get the ID from sale model
var saleToSave_Id = sql.LoadDataInTransaction <int, dynamic>("spSaleLookup", new { CachierId = saleToSave.CachierId, SaleDate = saleToSave.SaleDate }).FirstOrDefault();
// Finish filling in the sale detail models
foreach (var item in saleDetails)
{
item.SaleId = saleToSave_Id;
// Save the sale detail model
sql.SaveDataInTransaction("dbo.spSaleDetailInsert", item);
}
sql.CommitTransaction();
}
catch (Exception exc)
{
sql.RollbackTransaction();
throw exc;
}
}
}
/// <summary>
/// Saves selling transaction to the database
/// </summary>
public void SaveSale(SellingTransactionModel saleInfo)
{
SellingTransactionMasterDBModel masterSaleData = new SellingTransactionMasterDBModel
{
SellingTransactionTypeId = saleInfo.SellingTransactionTypeId,
TransactionTiming = DateTime.Now,
Subtotal = saleInfo.Subtotal,
calculatedTaxesPercentage = saleInfo.CalculatedTaxesPercentage,
calculatedTaxesValue = saleInfo.CalculatedTaxesValue,
calculatedDiscountPercentage = saleInfo.CalculatedDiscountPercentage,
calculatedDiscountValue = saleInfo.CalculatedDiscountValue,
TaxesPercentageOverInvoice = saleInfo.TaxesPercentageOverInvoice,
TaxesValueOverInvoice = saleInfo.TaxesValueOverInvoice,
DiscountPercentageOverInvoice = saleInfo.DiscountPercentageOverInvoice,
DiscountValueOverInvoice = saleInfo.DiscountValueOverInvoice,
Total = saleInfo.Total,
SellerId = saleInfo.SellerId,
ShiftOwnerId = saleInfo.ShiftOwnerId,
CustomerId = saleInfo.CustomerId,
NumberOfItems = saleInfo.NumberOfItems,
NumberOfPieces = saleInfo.NumberOfPieces
};
using (SqlDataAccess sql = new SqlDataAccess())
{
try
{
sql.StartTransaction("GaroshaPrimoData");
//save the sale model
sql.SaveDataInTransaction("dbo.spSellingTransactionMaster_Insert", masterSaleData);
//get the id of the transaction
masterSaleData.TransactionId = sql.LoadDataInTransaction <int, dynamic>("dbo.spSellingTransactionMaster_GetId",
new { masterSaleData.SellerId, masterSaleData.TransactionTiming }).FirstOrDefault();
if (saleInfo.InvoicePayment.cash > 0)
{
CashPaymentDBModel cashPayment = new CashPaymentDBModel
{
SellingTransactionId = masterSaleData.TransactionId,
Amount = saleInfo.InvoicePayment.cash
};
sql.SaveDataInTransaction("dbo.spCashPayments_Insert", cashPayment);
}
if (saleInfo.InvoicePayment.bankCardsPayments.Length > 0)
{
for (int i = 0; i < saleInfo.InvoicePayment.bankCardsPayments.Length; i++)
{
saleInfo.InvoicePayment.bankCardsPayments[i].SellingTransactionId = masterSaleData.TransactionId;
sql.SaveDataInTransaction("dbo.spBankCardsPayments_Insert",
saleInfo.InvoicePayment.bankCardsPayments[i]);
}
}
if (saleInfo.InvoicePayment.contractorsPayments.Length > 0)
{
for (int i = 0; i < saleInfo.InvoicePayment.contractorsPayments.Length; i++)
{
saleInfo.InvoicePayment.contractorsPayments[i].SellingTransactionId = masterSaleData.TransactionId;
sql.SaveDataInTransaction("dbo.spContractorsPayments_Insert",
saleInfo.InvoicePayment.contractorsPayments[i]);
}
}
foreach (var item in saleInfo.SaleItems)
{
SellingTransactionItemDBModel saleItem = new SellingTransactionItemDBModel
{
SellingTransactionId = masterSaleData.TransactionId,
ItemId = item.ItemId,
ItemExpiryDate = item.ItemExpiryDate,
SellingTransactionItemQuantity = item.SellingTransactionItemQuantity,
ItemSellingPrice = item.ItemSellingPrice,
ItemSubtotal = item.ItemSubtotal,
ItemSellingDiscountPercentage = item.ItemSellingDiscountPercentage,
ItemSellingDiscountValue = item.ItemSellingDiscountValue,
ItemSellingTaxesPercentage = item.ItemSellingTaxesPercentage,
ItemSellingTaxesValue = item.ItemSellingTaxesValue,
ItemTotal = item.ItemTotal,
ItemCostOnSelling = item.ItemCostOnSelling
};
sql.SaveDataInTransaction("dbo.spSellingTransactionsItems_Insert", saleItem);
UpdateQuantitiesOnSelling(item, sql);
}
sql.CommitTransaction();
}
catch
{
sql.RollBackTransaction();
throw;
}
}
}
public void SaveSale(SaleModel saleInfo, string cashierId)
{
//TODO: Make this DRY/SOLID/Better
ProductData products = new ProductData();
var taxRate = ConfigHelper.GetTaxRate() / 100;
//Start filling the sale detail models we will save in the database
List <SaleDetailDBModel> details = new List <SaleDetailDBModel>();
foreach (var item in saleInfo.SaleDetails)
{
var detail = new SaleDetailDBModel
{
ProductId = item.ProductId,
Quantity = item.Quantity
};
//Get the information about this product
//Question: Why are we getting productInfo from DB?
//Answer: To validate calculations of taxes and purchace price at server side to
//get protection from probable bad behaviour of some users
var productInfo = products.GetProductById(detail.ProductId);
if (productInfo == null)
{
throw new Exception($"The Product Id of {detail.ProductId} could not be found in the database");
}
detail.PurchasePrice = (productInfo.RetailPrice * detail.Quantity);
if (productInfo.IsTaxable)
{
detail.Tax = (detail.PurchasePrice * taxRate);
}
details.Add(detail);
}
//Fill in the available information
//Create the sale model
SaleDBModel sale = new SaleDBModel
{
SubTotal = details.Sum(x => x.PurchasePrice),
Tax = details.Sum(x => x.Tax),
CashierId = cashierId
};
sale.Total = sale.SubTotal + sale.Tax;
using (SqlDataAccess sql = new SqlDataAccess())
{
try
{
sql.StartTransaction("PrimoData");
//Save the sale model
sql.SaveDataInTransaction("dbo.spSale_Insert", sale);
//Get the Id from the sale model
sale.Id = sql.LoadDataInTransaction <int, dynamic>("spSale_Lookup",
new { sale.CashierId, sale.SaleDate }).FirstOrDefault();
//Finish Filling in the sale detail models
foreach (var item in details)
{
item.SaleId = sale.Id;
//Save the sale detail models
sql.SaveDataInTransaction("dbo.spSaleDetail_Insert", item);
}
sql.CommitTransaction();
}
catch
{
sql.RollBackTransaction();
throw;
}
}
}
public void SaveSale(SaleModel saleInfo, string cashierId, List <SaleDetailDBModel> details)
{
//Start filling in the sale detail models we will save to the database
ProductData products = new ProductData();
var taxRate = ConfigHelper.GetTaxRate() / 100;
foreach (var item in saleInfo.SaleDetails)
{
var detail = new SaleDetailDBModel
{
ProductId = item.ProductId,
Quantity = item.Quantity
};
//Get the info about this product
var productInfo = products.GetProductById(detail.ProductId);
if (productInfo == null)
{
throw new Exception($"The product Id of {detail.ProductId} could not be found in the database");
}
detail.PurchasePrice = (productInfo.RetailPrice * detail.Quantity);
if (productInfo.IsTaxable)
{
detail.Tax = (detail.PurchasePrice * taxRate);
}
//update the products quantity in database
int refreshQuantity = productInfo.QuantityInStock - detail.Quantity;
products.UpdateProductQuantitity(detail.ProductId, refreshQuantity);
details.Add(detail);
}
//Create the Sale model
SaleDBModel sale = new SaleDBModel
{
SubTotal = details.Sum(x => x.PurchasePrice),
Tax = details.Sum(x => x.Tax),
CashierId = cashierId
};
sale.Total = sale.SubTotal + sale.Tax;
using (SqlDataAccess sql = new SqlDataAccess())
{
try
{
sql.StartTransaction("TRMData");
//Save the sale model
sql.SaveDataInTransaction("dbo.spSale_Insert", sale);
//Get the ID from the sale model
sale.Id = sql.LoadDataInTransaction <int, dynamic>("spSale_Lookup", new { sale.CashierId, sale.SaleDate }).FirstOrDefault();
//Finish filling in the sale detail models
foreach (var item in details)
{
item.SaleId = sale.Id;
//Save the sale detail models
sql.SaveDataInTransaction("dbo.spSaleDetail_Insert", item);
}
sql.CommitTransaction(); //using statement can also take care of doing it at the close of curly bracket
}
catch
{
sql.RollBackTransaction();
throw;
}
}
}
/// <summary>
/// saves buying transaction to the database
/// </summary>
public void SaveBuyingTransaction(BuyingTransactionModel buyingTransactionInfo)
{
BuyingTransactionMasterDBModel masterBuyingTransactionData = new BuyingTransactionMasterDBModel
{
TransactionTiming = DateTime.Now,
Subtotal = buyingTransactionInfo.Subtotal,
CalculatedTaxesPercentage = buyingTransactionInfo.CalculatedTaxesPercentage,
CalculatedTaxesValue = buyingTransactionInfo.CalculatedTaxesValue,
CalculatedRetailDiscountPercentage = buyingTransactionInfo.CalculatedRetailDiscountPercentage,
CalculatedRetailDiscountValue = buyingTransactionInfo.CalculatedRetailDiscountValue,
TaxesPercentageOverInvoice = buyingTransactionInfo.TaxesPercentageOverInvoice,
TaxesValueOverInvoice = buyingTransactionInfo.TaxesValueOverInvoice,
RetailDiscountPercentageOverInvoice = buyingTransactionInfo.RetailDiscountPercentageOverInvoice,
RetailDiscountValueOverInvoice = buyingTransactionInfo.RetailDiscountValueOverInvoice,
Total = buyingTransactionInfo.Total,
UserId = buyingTransactionInfo.UserId,
ShiftOwnerId = buyingTransactionInfo.ShiftOwnerId,
VendorId = buyingTransactionInfo.VendorId,
NumberOfPieces = buyingTransactionInfo.NumberOfPieces,
NumberOfItems = buyingTransactionInfo.NumberOfItems
};
using (SqlDataAccess sql = new SqlDataAccess())
{
try
{
sql.StartTransaction("GaroshaPrimoData");
sql.SaveDataInTransaction("dbo.spBuyingTransactionMaster_Insert", masterBuyingTransactionData);
masterBuyingTransactionData.TransactionId = sql.LoadDataInTransaction <int, dynamic>("dbo.spBuyingTransactionMaster_GetId",
null).FirstOrDefault();
foreach (var item in buyingTransactionInfo.BuyingTransactionItems)
{
BuyingTransactionItemDBModel buyingTransactionItem = new BuyingTransactionItemDBModel
{
BuyingTransactionId = masterBuyingTransactionData.TransactionId,
ItemId = item.ItemId,
ItemExpiryDate = item.ItemExpiryDate,
BuyingTransactionItemQuantity = item.BuyingTransactionItemQuantity,
ItemSellingPrice = item.ItemSellingPrice,
ItemBuyingDiscountPercentage = item.ItemBuyingDiscountPercentage,
ItemBuyingPrice = item.ItemBuyingPrice,
ItemSubtotal = item.ItemSubtotal,
ItemBuyingTaxesPercentage = item.ItemBuyingTaxesPercentage,
ItemBuyingTaxesValue = item.ItemBuyingTaxesValue,
ItemTotal = item.ItemTotal
};
sql.SaveDataInTransaction("dbo.spBuyingTransactionsItems_Insert", buyingTransactionItem);
UpdateQuantitiesOnBuying(buyingTransactionItem, sql);
}
sql.CommitTransaction();
}
catch
{
sql.RollBackTransaction();
throw;
}
}
}
public void SaveSale(SaleModel saleInfo, string userId)
{
// TODO: Make this SOLID/DRY/Btter
// Start filling in the sale detail models we will save to the database
List <SaleDetailDbModel> details = new List <SaleDetailDbModel>();
ProductData product = new ProductData(config);
var taxRate = ConfigHelper.GetTaxRate() / 100;
foreach (var item in saleInfo.SaleDetails)
{
var detail = new SaleDetailDbModel
{
ProductId = item.ProductId,
Quantity = item.Quantity
};
// Get the information about this product
var productInfo = product.GetProductById(detail.ProductId);
if (productInfo == null)
{
throw new Exception($"El producto con Id { detail.ProductId } no se encuentra en la base de datos.");
}
detail.PurchasePrice = (productInfo.RetailPrice * detail.Quantity);
if (productInfo.IsTaxable)
{
detail.Tax = (detail.PurchasePrice * taxRate);
}
details.Add(detail);
}
// Create the sale model
SaleDbModel sale = new SaleDbModel
{
SubTotal = details.Sum(x => x.PurchasePrice),
Tax = details.Sum(x => x.Tax),
CashierId = userId
};
sale.Total = sale.SubTotal + sale.Tax;
using (SqlDataAccess sql = new SqlDataAccess(config))
{
try
{
sql.StartTransaction("TRMData");
// Save the sale model
sql.SaveDataInTransaction("dbo.spSale_Insert", sale);
// Get the Id from the sale model
sale.Id = sql.LoadDataInTransaction <int, dynamic>("dbo.spSale_Lookup", new
{ CashierId = sale.CashierId, SaleDate = sale.SaleDate }).FirstOrDefault();
// Finish filling in the sale details model
foreach (var item in details)
{
item.SaleId = sale.Id;
// Save the sale detail model
sql.SaveDataInTransaction("dbo.spSaleDetail_Insert", item);
}
sql.CommitTransaction();
}
catch
{
sql.RollBackTransaction();
throw;
}
}
}
public void SaveSale(SaleModel saleInfo, string cashierId)
{
List <SaleDetailDBModel> details = new List <SaleDetailDBModel>();
ProductData products = new ProductData(_config);
var taxRate = ConfigHelper.GetTaxRate() / 100;
foreach (var item in saleInfo.SaleDetails)
{
var detail = new SaleDetailDBModel
{
ProductId = item.ProductId,
Quantity = item.Quantity
};
var productInfo = products.GetProductById(item.ProductId);
if (productInfo == null)
{
throw new Exception($"The product Id of {item.ProductId} could not be found in the database.");
}
detail.PurchasePrice = (productInfo.RetailPrice * detail.Quantity);
if (productInfo.IsTaxable)
{
detail.Tax = (detail.PurchasePrice * taxRate);
}
details.Add(detail);
}
SaleDBModel sale = new SaleDBModel
{
SubTotal = details.Sum(x => x.PurchasePrice),
Tax = details.Sum(x => x.Tax),
CashierId = cashierId
};
sale.Total = sale.SubTotal + sale.Tax;
//Wrap the code below in a transaction. So all of the process complete or none of it complete.
//This is to avoid having incomplete/corrupt data in the SQL server incase part of the process
//completes and another part fails.
//Uses a C# transaction, meaning C# will handle the opening and closing of the transaction.
//This is to be used sparingly. Most of the time you do not want to open transaction in C#
//Instead transaction should be open in the SQL side. As you can leave a connection open
//and forget to close it, making proformance to be reduced significantly.
//using make it so all of the calls and made together.
//The end of the using statement will close the connection and use the dispose method
//However, we place the commitTransaction at the end anyways to help visability
//if the transaction fail then we catch the failure and do a rollback.
using (SqlDataAccess sql = new SqlDataAccess(_config))
{
try
{
sql.StartTransaction("TSDatabase");
sql.SaveData("dbo.spSale_Insert", sale, "TSDatabase");
sale.Id = sql.LoadDataInTransaction <int, dynamic>("spSale_Lookup", new
{
sale.CashierId,
sale.SaleDate
}).FirstOrDefault();
foreach (var item in details)
{
item.SaleId = sale.Id;
sql.SaveDataInTransaction("dbo.spSaleDetail_Insert", item);
}
sql.CommitTransaction();
}
catch
{
sql.RollbackTransaction();
throw;
}
}
}
/// <summary>
/// Saves the sale model to database
/// </summary>
/// <param name="saleInfo">sale is a SaleModel received from the API</param>
public void SaveSale(SaleModel saleInfo, string cashierId)
{
// Note, We do not trust the frontend, therefore we don't get all the infos from there.
// TODO: Make this SOLID/DRY/Better
// Start filling in the Sale Detail models we will save to the database
List <SaleDetailDBModel> details = new List <SaleDetailDBModel>();
ProductData products = new ProductData();
var taxRate = ConfigHelper.GetTaxRate() / 100;
foreach (var item in saleInfo.SaleDetails)
{
var detail = new SaleDetailDBModel
{
ProductId = item.ProductId,
Quantity = item.Quantity,
};
//Get the info about this product
var productInfo = products.GetProductById(detail.ProductId);
// Fill in the available information (SaleId is not get yet.)
if (productInfo == null)
{
throw new Exception($"The product Id of {detail.ProductId} could not be found in the database.");
}
detail.PurchasePrice = (productInfo.RetailPrice * detail.Quantity);
if (productInfo.IsTaxable)
{
detail.Tax = (detail.PurchasePrice * taxRate);
}
details.Add(detail);
}
// Create the Sale model (Consits of several items in the shopping cart.)
SaleDBModel sale = new SaleDBModel
{
SubTotal = details.Sum(x => x.PurchasePrice),
Tax = details.Sum(x => x.Tax),
CashierId = cashierId
};
sale.Total = sale.SubTotal + sale.Tax;
// Saving into sale and saledetail tables happen in one transaction
using (SqlDataAccess sql = new SqlDataAccess())
{
try
{
sql.StartTransaction("TRMData");
// Save the Sale model
sql.SaveDataInTransaction("dbo.spSale_Insert", sale);
// Get the Sale ID from the Sale model - dbo.spSale_Insert has an output variable Id, but "sale.id" do not get it.
// Therefore an other query needs to be run.
sale.Id = sql.LoadDataInTransaction <int, dynamic>("spSale_Lookup", new { CashierId = sale.CashierId, SaleDate = sale.SaleDate }).FirstOrDefault();
// Finish filling the Sale detail models
foreach (var item in details)
{
item.SaleId = sale.Id;
// Save the sale detail models
sql.SaveDataInTransaction("dbo.spSaleDetail_Insert", item);
}
sql.CommitTransaction();
}
catch
{
sql.RollbackTransaction();
throw; // It throws the original exception, from the deep --> more info
}
}
}
public void SaveSale(SaleModel saleInfo, string cashierId)
{
//TODO: Improve - Make this SOLID/DRY
// start filling in sale detailmodels we will save to the database
List <SaleDetailDBModel> details = new List <SaleDetailDBModel>();
ProductData products = new ProductData();
var taxRate = ConfigHelper.GetTaxRate() / 100;
foreach (var item in saleInfo.SaleDetails)
{
var detail = new SaleDetailDBModel
{
ProductId = item.ProductId,
Quantity = item.Quantity
};
//get info about product
var productInfo = products.GetProductById(item.ProductId);
if (productInfo == null)
{
throw new Exception($"The product Id of {item.ProductId} come not be found in the database.");
}
detail.PurchasePrice = (productInfo.RetailPrice * detail.Quantity);
if (productInfo.IsTaxable)
{
detail.Tax = (detail.PurchasePrice * taxRate);
}
details.Add(detail);
}
//create sale model
SaleDBModel sale = new SaleDBModel
{
SubTotal = details.Sum(x => x.PurchasePrice),
Tax = details.Sum(x => x.Tax),
CashierId = cashierId
};
sale.Total = sale.SubTotal + sale.Tax;
using (SqlDataAccess sql = new SqlDataAccess())
{
try
{
sql.StartTransaction("TRMData");
//save sale model
sql.SaveDataInTransaction("dbo.spSale_Insert", sale);
//get id from sale model - assume we have it
sale.Id = sql.LoadDataInTransaction <int, dynamic>("spSale_Lookup", new { sale.CashierId, sale.SaleDate }).FirstOrDefault();
//finish filling in the sale detail models
foreach (var item in details)
{
item.SaleId = sale.Id;
//save the sale detail models
sql.SaveDataInTransaction("dbo.spSaleDetail_Insert", item);
}
sql.CommitTransaction();
}
catch
{
sql.RollbackTransaction();
throw;
}
};
}
public int saveALLCommentMDB(string MCID, string iDocDettailID, string cType, string sComment, string docNm, DateTime dueDt, int uID = -1, string ToUsers = "", string TaorLoan = "", string CCUsers = "") // mcid added on 27 dec
{
try
{
if (objConnection.Connection.State == ConnectionState.Closed)
{
objConnection.Connection.Open();
}
ToUsers = ToUsers.Replace("," + uID, "");
objConnection.BeginTransaction();
#region SAVE THE NEW DOCUMENT
SqlParameter[] newParam = new SqlParameter[] {
new SqlParameter("@userId", uID),
new SqlParameter("@messageDetails", sComment),
new SqlParameter("@action", "A"), // reply All
new SqlParameter("@docSendID", iDocDettailID), // SEND THE DOCUMENT DETAILS ID AND FIND THE PARENT FOR THE SAME
new SqlParameter("@docName ", docNm), new SqlParameter("@dueDate", dueDt), new SqlParameter("@mcid", MCID)
};
int getId = int.Parse(objConnection.ExecuteScalarQuery("sp_insertSendNewDocumentmdb", CommandType.StoredProcedure, newParam).ToString());
#endregion
#region SAVE ALL Users
if (getId != -1)
{
int defaultval = 0; // CC
int defaultval1 = 1; // TO
// CHECK FOR THE TO USERS
if (ToUsers != "")
{
SqlParameter[] pAllUsers = new SqlParameter[] {
new SqlParameter("@iDocID", getId),
new SqlParameter("@bToOrCC", defaultval1),
new SqlParameter("@vsUsrString", ToUsers),
new SqlParameter("@cTAStatus", TaorLoan),
new SqlParameter("@action", "A") // REPLY ALL
}; //, new SqlParameter("@bIsReply", defaultval) };// For To 1
objConnection.ExecuteNonQuery("sp_insertDocSendDetailsMDB", CommandType.StoredProcedure, pAllUsers);
}
//CHECK FOR CC USERS
if (CCUsers != "")
{
SqlParameter[] pCcUsers = new SqlParameter[] {
new SqlParameter("@iDocID", getId),
new SqlParameter("@bToOrCC", defaultval),
new SqlParameter("@vsUsrString", CCUsers),
new SqlParameter("@cTAStatus", TaorLoan),
new SqlParameter("@action", "A") // REPLY ALL
}; //, new SqlParameter("@bIsReply", defaultval) };// For To 1
objConnection.ExecuteNonQuery("sp_insertDocSendDetailsmdb", CommandType.StoredProcedure, pCcUsers);
}
}
else
{
objConnection.RollbackTransaction();
return(-1);
}
#endregion
objConnection.CommitTransaction();
return(getId);
}
catch (Exception ex)
{
return(-1);
}
}
public void SaveNewItem(NewItemModel newItem)
{
ItemMasterDBModel item = new ItemMasterDBModel
{
ItemNameEnglish = newItem.ItemNameEnglish,
ItemOtherName = newItem.ItemOtherName,
ProducerCompanyId = newItem.ProducerCompanyId,
ItemSellingPrice = newItem.ItemSellingPrice,
ItemBuyingDiscountPercentage = newItem.ItemBuyingDiscountPercentage,
ItemBuyingPrice = newItem.ItemBuyingPrice,
TaxesPercentageOnBuying = newItem.TaxesPercentageOnBuying,
TaxesValueOnBuying = newItem.TaxesValueOnBuying,
TaxesPercentageOnSelling = newItem.TaxesPercentageOnSelling,
TaxesValueOnSelling = newItem.TaxesValueOnSelling,
ItemDescription = newItem.ItemDescription,
CreatedDate = DateTime.Now,
LastModified = DateTime.Now,
ItemStatus = "active"
};
using (SqlDataAccess sql = new SqlDataAccess())
{
try
{
sql.StartTransaction("GaroshaPrimoData");
//save item master data
sql.SaveDataInTransaction("dbo.spItemMaster_Insert", item);
//get the new item's id
item.ItemId = sql.LoadDataInTransaction <int, dynamic>("dbo.spItemMaster_GetItemId",
null).FirstOrDefault();
if (newItem.ItemCodes.Length > 0)
{
for (int i = 0; i < newItem.ItemCodes.Length; i++)
{
ItemCodeDBModel itemCode = new ItemCodeDBModel
{
ItemId = item.ItemId,
ItemCode = newItem.ItemCodes[i]
};
sql.SaveDataInTransaction("dbo.spItemCode_Insert", itemCode);
}
}
if (newItem.TherapeuticClassesIds.Length > 0)
{
for (int x = 0; x < newItem.TherapeuticClassesIds.Length; x++)
{
ItemTherapeuticClassDBModel itemTherapeuticClass = new ItemTherapeuticClassDBModel
{
ItemId = item.ItemId,
TherapeuticClassId = newItem.TherapeuticClassesIds[x]
};
sql.SaveDataInTransaction("dbo.spItemTherapeuticClass_Insert", itemTherapeuticClass);
}
}
if (newItem.IngredientsIds.Length > 0)
{
for (int z = 0; z < newItem.IngredientsIds.Length; z++)
{
ItemIngredientDBModel itemIngredient = new ItemIngredientDBModel
{
ItemId = item.ItemId,
IngredientId = newItem.IngredientsIds[z]
};
sql.SaveDataInTransaction("dbo.spItemIngredient_Insert", itemIngredient);
}
}
sql.CommitTransaction();
}
catch
{
sql.RollBackTransaction();
throw;
}
}
}
public bool addDocument(string sFileName, string sContentType, byte[] readByteFile, int docSendID, int iParentDocID, string userForwardIDs = "", string docChkLst = "")
{
SqlDataAccess objConnection = new SqlDataAccess(clsConstant.DBCONSTRING);
try
{
if (objConnection.Connection.State == ConnectionState.Closed)
{
objConnection.Connection.Open();
}
objConnection.BeginTransaction();
if (docSendID != 0 && iParentDocID != 0)
{
if (sFileName != string.Empty && sContentType != string.Empty && readByteFile != null)
{
//Insert New File
SqlParameter[] param = new SqlParameter[] { new SqlParameter("@filename", sFileName),
new SqlParameter("@contenttype", sContentType),
new SqlParameter("@attachFile", readByteFile),
new SqlParameter("@iDocSendID", docSendID),
new SqlParameter("@iPDocID", iParentDocID),
new SqlParameter("@iForwardUsers", userForwardIDs), // Use file name as variable for storing Forward ID's
new SqlParameter("@iDocSendFid", docChkLst) };
objConnection.ExecuteNonQuery(clsConstant.SP_INSERT_DOC_SEND_FILE, CommandType.StoredProcedure, param);
}
else if ((sFileName == string.Empty && sContentType == string.Empty && readByteFile == null))
{
//Update "Forward" Details with null values
//Insert New File
SqlParameter[] param = new SqlParameter[] { new SqlParameter("@iDocSendID", docSendID),
new SqlParameter("@iPDocID", iParentDocID),
new SqlParameter("@iForwardUsers", userForwardIDs), // Use file name as variable for storing Forward ID's
new SqlParameter("@iDocSendFid", docChkLst) };
objConnection.ExecuteNonQuery(clsConstant.SP_INSERT_DOC_SEND_FILE, CommandType.StoredProcedure, param);
}
}
else // GO FOR REPLYALL
{
if (sFileName != string.Empty && sContentType != string.Empty && readByteFile != null)
{
//Insert New File
SqlParameter[] param = new SqlParameter[] { new SqlParameter("@filename", sFileName),
new SqlParameter("@contenttype", sContentType),
new SqlParameter("@attachFile", readByteFile),
new SqlParameter("@iDocSendID", docSendID),
new SqlParameter("@iPDocID", iParentDocID),
new SqlParameter("@iForwardUsers", userForwardIDs), // Use file name as variable for storing Forward ID's
new SqlParameter("@iDocSendFid", docChkLst) };
// STORE THE DOCUMENT ID
objConnection.ExecuteNonQuery(clsConstant.SP_INSERT_DOC_SEND_FILE, CommandType.StoredProcedure, param);
}
else if ((sFileName == string.Empty && sContentType == string.Empty && readByteFile == null)) // Forward Document Mapping
/*
* Insert document in database against a empty message forwarded, with previous document send
*/
{
SqlParameter[] param = new SqlParameter[] { new SqlParameter("@iDocSendID", docSendID),
new SqlParameter("@iPDocID", iParentDocID),
new SqlParameter("@iForwardUsers", userForwardIDs), // Use file name as variable for storing Forward ID's
new SqlParameter("@iDocSendFid", docChkLst) };
objConnection.ExecuteNonQuery(clsConstant.SP_INSERT_DOC_SEND_FILE, CommandType.StoredProcedure, param);
}
//// UPLOAD OLD Way THE ATTACHEMENT
//if (sFileName != string.Empty && sContentType != string.Empty && readByteFile != null)
//{
// //Insert New File
// SqlParameter[] param = new SqlParameter[] { new SqlParameter("@filename", sFileName),
// new SqlParameter("@contenttype", sContentType),
// new SqlParameter("@attachFile", readByteFile),
// new SqlParameter("@iDocSendID", docSendID)
// };
// objConnection.ExecuteNonQuery(clsConstant.SP_INSERT_DOC_SEND_FILE, CommandType.StoredProcedure, param);
//}
//else if ((sFileName == string.Empty && sContentType == string.Empty && readByteFile == null))
//{
// //Update "Forward" Details with null values
// //Insert New File
// SqlParameter[] param = new SqlParameter[] { new SqlParameter("@iDocSendID", docSendID),
// };
// objConnection.ExecuteNonQuery(clsConstant.SP_INSERT_DOC_SEND_FILE, CommandType.StoredProcedure, param);
//}
}
objConnection.CommitTransaction();
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
//public List<ProductModel> GetProducts()
//{
// SqlDataAccess sql = new SqlDataAccess();
// var output = sql.LoadData<ProductModel, dynamic>("dbo.spProduct_GetAll", new { }, "TRMDATA");
// return output;
//}
public void SaveSale(SaleModel saleInfo, string cashierId)
{
//TODO: Make this SOLID/DRY/Better
// Start filling int the models we will save to the database
List <SaleDetailDBModel> details = new List <SaleDetailDBModel>();
ProductData product = new ProductData();
var taxRate = ConfigHelper.GetTaxRate();
foreach (var item in saleInfo.SaleDetails)
{
var detail = new SaleDetailDBModel
{
ProductId = item.ProductId,
Quantity = item.Quantity
};
// Get the information about this product
var productInfo = product.GetProductById(detail.ProductId);
if (productInfo == null)
{
throw new Exception($"The product Id of {detail.ProductId} could not be found in the database.");
}
detail.PurchasePrice = (productInfo.RetailPrice * detail.Quantity);
if (productInfo.IsTaxable)
{
detail.Tax = (detail.PurchasePrice * (taxRate / 100));
}
details.Add(detail);
}
// Fill in the available information
// Create the Sale model
SaleDBModel sale = new SaleDBModel
{
SubTotal = details.Sum(x => x.PurchasePrice),
Tax = details.Sum(x => x.Tax),
UserId = cashierId
};
sale.Total = sale.SubTotal + sale.Tax;
// NEW WAY WITH TRANSACTION:
using (SqlDataAccess sql = new SqlDataAccess())
{
try
{
sql.StartTransaction("TRMData");
// Save the Sale model
sql.SaveDataInTransaction("dbo.spSale_Insert", sale);
// Get the ID from the Sale Model
sale.Id = sql.LoadDataInTransaction <int, dynamic>("spSale_Lookup", new { CashierId = sale.UserId, sale.SaleDate }).FirstOrDefault();
// Finish filling in the Sale Detail Models
foreach (var item in details)
{
item.SaleId = sale.Id;
// Save the Sale Detail Models
sql.SaveDataInTransaction("dbo.spSaleDetail_Insert", item);
}
sql.CommitTransaction();
}
catch
{
sql.RollbackTransaction();
throw;
}
}
//// OLD WAY WITHOUT TRANSACTION:
// Save the Sale model
//SqlDataAccess sql = new SqlDataAccess();
//sql.SaveData("dbo.spSale_Insert", sale, "TRMData");
//// Get the ID from the Sale Model
//sale.Id = sql.LoadData<int, dynamic>("spSale_Lookup", new { CashierId = sale.UserId, sale.SaleDate }, "TRMData").FirstOrDefault();
//// Finish filling in the Sale Detail Models
//foreach (var item in details)
//{
// item.SaleId = sale.Id;
// // Save the Sale Detail Models
// sql.SaveData("dbo.spSaleDetail_Insert", item, "TRMData");
//}
}
public void SaveSale(SaleModel saleInfo, string cashierId)
{
// TODO: make this SOLID / DRY / better
// Start filling in the saleInfo detail models we will persist
var details = new List <SaleDetailDbModel>();
var products = new ProductData();
decimal taxRate = (decimal)ConfigHelper.GetTaxRate() / 100;
foreach (var item in saleInfo.SaleDetails)
{
var detail = new SaleDetailDbModel
{
ProductId = item.ProductId,
Quantity = item.Quantity
};
// Get product information
var productInfo = products.GetProductById(detail.ProductId);
if (productInfo == null)
{
throw new Exception($"The product Id of {detail.ProductId} could not be found in the database.");
}
detail.PurchasePrice = (productInfo.RetailPrice * detail.Quantity);
if (productInfo.IsTaxable)
{
detail.Tax = (detail.PurchasePrice * taxRate);
}
details.Add(detail);
}
// Create the SaleModel
SaleDbModel sale = new SaleDbModel
{
SubTotal = details.Sum(x => x.PurchasePrice),
TaxTotal = details.Sum(x => x.Tax),
CashierId = cashierId
};
sale.Total = sale.SubTotal + sale.TaxTotal;
// Save the SaleModel
using (var sql = new SqlDataAccess())
{
try
{
sql.StartTransaction("ARMData");
sql.SaveDataInTransaction <SaleDbModel>("dbo.spSale_Insert", sale);
// Get the id for the new sale
sale.Id = sql
.LoadDataInTransaction <int, dynamic>("dbo.spSale_Lookup", new { sale.CashierId, sale.SaleDate })
.FirstOrDefault();
// Finish filling in the saleInfo detail and save the saleInfo detail models
foreach (var item in details)
{
item.SaleId = sale.Id;
sql.SaveDataInTransaction("dbo.spSaleDetail_Insert", item);
}
sql.CommitTransaction();
}
catch
{
// TODO: notify the user
sql.RollbackTransaction();
throw;
}
}
}