Ejemplo n.º 1
0
        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;
                }
            }
        }
Ejemplo n.º 3
0
        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");*/
        }
Ejemplo n.º 4
0
        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;
            }
        }
Ejemplo n.º 5
0
        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;
            }
        }
Ejemplo n.º 6
0
        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;
            }
        }
Ejemplo n.º 7
0
        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;
                }
            }
        }
Ejemplo n.º 8
0
        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);
        }
Ejemplo n.º 9
0
        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;
                }
            }
        }
Ejemplo n.º 10
0
        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;
                }
            }
        }
Ejemplo n.º 11
0
        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;
                }
            }
        }
Ejemplo n.º 12
0
        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;
                }
            }
        }
Ejemplo n.º 14
0
        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;
                }
            }
        }
Ejemplo n.º 15
0
        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;
                }
            }
        }
Ejemplo n.º 16
0
        /// <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;
                }
            }
        }
Ejemplo n.º 17
0
        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;
                }
            }
        }
Ejemplo n.º 18
0
        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;
                }
            }
        }
Ejemplo n.º 19
0
        /// <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
                }
            }
        }
Ejemplo n.º 20
0
        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;
                }
            };
        }
Ejemplo n.º 21
0
        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);
            }
        }
Ejemplo n.º 22
0
        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;
                }
            }
        }
Ejemplo n.º 23
0
        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;
            }
        }
Ejemplo n.º 24
0
        //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");
            //}
        }
Ejemplo n.º 25
0
        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;
                }
            }
        }