人事系统-全局设置

      Exec sp_addtype rowid,'Varchar(6)','Not Null' --自定义数据类型rowid　　标识代码

Exec sp_addtype orgcode,'Varchar(20)','Not Null'  --自定义数据类型orgcode　　公司代码
    

      --可根据实际情况设置默认值

CREATE TABLE _sysparameters(

nodetype varchar(10) NOT NULL,	　　　　　　--标识类型

nodeid rowid NOT NULL,			　　--标识的最大值,之后新的ID在这个基础上递增

siteid varchar(10),				--默认为NULL,自定义

version char(10)				--版本,自定义

--设置rowid为6位数.创建规则

CREATE RULE rowid6

AS

STR(@rowid)=6

--绑定规则

SP_BINDRULE rowid6,'_sysparameters.nodeid'
    

      /*

	2015.4.26	HZF

*/

ALTER PROC [dbo].[p_sysxxxxrowid]

	@XXXX ROWID OUTPUT

AS

	DECLARE @nodeid rowid

	DECLARE @oldnodeid rowid

	

	BEGIN TRAN

		

		--  ASCII 40 - 96

		select @nodeid = nodeid from _sysparameters where nodetype='rowid'	--获取历史最大的id码

		set @oldnodeid = @nodeid

		

		if RIGHT(@nodeid,1) = '9'

			set @nodeid = left(@nodeid,5) + 'A'

		else if RIGHT(@nodeid,2) = '9Z'

			set @nodeid = left(@nodeid,4) + 'A0'

		else if RIGHT(@nodeid,3) = '9ZZ'

			set @nodeid = left(@nodeid,3) + 'A00'

		else if RIGHT(@nodeid,4) = '9ZZZ'

			set @nodeid = left(@nodeid,2) + 'A000'

		else if RIGHT(@nodeid,5) = '9ZZZZ'

			set @nodeid = left(@nodeid,1) + 'A0000'

		else if @nodeid = '9ZZZZZ'

			set @nodeid = 'A00000'

		else if Right(@nodeid,1) <> 'Z'

			set @nodeid = left(@nodeid,5) + CHAR(ASCII(right(@nodeid,1))+1) --尾数不为Z的情况下，递增1，使用CHAR是为了字母递增

		else if @nodeid = 'ZZZZZZ'

			raiserror('请重建编号',16,1)

		

		--尾数为Z的时候,Z前一位+1,之后的0替换

		else if RIGHT(@nodeid,5) = 'ZZZZZ'

			set @nodeid = CHAR(ASCII(@nodeid)+1) + '00000'

		else if RIGHT(@nodeid,4) = 'ZZZZ'

			set @nodeid = left(@nodeid,1) + CHAR(ASCII(right(@nodeid,5))+1) + '0000'

		else if RIGHT(@nodeid,3) = 'ZZZ'

			set @nodeid = left(@nodeid,2) + CHAR(ASCII(right(@nodeid,4))+1) + '000'

		else if RIGHT(@nodeid,2) = 'ZZ'

			set @nodeid = left(@nodeid,3) + CHAR(ASCII(right(@nodeid,3))+1) + '00'

		else if RIGHT(@nodeid,1) = 'Z'

			set @nodeid = left(@nodeid,4) + CHAR(ASCII(right(@nodeid,2))+1) + '0'

		

		--更新_sysparameters

		update _sysparameters set nodeid = @nodeid where nodetype='rowid' and nodeid = @oldnodeid

		

		if @@rowcount <> 0

			begin

			select @xxxx = @nodeid

			commit

			end

		

		else begin

			  raiserror('rowid 重复！',16,1)

			  rollback

			end



GO


    

      --uom:计量单位，uomname:计量单位名称

CREATE TABLE guom(UOM VARCHAR(50) NOT NULL PRIMARY KEY,uomname VARCHAR(40))

INSERT INTO guom values('%','百分比')

INSERT INTO guom values('UOM','通用单位,用于无法确定单位的虚拟物料')

--数据根据实际情况设置，通用单位必须设置
    

      --设置表架构

CREATE TABLE oCompany(

	companyid orgcode NOT NULL primary key,

	companyname varchar(50),

	isproduct int, --是否在生产之中，绑定规则默认1（生产），2（停产）

	coacode float, --公司标识1 ,1.1, 1.2,便于生成父级标识码

	rowid varchar(6) not null, --标识码

	treecontrol varchar(60) not null --父级标识码

)

--rowid系统中使用存储过程来执行

insert into oCompany values('JX10','江西公司',1,1.0,'000001','000001')


    

      CREATE TABLE ms_hrcodebycompany 

(

	companyid varchar(20) not null primary key,	--公司ID

	hrbeginno int not null,		--最小工号

	hrendno int not null,		--工号上限

	cuhrcode int not null,		--当前最大的工号

	memo varchar(50)	--说明

)　


    

      CREATE     FUNCTION f_hrNextHrcode ( @companyid VARCHAR(10) )  

RETURNS @Table TABLE ( newhrid VARCHAR(10) )  

AS   

    BEGIN      

         

        INSERT  INTO @Table  

                SELECT  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   

            

        RETURN   

    END