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-TNT04"; if (cbOption.SelectedIndex + 1 == 3) type = "MB-TNT05"; if (cbOption.SelectedIndex + 1 == 4) type = "CR_TN21"; if (cbOption.SelectedIndex + 1 == 5) type = "CR_TN3"; if (cbOption.SelectedIndex + 1 == 6) type = "CR_ADC"; if (cbOption.SelectedIndex + 1 == 7) type = "MB-TNT09"; if (cbOption.SelectedIndex + 1 == 8) type = "CR_LV1"; if (cbOption.SelectedIndex + 1 == 9) type = "CR_TD1"; 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 "+Businessbp.executedb.owner+"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-TNT04") { //sql = "select a.cr_account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') payment_date,"; //old 2014-04-01 (1) if (Frm_main.admin_flag) { sql = "select distinct a.cr_account_nbr account_nbr,a.cr_account_nbr , e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') payment_date,"; //2014-04-01 (1) } else { sql = "select distinct a.cr_account_nbr account_nbr,substr(a.cr_account_nbr,1,4)||'xxxxxxxx'||substr(a.cr_account_nbr,-4) cr_account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') payment_date,"; //2014-04-01 (1) } sql += " nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) number_days,'' as Ghi_chu"; sql += " from " + Businessbp.executedb.owner + "account a, " + Businessbp.executedb.owner + "cr_term b, " + Businessbp.executedb.owner + "card c, " + Businessbp.executedb.owner + "application d, " + Businessbp.executedb.owner + "cp_card e"; sql += " where a.cr_account_nbr = b.cr_account_nbr"; sql += " and a.cr_account_nbr = c.cr_account_nbr"; sql += " and c.cr_account_nbr = d.card_number"; sql += " and d.application_no = e.source_application_no"; //sql += " and nvl(a.acc_unpaid_status, '0') <> '0'"; sql += " and " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) <> '0' "; sql += " and b.stat_date = ("; sql += " select max(f.stat_date)"; sql += " from " + Businessbp.executedb.owner + "cr_term f"; sql += " where f.cr_account_nbr = a.cr_account_nbr"; sql += " )"; sql += " and c.status_code in ('0','3','9','10') "; //Phong edit 15/06/2013 sql += " and nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) >= 1 "; sql += " and nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) < 16 "; sql += " and trim(e.branch_code_dsa_code) = '" + branch + "'";// --set for choosing branch"; if (cbCycle.SelectedValue.ToString().ToUpper() != "000")//000: All sql += " and a.cycoff_code = '" + cbCycle.SelectedValue.ToString().ToUpper() + "'"; title = "DANH SÁCH KHÁCH HÀNG CẦN THÚC NỢ LẦN 1"; } #region chualam else if (type.ToUpper() == "MB-TNT05") { //sql = "select a.cr_account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due," + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') payment_date,"; //old 2014-04-01 (1) if (Frm_main.admin_flag) { sql = "select distinct a.cr_account_nbr account_nbr,a.cr_account_nbr , e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due," + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') payment_date,"; //2014-04-01 (1) } else { sql = "select distinct a.cr_account_nbr account_nbr,substr(a.cr_account_nbr,1,4)||'xxxxxxxx'||substr(a.cr_account_nbr,-4) cr_account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due," + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') payment_date,"; //2014-04-01 (1) } sql += " nvl(" +Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) number_days,'' as Ghi_chu"; sql += " from " + Businessbp.executedb.owner + "account a, " + Businessbp.executedb.owner + "cr_term b, " + Businessbp.executedb.owner + "card c, " + Businessbp.executedb.owner + "application d, " + Businessbp.executedb.owner + "cp_card e"; sql += " where a.cr_account_nbr = b.cr_account_nbr"; sql += " and a.cr_account_nbr = c.cr_account_nbr"; sql += " and c.cr_account_nbr = d.card_number"; sql += " and d.application_no = e.source_application_no"; // sql += " and nvl(a.acc_unpaid_status, '0') <> '0'"; sql += " and " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) <> '0' "; sql += " and b.stat_date = ("; sql += " select max(f.stat_date)"; sql += " from " + Businessbp.executedb.owner + "cr_term f"; sql += " where f.cr_account_nbr = a.cr_account_nbr"; sql += " )"; sql += " and c.status_code in ('0','3','9','10') "; //Phong edit 15/06/2013 sql += " and nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) >= 16 "; sql += " and nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) < 31 "; sql += " and trim(e.branch_code_dsa_code) = '" + branch.ToUpper() + "'";// --set for choosing branch"; if (cbCycle.SelectedValue.ToString().ToUpper() != "000")//000: All sql += " and a.cycoff_code = '" + cbCycle.SelectedValue.ToString().ToUpper() + "'"; title = "DANH SÁCH KHÁCH HÀNG CẦN THÚC NỢ LẦN 2"; } else if (type.ToUpper() == "CR_TN21") { //sql = "select a.cr_account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, trunc(b.payment_date) payment_date,"; //old 2014-04-01 (1) if (Frm_main.admin_flag) { sql = "select distinct a.cr_account_nbr account_nbr,a.cr_account_nbr , e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, trunc(b.payment_date) payment_date,"; //2014-04-01 (1) } else { sql = "select distinct a.cr_account_nbr account_nbr,substr(a.cr_account_nbr,1,4)||'xxxxxxxx'||substr(a.cr_account_nbr,-4) cr_account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, trunc(b.payment_date) payment_date,"; //2014-04-01 (1) } sql += " nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) number_days,'' as Ghi_chu"; sql += " from " + Businessbp.executedb.owner + "account a, " + Businessbp.executedb.owner + "cr_term b, " + Businessbp.executedb.owner + "card c, " + Businessbp.executedb.owner + "application d, " + Businessbp.executedb.owner + "cp_card e"; sql += " where a.cr_account_nbr = b.cr_account_nbr"; sql += " and a.cr_account_nbr = c.card_number"; sql += " and c.cr_account_nbr = d.card_number"; sql += " and d.application_no = e.source_application_no"; // sql += " and nvl(a.acc_unpaid_status, '0') <> '0'"; sql += " and " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) <> '0' "; sql += " and b.stat_date = ("; sql += " select max(f.stat_date)"; sql += " from " + Businessbp.executedb.owner + "cr_term f"; sql += " where f.cr_account_nbr = a.cr_account_nbr"; sql += " )"; sql += " and c.status_code in ('0','3','9','10') "; //Phong edit 15/06/2013 sql += " and nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) >= 31"; sql += " and nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) < 61 "; sql += " and trim(e.branch_code_dsa_code) = '" + branch.ToUpper() + "'";// --set for choosing branch"; if (cbCycle.SelectedValue.ToString().ToUpper() != "000")//000: All sql += " and a.cycoff_code = '" + cbCycle.SelectedValue.ToString().ToUpper() + "'"; title = "DANH SÁCH KHÁCH HÀNG CẦN THÚC NỢ LẦN 3";// VÀ GỬI THƯ LẦN 1"; } else if (type.ToUpper() == "CR_TN3") { //sql = "select a.cr_account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') payment_date,"; //old 2014-04-01 (1) if (Frm_main.admin_flag) { sql = "select distinct a.cr_account_nbr account_nbr,a.cr_account_nbr , e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') payment_date,"; //2014-04-01 (1) } else { sql = "select distinct a.cr_account_nbr account_nbr,substr(a.cr_account_nbr,1,4)||'xxxxxxxx'||substr(a.cr_account_nbr,-4) cr_account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') payment_date,"; //2014-04-01 (1) } sql += " nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) number_days,'' as Ghi_chu"; sql += " from " + Businessbp.executedb.owner + "account a, " + Businessbp.executedb.owner + "cr_term b, " + Businessbp.executedb.owner + "card c, " + Businessbp.executedb.owner + "application d, " + Businessbp.executedb.owner + "cp_card e"; sql += " where a.cr_account_nbr = b.cr_account_nbr"; sql += " and a.cr_account_nbr = c.card_number"; sql += " and c.card_number = d.card_number"; sql += " and d.application_no = e.source_application_no"; // sql += " and nvl(a.acc_unpaid_status, '0') <> '0'"; sql += " and " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) <> '0' "; sql += " and b.stat_date = ("; sql += " select max(f.stat_date)"; sql += " from " + Businessbp.executedb.owner + "cr_term f"; sql += " where f.cr_account_nbr = a.cr_account_nbr"; sql += " )"; sql += " and c.status_code in ('0','3','9','10') "; //Phong edit 15/06/2013 sql += " and nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) >= 61"; sql += " and nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) < 91"; sql += " and trim(e.branch_code_dsa_code) = '" + branch.ToUpper() + "'";// --set for choosing branch"; if (cbCycle.SelectedValue.ToString().ToUpper() != "000")//000: All sql += " and a.cycoff_code = '" + cbCycle.SelectedValue.ToString().ToUpper() + "'"; title = "DANH SÁCH CÁC THẺ CẦN GỬI THƯ LẦN 2"; } else if (type.ToUpper() == "CR_ADC") { //sql = " select a.cr_account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status,to_char(b.payment_date,'dd/mm/yyyy') payment_date,'' as number_days,'' as Ghi_chu"; //old 2014-04-01 (1) if (Frm_main.admin_flag) { sql = " select distinct a.cr_account_nbr account_nbr,a.cr_account_nbr , e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status,to_char(b.payment_date,'dd/mm/yyyy') payment_date,'' as number_days,'' as Ghi_chu"; //old 2014-04-01 (1) } else { sql = " select distinct a.cr_account_nbr account_nbr,substr(a.cr_account_nbr,1,4)||'xxxxxxxx'||substr(a.cr_account_nbr,-4) cr_account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status,to_char(b.payment_date,'dd/mm/yyyy') payment_date,'' as number_days,'' as Ghi_chu"; //old 2014-04-01 (1) } sql += " from " + Businessbp.executedb.owner + "account a, " + Businessbp.executedb.owner + "cr_term b, " + Businessbp.executedb.owner + "card c, " + Businessbp.executedb.owner + "application d, " + Businessbp.executedb.owner + "cp_card e"; sql += " where a.cr_account_nbr = b.cr_account_nbr"; sql += " and a.cr_account_nbr = c.card_number"; sql += " and c.card_number = d.card_number"; sql += " and d.application_no = e.source_application_no"; // sql += " and nvl(a.acc_unpaid_status, '0') <> '0'"; sql += " and b.stat_date = ("; sql += " select max(f.stat_date)"; sql += " from " + Businessbp.executedb.owner + "cr_term f"; sql += " where f.cr_account_nbr = a.cr_account_nbr"; sql += " )"; sql += " and c.status_code in ('0','3','9','10') "; //Phong edit 15/06/2013 sql += " and nvl(" + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) ,0) >= 3"; sql += " and nvl(a.acc_adm_status,0) = 0 "; sql += " and trim(e.branch_code_dsa_code) = '" + branch.ToUpper() + "'";// --set for choosing branch"; if (cbCycle.SelectedValue.ToString().ToUpper() != "000")//000: All sql += " and a.cycoff_code = '" + cbCycle.SelectedValue.ToString().ToUpper() + "'"; title = "DANH SÁCH CÁC THẺ CẦN KHÓA ADMIN STATUS"; } else if (type.ToUpper() == "CR_LV1") { //sql = " select a.cr_account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') payment_date,"; //old 2014-04-01 (1) if (Frm_main.admin_flag) { sql = " select distinct a.cr_account_nbr account_nbr,a.cr_account_nbr , e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') payment_date,"; //2014-04-01 (1) } else { sql = " select distinct a.cr_account_nbr account_nbr,substr(a.cr_account_nbr,1,4)||'xxxxxxxx'||substr(a.cr_account_nbr,-4) cr_account_nbr,e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') payment_date,"; //2014-04-01 (1) } sql += " nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) number_days,'' as Ghi_chu"; sql += " from " + Businessbp.executedb.owner + "account a, " + Businessbp.executedb.owner + "cr_term b, " + Businessbp.executedb.owner + "card c, " + Businessbp.executedb.owner + "application d, " + Businessbp.executedb.owner + "cp_card e"; sql += " where a.cr_account_nbr = b.cr_account_nbr"; sql += " and a.cr_account_nbr = c.card_number"; sql += " and c.card_number = d.card_number"; sql += " and d.application_no = e.source_application_no"; // sql += " and nvl(a.acc_unpaid_status, '0') <> '0'"; sql += " and " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) <> '0' "; sql += " and b.stat_date = ("; sql += " select max(f.stat_date)"; sql += " from " + Businessbp.executedb.owner + "cr_term f"; sql += " where f.cr_account_nbr = a.cr_account_nbr"; sql += " )"; sql += " and c.status_code in ('0','3','9','10') "; //Phong edit 15/06/2013 sql += " and nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) >= 91"; sql += " and nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) < 121 "; sql += " and trim(e.branch_code_dsa_code) = '" + branch.ToUpper() + "'";// --set for choosing branch"; if (cbCycle.SelectedValue.ToString().ToUpper() != "000")//000: All sql += " and a.cycoff_code = '" + cbCycle.SelectedValue.ToString().ToUpper() + "'"; title = "DANH SÁCH CÁC THẺ CẦN LÀM VIỆC TRỰC TIẾP"; } else if (type.ToUpper() == "CR_TD1") { //sql = " select a.cr_account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') payment_date,";//old 2014-04-01 (1) if (Frm_main.admin_flag) { sql = " select distinct a.cr_account_nbr account_nbr,a.cr_account_nbr , e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') payment_date,";//2014-04-01 (1) } else { sql = " select distinct a.cr_account_nbr account_nbr,substr(a.cr_account_nbr,1,4)||'xxxxxxxx'||substr(a.cr_account_nbr,-4) cr_account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') payment_date,";//2014-04-01 (1) } sql += " nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) number_days,'' as Ghi_chu"; sql += " from " + Businessbp.executedb.owner + "account a, " + Businessbp.executedb.owner + "cr_term b, " + Businessbp.executedb.owner + "card c, " + Businessbp.executedb.owner + "application d, " + Businessbp.executedb.owner + "cp_card e"; sql += " where a.cr_account_nbr = b.cr_account_nbr"; sql += " and a.cr_account_nbr = c.card_number"; sql += " and c.card_number = d.card_number"; sql += " and d.application_no = e.source_application_no"; // sql += " and nvl(a.acc_unpaid_status, '0') <> '0'"; sql += " and " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) <> '0' "; sql += " and b.stat_date = ("; sql += " select max(f.stat_date)"; sql += " from " + Businessbp.executedb.owner + "cr_term f"; sql += " where f.cr_account_nbr = a.cr_account_nbr"; sql += " )"; sql += " and c.status_code in ('0','3','9','10') "; //Phong edit 15/06/2013 sql += " and nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) >= 121"; sql += " and nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) <= 150"; sql += " and a.acc_adm_status not in ('3')"; //sql += " and c.embossed_name not in ('%NQH-CD')"; sql += " and trim(e.branch_code_dsa_code) = '" + branch.ToUpper() + "'";// --set for choosing branch"; if (cbCycle.SelectedValue.ToString().ToUpper() != "000")//000: All sql += " and a.cycoff_code = '" + cbCycle.SelectedValue.ToString().ToUpper() + "'"; title = "DANH SÁCH CÁC THẺ CẦN TÁI THẨM ĐỊNH"; } else if (type.ToUpper() == "MB-TNT01") { //sql = "select a.cr_account_nbr, e.branch_code_dsa_code branch_code,";//old 2014-04-01 (1) if (Frm_main.admin_flag) { sql = "select distinct a.cr_account_nbr account_nbr,a.cr_account_nbr , e.branch_code_dsa_code branch_code,";// 2014-04-01 (1) } else { sql = "select distinct a.cr_account_nbr account_nbr,substr(a.cr_account_nbr,1,4)||'xxxxxxxx'||substr(a.cr_account_nbr,-4) cr_account_nbr, e.branch_code_dsa_code branch_code,";// 2014-04-01 (1) } sql += " decode(e.ADDON_CARD_FLAG, '1', nvl(("; sql += " select aa.embossed_name"; sql += " from " + Businessbp.executedb.owner + "card aa"; sql += " where aa.card_number = e.ADDON_CARD_NUMBER"; sql += " ),' ')"; sql += " , c.embossed_name) embossed_name,";//Lấy embossing name cho thẻ phụ sql += " c.family_name, b.opening_bal, b.closing_bal, b.min_due," + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') payment_date,"; sql += " nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) number_days,'' as Ghi_chu"; sql += " from " + Businessbp.executedb.owner + "account a, " + Businessbp.executedb.owner + "cr_term b, " + Businessbp.executedb.owner + "card c, " + Businessbp.executedb.owner + "application d, " + Businessbp.executedb.owner + "cp_card e"; sql += " where a.cr_account_nbr = b.cr_account_nbr"; sql += " and a.cr_account_nbr = c.card_number"; sql += " and c.card_number = d.card_number"; sql += " and d.application_no = e.source_application_no"; //sql += " and nvl(a.acc_unpaid_status, '0') <> '0'"; sql += " and b.stat_date = ("; sql += " select max(f.stat_date)"; sql += " from " + Businessbp.executedb.owner + "cr_term f"; sql += " where f.cr_account_nbr = a.cr_account_nbr"; sql += " )"; sql += " and c.status_code in ('0','3','9','10') "; //Phong edit 15/06/2013 sql += " and b.min_due>0 "; sql += " and nvl(b.closing_bal,0)>0 "; sql += " and trunc(b.stat_date)<=trunc(sysdate) ";// Sau ngay chay mounthly sql += " and trunc(sysdate)<= trunc(nvl(b.payment_date,sysdate)) ";// Nho hon ngay T sql += " and a.cr_account_nbr not in (select r.cr_account_nbr from " + Businessbp.executedb.owner + "nab_credit_unpaid r) "; sql += " and trim(e.branch_code_dsa_code) = '" + branch.ToUpper() + "'";// --set for choosing branch"; if (cbCycle.SelectedValue.ToString().ToUpper() != "000")//000: All sql += " and a.cycoff_code = '" + cbCycle.SelectedValue.ToString().ToUpper() + "'"; title = "DANH SÁCH CÁC THẺ CẦN NHẮC NỢ"; } ////else if (cbOption.SelectedValue.ToString().ToUpper() == "MB-CNQH") ////{ //// Session["type"] = "MB-CNQH"; //// //sql += " select a.cr_account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, a.acc_unpaid_status, b.payment_date,"; //// //sql += " ((sysdate - nvl(a.acc_unpaid_status_date, sysdate)) + (nvl(a.acc_unpaid_status,0) - 1)*30) number_days,'' as Ghi_chu"; //// sql = " select to_char(rownum) as STT,a.cr_account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, e.legal_id as CMND "; //// sql += " ,(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)) as du_no"; //// sql += " , b.opening_bal, b.closing_bal, b.min_due, a.acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') as payment_date"; //// sql += " ,a.cycoff_code"; //// sql += " ,round(((sysdate - nvl(a.acc_unpaid_status_date, sysdate)) + (nvl(a.acc_unpaid_status,0) - 1)*30)) number_days,'' as Ghi_chu"; //// sql += " from " + executedb.owner + "account a, " + executedb.owner + "cr_term b, card c, " + executedb.owner + "application d, " + executedb.owner + "cp_card e"; //// sql += " where a.cr_account_nbr = b.cr_account_nbr"; //// sql += " and a.cr_account_nbr = c.card_number"; //// sql += " and c.card_number = d.card_number"; //// sql += " and d.application_no = e.source_application_no"; //// sql += " and b.stat_date = ("; //// sql += " select max(f.stat_date)"; //// sql += " from " + executedb.owner + "cr_term f"; //// sql += " where f.cr_account_nbr = a.cr_account_nbr"; //// sql += " )"; //// sql += " and ((sysdate - nvl(a.acc_unpaid_status_date, sysdate)) + (nvl(a.acc_unpaid_status,0) - 1)*30) >= 106"; //// sql += " and not exists ("; //// sql += " select 1 "; //// sql += " from " + executedb.owner + "nab_credit_unpaid v"; //// sql += " where v.cr_account_nbr = a.cr_account_nbr"; //// sql += " )"; //// if (cbBranch.SelectedValue.ToString().ToUpper() != "9999")//9999: all //// sql += " and e.branch_code_dsa_code = '" + cbBranch.SelectedValue.ToString().ToUpper() + "'";// --set for choosing branch"; //// if (tbAccount.Text.Trim() != "") //// sql += " and a.cr_account_nbr = '" + tbAccount.Text.Trim() + "'";// --set for choosing account"; //// if (cbCycle.SelectedValue.ToString().ToUpper() != "000")//000: All //// sql += " and a.cycoff_code = '" + cbCycle.SelectedValue.ToString().ToUpper() + "'"; //// Session["sql"] = sql; //// Session["title"] = "DANH SÁCH KHÁCH HÀNG CẦN CHUYỂN NỢ QUÁ HẠN NHƯNG CHƯA CHUYỂN"; //// //Session["fromtime"] = dpFromDate.Text; //// // Session["totime"] = dpToDate.Text; //// Response.Write("<script type='text/javascript'>window.open('reportview.aspx','window_name','scrollbars=yes,menubar=no,height=700,width=785,resizable=yes,toolbar=no,location=no,status=no,fullscreen=no,titlebar=no,directories=no');</script>"); ////} else if (type.ToUpper() == "MB-TNT09") { //sql += " select a.cr_account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, c.family_name, b.opening_bal, b.closing_bal, b.min_due, a.acc_unpaid_status, b.payment_date,"; //sql += " ((sysdate - nvl(a.acc_unpaid_status_date, sysdate)) + (nvl(a.acc_unpaid_status,0) - 1)*30) number_days,'' as Ghi_chu"; if (Frm_main.admin_flag) { sql = " select to_char(rownum) as STT,a.cr_account_nbr,a.cr_account_nbr account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, e.legal_id as CMND "; } else { sql = " select to_char(rownum) as STT,substr(a.cr_account_nbr,1,4)||'xxxxxxxx'||substr(a.cr_account_nbr,-4) cr_account_nbr ,a.cr_account_nbr account_nbr, e.branch_code_dsa_code branch_code, c.embossed_name, e.legal_id as CMND "; } sql += " ,(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)) as du_no"; sql += " , b.opening_bal, b.closing_bal, b.min_due, " + Businessbp.executedb.owner + "nab_getUnpaidStatus(a.cr_account_nbr) acc_unpaid_status, to_char(b.payment_date,'dd/mm/yyyy') as payment_date"; sql += " ,a.cycoff_code"; sql += " ,nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) number_days,'' as Ghi_chu"; sql += " from " + Businessbp.executedb.owner + "account a, " + Businessbp.executedb.owner + "cr_term b, card c, " + Businessbp.executedb.owner + "application d, " + Businessbp.executedb.owner + "cp_card e"; sql += " where a.cr_account_nbr = b.cr_account_nbr"; sql += " and a.cr_account_nbr = c.card_number"; sql += " and c.card_number = d.card_number"; sql += " and d.application_no = e.source_application_no"; sql += " and b.stat_date = ("; sql += " select max(f.stat_date)"; sql += " from " + Businessbp.executedb.owner + "cr_term f"; sql += " where f.cr_account_nbr = a.cr_account_nbr"; sql += " )"; sql += " and c.status_code in ('0','3','9','10') "; //Phong edit 15/06/2013 //sql += " and ((sysdate - nvl(a.acc_unpaid_status_date, sysdate)) + (nvl(a.acc_unpaid_status,0) - 1)*30) >= 91"; sql += " and nvl(" + Businessbp.executedb.owner + "nab_getThucNoDays(a.cr_account_nbr),0) >= 106"; sql += " and not exists ("; sql += " select 1 "; sql += " from " + Businessbp.executedb.owner + "nab_credit_unpaid v"; sql += " where v.cr_account_nbr = a.cr_account_nbr"; sql += " )"; sql += " and trim(e.branch_code_dsa_code) = '" + branch.ToUpper() + "'";// --set for choosing branch"; if (cbCycle.SelectedValue.ToString().ToUpper() != "000")//000: All sql += " and a.cycoff_code = '" + cbCycle.SelectedValue.ToString().ToUpper() + "'"; title = "DANH SÁCH KHÁCH HÀNG CẦN CHUYỂN NỢ QUÁ HẠN NHƯNG CHƯA CHUYỂN"; } #endregion chualam if (sql != "") { DataTable dtReport = Businessbp.executedb.getTable(sql, ref err); dtReport.Columns.Remove("account_nbr"); if (err != "") { MessageBox.Show(err); this.Cursor = Cursors.Default; return; } else { string file_path = tbFilePath.Text.Trim() + branch.Trim() + "_" + type + "_" + DateTime.Now.ToString("ddMMyyyy") + ".pdf"; //Export pdf try { if (dtReport.Rows.Count > 0) { 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 " + Businessbp.executedb.owner + "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"; } } }
public virtual CrystalDecisions.CrystalReports.Engine.ReportDocument CreateReport() { rpt_Xu_Ly_No rpt = new rpt_Xu_Ly_No(); rpt.Site = this.Site; return rpt; }