private static List<RawConversation> GetConversationList()
{
List<RawConversation> rawMessageList = new List<RawConversation>();
NpgsqlConnection conn = new NpgsqlConnection("Server=52.74.179.57;Port=5432;UserId=postgres;Password=1234;Database=smsdb;");
conn.Open();
try
{
#region Retrieve Data
// Define a query returning a single row result set
string query = @"
select M.phone_number, vm, rm, U.location, U.nationality, U.age_group, U.gender
from
(
select phone_number, sms_content as vm, null as rm
from raw_conversation
where ""from"" like 'Vol%'
union
select phone_number, null as vm, sms_content as rm
from raw_conversation
where ""from"" not like 'Vol%'
) M
INNER JOIN
user_data U
ON (M.phone_number = U.contact_number)
LIMIT 5
";
NpgsqlCommand command = new NpgsqlCommand(query, conn);
// Execute the query and obtain a result set
NpgsqlDataReader dr = command.ExecuteReader();
// Output rows
while (dr.Read())
{
//V.phone_number, vm, rm, U.location, U.nationality, U.age_group, U.gender
RawConversation message = new RawConversation();
message.PhoneNumber = dr[0].ToString();
message.VolunteerMessage = dr[1].ToString();
message.ReporterMessage = dr[2].ToString();
message.Location = dr[3].ToString();
message.Nationality = dr[4].ToString();
message.AgeGroup = Convert.ToInt32(dr[5]);
message.Gender = dr[6].ToString();
rawMessageList.Add(message);
}
#endregion
}
finally
{
conn.Close();
}
return rawMessageList;
}
private static ProcessedConversation GetSentiment(RawConversation rawConversation)
{
Console.WriteLine("Processing sentiment for phone_number {0} data...", rawConversation.PhoneNumber);
ProcessedConversation processedConversation = new ProcessedConversation();
processedConversation.PhoneNumber = rawConversation.PhoneNumber;
processedConversation.Location = rawConversation.Location;
processedConversation.Nationality = rawConversation.Nationality;
processedConversation.Gender = rawConversation.Gender;
processedConversation.AgeGroup = rawConversation.AgeGroup;
processedConversation.Exchanges = rawConversation.Exchanges;
Random random = new Random();
processedConversation.Category = random.Next(0, 8);
string IODApiKey = "Put your Api Key here.";
try
{
IODJobBatchRequest req = new IODJobBatchRequest();
req.ApiKey = IODApiKey;
if (String.IsNullOrEmpty(rawConversation.VolunteerMessage))
{
processedConversation.VolunteerMsgSenti = 0;
}
else
{
SentimentRequest srVM = new SentimentRequest(IODSource.text, rawConversation.VolunteerMessage);
req.AddRequest(srVM);
srVM.OnResponse = (status, response) =>
{
processedConversation.VolunteerMsgSenti = response.aggregate.score;
};
}
if (String.IsNullOrEmpty(rawConversation.ReporterMessage))
{
processedConversation.ReporterMsgSenti = 0;
}
else
{
SentimentRequest srRM = new SentimentRequest(IODSource.text, rawConversation.ReporterMessage);
req.AddRequest(srRM);
srRM.OnResponse = (status, response) =>
{
processedConversation.ReporterMsgSenti = response.aggregate.score;
};
}
if (String.IsNullOrEmpty(rawConversation.VolunteerMessage + rawConversation.ReporterMessage))
{
processedConversation.OverallMsgSenti = 0;
}
else
{
SentimentRequest srOM = new SentimentRequest(IODSource.text, rawConversation.VolunteerMessage + rawConversation.ReporterMessage);
req.AddRequest(srOM);
srOM.OnResponse = (status, response) =>
{
processedConversation.OverallMsgSenti = response.aggregate.score;
};
}
if (!String.IsNullOrEmpty(rawConversation.VolunteerMessage + rawConversation.ReporterMessage))
{
Console.WriteLine("Making request ...");
req.MakeRequest();
}
}
catch (Exception e)
{
Console.WriteLine(string.Format("\r\nException: {0}", e.Message));
}
return processedConversation;
}
private static List<RawConversation> GetUniqueConversationList(List<RawConversation> rawlist)
{
List<RawConversation> uniqueList = new List<RawConversation>();
string phoneNumber = "";
RawConversation tempMessage = new RawConversation();
foreach (RawConversation message in rawlist)
{
//V.phone_number, vm, rm, U.location, U.nationality, U.age_group, U.gender
if (phoneNumber != message.PhoneNumber)
{
if (phoneNumber != "")
{
uniqueList.Add(tempMessage);
}
tempMessage = new RawConversation();
phoneNumber = message.PhoneNumber;
tempMessage.PhoneNumber = message.PhoneNumber;
tempMessage.VolunteerMessage = message.VolunteerMessage;
tempMessage.ReporterMessage = message.ReporterMessage;
tempMessage.Location = message.Location;
tempMessage.Nationality = message.Nationality;
tempMessage.AgeGroup = message.AgeGroup;
tempMessage.Gender = message.Gender;
tempMessage.Exchanges = 1;
}
else
{
tempMessage.VolunteerMessage += message.VolunteerMessage;
tempMessage.ReporterMessage += message.ReporterMessage;
tempMessage.Exchanges++;
}
}
uniqueList.Add(tempMessage);
return uniqueList;
}