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); }
}
