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; } } }