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