数据库使用的是PostgreSQL,所以下面的SQL语句均以PostgreSQL形式出现
表及其结构
人员表
用户人员的基本信息
CREATE TABLE innovation_management_user
(
pk_user_id serial NOT NULL,
user_firstname character varying(255),
user_lastname character varying(255),
user_name character varying(50),
user_email character varying(50),
user_password text,
user_area character varying(255),
fk_assigned_country integer DEFAULT 154,
user_picture text,
user_isadmin boolean DEFAULT false,
user_isdeleted boolean DEFAULT false,
special_permission_1 boolean NOT NULL DEFAULT false,
CONSTRAINT pk_innovation_management_user PRIMARY KEY (pk_user_id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE innovation_management_user OWNER TO postgres;
审批关系表
审批人员与请假人员的对应关系
CREATE TABLE leave_management_approval_matrix
(
pk_approval_id serial NOT NULL,
fk_requestor_id integer,
fk_approver_id integer,
CONSTRAINT pk_leave_management_approval_matrix PRIMARY KEY (pk_approval_id),
CONSTRAINT fk_leave_management_approval_matrix_innovation_management_user2 FOREIGN KEY (fk_requestor_id)
REFERENCES innovation_management_user (pk_user_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_leave_management_approval_matrix_innovation_management_user3 FOREIGN KEY (fk_approver_id)
REFERENCES innovation_management_user (pk_user_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
ALTER TABLE leave_management_approval_matrix OWNER TO postgres;
假期分配表
每个用户假期的配额
CREATE TABLE leave_management_leave_allowance
(
pk_leave_allowance_id serial NOT NULL,
fk_user_id integer,
fk_leave_type_id integer,
allowance_year character varying(50),
allowance_entitlement integer DEFAULT 0,
allowance_taken double precision DEFAULT 0,
CONSTRAINT pk_leave_management_leave_allowance PRIMARY KEY (pk_leave_allowance_id),
CONSTRAINT fk_leave_management_leave_allowance_innovation_management_user FOREIGN KEY (fk_user_id)
REFERENCES innovation_management_user (pk_user_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_leave_management_leave_allowance_leave_management_leave_type FOREIGN KEY (fk_leave_type_id)
REFERENCES leave_management_leave_type (pk_leave_type_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
ALTER TABLE leave_management_leave_allowance OWNER TO postgres;
假期申请记录表
假期申请的记录
CREATE TABLE leave_management_leave_taken
(
pk_leave_taken_id serial NOT NULL,
fk_user_id integer,
fk_leave_type integer,
leave_days date,
is_halfday boolean,
is_afternoon boolean,
leave_status integer DEFAULT 0,
fk_approved_user_id integer,
CONSTRAINT pk_leave_management_leave_taken PRIMARY KEY (pk_leave_taken_id),
CONSTRAINT fk_leave_management_leave_taken_innovation_management_user FOREIGN KEY (fk_user_id)
REFERENCES innovation_management_user (pk_user_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_leave_management_leave_taken_innovation_management_user1 FOREIGN KEY (fk_approved_user_id)
REFERENCES innovation_management_user (pk_user_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_leave_management_leave_taken_leave_management_leave_type FOREIGN KEY (fk_leave_type)
REFERENCES leave_management_leave_type (pk_leave_type_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
ALTER TABLE leave_management_leave_taken OWNER TO postgres;
假期类型表
有哪些类型的假期,比如年假、婚假、产假、病假、丧假、陪护假
CREATE TABLE leave_management_leave_type
(
pk_leave_type_id serial NOT NULL,
leave_type character varying,
CONSTRAINT pk_leave_management_leave_type PRIMARY KEY (pk_leave_type_id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE leave_management_leave_type OWNER TO postgres;
请假数据视图
当前年份下,已批准的请假
CREATE OR REPLACE VIEW leave_data AS
select to_char(leave_days, 'dd/mm/yyyy') as leave_days,user_firstname,fk_user_id from leave_management_leave_taken,innovation_management_user where leave_status=1
and date_part('year', leave_days)=date_part('year',CURRENT_DATE) and fk_user_id=pk_user_id order by leave_days asc
应用场景及其SQL构造
使用到数据库的地方无非插入、更新、查询、更新这四种操作,复杂一点的应用不过是多表联合操作,或条件语句多
按日期获取请假数据
需要操作的数据库表:Leave_Management_Approval_Matrix,leave_management_leave_taken
要求:不同的用户读取的数据应该有所区别,比如admin可以获取所有数据;审批人员只需要获取自己审批范围内的请假数据
数据:所有已审批过的请假记录,包括批准、否决
步骤:
- 根据当前用户获取管辖范围内的假期申请用户,比如当前用户为张三,那么需要张三审批的用户ID都将被列举出来
- 获取有请假的日期,返回的结果是有请假的日期,比如28/11/2014至少有一个人请假,那么28/11/2014就会被列举出来并且只列举一次
- 按日期列举当日的请假申请人
请假类型的新建与修改
这里用到的是基本的数据库表操作,比如insert,update,不详述
需要注意的是,避免假期类型重复
按用户获取请假数据
这个功能主要用来给使用者提供列表信息,比如张三登录,那么读取张三过往所有提交过的申请
唯一的条件,是使用用户id进行过滤
获取待批准假期数据
审批人员需要,所有属于当前审批人员管辖范围内的假期申请数据都将被列举出来,当然需要满足如下过滤条件
过滤条件:
- 当前年份,近3个月内的申请
- 当前审批者审批范围内的申请
获取假期份额
有几个不同获取场景
按userID列举
按userID获取所有假期,已请假期,某一类型假期,某一类型已请假期
获取审批关系
简单的查询