目的:
工资系统中,在计算各工资项目的时候,用户能自定义公式。这里的讲述是从数据库方设计方面考虑,做简要的数据模拟和实体、存储过程的设计。
收集数据:
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:工资项目分类表
建表:
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
建测试存储过程:
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 ' 20090501 '
Go
---查看计算结果
@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效果如图: