public virtual CrystalDecisions.CrystalReports.Engine.ReportDocument CreateReport()
{
rpt_Thanh_ly_tai_khoan rpt = new rpt_Thanh_ly_tai_khoan();
rpt.Site = this.Site;
return rpt;
}
private void button1_Click(object sender, EventArgs e)
{
string sql = "", err = "";
string title = "";
string type = "";
if (cbOption.SelectedIndex + 1 == 1)
{ type = "MB-TNT01"; }
if (cbOption.SelectedIndex + 1 == 2)
{ type = "MB-TNT02"; }
if (cbOption.SelectedIndex + 1 == 3)
{ type = "MB-TNT03"; }
if (cbOption.SelectedIndex + 1 == 4)
{ type = "MB-TLT01"; }
this.Cursor = Cursors.WaitCursor;
string folderOut = tbFilePath.Text.Trim();
//Tao thu muc
if (!Directory.Exists(folderOut))
{
Directory.CreateDirectory(folderOut);
}
if (type != "")
{
sql = ""; err = "";
sql = " select *";
sql += " from nab_credit_xln_sendmail g";
sql += " where trim(g.type_report)='" + type + "'";
sql += " and trunc(g.date_create)=trunc(sysdate)";
DataTable dtCheck = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
return;
}
else
{
//if (dtCheck.Rows.Count > 0)
//{
// MessageBox.Show("Đã xử lý Report này cho hôm nay.");
// return;
//}
}
for (int k = 0; k < dtBranch.Rows.Count; k++)//Vòng lặp để xuất ra từng Branch
{
string branch = dtBranch.Rows[k]["branch_code"].ToString().Trim();
tbStatus.Text = "Da xu ly : "+ (k +1).ToString() +" --" +"Dang xu ly :"+branch;
if (type.ToUpper() == "MB-TNT01")
{
sql = "select cl.short_name embossed_name, ";
if (Frm_main.admin_flag)
{
sql += " ctr_a.contract_number cr_account_nbr, ";
}
else
{
sql += " substr(ctr_a.contract_number,1,4)||'xxxxxxxx'||substr(ctr_a.contract_number,-4) cr_account_nbr, ";
}
sql += " cl.reg_number, ";
sql += " cl.client_number, ";
sql += " abs(te.CLOSE_CASH + te.CLOSE_SALE) closing_bal, ";
sql += " (select abs(blg.total_balance) ";
sql += " from " + Businessbp.executedb.owner + "billing_log blg ";
sql += " where blg.acnt_contract__oid = bl.acnt_contract__oid ";
sql += " and blg.start_billing_date = ";
sql += " (select max(blg1.start_billing_date) ";
sql += " from " + Businessbp.executedb.owner + "billing_log blg1 ";
sql += " where blg1.acnt_contract__oid = ctr_a.id)) opening_bal, ";
sql += " (nab_pck_out.nab_fnc_Get_payment(ctr_a.contract_number, ";
sql += " (case ";
sql += " when extract(day from sysdate) >= 24 then ";
sql += " to_date('24/' || to_char(sysdate, 'mm/yyyy'), 'dd/mm/yyyy') ";
sql += " else ";
sql += " to_date('24/' || to_char(add_months(sysdate, -1), 'mm/yyyy'), ";
sql += " 'dd/mm/yyyy') ";
sql += " end), ";
sql += " trunc(sysdate)) + ";
sql += " (select nvl(sum(r.transaction_amount), 0) ";
sql += " from nab_balance_credit r ";
sql += " where (select ctr.contract_number ";
sql += " from " + Businessbp.executedb.owner + "Acnt_Contract ctr ";
sql += " where ctr.amnd_state = 'A' ";
sql += " and ctr.id = ";
sql += " (select ctr1.acnt_contract__oid ";
sql += " from " + Businessbp.executedb.owner + "acnt_contract ctr1 ";
sql += " where 1 = 1 ";
sql += " and ctr1.amnd_state = 'A' ";
sql += " and ctr1.contract_number = r.card_number)) = ";
sql += " ctr_a.contract_number ";
sql += " and nvl(r.extract_flag, 'N') = 'N' ";
sql += " and substr(r.message_receive, 1, 7) = 'success' ";
sql += " and nvl(r.authorize, 'N') = 'Y')) cur_payment, ";
sql += " abs(bl.total_due) + ";
sql += " (select abs(nvl(sum(acc1.current_balance), 0)) ";
sql += " from " + Businessbp.executedb.owner + "account acc1 ";
sql += " where acc1.acnt_contract__oid = ctr_a.id ";
sql += " and acc1.code in ('L2', 'L4')) min_due, ";
sql += " bl.due_date, ";
sql += " nvl(ctr_a.add_info_03, 0) unpaid_period, ";
sql += " nab_pck_out.nab_fnc_getThucNoDays(ctr_a.contract_number) unpaid_date, ";
sql += " (select addr.phone_m ";
sql += " from " + Businessbp.executedb.owner + "contract_addresses addr ";
sql += " where 1 = 1 ";
sql += " and addr.acnt_contract__oid = ctr_a.id ";
sql += " and addr.address_type__id = ";
sql += " (select at.id ";
sql += " from " + Businessbp.executedb.owner + "address_type at ";
sql += " where 1 = 1 ";
sql += " and at.amnd_state = 'A' ";
sql += " and at.code = 'PHS_ADDR')) phone_mobile, ";
sql += " cl.e_mail, ";
sql += " decode(nvl(ctr_a.add_info_01, '0'), ";
sql += " 'M', ";
sql += " 'MAD', ";
sql += " '100', ";
sql += " 'TAD', ";
sql += " '0', ";
sql += " 'not registed', ";
sql += " (to_number(ctr_a.add_info_01) || '%')) auto_payment, ";
sql += " ctr_a.rbs_number, ";
sql += " (select nvl(acy_avl_bal, 0) ";
sql += " from sttm_cust_account@Flexcube ";
sql += " where cust_ac_no = ctr_a.rbs_number ";
sql += " and record_stat = 'O' ";
sql += " and auth_stat = 'A') So_Tien_TKTT ";
sql += " from " + Businessbp.executedb.owner + "acnt_contract ctr_a, ";
sql += " " + Businessbp.executedb.owner + "acnt_contract ctr_c, ";
sql += " " + Businessbp.executedb.owner + "appl_product prd, ";
sql += " nab_cp_card cc, ";
sql += " nab_vw_total_endcycle te, ";
sql += " " + Businessbp.executedb.owner + "billing_log bl, ";
sql += " " + Businessbp.executedb.owner + "client cl ";
sql += " where 1 = 1 ";
sql += " and cl.id = ctr_c.client__id ";
sql += " and ctr_c.acnt_contract__oid = ctr_a.id ";
sql += " and ctr_c.product = prd.internal_code ";
sql += " and ctr_a.contract_number = te.contract_number ";
sql += " and ctr_c.add_info_01 = cc.source_application_no ";
sql += " and bl.acnt_contract__oid = ctr_a.id ";
sql += " and te.DATE_TO = bl.finish_billing_date ";
sql += " and ctr_a.amnd_state = 'A' ";
sql += " and ctr_c.amnd_state = 'A' ";
sql += " and prd.amnd_state = 'A' ";
sql += " and substr(prd.code, 5, 1) = 'M' ";
sql += " and te.DATE_TO = (case ";
sql += " when extract(day from sysdate) >= 24 then ";
sql += " to_date('24/' || to_char(sysdate, 'mm/yyyy'), 'dd/mm/yyyy') ";
sql += " else ";
sql += " to_date('24/' || to_char(add_months(sysdate, -1), 'mm/yyyy'), ";
sql += " 'dd/mm/yyyy') ";
sql += " end) ";
sql += " ";
sql += " and ctr_c.branch = '" + branch + "' ";
title = "DANH SÁCH KHÁCH HÀNG CẦN NHẮC NỢ";
}
#region chualam
else if (type.ToUpper() == "MB-TNT02")
{
sql = "select cl.short_name embossed_name, ";
sql += " cl.client_number client_number, ";
if (Frm_main.admin_flag)
{
sql += " ca.contract_number cr_account_nbr, ";
}
else
{
sql += " substr(ca.contract_number,1,4)||'xxxxxxxx'||substr(ca.contract_number,-4) cr_account_nbr, ";
}
sql += " -ca.total_balance closing_bal, ";
sql += " -l.total_balance opening_bal, ";
sql += " -l.total_due min_due, ";
sql += " to_char(trunc(l.due_date-1),'dd/mm/yyyy') payment_date, ";
sql += " to_char(nab_pck_out.GET_UNPAID_STATUS(ca.contract_number),'999') acc_unpaid_status, ";
//sql += " to_char(nab_pck_out.nab_fnc_getThucNoDays(ca.contract_number),'999') number_days, ";
sql += " cl.phone_m phone_mobile, ";
sql += " cl.e_mail, ";
sql += " ca.add_info_01 auto_payment, ";
sql += " ca.rbs_number rbs_number, ";
sql += " (select ltrim(to_char(nvl(a.acy_avl_bal, 0), '999,999,999,999')) as acy_avl_bal ";
sql += " from sttm_cust_account@FLEXCUBE a ";
sql += " WHERE a.cust_ac_no = trim(ca.rbs_number)) So_Tien_TKTT, ";
sql += " (select br.name";
sql += " from ows.branch br";
sql += " where br.amnd_state = 'A'";
sql += " and br.code = ca.branch) branch_code";
sql += " from " + Businessbp.executedb.owner + "acnt_contract ca, ";
sql += " " + Businessbp.executedb.owner + "client cl, ";
sql += " " + Businessbp.executedb.owner + "billing_log l ";
sql += " where ca.amnd_state = 'A' and ca.con_cat = 'A' ";
sql += " and cl.amnd_state = 'A' ";
sql += " and ca.client__id = cl.id ";
sql += " and ca.id = l.acnt_contract__oid ";
sql += " and l.finish_billing_date = (select max(l1.finish_billing_date) ";
sql += " from " + Businessbp.executedb.owner + "billing_log l1 ";
sql += " where l1.acnt_contract__oid = ca.id) ";
sql += " and nab_pck_out.GET_UNPAID_STATUS(ca.contract_number) = '4' ";
sql += " and not exists (select 1 from " + Businessbp.executedb.owner + "V_CS_ALL_ACNT_STATUS vcs ";
sql += " where vcs.acnt_contract__oid = ca.id ";
sql += " and vcs.status_type_code = 'DEBT_LEVEL' ";
sql += " and substr(vcs.status_value_code,1,1) = 'O') ";
sql += " and ca.branch = '" + branch + "' ";
title = "DANH SÁCH KHÁCH HÀNG CẦN CHUYỂN NỢ QUÁ HẠN";
}
else if (type.ToUpper() == "MB-TNT03")
{
sql = "";
sql += " with payment as";
sql += " (";
sql += " select nvl(sum(th.local_amount),0) payment, acnt.id";
sql += " from v_transaction_hist th, " + Businessbp.executedb.owner + "acnt_contract acnt ";
sql += " where th.issuing_id = acnt.id";
sql += " and acnt.amnd_state = 'A'";
sql += " and acnt.con_cat = 'A'";
sql += " and th.POSTING_DATE >= acnt.last_billing_date";
sql += " and th.trans_type in ('36628','36630','36643','36646','36461','37250','146','700','15','36522','36682','36994')";
sql += " group by acnt.id";
sql += " ), ";
sql += " payment_total as";
sql += " ( ";
sql += " select p.payment, ";
sql += " ac.id,";
sql += " -bl.total_balance total_balance,";
sql += " -bl.total_due total_due,";
sql += " bl.due_date";
sql += " from " + Businessbp.executedb.owner + "billing_log bl,";
sql += " " + Businessbp.executedb.owner + "acnt_contract ac,";
sql += " (select *";
sql += " from payment p ";
sql += " union ";
sql += " select 0 payment , id";
sql += " from " + Businessbp.executedb.owner + "acnt_contract ac";
sql += " where ac.amnd_state = 'A'";
sql += " and ac.con_cat = 'A'";
sql += " and ac.liab_category is not null";
sql += " and substr(ac.contract_number,1,1) in ('3','6')";
sql += " and not exists (select 1 from payment p1 where p1.id = ac.id)) p";
sql += " where bl.finish_billing_date = (select max(l.finish_billing_date) from ows.billing_log l where l.acnt_contract__oid = bl.acnt_contract__oid)";
sql += " and ac.con_cat = 'A'";
sql += " and ac.amnd_state = 'A'";
sql += " and ac.id = bl.acnt_contract__oid";
sql += " and bl.total_due < 0";
sql += " and p.id = ac.id ";
sql += " ),";
sql += " email_phone as";
sql += " (select cp.mobile_number,";
sql += " cp.email_id,";
sql += " c.acnt_contract__oid,";
sql += " cp.machinhsachtd";
sql += " from " + Businessbp.executedb.owner + "acnt_contract c, nab_cp_card cp, " + Businessbp.executedb.owner + "appl_product pr";
sql += " where c.amnd_state = 'A'";
sql += " and c.con_cat = 'C'";
sql += " and c.add_info_01 = cp.source_application_no";
sql += " and c.product = pr.internal_code";
sql += " and pr.amnd_state = 'A'";
sql += " and substr(pr.code, 2, 1) = 'C'";
sql += " and substr(pr.code, 5, 1) = 'M'";
sql += " and c.contr_status in ('14', '423', '267', '174', '109')";
sql += " ";
sql += " )";
sql += " select (select br.name";
sql += " from ows.branch br";
sql += " where br.amnd_state = 'A'";
sql += " and br.code = ctr_a.branch) branch_code,";
sql += " cl.short_name embossed_name,";
sql += " cl.client_number client_number,";
sql += " cl.reg_number legal_id,";
sql += " ctr_a.contract_number cr_account_nbr,";
sql += " -ctr_a.total_balance closing_bal,";
sql += " (select sum(acc.current_balance)";
sql += " from ows.account acc";
sql += " where acc.acnt_contract__oid = ctr_a.id";
sql += " and acc.account_type in ('929', '933', '1098', '1099')) lai_ngoai_bang,";
sql += " to_char((select min(ua.start_date)";
sql += " from ows.usage_action ua";
sql += " where ua.posting_status = 'C'";
sql += " and (select substr(et.name, 1, 4)";
sql += " from ows.event_type et";
sql += " where et.id = ua.event_type) = 'Dept'";
sql += " and ua.acnt_contract__id = ctr_a.id),";
sql += " 'dd/mm/yyyy') ngay_chuyen_lan_dau,";
sql += " bl.total_balance opening_bal,";
sql += " to_char(bl.total_due,'999,999,999,999') min_due,";
sql += " bl.payment,";
sql += " to_char(bl.due_date-1, 'dd/mm/yyyy') payment_date,";
sql += " ctr_a.add_info_03 acc_unpaid_status,";
//sql += " to_char(nab_pck_out.nab_fnc_getThucNoDays(ctr_a.contract_number),'999') number_days,";
sql += " (select c.mobile_number";
sql += " from email_phone c";
sql += " where c.acnt_contract__oid = ctr_a.id) phone_mobile,";
sql += " (select c.email_id";
sql += " from email_phone c";
sql += " where c.acnt_contract__oid = ctr_a.id) e_mail,";
sql += " decode(nvl(ctr_a.add_info_01, '0'),";
sql += " 'M',";
sql += " 'MAD',";
sql += " '100',";
sql += " 'TAD',";
sql += " '0',";
sql += " 'not registed',";
sql += " (to_number(ctr_a.add_info_01) || '%')) auto_payment,";
sql += " ctr_a.rbs_number,";
sql += " (select nvl(acy_avl_bal, 0)";
sql += " from sttm_cust_account@Flexcube";
sql += " where cust_ac_no = ctr_a.rbs_number";
sql += " and record_stat = 'O'";
sql += " and auth_stat = 'A') So_Tien_TKTT";
sql += " from " + Businessbp.executedb.owner + "acnt_contract ctr_a, payment_total bl, " + Businessbp.executedb.owner + "client cl";
sql += " where 1 = 1";
sql += " and cl.id = ctr_a.client__id";
sql += " and ctr_a.amnd_state = 'A'";
sql += " and ctr_a.con_cat = 'A'";
sql += " and cl.amnd_state = 'A'";
sql += " and ctr_a.liab_category is not null";
sql += " and ctr_a.add_info_03 = 2";
sql += " and bl.id = ctr_a.id";
sql += " and bl.total_due > bl.payment";
sql += " and ctr_a.contr_status = '51'";
sql += " and ctr_a.branch = '"+branch+"' ";
title = "DANH SÁCH KHÁCH HÀNG QUÁ HẠN/KÉO THEO";
}
else if (type.ToUpper() == "MB-TLT01")
{
if (Frm_main.admin_flag)
{
sql = " select br.name branch_code, a.contract_name hoten,cl.reg_number gttt,cl.client_number makh, a.contract_number sotaikhoan, st.name trangthai,to_char(h.amnd_date,'dd/mm/yyyy') ngaythanhly,to_char(-a.total_balance,'999,999,999,999,999') duno";
}
else
{
sql = " select br.name branch_code, a.contract_name hoten,cl.reg_number gttt,cl.client_number makh, substr(a.contract_number,1,4)||'xxxxxxxx'||substr(a.contract_number,-4) sotaikhoan, st.name trangthai,to_char(a.amnd_date,'dd/mm/yyyy') ngaythanhly,to_char(-a.total_balance,'999,999,999,999,999') duno";
}
sql += " from " + Businessbp.executedb.owner + "acnt_contract a, " + Businessbp.executedb.owner + "contr_status st," + Businessbp.executedb.owner + "client cl, " + Businessbp.executedb.owner + "branch br, status_history h" +
" where a.amnd_state ='A' and cl.amnd_state = 'A'"+
" and a.con_cat = 'A' "+
" and st.id = a.contr_status"+
" and cl.id = a.client__id and br.code = a.branch" +
" and h.contract_number = a.contract_number" +
" and h.val like '%--> 13 (Account Closed)%'" +
" and trunc(h.amnd_date)= to_date('" + dt_Date.Text + "', 'dd/mm/yyyy')";
sql += " and a.branch = '" + branch + "' ";
title = "THÔNG BÁO XÁC NHẬN DƯ NỢ VÀ THANH LÝ TÀI KHOẢN THẺ TÍN DỤNG CỦA KHÁCH HÀNG";
}
#endregion chualam
if (sql != "")
{
DataTable dtReport = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
this.Cursor = Cursors.Default;
return;
}
else
{
string file_path = "";
if (cbOption.SelectedIndex + 1 != 4)
{
file_path = tbFilePath.Text.Trim() + branch.Trim() + "_" + type + "_" + DateTime.Now.ToString("ddMMyyyy") + ".pdf";
}
else
{
string s = Convert.ToDateTime(dt_Date.Value).ToString("ddMMyyyy");
file_path = tbFilePath.Text.Trim() + branch.Trim() + "_" + type + "_" + s + ".pdf";
}
//Export pdf
try
{
if (dtReport.Rows.Count > 0)
{
if (cbOption.SelectedIndex + 1 != 4)
{
rpt_Xu_Ly_No cry = new rpt_Xu_Ly_No();
cry.SetDataSource(dtReport);
// string title = "";
cry.SetParameterValue("title", title);
cry.SetParameterValue("MauBieu", "Mẫu: ");
cry.SetParameterValue("Ngayin", DateTime.Now.ToShortDateString());
cry.SetParameterValue("Gioin", DateTime.Now.ToLongTimeString());
ExportOptions CrExportOptions;
DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions();
PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions();
CrDiskFileDestinationOptions.DiskFileName = file_path;// tbFilePath.Text.Trim() + "\\" + card_number.Trim() + ".pdf";//"c:\\csharp.net-informations.pdf";
CrExportOptions = cry.ExportOptions;
{
CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile;
CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat;
//CrExportOptions.ExportFormatType = ExportFormatType.WordForWindows;
CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions;
CrExportOptions.FormatOptions = CrFormatTypeOptions;
}
cry.Export();
//--Fix for Load Report failse
if (cry != null)
{
cry.Close();
cry.Dispose();
}
//lấy dữ liệu insert table
sql = ""; err = "";
sql = "insert into NAB_CREDIT_XLN_SENDMAIL";
sql += " (BRANCH_CODE ,FILE_PATH ,USER_CREATE ,DATE_CREATE,SEND_FLAG ,TYPE_REPORT)";
sql += "values (";
sql += "'" + branch + "',";
sql += "'" + file_path.Replace("\\", @"\") + "',";
sql += "'" + Businessbp.executedb.Usrid + "',";
sql += "sysdate,";
sql += "'0',";
sql += "'" + type + "'";
sql += ")";
Businessbp.executedb.ExecuteDML(sql, ref err);
if (err != "")
{
MessageBox.Show(err); //return;
// this.Cursor = Cursors.Default;
}
}
else
{
rpt_Thanh_ly_tai_khoan cry1 = new rpt_Thanh_ly_tai_khoan();
cry1.SetDataSource(dtReport);
// string title = "";
cry1.SetParameterValue("title", title);
cry1.SetParameterValue("Ngayin", DateTime.Now.ToShortDateString());
cry1.SetParameterValue("Gioin", DateTime.Now.ToLongTimeString());
ExportOptions CrExportOptions;
DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions();
PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions();
CrDiskFileDestinationOptions.DiskFileName = file_path;// tbFilePath.Text.Trim() + "\\" + card_number.Trim() + ".pdf";//"c:\\csharp.net-informations.pdf";
CrExportOptions = cry1.ExportOptions;
{
CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile;
CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat;
//CrExportOptions.ExportFormatType = ExportFormatType.WordForWindows;
CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions;
CrExportOptions.FormatOptions = CrFormatTypeOptions;
}
cry1.Export();
//--Fix for Load Report failse
if (cry1 != null)
{
cry1.Close();
cry1.Dispose();
}
//lấy dữ liệu insert table
sql = ""; err = "";
sql = "insert into NAB_CREDIT_XLN_SENDMAIL";
sql += " (BRANCH_CODE ,FILE_PATH ,USER_CREATE ,DATE_CREATE,SEND_FLAG ,TYPE_REPORT)";
sql += "values (";
sql += "'" + branch + "',";
sql += "'" + file_path.Replace("\\", @"\") + "',";
sql += "'" + Businessbp.executedb.Usrid + "',";
sql += "sysdate,";
sql += "'0',";
sql += "'" + type + "'";
sql += ")";
Businessbp.executedb.ExecuteDML(sql, ref err);
if (err != "")
{
MessageBox.Show(err); //return;
// this.Cursor = Cursors.Default;
}
}
}
else
{
// MessageBox.Show("");//+ card_number.Trim());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
this.Cursor = Cursors.Default;
}
}
}//End for branch
this.Cursor = Cursors.Default;
tbStatus.Text += "-- Success";
if (k+1 == dtBranch.Rows.Count)
{
MessageBox.Show("Đã process thành công!!!");
}
}
}
}