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 button2_Click(object sender, EventArgs e)
{
Frm_Report a = new Frm_Report();
a.type = "1";
a.dt = (DataTable)dataGridView1.DataSource;
a.ShowDialog();
}
private void button3_Click(object sender, EventArgs e)
{
Frm_Report a = new Frm_Report();
string sql = "", err = "";
string title = "";
sql = " select decode(a.hold_release_flag,'1','HOLD','2','RELEASE') TYPE,a.embossed_name EMBOSSED_NAME,a.legal_id LEGAL_ID,b.description as PRODUCT_CODE,a.client_code client_code";
sql += " ,a.user_create user_create,to_char(a.date_create,'dd/mm/yyyy') date_create";
sql += " ,a.user_authorize user_authorize,to_char(a.date_authorize,'dd/mm/yyyy') date_authorize";
sql += " ,'' Ghi_Chu";
sql += " from " + Businessbp.executedb.owner + "cp_card a," + Businessbp.executedb.owner + "product b";
sql += " where a.product_code=b.product_code";
sql += " and nvl(a.authorize,'N')='Y'";
if (rbHoldRp.Checked == true)
{
title = "DANH SÁCH CÁC THẺ HOLD";
sql += " and trunc(a.date_hold)>= trunc(to_date('" + dateTimePicker1.Text + "','dd/MM/yyyy'))";
sql += " and trunc(a.date_hold)<= trunc(to_date('" + dateTimePicker2.Text + "','dd/MM/yyyy'))";
sql += " and a.hold_release_flag='1'";
}
else if (rbReleaseRp.Checked == true)
{
title = "DANH SÁCH CÁC THẺ RELEASE";
sql += " and trunc(a.date_release)>= trunc(to_date('" + dateTimePicker1.Text + "','dd/MM/yyyy'))";
sql += " and trunc(a.date_release)<= trunc(to_date('" + dateTimePicker2.Text + "','dd/MM/yyyy'))";
sql += " and a.hold_release_flag='2'";
}
else
{
title = "DANH SÁCH CÁC THẺ HOLD/RELEASE";
sql += " and (trunc(a.date_hold)>= trunc(to_date('" + dateTimePicker1.Text + "','dd/MM/yyyy')) ";
sql += " or trunc(a.date_release)>= trunc(to_date('" + dateTimePicker1.Text + "','dd/MM/yyyy'))) ";
sql += " and (trunc(a.date_hold)<= trunc(to_date('" + dateTimePicker2.Text + "','dd/MM/yyyy'))";
sql += " or trunc(a.date_release)<= trunc(to_date('" + dateTimePicker2.Text + "','dd/MM/yyyy')))";
sql += " and a.hold_release_flag in ('1','2')";
}
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 " + dateTimePicker1.Text + " đến ngày " + dateTimePicker2.Text;
a.ShowDialog();
}
}
private void button1_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 a.embosing_name ,a.CARD_NUMBER,a.ACCOUNT_NUMBER,a.LEGAL_ID,b.address1||' '||b.address2||b.address3||b.address4 as ADDRESS,PHI_AMOUNT,NOI_DUNG,'' GHI_CHU";
sql += " from " + Businessbp.executedb.owner + "nab_pht_thuphi a," + Businessbp.executedb.owner + "card b, " + Businessbp.executedb.owner + "nab_phanloaisp pl ";
sql += " where a.card_number=b.card_number";
sql += " and a.loaithe = pl.product_code";
sql += " and pl.type = 'D'";
#endregion 2014-06-09 (1)
if (rbChuaThuPhi.Checked)
{
// sql += " and nvl(a.authorize_flag,'N')='N'";
sql += " and nvl(a.accounting_flag,'N')='N'";
sql += " and trunc(a.date_create)>=trunc(to_date('" + dtFrom.Text + "','dd/mm/yyyy'))";
sql += " and trunc(a.date_create)<=trunc(to_date('" + dtTo.Text + "','dd/mm/yyyy'))";
}
else if (rbDaThuPhi.Checked)
{
sql += " and nvl(a.authorize_flag,'N')='Y'";
sql += " and nvl(a.accounting_flag,'N')='Y'";
sql += " and trunc(a.date_authorize)>=trunc(to_date('" + dtFrom.Text + "','dd/mm/yyyy'))";
sql += " and trunc(a.date_authorize)<=trunc(to_date('" + dtTo.Text + "','dd/mm/yyyy'))";
}
else
{
sql += " and trunc(a.date_create)>=trunc(to_date('" + dtFrom.Text + "','dd/mm/yyyy'))";
sql += " and trunc(a.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 button1_Click(object sender, EventArgs e)
{
#region 1st presentment
if (comboBox1.SelectedIndex + 1 == 1)//1st presentment
{
Frm_MC_1st_Presentment a = new Frm_MC_1st_Presentment();
gldtFrom = dtFrom.Text.Trim();
gldtTo = dtTo.Text.Trim();
combox = "1";
a.ShowDialog();
}
#endregion 1st presentment
#region Retrieval Request
if (comboBox1.SelectedIndex + 1 == 2)//Retrieval Request
{
Frm_MC_Retrieval_Request_1st_Presentment a = new Frm_MC_Retrieval_Request_1st_Presentment();
gldtFrom = dtFrom.Text.Trim();
gldtTo = dtTo.Text.Trim();
combox = "2";
a.ShowDialog();
}
#endregion Retrieval Request
#region 1st chargeback
if (comboBox1.SelectedIndex + 1 == 3)//1st chargeback
{
Frm_MC_Chargeback_1st_Presentment a = new Frm_MC_Chargeback_1st_Presentment();
gldtFrom = dtFrom.Text.Trim();
gldtTo = dtTo.Text.Trim();
combox = "3";
a.ShowDialog();
}
#endregion 1st chargeback
#region 2nd presentment
if (comboBox1.SelectedIndex + 1 == 4)//2nd presentment
{
Frm_MC_2nd_Presentment a = new Frm_MC_2nd_Presentment();
gldtFrom = dtFrom.Text.Trim();
gldtTo = dtTo.Text.Trim();
combox = "4";
a.ShowDialog();
}
#endregion 2nd presentment
#region arbitration chargeback
if (comboBox1.SelectedIndex + 1 == 5) // Arbitration chargeback
{
Frm_MC_Arbitration_2nd_Presentment a = new Frm_MC_Arbitration_2nd_Presentment();
gldtFrom = dtFrom.Text.Trim();
gldtTo = dtTo.Text.Trim();
combox = "5";
a.ShowDialog();
}
#endregion arbitration chargeback
#region reprot fee
if (comboBox1.SelectedIndex + 1 == 6) // Report Fee
{
Frm_Report a = new Frm_Report();
string sql = "", err = "";
sql = "select h.ipi_additional_data4_desc file_ID,k.ifd_transaction_amt as Trans_amt,k.ifd_curr_code_txn_cd as Trans_cur, " +
"k.ifd_original_amt as Original_amt,k.ifd_curr_original_txn_cd as Original_cur,k.ifd_recon_amt as Recon_amt,k.ifd_curr_code_recon_cd as Recon_cur," +
"k.ifd_convertion_rate_cd as Convert_rate,k.ifd_function_code_num as Func_code,k.ifd_reason_code_num as Rea_code, " +
"k.ifd_msg_reversal_indicator_cd as Reversal,k.ifd_processing_status as Process_status " +
"from " + Businessbp.executedb.owner + "ipm_fees_detail_t k," + Businessbp.executedb.owner + "ipm_incoming_header h " +
"where 1 = 1 " +
"and k.ifd_job_id = h.ipi_job_id " +
"and trunc(k.date_create) >= trunc(to_date('" + dtFrom.Text.Trim() + "','mm/dd/yyyy')) " +
"and trunc(k.date_create) <= trunc(to_date('" + dtTo.Text.Trim() + "','mm/dd/yyyy')) " +
"order by file_ID,Recon_cur ";
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
else
{
if (dtt.Rows.Count == 0)
{
MessageBox.Show("No data found!!!");
return;
}
else
{
a.type = "MC_Report_Fee";
a.thoiGian = "Từ ngày: " + dtFrom.Text.Trim() + " đến ngày " + dtTo.Text.Trim();
a.title = "DANH SÁCH REPORT FEE";
a.dt = dtt;
combox = "6";
a.ShowDialog();
}
}
}
#endregion reprot fee
}
private void button1_Click(object sender, EventArgs e)
{
string sql = "", err = "";
if (cbReport.SelectedIndex + 1 == 1)
{
//sql += " select e.embossed_name,e.card_number,c.legal_id,c.auto_payment_account,e.client_code,c.branch_code_dsa_code Branch_code";
//sql += " ,decode(c.auto_payment_flag,'MMM','X','') as Mindue";
//sql += " ,decode(c.auto_payment_flag,'FFF','X','') as Full_Bill";
//sql += " ,decode(c.auto_payment_flag,'FFF','','MMM','',c.auto_payment_flag) as percent";
//sql += " ,'' Ghi_Chu";
//sql += " from " + Businessbp.executedb.owner + "cp_card c," + Businessbp.executedb.owner + "application d," + Businessbp.executedb.owner + "card e";
//sql += " where c.source_application_no=d.application_no";
//sql += " and d.card_number=e.cr_account_nbr";
//sql += " and c.auto_payment_flag<>'000'";
//if (cbBranch.SelectedValue.ToString() != "9999")
// sql += " and trim(c.branch_code_dsa_code)='" + cbBranch.SelectedValue.ToString().Trim() + "'";
//sql += " and trunc(c.date_create)>=trunc(to_date('" + dateTimePicker1.Text + "','dd/mm/yyyy'))";
//sql += " and trunc(c.date_create)<=trunc(to_date('" + dateTimePicker2.Text + "','dd/mm/yyyy'))";
//a Phuong
if (Frm_main.admin_flag)
{
sql += " select e.embossed_name,e.card_number,c.legal_id,c.auto_payment_account,e.client_code,c.branch_code_dsa_code Branch_code";
}
else
{
sql += " select e.embossed_name,substr(e.card_number,1,4)||'xxxxxxxx'||substr(e.card_number,1,4) card_number,c.legal_id,substr(c.auto_payment_account,1,4)||'xxxxxxxx'||substr(c.auto_payment_account,1,4) auto_payment_account,e.client_code,c.branch_code_dsa_code Branch_code";
}
sql += " ,decode(c.auto_payment_flag,'MMM','X','') as Mindue";
sql += " ,decode(c.auto_payment_flag,'FFF','X','') as Full_Bill";
sql += " ,decode(c.auto_payment_flag,'FFF','','MMM','',c.auto_payment_flag) as percent";
sql += " ,'' Ghi_Chu";
sql += " from " + Businessbp.executedb.owner + "cp_card c," + Businessbp.executedb.owner + "application d," + Businessbp.executedb.owner + "card e";
sql += " where c.source_application_no=d.application_no";
sql += " and d.card_number=e.card_number";
sql += " and e.basic_card_flag=0";
sql += " and c.auto_payment_flag<>'000'";
if (cbBranch.SelectedValue.ToString() != "9999")
sql += " and trim(c.branch_code_dsa_code)='" + cbBranch.SelectedValue.ToString().Trim() + "'";
DataTable dt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
else
{
Frm_Report a = new Frm_Report();
a.type = "BC_AUTOPAYMENT_1";
a.title2 = "Ngày "+dateTimePicker1.Text+"";// đến ngày "+dateTimePicker2.Text;
a.dt = dt;
a.ShowDialog();
}
}
if (cbReport.SelectedIndex + 1 == 2)
{
sql = ""; err = "";
// sql += " select c.embossed_name,c.card_number,legal_id,auto_payment_account,g.acy_avl_bal So_Du,e.closing_bal Closing_Bal,d.amount_will_process Amount_Will_Process";
// sql += " ,d.amount_processed Amount_process";
// sql += " ,decode(a.auto_payment_flag,'MMM',d.amount_will_process,'') Mindue";
// sql += " ,decode(a.auto_payment_flag,'FFF',d.amount_will_process,'') Full_Bill";
// sql += " ,decode(a.auto_payment_flag,'MMM','','FFF','',d.amount_will_process,'') percent";
// sql += " ,(e.closing_bal-d.amount_processed)So_Du_Sau_Khi_Trich,'' GhiChu ";
// sql += " from " + Businessbp.executedb.owner + "cp_card a," + Businessbp.executedb.owner + "application b," + Businessbp.executedb.owner + "card c," + Businessbp.executedb.owner + "nab_auto_payment d," + Businessbp.executedb.owner + "cr_term e,sttm_cust_account@FLEXCUBE g";
// sql += " where a.source_application_no=b.application_no";
// sql += " and b.card_number=c.card_number";
// sql += " and c.card_number=d.cr_account_nbr";
// sql += " and d.cr_account_nbr=e.cr_account_nbr";
// sql += " and d.account_flex_number=g.cust_Ac_no";
// sql += " and e.stat_date =(select max (f.stat_date) from " + Businessbp.executedb.owner + "cr_term f where f.cr_account_nbr=e.cr_account_nbr)";
//// sql += " and nvl(d.processed,'0')='0'";
// sql += " and d.business_date= (select max(h.business_date) from " + Businessbp.executedb.owner + "nab_auto_payment h where h.cr_account_nbr=d.cr_account_nbr)";
if (Frm_main.admin_flag)
{
sql += " select c.embossed_name,a.cr_account_nbr card_number,c.legal_id,c.auto_payment_account,g.acy_avl_bal So_Du";
}
else
{
sql += " select c.embossed_name,substr(a.cr_account_nbr,1,4)||'xxxxxxxx'||substr(a.cr_account_nbr,1,4) card_number,c.legal_id,substr(c.auto_payment_account,1,4)||'xxxxxxxx'||substr(c.auto_payment_account,1,4) auto_payment_account,g.acy_avl_bal So_Du";
}
sql += " ,a.closing_bal Closing_Bal";
sql += " ,round(decode(c.auto_payment_flag, 'FFF', a.closing_bal, 'MMM', a.min_due, to_number(c.auto_payment_flag)*a.closing_bal/100),0) Amount_Will_Process";
sql += " ,0 Amount_process";
sql += " ,decode(c.auto_payment_flag,'MMM',";
//round(decode(c.auto_payment_flag, 'FFF', a.closing_bal, 'MMM', a.min_due, to_number(c.auto_payment_flag)*a.closing_bal/100),0),'') Mindue";
sql += " decode(substr(to_char((round(decode(c.auto_payment_flag, 'FFF', a.closing_bal, 'MMM', a.min_due, to_number(c.auto_payment_flag)*a.closing_bal/100),0) - (";
sql += " select nvl(u.cur_payment,0)";
sql += " from " + Businessbp.executedb.owner + "account u";
sql += " where u.cr_account_nbr = a.cr_account_nbr";
sql += " )";
sql += " )),1,1),'-',0,";
sql += " (round(decode(c.auto_payment_flag, 'FFF', a.closing_bal, 'MMM', a.min_due, to_number(c.auto_payment_flag)*a.closing_bal/100),0) - (";
sql += " select nvl(u.cur_payment,0)";
sql += " from " + Businessbp.executedb.owner + "account u";
sql += " where u.cr_account_nbr = a.cr_account_nbr";
sql += " ))";
sql += " ),0) Mindue";
sql += " ,decode(c.auto_payment_flag,'FFF',";
//round(decode(c.auto_payment_flag, 'FFF', a.closing_bal, 'MMM', a.min_due, to_number(c.auto_payment_flag)*a.closing_bal/100),0),'') Full_Bill";
sql += " decode(substr(to_char((round(decode(c.auto_payment_flag, 'FFF', a.closing_bal, 'MMM', a.min_due, to_number(c.auto_payment_flag)*a.closing_bal/100),0) - (";
sql += " select nvl(u.cur_payment,0)";
sql += " from " + Businessbp.executedb.owner + "account u";
sql += " where u.cr_account_nbr = a.cr_account_nbr";
sql += " )";
sql += " )),1,1),'-',0,";
sql += " (round(decode(c.auto_payment_flag, 'FFF', a.closing_bal, 'MMM', a.min_due, to_number(c.auto_payment_flag)*a.closing_bal/100),0) - (";
sql += " select nvl(u.cur_payment,0)";
sql += " from " + Businessbp.executedb.owner + "account u";
sql += " where u.cr_account_nbr = a.cr_account_nbr";
sql += " ))";
sql += " ),0) Full_Bill";
sql += " ,decode(c.auto_payment_flag,'MMM','','FFF','',";
//round(decode(c.auto_payment_flag, 'FFF', a.closing_bal, 'MMM', a.min_due, to_number(c.auto_payment_flag)*a.closing_bal/100),0),'') percent";
sql += " decode(substr(to_char((round(decode(c.auto_payment_flag, 'FFF', a.closing_bal, 'MMM', a.min_due, to_number(c.auto_payment_flag)*a.closing_bal/100),0) - (";
sql += " select nvl(u.cur_payment,0)";
sql += " from " + Businessbp.executedb.owner + "account u";
sql += " where u.cr_account_nbr = a.cr_account_nbr";
sql += " )";
sql += " )),1,1),'-',0,";
sql += " (round(decode(c.auto_payment_flag, 'FFF', a.closing_bal, 'MMM', a.min_due, to_number(c.auto_payment_flag)*a.closing_bal/100),0) - (";
sql += " select nvl(u.cur_payment,0)";
sql += " from " + Businessbp.executedb.owner + "account u";
sql += " where u.cr_account_nbr = a.cr_account_nbr";
sql += " ))";
sql += " )) percent";
sql += " ,0 So_Du_Sau_Khi_Trich,'' GhiChu";
sql += " from " + Businessbp.executedb.owner + "cr_term a, " + Businessbp.executedb.owner + "application b, " + Businessbp.executedb.owner + "cp_card c,sttm_cust_account@FLEXCUBE g";
sql += " where a.cr_account_nbr = b.card_number";
sql += " and b.application_no = c.source_application_no";
sql += " and trim(c.auto_payment_account)=trim(g.cust_ac_no)";
sql += " and trunc(a.payment_date) = ( select max(g.payment_date) from " + Businessbp.executedb.owner + "cr_term g )";
sql += " and c.auto_payment_flag <> '000' ";//-- Khong dang ky dich vu
sql += " and (nvl(a.min_due,0) > 0 or nvl(a.closing_bal,0) > 0)";
if (cbBranch.SelectedValue.ToString() != "9999")
sql += " and trim(c.branch_code_dsa_code)='" + cbBranch.SelectedValue.ToString().Trim() + "'";
//sql += " and trunc(a.business_date)>=trunc(to_date('" + dateTimePicker1.Text + "','dd/mm/yyyy'))";
//sql += " and trunc(a.business_date)<=trunc(to_date('" + dateTimePicker2.Text + "','dd/mm/yyyy'))";
DataTable dt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
else
{
Frm_Report a = new Frm_Report();
a.type = "BC_AUTOPAYMENT_2";
a.title2 = "Ngày " + dateTimePicker1.Text;// +" đến ngày " + dateTimePicker2.Text;
a.dt = dt;
a.ShowDialog();
}
}
if (cbReport.SelectedIndex + 1 == 3)
{
sql = ""; err = "";
if (Frm_main.admin_flag)
{
sql += " select c.embossed_name,c.card_number,legal_id,auto_payment_account,g.acy_avl_bal So_Du,e.closing_bal Closing_Bal,d.amount_will_process Amount_Will_Process";
}
else
{
sql += " select c.embossed_name,substr(c.card_number,1,4)||'xxxxxxxx'||substr(c.card_number,1,4) card_number,legal_id,substr(auto_payment_account,1,4)||'xxxxxxxx'||substr(auto_payment_account,1,4) auto_payment_account,g.acy_avl_bal So_Du,e.closing_bal Closing_Bal,d.amount_will_process Amount_Will_Process";
}
sql += " ,d.amount_processed Amount_process";
sql += " ,decode(a.auto_payment_flag,'MMM',d.amount_will_process,'') Mindue";
sql += " ,decode(a.auto_payment_flag,'FFF',d.amount_will_process,'') Full_Bill";
sql += " ,decode(a.auto_payment_flag,'MMM','','FFF','',d.amount_will_process,'') percent";
sql += " ,(decode(substr(to_char((round(decode(a.auto_payment_flag, 'FFF', e.closing_bal, 'MMM', e.min_due, to_number(a.auto_payment_flag)*e.closing_bal/100),0) - (";
sql += " select nvl(u.cur_payment,0)";
sql += " from " + Businessbp.executedb.owner + "account u";
sql += " where u.cr_account_nbr = e.cr_account_nbr";
sql += " )";
sql += " )),1,1),'-',0,";
sql += " (round(decode(a.auto_payment_flag, 'FFF', e.closing_bal, 'MMM', e.min_due, to_number(a.auto_payment_flag)*e.closing_bal/100),0) - (";
sql += " select nvl(u.cur_payment,0)";
sql += " from " + Businessbp.executedb.owner + "account u";
sql += " where u.cr_account_nbr = e.cr_account_nbr";
sql += " ))";
sql += " )-d.amount_processed)So_Du_Sau_Khi_Trich";
//sql += " ,(e.closing_bal-d.amount_processed)So_Du_Sau_Khi_Trich,'' GhiChu ";
sql += " ,'' GhiChu ";
sql += " from " + Businessbp.executedb.owner + "cp_card a," + Businessbp.executedb.owner + "application b," + Businessbp.executedb.owner + "card c," + Businessbp.executedb.owner + "nab_auto_payment d," + Businessbp.executedb.owner + "cr_term e,sttm_cust_account@FLEXCUBE g";
sql += " where a.source_application_no=b.application_no";
sql += " and b.card_number=c.cr_account_nbr";
sql += " and c.card_number=d.cr_account_nbr";
sql += " and d.cr_account_nbr=e.cr_account_nbr";
sql += " and d.account_flex_number=g.cust_Ac_no";
sql += " and e.stat_date =(select max (f.stat_date) from " + Businessbp.executedb.owner + "cr_term f where f.cr_account_nbr=e.cr_account_nbr and trunc(f.stat_date)< trunc(to_date('" + dateTimePicker1.Text + "','dd/mm/yyyy')))";
sql += " and nvl(d.processed,'0')='1'";
// sql += " and d.business_date= (select max(h.business_date) from " + Businessbp.executedb.owner + "nab_auto_payment h where h.cr_account_nbr=d.cr_account_nbr)";
if (cbBranch.SelectedValue.ToString() != "9999")
sql += " and trim(a.branch_code_dsa_code)='" + cbBranch.SelectedValue.ToString().Trim() + "'";
sql += " and trunc(d.business_date)=trunc(to_date('" + dateTimePicker1.Text + "','dd/mm/yyyy'))";
//sql += " and trunc(d.business_date)<=trunc(to_date('" + dateTimePicker2.Text + "','dd/mm/yyyy'))";
DataTable dt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
else
{
Frm_Report a = new Frm_Report();
a.type = "BC_AUTOPAYMENT_3";
a.title2 = "Ngày " + dateTimePicker1.Text;// +" đến ngày " + dateTimePicker2.Text;
a.dt = dt;
a.ShowDialog();
}
}
}
private void button1_Click(object sender, EventArgs e)
{
Frm_Report a = new Frm_Report();
string sql = "", err = "";
#region old 2014-06-26 (1)
/*sql = " select a.card_number, a.embossed_name,a.credit_limit";
sql += " ,to_char(a.processing_date,'dd/mm/yyyy') as processing_date";
sql += " ,to_char(a.payment_date,'dd/mm/yyyy') as payment_date";
sql += " ,a.opening_bal,a.total_debit,a.bal_payment,a.closing_bal,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,a.details,a.billing_amount";
sql += " ,(a.MAILING_ADDRESS1||' '||a.MAILING_ADDRESS2||' '||a.MAILING_ADDRESS3||' '||a.MAILING_ADDRESS4) address";
sql += " ,a.PHONE_HOME, a.PHONE_MOBILE";
sql += " , a.cr_account_nbr, a.transaction_amount "; //phong add
sql += " ,(select b.currency_code_alpha from " + Businessbp.executedb.owner + "currency_table b where trim(b.currency_code) = trim(a.transaction_currency)) transaction_currency";
sql += " ,(select b.currency_code_alpha from " + Businessbp.executedb.owner + "currency_table b where trim(b.currency_code) = trim(a.billing_currency)) billing_currency";
sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log a," + Businessbp.executedb.owner + "application b," + Businessbp.executedb.owner + "cp_card c," + Businessbp.executedb.owner + "card d";
sql += " where trunc(a.stat_date)=trunc(to_date('" + dateTimePicker1.Text.Trim() + "','dd/mm/yyyy'))";
sql += " and (a.cr_account_nbr=b.card_number )";
sql += " and a.card_number = d.card_number";
sql += " and b.application_no=c.source_application_no";
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 not exists (";
sql += " select 1 from " + Businessbp.executedb.owner + "nab_credit_unpaid ff where ff.cr_account_nbr = a.cr_account_nbr";
sql += " )";
a.type = "BILILING_CREDIT";
a.title3 = "Yêu cầu thanh toán trước 10h ngày:";
}
else
{
sql += " and exists (";
sql += " select 1 from " + Businessbp.executedb.owner + "nab_credit_unpaid ff where ff.cr_account_nbr = a.cr_account_nbr";
sql += " )";
a.type = "BILILING_CREDIT_NQH";
a.title3 = "Nợ đã chuyển nợ quá hạn. Đề nghị thanh toán toàn bộ dư nợ";
}
//sql += " order by a.card_number asc,a.transaction_date asc";
sql += " order by a.cr_account_nbr asc,a.transaction_date asc";*/
#endregion old 2014-06-26 (1)
#region 2014-06-26 (1)
sql += " select r.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, ";
sql += " r.transaction_amount, ";
sql += " r.transaction_currency, ";
sql += " r.billing_currency ";
sql += " from (select a.card_number, ";
sql += " a.embossed_name, ";
sql += " a.credit_limit, ";
sql += " to_char(a.processing_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(e.transaction_sign, ";
sql += " '0', ";
sql += " to_number(''), ";
sql += " '1', ";
sql += " a.billing_amount, ";
sql += " a.billing_amount) billing_amount_du_co, ";
sql += " decode(e.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, ";
sql += " a.transaction_amount, ";
sql += " (select b.currency_code_alpha ";
sql += " from " + Businessbp.executedb.owner + "currency_table b ";
sql += " where trim(b.currency_code) = trim(a.transaction_currency)) transaction_currency, ";
sql += " (select b.currency_code_alpha ";
sql += " from " + Businessbp.executedb.owner + "currency_table b ";
sql += " where trim(b.currency_code) = trim(a.billing_currency)) billing_currency ";
sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log a, ";
sql += " " + Businessbp.executedb.owner + "trans_def_stand e, ";
sql += " " + Businessbp.executedb.owner + "application app, ";
sql += " " + Businessbp.executedb.owner + "cp_card c ";
sql += " where 1 = 1 ";
sql += " and a.transaction_code = e.transaction_code ";
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('" + dateTimePicker1.Text.Trim() + "', 'dd/mm/yyyy')) ";
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 not exists (";
sql += " select 1 from " + Businessbp.executedb.owner + "nab_credit_unpaid ff where ff.cr_account_nbr = a.cr_account_nbr";
sql += " )";
a.type = "BILILING_CREDIT";
a.title3 = "Yêu cầu thanh toán trước 10h ngày:";
}
else
{
sql += " and exists (";
sql += " select 1 from " + Businessbp.executedb.owner + "nab_credit_unpaid ff where ff.cr_account_nbr = a.cr_account_nbr";
sql += " )";
a.type = "BILILING_CREDIT_NQH";
a.title3 = "Nợ đã chuyển nợ quá hạn. Đề nghị thanh toán toàn bộ dư nợ";
}
sql += " UNION ALL ";
sql += " select a.card_number, ";
sql += " a.embossed_name, ";
sql += " a.credit_limit, ";
sql += " to_char(a.processing_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) = trim(a.transaction_currency)) transaction_currency, ";
sql += " (select b.currency_code_alpha ";
sql += " from " + Businessbp.executedb.owner + "currency_table b ";
sql += " where trim(b.currency_code) = trim(a.billing_currency)) billing_currency ";
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('" + dateTimePicker1.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 not exists (";
sql += " select 1 from " + Businessbp.executedb.owner + "nab_credit_unpaid ff where ff.cr_account_nbr = a.cr_account_nbr";
sql += " )";
a.type = "BILILING_CREDIT";
a.title3 = "Yêu cầu thanh toán trước 10h ngày:";
}
else
{
sql += " and exists (";
sql += " select 1 from " + Businessbp.executedb.owner + "nab_credit_unpaid ff where ff.cr_account_nbr = a.cr_account_nbr";
sql += " )";
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 += " order by r.card_number asc, r.transaction_date asc ";
#endregion 2014-06-26 (1)
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
a.dt = dtt;
a.title = "BẢNG THÔNG BÁO GIAO DỊCH";
a.title2 = "(Từ ngày " + dateTimePicker1.Value.AddMonths(-1).ToString("dd/MM/yyyy").Trim() + " đến ngày " + dateTimePicker1.Text.Trim() + " )";
a.ShowDialog();
}
private void button1_Click(object sender, EventArgs e)
{
Frm_Report a = new Frm_Report();
string sql = "", err = "";
#region old 2014-06-26 (1)
/*sql = " select a.card_number, a.embossed_name,a.credit_limit";
sql += " ,to_char(a.processing_date,'dd/mm/yyyy') as processing_date";
sql += " ,to_char(a.payment_date,'dd/mm/yyyy') as payment_date";
sql += " ,a.opening_bal,a.total_debit,a.bal_payment,a.closing_bal,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,a.details,a.billing_amount";
sql += " ,(a.MAILING_ADDRESS1||' '||a.MAILING_ADDRESS2||' '||a.MAILING_ADDRESS3||' '||a.MAILING_ADDRESS4) address";
sql += " ,a.PHONE_HOME, a.PHONE_MOBILE";
sql += " , a.cr_account_nbr, a.transaction_amount "; //phong add
sql += " ,(select b.currency_code_alpha from " + Businessbp.executedb.owner + "currency_table b where trim(b.currency_code) = trim(a.transaction_currency)) transaction_currency";
sql += " ,(select b.currency_code_alpha from " + Businessbp.executedb.owner + "currency_table b where trim(b.currency_code) = trim(a.billing_currency)) billing_currency";
sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log a," + Businessbp.executedb.owner + "application b," + Businessbp.executedb.owner + "cp_card c," + Businessbp.executedb.owner + "card d";
sql += " where trunc(a.stat_date)=trunc(to_date('" + dateTimePicker1.Text.Trim() + "','dd/mm/yyyy'))";
sql += " and (a.cr_account_nbr=b.card_number )";
sql += " and a.card_number = d.card_number";
sql += " and b.application_no=c.source_application_no";
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 not exists (";
sql += " select 1 from " + Businessbp.executedb.owner + "nab_credit_unpaid ff where ff.cr_account_nbr = a.cr_account_nbr";
sql += " )";
a.type = "BILILING_CREDIT";
a.title3 = "Yêu cầu thanh toán trước 10h ngày:";
}
else
{
sql += " and exists (";
sql += " select 1 from " + Businessbp.executedb.owner + "nab_credit_unpaid ff where ff.cr_account_nbr = a.cr_account_nbr";
sql += " )";
a.type = "BILILING_CREDIT_NQH";
a.title3 = "Nợ đã chuyển nợ quá hạn. Đề nghị thanh toán toàn bộ dư nợ";
}
//sql += " order by a.card_number asc,a.transaction_date asc";
sql += " order by a.cr_account_nbr asc,a.transaction_date asc";*/
#endregion old 2014-06-26 (1)
#region 2015-05-20 (9) comment
//#region 2014-06-26 (1)
//sql += " select r.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, ";
//sql += " r.transaction_amount, ";
//sql += " r.transaction_currency, ";
//sql += " r.billing_currency, ";
//sql += " to_char(nvl(r.cashback_amt_old, 0), '999,999,999,999') cashback_old, ";//2015-04-22 (8)
//sql += " to_char(nvl(r.cashback_amt, 0), '999,999,999,999') cashback"; //2015-03-19 (6)
////sql += " from (select a.card_number, "; //old 2015-04-15 (7)
//sql += " from (select a.card_number,c.client_code, "; //2015-04-15 (7)
//sql += " a.embossed_name, ";
//sql += " a.credit_limit, ";
////sql += " to_char(a.processing_date, 'dd/mm/yyyy') as processing_date, "; // old 2014-08-26 (2)
//sql += " to_char(a.stat_date, 'dd/mm/yyyy') as processing_date, "; // 2014-08-26 (2)
//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(e.transaction_sign, ";
//sql += " '0', ";
//sql += " to_number(''), ";
//sql += " '1', ";
//sql += " a.billing_amount, ";
//sql += " a.billing_amount) billing_amount_du_co, ";
//sql += " decode(e.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, ";
//sql += " a.transaction_amount, ";
//sql += " (select b.currency_code_alpha ";
//sql += " from " + Businessbp.executedb.owner + "currency_table b ";
//sql += " where trim(b.currency_code) = trim(a.transaction_currency)) transaction_currency, ";
//sql += " (select b.currency_code_alpha ";
//sql += " from " + Businessbp.executedb.owner + "currency_table b ";
//sql += " where trim(b.currency_code) = trim(a.billing_currency)) billing_currency, ";
//#region 2015-03-19 (6)
////sql += " " + Businessbp.executedb.owner + "nab_getCashBack_Amount(a.cr_account_nbr,";
////sql += " to_char(ADD_MONTHS(a.stat_date,";
////sql += " -1) + 1,";
////sql += " 'dd/mm/yyyy'),";
////sql += " to_char(a.stat_date,";
////sql += " 'dd/mm/yyyy')) cashback_amt";
//#endregion 2015-03-19 (6)
//#region 2015-04-22 (8)
//sql += " (select sum(nvl(b.transaction_amount, 0)) ";
//sql += " from " + Businessbp.executedb.owner + "nab_balance_credit b";
//sql += " where 1 = 1";
//sql += " and b.type = '9'";
//sql += " and b.user_create = 'AutoCashBack'";
//sql += " and b.batch_id = '000656'";
//sql += " and b.card_number = a.cr_account_nbr) cashback_amt_old,";
//sql += " " + Businessbp.executedb.owner + "nab_getCashBack_Amount(a.cr_account_nbr,";
//sql += " '11/03/2015','10/04/2015') cashback_amt";
//#endregion 2015-04-22 (8)
//sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log a, ";
//sql += " " + Businessbp.executedb.owner + "trans_def_stand e, ";
//sql += " " + Businessbp.executedb.owner + "application app, ";
//sql += " " + Businessbp.executedb.owner + "cp_card c ";
//sql += " where 1 = 1 ";
//sql += " and a.transaction_code = e.transaction_code ";
//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('" + dateTimePicker1.Text.Trim() + "', 'dd/mm/yyyy')) ";
//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 not exists (";
// sql += " select 1 from " + Businessbp.executedb.owner + "nab_credit_unpaid ff where ff.cr_account_nbr = a.cr_account_nbr";
// sql += " and substr(ff.nqh_code,1,1) = 'O'"; // 2014-08-26 (2)
// sql += " )";
// a.type = "BILILING_CREDIT";
// a.title3 = "Yêu cầu thanh toán trước 10h ngày:";
//}
//else
//{
// sql += " and exists (";
// sql += " select 1 from " + Businessbp.executedb.owner + "nab_credit_unpaid ff where ff.cr_account_nbr = a.cr_account_nbr";
// sql += " and substr(ff.nqh_code,1,1) = 'O'"; // 2014-08-26 (2)
// sql += " )";
// a.type = "BILILING_CREDIT_NQH";
// a.title3 = "Nợ đã chuyển nợ quá hạn. Đề nghị thanh toán toàn bộ dư nợ";
//}
//sql += " UNION ALL ";
////sql += " select a.card_number, "; //old 2015-04-15 (7)
//sql += " select a.card_number,c.client_code, "; //2015-04-15 (7)
//sql += " a.embossed_name, ";
//sql += " a.credit_limit, ";
////sql += " to_char(a.processing_date, 'dd/mm/yyyy') as processing_date, "; // old 2014-08-26 (2)
//sql += " to_char(a.stat_date, 'dd/mm/yyyy') as processing_date, "; //2014-08-26 (2)
//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) = trim(a.transaction_currency)) transaction_currency, ";
//sql += " (select b.currency_code_alpha ";
//sql += " from " + Businessbp.executedb.owner + "currency_table b ";
//sql += " where trim(b.currency_code) = trim(a.billing_currency)) billing_currency, ";
//#region 2015-03-19 (6)
////sql += " " + Businessbp.executedb.owner + "nab_getCashBack_Amount(a.cr_account_nbr,";
////sql += " to_char(ADD_MONTHS(a.stat_date,";
////sql += " -1) + 1,";
////sql += " 'dd/mm/yyyy'),";
////sql += " to_char(a.stat_date,";
////sql += " 'dd/mm/yyyy')) cashback_amt";
//#endregion 2015-03-19 (6)
//#region 2015-04-22 (8)
//sql += " (select sum(nvl(b.transaction_amount, 0)) ";
//sql += " from " + Businessbp.executedb.owner + "nab_balance_credit b";
//sql += " where 1 = 1";
//sql += " and b.type = '9'";
//sql += " and b.user_create = 'AutoCashBack'";
//sql += " and b.batch_id = '000656'";
//sql += " and b.card_number = a.cr_account_nbr) cashback_amt_old,";
//sql += " " + Businessbp.executedb.owner + "nab_getCashBack_Amount(a.cr_account_nbr,";
//sql += " '11/03/2015','10/04/2015') cashback_amt";
//#endregion 2015-04-22 (8)
//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('" + dateTimePicker1.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 not exists (";
// sql += " select 1 from " + Businessbp.executedb.owner + "nab_credit_unpaid ff where ff.cr_account_nbr = a.cr_account_nbr";
// sql += " and substr(ff.nqh_code,1,1) = 'O'"; //2014-08-26 (2)
// sql += " )";
// a.type = "BILILING_CREDIT";
// a.title3 = "Yêu cầu thanh toán trước 10h ngày:";
//}
//else
//{
// sql += " and exists (";
// sql += " select 1 from " + Businessbp.executedb.owner + "nab_credit_unpaid ff where ff.cr_account_nbr = a.cr_account_nbr";
// sql += " and substr(ff.nqh_code,1,1) = 'O'"; //2014-08-26 (2)
// sql += " )";
// 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 , ";
//#region 2014-10-03 (4)
////sql += " (select s.cr_account_nbr, s.so_luong ";
////sql += " from (select r.cr_account_nbr, ";
////sql += " count(r.microfilm_ref_number) as so_luong ";
////sql += " from (select distinct cct.cr_account_nbr, ";
////sql += " cct.microfilm_ref_number ";
////sql += " from " + Businessbp.executedb.owner + "cr_current_trans cct, ";
////sql += " " + Businessbp.executedb.owner + "nab_billing_credit_log l ";
////sql += " where cct.cr_account_nbr = l.cr_account_nbr ";
////sql += " and trunc(cct.statement_date) = ";
////sql += " trunc(to_date('" + dateTimePicker1.Text.Trim() + "', 'dd/mm/yyyy')) ";
////sql += " union all ";
////sql += " select distinct cht.cr_account_nbr, ";
////sql += " cht.microfilm_ref_number ";
////sql += " from " + Businessbp.executedb.owner + "cr_hist_trans cht, ";
////sql += " " + Businessbp.executedb.owner + "nab_billing_credit_log l ";
////sql += " where cht.cr_account_nbr = l.cr_account_nbr ";
////sql += " and trunc(cht.statement_date) = ";
////sql += " trunc(to_date('" + dateTimePicker1.Text.Trim() + "', 'dd/mm/yyyy'))) r ";
////sql += " group by r.cr_account_nbr) s ";
////sql += " where s.so_luong > 0) t ";
////sql += " where 1 = 1 ";
////sql += " and r.cr_account_nbr = t.cr_account_nbr ";
//#endregion 2014-10-03 (4)
//#region 2014-10-28 (5)
//sql += " (select distinct w.cr_account_nbr ";
//sql += " from (select s.cr_account_nbr ";
//sql += " from (select r.cr_account_nbr, ";
//sql += " count(r.microfilm_ref_number) as so_luong ";
//sql += " from (select distinct cct.cr_account_nbr, ";
//sql += " cct.microfilm_ref_number ";
//sql += " from " + Businessbp.executedb.owner + "cr_current_trans cct, ";
//sql += " " + Businessbp.executedb.owner + "nab_billing_credit_log l ";
//sql += " where cct.cr_account_nbr = l.cr_account_nbr ";
//sql += " and trunc(cct.statement_date) = ";
//sql += " trunc(to_date('" + dateTimePicker1.Text.Trim() + "', 'dd/mm/yyyy')) ";
//sql += " union all ";
//sql += " select distinct cht.cr_account_nbr, ";
//sql += " cht.microfilm_ref_number ";
//sql += " from " + Businessbp.executedb.owner + "cr_hist_trans cht, ";
//sql += " " + Businessbp.executedb.owner + "nab_billing_credit_log l ";
//sql += " where cht.cr_account_nbr = l.cr_account_nbr ";
//sql += " and trunc(cht.statement_date) = ";
//sql += " trunc(to_date('" + dateTimePicker1.Text.Trim() + "', 'dd/mm/yyyy'))) r ";
//sql += " group by r.cr_account_nbr) s ";
//sql += " where s.so_luong > 0 ";
//sql += " union all ";
//sql += " select l.cr_account_nbr ";
//sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log l ";
//sql += " where 1 = 1 ";
//sql += " and trunc(l.stat_date) = ";
//sql += " to_date('" + dateTimePicker1.Text.Trim() + "', 'dd/mm/yyyy') ";
//sql += " and l.closing_bal <> 0) w) t ";
//sql += " where 1 = 1 ";
//sql += " and r.cr_account_nbr = t.cr_account_nbr ";
//#endregion 2014-10-28 (5)
////sql += " order by r.card_number asc, r.transaction_date asc "; // old 2014-08-26 (2)
////sql += " order by r.card_number asc, to_date(r.transaction_date,'dd/mm/yyyy') asc "; // 2014-08-26 (2)
////sql += " order by r.card_number asc, to_date(r.transaction_date,'dd/mm/yyyy') desc "; // 2014-10-01 (3) //old 2015-04-15 (7)
//sql += " order by r.client_code,r.card_number asc, to_date(r.transaction_date,'dd/mm/yyyy') desc "; // 2015-04-15 (7)
//#endregion 2014-06-26 (1)
#endregion 2015-05-20 (9) comment
if (DateTime.ParseExact(dateTimePicker1.Text.Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture) < DateTime.ParseExact("24/09/2015", "dd/MM/yyyy", CultureInfo.InvariantCulture)) // 2015-09-08 (12)
{
#region 2015-05-20 (9)
sql += "select distinct A.*,";
sql += " case";
sql += " when (A.date_create + 90 <";
sql += " add_months(to_date(A.processing_date, '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_getcashback_amount(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),'999,999,999,999')";
sql += " end cashback";
if (Frm_main.admin_flag)
{
sql += " from (select r.card_number,";
}
else
{
sql += " from (select substr(r.card_number,1,4)||'xxxxxxxx'||substr(r.card_number,-4) card_number,";
}
sql += " r.client_code,"; //2015-05-27 (10)
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 += " from (select r.cr_account_nbr,";
}
else
{
sql += " from (select 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_create";
sql += " from " + Businessbp.executedb.owner + "card";
sql += " where card_number = r.cr_account_nbr) date_create";
sql += " from (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 += " decode(e.transaction_sign,";
sql += " '0',";
sql += " to_number(''),";
sql += " '1',";
sql += " a.billing_amount,";
sql += " a.billing_amount) billing_amount_du_co,";
sql += " decode(e.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,";
sql += " a.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)) 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)) billing_currency,";
sql += " c.machinhsachtd";
sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log a,";
sql += " " + Businessbp.executedb.owner + "trans_def_stand e,";
sql += " " + Businessbp.executedb.owner + "application app,";
sql += " " + Businessbp.executedb.owner + "cp_card c";
sql += " where 1 = 1";
sql += " and a.transaction_code = e.transaction_code";
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('" + dateTimePicker1.Text.Trim() + "', 'dd/mm/yyyy'))";
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";
if (Frm_main.admin_flag)
{
sql += " select a.card_number,";
}
else
{
sql += " select substr(a.card_number,1,4)||'xxxxxxxx'||substr(a.card_number,-4) 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)) 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)) 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('" + dateTimePicker1.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 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)
{
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)
{
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ợ";
}
sql += " ) r,";
sql += " (select distinct w.cr_account_nbr";
sql += " from (select s.cr_account_nbr";
sql += " from (select r.cr_account_nbr,";
sql += " count(r.microfilm_ref_number) as so_luong";
sql += " from (select distinct cct.cr_account_nbr,";
sql += " cct.microfilm_ref_number";
sql += " from " + Businessbp.executedb.owner + "cr_current_trans cct,";
sql += " " + Businessbp.executedb.owner + "nab_billing_credit_log l";
sql += " where cct.cr_account_nbr =";
sql += " l.cr_account_nbr";
sql += " and trunc(cct.statement_date) =";
sql += " trunc(to_date('" + dateTimePicker1.Text.Trim() + "',";
sql += " 'dd/mm/yyyy'))";
sql += " union all";
sql += " select distinct cht.cr_account_nbr,";
sql += " cht.microfilm_ref_number";
sql += " from " + Businessbp.executedb.owner + "cr_hist_trans cht,";
sql += " " + Businessbp.executedb.owner + "nab_billing_credit_log l";
sql += " where cht.cr_account_nbr =";
sql += " l.cr_account_nbr";
sql += " and trunc(cht.statement_date) =";
sql += " trunc(to_date('" + dateTimePicker1.Text.Trim() + "',";
sql += " 'dd/mm/yyyy'))) r";
sql += " group by r.cr_account_nbr) s";
sql += " where s.so_luong > 0";
sql += " union all";
sql += " select l.cr_account_nbr";
sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log l";
sql += " where 1 = 1";
sql += " and trunc(l.stat_date) =";
sql += " to_date('" + dateTimePicker1.Text.Trim() + "', 'dd/mm/yyyy')";
sql += " and l.closing_bal <> 0) w) t,";
sql += " card c";
sql += " where 1 = 1";
sql += " and r.cr_account_nbr = t.cr_account_nbr";
sql += " and c.cr_account_nbr = r.cr_account_nbr";
if (cbNoEmail.Checked == true)
{
//sql += " and (INSTR(c.email_id, '@', 1) = 0 or length(c.email_id) = 0) "; old 2016-01-20 (14)
sql += " and (INSTR(nvl(c.email_id,' '), '@', 1) = 0 or length(c.email_id) = 0) "; //2016-01-20 (14)
}
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 2015-09-08 (12)
else
{
sql = " select (substr(l.card_number,1,4) ||'xxxxxxxx '||substr(l.card_number,13,4)) card_number, ";
sql += " (substr(l.cr_account_nbr,1,4) ||'xxxxxxxx '||substr(l.cr_account_nbr,13,4)) 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.currency_code_alpha||')' ";
sql += " from " + Businessbp.executedb.owner + "currency_table ccy ";
sql += " where trim(ccy.currency_code) = ";
sql += " trim(l.transaction_currency)) ";
sql += " transaction_currency, ";
sql += " nvl(l.billing_amount,0) billing_amount, ";
sql += " decode((select tds.transaction_sign ";
sql += " from " + Businessbp.executedb.owner + "trans_def_stand tds ";
sql += " where tds.transaction_code = l.transaction_code), ";
sql += " '0','(DR)','1','(CR)','') transaction_sign, ";
sql += " l.details, ";
sql += " (case when not exists (select 1 ";
sql += " from " + Businessbp.executedb.owner + "nab_credit_unpaid un ";
sql += " where 1 = 1";
sql += " and un.cr_account_nbr = l.cr_account_nbr ";
sql += " and substr(un.nqh_code, 1, 1) = 'O') then ";
sql += " nvl(l.min_due, 0) ";
sql += " else ";
sql += " nvl(l.closing_bal, 0) ";
sql += " end ) 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 += " from " + Businessbp.executedb.owner + "nab_billing_credit_log l, ";
sql += " " + Businessbp.executedb.owner + "application app, ";
sql += " " + Businessbp.executedb.owner + "cp_card cc, ";
sql += " " + Businessbp.executedb.owner + "card c, ";
sql += " (select l1.cr_account_nbr,count(*) ";
sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log l1 ";
sql += " where trunc(l1.stat_date) = trunc(to_date('" + dateTimePicker1.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('" + dateTimePicker1.Text.Trim() + "', 'dd/mm/yyyy')) ";
sql += " and (l.cr_account_nbr = app.card_number) ";
sql += " and l.card_number = c.card_number ";
sql += " and app.application_no = cc.source_application_no ";
sql += " and (trim(l.arn) is not null or l.card_number=l.cr_account_nbr) "; //2015-10-21 (13)
sql += " and l.cr_account_nbr = tmp1.cr_account_nbr ";
sql += " and l.closing_bal <> 0 ";
if (rbNV.Checked == true)
sql += " and trim(cc.connect_school) = '000001'";
if (rbKH.Checked == true)
sql += " and trim(cc.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 = c.cr_account_nbr";
sql += " and substr(ff.nqh_code, 1, 1) = 'O')";
a.type = "BILILING_CREDIT_3";
}
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 = c.cr_account_nbr";
sql += " and substr(ff.nqh_code, 1, 1) = 'O')";
a.type = "BILILING_CREDIT_3";
}
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 = c.cr_account_nbr";
sql += " and substr(ff.nqh_code, 1, 1) = 'L')";
a.type = "BILILING_CREDIT_3";
}
if (cbNoEmail.Checked == true)
{
sql += " and (INSTR(c.email_id, '@', 1) = 0 or length(c.email_id) = 0) ";
}
sql += " order by c.client_code,l.cr_account_nbr,l.card_number asc, to_date(l.transaction_date, 'dd/mm/yyyy') desc,to_date(l.value_date, 'dd/mm/yyyy') desc ";
}
#endregion 2015-09-08 (12)
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
a.dt = dtt;
a.title = "BẢNG THÔNG BÁO GIAO DỊCH";
a.title2 = "(Từ ngày " + dateTimePicker1.Value.AddMonths(-1).ToString("dd/MM/yyyy").Trim() + " đến ngày " + dateTimePicker1.Text.Trim() + " )";
a.ShowDialog();
}
private void button1_Click(object sender, EventArgs e)
{
Frm_Report a = new Frm_Report();
string sql = "", err = "", ref_no = "", from = "", to = "";
ref_no = tbRetrieval_Ref_no.Text;
from = dtFrom.Text.Trim();
to = dtTo.Text.Trim();
#region Da Reversal Retrieval Request 1st Presentment
if (rbRetrieval_DaReversal.Checked == true)
{
if (ref_no !="")
{
if (Frm_main.admin_flag)
{
sql = " select r.microfilm_ref_number as Ref_no, r.card_number as Card_no, r.request_reason_code as Req_reason_code,";
}
else
{
sql = " select r.microfilm_ref_number as Ref_no, substr(r.card_number,1,4)||'xxxxxxxx'||substr(r.card_number,-4) as Card_no, r.request_reason_code as Req_reason_code,";
}
sql += "decode(r.request_code,1,'Original',2,'Copy',3,'Microfilm',4,'Fax') request_code,";
sql += " decode(r.request_indicator,0,'Response not sent',1,'Response sent',2,'Response not received',3,'Response received',4,'Canceled') Req_indicator,";
sql += " r.transaction_amount as Trans_amt, r.currency_code as Cur_code, r.fees_amount as Fee_amt, r.retrieval_date,rr.delay TongThoiGian,";
sql += " '' as pending ";
sql += " from " + Businessbp.executedb.owner + "retrieval_request r, " + Businessbp.executedb.owner + "ret_req_reason_code rr";
sql += " where r.microfilm_ref_number = '" + ref_no + "'";
sql += " and r.request_indicator = '4'";
sql += " and trunc(r.date_create) >= trunc(to_date('" + from + "','mm/dd/yyyy'))";
sql += " and trunc(r.date_create) <= trunc(to_date('" + to + "','mm/dd/yyyy'))";
sql += " and r.request_reason_code = rr.reason_code";
}
else {
if (Frm_main.admin_flag)
{
sql = " select r.microfilm_ref_number as Ref_no, r.card_number as Card_no, r.request_reason_code as Req_reason_code,";
}
else
{
sql = " select r.microfilm_ref_number as Ref_no, substr(r.card_number,1,4)||'xxxxxxxx'||substr(r.card_number,-4) as Card_no, r.request_reason_code as Req_reason_code,";
}
sql += "decode(r.request_code,1,'Original',2,'Copy',3,'Microfilm',4,'Fax') request_code,";
sql += " decode(r.request_indicator,0,'Response not sent',1,'Response sent',2,'Response not received',3,'Response received',4,'Canceled') Req_indicator,";
sql += " r.transaction_amount as Trans_amt, r.currency_code as Cur_code, r.fees_amount as Fee_amt, r.retrieval_date,rr.delay TongThoiGian,";
sql += " '' as pending ";
sql += " from " + Businessbp.executedb.owner + "retrieval_request r, " + Businessbp.executedb.owner + "ret_req_reason_code rr";
sql += " where r.request_indicator = '4'";
sql += " and trunc(r.date_create) >= trunc(to_date('" + from + "','mm/dd/yyyy'))";
sql += " and trunc(r.date_create) <= trunc(to_date('" + to + "','mm/dd/yyyy'))";
sql += " and r.request_reason_code = rr.reason_code";
}
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
else
{
if (dtt.Rows.Count == 0)
{
MessageBox.Show("No data found!!!");
return;
}
else
{
a.type = "MC_Retrival_Reversal";
a.thoiGian = "Từ ngày: " + from + " đến ngày " + to;
a.title = "DANH SÁCH GIAO DỊCH RETRIEVAL REQUEST ĐÃ REVERSAL";
a.dt = dtt;
a.ShowDialog();
}
}
}
#endregion Da Reversal Retrieval Request 1st Presentment
#region Chua Tra Loi Retrieval Request 1st Presentment
if (rbRetrieval_ChuaNhanTL.Checked == true)
{
if (ref_no != "")
{
if (Frm_main.admin_flag)
{
sql = " select r.microfilm_ref_number as Ref_no, r.card_number as Card_no, r.request_reason_code as Req_reason_code,";
}
else
{
sql = " select r.microfilm_ref_number as Ref_no, substr(r.card_number,1,4)||'xxxxxxxx'||substr(r.card_number,-4) as Card_no, r.request_reason_code as Req_reason_code,";
}
sql += " decode(r.request_code,1,'Original',2,'Copy',3,'Microfilm',4,'Fax') request_code,";
sql += " decode(r.request_indicator,0,'Response not sent',1,'Response sent',2,'Response not received',3,'Response received',4,'Canceled') Req_indicator,";
sql += " r.transaction_amount as Trans_amt, r.currency_code as Cur_code, r.fees_amount as Fee_amt, r.retrieval_date,rr.delay TongThoiGian,";
sql += " (rr.delay - (trunc(sysdate) - r.retrieval_date)) pending ";
sql += " from " + Businessbp.executedb.owner + "retrieval_request r, " + Businessbp.executedb.owner + "ret_req_reason_code rr";
sql += " where r.microfilm_ref_number = '" + ref_no + "'";
sql += " and r.request_indicator = '2'";
sql += " and trunc(r.date_create) >= trunc(to_date('" + from + "','mm/dd/yyyy'))";
sql += " and trunc(r.date_create) <= trunc(to_date('" + to + "','mm/dd/yyyy'))";
sql += " and request_reason_code = rr.reason_code";
}
else {
if (Frm_main.admin_flag)
{
sql = " select r.microfilm_ref_number as Ref_no, r.card_number as Card_no, r.request_reason_code as Req_reason_code,";
}
else
{
sql = " select r.microfilm_ref_number as Ref_no, substr(r.card_number,1,4)||'xxxxxxxx'||substr(r.card_number,-4) as Card_no, r.request_reason_code as Req_reason_code,";
}
sql += " decode(r.request_code,1,'Original',2,'Copy',3,'Microfilm',4,'Fax') request_code,";
sql += " decode(r.request_indicator,0,'Response not sent',1,'Response sent',2,'Response not received',3,'Response received',4,'Canceled') Req_indicator,";
sql += " r.transaction_amount as Trans_amt, r.currency_code as Cur_code, r.fees_amount as Fee_amt, r.retrieval_date,rr.delay TongThoiGian,";
sql += " (rr.delay - (trunc(sysdate) - r.retrieval_date)) pending ";
sql += " from " + Businessbp.executedb.owner + "retrieval_request r, " + Businessbp.executedb.owner + "ret_req_reason_code rr";
sql += " where r.request_indicator = '2'";
sql += " and trunc(r.date_create) >= trunc(to_date('" + from + "','mm/dd/yyyy'))";
sql += " and trunc(r.date_create) <= trunc(to_date('" + to + "','mm/dd/yyyy'))";
sql += " and request_reason_code = rr.reason_code";
}
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
else
{
if (dtt.Rows.Count == 0)
{
MessageBox.Show("No data found!!!");
return;
}
else
{
a.type = "MC_Retrival_Reversal";
a.thoiGian = "Từ ngày: " + from + " đến ngày " + to;
a.title = "DANH SÁCH GIAO DỊCH RETRIEVAL REQUEST CHƯA TRẢ LỜI";
a.dt = dtt;
a.ShowDialog();
}
}
}
#endregion Chua Tra Loi Retrieval Request 1st Presentment
#region Da Tra Loi Retrieval Request 1st Presentment
if (rbRetrieval_DaNhanTL.Checked == true)
{
if (ref_no != "")
{
if (Frm_main.admin_flag)
{
sql = " select r.microfilm_ref_number as Ref_no, r.card_number as Card_no, r.request_reason_code as Req_reason_code,";
}
else
{
sql = " select r.microfilm_ref_number as Ref_no, substr(r.card_number,1,4)||'xxxxxxxx'||substr(r.card_number,-4) as Card_no, r.request_reason_code as Req_reason_code,";
}
sql += " decode(r.request_code,1,'Original',2,'Copy',3,'Microfilm',4,'Fax') request_code,";
sql += " decode(r.request_indicator,0,'Response not sent',1,'Response sent',2,'Response not received',3,'Response received',4,'Canceled') Req_indicator,";
sql += " r.transaction_amount as Trans_amt, r.currency_code as Cur_code, r.fees_amount as Fee_amt, r.retrieval_date,rr.delay TongThoiGian,";
sql += " '' as pending ";
sql += " from " + Businessbp.executedb.owner + "retrieval_request r, " + Businessbp.executedb.owner + "ret_req_reason_code rr";
sql += " where r.microfilm_ref_number = '" + ref_no + "'";
sql += " and r.request_indicator = '3'";
sql += " and r.fees_amount >= 0";
// sql += " and r.retrieval_response_date is not null";
sql += " and trunc(r.date_create) >= trunc(to_date('" + from + "','mm/dd/yyyy'))";
sql += " and trunc(r.date_create) <= trunc(to_date('" + to + "','mm/dd/yyyy'))";
sql += " and r.request_reason_code = rr.reason_code";
}
else
{
if (Frm_main.admin_flag)
{
sql = " select r.microfilm_ref_number as Ref_no, r.card_number as Card_no, r.request_reason_code as Req_reason_code,";
}
else
{
sql = " select r.microfilm_ref_number as Ref_no, substr(r.card_number,1,4)||'xxxxxxxx'||substr(r.card_number,-4) as Card_no, r.request_reason_code as Req_reason_code,";
}
sql += " decode(r.request_code,1,'Original',2,'Copy',3,'Microfilm',4,'Fax') request_code,";
sql += " decode(r.request_indicator,0,'Response not sent',1,'Response sent',2,'Response not received',3,'Response received',4,'Canceled') Req_indicator,";
sql += " r.transaction_amount as Trans_amt, r.currency_code as Cur_code, r.fees_amount as Fee_amt, r.retrieval_date,rr.delay TongThoiGian,";
sql += " '' as pending ";
sql += " from " + Businessbp.executedb.owner + "retrieval_request r, " + Businessbp.executedb.owner + "ret_req_reason_code rr";
sql += " where r.request_indicator = '3'";
sql += " and r.fees_amount >= 0";
// sql += " and r.retrieval_response_date is not null";
sql += " and trunc(r.date_create) >= trunc(to_date('" + from + "','mm/dd/yyyy'))";
sql += " and trunc(r.date_create) <= trunc(to_date('" + to + "','mm/dd/yyyy'))";
sql += " and r.request_reason_code = rr.reason_code";
}
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
else
{
if (dtt.Rows.Count == 0)
{
MessageBox.Show("No data found!!!");
return;
}
else
{
a.type = "MC_Retrival_Reversal";
a.thoiGian = "Từ ngày: " + from + " đến ngày " + to;
a.title = "DANH SÁCH GIAO DỊCH RETRIEVAL REQUEST ĐÃ TRẢ LỜI";
a.dt = dtt;
a.ShowDialog();
}
}
}
#endregion Da Tra Loi Retrieval Retrieval Request 1st Presentment
}
private void button1_Click(object sender, EventArgs e)
{
string sql = "", err = "";
if (rbISS.Checked)
{
string title = "BÁO CÁO TỔNG HỢP PHÍ ";
string title2 = "";
string all_orther_standard = "1.25";
string all_orther_gold = "1.45";
#region Card Standard in Market 1 Market 2 petroleum
sql += " select 'Card Standard' as TYPE ,upper(e.market_code) as Market,count(*) as SL_GD, nvl(sum (a.transaction_amount),0) as ST_GD";
sql += " , nvl(sum((nvl(a.transaction_amount,0)* nvl(e.fee_per,0))+ e.FIX_FEE),0)/100 as PHI,'' as GHI_CHU";
sql += " from " + Businessbp.executedb.owner + "autho_activity a," + Businessbp.executedb.owner + "card b," + Businessbp.executedb.owner + "NAB_MCC_MARKET e ";
sql += " where 1=1";
sql += " and trim(a.card_acceptor_activity)=trim(e.MCC)";
sql += " and trim(a.card_number)=trim(b.card_number)";
sql += " and a.code_action='000'";
sql += " and trim(b.product_code) in (select trim(d.product_code) from " + Businessbp.executedb.owner + "nab_phanloaisp d where d.type in ('D','P','C'))";
sql += " and b.product_code not in ('004')";//-- Loai Card GOLD";
if (rbATM.Checked)//ATM
sql += " and trim(nvl(a.card_acceptor_activity,'6011'))='6011'";
else if (rbPOS.Checked)//POS
sql += " and trim(nvl(a.card_acceptor_activity,'6011'))<>'6011'";
sql += " and a.message_type='1200'";
if (rbISS.Checked)
sql += " and trim(a.issuing_bank)='970428'";
else
sql += " and trim(a.issuing_bank)<>'970428'";
sql += " and trim(a.network_code)='02'";
// sql += " and nvl(a.matching_code,'0')='2'";
sql += " and a.transaction_amount>0";
sql += " and trunc(a.processing_date)>=trunc(to_date('" + dtFrom.Text + "','dd/mm/yyyy'))";
sql += " and trunc(a.processing_date)<=trunc(to_date('" + dtTo.Text + "','dd/mm/yyyy'))";
sql += " and not exists ";
sql += " ( select 1 from " + Businessbp.executedb.owner + "autho_activity k ";
sql += " where k.card_number = a.card_number ";
sql += " and trim(k.reference_number) = trim(a.reference_number )";
sql += " and k.transaction_amount = a.transaction_amount ";
sql += " and trim(k.card_acc_name_address) = trim(a.card_acc_name_address) ";
sql += " and substr(k.processing_code,1,2) = '02' ";
sql += " and k.code_action = '000' ";
sql += " and a.function_code = '200' ) ";
sql += " group by e.market_code";
//END Card Standard in Market 1 Market 2 petroleum
#endregion Card Standard in Market 1 Market 2 petroleum
sql += " union all ";
#region Card Standard in Standard Orther
//Card Standard in Standard Orther
sql += " select 'Card Standard' as TYPE ,upper('Standard Orther') as Market,count(*) as SL_GD, nvl(sum (a.transaction_amount),0) as ST_GD";
sql += " , nvl(sum((nvl(a.transaction_amount,0)* " + all_orther_standard + ")),0)/100 as PHI,'' as GHI_CHU";
sql += " from " + Businessbp.executedb.owner + "autho_activity a," + Businessbp.executedb.owner + "card b";//," + Businessbp.executedb.owner + "NAB_MCC_MARKET e ";
sql += " where 1=1";
// sql += " and trim(a.card_acceptor_activity)=trim(e.MCC)";
sql += " and trim(a.card_acceptor_activity) not in (select trim(MCC) from " + Businessbp.executedb.owner + "NAB_MCC_MARKET e where upper(trim(e.MARKET_CODE))=upper('market1'))";
sql += " and trim(a.card_number)=trim(b.card_number)";
sql += " and a.code_action='000'";
sql += " and trim(b.product_code) in (select trim(d.product_code) from " + Businessbp.executedb.owner + "nab_phanloaisp d where d.type in ('D','P','C'))";
sql += " and b.product_code not in ('004')";//-- Loai Card GOLD";
if (rbATM.Checked)//ATM
sql += " and trim(nvl(a.card_acceptor_activity,'6011'))='6011'";
else if (rbPOS.Checked)//POS
sql += " and trim(nvl(a.card_acceptor_activity,'6011'))<>'6011'";
sql += " and a.message_type='1200'";
if (rbISS.Checked)
sql += " and trim(a.issuing_bank)='970428'";
else
sql += " and trim(a.issuing_bank)<>'970428'";
sql += " and trim(a.network_code)='02'";
// sql += " and nvl(a.matching_code,'0')='2'";
sql += " and a.transaction_amount>0";
sql += " and trunc(a.processing_date)>=trunc(to_date('" + dtFrom.Text + "','dd/mm/yyyy'))";
sql += " and trunc(a.processing_date)<=trunc(to_date('" + dtTo.Text + "','dd/mm/yyyy'))";
sql += " and not exists ";
sql += " ( select 1 from " + Businessbp.executedb.owner + "autho_activity k ";
sql += " where k.card_number = a.card_number ";
sql += " and trim(k.reference_number) = trim(a.reference_number )";
sql += " and k.transaction_amount = a.transaction_amount ";
sql += " and trim(k.card_acc_name_address) = trim(a.card_acc_name_address) ";
sql += " and substr(k.processing_code,1,2) = '02' ";
sql += " and k.code_action = '000' ";
sql += " and a.function_code = '200' ) ";
//sql += " group by e.market_code";
//END Card Standard in Standard Orther
#endregion Card Standard in Standard Orther
sql += " union all ";
#region Card GOLD in Market 1 Market 2 petroleum
sql += " select 'Card GOLD' as TYPE ,upper(e.market_code) as Market,count(*) as SL_GD, nvl(sum (a.transaction_amount),0) as ST_GD";
sql += " , nvl(sum((nvl(a.transaction_amount,0)* nvl(e.fee_per,0))+ e.FIX_FEE),0)/100 as PHI,'' as GHI_CHU";
sql += " from " + Businessbp.executedb.owner + "autho_activity a," + Businessbp.executedb.owner + "card b," + Businessbp.executedb.owner + "NAB_MCC_MARKET e ";
sql += " where 1=1";
sql += " and trim(a.card_acceptor_activity)=trim(e.MCC)";
sql += " and trim(a.card_number)=trim(b.card_number)";
sql += " and a.code_action='000'";
sql += " and trim(b.product_code) in (select trim(d.product_code) from " + Businessbp.executedb.owner + "nab_phanloaisp d where d.type in ('D','P','C'))";
sql += " and b.product_code in ('004')";//-- Card GOLD";
if (rbATM.Checked)//ATM
sql += " and trim(nvl(a.card_acceptor_activity,'6011'))='6011'";
else if (rbPOS.Checked)//POS
sql += " and trim(nvl(a.card_acceptor_activity,'6011'))<>'6011'";
sql += " and a.message_type='1200'";
if (rbISS.Checked)
sql += " and trim(a.issuing_bank)='970428'";
else
sql += " and trim(a.issuing_bank)<>'970428'";
sql += " and trim(a.network_code)='02'";
// sql += " and nvl(a.matching_code,'0')='2'";
sql += " and a.transaction_amount>0";
sql += " and trunc(a.processing_date)>=trunc(to_date('" + dtFrom.Text + "','dd/mm/yyyy'))";
sql += " and trunc(a.processing_date)<=trunc(to_date('" + dtTo.Text + "','dd/mm/yyyy'))";
sql += " and not exists ";
sql += " ( select 1 from " + Businessbp.executedb.owner + "autho_activity k ";
sql += " where k.card_number = a.card_number ";
sql += " and trim(k.reference_number) = trim(a.reference_number )";
sql += " and k.transaction_amount = a.transaction_amount ";
sql += " and trim(k.card_acc_name_address) = trim(a.card_acc_name_address) ";
sql += " and substr(k.processing_code,1,2) = '02' ";
sql += " and k.code_action = '000' ";
sql += " and a.function_code = '200' ) ";
sql += " group by e.market_code";
//END Card Standard in Market 1 Market 2 petroleum
#endregion Card GOLD in Market 1 Market 2 petroleum
sql += " union all ";
#region Card GOLD in GOLD ORTHER
//Card Standard in Standard Orther
sql += " select 'Card GOLD' as TYPE ,upper('all other') as Market,count(*) as SL_GD, nvl(sum (a.transaction_amount),0) as ST_GD";
sql += " , nvl(sum((nvl(a.transaction_amount,0)* " + all_orther_gold + ")),0)/100 as PHI,'' as GHI_CHU";
sql += " from " + Businessbp.executedb.owner + "autho_activity a," + Businessbp.executedb.owner + "card b";//," + Businessbp.executedb.owner + "NAB_MCC_MARKET e ";
sql += " where 1=1";
// sql += " and trim(a.card_acceptor_activity)=trim(e.MCC)";
sql += " and trim(a.card_acceptor_activity) not in (select trim(MCC) from " + Businessbp.executedb.owner + "NAB_MCC_MARKET e where upper(trim(e.MARKET_CODE))=upper('market1'))";
sql += " and trim(a.card_number)=trim(b.card_number)";
sql += " and a.code_action='000'";
sql += " and trim(b.product_code) in (select trim(d.product_code) from " + Businessbp.executedb.owner + "nab_phanloaisp d where d.type in ('D','P','C'))";
sql += " and b.product_code in ('004')";//-- Card GOLD";
if (rbATM.Checked)//ATM
sql += " and trim(nvl(a.card_acceptor_activity,'6011'))='6011'";
else if (rbPOS.Checked)//POS
sql += " and trim(nvl(a.card_acceptor_activity,'6011'))<>'6011'";
sql += " and a.message_type='1200'";
if (rbISS.Checked)
sql += " and trim(a.issuing_bank)='970428'";
else
sql += " and trim(a.issuing_bank)<>'970428'";
sql += " and trim(a.network_code)='02'";
// sql += " and nvl(a.matching_code,'0')='2'";
sql += " and a.transaction_amount>0";
sql += " and trunc(a.processing_date)>=trunc(to_date('" + dtFrom.Text + "','dd/mm/yyyy'))";
sql += " and trunc(a.processing_date)<=trunc(to_date('" + dtTo.Text + "','dd/mm/yyyy'))";
sql += " and not exists ";
sql += " ( select 1 from " + Businessbp.executedb.owner + "autho_activity k ";
sql += " where k.card_number = a.card_number ";
sql += " and trim(k.reference_number) = trim(a.reference_number )";
sql += " and k.transaction_amount = a.transaction_amount ";
sql += " and trim(k.card_acc_name_address) = trim(a.card_acc_name_address) ";
sql += " and substr(k.processing_code,1,2) = '02' ";
sql += " and k.code_action = '000' ";
sql += " and a.function_code = '200' ) ";
// sql += " group by e.market_code";
//END Card Standard in Standard Orther
#endregion Card GOLD in GOLD ORTHER
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
{
Frm_Report a = new Frm_Report();
a.type = "MC_BC_TH_FEE";
if (rbISS.Checked)
title2 = " Issuer";
else
title2 = " Acquirer";
if (rbATM.Checked)
title2 += " - ATM";
else
if (rbPOS.Checked)
title2 += " - POS";
else
title2 += " - ATM & POS";
a.title = title;
a.title2 = title2;
a.dt = dtt;
a.thoiGian = "Từ ngày: " + dtFrom.Text + " đến ngày " + dtTo.Text;
a.ShowDialog();
}
}
else
{
MessageBox.Show("Lam sau nhe");
return;
}
}
private void button2_Click(object sender, EventArgs e)
{
Frm_Report a = new Frm_Report();
a.type = "POS_2";
string sql = "", err = "";
sql = "select (select b.full_name from "+Businessbp.executedb.owner+"nab_qlthe_nhanvien b where b.manv=a.nhan_vien_kd) as nhan_vien_kd," +
" (select b.name from "+Businessbp.executedb.owner+"app_users b where b.userid=a.user_create) as Nhan_vien_Nhan_HS," +
" to_char(a.ngay_nhan_hs,'dd/mm/yyyy') as Ngay_Nhan_HS,a.ref_no,a.merchant_name," +
" decode(a.giay_mo_tk,'1','X','') as Giay_mo_tk," +
" decode(a.giay_phep_kd,'1','X','') as giay_phep_kd," +
" decode(a.cmnd,'1','X','') as cmnd," +
" decode(a.chung_nhan_mau_dau,'1','X','') as chung_nhan_mau_dau," +
" decode(a.chung_nhan_thue,'1','X','') as chung_nhan_thue," +
" decode(a.bb_hop_hdtv,'1','X','') as bb_hop_hdtv," +
" decode(a.qd_bo_nhiem,'1','X','') as qd_bo_nhiem," +
" decode(a.giay_uy_quyen,'1','X','') as giay_uy_quyen," +
" a.so_luong" +
" from "+Businessbp.executedb.owner+"nab_pos_issuance_branch a" +
" where 1=1" +
" and a.ref_no=" + Ref_no +
" and a.branch="+tbBranch.Text.Trim();
dt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
else
{
a.title = "BIÊN BẢN GIAO NHẬN HỒ SƠ ĐẠI LÝ";
a.thoiGian = "Tp.HCM, Ngày " + DateTime.Now.Day + " tháng " + DateTime.Now.Month + " năm " + DateTime.Now.Year;
a.dt = dt;
a.ShowDialog();
}
}
// --- Phong 20/12/2012
private void btn_ViewReport_Click(object sender, EventArgs e)
{
Frm_Report a = new Frm_Report();
a.type = "Unbill_transaction_credit";
string name = TxtName.Text.Trim();
string card_nbr = Txt_cardnumber.Text.Trim();
string acc_nbr = Txt_accountnumber.Text.Trim();
string status = TxtStatus.Text.Trim();
#region declare datatable
//DataTable dt = new DataTable();
//DataColumn txn_type = new DataColumn();
//DataColumn rev = new DataColumn();
//DataColumn card_no = new DataColumn();
//DataColumn card_type = new DataColumn();
//DataColumn txn_date = new DataColumn();
//DataColumn proc_date = new DataColumn();
//DataColumn merchant_name = new DataColumn();
//DataColumn billing_amt = new DataColumn();
//DataColumn mcg = new DataColumn();
//DataColumn pts = new DataColumn();
//DataColumn[] col = { txn_type, rev, card_no, card_type, txn_date, proc_date, merchant_name, billing_amt, mcg, pts };
//dt.Columns.AddRange(col);
#endregion declare datatable
string SQL = "", ERR = "", TempUnBill = "", primaycard="";
SQL = " select r.basic_card_flag from " + Businessbp.executedb.owner + "card r";
SQL += " where r.card_number='" + card_nbr + "'";
DataTable Primanytable = Businessbp.executedb.getTable(SQL, ref ERR);
if ((ERR == "") && (Primanytable.Rows.Count > 0))
{
if (Primanytable.Rows[0]["basic_card_flag"].ToString() == "0")
primaycard = card_nbr;
else primaycard = acc_nbr;
}
if (this.AllS.Checked)
{
TempUnBill = " where t.cr_account_nbr='" + acc_nbr + "'";
}
else if (this.Addon.Checked)
{
TempUnBill = " where t.cr_account_nbr='" + acc_nbr + "' and t.card_number <> '" + acc_nbr + "' and t.card_number <> '" + primaycard + "'";
}
else
{
TempUnBill = " where (t.card_number='" + acc_nbr + "' or t.card_number='" + primaycard + "')";
}
SQL = " select ( select tt.transaction_wording from " + Businessbp.executedb.owner + "cr_partrcr tt where tt.transaction_code=t.transaction_code and t.reversal_flag=tt.reversal_code) as txn_type, ";
SQL += " case t.reversal_flag when '1' then 'R' else ' 'end as rev, ";
SQL += " t.card_number as card_no, ";
SQL += " case (select f1.basic_card_flag from " + Businessbp.executedb.owner + "card f1 where f1.card_number=t.card_number) when '0' then 'PRIMARY CARD' else 'ADDON CARD ' end as card_type, ";
SQL += " to_char(t.transaction_date,'dd/MM/yyyy') as txn_date, to_char(t.processing_date,'dd/MM/yyyy') as proc_date, ";
SQL += " case when t.transaction_code in('01','03','59','21','70','71','09') then upper(t.acronym) ";
SQL += " when t.transaction_code in('04') then case when t.acronym is not null then upper(t.acronym) ";
SQL += " else upper((select distinct trim(a.card_acc_name_address) from " + Businessbp.executedb.owner + "autho_activity a ";
SQL += " where a.card_number=t.card_number ";
SQL += " and a.authorization_code=t.authorization_number ";
SQL += " and a.transaction_amount=t.transaction_amount)) end ";
SQL += " else upper((select r.transaction_wording from " + Businessbp.executedb.owner + "trans_def_stand_bill r where r.transaction_code=t.transaction_code )) end as merchant_name,";
SQL += " to_char(t.billing_amount,'999,999,999,999,999.00') as billing_amt, ";
SQL += " case t.charging_category when '8' then 'CR' else case t.reversal_flag when '1' then 'CR' else ' ' end end as mcg,";
SQL += " '' as pts ";
SQL += " from " + Businessbp.executedb.owner + "cr_current_trans t ";
SQL += TempUnBill;
SQL += " and t.cutof_flag='0'";
SQL += " order by t.processing_date DESC";
DataTable dt = Businessbp.executedb.getTable(SQL, ref ERR);
//DataTable dt = (DataTable)UnbilledTransaction.DataSource;
//billedTransaction.DataSource = dt;
a.name = name;
a.card_nbr = card_nbr;
a.acc_nbr = acc_nbr;
a.status = status;
a.bill_total_debit_amt = bill_total_debit_amt;
a.unbill_total_debit_amt = unbill_total_debit_amt;
a.curr_auth = curr_auth;
a.dt = dt;
a.title = "GIẤY XÁC NHẬN TẠM THỜI GIAO DỊCH VÀ TỔNG DƯ NỢ THẺ TÍN DỤNG (DÀNH CHO CHỦ THẺ THANH LÝ THẺ TÍN DỤNG)";
a.thoiGian = "Ngày " + DateTime.Now.Day.ToString() + " ,tháng " + DateTime.Now.Month.ToString() + ", năm " + DateTime.Now.Year.ToString();
a.thoigian2 = "( ngày " + DateTime.Now.Day.ToString() + " ,tháng " + DateTime.Now.Month.ToString() + ", năm " + DateTime.Now.Year.ToString()+" )";
a.ShowDialog();
}
private void button1_Click(object sender, EventArgs e)
{
string sql = "", err = "";
string sqlPrepaid = "", sqlCredit = "", dk = "";
sqlPrepaid = " select 'Prepaid' as type, substr(a.card_number,1,4)||'xxxxxxxx'||substr(a.card_number,-4) card_number,a.embossed_name as card_holder,to_char(a.date_create,'dd/mm/yyyy') as enter_date,to_char(a.date_modify,'dd/mm/yyyy') as varify_date," +
" a.balance as transaction_amount,'' Decription,a.user_create as user_enter,a.user_modify as user_varify"+
" from " + Businessbp.executedb.owner + "nab_balance_prepaid a" +
" where 1=1";
sqlCredit = " select 'Credit' as type,substr(a.card_number,1,4)||'xxxxxxxx'||substr(a.card_number,-4) card_number,a.embossed_name as card_holder,to_char(a.date_create,'dd/mm/yyyy') as enter_date,to_char(a.date_modify,'dd/mm/yyyy') as varify_date," +
" a.transaction_amount as transaction_amount,a.decription as Decription,a.user_create as user_enter,a.user_modify as user_varify"+
" from " + Businessbp.executedb.owner + "nab_balance_credit a" +
" where 1=1";
if (rbAuthorize.Checked)
{
dk = "and nvl(a.authorize,'N')='Y'";
dk += " and trunc(a.date_create)>=to_date('"+dtFrom.Text+"','dd/mm/yyyy')";
dk += " and trunc(a.date_create)<=to_date('"+dtTo.Text+"','dd/mm/yyyy')";
}
else
{
if (rbUnAuthorize.Checked)
{
dk = "and nvl(a.authorize,'N')='N'";
dk += " and trunc(a.date_create)>=to_date('" + dtFrom.Text + "','dd/mm/yyyy')";
dk += " and trunc(a.date_create)<=to_date('" + dtTo.Text + "','dd/mm/yyyy')";
}
else
{
if (rbExtract.Checked)
{
dk = " and nvl(a.extract_flag,'N')='Y'";
dk += " and trunc(a.date_extract)>=to_date('" + dtFrom.Text + "','dd/mm/yyyy')";
dk += " and trunc(a.date_extract)<=to_date('" + dtTo.Text + "','dd/mm/yyyy')";
}
}
}
if (rbAll.Checked)
{
sql = sqlPrepaid + dk+
" union all "+
sqlCredit + dk;
}
else
{
if (rbPrepaid.Checked)
{
sql = sqlPrepaid + dk;
}
if (rbCredit.Checked)
{
sql = sqlCredit + dk;
}
}
DataTable dt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
else
{
Frm_Report a = new Frm_Report();
a.type = "bal";
if (rbPrepaid.Checked)
a.title = "BÁO CÁO DANH SÁCH KHÁCH HÀNG ĐÃ NẠP TIỀN THẺ PREPAID";
if (rbCredit.Checked)
a.title = "BÁO CÁO DANH SÁCH KHÁCH HÀNG ĐÃ NẠP TIỀN THẺ CREDIT";
if (rbAll.Checked)
a.title = "BÁO CÁO DANH SÁCH KHÁCH HÀNG ĐÃ NẠP TIỀN";
if (rbAuthorize.Checked)
a.title += " - ĐÃ DUYỆT";
else if (rbUnAuthorize.Checked)
a.title += " - CHƯA DUYỆT";
// a.title = "BÁO CÁO DANH SÁCH KHÁCH HÀNG ĐÃ NẠP TIỀN";
a.dt = dt;
a.ShowDialog();
}
}
private void button3_Click(object sender, EventArgs e)
{
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";
if (Frm_main.admin_flag)
{
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 (";
}
else
{
sql = " select hh.LoaiTB,hh.LoaiGD,substr(hh.Sothe,1,4)||'xxxxxxxx'||substr(hh.Sothe,-4) 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_issuer_dispute_pri1 a"+
" where 1=1";
}
else if (rbTypeIssuer2.Checked)
{
a.title = "ISSUER: NOT IN BANKNET - IN ELECTRA";
if (Frm_main.admin_flag)
{
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 (";
}
else
{
sql = " select hh.LoaiTB,hh.LoaiGD,substr(hh.Sothe,1,4)||'xxxxxxxx'||substr(hh.Sothe,-4) 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_issuer_dispute_pri 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
if (Frm_main.admin_flag)
{
sql = "select hh.LoaiTB,hh.LoaiGD,hh.Sothe,hh.ngaygd, hh.trace, hh.sotien,";
}
else
{
sql = "select hh.LoaiTB,hh.LoaiGD,substr(hh.Sothe,1,4)||'xxxxxxxx'||substr(hh.Sothe,-4) 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 "+Businessbp.executedb.owner+"banknet_acq_dispute_pri a, "+Businessbp.executedb.owner+"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";
if (Frm_main.admin_flag)
{
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 (";
}
else
{
sql = " select hh.LoaiTB,hh.LoaiGD,substr(hh.Sothe,1,4)||'xxxxxxxx'||substr(hh.Sothe,-4) 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 "+Businessbp.executedb.owner+"banknet_dispute_pri a" +
" where 1=1";
}
//---------------
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')";
else //chi lam don't receive...
// 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 button3_Click(object sender, EventArgs e)
{
//So luong nhieu
Frm_Report a = new Frm_Report();
a.type = "POS_1";
string sql = "", err = "";
sql = "select (select b.full_name from "+Businessbp.executedb.owner+"nab_qlthe_nhanvien b where b.manv=a.nhan_vien_kd) as nhan_vien_kd," +
" a.ref_no,a.merchant_name," +
" (select g.mcc_code||'-'||g.description from "+Businessbp.executedb.owner+"mcc g where g.mcc_code=a.mcc_code ) as MCC," +
" a.diachi_so||' '||a.diachi_duong||' , '||a.diachi_phuong||', '||a.diachi_quan||', '||a.diachi_tp as DIA_CHI," +
" a.chu_dai_ly,a.chuc_vu,a.dienthoai_ll," +
" to_char(a.ngay_nhan_hs,'dd/mm/yyyy') as Ngay_Nhan_HS," +
" (select c.equipment_label from "+Businessbp.executedb.owner+"equipment c where c.equipment_code=a.loai_may) as Loai_May," +
" a.so_hieu_may,a.so_luong,a.thiet_bi_di_kem,a.ghi_chu_thiet_bi," +
" a.nama_card,a.visa_card,a.bnk_card,a.master_card,a.jcb,a.dinner_club_card" +
" from "+Businessbp.executedb.owner+"nab_pos_issuance_branch a" +
" where 1=1" +
" and a.ref_no=" + Ref_no +
" and a.branch=" + tbBranch.Text.Trim();
dt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
else
{
a.title = "BIÊN BẢN GIAO NHẬN THIẾT BỊ THANH TOÁN THẺ";
a.thoiGian = "Tp.HCM, Ngày " + DateTime.Now.Day + " tháng " + DateTime.Now.Month + " năm " + DateTime.Now.Year;
a.dt = dt;
a.ShowDialog();
}
}
private void button6_Click(object sender, EventArgs e)
{
string sql = "", err = "";
sql = "select distinct decode(a.assign_to,null,'-',a.assign_to) as assign_to, to_char(a.ref_no) as ref, a.merchant_name, a.loai_hinh, a.diachi_so||' '||a.diachi_duong||' '||a.diachi_phuong||' '||a.diachi_quan||' '||a.diachi_tp as DIA_CHI,to_char(a.ngay_hen,'dd/mm/yyyy') as ngay_hen, " +
" decode(a.ket_qua_hen,null,'CHUA GAP',a.ket_qua_hen) as ket_qua_hen, decode(a.cuoc_gap_kt,null,'---',to_char(a.cuoc_gap_kt,'dd/mm/yyyy')) as cuoc_gap_kt, decode(a.ngay_ky_hd,null,'---',to_char(a.ngay_ky_hd,'dd/mm/yyyy')) as ngay_ky_hd" +
" from "+Businessbp.executedb.owner+"nab_pos_activity a" +
" where a.assign_to is not null" +
" and trunc(a.date_create) >= trunc(to_date('" + dateTimePicker1.Text + "','dd/mm/yyyy'))" +
" and trunc(a.date_create) <= trunc(to_date('" + dateTimePicker2.Text + "','dd/mm/yyyy'))";
//" order by b.cuoc_gap_kt desc";
sql += " union"+
" select distinct decode(a.assign_to,null,'-',a.assign_to) as assign_to, to_char(a.ref_no) as ref, a.merchant_name, a.loai_hinh, a.diachi_so||' '||a.diachi_duong||' '||a.diachi_phuong||' '||a.diachi_quan||' '||a.diachi_tp as DIA_CHI,to_char(a.ngay_hen,'dd/mm/yyyy') as ngay_hen, " +
" decode(a.ket_qua_hen,null,'CHUA GAP',a.ket_qua_hen) as ket_qua_hen, decode(a.cuoc_gap_kt,null,'---',to_char(a.cuoc_gap_kt,'dd/mm/yyyy')) as cuoc_gap_kt, decode(a.ngay_ky_hd,null,'---',to_char(a.ngay_ky_hd,'dd/mm/yyyy')) as ngay_ky_hd" +
" from "+Businessbp.executedb.owner+"nab_pos_activity_df a" +
" where a.assign_to is not null" +
" and trunc(a.date_create) >= trunc(to_date('" + dateTimePicker1.Text + "','dd/mm/yyyy'))" +
" and trunc(a.date_create) <= trunc(to_date('" + dateTimePicker2.Text + "','dd/mm/yyyy'))";
//" order by b.cuoc_gap_kt desc";
DataTable dt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
MessageBox.Show(err);
else
{
Frm_Report a = new Frm_Report();
a.type = "2";
a.dt = dt;
a.ShowDialog();
}
}
private void button1_Click(object sender, EventArgs e)
{
#region R7.4
if (comboBox1.SelectedIndex + 1 == 7) // Report 7.4 //Lãi thẻ Prepaid
{
Frm_Report a = new Frm_Report();
a.type = "MC_7.4";
string sql = "", dk = "", err = "";
if (cbNoidia.Checked == true)
{
dk = "and substr(m.card_number,0,1) <> '5'";
}
else if (cbQuocte.Checked == true)
{
dk = "and substr(m.card_number,0,1) = '5'";
}
if (Frm_main.admin_flag)
{
sql += "select m.card_number card_number, m.billing_amount, m.merchant_description description,trunc(m.processing_date) Ngay_XuLy";
}
else
{
sql += "select substr(m.card_number,1,4)||'xxxxxxxx'||substr(m.card_number,-4) card_number, m.billing_amount, m.merchant_description description,trunc(m.processing_date) Ngay_XuLy" ;
}
sql += ",a.branch_code as branch_code " +
" from " + Businessbp.executedb.owner + "transaction_hist m ," + Businessbp.executedb.owner + "cp_card_prepaid a " +
" where m.transaction_code = '64' " +
" and a.card_number=m.card_number" +
" and trunc(m.processing_date) >= trunc(to_date('" + dtFrom.Text.Trim() + "','mm/dd/yyyy')) " +
" and trunc(m.processing_date) <= trunc(to_date('" + dtTo.Text.Trim() + "','mm/dd/yyyy'))";
sql = sql + dk;
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
a.dt = dtt;
a.title = "PHÁT SINH LÃI PREPAID";
a.thoiGian = "Từ ngày: " + dtFrom.Text + " đến ngày " + dtTo.Text;
a.ShowDialog();
}
#endregion R7.4
#region R7.5-8
if (comboBox1.SelectedIndex + 1 == 8) // Report 7.5
{
string sql = "", dk = "", err = "";
sql = "select b.branch_code, round(sum(decode(substr(to_char(round(a.balance,0)),0,1),'-', 0, round(a.balance,0))),0) as DU_CO,round(sum(decode(substr(to_char(round(a.balance,0)),0,1),'-',round(a.balance,0),0)),0) THAU_CHI " +
"from " + Businessbp.executedb.owner + "daily_balance a, " + Businessbp.executedb.owner + "cp_card_prepaid b, " + Businessbp.executedb.owner + "nab_phanloaisp c " +
"where a.account_number = b.card_number " +
"and b.product_code = c.product_code " +
"and c.type = 'P' " +
"and trunc(a.business_date) = trunc(to_date('" + dtFrom.Text.Trim() + "','mm/dd/yyyy')) ";
if (cbNoidia.Checked == true)
{
dk = "and substr(b.card_number,0,1) <> '5' ";
}
else if (cbQuocte.Checked == true)
{
dk = "and substr(b.card_number,0,1) = '5' ";
}
sql = sql + dk;
sql += " group by b.branch_code ";
Frm_Show_R7 a = new Frm_Show_R7();
a.sql_R75 = sql;
a.ShowDialog();
}
#endregion R7.5-8
#region 2
if (comboBox1.SelectedIndex + 1 == 2)
{
Frm_Report a = new Frm_Report();
a.type = "MC_R2";
string sql = "", err = "";
if (cbQuocte.Checked == true)
{
if (Frm_main.admin_flag)
{
sql += "select A.card_number card_number, A.card_account_number card_account_number,A.billing_amount, A.date_create,";
}
else
{
sql += "select substr(A.card_number,1,4)||'xxxxxxxx'||substr(A.card_number,-4) card_number,substr(A.card_account_number,1,4)||'xxxxxxxx'||substr(A.card_account_number,-4) card_account_number,A.billing_amount, A.date_create,";
}
sql += " decode((select c2.product_type "
+ " from " + Businessbp.executedb.owner + "card c1, " + Businessbp.executedb.owner + "product c2 "
+ " where c1.card_number = A.card_number "
+ " and c1.product_code = c2.product_code),'0','Prepaid','1','Credit') product_type"
+ " from (select h1.card_number,h1.card_account_number,h1.billing_amount, h1.date_create"
+ " from " + Businessbp.executedb.owner + "transaction_hist h1"
+ " where trunc(h1.date_create) >= trunc(to_date('" + dtFrom.Text.Trim() + "','mm/dd/yyyy'))"
+ " and trunc(h1.date_create) <= trunc(to_date('" + dtTo.Text.Trim() + "','mm/dd/yyyy'))"
+ " and h1.transaction_code = 'U1'"
+ " and substr(h1.card_account_number,0,1) = '5'"
+ " union all"
+ " select vv.card_number,vv.cr_account_nbr, vv.billing_amount, vv.date_create"
+ " from " + Businessbp.executedb.owner + "cr_current_trans vv"
+ " where vv.transaction_code in ('58','71')"
+ " and trunc(vv.date_create) >= trunc(to_date('" + dtFrom.Text.Trim() + "','mm/dd/yyyy'))"
+ " and trunc(vv.date_create) <= trunc(to_date('" + dtTo.Text.Trim() + "','mm/dd/yyyy'))"
+ " and substr(vv.cr_account_nbr,0,1) = '5'"
+ " union all"
+ " select uu.card_number,uu.cr_account_nbr, uu.billing_amount,uu.date_create"
+ " from " + Businessbp.executedb.owner + "cr_hist_trans uu"
+ " where uu.transaction_code in ('58','71')"
+ " and trunc(uu.date_create) >= trunc(to_date('" + dtFrom.Text.Trim() + "','mm/dd/yyyy'))"
+ " and trunc(uu.date_create) <= trunc(to_date('" + dtTo.Text.Trim() + "','mm/dd/yyyy'))"
+ " and substr(uu.cr_account_nbr,0,1) = '5') A";
}
else if (cbNoidia.Checked == true)
{
if (Frm_main.admin_flag)
{
sql += "select A.card_number card_number, A.card_account_number card_account_number,A.billing_amount, A.date_create,";
}
else
{
sql += "select substr(A.card_number,1,4)||'xxxxxxxx'||substr(A.card_number,-4) card_number,substr(A.card_account_number,1,4)||'xxxxxxxx'||substr(A.card_account_number,-4) card_account_number,A.billing_amount, A.date_create,";
}
sql += "decode((select c2.product_type " +
"from " + Businessbp.executedb.owner + "card c1, " + Businessbp.executedb.owner + "product c2 " +
"where c1.card_number = A.card_number " +
"and c1.product_code = c2.product_code),'0','Prepaid','1','Credit') product_type " +
"from " +
"(select h1.card_number,h1.card_account_number,h1.billing_amount, h1.date_create " +
"from " + Businessbp.executedb.owner + "transaction_hist h1 " +
"where trunc(h1.date_create) >= trunc(to_date('" + dtFrom.Text.Trim() + "','mm/dd/yyyy')) " +
"and trunc(h1.date_create) <= trunc(to_date('" + dtTo.Text.Trim() + "','mm/dd/yyyy')) " +
"and h1.transaction_code = 'U1' " +
"and substr(h1.card_account_number,0,1) <> '5' " +
"union all " +
"select vv.card_number,vv.cr_account_nbr, vv.billing_amount, vv.date_create " +
"from " + Businessbp.executedb.owner + "cr_current_trans vv " +
"where vv.transaction_code in ('58','71') " +
"and trunc(vv.date_create) >= trunc(to_date('" + dtFrom.Text.Trim() + "','mm/dd/yyyy')) " +
"and trunc(vv.date_create) <= trunc(to_date('" + dtTo.Text.Trim() + "','mm/dd/yyyy')) " +
"and substr(vv.cr_account_nbr,0,1) <> '5' " +
"union all " +
"select uu.card_number,uu.cr_account_nbr, uu.billing_amount,uu.date_create " +
"from " + Businessbp.executedb.owner + "cr_hist_trans uu " +
"where uu.transaction_code in ('58','71') " +
"and trunc(uu.date_create) >= trunc(to_date('" + dtFrom.Text.Trim() + "','mm/dd/yyyy')) " +
"and trunc(uu.date_create) <= trunc(to_date('" + dtTo.Text.Trim() + "','mm/dd/yyyy')) " +
"and substr(uu.cr_account_nbr,0,1) <> '5' " +
") A ";
}
if (rbAllKhuVuc.Checked == true)
{
if (Frm_main.admin_flag)
{
sql += "select A.card_number card_number, A.card_account_number card_account_number,A.billing_amount, A.date_create,";
}
else
{
sql += "select substr(A.card_number,1,4)||'xxxxxxxx'||substr(A.card_number,-4) card_number,substr(A.card_account_number,1,4)||'xxxxxxxx'||substr(A.card_account_number,-4) card_account_number,A.billing_amount, A.date_create,";
}
sql += "decode((select c2.product_type " +
"from " + Businessbp.executedb.owner + "card c1, " + Businessbp.executedb.owner + "product c2 " +
"where c1.card_number = A.card_number " +
"and c1.product_code = c2.product_code),'0','Prepaid','1','Credit') product_type " +
"from " +
"(select h1.card_number,h1.card_account_number,h1.billing_amount, h1.date_create " +
"from " + Businessbp.executedb.owner + "transaction_hist h1 " +
"where trunc(h1.date_create) >= trunc(to_date('" + dtFrom.Text.Trim() + "','mm/dd/yyyy')) " +
"and trunc(h1.date_create) <= trunc(to_date('" + dtTo.Text.Trim() + "','mm/dd/yyyy')) " +
"and h1.transaction_code = 'U1' " +
"union all " +
"select vv.card_number,vv.cr_account_nbr, vv.billing_amount, vv.date_create " +
"from " + Businessbp.executedb.owner + "cr_current_trans vv " +
"where vv.transaction_code in ('58','71') " +
"and trunc(vv.date_create) >= trunc(to_date('" + dtFrom.Text.Trim() + "','mm/dd/yyyy')) " +
"and trunc(vv.date_create) <= trunc(to_date('" + dtTo.Text.Trim() + "','mm/dd/yyyy')) " +
"union all " +
"select uu.card_number,uu.cr_account_nbr, uu.billing_amount,uu.date_create " +
"from " + Businessbp.executedb.owner + "cr_hist_trans uu " +
"where uu.transaction_code in ('58','71') " +
"and trunc(uu.date_create) >= trunc(to_date('" + dtFrom.Text.Trim() + "','mm/dd/yyyy')) " +
"and trunc(uu.date_create) <= trunc(to_date('" + dtTo.Text.Trim() + "','mm/dd/yyyy')) " +
") A ";
}
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
a.dt = dtt;
a.title = "DANH SÁCH KHÁCH HÀNG NỘP TIỀN THEO LOẠI THẺ";
a.thoiGian = "Từ ngày: " + dtFrom.Text + " đến ngày " + dtTo.Text;
a.ShowDialog();
}
#endregion 2
#region 3
if (comboBox1.SelectedIndex + 1 == 3)
{
string sql = "", dk = "", err = "";
sql = " select b.branch_code, sum(round(a.transaction_amount,0)) So_Tien ";
sql += " from " + Businessbp.executedb.owner + "transaction_hist a, " + Businessbp.executedb.owner + "cp_card_prepaid b";
sql += " where a.transaction_code = '64'";
sql += " and trim(a.card_number) = trim(b.card_number)";
sql += " group by b.branch_code";
Frm_Show_R9 a = new Frm_Show_R9();
a.sql_R91 = sql;
a.type = "R3";
a.ShowDialog();
}
#endregion 3
#region 4
if (comboBox1.SelectedIndex + 1 == 4)
{
string sql = "", dk = "", err = "";
sql += " select trim(c.branch_code_dsa_code) branch_code, sum(a.transaction_amount) So_Tien ";
sql += " from ";
sql += " (";
sql += " select a1.transaction_code, a1.cr_account_nbr, round(a1.billing_amount,0) transaction_amount ";
sql += " from " + Businessbp.executedb.owner + "cr_current_trans a1 ";
sql += " where a1.transaction_code in ('63','53')";
sql += " union all";
sql += " select a2.transaction_code, a2.cr_account_nbr, round(a2.billing_amount,0) transaction_amount ";
sql += " from " + Businessbp.executedb.owner + "cr_hist_trans a2 ";
sql += " where a2.transaction_code in ('63','53')";
sql += " ) a,";
sql += " " + Businessbp.executedb.owner + "application b, " + Businessbp.executedb.owner + "cp_card c";
sql += " where trim(a.cr_account_nbr) = trim(b.card_number)";
sql += " and trim(b.source_application_no) = trim(c.source_application_no)";
sql += " group by trim(c.branch_code_dsa_code)";
Frm_Show_R9 a = new Frm_Show_R9();
a.sql_R91 = sql;
a.type = "R4";
a.ShowDialog();
}
#endregion 4
//Tont begin: 19042012
//cập nhật dk: phân loại theo loại thẻ, khu vực,...
//Tont end: 19042012
//Nguyen: 07062012: bỏ option thiết bị, option không chuyển khoản
#region 5
if (comboBox1.SelectedIndex + 1 == 5)
{
Frm_Report a = new Frm_Report();
a.type = "MC_R5";
string sql = "", sqlPrepaid = "", sqlCredit1 = "", sqlCredit2 = "", sqlCredit = "", dk = "", err = "";
//string dkkhuvuc = "", dkgiaodich = "", dkchuyenkhoan = "", dkthietbi = "", tohopdk = "";
string dkkhuvuc = "", dkgiaodich = "", tohopdk = "";
if (Frm_main.admin_flag)
{
sqlPrepaid = "select 'Prepaid' as product_type ,b.card_number card_number, c.transaction_wording, b.transaction_date, b.processing_date, decode(b.reversal_flag,'R',-b.transaction_amount,b.transaction_amount) transaction_amount, b.transaction_currency,";
}
else
{
sqlPrepaid = "select 'Prepaid' as product_type ,substr(b.card_number,1,4)||'xxxxxxxx'||substr(b.card_number,-4) card_number, c.transaction_wording, b.transaction_date, b.processing_date, decode(b.reversal_flag,'R',-b.transaction_amount,b.transaction_amount) transaction_amount, b.transaction_currency,";
}
sqlPrepaid += "decode(b.reversal_flag,'R',-b.billing_amount,billing_amount) billing_amount, b.billing_currency, b.reversal_flag,d.branch_code, " +
"'' as branch_name " +
" from " + Businessbp.executedb.owner + "transaction_hist b, " + Businessbp.executedb.owner + "trans_def_stand c, " + Businessbp.executedb.owner + "cp_card_prepaid d " +
" where b.transaction_code = c.transaction_code " +
"and b.product_type = '0' " + // Prepaid va Debit Master
"and b.product_code not in (select sp.product_code from " + Businessbp.executedb.owner + "nab_phanloaisp sp where sp.type = 'D') " + // loai ra Debit Master
"and b.acquirer_bank_code = '970428' " +
"and b.origine_code = '0' " +
//"and trim(b.issuer_ica) = '013481' " +
"and b.issuer_bank_code = '970428' " +
"and b.transaction_code not in (select transaction_code from " + Businessbp.executedb.owner + "nab_exception_trn) "+//
" and b.transaction_code in ('01')"+
"and trunc(b.processing_date) >= trunc(to_date('" + dtFrom.Text.Trim() + "','mm/dd/yyyy')) " +
"and trunc(b.processing_date) <= trunc(to_date('" + dtTo.Text.Trim() + "','mm/dd/yyyy')) " +
"and b.card_number = d.card_number " +
"and d.bank_code = '970428' ";
/*
sqlCredit = "select 'Credit' as product_type ,b.card_number, c.transaction_wording, b.transaction_date, b.processing_date, decode(b.reversal_flag,'R',-b.transaction_amount,b.transaction_amount) transaction_amount, b.transaction_currency," +
"decode(b.reversal_flag,'R',-b.billing_amount,billing_amount) billing_amount, b.billing_currency, b.reversal_flag " +
"from transaction_hist b, trans_def_stand c " +
"where b.transaction_code = c.transaction_code " +
"and b.product_type = '1' " + // Credit
"and b.acquirer_bank_code = '970428' " +
"and b.origine_code = '0' " +
//"and trim(b.issuer_ica) = '013481' " +
"and b.issuer_bank_code = '970428' " +
"and b.transaction_code not in ('63','64') " +
"and trunc(b.processing_date) >= trunc(to_date('" + dtFrom.Text.Trim() + "','mm/dd/yyyy')) " +
"and trunc(b.processing_date) <= trunc(to_date('" + dtTo.Text.Trim() + "','mm/dd/yyyy')) ";
*/
if (Frm_main.admin_flag)
{
sqlCredit1 = "select 'Credit' as product_type,b.card_number card_number, c.transaction_wording, b.transaction_date, b.processing_date, decode(b.reversal_flag,'R',-b.transaction_amount,b.transaction_amount) transaction_amount, b.transaction_currency,";
}
else
{
sqlCredit1 = "select 'Credit' as product_type,substr(b.card_number,1,4)||'xxxxxxxx'||substr(b.card_number,-4) card_number, c.transaction_wording, b.transaction_date, b.processing_date, decode(b.reversal_flag,'R',-b.transaction_amount,b.transaction_amount) transaction_amount, b.transaction_currency,";
}
sqlCredit1 += "decode(b.reversal_flag,'R',-b.billing_amount,billing_amount) billing_amount, b.billing_currency, b.reversal_flag,cd.branch_code_dsa_code as branch_code, " +
" '' as branch_name " +
" from " + Businessbp.executedb.owner + "cr_current_trans b, " + Businessbp.executedb.owner + "trans_def_stand c , " + Businessbp.executedb.owner + "cp_card cd ," + Businessbp.executedb.owner + "application app" +
" where b.transaction_code = c.transaction_code " +
" and b.card_number = app.card_number " +
" and app.source_application_no = cd.source_application_no " +
" and cd.bank_code = '970428' " +
" and b.transaction_code in ('01')" +
"and b.MICROFILM_REF_NUMBER in (select b.microfilm_ref_number " +
"from " + Businessbp.executedb.owner + "transaction_hist b " +
"where b.acquirer_bank_code = '970428' " +
"and b.product_type = '1' " + //the credit
"and b.origine_code = '0' " +
"and b.issuer_bank_code = '970428' " +
"and b.transaction_code not in (select transaction_code from " + Businessbp.executedb.owner + "nab_exception_trn) " +//
"and trunc(b.processing_date) >= trunc(to_date('" + dtFrom.Text.Trim() + "','mm/dd/yyyy')) " +
"and trunc(b.processing_date) <= trunc(to_date('" + dtTo.Text.Trim() + "','mm/dd/yyyy')) ";
if (Frm_main.admin_flag)
{
sqlCredit2 = "select 'Credit' as product_type, b.card_number card_number, c.transaction_wording, b.transaction_date,b.processing_date, decode(b.reversal_flag,'R',-b.transaction_amount,b.transaction_amount) transaction_amount, b.transaction_currency,";
}
else
{
sqlCredit2 = "select 'Credit' as product_type,substr(b.card_number,1,4)||'xxxxxxxx'||substr(b.card_number,-4) card_number, c.transaction_wording, b.transaction_date,b.processing_date, decode(b.reversal_flag,'R',-b.transaction_amount,b.transaction_amount) transaction_amount, b.transaction_currency,";
}
sqlCredit2 += "decode(b.reversal_flag,'R',-b.billing_amount,billing_amount) billing_amount, b.billing_currency, b.reversal_flag,cd.branch_code_dsa_code as branch_code, " +
" '' as branch_name " +
" from " + Businessbp.executedb.owner + "cr_hist_trans b, " + Businessbp.executedb.owner + "trans_def_stand c , " + Businessbp.executedb.owner + "cp_card cd ," + Businessbp.executedb.owner + "application app " +
" where b.transaction_code = c.transaction_code " +
" and b.card_number = app.card_number " +
" and app.source_application_no = cd.source_application_no " +
" and cd.bank_code = '970428' " +
" and b.transaction_code in ('01')" +
" and b.MICROFILM_REF_NUMBER in (select b.microfilm_ref_number " +
"from " + Businessbp.executedb.owner + "transaction_hist b " +
"where b.acquirer_bank_code = '970428' " +
"and b.product_type = '1' " + //the credit
"and b.origine_code = '0' " +
"and b.issuer_bank_code = '970428' " +
"and b.transaction_code not in (select transaction_code from " + Businessbp.executedb.owner + "nab_exception_trn) " +//
"and trunc(b.processing_date) >= trunc(to_date('" + dtFrom.Text.Trim() + "','mm/dd/yyyy')) " +
"and trunc(b.processing_date) <= trunc(to_date('" + dtTo.Text.Trim() + "','mm/dd/yyyy')) ";
// chon theo loai the: noi dia, qte
if (cbNoidia.Checked == true)
{
dkkhuvuc = "and substr(b.card_number,0,1) = '9' ";
}
else
{
if (cbQuocte.Checked == true)
{
dkkhuvuc = "and substr(b.card_number,0,1) = '5' ";
}
else
if (rbAllKhuVuc.Checked == true)
{
dkkhuvuc = "";
}
}
// chon theo Fee/GD
if (rbFee.Checked == true)
{
dkgiaodich = "and b.transaction_code in ('22') ";
}
else
{
if (rbGiaoDich.Checked == true)
{
dkgiaodich = "and b.transaction_code not in ('22') ";
}
else
if (rbAllGiaoDich.Checked == true)
{
dkgiaodich = "";
}
}
// chon theo chuyen khoan
//if(rbKhongChuyenKhoan.Checked == true)
//{
// dkchuyenkhoan = "and b.transaction_code not in ('45','60') ";
//}
//else
//{
// dkchuyenkhoan = "";
//}
//// chon theo ATM/POS
//if(rbATM.Checked == true)
//{
// dkthietbi = "and trim(b.MCC) = '6011' ";
//}
//else
//{
// if(rbPOS.Checked == true)
// {
// dkthietbi = "and trim(b.MCC) <> '6011' ";
// }
// else
// if(rbAllThietBi.Checked == true)
// {
// dkthietbi = "";
// }
//}
//tohopdk += dkkhuvuc + dkchuyenkhoan + dkthietbi; // to hop dk nay ko co chua dkgiaodich, dkgiaodich nay se duoc them rieng trong cau credit
tohopdk += dkkhuvuc;
// chon theo loai Credit/ Prepaid : Day la dk chu dao
if (rbAllCard.Checked == true)
{
sqlCredit = sqlCredit1 +
tohopdk + ") " +
dkgiaodich +
"union all " +
sqlCredit2 +
tohopdk + ")" +
dkgiaodich;
//sql = sqlPrepaid + dkkhuvuc + dkgiaodich + dkchuyenkhoan + dkthietbi +
sql = sqlPrepaid + dkkhuvuc + dkgiaodich +
"union all " +
sqlCredit;
}
else
{
if (rbPrepaid.Checked == true)
{
//sql = sqlPrepaid + dkkhuvuc + dkgiaodich + dkchuyenkhoan + dkthietbi;
sql = sqlPrepaid + dkkhuvuc + dkgiaodich;
}
else
{
sqlCredit = sqlCredit1 +
tohopdk + ") " +
dkgiaodich +
"union all " +
sqlCredit2 +
tohopdk + ")" +
dkgiaodich;
sql = sqlCredit;
}
}
sql = sql + " order by branch_code";
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
a.dt = dtt;
a.title = "GIAO DỊCH POS ĐÃ SETTLEMENT (ON-US)";
a.thoiGian = "Từ ngày: " + dtFrom.Text + " đến ngày " + dtTo.Text;
a.ShowDialog();
}
#endregion 5
#region 6
//Tont begin: 19042012
//cập nhật dk: phân loại theo loại thẻ, khu vực,...
//Tont end: 19042012
//Nguyen: 07062012 thêm thẻ Debit
if (comboBox1.SelectedIndex + 1 == 6)
{
Frm_Report a = new Frm_Report();
a.type = "MC_R6";
string sql = "", sqlPrepaid = "", sqlCredit1 = "", sqlCredit2 = "", sqlCredit = "", sqlDebit = "", dk = "", err = "";
string dkkhuvuc = "", dkgiaodich = "", dkchuyenkhoan = "", dkvitri = "", tohopdk = "";
sqlPrepaid = "select 'Prepaid' as product_type, ";
if (Frm_main.admin_flag)
{
sqlPrepaid += " b.card_number card_number, c.transaction_wording, b.transaction_date,";
}
else
{
sqlPrepaid += " substr(b.card_number,1,4)||'xxxxxxxx'||substr(b.card_number,-4) card_number, c.transaction_wording, b.transaction_date,";
}
sqlPrepaid += " decode(b.reversal_flag,'R',-b.transaction_amount,b.transaction_amount) transaction_amount," +
" b.transaction_currency, decode(b.reversal_flag,'R',-b.billing_amount,billing_amount) billing_amount," +
" b.billing_currency," +
" b.settlement_amount settle_amount,b.settlement_currency settle_currency," +
" b.reversal_flag,d.branch_code," +
" '' as branch_name " +
" from " + Businessbp.executedb.owner + "transaction_hist b, " + Businessbp.executedb.owner + "trans_def_stand c, " + Businessbp.executedb.owner + "cp_card_prepaid d " +
" where b.transaction_code = c.transaction_code" +
" and b.product_type = '0'" + // Prepaid va Debit Master
" and b.product_code not in (select sp.product_code from " + Businessbp.executedb.owner + "nab_phanloaisp sp where sp.type = 'D') " + // loai ra Debit Master
" and b.acquirer_bank_code <> '970428'" +
//" and trim(b.issuer_ica) = '013481'" +
" and b.issuer_bank_code = '970428'" +
//" and trim(b.outlet_number) = '0000018'" +
" and b.transaction_code not in (select transaction_code from " + Businessbp.executedb.owner + "nab_exception_trn)" +
" and b.function_code = '205'" +
" and trunc(b.processing_date) >= trunc(to_date('" + dtFrom.Text.Trim() + "','mm/dd/yyyy'))" +
" and trunc(b.processing_date) <= trunc(to_date('" + dtTo.Text.Trim() + "','mm/dd/yyyy'))" +
" and b.card_number = d.card_number " +
" and d.bank_code = '970428' ";
sqlDebit = "select 'Debit' as product_type, ";
if (Frm_main.admin_flag)
{
sqlDebit += " b.card_number card_number, c.transaction_wording, b.transaction_date,";
}
else
{
sqlDebit += " substr(b.card_number,1,4)||'xxxxxxxx'||substr(b.card_number,-4) card_number, c.transaction_wording, b.transaction_date,";
}
sqlDebit += " decode(b.reversal_flag,'R',-b.transaction_amount,b.transaction_amount) transaction_amount," +
" b.transaction_currency, decode(b.reversal_flag,'R',-b.billing_amount,billing_amount) billing_amount," +
" b.billing_currency," +
" b.settlement_amount settle_amount,b.settlement_currency settle_currency," +
" b.reversal_flag,d.branch_code," +
" '' as branch_name " +
" from " + Businessbp.executedb.owner + "transaction_hist b, " + Businessbp.executedb.owner + "trans_def_stand c, " + Businessbp.executedb.owner + "cp_card_prepaid d " +
" where b.transaction_code = c.transaction_code" +
" and b.product_type = '0'" + // Prepaid va Debit Master
" and b.product_code in (select sp.product_code from " + Businessbp.executedb.owner + "nab_phanloaisp sp where sp.type = 'D') " + // lay Debit Master
" and b.acquirer_bank_code <> '970428'" +
//" and trim(b.issuer_ica) = '013481'" +
" and b.issuer_bank_code = '970428'" +
//" and trim(b.outlet_number) = '0000018'" +
" and b.transaction_code not in (select transaction_code from " + Businessbp.executedb.owner + "nab_exception_trn)" +
" and b.function_code = '205'" +
" and trunc(b.processing_date) >= trunc(to_date('" + dtFrom.Text.Trim() + "','mm/dd/yyyy'))" +
" and trunc(b.processing_date) <= trunc(to_date('" + dtTo.Text.Trim() + "','mm/dd/yyyy'))" +
" and b.card_number = d.card_number " +
" and d.bank_code = '970428' ";
/*
sqlCredit = "select 'Credit' as product_type, " +
" b.card_number, c.transaction_wording, b.transaction_date," +
" decode(b.reversal_flag,'R',-b.transaction_amount,b.transaction_amount) transaction_amount," +
" b.transaction_currency, decode(b.reversal_flag,'R',-b.billing_amount,billing_amount) billing_amount," +
" b.billing_currency, b.reversal_flag" +
" from transaction_hist b, trans_def_stand c" +
" where b.transaction_code = c.transaction_code" +
" and b.product_type = '1'" + // Credit
" and b.acquirer_bank_code <> '970428'" +
//" and b.origine_code in ('1','2')" +
" and trim(b.issuer_ica) = '013481'" +
//" and trim(b.outlet_number) = '0000018'" +
" and b.transaction_code not in ('63','64')" +
" and trunc(b.processing_date) >= trunc(to_date('" + dtFrom.Text.Trim() + "','dd/mm/yyyy'))" +
" and trunc(b.processing_date) <= trunc(to_date('" + dtTo.Text.Trim() + "','dd/mm/yyyy'))";
*/
if (Frm_main.admin_flag)
{
sqlCredit1 = "select 'Credit' as product_type, b.card_number card_number, c.transaction_wording, b.transaction_date,decode(b.reversal_flag,'R',-b.transaction_amount,b.transaction_amount) transaction_amount, b.transaction_currency," ;
}
else
{
sqlCredit1 = "select 'Credit' as product_type,substr(b.card_number,1,4)||'xxxxxxxx'||substr(b.card_number,-4) card_number, c.transaction_wording, b.transaction_date,decode(b.reversal_flag,'R',-b.transaction_amount,b.transaction_amount) transaction_amount, b.transaction_currency,";
}
sqlCredit1 += "decode(b.reversal_flag,'R',-b.billing_amount,billing_amount) billing_amount, b.billing_currency," +
"(case b.transaction_code" +
" when '22' then null" +
" else" +
" (select s.settlement_amount" +
" from " + Businessbp.executedb.owner + "transaction_hist s" +
" where s.microfilm_ref_number = b.MICROFILM_REF_NUMBER" +
" and s.sequence_number = 1)" +
" end) settle_amount," +
"(case b.transaction_code" +
" when '22' then null" +
" else" +
" (select s.settlement_currency" +
" from " + Businessbp.executedb.owner + "transaction_hist s" +
" where s.microfilm_ref_number = b.MICROFILM_REF_NUMBER" +
" and s.sequence_number = 1)" +
" end) settle_currency," +
"b.reversal_flag ,cd.branch_code_dsa_code as branch_code, " +
" '' as branch_name " +
" from " + Businessbp.executedb.owner + "cr_current_trans b, " + Businessbp.executedb.owner + "trans_def_stand c , " + Businessbp.executedb.owner + "cp_card cd ," + Businessbp.executedb.owner + "application app" +
" where b.transaction_code = c.transaction_code " +
" and b.cr_account_nbr = app.card_number " +
" and app.source_application_no = cd.source_application_no " +
" and cd.bank_code = '970428' " +
"and b.MICROFILM_REF_NUMBER in (select b.microfilm_ref_number " +
"from " + Businessbp.executedb.owner + "transaction_hist b " +
"where b.acquirer_bank_code <> '970428' " +
"and b.product_type = '1' " + //the credit
"and b.issuer_bank_code = '970428' " +
"and b.transaction_code not in (select transaction_code from " + Businessbp.executedb.owner + "nab_exception_trn)" +
"and trunc(b.processing_date) >= trunc(to_date('" + dtFrom.Text.Trim() + "','mm/dd/yyyy')) " +
"and trunc(b.processing_date) <= trunc(to_date('" + dtTo.Text.Trim() + "','mm/dd/yyyy')) ";
if (Frm_main.admin_flag)
{
sqlCredit2 = "select 'Credit' as product_type, b.card_number card_number, c.transaction_wording, b.transaction_date,decode(b.reversal_flag,'R',-b.transaction_amount,b.transaction_amount) transaction_amount, b.transaction_currency," ;
}
else
{
sqlCredit2 = "select 'Credit' as product_type,substr(b.card_number,1,4)||'xxxxxxxx'||substr(b.card_number,-4) card_number, c.transaction_wording, b.transaction_date,decode(b.reversal_flag,'R',-b.transaction_amount,b.transaction_amount) transaction_amount, b.transaction_currency,";
}
sqlCredit2 += "decode(b.reversal_flag,'R',-b.billing_amount,billing_amount) billing_amount, b.billing_currency," +
"(case b.transaction_code" +
" when '22' then null" +
" else" +
" (select s.settlement_amount" +
" from " + Businessbp.executedb.owner + "transaction_hist s" +
" where s.microfilm_ref_number = b.MICROFILM_REF_NUMBER" +
" and s.sequence_number = 1)" +
" end) settle_amount," +
"(case b.transaction_code" +
" when '22' then null" +
" else" +
" (select s.settlement_currency" +
" from " + Businessbp.executedb.owner + "transaction_hist s" +
" where s.microfilm_ref_number = b.MICROFILM_REF_NUMBER" +
" and s.sequence_number = 1)" +
" end) settle_currency," +
"b.reversal_flag ,cd.branch_code_dsa_code as branch_code, " +
" '' as branch_name " +
" from " + Businessbp.executedb.owner + "cr_hist_trans b, " + Businessbp.executedb.owner + "trans_def_stand c , " + Businessbp.executedb.owner + "cp_card cd ," + Businessbp.executedb.owner + "application app" +
" where b.transaction_code = c.transaction_code " +
" and b.cr_account_nbr = app.card_number " +
" and app.source_application_no = cd.source_application_no " +
" and cd.bank_code = '970428' " +
" and b.MICROFILM_REF_NUMBER in (select b.microfilm_ref_number " +
"from " + Businessbp.executedb.owner + "transaction_hist b " +
"where b.acquirer_bank_code <> '970428' " +
"and b.product_type = '1' " + //the credit
"and b.issuer_bank_code = '970428' " +
"and b.transaction_code not in (select transaction_code from " + Businessbp.executedb.owner + "nab_exception_trn)" +
"and trunc(b.processing_date) >= trunc(to_date('" + dtFrom.Text.Trim() + "','mm/dd/yyyy')) " +
"and trunc(b.processing_date) <= trunc(to_date('" + dtTo.Text.Trim() + "','mm/dd/yyyy')) ";
// chon theo loai the: noi dia, qte
if (cbNoidia.Checked == true)
{
dkkhuvuc = "and substr(b.card_number,0,1) = '9' ";
}
else
{
if (cbQuocte.Checked == true)
{
dkkhuvuc = "and substr(b.card_number,0,1) = '5' ";
}
else
if (rbAllKhuVuc.Checked == true)
{
dkkhuvuc = "";
}
}
// chon theo Fee/GD
if (rbFee.Checked == true)
{
dkgiaodich = "and b.transaction_code in ('22') ";
}
else
{
if (rbGiaoDich.Checked == true)
{
dkgiaodich = "and b.transaction_code not in ('22') ";
}
else
if (rbAllGiaoDich.Checked == true)
{
dkgiaodich = "";
}
}
// chon theo chuyen khoan
if (rbKhongChuyenKhoan.Checked == true)
{
dkchuyenkhoan = "and b.transaction_code not in ('45','60') ";
}
else
{
dkchuyenkhoan = "";
}
// chon theo vi tri giao dich xay ra
if (rbVN.Checked == true)
{
dkvitri = "and b.origine_code = '1' ";
}
else
{
if (rbNuocNgoai.Checked == true)
{
dkvitri = "and b.origine_code = '2' ";
}
else
if (rbAllViTri.Checked == true)
{
dkvitri = "";
}
}
tohopdk += dkkhuvuc + dkchuyenkhoan + dkvitri; // to hop dk nay ko co chua dkgiaodich, dkgiaodich nay se duoc them rieng trong cau credit
// chon theo loai Credit/ Prepaid/Debit : Day la dk chu dao
if (rbAllCard.Checked == true)
{
sqlCredit = sqlCredit1 +
tohopdk + ") " +
dkgiaodich +
"union all " +
sqlCredit2 +
tohopdk + ")" +
dkgiaodich;
sql = sqlPrepaid + dkkhuvuc + dkgiaodich + dkchuyenkhoan + dkvitri +
"union all " +
sqlDebit + dkkhuvuc + dkgiaodich + dkchuyenkhoan + dkvitri +
"union all " +
sqlCredit;
}
else
{
if (rbPrepaid.Checked == true)
{
sql = sqlPrepaid + dkkhuvuc + dkgiaodich + dkchuyenkhoan + dkvitri;
}
else
{
if (rbDebit.Checked == true)
{
sql = sqlDebit + dkkhuvuc + dkgiaodich + dkchuyenkhoan + dkvitri;
}
else
{
sqlCredit = sqlCredit1 +
tohopdk + ") " +
dkgiaodich +
"union all " +
sqlCredit2 +
tohopdk + ")" +
dkgiaodich;
sql = sqlCredit;
}
}
}
sql = sql + " order by branch_code";
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
a.dt = dtt;
a.title = "GIAO DỊCH MATCH VỚI MASTER/BANKNET";
a.thoiGian = "Từ ngày: " + dtFrom.Text + " đến ngày " + dtTo.Text;
a.ShowDialog();
}
#endregion 6
#region 9
//Nguyen: 19-04-2012
if (comboBox1.SelectedIndex + 1 == 9)
{
string sql = "", dk = "", err = "";
if (Frm_main.admin_flag)
{
sql = " select b.card_number card_number, c.transaction_wording, b.transaction_date, b.processing_date," ;
}
else
{
sql = " select substr(b.card_number,1,4)||'xxxxxxxx'||substr(b.card_number,-4) card_number, c.transaction_wording, b.transaction_date, b.processing_date,";
}
sql += " decode(b.reversal_flag,'R',-b.transaction_amount, b.transaction_amount) transaction_amount, b.transaction_currency, " +
" decode(b.reversal_flag,'R',-b.billing_amount,billing_amount) billing_amount, b.billing_currency," +
" b.settlement_amount settle_amount,b.settlement_currency settle_currency," +
"b.reversal_flag " +
" from " + Businessbp.executedb.owner + "transaction_hist b, " + Businessbp.executedb.owner + "trans_def_stand c" +
" where b.transaction_code = c.transaction_code" +
" and b.acquirer_bank_code = '970428'" +
" and b.issuer_bank_code <> '970428'" +
" and trim(b.outlet_number) <> '0000018' " +
" and b.transaction_code not in ('63','64')" +
" and trunc(b.processing_date) >= trunc(to_date('" + dtFrom.Text + "','mm/dd/yyyy'))" +
" and trunc(b.processing_date) <= trunc(to_date('" + dtTo.Text + "','mm/dd/yyyy'))";
//khu vuc
if (cbNoidia.Checked)
{
dk += " and substr(b.card_number,0,1) <> '5' ";
}
if (cbQuocte.Checked)
{
dk += " and substr(b.card_number,0,1) = '5' ";
}
if (rbAllKhuVuc.Checked)
{
dk += "";
}
//vi tri
if (rbVN.Checked)
{
dk += " and b.origine_code = '3' ";
}
if (rbNuocNgoai.Checked)
{
dk += " and b.origine_code = '4' ";
}
if (rbAllViTri.Checked)
{
sql = sql + " and b.origine_code in ('3','4') ";
}
//loai tien
if (rbVND.Checked)
{
dk += " and b.transaction_currency = '704' ";
}
if (rbUSD.Checked)
{
dk += " and b.transaction_currency <> '704' ";
}
if (rbALL.Checked)
{
dk += "";
}
//
dk += " order by transaction_wording, processing_date asc ";
sql = sql + dk;
DataTable dt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
else
{
Frm_Report a = new Frm_Report();
a.type = "MC_8";
a.title = "POS ACQUIER ĐÃ SETTLEMENT (OFF-US)";
a.thoiGian = "Từ ngày: " + dtFrom.Text + " đến ngày " + dtTo.Text;
a.dt = dt;
a.ShowDialog();
}
}
//End Nguyen: 19-04-2012
#endregion 9
#region 10
//Dat lam ngay 10/04/2012
if (comboBox1.SelectedIndex + 1 == 10)//R9
{
Frm_Report a = new Frm_Report();
a.type = "MC_R9";
string sql = "", dk = "", err = "";
//Câu SQL
if (Frm_main.admin_flag)
{
sql = "select to_char(ff.business_date,'dd/mm/yyyy') as business_date, ff.cr_account_nbr cr_account_nbr, ";
}
else
{
sql = "select to_char(ff.business_date,'dd/mm/yyyy') as business_date,substr(ff.cr_account_nbr,1,4)||'xxxxxxxx'||substr(ff.cr_account_nbr,-4) cr_account_nbr, ";
}
sql += " decode(substr(ff.closing_bal,0,1),'-',0,ff.closing_bal) DU_NO," +
" decode(substr(ff.closing_bal,0,1),'-', ff.closing_bal, 0) DU_CO," +
" '' as GHI_CHU,e.branch_code_dsa_code as branch_code" +
" from " + Businessbp.executedb.owner + "account_balance ff," + Businessbp.executedb.owner + "cr_card_balance_details b"+
"," + Businessbp.executedb.owner + "card c, " + Businessbp.executedb.owner + "application d," + Businessbp.executedb.owner + "cp_card e" +
" where trunc(ff.business_date) >= trunc(to_date('" + dtFrom.Text.Trim() + "','mm/dd/yyyy')) " +
" and trunc(ff.business_date) <= trunc(to_date('" + dtTo.Text.Trim() + "','mm/dd/yyyy')) " +
" and ff.cr_account_nbr=b.cr_account_nbr"+
" and b.card_number=c.card_number"+
" and c.card_number=d.card_number"+
" and d.application_no=e.source_application_no"+
" order by ff.business_date desc ";
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
a.dt = dtt;
a.title = "CREDIT ACCOUNTING ";
a.thoiGian = "Từ ngày: " + dtFrom.Text + " đến ngày " + dtTo.Text;
a.ShowDialog();
}
#endregion 10
#region 11
if (comboBox1.SelectedIndex + 1 == 11)//R9.1
{
string sql = "", dk = "", err = "";
sql = " select b.branch_code_dsa_code, ltrim(to_char(nvl(sum(round(decode(substr((nvl(a.bal_purchase,0) + nvl(a.bal_cash,0) + nvl(a.bal_te,0) + nvl(a.bal_unique,0) + nvl(a.bal_cheque,0) + nvl(a.bal_fee,0) + nvl(a.bal_interest,0) - nvl(a.bal_payment,0) + nvl(a.bal_transfer,0) + nvl(a.cur_purchase,0) + nvl(a.cur_cash,0) + nvl(a.cur_te,0) + nvl(a.cur_unique,0) + nvl(a.cur_cheque,0) + nvl(a.cur_fee,0) + nvl(a.cur_interest,0) - nvl(a.cur_payment,0) + nvl(a.cur_transfer,0)),1,1),'-',nvl(a.bal_purchase,0) + nvl(a.bal_cash,0) + nvl(a.bal_te,0) + nvl(a.bal_unique,0) + nvl(a.bal_cheque,0) + nvl(a.bal_fee,0) + nvl(a.bal_interest,0) - nvl(a.bal_payment,0) + nvl(a.bal_transfer,0) + nvl(a.cur_purchase,0) + nvl(a.cur_cash,0) + nvl(a.cur_te,0) + nvl(a.cur_unique,0) + nvl(a.cur_cheque,0) + nvl(a.cur_fee,0) + nvl(a.cur_interest,0) - nvl(a.cur_payment,0) + nvl(a.cur_transfer,0),0),0)),0), '999,999,999,999.00')) DU_CO";
sql += ",ltrim(to_char(nvl(sum(round(decode(substr((nvl(a.bal_purchase,0) + nvl(a.bal_cash,0) + nvl(a.bal_te,0) + nvl(a.bal_unique,0) + nvl(a.bal_cheque,0) + nvl(a.bal_fee,0) + nvl(a.bal_interest,0) - nvl(a.bal_payment,0) + nvl(a.bal_transfer,0) + nvl(a.cur_purchase,0) + nvl(a.cur_cash,0) + nvl(a.cur_te,0) + nvl(a.cur_unique,0) + nvl(a.cur_cheque,0) + nvl(a.cur_fee,0) + nvl(a.cur_interest,0) - nvl(a.cur_payment,0) + nvl(a.cur_transfer,0)),1,1),'-',0,nvl(a.bal_purchase,0) + nvl(a.bal_cash,0) + nvl(a.bal_te,0) + nvl(a.bal_unique,0) + nvl(a.bal_cheque,0) + nvl(a.bal_fee,0) + nvl(a.bal_interest,0) - nvl(a.bal_payment,0) + nvl(a.bal_transfer,0) + nvl(a.cur_purchase,0) + nvl(a.cur_cash,0) + nvl(a.cur_te,0) + nvl(a.cur_unique,0) + nvl(a.cur_cheque,0) + nvl(a.cur_fee,0) + nvl(a.cur_interest,0) - nvl(a.cur_payment,0) + nvl(a.cur_transfer,0)),0)),0), '999,999,999,999.00')) DU_NO";
sql += " from " + Businessbp.executedb.owner + "account_balance a, " + Businessbp.executedb.owner + "cp_card b, " + Businessbp.executedb.owner + "application c";
sql += " where 1=1";
sql += " and a.cr_account_nbr = c.card_number";
sql += " and b.source_application_no = c.application_no";
sql += " and trunc(a.business_date) >= trunc(to_date('" + dtFrom.Text.Trim() + "','mm/dd/yyyy')) ";
sql += " and trunc(a.business_date) <= trunc(to_date('" + dtTo.Text.Trim() + "','mm/dd/yyyy')) ";
if (cbNoidia.Checked == true)
{
sql += "and substr(a.cr_account_nbr,1,1) <> '5'";
}
else if (cbQuocte.Checked == true)
{
sql += "and substr(a.cr_account_nbr,1,1) = '5'";
}
sql += " group by b.branch_code_dsa_code";
Frm_Show_R9 a = new Frm_Show_R9();
a.sql_R91 = sql;
a.type = "R9";
a.ShowDialog();
}
#endregion 11
#region 17
if (comboBox1.SelectedIndex + 1 == 17)//R15
{
Frm_Report a = new Frm_Report();
a.type = "MC_R15";
string sql = "", dk = "", err = "";
//Câu SQL
if (Frm_main.admin_flag)
{
sql = "select vv.cr_account_nbr as Card_number, sum(vv.billing_amount) as Interest, to_char(vv.processing_date,'dd/mm/yyyy') as processing_date,'' as Ghi_chu,e.branch_code_dsa_code as branch_code";
}
else
{
sql = "select substr(vv.cr_account_nbr,1,4)||'xxxxxxxx'||substr(vv.cr_account_nbr,-4) as Card_number, sum(vv.billing_amount) as Interest, to_char(vv.processing_date,'dd/mm/yyyy') as processing_date,'' as Ghi_chu,e.branch_code_dsa_code as branch_code";
}
sql += " from " + Businessbp.executedb.owner + "cr_current_trans vv," + Businessbp.executedb.owner + "cr_card_balance_details b" +
"," + Businessbp.executedb.owner + "card c, " + Businessbp.executedb.owner + "application d," + Businessbp.executedb.owner + "cp_card e" +
" where vv.transaction_code in ('63','53')" +
" and vv.cr_account_nbr=b.cr_account_nbr" +
" and b.card_number=c.card_number" +
" and c.card_number=d.card_number";
if (cbNoidia.Checked)
sql += " and substr(b.card_number,1,1)='9'";
else if (cbQuocte.Checked)
sql += " and substr(b.card_number,1,1)='5'";
sql += " and d.application_no=e.source_application_no" +
" and trunc(vv.processing_date) >= to_date('" + dtFrom.Text.Trim() + "','mm/dd/yyyy') " +
" and trunc(vv.processing_date) <= to_date('" + dtTo.Text.Trim() + "','mm/dd/yyyy') " +
" Group by e.branch_code_dsa_code,vv.cr_account_nbr,vv.processing_date ";
sql += " union all";
if (Frm_main.admin_flag)
{
sql += " select vv.cr_account_nbr as Card_number, sum(vv.billing_amount) as Interest, to_char(vv.processing_date,'dd/mm/yyyy') as processing_date,'' as Ghi_chu,e.branch_code_dsa_code as branch_code";
}
else
{
sql += " select substr(vv.cr_account_nbr,1,4)||'xxxxxxxx'||substr(vv.cr_account_nbr,-4) as Card_number, sum(vv.billing_amount) as Interest, to_char(vv.processing_date,'dd/mm/yyyy') as processing_date,'' as Ghi_chu,e.branch_code_dsa_code as branch_code";
}
sql += " from " + Businessbp.executedb.owner + "cr_hist_trans vv," + Businessbp.executedb.owner + "cr_card_balance_details b" +
"," + Businessbp.executedb.owner + "card c, " + Businessbp.executedb.owner + "application d," + Businessbp.executedb.owner + "cp_card e" +
" where vv.transaction_code in ('63','53')" +
" and vv.cr_account_nbr=b.cr_account_nbr" +
" and b.card_number=c.card_number" +
" and c.card_number=d.card_number";
if (cbNoidia.Checked)
sql += " and substr(b.card_number,1,1)='9'";
else if (cbQuocte.Checked)
sql += " and substr(b.card_number,1,1)='5'";
sql += " and d.application_no=e.source_application_no" +
" and trunc(vv.processing_date) >= to_date('" + dtFrom.Text.Trim() + "','mm/dd/yyyy') " +
" and trunc(vv.processing_date) <= to_date('" + dtTo.Text.Trim() + "','mm/dd/yyyy') " +
" Group by e.branch_code_dsa_code,vv.cr_account_nbr,vv.processing_date";
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
string title = "CREDIT ACCOUNTING INTEREST";
if (cbNoidia.Checked)
title += " - NỘI ĐỊA";
else if (cbQuocte.Checked)
title += " - QUỐC TẾ";
else
title += " - NỘI ĐỊA VÀ QUỐC TẾ";
a.dt = dtt;
a.title = title;// "CREDIT ACCOUNTING INTEREST";
a.thoiGian = "Từ ngày: " + dtFrom.Text + " đến ngày " + dtTo.Text;
a.ShowDialog();
}
#endregion 17
#region 13
if (comboBox1.SelectedIndex + 1 == 13)//R9.1
{
Frm_Report a = new Frm_Report();
a.type = "MC_R13";
string sql = "", dk = "", err = "";
string sql_Prepaid = "", sql_credit = "", err1 = "", err2 = "";
string Bbieu = "";
if (Frm_main.admin_flag)
{
sql_Prepaid = " select 'PREPAID' as LOAI_THE,b.branch_code, a.card_number as so_the,to_char(a.processing_date,'dd/mm/yyyy') as proccessing_date";
}
else
{
sql_Prepaid = " select 'PREPAID' as LOAI_THE,b.branch_code,substr(a.card_number,1,4)||'xxxxxxxx'||substr(a.card_number,-4) as so_the,to_char(a.processing_date,'dd/mm/yyyy') as proccessing_date";
}
sql_Prepaid += " ,d.description as LOAI_PHI,nvl(a.transaction_amount,0) as ST_GD,a.transaction_currency as LOAI_TIEN ";
sql_Prepaid += " ,sum(decode(a.reversal_flag, 'R', -a.billing_amount, a.billing_amount)) SO_TIEN_PHI ";
sql_Prepaid += " ,count(*) as GHI_CHU";
sql_Prepaid += " from " + Businessbp.executedb.owner + "transaction_hist a, " + Businessbp.executedb.owner + "cp_card_prepaid b, " + Businessbp.executedb.owner + "nab_phanloaisp c," + Businessbp.executedb.owner + "NAB_FEE_CONFIG d";
sql_Prepaid += " where a.card_number = b.card_number";
sql_Prepaid += " and b.product_code = c.product_code";
if (cbNoidia.Checked)
sql_Prepaid += " and substr(a.card_number,1,1) = '9'";// -- Noi dia";
else if (cbQuocte.Checked)
sql_Prepaid += " and substr(a.card_number,1,1) = '5'";// -- QUOC TE";
sql_Prepaid += " and c.type = 'P'";// ----Prepaid";
sql_Prepaid += " and a.product_type = '0'";// --Not Credit";
sql_Prepaid += " and trunc(a.processing_date) >= trunc(to_date('" + dtFrom.Text + "','mm/dd/yyyy'))";
sql_Prepaid += " and trunc(a.processing_date) <= trunc(to_date('" + dtTo.Text + "','mm/dd/yyyy'))";
sql_Prepaid += " and trim(a.transaction_code)=trim(d.fee_code)";
sql_Prepaid += " group by b.branch_code,a.card_number,a.transaction_amount,a.processing_date,d.description,a.transaction_currency";
if (Frm_main.admin_flag)
{
sql_credit = " select 'CREDIT' as LOAI_THE,substr(b.branch_code_dsa_code,2,3) branch_code, a.cr_account_nbr as SO_THE";
}
else
{
sql_credit = " select 'CREDIT' as LOAI_THE,substr(b.branch_code_dsa_code,2,3) branch_code,substr(a.cr_account_nbr,1,4)||'xxxxxxxx'||substr(a.cr_account_nbr,-4) as SO_THE";
}
sql_credit += " ,to_char(a.processing_date,'dd/mm/yyyy') as proccessing_date,a.description as LOAI_PHI,nvl(a.transaction_amount,0) as ST_GD,a.transaction_currency as LOAI_TIEN, sum(a.billing_amount) SO_TIEN_PHI,count(*) as GHI_CHU";
sql_credit += " from (";
sql_credit += " select aa.cr_account_nbr, aa.processing_date,aa.transaction_amount,aa.transaction_currency, decode(aa.reversal_flag, '1', -aa.billing_amount, aa.billing_amount) billing_amount,d.description ";
sql_credit += " from " + Businessbp.executedb.owner + "cr_current_trans aa," + Businessbp.executedb.owner + "NAB_FEE_CONFIG d ";
sql_credit += " where trunc(aa.processing_date) >= trunc(to_date('" + dtFrom.Text + "','mm/dd/yyyy'))";
sql_credit += " and trunc(aa.processing_date) <= trunc(to_date('" + dtTo.Text + "','mm/dd/yyyy'))";
if (cbNoidia.Checked)
sql_credit += " and substr(aa.card_number,1,1) = '9'";// -- Noi dia";
else if (cbQuocte.Checked)
sql_credit += " and substr(aa.card_number,1,1) = '5'";//
sql_credit += " and aa.transaction_code = d.Fee_code";
sql_credit += " union all";
sql_credit += " select dd.card_number, dd.processing_date,dd.transaction_amount,dd.transaction_currency, decode(dd.reversal_flag, '1', -dd.billing_amount, dd.billing_amount) billing_amount,d.description ";
sql_credit += " from " + Businessbp.executedb.owner + "cr_hist_trans dd ," + Businessbp.executedb.owner + "NAB_FEE_CONFIG d ";
sql_credit += " where 1=1";
if (cbNoidia.Checked)
sql_credit += " and substr(dd.card_number,1,1) = '9'";// -- Noi dia";
else if (cbQuocte.Checked)
sql_credit += " and substr(dd.card_number,1,1) = '5'";//
sql_credit += " and trunc(dd.processing_date) >= trunc(to_date('" + dtFrom.Text + "','mm/dd/yyyy'))";
sql_credit += " and trunc(dd.processing_date) <= trunc(to_date('" + dtTo.Text + "','mm/dd/yyyy'))";
sql_credit += " and dd.transaction_code = d.Fee_code";
sql_credit += " ) a, ";
sql_credit += " " + Businessbp.executedb.owner + "cp_card b, " + Businessbp.executedb.owner + "application c";
sql_credit += " where a.cr_account_nbr = c.card_number";
sql_credit += " and b.source_application_no = c.application_no";
sql_credit += " group by substr(b.branch_code_dsa_code,2,3),a.cr_account_nbr,a.processing_date,a.description,transaction_amount,transaction_currency";
//Câu SQL
if (rbPrepaid.Checked)
{
sql = sql_Prepaid;
Bbieu += " THẺ PREPAID";
}
else if (rbCredit.Checked)
{
sql = "";
sql = sql_credit;
Bbieu += " THẺ CREDIT";
}
else if(rbAllCard.Checked)
{
sql = sql_Prepaid +
" union all" +
sql_credit;
Bbieu += " THẺ CREDIT & PREPAID ";
}
if (cbQuocte.Checked)
{ Bbieu += " - QUỐC TẾ"; }
else if( cbNoidia.Checked)
{ Bbieu += " - NỘI ĐỊA"; }
else if(rbAllKhuVuc.Checked)
{ Bbieu += " - NỘI ĐỊA & QUỐC TẾ"; }
//sql = sql_Prepaid;
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
a.dt = dtt;
a.title = " TỔNG KẾT PHÍ GIAO DỊCH ";
a.title2 = Bbieu;
a.thoiGian = "Từ ngày: " + dtFrom.Text + " đến ngày " + dtTo.Text;
a.ShowDialog();
}
#endregion 13
//End Dat lam ngay 10/04/2012
#region R12
//Nguyen: 20062012
if (comboBox1.SelectedIndex + 1 == 12)
{
Frm_Report a = new Frm_Report();
a.type = "MC_12";
string sql = "", sqlPrepaid = "", sqlCredit1 = "", err = "",bb="";
if (Frm_main.admin_flag)
{
sqlPrepaid = " select 'Prepaid' as product_type ,b.branch_code,a.card_number as card_no, a.card_account_number as account1,";
}
else
{
sqlPrepaid = " select 'Prepaid' as product_type ,b.branch_code,substr(a.card_number,1,4)||'xxxxxxxx'||substr(a.card_number,-4) as card_no,substr(a.card_account_number,1,4)||'xxxxxxxx'||substr(a.card_account_number,-4) as account1,";
}
sqlPrepaid += " decode(a.reversal_flag, 'R', -a.transaction_amount, a.transaction_amount) as txn_amt, a.transaction_currency as txn_cur,";
sqlPrepaid += " decode(a.reversal_flag, 'R', -a.billing_amount, a.billing_amount) bill_amt,a.billing_currency as bill_cur,";
sqlPrepaid += " a.trace_audit_number as trace_no,a.authorization_number as auth_no,nvl(a.surcharge_amount,0) as surchage,to_char(a.processing_date,'dd/mm/yyyy') as process_date ";
sqlPrepaid += " from " + Businessbp.executedb.owner + "transaction_hist a, " + Businessbp.executedb.owner + "cp_card_prepaid b, " + Businessbp.executedb.owner + "nab_phanloaisp c";
sqlPrepaid += " where a.card_number = b.card_number ";
sqlPrepaid += " and b.product_code = c.product_code ";
sqlPrepaid += " and c.type = 'P' "; //prepaid
sqlPrepaid += " and a.product_type = '0' ";// not credit
sqlPrepaid += " and a.mcc = '6011' ";//ATM
sqlPrepaid += " and a.acquirer_bank_code = '970428' ";
sqlPrepaid += " and a.issuer_bank_code = '970428' ";
sqlPrepaid += " and a.origine_code = '0' "; //VN
sqlPrepaid += " and a.transaction_code not in (select transaction_code from " + Businessbp.executedb.owner + "nab_exception_trn) ";//
if (cbNoidia.Checked)
sqlPrepaid += " and substr(a.card_number,1,1) = '9' ";//-- Noi dia
if (cbQuocte.Checked)
sqlPrepaid += " and substr(a.card_number,1,1) = '5' ";//-- Quoc te
sqlPrepaid += " and a.transaction_code not in ( select Fee_code from " + Businessbp.executedb.owner + "NAB_FEE_CONFIG) "; //not Fee
sqlPrepaid += " and trunc(a.processing_date) >= trunc(to_date('" + dtFrom.Text.Trim() + "','mm/dd/yyyy')) ";
sqlPrepaid += " and trunc(a.processing_date) <= trunc(to_date('" + dtTo.Text.Trim() + "','mm/dd/yyyy')) ";
//credit
sqlCredit1 = "select 'Credit' as product_type,b.branch_code_dsa_code as branch_code, a.* ";
sqlCredit1 += " from ( ";
if (Frm_main.admin_flag)
{
sqlCredit1 += " select aa.card_number as card_no,aa.cr_account_nbr as account1,";
}
else
{
sqlCredit1 += " select substr(aa.card_number,1,4)||'xxxxxxxx'||substr(aa.card_number,-4) as card_no,substr(aa.cr_account_nbr,1,4)||'xxxxxxxx'||substr(aa.cr_account_nbr,-4) as account1,";
}
sqlCredit1 += " decode(aa.reversal_flag, '1', -aa.transaction_amount, aa.transaction_amount) txn_amt,aa.transaction_currency as txn_cur,";
sqlCredit1 += " decode(aa.reversal_flag, '1', -aa.billing_amount, aa.billing_amount) bill_amt,aa.billing_currency as bill_cur,";
sqlCredit1 += " '' as trace_no,aa.authorization_number as auth_no, 0 as surchage, to_char(aa.processing_date,'dd/mm/yyyy') as process_date";
sqlCredit1 += " from " + Businessbp.executedb.owner + "cr_current_trans aa";
sqlCredit1 += " where trunc(aa.processing_date) >= trunc(to_date('" + dtFrom.Text.Trim() + "','mm/dd/yyyy')) ";
sqlCredit1 += " and trunc(aa.processing_date) <= trunc(to_date('" + dtTo.Text.Trim() + "','mm/dd/yyyy')) ";
sqlCredit1 += " and exists (";
sqlCredit1 += " select 1 from " + Businessbp.executedb.owner + "transaction_hist cc";
sqlCredit1 += " where cc.microfilm_ref_number = aa.microfilm_ref_number";
sqlCredit1 += " and cc.origine_code = '0'";//VN
sqlCredit1 += " and trim(cc.acquirer_bank_code) = '970428'";
sqlCredit1 += " and trim(cc.issuer_bank_code) = '970428'";
sqlCredit1 += " and trim(cc.mcc) = '6011' ";//--ATM
sqlCredit1 += " and trim(cc.product_type) = '1'"; //--Credit
sqlCredit1 += " and cc.transaction_code not in (select transaction_code from " + Businessbp.executedb.owner + "nab_exception_trn) ";//
if (cbNoidia.Checked)
sqlCredit1 += " and substr(cc.card_number,1,1) = '9' ";//-- Noi dia
if (cbQuocte.Checked)
sqlCredit1 += " and substr(cc.card_number,1,1) = '5' ";//-- Quoc te
sqlCredit1 += " )";
sqlCredit1 += " and aa.transaction_code not in (select Fee_code from " + Businessbp.executedb.owner + "NAB_FEE_CONFIG)";
sqlCredit1 += " union all ";
if (Frm_main.admin_flag)
{
sqlCredit1 += " select dd.card_number as card_no,dd.cr_account_nbr as account1,";
}
else
{
sqlCredit1 += " select substr(dd.card_number,1,4)||'xxxxxxxx'||substr(dd.card_number,-4) as card_no,substr(dd.cr_account_nbr,1,4)||'xxxxxxxx'||substr(dd.cr_account_nbr,-4) as account1,";
}
sqlCredit1 += " decode(dd.reversal_flag, '1', -dd.transaction_amount, dd.transaction_amount) as txn_amt,dd.transaction_currency as txn_cur,";
sqlCredit1 += " decode(dd.reversal_flag, '1', -dd.billing_amount, dd.billing_amount) bill_amt, dd.billing_currency as bill_cur,";
sqlCredit1 += " '' as trace_no,dd.authorization_number as auth_no,0 as surchage,to_char(dd.processing_date,'dd/mm/yyyy') as process_date ";
sqlCredit1 += " from " + Businessbp.executedb.owner + "cr_hist_trans dd ";
sqlCredit1 += " where trunc(dd.processing_date) >= trunc(to_date('" + dtFrom.Text.Trim() + "','mm/dd/yyyy')) ";
sqlCredit1 += " and trunc(dd.processing_date) <= trunc(to_date('" + dtTo.Text.Trim() + "','mm/dd/yyyy')) ";
sqlCredit1 += " and exists (";
sqlCredit1 += " select 1 from " + Businessbp.executedb.owner + "transaction_hist cc";
sqlCredit1 += " where cc.microfilm_ref_number = dd.microfilm_ref_number";
sqlCredit1 += " and cc.origine_code = '0'";
sqlCredit1 += " and cc.acquirer_bank_code = '970428'";
sqlCredit1 += " and cc.issuer_bank_code = '970428'";
sqlCredit1 += " and cc.mcc = '6011' ";//--ATM
sqlCredit1 += " and cc.product_type = '1'"; //--Credit
sqlCredit1 += " and cc.transaction_code not in (select transaction_code from " + Businessbp.executedb.owner + "nab_exception_trn) ";//
if (cbNoidia.Checked)
sqlCredit1 += " and substr(cc.card_number,1,1) = '9' ";//-- Noi dia
if (cbQuocte.Checked)
sqlCredit1 += " and substr(cc.card_number,1,1) = '5' ";//-- Quoc te
sqlCredit1 += " )";
sqlCredit1 += " and dd.transaction_code not in (select Fee_code from " + Businessbp.executedb.owner + "NAB_FEE_CONFIG)";
sqlCredit1 += " ) a, ";
sqlCredit1 += " " + Businessbp.executedb.owner + "cp_card b, " + Businessbp.executedb.owner + "application c";
sqlCredit1 += " where account1 = c.card_number";
sqlCredit1 += " and b.source_application_no = c.application_no";
if (rbCredit.Checked)
{
sql = sqlCredit1 + " order by branch_code";
bb += " CREDIT ";
}
else
{
if (rbPrepaid.Checked)
{
sql = sqlPrepaid + " order by branch_code";
bb += " PREPAID ";
}
else
if (rbAllCard.Checked)
{
sql = sqlPrepaid + " union all " + sqlCredit1 + " order by branch_code";
bb += " CREDIT & PREPAID ";
}
}
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
if(cbNoidia.Checked)
bb += " NỘI ĐỊA ";
if(cbQuocte.Checked)
bb += " QUỐC TẾ ";
a.dt = dtt;
a.title = "GIAO DỊCH ATM (ON-US) ĐÃ XỬ LÝ CUỐI NGÀY" +bb;
a.thoiGian = "Từ ngày: " + dtFrom.Text + " đến ngày " + dtTo.Text;
a.ShowDialog();
}
#endregion R12
#region R15
//-- bao cao giao dich IPM file MC
if (comboBox1.SelectedIndex + 1 == 15)
{
Frm_Report a = new Frm_Report();
a.type = "MC_15";
string sql = "", err = "";
if (Frm_main.admin_flag)
{
sql = " select a.card_number card_no, b.transaction_wording descript ,to_char(a.transaction_date,'dd/mm/yyyy') trans_date,to_char(a.processing_date,'dd/mm/yyyy') proc_date, ";
}
else
{
sql = " select substr(a.card_number,1,4)||'xxxxxxxx'||substr(a.card_number,-4) card_no, b.transaction_wording descript ,to_char(a.transaction_date,'dd/mm/yyyy') trans_date,to_char(a.processing_date,'dd/mm/yyyy') proc_date, ";
}
sql += " decode(a.reversal_flag,'R',-a.transaction_amount,a.transaction_amount) trans_amt, a.transaction_currency trans_curr, ";
sql += " decode(a.reversal_flag,'R',-a.billing_amount,a.billing_amount) bill_amt, a.billing_currency bill_curr, ";
sql += " decode(a.reversal_flag,'R',-a.settlement_amount,a.settlement_amount) settle_amt, a.settlement_currency settle_curr ";
sql += " from " + Businessbp.executedb.owner + "transaction_hist a, " + Businessbp.executedb.owner + "trans_def_stand b ";
sql += " where a.transaction_code = b.transaction_code ";
sql += " and substr(a.card_number,1,6) in ('533147','524083','516995','528645') ";
sql += " and a.acquirer_bank_code <> '970428' ";
sql += " and a.transaction_code not in (select transaction_code from " + Businessbp.executedb.owner + "nab_exception_trn) ";
sql += " and a.transaction_code not in ('22','26','27') ";
sql += " and trunc(a.processing_date) >= trunc(to_date('" + dtFrom.Text.Trim() + "','mm/dd/yyyy')) ";
sql += " and trunc(a.processing_date) <= trunc(to_date('" + dtTo.Text.Trim() + "','mm/dd/yyyy')) ";
if (cb_author.SelectedIndex + 1 == 1)
{
sql += " and a.authorization_status in('0') ";
}
else if (cb_author.SelectedIndex + 1 == 2)
{
sql += " and a.authorization_status in('1') ";
}
else if (cb_author.SelectedIndex + 1 == 3)
{
sql += " and a.authorization_status in('2') ";
}
else if (cb_author.SelectedIndex + 1 == 4)
{
sql += " and a.authorization_status in('3') ";
}
else if (cb_author.SelectedIndex + 1 == 5)
{
sql += " and a.authorization_status in('4') ";
}
else if (cb_author.SelectedIndex + 1 == 6)
{
sql += " and a.authorization_status in('5') ";
}
else if (cb_author.SelectedIndex + 1 == 7)
{
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 if (cb_author.SelectedIndex + 1 == 8)
{
sql += " and a.authorization_status in('6') ";
}
else if (cb_author.SelectedIndex + 1 == 9)
{
sql += " and a.authorization_status in('7') ";
}
else if (cb_author.SelectedIndex + 1 == 10)
{
sql += " and a.authorization_status in('1','3','4') ";
}
else if (cb_author.SelectedIndex + 1 == 11)
{
sql += " and a.authorization_status in('0','2','5','6','7') ";
}
else if (cb_author.SelectedIndex + 1 == 12)
{
sql += " ";
}
sql += " order by trans_date, descript ";
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();
}
#endregion R15
}
public void LoadData()
{
string sql = "", err = "";
//Báo biểu PHT03
#region PHT03
if (rbPHT03.Checked==true)
{
Frm_Report a = new Frm_Report();
a.type = "PHT03";
sql = " select t.card_number, t.client_code, (t.title || ' ' || t.first_name || ' ' || t.family_name) cust_name, t.birth_date, "+
" (case (select product_code from "+Businessbp.executedb.owner+"product p where t.product_code = p.product_code) when '333' then t.branch_ref_number else (select cl.legal_id from "+Businessbp.executedb.owner+"client cl where t.client_code = cl.client_code) end) CMND,"+
" t.embossed_name card_name, t.encoded_name,"+
" (address1 || ' ' || address2 || ' ' || address3 || ' ' || address4) address, "+
" (select product_code || '-' || description from "+Businessbp.executedb.owner+"product p where t.product_code = p.product_code) product,"+
" (case t.basic_card_flag when '0' then 'P' else 'S' end) card_type, "+
" t.basic_card_number, t.expiry_date, t.batch_create_num, t.user_create, t.date_create, t.opening_date,"+
" cal.account_number, '0' || substr(cal.account_number,1,3)as TK_BranchCode,"+
" (select b1.branch_code || b1.branch_name from "+Businessbp.executedb.owner+"branch b1 where substr(b1.branch_code,2,3) = substr(cal.account_number,0,3) and b1.bank_code='970428') TK_BranchName, " +
" (select b.branch_code || b.branch_name from "+Businessbp.executedb.owner+"branch b where b.branch_code = t.branch_code and b.bank_code='970428') branch_name " +
" ,( select (select b.branch_code || b.branch_name from "+Businessbp.executedb.owner+"branch b where b.branch_code = otu.branch_code and b.bank_code='970428') from "+Businessbp.executedb.owner+"ot_tb_users otu where 'CRA_'||otu.usr_id=t.user_create) as TK_BranchName "+
" from "+Businessbp.executedb.owner+"card t, "+Businessbp.executedb.owner+"card_account_link cal "+
" where t.opening_date between to_date('" + dtFrom.Text + "', 'dd/MM/yyyy') " +
" and to_date('" + dtTo.Text + "', 'dd/MM/yyyy')" +
" and to_char(t.status_code) = '0'"+
" and t.card_number=cal.card_number "+
" order by t.card_number asc";
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
else
{
if (dtt.Rows.Count > 0)
{
a.dt = dtt;
a.thoiGian = "Từ ngày: " + dtFrom.Text + " đến ngày " + dtTo.Text;
a.ShowDialog();
}
else
{
MessageBox.Show("Không có dữ liệu!!!");
return;
}
}
}
#endregion PHT03
//Báo biểu PHT04
#region PHT04
if (rbPHT04.Checked == true)
{
Frm_Report a = new Frm_Report();
a.type = "PHT04";
sql = " select t.card_number, t.client_code, (t.title || ' ' || t.first_name || ' ' || t.family_name) cust_name, t.birth_date, " +
" (case (select product_code from "+Businessbp.executedb.owner+"product p where t.product_code = p.product_code) when '333' then t.branch_ref_number else (select cl.legal_id from "+Businessbp.executedb.owner+"client cl where t.client_code = cl.client_code) end) CMND," +
" t.embossed_name card_name, t.encoded_name," +
" (address1 || ' ' || address2 || ' ' || address3 || ' ' || address4) address, " +
" (select product_code || '-' || description from "+Businessbp.executedb.owner+"product p where t.product_code = p.product_code) product," +
" (case t.basic_card_flag when '0' then 'P' else 'S' end) card_type, " +
" t.basic_card_number, t.expiry_date, t.batch_create_num, t.user_create, t.date_create, t.opening_date," +
" cal.account_number, '0' || substr(cal.account_number,1,3)as TK_BranchCode," +
" (select b1.branch_code || b1.branch_name from "+Businessbp.executedb.owner+"branch b1 where substr(b1.branch_code,2,3) = substr(cal.account_number,0,3) and b1.bank_code='970428') TK_BranchName, " +
" (select b.branch_code || b.branch_name from "+Businessbp.executedb.owner+"branch b where b.branch_code = t.branch_code and b.bank_code='970428') branch_name " +
" ,( select (select b.branch_code || b.branch_name from "+Businessbp.executedb.owner+"branch b where b.branch_code = otu.branch_code and b.bank_code='970428') from "+Businessbp.executedb.owner+"ot_tb_users otu where 'CRA_'||otu.usr_id=t.user_create) as TK_BranchName " +
" from "+Businessbp.executedb.owner+"card t, "+Businessbp.executedb.owner+"card_account_link cal " +
" where t.opening_date between to_date('" + dtFrom.Text + "', 'dd/MM/yyyy') " +
" and to_date('" + dtTo.Text + "', 'dd/MM/yyyy')" +
" and to_char(t.status_code) = '0'" +
" and t.card_number=cal.card_number " +
" order by t.card_number asc";
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
else
{
if (dtt.Rows.Count > 0)
{
a.dt = dtt;
a.thoiGian = "Từ ngày: " + dtFrom.Text + " đến ngày " + dtTo.Text;
a.ShowDialog();
}
else
{
MessageBox.Show("Không có dữ liệu!!!");
return;
}
}
}
#endregion PHT04
//Báo biểu PHT05
#region PHT05
if (rbPHT05.Checked == true)
{
Frm_Report a = new Frm_Report();
a.type = "PHT05";
string select = "", from = "", where = "";
select = " select to_date(c.date_create, 'dd/MM/yy') as NGAY_PH," +
" (c.title || ' ' || c.first_name || ' ' || c.family_name) as HO_TEN, c.branch_code, b.branch_code || b.branch_name branch_name," +
" c.card_number as SO_THE, cl.legal_id as CMND, (cl.address1 || cl.address2 || cl.address3 || cl.address4) AS DIA_CHI, " +
" p.description AS SAN_PHAM, (case when (c.pin_production_count = 0) then 'N' else 'Y' end) IN_PIN," +
" '' AS HINH_THUC_PHAT_HANH, '' AS GHI_CHU, c.batch_create_num, c.user_create," +
" cal.account_number, '0' || substr(cal.account_number,0,3)as TK_BranchCode," +
" (select b1.branch_code || b1.branch_name from "+Businessbp.executedb.owner+"branch b1 where substr(b1.branch_code,2,3) = substr(cal.account_number,0,3) and b1.bank_code='970428') TK_BranchName," +
" ( select (select b.branch_code || b.branch_name from "+Businessbp.executedb.owner+"branch b where b.branch_code = otu.branch_code and b.bank_code='970428') from "+Businessbp.executedb.owner+"ot_tb_users otu where 'CRA_'||otu.usr_id=c.user_create) as user_BranchName";
from = " from "+Businessbp.executedb.owner+"card c, "+Businessbp.executedb.owner+"client cl, "+Businessbp.executedb.owner+"product p, "+Businessbp.executedb.owner+"branch b, "+Businessbp.executedb.owner+"card_account_link cal";
where = " where c.client_code = cl.client_code" +
" and c.product_code = p.product_code " +
" and c.action_code not in ('I', 'S', 'P')" +
" and to_char(c.status_code) = '0'" +
" and b.branch_code = c.branch_code " +
" and b.bank_code = '970428'" +
" and b.office_type='B'" +
" and c.product_code <> '333'" +
" and c.card_number=cal.card_number" +
" and trunc(c.date_create) between to_date('" + dtFrom.Text + "', 'dd/MM/yyyy') " +
" and to_date('" + dtTo.Text + "', 'dd/MM/yyyy')";
if (rbAllBranch.Checked == true)
{
sql = select + from + where;
a.NhanVien = "";
}
else
if (rbBranch.Checked == true)
{
where += " and b.branch_code='" + cbChiNhanh.SelectedValue + "'";
sql = select + from + where;
a.NhanVien = "";
}
else
if (rbHoiSo.Checked == true)
{
from += ", "+Businessbp.executedb.owner+"OT_TB_USERS u";
where += " and c.user_create ='CRA_'||u.usr_id" +
" and u.branch_code='0000'" +
" and substr(cal.account_number,4,7) not in ('0093377')";
where += " and cl.legal_id not in " +
" (" +
" select cl.legal_id as CMND " +
" from "+Businessbp.executedb.owner+"card c, "+Businessbp.executedb.owner+"client cl, "+Businessbp.executedb.owner+"product p, "+Businessbp.executedb.owner+"branch b, "+Businessbp.executedb.owner+"card_account_link cal, "+Businessbp.executedb.owner+"nab_card_issuance T " +
" where c.client_code = cl.client_code " +
" and c.product_code = p.product_code " +
" and c.action_code not in ('I', 'S', 'P') " +
" and to_char(c.status_code) = '0' " +
" and b.branch_code = c.branch_code " +
" and b.bank_code='970428'" +
" and c.product_code <> '333' " +
" and c.card_number=cal.card_number " +
" and trunc(c.date_create) between to_date('" + dtFrom.Text + "', 'dd/MM/yyyy') " +
" and to_date('" + dtTo.Text + "', 'dd/MM/yyyy')"+
" and T.CMND=cl.legal_id )";
sql = select + from + where;
a.NhanVien = "";
}
else
if (rbBoPhanKD.Checked == true)
{
from += ", "+Businessbp.executedb.owner+"nab_card_issuance T";
where += " and T.CMND=cl.legal_id";
sql = select + from + where;
//Chon nhan vien hoac nhom kinh doanh
if (cbNhanVien.Text == "-ALL-")
{
a.NhanVien = "";
}
else
{
if (cbNhanVien.Text == "NHOM POS")
{
a.NhanVien = "NHOM POS";
sql += " and (T.HO_TEN_NHAN_VIEN in ('NGUYEN THI THANH TRUC','DOAN VU LONG','NGUYEN THANH VINH','NGUYEN THI MY NHU','NGUYEN THANH TRU','BUI PHUONG HA','BUI XUAN HIEP'))";
}
else
{
if (cbNhanVien.Text == "NHOM THE 1")
{
a.NhanVien = "NHOM THE 1"; //"DO THI HUONG";
sql += " and (T.HO_TEN_NHAN_VIEN in ('DO THI HUONG','QUANG NHUT DIEM DIEM','NGUYEN TUAN QUANG'))";
}
else
if (cbNhanVien.Text == "NHOM THE 2")
{
a.NhanVien = "NHOM THE 2";// "LE VAN THANG";
sql += " and (T.HO_TEN_NHAN_VIEN in ('LE VAN THANG','NGUYEN THANH LONG','PHAM THI HUONG THUC','NGUYEN THI NHU Y'))";
}
else
if (cbNhanVien.Text != "")
{
a.NhanVien = cbNhanVien.Text;
sql += " and (upper(T.HO_TEN_NHAN_VIEN)='" + cbNhanVien.Text.ToUpper() + "')";
}
else
{
a.NhanVien = "";
}
}
}
//Order by
if (rbOrderBy_CardNumber.Checked == true)
sql += " order by c.card_number asc";
else
if (rbOrderby_CardHolder.Checked == true)
sql += " order by (c.title || ' ' || c.first_name || ' ' || c.family_name) asc";
}
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
else
{
if (dtt.Rows.Count > 0)
{
a.dt = dtt;
a.thoiGian = "Từ ngày: " + dtFrom.Text + " đến ngày " + dtTo.Text;
a.ShowDialog();
}
else
{
MessageBox.Show("Không có dữ liệu!!!");
return;
}
}
}
#endregion PHT05
//Báo biểu PHT17
#region PHT17
if (rbPHT17.Checked == true)
{
Frm_Report a = new Frm_Report();
a.type = "PHT17";
sql = "select cd.fcc_acc_brn MACHINHANH, br.branch_name TENCHINHANH,(cd.fcc_acc_brn || ' - ' || a.branch_name) CHINHANH ," +
" substr(cd.fcc_acc_no, 4, 7) MAKH, cus.customer_name1 TENKH, cus.unique_id_value CMND," +
" cus.address_line1 ||' ' || cus.address_line2 ||' ' || cus.address_line3 ||' ' || cus.address_line4 DIACHI," +
" cd.fcc_acc_no TK_FCC, cd.atm_acc_no TK_ATM," +
" cd.maker_dt_stamp NGAY_MAP,cd.maker_ID NGUOI_MAP,cd.checker_dt_stamp NGAY_DUYET,cd.checker_id NGUOI_DUYET," +
" cd.card_product, '' product," +
" (case (select count(ca.cust_ac_no) from STTM_CUST_ACCOUNT@Flexcube ca where ca.cust_ac_no=cd.fcc_acc_no) when 0 then 'Khong co TK nay' else '' end) GHICHU" +
" from swtm_card_details@Flexcube cd, Sttm_Branch@Flexcube br, sttm_customer@Flexcube cus,"+Businessbp.executedb.owner+"branch a" +
" where 1=1" +
" and cd.card_product is not null" +
" and cd.fcc_acc_brn=br.branch_code" +
//Dat them
" and substr(a.branch_code,2,3)=br.branch_code"+
" and b.bank_code='970428'" +
" and a.office_type='B'"+
" and substr(cd.fcc_acc_no, 4, 7)=cus.customer_no " +
" and trunc(cd.maker_dt_stamp) between to_date('" + dtFrom.Text + "', 'dd/MM/yyyy') " +
" and to_date('" + dtTo.Text + "', 'dd/MM/yyyy')" +
" order by cd.maker_dt_stamp asc";
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
else
{
if (dtt.Rows.Count > 0)
{
a.dt = dtt;
a.thoiGian = "Từ ngày: " + dtFrom.Text + " đến ngày " + dtTo.Text;
a.ShowDialog();
}
else
{
MessageBox.Show("Không có dữ liệu!!!");
return;
}
}
}
#endregion PHT17
}
private void button1_Click(object sender, EventArgs e)
{
string branch_code = "";
Frm_Report a = new Frm_Report();
a.type = "4";
if (cbBranch.Text != "" && cbBranch.Text != "-ALL-")
{
branch_code = cbBranch.SelectedValue.ToString();
//for (int i = 0; i < dt.Rows.Count; i++)
//{
// if (cbBranch.Text.ToUpper() == dt.Rows[i]["branch_name"].ToString().ToUpper())
// {
// branch_code = dt.Rows[i]["branch_code"].ToString().ToUpper();
// break;
// }
//}
}
string sql = "", err = "";
//sql = "select c.title || ' ' || c.first_name || ' ' || c.family_name as Full_name,c.client_code CIF,cl.legal_id as CMND,c.card_number as Card_number,cal.account_number" +
// " from "+Businessbp.executedb.owner+"card c, "+Businessbp.executedb.owner+"client cl, "+Businessbp.executedb.owner+"card_account_link cal,"+Businessbp.executedb.owner+"ot_tb_users otu" +
// " where c.client_code = cl.client_code" +
// //" and c.action_code not in ('I', 'S', 'P') "+
// " and 'CRA_'||otu.usr_id=c.user_create" +
// " and to_char(c.status_code) = '0'" +
// " and c.product_code <> '333'" +
// " and c.card_number=cal.card_number" +
// " and substr(cal.account_number,6,5)<>'93377' " + //Loai tk luong
// " and trunc(c.date_create) between to_date('" + dtFrom.Text + "', 'dd/MM/yyyy')" +
// " and to_date('" + dtTo.Text + "', 'dd/MM/yyyy')";
////"+Businessbp.executedb.owner+"ot_tb_users otu where 'CRA_'||otu.usr_id=t.user_create
//if (branch_code != "")
//{
// if (branch_code != "0000")
// sql += " and otu.branch_code ='" + branch_code + "'";
// else
// sql += " and c.branch_code ='" + branch_code + "'";
//}
//sql += " order by c.card_number asc,c.date_create asc";
// sql += " select * from (";
//sql += " select c.title || ' ' || c.first_name || ' ' || c.family_name as Full_name,c.client_code CIF,cl.legal_id as CMND,c.card_number as Card_number,cal.account_number";
//sql += " from " + Businessbp.executedb.owner + "card c, " + Businessbp.executedb.owner + "client cl, " + Businessbp.executedb.owner + "card_account_link cal," + Businessbp.executedb.owner + "ot_tb_users otu";
//sql += " where c.client_code = cl.client_code";
////sql += " -- // and c.action_code not in ('I', 'S', 'P') ";
//sql += " and ('CRA_'||otu.usr_id=c.user_create or otu.usr_id=c.user_create) ";
//sql += " and to_char(c.status_code) = '0'";
//sql += " and c.product_code <> '333'";
//sql += " and c.card_number=cal.card_number";
//sql += " and c.branch_code='0000'";
////sql += " -- and trim(c.card_number) not in (select trim(card_number) from " + Businessbp.executedb.owner + "cp_card_prepaid a)";
//sql += " and substr(cal.account_number,6,5)<>'93377'";// -- //Loai tk luong";
// sql += " and trunc(c.date_create) >= to_date('" + dtFrom.Text + "', 'dd/MM/yyyy')";
// sql += " and trunc(c.date_create) <= to_date('" + dtTo.Text + "', 'dd/MM/yyyy')";
////sql += " --order by c.card_number asc,c.date_create asc";
//sql += " union all";
////sql += " --select card_number from "+Businessbp.executedb.owner+"cp_card_prepaid a";
//sql += " select c.title || ' ' || c.first_name || ' ' || c.family_name as Full_name,c.client_code CIF,cl.legal_id as CMND,c.card_number as Card_number,cal.account_number";
//sql += " from " + Businessbp.executedb.owner + "card c, " + Businessbp.executedb.owner + "client cl, " + Businessbp.executedb.owner + "card_account_link cal," + Businessbp.executedb.owner + "cp_card_prepaid b";
//sql += " where c.card_number=b.card_number";
//sql += " and c.client_code = cl.client_code";
//sql += " and to_char(c.status_code) = '0'";
//sql += " and c.product_code <> '333'";
//sql += " and c.card_number=cal.card_number";
//sql += " and substr(cal.account_number,6,5)<>'93377'";
//sql += " and trim(b.branch_code)='0000' ";
//sql += " and trunc(c.date_create) >= to_date('" + dtFrom.Text + "', 'dd/MM/yyyy')";
//sql += " and trunc(c.date_create) <= to_date('" + dtTo.Text + "', 'dd/MM/yyyy')";
//sql += " union all ";
//// Credit
//sql += " select c.title || ' ' || trim(c.first_name) || ' ' || trim(c.family_name) as Full_name,c.client_code CIF,cl.legal_id as CMND,c.card_number as Card_number,cra.cr_account_nbr as account_number";//--,cal.account_number";
//sql += " from " + Businessbp.executedb.owner + "card c, " + Businessbp.executedb.owner + "client cl," + Businessbp.executedb.owner + "application app," + Businessbp.executedb.owner + "cp_card cr," + Businessbp.executedb.owner + "cr_card_balance_details cra";//--, "+Businessbp.executedb.owner+"card_account_link cal,"+Businessbp.executedb.owner+"cp_card_prepaid b";
//sql += " where c.card_number=app.card_number";
//sql += " and c.client_code = cl.client_code";
//sql += " and app.application_no=cr.source_application_no";
//sql += " and c.card_number=cra.card_number";
//sql += " and to_char(c.status_code) = '0'";
//sql += " and c.product_code <> '333'";
//sql += " and trim(cr.branch_code_dsa_code)='0000'";
//sql += " and trunc(c.date_create) >= to_date('" + dtFrom.Text + "', 'dd/MM/yyyy')";
//sql += " and trunc(c.date_create) <= to_date('" + dtTo.Text + "', 'dd/MM/yyyy')";
//sql += " )";
//sql += " order by card_number";
sql += " select * from (";
sql += " select c.title || ' ' || c.first_name || ' ' || c.family_name as Full_name,c.client_code CIF,cl.legal_id as CMND";
sql += " ,substr(c.card_number,1,4)||'********'||substr(c.card_number,13) as Card_number";
sql += " ,substr(cal.account_number,1,4)||'********'||substr(cal.account_number,13) as account_number ";
sql += " from " + Businessbp.executedb.owner + "card c," + Businessbp.executedb.owner + "client cl," + Businessbp.executedb.owner + "card_account_link cal," + Businessbp.executedb.owner + "cp_card_prepaid cpp";
sql += " where c.client_code=cl.client_code";
sql += " and to_char(c.status_code) = '0' ";
sql += " and c.product_code <> '333' ";
sql += " and c.card_number=cal.card_number ";
sql += " and substr(cal.account_number,6,5)<>'93377'";
sql += " and trunc(c.date_create) >= to_date('" + dtFrom.Text + "', 'dd/MM/yyyy') ";
sql += " and trunc(c.date_create) <= to_date('" + dtTo.Text + "', 'dd/MM/yyyy') ";
sql += " and (trim(c.card_number)=trim(cpp.card_number) or trim(c.account1) = trim(cpp.card_number) or cpp.card_number in (select distinct h.previous_card_number from " + Businessbp.executedb.owner + "card_replace h where h.card_number=c.card_number)) ";
if (branch_code != "" && branch_code != "9999" && cbBranch.Text.Trim() != "ALL")
sql += " and trim(cpp.branch_code)=trim('"+cbBranch.SelectedValue+"')";
sql += " union all ";
sql += " select c.embossed_name as Full_name,c.client_code CIF,cpc.legal_id as CMND";
sql += " ,substr(c.card_number,1,4)||'********'||substr(c.card_number,13) as Card_number";
sql += " ,substr(ac.cr_account_nbr,1,4)||'********'||substr(ac.cr_account_nbr,13) account_number ";
sql += " from " + Businessbp.executedb.owner + "card c," + Businessbp.executedb.owner + "application ap," + Businessbp.executedb.owner + "account ac," + Businessbp.executedb.owner + "cp_card cpc";
sql += " where 1=1";
sql += " and (trim(c.card_number)=trim(ap.card_number) or trim(c.branch_ref_number) = trim(ap.source_application_no) or trim(c.cr_account_nbr)=trim(ap.card_number))";
sql += " and c.cr_account_nbr=ac.cr_account_nbr";
sql += " and to_char(c.status_code) = '0' ";
sql += " and c.product_code <> '333' ";
sql += " and trim(ap.application_no)=trim(cpc.source_application_no)";
sql += " and trunc(c.date_create) >= to_date('" + dtFrom.Text + "', 'dd/MM/yyyy') ";
sql += " and trunc(c.date_create) <= to_date('" + dtTo.Text + "', 'dd/MM/yyyy') ";
if (branch_code != "" && branch_code != "9999" && cbBranch.Text.Trim() != "ALL")
// sql += " and otu.branch_code ='" + branch_code + "'";
sql += " and trim(cpc.branch_code_dsa_code)=trim('" + cbBranch.SelectedValue + "')";
sql += " )";
sql += " order by card_number";
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
a.dt = dtt;
a.title = "THÔNG TIN THẺ";
a.ShowDialog();
}
private void button5_Click(object sender, EventArgs e)
{
Frm_Report a = new Frm_Report();
//frmShowReport a = new frmShowReport();
a.thoiGian = "Từ " + dtFrom.Text + " đến " + dtTo.Text;
a.type = "BC_TTDL";
string sql = "", err = "";
string sqlSelect = "", sqlFrom = "", sqlwhere = "";
sqlSelect = "select c.merchant_number,d.company_name merchant_name,d.account1 account," +
"b.card_number,b.transaction_date,b.trace_audit_number trace_number,b.authorization_number," +
"decode(substr(lpad(b.processing_code,6,'0'),1,2),'00','Sale/Purchase','01','Cash Advance','02',decode(b.function_code,'401','Adjustment','200','Void'),'20','Refund','31','Balance Inquiry') txn_type," +
"trunc(b.settlement_date) settlement_date, to_number(to_char(a.transaction_amount)) as transaction_amount, a.transaction_currency,to_number(to_char(a.discount_amount)) as discount_amount,to_number(to_char(a.net_amount)) as net_amount, " +
"to_number('') as Discount_amount_correct, to_number('') as VAT_amount_correct ";
sqlFrom = " from " + Businessbp.executedb.owner + "merchant_stmt_txn_details a, " + Businessbp.executedb.owner + "transaction_hist b, " + Businessbp.executedb.owner + "merchant_stmt_details c, " + Businessbp.executedb.owner + "merchant d ";
sqlwhere= " where a.outlet_number = b.outlet_number " +
" and a.microfilm_ref_number = b.microfilm_ref_number " +
" and a.sequence_number = b.sequence_number " +
" and a.transaction_code = b.transaction_code " +
" and a.statement_no = c.statement_no " +
" and c.merchant_number = d.merchant_number " +
" and trunc(a.date_create) >= to_date('" + dtFrom.Text.Trim() + "','mm/dd/yyyy') " +
" and trunc(a.date_create) <= to_date('" + dtTo.Text.Trim() + "','mm/dd/yyyy')";
if (rbONUS.Checked)
{
if (rbDebit.Checked)
{
sqlFrom += "," + Businessbp.executedb.owner + "card e";// +Businessbp.executedb.owner + "nab_phanloaisp";
sqlwhere += " and trim(b.issuer_bank_code)='970428'";
sqlwhere += " and e.card_number= b.card_number" +
" and e.product_code in (select product_code from " + Businessbp.executedb.owner + "nab_phanloaisp f where f.bank_code='970428' and f.type='D')";
}
else if (rbPrepaid.Checked)
{
sqlFrom += "," + Businessbp.executedb.owner + "card e";// +Businessbp.executedb.owner + "nab_phanloaisp";
sqlwhere += " and trim(b.issuer_bank_code)='970428'";
sqlwhere += " and c.card_number= b.card_number" +
" and e.product_code in (select product_code from " + Businessbp.executedb.owner + "nab_phanloaisp f where f.bank_code='970428' and f.type='P')";
}
else if (rbCredit.Checked)
{
sqlFrom += "," + Businessbp.executedb.owner + "card e";// +Businessbp.executedb.owner + "nab_phanloaisp";
sqlwhere += " and trim(b.issuer_bank_code)='970428'";
sqlwhere += " and e.card_number= b.card_number" +
" and e.product_code in (select product_code from " + Businessbp.executedb.owner + "nab_phanloaisp f where f.bank_code='970428' and f.type='C')";
}
else
{
sqlwhere += " and trim(b.issuer_bank_code)='970428'";
}
}
else if (rbOFFUS.Checked)
{
sqlwhere += " and trim(b.issuer_bank_code) <> '970428'";
}
else
{
}
sql = sqlSelect + sqlFrom + sqlwhere;
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
else
{
int txn_fee = 0;
int txn_fee1 = 0;
int vat = 0;
for (int i = 0; i < dtt.Rows.Count; i++)
{
string checkVAT = "";
txn_fee = Convert.ToInt32(dtt.Rows[i]["discount_amount"].ToString().Replace("-", ""));
txn_fee1 = (int)Math.Round((txn_fee / 1.1), 0);
checkVAT = txn_fee1.ToString();
if (checkVAT.Substring(checkVAT.Length - 1, 1) == "5")
{
vat = (int)(Math.Round((txn_fee1 * 0.1), 0)) + 1;
}
else
vat = (int)(Math.Round((txn_fee1 * 0.1), 0));
dtt.Rows[i]["Discount_amount_correct"] = txn_fee1;
dtt.Rows[i]["VAT_amount_correct"] = vat;
}
a.dt = dtt;
a.title = "BÁO CÁO TỔNG HỢP THANH TOÁN ĐẠI LÝ";
a.ShowDialog();
//dg1.DataSource = dtt;
}
}
private void btn_view_Click(object sender, EventArgs e)
{
Frm_Report a = new Frm_Report();
a.type = "MC_Reconciliation_ATM_Pending";
string sql = "", dk = "", err = "";
//định dạng ngày tháng trong bảng nab_reconsilaton_acqmc_details khác nên phải tách riêng ngày tháng và giờ phút ra
//string fromdate = "to_date('" + dtFrom.Text.Trim() + "','dd/mm/yyyy')";
//string fromtime = "to_date('" + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','hh24:mi:ss')";
//string todate = "to_date('" + dtTo.Text.Trim() + "','dd/mm/yyyy')";
//string totime = "to_date('" + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','hh24:mi:ss')";
//string from_date = "to_date('" + dtFrom.Text.Trim() + " " + tbFh.Text.Trim() + ":" + tbFm.Text.Trim() + ":" + tbFs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')";
//string to_date = "to_date('" + dtTo.Text.Trim() + " " + tbTh.Text.Trim() + ":" + tbTm.Text.Trim() + ":" + tbTs.Text.Trim() + "','dd/mm/yyyy hh24:mi:ss')";
string from_date = "to_date('" + dtFrom.Text.Trim() + "','dd/mm/yyyy')";
string to_date = "to_date('" + dtTo.Text.Trim() + "','dd/mm/yyyy')";
sql += " select au_ac.card_number, au_ac.reference_number, au_ac.trace_audit_number, au_ac.transaction_amount,au_ac.transaction_currency, ";
sql += " to_char(trunc(au_ac.transaction_local_date),'dd/mm/yyyy') Transaction_Date ";
sql += " from " + Businessbp.executedb.owner + "autho_activity au_ac ";
sql += " where 1=1 ";
sql += " and ( au_ac.trace_audit_number,au_ac.reference_number,au_ac.card_number, ";
sql += " au_ac.transaction_amount, ";
sql += " to_char(trunc(au_ac.transaction_local_date),'mmddyy') ) not in ( ";
sql += " select rad.trace_number,rad.reference_number,rad.card_number, ";
sql += " rad.completed_amt_trans_local,rad.transaction_date ";
sql += " from " + Businessbp.executedb.owner + "nab_reconsilaton_acqmc_details rad ";
//sql += " where 1=1 ";
//sql += " and to_date(rad.transaction_date,'mmddyy') >=" + fromdate;
//sql += " and to_date(rad.transaction_time,'hh24miss') >=" + fromtime;
//sql += " and to_date(rad.transaction_date,'mmddyy') <=" + todate;
//sql += " and to_date(rad.transaction_time,'hh24miss') <=" + totime;
sql += " ) ";
sql += " and trunc(au_ac.transaction_local_date) >=" + from_date;
sql += " and trunc(au_ac.transaction_local_date) <=" + to_date;
sql += " and au_ac.code_action ='000' ";
sql += " and au_ac.issuing_bank <>'970428' ";
sql += " and au_ac.acquirer_institution_code ='970428' ";
sql += " and au_ac.card_acceptor_activity='6011' ";
sql += " and substr(trim(au_ac.card_number),1,1) in ('5','6') ";
sql += " and au_ac.network_code ='02' ";
sql += " and substr(au_ac.processing_code,1,2)='17' ";
if (rbVN.Checked)
{
dk += " and trim(au_ac.acquiring_country_code) = '704' ";
}
if (rbNuocNgoai.Checked)
{
dk += " and trim(au_ac.acquiring_country_code) <> '704' ";
}
//loai tien
if (cb_transaction_curr.Text.Trim() != "-ALL-")
{
dk += " and au_ac.transaction_currency = '" + cb_transaction_curr.Text.Trim() + "'";
}
sql += dk;
sql += " order by au_ac.transaction_local_date desc ";
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
a.dt = dtt;
a.title = "REPORT MASTERCARD RECONCILIATION PENDING";
a.thoiGian = "Từ ngày: " + dtFrom.Text + " đến ngày " + dtTo.Text;
a.ShowDialog();
//MessageBox.Show(from_date);
}
private void bt_print_Click(object sender, EventArgs e)
{
Frm_Report a = new Frm_Report();
string from_date = "", to_date = "";
from_date = Frm_MC_Chargeback.gldtFrom;
to_date = Frm_MC_Chargeback.gldtTo;
Ref_no_2nd = tbRef_no.Text;
string sql = "", err = "";
#region arbitration 2nd presentment
if (rbArbitration.Checked == true)
{
Frm_MC_Arbitration_2nd_Presentment b = new Frm_MC_Arbitration_2nd_Presentment();
b.Show();
}
#endregion arbitration 2nd presentment
#region reversal 2nd presentment
if (rbReversal2nd.Checked == true)
{
if (Frm_main.admin_flag)
{
sql = " select a.microfilm_ref_number as Ref_no,a.card_number as Card_no,a.card_account_number,a.cr_account_number,";
}
else
{
sql = " select a.microfilm_ref_number as Ref_no, substr(a.card_number,1,4)||'xxxxxxxx'||substr(a.card_number,-4) as Card_no, substr(a.card_account_number,1,4)||'xxxxxxxx'||substr(a.card_account_number,-4) card_account_number,substr(a.cr_account_number,1,4)||'xxxxxxxx'||substr(a.cr_account_number,-4) cr_account_number,";
}
sql += "a.trace_audit_number as Trace_no,a.authorization_number as Auth_no,a.transaction_date as Trans_date,";
sql += "a.transaction_amount as Trans_amt,a.transaction_currency as Trans_cur,a.billing_amount as Billing_amt,";
sql += "a.billing_currency as Bill_cur,a.settlement_amount as Settle_amt,a.settlement_currency as Settle_cur,";
sql += "a.function_code as Func_code, (select transaction_wording from " + Businessbp.executedb.owner + "trans_def_stand where transaction_code = a.transaction_code) Trans_code,";
sql += "a.chargeback_status,a.reversal_flag,a.reversal_reason_code";
sql += " from " + Businessbp.executedb.owner + "transaction_hist a";
sql += " where a.function_code = 206";
sql += " and a.microfilm_ref_number = '" +Ref_no_2nd+ "'";
sql += " and a.reversal_flag = 'R'";
sql += " and a.issuer_bank_code = '970428'";
sql += " and a.acquirer_bank_code <> '970428'";
sql += " and trunc(a.date_create) >= trunc(to_date('" + from_date + "','mm/dd/yyyy'))";
sql += " and trunc(a.date_create) <= trunc(to_date('" + to_date + "','mm/dd/yyyy'))";
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
else
{
if (dtt.Rows.Count == 0)
{
MessageBox.Show("No data found!!!");
return;
}
else
{
a.type = "MC_Reversal_2nd_Presentment";
a.thoiGian = "Từ ngày: " + from_date + " đến ngày " + to_date;
a.title = "DANH SÁCH GIAO DỊCH REVERSAL SECOND PRESNETMENT";
a.dt = dtt;
a.ShowDialog();
}
}
}
#endregion reversal 2nd presentment
}
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 "+Businessbp.executedb.owner+"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 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)
{
type = "ACCOUNTING_THAUCHI";
file_ID = "1";
a.title = "BÁO CÁO HẠCH TOÁN THẤU CHI";
}
else if (cbType.SelectedIndex + 1 == 2)
{
type = "ACCOUNTING_TRANSACTION";
file_ID = "2";
a.title ="BÁO CÁO HẠCH TOÁN GIAO DỊCH";
}
else if (cbType.SelectedIndex + 1 == 3)
{
type = "ACCOUNTING_DCCR";
file_ID = "3";
a.title = "BÁO CÁO HẠCH TOÁN ĐIỀU CHỈNH CREDIT";
}
else if (cbType.SelectedIndex + 1 == 4)
{
type = "ACCOUNTING_DCDEBIT";
file_ID = "4";
a.title = "BÁO CÁO HẠCH TOÁN ĐIỀU CHỈNH DEBIT";
}
else if (cbType.SelectedIndex + 1 == 5)
{ type = "ACCOUNTING_PREPAID_INTEREST";
file_ID = "1";
a.title = "BÁO CÁO HẠCH TOÁN LÃI PREPAID";
}
sql = "";
sql += " select b.branch_code||'-'||b.branch_name as Branch,to_char(a.date_proccessing,'dd/mm/yyyy') date_process";
if (Frm_main.admin_flag)
{
sql += " ,a.account_number,a.amount_accounting,a.dr_cr,a.description,'' Ghi_Chu";
}
else
{
sql += " ,substr(a.account_number,1,4)||'xxxxxxxx'||substr(a.account_number,-4) account_number,a.amount_accounting,a.dr_cr,a.description,'' Ghi_Chu";
}
sql += " from " + Businessbp.executedb.owner + "nab_accounting a," + Businessbp.executedb.owner + "branch b";
sql += " where trim(a.account_branch)=substr(trim(b.branch_code),2,3)";
sql += " and trim(b.bank_code)='970428'";
sql += " and b.office_type='B'";
sql += " and a.file_id='" + file_ID + "'";
if (file_ID == "1")
{
if (type == "ACCOUNTING_PREPAID_INTEREST")
sql += " and a.accounting_type='" + type + "'";
else
{
sql += " and a.accounting_type <>'ACCOUNTING_PREPAID_INTEREST'";
}
}
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 += " order by a.date_proccessing 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 button5_Click(object sender, EventArgs e)
{
Frm_Report a = new Frm_Report();
a.type = "MC_THANH_TOAN_FILE";
a.title = "MASTER PAYMENT";
string sql = "", err = "";
sql = "select (substr(a.file_id,4,6)||' - '||a.file_id) file_id,a.settlement_currency,a.type";
sql += " ,decode(settlement_currency,'704',round(billing_amount_electra),billing_amount_electra) as billing_amount_electra";
sql += " ,decode(settlement_currency,'704',round(billing_amount_flexcube),billing_amount_flexcube) as billing_amount_flexcube";
sql += " ,decode(settlement_currency,'704',round(settlement_amount),settlement_amount) as settlement_amount";
sql += " ,decode(settlement_currency,'704',round(fee_phai_thu),fee_phai_thu) as fee_phai_thu";
sql += " ,decode(settlement_currency,'704',round(fee_phai_tra),fee_phai_tra) as fee_phai_tra";
sql += " ,decode(nvl(a.ACTION_MASTERCARD,'0'),'0',decode(nvl(a.ACTION_TTT,'0'),'2','Tam treo KT-TH','1','Tam treo TTT','Chua hach toan'),'Da thanh toan') as ACTION";
//sql += " ,decode(nvl(a.ACTION_MASTERCARD,'0'),'0','Chua thanh toan','Da thanh toan') as ACTION_MASTERCARD";
sql += " from "+Businessbp.executedb.owner+"nab_clearning_mastercard a";
sql += " where 1=1";
if (rbDaThanhToan.Checked)
sql += " and nvl(a.ACTION_MASTERCARD,'0') = '1'";
if (rbChuaThanhToan.Checked)
{
sql += " and nvl(a.ACTION_MASTERCARD,'0') = '0'";
if (rbChuaHachToan.Checked)
sql += " and nvl(ACTION_TTT,'0')='0'";
else if (rbKTTH.Checked)
sql += " and nvl(ACTION_TTT,'0')='2'";
else if (rbTTTtamTreo.Checked)
sql += " and nvl(ACTION_TTT,'0')='1'";
}
if (tbFileID.Text != "")
sql += " and substr(a.file_id,4,6) in ("+tbFileID.Text.Trim()+")";
if (tbCCY.Text.Trim() != "")
sql += " and settlement_currency='" + tbCCY.Text.Trim() + "'";
sql += " order by file_id";
DataTable dt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
else
{
a.dt = dt;
a.Show();
}
}
private void button1_Click(object sender, EventArgs e)
{
Frm_Report a = new Frm_Report();
string sql = "", err = "", ref_no = "", from = "", to = "";
ref_no = tbArbitration_Ref_no.Text;
from = dtFrom.Text.Trim();
to = dtTo.Text.Trim();
#region Da Reversal Chargeback 1st Presentment
if (rbArbitration_DaReversal.Checked == true)
{
if (ref_no !="")
{
if (Frm_main.admin_flag)
{
sql = " select c.microfilm_ref_number as Ref_no, c.card_number as Card_no, c.chargeback_type,c.amount as Amt, c.currency_code as cur_code,";
}
else
{
sql = " select c.microfilm_ref_number as Ref_no, substr(c.card_number,1,4)||'xxxxxxxx'||substr(c.card_number,-4) as Card_no, c.chargeback_type,c.amount as Amt, c.currency_code as cur_code,";
}
sql += " c.source_amount as source_amt,c.source_currency_code as Source_cur,";
sql += " decode(c.documentation_indicator,0,'Documentation not required',1,'Documentation not provided',2,'Documentation follows') Doc_Indicator,";
sql += " c.reason_code,c.indicator_status,c.doc_received,cc.std_delay TongThoiGian,";
sql += " (cc.std_delay - (trunc(sysdate)- c.chargeback_date)) pending ";
sql += " from " + Businessbp.executedb.owner + "chargeback c, " + Businessbp.executedb.owner + "ch_reason_code cc";
sql += " where c.microfilm_ref_number in (select tr.microfilm_ref_number";
sql += " from " + Businessbp.executedb.owner + "transaction_hist tr";
sql += " where tr.function_code = '206'";
sql += " and tr.microfilm_ref_number = '" + ref_no + "'";
sql += " and tr.chargeback_status = '1'";
sql += " and tr.issuer_bank_code = '970428'";
sql += " and tr.acquirer_bank_code <> '970428'";
sql += " and trunc(tr.date_create) >= trunc(to_date('" + from + "','mm/dd/yyyy'))";
sql += " and trunc(tr.date_create) <= trunc(to_date('" + to + "','mm/dd/yyyy')))";
sql += " and c.indicator_status = '3'";
sql += " and trunc(c.date_create) >= trunc(to_date('" + from + "','mm/dd/yyyy'))";
sql += " and trunc(c.date_create) <= trunc(to_date('" + to + "','mm/dd/yyyy'))";
sql += " and c.reason_code = cc.reason_code";
}
else {
if (Frm_main.admin_flag)
{
sql = " select c.microfilm_ref_number as Ref_no, c.card_number as Card_no, c.chargeback_type,c.amount as Amt, c.currency_code as cur_code,";
}
else
{
sql = " select c.microfilm_ref_number as Ref_no, substr(c.card_number,1,4)||'xxxxxxxx'||substr(c.card_number,-4) as Card_no, c.chargeback_type,c.amount as Amt, c.currency_code as cur_code,";
}
sql += " c.source_amount as source_amt,c.source_currency_code as Source_cur,";
sql += " decode(c.documentation_indicator,0,'Documentation not required',1,'Documentation not provided',2,'Documentation follows') Doc_Indicator,";
sql += " c.reason_code,c.indicator_status,c.doc_received,cc.std_delay TongThoiGian,";
sql += " (cc.std_delay - (trunc(sysdate)- c.chargeback_date)) pending ";
sql += " from " + Businessbp.executedb.owner + "chargeback c, " + Businessbp.executedb.owner + "ch_reason_code cc";
sql += " where c.microfilm_ref_number in (select tr.microfilm_ref_number";
sql += " from " + Businessbp.executedb.owner + "transaction_hist tr";
sql += " where tr.function_code = '206'";
sql += " and tr.chargeback_status = '1'";
sql += " and tr.issuer_bank_code = '970428'";
sql += " and tr.acquirer_bank_code <> '970428'";
sql += " and trunc(tr.date_create) >= trunc(to_date('" + from + "','mm/dd/yyyy'))";
sql += " and trunc(tr.date_create) <= trunc(to_date('" + to + "','mm/dd/yyyy')))";
sql += " and c.indicator_status = '3'";
sql += " and trunc(c.date_create) >= trunc(to_date('" + from + "','mm/dd/yyyy'))";
sql += " and trunc(c.date_create) <= trunc(to_date('" + to + "','mm/dd/yyyy'))";
sql += " and c.reason_code = cc.reason_code";
}
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
else
{
if (dtt.Rows.Count == 0)
{
MessageBox.Show("No data found!!!");
return;
}
else
{
a.type = "MC_Arbitration_Chargeback";
a.thoiGian = "Từ ngày: " + from + " đến ngày " + to;
a.title = "DANH SÁCH GIAO DỊCH ARBITRATION CHARGEBACK ĐÃ REVERSAL";
a.dt = dtt;
a.ShowDialog();
}
}
}
#endregion Da Reversal Chargeback 1st Presentment
#region Chua Tra Loi Arbitration Chargeback
if (rbArbitration_ChuaNhanTL.Checked == true)
{
if (ref_no != "")
{
if (Frm_main.admin_flag)
{
sql = " select c.microfilm_ref_number as Ref_no, c.card_number as Card_no, c.chargeback_type,c.amount as Amt, c.currency_code as cur_code,";
}
else
{
sql = " select c.microfilm_ref_number as Ref_no, substr(c.card_number,1,4)||'xxxxxxxx'||substr(c.card_number,-4) as Card_no, c.chargeback_type,c.amount as Amt, c.currency_code as cur_code,";
}
sql += " c.source_amount as source_amt,c.source_currency_code as Source_cur,";
sql += " decode(c.documentation_indicator,0,'Documentation not required',1,'Documentation not provided',2,'Documentation follows') Doc_Indicator,";
sql += " c.reason_code,c.indicator_status,c.doc_received,cc.std_delay TongThoiGian,";
sql += " (cc.std_delay - (trunc(sysdate)- c.chargeback_date)) pending ";
sql += " from " + Businessbp.executedb.owner + "chargeback c, " + Businessbp.executedb.owner + "ch_reason_code cc";
sql += " where c.microfilm_ref_number in (select tr.microfilm_ref_number";
sql += " from " + Businessbp.executedb.owner + "transaction_hist tr";
sql += " where tr.function_code = '206'";
sql += " and tr.microfilm_ref_number = '" + ref_no + "'";
sql += " and tr.chargeback_status = '1'";
sql += " and tr.issuer_bank_code = '970428'";
sql += " and tr.acquirer_bank_code <> '970428'";
sql += " and trunc(tr.date_create) >= trunc(to_date('" + from + "','mm/dd/yyyy'))";
sql += " and trunc(tr.date_create) <= trunc(to_date('" + to + "','mm/dd/yyyy')))";
sql += " and c.indicator_status = '2'";
sql += " and trunc(c.date_create) >= trunc(to_date('" + from + "','mm/dd/yyyy'))";
sql += " and trunc(c.date_create) <= trunc(to_date('" + to + "','mm/dd/yyyy'))";
sql += " and c.reason_code = cc.reason_code ";
sql += " and (cc.std_delay - (trunc(sysdate)- c.chargeback_date)) > 0";
}
else
{
if (Frm_main.admin_flag)
{
sql = " select c.microfilm_ref_number as Ref_no, c.card_number as Card_no, c.chargeback_type,c.amount as Amt, c.currency_code as cur_code,";
}
else
{
sql = " select c.microfilm_ref_number as Ref_no, substr(c.card_number,1,4)||'xxxxxxxx'||substr(c.card_number,-4) as Card_no, c.chargeback_type,c.amount as Amt, c.currency_code as cur_code,";
}
sql += " c.source_amount as source_amt,c.source_currency_code as Source_cur,";
sql += " decode(c.documentation_indicator,0,'Documentation not required',1,'Documentation not provided',2,'Documentation follows') Doc_Indicator,";
sql += " c.reason_code,c.indicator_status,c.doc_received,cc.std_delay TongThoiGian,";
sql += " (cc.std_delay - (trunc(sysdate)- c.chargeback_date)) pending ";
sql += " from " + Businessbp.executedb.owner + "chargeback c, " + Businessbp.executedb.owner + "ch_reason_code cc";
sql += " where c.microfilm_ref_number in (select tr.microfilm_ref_number";
sql += " from " + Businessbp.executedb.owner + "transaction_hist tr";
sql += " where tr.function_code = '206'";
sql += " and tr.chargeback_status = '1'";
sql += " and tr.issuer_bank_code = '970428'";
sql += " and tr.acquirer_bank_code <> '970428'";
sql += " and trunc(tr.date_create) >= trunc(to_date('" + from + "','mm/dd/yyyy'))";
sql += " and trunc(tr.date_create) <= trunc(to_date('" + to + "','mm/dd/yyyy')))";
sql += " and c.indicator_status = '2'";
sql += " and trunc(c.date_create) >= trunc(to_date('" + from + "','mm/dd/yyyy'))";
sql += " and trunc(c.date_create) <= trunc(to_date('" + to + "','mm/dd/yyyy'))";
sql += " and c.reason_code = cc.reason_code ";
sql += " and (cc.std_delay - (trunc(sysdate)- c.chargeback_date)) > 0";
}
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
else
{
if (dtt.Rows.Count == 0)
{
MessageBox.Show("No data found!!!");
return;
}
else
{
a.type = "MC_Arbitration_Chargeback";
a.thoiGian = "Từ ngày: " + from + " đến ngày " + to;
a.title = "DANH SÁCH GIAO DỊCH ARBITRATION CHARGEBACK CHƯA TRẢ LỜI";
a.dt = dtt;
a.ShowDialog();
}
}
}
#endregion Chua Tra Loi Arbitration Chargeback
}
private void bt_report_Click(object sender, EventArgs e)
{
#region 2014-06-23 (1) (LUAN)
Frm_Report a = new Frm_Report();
a.type = "PHI_CHIET_KHAU_DAI_LY";
string sql = "", err = "";
sql += " select substr(r.tai_khoan_dai_ly,1,4)||'xxxxxxxx'||substr(r.tai_khoan_dai_ly,-4) tai_khoan_dai_ly, ";
sql += " r.company_name, ";
sql += " r.phi, ";
sql += " r.phi_chua_vat, ";
sql += " r.VAT, ";
sql += " r.ma_don_vi, ";
sql += " r.don_vi, ";
sql += " r.Phi_Chuyen_DVKD, ";
sql += " round(r.Phi_Chuyen_DVKD / 1.1) as Phi_Chuyen_DVKD_Chua_VAT, ";
sql += " round((r.Phi_Chuyen_DVKD / 1.1) * 0.1) as VAT_Phi_Chuyen_DVKD ";
sql += " from (select ttdl.tai_khoan_dai_ly, ";
sql += " m.company_name, ";
sql += " sum(ttdl.so_tien_thu_nhap) phi, ";
sql += " round(sum(ttdl.so_tien_thu_nhap) / 1.1) as phi_chua_vat, ";
sql += " round((sum(ttdl.so_tien_thu_nhap) / 1.1) * 0.1) as VAT, ";
sql += " p.settlement_branch as ma_don_vi, ";
sql += " b.branch_name as don_vi, ";
sql += " round(sum(ttdl.so_tien_thu_nhap) * 0.8) as Phi_Chuyen_DVKD ";
sql += " from " + Businessbp.executedb.owner + " nab_thanh_toan_dai_ly ttdl, ";
sql += " " + Businessbp.executedb.owner + " merchant m, ";
sql += " " + Businessbp.executedb.owner + " branch b, ";
sql += " " + Businessbp.executedb.owner + " ptserv p ";
sql += " where 1 = 1 ";
sql += " and ttdl.payment_flag = '0' ";
sql += " and ttdl.tai_khoan_dai_ly = m.merchant_number ";
sql += " and m.merchant_number = p.merchant_number ";
sql += " and p.settlement_branch = b.branch_code ";
sql += " and trunc(ttdl.batch_id) >= to_date('" + dt_FromDate.Text + "', 'dd/mm/yyyy') ";
sql += " and trunc(ttdl.batch_id) <= to_date('" + dt_ToDate.Text + "', 'dd/mm/yyyy') ";
sql += " and p.settlement_branch <> '0000' ";
sql += " group by ttdl.tai_khoan_dai_ly, ";
sql += " m.company_name, ";
sql += " p.settlement_branch, ";
sql += " b.branch_name ";
sql += " UNION ALL ";
sql += " select substr(ttdl.tai_khoan_dai_ly,1,4)||'xxxxxxxx'||substr(ttdl.tai_khoan_dai_ly,-4) tai_khoan_dai_ly, ";
sql += " m.company_name, ";
sql += " sum(ttdl.so_tien_thu_nhap) phi, ";
sql += " round(sum(ttdl.so_tien_thu_nhap) / 1.1) as phi_chua_vat, ";
sql += " round((sum(ttdl.so_tien_thu_nhap) / 1.1) * 0.1) as VAT, ";
sql += " '' as ma_don_vi, ";
sql += " '' as don_vi, ";
sql += " to_number('') as Phi_Chuyen_DVKD ";
sql += " from " + Businessbp.executedb.owner + " nab_thanh_toan_dai_ly ttdl, ";
sql += " " + Businessbp.executedb.owner + " merchant m, ";
sql += " " + Businessbp.executedb.owner + " ptserv p ";
sql += " where 1 = 1 ";
sql += " and ttdl.payment_flag = '0' ";
sql += " and ttdl.tai_khoan_dai_ly = m.merchant_number ";
sql += " and m.merchant_number = p.merchant_number ";
sql += " and trunc(ttdl.batch_id) >= to_date('" + dt_FromDate.Text + "', 'dd/mm/yyyy') ";
sql += " and trunc(ttdl.batch_id) <= to_date('" + dt_ToDate.Text + "', 'dd/mm/yyyy') ";
sql += " and p.settlement_branch = '0000' ";
sql += " group by ttdl.tai_khoan_dai_ly, ";
sql += " m.company_name, ";
sql += " p.settlement_branch ";
sql += " ) r ";
DataTable dt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
else
{
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 2014-06-23 (1) (LUAN)
}
private void btn_View_Click(object sender, EventArgs e)
{
string sql = "", err = "";
string from_date = "to_date('" + dtFrom.Text.Trim() + "','dd/mm/yyyy')";
string to_date = "to_date('" + dtTo.Text.Trim() + "','dd/mm/yyyy')";
if (Frm_main.admin_flag)
{
sql += " select a.card_number , ";
}
else
{
sql += " select substr(a.card_number,1,4)||'xxxxxxxx'||substr(a.card_number,-4) card_number, ";
}
sql += " plsp.description, ";
sql += " decode(plsp.type, ";
sql += " 'C', ";
sql += " ( ";
sql += " select cc.branch_code_dsa_code ";
sql += " from " + Businessbp.executedb.owner + "application app, " + Businessbp.executedb.owner + "card c1, " + Businessbp.executedb.owner + "cp_card cc ";
sql += " where 1=1 ";
sql += " and app.application_no = cc.source_application_no ";
sql += " and app.card_number = c1.card_number ";
sql += " and c1.card_number = (select ccbd.cr_account_nbr ";
sql += " from " + Businessbp.executedb.owner + "cr_card_balance_details ccbd ";
sql += " where a.card_number = ccbd.card_number ";
sql += " and ccbd.default_indicator ='1') ";
sql += " ), ";
sql += " 'P', ";
sql += " (select ccp.branch_code from " + Businessbp.executedb.owner + "cp_card_prepaid ccp where ccp.card_number =(select cal.account_number ";
sql += " from " + Businessbp.executedb.owner + "card_account_link cal ";
sql += " where a.card_number = cal.card_number ";
sql += " and cal.default_indicator = '1' ";
sql += " ) ";
sql += " ), ";
//sql += " --'D', ";
sql += " lpad(substr((select cal.account_number from " + Businessbp.executedb.owner + "card_account_link cal where a.card_number = cal.card_number and cal.default_indicator = '1'),1,3),4,'0')) ";
sql += " branch, ";
sql += " a.billing_amount,a.billing_currency, ";
sql += " to_char(trunc(a.processing_date),'dd/MM/yyyy') processing_date ";
sql += " from " + Businessbp.executedb.owner + "transaction_hist a, " + Businessbp.executedb.owner + "ipm_incoming_header b, ";
sql += " " + Businessbp.executedb.owner + "ipm_incoming_t c ," + Businessbp.executedb.owner + "card c1, " + Businessbp.executedb.owner + "nab_phanloaisp plsp ";
sql += " where 1=1 ";
sql += " and a.card_number = c1.card_number ";
sql += " and c1.product_code = plsp.product_code ";
sql += " and trim(b.ipi_job_id) = trim(c.ipi_job_id) ";
sql += " and trim(a.microfilm_ref_number) = trim(c.ipi_arn_cd) ";
sql += " and trim(a.card_number) = trim(c.ipi_pan_cd) ";
sql += " and a.additional_data = c.ipi_additional_data1_desc ";
sql += " and NVL(b.ipi_processing_status,0) = 1 ";
sql += " and substr(a.card_number,1,6) in ('533147','524083','516995','528645', '533968') ";
sql += " and a.acquirer_bank_code <> '970428' ";
sql += " and a.transaction_code not in (select transaction_code from " + Businessbp.executedb.owner + "nab_exception_trn) ";
sql += " and a.transaction_code not in ('22','26','27') ";
//sql += " and b.ipi_additional_data4_desc not in (select FILE_ID from " + Businessbp.executedb.owner + "nab_clearning_mastercard) ";
sql += " and a.mcc in ('5309') ";
sql += " and trunc(a.processing_date) >=" + from_date;
sql += " and trunc(a.processing_date) <=" + to_date;
sql += " order by a.processing_date ";
Frm_Report a = new Frm_Report();
a.type = "MC_TAX_Reversal";
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
a.dt = dtt;
a.title = "BẢNG KÊ CHI TIẾT HOÀN THUẾ";
a.thoiGian = "Từ ngày: " + dtFrom.Text + " đến ngày " + dtTo.Text;
a.thoigian2 = "Ngày " + DateTime.Now.Day.ToString() + " tháng " + DateTime.Now.Month.ToString() + " năm " + DateTime.Now.Year.ToString();
a.ShowDialog();
}
private void button1_Click(object sender, EventArgs e)
{
Frm_Report a = new Frm_Report();
string sql = "", err = "", ref_no = "", from = "", to = "";
ref_no = tbChargeback_Ref_no.Text;
from = dtFrom.Text.Trim();
to = dtTo.Text.Trim();
#region Da Reversal Chargeback 1st Presentment
if (rbChargeback_DaReversal.Checked == true)
{
if (ref_no !="")
{
sql = " select c.microfilm_ref_number as Ref_no, c.card_number as Card_no, c.chargeback_type,c.amount as Amt, c.currency_code as Cur_code,";
sql += " c.source_amount as Soure_amt,c.source_currency_code as Soure_cur,";
sql += " decode(c.documentation_indicator,0,'Documentation not required',1,'Documentation not provided',2,'Documentation follows') Doc_Indicator,";
sql += " c.reason_code,c.indicator_status,c.doc_received,cc.std_delay TongThoiGian,";
sql += " '' as pending ";
sql += " from " + Businessbp.executedb.owner + "chargeback c, " + Businessbp.executedb.owner + "ch_reason_code cc";
sql += " where c.microfilm_ref_number = '" + ref_no + "'";
sql += " and c.indicator_status = '3'";
sql += " and trunc(c.date_create) >= trunc(to_date('" + from + "','mm/dd/yyyy'))";
sql += " and trunc(c.date_create) <= trunc(to_date('" + to + "','mm/dd/yyyy'))";
sql += " and c.reason_code = cc.reason_code";
}
else {
sql = " select c.microfilm_ref_number as Ref_no, c.card_number as Card_no, c.chargeback_type,c.amount as Amt, c.currency_code as Cur_code,";
sql += " c.source_amount as Soure_amt,c.source_currency_code as Soure_cur,";
sql += " decode(c.documentation_indicator,0,'Documentation not required',1,'Documentation not provided',2,'Documentation follows') Doc_Indicator,";
sql += " c.reason_code,c.indicator_status,c.doc_received,cc.std_delay TongThoiGian,";
sql += " '' as pending ";
sql += " from " + Businessbp.executedb.owner + "chargeback c, " + Businessbp.executedb.owner + "ch_reason_code cc";
sql += " where c.indicator_status = '3'";
sql += " and trunc(c.date_create) >= trunc(to_date('" + from + "','mm/dd/yyyy'))";
sql += " and trunc(c.date_create) <= trunc(to_date('" + to + "','mm/dd/yyyy'))";
sql += " and c.reason_code = cc.reason_code";
}
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
else
{
if (dtt.Rows.Count == 0)
{
MessageBox.Show("No data found!!!");
return;
}
else
{
a.type = "MC_Chargeback_Reversal";
a.thoiGian = "Từ ngày: " + from + " đến ngày " + to;
a.title = "DANH SÁCH GIAO DỊCH FIRST CHARGEBACK ĐÃ REVERSAL";
a.dt = dtt;
a.ShowDialog();
}
}
}
#endregion Da Reversal Chargeback 1st Presentment
#region Chua Tra Loi Chargeback 1st Presentment
if (rbCharegeback_ChuaNhanTL.Checked == true)
{
if (ref_no != "")
{
sql = " select c.microfilm_ref_number as Ref_no, c.card_number as Card_no, c.chargeback_type,c.amount as Amt, c.currency_code as Cur_code,";
sql += " c.source_amount as Soure_amt,c.source_currency_code as Soure_cur,";
sql += " decode(c.documentation_indicator,0,'Documentation not required',1,'Documentation not provided',2,'Documentation follows') Doc_Indicator,";
sql += " c.reason_code,c.indicator_status,c.doc_received,cc.std_delay TongThoiGian,";
sql += " (cc.std_delay - (trunc(sysdate)- c.chargeback_date)) pending";
sql += " from " + Businessbp.executedb.owner + "chargeback c, " + Businessbp.executedb.owner + "ch_reason_code cc";
sql += " where c.microfilm_ref_number = '" + ref_no + "'";
sql += " and c.indicator_status = '2'";
sql += " and trunc(c.date_create) >= trunc(to_date('" + from + "','mm/dd/yyyy'))";
sql += " and trunc(c.date_create) <= trunc(to_date('" + to + "','mm/dd/yyyy'))";
sql += " and c.reason_code = cc.reason_code";
sql += " and (cc.std_delay - (trunc(sysdate)- c.chargeback_date)) > 0";
}
else {
sql = " select c.microfilm_ref_number as Ref_no, c.card_number as Card_no, c.chargeback_type,c.amount as Amt, c.currency_code as Cur_code,";
sql += " c.source_amount as Soure_amt,c.source_currency_code as Soure_cur,";
sql += " decode(c.documentation_indicator,0,'Documentation not required',1,'Documentation not provided',2,'Documentation follows') Doc_Indicator,";
sql += " c.reason_code,c.indicator_status,c.doc_received,cc.std_delay TongThoiGian,";
sql += " (cc.std_delay - (trunc(sysdate)- c.chargeback_date)) pending";
sql += " from " + Businessbp.executedb.owner + "chargeback c, " + Businessbp.executedb.owner + "ch_reason_code cc";
sql += " where c.indicator_status = '2'";
sql += " and trunc(c.date_create) >= trunc(to_date('" + from + "','mm/dd/yyyy'))";
sql += " and trunc(c.date_create) <= trunc(to_date('" + to + "','mm/dd/yyyy'))";
sql += " and c.reason_code = cc.reason_code";
sql += " and (cc.std_delay - (trunc(sysdate)- c.chargeback_date)) > 0";
}
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
else
{
if (dtt.Rows.Count == 0)
{
MessageBox.Show("No data found!!!");
return;
}
else
{
a.type = "MC_Chargeback_Reversal";
a.thoiGian = "Từ ngày: " + from + " đến ngày " + to;
a.title = "DANH SÁCH GIAO DỊCH FIRST CHARGEBACK CHƯA TRẢ LỜI";
a.dt = dtt;
a.ShowDialog();
}
}
}
#endregion Chua Tra Loi Chargeback 1st Presentment
#region Da Tra Loi Retrieval Request 1st Presentment
if (rbChargeback_DaNhanTL.Checked == true)
{
if (ref_no != "")
{
sql = " select 'Dong y' Typeas ,c.microfilm_ref_number as Ref_no, c.card_number as Card_no, c.chargeback_type,c.amount as Amt, c.currency_code as Cur_code,";
sql += " c.source_amount as Soure_amt,c.source_currency_code as Soure_cur,";
sql += " decode(c.documentation_indicator,0,'Documentation not required',1,'Documentation not provided',2,'Documentation follows') Doc_Indicator,";
sql += " c.reason_code,c.indicator_status,c.doc_received,cc.std_delay TongThoiGian,";
sql += " (cc.std_delay - (trunc(sysdate)- c.chargeback_date)) pending";
sql += " from " + Businessbp.executedb.owner + "chargeback c, " + Businessbp.executedb.owner + "ch_reason_code cc";
sql += " where c.microfilm_ref_number = '" + ref_no + "'";
sql += " and trunc(c.date_create) >= trunc(to_date('" + from + "','mm/dd/yyyy'))";
sql += " and trunc(c.date_create) <= trunc(to_date('" + to + "','mm/dd/yyyy'))";
sql += " and c.reason_code = cc.reason_code";
sql += " and (cc.std_delay - (trunc(sysdate)- c.chargeback_date)) <= 0 ";//-- het thoi gian tra loi
sql += " and not exists (select 1 ";// -- ma ko co 2nd pre
sql += " from " + Businessbp.executedb.owner + "transaction_hist tr";
sql += " where tr.microfilm_ref_number = c.microfilm_ref_number";
sql += " and tr.card_number = c.card_number";
sql += " and tr.function_code = '206')";
sql += " union all";
sql += " select 'Khong Dong y' Typeas ,c.microfilm_ref_number as Ref_no, c.card_number as Card_no, c.chargeback_type,c.amount as Amt, c.currency_code as Cur_code,";
sql += " c.source_amount as Soure_amt,c.source_currency_code as Soure_cur,";
sql += " decode(c.documentation_indicator,0,'Documentation not required',1,'Documentation not provided',2,'Documentation follows') Doc_Indicator,";
sql += " c.reason_code,c.indicator_status,c.doc_received,cc.std_delay TongThoiGian,";
sql += " (cc.std_delay - (trunc(sysdate)- c.chargeback_date)) pending ";
sql += " from " + Businessbp.executedb.owner + "chargeback c, " + Businessbp.executedb.owner + "ch_reason_code cc";
sql += " where c.microfilm_ref_number = '" + ref_no + "'";
sql += " and trunc(c.date_create) >= trunc(to_date('" + from + "','mm/dd/yyyy'))";
sql += " and trunc(c.date_create) <= trunc(to_date('" + to + "','mm/dd/yyyy'))";
sql += " and c.reason_code = cc.reason_code";
sql += " and exists (select 1"; // ton tai 2nd pre trong trans_hist
sql += " from " + Businessbp.executedb.owner + "transaction_hist tr";
sql += " where tr.microfilm_ref_number = c.microfilm_ref_number";
sql += " and tr.card_number = c.card_number";
sql += " and tr.function_code = '206')";
}
else
{
sql = " select 'Dong y' Typeas ,c.microfilm_ref_number as Ref_no, c.card_number as Card_no, c.chargeback_type,c.amount as Amt, c.currency_code as Cur_code,";
sql += " c.source_amount as Soure_amt,c.source_currency_code as Soure_cur,";
sql += " decode(c.documentation_indicator,0,'Documentation not required',1,'Documentation not provided',2,'Documentation follows') Doc_Indicator,";
sql += " c.reason_code,c.indicator_status,c.doc_received,cc.std_delay TongThoiGian,";
sql += " (cc.std_delay - (trunc(sysdate)- c.chargeback_date)) pending";
sql += " from " + Businessbp.executedb.owner + "chargeback c, " + Businessbp.executedb.owner + "ch_reason_code cc";
sql += " where trunc(c.date_create) >= trunc(to_date('" + from + "','mm/dd/yyyy'))";
sql += " and trunc(c.date_create) <= trunc(to_date('" + to + "','mm/dd/yyyy'))";
sql += " and c.reason_code = cc.reason_code";
sql += " and (cc.std_delay - (trunc(sysdate)- c.chargeback_date)) <= 0 ";//-- het thoi gian tra loi
sql += " and not exists (select 1 ";// -- ma ko co 2nd pre
sql += " from " + Businessbp.executedb.owner + "transaction_hist tr";
sql += " where tr.microfilm_ref_number = c.microfilm_ref_number";
sql += " and tr.card_number = c.card_number";
sql += " and tr.function_code = '206')";
sql += " union all";
sql += " select 'Khong Dong y' Typeas ,c.microfilm_ref_number as Ref_no, c.card_number as Card_no, c.chargeback_type,c.amount as Amt, c.currency_code as Cur_code,";
sql += " c.source_amount as Soure_amt,c.source_currency_code as Soure_cur,";
sql += " decode(c.documentation_indicator,0,'Documentation not required',1,'Documentation not provided',2,'Documentation follows') Doc_Indicator,";
sql += " c.reason_code,c.indicator_status,c.doc_received,cc.std_delay TongThoiGian,";
sql += " (cc.std_delay - (trunc(sysdate)- c.chargeback_date)) pending ";
sql += " from " + Businessbp.executedb.owner + "chargeback c, " + Businessbp.executedb.owner + "ch_reason_code cc";
sql += " where trunc(c.date_create) >= trunc(to_date('" + from + "','mm/dd/yyyy'))";
sql += " and trunc(c.date_create) <= trunc(to_date('" + to + "','mm/dd/yyyy'))";
sql += " and c.reason_code = cc.reason_code";
sql += " and exists (select 1"; // ton tai 2nd pre trong trans_hist
sql += " from " + Businessbp.executedb.owner + "transaction_hist tr";
sql += " where tr.microfilm_ref_number = c.microfilm_ref_number";
sql += " and tr.card_number = c.card_number";
sql += " and tr.function_code = '206')";
}
DataTable dtt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
else
{
if (dtt.Rows.Count == 0)
{
MessageBox.Show("No data found!!!");
return;
}
else
{
a.type = "MC_Chargeback_DaTL";
a.thoiGian = "Từ ngày: " + from + " đến ngày " + to;
a.title = "DANH SÁCH GIAO DỊCH CHARGEBACK ĐÃ TRẢ LỜI";
a.dt = dtt;
a.ShowDialog();
}
}
}
#endregion Da Tra Loi Retrieval Retrieval Request 1st Presentment
}
