public async Task MarkChatMessageAsSeen(string phoneNumOfMsgReceiver, PrimaryKeyLeftBehindMsg primaryKeyOfLeftBehindMsg)
{
string sql = "UPDATE " + "[" + phoneNumOfMsgReceiver + "] "
+ "SET " + ApplicationConstants.SEEN_COLUMN + " = 'Yes' "
+ "WHERE " + ApplicationConstants.MSG_ID + " = " + primaryKeyOfLeftBehindMsg.MsgID
+ " AND " + ApplicationConstants.MSG_PHONE_NUM_WRITER + " = " + "'" + primaryKeyOfLeftBehindMsg.PhoneNumWriter + "';";
await SQLNonQueryExecutor.ExecuteNonQuery(sql, Configuration);
}
public async Task PlaceChatMessageInDatabase(ChatMessage chatMessage, string phoneNumOfReceivingUser)
{
string sql = "INSERT INTO " + "[" + phoneNumOfReceivingUser + "]"
+ " (" + ApplicationConstants.MSG_ID + ", " + ApplicationConstants.MSG_PHONE_NUM_WRITER + ", " + ApplicationConstants.MSG_COLUMN + ", "
+ ApplicationConstants.DATE_COLUMN + ", " + ApplicationConstants.WRITER_COLUMN + ", " + ApplicationConstants.SEEN_COLUMN + ") "
+ "VALUES "
+ "( " + "'" + chatMessage.MsgID + "' ," + "'" + chatMessage.PhoneNumOfWriter + "' ," + "'" + chatMessage.Msg + "' ,"
+ "'" + chatMessage.Date + "'" + " ," + "'" + chatMessage.Writer + "'" + " ," + "'" + chatMessage.SeenByRecipient + "'" + ");";
await SQLNonQueryExecutor.ExecuteNonQuery(sql, Configuration);
}
public async Task CreateChatMessageTableForUser(string phoneNumber)
{
string sqlText = "CREATE TABLE " + "[" + phoneNumber + "]" + " ("
+ ApplicationConstants.MSG_ID + " INTEGER, "
+ ApplicationConstants.MSG_PHONE_NUM_WRITER + " varchar(255),"
+ ApplicationConstants.MSG_COLUMN + " varchar(255),"
+ ApplicationConstants.DATE_COLUMN + " varchar(255),"
+ ApplicationConstants.WRITER_COLUMN + " varchar(255),"
+ ApplicationConstants.SEEN_COLUMN + " varchar(255), "
+ "CONSTRAINT PK_" + phoneNumber + " PRIMARY KEY (" + ApplicationConstants.MSG_ID + "," + ApplicationConstants.MSG_PHONE_NUM_WRITER + ")"
+ ");";
await SQLNonQueryExecutor.ExecuteNonQuery(sqlText, Configuration);
}
public async Task DeleteAllYourMessagesFromFriendsTable(string yourPhoneNum, string friendPhoneNum)
{
string sql = "DELETE FROM " + "[" + friendPhoneNum + "] "
+ "WHERE " + ApplicationConstants.MSG_PHONE_NUM_WRITER + "=" + "'" + yourPhoneNum + "';";
await SQLNonQueryExecutor.ExecuteNonQuery(sql, Configuration);
}
public async Task ResetUserTableInChatHub(string userPhoneNum)
{
string sql = "DELETE FROM " + "[" + userPhoneNum + "];";
await SQLNonQueryExecutor.ExecuteNonQuery(sql, Configuration);
}
