自定义工资公式设计

目的：

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

 

收集数据：

      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效果如图：