public static int Insert_SMS_Nhac_No_Sao_Ke(ObjInsertSMSSaoKe obj)
{
OracleConnection conn = new OracleConnection();
try
{
conn = OracleDBConnection.OpenConnectionDB("CW_DW");
string str = "Insert into SMS_NHAC_NO VALUES " + "('" + obj.getId() + "','" + obj.getSmsType() + "','" + obj.getSmsDetail() + "','" + obj.getDesMobile() + "'," + obj.getDateTime() + "," + obj.getInsertTransDateTime() + ",'" + obj.getPan() + "','" + obj.getCardBrn() + "','" + obj.getCardType() + "'," + obj.getSmsMonth() + "," + obj.getClosingBalance() + "," + obj.getDueDate() + "," + obj.getMinimumPayment() + ",'" + obj.getActionType() + "'," + obj.getTotalBalIpp() + ",'" + obj.getVip() + "','" + obj.getCifVip() + "','" + obj.getCardNo() + "'," + obj.getLoc() + ",'" + obj.getIdStatement() + "'" + ")";
OracleCommand cmd = new OracleCommand(str, conn);
cmd.CommandType = CommandType.Text;
int rowsUpdated = cmd.ExecuteNonQuery();
if (rowsUpdated == 0)
{
_logSMSNhacNoSaoKe.WriteLog("Insert record table SMS_NHAC_NO failed because " + rowsUpdated);
}
else
{
_logSMSNhacNoSaoKe.WriteLog("Insert record table SMS_NHAC_NO");
}
conn.Close();
return(rowsUpdated);
}
catch (Exception ex)
{
_logSMSNhacNoSaoKe.WriteLog("Insert record DB SMS_NHAC_NO failed because " + ex.Message);
if (conn != null)
{
conn.Close();
}
return(-1);
}
}
public static List <ObjInsertSMSSaoKe> getListSMSNhacNoSaoKe(string p_cardBrn, string p_stateMonth, string p_smsType)
{
List <ObjInsertSMSSaoKe> listObjs = new List <ObjInsertSMSSaoKe>();
OracleConnection conn = new OracleConnection();
try
{
conn = OracleDBConnection.OpenConnectionDB("CW_DW");
string str = @"select ID, SMS_TYPE, SMS_DETAIL, DEST_MOBILE, GET_TRANS_DATETIME
, INSERT_TRANS_DATETIME, PAN, CARD_BRN, CARD_TYPE, SMS_MONTH
, CLOSING_BALANCE, DUE_DATE, MINIMUM_PAYMENT, ACTION_TYPE
, TOL_BAL_IPP, VIP, CIF_VIP, CARD_NO, LOC, ID_STATEMENT
from SMS_NHAC_NO
where ACTION_TYPE = 'N'
and SMS_MONTH = " + p_stateMonth + @"
and CARD_BRN = '" + p_cardBrn + @"'
and SMS_TYPE = '" + p_smsType + "'";
OracleCommand cmd = new OracleCommand(str, conn);
cmd.CommandType = CommandType.Text;
OracleDataReader objReader = cmd.ExecuteReader();
if (objReader.HasRows)
{
while (objReader.Read())
{
string id = "";
if (objReader["ID"] != null)
{
id = objReader["ID"].ToString().Trim();
}
string smsType = "";
if (objReader["SMS_TYPE"] != null)
{
smsType = objReader["SMS_TYPE"].ToString().Trim();
}
string smsDetail = "";
if (objReader["SMS_DETAIL"] != null)
{
smsDetail = objReader["SMS_DETAIL"].ToString().Trim();
}
string destMobile = "";
if (objReader["DEST_MOBILE"] != null)
{
destMobile = objReader["DEST_MOBILE"].ToString().Trim();
}
string dateTime = "";
if (objReader["GET_TRANS_DATETIME"] != null)
{
dateTime = objReader["GET_TRANS_DATETIME"].ToString().Trim();
}
string insertTransDate = "";
if (objReader["INSERT_TRANS_DATETIME"] != null)
{
insertTransDate = objReader["INSERT_TRANS_DATETIME"].ToString().Trim();
}
string pan = "";
if (objReader["PAN"] != null)
{
pan = objReader["PAN"].ToString().Trim();
}
string cardBrn = "";
if (objReader["CARD_BRN"] != null)
{
cardBrn = objReader["CARD_BRN"].ToString().Trim();
}
string cardType = "";
if (objReader["CARD_TYPE"] != null)
{
cardType = objReader["CARD_TYPE"].ToString().Trim();
}
string smsMonth = "";
if (objReader["SMS_MONTH"] != null)
{
smsMonth = objReader["SMS_MONTH"].ToString().Trim();
}
string closingBal = "";
if (objReader["CLOSING_BALANCE"] != null)
{
closingBal = objReader["CLOSING_BALANCE"].ToString().Trim();
}
string dueDate = "";
if (objReader["DUE_DATE"] != null)
{
dueDate = objReader["DUE_DATE"].ToString().Trim();
}
string minPayment = "";
if (objReader["MINIMUM_PAYMENT"] != null)
{
minPayment = objReader["MINIMUM_PAYMENT"].ToString().Trim();
}
string actionType = "";
if (objReader["ACTION_TYPE"] != null)
{
actionType = objReader["ACTION_TYPE"].ToString().Trim();
}
string tolBallIpp = "";
if (objReader["TOL_BAL_IPP"] != null)
{
tolBallIpp = objReader["TOL_BAL_IPP"].ToString().Trim();
}
string vip = "";
if (objReader["VIP"] != null)
{
vip = objReader["VIP"].ToString().Trim();
}
string cifVip = "";
if (objReader["CIF_VIP"] != null)
{
cifVip = objReader["CIF_VIP"].ToString().Trim();
}
string cardNo = "";
if (objReader["CARD_NO"] != null)
{
cardNo = objReader["CARD_NO"].ToString().Trim();
}
string loc = "";
if (objReader["LOC"] != null)
{
loc = objReader["LOC"].ToString().Trim();
}
string idStatement = "";
if (objReader["ID_STATEMENT"] != null)
{
idStatement = objReader["ID_STATEMENT"].ToString().Trim();
}
ObjInsertSMSSaoKe obj = new ObjInsertSMSSaoKe(id, smsType, smsDetail, destMobile,
dateTime, insertTransDate, pan, cardBrn, cardType, smsMonth, closingBal, dueDate,
minPayment, actionType, tolBallIpp, vip, cifVip, cardNo, loc, idStatement);
listObjs.Add(obj);
}
}
conn.Close();
}
catch (Exception ex)
{
_logSMSNhacNoSaoKe.WriteLog("Get data DB SMS_NHAC_NO failed because " + ex.Message);
if (conn != null)
{
conn.Close();
}
return(null);
}
return(listObjs);
}
private void btnInsetSMSSaoKe_Click(object sender, EventArgs e)
{
string cardBrn = tboxCardBrn.Text;
string statementMonth = tboxStatementMonth.Text;
DataTable table = new DataTable();
try
{
_log.WriteLog("----------------Begin Process-----------------");
table.Rows.Clear();
table = GetSmsData(statementMonth, cardBrn);
if (table.Rows.Count > 0)
{
DialogResult dialogResult = MessageBox.Show("So luong gui tin nhan SMS la " + table.Rows.Count, "Info", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
List <ObjInsertSMSSaoKe> listObjs = new List <ObjInsertSMSSaoKe>();
foreach (DataRow row in table.Rows)
{
string id = row.ItemArray[0].ToString();
string smsType = row.ItemArray[1].ToString();
string smsDetail = row.ItemArray[2].ToString(); //empty
string desMobile = row.ItemArray[3].ToString();
string dateTime = row.ItemArray[4].ToString();
string insertTransDate = row.ItemArray[5].ToString(); //empty
string pan = row.ItemArray[6].ToString();
string cardBrnData = row.ItemArray[7].ToString();
string cardType = row.ItemArray[8].ToString();
string smsMonth = row.ItemArray[9].ToString(); //YYYYMM
string closingBal = row.ItemArray[10].ToString();
string dueDate = row.ItemArray[11].ToString();
string minimumPayment = row.ItemArray[12].ToString();
string actType = row.ItemArray[13].ToString();//N: tao moi, Y: da gui qua eb, F: gui qua eb failed, D: ko gui
string totBalIpp = row.ItemArray[14].ToString();
string vip = row.ItemArray[15].ToString();
string cifVip = row.ItemArray[16].ToString();
string cardNo = row.ItemArray[17].ToString();
string loc = row.ItemArray[18].ToString();
string idStatement = row.ItemArray[19].ToString();
if (totBalIpp == null || totBalIpp.Equals(""))
{
totBalIpp = "0";
}
ObjInsertSMSSaoKe obj = new ObjInsertSMSSaoKe(id, smsType, smsDetail, desMobile
, dateTime, insertTransDate, pan, cardBrnData, cardType, smsMonth, closingBal, dueDate
, minimumPayment, actType, totBalIpp, vip, cifVip, cardNo, loc, idStatement);
listObjs.Add(obj);
}
Insert_SMSMessage(listObjs, cardBrn);
}
else if (dialogResult == DialogResult.No)
{
_log.WriteLog("User press button No ==> exit");
return;
}
}
}
catch (Exception ex)
{
_log.WriteLog("Error RunService(), " + ex.Message);
}
}
private void btnInsetSMSSaoKe_Click(object sender, EventArgs e)
{
string cardBrn = tboxCardBrn.Text;
string statementMonth = tboxStatementMonth.Text;
DataTable table = new DataTable();
try
{
_log.WriteLog("----------------Begin Process-----------------");
lblLoading.Text = "Loading...";
lblLoading.Visible = true;
table.Rows.Clear();
table = GetSmsData(statementMonth, cardBrn);
int rowNum = 0;
string cyccDay = "";
if (tboxCardBrn.Text == "VS")
{
cyccDay = "55";
}
else
if (chkMCW.Checked == true)
{
cyccDay = "68";
}
else
{
cyccDay = "45";
}
if (table.Rows.Count > 0)
{
DialogResult dialogResult = MessageBox.Show("So luong tin nhan SMS import la " + table.Rows.Count, "Info", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
if (dialogResult == DialogResult.Yes)
{
DataAccess.update_Data_Notify_Send_SMS_Statement(statementMonth, cardBrn, cyccDay);
List <ObjInsertSMSSaoKe> listObjs = new List <ObjInsertSMSSaoKe>();
foreach (DataRow row in table.Rows)
{
rowNum++;
string id = row.ItemArray[0].ToString();
string smsType = row.ItemArray[1].ToString();
string smsDetail = row.ItemArray[2].ToString(); //empty
string desMobile = row.ItemArray[3].ToString();
string dateTime = row.ItemArray[4].ToString();
string insertTransDate = row.ItemArray[5].ToString(); //empty
string pan = row.ItemArray[6].ToString();
string cardBrnData = row.ItemArray[7].ToString();
string cardType = row.ItemArray[8].ToString();
string smsMonth = row.ItemArray[9].ToString(); //YYYYMM
string closingBal = row.ItemArray[10].ToString();
string dueDate = row.ItemArray[11].ToString();
string minimumPayment = row.ItemArray[12].ToString();
string actType = row.ItemArray[13].ToString();//N: tao moi, Y: da gui qua eb, F: gui qua eb failed, D: ko gui
string totBalIpp = row.ItemArray[14].ToString();
string vip = row.ItemArray[15].ToString();
string cifVip = row.ItemArray[16].ToString();
string cardNo = row.ItemArray[17].ToString();
string loc = row.ItemArray[18].ToString();
string idStatement = row.ItemArray[19].ToString();
string statementAmt = row.ItemArray[20].ToString();
if (totBalIpp == null || totBalIpp.Equals(""))
{
totBalIpp = "0";
}
ObjInsertSMSSaoKe obj = new ObjInsertSMSSaoKe(id, smsType, smsDetail, desMobile
, dateTime, insertTransDate, pan, cardBrnData, cardType, smsMonth, closingBal, dueDate
, minimumPayment, actType, totBalIpp, vip, cifVip, cardNo, loc, idStatement, statementAmt);
listObjs.Add(obj);
lblLoading.Text = "Loading " + (rowNum * 50 / listObjs.Count) + "%...";
}
Insert_SMSMessage(listObjs, cardBrn);
lblLoading.Text = "Import data completed.";
lblLoading.Visible = false;
}
else if (dialogResult == DialogResult.No)
{
_log.WriteLog("User press button No ==> exit");
MessageBox.Show("User press button No ==> exit", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
}
catch (Exception ex)
{
_log.WriteLog("Error RunService(), " + ex.Message);
MessageBox.Show("Error RunService(), " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}