久草资源站,黄在线免费看,无码人妻丰满熟妇A片护士电影

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元

自定義