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 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 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 bt_Report_Click(object sender, EventArgs e) { Frm_Report a = new Frm_Report(); a.type = "PHT_THUPHI"; string sql = "", err = ""; 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 in ('C', 'P') "; //sql += " and a.loaithe in ('699', '251', '252', '254', '299', '003', '015', '012', '008', '004', '002', '005', '014') "; 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 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 }
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 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(); } }
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(); } }
// --- 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(); } }
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 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 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 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 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 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 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 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 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 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 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 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 }