C# ConnectionProvider.OpenConnection方法代码示例

        public void DeleteRedemption(int nTransactionID, int qtyOfItemToRestoreAfterDelete)
        {
            TblRewardsTransaction rewardTrans = new TblRewardsTransaction();
            rewardTrans.NTransactionID = nTransactionID;
            rewardTrans.SelectOne();

            if (rewardTrans.NTypeID != 2 || (Convert.ToDateTime(rewardTrans.DtDate.Value).Date != DateTime.Now.Date && ACMSLogic.User.RightsLevelID != 9000))
                throw new Exception("You have no right to delete this record. It is not a redemption or the redemption date is before today. ");

            string itemCode = rewardTrans.StrReferenceNo.IsNull ? "" : rewardTrans.StrReferenceNo.Value;

            //TblProductInventory productInvent = new TblProductInventory();
            //productInvent.StrProductCode = itemCode;
            //productInvent.SelectOne();

            //productInvent.NQuantity = ACMS.Convert.ToInt32(productInvent.NQuantity) + qtyOfItemToRestoreAfterDelete;

            ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();

            try
            {

                rewardTrans.MainConnectionProvider = connProvider;
                //productInvent.MainConnectionProvider = connProvider;

                connProvider.OpenConnection();
                connProvider.BeginTransaction("DeleteRedemption");
                rewardTrans.Delete();
                //productInvent.Update();

                connProvider.CommitTransaction();
            }
            catch (Exception)
            {
                connProvider.RollbackTransaction("DeleteRedemption");
                throw;
            }
            finally
            {
                if (connProvider.CurrentTransaction != null)
                    connProvider.CurrentTransaction.Dispose();

                if (connProvider.DBConnection != null)
                {
                    if (connProvider.DBConnection.State == ConnectionState.Open)
                        connProvider.DBConnection.Close();
                }
                rewardTrans.MainConnactionIsCreatedLocal = true;
                //productInvent.MainConnactionIsCreatedLocal = true;
            }
        }

        public bool Delete(int nCaseID)
        {
            ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();

            try
            {
                myCase.MainConnectionProvider = connProvider;
                myCaseAction.MainConnectionProvider = connProvider;

                connProvider.OpenConnection();
                connProvider.BeginTransaction("CancelMemberPackage");

                myCase.NCaseID = nCaseID;
                myCaseAction.NCaseID = nCaseID;

                myCaseAction.DeleteAllWnCaseIDLogic();
                myCase.Delete();

                connProvider.CommitTransaction();
                return true;
            }
            catch (Exception)
            {
                connProvider.RollbackTransaction("DeleteCase");
                throw;
            }
            finally
            {
                if (connProvider.CurrentTransaction != null)
                    connProvider.CurrentTransaction.Dispose();
                if (connProvider.DBConnection != null)
                {
                    if (connProvider.DBConnection.State == ConnectionState.Open)
                        connProvider.DBConnection.Close();
                    //connProvider.DBConnection.Dispose();
                }
                myCase.MainConnactionIsCreatedLocal = true;
                myCaseAction.MainConnactionIsCreatedLocal = true;
            }
        }

        //2006
        // public DataTable SaveIntroduceFriendNew(string strMembershipID, string strIntroducerID, string strPackage, string strRemark, int employeeID)
        public bool SaveIntroduceFriendNew(string strMembershipID, string strIntroducerID, string strPackage, string strRemark, int employeeID,int nPackageID)
        {
            ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();
            TblMember sqlMember = new TblMember();
            TblMemberPackage sqlMemberPackage = new TblMemberPackage();
            TblPackage sqlPackage = new TblPackage();
            try
            {

                sqlMember.MainConnectionProvider = connProvider;
                connProvider.OpenConnection();
                connProvider.BeginTransaction("SaveIntroduceFriend");

              //  sqlMember.UpdateIntroducerMembershipID(strMembershipID, strIntroducerID);
                sqlMember.UpdateIntroducerMembershipID(strIntroducerID, strMembershipID);
                MemberPackage.CreateFreeMemberSpaPackage_IntroFriend(strMembershipID, strPackage, strRemark, ref nPackageID);
               // MemberPackage.CreateFreeMemberSpaPackage_IntroFriend(strIntroducerID, strPackage, strRemark, ref nPackageID);

                connProvider.CommitTransaction();
                return true;
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                if (connProvider.CurrentTransaction != null)
                    connProvider.CurrentTransaction.Dispose();
                if (connProvider.DBConnection != null)
                {
                    if (connProvider.DBConnection.State == ConnectionState.Open)
                        connProvider.DBConnection.Close();
                    connProvider.DBConnection.Dispose();
                }
                sqlPackage.Dispose();
                sqlMember.Dispose();
            }
        }

        public bool SavePOS(ACMSLogic.POS pos)
        {
            ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();
            //ACMSDAL.ConnectionProvider connProvider1 = new ConnectionProvider();

            try
            {
                DataTable branchReceiptNoTable = new DataTable();
                int latestReceiptNo = GetLatestReceiptNo(connProvider, pos, ref branchReceiptNoTable) + 1;
                string receiptNo = pos.StrBranchCode.ToString().Trim() + pos.NTerminalID.ToString().Trim() + latestReceiptNo.ToString().Trim();

                pos.StrReceiptNo = receiptNo;

                //Derek Instalment Plan Need to Add PaymentPlan Table Here
                //Get pos.NCategoryID here to determine if the item is Pay O/S
                //Here Can get the new Receipt No Value
                //3 Main table that always need to update

                TblReceipt receipt = new TblReceipt();
                TblReceiptEntries receiptEntries = new TblReceiptEntries();
                TblReceiptPayment receiptPayment = new TblReceiptPayment();
                TblPaymentPlan myPaymentPlan = new TblPaymentPlan();

                receipt.MainConnectionProvider = connProvider;
                receiptEntries.MainConnectionProvider = connProvider;
                receiptPayment.MainConnectionProvider = connProvider;
                myPaymentPlan.MainConnectionProvider = connProvider;

                connProvider.OpenConnection();
                connProvider.BeginTransaction("SaveReceipt");

                DataTable receiptTable = pos.ReceiptMasterTable.Copy();
                DataTable receiptEntriesTable = pos.ReceiptItemsTable.Copy();
                DataTable receiptPaymentTable = pos.ReceiptPaymentTable.Copy();
                DataTable freeMemberPackageTable = receipt.FillSchema("Select * From tblMemberPackage"); // use to store the free member package.
                DataTable freeMemberCreditPackageTable = receipt.FillSchema("Select * From tblMemberCreditPackage");

                if (pos.PaymentPlanTable != null && pos.NCategoryID != 0)
                {
                    DataTable paymentPlanTable = pos.PaymentPlanTable.Copy();

                    if (paymentPlanTable != null && paymentPlanTable.Rows.Count > 0)
                    {
                        foreach (DataRow r in paymentPlanTable.Rows)
                        {
                            r["strReceiptNo"] = pos.StrReceiptNo;

                            if (r["nInstalmentNo"].ToString() == "1")
                            {
                                r["strPaymentReceiptNo"] = pos.StrReceiptNo;

                                myPaymentPlan.IPPPrimaryEntry(r["strReceiptNo"].ToString(), Convert.ToInt32(r["nInstalmentNo"]),
                                                                r["strPaymentReceiptNo"].ToString(), Convert.ToDecimal(r["mPaymentPlanAmt"]),
                                                                Convert.ToDecimal(r["mPaidAmount"]), Convert.ToDecimal(r["mAdjustedPaymentPlanAmt"]),
                                                                Convert.ToDecimal(r["mOutstandingAmt"]), Convert.ToDateTime(r["dtPaymentDate"]),
                                                                Convert.ToDateTime(r["dtDueDate"]), Convert.ToDateTime(r["dtFinalDueDate"]),
                                                                Convert.ToInt32(r["nPaymentPlanID"]));

                            }
                            else
                            {
                                myPaymentPlan.IPPSubsequentEntry(r["strReceiptNo"].ToString(), Convert.ToInt32(r["nInstalmentNo"]),
                                                                    Convert.ToDecimal(r["mPaymentPlanAmt"]), Convert.ToDecimal(r["mPaidAmount"]),
                                                                    Convert.ToDecimal(r["mAdjustedPaymentPlanAmt"]), Convert.ToDecimal(r["mOutstandingAmt"]),
                                                                    Convert.ToDateTime(r["dtDueDate"]), Convert.ToDateTime(r["dtFinalDueDate"]),
                                                                    Convert.ToInt32(r["nPaymentPlanID"]));
                            }
                        }
                        paymentPlanTable.AcceptChanges();
                    }
                    pos.PaymentPlanTable.AcceptChanges();

                }
                else if (pos.PaymentPlanTable != null && pos.NCategoryID == 0)
                {
                    DataTable paymentPlanTable = pos.PaymentPlanTable.Copy();

                    if (paymentPlanTable != null && paymentPlanTable.Rows.Count > 0)
                    {
                        foreach (DataRow r in paymentPlanTable.Rows)
                        {
                            if (r["lastestUpdate"].ToString() == "1")
                            {
                                r["strPaymentReceiptNo"] = pos.StrReceiptNo;

                                myPaymentPlan.IPPPrimaryUpdate(Convert.ToInt32(r["nPaymentPlanID"]), r["strReceiptNo"].ToString(),
                                                                Convert.ToInt32(r["nInstalmentNo"]), r["strPaymentReceiptNo"].ToString(),
                                                                Convert.ToDecimal(r["mPaidAmount"]), Convert.ToDecimal(r["mAdjustedPaymentPlanAmt"]),
                                                                Convert.ToDecimal(r["mOutstandingAmt"]), Convert.ToDateTime(r["dtPaymentDate"]));

                            }
                            else
                            {
                                myPaymentPlan.IPPSubsequentUpdate(Convert.ToInt32(r["nPaymentPlanID"]), r["strReceiptNo"].ToString(),
                                                                    Convert.ToInt32(r["nInstalmentNo"]), Convert.ToDecimal(r["mPaidAmount"]),
                                                                    Convert.ToDecimal(r["mAdjustedPaymentPlanAmt"]), Convert.ToDecimal(r["mOutstandingAmt"]));
                            }
                        }

                        //paymentPlanTable.AcceptChanges();
//.........这里部分代码省略.........

        public void DeleteLastExtensionHistory(int nPackageID, int last_nExtensionID)
        {
            myMemberPackage.NPackageID = nPackageID;
            myMemberPackage.SelectOne();

            TblPackageExtension packageExt = new TblPackageExtension();
            packageExt.NPackageID = nPackageID;
            DataTable packageExtTable = packageExt.SelectAllWnPackageIDLogic();

            if (packageExtTable == null)
                throw new Exception("This extension row has been deleted by others");
            if (packageExtTable.Rows.Count == 0)
                throw new Exception("This extension row has been deleted by others");

            if (packageExtTable.Rows.Count > 0)
            {
                DataRow [] rowList = packageExtTable.Select(" nStatusID = 0 AND nExtensionID > " + last_nExtensionID.ToString());
                if (rowList.Length > 0) // mean is not last package extension
                {
                    throw new Exception("Only the most recent package extension can be deleted.");
                }
            }

            DataRow[] deletedDataRowList = packageExtTable.Select("nStatusID = 0 and nExtensionID = " + last_nExtensionID, "nExtensionID", DataViewRowState.CurrentRows);
            if (deletedDataRowList.Length == 0)
                throw new Exception("This extension row has been deleted by others");
            //deletedDataRowList should be one row
            deletedDataRowList[0]["nStatusID"] = 1;
            TimeSpan durationToReverse = TimeSpan.FromDays((double) ACMS.Convert.ToInt32(deletedDataRowList[0]["nDaysExtended"]));

            DataRow[] tempDataRowList = packageExtTable.Select("nStatusID = 0", "nExtensionID", DataViewRowState.CurrentRows);
            // now need to use the last row's info to calculate the expriry date.
            // but if there is the only row in extension package for this member package, i have to use other info to calculate the expiry date for this memberpackage

            myMemberPackage.DtExpiryDate = myMemberPackage.DtExpiryDate.Value.Subtract(durationToReverse);

            ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();

            try
            {
                myMemberPackage.MainConnectionProvider = connProvider;
                packageExt.MainConnectionProvider = connProvider;
                connProvider.OpenConnection();
                connProvider.BeginTransaction("SaveExtension");
                myMemberPackage.Update();
                packageExt.SaveData(packageExtTable);
                connProvider.CommitTransaction();
            }
            catch (Exception)
            {
                connProvider.RollbackTransaction("SaveExtension");
                throw new Exception("Failed to Create New Extension");
            }
            finally
            {
                if (connProvider.CurrentTransaction != null)
                    connProvider.CurrentTransaction.Dispose();
                if (connProvider.DBConnection != null)
             		{
                    if (connProvider.DBConnection.State == ConnectionState.Open)
                        connProvider.DBConnection.Close();
                }
                myMemberPackage.MainConnactionIsCreatedLocal = true;
                packageExt.MainConnactionIsCreatedLocal = true;
            }
        }

        public bool Cancel(int NPackageID)
        {
            myMemberPackage.NPackageID = NPackageID;
            myMemberPackage.SelectOne();
            myMemberPackage.NStatusID = 2;// 2 represent cancel
            myAudit.DtDate = DateTime.Today;
            myAudit.NAuditTypeID = 0;
            myAudit.NEmployeeID = ACMSLogic.User.EmployeeID;
            myAudit.StrAuditEntry = "Cancel Member Package " + NPackageID.ToString();
            myAudit.StrReference = NPackageID.ToString();

            ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();

            try
            {

                myMemberPackage.MainConnectionProvider = connProvider;
                myAudit.MainConnectionProvider = connProvider;
                connProvider.OpenConnection();
                connProvider.BeginTransaction("CancelMemberPackage");

                myMemberPackage.Update();
                myAudit.Insert();

                connProvider.CommitTransaction();
                return true;
            }
            catch (Exception)
            {
                connProvider.RollbackTransaction("CancelMemberPackage");
                throw;
            }
            finally
            {
                if (connProvider.CurrentTransaction != null)
                    connProvider.CurrentTransaction.Dispose();
                if (connProvider.DBConnection != null)
                {
                    if (connProvider.DBConnection.State == ConnectionState.Open)
                        connProvider.DBConnection.Close();
                    //connProvider.DBConnection.Dispose();
                }
                myMemberPackage.MainConnactionIsCreatedLocal = true;
                myAudit.MainConnactionIsCreatedLocal = true;
            }
        }

        public void UpdateClassAttendance(int nAttendanceID, int nNewPackageID)
        {
            TblClassAttendance classAttendance = new TblClassAttendance();
            System.Data.SqlTypes.SqlInt32 nOldPackageID;

            classAttendance.NAttendanceID = nAttendanceID;
            DataTable table = classAttendance.SelectOne();
            if (table != null && table.Rows.Count > 0)
            {
                nOldPackageID = classAttendance.NPackageID;
                classAttendance.NEmployeeID = User.EmployeeID;
                classAttendance.NPackageID = nNewPackageID;

                myMemberPackage.NPackageID = nNewPackageID;
                DataTable memberPackageTable = myMemberPackage.SelectOne();
                if (memberPackageTable == null || memberPackageTable.Rows.Count == 0)
                    throw new Exception("Failed to update/transfer class attendance. Member Package with Package ID = "+ nNewPackageID.ToString() + "has been deleted");

                DataRow masterRow = memberPackageTable.Rows[0];
                TblPackage package = new TblPackage();
                package.StrPackageCode = masterRow["strPackageCode"].ToString();
                package.SelectOne();

                if (masterRow["dtStartDate"] == DBNull.Value)
                {
                    if (!package.FGIRO || package.FGIRO.IsNull)
                    {
                        myMemberPackage.DtStartDate = classAttendance.DtDate;
                        int iduration = package.NPackageDuration.Value;
                        if (package.StrDurationUnit.ToString().Trim() == "DAY")
                            myMemberPackage.DtExpiryDate = classAttendance.DtDate.Value.AddDays(iduration - 1).AddDays(0);
                        else if (package.StrDurationUnit.ToString().Trim() == "WEEK")
                        {
                            iduration = iduration * 7;
                            myMemberPackage.DtExpiryDate = classAttendance.DtDate.Value.AddDays(iduration - 1).AddDays(0);
                        }
                        else
                            myMemberPackage.DtExpiryDate = classAttendance.DtDate.Value.AddMonths(package.NPackageDuration.Value).AddDays(-1);
                    }
                }
                else
                {
                    if (!package.FGIRO || package.FGIRO.IsNull)
                    {
                        if (classAttendance.DtDate < ACMS.Convert.ToDateTime(masterRow["DtStartDate"]))
                        {
                            myMemberPackage.DtStartDate = classAttendance.DtDate;
                            int iduration = package.NPackageDuration.Value;
                            if (package.StrDurationUnit.ToString().Trim() == "DAY")
                                myMemberPackage.DtExpiryDate = classAttendance.DtDate.Value.AddDays(iduration - 1).AddDays(0);
                            else if (package.StrDurationUnit.ToString().Trim() == "WEEK")
                            {
                                iduration = iduration * 7;
                                myMemberPackage.DtExpiryDate = classAttendance.DtDate.Value.AddDays(iduration - 1).AddDays(0);
                            }
                            else
                                myMemberPackage.DtExpiryDate = classAttendance.DtDate.Value.AddMonths(package.NPackageDuration.Value).AddDays(-1);
                        }
                        else
                        {
                            DateTime memberPackageStartDate = ACMS.Convert.ToDateTime(masterRow["DtStartDate"]);
                            DateTime memberPackageExpiryDate = ACMS.Convert.ToDateTime(masterRow["dtExpiryDate"]);

                            if (memberPackageStartDate > classAttendance.DtDate)
                            {
                                myMemberPackage.DtExpiryDate = memberPackageExpiryDate.Subtract(memberPackageStartDate.Subtract(classAttendance.DtDate.Value));
                                myMemberPackage.DtStartDate = classAttendance.DtDate;
                            }
                        }
                    }
                }

                ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();

                try
                {
                    myMemberPackage.MainConnectionProvider = connProvider;
                    classAttendance.MainConnectionProvider = connProvider;

                    connProvider.OpenConnection();
                    connProvider.BeginTransaction("SaveClassAttendance");

                    myMemberPackage.Update();
                    classAttendance.Update();
                    //UpdateStartExpiryDate(nOldPackageID);

                    connProvider.CommitTransaction();
                    connProvider.BeginTransaction("SaveClassAttendance");
                    UpdateStartExpiryDate(nOldPackageID);
                    connProvider.CommitTransaction();
                }
                catch (Exception)
                {
                    connProvider.RollbackTransaction("SaveClassAttendance");
                    throw new Exception("Failed to save Class Attendance");
                }
                finally
                {
                    if (connProvider.CurrentTransaction != null)
                        connProvider.CurrentTransaction.Dispose();
//.........这里部分代码省略.........

        //public void SaveExtensionHistory(int nPackageID, DataTable packageExtensionTable, DateTime oldExpiryDate)
        public void SaveExtensionHistory(int nPackageID, DataTable packageExtensionTable, DateTime oldExpiryDate, bool isGiro, DateTime StartDate, DateTime EndDate, int iReasonID)
        {
            // *TO Do -- if dtStartDate is null, no allow extend
            DataRow packExtRow = packageExtensionTable.Rows[0];
            int nReasonID=0;
            if (iReasonID != 0)
                nReasonID = iReasonID;
            else
                nReasonID = ACMS.Convert.ToInt32(packExtRow["nReasonID"]);

            if (nReasonID < 0)
                return;
            // Assume that nReasonID = 0 is Others

            TblPackageExtension packageExt = new TblPackageExtension();
            ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();

            if (nReasonID > 0)
            {
                try
                {
                    myMemberPackage.MainConnectionProvider = connProvider;
                    packageExt.MainConnectionProvider = connProvider;

                    connProvider.OpenConnection();
                    connProvider.BeginTransaction("SaveExtension");

                        DateTime packageExtensionStartDate;
                        DateTime packageExtensionEndDate;

                        if (isGiro == true)
                    {
                        packExtRow["dtStartDate"] = StartDate;
                        packExtRow["dtEndDate"] = EndDate;
                        packageExtensionStartDate = StartDate;
                        packageExtensionEndDate = EndDate;
                    }
                    else
                    {
                            //to support extension for multiple package at once
                        if (StartDate.ToString() != "" && EndDate.ToString() != "")
                        {
                            // C=Leave Start Date
                            packageExtensionStartDate = Convert.ToDateTime(StartDate);
                            // D=Leave End Date
                            packageExtensionEndDate = Convert.ToDateTime(EndDate);
                        }
                        else
                        {
                            // C=Leave Start Date
                            packageExtensionStartDate = ACMS.Convert.ToDateTime(packExtRow["dtStartDate"]);
                            // D=Leave End Date
                            packageExtensionEndDate = ACMS.Convert.ToDateTime(packExtRow["dtEndDate"]);
                        }
                    }

                    //B4 Save, must Check whether the startdate have overlap with previous one or not
                    packageExt.NPackageID = nPackageID;
                    DataTable table = packageExt.SelectAllWnPackageIDLogic();
                    if (table != null && table.Rows.Count > 0)
                    {
            //						DataRow[] tempRow = table.Select("nStatusID = 0 and dtEndDate > '" + packageExtensionStartDate + "'", "nExtensionID");
            //						if (tempRow.Length > 0)
            //							throw new Exception("Overlapped date in extension package.");
                        int RowExtention;
                        DateTime e = packageExtensionStartDate;
                        DateTime f = packageExtensionEndDate;
                        for (RowExtention = 0; RowExtention <= table.Rows.Count-1; RowExtention++)
                        {
                            if (e < ACMS.Convert.ToDateTime(table.Rows[RowExtention]["dtEndDate"]) && f >= ACMS.Convert.ToDateTime(table.Rows[RowExtention]["dtStartDate"])
                                || (f < e))
                            {
                            throw new Exception("Overlapped date in extension package.");
                            }
                        }

                    }
                    //end checking

                    myMemberPackage.NPackageID = nPackageID;
                    DataTable memberPackageTable = myMemberPackage.SelectOne();

                    if  (myMemberPackage.DtStartDate.IsNull)
                        throw new Exception("Member Package not been used yet.");

                    if  (memberPackageTable.Rows[0]["DtStartDate"] == DBNull.Value)
                        return;

                    //bool isGiro = !myMemberPackage.NGIRORefID.IsNull;

                    // A=Member Package Start Date
                    DateTime memberPackageStartDate = myMemberPackage.DtStartDate.Value;
                    // B=Member Package End Date
                    DateTime memberPackageExpiryDate = myMemberPackage.DtExpiryDate.Value;
                    DateTime memberPackagePurchaseDate = myMemberPackage.DtPurchaseDate.Value;

                    //if (packageExtensionStartDate > memberPackageExpiryDate)
                    //	throw new Exception("Extension Start Date is not allow to later than Member Package's Expiry Date.");

//.........这里部分代码省略.........

//.........这里部分代码省略.........
                                    iduration = iduration * 7;
                                    masterRow["dtExpiryDate"] = classDate.AddDays(iduration - 1).AddDays(0);

                                }
                            }

                        }
                        else
                        {
                            int iduration = package.NPackageDuration.Value;
                            if (package.StrDurationUnit.ToString().Trim() == "DAY")
                                masterRow["dtExpiryDate"] = classDate.AddDays(iduration - 1).AddDays(0);
                            else if (package.StrDurationUnit.ToString().Trim() == "WEEK")
                            {
                                iduration = iduration * 7;
                                masterRow["dtExpiryDate"] = classDate.AddDays(iduration - 1).AddDays(0);
                            }
                            else if (package.StrDurationUnit.ToString().Trim() == "MONTH")
                                masterRow["dtExpiryDate"] = classDate.AddMonths(package.NPackageDuration.Value).AddDays(-1);
                        }
                        //jackie END 2/02/2012

                    }
                    else
                    {
                        // Kean Yiap
                        string strPackageCode = masterRow["strPackageCode"].ToString();
                        CalculateMemberPackageBalance(strPackageCode, strMemberShipID, memberPackageTable);
                        DateTime memberPackageStartDate = ACMS.Convert.ToDateTime(masterRow["DtStartDate"]);
                        DateTime memberPackageExpiryDate = ACMS.Convert.ToDateTime(masterRow["dtExpiryDate"]);

                        //if (memberPackageStartDate > classDate)
                        //{
                        //jackie why must change ExpiryDate 26042012
                        //	masterRow["dtExpiryDate"] = memberPackageExpiryDate.Subtract(memberPackageStartDate.Subtract(classDate));
                        //}
                    }
                }

                if (ACMS.Convert.ToInt32(memberPackageTable.Rows[0]["Balance"]) <= 1)
                {
                    if (masterRow["strPackageCode"].ToString() == "FTRIAL")
                    {
                        memberPackageTable.Rows[0]["nBalance"] = 1;
                    }
                    else
                    {
                        memberPackageTable.Rows[0]["nBalance"] = 0;
                    }
                }

            }

            ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();

            try
            {
                myMemberPackage.MainConnectionProvider = connProvider;
                classAttendance.MainConnectionProvider = connProvider;

                connProvider.OpenConnection();
                connProvider.BeginTransaction("SaveClassAttendance");

                if (needtoAddBackOneDayExpiryDate && memberPackageTable.Rows[0]["fGIRO"].ToString() != "1")
                {
                    memberPackageTable.Rows[0]["dtExpiryDate"] = ACMS.Convert.ToDateTime(memberPackageTable.Rows[0]["dtExpiryDate"]).AddDays(1);
                }

                myMemberPackage.SaveData(memberPackageTable);

                if (BalanceStatus)
                {
                    if (!isPostFromOtherRecord)
                        classAttendance.Insert();
                    else
                        classAttendance.Update();

                    connProvider.CommitTransaction();
                }

                return true;
            }
            catch (Exception)
            {
                connProvider.RollbackTransaction("SaveClassAttendance");
                throw new Exception("Failed to save Class Attendance");
            }
            finally
            {
                if (connProvider.CurrentTransaction != null)
                    connProvider.CurrentTransaction.Dispose();
                if (connProvider.DBConnection != null)
                {
                    if (connProvider.DBConnection.State == ConnectionState.Open)
                        connProvider.DBConnection.Close();
                }
                myMemberPackage.MainConnactionIsCreatedLocal = true;
                classAttendance.MainConnactionIsCreatedLocal = true;
            }
        }

        public bool ForfeitBooking(int nSessionID)
        {
            myServiceSession.NSessionID = nSessionID;
            myServiceSession.SelectOne();

            myServiceSession.NStatusID = 6;
            myServiceSession.DtLastEditDate = DateTime.Now;
            myServiceSession.NEmployeeID = ACMSLogic.User.EmployeeID;

            TblAudit audit = new TblAudit();
            audit.DtDate = DateTime.Today;
            audit.NAuditTypeID = AuditTypeID.Service;
            audit.NEmployeeID = ACMSLogic.User.EmployeeID;
            audit.StrAuditEntry = "Cancel Booking " + nSessionID.ToString();
            audit.StrReference = nSessionID.ToString();

            ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();

            try
            {

                myServiceSession.MainConnectionProvider = connProvider;
                audit.MainConnectionProvider = connProvider;
                connProvider.OpenConnection();
                connProvider.BeginTransaction("ForfeitBooking");
                myServiceSession.Update();
                audit.Insert();
                connProvider.CommitTransaction();
                return true;
            }
            catch (Exception)
            {
                connProvider.RollbackTransaction("ForfeitBooking");
                throw;
            }
            finally
            {
                if (connProvider.CurrentTransaction != null)
                    connProvider.CurrentTransaction.Dispose();
                if (connProvider.DBConnection != null)
                {
                    if (connProvider.DBConnection.State == ConnectionState.Open)
                        connProvider.DBConnection.Close();
                    //connProvider.DBConnection.Dispose();
                }
                myServiceSession.MainConnactionIsCreatedLocal = true;
                audit.MainConnactionIsCreatedLocal = true;
            }
        }

        public string GetCurrentYearExpiredPoint(SqlConnection conn, string strMembershipID)
        {
            DataSet _ds = new DataSet();

            string strSql = "sp_GetCurrentYearExpiredPoint";
            ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();
            connProvider.OpenConnection();
            SqlCommand cmd_NewLead = conn.CreateCommand();
            cmd_NewLead.CommandText = strSql;
            cmd_NewLead.CommandType = CommandType.StoredProcedure;

            cmd_NewLead.Parameters.Add(new SqlParameter("@strMembershipID", strMembershipID));
            SqlHelper.FillDataset(conn, CommandType.StoredProcedure, "sp_GetCurrentYearExpiredPoint", _ds, new string[] { "Table" }, new SqlParameter("@strMembershipID", strMembershipID));

            if (_ds.Tables[0].Rows.Count>0)
                return "Current Year Expired Points: " + _ds.Tables[0].Rows[0]["ExpiredPoint"].ToString();
            else
                return "";
        }

        public void NewRedemption(string strMembershipID, string itemCode, int qtyOfItemCodeToRedempt, ref int strTransNo)
        {
            ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();
            connProvider.OpenConnection();
            TblRewardsCatalogue catalogue = new TblRewardsCatalogue();
            catalogue.StrItemCode = itemCode;
            catalogue.SelectOne();

            TblRewardsTransaction rewardTrans = new TblRewardsTransaction();
            rewardTrans.MainConnectionProvider = connProvider;
            rewardTrans.StrMembershipID = strMembershipID;
            rewardTrans.DtDate = DateTime.Now;
            rewardTrans.NTypeID = 2;
            rewardTrans.StrReferenceNo = itemCode;
            rewardTrans.NEmployeeID = User.EmployeeID;
            rewardTrans.StrBranchCode = User.BranchCode;

            if (!catalogue.DRewardsPoints.IsNull)
                rewardTrans.DRewardsPoints = -catalogue.DRewardsPoints.Value * qtyOfItemCodeToRedempt;

            rewardTrans.Insert();
            strTransNo = rewardTrans.NTransactionID.Value;
            TblPromotionFreebie promoFreebie = new TblPromotionFreebie();
            promoFreebie.StrPromotionCode = itemCode;
            DataTable receiptFreebieTable = promoFreebie.SelectAllWstrPromotionCodeLogic();
            //Code selected is Promotion Freebie Code
            if (receiptFreebieTable.Rows.Count > 0)
            {
                //TblProductInventory proInven = new TblProductInventory();
                //proInven.MainConnectionProvider = connProvider;

                //// deduct stock
                //foreach (DataRow r in receiptFreebieTable.Rows)
                //{
                //    proInven.IncreaseQuantity(r["strItemCode"].ToString(), User.BranchCode, -qtyOfItemCodeToRedempt);
                //}
            }
            else
            {
                //Code selected is Package Code
                TblPackage pkg = new TblPackage();
                pkg.StrPackageCode = itemCode;
                DataTable pkgTable = pkg.SelectOne();
                if (pkgTable.Rows.Count > 0)
                {
                    TblMemberPackage mp = new TblMemberPackage();
                    mp.SelectAll();
                    MemberPackage memberPackage = new MemberPackage();
                    DataTable memberPackageTable = memberPackage.New(false, strMembershipID);
                    DataRow memberPackageRow = memberPackageTable.Rows[0];

                    memberPackageRow["strMembershipID"] = strMembershipID;
                    memberPackageRow["dtPurchaseDate"] = DateTime.Now;
                    memberPackageRow["strRemarks"] = "REWARDS";
                    memberPackageRow["nStatusID"] = 0;
                    memberPackageRow["strPackageCode"] = itemCode;
                    memberPackageRow["fFree"] = 1;

                    mp.SaveData(memberPackageTable);
                }
                else
                {
                    //Code selected is Product Code
                    //TblProductInventory productInvent = new TblProductInventory();
                    //productInvent.MainConnectionProvider = connProvider;
                    //productInvent.StrProductCode = itemCode;
                    //productInvent.SelectOne();

                    //productInvent.NQuantity = ACMS.Convert.ToInt32(productInvent.NQuantity) - qtyOfItemCodeToRedempt;
                    //productInvent.Update();
                }
            }

            if (connProvider.CurrentTransaction != null)
                connProvider.CurrentTransaction.Dispose();

            if (connProvider.DBConnection != null)
            {
                if (connProvider.DBConnection.State == ConnectionState.Open)
                    connProvider.DBConnection.Close();
            }
        }

        public bool UpdateMember(string strMembershipID, 
                                    object dateDob, 
                                    bool fAirCrew, 
                                    bool fBoundCheck, 
                                    bool fEmail, 
			                        bool fGiroFailed, 
                                    bool fPostalMail, 
                                    bool fSingaporean, 
                                    bool fSMS, 
                                    int nLoyaltyStatusID, 
                                    string strAdd1, 
                                    string strAdd2, 
			                        string strAltEmail, 
                                    string strCardName, 
                                    string strCompany, 
                                    string strCreditCardNo, 
                                    string strEmail, 
			                        string strEmergencyContactNo, 
                                    string strEmergencyContactPerson, 
                                    string strHomeNo, 
                                    string strMemberName, 
			                        string strMobileNo, 
                                    string strNIRCFIN, 
                                    string strOccupation, 
                                    string strOfficeNo, 
                                    string strPagerNo, 
			                        string strPostalCode, 
                                    string strRemark, 
                                    int nMediaSourceID, 
                                    string strMediaSource, 
                                    string strBeforePhoto,
			                        string strAfterPhoto, 
                                    byte[] imgPhoto, 
                                    User aUser, 
                                    bool fFemale,
                                    bool fPhoneCall,
                                    string strSecurityQuestion,
                                    string strSecurityAnswer,
                                    string strMobileAppPwd)
        {
            ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();
            TblMember sqlMember = new TblMember();
            TblAudit sqlAudit = new TblAudit();

            bool isSuccess = false;

            try
            {
                sqlMember.MainConnectionProvider = connProvider;
                sqlAudit.MainConnectionProvider = connProvider;
                connProvider.OpenConnection();
                connProvider.BeginTransaction("UpdateMemberRecord");

                sqlMember.StrMembershipID = strMembershipID;
                sqlMember.FFemale = fFemale;
                sqlMember.FAirCrew = fAirCrew;
                sqlMember.FBounceCheck = fBoundCheck;
                sqlMember.FEmail = fEmail;
                sqlMember.FGIROFailed = fGiroFailed;
                sqlMember.FPostalMail = fPostalMail;
                sqlMember.FSingaporean = fSingaporean;
                sqlMember.FSMS = fSMS;
                sqlMember.FPhoneCall = fPhoneCall;

                if (nLoyaltyStatusID != 0)
                {
                    sqlMember.NLoyaltyStatusID = nLoyaltyStatusID;
                }

                sqlMember.StrAddress1 = strAdd1;
                sqlMember.StrAddress2 = strAdd2;
                sqlMember.StrAltEmail = strAltEmail;
                sqlMember.StrCardName = strCardName;
                sqlMember.StrCompany = strCompany;
                sqlMember.StrCreditCardNo = strCreditCardNo;
                sqlMember.StrEmail = strEmail;
                sqlMember.StrEmergencyContactNumber = strEmergencyContactNo;
                sqlMember.StrEmergencyContactPerson = strEmergencyContactPerson;
                sqlMember.StrHomeNo = strHomeNo;
                sqlMember.StrMemberName = strMemberName;
                sqlMember.StrMobileNo = strMobileNo;
                sqlMember.StrNRICFIN = strNIRCFIN;
                sqlMember.StrOccupation = strOccupation;
                sqlMember.StrOfficeNo = strOfficeNo;
                sqlMember.StrPagerNo = strPagerNo;
                sqlMember.StrPostalCode = strPostalCode;
                sqlMember.StrRemarks = strRemark;
                sqlMember.NMediaSourceID = nMediaSourceID;
                sqlMember.StrMediaSource = strMediaSource;
                sqlMember.NEmployeeID = aUser.NEmployeeID();
                sqlMember.StrSecurityQuestion = strSecurityQuestion;
                sqlMember.StrSecurityAnswer = strSecurityAnswer;
                sqlMember.StrPassword = strMobileAppPwd;

                sqlMember.Update2(dateDob, strBeforePhoto, strAfterPhoto, imgPhoto);

                sqlAudit.NAuditTypeID = ACMSLogic.AuditTypeID.MemberRecord;
                sqlAudit.NEmployeeID = aUser.NEmployeeID();
                sqlAudit.StrAuditEntry = "Update member " +strMembershipID;
                sqlAudit.StrReference = strMembershipID;
//.........这里部分代码省略.........

        public static bool BlockMembershipID(int numberOfNonMemberID, int numberOfMemberID, string strBranchCode)
        {
            ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();
            TblCompany sqlCompany = new TblCompany();
            TblBranch sqlBranch = new TblBranch();

            try
            {
                sqlCompany.MainConnectionProvider = connProvider;
                sqlBranch.MainConnectionProvider = connProvider;
                connProvider.OpenConnection();
                connProvider.BeginTransaction("BlockMembershipID");

                if (numberOfNonMemberID > 0)
                    sqlCompany.UpdateBlockNonMembershipID(numberOfNonMemberID);
                if (numberOfMemberID > 0)
                    sqlBranch.UpdateBlockMembershipID(strBranchCode, numberOfMemberID);

                connProvider.CommitTransaction();
                return true;
            }
            catch (Exception)
            {
                connProvider.RollbackTransaction("BlockMembershipID");
                throw;
            }
            finally
            {
                if (connProvider.CurrentTransaction != null)
                    connProvider.CurrentTransaction.Dispose();
                if (connProvider.DBConnection != null)
                {
                    if (connProvider.DBConnection.State == ConnectionState.Open)
                        connProvider.DBConnection.Close();
                    connProvider.DBConnection.Dispose();
                }
                sqlCompany.Dispose();
                sqlBranch.Dispose();
            }
        }

        public bool UpdateCardStatus(string strMembershipID, User aUser, CardStatusType myType, int nRequestID, 
			string strBranchCode)
        {
            ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();
            TblMember sqlMember = new TblMember();
            TblCardRequest sqlCardRequest = new TblCardRequest();
            TblAudit sqlAudit = new TblAudit();

            bool isSuccess = false;
            try
            {
                sqlMember.MainConnectionProvider = connProvider;
                sqlCardRequest.MainConnectionProvider = connProvider;
                sqlAudit.MainConnectionProvider = connProvider;
                connProvider.OpenConnection();
                connProvider.BeginTransaction("ChangeCardStatus");

                if (CardStatusType.RequestPrint == myType)
                    sqlMember.UpdateCardStatus(strMembershipID, (int)myType, "HQ");
                else
                    sqlMember.UpdateCardStatus(strMembershipID, (int)myType, strBranchCode);
                sqlCardRequest.NEmployeeID = aUser.NEmployeeID();
                sqlCardRequest.NRequestID = nRequestID;
                sqlCardRequest.NStatusID = (int)myType;
                sqlCardRequest.StrBranchCode = strBranchCode;
                sqlCardRequest.StrMembershipID = strMembershipID;
                sqlCardRequest.DtLastEditDate = DateTime.Now;
                sqlCardRequest.Update();

                sqlAudit.NAuditTypeID = ACMSLogic.AuditTypeID.MemberCard;
                sqlAudit.NEmployeeID = aUser.NEmployeeID();
                sqlAudit.StrAuditEntry = "Update member card to " +myType.ToString() +".";
                sqlAudit.StrReference = strMembershipID;
                sqlAudit.DtDate = DateTime.Now;
                sqlAudit.Insert();

                connProvider.CommitTransaction();
                isSuccess = true;
            }
            catch (Exception)
            {
                connProvider.RollbackTransaction("ChangeCardStatus");
                throw;
            }
            finally
            {
                if (connProvider.CurrentTransaction != null)
                    connProvider.CurrentTransaction.Dispose();
                if (connProvider.DBConnection != null)
                {
                    if (connProvider.DBConnection.State == ConnectionState.Open)
                        connProvider.DBConnection.Close();
                    connProvider.DBConnection.Dispose();
                }
                sqlMember.Dispose();
                sqlCardRequest.Dispose();
                sqlAudit.Dispose();
            }
            return isSuccess;
        }