SET QUOTED_IDENTIFIER ON 
SET NOCOUNT ON

--v6.1版青岛定制功能脚本，此次统一集成在v6.3版本中
--sitePara 系统参数


if NOT exists(select * from sitePara where pcode='picWidth')
insert into sitePara(ptype,pcode,pvalue,pseq,pnote)values('sys','picWidth','1000','1','是否开启频道静态页')
if NOT exists(select * from sitePara where pcode='MbNewsTmpid')
insert into sitePara(ptype,pcode,pvalue,pseq,pnote)values('PAGE','MbNewsTmpid','0','2','手机版新闻页模版id')
if NOT exists(select * from sitePara where pcode='IsStatic')
insert into sitePara(ptype,pcode,pvalue,pseq,pnote)values('sys','IsStatic','0','5','是否开启频道页静态化')
if NOT exists(select * from sitePara where pcode='MbIndex')
insert into sitePara(ptype,pcode,pvalue,pseq,pnote)values('PAGE','MbIndex','0','3','手机版首页频道id');
if NOT exists(select * from sitePara where pcode='IsAllowCtb')insert into sitePara(ptype,pcode,pvalue,pseq,pnote)values('sys','IsAllowCtb','1','10','是否开启子站投稿')

update sitepara set pseq = '5',pnote='是否开启频道静态页' where pcode='IsStatic';

if NOT exists(select * from sitePara where pcode='IsAutoUpdate')
insert into sitePara(ptype,pcode,pvalue,pseq,pnote)values('sys','IsAutoUpdate','1','6','静态页生成是否启用自动更新1自动更新0手动更新')

--新增图文翻动(宽版)栏目
if not exists(select * from template where pkId=77)
begin
SET IDENTITY_INSERT [dbo].[template] ON
insert into template ([pkId],[name],[remark],[code],[previewTemplate],[typeId])values('77','图文翻动(宽版)','图文翻动(宽版)','scrollFlashNews1','scrollFlashNews1.htm',2)
SET IDENTITY_INSERT [dbo].[template] OFF
end
GO

--v6.3版新增脚本请在此后开始维护
--新增最新图文翻动栏目
if not exists(select * from template where pkId=78)
begin
SET IDENTITY_INSERT [template] ON
insert into template ([pkId],[name],[remark],[code],[previewTemplate],[typeId])values('78','最新图文翻动','最新图文翻动','latestscrollFlashNews','latestscrollFlashNews.htm',2)
SET IDENTITY_INSERT [template] OFF
end
GO

--增加留言本用到的系统权限
if not exists(select * from privilege where pkId=13)
begin
SET IDENTITY_INSERT [privilege] ON
insert into privilege ([pkId],[name],nameRemark,accreditValue,accreditRemark,showType,itemEnglish,itemChinese) 
values ('13','canAuditReply','回复审核','0|1','禁止|允许','Select','forAuditing|audited','待审|通过')
SET IDENTITY_INSERT [privilege] OFF
end
go

if not exists(select * from privilege where pkId=14)
begin
SET IDENTITY_INSERT [privilege] ON
insert into privilege ([pkId],[name],nameRemark,accreditValue,accreditRemark,showType,itemEnglish,itemChinese) 
values ('14','defaultReplyPassed','回复默认审核状态','0|1','未审|已审','Select','','')
SET IDENTITY_INSERT [privilege] OFF
end
go

--增加系统栏目权限对应关系数据
if not exists(select * from templatePrivilege where templateid=32)
begin
insert into templatePrivilege (templateId,privilegeId) values ('32','13')
insert into templatePrivilege (templateId,privilegeId) values ('32','14')
end
go

if not exists(select * from templatePrivilege where templateid=63)
begin
insert into templatePrivilege (templateId,privilegeId) values ('63','13')
insert into templatePrivilege (templateId,privilegeId) values ('63','14')
end
go

--增加不通过状态 v6.3版20150805 
update privilege set itemEnglish='forAuditing|audited|rejected' ,itemChinese ='待审|通过|不通过' where pkid='1'

GO

--新增信息查询(新版)栏目
if not exists(select * from template where pkId=79)
begin
SET IDENTITY_INSERT [template] ON
insert into template ([pkId],[name],[remark],[code],[previewTemplate],[typeId])values('79','信息查询(新版)','信息查询(新版)','infoSearch1','infoSearch1.htm',5)
SET IDENTITY_INSERT [template] OFF
end
GO

--新增信息查询(新版)栏目权限
if not exists(select * from templatePrivilege where templateid=79)
begin
insert into templatePrivilege (templateId,privilegeId) values ('79','1')
insert into templatePrivilege (templateId,privilegeId) values ('79','4')
insert into templatePrivilege (templateId,privilegeId) values ('79','5')
insert into templatePrivilege (templateId,privilegeId) values ('79','6')
insert into templatePrivilege (templateId,privilegeId) values ('79','7')
insert into templatePrivilege (templateId,privilegeId) values ('79','8')
insert into templatePrivilege (templateId,privilegeId) values ('79','9')
end
go

--修改留言本系统权限
update privilege set itemEnglish='forAuditing|audited|noReply' ,itemChinese ='待审|通过|未回复' where pkid='13'
GO

--增加微课掌上通公众号推送参数
if NOT exists(select * from sitePara where pcode='IsPushwk')
insert into sitePara(ptype,pcode,pvalue,pseq,pnote)values('weike','IsPushwk','0','15','是否开启微课公众号推送');

if NOT exists(select * from sitePara where pcode='wkUrl')
insert into sitePara(ptype,pcode,pvalue,pseq,pnote)values('weike','wkUrl','','16','微课推送服务地址');

if NOT exists(select * from sitePara where pcode='wkAppoint')
insert into sitePara(ptype,pcode,pvalue,pseq,pnote)values('weike','wkAppoint','','17','微课推送服务约定字符串');

if NOT exists(select * from sitePara where pcode='wkPubid')
insert into sitePara(ptype,pcode,pvalue,pseq,pnote)values('weike','wkPubid','','18','微课公众号ID');

if NOT exists(select * from sitePara where pcode='wkPubname')
insert into sitePara(ptype,pcode,pvalue,pseq,pnote)values('weike','wkPubname','','19','微课公众号名称');

GO

----------------------------------------------------------------------
--v6.3版本升级处理脚本
----------------------------------------------------------------------

--处理用户组权限表冗余数据
delete groupAssign where menuId not in (select pkId from menu)
go
--处理用户授权冗余数据
delete moduleAssign where menuId not in (select pkId from menu)
go
--处理抄送信息审核异常数据
update news set passed='1' where passed is null

--需要清理原来列表标题中存放的html标签，检索出数据库中的href=”开头”结束的链接字符
update news set listtitle=left(right(listTitle,len(listTitle)-charindex('href="',listTitle)-5),charindex('"',right(listTitle,len(listTitle)-charindex('href="',listTitle)-5))-1) where charindex('href="',listTitle)>0
go

--取该栏目最新一条新闻的new.Width和height作为menu的默认值
update a set width=(select top 1 width from news d where d.bigclassId=a.bigclassid  order by updateTime desc),
height=(select top 1 height from news d where d.bigclassId=a.bigclassid  order by updateTime desc)
from menu a,bigclass b,template c
where a.bigclassid=b.pkid and b.templateId=c.pkid and c.typeId=2 and a.width is null and a.height is null

go

--批量处理栏目类型名称--v6.1版本更过一次。此处重复执行防止遗漏。
update template set [name]='网站搜索(无选择条件)' where pkid='17'
GO
update template set [name]='会员登录(横向)' where pkid='18'
GO
update template set [name]='网站搜索(无选择条件)' where pkid='17'
GO
update template set [name]='新闻点击排行' where pkid='7'
GO

--修改templateType表中数据--v6.1版本更过一次。此处重复执行防止遗漏。此处分类影响图文类栏目指定默认图片宽高。
--1.新闻类2.图文类3.链接类4.互动类5.工具类
update templatetype set [name]='news',type='新闻类' where pkid='1'
GO
UPDATE templateType SET [name] ='picVedio',type ='图文类' where pkId='2'
GO
UPDATE templateType SET [name] ='interact',type ='互动类'  where pkId='4'
GO
UPDATE templateType SET [name] ='tool' ,type = '功能类' where pkId='5' 
GO
update template set typeId='1' where pkid in (1,5,7,10,13,23,28,36)
GO
update template set typeId='2' where pkid in (6,12,24,25,26,27,43,71)
GO
update template set typeId='3' where pkid='37'
GO
update template set typeId='4' where pkid in (14,22,32,33,45,46,47,48,60,63,65)
GO
update template set typeId='5' where pkid in (9,11,15,16,17,18,19,30,58,65,66,67,69,72,73,74)
GO
update template set typeId='6' where pkid in (49,50,51,52,53,54,55,56,57,62,64,68,70)
GO

--浏览权限:批量更新【仅外网用户访问2】改为【无限制0】
update news set gate='0' where gate='2' 
go

--初始化数据将留言回复审核设置为通过
update messageboard set reAudit='1' 
go

--用户权限：批量追加留言本、留言问答栏目回复审核权限，默认通过
update moduleAssign set privilegeset= privilegeset+'|canAuditReply=1|defaultReplyPassed=1' 
where menuid in ( 
select pkid from menu where modeid=4 and bigclassid in (select pkid from bigclass where templateid in (32,63) )) 
and privilegeset not like '%canAuditReply%'
go

--用户组权限：批量追加留言本、留言问答栏目在回复审核权限，默认通过
update groupAssign set privilegeset= privilegeset+'|canAuditReply=1|defaultReplyPassed=1' 
where menuid in ( 
select pkid from menu where modeid=4 and bigclassid in (select pkid from bigclass where templateid in (32,63) )) 
and privilegeset not like '%canAuditReply%'
go

--删除网站调查栏目的冗余权限信息
delete from templatePrivilege where templateid='14' and privilegeid='8'
go
delete from templatePrivilege where templateid='14' and privilegeid='9'
go


SET NOCOUNT OFF
SET QUOTED_IDENTIFIER OFF 