//User defined
private void LoadReport()
{
try
{
MemberReport.Reset();
DataTable dt = getData();
if (dt.Rows.Count > 0)
{
ReportDataSource masterData = new ReportDataSource("ViewMasterMember", dt);
MemberReport.LocalReport.DataSources.Add(masterData);
MemberReport.LocalReport.ReportEmbeddedResource = "Nube.Reports.MemberReport.rdlc";
ReportParameter RP = new ReportParameter("Title", "STRUCKOFF MEMBER REPORT");
MemberReport.LocalReport.SetParameters(RP);
MemberReport.RefreshReport();
LoadBankReport(dt);
}
else
{
MessageBox.Show("No Records Found!");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public override Task <ActionResponse> DoReportMember(MemberReport r, ServerCallContext context)
{
var response = new ActionResponse();
try
{
var z = from x in db.Members
join u in db.Users on x.UserId equals u.Id
where u.Name == r.ReportingMember.User.Name
select x;
var q = from x in db.Members
join u in db.Users on x.UserId equals u.Id
join y in db.Members on r.ReportedMember.User.Name equals y.User.Name
where u.Name == r.ReportingMember.User.Name
select new MemberReport {
ReportedMember = y, ReportingMember = x, Reason = r.Reason
};
db.Add(q.First());
db.SaveChanges();
response.Result = (int)HttpStatusCode.OK;
}
catch
{
response.Result = (int)HttpStatusCode.Forbidden;
}
return(Task.FromResult(response));
}
private void btnSearch_Click(object sender, RoutedEventArgs e)
{
if (dtpDate.Text == "")
{
MessageBox.Show("Enter date");
}
else
{
try
{
mon = dtpDate.SelectedDate.Value.Month.ToString();
MemberReport.Reset();
DataTable dt = getData();
ReportDataSource masterData = new ReportDataSource("HalfShare", dt);
MemberReport.LocalReport.DataSources.Add(masterData);
MemberReport.LocalReport.ReportEmbeddedResource = "Nube.Reports.HalfShare.rdlc";
ReportParameter rp = new ReportParameter("Month", String.Format("{0:MMM-yyyy}", dtpDate.SelectedDate.Value));
MemberReport.LocalReport.SetParameters(rp);
MemberReport.RefreshReport();
}
catch (Exception ex)
{
ExceptionLogging.SendErrorToText(ex);
}
}
}
public Member RandomMemberFromData(ReportManager manager)
{
MemberReport report = manager.GetMemberReport();
MemberRecord selected = (MemberRecord)report.Rows[random.Next(report.Rows.Count)];
return(selected.ToMemberObject());
}
private void btnClear_Click(object sender, RoutedEventArgs e)
{
cmbNubeBranch.Text = "";
cmbBank.Text = "";
cmbBranch.Text = "";
dtpFromDate.Text = "";
dtpToDate.Text = "";
MemberReport.Clear();
NUBEMemberReport.Clear();
}
private void MemberReportForm_Load(object sender, EventArgs e)
{
MemberReport ds = new MemberReport();
MemberReportTableAdapters.viewMemRptTableAdapter ta =
new MemberReportTableAdapters.viewMemRptTableAdapter();
ta.Fill(ds.viewMemRpt);
MemberReportCR cr = new MemberReportCR();
cr.SetDataSource(ds);
crystalReportViewer1.ReportSource = cr;
}
private void btnClear_Click(object sender, RoutedEventArgs e)
{
cmbNubeBranch.Text = "";
cmbBank.Text = "";
cmbBranch.Text = "";
dtpFromDate.Text = "";
dtpToDate.Text = "";
cmbReasonBranch.Text = "";
txtMemberNoFrom.Text = "";
txtMemberNoTo.Text = "";
NUBEMemberReport.Clear();
MemberReport.Clear();
txtMemberNo.Text = "";
rbtResingDate.IsChecked = true;
}
public ActionResult DownloadReport(MemberSearchViewModel model)
{
SysUser user = Session[SessionKey.USER] as SysUser;
MemoryStream memoryStream = new MemoryStream();
try
{
List <Member> memberList = this.memberService.search(model, user);
MemberReport report = new MemberReport();
IWorkbook wb = report.create(memberList);
wb.Write(memoryStream);
}
catch (Exception e)
{
ViewBag.ErrorMessage = e.Message;
}
return(File(memoryStream.ToArray(), "application/vnd.ms-excel", "會員資料.xls"));
}
private void btnClear_Click(object sender, RoutedEventArgs e)
{
try
{
cmbNubeBranch.Text = "";
cmbBank.Text = "";
cmbBranch.Text = "";
dtpFromDate.Text = "";
dtpToDate.Text = "";
txtMemberNoFrom.Text = "";
txtMemberNoTo.Text = "";
chkRejoin.IsChecked = false;
MemberReport.Clear();
NUBEMemberReport.Clear();
}
catch (Exception ex)
{
ExceptionLogging.SendErrorToText(ex);
}
}
//User defined
private void LoadReport()
{
try
{
MemberReport.Reset();
DataTable dt = getData();
DataTable rt = getResignData();
if (dt.Rows.Count > 0)
{
//if (chkSimple.IsChecked == true)
//{
// dgvResigned.ItemsSource = dt.DefaultView;
//}
//else
//{
ReportDataSource masterData = new ReportDataSource("Resign", dt);
MemberReport.LocalReport.DataSources.Add(masterData);
ReportDataSource master = new ReportDataSource("ResignStatus", rt);
MemberReport.LocalReport.DataSources.Add(master);
MemberReport.LocalReport.ReportEmbeddedResource = "Nube.Reports.rptResign.rdlc";
ReportParameter RP = new ReportParameter("TotalMember", dt.Rows.Count.ToString());
MemberReport.LocalReport.SetParameters(RP);
MemberReport.RefreshReport();
LoadBankReport();
//}
}
else
{
MessageBox.Show("No Records Found!");
}
}
catch (Exception ex)
{
ExceptionLogging.SendErrorToText(ex);
}
}
/**
* Creates a new SchedulerInterface which then runs the accounting procedure.
*/
public SchedulerInterface()
{
//for communciation with the tester
UserInterface ui = new UserInterface();
ui.message("\nRunning the accounting procedure ...\n");
//Use today's date for all reports
DateTime now = new DateTime();
try
{
//Generate provider reports
ui.message("Generating the providers' reports ...");
Providers providers = new Providers();
providers.open();
List <Person> allProviders = providers.getAll();
foreach (Person person in allProviders)
{
Provider provider = (Provider)person;
ProviderReportGenerator generator
= new ProviderReportGenerator(provider, now);
ProviderReport theReport = generator.getReport();
if (theReport.getDetailCount() > 0)
{
theReport.sendByEmail(provider.getName());
}
}
providers.close();
//Generate member reports
ui.message("Generating the members' reports ...");
Members members = new Members();
members.open();
List <Person> allMembers = members.getAll();
foreach (Person person in allMembers)
{
Member member = (Member)person;
MemberReportGenerator generator
= new MemberReportGenerator(member, now);
MemberReport theReport = generator.getReport();
if (theReport.getDetailCount() > 0)
{
theReport.sendByEmail(member.getName());
}
}
members.close();
//Generate accounts payable report
ui.message("Generating the accounts payable report ...");
AccountsPayableReportGenerator generator
= new AccountsPayableReportGenerator(now);
generator.getReport().sendByEmail("Accounts Payable");
//Generate EFT data
ui.message("Generating the EFT data ...");
EFTReportGenerator eftGenerator = new EFTReportGenerator(now);
eftGenerator.getReport().print("EFT Data");
ui.message("\nAccounting procedure completed successfully.\n\n");
}
catch (FileNotFoundException ex)
{
//occurs if a file cannot be created
ui.errorMessage(ex.Message);
}
}//default constructor
void FormLoad()
{
string sWhere = "";
string sWhere1 = "";
string sWhere2 = "";
if (!string.IsNullOrEmpty(dtpDate.Text.ToString()))
{
sWhere = string.Format(" WHERE FD.FEEYEAR={0} AND FD.FEEMONTH={1} ", Convert.ToDateTime(dtpDate.SelectedDate).Year, Convert.ToDateTime(dtpDate.SelectedDate).Month);
}
sWhere = sWhere + " AND ISNOTMATCH=0 AND ISUNPAID=0 AND FD.STATUS='FEES ENTRY' ";
if (!string.IsNullOrEmpty(cmbNubeBranch.Text))
{
if (!string.IsNullOrEmpty(sWhere))
{
sWhere = sWhere + " AND MB.NUBE_BRANCH_CODE=" + cmbNubeBranch.SelectedValue;
}
}
decimal dBankCode = 0;
if (!string.IsNullOrEmpty(cmbBank.Text))
{
dBankCode = Convert.ToDecimal(cmbBank.SelectedValue);
}
if (!string.IsNullOrEmpty(cmbBank.Text))
{
sWhere = sWhere + " AND MM.BANK_CODE=" + dBankCode;
}
if (!string.IsNullOrEmpty(cmbBranch.Text))
{
sWhere = sWhere + " AND MM.BRANCH_CODE=" + cmbBranch.SelectedValue;
}
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(AppLib.connStr))
{
string str = "";
str = " SELECT ROW_NUMBER() OVER(ORDER BY MEMBER_NAME ASC) AS RNO,ISNULL(MM.MEMBER_ID, 0)MEMBERID,BK.BANK_USERCODE BANK, \r" +
" ISNULL(MM.MEMBER_NAME, '')MEMBER_NAME, CASE WHEN ISNULL(MM.ICNO_NEW, '') <> '' THEN MM.ICNO_NEW ELSE MM.ICNO_OLD END NRIC, \r" +
" FD.AMOUNTINS \r" +
" FROM FEESDETAILS FD(NOLOCK) \r" +
" LEFT JOIN FEESMASTER FM(NOLOCK) ON FM.FEEID = FD.FEEID \r" +
" LEFT JOIN MASTERBANK BK(NOLOCK) ON BK.BANK_CODE=FM.BANKID \r" +
" LEFT JOIN MASTERMEMBER MM(NOLOCK) ON MM.MEMBER_CODE = FD.MEMBERCODE \r" +
" LEFT JOIN MASTERBANKBRANCH MB(NOLOCK) ON MB.BANKBRANCH_CODE = MM.BRANCH_CODE \r" +
sWhere + sWhere2 + "\r";
string sNewMEmber = string.Format(" SELECT ROW_NUMBER() OVER(ORDER BY MEMBER_NAME ASC) AS RNO,ISNULL(MM.MEMBER_ID, 0)MEMBERID,MB.BANK_USERCODE BANK, \r" +
" ISNULL(MM.MEMBER_NAME, '')MEMBER_NAME, CASE WHEN ISNULL(MM.ICNO_NEW, '') <> '' THEN MM.ICNO_NEW ELSE MM.ICNO_OLD END NRIC,10 AMOUNTINS \r" +
" FROM MASTERMEMBER MM (NOLOCK) \r" +
" LEFT JOIN MASTERBANK MB(NOLOCK) ON MB.BANK_CODE=MM.BANK_CODE \r" +
" WHERE YEAR(MM.DATEOFJOINING)={0} AND MONTH(MM.DATEOFJOINING)={1} ", Convert.ToDateTime(dtpDate.SelectedDate).Year, Convert.ToDateTime(dtpDate.SelectedDate).Month);
//string sSummary = string.Format(" SELECT FM.FEEID,MB.BANK_USERCODE BANK,COUNT(*) TOTALMEMBERS,SUM(FD.AMOUNTINS)AMOUNT \r" +
// " FROM FEESDETAILS FD(NOLOCK) \r" +
// " LEFT JOIN FEESMASTER FM(NOLOCK) ON FM.FEEID = FD.FEEID \r" +
// " LEFT JOIN MASTERBANK MB(NOLOCK) ON MB.BANK_CODE=FM.BANKID \r" +
// " WHERE FD.FEEYEAR={0} AND FD.FEEMONTH={1} AND ISNOTMATCH=0 AND ISUNPAID=0 AND FD.STATUS='FEES ENTRY' \r GROUP BY FM.FEEID,MB.BANK_USERCODE", Convert.ToDateTime(dtpDate.SelectedDate).Year, Convert.ToDateTime(dtpDate.SelectedDate).Month);
string sSummary = string.Format("select BANK,COUNT(*)TOTALMEMBERS,sum(amountins) AMOUNT from ({0} union {1}) t1 group by BANK", str, sNewMEmber);
SqlCommand cmd = new SqlCommand(str, conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.SelectCommand.CommandTimeout = 0;
da.Fill(dt);
cmd = new SqlCommand(sSummary, conn);
da = new SqlDataAdapter(cmd);
da.SelectCommand.CommandTimeout = 0;
DataTable dtSummary = new DataTable();
da.Fill(dtSummary);
cmd = new SqlCommand(sNewMEmber, conn);
da = new SqlDataAdapter(cmd);
da.SelectCommand.CommandTimeout = 0;
DataTable dtNewMember = new DataTable();
da.Fill(dtNewMember);
// MEMBERSHIP REPORT
if (dt.Rows.Count > 0)
{
MemberReport.Reset();
ReportDataSource masterData = new ReportDataSource("TakafulMemberReport", dt);
MemberReport.LocalReport.DataSources.Add(masterData);
MemberReport.LocalReport.ReportEmbeddedResource = "Nube.Reports.TakafulMemberReport.rdlc";
ReportParameter[] NB = new ReportParameter[3];
NB[0] = new ReportParameter("BANK", cmbBank.Text);
NB[1] = new ReportParameter("MONTH", string.Format("{0:MMM}", dtpDate.SelectedDate));
NB[2] = new ReportParameter("YEAR", Convert.ToDateTime(dtpDate.SelectedDate).Year.ToString());
MemberReport.LocalReport.SetParameters(NB);
MemberReport.RefreshReport();
}
else
{
MessageBox.Show("No Data Foud!");
}
if (dtNewMember.Rows.Count > 0)
{
NewMemberReport.Reset();
ReportDataSource masterDataNewMember = new ReportDataSource("TakafulNewMember", dtNewMember);
NewMemberReport.LocalReport.DataSources.Add(masterDataNewMember);
NewMemberReport.LocalReport.ReportEmbeddedResource = "Nube.Reports.TakafulNewMember.rdlc";
ReportParameter[] NBNew = new ReportParameter[2];
NBNew[0] = new ReportParameter("MONTH", string.Format("{0:MMM}", dtpDate.SelectedDate));
NBNew[1] = new ReportParameter("YEAR", Convert.ToDateTime(dtpDate.SelectedDate).Year.ToString());
NewMemberReport.LocalReport.SetParameters(NBNew);
NewMemberReport.RefreshReport();
}
else
{
MessageBox.Show("New Member Nota Foud!");
}
if (dtSummary.Rows.Count > 0)
{
SummaryReport.Reset();
ReportDataSource masterDataSummary = new ReportDataSource("TAKAFULSUMMARY", dtSummary);
SummaryReport.LocalReport.DataSources.Add(masterDataSummary);
SummaryReport.LocalReport.ReportEmbeddedResource = "Nube.Reports.TakafulSummary.rdlc";
ReportParameter[] NBs = new ReportParameter[2];
NBs[0] = new ReportParameter("MONTH", string.Format("{0:MMM}", dtpDate.SelectedDate));
NBs[1] = new ReportParameter("YEAR", Convert.ToDateTime(dtpDate.SelectedDate).Year.ToString());
SummaryReport.LocalReport.SetParameters(NBs);
SummaryReport.RefreshReport();
}
else
{
MessageBox.Show("Summary No Data Foud!");
}
}
}
public int GetWeeklyMemberReports()
{
int result = 0; //0: success, 1: member is null, 2: serveList
if (Validator.OnlyRunOnceFlagForMember)
{
Validator.OnlyRunOnceFlagForMember = false;
List <MemberReport> memberReports = null;
try
{
while (true)
{
Schedule _schedule = scheduleList.GetSchedule(ReportType.MemberReportType);
DateTime nTime = DateTime.Now;
//String _nowTime = DateTime.Now.ToString("hh:mm");
String _nowTime = nTime.Hour.ToString() + ":" + nTime.Minute.ToString();
String _schTime = _schedule.Time.Hours.ToString() + ":" + _schedule.Time.Minutes.ToString();
Console.WriteLine("now -> system: " + _nowTime + "->" + _schTime);
if (_nowTime.Equals(_schTime))
{
memberReports = new List <MemberReport>();
List <Member> memberList = ml.GetAllMembers();
if (memberList != null)
{
//compare the current time with the time set
foreach (Member _member in memberList)
{
MemberReport memberReport = new MemberReport();
memberReports.Add(memberReport);
String fileName;
_nowTime = _nowTime.Replace(":", "_");
fileName = "Member_" + _member.Name + "_" + _nowTime + ".txt";
string _status = "";
// System.IO.File.WriteAllText(@"WriteText.txt", text);
using (System.IO.StreamWriter file = new System.IO.StreamWriter(PathFactory.ReportPath() + fileName))
{
file.WriteLine("----------------------Member Report--------------------");
file.WriteLine("Member ID: " + _member.ID);
file.WriteLine("Member Name: " + _member.Name);
file.WriteLine("State: " + _member.State);
file.WriteLine("Street Address: " + _member.StreetAddress);
file.WriteLine("Zipcode: " + _member.ZipCode);
if (_member.Status == 0)
{
_status = "Accepted";
}
else if (_member.Status == 2)
{
_status = "Suspened";
}
else
{
_status = "Invalid";
}
file.WriteLine("Status: " + _status);
List <ServiceRecord> serveList = serviceRecordList.GetAllServiceRecordForMember(_member.ID);
if (serveList != null)
{
int counter = 0;
foreach (ServiceRecord s in serveList)
{
counter++;
int serviceCode = s.ServiceCode;
Service service = providerDirectory.GetService(serviceCode);
if (service != null)
{
file.WriteLine("\nService:" + counter);
file.WriteLine("Service Name: " + service.ServiceName);
file.WriteLine("Service Code: " + service.ServiceCode);
file.WriteLine("Service Fee: " + service.ServiceFee);
}
}
}
else
{
result = 2; //servicelist is null;
//break;
}
}
memberReports.Add(memberReport);
}
}
}
Thread.Sleep(40000);
}
}
catch (Exception e)
{
memberReports = null;
throw new HttpResponseException(
Request.CreateErrorResponse(HttpStatusCode.BadRequest, e.Message));
}
}
return(result);
}
private void fullReportToolStripMenuItem1_Click(object sender, EventArgs e)
{
MemberReport frm = new MemberReport();
frm.ShowDialog();
}
public string GenerateMemberReport(User member, List <UserServiceLinker> services)
{
Report memberReport = new MemberReport(member, services);
return(memberReport.ReportBody);
}
private void LoadReport()
{
try
{
MemberReport.Reset();
DataTable dt = new DataTable();
dt = getData();
if (dt.Rows.Count > 0)
{
string Total = dt.Rows.Count.ToString();
ReportDataSource masterData = new ReportDataSource("ActiveMember", dt);
MemberReport.LocalReport.DataSources.Add(masterData);
ReportDataSource Data = new ReportDataSource("DataSet1", dt);
MemberReport.LocalReport.DataSources.Add(Data);
MemberReport.LocalReport.ReportEmbeddedResource = "Nube.Reports.frmActiveMemberReport.rdlc";
ReportParameter[] NB = new ReportParameter[5];
if (!string.IsNullOrEmpty(cmbNubeBranch.Text))
{
NB[0] = new ReportParameter("NubeBranchName", "NUBE BRANCH - " + cmbNubeBranch.Text.ToString());
}
else
{
NB[0] = new ReportParameter("NubeBranchName", "");
}
if (!string.IsNullOrEmpty(cmbBank.Text))
{
NB[1] = new ReportParameter("BankName", "BANK - " + cmbBank.Text.ToString());
}
else
{
NB[1] = new ReportParameter("BankName", "");
}
if (!string.IsNullOrEmpty(cmbBranch.Text))
{
NB[2] = new ReportParameter("BranchName", "BANK BRANCH - " + cmbBranch.Text.ToString());
}
else
{
NB[2] = new ReportParameter("BranchName", "");
}
NB[3] = new ReportParameter("TotalMember", Total);
if (sForm_Name == "NewMemberReport")
{
NB[4] = new ReportParameter("Title", "NEW MEMBER REPORT");
}
else
{
NB[4] = new ReportParameter("Title", "ACTIVE MEMBER REPORT");
}
MemberReport.LocalReport.SetParameters(NB);
MemberReport.RefreshReport();
LoadBankReport(dt);
}
else
{
MessageBox.Show("No Records Found!");
}
}
catch (Exception ex)
{
ExceptionLogging.SendErrorToText(ex);
}
}
private void GetDetails()
{
DataTable dt = new DataTable();
dtEXBANK.Rows.Clear();
dtEXNUBE.Rows.Clear();
DateTime dtfirstDayOfNextMonth = new DateTime(Convert.ToDateTime(dtpDOB.SelectedDate).Year, Convert.ToDateTime(dtpDOB.SelectedDate).Month, 1).AddMonths(1);
string dateMonth = string.Format("{0:MMyyyy}", dtpDOB.SelectedDate.Value);
//if (dtpDOB.SelectedDate > Convert.ToDateTime("28/FEB/2018").Date)
//{
using (SqlConnection con = new SqlConnection(AppLib.connStr))
{
con.Open();
string sWhere = "";
if (!string.IsNullOrEmpty(cmbBankName.Text))
{
sWhere = " AND BB.NUBE_BRANCH_CODE=" + cmbBankName.SelectedValue;
}
else
{
sWhere = "";
}
if (!string.IsNullOrEmpty(cmbBank.Text))
{
sWhere = sWhere + " AND MB.BANK_NAME='" + cmbBank.Text + "'";
}
if (!string.IsNullOrEmpty(cmbBranch.Text))
{
sWhere = sWhere + " AND BB.BANKBRANCH_NAME='" + cmbBranch.Text + "'";
}
if (Convert.ToInt32(cmbBankName.SelectedValue) == 11)
{
if (chkMelaka.IsChecked == true && chkNegeriSembilan.IsChecked == false)
{
sWhere = sWhere + " AND (MS.STATE_NAME LIKE '%MELAKA%') ";
}
else if (chkMelaka.IsChecked == false && chkNegeriSembilan.IsChecked == true)
{
sWhere = sWhere + " AND (MS.STATE_NAME NOT LIKE '%MELAKA%') ";
}
}
DateTime dtSelect = dtpDOB.SelectedDate.Value;
dtSelect = new DateTime(dtSelect.Year, dtSelect.Month, 1);
Qry = string.Format(" SELECT ISNULL(NB.NUBE_BRANCH_NAME,'') AS NUBE_BRANCH_NAME,ISNULL(MB.BANK_NAME,'') AS BANK_NAME, \r" +
" ISNULL(MB.BANK_USERCODE, '') + '_' + ISNULL(BB.BANKBRANCH_USERCODE, '') AS BANKBRANCH, \r" +
" ISNULL(MM.SEX, '') AS SEX, ISNULL(MM.RACE_CODE, 0) AS RACE_CODE, \r" +
" (CASE WHEN ST.STATUS_CODE = 1 THEN 1.0 ELSE 2.0 END) STATUS \r" +
" FROM MemberMonthEndStatus ST(NOLOCK)\r" +
" LEFT JOIN MASTERMEMBER MM(NOLOCK) ON MM.MEMBER_CODE = ST.MEMBER_CODE \r" +
" LEFT JOIN MASTERBANK MB(NOLOCK) ON MB.BANK_CODE = ST.BANK_CODE \r" +
" LEFT JOIN MASTERBANKBRANCH BB(NOLOCK) ON BB.BANKBRANCH_CODE = ST.BRANCH_CODE \r" +
" LEFT JOIN MASTERNUBEBRANCH NB(NOLOCK) ON NB.NUBE_BRANCH_CODE = BB.NUBE_BRANCH_CODE \r" +
" LEFT JOIN MASTERSTATE MS(NOLOCK) ON MS.STATE_CODE = BB.BANKBRANCH_STATE_CODE \r" +
" WHERE ST.StatusMonth = '{0:yyyy/MM/dd}' and ST.STATUS_CODE IN(1,2) " + sWhere + " \r ORDER BY NB.NUBE_BRANCH_NAME ", dtSelect);
//if ((Convert.ToDateTime(dtpDOB.SelectedDate).Year >= 2016 && Convert.ToDateTime(dtpDOB.SelectedDate).Month > 3) || Convert.ToDateTime(dtpDOB.SelectedDate).Year > 2016)
//{
// Qry = string.Format(" SELECT ISNULL(NB.NUBE_BRANCH_NAME,'') AS NUBE_BRANCH_NAME,ISNULL(MB.BANK_NAME,'') AS BANK_NAME, \r" +
// " ISNULL(MB.BANK_USERCODE, '') + '_' + ISNULL(BB.BANKBRANCH_USERCODE, '') AS BANKBRANCH, \r" +
// " ISNULL(MM.SEX, '') AS SEX, ISNULL(MM.RACE_CODE, 0) AS RACE_CODE, \r" +
// " (CASE WHEN ST.STATUS_CODE = 1 THEN 1.0 ELSE 2.0 END) STATUS \r" +
// " FROM NUBESTATUS..STATUS{0:MMyyyy} ST(NOLOCK)\r" +
// " LEFT JOIN MASTERMEMBER MM(NOLOCK) ON MM.MEMBER_CODE = ST.MEMBER_CODE \r" +
// " LEFT JOIN MASTERBANK MB(NOLOCK) ON MB.BANK_CODE = ST.BANK_CODE \r" +
// " LEFT JOIN MASTERBANKBRANCH BB(NOLOCK) ON BB.BANKBRANCH_CODE = ST.BRANCH_CODE \r" +
// " LEFT JOIN MASTERNUBEBRANCH NB(NOLOCK) ON NB.NUBE_BRANCH_CODE = st.NUBE_BRANCH_CODE \r" +
// " LEFT JOIN MASTERSTATE MS(NOLOCK) ON MS.STATE_CODE = BB.BANKBRANCH_STATE_CODE \r" +
// " WHERE ST.STATUS_CODE IN(1,2) " + sWhere + " \r ORDER BY NB.NUBE_BRANCH_NAME ", dtpDOB.SelectedDate);
//}
//else
//{
// Qry = string.Format(" SELECT ISNULL(NB.NUBE_BRANCH_NAME,'') AS NUBE_BRANCH_NAME,ISNULL(MB.BANK_NAME,'') AS BANK_NAME, \r" +
// " ISNULL(MB.BANK_USERCODE, '') + '_' + ISNULL(BB.BANKBRANCH_USERCODE, '') AS BANKBRANCH, \r" +
// " ISNULL(MM.SEX, '') AS SEX, ISNULL(MM.RACE_CODE, 0) AS RACE_CODE, \r" +
// " (CASE WHEN ST.STATUS_CODE=1 THEN 1.0 ELSE 2.0 END) STATUS \r" +
// " FROM NUBESTATUS..STATUS{0:MMyyyy} ST(NOLOCK)\r" +
// " LEFT JOIN MASTERMEMBER MM(NOLOCK) ON MM.MEMBER_CODE=ST.MEMBER_CODE \r" +
// " LEFT JOIN MASTERBANK MB(NOLOCK) ON MB.BANK_CODE=ST.BANK_CODE \r" +
// " LEFT JOIN MASTERBANKBRANCH BB(NOLOCK) ON BB.BANKBRANCH_CODE=ST.BRANCH_CODE \r" +
// " LEFT JOIN MASTERNUBEBRANCH NB(NOLOCK) ON NB.NUBE_BRANCH_CODE=ST.NUBE_BRANCH_CODE \r" +
// " LEFT JOIN MASTERSTATE MS(NOLOCK) ON MS.STATE_CODE = BB.BANKBRANCH_STATE_CODE \r" +
// " WHERE ST.STATUS_CODE IN(1,2) " + sWhere + " \r ORDER BY NB.NUBE_BRANCH_NAME ", dtpDOB.SelectedDate);
//}
SqlCommand cmd = new SqlCommand(Qry, con);
SqlDataAdapter sdp = new SqlDataAdapter(cmd);
sdp.SelectCommand.CommandTimeout = 0;
sdp.Fill(dt);
}
if (dt.Rows.Count > 0)
{
var datas = (from DataRow row in dt.Rows
select new
{
Bank_Name = (string)(row["BANK_NAME"] ?? ""),
Nube_branch_code = (string)(row["NUBE_BRANCH_NAME"] ?? ""),
Branch_Code = (string)(row["BANKBRANCH"] ?? ""),
SEX = (string)(row["SEX"] ?? ""),
RACE_CODE = (decimal)(row["RACE_CODE"]),
MEMBERTYPE_CODE = (decimal)(row["STATUS"])
}).ToList();
var BankBranchDatas1 = datas.GroupBy(x => x.Branch_Code).ToList();
var BankBranchdatas2 = BankBranchDatas1.Select(x => new branchStatistics
{
BranchCode = x.Key.ToString(),
CMM = x.Where(y => y.SEX == "Male" && y.RACE_CODE == 1 && y.MEMBERTYPE_CODE == 1).Count(),
CMI = x.Where(y => y.SEX == "Male" && y.RACE_CODE == 2 && y.MEMBERTYPE_CODE == 1).Count(),
CMC = x.Where(y => y.SEX == "Male" && y.RACE_CODE == 3 && y.MEMBERTYPE_CODE == 1).Count(),
CMO = x.Where(y => y.SEX == "Male" && y.RACE_CODE == 4 && y.MEMBERTYPE_CODE == 1).Count(),
CFM = x.Where(y => y.SEX == "Female" && y.RACE_CODE == 1 && y.MEMBERTYPE_CODE == 1).Count(),
CFI = x.Where(y => y.SEX == "Female" && y.RACE_CODE == 2 && y.MEMBERTYPE_CODE == 1).Count(),
CFC = x.Where(y => y.SEX == "Female" && y.RACE_CODE == 3 && y.MEMBERTYPE_CODE == 1).Count(),
CFO = x.Where(y => y.SEX == "Female" && y.RACE_CODE == 4 && y.MEMBERTYPE_CODE == 1).Count(),
NFM = x.Where(y => y.SEX == "Female" && y.RACE_CODE == 1 && y.MEMBERTYPE_CODE == 2).Count(),
NFI = x.Where(y => y.SEX == "Female" && y.RACE_CODE == 2 && y.MEMBERTYPE_CODE == 2).Count(),
NFC = x.Where(y => y.SEX == "Female" && y.RACE_CODE == 3 && y.MEMBERTYPE_CODE == 2).Count(),
NFO = x.Where(y => y.SEX == "Female" && y.RACE_CODE == 4 && y.MEMBERTYPE_CODE == 2).Count(),
NMM = x.Where(y => y.SEX == "Male" && y.RACE_CODE == 1 && y.MEMBERTYPE_CODE == 2).Count(),
NMI = x.Where(y => y.SEX == "Male" && y.RACE_CODE == 2 && y.MEMBERTYPE_CODE == 2).Count(),
NMC = x.Where(y => y.SEX == "Male" && y.RACE_CODE == 3 && y.MEMBERTYPE_CODE == 2).Count(),
NMO = x.Where(y => y.SEX == "Male" && y.RACE_CODE == 4 && y.MEMBERTYPE_CODE == 2).Count()
});
var BankBranchDatas3 = BankBranchdatas2.Select(x => new branchStatistics
{
BranchCode = x.BranchCode,
CMM = x.CMM,
CMI = x.CMI,
CMC = x.CMC,
CMO = x.CMO,
CMSTOT = x.CMM + x.CMI + x.CMC + x.CMO,
CFC = x.CFC,
CFI = x.CFI,
CFM = x.CFM,
CFO = x.CFO,
CFSTOT = x.CFO + x.CFM + x.CFI + x.CFC,
CTOTAL = x.CMM + x.CMI + x.CMC + x.CMO + x.CFO + x.CFM + x.CFI + x.CFC,
NMM = x.NMM,
NMI = x.NMI,
NMO = x.NMO,
NMC = x.NMC,
NMSTOT = x.NMM + x.NMI + x.NMO + x.NMC,
NFC = x.NFC,
NFM = x.NFM,
NFI = x.NFI,
NFO = x.NFO,
NFSTOT = x.NFC + x.NFM + x.NFI + x.NFO,
NTOTAL = x.NMM + x.NMI + x.NMO + x.NMC + x.NFC + x.NFM + x.NFI + x.NFO,
GTOTAL = x.CMM + x.CMI + x.CMC + x.CMO + x.CFO + x.CFM + x.CFI + x.CFC + x.NMM + x.NMI + x.NMO + x.NMC + x.NFC + x.NFM + x.NFI + x.NFO
}).ToList();
dgvBankStatistics.ItemsSource = BankBranchDatas3;
dtEXBANK = AppLib.LINQResultToDataTable(BankBranchDatas3);
MemberReport.Reset();
if (dtEXBANK.Rows.Count > 0)
{
ReportDataSource masterData = new ReportDataSource("BANKBRANCHSTATISTICAL", dtEXBANK);
MemberReport.LocalReport.DataSources.Add(masterData);
MemberReport.LocalReport.ReportEmbeddedResource = "Nube.Reports.rptBranchStaticsReport.rdlc";
ReportParameter[] NB = new ReportParameter[2];
NB[0] = new ReportParameter("Month", string.Format("{0:MMM - yyyy}", dtpDOB.SelectedDate));
if (!string.IsNullOrEmpty(cmbBank.Text))
{
NB[1] = new ReportParameter("ReportName", "NUBE BRANCH - " + cmbBank.Text.ToString());
}
else
{
NB[1] = new ReportParameter("ReportName", "OVER ALL BANK BRANCH");
}
MemberReport.LocalReport.SetParameters(NB);
MemberReport.RefreshReport();
//using (SqlConnection con = new SqlConnection(AppLib.connStr))
//{
// SqlCommand cmd;
// string str = "";
// if (!string.IsNullOrEmpty(cmbBank.Text))
// {
// str = "";
// }
// else
// {
// }
// cmd = new SqlCommand("SELECT ST.MEMBERTYPE_NAME,COUNT(*) TOTAL \r" +
// " FROM TEMPVIEWMASTERMEMBER ST(NOLOCK)\r" +
// " WHERE ST.MEMBERSTATUSCODE IN(1,2) AND ST.DATEOFJOINING < '2017-03-01' AND--ST.NUBEBANCHCODE = 11\r" +
// " GROUP BY ST.MEMBERTYPE_NAME\r" +
// " ORDER BY ST.MEMBERTYPE_NAME", con);
//}
}
//dgvBranch
var NubeBranchDatas1 = datas.GroupBy(x => x.Nube_branch_code).ToList();
var NubeBranchdatas2 = NubeBranchDatas1.Select(x => new branchStatistics
{
BranchCode = x.Key.ToString(),
CMM = x.Where(y => y.SEX == "Male" && y.RACE_CODE == 1 && y.MEMBERTYPE_CODE == 1).Count(),
CMI = x.Where(y => y.SEX == "Male" && y.RACE_CODE == 2 && y.MEMBERTYPE_CODE == 1).Count(),
CMC = x.Where(y => y.SEX == "Male" && y.RACE_CODE == 3 && y.MEMBERTYPE_CODE == 1).Count(),
CMO = x.Where(y => y.SEX == "Male" && y.RACE_CODE == 4 && y.MEMBERTYPE_CODE == 1).Count(),
CFM = x.Where(y => y.SEX == "Female" && y.RACE_CODE == 1 && y.MEMBERTYPE_CODE == 1).Count(),
CFI = x.Where(y => y.SEX == "Female" && y.RACE_CODE == 2 && y.MEMBERTYPE_CODE == 1).Count(),
CFC = x.Where(y => y.SEX == "Female" && y.RACE_CODE == 3 && y.MEMBERTYPE_CODE == 1).Count(),
CFO = x.Where(y => y.SEX == "Female" && y.RACE_CODE == 4 && y.MEMBERTYPE_CODE == 1).Count(),
NFM = x.Where(y => y.SEX == "Female" && y.RACE_CODE == 1 && y.MEMBERTYPE_CODE == 2).Count(),
NFI = x.Where(y => y.SEX == "Female" && y.RACE_CODE == 2 && y.MEMBERTYPE_CODE == 2).Count(),
NFC = x.Where(y => y.SEX == "Female" && y.RACE_CODE == 3 && y.MEMBERTYPE_CODE == 2).Count(),
NFO = x.Where(y => y.SEX == "Female" && y.RACE_CODE == 4 && y.MEMBERTYPE_CODE == 2).Count(),
NMM = x.Where(y => y.SEX == "Male" && y.RACE_CODE == 1 && y.MEMBERTYPE_CODE == 2).Count(),
NMI = x.Where(y => y.SEX == "Male" && y.RACE_CODE == 2 && y.MEMBERTYPE_CODE == 2).Count(),
NMC = x.Where(y => y.SEX == "Male" && y.RACE_CODE == 3 && y.MEMBERTYPE_CODE == 2).Count(),
NMO = x.Where(y => y.SEX == "Male" && y.RACE_CODE == 4 && y.MEMBERTYPE_CODE == 2).Count()
});
var NubeBranchDatas3 = NubeBranchdatas2.Select(x => new branchStatistics
{
BranchCode = x.BranchCode,
CMM = x.CMM,
CMI = x.CMI,
CMC = x.CMC,
CMO = x.CMO,
CMSTOT = x.CMM + x.CMI + x.CMC + x.CMO,
CFC = x.CFC,
CFI = x.CFI,
CFM = x.CFM,
CFO = x.CFO,
CFSTOT = x.CFO + x.CFM + x.CFI + x.CFC,
CTOTAL = x.CMM + x.CMI + x.CMC + x.CMO + x.CFO + x.CFM + x.CFI + x.CFC,
NMM = x.NMM,
NMI = x.NMI,
NMO = x.NMO,
NMC = x.NMC,
NMSTOT = x.NMM + x.NMI + x.NMO + x.NMC,
NFC = x.NFC,
NFM = x.NFM,
NFI = x.NFI,
NFO = x.NFO,
NFSTOT = x.NFC + x.NFM + x.NFI + x.NFO,
NTOTAL = x.NMM + x.NMI + x.NMO + x.NMC + x.NFC + x.NFM + x.NFI + x.NFO,
GTOTAL = x.CMM + x.CMI + x.CMC + x.CMO + x.CFO + x.CFM + x.CFI + x.CFC + x.NMM + x.NMI + x.NMO + x.NMC + x.NFC + x.NFM + x.NFI + x.NFO
}).ToList();
dgvNubeStatistics.ItemsSource = NubeBranchDatas3;
dtEXNUBE = AppLib.LINQResultToDataTable(NubeBranchDatas3);
MemberReport2.Reset();
if (dtEXNUBE.Rows.Count > 0)
{
ReportDataSource masterData = new ReportDataSource("NUBEBRANCHSTATISTICAL", dtEXNUBE);
MemberReport2.LocalReport.DataSources.Add(masterData);
MemberReport2.LocalReport.ReportEmbeddedResource = "Nube.Reports.rptBranchStaticsReportNubeBranch.rdlc";
ReportParameter[] NB2 = new ReportParameter[2];
NB2[0] = new ReportParameter("Month", string.Format("{0:MMM - yyyy}", dtpDOB.SelectedDate));
if (!string.IsNullOrEmpty(cmbBank.Text))
{
NB2[1] = new ReportParameter("ReportName", "NUBE BRANCH - " + cmbBank.Text.ToString());
}
else
{
NB2[1] = new ReportParameter("ReportName", "OVER ALL - NUBE BRANCH");
}
MemberReport2.LocalReport.SetParameters(NB2);
MemberReport2.RefreshReport();
}
//Bank
var BankDatas1 = datas.GroupBy(x => x.Bank_Name).ToList();
var BankDatas2 = BankDatas1.Select(x => new branchStatistics
{
Bank = x.Key.ToString(),
CMM = x.Where(y => y.SEX == "Male" && y.MEMBERTYPE_CODE == 1).Count(),
CFM = x.Where(y => y.SEX == "Female" && y.MEMBERTYPE_CODE == 1).Count(),
NFM = x.Where(y => y.SEX == "Female" && y.MEMBERTYPE_CODE == 2).Count(),
NMM = x.Where(y => y.SEX == "Male" && y.MEMBERTYPE_CODE == 2).Count(),
});
var BankDatas3 = BankDatas2.Select(x => new branchStatistics
{
Bank = x.Bank,
CMM = x.CMM,
CFM = x.CFM,
NMM = x.NMM,
NFM = x.NFM,
GTOTAL = x.CMM + x.CFM + x.NMM + x.NFM
}).ToList();
dgvBankList.ItemsSource = BankDatas3;
}
else
{
MessageBox.Show("No Records Found!");
}
//}
//else
//{
// MessageBox.Show("No Records Found!");
//}
}
private void Button_Click_2(object sender, RoutedEventArgs e)
{
MemberReport win = new MemberReport();
win.ShowDialog();
}
