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();
}
}