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