static void MergeMemberStatus() { try { AppLib.WriteLog("MergeMemberStatus Start"); AppLib.DisplayClear(); long total = 0; for (int yy = 2005; yy <= 2018; yy++) { for (int mm = (yy == 2005 ? 9 : 1); mm <= (yy == 2018 ? 7 : 12); mm++) { string TBName = string.Format("Status{0:d2}{1}", mm, yy); AppLib.DisplayMsg(1, 1, TBName); string Qry = string.Format("insert into {0}..MemberMonthEndStatus select '{3}-{4:d2}-01' ,* from {1}..{2}", DBName_BFS, DBName_Status, TBName, yy, mm); SqlCommand cmd = new SqlCommand(Qry, ConStatus); var data = cmd.ExecuteNonQuery(); AppLib.WriteLog(string.Format("{0} => {1}", TBName, data)); total += long.Parse(data.ToString()); } } AppLib.WriteLog(string.Format("Total = {0}", total)); AppLib.WriteLog("MergeMemberStatus End"); } catch (Exception ex) { AppLib.WriteLog(ex); } }
static void MemberRangeScan() { try { AppLib.WriteLog("Start MemberRangeScan"); AppLib.DisplayClear(); AppLib.DisplayMsg(1, 1, "Starting Member No : "); AppLib.DisplayMsg(1, 3, "Number of Member : "); string MemberNo = AppLib.ReadMsg(25, 1); string Num = AppLib.ReadMsg(25, 3); List <string> lstMemberId = new List <string>(); AppLib.WriteLogDT("Starting Member No: {0}, Number Of Member: {1}", MemberNo, Num); string Qry = string.Format("select top {0} Member_Id from MASTERMEMBER where MEMBER_ID >= {1} order by status_code, MEMBER_ID", Num, MemberNo); SqlCommand cmd = new SqlCommand(Qry, conBFS); var dr = cmd.ExecuteReader(); while (dr.Read()) { lstMemberId.Add(dr["Member_Id"].ToString()); } dr.Close(); AppLib.WriteLogDT("List of Members No : {0}", string.Join(", ", lstMemberId)); foreach (var MNo in lstMemberId) { MemberScanByNo(MNo); } AppLib.WriteLog("End MemberRangeScan"); } catch (Exception ex) { AppLib.WriteLog(ex); } }
static void MonthEndCount() { try { AppLib.WriteLog("MonthEndCount Start"); AppLib.DisplayClear(); long total = 0; for (int yy = 2005; yy <= 2018; yy++) { for (int mm = (yy == 2005 ? 9 : 1); mm <= (yy == 2018 ? 7 : 12); mm++) { string TBName = string.Format("Status{0:d2}{1}", mm, yy); AppLib.DisplayMsg(1, 1, TBName); string Qry = string.Format("Select count(*) from {0}", TBName); SqlCommand cmd = new SqlCommand(Qry, ConStatus); var data = cmd.ExecuteScalar(); AppLib.WriteLog(string.Format("{0} => {1}", TBName, data)); total += long.Parse(data.ToString()); } } AppLib.WriteLog(string.Format("Total = {0}", total)); AppLib.WriteLog("MonthEndCount End"); } catch (Exception ex) { AppLib.WriteLog(ex); } }
static void MemberScan() { try { AppLib.WriteLog("MemberScan Start"); AppLib.DisplayClear(); AppLib.DisplayMsg(1, 1, "Member No : "); string MemberNo = AppLib.ReadMsg(14, 1); MemberScanByNo(MemberNo); AppLib.WriteLog("MemberScan End"); } catch (Exception ex) { AppLib.WriteLog(ex); } }
static void ChangeServer() { try { AppLib.DisplayClear(); AppLib.WriteLog("ChangeServer Start"); AppLib.DisplayMsg(1, 1, "Server Name : "); AppLib.DisplayMsg(1, 3, "User Id : "); AppLib.DisplayMsg(1, 5, "Password : "******"Database Name BFS : "); AppLib.DisplayMsg(1, 9, "Database Name Status : "); ServerName = AppLib.ReadMsg(24, 1); UserId = AppLib.ReadMsg(24, 3); Password = AppLib.ReadMsg(24, 5); DBName_BFS = AppLib.ReadMsg(24, 7); DBName_Status = AppLib.ReadMsg(24, 9); AppLib.WriteLog("ChangeServer End"); DBConnect(); } catch (Exception ex) { AppLib.WriteLog(ex); } }
static void MemberScanByNo(string MemberNo) { try { AppLib.WriteLog(string.Format("Start MemberScanByNo : {0}", MemberNo)); AppLib.DisplayClear(); String Qry = string.Format("Select Member_Code,DateOfJoining from MasterMember where Member_Id = {0}", MemberNo); SqlCommand cmd = new SqlCommand(Qry, conBFS); var drMember = cmd.ExecuteReader(); if (!drMember.Read()) { AppLib.WriteLog(string.Format("Member No {0} is Not Found", MemberNo)); drMember.Close(); return; } string MemberCode = drMember["Member_Code"].ToString(); DateTime DOJ = (DateTime)drMember["DateOfJoining"]; drMember.Close(); AppLib.WriteLogDT("Member Code : {0}, DOJ : {1:dd/MM/yyyy}", MemberCode, DOJ); int yyStart = DOJ.Year <= 2005 ? 2005 : DOJ.Year; int mmStart = DOJ.Year < 2005 || (DOJ.Year == 2005 && DOJ.Month <= 9) ? 9 : DOJ.Month; int yyEnd = 2018; int mmEnd = 7; int MMType = 0, BankCode, BankBranchCode = 0, NubeBranchCode = 0, PaidMonth, TotalPaidMonth, TotalDueMonth, tmpTotalPaidMonth, tmpTotalDueMonth, StatusCode = 0, Resigned = 0, Cancelled = 0, StructOff = 0; decimal SubAmt, BFAmt, SubPaid, BFPaid, SubDue, BFDue, SubTotPaid, BFTotPaid, tmpSubDue, tmpBFDue, tmpSubTotPaid, tmpBFTotPaid, AcBenefit; DateTime LastPaidDate; for (int yy = yyStart; yy <= yyEnd; yy++) { for (int mon = (yy == yyStart ? mmStart : 1); mon <= (yy == yyEnd ? mmEnd : 12); mon++) { string TBName = string.Format("Status{0:d2}{1}", mon, yy); AppLib.DisplayMsg(1, 1, string.Format("Member No : {0}", MemberNo)); AppLib.DisplayMsg(1, 3, string.Format("Member Code : {0}", MemberCode)); AppLib.DisplayMsg(1, 5, string.Format("DOJ : {0:dd/MM/yyyy}", DOJ)); AppLib.DisplayMsg(1, 7, string.Format("Year : {0}", yy)); AppLib.DisplayMsg(1, 9, string.Format("Month : {0:d2}", mon)); Qry = string.Format("Select * from {0} where Member_Code = {1}", TBName, MemberCode); cmd = new SqlCommand(Qry, ConStatus); var dr = cmd.ExecuteReader(); if (dr.Read()) { if (MMType != int.Parse(dr["MemberType_Code"].ToString())) { if (yy != yyStart || mon != mmStart) { MemberType_Changed(MemberCode, MMType, int.Parse(dr["MemberType_Code"].ToString()), yy, mon); } MMType = int.Parse(dr["MemberType_Code"].ToString()); } BankCode = int.Parse(dr["Bank_Code"].ToString()); if (BankBranchCode != int.Parse(dr["Branch_Code"].ToString())) { if (yy != yyStart || mon != mmStart) { BankBranch_Changed(MemberCode, BankBranchCode, int.Parse(dr["Branch_Code"].ToString()), yy, mon); } BankBranchCode = int.Parse(dr["Branch_Code"].ToString()); } if (NubeBranchCode != int.Parse(dr["NUBE_BRANCH_CODE"].ToString())) { if (yy != yyStart || mon != mmStart) { NubeBranch_Changed(MemberCode, NubeBranchCode, int.Parse(dr["NUBE_BRANCH_CODE"].ToString()), yy, mon); } NubeBranchCode = int.Parse(dr["NUBE_BRANCH_CODE"].ToString()); } if (StatusCode != int.Parse(dr["STATUS_CODE"].ToString())) { if (yy != yyStart || mon != mmStart) { Status_Changed(MemberCode, StatusCode, int.Parse(dr["STATUS_CODE"].ToString()), yy, mon); } StatusCode = int.Parse(dr["STATUS_CODE"].ToString()); } if (Resigned != int.Parse(dr["RESIGNED"].ToString())) { if (yy != yyStart || mon != mmStart) { Resigned_Changed(MemberCode, Resigned, int.Parse(dr["RESIGNED"].ToString()), yy, mon); } Resigned = int.Parse(dr["RESIGNED"].ToString()); } if (Cancelled != int.Parse(dr["CANCELLED"].ToString())) { if (yy != yyStart || mon != mmStart) { Cancelled_Changed(MemberCode, Cancelled, int.Parse(dr["CANCELLED"].ToString()), yy, mon); } Cancelled = int.Parse(dr["CANCELLED"].ToString()); } if (StructOff != int.Parse(dr["STRUCKOFF"].ToString())) { if (yy != yyStart || mon != mmStart) { StructOff_Changed(MemberCode, StructOff, int.Parse(dr["STRUCKOFF"].ToString()), yy, mon); } StructOff = int.Parse(dr["STRUCKOFF"].ToString()); } PaidMonth = int.Parse(dr["TOTAL_MONTHS"].ToString()); TotalPaidMonth = int.Parse(dr["TOTALMONTHSPAID"].ToString()); TotalDueMonth = int.Parse(dr["TOTALMONTHSDUE"].ToString()); SubAmt = decimal.Parse(dr["SUBSCRIPTION_AMOUNT"].ToString()); BFAmt = decimal.Parse(dr["BF_AMOUNT"].ToString()); SubPaid = decimal.Parse(dr["TOTALSUBCRP_AMOUNT"].ToString()); BFPaid = decimal.Parse(dr["TOTALBF_AMOUNT"].ToString()); SubDue = decimal.Parse(dr["SUBSCRIPTIONDUE"].ToString()); BFDue = decimal.Parse(dr["BFDUE"].ToString()); SubTotPaid = decimal.Parse(dr["ACCSUBSCRIPTION"].ToString()); BFTotPaid = decimal.Parse(dr["ACCBF"].ToString()); AcBenefit = decimal.Parse(dr["ACCBENEFIT"].ToString()); LastPaidDate = (DateTime)dr["LASTPAYMENTDATE"]; if (yy == yyStart && mon == mmStart) { tmpTotalPaidMonth = TotalPaidMonth; tmpTotalDueMonth = TotalDueMonth; tmpBFDue = BFDue; tmpBFTotPaid = BFTotPaid; tmpSubDue = SubDue; tmpSubTotPaid = SubTotPaid; if (yy == DOJ.Year && mon == DOJ.Month) { if (tmpTotalPaidMonth != 1) { } } else { } } else { } AppLib.WriteLogDT("Month: {0}-{1:d2}, MMType: {2}, Bank: {3:d4}, BankBranch: {4:d4}, NubeBranch: {5:d2}, PaidMonth: {6:d2}, " + "TotalPaidMonth: {7:d3}, TotalDueMonth: {8:d3}, Status: {9}, Resigned: {10}, Cancelled: {11}, StructOff: {12}, " + "SubAmt: {13:N2}, BFAmt: {14:N2}, SubPaid: {15:N2}, BFPaid: {16:N2}, SubDue: {17:N2}, BFDue: {18:N2}, " + "SubTotPaid: {19:N2}, BFTotPaid: {20:N2}, AcBenefit: {21:N2}, LastPaidDate: {22:dd/MM/yyyy}" , yy, mon, MMType, BankCode, BankBranchCode, NubeBranchCode, PaidMonth , TotalPaidMonth, TotalDueMonth, StatusCode, Resigned, Cancelled, StructOff , SubAmt, BFAmt, SubPaid, BFPaid, SubDue, BFDue, SubTotPaid, BFTotPaid, AcBenefit, LastPaidDate); } dr.Close(); } } AppLib.WriteLog(string.Format("End MemberScanByNo : {0}", MemberNo)); } catch (Exception ex) { AppLib.WriteLog(ex); } }
static void MemberInitScanByBankCode(decimal BANK_CODE) { try { AppLib.WriteLogFileName = "NUBE_" + "MemberInitScanByBankCode"; AppLib.WriteLogDT("START:"); AppLib.WriteLog("MemberInitScanByBankCode"); AppLib.WriteLog("------------------------"); AppLib.WriteLog(""); AppLib.WriteLog(""); var bank = db.MASTERBANKs.FirstOrDefault(x => x.BANK_CODE == BANK_CODE); if (bank != null) { AppLib.WriteLog("Bank Id: {0}, Bank Code : {1}, Bank Name : {2}", bank.BANK_CODE, bank.BANK_USERCODE, bank.BANK_NAME); AppLib.DisplayClear(); int i = 1; AppLib.WriteLog("{0,5} {1,-10} {2,-10} {3,-50} {4,-15} {5,-15} {6,-10} {7,-10} {8,-10} {9,-10} {10,-10} {11,-10} {12,-10} {13,-10} {14,-10} {15,-10} {16,-10} {17,-10} {18,-10} {19,-10} {20,-10} {21,-10} {22,-10} {23,-10} {24,-10}", "SNo", "Code", "MemberNo", "Member Name", "IC Old", "IC New", "DOB", "DOJ", "MEND", "LPAID", "SUBSAMT", "TSUBSAMT", "ACCSUBS", "SUBSDUE", "BFAMT", "TBFAMT", "ACCBF", "BFDUE", "TOTMNTS", "TOTMNTSPAD", "TOTMNTSDUE", "STATUSCDE", "RESIGNED", "STRUCKOFF", "STATUS"); var lstMember = db.MASTERMEMBERs.Where(x => x.BANK_CODE == BANK_CODE).OrderBy(x => x.DATEOFJOINING).Select(x => x.MEMBER_CODE).ToList(); foreach (var mmCode in lstMember) { AppLib.DisplayMsg(1, 1, string.Format("[ {0,10} - {1} ]", i, bank.MASTERMEMBERs.Count())); var mm = db.MASTERMEMBERs.FirstOrDefault(x => x.MEMBER_CODE == mmCode); var Status = db.MemberMonthEndStatus.Where(x => x.MEMBER_CODE == mm.MEMBER_CODE).OrderBy(x => x.StatusMonth).FirstOrDefault(); string statusMsg = ""; if (Status == null) { Status = new MemberMonthEndStatu(); statusMsg = "Status Record not Found"; } else { decimal Subs = Status.MEMBERTYPE_CODE == 1 ? (mm.REJOINED == 0 ? 8 : 32) : (mm.REJOINED == 0 ? 4 : 16); decimal BFs = 3; if (mm.DATEOFJOINING >= new DateTime(2005, 9, 1)) { if (Status.SUBSCRIPTION_AMOUNT != Subs) { statusMsg += "SUBSCRIPTION_AMOUNT MISMATCH, "; } if (Status.TOTALSUBCRP_AMOUNT != Subs) { statusMsg += "TOTALSUBCRP_AMOUNT MISMATCH, "; } if (Status.ACCSUBSCRIPTION != Subs) { statusMsg += "ACCSUBSCRIPTION MISMATCH, "; } if (Status.SUBSCRIPTIONDUE != 0) { statusMsg += "SUBSCRIPTIONDUE MISMATCH, "; } if (Status.BF_AMOUNT != BFs) { statusMsg += "BF_AMOUNT MISMATCH, "; } if (Status.TOTALBF_AMOUNT != BFs) { statusMsg += "TOTALBF_AMOUNT MISMATCH, "; } if (Status.ACCBF != BFs) { statusMsg += "ACCBF MISMATCH, "; } if (Status.BFDUE != 0) { statusMsg += "BFDUE MISMATCH, "; } if (Status.TOTAL_MONTHS != 1) { statusMsg += "TOTAL_MONTHS MISMATCH, "; } if (Status.TOTALMONTHSPAID != 1) { statusMsg += "TOTALMONTHSPAID MISMATCH, "; } if (Status.TOTALMONTHSDUE != 0) { statusMsg += "TOTALMONTHSDUE MISMATCH, "; } if (Status.STATUS_CODE != 1 && Status.STATUS_CODE != 5) { statusMsg += "Status Coce Mismatch, "; } if (Status.STRUCKOFF == 1) { statusMsg += "Struckoff Mismatch, "; } if (Status.RESIGNED == 1) { statusMsg += "Resigned Mismatch, "; } if (mm.DATEOFJOINING.Value.Year != Status.LASTPAYMENTDATE.Value.Year || mm.DATEOFJOINING.Value.Month != Status.LASTPAYMENTDATE.Value.Month) { statusMsg += "Last payment Date Mismatch, "; } if (mm.DATEOFJOINING.Value.Year != Status.StatusMonth.Value.Year || mm.DATEOFJOINING.Value.Month != Status.StatusMonth.Value.Month) { statusMsg += "Status Month Date Mismatch, "; } } else { if (Status.StatusMonth.Value.Year != 2005 || Status.StatusMonth.Value.Month != 09) { statusMsg += "Status Month Date Mismatch, "; } var ts = DateTimeSpan.CompareDates(mm.DATEOFJOINING.Value, new DateTime(2005, 9, 1)); if ((Status.TOTALMONTHSPAID + Status.TOTALMONTHSDUE) != ((ts.Years * 12) + ts.Months) + 2) { statusMsg += "Total Month Mismatch, "; } if (Status.ACCBF != (Status.TOTALMONTHSPAID * BFs)) { statusMsg += "Acc BF Mismatch, "; } if (Status.BFDUE != (Status.TOTALMONTHSDUE * BFs)) { statusMsg += "Due BF Mismatch, "; } } } if (!string.IsNullOrWhiteSpace(statusMsg)) { //try //{ // MemberMonthEndErrorLog d = new MemberMonthEndErrorLog() // { // Member_Code = mm.MEMBER_CODE, // YY = mm.DATEOFJOINING.Value.Year, // MM = mm.DATEOFJOINING.Value.Month, // ErrorMsg = "Member_Init" + statusMsg // }; //} //catch (Exception ex) { AppLib.WriteLog(ex); } statusMsg = " Error=> " + statusMsg; } AppLib.WriteLog("{0,5} {1,-10} {2,-10} {3,-50} {4,-15} {5,-15} {6:dd/MM/yyyy} {7:dd/MM/yyyy} {8:dd/MM/yyyy} {9:dd/MM/yyyy} {10,-10} {11,-10} {12,-10} {13,-10} {14,-10} {15,-10} {16,-10} {17,-10} {18,-10} {19,-10} {20,-10} {21,-10} {22,-10} {23,-10} {24,-10}", i++, mm.MEMBER_CODE, mm.MEMBER_ID, mm.MEMBER_NAME, mm.ICNO_OLD, mm.ICNO_NEW, mm.DATEOFBIRTH, mm.DATEOFJOINING, Status.LASTPAYMENTDATE, Status.StatusMonth, Status.SUBSCRIPTION_AMOUNT, Status.TOTALSUBCRP_AMOUNT, Status.ACCSUBSCRIPTION, Status.SUBSCRIPTIONDUE, Status.BF_AMOUNT, Status.TOTALBF_AMOUNT, Status.ACCBF, Status.BFDUE, Status.TOTAL_MONTHS, Status.TOTALMONTHSPAID, Status.TOTALMONTHSPAID, Status.STATUS_CODE, Status.RESIGNED, Status.STRUCKOFF, statusMsg); } } else { AppLib.WriteLog("Bank Code {0} is not found", BANK_CODE); } AppLib.WriteLogDT("END:"); } catch (Exception ex) { AppLib.WriteLog(ex); } }