public CMessageInfo GetLastMessageFromChat(Guid chatId)
{
s_log.LogInfo($@"Data provider's method '{nameof(GetLastMessageFromChat)}({chatId})' is called");
#region Sql
var sql = @"
SELECT DISTINCT
[Id] = m.Id,
[DispatchDate] = m.DispatchDate,
[MessageText] = m.MessageText,
[Type] = m.Type,
[ContentUri] = m.ContentUri,
[FromUserId] = c.FromUserId,
[IsRead] = c.IsRead,
[Login] = u.Login,
[USN] = 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
ORDER BY m.DispatchDate DESC
";
#endregion
using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString))
{
using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection))
{
try
{
var result = executor.GetItem(new CMessageInfoMapper(), sql,
SSqlParameterCreator.Create("@ChatId", chatId, SqlDbType.UniqueIdentifier, false)
);
executor.Commit();
return(result);
}
catch (SqlException e)
{
s_log.LogError($@"{nameof(GetLastMessageFromChat)}({chatId}): Error occured during SQL query execution", e);
s_log.LogInfo($@"{nameof(GetLastMessageFromChat)}({chatId}): Operation was rolled back because of error");
Console.WriteLine($@"{nameof(GetLastMessageFromChat)}({chatId}): Error occured during SQL query execution");
Console.WriteLine("Operation was rolled back because of error");
return(null);
}
}
}
}
// public static CUserInfo GetUserByAuthData(String login, String password)
// {
// s_log.LogInfo($"{System.Reflection.MethodBase.GetCurrentMethod().ToString()}({login}, {password}) 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 users AS u
// WHERE u.Login = @Login
// AND u.Password = @Password
//";
// #endregion
// var result = CDbQueryExecutor.GetItemParametrized(new CUserInfoMapper(), sql,
// SSqlParameterCreator.Create(
// "@Login", login, System.Data.SqlDbType.NVarChar, false
// ),
// SSqlParameterCreator.Create(
// "@Password", password, System.Data.SqlDbType.NVarChar, false
// )
// );
// return result;
// }
// public static Int32 UpdateUserLastActiveDate(Guid userId, DateTimeOffset lastActiveDate)
// {
// var sql = @"
//UPDATE users
//SET LastActiveDate = (CASE
// WHEN LastActiveDate < @LastActiveDate
// THEN @LastActiveDate
// ELSE LastActiveDate
// END)
// WHERE Id = @UserId
//";
// return CDbQueryExecutor.UpdateItemParametrized(sql,
// SSqlParameterCreator.Create(
// "@Id", userId, System.Data.SqlDbType.UniqueIdentifier, false
// ),
// SSqlParameterCreator.Create(
// "@LastActiveDate", lastActiveDate, SqlDbType.DateTimeOffset, true
// )
// );
// }
// public static CUserInfo GetUserData(Guid id)
// {
// #region Sql
// var sql = @"
//SELECT
// [Id] = u.Id,
// [Login] = u.Login,
// [Password] = u.Password,
// [LastActiveDate] = u.LastActiveDate,
// [ActivityStatus] = u.ActivityStatus,
// [Avatar] = u.Avatar
//FROM users AS u
// WHERE u.Id = @Id
//";
// #endregion
// return CDbQueryExecutor.GetItemParametrized(new CUserInfoMapper(), sql,
// SSqlParameterCreator.Create(
// "@Id", id, System.Data.SqlDbType.UniqueIdentifier, false
// )
// );
// }
// public static Int32 UpdateUserStatus(Guid userId, Int32 currentStatus)
// {
// #region Sql
// var sql = @"
//UPDATE users
//SET ActivityStatus = @CurrentStatus
// WHERE Id = @UserId
//";
// #endregion
// return CDbQueryExecutor.UpdateItemParametrized(sql,
// SSqlParameterCreator.Create(
// "@Id", userId, System.Data.SqlDbType.UniqueIdentifier, false
// ),
// SSqlParameterCreator.Create(
// "@CurrentStatus", (Byte)currentStatus, SqlDbType.TinyInt, false
// )
// );
// }
// //TODO Куда этот метод убрать? Или оставить здесь?
// public static IList<CUserInfo> GetAllChatParticipantsByChatId(Guid chatId)
// {
// #region Sql
// var sql = @"
//SELECT
// [Id] = u.Id,
// [Login] = u.Login,
// [Password] = u.Password,
// [LastActiveDate] = u.LastActiveDate,
// [ActivityStatus] = u.ActivityStatus,
// [Avatar] = u.Avatar
//FROM chatsParticipants c
//INNER JOIN users u
//ON c.UserId = u.Id
// WHERE c.ChatId = @ChatId
//";
// #endregion
// return CDbQueryExecutor.GetDataParametrized(new CUserInfoMapper(), sql,
// SSqlParameterCreator.Create(
// "@ChatId", chatId, SqlDbType.UniqueIdentifier, false
// )
// );
// }
// public static Guid CreateUser(CUserInfo user)
// {
// #region SQL Query
// var sql = @"
//INSERT INTO users (Id, Login, Password, LastActiveDate, ActivityStatus, Avatar)
// VALUES (
//@Id, @Login, @Password, @LastActiveDate, @ActivityStatus, @Avatar
//)
//";
// #endregion
// var newId = Guid.NewGuid();
// try
// {
// var result = CDbQueryExecutor.CreateItemParametrized(sql,
// SSqlParameterCreator.Create(
// "@Id", newId, SqlDbType.UniqueIdentifier, false
// ),
// SSqlParameterCreator.Create(
// "@Login", user.Login, System.Data.SqlDbType.NVarChar, false
// ),
// SSqlParameterCreator.Create(
// "@Password", user.Password, System.Data.SqlDbType.NVarChar, false
// ),
// SSqlParameterCreator.Create(
// "@LastActiveDate", user.LastActiveDate, System.Data.SqlDbType.DateTimeOffset, true
// ),
// SSqlParameterCreator.Create(
// "@ActivityStatus", user.ActivityStatus, System.Data.SqlDbType.TinyInt, false
// ),
// SSqlParameterCreator.Create(
// "@Avatar", user.Avatar, System.Data.SqlDbType.NVarChar, true
// )
// );
// }
// catch (InvalidOperationException e)
// {
// Console.WriteLine(e);
// throw;
// }
// return newId;
// }
// public static Int32 DeleteUser(Guid id)
// {
// #region SQL Query
// var sql = @"
//DELETE FROM users
// WHERE users.Id = @Id
//";
// #endregion
// return CDbQueryExecutor.DeleteItemParametrized(sql,
// SSqlParameterCreator.Create(
// "@Id", id, System.Data.SqlDbType.UniqueIdentifier, false
// )
// );
// }
// public static IList<CUserInfo> GetAllNotOfflineUsers()
// {
// #region Sql
// var sql = @"
//SELECT
// [Id] = Id,
// [Login] = Login,
// [Password] = Password,
// [LastActiveDate] = LastActiveDate,
// [ActivityStatus] = ActivityStatus,
// [Avatar] = Avatar
//FROM users
// WHERE ActivityStatus != 0
//";
// #endregion
// return CDbQueryExecutor.GetDataParametrized(new CUserInfoMapper(), sql);
// }
// public static IList<CUserInfo> SearchContacts(Guid ownerId, String q)
// {
// #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
// var result = CDbQueryExecutor.GetDataParametrized(new CUserInfoMapper(), sql,
// SSqlParameterCreator.Create(
// "@OwnerId", ownerId, System.Data.SqlDbType.UniqueIdentifier, false
// ),
// SSqlParameterCreator.Create(
// "@SearchQuery", "%" + q + "%", System.Data.SqlDbType.NVarChar, false
// )
// );
// return result;
// }
#endregion
public CUserInfo GetUserByAuthData(String login, String password)
{
s_log.LogInfo($@"Data provider's method '{nameof(GetUserByAuthData)}({login}, {password})' 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 users AS u
WHERE u.Login = @Login
AND u.Password = @Password
";
#endregion
using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString))
{
using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection))
{
try
{
var result = executor.GetItem(new CUserInfoMapper(), sql,
SSqlParameterCreator.Create(
"@Login", login, System.Data.SqlDbType.NVarChar, false
),
SSqlParameterCreator.Create(
"@Password", password, System.Data.SqlDbType.NVarChar, false
)
);
executor.Commit();
return(result);
}
catch (SqlException e)
{
s_log.LogError($@"{nameof(GetUserByAuthData)}({login}, {password}): Error occured during SQL query execution", e);
s_log.LogInfo($@"{nameof(GetUserByAuthData)}({login}, {password}): Operation was rolled back because of error");
Console.WriteLine($@"{nameof(GetUserByAuthData)}({login}, {password}): Error occured during SQL query execution");
Console.WriteLine("Operation was rolled back because of error");
return(null);
}
}
}
}
public CChatInfo GetChatById(Guid chatId)
{
s_log.LogInfo($@"Data provider's method '{nameof(GetChatById)}({chatId})' is called");
#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
using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString))
{
using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection))
{
try
{
var result = executor.GetItem(new CChatInfoMapper(), sqlQuery);
executor.Commit();
return(result);
}
catch (SqlException e)
{
s_log.LogError($@"{nameof(GetChatById)}({chatId}): Error occured during SQL query execution", e);
s_log.LogInfo($@"{nameof(GetChatById)}({chatId}): Operation was rolled back because of error");
Console.WriteLine($@"{nameof(GetChatById)}({chatId}): Error occured during SQL query execution");
Console.WriteLine("Operation was rolled back because of error");
return(null);
}
}
}
}
public CChatInfo GetDialog(Guid userId, Guid participantId)
{
s_log.LogInfo($@"Data provider's method '{nameof(GetDialog)}({userId}, {participantId})' is called");
#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
using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString))
{
using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection))
{
try
{
var result = executor.GetItem(new CChatInfoMapper(), sqlQuery,
SSqlParameterCreator.Create(
"@UserId", userId, System.Data.SqlDbType.UniqueIdentifier, false
),
SSqlParameterCreator.Create(
"@ParticipantId", participantId, System.Data.SqlDbType.UniqueIdentifier, false
)
);
executor.Commit();
return(result);
}
catch (SqlException e)
{
s_log.LogError($@"{nameof(GetDialog)}({userId}, {participantId}): Error occured during SQL query execution", e);
s_log.LogInfo($@"{nameof(GetDialog)}({userId}, {participantId}): Operation was rolled back because of error");
Console.WriteLine($@"{nameof(GetDialog)}({userId}, {participantId}): Error occured during SQL query execution");
Console.WriteLine("Operation was rolled back because of error");
return(null);
}
}
}
}
