public async static Task<long> Insert(Group group)
{
string commandText = "Insert into Groups (Id, ParentId, Name, Description) values (@id, @parentId, @name, @description)";
Dictionary<string, object> parameters = new Dictionary<string, object>();
if (group.Id > 0)
{
parameters.Add("@id", group.Id);
} else
{
parameters.Add("@id", null);
}
parameters.Add("@parentId", @group.ParentId > 0 ? @group.ParentId : null);
parameters.Add("@name", group.Name);
parameters.Add("@description", group.Description);
long results;
using (var conn = await DataAccessBase.GetOpenAsync(DataAccessBase.QuangAuthConn))
{
var id = await conn.ExecuteAsync(commandText, parameters);
results = id;
}
return results;
}
public async static Task<long> Update(Group group)
{
string commandText = "Update Groups set ParentId = @parentId, Name = @name, Description = @description where Id = @id";
var parameters = new Dictionary<string, object>();
if (group.ParentId > 0)
{
parameters.Add("@parentId", group.ParentId.Value);
} else
{
parameters.Add("@parentId", null);
}
parameters.Add("@id", group.Id);
parameters.Add("@name", group.Name);
parameters.Add("@description", group.Description);
long results;
using (var conn = await DataAccessBase.GetOpenAsync(DataAccessBase.QuangAuthConn))
{
var id = await conn.ExecuteAsync(commandText, parameters);
results = id;
}
return results;
}
public int Update(Group group)
{
const string commandText = "Update Groups set ParentId = @parentId, Name = @name, Description = @description where Id = @id";
var parameters = new Dictionary<string, object>();
var parentId = @group.ParentId;
if ((parentId.GetValueOrDefault() <= 0 ? 0 : (parentId.HasValue ? 1 : 0)) != 0)
parameters.Add("@parentId", @group.ParentId.Value);
else
parameters.Add("@parentId", null);
parameters.Add("@id", @group.Id);
parameters.Add("@name", @group.Name);
parameters.Add("@description", @group.Description);
return _database.Execute(commandText, parameters);
}
public int Insert(Group group)
{
const string commandText = "Insert into Groups (Id, ParentId, Name, Description) values (@id, @parentId, @name, @description)";
var parameters = new Dictionary<string, object>();
if (group.Id > 0)
parameters.Add("@id", @group.Id);
else
parameters.Add("@id", null);
int? parentId = @group.ParentId;
parameters.Add("@parentId",
(parentId.GetValueOrDefault() <= 0 ? 0 : (parentId.HasValue ? 1 : 0)) != 0 ? @group.ParentId : null);
parameters.Add("@name", @group.Name);
parameters.Add("@description", @group.Description);
return _database.Execute(commandText, parameters);
}
public IDictionary<int, Group> GetAllGroups()
{
var dictionary1 = new Dictionary<int, Group>();
foreach (var dictionary2 in _database.Query("" + "select g.Id, g.Name, g.Description, g.ParentId, g1.Name as ParentName " + ", sum(if(gu.UserId is not null, 1, 0)) as TotalMembers " + "from Groups g " + "left join GroupUsers gu on gu.GroupId = g.Id " + "left join Groups as g1 on g1.Id = g.ParentId " + "group by g.Id, g.Name, g.Description, g.ParentId, ParentName " + "order by g.ParentId, g.Name"))
{
var group = new Group();
group.Id = int.Parse(dictionary2["Id"]);
group.Name = dictionary2["Name"];
group.Description = dictionary2["Description"];
if (!string.IsNullOrEmpty(dictionary2["ParentId"]))
{
group.ParentId = int.Parse(dictionary2["ParentId"]);
group.ParentName = dictionary2["ParentName"];
}
if (!string.IsNullOrEmpty(dictionary2["TotalMembers"]))
group.TotalMembers = int.Parse(dictionary2["TotalMembers"]);
dictionary1.Add(@group.Id, group);
}
return dictionary1;
}
/// <summary>
///
/// </summary>
/// <param name="group"></param>
/// <returns></returns>
public async static Task<long> Update(Group group)
{
return await GroupDal.Update(group);
}
/// <summary>
///
/// </summary>
/// <param name="group"></param>
/// <returns></returns>
public async static Task<long> Insert(Group group)
{
return await GroupDal.Insert(group);
}
public async static Task<IDictionary<long, Group>> GetAllGroups()
{
IDictionary<long, Group> groups = new Dictionary<long, Group>();
string commandText = "";
commandText += "select g.Id, g.Name, g.Description, g.ParentId, g1.Name as ParentName ";
commandText += ", sum(if(gu.UserId is not null, 1, 0)) as TotalMembers ";
commandText += "from Groups g ";
commandText += "left join GroupUsers gu on gu.GroupId = g.Id ";
commandText += "left join Groups as g1 on g1.Id = g.ParentId ";
commandText += "group by g.Id, g.Name, g.Description, g.ParentId, ParentName ";
commandText += "order by g.ParentId, g.Name";
using (var conn = await DataAccessBase.GetOpenAsync(DataAccessBase.QuangAuthConn))
{
var rows = await conn.QueryAsync<Group>(commandText, new{ });
foreach (var row in rows)
{
var group = new Group();
group.Id = row.Id;
group.Name = row.Name;
group.Description = row.Description;
if (row.ParentId.HasValue)
{
group.ParentId = row.ParentId;
group.ParentName = row.ParentName;
}
if ((row.TotalMembers > 0))
{
group.TotalMembers = row.TotalMembers;
}
groups.Add(group.Id, group);
}
}
return groups;
}
public Group GetOneGroup(int groupId)
{
Group group = null;
List<Dictionary<string, string>> list = _database.Query("Select * from Groups where Id = @id", new Dictionary<string, object>()
{
{
"@id",
groupId
}
});
if (list != null && list.Count == 1)
{
Dictionary<string, string> dictionary = list[0];
group = new Group {Id = int.Parse(dictionary["Id"]), ParentId = new int?()};
if (!string.IsNullOrEmpty(dictionary["ParentId"]))
group.ParentId = int.Parse(dictionary["ParentId"]);
group.Name = string.IsNullOrEmpty(dictionary["Name"]) ? null : dictionary["Name"];
group.Description = string.IsNullOrEmpty(dictionary["Description"]) ? null : dictionary["Description"];
}
return group;
}
public IEnumerable<Group> GetPaging(int pageSize, int pageNumber, int? parentId, string keyword)
{
var parameters = new Dictionary<string, object>();
string str = "select g1.*,g2.Name as ParentName from Groups g1 left join Groups g2 on g2.Id = g1.ParentId where g1.Name LIKE @param";
parameters.Add("@param", "%" + Utils.EncodeForLike(keyword) + "%");
if (parentId.HasValue)
{
int? nullable = parentId;
if ((nullable.GetValueOrDefault() <= 0 ? 0 : (1)) != 0)
{
str += " and g1.ParentId = @param1";
parameters.Add("@param1", parentId.Value);
}
}
string commandText = str + " order by g1.Name limit @rowNumber, @pageSize";
parameters.Add("@rowNumber", pageSize * pageNumber);
parameters.Add("@pageSize", pageSize);
var list = new List<Group>();
foreach (Dictionary<string, string> dictionary in _database.Query(commandText, parameters))
{
var group = new Group();
group.Id = int.Parse(dictionary["Id"]);
group.Name = dictionary["Name"];
group.ParentName = dictionary["ParentName"];
group.Description = dictionary["Description"];
if (!string.IsNullOrEmpty(dictionary["ParentId"]))
group.ParentId = int.Parse(dictionary["ParentId"]);
list.Add(group);
}
return list;
}
public IEnumerable<Group> GetGroupsByUser(int userId)
{
var parameters = new Dictionary<string, object>();
const string commandText = "" + "select g.* " + "from GroupUsers gu " + "inner join Users u on u.Id = gu.UserId " + "inner join Groups g on g.Id = gu.GroupId " + "where u.Id = @userId";
parameters.Add("@userId", userId);
IList<Group> list = new List<Group>();
foreach (Dictionary<string, string> dictionary in _database.Query(commandText, parameters))
{
var group = new Group();
group.Id = int.Parse(dictionary["Id"]);
group.ParentId = new int?();
if (!string.IsNullOrEmpty(dictionary["ParentId"]))
group.ParentId = int.Parse(dictionary["ParentId"]);
group.Name = dictionary["Name"];
group.Description = dictionary["Description"];
list.Add(group);
}
return list;
}