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;
}
