//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); } }
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); } } }
//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); } }
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); } }
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!"); } } }
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!"); //} }