人事系统-人事子系统:人事档案 - 军军小站|张军博客

1. 表：人事档案 Hrgeneral

      CREATE TABLE [dbo].[Hrgeneral](

	[hrcode] [varchar](50) NOT NULL,	--员工工号

	[hrname] [varchar](16) NULL,		--员工姓名

	[oldName] [varchar](50) NULL,		--曾用名

	[CardNo] [varchar](50) NULL,		--考虑对应的IC卡号码

	[ID] [varchar](22) NULL,			--身份证号码

	[birthday] [datetime] NULL,			--出生日期

	[Age]  AS (round(datediff(month,[birthday],getdate()),0) / 12),	--根据出生日期取年龄

	[gender] [varchar](8) NULL,			--性别 1为男 2为女

	[folk] [varchar](16) NULL,			--民族ID 引用表

	[folkname] [varchar](50) NULL,		--民族名称 引用表

	[nativeplace] [varchar](40) NULL,	--籍贯ID 引用表

	[nativeplacename] [varchar](50) NULL,	--籍贯ID 引用表

	[politicalview] [varchar](8) NULL,	--政治面貌，引用表

	[politicalviewname] [varchar](50) NULL,	--政治面貌，引用表

	[marriage] [varchar](8) NULL,	--婚姻情况 引用表

	[health] [varchar](500) NULL,	--身体情况 引用表

	[bodylength] [money] NULL,		--身高

	[bodyweight] [money] NULL,		--体重

	[mobile] [varchar](20) NULL,	--手机号码

	[email] [varchar](100) NULL,	--电子邮箱

	[homeaddress] [varchar](100) NULL,	--家庭地址

	[homephone] [varchar](20) NULL,		--家庭电话

	[curaddress] [varchar](100) NULL,	--暂住地址

	[telephone] [varchar](20) NULL,		

	[tekTitle] [varchar](16) NULL,		

	[tekTitlename] [varchar](50) NULL,	

	[education] [varchar](20) NULL,		--学历

	[educationcode] [varchar](50) NULL,	

	[school] [varchar](255) NULL,		

	[special] [varchar](50) NULL,		

	[secondspecial] [varchar](50) NULL,

	[computer] [varchar](16) NULL,

	[languagetype] [varchar](16) NULL,

	[languagelevel] [varchar](16) NULL,

	[otherspecial] [varchar](50) NULL,

	[companyid] [dbo].[orgcode] NULL,	--公司代码 引用表

	[companyname] [varchar](50) NULL,	--公司名称	引用表

	[cccode] [dbo].[orgcode] NULL,		--部门ID	引用表

	[ccname] [varchar](50) NULL,		--部门名称 引用表

	[positionid] [varchar](20) NULL,	--岗位定级	引用表

	[positionname] [varchar](50) NULL,	--岗位定级	引用表

	[jobtime] [datetime] NULL,			--入职时间

	[TrialTime] [int] NULL,		

	[workyears] [int] NULL,

	[joinfundtime] [datetime] NULL,

	[jobstate]  AS (case when (isnull([Leavedate],convert(datetime,'1900-01-01',120)) < '1902-01-01') then '在职' else '离职' end),  --工作状态

	[jobtype] [varchar](20) NULL,

	[leavedate] [datetime] NULL,

	[leavetype] [varchar](20) NULL,

	[LeaveName] [varchar](50) NULL,

	[MedicareCode] [varchar](50) NULL,

	[MedicareLevel] [int] NULL,

	[positionlevel] [varchar](20) NULL,

	[positionlevelname] [varchar](50) NULL,

	[positionsubsidy] [money] NULL,

	[employeeType] [varchar](50) NULL,

	[EmployeetypeName] [varchar](50) NULL,

	[hrtypecode] [varchar](50) NULL,	--薪资类型 引用表

	[hrtypename] [varchar](50) NULL,	--薪资类型	引用表

	[PhotoDir] [varchar](30) NULL,

	[enterdate] [datetime] NULL,		--修改时间

	[isvoucher] [varchar](20) NULL,

	[memo] [varchar](200) NULL,

	[cv1] [varchar](50) NULL,

	[cv1name] [varchar](50) NULL,

	[cv2] [varchar](50) NULL,

	[cv2name] [varchar](50) NULL,

	[cv3] [varchar](50) NULL,

	[cv3name] [varchar](50) NULL,

	[CompMark] [varchar](50) NULL,

	[CompMarkName] [varchar](200) NULL,

	[AgentMan] [varchar](50) NULL,

	[ActiveAgent] [int] NULL,

	[Notesid] [varchar](50) NULL,

	[birthmonth]  AS (substring(convert(varchar(10),[birthday],121),6,2)),

	[AuditusrYN] [int] NULL,

	[deduction] [varchar](50) NULL,

	[deductionName] [varchar](50) NULL,

	[entercode] [varchar](50) NULL,

	[TrainDebitDate] [datetime] NULL,

	[ReplFlgDelYN] [int] NULL,

	[shorthrcode] [varchar](50) NULL,

	[password] [varchar](15) NULL,

	[password2] [varchar](50) NULL,

	[crewcode] [varchar](30) NULL,

	[crewname] [varchar](30) NULL)



--设置主键

ALTER TABLE Hrgeneral ADD CONSTRAINT PK_Hrgeneral primary key(hrcode)

--设置约束

ALTER TABLE [dbo].[Hrgeneral] ADD  CONSTRAINT [DF_hrgeneral_bodylength]  DEFAULT (0) FOR [bodylength]



ALTER TABLE [dbo].[Hrgeneral] ADD  CONSTRAINT [DF_hrgeneral_bodyweight]  DEFAULT (0) FOR [bodyweight]



ALTER TABLE [dbo].[Hrgeneral] ADD  CONSTRAINT [DF_hrgeneral_positionsubsidy]  DEFAULT (0) FOR [positionsubsidy]

--创建非聚集索引

CREATE NONCLUSTERED INDEX [IX_Hrgeneral] ON [dbo].[Hrgeneral] 

(

	[CardNo] ASC,

	[hrcode] ASC

)

2. 功能：新员工入职

2.1 表：入职表

      CREATE TABLE [dbo].[hrOfferCome_h](

	[DocCode] [varchar](20) NOT NULL,	--单号 自动生成

	[FormID] [int] NULL,				--表类型ID  引用

	[DocDate] [datetime] NULL,			--单据日期

	[DocType] [varchar](20) NULL,		--单据类型	引用formid

	[refcode] [varchar](50) NULL,		--关联单据

	[DocStatus] [int] NULL,				--单据状态 0-未保存 50-保存 100-确定

	[periodid] [varchar](20) NULL,		--

	[EnterName] [varchar](100) NULL,

	[EnterDate] [datetime] NULL,

	[ModifyName] [varchar](100) NULL,

	[ModifyDate] [datetime] NULL,

	[PostName] [varchar](100) NULL,

	[PostDate] [datetime] NULL,

	[hrcode] [varchar](50) NULL,		--工号 自动生成

	[hrname] [varchar](16) NULL,		--姓名

	[oldName] [varchar](16) NULL,

	[birthday] [datetime] NULL,

	[CardNo] [varchar](50) NULL,

	[ID] [varchar](22) NULL,

	[gender] [varchar](8) NULL,

	[folk] [varchar](16) NULL,

	[folkname] [varchar](50) NULL,

	[nativeplace] [varchar](40) NULL,

	[nativeplacename] [varchar](50) NULL,

	[politicalview] [varchar](8) NULL,

	[politicalviewname] [varchar](50) NULL,

	[marriage] [varchar](8) NULL,

	[health] [varchar](500) NULL,

	[jobtime] [datetime] NULL,

	[TrialTime] [int] NULL,

	[bodylength] [money] NULL,

	[bodyweight] [money] NULL,

	[mobile] [varchar](20) NULL,

	[homeaddress] [varchar](100) NULL,

	[homephone] [varchar](20) NULL,

	[tekTitle] [varchar](16) NULL,

	[tekTitlename] [varchar](50) NULL,

	[education] [varchar](20) NULL,

	[educationcode] [varchar](50) NULL,

	[school] [varchar](255) NULL,

	[special] [varchar](50) NULL,

	[companyid] [dbo].[orgcode] NULL,

	[companyname] [varchar](50) NULL,

	[origcompanyid] [dbo].[orgcode] NULL,

	[cccode] [dbo].[orgcode] NULL,

	[ccname] [varchar](50) NULL,

	[positionid] [varchar](20) NULL,

	[positionname] [varchar](50) NULL,

	[positionlevel] [varchar](20) NULL,

	[positionlevelname] [varchar](50) NULL,

	[positionsubsidy] [money] NULL,

	[employeeType] [varchar](50) NULL,

	[EmployeetypeName] [varchar](50) NULL,

	[hrtypecode] [varchar](50) NULL,

	[hrtypename] [varchar](50) NULL,

	[isvoucher] [varchar](20) NULL,

	[memo] [varchar](200) NULL,

	[cv1] [varchar](50) NULL,

	[cv1name] [varchar](50) NULL,

	[curaddress] [varchar](100) NULL,

	[blClosed] [int] NULL,

	[BankNo] [varchar](20) NULL,

	[BankName] [varchar](60) NULL,

	[BankAcct] [varchar](30) NULL,

	[entercode] [varchar](50) NULL,

	[PhotoDir] [varchar](30) NULL,

	[ClearDoccode] [varchar](50) NULL,

	[shorthrcode] [varchar](50) NULL)

--主键

ALTER TABLE hrOfferCome_h ADD CONSTRAINT PK_hrOfferCome_h PRIMARY KEY(DOCCODE)

--约束

ALTER TABLE [dbo].[hrOfferCome_h] ADD  CONSTRAINT [DF__hrinput_h__bodyl__54DA7ABA]  DEFAULT (0) FOR [bodylength]



ALTER TABLE [dbo].[hrOfferCome_h] ADD  CONSTRAINT [DF__hrinput_h__bodyw__55CE9EF3]  DEFAULT (0) FOR [bodyweight]



ALTER TABLE [dbo].[hrOfferCome_h] ADD  CONSTRAINT [DF__hrinput_h__posit__56C2C32C]  DEFAULT (0) FOR [positionsubsidy]

可以添加一个明细表，用来记录员工的工作经历

2.2 存储过程：p_hrCreateHrcod --产生新工号，并更新到表头

             

CREATE      PROCEDURE [dbo].[p_hrCreateHrcode] ( @doccode VARCHAR(20) )    

AS     

    BEGIN      

        DECLARE @newHrcode VARCHAR(10) ,    

            @companyid VARCHAR(10),  

            @hrcode Varchar(10)  

        SELECT  @companyid = companyid,@hrcode=hrcode     

        FROM    hrOfferCome_h    

        WHERE   doccode = @doccode      

/*    

        SELECT  @newHrcode = MAX(hrcode) + 1    

        FROM    hrgeneral a    

                JOIN Ms_HrCodeByCompany b ON a.companyid = b.companyid    

        WHERE   a.hrcode BETWEEN b.hrbeginno AND b.hrendno    

                AND a.companyid = @companyid      

  */    

  --已有工号（取消确认的情况）不再产生    

        IF EXISTS ( SELECT  1    

                    FROM    dbo.hrOfferCome_h    

                    WHERE   ISNULL(hrcode, '') <> ''    

                            AND ISNULL(companyid,'')=ISNULL(origcompanyid,'')  

                            AND DocCode = @doccode )  and not Exists(select hrcode from hrgeneral where hrcode=@hrcode)  

            RETURN    

        --生成新工号      

        SELECT  @newHrcode = newhrid    

        FROM    f_hrNextHrcode(@companyid)    

      

        IF EXISTS ( SELECT  *    

                    FROM    Ms_HrCodeByCompany    

                    WHERE   companyid = @companyid    

                            AND HrEndNo < @newHrcode )     

            BEGIN      

                RAISERROR('产生的工号已经超出范围，请联系管理员',16,1)   --检查(Ms_HrCodeByCompany)    

                RETURN       

            END       

        ELSE     

            BEGIN      

                UPDATE  hrOfferCome_h    

                SET     Hrcode = @newHrcode ,shorthrcode=substring(@newHrcode,1,1)+substring(@newHrcode,3,4)  

                WHERE   doccode = @doccode      

                    

                UPDATE  Ms_HrCodeByCompany    

                SET     cuHrcode = @newHrcode    

                WHERE   companyid = @companyid      

            END      

    END

2.3 插入到hrgeneral(根据录入的单据，将数据插入到人事主表hrgeneral之中)

2.4 根据实际情况，根据录入表单数据，更新数据到：员工生命周期表（日志表），工作经历表，银行账号表，工号跟考勤卡号对应起来。考勤卡号的生成可以参考工号的生成。

3. 功能：员工资料修正

可以另外设置一个表，录入数据之后修改人事主表，或者引用之前表的数据。银行账号表之类的，如果数据修改，也需同步更新。

4. 功能：人员岗位调动

　4.1：表：hrdochd

      CREATE TABLE [dbo].[hrDocHD](

	[Doccode] [varchar](50) NOT NULL primary key ,

	[Formid] [int] NULL,

	[Docdate] [datetime] NULL,

	[Periodid] [varchar](50) NULL,

	[ChargesPeriodid] [varchar](50) NULL,

	[Doctype] [varchar](50) NULL,

	[Companyid] [varchar](50) NULL,

	[CompanyName] [varchar](50) NULL,

	[ccCode] [varchar](50) NULL,

	[ccName] [varchar](50) NULL,

	[hrCode] [varchar](50) NULL,

	[hrName] [varchar](50) NULL,

	[effbeginday] [datetime] NULL,

	[effEndday] [datetime] NULL,

	[Amnt] [money] NULL,

	[organizor] [varchar](50) NULL,

	[organizorname] [varchar](50) NULL,

	[Applyer] [varchar](50) NULL,

	[ApplyName] [varchar](50) NULL,

	[applydate] [datetime] NULL,

	[blclosed] [int] NULL,

	[blscrap] [int] NULL,

	[ClearDocCode] [varchar](50) NULL,

	[Refcode] [varchar](50) NULL,

	[refformid] [int] NULL,

	[docstatus] [int] NULL,

	[Matcode] [varchar](50) NULL,

	[MatName] [varchar](50) NULL,

	[Linkdoccode] [varchar](50) NULL,

	[Linkformid] [varchar](50) NULL,

	[EnterName] [varchar](50) NULL,

	[EnterDate] [datetime] NULL,

	[ModifyName] [varchar](50) NULL,

	[Modifydate] [datetime] NULL,

	[PostName] [varchar](50) NULL,

	[Postdate] [datetime] NULL,

	[remarks1] [varchar](200) NULL,

	[remarks2] [varchar](50) NULL,

	[remarks3] [varchar](50) NULL,

	[ReplFlgDelYN] [int] NULL)

　4.2 插入员工生命周期（日志表），更新人事主表。

5. 转正登记

人事主表中，有一个参数，是否是正式员工。确认转正表之后，修改人事主表员工状态，转正日期等。插入到日志表。

6. 培训记录

插入到日志表中即可

7. 离职办理

　7.1 主表：　　hrdochd

　　7.2 明细表：　　HrDocdetail

      CREATE TABLE [dbo].[HrDocdetail](

	[Doccode] [varchar](50) NOT NULL,

	[docitem] [int] NULL,

	[Rowid] [varchar](10) NOT NULL primary key ,

	[Hrcode] [varchar](50) NULL,

	[HrName] [varchar](50) NULL,

	[effBeginday] [datetime] NULL,

	[effEndday] [datetime] NULL,

	[Content] [varchar](500) NULL,

	[Amt] [money] NULL,

	[ApplyDate] [datetime] NULL,

	[Confirmdate] [datetime] NULL,

	[HandleDate] [datetime] NULL,

	[OrigPosition] [varchar](50) NULL,

	[OrigPositionname] [varchar](50) NULL,

	[OrigCccode] [varchar](50) NULL,

	[OrigCcname] [varchar](50) NULL,

	[NewPosition] [varchar](50) NULL,

	[NewPositionname] [varchar](50) NULL,

	[NewCccode] [varchar](50) NULL,

	[NewCcname] [varchar](50) NULL,

	[Matcode] [varchar](50) NULL,

	[MatName] [varchar](50) NULL,

	[Memo] [varchar](200) NULL,

	[refdoccode] [varchar](50) NULL,

	[refformid] [varchar](50) NULL,

	[refrowid] [varchar](50) NULL,

	[Linkdoccode] [varchar](50) NULL,

	[Linkformid] [varchar](50) NULL)

　7.3 更新人事主表信息：离职日期，最新录入日期等；插入日志表

　　7.4 存储过程：p_hrhrDocCreation--更新辞职记录

      CREATE       procedure p_hrhrDocCreation  

@doccode varchar(20),  

@fag int     -- 1,培训记录 2,辞职记录 3,岗位 4,奖励，5，领用  

as   

  

set nocount on  

  

if @fag not in (1,2,3,4,5)  

begin  

  raiserror('参数出错!',16,1);  

  return  

   

end  

  

if @fag=1  

  

begin  

  

 if exists(  select 1 from Hrpsm_train where doccode =@doccode)  

 begin  

  raiserror('培训记录已存在!',16,1);  

  return  

 end  

 insert into Hrpsm_train  

  (  

  Doccode,Rowid, hrcode ,hrname ,amt ,  

  organizor ,matcode,matname,  

  start_date ,end_date ,RegDate ,  

  remark ,EnterName ,EnterDate , Formid   

  )  

  

  

  

 select   

  Doccode ,Rowid ,Hrcode ,HrName ,Amt ,  

  Organizor ,matcode,matname,  

  effBeginday ,effEndday ,  EnterDate,  

  Memo,EnterName ,EnterDate , Formid   

  

  from vHrDocDetail where doccode=@doccode  

  

end  

  

  

  

if @fag=2  

  

begin     

  

select  * from Hrpsm_leave where formid='27203'  

  

 if exists(  select 1 from Hrpsm_leave where doccode =@doccode)  

 begin  

  raiserror('辞职记录已存在!',16,1);  

  return  

 end  

 --sp_help vhrDocDetail  

  

 insert into Hrpsm_leave  

  (  

  Doccode,hrcode ,hrname ,cccode,ccname,  

  regdate ,applydate ,leavedate,  

  remark ,EnterName ,EnterDate , Formid   

  )  

  

  

  

 select   

  Doccode ,Hrcode ,HrName ,cccode,ccname,  

  ApplyDate ,EnterDate , effbeginday,  

  remarks1,EnterName ,EnterDate , Formid   

  

  

  from hrDocHD where doccode=@doccode  

  

end  

  

  

  

  

if @fag=3  

  

begin     

  

/*       2720302微更新员工生命周期管理 已插入Hrpsm_relocation   modi by liangweijian  

 if exists(  select 1 from Hrpsm_relocation where doccode =@doccode)  

 begin  

  raiserror('岗位记录已存在!',16,1);  

  return  

 end  

   

  

 insert into Hrpsm_relocation  

  (  

    

  

  hrcode ,hrname ,rowid ,  

  RegDate ,  

  

  orgposition ,orgpositionname ,orgdepartment ,orgdepartmentname ,  

  NewPosition ,NewPositionname ,Newdepartment ,Newdepartmentname ,  

  remark ,  

  EnterDate ,EnterName , Doccode , Formid   

  

  )  

  

   

 select   

    

    

  Hrcode ,HrName ,Rowid ,  

  ApplyDate,  

  OrigPosition ,OrigPositionname ,cccode  ,ccname ,    ---OrigCccode ,OrigCcname ,  

  NewPosition ,NewPositionname ,NewCccode ,NewCcname ,  

  Memo ,  

  EnterDate ,EnterName , Doccode , Formid   

  

  from vHrDocDetail where doccode=@doccode  

*/  

 IF @@ROWCOUNT > 0  

 begin --更新当前员工资料表  

  

  

 declare @Hrcode  varchar(20),  

    

  @NewPosition varchar(20),  

  @NewPositionname varchar(200),  

  @NewCccode varchar(20),  

  @NewCcname varchar(200)  

    

    

  

  declare mycur CURSOR FOR select     

      Hrcode ,  

      NewPosition ,NewPositionname ,NewCccode ,NewCcname   

      from vHrDocDetail where doccode=@doccode  

  

  

  open mycur  

  fetch next from mycur into  

     @Hrcode,  

    @NewPosition,@NewPositionname,@NewCccode,@NewCcname  

      

  while @@FETCH_STATUS = 0  

  begin  

    

   update hrgeneral set positionid=@NewPosition,positionname=@NewPositionname,  

       cccode=@NewCccode,ccname=@NewCcname  

   where hrcode=@hrcode  

      

  

  

  fetch next from mycur into  

     @Hrcode,  

    @NewPosition,@NewPositionname,@NewCccode,@NewCcname  

  end  

    

  close mycur  

  deallocate mycur  

  

end--更新人事资料  

  

end  

  

  

if @fag=4  

begin  

  

  

  

  

 if exists(  select 1 from hrpsm_rewardspunish where doccode =@doccode)  

 begin  

  raiserror('奖惩记录已存在!',16,1);  

  return  

 end  

   

  

  

 insert into hrpsm_rewardspunish  

  (  

    

  

  hrcode ,hrname ,rowid ,  

  RegDate ,  

  

  matcode,matname,  

  remark ,  

  EnterDate ,EnterName , Doccode , Formid   

  

  )  

  

   

 select   

    

    

  Hrcode ,HrName ,Rowid ,  

  ApplyDate,  

  matcode,matname,  

  Memo ,  

  EnterDate ,EnterName , Doccode , Formid   

  

  from vHrDocDetail where doccode=@doccode  

   

end  

  

  

  

if @fag=5  

  

begin  

 if exists(  select 1 from Hrpsm_takegoods where doccode =@doccode)  

 begin  

  raiserror('领用记录已存在!',16,1);  

  return  

 end  

   

  

 insert into Hrpsm_takegoods  

  (  

    

  

  hrcode ,hrname ,rowid ,  

  RegDate ,  

  

  matcode,matname,  

  remark ,  

  EnterDate ,EnterName , Doccode , Formid   

  

  )  

  

   

 select   

    

    

  Hrcode ,HrName ,Rowid ,  

  ApplyDate,  

  matcode,matname,  

  Memo ,  

  EnterDate ,EnterName , Doccode , Formid   

  

  from vhrDocDetail where doccode=@doccode  

    

end

人事系统-人事子系统:人事档案

更多文章、技术交流、商务合作、联系博主

微信扫码或搜索：z360901061

微信扫一扫加我为好友

QQ号联系： 360901061

您的支持是博主写作最大的动力，如果您喜欢我的文章，感觉我的文章对您有帮助，请用微信扫描下面二维码支持博主2元、5元、10元、20元等您想捐的金额吧，狠狠点击下面给点支持吧，站长非常感激您！手机微信长按不能支付解决办法：请将微信支付二维码保存到相册，切换到微信，然后点击微信右上角扫一扫功能，选择支付二维码完成支付。

【本文对您有帮助就好】元

2元

5元

10元

20元

自定义