//TODO Just search chats with type = 0 (Dialog)
public IList <CContactInfo> GetHasDialogContacts(Guid ownerId)
{
s_log.LogInfo($@"Data provider's method '{nameof(DeleteContact)}({ownerId})' is called");
#region SQL Query
var sql = @"
SELECT
[Id] = c.Id,
[OwnerId] = c.OwnerId,
[UserId] = c.UserId,
[IsBlocked] = c.IsBlocked
FROM (
SELECT DISTINCT
p.ChatId
FROM chatsParticipants p
INNER JOIN chatsParticipants p2
ON p.ChatId = p2.ChatId
WHERE p.UserId = @OwnerId
GROUP BY p.ChatId
HAVING COUNT(p.ChatId) = 2
) as res
INNER JOIN chatsParticipants p
ON res.ChatId = p.ChatId
INNER JOIN contactsLists c
ON p.UserId = c.UserId
WHERE p.UserId != @OwnerId
AND c.OwnerId = @OwnerId
";
#endregion
using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString))
{
using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection))
{
try
{
var result = executor.GetData(new CContactInfoMapper(), sql,
SSqlParameterCreator.Create(
"@OwnerId", ownerId, System.Data.SqlDbType.UniqueIdentifier, false
)
);
executor.Commit();
return(result);
}
catch (SqlException e)
{
s_log.LogError($@"{nameof(DeleteContact)}({ownerId}): Error occured during SQL query execution", e);
s_log.LogInfo($@"{nameof(DeleteContact)}({ownerId}): Operation was rolled back because of error");
Console.WriteLine($@"{nameof(DeleteContact)}({ownerId}): Error occured during SQL query execution");
Console.WriteLine("Operation was rolled back because of error");
return(null);
}
}
}
}
public IList <CUserInfo> GetContactsLastActiveDate(Guid ownerId)
{
s_log.LogInfo($@"Data provider's method '{nameof(GetContactsLastActiveDate)}({ownerId})' is called");
#region SQL Query
var sql = @"
SELECT
[Id] = u.Id,
[Login] = u.Login,
[Password] = u.Password,
[LastActiveDate] = u.LastActiveDate,
[ActivityStatus] = u.ActivityStatus,
[Avatar] = u.Avatar
FROM contactsLists c
INNER JOIN users u
ON c.UserId = u.Id
WHERE c.OwnerId = @OwnerId
";
#endregion
using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString))
{
using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection))
{
try
{
var result = executor.GetData(new CUserInfoMapper(), sql,
SSqlParameterCreator.Create(
"@OwnerId", ownerId, System.Data.SqlDbType.UniqueIdentifier, false
)
);
executor.Commit();
return(result);
}
catch (SqlException e)
{
s_log.LogError($@"{nameof(GetContactsLastActiveDate)}({ownerId}): Error occured during SQL query execution",
e);
s_log.LogInfo(
$@"{nameof(GetContactsLastActiveDate)}({ownerId}): Operation was rolled back because of error");
Console.WriteLine(
$@"{nameof(GetContactsLastActiveDate)}({ownerId}): Error occured during SQL query execution");
Console.WriteLine("Operation was rolled back because of error");
return(null);
}
}
}
}
// #region Static methods
// [Obsolete("Use instance method instead of static")]
// public static IList<CChatInfo> GetChatsByParticipantId(Guid userId)
// {
// #region SQL Query
// var sqlQuery = @"
//SELECT DISTINCT
// [Id] = c.Id,
// [Title] = c.Title,
// [OwnerId] = c.OwnerId,
// [IsPersonal] = c.IsPersonal,
// [Type] = c.Type
//FROM chatsParticipants p
//INNER JOIN chats c
//ON p.ChatId = c.Id
// WHERE p.UserId = @UserId
//";
// #endregion
// var result = CDbQueryExecutor.GetDataParametrized(new CChatInfoMapper(), sqlQuery,
// SSqlParameterCreator.Create(
// "@UserId", userId, System.Data.SqlDbType.UniqueIdentifier, false
// )
// );
// return result;
// }
// [Obsolete("Use instance method instead of static")]
// public static Int32 GetUnreadMessagesCount(Guid userId, Guid chatId)
// {
// #region SQL Query
// var sqlQuery = @"
//SELECT DISTINCT
// COUNT(*)
//FROM messagesInChats
// WHERE ChatId = @ChatId
// AND ToUserId = @UserId
// AND IsRead = 0
//";
// #endregion
// Int32 result = CDbQueryExecutor.GetScalar<Int32>(sqlQuery,
// SSqlParameterCreator.Create("@UserId", userId, System.Data.SqlDbType.UniqueIdentifier, false),
// SSqlParameterCreator.Create("@ChatId", chatId, System.Data.SqlDbType.UniqueIdentifier, false)
// );
// return result;
// }
// [Obsolete("Use instance method instead of static")]
// public static IList<CChatInfo> GetChatsByOwnerId(Guid userId)
// {
// #region SQL Query
// var sqlQuery = @"
//SELECT
// [Id] = Id,
// [Title] = Title,
// [OwnerId] = OwnerId,
// [IsPersonal] = IsPersonal,
// [Type] = Type
//FROM chats AS c
// WHERE c.OwnerId = @OwnerId
//";
// #endregion
// var result = CDbQueryExecutor.GetData(new CChatInfoMapper(), sqlQuery);
// return result;
// }
// [Obsolete("Use instance method instead of static")]
// public static CChatInfo GetChatById(Guid chatId)
// {
// #region SQL Query
// var sqlQuery = @"
//SELECT
// [Id] = Id,
// [Title] = Title,
// [OwnerId] = OwnerId,
// [IsPersonal] = IsPersonal,
// [Type] = Type
//FROM chats AS c
// WHERE c.Id = @Id
//";
// #endregion
// var result = CDbQueryExecutor.GetItem(new CChatInfoMapper(), sqlQuery);
// return result;
// }
// [Obsolete("Use instance method instead of static")]
// public static CChatInfo GetDialog(Guid userId, Guid participantId)
// {
// #region SQL Query
// var sqlQuery = @"
//SELECT
// [Id] = c.Id,
// [Title] = c.Title,
// [OwnerId] = c.OwnerId,
// [IsPersonal] = c.IsPersonal,
// [Type] = c.Type
//FROM (
// SELECT DISTINCT
// p.ChatId
// FROM chatsParticipants p
// INNER JOIN chatsParticipants p2
// ON p.ChatId = p2.ChatId
// WHERE p.UserId = @UserId
// GROUP BY p.ChatId
// HAVING COUNT(p.ChatId) = 2
// ) as res
//INNER JOIN chatsParticipants p
//ON res.ChatId = p.ChatId
//INNER JOIN chats c
//ON p.ChatId = c.Id
// WHERE p.UserId = @ParticipantId
//";
// #endregion
// var result = CDbQueryExecutor.GetItemParametrized(new CChatInfoMapper(), sqlQuery,
// SSqlParameterCreator.Create(
// "@UserId", userId, System.Data.SqlDbType.UniqueIdentifier, false
// ),
// SSqlParameterCreator.Create(
// "@ParticipantId", participantId, System.Data.SqlDbType.UniqueIdentifier, false
// )
// );
// return result;
// }
// [Obsolete("Use instance method instead of static")]
// public static CChatInfo CreateChat(CChatInfo chatInfo)
// {
// #region SQL Query
// var sqlQuery = @"
//INSERT INTO
// chats
//VALUES (
// @Id, @Title, @OwnerId, @IsPersonal, @Type
//)
//";
// #endregion
// var id = Guid.NewGuid();
// CChatInfo output = null;
// var result = CDbQueryExecutor.CreateItem(sqlQuery,
// SSqlParameterCreator.Create(
// "@Id", id, System.Data.SqlDbType.UniqueIdentifier, false
// ),
// SSqlParameterCreator.Create(
// "@Title", chatInfo.Title, System.Data.SqlDbType.NVarChar, false
// ),
// SSqlParameterCreator.Create(
// "@OwnerId", chatInfo.OwnerId, System.Data.SqlDbType.UniqueIdentifier, false
// ),
// SSqlParameterCreator.Create(
// "@IsPersonal", chatInfo.IsPersonal, System.Data.SqlDbType.Bit, false
// ),
// SSqlParameterCreator.Create(
// "@Type", chatInfo.Type, System.Data.SqlDbType.TinyInt, false
// )
// );
// output = new CChatInfo(id, chatInfo.Title, chatInfo.OwnerId, chatInfo.IsPersonal, chatInfo.Type);
// return output;
// }
// #endregion
public IList <CChatInfo> GetChatsByParticipantId(Guid userId)
{
s_log.LogInfo($@"Data provider's method '{nameof(GetChatsByParticipantId)}({userId})' is called");
#region SQL Query
var sqlQuery = @"
SELECT DISTINCT
[Id] = c.Id,
[Title] = c.Title,
[OwnerId] = c.OwnerId,
[IsPersonal] = c.IsPersonal,
[Type] = c.Type
FROM chatsParticipants p
INNER JOIN chats c
ON p.ChatId = c.Id
WHERE p.UserId = @UserId
";
#endregion
using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString))
{
using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection))
{
try
{
var result = executor.GetData(new CChatInfoMapper(), sqlQuery,
SSqlParameterCreator.Create(
"@UserId", userId, System.Data.SqlDbType.UniqueIdentifier, false
)
);
executor.Commit();
return(result);
}
catch (SqlException e)
{
s_log.LogError($@"{nameof(GetChatsByParticipantId)}({userId}): Error occured during SQL query execution", e);
s_log.LogInfo($@"{nameof(GetChatsByParticipantId)}({userId}): Operation was rolled back because of error");
Console.WriteLine($@"{nameof(GetChatsByParticipantId)}({userId}): Error occured during SQL query execution");
Console.WriteLine("Operation was rolled back because of error");
return(null);
}
}
}
}
// #region Static
// public static IList<CContactInfo> GetAllContactsByOwnerId(Guid ownerId)
// {
// #region SQL Query
// var sql = @"
//SELECT
// [Id] = c.Id,
// [OwnerId] = c.OwnerId,
// [UserId] = c.UserId,
// [IsBlocked] = c.IsBlocked
//FROM contactsLists AS c
// WHERE c.OwnerId = @OwnerId
//";
// #endregion
// return CDbQueryExecutor.GetDataParametrized(new CContactInfoMapper(), sql,
// SSqlParameterCreator.Create(
// "@OwnerId", ownerId, System.Data.SqlDbType.UniqueIdentifier, false
// )
// );
// }
// public static Int32 CreateContact(Guid ownerId, Guid userId)
// {
// #region SQL Query
// var sql = @"
//INSERT INTO contactsLists (Id, OwnerId, UserId, IsBlocked)
// VALUES (
//DEFAULT, @OwnerId, @UserId, 0
//)
//";
// #endregion
// return CDbQueryExecutor.CreateItemParametrized(sql,
// SSqlParameterCreator.Create(
// "@OwnerId", ownerId, System.Data.SqlDbType.UniqueIdentifier, false
// ),
// SSqlParameterCreator.Create(
// "@UserId", userId, System.Data.SqlDbType.UniqueIdentifier, false
// )
// );
// }
// public static Int32 DeleteContact(Guid ownerId, Guid userId)
// {
// #region SQL Query
// var sqlQuery = @"
//DELETE FROM contactsLists
// WHERE OwnerId = @OwnerId
// AND UserId = @UserId
//";
// #endregion
// return CDbQueryExecutor.DeleteItemParametrized(sqlQuery,
// SSqlParameterCreator.Create(
// "@OwnerId", ownerId, System.Data.SqlDbType.UniqueIdentifier, false
// ),
// SSqlParameterCreator.Create(
// "@UserId", userId, System.Data.SqlDbType.UniqueIdentifier, false
// )
// );
// }
// public static IList<CContactInfo> GetHasDialogContacts(Guid ownerId)
// {
// #region SQL Query
// var sql = @"
//SELECT
// [Id] = c.Id,
// [OwnerId] = c.OwnerId,
// [UserId] = c.UserId,
// [IsBlocked] = c.IsBlocked
//FROM (
// SELECT DISTINCT
// p.ChatId
// FROM chatsParticipants p
// INNER JOIN chatsParticipants p2
// ON p.ChatId = p2.ChatId
// WHERE p.UserId = @OwnerId
// GROUP BY p.ChatId
// HAVING COUNT(p.ChatId) = 2
// ) as res
//INNER JOIN chatsParticipants p
//ON res.ChatId = p.ChatId
//INNER JOIN contactsLists c
//ON p.UserId = c.UserId
// WHERE p.UserId != @OwnerId
// AND c.OwnerId = @OwnerId
//";
// #endregion
// return CDbQueryExecutor.GetDataParametrized(new CContactInfoMapper(), sql,
// SSqlParameterCreator.Create(
// "@OwnerId", ownerId, System.Data.SqlDbType.UniqueIdentifier, false
// )
// );
// }
// #endregion
public IList <CContactInfo> GetAllContactsByOwnerId(Guid ownerId)
{
s_log.LogInfo($@"Data provider's method '{nameof(GetAllContactsByOwnerId)}({ownerId})' is called");
#region SQL Query
var sql = @"
SELECT
[Id] = c.Id,
[OwnerId] = c.OwnerId,
[UserId] = c.UserId,
[IsBlocked] = c.IsBlocked
FROM contactsLists AS c
WHERE c.OwnerId = @OwnerId
";
#endregion
using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString))
{
using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection))
{
try
{
var result = executor.GetData(new CContactInfoMapper(), sql,
SSqlParameterCreator.Create(
"@OwnerId", ownerId, System.Data.SqlDbType.UniqueIdentifier, false
)
);
executor.Commit();
return(result);
}
catch (SqlException e)
{
s_log.LogError($@"{nameof(GetAllContactsByOwnerId)}({ownerId}): Error occured during SQL query execution", e);
s_log.LogInfo($@"{nameof(GetAllContactsByOwnerId)}({ownerId}): Operation was rolled back because of error");
Console.WriteLine($@"{nameof(GetAllContactsByOwnerId)}({ownerId}): Error occured during SQL query execution");
Console.WriteLine("Operation was rolled back because of error");
return(null);
}
}
}
}
public IList <CUserInfo> GetAllNotOfflineUsers()
{
s_log.LogInfo($@"Data provider's method '{nameof(GetAllNotOfflineUsers)}()' is called");
#region Sql
var sql = @"
SELECT
[Id] = Id,
[Login] = Login,
[Password] = Password,
[LastActiveDate] = LastActiveDate,
[ActivityStatus] = ActivityStatus,
[Avatar] = Avatar
FROM users
WHERE ActivityStatus != 0
";
#endregion
using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString))
{
using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection))
{
try
{
var result = executor.GetData(new CUserInfoMapper(), sql);
executor.Commit();
return(result);
}
catch (SqlException e)
{
s_log.LogError($@"{nameof(GetAllNotOfflineUsers)}(): Error occured during SQL query execution", e);
s_log.LogInfo($@"{nameof(GetAllNotOfflineUsers)}(): Operation was rolled back because of error");
Console.WriteLine($@"{nameof(GetAllNotOfflineUsers)}(): Error occured during SQL query execution");
Console.WriteLine("Operation was rolled back because of error");
return(null);
}
}
}
}
public IList <CChatInfo> GetChatsByOwnerId(Guid userId)
{
s_log.LogInfo($@"Data provider's method '{nameof(GetChatsByOwnerId)}({userId})' is called");
#region SQL Query
var sqlQuery = @"
SELECT
[Id] = Id,
[Title] = Title,
[OwnerId] = OwnerId,
[IsPersonal] = IsPersonal,
[Type] = Type
FROM chats AS c
WHERE c.OwnerId = @OwnerId
";
#endregion
using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString))
{
using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection))
{
try
{
var result = executor.GetData(new CChatInfoMapper(), sqlQuery);
executor.Commit();
return(result);
}
catch (SqlException e)
{
s_log.LogError($@"{nameof(GetChatsByOwnerId)}({userId}): Error occured during SQL query execution", e);
s_log.LogInfo($@"{nameof(GetChatsByOwnerId)}({userId}): Operation was rolled back because of error");
Console.WriteLine($@"{nameof(GetChatsByOwnerId)}({userId}): Error occured during SQL query execution");
Console.WriteLine("Operation was rolled back because of error");
return(null);
}
}
}
}
public IList <CMessageInfo> GetNewMessagesFromChat(Guid userId, Guid chatId, DateTimeOffset lastRequestDate, Int32 limit, Int32 offset, Int64 usn)
{
s_log.LogInfo($@"Data provider's method '{nameof(GetNewMessagesFromChat)}({chatId}, {lastRequestDate}, {limit}, {offset})' is called");
//TODO Надо добавить информацию об авторе сообщения
#region Sql
var sql = @"
SELECT
[Id] = res.Id,
[DispatchDate] = res.DispatchDate,
[MessageText] = res.MessageText,
[Type] = res.Type,
[ContentUri] = res.ContentUri,
[FromUserId] = res.FromUserId,
[IsRead] = res.IsRead,
[Login] = res.Login,
[USN] = res.USN
FROM
(
SELECT DISTINCT
m.Id,
m.DispatchDate,
m.MessageText,
m.Type,
m.ContentUri,
c.FromUserId,
c.IsRead,
u.Login,
m.USN
FROM messagesInChats c
INNER JOIN messages m
ON c.MessageId = m.Id
INNER JOIN users u
ON c.FromUserId = u.Id
WHERE c.ChatId = @ChatId
AND c.ToUserId = @UserId
AND c.FromUserId != @UserId
AND m.DispatchDate > @LastRequestDate
AND m.USN > @USN
ORDER BY m.DispatchDate DESC
OFFSET @Offset ROWS
FETCH NEXT @Limit ROWS ONLY
) res
ORDER BY res.DispatchDate
";
#endregion
using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString))
{
using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection))
{
try
{
var result = executor.GetData(new CMessageInfoMapper(), sql,
SSqlParameterCreator.Create("@UserId", userId, SqlDbType.UniqueIdentifier, false),
SSqlParameterCreator.Create("@ChatId", chatId, SqlDbType.UniqueIdentifier, false),
SSqlParameterCreator.Create("@LastRequestDate", lastRequestDate, SqlDbType.DateTimeOffset, false),
SSqlParameterCreator.Create("@Offset", offset, SqlDbType.Int, false),
SSqlParameterCreator.Create("@Limit", limit, SqlDbType.Int, false),
SSqlParameterCreator.Create("@USN", usn, SqlDbType.BigInt, false)
);
executor.Commit();
return(result);
}
catch (SqlException e)
{
s_log.LogError($@"{nameof(GetNewMessagesFromChat)}({chatId}, {lastRequestDate}, {limit}, {offset}): Error occured during SQL query execution", e);
s_log.LogInfo($@"{nameof(GetNewMessagesFromChat)}({chatId}, {lastRequestDate}, {limit}, {offset}): Operation was rolled back because of error");
Console.WriteLine($@"{nameof(GetNewMessagesFromChat)}({chatId}, {lastRequestDate}, {limit}, {offset}): Error occured during SQL query execution");
Console.WriteLine("Operation was rolled back because of error");
return(null);
}
}
}
}
public IList <CUserInfo> SearchContacts(Guid ownerId, String q)
{
s_log.LogInfo($@"Data provider's method '{nameof(SearchContacts)}({ownerId}, {q})' is called");
#region SQL Query
// var sql = @"
//SELECT DISTINCT
// [Id] = c.Id,
// [OwnerId] = c.OwnerId,
// [UserId] = c.UserId,
// [IsBlocked] = c.IsBlocked
//FROM contactsLists c
//INNER JOIN users u
//ON c.UserId = u.Id
// WHERE c.OwnerId != @OwnerId
// AND CONTAINS (u.Login, @SearchQuery)
//";
var sql = @"
SELECT
[Id] = u.Id,
[Login] = u.Login,
[Password] = u.Password,
[LastActiveDate] = u.LastActiveDate,
[ActivityStatus] = u.ActivityStatus,
[Avatar] = u.Avatar
FROM users u
WHERE NOT EXISTS (
SELECT *
FROM contactsLists c
WHERE c.OwnerId = @OwnerId
AND u.Id = c.UserId
OR u.Id = @OwnerId
)
AND u.Login LIKE @SearchQuery
";
#endregion
using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString))
{
using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection))
{
try
{
var result = executor.GetData(new CUserInfoMapper(), sql,
SSqlParameterCreator.Create(
"@OwnerId", ownerId, System.Data.SqlDbType.UniqueIdentifier, false
),
SSqlParameterCreator.Create(
"@SearchQuery", "%" + q + "%", System.Data.SqlDbType.NVarChar, false
)
);
executor.Commit();
return(result);
}
catch (SqlException e)
{
s_log.LogError($@"{nameof(SearchContacts)}({ownerId}, {q}): Error occured during SQL query execution", e);
s_log.LogInfo($@"{nameof(SearchContacts)}({ownerId}, {q}): Operation was rolled back because of error");
Console.WriteLine($@"{nameof(SearchContacts)}({ownerId}, {q}): Error occured during SQL query execution");
Console.WriteLine("Operation was rolled back because of error");
return(null);
}
}
}
}