public void DeleteServiceSession(int nPackageID, int nSessionID, string remark)
{
// i need to know it is only one record, and it is 1st record also
TblServiceSession serviceSession = new TblServiceSession();
serviceSession.NPackageID = nPackageID;
DataTable table = serviceSession.SelectAllWnPackageIDLogic();
bool setNullInMemberPackage = false;
bool isDelete1stRecord = false;
DateTime newfirstRecordServiceSessionDate = DateTime.MinValue;
if (table != null && table.Rows.Count > 0)
{
if (table.Rows.Count == 1)
{
setNullInMemberPackage = true;
}
else
{
DataRow [] rowList = table.Select("", "nSessionID", DataViewRowState.CurrentRows);
int firstRecord_nSessionID = ACMS.Convert.ToInt32(rowList[0]["nSessionID"]);
if (firstRecord_nSessionID == nSessionID)
{
isDelete1stRecord = true;
newfirstRecordServiceSessionDate = ACMS.Convert.ToDateTime(rowList[1]["dtDate"]);
}
}
}
serviceSession.NSessionID = nSessionID;
serviceSession.SelectOne();
serviceSession.DtLastEditDate = DateTime.Now;
serviceSession.NEmployeeID = ACMSLogic.User.EmployeeID;
//serviceSession.NPackageID = -1;
serviceSession.NStatusID = 1;
serviceSession.StrRemarks = remark;
TblAudit audit = new TblAudit();
audit.DtDate = DateTime.Now;
audit.NAuditTypeID = 4;
audit.NEmployeeID = User.EmployeeID;
audit.StrAuditEntry = "Delete service session with nSessionID = " + nSessionID+
" and nPackageID = " + nPackageID;
audit.StrReference = nSessionID.ToString();
TblMemberPackage memberPackage = new TblMemberPackage();
memberPackage.NPackageID = nPackageID;
memberPackage.SelectOne();
if (setNullInMemberPackage)
{
bool isUnlimited = memberPackage.IsUnlimitedPackage(nPackageID);
if (!isUnlimited)
{
memberPackage.DtStartDate = System.Data.SqlTypes.SqlDateTime.Null;
memberPackage.DtExpiryDate = System.Data.SqlTypes.SqlDateTime.Null;
memberPackage.DtLastEdit = DateTime.Now;
memberPackage.NEmployeeID = User.EmployeeID;
}
}
else if (isDelete1stRecord)
{
TimeSpan duration = memberPackage.DtExpiryDate.Value.Subtract(memberPackage.DtStartDate.Value);
DateTime newExpriry = newfirstRecordServiceSessionDate.Add(duration);
memberPackage.DtExpiryDate = newExpriry;
memberPackage.DtStartDate = newfirstRecordServiceSessionDate;
}
ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();
try
{
memberPackage.MainConnectionProvider = connProvider;
serviceSession.MainConnectionProvider = connProvider;
audit.MainConnectionProvider = connProvider;
connProvider.OpenConnection();
connProvider.BeginTransaction("DeleteServiceSession");
memberPackage.Update();
audit.Insert();
serviceSession.Update();
connProvider.CommitTransaction();
//return true;
}
catch (Exception)
{
connProvider.RollbackTransaction("DeleteServiceSession");
throw new Exception("Failed to delete Service Session");
}
finally
{
if (connProvider.CurrentTransaction != null)
connProvider.CurrentTransaction.Dispose();
if (connProvider.DBConnection != null)
{
if (connProvider.DBConnection.State == ConnectionState.Open)
connProvider.DBConnection.Close();
}
memberPackage.MainConnactionIsCreatedLocal = true;
serviceSession.MainConnactionIsCreatedLocal = true;
audit.MainConnactionIsCreatedLocal = true;
}
}
/// <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;
}
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";
}
}
}
}
}
//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"]);
}
}
}
}
/// <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;
}
}
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 void DeleteCreditPackageUsage(int nPackageID)
{
int nCreditPackageID;
TblServiceSession ssUsage = new TblServiceSession();
ssUsage.NPackageID = nPackageID;
DataTable tblUsage = ssUsage.SelectAllWnPackageIDLogic();
if (tblUsage.Rows.Count > 0)
throw new Exception("You Cant deleted a Used Package.");
TblMemberPackage mmbPckg = new TblMemberPackage();
mmbPckg.NPackageID = nPackageID;
DataTable table = mmbPckg.SelectOne();
if (table == null || table.Rows.Count == 0)
throw new Exception("The Data has been deleted from database.");
mmbPckg.NStatusID = 2;
mmbPckg.Update();
nCreditPackageID = mmbPckg.NCreditPackageID.Value;
//Check any package redeemed from this credit package
ACMSDAL.TblMemberPackage memberPackage = new TblMemberPackage();
string cmdText = "Select COUNT(*) from TblMemberPackage Where (nStatusID = 0 or nStatusID = 1) AND " +
" nCreditPackageID = @nCreditPackageID ";
int nExist = (int) memberPackage.ExecuteScalar(cmdText, new string [] {"@nCreditPackageID"}, new object[] {nCreditPackageID});
if (nExist==0)
{
TblMemberCreditPackage creditPackage = new TblMemberCreditPackage();
creditPackage.NCreditPackageID = nCreditPackageID;
creditPackage.SelectOne();
creditPackage.DtStartDate = SqlDateTime.Null;
creditPackage.DtExpiryDate = SqlDateTime.Null;
creditPackage.Update();
}
}
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;
}