public static bool IsMemberExceedUOBBookingThisMonth(string strMembershipID, DateTime uobBookingDate)
{
TblCompany comp = new TblCompany();
DataTable table = comp.SelectAll();
DataRow compMasterRow = table.Rows[0];
int nUOBMonthlyBooking = ACMS.Convert.ToInt32(compMasterRow["nUOBMonthlyBooking"]);
TblServiceSession serviceSession = new TblServiceSession();
DataTable uobBookingTable = serviceSession.GetUOBBookingServiceSessionBaseMembershipID(strMembershipID);
int uobBookingMade = 0;
if (uobBookingTable != null && uobBookingTable.Rows.Count > 0)
{
foreach (DataRow r in uobBookingTable.Rows)
{
DateTime serviceSessionDate = ACMS.Convert.ToDateTime(r["dtDate"]);
if (serviceSessionDate != DateTime.MinValue)
{
if (serviceSessionDate.Month == uobBookingDate.Month
&& serviceSessionDate.Year == uobBookingDate.Year)
uobBookingMade++;
}
}
}
return uobBookingMade >= nUOBMonthlyBooking;
}
private void ReplaceMembershipCardTransaction(ACMSDAL.ConnectionProvider connProvider, ACMSLogic.POS pos)
{
if (pos.NCategoryID == 20)
{
TblMember sqlMember = new TblMember();
sqlMember.MainConnectionProvider = connProvider;
sqlMember.StrMembershipID = pos.StrMembershipID;
sqlMember.SelectOne();
sqlMember.NCardStatusID = 0;
sqlMember.StrCardBranchCode = "WL";
sqlMember.Update();
TblMember sqlIntroduceMember = new TblMember();
sqlIntroduceMember.MainConnectionProvider = connProvider;
sqlIntroduceMember.StrIntroducerMembershipID = pos.StrMembershipID;
DataTable introduceMemberTable = sqlIntroduceMember.SelectAllWstrIntroducerMembershipIDLogic();
if (introduceMemberTable != null && introduceMemberTable.Rows.Count > 0)
{
foreach (DataRow r in introduceMemberTable.Rows)
r["StrIntroducerMembershipID"] = DBNull.Value;
sqlIntroduceMember.SaveData(introduceMemberTable);
}
string membershipID = pos.StrMembershipID;
string newStrMembershipID = "";
bool isMember = !membershipID.StartsWith("NMC");
if (!isMember)
{
TblCardRequest sqlCardRequest = new TblCardRequest();
TblCompany sqlCompany = new TblCompany();
sqlCompany.MainConnectionProvider = connProvider;
sqlCardRequest.MainConnectionProvider = connProvider;
sqlCompany.IncOne();
sqlMember.StrMembershipID = pos.StrMembershipID;
DataTable memberTable = sqlMember.SelectOne();
newStrMembershipID = "NMC" + sqlCompany.NNonMembershipNo.Value.ToString();
string origStrMembershipID = sqlMember.StrMembershipID.Value;
pos.StrNewMembershipID = newStrMembershipID;
memberTable.Rows[0]["nMembershipNo"] = sqlCompany.NNonMembershipNo.Value;
memberTable.Rows[0]["strMembershipID"] = newStrMembershipID;
memberTable.Rows[0]["nCardStatusID"] = (int)CardStatusType.RequestPrint;
string cmdText = "Update tblMember SET strMembershipID = @NewStrMembershipID, " +
" nMembershipNo = @nMembershipNo Where strMembershipID = @OriginalStrMembershipID";
sqlMember.UpdateKey(memberTable, cmdText, "@NewStrMembershipID", newStrMembershipID,
"@OriginalStrMembershipID", origStrMembershipID, new string[] {"@nMembershipNo"},
new object[] {sqlCompany.NNonMembershipNo.Value});
#region ====== Added By Albert ======
//To update member packages after changing MembershipID.
TblMemberPackage mMemberPackage;
mMemberPackage = new TblMemberPackage();
mMemberPackage.StrMembershipIDOld = origStrMembershipID;
mMemberPackage.StrMembershipID = newStrMembershipID;
mMemberPackage.UpdateAllWstrMembershipIDLogic();
TblMemberCreditPackage mMemberCreditPackage;
mMemberCreditPackage = new TblMemberCreditPackage();
mMemberCreditPackage.StrMembershipIDOld = origStrMembershipID;
mMemberCreditPackage.StrMembershipID = newStrMembershipID;
mMemberCreditPackage.UpdateAllWstrMembershipIDLogic();
#endregion
//Request print for new non member
sqlCardRequest.NEmployeeID = pos.NCashierID;
sqlCardRequest.NStatusID = (int)CardStatusType.RequestPrint;
sqlCardRequest.StrBranchCode = pos.StrBranchCode;
sqlCardRequest.StrMembershipID = "NMC" +sqlCompany.NNonMembershipNo;
sqlCardRequest.DtLastEditDate = DateTime.Now;
sqlCardRequest.Insert();
}
else
{
TblCardRequest sqlCardRequest = new TblCardRequest();
TblBranch sqlBranch = new TblBranch();
sqlBranch.MainConnectionProvider = connProvider;
sqlCardRequest.MainConnectionProvider = connProvider;
sqlBranch.StrBranchCode = pos.StrBranchCode;
sqlBranch.IncOne();
sqlMember.StrMembershipID = pos.StrMembershipID;
DataTable memberTable = sqlMember.SelectOne();
newStrMembershipID = pos.StrBranchCode.TrimEnd() + ACMS.Convert.ToInt32(sqlBranch.NMembershipNo);
string origStrMembershipID = sqlMember.StrMembershipID.Value;
pos.StrNewMembershipID = newStrMembershipID;
memberTable.Rows[0]["nMembershipNo"] = ACMS.Convert.ToInt32(sqlBranch.NMembershipNo);
memberTable.Rows[0]["strMembershipID"] = newStrMembershipID;
memberTable.Rows[0]["nCardStatusID"] = (int)CardStatusType.RequestPrint;
string cmdText = "Update tblMember SET strMembershipID = @NewStrMembershipID, " +
" nMembershipNo = @nMembershipNo Where strMembershipID = @OriginalStrMembershipID";
sqlMember.UpdateKey(memberTable, cmdText, "@NewStrMembershipID", newStrMembershipID,
"@OriginalStrMembershipID", origStrMembershipID, new string[] {"@nMembershipNo"},
new object[] {ACMS.Convert.ToInt32(sqlBranch.NMembershipNo)});
#region ====== Added By Albert ======
//To update member packages after changing MembershipID.
TblMemberPackage mMemberPackage;
mMemberPackage = new TblMemberPackage();
mMemberPackage.StrMembershipIDOld = origStrMembershipID;
mMemberPackage.StrMembershipID = newStrMembershipID;
mMemberPackage.UpdateAllWstrMembershipIDLogic();
TblMemberCreditPackage mMemberCreditPackage;
mMemberCreditPackage = new TblMemberCreditPackage();
mMemberCreditPackage.StrMembershipIDOld = origStrMembershipID;
mMemberCreditPackage.StrMembershipID = newStrMembershipID;
mMemberCreditPackage.UpdateAllWstrMembershipIDLogic();
#endregion
//Request print for new non member
sqlCardRequest.NEmployeeID = pos.NCashierID;
sqlCardRequest.NStatusID = (int)CardStatusType.RequestPrint;
sqlCardRequest.StrBranchCode = pos.StrBranchCode;
sqlCardRequest.StrMembershipID = newStrMembershipID;
sqlCardRequest.DtLastEditDate = DateTime.Now;
sqlCardRequest.Insert();
}
if (introduceMemberTable != null && introduceMemberTable.Rows.Count > 0)
{
foreach (DataRow r in introduceMemberTable.Rows)
r["StrIntroducerMembershipID"] = newStrMembershipID;
sqlIntroduceMember.SaveData(introduceMemberTable);
}
TblAudit sqlAudit = new TblAudit();
sqlAudit.MainConnectionProvider = connProvider;
//Audit trail for request print
sqlAudit.NAuditTypeID = ACMSLogic.AuditTypeID.MemberCard;
sqlAudit.NEmployeeID = pos.NCashierID;
sqlAudit.StrAuditEntry = "Replace member card with new ID";
sqlAudit.StrReference = newStrMembershipID;
sqlAudit.DtDate = DateTime.Now;
sqlAudit.Insert();
}
}
//DEREK Instalment Plan Need to Update Receipt Here
public void PrintReceipt(string receiptNo)
{
DataColumn colItemDiscountAmt = new DataColumn("DiscountAmt", typeof(decimal));
DataColumn colBillDiscountAmt = new DataColumn("DiscountAmt", typeof(decimal));
DataColumn colMemberName = new DataColumn("strMemberName", typeof(string));
DataColumn colRewardPoint = new DataColumn("RewardPoint", typeof(decimal));
DataColumn colGSTPaid = new DataColumn("GSTPaid", typeof(decimal));
//DataColumn colOSAmt = new DataColumn("mOutstandingAmount", typeof(decimal));
TblReceipt receipt = new TblReceipt();
receipt.StrReceiptNo = receiptNo;
DataTable tableReceipt = receipt.SelectOne();
tableReceipt.TableName = "TblReceipt";
tableReceipt.Columns.Add(colBillDiscountAmt);
tableReceipt.Columns.Add(colMemberName);
tableReceipt.Columns.Add(colRewardPoint);
tableReceipt.Columns.Add(colGSTPaid);
// tableReceipt.Columns.Add(colOSAmt);
TblMember sqlMember = new TblMember();
sqlMember.StrMembershipID = receipt.StrMembershipID;
sqlMember.SelectOne();
tableReceipt.Rows[0]["strMemberName"] = sqlMember.StrMemberName.IsNull ? "" : sqlMember.StrMemberName.Value.ToUpper();
TblBranch sqlBranch = new TblBranch();
sqlBranch.StrBranchCode = receipt.StrBranchCode == SqlString.Null ? "" : receipt.StrBranchCode.Value;
DataTable branchTable = sqlBranch.SelectOne();
TblRewardsTransaction sqlRewTrans = new TblRewardsTransaction();
decimal rewardpoint = sqlRewTrans.GetMemberLoyaltyPoint(receipt.StrMembershipID.Value);
tableReceipt.Rows[0]["RewardPoint"] = System.Math.Round(rewardpoint);
TblReceiptEntries receiptEntries = new TblReceiptEntries();
receiptEntries.StrReceiptNo = receiptNo;
DataTable tableReceiptEntries = receiptEntries.SelectAllWstrReceiptNoLogic();
tableReceiptEntries.TableName = "TblReceiptEntries";
tableReceiptEntries.Columns.Add(colItemDiscountAmt);
TblReceiptFreebie freebie = new TblReceiptFreebie();
freebie.StrReceiptNo = receiptNo;
DataTable tableReceiptFreebie = freebie.SelectReceiptFreebie();
tableReceiptFreebie.TableName = "TblReceiptFreebie";
TblMemberPackage FreePackage = new TblMemberPackage();
FreePackage.StrReceiptNo = receiptNo;
DataTable tableFreePackage = FreePackage.GetFreePackage(receiptNo);
tableFreePackage.TableName = "TblFreePackage";
TblMemberPackage ConvertedPackage = new TblMemberPackage();
ConvertedPackage.StrReceiptNo = receiptNo;
DataTable tableConvertedPackage = ConvertedPackage.GetConvertedPackage(receiptNo);
tableConvertedPackage.TableName = "TblConvertedPackage";
TblPaymentPlan myPaymentPlan = new TblPaymentPlan();
DataTable tablePaymentPlan = myPaymentPlan.GetInhouseIPPOSByReceiptNo(receiptNo);
tablePaymentPlan.TableName = "TblPaymentPlan";
DataTable tableDeposit = myPaymentPlan.GetInhouseIPPReceiptDeposit(receiptNo);
tableDeposit.TableName = "TblReceiptDeposit";
// TblProduct Product = new TblProduct();
// // Product.StrProductCode = tableReceiptFreebie.Rows[0]["strItemCode"].ToString();
// DataTable tableProduct = Product.SelectAll();
// tableProduct.TableName = "TblProductName";
decimal nettAmountB4Discount = 0;
decimal discoutnAmt =0;
decimal depositAmt = 0;
if (tableDeposit.Rows.Count > 0)
{
depositAmt = Convert.ToDecimal(tableDeposit.Rows[0]["TotalDeposit"]);
}
foreach (DataRow r in tableReceiptEntries.Rows)
{
int qty = ACMS.Convert.ToInt32(r["nQuantity"]);
decimal unitPrice = ACMS.Convert.ToDecimal(r["mUnitPrice"]);
decimal currSubtotal = ACMS.Convert.ToDecimal(r["mSubTotal"]);
discoutnAmt = (unitPrice * qty) - currSubtotal;
r["DiscountAmt"] = decimal.Round(discoutnAmt, 2);
nettAmountB4Discount += currSubtotal;
}
//Sub Total
DataColumn colSubTotal = new DataColumn("mSubTotal", typeof(decimal));
tableReceipt.Columns.Add(colSubTotal);
tableReceipt.Rows[0]["mSubTotal"] = nettAmountB4Discount;
decimal nettAmountAfterDiscount = ACMS.Convert.ToDecimal(tableReceipt.Rows[0]["mNettAmount"]);
if (tableReceipt.Rows[0]["strDiscountCode"].ToString().Length > 0 &&
tableReceipt.Rows[0]["strDiscountCode"] != DBNull.Value)
{
tableReceipt.Rows[0]["DiscountAmt"] = nettAmountB4Discount - (nettAmountAfterDiscount + depositAmt);
}
TblReceiptPayment receiptPayment = new TblReceiptPayment();
receiptPayment.StrReceiptNo = receiptNo;
DataTable tableReceiptPayment = receiptPayment.SelectAllWstrReceiptNoLogic();
tableReceiptPayment.TableName = "TblReceiptPayment";
TblCompany comp = new TblCompany();
DataTable compTable = comp.SelectAll();
int myTaxID = ACMS.Convert.ToInt32(compTable.Rows[0]["NTaxID"]);
TblTax tax = new TblTax();
tax.NTaxID = myTaxID;
tax.SelectOne();
decimal myGSTRate = tax.DTaxRate.IsNull ? 0 : (decimal)tax.DTaxRate.Value;
decimal GSTAmtPaid=0;
decimal GSTRate1=myGSTRate;//0.05M;
decimal GSTRate2=myGSTRate+1;// 1.05M;
foreach (DataRow p in tableReceiptPayment.Rows)
{
GSTAmtPaid=GSTAmtPaid+ACMS.Convert.ToDecimal(p[3])* GSTRate1 /GSTRate2;
}
if (receipt.NCategoryID.ToString() != "38")
{
tableReceipt.Rows[0]["GSTPaid"] = decimal.Round(GSTAmtPaid, 2);
}
else
{
tableReceipt.Rows[0]["GSTPaid"] = decimal.Round(0, 2);
}
DataSet newDS = new DataSet("Receipt");
newDS.Tables.Add(tableReceipt);
newDS.Tables.Add(tableReceiptEntries);
newDS.Tables.Add(tableReceiptPayment);
newDS.Tables.Add(branchTable);
// newDS.Tables.Add(tableProduct);
newDS.Tables.Add(tableReceiptFreebie);
newDS.Tables.Add(tableFreePackage);
newDS.Tables.Add(tableConvertedPackage);
newDS.Tables.Add(tablePaymentPlan);
newDS.Tables.Add(tableDeposit);
DataRelation relation1 = new DataRelation("Receipt_ReceiptEntries_Relation", newDS.Tables["TblReceipt"].Columns["strReceiptNo"],
newDS.Tables["TblReceiptEntries"].Columns["strReceiptNo"], false);
DataRelation relation2 = new DataRelation("Receipt_ReceiptPayment_Relation", newDS.Tables["TblReceipt"].Columns["strReceiptNo"],
newDS.Tables["TblReceiptPayment"].Columns["strReceiptNo"], false);
DataRelation relation3 = new DataRelation("Receipt_ReceiptFreebie_Relation", newDS.Tables["TblReceipt"].Columns["strReceiptNo"],
newDS.Tables["TblReceiptFreebie"].Columns["strReceiptNo"], false);
DataRelation relation4 = new DataRelation("Receipt_FreePackage_Relation", newDS.Tables["TblReceipt"].Columns["strReceiptNo"],
newDS.Tables["TblFreePackage"].Columns["strReceiptNo"], false);
DataRelation relation5 = new DataRelation("Receipt_ConvertedPackage_Relation", newDS.Tables["TblReceipt"].Columns["strReceiptNo"],
newDS.Tables["TblConvertedPackage"].Columns["strReceiptNo"], false);
DataRelation relation6 = new DataRelation("Receipt_PaymentPlan_Relation", newDS.Tables["TblReceipt"].Columns["strReceiptNo"],
newDS.Tables["TblPaymentPlan"].Columns["strReceiptNo"], false);
DataRelation relation7 = new DataRelation("Receipt_Deposit_Relation", newDS.Tables["TblReceipt"].Columns["strReceiptNo"],
newDS.Tables["TblReceiptDeposit"].Columns["strReceiptNo"], false);
newDS.Relations.Add(relation1);
newDS.Relations.Add(relation2);
newDS.Relations.Add(relation3);
newDS.Relations.Add(relation4);
newDS.Relations.Add(relation5);
newDS.Relations.Add(relation6);
newDS.Relations.Add(relation7);
//display GST %
DataColumn colGstTax = new DataColumn("GstTax", typeof(int));
tableReceipt.Columns.Add(colGstTax);
tableReceipt.Rows[0]["GstTax"] = myGSTRate * 100;
ACMSLogic.Report.Receipt receiptReport = new ACMSLogic.Report.Receipt();
receiptReport.DataSource = newDS;
// if (ACMS.Convert.ToDecimal(tableReceipt.Rows[0]["DiscountAmt"])==0)
// {
// receiptReport.HideFields(false);
// }
// else
// {
// receiptReport.HideFields(true);
// }
// receiptReport.ShowDesignerDialog();
receiptReport.Print();
}
//Derek Instalment Plan Need to Add tblPaymentPlan
private void Init()
{
TblCompany comp = new TblCompany();
DataTable compTable = comp.SelectAll();
myTaxID = ACMS.Convert.ToInt32(compTable.Rows[0]["NTaxID"]);
TblTax tax = new TblTax();
tax.NTaxID = myTaxID;
tax.SelectOne();
myGSTRate = tax.DTaxRate.IsNull ? 0 : (decimal)tax.DTaxRate.Value;
myReceiptMasterTable = comp.FillSchema("Select * From tblReceipt");
myReceiptItemsTable = comp.FillSchema("Select * From tblReceiptEntries");
myReceiptFreebieTable = comp.FillSchema("Select * From tblReceiptFreebie");
myReceiptPaymentTable = comp.FillSchema("Select * From tblReceiptPayment");
InitTblReceipt();
InitTblReceiptEntries();
InitTblReceiptPackage();
InitTblReceiptItemsFreebie();
InitTblReceiptItemsPackage();
InitTblGiro();
InitWantToUpgradeMemberPackageTableNew();
//InitWantToUpgradeMemberPackageTable();
//Derek Instalment Plan No Need Fire ColumnChanged Event for tblPaymentPlan
myReceiptMasterTable.ColumnChanged +=new DataColumnChangeEventHandler(myReceiptMasterTable_ColumnChanged);
myReceiptItemsTable.ColumnChanged +=new DataColumnChangeEventHandler(myReceiptItemsTable_ColumnChanged);
myReceiptPaymentTable.ColumnChanged +=new DataColumnChangeEventHandler(myReceiptPaymentTable_ColumnChanged);
InitPaymentTypeTable();
}
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 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 OnReplaceMembershipCard()
{
if (myPOS.ReceiptItemsTable.Rows.Count > 0)
{
DialogResult result = MessageBox.Show(this,
"Only one item is allow. Do you want to overwrite the existing one?", "Warning", MessageBoxButtons.YesNo);
if (result == DialogResult.No)
return;
else
myPOS.ReceiptItemsTable.Rows.Clear();
}
string membershipID = myPOS.StrMembershipID;
bool isMember = !membershipID.StartsWith("NMC");
TblCompany sqlCompany = new TblCompany();
DataTable compTable = sqlCompany.SelectAll();
decimal replaceCardRate = ACMS.Convert.ToDecimal(compTable.Rows[0]["mReplaceCardRate"]);
if (!isMember)
{
int nextNonMemberNo = ACMS.Convert.ToInt32(compTable.Rows[0]["nNonMembershipNo"]) + 1;
string newStrMembershipID = "NMC" + nextNonMemberNo.ToString();
string origStrMembershipID = myPOS.StrMembershipID;
myPOS.NewReceiptEntry(newStrMembershipID, -1,
string.Format("Replace New Member Card. Possible ID : {0}", newStrMembershipID),
1, replaceCardRate, origStrMembershipID);
}
else
{
TblBranch sqlbranch = new TblBranch();
sqlbranch.StrBranchCode = myPOS.StrBranchCode;
sqlbranch.SelectOne();
int nextNonMemberNo = sqlbranch.NMembershipNo.Value + 1;
string newStrMembershipID = myPOS.StrBranchCode.TrimEnd() + nextNonMemberNo.ToString();
string origStrMembershipID = myPOS.StrMembershipID;
myPOS.NewReceiptEntry(newStrMembershipID, -1,
string.Format("Replace New Member Card. Possible ID : {0}", newStrMembershipID),
1, replaceCardRate, origStrMembershipID);
StrNewMembershipID = newStrMembershipID;
}
}
public bool IsPeakHour(DateTime dtDate, DateTime dtStartTime, DateTime dtEndTime,
bool isWeekDay, string serviceCode, ref int totalServiceSession)
{
TblCompany comp = new TblCompany();
DataTable table = comp.SelectAll();
DataRow masterRow = table.Rows[0];
TimeSpan dtStartTimeSpan = dtStartTime.TimeOfDay;
TimeSpan dtEndTimeSpan = dtEndTime.TimeOfDay;
if (isWeekDay)
{
if (masterRow["DtUOBWeekdayPeakStart"] == DBNull.Value ||
masterRow["DtUOBWeekdayPeakEnd"] == DBNull.Value)
return false;
DateTime weekDayPeakStart = ACMS.Convert.ToDateTime(masterRow["DtUOBWeekdayPeakStart"]);
DateTime weekDayPeakEnd = ACMS.Convert.ToDateTime(masterRow["DtUOBWeekdayPeakEnd"]);
DateTime startTime = new DateTime(dtDate.Year, dtDate.Month, dtDate.Day,
weekDayPeakStart.Hour, weekDayPeakStart.Minute, weekDayPeakStart.Second);
DateTime endTime = new DateTime(dtDate.Year, dtDate.Month, dtDate.Day,
weekDayPeakEnd.Hour, weekDayPeakEnd.Minute, weekDayPeakEnd.Second);
totalServiceSession = 0;
DataTable serviceSessionTable = GetServiceSessionBaseTime(User.BranchCode, serviceCode, dtDate, startTime, endTime);
if (serviceSessionTable != null && serviceSessionTable.Rows.Count > 0)
totalServiceSession = serviceSessionTable.Rows.Count;
TimeSpan weekDayPeakStartTimeSpan = weekDayPeakStart.TimeOfDay;
TimeSpan weekDayPeakEndTimeSpan = weekDayPeakEnd.TimeOfDay;
if (weekDayPeakStartTimeSpan >= dtStartTimeSpan &&
weekDayPeakEndTimeSpan <= dtStartTimeSpan)
return true;
else if (weekDayPeakStartTimeSpan >= dtEndTimeSpan &&
weekDayPeakEndTimeSpan <= dtEndTimeSpan)
return true;
else
return false;
}
else
{
DateTime weekendPeakStart = ACMS.Convert.ToDateTime(masterRow["DtUOBWeekendPeakStart"]);
DateTime weekendPeakEnd = ACMS.Convert.ToDateTime(masterRow["DtUOBWeekendPeakEnd"]);
DateTime startTime = new DateTime(dtDate.Year, dtDate.Month, dtDate.Day,
weekendPeakStart.Hour, weekendPeakStart.Minute, weekendPeakStart.Second);
DateTime endTime = new DateTime(dtDate.Year, dtDate.Month, dtDate.Day,
weekendPeakEnd.Hour, weekendPeakEnd.Minute, weekendPeakEnd.Second);
totalServiceSession = 0;
DataTable serviceSessionTable = GetServiceSessionBaseTime(User.BranchCode, serviceCode, dtDate, startTime, endTime);
if (serviceSessionTable != null && serviceSessionTable.Rows.Count > 0)
totalServiceSession = serviceSessionTable.Rows.Count;
TimeSpan weekendPeakStartTimeSpan = ACMS.Convert.ToDateTime(masterRow["DtUOBWeekendPeakStart"]).TimeOfDay;
TimeSpan weekendPeakEndTimeSpan = ACMS.Convert.ToDateTime(masterRow["DtUOBWeekendPeakEnd"]).TimeOfDay;
if (weekendPeakStartTimeSpan >= dtStartTimeSpan &&
weekendPeakEndTimeSpan <= dtStartTimeSpan)
return true;
else if (weekendPeakStartTimeSpan >= dtEndTimeSpan &&
weekendPeakEndTimeSpan <= dtEndTimeSpan)
return true;
else
return false;
}
}
public static bool BlockMembershipID(int numberOfNonMemberID, int numberOfMemberID, string strBranchCode)
{
ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();
TblCompany sqlCompany = new TblCompany();
TblBranch sqlBranch = new TblBranch();
try
{
sqlCompany.MainConnectionProvider = connProvider;
sqlBranch.MainConnectionProvider = connProvider;
connProvider.OpenConnection();
connProvider.BeginTransaction("BlockMembershipID");
if (numberOfNonMemberID > 0)
sqlCompany.UpdateBlockNonMembershipID(numberOfNonMemberID);
if (numberOfMemberID > 0)
sqlBranch.UpdateBlockMembershipID(strBranchCode, numberOfMemberID);
connProvider.CommitTransaction();
return true;
}
catch (Exception)
{
connProvider.RollbackTransaction("BlockMembershipID");
throw;
}
finally
{
if (connProvider.CurrentTransaction != null)
connProvider.CurrentTransaction.Dispose();
if (connProvider.DBConnection != null)
{
if (connProvider.DBConnection.State == ConnectionState.Open)
connProvider.DBConnection.Close();
connProvider.DBConnection.Dispose();
}
sqlCompany.Dispose();
sqlBranch.Dispose();
}
}
/// <summary>
/// Include member or non member
/// </summary>
/// <param name="isMember"></param>
/// <param name="branchCode"></param>
/// <param name="name"></param>
/// <param name="isSingaporean"></param>
/// <param name="NRIC"></param>
/// <param name="nSignUpID"></param>
/// <param name="dob"></param>
/// <param name="aUser"></param>
///
/// 0705
public string AddNewMember(bool isMember, string branchCode, string name, bool isSingaporean, string NRIC, int nSignUpID,
DateTime dob, User aUser, bool isFemale, int nmediaSource)
{
string membershipID = string.Empty;
TblMember sqlMember = new TblMember();
sqlMember.FMember = isMember;
sqlMember.StrMemberName = name;
sqlMember.FFemale = isFemale;
if (name.Length >= 19)
sqlMember.StrCardName = name.Substring(0, 19);
else
sqlMember.StrCardName = name;
sqlMember.StrNRICFIN = NRIC;
sqlMember.DtDOB = dob;
sqlMember.StrBranchCode = branchCode;
sqlMember.FSingaporean = isSingaporean;
sqlMember.NMediaSourceID = nmediaSource;
TblCardRequest sqlCardRequest = new TblCardRequest();
TblAudit sqlAudit = new TblAudit();
if (!isMember)
{
ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();
TblCompany sqlCompany = new TblCompany();
try
{
sqlCompany.MainConnectionProvider = connProvider;
sqlMember.MainConnectionProvider = connProvider;
sqlCardRequest.MainConnectionProvider = connProvider;
sqlAudit.MainConnectionProvider = connProvider;
connProvider.OpenConnection();
connProvider.BeginTransaction("AddNewNonMember");
sqlCompany.IncOne();
sqlMember.NMembershipNo = sqlCompany.NNonMembershipNo;
sqlMember.NStatus = 1;
sqlMember.NSignupID = nSignUpID;
sqlMember.StrMembershipID = "NMC" +sqlCompany.NNonMembershipNo;
sqlMember.DtSignupDate = DateTime.Now;
//sqlMember.NCardStatusID = (int)CardStatusType.RequestPrint;
sqlMember.NLoyaltyStatusID = 1;
sqlMember.Insert();
membershipID = "NMC" +sqlCompany.NNonMembershipNo;
connProvider.CommitTransaction();
}
catch (Exception)
{
connProvider.RollbackTransaction("AddNewNonMember");
throw;
}
finally
{
if (connProvider.CurrentTransaction != null)
connProvider.CurrentTransaction.Dispose();
if (connProvider.DBConnection != null)
{
if (connProvider.DBConnection.State == ConnectionState.Open)
connProvider.DBConnection.Close();
connProvider.DBConnection.Dispose();
}
sqlCompany.MainConnactionIsCreatedLocal = true;
sqlMember.MainConnactionIsCreatedLocal = true;
sqlCardRequest.MainConnactionIsCreatedLocal = true;
}
}
else
{
ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();
TblBranch sqlBranch = new TblBranch();
try
{
sqlBranch.MainConnectionProvider = connProvider;
sqlMember.MainConnectionProvider = connProvider;
sqlCardRequest.MainConnectionProvider = connProvider;
sqlAudit.MainConnectionProvider = connProvider;
connProvider.OpenConnection();
connProvider.BeginTransaction("AddNewNonMember");
sqlBranch.StrBranchCode = branchCode;
//sqlBranch.StrBranchCode = "PM";
sqlBranch.IncOne();
sqlMember.NMembershipNo = sqlBranch.NMembershipNo;
sqlMember.NStatus = 1;
sqlMember.NSignupID = nSignUpID;
sqlMember.StrMembershipID = branchCode.TrimEnd() +sqlBranch.NMembershipNo;
// sqlMember.StrMembershipID = "PM" + sqlBranch.NMembershipNo;
sqlMember.DtSignupDate = DateTime.Now;
sqlMember.NCardStatusID = (int)CardStatusType.RequestPrint;
sqlMember.StrCardBranchCode = "HQ";
sqlMember.NLoyaltyStatusID = 1;
sqlMember.Insert();
//Request print for member --now only for fitness member
// sqlCardRequest.NEmployeeID = aUser.NEmployeeID();
// sqlCardRequest.NStatusID = (int)CardStatusType.RequestPrint;
// sqlCardRequest.StrBranchCode = aUser.StrBranchCode();
// sqlCardRequest.StrMembershipID = branchCode.TrimEnd() +sqlBranch.NMembershipNo;
// sqlCardRequest.DtLastEditDate = DateTime.Now;
// sqlCardRequest.Insert();
//Audit trail for request print
// sqlAudit.NAuditTypeID = ACMSLogic.AuditTypeID.MemberCard;
// sqlAudit.NEmployeeID = aUser.NEmployeeID();
// sqlAudit.StrAuditEntry = "New member card request when create new member.";
// sqlAudit.StrReference = branchCode.TrimEnd() +sqlBranch.NMembershipNo;
// sqlAudit.DtDate = DateTime.Now;
// sqlAudit.Insert();
membershipID = branchCode.TrimEnd() +sqlBranch.NMembershipNo;
connProvider.CommitTransaction();
}
catch (Exception)
{
connProvider.RollbackTransaction("AddNewNonMember");
throw;
}
finally
{
if (connProvider.CurrentTransaction != null)
connProvider.CurrentTransaction.Dispose();
if (connProvider.DBConnection != null)
{
if (connProvider.DBConnection.State == ConnectionState.Open)
connProvider.DBConnection.Close();
connProvider.DBConnection.Dispose();
}
sqlBranch.Dispose();
sqlMember.Dispose();
sqlCardRequest.Dispose();
}
}
return membershipID;
}
public static void CurrentLastMembershipID(string strTerminalBranch, ref int lastNonMemberID, ref int lastMemberID)
{
ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();
TblCompany sqlCompany = new TblCompany();
TblBranch sqlBranch = new TblBranch();
try
{
sqlCompany.MainConnectionProvider = connProvider;
sqlBranch.MainConnectionProvider = connProvider;
connProvider.OpenConnection();
connProvider.BeginTransaction("LastMembershipID");
DataTable companyTable = sqlCompany.SelectAll();
sqlBranch.StrBranchCode = strTerminalBranch;
DataTable branchTable = sqlBranch.SelectOne();
connProvider.CommitTransaction();
lastNonMemberID = ACMS.Convert.ToInt32(companyTable.Rows[0]["nNonMembershipNo"]);
lastMemberID = ACMS.Convert.ToInt32(branchTable.Rows[0]["nMembershipNo"]);
return;
}
catch (Exception)
{
connProvider.RollbackTransaction("ResetMembershipID");
throw;
}
finally
{
if (connProvider.CurrentTransaction != null)
connProvider.CurrentTransaction.Dispose();
if (connProvider.DBConnection != null)
{
if (connProvider.DBConnection.State == ConnectionState.Open)
connProvider.DBConnection.Close();
connProvider.DBConnection.Dispose();
}
sqlCompany.Dispose();
sqlBranch.Dispose();
}
}