private void button1_Click(object sender, EventArgs e)
{
/* Điều kiện kiểm tra
* B1: kiểm tra trong ổ D:\KTT\SEND_EMAIL\BTBGD\yyyymmdd đã xử lý với ngày hiện tại chưa
* Nếu đã xử lý: Không cho xử lý nữa
* B2: Kiểm tra trong bảng NAB_CREDIT_SENDMAIL đã xử lý với ngày hiện tại chưa? Nếu rồi--> Không xử lý
* Xử lý tạo file pdf và lưu vào đường dẫn trên D:\KTT\SEND_EMAIL\BTBGD\yyyymmdd\số thẻ.pdf
* */
//B1: Kiểm tra đường dẫn đã tồn tại chưa?
this.Cursor = Cursors.WaitCursor;
string folderOut = tbFilePath.Text.Trim();
if (Directory.Exists(folderOut))
{
MessageBox.Show("Đã xử lý SendMail ngày " + DateTime.Now.ToString("yyyyMMdd"));
this.Cursor = Cursors.Default;
return;
// Directory.CreateDirectory(folderOut);
}
string sql = "", err = "";
sql += " select * from " + Businessbp.executedb.owner + "NAB_CREDIT_SENDMAIL a";
sql += " where trunc(a.DATE_CREATE)= trunc(sysdate)";
//sql += " and trunc(sysdate)>=(select trunc(max(b.stat_date)) from cr_term b)";
DataTable dt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
this.Cursor = Cursors.Default;
return;
}
else
{
if (dt.Rows.Count > 0)
{
MessageBox.Show("Đã xử lý SendMail ngày " + DateTime.Now.ToString("yyyyMMdd"));
this.Cursor = Cursors.Default;
return;
}
else
{
//Xử lý và insert vào table
//tạo thư mục
Directory.CreateDirectory(folderOut);
try
{
sql = ""; err = "";
#region 2014-10-28 (6) old
//sql = " select distinct a.cr_account_nbr ";
//sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log a";
//sql += " where trunc(a.stat_date)=trunc(to_date('" + dateTimePicker1.Text.Trim() + "','dd/mm/yyyy'))";
//sql += " order by a.cr_account_nbr asc";
#endregion 2014-10-28 (6) old
#region 2014-10-28 (6) new
sql += "select distinct a.cr_account_nbr";
sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log a, " + Businessbp.executedb.owner + "card c";
sql += " where a.card_number = c.card_number";
sql += " and trunc(a.stat_date) = trunc(to_date('" + dateTimePicker1.Text.Trim() + "', 'dd/mm/yyyy'))";
sql += " and trim(c.email_id) is not null";
//sql += " and a.card_number ='5331470000139786'";
sql += " order by a.cr_account_nbr asc";
#endregion 2014-10-28 (6) new
DataTable dt_card_process = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
this.Cursor = Cursors.Default;
return;
}
else
{
if (dt_card_process.Rows.Count <= 0)
{
MessageBox.Show("Chưa xử lý cuối ngày.");
this.Cursor = Cursors.Default;
return;
}
else
{
DataTable dt_bill = new DataTable();
for (int i = 0; i < dt_card_process.Rows.Count; i++)
{
//string card_number = dt_card_process.Rows[i]["card_number"].ToString();
string cr_account_nbr = dt_card_process.Rows[i]["cr_account_nbr"].ToString();
sql = ""; err = "";
#region old 2014-06-19 (2)
/*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,nvl(a.billing_amount,0) billing_amount";
sql += " ,(a.MAILING_ADDRESS1||' '||a.MAILING_ADDRESS2||' '||a.MAILING_ADDRESS3||' '||a.MAILING_ADDRESS4) address";
sql += " ,a.PHONE_HOME, a.PHONE_MOBILE, a.cr_account_nbr, a.transaction_amount ";
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";
sql += " where trunc(a.stat_date)=trunc(to_date('" + dateTimePicker1.Text.Trim() + "','dd/mm/yyyy'))";
//sql += " and a.card_number='" + card_number.Trim() + "'";
sql += " and a.cr_account_nbr='" + cr_account_nbr.Trim() + "'";
sql += " order by a.card_number asc,a.transaction_date asc";*/
#endregion old 2014-06-19 (2)
//dt_bill = null;
#region 2015-05-20 (10) comment
//#region 2014-06-19 (2)
//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 (9)
//sql += " to_char(nvl(r.cashback_amt, 0), '999,999,999,999') cashback"; //2015-03-19 (8)
//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, "; //old 2014-08-26 (3)
//sql += " to_char(a.stat_date, 'dd/mm/yyyy') as processing_date, "; // 2014-08-26 (3)
//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 (8)
////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 (8)
//#region 2015-04-22 (9)
//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 (9)
//sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log a, " + Businessbp.executedb.owner + "trans_def_stand e ";
//sql += " where 1 = 1 ";
//sql += " and a.transaction_code = e.transaction_code ";
//sql += " and trunc(a.stat_date) = ";
//sql += " trunc(to_date('" + dateTimePicker1.Text.Trim() + "', 'dd/mm/yyyy')) ";
//sql += " and a.cr_account_nbr = '" + cr_account_nbr.Trim() + "' ";
//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, "; //old 2014-08-26 (3)
//sql += " to_char(a.stat_date, 'dd/mm/yyyy') as processing_date, "; //2014-08-26 (3)
//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 (8)
////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 (8)
//#region 2015-04-22 (9)
//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 (9)
//sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log a ";
//sql += " where trunc(a.stat_date) = ";
//sql += " trunc(to_date('" + dateTimePicker1.Text.Trim() + "', 'dd/mm/yyyy')) ";
//sql += " and trim(a.transaction_code) is null ";
//sql += " and a.cr_account_nbr = '" + cr_account_nbr.Trim() + "') r , ";
//#region 2014-10-03 (5)
////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 (5)
//#region 2014-10-28 (7)
//sql += " (select distinct l.cr_account_nbr ";
//sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log l ";
//sql += " where 1 = 1 ";
//sql += " and trunc(l.stat_date) = to_date('" + dateTimePicker1.Text.Trim() + "', 'dd/mm/yyyy') ";
//sql += " and l.cr_account_nbr = '" + cr_account_nbr.Trim() + "' ";
//sql += " and (l.closing_bal <> 0 or ";
//sql += " l.cr_account_nbr = ";
//sql += " (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() + "', ";
//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 = 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 += " and s.cr_account_nbr = '" + cr_account_nbr.Trim() + "'))) t ";
//sql += " where 1 = 1 ";
//sql += " and r.cr_account_nbr = t.cr_account_nbr ";
//#endregion 2014-10-28 (7)
//////sql += " order by r.card_number asc, to_date(r.transaction_date,'dd/mm/yyyy') asc "; // 2014-08-26 (3)
////sql += " order by r.card_number asc, to_date(r.transaction_date,'dd/mm/yyyy') desc "; // 2014-10-03 (4)
////#endregion 2014-06-19 (2)
//#endregion 2015-05-20 (10) comment
#endregion 2015-05-20 (10) 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 (10)
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";
sql += " from (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 += " 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 += " 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'))";
sql += " and a.cr_account_nbr = '" + cr_account_nbr.Trim() + "'";
sql += " UNION ALL";
sql += " select a.card_number,";
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 trunc(a.stat_date) =";
sql += " trunc(to_date('" + dateTimePicker1.Text.Trim() + "', 'dd/mm/yyyy'))";
sql += " and a.cr_account_nbr = app.card_number";
sql += " and app.application_no = c.source_application_no";
sql += " and trim(a.transaction_code) is null";
sql += " and a.cr_account_nbr = '" + cr_account_nbr.Trim() + "') r,";
sql += " (select distinct 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.cr_account_nbr = '" + cr_account_nbr.Trim() + "'";
sql += " and (l.closing_bal <> 0 or";
sql += " l.cr_account_nbr =";
sql += " (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 += " and s.cr_account_nbr = '" + cr_account_nbr.Trim() + "'))) t";
sql += " where 1 = 1";
sql += " and r.cr_account_nbr = t.cr_account_nbr";
sql += " order by r.card_number asc,";
sql += " to_date(r.transaction_date, 'dd/mm/yyyy') desc) A";
sql += " order by A.card_number asc, to_date(A.transaction_date, 'dd/mm/yyyy') desc ";//2015-06-23 (11)
#endregion 2015-05-20 (10)
}
#region 2015-09-08 (12)
else
{
sql = " select (substr(l.card_number,1,4) || ' '||substr(l.card_number,5,2) || 'xx xxxx '||substr(l.card_number,13,4)) card_number, ";
sql += " (substr(l.cr_account_nbr,1,4) || ' '||substr(l.cr_account_nbr,5,2) || 'xx xxxx '||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 += " 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) //old 2015-11-30 (14)
sql += " and (trim(l.arn) is not null or (l.total_debit+l.fee+l.bal_payment+abs(l.closing_bal)) <>0 ) "; //2015-11-30 (14)
sql += " and trim(c.cr_account_nbr)='" + cr_account_nbr.Trim() + "' ";
sql += " order by 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)
dt_bill.Clear();
dt_bill = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
this.Cursor = Cursors.Default;
return;
}
else
{
//dt_bill.Columns.Add("card_number", typeof(string));//card_number
//dt_bill.Columns.Add("embossed_name", typeof(string));//embossed_name
//dt_bill.Columns.Add("credit_limit", typeof(string));//credit_limit
//dt_bill.Columns.Add("processing_date", typeof(string));//processing_date
//dt_bill.Columns.Add("payment_date", typeof(string));//payment_date
//dt_bill.Columns.Add("opening_bal", typeof(string));//opening_bal
//dt_bill.Columns.Add("total_debit", typeof(string));//total_debit
//dt_bill.Columns.Add("bal_payment", typeof(string));//bal_payment
//dt_bill.Columns.Add("closing_bal", typeof(string));//closing_bal
//dt_bill.Columns.Add("min_due", typeof(string));//min_due
//dt_bill.Columns.Add("transaction_date", typeof(string));//transaction_date
//dt_bill.Columns.Add("value_date", typeof(string));//value_date
//dt_bill.Columns.Add("arn", typeof(string));
//dt_bill.Columns.Add("details", typeof(string));
//dt_bill.Columns.Add("billing_amount", typeof(string));
////get data
//string card_number = dt_card_process.Rows[i]["card_number"].ToString();
//string embossed_name = dt_card_process.Rows[i]["embossed_name"].ToString();
//string credit_limit = dt_card_process.Rows[i]["credit_limit"].ToString();
//string processing_date = dt_card_process.Rows[i]["processing_date"].ToString();
//string payment_date = dt_card_process.Rows[i]["payment_date"].ToString();
//string opening_bal = dt_card_process.Rows[i]["opening_bal"].ToString();
//string total_debit = dt_card_process.Rows[i]["total_debit"].ToString();
//string bal_payment = dt_card_process.Rows[i]["bal_payment"].ToString();
//string closing_bal = dt_card_process.Rows[i]["closing_bal"].ToString();
//string min_due = dt_card_process.Rows[i]["min_due"].ToString();
//string transaction_date = dt_card_process.Rows[i]["transaction_date"].ToString();
//string value_date = dt_card_process.Rows[i]["value_date"].ToString();
//string arn = dt_card_process.Rows[i]["arn"].ToString();
//string details = dt_card_process.Rows[i]["details"].ToString();
//string billing_amount = dt_card_process.Rows[i]["billing_amount"].ToString();
//try
//{
// dt_bill.Rows.Add(card_number, embossed_name, credit_limit, processing_date, payment_date, opening_bal, total_debit, bal_payment
// , closing_bal, min_due, transaction_date, value_date, arn, details, billing_amount);
//}
//catch (Exception ex)
//{
// MessageBox.Show(ex.ToString());
//}
//string file_path = tbFilePath.Text.Trim() + "\\" + card_number.Trim() + ".pdf";
string file_path = tbFilePath.Text.Trim() + "\\" + cr_account_nbr.Trim() + ".pdf";
//Export pdf
try
{
if (dt_bill.Rows.Count > 0)
{
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)
{
//Rpt_Credit_Billing_new cry = new Rpt_Credit_Billing_new(); //2015-03-19 (8) old
Rpt_Credit_Billing_new_2 cry = new Rpt_Credit_Billing_new_2(); //2015-03-19 (8) new
cry.SetDataSource(dt_bill);
string title = "";
string title2 = "";
string title3 = "";
title = "BẢNG THÔNG BÁO GIAO DỊCH";
title2 = "(Từ ngày " + dateTimePicker1.Value.AddMonths(-1).ToString("dd/MM/yyyy").Trim() + " đến ngày " + dateTimePicker1.Text.Trim() + " )";
cry.SetParameterValue("title", title);
cry.SetParameterValue("title2", title2);
title3 = Get_title3(cr_account_nbr);
if (title3.Trim() != "")
cry.SetParameterValue("title3", title3);
else
{
MessageBox.Show("Err when get title3");
return;
}
// a.title3 = "Yêu cầu thanh toán trước 10h ngày:";
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.DestinationOptions = CrDiskFileDestinationOptions;
CrExportOptions.FormatOptions = CrFormatTypeOptions;
}
cry.Export();
//--Fix for Load Report failse
if (cry != null)
{
cry.Close();
cry.Dispose();
}
}
#region 2015-09-08 (12)
else
{
Rpt_Credit_Billing_new_3 cry = new Rpt_Credit_Billing_new_3();
cry.SetDataSource(dt_bill);
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.DestinationOptions = CrDiskFileDestinationOptions;
CrExportOptions.FormatOptions = CrFormatTypeOptions;
}
cry.Export();
//--Fix for Load Report failse
if (cry != null)
{
cry.Close();
cry.Dispose();
}
}
#endregion 2015-09-08 (12)
//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.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 += " select distinct to_char(a.stat_date,'dd/mm/yyyy') DATE_START,c.email_id EMAIL,to_char(a.stat_date,'dd/mm/yyyy') BILL_DATE";
//sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log a," + Businessbp.executedb.owner + "application b, " + Businessbp.executedb.owner + "cp_card c";
//sql += " where a.card_number=b.card_number";
//sql += " and b.application_no=c.source_application_no";
//sql += " and trunc(a.stat_date) =trunc(to_date('" + dateTimePicker1.Text.Trim() + "','dd/mm/yyyy'))";
//sql += " and a.card_number='" + card_number.Trim() + "'";
//sql += " and c.email_id is not null";
//sql += " select distinct to_char(a.stat_date,'dd/mm/yyyy') DATE_START,e.email_id EMAIL,to_char(a.stat_date,'dd/mm/yyyy') BILL_DATE,a.card_number";
//sql += " select distinct to_char(a.stat_date,'dd/mm/yyyy') DATE_START,e.email_id EMAIL,to_char(a.stat_date,'dd/mm/yyyy') BILL_DATE,a.cr_account_nbr";
//sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log a," + Businessbp.executedb.owner + "card e";
//sql += " where a.cr_account_nbr=e.card_number"; // 2016-03-04 (15)
//sql += " and trunc(a.stat_date) =trunc(to_date('" + dateTimePicker1.Text.Trim() + "','dd/mm/yyyy'))";
////sql += " and e.email_id is not null"; //OLD 2014-05-08 (1)
//sql += " and trim(e.email_id) is not null"; // 2014-05-08 (1)
////sql += " and a.card_number='" + card_number.Trim() + "'";
//sql += " and a.cr_account_nbr='" + cr_account_nbr.Trim() + "'";
#region 2016-06-16 (16)
sql += "select distinct to_char(a.stat_date, 'dd/mm/yyyy') DATE_START, ";
sql += " e.email_id EMAIL, ";
sql += " to_char(a.stat_date, 'dd/mm/yyyy') BILL_DATE, ";
sql += " a.cr_account_nbr, ";
sql += " cor.email_id EMAILCOR ";
sql += " from " + Businessbp.executedb.owner + "nab_billing_credit_log a ";
sql += " left join " + Businessbp.executedb.owner + "nab_corporate_card cor ";
sql += " on a.cr_account_nbr = cor.cr_account_number_corporate ";
sql += " and trim(cor.email_id) is not null, " + Businessbp.executedb.owner + "card e ";
sql += " where a.cr_account_nbr = e.card_number ";
sql += " and trunc(a.stat_date) =trunc(to_date('" + dateTimePicker1.Text.Trim ( ) + "','dd/mm/yyyy'))";
sql += " and trim(e.email_id) is not null ";
sql += " and a.cr_account_nbr='" + cr_account_nbr.Trim ( ) + "'";
#endregion 2016-06-16 (16)
sql += " ";
DataTable dtInsert = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show(err);
// return;
}
else
{
if (dtInsert.Rows.Count > 0)
{
string DATE_START = dtInsert.Rows[0]["DATE_START"].ToString().Trim();
string EMAIL = dtInsert.Rows[0]["EMAIL"].ToString().Trim();
string BILL_DATE = dtInsert.Rows[0]["BILL_DATE"].ToString().Trim();
//
sql = ""; err = "";
if (EMAIL.Trim() == "")
{ }
else
{
if (!cr_account_nbr.Substring ( 0, 4 ).Trim ( ).Equals ( "5592" ))
{
sql = "insert into " + Businessbp.executedb.owner + "NAB_CREDIT_SENDMAIL(DATE_START ,CARD_NUMBER ,FILE_NAME ,EMAIL,BILL_DATE ,SEND_FLAG, USER_CREATE , DATE_CREATE)";
sql += "values (";
sql += "" + ( DATE_START.Trim ( ) == "" ? "null" : ( "to_date('" + DATE_START + "','dd/MM/yyyy')" ) ) + ",";
//sql += "'" + card_number + "',";
sql += "'" + cr_account_nbr + "',";
sql += "'" + file_path + "',";
sql += "" + ( EMAIL.Trim ( ) == "" ? "null" : ( "'" + EMAIL + "'" ) ) + ",";
sql += "" + ( BILL_DATE == "" ? "null" : ( "to_date('" + BILL_DATE + "','dd/mm/yyyy')" ) ) + ",";
sql += "'0',";
sql += "'" + Businessbp.executedb.Usrid + "',";
sql += "sysdate";
sql += ")";
Businessbp.executedb.ExecuteDML ( sql, ref err );
if (err != "")
{
MessageBox.Show ( err ); //return;
}
}
else //2016-06-16 (16)
{
string EMAILCOR = dtInsert.Rows[0]["EMAILCOR"].ToString ( ).Trim ( );
if (EMAILCOR.Trim ( ) != "")
{
sql = " begin ";
sql += " insert into " + Businessbp.executedb.owner + "NAB_CREDIT_SENDMAIL(DATE_START ,CARD_NUMBER ,FILE_NAME ,EMAIL,BILL_DATE ,SEND_FLAG, USER_CREATE , DATE_CREATE)";
sql += " values (";
sql += "" + ( DATE_START.Trim ( ) == "" ? "null" : ( "to_date('" + DATE_START + "','dd/MM/yyyy')" ) ) + ",";
//sql += "'" + card_number + "',";
sql += "'" + cr_account_nbr + "',";
sql += "'" + file_path + "',";
sql += "" + ( EMAILCOR.Trim ( ) == "" ? "null" : ( "'" + EMAILCOR.Trim ( ) + "'" ) ) + ",";
sql += "" + ( BILL_DATE == "" ? "null" : ( "to_date('" + BILL_DATE + "','dd/mm/yyyy')" ) ) + ",";
sql += "'0',";
sql += "'" + Businessbp.executedb.Usrid + "',";
sql += "sysdate";
sql += ");";
sql += " insert into " + Businessbp.executedb.owner + "NAB_CREDIT_SENDMAIL(DATE_START ,CARD_NUMBER ,FILE_NAME ,EMAIL,BILL_DATE ,SEND_FLAG, USER_CREATE , DATE_CREATE)";
sql += " values (";
sql += "" + ( DATE_START.Trim ( ) == "" ? "null" : ( "to_date('" + DATE_START + "','dd/MM/yyyy')" ) ) + ",";
//sql += "'" + card_number + "',";
sql += "'" + cr_account_nbr + "',";
sql += "'" + file_path + "',";
sql += "" + ( EMAIL.Trim ( ) == "" ? "null" : ( "'" + EMAIL + "'" ) ) + ",";
sql += "" + ( BILL_DATE == "" ? "null" : ( "to_date('" + BILL_DATE + "','dd/mm/yyyy')" ) ) + ",";
sql += "'0',";
sql += "'" + Businessbp.executedb.Usrid + "',";
sql += "sysdate";
sql += ");";
sql += " insert into " + Businessbp.executedb.owner + "NAB_CREDIT_SENDMAIL(DATE_START ,CARD_NUMBER ,FILE_NAME ,EMAIL,BILL_DATE ,SEND_FLAG, USER_CREATE , DATE_CREATE)";
sql += " values (";
sql += "" + ( DATE_START.Trim ( ) == "" ? "null" : ( "to_date('" + DATE_START + "','dd/MM/yyyy')" ) ) + ",";
//sql += "'" + card_number + "',";
sql += "'" + cr_account_nbr + "',";
sql += "'" + file_path + "',";
sql += " '*****@*****.**',";
sql += "" + ( BILL_DATE == "" ? "null" : ( "to_date('" + BILL_DATE + "','dd/mm/yyyy')" ) ) + ",";
sql += "'0',";
sql += "'" + Businessbp.executedb.Usrid + "',";
sql += "sysdate";
sql += ");";
sql += " commit;";
sql += " exception";
sql += " when others then";
sql += " rollback;";
sql += " end;";
Businessbp.executedb.ExecuteDML ( sql, ref err );
if (err != "")
{
MessageBox.Show ( err ); //return;
}
}
}
}
}
else//Nếu không có dữ liệu
{
}
}
}
else
{
//MessageBox.Show("" + card_number.Trim());
//MessageBox.Show("" + cr_account_nbr.Trim()); //2014-10-28 Dong tam thoi MessageBox
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
tbStatus.Text = "Successful!!!";
this.Cursor = Cursors.Default;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
}
public virtual CrystalDecisions.CrystalReports.Engine.ReportDocument CreateReport()
{
Rpt_Credit_Billing_new_2 rpt = new Rpt_Credit_Billing_new_2();
rpt.Site = this.Site;
return rpt;
}
private void Frm_Report_Load(object sender, EventArgs e)
{
if (type == "1")
{
Rp_POS_Monitor cry = new Rp_POS_Monitor();
cry.SetDataSource(dt);
crystalReportViewer1.ReportSource = cry;
}
else if (type == "2")
{
Rp_POS_Activity cry = new Rp_POS_Activity();
cry.SetDataSource(dt);
crystalReportViewer1.ReportSource = cry;
}
else if (type == "3")
{
Rp_Chargeback_BNK cry = new Rp_Chargeback_BNK();
cry.SetDataSource(dt);
cry.SetParameterValue("Ngayin", DateTime.Now.ToShortDateString());
cry.SetParameterValue("Gioin", DateTime.Now.ToLongTimeString());
cry.SetParameterValue("Title", title);
crystalReportViewer1.ReportSource = cry;
}
else if (type == "4")
{
Rp_Card_Info cry = new Rp_Card_Info();
cry.SetDataSource(dt);
//cry.SetParameterValue("Ngayin", DateTime.Now.ToShortDateString());
//cry.SetParameterValue("Gioin", DateTime.Now.ToLongTimeString());
//cry.SetParameterValue("Thoigian", thoiGian);
cry.SetParameterValue("Title", title);
crystalReportViewer1.ReportSource = cry;
}
else if (type == "PHT05")
{
rpt_PHT05 cry = new rpt_PHT05();
cry.SetDataSource(dt);
//cry.SetParameterValue("@fromdate", DateTime.Now.ToShortDateString());
// cry.SetParameterValue("@todate", DateTime.Now.ToLongTimeString());
cry.SetParameterValue("ThoiGian", thoiGian);
cry.SetParameterValue("NhanVien", NhanVien);
cry.SetParameterValue("@User", Businessbp.executedb.Usrid);
crystalReportViewer1.ReportSource = cry;
}
else if (type == "PHT03")
{
rpt_PHT03 cry = new rpt_PHT03();
cry.SetDataSource(dt);
cry.SetParameterValue("ThoiGian", thoiGian);
//cry.SetParameterValue("NhanVien", NhanVien);
cry.SetParameterValue("@User", Businessbp.executedb.Usrid);
crystalReportViewer1.ReportSource = cry;
}
else if (type == "PHT04")
{
rpt_PHT04 cry = new rpt_PHT04();
cry.SetDataSource(dt);
cry.SetParameterValue("ThoiGian", thoiGian);
//cry.SetParameterValue("NhanVien", NhanVien);
cry.SetParameterValue("@User", Businessbp.executedb.Usrid);
crystalReportViewer1.ReportSource = cry;
}
else if (type == "PHT17")
{
rpt_PHT17 cry = new rpt_PHT17();
cry.SetDataSource(dt);
cry.SetParameterValue("ThoiGian", thoiGian);
cry.SetParameterValue("@User", Businessbp.executedb.Usrid);
crystalReportViewer1.ReportSource = cry;
}
else if (type == "POS_1")
{
Rp_POS_Issuance_GNDaiLy cry = new Rp_POS_Issuance_GNDaiLy();
cry.SetDataSource(dt);
cry.SetParameterValue("ThoiGian", thoiGian);
cry.SetParameterValue("Title", title);
crystalReportViewer1.ReportSource = cry;
}
else if (type == "POS_2")
{
Rp_POS_Issuance_GNSales cry = new Rp_POS_Issuance_GNSales();
cry.SetDataSource(dt);
cry.SetParameterValue("ThoiGian", thoiGian);
cry.SetParameterValue("Title", title);
crystalReportViewer1.ReportSource = cry;
}
else if (type == "SALE_1")
{
Rp_Sale_POSCard cry = new Rp_Sale_POSCard();
cry.SetDataSource(dt);
cry.SetParameterValue("ThoiGian", thoiGian);
cry.SetParameterValue("Title", title);
crystalReportViewer1.ReportSource = cry;
}
else if (type == "bal")
{
Rp_balance_enter cry = new Rp_balance_enter();
cry.SetDataSource(dt);
cry.SetParameterValue("Ngayin", DateTime.Now.ToShortDateString());
cry.SetParameterValue("Gioin", DateTime.Now.ToLongTimeString());
cry.SetParameterValue("Title", title);
crystalReportViewer1.ReportSource = cry;
}
// BẮT ĐẦU REPORT NHÓM: TƠ, A.QUÝ, NGUYÊN
else if (type == "MC_7.4")
{
rpt_MC_R74 cry = new rpt_MC_R74();
cry.SetDataSource(dt);
cry.SetParameterValue("thoigian", thoiGian);
cry.SetParameterValue("title", title);
crystalReportViewer1.ReportSource = cry;
}
else if (type == "MC_R2")
{
Rpt_MC_R2 cry = new Rpt_MC_R2();
cry.SetDataSource(dt);
cry.SetParameterValue("thoigian", thoiGian);
cry.SetParameterValue("title", title);
crystalReportViewer1.ReportSource = cry;
}
else if (type == "MC_R5")
{
rpt_MC_R5 cry = new rpt_MC_R5();
cry.SetDataSource(dt);
cry.SetParameterValue("thoigian", thoiGian);
cry.SetParameterValue("title", title);
crystalReportViewer1.ReportSource = cry;
}
else if (type == "MC_R6")
{
Rpt_MC_R6 cry = new Rpt_MC_R6();
cry.SetDataSource(dt);
cry.SetParameterValue("thoigian", thoiGian);
cry.SetParameterValue("title", title);
crystalReportViewer1.ReportSource = cry;
}
else if (type == "MC_8")
{
Rpt_MC_R8 cry = new Rpt_MC_R8();
cry.SetDataSource(dt);
cry.SetParameterValue("Ngayin", DateTime.Now.ToShortDateString());
cry.SetParameterValue("Gioin", DateTime.Now.ToLongTimeString());
cry.SetParameterValue("Title", title);
cry.SetParameterValue("Thoigian", thoiGian);
crystalReportViewer1.ReportSource = cry;
}
//End nhom REPORT NHÓM: TƠ, A.QUÝ, NGUYÊN
//Start Dat lam ngay 10/04/2012
else if (type == "MC_R9")
{
rpt_MC_R9 cry = new rpt_MC_R9();
cry.SetDataSource(dt);
// cry.SetParameterValue("Ngayin", DateTime.Now.ToShortDateString());
// cry.SetParameterValue("Gioin", DateTime.Now.ToLongTimeString());
cry.SetParameterValue("Title", title);
cry.SetParameterValue("Thoigian", thoiGian);
crystalReportViewer1.ReportSource = cry;
}
else if (type == "MC_R15")
{
rpt_MC_R15 cry = new rpt_MC_R15();
cry.SetDataSource(dt);
// cry.SetParameterValue("Ngayin", DateTime.Now.ToShortDateString());
// cry.SetParameterValue("Gioin", DateTime.Now.ToLongTimeString());
cry.SetParameterValue("Title", title);
cry.SetParameterValue("Thoigian", thoiGian);
crystalReportViewer1.ReportSource = cry;
}
//End Start Dat lam ngay 10/04/2012
//Dat: Report Thanh toan dai ly
else if (type == "BC_TTDL")
{
Rpt_TTDL_Sum cry = new Rpt_TTDL_Sum();
cry.SetDataSource(dt);
cry.SetParameterValue("Ngayin", DateTime.Now.ToShortDateString());
cry.SetParameterValue("Gioin", DateTime.Now.ToLongTimeString());
cry.SetParameterValue("Thoigian", thoiGian);
cry.SetParameterValue("Title", title);
crystalReportViewer1.ReportSource = cry;
}
else if (type == "MC_R13")
{
Rpt_MC_R13 cry = new Rpt_MC_R13();
cry.SetDataSource(dt);
//cry.SetParameterValue("Ngayin", DateTime.Now.ToShortDateString());
//cry.SetParameterValue("Gioin", DateTime.Now.ToLongTimeString());
cry.SetParameterValue("Thoigian", thoiGian);
cry.SetParameterValue("Title", title);
cry.SetParameterValue("title2", title2);
crystalReportViewer1.ReportSource = cry;
}
else if (type == "MC_12")
{
rpt_MC_12 cry = new rpt_MC_12();
cry.SetDataSource(dt);
cry.SetParameterValue("Ngayin", DateTime.Now.ToShortDateString());
cry.SetParameterValue("Gioin", DateTime.Now.ToLongTimeString());
cry.SetParameterValue("title", title);
cry.SetParameterValue("thoigian", thoiGian);
crystalReportViewer1.ReportSource = cry;
}//BILILING_CREDIT
else if (type == "BILILING_CREDIT")
{
//Rp_BillingCredit cry = new Rp_BillingCredit();
//Rpt_Credit_Billing cry = new Rpt_Credit_Billing(); //2015-03-19 (1) comment tam
Rpt_Credit_Billing_new_2 cry = new Rpt_Credit_Billing_new_2(); //2015-03-19 (1)
cry.SetDataSource(dt);
// cry.SetParameterValue("Ngayin", DateTime.Now.ToShortDateString());
//cry.SetParameterValue("Gioin", DateTime.Now.ToLongTimeString());
cry.SetParameterValue("title", title);
cry.SetParameterValue("title2", title2);
cry.SetParameterValue("title3", title3);
// cry.SetParameterValue("thoigian", thoiGian);
crystalReportViewer1.ReportSource = cry;
}
else if (type == "BILILING_CREDIT_NQH")
{
//Rp_BillingCredit cry = new Rp_BillingCredit();
//Rpt_Credit_Billing cry = new Rpt_Credit_Billing(); ////2015-03-19 (1) comment tam
Rpt_Credit_Billing_new_2 cry = new Rpt_Credit_Billing_new_2(); ////2015-03-19 (1)
cry.SetDataSource(dt);
// cry.SetParameterValue("Ngayin", DateTime.Now.ToShortDateString());
//cry.SetParameterValue("Gioin", DateTime.Now.ToLongTimeString());
cry.SetParameterValue("title", title);
cry.SetParameterValue("title2", title2);
cry.SetParameterValue("title3", title3);
// cry.SetParameterValue("thoigian", thoiGian);
crystalReportViewer1.ReportSource = cry;
}
// BẮT ĐẦU REPORT CHARGEBACK
else if (type == "MC_Reversal_1st_Presentment")
{
rpt_MC_Reversal_1st_Presentment cry = new rpt_MC_Reversal_1st_Presentment();
cry.SetDataSource(dt);
cry.SetParameterValue("Ngayin", DateTime.Now.ToShortDateString());
cry.SetParameterValue("Gioin", DateTime.Now.ToLongTimeString());
cry.SetParameterValue("Thoigian", thoiGian);
cry.SetParameterValue("Title", title);
crystalReportViewer1.ReportSource = cry;
}
else if (type == "MC_Retrival_Reversal")
{
rpt_MC_Retrival_Reversal cry = new rpt_MC_Retrival_Reversal();
cry.SetDataSource(dt);
cry.SetParameterValue("Ngayin", DateTime.Now.ToShortDateString());
cry.SetParameterValue("Gioin", DateTime.Now.ToLongTimeString());
cry.SetParameterValue("Thoigian", thoiGian);
cry.SetParameterValue("Title", title);
crystalReportViewer1.ReportSource = cry;
}
else if (type == "MC_Chargeback_Reversal")
{
rpt_MC_Chargeback_Reversal cry = new rpt_MC_Chargeback_Reversal();
cry.SetDataSource(dt);
cry.SetParameterValue("Ngayin", DateTime.Now.ToShortDateString());
cry.SetParameterValue("Gioin", DateTime.Now.ToLongTimeString());
cry.SetParameterValue("Thoigian", thoiGian);
cry.SetParameterValue("Title", title);
crystalReportViewer1.ReportSource = cry;
}
else if (type == "MC_Chargeback_DaTL")
{
rpt_MC_Chargeback_DaTL cry = new rpt_MC_Chargeback_DaTL();
cry.SetDataSource(dt);
cry.SetParameterValue("Ngayin", DateTime.Now.ToShortDateString());
cry.SetParameterValue("Gioin", DateTime.Now.ToLongTimeString());
cry.SetParameterValue("Thoigian", thoiGian);
cry.SetParameterValue("Title", title);
crystalReportViewer1.ReportSource = cry;
}
else if (type == "MC_Arbitration_Chargeback")
{
rpt_MC_Arbitration_Chargeback cry = new rpt_MC_Arbitration_Chargeback();
cry.SetDataSource(dt);
cry.SetParameterValue("Ngayin", DateTime.Now.ToShortDateString());
cry.SetParameterValue("Gioin", DateTime.Now.ToLongTimeString());
cry.SetParameterValue("Thoigian", thoiGian);
cry.SetParameterValue("Title", title);
crystalReportViewer1.ReportSource = cry;
}
else if (type == "MC_Reversal_2nd_Presentment")
{
rpt_MC_Reversal_2nd_Presentment cry = new rpt_MC_Reversal_2nd_Presentment();
cry.SetDataSource(dt);
cry.SetParameterValue("Ngayin", DateTime.Now.ToShortDateString());
cry.SetParameterValue("Gioin", DateTime.Now.ToLongTimeString());
cry.SetParameterValue("Thoigian", thoiGian);
cry.SetParameterValue("Title", title);
crystalReportViewer1.ReportSource = cry;
}
else if (type == "MC_Report_Fee")
{
rpt_MC_Report_Fee cry = new rpt_MC_Report_Fee();
cry.SetDataSource(dt);
cry.SetParameterValue("Ngayin", DateTime.Now.ToShortDateString());
cry.SetParameterValue("Gioin", DateTime.Now.ToLongTimeString());
cry.SetParameterValue("Thoigian", thoiGian);
cry.SetParameterValue("Title", title);
crystalReportViewer1.ReportSource = cry;
}
//End nhom REPORT CHARGEBACK
//Dat 03/07/2012
else if (type == "MC_BC_TH_FEE")
{
Rpt_MC_TONG_HOP_FEE cry = new Rpt_MC_TONG_HOP_FEE();
cry.SetDataSource(dt);
cry.SetParameterValue("thoigian", thoiGian);
cry.SetParameterValue("title", title);
cry.SetParameterValue("title2", title2);
crystalReportViewer1.ReportSource = cry;
}
//End Dat 03/07/2012
else if (type == "MC_15")
{
rpt_MC_15 cry = new rpt_MC_15();
cry.SetDataSource(dt);
cry.SetParameterValue("ThoiGian", thoiGian);
cry.SetParameterValue("title", title);
crystalReportViewer1.ReportSource = cry;
}
else if (type == "MC_15B")
{
rpt_MC_15B cry = new rpt_MC_15B();
cry.SetDataSource(dt);
cry.SetParameterValue("ThoiGian", thoiGian);
cry.SetParameterValue("title", title);
crystalReportViewer1.ReportSource = cry;
}
else if (type == "MC_THANH_TOAN_FILE")
{
Rpt_MC_ThanhToan_File cry = new Rpt_MC_ThanhToan_File();
cry.SetDataSource(dt);
// cry.SetParameterValue("ThoiGian", thoiGian);
cry.SetParameterValue("title", title);
crystalReportViewer1.ReportSource = cry;
}
if (type == "RP_MC_FEE_PHAITHU_TRA")
{
Rpt_MC_TONGHOPFEE_PHAITHU_TRA cry = new Rpt_MC_TONGHOPFEE_PHAITHU_TRA();
cry.SetDataSource(dt);
cry.SetParameterValue("Ngayin", DateTime.Now.ToShortDateString());
cry.SetParameterValue("Gioin", DateTime.Now.ToLongTimeString());
cry.SetParameterValue("Thoigian", thoiGian);
cry.SetParameterValue("Title", title);
crystalReportViewer1.ReportSource = cry;
}
if (type == "PHT_THUPHI")
{
Rpt_PHT_THUPHI cry = new Rpt_PHT_THUPHI();
cry.SetDataSource(dt);
cry.SetParameterValue("Title", title);
cry.SetParameterValue("Title2", title2);
crystalReportViewer1.ReportSource = cry;
}
if (type == "BC_AUTOPAYMENT_1")
{
Rpt_Auto_Payment_DSKHDangKy cry = new Rpt_Auto_Payment_DSKHDangKy();
cry.SetDataSource(dt);
cry.SetParameterValue("Title2", title2);
crystalReportViewer1.ReportSource = cry;
}
if (type == "BC_AUTOPAYMENT_2")
{
Rpt_Auto_Payment_2 cry = new Rpt_Auto_Payment_2();
cry.SetDataSource(dt);
cry.SetParameterValue("Title2", title2);
crystalReportViewer1.ReportSource = cry;
}
if (type == "BC_AUTOPAYMENT_3")
{
Rpt_Auto_Payment_Will_Process cry = new Rpt_Auto_Payment_Will_Process();
cry.SetDataSource(dt);
cry.SetParameterValue("Title2", title2);
crystalReportViewer1.ReportSource = cry;
}
if (type == "BC_CR_APP_HOLD_RELEASE")
{
Rp_Credit_App_Hold_Release cry = new Rp_Credit_App_Hold_Release();
cry.SetDataSource(dt);
//cry.SetParameterValue("Ngayin", DateTime.Now.ToShortDateString());
//cry.SetParameterValue("Gioin", DateTime.Now.ToLongTimeString());
cry.SetParameterValue("Thoigian", thoiGian);
cry.SetParameterValue("Title", title);
crystalReportViewer1.ReportSource = cry;
}
if (type == "MC_Reconciliation_ATM_Pending")
{
rP_MC_Reconciliation_ATM_Pending cry = new rP_MC_Reconciliation_ATM_Pending();
cry.SetDataSource(dt);
//cry.SetParameterValue("Ngayin", DateTime.Now.ToShortDateString());
//cry.SetParameterValue("Gioin", DateTime.Now.ToLongTimeString());
cry.SetParameterValue("Thoigian", thoiGian);
cry.SetParameterValue("Title", title);
crystalReportViewer1.ReportSource = cry;
}
if (type == "Unbill_transaction_credit")
{
Rp_Unbill_transaction_credit cry = new Rp_Unbill_transaction_credit();
cry.SetDataSource(dt);
//cry.SetParameterValue("Ngayin", DateTime.Now.ToShortDateString());
//cry.SetParameterValue("Gioin", DateTime.Now.ToLongTimeString());
cry.SetParameterValue("Thoigian", thoiGian);
cry.SetParameterValue("Thoigian2", thoigian2);
cry.SetParameterValue("Title", title);
cry.SetParameterValue("Name", name);
cry.SetParameterValue("Card_nbr", card_nbr);
cry.SetParameterValue("Acc_nbr", acc_nbr);
cry.SetParameterValue("Status", status);
cry.SetParameterValue("bill_total_debit_amt", bill_total_debit_amt);
cry.SetParameterValue("unbill_total_debit_amt", unbill_total_debit_amt);
cry.SetParameterValue("curr_auth", curr_auth);
crystalReportViewer1.ReportSource = cry;
}
if (type == "ACCOUNTING_REPORT")
{
Rpt_ACCOUNTING_REPORT cry = new Rpt_ACCOUNTING_REPORT();
cry.SetDataSource(dt);
cry.SetParameterValue("ThoiGian", thoiGian);
cry.SetParameterValue("Title", title);
crystalReportViewer1.ReportSource = cry;
}
if (type == "MC_TAX_Reversal")
{
rpt_MC_Report_TAX_Reversal cry = new rpt_MC_Report_TAX_Reversal();
cry.SetDataSource(dt);
cry.SetParameterValue("ThoiGian", thoiGian);
cry.SetParameterValue("Thoigian2", thoigian2);
cry.SetParameterValue("Title", title);
crystalReportViewer1.ReportSource = cry;
}
if (type == "PHI_CHIET_KHAU_DAI_LY")
{
rpt_PHI_CHIET_KHAU_DAI_LY phi = new rpt_PHI_CHIET_KHAU_DAI_LY();
phi.SetDataSource(dt);
phi.SetParameterValue("title", title);
phi.SetParameterValue("title2", title2);
crystalReportViewer1.ReportSource = phi;
}
if (type == "ACCOUNTING_REPORT_DETAILS")
{
rpt_ACCOUNTING_REPORT_DETAILS cry = new rpt_ACCOUNTING_REPORT_DETAILS();
cry.SetDataSource(dt);
cry.SetParameterValue("ThoiGian", thoiGian);
cry.SetParameterValue("Title", title);
crystalReportViewer1.ReportSource = cry;
}
#region 2015-09-08 (2)
if (type == "BILILING_CREDIT_3")
{
Rpt_Credit_Billing_new_3 cry = new Rpt_Credit_Billing_new_3();
cry.SetDataSource(dt);
crystalReportViewer1.ReportSource = cry;
}
#endregion 2015-09-08 (2)
}