use master

go

-- =============================================

-- DatabaseName: WelfareSystem

-- remark: 福利发放系统

-- author: YuanBo

-- date: 09:51 2013-03-26

-- =============================================

IF EXISTS (SELECT *

       FROM   master..sysdatabases

       WHERE  name = N'WelfareSystem')

    DROP DATABASE WelfareSystem

GO

CREATE DATABASE WelfareSystem

GO

use WelfareSystem

go

-- =============================================

-- ylb:1,部门表

-- =============================================

create table Department

(

departmentId int primary key identity(100,1),    --编号【PK】

departmentName varchar(100)        --部门名称

)

go

-- =============================================

-- ylb:2,员工表

-- =============================================

create table Employee

(

employeeId int primary key identity(1001,1),    --编号【PK】

[id] char(18),        --身份证号

username varchar(40),    --姓名

sex char(6) check(sex='男'or sex='女'),--性别

cardNo char(22),    --银行卡号

hireDate datetime,        --受雇日期

departmentId int,        --部门编号

state char(8)        --员工性质（正式内，正式入，中心版）

)

--select employeeId,[id],username,sex,cardNo,hireDate,departmentId,state,ToRegularDate from Employee

go

-- =============================================

-- ylb:2.2,员工子女表

-- =============================================

create table Children

(

childrenId int primary key identity(1001,1),    --编号【PK】

[id] char(18),        --身份证号

username varchar(20),    --姓名

sex char(6) check(sex='男'or sex='女'),--性别

birthdate datetime,        --出生日期

multipleBirths varchar(20),    --多胞胎    单保胎，多保胎

howManyChild varchar(20),    --第一胎，第二胎，第三胎...

employeeId int        --员工编号

)

go

-- =============================================

-- ylb:3.1,项目表

-- =============================================

create table Project

(

projectId int primary key identity(100,1),    --编号【PK】

projectName varchar(100),        --项目名称

salary money,        --福利金额

type char(20)        --发放形式（一次性发放，多次性发放）

)

--go

---- =============================================

---- ylb:3.2,项目年度发放表

---- =============================================

--create table ProjectAnnualIssue

--(

--projectAnnualIssueId int primary key identity(1,1),    --编号【PK】

--pubdate datetime,                    --发放日期

--projectId int    --项目编号【FK】

--)

go

-- =============================================

-- ylb:3.2,项目年度发放表

-- =============================================

create table ProjectAnnualIssue

(

projectAnnualIssueId int primary key identity(1,1),    --编号【PK】

pubdate datetime,                    --发放日期

projectId int,    --项目编号【FK】

projectName varchar(100),    --项目名称

total money,        --发放金额

baseId int default(-1),    -- 上级编号 -1:代表无上级

remark varchar(100),    --摘要，即描述

number int    --发放人数

)

go

-- =============================================

-- ylb:4,金额发放表

-- =============================================

create table AmountIssuing

(

amountIssuingId int primary key identity(1,1),    --编号【PK】

employeeId int,        --员工编号【FK】

departmentId int,    --部门编号【FK】

departmentName varchar(20),    --部门名称

projectId int,            --项目编号【FK】

salary money,            --发放金额【单个项目|一系列项目总额】

pubdate datetime        --发放日期

)

print'福利发放系统创建成功！'

select amountIssuingId,employeeId,departmentId,departmentName,projectId,salary,pubdate from AmountIssuing

use WelfareSystem

go

--1,把项目表类型列，修饰类型换为varchar

  alter table Project

  alter column [type] varchar(20)

  alter table Employee

  alter column [id] varchar(30)

  alter table Employee

  alter column cardNo varchar(30)

  alter table Employee

alter column state varchar(8)

alter table Employee

alter column isRent varchar(20)

alter table Employee

alter column sex varchar(6)        --注意检查约束

alter table Employee

alter column toRegularDate varchar(20)    --把允许为空的日期类型换成字符串修饰符

alter table Employee

alter column hireDate varchar(20)    --把允许为空的日期类型换成字符串修饰符

alter table Children

alter column sex varchar(6) 

alter table Children

alter column isOnlyChild varchar(10) 

--begin-去除原先Char修饰符产生的多余空格

select employeeId,SUBSTRING([id],1,len([id])) from Employee where employeeId=1404

update Project set [type]= SUBSTRING([type],1,len([type]))

go

update Employee set [id]= SUBSTRING([id],1,len([id])),cardNo=SUBSTRING([cardNo],1,len([cardNo]))

,sex=SUBSTRING([sex],1,len([sex])),state=SUBSTRING([state],1,len([state]))

,isRent=SUBSTRING([isRent],1,len([isRent])),toRegularDate=SUBSTRING([toRegularDate],1,len([toRegularDate]))

go

update Children set [sex]= SUBSTRING([sex],1,len([sex])),isOnlyChild=SUBSTRING([isOnlyChild],1,len([isOnlyChild]))

--end-去除原先Char修饰符产生的多余空格

--2,

update WelfareSystem.dbo.Children set howManyChild='第一胎' where isOnlychild='' or isOnlychild is null

update WelfareSystem.dbo.Children set howManyChild='非第一胎' where isOnlychild=''

  --3,移除“身份帐号”

  alter table Children

  drop column [id]

update employee set state='正式内' where state='内'

update employee set state='正式外' where state='外'

-- =============================================

-- ylb: 5,用户表

-- =============================================

create table Users

(

username varchar(100) unique not null,            --姓名【UN】

userpass varchar(100) not null            --密码

)

insert into Users(username,userpass) values('admin','m12345')

alter table AmountIssuing

add [year] int 

alter table AmountIssuing

add [month] int 

alter table AmountIssuing

add hireDate varchar(30) 

alter table AmountIssuing

add toRegularDate varchar(30)

alter table AmountIssuing

add [state] varchar(30)

--NewAdd

alter table projectAnnualIssue

add projectAnnualIssueGuid uniqueidentifier    --项目发放编号【FK】

alter table AmountIssuing

add projectAnnualIssueGuid uniqueidentifier    --项目发放编号【FK】

alter table projectAnnualIssue

add flagNgn int default(0)    --财务工会是否生成凭证 0:未生成；1：已生成

--年度

create table Annual

(

annualId int primary key identity(1,1),

[year] int,

[month] int

)

alter table Project

add kmdm varchar(30)    --科目代码

alter table Department

add [type] varchar(30)    --部门区域京内、京外

update Department set [type]='京内'

alter table Employee

add [type] varchar(30) --职工状态 在职、离职、退休

update Employee set [type]='在职'

use WelfareSystem

go

alter table Children

add duZi varchar(20) --独子 有；无

update Children set duZi='无'

go

update Children set duZi='有' where howManyChild='第一胎'

alter table Children

add remark varchar(500) --备注

alter table Children

add flagDuZi varchar(20)    --独子补发表标识，1:年底要补发；0：已经补发过

alter table Children

add updateDate datetime  --补充独子证书时间

alter table Children

add remark varchar(500) --备注

select * from Children

/***

select duZi,flagDuZi,updateDate,remark from Children

select * from Employee where employeeId in(select employeeId from Children where flagDuZi='1')

select employeeId from Children where flagDuZi='1'

select e.hireDate,c.birthdate, c.updateDate,* from Employee e

 inner join Department d on e.departmentId=d.departmentId

 inner join Children c on e.employeeId=c.employeeId

where e.[type]='在职' and c.flagDuZi='1'

order by e.departmentId asc, e.employeeId asc

update Children set flagDuZi='0'

***/