升级脚本.sql

CallSun 人才招聘求职系统 V2.20

    return
  END

  BEGIN TRANSACTION InvitePersonUpdate

-- 更新企业邀请记录
  UPDATE rc_com_invite SET ci_invite_content=@memo, ci_invite_date=getdate(), ci_state=0 WHERE ci_id=@ciID

-- 更新个人面试记录
  UPDATE rc_per_interview SET pi_invite_content=@memo, pi_invite_date=getdate(), pi_state=0 WHERE u_id_com=@cID AND u_id_per=@pUID AND j_id=@jid

  if @@ERROR > 0
  BEGIN
    ROLLBACK TRANSACTION InvitePersonUpdate
    set @result = -1
    return
  END

  COMMIT TRANSACTION InvitePersonUpdate

  set @result = 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_Pagination   -- 获取指定页的数据

@tName varchar(255),             -- 表名
@getFields varchar(1000),        -- 需要返回的列
@keyField varchar(255),          -- 排序的字段名
@PageSize int = 10,              -- 页尺寸
@PageIndex int = 1,              -- 页码
@OrderType bit = 0,              -- 设置排序类型, 非 0 值则降序
@sQuery varchar(1500) = ''       -- 查询条件 (注意: 不要加 WHERE)

AS

declare @strSQL   nvarchar(4000) -- 主语句
declare @strTmp   varchar(110)   -- 临时变量
declare @strOrder varchar(400)   -- 排序类型

if @OrderType != 0
begin
  set @strTmp = '<(SELECT MIN'
  set @strOrder = ' ORDER BY ' + @keyField +' DESC'
end
else                             --如果@OrderType不是0，就执行降序，这句很重要！
begin
  set @strTmp = '>(SELECT MAX'
  set @strOrder = ' ORDER BY ' + @keyField +' ASC'
end

if @PageIndex = 1                --如果是第一页就执行以上代码，这样会加快执行速度
begin
  if @sQuery != ''
    set @strSQL = 'SELECT TOP ' + str(@PageSize) + ' ' + @getFields + ' FROM ' + @tName + ' WHERE ' + @sQuery + ' ' + @strOrder
  else
    set @strSQL = 'SELECT TOP ' + str(@PageSize) + ' ' + @getFields + ' FROM ' + @tName + ' ' + @strOrder
end
else                             --以下代码赋予了@strSQL以真正执行的SQL代码
begin
  set @strSQL = 'SELECT TOP ' + str(@PageSize) + ' ' + @getFields + ' FROM '
    + @tName + ' WHERE ' + @keyField + '' + @strTmp + '('+ @keyField + ') FROM (SELECT TOP ' + str((@PageIndex-1)*@PageSize) + ' ' + @keyField + ' FROM ' + @tName + '' + @strOrder + ') AS tblTmp)' + @strOrder
  if @sQuery != ''
    set @strSQL = 'SELECT TOP ' + str(@PageSize) + ' ' + @getFields + ' FROM '
      + @tName + ' WHERE ' + @keyField + '' + @strTmp + '('
      + @keyField + ') FROM (SELECT TOP ' + str((@PageIndex-1)*@PageSize) + ' '
      + @keyField + ' FROM ' + @tName + ' WHERE ' + @sQuery + ' '
      + @strOrder + ') AS tblTmp) AND ' + @sQuery + ' ' + @strOrder
end
--print @strSQL
exec (@strSQL)
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_Pagination2
  @tName varchar(255),
  @getFields varchar(1000),
  @keyField varchar(255),
  @orderField varchar(255),
  @PageSize int = 10,              --页码
  @PageIndex int = 1,       --每页容纳的记录数
  @OrderType bit = 0,
  @sQuery varchar(1500) = ''      --排序字段及规则

AS

declare @strOrder varchar(400)

if @OrderType != 0
begin
  set @strOrder = ' ORDER BY ' + @orderField + ' DESC'
end
else
begin
  set @strOrder = ' ORDER BY ' + @orderField + ' ASC'
end

DECLARE @Str nvarchar(4000)

if @sQuery = ''
begin
SET @Str='SELECT TOP '+str(@PageSize)+' '+@getFields+' FROM '+@tName+' WHERE '+@keyField+
  ' NOT IN (SELECT TOP '+str(@PageSize*(@PageIndex-1))+' '+@keyField+' FROM '+@tName+@strOrder+') '+@strOrder
end
else
begin
SET @Str='SELECT TOP '+str(@PageSize)+' '+@getFields+' FROM '+@tName+' WHERE '+@keyField+
  ' NOT IN (SELECT TOP '+str(@PageSize*(@PageIndex-1))+' '+@keyField+' FROM '+@tName+' WHERE '+@sQuery+''+@strOrder+') '+
  ' AND '+@sQuery+@strOrder
end

PRINT @Str

EXEC sp_ExecuteSql @Str
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_PaginationCount   -- 获取数据的总数

@tName varchar(255),             -- 表名
@iCount int output,              -- 总数
@sQuery varchar(1500) = ''       -- 查询条件 (注意: 不要加 WHERE)

AS

declare @strSQL   nvarchar(4000) -- 主语句

if @sQuery !=''
  set @strSQL = 'SELECT @c=COUNT(*) FROM ' + @tName + ' WHERE ' + @sQuery
else
  set @strSQL = 'SELECT @c=COUNT(*) FROM ' + @tName + ''
exec sp_executesql @strSQL, N'@c int output', @iCount output
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_PersonCheckLogin
(
  @uname varchar(50),
  @upwd varchar(50),
  @uarea varchar(100),
  @uip varchar(20),
  @uid int output
)
AS

  declare @pwd varchar(50)

-- 根据用户名选出记录
  SELECT @uid=u_id, @pwd=u_password, @uarea=u_areacode FROM rc_user WHERE (u_type<10) AND (u_loginname=@uname)
  if @uid is NULL
  BEGIN
    set @uid = -1
    return
  END
-- 检查用户密码
  if @pwd <> @upwd
  BEGIN
    set @uid = -1
    return
  END
-- 检查用户地区权限

-- 更新个人的登录信息
  UPDATE rc_user SET u_countlogin=u_countlogin+1, u_loginip=@uip, u_date_lastlogin=getdate() WHERE u_id=@uid
  if @@ERROR > 0
  BEGIN
    set @uid = -1
    return
  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_PersonRegistration
(
  @uname varchar(50),
  @upwd varchar(50),
  @uidcode varchar(20),
  @realname varchar(50),
  @uquestion varchar(100),
  @uanswer varchar(50),
  @utype int,
  @uarea varchar(100),
  @uip varchar(20),
  @uid int output
)
AS

  BEGIN TRANSACTION PersonRegistration

-- 登录用户表里增加一条记录
  INSERT INTO rc_user(u_loginname, u_password, u_question, u_answer, u_type, r_id, u_areacode, u_loginip) VALUES(@uname, @upwd, @uquestion, @uanswer, @utype, 1, @uarea, @uip)
-- 选出新记录的编号
  SELECT TOP 1 @uid=u_id FROM rc_user ORDER BY u_id DESC
-- 个人表里增加一条记录
  INSERT INTO rc_person(u_id, p_idcode, p_realname) VALUES(@uid, @uidcode, @realname)
  if @@ERROR > 0
  BEGIN
    ROLLBACK TRANSACTION PersonRegistration
    set @uid = -1
    return
  END

  COMMIT TRANSACTION PersonRegistration

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_UpdateUserJifen
(
  @uid int,
  @jltype int,  -- 0=浏览、1=注册
  @jlsrc varchar(200),
  @jlip varchar(200),
  @jf int,
  @result int output  -- -1=用户不存在，出错等、0=、1=正常、2=增加积分
)
AS

declare @utype int

  SELECT @utype=u_type FROM rc_user WHERE u_id=@uid
  if @utype is NULL
  BEGIN
    set @result = -1
    return
  END

  if @jltype = 0  -- 浏览要先检查ip的时间限制
  BEGIN
    declare @dt datetime
    SELECT @dt=ji_date FROM jf_iplog WHERE u_id=@uid AND ji_ip=@jlip
    if @dt is NULL
      INSERT INTO jf_iplog(u_id, ji_ip) VALUES(@uid, @jlip)
    else
    BEGIN
      if datediff(day, @dt, getdate()) = 0 and datediff(hh, @dt, getdate()) < 24  -- 同一天 and 24小时内不计算
      BEGIN
        set @result = 1
        return
      END
      else
        UPDATE jf_iplog SET ji_date=getdate(), ji_count=ji_count+1 WHERE u_id=@uid AND ji_ip=@jlip
    END
  END

  if @jltype = 1  -- 注册不用检查
  BEGIN
    set @result = 1
  END

-- 记录本次信息
  INSERT INTO jf_log(u_id, jl_type, jl_source, jl_ip) VALUES(@uid, @jltype, @jlsrc, @jlip)

declare @jfid int

-- 根据用户ID选出记录，不存在就新增加一条
  SELECT @jfid=jf_id FROM jf_data WHERE u_id=@uid
  if @jfid is NULL
    INSERT INTO jf_data(u_id, jf_name) SELECT u_id, u_loginname FROM rc_user WHERE u_id=@uid

-- 更新分值和记录数量
  if @jltype=0
    UPDATE jf_data SET jf_value=jf_value+@jf, jf_valuetoday=jf_valuetoday+@jf, jf_viewcount=jf_viewcount+1, jf_viewtoday=jf_viewtoday+1 WHERE u_id=@uid
  if @jltype=1
    UPDATE jf_data SET jf_value=jf_value+@jf, jf_valuetoday=jf_valuetoday+@jf, jf_regcount=jf_regcount+1, jf_regtoday=jf_regtoday+1 WHERE u_id=@uid

  if @@ERROR > 0
  BEGIN
    set @result = -1
    return
  END
  set @result = 2

return
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

-- 数据的升级

update rc_person set p_countview=pr_countview,p_state=pr_state,p_update=pr_update,p_expire=pr_expire,p_settings=pr_settings
from rc_person P, rc_per_resume R where P.u_id=R.u_id
GO

update rc_sys_admin set sa_settings='0594|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1' where sa_loginname='admin'