ATS考勤2.9考勤计算（请假单）

--建表脚本--

create table T_ATS_HOLY(

FID int<br> not null  comment '主键'

,FBILLNO nvarchar(30) default null  comment '请假单号'

,FBILLTYPEID nvarchar(36) default null  comment '单据类型'

,FMGRCENTERID int<br> default null  comment '人事管理中心'

,FFORBIDSTATUS char(1) default null  comment '作废状态'

,FAUDITORID int<br> default null  comment '审核人'

,FCREATORID int<br> default null  comment '创建人'

,FDOCUMENTSTATUS char(1) default null  comment '单据状态'

,FMODIFYDATE datetime<br> default null  comment '修改日期'

,FFORBIDDATE datetime<br> default null  comment '作废日期'

,FAUDITDATE datetime<br> default null  comment '审核日期'

,FCREATEDATE datetime<br> default null  comment '创建日期'

,FMODIFIERID int<br> default null  comment '修改人'

,FFORBIDDERID int<br> default null  comment '作废人'

,FPRIMARYORGID int<br> default null  comment '主业务组织'

,FSTAFFID int<br> default null  comment '员工信息'

,FPERSONID int<br> default null  comment '人员信息'

,FDOCID int<br> default null  comment '职员姓名'

,FHOLYTYPE int<br> default null  comment '假期类型'

,FPLANTIMESPAN decimal(19,6) default null  comment '计划时间长度'

,FHOLYTYPEUNIT nvarchar(30) default null  comment '假期单位'

,FSTARTDATE datetime<br> default null  comment '开始日期'

,FREALTIMESPAN decimal(19,6) default null  comment '实际时间长度'

,FENDDATE datetime<br> default null  comment '结束日期'

,FAPPLYDATE datetime<br> default null  comment '申请日期'

,FSTARTTIME datetime<br> default null  comment '开始时间'

,FENDTIME datetime<br> default null  comment '结束时间'

,FREALSTARTTIME datetime<br> default null  comment '请假开始时间'

,FREALENDTIME datetime<br> default null  comment '请假结束时间'

,FISWHOLEDAY nvarchar(30) default null  comment '是否整天'

,primary key (FID)

) comment = '请假单'

--查询--

select FID as "fid",FBILLNO as "fbillno",FBILLTYPEID as "fbilltypeid",FMGRCENTERID as "fmgrcenterid",FFORBIDSTATUS as "fforbidstatus",FAUDITORID as "fauditorid",FCREATORID as "fcreatorid",FDOCUMENTSTATUS as "fdocumentstatus",FMODIFYDATE as "fmodifydate",FFORBIDDATE as "fforbiddate",FAUDITDATE as "fauditdate",FCREATEDATE as "fcreatedate",FMODIFIERID as "fmodifierid",FFORBIDDERID as "fforbidderid",FPRIMARYORGID as "fprimaryorgid",FSTAFFID as "fstaffid",FPERSONID as "fpersonid",FDOCID as "fdocid",FHOLYTYPE as "fholytype",FPLANTIMESPAN as "fplantimespan",FHOLYTYPEUNIT as "fholytypeunit",FSTARTDATE as "fstartdate",FREALTIMESPAN as "frealtimespan",FENDDATE as "fenddate",FAPPLYDATE as "fapplydate",FSTARTTIME as "fstarttime",FENDTIME as "fendtime",FREALSTARTTIME as "frealstarttime",FREALENDTIME as "frealendtime",FISWHOLEDAY as "fiswholeday" from T_ATS_HOLY

--查询(中文字段)--

select FID as "主键",FBILLNO as "请假单号",FBILLTYPEID as "单据类型",FMGRCENTERID as "人事管理中心",FFORBIDSTATUS as "作废状态",FAUDITORID as "审核人",FCREATORID as "创建人",FDOCUMENTSTATUS as "单据状态",FMODIFYDATE as "修改日期",FFORBIDDATE as "作废日期",FAUDITDATE as "审核日期",FCREATEDATE as "创建日期",FMODIFIERID as "修改人",FFORBIDDERID as "作废人",FPRIMARYORGID as "主业务组织",FSTAFFID as "员工信息",FPERSONID as "人员信息",FDOCID as "职员姓名",FHOLYTYPE as "假期类型",FPLANTIMESPAN as "计划时间长度",FHOLYTYPEUNIT as "假期单位",FSTARTDATE as "开始日期",FREALTIMESPAN as "实际时间长度",FENDDATE as "结束日期",FAPPLYDATE as "申请日期",FSTARTTIME as "开始时间",FENDTIME as "结束时间",FREALSTARTTIME as "请假开始时间",FREALENDTIME as "请假结束时间",FISWHOLEDAY as "是否整天" from T_ATS_HOLY

--INSERT脚本--

insert into T_ATS_HOLY(FID,FBILLNO,FBILLTYPEID,FMGRCENTERID,FFORBIDSTATUS,FAUDITORID,FCREATORID,FDOCUMENTSTATUS,FMODIFYDATE,FFORBIDDATE,FAUDITDATE,FCREATEDATE,FMODIFIERID,FFORBIDDERID,FPRIMARYORGID,FSTAFFID,FPERSONID,FDOCID,FHOLYTYPE,FPLANTIMESPAN,FHOLYTYPEUNIT,FSTARTDATE,FREALTIMESPAN,FENDDATE,FAPPLYDATE,FSTARTTIME,FENDTIME,FREALSTARTTIME,FREALENDTIME,FISWHOLEDAY) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

--UPDATE脚本--

update T_ATS_HOLY set FID=?,FBILLNO=?,FBILLTYPEID=?,FMGRCENTERID=?,FFORBIDSTATUS=?,FAUDITORID=?,FCREATORID=?,FDOCUMENTSTATUS=?,FMODIFYDATE=?,FFORBIDDATE=?,FAUDITDATE=?,FCREATEDATE=?,FMODIFIERID=?,FFORBIDDERID=?,FPRIMARYORGID=?,FSTAFFID=?,FPERSONID=?,FDOCID=?,FHOLYTYPE=?,FPLANTIMESPAN=?,FHOLYTYPEUNIT=?,FSTARTDATE=?,FREALTIMESPAN=?,FENDDATE=?,FAPPLYDATE=?,FSTARTTIME=?,FENDTIME=?,FREALSTARTTIME=?,FREALENDTIME=?,FISWHOLEDAY=? where FID=?

--delete脚本--

delete from T_ATS_HOLY where FID=?

--给字段加备注--

alter table T_ATS_HOLY comment '请假单';

alter table T_ATS_HOLY modify column FID int<br> not null  comment '主键';

alter table T_ATS_HOLY modify column FBILLNO nvarchar(30) default null  comment '请假单号';

alter table T_ATS_HOLY modify column FBILLTYPEID nvarchar(36) default null  comment '单据类型';

alter table T_ATS_HOLY modify column FMGRCENTERID int<br> default null  comment '人事管理中心';

alter table T_ATS_HOLY modify column FFORBIDSTATUS char(1) default null  comment '作废状态';

alter table T_ATS_HOLY modify column FAUDITORID int<br> default null  comment '审核人';

alter table T_ATS_HOLY modify column FCREATORID int<br> default null  comment '创建人';

alter table T_ATS_HOLY modify column FDOCUMENTSTATUS char(1) default null  comment '单据状态';

alter table T_ATS_HOLY modify column FMODIFYDATE datetime<br> default null  comment '修改日期';

alter table T_ATS_HOLY modify column FFORBIDDATE datetime<br> default null  comment '作废日期';

alter table T_ATS_HOLY modify column FAUDITDATE datetime<br> default null  comment '审核日期';

alter table T_ATS_HOLY modify column FCREATEDATE datetime<br> default null  comment '创建日期';

alter table T_ATS_HOLY modify column FMODIFIERID int<br> default null  comment '修改人';

alter table T_ATS_HOLY modify column FFORBIDDERID int<br> default null  comment '作废人';

alter table T_ATS_HOLY modify column FPRIMARYORGID int<br> default null  comment '主业务组织';

alter table T_ATS_HOLY modify column FSTAFFID int<br> default null  comment '员工信息';

alter table T_ATS_HOLY modify column FPERSONID int<br> default null  comment '人员信息';

alter table T_ATS_HOLY modify column FDOCID int<br> default null  comment '职员姓名';

alter table T_ATS_HOLY modify column FHOLYTYPE int<br> default null  comment '假期类型';

alter table T_ATS_HOLY modify column FPLANTIMESPAN decimal(19,6) default null  comment '计划时间长度';

alter table T_ATS_HOLY modify column FHOLYTYPEUNIT nvarchar(30) default null  comment '假期单位';

alter table T_ATS_HOLY modify column FSTARTDATE datetime<br> default null  comment '开始日期';

alter table T_ATS_HOLY modify column FREALTIMESPAN decimal(19,6) default null  comment '实际时间长度';

alter table T_ATS_HOLY modify column FENDDATE datetime<br> default null  comment '结束日期';

alter table T_ATS_HOLY modify column FAPPLYDATE datetime<br> default null  comment '申请日期';

alter table T_ATS_HOLY modify column FSTARTTIME datetime<br> default null  comment '开始时间';

alter table T_ATS_HOLY modify column FENDTIME datetime<br> default null  comment '结束时间';

alter table T_ATS_HOLY modify column FREALSTARTTIME datetime<br> default null  comment '请假开始时间';

alter table T_ATS_HOLY modify column FREALENDTIME datetime<br> default null  comment '请假结束时间';

alter table T_ATS_HOLY modify column FISWHOLEDAY nvarchar(30) default null  comment '是否整天';