public async Task <Result <List <UntaggedUPC> > > GetUntaggedUPCList(UPCSearchFilter upcSearch) { var query = new StringBuilder(); query.AppendFormat(Constants.UnTaggedUPCQuery); var whereQuery = CustomWhereQuery(query, upcSearch); var orderQuery = CustomSort(query, upcSearch); orderQuery.AppendFormat($" LIMIT {upcSearch.Rows} OFFSET {upcSearch.First} "); DataTable dt = new DataTable(); using (var command = _dbContext.Database.GetDbConnection().CreateCommand()) { command.CommandText = orderQuery.ToString(); _dbContext.Database.OpenConnection(); using (var result = await command.ExecuteReaderAsync()) dt.Load(result); } var unTaggedUPCGrp = dt.DataTableToUntaggedUPCGroup(); //if (unTaggedUPCGrp.Count < 0) return Result.Fail<List<UntaggedUPC>>(Constants.No_Records_Found); return(Result.Ok(unTaggedUPCGrp)); }
public async Task <Result <List <UntaggedUPCBusinessModal> > > GetUPCList(UPCSearchFilter searchFilter) { var untaggedGroup = await _untagggedUPCRepo.GetUntaggedUPCList(searchFilter); if (!untaggedGroup.IsSuccessed) { return(Result.Fail <List <UntaggedUPCBusinessModal> >(untaggedGroup.GetErrorString())); } return(Result.Ok(ObjectMapper.CreateMap(untaggedGroup.Value))); }
public async Task <Result <List <TaggedUPC> > > GetUPCList(UPCSearchFilter searchFilter) { var taggedGroup = await _taggedUPCRepo.GetTaggedUPCList(searchFilter); if (!taggedGroup.IsSuccessed) { return(Result.Fail <List <TaggedUPC> >(taggedGroup.GetErrorString())); } return(Result.Ok(ObjectMapper.CreateMap(taggedGroup.Value))); }
private StringBuilder CustomWhereQuery(StringBuilder query, UPCSearchFilter upcSearch) { bool whereAppended = false; if (!string.IsNullOrEmpty(upcSearch.UPCCode)) { return(query.AppendFormat($" WHERE s.upccode ='{upcSearch.UPCCode}'")); } if (upcSearch.Status != null && upcSearch.Status.Count > 0) { query.AppendFormat($" { Utilities.AppendWhereOrAnd(whereAppended) } s.statusid IN ({string.Join(",", upcSearch.Status.Select(s => s.ToString())) })"); whereAppended = true; } if (upcSearch.Type != null && upcSearch.Type.Count > 0) { query.AppendFormat($" { Utilities.AppendWhereOrAnd(whereAppended) } s.producttypeid IN ({string.Join(",", upcSearch.Type.Select(s => s.ToString())) })"); whereAppended = true; } if (upcSearch.ProductCategory != null && upcSearch.ProductCategory.Count > 0) { query.AppendFormat($" { Utilities.AppendWhereOrAnd(whereAppended)} s.productcategoryid IN ({string.Join(",", upcSearch.ProductCategory.Select(s => s.ToString())) })"); whereAppended = true; } if (upcSearch.ProductSubcategory != null && upcSearch.ProductSubcategory.Count > 0) { query.AppendFormat($" { Utilities.AppendWhereOrAnd(whereAppended) } s.productsubcategoryid IN ({string.Join(",", upcSearch.ProductSubcategory.Select(s => s.ToString())) })"); whereAppended = true; } if (!string.IsNullOrEmpty(upcSearch.ProductSizing)) { query.AppendFormat($" { Utilities.AppendWhereOrAnd(whereAppended) } s.productsizing ='{upcSearch.ProductSizing}'"); whereAppended = true; } if (!string.IsNullOrEmpty(upcSearch.Description)) { query.AppendFormat($" { Utilities.AppendWhereOrAnd(whereAppended) } s.description LIKE '%{upcSearch.Description}%'"); whereAppended = true; } if (upcSearch.RoleID == (int)Common.CommonUtilities.Role.User) { query.AppendFormat($" { Utilities.AppendWhereOrAnd(whereAppended) } s.itemassingedto={ upcSearch.UserID }"); } return(query); }
private StringBuilder CustomSort(StringBuilder query, UPCSearchFilter upcSearch) { var sortOrder = upcSearch.SortOrder == 1 ? "asc" : "desc"; if (!string.IsNullOrEmpty(upcSearch.SortField)) { query.AppendFormat(" ORDER BY"); switch (upcSearch.SortField) { case "descriptionID": query.AppendFormat($" s.descriptionid {sortOrder}"); break; case "description": query.AppendFormat($" s.description {sortOrder}"); break; case "upcCode": query.AppendFormat($" s.upcCode {sortOrder}"); break; case "productType": query.AppendFormat($" pt.producttype {sortOrder}"); break; case "productCategory": query.AppendFormat($" pc.category {sortOrder}"); break; case "productSubCategory": query.AppendFormat($" pst.subcategory {sortOrder}"); break; case "productSizing": query.AppendFormat($" s.productsizing {sortOrder}"); break; case "itemAssignedTo": query.AppendFormat($" u.name {sortOrder}"); break; default: query.AppendFormat($" s.descriptionID {sortOrder}"); break; } } return(query); }
public async Task <IActionResult> GetTaggedUPCList([FromBody] UPCSearchFilter filter) { var roleId = Convert.ToInt32(GetValueFromClaim(Constants.AuthConstants.UserRole)); if (roleId != (int)Role.Admin && roleId != (int)Role.User) { return(Unauthorized()); } var result = (await _taggedUPCService.GetUPCList(filter)); if (!result.IsSuccessed) { return(BadRequest(Constants.BadRequestErrorMessage)); } return(Ok(result.Value)); }
private StringBuilder CustomWhereQuery(StringBuilder query, UPCSearchFilter upcSearch) { bool whereAppended = false; if (!string.IsNullOrEmpty(upcSearch.UPCCode)) { return(query.AppendFormat($" WHERE s.upccode ='@upccode'")); } if (upcSearch.Type != null && upcSearch.Type.Count > 0) { query.AppendFormat($" { Utilities.AppendWhereOrAnd(whereAppended) } s.producttypeid IN ({string.Join(",", upcSearch.Type.Select(s => s.ToString())) })"); whereAppended = true; } if (upcSearch.ProductCategory != null && upcSearch.ProductCategory.Count > 0) { query.AppendFormat($" { Utilities.AppendWhereOrAnd(whereAppended)} s.productcategoryid IN ({string.Join(",", upcSearch.ProductCategory.Select(s => s.ToString())) })"); whereAppended = true; } if (upcSearch.ProductSubcategory != null && upcSearch.ProductSubcategory.Count > 0) { query.AppendFormat($" { Utilities.AppendWhereOrAnd(whereAppended) } s.productsubcategoryid IN ({string.Join(",", upcSearch.ProductSubcategory.Select(s => s.ToString())) })"); whereAppended = true; } if (!string.IsNullOrEmpty(upcSearch.ProductSizing)) { query.AppendFormat($" { Utilities.AppendWhereOrAnd(whereAppended) } s.productsizing ='@productsizing'"); whereAppended = true; } if (!string.IsNullOrEmpty(upcSearch.Description)) { query.AppendFormat($" { Utilities.AppendWhereOrAnd(whereAppended) } s.description LIKE '%@description%'"); whereAppended = true; } return(query); }
public async Task <Result <List <TaggedUPC> > > GetTaggedUPCList(UPCSearchFilter upcSearch) { var query = new StringBuilder(); query.AppendFormat(Constants.TaggedUPCQuery); var whereQuery = CustomWhereQuery(query, upcSearch); var orderQuery = CustomSort(query, upcSearch); orderQuery.AppendFormat($" LIMIT {upcSearch.Rows} OFFSET {upcSearch.First} "); DataTable dt = new DataTable(); using (var command = _dbContext.Database.GetDbConnection().CreateCommand()) { var upcCode = command.CreateParameter(); upcCode.ParameterName = "upccode"; upcCode.Value = upcSearch.UPCCode; var description = command.CreateParameter(); description.ParameterName = "description"; description.Value = upcSearch.Description; var productSizing = command.CreateParameter(); productSizing.ParameterName = "productsizing"; productSizing.Value = upcSearch.ProductSizing; command.CommandText = orderQuery.ToString(); _dbContext.Database.OpenConnection(); using (var result = await command.ExecuteReaderAsync()) dt.Load(result); } var taggedUPCGrp = dt.DataTableToTaggedUPCGroup(); //if (taggedUPCGrp.Count <= 0) return Result.Fail<List<TaggedUPC>>(Constants.No_Records_Found); return(Result.Ok(taggedUPCGrp)); }