SQL server 自定义工资公式设计

目的：

      工资系统中，在计算各工资项目的时候，用户能自定义公式。这里的讲述是从数据库方设计方面考虑，做简要的数据模拟和实体、存储过程的设计。

 

收集数据：

      1.Employee员工信息数据(EmployeeID,Name,Sex,Department,Position,Joindate(到职日期),DimissionDate(离职日期)… …)

      2.Salay员工工资清单(SalaryMonth(工资月份),Employee(员工),基本工资,岗位津贴,技术津贴,住宿费,上月余额,应得工资,本月余额,实得工资 … …)

分析：

       这里只是作为Demo，就制作一个简单的数据分析。

       1.Employee员工信息就认为一个对象实体，不做详细的数据提取筛分。

       2.在Salay员工工资清单，可以把“基本工资,岗位津贴,技术津贴,住宿费 。。。”这些项提取出来，归类为SalaryItem工资项目；SalaryItem工资项目还可以再分类为“公式项”与“非公式项”。（注：“公式项”是指可以使用公式来计算非手工输入的工资项目）当然还可以根据实际的需要分类的更详细，清晰。

数据表：

    根据上面的简单分析，可以设计出作Demo使用的表，

   1.Emplyee:员工信息表。

   2.Salary:员工工资表

   3.SalaryItem:工资项目表

   4.Formulary:公式表

   5.SysSalaryItemTypeMTR:工资项目分类表

 

 

 

 

 

建表：

 

 

USE [test]

GO

/*建表脚本*/

If object_id('Salary') Is Not Null Drop Table Salary

If object_id('Formulary') Is Not Null Drop Table Formulary

If object_id('Employee') Is Not Null Drop Table Employee

If object_id('SalaryItem') Is Not Null Drop Table SalaryItem

If object_id('SysSalaryItemTypeMTR') Is Not Null Drop Table SysSalaryItemTypeMTR

Go

CREATE TABLE [SysSalaryItemTypeMTR](

    [ID] [smallint] IDENTITY(1,1) NOT NULL,

    [Name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,

CONSTRAINT [PK_SysSalaryItemTypeMTR] PRIMARY KEY CLUSTERED

(

    [ID] ASC

)

)

CREATE TABLE [Employee](

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [EmployeeNo] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,

    [Name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,

    [Sex] [nchar](1) COLLATE Chinese_PRC_CI_AS NOT NULL,

    [Department] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,

    [Position] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,

    [JoinDate] [datetime] NULL,

    [DimissionDate] [datetime] NULL,

CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED

(

    [ID] ASC

)

)

CREATE TABLE [SalaryItem](

    [ID] [smallint] IDENTITY(1,1) NOT NULL,

    [ItemType] [smallint] NOT NULL,

    [Name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,

CONSTRAINT [PK_SalaryItem] PRIMARY KEY CLUSTERED

(

    [ID] ASC

)

)

ALTER TABLE [SalaryItem]  WITH CHECK ADD  CONSTRAINT [FK_SalaryItem_SysSalaryItemTypeMTR] FOREIGN KEY([ItemType])

REFERENCES [SysSalaryItemTypeMTR] ([ID])

CREATE TABLE [Salary](

    [ID] [int] Identity(1,1)NOT NULL,

    [EmployeeID] [int] NOT NULL,

    [SalaryMonth] [datetime] NULL,

    [SalaryItemID] [smallint] NOT NULL,

    [Amount] [money] NULL,

CONSTRAINT [PK_Salary] PRIMARY KEY CLUSTERED

(

    [ID] ASC

)

)

ALTER TABLE [Salary]  WITH CHECK ADD  CONSTRAINT [FK_Salary_Employee] FOREIGN KEY([EmployeeID])

REFERENCES [Employee] ([ID])

ALTER TABLE [Salary]  WITH CHECK ADD  CONSTRAINT [FK_Salary_SalaryItem] FOREIGN KEY([SalaryItemID])

REFERENCES [SalaryItem] ([ID])

Create Table Formulary

(

    ID int Identity(1,1) Not Null,

    SalaryItemID Smallint Not Null,

    Definition nvarchar(4000) Null,

    Sequence smallint Null,

    EffectiveDate datetime Null,

    ExpiryDate datetime Null,

    Constraint PK_Formulary Primary Key(ID Asc),

    Constraint FK_Formulary_SalaryItem Foreign Key (SalaryItemID) References SalaryItem(ID)

)

CREATE NONCLUSTERED INDEX [IX_Employee_EmployeeNo] ON [Employee]

(

    [EmployeeNo] ASC

)

CREATE NONCLUSTERED INDEX [IX_Formulary_SalaryItemID] ON [Formulary]

(

    [SalaryItemID] ASC

)

CREATE NONCLUSTERED INDEX [IX_Salary_EmployeeID] ON [Salary]

(

    [EmployeeID] ASC

)

CREATE NONCLUSTERED INDEX [IX_Salary_SalaryMonth_EmployeeID] ON [Salary]

(

    [SalaryMonth] ASC,

    [EmployeeID] ASC

)

插入测试数据：

Use test
Go
If Not Exists(Select 1 From SysSalaryItemTypeMTR)
    Insert Into SysSalaryItemTypeMTR(Name)
        Select N'上月项' Union All
        Select N'固定项' Union All
        Select N'输入项' Union All
        Select N'公式项' Union All
        Select N'文本项'

If Not Exists(Select 1 From SalaryItem)
    Insert Into SalaryItem(ItemType,Name)
        Select 2,N'基本工资' Union All
        Select 3,N'其他扣款' Union All
        Select 3,N'岗位津贴' Union All
        Select 3,N'技术津贴' Union All
        Select 3,N'住宿费'     Union All
        Select 1,N'上月余额' Union All
        Select 4,N'应得工资' Union All
        Select 4,N'本月余额' Union All
        Select 4,N'实得工资'

If Not Exists(Select 1 From Formulary)
    Insert Into Formulary(SalaryItemID,Definition,Sequence,EffectiveDate,ExpiryDate)
        Select 1,N'Isnull([1],0)',1,'20090101','21001231' Union ALl
        Select 2,N'Isnull([2],0)',2,'20090101','21001231' Union ALl
        Select 3,N'Isnull([3],0)',3,'20090101','21001231' Union ALl
        Select 4,N'Isnull([4],0)',4,'20090101','21001231' Union ALl
        Select 5,N'Isnull([5],0)',5,'20090101','21001231' Union ALl
        Select 6,N'Isnull([8],0)',6,'20090101','21001231' Union ALl
        Select 7,N'Isnull([1],0)+Isnull([2],0)+Isnull([3],0)+Isnull([4],0)+Isnull([5],0)+Isnull([6],0)',7,'20090101','21001231' Union ALl
        Select 8,N'Case Convert(char(6),DimissionDate,112)+''01'' When SalaryMonth Then 0 Else Isnull([7],0)%10 End ',8,'20090101','21001231' Union ALl
        Select 9,N'Case Convert(char(6),DimissionDate,112)+''01'' When SalaryMonth Then Isnull([7],0) Else Isnull([7],0)-Isnull([8],0) End',9,'20090101','21001231'

If Not Exists(Select 1 From Employee)
    Insert Into Employee(EmployeeNo,Name,Sex,Department,Position,JoinDate,DimissionDate)
        Select N'N0001',N'A1',N'男',N'Dep1',N'Pos1','20080101',Null Union All
        Select N'N0002',N'A2',N'女',N'Dep2',N'Pos2','20080101','20090514' Union All
        Select N'N0003',N'A3',N'男',N'Dep3',N'Pos3','20080101',Null

If Not Exists(Select 1 From Salary)
    Insert Into Salary(EmployeeID,SalaryMonth,SalaryItemID,Amount)
        Select 1,'20090401',1,1000 Union All
        Select 2,'20090401',1,2000 Union All
        Select 3,'20090401',1,3000 Union All
        Select 1,'20090401',2,-10 Union All
        Select 2,'20090401',2,-20 Union All
        Select 3,'20090401',2,-30 Union All
        Select 1,'20090401',3,0 Union All
        Select 2,'20090401',3,0 Union All
        Select 3,'20090401',3,0 Union All
        Select 1,'20090401',4,105 Union All
        Select 2,'20090401',4,0 Union All
        Select 3,'20090401',4,107 Union All
        Select 1,'20090401',5,-60 Union All
        Select 2,'20090401',5,-60 Union All
        Select 3,'20090401',5,-60 Union All
        Select 1,'20090401',6,0 Union All
        Select 2,'20090401',6,0 Union All
        Select 3,'20090401',6,0 Union All
        Select 1,'20090401',7,0 Union All
        Select 2,'20090401',7,0 Union All
        Select 3,'20090401',7,0 Union All
        Select 1,'20090401',8,0 Union All
        Select 2,'20090401',8,0 Union All
        Select 3,'20090401',8,0 Union All
        Select 1,'20090401',9,0 Union All
        Select 2,'20090401',9,0 Union All
        Select 3,'20090401',9,0 Union All

        Select 1,'20090501',1,1000 Union All
        Select 2,'20090501',1,2000 Union All
        Select 3,'20090501',1,3000 Union All
        Select 1,'20090501',2,-25 Union All
        Select 2,'20090501',2,-25 Union All
        Select 3,'20090501',2,-25 Union All
        Select 1,'20090501',3,20 Union All
        Select 2,'20090501',3,10 Union All
        Select 3,'20090501',3,10 Union All
        Select 1,'20090501',4,150 Union All
        Select 2,'20090501',4,20 Union All
        Select 3,'20090501',4,150 Union All
        Select 1,'20090501',5,-62 Union All
        Select 2,'20090501',5,-62 Union All
        Select 3,'20090501',5,-62 Union All
        Select 1,'20090501',6,0 Union All
        Select 2,'20090501',6,0 Union All
        Select 3,'20090501',6,0 Union All
        Select 1,'20090501',7,0 Union All
        Select 2,'20090501',7,0 Union All
        Select 3,'20090501',7,0 Union All
        Select 1,'20090501',8,0 Union All
        Select 2,'20090501',8,0 Union All
        Select 3,'20090501',8,0 Union All
        Select 1,'20090501',9,0 Union All
        Select 2,'20090501',9,0 Union All
        Select 3,'20090501',9,0
Go

建测试存储过程：

 

Use test
GO
If object_id('up_SalarySUM') Is Not Null
    Drop Proc up_SalarySUM
Go
Create Proc up_SalarySUM
(
    @SalaryMonth datetime
)
As
Set Nocount On

Declare @SalaryItemID nvarchar(50),
        @ItemType smallint,
        @Sql nvarchar(4000),
        @Definition nvarchar(4000),
        @Cols nvarchar(4000)

If object_id('tempdb..#Salary') Is Not Null
    Drop Table #Salary

Create Table #Salary(EmployeeID int,DimissionDate datetime,SalaryMonth datetime)

Select @Cols=Isnull(@Cols+',','')+Rtrim(Quotename(a.ID))
        From SalaryItem As a
            Inner Join Formulary As b On b.SalaryItemID=a.ID
        Where b.EffectiveDate<=@SalaryMonth
                And b.ExpiryDate>@SalaryMonth

Set @Sql=N'Alter Table #Salary Add '+Replace(@Cols,',',' money,')+' money'
Exec sp_executesql @Sql

Set @Sql=N'
;With CTE_Salary As
(
    Select a.EmployeeID,b.DimissionDate,SalaryMonth,a.SalaryItemID,a.Amount
        From Salary As a
            Inner Join Employee As b On b.ID=a.EmployeeID
        Where SalaryMonth In(@SalaryMonth,Dateadd(month,-1,@SalaryMonth))
)
Insert Into #Salary
    Select *
        From  CTE_Salary
        Pivot(Max(Amount) For SalaryItemID In('+@Cols+')) As b
'
Exec sp_executesql @Sql,N'@SalaryMonth datetime',@SalaryMonth

Declare Cur_SalaryItem Cursor For
    Select Quotename(a.ID) As ID,a.ItemType,b.Definition 
        From SalaryItem As a
            Inner Join Formulary As b On b.SalaryItemID=a.ID
        Where b.EffectiveDate<=@SalaryMonth
                And b.ExpiryDate>@SalaryMonth
                And a.ItemType In(1,4)
        Order By b.Sequence
    For Read Only

Open Cur_SalaryItem
Fetch Next From Cur_SalaryItem Into @SalaryItemID,@ItemType,@Definition
While @@Fetch_status=0
Begin
    If @ItemType=4 --本月可计算的工资项
        Set @sql='Update #Salary Set '+@SalaryItemID+'='+@Definition +' Where SalaryMonth=@SalaryMonth'
    Else            --计算上月工资项
        Set @sql='Update a  Set a.'+@SalaryItemID+'=(Select '+@Definition +' From #Salary Where EmployeeID=a.EmployeeID And SalaryMonth=Dateadd(Month,-1,a.SalaryMonth)) From #Salary As a Where SalaryMonth=@SalaryMonth'

    Exec sp_executesql @sql,N'@SalaryMonth datetime',@SalaryMonth

    Fetch Next From Cur_SalaryItem Into @SalaryItemID,@ItemType,@Definition
End
Close Cur_SalaryItem
Deallocate Cur_SalaryItem

Set @sql=(N'
;With CTE_Salary As
(
    Select * From #Salary Unpivot(Amount For SalaryItemID In('+@Cols+')) As up Where SalaryMonth=@SalaryMonth
)
Update a
    Set a.Amount=b.Amount
    From Salary As a
        Inner Join CTE_Salary As b On b.SalaryMonth=a.SalaryMonth
                And b.EmployeeID=a.EmployeeID
                And b.SalaryItemID=a.SalaryItemID
')
Exec sp_executesql @sql,N'@SalaryMonth datetime',@SalaryMonth

Drop Table #Salary
Go

--执行工资计算

 

Exec up_SalarySUM '20090401'
Exec up_SalarySUM '20090501'
Go

 

---查看计算结果

 

Declare @Cols nvarchar(4000),
        @SalaryMonth datetime,
        @Sql nvarchar(4000)

Set @SalaryMonth='20090501'

Select @Cols=Isnull(@Cols+',','')+Rtrim(Quotename(a.Name))
        From SalaryItem As a
            Inner Join Formulary As b On b.SalaryItemID=a.ID
        Where b.EffectiveDate<=@SalaryMonth
                And b.ExpiryDate>@SalaryMonth

Set @Sql=N'
;With CTE_Salary As
(
    Select a.EmployeeID,b.DimissionDate,SalaryMonth,c.Name As SalaryItem,a.Amount
        From Salary As a
            Inner Join Employee As b On b.ID=a.EmployeeID
            Inner Join SalaryItem As c On c.ID=a.SalaryItemID
        Where SalaryMonth=@SalaryMonth
)
    Select *
        From  CTE_Salary
        Pivot(Max(Amount) For SalaryItem In('+@Cols+')) As b
'
Exec sp_executesql @Sql,N'@SalaryMonth datetime',@SalaryMonth

Go

 

Demo效果如图：