public List <OutgoingTwilioMessage> GetAllOutboxMessages()
{
List <OutgoingTwilioMessage> outboxMsgs = new List <OutgoingTwilioMessage>();
SqlCeCommand cmd = dbConnection.CreateCommand();
cmd.CommandText = "SELECT COUNT(*) FROM " + TableOutbox;
Int32 count = (Int32)cmd.ExecuteScalar();
if (count == 0)
{
App.logger.Log("DBStore.GetAllOutboxMessages(): no messages to offload");
return(outboxMsgs);
}
// DEBUG
App.logger.Log("DBStore.GetAllOutboxMessages(): offloading " + count + " outbox message(s)");
try
{
//cmd.CommandText = "SELECT TOP (" + maxBatchLoad + ") * FROM " + TableActivations;
cmd.CommandText = "SELECT * FROM " + TableOutbox;
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);
while (rs.Read())
{
OutgoingTwilioMessage outMsg = new OutgoingTwilioMessage
{
id = rs.GetInt32(0),
Timestamp = rs.GetDateTime(1),
From = rs.GetString(2),
To = rs.GetString(3),
Action = rs.GetString(4),
Method = rs.GetString(5),
Body = rs.GetString(6),
MediaURLs = rs.GetString(7),
Client = rs.GetString(8)
};
// DEBUG
App.logger.Log("DBStore.GetAllOutboxMessages(): message = " + outMsg.ToString());
outboxMsgs.Add(outMsg);
}
}
catch (Exception ex)
{
var message = "! Error in DBStore.GetAllOutboxMessages(): " + ex.Message + "\n" + ex.TargetSite;
App.logger.Log(message);
}
return(outboxMsgs);
}
public int StoreOutboxMessage(OutgoingTwilioMessage msgout)
{
int id = 0; // ID of the inserted SQL CE record
// This results to
// INSERT INTO User (FirstName, LastName) VALUES ('test','test'),('test','test'),... ;
SqlCeCommand cmd = dbConnection.CreateCommand();
cmd.CommandText = TableOutbox;
cmd.CommandType = CommandType.TableDirect;
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);
SqlCeUpdatableRecord rec = rs.CreateRecord();
try
{
// DEBUG
/*
* App.logger.Log("DBStore.StoreDataEntries(): Storing new entry:\n" +
* "\ttimestamp: " + qlmActivation.Timestamp +
* "\tkey: " + qlmActivation.LicenseKey +
* "\texpiration: " + qlmActivation.ExpirationDateTime);
*/
rec.SetSqlDateTime(1, DateTime.Now);
rec.SetString(2, msgout.From);
rec.SetString(3, msgout.To);
rec.SetString(4, msgout.Action);
rec.SetString(5, msgout.Method);
rec.SetString(6, msgout.Body);
rec.SetString(7, msgout.Client);
rs.Insert(rec);
// Get this inserter record ID
cmd.CommandText = "SELECT @@IDENTITY";
cmd.CommandType = CommandType.Text;
id = Convert.ToInt32(cmd.ExecuteScalar());
msgout.id = id; // Assign id to this message for further referencing from message to its DBStore record
// DEBUG
App.logger.Log("DBStore.StoreOutboxMessage() storing outbox message from " + msgout.From + " to " + msgout.To + ", ID=" + id);
}
catch (Exception ex)
{
var message = "! Error in DBStore.StoreOutboxMessage(): " + ex.Message + "\n" + ex.TargetSite;
App.logger.Log(message);
}
cmd.Dispose();
return(id);
}
public async Task <HttpResponseMessage> ReceiveMmsMessageFromClient(string to, string body, string mediaurls, string gatewaynum = "")
{
// DEBUG
App.logger.Log("SMSController.ReceiveMessageFromClient(): New outbox message to " + to);
if (string.IsNullOrWhiteSpace(to) || string.IsNullOrWhiteSpace(body))
{
return(new HttpResponseMessage(HttpStatusCode.NoContent));
}
string from = App.restConfiguration.TwilioPhoneNumber;
if (!string.IsNullOrWhiteSpace(gatewaynum))
{
from = gatewaynum;
App.logger.Log("SMSController.ReceiveMessageFromClient(): Client specified gateway phone number +" + from);
}
OutgoingTwilioMessage msg = new OutgoingTwilioMessage()
{
From = $"+{from}", // From phone number (gateway number via which the message will be sent)
To = $"+{to}", // Destination phone number (any recipient's mobile number)
Action = "", // unused
Method = "", // unused
Body = body, // Message body
MediaURLs = mediaurls, // URL-Encoded comma-separated set of URL-Endcoded URLs (two-pass URL-Encode!)</param>
Client = "" // Client identofier, unused here, but can be relevant for incoming messages if implemented
};
try
{
App.messageRepository.StoreIncomingMessageFromClient(msg);
HttpResponseMessage response = await ForwardMessageToTwilio(msg);
return(response);
}
catch
{
return(new HttpResponseMessage(HttpStatusCode.BadGateway));
}
finally
{ }
}
public void StoreIncomingMessageFromClient(OutgoingTwilioMessage msg)
{
App.dbstore.StoreOutboxMessage(msg);
}
// Documentation for Twilio sending API: https://www.twilio.com/docs/api/rest/request
//
private async Task <HttpResponseMessage> ForwardMessageToTwilio(OutgoingTwilioMessage msg)
{
// DEBUG
App.logger.Log("SMSController.ForwardMessageToTwilio(): Forwarding message via Twilio (" + msg.From + ") to " + msg.To + " , ID=" + msg.id);
using (var client = new HttpClient {
BaseAddress = new Uri(App.restConfiguration.TwilioBaseUrl)
})
{
client.DefaultRequestHeaders.Authorization =
new AuthenticationHeaderValue(
"Basic",
Convert.ToBase64String(Encoding.ASCII.GetBytes($"{App.restConfiguration.TwilioAccountsid}:{App.restConfiguration.TwilioToken}")));
string requestUri = $"/2010-04-01/Accounts/{App.restConfiguration.TwilioAccountsid}/Messages";
// POST parameters for Twilio
var keyValues = new List <KeyValuePair <string, string> >();
// Basic SMS content
keyValues.Add(new KeyValuePair <string, string>("To", msg.To));
keyValues.Add(new KeyValuePair <string, string>("From", msg.From));
keyValues.Add(new KeyValuePair <string, string>("Body", msg.Body));
// Optional MMS media URL(s)
if (!String.IsNullOrEmpty(msg.MediaURLs))
{
// UrlDecode first pass -- allow delimiters to work
var mediaUrlsDecoded1 = WebUtility.UrlDecode(msg.MediaURLs);
App.logger.Log("SMSController.ForwardMessageToTwilio(): MMS mode: MediaUrls UrlDecoded 1st pass is '" + mediaUrlsDecoded1 + "'");
// Delimit decoded mediaUrls to a set of mediaUrls
var mediaUrls = mediaUrlsDecoded1.Split(',');
if (mediaUrls.Count() > 0)
{
foreach (var mediaUrl in mediaUrls)
{
// Add each media URL after UrlDecode (second pass)
var mediaUrlDecoded2 = WebUtility.UrlDecode(mediaUrl); // This is possibly not needed (1st pass decode takes care about it)
App.logger.Log("SMSController.ForwardMessageToTwilio(): Adding UrlDecoded 2nd pass MediaUrl '" + mediaUrlDecoded2 + "'");
keyValues.Add(new KeyValuePair <string, string>("MediaUrl", mediaUrlDecoded2));
}
}
else
{
App.logger.Log("SMSController.ForwardMessageToTwilio(): MediaUrl format error, unable to parse MediaUrls. Will use SMS mode.");
}
}
var content = new FormUrlEncodedContent(keyValues);
HttpResponseMessage response = new HttpResponseMessage();
try
{
response = await client.PostAsync(requestUri, content);
}
catch (HttpRequestException hre)
{
App.logger.Log("SMSController.ForwardMessageToTwilio(): Http request to Twilio failed: " + hre.Message + " (" + hre.InnerException.Message + ")");
// Generate new response on behalf of failed connected endpoint
HttpResponseMessage resp = Request.CreateResponse(HttpStatusCode.GatewayTimeout, "Message sending failed: " + hre.Message + " (" + hre.InnerException.Message + ")");
return(resp);
}
if (response.IsSuccessStatusCode)
{
App.messageRepository.DeleteOutboxMessage(msg.id); // Sent successfully via Twilio, delete the message from local Outbox database
App.logger.Log("SMSController.ForwardMessageToTwilio(): Sent successfully via Twilio with status '" + response.StatusCode + "'");
}
else
{
// DEBUG
App.logger.Log("SMSController.ForwardMessageToTwilio(): Forwarding to Twilio failed with status '" + response.StatusCode + "'");
if (response.StatusCode == HttpStatusCode.BadRequest)
{
App.logger.Log("SMSController.ForwardMessageToTwilio(): Deleting invalid outbox message");
App.messageRepository.DeleteOutboxMessage(msg.id);
}
}
// Forward Twilio returned status code to the client
return(response);
}
}
