public JObject GetProductList(QueryProduct query, int page, int limit)
{
string acc = "AC003";
ProductCtrl productCtrl = new ProductCtrl(acc);
var que = productCtrl.GetList().AsQueryable();
if (!string.IsNullOrWhiteSpace(query.ProID))
{
que = que.Where(x => x.SID.Contains(query.ProID));
}
if (!string.IsNullOrWhiteSpace(query.ProName))
{
que = que.Where(x => x.ProName.Contains(query.ProName));
}
if (!string.IsNullOrWhiteSpace(query.Models))
{
que = que.Where(x => x.Model.Contains(query.Models));
}
if (!string.IsNullOrWhiteSpace(query.Specs))
{
que = que.Where(x => x.Specs.Contains(query.Specs));
}
var list = que.Skip((page - 1) * limit).Take(limit).ToList();
JObject json = new JObject(
new JProperty("code", 0),
new JProperty("msg", ""),
new JProperty("count", que.ToList().Count),
new JProperty("data",
new JArray(
from r in list
select new JObject(
new JProperty("AssistCode", r.AssistCode),
new JProperty("BarCode", r.BarCode),
new JProperty("CreatDate", r.CreatDate),
new JProperty("Creator", r.Creator),
new JProperty("FactryName", r.FactryName),
new JProperty("ID", r.ID),
new JProperty("Model", r.Model),
new JProperty("PinyinCode", r.PinyinCode),
new JProperty("OnlyUnit", r.OnlyUnit),
new JProperty("ClassName", r.ClassName),
new JProperty("ClassID", r.ClassID),
new JProperty("MainUnitName", r.MainUnitName),
new JProperty("AssistUnitName", r.AssistUnitName),
new JProperty("AssistRate", r.AssistRate),
new JProperty("IsFloat", r.IsFloat),
new JProperty("ProName", r.ProName),
new JProperty("Remark", r.Remark),
new JProperty("SID", r.SID),
new JProperty("Specs", r.Specs),
new JProperty("Status", r.Status)))));
return(json);
}
public async Task <IActionResult> Query([FromQuery] QueryProduct query)
{
var result = await this.mediator.Send(query);
if (result.Errors?.Count > 0)
{
return(this.BadRequest(result));
}
return(this.Ok(result));
}
internal static string BuilderSql(CustomDataCommand command, QueryConditionEntity <QueryProduct> query)
{
CommonConst commonConst = new CommonConst();
string sql = command.CommandText;
bool hasWhere = false;
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(
sql, command, query.PagingInfo, "its.StockSysNo"))
{
QueryProduct condition = query.Condition;
if (!string.IsNullOrEmpty(condition.CompanyCode))
{
builder.AddCondition(QueryConditionRelationType.AND,
"ppm.CompanyCode",
DbType.String,
"@CompanyCode",
QueryConditionOperatorType.Equal,
condition.CompanyCode
);
hasWhere = true;
}
if (!string.IsNullOrEmpty(condition.PartnerType))
{
builder.AddCondition(QueryConditionRelationType.AND,
"ppm.PartnerType",
DbType.String,
"@PartnerType",
QueryConditionOperatorType.Equal,
condition.PartnerType
);
hasWhere = true;
}
if (condition.ProductID != null && condition.ProductID.Length > 0)
{
string productIds = Util.Contract("','", condition.ProductID);
productIds = "'" + productIds + "'";
builder.AddCondition(QueryConditionRelationType.AND,
"ppm.ProductID",
DbType.String,
"@ProductID",
QueryConditionOperatorType.In,
productIds
);
hasWhere = true;
}
if (condition.ProductSysNo != null && condition.ProductSysNo.Length > 0)
{
string productSysNos = Util.Contract("','", condition.ProductSysNo);
productSysNos = "'" + productSysNos + "'";
builder.AddCondition(QueryConditionRelationType.AND,
"ppm.ProductSysNo",
DbType.String,
"@ProductSysNo",
QueryConditionOperatorType.In,
productSysNos
);
hasWhere = true;
}
if (condition.SysProductID != null && condition.SysProductID.Length > 0)
{
string sysProductIds = Util.Contract("','", condition.SysProductID);
sysProductIds = "'" + sysProductIds + "'";
builder.AddCondition(QueryConditionRelationType.AND,
"ppm.SysProductID",
DbType.String,
"@SysProductID",
QueryConditionOperatorType.In,
sysProductIds
);
hasWhere = true;
}
if (condition.WareHourseNumber != null && condition.WareHourseNumber.Length > 0)
{
string wareHourseNumbers = Util.Contract(",", condition.WareHourseNumber);
builder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND,
"its.StockSysNo",
QueryConditionOperatorType.In,
wareHourseNumbers
);
hasWhere = true;
}
//状态筛选
builder.AddCondition(QueryConditionRelationType.AND,
"ppm.Status",
DbType.String,
"@Status",
QueryConditionOperatorType.Equal,
'A'
);
hasWhere = true;
sql = builder.BuildQuerySql();
//有库存变化的才进行数据捞取
string where = "(isnull(its.AvailableQty,0)+ isnull(its.ConsignQty,0)+isnull(its.VirtualQty,0)) <> isnull(ppsi.InventoryQty,0)";
sql = sql.Replace("@Where@", string.Format("{0}{1}", hasWhere ? " AND " : " ", where));
}
return(sql);
}
private QueryProduct GetQueryProduct()
{
QueryProduct query = Addapter.CalculateInventoryQty.CreateQueryProduct();
return(query);
}
