private void btView_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 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 nab_batch_mics a,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 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 nab_batch_mics a,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;
}
}
public virtual CrystalDecisions.CrystalReports.Engine.ReportDocument CreateReport() {
Rpt_IBPS_Sumary rpt = new Rpt_IBPS_Sumary();
rpt.Site = this.Site;
return rpt;
}