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