public HttpResponseMessage Receive(FormDataCollection formData)
{
// DEBUG
App.logger.Log("SMSController.Receive(): receiving message from Twilio");
IncomingTwilioMessage msgIn = new IncomingTwilioMessage()
{
Timestamp = DateTime.Now,
AccountSid = formData["AccountSid"] ?? "",
ApiVersion = formData["ApiVersion"] ?? "",
Body = formData["Body"] ?? "",
From = formData["From"] ?? "",
FromCity = formData["FromCity"] ?? "",
FromCountry = formData["FromCountry"] ?? "",
FromState = formData["FromState"] ?? "",
FromZip = formData["FromZip"] ?? "",
MessageSid = formData["MessageSid"] ?? "",
NumMedia = formData["NumMedia"] ?? "",
NumSegments = formData["NumSegments"] ?? "",
SmsSid = formData["SmsSid"] ?? "",
SmsStatus = formData["SmsStatus"] ?? "",
To = formData["To"] ?? "",
ToCity = formData["ToCity"] ?? "",
ToCountry = formData["ToCountry"] ?? "",
ToState = formData["ToState"] ?? "",
ToZip = formData["ToZip"] ?? "",
MediaURLs = ""
};
// DEBUG
App.logger.Log("SMSController.Receive(): new message received via Twilio from " + msgIn.From + " to " + msgIn.To);
App.messageRepository.StoreIncomingMessageFromTwilio(msgIn);
return(new HttpResponseMessage(HttpStatusCode.OK));
}
public void StoreIncomingMessageFromTwilio(IncomingTwilioMessage msg)
{
App.dbstore.StoreInboxMessage(msg);
}
public List <IncomingTwilioMessage> GetAllInboxMessages()
{
List <IncomingTwilioMessage> inboxMsgs = new List <IncomingTwilioMessage>();
SqlCeCommand cmd = dbConnection.CreateCommand();
cmd.CommandText = "SELECT COUNT(*) FROM " + TableInbox;
Int32 count = (Int32)cmd.ExecuteScalar();
if (count == 0)
{
App.logger.Log("DBStore.GetAllInboxMessages(): no messages to offload");
return(inboxMsgs);
}
App.logger.Log("DBStore.GetAllInboxMessages(): offloading " + count + " inbox message(s)");
try
{
//cmd.CommandText = "SELECT TOP (" + maxBatchLoad + ") * FROM " + TableActivations;
cmd.CommandText = "SELECT * FROM " + TableInbox;
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);
while (rs.Read())
{
IncomingTwilioMessage inMsg = new IncomingTwilioMessage
{
id = rs.GetInt32(0),
Timestamp = rs.GetDateTime(1),
AccountSid = rs.GetString(2),
ApiVersion = rs.GetString(3),
Body = rs.GetString(4),
From = rs.GetString(5),
FromCity = rs.GetString(6),
FromCountry = rs.GetString(7),
FromState = rs.GetString(8),
FromZip = rs.GetString(9),
MessageSid = rs.GetString(10),
NumMedia = rs.GetString(11),
NumSegments = rs.GetString(12),
SmsSid = rs.GetString(13),
SmsStatus = rs.GetString(14),
ToState = rs.GetString(15),
To = rs.GetString(16),
ToCity = rs.GetString(17),
ToCountry = rs.GetString(18),
ToZip = rs.GetString(19),
MediaURLs = rs.GetString(20)
};
// DEBUG
App.logger.Log("DBStore.GetAllInboxMessages(): message = " + inMsg.ToString());
inboxMsgs.Add(inMsg);
}
}
catch (Exception ex)
{
var message = "! Error in DBStore.GetAllInboxMessages(): " + ex.Message + "\n" + ex.TargetSite;
App.logger.Log(message);
}
return(inboxMsgs);
}
public bool StoreInboxMessage(IncomingTwilioMessage msgin)
{
bool retVal = true;
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 = TableInbox;
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, msgin.Timestamp);
rec.SetString(2, msgin.AccountSid);
rec.SetString(3, msgin.ApiVersion);
rec.SetString(4, msgin.Body);
rec.SetString(5, msgin.From);
rec.SetString(6, msgin.FromCity);
rec.SetString(7, msgin.FromCountry);
rec.SetString(8, msgin.FromState);
rec.SetString(9, msgin.FromZip);
rec.SetString(10, msgin.MessageSid);
rec.SetString(11, msgin.NumMedia);
rec.SetString(12, msgin.NumSegments);
rec.SetString(13, msgin.SmsSid);
rec.SetString(14, msgin.SmsStatus);
rec.SetString(15, msgin.ToState);
rec.SetString(16, msgin.To);
rec.SetString(17, msgin.ToCity);
rec.SetString(18, msgin.ToCountry);
rec.SetString(19, msgin.ToZip);
rec.SetString(20, msgin.MediaURLs);
rs.Insert(rec);
// Get this inserter record ID
cmd.CommandText = "SELECT @@IDENTITY";
cmd.CommandType = CommandType.Text;
id = Convert.ToInt32(cmd.ExecuteScalar());
// DEBUG
App.logger.Log("DBStore.StoreInboxMessage() storing inbox message from '" + msgin.From + "' to '" + msgin.To + "', ID=" + id);
}
catch (Exception ex)
{
var message = "! Error in DBStore.StoreInboxMessage(): " + ex.Message + "\n" + ex.TargetSite;
App.logger.Log(message);
retVal = false;
}
cmd.Dispose();
return(retVal);
}
