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);
}
}
/// <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 ForgetCardTransaction(ACMSDAL.ConnectionProvider connProvider, ACMSLogic.POS pos)
{
if (pos.NCategoryID == 17 && pos.POSForgetCardAction == ACMSLogic.POS.ForgetCardAction.Refund)
{
TblClassAttendance classAttendance = new TblClassAttendance();
classAttendance.MainConnectionProvider = connProvider;
TblReceipt sqlReceipt = new TblReceipt();
sqlReceipt.MainConnectionProvider = connProvider;
foreach (DataRow row in pos.ReceiptItemsTable.Rows)
{
DateTime dtDate = ACMS.Convert.ToDateTime(row["strReferenceNo"]);
DataTable classAttendanceTable = classAttendance.GetForgetCardClassAttendance(pos.StrMembershipID, dtDate);
foreach (DataRow r in classAttendanceTable.Rows)
{
r["fRefunded"] = true;
}
classAttendance.SaveData(classAttendanceTable);
/*
string receiptToVoid = row["strCode"].ToString();
sqlReceipt.StrReceiptNo = receiptToVoid;
sqlReceipt.SelectOne();
sqlReceipt.FVoid = SqlBoolean.True;
sqlReceipt.Update();*/
}
}
}
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"]);
}
}
}
}
public DataTable GetUnLinkedClassAttendance(string memberShipID)
{
TblClassAttendance classAttendance = new TblClassAttendance();
return classAttendance.GetUnLinkedClassAttendance(memberShipID);
}
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 CancelClassInstance(int nClassInstanceID, string remark)
{
myClassInstance.NClassInstanceID = nClassInstanceID;
myClassInstance.SelectOne();
myClassInstance.FCancelled = System.Data.SqlTypes.SqlBoolean.True;
myClassInstance.StrRemarks = remark;
TblAudit audit = new TblAudit();
audit.DtDate = DateTime.Today;
audit.NAuditTypeID = AuditTypeID.ClassAuditTypeID;
audit.NEmployeeID = ACMSLogic.User.EmployeeID;
audit.StrAuditEntry = "Cancel class instance " + nClassInstanceID.ToString();
audit.StrReference = nClassInstanceID.ToString();
TblClassAttendance classAttendance = new TblClassAttendance();
classAttendance.NClassInstanceID = nClassInstanceID;
DataTable classAttendanceTable = classAttendance.SelectAllWnClassInstanceIDLogic();
if (classAttendanceTable != null && classAttendanceTable.Rows.Count > 0)
{
foreach (DataRow r in classAttendanceTable.Rows)
{
r["nStatusID"] = 3;
}
}
ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();
try
{
myClassInstance.MainConnectionProvider = connProvider;
audit.MainConnectionProvider = connProvider;
classAttendance.MainConnectionProvider = connProvider;
connProvider.OpenConnection();
connProvider.BeginTransaction("CancelClassInstance");
myClassInstance.Update();
audit.Insert();
classAttendance.SaveData(classAttendanceTable);
connProvider.CommitTransaction();
DataTable dtAttendance = new DataTable();
dtAttendance = GetClassAttendanceReservedAndAttended(nClassInstanceID);
DialogResult result1 = MessageBox.Show("Push Notification to Mobile app users? ", "Confirm", MessageBoxButtons.YesNo);
if (result1 == DialogResult.Yes)
{
foreach (DataRow row in dtAttendance.Rows)
{
string strMsgContent = row["strDescription"].ToString() + " at " + row["strBranchName"] + " at " + Convert.ToDateTime(myClassInstance.DtStartTime).ToString("h.mmtt") + " on " + Convert.ToDateTime(myClassInstance.DtStartTime).ToString("dd MMM") + " has been cancelled. Sorry for any inconvenience caused.";
PushNotification(row["strMembershipID"].ToString(), strMsgContent);
}
}
}
catch (Exception)
{
connProvider.RollbackTransaction("CancelClassInstance");
throw;
}
finally
{
if (connProvider.CurrentTransaction != null)
connProvider.CurrentTransaction.Dispose();
if (connProvider.DBConnection != null)
{
if (connProvider.DBConnection.State == ConnectionState.Open)
connProvider.DBConnection.Close();
}
myClassInstance.MainConnactionIsCreatedLocal = true;
}
}
public void CancelReservation(int nAttendanceID)
{
TblCompany comp = new TblCompany();
DataTable table = comp.SelectAll();
int nhourBeforeToDelete = 0;
if (table != null && table.Rows.Count>0)
{
nhourBeforeToDelete = ACMS.Convert.ToInt32(table.Rows[0]["nCancelBookingLimit"]);
}
TblClassAttendance classAttendance = new TblClassAttendance();
classAttendance.NAttendanceID = nAttendanceID;
classAttendance.SelectOne();
TblClassInstance classInstance = new TblClassInstance();
classInstance.NClassInstanceID = classAttendance.NClassInstanceID.Value;
classInstance.SelectOne();
if (DateTime.Today.Date > classInstance.DtDate.Value.Date)
return;
else if (DateTime.Today.Date == classInstance.DtDate.Value.Date)
{
// if (oUser.DateTime.Now.Hour + nhourBeforeToDelete > classAttendance.DtStartTime.Value.Hour)
// throw new Exception("You are not allow to cancel this reservation because you have passed the time period that allow you to cancel the reservation.");
// else
// {
classAttendance.NStatusID = 3;
classAttendance.Update();
//}
}
else
{
try
{
classAttendance.NStatusID = 3;
classAttendance.Update();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
public bool VerifyIntructor(int nClassInstanceIDToBeVerified, int verifyUserID,string verifyUserPassword,
int nStandingIntructorID, string remark)
{
TblEmployee verifyUser = new TblEmployee();
verifyUser.NEmployeeID = verifyUserID;
verifyUser.SelectOne();
int [,] intArray = new int[50,2];
int ArrayNo=1;
if (verifyUser.StrPassword.Value != verifyUserPassword)
return false;
myClassInstance.NClassInstanceID = nClassInstanceIDToBeVerified;
myClassInstance.SelectOne();
if (myClassInstance.NActualInstructorID.IsNull)
throw new Exception("Missing Intructor login info");
if (myClassInstance.NActualInstructorID.Value == verifyUserID)
throw new Exception("Instructor are not allowed to verfiy for the same class.");
if (!myClassInstance.NVerifyID.IsNull)
throw new Exception("This class have been verify by other staff with the employee ID : "+ myClassInstance.NVerifyID.Value.ToString());
bool isLate =false;
DateTime StartTime;
DateTime LoginTime;
myClassInstance.MInstructorDeduction=0;
StartTime=ACMS.Convert.ToDBDateTime(myClassInstance.DtStartTime.Value.ToLongTimeString());
LoginTime=ACMS.Convert.ToDBDateTime(myClassInstance.DtInstructorLogin.Value.ToLongTimeString());
if (DateTime.Compare(LoginTime,StartTime)>0)
{
isLate=true;
}
bool haveStandingIntructor = nStandingIntructorID > 0;
if (haveStandingIntructor)
myClassInstance.NStandinInstructorID = nStandingIntructorID;
if (isLate)
{
TblCompany company = new TblCompany();
DataTable companyTable = company.SelectAll();
decimal instructorLateDeductionFee = 0;
if (companyTable.Rows.Count > 0)
{
instructorLateDeductionFee = ACMS.Convert.ToDecimal(companyTable.Rows[0]["mInstructorLateDeductionFee"]);
}
myClassInstance.MInstructorDeduction=instructorLateDeductionFee;
if (nStandingIntructorID != 0)
myClassInstance.MStandinInstructorFees = instructorLateDeductionFee;
}
int nCommissionTypeID = myClassInstance.NCommissionTypeID.Value;
TblEmployee actualInstructor = new TblEmployee();
actualInstructor.NEmployeeID = myClassInstance.NActualInstructorID;
actualInstructor.SelectOne();
int nInstructorTypeID = ACMS.Convert.ToInt32(actualInstructor.NInstructorTypeID);
myClassInstance.MInstructorFees = (GetCommission(nCommissionTypeID, nInstructorTypeID) - myClassInstance.MInstructorDeduction);
TblClassAttendance classAttendance = new TblClassAttendance();
classAttendance.NClassInstanceID = nClassInstanceIDToBeVerified;
DataTable classAttendanceTable = classAttendance.SelectAllWnClassInstanceIDLogic();
DataRow[] classAttendanceRowList = null;
if (classAttendanceTable != null && classAttendanceTable.Rows.Count > 0)
{
classAttendanceRowList = classAttendanceTable.Select("nStatusID = 0", "nAttendanceID", DataViewRowState.CurrentRows);
foreach (DataRow r in classAttendanceRowList)
{
r["nStatusID"] = 2;
r["nTypeID"]=0;
}
}
ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();
try
{
myClassInstance.MainConnectionProvider = connProvider;
classAttendance.MainConnectionProvider = connProvider;
connProvider.OpenConnection();
connProvider.BeginTransaction("VerifyClassInstance");
myClassInstance.NVerifyID = verifyUserID;
myClassInstance.StrRemarks = remark;
myClassInstance.Update();
classAttendance.SaveData(classAttendanceTable);
TblMemberPackage sqlMemberPackage = new TblMemberPackage();
sqlMemberPackage.MainConnectionProvider = connProvider;
if (classAttendanceRowList != null)
{
int nPackageID = -1;
foreach (DataRow r in classAttendanceRowList)
{
nPackageID = ACMS.Convert.ToInt32(r["nPackageID"]);
int nAttendanceID=ACMS.Convert.ToInt32(r["nAttendanceID"]);
DataTable memberPackageTable = sqlMemberPackage.GetMemberPackage(nPackageID);
if (memberPackageTable.Rows[0]["strPackageCode"].ToString() != "Unlinked")
{
#region test
//MemberPackage.CalculateMemberPackageBalance(memberPackageTable, connProvider);
if (ACMS.Convert.ToInt32(memberPackageTable.Rows[0]["Balance"]) > 0)
{
DateTime classDate = myClassInstance.DtDate.IsNull ? DateTime.Today.Date : myClassInstance.DtDate.Value;
DataRow masterRow = memberPackageTable.Rows[0];// Means new class Attendance gonna insert
if (masterRow["dtStartDate"] == DBNull.Value &&
masterRow["dtExpiryDate"] == DBNull.Value)
{
TblPackage package = new TblPackage();
package.StrPackageCode = masterRow["strPackageCode"].ToString();
package.SelectOne();
if (package.NPackageDuration == 0 && package.NPackageDay > 0)
{
masterRow["dtStartDate"] = classDate;
masterRow["dtExpiryDate"] = classDate.AddDays(package.NPackageDay.Value -1);
}
else
{
masterRow["dtStartDate"] = classDate;
masterRow["dtExpiryDate"] = classDate.AddMonths(package.NPackageDuration.Value).AddDays(-1);
}
}
else
{
DateTime memberPackageStartDate = ACMS.Convert.ToDateTime(masterRow["DtStartDate"]);
DateTime memberPackageExpiryDate = ACMS.Convert.ToDateTime(masterRow["dtExpiryDate"]);
if (memberPackageStartDate > classDate)
{
masterRow["dtExpiryDate"] = memberPackageExpiryDate.Subtract(memberPackageStartDate.Subtract(classDate));
}
}
}
else if (ACMS.Convert.ToInt32(memberPackageTable.Rows[0]["Balance"]) <=0)
{
intArray[ArrayNo,0]=nPackageID;
intArray[ArrayNo,1]=nAttendanceID;
ArrayNo=ArrayNo+1;
MessageBox.Show("Package Balance of " + memberPackageTable.Rows[0]["strMembershipID"].ToString().TrimEnd() + " is Zero. Attendance will be cancel");
}
#endregion
}
if (ACMS.Convert.ToInt32(memberPackageTable.Rows[0]["nMaxSession"]) == 9999 && memberPackageTable.Rows[0]["fGIRO"].ToString() != "1")
{
DateTime dtExpiryDate = ACMS.Convert.ToDateTime(memberPackageTable.Rows[0]["dtExpiryDate"]);
DateTime dtStartDate = ACMS.Convert.ToDateTime(memberPackageTable.Rows[0]["dtStartDate"]);
if (dtExpiryDate != DateTime.MinValue)
{
memberPackageTable.Rows[0]["dtExpiryDate"] = dtExpiryDate.AddDays(-1);
}
}
sqlMemberPackage.SaveData(memberPackageTable);
}
}
connProvider.CommitTransaction();
}
catch (Exception)
{
connProvider.RollbackTransaction("VerifyClassInstance");
throw;
}
finally
{
if (connProvider.CurrentTransaction != null)
connProvider.CurrentTransaction.Dispose();
if (connProvider.DBConnection != null)
{
if (connProvider.DBConnection.State == ConnectionState.Open)
connProvider.DBConnection.Close();
}
myClassInstance.MainConnactionIsCreatedLocal = true;
classAttendance.MainConnactionIsCreatedLocal = true;
}
for (int j=1;j<=50;j++)
{
if (intArray[j,0]>0) DeleteClassAttendance(intArray[j,0],intArray[j,1],"");
else if (intArray[j,0]<=0)
{return true;}
}
return true;
}
public DataTable ViewReservation(int nClassInstanceID)
{
TblClassAttendance classAttendance = new TblClassAttendance();
return classAttendance.GetReservation(nClassInstanceID);
}
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);
}
}
/// <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 DataTable GetAttendedMember(int nClassInstanceID)
{
TblClassAttendance classAttendance = new TblClassAttendance();
DataTable table = classAttendance.LoadData(string.Format("Select strMembershipID From tblClassAttendance Where nClassInstanceID = {0}", nClassInstanceID));
return table;
}
public DataTable GetClassAttendance(int nPackageID, string strMemberShipID, int nTypeID)
{
TblClassAttendance classAttendance = new TblClassAttendance();
return classAttendance.GetClassAttendancesBasePackageID(nPackageID, strMemberShipID, nTypeID);
}
public DataTable GetClassAttendance(int nClassInstanceID)
{
TblClassAttendance classAttendance = new TblClassAttendance();
DataTable table = classAttendance.GetClassAttendancesBaseClassInstanceID(nClassInstanceID);
return table;
}
public bool NewClassAttendance(int nPackageID, string strMemberShipID, int nClassInstance,
int nType, string strBranchCode, DateTime classDate,
DateTime startTime, DateTime endTime, bool isRefunded)
{
if (!isRefunded)
{
ACMSDAL.TblReceipt sqlReceipt = new ACMSDAL.TblReceipt();
DataTable receipttable = sqlReceipt.GetToDayForgetCardReceipt(strMemberShipID, 17);
if (receipttable == null || receipttable.Rows.Count == 0)
{
throw new Exception("You have yet to pay the forget card deposit today.");
}
}
TblClassAttendance classAttendance = new TblClassAttendance();
bool isPostFromOtherRecord = false;
bool needtoAddBackOneDayExpiryDate = false;
bool BalanceStatus = true;
DataTable memberPackageTable = null;
if (nType != 1)
{
DataRow classAttendanceRow = null;
bool isAttendedNow = classAttendance.IsAttendedThisClass(nClassInstance, strMemberShipID, ref classAttendanceRow);
if (isAttendedNow)
{
int nStatusID = ACMS.Convert.ToInt32(classAttendanceRow["nStatusID"]);
if (nStatusID == 1)
{
// Process
throw new Exception("Member already been marked in this class.");
}
else
{
//Forfeit and need to change to process
if (classAttendanceRow != null)
{
int nClassAttendanceID = ACMS.Convert.ToInt32(classAttendanceRow["nAttendanceID"]);
classAttendance.NAttendanceID = nClassAttendanceID;
classAttendance.SelectOne();
isPostFromOtherRecord = true;
nPackageID = classAttendance.NPackageID.Value;
isRefunded = classAttendance.FRefunded.IsNull ? true : classAttendance.FRefunded.Value;
// this only true if the package is unlimited one
memberPackageTable = myMemberPackage.GetMemberPackage(nPackageID);
if (ACMS.Convert.ToInt32(memberPackageTable.Rows[0]["nMaxSession"]) == 9999)
needtoAddBackOneDayExpiryDate = true;
}
}
}
else if (classAttendanceRow != null && nType == 0)
{
int nClassAttendanceID = ACMS.Convert.ToInt32(classAttendanceRow["nAttendanceID"]);
classAttendance.NAttendanceID = nClassAttendanceID;
classAttendance.SelectOne();
isPostFromOtherRecord = true;
nPackageID = classAttendance.NPackageID.Value;
isRefunded = classAttendance.FRefunded.IsNull ? true : classAttendance.FRefunded.Value;
}
}
if (memberPackageTable == null)
{
myMemberPackage.NPackageID = nPackageID;
memberPackageTable = myMemberPackage.GetMemberPackage(nPackageID);
}
if (memberPackageTable == null || memberPackageTable.Rows.Count == 0)
throw new Exception("Failed to create new class attendance. Member Package with npackageID = " + nPackageID.ToString() + "has been deleted");
classAttendance.NPackageID = nPackageID;
classAttendance.StrMembershipID = strMemberShipID;
classAttendance.NClassInstanceID = nClassInstance;
classAttendance.NTypeID = nType;
classAttendance.StrBranchCode = strBranchCode;
classAttendance.DtDate = classDate;
classAttendance.DtStartTime = startTime;
classAttendance.DtEndTime = endTime;
classAttendance.DtLastEditDate = DateTime.Now;
classAttendance.NEmployeeID = ACMSLogic.User.EmployeeID;
//indicated free class or not
TblClassInstance classInstance = new TblClassInstance();
DataTable Instance = classInstance.LoadData("Select fFree from tblClassInstance Where nClassInstanceID = @nClassInstanceID",
new string[] { "@nClassInstanceID" }, new object[] { nClassInstance });
if (Instance.Rows[0][0].ToString() == "True")
classAttendance.NStatusID = 4;
else
classAttendance.NStatusID = 1;
classAttendance.FRefunded = isRefunded;// System.Data.SqlTypes.SqlBoolean.
//UnLinked is for temporary only
if (memberPackageTable.Rows[0]["strPackageCode"].ToString() != "Unlinked")
{
CalculateMemberPackageBalance(memberPackageTable.Rows[0]["strPackageCode"].ToString(), strMemberShipID, memberPackageTable);
DataRow masterRow = memberPackageTable.Rows[0];
// if (ACMS.Convert.ToDBInt32(memberPackageTable.Rows[0]["Balance"])<999 && ACMS.Convert.ToDBInt32(memberPackageTable.Rows[0]["nAdjust"])>=1)
// {
// memberPackageTable.Rows[0]["Balance"]=ACMS.Convert.ToDBInt32(memberPackageTable.Rows[0]["Balance"])- ACMS.Convert.ToDBInt32(memberPackageTable.Rows[0]["nAdjust"]);
// }
if (ACMS.Convert.ToInt32(memberPackageTable.Rows[0]["Balance"]) <= 0)
{
throw new Exception("Balance is zero");
masterRow["nBalance"] = false;
BalanceStatus = false;
}
if (masterRow["fGiro"].ToString() != "1")
{
if ((masterRow["dtStartDate"] == DBNull.Value &&
masterRow["dtExpiryDate"] == DBNull.Value) || (masterRow["dtStartDate"] != DBNull.Value && Convert.ToDateTime(masterRow["dtStartDate"]) > classDate))
{
// Means new class Attendance gonna insert
TblPackage package = new TblPackage();
package.StrPackageCode = masterRow["strPackageCode"].ToString();
package.SelectOne();
//if (package.NPackageDuration.Value == 0 && package.NPackageDay.Value > 0)
//{
// masterRow["dtStartDate"] = classDate;
// masterRow["dtExpiryDate"] = classDate.AddDays(package.NPackageDay.Value - 1);
//}
//else
//{
// masterRow["dtStartDate"] = classDate;
// masterRow["dtExpiryDate"] = classDate.AddMonths(package.NPackageDuration.Value).AddDays(-1);
//}
//jackie Start 05042012
masterRow["dtStartDate"] = classDate;
if (package.NPackageDuration.Value == 0)
{
string txt = package.StrPackageCode.ToString();
if (txt == "FTRIAL")
{
masterRow["dtExpiryDate"] = classDate.AddDays(1).AddDays(0);
}
else
{
string strduration;
int cstarts = txt.IndexOf("(") + 1;
int len = txt.IndexOf(")") - cstarts;
txt = txt.Substring(cstarts, len);
if (string.IsNullOrEmpty(txt))
{
strduration = string.Empty;
}
else
{
int lens = txt.Length;
strduration = txt.Substring(txt.Length - 1, 1);
}
if (strduration == "D")
{
strduration = txt.Replace("D", "");
int iduration = Int32.Parse(strduration);
masterRow["dtExpiryDate"] = classDate.AddDays(iduration - 1).AddDays(0);
}
if (strduration == "W")
{
strduration = txt.Replace("W", "");
int iduration = Int32.Parse(strduration);
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 DataTable GetClassAttendanceReservedAndAttended(int nClassInstanceID)
{
TblClassAttendance classAttendance = new TblClassAttendance();
DataTable table = classAttendance.GetClassAttendancesBaseClassInstanceID(nClassInstanceID);
table.DefaultView.RowFilter = "(nStatusID = 0 or nStatusID = 1) and fMobileBooking=1";
return table.DefaultView.ToTable();
}
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();
if (connProvider.DBConnection != null)
{
if (connProvider.DBConnection.State == ConnectionState.Open)
connProvider.DBConnection.Close();
}
myMemberPackage.MainConnactionIsCreatedLocal = true;
classAttendance.MainConnactionIsCreatedLocal = true;
}
}
}
public DataView GetClassAttendanceView(int nClassInstanceID)
{
TblClassAttendance classAttendance = new TblClassAttendance();
DataTable table = classAttendance.GetClassAttendancesBaseClassInstanceID(nClassInstanceID);
table.DefaultView.RowFilter = "nStatusID = 1";
return table.DefaultView;
}
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();
}
}
}
public DataView GetGymAttendance(DateTime dtDate)
{
TblClassAttendance classAttendance = new TblClassAttendance();
DataTable table = classAttendance.GetGymAttendancesBaseDate(dtDate, ACMSLogic.User.BranchCode);
table.DefaultView.RowFilter = "nTypeID = 1 And (nStatusID = 1 or nStatusID = 2 or nStatusID = 4 or nStatusID is null)";
return table.DefaultView;
}
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;
}
}
public DataView GetWaitingList(int nClassInstanceID)
{
TblClassAttendance classAttendance = new TblClassAttendance();
DataTable table = classAttendance.GetClassAttendancesBaseClassInstanceID(nClassInstanceID);
table.DefaultView.RowFilter = "nStatusID = 1 or nStatusID = 2 or nStatusID = 4 or nStatusID is null";
return table.DefaultView;
}
internal DataTable GetAttendedClassCode(int nPackageID)
{
TblClassAttendance classAttendance = new TblClassAttendance();
return classAttendance.GetValidClassCodeBasePackageID(nPackageID);
}
public void NormalReserve(string strMembershipID, int nPackageID, int nClassInstanceID, bool needToVerifyMemberPackage)
{
TblClassInstance classInstance = new TblClassInstance();
classInstance.NClassInstanceID = nClassInstanceID;
classInstance.SelectOne();
TblClassAttendance classAttendance = new TblClassAttendance();
DateTime classDate = new DateTime(classInstance.DtDate.Value.Year, classInstance.DtDate.Value.Month, classInstance.DtDate.Value.Day,
classInstance.DtStartTime.Value.Hour, classInstance.DtStartTime.Value.Minute, classInstance.DtStartTime.Value.Second);
if (DateTime.Today > classDate)
throw new Exception("You are not allow to reserve the previous day class.");
if (classAttendance.IsReservedThisClass(nClassInstanceID, strMembershipID))
{
throw new Exception("You have reserved this class.");
}
if (classAttendance.GetReservation(nClassInstanceID).Rows.Count > ACMS.Convert.ToInt32(classInstance.NMaxNo))
{
throw new Exception("Class is fully reserved by members. Please try again later.");
}
if (needToVerifyMemberPackage)
{
if (!VerifyMemberPackageAllowCertainClass(nPackageID, classInstance.StrClassCode.Value))
{
//throw new Exception("The package you wish to use is not allow to attend the class. Please use other package.");
DialogResult result = MessageBox.Show("The package you wish to use is not allow to attend the class. Do you still want to reserve it. \n " +
"(An unlinked record will be create if you click yes) ", "Warning",MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
MemberPackage.CreateUnlinkedMemberPackage(strMembershipID, ref nPackageID);
}
else
{
return;
}
}
}
// if (!VerifyMemberPackageAllowCertainClass(nPackageID, classInstance.StrClassCode.Value))
// throw new Exception("The package you wish to use is not allow to reserve the class. Please use other package.");
//Prompt Package going to expiry
//Prompt Remaining class
classAttendance.NClassInstanceID = nClassInstanceID;
classAttendance.StrMembershipID = strMembershipID;
classAttendance.NEmployeeID = User.EmployeeID;
classAttendance.NReservedByID = User.EmployeeID;
classAttendance.FUOBBooking = System.Data.SqlTypes.SqlBoolean.False;
classAttendance.DtDate = classInstance.DtDate.Value;
classAttendance.DtReservationDate = DateTime.Today.Date;
classAttendance.DtStartTime = Convert.ToDateTime(classInstance.DtDate.Value.ToString("yyyy-MM-dd")+" "+ classInstance.DtStartTime.Value.ToString("H:mm:ss"));
classAttendance.DtEndTime = Convert.ToDateTime(classInstance.DtDate.Value.ToString("yyyy-MM-dd") + " " + classInstance.DtEndTime.Value.ToString("H:mm:ss"));
classAttendance.StrBranchCode = User.BranchCode;
classAttendance.DtLastEditDate = DateTime.Now;
classAttendance.NStatusID = 0;
classAttendance.NPackageID = nPackageID;
classAttendance.Insert();
TblBranch sqlBranch = new TblBranch();
TblClass sqlClass = new TblClass();
sqlBranch.StrBranchCode = classAttendance.StrBranchCode.ToString();
sqlBranch.SelectOne();
sqlClass.StrClassCode = classInstance.StrClassCode;
sqlClass.SelectOne();
if (isMobileAppUser(strMembershipID))
PushNotification(strMembershipID, "Your class booking for " + sqlClass.StrDescription.ToString() + " at " + sqlBranch.StrBranchName.ToString() + " at " + classAttendance.DtStartTime.Value.ToString("h.mmtt") + " on " + classAttendance.DtStartTime.Value.ToString("dd MMM") + " has been confirmed.");
}
private int UpgradePackage(ACMSDAL.ConnectionProvider connProvider, ACMSLogic.POS pos)
{
int OriginalCategoryID = 10 ;
if (pos.NCategoryID == 10 && pos.WantToUpgradeMemberPackageTable.Rows.Count == 1)
{
int wantToUpgrade_nPackageID = ACMS.Convert.ToInt32(pos.WantToUpgradeMemberPackageRow["nPackageID"]);
string oldStrPackageCode = pos.WantToUpgradeMemberPackageRow["strPackageCode"].ToString();
foreach (DataRow r in pos.ReceiptItemsTable.Rows)
{
pos.CheckPackageWhenSave(r["strCode"].ToString(), wantToUpgrade_nPackageID);
}
// from here, we can get a list of strClassCode that already been used by member
//DataTable attendedClassCodetable = GetAttendedClassCode(ACMS.Convert.ToInt32(myWantToUpgradeMemberPackageTable.Rows[0]["nPackageID"]));
// from here, we can get a list of strClassCode that already been used by member
//DataTable attendedServiceCodetable = GetAttendedServiceCode(ACMS.Convert.ToInt32(myWantToUpgradeMemberPackageTable.Rows[0]["nPackageID"]));
TblMemberPackage memberPackage = new TblMemberPackage();
TblClassAttendance classAttendance = new TblClassAttendance();
TblServiceSession serviceSession = new TblServiceSession();
TblPackage freeSpaPackage = new TblPackage();
memberPackage.MainConnectionProvider = connProvider;
classAttendance.MainConnectionProvider = connProvider;
serviceSession.MainConnectionProvider = connProvider;
freeSpaPackage.MainConnectionProvider = connProvider;
DataTable memberPackagetable = memberPackage.FillSchema("Select * from tblMemberPackage");
//get old free spa package Code
freeSpaPackage.StrPackageCode = oldStrPackageCode;
DataTable OldFreePackageCode = freeSpaPackage.SelectSpaPackageFreebie();
string strOldFreePackage = OldFreePackageCode.Rows[0]["strFreePkgCode"].ToString();
foreach (DataRow r in pos.ReceiptItemsTable.Rows)
{
int nQuantity = ACMS.Convert.ToInt32(r["nQuantity"]);
for (int i = 0; i < nQuantity; i++)
{
DataRow rowToAdd = memberPackagetable.NewRow();
MemberPackage.InitMemberPackageRowInPOS(rowToAdd, pos.StrReceiptNo, pos.StrMembershipID, r["strCode"].ToString(), "", false);
memberPackagetable.Rows.Add(rowToAdd);
}
}
//insert a new upgrade package to member package
if (memberPackagetable.Rows.Count > 0)
memberPackage.SaveData(memberPackagetable);
DataTable newAddMemberPackageTable = memberPackage.SelectAllWstrReceiptNo(pos.StrReceiptNo);
DataRow[] rowNewAddMemberPackage = newAddMemberPackageTable.Select("fFree = 0");
DataTable attendedClassTable = classAttendance.GetAllClassAttendancesBasePackageID(wantToUpgrade_nPackageID, pos.StrMembershipID, pos.StrBranchCode);
DataTable serviceUsedTable = serviceSession.GetMemberServiceSessionBasePackageID(wantToUpgrade_nPackageID, pos.StrMembershipID, pos.StrBranchCode);
//insert a new freebie package based on new added upgrade pacakge
string newStrPackageCode = "";
for( int i = 0; i<newAddMemberPackageTable.Rows.Count; i++)
{
if (newAddMemberPackageTable.Rows[i]["fFree"].ToString() == "False")
{
if (newAddMemberPackageTable.Rows.Count > 1)
newStrPackageCode = newAddMemberPackageTable.Rows[newAddMemberPackageTable.Rows.Count - 1]["strPackageCode"].ToString();
else
newStrPackageCode = newAddMemberPackageTable.Rows[0]["strPackageCode"].ToString();
}
}
//get new free spa package Code
freeSpaPackage.StrPackageCode = newStrPackageCode;
DataTable NewFreePackageCode = freeSpaPackage.SelectSpaPackageFreebie();
string strNewFreePackage = NewFreePackageCode.Rows[0]["strFreePkgCode"].ToString();
if (strNewFreePackage != "")
{
DataTable FreememberPackagetable = memberPackage.FillSchema("Select * from tblMemberPackage");
DataRow rowToAdd2 = FreememberPackagetable.NewRow();
MemberPackage.InitMemberPackageRowInPOS(rowToAdd2, pos.StrReceiptNo, pos.StrMembershipID, strNewFreePackage, "", true);
FreememberPackagetable.Rows.Add(rowToAdd2);
if (FreememberPackagetable.Rows.Count > 0)
memberPackage.SaveData(FreememberPackagetable);
//move session from old freebie to new freebie
//find old free package ID,via wantToUpgrade_nPackageID
freeSpaPackage.NPackageID = wantToUpgrade_nPackageID;
freeSpaPackage.StrPackageCode = oldStrPackageCode;
DataTable OldFreePackageID = freeSpaPackage.SelectSpaPackageFreebieID();
int nOldFreePackageID;
if (OldFreePackageID.Rows.Count > 0)
nOldFreePackageID = ACMS.Convert.ToInt32(OldFreePackageID.Rows[OldFreePackageID.Rows.Count - 1]["nPackageID"]);
else
nOldFreePackageID = 0;
DataTable newFreeMemberPackageTable = memberPackage.SelectAllWstrReceiptNo(pos.StrReceiptNo);
DataRow[] rowNewFreeMemberPackage = newFreeMemberPackageTable.Select("fFree = 1");
DataTable freeserviceUsedTable = serviceSession.GetMemberServiceSessionBasePackageID(nOldFreePackageID, pos.StrMembershipID, pos.StrBranchCode);
//foreach (DataRow FmemberPackageRow in rowNewFreeMemberPackage)
//{
string strFreePackageCode ="";
if (rowNewFreeMemberPackage.Length > 0)
strFreePackageCode = rowNewFreeMemberPackage[rowNewFreeMemberPackage.Length - 1]["strPackageCode"].ToString();
DataRow[] tempRowList3 = freeserviceUsedTable.Select("nPackageID = '"+nOldFreePackageID+"'", "dtDate");
if (tempRowList3.Length > 0)
{
foreach (DataRow freeserviceUsedRow in tempRowList3)
{
freeserviceUsedRow["nPackageID"] = rowNewFreeMemberPackage[rowNewFreeMemberPackage.Length - 1]["nPackageID"];
}
}
if (tempRowList3.Length > 0)
{
//update the expiryDate
TblPackage package = new TblPackage();
package.MainConnectionProvider = connProvider;
package.StrPackageCode = strFreePackageCode;
DataTable tablePackage = package.SelectOne();
if (tablePackage == null && tablePackage.Rows.Count == 0)
throw new Exception("Package no found.");
if (tempRowList3.Length > 0)
{
rowNewFreeMemberPackage[rowNewFreeMemberPackage.Length - 1]["DtStartDate"] = ACMS.Convert.ToDateTime(tempRowList3[0]["dtDate"]);
rowNewFreeMemberPackage[rowNewFreeMemberPackage.Length - 1]["DtExpiryDate"] = ACMS.Convert.ToDateTime(
rowNewFreeMemberPackage[rowNewFreeMemberPackage.Length - 1]["DtStartDate"]).AddMonths(ACMS.Convert.ToInt32(package.NPackageDuration.Value)).AddDays(-1);
}
}
//}
DataTable oldFreeMemberPackageTable = memberPackage.SelectPackageID(nOldFreePackageID);
oldFreeMemberPackageTable.Rows[0]["nStatusID"] = 2;
serviceSession.UpgradePackage(ACMS.Convert.ToInt32(rowNewFreeMemberPackage[rowNewFreeMemberPackage.Length - 1]["nPackageID"]), nOldFreePackageID, pos.StrMembershipID);
memberPackage.SaveData(oldFreeMemberPackageTable);
memberPackage.SaveData(newFreeMemberPackageTable);
}
//move session from old package to new package
foreach (DataRow memberPackageRow in rowNewAddMemberPackage)
{
string strPackageCode = memberPackageRow["strPackageCode"].ToString();
DataRow[] tempRowList = attendedClassTable.Select("nPackageID = '" + wantToUpgrade_nPackageID + "'", "dtDate");
if (tempRowList.Length > 0)
{
foreach (DataRow attendedClassRow in tempRowList)
{ // new packageID
attendedClassRow["nPackageID"] = memberPackageRow["nPackageID"];
}
}
DataRow[] tempRowList2 = serviceUsedTable.Select("nPackageID = '" + wantToUpgrade_nPackageID + "'", "dtDate");
if (tempRowList2.Length > 0)
{
foreach (DataRow serviceUsedRow in tempRowList2)
{
serviceUsedRow["nPackageID"] = memberPackageRow["nPackageID"];
}
}
if (tempRowList.Length > 0 ||
tempRowList2.Length > 0)
{
//update the expiryDate
TblPackage package = new TblPackage();
package.MainConnectionProvider = connProvider;
package.StrPackageCode = strPackageCode;
DataTable tablePackage = package.SelectOne();
TblPackageExtension PackExtension = new TblPackageExtension();
PackExtension.MainConnectionProvider = connProvider;
PackExtension.NPackageID = ACMS.Convert.ToInt32(rowNewAddMemberPackage[0]["nPackageID"]);
PackExtension.NPackageIDOld = wantToUpgrade_nPackageID;
int days = 0;
DataTable tblPkgExt = PackExtension.SelectExtensionDays();
if (PackExtension.NDaysExtended.IsNull == false)
days = System.Convert.ToInt32(PackExtension.NDaysExtended.Value);
if (tablePackage == null && tablePackage.Rows.Count == 0)
throw new Exception("Package no found.");
if (tempRowList.Length > 0 && tempRowList2.Length == 0)
{
///07/11/2012 Jackie
DataTable myDataTable1;
DateTime dtDate;
ACMSDAL.TblClassAttendance ClassAttendance = new ACMSDAL.TblClassAttendance();
myDataTable1 = ClassAttendance.LoadData("Select top 1 dtDate From tblClassAttendance where nstatusid=1 and nPackageID= " + wantToUpgrade_nPackageID + " and " +
" strMemberShipID = @strMembershipID order by dtDate",
new string[] { "@strMembershipID" },
new object[] { pos.StrMembershipID });
dtDate = DateTime.Now;
if (myDataTable1.Rows.Count > 0)
dtDate = System.Convert.ToDateTime(myDataTable1.Rows[0][0]);
memberPackageRow["DtStartDate"] = dtDate;
// Stop
memberPackageRow["DtExpiryDate"] = ACMS.Convert.ToDateTime(
memberPackageRow["DtStartDate"]).AddMonths(ACMS.Convert.ToInt32(package.NPackageDuration.Value)).AddDays(days - 1);
}
else if (tempRowList2.Length > 0 &&
tempRowList.Length == 0)
{
memberPackageRow["DtStartDate"] = ACMS.Convert.ToDateTime(tempRowList2[0]["dtDate"]);
memberPackageRow["DtExpiryDate"] = ACMS.Convert.ToDateTime(
memberPackageRow["DtStartDate"]).AddMonths(ACMS.Convert.ToInt32(package.NValidMonths.Value)).AddDays(days - 1);
}
}
}
OriginalCategoryID= ACMS.Convert.ToDBInt32(pos.WantToUpgradeMemberPackageRow["nCategoryID"]) ;
pos.WantToUpgradeMemberPackageRow["nStatusID"] = 2;
serviceSession.UpgradePackage(ACMS.Convert.ToInt32(rowNewAddMemberPackage[0]["nPackageID"]), wantToUpgrade_nPackageID, pos.StrMembershipID);
memberPackage.SaveData(pos.WantToUpgradeMemberPackageTable);
memberPackage.SaveData(newAddMemberPackageTable);
classAttendance.SaveData(wantToUpgrade_nPackageID,pos.StrMembershipID);
}
return OriginalCategoryID;
}
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;
}