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