public DataSet GetSpreadSettingInfoByTemplateId(string strTemplateId)
{
string strSql = string.Format(@"SELECT *,b.ImageURL FROM dbo.T_CTW_SpreadSetting a INNER JOIN dbo.ObjectImages b ON a.ImageId=b.ImageId
WHERE a.TemplateId='{0}'", strTemplateId);
return(staticSqlHelper.ExecuteDataset(strSql));
}
public DataSet GetImageList(string strEventId)
{
DataSet ds = new DataSet();
string strSql = string.Format("select ImageURL,RuleId,RuleContent,BatId from ObjectImages with(nolock) where ObjectId='{0}' and isdelete=0", strEventId);
return(staticSqlHelper.ExecuteDataset(strSql));
}
public DataSet GetThemeInfo(string strTemplateId)
{
string strSql = string.Format(@"SELECT *,b.ImageURL ,C.ActivityGroupId,[TemplateName]
FROM [dbo].[T_CTW_LEventTheme] a
LEFT JOIN dbo.ObjectImages b ON a.ImageId=b.ImageId
LEFT JOIN [dbo].[T_CTW_LEventTemplate] C ON A.[TemplateId]=C.[TemplateId]
WHERE a.IsDelete=0 and A.TemplateId='{0}' Order by a.createtime asc", strTemplateId);
return(staticSqlHelper.ExecuteDataset(strSql));
}
/// <summary>
/// 根据模版id获取互动方式
/// </summary>
/// <param name="strTemplateId"></param>
/// <returns></returns>
public DataSet GetEventInteractionByTemplateId(string strTemplateId)
{
DataSet ds = null;
string strSql = @"SELECT
b.InteractionType
,CASE
WHEN b.InteractionType=1 THEN '吸粉'
WHEN b.InteractionType=2 THEN '促销'
END InteractionTypeName
,a.TemplateId
,a.ThemeId
,a.ThemeName
,a.H5Url
,a.H5TemplateId
,b.LeventId
,c.DrawMethodCode
,d.ActivityGroupId ,
d.ImageURL
FROM T_CTW_LEventTheme a
LEFT JOIN T_CTW_LEventInteraction b on a.ThemeId=b.ThemeId
LEFT JOIN T_CTW_LEventDrawMethod c on b.DrawMethodId=c.DrawMethodId
LEFT JOIN ( SELECT ActivityGroupId ,
TemplateId ,
o.ImageURL
FROM T_CTW_LEventTemplate a
LEFT JOIN dbo.ObjectImages o ON a.ImageId = o.ImageId
) d ON a.TemplateId = d.TemplateId
where a.TemplateId=@strTemplateId and a.IsDelete=0
order by a.createtime asc
";
List <SqlParameter> paras = new List <SqlParameter> {
};
paras.Add(new SqlParameter()
{
ParameterName = "@strTemplateId", Value = strTemplateId
});
ds = staticSqlHelper.ExecuteDataset(CommandType.Text, strSql, paras.ToArray());
return(ds);
}
/// <summary>
/// 获取创意仓库主题列表信息（包括主题列表，计划列表，Banner列表）
/// </summary>
/// <returns></returns>
public DataSet GetTemplateList(string strActivityGroupCode)
{
DataSet ds = new DataSet();
string strSql = string.Format(@"
SELECT AdId ,
a.ActivityGroupId,
b.[ActivityGroupCode] ,
b.Name,
a.TemplateId ,
c.ImageURL ,
BannerUrl ,
BannerName ,
a.DisplayIndex ,
Status,
( SELECT TOP 1
RCodeUrl
FROM T_CTW_LEventTheme theme
WHERE theme.TemplateId = a.TemplateId
AND RCodeUrl IS NOT NULL
order by theme.createtime asc
)QRCodeUrl
,
(ISNULL(temp.Usecount,0)+ISNULL(temp.ClickCount,0)) as UserCount
FROM T_CTW_Banner a
INNER JOIN T_CTW_LEventTemplate temp ON a.TemplateId=temp.TemplateId and temp.IsDelete=0
LEFT JOIN SysMarketingGroupType b ON a.ActivityGroupId = b.ActivityGroupId
LEFT JOIN dbo.ObjectImages c ON a.BannerImageId = c.ImageId
WHERE a.IsDelete = 0 and Status=30
ORDER BY a.DisplayIndex
SELECT tem.TemplateId ,
tem.TemplateName ,
Img.ImageURL ,
CASE WHEN tem.TemplateStatus = 10 THEN '待上架'
WHEN tem.TemplateStatus = 20 THEN '待发布'
WHEN tem.TemplateStatus = 30 THEN '已发布'
WHEN tem.TemplateStatus = 40 THEN '已下架'
END TemplateStatus ,
b.ActivityGroupCode,
b.ActivityGroupId,
( SELECT TOP 1
RCodeUrl
FROM T_CTW_LEventTheme theme
WHERE theme.TemplateId = Tem.TemplateId
AND RCodeUrl IS NOT NULL
order by theme.createtime asc
)RCodeUrl,
(ISNULL(tem.Usecount,0)+ISNULL(tem.ClickCount,0)) as UserCount
FROM T_CTW_LEventTemplate Tem
LEFT JOIN dbo.ObjectImages Img ON Tem.ImageId = Img.ImageId
INNER JOIN SysMarketingGroupType b ON Tem.ActivityGroupId = b.ActivityGroupId and (b.ActivityGroupCode='{0}' or '{0}'='')
WHERE Tem.IsDelete = 0 AND TemplateStatus=30
ORDER BY Tem.DisplayIndex ASC
SELECT
CONVERT(NVARCHAR(10), PlanDate,120) PlanDate ,
PlanName ,
DisplayIndex
FROM T_CTW_SeasonPlan
WHERE IsDelete =0
ORDER BY PlanDate ASC
SELECT O.ImageURL PlanImageUrl
FROM T_CTW_HomePageCommon H
INNER JOIN ObjectImages O ON H.ImageId=O.ImageId
WHERE h.IsDelete=0 AND SetType='SeasonPlan'
", strActivityGroupCode);
ds = staticSqlHelper.ExecuteDataset(strSql);
return(ds);
}
public DataSet GetPanicbuyingEventImage(string strEventId)
{
string strSql = string.Format("select b.ImageURL,a.EventName as PanicbuyingEventName from T_CTW_PanicbuyingEvent a inner join ObjectImages b on a.ImageId=b.ImageId and b.isdelete=0 where a.EventId='{0}'", strEventId);
return(staticSqlHelper.ExecuteDataset(strSql));
}