private void btn_report_Click(object sender, EventArgs e)
 {
     Frm_Report a = new Frm_Report();
     a.type = "PHT_THUPHI";
     string sql = "", err = "";
     #region 2014-06-09 (1)            
     sql += " select ci.card_name embosing_name, ";
     if (Frm_main.admin_flag)
     {
         sql += "        ft.CARD_NUMBER, ";
         sql += "        ft.ACCOUNT_NUMBER, ";
     }
     else
     {
         sql += "        substr(ft.CARD_NUMBER,1,4)||'xxxxxxxx'||substr(ft.CARD_NUMBER,-4) CARD_NUMBER, ";
         sql += "        substr(ft.ACCOUNT_NUMBER,1,4)||'xxxxxxxx'||substr(ft.ACCOUNT_NUMBER,-4) ACCOUNT_NUMBER, ";
     }
     
     sql += "        ft.LEGAL_ID, ";
     sql += "        cl.address_line_1|| ' ' || cl.address_line_2 || cl.address_line_3 || cl.address_line_4 as ADDRESS, ";
     sql += "        ft.PHI_AMOUNT, ";
     sql += "        ft.NOI_DUNG, ";
     sql += "        '' GHI_CHU ";
     sql += "   from NAB_card_fee_txn ft,  ";
     sql += "        " + Businessbp.executedb.owner +"acnt_contract ctr,  ";
     sql += "        " + Businessbp.executedb.owner + "card_info ci, ";
     sql += "        " + Businessbp.executedb.owner + "client cl, ";
     sql += "        nab_product prd ";
     sql += "  where 1=1 ";
     sql += "    and ft.card_number = ctr.contract_number ";
     sql += "    and ctr.id = ci.acnt_contract__oid ";
     sql += "    and ctr.client__id = cl.id ";
     sql += "    and ctr.product = prd.product_code ";
     sql += "    and ctr.amnd_state = 'A' ";
     sql += "    and ci.status = 'A' ";
     sql += "    and cl.amnd_state = 'A' ";
     #endregion 2014-06-09 (1)
     if (rbChuaThuPhi.Checked)
     {
         //  sql += "         and nvl(a.authorize_flag,'N')='N'";
         sql += "         and nvl(ft.accounting_flag,'N')='N'";
         sql += "         and trunc(ft.date_create)>=trunc(to_date('" + dtFrom.Text + "','dd/mm/yyyy'))";
         sql += "         and trunc(ft.date_create)<=trunc(to_date('" + dtTo.Text + "','dd/mm/yyyy'))";
     }
     else if (rbDaThuPhi.Checked)
     {
         sql += "         and nvl(ft.authorize_flag,'N')='Y'";
         sql += "         and nvl(ft.accounting_flag,'N')='Y'";
         sql += "         and trunc(ft.date_authorize)>=trunc(to_date('" + dtFrom.Text + "','dd/mm/yyyy'))";
         sql += "         and trunc(ft.date_authorize)<=trunc(to_date('" + dtTo.Text + "','dd/mm/yyyy'))";
     }
     else
     {
         sql += "         and trunc(ft.date_create)>=trunc(to_date('" + dtFrom.Text + "','dd/mm/yyyy'))";
         sql += "         and trunc(ft.date_create)<=trunc(to_date('" + dtTo.Text + "','dd/mm/yyyy'))";
     }
     DataTable dt = Businessbp.executedb.getTable(sql, ref err);
     if (err != "")
     {
         MessageBox.Show(err);
         return;
     }
     else
     {
         string title = "";
         if (rbChuaThuPhi.Checked)
             title = "DANH SÁCH CÁC THẺ CHƯA THU PHÍ";
         else if (rbDaThuPhi.Checked)
             title = "DANH SÁCH CÁC THẺ ĐÃ THU PHÍ";
         else
             title = "DANH SÁCH KHÁCH HÀNG THU PHÍ";
         a.title = title;
         a.title2 = "Từ ngày " + dtFrom.Text + " đến ngày " + dtTo.Text;
         a.dt = dt;
         a.ShowDialog();
     }
 }
        private void btPrint_Click ( object sender, EventArgs e )
        {
            Frm_Report a = new Frm_Report ( );
            string sql = "", err = "";
            string date = tbStartDate.Text;

            if (DateTime.ParseExact ( tbStartDate.Text.Trim ( ), "dd/MM/yyyy", CultureInfo.InvariantCulture ) < DateTime.ParseExact ( "24/09/2015", "dd/MM/yyyy", CultureInfo.InvariantCulture )) //2015-08-19 (8)
            {


                #region 19-08-2016
                //sql += "select distinct A.*, ";
                //sql += "                case ";
                //sql += "                  when (A.date_create 90 < add_months(to_date(A.processing_date, ";
                //sql += "                                                              'dd/mm/yyyy'), ";
                //sql += "                                                      -1)) then ";
                //sql += "                   '0' ";
                //sql += "                  else ";
                //sql += "                   to_char(decode(decode(A.machinhsachtd, ";
                //sql += "                                         'B01 - STK - UU DAI', ";
                //sql += "                                         '1', ";
                //sql += "                                         '0'), ";
                //sql += "                                  '1', ";
                //sql += "                                  " + Businessbp.executedb.owner + "nab_pck_out.nab_func_getcashback_amt(A.cr_account_nbr, ";
                //sql += "                                                         to_char(add_months(to_date(A.processing_date, ";
                //sql += "                                                                                    'dd/mm/yyyy'), ";
                //sql += "                                                                            -1) 1, ";
                //sql += "                                                                 'dd/mm/yyyy'), ";
                //sql += "                                                         to_char(least(A.date_create 90, ";
                //sql += "                                                                       to_date(A.processing_date, ";
                //sql += "                                                                               'dd/mm/yyyy')), ";
                //sql += "                                                                 'dd/mm/yyyy')), ";
                //sql += "                                  0), ";
                //sql += "                           '999,999,999,999') ";
                //sql += "                end cashback ";
                //sql += "  from (select substr(r.card_number, 1, 4) || 'xxxxxxxx' || ";
                //sql += "               substr(r.card_number, 13, 4) card_number, ";
                //sql += "               r.embossed_name, ";
                //sql += "               r.credit_limit, ";
                //sql += "               r.processing_date, ";
                //sql += "               r.payment_date, ";
                //sql += "               r.opening_bal, ";
                //sql += "               r.total_debit, ";
                //sql += "               r.bal_payment, ";
                //sql += "               r.closing_bal, ";
                //sql += "               r.min_due, ";
                //sql += "               r.transaction_date, ";
                //sql += "               r.value_date, ";
                //sql += "               r.arn, ";
                //sql += "               r.details, ";
                //sql += "               r.billing_amount_du_co, ";
                //sql += "               r.billing_amount_du_no, ";
                //sql += "               r.address, ";
                //sql += "               r.PHONE_HOME, ";
                //sql += "               r.PHONE_MOBILE, ";
                //sql += "               r.cr_account_nbr  cr_account_nbr, ";
                //sql += "               r.transaction_amount, ";
                //sql += "               r.transaction_currency, ";
                //sql += "               r.billing_currency, ";
                //sql += "               r.machinhsachtd, ";
                //sql += "               (select date_open ";
                //sql += "                  from " + Businessbp.executedb.owner + "acnt_contract ";
                //sql += "                 where contract_number = r.cr_account_nbr and amnd_state = 'A') date_create ";
                //sql += "          from (select substr(a.card_number, 1, 4) || 'xxxxxxxx' || ";
                //sql += "                       substr(a.card_number, 13, 4) card_number, ";
                //sql += "                       a.embossed_name, ";
                //sql += "                       a.credit_limit, ";
                //sql += "                       to_char(a.stat_date, 'dd/mm/yyyy') as processing_date, ";
                //sql += "                       to_char(a.payment_date, 'dd/mm/yyyy') as payment_date, ";
                //sql += "                       a.opening_bal, ";
                //sql += "                       a.total_debit, ";
                //sql += "                       a.bal_payment, ";
                //sql += "                       a.closing_bal, ";
                //sql += "                       a.min_due, ";
                //sql += "                       to_char(a.transaction_date, 'dd/mm/yyyy') as transaction_date, ";
                //sql += "                       to_char(a.value_date, 'dd/mm/yyyy') as value_date, ";
                //sql += "                       a.arn, ";
                //sql += "                       a.details, ";
                //sql += "                       decode(a.transaction_sign, ";
                //sql += "                              '0', ";
                //sql += "                              to_number(''), ";
                //sql += "                              '1', ";
                //sql += "                              a.billing_amount, ";
                //sql += "                              a.billing_amount) billing_amount_du_co, ";
                //sql += "                       decode(a.transaction_sign, ";
                //sql += "                              '0', ";
                //sql += "                              a.billing_amount, ";
                //sql += "                              '1', ";
                //sql += "                              to_number(''), ";
                //sql += "                              a.billing_amount) billing_amount_du_no, ";
                //sql += "                       (a.MAILING_ADDRESS1 || ' ' || a.MAILING_ADDRESS2 || ' ' || ";
                //sql += "                       a.MAILING_ADDRESS3 || ' ' || a.MAILING_ADDRESS4) address, ";
                //sql += "                       a.PHONE_HOME, ";
                //sql += "                       a.PHONE_MOBILE, ";
                //sql += "                       substr(a.cr_account_nbr, 1, 4) || 'xxxxxxxx' || ";
                //sql += "                       substr(a.cr_account_nbr, 13, 4) cr_account_nbr, ";
                //sql += "                       a.transaction_amount, ";
                //sql += "                       (select b.name ";
                //sql += "                          from " + Businessbp.executedb.owner + "currency b ";
                //sql += "                         where trim(b.code) = trim(a.transaction_currency) and b.amnd_state = 'A') transaction_currency, ";
                //sql += "                       (select b.name ";
                //sql += "                          from " + Businessbp.executedb.owner + "currency b ";
                //sql += "                         where trim(b.code) = trim(a.billing_currency) and b.amnd_state = 'A') billing_currency, ";
                //sql += "                       c.machinhsachtd ";
                //sql += "                  from nab_billing_credit_log a, ";
                //sql += "                       " + Businessbp.executedb.owner + "acnt_contract      crd, ";
                //sql += "                       nab_cp_card            c ";
                //sql += "                 where trunc(a.stat_date) = ";
                //sql += "                       trunc(to_date('" + tbStartDate.Text.Trim ( ) + "', ";
                //sql += "                                     'dd/mm/yyyy')) ";
                //sql += "                   and a.card_number = crd.contract_number ";
                //sql += "                   and crd.add_info_01 = c.source_application_no                  ";
                //sql += "                   and crd.amnd_state = 'A' ";
                //sql += "                   and crd.con_cat = 'C' ";
                //if (tbAccountNumber.Text.Trim ( ) != "")
                //    sql += "                   and trim(a.cr_account_nbr) ='" + tbAccountNumber.Text.Trim ( ) + "' ";
                //sql += "                UNION ALL ";
                //sql += "                select substr(a.card_number, 1, 4) || 'xxxxxxxx' || ";
                //sql += "                       substr(a.card_number, 13, 4) card_number, ";
                //sql += "                       a.embossed_name, ";
                //sql += "                       a.credit_limit, ";
                //sql += "                       to_char(a.stat_date, 'dd/mm/yyyy') as processing_date, ";
                //sql += "                       to_char(a.payment_date, 'dd/mm/yyyy') as payment_date, ";
                //sql += "                       a.opening_bal, ";
                //sql += "                       a.total_debit, ";
                //sql += "                       a.bal_payment, ";
                //sql += "                       a.closing_bal, ";
                //sql += "                       a.min_due, ";
                //sql += "                       to_char(a.transaction_date, 'dd/mm/yyyy') as transaction_date, ";
                //sql += "                       to_char(a.value_date, 'dd/mm/yyyy') as value_date, ";
                //sql += "                       a.arn, ";
                //sql += "                       a.details, ";
                //sql += "                       a.billing_amount billing_amount_du_co, ";
                //sql += "                       a.billing_amount billing_amount_du_no, ";
                //sql += "                       (a.MAILING_ADDRESS1 || ' ' || a.MAILING_ADDRESS2 || ' ' || ";
                //sql += "                       a.MAILING_ADDRESS3 || ' ' || a.MAILING_ADDRESS4) address, ";
                //sql += "                       a.PHONE_HOME, ";
                //sql += "                       a.PHONE_MOBILE, ";
                //sql += "                       a.cr_account_nbr cr_account_nbr, ";
                //sql += "                       a.transaction_amount transaction_amount, ";
                //sql += "                       (select b.name ";
                //sql += "                          from " + Businessbp.executedb.owner + "currency b ";
                //sql += "                         where trim(b.code) = ";
                //sql += "                               trim(a.transaction_currency) and b.amnd_state = 'A') transaction_currency, ";
                //sql += "                       (select b.name ";
                //sql += "                          from " + Businessbp.executedb.owner + "currency b ";
                //sql += "                         where trim(b.code) = ";
                //sql += "                               trim(a.billing_currency) and b.amnd_state = 'A') billing_currency, ";
                //sql += "                       c.machinhsachtd ";
                //sql += "                  from nab_billing_credit_log a, ";
                //sql += "                       nab_cp_card                c, ";
                //sql += "                       " + Businessbp.executedb.owner + "acnt_contract crd ";
                //sql += "                 where trunc(a.stat_date) = ";
                //sql += "                       trunc(to_date('" + tbStartDate.Text.Trim ( ) + "', ";
                //sql += "                                     'dd/mm/yyyy')) ";
                //sql += "                   and a.card_number = crd.contract_number ";
                //sql += "                   and crd.amnd_state = 'A'";
                //sql += "                   and crd.add_info_01 = c.source_application_no ";
                //sql += "                   and trim(a.transaction_code) is null ";
                //if (tbAccountNumber.Text.Trim ( ) != "")
                //    sql += "                   and trim(a.cr_account_nbr) = '" + tbAccountNumber.Text.Trim ( ) + "' ";

                //sql += "         order by r.card_number asc, ";
                //sql += "                  to_date(r.transaction_date, 'dd/mm/yyyy') desc) A ";
                //sql += " order by A.card_number asc, ";
                //sql += "          to_date(A.transaction_date, 'dd/mm/yyyy') desc ";
                #endregion 03-04-2016


            }

            else
            {

                IFormatProvider culture = new System.Globalization.CultureInfo ( "fr-FR", true );
                DateTime dt2 = DateTime.Parse ( date, culture, System.Globalization.DateTimeStyles.AssumeLocal );
                string  sql_installment = "";
                sql += "select (case when l.card_number = l.cr_account_nbr then l.card_number else (substr(l.card_number, 1, 4) || ' ' || substr(l.card_number, 5, 2) || ";
                sql += "       'xx xxxx ' || substr(l.card_number, 13, 4)) end) card_number, ";
                sql += "       l.cr_account_nbr cr_account_nbr, ";
                sql += "       l.embossed_name, ";
                sql += "       l.phone_mobile, ";
                sql += "       nvl(l.credit_limit, 0) credit_limit_acc, ";
                sql += "       nvl(l.credit_limit_client, 0) credit_limit_client, ";
                sql += "       nvl(l.avail_amount_acc, 0) avail_amount_acc, ";
                sql += "       nvl(l.avail_amount_client, 0) avail_amount_client, ";
                sql += "       to_char(l.stat_date, 'dd/mm/yyyy') as stat_date, ";
                sql += "       to_char(l.payment_date, 'dd/mm/yyyy') as payment_date, ";
                sql += "       nvl(l.opening_bal, 0) opening_bal, ";
                sql += "       nvl(l.total_debit, 0) total_debit, ";
                sql += "       (nvl(l.fee, 0) + nvl(l.interest, 0)) fee_and_interest, ";
                sql += "       nvl(l.bal_payment, 0) bal_payment, ";
                sql += "       nvl(l.closing_bal, 0) closing_bal, ";
                sql += "       (trim(l.MAILING_ADDRESS1) || ' ' || trim(l.MAILING_ADDRESS2) || ' ' || ";
                sql += "       trim(l.MAILING_ADDRESS3) || ' ' || trim(l.MAILING_ADDRESS4)) address, ";
                sql += "       to_char(l.transaction_date, 'dd/mm/yyyy') as transaction_date, ";
                sql += "       to_char(l.value_date, 'dd/mm/yyyy') as value_date, ";
                sql += "       l.arn, ";
                sql += "       nvl(l.transaction_amount, 0) transaction_amount, ";
                sql += "       (select '(' || ccy.name || ')' ";
                sql += "          from " + Businessbp.executedb.owner + "currency ccy ";
                sql += "         where trim(ccy.code) = trim(l.transaction_currency) and ccy.amnd_state = 'A') transaction_currency, ";
                sql += "       nvl(l.billing_amount, 0) billing_amount, ";
                sql += "       decode(l.transaction_sign, ";
                sql += "              '-1', ";
                sql += "              '(DR)', ";
                sql += "              '1', ";
                sql += "              '(CR)', ";
                sql += "              '') transaction_sign, ";
                sql += "       l.details, ";
                sql += "       nvl(l.min_due, 0) min_due, ";
                sql += "       nvl(l.min_due_install, 0) min_due_install, ";
                sql += "       nvl(l.min_due_crterm, 0) min_due_crterm, ";
                sql += "       nvl(l.cash_back_amount, 0) cash_back_amount, ";
                sql += "       nvl(l.diamondbay_amount, 0) diamondbay_amount, ";
                sql += "       nvl(l.diamondbay_rank, 0) diamondbay_rank, ";
                sql += "       nvl(l.loyalty_score_earned, 0) loyalty_score_earned, ";
                sql += "       nvl(l.loyalty_score_spent, 0) loyalty_score_spent, ";
                sql += "       nvl(l.loyalty_cash_exchanged, 0) loyalty_cash_exchanged, ";
                sql += "       nvl(l.loyalty_score_avail, 0) loyalty_score_avail, ";
                sql += "       nvl(l.transaction_fee,0) transaction_fee,";
                sql += "       nvl(l.ovd_flag,'N') ovd_flag";
                sql += "  from nab_billing_credit_log l, ";
                sql += "        (select l1.cr_account_nbr,count(*) ";
                sql += "        from nab_billing_credit_log l1 ";
                sql += "        where trunc(l1.stat_date) = trunc(to_date('" + tbStartDate.Text.Trim ( ) + "', 'dd/mm/yyyy')) ";
                sql += "          and l1.arn is not null ";
                sql += "         group by l1.cr_account_nbr ";
                sql += "         having count(*)>0 ";
                sql += "        ) tmp1 ";
                sql += " where 1=1 ";
                sql += "   and trunc(l.stat_date) =trunc(to_date('" + tbStartDate.Text.Trim ( ) + "', 'dd/mm/yyyy')) ";
                sql += "   and l.cr_account_nbr = tmp1.cr_account_nbr ";
                sql += "   and l.closing_bal <> 0 "; //2015-10-21 (9)
                if (tbAccountNumber.Text.Trim ( ) != "")
                    sql += "   and trim(l.cr_account_nbr)='" + tbAccountNumber.Text.Trim ( ) + "' ";
                sql += " order by l.card_number asc, to_date(l.transaction_date, 'dd/mm/yyyy') desc,to_date(l.value_date, 'dd/mm/yyyy') desc    ";

                DataTable dtt = Businessbp.executedb.getTable ( sql, ref err );
                if (err != "")
                {
                    MessageBox.Show ( err );
                    return;
                }

                #region get installment

                sql_installment = " select *";
                sql_installment += " from           ";
                sql_installment += " (";
                sql_installment += " select ipd.TRANSACTION_DETAILS, ";
                sql_installment += " nvl(ipd.TOTAL_AMOUNT,0) TOTAL_AMOUNT,  ";
                sql_installment += " nvl(sum(decode(pd.invoice_status,'WAITING',1,0)),0) PENDING_PERIOD,";
                sql_installment += " nvl(sum(decode(pd.invoice_status,'WAITING',0,1)),0) EXTRACTED_PERIOD,";
                sql_installment += " nvl(sum(decode(pd.invoice_status,'WAITING',pd.portion_amount,0)),0) pending_amount,";
                sql_installment += " nvl(sum(decode(pd.invoice_status,'WAITING',0,pd.portion_amount)),0) extracted_amount";
                sql_installment += " from " + Businessbp.executedb.owner + "INST_PLAN_DATA ipd left join ows.INST_PORTION_DATA pd";
                sql_installment += " on ipd.id = pd.instalment_plan";
                if (tbAccountNumber.Text.Trim ( ) != "")
                {
                    sql_installment += " where ipd.CONTRACT_ID = (select id ";
                    sql_installment += "                          from " + Businessbp.executedb.owner + "acnt_contract ";
                    sql_installment += "                          where amnd_state = 'A' ";
                    sql_installment += "                          and con_cat = 'C' ";
                    sql_installment += "                          and contract_number = '" + tbAccountNumber.Text.Trim ( ) + "')";
                }
                sql_installment += " group by ipd.TRANSACTION_DETAILS, ";
                sql_installment += " ipd.TOTAL_AMOUNT";
                sql_installment += " )";
                sql_installment += " where pending_period > 0";
                DataTable dtt_ins = Businessbp.executedb.getTable ( sql, ref err );
                if (err != "")
                {
                    MessageBox.Show ( err );
                    return;
                }
                #endregion

                a.dt = dtt;
                a.dt_ins = dtt_ins;
                a.type = "BILILING_CREDIT_3";
                a.title = "BẢNG THÔNG BÁO GIAO DỊCH";
                a.title2 = "(Từ ngày " + tbStartDate.Text.Split ( '/' )[0] + '/' + ( Int32.Parse ( tbStartDate.Text.Split ( '/' )[1] ) - 1 ).ToString ( ) + '/' + tbStartDate.Text.Split ( '/' )[2] + " đến ngày " + tbStartDate.Text.Trim ( ) + " )";
                a.ShowDialog ( );

            }
        }
        private void btPrint_Click ( object sender, EventArgs e )
        {
            string sql = "", err = "";
            Frm_Report a = new Frm_Report ( );
            if (DateTime.ParseExact ( dtpckDate.Text.Trim ( ), "dd/MM/yyyy", CultureInfo.InvariantCulture ) < DateTime.ParseExact ( "24/09/2015", "dd/MM/yyyy", CultureInfo.InvariantCulture )) // 2015-09-08 (12)
            {
                #region 03-04-2016

                //sql += "select distinct A.*, ";
                //sql += "                case ";
                //sql += "                  when (A.date_create 90 < add_months(to_date(A.processing_date, ";
                //sql += "                                                              'dd/mm/yyyy'), ";
                //sql += "                                                      -1)) then ";
                //sql += "                   '0' ";
                //sql += "                  else ";
                //sql += "                   to_char(decode(decode(A.machinhsachtd, ";
                //sql += "                                         'B01 - STK - UU DAI', ";           
                //sql += "                                         '1', ";
                //sql += "                                         '0'), ";
                //sql += "                                  '1', ";
                //sql += "                             nab_pck_out.nab_func_getcashback_amt(A.cr_account_nbr, ";
                //sql += "                                                         to_char(add_months(to_date(A.processing_date, ";
                //sql += "                                                                                    'dd/mm/yyyy'), ";
                //sql += "                                                                            -1) 1, ";
                //sql += "                                                                 'dd/mm/yyyy'), ";
                //sql += "                                                         to_char(least(A.date_create 90, ";
                //sql += "                                                                       to_date(A.processing_date, ";
                //sql += "                                                                               'dd/mm/yyyy')), ";
                //sql += "                                                                 'dd/mm/yyyy')), ";
                //sql += "                                  0), ";
                //sql += "                           '999,999,999,999') ";
                //sql += "                end cashback ";
                //if (Frm_main.admin_flag)
                //{
                //    sql += "  from (select substr(r.card_number, 1, 4) || 'xxxxxxxx' || ";
                //    sql += "               substr(r.card_number, 13, 4) card_number, ";
                //}
                //else
                //{
                //    sql += "  from (select substr(r.card_number, 1, 4) || 'xxxxxxxx' || ";
                //    sql += "               substr(r.card_number, 13, 4) card_number, ";
                //}

                //sql += "               r.embossed_name, ";
                //sql += "               r.credit_limit, ";
                //sql += "               r.processing_date, ";
                //sql += "               r.payment_date, ";
                //sql += "               r.opening_bal, ";
                //sql += "               r.total_debit, ";
                //sql += "               r.bal_payment, ";
                //sql += "               r.closing_bal, ";
                //sql += "               r.min_due, ";
                //sql += "               r.transaction_date, ";
                //sql += "               r.value_date, ";
                //sql += "               r.arn, ";
                //sql += "               r.details, ";
                //sql += "               r.billing_amount_du_co, ";
                //sql += "               r.billing_amount_du_no, ";
                //sql += "               r.address, ";
                //sql += "               r.PHONE_HOME, ";
                //sql += "               r.PHONE_MOBILE, ";
                //if (Frm_main.admin_flag)
                //{
                //    sql += "               r.cr_account_nbr cr_account_nbr, ";
                //}
                //else
                //{
                //    sql += "               substr(r.cr_account_nbr,1,4)||'xxxxxxxx'||substr(r.cr_account_nbr,-4) cr_account_nbr, ";
                //}

                //sql += "               r.transaction_amount, ";
                //sql += "               r.transaction_currency, ";
                //sql += "               r.billing_currency, ";
                //sql += "               r.machinhsachtd, ";
                //sql += "               (select date_open ";
                //sql += "                  from " + Businessbp.executedb.owner + "acnt_contract ";
                //sql += "                 where contract_number = r.cr_account_nbr) date_create ";
                //sql += "          from (select substr(a.card_number, 1, 4) || 'xxxxxxxx' || ";
                //sql += "                       substr(a.card_number, 13, 4) card_number, ";
                //sql += "                       a.embossed_name, ";
                //sql += "                       a.credit_limit, ";
                //sql += "                       to_char(a.stat_date, 'dd/mm/yyyy') as processing_date, ";
                //sql += "                       to_char(a.payment_date, 'dd/mm/yyyy') as payment_date, ";
                //sql += "                       a.opening_bal, ";
                //sql += "                       a.total_debit, ";
                //sql += "                       a.bal_payment, ";
                //sql += "                       a.closing_bal, ";
                //sql += "                       a.min_due, ";
                //sql += "                       to_char(a.transaction_date, 'dd/mm/yyyy') as transaction_date, ";
                //sql += "                       to_char(a.value_date, 'dd/mm/yyyy') as value_date, ";
                //sql += "                       a.arn, ";
                //sql += "                       a.details, ";
                //sql += "                       decode(a.transaction_sign, ";
                //sql += "                              '0', ";
                //sql += "                              to_number(''), ";
                //sql += "                              '1', ";
                //sql += "                              a.billing_amount, ";
                //sql += "                              a.billing_amount) billing_amount_du_co, ";
                //sql += "                       decode(a.transaction_sign, ";
                //sql += "                              '0', ";
                //sql += "                              a.billing_amount, ";
                //sql += "                              '1', ";
                //sql += "                              to_number(''), ";
                //sql += "                              a.billing_amount) billing_amount_du_no, ";
                //sql += "                       (a.MAILING_ADDRESS1 || ' ' || a.MAILING_ADDRESS2 || ' ' || ";
                //sql += "                       a.MAILING_ADDRESS3 || ' ' || a.MAILING_ADDRESS4) address, ";
                //sql += "                       a.PHONE_HOME, ";
                //sql += "                       a.PHONE_MOBILE, ";
                //sql += "                       a.cr_account_nbr cr_account_nbr, ";
                //sql += "                       a.transaction_amount, ";
                //sql += "                       (select b.name ";
                //sql += "                          from " + Businessbp.executedb.owner + "currency b ";
                //sql += "                         where trim(b.code) = trim(a.transaction_currency)";
                //sql += "                            and b.amnd_state = 'A') transaction_currency, ";  
                //sql += "                       (select b.name ";
                //sql += "                          from " + Businessbp.executedb.owner + "currency b ";
                //sql += "                         where trim(b.code) = trim(a.billing_currency)";
                //sql += "                        and b.amnd_state = 'A') billing_currency, ";
                //sql += "                       c.machinhsachtd ";
                //sql += "                  from nab_billing_credit_log a, ";
                //sql += "                       " + Businessbp.executedb.owner + "acnt_contract      crd, ";
                //sql += "                       nab_cp_card            c ";
                //sql += "                 where trunc(a.stat_date) = ";
                //sql += "                       trunc(to_date('" + dtpckDate.Text.Trim ( ) + "', ";
                //sql += "                                     'dd/mm/yyyy')) ";
                //sql += "                   and a.card_number = crd.contract_number ";
                //sql += "                   and crd.add_info_01 = c.source_application_no                  ";
                //sql += "                   and crd.amnd_state = 'A' ";
                //sql += "                   and crd.con_cat = 'C' ";
                //if (rbNV.Checked == true)
                //    sql += "               and trim(c.connect_school) = '000001'";
                //if (rbKH.Checked == true)
                //    sql += "               and trim(c.connect_school) <> '000001'";
                //if (cbNormal.Checked == true)
                //{
                //    sql += "               and not exists";
                //    sql += "             (select 1";
                //    sql += "                      from " + Businessbp.executedb.owner + "nab_credit_unpaid ff";
                //    sql += "                     where ff.cr_account_nbr = a.cr_account_nbr";
                //    sql += "                       and substr(ff.nqh_code, 1, 1) = 'O')";
                //    a.type = "BILILING_CREDIT";
                //    a.title3 = "Yêu cầu thanh toán trước 10h ngày:";
                //}
                //if (cbNQH.Checked == true)
                //{
                //    sql += "               and exists";
                //    sql += "             (select 1";
                //    sql += "                      from " + Businessbp.executedb.owner + "nab_credit_unpaid ff";
                //    sql += "                     where ff.cr_account_nbr = a.cr_account_nbr";
                //    sql += "                       and substr(ff.nqh_code, 1, 1) = 'O')";
                //    a.type = "BILILING_CREDIT_NQH";
                //    a.title3 = "Nợ đã chuyển nợ quá hạn. Đề nghị thanh toán toàn bộ dư nợ";
                //}
                //if (cbNQHKeotheo.Checked == true)
                //{
                //    sql += "               and exists";
                //    sql += "             (select 1";
                //    sql += "                      from " + Businessbp.executedb.owner + "nab_credit_unpaid ff";
                //    sql += "                     where ff.cr_account_nbr = a.cr_account_nbr";
                //    sql += "                       and substr(ff.nqh_code, 1, 1) = 'L')";
                //    a.type = "BILILING_CREDIT_NQH";
                //    a.title3 = "Nợ đã chuyển nợ quá hạn. Đề nghị thanh toán toàn bộ dư nợ";
                //}
                //if (cbNoEmail.Checked == true)
                //{
                //    sql += "  and (INSTR(cc.email_id, '@', 1) = 0 or length(cc.email_id) = 0) ";
                //}
                //sql += "                UNION ALL";
                //sql += "                select a.card_number,";
                //sql += "                       c.client_code,";
                //sql += "                       a.embossed_name,";
                //sql += "                       a.credit_limit,";
                //sql += "                       to_char(a.stat_date, 'dd/mm/yyyy') as processing_date,";
                //sql += "                       to_char(a.payment_date, 'dd/mm/yyyy') as payment_date,";
                //sql += "                       a.opening_bal,";
                //sql += "                       a.total_debit,";
                //sql += "                       a.bal_payment,";
                //sql += "                       a.closing_bal,";
                //sql += "                       a.min_due,";
                //sql += "                       to_char(a.transaction_date, 'dd/mm/yyyy') as transaction_date,";
                //sql += "                       to_char(a.value_date, 'dd/mm/yyyy') as value_date,";
                //sql += "                       a.arn,";
                //sql += "                       a.details,";
                //sql += "                       a.billing_amount billing_amount_du_co,";
                //sql += "                       a.billing_amount billing_amount_du_no,";
                //sql += "                       (a.MAILING_ADDRESS1 || ' ' || a.MAILING_ADDRESS2 || ' ' ||";
                //sql += "                       a.MAILING_ADDRESS3 || ' ' || a.MAILING_ADDRESS4) address,";
                //sql += "                       a.PHONE_HOME,";
                //sql += "                       a.PHONE_MOBILE,";
                //sql += "                       a.cr_account_nbr,";
                //sql += "                       a.transaction_amount transaction_amount,";
                //sql += "                       (select b.currency_code_alpha";
                //sql += "                          from " + Businessbp.executedb.owner + "currency_table b";
                //sql += "                         where trim(b.currency_code) =";
                //sql += "                               trim(a.transaction_currency) and b.amnd_state = 'A') transaction_currency,";
                //sql += "                       (select b.currency_code_alpha";
                //sql += "                          from " + Businessbp.executedb.owner + "currency_table b";
                //sql += "                         where trim(b.currency_code) =";
                //sql += "                               trim(a.billing_currency) and b.amnd_state = 'A') billing_currency,";
                //sql += "                       c.machinhsachtd";
                //sql += "                  from " + Businessbp.executedb.owner + "nab_billing_credit_log a,";
                //sql += "                       " + Businessbp.executedb.owner + "application            app,";
                //sql += "                       " + Businessbp.executedb.owner + "cp_card                c";
                //sql += "                 where 1 = 1";
                //sql += "                   and a.cr_account_nbr = app.card_number";
                //sql += "                   and app.application_no = c.source_application_no";
                //sql += "                   and trunc(a.stat_date) =";
                //sql += "                       trunc(to_date('" + dtpckDate.Text.Trim ( ) + "', 'dd/mm/yyyy'))";
                //sql += "                   and trim(a.transaction_code) is null";
                //if (rbNV.Checked == true)
                //    sql += "               and trim(c.connect_school) = '000001'";
                //if (rbKH.Checked == true)
                //    sql += "               and trim(c.connect_school) <> '000001'";
                //if (cbNormal.Checked)
                //{
                //    sql += "      and  exists (select 1 ";
                //    sql += "                           from " + Businessbp.executedb.owner + "v_cs_all_acnt_status v ";
                //    sql += "                          where v.acnt_contract__oid = acc.id";
                //    sql += "                            and v.status_type_code = 'NAB_BOVD_CAT' ";
                //    sql += "                            and v.status_value_code = 'A') ";
                //    a.type = "BILILING_CREDIT";
                //    a.title3 = "Yêu cầu thanh toán trước 10h ngày:";
                //}
                //if (cbNQH.Checked)
                //{
                //    sql += "      and exists (select 1 ";
                //    sql += "                           from " + Businessbp.executedb.owner + "v_cs_all_acnt_status v ";
                //    sql += "                          where v.acnt_contract__oid = acc.id";
                //    sql += "                            and v.status_type_code = 'NAB_BOVD_CAT' ";
                //    sql += "                            and v.status_value_code = 'B') ";
                //    a.type = "BILILING_CREDIT_NQH";
                //    a.title3 = "Nợ đã chuyển nợ quá hạn. Đề nghị thanh toán toàn bộ dư nợ";
                //}
                //if (cbNQHKeotheo.Checked)
                //{
                //    sql += "      and exists (select 1 ";
                //    sql += "                           from " + Businessbp.executedb.owner + "v_cs_all_acnt_status v ";
                //    sql += "                          where v.acnt_contract__oid = acc.id";
                //    sql += "                            and v.status_type_code = 'NAB_BOVD_CAT' ";
                //    sql += "                            and v.status_value_code = 'B') ";
                //    a.type = "BILILING_CREDIT_NQH";
                //    a.title3 = "Nợ đã chuyển nợ quá hạn. Đề nghị thanh toán toàn bộ dư nợ";
                //}
                //sql += "   ) r";
                //sql += "         where 1 = 1";
                //if (cbNoEmail.Checked == true)
                //{
                //    sql += "  and (INSTR(c.email_id, '@', 1) = 0 or length(c.email_id) = 0) ";
                //}
                //sql += "         order by r.client_code,";
                //sql += "                  r.card_number asc,";
                //sql += "                  to_date(r.transaction_date, 'dd/mm/yyyy') desc) A";
                //sql += " where 1 = 1"; //2015-05-27 (10)
                //sql += " order by A.client_code asc, A.cr_account_nbr asc, A.card_number asc, to_date(A.transaction_date, 'dd/mm/yyyy') desc "; //2015-05-27 (10) //2015-06-23 (11)
                #endregion 2015-05-20 (9)
            }
            #region 03-04-2016
            else
            {
                sql += " select * from ( ";
                sql += "select (case when l.card_number = l.cr_account_nbr then l.card_number else (substr(l.card_number, 1, 4) || ' ' || substr(l.card_number, 5, 2) || ";
                sql += "       'xx xxxx ' || substr(l.card_number, 13, 4)) end) card_number, ";
                sql += "       l.cr_account_nbr cr_account_nbr, ";
                sql += "       l.embossed_name, ";
                sql += "       l.phone_mobile, ";
                sql += "       nvl(l.credit_limit, 0) credit_limit_acc, ";
                sql += "       nvl(l.credit_limit_client, 0) credit_limit_client, ";
                sql += "       nvl(l.avail_amount_acc, 0) avail_amount_acc, ";
                sql += "       nvl(l.avail_amount_client, 0) avail_amount_client, ";
                sql += "       to_char(l.stat_date, 'dd/mm/yyyy') as stat_date, ";
                sql += "       to_char(l.payment_date, 'dd/mm/yyyy') as payment_date, ";
                sql += "       nvl(l.opening_bal, 0) opening_bal, ";
                sql += "       nvl(l.total_debit, 0) total_debit, ";
                sql += "       (nvl(l.fee, 0) + nvl(l.interest, 0)) fee_and_interest, ";
                sql += "       nvl(l.bal_payment, 0) bal_payment, ";
                sql += "       nvl(l.closing_bal, 0) closing_bal, ";
                sql += "       (trim(l.MAILING_ADDRESS1) || ' ' || trim(l.MAILING_ADDRESS2) || ' ' || ";
                sql += "       trim(l.MAILING_ADDRESS3) || ' ' || trim(l.MAILING_ADDRESS4)) address, ";
                sql += "       to_char(l.transaction_date, 'dd/mm/yyyy') as transaction_date, ";
                sql += "       to_char(l.value_date, 'dd/mm/yyyy') as value_date, ";
                sql += "       l.arn, ";
                sql += "       nvl(l.transaction_amount, 0) transaction_amount, ";
                sql += "       (select '(' || ccy.name || ')' ";
                sql += "          from " + Businessbp.executedb.owner + "currency ccy ";
                sql += "         where trim(ccy.code) = trim(l.transaction_currency) and ccy.amnd_state = 'A') transaction_currency, ";
                sql += "       nvl(l.billing_amount, 0) billing_amount, ";
                sql += "       decode(l.transaction_sign, ";
                sql += "              '-1', ";
                sql += "              '(DR)', ";
                sql += "              '1', ";
                sql += "              '(CR)', ";
                sql += "              '') transaction_sign, ";
                sql += "       l.details, ";
                sql += "       decode(l.ovd_flag,'N',nvl(l.min_due,0),nvl(l.closing_bal,0)) min_due, ";
                sql += "       nvl(l.min_due_install, 0) min_due_install, ";
                sql += "       nvl(l.min_due_crterm, 0) min_due_crterm, ";
                sql += "       nvl(l.cash_back_amount, 0) cash_back_amount, ";
                sql += "       nvl(l.diamondbay_amount, 0) diamondbay_amount, ";
                sql += "       nvl(l.diamondbay_rank, 0) diamondbay_rank, ";
                sql += "       nvl(l.loyalty_score_earned, 0) loyalty_score_earned, ";
                sql += "       nvl(l.loyalty_score_spent, 0) loyalty_score_spent, ";
                sql += "       nvl(l.loyalty_cash_exchanged, 0) loyalty_cash_exchanged, ";
                sql += "       nvl(l.loyalty_score_avail, 0) loyalty_score_avail, ";
                sql += "       nvl(l.transaction_fee,0) transaction_fee,";
                sql += "       nvl(l.ovd_flag,'N') ovd_flag,";
                sql += "       ( select substr(v.status_value_code,1,1) ";
                sql += "                           from " + Businessbp.executedb.owner + "v_cs_all_acnt_status v ";
                sql += "                          where v.acnt_contract__oid = acc.id";
                sql += "                            and v.id = '540' ) ovd_type, ";
                sql += "       acc.client__id ";
                sql += "  from nab_billing_credit_log l, ";
                sql += "       " + Businessbp.executedb.owner + "acnt_contract acc ";
                sql += " where acc.amnd_state = 'A' ";
                sql += "   and l.closing_bal > 0";
                sql += "   and l.cr_account_nbr = acc.contract_number ";
                sql += "   and l.stat_date =trunc(to_date('" + dtpckDate.Text.Trim ( ) + "', 'dd/mm/yyyy')) ";
                if (rbNV.Checked == true)
                {
                    sql += " and  exists  ( ";
                    sql += "              select cp.connect_school ";
                    sql += "              from  nab_cp_card cp  ";
                    sql += "                      ," + Businessbp.executedb.owner + "acnt_contract crd ";
                    sql += "              where crd.add_info_01 = cp.source_application_no ";
                    sql += "              and crd.acnt_contract__oid = acc.id";
                    sql += "              and crd.amnd_state = 'A' ";
                    sql += "              and crd.con_cat = 'C' ";
                    sql += "              and trim(cp.machinhsachtd) in ( 'A14 - NTNAB' , 'A07 - NAB' )";
                    sql += "           )  ";
                }
                if (rbKH.Checked == true)
                {
                    sql += " and not exists  ( ";
                    sql += "              select cp.connect_school ";
                    sql += "              from  nab_cp_card cp  ";
                    sql += "                      ," + Businessbp.executedb.owner + "acnt_contract crd ";
                    sql += "              where crd.add_info_01 = cp.source_application_no ";
                    sql += "              and crd.acnt_contract__oid = acc.id";
                    sql += "              and crd.amnd_state = 'A' ";
                    sql += "              and crd.con_cat = 'C' ";
                    sql += "              and trim(cp.machinhsachtd) in ( 'A14 - NTNAB' , 'A07 - NAB' )";
                    sql += "           )  ";
                }
                if (cbNormal.Checked == true)
                {
                    sql += " and nvl(l.ovd_flag,'N') = 'N' ";
                }
                else
                {
                    sql += " and nvl(l.ovd_flag,'N') = 'Y' ";
                }
                if (cbNoEmail.Checked == true)
                {
                    sql += " and  ( ( select nvl(addr.e_mail,'0')   from   " + Businessbp.executedb.owner + "contract_addresses addr   where  addr.acnt_contract__oid = acc.id    and addr.address_type__id = '1'         )      = '0' ";
                    sql += " or   ( select instr(addr.e_mail,'@',1)   from   " + Businessbp.executedb.owner + "contract_addresses addr   where  addr.acnt_contract__oid = acc.id    and addr.address_type__id = '1'         )      = 0 ) ";
                }
                sql += " ) ";
                if (cbNQH.Checked == true)
                    sql += " where ovd_type = 'O' ";
                if (cbNQHKeotheo.Checked == true)
                    sql += " where ovd_type = 'L' ";
                sql += " order by client__id, cr_account_nbr,card_number asc, transaction_date , value_date desc    ";
            }
            #endregion 2015-09-08 (12)
            DataTable dtt = Businessbp.executedb.getTable ( sql, ref err );
            if (err != "")
            {
                MessageBox.Show ( err );
                return;
            }

            a.type = "BILILING_CREDIT_3";
            a.dt = dtt;
            a.title = "BẢNG THÔNG BÁO GIAO DỊCH";
            a.title2 = "(Từ ngày " + dtpckDate.Value.AddMonths ( -1 ).ToString ( "dd/MM/yyyy" ).Trim ( ) + " đến ngày " + dtpckDate.Text.Trim ( ) + " )";
            a.ShowDialog ( );
        }
        private void bt_details_Click(object sender, EventArgs e)
        {
            Frm_Report a = new Frm_Report();
            //string file_ID = "";

            string sql = "", err = "";
            string type = "";
            if (cbType.SelectedIndex + 1 == 1)
            {
                type = "ACCOUNTING_THAUCHI";
                //file_ID = "1";
                a.title = "BÁO CÁO HẠCH TOÁN THẤU CHI";
                sql = sql_detail_task(type, cb_ChucNang.SelectedIndex);
            }
            else if (cbType.SelectedIndex + 1 == 2)
            {
                type = "ACCOUNTING_TRANSACTION";
                a.title = "BÁO CÁO HẠCH TOÁN GIAO DỊCH";
                sql = sql_detail_task(type, cb_ChucNang.SelectedIndex);
            }
            DataTable dt = Businessbp.executedb.getTable(sql, ref err);
            if (err != "")
            {
                MessageBox.Show(err);
                return;
            }
            a.type = "ACCOUNTING_REPORT_DETAILS";
            a.dt = dt;
            a.thoiGian = "Từ ngày " + dtFromDate.Text + " đến ngày " + dtToDate.Text;
            a.ShowDialog();
        }
        private void button1_Click ( object sender, EventArgs e )
        {
            Frm_Report a = new Frm_Report ( );
            string sql = "", err = "";
            //    1. Báo cáo hạch toán thấu chi
            //2. Báo cáo hạch toán giao dịch
            //3. Báo cáo hạch toán điều chỉnh Credit
            //4. Báo cáo hạch toán điều chỉnh Debit
            //5. Báo cáo hạch toán lãi Prepaid
            string file_ID = "";

            string type = "";
            if (cbType.SelectedIndex + 1 == 1)
            {
                file_ID = "1";
                a.title = "BÁO CÁO HẠCH TOÁN NẠP TIỀN";
            }
            else if (cbType.SelectedIndex + 1 == 2)
            {
                file_ID = "2";
                a.title = "BÁO CÁO HẠCH TOÁN GIAO DỊCH";
            }
            else if (cbType.SelectedIndex + 1 == 3)
            {
                file_ID = "3";
                a.title = "BÁO CÁO HẠCH TOÁN ĐIỀU CHỈNH CREDIT";
            }
            else if (cbType.SelectedIndex + 1 == 4)
            {
                MessageBox.Show ( "Unuse!!!" );
                return;
            }
            else if (cbType.SelectedIndex + 1 == 5)
            {
                file_ID = "5";
                a.title = "BÁO CÁO HẠCH TOÁN THANH TOÁN ĐẠI LÝ";
            }
            sql = "";
            sql += " select * from ( ";
            sql += "  select b.code||'-'||b.name as Branch,to_char(a.date_proccessing,'dd/mm/yyyy') date_process";
            sql += "         ,a.account_number,round(sum(a.amount_accounting)) amount_accounting,a.dr_cr,a.accounting_type description,'' Ghi_Chu";
            sql += "  from  nab_accounting a," + Businessbp.executedb.owner + "branch b";
            sql += "  where trim(a.account_branch)=b.code";
            sql += "        and a.file_id = '" + file_ID + "'";
            sql += "        and trunc(a.date_proccessing) >=trunc(to_date('" + dtFromDate.Text + "','dd/MM/yyyy'))";
            sql += "        and trunc(a.date_proccessing) <=trunc(to_date('" + dtToDate.Text + "','dd/MM/yyyy'))";
            //sql += "        and nvl(a.transaction_flag,'0')='1'"; //2014-11-20 (1) old
            sql += "        and a.description not like '%FEE ANNUAL%'"; //2014-11-20 (1) new
            if (cbType.SelectedIndex + 1 == 1 || cbType.SelectedIndex + 1 == 2)
            {
                sql += " and a.accounting_type <> 'Upper Norm' ";
            }
            sql += " group  by  a.accounting_type ";
            sql += "                  ,a.account_number ";
            sql += "                  ,a.account_branch ";
            sql += "                  ,a.date_proccessing";
            sql += "                  ,a.dr_cr, b.code, b.name  ";
            if (cbType.SelectedIndex + 1 == 3)
            {
                sql += " union all ";
                sql += "  select b.code||'-'||b.name as Branch,to_char(a.date_proccessing,'dd/mm/yyyy') date_process";
                sql += "         ,a.account_number,round(sum(a.amount_accounting)) amount_accounting,a.dr_cr,a.accounting_type description,'' Ghi_Chu";
                sql += "  from  nab_accounting a," + Businessbp.executedb.owner + "branch b";
                sql += "  where trim(a.account_branch)=b.code";
                sql += "        and a.file_id in ( '1','2' )";
                sql += "        and trunc(a.date_proccessing) >=trunc(to_date('" + dtFromDate.Text + "','dd/MM/yyyy'))";
                sql += "        and trunc(a.date_proccessing) <=trunc(to_date('" + dtToDate.Text + "','dd/MM/yyyy'))";
                //sql += "        and nvl(a.transaction_flag,'0')='1'"; //2014-11-20 (1) old
                sql += "        and a.description not like '%FEE ANNUAL%'"; //2014-11-20 (1) new
                sql += "        and a.accounting_type = 'Upper Norm' ";
                sql += " group  by  a.accounting_type ";
                sql += "                  ,a.account_number ";
                sql += "                  ,a.account_branch ";
                sql += "                  ,a.date_proccessing";
                sql += "                  ,a.dr_cr, b.code, b.name  ";
            }
       
            sql += " ) ";

            sql += "  order by date_process desc";
            DataTable dtt = Businessbp.executedb.getTable ( sql, ref err );
            if (err != "")
            {
                MessageBox.Show ( err );
                return;
            }
            a.type = "ACCOUNTING_REPORT";
            a.dt = dtt;
            a.thoiGian = "Từ ngày " + dtFromDate.Text + " đến ngày " + dtToDate.Text;
            a.ShowDialog ( );
        }
        private void btReportHachToan_Click(object sender, EventArgs e)
        {
            Frm_Report a = new Frm_Report();
            a.type = "RP_MC_FEE_PHAITHU_TRA";
            string title = "BÁO CÁO TỔNG HỢP PHÍ MASTER";
            string sql = "", err = "";
            #region old_dat 18/01/2013
            //sql = "  select a.settlement_currency TIEN_TE";
            //sql += "         ,substr(a.file_id,4,6) NGAY";
            //sql += "         ,a.file_id FILE_ID,decode(settlement_currency,'704',round(a.fee_phai_thu),a.fee_phai_thu) PHAI_THU ";
            //sql += "         ,a.file_id FILE_ID,decode(settlement_currency,'704',round(a.fee_phai_tra),a.fee_phai_tra) PHAI_TRA ";            
            //sql += "         ,(a.fee_phai_thu - a.fee_phai_tra) as CHENH_LECH";
            //sql += "         ,'' GHI_CHU";
            #endregion old_dat 18/01/2013
            sql = "  select decode(a.current_mcsettlement,null,a.settlement_currency,a.current_mcsettlement) TIEN_TE";
            sql += "         ,substr(a.file_id,4,6) NGAY";
            sql += "         ,a.file_id FILE_ID,decode(decode(a.current_mcsettlement,null,a.settlement_currency,a.current_mcsettlement),'704',round(a.fee_phai_thu),a.fee_phai_thu) PHAI_THU ";
            sql += "         ,a.file_id FILE_ID,decode(decode(a.current_mcsettlement,null,a.settlement_currency,a.current_mcsettlement),'704',round(a.fee_phai_tra),a.fee_phai_tra) PHAI_TRA ";
            sql += "         ,(a.fee_phai_thu - a.fee_phai_tra) as CHENH_LECH";
            sql += "         ,'' GHI_CHU";
            sql += "  from nab_clearning_mastercard a";
            sql += "  where nvl(a.fee_flag,'0')<>'1'";//Chưa hạch toán
            sql += "        and  nvl(a.action_ttt,'0')='1'";//Chỉ hạch toán tạm treo tại TTT
            //sql += "        and nvl(a.action_mastercard,'0')<>'1'";
            sql += "        and type not in ('FILLING','FEES CHRG')";
           // sql += "        and trunc(a.date_create)>=trunc(to_date('"+dateTimePicker1.Text+"','dd/mm/yyyy'))";
            sql += "        and trunc(a.date_create)<=trunc(to_date('" + dateTimePicker2.Text + "','dd/mm/yyyy'))";
            
            if (tbCCY.Text.Trim() != "")
            {
                title += " - "+tbCCY.Text.Trim();
                sql += " and settlement_currency='"+tbCCY.Text.Trim()+"'";
            }
            sql += "        order by substr(a.file_id,4,6)";


            DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
            if (err != "")
            {
                MessageBox.Show(err);
                return;
            }
            a.title = title;
            a.thoiGian = "Từ " + dateTimePicker1.Text + " đến " + dateTimePicker2.Text;
            a.dt = dtt;
            a.ShowDialog();
        }
        private void button3_Click(object sender, EventArgs e)
        {
            //return; //Phuong disable here
            Frm_Report a = new Frm_Report();
            a.type = "3";
            string sql = "", err = "";
            string phipos = "0";

            if (rbTypeIssuer1.Checked)
            {
                a.title = "ISSUER: IN BANKNET - NOT IN ELECTRA";
                    sql = " select hh.LoaiTB,hh.LoaiGD,hh.Sothe,hh.ngaygd, hh.trace, hh.sotien,  decode(substr(hh.sothe,1,6),'970428',decode(hh.loaigd,'GIAO DICH PHI TAI CHINH',1650,'RUT TIEN',3300,0),decode(hh.loaitb,'POS',(" + phipos + "*hh.sotien),decode(hh.loaigd,'GIAO DICH PHI TAI CHINH',1100,'RUT TIEN',1650,1650))) as phi " +
                   " from (";
               
                sql += " select decode(nvl(mcc,'6011'),6011,'ATM','POS') as LoaiTB," +
                              " decode(a.MESSAGE_TYPE,'1420','SUPPECT REVERSAL-1420',decode(substr(a.processing_code,1,2),'01','RUT TIEN','40','GIAO DICH PHI TAI CHINH','00','MUA HANG','30','GIAO DICH PHI TAI CHINH','35','GIAO DICH PHI TAI CHINH')) as LoaiGD," +
                              " a.card_number sothe, a.transaction_local_date ngaygd,a.trace_audit_number trace,a.transaction_amount sotien" +
                       " from banknet_issuer_dispute_pri1 a" +
                       " where 1=1";
            }
            else if (rbTypeIssuer2.Checked)
            {
                a.title = "ISSUER: NOT IN BANKNET - IN ELECTRA";
                    sql = " select hh.LoaiTB,hh.LoaiGD,hh.Sothe,hh.ngaygd, hh.trace, hh.sotien,  decode(substr(hh.sothe,1,6),'970428',decode(hh.loaigd,'GIAO DICH PHI TAI CHINH',1650,'RUT TIEN',3300,0),decode(hh.loaitb,'POS',(" + phipos + "*hh.sotien),decode(hh.loaigd,'GIAO DICH PHI TAI CHINH',1100,'RUT TIEN',1650,1650))) as phi " +
                   " from (";
               
                sql += " select decode(nvl(mcc,'6011'),6011,'ATM','POS') as LoaiTB," +
                              " decode(a.MESSAGE_TYPE,'1420','SUPPECT REVERSAL-1420',decode(substr(a.processing_code,1,2),'01','RUT TIEN','40','GIAO DICH PHI TAI CHINH','00','MUA HANG','30','GIAO DICH PHI TAI CHINH','35','GIAO DICH PHI TAI CHINH')) as LoaiGD," +
                              " a.card_number sothe, a.transaction_local_date ngaygd,a.trace_audit_number trace,a.transaction_amount sotien" +
                       " from banknet_issuer_dispute a" +
                       " where 1=1";
            }
            else if (rbTypeAcquier1.Checked == true)
            {
                a.title = "ACQUIER: IN BANKNET - NOT IN ELECTRA";
                //sql = " select hh.LoaiTB,hh.LoaiGD,hh.Sothe,hh.ngaygd, hh.trace, hh.sotien, decode(substr(hh.sothe,1,6),'970428',decode(hh.loaigd,'GIAO DICH PHI TAI CHINH',1650,'RUT TIEN',3300,0),decode(hh.loaitb,'POS',(" + phipos + "*hh.sotien),decode(hh.loaigd,'GIAO DICH PHI TAI CHINH',1100,'RUT TIEN',1650,1650))) as phi " +
                //    " from (";
                //sql += " select decode(nvl(mcc,'6011'),6011,'ATM','POS') as LoaiTB," +
                //              " decode(a.MESSAGE_TYPE,'1420','SUPPECT REVERSAL-1420',decode(substr(a.processing_code,1,2),'01','RUT TIEN','40','GIAO DICH PHI TAI CHINH','00','MUA HANG','30','GIAO DICH PHI TAI CHINH','35','GIAO DICH PHI TAI CHINH')) as LoaiGD," +
                //              " a.card_number sothe, a.transaction_local_date ngaygd,a.trace_audit_number trace,a.transaction_amount sotien" +
                //       " from "+Businessbp.executedb.owner+"banknet_acq_dispute_pri a";

                //Dat sua ngay 11/01/2012 --> Set phi the CUP
                    sql = "select hh.LoaiTB,hh.LoaiGD,hh.Sothe,hh.ngaygd, hh.trace, hh.sotien,";
                
                     sql+=        " decode(substr(hh.sothe,1,6),'970428',decode(hh.loaigd,'GIAO DICH PHI TAI CHINH',1650," +
                                                                                   " 'RUT TIEN',3300,0)," +
                                                                    " decode(hh.Cup_flag,'CUP', decode(hh.loaitb,'POS',(0*hh.sotien)," +
                                                                                                                      " decode(hh.loaigd,'GIAO DICH PHI TAI CHINH',2124," +
                                                                                                                                        " 'RUT TIEN',11801," +
                                                                                                                                        " 2124))," +
                                                                                              " decode(hh.loaitb,'POS',(0*hh.sotien)," +
                                                                                                                      " decode(hh.loaigd,'GIAO DICH PHI TAI CHINH',1100," +
                                                                                                                                        " 'RUT TIEN',1650," +
                                                                                                                                        " 1650))" +
                             ")) as phi" +
                      " from (";
                sql += "select decode(nvl(mcc,'6011'),6011,'ATM','POS') as LoaiTB," +
                              " decode(a.MESSAGE_TYPE,'1420','SUPPECT REVERSAL-1420'," +
                                                             " decode(substr(a.processing_code,1,2),'01','RUT TIEN'," +
                                                                                                  " '40','GIAO DICH PHI TAI CHINH'," +
                                                                                                  " '00','MUA HANG'," +
                                                                                                  " '30','GIAO DICH PHI TAI CHINH'," +
                                                                                                  " '35','GIAO DICH PHI TAI CHINH')" +
                              " ) as LoaiGD," +
                              " decode(b.tenviettac,'CUP','CUP','NOT CUP') as Cup_flag," + //--> pending: the CUP dau so the khac 6 so
                              " a.card_number sothe, a.transaction_local_date ngaygd,a.trace_audit_number trace,a.transaction_amount sotien" +
                        " from banknet_acquirer_dispute a, NAB_BNK_BANK b" +
                        " where substr(a.card_number,1,6) = trim(b.sodaucuathe)";
                //End Dat sua ngay 11/01/2012 --> Set phi the CUP
            }
            else if (rbTypeAcquier2.Checked)
            {
                return;
                //sql = " select hh.LoaiTB,hh.LoaiGD,hh.Sothe,hh.ngaygd, hh.trace, hh.sotien,  decode(substr(hh.sothe,1,6),'970428',decode(hh.loaigd,'GIAO DICH PHI TAI CHINH',1650,'RUT TIEN',3300,0),decode(hh.loaitb,'POS',(" + phipos + "*hh.sotien),decode(hh.loaigd,'GIAO DICH PHI TAI CHINH',1100,'RUT TIEN',1650,1650))) as phi " +
                //      " from (";
                //sql += " select decode(nvl(mcc,'6011'),6011,'ATM','POS') as LoaiTB," +
                //              " decode(a.MESSAGE_TYPE,'1420','SUPPECT REVERSAL-1420',decode(substr(a.processing_code,1,2),'01','RUT TIEN','40','GIAO DICH PHI TAI CHINH','00','MUA HANG','30','GIAO DICH PHI TAI CHINH','35','GIAO DICH PHI TAI CHINH')) as LoaiGD," +
                //              " a.card_number sothe, a.transaction_local_date ngaygd,a.trace_audit_number trace,a.transaction_amount sotien" +
                //       " from "+Businessbp.executedb.owner+"banknet_acq_dispute_pri1 a";
            }
            else if (rbTypeManual.Checked)
            {
                a.title = "MANUAL BANKNET ENTER";
                    sql = " select hh.LoaiTB,hh.LoaiGD,hh.Sothe,hh.ngaygd, hh.trace, hh.sotien, decode(substr(hh.sothe,1,6),'970428',decode(hh.loaigd,'GIAO DICH PHI TAI CHINH',1650,'RUT TIEN',3300,0),decode(hh.loaitb,'POS',(" + phipos + "*hh.sotien),decode(hh.loaigd,'GIAO DICH PHI TAI CHINH',1100,'RUT TIEN',1650,1650))) as phi " +
                       " from (";
                sql += " select decode(nvl(private_data_c_40,'ATM'),'ATM','ATM','POS') as LoaiTB," +
                              " decode(a.MESSAGE_TYPE,'1420','SUPPECT REVERSAL-1420',decode(substr(a.processing_code,1,2),'01','RUT TIEN','40','GIAO DICH PHI TAI CHINH','00','MUA HANG','30','GIAO DICH PHI TAI CHINH','35','GIAO DICH PHI TAI CHINH')) as LoaiGD," +
                              " a.card_number sothe, a.transaction_local_date ngaygd,a.trace_audit_number trace,a.transaction_amount sotien" +
                       " from banknet_dispute_pri a" +
                       " where 1=1";
            }

            //---------------  
            if (rbFlag1.Checked)
                sql += " and nvl(chargeback_flag,'0') = '1'";
            if (rbFlag2.Checked)
                sql += " and nvl(chargeback_flag,'0') = '0' and rc_receive is not null";
            if (rbFlag3.Checked)
                sql += " and rc_receive is not null and nvl(chargeback_flag,'0') = '0'";
            if (rbFlag4.Checked)
                // sql += " and rc_receive is null and nvl(chargeback_flag,'0') = '0'";
                sql += " and rc_receive is null and nvl(chargeback_flag,'0') in ('0','2')";

            //--------------
            if (tbBatchNumber.Text.Trim() != "")
                sql += " and batch_number = '" + tbBatchNumber.Text.Trim() + "'";

            //-------------
            if (tbTrace.Text.Trim() != "")
                sql += " and trace_audit_number = '" + tbTrace.Text.Trim() + "'";
            //---------------
            if (sql != "")
            {
                sql += " ) hh " +
                      " order by ngaygd ";
            }
            // tbTrace.Text = sql;

            DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
            if (err != "")
            {
                MessageBox.Show(err);
                return;
            }
            a.dt = dtt;
            a.ShowDialog();
        }
 private void bt_report_Click ( object sender, EventArgs e )
 {
     string err = "";
     #region 2016-05-09 (TUANANH)
     Frm_Report a = new Frm_Report ( );
     a.type = "PHI_CHIET_KHAU_DAI_LY";
     dt = Businessbp.executedb.getTable ( LoadData ( ), ref err );
     if (LoadData ( ) != null)
     {
         string title = "TỔNG PHÍ CHIẾT KHẤU ĐẠI LÝ CUỐI THÁNG";
         a.title = title;
         a.title2 = "Ngày in Report " + DateTime.Parse ( DateTime.Now.ToShortDateString ( ) ).ToString ( "dd/MM/yyyy" );
         a.dt = dt;
         a.ShowDialog ( );
     }
     #endregion 2016-05-09 (TUANANH)
 }
        private void btView_Click ( object sender, EventArgs e )
        {
            #region R15
            //-- bao cao giao dich IPM file MC
            Frm_Report a = new Frm_Report();
            a.type = "MC_15";
            string sql = "", err = "";
            if (cbbRbType.SelectedIndex == 0)
            {
               
                if (rbUnmatch.Checked == true)
                {
                    if (Frm_main.admin_flag)
                    {
                        sql += " select dc.target_number card_no";
                    }
                    else
                    {
                        sql += " select substr(dc.target_number,1,4)||'xxxxxxxx'||substr(dc.target_number,-4) card_no";
                    }
                    
                    sql += "       ,dc.trans_details descript";
                    sql += "       ,dc.trans_date trans_date";
                    sql += "       ,dc.posting_date proc_date";
                    sql += "       ,dc.trans_amount trans_amt";
                    sql += "       ,dc.trans_curr trans_curr";
                    sql += "       ,dc.settl_amount bill_amt";
                    sql += "       ,dc.settl_curr bill_curr";
                    sql += "       ,'' settle_amt";
                    sql += "       ,'' settle_curr";
                    sql += " from " + Businessbp.executedb.owner + "doc dc, " + Businessbp.executedb.owner + "m_transaction mt";
                    sql += " where dc.is_authorization = 'N'";
                    sql += " and mt.doc__oid = dc.id";
                    sql += " and dc.amnd_state = 'A'";
                    sql += " and dc.posting_status = 'P'";
                    sql += " and dc.return_code = '101'";
                    sql += " and dc.posting_date >= to_date('" + dtFrom.Text.Trim ( ) + "','dd/mm/yyyy')";
                    sql += " and dc.posting_date <= to_date('" + dtTo.Text.Trim ( ) + "','dd/mm/yyyy')";
                    sql += " order by dc.target_number";
                }
                if (rbLatePre.Checked == true)
                {
                    if (Frm_main.admin_flag)
                    {
                        sql += " select dc.target_number card_no";
                    }
                    else
                    {
                        sql += " select substr(dc.target_number,1,4)||'xxxxxxxx'||substr(dc.target_number,-4) card_no";
                    }
                    sql += "       ,dc.trans_details descript";
                    sql += "       ,dc.trans_date trans_date";
                    sql += "       ,dc.posting_date proc_date";
                    sql += "       ,dc.trans_amount trans_amt";
                    sql += "       ,dc.trans_curr trans_curr";
                    sql += "       ,dc.settl_amount bill_amt";
                    sql += "       ,dc.settl_curr bill_curr";
                    sql += "       ,'' settle_amt";
                    sql += "       ,'' settle_curr";
                    sql += " from " + Businessbp.executedb.owner + "doc dc";
                    sql += " where dc.is_authorization = 'N'";
                    sql += " and dc.posting_status = 'P'";
                    sql += " and dc.amnd_state = 'A'";
                    sql += " and dc.posting_date = trunc(sysdate)";
                    sql += " and dc.Trans_Date - trunc(sysdate) >= 30";
                    sql += " and dc.posting_date >= to_date('" + dtFrom.Text.Trim ( ) + "','dd/mm/yyyy')";
                    sql += " and dc.posting_date <= to_date('" + dtTo.Text.Trim ( ) + "','dd/mm/yyyy')";
                    sql += " order by dc.target_number";
                }
                if (rbMC.Checked == true)
                {
                    if (Frm_main.admin_flag)
                    {
                        sql += " select dc.target_number card_no";
                    }
                    else
                    {
                        sql += " select substr(dc.target_number,1,4)||'xxxxxxxx'||substr(dc.target_number,-4) card_no";
                    }
                    sql += "       ,dc.trans_details descript";
                    sql += "       ,dc.trans_date trans_date";
                    sql += "       ,dc.posting_date proc_date";
                    sql += "       ,dc.trans_amount trans_amt";
                    sql += "       ,dc.trans_curr trans_curr";
                    sql += "       ,dc.settl_amount bill_amt";
                    sql += "       ,dc.settl_curr bill_curr";
                    sql += "       ,'' settle_amt";
                    sql += "       ,'' settle_curr";
                    sql += " from " + Businessbp.executedb.owner + "doc dc, " + Businessbp.executedb.owner + "m_transaction mt";
                    sql += " where dc.is_authorization = 'N'";
                    sql += " and mt.doc__oid = dc.id";
                    sql += " and dc.amnd_state = 'A'";
                    sql += " and dc.posting_status <> 'P'";
                    sql += " and dc.source_channel ='E'";
                    sql += " and dc.target_channel ='e'";
                    sql += " and dc.posting_date >= to_date('" + dtFrom.Text.Trim ( ) + "','dd/mm/yyyy')";
                    sql += " and dc.posting_date <= to_date('" + dtTo.Text.Trim ( ) + "','dd/mm/yyyy')";
                    sql += " order by dc.target_number";

                }
                if (rbOD.Checked == true)
                {
                    if (Frm_main.admin_flag)
                    {
                        sql += " select dc.target_number card_no";
                    }
                    else
                    {
                        sql += " select substr(dc.target_number,1,4)||'xxxxxxxx'||substr(dc.target_number,-4) card_no";
                    }
                    sql += "       ,dc.trans_details descript";
                    sql += "       ,dc.trans_date trans_date";
                    sql += "       ,dc.posting_date proc_date";
                    sql += "       ,dc.trans_amount trans_amt";
                    sql += "       ,dc.trans_curr trans_curr";
                    sql += "       ,dc.settl_amount bill_amt";
                    sql += "       ,dc.settl_curr bill_curr";
                    sql += "       ,'' settle_amt";
                    sql += "       ,'' settle_curr";
                    sql += " from " + Businessbp.executedb.owner + "doc dc";
                    sql += " where dc.is_authorization = 'N'";
                    sql += " and dc.posting_status <> 'P'";
                    sql += " and dc.amnd_state = 'A'";
                    sql += " and dc.source_channel in ('P','A')";
                    sql += " and dc.posting_date >= to_date('" + dtFrom.Text.Trim ( ) + "','dd/mm/yyyy')";
                    sql += " and dc.posting_date <= to_date('" + dtTo.Text.Trim ( ) + "','dd/mm/yyyy')";
                    sql += " order by dc.target_number";
                }
                if (rbBNK.Checked == true)
                {
                    if (Frm_main.admin_flag)
                    {
                        sql += " select dc.target_number card_no";
                    }
                    else
                    {
                        sql += " select substr(dc.target_number,1,4)||'xxxxxxxx'||substr(dc.target_number,-4) card_no";
                    }
                    sql += "       ,dc.trans_details descript";
                    sql += "       ,dc.trans_date trans_date";
                    sql += "       ,dc.posting_date proc_date";
                    sql += "       ,dc.trans_amount trans_amt";
                    sql += "       ,dc.trans_curr trans_curr";
                    sql += "       ,dc.settl_amount bill_amt";
                    sql += "       ,dc.settl_curr bill_curr";
                    sql += "       ,'' settle_amt";
                    sql += "       ,'' settle_curr";
                    sql += " from " + Businessbp.executedb.owner + "doc dc";
                    sql += " where dc.is_authorization = 'N'";
                    sql += " and dc.posting_status <> 'P'";
                    sql += " and dc.amnd_state = 'A'";
                    sql += " and dc.source_channel = 'b'";
                    sql += " and dc.target_channel = 'O'";
                    sql += " and dc.posting_date >= to_date('" + dtFrom.Text.Trim ( ) + "','dd/mm/yyyy')";
                    sql += " and dc.posting_date <= to_date('" + dtTo.Text.Trim ( ) + "','dd/mm/yyyy')";
                    sql += " order by dc.target_number";
                }
                DataTable dtt = Businessbp.executedb.getTable ( sql, ref err );
                if (err != "")
                {
                    MessageBox.Show ( err );
                    return;
                }
                a.dt = dtt;
                a.title = "BÁO CÁO GIAO DỊCH IPM FILE MASTERCARD";
                a.thoiGian = "Từ ngày: " + dtFrom.Text + " đến ngày " + dtTo.Text;
                a.ShowDialog ( );
            }
            else if (cbbRbType.SelectedIndex == 1)
            {
                a.type = "MC_15B";
                sql = "with tbl_tmp as " +
                      "  (" +
                      "   select to_char(tr.transaction_date,'dd/mm/yyyy') trans_date, tr.card_number, " +
                      "           decode(tr.reversal_flag,'R',-tr.billing_amount,tr.billing_amount) as bill_amt, " +
                      "           elttest.nab_getissue_fee(tr.microfilm_ref_number, nvl(tr.reversal_flag, 'Z') || tr.additional_data, 1) as bill_fee," +
                      "           ipm.ipi_additional_data4_desc as file_id," +
                      "           ba.balance as card_bal" +
                      "    from elttest.transaction_hist tr" +
                      "         join elttest.nab_phanloaisp sp on (sp.product_code = tr.product_code and sp.type = 'P')" +
                      "         join elttest.balance ba on (ba.account_number = tr.card_account_number)" +
                      "         left join elttest.ipm_incoming_header ipm on (ipm.ipi_job_id = tr.batch_number)" +
                      "    where trunc(tr.processing_date) between to_date('" + dtFrom.Text.Trim() + "', 'mm/dd/yyyy') and to_date('" + dtTo.Text.Trim() + "', 'mm/dd/yyyy')" +
                      "        and substr(tr.card_number,1,6) in ('533147','524083','516995','528645') " +
                      "        and tr.acquirer_bank_code <> '970428' and tr.transaction_code not in ('22','26','27') and tr.authorization_status in('5')" +
                      "        and tr.transaction_code not in (select transaction_code from elttest.nab_exception_trn) " +
                      "  )    " +
                      "  select t1.* , ((select sum(bill_amt) from tbl_tmp t2 where t2.card_number = t1.card_number)-t1.card_bal) as bill_process" +
                      "  from  tbl_tmp t1";
                DataTable dtt1 = Businessbp.executedb.getTable(sql, ref err);
                if (err != "")
                {
                    MessageBox.Show(err);
                    return;
                }
                a.dt = dtt1;
                a.title = "BÁO CÁO GIAO DỊCH THẺ PREPAID BÁO NỢ 2 LẦN";
                a.thoiGian = "Từ ngày: " + dtFrom.Text + " đến ngày " + dtTo.Text;
                a.ShowDialog();
                return;
            }
            else
            {
                MessageBox.Show ( "Type not exist" );
            }
            #endregion R15
            
        }
        private void btView_Click ( object sender, EventArgs e )
        {
            Frm_Report a = new Frm_Report();
            string sql = "", sqlAll1 = "",sqlAll2 ="", err = "";
            sql = "";
            string title = "";
            #region 2014-09-16 (7)
            if (rbCancelRp.Checked == true)
            {
                sql = "select decode(a.authorize, 'R', 'Cancel') Type, ";
               
            }
            else if (rbReleaseRp.Checked == true)
            {
                sql = "select decode(a.hold_release_flag, '2', 'Release') Type, ";
            }
            else
            {
                sqlAll2 = "select decode(a.hold_release_flag, '2', 'Release') Type, ";
                sqlAll1 = "select decode(a.authorize, 'R', 'Cancel') Type, ";
            }
            sql += "           a.embossed_name EMBOSSED_NAME," +
                  "    a.legal_id LEGAL_ID,"+
                  "    (select o.name"+
                  "      from " + Businessbp.executedb.owner + "appl_product o" +
                  "     where o.internal_code = a.product_code"+
                  "       and o.amnd_state = 'A'"+
                  "       and substr(o.code, 5, 1) in ('M', 'S')"+
                  "       and o.con_cat = 'C') PRODUCT_CODE," +
                  "   decode(a.basic_card_flag, '1', 'The Phu', 'The Chinh') The_chinh_the_phu,"+
                  "    a.client_code client_code,"+
                  "    a.user_create user_create,"+
                  "    to_char(a.date_create, 'dd/mm/yyyy') date_create,"+
                  "    a.user_authorize user_authorize,"+
                  "    to_char(a.date_authorize, 'dd/mm/yyyy') date_authorize,"+
                  "    (select (br.code || '-' || br.name) from ows.branch br where br.amnd_state = 'A' and trim(a.branch_code_dsa_code) = br.code)  Ghi_Chu";
            #endregion
            sql += "  from nab_cp_card a  ";
            sql += "        where 1 = 1 ";//Chỉ được hold những hồ sơ chi nhánh đã duyệt
            if (rbCancelRp.Checked == true)
            {
                title = "DANH SÁCH CÁC THẺ CANCEL";
                sql += "      and  a.authorize='R'";
                sql += "      and  trunc(a.date_modify) >= to_date('" + dtpkFrom.Text.Trim() + "','dd/mm/yyyy')";
                sql += "      and  trunc(a.date_modify) <= to_date('" + dtpkTo.Text.Trim() + "','dd/mm/yyyy')";
            }
            else if (rbReleaseRp.Checked == true)
            {
                title = "DANH SÁCH CÁC THẺ RELEASE";
                sql += " and nvl(a.authorize,'N')='Y'  and nvl(a.extract_flag,'N')='N'";
                sql += "        and nvl(a.hold_release_flag,'2')='2'";
                sql += "      and  trunc(a.date_release) >= to_date('" + dtpkFrom.Text.Trim() + "','dd/mm/yyyy')";
                sql += "      and  trunc(a.date_release) <= to_date('" + dtpkTo.Text.Trim() + "','dd/mm/yyyy')";
            }
            else
            {
                title = "DANH SÁCH CÁC THẺ RELEASE VÀ CANCEL";
                string sql1="", sql2 = "";
                sql1 += sql + "      and  a.authorize='R'";
                sql1 += "      and  trunc(a.date_modify) >= to_date('" + dtpkFrom.Text.Trim() + "','dd/mm/yyyy')";
                sql1 += "      and  trunc(a.date_modify) <= to_date('" + dtpkTo.Text.Trim() + "','dd/mm/yyyy')";

                sql2 += sql+ " and nvl(a.authorize,'N')='Y'  and nvl(a.extract_flag,'N')='N'";
                sql2 += "        and nvl(a.hold_release_flag,'2')='2'";
                sql2 += "      and  trunc(a.date_release) >= to_date('" + dtpkFrom.Text.Trim() + "','dd/mm/yyyy')";
                sql2 += "      and  trunc(a.date_release) <= to_date('" + dtpkTo.Text.Trim() + "','dd/mm/yyyy')";

                sql = sqlAll1 + sql1 + " union " + sqlAll2 + sql2;
            }
            DataTable dt = Businessbp.executedb.getTable(sql, ref err);

            if (err != "")
            {
                MessageBox.Show(err);
                return;
            }
            else
            {
                a.type = "BC_CR_APP_HOLD_RELEASE";
                a.dt = dt;
                a.title = title;
                a.thoiGian = "Từ ngày " + dtpkFrom.Text + " đến ngày " + dtpkTo.Text;
                a.ShowDialog();

            }
        }