public IEnumerable <channel_connection> GetConnectionsWhereUserNotCreator(IDbConnection connection, int userId, byte channelTypeId)
{
ThrowIfConnectionIsNull(connection);
var channelTableName = _provider.GetTableName(nameof(channel));
var ch = ChannelExtensions.SqlAliaceChannel;
var chCon = ChannelExtensions.SqlAliaceChannelConnection;
var sql = $"SELECT {ChannelExtensions.SqlSelectFieldsChannelAndChannelConnection} " +
$"FROM {SchemeTableName} as {chCon} " +
$"JOIN {channelTableName} as {ch} ON {ch}.Id={chCon}.channelId AND {ch}.creatorId!={userId} " +
$"WHERE {chCon}.userId={userId} AND {chCon}.channelType={channelTypeId}";
var converted = ChannelExtensions.ConvertRowsChannelWhithConnectedChannel(_provider.Text <dynamic>(connection, sql));
if (!converted.Any())
{
return(null);
}
var combinded = ChannelExtensions.CombineChannelConnections(converted);
if (!combinded.Any())
{
return(null);
}
if (combinded.Count == 1)
{
var channel = combinded[0];
return(channel.HasConnections() ? channel.GetConnections() : null);
}
else
{
throw new NotImplementedException("combinded.Count !=1");
}
}
public channel GetChannelWithConnectedUsers(IDbConnection connection, int channelId, List <int> conectedUserIds)
{
var channelConnectedTableName = _provider.GetTableName(nameof(channel_connection));
var ch = ChannelExtensions.SqlAliaceChannel;
var chCon = ChannelExtensions.SqlAliaceChannelConnection;
var sql =
$"SELECT {ChannelExtensions.SqlSelectFieldsChannelAndChannelConnection} FROM {SchemeTableName} as {ch} " +
$"JOIN {channelConnectedTableName} AS {chCon} ON {chCon}.channelId={ch}.Id " +
$"WHERE {ch}.Id={channelId} ";
var whereConected = "";
if (conectedUserIds.Any())
{
whereConected = $" AND {chCon}.userId IN(" + conectedUserIds.Aggregate(whereConected, (current, uId) => current + (uId + ","));
whereConected = whereConected.RemoveLastSimbol() + ") ";
sql += whereConected;
}
var data = (IList <dynamic>)_provider.Text <dynamic>(connection, sql);
if (!data.Any())
{
return(null);
}
var converted = ChannelExtensions.ConvertRowsChannelWhithConnectedChannel(_provider.Text <dynamic>(connection, sql));
if (!converted.Any())
{
return(null);
}
var combinded = ChannelExtensions.CombineChannelConnections(converted);
if (!combinded.Any())
{
return(null);
}
if (combinded.Count == 1)
{
var channel = combinded[0];
return(channel);
}
else
{
throw new NotImplementedException("combinded.Count !=1");
}
}
public channel_connection GetCreatorConnectionWhithChannel(IDbConnection connection, int channelId, int creatorUserId)
{
ThrowIfConnectionIsNull(connection);
var chTableName = _provider.GetTableName(nameof(channel));
var ch = ChannelExtensions.SqlAliaceChannel;
var chCon = ChannelExtensions.SqlAliaceChannelConnection;
var sql = $"SELECT {ChannelExtensions.SqlSelectFieldsChannelAndChannelConnection} " +
$"FROM {chTableName} as {ch} WHERE {ch}.Id={channelId} and {ch}.creatorId={creatorUserId} " +
$"JOIN {SchemeTableName} as {chCon} ON {chCon}.channelId={ch}.Id" +
$"WHERE userId={creatorUserId}";
var data = _provider.Text <dynamic>(connection, sql).FirstOrDefault();
if (data == null || !data.Any())
{
return(null);
}
var chConnection = (channel)ChannelExtensions.ConvertRowChannelWhithConnectedChannel(data);
return(chConnection.GetConnections().First());
}
/// <summary>
/// в данных содерждится информация о канале
/// </summary>
/// <param name="connection"></param>
/// <param name="firstUserId"></param>
/// <param name="secodUserId"></param>
/// <returns></returns>
public IList <channel_connection> GetPrivateChannelConnections(IDbConnection connection, int firstUserId, int secodUserId)
{
ThrowIfConnectionIsNull(connection);
const byte ptivateType = (byte)ChannelTypes.Private;
var channelTableName = _provider.GetTableName(nameof(channel));
//todo что то тут не так
//var connections = c.channel_connection
// .Where(i => i.channelType == ptivateType
// && ((i.userId == firstUser && i.channel.creatorId == firstUser)
// || (i.userId == firstUser && i.channel.creatorId == secodUser)
// || (i.userId == secodUser && i.channel.creatorId == secodUser)
// || (i.userId == secodUser && i.channel.creatorId == firstUser)
// )).ToList();
var ch = ChannelExtensions.SqlAliaceChannel;
var chCon = ChannelExtensions.SqlAliaceChannelConnection;
var sql = $"SELECT {ChannelExtensions.SqlSelectFieldsChannelAndChannelConnection} " +
$"FROM {channelTableName} as {ch} " +
$"JOIN {SchemeTableName} as {chCon} ON {chCon}.channelId={ch}.Id " +
$"WHERE {ch}.channelType={ptivateType} " +
$"AND ({ch}.creatorId={firstUserId} OR {ch}.creatorId={secodUserId}) ";
var responce = _table <dynamic>(connection, sql).ToList();
var result = (IList <channel_connection>)null;
if (responce.Any())
{
result = new List <channel_connection>();
foreach (var item in responce)
{
var channel = (channel)ChannelExtensions.ConvertRowChannelWhithConnectedChannel(item);
var conn = channel.GetConnections().First();
result.Add(conn);
}
// result = tmpList.Select(i => i.GetConnections().First()).ToList();
}
return(result);
}