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();
                            }
        }
예제 #2
0
 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();
             }
         }
     }
 }
예제 #3
0
 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();
     }
 }
예제 #4
0
 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();
         }
     }
 }
예제 #6
0
        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;
            }
        }