C# LumexDBPlayer.ExecuteDataTable方法代码示例

        public DataTable SaveJournalVoucher(string SalesCenter,DataTable dtJournalList, LumexDBPlayer db)
        {
            try
            {
                decimal amt = -1;
                int transactionNo = 0;

                db.AddParameters("@Flag", "JV");
                DataTable dtJournalNumber = db.ExecuteDataTable("GET_AND_UPDATE_AUTO_SERIAL_NUMBER_BY_FLAG", true);

                string journalNumber = dtJournalNumber.Rows[0][0].ToString();

                for (int i = 0; i < dtJournalList.Rows.Count; i++)
                {
                    db.ClearParameters();
                    db.AddParameters("@JournalNumber", journalNumber.Trim());
                    db.AddParameters("@TransactionNumber", dtJournalList.Rows[i]["SN"].ToString());
                    db.AddParameters("@ManualVoucherNumber", dtJournalList.Rows[i]["VoucherNumber"].ToString());
                    db.AddParameters("@AccountId", dtJournalList.Rows[i]["AccountHead"].ToString());
                    db.AddParameters("@DebitCredit", dtJournalList.Rows[i]["DebitCredit"].ToString());
                    db.AddParameters("@Amount", dtJournalList.Rows[i]["Amount"].ToString());
                    db.AddParameters("@Bank", dtJournalList.Rows[i]["Bank"].ToString());
                    db.AddParameters("@BankBranch", dtJournalList.Rows[i]["BankBranch"].ToString());
                    db.AddParameters("@ChequeNumber", dtJournalList.Rows[i]["ChequeNumber"].ToString());
                    db.AddParameters("@ChequeDate", dtJournalList.Rows[i]["ChequeDate"].ToString());
                    db.AddParameters("@PayToFromCompany", dtJournalList.Rows[i]["PayToFromCompany"].ToString());
                    db.AddParameters("@Narration", dtJournalList.Rows[i]["Narration"].ToString());
                    db.AddParameters("@OfficeBranchId", SalesCenter);
                    db.AddParameters("@CreatedBy", LumexSessionManager.Get("ActiveUserId").ToString());
                    db.AddParameters("@CreatedFrom", LumexLibraryManager.GetTerminal());

                    DataTable dt = db.ExecuteDataTable("INSERT_JOURNAL_VOUCHER", true);

                    if (amt < decimal.Parse(dtJournalList.Rows[i]["Amount"].ToString()))
                    {
                        amt = decimal.Parse(dtJournalList.Rows[i]["Amount"].ToString());
                        transactionNo = int.Parse(dtJournalList.Rows[i]["SN"].ToString());
                    }
                }

                db.ClearParameters();
                db.AddParameters("@JournalNumber", journalNumber.Trim());
                db.AddParameters("@TransactionNumber", transactionNo);
                db.ExecuteNonQuery("INSERT_JOURNAL_VOUCHER_INTO_GENERAL_LEDGER_APPROVAL_BY_JOURNAL", true);

                return dtJournalNumber;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                dtJournalList = null;
            }
        }

        public string SaveProductTransferRequisition(List<ProductTransferRequisitionBLL> productTransferRequisitions, string transferType, string transferFrom, string transferTo, string narration, LumexDBPlayer db)
        {
            string transferRequisitionId = string.Empty;

            try
            {
                db.ClearParameters();
                db.AddParameters("@TransferType", transferType.Trim());
                db.AddParameters("@TransferFrom", transferFrom.Trim());
                db.AddParameters("@TransferTo", transferTo.Trim());
                db.AddParameters("@Narration", narration.Trim());
                db.AddParameters("@CreatedBy", LumexSessionManager.Get("ActiveUserId").ToString());
                db.AddParameters("@CreatedFrom", LumexLibraryManager.GetTerminal());

                DataTable dt = db.ExecuteDataTable("INSERT_PRODUCT_TRANSFER_REQUISITION", true);

                if (dt.Rows.Count > 0)
                {
                    transferRequisitionId = dt.Rows[0][0].ToString();

                    for (int i = 0; i < productTransferRequisitions.Count; i++)
                    {
                        db.ClearParameters();
                        db.AddParameters("@TransferRequisitionId", transferRequisitionId.Trim());
                        db.AddParameters("@ProductId", productTransferRequisitions[i].ProductId.Trim());
                        db.AddParameters("@RequisitionQuantity", productTransferRequisitions[i].RequisitionQuantity.Trim());
                        db.AddParameters("@RequiredDate", productTransferRequisitions[i].RequiredDate.Trim());
                        db.AddParameters("@Narration", productTransferRequisitions[i].ProductNarration.Trim());

                        db.ExecuteNonQuery("INSERT_PRODUCT_TRANSFER_REQUISITION_PRODUCT", true);
                    }
                }
                else
                {
                    db.ExecuteDataTable("there_is_no_procedure_to_execute", false);
                }

                return transferRequisitionId;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                productTransferRequisitions = null;
            }
        }

        public DataTable SaveChartOfAccount(ChartOfAccountBLL chartOfAccount, LumexDBPlayer db)
        {
            try
            {
                db.AddParameters("@AccountName", chartOfAccount.AccountName.Trim());
                db.AddParameters("@AccountType", chartOfAccount.AccountType.Trim());
                db.AddParameters("@TotallingAccountNumber", chartOfAccount.TotallingAccountNumber.Trim());
                db.AddParameters("@IsPosted", chartOfAccount.IsPosted.Trim());
                db.AddParameters("@UseAs", chartOfAccount.UseAs.Trim());
                db.AddParameters("@BankAccountNumber", chartOfAccount.BankAccountNumber.Trim());
                db.AddParameters("@Description", chartOfAccount.Description.Trim());
                db.AddParameters("@CreatedBy", LumexSessionManager.Get("ActiveUserId").ToString());
                db.AddParameters("@CreatedFrom", LumexLibraryManager.GetTerminal());
                db.AddParameters("@OfficeBranchId", "");

                DataTable dt = db.ExecuteDataTable("INSERT_CHART_OF_ACCOUNT", true);

                return dt;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                chartOfAccount = null;
            }
        }

        public bool CheckDuplicateChartOfAccount(ChartOfAccountBLL chartOfAccount, LumexDBPlayer db)
        {
            bool status = false;

            try
            {
                db.AddParameters("@AccountType", chartOfAccount.AccountType.Trim());
                db.AddParameters("@AccountName", chartOfAccount.AccountName.Trim());
                db.AddParameters("@TotallingAccountNumber", chartOfAccount.TotallingAccountNumber.Trim());

                DataTable dt = db.ExecuteDataTable("CHECK_DUPLICATE_CHART_OF_ACCOUNT", true);

                if (dt.Rows.Count > 0)
                {
                    status = true;
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                chartOfAccount = null;
            }

            return status;
        }

        public DataTable SaveCreditVoucherCheque(DebitCreditVoucherBLL debitCreditVoucher, LumexDBPlayer db)
        {
            try
            {
                db.AddParameters("@ManualVoucherNumber", debitCreditVoucher.ManualVoucherNumber.Trim());
                db.AddParameters("@AccountId", debitCreditVoucher.AccountId.Trim());
                db.AddParameters("@CounterAccountId", debitCreditVoucher.CounterAccountId.Trim());
                db.AddParameters("@Amount", debitCreditVoucher.Amount.Trim());
                db.AddParameters("@Bank", debitCreditVoucher.Bank.Trim());
                db.AddParameters("@BankBranch", debitCreditVoucher.BankBranch.Trim());
                db.AddParameters("@ChequeNumber", debitCreditVoucher.ChequeNumber.Trim());
                db.AddParameters("@ChequeDate", debitCreditVoucher.ChequeDate.Trim());
                db.AddParameters("@PayToFromCompany", debitCreditVoucher.PayToFromCompany.Trim());
                db.AddParameters("@Narration", debitCreditVoucher.Narration.Trim());
                db.AddParameters("@OfficeBranchId", debitCreditVoucher.SalesCenterId);
                db.AddParameters("@CreatedBy", LumexSessionManager.Get("ActiveUserId").ToString());
                db.AddParameters("@CreatedFrom", LumexLibraryManager.GetTerminal());

                DataTable dt = db.ExecuteDataTable("INSERT_CREDIT_VOUCHER_CHEQUE", true);
                return dt;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                debitCreditVoucher = null;
            }
        }

        public DataTable SaveUser(UserBLL user, LumexDBPlayer db)
        {
            try
            {
                db.AddParameters("@UserId", user.UserId.Trim());
                db.AddParameters("@SalesCenterId", user.SalesCenterId.Trim());
                db.AddParameters("@WareHouseId", user.warehouseId.Trim());
                db.AddParameters("@UserName", user.UserName.Trim());
                db.AddParameters("@EmployeeId", user.EmployeeId.Trim());
                db.AddParameters("@UserGroupId", user.UserGroupId.Trim());
                db.AddParameters("@Department", user.Department.Trim());
                db.AddParameters("@Designation", user.Designation.Trim());
                db.AddParameters("@Address", user.Address.Trim());
                db.AddParameters("@ContactNumber", user.ContactNumber.Trim());
                db.AddParameters("@Email", user.Email.Trim());
                db.AddParameters("@NationalId", user.NationalId.Trim());
                db.AddParameters("@PassportNumber", user.PassportNumber.Trim());
                db.AddParameters("@Password", ProtectPassword(user.Password.Trim()));
                db.AddParameters("@CreatedBy", LumexSessionManager.Get("ActiveUserId").ToString());
                db.AddParameters("@CreatedFrom", LumexLibraryManager.GetTerminal());

                DataTable dt = db.ExecuteDataTable("INSERT_USER", true);

                return dt;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                user = null;
            }
        }

        public void SaveUserSalesCentersByUserId(string userId, List<string> salesCenters, LumexDBPlayer db)
        {
            try
            {
                db.AddParameters("@UserId", userId);
                db.ExecuteNonQuery("DELETE_USER_SALES_CENTERS_BY_USER_ID", true);

                for (int i = 0; i < salesCenters.Count; i++)
                {
                    db.ClearParameters();
                    db.AddParameters("@UserId", userId);
                    db.AddParameters("@SalesCenterId", salesCenters[i].ToString());
                    db.AddParameters("@CreatedBy", LumexSessionManager.Get("ActiveUserId").ToString());
                    db.AddParameters("@CreatedFrom", LumexLibraryManager.GetTerminal());

                    db.ExecuteDataTable("INSERT_USER_SALES_CENTERS_BY_USER_ID", true);
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                salesCenters = null;
            }
        }

        public DataTable SaveSalesPerson(SalesPersonBLL salesPerson, LumexDBPlayer db)
        {
            try
            {
                db.AddParameters("@SalesPersonName", salesPerson.SalesPersonName.Trim());
                db.AddParameters("@Address", salesPerson.Address.Trim());
                db.AddParameters("@PostalCode", salesPerson.PostalCode.Trim());
                db.AddParameters("@City", salesPerson.City.Trim());
                db.AddParameters("@District", salesPerson.District.Trim());
                db.AddParameters("@Country", salesPerson.Country.Trim());
                db.AddParameters("@NationalId", salesPerson.NationalId.Trim());
                db.AddParameters("@PassportNumber", salesPerson.PassportNumber.Trim());
                db.AddParameters("@ContactNumber", salesPerson.ContactNumber.Trim());
                db.AddParameters("@Email", salesPerson.Email.Trim());
                db.AddParameters("@JoiningSalesCenterId", salesPerson.JoiningSalesCenterId.Trim());
                db.AddParameters("@WorkingSalesCenterId", salesPerson.WorkingSalesCenterId.Trim());
                db.AddParameters("@JoinDate", salesPerson.JoinDate.Trim());
                db.AddParameters("@CreatedBy", LumexSessionManager.Get("ActiveUserId").ToString());
                db.AddParameters("@CreatedFrom", LumexLibraryManager.GetTerminal());

                DataTable dt = db.ExecuteDataTable("INSERT_SALES_PERSON", true);
                return dt;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                salesPerson = null;
            }
        }

        public DataTable SaveMenu(AppMenuBLL appMenu, LumexDBPlayer db)
        {
            try
            {
                db.AddParameters("@MenuName", appMenu.MenuName.Trim());
                db.AddParameters("@DisplayName", appMenu.DisplayName.Trim());
                db.AddParameters("@MenuTarget", appMenu.MenuTarget.Trim());
                db.AddParameters("@ImageUrl", appMenu.ImageUrl.Trim());
                db.AddParameters("@ToolTipDescription", appMenu.ToolTipDescription.Trim());
                db.AddParameters("@ParentMenuId", appMenu.ParentMenuId.Trim());
                db.AddParameters("@URL", appMenu.URL.Trim());
                db.AddParameters("@MenuGroupId", appMenu.MenuGroupId.Trim());
                db.AddParameters("@MenuType", appMenu.MenuType.Trim());
                db.AddParameters("@MenuForApp", appMenu.MenuForApp.Trim());
                db.AddParameters("@MenuLevel", appMenu.MenuLevel.Trim());
                db.AddParameters("@IsDefault", appMenu.IsDefault.Trim());
                db.AddParameters("@IsDisplay", appMenu.IsDisplay.Trim());
                db.AddParameters("@IsSubParent", appMenu.IsSubParent.Trim());

                DataTable dt = db.ExecuteDataTable("INSERT_MENU", true);

                return dt;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                appMenu = null;
            }
        }

        public DataTable SavePayToFromCompany(PayToFromCompanyBLL payToFromCompany, LumexDBPlayer db)
        {
            try
            {
                db.AddParameters("@CompanyName", payToFromCompany.CompanyName);
                db.AddParameters("@Description", payToFromCompany.Description);
                db.AddParameters("@CompanyAddress", payToFromCompany.CompanyAddress);
                db.AddParameters("@CompanyContact", payToFromCompany.CompanyContact);
                db.AddParameters("@CompanyEmail", payToFromCompany.CompanyEmail);
                db.AddParameters("@CompanyWeb", payToFromCompany.CompanyWebsite);
                db.AddParameters("@CreatedBy", LumexSessionManager.Get("ActiveUserId").ToString());
                db.AddParameters("@CreatedFrom", LumexLibraryManager.GetTerminal());

                DataTable dt = db.ExecuteDataTable("INSERT_PAY_TO_FROM_COMPANY", true);
                return dt;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                payToFromCompany = null;
            }
        }

        public static bool ValidateUser(UserBLL user, LumexDBPlayer db)
        {
            bool isValid = false;

            try
            {
                db.AddParameters("@UserId", user.UserId);

                DataTable dt = db.ExecuteDataTable("GET_USER_PASS_ID_AT_LOGIN", true);

                DataTableReader dtr = dt.CreateDataReader();

                if (dtr.Read())
                {
                    if (ProtectPassword(user.Password) == dtr["Password"].ToString())
                    {
                        isValid = true;
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                user = null;
            }

            return isValid;
        }

        public DataTable SaveCustomer(CustomerBLL customer, LumexDBPlayer db)
        {
            try
            {
                db.AddParameters("@CustomerName", customer.CustomerName.Trim());
                db.AddParameters("@ContactNumber", customer.ContactNumber.Trim());
                db.AddParameters("@Email", customer.Email.Trim());
                db.AddParameters("@Country", customer.Country.Trim());
                db.AddParameters("@City", customer.City.Trim());
                db.AddParameters("@District", customer.District.Trim());
                db.AddParameters("@PostalCode", customer.PostalCode.Trim());
                db.AddParameters("@Address", customer.Address.Trim());
                db.AddParameters("@NationalId", customer.NationalId.Trim());
                db.AddParameters("@AdditionalInfo", customer.Description.Trim());
                db.AddParameters("@PassportNumber", customer.PassportNumber.Trim());
                db.AddParameters("@JoiningSalesCenterId", customer.JoiningSalesCenterId.Trim());
                db.AddParameters("@CreatedBy", LumexSessionManager.Get("ActiveUserId").ToString());
                db.AddParameters("@CreatedFrom", LumexLibraryManager.GetTerminal());

                DataTable dt = db.ExecuteDataTable("INSERT_CUSTOMER", true);
                return dt;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                customer = null;
            }
        }

        public ReportDocument GetVendorListByActivationStatus(string sortedBy, string sortingOrder, string isActive, LumexDBPlayer db)
        {
            try
            {
                LumexSessionManager.Add("rptName", "Vendor_List");
                ReportDocument reportDocument = new ReportDocument();

                db.AddParameters("@SortedBy", sortedBy.Trim());
                db.AddParameters("@SortingOrder", sortingOrder.Trim());
                db.AddParameters("@IsActive", isActive.Trim());

                if (isActive == "All")
                {
                    reportDocument.Load(reportPath + "GET_VENDORS.rpt");
                }
                else
                {
                    reportDocument.Load(reportPath + "GET_VENDORS_BY_ACTIVATION_STATUS.rpt");
                }

                reportDocument.SetDataSource(db.ExecuteDataTable("REPORT_GET_VENDORS_BY_ACTIVATION_STATUS", true));

                return reportDocument;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public DataTable SaveSalesCenter(SalesCenterBLL salesCenter, LumexDBPlayer db)
        {
            try
            {
                db.AddParameters("@SalesCenterName", salesCenter.SalesCenterName.Trim());
                db.AddParameters("@Address", salesCenter.Address.Trim());
                db.AddParameters("@Country", salesCenter.Country.Trim());
                db.AddParameters("@City", salesCenter.City.Trim());
                db.AddParameters("@District", salesCenter.District.Trim());
                db.AddParameters("@PostalCode", salesCenter.PostalCode.Trim());
                db.AddParameters("@Phone", salesCenter.Phone.Trim());
                db.AddParameters("@Mobile", salesCenter.Mobile.Trim());
                db.AddParameters("@Fax", salesCenter.Fax.Trim());
                db.AddParameters("@Email", salesCenter.Email.Trim());
                db.AddParameters("@WarehouseId", salesCenter.WarehouseId.Trim());
                db.AddParameters("@CreatedBy", LumexSessionManager.Get("ActiveUserId").ToString());
                db.AddParameters("@CreatedFrom", LumexLibraryManager.GetTerminal());

                DataTable dt = db.ExecuteDataTable("INSERT_SALES_CENTER", true);
                return dt;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                salesCenter = null;
            }
        }

        public void SaveProductVendorsByProductId(string productId, List<string> vendors, LumexDBPlayer db)
        {
            try
            {
                db.AddParameters("@ProductId", productId);
                db.ExecuteNonQuery("DELETE_PRODUCT_VENDORS_BY_PRODUCT_ID", true);

                for (int i = 0; i < vendors.Count; i++)
                {
                    db.ClearParameters();
                    db.AddParameters("@ProductId", productId);
                    db.AddParameters("@VendorId", vendors[i].ToString());
                    db.AddParameters("@CreatedBy", LumexSessionManager.Get("ActiveUserId").ToString());
                    db.AddParameters("@CreatedFrom", LumexLibraryManager.GetTerminal());

                    db.ExecuteDataTable("INSERT_PRODUCT_VENDORS_BY_PRODUCT_ID", true);
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                vendors = null;
            }
        }