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