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<bool> FindById() { bool success = false; Exception err = null; string sql = "SELECT name, description, status, created_at, created_by, category, category_id FROM dbo.ItemLists 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 = (ItemStatus)dt.Rows[0]["status"]; this.created_at = (DateTime)dt.Rows[0]["created_at"]; this.created_by = (Guid)dt.Rows[0]["created_by"]; this.category = dt.Rows[0]["category"].ToString(); this.category_id = (Guid)dt.Rows[0]["category_id"]; User aUser = new User() { id = this.created_by }; await aUser.FindByID(); this.creator = aUser; success = true; } } catch (Exception e) { err = e; } finally { db.Close(); } if (err != null) { throw err; } return success; }
public async Task<List<ItemListResponseModel>> ListByGroupId(Guid group_id) { List<ItemListResponseModel> items = new List<ItemListResponseModel>(); Exception err = null; string sql = "SELECT id, name, description, status, created_at, created_by, category, category_id FROM dbo.ItemLists WITH (NOLOCK) WHERE group_id = @group_id"; if (db.State != ConnectionState.Open) await db.OpenAsync(); try { DataTable dt = new DataTable(); SqlCommand cmd = new SqlCommand(sql, db); cmd.Parameters.AddWithValue("@group_id", group_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) { ItemList item = new ItemList(); item.id = (Guid)dr["id"]; item.name = dr["name"].ToString(); item.description = dr["description"].ToString(); item.status = (ItemStatus)dr["status"]; item.created_at = (DateTime)dr["created_at"]; item.created_by = (Guid)dr["created_by"]; item.category = dr["category"].ToString(); item.category_id = (Guid)dr["category_id"]; User aUser = new User() { id = item.created_by }; await aUser.FindByID(); item.creator = aUser; items.Add(item.Return); } } } catch (Exception e) { err = e; } finally { db.Close(); } if (err != null) { throw err; } return items; }
public async Task<bool> FindByDeviceId() { bool success = false; Exception err = null; string sql = "SELECT devices.id as device_id, devices.uuid, devices.os, devices.model, devices.installed_at, users.id as user_id, users.facebook_id, users.gender, users.email, users.name, users.first_name, users.last_name FROM dbo.DeviceOwners WITH (NOLOCK), dbo.Devices WITH (NOLOCK), dbo.Users WITH (NOLOCK) WHERE deviceowners.device_id = devices.id AND deviceowners.user_id = users.id AND deviceowners.device_id = @device_id ORDER BY linked_at DESC"; if (db.State != ConnectionState.Open) await db.OpenAsync(); try { DataTable dt = new DataTable(); SqlCommand cmd = new SqlCommand(sql, db); cmd.Parameters.AddWithValue("@device_id", this.device.id); SqlDataAdapter adp = new SqlDataAdapter(); adp.SelectCommand = cmd; adp.Fill(dt); if (dt != null && !dt.HasErrors && dt.Rows.Count > 0) { User newUser = new User(); newUser.id = (Guid)dt.Rows[0]["user_id"]; newUser.facebook_id = dt.Rows[0]["facebook_id"].ToString(); newUser.gender = dt.Rows[0]["gender"].ToString(); newUser.email = dt.Rows[0]["email"].ToString(); newUser.name = dt.Rows[0]["name"].ToString(); newUser.first_name = dt.Rows[0]["first_name"].ToString(); newUser.last_name = dt.Rows[0]["last_name"].ToString(); Device newDevice = new Device(); newDevice.id = (Guid)dt.Rows[0]["device_id"]; newDevice.uuid = dt.Rows[0]["uuid"].ToString(); newDevice.os = dt.Rows[0]["os"].ToString(); newDevice.model = dt.Rows[0]["model"].ToString(); newDevice.installed_at = (DateTime)dt.Rows[0]["installed_at"]; this.device = newDevice; this.user = newUser; success = true; } } catch (Exception e) { err = e; } finally { db.Close(); } if (err != null) { throw err; } return success; }