Exemple #1
0
        public void sendSmsAll()
        {
            var        context = GlobalHost.ConnectionManager.GetHubContext <ChatHub>();
            MonitorHub monitor = new MonitorHub();

            using (OracleConnection conn = new OracleConnection(Database.conString))
            {
                try
                {
                    conn.Open();
                    using (var cmd3 = new OracleCommand(SqlCmd.Log.logTest, conn)
                    {
                        CommandType = System.Data.CommandType.Text
                    })
                    {
                        cmd3.Parameters.Add(new OracleParameter("msg", "เริ่มดึงข้อความ"));
                        cmd3.ExecuteNonQueryAsync();
                        cmd3.Dispose();
                    }
                    using (var cmd = new OracleCommand(SqlCmd.User.getAllSmsWithConnId, conn)
                    {
                        CommandType = System.Data.CommandType.Text
                    })
                    {
                        var reader            = cmd.ExecuteReader();
                        List <m_sendSms> data = new List <m_sendSms>();
                        while (reader.Read())
                        {
                            data.Add(new m_sendSms
                            {
                                SMS010_PK     = Int32.Parse(reader["SMS010_PK"].ToString()),
                                CUST_NO       = Int32.Parse(reader["CUST_NO"].ToString()),
                                CONN_ID       = reader["CONN_ID"] == DBNull.Value ? string.Empty : reader["CONN_ID"].ToString(),
                                DEVICE_STATUS = reader["DEVICE_STATUS"].ToString(),
                                SMS_NOTE      = reader["SMS_NOTE"] == DBNull.Value ? string.Empty : reader["SMS_NOTE"].ToString(),
                                CON_NO        = reader["CON_NO"] == DBNull.Value ? string.Empty : reader["CON_NO"].ToString(),
                                SMS_TIME      = (DateTime)reader["SMS_TIME"],
                                SENDER        = reader["SENDER"] == DBNull.Value ? 0 : Int32.Parse(reader["SENDER"].ToString()),
                                SENDER_TYPE   = reader["SENDER_TYPE"].ToString(),
                                SMS010_REF    = reader["SMS010_REF"] == DBNull.Value ? 0 : Int32.Parse(reader["SMS010_REF"].ToString()),
                                READ_STATUS   = reader["READ_STATUS"].ToString()
                            });
                        }
                        using (var cmd3 = new OracleCommand(SqlCmd.Log.logTest, conn)
                        {
                            CommandType = System.Data.CommandType.Text
                        })
                        {
                            cmd3.Parameters.Add(new OracleParameter("msg", "ดึงข้อความเสร็จ"));
                            cmd3.ExecuteNonQueryAsync();
                            cmd3.Dispose();
                        }
                        using (var cmd3 = new OracleCommand(SqlCmd.Log.logTest, conn)
                        {
                            CommandType = System.Data.CommandType.Text
                        })
                        {
                            cmd3.Parameters.Add(new OracleParameter("msg", "เริ่มส่งข้อความ"));
                            cmd3.ExecuteNonQueryAsync();
                            cmd3.Dispose();
                        }
                        if (data.Count != 0)
                        {
                            List <m_SMS010> sms = new List <m_SMS010>();
                            //using (var cmd3 = new OracleCommand(SqlCmd.Log.logTest, conn) { CommandType = System.Data.CommandType.Text })
                            //{
                            //    cmd3.Parameters.Add(new OracleParameter("msg", OracleDbType.Varchar2));
                            //    foreach (var s in data)
                            //    {
                            //        var temp = new m_SMS010
                            //        {
                            //            SMS010_PK = s.SMS010_PK,
                            //            CUST_NO = s.CUST_NO,
                            //            CON_NO = s.CON_NO,
                            //            SMS_NOTE = s.SMS_NOTE,
                            //            SMS_TIME = DateTime.Now,
                            //            SENDER = s.SENDER,
                            //            SENDER_TYPE = s.SENDER_TYPE,
                            //            SMS010_REF = s.SMS010_REF,
                            //            READ_STATUS = s.READ_STATUS
                            //        };
                            //        cmd3.Parameters[0].Value = temp.SMS_NOTE;
                            //        cmd3.ExecuteNonQueryAsync();
                            //        //using (var cmd3 = new OracleCommand(SqlCmd.Log.logTest, conn) { CommandType = System.Data.CommandType.Text })
                            //        //{
                            //        //    cmd3.Parameters.Add(new OracleParameter("msg", temp.SMS_NOTE));
                            //        //    cmd3.ExecuteNonQueryAsync();
                            //        //    cmd3.Dispose();
                            //        //}

                            //        //monitor.sendMessage(string.Empty, s.CONN_ID, new { cust_no = s.CUST_NO }, new { request_status = "SUCCESS", desc = "Admin ส่งข้อความ", data = temp });
                            //        //context.Clients.Client(s.CONN_ID).sms(temp);
                            //    }
                            //    cmd3.Dispose();
                            //}
                            foreach (var s in data)
                            {
                                var temp = new m_SMS010
                                {
                                    SMS010_PK   = s.SMS010_PK,
                                    CUST_NO     = s.CUST_NO,
                                    CON_NO      = s.CON_NO,
                                    SMS_NOTE    = s.SMS_NOTE,
                                    SMS_TIME    = DateTime.Now,
                                    SENDER      = s.SENDER,
                                    SENDER_TYPE = s.SENDER_TYPE,
                                    SMS010_REF  = s.SMS010_REF,
                                    READ_STATUS = s.READ_STATUS
                                };
                                //using (var cmd3 = new OracleCommand(SqlCmd.Log.logTest, conn) { CommandType = System.Data.CommandType.Text })
                                //{
                                //    cmd3.Parameters.Add(new OracleParameter("msg", temp.SMS_NOTE));
                                //    cmd3.ExecuteNonQueryAsync();
                                //    cmd3.Dispose();
                                //}

                                monitor.sendMessage(string.Empty, s.CONN_ID, new { cust_no = s.CUST_NO }, new { request_status = "SUCCESS", desc = "Admin ส่งข้อความ", data = temp });
                                context.Clients.Client(s.CONN_ID).sms(temp);
                            }

                            using (var cmd2 = new OracleCommand(SqlCmd.Notification.markToSent, conn)
                            {
                                CommandType = System.Data.CommandType.Text
                            })
                            {
                                cmd2.ExecuteNonQueryAsync();
                                cmd2.Parameters.Clear();
                            }
                        }
                        //using (var cmd3 = new OracleCommand(SqlCmd.Log.logTest, conn) { CommandType = System.Data.CommandType.Text })
                        //{
                        //    cmd3.Parameters.Add(new OracleParameter("msg", "insert ข้อความลง log_test เสร็จ"));
                        //    cmd3.ExecuteNonQueryAsync();
                        //    cmd3.Dispose();
                        //}
                        using (var cmd3 = new OracleCommand(SqlCmd.Log.logTest, conn)
                        {
                            CommandType = System.Data.CommandType.Text
                        })
                        {
                            cmd3.Parameters.Add(new OracleParameter("msg", "ส่งข้อความเสร็จ"));
                            cmd3.ExecuteNonQueryAsync();
                            cmd3.Dispose();
                        }
                        reader.Dispose();
                        cmd.Dispose();
                    }
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }
Exemple #2
0
        public void sendSmsByCustNo(int cust_no)
        {
            var        context = GlobalHost.ConnectionManager.GetHubContext <ChatHub>();
            MonitorHub monitor = new MonitorHub();

            using (OracleConnection conn = new OracleConnection(Database.conString))
            {
                try
                {
                    conn.Open();
                    using (var cmd = new OracleCommand(SqlCmd.User.getSmsWithConnId, conn)
                    {
                        CommandType = System.Data.CommandType.Text
                    })
                    {
                        cmd.Parameters.Add(new OracleParameter("cust_no", cust_no));
                        var reader            = cmd.ExecuteReader();
                        List <m_sendSms> data = new List <m_sendSms>();
                        while (reader.Read())
                        {
                            data.Add(new m_sendSms
                            {
                                SMS010_PK     = Int32.Parse(reader["SMS010_PK"].ToString()),
                                CUST_NO       = Int32.Parse(reader["CUST_NO"].ToString()),
                                CONN_ID       = reader["CONN_ID"] == DBNull.Value ? string.Empty : reader["CONN_ID"].ToString(),
                                DEVICE_STATUS = reader["DEVICE_STATUS"].ToString(),
                                SMS_NOTE      = reader["SMS_NOTE"] == DBNull.Value ? string.Empty : reader["SMS_NOTE"].ToString(),
                                CON_NO        = reader["CON_NO"] == DBNull.Value ? string.Empty : reader["CON_NO"].ToString(),
                                SMS_TIME      = (DateTime)reader["SMS_TIME"],
                                SENDER        = reader["SENDER"] == DBNull.Value ? 0 : Int32.Parse(reader["SENDER"].ToString()),
                                SENDER_TYPE   = reader["SENDER_TYPE"].ToString(),
                                SMS010_REF    = reader["SMS010_REF"] == DBNull.Value ? 0 : Int32.Parse(reader["SMS010_REF"].ToString()),
                                READ_STATUS   = reader["READ_STATUS"].ToString()
                            });
                        }
                        if (data.Count != 0)
                        {
                            List <m_SMS010> sms = new List <m_SMS010>();
                            foreach (var s in data)
                            {
                                var temp = new m_SMS010
                                {
                                    SMS010_PK   = s.SMS010_PK,
                                    CUST_NO     = s.CUST_NO,
                                    CON_NO      = s.CON_NO,
                                    SMS_NOTE    = s.SMS_NOTE,
                                    SMS_TIME    = DateTime.Now,
                                    SENDER      = s.SENDER,
                                    SENDER_TYPE = s.SENDER_TYPE,
                                    SMS010_REF  = s.SMS010_REF,
                                    READ_STATUS = s.READ_STATUS
                                };
                                var connectionId = s.CONN_ID;
                                monitor.sendMessage(string.Empty, s.CONN_ID, new { cust_no = cust_no }, new { request_status = "SUCCESS", desc = "Admin ส่งข้อความ", data = temp });
                                context.Clients.Client(connectionId).sms(temp);
                                context.Clients.Client(s.CONN_ID).sms(temp);
                            }
                        }
                        reader.Dispose();
                        cmd.Dispose();
                        //cmd.Parameters.Add(new OracleParameter("cust_no", cust_no));
                        //var reader = cmd.ExecuteReader();
                        //reader.Read();
                        //List<m_SMS010> data = new List<m_SMS010>();
                        //while (reader.Read())
                        //{
                        //    data.Add(new m_SMS010
                        //    {
                        //        SMS010_PK = Int32.Parse(reader["SMS010_PK"].ToString()),
                        //        CUST_NO = Int32.Parse(reader["CUST_NO"].ToString()),
                        //        CON_NO = reader["CON_NO"] == DBNull.Value ? string.Empty : (string)reader["CON_NO"],
                        //        SMS_NOTE = reader["SMS_NOTE"] == DBNull.Value ? string.Empty : (string)reader["SMS_NOTE"],
                        //        SMS_TIME = (DateTime)reader["SMS_TIME"],
                        //        SENDER = reader["SENDER"] == DBNull.Value ? null : (int?)Int32.Parse(reader["SENDER"].ToString()),
                        //        SENDER_TYPE = (string)reader["SENDER_TYPE"],
                        //        SMS010_REF = reader["SMS010_REF"] == DBNull.Value ? null : (int?)Int32.Parse(reader["SMS010_REF"].ToString()),
                        //        READ_STATUS = (string)reader["READ_STATUS"]
                        //    });
                        //}
                        //if(data.Count != 0)
                        //{
                        //    using (var cmd2 = new OracleCommand(SqlCmd.User.getConnIdByCustNo, conn) { CommandType = System.Data.CommandType.Text })
                        //    {
                        //        foreach(var msg in data)
                        //        {
                        //            cmd2.Parameters.Clear();
                        //            cmd2.Parameters.Add(new OracleParameter("cust_no", msg.CUST_NO));
                        //            var reader2 = cmd2.ExecuteReader();
                        //            reader2.Read();
                        //            if (reader2.HasRows)
                        //            {
                        //                if(reader2["CONN_ID"] != DBNull.Value)
                        //                {
                        //                    var connectionId = reader2["CONN_ID"] == DBNull.Value ? string.Empty : (string)reader2["CONN_ID"];
                        //                    context.Clients.Client(connectionId).sms(msg);
                        //                }
                        //            }
                        //        }
                        //    }
                        //}
                        //reader.Dispose();
                        //cmd.Dispose();
                    }
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }