ATS考勤2.7考勤计算（明细计算归档表）

--建表脚本--

create table T_ATS_DETAILCALC_BAK(

FID int not null  comment '内码'

,FSTAFFID int not null  comment '员工id'

,FPERSONID int not null  comment '人员id'

,FMGRCENTERID int not null  comment '人事管理中心'

,FATTENDDATE datetime default null  comment '考勤日期'

,FSHIFTID int not null  comment '班次'

,FSTARTTIME1 datetime default null  comment '第一段上班时间'

,FENDTIME1 datetime default null  comment '第一段下班时间'

,FSTARTTIME2 datetime default null  comment '第二段上班时间'

,FENDTIME2 datetime default null  comment '第二段下班时间'

,FSTARTTIME3 datetime default null  comment '第三段上班时间'

,FENDTIME3 datetime default null  comment '第三段下班时间'

,FSTARTTIME4 datetime default null  comment '第四段上班时间'

,FENDTIME4 datetime default null  comment '第四段下班时间'

,FSTARTTIME5 datetime default null  comment '第五段上班时间'

,FENDTIME5 datetime default null  comment '第五段下班时间'

,FLATEMINUTE1 decimal(19,6) default 0  comment '第一段迟到分钟数'

,FLATEMINUTE2 decimal(19,6) default 0  comment '第二段迟到分钟数'

,FLATEMINUTE3 decimal(19,6) default 0  comment '第三段迟到分钟数'

,FLATEMINUTE4 decimal(19,6) default 0  comment '第四段迟到分钟数'

,FLATEMINUTE5 decimal(19,6) default 0  comment '第五段迟到分钟数'

,FEARLYMINUTE1 decimal(19,6) default 0  comment '第一段早退分钟数'

,FEARLYMINUTE3 decimal(19,6) default 0  comment '第二段早退分钟数'

,FEARLYMINUTE2 decimal(19,6) default 0  comment '第三段早退分钟数'

,FEARLYMINUTE4 decimal(19,6) default 0  comment '第四段早退分钟数'

,FEARLYMINUTE5 decimal(19,6) default 0  comment '第五段早退分钟数'

,FABSENTMINUTE1 decimal(19,6) default 0  comment '第一段旷工分钟数'

,FABSENTMINUTE2 decimal(19,6) default 0  comment '第二段旷工分钟数'

,FABSENTMINUTE3 decimal(19,6) default 0  comment '第三段旷工分钟数'

,FABSENTMINUTE4 decimal(19,6) default 0  comment '第四段旷工分钟数'

,FABSENTMINUTE5 decimal(19,6) default 0  comment '第五段旷工分钟数'

,FLATETIMES int default 0  comment '当天迟到次数'

,FLATESUM decimal(19,6) default 0  comment '当天迟到总分钟数'

,FEARLYTIMES int default 0  comment '当天早退次数'

,FEARLYSUM decimal(19,6) default 0  comment '当天早退总分钟数'

,FABSENTTIMES int default 0  comment '当天旷工次数'

,FABSENTSUM decimal(19,6) default 0  comment '当天旷工总分钟数'

,FSUPPLEMENTTIMES int default 0  comment '当天补卡次数'

,FCHECKINTIMES int default 0  comment '当天签卡次数'

,FLEAVETIMES int default 0  comment '当天请假次数'

,FLEAVESUM decimal(19,6) default 0  comment '当天请假总分钟数'

,FTRIPTIMES int default 0  comment '当天出差次数'

,FTRIPSUM decimal(19,6) default 0  comment '当天出差总分钟数'

,FUNCHECKINTIMES int default 0  comment '缺卡次数'

,FOT1 decimal(19,6) default 0  comment 'ot1'

,FOT2 decimal(19,6) default 0  comment 'ot2'

,FOT3 decimal(19,6) default 0  comment 'ot3'

,FOT4 decimal(19,6) default 0  comment 'ot4'

,FOT5 decimal(19,6) default 0  comment 'ot5'

,FOT6 decimal(19,6) default 0  comment 'ot6'

,primary key (FID)

) comment = '明细计算归档表'

--查询--

select FID as "fid",FSTAFFID as "fstaffid",FPERSONID as "fpersonid",FMGRCENTERID as "fmgrcenterid",FATTENDDATE as "fattenddate",FSHIFTID as "fshiftid",FSTARTTIME1 as "fstarttime1",FENDTIME1 as "fendtime1",FSTARTTIME2 as "fstarttime2",FENDTIME2 as "fendtime2",FSTARTTIME3 as "fstarttime3",FENDTIME3 as "fendtime3",FSTARTTIME4 as "fstarttime4",FENDTIME4 as "fendtime4",FSTARTTIME5 as "fstarttime5",FENDTIME5 as "fendtime5",FLATEMINUTE1 as "flateminute1",FLATEMINUTE2 as "flateminute2",FLATEMINUTE3 as "flateminute3",FLATEMINUTE4 as "flateminute4",FLATEMINUTE5 as "flateminute5",FEARLYMINUTE1 as "fearlyminute1",FEARLYMINUTE3 as "fearlyminute3",FEARLYMINUTE2 as "fearlyminute2",FEARLYMINUTE4 as "fearlyminute4",FEARLYMINUTE5 as "fearlyminute5",FABSENTMINUTE1 as "fabsentminute1",FABSENTMINUTE2 as "fabsentminute2",FABSENTMINUTE3 as "fabsentminute3",FABSENTMINUTE4 as "fabsentminute4",FABSENTMINUTE5 as "fabsentminute5",FLATETIMES as "flatetimes",FLATESUM as "flatesum",FEARLYTIMES as "fearlytimes",FEARLYSUM as "fearlysum",FABSENTTIMES as "fabsenttimes",FABSENTSUM as "fabsentsum",FSUPPLEMENTTIMES as "fsupplementtimes",FCHECKINTIMES as "fcheckintimes",FLEAVETIMES as "fleavetimes",FLEAVESUM as "fleavesum",FTRIPTIMES as "ftriptimes",FTRIPSUM as "ftripsum",FUNCHECKINTIMES as "funcheckintimes",FOT1 as "fot1",FOT2 as "fot2",FOT3 as "fot3",FOT4 as "fot4",FOT5 as "fot5",FOT6 as "fot6" from T_ATS_DETAILCALC_BAK

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

select FID as "内码",FSTAFFID as "员工id",FPERSONID as "人员id",FMGRCENTERID as "人事管理中心",FATTENDDATE as "考勤日期",FSHIFTID as "班次",FSTARTTIME1 as "第一段上班时间",FENDTIME1 as "第一段下班时间",FSTARTTIME2 as "第二段上班时间",FENDTIME2 as "第二段下班时间",FSTARTTIME3 as "第三段上班时间",FENDTIME3 as "第三段下班时间",FSTARTTIME4 as "第四段上班时间",FENDTIME4 as "第四段下班时间",FSTARTTIME5 as "第五段上班时间",FENDTIME5 as "第五段下班时间",FLATEMINUTE1 as "第一段迟到分钟数",FLATEMINUTE2 as "第二段迟到分钟数",FLATEMINUTE3 as "第三段迟到分钟数",FLATEMINUTE4 as "第四段迟到分钟数",FLATEMINUTE5 as "第五段迟到分钟数",FEARLYMINUTE1 as "第一段早退分钟数",FEARLYMINUTE3 as "第二段早退分钟数",FEARLYMINUTE2 as "第三段早退分钟数",FEARLYMINUTE4 as "第四段早退分钟数",FEARLYMINUTE5 as "第五段早退分钟数",FABSENTMINUTE1 as "第一段旷工分钟数",FABSENTMINUTE2 as "第二段旷工分钟数",FABSENTMINUTE3 as "第三段旷工分钟数",FABSENTMINUTE4 as "第四段旷工分钟数",FABSENTMINUTE5 as "第五段旷工分钟数",FLATETIMES as "当天迟到次数",FLATESUM as "当天迟到总分钟数",FEARLYTIMES as "当天早退次数",FEARLYSUM as "当天早退总分钟数",FABSENTTIMES as "当天旷工次数",FABSENTSUM as "当天旷工总分钟数",FSUPPLEMENTTIMES as "当天补卡次数",FCHECKINTIMES as "当天签卡次数",FLEAVETIMES as "当天请假次数",FLEAVESUM as "当天请假总分钟数",FTRIPTIMES as "当天出差次数",FTRIPSUM as "当天出差总分钟数",FUNCHECKINTIMES as "缺卡次数",FOT1 as "ot1",FOT2 as "ot2",FOT3 as "ot3",FOT4 as "ot4",FOT5 as "ot5",FOT6 as "ot6" from T_ATS_DETAILCALC_BAK

--INSERT脚本--

insert into T_ATS_DETAILCALC_BAK(FID,FSTAFFID,FPERSONID,FMGRCENTERID,FATTENDDATE,FSHIFTID,FSTARTTIME1,FENDTIME1,FSTARTTIME2,FENDTIME2,FSTARTTIME3,FENDTIME3,FSTARTTIME4,FENDTIME4,FSTARTTIME5,FENDTIME5,FLATEMINUTE1,FLATEMINUTE2,FLATEMINUTE3,FLATEMINUTE4,FLATEMINUTE5,FEARLYMINUTE1,FEARLYMINUTE3,FEARLYMINUTE2,FEARLYMINUTE4,FEARLYMINUTE5,FABSENTMINUTE1,FABSENTMINUTE2,FABSENTMINUTE3,FABSENTMINUTE4,FABSENTMINUTE5,FLATETIMES,FLATESUM,FEARLYTIMES,FEARLYSUM,FABSENTTIMES,FABSENTSUM,FSUPPLEMENTTIMES,FCHECKINTIMES,FLEAVETIMES,FLEAVESUM,FTRIPTIMES,FTRIPSUM,FUNCHECKINTIMES,FOT1,FOT2,FOT3,FOT4,FOT5,FOT6) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

--UPDATE脚本--

update T_ATS_DETAILCALC_BAK set FID=?,FSTAFFID=?,FPERSONID=?,FMGRCENTERID=?,FATTENDDATE=?,FSHIFTID=?,FSTARTTIME1=?,FENDTIME1=?,FSTARTTIME2=?,FENDTIME2=?,FSTARTTIME3=?,FENDTIME3=?,FSTARTTIME4=?,FENDTIME4=?,FSTARTTIME5=?,FENDTIME5=?,FLATEMINUTE1=?,FLATEMINUTE2=?,FLATEMINUTE3=?,FLATEMINUTE4=?,FLATEMINUTE5=?,FEARLYMINUTE1=?,FEARLYMINUTE3=?,FEARLYMINUTE2=?,FEARLYMINUTE4=?,FEARLYMINUTE5=?,FABSENTMINUTE1=?,FABSENTMINUTE2=?,FABSENTMINUTE3=?,FABSENTMINUTE4=?,FABSENTMINUTE5=?,FLATETIMES=?,FLATESUM=?,FEARLYTIMES=?,FEARLYSUM=?,FABSENTTIMES=?,FABSENTSUM=?,FSUPPLEMENTTIMES=?,FCHECKINTIMES=?,FLEAVETIMES=?,FLEAVESUM=?,FTRIPTIMES=?,FTRIPSUM=?,FUNCHECKINTIMES=?,FOT1=?,FOT2=?,FOT3=?,FOT4=?,FOT5=?,FOT6=? where FID=?

--delete脚本--

delete from T_ATS_DETAILCALC_BAK where FID=?

--给字段加备注--

alter table T_ATS_DETAILCALC_BAK comment '明细计算归档表';

alter table T_ATS_DETAILCALC_BAK modify column FID int not null  comment '内码';

alter table T_ATS_DETAILCALC_BAK modify column FSTAFFID int not null  comment '员工id';

alter table T_ATS_DETAILCALC_BAK modify column FPERSONID int not null  comment '人员id';

alter table T_ATS_DETAILCALC_BAK modify column FMGRCENTERID int not null  comment '人事管理中心';

alter table T_ATS_DETAILCALC_BAK modify column FATTENDDATE datetime default null  comment '考勤日期';

alter table T_ATS_DETAILCALC_BAK modify column FSHIFTID int not null  comment '班次';

alter table T_ATS_DETAILCALC_BAK modify column FSTARTTIME1 datetime default null  comment '第一段上班时间';

alter table T_ATS_DETAILCALC_BAK modify column FENDTIME1 datetime default null  comment '第一段下班时间';

alter table T_ATS_DETAILCALC_BAK modify column FSTARTTIME2 datetime default null  comment '第二段上班时间';

alter table T_ATS_DETAILCALC_BAK modify column FENDTIME2 datetime default null  comment '第二段下班时间';

alter table T_ATS_DETAILCALC_BAK modify column FSTARTTIME3 datetime default null  comment '第三段上班时间';

alter table T_ATS_DETAILCALC_BAK modify column FENDTIME3 datetime default null  comment '第三段下班时间';

alter table T_ATS_DETAILCALC_BAK modify column FSTARTTIME4 datetime default null  comment '第四段上班时间';

alter table T_ATS_DETAILCALC_BAK modify column FENDTIME4 datetime default null  comment '第四段下班时间';

alter table T_ATS_DETAILCALC_BAK modify column FSTARTTIME5 datetime default null  comment '第五段上班时间';

alter table T_ATS_DETAILCALC_BAK modify column FENDTIME5 datetime default null  comment '第五段下班时间';

alter table T_ATS_DETAILCALC_BAK modify column FLATEMINUTE1 decimal(19,6) default 0  comment '第一段迟到分钟数';

alter table T_ATS_DETAILCALC_BAK modify column FLATEMINUTE2 decimal(19,6) default 0  comment '第二段迟到分钟数';

alter table T_ATS_DETAILCALC_BAK modify column FLATEMINUTE3 decimal(19,6) default 0  comment '第三段迟到分钟数';

alter table T_ATS_DETAILCALC_BAK modify column FLATEMINUTE4 decimal(19,6) default 0  comment '第四段迟到分钟数';

alter table T_ATS_DETAILCALC_BAK modify column FLATEMINUTE5 decimal(19,6) default 0  comment '第五段迟到分钟数';

alter table T_ATS_DETAILCALC_BAK modify column FEARLYMINUTE1 decimal(19,6) default 0  comment '第一段早退分钟数';

alter table T_ATS_DETAILCALC_BAK modify column FEARLYMINUTE3 decimal(19,6) default 0  comment '第二段早退分钟数';

alter table T_ATS_DETAILCALC_BAK modify column FEARLYMINUTE2 decimal(19,6) default 0  comment '第三段早退分钟数';

alter table T_ATS_DETAILCALC_BAK modify column FEARLYMINUTE4 decimal(19,6) default 0  comment '第四段早退分钟数';

alter table T_ATS_DETAILCALC_BAK modify column FEARLYMINUTE5 decimal(19,6) default 0  comment '第五段早退分钟数';

alter table T_ATS_DETAILCALC_BAK modify column FABSENTMINUTE1 decimal(19,6) default 0  comment '第一段旷工分钟数';

alter table T_ATS_DETAILCALC_BAK modify column FABSENTMINUTE2 decimal(19,6) default 0  comment '第二段旷工分钟数';

alter table T_ATS_DETAILCALC_BAK modify column FABSENTMINUTE3 decimal(19,6) default 0  comment '第三段旷工分钟数';

alter table T_ATS_DETAILCALC_BAK modify column FABSENTMINUTE4 decimal(19,6) default 0  comment '第四段旷工分钟数';

alter table T_ATS_DETAILCALC_BAK modify column FABSENTMINUTE5 decimal(19,6) default 0  comment '第五段旷工分钟数';

alter table T_ATS_DETAILCALC_BAK modify column FLATETIMES int default 0  comment '当天迟到次数';

alter table T_ATS_DETAILCALC_BAK modify column FLATESUM decimal(19,6) default 0  comment '当天迟到总分钟数';

alter table T_ATS_DETAILCALC_BAK modify column FEARLYTIMES int default 0  comment '当天早退次数';

alter table T_ATS_DETAILCALC_BAK modify column FEARLYSUM decimal(19,6) default 0  comment '当天早退总分钟数';

alter table T_ATS_DETAILCALC_BAK modify column FABSENTTIMES int default 0  comment '当天旷工次数';

alter table T_ATS_DETAILCALC_BAK modify column FABSENTSUM decimal(19,6) default 0  comment '当天旷工总分钟数';

alter table T_ATS_DETAILCALC_BAK modify column FSUPPLEMENTTIMES int default 0  comment '当天补卡次数';

alter table T_ATS_DETAILCALC_BAK modify column FCHECKINTIMES int default 0  comment '当天签卡次数';

alter table T_ATS_DETAILCALC_BAK modify column FLEAVETIMES int default 0  comment '当天请假次数';

alter table T_ATS_DETAILCALC_BAK modify column FLEAVESUM decimal(19,6) default 0  comment '当天请假总分钟数';

alter table T_ATS_DETAILCALC_BAK modify column FTRIPTIMES int default 0  comment '当天出差次数';

alter table T_ATS_DETAILCALC_BAK modify column FTRIPSUM decimal(19,6) default 0  comment '当天出差总分钟数';

alter table T_ATS_DETAILCALC_BAK modify column FUNCHECKINTIMES int default 0  comment '缺卡次数';

alter table T_ATS_DETAILCALC_BAK modify column FOT1 decimal(19,6) default 0  comment 'ot1';

alter table T_ATS_DETAILCALC_BAK modify column FOT2 decimal(19,6) default 0  comment 'ot2';

alter table T_ATS_DETAILCALC_BAK modify column FOT3 decimal(19,6) default 0  comment 'ot3';

alter table T_ATS_DETAILCALC_BAK modify column FOT4 decimal(19,6) default 0  comment 'ot4';

alter table T_ATS_DETAILCALC_BAK modify column FOT5 decimal(19,6) default 0  comment 'ot5';

alter table T_ATS_DETAILCALC_BAK modify column FOT6 decimal(19,6) default 0  comment 'ot6';