数据库使用的是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可以获取所有数据；审批人员只需要获取自己审批范围内的请假数据

数据：所有已审批过的请假记录，包括批准、否决

步骤：

1. 根据当前用户获取管辖范围内的假期申请用户，比如当前用户为张三，那么需要张三审批的用户ID都将被列举出来
2. 获取有请假的日期，返回的结果是有请假的日期，比如28/11/2014至少有一个人请假，那么28/11/2014就会被列举出来并且只列举一次
3. 按日期列举当日的请假申请人

请假类型的新建与修改

这里用到的是基本的数据库表操作，比如insert，update，不详述

需要注意的是，避免假期类型重复

按用户获取请假数据

这个功能主要用来给使用者提供列表信息，比如张三登录，那么读取张三过往所有提交过的申请

唯一的条件，是使用用户id进行过滤

获取待批准假期数据

审批人员需要，所有属于当前审批人员管辖范围内的假期申请数据都将被列举出来，当然需要满足如下过滤条件

过滤条件：

1. 当前年份，近3个月内的申请
2. 当前审批者审批范围内的申请

获取假期份额

有几个不同获取场景

按userID列举

按userID获取所有假期，已请假期，某一类型假期，某一类型已请假期

获取审批关系

简单的查询