public virtual CrystalDecisions.CrystalReports.Engine.ReportDocument CreateReport()
{
Rpt_IBPS_Sumary rpt = new Rpt_IBPS_Sumary();
rpt.Site = this.Site;
return rpt;
}
private void button1_Click(object sender, EventArgs e)
{
string sql = "", err = "";
//Get A_Avai_TTT,A_TK_TTT,Ngay_Xu_Ly
sql = " select '"+Businessbp.executedb.IBPS_Account+"' as Account, to_char(a.business_date,'dd/mm/yyyy') as Ngay_Xu_Ly,nvl(a.balance_ttt,0) as Balance ";
sql += " from "+Businessbp.executedb.owner+"NAB_PROCESS_IBPS a ";
sql += " where trunc(a.business_date)=trunc((to_date('"+dateTimePicker1.Text+"','dd/mm/yyyy'))) ";
DataTable dt_A = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show("Err: \n" + err);
return;
}
else
{
if (dt_A.Rows.Count <= 0)
{
MessageBox.Show("Không xử lý IBPS với ngày này hoặc không có dữ liệu.");
return;
}
else
{
A_TK_TTT = dt_A.Rows[0]["Account"].ToString();
Ngay_Xu_Ly = dt_A.Rows[0]["Ngay_Xu_Ly"].ToString();
//A_Avai_TTT = dt_A.Rows[0]["Balance"].ToString();
A_Avai_TTT =Double.Parse(dt_A.Rows[0]["Balance"].ToString()).ToString("##,##0.00")+" VND";
}
}
sql = ""; err = "";
//Get Xy ly thanh cong (B: B1 (Prepaid) $ B2 (Credit))
sql = " select nvl(sum(Decode(b.card_type,'0',nvl(a.transaction_amount,0),0)),0) as B1 ";
sql += " ,nvl(sum(Decode(b.card_type,'1',nvl(a.transaction_amount,0),0)),0) as B2 ";
sql += " ,nvl(sum(Decode(b.card_type,'2',nvl(a.transaction_amount,0),0)),0) as B3 ";
sql += " from " + Businessbp.executedb.owner + "nab_batch_mics a," + Businessbp.executedb.owner + "nab_mics_batchroot b ";
sql += " where a.batch_number=b.batch_number ";
// sql += " --and b.card_type='1' ";
sql += " and b.mics_type ='C' ";
sql += " and nvl(a.processing_flag,'N') in ('Y','Z','N') ";
sql += " and trim(b.function_type)='BALANCE_PRE_CRE' ";
sql += " and trunc(a.date_create)=trunc((to_date('" + dateTimePicker1.Text + "','dd/mm/yyyy'))) ";
DataTable dt_B = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show("Err: \n" + err);
return;
}
else
{
if (dt_B.Rows.Count <= 0)
{
B1_Prepaid_Success = "0 VND";
B2_Credit_Success = "0 VND";
B3_Debit_Success = "0 VND";
}
else
{
//B1_Prepaid_Success = dt_B.Rows[0]["B1"].ToString();
// B2_Credit_Success = dt_B.Rows[0]["B2"].ToString();
B1_Prepaid_Success = Double.Parse(dt_B.Rows[0]["B1"].ToString()).ToString("##,##0.00") + " VND";
B2_Credit_Success = Double.Parse(dt_B.Rows[0]["B2"].ToString()).ToString("##,##0.00") + " VND";
B3_Debit_Success = Double.Parse(dt_B.Rows[0]["B3"].ToString()).ToString("##,##0.00") + " VND";
}
}
sql = ""; err = "";
//Get Xy ly khong thanh cong (C: Reject)
sql =" select nvl(sum(nvl(c.amount,0)),0) as C ";
sql += " from " + Businessbp.executedb.owner + "nab_batch_mics_reject c ";
sql += " where 1=1 ";//=trunc(c.business_date)= trunc(to_date('"+dateTimePicker1.Text+"','dd/mm/yyyy')) ";
sql += " and nvl(c.type,'1')='1' ";//Process IBPS
sql += " and nvl(c.accounting_flag,'N')='N' ";//Process IBPS
DataTable dt_C = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show("Err: \n" + err);
return;
}
else
{
if (dt_C.Rows.Count <= 0)
{
C_Reject = "0 VND";
}
else
{
C_Reject =Double.Parse(dt_C.Rows[0]["C"].ToString()).ToString("##,##0.00") + " VND";
//C_Reject = dt_C.Rows[0]["C"].ToString();
}
}
//Get Authorize (D: Prepaid : D1 (Success) $ D2 (Fail)); (E: Credit: E1 (Success) $ E2 (Fail) )
sql = " select nvl(sum(Decode(b.card_type,'0',decode(nvl(a.processing_flag,'N'),'Y',nvl(a.transaction_amount,0),0),0)),0) as D1 ";
sql += " ,nvl(sum(Decode(b.card_type,'0',decode(nvl(a.processing_flag,'N'),'Z',nvl(a.transaction_amount,0),0),0)),0) as D2 ";
sql += " ,nvl(sum(Decode(b.card_type,'1',decode(nvl(a.processing_flag,'N'),'Y',nvl(a.transaction_amount,0),0),0)),0) as E1 ";
sql += " ,nvl(sum(Decode(b.card_type,'1',decode(nvl(a.processing_flag,'N'),'Z',nvl(a.transaction_amount,0),0),0)),0) as E2 ";
sql += " ,nvl(sum(Decode(b.card_type,'2',decode(nvl(a.processing_flag,'N'),'Y',nvl(a.transaction_amount,0),0),0)),0) as F1 ";
sql += " ,nvl(sum(Decode(b.card_type,'2',decode(nvl(a.processing_flag,'N'),'Z',nvl(a.transaction_amount,0),0),0)),0) as F2 ";
sql += " from " + Businessbp.executedb.owner + "nab_batch_mics a," + Businessbp.executedb.owner + "nab_mics_batchroot b ";
sql += " where a.batch_number=b.batch_number ";
//sql += " --and b.card_type='1' ";
sql += " and b.mics_type ='C' ";
sql += " and nvl(a.processing_flag,'N') in ('Y','Z') ";
sql += " and trim(b.function_type)='BALANCE_PRE_CRE' ";
sql += " and trunc(a.date_create)=trunc(to_date('" + dateTimePicker1.Text + "','dd/mm/yyyy')) ";
DataTable dt_DE = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show("Err: \n" + err);
return;
}
else
{
if (dt_DE.Rows.Count <= 0)
{
D1_Authorize_Pre_Success = "0 VND";
D2_Authorize_Pre_Fail = "0 VND";
E1_Authorize_Cre_Success = "0 VND";
E2_Authorize_Cre_Fail = "0 VND";
F1_Authorize_Deb_Success = "0 VND";
F2_Authorize_Deb_Fail = "0 VND";
}
else
{
//D1_Authorize_Pre_Success = dt_DE.Rows[0]["D1"].ToString();
//D2_Authorize_Pre_Fail =dt_DE.Rows[0]["D2"].ToString();
//E1_Authorize_Cre_Success = dt_DE.Rows[0]["E1"].ToString();
//E2_Authorize_Cre_Fail = dt_DE.Rows[0]["E2"].ToString();
D1_Authorize_Pre_Success =Double.Parse(dt_DE.Rows[0]["D1"].ToString()).ToString("##,##0.00") + " VND";
D2_Authorize_Pre_Fail = Double.Parse(dt_DE.Rows[0]["D2"].ToString()).ToString("##,##0.00") + " VND";
E1_Authorize_Cre_Success = Double.Parse(dt_DE.Rows[0]["E1"].ToString()).ToString("##,##0.00") + " VND";
E2_Authorize_Cre_Fail = Double.Parse(dt_DE.Rows[0]["E2"].ToString()).ToString("##,##0.00") + " VND";
F1_Authorize_Deb_Success = Double.Parse(dt_DE.Rows[0]["F1"].ToString()).ToString("##,##0.00") + " VND";
F2_Authorize_Deb_Fail = Double.Parse(dt_DE.Rows[0]["F2"].ToString()).ToString("##,##0.00") + " VND";
}
}
title = "BÁO CÁO CHUYỂN KHOẢN NGOÀI HỆ THỐNG THẺ PREPAID-CREDIT-DEBIT";
//thoiGian = "Từ ngày "+ dateTimePicker1.Text +" đến ngày "+dateTimePicker2.Text;
thoiGian = "Ngày "+dateTimePicker1.Text;
Rpt_IBPS_Sumary cry = new Rpt_IBPS_Sumary();
//cry.SetDataSource(dt);
//cry.SetParameterValue("Ngayin", DateTime.Now.ToShortDateString());
//cry.SetParameterValue("Gioin", DateTime.Now.ToLongTimeString());
cry.SetParameterValue("title", title);
cry.SetParameterValue("A_Avai_TTT", A_Avai_TTT);
cry.SetParameterValue("thoigian", thoiGian);
cry.SetParameterValue("A_TK_TTT", A_TK_TTT);
cry.SetParameterValue("Ngay_Xu_Ly", Ngay_Xu_Ly);
cry.SetParameterValue("B1_Prepaid_Success", B1_Prepaid_Success);
cry.SetParameterValue("B2_Credit_Success", B2_Credit_Success);
cry.SetParameterValue("B3_Debit_Success", B3_Debit_Success);
cry.SetParameterValue("C_Reject", C_Reject);
cry.SetParameterValue("D1_Authorize_Pre_Success", D1_Authorize_Pre_Success);
cry.SetParameterValue("D2_Authorize_Pre_Fail", D2_Authorize_Pre_Fail);
cry.SetParameterValue("E1_Authorize_Cre_Success", E1_Authorize_Cre_Success);
cry.SetParameterValue("E2_Authorize_Cre_Fail", E2_Authorize_Cre_Fail);
cry.SetParameterValue("F1_Authorize_Deb_Success", F1_Authorize_Deb_Success);
cry.SetParameterValue("F2_Authorize_Deb_Fail", F2_Authorize_Deb_Fail);
crystalReportViewer1.ReportSource = cry;
}