public PagedQueryResult <VisitingParameterOptionsViewEntity> GetOptionNameList(IWhereCondition[] pWhereConditions, string ClientID, string ClientDistributorID, OrderBy[] pOrderBys, int pageIndex, int pageSize) { if (ClientID == null || ClientID == "") { ClientID = CurrentUserInfo.ClientID; } if (ClientDistributorID == null || ClientDistributorID == "") { ClientDistributorID = CurrentUserInfo.ClientDistributorID; } StringBuilder sqlWhere = new StringBuilder(); if (pWhereConditions != null) { foreach (var item in pWhereConditions) { sqlWhere.AppendFormat(" and {0}", item.GetExpression()); } } StringBuilder sqlOrder = new StringBuilder(); if (pOrderBys != null && pOrderBys.Length > 0) { foreach (var item in pOrderBys) { sqlOrder.AppendFormat(" {0} {1},", item.FieldName, item.Direction == OrderByDirections.Asc ? "asc" : "desc"); } sqlOrder.Remove(sqlOrder.Length - 1, 1); } //options StringBuilder sql = new StringBuilder(); sql.Append( @"select OptionName,0 as IsDelete,max(clientid) as ClientID,max(CreateTime) as CreateTime,COUNT(*) as OptionCount from VisitingParameterOptions where isdelete=0 " + sqlWhere.ToString() + " and ClientID='" + ClientID + "'" + " and ClientDistributorID=" + ClientDistributorID + " group by optionName"); //通用分页查询 UtilityEntity model = new UtilityEntity(); model.TableName = "(" + sql.ToString() + ") as A"; model.PageIndex = pageIndex; model.PageSize = pageSize; model.PageWhere = sqlWhere.ToString(); model.PageSort = sqlOrder.ToString(); new UtilityDAO(this.CurrentUserInfo).PagedQuery(model); //返回值 PagedQueryResult <VisitingParameterOptionsViewEntity> pEntity = new PagedQueryResult <VisitingParameterOptionsViewEntity>(); pEntity.RowCount = model.PageTotal; if (model.PageDataSet != null && model.PageDataSet.Tables != null && model.PageDataSet.Tables.Count > 0) { pEntity.Entities = DataLoader.LoadFrom <VisitingParameterOptionsViewEntity>(model.PageDataSet.Tables[0]); } return(pEntity); }
/// <summary> /// 公用的GetList方法 /// </summary> /// <param name="pageIndex">当前页面</param> /// <param name="pageSize">页面显示数</param> /// <param name="strWhere">查询条件</param> /// <param name="strOrder">排序条件</param> /// <param name="tableName">表名或查询的sql语句</param> /// <param name="RowCount">out 返回的总数量</param> /// <returns>DataSet</returns> public DataSet GetList(int pageIndex, int pageSize, string strWhere, string strOrder, string tableName, out int RowCount) { UtilityEntity model = new UtilityEntity(); if (!string.IsNullOrEmpty(tableName) && tableName.Length < 30 && tableName.Trim().IndexOf(' ') < 0) { model.TableName = tableName; } else { model.TableName = "(" + tableName + ") as A"; } model.PageIndex = pageIndex; model.PageSize = pageSize; model.PageWhere = strWhere; model.PageSort = strOrder; new UtilityDAO(this.CurrentUserInfo).PagedQuery(model); RowCount = 0; DataSet ds = null; if (model != null) { RowCount = model.PageTotal; ds = model.PageDataSet; } return(ds); }
/// <summary> /// 根据SQl语句返回受影响行数 /// </summary> /// <param name="sql">SQL语句</param> /// <param name="Params">参数(数组)</param> /// <returns>int</returns> public int?NonQuery(string sql, IDbTransaction pTran) { UtilityEntity model = new UtilityEntity(); model.CustomSql = sql; new UtilityDAO(this.CurrentUserInfo).Query(model, null); return(model.OpResultID); }
/// <summary> /// 获取拜访步骤职位对象列表 /// </summary> /// <param name="stepid"> VisitingTaskStepID </param> /// <param name="pWhereConditions"></param> /// <param name="pOrderBys"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public PagedQueryResult <VisitingTaskStepObjectViewEntity> GetStepPositionList(Guid stepid, IWhereCondition[] pWhereConditions, OrderBy[] pOrderBys, int pageIndex, int pageSize) { StringBuilder sqlWhere = new StringBuilder(); if (pWhereConditions != null) { foreach (var item in pWhereConditions) { sqlWhere.AppendFormat(" and {0}", item.GetExpression()); } } StringBuilder sqlOrder = new StringBuilder(); if (pOrderBys != null && pOrderBys.Length > 0) { foreach (var item in pOrderBys) { sqlOrder.AppendFormat(" {0} {1},", item.FieldName, item.Direction == OrderByDirections.Asc ? "asc" : "desc"); } sqlOrder.Remove(sqlOrder.Length - 1, 1); } string sql = @"select role_id as ClientPositionID,role_code as PositionNo,role_name as PositionName,status,0 as IsDelete,B.ObjectID,B.VisitingTaskStepID,CAST(A.role_id as nvarchar(200)) as Target1ID,B.Target2ID from t_role A left join VisitingTaskStepObject B on A.role_id=B.Target1ID and B.IsDelete=0 and B.VisitingTaskStepID='{0}' and B.ClientID='{1}' and B.ClientDistributorID={2} "; //通用分页查询 UtilityEntity model = new UtilityEntity(); model.TableName = "(" + string.Format(sql, stepid, CurrentUserInfo.ClientID, CurrentUserInfo.ClientDistributorID) + ") as A"; model.PageIndex = pageIndex; model.PageSize = pageSize; model.PageSort = sqlOrder.ToString(); new UtilityDAO(this.CurrentUserInfo).PagedQuery(model); //返回值 PagedQueryResult <VisitingTaskStepObjectViewEntity> pEntity = new PagedQueryResult <VisitingTaskStepObjectViewEntity>(); pEntity.RowCount = model.PageTotal; if (model.PageDataSet != null && model.PageDataSet.Tables != null && model.PageDataSet.Tables.Count > 0) { pEntity.Entities = DataLoader.LoadFrom <VisitingTaskStepObjectViewEntity>(model.PageDataSet.Tables[0]); } return(pEntity); }
public PagedQueryResult <EnterpriseMemberStructureEntity> GetList(IWhereCondition[] pWhereConditions, OrderBy[] pOrderBys, int pageIndex, int pageSize) { StringBuilder sqlWhere = new StringBuilder(); if (pWhereConditions != null) { foreach (var item in pWhereConditions) { sqlWhere.AppendFormat(" and {0}", item.GetExpression()); } } StringBuilder sqlOrder = new StringBuilder(); if (pOrderBys != null && pOrderBys.Length > 0) { foreach (var item in pOrderBys) { sqlOrder.AppendFormat(" {0} {1},", item.FieldName, item.Direction == OrderByDirections.Asc ? "asc" : "desc"); } sqlOrder.Remove(sqlOrder.Length - 1, 1); } //通用分页查询 UtilityEntity model = new UtilityEntity(); //TODO:活动媒体,关联活动表 model.TableName = "(" + @"select a.*,b.MemberName as MemberTitle,c.StructureTitle as ParentTitle from dbo.EnterpriseMemberStructure a left join EnterpriseMember b on a.EnterpriseMemberID=b.EnterpriseMemberID left join EnterpriseMemberStructure c on a.parentID=c.EnterpriseMemberStructureID where a.IsDelete=0 and b.IsDelete=0 and c.IsDelete=0 " + sqlWhere + ") as A"; model.PageIndex = pageIndex; model.PageSize = pageSize; model.PageSort = sqlOrder.ToString(); new UtilityDAO(this.CurrentUserInfo).PagedQuery(model); //返回值 PagedQueryResult <EnterpriseMemberStructureEntity> pEntity = new PagedQueryResult <EnterpriseMemberStructureEntity>(); pEntity.RowCount = model.PageTotal; if (model.PageDataSet != null && model.PageDataSet.Tables.Count > 0) { pEntity.Entities = DataLoader.LoadFrom <EnterpriseMemberStructureEntity>(model.PageDataSet.Tables[0], new DirectPropertyNameMapping()); } return(pEntity); }
public PagedQueryResult <TPaymentTypeEntity> GetPaymentTypePage(IWhereCondition[] pWhereConditions, int pageIndex, int pageSize) { StringBuilder strbWhere = new StringBuilder(); if (pWhereConditions != null) { foreach (var item in pWhereConditions) { strbWhere.AppendFormat(" AND {0}", item.GetExpression()); } } UtilityEntity model = new UtilityEntity(); string sql = @"select Payment_Type_Id PaymentTypeID, Payment_Type_Code PaymentTypeCode, Payment_Type_Name PaymentTypeName,Status ,PaymentCompany,PaymentItemType,LogoURL,PaymentDesc,IsNativePay,t.CreateTime,t.LastUpdateTime ,t.CreateBy,t.LastUpdateBy,t.IsDelete ,(case when m.PayDeplyType=0 then 'true' else 'false' end) IsDefault ,(case when m.MappingId IS NULL then 'false' else 'true' end )IsOpen ,(case when m.PayDeplyType=1 then 'true' else 'false' end) IsCustom ,m.ChannelId from T_Payment_Type as t left join TPaymentTypeCustomerMapping as m on t.Payment_Type_Id=m.PaymentTypeID and m.CustomerId='" + this.CurrentUserInfo.ClientID + @"' and m.IsDelete='0' INNER JOIN dbo.SysASCAndPaymentTypeMapping sm ON sm.PaymentTypeId=t.Payment_Type_Id where t.IsDelete='0' AND sm.APPChannelID=2 "; model.TableName = "(" + sql + strbWhere.ToString() + ") as A"; model.PageIndex = pageIndex; model.PageSize = pageSize; model.PageSort = " CreateTime desc"; new UtilityDAO(this.CurrentUserInfo).PagedQuery(model); PagedQueryResult <TPaymentTypeEntity> pEntity = new PagedQueryResult <TPaymentTypeEntity>(); pEntity.RowCount = model.PageTotal; if (model.PageDataSet != null && model.PageDataSet.Tables != null && model.PageDataSet.Tables.Count > 0) { pEntity.Entities = DataLoader.LoadFrom <TPaymentTypeEntity>(model.PageDataSet.Tables[0]); } return(pEntity); }
/// <summary> /// GetList, VisitingTaskID必传 /// </summary> /// <param name="entity"></param> /// <param name="pWhereConditions"></param> /// <param name="pOrderBys"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public PagedQueryResult <VisitingTaskStepViewEntity> GetList(VisitingTaskStepViewEntity entity, IWhereCondition[] pWhereConditions, OrderBy[] pOrderBys, int pageIndex, int pageSize) { StringBuilder sqlWhere = new StringBuilder(); if (pWhereConditions != null) { foreach (var item in pWhereConditions) { sqlWhere.AppendFormat(" and {0}", item.GetExpression()); } } StringBuilder sqlOrder = new StringBuilder(); if (pOrderBys != null && pOrderBys.Length > 0) { foreach (var item in pOrderBys) { sqlOrder.AppendFormat(" {0} {1},", item.FieldName, item.Direction == OrderByDirections.Asc ? "asc" : "desc"); } sqlOrder.Remove(sqlOrder.Length - 1, 1); } //通用分页查询 UtilityEntity model = new UtilityEntity(); model.TableName = "(" + string.Format(SqlMap.SQL_GETSTEPLIST, entity.VisitingTaskID, CurrentUserInfo.ClientID, CurrentUserInfo.ClientDistributorID) + ") as A"; model.PageIndex = pageIndex; model.PageSize = pageSize; model.PageSort = sqlOrder.ToString(); new UtilityDAO(this.CurrentUserInfo).PagedQuery(model); //返回值 PagedQueryResult <VisitingTaskStepViewEntity> pEntity = new PagedQueryResult <VisitingTaskStepViewEntity>(); pEntity.RowCount = model.PageTotal; if (model.PageDataSet != null && model.PageDataSet.Tables != null && model.PageDataSet.Tables.Count > 0) { pEntity.Entities = DataLoader.LoadFrom <VisitingTaskStepViewEntity>(model.PageDataSet.Tables[0]); } return(pEntity); }
public PagedQueryResult <ActivityMediaEntity> GetList(IWhereCondition[] pWhereConditions, OrderBy[] pOrderBys, int pageIndex, int pageSize) { StringBuilder sqlWhere = new StringBuilder(); if (pWhereConditions != null) { foreach (var item in pWhereConditions) { sqlWhere.AppendFormat(" and {0}", item.GetExpression()); } } StringBuilder sqlOrder = new StringBuilder(); if (pOrderBys != null && pOrderBys.Length > 0) { foreach (var item in pOrderBys) { sqlOrder.AppendFormat(" {0} {1},", item.FieldName, item.Direction == OrderByDirections.Asc ? "asc" : "desc"); } sqlOrder.Remove(sqlOrder.Length - 1, 1); } //通用分页查询 UtilityEntity model = new UtilityEntity(); //TODO:活动媒体,关联活动表 model.TableName = "(" + "select a.*,b.FileName,b.Path,'测试活动' as ActivityTitle,case a.MediaType when 1 then '图片' when 2 then '视频' end as MediaTypeText from dbo.ActivityMedia a left join dbo.Attachment b on a.AttachmentID=b.AttachmentID WHERE a.IsDelete=0" + sqlWhere.ToString() + ") as A"; model.PageIndex = pageIndex; model.PageSize = pageSize; model.PageSort = sqlOrder.ToString(); new UtilityDAO(this.CurrentUserInfo).PagedQuery(model); //返回值 PagedQueryResult <ActivityMediaEntity> pEntity = new PagedQueryResult <ActivityMediaEntity>(); pEntity.RowCount = model.PageTotal; if (model.PageDataSet != null && model.PageDataSet.Tables != null && model.PageDataSet.Tables.Count > 0) { pEntity.Entities = DataLoader.LoadFrom <ActivityMediaEntity>(model.PageDataSet.Tables[0], new DirectPropertyNameMapping()); } return(pEntity); }
/// <summary> /// 获取信息 /// </summary> /// <param name="pWhereConditions"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public PagedQueryResult <LNewsTypeEntity> GetLNewsTypeList(IWhereCondition[] pWhereConditions, int pageIndex, int pageSize) { StringBuilder strbWhere = new StringBuilder(); if (pWhereConditions != null) { foreach (var item in pWhereConditions) { strbWhere.AppendFormat(" AND {0}", item.GetExpression()); } } UtilityEntity model = new UtilityEntity(); string sql = @"select lt.NewsTypeId,lt.NewsTypeName,lt.ParentTypeId,ltp.NewsTypeName as ParentTypeName,lt.CreateTime,lt.isdelete,lt.CustomerId,lt.TypeLevel from LNewsType as lt left join LNewsType as ltp on ltp.NewsTypeId=lt.ParentTypeId and ltp.IsDelete='0' where lt.IsDelete='0' and lt.CustomerId='" + this.CurrentUserInfo.ClientID + "'"; model.TableName = "(" + string.Format(sql, CurrentUserInfo.ClientID, CurrentUserInfo.ClientDistributorID) + strbWhere.ToString() + ") as A"; model.PageIndex = pageIndex; model.PageSize = pageSize; model.PageSort = " CreateTime desc"; new UtilityDAO(this.CurrentUserInfo).PagedQuery(model); //返回值 PagedQueryResult <LNewsTypeEntity> pEntity = new PagedQueryResult <LNewsTypeEntity>(); pEntity.RowCount = model.PageTotal; if (model.PageDataSet != null && model.PageDataSet.Tables != null && model.PageDataSet.Tables.Count > 0) { pEntity.Entities = DataLoader.LoadFrom <LNewsTypeEntity>(model.PageDataSet.Tables[0]); } return(pEntity); }
/// <summary> /// 获取路线分页列表 /// </summary> /// <param name="pWhereConditions"></param> /// <param name="pOrderBys"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public PagedQueryResult <RouteViewEntity> GetRouteList(RouteViewEntity entity, OrderBy[] pOrderBys, int pageIndex, int pageSize) { StringBuilder sqlWhere = new StringBuilder(); if (entity != null && entity.RouteName != null && !string.IsNullOrEmpty(entity.RouteName.ToString())) { sqlWhere.AppendFormat(" and A.RouteName like '%{0}%'", entity.RouteName); } if (entity != null && entity.ClientStructureID != null && !string.IsNullOrEmpty(entity.ClientStructureID.ToString())) { sqlWhere.AppendFormat(" and C.ClientStructureID in (select StructureID from dbo.fnGetStructure('{0}',1))", entity.ClientStructureID); } if (entity != null && entity.ClientUserID != null && Convert.ToInt32(entity.ClientUserID) > 0) { sqlWhere.AppendFormat(" and C.ClientUserID='{0}'", entity.ClientUserID); } StringBuilder sqlOrder = new StringBuilder(); if (pOrderBys != null && pOrderBys.Length > 0) { foreach (var item in pOrderBys) { sqlOrder.AppendFormat(" {0} {1},", item.FieldName, item.Direction == OrderByDirections.Asc ? "asc" : "desc"); } sqlOrder.Remove(sqlOrder.Length - 1, 1); } StringBuilder sql = new StringBuilder(); sql.AppendFormat(@"select distinct A.*,B.ClientUserID,C.Name as UserName,D.PositionName from [Route] A inner join RouteUserMapping B on A.RouteID=B.RouteID INNER JOIN fnGetClientUser({2}, 1) S ON B.ClientUserID = S.ClientUserID left join ClientUser C on B.ClientUserID=C.ClientUserID left join ClientPosition D on C.ClientPositionID=D.ClientPositionID where A.IsDelete=0 and A.ClientID='{0}' and A.ClientDistributorID={1}" + sqlWhere.ToString(), CurrentUserInfo.ClientID, CurrentUserInfo.ClientDistributorID, CurrentUserInfo.UserID); //通用分页查询 UtilityEntity model = new UtilityEntity(); model.TableName = "(" + sql.ToString() + ") as A"; model.PageIndex = pageIndex; model.PageSize = pageSize; model.PageSort = sqlOrder.ToString(); new UtilityDAO(this.CurrentUserInfo).PagedQuery(model); //返回值 PagedQueryResult <RouteViewEntity> pEntity = new PagedQueryResult <RouteViewEntity>(); pEntity.RowCount = model.PageTotal; if (model.PageDataSet != null && model.PageDataSet.Tables != null && model.PageDataSet.Tables.Count > 0) { pEntity.Entities = DataLoader.LoadFrom <RouteViewEntity>(model.PageDataSet.Tables[0], new DirectPropertyNameMapping()); } return(pEntity); }
public PagedQueryResult <VisitingTaskStepObjectViewEntity> GetStepBrandList(int brandLevel, int categoryLevel, Guid stepID, int pageIndex, int pageSize) { //四个dao 方法 //获取品牌列表 //获取子品牌列表 //获取品牌、品类列表 //获取子品牌、子品类列表 StringBuilder sbSql = new StringBuilder(); if (categoryLevel == 0) { //品牌 sbSql.AppendFormat(@" select B.BrandID,B.BrandNo,B.BrandName,B.IsOwner,B.BrandLevel,B.ParentID,B.ClientID,B.ClientDistributorID,B.CreateTime,B.IsDelete,o.OptionText AS Firm, (select Brandname from Brand where BrandID=B.ParentID) as ParentBrandName, cast(A.BrandID as nvarchar(20)) as Target1ID,C.Target2ID,C.ObjectID,C.VisitingTaskStepID from ( --查询客户的 brandid 集合 select A.BrandID from Brand A --inner join SKU B on A.BrandID=B.BrandID and B.IsDelete=0 and B.ClientID='{2}' and isnull(B.ClientDistributorID,0)={3} where A.BrandLevel={1} and A.IsDelete=0 and A.ClientID='{2}' and isnull(A.ClientDistributorID,0)={3} group by A.BrandID ) A inner join Brand B on A.BrandID=B.BrandID LEFT JOIN dbo.Options o on o.OptionValue=B.Firm AND o.OptionName='BrandCompany' AND o.ClientID='{2}' AND o.IsDelete=0 left join VisitingTaskStepObject C on A.BrandID=C.Target1ID and C.Target2ID is null and C.IsDelete=0 and C.VisitingTaskStepID='{0}' and C.ClientID='{2}' and isnull(C.ClientDistributorID,0)={3} ", stepID, brandLevel, CurrentUserInfo.ClientID, CurrentUserInfo.ClientDistributorID); } else { /* * select A.*,B.ObjectID,B.VisitingTaskStepID,B.Target2ID, * cast(A.BrandID as nvarchar(20))+'|'+cast(A.CategoryID as nvarchar(20)) as Target1ID from * ( * select * a.BrandName,a.BrandID,a.Remark,a.Firm,a.IsDelete,b.CategoryID,b.CategoryName, * (select Brandname from Brand where BrandID=A.ParentID) as ParentBrandName * from Brand a,Category b * where a.BrandLevel={0} and b.CategoryLevel={1} and A.IsDelete=0 and B.IsDelete=0 * and A.ClientID='{3}' and A.ClientDistributorID={4} and B.ClientID='{3}' and B.ClientDistributorID={4} * ) as A * left join VisitingTaskStepObject B * on A.BrandID=B.Target1ID and A.CategoryID=B.Target2ID and B.IsDelete=0 and B.VisitingTaskStepID='{2}' * and B.ClientID='{3}' and B.ClientDistributorID={4} */ //品牌+品类 sbSql.AppendFormat(@" select o.OptionText AS Firm,C.BrandName,C.BrandID,C.Remark,C.IsDelete,D.CategoryID,D.CategoryName, (select Brandname from Brand where BrandID=C.ParentID) as ParentBrandName ,B.ObjectID,B.VisitingTaskStepID,B.Target2ID, cast(A.BrandID as nvarchar(20))+'|'+cast(A.CategoryID as nvarchar(20)) as Target1ID from ( --查询关联客户产品的品牌-品类id集合列表 select distinct A.BrandID,A.CategoryID from SKU A inner join Brand B on A.BrandID=B.BrandID inner join Category C on A.CategoryID=C.CategoryID where A.IsDelete=0 and B.IsDelete=0 and C.IsDelete=0 and B.BrandLevel={0} and C.CategoryLevel={1} and A.ClientID='{3}' and isnull(A.ClientDistributorID,0)={4} and B.ClientID='{3}' and isnull(B.ClientDistributorID,0)={4} and C.ClientID='{3}' and isnull(C.ClientDistributorID,0)={4} ) as A left join VisitingTaskStepObject B on A.BrandID=B.Target1ID and A.CategoryID=B.Target2ID and B.IsDelete=0 and B.VisitingTaskStepID='{2}' and B.ClientID='{3}' and isnull(B.ClientDistributorID,0)={4} inner join Brand C on A.BrandID=C.BrandID inner join Category D on A.CategoryID=D.CategoryID INNER JOIN dbo.Options o on o.OptionValue=C.Firm AND o.OptionName='BrandCompany' AND o.ClientID='{3}' AND o.IsDelete=0 ", brandLevel, categoryLevel, stepID, CurrentUserInfo.ClientID, CurrentUserInfo.ClientDistributorID); } UtilityEntity model = new UtilityEntity(); model.TableName = "(" + sbSql.ToString() + ") as A"; model.PageIndex = pageIndex; model.PageSize = pageSize; if (categoryLevel == 0) { model.PageSort = "case when ObjectID is null then 1 else 0 end asc,A.BrandName"; } else { model.PageSort = "case when ObjectID is null then 1 else 0 end asc,A.CategoryName"; } new UtilityDAO(this.CurrentUserInfo).PagedQuery(model); //返回值 PagedQueryResult <VisitingTaskStepObjectViewEntity> pEntity = new PagedQueryResult <VisitingTaskStepObjectViewEntity>(); pEntity.RowCount = model.PageTotal; if (model.PageDataSet != null && model.PageDataSet.Tables != null && model.PageDataSet.Tables.Count > 0) { pEntity.Entities = DataLoader.LoadFrom <VisitingTaskStepObjectViewEntity>(model.PageDataSet.Tables[0]); } return(pEntity); }
public PagedQueryResult <VisitingTaskStepObjectViewEntity> GetStepCategoryList(int brandLevel, int categoryLevel, Guid stepID, int pageIndex, int pageSize) { StringBuilder sbSql = new StringBuilder(); if (brandLevel == 0) { //品类 sbSql.AppendFormat(@" select b.item_category_id as CategoryID, b.item_category_code as CategoryNo, b.item_category_name as CategoryName, '' as CategoryNameEn, 0 as CategoryLevel, 0 as IsLeaf, parent_id as ParentID, '' as Remark,ClientID,ClientDistributorID,CreateBy,CreateTime,LastUpdateBy,LastUpdateTime,0 as IsDelete, (select item_category_name as Categoryname from T_Item_Category where item_category_id=B.Parent_ID) as ParentCategoryName, cast(A.item_category_id as nvarchar(20)) as Target1ID,C.Target2ID,C.ObjectID,C.VisitingTaskStepID from ( --查询客户的 brandid 集合 select A.item_category_id from dbo.T_Item_Category A group by A.item_category_id ) A inner join T_Item_Category B on A.item_category_id=B.item_category_id left join VisitingTaskStepObject C on A.item_category_id=C.Target1ID and C.Target2ID is null and C.IsDelete=0 and C.VisitingTaskStepID='{0}' and C.ClientID='{2}' and isnull(C.ClientDistributorID,0)=0 ", stepID, categoryLevel, CurrentUserInfo.ClientID, CurrentUserInfo.ClientDistributorID); } else { /* * select A.*,B.ObjectID,B.VisitingTaskStepID,B.Target2ID, * cast(A.CategoryID as nvarchar(20))+'|'+cast(A.BrandID as nvarchar(20)) as Target1ID from * ( * select * a.BrandName,a.BrandID,a.IsDelete,b.CategoryID,b.CategoryName,b.Remark, * (select CategoryName from Category where CategoryID=B.ParentID) as ParentCategoryName * from Brand a,Category b * where a.BrandLevel={0} and b.CategoryLevel={1} and A.IsDelete=0 and B.IsDelete=0 * and A.ClientID='{3}' and A.ClientDistributorID={4} and B.ClientID='{3}' and B.ClientDistributorID={4} * ) as A * left join VisitingTaskStepObject B * on A.CategoryID=B.Target1ID and A.BrandID=B.Target2ID and B.IsDelete=0 and B.VisitingTaskStepID='{2}' and B.ClientID='{3}' and B.ClientDistributorID={4} */ //品类+品牌 sbSql.AppendFormat(@" select C.BrandName,C.BrandID,C.IsDelete,D.CategoryID,D.CategoryName,D.Remark, (select CategoryName from Category where CategoryID=D.ParentID) as ParentCategoryName ,B.ObjectID,B.VisitingTaskStepID,B.Target2ID, cast(A.CategoryID as nvarchar(20))+'|'+cast(A.BrandID as nvarchar(20)) as Target1ID from ( --查询关联客户产品的品牌-品类id集合列表 select distinct A.BrandID,A.CategoryID from SKU A inner join Brand B on A.BrandID=B.BrandID inner join Category C on A.CategoryID=C.CategoryID where A.IsDelete=0 and B.IsDelete=0 and C.IsDelete=0 and B.BrandLevel={0} and C.CategoryLevel={1} and A.ClientID='{3}' and isnull(A.ClientDistributorID,0)={4} and B.ClientID='{3}' and isnull(B.ClientDistributorID,0)={4} and C.ClientID='{3}' and isnull(C.ClientDistributorID,0)={4} ) as A left join VisitingTaskStepObject B on A.CategoryID=B.Target1ID and A.BrandID=B.Target2ID and B.IsDelete=0 and B.VisitingTaskStepID='{2}' and B.ClientID='{3}' and isnull(B.ClientDistributorID,0)={4} inner join Brand C on A.BrandID=C.BrandID inner join Category D on A.CategoryID=D.CategoryID ", brandLevel, categoryLevel, stepID, CurrentUserInfo.ClientID, CurrentUserInfo.ClientDistributorID); } UtilityEntity model = new UtilityEntity(); model.TableName = "(" + sbSql.ToString() + ") as A"; model.PageIndex = pageIndex; model.PageSize = pageSize; if (brandLevel == 0) { model.PageSort = " case when ObjectID is null then 1 else 0 end asc,A.CategoryName"; } else { model.PageSort = " case when ObjectID is null then 1 else 0 end asc,A.CategoryName"; } new UtilityDAO(this.CurrentUserInfo).PagedQuery(model); //返回值 PagedQueryResult <VisitingTaskStepObjectViewEntity> pEntity = new PagedQueryResult <VisitingTaskStepObjectViewEntity>(); pEntity.RowCount = model.PageTotal; if (model.PageDataSet != null && model.PageDataSet.Tables != null && model.PageDataSet.Tables.Count > 0) { pEntity.Entities = DataLoader.LoadFrom <VisitingTaskStepObjectViewEntity>(model.PageDataSet.Tables[0]); } return(pEntity); }