private void LoadDataExcel()
{
try
{
if (tbUploadFile.Text == "")
{
MessageBox.Show("Vui lòng load file để cập nhật!");
}
string status_name = "", status_code = "", result = "";
string resultT = "" , MSG = "";
conTTTSOA SOA = new conTTTSOA();
status_name = cbTrangthai.Text.ToString();
status_code = cbTrangthai.SelectedValue.ToString();
// string file = @"C:\Users\thongnt.NAMABANK0\Pictures\CẬP NHẬT TRẠNG THÁI ĐỒNG LOẠT FILE MẪU.xls";
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(tbUploadFile.Text);
//Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(file);
Excel._Worksheet xlWorksheet = (Excel._Worksheet)xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
string contract_number = "";
int n_true = 0;
for (int i = 1; i <= xlRange.Count; i++)
{
if ((string)(xlRange.Cells[i, 1] as Excel.Range).Value2.ToString() == "")
{
return;
}
contract_number = (string)(xlRange.Cells[i, 1] as Excel.Range).Value2.ToString();
result = SOA.SOA_Change_Contr_Status("Web", contract_number, status_code, status_name, "82");
resultT = result.Substring(result.IndexOf("<result>") + "<result>".Length, result.IndexOf("</result>") - (result.IndexOf("<result>") + "<result>".Length));
MSG = result.Substring(result.IndexOf("<msg>") + "<msg>".Length, result.IndexOf("</msg>") - (result.IndexOf("<msg>") + "<msg>".Length));
if(resultT == "0")
{
n_true = n_true + 1;
lbKetqua.Text = MSG + " " + n_true;
}
if (i == xlRange.Count)
{
MessageBox.Show("Đã hoàn thành cập nhật!!!!");
return;
}
}
}
catch (Exception error)
{
MessageBox.Show("File excel không đúng định dạng!",error.Message);
}
}
private void btProcess_Click ( object sender, EventArgs e )
{
try
{
conTTTSOA soa = new conTTTSOA ( );
string result = soa.SOA_Migr_Autho ( );
MessageBox.Show ( result );
}
catch (Exception ex)
{
#region logError
MessageBox.Show ( ex.ToString ( ) );
#endregion logError
}
}
private void update_status(DataRow rows)
{
string sql = "", err = "";
#region update-status
string result = "";
conTTTSOA SOA = new conTTTSOA();
result = SOA.SOA_UPDATE_CONTRACT_STATUS("Web",
(rb_card_stt_up.Checked
? rows["card_number"].ToString().Trim()
: rows["migr_account_number"].ToString().Trim()
),
(rb_card_stt_up.Checked
?rows["card_status"].ToString().Trim()
:rows["account_status"].ToString().Trim()
),
"MIGRATION",
"82");
int s_rs = 8,
e_rs = result.IndexOf("</result>"),
s_msg = result.IndexOf("<msg>"),
e_msg = result.IndexOf("</msg>");
if (result.Substring(s_rs, e_rs - 8) == "0")
{
sql = "update " + (rbcredit_mgr.Checked ? "NAB_CP_CARD " : "NAB_CP_CARD_PREPAID")
+ " set "+(rb_card_stt_up.Checked ? " UPDATE_CARD_STATUS_FLAG" : "UPDATE_ACC_STATUS_FLAG") + "= 'Y', "
+ (rb_card_stt_up.Checked ? "DATE_UPDATE_CARD_STATUS" : "DATE_UPDATE_ACC_STATUS") + "= sysdate "
+ " where card_number = '" + (rb_card_stt_up.Checked
? rows["card_number"].ToString().Trim()
: rows["migr_account_number"].ToString().Trim()
) + "' "
+ " and batch_id = '" + txtbatch_id.Text.Trim() + "' ";
if (!Businessbp.executedb.ExecuteDML(sql, ref err))
{
MessageBox.Show(err + Environment.NewLine + "Cannot update NAB_CP_CARD table!!!");
return;
}
}
else
{
griderror_MGR.Rows.Add((rbcredit_mgr.Checked ? "Credit" : "Prepaid/Debit") + (rb_card_stt_up.Checked ?" - Card_Status":" - Acc_Status"),
rows["source_application_no"].ToString().Trim(),
rows["client_code"].ToString().Trim(),
rows["embossed_name"].ToString().Trim(),
(rb_card_stt_up.Checked
? rows["card_number"].ToString().Trim()
: rows["migr_account_number"].ToString().Trim()
),
result);
sql = "update " + (rbcredit_mgr.Checked ? "NAB_CP_CARD " : "NAB_CP_CARD_PREPAID")
+ " set " + (rb_card_stt_up.Checked ? "UPDATE_CARD_STATUS_FLAG" : "UPDATE_ACC_STATUS_FLAG") + "= 'Z', "
+ (rb_card_stt_up.Checked ? "update_card_status_err" : "update_acc_status_err") + "= '" + result + "', "
+ (rb_card_stt_up.Checked ? "DATE_UPDATE_CARD_STATUS" : "DATE_UPDATE_ACC_STATUS") + "= sysdate "
+ " where card_number = '" + (rb_card_stt_up.Checked
? rows["card_number"].ToString().Trim()
: rows["migr_account_number"].ToString().Trim()
) + "' "
+ " and batch_id = '" + txtbatch_id.Text.Trim() + "' ";
if (!Businessbp.executedb.ExecuteDML(sql, ref err))
{
MessageBox.Show(err + Environment.NewLine + "Cannot update NAB_CP_CARD table!!!");
return;
}
}
#endregion
}
private void button2_Click(object sender, EventArgs e)
{
try
{
string batch = txtbatch_id.Text.Trim();
if (rb_mgr_card.Checked)
{
if (rbcredit_mgr.Checked)
{
#region credit-mgr
string sql = "", err = "";
sql += " ";
sql += " SELECT * ";
sql += " FROM NAB_CP_CARD CP ";
sql += " WHERE CP.BATCH_ID = '" + txtbatch_id.Text.Trim() + "' ";
sql += " AND NVL(CP.EXTRACT_FLAG,'N') <> 'Y' ";
sql += " AND NVL(CP.AUTHORIZE,'N')='Y' ";
sql += " order by cp.client_code, cp.date_create, cp.migr_account_number, CP.basic_card_flag ";
DataTable dt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show("Error from excuting database!!!");
return;
}
else
{
if (dt.Rows.Count < 1)
{
MessageBox.Show("Không có thẻ đúng yêu cầu trong batch: " + txtbatch_id.Text.Trim());
return;
}
progressBar1.Minimum = 0;
progressBar1.Maximum = dt.Rows.Count;
//khởi tạo giá trị ban đầu cho progress bar sử thuộc tính Value
progressBar1.Value = 1;
//khoảng tăng giữa các nấc trong ProgressBar
progressBar1.Step = 1;
foreach (DataRow rows in dt.Rows)
//for (int i = 0; i < dt.Rows.Count; i++)
{
int min = (int)((double)progressBar1.Value);
int max = (int)((double)progressBar1.Maximum);
progressBar1.CreateGraphics().DrawString(min.ToString() + "/" + max.ToString(),
new Font("Arial", (float)10, FontStyle.Regular),
Brushes.Black,
new PointF(progressBar1.Width / 2 - 10, progressBar1.Height / 2 - 7));
progressBar1.PerformStep();
if (!check_client(rows["client_code"].ToString().Trim()))
{
#region add_client
#region sql
sql = "";
sql += " select ";
sql += " '" + get_regnum("001") + "', "; //0
sql += " cc.branch_code_dsa_code, "; //1
sql += " 'Client', "; //2
sql += " 'Add', "; //3
sql += " 'PR', "; //4
sql += " cc.client_code, "; //5
sql += " 'CMND', "; //6
sql += " cc.legal_id, "; //7
sql += " cc.legal_place, "; //8
sql += " cc.embossed_name, "; //9
sql += " cc.title, "; //10
sql += " cc.first_name, "; //11
sql += " cc.family_name, "; //12
sql += " (select co.code from " + Businessbp.executedb.owner + "country co where co.amnd_state = 'A' and co.n_code = cc.nationality), "; //13
sql += " cc.marital_status,"; //14
sql += " cc.applicant_profession,"; //15
sql += " cc.employer_name,"; //16
sql += " cc.employer_name,"; //17
sql += " substr(cc.birth_date,1,4) || '-' || substr(cc.birth_date,5,2) || '-' || substr(cc.birth_date,7,2),"; //18
sql += " cc.birth_city,"; //18
sql += " cc.family_name,"; //20
sql += " decode(cc.gender,'M','Male','F','Female'),"; //21
sql += " cc.first_name,"; //22
sql += " cc.family_name,"; //23
sql += " '' employer_name,"; //24
sql += " 'Work',"; //25
sql += " cc.office_phone_number,"; //26
sql += " 'Home',"; //24
sql += " cc.home_phone_number,"; //28
sql += " 'Mobile',"; //29
sql += " cc.phone_nbr_sms,"; //30
sql += " to_char(trunc(cc.date_create),'yyyy-mm-dd'),"; //31
sql += " 'PIN',"; //32
sql += " cc.email_id,"; //33
sql += " (select co.code from " + Businessbp.executedb.owner + "country co where co.amnd_state = 'A' and co.n_code = cc.permanent_country_code),"; //34
sql += " cc.permanent_city_code,"; //35
sql += " cc.permanent_zip_code,"; //36
sql += " nvl(trim(cc.permanent_address1),'N'),"; //37
sql += " cc.permanent_address2,"; //38
sql += " cc.permanent_address3,"; //39
sql += " cc.permanent_address4,"; //40
sql += " cc.auto_payment_flag,"; //41
sql += " cc.connect_school,"; //42
sql += " '" + get_regnum("002") + "',"; //43
sql += " 'ClientAddress',"; //44
sql += " 'Add',"; //45
sql += " 'PHS_ADDR',"; //46
sql += " 'Home', "; //47
sql += " cc.home_phone_number, "; //48
sql += " 'Mobile', "; //49
sql += " cc.mobile_number, "; //450
sql += " '', "; //51
sql += " '', "; //52
sql += " '', "; //53
sql += " nvl(trim(cc.current_address1),'N'), "; //54
sql += " nvl(cc.current_address2,'N'), "; //55
sql += " cc.current_address3, "; //56
sql += " cc.current_address4, "; //57
sql += " '" + get_regnum("003") + "', "; //58
sql += " 'ClientAddress', "; //59
sql += " 'Add', "; //60
sql += " 'WRK_ADDR', "; //61
sql += " 'Work', "; //62
sql += " cc.office_phone_number, "; //63
sql += " (select co.code from " + Businessbp.executedb.owner + "country co ";
sql += " where co.amnd_state = 'A' and co.n_code = cc.current_country_code), "; //64
sql += " cc.current_city_code,"; //65
sql += " cc.current_zip_code, "; //66
sql += " nvl(trim(cc.empl_address1),'N'), "; //67
sql += " cc.empl_address2, "; //68
sql += " cc.empl_address3, "; //69
sql += " cc.empl_address4, "; //70
sql += " '" + get_regnum("004") + "', "; //71
sql += " 'ClientAddress', "; //72
sql += " 'Add', "; //73
sql += " 'STMT',"; //74
sql += " 'Mobile', "; //75
sql += " cc.mobile_number, "; //76
sql += " cc.email_id, "; //77
sql += " (select co.code from " + Businessbp.executedb.owner + "country co ";
sql += " where co.amnd_state = 'A' and co.n_code = cc.ref1_country_code),"; //78
sql += " cc.ref1_city_code,"; //79
sql += " cc.ref1_zip_code,"; //80
sql += " nvl(trim(cc.ref1_address1),'N'),"; //81
sql += " cc.ref1_address2,"; //82
sql += " cc.ref1_address3,"; //83
sql += " cc.ref1_address4,"; //84
sql += " '" + get_regnum("005") + "',"; //85
sql += " cc.branch_code_dsa_code,"; //86
sql += " 'Contract',"; //87
sql += " 'Add',"; //88
sql += " 'PR',"; //89
sql += " (select pr2.code ";
sql += " from " + Businessbp.executedb.owner + "appl_product pr2 ";
sql += " where pr2.amnd_state = 'A' ";
sql += " and pr2.id = (select pr1.appl_product__oid ";
sql += " from " + Businessbp.executedb.owner + "appl_product pr1 ";
sql += " where pr1.amnd_state = 'A' ";
sql += " and pr1.id = (select pr.appl_product__oid ";
sql += " from " + Businessbp.executedb.owner + "appl_product pr ";
sql += " where pr.amnd_state = 'A' ";
sql += " and pr.internal_code = cc.product_code))), "; // 90 --Product code liability
sql += " to_char(trunc(cc.date_create),'yyyy-mm-dd'),"; //91
sql += " cc.LIABILITY_LIMIT,"; //92
sql += " 'VND',"; //93
sql += " 'Credit limit for Liability Contract',"; //94
sql += " to_char(trunc(cc.date_create),'yyyy-mm-dd'),"; //95
sql += " '2033-01-31',"; //96
sql += " '" + get_regnum("006") + "',"; //97
sql += " cc.branch_code_dsa_code,"; //98
sql += " 'Contract',"; //99
sql += " 'Add',"; //100
sql += " 'PR',"; //101
sql += " cc.migr_account_number, "; //102
sql += " nvl(cc.auto_payment_account,'N'), "; //103
sql += " (select pr1.code ";
sql += " from " + Businessbp.executedb.owner + "appl_product pr1 ";
sql += " where pr1.amnd_state = 'A' ";
sql += " and pr1.id = (select pr.appl_product__oid ";
sql += " from " + Businessbp.executedb.owner + "appl_product pr ";
sql += " where pr.amnd_state = 'A' ";
sql += " and pr.internal_code = cc.product_code)), "; //104 --Product code issuing
sql += " to_char(trunc(cc.date_create),'yyyy-mm-dd'),"; //105
sql += " cc.credit_limit,"; //106
sql += " 'VND',"; //107
sql += " 'Credit limit for Liability Contract',"; //108
sql += " to_char(trunc(cc.date_create),'yyyy-mm-dd'),"; //109
//sql += " '2033-01-31',"; //109
sql += " cc.auto_payment_flag,"; //110
sql += " cc.sms_flag,"; //111
sql += " '" + get_regnum("007") + "',"; //112
sql += " 'ContractAddress',"; //113
sql += " 'Add',"; //114
sql += " 'PHS_ADDR',"; //115
sql += " 'Work',"; //116
sql += " cc.office_phone_number,"; //117
sql += " 'Home',"; //118
sql += " cc.home_phone_number,"; //119
sql += " 'Mobile',"; //120
sql += " cc.mobile_number,"; //121
sql += " cc.email_id,"; //122
sql += " (select co.code from " + Businessbp.executedb.owner + "country co ";
sql += " where co.amnd_state = 'A' and co.n_code = cc.current_country_code),"; //123
sql += " cc.current_city_code,"; //124
sql += " cc.current_zip_code,"; //125
sql += " nvl(trim(cc.current_address1),'NA'),"; //126
sql += " cc.current_address2,"; //127
sql += " cc.current_address3,"; //128
sql += " cc.current_address4,"; //129
sql += " '" + get_regnum("008") + "',"; //130
sql += " cc.branch_code_dsa_code,"; //131
sql += " 'Contract',"; //132
sql += " 'Add',"; //133
sql += " cc.card_number,"; //134
sql += " (select pr.code ";
sql += " from " + Businessbp.executedb.owner + "appl_product pr ";
sql += " where pr.amnd_state = 'A' ";
sql += " and pr.internal_code = cc.product_code),"; //135
sql += " cc.first_name,"; //136
sql += " cc.family_name,"; //137
sql += " cc.employer_name,"; //138
sql += " to_char(trunc(cc.date_create),'yyyy-mm-dd'),"; //139
sql += " cc.source_application_no,"; //140
sql += " '" + get_regnum("009") + "',"; //141
sql += " cc.branch_code_dsa_code,"; //142
sql += " 'ContractClassifier', "; //143
sql += " 'AddOrUpdate',"; //144
sql += " 'NAB_CLIENT_CAT',"; //145
sql += " 'N',"; //146
sql += " '" + get_regnum("010") + "',"; //147
sql += " cc.branch_code_dsa_code,"; //148
sql += " 'Card',"; //149
sql += " 'Migrate',"; //150
sql += " 'NCRDNP',"; //151
sql += " to_char(trunc(cc.date_expire),'yymm'),"; //152
sql += " '', cc.pmcd, cc.pind, '01', "; //153
sql += " '" + get_regnum("011") + "',"; //154
sql += " 'ContractAddress',"; //155
sql += " 'Add',"; //156
sql += " 'STMT',"; //157
sql += " 'Work',"; //158
sql += " cc.office_phone_number,"; //159
sql += " 'Home', "; //160
sql += " cc.home_phone_number, "; //161
sql += " 'Mobile',"; //162
sql += " cc.mobile_number,"; //163
sql += " cc.email_id,"; //164
sql += " (select co.code ";
sql += " from " + Businessbp.executedb.owner + "country co ";
sql += " where co.amnd_state = 'A' ";
sql += " and co.n_code = "
+ (rows["preferred_mailing_address"].ToString().Trim() == "C"
? "cc.current_country_code "
: (rows["preferred_mailing_address"].ToString().Trim() == "O"
? "cc.empl_country_code"
: "cc.permanent_country_code"))
+ "), "; //165
sql += " " + (rows["preferred_mailing_address"].ToString().Trim() == "C"
? "cc.current_city_code, cc.current_zip_code"
: (rows["preferred_mailing_address"].ToString().Trim() == "O"
? "cc.empl_city_code, cc.empl_zip_code"
: "cc.permanent_city_code, cc.permanent_zip_code"))
+ " , "; //166, 167
sql += " " + (rows["preferred_mailing_address"].ToString().Trim() == "C"
? "nvl(trim(cc.current_address1),'NA'), cc.current_address2, cc.current_address3, cc.current_address4 "
: (rows["preferred_mailing_address"].ToString().Trim() == "O"
? "nvl(trim(cc.empl_address1),'N'), cc.empl_address2, cc.empl_address3, cc.empl_address4"
: "nvl(trim(cc.permanent_address1),'N'), cc.permanent_address2, cc.permanent_address3, cc.permanent_address4"))
+ " , "; //168,169,170,171
sql += " '" + get_regnum("012") + "',"; //172
sql += " cc.branch_code_dsa_code,"; //173
sql += " 'ContractClassifier', "; //174
sql += " 'AddOrUpdate',"; //175
sql += " 'NAB_GRTASS_CAT',"; //176
sql += " nvl(cc.profile_code,'N'), "; //177
sql += " '" + get_regnum("013") + "',"; //172
sql += " cc.branch_code_dsa_code,"; //173
sql += " 'ContractClassifier', "; //174
sql += " 'AddOrUpdate',"; //175
sql += " 'NAB_BOVD_CAT',"; //176
sql += " cc.migr_account_flag, "; //177
sql += " '" + get_regnum("014") + "',"; //172
sql += " cc.branch_code_dsa_code,"; //173
sql += " 'ContractClassifier', "; //174
sql += " 'AddOrUpdate',"; //175
sql += " 'DEBT_LEVEL',"; //176
sql += " cc.migr_debt_level"; //177
sql += " from nab_cp_card cc ";
sql += " where 1 = 1 ";
//sql += " where cc.source_application_no = '" + dt.Rows[i]["source_application_no"].ToString().Trim() + "' ";
#endregion
process_card("C", sql, rows, batch, "MGR_Credit_addclient_liaby_issu_card_Scard");
#endregion
}
else
{
if (!check_liability(rows["client_code"].ToString().Trim(), rows["product_code"].ToString().Trim()))
{
#region sql
sql = "";
sql += " select '" + get_regnum("001") + "', "; //0
sql += " cc.branch_code_dsa_code, "; //1
sql += " 'Contract', "; //2
sql += " 'Add', "; //3
sql += " cc.client_code, "; //4
sql += " (SELECT MAX(CL.SHORT_NAME) ";
sql += " FROM " + Businessbp.executedb.owner + "CLIENT CL ";
sql += " WHERE CL.AMND_STATE = 'A' ";
sql += " AND CL.CLIENT_NUMBER = cc.client_code), "; //5
sql += " 'PR', "; //6
sql += " (select pr.code ";
sql += " from " + Businessbp.executedb.owner + "appl_product pr, ";
sql += " " + Businessbp.executedb.owner + "appl_product pr1, ";
sql += " " + Businessbp.executedb.owner + "appl_product pr2 ";
sql += " where pr.amnd_state = 'A' ";
sql += " and pr1.amnd_state = 'A' ";
sql += " and pr2.amnd_state = 'A' ";
sql += " and pr2.parent_code = pr1.internal_code ";
sql += " and pr1.parent_code = pr.internal_code ";
sql += " and pr2.internal_code = cc.product_code), "; //7
sql += " to_char(cc.date_create, 'yyyy-mm-dd'), "; //8
sql += " cc.LIABILITY_LIMIT, "; //9
sql += " 'VND', "; //10
sql += " 'Credit limit for Liability Contract', "; //11
sql += " to_char(cc.date_create, 'yyyy-mm-dd'), "; //12
sql += " to_char(add_months(cc.date_create, 36), 'yyyy-mm-dd'), '', '', "; //13,14,15
sql += " '" + get_regnum("002") + "', "; //16
sql += " cc.branch_code_dsa_code, "; //17
sql += " 'Contract', "; //18
sql += " 'Add', "; //19
sql += " cc.migr_account_number, "; //20
sql += " nvl(cc.auto_payment_account,'N'), "; //21
sql += " (select pr1.code ";
sql += " from " + Businessbp.executedb.owner + "appl_product pr, " + Businessbp.executedb.owner + "appl_product pr1 ";
sql += " where pr.amnd_state = 'A' ";
sql += " and pr1.amnd_state = 'A' ";
sql += " and pr.parent_code = pr1.internal_code ";
sql += " and pr.internal_code = cc.product_code), "; //22
sql += " to_char(cc.date_create, 'yyyy-mm-dd'), "; //23
sql += " cc.credit_limit, "; //24
sql += " 'VND', "; //25
sql += " 'Credit limit for Liability Contract', "; //26
sql += " to_char(cc.date_create, 'yyyy-mm-dd'), "; //27
sql += " cc.auto_payment_flag, "; //28
sql += " cc.sms_flag, "; //29
sql += " '" + get_regnum("003") + "', "; //30
sql += " 'ContractAddress', "; //31
sql += " 'Add', "; //32
sql += " 'PHS_ADDR', "; //33
sql += " 'Work', "; //34
sql += " cc.office_phone_number, "; //35
sql += " 'Home', "; //36
sql += " cc.home_phone_number, "; //37
sql += " 'Mobile', "; //38
sql += " cc.mobile_number, "; //39
sql += " cc.email_id, "; //40
sql += " (select co.code ";
sql += " from " + Businessbp.executedb.owner + "country co ";
sql += " where co.amnd_state = 'A' ";
sql += " and co.n_code = cc.current_country_code), "; //41
sql += " cc.current_city_code, "; //42
sql += " cc.current_zip_code, "; //43
sql += " nvl(trim(cc.current_address1),'NA'), "; //44
sql += " cc.current_address2, "; //45
sql += " cc.current_address3, "; //46
sql += " cc.current_address4, "; //47
sql += " '" + get_regnum("004") + "', "; //48
sql += " cc.branch_code_dsa_code, "; //49
sql += " 'Contract', "; //50
sql += " 'Add', "; //51
sql += " cc.card_number, ";
sql += " (select pr.code ";
sql += " from " + Businessbp.executedb.owner + "appl_product pr ";
sql += " where pr.amnd_state = 'A' ";
sql += " and pr.internal_code = cc.product_code), "; //52
sql += " cc.first_name, "; //53
sql += " cc.family_name, "; //54
sql += " '', "; //55
sql += " to_char(cc.date_create, 'yyyy-mm-dd'), "; //56
sql += " cc.source_application_no, "; //57
sql += " '" + get_regnum("005") + "', "; //58
sql += " cc.branch_code_dsa_code, "; //59
sql += " 'ContractClassifier', "; //60
sql += " 'AddOrUpdate', "; //61
sql += " 'NAB_CLIENT_CAT', "; //62
sql += " 'N', "; //63
sql += " '" + get_regnum("006") + "', "; //64
sql += " cc.branch_code_dsa_code, "; //65
sql += " 'Card', "; //66
sql += " 'Migrate', 'NCRDNP', to_char(trunc(cc.date_expire),'yymm'), '', cc.PMCD, cc.PIND, '01',";
sql += " '" + get_regnum("007") + "', "; //71
sql += " 'ContractAddress', "; //72
sql += " 'Add', "; //73
sql += " 'STMT', "; //74
sql += " 'Work', "; //75
sql += " cc.office_phone_number, "; //76
sql += " 'Home', "; //77
sql += " cc.home_phone_number, "; //78
sql += " 'Mobile', "; //79
sql += " cc.mobile_number, "; //80
sql += " cc.email_id, "; //81
sql += " (select co.code ";
sql += " from " + Businessbp.executedb.owner + "country co ";
sql += " where co.amnd_state = 'A' ";
sql += " and co.n_code = "
+ (rows["preferred_mailing_address"].ToString().Trim() == "C"
? "cc.current_country_code"
: (rows["preferred_mailing_address"].ToString().Trim() == "O"
? "cc.empl_country_code"
: "cc.permanent_country_code"))
+ "), "; //82
sql += " " + (rows["preferred_mailing_address"].ToString().Trim() == "C"
? "cc.current_city_code, cc.current_zip_code"
: (rows["preferred_mailing_address"].ToString().Trim() == "O"
? "cc.empl_city_code, cc.empl_zip_code"
: "cc.permanent_city_code, cc.permanent_zip_code"))
+ " , "; //83, 84
sql += " " + (rows["preferred_mailing_address"].ToString().Trim() == "C"
? "nvl(trim(cc.current_address1),'NA'),, cc.current_address2, cc.current_address3, cc.current_address4 "
: (rows["preferred_mailing_address"].ToString().Trim() == "O"
? "nvl(trim(cc.empl_address1),'NA'), cc.empl_address2, cc.empl_address3, cc.empl_address4"
: "nvl(trim(cc.permanent_address1),'NA'), cc.permanent_address2, cc.permanent_address3, cc.permanent_address4"))
+ " , "; //86,86,87,88
sql += " '" + get_regnum("008") + "', "; //89
sql += " cc.branch_code_dsa_code, "; //90
sql += " 'ContractClassifier', "; //91
sql += " 'AddOrUpdate', "; //92
sql += " 'NAB_GRTASS_CAT', "; //93
sql += " nvl(cc.profile_code,'N'), "; //94
sql += " '" + get_regnum("009") + "',"; //172
sql += " cc.branch_code_dsa_code,"; //173
sql += " 'ContractClassifier', "; //174
sql += " 'AddOrUpdate',"; //175
sql += " 'NAB_BOVD_CAT',"; //176
sql += " cc.migr_account_flag, "; //177
sql += " '" + get_regnum("010") + "',"; //172
sql += " cc.branch_code_dsa_code,"; //173
sql += " 'ContractClassifier', "; //174
sql += " 'AddOrUpdate',"; //175
sql += " 'DEBT_LEVEL',"; //176
sql += " cc.migr_debt_level "; //177
sql += " from nab_cp_card cc ";
sql += " where 1 = 1 ";
//sql += " where cc.source_application_no = '' ";
#endregion
process_card("C", sql, rows, batch, "MGR_Credit_exist_client_add_liab_issu_card_Scard");
}
else
{
sql = ""; err = "";
sql += " select substr(pr.code,5,1) ";
sql += " from nab_cp_card cc, " + Businessbp.executedb.owner + "appl_product pr ";
sql += " where pr.amnd_state = 'A' and pr.con_cat = 'C' ";
sql += " and pr.internal_code = cc.product_code ";
sql += " and cc.card_number = '" + rows["card_number"].ToString().Trim() + "'";
DataTable dt_mainsub = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show("Error from excuting database!!!");
return;
}
else
{
if (dt_mainsub.Rows[0][0].ToString().Trim() == "M")
{
#region add_issuing
#region sql
sql = "";
sql += " select ";
sql += " '" + get_regnum("001") + "', "; //0
sql += " cc.branch_code_dsa_code, "; //1
sql += " 'Contract', "; //2
sql += " 'Add', "; //3
sql += " (select ca.contract_number ";
sql += " from " + Businessbp.executedb.owner + "acnt_contract ca, " + Businessbp.executedb.owner + "client cl ";
sql += " where ca.amnd_state = 'A' and cl.amnd_state = 'A' ";
sql += " and ca.client__id = cl.id ";
sql += " and cl.client_number = cc.client_code ";
sql += " and ca.product = (select pr1.parent_code ";
sql += " from " + Businessbp.executedb.owner + "appl_product pr, " + Businessbp.executedb.owner + "appl_product pr1 ";
sql += " where pr.amnd_state = 'A' ";
sql += " and pr1.amnd_state = 'A' ";
sql += " and pr.internal_code = cc.product_code ";
sql += " and pr1.id = pr.appl_product__oid)), "; //4
sql += " cc.client_code, "; //5
sql += " (SELECT CL.SHORT_NAME FROM " + Businessbp.executedb.owner + "CLIENT CL WHERE CL.AMND_STATE = 'A' AND CL.CLIENT_NUMBER = cc.client_code), "; //6
sql += " cc.migr_account_number, "; //7
sql += " nvl(nvl(cc.auto_payment_account,'N'),'N'), "; //8
sql += " (select pr1.code from " + Businessbp.executedb.owner + "appl_product pr, "
+ Businessbp.executedb.owner + "appl_product pr1 ";
sql += " where pr.amnd_state = 'A' and pr1.amnd_state = 'A' ";
sql += " and pr.internal_code = cc.product_code ";
sql += " and pr1.id = pr.appl_product__oid), "; //9
sql += " to_char(trunc(cc.date_create),'yyyy-mm-dd'), "; //10
sql += " cc.credit_limit, "; //11
sql += " 'VND', "; //12
sql += " 'Credit limit for Liability Contract', "; //13
sql += " to_char(trunc(cc.date_create),'yyyy-mm-dd'), "; //14
sql += " to_char(trunc(add_months(cc.date_create,30*12)),'yyyy-mm-dd'), "; //15
sql += " cc.auto_payment_flag, "; //16
sql += " cc.sms_flag, "; //17
sql += " '" + get_regnum("002") + "', "; //18
sql += " 'ContractAddress', "; //19
sql += " 'Add', "; //20
sql += " 'PHS_ADDR', "; //21
sql += " 'Work', "; //22
sql += " cc.office_phone_number, "; //23
sql += " 'Home', "; //24
sql += " cc.home_phone_number, "; //25
sql += " 'Mobile', "; //26
sql += " cc.mobile_number, "; //27
sql += " cc.email_id, "; //28
sql += " (select co.code from " + Businessbp.executedb.owner + "country co where co.amnd_state = 'A' and co.n_code = cc.current_country_code), "; //29
sql += " cc.current_city_code, "; //30
sql += " cc.current_zip_code, "; //31
sql += " nvl(trim(cc.current_address1),'NA'), "; //32
sql += " cc.current_address2, "; //33
sql += " cc.current_address3, "; //34
sql += " cc.current_address4, "; //35
sql += " '" + get_regnum("003") + "', "; //36
sql += " cc.branch_code_dsa_code, "; //37
sql += " 'Contract', "; //38
sql += " 'Add', "; //39
sql += " cc.card_number, "; //40
sql += " (select pr.code from " + Businessbp.executedb.owner + "appl_product pr where pr.amnd_state = 'A' and pr.internal_code = cc.product_code), "; //41
sql += " cc.first_name, "; //42
sql += " cc.family_name, "; //43
sql += " '' company_name, "; //44
sql += " to_char(trunc(cc.date_create),'yyyy-mm-dd'), "; //45
sql += " cc.source_application_no, "; //46
sql += " '" + get_regnum("004") + "', "; //47
sql += " cc.branch_code_dsa_code, "; //48
sql += " 'ContractClassifier', "; //49
sql += " 'AddOrUpdate', "; //50
sql += " 'NAB_CLIENT_CAT', "; //51
sql += " 'N', "; //52
sql += " '" + get_regnum("005") + "', "; //53
sql += " cc.branch_code_dsa_code, "; //54
sql += " 'Card', "; //55
sql += " 'Migrate', 'NCRDNP', to_char(trunc(cc.date_expire),'yymm'), '', cc.PMCD, cc.PIND, '01', ";
sql += " '" + get_regnum("007") + "', "; //60
sql += " 'ContractAddress', "; //61
sql += " 'Add', "; //62
sql += " 'STMT', "; //63
sql += " 'Work', "; //64
sql += " cc.office_phone_number, "; //65
sql += " 'Home', "; //66
sql += " cc.home_phone_number, "; //67
sql += " 'Mobile', "; //68
sql += " cc.mobile_number, "; //69
sql += " cc.email_id, "; //70
sql += " (select co.code ";
sql += " from " + Businessbp.executedb.owner + "country co ";
sql += " where co.amnd_state = 'A' ";
sql += " and co.n_code = "
+ (rows["preferred_mailing_address"].ToString().Trim() == "C"
? "cc.current_country_code"
: (rows["preferred_mailing_address"].ToString().Trim() == "O"
? "cc.empl_country_code"
: "cc.permanent_country_code"))
+ "), "; //71
sql += " " + (rows["preferred_mailing_address"].ToString().Trim() == "C"
? "cc.current_city_code, cc.current_zip_code"
: (rows["preferred_mailing_address"].ToString().Trim() == "O"
? "cc.empl_city_code, cc.empl_zip_code"
: "cc.permanent_city_code, cc.permanent_zip_code"))
+ " , "; //72,73
sql += " " + (rows["preferred_mailing_address"].ToString().Trim() == "C"
? "nvl(trim(cc.current_address1),'NA'), cc.current_address2, cc.current_address3, cc.current_address4 "
: (rows["preferred_mailing_address"].ToString().Trim() == "O"
? "nvl(trim(cc.empl_address1),'NA'), cc.empl_address2, cc.empl_address3, cc.empl_address4"
: "nvl(trim(cc.permanent_address1),'NA'), cc.permanent_address2, cc.permanent_address3, cc.permanent_address4"))
+ " , "; //74,75,76,77
sql += " '" + get_regnum("008") + "', "; //78
sql += " cc.branch_code_dsa_code, "; //79
sql += " 'ContractClassifier', "; //80
sql += " 'AddOrUpdate', "; //81
sql += " 'NAB_GRTASS_CAT', "; //82
sql += " nvl(cc.profile_code,'N'), "; //83
sql += " '" + get_regnum("009") + "',"; //172
sql += " cc.branch_code_dsa_code,"; //173
sql += " 'ContractClassifier', "; //174
sql += " 'AddOrUpdate',"; //175
sql += " 'NAB_BOVD_CAT',"; //176
sql += " cc.migr_account_flag, "; //177
sql += " '" + get_regnum("010") + "',"; //172
sql += " cc.branch_code_dsa_code,"; //173
sql += " 'ContractClassifier', "; //174
sql += " 'AddOrUpdate',"; //175
sql += " 'DEBT_LEVEL',"; //176
sql += " cc.migr_debt_level"; //177
sql += " from nab_cp_card cc ";
sql += " where 1 = 1 ";
//sql += " where cc.source_application_no = '" + dt.Rows[i]["source_application_no"].ToString().Trim() + "' ";
#endregion
//Credit_exist_client_liab_add_issu_card_Scard
process_card("C", sql, rows, batch, "MGR_Credit_exist_client_liab_add_issu_card_Scard");
#endregion
}
else
{
#region add_card
#region sql
sql = "";
sql += " select ";
sql += " '" + get_regnum("001") + "', "; //0
sql += " cc.branch_code_dsa_code, "; //1
sql += " 'Contract', "; //2
sql += " 'Add', "; //3
sql += " cc.migr_account_number, "; //4
sql += " cc.client_code, "; //5
sql += " (SELECT CL.SHORT_NAME FROM " + Businessbp.executedb.owner + "CLIENT CL "
+ " WHERE CL.AMND_STATE = 'A' AND CL.CLIENT_NUMBER = cc.client_code), "; //6
sql += " cc.card_number, "; //7
sql += " (select pr.code from " + Businessbp.executedb.owner + "appl_product pr "
+ " where pr.amnd_state = 'A' and pr.internal_code = cc.product_code), "; //8
sql += " cc.first_name, "; //9
sql += " cc.family_name, '' company_name,"; //10,11
sql += " to_char(trunc(cc.date_create),'yyyy-mm-dd'), "; //12
sql += " to_char(trunc(cc.date_expire),'yyyy-mm-dd'), ";
sql += " cc.source_application_no, "; //13
sql += " '" + get_regnum("002") + "', "; //14
sql += " cc.branch_code_dsa_code, "; //15
sql += " 'ContractClassifier', "; //16
sql += " 'AddOrUpdate', "; //17
sql += " 'NAB_CLIENT_CAT', "; //18
sql += " 'N', "; //19
sql += " '" + get_regnum("003") + "', "; //20
sql += " cc.branch_code_dsa_code, "; //21
sql += " 'Card', "; //22
sql += " 'Migrate', 'NCRDNP', to_char(trunc(cc.date_expire),'yymm'), '', cc.PMCD, cc.PIND, '01', '' ";
sql += " from nab_cp_card cc ";
sql += " where 1 = 1 ";
//sql += " where cc.source_application_no = '" + dt.Rows[i]["source_application_no"].ToString().Trim() + "' ";
#endregion
//Credit_exist_client_liabi_issu_add_card
process_card("C", sql, rows, batch, "MGR_Credit_exist_client_liabi_issu_add_card");
#endregion
}
}
}
}
}
}
#endregion
}
else
{
if (rbrelated.Checked)
{
#region add-related
string sql = "", err = "";
sql += " select ";
sql += " cp.branch_code, 'Contract', 'Add', ";
sql += " cp.migr_account_number, ";
sql += " cp.client_code, (SELECT CL.SHORT_NAME FROM " + Businessbp.executedb.owner + "CLIENT CL WHERE CL.AMND_STATE = 'A' AND trim(CL.CLIENT_NUMBER) = trim(cp.client_code)), ";
sql += " card.card_number, ";
sql += " card.base_relation, card.rbs_number, ";
sql += " (select pr1.code ";
sql += " from " + Businessbp.executedb.owner + "appl_product pr, " + Businessbp.executedb.owner + "appl_product pr1 ";
sql += " where pr.amnd_state = 'A' and pr1.amnd_state = 'A' ";
sql += " and trim(pr.internal_code) = trim(cp.product_code) ";
sql += " and pr.parent_code = pr1.parent_code ";
sql += " and trim(pr1.base_relation) = trim(card.base_relation)), ";
sql += " to_char(trunc(card.date_open),'yyyy-mm-dd'), '' CIFSUB, cp.source_application_no, cp.embossed_name ";
sql += " from nab_cp_card_prepaid cp, nab_mgr_card_account_link card ";
sql += " where 1 = 1 ";
sql += " and cp.card_number = card.card_number ";
sql += " and nvl(cp.extract_flag,'N') = 'Y' ";
sql += " and nvl(card.extract_flag,'N') <> 'Y' ";
DataTable dt_related = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show("Error from excuting database: " + err);
return;
}
else
{
if (dt_related.Rows.Count < 1)
{
MessageBox.Show("No data found.");
return;
}
else
{
progressBar1.Minimum = 0;
progressBar1.Maximum = dt_related.Rows.Count;
//khởi tạo giá trị ban đầu cho progress bar sử thuộc tính Value
progressBar1.Value = 1;
//khoảng tăng giữa các nấc trong ProgressBar
progressBar1.Step = 1;
int dem_rela=0;
foreach (DataRow rows in dt_related.Rows)
{
int min = (int)((double)progressBar1.Value);
int max = (int)((double)progressBar1.Maximum);
progressBar1.CreateGraphics().DrawString(min.ToString() + "/" + max.ToString(),
new Font("Arial", (float)10, FontStyle.Regular),
Brushes.Black,
new PointF(progressBar1.Width / 2 - 10, progressBar1.Height / 2 - 7));
progressBar1.PerformStep();
string input = "";
for (int i = 0; i < dt_related.Columns.Count; i++)
{
input = input + rows[i].ToString().Trim() + "#";
}
dem_rela++;
input = get_regnum("001") + dem_rela + "#" + input;
conTTTSOA soa = new conTTTSOA();
string result = "";
result = soa.SOA_ISSUING_CARD("Web", input, "Debit_exist_client_issu_card_add_relatedcard");
int s_rs = 8, e_rs = result.IndexOf("</result>"), s_msg = result.IndexOf("<msg>"), e_msg = result.IndexOf("</msg>");
if (result.Substring(s_rs, e_rs - 8) == "0")
{
sql = "";
sql += "update nab_mgr_card_account_link card ";
sql += "set card.extract_flag = 'Y', ";
sql += " card.date_extract = sysdate ";
sql += "where card.card_number = '" + rows["card_number"] + "' ";
sql += " and card.base_relation = '" + rows["base_relation"] + "' ";
sql += " and card.rbs_number = '" + rows["rbs_number"] + "' ";
sql += " and card.client_code = '" + rows["client_code"] + "' ";
if (!Businessbp.executedb.ExecuteDML(sql, ref err))
{
MessageBox.Show(err + Environment.NewLine + "Cannot update nab_mgr_card_account_link table!!!");
return;
}
}
else
{
griderror_MGR.Rows.Add("Related",
rows["source_application_no"].ToString().Trim(),
rows["client_code"].ToString().Trim(),
rows["embossed_name"].ToString().Trim(),
rows["card_number"].ToString().Trim(),
result);
sql = "";
sql += "update nab_mgr_card_account_link card ";
sql += "set card.extract_flag = 'Z', ";
sql += " card.date_extract = sysdate, ";
sql += " card.extract_err = substr('" + result + "',1,255) ";
sql += "where card.card_number = '" + rows["card_number"] + "' ";
sql += " and card.base_relation = '" + rows["base_relation"] + "' ";
sql += " and card.rbs_number = '" + rows["rbs_number"] + "' ";
sql += " and card.client_code = '" + rows["client_code"] + "' ";
if (!Businessbp.executedb.ExecuteDML(sql, ref err))
{
MessageBox.Show(err + Environment.NewLine + "Cannot update nab_mgr_card_account_link table!!!");
return;
}
}
}
}
}
#endregion
}
else
{
#region prepaid
string sql = "", err = "";
sql += " ";
sql += " SELECT * ";
sql += " FROM NAB_CP_CARD_PREPAID CP ";
sql += " WHERE CP.BATCH_ID = '" + txtbatch_id.Text.Trim() + "' ";
sql += " AND NVL(CP.EXTRACT_FLAG,'N') <> 'Y' ";
sql += " AND NVL(CP.AUTHORIZE,'N')='Y' ";
if (rbpre_mgr.Checked)
{
sql += " and (substr(cp.card_number,1,6) in ('516995') ";
sql += " or substr(cp.card_number,1,8) = '97042808') ";
}
else
{
if (rb_mgrdebit.Checked)
{
sql += " and substr(cp.card_number,1,6) <> '516995' ";
sql += " and substr(cp.card_number,1,8) <> '97042808' ";
}
else
{
return;
}
}
sql += " order by cp.client_code, cp.date_create, cp.migr_account_number, CP.basic_card_flag ";
DataTable dt = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show("Error from excuting database!!!");
return;
}
else
{
if (dt.Rows.Count < 1)
{
MessageBox.Show("Không có thẻ cần tìm.");
return;
}
progressBar1.Minimum = 0;
progressBar1.Maximum = dt.Rows.Count;
//khởi tạo giá trị ban đầu cho progress bar sử thuộc tính Value
progressBar1.Value = 1;
//khoảng tăng giữa các nấc trong ProgressBar
progressBar1.Step = 1;
foreach (DataRow rows in dt.Rows)
//for (int i = 0; i < dt.Rows.Count; i++)
{
int min = (int)((double)progressBar1.Value);
int max = (int)((double)progressBar1.Maximum);
progressBar1.CreateGraphics().DrawString(min.ToString() + "/" + max.ToString(),
new Font("Arial", (float)10, FontStyle.Regular),
Brushes.Black,
new PointF(progressBar1.Width / 2 - 10, progressBar1.Height / 2 - 7));
progressBar1.PerformStep();
if (!check_client(rows["client_code"].ToString().Trim()))
{
#region add_client
#region sql
sql = "";
sql += " select "; //
sql += " '" + get_regnum("001") + "', "; //0
sql += " cp.branch_code, "; //1
sql += " 'Client', "; //2
sql += " 'Add', "; //3
sql += " 'PR', "; //4
sql += " cp.client_code, "; //5
sql += " 'CMND' RegNumberType, "; //6
sql += " cp.legal_id, "; //7
sql += " '1990-01-01' RegNumberDetails, "; //8
sql += " cp.embossed_name, "; //9
sql += " cp.title, "; //10
sql += " cp.first_name, "; //11
sql += " cp.family_name, "; //12
sql += " (select co.code from " + Businessbp.executedb.owner + "country co where co.amnd_state = 'A' and co.n_code = cp.birth_country), "; //13
sql += " decode(trim(cp.marital_status),'1','S','2','M','3','D','X'), "; //14
sql += " '' position, "; //15
sql += " cp.employer, "; //16
sql += " '' companytradename, "; //17
sql += " to_char(trunc(to_date(cp.birth_date,'yyyymmdd')), 'yyyy-mm-dd'), "; //18
sql += " cp.birth_city, "; //19
sql += " cp.encoded_name, "; //20
sql += " decode(trim(cp.gender), 'M', 'Male','Female'), "; //21
sql += " cp.first_name, "; //22
sql += " cp.family_name, "; //23
sql += " cp.employer, "; //24
sql += " 'Work', cp.phone_no_1, "; //25,26
sql += " 'Home', cp.phone_home, "; //27,28
sql += " 'Mobile', cp.phone_mobile, "; //29,30
sql += " to_char(trunc(cp.date_create),'yyyy-mm-dd'), "; //31
sql += " 'PIN',cp.email_id, "; //32,33
sql += " (select co.code from " + Businessbp.executedb.owner + "country co where co.amnd_state = 'A' and co.n_code = cp.empl_country_code), "; //34
sql += " cp.city_code, cp.empl_zip_code, nvl(trim(cp.empl_address1),'N'), cp.empl_address2, cp.empl_address3, cp.empl_address4, "; //
// 35 36 37 38 39 40
sql += " 'O', cp.connect_school, "; //41, 42
sql += " '" + get_regnum("002") + "','ClientAddress', 'Add', 'PHS_ADDR', "; //43, 44, 45, 46
sql += " 'Home', cp.phone_home, "; //47,48
sql += " 'Mobile', cp.phone_mobile, "; //49,50
sql += " (select co.code from " + Businessbp.executedb.owner + "country co where co.amnd_state = 'A' and co.n_code = cp.mailing_country_code), ";
//51
sql += " cp.mailing_city_code, cp.mailing_zip_code, "; //52, 53
sql += " nvl(trim(cp.mailing_address1),'N'), cp.mailing_address2, cp.mailing_address3, cp.mailing_address4, "; //
//54 55 56 57
sql += " '" + get_regnum("003") + "', 'ClientAddress', 'Add', 'PST_ADDR', "; //58, 59, 60, 61
sql += " 'Work', cp.phone_home, "; //62, 63
sql += " (select co.code from " + Businessbp.executedb.owner + "country co where co.amnd_state = 'A' and co.n_code = cp.country_code), "; //
//64
sql += " cp.city_code, cp.zip_code, nvl(trim(cp.address1),'N'), cp.address2, cp.address3, cp.address4, "; //
//65 66 67 68 69 70
sql += " '" + get_regnum("004") + "', 'ClientAddress', 'Add', 'WRK_ADDR', "; //71, 72, 73, 74
sql += " 'Work', cp.phone_home, "; //75, 76
sql += " (select co.code from " + Businessbp.executedb.owner + "country co where co.amnd_state = 'A' and co.n_code = cp.empl_country_code), "; //
//77
sql += " cp.email_id, cp.empl_city_code, cp.empl_zip_code, nvl(trim(cp.empl_address1),'N'), cp.empl_address2, cp.empl_address3, cp.empl_address4, "; //
//78 79 80 81 82 83 84
sql += " '" + get_regnum("005") + "', cp.branch_code, 'Contract', 'Add', "; // 85,86,87,88
sql += " cp.migr_account_number, "; //89
sql += " (select pr.code from " + Businessbp.executedb.owner + "appl_product pr," + Businessbp.executedb.owner + "appl_product pr1 "; //
sql += " where pr.amnd_state = 'A' and pr1.amnd_state = 'A' "; //
sql += " and pr.id = pr1.appl_product__oid "; //
sql += " and pr1.internal_code = trim(cp.product_code)), "; //90
sql += " to_char(trunc(cp.date_create),'yyyy-mm-dd'), "; //91
sql += " '', "; //92
sql += " '', "; //93
sql += " '" + get_regnum("006") + "', 'ContractAddress', 'Add', 'PHS_ADDR', "; //94, 95, 96, 97
sql += " 'Work', cp.phone_no_1, "; //98, 99
sql += " 'Home', cp.phone_no_2, "; //100, 101
sql += " 'Mobile', cp.phone_alternate, "; //102, 103
sql += " cp.email_id, "; //104
sql += " (select co.code from " + Businessbp.executedb.owner + "country co where co.amnd_state = 'A' and co.n_code = cp.empl_country_code), "; //
//105
sql += " cp.empl_city_code, cp.empl_zip_code, nvl(trim(cp.empl_address1),'N'), cp.empl_address2, cp.empl_address3, cp.empl_address4, "; //
//106 107 108 109 110 111
sql += " '" + get_regnum("007") + "', cp.branch_code, 'Contract', 'Add', cp.card_number, "; //112, 113, 114, 115, 116
sql += " (select pr.code from " + Businessbp.executedb.owner + "appl_product pr "; //
sql += " where pr.amnd_state = 'A' and pr.internal_code = trim(cp.product_code)), "; //117
sql += " cp.first_name, cp.family_name, '', "; //118, 119, 120
sql += " to_char(trunc(cp.date_create),'yyyy-mm-dd'), cp.source_application_no, "; //121, 122
sql += " '" + get_regnum("008") + "', cp.branch_code, 'ContractClassifier', 'AddOrUpdate', 'NAB_CLIENT_CAT', 'N', "; //
//122 124 125 126 127 128
sql += " '" + get_regnum("009") + "', cp.branch_code, 'Card', 'Migrate', 'NCRDNP', '', '', cp.PMCD, cp.PIND, '01', "; //
//129 130 131 132 133 134 135 136 137 138
sql += " '" + get_regnum("010") + "',"; //139
sql += " 'ContractAddress',"; //140
sql += " 'Add',"; //141
sql += " 'STMT',"; //142
sql += " 'Work',"; //143
sql += " cp.phone_no_1,"; //144
sql += " 'Home', "; //145
sql += " cp.phone_home, "; //146
sql += " 'Mobile',"; //147
sql += " cp.phone_mobile,"; //148
sql += " cp.email_id,"; //149
sql += " (select co.code ";
sql += " from " + Businessbp.executedb.owner + "country co ";
sql += " where co.amnd_state = 'A' ";
sql += " and co.n_code = cp.mailing_country_code), "; //150
sql += " cp.mailing_city_code, cp.mailing_zip_code , "; //151, 152
sql += " nvl(trim(cp.mailing_address1),'NA'), cp.mailing_address2, cp.mailing_address3, cp.mailing_address4, "; //168,169,170,171
//153 154 155 156
sql += " '" + get_regnum("012") + "',"; //157
sql += " cp.branch_code,"; //158
sql += " 'ContractClassifier', "; //159
sql += " 'AddOrUpdate',"; //160
sql += " 'NAB_CLIENT_CAT',"; //161
sql += " 'N'"; //162
sql += " from nab_cp_card_prepaid cp ";
sql += " where 1 = 1 ";
//sql += " where cp.source_application_no = '" + dt.Rows[i]["source_application_no"].ToString().Trim() + "'";
#endregion
process_card("P", sql, rows, batch, "MGR_Debit_Prepaid_addclient_issu_card_Scard");
//sql = ""; err = "";
//sql += " select substr(pr.code,5,1) maninsub, substr(pr.code,2,1) debit ";
//sql += " from nab_cp_card_prepaid cc, " + Businessbp.executedb.owner + "appl_product pr ";
//sql += " where pr.amnd_state = 'A' and pr.con_cat = 'C' ";
//sql += " and trim(pr.internal_code) = trim(cc.product_code) ";
//sql += " and trim(cc.source_application_no) = '" + rows["source_application_no"].ToString().Trim() + "'";
//DataTable dt_mainsub = Businessbp.executedb.getTable(sql, ref err);
//if (err != "")
//{
// MessageBox.Show("Error from excuting database!!!");
// return;
//}
//else
//{
// if (dt_mainsub.Rows[0][1].ToString().Trim() == "D")
// {
// #region add-related
// #region sql
// sql = ""; err = "";
// sql += " select ";
// sql += " '" + get_regnum("001") + "', cp.branch_code, 'Contract', 'Add', ";
// sql += " card.issuing_contr_nbr, ";
// sql += " cp.client_code, (SELECT CL.SHORT_NAME FROM " + Businessbp.executedb.owner + "CLIENT CL WHERE CL.AMND_STATE = 'A' AND CL.CLIENT_NUMBER = cp.client_code), ";
// sql += " card.card_number, ";
// sql += " card.base_relation, cp.card_number, ";
// sql += " (select pr1.code ";
// sql += " from " + Businessbp.executedb.owner + "appl_product pr, " + Businessbp.executedb.owner + "appl_product pr1 ";
// sql += " where pr.amnd_state = 'A' and pr1.amnd_state = 'A' ";
// sql += " and trim(pr.internal_code) = trim(cp.product_code) ";
// sql += " and pr.parent_code = pr1.parent_code ";
// sql += " and trim(pr1.base_relation) = trim(card.base_relation)), ";
// sql += " to_char(trunc(card.date_open),'yyyy-mm-dd'), '' CIFSUB ";
// sql += " from nab_cp_card_prepaid cp, nab_mgr_card_account_link card ";
// sql += " where 1 = 1 ";
// sql += " and cp.card_number = card.card_number ";
// //sql += " where cp.source_application_no = '" + dt.Rows[i]["source_application_no"].ToString().Trim() + "' ";
// #endregion
// //Debit_exist_client_issu_card_add_relatedcard
// process_card("P", sql, rows, batch, "Debit_exist_client_issu_card_add_relatedcard");
// #endregion
// }
//}
#endregion
}
else
{
sql = ""; err = "";
sql += " select substr(pr.code,5,1) maninsub, substr(pr.code,2,1) debit ";
sql += " from nab_cp_card_prepaid cc, " + Businessbp.executedb.owner + "appl_product pr ";
sql += " where pr.amnd_state = 'A' and pr.con_cat = 'C' ";
sql += " and trim(pr.internal_code) = trim(cc.product_code) ";
sql += " and trim(cc.card_number) = '" + rows["card_number"].ToString().Trim() + "'";
DataTable dt_mainsub = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show("Error from excuting database!!!");
return;
}
else
{
if (dt_mainsub.Rows[0]["maninsub"].ToString().Trim() == "M")
{
#region add-issuing
#region sql
sql = ""; err = "";
sql += " select "; //0
sql += " '" + get_regnum("001") + "', cp.branch_code, 'Contract', 'Add', cp.client_code, (SELECT CL.SHORT_NAME FROM " + Businessbp.executedb.owner + "CLIENT CL WHERE CL.AMND_STATE = 'A' AND CL.CLIENT_NUMBER = cp.client_code), ";
//0 1 2 3 4 5
sql += " cp.migr_account_number,";
sql += " (select pr.code from " + Businessbp.executedb.owner + "appl_product pr," + Businessbp.executedb.owner + "appl_product pr1 ";
sql += " where pr.amnd_state = 'A' and pr1.amnd_state = 'A' ";
sql += " and pr.id = pr1.appl_product__oid ";
sql += " and pr1.internal_code = trim(cp.product_code)), ";//7
sql += " to_char(trunc(cp.date_create),'yyyy-mm-dd'), ";//8
sql += " '' auto_pay, '' sms_flag, ";//9, 10
sql += " '" + get_regnum("002") + "','ContractAddress', 'Add', 'PHS_ADDR', ";
//11 12 13 14
sql += " 'Home', cp.phone_home, "; //15, 16
sql += " 'Work', cp.phone_no_1, "; //17, 18
sql += " 'Mobile', cp.phone_mobile, "; //19, 20
sql += " cp.email_id, "; //21
sql += " (select co.code from " + Businessbp.executedb.owner + "country co where co.amnd_state = 'A' and co.n_code = cp.mailing_country_code), ";
//22
sql += " cp.mailing_city_code, cp.mailing_zip_code, "; //23, 24
sql += " nvl(trim(cp.mailing_address1),'NA'), cp.mailing_address2, cp.mailing_address3, cp.mailing_address4, ";
//25 26 27 28
sql += " '" + get_regnum("003") + "', cp.branch_code, 'Contract', 'Add', cp.card_number, ";
// 29 30 31 32 33
sql += " (select pr.code from " + Businessbp.executedb.owner + "appl_product pr ";
sql += " where pr.amnd_state = 'A' and pr.internal_code = trim(cp.product_code)), ";
//34
sql += " cp.first_name, cp.family_name, '' companyname, ";//35, 36, 37
sql += " to_char(trunc(cp.date_create),'yyyy-mm-dd'), cp.source_application_no, "; //38, 39
sql += " '" + get_regnum("004") + "', cp.branch_code, 'ContractClassifier', 'AddOrUpdate', 'NAB_CLIENT_CAT', 'N', ";
//40 41 42 43 44 45
sql += " '" + get_regnum("005") + "', cp.branch_code, 'Card', 'Migrate', 'NCRDNP', to_char(trunc(cp.date_expire),'yymm'), '', cp.PMCD, cp.PIND, '01', ";
//46 47 48 49 50 51 52 53 54 55
sql += " '" + get_regnum("006") + "', ";//56
sql += " 'ContractAddress',"; //57
sql += " 'Add',"; //58
sql += " 'STMT',"; //59
sql += " 'Work',"; //60
sql += " cp.phone_no_1,"; //61
sql += " 'Home', "; //62
sql += " cp.phone_home, "; //63
sql += " 'Mobile',"; //64
sql += " cp.phone_mobile,"; //65
sql += " cp.email_id,"; //66
sql += " (select co.code ";
sql += " from " + Businessbp.executedb.owner + "country co ";
sql += " where co.amnd_state = 'A' ";
sql += " and co.n_code = cp.mailing_country_code), "; //67
sql += " cp.mailing_city_code, cp.mailing_zip_code , "; //68 69
sql += " nvl(trim(cp.mailing_address1),'NA'), cp.mailing_address2, cp.mailing_address3, cp.mailing_address4, "; //168,169,170,171
//70 71 72 73
sql += " '" + get_regnum("012") + "',"; //74
sql += " cp.branch_code,"; //75
sql += " 'ContractClassifier', "; //76
sql += " 'AddOrUpdate',"; //77
sql += " 'NAB_CLIENT_CAT',"; //78
sql += " 'N', ''"; //79
sql += " from nab_cp_card_prepaid cp ";
sql += " where 1 = 1 ";
//sql += " where cp.source_application_no = '" + dt.Rows[i]["source_application_no"].ToString().Trim() + "'";
#endregion
//Debit_Prepaid_existclient_add_issu_card_Scard
process_card("P", sql, rows, batch, "MGR_Debit_Prepaid_existclient_add_issu_card_Scard");
#endregion
}
else
{
#region add-card
#region sql
sql = "";
sql += " select ";
sql += " '" + get_regnum("001") + "', cp.branch_code, 'Contract', 'Add', ";
sql += " (select ca.contract_number ";
sql += " from " + Businessbp.executedb.owner + "acnt_contract ca, " + Businessbp.executedb.owner + "acnt_contract cas ";
sql += " where ca.amnd_state = 'A' and cas.amnd_state = 'A' ";
sql += " and cas.acnt_contract__oid = ca.id ";
sql += " and trim(cas.add_info_01) = trim(cp.basic_source_application_no) ";
sql += " and substr(cas.contract_number,1,1) <> '5'), ";
sql += " cp.client_code, (SELECT CL.SHORT_NAME FROM " + Businessbp.executedb.owner + "CLIENT CL WHERE CL.AMND_STATE = 'A' AND CL.CLIENT_NUMBER = cp.client_code), ";
sql += " cp.card_number, (select pr.code from " + Businessbp.executedb.owner + "appl_product pr ";
sql += " where pr.amnd_state = 'A' and pr.internal_code = trim(cp.product_code)), ";
sql += " cp.first_name, cp.family_name, '', ";
sql += " to_char(trunc(cp.date_create),'yyyy-mm-dd'), cp.source_application_no, ";
sql += " '" + get_regnum("002") + "', cp.branch_code, 'ContractClassifier', 'AddOrUpdate', 'NAB_CLIENT_CAT', 'N', ";
sql += " '" + get_regnum("003") + "', cp.branch_code, 'Card', 'Migrate', 'NCRDNP', '', '', cp.PMCD, cp.PIND, '01', '' ";
sql += " from nab_cp_card_prepaid cp ";
sql += " where 1 = 1 ";
//sql += " where cp.source_application_no = '" + dt.Rows[i]["source_application_no"].ToString().Trim() + "'";
#endregion
//Debit_Prepaid_exist_client_issu_add_card
process_card("P", sql, rows, batch, "MGR_Debit_Prepaid_exist_client_issu_add_card");
#endregion
}
}
}
}
}
#endregion
}
}
}
else
{
if ((rb_acc_stt_up.Checked && rb_mgrdebit.Checked)
|| (rb_acc_stt_up.Checked && rbpre_mgr.Checked))
{
MessageBox.Show("Không thể UPDATE_ACC_STATUS cho thẻ Prepaid/Debit.");
}
else
{
string sql = "", err = "";
sql += "select cp.source_application_no, ";
sql += " cp.card_number, ";//migr_account_number
if (rb_acc_stt_up.Checked)
{
sql += " cp.migr_account_number, ";//
}
sql += " cp.embossed_name, ";
sql += " cp.client_code, ";
sql += " cp." + (rb_card_stt_up.Checked
? "card_status"
: "account_status"
);
if (rbcredit_mgr.Checked)
{
sql += " from nab_cp_card cp ";
}
else
{
if (rb_mgrdebit.Checked || rbpre_mgr.Checked)
{
sql += " from nab_cp_card_Prepaid cp ";
}
else
{
MessageBox.Show("Các lựa chọn chưa đúng.");
return;
}
}
sql += " where nvl(cp.extract_flag,'N') = 'Y' ";
if (rb_card_stt_up.Checked)
{
sql += " and nvL(cp.update_card_status_flag,'N') <> 'Y' ";
}
else
{
if (rb_acc_stt_up.Checked)
{
sql += " and nvl(cp.update_card_status_flag,'N') <> 'Y' ";
}
}
DataTable dt_status = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show("Error from excuting database: " + err);
return;
}
else
{
if (dt_status.Rows.Count < 1)
{
MessageBox.Show("Không có status cần update.");
}
else
{
progressBar1.Minimum = 0;
progressBar1.Maximum = dt_status.Rows.Count ;
//khởi tạo giá trị ban đầu cho progress bar sử thuộc tính Value
progressBar1.Value = 1 ;
//khoảng tăng giữa các nấc trong ProgressBar
progressBar1.Step = 1;
foreach (DataRow row in dt_status.Rows)
{
update_status(row);
//bắt đầu chạy
progressBar1.PerformStep();
int min = (int)((double)progressBar1.Value);
int max = (int)((double)progressBar1.Maximum);
progressBar1.CreateGraphics().DrawString(min.ToString() + "/" + max.ToString(),
new Font("Arial", (float)10, FontStyle.Regular),
Brushes.Black,
new PointF(progressBar1.Width / 2 - 10, progressBar1.Height / 2 - 7));
}
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show("Error from coding: :" + ex.ToString());
}
}
private void process_card(string type, string sql, DataRow dtrows, string batch, string incase)
{
string err = "";
if (incase.Substring(0, 3).Trim() != "MGR")
{
sql += " and " + (type.Trim() == "C" ? "cc" : "cp") + ".source_application_no = '" + dtrows["source_application_no"].ToString().Trim() + "' ";
}
if (incase.Substring(0, 3).Trim() == "MGR")
{
sql += " and " + (type.Trim() == "C" ? "cc" : "cp") + ".card_number = '" + dtrows["card_number"].ToString().Trim() + "' ";
}
sql += " and " + (type.Trim() == "C" ? "cc" : "cp") + ".batch_id = '" + batch + "' ";
DataTable dt_rows = Businessbp.executedb.getTable(sql, ref err);
if (err != "")
{
MessageBox.Show("Error from excuting Database!!!");
}
else
{
if (dt_rows.Rows.Count < 1)
{
MessageBox.Show("Không tìm thấy thông tin.");
return;
}
string input = "";
for (int j = 0; j < dt_rows.Columns.Count; j++)
{
input = input + dt_rows.Rows[0][j].ToString().Trim() + "#";
}
conTTTSOA SOA = new conTTTSOA();
string result = "";
result = SOA.SOA_ISSUING_CARD("Web", input, incase);
int s_rs = 8, e_rs = result.IndexOf("</result>"), s_msg = result.IndexOf("<msg>"), e_msg = result.IndexOf("</msg>");
//Alert.Show(result.Substring(s_rs, e_rs - 8) + "___" + result.Substring(s_msg + 5, e_msg - s_msg - 5), this);
if (result.Substring(s_rs, e_rs - 8) == "0")
{
//MessageBox.Show("Thành công! " + dtrows["source_application_no"].ToString().Trim());
sql = "update " + ( type.Trim ( ) == "C" ? "NAB_CP_CARD " : "NAB_CP_CARD_PREPAID" )
+ " set EXTRACT_FLAG = 'Y', "
+ " DATE_EXTRACT = sysdate, "
+ " user_extract = '" + Businessbp.executedb.Usrid + "'"
+ " where batch_id = '" + batch + "'";
if (incase.Substring(0, 3).Trim() != "MGR")
{
sql += " and source_application_no = '" + dtrows["source_application_no"].ToString().Trim() + "' ";
}
if (incase.Substring(0, 3).Trim() == "MGR")
{
sql += " and card_number = '" + dtrows["card_number"].ToString().Trim() + "' ";
}
if (!Businessbp.executedb.ExecuteDML(sql, ref err))
{
MessageBox.Show(err + Environment.NewLine + "Cannot update " + (type.Trim() == "C" ? "NAB_CP_CARD " : "NAB_CP_CARD_PREPAID") + "!!!");
return;
}
//else
//{
// sql = "update nab_cp_run_batch ";
// sql += " set status = 'Y' ";
// sql += " where batch_id = '" + batch.Trim() + "' ";
// if (!Businessbp.executedb.ExecuteDML(sql, ref err))
// {
// MessageBox.Show(err + Environment.NewLine + "Cannot update NAB_CP_RUN_BATCH!!!");
// return;
// }
// else
// {
// MessageBox.Show("Process successful!!!");
// }
//}
}
else
{
//MessageBox.Show("Lỗi: " + result.Substring(s_rs, e_rs - 8) + " - " + result.Substring(s_msg + 5, e_msg - s_msg - 5));
if (incase.Substring(0, 3).Trim() == "MGR")
{
griderror_MGR.Rows.Add((type == "C" ? "Credit" : "Prepaid/Debit") + " - Add Card",
dtrows["source_application_no"].ToString().Trim(),
dtrows["client_code"].ToString().Trim(),
dtrows["embossed_name"].ToString().Trim(),
dtrows["card_number"].ToString().Trim(),
result);
}
else
{
griderror.Rows.Add(dtrows["source_application_no"].ToString().Trim(),
dtrows["client_code"].ToString().Trim(),
dtrows["embossed_name"].ToString().Trim(),
dtrows["product_code"].ToString().Trim(),
result);
}
sql = "update " + (type.Trim() == "C" ? "NAB_CP_CARD " : "NAB_CP_CARD_PREPAID")
+ " set extract_err = '" + result + "', "
+ " DATE_EXTRACT = sysdate, "
+ " extract_flag = 'Z', "
+ " user_extract = '" + Businessbp.executedb.Usrid + "'"
+ " where batch_id = '" + batch + "'";
if (incase.Substring(0, 3).Trim() != "MGR")
{
sql += " and source_application_no = '" + dtrows["source_application_no"].ToString().Trim() + "' ";
}
if (incase.Substring(0, 3).Trim() == "MGR")
{
sql += " and card_number = '" + dtrows["card_number"].ToString().Trim() + "' ";
}
if (!Businessbp.executedb.ExecuteDML(sql, ref err))
{
MessageBox.Show(err + Environment.NewLine + "Cannot update NAB_CP_CARD table!!!");
return;
}
return;
}
}
}
private void btprocess_Click(object sender, EventArgs e)
{
if ((txtamount.Text == "" || txtdesc.Text == "")
|| !(rbmisccredit.Checked == false || rbmiscdebit.Checked == false)
|| !(rbcard.Checked == false || rbacc.Checked == false)
|| !(rbsoa.Checked == false || rbproc.Checked == false))
{
MessageBox.Show("Vui lòng nhập thông tin chính xác.");
}
else
{
try
{
double.Parse(txtamount.Text.Trim());
}
catch
{
MessageBox.Show("Vui lòng nhập thông tin chính xác.");
return;
}
conTTTSOA soa = new conTTTSOA();
string result = "";
if (rbsoa.Checked)
{
result = soa.SOA_Misc_Credit("Form",
rbcard.Checked ? cardnbr : accnbr,
txtamount.Text.Trim(),
txtdesc.Text.Trim(),
rbmiscdebit.Checked ? "84" : "85");
int s_rs = 8, e_rs = result.IndexOf("</result>"), s_msg = result.IndexOf("<msg>"), e_msg = result.IndexOf("</msg>");
if (result.Substring(s_rs, e_rs - 8) == "0")
{
MessageBox.Show("Thành công!");
txtamount.Text = "0";
txtdesc.Text = "";
}
else
{
MessageBox.Show("Lỗi: " + result.Substring(s_rs, e_rs - 8) + " - " + result.Substring(s_msg + 5, e_msg - s_msg - 5));
return;
}
}
else
{
string name = "NAB_PCK_FEE_CHARGE.MICS_TRANS";
string err = "";
string[] InParaName = new string[5] { "vCard_nbr",
"vAmount",
"vSign",
"vC_R",
"vTrans_details" };
string[] OutParaName = new string[3] { "vDocID", "vResult_Flag", "vErr_Msg" };
OracleType[] InParaType = new OracleType[5] { OracleType.VarChar, OracleType.Number, OracleType.VarChar, OracleType.VarChar, OracleType.VarChar };
OracleType[] OutParaType = new OracleType[3] { OracleType.Number, OracleType.Number, OracleType.VarChar };
string[] InParaValues = new string[5] { rbcard.Checked?cardnbr:accnbr,
txtamount.Text.Trim(),
rbmisccredit.Checked?"C":"D",
rbmiscdebit.Checked?(rbcash.Checked?"C":"R"):"Z",
txtdesc.Text.Trim() };
string[] OutParaValues = new string[3];
int[] OutParaSize = new int[3] { 1000, 1000, 1000 };
Businessbp.executedb.ExecuteProc(name, InParaName, InParaType, InParaValues, OutParaName, OutParaType, OutParaSize, ref OutParaValues, ref err);
if (err != "")
{
MessageBox.Show("Error from excuting database: " + err);
}
else
{
if (OutParaValues[1].ToString().Trim() == "-1")
{
MessageBox.Show("Giao dịch không thành công: " + OutParaValues[2].ToString().Trim());
}
else
{
MessageBox.Show("Thành công.");
txtamount.Text = "0";
txtdesc.Text = "";
}
}
}
}
}