//Update statement
public void UpdateSmsStatusToSent(string id)
{
string query = "UPDATE sms SET sent=1 WHERE id=" + id;
try
{
//Open connection
if (this.OpenConnection() == true)
{
//create mysql command
MySqlCommand cmd = new MySqlCommand();
//Assign the query using CommandText
cmd.CommandText = query;
//Assign the connection using Connection
cmd.Connection = connection;
//Execute query
cmd.ExecuteNonQuery();
//close connection
this.CloseConnection();
}
}
catch (Exception ex)
{
ServiceLog.WriteErrorLog(ex);
//close Connection
this.CloseConnection();
}
}
public List <Message> Select_SMSPending_Type_ConnectionIssue()
{
try
{
var list = new List <Message>();
string query = @"
select sms.id, sms.hostId,h.name as hostname, DATE_FORMAT(sms.createddate, "" %d-%m-%Y %T"") as createddate, sms_type.message
from sms join sms_type on sms.type = sms_type.id and sms_type.allowsendsms = 1
join host h on h.id = sms.hostId and h.allow_send_sms=1
where sms.sent = 0 and sms.sms_groupId= " + SMSGroup.GROUP_CONNECTION_ISSUE + @"
order by sms.createddate asc
";
//Create a list to store the result
//Open connection
if (this.OpenConnection() == true)
{
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection);
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
list.Add(new Message()
{
id = dataReader["id"] + "",
hostId = dataReader["hostId"] + "",
message = dataReader["message"] + "",
hostname = dataReader["hostname"] + "",
createddate = dataReader["createddate"] + ""
});
}
//close Data Reader
dataReader.Close();
//close Connection
this.CloseConnection();
//return list to be displayed
return(list);
}
else
{
return(list);
}
}
catch (Exception ex)
{
ServiceLog.WriteErrorLog(ex);
//close Connection
this.CloseConnection();
return(new List <Message>());
}
}
public List <People> Select_PeopleToSend()
{
try
{
var list = new List <People>();
string query = @"
select uh.hostId, uh.userId, u.phone
from user_host uh join user u on uh.userId = u.Id
join host h on h.id = uh.hostId
where u.status = 1 and sendsms = 1 and h.allow_send_sms=1
order by uh.hostId
";
//Create a list to store the result
//Open connection
if (this.OpenConnection() == true)
{
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection);
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
list.Add(new People()
{
hostId = dataReader["hostId"] + "",
userId = dataReader["userId"] + "",
phone = dataReader["phone"] + ""
});
}
//close Data Reader
dataReader.Close();
//close Connection
this.CloseConnection();
//return list to be displayed
return(list);
}
else
{
return(list);
}
}
catch (Exception ex)
{
ServiceLog.WriteErrorLog(ex);
//close Connection
this.CloseConnection();
return(new List <People>());
}
}
/// <summary>
/// Open conn
/// </summary>
/// <returns></returns>
public bool CloseConnection()
{
try
{
if (connection != null && connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
ServiceLog.WriteErrorLog(ex);
return(false);
}
return(true);
}
/// <summary>
/// Open conn
/// </summary>
/// <returns></returns>
public bool OpenConnection()
{
try
{
if (connection.State == ConnectionState.Open)
{
return(true);
}
connection.Open();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
ServiceLog.WriteErrorLog(ex);
return(false);
}
return(true);
}
public bool SendMessage(string PhoneNumber, string Message)
{
try
{
var response = SMSAPI.SendSMS(PhoneNumber, Message, "");
if (response.code != 0)
{
var errMsg = "Error code: " + response.code + "; Message: " + response.msg;
ServiceLog.WriteErrorLog(errMsg);
return(false);
}
}
catch (Exception ex)
{
ServiceLog.WriteErrorLog(ex);
return(false);
}
return(true);
}
protected override void OnStart(string[] args)
{
hasDataStatus = new Hashtable();
// Load config
config = FileHelper.ReadConfigFile();
if (config.Exception != null)
{
ServiceLog.WriteErrorLog(config.Exception);
// Stop service
OnStop();
}
// Try to connect to DB
mySql = new MySqlHelper(config.MySql.Server, config.MySql.User, config.MySql.Pass, config.MySql.DBName, config.MySql.SSL);
sms = new SmsHelper(config.SmsApi.APIEndpoint, config.SmsApi.User, config.SmsApi.Pass);
timeDelay = new Timer();
this.timeDelay.Interval = config.Interval;
this.timeDelay.Elapsed += new ElapsedEventHandler(WorkProcess);
timeDelay.Enabled = true;
ServiceLog.WriteErrorLog("SMS service started");
}
public void SendSMS_Warning()
{
// Try to connect to DB
var mySql = new MySqlHelper(config.MySql.Server, config.MySql.User, config.MySql.Pass, config.MySql.DBName, config.MySql.SSL);
// Get data and send message
// Try to send a message
// get all people to send sms
var lstPeople = mySql.Select_PeopleToSend();
// ServiceLog.WriteErrorLog("lstPeople:" + lstPeople.Count);
if (lstPeople != null && lstPeople.Count > 0)
{
var lstSms = mySql.Select_SMSPending_Type_Warning();
// ServiceLog.WriteErrorLog("lstSms:" + lstSms.Count);
if (lstSms != null && lstSms.Count > 0)
{
//Sending message...
foreach (var itemSms in lstSms)
{
var people_Filter = lstPeople.Where(o => o.hostId == itemSms.hostId).ToList();
var people_Filter_phone_number = new List <string>();
if (people_Filter != null && people_Filter.Count > 0)
{
people_Filter_phone_number = people_Filter.Select(o => o.phone).ToList();
}
// List default
if (config.SmsApi.ListPeopleByPhoneNumber != null && config.SmsApi.ListPeopleByPhoneNumber.Count > 0)
{
people_Filter_phone_number.AddRange(config.SmsApi.ListPeopleByPhoneNumber);
}
// Distinct
people_Filter_phone_number = people_Filter_phone_number.Distinct().ToList();
if (people_Filter_phone_number != null && people_Filter_phone_number.Count > 0)
{
itemSms.message = convertToUnSign(itemSms.hostname + ": " + itemSms.message) + " - luc " + itemSms.createddate;
// List phone
var listPhone = string.Join(",", people_Filter_phone_number);
ServiceLog.WriteErrorLog(string.Format("[GROUP=" + SMSGroup.GROUP_WARNING + "] SMS sending...: HostId: {0}; SMSId: {1}; List Phone number: {2}", itemSms.hostId, itemSms.id, listPhone));
// send each person
foreach (var phoneNumber in people_Filter_phone_number)
{
// Send message
if (sms.SendMessage(phoneNumber, itemSms.message))
{
// Update message record to sent
mySql.UpdateSmsStatusToSent(itemSms.id);
}
}
} // End if (people_Filter != null && people_Filter.Count > 0)
} // End foreach (var itemSms in lstSms)
} // End if (lstSms != null && lstSms.Count > 0)
}
// Set status
hasDataStatus[strWarning] = true;
}
private void LogService(string content)
{
ServiceLog.WriteErrorLog(content);
}
