public async Task <EquipmentPaging> GetAll(int skip, int limit) { using (var conn = new SqlConnection(AppSettings.ConnectionString)) { var sql = Sql.SqlQueryCach["Equipment.All"]; var result = (await conn.QueryAsync <EquipmentWitcAct, EquipmentCategory, EquipmentWitcAct>( sql, (equipment, category) => { equipment.Category = category; return(equipment); }, new { skip = skip, limit = limit })).ToList(); var sqlc = Sql.SqlQueryCach["Equipment.CountAll"]; var count = (await conn.QueryAsync <int>(sqlc)).FirstOrDefault(); var sqlRAct = new ActCategoriesRepository(); foreach (var item in result) { var act = await sqlRAct.GetByEquipmentId(item.Id); if (act.Count > 0 && act.FirstOrDefault().Id != 0) { item.EquipmentActCategoryDescription = act.FirstOrDefault().Description; item.EquipmentActCategoryName = act.FirstOrDefault().Name; item.EquipmentActCategoryId = act.FirstOrDefault().Id; } } var output = new EquipmentPaging { Data = result.ToArray(), Total = count }; return(output); } }
public async Task <EquipmentPaging> GetByCategory(EquipmentCategory cat, int skip = 0, int limit = Int32.MaxValue, string filter = null) { using (var conn = new SqlConnection(AppSettings.ConnectionString)) { var sql = Sql.SqlQueryCach["Equipment.ByCategoryId"]; var result = await conn.QueryAsync <EquipmentWitcAct, EquipmentCategory, EquipmentWitcAct>( sql, (equipment, category) => { equipment.Category = category; return(equipment); }, new { category_id = cat.Id, skip = skip, limit = limit }); var sqlc = Sql.SqlQueryCach["Equipment.CountByCategoryId"]; var count = conn.ExecuteScalar <int>(sqlc, new { category_id = cat.Id }); TaskCommon.FilterBody[] filters; var filterOutput = new List <EquipmentWitcAct>(); if (filter != null) { filters = JsonConvert.DeserializeObject <TaskCommon.FilterBody[]>(filter); if (filters.Length > 0) { foreach (var value in result) { foreach (var item in filters) { switch (item.Filter) { case "Name": if (value.Name.ToLower().Contains(item.Value.ToLower())) { filterOutput.Add(value); } break; } } } } } if (filter != null) { result = filterOutput; count = filterOutput.Count; } //var newFuckingResult = new List<EquipmentWitcAct>(); var sqlRAct = new ActCategoriesRepository(); foreach (var item in result.ToArray()) { var actCategory = await sqlRAct.GetByEquipmentId(item.Id); if (actCategory.ToArray().Length > 0) { item.EquipmentActCategoryDescription = actCategory.FirstOrDefault().Description; item.EquipmentActCategoryName = actCategory.FirstOrDefault().Name; item.EquipmentActCategoryId = actCategory.FirstOrDefault().Id; } } var output = new EquipmentPaging { Data = result.ToArray(), Total = count }; return(output); } }