private void button1_Click(object sender, EventArgs e)
{
//Viết câu truy vấn để đưa ra report
if (comboBox1.SelectedIndex + 1 == 1)
{
//Bao cao 1
frmShowReport a = new frmShowReport();
a.type = "PM_1";
a.thoiGian = "Từ " + dtFrom.Text + " đến " + dtTo.Text;
string sql = "", err = "";
sql =//
"select 'Mua hang online' as loai_gd,count(*) as so_luong_gd," +
" sum(bb.transaction_amount) as so_tien_gd,sum(bb.banknet_receive_fee) as phi_phai_tra," +
" sum(bb.bank_receive_fee) as phi_phai_thu," +
" (sum(bb.transaction_amount)+sum(bb.bank_receive_fee)) as tong," +
" decode(bb.transaction_status,'115','GIAO DICH 115',"+
" '0','GIAO DICH THANH CONG',"+
" '113','GIAO DICH 113',"+
" '215','GIAO DICH 215'," +
" '225','GIAO DICH 225'" +
") as loai_giao_dich" +
" from " + Businessbp.executedb.owner + "banknet_payment_detail bb" +
" where bb.settlement_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and bb.settlement_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" group by transaction_status" +
" order by transaction_status desc";
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
a.dt = dtt;
a.title = "BÁO CÁO BÙ TRỪ GIAO DỊCH PAYMENT GATEWAY";
a.ShowDialog();
}
else
if (comboBox1.SelectedIndex + 1 == 2)
{
//Bao cao 2
frmShowReport a = new frmShowReport();
a.type = "PM_2";
a.thoiGian = "Từ " + dtFrom.Text + " đến " + dtTo.Text;
string sql = "", err = "";
//Lay ngay gd goc cua giao dich 115 va 113
string ngaygd_goc = " select dd.transaction_local_date from " + Businessbp.executedb.owner + "autho_internet_transaction dd " +
" where dd.card_number=bb.account_number" +
" and dd.trace_audit_number=bb.trace_audit_number" +
" and dd.message_type='1200'" +
" and dd.transaction_amount=bb.transaction_amount" +
" and dd.transaction_number=bb.transaction_number" +
" and dd.code_action='00'" +
" and dd.merchant_number=bb.merchant_id" +
" and bb.settlement_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and bb.settlement_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')";
sql =//--Bao cao chi tiet cac giao dich Payment gateway
" select decode(bb.transaction_status,0,bb.transaction_local_date," +
" 115,(" + ngaygd_goc + " and bb.transaction_status='115' )," +
" 113,(" + ngaygd_goc + " and bb.transaction_status='113' )," +
" 215,(" + ngaygd_goc + " and bb.transaction_status='215' )," +
" 225,(" + ngaygd_goc + " and bb.transaction_status='225' )," +
" bb.transaction_local_date" +
" ) as ngay_gd," +
" bb.issuer_id as to_chuc_thanh_toan,cc.TENNGANHANG as ten_ngan_hang," +
" bb.account_number as so_the,bb.trace_audit_number as trace,bb.merchant_id, 'Mua hang' as loai_gd,bb.transaction_amount as so_tien_gd,'704' as loai_tien," +
" decode(bb.transaction_status,'115',-bb.bank_receive_fee,bb.bank_receive_fee) as phi," +
" decode(bb.transaction_status,'115',-bb.banknet_receive_fee,bb.banknet_receive_fee) as phi_bnk," +
" trim(bb.transaction_status) as transaction_status,bb.good_code," +
" decode(bb.transaction_status,'115','GIAO DICH 115',"+
" '0','GIAO DICH THANH CONG',"+
" '113','GIAO DICH 113',"+
" '215','GIAO DICH 215'," +
" '225','GIAO DICH 225'" +
" ) as loai_giao_dich," +
" bb.transaction_number" +
" from " + Businessbp.executedb.owner + "banknet_payment_detail bb, " + Businessbp.executedb.owner + "bnk_bank cc" +
" where trim(bb.issuer_id)=trim(cc.sodaucuathe)" +
" and bb.settlement_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and bb.settlement_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" order by bb.transaction_status asc,ngay_gd asc";
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
a.dt = dtt;
a.title = "BÁO CÁO BÙ TRỪ GIAO DỊCH PAYMENT GATEWAY";
a.ShowDialog();
}
else
if (comboBox1.SelectedIndex + 1 == 3)
{
//Bao cao 7
frmShowReport a = new frmShowReport();
a.type = "PM_3";
a.thoiGian = "Từ " + dtFrom.Text + " đến " + dtTo.Text;
string sql = "", err = "";
//sql = " select hh.ngay_gd,hh.so_luong_phai_thu,hh.so_tien_phai_thu,hh.so_luong_phai_tra,hh.so_tien_phai_tra," +
// " (case " +
// " when (hh.so_tien_phai_thu-hh.so_tien_phai_tra)>0 then hh.so_tien_phai_thu-hh.so_tien_phai_tra"+
// " else "+
// " null"+
// " end "+
// " ) as chenh_lech_phai_thu," +
// " (case " +
// " when (hh.so_tien_phai_thu-hh.so_tien_phai_tra)<0 then -(hh.so_tien_phai_thu-hh.so_tien_phai_tra)" +
// " else " +
// " null" +
// " end " +
// " ) as chenh_lech_phai_tra" +
// " from (" +
// " select " +
// " bb.settlement_date as ngay_gd," +
// " sum(decode(bb.transaction_status,'0',1,'115',0)) AS so_luong_phai_thu, " +
// " sum(decode(bb.transaction_status,'0',bb.bank_receive_fee,0)) as so_tien_phai_thu,"+
// " sum(decode(bb.transaction_status,'113',1,'0',0)) AS so_luong_phai_tra, " +
// " sum(decode(bb.transaction_status,'113',-bb.bank_receive_fee ,0)) as so_tien_phai_tra" +
// " from " + Businessbp.executedb.owner + "banknet_payment_detail bb"+
// " where bb.transaction_local_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
// " and bb.transaction_local_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
// " group by bb.settlement_date" +
// " ) hh"+
// " order by ngay_gd asc";
//===================22/08/2011====================
// Phan Thanh Dat
//Giao dich refund
//=================================================
sql = "select 'Giao dich thanh cong' as loaigd," +
" bb.settlement_date as ngay_gd," +
" sum(decode(bb.transaction_status,'0',1,'115',0)) AS so_luong_phai_thu, " +
" sum(decode(bb.transaction_status,'0',bb.bank_receive_fee,0)) as so_tien_phai_thu" +
" from " + Businessbp.executedb.owner + "banknet_payment_detail bb" +
" where bb.transaction_local_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and bb.transaction_local_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and bb.transaction_status='0' " +
" group by bb.settlement_date" +
" union all" +
" select 'Giao dich Refund' as loaigd," +
" cc.settlement_date as ngay_gd," +
" sum(decode(cc.transaction_status,'113',1,'115',0)) AS so_luong_phai_thu, " +
" sum(decode(cc.transaction_status,'113',-cc.bank_receive_fee,0)) as so_tien_phai_thu" +
" from " + Businessbp.executedb.owner + "banknet_payment_detail cc" +
" where cc.transaction_local_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and cc.transaction_local_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and cc.transaction_status='113'" +
" group by cc.settlement_date" +
" union all" +
" select 'Giao dich dao 115' as loaigd," +
" dd.settlement_date as ngay_gd," +
" sum(decode(dd.transaction_status,'115',1,0)) AS so_luong_phai_thu, " +
" sum(decode(dd.transaction_status,'115',-dd.bank_receive_fee,0)) as so_tien_phai_thu" +
" from " + Businessbp.executedb.owner + "banknet_payment_detail dd" +
" where dd.transaction_local_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and dd.transaction_local_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and dd.transaction_status='115'" +
" group by dd.settlement_date"+
" union all" +
" select 'Giao dich dao 215' as loaigd," +
" dd.settlement_date as ngay_gd," +
" sum(decode(dd.transaction_status,'215',1,0)) AS so_luong_phai_thu, " +
" sum(decode(dd.transaction_status,'215',-dd.bank_receive_fee,0)) as so_tien_phai_thu" +
" from " + Businessbp.executedb.owner + "banknet_payment_detail dd" +
" where dd.transaction_local_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and dd.transaction_local_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and dd.transaction_status='215'" +
" group by dd.settlement_date"+
" union all" +
" select 'Giao dich dao 225' as loaigd," +
" dd.settlement_date as ngay_gd," +
" sum(decode(dd.transaction_status,'225',1,0)) AS so_luong_phai_thu, " +
" sum(decode(dd.transaction_status,'225',-dd.bank_receive_fee,0)) as so_tien_phai_thu" +
" from " + Businessbp.executedb.owner + "banknet_payment_detail dd" +
" where dd.transaction_local_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and dd.transaction_local_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and dd.transaction_status='225'" +
" group by dd.settlement_date";
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
a.dt = dtt;
a.title = "BÁO CÁO TỔNG HỢP PHÍ GIAO DỊCH PAYMENT GATEWAY";
a.ShowDialog();
}
else if (comboBox1.SelectedIndex + 1 == 4)
{
//Bao cao 10
frmShowReport a = new frmShowReport();
a.type = "PM_10";
a.thoiGian = "Từ " + dtFrom.Text + " " + tbFh.Text + ":" + tbFm.Text + ":" + tbFs.Text + " đến " + dtTo.Text + " " + tbTh.Text + ":" + tbTm.Text + ":" + tbTs.Text;
string sql = "", err = "";
if (rbPayment.Checked)
{
sql = //"--co electra, ko co banknet
"select aa.card_number as account_number,aa.transaction_local_date,aa.trace_audit_number," +
" decode(aa.message_type,'1420',-aa.transaction_amount,aa.transaction_amount) as transaction_amount," +
" aa.merchant_number as merchant_id,aa.vouncher_id as good_code,aa.transaction_fee" +
" from " + Businessbp.executedb.owner + "autho_internet_transaction aa " +
" where aa.transaction_local_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and aa.transaction_local_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and aa.code_action = 00" +
" and aa.reversal_used = '0' " +
" and aa.refund_used = '0' " +
" and aa.message_type = 1200 " +
" and (trim(aa.card_number), trim(aa.trace_audit_number), aa.transaction_amount, aa.transaction_local_date,aa.transaction_number)" +
" not in ( select trim(bb.account_number), trim(bb.trace_audit_number), bb.transaction_amount, bb.transaction_local_date, bb.transaction_number" +
" from " + Businessbp.executedb.owner + "banknet_payment_detail bb " +
" where bb.transaction_local_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and bb.transaction_local_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and bb.transaction_status = 0) " +
" order by transaction_local_date asc";
}
else
{
sql =//Giao dich refund
"select aa.card_number as account_number,aa.transaction_local_date,aa.trace_audit_number," +
" decode(aa.message_type,'1420',-aa.transaction_amount,aa.transaction_amount) as transaction_amount," +
" aa.merchant_number as merchant_id,aa.vouncher_id as good_code,aa.transaction_fee" +
" from " + Businessbp.executedb.owner + "autho_internet_transaction aa " +
" where aa.transaction_local_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and aa.transaction_local_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and aa.code_action = 00" +
" and aa.reversal_used = '0' " +
" and aa.refund_used = '0' " +
" and aa.message_type = 1430 " +
" and (trim(aa.card_number), trim(aa.trace_audit_number), aa.transaction_amount, aa.transaction_local_date,aa.transaction_number)" +
" not in ( select trim(bb.account_number), trim(bb.trace_audit_number), bb.transaction_amount, bb.transaction_local_date, bb.transaction_number" +
" from " + Businessbp.executedb.owner + "banknet_payment_detail bb " +
" where bb.transaction_local_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and bb.transaction_local_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and bb.transaction_status = 115) " +
" order by transaction_local_date asc";
}
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
a.dt = dtt;
if (rbPayment.Checked)
a.title = "BÁO CÁO CHI TIẾT GIAO DỊCH SAI LỆCH PAYMENT GATEWAY - CÓ ELECTRA KHÔNG CÓ BANKNET";
else
a.title = "BÁO CÁO CHI TIẾT GIAO DỊCH REFUND PAYMENT GATEWAY - CÓ ELECTRA KHÔNG CÓ BANKNET";
a.ShowDialog();
}
else
if (comboBox1.SelectedIndex + 1 == 5)
{
//Bao cao 11
frmShowReport a = new frmShowReport();
a.type = "PM_11";
a.thoiGian = "Từ " + dtFrom.Text + " " + tbFh.Text + ":" + tbFm.Text + ":" + tbFs.Text + " đến " + dtTo.Text + " " + tbTh.Text + ":" + tbTm.Text + ":" + tbTs.Text;
string sql = "", err = "";
if (rbPayment.Checked)
sql = //"--không có electra, co banknet
"select bb.account_number,bb.transaction_local_date,bb.trace_audit_number," +
" bb.transaction_amount," +
" bb.merchant_id as merchant_id,bb.good_code,bb.bank_receive_fee as transaction_fee" +
" from " + Businessbp.executedb.owner + "banknet_payment_detail bb " +
" where bb.transaction_status= 0" +
" and bb.transaction_local_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and bb.transaction_local_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and (trim(bb.account_number), trim(bb.trace_audit_number), bb.transaction_amount, bb.transaction_local_date) not in" +
" (" +
" select distinct trim(aa.card_number), trim(aa.trace_audit_number), aa.transaction_amount, aa.transaction_local_date" +
" from " + Businessbp.executedb.owner + "autho_internet_transaction aa" +
" where aa.transaction_local_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and aa.transaction_local_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and aa.code_action=00" +
" and aa.reversal_used=0 " +
" and aa.refund_used=0" +
" and aa.message_type='1200'" +
" )" +
//" group by account_number,transaction_local_date,trace_audit_number,transaction_amount,merchant_id,good_code "+
" order by bb.transaction_local_date asc";
else
sql =
"select bb.account_number,bb.transaction_local_date,bb.trace_audit_number," +
" bb.transaction_amount," +
" bb.merchant_id as merchant_id,bb.good_code,bb.bank_receive_fee as transaction_fee" +
" from " + Businessbp.executedb.owner + "banknet_payment_detail bb " +
" where bb.transaction_status= 115" +
" and bb.transaction_local_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and bb.transaction_local_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and (trim(bb.account_number), trim(bb.trace_audit_number), bb.transaction_amount, bb.transaction_local_date) not in" +
" (" +
" select distinct trim(aa.card_number), trim(aa.trace_audit_number), aa.transaction_amount, aa.transaction_local_date" +
" from " + Businessbp.executedb.owner + "autho_internet_transaction aa" +
" where aa.transaction_local_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and aa.transaction_local_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and aa.code_action=00" +
" and aa.reversal_used=0 " +
" and aa.refund_used=0" +
" and aa.message_type='1430'" +
" )" +
//" group by account_number,transaction_local_date,trace_audit_number,transaction_amount,merchant_id,good_code "+
" order by bb.transaction_local_date asc";
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
a.dt = dtt;
if (rbPayment.Checked)
a.title = "BÁO CÁO CHI TIẾT GIAO DỊCH SAI LỆCH PAYMENT GATEWAY - KHÔNG CÓ ELECTRA CÓ BANKNET";
else
a.title = "BÁO CÁO CHI TIẾT GIAO DỊCH REFUND PAYMENT GATEWAY - KHÔNG CÓ ELECTRA CÓ BANKNET";
a.ShowDialog();
}
else
if (comboBox1.SelectedIndex + 1 == 6)
{
//Bao cao 12
frmShowReport a = new frmShowReport();
a.type = "PM_12";
a.thoiGian = "Từ " + dtFrom.Text + " " + tbFh.Text + ":" + tbFm.Text + ":" + tbFs.Text + " đến " + dtTo.Text + " " + tbTh.Text + ":" + tbTm.Text + ":" + tbTs.Text;
string sql = "", err = "";
if (rbPayment.Checked)
sql = //"--có electra, co banknet
"select bb.account_number,bb.transaction_local_date,bb.trace_audit_number," +
" bb.transaction_amount," +
" bb.merchant_id as merchant_id,bb.good_code,bb.bank_receive_fee as transaction_fee" +
" from " + Businessbp.executedb.owner + "banknet_payment_detail bb " +
" where bb.transaction_status= 0" +
" and bb.transaction_local_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and bb.transaction_local_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and (trim(bb.account_number), trim(bb.trace_audit_number), bb.transaction_amount, bb.transaction_local_date) in" +
" (" +
" select distinct trim(aa.card_number), trim(aa.trace_audit_number), aa.transaction_amount, aa.transaction_local_date" +
" from " + Businessbp.executedb.owner + "autho_internet_transaction aa" +
" where aa.transaction_local_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and aa.transaction_local_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and aa.code_action=00" +
" and aa.reversal_used=0 " +
" and aa.refund_used=0" +
" and aa.message_type='1200'" +
" )" +
//" group by account_number,transaction_local_date,trace_audit_number,transaction_amount,merchant_id,good_code "+
" order by bb.transaction_local_date asc";
else
sql =
"select bb.account_number,bb.transaction_local_date,bb.trace_audit_number," +
" bb.transaction_amount," +
" bb.merchant_id as merchant_id,bb.good_code,bb.bank_receive_fee as transaction_fee" +
" from " + Businessbp.executedb.owner + "banknet_payment_detail bb " +
" where bb.transaction_status= 115" +
" and bb.transaction_local_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and bb.transaction_local_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and (trim(bb.account_number), trim(bb.trace_audit_number), bb.transaction_amount, bb.transaction_local_date) in" +
" (" +
" select distinct trim(aa.card_number), trim(aa.trace_audit_number), aa.transaction_amount, aa.transaction_local_date" +
" from " + Businessbp.executedb.owner + "autho_internet_transaction aa" +
" where aa.transaction_local_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and aa.transaction_local_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and aa.code_action=00" +
" and aa.reversal_used=0 " +
" and aa.refund_used=0" +
" and aa.message_type='1430'" +
" )" +
//" group by account_number,transaction_local_date,trace_audit_number,transaction_amount,merchant_id,good_code "+
" order by bb.transaction_local_date asc";
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
a.dt = dtt;
if (rbPayment.Checked)
a.title = "BÁO CÁO CHI TIẾT GIAO DỊCH PAYMENT GATEWAY - CÓ ELECTRA CÓ BANKNET";
else
a.title = "BÁO CÁO CHI TIẾT GIAO DỊCH REFUN PAYMENT GATEWAY - CÓ ELECTRA CÓ BANKNET";
a.ShowDialog();
}
else if (comboBox1.SelectedIndex + 1 == 7)
{
frmShowReport a = new frmShowReport();
a.type = "PM_2";
a.thoiGian = "Từ " + dtFrom.Text + " đến " + dtTo.Text;
string sql = "", err = "";
//Lay ngay gd goc cua giao dich 115 va 113
sql = "select bb.transaction_local_date as ngay_gd," +
" bb.issuer_id as to_chuc_thanh_toan,cc.TENNGANHANG as ten_ngan_hang," +
" bb.account_number as so_the,bb.trace_audit_number as trace,bb.merchant_id, 'Mua hang' as loai_gd,bb.transaction_amount as so_tien_gd,'704' as loai_tien," +
" decode(bb.transaction_status,'115',-bb.bank_receive_fee,bb.bank_receive_fee) as phi," +
" decode(bb.transaction_status,'115',-bb.banknet_receive_fee,bb.banknet_receive_fee) as phi_bnk," +
" trim(bb.transaction_status) as transaction_status,bb.good_code," +
" decode(bb.transaction_status,'111','GIAO DICH KHONG THANH CONG TREN BANKNET','0','GIAO DICH THANH CONG') as loai_giao_dich," +
" bb.transaction_number" +
" from " + Businessbp.executedb.owner + "banknet_payment_dispute_solve bb, " + Businessbp.executedb.owner + "bnk_bank cc" +
" where bb.record_type='002'" +
" and trim(bb.issuer_id)=trim(cc.sodaucuathe)" +
" and bb.transaction_local_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and bb.transaction_local_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
// " and bb.transaction_status='111'" +
" order by bb.transaction_local_date desc";
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
a.dt = dtt;
a.title = "BÁO CÁO CHI TIẾT GIAO DỊCH BANKNET TRẢ LỜI";
a.ShowDialog();
}
}
private void button1_Click(object sender, EventArgs e)
{
//View
if (comboBox1.SelectedIndex != -1)
{
string sql = "", err = "";
DataTable dtt = new DataTable();
if (comboBox1.SelectedIndex + 1 == 1)
{
//Bao cao 1
//---------------
if (isNumber(tbSwim.Text.Trim()))
{
sql = "select 'GIAO DICH TAI CHINH' as TYPE, substr(aa.transaction_datetime,1,10) as Ngay_GD, substr(aa.transaction_datetime,12,6) as GIO_GD," +
" aa.cardnbr, to_char(aa.acctnbr) as acctnbr, to_char(bb.trace_audit_number) as trace, bb.authorization_number, bb.mcc_description as Loai_TB, bb.type_desc as LOAI_GD, decode(bb.type_id,6,-bb.billing_amt,7,-bb.billing_amt,-2,-bb.billing_amt,bb.billing_amt) as SOTIEN_GD, bb.billing_currency as LOAITIEN_GD," +
" bb.txnfee as PHI, to_char(bb.RC) as RC, substr(bb.card_acceptor_term_id,1,4) as Ma_TB, aa.merchant_desc as TEN_TB, bb.NAME_NH_PH" +
" from " + Businessbp.executedb.owner + "card_swim_bnk aa, " + Businessbp.executedb.owner + "card_bnk_detail bb" +
" where aa.swim_id = bb.swim_id" +
" and aa.record_seq = bb.record_seq" +
" and aa.swim_id = " + tbSwim.Text.Trim() +
" and aa.autho_status = 1" +
" and bb.acqid = '970428'" +
" and aa.can_swim = 'Y'" +
" and aa.status = 'Y'" +
" and aa.swim_file is not null" +
" and nvl(aa.islg,'N') = 'N'" +
" and bb.txn_type <> 'B'";
//if (cbBanknet.Checked)
// sql += " and aa.network_data = '23'"+
// " and bb.network_data = '23'";
//else
// sql += " and aa.network_data = '27'" +
// " and bb.network_data = '27'";
sql+= " union all" +
" select 'GIAO DICH PHI TAI CHINH' as TYPE, substr(cc.transaction_datetime,1,10) as Ngay_GD, substr(cc.transaction_datetime,12,6) as GIO_GD," +
" cc.card_number as cardnbr, cc.account_number as acctnbr, cc.trace_audit_number as trace, cc.authorization_number, cc.mcc_description as Loai_TB, cc.type_desc as LOAI_GD," +
" decode(cc.type_id,6,-cc.billing_amt,7,-cc.billing_amt,-2,-cc.billing_amt,cc.billing_amt) as SOTIEN_GD, cc.billing_currency as LOAITIEN_GD, cc.txnfee as PHI, to_char(cc.RC) as RC, substr(cc.card_acceptor_term_id,1,4) as Ma_TB, cc.merchant_name as TEN_TB, cc.NAME_NH_PH" +
" from " + Businessbp.executedb.owner + "card_bnk_detail cc" +
" where cc.swim_id = " + tbSwim.Text.Trim() +
" and cc.txn_type = 'B'"+
" and cc.acqid = '970428'";
//if (cbBanknet.Checked)
// sql += " and cc.network_data = '23'";
//else
// sql += " and cc.network_data = '27'";
dtt = Businessbp.executedb.getTableTCBS(sql, ref err);
if(err!="")
MessageBox.Show(err);
else
{
frmShowReport a = new frmShowReport();
a.dt = dtt;
a.swim = "Swim: "+tbSwim.Text;
a.type = "1";
if (cbBanknet.Checked)
a.title = "BÁO CÁO CHI TIẾT GD THẺ NAB BANKNETVN/SMARTLINK SỬ DỤNG TẠI POS NAB";
else
a.title = "BÁO CÁO CHI TIẾT GD THẺ NAB BANKNETVN/SMARTLINK SỬ DỤNG TẠI POS NAB";
a.ShowDialog();
}
}
}
else if (comboBox1.SelectedIndex + 1 == 2)
{
//Bao cao 2
//---------------
if (isNumber(tbSwim.Text.Trim()))
{
sql = "select 'GIAO DICH TAI CHINH' as TYPE, substr(aa.transaction_datetime,1,10) as Ngay_GD, substr(aa.transaction_datetime,12,6) as GIO_GD," +
" aa.cardnbr, to_char(aa.acctnbr) as acctnbr, to_char(aa.trace) as trace, bb.authorization_number, bb.mcc_description as Loai_TB, bb.type_desc as LOAI_GD, decode(bb.type_id,6,-bb.billing_amt,7,-bb.billing_amt,-2,-bb.billing_amt,bb.billing_amt) as SOTIEN_GD, bb.billing_currency as LOAITIEN_GD," +
" decode(bb.type_id,6,-bb.txnfee,7,-bb.txnfee,-2,-bb.txnfee,bb.txnfee) as PHI, to_char(bb.RC) as RC, substr(bb.card_acceptor_term_id,1,4) as Ma_TB, aa.merchant_desc as TEN_TB, bb.NAME_NH_PH," +
" decode(bb.type_desc, 'FEE',0,decode(bb.type_id,6,0,7,0,-2,0,bb.billing_amt)) as Tong_gd_bt," +
" decode(bb.type_id,-2,decode(bb.txn_code,'22',0,-bb.billing_amt),0) as Tong_gd_bt_Reversal," +
" decode(bb.type_id,6,0,7,0,-2,0,bb.txnfee) as Tong_gd_fee," +
" decode(bb.type_id,-2,decode(bb.txn_code,'22',-bb.billing_amt,0),0) as Tong_gd_fee_Reversal" +
" from " + Businessbp.executedb.owner + "card_swim_bnk aa, " + Businessbp.executedb.owner + "card_bnk_detail bb" +
" where aa.swim_id = bb.swim_id" +
" and aa.record_seq = bb.record_seq" +
" and aa.swim_id = " + tbSwim.Text.Trim() +
" and aa.autho_status = 1" +
" and bb.acqid <> '970428'" +
" and aa.can_swim = 'Y'" +
" and aa.status = 'Y'" +
" and aa.swim_file is not null" +
" and nvl(aa.islg,'N') = 'N'" +
" and bb.txn_type <> 'B'";
if (cbBanknet.Checked)
sql += " and aa.network_data = '23'" +
" and bb.network_data = '23'";
else
sql += " and aa.network_data = '27'" +
" and bb.network_data = '27'";
//sql += " order by to_date(substr(aa.transaction_datetime,1,10),'dd/mm/yyyy') asc";
sql += " union all" +
" select 'GIAO DICH PHI TAI CHINH' as TYPE, substr(cc.transaction_datetime,1,10) as Ngay_GD, substr(cc.transaction_datetime,12,6) as GIO_GD," +
" cc.card_number as cardnbr, cc.account_number as acctnbr, cc.trace_audit_number as trace, cc.authorization_number, cc.mcc_description as Loai_TB, cc.type_desc as LOAI_GD," +
" decode(cc.type_id,6,-cc.billing_amt,7,-cc.billing_amt,-2,-cc.billing_amt,cc.billing_amt) as SOTIEN_GD, cc.billing_currency as LOAITIEN_GD, decode(cc.type_id,6,-cc.txnfee,7,-cc.txnfee,-2,-cc.txnfee,cc.txnfee) as PHI, to_char(cc.RC) as RC, substr(cc.card_acceptor_term_id,1,4) as Ma_TB, cc.merchant_name as TEN_TB, cc.NAME_NH_PH," +
" decode(cc.type_desc, 'FEE',0,decode(cc.type_id,6,0,7,0,-2,0,cc.billing_amt)) as Tong_gd_bt," +
" decode(cc.type_id,-2,decode(cc.txn_code,'22',0,-cc.billing_amt),0) as Tong_gd_bt_Reversal," +
" decode(cc.type_id,6,0,7,0,-2,0,cc.txnfee) as Tong_gd_fee," +
" decode(cc.type_id,-2,decode(cc.txn_code,'22',-cc.billing_amt,0),0) as Tong_gd_fee_Reversal" +
" from " + Businessbp.executedb.owner + "card_bnk_detail cc" +
" where cc.swim_id = " + tbSwim.Text.Trim() +
" and cc.txn_type = 'B'" +
" and cc.acqid <> '970428'";
if (cbBanknet.Checked)
sql += " and cc.network_data = '23'";
else
sql += " and cc.network_data = '27'";
sql += " order by Ngay_GD, GIO_GD asc";
dtt = Businessbp.executedb.getTableTCBS(sql, ref err);
if (err != "")
MessageBox.Show(err);
else
{
frmShowReport a = new frmShowReport();
a.dt = dtt;
a.type = "2";
if (cbBanknet.Checked)
a.title = "BÁO CÁO CHI TIẾT GIAO DỊCH NOT ON US THEO TỔ CHỨC PHÁT HÀNH THẺ - BANKNETVN";
else
a.title = "BÁO CÁO CHI TIẾT GIAO DỊCH NOT ON US THEO TỔ CHỨC PHÁT HÀNH THẺ - SMARTLINK";
a.swim = "Swim: " + tbSwim.Text;
a.ShowDialog();
}
}
}
else if(comboBox1.SelectedIndex+1==3)
{
//Bao cao 3
//---------------
if (isNumber(tbSwim.Text.Trim()))
{
sql = "select to_char(aa.acctnbr) as acctnbr, aa.cardnbr, bb.arn, aa.transaction_datetime, decode(bb.type_id,6,-bb.billing_amt,7,-bb.billing_amt,-2,-bb.billing_amt,bb.billing_amt) as txnamt, bb.billing_currency as txn_currency, "+
" decode(bb.type_id,6,-bb.txnfee,7,-bb.txnfee,-2,-bb.txnfee,bb.txnfee) as txnfee, aa.hold_amount as holdamt," +
" bb.type_desc as txn_type, bb.authorization_number, bb.acquirer_institution_code, bb.name_nh_ph" +
" from " + Businessbp.executedb.owner + "card_swim_bnk aa, " + Businessbp.executedb.owner + "card_bnk_detail bb" +
" where aa.swim_id = bb.swim_id"+
" and aa.record_seq = bb.record_seq"+
" and aa.swim_id = "+ tbSwim.Text.Trim() +
" and aa.autho_status = 2"+
" and nvl(aa.islg,'N') = 'N'";
if (cbBanknet.Checked)
sql += " and aa.network_data = '23'" +
" and bb.network_data = '23'";
else
sql += " and aa.network_data = '27'" +
" and bb.network_data = '27'";
dtt = Businessbp.executedb.getTableTCBS(sql, ref err);
if (err != "")
MessageBox.Show(err);
else
{
frmShowReport a = new frmShowReport();
a.dt = dtt;
a.type = "3";
if (cbBanknet.Checked)
a.title = "BÁO CÁO GIAO DỊCH BỊ LỖI (UNMATCHED) - BANKNETVN";
else
a.title = "BÁO CÁO GIAO DỊCH BỊ LỖI (UNMATCHED) - SMARTLINK";
a.swim = "Swim: " + tbSwim.Text;
a.ShowDialog();
}
}
}
else if (comboBox1.SelectedIndex + 1 == 4)
{
//Bao cao 4
//---------------
if (isNumber(tbSwim.Text.Trim()))
{
sql = "select to_char(aa.acctnbr) as acctnbr, aa.cardnbr, bb.arn, aa.transaction_datetime, decode(bb.type_id,6,-bb.billing_amt,7,-bb.billing_amt,-2,-bb.billing_amt,bb.billing_amt) as txnamt, bb.txn_currency, "+
" decode(bb.type_id,6,-bb.txnfee,7,-bb.txnfee,-2,-bb.txnfee,bb.txnfee) as txnfee, aa.hold_amount as holdamt," +
" bb.type_desc as txn_type, bb.authorization_number, bb.acquirer_institution_code, bb.name_nh_ph" +
" from " + Businessbp.executedb.owner + "card_swim_bnk aa, " + Businessbp.executedb.owner + "card_bnk_detail bb" +
" where aa.swim_id = bb.swim_id" +
" and aa.record_seq = bb.record_seq" +
" and aa.swim_id = " + tbSwim.Text.Trim() +
" and aa.autho_status = 2" +
" and nvl(aa.islg,'N') = 'N'"+
" and nvl(aa.can_swim,'N') = 'Y'"+
" and aa.status = 'Y'"+
" and aa.swim_file is not null";
if (cbBanknet.Checked)
sql += " and aa.network_data = '23'" +
" and bb.network_data = '23'";
else
sql += " and aa.network_data = '27'" +
" and bb.network_data = '27'";
dtt = Businessbp.executedb.getTableTCBS(sql, ref err);
if (err != "")
MessageBox.Show(err);
else
{
frmShowReport a = new frmShowReport();
a.dt = dtt;
a.type = "4";
if (cbBanknet.Checked)
a.title = "BÁO CÁO GIAO DỊCH BỊ LỖI (UNMATCHED) ĐÃ HOLD ĐƯỢC TIỀN - BANKNETVN";
else
a.title = "BÁO CÁO GIAO DỊCH BỊ LỖI (UNMATCHED) ĐÃ HOLD ĐƯỢC TIỀN - SMARTLINK";
a.swim = "Swim: " + tbSwim.Text;
a.ShowDialog();
}
}
}
else if (comboBox1.SelectedIndex + 1 == 5)
{
//Bao cao 5
//---------------
if (isNumber(tbSwim.Text.Trim()))
{
sql = "select to_char(aa.acctnbr) as acctnbr, aa.cardnbr, bb.arn, aa.transaction_datetime, decode(bb.type_id,6,-bb.billing_amt,7,-bb.billing_amt,-2,-bb.billing_amt,bb.billing_amt) as txnamt, bb.txn_currency, " +
" decode(bb.type_id,6,-bb.txnfee,7,-bb.txnfee,-2,-bb.txnfee,bb.txnfee) as txnfee, aa.hold_amount as holdamt," +
" bb.type_desc as txn_type, bb.authorization_number, bb.acquirer_institution_code, bb.name_nh_ph" +
" from " + Businessbp.executedb.owner + "card_swim_bnk aa, " + Businessbp.executedb.owner + "card_bnk_detail bb" +
" where aa.swim_id = bb.swim_id" +
" and aa.record_seq = bb.record_seq" +
" and aa.swim_id = " + tbSwim.Text.Trim() +
" and aa.autho_status = 2" +
" and nvl(aa.islg,'N') = 'N'" +
" and nvl(aa.can_swim,'N') = 'N'" +
" and aa.swim_file is null";
if (cbBanknet.Checked)
sql += " and aa.network_data = '23'" +
" and bb.network_data = '23'";
else
sql += " and aa.network_data = '27'" +
" and bb.network_data = '27'";
dtt = Businessbp.executedb.getTableTCBS(sql, ref err);
if (err != "")
MessageBox.Show(err);
else
{
frmShowReport a = new frmShowReport();
a.dt = dtt;
a.type = "5";
if (cbBanknet.Checked)
a.title = "BÁO CÁO GIAO DỊCH BỊ LỖI (UNMATCHED) KHÔNG ĐỦ TIỀN HOLD - BANKNETVN";
else
a.title = "BÁO CÁO GIAO DỊCH BỊ LỖI (UNMATCHED) KHÔNG ĐỦ TIỀN HOLD - SMARTLINK";
a.swim = "Swim: " + tbSwim.Text;
a.ShowDialog();
}
}
}
else if (comboBox1.SelectedIndex + 1 == 6)
{
//Bao cao 6
//---------------
if (isNumber(tbSwim.Text.Trim()))
{
sql = "select 'GIAO DICH REVERSAL/CREDIT CO GIAO DICH GOC' as TYPE, aa.transaction_datetime, decode(bb.acquirer_institution_code,'970428','970416',bb.acquirer_institution_code) as acquirer_institution_code, bb.name_nh_ph, aa.cardnbr, bb.trace_audit_number as trace, bb.authorization_number," +
" bb.mcc_description as Loai_TB, bb.type_desc as LOAI_GD, aa.amt as SOTIEN_GD, bb.txn_currency as LOAITIEN_GD, bb.txnfee as PHI, to_char(bb.rc) as RC, substr(bb.card_acceptor_term_id,1,4) as Ma_TB, bb.merchant_desc as Ten_TB" +
" from " + Businessbp.executedb.owner + "card_swim_bnk aa, " + Businessbp.executedb.owner + "card_bnk_detail bb" +
" where aa.swim_id = bb.swim_id" +
" and aa.record_seq = bb.record_seq" +
" and aa.swim_id = " + tbSwim.Text.Trim() +
" and bb.txn_type in ('C','R')" +
" and nvl(aa.islg,'N') = 'N'" +
" and aa.pri_transaction_seq is not null" +
" and aa.pri_transaction_swim is not null";
if (cbBanknet.Checked)
sql += " and aa.network_data = '23'" +
" and bb.network_data = '23'";
else
sql += " and aa.network_data = '27'" +
" and bb.network_data = '27'";
sql += " union all"+
" select 'GIAO DICH REVERSAL/CREDIT KHONG CO GIAO DICH GOC' as TYPE, aa.transaction_datetime, decode(bb.acquirer_institution_code,'970428','970416',bb.acquirer_institution_code) as acquirer_institution_code, bb.name_nh_ph, aa.cardnbr, bb.trace_audit_number as trace, bb.authorization_number," +
" bb.mcc_description as Loai_TB, bb.type_desc as LOAI_GD, aa.amt as SOTIEN_GD, bb.txn_currency as LOAITIEN_GD, bb.txnfee as PHI, to_char(bb.rc) as RC, substr(bb.card_acceptor_term_id,1,4) as Ma_TB, bb.merchant_desc as Ten_TB" +
" from " + Businessbp.executedb.owner + "card_swim_bnk aa, " + Businessbp.executedb.owner + "card_bnk_detail bb" +
" where aa.swim_id = bb.swim_id"+
" and aa.record_seq = bb.record_seq"+
" and aa.swim_id = " + tbSwim.Text.Trim() +
" and bb.txn_type in ('C','R')"+
" and nvl(aa.islg,'N') = 'N'" +
" and aa.pri_transaction_seq is null"+
" and aa.pri_transaction_swim is null";
if (cbBanknet.Checked)
sql += " and aa.network_data = '23'" +
" and bb.network_data = '23'";
else
sql += " and aa.network_data = '27'" +
" and bb.network_data = '27'";
dtt = Businessbp.executedb.getTableTCBS(sql, ref err);
if (err != "")
MessageBox.Show(err);
else
{
frmShowReport a = new frmShowReport();
a.dt = dtt;
a.type = "6";
if (cbBanknet.Checked)
a.title = "BÁO CÁO GIAO DỊCH ĐẢO CREDIT/REVERSAL - BANKNETVN";
else
a.title = "BÁO CÁO GIAO DỊCH ĐẢO CREDIT/REVERSAL - SMARTLINK";
a.swim = "Swim: " + tbSwim.Text;
a.ShowDialog();
}
}
}
else if (comboBox1.SelectedIndex + 1 == 7)
{
//Bao cao 7
//---------------
if (!isNumber(tbSwim.Text.Trim()))
{
if(tbCard.Text.Replace(" ","")=="")
sql = "select aa.transaction_datetime, decode(bb.acquirer_institution_code,'970428','970416',bb.acquirer_institution_code) acquirer_institution_code, bb.name_nh_ph, bb.trace_audit_number as trace, bb.authorization_number," +
" bb.mcc_description, to_char(bb.rc) as RC, bb.card_acceptor_term_id, bb.txn_desc, bb.billing_currency as txn_currency, bb.billing_amt as txnamt, bb.txnfee, aa.amt, aa.hold_amount, to_char(aa.swim_id) as swim_id, to_char(aa.acctnbr) as acctnbr, null as cardnbr" +
" from " + Businessbp.executedb.owner + "card_swim_bnk aa, " + Businessbp.executedb.owner + "card_bnk_detail bb" +
" where aa.swim_id = bb.swim_id"+
" and aa.record_seq = bb.record_seq"+
" and aa.isrelease = 'Y'"+
" and trunc(aa.release_date) >= trim(to_date('"+dtFrom.Text.Trim()+"','dd/mm/yyyy'))"+
" and trunc(aa.release_date) <= trim(to_date('"+dtTo.Text.Trim()+"','dd/mm/yyyy'))";
else
sql = "select aa.transaction_datetime, decode(bb.acquirer_institution_code,'970428','970416',bb.acquirer_institution_code) acquirer_institution_code, bb.name_nh_ph, bb.trace_audit_number as trace, bb.authorization_number," +
" bb.mcc_description, to_char(bb.rc) as RC, bb.card_acceptor_term_id, bb.txn_desc, bb.billing_currency as txn_currency, bb.billing_amt as txnamt, bb.txnfee, aa.amt, aa.hold_amount, to_char(aa.swim_id) as swim_id, to_char(aa.acctnbr) as acctnbr, null as cardnbr" +
" from " + Businessbp.executedb.owner + "card_swim_bnk aa, " + Businessbp.executedb.owner + "card_bnk_detail bb" +
" where aa.swim_id = bb.swim_id" +
" and aa.record_seq = bb.record_seq" +
" and aa.isrelease = 'Y'" +
" and aa.cardnbr = '" + tbCard.Text.Replace(" ", "") + "'" +
" and trunc(aa.release_date) >= trim(to_date('" + dtFrom.Text.Trim() + "','dd/mm/yyyy'))" +
" and trunc(aa.release_date) <= trim(to_date('" + dtTo.Text.Trim() + "','dd/mm/yyyy'))";
//if (cbBanknet.Checked)
// sql += " and aa.network_data = '23'" +
// " and bb.network_data = '23'";
//else
// sql += " and aa.network_data = '27'" +
// " and bb.network_data = '27'";
dtt = Businessbp.executedb.getTableTCBS(sql, ref err);
if (err != "")
MessageBox.Show(err);
else
{
frmShowReport a = new frmShowReport();
a.dt = dtt;
a.type = "7";
a.thoiGian = "Từ "+dtFrom.Text.Trim()+" đến "+dtTo.Text.Trim();
a.soThe = "Số thẻ: "+tbCard.Text.Replace(" ","");
a.ShowDialog();
}
}
}
else if (comboBox1.SelectedIndex + 1 == 8)
{
//Bao cao 8
//---------------
tbSwim.Text = "";
if (true)
{
sql = "select bb.transaction_datetime, bb.acquirer_institution_code, bb.name_nh_ph, bb.trace_audit_number as trace, bb.authorization_number," +
" bb.mcc_description, to_char(bb.rc) as RC, bb.card_acceptor_term_id, bb.type_desc as txn_desc, bb.billing_currency as txn_currency, bb.billing_amt as txnamt, bb.txnfee, aa.amt, aa.hold_amount, to_char(aa.swim_id) as swim_id, to_char(aa.acctnbr) as acctnbr, aa.cardnbr" +
" from " + Businessbp.executedb.owner + "card_swimrepeat_bnk aa, " + Businessbp.executedb.owner + "card_bnk_detail bb" +
" where aa.swim_id = bb.swim_id"+
" and aa.record_seq = bb.record_seq"+
" and nvl(aa.islg,'N') = 'N'" +
" and trunc(aa.datelastmaint) >= trim(to_date('" + dtFrom.Text.Trim() + "','dd/mm/yyyy'))" +
" and trunc(aa.datelastmaint) <= trim(to_date('" + dtTo.Text.Trim() + "','dd/mm/yyyy'))";
//if (cbBanknet.Checked)
// sql += " and aa.network_data = '23'" +
// " and bb.network_data = '23'";
//else
// sql += " and aa.network_data = '27'" +
// " and bb.network_data = '27'";
dtt = Businessbp.executedb.getTableTCBS(sql, ref err);
if (err != "")
MessageBox.Show(err);
else
{
frmShowReport a = new frmShowReport();
a.dt = dtt;
a.type = "8";
a.thoiGian = "Từ " + dtFrom.Text.Trim() + " đến " + dtTo.Text.Trim();
a.ShowDialog();
}
}
}
else if (comboBox1.SelectedIndex + 1 == 9)
{
//Bao cao 9
//---------------
tbSwim.Text = "";
if (true)
{
sql = "select aa.transaction_datetime, bb.acquirer_institution_code, bb.name_nh_ph, bb.trace_audit_number as trace, bb.authorization_number,"+
" bb.mcc_description, to_char(bb.rc) as RC, bb.card_acceptor_term_id, bb.type_desc as txn_desc, bb.txn_currency, bb.txnamt, " +
" bb.txnfee, aa.amt, aa.hold_amount, to_char(aa.swim_id) as swim_id, to_char(aa.acctnbr) as acctnbr, aa.cardnbr"+
" from " + Businessbp.executedb.owner + "card_swim_bnk aa, " + Businessbp.executedb.owner + "card_bnk_detail bb" +
" where aa.swim_id = bb.swim_id"+
" and aa.record_seq = bb.record_seq"+
" and nvl(aa.islg,'N') = 'N'"+
" and aa.status = 'N'"+
" and nvl(aa.can_swim,'N') = 'N'"+
" and aa.swim_file is null"+
" and nvl(aa.manual_remove,'N') = 'Y'"+
" and trunc(aa.manual_remove_date) >= trunc(to_date('"+dtFrom.Text.Trim()+"','dd/mm/yyyy'))"+
" and trunc(aa.manual_remove_date) <= trunc(to_date('"+dtTo.Text.Trim()+"','dd/mm/yyyy'))";
if (cbBanknet.Checked)
sql += " and aa.network_data = '23'" +
" and bb.network_data = '23'";
else
sql += " and aa.network_data = '27'" +
" and bb.network_data = '27'";
dtt = Businessbp.executedb.getTableTCBS(sql, ref err);
if (err != "")
MessageBox.Show(err);
else
{
frmShowReport a = new frmShowReport();
a.dt = dtt;
a.type = "9";
a.thoiGian = "Từ " + dtFrom.Text.Trim() + " đến " + dtTo.Text.Trim();
a.ShowDialog();
}
}
}
else if (comboBox1.SelectedIndex + 1 == 10)
{
//Bao cao 10
//---------------
tbSwim.Text = "";
if (true)
{
err = "";
string tk = "";
sql = "select kk.acctnbr"+
" from " + Businessbp.executedb.owner + "debit_card_account kk" +
" where kk.cardnbr = '" + tbCard.Text+ "'";
DataTable dtt_tk = Businessbp.executedb.getTableTCBS(sql, ref err);
if (err == "")
{
if (dtt_tk.Rows.Count > 0)
tk = "Tài khoản: " + dtt_tk.Rows[0][0].ToString();
}
else
MessageBox.Show(err);
err = "";
sql = //"--Hold chua settlement
"select 'GIAO DICH HOLD CHUA SETTLEMENT' as type, aa.localdatetime, substr(aa.in_message, instr(aa.in_message,'[32]')+5, 6) as TCTT, "+
" aa.tracenbr, substr(aa.in_message, instr(aa.in_message,'[124]')+6, 3) as LoaiTB, aa.terminalid, aa.refnbr, "+
" substr(aa.in_message, instr(aa.in_message,'[49]')+5, 3) as curr, aa.tranamount, aa.feeamount, "+
" bb.rtxn_desc as txn_desc, null as Swim_id, NULL release_trichtien_date" +
" from " + Businessbp.executedb.owner + "card_transaction aa, " + Businessbp.executedb.owner + "vd_rtxn_typ bb" +
" where aa.transactiontypcd = bb.rtxn_typ"+
" and aa.cardnbr = '"+tbCard.Text.Replace(" ","")+"'"+
" and aa.currstatuscd = 'BOK'"+
" and trunc(to_date(substr(aa.localdatetime,1,6),'yymmdd')) >= trunc(to_date('"+dtFrom.Text.Trim()+"','dd/mm/yyyy'))"+
" and trunc(to_date(substr(aa.localdatetime,1,6),'yymmdd')) <= trunc(to_date('"+dtTo.Text.Trim()+"','dd/mm/yyyy'))"+
" union all"+
//--Da release tren 30 ngay
" select 'GIAO DICH DA RELEASE' AS TYPE, hh.localdatetime, hh.tctt, hh.tracenbr, hh.loaitb as LoaiTB, hh.terminalid, hh.refnbr,"+
" hh.curr, hh.tranamount, hh.feeamount, hh.rtxn_desc as txn_desc, null as Swim_id, hh.date_create as release_trichtien_date" +
" from " + Businessbp.executedb.owner + "card_bnk_release30days hh" +
" where hh.cardnbr = '"+tbCard.Text.Replace(" ","")+"'"+
" and hh.RELEASE_TYPE = '1'"+
" and trunc(date_create) >= trunc(to_date('"+dtFrom.Text.Trim()+"','dd/mm/yyyy'))"+
" and trunc(date_create) <= trunc(to_date('"+dtTo.Text.Trim()+"','dd/mm/yyyy'))"+
" union all"+
//--Da trich tien
" select 'GIAO DICH DA TRICH DU TIEN' AS TYPE, kk.transaction_datetime as localdatetime, pp.acquirer_institution_code as TCTT,"+
" pp.trace_audit_number as tracenbr, pp.mcc_description as LoaiTB, pp.terminal_id as terminalid, pp.autho_reference_number as refnbr,"+
//" pp.txn_currency as curr,
" 'VND' as curr," +
" kk.amt as tranamount, pp.txnfee as feeamount, pp.type_desc as txn_desc, to_char(kk.swim_id) as swim_id, kk.datelastmaint as release_trichtien_date" +
" from " + Businessbp.executedb.owner + "card_swim_bnk kk, " + Businessbp.executedb.owner + "card_bnk_detail pp" +
" where kk.swim_id = pp.swim_id"+
" and kk.record_seq = pp.record_seq"+
" and kk.cardnbr = '"+tbCard.Text.Replace(" ","")+"'"+
" and kk.status = 'Y'"+
" and kk.can_swim = 'Y'"+
" and kk.swim_file is not null"+
" and nvl(kk.islg,'N') = 'N'"+
" and trunc(kk.datelastmaint) >= trunc(to_date('"+dtFrom.Text.Trim()+"','dd/mm/yyyy'))"+
" and trunc(kk.datelastmaint) <= trunc(to_date('"+dtTo.Text.Trim()+"','dd/mm/yyyy'))"+
" union all"+
" select 'GIAO DICH DA TRICH DU TIEN' AS TYPE, pp.transaction_datetime as localdatetime, pp.acquirer_institution_code as TCTT,"+
" pp.trace_audit_number as tracenbr, pp.mcc_description as LoaiTB, pp.terminal_id as terminalid, pp.autho_reference_number as refnbr,"+
//" pp.txn_currency as curr
" 'VND' as curr," +
" ww.amt as tranamount, pp.txnfee as feeamount, pp.type_desc as txn_desc, to_char(ww.swim_id)||'/'||to_char(ww.batch_id) as swim_id, ww.datelastmaint as release_trichtien_date" +
" from " + Businessbp.executedb.owner + "card_swimrepeat_bnk ww, " + Businessbp.executedb.owner + "card_bnk_detail pp" +
" where ww.swim_id = pp.swim_id"+
" and ww.record_seq = pp.record_seq"+
" and ww.cardnbr = '"+tbCard.Text.Replace(" ","")+"'"+
" and nvl(ww.islg,'N') = 'N'"+
" and trunc(ww.datelastmaint) >= trunc(to_date('"+dtFrom.Text.Trim()+"','dd/mm/yyyy'))"+
" and trunc(ww.datelastmaint) <= trunc(to_date('"+dtTo.Text.Trim()+"','dd/mm/yyyy'))";
dtt = Businessbp.executedb.getTableTCBS(sql, ref err);
if (err != "")
MessageBox.Show(err);
else
{
frmShowReport a = new frmShowReport();
a.dt = dtt;
a.type = "10";
a.soThe = "Số thẻ: " + tbCard.Text;
a.soTK = tk;
a.thoiGian = "Từ " + dtFrom.Text.Trim() + " đến " + dtTo.Text.Trim();
a.ShowDialog();
}
}
}
else if (comboBox1.SelectedIndex + 1 == 11)
{
//BC 11
FileStream ft = new FileStream(@"C:\reportSum.txt", FileMode.Open, FileAccess.Read);
StreamReader trr = new StreamReader(ft);
string text = "";
sql = "";
while ((text = trr.ReadLine()) != null)
{
sql += " "+text;
}
trr.Close();
ft.Close();
sql = sql.Replace("xxx",tbSwim.Text.Trim());
dtt = Businessbp.executedb.getTableTCBS(sql, ref err);
if (err != "")
MessageBox.Show(err);
else
{
frmShowReport a = new frmShowReport();
a.dt = dtt;
a.type = "11";
a.swim = tbSwim.Text;
a.ShowDialog();
}
}
}
}
private void button6_Click(object sender, EventArgs e)
{
//Print exception
string sql = "", err="";
sql = "select 1 as TYPE, substr(bn.transaction_datetime,1,10) as Ngay_GD, substr(bn.transaction_datetime,12,6) as GIO_GD,"+
" bn.card_number as cardnbr, bn.account_number as acctnbr, bn.trace_audit_number as trace, bn.authorization_number as authorization_number,"+
" mcc_description as Loai_TB, bn.type_desc as LOAI_GD, decode(bn.type_id,6,-bn.billing_amt,7,-bn.billing_amt,-2,-bn.billing_amt,bn.billing_amt) as SOTIEN_GD, bn.billing_currency as LOAITIEN_GD,"+
" bn.txnfee as PHI, to_char(bn.RC) as RC, substr(bn.card_acceptor_term_id,1,4) as Ma_TB, bn.merchant_desc as TEN_TB, bn.NAME_NH_PH " +
" from " + Businessbp.executedb.owner + "card_bnk_detail bn" +
" where bn.swim_id = " + tbSwim.Text.Trim() +
" and (bn.status = 'H' or autho_status = 3)";
DataTable dtt = Businessbp.executedb.getTableTCBS(sql, ref err);
if (err != "")
MessageBox.Show(err);
else
{
frmShowReport a = new frmShowReport();
a.dt = dtt;
a.swim = "Swim file: "+tbSwim.Text;
a.type = "ShowException";
a.ShowDialog();
}
}
private void button5_Click(object sender, EventArgs e)
{
//Print
if (isNumber(tbSwim.Text.Trim()))
{
string sql = "", err = "";
sql = "SELECT aa.rtxntypcd, to_char(aa.acctnbr) as acctnbr, aa.amt, aa.txndesc," +
" decode(aa.rtxntypcd,'WTHD',aa.amt,0) as st_no," +
" decode(aa.rtxntypcd,'DEPD',aa.amt,0) as st_co," +
" decode(aa.rtxntypcd,'WTHD',1,0) as record_no," +
" decode(aa.rtxntypcd,'DEPD',1,0) as record_co," +
" decode(aa.network_data,'23','GIAO DICH TAI BANKNET','GIAO DICH TAI SMARTLINK') as LoaiThe,"+
" aa.swim_id"+
" FROM " + Businessbp.executedb.owner + "card_swim_bnk aa" +
" WHERE SWIM_ID = " + tbSwim.Text.Trim() +
" and swim_file is not null" +
" and can_swim = 'Y'" +
" and amt > 0" +
" order by record_seq, decode(islg,'Y','1','2') desc";
DataTable dtt = Businessbp.executedb.getTableTCBS(sql, ref err);
if (err != "")
MessageBox.Show(err);
else
{
frmShowReport a = new frmShowReport();
a.dt = dtt;
a.type = "Swim";
a.title = "Swim number: "+tbSwim.Text;
a.ShowDialog();
}
}
}
private void button3_Click(object sender, EventArgs e)
{
string sql = "", err = "";
sql = "select a.aut_terminal, b.orgnbr, a.account_type, a.account_branch, a.account_number, a.account_amount"+
" from " + Businessbp.executedb.owner + "nab_atm_acquier a, " + Businessbp.executedb.owner + "card_centercode b"+
" where a.aut_terminal = b.aut_terminal"+
" and a.ref_no = '" + dtFrom.Text + tbFh.Text + tbFm.Text + tbFs.Text + "_" + dtTo.Text + tbTh.Text + tbTm.Text + tbTs.Text + "'"+
" union all"+
" select '' as aut_terminal, '' as orgnbr, 'N' as account_type, '000' as account_branch, '000000000100030' as account_number, sum(a.account_amount)" +
" from " + Businessbp.executedb.owner + "nab_atm_acquier a"+
" where a.ref_no = '" + dtFrom.Text + tbFh.Text + tbFm.Text + tbFs.Text + "_" + dtTo.Text + tbTh.Text + tbTm.Text + tbTs.Text + "'";
DataTable dt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
MessageBox.Show(err);
else
{
if (dt.Rows.Count <= 0)
MessageBox.Show("No records to print");
else
{
frmShowReport a = new frmShowReport();
a.type = "ATM_ACQUIER";
a.dt = dt;
a.title = "Từ "+dtFrom.Text.ToString()+" "+tbFh.Text+":"+tbFm.Text+":"+tbFs.Text+" Đến "+dtTo.Text.ToString()+" "+tbTh.Text+":"+tbTm.Text+":"+tbTs.Text;
a.ShowDialog();
}
}
}
private void button1_Click(object sender, EventArgs e)
{
#region R1
if (comboBox1.SelectedIndex + 1 == 1)
{
//Bao cao 1
frmShowReport a = new frmShowReport();
a.type = "DC_1";
string where = " and to_date(decode(substr(aa.file_name,1,1),'I',substr(aa.file_name,2,6)," +
" decode(substr(aa.file_name,1,1),'O',substr(aa.file_name,2,6)," +
" (substr(aa.file_name,1,6)))),'mmddyy') " +
" >= to_date('" + dtFrom.Text.Trim() + "','dd/mm/yyyy')" +
" and to_date(decode(substr(aa.file_name,1,1),'I',substr(aa.file_name,2,6)," +
" decode(substr(aa.file_name,1,1),'O',substr(aa.file_name,2,6)," +
" (substr(aa.file_name,1,6)))),'mmddyy') " +
" <= to_date('" + dtTo.Text.Trim() + "','dd/mm/yyyy')";
//" and to_date(substr(aa.file_name,1,6),'mmddyy') >= to_date('" + dtFrom.Text.Trim() + "','dd/mm/yyyy')"+
//" and to_date(substr(aa.file_name,1,6),'mmddyy') <= to_date('" + dtTo.Text.Trim() + "','dd/mm/yyyy') ";
a.thoiGian = "Từ " + dtFrom.Text+" đến " + dtTo.Text;
string sql = "", err = "";
if (cbBanknet.Checked)
sql = "select distinct 'ISSUER (NAB dong vai tro ISS, chi bao gom giao dich chu the NAB su dung tai dai ly ngoai he thong NAB - GD not onus)' as ISS_ACQ, " +
" decode(substr(aa.processing_code,1,2),'30','GIAO DICH PHI TAI CHINH','35','GIAO DICH PHI TAI CHINH','GIAO DICH TAI CHINH') as TC_PTC, " +
" decode(substr(aa.processing_code,1,2),'01',decode(aa.transaction_status,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.transaction_status,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.transaction_status,0,'MUA HANG',110,'DAO MUA HANG BO SUNG','DAO MUA HANG'),'30',decode(aa.transaction_status,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.transaction_status,0,'SAO KE','DAO SAO KE')) AS TYPE," +
" COUNT(*) AS SL_GD, " +
//Dat sua 23/09/2011
//Noi dung sua: giao dich dao 113
//Noi dung cu--" SUM(DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'01',decode(aa.transaction_status,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.transaction_status,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.transaction_status,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.transaction_status,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.transaction_status,0,'SAO KE','DAO SAO KE')),1,3),'DAO', -AA.TRANSACTION_AMOUNT, AA.TRANSACTION_AMOUNT)) as ST_GD, " +
"SUM(decode(substr(aa.processing_code,1,2),'01',decode(aa.transaction_status,0,AA.TRANSACTION_AMOUNT,'110',aa.TRANSACTION_AMOUNT,'113',-(AA.TRANSACTION_AMOUNT-aa.actual_amount),-aa.TRANSACTION_AMOUNT)," +
" '40',decode(aa.transaction_status,0,AA.TRANSACTION_AMOUNT,'110',aa.TRANSACTION_AMOUNT,'113',-(AA.TRANSACTION_AMOUNT-aa.actual_amount),-aa.TRANSACTION_AMOUNT)," +
" '00',decode(aa.transaction_status,0,AA.TRANSACTION_AMOUNT,'110',aa.TRANSACTION_AMOUNT,'113',-(AA.TRANSACTION_AMOUNT-aa.actual_amount),-aa.TRANSACTION_AMOUNT)," +
" '30',decode(aa.transaction_status,0,AA.TRANSACTION_AMOUNT,'110',aa.TRANSACTION_AMOUNT,'113',-(AA.TRANSACTION_AMOUNT-aa.actual_amount),-aa.TRANSACTION_AMOUNT)," +
" '35',decode(aa.transaction_status,0,AA.TRANSACTION_AMOUNT,'110',aa.TRANSACTION_AMOUNT,'113',-(AA.TRANSACTION_AMOUNT-aa.actual_amount),-aa.TRANSACTION_AMOUNT)))" +
" as ST_GD," +
" sum(decode(aa.MCC,'6011',0, DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'01',decode(aa.transaction_status,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.transaction_status,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.transaction_status,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.transaction_status,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.transaction_status,0,'SAO KE','DAO SAO KE')),1,3),'DAO', -decode(substr(aa.processing_code,1,2),'30',0,aa.iss_fee), decode(substr(aa.processing_code,1,2),'30',0,aa.iss_fee)))) as PHAI_THU," +
" sum(decode(aa.MCC,'6011',DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'01',decode(aa.transaction_status,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.transaction_status,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.transaction_status,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.transaction_status,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.transaction_status,0,'SAO KE','DAO SAO KE')),1,3),'DAO', -aa.iss_fee, aa.iss_fee),decode(substr(aa.processing_code,1,2),'30',aa.iss_fee,0))) as PHAI_TRA," +
//" (SUM(DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'01',decode(aa.transaction_status,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.transaction_status,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.transaction_status,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.transaction_status,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.transaction_status,0,'SAO KE','DAO SAO KE')),1,3),'DAO', -AA.TRANSACTION_AMOUNT, AA.TRANSACTION_AMOUNT)) + " +
//" - sum(decode(aa.MCC,'6011',0, DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'01',decode(aa.transaction_status,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.transaction_status,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.transaction_status,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.transaction_status,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.transaction_status,0,'SAO KE','DAO SAO KE')),1,3),'DAO', -aa.iss_fee, aa.iss_fee)))" +
//" + sum(decode(aa.MCC,'6011',DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'01',decode(aa.transaction_status,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.transaction_status,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.transaction_status,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.transaction_status,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.transaction_status,0,'SAO KE','DAO SAO KE')),1,3),'DAO', -aa.iss_fee, aa.iss_fee),0))" +
//" ) as Tong" +
//Dat sua 23/09/2011
// " (SUM(DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'01',decode(aa.transaction_status,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.transaction_status,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.transaction_status,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.transaction_status,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.transaction_status,0,'SAO KE','DAO SAO KE')),1,3),'DAO', -AA.TRANSACTION_AMOUNT, AA.TRANSACTION_AMOUNT)) " +
" (SUM(DECODE(substr(aa.processing_code,1,2),'01',decode(aa.transaction_status,0,AA.TRANSACTION_AMOUNT,'110',aa.TRANSACTION_AMOUNT,113,-(AA.TRANSACTION_AMOUNT-AA.Actual_Amount),-AA.TRANSACTION_AMOUNT)," +
" '40',decode(aa.transaction_status,0,AA.TRANSACTION_AMOUNT,'110',aa.TRANSACTION_AMOUNT,113,-(AA.TRANSACTION_AMOUNT-AA.Actual_Amount),-AA.TRANSACTION_AMOUNT)," +
" '00',decode(aa.transaction_status,0,AA.TRANSACTION_AMOUNT,'110',aa.TRANSACTION_AMOUNT,113,-(AA.TRANSACTION_AMOUNT-AA.Actual_Amount),-AA.TRANSACTION_AMOUNT)," +
" '30',decode(aa.transaction_status,0,AA.TRANSACTION_AMOUNT,'110',aa.TRANSACTION_AMOUNT,113,-(AA.TRANSACTION_AMOUNT-AA.Actual_Amount),-AA.TRANSACTION_AMOUNT)," +
" '35',decode(aa.transaction_status,0,AA.TRANSACTION_AMOUNT,'110',aa.TRANSACTION_AMOUNT,113,-(AA.TRANSACTION_AMOUNT-AA.Actual_Amount),-AA.TRANSACTION_AMOUNT)" +
" )) " +
//End Dat sua 23/09/2011
" + sum(decode(aa.MCC,'6011',DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'01',decode(aa.transaction_status,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.transaction_status,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.transaction_status,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.transaction_status,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.transaction_status,0,'SAO KE','DAO SAO KE')),1,3),'DAO', -aa.iss_fee, aa.iss_fee),decode(substr(aa.processing_code,1,2),'30',aa.iss_fee,0)))" +
" - sum(decode(aa.MCC,'6011',0, DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'01',decode(aa.transaction_status,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.transaction_status,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.transaction_status,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.transaction_status,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.transaction_status,0,'SAO KE','DAO SAO KE')),1,3),'DAO', -decode(substr(aa.processing_code,1,2),'30',0,aa.iss_fee), decode(substr(aa.processing_code,1,2),'30',0,aa.iss_fee))))" +
" ) as Tong" +
" from " + Businessbp.executedb.owner + "banknet_issuer_transaction aa"+//," + Businessbp.executedb.owner + "card bb" +
" where 1=1" +
// " and substr(aa.card_number,1,8) not in ('97042808','97042809') " + //Phuong add loai bo the credit va Prepaid
// " and aa.card_number=bb.card_number"+ //Dat modify loai bo the Credit va Prepaid
// " and bb.product_code in (select cc.product_code from " + Businessbp.executedb.owner + "nab_phanloaisp cc where cc.type='D' and cc.bank_code='970428')" +
where +
" group by decode(substr(aa.processing_code,1,2),'30','GIAO DICH PHI TAI CHINH','35','GIAO DICH PHI TAI CHINH','GIAO DICH TAI CHINH')," +
" decode(substr(aa.processing_code,1,2),'01',decode(aa.transaction_status,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.transaction_status,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.transaction_status,0,'MUA HANG',110,'DAO MUA HANG BO SUNG','DAO MUA HANG'),'30',decode(aa.transaction_status,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.transaction_status,0,'SAO KE','DAO SAO KE'))" +
" union all" +
" select distinct 'ACQUER (NAB dong vai tro ACQ, chi bao gom giao dich the do NH khac phat hanh su dung tai he thong NAB)' as ISS_ACQ, " +
" decode(substr(aa.processing_code,1,2),'30','GIAO DICH PHI TAI CHINH','35','GIAO DICH PHI TAI CHINH','GIAO DICH TAI CHINH') as TC_PTC, " +
// Goc ngay 11012012
//" decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.response_code,0,'MUA HANG',110,'DAO MUA HANG BO SUNG','DAO MUA HANG'),'30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.response_code,0,'SAO KE','DAO SAO KE')) AS TYPE," +
//end goc ngay 11012012
// Dat sua ngày 11012012 --> Thẻ CUP
" decode(cc.tenviettac,'CUP',decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM (CUP)','DAO RUT TIEN ATM (CUP)')," +
" '40',decode(aa.response_code,0,'CHUYEN KHOAN (CUP)','DAO CHUYEN KHOAN (CUP)')," +
" '00',decode(aa.response_code,0,'MUA HANG',110,'DAO MUA HANG BO SUNG (CUP)','DAO MUA HANG (CUP)')," +
" '30',decode(aa.response_code,0,'VAN TIN (CUP)','DAO VAN TIN (CUP)')," +
" '35',decode(aa.response_code,0,'SAO KE (CUP)','DAO SAO KE (CUP)')" +
")," +
" decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM')," +
" '40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN')," +
" '00',decode(aa.response_code,0,'MUA HANG',110,'DAO MUA HANG BO SUNG','DAO MUA HANG')," +
" '30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN')," +
" '35',decode(aa.response_code,0,'SAO KE','DAO SAO KE')" +
")) AS TYPE," +
//End Dat sua ngày 11012012 --> Thẻ CUP
" COUNT(*) AS SL_GD, " +
//Dat sua 23/09/2011
//Noi dung sua: giao dich dao 113
//noi dung cu-- " SUM(DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.response_code,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.response_code,0,'SAO KE','DAO SAO KE')),1,3),'DAO', -AA.TRANSACTION_AMOUNT, AA.TRANSACTION_AMOUNT)) as ST_GD, " +
"SUM(decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,AA.TRANSACTION_AMOUNT,'110',aa.TRANSACTION_AMOUNT,'113',-(AA.TRANSACTION_AMOUNT-aa.actual_amount),-aa.TRANSACTION_AMOUNT)," +
" '40',decode(aa.response_code,0,AA.TRANSACTION_AMOUNT,'110',aa.TRANSACTION_AMOUNT,'113',-(AA.TRANSACTION_AMOUNT-aa.actual_amount),-aa.TRANSACTION_AMOUNT)," +
" '00',decode(aa.response_code,0,AA.TRANSACTION_AMOUNT,'110',aa.TRANSACTION_AMOUNT,'113',-(AA.TRANSACTION_AMOUNT-aa.actual_amount),-aa.TRANSACTION_AMOUNT)," +
" '30',decode(aa.response_code,0,AA.TRANSACTION_AMOUNT,'110',aa.TRANSACTION_AMOUNT,'113',-(AA.TRANSACTION_AMOUNT-aa.actual_amount),-aa.TRANSACTION_AMOUNT)," +
" '35',decode(aa.response_code,0,AA.TRANSACTION_AMOUNT,'110',aa.TRANSACTION_AMOUNT,'113',-(AA.TRANSACTION_AMOUNT-aa.actual_amount),-aa.TRANSACTION_AMOUNT)))" +
" as ST_GD," +
//" sum(decode(aa.MCC, '6011', DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.response_code,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.response_code,0,'SAO KE','DAO SAO KE')),1,3),'DAO', -aa.acq_fee, aa.acq_fee),0)) as PHAI_THU," +
" sum(decode(aa.MCC, '6011', DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.response_code,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.response_code,0,'SAO KE','DAO SAO KE')),1,3),'DAO', -aa.acq_fee, aa.acq_fee),decode(substr(aa.processing_code,1,2),'30',decode(aa.response_code,115,-aa.acq_fee,aa.acq_fee),0))) as PHAI_THU," +
//" sum(decode(aa.MCC, '6011', 0, DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.response_code,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.response_code,0,'SAO KE','DAO SAO KE')),1,3),'DAO', -aa.acq_fee, aa.acq_fee))) as PHAI_TRA," +
//05/10/2011 -->" sum(decode(aa.MCC, '6011', 0, DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.response_code,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.response_code,0,'SAO KE','DAO SAO KE')),1,3),'DAO', -decode(substr(aa.processing_code,1,2),'30',0,aa.acq_fee), decode(substr(aa.processing_code,1,2),'30',0,aa.acq_fee)))) as PHAI_TRA," +
//05/10/2011
" sum(decode(aa.MCC, '6011', 0," +
" DECODE(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,AA.acq_fee,'110',aa.acq_fee,-AA.acq_fee)," +
" '40',decode(aa.response_code,0,AA.acq_fee,'110',aa.acq_fee,-AA.acq_fee)," +
" '00',decode(aa.response_code,0,AA.acq_fee,'110',aa.acq_fee,-AA.acq_fee)," +
" '30',decode(aa.response_code,0,0)," +
" '35',decode(aa.response_code,0,AA.acq_fee,'110',aa.acq_fee,-AA.acq_fee)" +
" ))) as PHAI_TRA," +
//Dat sua 23/09/2011
//" (SUM(DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.response_code,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.response_code,0,'SAO KE','DAO SAO KE')),1,3),'DAO', -AA.TRANSACTION_AMOUNT, AA.TRANSACTION_AMOUNT))" +
" (SUM(DECODE(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,AA.TRANSACTION_AMOUNT,'110',aa.TRANSACTION_AMOUNT,113,-(AA.TRANSACTION_AMOUNT-AA.Actual_Amount),-AA.TRANSACTION_AMOUNT)," +
" '40',decode(aa.response_code,0,AA.TRANSACTION_AMOUNT,'110',aa.TRANSACTION_AMOUNT,113,-(AA.TRANSACTION_AMOUNT-AA.Actual_Amount),-AA.TRANSACTION_AMOUNT)," +
" '00',decode(aa.response_code,0,AA.TRANSACTION_AMOUNT,'110',aa.TRANSACTION_AMOUNT,113,-(AA.TRANSACTION_AMOUNT-AA.Actual_Amount),-AA.TRANSACTION_AMOUNT)," +
" '30',decode(aa.response_code,0,AA.TRANSACTION_AMOUNT,'110',aa.TRANSACTION_AMOUNT,113,-(AA.TRANSACTION_AMOUNT-AA.Actual_Amount),-AA.TRANSACTION_AMOUNT)," +
" '35',decode(aa.response_code,0,AA.TRANSACTION_AMOUNT,'110',aa.TRANSACTION_AMOUNT,113,-(AA.TRANSACTION_AMOUNT-AA.Actual_Amount),-AA.TRANSACTION_AMOUNT)" +
" )) " +
//End Dat sua 23/09/2011
" +sum(decode(aa.MCC, '6011', DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.response_code,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.response_code,0,'SAO KE','DAO SAO KE')),1,3),'DAO', -aa.acq_fee, aa.acq_fee),decode(substr(aa.processing_code,1,2),'30',decode(aa.response_code,115,-aa.acq_fee,aa.acq_fee),0)))" +
" -sum(decode(aa.MCC, '6011', 0, DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.response_code,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.response_code,0,'SAO KE','DAO SAO KE')),1,3),'DAO', -decode(substr(aa.processing_code,1,2),'30',0,aa.acq_fee), decode(substr(aa.processing_code,1,2),'30',0,aa.acq_fee))))" +
" ) as Tong" +
" from " + Businessbp.executedb.owner + "banknet_acquirer_transaction aa " +
//Dat them ngày 11/01/2012 --> the CUP
" ," + Businessbp.executedb.owner + "bnk_bank cc" +
" where 1=1" +
" and substr(aa.card_number,1,6)= trim(cc.sodaucuathe)" +
// End Dat them ngày 11/01/2012 --> the CUP
where +
" group by decode(substr(aa.processing_code,1,2),'30','GIAO DICH PHI TAI CHINH','35','GIAO DICH PHI TAI CHINH','GIAO DICH TAI CHINH')," +
//goc ngay 11012012" decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.response_code,0,'MUA HANG',110,'DAO MUA HANG BO SUNG','DAO MUA HANG'),'30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.response_code,0,'SAO KE','DAO SAO KE'))";
// Dat sua ngày 11012012 --> Thẻ CUP
" decode(cc.tenviettac,'CUP',decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM (CUP)','DAO RUT TIEN ATM (CUP)')," +
" '40',decode(aa.response_code,0,'CHUYEN KHOAN (CUP)','DAO CHUYEN KHOAN (CUP)')," +
" '00',decode(aa.response_code,0,'MUA HANG',110,'DAO MUA HANG BO SUNG (CUP)','DAO MUA HANG (CUP)')," +
" '30',decode(aa.response_code,0,'VAN TIN (CUP)','DAO VAN TIN (CUP)')," +
" '35',decode(aa.response_code,0,'SAO KE (CUP)','DAO SAO KE (CUP)')" +
")," +
" decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM')," +
" '40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN')," +
" '00',decode(aa.response_code,0,'MUA HANG',110,'DAO MUA HANG BO SUNG','DAO MUA HANG')," +
" '30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN')," +
" '35',decode(aa.response_code,0,'SAO KE','DAO SAO KE')" +
"))";
//End Dat sua ngày 11012012 --> Thẻ CUP
//Smartlink
else
sql = "select distinct 'ISSUER (NAB dong vai tro ISS, chi bao gom giao dich chu the NAB su dung tai dai ly ngoai he thong NAB - GD not onus)' as ISS_ACQ, " +
" decode(substr(aa.processing_code,1,2),'30','GIAO DICH PHI TAI CHINH','35','GIAO DICH PHI TAI CHINH','GIAO DICH TAI CHINH') as TC_PTC, " +
" decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.response_code,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.response_code,0,'SAO KE','DAO SAO KE')) AS TYPE," +
" COUNT(*) AS SL_GD, " +
//Dat sua 23/09/2011
//Noi dung sua: giao dich dao 113
//Noi dung cu " SUM(DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.response_code,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.response_code,0,'SAO KE','DAO SAO KE')),1,3),'DAO', -AA.TRANSACTION_AMOUNT, AA.TRANSACTION_AMOUNT)) as ST_GD, " +
"SUM(decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,AA.TRANSACTION_AMOUNT,'113',-(AA.TRANSACTION_AMOUNT-aa.actual_amount),-aa.TRANSACTION_AMOUNT)," +
" '40',decode(aa.response_code,0,AA.TRANSACTION_AMOUNT,'113',-(AA.TRANSACTION_AMOUNT-aa.actual_amount),-aa.TRANSACTION_AMOUNT)," +
" '00',decode(aa.response_code,0,AA.TRANSACTION_AMOUNT,'113',-(AA.TRANSACTION_AMOUNT-aa.actual_amount),-aa.TRANSACTION_AMOUNT)," +
" '30',decode(aa.response_code,0,AA.TRANSACTION_AMOUNT,'113',-(AA.TRANSACTION_AMOUNT-aa.actual_amount),-aa.TRANSACTION_AMOUNT)," +
" '35',decode(aa.response_code,0,AA.TRANSACTION_AMOUNT,'113',-(AA.TRANSACTION_AMOUNT-aa.actual_amount),-aa.TRANSACTION_AMOUNT)))" +
" as ST_GD," +
" sum(decode(aa.MCC,'6011',0, DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.response_code,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.response_code,0,'SAO KE','DAO SAO KE')),1,3),'DAO', -decode(aa.mcc,'6011',decode(aa.transaction_amount,0,1650,3300),0), decode(aa.mcc,'6011',decode(aa.transaction_amount,0,1650,3300),0)))) as PHAI_THU," +
" sum(decode(aa.MCC,'6011',DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.response_code,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.response_code,0,'SAO KE','DAO SAO KE')),1,3),'DAO', -decode(aa.mcc,'6011',decode(aa.transaction_amount,0,1650,3300),0), decode(aa.mcc,'6011',decode(aa.transaction_amount,0,1650,3300),0)),0)) as PHAI_TRA," +
//Dat sua 23/09/2011
//" (SUM(DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.response_code,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.response_code,0,'SAO KE','DAO SAO KE')),1,3),'DAO', -AA.TRANSACTION_AMOUNT, AA.TRANSACTION_AMOUNT)) + " +
" (SUM(DECODE(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,AA.TRANSACTION_AMOUNT,113,-(AA.TRANSACTION_AMOUNT-AA.Actual_Amount),-AA.TRANSACTION_AMOUNT)," +
" '40',decode(aa.response_code,0,AA.TRANSACTION_AMOUNT,113,-(AA.TRANSACTION_AMOUNT-AA.Actual_Amount),-AA.TRANSACTION_AMOUNT)," +
" '00',decode(aa.response_code,0,AA.TRANSACTION_AMOUNT,113,-(AA.TRANSACTION_AMOUNT-AA.Actual_Amount),-AA.TRANSACTION_AMOUNT)," +
" '30',decode(aa.response_code,0,AA.TRANSACTION_AMOUNT,113,-(AA.TRANSACTION_AMOUNT-AA.Actual_Amount),-AA.TRANSACTION_AMOUNT)," +
" '35',decode(aa.response_code,0,AA.TRANSACTION_AMOUNT,113,-(AA.TRANSACTION_AMOUNT-AA.Actual_Amount),-AA.TRANSACTION_AMOUNT)" +
" )) " +
//End Dat sua 23/09/2011
" - sum(decode(aa.MCC,'6011',0, DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.response_code,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.response_code,0,'SAO KE','DAO SAO KE')),1,3),'DAO', -decode(aa.mcc,'6011',decode(aa.transaction_amount,0,1650,3300),0), decode(aa.mcc,'6011',decode(aa.transaction_amount,0,1650,3300),0))))" +
" + sum(decode(aa.MCC,'6011',DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.response_code,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.response_code,0,'SAO KE','DAO SAO KE')),1,3),'DAO', -decode(aa.mcc,'6011',decode(aa.transaction_amount,0,1650,3300),0), decode(aa.mcc,'6011',decode(aa.transaction_amount,0,1650,3300),0)),0))" +
" ) as Tong" +
" from " + Businessbp.executedb.owner + "sml_issuer_transaction aa" +
" where 1=1" +
where +
" group by decode(substr(aa.processing_code,1,2),'30','GIAO DICH PHI TAI CHINH','35','GIAO DICH PHI TAI CHINH','GIAO DICH TAI CHINH')," +
" decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.response_code,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.response_code,0,'SAO KE','DAO SAO KE'))" +
" union all" +
" select distinct 'ACQUER (NAB dong vai tro ACQ, chi bao gom giao dich the do NH khac phat hanh su dung tai he thong NAB)' as ISS_ACQ, " +
" decode(substr(aa.processing_code,1,2),'30','GIAO DICH PHI TAI CHINH','35','GIAO DICH PHI TAI CHINH','GIAO DICH TAI CHINH') as TC_PTC, " +
" decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.response_code,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.response_code,0,'SAO KE','DAO SAO KE')) AS TYPE," +
" COUNT(*) AS SL_GD, " +
//Dat sua 23/09/2011
//Noi dung sua: giao dich dao 113
//Noi dung cu " SUM(DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.response_code,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.response_code,0,'SAO KE','DAO SAO KE')),1,3),'DAO', -AA.TRANSACTION_AMOUNT, AA.TRANSACTION_AMOUNT)) as ST_GD, " +
"SUM(decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,AA.TRANSACTION_AMOUNT,'113',-(AA.TRANSACTION_AMOUNT-aa.actual_amount),-aa.TRANSACTION_AMOUNT)," +
" '40',decode(aa.response_code,0,AA.TRANSACTION_AMOUNT,'113',-(AA.TRANSACTION_AMOUNT-aa.actual_amount),-aa.TRANSACTION_AMOUNT)," +
" '00',decode(aa.response_code,0,AA.TRANSACTION_AMOUNT,'113',-(AA.TRANSACTION_AMOUNT-aa.actual_amount),-aa.TRANSACTION_AMOUNT)," +
" '30',decode(aa.response_code,0,AA.TRANSACTION_AMOUNT,'113',-(AA.TRANSACTION_AMOUNT-aa.actual_amount),-aa.TRANSACTION_AMOUNT)," +
" '35',decode(aa.response_code,0,AA.TRANSACTION_AMOUNT,'113',-(AA.TRANSACTION_AMOUNT-aa.actual_amount),-aa.TRANSACTION_AMOUNT)))" +
" as ST_GD," +
" sum(decode(aa.MCC, '6011', DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.response_code,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.response_code,0,'SAO KE','DAO SAO KE')),1,3),'DAO', -decode(aa.mcc,'6011',decode(aa.transaction_amount,0,550,2200),150), decode(aa.mcc,'6011',decode(aa.transaction_amount,0,550,2200),150)),0)) as PHAI_THU," +
" sum(decode(aa.MCC, '6011', 0, DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.response_code,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.response_code,0,'SAO KE','DAO SAO KE')),1,3),'DAO', -decode(aa.mcc,'6011',decode(aa.transaction_amount,0,550,2200),150), decode(aa.mcc,'6011',decode(aa.transaction_amount,0,550,2200),150)))) as PHAI_TRA," +
//Dat sua 23/09/2011
//" (SUM(DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.response_code,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.response_code,0,'SAO KE','DAO SAO KE')),1,3),'DAO', -AA.TRANSACTION_AMOUNT, AA.TRANSACTION_AMOUNT))" +
" (SUM(DECODE(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,AA.TRANSACTION_AMOUNT,113,-(AA.TRANSACTION_AMOUNT-AA.Actual_Amount),-AA.TRANSACTION_AMOUNT)," +
" '40',decode(aa.response_code,0,AA.TRANSACTION_AMOUNT,113,-(AA.TRANSACTION_AMOUNT-AA.Actual_Amount),-AA.TRANSACTION_AMOUNT)," +
" '00',decode(aa.response_code,0,AA.TRANSACTION_AMOUNT,113,-(AA.TRANSACTION_AMOUNT-AA.Actual_Amount),-AA.TRANSACTION_AMOUNT)," +
" '30',decode(aa.response_code,0,AA.TRANSACTION_AMOUNT,113,-(AA.TRANSACTION_AMOUNT-AA.Actual_Amount),-AA.TRANSACTION_AMOUNT)," +
" '35',decode(aa.response_code,0,AA.TRANSACTION_AMOUNT,113,-(AA.TRANSACTION_AMOUNT-AA.Actual_Amount),-AA.TRANSACTION_AMOUNT)" +
" )) " +
//End Dat sua 23/09/2011
" +sum(decode(aa.MCC, '6011', DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.response_code,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.response_code,0,'SAO KE','DAO SAO KE')),1,3),'DAO', -decode(aa.mcc,'6011',decode(aa.transaction_amount,0,550,2200),150), decode(aa.mcc,'6011',decode(aa.transaction_amount,0,550,2200),150)),0))" +
" -sum(decode(aa.MCC, '6011', 0, DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.response_code,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.response_code,0,'SAO KE','DAO SAO KE')),1,3),'DAO', -decode(aa.mcc,'6011',decode(aa.transaction_amount,0,550,2200),150), decode(aa.mcc,'6011',decode(aa.transaction_amount,0,550,2200),150))))" +
" ) as Tong" +
" from " + Businessbp.executedb.owner + "sml_acq_transaction aa" +
" where 1=1" +
where +
" group by decode(substr(aa.processing_code,1,2),'30','GIAO DICH PHI TAI CHINH','35','GIAO DICH PHI TAI CHINH','GIAO DICH TAI CHINH')," +
" decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.response_code,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.response_code,0,'SAO KE','DAO SAO KE'))";
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if(err!="")
{
MessageBox.Show(err);
return;
}
a.dt = dtt;
if (cbBanknet.Checked)
a.title = "BÁO CÁO BÙ TRỪ GIAO DỊCH THẺ BANKNETVN";
else
a.title = "BÁO CÁO BÙ TRỪ GIAO DỊCH THẺ SMARTLINK";
a.ShowDialog();
}
# endregion R1
#region R2
else if (comboBox1.SelectedIndex + 1 == 2)
{
//Bao cao 2
frmShowReport a = new frmShowReport();
a.type = "DC_2";
a.thoiGian = a.thoiGian = "Từ " + dtFrom.Text + " đến " + dtTo.Text;
string sql = "",err="";
string where = " and to_date(decode(substr(aa.file_name,1,1),'I',substr(aa.file_name,2,6)," +
" decode(substr(aa.file_name,1,1),'O',substr(aa.file_name,2,6)," +
" (substr(aa.file_name,1,6)))),'mmddyy') " +
" >= to_date('" + dtFrom.Text.Trim() + "','dd/mm/yyyy')" +
" and to_date(decode(substr(aa.file_name,1,1),'I',substr(aa.file_name,2,6)," +
" decode(substr(aa.file_name,1,1),'O',substr(aa.file_name,2,6)," +
" (substr(aa.file_name,1,6)))),'mmddyy') " +
" <= to_date('" + dtTo.Text.Trim() + "','dd/mm/yyyy')";
//" and to_date(substr(aa.file_name,1,6),'mmddyy') >= to_date('" + dtFrom.Text.Trim() + "','dd/mm/yyyy')" +
//" and to_date(substr(aa.file_name,1,6),'mmddyy') <= to_date('" + dtTo.Text.Trim() + "','dd/mm/yyyy') ";
if(cbBanknet.Checked)
sql = "select distinct 'ISSUER (NAB dong vai tro ISS, chi bao gom giao dich chu the NAB su dung tai dai ly ngoai he thong NAB - GD not onus)' as ISS_ACQ, " +
" DECODE(substr(aa.processing_code,1,2),'30','GIAO DICH PHI TAI CHINH','35','GIAO DICH PHI TAI CHINH','GIAO DICH TAI CHINH') as TC_PTC, "+
" to_char(aa.transaction_local_date,'dd/mm/yyyy hh24:mi:ss') as transaction_local_date, aa.acquirer_institution_code as acquirer_institution_code, bb.TENNGANHANG, aa.card_number, aa.trace_audit_number, aa.authorization_number," +
" decode(aa.mcc,6011,'ATM','POS') as Loai_TB, "+
" decode(substr(aa.processing_code,1,2),'01',decode(aa.transaction_status,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.transaction_status,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.transaction_status,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.transaction_status,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.transaction_status,0,'SAO KE','DAO SAO KE')) AS Loai_GD," +
//Dat them 113 ngay 23/09/2011
" decode(aa.transaction_status,115,-aa.transaction_amount,111,-aa.transaction_amount,113,(aa.transaction_amount-aa.actual_amount),aa.transaction_amount) as transaction_amount, aa.currency_code, " +
" decode(aa.MCC,'6011',decode(aa.transaction_status,115,aa.iss_fee,-aa.iss_fee),decode(aa.transaction_status,115,-aa.iss_fee,decode(substr(aa.processing_code,1,2),'30',-aa.iss_fee,aa.iss_fee))) as Fee, " +
" decode(aa.transaction_status,115,-aa.banknetvn_fee,aa.banknetvn_fee) as banknetvn_fee, to_char(aa.transaction_status) as RC, "+
" aa.card_acceptor_term_id as MA_MAY, null as TEN_MAY " +
" from " + Businessbp.executedb.owner + "banknet_issuer_transaction aa, " + Businessbp.executedb.owner + "bnk_bank bb"+//, " + Businessbp.executedb.owner + "card dd" +
" where substr(aa.card_number,1,6) = trim(bb.sodaucuathe)" +
// " and aa.card_number=dd.card_number" + //Dat modify loai bo the Credit va Prepaid
// " and dd.product_code in (select cc.product_code from " + Businessbp.executedb.owner + "nab_phanloaisp cc where cc.type='D' and cc.bank_code='970428')" +
where+
" union all"+
" select distinct 'ACQUER (NAB dong vai tro ACQ, chi bao go giao dich the do NH khac phat hanh su dung tai he thong NAB)' as ISS_ACQ, " +
" DECODE(substr(aa.processing_code,1,2),'30','GIAO DICH PHI TAI CHINH','35','GIAO DICH PHI TAI CHINH','GIAO DICH TAI CHINH') as TC_PTC, "+
" to_char(aa.transaction_local_date,'dd/mm/yyyy hh24:mi:ss') as transaction_local_date, substr(aa.card_number,1,6) as acquirer_institution_code, bb.TENNGANHANG, aa.card_number, aa.trace_audit_number, aa.authorization_number," +
" decode(aa.mcc,6011,'ATM','POS') as Loai_TB, "+
" decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.response_code,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.response_code,0,'SAO KE','DAO SAO KE')) AS Loai_GD," +
//Dat them 113 ngay 23/09/2011
" decode(aa.response_code,115,-aa.transaction_amount,113,(aa.transaction_amount-aa.actual_amount),aa.transaction_amount) as transaction_amount, aa.currency_code, " +
" decode(aa.MCC, '6011',decode(aa.response_code,115,-aa.acq_fee,+aa.acq_fee),decode(aa.response_code,115,+aa.acq_fee,-aa.acq_fee)) as Fee, " +
" decode(aa.response_code,115,-aa.banknetvn_fee,aa.banknetvn_fee) as banknetvn_fee, to_char(aa.response_code) as RC," +
" substr(aa.card_acceptor_term_id,1,4) MA_MAY, cc.aut_localisation as TEN_MAY"+
" from " + Businessbp.executedb.owner + "banknet_acquirer_transaction aa, " + Businessbp.executedb.owner + "bnk_bank bb, " + Businessbp.executedb.owner + "pg_automates cc" +
" where substr(aa.card_number,1,6) = trim(bb.sodaucuathe)" +
" and substr(aa.card_acceptor_term_id,1,4) = trim(cc.aut_numlogique)"+
" and trim(aa.mcc) = '6011'" +
where+
//" order by ISS_ACQ, TC_PTC, transaction_local_date asc"+
" union all" +
" select distinct 'ACQUER (NAB dong vai tro ACQ, chi bao go giao dich the do NH khac phat hanh su dung tai he thong NAB)' as ISS_ACQ, " +
" DECODE(substr(aa.processing_code,1,2),'30','GIAO DICH PHI TAI CHINH','35','GIAO DICH PHI TAI CHINH','GIAO DICH TAI CHINH') as TC_PTC, " +
" to_char(aa.transaction_local_date,'dd/mm/yyyy hh24:mi:ss') as transaction_local_date, substr(aa.card_number,1,6) as acquirer_institution_code, bb.TENNGANHANG, aa.card_number, aa.trace_audit_number, aa.authorization_number," +
" decode(aa.mcc,6011,'ATM','POS') as Loai_TB, " +
" decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.response_code,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.response_code,0,'SAO KE','DAO SAO KE')) AS Loai_GD," +
//Dat them 113 ngay 23/09/2011
" decode(aa.response_code,115,-aa.transaction_amount,113,(aa.transaction_amount-aa.actual_amount),aa.transaction_amount) as transaction_amount, aa.currency_code, " +
" decode(aa.MCC, '6011',decode(aa.response_code,115,+aa.acq_fee,-aa.acq_fee),decode(aa.response_code,115,-aa.acq_fee,+aa.acq_fee)) as Fee, " +
" decode(aa.response_code,115,-aa.banknetvn_fee,aa.banknetvn_fee) as banknetvn_fee, to_char(aa.response_code) as RC," +
" substr(aa.card_acceptor_term_id,1,4) MA_MAY, ee.company_name as TEN_MAY" +
" from " + Businessbp.executedb.owner + "banknet_acquirer_transaction aa, " + Businessbp.executedb.owner + "bnk_bank bb, " + Businessbp.executedb.owner + "posptserv cc, " + Businessbp.executedb.owner + "ptserv dd, " + Businessbp.executedb.owner + "merchant ee" +
" where substr(aa.card_number,1,6) = trim(bb.sodaucuathe)" +
" and trim(aa.card_acceptor_term_id) = trim(cc.pos_code)" +
//" and substr(cc.outlet_number,1,3) = trim(aa.currency_code)" +
" and trim(dd.currency_code) = trim(aa.currency_code)" +//Dat update ngay 01/03/2013
" and trim(cc.outlet_number) = trim(dd.outlet_number)" +
" and trim(dd.merchant_number) = trim(ee.merchant_number)"+
where +
" and trim(aa.mcc) <> '6011'" +
" order by ISS_ACQ, TC_PTC, transaction_local_date asc";
else
sql = "select distinct 'ISSUER (NAB dong vai tro ISS, chi bao gom giao dich chu the NAB su dung tai dai ly ngoai he thong NAB - GD not onus)' as ISS_ACQ, " +
" DECODE(substr(aa.processing_code,1,2),'30','GIAO DICH PHI TAI CHINH','35','GIAO DICH PHI TAI CHINH','GIAO DICH TAI CHINH') as TC_PTC, " +
" to_char(aa.transaction_local_date,'dd/mm/yyyy hh24:mi:ss') as transaction_local_date, aa.acquirer_institution_code as acquirer_institution_code, bb.bank_name as TENNGANHANG, aa.card_number, aa.trace_audit_number, aa.authorization_number," +
" decode(aa.mcc,6011,'ATM','POS') as Loai_TB, " +
" decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.response_code,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.response_code,0,'SAO KE','DAO SAO KE')) AS Loai_GD," +
//Dat them 113 ngay 23/09/2011
" decode(aa.response_code,115,-aa.transaction_amount,113,(aa.transaction_amount-aa.actual_amount),aa.transaction_amount) as transaction_amount, aa.currency_code, " +
" decode(aa.MCC,'6011',decode(aa.response_code,115,decode(aa.mcc,'6011',decode(aa.transaction_amount,0,1650,3300),0),-decode(aa.mcc,'6011',decode(aa.transaction_amount,0,1650,3300),0)),decode(aa.response_code,115,-decode(aa.mcc,'6011',decode(aa.transaction_amount,0,1650,3300),0),decode(aa.mcc,'6011',decode(aa.transaction_amount,0,1650,3300),0))) as Fee, " +
" decode(aa.response_code,115,-decode(aa.mcc,'6011',decode(aa.transaction_amount,0,1100,1100),150),decode(aa.mcc,'6011',decode(aa.transaction_amount,0,1100,1100),150)) as banknetvn_fee, to_char(aa.response_code) as RC, " +
" aa.card_acceptor_term_id as MA_MAY, null as TEN_MAY " +
" from " + Businessbp.executedb.owner + "sml_issuer_transaction aa, " + Businessbp.executedb.owner + "dsnganhang_smartlink bb " +
" where trim(aa.acquirer_institution_code) = trim(bb.bank_id)" +
where +
" union all" +
" select distinct 'ACQUER (NAB dong vai tro ACQ, chi bao go giao dich the do NH khac phat hanh su dung tai he thong NAB)' as ISS_ACQ, " +
" DECODE(substr(aa.processing_code,1,2),'30','GIAO DICH PHI TAI CHINH','35','GIAO DICH PHI TAI CHINH','GIAO DICH TAI CHINH') as TC_PTC, " +
" to_char(aa.transaction_local_date,'dd/mm/yyyy hh24:mi:ss') as transaction_local_date, substr(aa.card_number,1,6) as acquirer_institution_code, bb.bank_name as TENNGANHANG, aa.card_number, aa.trace_audit_number, aa.authorization_number," +
" decode(aa.mcc,6011,'ATM','POS') as Loai_TB, " +
" decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.response_code,0,'MUA HANG','DAO MUA HANG'),'30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.response_code,0,'SAO KE','DAO SAO KE')) AS Loai_GD," +
//Dat them 113 ngay 23/09/2011
" decode(aa.response_code,115,-aa.transaction_amount,113,(aa.transaction_amount-aa.actual_amount),aa.transaction_amount) as transaction_amount, aa.currency_code, " +
" decode(aa.response_code,115,-aa.transaction_amount,aa.transaction_amount) as transaction_amount, aa.currency_code, " +
" decode(aa.MCC, '6011',decode(aa.response_code,115,-decode(aa.mcc,'6011',decode(aa.transaction_amount,0,550,2200),150),+decode(aa.mcc,'6011',decode(aa.transaction_amount,0,550,2200),150)),decode(aa.response_code,115,+decode(aa.mcc,'6011',decode(aa.transaction_amount,0,550,2200),150),-decode(aa.mcc,'6011',decode(aa.transaction_amount,0,550,2200),150))) as Fee, " +
" decode(aa.response_code,115,-decode(aa.mcc,'6011',decode(aa.transaction_amount,0,1100,1100),150),decode(aa.mcc,'6011',decode(aa.transaction_amount,0,1100,1100),150)) as banknetvn_fee, to_char(aa.response_code) as RC," +
" substr(aa.card_acceptor_term_id,1,4) MA_MAY, cc.aut_localisation as TEN_MAY" +
" from " + Businessbp.executedb.owner + "sml_acq_transaction aa, " + Businessbp.executedb.owner + "dsnganhang_smartlink bb, " + Businessbp.executedb.owner + "pg_automates cc" +
" where substr(aa.card_number,1,6) = trim(bb.ma_bin)" +
" and substr(aa.card_acceptor_term_id,1,4) = trim(cc.aut_numlogique)" +
where +
" order by transaction_local_date asc";
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
a.dt = dtt;
if (cbBanknet.Checked)
a.title = "BÁO CÁO BÙ TRỪ GIAO DỊCH THẺ BANKNETVN";
else
a.title = "BÁO CÁO BÙ TRỪ GIAO DỊCH THẺ SMARTLINK";
a.ShowDialog();
}
# endregion R2
#region R3
else if (comboBox1.SelectedIndex + 1 == 3)
{
//Bao cao 3
frmShowReport a = new frmShowReport();
a.type = "DC_3";
a.thoiGian = "Từ " + dtFrom.Text + " " + tbFh.Text + ":" + tbFm.Text + ":" + tbFs.Text + " đến " + dtTo.Text + " " + tbTh.Text + ":" + tbTm.Text + ":" + tbTs.Text;
string sql = "", err = "";
string gd_type = "";
if (rbDomestic.Checked)
gd_type = "BNK";
else
if (rbInternal.Checked)
gd_type = "MASTER";
else gd_type = "ACQ";
string where = " and trunc(aa.processing_date) >= trunc(to_date('" + dtFrom.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss'))" +
" and trunc(aa.processing_date) <= trunc(to_date('" + dtTo.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss'))";
sql = "select 'POS NAB' as Type, 'Ghi chu: Chi bao gom giao dich tai POS NAB' as GiChu," +
" substr(aa.card_number,1,6) as BIN, " +
" sum(decode(aa.reversal_flag,'R',-aa.billing_amount,decode(aa.transaction_code,'09',-aa.billing_amount,aa.billing_amount))) as SoTien," +
" sum(nvl((select decode(bb.reversal_flag,'R',-bb.billing_amount,decode(bb.transaction_code,'09',-bb.billing_amount,bb.billing_amount))" +
" from " + Businessbp.executedb.owner + "transaction_hist bb" +
" where bb.microfilm_ref_number = aa.microfilm_ref_number" +
" and bb.transaction_code = '22'" +
" and bb.card_number = aa.card_number" +
" and nvl(bb.reversal_flag,'N') = nvl(aa.reversal_flag,'N')" +
" ),0)) as Phi," +
" count(*) as Record" +
" from " + Businessbp.executedb.owner + "transaction_hist aa," + Businessbp.executedb.owner + "nab_phanloaisp cc"+
" where 1=1 ";
//if (cbBanknet.Checked)
// sql += " where aa.network_code = '22'";
//else
// sql += " where aa.network_data = '23'";
if (rbDomestic.Checked)
sql += " and aa.network_code = '22'";
else if (rbInternal.Checked)
sql += " and aa.network_code = '02'";
else
sql += " and aa.network_code in ('22','02') ";
//" where aa.network_code = '22'"+
sql +=
" and aa.acquirer_bank_code = '970428'" +
" and aa.issuer_bank_code = '970428'" +
//Dat them ngay 11/04/2012: chi lay the debit
" and trim(aa.product_code)=trim(cc.product_code)" +
" and trim(cc.type)='D'"+
//End Dat them ngay 11/04/2012: chi lay the debit
//" and rtrim(aa.network_data,chr(0)) is null" +
" and aa.mcc <> '6011'" +
" and aa.transaction_code <> '22'" +
where +
" group by substr(aa.card_number,1,6)";
sql += " union all";
sql += " select 'POS " + gd_type + "' as Type, 'Ghi chu: Chi bao gom giao dich tai POS NAB cua " + gd_type + "' as GiChu," +
" substr(aa.card_number,1,6) as BIN, " +
" sum(decode(aa.reversal_flag,'R',-aa.billing_amount,decode(aa.transaction_code,'09',-aa.billing_amount,aa.billing_amount))) as SoTien," +
" sum(nvl((select decode(bb.reversal_flag,'R',-bb.billing_amount,decode(bb.transaction_code,'09',-bb.billing_amount,bb.billing_amount))" +
" from " + Businessbp.executedb.owner + "transaction_hist bb" +
" where bb.microfilm_ref_number = aa.microfilm_ref_number" +
" and bb.transaction_code = '22'" +
" and bb.card_number = aa.card_number" +
" and nvl(bb.reversal_flag,'N') = nvl(aa.reversal_flag,'N')" +
" ),0)) as Phi," +
" count(*) as Record" +
" from " + Businessbp.executedb.owner + "transaction_hist aa"+// + Businessbp.executedb.owner + "nab_phanloaisp cc" +
" where 1=1";
//if (cbBanknet.Checked)
// sql += " where aa.network_code = '23'";
//else
// sql += " where aa.network_code = '23'";
if (rbDomestic.Checked)
sql += " and aa.network_code = '23'";
else if (rbInternal.Checked)
sql += " and aa.network_code = '02'";
else
sql += " and aa.network_code in ('23','02') ";
//" where aa.network_code = '23'"+
sql +=
" and aa.acquirer_bank_code = '970428'" +
//Dat them ngay 11/04/2012: chi lay the debit
// " and aa.product_code=cc.product_code" +
// " and trim(cc.type)='D'" +
//End Dat them ngay 11/04/2012: chi lay the debit
" and decode(rtrim(aa.issuer_bank_code,chr(0)),null,'500001',aa.issuer_bank_code) <> '970428'"+//" and aa.issuer_bank_code <> '970428'" +
//" and rtrim(aa.network_data,chr(0)) is null"+
" and aa.mcc <> '6011'" +
" and aa.transaction_code <> '22'" +
where +
" group by substr(aa.card_number,1,6)";
//textBox1.Text = sql;
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
a.dt = dtt;
a.ShowDialog();
}
#endregion R3
#region R4
else if (comboBox1.SelectedIndex + 1 == 4)
{
//Bao cao 4
frmShowReport a = new frmShowReport();
a.type = "DC_4";
a.thoiGian = "Từ " + dtFrom.Text + " " + tbFh.Text + ":" + tbFm.Text + ":" + tbFs.Text + " đến " + dtTo.Text + " " + tbTh.Text + ":" + tbTm.Text + ":" + tbTs.Text;
string sql = "", err = "";
string where = " and aa.transaction_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and aa.transaction_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')";
sql = "select decode(substr(aa.processing_code,1,2),'31','GIAO DICH PHI TAI CHINH','91','GIAO DICH PHI TAI CHINH','GIAO DICH TAI CHINH') AS TYPE," +
" aa.transaction_date, " +
" aa.card_number, " +
" aa.trace_audit_number," +
" aa.authorization_number," +
" bb.description as Loai_TB," +
" cc.transaction_wording as Loai_GD," +
" decode(aa.reversal_flag,'R',-aa.transaction_amount,decode(aa.transaction_code,'09',-aa.transaction_amount,aa.transaction_amount)) as transaction_amount," +
" aa.transaction_currency," +
" decode(aa.reversal_flag,'R',-aa.billing_amount,decode(aa.transaction_code,'09',-aa.billing_amount,aa.billing_amount)) as billing_amount," +
" nvl((" +
" select sum(decode(dd.reversal_flag,'R',-dd.billing_amount,decode(dd.transaction_code,'09',-dd.billing_amount,dd.billing_amount)))" +
" from " + Businessbp.executedb.owner + "transaction_hist dd" +
" where dd.microfilm_ref_number=aa.microfilm_ref_number" +
" and dd.transaction_code = '22'" +
" and dd.card_number = aa.card_number" +
" and nvl(dd.reversal_flag,'N') = nvl(aa.reversal_flag,'N')" +
" ),0) as Phi," +
" aa.reversal_flag," +
" ee.company_name as merchant_acronym" +
" from " + Businessbp.executedb.owner + "transaction_hist aa, " + Businessbp.executedb.owner + "mcc bb, " + Businessbp.executedb.owner + "trans_def_stand cc, ptserv dd, merchant ee" +
" where aa.mcc = bb.mcc_code" +
" and aa.transaction_code = cc.transaction_code"+
" and aa.outlet_number = dd.outlet_number"+
" and dd.merchant_number = ee.merchant_number"+
" AND aa.network_code = '22'";
if (cbBanknet.Checked)
sql += " and ee.chain_code <> '005'";
else
sql += " and ee.chain_code = '005'";
sql += " and aa.acquirer_bank_code = '970428'" +
" and aa.issuer_bank_code = '970428'" +
" and aa.mcc <> '6011'" +
" and aa.transaction_code <> '22'" +//------------------
where;
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
a.dt = dtt;
if (cbBanknet.Checked)
a.title = "BÁO CÁO CHI TIẾT GIAO DỊCH THẺ NAB SỬ DỤNG TẠI POS NAB";
else
a.title = "BÁO CÁO CHI TIẾT GIAO DỊCH THẺ BANKNETVN/SMARTLINK SỬ DỤNG TẠI POS NAB";
a.ShowDialog();
}
#endregion R4
#region R5
else if (comboBox1.SelectedIndex + 1 == 5)
{
//Bao cao 5
frmShowReport a = new frmShowReport();
a.type = "DC_5";
a.thoiGian = "Từ " + dtFrom.Text + " " + tbFh.Text + ":" + tbFm.Text + ":" + tbFs.Text + " đến " + dtTo.Text + " " + tbTh.Text + ":" + tbTm.Text + ":" + tbTs.Text;
string sql = "", err = "";
string where = " and aa.transaction_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and aa.transaction_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')";
sql = "select decode(substr(aa.processing_code,1,2),'31','GIAO DICH PHI TAI CHINH','91','GIAO DICH PHI TAI CHINH','GIAO DICH TAI CHINH') AS TYPE," +
" aa.transaction_date, " +
" aa.card_number, " +
" aa.trace_audit_number," +
" aa.authorization_number," +
" bb.description as Loai_TB," +
" cc.transaction_wording as Loai_GD," +
" decode(aa.reversal_flag,'R',-aa.transaction_amount,aa.transaction_amount) as transaction_amount," +
" aa.transaction_currency," +
" decode(aa.reversal_flag,'R',-aa.billing_amount,aa.billing_amount) as billing_amount," +
" nvl((" +
" select sum(decode(dd.reversal_flag,'R',-dd.billing_amount,dd.billing_amount))" +
" from " + Businessbp.executedb.owner + "transaction_hist dd" +
" where dd.microfilm_ref_number=aa.microfilm_ref_number" +
" and dd.transaction_code = '22'" +
" and dd.card_number = aa.card_number" +
" and nvl(dd.reversal_flag,'N') = nvl(aa.reversal_flag,'N')" +
" ),0) as Phi," +
" aa.reversal_flag," +
" aa.merchant_acronym" +
" from " + Businessbp.executedb.owner + "transaction_hist aa, " + Businessbp.executedb.owner + "mcc bb, " + Businessbp.executedb.owner + "trans_def_stand cc" +
" where aa.mcc = bb.mcc_code" +
" and substr(aa.processing_code,1,2) = cc.transaction_code";
if (cbBanknet.Checked)
sql += " and aa.network_code = '22'";
else
sql += " and aa.network_data = '27'";
sql += " and aa.acquirer_bank_code= '970428'" +
" and aa.issuer_bank_code = '970428'" +
" and aa.mcc = '6011'" +
" and aa.transaction_code <> '22'" +
where;
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
a.dt = dtt;
if (cbBanknet.Checked)
a.title = "BÁO CÁO CHI TIẾT GIAO DỊCH THẺ NAB SỬ DỤNG TẠI ATM NAB";
else
a.title = "BÁO CÁO CHI TIẾT GIAO DỊCH THẺ BANKNETVN/SMARTLINK SỬ DỤNG TẠI ATM NAB";
a.ShowDialog();
}
#endregion R5
#region R6
else if (comboBox1.SelectedIndex + 1 == 6)
{
//Bao cao 6
frmShowReport a = new frmShowReport();
a.type = "DC_6";
a.thoiGian = "Từ " + dtFrom.Text + " " + tbFh.Text + ":" + tbFm.Text + ":" + tbFs.Text + " đến " + dtTo.Text + " " + tbTh.Text + ":" + tbTm.Text + ":" + tbTs.Text;
string sql = "", err = "";
string title2 = " ";
string where = " and aa.transaction_local_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss') ";
if(!checkBox1.Checked)
where += " and aa.transaction_local_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss') ";
else
where += " and aa.date_create <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss') ";
// Tont: 14122010 them dk loai gd void
string loaivoid = " and not exists (select *" +
" from " + Businessbp.executedb.owner + "autho_activity nn" +
" where nn.message_type = '1200'" +
" and substr(nn.processing_code,1,2) = '02'" +
" and nn.function_code = '200'" +
" and nn.code_action = '000'" +
" and nn.card_number = aa.card_number" +
" and nn.transaction_amount=aa.transaction_amount"+
" and (nn.trace_audit_number = aa.trace_audit_number or nn.reference_number = aa.reference_number))";
// Tont: 14122010
// Tont: 24052011: them dk 91: GIAO DICH PHI TAI CHINH
//Dat note ngay 18/06/2012: cho OPUS sua lai
//sql = "select private_data_c_40 as Type," +
//End Dat note ngay 18/06/2012: cho OPUS sua lai
string sql_select = "select decode(aa.card_acceptor_activity,'6011','ATM','POS') as Type," +
" decode(Substr(aa.processing_code,1,2),'30','GIAO DICH PHI TAI CHINH','40','GIAO DICH PHI TAI CHINH','45','GIAO DICH PHI TAI CHINH','31','GIAO DICH PHI TAI CHINH','35','GIAO DICH PHI TAI CHINH','91','GIAO DICH PHI TAI CHINH','GIAO DICH TAI CHINH') AS LOAI_gd," +
" sum(decode(aa.message_type,'1420',-aa.billing_amount,aa.billing_amount)) as SoTien," +
" count(*) as Record," +
" null as Phi" ;
string sql_from= " from " + Businessbp.executedb.owner + "autho_activity aa";
if (cbBanknet.Checked)
{
//NAB dung tai BNK
sql_from +="," + Businessbp.executedb.owner + "card bb";
sql=sql_select+sql_from;
sql += " where aa.network_code = '22'" +
" and aa.card_number=bb.card_number" +
" and (aa.network_data = '23' or rtrim(aa.private_data_c_100,chr(0)) = 'BNI')" +
" and aa.acquirer_institution_code <> '970428'" +
" and aa.issuing_bank = '970428'"+
" and " + Businessbp.executedb.owner + "reversalyn(aa.card_number,aa.reference_number,aa.trace_audit_number)='N'"+
" and aa.message_type <> '1420'";
if (rbOnlyDebit.Checked)//Chi Debit
{
sql += " " +
//Dat them 20/06/2012: loai bo the Credit va Prepaid
" and bb.product_code in (select cc.product_code from " + Businessbp.executedb.owner + "nab_phanloaisp cc where cc.type='D' and cc.bank_code='970428')";
title2 = "THẺ DEBIT";
//End Dat them 20/06/2012: loai bo the Credit va Prepaid
}
else
{
if (rbPrepaidCredit.Checked)//Chỉ Prepaid va Credit
{
sql += " and bb.product_code in (select cc.product_code from " + Businessbp.executedb.owner + "nab_phanloaisp cc where cc.type in ('C','P') and cc.bank_code='970428')";
title2 = "THẺ PREPAID VÀ CREDIT";
}
else
{
title2 = "THẺ DEBIT, PREPAID VÀ CREDIT";
}
}
}
else
{
//BNK dung tai NAB
sql=sql_select+sql_from;
sql += " where aa.network_code = '23'"+
" and rtrim(aa.network_data,chr(0)) is NULL"+
" and aa.acquirer_institution_code = '970428'"+
" and aa.issuing_bank <> '970428'"+
" and " + Businessbp.executedb.owner + "reversalyn(aa.card_number,aa.reference_number,aa.trace_audit_number)='N'" +
" and aa.message_type <> '1420'";
title2 = " ";
}
sql += " and aa.code_action = '000'" +
loaivoid +
where+
" group by aa.card_acceptor_activity," +
" decode(Substr(aa.processing_code,1,2),'30','GIAO DICH PHI TAI CHINH','40','GIAO DICH PHI TAI CHINH','45','GIAO DICH PHI TAI CHINH','31','GIAO DICH PHI TAI CHINH','35','GIAO DICH PHI TAI CHINH','91','GIAO DICH PHI TAI CHINH','GIAO DICH TAI CHINH')";
//textBox1.Text = sql;
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
a.dt = dtt;
if (cbBanknet.Checked)
{
a.title = "BÁO CÁO TỔNG KẾT GIAO DỊCH THẺ NAB SỬ DỤNG TẠI BANKNET";
//a.title2 = title2;
}
else
a.title = "BÁO CÁO TỔNG KẾT GIAO DỊCH THẺ BANKNET SỬ DỤNG TẠI NAB";
a.title2 = title2;
a.ShowDialog();
}
#endregion R6
#region R7
else if (comboBox1.SelectedIndex + 1 == 7)
{
//Bao cao 7
frmShowReport a = new frmShowReport();
a.type = "DC_7";
a.thoiGian = a.thoiGian = "Từ " + dtFrom.Text + " đến " + dtTo.Text;
string sql = "", err = "";
string where = " and to_date(decode(substr(aa.file_name,1,1),'I',substr(aa.file_name,2,6)," +
" decode(substr(aa.file_name,1,1),'O',substr(aa.file_name,2,6)," +
" (substr(aa.file_name,1,6)))),'mmddyy') " +
" >= to_date('" + dtFrom.Text.Trim() + "','dd/mm/yyyy')" +
" and to_date(decode(substr(aa.file_name,1,1),'I',substr(aa.file_name,2,6)," +
" decode(substr(aa.file_name,1,1),'O',substr(aa.file_name,2,6)," +
" (substr(aa.file_name,1,6)))),'mmddyy') " +
" <= to_date('" + dtTo.Text.Trim() + "','dd/mm/yyyy')";
//" and to_date(substr(aa.file_name,1,6),'mmddyy') >= to_date('" + dtFrom.Text.Trim() + "','dd/mm/yyyy')" +
//" and to_date(substr(aa.file_name,1,6),'mmddyy') <= to_date('" + dtTo.Text.Trim() + "','dd/mm/yyyy') ";
if (cbBanknet.Checked)
sql = "select hh.Ngay_GD, hh.SL_PHAITHU, hh.PHAI_THU, hh.SL_PHAITRA, hh.PHAI_TRA," +
" (case " +
" when (PHAI_THU-PHAI_TRA) > 0 then PHAI_THU-PHAI_TRA" +
" else" +
" null" +
" end" +
" ) as CL_PHAITHU," +
" (case " +
" when (PHAI_THU-PHAI_TRA) < 0 then -(PHAI_THU-PHAI_TRA)" +
" else" +
" null" +
" end" +
" ) as CL_PHAITRA" +
" from (" +
" select " +
//Dat sua
" decode(substr(aa.file_name,1,1),'I',substr(aa.file_name,2,6)," +
" decode(substr(aa.file_name,1,1),'O',substr(aa.file_name,2,6)," +
" (substr(aa.file_name,1,6)))) as Ngay_GD," +
//" substr(aa.file_name,1,6) as Ngay_GD," +
" sum(decode(substr(aa.processing_code,1,2),'00',1,'20',1,0)) AS SL_PHAITHU, " +
" sum(DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'00',decode(aa.transaction_status,0,'MUA HANG','DAO MUA HANG'),'20',decode(aa.transaction_status,0,'REFUND','DAO REFUND'),'ABC'),1,3),'DAO', -aa.iss_fee,'ABC',0,aa.iss_fee)) as PHAI_THU," +
" sum(decode(substr(aa.processing_code,1,2),'01',1,'30',1,'35',1,'40',1,0)) AS SL_PHAITRA, " +
" sum(DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'01',decode(aa.transaction_status,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'30',decode(aa.transaction_status,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.transaction_status,0,'SAO KE','DAO SAO KE'),'40',decode(aa.transaction_status,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'ABC'),1,3),'DAO', -aa.iss_fee,'ABC',0,aa.iss_fee)) as PHAI_TRA" +
" from " + Businessbp.executedb.owner + "banknet_issuer_transaction aa" +
" where 1=1" +
where +
" group by" +
//Dat sua
" decode(substr(aa.file_name,1,1),'I',substr(aa.file_name,2,6)," +
" decode(substr(aa.file_name,1,1),'O',substr(aa.file_name,2,6)," +
" (substr(aa.file_name,1,6))))" +
//" substr(aa.file_name,1,6)" +
" union all" +
" select " +
//Dat sua
" decode(substr(aa.file_name,1,1),'I',substr(aa.file_name,2,6)," +
" decode(substr(aa.file_name,1,1),'O',substr(aa.file_name,2,6)," +
" (substr(aa.file_name,1,6)))) as Ngay_GD," +
//" substr(aa.file_name,1,6) as Ngay_GD," +
" sum(decode(substr(aa.processing_code,1,2),'01',1,'30',1,'35',1,'40',1,0)) as SL_PHAITHU," +
" sum(DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.response_code,0,'SAO KE','DAO SAO KE'),'40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'ABC'),1,3),'DAO', -aa.acq_fee,'ABC',0,aa.acq_fee)) as PHAI_THU," +
" sum(decode(substr(aa.processing_code,1,2),'00',1,'20',1,0)) as SL_PHAITRA," +
//05/10/2011 --> " sum(DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'00',decode(aa.response_code,0,'MUA HANG','DAO MUA HANG'),'20',decode(aa.response_code,0,'REFUND','DAO REFUND'),'ABC'),1,3),'DAO', -aa.acq_fee,'ABC',0,aa.acq_fee)) as PHAI_TRA" +
//05/10/2011
" sum(DECODE(substr(aa.processing_code,1,2),'00',decode(aa.response_code,0,AA.acq_fee,'110',aa.acq_fee,-AA.acq_fee)," +
" '20',decode(aa.response_code,0,AA.acq_fee,'110',aa.acq_fee,-AA.acq_fee)," +
" 0"+
" )) as PHAI_TRA"+
" from " + Businessbp.executedb.owner + "banknet_acquirer_transaction aa" +
" where 1=1" +
where +
" group by " +
//Dat sua
" decode(substr(aa.file_name,1,1),'I',substr(aa.file_name,2,6)," +
" decode(substr(aa.file_name,1,1),'O',substr(aa.file_name,2,6)," +
" (substr(aa.file_name,1,6))))" +
//" substr(aa.file_name,1,6)" +
" ) hh" +
" order by Ngay_GD asc";
else
sql = "select hh.Ngay_GD, hh.SL_PHAITHU, hh.PHAI_THU, hh.SL_PHAITRA, hh.PHAI_TRA," +
" (case " +
" when (PHAI_THU-PHAI_TRA) > 0 then PHAI_THU-PHAI_TRA" +
" else" +
" null" +
" end" +
" ) as CL_PHAITHU," +
" (case " +
" when (PHAI_THU-PHAI_TRA) < 0 then -(PHAI_THU-PHAI_TRA)" +
" else" +
" null" +
" end" +
" ) as CL_PHAITRA" +
" from (" +
" select " +
//Dat sua
" decode(substr(aa.file_name,1,1),'I',substr(aa.file_name,2,6)," +
" decode(substr(aa.file_name,1,1),'O',substr(aa.file_name,2,6)," +
" (substr(aa.file_name,1,6)))) as Ngay_GD," +
//" substr(aa.file_name,1,6) as Ngay_GD," +
" sum(decode(substr(aa.processing_code,1,2),'00',1,'20',1,0)) AS SL_PHAITHU, " +
" sum(DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'00',decode(aa.response_code,0,'MUA HANG','DAO MUA HANG'),'20',decode(aa.response_code,0,'REFUND','DAO REFUND'),'ABC'),1,3),'DAO', -decode(mcc,'6011',decode(transaction_amount,0,1650,3300),0),'ABC',0,decode(mcc,'6011',decode(transaction_amount,0,1650,3300),0))) as PHAI_THU," +
" sum(decode(substr(aa.processing_code,1,2),'01',1,'30',1,'35',1,'40',1,0)) AS SL_PHAITRA, " +
" sum(DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.response_code,0,'SAO KE','DAO SAO KE'),'40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'ABC'),1,3),'DAO', -decode(mcc,'6011',decode(transaction_amount,0,1650,3300),0),'ABC',0,decode(mcc,'6011',decode(transaction_amount,0,1650,3300),0))) as PHAI_TRA" +
" from " + Businessbp.executedb.owner + "sml_issuer_transaction aa" +
" where 1=1" +
where +
" group by" +
//Dat sua
" decode(substr(aa.file_name,1,1),'I',substr(aa.file_name,2,6)," +
" decode(substr(aa.file_name,1,1),'O',substr(aa.file_name,2,6)," +
" (substr(aa.file_name,1,6))))" +
//" substr(aa.file_name,1,6)" +
" union all" +
" select " +
//Dat sua
" decode(substr(aa.file_name,1,1),'I',substr(aa.file_name,2,6)," +
" decode(substr(aa.file_name,1,1),'O',substr(aa.file_name,2,6)," +
" (substr(aa.file_name,1,6)))) as Ngay_GD," +
//" substr(aa.file_name,1,6) as Ngay_GD," +
" sum(decode(substr(aa.processing_code,1,2),'01',1,'30',1,'35',1,'40',1,0)) as SL_PHAITHU," +
" sum(DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'30',decode(aa.response_code,0,'VAN TIN','DAO VAN TIN'),'35',decode(aa.response_code,0,'SAO KE','DAO SAO KE'),'40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'ABC'),1,3),'DAO', -decode(mcc,'6011',decode(transaction_amount,0,550,2200),150),'ABC',0,decode(mcc,'6011',decode(transaction_amount,0,550,2200),150))) as PHAI_THU," +
" sum(decode(substr(aa.processing_code,1,2),'00',1,'20',1,0)) as SL_PHAITRA," +
" sum(DECODE(SUBSTR(decode(substr(aa.processing_code,1,2),'00',decode(aa.response_code,0,'MUA HANG','DAO MUA HANG'),'20',decode(aa.response_code,0,'REFUND','DAO REFUND'),'ABC'),1,3),'DAO', -decode(mcc,'6011',decode(transaction_amount,0,550,2200),150),'ABC',0,decode(mcc,'6011',decode(transaction_amount,0,550,2200),150))) as PHAI_TRA" +
" from " + Businessbp.executedb.owner + "sml_acq_transaction aa" +
" where 1=1" +
where +
" group by " +
//Dat sua
" decode(substr(aa.file_name,1,1),'I',substr(aa.file_name,2,6)," +
" decode(substr(aa.file_name,1,1),'O',substr(aa.file_name,2,6)," +
" (substr(aa.file_name,1,6))))" +
//" substr(aa.file_name,1,6)" +
" ) hh" +
" order by Ngay_GD asc";
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
a.dt = dtt;
if (cbBanknet.Checked)
a.title = "BÁO CÁO TỔNG HỢP PHÍ GIAO DỊCH BANKNETVN";
else
a.title = "BÁO CÁO TỔNG HỢP PHÍ GIAO DỊCH SMARTLINK";
a.ShowDialog();
}
#endregion R7
#region R8
else if (comboBox1.SelectedIndex + 1 == 8)
{
//Bao cao 8
frmShowReport a = new frmShowReport();
a.type = "DC_8";
a.thoiGian = a.thoiGian = "Từ " + dtFrom.Text + " đến " + dtTo.Text;
string sql = "", err = "";
string where = " and to_date(decode(substr(aa.file_name,1,1),'I',substr(aa.file_name,2,6)," +
" decode(substr(aa.file_name,1,1),'O',substr(aa.file_name,2,6)," +
" (substr(aa.file_name,1,6)))),'mmddyy') " +
" >= to_date('" + dtFrom.Text.Trim() + "','dd/mm/yyyy')" +
" and to_date(decode(substr(aa.file_name,1,1),'I',substr(aa.file_name,2,6)," +
" decode(substr(aa.file_name,1,1),'O',substr(aa.file_name,2,6)," +
" (substr(aa.file_name,1,6)))),'mmddyy') " +
" <= to_date('" + dtTo.Text.Trim() + "','dd/mm/yyyy')";
//" and to_date(substr(aa.file_name,1,6),'mmddyy') >= to_date('" + dtFrom.Text.Trim() + "','dd/mm/yyyy')" +
//" and to_date(substr(aa.file_name,1,6),'mmddyy') <= to_date('" + dtTo.Text.Trim() + "','dd/mm/yyyy') ";
if(cbBanknet.Checked)
sql = "select distinct aa.transaction_local_date, aa.acquirer_institution_code, bb.TENNGANHANG, aa.card_number, aa.trace_audit_number, aa.authorization_number," +
" decode(aa.mcc,'6011','ATM','POS') as LOAI_TB, " +
" decode(substr(aa.processing_code,1,2),'01',decode(aa.transaction_status,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.transaction_status,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.transaction_status,0,'MUA HANG','DAO MUA HANG'),'30','VAN TIN','35','SAO KE') as LOAI_GD," +
" aa.transaction_amount, aa.currency_code, aa.iss_fee, aa.banknetvn_fee, aa.acq_fee, aa.card_acceptor_term_id" +
" from " + Businessbp.executedb.owner + "banknet_issuer_transaction aa, " + Businessbp.executedb.owner + "bnk_bank bb " +
" where aa.transaction_status in (115)"+
" and trim(aa.acquirer_institution_code) = trim(bb.sobintronghtbc)"+
where;
else
sql = "select distinct aa.transaction_local_date, aa.acquirer_institution_code, bb.TENNGANHANG, aa.card_number, aa.trace_audit_number, aa.authorization_number," +
" decode(aa.mcc,'6011','ATM','POS') as LOAI_TB, " +
" decode(substr(aa.processing_code,1,2),'01',decode(aa.response_code,0,'RUT TIEN ATM','DAO RUT TIEN ATM'),'40',decode(aa.response_code,0,'CHUYEN KHOAN','DAO CHUYEN KHOAN'),'00',decode(aa.response_code,0,'MUA HANG','DAO MUA HANG'),'30','VAN TIN','35','SAO KE') as LOAI_GD," +
" aa.transaction_amount, aa.currency_code, decode(mcc,'6011',decode(transaction_amount,0,1650,3300),0), decode(mcc,'6011',decode(transaction_amount,0,1100,1100),150), decode(mcc,'6011',decode(transaction_amount,0,550,2200),150), aa.card_acceptor_term_id" +
" from " + Businessbp.executedb.owner + "sml_issuer_transaction aa, " + Businessbp.executedb.owner + "dsnganhang_smartlink bb " +
" where aa.response_code in (118,115)" +
" and trim(aa.acquirer_institution_code) = trim(bb.bank_id)" +
where;
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
a.dt = dtt;
if (cbBanknet.Checked)
a.title = "BÁO CÁO GIAO DỊCH ĐẢO NOT ONUS THEO TỔ CHỨC THANH TOÁN-BANKNETVN";
else
a.title = "BÁO CÁO GIAO DỊCH ĐẢO NOT ONUS THEO TỔ CHỨC THANH TOÁN-SMARTLINK";
a.ShowDialog();
}
#endregion R8
#region R9
else if (comboBox1.SelectedIndex + 1 == 9)
{
MessageBox.Show("Don't support!!!");
return;
//Bao cao 9
frmShowReport a = new frmShowReport();
a.type = "DC_9";
a.thoiGian = "Từ " + dtFrom.Text + " " + tbFh.Text + ":" + tbFm.Text + ":" + tbFs.Text + " đến " + dtTo.Text + " " + tbTh.Text + ":" + tbTm.Text + ":" + tbTs.Text;
string sql = "", err = "";
string where = " and aa.transaction_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and aa.transaction_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')";
sql = "select decode(substr(aa.product_code,1,2),'31','GIAO DICH PHI TAI CHINH','91','GIAO DICH PHI TAI CHINH','GIAO DICH TAI CHINH') AS type," +
" DECODE(substr(aa.processing_code,1,2)," +
" '17',DECODE(aa.reversal_flag,'R','DAO RUT TIEN ATM','RUT TIEN ATM')," +
" '18',DECODE(aa.reversal_flag,'R','DAO CHUYEN KHOAN','CHUYEN KHOAN')," +
" '40',DECODE(aa.reversal_flag,'R','DAO CHUYEN KHOAN','CHUYEN KHOAN')," +
" '44',DECODE(aa.reversal_flag,'R','DAO CHUYEN KHOAN','CHUYEN KHOAN')," +
" '48',DECODE(aa.reversal_flag,'R','DAO CHUYEN KHOAN','CHUYEN KHOAN')," +
" '01',decode(aa.reversal_flag,'R','DAO RUT TIEN POS','RUT TIEN POS')," +
" '00',decode(aa.reversal_flag,'R','DAO MUA HANG','MUA HANG'), " +
" '02',decode(aa.reversal_flag,'R','DAO VOID-POS','VOID-POS')," +
" '21',decode(aa.reversal_flag,'R','DAO debit adjustment-POS','Debit adjustment-POS')," +
" '22',decode(aa.reversal_flag,'R','DAO Credit adjustment-POS','Credit adjustment-POS')," +
" '31','BALANCE INQUIRY'," +
" '91','MINI STATEMENT'" +
" ) AS loai_gd," +
" count(*) as SL_GD," +
" sum(aa.billing_amount) as ST_GD," +
" sum(" +
" nvl((select bb.billing_amount" +
" from " + Businessbp.executedb.owner + "transaction_hist bb" +
" where bb.microfilm_ref_number = aa.microfilm_ref_number" +
" and bb.transaction_code = '22'),0)" +
" ) as Phi " +
" from " + Businessbp.executedb.owner + "transaction_hist aa";
//if (cbBanknet.Checked)
// sql += " where aa.network_data = '23'";
//else
// sql += " where aa.network_data = '27'";
sql += " where aa.issuer_bank_code = '970428'"+
" and aa.acquirer_institution_code_code = '970428'"+
" and aa.transaction_code <> '22'"+
where+
" group by decode(substr(aa.product_code,1,2),'31','GIAO DICH PHI TAI CHINH','91','GIAO DICH PHI TAI CHINH','GIAO DICH TAI CHINH'),"+
" DECODE(substr(aa.processing_code,1,2),"+
" '17',DECODE(aa.reversal_flag,'R','DAO RUT TIEN ATM','RUT TIEN ATM'),"+
" '18',DECODE(aa.reversal_flag,'R','DAO CHUYEN KHOAN','CHUYEN KHOAN'),"+
" '40',DECODE(aa.reversal_flag,'R','DAO CHUYEN KHOAN','CHUYEN KHOAN'),"+
" '44',DECODE(aa.reversal_flag,'R','DAO CHUYEN KHOAN','CHUYEN KHOAN'),"+
" '48',DECODE(aa.reversal_flag,'R','DAO CHUYEN KHOAN','CHUYEN KHOAN'),"+
" '01',decode(aa.reversal_flag,'R','DAO RUT TIEN POS','RUT TIEN POS'),"+
" '00',decode(aa.reversal_flag,'R','DAO MUA HANG','MUA HANG'), "+
" '02',decode(aa.reversal_flag,'R','DAO VOID-POS','VOID-POS'),"+
" '21',decode(aa.reversal_flag,'R','DAO debit adjustment-POS','Debit adjustment-POS'),"+
" '22',decode(aa.reversal_flag,'R','DAO Credit adjustment-POS','Credit adjustment-POS'),"+
" '31','BALANCE INQUIRY',"+
" '91','MINI STATEMENT'"+
" )";
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
a.dt = dtt;
if (cbBanknet.Checked)
a.title = "BÁO CÁO GIAO DỊCH THẺ BANKNET/SMARTLINK SỬ DỤNG TẠI NAB";
else
a.title = "BÁO CÁO GIAO DỊCH THẺ BANKNET/SMARTLINK SỬ DỤNG TẠI NAB";
a.ShowDialog();
}
#endregion R9
#region R10
else if (comboBox1.SelectedIndex + 1 == 10)
{
//Bao cao 10
frmShowReport a = new frmShowReport();
a.type = "DC_10";
a.thoiGian = "Từ " + dtFrom.Text + " " + tbFh.Text + ":" + tbFm.Text + ":" + tbFs.Text + " đến " + dtTo.Text + " " + tbTh.Text + ":" + tbTm.Text + ":" + tbTs.Text;
string sql = "", err = "";
if(cbBanknet.Checked)
sql = //"--co electra, ko co banknet
"select distinct aa.acquirer_institution_code, ff.TENNGANHANG, aa.transaction_local_date, aa.card_number, aa.trace_audit_number, aa.authorization_code, aa.private_data_c_40 as Loai_TB, decode(aa.message_type,'1420','REVERSAL-','')||bb.description as Loai_GD," +
" decode(aa.message_type,'1420',-aa.transaction_amount,aa.transaction_amount) as transaction_amount, aa.transaction_currency, "+
" null as iss_fee, "+
" null as banknetvn_fee, "+
" null as acq_fee" +
" from " + Businessbp.executedb.owner + "autho_activity aa, " + Businessbp.executedb.owner + "processing_code_map bb, " + Businessbp.executedb.owner + "bnk_bank ff" +
" where substr(aa.processing_code,1,2) = trim(bb.processing_code)" +
" and SUBSTR(trim(aa.card_number),1,6) = trim(ff.SODAUCUATHE)" +
" and (aa.network_data = '23' or rtrim(aa.private_data_c_100,chr(0)) = 'BNI')" +
" and aa.network_code = '22'" +
" and aa.code_action = '000'" +
" and " + Businessbp.executedb.owner + "reversalYN(aa.card_number,aa.reference_number, aa.trace_audit_number)='N'" +
" and aa.message_type <> '1420'"+
" and aa.acquirer_institution_code <> '970428'" +
" and aa.issuing_bank = '970428'" +
//" and rtrim(aa.authorization_code,chr(0)) is not NULL" +
" and aa.transaction_local_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and aa.transaction_local_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and (trim(aa.card_number), trim(aa.trace_audit_number), aa.transaction_amount, aa.transaction_local_date, trim(aa.acquirer_institution_code)) not in" +
" (" +
" select trim(bb.card_number), trim(bb.trace_audit_number), bb.transaction_amount, bb.transaction_local_date, trim(bb.acquirer_institution_code)" +
" from " + Businessbp.executedb.owner + "banknet_issuer_transaction bb" +
" where bb.transaction_local_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and bb.transaction_local_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and bb.transaction_status = 0"+
" )"+
" order by transaction_local_date asc";
else
sql = //"--co electra, ko co banknet
"select distinct aa.acquirer_institution_code, ff.bank_name as TENNGANHANG, aa.transaction_local_date, aa.card_number, aa.trace_audit_number, aa.authorization_code, aa.private_data_c_40 as Loai_TB, decode(aa.message_type,'1420','REVERSAL-','')||bb.description as Loai_GD," +
" decode(aa.message_type,'1420',-aa.transaction_amount,aa.transaction_amount) as transaction_amount, aa.transaction_currency, " +
" null as iss_fee, " +
" null as banknetvn_fee, " +
" null as acq_fee" +
" from " + Businessbp.executedb.owner + "autho_activity aa, " + Businessbp.executedb.owner + "processing_code_map bb, " + Businessbp.executedb.owner + "dsnganhang_smartlink ff" +
" where substr(aa.processing_code,1,2) = trim(bb.processing_code)" +
" and trim(aa.acquirer_institution_code) = trim(ff.bank_id)" +
//" and aa.network_data = '23'" +
" and aa.code_action = '000'" +
" and aa.acquirer_institution_code <> '970428'" +
" and aa.issuing_bank = '970428'" +
//" and rtrim(aa.authorization_code,chr(0)) is not NULL" +
" and aa.transaction_local_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and aa.transaction_local_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and (trim(aa.card_number), trim(aa.trace_audit_number), aa.transaction_amount, aa.transaction_local_date, trim(aa.acquirer_institution_code)) not in" +
" (" +
" select trim(bb.card_number), trim(bb.trace_audit_number), bb.transaction_amount, bb.transaction_local_date, trim(bb.acquirer_institution_code)" +
" from " + Businessbp.executedb.owner + "sml_issuer_transaction bb" +
" where bb.transaction_local_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and bb.transaction_local_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" )" +
" order by transaction_local_date asc";
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
a.dt = dtt;
if (cbBanknet.Checked)
a.title = "BÁO CÁO CHI TIẾT GIAO DỊCH SAI LỆCH - CÓ ELECTRA KHÔNG CÓ BANKNET";
else
a.title = "BÁO CÁO CHI TIẾT GIAO DỊCH SAI LỆCH - CÓ ELECTRA KHÔNG CÓ SMARTLINK";
a.ShowDialog();
}
#endregion R10
#region R11
else if (comboBox1.SelectedIndex + 1 == 11)
{
//Bao cao 11
frmShowReport a = new frmShowReport();
a.type = "DC_11";
a.thoiGian = "Từ " + dtFrom.Text + " " + tbFh.Text + ":" + tbFm.Text + ":" + tbFs.Text + " đến " + dtTo.Text + " " + tbTh.Text + ":" + tbTm.Text + ":" + tbTs.Text;
string sql = "", err = "";
if(cbBanknet.Checked)
sql = //--ko co elctra, co banknet
" select distinct cc.acquirer_institution_code, ff.TENNGANHANG, cc.transaction_local_date, cc.card_number, cc.trace_audit_number, cc.authorization_number as authorization_code, decode(cc.mcc,'6011','ATM','POS') as Loai_TB, decode(cc.transaction_status,115,'REVERSAL-','111','REVERSAL-','')||decode(substr(cc.processing_code,1,2),'01','Withdraw','30','Balance Inquiry','35','Mini statement','40','Fund transfer','00','Purchase','20','Refund') as Loai_GD," +
" decode(cc.transaction_status,115,-cc.transaction_amount,cc.transaction_amount) as transaction_amount, cc.currency_code as transaction_currency, "+
" decode(cc.MCC, '6011', decode(cc.transaction_status,115,cc.iss_fee,-iss_fee),decode(cc.transaction_status,115,-cc.iss_fee,iss_fee)) as iss_fee, " +
" decode(cc.transaction_status,115,-cc.banknetvn_fee,banknetvn_fee) as banknetvn_fee, "+
" decode(cc.MCC, '6011', decode(cc.transaction_status,115,-cc.acq_fee,acq_fee),decode(cc.transaction_status,115,cc.acq_fee,-acq_fee)) as acq_fee" +
" from " + Businessbp.executedb.owner + "banknet_issuer_transaction cc, " + Businessbp.executedb.owner + "bnk_bank ff" +
" where SUBSTR(trim(cc.card_number),1,6) = trim(ff.SODAUCUATHE)" +
" and cc.transaction_status = 0" +
" and (trim(cc.card_number), trim(cc.trace_audit_number), cc.transaction_amount, cc.transaction_local_date, trim(cc.acquirer_institution_code)) not in" +
" (" +
" select trim(dd.card_number), trim(dd.trace_audit_number), dd.transaction_amount, dd.transaction_local_date, trim(dd.acquirer_institution_code)" +
" from " + Businessbp.executedb.owner + "autho_activity dd" +
" where dd.code_action = '000'" +
" and " + Businessbp.executedb.owner + "reversalYN(dd.card_number,dd.reference_number, dd.trace_audit_number)='N'" +
" and (dd.network_data = '23' or rtrim(dd.private_data_c_100,chr(0)) = 'BNI')" +
" and dd.network_code = '22'" +
//--Phuong add
" and dd.card_number = cc.card_number"+
" and trim(dd.trace_audit_number) = trim(cc.trace_audit_number)"+
" and dd.transaction_local_date = cc.transaction_local_date"+
" and dd.transaction_amount = cc.transaction_amount"+
" and dd.message_type <> '1420'"+
//--End Phuong add
" and dd.acquirer_institution_code <> '970428'" +
" and dd.issuing_bank = '970428'" +
" and dd.transaction_local_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and dd.transaction_local_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" )" +
" and cc.transaction_local_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and cc.transaction_local_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" order by transaction_local_date asc";
else
sql = //--ko co elctra, co banknet
" select distinct cc.acquirer_institution_code, ff.bank_id as TENNGANHANG, cc.transaction_local_date, cc.card_number, cc.trace_audit_number, cc.authorization_number as authorization_code, decode(cc.mcc,'6011','ATM','POS') as Loai_TB, decode(cc.transaction_status,115,'REVERSAL-','118','REVERSAL-','')||decode(substr(cc.processing_code,1,2),'01','Withdraw','30','Balance Inquiry','35','Mini statement','40','Fund transfer','00','Purchase','20','Refund') as Loai_GD," +
" decode(cc.transaction_status,115,-cc.transaction_amount,cc.transaction_amount) as transaction_amount, cc.currency_code as transaction_currency, " +
" decode(cc.MCC, '6011', decode(cc.transaction_status,115,decode(cc.mcc,'6011',decode(cc.transaction_amount,0,1650,3300),0),-decode(cc.mcc,'6011',decode(cc.transaction_amount,0,1650,3300),0)),decode(cc.transaction_status,115,-decode(cc.mcc,'6011',decode(cc.transaction_amount,0,1650,3300),0),decode(cc.mcc,'6011',decode(cc.transaction_amount,0,1650,3300),0))) as iss_fee, " +
" decode(cc.transaction_status,115,-decode(cc.mcc,'6011',decode(cc.transaction_amount,0,1100,1100),150),decode(cc.mcc,'6011',decode(cc.transaction_amount,0,1100,1100),150)) as banknetvn_fee, " +
" decode(cc.MCC, '6011', decode(cc.transaction_status,115,-decode(cc.mcc,'6011',decode(cc.transaction_amount,0,550,2200),150),decode(cc.mcc,'6011',decode(cc.transaction_amount,0,550,2200),150)),decode(cc.transaction_status,115,decode(cc.mcc,'6011',decode(cc.transaction_amount,0,550,2200),150),-decode(cc.mcc,'6011',decode(cc.transaction_amount,0,550,2200),150))) as acq_fee" +
" from " + Businessbp.executedb.owner + "banknet_issuer_transaction cc, " + Businessbp.executedb.owner + "dsnganhang_smartlink ff" +
" where trim(cc.acquirer_institution_code) = trim(ff.bank_id)" +
" and (trim(cc.card_number), trim(cc.trace_audit_number), cc.transaction_amount, cc.transaction_local_date, trim(cc.acquirer_institution_code)) not in" +
" (" +
" select trim(dd.card_number), trim(dd.trace_audit_number), dd.transaction_amount, dd.transaction_local_date, trim(dd.acquirer_institution_code)" +
" from " + Businessbp.executedb.owner + "autho_activity dd" +
" where dd.code_action = '000'" +
" and dd.network_code = '22'" +
" and dd.acquirer_institution_code <> '970428'" +
" and dd.issuing_bank = '970428'" +
" and dd.transaction_local_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and dd.transaction_local_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" )" +
" and cc.transaction_local_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and cc.transaction_local_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" order by transaction_local_date asc";
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
a.dt = dtt;
if (cbBanknet.Checked)
a.title = "BÁO CÁO CHI TIẾT GIAO DỊCH SAI LỆCH - KHÔNG CÓ ELECTRA CÓ BANKNET";
else
a.title = "BÁO CÁO CHI TIẾT GIAO DỊCH SAI LỆCH - KHÔNG CÓ ELECTRA CÓ SMARTLINK";
a.ShowDialog();
}
#endregion R11
#region R12
else if (comboBox1.SelectedIndex + 1 == 12)
{
//Bao cao 12
frmShowReport a = new frmShowReport();
a.type = "DC_12";
a.thoiGian = "Từ " + dtFrom.Text + " " + tbFh.Text + ":" + tbFm.Text + ":" + tbFs.Text + " đến " + dtTo.Text + " " + tbTh.Text + ":" + tbTm.Text + ":" + tbTs.Text;
string sql = "", err = "";
if(cbBanknet.Checked)
sql = //--co banknet, co elctra
" select distinct cc.acquirer_institution_code, ff.tennganhang, cc.transaction_local_date, cc.card_number, cc.trace_audit_number, cc.authorization_number as authorization_code, decode(cc.mcc,'6011','ATM','POS') as Loai_TB, decode(cc.transaction_status,115,'REVERSAL-','111','REVERSAL-','')||decode(substr(cc.processing_code,1,2),'01','Withdraw','30','Balance Inquiry','35','Mini statement','40','Fund transfer','00','Purchase','20','Refund') as Loai_GD," +
" decode(cc.transaction_status,115,-cc.transaction_amount,cc.transaction_amount) as transaction_amount, cc.currency_code as transaction_currency, "+
" decode(cc.MCC, '6011', decode(cc.transaction_status,115,cc.iss_fee,-iss_fee),decode(cc.transaction_status,115,-cc.iss_fee,iss_fee)) as iss_fee, " +
" decode(cc.transaction_status,115,-cc.banknetvn_fee,banknetvn_fee) as banknetvn_fee, " +
" decode(cc.MCC, '6011', decode(cc.transaction_status,115,-cc.acq_fee,acq_fee),decode(cc.transaction_status,115,cc.acq_fee,-acq_fee)) as acq_fee" +
" from " + Businessbp.executedb.owner + "banknet_issuer_transaction cc, (select distinct kk.sobintrongistswitch, kk.tennganhang "+
"from " + Businessbp.executedb.owner + "bnk_bank kk"+
") ff" +
" where SUBSTR(trim(cc.acquirer_institution_code),1,6) = trim(ff.sobintrongistswitch) " +
" and cc.transaction_status = 0" +
" and (trim(cc.card_number), trim(cc.trace_audit_number), cc.transaction_amount, cc.transaction_local_date, trim(cc.acquirer_institution_code)) in" +
" (" +
" select trim(dd.card_number), trim(dd.trace_audit_number), dd.transaction_amount, dd.transaction_local_date, trim(dd.acquirer_institution_code)" +
" from " + Businessbp.executedb.owner + "autho_activity dd" +
" where dd.code_action = '000'" +
" and " + Businessbp.executedb.owner + "reversalYN(dd.card_number,dd.reference_number,dd.trace_audit_number)='N'" +
" and (dd.network_data = '23' or rtrim(dd.private_data_c_100,chr(0)) = 'BNI')" +
" and dd.network_code = '22'" +
" and dd.acquirer_institution_code <> '970428'" +
" and dd.issuing_bank = '970428'" +
" and dd.transaction_local_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and dd.transaction_local_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" )" +
" and cc.transaction_local_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and cc.transaction_local_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" order by transaction_local_date asc";
else
sql = //--co banknet, co elctra
" select distinct cc.acquirer_institution_code, ff.bank_name as TENNGANHANG, cc.transaction_local_date, cc.card_number, cc.trace_audit_number, cc.authorization_number as authorization_code, decode(cc.mcc,'6011','ATM','POS') as Loai_TB, decode(cc.response_code,115,'REVERSAL-','118','REVERSAL-','')||decode(substr(cc.processing_code,1,2),'01','Withdraw','30','Balance Inquiry','35','Mini statement','40','Fund transfer','00','Purchase','20','Refund') as Loai_GD," +
" decode(cc.response_code,115,-cc.transaction_amount,cc.transaction_amount) as transaction_amount, cc.currency_code as transaction_currency, " +
" decode(cc.MCC, '6011', decode(cc.response_code,115,decode(cc.mcc,'6011',decode(cc.transaction_amount,0,1650,3300),0),-decode(cc.mcc,'6011',decode(cc.transaction_amount,0,1650,3300),0)),decode(cc.response_code,115,-decode(cc.mcc,'6011',decode(cc.transaction_amount,0,1650,3300),0),decode(cc.mcc,'6011',decode(cc.transaction_amount,0,1650,3300),0))) as iss_fee, " +
" decode(cc.response_code,115,-decode(cc.mcc,'6011',decode(cc.transaction_amount,0,1100,1100),150),decode(cc.mcc,'6011',decode(cc.transaction_amount,0,1100,1100),150)) as banknetvn_fee, " +
" decode(cc.MCC, '6011', decode(cc.response_code,115,-decode(cc.mcc,'6011',decode(cc.transaction_amount,0,550,2200),150),decode(cc.mcc,'6011',decode(cc.transaction_amount,0,550,2200),150)),decode(cc.response_code,115,decode(cc.mcc,'6011',decode(cc.transaction_amount,0,550,2200),150),-decode(cc.mcc,'6011',decode(cc.transaction_amount,0,550,2200),150))) as acq_fee" +
" from " + Businessbp.executedb.owner + "sml_issuer_transaction cc, " + Businessbp.executedb.owner + "dsnganhang_smartlink ff" +
" where trim(cc.acquirer_institution_code) = trim(ff.bank_id)" +
" and (trim(cc.card_number), trim(cc.trace_audit_number), cc.transaction_amount, cc.transaction_local_date, trim(cc.acquirer_institution_code)) in" +
" (" +
" select trim(dd.card_number), trim(dd.trace_audit_number), dd.transaction_amount, dd.transaction_local_date, trim(dd.acquirer_institution_code)" +
" from " + Businessbp.executedb.owner + "autho_activity dd" +
" where dd.code_action = '000'" +
" and dd.network_data = '27'" +
" and dd.acquirer_institution_code <> '970428'" +
" and dd.issuing_bank = '970428'" +
" and dd.transaction_local_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and dd.transaction_local_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" )" +
" and cc.transaction_local_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" and cc.transaction_local_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')" +
" order by transaction_local_date asc";
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
a.dt = dtt;
if (cbBanknet.Checked)
a.title = "BÁO CÁO CHI TIẾT GIAO DỊCH - CÓ ELECTRA CÓ BANKNET";
else
a.title = "BÁO CÁO CHI TIẾT GIAO DỊCH - CÓ ELECTRA CÓ SMARTLINK";
a.ShowDialog();
}
#endregion R12
#region R13
else if (comboBox1.SelectedIndex + 1 == 13)
{
//Bao cao 13
frmShowReport a = new frmShowReport();
a.type = "DC_13";
a.thoiGian = "Từ " + dtFrom.Text + " " + tbFh.Text + ":" + tbFm.Text + ":" + tbFs.Text + " đến " + dtTo.Text + " " + tbTh.Text + ":" + tbTm.Text + ":" + tbTs.Text;
string sql = "", err = "";
string where = " and aa.transaction_local_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')";
if (!checkBox1.Checked)
where += " and aa.transaction_local_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss') ";
else
where += " and aa.date_create <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss') ";
// Tont: 14122010 them dk loai ra gd void
string loaivoid = " and not exists (select *" +
" from " + Businessbp.executedb.owner + "autho_activity nn" +
" where nn.message_type = '1200'" +
" and substr(nn.processing_code,1,2) = '02'" +
" and nn.function_code = '200'" +
" and nn.code_action = '000'" +
" and nn.card_number = aa.card_number" +
" and nn.transaction_amount=aa.transaction_amount"+
" and (nn.trace_audit_number = aa.trace_audit_number or nn.reference_number = aa.reference_number))";
// Tont: 14122010 them dk loai ra gd void
//sql = "select '' as acquirer_institution_code,'' as TENNGANHANG, aa.transaction_local_date as transaction_local_date, aa.card_number, aa.trace_audit_number, " +
// " aa.authorization_code as authorization_code, aa.private_data_c_40 as Loai_TB, decode(Substr(aa.processing_code,1,2),'30','GIAO DICH PHI TAI CHINH','35','GIAO DICH PHI TAI CHINH','GIAO DICH TAI CHINH') AS LOAI_gd, " +
// " decode(aa.message_type,'1420',-aa.billing_amount,aa.billing_amount) as transaction_amount, " +
// " aa.transaction_currency" +
string sql_select = "", sql_from = "", sql_where = "";
string title2 = "";
sql_select = "select '' as acquirer_institution_code,'' as TENNGANHANG, aa.transaction_local_date as transaction_local_date, aa.card_number, aa.trace_audit_number, aa.authorization_code as authorization_code, aa.private_data_c_40 as Loai_TB," +
" decode(substr(aa.processing_code,1,2),'17','Cash withdrawal','31','Balance Inquiry','91','Mini statement','45','Open fund transfer','01','Cash advance','00','Sale/purchase','20','Refund','02',decode(aa.function_code,'401','Adjustment','200','Void'),'Unknow') AS LOAI_gd," +
" aa.card_acc_name_address as tenmay," +
" decode(aa.message_type,'1420',-aa.billing_amount,aa.billing_amount) as transaction_amount, aa.transaction_currency";
sql_from= " from " + Businessbp.executedb.owner + "autho_activity aa ";
if (cbBanknet.Checked)
{
sql_select += " ,cc.description "; //2014-08-11 (1): (LUAN)
sql_from += " ," + Businessbp.executedb.owner + "card bb";
sql_from += " ," + Businessbp.executedb.owner + "nab_phanloaisp cc"; //2014-08-11 (1): (LUAN)
sql_where = " where aa.network_code = '22'" +
" and bb.product_code = cc.product_code " + //2014-08-11 (1): (LUAN)
" and aa.card_number=bb.card_number "+
" and (aa.network_data = '23' or rtrim(aa.private_data_c_100,chr(0)) = 'BNI')" +
" and aa.acquirer_institution_code <> '970428'" +
" and aa.issuing_bank = '970428'";
if (rbOnlyDebit.Checked)//Chi Debit
{
sql_where += " " +
//Dat them 20/06/2012: loai bo the Credit va Prepaid
" and trim(bb.product_code) in (select trim(cc.product_code) from " + Businessbp.executedb.owner + "nab_phanloaisp cc where cc.type='D' and cc.bank_code='970428')";
title2 = "THẺ DEBIT";
//End Dat them 20/06/2012: loai bo the Credit va Prepaid
}
else
{
if (rbPrepaidCredit.Checked)//Chỉ Prepaid va Credit
{
sql_where += " and trim(bb.product_code) in (select trim(cc.product_code) from " + Businessbp.executedb.owner + "nab_phanloaisp cc where cc.type in ('C','P') and cc.bank_code='970428')";
title2 = "THẺ PREPAID VÀ CREDIT";
}
else
{
title2 = "THẺ DEBIT, PREPAID VÀ CREDIT";
}
}
}
else
{
sql_where += " where aa.network_code = '23'" +
" and rtrim(aa.network_data,chr(0)) is NULL"+
" and aa.acquirer_institution_code = '970428'"+
" and aa.issuing_bank <> '970428'";
}
if (cbFundtranfer.Checked)//Loại gd chuyển khoản
{ sql_where += " and substr(aa.processing_code,1,2) not in ('45','60')"; }
else
{ }
sql_where += " and aa.code_action = '000'" +
" and " + Businessbp.executedb.owner + "reversalYN(aa.card_number,aa.reference_number,aa.trace_audit_number)='N'"+
" and aa.message_type <> '1420' "+
where +
loaivoid;
sql_where += " order by aa.transaction_local_date asc";
sql = sql_select + sql_from + sql_where;
// textBox1.Text = sql;
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
a.dt = dtt;
if (cbBanknet.Checked)
{
a.title = "BÁO CÁO CHI TIẾT GIAO DỊCH THẺ NAB SỬ DỤNG TẠI BANKNET";
a.title2 = title2;
a.tochuc = "Mã tổ chức phát hành:";
}
else
{
a.title = "BÁO CÁO CHI TIẾT GIAO DỊCH THẺ BANKNET SỬ DỤNG TẠI NAB";
a.title2 = " ";
a.tochuc = "Mã tổ chức thanh toán:";
}
a.ShowDialog();
}
#endregion R13
#region R14
else if (comboBox1.SelectedIndex + 1 == 14)
{
if (tbrefno.Text == "")
{
MessageBox.Show("Nhap so trn_ref_no");
tbrefno.Focus();
return;
}
//Bao cao 14
frmShowReport a = new frmShowReport();
a.type = "DC_14";
a.trnrefno = tbrefno.Text;
//a.thoiGian = "Từ " + dtFrom.Text + " " + tbFh.Text + ":" + tbFm.Text + ":" + tbFs.Text + " đến " + dtTo.Text + " " + tbTh.Text + ":" + tbTm.Text + ":" + tbTs.Text;
string sql = "", err = "";
sql = "select AC_BRANCH, AC_NO, AC_CCY, DECODE(DRCR_IND,'D','DEBIT','CREDIT') AS TYPE_RECORD," +
"LCY_AMOUNT, VALUE_DT, TRN_DT " +
"from acvw_all_ac_entries@FLEXCUBE " +
"where trn_ref_no = '" + tbrefno.Text + "'" +
" and ac_no not like '5191%'";
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
a.dt = dtt;
/*if (cbBanknet.Checked)
a.title = "BÁO CÁO GIAO DỊCH THẺ BANKNET/SMARTLINK SỬ DỤNG TẠI NAB";
else
a.title = "BÁO CÁO GIAO DỊCH THẺ BANKNET/SMARTLINK SỬ DỤNG TẠI NAB";*/
a.ShowDialog();
}
#endregion R14
#region R15
else if (comboBox1.SelectedIndex + 1 == 15)
{
//Bao cao 15
frmShowReport a = new frmShowReport();
a.type = "DC_15";
a.thoiGian = "Từ " + dtFrom.Text + " " + tbFh.Text + ":" + tbFm.Text + ":" + tbFs.Text + " đến " + dtTo.Text + " " + tbTh.Text + ":" + tbTm.Text + ":" + tbTs.Text;
string sql = "", err = "";
//sql = "select rownum as STT,decode(substr(aa.processing_code,1,2),'31','Balance Inquiry','91','Mini Statement') as loai_gd,"+
// " to_char(aa.transaction_local_date,'dd/mm/yyyy hh24:mi:ss') as transaction_local_date,"+
// " aa.card_number,aa.trace_audit_number as trace,aa.authorization_code as autho,aa.transaction_amount,"+
// " decode(substr(aa.processing_code,1,2),'31',1650,'91',1650,'0') as Phi,"+
// " aa.card_acc_name_address as ten_may,aa.transaction_currency as loai_tien"+
// " from " + Businessbp.executedb.owner + "autho_activity aa "+
// " where aa.network_code = '22' "+
// " and (aa.network_data = '23' or rtrim(aa.private_data_c_100,chr(0)) = 'BNI') "+
// " and aa.acquirer_institution_code <> '970428' "+
// " and aa.issuing_bank = '970428' "+
// " and substr(aa.processing_code,1,2) in ('31','91')"+
// " and " + Businessbp.executedb.owner + "reversalyn(aa.card_number,aa.reference_number,aa.trace_audit_number)='N' "+
// " and aa.message_type <> '1420' "+
// " and aa.code_action = '000' "+
// " and not exists "+ //Loai VOID
// " (select * from " + Businessbp.executedb.owner + "autho_activity nn "+
// " where nn.message_type = '1200' "+
// " and substr(nn.processing_code,1,2) = '02' "+
// " and nn.function_code = '200' "+
// " and nn.code_action = '000' "+
// " and nn.card_number = aa.card_number "+
// " and nn.transaction_amount=aa.transaction_amount "+
// " and (nn.trace_audit_number = aa.trace_audit_number or nn.reference_number = aa.reference_number)"+
// " ) "+
// " and aa.transaction_local_date >= to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss') " +
// " and aa.transaction_local_date <= to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss') " +
//" order by aa.transaction_local_date asc";
//textBox1.Text = sql;
sql = " select rownum as STT,decode(substr(a.proc_code,1,2),'31','Balance Inquiry','38','Mini Statement','17','Cash Withdraw','Others') as loai_gd";
sql += " ,to_char(b.TRN_DT,'dd/mm/yyyy') transaction_local_date,a.pan card_number";
sql += " ,a.stan trace,a.trn_ref_no autho,b.LCY_AMOUNT transaction_amount";
sql += " ,b.LCY_AMOUNT Phi,a.term_id ten_may,a.txn_ccy_code as loai_tien";
// sql += " , a.*,b.*";
sql += " from SWTB_TXN_LOG@FLEXCUBE a,acvw_all_ac_entries@FLEXCUBE b";
sql += " where a.TRN_REF_NO=b.TRN_REF_NO";
sql += " and b.AC_NO='000000000100118'";
sql += " and a.resp_code='00'";
sql += " and b.USER_ID='ATMUSER'";
sql += " and b.DRCR_IND='C'";
sql += " and (b.TRN_REF_NO ) not in ";
sql += " (";
sql += " select e.TRN_REF_NO";
sql += " from acvw_all_ac_entries@FLEXCUBE e";
sql += " where e.TRN_REF_NO=b.TRN_REF_NO";
sql += " and e.AC_NO='000000000100118'";
sql += " and e.DRCR_IND='C'";
sql += " and e.LCY_AMOUNT <0";
sql += " )";
// sql += " --and a.acq_ins_id<>'970428'";
sql += " and trunc(b.TRN_DT)>= trunc(to_date('" + dtFrom.Text.Trim() + "','dd/mm/yyyy'))";
sql += " and trunc(b.TRN_DT)<= trunc(to_date('" + dtTo.Text.Trim() + "','dd/mm/yyyy'))";
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
a.dt = dtt;
a.title = "BÁO CÁO CHI TIẾT THU PHÍ KHÁCH HÀNG GIAO DỊCH NGOÀI HỆ THỐNG";
a.ShowDialog();
}
#endregion R15
}
private void button6_Click(object sender, EventArgs e)
{
if (dataGridView2.DataSource != null)
{
DataTable dtt = (DataTable)dataGridView2.DataSource;
frmShowReport a = new frmShowReport();
a.dt = dtt;
a.type = "HoldOver30days";
a.thoiGian = "Từ "+dtFrom.Text+" đến "+dtTo.Text;
if (radioButton1.Checked)
a.title = "DANH SÁCH CHI TIẾT GIAO DỊCH ĐÃ HOLD";
else
a.title = "DANH SÁCH RELEASE GIAO DỊCH ĐÃ HOLD";
a.ShowDialog();
}
}
private void button7_Click(object sender, EventArgs e)
{
//Print
if (isNumber(tbSwimBatch.Text.Trim()))
{
string sql = "", err = "";
sql = "select aa.rtxntypcd, to_char(aa.acctnbr) as acctnbr, aa.amt, aa.txndesc," +
" decode(aa.rtxntypcd,'WTHD',aa.amt,0) as st_no," +
" decode(aa.rtxntypcd,'DEPD',aa.amt,0) as st_co," +
" decode(aa.rtxntypcd,'WTHD',1,0) as record_no," +
" decode(aa.rtxntypcd,'DEPD',1,0) as record_co" +
" from " + Businessbp.executedb.owner + "card_swimrepeat_bnk aa" +
" where aa.batch_id = " + tbSwimBatch.Text.Trim() +
" and aa.swim_file is not null" +
" and aa.amt > 0" +
" order by record_seq, decode(islg,'Y','1','2') desc";
DataTable dtt = Businessbp.executedb.getTableTCBS(sql, ref err);
if (err != "")
MessageBox.Show(err);
else
{
frmShowReport a = new frmShowReport();
a.dt = dtt;
a.type = "Swim";
a.title = "Swim number: " + tbSwimBatch.Text;
a.ShowDialog();
}
}
}
private void button6_Click(object sender, EventArgs e)
{
if (dataGridView2.DataSource != null)
{
DataTable dtt = (DataTable)dataGridView2.DataSource;
frmShowReport a = new frmShowReport();
a.dt = dtt;
a.thoiGian = "Từ " + dtFrom.Text + " " + tbFh.Text + ":" + tbFm.Text + ":" + tbFs.Text + " đến " + dtTo.Text + " " + tbTh.Text + ":" + tbTm.Text + ":" + tbTs.Text;
if (radioButton1.Checked)
a.title = "DANH SÁCH CHI TIẾT GIAO DỊCH ĐÃ HOLD NHƯNG CHƯA SETTLEMENT";
else if (radioButton2.Checked)
a.title = "DANH SÁCH RELEASE GIAO DỊCH ĐÃ HOLD NHƯNG CHƯA SETTLEMENT";
else
a.title = "DANH SÁCH RELEASE THẤT BẠI GIAO DỊCH ĐÃ HOLD NHƯNG CHƯA SETTLEMENT";
a.type = "NotSettlement";
a.ShowDialog();
}
}
private void button6_Click(object sender, EventArgs e)
{
frmShowReport a = new frmShowReport();
a.thoiGian = "Từ " + dtFrom.Text + " đến " + dtTo.Text;
a.type = "BC_TTDL";
string sql = "", err = "";
sql = "select c.merchant_number,d.company_name merchant_name,d.account1 account," +
"b.card_number,b.transaction_date,b.trace_audit_number trace_number,b.authorization_number," +
"decode(substr(lpad(b.processing_code,6,'0'),1,2),'00','Sale/Purchase','01','Cash Advance','02',decode(b.function_code,'401','Adjustment','200','Void'),'20','Refund','31','Balance Inquiry') txn_type," +
"trunc(b.settlement_date) settlement_date, to_number(to_char(a.transaction_amount)) as transaction_amount, a.transaction_currency,to_number(to_char(a.discount_amount)) as discount_amount,to_number(to_char(a.net_amount)) as net_amount, " +
"to_number('') as Discount_amount_correct, to_number('') as VAT_amount_correct " +
"from " + Businessbp.executedb.owner + "merchant_stmt_txn_details a, " + Businessbp.executedb.owner + "transaction_hist b, " + Businessbp.executedb.owner + "merchant_stmt_details c, " + Businessbp.executedb.owner + "merchant d " +
"where a.outlet_number = b.outlet_number " +
"and a.microfilm_ref_number = b.microfilm_ref_number " +
"and a.sequence_number = b.sequence_number " +
"and a.transaction_code = b.transaction_code " +
"and a.statement_no = c.statement_no " +
"and c.merchant_number = d.merchant_number " +
"and trunc(a.date_create) >= to_date('" + dtFrom.Text.Trim() + "','mm/dd/yyyy') " +
"and trunc(a.date_create) <= to_date('" + dtTo.Text.Trim() + "','mm/dd/yyyy')";
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
else
{
int txn_fee = 0;
int txn_fee1 = 0;
int vat = 0;
for (int i = 0; i < dtt.Rows.Count; i++)
{
string checkVAT = "";
txn_fee = Convert.ToInt32(dtt.Rows[i]["discount_amount"].ToString().Replace("-", ""));
txn_fee1 = (int)Math.Round((txn_fee / 1.1), 0);
checkVAT = txn_fee1.ToString();
if (checkVAT.Substring(checkVAT.Length - 1, 1) == "5")
{
vat = (int)(Math.Round((txn_fee1 * 0.1), 0)) + 1;
}
else
vat = (int)(Math.Round((txn_fee1 * 0.1), 0));
dtt.Rows[i]["Discount_amount_correct"] = txn_fee1;
dtt.Rows[i]["VAT_amount_correct"] = vat;
}
a.dt = dtt;
a.title = "BÁO CÁO TỔNG HỢP THANH TOÁN ĐẠI LÝ";
a.ShowDialog();
//dg1.DataSource = dtt;
}
}