/// <summary> /// Balance for new member package is not allow to set nMaxSession is bcoz need the value zero /// to do calculation In POS /// </summary> /// <param name="strMembershipID"></param> /// <returns></returns> /// start jackie 4/3/2012 public DataView GetActive_n_NonFreeMemberPackage_Convert_Calculation(string strMembershipID) { DataTable table = myMemberPackage.GetActivenNonFreeMemberPackage(strMembershipID); if (table != null) { if (!table.Columns.Contains("Balance")) { DataColumn colBalance = new DataColumn("Balance", System.Type.GetType("System.Int32")); table.Columns.Add(colBalance); } if (!table.Columns.Contains("FNew")) { DataColumn colNew = new DataColumn("FNew", System.Type.GetType("System.Int32")); table.Columns.Add(colNew); } TblClassAttendance classAttendance = new TblClassAttendance(); TblServiceSession serviceSession = new TblServiceSession(); foreach (DataRow r in table.Rows) { int nCategoryID = ACMS.Convert.ToInt32(r["nCategoryID"]); int nPackageID = ACMS.Convert.ToInt32(r["nPackageID"]); // class Attendance if (nCategoryID == 1 || nCategoryID == 2) { classAttendance.NPackageID = nPackageID; DataView classAttendanceTable = classAttendance.SelectAllWnPackageIDLogic().DefaultView; classAttendanceTable.RowFilter = "nStatusID = 1 or nStatusID = 2"; if (classAttendanceTable.Count > 0) { // r["Balance"] = ACMS.Convert.ToInt32(r["nMaxSession"]) - classAttendanceTable.Count; r["Balance"] = (ACMS.Convert.ToInt32(r["mbaseunitprice"]) * classAttendanceTable.Count); r["FNew"] = 0; } else { r["Balance"] = 0; r["FNew"] = 1; } } else if (nCategoryID == 3 || nCategoryID == 4 || nCategoryID == 5 || nCategoryID == 6) // Service Session { serviceSession.NPackageID = nPackageID; DataView serviceSessionTable = serviceSession.SelectAllWnPackageIDLogic().DefaultView; serviceSessionTable.RowFilter = "nStatusID = 5 or nStatusID = 6"; if (serviceSessionTable.Count > 0) { // r["Balance"] = ACMS.Convert.ToInt32(r["nMaxSession"]) - serviceSessionTable.Count; r["Balance"] =ACMS.Convert.ToInt32(r["mlistprice"])- (ACMS.Convert.ToInt32(r["mbaseunitprice"]) * serviceSessionTable.Count); r["FNew"] = 0; } else { r["Balance"] = 0; r["FNew"] = 1; } } } } table.DefaultView.RowFilter = " Balance > 0 OR (Balance = 0 AND FNew = 1)"; return table.DefaultView; }
//19032012 public static void CalculateCreditMemberPackageBalance(DataTable memberPackageTable) { if (!memberPackageTable.Columns.Contains("Balance")) { DataColumn colBalance = new DataColumn("Balance", System.Type.GetType("System.Int32")); memberPackageTable.Columns.Add(colBalance); } DataTable gymTable = new DataTable(); DataColumn colDtdate = new DataColumn("dtDate", typeof(string)); DataColumn colPackageID = new DataColumn("nPackageID", typeof(int)); DataColumn colAttendanceID = new DataColumn("nAttendanceID", typeof(int)); gymTable.Columns.Add(colDtdate); gymTable.Columns.Add(colPackageID); gymTable.Columns.Add(colAttendanceID); TblClassAttendance classAttendance = new TblClassAttendance(); TblServiceSession serviceSession = new TblServiceSession(); foreach (DataRow r in memberPackageTable.Rows) { r["Balance"] = r["nMaxSession"]; // here to put the free class limit if (ACMS.Convert.ToInt32(r["nMaxSession"]) == 9999) { r["Balance"] = 9999; continue; } int nCategoryID = ACMS.Convert.ToInt32(r["nCategoryID"]); int nPackageID = ACMS.Convert.ToInt32(r["nPackageID"]); // class Attendance if (nCategoryID == 1 || nCategoryID == 2) { classAttendance.NPackageID = nPackageID; DataTable classAttendanceTable = classAttendance.SelectAllWnPackageIDLogic(); if (classAttendanceTable == null || classAttendanceTable.Rows.Count == 0) { if (ACMS.Convert.ToDBInt32(r["nAdjust"]) >= 1 && ACMS.Convert.ToInt32(r["nMaxSession"]) < 9999) { r["Balance"] = ACMS.Convert.ToDBInt32(r["Balance"]) - ACMS.Convert.ToDBInt32(r["nAdjust"]); } continue; } DataView classAttendanceTableView = classAttendanceTable.DefaultView; // Need to filter out the non GYM attendance here classAttendanceTableView.RowFilter = "((nStatusID = 1 or nStatusID = 2) AND nTypeID = 0)"; //dw testing TblMemberPackage MemPackage = new TblMemberPackage(); if (ACMS.Convert.ToDBInt32(r["nAdjust"]) >= 1 && ACMS.Convert.ToInt32(r["nMaxSession"]) < 9999) { r["Balance"] = ACMS.Convert.ToDBInt32(r["Balance"]) - ACMS.Convert.ToDBInt32(r["nAdjust"]); } int nPackBalance; if (classAttendanceTableView.Count > 0) { r["Balance"] = ACMS.Convert.ToInt32(r["Balance"]) - classAttendanceTableView.Count; nPackBalance = ACMS.Convert.ToInt32(r["Balance"]); if (nPackBalance <= 0) MemPackage.nBalance = 1; } // Start calculate the POWER Package // It is consider PWR Package if the class attendance is GYM Class classAttendanceTableView.RowFilter = "((nStatusID = 1 or nStatusID = 2) AND nTypeID = 1)"; if (classAttendanceTableView.Count > 0) { for (int i = 0; i < classAttendanceTableView.Count; i++) { DataRow row = classAttendanceTableView[i].Row; string dtDate = ACMS.Convert.ToDateTime(row["dtDate"]).ToString("yyyy/MM/dd"); int nAttendanceID = ACMS.Convert.ToInt32(row["nAttendanceID"]); DataRow[] foundRow = gymTable.Select("dtDate = '" + dtDate + "'"); if (foundRow.Length == 0) { DataRow addRow = gymTable.NewRow(); addRow["dtDate"] = dtDate; addRow["nPackageID"] = nPackageID; addRow["nAttendanceID"] = nAttendanceID; gymTable.Rows.Add(addRow); } } foreach (DataRow pRow in gymTable.Rows) { DateTime dtDate = ACMS.Convert.ToDateTime(pRow["dtDate"]); int nPackageIDInGymTable = ACMS.Convert.ToInt32(pRow["nPackageID"]); string strFilter = string.Format("(nStatusID = 1 or nStatusID = 2) " + " AND nTypeID = 0 AND nPackageID = {0} AND DtDate = #{1}#", nPackageIDInGymTable.ToString(), dtDate.ToString("yyyy/MM/dd")); DataRow[] foundRow = classAttendanceTable.Select(strFilter, "nPackageID", DataViewRowState.CurrentRows); if (foundRow.Length > 0) r["Balance"] = ACMS.Convert.ToInt32(r["Balance"]) + 1; } r["Balance"] = ACMS.Convert.ToInt32(r["Balance"]) - gymTable.Rows.Count; } } else if (nCategoryID == 3 || nCategoryID == 4 || nCategoryID == 5 || nCategoryID == 6) // Service Session { serviceSession.NPackageID = nPackageID; DataView serviceSessionTable = serviceSession.SelectAllWnPackageIDLogic().DefaultView; serviceSessionTable.RowFilter = "nStatusID = 5 or nStatusID = 6"; if (serviceSessionTable.Count > 0) { r["Balance"] = ACMS.Convert.ToInt32(r["Balance"]) - serviceSessionTable.Count; } if (ACMS.Convert.ToDBInt32(r["nAdjust"]) >= 1 && ACMS.Convert.ToInt32(r["nMaxSession"]) < 9999) { r["Balance"] = ACMS.Convert.ToDBInt32(r["Balance"]) - ACMS.Convert.ToDBInt32(r["nAdjust"]); } } } }
public void DeleteClassAttendance(int nPackageID, int nAttendanceID, string remark) { // i need to know it is only one record, and it is 1st record also TblClassAttendance classAttendance = new TblClassAttendance(); classAttendance.NPackageID = nPackageID; DataTable table = classAttendance.SelectAllWnPackageIDLogic(); bool setNullInMemberPackage = false; bool isDelete1stRecord = false; bool isGIRO = false; bool isGiveBackOneDay = false; DateTime newfirstRecordClassAttendanceDate = DateTime.MinValue; if (table != null && table.Rows.Count > 0) { table.DefaultView.RowFilter = "nStatusID = 1 or nStatusID = 2 or nStatusID = 4"; if (table.DefaultView.Count == 1) { DataSet ds = new DataSet(); string strSQL = "select nCategoryID from tblMemberPackage mp join tblPackage p on mp.strPackageCode=p.strPackageCode where nPackageID=" + nPackageID.ToString(); SqlHelper.FillDataset(connection, CommandType.StoredProcedure, "UP_GETDATA", ds, new string[] { "Table" }, new SqlParameter("@strSQL", strSQL)); if (ds.Tables[0].Rows.Count > 0) { if (ds.Tables[0].Rows[0]["nCategoryID"].ToString() == "2") { isGIRO = true; setNullInMemberPackage = false; } else setNullInMemberPackage = true; } else setNullInMemberPackage = true; } else { DataRow [] rowList = table.Select("nStatusID = 1 or nStatusID = 2 or nStatusID = 4", "nAttendanceID", DataViewRowState.CurrentRows); int firstRecord_nAttendanceID = ACMS.Convert.ToInt32(rowList[0]["nAttendanceID"]); if (firstRecord_nAttendanceID == nAttendanceID) { isDelete1stRecord = true; newfirstRecordClassAttendanceDate = ACMS.Convert.ToDateTime(rowList[1]["dtDate"]); } } } classAttendance.NAttendanceID = nAttendanceID; classAttendance.SelectOne(); classAttendance.DtLastEditDate = DateTime.Now; classAttendance.NEmployeeID = ACMSLogic.User.EmployeeID; if (classAttendance.NStatusID == 2) isGiveBackOneDay = true; classAttendance.NStatusID = 5; classAttendance.StrRemarks = remark; TblAudit audit = new TblAudit(); audit.DtDate = DateTime.Now; audit.NAuditTypeID = 3; audit.NEmployeeID = User.EmployeeID; audit.StrAuditEntry = "Delete class attendance with nAttendanceID = " + nAttendanceID + " and nPackageID = " + nPackageID; audit.StrReference = nAttendanceID.ToString(); TblMemberPackage memberPackage = new TblMemberPackage(); memberPackage.NPackageID = nPackageID; memberPackage.SelectOne(); if (setNullInMemberPackage) { // bool isUnlimited = memberPackage.IsUnlimitedPackage(nPackageID); // // if (!isUnlimited) // { memberPackage.DtExpiryDate = System.Data.SqlTypes.SqlDateTime.Null; memberPackage.DtStartDate = System.Data.SqlTypes.SqlDateTime.Null; memberPackage.DtLastEdit = DateTime.Now; memberPackage.NEmployeeID = User.EmployeeID; // } } else if (isDelete1stRecord) { if (!isGIRO) { TimeSpan duration = memberPackage.DtExpiryDate.Value.Subtract(memberPackage.DtStartDate.Value); DateTime newExpriry = newfirstRecordClassAttendanceDate.Add(duration); memberPackage.DtExpiryDate = newExpriry; memberPackage.DtStartDate = newfirstRecordClassAttendanceDate; memberPackage.DtLastEdit = DateTime.Now; memberPackage.NEmployeeID = User.EmployeeID; } } else if (isGiveBackOneDay) { if (!isGIRO) { // Check if status = forfeited, give back 1 day to duration pkg DateTime newExpriry = memberPackage.DtExpiryDate.Value.AddDays(1); memberPackage.DtExpiryDate = newExpriry; memberPackage.DtLastEdit = DateTime.Now; memberPackage.NEmployeeID = User.EmployeeID; } } ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider(); try { memberPackage.MainConnectionProvider = connProvider; classAttendance.MainConnectionProvider = connProvider; audit.MainConnectionProvider = connProvider; connProvider.OpenConnection(); connProvider.BeginTransaction("DeleteClassAttendance"); memberPackage.Update(); audit.Insert(); classAttendance.Update(); connProvider.CommitTransaction(); //return true; } catch (Exception ex) { connProvider.RollbackTransaction("DeleteClassAttendance"); throw new Exception("Failed to delete Class Attendance :: " + ex.Message); } finally { if (connProvider.CurrentTransaction != null) connProvider.CurrentTransaction.Dispose(); if (connProvider.DBConnection != null) { if (connProvider.DBConnection.State == ConnectionState.Open) connProvider.DBConnection.Close(); } memberPackage.MainConnactionIsCreatedLocal = true; classAttendance.MainConnactionIsCreatedLocal = true; audit.MainConnactionIsCreatedLocal = true; } }
private void CalculateBalance(string strMembershipID,string strMembershipPackageCode) { if (myDataTable != null) { if (!myDataTable.Columns.Contains("strBalNew")) { DataColumn colBalNew = new DataColumn("strBalNew", System.Type.GetType("System.String")); myDataTable.Columns.Add(colBalNew); } if (!myDataTable.Columns.Contains("Balance")) { DataColumn colBalance = new DataColumn("Balance", System.Type.GetType("System.Int32")); myDataTable.Columns.Add(colBalance); } if (!myDataTable.Columns.Contains("strPackageType")) { DataColumn colPackageType = new DataColumn("strPackageType", System.Type.GetType("System.String")); myDataTable.Columns.Add(colPackageType); } TblClassAttendance classAttendance = new TblClassAttendance(); TblServiceSession serviceSession = new TblServiceSession(); DataTable gymTable = new DataTable(); DataColumn colDtdate = new DataColumn("dtDate", typeof(string)); DataColumn colPackageID = new DataColumn("nPackageID", typeof(int)); DataColumn colAttendanceID = new DataColumn("nAttendanceID", typeof(int)); gymTable.Columns.Add(colDtdate); gymTable.Columns.Add(colPackageID); gymTable.Columns.Add(colAttendanceID); foreach (DataRow r in myDataTable.Rows) { r["Balance"] = r["nMaxSession"]; if (ACMS.Convert.ToInt32(r["nMaxSession"]) == 9999) { r["Balance"] = 9999; r["strBalNew"] = r["Balance"].ToString(); if (r["dtStartDate"] == DBNull.Value) r["strBalNew"] = "New"; else { if (r["strPackageType"].ToString() == "Normal Package") { if (ACMS.Convert.ToInt32(r["nMaxSession"]) == ACMS.Convert.ToInt32(r["Balance"]) && ACMS.Convert.ToInt32(r["nMaxSession"])!=9999) r["strBalNew"] = "New"; } } continue; } int nCategoryID = ACMS.Convert.ToInt32(r["nCategoryID"]); int nPackageID = ACMS.Convert.ToInt32(r["nPackageID"]); // class Attendance if (nCategoryID == 1 || nCategoryID == 2) { classAttendance.NPackageID = nPackageID; DataTable classAttendanceTable = classAttendance.SelectAllWnPackageIDLogic(); if (classAttendanceTable == null || classAttendanceTable.Rows.Count == 0) { if (ACMS.Convert.ToDBInt32(r["nAdjust"])>=1 && ACMS.Convert.ToInt32(r["nMaxSession"]) < 9999) { r["Balance"]=ACMS.Convert.ToDBInt32(r["Balance"])-ACMS.Convert.ToDBInt32(r["nAdjust"]); } r["strBalNew"] = r["Balance"].ToString(); if (r["dtStartDate"] == DBNull.Value) r["strBalNew"] = "New"; else { if (r["strPackageType"].ToString() == "Normal Package") { if (ACMS.Convert.ToInt32(r["nMaxSession"]) == ACMS.Convert.ToInt32(r["Balance"])) r["strBalNew"] = "New"; } } continue; } DataView classAttendanceTableView = classAttendanceTable.DefaultView; // Need to filter out the non GYM attendance here classAttendanceTableView.RowFilter = "((nStatusID = 1 or nStatusID = 2) AND nTypeID = 0)"; if (classAttendanceTableView.Count > 0) { ACMSDAL.TblMemberPackage sqlFindPackageCode = new ACMSDAL.TblMemberPackage(); string strPackageCode = sqlFindPackageCode.GetPackageCode(strMembershipID, nPackageID); //if (strPackageCode == "AA(1080/12)" || strPackageCode == "AA(2160/24)" ) //{ // r["Balance"] = ACMS.Convert.ToInt32(r["Balance"]) - myDTransactionID; //} if (r["fEntries"].ToString()=="True") { if (nCategoryID == 2) //2604 { string strPackageID = nPackageID.ToString(); ACMSDAL.TblMemberPackage sqlCalcTotalGIRO = new ACMSDAL.TblMemberPackage();//jackie 15/03/2012 int intTotalGIRO = sqlCalcTotalGIRO.CalculateTotalGIRO(strMembershipID, strPackageID, strPackageCode); ACMSDAL.TblMemberPackage sqlCalcPackages = new ACMSDAL.TblMemberPackage(); int myDTransactionID = sqlCalcPackages.CalculateSpecialSessionPackages(strMembershipID, nPackageID); r["Balance"] = (intTotalGIRO * ACMS.Convert.ToInt32(r["Balance"])) - myDTransactionID; } else { //1203 jackie ACMSDAL.TblMemberPackage sqlCalcPackages = new ACMSDAL.TblMemberPackage(); int myDTransactionID = sqlCalcPackages.CalculateSpecialSessionPackages(strMembershipID, nPackageID); r["Balance"] = ACMS.Convert.ToInt32(r["Balance"]) - myDTransactionID; } } else//2604 if ( strPackageCode == "FTRIAL") { r["Balance"] = classAttendanceTableView.Count; } else { if (nCategoryID == 2 || ACMS.Convert.ToInt32(r["Balance"]) == 9999) //2604 { string strPackageID = nPackageID.ToString(); ACMSDAL.TblMemberPackage sqlCalcTotalGIRO = new ACMSDAL.TblMemberPackage();//jackie 15/03/2012 int intTotalGIRO = sqlCalcTotalGIRO.CalculateTotalGIRO(strMembershipID, strPackageID,strPackageCode); r["Balance"] = (intTotalGIRO * ACMS.Convert.ToInt32(r["Balance"])) - classAttendanceTableView.Count; } else { r["Balance"] = ACMS.Convert.ToInt32(r["Balance"]) - classAttendanceTableView.Count; } } } else { if (nCategoryID == 2) //2604 { if (r["fEntries"].ToString() == "True") { ACMSDAL.TblMemberPackage sqlFindPackageCode = new ACMSDAL.TblMemberPackage(); string strPackageCode = sqlFindPackageCode.GetPackageCode(strMembershipID, nPackageID); string strPackageID = nPackageID.ToString(); ACMSDAL.TblMemberPackage sqlCalcTotalGIRO = new ACMSDAL.TblMemberPackage();//jackie 15/03/2012 int intTotalGIRO = sqlCalcTotalGIRO.CalculateTotalGIRO(strMembershipID, strPackageID, strPackageCode); ACMSDAL.TblMemberPackage sqlCalcPackages = new ACMSDAL.TblMemberPackage(); int myDTransactionID = sqlCalcPackages.CalculateSpecialSessionPackages(strMembershipID, nPackageID); r["Balance"] = (intTotalGIRO * ACMS.Convert.ToInt32(r["Balance"])); } else { ACMSDAL.TblMemberPackage sqlFindPackageCode = new ACMSDAL.TblMemberPackage(); string strPackageCode = sqlFindPackageCode.GetPackageCode(strMembershipID, nPackageID); string strPackageID = nPackageID.ToString(); ACMSDAL.TblMemberPackage sqlCalcTotalGIRO = new ACMSDAL.TblMemberPackage();//jackie 15/03/2012 int intTotalGIRO = sqlCalcTotalGIRO.CalculateTotalGIRO(strMembershipID, strPackageID, strPackageCode); r["Balance"] = (intTotalGIRO * ACMS.Convert.ToInt32(r["Balance"])); } } } if (ACMS.Convert.ToDBInt32(r["nAdjust"])>=1 && ACMS.Convert.ToInt32(r["nMaxSession"]) < 9999) { r["Balance"]=ACMS.Convert.ToDBInt32(r["Balance"])-ACMS.Convert.ToDBInt32(r["nAdjust"]); } // Start calculate the POWER Package // It is consider PWR Package if the class attendance is GYM Class classAttendanceTableView.RowFilter = "((nStatusID = 1 or nStatusID = 2) AND nTypeID = 1 AND nPackageID ='" +nPackageID+ "')"; if (classAttendanceTableView.Count > 0) { for (int i = 0; i < classAttendanceTableView.Count; i ++) { DataRow row = classAttendanceTableView[i].Row; string dtDate = ACMS.Convert.ToDateTime(row["dtDate"]).ToString("yyyy/MM/dd"); int nAttendanceID = ACMS.Convert.ToInt32(row["nAttendanceID"]); DataRow[] foundRow = gymTable.Select("dtDate = '" + dtDate + "'" + "AND nPackageID = '" + nPackageID + "'"); if (foundRow.Length == 0) { DataRow addRow = gymTable.NewRow(); addRow["dtDate"] = dtDate; addRow["nPackageID"] = nPackageID; addRow["nAttendanceID"] = nAttendanceID; gymTable.Rows.Add(addRow); } } foreach (DataRow pRow in gymTable.Rows) { DateTime dtDate = ACMS.Convert.ToDateTime(pRow["dtDate"]); int nPackageIDInGymTable = ACMS.Convert.ToInt32(pRow["nPackageID"]); string strFilter = string.Format("(nStatusID = 1 or nStatusID = 2) " + " AND nTypeID = 0 AND nPackageID = {0} AND DtDate = #{1}#", nPackageID, dtDate.ToString("yyyy/MM/dd")); DataRow[] foundRow = classAttendanceTable.Select(strFilter, "nPackageID", DataViewRowState.CurrentRows); if (foundRow.Length > 0) r["Balance"] = ACMS.Convert.ToInt32(r["Balance"]) + 1; } if (gymTable.Rows.Count>0) r["Balance"] = ACMS.Convert.ToInt32(r["Balance"]) - gymTable.Rows.Count; gymTable.Rows.Clear(); //////////// //ACMSDAL.TblMemberPackage sqlCalcPackages = new ACMSDAL.TblMemberPackage();//jackie 15/03/2012 //int myDTransactionID = sqlCalcPackages.CalculateSpecialSessionPackages(strMembershipID, nPackageID); //ACMSDAL.TblMemberPackage sqlFindPackageCode = new ACMSDAL.TblMemberPackage(); //string strPackageCode = sqlFindPackageCode.GetPackageCode(strMembershipID, nPackageID); ////if (strPackageCode == "AA(1080/12)" || strPackageCode == "AA(2160/24)") ////{ //// r["Balance"] = ACMS.Convert.ToInt32(r["nMaxSession"]) - myDTransactionID; ////} //if (r["fEntries"].ToString() == "True") //{ // r["Balance"] = ACMS.Convert.ToInt32(r["nMaxSession"]) - myDTransactionID; //} //else // if (strPackageCode == "FTRIAL") // { // r["Balance"] = myDTransactionID; // } // else // ///////// // { // r["Balance"] = ACMS.Convert.ToInt32(r["Balance"]) - gymTable.Rows.Count; // } //gymTable.Rows.Clear(); } } else if (nCategoryID == 3 || nCategoryID == 4 || nCategoryID == 5 || nCategoryID == 6 || nCategoryID == 34) // Service Session { serviceSession.NPackageID = nPackageID; DataView serviceSessionTable = serviceSession.SelectAllWnPackageIDLogic().DefaultView; serviceSessionTable.RowFilter = "nStatusID = 5 or nStatusID = 6"; if (nCategoryID == 34) { r["Balance"] = ACMS.Convert.ToInt32(r["Balance"]) - serviceSessionTable.Count + ACMS.Convert.ToDBInt32(r["nAdjust"]); } else { ACMSDAL.TblMemberPackage sqlCalcPackages = new ACMSDAL.TblMemberPackage();//jackie 15/03/2012 int myDTransactionID = sqlCalcPackages.CalculateSpecialSessionPackages(strMembershipID, nPackageID); ACMSDAL.TblMemberPackage sqlFindPackageCode = new ACMSDAL.TblMemberPackage(); string strPackageCode = sqlFindPackageCode.GetPackageCode(strMembershipID, nPackageID); if (strPackageCode == "AA(1080/12)" || strPackageCode == "AA(2160/24)" ) { r["Balance"] = ACMS.Convert.ToInt32(r["Balance"]) - myDTransactionID; } else if (strPackageCode == "FTRIAL") { r["Balance"] = myDTransactionID; } else { if (nCategoryID == 2 || ACMS.Convert.ToInt32(r["Balance"]) == 9999) //2604 { string strPackageID = nPackageID.ToString(); ACMSDAL.TblMemberPackage sqlCalcTotalGIRO = new ACMSDAL.TblMemberPackage();//jackie 15/03/2012 int intTotalGIRO = sqlCalcTotalGIRO.CalculateTotalGIRO(strMembershipID, strPackageID,strPackageCode); r["Balance"] = (intTotalGIRO * ACMS.Convert.ToInt32(r["Balance"])) - serviceSessionTable.Count; } else { r["Balance"] = ACMS.Convert.ToInt32(r["Balance"]) - serviceSessionTable.Count; } } if (ACMS.Convert.ToDBInt32(r["nAdjust"]) >= 1 && ACMS.Convert.ToInt32(r["nMaxSession"]) < 9999) { //jackie r["Balance"] = ACMS.Convert.ToDBInt32(r["Balance"]) - ACMS.Convert.ToDBInt32(r["nAdjust"]); } } //if (dr["strFreePkgCode"].ToString() != string.Empty) //{ // myPOS.EditItemFreebieAndDiscount(dr["strFreePkgCode"].ToString()); //} } r["strBalNew"] = r["Balance"].ToString(); if (r["dtStartDate"] == DBNull.Value) r["strBalNew"] = "New"; else { if (r["strPackageType"].ToString() == "Normal Package") { if (ACMS.Convert.ToInt32(r["nMaxSession"]) == ACMS.Convert.ToInt32(r["Balance"])) r["strBalNew"] = "New"; } } } } }
private void UpdateStartExpiryDate(System.Data.SqlTypes.SqlInt32 nPackageID) { TblClassAttendance clsAttendance = new TblClassAttendance(); clsAttendance.NPackageID = nPackageID; //DataTable tableClassAttendance = clsAttendance.SelectAllWnPackageIDLogic(); DataView tableClassAttendance = clsAttendance.SelectAllWnPackageIDLogic().DefaultView; tableClassAttendance.RowFilter = "nStatusID = 1 or nStatusID = 2"; if (tableClassAttendance.Count == 0) { myMemberPackage.NPackageID = nPackageID; DataTable tableMemberPackage = myMemberPackage.SelectOne(); if (tableMemberPackage != null && tableMemberPackage.Rows.Count >= 0) { myMemberPackage.DtExpiryDate = System.Data.SqlTypes.SqlDateTime.Null; myMemberPackage.DtStartDate = System.Data.SqlTypes.SqlDateTime.Null; myMemberPackage.NStatusID = 0; myMemberPackage.Update(); } } }
/// <summary> /// Balance for new member package is not allow to set nMaxSession is bcoz need the value zero /// to do calculation In POS /// </summary> /// <param name="strMembershipID"></param> /// <returns></returns> public DataView GetActive_n_NonFreeMemberPackage_For_POS_Calculation(string strMembershipID) { //DataTable table = myMemberPackage.GetActivenNonFreeMemberPackage(strMembershipID); DataTable table = myMemberPackage.GetActivenNonFreeMemberPackage(strMembershipID); if (table != null) { if (!table.Columns.Contains("Balance")) { DataColumn colBalance = new DataColumn("Balance", System.Type.GetType("System.Int32")); table.Columns.Add(colBalance); } if (!table.Columns.Contains("FNew")) { DataColumn colNew = new DataColumn("FNew", System.Type.GetType("System.Int32")); table.Columns.Add(colNew); } TblClassAttendance classAttendance = new TblClassAttendance(); TblServiceSession serviceSession = new TblServiceSession(); foreach (DataRow r in table.Rows) { int nCategoryID = ACMS.Convert.ToInt32(r["nCategoryID"]); int nPackageID = ACMS.Convert.ToInt32(r["nPackageID"]); // class Attendance if (nCategoryID == 1 || nCategoryID == 2) { classAttendance.NPackageID = nPackageID; DataView classAttendanceTable = classAttendance.SelectAllWnPackageIDLogic().DefaultView; classAttendanceTable.RowFilter = "nStatusID = 1 or nStatusID = 2"; if (classAttendanceTable.Count > 0) { r["Balance"] = ACMS.Convert.ToInt32(r["nMaxSession"]) - classAttendanceTable.Count; r["FNew"] = 0; } else { r["Balance"] = 0; r["FNew"] = 1; } } else if (nCategoryID == 3 || nCategoryID == 4 || nCategoryID == 5 || nCategoryID == 6) // Service Session { /// Checkout any Outstanding for nCategoryID == 3 PT 13/09/2012 //if (nCategoryID == 3) //{ // ACMSDAL.TblMemberPackage sqlCalcAnyOS = new ACMSDAL.TblMemberPackage(); // int dOutAmount = sqlCalcAnyOS.CalculateAnyOutStandingPayment(strMembershipID, nCategoryID); // if (dOutAmount > 0) // { // } //} /// serviceSession.NPackageID = nPackageID; DataView serviceSessionTable = serviceSession.SelectAllWnPackageIDLogic().DefaultView; serviceSessionTable.RowFilter = "nStatusID = 5 or nStatusID = 6"; if (serviceSessionTable.Count > 0) { r["Balance"] = ACMS.Convert.ToInt32(r["nMaxSession"]) - serviceSessionTable.Count; r["FNew"] = 0; } else { r["Balance"] = 0; r["FNew"] = 1; } } } } table.DefaultView.RowFilter = " Balance > 0 OR (Balance = 0 AND FNew = 1)"; return table.DefaultView; }
public void TransferMemberPackage(int packageID, string currMemberID, string newMemberID, string remark) { TblClassAttendance classAttendance = new TblClassAttendance(); TblServiceSession serviceSession = new TblServiceSession(); classAttendance.NPackageID = packageID; DataTable tableClassAttendance = classAttendance.SelectAllWnPackageIDLogic(); serviceSession.NPackageID = packageID; DataTable tableServiceSession = serviceSession.SelectAllWnPackageIDLogic(); //classAttendance.nP ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider(); try { classAttendance.MainConnectionProvider = connProvider; serviceSession.MainConnectionProvider = connProvider; myMemberPackage.MainConnectionProvider = connProvider; myAudit.MainConnectionProvider = connProvider; connProvider.OpenConnection(); connProvider.BeginTransaction("TransferMemberPackage"); if (tableClassAttendance != null && tableClassAttendance.Rows.Count > 0) { foreach (DataRow r in tableClassAttendance.Rows) { r["strMembershipID"] = newMemberID; } //classAttendance.StrMembershipID = newMemberID; //classAttendance.UpdateAllWnEmployeeIDLogic(); } if (tableServiceSession != null && tableServiceSession.Rows.Count > 0) { // serviceSession.StrMembershipID = newMemberID; // serviceSession.UpdateAllWnPackageIDLogic(); foreach (DataRow r in tableServiceSession.Rows) { r["strMembershipID"] = newMemberID; } } myMemberPackage.NPackageID = packageID; myMemberPackage.SelectOne(); myMemberPackage.StrMembershipID = newMemberID; myMemberPackage.StrRemarks = remark; myAudit.DtDate = DateTime.Today; myAudit.NAuditTypeID = 1; myAudit.NEmployeeID = ACMSLogic.User.EmployeeID; myAudit.StrAuditEntry = "Transfer Member Package " + packageID.ToString() + "from " + currMemberID + "to " + newMemberID; myAudit.StrReference = packageID.ToString(); classAttendance.SaveData(tableClassAttendance); serviceSession.SaveData(tableServiceSession); myMemberPackage.Update(); myAudit.Insert(); connProvider.CommitTransaction(); } catch (Exception) { connProvider.RollbackTransaction("CancelMemberPackage"); throw new Exception("Failed to transfer Member Package"); } 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; classAttendance.MainConnactionIsCreatedLocal = true; serviceSession.MainConnactionIsCreatedLocal = true; } }
public void PrintClassAttendanceReminder(int nPackageID, string strMembershipID, int nClassInstanceID) { TblCompany comp = new TblCompany(); DataTable compTable = comp.SelectAll(); int nClassLeft = 0; int nDaysToExpire = 0; if (compTable != null && compTable.Rows.Count > 0) { nClassLeft = ACMS.Convert.ToInt32(compTable.Rows[0]["nClassLeft"]); nDaysToExpire = ACMS.Convert.ToInt32(compTable.Rows[0]["nDaysToExpire"]); } TblMemberPackage sqlMemberPackage = new TblMemberPackage(); DataTable memberPackageTable = sqlMemberPackage.GetMemberPackage(nPackageID); ACMSLogic.MemberPackage.CalculateMemberPackageBalance(memberPackageTable.Rows[0]["strPackageCode"].ToString(),strMembershipID, memberPackageTable); DateTime expiryDate = ACMS.Convert.ToDateTime(memberPackageTable.Rows[0]["dtExpiryDate"]); TblMember member = new TblMember(); DataTable memberTable = member.LoadData("Select dtDOB, strMemberName from tblMember Where strMembershipID = @strMembershipID", new string[] {"@strMembershipID"}, new object[] {strMembershipID}); ACMSLogic.Printing myReceiptPrinting = new Printing(); myReceiptPrinting.WriteLine(string.Format("Branch : {0} {1}", User.BranchCode, DateTime.Now.ToString("dd/MM/yyyy hh:mm tt"))); myReceiptPrinting.WriteLine(string.Format("Membership ID : {0}", strMembershipID)); myReceiptPrinting.WriteLine(string.Format("Member Name : {0}", memberTable.Rows[0]["strMemberName"].ToString())); myReceiptPrinting.WriteLine(""); if (memberPackageTable.Rows[0]["strPackageCode"].ToString() == "Unlinked") { myReceiptPrinting.WriteLine("No Valid Fitness Package."); //DataTable packageTable = sqlMemberPackage.LoadData("select mListPrice from tblPackage Where strDescription = 'Trial Gym'"); //myReceiptPrinting.WriteLine("No Valid Fitness Package."); myReceiptPrinting.Print(); return; } bool bClassPrint ; bClassPrint=false; if (memberPackageTable.Rows[0]["dtStartDate"] != DBNull.Value) myReceiptPrinting.WriteLine(string.Format("Package Start On : {0}", ACMS.Convert.ToDateTime(memberPackageTable.Rows[0]["dtStartDate"]).ToString("dd/MM/yyyy"))); if (memberPackageTable.Rows[0]["dtExpiryDate"] != DBNull.Value) myReceiptPrinting.WriteLine(string.Format("Package Expiry Date : {0}", ACMS.Convert.ToDateTime(memberPackageTable.Rows[0]["dtExpiryDate"]).ToString("dd/MM/yyyy"))); if (DateTime.Compare(ACMS.Convert.ToDBDateTime(memberPackageTable.Rows[0]["dtStartDate"]),DateTime.Today.Date)==0) //if (memberPackageTable.Rows[0]["dtExpiryDate"] == DBNull.Value) { TblClassAttendance classAttendance = new TblClassAttendance(); classAttendance.NPackageID = nPackageID; int totalClassAttend = classAttendance.SelectAllWnPackageIDLogic().Rows.Count; if (totalClassAttend == 1) { myReceiptPrinting.WriteLine("Package Has Been Activated."); bClassPrint = true; } } //Print Day To Expiry System.TimeSpan timeSpan = expiryDate.Date.Subtract(DateTime.Today.Date); if (timeSpan.Days <= nDaysToExpire) { myReceiptPrinting.WriteLine(string.Format("Days To Expiry : {0}", timeSpan.Days)); bClassPrint = true; } //Print Member Package Balance int balance = ACMS.Convert.ToInt32(memberPackageTable.Rows[0]["Balance"]); if (balance <= nClassLeft) { myReceiptPrinting.WriteLine(string.Format("Balance Visit : {0} ", balance)); bClassPrint = true; } // Check Member Outstanding Receipt TblReceipt receipt = new TblReceipt(); Decimal mTotalOutstanding = 0; object obj= receipt.GetOutStandingBalance(strMembershipID); if (obj != null) { mTotalOutstanding = (decimal)obj; mTotalOutstanding = decimal.Round(mTotalOutstanding, 2); } if (mTotalOutstanding > 0) { myReceiptPrinting.WriteLine("Outstanding Cash Bill Check."); bClassPrint = true; } if (memberPackageTable.Rows[0]["strRemarks"].ToString().Length > 0) { myReceiptPrinting.WriteLine("Refer To Member Package's Remark."); bClassPrint = true; } if (ACMS.Convert.ToDateTime(memberTable.Rows[0]["DtDOB"]).Date == DateTime.Today.Date) { myReceiptPrinting.WriteLine(""); myReceiptPrinting.WriteLine(string.Format("Happy Birthday To {0}.", memberTable.Rows[0]["strMemberName"].ToString())); bClassPrint = true; } // myReceiptPrinting.Close; if (bClassPrint == true) { myReceiptPrinting.Print(); } else myReceiptPrinting.DelFile(); }
private void ActionTakeWhenVoidReceiptForCategory_7(string strReceiptNo, ConnectionProvider connProvider) { TblMemberCreditPackage memberCreditPackage = new TblMemberCreditPackage(); memberCreditPackage.MainConnectionProvider = connProvider; DataTable table = memberCreditPackage.SelectAllWstrReceiptNo(strReceiptNo); TblMemberPackage sqlMemberPackage = new TblMemberPackage(); sqlMemberPackage.MainConnectionProvider = connProvider; if (table.Rows.Count > 0) { int nCreditPackageID = 0; foreach (DataRow r in table.Rows) { nCreditPackageID = ACMS.Convert.ToInt32(r["nCreditPackageID"]); sqlMemberPackage.NCreditPackageID = nCreditPackageID; DataTable memberPackagetable = sqlMemberPackage.SelectAllWnCreditPackageIDLogic(); if (Convert.ToInt32(memberPackagetable.Compute("COUNT(nStatusID)", "nStatusID=0 OR nStatusID=1")) > 0) throw new Exception("The item(s) has been used, you are not allow to void the receipt"); r["nEmployeeID"] = User.EmployeeID; r["nStatusID"] = 2; r["strRemarks"] = "Voided Receipt"; r["dtLastEditDate"] = System.DateTime.Now; } memberCreditPackage.SaveData(table); } sqlMemberPackage.StrReceiptNo = strReceiptNo; DataTable tblMemPackage = sqlMemberPackage.SelectAllWstrReceiptNo(strReceiptNo); TblClassAttendance sqlClassAttendance = new TblClassAttendance(); TblServiceSession sqlServiceSession = new TblServiceSession(); sqlClassAttendance.MainConnectionProvider = connProvider; sqlServiceSession.MainConnectionProvider = connProvider; if (tblMemPackage.Rows.Count > 0) { int nPackageID = 0; foreach (DataRow row in tblMemPackage.Rows) { nPackageID = ACMS.Convert.ToInt32(row["nPackageID"]); sqlClassAttendance.NPackageID = nPackageID; DataTable classAttTable = sqlClassAttendance.SelectAllWnPackageIDLogic(); if (classAttTable.Rows.Count > 0) throw new Exception("The item(s) has been used, you are not allow to void the receipt"); else { sqlServiceSession.NPackageID = nPackageID; classAttTable = sqlServiceSession.SelectAllWnPackageIDLogic(); if (classAttTable.Rows.Count > 0) throw new Exception("The item(s) has been used, you are not allow to void the receipt"); } row["nEmployeeID"] = User.EmployeeID; row["nStatusID"] = 2; row["strRemarks"] = "Voided Receipt"; row["dtLastEdit"] = System.DateTime.Now; } sqlMemberPackage.SaveData(tblMemPackage); } }
private void ActionTakeWhenVoidReceiptForCategory_2(string strReceiptNo, ConnectionProvider connProvider) { TblMemberPackage memberPackage = new TblMemberPackage(); TblGIRO giro = new TblGIRO(); giro.MainConnectionProvider = connProvider; memberPackage.MainConnectionProvider = connProvider; memberPackage.StrReceiptNo = strReceiptNo; DataTable table = memberPackage.SelectAllWstrReceiptNo(strReceiptNo); TblClassAttendance sqlClassAttendance = new TblClassAttendance(); TblServiceSession sqlServiceSession = new TblServiceSession(); sqlClassAttendance.MainConnectionProvider = connProvider; sqlServiceSession.MainConnectionProvider = connProvider; if (table.Rows.Count > 0) { int nPackageID = 0; foreach (DataRow r in table.Rows) { nPackageID = ACMS.Convert.ToInt32(r["nPackageID"]); sqlClassAttendance.NPackageID = nPackageID; DataTable classAttTable = sqlClassAttendance.SelectAllWnPackageIDLogic(); if (classAttTable.Rows.Count > 0) throw new Exception("The item(s) has been used, you are not allow to void the receipt"); else { sqlServiceSession.NPackageID = nPackageID; classAttTable = sqlServiceSession.SelectAllWnPackageIDLogic(); if (classAttTable.Rows.Count > 0) throw new Exception("The item(s) has been used, you are not allow to void the receipt"); } r["nEmployeeID"] = User.EmployeeID; r["nStatusID"] = 2; r["strRemarks"] = "Voided Receipt"; r["dtLastEdit"] = System.DateTime.Now; if (r["nGIRORefID"] != DBNull.Value) { int nGiroRefID = ACMS.Convert.ToInt32(r["nGIRORefID"]); giro.NGIRORefID = nGiroRefID; giro.SelectOne(); giro.NEmployeeID = User.EmployeeID; giro.NStatusID = 4; giro.StrRemarks = "Voided Receipt" + " - " + System.DateTime.Now.ToString("dd/MM/yyyy"); giro.Update(); } } memberPackage.SaveData(table); } }
private void ActionTakeWhenVoidReceiptForCategory_1_3_4_5_6_8_9_14_23(string strReceiptNo, ConnectionProvider connProvider) { //1 Fitness Package //3 PT Package //4 Spa Single Treatment //5 Spa Package //6 IPL Package //8 Fitness Combined Package //9 Spa Combined Package TblMemberPackage memberPackage = new TblMemberPackage(); memberPackage.MainConnectionProvider = connProvider; memberPackage.StrReceiptNo = strReceiptNo; DataTable table = memberPackage.SelectAllWstrReceiptNo(strReceiptNo); TblClassAttendance sqlClassAttendance = new TblClassAttendance(); TblServiceSession sqlServiceSession = new TblServiceSession(); sqlClassAttendance.MainConnectionProvider = connProvider; sqlServiceSession.MainConnectionProvider = connProvider; if (table.Rows.Count > 0) { int nPackageID = 0; foreach (DataRow r in table.Rows) { nPackageID = ACMS.Convert.ToInt32(r["nPackageID"]); sqlClassAttendance.NPackageID = nPackageID; DataTable classAttTable = sqlClassAttendance.SelectAllWnPackageIDLogic(); if (classAttTable.Rows.Count > 0) throw new Exception("The item(s) has been used, you are not allow to void the receipt"); else { sqlServiceSession.NPackageID = nPackageID; classAttTable = sqlServiceSession.SelectAllWnPackageIDLogic(); if (classAttTable.Rows.Count > 0) throw new Exception("The item(s) has been used, you are not allow to void the receipt"); } r["nEmployeeID"] = User.EmployeeID; r["nStatusID"] = 2; r["strRemarks"] = "Voided Receipt"; r["dtLastEdit"] = System.DateTime.Now; } memberPackage.SaveData(table); } TblMemberCreditPackage memberCreditPackage = new TblMemberCreditPackage(); memberCreditPackage.MainConnectionProvider = connProvider; memberCreditPackage.StrReceiptNo = strReceiptNo; DataTable table2 = memberCreditPackage.SelectAllWstrReceiptNo(strReceiptNo); TblMemberPackage sqlMemberPackage = new TblMemberPackage(); sqlMemberPackage.MainConnectionProvider = connProvider; if (table2.Rows.Count > 0) { int nCreditPackageID = 0; foreach (DataRow r in table2.Rows) { nCreditPackageID = ACMS.Convert.ToInt32(r["nCreditPackageID"]); DataTable mpTable = sqlMemberPackage.SelectAllWnCreditPackageIDLogic(); if (mpTable.Rows.Count > 0) throw new Exception("The item(s) has been used, you are not allow to void the receipt"); r["nEmployeeID"] = User.EmployeeID; r["nStatusID"] = 2; r["strRemarks"] = "Voided Receipt"; r["dtLastEditDate"] = System.DateTime.Now; } memberCreditPackage.SaveData(table2); } }
public DataTable GetMemberPackage(string strMemberShipID) { TblMemberPackage memberPackege = new TblMemberPackage(); DataTable table = memberPackege.GetActiveMemberPackageForServiceSession(); if (!table.Columns.Contains("Balance")) { DataColumn colBalance = new DataColumn("Balance", System.Type.GetType("System.Int32")); table.Columns.Add(colBalance); } TblClassAttendance classAttendance = new TblClassAttendance(); TblServiceSession serviceSession = new TblServiceSession(); foreach (DataRow r in table.Rows) { int nCategoryID = ACMS.Convert.ToInt32(r["nCategoryID"]); int nPackageID = ACMS.Convert.ToInt32(r["nPackageID"]); // class Attendance if (nCategoryID == 1 || nCategoryID == 2) { classAttendance.NPackageID = nPackageID; DataView classAttendanceTable = classAttendance.SelectAllWnPackageIDLogic().DefaultView; classAttendanceTable.RowFilter = "nStatusID = 1 or nStatusID = 2"; r["Balance"] = ACMS.Convert.ToInt32(r["nMaxSession"]) - classAttendanceTable.Count; } else if (nCategoryID == 4 || nCategoryID == 5 || nCategoryID == 6) // Service Session { serviceSession.NPackageID = nPackageID; DataView serviceSessionTable = serviceSession.SelectAllWnPackageIDLogic().DefaultView; serviceSessionTable.RowFilter = "nStatusID = 5 or nStatusID = 6"; r["Balance"] = ACMS.Convert.ToInt32(r["nMaxSession"]) - serviceSessionTable.Count; } } return table; }