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