CREATE TABLE [dbo].[PALBUM_TAB] (
[photoid] [bigint] IDENTITY (300000, 1) NOT NULL ,
[id] [bigint] NOT NULL ,
[user_name] [varchar] (40) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[cataid] [int] NOT NULL ,
[filetype] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[filesize] [bigint] NOT NULL ,
[title] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[intro] [text] COLLATE Chinese_PRC_CI_AS NULL ,
[pubtime] [datetime] NOT NULL ,
[pubip] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[width] [int] NOT NULL ,
[height] [int] NOT NULL ,
[ifuse] [int] NOT NULL ,
[set_time] [datetime] NULL ,
[set_id] [bigint] NULL ,
[Photostatus] [int] NOT NULL ,
[favoriteCount] [bigint] NOT NULL ,
[commentsCount] [bigint] NOT NULL ,
[indexed] [int] NOT NULL ,
[viewCount] [bigint] NOT NULL ,
[hashid] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[PHOTOADMIN_TAB] (
[id] [bigint] NOT NULL ,
[user_name] [varchar] (40) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[settime] [datetime] NOT NULL ,
[deleted] [char] (1) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[type] [char] (1) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PHOTOSIZE_TAB] (
[PhotoSizeID] [bigint] IDENTITY (1, 1) NOT NULL ,
[id] [bigint] NOT NULL ,
[UploadMonth] [char] (6) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[PhotoSize] [bigint] NOT NULL ,
[PhotoCount] [bigint] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PhotoVIP_TAB] (
[id] [bigint] NOT NULL ,
[timeout] [datetime] NOT NULL ,
[lastfeetime] [datetime] NOT NULL ,
[type] [char] (1) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[SECTION_TAB] (
[SectionID] [bigint] IDENTITY (1, 1) NOT NULL ,
[id] [bigint] NOT NULL ,
[SectionName] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[PhotoCount] [bigint] NOT NULL ,
[CreateTime] [datetime] NOT NULL ,
[intro] [text] COLLATE Chinese_PRC_CI_AS NULL ,
[photoID] [bigint] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[dtproperties] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[objectid] [int] NULL ,
[property] [varchar] (64) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[value] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[uvalue] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[lvalue] [image] NULL ,
[version] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[COMMENT_TAB] (
[CommentID] [bigint] IDENTITY (1, 1) NOT NULL ,
[id] [bigint] NOT NULL ,
[user_name] [varchar] (40) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[CommentTime] [datetime] NOT NULL ,
[photoid] [bigint] NOT NULL ,
[ip] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[CommentContents] [text] COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[FAVORITE_TAB] (
[id] [bigint] NOT NULL ,
[photoID] [bigint] NOT NULL ,
[addtime] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PHOTO_SECTION_TAB] (
[PhotoID] [bigint] NOT NULL ,
[SectionID] [bigint] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PHOTO_TAG_TAB] (
[PhotoTagID] [bigint] IDENTITY (1, 1) NOT NULL ,
[PhotoID] [bigint] NOT NULL ,
[TagID] [bigint] NOT NULL ,
[TagName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[UserTagID] [bigint] NOT NULL ,
[id] [bigint] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ADMINSIGN_TAB] WITH NOCHECK ADD
CONSTRAINT [PK_ADMINSIGN_TAB] PRIMARY KEY CLUSTERED
(
  [id],
  [user_name],
  [in_time]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[BANCOOKIE_TAB] WITH NOCHECK ADD
CONSTRAINT [PK_BANCOOKIE_TAB] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[BANIP_TAB] WITH NOCHECK ADD
CONSTRAINT [PK_BANIP_TAB] PRIMARY KEY CLUSTERED
(
[ip]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[BANUSER_TAB] WITH NOCHECK ADD
CONSTRAINT [PK_BANUSER_TAB] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Broadcast_Tab] WITH NOCHECK ADD
CONSTRAINT [PK_Broadcast_Tab] PRIMARY KEY CLUSTERED
(
[BroadCastID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CommendedFavorite_Tab] WITH NOCHECK ADD
CONSTRAINT [PK_CommendedFavorite] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CommendedSection_Tab] WITH NOCHECK ADD
CONSTRAINT [PK_SectionCommended_Tab] PRIMARY KEY CLUSTERED
(
[SectionID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[LastLogin_Tab] WITH NOCHECK ADD
CONSTRAINT [PK_LastLogin_Tab] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PALBUM_TAB] WITH NOCHECK ADD
CONSTRAINT [PK_PALBUM_TAB] PRIMARY KEY CLUSTERED
(
[photoid]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[PHOTOADMIN_TAB] WITH NOCHECK ADD
CONSTRAINT [PK_PHOTOADMIN_TAB] PRIMARY KEY CLUSTERED
(
[id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[PHOTOSIZE_TAB] WITH NOCHECK ADD
CONSTRAINT [PK_PHOTOSIZE_TAB] PRIMARY KEY CLUSTERED
(
[PhotoSizeID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PhotoVIP_TAB] WITH NOCHECK ADD
CONSTRAINT [PK_PhotoVIP_TAB] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SECTION_TAB] WITH NOCHECK ADD
CONSTRAINT [PK_SECTION_TAB] PRIMARY KEY CLUSTERED
(
[SectionID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[dtproperties] WITH NOCHECK ADD
CONSTRAINT [pk_dtproperties] PRIMARY KEY CLUSTERED
(
[id],
[property]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[COMMENT_TAB] WITH NOCHECK ADD
CONSTRAINT [PK_COMMENT_TAB] PRIMARY KEY CLUSTERED
(
[CommentID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[FAVORITE_TAB] WITH NOCHECK ADD
CONSTRAINT [PK_FAVORITE_TAB] PRIMARY KEY CLUSTERED
(
[id],
[photoID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PHOTO_SECTION_TAB] WITH NOCHECK ADD
CONSTRAINT [PK_PHOTO_SECTION_TAB] PRIMARY KEY CLUSTERED
(
[PhotoID],
[SectionID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PHOTO_TAG_TAB] WITH NOCHECK ADD
CONSTRAINT [PK_PHOTO_TAG_TAB] PRIMARY KEY CLUSTERED
(
[PhotoTagID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[BANCOOKIE_TAB] WITH NOCHECK ADD
CONSTRAINT [DF_BANCOOKIE_TAB_addtime] DEFAULT (getdate()) FOR [addtime]
GO

ALTER TABLE [dbo].[BANIP_TAB] WITH NOCHECK ADD
CONSTRAINT [DF_BANIP_TAB_addtime] DEFAULT (getdate()) FOR [addtime]
GO

ALTER TABLE [dbo].[BANUSER_TAB] WITH NOCHECK ADD
CONSTRAINT [DF_BANUSER_TAB_addtime] DEFAULT (getdate()) FOR [addtime]
GO

ALTER TABLE [dbo].[Broadcast_Tab] WITH NOCHECK ADD
CONSTRAINT [DF_Broadcast_Tab_addtime] DEFAULT (getdate()) FOR [addtime]
GO

ALTER TABLE [dbo].[CommendedFavorite_Tab] WITH NOCHECK ADD
CONSTRAINT [DF_CommendedFavorite_CommendedTime] DEFAULT (getdate()) FOR [CommendedTime],
CONSTRAINT [DF_CommendedFavorite_Tab_CommendedOrder] DEFAULT (0) FOR [CommendedOrder]
GO

ALTER TABLE [dbo].[CommendedSection_Tab] WITH NOCHECK ADD
CONSTRAINT [DF_SectionCommended_Tab_CommendedTime] DEFAULT (getdate()) FOR [CommendedTime]
GO

ALTER TABLE [dbo].[LastLogin_Tab] WITH NOCHECK ADD
CONSTRAINT [DF_LastLogin_Tab_LastLoginTime] DEFAULT (getdate()) FOR [LastLoginTime]
GO

ALTER TABLE [dbo].[PALBUM_TAB] WITH NOCHECK ADD
CONSTRAINT [DF_PALBUM_TAB_cataid] DEFAULT (0) FOR [cataid],
CONSTRAINT [DF_PALBUM_TAB_ifuse] DEFAULT (0) FOR [ifuse],
CONSTRAINT [DF_PALBUM_TAB_favoriteCount] DEFAULT (0) FOR [favoriteCount],
CONSTRAINT [DF_PALBUM_TAB_commentsCount] DEFAULT (0) FOR [commentsCount],
CONSTRAINT [DF_PALBUM_TAB_indexed] DEFAULT (0) FOR [indexed],
CONSTRAINT [DF_PALBUM_TAB_viewCount] DEFAULT (0) FOR [viewCount]
GO

ALTER TABLE [dbo].[PHOTOADMIN_TAB] WITH NOCHECK ADD
CONSTRAINT [DF_PHOTOADMIN_TAB_settime] DEFAULT (getdate()) FOR [settime],
CONSTRAINT [DF_PHOTOADMIN_TAB_deleted] DEFAULT (0) FOR [deleted],
CONSTRAINT [DF_PHOTOADMIN_TAB_type] DEFAULT (1) FOR [type]
GO

ALTER TABLE [dbo].[PHOTOSIZE_TAB] WITH NOCHECK ADD
CONSTRAINT [IX_PHOTOSIZE_TAB] UNIQUE NONCLUSTERED
(
[id],
[UploadMonth]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PhotoVIP_TAB] WITH NOCHECK ADD
CONSTRAINT [DF_PhotoVIP_TAB_lastfeetime] DEFAULT (getdate()) FOR [lastfeetime]
GO

ALTER TABLE [dbo].[SECTION_TAB] WITH NOCHECK ADD
CONSTRAINT [DF_SECTION_TAB_CreateTime] DEFAULT (getdate()) FOR [CreateTime]
GO

ALTER TABLE [dbo].[dtproperties] WITH NOCHECK ADD
CONSTRAINT [DF__dtpropert__versi__77BFCB91] DEFAULT (0) FOR [version]
GO

ALTER TABLE [dbo].[COMMENT_TAB] WITH NOCHECK ADD
CONSTRAINT [DF_COMMENT_TAB_CommentTime] DEFAULT (getdate()) FOR [CommentTime]
GO

ALTER TABLE [dbo].[FAVORITE_TAB] WITH NOCHECK ADD
CONSTRAINT [DF_FAVORITE_TAB_addtime] DEFAULT (getdate()) FOR [addtime],
CONSTRAINT [IX_FAVORITE_TAB] UNIQUE NONCLUSTERED
(
[id],
[photoID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PHOTO_SECTION_TAB] WITH NOCHECK ADD
CONSTRAINT [IX_PHOTO_SECTION_TAB] UNIQUE NONCLUSTERED
(
[PhotoID],
[SectionID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PHOTO_TAG_TAB] WITH NOCHECK ADD
CONSTRAINT [IX_PHOTO_TAG_TAB_1] UNIQUE NONCLUSTERED
(
[PhotoID],
[TagID]
) ON [PRIMARY]
GO

CREATE INDEX [IX_CommendedFavorite_order] ON [dbo].[CommendedFavorite_Tab]([CommendedOrder]) ON [PRIMARY]
GO

CREATE INDEX [IX_CommendedFavorite_ifuse_order] ON [dbo].[CommendedFavorite_Tab]([CommendedOrder], [ifuse]) ON [PRIMARY]
GO

CREATE INDEX [IX_ifuse_order] ON [dbo].[CommendedSection_Tab]([ifuse], [CommendedOrder]) ON [PRIMARY]
GO

CREATE INDEX [ix_status] ON [dbo].[PALBUM_TAB]([Photostatus]) ON [PRIMARY]
GO

CREATE INDEX [ix_ifuse] ON [dbo].[PALBUM_TAB]([ifuse]) ON [PRIMARY]
GO

CREATE INDEX [ix_ifuse_index] ON [dbo].[PALBUM_TAB]([ifuse], [indexed]) ON [PRIMARY]
GO

CREATE INDEX [ix_id_index] ON [dbo].[PALBUM_TAB]([photoid], [indexed]) ON [PRIMARY]
GO

CREATE INDEX [ix_photoid_id] ON [dbo].[PALBUM_TAB]([photoid], [id]) ON [PRIMARY]
GO

CREATE INDEX [ix_photoid_index] ON [dbo].[PALBUM_TAB]([photoid], [indexed]) ON [PRIMARY]
GO

CREATE INDEX [ix_photoid_ifuse] ON [dbo].[PALBUM_TAB]([photoid], [ifuse]) ON [PRIMARY]
GO

CREATE INDEX [ix_photoid_id_ifuse] ON [dbo].[PALBUM_TAB]([photoid], [id], [ifuse]) ON [PRIMARY]
GO

CREATE INDEX [ix_id_status_ifuse] ON [dbo].[PALBUM_TAB]([photoid], [ifuse], [Photostatus]) ON [PRIMARY]
GO

CREATE INDEX [ix_status_ifuse] ON [dbo].[PALBUM_TAB]([Photostatus], [ifuse]) ON [PRIMARY]
GO

CREATE INDEX [IX_photoid_status_ifuse] ON [dbo].[PALBUM_TAB]([photoid], [ifuse], [Photostatus]) ON [PRIMARY]
GO

CREATE INDEX [IX_id_photoid_status_ifuse] ON [dbo].[PALBUM_TAB]([photoid], [id], [ifuse], [Photostatus]) ON [PRIMARY]
GO

CREATE INDEX [index_photoid_indexed_ifuse] ON [dbo].[PALBUM_TAB]([photoid], [ifuse], [indexed]) ON [PRIMARY]
GO

CREATE INDEX [IX_PhotoVIP_TAB] ON [dbo].[PhotoVIP_TAB]([timeout]) ON [PRIMARY]
GO

CREATE INDEX [id_timeout] ON [dbo].[PhotoVIP_TAB]([id], [timeout]) ON [PRIMARY]
GO

CREATE INDEX [IX_createtime] ON [dbo].[SECTION_TAB]([CreateTime]) ON [PRIMARY]
GO

CREATE INDEX [ix_PhotoCount] ON [dbo].[SECTION_TAB]([PhotoCount]) ON [PRIMARY]
GO

CREATE INDEX [ix_id] ON [dbo].[SECTION_TAB]([id]) ON [PRIMARY]
GO

CREATE INDEX [ix_id_sectionid] ON [dbo].[SECTION_TAB]([SectionID], [id]) ON [PRIMARY]
GO

CREATE INDEX [IX_COMMENT_TAB] ON [dbo].[COMMENT_TAB]([photoid]) ON [PRIMARY]
GO

CREATE INDEX [IX_COMMENT_TAB_1] ON [dbo].[COMMENT_TAB]([CommentID]) ON [PRIMARY]
GO

CREATE INDEX [IX_comment_photoid_id] ON [dbo].[COMMENT_TAB]([photoid], [id]) ON [PRIMARY]
GO

CREATE INDEX [IX_FAVORITE_TAB_1] ON [dbo].[FAVORITE_TAB]([id]) ON [PRIMARY]
GO

CREATE INDEX [ix_addtime] ON [dbo].[FAVORITE_TAB]([addtime]) ON [PRIMARY]
GO

CREATE INDEX [IX_PHOTO_TAG_TAB] ON [dbo].[PHOTO_TAG_TAB]([TagID]) ON [PRIMARY]
GO

CREATE INDEX [IX_PHOTO_TAG_TAB_2] ON [dbo].[PHOTO_TAG_TAB]([PhotoTagID]) ON [PRIMARY]
GO

CREATE INDEX [IX_tagid_id] ON [dbo].[PHOTO_TAG_TAB]([TagID], [id]) ON [PRIMARY]
GO

CREATE INDEX [IX_tagname] ON [dbo].[PHOTO_TAG_TAB]([TagName]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[COMMENT_TAB] ADD
CONSTRAINT [FK_COMMENT_TAB_PALBUM_TAB] FOREIGN KEY
(
[photoid]
) REFERENCES [dbo].[PALBUM_TAB] (
[photoid]
)
GO

ALTER TABLE [dbo].[FAVORITE_TAB] ADD
CONSTRAINT [FK_FAVORITE_TAB_PALBUM_TAB] FOREIGN KEY
(
[photoID]
) REFERENCES [dbo].[PALBUM_TAB] (
[photoid]
)
GO

ALTER TABLE [dbo].[PHOTO_SECTION_TAB] ADD
CONSTRAINT [FK_PHOTO_SECTION_TAB_PALBUM_TAB] FOREIGN KEY
(
  [PhotoID]
) REFERENCES [dbo].[PALBUM_TAB] (
  [photoid]
),
CONSTRAINT [FK_PHOTO_SECTION_TAB_SECTION_TAB1] FOREIGN KEY
(
  [SectionID]
) REFERENCES [dbo].[SECTION_TAB] (
  [SectionID]
)
GO

ALTER TABLE [dbo].[PHOTO_TAG_TAB] ADD
CONSTRAINT [FK_PHOTO_TAG_TAB_PALBUM_TAB] FOREIGN KEY
(
[PhotoID]
) REFERENCES [dbo].[PALBUM_TAB] (
[photoid]
)
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.SP_BANCOOKIE_DELETE
@id varchar(1000)
AS
SET NOCOUNT ON
declare @sql nvarchar (1100);
set @sql = 'delete BANCOOKIE_TAB where id in ('+ @id + ')'
EXECUTE sp_executesql @sql;
RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.SP_BANIP_DELETE
@ip varchar(1000)
AS
SET NOCOUNT ON
declare @sql nvarchar (1100);
set @sql = 'delete BANIP_TAB where ip in ('+ @ip + ')'
EXECUTE sp_executesql @sql;
RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.SP_BANUSER_DELETE
@id varchar(1000)
AS
SET NOCOUNT ON
declare @sql nvarchar (1100);
set @sql = 'delete BANUSER_TAB where id in ('+ @id + ')'
EXECUTE sp_executesql @sql;
RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.SP_BOARDADMIN_CHECKMASTER
@id  int,
@result  char(1) output
AS
SET NOCOUNT ON
  if (exists(select id from boardadmin_tab where bid = '0' and id = @id))
  set @result = '1'
  else
  set @result = '0'
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.SP_PHOTOADMIN_DELPHOTOMASTER
@id varchar(1000)
AS
SET NOCOUNT ON
declare @sql nvarchar(1100)
set @sql = 'update PHOTOADMIN_TAB set deleted = ''1'' where id in ('+@id+') '
EXECUTE sp_executesql @sql;
RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.dt_displayoaerror
@iObject int,
@iresult int
as

set nocount on

declare @vchOutput      varchar(255)
declare @hr             int
declare @vchSource      varchar(255)
declare @vchDescription varchar(255)

exec @hr = sp_OAGetErrorInfo @iObject, @vchSource OUT, @vchDescription OUT

select @vchOutput = @vchSource + ': ' + @vchDescription
raiserror (@vchOutput,16,-1)

return

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.dt_displayoaerror_u
@iObject int,
@iresult int
as

set nocount on

declare @vchOutput      nvarchar(255)
declare @hr             int
declare @vchSource      nvarchar(255)
declare @vchDescription nvarchar(255)

exec @hr = sp_OAGetErrorInfo @iObject, @vchSource OUT, @vchDescription OUT

select @vchOutput = @vchSource + ': ' + @vchDescription
raiserror (@vchOutput,16,-1)

return

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create proc dbo.dt_vcsenabled

set nocount on

declare @iObjectId int
select @iObjectId = 0

declare @VSSGUID varchar(100)
select @VSSGUID = 'SQLVersionControl.VCS_SQL'

    declare @iReturn int
    exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT
    if @iReturn <> 0 raiserror('', 16, -1) /* Can't Load Helper DLLC */

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*
** This procedure returns the version number of the stored
** procedures used by the Microsoft Visual Database Tools.
** Current version is 7.0.00.
*/
create procedure dbo.dt_verstamp006
as
select 7000

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_EditvipTimeout
@id bigint,
@timeout datetime
AS
SET NOCOUNT ON
--unuse

if exists (select * from USER_PHOTO_MAXSIZE_TAB where id=@id)
update dbo.USER_PHOTO_MAXSIZE_TAB set timeout = (@timeout) where id=@id
else
insert into USER_PHOTO_MAXSIZE_TAB (id,timeout) values (@id,@timeout)
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetFriendByUserID
@owner_id bigint,
@relation char(1),
@minRecord  varchar(20),
@maxRecord  varchar(20),
@friendCount  int output
AS
SET NOCOUNT ON

--取得用户好友
declare @sql1 nvarchar(2000);
declare @sql2 nvarchar(1000);
declare @wherestr nvarchar(2000);
declare @orderstr nvarchar(2000);
declare @min varchar(10);
set @friendCount = 0

select @friendCount=(count(distinct object_id)) from db_wayup..mapping_tab
where owner_id = @owner_id and relation='f'

if( (@minRecord !< @friendCount))
if(@friendCount > 0)
begin
  set @minRecord = (@friendCount - @maxRecord)
end
if(@minRecord<>0)
begin
  set @sql1='select top '+@maxRecord+ ' dmt.object_id, object_username,pat.photoid,user_name , filetype , filesize , title , hashid,
  pubtime , ifuse , Photostatus,isnull(bt.blogname,cast(object_id as nvarchar(100))+'''+'@'+''') as blogname from
  (select distinct object_id,object_username,set_time from db_wayup..mapping_tab
  where owner_id = '+cast(@owner_id as varchar(20))+' and relation='''+'f'+''') as dmt
  left join
  (select max(photoid) as photoid,id from palbum_tab where Photostatus in (1,2) and ifuse in (1,4) group by id) as pt
  on pt.id = dmt.object_id left join palbum_tab as pat on pt.photoid=pat.photoid left join
  (SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType = 1)) as bt on bt.UserID = dmt.object_id

  where dmt.set_time>
  (
   select max(set_time) from
   (
    select distinct set_time from db_wayup..mapping_tab
    where owner_id = '+cast(@owner_id as varchar(20))+' and relation='''+'f'+'''
    and object_id in
     (select top '+@minRecord+' dmtTab.object_id from
      (select distinct object_id,object_username,set_time from db_wayup..mapping_tab
      where owner_id ='+cast(@owner_id as varchar(20))+'
      and relation='''+'f'+''') as dmtTab order by
      dmtTab.set_time
      )
   ) as t1
  )
  order by dmt.set_time '

end
else
  set @sql1='select top '+@maxRecord+ ' dmt.object_id, object_username,pat.photoid,user_name , filetype , filesize , title , hashid,
   pubtime , ifuse , Photostatus,isnull(bt.blogname,cast(object_id as nvarchar(100))+'''+'@'+''') as blogname from
   (select distinct object_id,object_username,set_time from db_wayup..mapping_tab
   where owner_id = '+cast(@owner_id as varchar(20))+' and relation='''+'f'+''') as dmt
   left join
   (select max(photoid) as photoid,id from palbum_tab where Photostatus in (1,2) and   ifuse in (1,4) group by id) as pt
   on pt.id = dmt.object_id left join palbum_tab as pat on pt.photoid=pat.photoid left join
   (SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType = 1)) as bt on bt.UserID = dmt.object_id
   order by dmt.set_time '
--print @sql1
EXECUTE sp_executesql @sql1;

SELECT id, [user_name]
FROM DB_user..USER_TAB ut
WHERE (ut.id = @owner_id)

return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetPhotosByAllTags
@minRecord  varchar(20),
@maxRecord  varchar(20),
@tags   nvarchar(4000)
AS
SET NOCOUNT ON
---拥有所有标签的图片
declare @sql1 nvarchar(4000);
declare @sqlCount nvarchar(4000);

--循环拼凑
declare @strL varchar(4000)
declare @strR varchar(4000)
set @strR=@tags
set @strL = ''

declare @strwh varchar(4000)
declare @strResult varchar(4000)
set @strL = ''
set @strwh = ''
declare @i int
set @i = 0
while (CHARINDEX(' ',@strR)>0)
begin
  set @i = @i+1
  set @strL = @strL+'(SELECT photoid FROM PHOTO_TAG_TAB WHERE tagName='''
    +left(@strR,(CHARINDEX(' ',@strR)-1)) + ''' ) as t'+cast(@i as varchar(10))+', '
  set @strr = right(@strR,LEN(@strr)-(CHARINDEX(' ',@strR)))
  if(@i > 1)
   set @strwh = @strwh + ' t1.photoid = t'+cast(@i as varchar(10))+'.photoid and '
end
set @i = @i+1
set @strL = @strL+'(SELECT photoid FROM PHOTO_TAG_TAB WHERE tagName='''+@strR+''') as t'+cast(@i as varchar(10))

set @strwh = @strwh + ' t1.photoid = t'+cast(@i as varchar(10))+'.photoid '

set @strResult = 'select distinct t1.* from '+@strL +' where '+ @strwh

-- end 拼凑

--取得结果
set @sqlCount = 'select (count(distinct pt.photoid)) as count from palbum_tab as pt inner join
  ('+@strResult+')as ptt
  on pt.photoid = ptt.photoid
  where Photostatus =1 and ifuse in (1,4) '

EXECUTE sp_executesql @sqlCount;

--end 取得结果

if(@minRecord<>0)
begin
  set @sql1='select top '+@maxRecord+ ' PALBUM_TAB.photoid, PALBUM_TAB.id, PALBUM_TAB.user_name, hashid,
   PALBUM_TAB.filetype, PALBUM_TAB.filesize, PALBUM_TAB.title, PALBUM_TAB.intro, PALBUM_TAB.pubtime,
   PALBUM_TAB.pubip, PALBUM_TAB.width,
        PALBUM_TAB.height, PALBUM_TAB.ifuse, PALBUM_TAB.favoriteCount, PALBUM_TAB.Photostatus, PALBUM_TAB.commentsCount, PALBUM_TAB.indexed,
        PALBUM_TAB.viewCount, isnull(bt.blogname,cast(palbum_tab.id as nvarchar(100))+'''+'@'+''') as blogname
  from PALBUM_TAB inner join ('+@strResult+') as ptt
  on PALBUM_TAB.photoid = ptt.photoid

  left join (SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1)) as bt on bt.UserID=PALBUM_TAB.id

  where Photostatus =1 and ifuse in (1,4) and
  PALBUM_TAB.photoid< (select min(dptt.photoid)
  from ('+@strResult+') as dptt
  where dptt.photoid in
  (select top '+@minRecord+' dptt.photoid from
  ('+@strResult+') as dptt
   order by dptt.photoid desc ))
   order by PALBUM_TAB.photoid desc'
end
else
  set @sql1='select top '+@maxRecord+' PALBUM_TAB.photoid, PALBUM_TAB.id, PALBUM_TAB.user_name, hashid,
    PALBUM_TAB.filetype, PALBUM_TAB.filesize, PALBUM_TAB.title, PALBUM_TAB.intro, PALBUM_TAB.pubtime,
    PALBUM_TAB.pubip, PALBUM_TAB.width,
         PALBUM_TAB.height, PALBUM_TAB.ifuse, PALBUM_TAB.favoriteCount, PALBUM_TAB.Photostatus, PALBUM_TAB.commentsCount, PALBUM_TAB.indexed,
         PALBUM_TAB.viewCount, isnull(bt.blogname,cast(palbum_tab.id as nvarchar(100))+'''+'@'+''') as blogname
   from PALBUM_TAB inner join ('+@strResult+') as ptt
   on PALBUM_TAB.photoid = ptt.photoid

   left join (SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1)) as bt on bt.UserID=PALBUM_TAB.id

   where Photostatus =1 and ifuse in (1,4) order by PALBUM_TAB.photoid desc'

--print @sql1
EXECUTE sp_executesql @sql1;

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetPhotosByAnyTags
@minRecord  varchar(20),
@maxRecord  varchar(20),
@tags   nvarchar(4000)
AS
SET NOCOUNT ON

---拥有任意一标签的图片
declare @sql1 nvarchar(4000);
declare @sqlCount nvarchar(4000);

--循环拼凑
declare @strL varchar(4000)
declare @strR varchar(4000)
set @strR=@tags
set @strL = ''
while (CHARINDEX(' ',@strR)>0)
begin
  set @strL = @strL+'tagName='''+left(@strR,(CHARINDEX(' ',@strR)-1)) + ''' or '
  set @strr = right(@strR,LEN(@strr)-(CHARINDEX(' ',@strR)))
end
set @strL = @strL+'tagName='''+@strR+''''
-- end 拼凑

--取得结果
set @sqlCount = 'select (count(distinct pt.photoid)) as count from palbum_tab as pt inner join
  (SELECT DISTINCT PhotoID FROM PHOTO_TAG_TAB WHERE ('+@strL+') )as ptt
  on pt.photoid = ptt.photoid
  where Photostatus =1 and ifuse in (1,4)'

EXECUTE sp_executesql @sqlCount;

if(@minRecord<>0)
begin
  set @sql1='select top '+@maxRecord+ ' PALBUM_TAB.photoid, PALBUM_TAB.id, PALBUM_TAB.user_name, hashid,
   PALBUM_TAB.filetype, PALBUM_TAB.filesize, PALBUM_TAB.title, PALBUM_TAB.intro, PALBUM_TAB.pubtime,
   PALBUM_TAB.pubip, PALBUM_TAB.width,
        PALBUM_TAB.height, PALBUM_TAB.ifuse, PALBUM_TAB.favoriteCount, PALBUM_TAB.Photostatus, PALBUM_TAB.commentsCount, PALBUM_TAB.indexed,
        PALBUM_TAB.viewCount, isnull(bt.blogname,cast(palbum_tab.id as nvarchar(100))+'''+'@'+''') as blogname
  from PALBUM_TAB inner join (SELECT DISTINCT PhotoID FROM PHOTO_TAG_TAB WHERE ('+@strL+')) as ptt
  on PALBUM_TAB.photoid = ptt.photoid

  left join (SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1)) as bt on bt.UserID=PALBUM_TAB.id

  where Photostatus =1 and   ifuse in (1,4) and
  PALBUM_TAB.photoid< (select min(dptt.photoid)
  from (SELECT DISTINCT PhotoID FROM PHOTO_TAG_TAB WHERE ('+@strL+')) as dptt
  where dptt.photoid in
  (select top '+@minRecord+' dptt.photoid from
  (SELECT DISTINCT PhotoID FROM PHOTO_TAG_TAB WHERE ('+@strL+')) as dptt
   order by dptt.photoid desc ))
   order by PALBUM_TAB.photoid desc'
end
else
  set @sql1='select top '+@maxRecord+' PALBUM_TAB.photoid, PALBUM_TAB.id, PALBUM_TAB.user_name, hashid,
    PALBUM_TAB.filetype, PALBUM_TAB.filesize, PALBUM_TAB.title, PALBUM_TAB.intro, PALBUM_TAB.pubtime,
    PALBUM_TAB.pubip, PALBUM_TAB.width,
         PALBUM_TAB.height, PALBUM_TAB.ifuse, PALBUM_TAB.favoriteCount, PALBUM_TAB.Photostatus, PALBUM_TAB.commentsCount, PALBUM_TAB.indexed,
         PALBUM_TAB.viewCount, isnull(bt.blogname,cast(palbum_tab.id as nvarchar(100))+'''+'@'+''') as blogname
   from PALBUM_TAB inner join (SELECT DISTINCT PhotoID FROM PHOTO_TAG_TAB WHERE ('+@strL+')) as ptt
   on PALBUM_TAB.photoid = ptt.photoid

   left join (SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1)) as bt on bt.UserID=PALBUM_TAB.id

   where Photostatus =1 and   ifuse in (1,4) order by PALBUM_TAB.photoid desc'

--print @sql1
EXECUTE sp_executesql @sql1;

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.sp_GetPhotosByTagID
@tagid   bigint,
@minRecord  varchar(20),
@maxRecord  varchar(10)
AS
SET NOCOUNT ON

--取得某个标签下的图片--unuse
declare @sql1 nvarchar(3000);
declare @sql2 nvarchar(1000);
declare @wherestr nvarchar(200);
declare @orderstr nvarchar(200);
declare @min varchar(10);

set @wherestr=' inner join photo_tag_tab on photo_tag_tab.photoid = PALBUM_TAB.photoid
WHERE tagid ='+ @tagid+') ';

set @orderstr=' order by photoid desc'

set @sql2 = 'select '+@min+'= min(photoid) from photo_tag_tab where photoid in (select top '+@minRecord+' photoid from photo_tag_tab where tagid='+@tagid+@orderstr+' ) '
EXECUTE sp_executesql @sql2;

if(@minRecord<>0)
  set @sql1='select top '+@maxRecord+' PALBUM_TAB.photoid, PALBUM_TAB.id, PALBUM_TAB.user_name,
PALBUM_TAB.filetype, PALBUM_TAB.filesize, PALBUM_TAB.title, PALBUM_TAB.intro, PALBUM_TAB.pubtime,
PALBUM_TAB.pubip, PALBUM_TAB.width,
      PALBUM_TAB.height, PALBUM_TAB.ifuse, PALBUM_TAB.favoriteCount, PALBUM_TAB.Photostatus, PALBUM_TAB.commentsCount, PALBUM_TAB.indexed,
      PALBUM_TAB.viewCount
  from PALBUM_TAB as pt '+@wherestr+' and (pt.photoid< '+@min+') order by photoid desc'
else
  set @sql1='select top '+@maxRecord+' PALBUM_TAB.photoid, PALBUM_TAB.id, PALBUM_TAB.user_name,
PALBUM_TAB.filetype, PALBUM_TAB.filesize, PALBUM_TAB.title, PALBUM_TAB.intro, PALBUM_TAB.pubtime,
PALBUM_TAB.pubip, PALBUM_TAB.width,
      PALBUM_TAB.height, PALBUM_TAB.ifuse, PALBUM_TAB.favoriteCount, PALBUM_TAB.Photostatus, PALBUM_TAB.commentsCount, PALBUM_TAB.indexed,
      PALBUM_TAB.viewCount
    from PALBUM_TAB '+@wherestr+' order by photoid desc'

EXECUTE sp_executesql @sql1;
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.sp_GetPhotosByTagIDUserID
@tagid   bigint,
@id   bigint,
@minRecord  varchar(20),
@maxRecord  varchar(10)
AS
SET NOCOUNT ON

--取得某个用户在特定标签下的图片
declare @sql1 nvarchar(3000);
declare @sql2 nvarchar(1000);
declare @wherestr nvarchar(200);
declare @orderstr nvarchar(200);
declare @min varchar(10);

set @wherestr=' inner join photo_tag_tab on photo_tag_tab.photoid = PALBUM_TAB.photoid
          WHERE tagid = '+@tagid+')) and id = '+@id;

set @orderstr=' order by photoid desc'

set @sql2 = 'select '+@min+'= min(photoid) from photo_tag_tab where photoid in (select top '+@minRecord+' photoid from photo_tag_tab as ptt inner join PALBUM_TAB as pt on ptt.photoid=pt.photoid where ptt.tagid='+@tagid+@orderstr+' and pt.id='+@id+' order by photoid desc) '
EXECUTE sp_executesql @sql2;

if(@minRecord<>0)
  set @sql1='select top '+@maxRecord+' PALBUM_TAB.photoid, PALBUM_TAB.id, PALBUM_TAB.user_name,
PALBUM_TAB.filetype, PALBUM_TAB.filesize, PALBUM_TAB.title, PALBUM_TAB.intro, PALBUM_TAB.pubtime,
PALBUM_TAB.pubip, PALBUM_TAB.width,
      PALBUM_TAB.height, PALBUM_TAB.ifuse, PALBUM_TAB.favoriteCount, PALBUM_TAB.Photostatus, PALBUM_TAB.commentsCount, PALBUM_TAB.indexed,
      PALBUM_TAB.viewCount
  from PALBUM_TAB '+@wherestr +' and photoid<'+@min+' order by photoid desc'
else
  set @sql1='select top '+@maxRecord+' PALBUM_TAB.photoid, PALBUM_TAB.id, PALBUM_TAB.user_name,
PALBUM_TAB.filetype, PALBUM_TAB.filesize, PALBUM_TAB.title, PALBUM_TAB.intro, PALBUM_TAB.pubtime,
PALBUM_TAB.pubip, PALBUM_TAB.width,
      PALBUM_TAB.height, PALBUM_TAB.ifuse, PALBUM_TAB.favoriteCount, PALBUM_TAB.Photostatus, PALBUM_TAB.commentsCount, PALBUM_TAB.indexed,
      PALBUM_TAB.viewCount
    from PALBUM_TAB '+@wherestr+' order by photoid desc'

EXECUTE sp_executesql @sql1;
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetPhotosTopN
@topCount  varchar(20)
AS
SET NOCOUNT ON
--取得大家的所有图片top N
declare @sql1 nvarchar(2000);

set @sql1='select top '+@topCount+' PALBUM_TAB.photoid, PALBUM_TAB.id, PALBUM_TAB.user_name, hashid,
   PALBUM_TAB.filetype, PALBUM_TAB.filesize, PALBUM_TAB.title, PALBUM_TAB.intro, PALBUM_TAB.pubtime,
   PALBUM_TAB.pubip, PALBUM_TAB.width,
        PALBUM_TAB.height, PALBUM_TAB.ifuse, PALBUM_TAB.favoriteCount, PALBUM_TAB.Photostatus, PALBUM_TAB.commentsCount, PALBUM_TAB.indexed,
        PALBUM_TAB.viewCount, isnull(bt.blogname,cast(palbum_tab.id as nvarchar(100))+'''+'@'+''') as blogname
  from PALBUM_TAB

  left join (SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1)) as bt on bt.UserID=PALBUM_TAB.id

  where Photostatus =1 and ifuse = 4 order by PALBUM_TAB.photoid desc'
--print @sql1
EXECUTE sp_executesql @sql1;

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetPhotosbyIndex
@minRecord varchar(20),
@maxRecord varchar(10)
AS
SET NOCOUNT ON

--unuse
declare @sql1 nvarchar(1000);
declare @sql2 nvarchar(1000);
declare @wherestr nvarchar(200);
declare @orderstr nvarchar(200);

set @orderstr=' order by photoid desc'

if(@minRecord<>0)
  set @sql1='select top '+@maxRecord+' PALBUM_TAB.photoid, PALBUM_TAB.id, PALBUM_TAB.user_name,
   PALBUM_TAB.filetype, PALBUM_TAB.filesize, PALBUM_TAB.title, PALBUM_TAB.intro, PALBUM_TAB.pubtime,
   PALBUM_TAB.pubip, PALBUM_TAB.width,
        PALBUM_TAB.height, PALBUM_TAB.ifuse, PALBUM_TAB.favoriteCount, PALBUM_TAB.Photostatus, PALBUM_TAB.commentsCount, PALBUM_TAB.indexed,
        PALBUM_TAB.viewCount
  from PALBUM_TAB where photoid<(select min(photoid)
  from PALBUM_TAB where photoid in (select top '+@minRecord+' photoid from PALBUM_TAB '+
  @orderstr+') ) order by photoid desc'
else
  set @sql1='select top '+@maxRecord+' *
    from PALBUM_TAB
    order by photoid desc'

EXECUTE sp_executesql @sql1;

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetRelation
@owner_id bigint,
@object_id bigint,
@relation char(1) ,
@hasRelation int output
AS
--取得好友关系
SET NOCOUNT ON
set @hasRelation = 0
if exists(select * from db_wayup..mapping_tab where owner_id = @owner_id and [object_id]=@object_id and relation=@relation)
begin
set @hasRelation = 1
end
return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetTimeoutByid
@id bigint,
@timeout datetime out
AS
SET NOCOUNT ON
--unuse

set @timeout = getdate()
select @timeout=isnull(timeout,getdate()) from USER_PHOTO_MAXSIZE_TAB where id=@id

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_Getviptimeout
@id bigint
AS
SET NOCOUNT ON
select * from dbo.USER_PHOTO_MAXSIZE_TAB where id=@id
return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.SP_BANCOOKIE_ADDNEW
@id bigint,
@user_name varchar(40)
AS
SET NOCOUNT ON
insert into BANCOOKIE_TAB (id,user_name) values (@id,@user_name)

RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.SP_BANCOOKIE_CheckedByID
@id bigint,
@isBaned int output
AS
SET NOCOUNT ON
set @isBaned = 0
if exists (SELECT * FROM BANCOOKIE_TAB where id=@id)
set @isBaned = 1
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.SP_BANCOOKIE_GETINFO

AS
SET NOCOUNT ON
SELECT * FROM BANCOOKIE_TAB order by addtime

RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.SP_BANIP_ADDNEW
@ip varchar(19)
AS
SET NOCOUNT ON
insert into BANIP_TAB (ip) values (@ip)

RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.SP_BANIP_CheckedByIP
@ip varchar(19),
@isBaned int output
AS
SET NOCOUNT ON
set @isBaned =0
if exists (SELECT * FROM BANIP_TAB where ip = @ip)
set @isBaned = 1
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.SP_BANIP_GETINFO

AS
SET NOCOUNT ON
SELECT * FROM BANIP_TAB

RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.SP_BANUSER_ADDNEW
@id bigint,
@user_name varchar(40)
AS
SET NOCOUNT ON
insert into BANUSER_TAB (id,user_name) values (@id,@user_name)

RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.SP_BANUSER_CheckedByID
@id bigint,
@isBaned int output
AS
SET NOCOUNT ON
set @isBaned = 0
if exists (SELECT * FROM BANUSER_TAB where id=@id)
set @isBaned = 1
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.SP_BANUSER_GETINFO

AS
SET NOCOUNT ON
SELECT * FROM BANUSER_TAB

RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.SP_BOARDADMIN_CHECKSUPERMASTER
@id  int,
@result  char(1) output
AS
SET NOCOUNT ON
  if (exists(select id from PHOTOADMIN_TAB where type = '2' and id = @id))
  set @result = '1'
  else
  set @result = '0'
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.SP_PALBUM_INSERT
@id  bigint,
@user_name varchar(40),
@filetype varchar(20),
@filesize bigint,
@title  varchar(200),
@intro  text,
@pubip  varchar(20),
@width  int,
@height int,
@ifuse  int,
@pubtime varchar(19),
@Photostatus int,
@photoid bigint output
AS
SET NOCOUNT ON
--新增图片

declare @uploadmonth char(6),@curphotosize bigint

set @photoid=0
set @uploadmonth =SUBSTRING(@pubtime, 1, 4)+SUBSTRING(@pubtime, 6, 2)
select @curphotosize=photosize from photosize_tab where id=@id and uploadmonth=@uploadmonth

if(@curphotosize>104857600)
  begin
   return
  end
else if(@curphotosize>52428800)
  begin
   if(Not Exists(select t.id from db_wayup..vip_tab as t where t.id=@id and t.timeout>getdate()))
    return
  end
else if(@curphotosize>20971520)
  begin
   if(Not Exists(select id from photovip_tab where id=@id and timeout>getdate())) and
   (Not Exists(select t.id from db_wayup..vip_tab as t where t.id=@id and t.timeout>getdate()))
    return
  end

insert into palbum_tab(id,[user_name],filetype,filesize,title,intro,pubip,width,height,ifuse,pubtime,Photostatus)
values(@id,@user_name,@filetype,@filesize,@title,@intro,@pubip,@width,@height,@ifuse,@pubtime,@Photostatus)

set @photoid=SCOPE_IDENTITY()

if (exists(select id from photosize_tab where id = @id and uploadmonth=@uploadmonth))
  --已经上载过图片有记录
  begin
   update photosize_tab set photosize = photosize + @filesize,photocount = photocount + 1
   where id = @id and uploadmonth=@uploadmonth
  end
else
  --第一次上载图片
  begin
   insert into photosize_tab (id,uploadmonth,photosize,photocount) values (@id,@uploadmonth,@filesize,1)
  end

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

--更新图片HashID
CREATE PROCEDURE dbo.SP_PALBUM_UpdateHashID
@photoid bigint,
@hashid varchar(100)
AS
SET NOCOUNT ON

update palbum_tab set hashid = @hashid where photoid=@photoid

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.SP_PHOTOADMIN_ADDPHOTOMASTER
@id  int,
@username varchar(40)
AS
SET NOCOUNT ON
--增加管理员
if (exists(select id from PHOTOADMIN_TAB where id=@id))
  update PHOTOADMIN_TAB set deleted = '0' where id=@id
else
  INSERT PHOTOADMIN_TAB (id,user_name) values (@id,@username)
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.SP_PHOTOADMIN_CHECKADMIN
@id   bigint,
@result  char(1) OUTPUT
AS
--检查是否是管理员
SET NOCOUNT ON
declare @deleted char(1);
set @deleted='';
select @deleted=deleted from PHOTOADMIN_TAB where id=@id;
if(@deleted='0')
  set @result=1;
else
  set @result=0;
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.SP_PHOTOADMIN_GETALL
AS
SET NOCOUNT ON
--取得管理员
select * from PHOTOADMIN_TAB where deleted = '0'
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*
** Add an object to the dtproperties table
*/
create procedure dbo.dt_adduserobject
as
set nocount on
/*
** Create the user object if it does not exist already
*/
begin transaction
  insert dbo.dtproperties (property) VALUES ('DtgSchemaOBJECT')
  update dbo.dtproperties set objectid=@@identity
   where id=@@identity and property='DtgSchemaOBJECT'
commit
return @@identity

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create procedure dbo.dt_adduserobject_vcs
@vchProperty varchar(64)

set nocount on

declare @iReturn int
    /*
    ** Create the user object if it does not exist already
    */
    begin transaction
        select @iReturn = objectid from dbo.dtproperties where property = @vchProperty
        if @iReturn IS NULL
        begin
            insert dbo.dtproperties (property) VALUES (@vchProperty)
            update dbo.dtproperties set objectid=@@identity
                    where id=@@identity and property=@vchProperty
            select @iReturn = @@identity
        end
    commit
    return @iReturn

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create proc dbo.dt_checkinobject_u
    @chObjectType char(4),
    @vchObjectName nvarchar(255),
    @vchComment    nvarchar(255)='',
    @vchLoginName nvarchar(255),
    @vchPassword   nvarchar(255)='',
    @iVCSFlags     int = 0,
    @iActionFlag   int = 0,   /* 0 => AddFile, 1 => CheckIn */
    @txStream1     Text = '', /* There is a bug that if items are NULL they do not pass to OLE servers */
    @txStream2     Text = '',
    @txStream3     Text = ''

set nocount on

declare @iReturn int
declare @iObjectId int
select @iObjectId = 0

declare @VSSGUID nvarchar(100)
select @VSSGUID = N'SQLVersionControl.VCS_SQL'

declare @iPropertyObjectId int
select @iPropertyObjectId = 0

select @iPropertyObjectId = (select objectid from dbo.dtproperties where property = 'VCSProjectID')

    declare @vchProjectName   nvarchar(255)
    declare @vchSourceSafeINI nvarchar(255)
    declare @vchServerName    nvarchar(255)
    declare @vchDatabaseName nvarchar(255)
    exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSProject',       @vchProjectName   OUT
    exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSSourceSafeINI', @vchSourceSafeINI OUT
    exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSSQLServer',     @vchServerName    OUT
    exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSSQLDatabase',   @vchDatabaseName OUT

    if @chObjectType = 'PROC'
    begin
        if @iActionFlag = 1
        begin
            /* Procedure Can have up to three streams
            Drop Stream, Create Stream, GRANT stream */

begin tran compile_all

            /* try to compile the streams */
            exec (@txStream1)
            if @@error <> 0 GOTO E_Compile_Fail

exec (@txStream2)
if @@error <> 0 GOTO E_Compile_Fail

            exec (@txStream3)
            if @@error <> 0 GOTO E_Compile_Fail
        end

exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT
if @iReturn <> 0 GOTO E_OAError

        if @iActionFlag = 1
        begin
            exec @iReturn = sp_OAMethod @iObjectId,
                                        N'CheckIn_StoredProcedure',
                                        NULL,
                                        @sProjectName = @vchProjectName,
                                        @sSourceSafeINI = @vchSourceSafeINI,
                                        @sServerName = @vchServerName,
                                        @sDatabaseName = @vchDatabaseName,
                                        @sObjectName = @vchObjectName,
                                        @sComment = @vchComment,
                                        @sLoginName = @vchLoginName,
                                        @sPassword = @vchPassword,
                                        @iVCSFlags = @iVCSFlags,
                                        @iActionFlag = @iActionFlag,
                                        @sStream = @txStream2
        end
        else
        begin
            declare @iStreamObjectId int
            declare @iReturnValue int

exec @iReturn = sp_OAGetProperty @iObjectId, N'GetStreamObject', @iStreamObjectId OUT
if @iReturn <> 0 GOTO E_OAError

            select colid, text into #ProcLines
            from syscomments
            where id = object_id(@vchObjectName)
            order by colid

            declare @iCurProcLine int
            declare @iProcLines int
            select @iCurProcLine = 1
            select @iProcLines = (select count(*) from #ProcLines)
            while @iCurProcLine <= @iProcLines
            begin
                declare @pos int
                select @pos = 1
                declare @iCurLineSize int
                select @iCurLineSize = len((select text from #ProcLines where colid = @iCurProcLine))
                while @pos <= @iCurLineSize
                begin
                    declare @vchProcLinePiece nvarchar(255)
                    select @vchProcLinePiece = convert(nvarchar(255),
                        substring((select text from #ProcLines where colid = @iCurProcLine),
                                  @pos, 255 ))
                    exec @iReturn = sp_OAMethod @iStreamObjectId, N'AddStream', @iReturnValue OUT, @vchProcLinePiece
                    if @iReturn <> 0 GOTO E_OAError
                    select @pos = @pos + 255
                end
                select @iCurProcLine = @iCurProcLine + 1
            end
            drop table #ProcLines

            exec @iReturn = sp_OAMethod @iObjectId,
                                        N'CheckIn_StoredProcedure',
                                        NULL,
                                        @sProjectName = @vchProjectName,
                                        @sSourceSafeINI = @vchSourceSafeINI,
                                        @sServerName = @vchServerName,
                                        @sDatabaseName = @vchDatabaseName,
                                        @sObjectName = @vchObjectName,
                                        @sComment = @vchComment,
                                        @sLoginName = @vchLoginName,
                                        @sPassword = @vchPassword,
                                        @iVCSFlags = @iVCSFlags,
                                        @iActionFlag = @iActionFlag,
                                        @sStream = ''
        end

if @iReturn <> 0 GOTO E_OAError

        if @iActionFlag = 1
        begin
            commit tran compile_all
            if @@error <> 0 GOTO E_Compile_Fail
        end

end

CleanUp:
return

E_Compile_Fail:
    declare @lerror int
    select @lerror = @@error
    rollback tran compile_all
    RAISERROR (@lerror,16,-1)
    goto CleanUp

E_OAError:
    if @iActionFlag = 1 rollback tran compile_all
    exec dbo.dt_displayoaerror_u @iObjectId, @iReturn
    goto CleanUp

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create proc dbo.dt_checkoutobject_u
    @chObjectType char(4),
    @vchObjectName nvarchar(255),
    @vchComment    nvarchar(255),
    @vchLoginName nvarchar(255),
    @vchPassword   nvarchar(255),
    @iVCSFlags     int = 0,
    @iActionFlag   int = 0/* 0 => Checkout, 1 => GetLatest, 2 => UndoCheckOut */

set nocount on

declare @iReturn int
declare @iObjectId int
select @iObjectId =0

declare @VSSGUID nvarchar(100)
select @VSSGUID = N'SQLVersionControl.VCS_SQL'

declare @iReturnValue int
select @iReturnValue = 0

declare @vchTempText nvarchar(255)

/* this is for our strings */
declare @iStreamObjectId int
select @iStreamObjectId = 0

declare @iPropertyObjectId int
select @iPropertyObjectId = (select objectid from dbo.dtproperties where property = 'VCSProjectID')

    if @chObjectType = 'PROC'
    begin
        /* Procedure Can have up to three streams
           Drop Stream, Create Stream, GRANT stream */

exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT

if @iReturn <> 0 GOTO E_OAError

        exec @iReturn = sp_OAMethod @iObjectId,
                                    N'CheckOut_StoredProcedure',
                                    NULL,
                                    @sProjectName = @vchProjectName,
                                    @sSourceSafeINI = @vchSourceSafeINI,
                                    @sObjectName = @vchObjectName,
                                    @sServerName = @vchServerName,
                                    @sDatabaseName = @vchDatabaseName,
                                    @sComment = @vchComment,
                                    @sLoginName = @vchLoginName,
                                    @sPassword = @vchPassword,
                                    @iVCSFlags = @iVCSFlags,
                                    @iActionFlag = @iActionFlag

if @iReturn <> 0 GOTO E_OAError

exec @iReturn = sp_OAGetProperty @iObjectId, N'GetStreamObject', @iStreamObjectId OUT

if @iReturn <> 0 GOTO E_OAError

create table #commenttext (id int identity, sourcecode nvarchar(255))

        select @vchTempText = N'STUB'
        while @vchTempText IS NOT NULL
        begin
            exec @iReturn = sp_OAMethod @iStreamObjectId, N'GetStream', @iReturnValue OUT, @vchTempText OUT
            if @iReturn <> 0 GOTO E_OAError

if (@vchTempText IS NOT NULL) insert into #commenttext (sourcecode) select @vchTempText
end

select N'VCS'=sourcecode from #commenttext order by id
select N'SQL'=text from syscomments where id = object_id(@vchObjectName) order by colid

end

CleanUp:
return

E_OAError:
exec dbo.dt_displayoaerror_u @iObjectId, @iReturn
GOTO CleanUp

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*
** Drop one or all the associated properties of an object or an attribute
**
** dt_dropproperties objid, null or '' -- drop all properties of the object itself
** dt_dropproperties objid, property -- drop the property
*/
create procedure dbo.dt_droppropertiesbyid
@id int,
@property varchar(64)
as
set nocount on

if (@property is null) or (@property = '')
  delete from dbo.dtproperties where objectid=@id
else
  delete from dbo.dtproperties
   where objectid=@id and property=@property

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*
** Drop an object from the dbo.dtproperties table
*/
create procedure dbo.dt_dropuserobjectbyid
@id int
as
set nocount on
delete from dbo.dtproperties where objectid=@id

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*
** Generate an ansi name that is unique in the dtproperties.value column
*/
create procedure dbo.dt_generateansiname(@name varchar(255) output)
as
declare @prologue varchar(20)
declare @indexstring varchar(20)
declare @index integer

set @prologue = 'MSDT-A-'
set @index = 1

while 1 = 1
begin
  set @indexstring = cast(@index as varchar(20))
  set @name = @prologue + @indexstring
  if not exists (select value from dtproperties where value = @name)
   break

  set @index = @index + 1

  if (@index = 10000)
   goto TooMany
end

Leave:

return

TooMany:

set @name = 'DIAGRAM'
goto Leave

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*
** Retrieve the owner object(s) of a given property
*/
create procedure dbo.dt_getobjwithprop
@property varchar(30),
@value varchar(255)
as
set nocount on

if (@property is null) or (@property = '')
begin
raiserror('Must specify a property name.',-1,-1)
return (1)
end

if (@value is null)
select objectid id from dbo.dtproperties
where property=@property

else
select objectid id from dbo.dtproperties
where property=@property and value=@value

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*
** Retrieve the owner object(s) of a given property
*/
create procedure dbo.dt_getobjwithprop_u
@property varchar(30),
@uvalue nvarchar(255)
as
set nocount on

if (@property is null) or (@property = '')
begin
raiserror('Must specify a property name.',-1,-1)
return (1)
end

if (@uvalue is null)
select objectid id from dbo.dtproperties
where property=@property

else
select objectid id from dbo.dtproperties
where property=@property and uvalue=@uvalue

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*
** Retrieve properties by id's
**
** dt_getproperties objid, null or '' -- retrieve all properties of the object itself
** dt_getproperties objid, property -- retrieve the property specified
*/
create procedure dbo.dt_getpropertiesbyid
@id int,
@property varchar(64)
as
set nocount on

if (@property is null) or (@property = '')
  select property, version, value, lvalue
   from dbo.dtproperties
   where @id=objectid
else
  select property, version, value, lvalue
   from dbo.dtproperties
   where @id=objectid and @property=property

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if (@property is null) or (@property = '')
  select property, version, uvalue, lvalue
   from dbo.dtproperties
   where @id=objectid
else
  select property, version, uvalue, lvalue
   from dbo.dtproperties
   where @id=objectid and @property=property

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create procedure dbo.dt_getpropertiesbyid_vcs
    @id       int,
    @property varchar(64),
    @value    varchar(255) = NULL OUT

set nocount on

    select @value = (
        select value
                from dbo.dtproperties
                where @id=objectid and @property=property
                )

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create procedure dbo.dt_getpropertiesbyid_vcs_u
    @id       int,
    @property varchar(64),
    @value    nvarchar(255) = NULL OUT

set nocount on

    select @value = (
        select uvalue
                from dbo.dtproperties
                where @id=objectid and @property=property
                )

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*
** If the property already exists, reset the value; otherwise add property
**  id -- the id in sysobjects of the object
**  property -- the name of the property
**  value -- the text value of the property
**  lvalue -- the binary value of the property (image)
*/
create procedure dbo.dt_setpropertybyid
@id int,
@property varchar(64),
@value varchar(255),
@lvalue image
as
set nocount on
declare @uvalue nvarchar(255)
set @uvalue = convert(nvarchar(255), @value)
if exists (select * from dbo.dtproperties
   where objectid=@id and property=@property)
begin
  --
  -- bump the version count for this row as we update it
  --
  update dbo.dtproperties set value=@value, uvalue=@uvalue, lvalue=@lvalue, version=version+1
   where objectid=@id and property=@property
end
else
begin
  --
  -- version count is auto-set to 0 on initial insert
  --
  insert dbo.dtproperties (property, objectid, value, uvalue, lvalue)
   values (@property, @id, @value, @uvalue, @lvalue)
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_AddSection
@id bigint,
@SectionName varchar(100),
@intro text,
@sectionID bigint output
AS
SET NOCOUNT ON
--添加相册
insert into section_tab (id,sectionName,PhotoCount,intro) values
(@id,@SectionName,0,@intro)
select @sectionID= SCOPE_IDENTITY()
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_AddtoMonthPhotoSize
@id bigint,
@UploadMonth char(6),
@PhotoSize bigint ,
@PhotoCount bigint

AS
SET NOCOUNT ON
insert into PHOTOSIZE_TAB (id,UploadMonth,PhotoSize,PhotoCount)
values (@id,@uploadMonth,@photoSize,@photoCount)

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_Broadcast_UpdatebroadCast
@Content text
AS
SET NOCOUNT ON

--更新公告
if exists (select * from broadcast_tab )
begin
   update broadcast_tab
  set Content = @Content
end
else
  insert into broadcast_tab (Content) values (@Content)
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_Broadcast_getCast
AS
SET NOCOUNT ON
--取得公告
select Content from broadcast_tab
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetAllPhotosByID
@id  bigint,
@userPrivacy int
AS
--取得用户的所有图片
SET NOCOUNT ON
  if(@userPrivacy = 0)
   select photoid, id, user_name, filetype, filesize, title, intro, pubtime,
   pubip, width, height, ifuse,favoriteCount,
   Photostatus, commentsCount, indexed, viewCount from
   palbum_tab where id=@id and Photostatus in (0,1,2) and ifuse in (0,1,4) order by photoid desc
  else
  begin
   if(@userPrivacy = 1)
    select photoid, id, user_name, filetype, filesize, title, intro, pubtime,
    pubip, width, height, ifuse,favoriteCount,
    Photostatus, commentsCount, indexed, viewCount from
    palbum_tab where id=@id and Photostatus in (1,2) and ifuse in (1,4) order by photoid desc
   else
    select photoid, id, user_name, filetype, filesize, title, intro, pubtime,
    pubip, width, height, ifuse,favoriteCount,
    Photostatus, commentsCount, indexed, viewCount from palbum_tab
    where id=@id and Photostatus =1 and   ifuse in (1,4) order by photoid desc
  end
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetAllSectionsByID
@id bigint
AS
SET NOCOUNT ON

--取得用户所有相册
select SectionID, id, SectionName, PhotoCount, intro, CreateTime, photoID from section_tab as st
where st. id =@id
order by st.sectionid desc
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetCommendedUsersFavorite
AS
SET NOCOUNT ON

--取得推荐用户收藏top4
SELECT top 4 ut.id,user_name,
isnull(bt.blogname,cast(ut.id as nvarchar(100))+'@') as blogname,ct.*

  FROM DB_user..USER_TAB ut
left join
(SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1))
as bt on bt.UserID=ut.id

inner join
CommendedFavorite_Tab as ct on ut.id=ct.id
where   ifuse in (1,4)
order by CommendedOrder desc


RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetGoodSections
AS
SET NOCOUNT ON

--取得满足推荐条件的相册
SELECT st.SectionID, st.id, st.SectionName, st.PhotoCount, st.intro, st.CreateTime, st.photoID,
isnull(bt.blogname,cast(st.id as nvarchar(100))+'@') as blogname

  FROM dbo.SECTION_TAB st

  left join
  (SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1)) as bt
  on bt.UserID=st.id where st.photocount > 3
  ORDER BY st.photocount DESC

return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetIDbyPhotoID
@photoID bigint,
@id bigint out
AS
SET NOCOUNT ON

--取得图片的userid
set @id = 0
select @id=id from palbum_tab where photoid = @photoID

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetIDbySectionID
@sectionID bigint,
@id int output
AS
SET NOCOUNT ON

--取得某个相册的id
select @id=id from section_tab where sectionid=@sectionid

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetLastLoginTime
@id bigint,
@lastlogintime datetime output

AS
--取得上次登陆信息
SET NOCOUNT ON
set @lastlogintime = '2000-01-01'
IF EXISTS (SELECT * FROM LastLogin_Tab WHERE id = @id)
begin
     select @lastlogintime = lastlogintime from LastLogin_Tab WHERE id = @id
     UPDATE LastLogin_Tab set lastlogintime = getdate()   WHERE id = @id
end
ELSE
  INSERT LastLogin_Tab (id) VALUES (@id)
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.sp_GetMostCommentsPhotos
@minRecord  varchar(20),
@maxRecord  varchar(20),
@photoCount  int output
AS
SET NOCOUNT ON

--取得热评图片
declare @sql1 nvarchar(3000);
declare @sql2 nvarchar(1000);
declare @wherestr nvarchar(200);
declare @orderstr nvarchar(200);
declare @min varchar(10);
set @photoCount = 0

select @photoCount=(count(*)) from palbum_tab
where Photostatus =1 and ifuse in (1,4) and commentsCount > 0

if( (@minRecord !< @photoCount))
if(@photoCount > 0)
begin
set @minRecord = (@photoCount - @maxRecord)
end

if(@minRecord<>0)
begin
  set @sql1='select top '+@maxRecord+ ' PALBUM_TAB.photoid, PALBUM_TAB.id, PALBUM_TAB.user_name, hashid,
   PALBUM_TAB.filetype, PALBUM_TAB.filesize, PALBUM_TAB.title, PALBUM_TAB.intro, PALBUM_TAB.pubtime,
   PALBUM_TAB.pubip, PALBUM_TAB.width,
        PALBUM_TAB.height, PALBUM_TAB.ifuse, PALBUM_TAB.favoriteCount, PALBUM_TAB.Photostatus, PALBUM_TAB.commentsCount, PALBUM_TAB.indexed,
        PALBUM_TAB.viewCount,isnull(bt.blogname,cast(palbum_tab.id as nvarchar(100))+'''+'@'+''') as blogname
  from PALBUM_TAB
  left join (SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1)) as bt on bt.UserID=PALBUM_TAB.id

  where Photostatus =1 and ifuse in (1,4) and
  PALBUM_TAB.commentsCount <= (select min(palbum_tab.commentsCount) from palbum_tab
  where palbum_tab.photoid in
  (select top '+@minRecord+' palbum_tab.photoid from palbum_tab order by palbum_tab.commentsCount desc))
  and palbum_tab.photoid not in
  (select top '+@minRecord+' palbum_tab.photoid from palbum_tab order by palbum_tab.commentsCount desc)
  and commentsCount > 0
   order by PALBUM_TAB.commentsCount desc'
end
else
  set @sql1='select top 50 PALBUM_TAB.photoid, PALBUM_TAB.id, PALBUM_TAB.user_name,hashid,
    PALBUM_TAB.filetype, PALBUM_TAB.filesize, PALBUM_TAB.title, PALBUM_TAB.intro, PALBUM_TAB.pubtime,
    PALBUM_TAB.pubip, PALBUM_TAB.width,
         PALBUM_TAB.height, PALBUM_TAB.ifuse,PALBUM_TAB.favoriteCount, PALBUM_TAB.Photostatus, PALBUM_TAB.commentsCount, PALBUM_TAB.indexed,
         PALBUM_TAB.viewCount,isnull(bt.blogname,cast(palbum_tab.id as nvarchar(100))+'''+'@'+''') as blogname
   from PALBUM_TAB
   left join (SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1)) as bt on bt.UserID=PALBUM_TAB.id

   where Photostatus =1 and   ifuse in (1,4) and commentsCount > 0
   order by PALBUM_TAB.commentsCount desc'

EXECUTE sp_executesql @sql1;

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.sp_GetNearPhotosByPhotoID
@photoid  bigint,
@userPrivacy  int,
@max   smallint output,
@min   smallint output,
@photoCount  int output
AS
SET NOCOUNT ON
set @max = 0
set @min = 0
set @photoCount = 0

declare @id bigint
set @id = 0
select @id=id from palbum_tab where photoid=@photoid
--===========--上下图片的信息
if(@userPrivacy = 0)
begin
  SELECT @photoCount = (COUNT(DISTINCT photoid)) FROM dbo.palbum_tab where id=@id and Photostatus in (0,1,2) and ifuse in (0,1,4)

  if exists (select * from palbum_tab where photoid > @photoID and id = @id and Photostatus in (0,1,2) and ifuse in (0,1,4))
   set @max = 1
  if exists (select * from palbum_tab where photoid < @photoID and id = @id and Photostatus in (0,1,2) and ifuse in (0,1,4) )
   set @min = 1

  select top 1 id, photoid, user_name, hashid,filetype, filesize, title, intro, pubtime, ifuse,Photostatus from palbum_tab where photoid > @photoID and id = @id and Photostatus in (0,1,2) and ifuse in (0,1,4)
  union all
  select top 1 id, photoid, user_name,hashid, filetype, filesize, title, intro, pubtime, ifuse,Photostatus from palbum_tab where photoid =(select max (photoid) from palbum_tab
       where photoid<@photoID and id = @id and Photostatus in (0,1,2) and ifuse in (0,1,4))

end
else
begin
  if(@userPrivacy = 1)
  begin
   SELECT @photoCount = (COUNT(DISTINCT photoid)) FROM dbo.palbum_tab where id=@id and Photostatus in (1,2) and ifuse in (1,4)

   if exists (select * from palbum_tab where photoid > @photoID and id = @id and Photostatus in (1,2) and ifuse in (1,4))
    set @max = 1
   if exists (select * from palbum_tab where photoid < @photoID and id = @id and Photostatus in (1,2) and ifuse in (1,4))
    set @min = 1

   select top 1 id, photoid, user_name,hashid, filetype, filesize, title, intro, pubtime, ifuse,Photostatus from palbum_tab where photoid > @photoID and id = @id and Photostatus in (1,2) and   ifuse in (1,4)
   union all
    select top 1 id, photoid, user_name, hashid,filetype, filesize, title, intro, pubtime, ifuse,Photostatus from palbum_tab where photoid =(select max (photoid) from palbum_tab
       where photoid<@photoID and id = @id and Photostatus in (1,2) and ifuse in (1,4))

  end
  else
  begin
   SELECT @photoCount = (COUNT(DISTINCT photoid)) FROM dbo.palbum_tab where id=@id and Photostatus =1 and   ifuse in (1,4)

   if exists (select * from palbum_tab where photoid > @photoID and id = @id and Photostatus =1 and   ifuse in (1,4))
    set @max = 1
   if exists (select * from palbum_tab where photoid < @photoID and id = @id and Photostatus =1 and   ifuse in (1,4))
    set @min = 1

   select top 1 id, photoid, user_name, hashid,filetype, filesize, title, intro, pubtime, ifuse,Photostatus from palbum_tab where photoid > @photoID and id = @id and Photostatus =1 and   ifuse in (1,4)
   union all
   select top 1 id, photoid, user_name, hashid,filetype, filesize, title, intro, pubtime, ifuse,Photostatus from palbum_tab where photoid =(select max (photoid) from palbum_tab
       where photoid<@photoID and id = @id and Photostatus =1 and   ifuse in (1,4) )

end
end

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetPhotos
@minRecord  varchar(20),
@maxRecord  varchar(20),
@photoCount  int output
AS
SET NOCOUNT ON
--取得大家的所有图片
declare @sql1 nvarchar(1000);
declare @sql2 nvarchar(1000);
declare @wherestr nvarchar(200);
declare @orderstr nvarchar(200);
declare @min varchar(10);
set @photoCount = 0

select @photoCount=(count(*)) from palbum_tab
  where Photostatus =1 and ifuse = 4

if( (@minRecord !< @photoCount))
if(@photoCount > 0)
begin
set @minRecord = (@photoCount - @maxRecord)
end

if(@minRecord<>0)
begin
  set @sql1='select top '+@maxRecord+ ' PALBUM_TAB.photoid, PALBUM_TAB.id, PALBUM_TAB.user_name, hashid,
   PALBUM_TAB.filetype, PALBUM_TAB.filesize, PALBUM_TAB.title, PALBUM_TAB.intro, PALBUM_TAB.pubtime,
   PALBUM_TAB.pubip, PALBUM_TAB.width,
        PALBUM_TAB.height, PALBUM_TAB.ifuse, PALBUM_TAB.favoriteCount, PALBUM_TAB.Photostatus, PALBUM_TAB.commentsCount, PALBUM_TAB.indexed,
        PALBUM_TAB.viewCount, isnull(bt.blogname,cast(palbum_tab.id as nvarchar(100))+'''+'@'+''') as blogname
  from PALBUM_TAB

  left join (SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1)) as bt on bt.UserID=PALBUM_TAB.id

  where Photostatus =1 and ifuse = 4 and
  PALBUM_TAB.photoid< (select min(palbum_tab.photoid) from palbum_tab
  where palbum_tab.photoid in
  (select top '+@minRecord+' palbum_tab.photoid from palbum_tab where Photostatus =1 and ifuse = 4 order by palbum_tab.photoid desc ))
   order by PALBUM_TAB.photoid desc'
end
else
  set @sql1='select top '+@maxRecord+' PALBUM_TAB.photoid, PALBUM_TAB.id, PALBUM_TAB.user_name, hashid,
    PALBUM_TAB.filetype, PALBUM_TAB.filesize, PALBUM_TAB.title, PALBUM_TAB.intro, PALBUM_TAB.pubtime,
    PALBUM_TAB.pubip, PALBUM_TAB.width,
         PALBUM_TAB.height, PALBUM_TAB.ifuse, PALBUM_TAB.favoriteCount, PALBUM_TAB.Photostatus, PALBUM_TAB.commentsCount, PALBUM_TAB.indexed,
         PALBUM_TAB.viewCount, isnull(bt.blogname,cast(palbum_tab.id as nvarchar(100))+'''+'@'+''') as blogname
   from PALBUM_TAB

   left join (SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1)) as bt on bt.UserID=PALBUM_TAB.id

   where Photostatus =1 and ifuse = 4 order by PALBUM_TAB.photoid desc'
--print @sql1
EXECUTE sp_executesql @sql1;

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.sp_GetPhotosByID
@id  bigint,
@minRecord  varchar(20),
@maxRecord  varchar(20),
@photoCount  int output,
@curID   bigint
AS
--取得某个用户的图片
SET NOCOUNT ON

declare @sql1 nvarchar(1000);
declare @wherestr nvarchar(200);
declare @orderstr nvarchar(200);
declare @relation char(1)
declare @userPrivacy  bigint

set @userPrivacy = 2
set @photoCount = 0
if(@curID>0)
begin
  if(@curID = @id)
  begin
   set @userPrivacy = 0
  end
  else
  begin
   select @relation=relation from db_wayup..mapping_tab where owner_id = @id and [object_id]=@curID
   begin
    if(@relation = 'F')
     set @userPrivacy = 1
   end
  end

end

if(@userPrivacy = 0)
begin
  set @wherestr=' id ='+cast(@id as varchar(20)) + ' and Photostatus in (0,1,2) and ifuse in (0,1,4) '
  select @photoCount=(count(*)) from palbum_tab where id=@id and Photostatus in (0,1,2) and ifuse in (0,1,4)
end
else
begin
  if(@userPrivacy = 1)
  begin
   set @wherestr=' id ='+cast(@id as varchar(20))+ ' and Photostatus in (1,2) and ifuse in (1,4) '
   select @photoCount=(count(*)) from palbum_tab where id=@id and Photostatus in (1,2) and ifuse in (1,4)
  end
  else
  begin
   set @wherestr=' id ='+cast(@id as varchar(20))+' and Photostatus =1 and ifuse in (1,4) '
   select @photoCount=(count(*)) from palbum_tab where id=@id and Photostatus =1 and ifuse in (1,4)
  end
end

set @orderstr=' order by photoid desc '

if(@minRecord<>0 )
begin
  if( (@minRecord !< @photoCount))
  begin
   set @minRecord = @photoCount - @maxRecord
  end

  set @sql1='select top '+@maxRecord+' PALBUM_TAB.photoid, PALBUM_TAB.id, PALBUM_TAB.user_name, hashid,
    PALBUM_TAB.filetype, PALBUM_TAB.filesize, PALBUM_TAB.title, PALBUM_TAB.intro, PALBUM_TAB.pubtime,
    PALBUM_TAB.pubip, PALBUM_TAB.width,
         PALBUM_TAB.height, PALBUM_TAB.ifuse,
        PALBUM_TAB.favoriteCount, PALBUM_TAB.Photostatus, PALBUM_TAB.commentsCount, PALBUM_TAB.indexed,
         PALBUM_TAB.viewCount
  from PALBUM_TAB where photoid<(select min(photoid)
  from PALBUM_TAB where photoid in (select top '+@minRecord+' photoid from PALBUM_TAB where '+@wherestr+
  @orderstr+' ) ) and '+@wherestr+' order by photoid desc'
end
else
  set @sql1='select top '+@maxRecord+' PALBUM_TAB.photoid, PALBUM_TAB.id, PALBUM_TAB.user_name, hashid,
    PALBUM_TAB.filetype, PALBUM_TAB.filesize, PALBUM_TAB.title, PALBUM_TAB.intro, PALBUM_TAB.pubtime,
    PALBUM_TAB.pubip, PALBUM_TAB.width,
         PALBUM_TAB.height, PALBUM_TAB.ifuse, PALBUM_TAB.favoriteCount,
        PALBUM_TAB.Photostatus, PALBUM_TAB.commentsCount, PALBUM_TAB.indexed,
         PALBUM_TAB.viewCount
    from PALBUM_TAB where '+@wherestr+' order by photoid desc'

EXECUTE sp_executesql @sql1;

SELECT id, user_name
FROM DB_user..USER_TAB ut
WHERE (ut.id = @id)

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetPhotosByIDPhotoID
@photoid  bigint,
@id   bigint
AS
SET NOCOUNT ON

--取得某张图片
select photoid, id, user_name, cataid, filetype, filesize, title, intro, pubtime, pubip, width,
       height, ifuse, favoriteCount, Photostatus, commentsCount, indexed,
       viewCount from palbum_tab where photoid = @photoID and id=@id and Photostatus in (0,1,2) and ifuse in (0,1,4)

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetPhotosByPhotoID
@photoid  bigint
AS
SET NOCOUNT ON

--取得图片信息
select photoid, id, user_name, cataid, filetype, filesize, title, intro, pubtime, pubip, width, hashid,
       height, ifuse, set_time, set_id, favoriteCount, Photostatus, commentsCount, indexed,
       viewCount from palbum_tab where photoid = @photoID and Photostatus in (0,1,2) and ifuse in (0,1,4)

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetPhotosByifUse
@minRecord  varchar(20),
@maxRecord  varchar(20),
@ifuse  int,
@photoCount  int output
AS
SET NOCOUNT ON
--根据ifuse 信息取得图片
declare @sql1 nvarchar(1000);
declare @sql2 nvarchar(1000);
declare @wherestr nvarchar(200);
declare @orderstr nvarchar(200);
declare @min varchar(10);
set @photoCount = 0

select @photoCount=(count(*)) from palbum_tab
  where ifuse=@ifuse

if( (@minRecord !< @photoCount))
if(@photoCount > 0)
begin
set @minRecord = (@photoCount - @maxRecord)
end

if(@minRecord<>0)
begin
  set @sql1='select top '+@maxRecord+ ' PALBUM_TAB.photoid, PALBUM_TAB.id, PALBUM_TAB.user_name, hashid,
   PALBUM_TAB.filetype, PALBUM_TAB.filesize, PALBUM_TAB.title, PALBUM_TAB.intro, PALBUM_TAB.pubtime,
   PALBUM_TAB.pubip, PALBUM_TAB.width,
        PALBUM_TAB.height, PALBUM_TAB.ifuse, PALBUM_TAB.favoriteCount, PALBUM_TAB.Photostatus, PALBUM_TAB.commentsCount, PALBUM_TAB.indexed,
        PALBUM_TAB.viewCount, isnull(bt.blogname,cast(palbum_tab.id as nvarchar(100))+'''+'@'+''') as blogname
  from PALBUM_TAB

  left join (SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1)) as bt on bt.UserID=PALBUM_TAB.id

  where ifuse ='+ cast(@ifuse as varchar(20))+'and
  PALBUM_TAB.photoid< (select min(palbum_tab.photoid) from palbum_tab
  where palbum_tab.photoid in
  (select top '+@minRecord+' palbum_tab.photoid from palbum_tab where ifuse ='+ cast(@ifuse as varchar(20)) + ' order by palbum_tab.photoid desc ))
   order by PALBUM_TAB.photoid desc'
end
else
  set @sql1='select top '+@maxRecord+' PALBUM_TAB.photoid, PALBUM_TAB.id, PALBUM_TAB.user_name, hashid,
   PALBUM_TAB.filetype, PALBUM_TAB.filesize, PALBUM_TAB.title, PALBUM_TAB.intro, PALBUM_TAB.pubtime,
   PALBUM_TAB.pubip, PALBUM_TAB.width,
        PALBUM_TAB.height, PALBUM_TAB.ifuse, PALBUM_TAB.favoriteCount, PALBUM_TAB.Photostatus, PALBUM_TAB.commentsCount, PALBUM_TAB.indexed,
        PALBUM_TAB.viewCount, isnull(bt.blogname,cast(palbum_tab.id as nvarchar(100))+'''+'@'+''') as blogname
   from PALBUM_TAB

   left join (SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1)) as bt on bt.UserID=PALBUM_TAB.id

   where ifuse ='+ cast(@ifuse as varchar(20)) +' order by PALBUM_TAB.photoid desc'
--print @sql1
EXECUTE sp_executesql @sql1;

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetPhotosForAll
AS
SET NOCOUNT ON

--取得大家的最新的top4

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetPhotosForIndexPage
@id bigint
AS
SET NOCOUNT ON
--取得首页显示图片

select top 4 PALBUM_TAB.photoid, PALBUM_TAB.id, PALBUM_TAB.user_name,hashid,
PALBUM_TAB.filetype, PALBUM_TAB.filesize, PALBUM_TAB.title, PALBUM_TAB.intro, PALBUM_TAB.pubtime,
PALBUM_TAB.pubip, PALBUM_TAB.width,
      PALBUM_TAB.height, PALBUM_TAB.ifuse, PALBUM_TAB.favoriteCount, PALBUM_TAB.Photostatus, PALBUM_TAB.commentsCount, PALBUM_TAB.indexed,
      PALBUM_TAB.viewCount,isnull(bt.blogname,cast(palbum_tab.id as nvarchar(100))+'@') as blogname
from PALBUM_TAB left join
(SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1))
as bt on bt.UserID=PALBUM_TAB.id where PALBUM_TAB.id= @id and Photostatus in (0,1,2) and ifuse in (0,1,4)   order by photoid desc

select top 4 pt.photoid,user_name , filetype , filesize , title , hashid,
pubtime , ifuse , Photostatus,isnull(bt.blogname,cast(object_id as nvarchar(100))+'@') as blogname
from palbum_tab as pt inner join
(select distinct object_id,object_username from db_wayup..mapping_tab
where owner_id = @id and relation='f') as dmt
on pt.id = dmt.object_id left join
(SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType = 1)) as bt on bt.UserID = pt.id
where Photostatus in (1,2) and ifuse in (1,4)
order by pt.photoid desc

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE   PROCEDURE dbo.sp_GetRecomSections
AS
SET NOCOUNT ON
--取得推荐相册
SELECT ct.*,isnull(bt.blogname,cast(ct.id as nvarchar(100))+'@') as blogname
  from CommendedSection_Tab as ct
  left join
  (SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1)) as bt
  on bt.UserID=ct.id
  order by ifuse desc , CommendedOrder desc

return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetSectionInfobySectionID
@SectionID bigint,
@id bigint
AS
--取得相册信息
SET NOCOUNT ON
select SectionID, id, SectionName, PhotoCount, CreateTime, intro, photoID from section_tab where sectionID = @SectionID and id=@id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.sp_GetUpSizeByIDMonth
@id bigint,
@UploadMonth char(6),
@PhotoSize bigint output,
@PhotoCount bigint output

AS
SET NOCOUNT ON
--取得用户在当前月的上传数量
set @PhotoSize = 0
set @PhotoCount = 0

SELECT @PhotoSize=PhotoSize,@PhotoCount=PhotoCount
FROM dbo.PHOTOSIZE_TAB
WHERE (id = @id) AND (UploadMonth = @UploadMonth)
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetUserNewsetPhotos
@id  bigint
AS
SET NOCOUNT ON

--取得最新4张
select top 4
PALBUM_TAB.photoid, PALBUM_TAB.id, PALBUM_TAB.user_name,
PALBUM_TAB.filetype, PALBUM_TAB.filesize, PALBUM_TAB.title, PALBUM_TAB.intro, PALBUM_TAB.pubtime,
PALBUM_TAB.pubip, PALBUM_TAB.width,
       PALBUM_TAB.height, PALBUM_TAB.ifuse, PALBUM_TAB.favoriteCount, PALBUM_TAB.Photostatus, PALBUM_TAB.commentsCount, PALBUM_TAB.indexed,
       PALBUM_TAB.viewCount

,isnull(bt.blogname,cast(palbum_tab.id as nvarchar(100))+'@') as blogname
from PALBUM_TAB left join
(SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1))
as bt on bt.UserID=PALBUM_TAB.id
where ifuse in (0,1,4)
order by photoid desc
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetUsersHasMostFavorite
AS
SET NOCOUNT ON

--取得推荐收藏的用户信息
SELECT ut.id,user_name,
isnull(bt.blogname,cast(ut.id as nvarchar(100))+'@') as blogname,ct.*

FROM DB_user..USER_TAB ut
left join
(SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1))
as bt on bt.UserID=ut.id

inner join
CommendedFavorite_Tab as ct on ut.id=ct.id
order by CommendedOrder desc

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_UpdateCommendedFavoriteSections
@sectionID bigint,
@commendedorder int,
@ifuse int

AS
SET NOCOUNT ON

--更新推荐相册
--if exists(SELECT * FROM CommendedSection_Tab where commendedorder = @commendedorder )
--begin
-- update CommendedSection_Tab set commendedorder = 0 , ifuse=0
-- where commendedorder = @commendedorder
--end
declare @id bigint
declare @SectionName varchar(100)

if exists(SELECT * FROM CommendedSection_Tab where sectionID = @sectionID )
begin
  update CommendedSection_Tab set commendedorder = @commendedorder , ifuse=@ifuse
   where sectionID=@sectionID
end
else
begin
  select @id=id,@SectionName=SectionName from section_tab as st where sectionid=@sectionid

insert CommendedSection_Tab (id,commendedorder, ifuse,SectionName,sectionID)
values(@id,@commendedorder ,@ifuse,@SectionName,@sectionID)
end

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_UpdateCommendedFavoritedUsers
@id  bigint,
@commendedorder int,
@ifuse   int

AS
SET NOCOUNT ON

--更新推荐的用户收藏
--if exists(SELECT * FROM CommendedFavorite_Tab where commendedorder = @commendedorder )
--begin
-- update commendedFavorite_tab set commendedorder = 0 , ifuse=0
-- where commendedorder = @commendedorder
--end
if exists(SELECT * FROM CommendedFavorite_Tab where id = @id )
begin

  update commendedFavorite_tab set id=@id , commendedorder = @commendedorder , ifuse=@ifuse
   where id=@id
end
else
begin
  insert commendedFavorite_tab (id,commendedorder, ifuse) values(@id,@commendedorder ,@ifuse)
end

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_UpdateIfUseToOK
@photoid bigint,
@photoOKCount bigint output

AS
SET NOCOUNT ON
--通过图片并返回@photoOKCount以被blog使用
update
PALBUM_TAB
set ifuse=1
where photoid =@photoid

set @photoOKCount = 0
select @photoOKCount=count(*) from palbum_tab where id=(select id from palbum_tab where photoid=@photoid and ifuse in (1,4))

update
PALBUM_TAB
set indexed =2
where photoid =@photoid and indexed=1

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_UpdateMonthPhotoSize
@id bigint,
@UploadMonth char(6),
@PhotoSize bigint ,
@PhotoCount bigint

AS
SET NOCOUNT ON
--更新上传数量
update PHOTOSIZE_TAB set PhotoSize=@photoSize,PhotoCount=@photoCount
where id=@id and UploadMonth=@uploadMonth

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_UpdatePhotoFavoriteCount
@photoID bigint
AS
SET NOCOUNT ON
--更新收藏数量
update palbum_tab set favoritecount = (favoriteCount+1) where photoID = @photoID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_UpdatePhotoIntroByPhotoID
@photoid  bigint,
@id  bigint,
@intro   text
AS
SET NOCOUNT ON
--更新描述
update
PALBUM_TAB
set intro=@intro
where photoid =@photoid and id=@id

if(@@rowcount>0)
update
PALBUM_TAB
set indexed =2
where photoid =@photoid and indexed=1

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_UpdatePhotoTitleByPhotoID
@photoid  bigint,
@id  bigint,
@title   varchar(200)
AS
SET NOCOUNT ON
--更新图片title
update
PALBUM_TAB
set title=@title
where photoid =@photoid and id=@id

if(@@rowcount>0)
update
PALBUM_TAB
set indexed =2
where photoid =@photoid and indexed=1

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_UpdatePhotoViewedCount
@PhotoID bigint
AS
SET NOCOUNT ON
--更新viewed count
update palbum_tab
set viewCount = viewCount+1
where PhotoID = @PhotoID

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.sp_UpdatePhotosByPhotoID
@photoid  bigint,
@title   varchar(50),
@intro   varchar(600)
AS
SET NOCOUNT ON
--更新图片信息
update
PALBUM_TAB
set title=@title,intro=@intro
where photoid =@photoid

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_UpdatePhotosStatus
@photoid bigint,
@id bigint,
@Photostatus int

AS
SET NOCOUNT ON

--更新图片权限
update
PALBUM_TAB
set Photostatus=@Photostatus
where photoid =@photoid and id = @id

if(@@rowcount>0)
update
PALBUM_TAB
set indexed =2
where photoid =@photoid and indexed=1

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_UpdateSectionPhotoCountbySectionsID
@SectionsID bigint,
@PhotoCount bigint
AS
SET NOCOUNT ON
--unused

update SECTION_TAB
set PhotoCount = PhotoCount+ @PhotoCount
where SectionID = @SectionsID
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.sp_UpdateSectionbySectionID
@SectionID  bigint,
@id   bigint,
@sectionName varchar(100),
@intro  text
AS
--更新section
SET NOCOUNT ON

update SECTION_TAB
set sectionName = @sectionName,intro=@intro
where SectionID = @SectionID and id=@id

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.sp_getUploadSizeInfo
@id bigint,
@UploadMonth char(6),
@PhotoSize  bigint output,
@PhotoCount  bigint output,
@isVip   smallint output,
@IsWayUpVIP  char(1) OUTPUT

AS
SET NOCOUNT ON

--取得上传信息，是否是vip
set @PhotoCount = 0
set @PhotoSize = 0
set @isVip = 0
if exists (select * from dbo.PhotoVIP_TAB where id=@id and (timeout> getdate() ))
set @isVip = 1

SELECT @PhotoSize=PhotoSize,@PhotoCount=PhotoCount
FROM dbo.PHOTOSIZE_TAB
WHERE (id = @id) AND (UploadMonth = @UploadMonth)

set @IsWayUpVIP=0;
if(Exists(select id from DB_WayUp..vip_tab where id=@id and timeout>getdate()))
set @IsWayUpVIP=1;

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_updateIfUseToOKPublic
@photoid bigint,
@photoOKCount bigint output

AS
SET NOCOUNT ON
--通过图片并返回@photoOKCount以被blog使用
update
PALBUM_TAB
set ifuse=4
where photoid =@photoid

set @photoOKCount = 0
select @photoOKCount=count(*) from palbum_tab where id=(select id from palbum_tab where photoid=@photoid and ifuse=4)

update
PALBUM_TAB
set indexed =2
where photoid =@photoid and indexed=1

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create proc dbo.dt_addtosourcecontrol
    @vchSourceSafeINI varchar(255) = '',
    @vchProjectName   varchar(255) ='',
    @vchComment       varchar(255) ='',
    @vchLoginName     varchar(255) ='',
    @vchPassword      varchar(255) =''

set nocount on

declare @iReturn int
declare @iObjectId int
select @iObjectId = 0

declare @iStreamObjectId int
select @iStreamObjectId = 0

declare @VSSGUID varchar(100)
select @VSSGUID = 'SQLVersionControl.VCS_SQL'

declare @vchDatabaseName varchar(255)
select @vchDatabaseName = db_name()

declare @iReturnValue int
select @iReturnValue = 0

declare @iPropertyObjectId int
declare @vchParentId varchar(255)

declare @iObjectCount int
select @iObjectCount = 0

exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT
if @iReturn <> 0 GOTO E_OAError

    /* Create Project in SS */
    exec @iReturn = sp_OAMethod @iObjectId,
                                'AddProjectToSourceSafe',
                                NULL,
                                @vchSourceSafeINI,
                                @vchProjectName output,
                                @@SERVERNAME,
                                @vchDatabaseName,
                                @vchLoginName,
                                @vchPassword,
                                @vchComment

if @iReturn <> 0 GOTO E_OAError

exec @iReturn = sp_OAGetProperty @iObjectId, 'GetStreamObject', @iStreamObjectId OUT

if @iReturn <> 0 GOTO E_OAError

/* Set Database Properties */

begin tran SetProperties

/* add high level object */

exec @iPropertyObjectId = dbo.dt_adduserobject_vcs 'VCSProjectID'

select @vchParentId = CONVERT(varchar(255),@iPropertyObjectId)

    exec dbo.dt_setpropertybyid @iPropertyObjectId, 'VCSProjectID', @vchParentId , NULL
    exec dbo.dt_setpropertybyid @iPropertyObjectId, 'VCSProject' , @vchProjectName , NULL
    exec dbo.dt_setpropertybyid @iPropertyObjectId, 'VCSSourceSafeINI' , @vchSourceSafeINI , NULL
    exec dbo.dt_setpropertybyid @iPropertyObjectId, 'VCSSQLServer', @@SERVERNAME, NULL
    exec dbo.dt_setpropertybyid @iPropertyObjectId, 'VCSSQLDatabase', @vchDatabaseName, NULL

if @@error <> 0 GOTO E_General_Error

commit tran SetProperties

    declare cursorProcNames cursor for
        select convert(varchar(255), name) from sysobjects where type = 'P' and name not like 'dt_%'
    open cursorProcNames

    while 1 = 1
    begin
        declare @vchProcName varchar(255)
        fetch next from cursorProcNames into @vchProcName
        if @@fetch_status <> 0
            break

        select colid, text into #ProcLines
        from syscomments
        where id = object_id(@vchProcName)
        order by colid

        declare @iCurProcLine int
        declare @iProcLines int
        select @iCurProcLine = 1
        select @iProcLines = (select count(*) from #ProcLines)
        while @iCurProcLine <= @iProcLines
        begin
            declare @pos int
            select @pos = 1
            declare @iCurLineSize int
            select @iCurLineSize = len((select text from #ProcLines where colid = @iCurProcLine))
            while @pos <= @iCurLineSize
            begin
                declare @vchProcLinePiece varchar(255)
                select @vchProcLinePiece = convert(varchar(255),
                    substring((select text from #ProcLines where colid = @iCurProcLine),
                              @pos, 255 ))
                exec @iReturn = sp_OAMethod @iStreamObjectId, 'AddStream', @iReturnValue OUT, @vchProcLinePiece
                if @iReturn <> 0 GOTO E_OAError
                select @pos = @pos + 255
            end
            select @iCurProcLine = @iCurProcLine + 1
        end
        drop table #ProcLines

        exec @iReturn = sp_OAMethod @iObjectId,
                                    'CheckIn_StoredProcedure',
                                    NULL,
                                    @sProjectName = @vchProjectName,
                                    @sSourceSafeINI = @vchSourceSafeINI,
                                    @sServerName = @@SERVERNAME,
                                    @sDatabaseName = @vchDatabaseName,
                                    @sObjectName = @vchProcName,
                                    @sComment = @vchComment,
                                    @sLoginName = @vchLoginName,
                                    @sPassword = @vchPassword,
                                    @iVCSFlags = 0,
                                    @iActionFlag = 0,
                                    @sStream = ''

if @iReturn = 0 select @iObjectCount = @iObjectCount + 1

end

CleanUp:
close cursorProcNames
deallocate cursorProcNames
    select @vchProjectName
    select @iObjectCount
    return

E_General_Error:
/* this is an all or nothing. No specific error messages */
goto CleanUp

E_OAError:
exec dbo.dt_displayoaerror @iObjectId, @iReturn
goto CleanUp

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create proc dbo.dt_checkinobject
    @chObjectType char(4),
    @vchObjectName varchar(255),
    @vchComment    varchar(255)='',
    @vchLoginName varchar(255),
    @vchPassword   varchar(255)='',
    @iVCSFlags     int = 0,
    @iActionFlag   int = 0,   /* 0 => AddFile, 1 => CheckIn */
    @txStream1     Text = '', /* There is a bug that if items are NULL they do not pass to OLE servers */
    @txStream2     Text = '',
    @txStream3     Text = ''

set nocount on

declare @iReturn int
declare @iObjectId int
select @iObjectId = 0

declare @VSSGUID varchar(100)
select @VSSGUID = 'SQLVersionControl.VCS_SQL'

declare @iPropertyObjectId int
select @iPropertyObjectId = 0

select @iPropertyObjectId = (select objectid from dbo.dtproperties where property = 'VCSProjectID')

    declare @vchProjectName   varchar(255)
    declare @vchSourceSafeINI varchar(255)
    declare @vchServerName    varchar(255)
    declare @vchDatabaseName varchar(255)
    exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSProject',       @vchProjectName   OUT
    exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSSourceSafeINI', @vchSourceSafeINI OUT
    exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSSQLServer',     @vchServerName    OUT
    exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSSQLDatabase',   @vchDatabaseName OUT

    if @chObjectType = 'PROC'
    begin
        if @iActionFlag = 1
        begin
            /* Procedure Can have up to three streams
            Drop Stream, Create Stream, GRANT stream */

begin tran compile_all

            /* try to compile the streams */
            exec (@txStream1)
            if @@error <> 0 GOTO E_Compile_Fail

exec (@txStream2)
if @@error <> 0 GOTO E_Compile_Fail

            exec (@txStream3)
            if @@error <> 0 GOTO E_Compile_Fail
        end

exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT
if @iReturn <> 0 GOTO E_OAError

        if @iActionFlag = 1
        begin
            exec @iReturn = sp_OAMethod @iObjectId,
                                        'CheckIn_StoredProcedure',
                                        NULL,
                                        @sProjectName = @vchProjectName,
                                        @sSourceSafeINI = @vchSourceSafeINI,
                                        @sServerName = @vchServerName,
                                        @sDatabaseName = @vchDatabaseName,
                                        @sObjectName = @vchObjectName,
                                        @sComment = @vchComment,
                                        @sLoginName = @vchLoginName,
                                        @sPassword = @vchPassword,
                                        @iVCSFlags = @iVCSFlags,
                                        @iActionFlag = @iActionFlag,
                                        @sStream = @txStream2
        end
        else
        begin
            declare @iStreamObjectId int
            declare @iReturnValue int

exec @iReturn = sp_OAGetProperty @iObjectId, 'GetStreamObject', @iStreamObjectId OUT
if @iReturn <> 0 GOTO E_OAError

            select colid, text into #ProcLines
            from syscomments
            where id = object_id(@vchObjectName)
            order by colid

            declare @iCurProcLine int
            declare @iProcLines int
            select @iCurProcLine = 1
            select @iProcLines = (select count(*) from #ProcLines)
            while @iCurProcLine <= @iProcLines
            begin
                declare @pos int
                select @pos = 1
                declare @iCurLineSize int
                select @iCurLineSize = len((select text from #ProcLines where colid = @iCurProcLine))
                while @pos <= @iCurLineSize
                begin
                    declare @vchProcLinePiece varchar(255)
                    select @vchProcLinePiece = convert(varchar(255),
                        substring((select text from #ProcLines where colid = @iCurProcLine),
                                  @pos, 255 ))
                    exec @iReturn = sp_OAMethod @iStreamObjectId, 'AddStream', @iReturnValue OUT, @vchProcLinePiece
                    if @iReturn <> 0 GOTO E_OAError
                    select @pos = @pos + 255
                end
                select @iCurProcLine = @iCurProcLine + 1
            end
            drop table #ProcLines

            exec @iReturn = sp_OAMethod @iObjectId,
                                        'CheckIn_StoredProcedure',
                                        NULL,
                                        @sProjectName = @vchProjectName,
                                        @sSourceSafeINI = @vchSourceSafeINI,
                                        @sServerName = @vchServerName,
                                        @sDatabaseName = @vchDatabaseName,
                                        @sObjectName = @vchObjectName,
                                        @sComment = @vchComment,
                                        @sLoginName = @vchLoginName,
                                        @sPassword = @vchPassword,
                                        @iVCSFlags = @iVCSFlags,
                                        @iActionFlag = @iActionFlag,
                                        @sStream = ''
        end

if @iReturn <> 0 GOTO E_OAError

        if @iActionFlag = 1
        begin
            commit tran compile_all
            if @@error <> 0 GOTO E_Compile_Fail
        end

end

CleanUp:
return

E_Compile_Fail:
    declare @lerror int
    select @lerror = @@error
    rollback tran compile_all
    RAISERROR (@lerror,16,-1)
    goto CleanUp

E_OAError:
    if @iActionFlag = 1 rollback tran compile_all
    exec dbo.dt_displayoaerror @iObjectId, @iReturn
    goto CleanUp

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create proc dbo.dt_checkoutobject
    @chObjectType char(4),
    @vchObjectName varchar(255),
    @vchComment    varchar(255),
    @vchLoginName varchar(255),
    @vchPassword   varchar(255),
    @iVCSFlags     int = 0,
    @iActionFlag   int = 0/* 0 => Checkout, 1 => GetLatest, 2 => UndoCheckOut */

set nocount on

declare @iReturn int
declare @iObjectId int
select @iObjectId =0

declare @VSSGUID varchar(100)
select @VSSGUID = 'SQLVersionControl.VCS_SQL'

declare @iReturnValue int
select @iReturnValue = 0

declare @vchTempText varchar(255)

/* this is for our strings */
declare @iStreamObjectId int
select @iStreamObjectId = 0

declare @iPropertyObjectId int
select @iPropertyObjectId = (select objectid from dbo.dtproperties where property = 'VCSProjectID')

    if @chObjectType = 'PROC'
    begin
        /* Procedure Can have up to three streams
           Drop Stream, Create Stream, GRANT stream */

exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT

if @iReturn <> 0 GOTO E_OAError

        exec @iReturn = sp_OAMethod @iObjectId,
                                    'CheckOut_StoredProcedure',
                                    NULL,
                                    @sProjectName = @vchProjectName,
                                    @sSourceSafeINI = @vchSourceSafeINI,
                                    @sObjectName = @vchObjectName,
                                    @sServerName = @vchServerName,
                                    @sDatabaseName = @vchDatabaseName,
                                    @sComment = @vchComment,
                                    @sLoginName = @vchLoginName,
                                    @sPassword = @vchPassword,
                                    @iVCSFlags = @iVCSFlags,
                                    @iActionFlag = @iActionFlag

if @iReturn <> 0 GOTO E_OAError

exec @iReturn = sp_OAGetProperty @iObjectId, 'GetStreamObject', @iStreamObjectId OUT

if @iReturn <> 0 GOTO E_OAError

create table #commenttext (id int identity, sourcecode varchar(255))

        select @vchTempText = 'STUB'
        while @vchTempText IS NOT NULL
        begin
            exec @iReturn = sp_OAMethod @iStreamObjectId, 'GetStream', @iReturnValue OUT, @vchTempText OUT
            if @iReturn <> 0 GOTO E_OAError

if (@vchTempText IS NOT NULL) insert into #commenttext (sourcecode) select @vchTempText
end

select 'VCS'=sourcecode from #commenttext order by id
select 'SQL'=text from syscomments where id = object_id(@vchObjectName) order by colid

end

CleanUp:
return

E_OAError:
exec dbo.dt_displayoaerror @iObjectId, @iReturn
GOTO CleanUp

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create proc dbo.dt_isundersourcecontrol
    @vchLoginName varchar(255) = '',
    @vchPassword varchar(255) = '',
    @iWhoToo      int = 0 /* 0 => Just check project; 1 => get list of objs */

set nocount on

declare @iReturn int
declare @iObjectId int
select @iObjectId = 0

declare @VSSGUID varchar(100)
select @VSSGUID = 'SQLVersionControl.VCS_SQL'

declare @iReturnValue int
select @iReturnValue = 0

declare @iStreamObjectId int
select @iStreamObjectId = 0

declare @vchTempText varchar(255)

declare @iPropertyObjectId int
select @iPropertyObjectId = (select objectid from dbo.dtproperties where property = 'VCSProjectID')

    if (@vchProjectName IS NULL) or (@vchSourceSafeINI IS NULL) or (@vchServerName IS NULL) or (@vchDatabaseName IS NULL)
    begin
        RAISERROR('Not Under Source Control',16,-1)
        return
    end

if @iWhoToo = 1
begin

        /* Get List of Procs in the project */
        exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT
        if @iReturn <> 0 GOTO E_OAError

        exec @iReturn = sp_OAMethod @iObjectId,
                                    'GetListOfObjects',
                                    NULL,
                                    @vchProjectName,
                                    @vchSourceSafeINI,
                                    @vchServerName,
                                    @vchDatabaseName,
                                    @vchLoginName,
                                    @vchPassword

if @iReturn <> 0 GOTO E_OAError

exec @iReturn = sp_OAGetProperty @iObjectId, 'GetStreamObject', @iStreamObjectId OUT

if @iReturn <> 0 GOTO E_OAError

create table #ObjectList (id int identity, vchObjectlist varchar(255))

if (@vchTempText IS NOT NULL) insert into #ObjectList (vchObjectlist ) select @vchTempText
end

select vchObjectlist from #ObjectList order by id
end

CleanUp:
return

E_OAError:
exec dbo.dt_displayoaerror @iObjectId, @iReturn
goto CleanUp

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create proc dbo.dt_isundersourcecontrol_u
    @vchLoginName nvarchar(255) = '',
    @vchPassword nvarchar(255) = '',
    @iWhoToo      int = 0 /* 0 => Just check project; 1 => get list of objs */

set nocount on

declare @iReturn int
declare @iObjectId int
select @iObjectId = 0

declare @VSSGUID nvarchar(100)
select @VSSGUID = N'SQLVersionControl.VCS_SQL'

declare @iReturnValue int
select @iReturnValue = 0

declare @iStreamObjectId int
select @iStreamObjectId = 0

declare @vchTempText nvarchar(255)

declare @iPropertyObjectId int
select @iPropertyObjectId = (select objectid from dbo.dtproperties where property = 'VCSProjectID')

    if (@vchProjectName IS NULL) or (@vchSourceSafeINI IS NULL) or (@vchServerName IS NULL) or (@vchDatabaseName IS NULL)
    begin
        RAISERROR(N'Not Under Source Control',16,-1)
        return
    end

if @iWhoToo = 1
begin

        /* Get List of Procs in the project */
        exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT
        if @iReturn <> 0 GOTO E_OAError

        exec @iReturn = sp_OAMethod @iObjectId,
                                    N'GetListOfObjects',
                                    NULL,
                                    @vchProjectName,
                                    @vchSourceSafeINI,
                                    @vchServerName,
                                    @vchDatabaseName,
                                    @vchLoginName,
                                    @vchPassword

if @iReturn <> 0 GOTO E_OAError

exec @iReturn = sp_OAGetProperty @iObjectId, N'GetStreamObject', @iStreamObjectId OUT

if @iReturn <> 0 GOTO E_OAError

create table #ObjectList (id int identity, vchObjectlist nvarchar(255))

if (@vchTempText IS NOT NULL) insert into #ObjectList (vchObjectlist ) select @vchTempText
end

select vchObjectlist from #ObjectList order by id
end

CleanUp:
return

E_OAError:
exec dbo.dt_displayoaerror_u @iObjectId, @iReturn
goto CleanUp

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create procedure dbo.dt_removefromsourcecontrol

set nocount on

declare @iPropertyObjectId int
select @iPropertyObjectId = (select objectid from dbo.dtproperties where property = 'VCSProjectID')

exec dbo.dt_droppropertiesbyid @iPropertyObjectId, null

/* -1 is returned by dt_droppopertiesbyid */
if @@error <> 0 and @@error <> -1 return 1

return 0

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*
** If the property already exists, reset the value; otherwise add property
**  id -- the id in sysobjects of the object
**  property -- the name of the property
**  uvalue -- the text value of the property
**  lvalue -- the binary value of the property (image)
*/
create procedure dbo.dt_setpropertybyid_u
@id int,
@property varchar(64),
@uvalue nvarchar(255),
@lvalue image
as
set nocount on
--
-- If we are writing the name property, find the ansi equivalent.
-- If there is no lossless translation, generate an ansi name.
--
declare @avalue varchar(255)
set @avalue = null
if (@uvalue is not null)
begin
  if (convert(nvarchar(255), convert(varchar(255), @uvalue)) = @uvalue)
  begin
   set @avalue = convert(varchar(255), @uvalue)
  end
  else
  begin
   if 'DtgSchemaNAME' = @property
   begin
    exec dbo.dt_generateansiname @avalue output
   end
  end
end
if exists (select * from dbo.dtproperties
   where objectid=@id and property=@property)
begin
  --
  -- bump the version count for this row as we update it
  --
  update dbo.dtproperties set value=@avalue, uvalue=@uvalue, lvalue=@lvalue, version=version+1
   where objectid=@id and property=@property
end
else
begin
  --
  -- version count is auto-set to 0 on initial insert
  --
  insert dbo.dtproperties (property, objectid, value, uvalue, lvalue)
   values (@property, @id, @avalue, @uvalue, @lvalue)
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create proc dbo.dt_validateloginparams
@vchLoginName varchar(255),
@vchPassword varchar(255)
as

set nocount on

declare @iReturn int
declare @iObjectId int
select @iObjectId =0

declare @VSSGUID varchar(100)
select @VSSGUID = 'SQLVersionControl.VCS_SQL'

declare @iPropertyObjectId int
select @iPropertyObjectId = (select objectid from dbo.dtproperties where property = 'VCSProjectID')

declare @vchSourceSafeINI varchar(255)
exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSSourceSafeINI', @vchSourceSafeINI OUT

exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT
if @iReturn <> 0 GOTO E_OAError

    exec @iReturn = sp_OAMethod @iObjectId,
                                'ValidateLoginParams',
                                NULL,
                                @sSourceSafeINI = @vchSourceSafeINI,
                                @sLoginName = @vchLoginName,
                                @sPassword = @vchPassword
    if @iReturn <> 0 GOTO E_OAError

CleanUp:
return

E_OAError:
exec dbo.dt_displayoaerror @iObjectId, @iReturn
GOTO CleanUp

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create proc dbo.dt_validateloginparams_u
@vchLoginName nvarchar(255),
@vchPassword nvarchar(255)
as

set nocount on

declare @iReturn int
declare @iObjectId int
select @iObjectId =0

declare @VSSGUID nvarchar(100)
select @VSSGUID = N'SQLVersionControl.VCS_SQL'

declare @iPropertyObjectId int
select @iPropertyObjectId = (select objectid from dbo.dtproperties where property = 'VCSProjectID')

declare @vchSourceSafeINI nvarchar(255)
exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSSourceSafeINI', @vchSourceSafeINI OUT

exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT
if @iReturn <> 0 GOTO E_OAError

    exec @iReturn = sp_OAMethod @iObjectId,
                                N'ValidateLoginParams',
                                NULL,
                                @sSourceSafeINI = @vchSourceSafeINI,
                                @sLoginName = @vchLoginName,
                                @sPassword = @vchPassword
    if @iReturn <> 0 GOTO E_OAError

CleanUp:
return

E_OAError:
exec dbo.dt_displayoaerror_u @iObjectId, @iReturn
GOTO CleanUp

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create proc dbo.dt_whocheckedout
        @chObjectType char(4),
        @vchObjectName varchar(255),
        @vchLoginName varchar(255),
        @vchPassword   varchar(255)

set nocount on

declare @iReturn int
declare @iObjectId int
select @iObjectId =0

declare @VSSGUID varchar(100)
select @VSSGUID = 'SQLVersionControl.VCS_SQL'

declare @iPropertyObjectId int

select @iPropertyObjectId = (select objectid from dbo.dtproperties where property = 'VCSProjectID')

    if @chObjectType = 'PROC'
    begin
        exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT

if @iReturn <> 0 GOTO E_OAError

declare @vchReturnValue varchar(255)
select @vchReturnValue = ''

        exec @iReturn = sp_OAMethod @iObjectId,
                                    'WhoCheckedOut',
                                    @vchReturnValue OUT,
                                    @sProjectName = @vchProjectName,
                                    @sSourceSafeINI = @vchSourceSafeINI,
                                    @sObjectName = @vchObjectName,
                                    @sServerName = @vchServerName,
                                    @sDatabaseName = @vchDatabaseName,
                                    @sLoginName = @vchLoginName,
                                    @sPassword = @vchPassword

if @iReturn <> 0 GOTO E_OAError

select @vchReturnValue

end

CleanUp:
return

E_OAError:
exec dbo.dt_displayoaerror @iObjectId, @iReturn
GOTO CleanUp

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create proc dbo.dt_whocheckedout_u
        @chObjectType char(4),
        @vchObjectName nvarchar(255),
        @vchLoginName nvarchar(255),
        @vchPassword   nvarchar(255)

set nocount on

declare @iReturn int
declare @iObjectId int
select @iObjectId =0

declare @VSSGUID nvarchar(100)
select @VSSGUID = N'SQLVersionControl.VCS_SQL'

declare @iPropertyObjectId int

select @iPropertyObjectId = (select objectid from dbo.dtproperties where property = 'VCSProjectID')

    if @chObjectType = 'PROC'
    begin
        exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT

if @iReturn <> 0 GOTO E_OAError

declare @vchReturnValue nvarchar(255)
select @vchReturnValue = ''

        exec @iReturn = sp_OAMethod @iObjectId,
                                    N'WhoCheckedOut',
                                    @vchReturnValue OUT,
                                    @sProjectName = @vchProjectName,
                                    @sSourceSafeINI = @vchSourceSafeINI,
                                    @sObjectName = @vchObjectName,
                                    @sServerName = @vchServerName,
                                    @sDatabaseName = @vchDatabaseName,
                                    @sLoginName = @vchLoginName,
                                    @sPassword = @vchPassword

if @iReturn <> 0 GOTO E_OAError

select @vchReturnValue

end

CleanUp:
return

E_OAError:
exec dbo.dt_displayoaerror_u @iObjectId, @iReturn
GOTO CleanUp

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_AddComments
@id bigint,
@user_name varchar(40),
@photoid bigint,
@ip varchar(20),
@CommentContents text

AS
SET NOCOUNT ON
--添加评论

insert into COMMENT_TAB (id,user_name, photoid,ip,CommentContents)
values
(@id,@user_name, @photoid,@ip,@CommentContents)

update palbum_tab set commentsCount =(commentsCount+1) where photoid= @photoid

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_AddSectionPhoto
@SectionID bigint,
@PhotoID bigint,
@id bigint
AS
--增加图片- 相册对应信息
if not exists (select * from photo_section_tab where photoid =@photoid and sectionid = @sectionid)
begin
  insert into photo_section_tab (photoID,SectionID) values
  (@PhotoID,@SectionID)

  if @@rowcount > 0
   SET NOCOUNT ON
   update section_tab set photocount = (photocount +1) where SectionID = @SectionID and id=@id

end

--update section_tab set section_tab.photoid= (SELECT MAX(photo_section_tab.photoid) AS photoid FROM photo_section_tab
-- where photo_section_tab.SectionID=@SectionID)
--where section_tab.SectionID=@SectionID and id=@id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_AddUserFavorite
@id bigint,
@photoID bigint
AS
SET NOCOUNT ON
--添加收藏

declare @userID as bigint
set @userID = 0
select @userID = id from palbum_tab where photoid=@photoid
if(@userID<>@id)
begin
insert into favorite_TAB
(id,photoID) values (@id, @photoID)

update palbum_tab set favoritecount = favoritecount +1 where photoid = @photoid
end
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_AddtoPhotoTag
@photoID bigint,
@tagID bigint,
@tagName nvarchar(50),
@UserTagID bigint,
@id bigint

AS
SET NOCOUNT ON

--添加tag
insert into photo_tag_tab
(photoID,tagID,TagName,UserTagID,id) values (@photoID,@tagID,@tagName,@UserTagID,@id)

update
PALBUM_TAB
set indexed =2
where photoid =@photoid and indexed=1

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_DeleteFavoritePhotobyID
@photoid bigint,
@id bigint
AS
SET NOCOUNT ON

--删除收藏
delete
FROM favorite_TAB where photoid =@photoid and id=@id

if(@@rowcount > 0)
update palbum_tab set favoritecount = favoritecount -1 where photoid = @photoid

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_DeletePhotoTag
@photoid bigint

AS
SET NOCOUNT ON
--删除tag
delete from PHOTO_TAG_TAB where photoid = @photoid

update
PALBUM_TAB
set indexed =2
where photoid =@photoid and indexed=1

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_DeletePhotoTagbyPhotoIDTagID
@photoid bigint,
@tagID bigint,
@id bigint

AS
--删除photo_tag_tab 中的对应记录
SET NOCOUNT ON
delete from PHOTO_TAG_TAB where photoid = @photoid and tagID=@tagID and id=@id
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.sp_DeletePhotosByPhotoID
@photoid  bigint,
@id  bigint,
@isAdmin int,
@filetype varchar(20) output,
@pubtime datetime output,
@photoOKCount bigint output ,
@hashid  varchar(100) output
AS
SET NOCOUNT ON

--删除图片
  declare @uploadMonth char(6)
  declare @filesize bigint
  set @pubtime = getdate()
  set @filetype = ''
  set @filesize = 0
  SELECT * FROM PHOTO_TAG_TAB WHERE (PhotoID = @photoid)
  select @filesize=filesize,@pubtime=pubtime,@filetype=filetype,@hashid=hashid from palbum_tab where photoid=@photoID and id=@id

if(@filesize > 0)
begin
  if(month(@pubtime)<10)
   set @uploadMonth = cast(year(@pubtime) as char(4))+'0'+cast(month(@pubtime) as char(1))
  else
   set @uploadMonth =cast(year(@pubtime) as char(4))+cast(month(@pubtime) as char(2))


BEGIN TRAN
  update PHOTOSIZE_TAB set PhotoSize=(PhotoSize-@filesize),PhotoCount=(PhotoCount-1)
  where id=@id and UploadMonth=@uploadMonth


  --delete from favorite_tab where photoid =@photoid
  --delete from photo_tag_tab where photoid =@photoid
  --delete from comment_tab where photoid =@photoid

  UPDATE SECTION_TAB
  SET photocount = (photocount - 1)
  WHERE (SECTION_TAB.sectionid IN
            (SELECT photo_section_tab.sectionid
           FROM photo_section_tab
           WHERE photoid = @photoid))
  --delete from photo_section_tab where photoid =@photoid

  --update section_tab set section_tab.photoid= (SELECT MAX(photo_section_tab.photoid) AS photoid FROM photo_section_tab
  --inner join section_tab on photo_section_tab.sectionid = section_tab.sectionid
  -- where section_tab.photoid=@photoid) where section_tab.photoid=@photoid

  if(@isAdmin =1)
  begin
   update PALBUM_TAB set ifuse=2 where photoid =@photoid
  end
  else
   update PALBUM_TAB set ifuse=3 where photoid =@photoid

set @photoOKCount = 0
select @photoOKCount=count(*) from palbum_tab where id=(select id from palbum_tab where photoid=@photoid and ifuse in (1,4))

COMMIT TRAN
end

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_DeletePhotosFromSection
@photoid  bigint,
@sectionID  bigint,
@id  bigint
AS
SET NOCOUNT ON
--从相册中移出图片
if exists (select * from section_tab where id=@id and sectionID = @sectionid)
if exists (select * from palbum_tab where id=@id and photoID = @photoid)
begin
delete
FROM photo_section_tab where photoid =@photoid and sectionID = @sectionID

update section_tab set photocount = (photocount -1) where SectionID = @SectionID

--update section_tab set section_tab.photoid= (SELECT MAX(photo_section_tab.photoid) AS photoid FROM photo_section_tab
-- where photo_section_tab.SectionID=@SectionID) where section_tab.SectionID=@SectionID

end

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.sp_DeleteSectionbySectionID
@sectionid bigint,
@id bigint
AS
SET NOCOUNT ON

--删除相册
if exists(select * from section_tab where sectionid = @sectionid and id=@id)
begin
delete from PHOTO_SECTION_TAB where SectionID = @sectionid
delete from SECTION_TAB
where SectionID = @sectionid

delete from CommendedSection_Tab
where SectionID = @sectionid

end
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE   PROCEDURE dbo.sp_GetAdmPhotosBySectionsID
@sectionsid  bigint,
@id   bigint,
@minRecord  varchar(20),
@maxRecord  varchar(10),
@photoCount  int output
AS
SET NOCOUNT ON
--取得相册下的所有图片for admin

if exists (select * from section_tab where id=@id and sectionid=@sectionsid)
begin

declare @sql1 nvarchar(2000);
declare @sql2 nvarchar(1000);
declare @wherestr nvarchar(200);
declare @orderstr nvarchar(200);
declare @min varchar(10);

set @wherestr=' inner join PHOTO_SECTION_TAB on PHOTO_SECTION_TAB.photoid=PALBUM_TAB.photoid
   where SectionID ='+ cast(@SectionsID as varchar(64)) +' and Photostatus in (0,1,2) and ifuse in (0,1,4)';

  select @photoCount=(count(*)) from palbum_tab inner join PHOTO_SECTION_TAB
   on PHOTO_SECTION_TAB.photoid=PALBUM_TAB.photoid
   where SectionID =@SectionsID and Photostatus in (0,1,2) and ifuse in (0,1,4)

set @orderstr=' order by palbum_tab.photoid desc '

if( (@minRecord !< @photoCount))
if(@photoCount > 0)
begin
set @minRecord = (@photoCount - @maxRecord)
end

if(@minRecord<>0)
begin
  set @sql1='select top '+@maxRecord+ ' PALBUM_TAB.photoid, PALBUM_TAB.id, PALBUM_TAB.user_name, hashid,
   PALBUM_TAB.filetype, PALBUM_TAB.filesize, PALBUM_TAB.title, PALBUM_TAB.intro, PALBUM_TAB.pubtime,
   PALBUM_TAB.pubip, PALBUM_TAB.width,
        PALBUM_TAB.height, PALBUM_TAB.ifuse,
         PALBUM_TAB.favoriteCount, PALBUM_TAB.Photostatus,
         PALBUM_TAB.commentsCount, PALBUM_TAB.indexed, PALBUM_TAB.viewCount
  from PALBUM_TAB '+@wherestr +' and PALBUM_TAB.photoid< (select min(PHOTO_SECTION_TAB.photoid) from PHOTO_SECTION_TAB where PHOTO_SECTION_TAB.photoid in
  (select top '+@minRecord+' photo_section_tab.photoid from PHOTO_SECTION_TAB
  inner join palbum_tab on PHOTO_SECTION_TAB.photoid=PALBUM_TAB.photoid
   where SectionID ='+ cast(@SectionsID as varchar(64)) +' and Photostatus in (0,1,2) and ifuse in (0,1,4) order by photo_section_tab.photoid desc )) order by PALBUM_TAB.photoid desc'
end
else
  set @sql1='select top '+@maxRecord+' PALBUM_TAB.photoid, PALBUM_TAB.id, PALBUM_TAB.user_name, hashid,
     PALBUM_TAB.filetype, PALBUM_TAB.filesize, PALBUM_TAB.title, PALBUM_TAB.intro, PALBUM_TAB.pubtime,
     PALBUM_TAB.pubip, PALBUM_TAB.width,
          PALBUM_TAB.height, PALBUM_TAB.ifuse,
           PALBUM_TAB.favoriteCount, PALBUM_TAB.Photostatus,
           PALBUM_TAB.commentsCount, PALBUM_TAB.indexed, PALBUM_TAB.viewCount
    from PALBUM_TAB '+@wherestr+' order by PALBUM_TAB.photoid desc'

EXECUTE sp_executesql @sql1;

select SectionID, id, SectionName, PhotoCount, intro, CreateTime, photoID,isnull(bt.blogname,cast(section_tab.id as nvarchar(100))+'@') as blogname from section_tab
left join (SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1)) as bt on bt.UserID=section_tab.id
where sectionid = @sectionsid
end

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetAdmSectionsByID
@id bigint
AS
SET NOCOUNT ON
--取得用户所有相册 for admin

SELECT pt.photoid ,pt.filetype ,
        pt.filesize ,
        pt.pubtime ,

        st.SectionID, st.id ,
        st.SectionName, st.PhotoCount,
        st.intro AS intro, st.CreateTime

  FROM dbo.SECTION_TAB st
  left join (SELECT MAX(photo_section_tab.photoid) AS photoid,sectionid
  FROM photo_section_tab inner join palbum_tab as pt1
  on pt1.photoid=photo_section_tab.photoid where photostatus in (0,1,2) and ifuse in (0,1,4)
  and pt1.id = @id group by sectionid) as mp
  on st.sectionid = mp.sectionid
  left join PALBUM_TAB pt
        on (mp.photoid = pt.photoid )
  WHERE (st.id = @id)
  ORDER BY st.photocount DESC
return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetAllPhotosBySectionsID
@sectionID  bigint,
@userPrivacy int
AS
--取得用户所有相册
SET NOCOUNT ON
  if(@userPrivacy = 0)
   select pt.* from palbum_tab as pt inner join photo_section_tab as pst on pt.photoid=pst.photoid where sectionid=@sectionid and Photostatus in (0,1,2) and ifuse in (0,1,4) order by pt.photoid desc
  else
  begin
   if(@userPrivacy = 1)
    select pt.* from palbum_tab as pt inner join photo_section_tab as pst on pt.photoid=pst.photoid where sectionid=@sectionid    and Photostatus in (1,2) and ifuse in (1,4) order by pt.photoid desc
   else
    select pt.* from palbum_tab as pt inner join photo_section_tab as pst on pt.photoid=pst.photoid where sectionid=@sectionid and Photostatus =1 and   ifuse in (1,4) order by pt.photoid desc
  end
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.sp_GetCommentsByPhotoID
@PhotoID  bigint,
@minRecord  varchar(20),
@maxRecord  varchar(20),
@id  bigint,
@commentsCount  int output
AS
SET NOCOUNT ON
--取得图片评论

declare @sql1 nvarchar(2000);
declare @wherestr nvarchar(200);
declare @orderstr nvarchar(200);

set @commentsCount = 0
select @commentsCount=(count(*)) from comment_tab where PhotoID=@PhotoID

set @wherestr=' where PhotoID ='+cast(@PhotoID as varchar(64))

set @orderstr=' order by commentid desc '

if(@minRecord<>0 )
begin
  if( (@minRecord !< @commentsCount))
  begin
   set @minRecord = @commentsCount - @maxRecord
  end

  set @sql1='select top '+@maxRecord+' ct.*,isnull(bb.blogname,cast(ct.id as nvarchar(100))+'''+'@'+''') as blogname
  from comment_tab as ct left join db_blog..blog_blog as bb on bb.UserID = ct.id and bb.blogtype=1 where commentid<(select min(commentid)
  from comment_tab where commentid in (select top '+@minRecord+' commentid from comment_tab '+@wherestr+
  @orderstr+' ) ) and PhotoID='+cast(@PhotoID as varchar(64))+'   order by commentid desc'
end
else
  set @sql1='select top '+@maxRecord+' ct.*,isnull(bb.blogname,cast(ct.id as nvarchar(100))+'''+'@'+''') as blogname
    from comment_tab as ct left join db_blog..blog_blog as bb on bb.UserID = ct.id and bb.blogtype=1 '+@wherestr+'     order by commentid desc'

EXECUTE sp_executesql @sql1;
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE   PROCEDURE dbo.sp_GetFaveSections
AS
--取得首页相册
--the first section
select top 4 pt.* ,isnull(bt.blogname,cast(pt.id as nvarchar(100))+'@') as blogname,
ct.sectionid,ct.sectionName
from palbum_tab as pt
left join
(
  SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1)) as bt
  on bt.UserID=pt.id
  inner join photo_section_tab as pst on pst.photoid=pt.photoid
  inner join CommendedSection_Tab as ct
  on ct.sectionid=pst.sectionid
  where
  (
     ct.SectionID =
               (
   SELECT top 1 sectionid
           FROM CommendedSection_Tab
           WHERE (CommendedOrder =
                    (
    SELECT MAX(CommendedOrder)
                    FROM CommendedSection_Tab where ifuse in (1,4)
    )
   )
  )
)
order by pt.photoid desc

--- other sections
SELECT top 4 ct.*,isnull(bt.blogname,cast(ct.id as nvarchar(100))+'@') as blogname
from CommendedSection_Tab as ct
left join
(SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1)) as bt
on bt.UserID=ct.id where
   ifuse in (1,4) and
(ct.SectionID <>
           (SELECT top 1 sectionid
          FROM CommendedSection_Tab
          WHERE (CommendedOrder =
                    (SELECT MAX(CommendedOrder)
                   FROM CommendedSection_Tab where ifuse in (1,4)))))

order by CommendedOrder desc

return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.sp_GetFaveUsersByPhotoID
@photoid bigint,
@id bigint,
@minRecord  varchar(20),
@maxRecord  varchar(20),
@currentID bigint,
@FaveCount  int output
AS
SET NOCOUNT ON

--取得某张图片的收藏者
declare @sql1 nvarchar(2000);
declare @sql2 nvarchar(1000);
declare @wherestr nvarchar(2000);
declare @orderstr nvarchar(2000);
declare @min varchar(10);
set @FaveCount = 0

SELECT photoid, id, user_name, filetype, title, intro, pubtime, commentsCount, hashid,
favoriteCount, viewCount
FROM PALBUM_TAB
WHERE (photoid = @photoid)

select @FaveCount=(count(*)) from favorite_tab where photoid = @photoid

if( (@minRecord !< @FaveCount))
if(@FaveCount > 0)
begin
  set @minRecord = (@FaveCount - @maxRecord)
end
if(@minRecord<>0)
begin
  set @sql1='select top '+ @maxRecord + ' ft.id,ft.addtime, ut.user_name ,
   isnull(bt.blogname,cast(ut.id as nvarchar(100))+'''+'@'+''') as blogname,
   isnull(dmt.object_id,0) as friendid
   from favorite_tab as ft
   inner join palbum_tab as pt on pt.photoid = ft.photoid
   inner join db_user..user_tab as ut on ut.id=ft.id
   left join (SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1)) as bt on bt.UserID=ut.id
   left join
   (select distinct object_id,object_username from db_wayup..mapping_tab
     where owner_id = '+cast(@currentID as varchar(20))+' and relation='''+'f'+''') as dmt
   on ut.id = dmt.object_id
   where ft.photoid = '+cast(@photoid as varchar(20))+'

   and ft.addtime<
   (
    select min(addtime) from
     favorite_tab
     where id in
      (select top '+@minRecord+' id from favorite_tab
       where photoid = '+cast(@photoid as varchar(20))+' order by addtime desc
       )
     and photoid = '+cast(@photoid as varchar(20))+'
   )
   order by ft.addtime desc '

end
else
  set @sql1='select top '+@maxRecord+ ' ft.id,ft.addtime, ut.user_name ,
    isnull(bt.blogname,cast(ut.id as nvarchar(100))+'''+'@'+''') as blogname,
    isnull(dmt.object_id,0) as friendid
    from favorite_tab as ft
    inner join palbum_tab as pt on pt.photoid = ft.photoid
    inner join db_user..user_tab as ut on ut.id=ft.id
    left join (SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1)) as bt on bt.UserID=ut.id
    left join
    (select distinct object_id,object_username from db_wayup..mapping_tab
      where owner_id = '+cast(@currentID as varchar(20))+' and relation='''+'f'+''') as dmt
    on ut.id = dmt.object_id
    where ft.photoid = '+cast(@photoid as varchar(20))+' order by ft.addtime desc '

--print @sql1
EXECUTE sp_executesql @sql1;

return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetMyCommentPhotos
@id  bigint,
@lastLoginTime datetime,
@minRecord  varchar(20),
@maxRecord  varchar(20),
@photoCount  int output
AS
SET NOCOUNT ON

--取得我的评论
declare @sql1 nvarchar(4000);
declare @sql2 nvarchar(4000);
declare @wherestr nvarchar(200);
declare @orderstr nvarchar(200);
declare @min varchar(10);
set @photoCount = 0

select @photoCount=(count(*)) from
  (select max(commentid)as commentid,photoid from comment_tab where comment_tab.id=@id group by photoid) as mct
  inner join comment_tab as ct on ct.commentid=mct.commentid inner join palbum_tab as pt
  on mct.photoid = pt.photoid where Photostatus in (1,2) and   ifuse in (1,4) and ct.CommentTime > @lastLoginTime

if( (@minRecord !< @photoCount))
if(@photoCount > 0)
begin
set @minRecord = (@photoCount - @maxRecord)
end

if(@minRecord<>0)
begin
  set @sql1='select top '+@maxRecord+ ' pt.*,
   isnull(bt.blogname,cast(pt.id as nvarchar(100))+'''+'@'+''') as blogname from
    (select max(commentid) as commentid,photoid from comment_tab
    where comment_tab.id='+cast( @id as varchar(20)) + '
    and CommentTime > '''+cast( @lastLoginTime as varchar(20)) + '''
    group by photoid) as ct
   inner join palbum_tab as pt
   on ct.photoid = pt.photoid
   left join
   (SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1))
   as bt on bt.UserID=pt.id where Photostatus in (1,2) and   ifuse in (1,4)
   and ct.commentid<
    (
    select min(commentid) from comment_tab
    where commentid in
    (
     select top '+@minRecord+' ct.commentid
     from
     ( select max(commentid) as commentid,photoid from comment_tab
      where comment_tab.id='+cast( @id as varchar(20)) + '
      and CommentTime > '''+cast( @lastLoginTime as varchar(20)) + '''
      group by photoid
     ) as ct

       inner join palbum_tab as pt
     on ct.photoid = pt.photoid
     where Photostatus in (1,2) and ifuse in (1,4)
     order by ct.commentid desc
    )
   )
    order by ct.commentid desc'


  set @sql2=' select ct.CommentID, ct.id, ct.user_name, ct.CommentTime, ct.photoid, ct.ip, ct.CommentContents,
    isnull(bt.blogname,cast(ct.id as nvarchar(100))+'''+'@'+''') as blogname
   from comment_tab as ct inner join
   (
    select top '+@maxRecord+ ' pt.* from
    (select max(commentid) as commentid,photoid from comment_tab
    where comment_tab.id='+cast( @id as varchar(20)) + '
    and CommentTime > '''+cast( @lastLoginTime as varchar(20)) + '''
    group by photoid) as ct
    inner join palbum_tab as pt
    on ct.photoid = pt.photoid
    where Photostatus in (1,2) and   ifuse in (1,4)
    and ct.commentid<
     (
     select min(commentid) from comment_tab
     where commentid in
     (
      select top '+@minRecord+' ct.commentid
      from
      ( select max(commentid) as commentid,photoid from comment_tab
       where comment_tab.id='+cast( @id as varchar(20)) + '
       and CommentTime > '''+cast( @lastLoginTime as varchar(20)) + '''
       group by photoid
      ) as ct

        inner join palbum_tab as pt
      on ct.photoid = pt.photoid
      where Photostatus in (1,2) and   ifuse in (1,4)
      order by ct.commentid desc
     )
    )
   ) as pt on ct.photoid = pt.photoid
   left join (SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1))
   as bt on bt.UserID=ct.id
    and CommentTime > '''+cast( @lastLoginTime as varchar(20)) + '''
   order by commentid desc
     '

end
else
begin
  set @sql1='select top '+@maxRecord+' pt.*,
   isnull(bt.blogname,cast(pt.id as nvarchar(100))+'''+'@'+''') as blogname from
   (select max(commentid)as commentid,photoid from comment_tab
   where comment_tab.id='+cast( @id as varchar(20)) + '
   and CommentTime > '''+cast( @lastLoginTime as varchar(20)) + '''
   group by photoid) as ct

    inner join palbum_tab as pt
   on ct.photoid = pt.photoid
   left join
   (SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1))
   as bt on bt.UserID=pt.id where Photostatus in (1,2) and ifuse in (1,4)


   order by ct.commentid desc'

  set @sql2=' select ct.CommentID, ct.id, ct.user_name, ct.CommentTime, ct.photoid, ct.ip, ct.CommentContents,
    isnull(bt.blogname,cast(ct.id as nvarchar(100))+'''+'@'+''') as blogname
   from comment_tab as ct inner join
   (
    select top '+@maxRecord+' pt.photoid,Photostatus,ifuse from
    (select max(commentid)as commentid,photoid from comment_tab
    where comment_tab.id='+cast( @id as varchar(20)) + '
    and CommentTime > '''+cast( @lastLoginTime as varchar(20)) + '''
    group by photoid) as ct

     inner join palbum_tab as pt
    on ct.photoid = pt.photoid
    where Photostatus in (1,2) and ifuse in (1,4)

    order by ct.commentid desc
   ) as pt on ct.photoid = pt.photoid
   left join (SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1))
   as bt on bt.UserID=ct.id
   where Photostatus in (1,2) and   ifuse in (1,4)
   order by commentid desc
     '
  end
--print @sql1
EXECUTE sp_executesql @sql1;
EXECUTE sp_executesql @sql2;

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetMyPhotosCommented
@id  bigint,
@timeSpan datetime,
@minRecord  varchar(20),
@maxRecord  varchar(20),
@photoCount  int output
AS
SET NOCOUNT ON

--取得对我的图片的评论
declare @sql1 nvarchar(4000);
declare @sql2 nvarchar(4000);
declare @wherestr nvarchar(200);
declare @orderstr nvarchar(200);
declare @min varchar(10);
set @photoCount = 0

select @photoCount=(count(*)) from palbum_tab as pt inner join
  (select distinct photoid from comment_tab where CommentTime > @timeSpan ) as ct
    on pt.photoid = ct.photoid
    where pt.id =@id and pt.ifuse in (0,1,4)

if( (@minRecord !< @photoCount))
if(@photoCount > 0)
begin
set @minRecord = (@photoCount - @maxRecord)
end

if(@minRecord<>0)
begin
  set @sql1='select top '+@maxRecord+ '   pt.photoID,pt.pubtime,pt.filetype,hashid,
   pt.commentsCount,pt.favoriteCount,pt.title,pt.viewcount
   from palbum_tab as pt inner join
   (select max(commentid) as commentid, photoid from comment_tab where   CommentTime > '''+cast( @timeSpan as varchar(20)) + ''' group by photoid ) as ct
     on pt.photoid = ct.photoid
     where pt.id ='+cast(@id as varchar(20)) +' and pt.ifuse in (0,1,4)
   and ct.commentid <
   (select min(commentid) from comment_tab
    where commentid in
     (select top '+@minRecord+'   commentid
      from palbum_tab as pt inner join
      (select max(commentid) as commentid, photoid from comment_tab where   CommentTime > '''+cast( @timeSpan as varchar(20)) + ''' group by photoid ) as ct
        on pt.photoid = ct.photoid
        where pt.id ='+cast(@id as varchar(20)) +' and pt.ifuse in (0,1,4)
      order by commentid desc

     )
   )
    order by commentid desc'

  set @sql2='select ct.CommentID, ct.id, ct.user_name, ct.CommentTime, ct.photoid, ct.ip,
  ct.CommentContents,isnull(bt.blogname,cast(ct.id as nvarchar(100))+'''+'@'+''') as blogname
   from comment_tab as ct left join
  (SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1))
  as bt on bt.UserID=ct.id
  inner join
  ('+@sql1+') as mp
  on mp.photoid=ct.photoid order by CommentID desc '


end
else
begin
  set @sql1='select top '+@maxRecord+ '    pt.photoID,pt.pubtime,pt.filetype,hashid,
   pt.commentsCount,pt.favoriteCount,pt.title,pt.viewcount
   from palbum_tab as pt inner join
   (select max(commentid) as commentid, photoid from comment_tab where CommentTime > '''+cast( @timeSpan as varchar(20)) + ''' group by photoid ) as ct
     on pt.photoid = ct.photoid
     where pt.id ='+cast(@id as varchar(20)) +' and pt.ifuse in (0,1,4)
   order by commentid desc'

  set @sql2='select ct.CommentID, ct.id, ct.user_name, ct.CommentTime, ct.photoid, ct.ip,
  ct.CommentContents,isnull(bt.blogname,cast(ct.id as nvarchar(100))+'''+'@'+''') as blogname
   from comment_tab as ct left join
  (SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1))
  as bt on bt.UserID=ct.id
  inner join
  ('+@sql1+') as mp
  on mp.photoid=ct.photoid order by CommentID desc '

end
--print @sql2
EXECUTE sp_executesql @sql1;

EXECUTE sp_executesql @sql2;

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetNearPhotosByPhotoIDSectionID
@photoid  bigint,
@sectionID  bigint,
@userPrivacy  int,
@max   smallint output,
@min   smallint output,
@photoCount  int output
AS
SET NOCOUNT ON
set @max = 0
set @min = 0
set @photoCount = 0
----=================
--上下图片的信息在一个相册中
if(@userPrivacy = 0)
begin
  SELECT @photoCount = (COUNT(DISTINCT pst.photoid)) FROM dbo.photo_section_tab as pst inner join palbum_tab as pt on pst.photoid=pt.photoid WHERE (sectionid =@sectionid) and Photostatus in (0,1,2) and ifuse in (0,1,4)

  if exists (SELECT top 1 pst.photoid as photoid FROM dbo.PHOTO_SECTION_TAB
    as pst inner join palbum_tab as pt on pst.photoid=pt.photoid
    where pst.photoid > @photoid and sectionid=@sectionid
     and Photostatus in (0,1,2) and ifuse in (0,1,4))
   set @max = 1
  if exists (SELECT top 1 pst.photoid as photoid FROM dbo.PHOTO_SECTION_TAB
    as pst inner join palbum_tab as pt on pst.photoid=pt.photoid
    where pst.photoid < @photoid and sectionid=@sectionid
     and Photostatus in (0,1,2) and ifuse in (0,1,4) order by pst.photoid desc)
   set @min = 1

  select top 1 id, photoid, user_name,hashid, filetype, filesize, title, intro, pubtime, ifuse,Photostatus from palbum_tab where photoid =(SELECT top 1 pst.photoid as photoid FROM dbo.PHOTO_SECTION_TAB
               as pst inner join palbum_tab as pt on pst.photoid=pt.photoid
               where pst.photoid > @photoid and sectionid=@sectionid
                and Photostatus in (0,1,2) and ifuse in (0,1,4))
  union all
  select top 1 id, photoid, user_name,hashid, filetype, filesize, title, intro, pubtime, ifuse,Photostatus from palbum_tab where photoid =(SELECT top 1 pst.photoid as photoid FROM dbo.PHOTO_SECTION_TAB
               as pst inner join palbum_tab as pt on pst.photoid=pt.photoid
               where pst.photoid < @photoid and sectionid=@sectionid
                and Photostatus in (0,1,2) and ifuse in (0,1,4) order by pst.photoid desc)
end
else
begin
  if(@userPrivacy = 1)
  begin
   SELECT @photoCount = (COUNT(DISTINCT pst.photoid)) FROM dbo.photo_section_tab as pst inner join palbum_tab as pt on pst.photoid=pt.photoid WHERE (sectionid =@sectionid) and Photostatus in (1,2) and   ifuse in (1,4)

   if exists (SELECT top 1 pst.photoid as photoid FROM dbo.PHOTO_SECTION_TAB
      as pst inner join palbum_tab as pt on pst.photoid=pt.photoid
      where pst.photoid > @photoid and sectionid=@sectionid
       and Photostatus in (1,2) and ifuse in (1,4) )
     set @max = 1
   if exists (SELECT top 1 pst.photoid as photoid FROM dbo.PHOTO_SECTION_TAB
     as pst inner join palbum_tab as pt on pst.photoid=pt.photoid
     where pst.photoid < @photoid and sectionid=@sectionid
      and Photostatus in (1,2) and   ifuse in (1,4) order by pst.photoid desc)
    set @min = 1

   select top 1 id, photoid, user_name, hashid,filetype, filesize, title, intro, pubtime, ifuse,Photostatus from palbum_tab where photoid =(SELECT top 1 pst.photoid as photoid FROM dbo.PHOTO_SECTION_TAB
                as pst inner join palbum_tab as pt on pst.photoid=pt.photoid
                where pst.photoid > @photoid and sectionid=@sectionid
                 and Photostatus in (1,2) and   ifuse in (1,4) )
   union all
   select top 1 id, photoid, user_name, hashid,filetype, filesize, title, intro, pubtime, ifuse,Photostatus from palbum_tab where photoid =(SELECT top 1 pst.photoid as photoid FROM dbo.PHOTO_SECTION_TAB
                as pst inner join palbum_tab as pt on pst.photoid=pt.photoid
                where pst.photoid < @photoid and sectionid=@sectionid
                 and Photostatus in (1,2) and   ifuse in (1,4) order by pst.photoid desc)
  end
  else
  begin
   SELECT @photoCount = (COUNT(DISTINCT pst.photoid)) FROM dbo.photo_section_tab as pst inner join palbum_tab as pt on pst.photoid=pt.photoid WHERE (sectionid =@sectionid) and Photostatus =1 and   ifuse in (1,4)

   if exists (SELECT top 1 pst.photoid as photoid FROM dbo.PHOTO_SECTION_TAB
    as pst inner join palbum_tab as pt on pst.photoid=pt.photoid
    where pst.photoid > @photoid and sectionid=@sectionid
     and Photostatus =1 and   ifuse in (1,4) )
    set @max = 1
   if exists (SELECT top 1 pst.photoid as photoid FROM dbo.PHOTO_SECTION_TAB
     as pst inner join palbum_tab as pt on pst.photoid=pt.photoid
     where pst.photoid < @photoid and sectionid=@sectionid
      and Photostatus =1 and   ifuse in (1,4) order by pst.photoid desc)
    set @min = 1

   select top 1 id, photoid, user_name, hashid,filetype, filesize, title, intro, pubtime, ifuse,Photostatus from palbum_tab where photoid =(SELECT top 1 pst.photoid as photoid FROM dbo.PHOTO_SECTION_TAB
                as pst inner join palbum_tab as pt on pst.photoid=pt.photoid
                where pst.photoid > @photoid and sectionid=@sectionid
                 and Photostatus =1 and   ifuse in (1,4) )
   union all
   select top 1 id, photoid, user_name, hashid,filetype, filesize, title, intro, pubtime, ifuse,Photostatus from palbum_tab where photoid =(SELECT top 1 pst.photoid as photoid FROM dbo.PHOTO_SECTION_TAB
                as pst inner join palbum_tab as pt on pst.photoid=pt.photoid
                where pst.photoid < @photoid and sectionid=@sectionid
                 and Photostatus =1 and   ifuse in (1,4) order by pst.photoid desc)
  end
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.sp_GetPhotoAllInfoByPhotoID
@photoid  bigint,
@userPrivacy  bigint,
@max   smallint output,
@min   smallint output,
@photoCount  int output,
@curID   bigint,
@requestID  bigint
AS
SET NOCOUNT ON
--取得当前图片的所有信息
set @max = 0
set @min = 0
set @photoCount = 0

declare @relation char(1)
declare @id bigint
declare @ownerID bigint
set @id = 0
select @id=id from palbum_tab where photoid=@photoid

if(@curID>0)
if(@userPrivacy > 2)
  select @relation=relation from db_wayup..mapping_tab where owner_id = @curID and [object_id]=@userPrivacy
  begin
   if(@relation = 'F')
    set @userPrivacy = 1
  end


------当前图片的所有信息

if(@userPrivacy = 0)
begin

SELECT @photoCount = (COUNT(DISTINCT photoid)) FROM dbo.PALBUM_TAB WHERE (id =@id) and Photostatus in (0,1,2) and ifuse in (0,1,4)

  SELECT top 1 pt.photoid, pt.id, pt.user_name, hashid,
        pt.filetype, pt.filesize, pt.title,
        pt.intro, pt.pubtime, pt.ifuse, pt.width,
        pt.Photostatus,commentsCount,isnull(ft.isFave,'0') as isFave,pt.viewCount,pt.favoriteCount
  FROM
  palbum_tab as pt
  left join (SELECT '1' AS isFave ,photoID FROM FAVORITE_TAB WHERE (id = @curID) AND (photoID = @photoid) )
  as FT on ft.photoID=pt.photoID
  where pt.photoid=@photoid and Photostatus in (0,1,2) and ifuse in (0,1,4) and pt.id=@requestID

end
else
begin
  if(@userPrivacy = 1)
  begin
   SELECT @photoCount = (COUNT(DISTINCT photoid)) FROM dbo.PALBUM_TAB WHERE (id =@id) and Photostatus in (1,2) and ifuse in (1,4)

   SELECT top 1 pt.photoid, pt.id, pt.user_name, hashid,
         pt.filetype, pt.filesize, pt.title,
         pt.intro, pt.pubtime, pt.ifuse, pt.width,
         pt.Photostatus,commentsCount,isnull(ft.isFave,'0') as isFave,pt.viewCount,pt.favoriteCount
   FROM
   palbum_tab as pt
   left join (SELECT '1' AS isFave ,photoID FROM FAVORITE_TAB WHERE (id = @curID) AND (photoID = @photoid) )
   as FT on ft.photoID=pt.photoID
   where pt.photoid=@photoid and Photostatus in (1,2) and   ifuse in (1,4)   and pt.id=@requestID
  end
  else
  begin
   SELECT @photoCount = (COUNT(DISTINCT photoid)) FROM dbo.PALBUM_TAB WHERE (id =@id) and Photostatus =1 and ifuse in (1,4)

   SELECT top 1 pt.photoid, pt.id, pt.user_name, hashid,
         pt.filetype, pt.filesize, pt.title,
         pt.intro, pt.pubtime, pt.ifuse, pt.width,
         pt.Photostatus,commentsCount,isnull(ft.isFave,'0') as isFave,pt.viewCount,pt.favoriteCount
   FROM
   palbum_tab as pt
   left join (SELECT '1' AS isFave ,photoID FROM FAVORITE_TAB WHERE (id = @curID) AND (photoID = @photoid) )
   as FT on ft.photoID=pt.photoID
   where pt.photoid=@photoid and Photostatus =1 and ifuse in (1,4) and pt.id=@requestID
  end
end

--上下图片的信息
if(@userPrivacy = 0)
begin
  if exists (select * from palbum_tab where photoid > @photoID and id = @id and Photostatus in (0,1,2) and ifuse in (0,1,4))
   set @max = 1
  if exists (select * from palbum_tab where photoid < @photoID and id = @id and Photostatus in (0,1,2) and ifuse in (0,1,4))
   set @min = 1

  select top 1 id, photoid, user_name,hashid, filetype, filesize, title, intro, pubtime, ifuse,Photostatus from palbum_tab where photoid > @photoID and id = @id and Photostatus in (0,1,2) and ifuse in (0,1,4)
  union all
  select top 1 id, photoid, user_name,hashid, filetype, filesize, title, intro, pubtime, ifuse,Photostatus from palbum_tab where photoid =(select max (photoid) from palbum_tab
       where photoid<@photoID and id = @id and Photostatus in (0,1,2) and ifuse in (0,1,4))
end
else
begin
  if(@userPrivacy = 1)
  begin
   if exists (select * from palbum_tab where photoid > @photoID and id = @id and Photostatus in (1,2) and ifuse in (1,4) )
    set @max = 1
   if exists (select * from palbum_tab where photoid < @photoID and id = @id and Photostatus in (1,2) and ifuse in (1,4) )
    set @min = 1

   select top 1 id, photoid, user_name,hashid, filetype, filesize, title, intro, pubtime, ifuse,Photostatus from palbum_tab where photoid > @photoID and id = @id and Photostatus in (1,2) and ifuse in (1,4)
   union all
   select top 1 id, photoid, user_name,hashid, filetype, filesize, title, intro, pubtime, ifuse,Photostatus from palbum_tab where photoid =(select max (photoid) from palbum_tab
         where photoid<@photoID and id = @id and Photostatus in (1,2) and ifuse in (1,4) )
  end
  else
  begin
   if exists (select * from palbum_tab where photoid > @photoID and id = @id and Photostatus =1 and ifuse in (1,4) )
    set @max = 1
   if exists (select * from palbum_tab where photoid < @photoID and id = @id and Photostatus =1 and ifuse in (1,4) )
    set @min = 1

   select top 1 id, photoid, user_name, hashid,filetype, filesize, title, intro, pubtime, ifuse,Photostatus from palbum_tab where photoid > @photoID and id = @id and Photostatus =1 and   ifuse in (1,4)
   union all
   select top 1 id, photoid, user_name,hashid, filetype, filesize, title, intro, pubtime, ifuse,Photostatus from palbum_tab where photoid =(select max (photoid) from palbum_tab
       where photoid<@photoID and id = @id and Photostatus =1 and   ifuse in (1,4))
  end
end

--当前图片的评论信息
SELECT top 11 CommentID, id, user_name, CommentTime, photoid, ip, CommentContents,isnull(bb.blogname,cast(id as nvarchar(100))+'@') as blogName
FROM dbo.COMMENT_TAB as ct left join db_blog..blog_blog as bb on bb.UserID = ct.id and bb.blogtype=1
WHERE (photoid = @photoid) order by CommentID desc

--相关section info

if(@userPrivacy = 0)
begin

  SELECT st.SectionID, st.id ,
        st.SectionName, st.PhotoCount,
        st.intro AS intro, st.CreateTime,st.photoid

  FROM dbo.SECTION_TAB st LEFT OUTER JOIN
        dbo.photo_section_tab as pst ON pst.sectionid=st.sectionid inner join
        palbum_tab as pt on pst.photoid = pt.photoid
  WHERE (pt.photoid=@photoid) and Photostatus in (0,1,2) and ifuse in (0,1,4)
  ORDER BY st.photocount DESC

end
else
begin
  if(@userPrivacy = 1)
  begin
   SELECT st.SectionID, st.id ,
         st.SectionName, st.PhotoCount,
         st.intro AS intro, st.CreateTime,st.photoid

   FROM dbo.SECTION_TAB st LEFT OUTER JOIN
         dbo.photo_section_tab as pst ON pst.sectionid=st.sectionid inner join
         palbum_tab as pt on pst.photoid = pt.photoid
   WHERE (pt.photoid=@photoid) and Photostatus in (1,2) and ifuse in (1,4)
   ORDER BY st.photocount DESC



  end
  else
  begin
   SELECT st.SectionID, st.id ,
         st.SectionName, st.PhotoCount,
         st.intro AS intro, st.CreateTime,st.photoid

   FROM dbo.SECTION_TAB st LEFT OUTER JOIN
         dbo.photo_section_tab as pst ON pst.sectionid=st.sectionid inner join
         palbum_tab as pt on pst.photoid = pt.photoid
   WHERE (pt.photoid=@photoid) and Photostatus =1 and   ifuse in (1,4)
   ORDER BY st.photocount DESC


  end
end

--相关tag info
select PhotoTagID, PhotoID, TagID, TagName, UserTagID, id from photo_tag_tab as ptt where ptt.photoid=@photoid

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetPhotoAllInfoByPhotoIDSectionID
@photoid  bigint,
@sectionID  bigint,
@userPrivacy  bigint,
@max   smallint output,
@min   smallint output,
@photoCount  int output,
@curID   bigint,
@requestID  bigint

AS
--取得当前相册的图片的所有信息
SET NOCOUNT ON
set @max = 0
set @min = 0
set @photoCount = 0

declare @id bigint
set @id = 0
declare @relation char(1)
select @id=id from palbum_tab where photoid=@photoid

if(@curID>0)
  select @relation=relation from db_wayup..mapping_tab where owner_id = @curID and [object_id]=@userPrivacy
  begin
   if(@relation = 'F')
    set @userPrivacy = 1
  end


------当前图片的所有信息

if(@userPrivacy = 0)
begin

  SELECT top 1 pt.photoid, pt.id, pt.user_name,
        pt.filetype, pt.filesize, pt.title, hashid,
        pt.intro, pt.pubtime, pt.ifuse, pt.width,
        pt.Photostatus,commentsCount,isnull(ft.isFave,'0') as isFave,pt.viewCount,pt.favoriteCount
  FROM
  palbum_tab as pt inner join photo_section_tab as pst on pt.photoid = pst.photoid
  left join (SELECT '1' AS isFave ,photoID FROM FAVORITE_TAB WHERE (id = @curID) AND (photoID = @photoid) )
  as FT on ft.photoID=pt.photoID

  where pst.sectionid=@sectionid and pt.photoid=@photoid and Photostatus in (0,1,2) and ifuse in (0,1,4) and pt.id=@requestID

end
else
begin
  if(@userPrivacy = 1)
  begin

   SELECT top 1 pt.photoid, pt.id, pt.user_name,
         pt.filetype, pt.filesize, pt.title,
         pt.intro, pt.pubtime, pt.ifuse, pt.width,hashid,
         pt.Photostatus,commentsCount,isnull(ft.isFave,'0') as isFave,pt.viewCount,pt.favoriteCount
   FROM
   palbum_tab as pt inner join photo_section_tab as pst on pt.photoid = pst.photoid

   left join (SELECT '1' AS isFave ,photoID FROM FAVORITE_TAB WHERE (id = @curID) AND (photoID = @photoid) )
   as FT on ft.photoID=pt.photoID

   where pst.sectionid=@sectionid and pt.photoid=@photoid and Photostatus in (1,2) and ifuse in (1,4)   and pt.id=@requestID
  end
  else

   SELECT top 1 pt.photoid, pt.id, pt.user_name,
         pt.filetype, pt.filesize, pt.title,
         pt.intro, pt.pubtime, pt.ifuse, pt.width,hashid,
         pt.Photostatus,commentsCount,isnull(ft.isFave,'0') as isFave,pt.viewCount,pt.favoriteCount
   FROM
   palbum_tab as pt inner join photo_section_tab as pst on pt.photoid = pst.photoid

   left join (SELECT '1' AS isFave ,photoID FROM FAVORITE_TAB WHERE (id = @curID) AND (photoID = @photoid) )
   as FT on ft.photoID=pt.photoID

   where pst.sectionid=@sectionid and pt.photoid=@photoid and Photostatus =1 and ifuse in (1,4) and pt.id=@requestID
end

--上下图片的信息
if(@userPrivacy = 0)
begin
  SELECT @photoCount = (COUNT(DISTINCT pst.photoid)) FROM dbo.photo_section_tab as pst inner join palbum_tab as pt on pst.photoid=pt.photoid WHERE (sectionid =@sectionid) and Photostatus in (0,1,2) and ifuse in (0,1,4)

  if exists (SELECT top 1 pst.photoid as photoid FROM dbo.PHOTO_SECTION_TAB
    as pst inner join palbum_tab as pt on pst.photoid=pt.photoid
    where pst.photoid > @photoid and sectionid=@sectionid
     and Photostatus in (0,1,2) and ifuse in (0,1,4))
   set @max = 1
  if exists (SELECT top 1 pst.photoid as photoid FROM dbo.PHOTO_SECTION_TAB
    as pst inner join palbum_tab as pt on pst.photoid=pt.photoid
    where pst.photoid < @photoid and sectionid=@sectionid
     and Photostatus in (0,1,2) and ifuse in (0,1,4) order by pst.photoid desc)
   set @min = 1

  select top 1 id, photoid, user_name, filetype, filesize,hashid, title, intro, pubtime, ifuse,Photostatus from palbum_tab where photoid =(SELECT top 1 pst.photoid as photoid FROM dbo.PHOTO_SECTION_TAB
               as pst inner join palbum_tab as pt on pst.photoid=pt.photoid
               where pst.photoid > @photoid and sectionid=@sectionid
                and Photostatus in (0,1,2) and ifuse in (0,1,4))
  union all
  select top 1 id, photoid, user_name, filetype, filesize, hashid,title, intro, pubtime, ifuse,Photostatus from palbum_tab where photoid =(SELECT top 1 pst.photoid as photoid FROM dbo.PHOTO_SECTION_TAB
               as pst inner join palbum_tab as pt on pst.photoid=pt.photoid
               where pst.photoid < @photoid and sectionid=@sectionid

                and Photostatus in (0,1,2) and ifuse in (0,1,4) order by pst.photoid desc)
end
else
begin
  if(@userPrivacy = 1)
  begin
   SELECT @photoCount = (COUNT(DISTINCT pst.photoid)) FROM dbo.photo_section_tab as pst inner join palbum_tab as pt on pst.photoid=pt.photoid WHERE (sectionid =@sectionid) and Photostatus in (1,2) and   ifuse in (1,4)

   if exists (SELECT top 1 pst.photoid as photoid FROM dbo.PHOTO_SECTION_TAB
      as pst inner join palbum_tab as pt on pst.photoid=pt.photoid
      where pst.photoid > @photoid and sectionid=@sectionid
       and Photostatus in (1,2) and ifuse in (1,4) )
     set @max = 1
   if exists (SELECT top 1 pst.photoid as photoid FROM dbo.PHOTO_SECTION_TAB
     as pst inner join palbum_tab as pt on pst.photoid=pt.photoid
     where pst.photoid < @photoid and sectionid=@sectionid
      and Photostatus in (1,2) and ifuse in (1,4) )
    set @min = 1

   select top 1 id, photoid, user_name, filetype, hashid,filesize, title, intro, pubtime, ifuse,Photostatus from palbum_tab where photoid =(SELECT top 1 pst.photoid as photoid FROM dbo.PHOTO_SECTION_TAB
                as pst inner join palbum_tab as pt on pst.photoid=pt.photoid
                where pst.photoid > @photoid and sectionid=@sectionid
                 and Photostatus in (1,2) and ifuse in (1,4) )
   union all
   select top 1 id, photoid, user_name, filetype, hashid,filesize, title, intro, pubtime, ifuse,Photostatus from palbum_tab where photoid =(SELECT top 1 pst.photoid as photoid FROM dbo.PHOTO_SECTION_TAB
                as pst inner join palbum_tab as pt on pst.photoid=pt.photoid
                where pst.photoid < @photoid and sectionid=@sectionid
                 and Photostatus in (1,2) and ifuse in (1,4)   order by pst.photoid desc)
  end
  else
  begin
   SELECT @photoCount = (COUNT(DISTINCT pst.photoid)) FROM dbo.photo_section_tab as pst inner join palbum_tab as pt on pst.photoid=pt.photoid WHERE (sectionid =@sectionid) and Photostatus =1 and ifuse in (1,4)

   if exists (SELECT top 1 pst.photoid as photoid FROM dbo.PHOTO_SECTION_TAB
    as pst inner join palbum_tab as pt on pst.photoid=pt.photoid
    where pst.photoid > @photoid and sectionid=@sectionid
     and Photostatus =1 and ifuse in (1,4) )
    set @max = 1
   if exists (SELECT top 1 pst.photoid as photoid FROM dbo.PHOTO_SECTION_TAB
     as pst inner join palbum_tab as pt on pst.photoid=pt.photoid
     where pst.photoid < @photoid and sectionid=@sectionid
      and Photostatus =1 and ifuse in (1,4) )
    set @min = 1

   select top 1 id, photoid, user_name, filetype, hashid,filesize, title, intro, pubtime, ifuse,Photostatus from palbum_tab where photoid =(SELECT top 1 pst.photoid as photoid FROM dbo.PHOTO_SECTION_TAB
                as pst inner join palbum_tab as pt on pst.photoid=pt.photoid
                where pst.photoid > @photoid and sectionid=@sectionid
                 and Photostatus =1 and ifuse in (1,4) )
   union all
   select top 1 id, photoid, user_name, filetype, hashid,filesize, title, intro, pubtime, ifuse,Photostatus from palbum_tab where photoid =(SELECT top 1 pst.photoid as photoid FROM dbo.PHOTO_SECTION_TAB
                as pst inner join palbum_tab as pt on pst.photoid=pt.photoid
                where pst.photoid < @photoid and sectionid=@sectionid
                 and Photostatus =1 and ifuse in (1,4) order by pst.photoid desc)
  end
end

--当前图片的评论信息
SELECT top 11 CommentID, id, user_name, CommentTime, photoid, ip, CommentContents,bb.blogname
FROM dbo.COMMENT_TAB as ct left join db_blog..blog_blog as bb on bb.UserID = ct.id and bb.blogtype=1
WHERE (photoid = @photoid) order by CommentID desc

--相关section info

if(@userPrivacy = 0)
begin

  SELECT st.SectionID, st.id ,
        st.SectionName, st.PhotoCount,
        st.intro AS intro, st.CreateTime,st.photoid

  FROM dbo.SECTION_TAB st LEFT OUTER JOIN
        dbo.photo_section_tab as pst ON pst.sectionid=st.sectionid inner join
        palbum_tab as pt on pst.photoid = pt.photoid
  WHERE (pt.photoid=@photoid) and Photostatus in (0,1,2) and ifuse in (0,1,4)
  ORDER BY st.photocount DESC

end
else
begin
  if(@userPrivacy = 1)
  begin
   SELECT st.SectionID, st.id ,
         st.SectionName, st.PhotoCount,
         st.intro AS intro, st.CreateTime,st.photoid

   FROM dbo.SECTION_TAB st LEFT OUTER JOIN
         dbo.photo_section_tab as pst ON pst.sectionid=st.sectionid inner join
         palbum_tab as pt on pst.photoid = pt.photoid
   WHERE (pt.photoid=@photoid) and Photostatus in (1,2) and ifuse in (1,4)
   ORDER BY st.photocount DESC



  end
  else
  begin
   SELECT st.SectionID, st.id ,
         st.SectionName, st.PhotoCount,
         st.intro AS intro, st.CreateTime,st.photoid

   FROM dbo.SECTION_TAB st LEFT OUTER JOIN
         dbo.photo_section_tab as pst ON pst.sectionid=st.sectionid inner join
         palbum_tab as pt on pst.photoid = pt.photoid
   WHERE (pt.photoid=@photoid) and Photostatus =1 and ifuse in (1,4)
   ORDER BY st.photocount DESC


  end
end

--相关tag info
select PhotoTagID, PhotoID, TagID, TagName, UserTagID, id from photo_tag_tab as ptt where ptt.photoid=@photoid

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE   PROCEDURE dbo.sp_GetPhotosBySectionsID
@sectionsid  bigint,
@minRecord  varchar(20),
@maxRecord  varchar(20),
@userPrivacy  bigint,
@photoCount  int output,
@curID   bigint,
@requestID  bigint,
@isSelf   int output
AS
SET NOCOUNT ON

--取得某个相册下的图片
declare @sql1 nvarchar(3000);
declare @sql2 nvarchar(1000);
declare @wherestr nvarchar(500);
declare @wherestr2 nvarchar(500);
declare @orderstr nvarchar(500);
declare @min varchar(10);
declare @id bigint
set @photoCount = 0
set @isSelf = 0

set @id = 0
if(@sectionsid =0)
begin
  set @isSelf = 1
end
else
begin
  select @id=id from section_tab where sectionid=@sectionsid
  if(@id = @requestID)
   set @isSelf = 1
end

declare @relation char(1)
if(@curID>0)
  select @relation=relation from db_wayup..mapping_tab where owner_id = @curID and [object_id]=@userPrivacy
  begin
   if(@relation = 'F')
    set @userPrivacy = 1
  end

if(@userPrivacy = 0)
begin
  set @wherestr=' inner join PHOTO_SECTION_TAB on PHOTO_SECTION_TAB.photoid=PALBUM_TAB.photoid
   where SectionID ='+ cast(@SectionsID as varchar(20)) +' and Photostatus in (0,1,2) and ifuse in (0,1,4)';

  set @wherestr2=' inner join palbum_tab on palbum_tab.photoid=PHOTO_SECTION_TAB.photoid
  where PHOTO_SECTION_TAB.SectionID ='+ cast(@SectionsID as varchar(20)) +' and Photostatus in (0,1,2) and ifuse in (0,1,4) ';

  select @photoCount=(count(*)) from palbum_tab inner join PHOTO_SECTION_TAB
   on PHOTO_SECTION_TAB.photoid=PALBUM_TAB.photoid
   where SectionID =@SectionsID and Photostatus in (0,1,2) and ifuse in (0,1,4)
end
else
begin
  if(@userPrivacy = 1)
  begin
   set @wherestr=' inner join PHOTO_SECTION_TAB on PHOTO_SECTION_TAB.photoid=PALBUM_TAB.photoid
   where SectionID ='+ cast(@SectionsID as varchar(20)) +' and Photostatus in (1,2) and ifuse in (1,4) ';

   set @wherestr2=' inner join palbum_tab on palbum_tab.photoid=PHOTO_SECTION_TAB.photoid
   where PHOTO_SECTION_TAB.SectionID ='+ cast(@SectionsID as varchar(20)) +' and Photostatus in (1,2) and ifuse in (1,4) ';

   select @photoCount=(count(*)) from palbum_tab inner join PHOTO_SECTION_TAB
    on PHOTO_SECTION_TAB.photoid=PALBUM_TAB.photoid
    where SectionID =@SectionsID and Photostatus in (1,2) and ifuse in (1,4)
  end
  else
  begin
   set @wherestr=' inner join PHOTO_SECTION_TAB on PHOTO_SECTION_TAB.photoid=PALBUM_TAB.photoid
   where SectionID ='+ cast(@SectionsID as varchar(20)) +' and Photostatus =1 and   ifuse in (1,4) ' ;

   set @wherestr2=' inner join palbum_tab on palbum_tab.photoid=PHOTO_SECTION_TAB.photoid where
   PHOTO_SECTION_TAB.SectionID ='+ cast(@SectionsID as varchar(20)) +' and Photostatus =1 and ifuse in (1,4) ';

set @orderstr=' order by palbum_tab.photoid desc '

if( (@minRecord !< @photoCount))
if(@photoCount > 0)
begin
set @minRecord = (@photoCount - @maxRecord)
end

      PALBUM_TAB.height, PALBUM_TAB.ifuse, PALBUM_TAB.favoriteCount, PALBUM_TAB.Photostatus, PALBUM_TAB.commentsCount, PALBUM_TAB.indexed,
      PALBUM_TAB.viewCount
    from PALBUM_TAB '+@wherestr+' and palbum_tab.id= '+ cast(@requestID as varchar(20)) +'   order by PALBUM_TAB.photoid desc'

--print @sql1
EXECUTE sp_executesql @sql1;

select SectionID, id, SectionName, PhotoCount, intro, CreateTime, photoID from section_tab where sectionid = @sectionsid

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetPhotosByTagName
@tagName  nvarchar(50),
@minRecord  varchar(20),
@maxRecord  varchar(10),
@photoCount  int output
AS
SET NOCOUNT ON

declare @sql1 nvarchar(3000);
declare @wherestr nvarchar(500);
declare @wherestr2 nvarchar(500);
declare @orderstr nvarchar(200);
declare @min varchar(10);
set @photoCount = 0
---============

--取得某个标签下的图片
set @wherestr=' left join (SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1)) as bt on bt.UserID=PALBUM_TAB.id
  inner join PHOTO_Tag_TAB as ptt on ptt.photoid=PALBUM_TAB.photoid
  where tagname ='''+ @tagname +''' and Photostatus =1 and ifuse = 4 ';

select @photoCount=(count(*)) from palbum_tab inner join PHOTO_Tag_TAB as ptt on ptt.photoid=PALBUM_TAB.photoid
  where tagName =@tagName and Photostatus =1 and ifuse = 4

set @orderstr=' order by palbum_tab.photoid desc '

if( (@minRecord !< @photoCount))
if(@photoCount > 0)
begin
set @minRecord = (@photoCount - @maxRecord)
end

   and PALBUM_TAB.photoid< (select min(PHOTO_Tag_TAB.photoid) from PHOTO_Tag_TAB where PHOTO_Tag_TAB.photoid in
  (select top '+@minRecord+' photo_Tag_tab.photoid from PHOTO_Tag_TAB
  inner join palbum_tab on palbum_tab.photoid=photo_tag_tab.photoid where Photostatus =1 and ifuse = 4 and
  tagname ='''+ @tagname
  +''' order by photo_tag_tab.photoid desc )) order by PALBUM_TAB.photoid desc'
end
else
  set @sql1='select top '+@maxRecord+'
  PALBUM_TAB.photoid, PALBUM_TAB.id, PALBUM_TAB.user_name,hashid,
PALBUM_TAB.filetype, PALBUM_TAB.filesize, PALBUM_TAB.title, PALBUM_TAB.intro, PALBUM_TAB.pubtime,
PALBUM_TAB.pubip, PALBUM_TAB.width,
      PALBUM_TAB.height, PALBUM_TAB.ifuse, PALBUM_TAB.favoriteCount, PALBUM_TAB.Photostatus, PALBUM_TAB.commentsCount, PALBUM_TAB.indexed,
      PALBUM_TAB.viewCount
   ,isnull(bt.blogname,cast(palbum_tab.id as nvarchar(100)) +'''+'@'+''') as blogname
    from PALBUM_TAB left join (SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1)) as bt on bt.UserID=PALBUM_TAB.id
  inner join PHOTO_Tag_TAB as ptt on ptt.photoid=PALBUM_TAB.photoid
  where tagname ='''+ @tagname +''' and Photostatus =1 and ifuse = 4 order by PALBUM_TAB.photoid desc'

EXECUTE sp_executesql @sql1;
--=============

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetPhotosComments
@photoid bigint
AS
SET NOCOUNT ON

--取得评论
select ct.CommentID, ct.id, ct.user_name, ct.CommentTime, ct.photoid, ct.ip, ct.CommentContents
,isnull(bt.blogname,cast(ct.id as nvarchar(100))+'@') as blogname
from comment_tab as ct left join
(SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1))
as bt on bt.UserID=ct.id
where photoid=@photoid

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetPhotosHasNoAlbumByID
@id  bigint,
@minRecord  varchar(20),
@maxRecord  varchar(20),
@photoCount  int output
AS
--取得未加到相册图片
SET NOCOUNT ON

declare @sql1 nvarchar(3000);
declare @wherestr nvarchar(500);
declare @orderstr nvarchar(200);
declare @relation char(1)
declare @userPrivacy  bigint

set @photoCount = 0

set @wherestr=' id ='+cast(@id as varchar(20)) + '
and photoid not in (select pst.photoid from section_tab as st
inner join photo_section_tab as pst on pst.sectionid = st.sectionid where st.id ='+cast(@id as varchar(20)) + ')
and Photostatus in (0,1,2) and ifuse in (0,1,4) '

select @photoCount=(count(*)) from palbum_tab as pt
where photoid not in (select pst.photoid from section_tab as st
inner join photo_section_tab as pst on pst.sectionid = st.sectionid where st.id=@id)
and id=@id and Photostatus in (0,1,2) and ifuse in (0,1,4)

set @orderstr=' order by photoid desc '

if(@minRecord<>0 )
begin
  if( (@minRecord !< @photoCount))
  begin
   set @minRecord = @photoCount - @maxRecord
  end

  set @sql1='select top '+@maxRecord+' PALBUM_TAB.photoid, PALBUM_TAB.id, PALBUM_TAB.user_name,
PALBUM_TAB.filetype, PALBUM_TAB.filesize, PALBUM_TAB.title, PALBUM_TAB.intro, PALBUM_TAB.pubtime,
PALBUM_TAB.pubip, PALBUM_TAB.width,
      PALBUM_TAB.height, PALBUM_TAB.ifuse, PALBUM_TAB.favoriteCount, PALBUM_TAB.Photostatus, PALBUM_TAB.commentsCount, PALBUM_TAB.indexed,
      PALBUM_TAB.viewCount
  from PALBUM_TAB where photoid<(select min(photoid)
  from PALBUM_TAB where photoid in (select top '+@minRecord+' photoid from PALBUM_TAB where '+@wherestr+
  @orderstr+' ) ) and '+@wherestr+' order by PALBUM_TAB.photoid desc'
end
else
  set @sql1='select top '+@maxRecord+' PALBUM_TAB.photoid, PALBUM_TAB.id, PALBUM_TAB.user_name,
PALBUM_TAB.filetype, PALBUM_TAB.filesize, PALBUM_TAB.title, PALBUM_TAB.intro, PALBUM_TAB.pubtime,
PALBUM_TAB.pubip, PALBUM_TAB.width,
      PALBUM_TAB.height, PALBUM_TAB.ifuse, PALBUM_TAB.favoriteCount, PALBUM_TAB.Photostatus, PALBUM_TAB.commentsCount, PALBUM_TAB.indexed,
      PALBUM_TAB.viewCount
    from PALBUM_TAB where '+@wherestr+' order by PALBUM_TAB.photoid desc'

EXECUTE sp_executesql @sql1;

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE   PROCEDURE dbo.sp_GetSectionsByID
@id  bigint,
@userPrivacy bigint,
@curID   bigint,
@isFriend int output
AS
SET NOCOUNT ON
set @isFriend = 0
--取得用户相册
declare @relation char(1)
if(@curID>0)
  select @relation=relation from db_wayup..mapping_tab where owner_id = @curID and [object_id]=@userPrivacy
  begin
   if(@relation = 'F')
   begin
    set @userPrivacy = 1
    set @isFriend = 1
   end
  end

if(@userPrivacy = 0)
begin

  SELECT pt.photoid ,pt.filetype ,
        pt.filesize ,
        pt.pubtime , hashid,

        st.SectionID, st.id ,
        st.SectionName, st.PhotoCount,
        st.intro AS intro, st.CreateTime

  FROM dbo.SECTION_TAB st
  left join (SELECT MAX(photo_section_tab.photoid) AS photoid,sectionid
  FROM photo_section_tab inner join palbum_tab as pt1
  on pt1.photoid=photo_section_tab.photoid where photostatus in (0,1,2) and ifuse in (0,1,4)
  and pt1.id = @id group by sectionid) as mp
  on st.sectionid = mp.sectionid
  left join PALBUM_TAB pt
        on (mp.photoid = pt.photoid )
  WHERE (st.id = @id)
  ORDER BY st.photocount DESC

end
else
begin
  if(@userPrivacy = 1)
  begin
   SELECT pt.photoid ,pt.filetype ,
        pt.filesize ,
        pt.pubtime , hashid,

        st.SectionID, st.id ,
        st.SectionName, st.PhotoCount,
        st.intro AS intro, st.CreateTime

  FROM dbo.SECTION_TAB st
  inner join (SELECT MAX(photo_section_tab.photoid) AS photoid,sectionid
  FROM photo_section_tab inner join palbum_tab as pt1
  on pt1.photoid=photo_section_tab.photoid where photostatus in (1,2) and ifuse in (1,4)
  and pt1.id = @id group by sectionid) as mp
  on st.sectionid = mp.sectionid
  inner join PALBUM_TAB pt
        on (mp.photoid = pt.photoid )
  WHERE (st.id = @id)
  ORDER BY st.photocount DESC



  end
  else
  begin
   SELECT pt.photoid ,pt.filetype ,
        pt.filesize ,
        pt.pubtime , hashid,

        st.SectionID, st.id ,
        st.SectionName, st.PhotoCount,
        st.intro AS intro, st.CreateTime

SELECT id, user_name
FROM DB_user..USER_TAB ut
WHERE (ut.id = @id)

return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetUserFavoritePhotos
@id  bigint
AS
SET NOCOUNT ON
--unuse
select photoid, id, user_name, filetype, filesize, title, intro, pubtime, pubip, width,
       height, ifuse, favoriteCount, Photostatus, commentsCount, indexed,
       viewCount from palbum_tab where photoid in (select photoid from favorite_TAB where id = @id)
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetUserFavorites
@id   bigint,
@userPrivacy  bigint,
@minRecord  varchar(20),
@maxRecord  varchar(10),
@photoCount  int output,
@curID   bigint,
@user_name  varchar(40) output
AS
SET NOCOUNT ON

--取得用户收藏
declare @sql1 nvarchar(3000);
declare @sql2 nvarchar(1000);
declare @wherestr nvarchar(500);
declare @wherestr2 nvarchar(500);
declare @orderstr nvarchar(200);
declare @min varchar(10);
set @photoCount = 0
declare @relation char(1)
select @user_name = user_name from DB_user..USER_TAB where id=@id
if(@curID>0)
  select @relation=relation from db_wayup..mapping_tab where owner_id = @curID and [object_id]=@userPrivacy
  begin
   if(@relation = 'F')
    set @userPrivacy = 1
  end
---============
if(@userPrivacy = 0)
begin
  set @wherestr=' inner join favorite_tab as ft on ft.photoid=PALBUM_TAB.photoid
   left join (SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1)) as bt on bt.UserID=PALBUM_TAB.id
   where ft.id= '+cast(@id as varchar(20)) +' and Photostatus in (0,1,2) and ifuse in (0,1,4)';

  set @wherestr2=' inner join palbum_tab on palbum_tab.photoid=favorite_tab.photoid where favorite_tab.id= '+cast(@id as varchar(20)) +' and Photostatus in (0,1,2) and ifuse in (0,1,4)';


  select @photoCount=(count(*)) from palbum_tab inner join favorite_tab as ft
   on ft.photoid=PALBUM_TAB.photoid
   where ft.id= @id and Photostatus in (0,1,2) and ifuse in (0,1,4)
end
else
begin
  if(@userPrivacy = 1)
  begin
   set @wherestr=' inner join favorite_tab as ft on ft.photoid=PALBUM_TAB.photoid
   left join (SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1)) as bt on bt.UserID=PALBUM_TAB.id
   where ft.id= '+cast(@id as varchar(20)) +'   and Photostatus in (1,2) and ifuse in (1,4) ';

   set @wherestr2=' inner join palbum_tab on palbum_tab.photoid=favorite_tab.photoid where favorite_tab.id= '+cast(@id as varchar(20)) +' and Photostatus in (1,2) and ifuse in (1,4)';

   select @photoCount=(count(*)) from palbum_tab inner join favorite_tab as ft on ft.photoid=PALBUM_TAB.photoid
   where ft.id= @id and Photostatus in (1,2) and ifuse in (1,4)


  end
  else
  begin
   set @wherestr=' inner join favorite_tab as ft on ft.photoid=PALBUM_TAB.photoid
   left join (SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1)) as bt on bt.UserID=PALBUM_TAB.id
   where ft.id= '+cast(@id as varchar(20)) +'   and Photostatus =1 and ifuse in (1,4) ';

   set @wherestr2=' inner join palbum_tab on palbum_tab.photoid=favorite_tab.photoid where favorite_tab.id= '+cast(@id as varchar(20)) +' and Photostatus =1 and ifuse in (1,4)';


   select @photoCount=(count(*)) from palbum_tab inner join favorite_tab as ft on ft.photoid=PALBUM_TAB.photoid
   where ft.id= @id and Photostatus =1 and ifuse in (1,4)

  end
end

set @orderstr=' order by palbum_tab.photoid desc '

if( (@minRecord !< @photoCount))
if(@photoCount > 0)
begin
set @minRecord = (@photoCount - @maxRecord)
end

--print @sql1
EXECUTE sp_executesql @sql1;
--=============

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetUserPhotosByTagName
@tagName  nvarchar(50),
@id   bigint,
@minRecord  varchar(20),
@maxRecord  varchar(10),
@userPrivacy  int,
@photoCount  int output
AS
SET NOCOUNT ON
--取得用户在某个标签下的图片
declare @sql1 nvarchar(2000);
declare @sql2 nvarchar(1000);
declare @wherestr nvarchar(500);
declare @wherestr2 nvarchar(500);
declare @orderstr nvarchar(200);
declare @min varchar(10);
set @photoCount = 0
---============
if(@userPrivacy = 0)
begin
  set @wherestr=' inner join PHOTO_Tag_TAB as ptt on ptt.photoid=PALBUM_TAB.photoid
   where tagName ='''+ @tagname+''' and ptt.id= '+cast(@id as varchar(20)) +' and Photostatus in (0,1,2) and ifuse in (0,1,4)';

  set @wherestr2=' inner join palbum_tab on palbum_tab.photoid=photo_tag_tab.photoid
  where photo_tag_tab.id= '+cast(@id as varchar(20)) +' and Photostatus in (0,1,2) and ifuse in (0,1,4) and tagname ='''+ @tagname+''' ';

  select @photoCount=(count(*)) from palbum_tab inner join PHOTO_Tag_TAB as ptt on ptt.photoid=PALBUM_TAB.photoid
   where tagName =@tagName and ptt.id=@id and Photostatus in (0,1,2) and ifuse in (0,1,4)
end
else
begin
  if(@userPrivacy = 1)
  begin
   set @wherestr=' inner join PHOTO_Tag_TAB as ptt on ptt.photoid=PALBUM_TAB.photoid
   where tagName ='''+ @tagName +''' and ptt.id= '+cast(@id as varchar(20)) +' and Photostatus in (1,2) and ifuse in (1,4) ';

   set @wherestr2=' inner join palbum_tab on palbum_tab.photoid=photo_tag_tab.photoid
   where photo_tag_tab.id= '+cast(@id as varchar(20)) +' and Photostatus in (1,2) and   ifuse in (1,4) and tagname ='''+ @tagname+''' ';

   select @photoCount=(count(*)) from palbum_tab inner join PHOTO_Tag_TAB as ptt on ptt.photoid=PALBUM_TAB.photoid
    where tagName =@tagName and ptt.id=@id and Photostatus in (1,2) and ifuse in (1,4)


  end
  else
  begin
   set @wherestr=' inner join PHOTO_Tag_TAB as ptt on ptt.photoid=PALBUM_TAB.photoid
   where tagName ='''+ @tagname +''' and ptt.id= '+cast(@id as varchar(20)) +' and Photostatus =1 and ifuse in (1,4) ';

   set @wherestr2=' inner join palbum_tab on palbum_tab.photoid=photo_tag_tab.photoid
   where photo_tag_tab.id= '+cast(@id as varchar(20)) +' and Photostatus=1 and   ifuse in (1,4) and tagname ='''+ @tagname+''' ';

   select @photoCount=(count(*)) from palbum_tab inner join PHOTO_Tag_TAB as ptt on ptt.photoid=PALBUM_TAB.photoid
    where tagName =@tagName and ptt.id=@id and Photostatus =1 and ifuse in (1,4)

  end
end

set @orderstr=' order by palbum_tab.photoid desc '

if( (@minRecord !< @photoCount))
if(@photoCount > 0)
begin
set @minRecord = (@photoCount - @maxRecord)
end

EXECUTE sp_executesql @sql1;

--=============
SELECT ut.*
FROM DB_user..USER_TAB ut
WHERE (ut.id = @id)

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetUserTagsByPhotoID
@PhotoID   bigint,
@id    bigint
AS
--取得用户的某个图片的标签
SET NOCOUNT ON
  select PhotoTagID, PhotoID, TagID, TagName, UserTagID, id from photo_tag_tab where photoid=@photoid and id=@id

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_GetUsersHasFaves
AS
SET NOCOUNT ON

--取得多于16个收藏的用户
SELECT ut.id,user_name,
isnull(bt.blogname,cast(ut.id as nvarchar(100))+'@') as blogname

FROM DB_user..USER_TAB ut
left join
(SELECT UserID, BlogName FROM db_blog..BLOG_Blog WHERE (BlogType=1))
as bt on bt.UserID=ut.id

inner join
(select id from favorite_tab
group by id having count(photoid)>=16) as fut on ut.id=fut.id

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_RecoverPhotosByPhotoID
@photoid  bigint,
@id   bigint,
@filetype varchar(20) output,
@pubtime datetime output,
@user_name varchar(40) output,
@photoOKCount bigint output
AS
SET NOCOUNT ON
--恢复被删图片
  declare @uploadMonth char(6)
  declare @filesize bigint
  set @filetype = ''
  set @filesize = 0
  SELECT * FROM PHOTO_TAG_TAB WHERE (PhotoID = @photoid)
  select @filesize=filesize,@pubtime=pubtime,@filetype=filetype,@id=id,@user_name=user_name from palbum_tab where photoid=@photoID and id=@id and ifuse=2

if(@filesize > 0)
begin

  if(month(@pubtime)<10)
   set @uploadMonth = cast(year(@pubtime) as char(4))+'0'+cast(month(@pubtime) as char(1))
  else
   set @uploadMonth =cast(year(@pubtime) as char(4))+cast(month(@pubtime) as char(2))


BEGIN TRAN
  update PHOTOSIZE_TAB set PhotoSize=(PhotoSize+@filesize),PhotoCount=(PhotoCount+1)
  where id=@id and UploadMonth=@uploadMonth


  UPDATE SECTION_TAB
  SET photocount = (photocount +1)
  WHERE (SECTION_TAB.sectionid IN
            (SELECT photo_section_tab.sectionid
           FROM photo_section_tab
           WHERE photoid = @photoid))

  update PALBUM_TAB set ifuse=1 where photoid =@photoid

  set @photoOKCount = 0
  select @photoOKCount=count(*) from palbum_tab where id=(select id from palbum_tab where photoid=@photoid and ifuse in (1,4))

COMMIT TRAN
end

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_getUserTagsByPopular

AS
--取得最热标签top50
SET NOCOUNT ON
select top 50 ptt.tagid,ptt.tagname,count(ptt.photoid) as photoCount from photo_tag_tab
   as ptt inner join palbum_tab as pt
   on ptt.photoid = pt.photoid where photostatus =1 and ifuse in ( 1,4)
   group by ptt.tagid,tagname
   order by photoCount desc

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.sp_getUserTagsByUserID
@id    bigint,
@userPrivacy   int
AS
--取得用户标签
SET NOCOUNT ON
if(@userPrivacy = 0)
  select ptt.tagid,ptt.tagname,count(ptt.photoid) as photoCount from photo_tag_tab
   as ptt left join palbum_tab as pt
   on ptt.photoid = pt.photoid where ptt.id=@id and Photostatus in (0,1,2) and ifuse in (0,1,4)
   group by ptt.tagid,tagname
   order by photoCount desc
else
begin
  if(@userPrivacy = 1)
  begin
   select ptt.tagid,ptt.tagname,count(ptt.photoid) as photoCount from photo_tag_tab
   as ptt inner join palbum_tab as pt
   on ptt.photoid = pt.photoid where ptt.id=@id and Photostatus in (1,2) and ifuse in (1,4)
   group by ptt.tagid,tagname
   order by photoCount desc
  end
  else
  begin
   select ptt.tagid,ptt.tagname,count(ptt.photoid) as photoCount from photo_tag_tab
   as ptt inner join palbum_tab as pt
   on ptt.photoid = pt.photoid where ptt.id=@id and photostatus =1 and ifuse in (1,4)
   group by ptt.tagid,tagname
   order by photoCount desc
  end
end

SELECT ut.id, ut.user_name
FROM DB_user..USER_TAB ut
WHERE (ut.id = @id)

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create proc dbo.dt_addtosourcecontrol_u
    @vchSourceSafeINI nvarchar(255) = '',
    @vchProjectName   nvarchar(255) ='',
    @vchComment       nvarchar(255) ='',
    @vchLoginName     nvarchar(255) ='',
    @vchPassword      nvarchar(255) =''

set nocount on

declare @iReturn int
declare @iObjectId int
select @iObjectId = 0

declare @iStreamObjectId int
select @iStreamObjectId = 0

declare @VSSGUID nvarchar(100)
select @VSSGUID = N'SQLVersionControl.VCS_SQL'

declare @vchDatabaseName varchar(255)
select @vchDatabaseName = db_name()

declare @iReturnValue int
select @iReturnValue = 0

declare @iPropertyObjectId int
declare @vchParentId nvarchar(255)

declare @iObjectCount int
select @iObjectCount = 0

exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT
if @iReturn <> 0 GOTO E_OAError

if @iReturn <> 0 GOTO E_OAError

exec @iReturn = sp_OAGetProperty @iObjectId, N'GetStreamObject', @iStreamObjectId OUT

if @iReturn <> 0 GOTO E_OAError

/* Set Database Properties */

begin tran SetProperties

/* add high level object */

exec @iPropertyObjectId = dbo.dt_adduserobject_vcs 'VCSProjectID'

select @vchParentId = CONVERT(nvarchar(255),@iPropertyObjectId)

    exec dbo.dt_setpropertybyid_u @iPropertyObjectId, 'VCSProjectID', @vchParentId , NULL
    exec dbo.dt_setpropertybyid_u @iPropertyObjectId, 'VCSProject' , @vchProjectName , NULL
    exec dbo.dt_setpropertybyid_u @iPropertyObjectId, 'VCSSourceSafeINI' , @vchSourceSafeINI , NULL
    exec dbo.dt_setpropertybyid_u @iPropertyObjectId, 'VCSSQLServer', @@SERVERNAME, NULL
    exec dbo.dt_setpropertybyid_u @iPropertyObjectId, 'VCSSQLDatabase', @vchDatabaseName, NULL

if @@error <> 0 GOTO E_General_Error

commit tran SetProperties

    declare cursorProcNames cursor for
        select convert(nvarchar(255), name) from sysobjects where type = N'P' and name not like N'dt_%'
    open cursorProcNames

    while 1 = 1
    begin
        declare @vchProcName nvarchar(255)
        fetch next from cursorProcNames into @vchProcName
        if @@fetch_status <> 0
            break

        select colid, text into #ProcLines
        from syscomments
        where id = object_id(@vchProcName)
        order by colid

        declare @iCurProcLine int
        declare @iProcLines int
        select @iCurProcLine = 1
        select @iProcLines = (select count(*) from #ProcLines)
        while @iCurProcLine <= @iProcLines
        begin
            declare @pos int
            select @pos = 1
            declare @iCurLineSize int
            select @iCurLineSize = len((select text from #ProcLines where colid = @iCurProcLine))
            while @pos <= @iCurLineSize
            begin
                declare @vchProcLinePiece nvarchar(255)
                select @vchProcLinePiece = convert(nvarchar(255),
                    substring((select text from #ProcLines where colid = @iCurProcLine),
                              @pos, 255 ))
                exec @iReturn = sp_OAMethod @iStreamObjectId, N'AddStream', @iReturnValue OUT, @vchProcLinePiece
                if @iReturn <> 0 GOTO E_OAError
                select @pos = @pos + 255
            end
            select @iCurProcLine = @iCurProcLine + 1
        end
        drop table #ProcLines

if @iReturn = 0 select @iObjectCount = @iObjectCount + 1

end

CleanUp:
close cursorProcNames
deallocate cursorProcNames
    select @vchProjectName
    select @iObjectCount
    return

E_General_Error:
/* this is an all or nothing. No specific error messages */
goto CleanUp

E_OAError:
exec dbo.dt_displayoaerror_u @iObjectId, @iReturn
goto CleanUp

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

posted on 2005-04-27 09:44 Jacky 阅读(524) 评论(0) 收藏举报

刷新页面返回顶部

公告

导航