//Button Events
private void btnSearch_Click(object sender, RoutedEventArgs e)
{
mon = dtpDate.SelectedDate.Value.Month.ToString();
if (cmbNubeBranch.Text == "")
{
MessageBox.Show("Enter Nube Branch");
}
else
{
try
{
NewMemberReport.Reset();
DataTable dt = getData();
ReportDataSource masterData = new ReportDataSource("AnnualStatement", dt);
NewMemberReport.LocalReport.DataSources.Add(masterData);
NewMemberReport.LocalReport.ReportEmbeddedResource = "Nube.Reports.MonthlyStatement.rdlc";
// NewMemberReport.LocalReport.SubreportProcessing += new SubreportProcessingEventHandler(Details);
ReportParameter[] rp = new ReportParameter[3];
rp[0] = new ReportParameter("BranchName", cmbNubeBranch.SelectedValue.ToString());
rp[1] = new ReportParameter("Month", String.Format("{0:MMM-yyyy}", dtpDate.SelectedDate.Value));
rp[2] = new ReportParameter("Title", "New Member");
NewMemberReport.LocalReport.SetParameters(rp);
NewMemberReport.RefreshReport();
LoadResignMember();
}
catch (Exception ex)
{
ExceptionLogging.SendErrorToText(ex);
}
}
}
private void btnSearch_Click(object sender, RoutedEventArgs e)
{
//if (string.IsNullOrEmpty(cmbBranch.Text))
//{
// MessageBox.Show("Branch Name is Empty!");
// cmbBranch.Focus();
//}
//else
if (dtpDateFrom.SelectedDate == null || dtpDateTo.SelectedDate == null)
{
MessageBox.Show("Select Date Range");
dtpDateFrom.Focus();
}
else
{
try
{
NewMemberReport.Reset();
DataTable dt = getData();
if (dt.Rows.Count > 0)
{
ReportDataSource masterData = new ReportDataSource("BranchAdviceList", dt);
NewMemberReport.LocalReport.DataSources.Add(masterData);
NewMemberReport.LocalReport.ReportEmbeddedResource = "Nube.Reports.NUBEBranchAdviceList.rdlc";
ReportParameter[] rp = new ReportParameter[3];
if (!string.IsNullOrEmpty(cmbBranch.Text))
{
rp[0] = new ReportParameter("BranchName", "Branch Name : " + cmbBranch.Text);
}
else
{
rp[0] = new ReportParameter("BranchName", "");
}
rp[1] = new ReportParameter("Month", String.Format("{0:dd MMM yyyy} - {1:dd MMM yyyy}", dtpDateFrom.SelectedDate.Value, dtpDateTo.SelectedDate.Value));
if (!string.IsNullOrEmpty(cmbBranch.Text))
{
rp[2] = new ReportParameter("BranchCode", cmbBranch.SelectedValue.ToString());
}
else
{
rp[2] = new ReportParameter("BranchCode", "");
}
NewMemberReport.LocalReport.SetParameters(rp);
NewMemberReport.RefreshReport();
}
else
{
MessageBox.Show("New Member's Not Found !");
}
LoadResignMember();
}
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!");
}
}
}