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();
}
}
}
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();
}
}
}