Title ,
IsRelease ,
CarrierType ,
CarrierID ,
CarrierValue = ( CASE WHEN CarrierType = 1
THEN ( SELECT CONVERT(VARCHAR(15), ItemCode)
+ '_' + ProductName + '_'
+ PartsNO
FROM dbo.View_MallItem WITH ( NOLOCK )
WHERE ItemId = CarrierID
)
WHEN CarrierType = 2
THEN ( SELECT BrandName + '-' + [CategoryName]
+ '_' + [Name]
FROM dbo.Product AS P WITH ( NOLOCK )
JOIN dbo.Brand AS b WITH ( NOLOCK ) ON p.BrandId = b.BrandId
JOIN dbo.View_MallCategory AS c
WITH ( NOLOCK ) ON p.CategoryId = c.CategoryId
WHERE P.ProductId = CarrierID
)
WHEN CarrierType = 3
THEN ( SELECT CONVERT(VARCHAR(15), BrandId)
+ '_' + BrandName
FROM dbo.Brand WITH ( NOLOCK )
WHERE BrandId = CarrierID
)
WHEN CarrierType = 4
THEN ( SELECT NavCategoryName
FROM dbo.View_MallNavCategory WITH ( NOLOCK )
WHERE NavCategoryId = CarrierID
)
ELSE ''
END ) ,
[Description] ,
StartTime ,
EndTime ,
AuditStatus ,
AuditUser ,
AuditTime ,
IsDeleted ,
LatestEditedUser ,
LatestEditedTime
FROM dbo.SalesActivity WITH ( NOLOCK )
WHERE ActivityID = @ActivityID
DECLARE @sql NVARCHAR(MAX)
SET @sql = '
with temp as
(
SELECT ROW_NUMBER() OVER ( ORDER BY LatestEditedTime DESC ) AS RowId ,
ActivityID ,
Title ,
IsRelease,
CarrierType ,
CarrierID ,
CarrierValue = ( CASE WHEN CarrierType = 1
THEN ( SELECT CONVERT(VARCHAR(15), ItemCode)
+ ''_'' + ProductName + ''_''
+ PartsNO
FROM dbo.View_MallItem WITH ( NOLOCK )
WHERE ItemId = CarrierID
)
WHEN CarrierType = 2
THEN ( SELECT BrandName + ''-'' + [CategoryName]
+ ''_'' + [Name]
FROM dbo.Product AS P WITH ( NOLOCK )
JOIN dbo.Brand AS b WITH ( NOLOCK ) ON p.BrandId = b.BrandId
JOIN dbo.View_MallCategory AS c WITH ( NOLOCK ) ON p.CategoryId = c.CategoryId
WHERE P.ProductId = CarrierID
)
WHEN CarrierType = 3
THEN ( SELECT BrandName
FROM dbo.Brand WITH ( NOLOCK )
WHERE BrandId = CarrierID
)
WHEN CarrierType = 4
THEN ( SELECT NavCategoryName
FROM dbo.View_MallNavCategory WITH ( NOLOCK )
WHERE NavCategoryId = CarrierID
)
ELSE ''所有产品''
END ) ,
Description ,
StartTime ,
EndTime ,
AuditStatus ,
AuditUser ,
AuditTime ,
IsDeleted ,
LatestEditedUser ,
LatestEditedTime
FROM dbo.SalesActivity WITH ( NOLOCK )
WHERE IsDeleted = 0 ' + @Condition + '
)
select * from temp where rowid BETWEEN @StartRowID AND @EndRowID'
EXEC SP_EXECUTESQL @sql,
N'@Condition nvarchar(max),@StartRowID INT,@EndRowID INT', @Condition,
@StartRowID, @EndRowID
DECLARE @sql NVARCHAR(MAX)
DECLARE @condition NVARCHAR(MAX)
SET @condition = ''
SET @sql = '
SELECT [AutoBrandName] ,
[AutoModelName] ,
[AutoModelSubId] ,
[QPType] ,
[DisplacementNoEngine] ,
[StartYear] ,
[EndYear] ,
[PartsID] ,
[PartsName] ,
[IsCommonParts] ,
[PartsSuitID] ,
[PartsConfigIDSource] ,
[AutoModelParameterID] ,
[ParameterValue] ,
[AutoModelParamConfigID] ,
[AutoPartsOEMapId] ,
[PartsConfigId] ,
[StartYearMonth] ,
[EndYearMonth] ,
[OEMId] ,
[DiplayOEMNO],
[OEMNO]
FROM [dbo].[View_AutoPartsOeData] WITH ( NOLOCK )
WHERE 1=1 '
IF @PartsID = -1
BEGIN
SET @condition = @condition + ' and PartsID IN ( '+ @PartsIDs+ ' )'
END
ELSE
BEGIN
SET @condition = @condition + ' and PartsID = @PartsID'
END
IF @PartsSuitID = 0
BEGIN
SET @condition = @condition + ' and PartsSuitID = 0 '
END
IF @IsCommonParts = 0
BEGIN
SET @condition = @condition + ' and IsCommonParts = 0 '
END
IF @IsCommonParts = 1
BEGIN
SET @condition = @condition + 'and IsCommonParts = 1 '
END
--PRINT @sql
SET @sql = @sql + @condition
EXEC SP_EXECUTESQL @sql,
N'@PartsID INT,@PartsSuitID INT,@IsCommonParts INT,@PartsIDs NVARCHAR(100)',
@PartsID, @PartsSuitID, @IsCommonParts, @PartsIDs
DECLARE @sql NVARCHAR(MAX)
DECLARE @condition NVARCHAR(MAX)
SET @condition = 'WHERE b.IsDeleted<>1 '
IF @keyword <> ''
BEGIN
SET @condition = @condition
+ ' AND (b.BrandName LIKE @keyword OR b.AbbreviatedName LIKE @keyword
OR b.EnglishName LIKE @keyword OR b.DisplayBrandName LIKE @keyword
OR b.[Description] LIKE @keyword) '
END
IF @IsEnabled = 1
OR @IsEnabled = 0
BEGIN
SET @condition = @condition + 'AND b.[IsEnabled]=@IsEnabled'
END
IF @CategoryType = 2
BEGIN
SET @condition = @condition + ' AND ( SELECT COUNT(ItemId)
FROM dbo.ItemMaintain i WITH ( NOLOCK )
WHERE i.IsDeleted = 0 and i.BrandId = b.BrandId
AND i.MerchantUserID > 1
) > 0 '
END
IF @CategoryType = 0
BEGIN
SET @condition = @condition + ' AND ( SELECT COUNT(ItemId)
FROM dbo.ItemMaintain i WITH ( NOLOCK )
WHERE i.IsDeleted = 0 and i.BrandId = b.BrandId
AND i.MerchantUserID = 1
) > 0 '
END
SET @sql = 'WITH BrandList AS
(
SELECT ROW_NUMBER() OVER ( ORDER BY b.[Alphabet] ) AS rowid ,
b.[BrandId] ,
b.[BrandName] ,
b.[Alphabet] ,
b.[AbbreviatedName] ,
b.[EnglishName] ,
b.[DisplayBrandName] ,
b.[LogoPath] ,
b.[CertificateImage] ,
b.[WebsiteURL] ,
b.[DisplayOrder] ,
b.[IsEnabled] ,
b.[IsDeleted] ,
b.[LatestEditedUser] ,
b.[LatestEditedTime] ,
b.[Description] ,
b.IsTrusted ,
b.[TrustBrandUrl]
FROM [dbo].[Brand] AS b WITH ( NOLOCK )
' + @condition + ')
SELECT *
FROM BrandList WITH ( NOLOCK )
WHERE rowid BETWEEN @StartIndex AND @EndIndex'
EXEC SP_EXECUTESQL @sql,
N'@StartIndex INT,@EndIndex INT,@keyword NVARCHAR(100),@IsEnabled SMALLINT,@CategoryType INT',
@StartIndex, @EndIndex, @keyword, @IsEnabled,@CategoryType
<!--判断等级是否已存在-->
--新增判断
IF @GrandId=0
BEGIN
SELECT COUNT(GrandId)
FROM dbo.CategoryGrand WITH(NOLOCK)
WHERE IsDeleted = 0 AND CategoryId=@CategoryId
AND (GrandName=@GrandName OR GrandValue=@GrandValue) AND GradeType=@GradeType
END
--编辑判断
IF @GrandId<>0
BEGIN
SELECT COUNT(GrandId)
FROM dbo.CategoryGrand WITH(NOLOCK)
WHERE IsDeleted = 0 AND CategoryId=@CategoryId
AND (GrandName=@GrandName OR GrandValue=@GrandValue) AND GradeType=@GradeType
AND GrandId NOT IN (@GrandId)
END