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 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')";
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, " +
"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(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)" +
" )) " +
" + 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 " + "banknet_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.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 '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 CASHCARD)' 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, " +
" 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(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)" +
" )) " +
" + 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 " + "banknet_issuer_transaction aa" +
" where 1=1" +
where +
" and substr(aa.card_number,1,8) = '97042809' " +
" 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, " +
" decode(substr(cc.name,1,3),'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," +
" COUNT(*) AS SL_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),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(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," +
" (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)" +
" )) " +
" +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 " + "banknet_acquirer_transaction aa, " +
" " + Businessbp.executedb.owner + "bin_table cc" +
" where 1=1" +
" and substr(aa.card_number,1,6)= substr(cc.start_bin,1,6)" +
" and cc.amnd_state = 'A'" +
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(cc.name,1,3),'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')" +
"))";
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Ẻ NAPAS";
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')";
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, ";
if (Frm_main.admin_flag)
{
sql += " 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.name TENNGANHANG, aa.card_number, aa.trace_audit_number, aa.authorization_number,";
}
else
{
sql += " 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.name TENNGANHANG, substr(aa.card_number,1,4)||'xxxxxxxx'||substr(aa.card_number,-4) card_number, aa.trace_audit_number, aa.authorization_number,";
}
sql += " 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," +
" 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 " + "banknet_issuer_transaction aa, " +
" " + Businessbp.executedb.owner + "bin_table bb" +
" where substr(aa.card_number,1,6) = substr(bb.start_bin,1,6)" +
" and bb.amnd_state = 'A'" +
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, ";
if (Frm_main.admin_flag)
{
sql += " 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.name TENNGANHANG, aa.card_number, aa.trace_audit_number, aa.authorization_number,";
}
else
{
sql += " 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.name TENNGANHANG, substr(aa.card_number,1,4)||'xxxxxxxx'||substr(aa.card_number,-4) card_number, aa.trace_audit_number, aa.authorization_number,";
}
sql += " 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," +
" 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, br.name as TEN_MAY" +
" from " + "banknet_acquirer_transaction aa, " +
" " + Businessbp.executedb.owner + "bin_table bb, " +
" " + Businessbp.executedb.owner + "acnt_contract cc, " +
" " + Businessbp.executedb.owner + "branch br " +
" where substr(aa.card_number,1,6) = substr(bb.start_bin,1,6)" +
" and aa.card_acceptor_term_id = cc.contract_number " +
" and cc.branch = br.code " +
" and cc.amnd_state = 'A' " +
" and br.amnd_state = 'A' " +
" and bb.amnd_state = 'A' " +
" and cc.con_cat = 'M' " +
//" and trim(aa.mcc) = '6011'" +
where +
" order by ISS_ACQ, TC_PTC, 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Ẻ NAPAS";
else
a.title = "BÁO CÁO BÙ TRỪ GIAO DỊCH THẺ SMARTLINK";
a.ShowDialog ( );
}
#endregion R2
#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 dc.trans_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 dc.trans_date <= to_date('" + dtTo.Text.Trim ( ) + " " + tbTh.Text.Trim ( ) + ":" + tbTm.Text.Trim ( ) + ":" + tbTs.Text.Trim ( ) + "','dd/mm/yyyy hh24:mi:ss') ";
else
where += " and dc.trans_date <= to_date('" + dtTo.Text.Trim ( ) + " " + tbTh.Text.Trim ( ) + ":" + tbTm.Text.Trim ( ) + ":" + tbTs.Text.Trim ( ) + "','dd/mm/yyyy hh24:mi:ss') ";
string sql_select = " select decode(dc.sic_code, '6011', 'ATM', 'POS') as Type," +
" decode(dc.trans_type," +
" '5'," +
" 'GIAO DICH TAI CHINH'," +
" '11'," +
" 'GIAO DICH TAI CHINH'," +
" '13'," +
" 'GIAO DICH TAI CHINH'," +
" 'GIAO DICH PHI TAI CHINH') AS LOAI_gd," +
" sum(dc.trans_amount) as SoTien," +
" count(*) as Record," +
" null as Phi";
string sql_from = " from " + Businessbp.executedb.owner + "doc dc";
if (cbBanknet.Checked)
{
//NAB dung tai BNK
sql_from += "," + Businessbp.executedb.owner + "acnt_contract acnt ";
sql = sql_select + sql_from;
sql += " where dc.target_number = acnt.contract_number" +
" and dc.amnd_state = 'A'" +
" and acnt.amnd_state = 'A'" +
" and acnt.con_cat = 'C'" +
" and base_relation is null" +
" and dc.source_channel = 'b' and dc.target_channel = 'O'" +
" and nab_pck_out.IS_REVERSAL(dc.id) = 'N'" +
" and dc.is_authorization = 'Y'" +
" and dc.request_category <> 'R'";
if (rbOnlyDebit.Checked)//Chi Debit
{
sql += " and acnt.product in ( select cc.product_code" +
" from nab_product cc" +
" where cc.type = 'D'" +
" and cc.bank_code = '970428')";
title2 = "THẺ DEBIT";
}
else
{
if (rbPrepaidCredit.Checked)//Chỉ Prepaid va Credit
{
sql += " and acnt.product in ( select cc.product_code" +
" from nab_product 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 dc.target_channel = 'b'"
+ " and nab_pck_out.IS_REVERSAL(dc.id) = 'N'"
+ " and dc.request_category <> 'R'"
+ " and dc.is_authorization = 'N'"
+ " and dc.target_channel = 'b'"
+ " and dc.source_channel in ('A','P')";
title2 = " ";
}
//================================================
sql += " and dc.posting_status in ('P','W')" + //" and dc.return_code = 0" +
where +
" group by decode(dc.sic_code, '6011', 'ATM', 'POS')," +
" decode(dc.trans_type," +
" '5'," +
" 'GIAO DICH TAI CHINH'," +
" '11'," +
" 'GIAO DICH TAI CHINH'," +
" '13'," +
" 'GIAO DICH TAI CHINH'," +
" 'GIAO DICH PHI 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 NAPAS";
//a.title2 = title2;
}
else
a.title = "BÁO CÁO TỔNG KẾT GIAO DỊCH THẺ NAPAS 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 " +
" 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," +
" 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 banknet_issuer_transaction aa" +
" where 1=1" +
where +
" group by" +
" 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))))" +
" union all" +
" select " +
" 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," +
" 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," +
" 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 banknet_acquirer_transaction aa" +
" where 1=1" +
where +
" group by " +
" 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))))" +
" ) 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 " +
" 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," +
" 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" +
" 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))))" +
" union all" +
" select " +
" 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," +
" 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 " +
" 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))))" +
" ) 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 NAPAS";
else
a.title = "BÁO CÁO TỔNG HỢP PHÍ GIAO DỊCH SMARTLINK";
a.ShowDialog ( );
}
#endregion R7
#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.source_member_id as acquirer_institution_code," +
" ff.name tennganhang," +
" aa.trans_date transaction_local_date,";
if (Frm_main.admin_flag)
{
sql += " aa.target_number card_number,";
}
else
{
sql += " substr(aa.target_number,1,4)||'xxxxxxxx'||substr(aa.target_number,-4) card_number,";
}
sql += " nab_pck_out.GET_TRACE_NUMBER(aa.id) as trace_audit_number,"
+ " aa.auth_code authorization_code,"
+ " decode(aa.sic_code, '6011', 'ATM', 'POS') as Loai_TB,"
+ " bb.name as Loai_GD,"
+ " aa.TRANS_AMOUNT as transaction_amount,"
+ " aa.trans_curr transaction_currency,"
+ " null as iss_fee,"
+ " null as banknetvn_fee,"
+ " null as acq_fee"
+ " from " + Businessbp.executedb.owner + "doc aa,"
+ Businessbp.executedb.owner + "trans_type bb,"
+ Businessbp.executedb.owner + "BIN_TABLE ff"
+ " where aa.trans_type = bb.id"
+ " and aa.amnd_state = 'A'"
+ " and bb.amnd_state = 'A'"
+ " and ff.amnd_state = 'A'"
+ " and aa.posting_status in ('P','W') "
+ " and SUBSTR(aa.target_number, 1, 6) = substr(ff.start_bin,1,6)"
+ " and aa.target_channel = 'O' and aa.source_channel = 'b'"
+ " and not exists ( "
+ " select 1 "
+ " from " + Businessbp.executedb.owner + "doc dc1 "
+ " where dc1.return_code = 0 "
+ " and dc1.request_category = 'R' "
+ " and dc1.source_reg_num = aa.source_reg_num "
+ " and dc1.target_number = aa.target_number "
+ " ) "
+ " and decode(substr(aa.target_number,1,8),'97042808','1','97042809','1', ( "
+ " select nvl(count(*),0) "
+ " from " + Businessbp.executedb.owner + "doc dc1 "
+ " where dc1.source_reg_num = aa.source_reg_num "
+ " and dc1.target_number = aa.target_number "
+ " and dc1.return_code = 0 "
+ " and dc1.is_authorization = 'y' "
+ " ) "
+ " ) = 1 "
+ " and aa.request_category <> 'R'"
+ " and aa.is_authorization = 'Y'"
+ " and aa.trans_date >= to_date('" + dtFrom.Text.Trim ( ) + " " + tbFh.Text.Trim ( ) + ":" + tbFm.Text.Trim ( ) + ":" + tbFs.Text.Trim ( ) + "','dd/mm/yyyy hh24:mi:ss')"
+ " and aa.trans_date <= to_date('" + dtTo.Text.Trim ( ) + " " + tbTh.Text.Trim ( ) + ":" + tbTm.Text.Trim ( ) + ":" + tbTs.Text.Trim ( ) + "','dd/mm/yyyy hh24:mi:ss')"
+ " and not exists( select 1"
+ " from banknet_issuer_transaction bi"
+ " where bi.card_number = aa.target_number"
+ " and ( bi.reference_number = aa.ret_ref_number"
+ " or ( bi.authorization_number = aa.auth_code"
+ " and trunc(bi.transaction_local_date) = trunc(aa.trans_date)"
+ " )"
+ " )"
+ " )"
+ " order by trans_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Ó WAY4 KHÔNG CÓ NAPAS";
else
a.title = "BÁO CÁO CHI TIẾT GIAO DỊCH SAI LỆCH - CÓ WAY4 KHÔNG CÓ SMARTLINK";
a.ShowDialog ( );
}
#endregion R10
#region R11
else if (comboBox1.SelectedIndex + 1 == 11)
{
//Báo cáo chi tiết giao dịch sai lệch (Không có Elctra - Có Napas)
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)
{
if (Frm_main.admin_flag)
{
sql += " select distinct cc.acquirer_institution_code, " +
" ff.name 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,";
}
else
{
sql += " select distinct cc.acquirer_institution_code, " +
" ff.name tennganhang, " +
" cc.transaction_local_date, " +
" substr(cc.card_number,1,4)||'xxxxxxxx'||substr(cc.card_number,-4) 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,";
}
sql += " 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 " + "banknet_issuer_transaction cc, " + Businessbp.executedb.owner + "bin_table ff" +
" where SUBSTR(cc.card_number,1,6) = substr(ff.start_bin,1,6)" +
//" and cc.acquirer_institution_code = ff.member_id"+
" and ff.amnd_state = 'A'" +
" and cc.transaction_status = 0" +
" and not exists( select 1" +
" from " + Businessbp.executedb.owner + "doc dd " +
" where dd.return_code = 0 " +
" and dd.AMND_STATE = 'A'" +
" AND dd.IS_AUTHORIZATION = 'Y'" +
" and dd.target_channel = 'O' and dd.source_channel = 'b'" +
" and nab_pck_out.IS_REVERSAL(dd.id) = 'N'" +
" and dd.request_category <> 'R'" +
" and dd.target_number = cc.card_number " +
" and dd.Trans_Amount = cc.transaction_amount " +
" and ( trim(dd.ret_ref_number) = trim(cc.reference_number)" +
" or" +
" (" +
" dd.auth_code = cc.authorization_number" +
" and trunc(dd.trans_date) = trunc(cc.transaction_local_date)" +
" )" +
" )" +
" )" +
" 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Ó NAPAS";
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 electra, ko co banknet
" select distinct aa.source_member_id as acquirer_institution_code," +
" ff.name tennganhang," +
" aa.trans_date transaction_local_date,";
if (Frm_main.admin_flag)
{
sql += " aa.target_number card_number,";
}
else
{
sql += " substr(aa.target_number,1,4)||'xxxxxxxx'||substr(aa.target_number,-4) card_number,";
}
sql += " nab_pck_out.GET_TRACE_NUMBER(aa.id) as trace_audit_number,"
+ " aa.auth_code authorization_code,"
+ " decode(aa.sic_code, '6011', 'ATM', 'POS') as Loai_TB,"
+ " bb.name as Loai_GD,"
+ " aa.TRANS_AMOUNT as transaction_amount,"
+ " aa.trans_curr transaction_currency,"
+ " null as iss_fee,"
+ " null as banknetvn_fee,"
+ " null as acq_fee"
+ " from " + Businessbp.executedb.owner + "doc aa,"
+ Businessbp.executedb.owner + "trans_type bb,"
+ Businessbp.executedb.owner + "BIN_TABLE ff"
+ " where aa.trans_type = bb.id"
+ " and aa.amnd_state = 'A'"
+ " and bb.amnd_state = 'A'"
+ " and ff.amnd_state = 'A'"
+ " and SUBSTR(aa.target_number, 1, 6) = substr(ff.start_bin,1,6)"
//+ " and aa.source_member_id = ff.member_id"
+ " and aa.target_channel = 'O' and aa.source_channel = 'b'"
+ " and nab_pck_out.IS_REVERSAL(aa.id) = 'N'"
+ " and aa.request_category <> 'R'"
+ " and aa.return_code = 0"
+ " and aa.is_authorization = 'Y'"
+ " and aa.trans_date >= to_date('" + dtFrom.Text.Trim ( ) + " " + tbFh.Text.Trim ( ) + ":" + tbFm.Text.Trim ( ) + ":" + tbFs.Text.Trim ( ) + "','dd/mm/yyyy hh24:mi:ss')"
+ " and aa.trans_date <= to_date('" + dtTo.Text.Trim ( ) + " " + tbTh.Text.Trim ( ) + ":" + tbTm.Text.Trim ( ) + ":" + tbTs.Text.Trim ( ) + "','dd/mm/yyyy hh24:mi:ss')"
+ " and exists( select 1"
+ " from banknet_issuer_transaction bi"
+ " where bi.card_number = aa.target_number"
+ " and ( bi.reference_number = aa.ret_ref_number"
+ " or ( bi.authorization_number = aa.auth_code"
+ " and trunc(bi.transaction_local_date) = trunc(aa.trans_date)"
+ " )"
+ " )"
+ " )"
+ " order by trans_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.trans_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.trans_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.trans_date <= to_date('" + dtTo.Text.Trim ( ) + " " + tbTh.Text.Trim ( ) + ":" + tbTm.Text.Trim ( ) + ":" + tbTs.Text.Trim ( ) + "','dd/mm/yyyy hh24:mi:ss') ";
string sql_select = "", sql_from = "", sql_where = "";
string title2 = "";
sql_select = " select '' as acquirer_institution_code," +
" '' as TENNGANHANG," +
" aa.trans_date as transaction_local_date,";
if (Frm_main.admin_flag)
{
sql_select += " aa.target_number,";
}
else
{
sql_select += " substr(aa.target_number,1,4)||'xxxxxxxx'||substr(aa.target_number,-4) target_number, ";
}
sql_select += " nab_pck_out.GET_TRACE_NUMBER(aa.id)," +
" aa.auth_code as authorization_code," +
" decode(aa.sic_code, '6011', 'ATM', 'POS') as Loai_TB," +
" to_char(aa.trans_type) AS LOAI_gd," +
" aa.trans_details as tenmay," +
" aa.settl_amount as transaction_amount," +
" aa.trans_curr," +
" pr.description";
sql_from = " from " + Businessbp.executedb.owner + "doc aa ";
if (cbBanknet.Checked)
{
sql_from += " , " + Businessbp.executedb.owner + "acnt_contract ca ,nab_product pr";
sql_where = " where aa.target_number = ca.contract_number" +
" and pr.product_code = ca.product" +
" and aa.amnd_state = 'A'" +
" and ca.amnd_state = 'A'" +
" and aa.target_channel = 'O' and aa.source_channel = 'b'";
if (rbOnlyDebit.Checked)//Chi Debit
{
sql_where += " and pr.type = 'D' and pr.bank_code = '970428'";
title2 = "THẺ DEBIT";
}
else
{
if (rbPrepaidCredit.Checked)//Chỉ Prepaid va Credit
{
sql_where += " and pr.type in ('C','P') and pr.bank_code = '970428'";
title2 = "THẺ PREPAID VÀ CREDIT";
}
else
{
title2 = "THẺ DEBIT, PREPAID VÀ CREDIT";
}
}
}
else
{
sql_where = " where aa.target_channel = 'b' " +
" and aa.amnd_state = 'A'" +
" and aa.source_channel in ('A','P')";
}
if (cbFundtranfer.Checked)//Loại gd chuyển khoản
{
sql_where += " and aa.source_code <> 'FTC' and aa.trans_code <> 'KS'";
}
sql_where += " and aa.return_code = '0'" +
" and nab_pck_out.IS_REVERSAL(aa.id) = 'N'" +
" and decode(aa.source_channel,'b','Y','N') = aa.IS_AUTHORIZATION" +
" and aa.request_category <> 'R'" +
where;
sql_where += " order by aa.trans_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 NAPAS";
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Ẻ NAPAS 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Ẻ NAPAS/SMARTLINK SỬ DỤNG TẠI NAB";
else
a.title = "BÁO CÁO GIAO DỊCH THẺ NAPAS/SMARTLINK SỬ DỤNG TẠI NAB";
a.ShowDialog ( );
}
#endregion R14
}
