public DataTable GetDataTable() { TblEmployee employee = new TblEmployee(); DataTable table = new DataTable(); string cmdText = ""; int MemberAge = 0; //string cmdAge = "select datediff(yy, dtDOB,getdate()) from tblmember where strMembershipID = @strMembershipID"; string cmdAge = "select case when datediff(yy, dtDOB,getdate())=22 and getdate()<(case when ISDATE(Convert(varchar(4),year(GETDATE()))+'-'+Convert(varchar(5),dtDOB,110))=1 then Convert(varchar(4),year(GETDATE()))+'-'+Convert(varchar(5),dtDOB,110) else Convert(varchar(4),year(GETDATE()))+'-02-28' end) then 21 else datediff(yy, dtDOB,getdate()) end from tblmember where strMembershipID = @strMembershipID"; DataTable AgeTable = new DataTable(); AgeTable = employee.LoadData(cmdAge, new string[] {"@strMembershipID"},new object[] {myPOS.StrMembershipID}); if (ACMS.Convert.ToInt32(AgeTable.Rows[0][0]) <= 21)//jackie change to 18yrs old 1507 MemberAge =1; if (myCategoryID == 1 || myCategoryID == 3 || myCategoryID == 4 || myCategoryID == 5 || myCategoryID == 6 || myCategoryID == 14 || myCategoryID == 23) { if (MemberAge == 0) cmdText = "Select A.* from tblPackage A, tblPackageBranch B where nCategoryID = @nCategoryID AND " + " A.dtValidStart <= @today and A.dtValidEnd >= @today AND A.strPackageCode = B.strPackageCode AND " + " B.strBranchCode = @strBranchCode AND A.nStatus = 1 and fSell=1 and fstudentpackage <> 1"; else cmdText = "Select A.* from tblPackage A, tblPackageBranch B where nCategoryID = @nCategoryID AND " + " A.dtValidStart <= @today and A.dtValidEnd >= @today AND A.strPackageCode = B.strPackageCode AND " + " B.strBranchCode = @strBranchCode AND A.nStatus = 1 and fSell=1"; table = employee.LoadData(cmdText, new string[] {"@nCategoryID", "@today", "@strBranchCode","@strMembershipID","@fMemberAge"}, new object[] {myCategoryID, DateTime.Today.Date, myStrBranchCode,myPOS.StrMembershipID,MemberAge}); return table; } if (myCategoryID == 2) { cmdText = "Select A.* from tblPackage A, tblPackageBranch B where nCategoryID = @nCategoryID AND " + " A.dtValidStart <= @today and A.dtValidEnd >= @today AND A.strPackageCode = B.strPackageCode AND " + " B.strBranchCode = @strBranchCode AND A.nStatus = 1 AND A.fGiro = 1"; table = employee.LoadData(cmdText, new string[] {"@nCategoryID", "@today", "@strBranchCode"}, new object[] {myCategoryID, DateTime.Today.Date, myStrBranchCode}); return table; } if (myCategoryID == 7) { cmdText = "Select * from tblCreditPackage where nCategoryID = 7 AND " + " dtValidStart <= @today and dtValidEnd >= @today"; table = employee.LoadData(cmdText, new string[] {"@today"}, new object[] {DateTime.Today.Date}); return table; } //if (myCategoryID == 13) //{ // cmdText = "Select * from tblCreditPackage where nCategoryID = 13 AND " + // " dtValidStart <= @today and dtValidEnd >= @today"; // table = employee.LoadData(cmdText, new string[] { "@today" }, new object[] { DateTime.Today.Date }); // return table; //} if (myCategoryID == 36) { cmdText = "Select * from tblCreditPackage where nCategoryID = 36 AND " + " dtValidStart <= @today and dtValidEnd >= @today"; table = employee.LoadData(cmdText, new string[] { "@today" }, new object[] { DateTime.Today.Date }); return table; } if (myCategoryID == 37) { cmdText = "Select * from tblCreditPackage where nCategoryID = 37 AND " + " dtValidStart <= @today and dtValidEnd >= @today"; table = employee.LoadData(cmdText, new string[] { "@today" }, new object[] { DateTime.Today.Date }); return table; } if (myCategoryID == 38) { cmdText = "Select * from tblCashVoucher where strbranchcode=@strBranchCode and fsell=1 and nstatusid=0 ORDER BY strSN "; table = employee.LoadData(cmdText, new string[] { "@strBranchCode" }, new object[] { myStrBranchCode }); return table; } if (myCategoryID == 8 || myCategoryID == 9) { cmdText = "Select * from tblPackageGroup where nCategoryID = @nCategoryID AND " + " dtValidStart <= @today and dtValidEnd >= @today"; table = employee.LoadData(cmdText, new string[] {"@nCategoryID", "@today"}, new object[] {myCategoryID, DateTime.Today.Date}); return table; } else if (myCategoryID == 11 || myCategoryID == 12 || myCategoryID == 21) { cmdText = "Select A.*, C.strDescription as BrandDesc, D.strDescription as ColorDesc, " + " E.strDescription as SizeDesc, F.strDescription as StyleDesc, R.nQty " + " from tblProduct A inner join tblProductBranch B on A.strProductCode = B.strProductCode " + " left outer join tblBrand C on A.strBrandCode = C.strBrandCode "+ " left outer join tblColor D on A.strColorCode = D.strColorCode "+ " left outer join tblSize E on A.strSizeCode = E.strSizeCode "+ " left outer join tblStyle F on A.strStyleCode = F.strStyleCode "+ " left outer join tblSCStockRecon R on (A.strProductCode = R.strProductCode AND B.strBranchCode=R.strBranchCode) "+ " where nCategoryID = @nCategoryID AND " + " A.dtValidStart <= @today and A.dtValidEnd >= @today AND " + " B.strBranchCode = @strBranchCode AND A.nStatus = 1 "; table = employee.LoadData(cmdText, new string[] {"@nCategoryID", "@today", "@strBranchCode"}, new object[] {myCategoryID, DateTime.Today.Date, myStrBranchCode}); return table; } else if (myCategoryID == 21) { cmdText = "Select A.* from tblProduct A, tblProductBranch B where nCategoryID = @nCategoryID AND " + " A.dtValidStart <= @today and A.dtValidEnd >= @today AND A.strProductCode = B.strProductCode AND " + " B.strBranchCode = @strBranchCode AND A.nStatus = 1 AND A.strProductCode = 'MW' "; table = employee.LoadData(cmdText, new string[] {"@nCategoryID", "@today", "@strBranchCode"}, new object[] {myCategoryID, DateTime.Today.Date, myStrBranchCode}); return table; } else if (myCategoryID == 17) { //cmdText = "select r2.* from tblreceipt r2 Where strMembershipID = @strMembershipID AND nCategoryID = 17 AND fVoid = 0 AND mTotalAmount > 0 and strReceiptNo not in (select strcode from tblReceiptEntries re join tblReceipt r on re.strReceiptNo=r.strReceiptNo where r.fVoid=0 and r.strReceiptNo=r2.strReceiptNo)"; cmdText = "select r1.* from (select r.* from tblreceipt r Where strMembershipID = '" + myPOS.StrMembershipID + "' AND nCategoryID = 17 AND fVoid = 0 AND mTotalAmount > 0) r1 left join (select re.strCode from tblReceiptEntries re join tblReceipt r on re.strReceiptNo=r.strReceiptNo where r.fVoid=0) r2 on r1.strReceiptNo=r2.strCode where strCode is null "; table = employee.LoadData(cmdText, new string[] { "@strMembershipID" }, new object[] { myPOS.StrMembershipID }); return table; } return null; }
public DataTable SearchOnePackageCode(string strPackageCode) { TblEmployee employee = new TblEmployee(); DataTable table = new DataTable(); string cmdText = ""; if (myCategoryID == 1 || myCategoryID == 3 || myCategoryID == 4 || myCategoryID == 5 || myCategoryID == 6) { cmdText = "Select A.* from tblPackage A, tblPackageBranch B where nCategoryID = @nCategoryID AND " + " A.dtValidStart <= @today and A.dtValidEnd >= @today AND A.strPackageCode = B.strPackageCode AND " + " B.strBranchCode = @strBranchCode AND A.nStatus = 1 and A.strPackageCode = @strPackageCode"; table = employee.LoadData(cmdText, new string[] {"@nCategoryID", "@today", "@strBranchCode", "@strPackageCode"}, new object[] {myCategoryID, DateTime.Today.Date, myStrBranchCode, strPackageCode}); return table; } if ((myCategoryID == 2) || (myCategoryID == 34)) { cmdText = "Select A.* from tblPackage A, tblPackageBranch B where nCategoryID = @nCategoryID AND " + " A.dtValidStart <= @today and A.dtValidEnd >= @today AND A.strPackageCode = B.strPackageCode AND " + " B.strBranchCode = @strBranchCode AND A.nStatus = 1 AND A.fGiro = 1 and A.strPackageCode = @strPackageCode"; table = employee.LoadData(cmdText, new string[] {"@nCategoryID", "@today", "@strBranchCode", "@strPackageCode"}, new object[] {myCategoryID, DateTime.Today.Date, myStrBranchCode, strPackageCode}); return table; } if (myCategoryID == 7) { cmdText = "Select * from tblCreditPackage where nCategoryID = 7 AND " + " dtValidStart <= @today and dtValidEnd >= @today and strCreditPackageCode = @strCreditPackageCode " ; table = employee.LoadData(cmdText, new string[] {"@today", "@strCreditPackageCode"}, new object[] {DateTime.Today.Date, strPackageCode}); return table; } if (myCategoryID == 8 || myCategoryID == 9) { cmdText = "Select * from tblPackageGroup where nCategoryID = @nCategoryID AND " + " dtValidStart <= @today and dtValidEnd >= @today and strPackageGroupCode = @strPackageGroupCode"; table = employee.LoadData(cmdText, new string[] {"@nCategoryID", "@today", "@strPackageGroupCode"}, new object[] {myCategoryID, DateTime.Today.Date, strPackageCode}); return table; } return null; }