コード例 #1
0
        public static int Insert_SMS_Nhac_No_Due_Date(ObjInsertSMSDueDate 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);
            }
        }
コード例 #2
0
        public static int InsertSMSMessateToEBankGW_2(string idAlert, string mobile, string message,
                                                      char msgstat, string smsType)
        {
            OracleConnection connection = null;

            try
            {
                connection = OracleDBConnection.OpenConnectionDB("EBANK_GW");
                OracleCommand cmd = new OracleCommand("SMS_SCB.PROC_INS_MASTERCARD_KM", connection);
                cmd.CommandType = CommandType.StoredProcedure;

                OracleParameter idAlert_p = new OracleParameter("id_alert", idAlert);
                idAlert_p.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(idAlert_p);

                OracleParameter mobile_p = new OracleParameter("mobile", mobile);
                mobile_p.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(mobile_p);

                OracleParameter message_p = new OracleParameter("message", message);
                message_p.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(message_p);

                OracleParameter msgstat_p = new OracleParameter("msgstat", msgstat);
                msgstat_p.Direction  = ParameterDirection.Input;
                msgstat_p.OracleType = OracleType.Char;
                cmd.Parameters.Add(msgstat_p);

                OracleParameter mc_sms_type_p = new OracleParameter("mc_sms_type", smsType);
                mc_sms_type_p.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(mc_sms_type_p);

                OracleString rowID;
                int          insertedRow = 0;

                insertedRow = cmd.ExecuteOracleNonQuery(out rowID);
                if (connection != null)
                {
                    connection.Close();
                }
                return(insertedRow);
            }
            catch (Exception ex)
            {
                _logSMSNhacNoSaoKe.WriteLog("Error InsertSMSMessateToEBankGW_2() at DataAccess, " + ex.Message);
                if (connection != null)
                {
                    connection.Close();
                }
                return(0);
            }
        }
コード例 #3
0
        public static DataTable GetSMSNhacNoSaoKeDB(string statementMonth, string cardBrn)
        {
            DataTable        data = new DataTable();
            OracleConnection conn = null;

            try
            {
                conn = OracleDBConnection.OpenConnectionDB("CW_IM_STAN");
                //OracleCommand cmd = new OracleCommand("Get_Reminder_Payment_1_IPP", connection);
                //////OracleCommand cmd = new OracleCommand("Get_Reminder_Payment_1_IPP_E", connection); //loai bo loc
                OracleCommand cmd = new OracleCommand("Get_Reminder_Payment_1_IPP_T", conn);

                cmd.CommandType = CommandType.StoredProcedure;

                OracleParameter settl_month = new OracleParameter("settl_month", statementMonth);
                settl_month.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(settl_month);

                OracleParameter crd_brn = new OracleParameter("p_crd_brn", cardBrn);
                crd_brn.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(crd_brn);

                OracleParameter results = new OracleParameter("sys_cursor", OracleType.Cursor);
                results.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(results);

                OracleDataAdapter da = new OracleDataAdapter(cmd);

                da.Fill(data);
                if (conn != null)
                {
                    conn.Close();
                }
                return(data);
            }
            catch (Exception ex)
            {
                _logSMSNhacNoSaoKe.WriteLog("Error GetReminderPayment1() at DataAccess, " + ex.Message);
                if (conn != null)
                {
                    conn.Close();
                }
                data.Clear();
                return(data);
            }
        }
コード例 #4
0
        public static DataTable GetSmsDataDueDate(string settlMonth, string dueDate)
        {
            DataTable        data = new DataTable();
            OracleConnection conn = null;

            try
            {
                conn = OracleDBConnection.OpenConnectionDB("CW_IM_STAN");
                OracleCommand cmd = new OracleCommand("GET_SMS_NHAC_NO_DUE_DATE", conn);

                cmd.CommandType = CommandType.StoredProcedure;

                OracleParameter settl_month = new OracleParameter("p_settl_month", settlMonth);
                settl_month.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(settl_month);

                OracleParameter due_date = new OracleParameter("p_due_date", dueDate);
                due_date.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(due_date);

                OracleParameter results = new OracleParameter("sys_cursor", OracleType.Cursor);
                results.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(results);

                OracleDataAdapter da = new OracleDataAdapter(cmd);

                da.Fill(data);
                if (conn != null)
                {
                    conn.Close();
                }
                return(data);
            }
            catch (Exception ex)
            {
                _logSMSNhacNoSaoKe.WriteLog("Error GET_SMS_NHAC_NO_DUE_DATE at DataAccess, " + ex.Message);
                if (conn != null)
                {
                    conn.Close();
                }
                data.Clear();
                return(data);
            }
        }
コード例 #5
0
        public static void UpdateStatusSMSNhacNoSaoKe(string id, string cardBrn, string stateMonth, string smsType, string status)
        {
            OracleConnection conn = new OracleConnection(); // C#

            try
            {
                conn = OracleDBConnection.OpenConnectionDB("CW_DW");
                string str = @"update SMS_NHAC_NO set ACTION_TYPE = '" + status + @"', INSERT_TRANS_DATETIME = TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')
                               where id = '" + id + @"' 
                                     and CARD_BRN = '" + cardBrn + @"'
                                     and SMS_MONTH = " + stateMonth + @" 
                                     and SMS_TYPE = '" + smsType + "'";

                OracleCommand cmd = new OracleCommand(str, conn);
                cmd.CommandType = CommandType.Text;
                int rowsUpdated = cmd.ExecuteNonQuery();
                if (rowsUpdated == 0)
                {
                    _logSMSNhacNoSaoKe.WriteLog("UpdateStatusSMSNhacNoSaoKe: Update record DB SMS_NHAC_NO failed because " + rowsUpdated);
                }
                else
                {
                    _logSMSNhacNoSaoKe.WriteLog("UpdateStatusSMSNhacNoSaoKe: Update stattus record DB SMS_NHAC_NO");
                }

                conn.Close();
            }
            catch (Exception ex)
            {
                _logSMSNhacNoSaoKe.WriteLog("UpdateStatusSMSNhacNoSaoKe: Update record DB SMS_NHAC_NO failed because " + ex.Message);
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }
コード例 #6
0
        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);
        }
コード例 #7
0
        public static int InsertReminderPayment_2SMSToDW(
            string sms_type,
            string sms_detail,
            string dest_mobile,                                //long dest_mobile,
            DateTime get_trans_datetime,
            string pan,
            string card_brn,
            string card_type,
            string sms_date,                                //long sms_date,
            string closing_balance,
            string minimum_payment,                         //long paymented_amt,
            string due_date,
            string sms_stat
            )
        {
            OracleConnection conn = null;

            try
            {
                conn = OracleDBConnection.OpenConnectionDB("CW_DW");
                OracleCommand cmd = new OracleCommand("fpt.Insert_Reminder_Payment_1_2", conn);
                //OracleCommand cmd = new OracleCommand("fpt.Insert_Reminder_Payment_1", connection);
                cmd.CommandType = CommandType.StoredProcedure;

                OracleParameter sms_type_p = new OracleParameter("sms_type_p", sms_type);
                sms_type_p.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(sms_type_p);

                OracleParameter sms_detail_p = new OracleParameter("sms_detail_p", sms_detail);
                sms_detail_p.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(sms_detail_p);

                OracleParameter dest_mobile_p = new OracleParameter("dest_mobile_p", dest_mobile);
                dest_mobile_p.Direction = ParameterDirection.Input;
                //dest_mobile_p.OracleType = OracleType.Number;
                cmd.Parameters.Add(dest_mobile_p);

                OracleParameter get_trans_datetime_p = new OracleParameter("get_trans_datetime_p", get_trans_datetime);
                get_trans_datetime_p.Direction = ParameterDirection.Input;
                //get_trans_datetime_p.OracleType = OracleType.DateTime;
                cmd.Parameters.Add(get_trans_datetime_p);

                OracleParameter pan_p = new OracleParameter("pan_p", pan);
                pan_p.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(pan_p);

                OracleParameter card_brn_p = new OracleParameter("card_brn_p", card_brn);
                card_brn_p.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(card_brn_p);

                OracleParameter card_type_p = new OracleParameter("card_type_p", card_type);
                card_type_p.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(card_type_p);

                OracleParameter sms_date_p = new OracleParameter("sms_date_p", sms_date);
                sms_date_p.Direction = ParameterDirection.Input;
                //sms_date_p.OracleType = OracleType.Number;
                cmd.Parameters.Add(sms_date_p);

                OracleParameter closing_balance_p = new OracleParameter("closing_balance_p", closing_balance);
                closing_balance_p.Direction = ParameterDirection.Input;
                //sms_date_p.OracleType = OracleType.Number;
                cmd.Parameters.Add(closing_balance_p);

                OracleParameter minimum_payment_p = new OracleParameter("minimum_payment_p", minimum_payment);
                minimum_payment_p.Direction = ParameterDirection.Input;
                //sms_date_p.OracleType = OracleType.Number;
                cmd.Parameters.Add(minimum_payment_p);

                OracleParameter due_date_p = new OracleParameter("due_date_p", due_date);
                due_date_p.Direction = ParameterDirection.Input;
                //sms_date_p.OracleType = OracleType.Number;
                cmd.Parameters.Add(due_date_p);

                OracleParameter sms_stat_p = new OracleParameter("sms_stat_p", sms_stat);
                sms_stat_p.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(sms_stat_p);

                OracleString rowID;
                int          insertedRow = 0;

                insertedRow = cmd.ExecuteOracleNonQuery(out rowID);
                if (conn != null)
                {
                    conn.Close();
                }
                return(insertedRow);
            }
            catch (Exception ex)
            {
                _logSMSNhacNoDueDate.WriteLog("Error InsertReminderPayment_2SMSToDW() at DataAccess, " + ex.Message);
                if (conn != null)
                {
                    conn.Close();
                }
                return(0);
            }
        }