public async Task<All_UserModel> ListAll(Guid userid)
{
All_UserModel userModel = new All_UserModel();
//get user
User user = new User() { id = userid };
if (await user.FindByID())
{
userModel.id = user.id;
userModel.facebook_id = user.facebook_id;
userModel.gender = user.gender;
userModel.email = user.email;
userModel.name = user.name;
userModel.first_name = user.first_name;
userModel.last_name = user.last_name;
}
//get group
GroupList group = new GroupList();
List<GroupListResponseModel> groups = await group.ListByUserId(user.id);
userModel.groups = new List<All_GroupModel>();
foreach (GroupListResponseModel g in groups)
{
All_GroupModel groupModel = new All_GroupModel();
groupModel.id = g.id;
groupModel.name = g.name;
groupModel.description = g.description;
groupModel.status = g.status;
groupModel.created_at = g.created_at;
//get group members
Membership member = new Membership();
groupModel.members = await member.ListGroupMember(g.id);
//get items in each group
ItemList item = new ItemList();
List<ItemListResponseModel> items = await item.ListByGroupId(g.id);
groupModel.items = new List<All_ItemModel>();
foreach (ItemListResponseModel i in items)
{
All_ItemModel itemModel = new All_ItemModel();
itemModel.id = i.id;
itemModel.name = i.name;
itemModel.description = i.description;
itemModel.status = i.status;
itemModel.created_at = i.created_at;
itemModel.category = i.category;
itemModel.category_id = i.category_id;
itemModel.created_by = i.created_by;
groupModel.items.Add(itemModel);
}
userModel.groups.Add(groupModel);
}
return userModel;
}
public async Task<List<GroupListResponseModel>> ListByUserId(Guid user_id)
{
List<GroupListResponseModel> groups = new List<GroupListResponseModel>();
Exception err = null;
string sql = "SELECT grouplists.id, grouplists.name, grouplists.description, grouplists.status, grouplists.created_at FROM dbo.GroupLists WITH (NOLOCK), dbo.Memberships WITH (NOLOCK) WHERE grouplists.id = memberships.group_id AND memberships.user_id = @user_id AND grouplists.status = 1 AND memberships.status != 0";
if (db.State != ConnectionState.Open)
await db.OpenAsync();
try
{
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand(sql, db);
cmd.Parameters.AddWithValue("@user_id", user_id);
SqlDataAdapter adp = new SqlDataAdapter();
adp.SelectCommand = cmd;
adp.Fill(dt);
if (dt != null && !dt.HasErrors && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
GroupList group = new GroupList();
group.id = (Guid)dr["id"];
group.name = dr["name"].ToString();
group.description = dr["description"].ToString();
group.status = (GroupStatus)dr["status"];
group.created_at = (DateTime)dr["created_at"];
Membership member = new Membership();
group.members = await member.ListGroupMember(group.id);
groups.Add(group.Return);
}
}
}
catch (Exception e)
{
err = e;
}
finally
{
db.Close();
}
if (err != null)
{
throw err;
}
return groups;
}
public async Task<bool> FindById()
{
bool success = false;
Exception err = null;
string sql = "SELECT name, description, status, created_at FROM dbo.GroupLists WITH (NOLOCK) WHERE id LIKE @id";
if (db.State != ConnectionState.Open)
await db.OpenAsync();
try
{
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand(sql, db);
cmd.Parameters.AddWithValue("@id", id);
SqlDataAdapter adp = new SqlDataAdapter();
adp.SelectCommand = cmd;
adp.Fill(dt);
if (dt != null && !dt.HasErrors && dt.Rows.Count > 0)
{
this.name = dt.Rows[0]["name"].ToString();
this.description = dt.Rows[0]["description"].ToString();
this.status = (GroupStatus)dt.Rows[0]["status"];
this.created_at = (DateTime)dt.Rows[0]["created_at"];
Membership member = new Membership();
this.members = await member.ListGroupMember(this.id);
success = true;
}
}
catch (Exception e)
{
err = e;
}
finally
{
db.Close();
}
if (err != null)
{
throw err;
}
return success;
}