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