升级脚本.sql

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

  INSERT INTO rc_com_apply(u_id_com, u_id_per, j_id, j_title, ca_update, ca_realname, ca_sex, ca_photo, ca_people, ca_polity, ca_birthday, ca_marry, ca_experience, ca_wage, ca_graduation, ca_education, ca_professionname, ca_profession, ca_language, ca_language_level, ca_place, ca_linkman, ca_telephone, ca_email, ca_qq, ca_address, ca_code, ca_prefer_jobclass1, ca_prefer_jobtitle1, ca_prefer_jobclass2, ca_prefer_jobtitle2, ca_introduce, ca_training, ca_skill, ca_award)
  SELECT @cUID, @pUID, @jID, @jobTitle, p_update, p_realname, p_sex, p_photo, p_people, p_polity, p_birthday, p_marry, p_experience, p_wage, p_graduation, p_education, p_professionname, p_profession, p_language, p_language_level, p_place, p_linkman, p_telephone, p_email, p_qq, p_address, p_code, p_prefer_jobclass1, p_prefer_jobtitle1, p_prefer_jobclass2, p_prefer_jobtitle2, p_introduce, p_training, p_skill, p_award FROM rc_person WHERE u_id=@pUID

  UPDATE rc_job SET j_countapply=j_countapply+1 WHERE j_id=@jID

  INSERT INTO rc_per_apply(u_id_per, u_id_com, j_id, j_title, pa_comname) VALUES(@pUID, @cUID, @jID, @jobTitle, @comName)

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

  COMMIT TRANSACTION ApplyForAJob

  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_ChangePassword
(
  @uid int,
  @uoldpwd varchar(50),
  @unewpwd varchar(50),
  @result int output
)
AS

  declare @pwd varchar(50)

-- 根据用户ID选出记录
  SELECT @pwd=u_password FROM rc_user WHERE u_id=@uid
  if @pwd is NULL
  BEGIN
    set @result = -1
    return
  END
-- 检查用户密码
  if @pwd <> @uoldpwd
  BEGIN
    set @result = -1
    return
  END
-- 更新用户密码
  UPDATE rc_user SET u_password=@unewpwd WHERE u_id=@uid
-- 有必要可以加入日志
  if @@ERROR > 0
  BEGIN
    set @result = -1
    return
  END
  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_CompanyApplyReply
(
  @cUID int,              -- 发送人＝职位的企业的ID
  @cID int,               -- 企业ID
  @comName varchar(100),  -- 发送名称
  @pUID int,              -- 接收人＝申请职位的个人ID
  @jID int,               -- 职位的ID
  @title varchar(100),    -- 消息标题
  @content varchar(2000), -- 消息内容
  @result int output
)
AS

declare @mid int

  BEGIN TRANSACTION CompanyApplyReply

-- 插入新的短消息
  INSERT INTO rc_message(u_id_send, u_id_recv, m_sendname, m_title, m_content) VALUES(@cUID, @pUID, @comName, @title, @content)
  SELECT TOP 1 @mid=m_id FROM rc_message WHERE (u_id_send=@cUID) AND (u_id_recv=@pUID) ORDER BY m_id DESC
  if @mid IS NULL
  BEGIN
    ROLLBACK TRANSACTION CompanyApplyReply
    set @result = -1
    return
  END
-- 更新企业应聘记录状态
  UPDATE rc_com_apply SET ca_state=2 WHERE (u_id_com=@cID) AND (u_id_per=@pUID) AND (j_id=@jID)
-- 更新个人应聘记录状态
  UPDATE rc_per_apply SET pa_state=1, m_id=@mid WHERE (u_id_per=@pUID) AND (u_id_com=@cID) AND (j_id=@jID)

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

  COMMIT TRANSACTION CompanyApplyReply

  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_CompanyCheckLogin
(
  @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_CompanyRegistration
(
  @uname varchar(50),
  @upwd varchar(50),
  @ucomname varchar(100),
  @uquestion varchar(100),
  @uanswer varchar(50),
  @utype int,
  @uarea varchar(100),
  @uip varchar(20),
  @uid int output
)
AS

  BEGIN TRANSACTION CompanyRegistration

-- 登录用户表里增加一条记录
  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, 2, @uarea, @uip)
-- 选出新记录的编号
  SELECT TOP 1 @uid=u_id FROM rc_user ORDER BY u_id DESC
-- 企业表里增加一条记录
  INSERT INTO rc_company(u_id, c_name, c_license) VALUES(@uid, @ucomname, '')
  if @@ERROR > 0
  BEGIN
    ROLLBACK TRANSACTION CompanyRegistration
    set @uid = -1
    return
  END

  COMMIT TRANSACTION CompanyRegistration

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_InterviewUpdate
(
  @piID int,
  @cID int,           -- 发出邀请的企业ID(是企业表的ID)
  @pUID int,          -- 被邀请的个人ID
  @jID int,           -- 邀请的职位ID
  @memo varchar(200), -- 邀请的内容
  @result int output  -- 处理结果(-1=出错，1=正常)
)
AS

  declare @id int
  SELECT @id=pi_id FROM rc_per_interview WHERE u_id_com=@cID AND u_id_per=@pUID AND j_id=@jID
  if @id <> @piID
  BEGIN
    set @result = -1
    return
  END

  BEGIN TRANSACTION InterviewUpdate

-- 更新企业邀请记录
  UPDATE rc_com_invite SET ci_reply_content=@memo, ci_reply_date=getdate(), ci_state=1 WHERE u_id_com=@cID AND u_id_per=@pUID AND j_id=@jid

-- 更新个人面试记录
  UPDATE rc_per_interview SET pi_reply_content=@memo, pi_reply_date=getdate(), pi_state=1 WHERE pi_id=@piID

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

  COMMIT TRANSACTION InterviewUpdate

  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_InvitePerson
(
  @cID int,           -- 发出邀请的企业ID(是企业表的ID)
  @pUID int,          -- 被邀请的个人ID
  @jID int,           -- 邀请的职位ID
  @memo varchar(200), -- 邀请的内容
  @result int output  -- 处理结果(-1=出错，1=正常，2=已经邀请过了)
)
AS

-- 检查是否重复邀请
  declare @tmp int
  SELECT @tmp=ci_id FROM rc_com_invite WHERE u_id_com=@cID AND u_id_per=@pUID AND j_id=@jID
  if @tmp <> NULL
  BEGIN
    set @result = 2
    return
  END

  BEGIN TRANSACTION InvitePerson

-- 删除重复的邀请
  DELETE FROM rc_per_interview WHERE u_id_com=@cID AND u_id_per=@pUID AND j_id=@jID

-- 增加企业邀请记录
  INSERT INTO rc_com_invite(u_id_com, u_id_per, j_id, j_title, ci_realname, ci_sex, ci_invite_content)
  SELECT @cid, @puid, @jid, j_title, p_realname, p_sex, @memo FROM rc_person, rc_job WHERE rc_person.u_id=@puid AND j_id=@jid

-- 增加个人面试记录
  INSERT INTO rc_per_interview(u_id_per, u_id_com, j_id, j_title, pi_comname, pi_invite_content)
  SELECT @puid, @cid, @jid, j_title, c_name, @memo FROM rc_company, rc_job WHERE c_id=@cID AND j_id=@jid

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

  COMMIT TRANSACTION InvitePerson

  set @result = 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_InvitePersonUpdate
(
  @ciID int,
  @cID int,           -- 发出邀请的企业ID(是企业表的ID)
  @pUID int,          -- 被邀请的个人ID
  @jID int,           -- 邀请的职位ID
  @memo varchar(200), -- 邀请的内容
  @result int output  -- 处理结果(-1=出错，1=正常)
)
AS

  declare @id int
  SELECT @id=ci_id FROM rc_com_invite WHERE u_id_com=@cID AND u_id_per=@pUID AND j_id=@jID
  if @id <> @ciID
  BEGIN
    set @result = -1