ATS考勤2.19考勤计算（汇总计算归档表）

--建表脚本--

create table T_ATS_SUMCALC_BAK(

FID int not null  comment '内码'

,FSTAFFID int not null  comment '员工id'

,FPERSONID int not null  comment '人员id'

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

,FSCHEMEID int default null  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 '出差总分钟数'

,FSICKLEAVETIMES int default 0  comment '病假次数'

,FSICKLEAVESUM decimal(19,6) default 0  comment '病假总分钟数'

,FTHINGLEAVETIMES int default 0  comment '事假次数'

,FTHINGLEAVESUM decimal(19,6) default 0  comment '事假总分钟数'

,FANNUALLEAVETIMES int default 0  comment '年假次数'

,FANNUALLEAVESUM 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",FSCHEMEID as "fschemeid",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",FSICKLEAVETIMES as "fsickleavetimes",FSICKLEAVESUM as "fsickleavesum",FTHINGLEAVETIMES as "fthingleavetimes",FTHINGLEAVESUM as "fthingleavesum",FANNUALLEAVETIMES as "fannualleavetimes",FANNUALLEAVESUM as "fannualleavesum",FUNCHECKINTIMES as "funcheckintimes",FOT1 as "fot1",FOT2 as "fot2",FOT3 as "fot3",FOT4 as "fot4",FOT5 as "fot5",FOT6 as "fot6" from T_ATS_SUMCALC_BAK

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

select FID as "内码",FSTAFFID as "员工id",FPERSONID as "人员id",FMGRCENTERID as "人事管理中心",FSCHEMEID 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 "出差总分钟数",FSICKLEAVETIMES as "病假次数",FSICKLEAVESUM as "病假总分钟数",FTHINGLEAVETIMES as "事假次数",FTHINGLEAVESUM as "事假总分钟数",FANNUALLEAVETIMES as "年假次数",FANNUALLEAVESUM as "年假总分钟数",FUNCHECKINTIMES as "缺卡次数",FOT1 as "ot1",FOT2 as "ot2",FOT3 as "ot3",FOT4 as "ot4",FOT5 as "ot5",FOT6 as "ot6" from T_ATS_SUMCALC_BAK

--INSERT脚本--

insert into T_ATS_SUMCALC_BAK(FID,FSTAFFID,FPERSONID,FMGRCENTERID,FSCHEMEID,FLATETIMES,FLATESUM,FEARLYTIMES,FEARLYSUM,FABSENTTIMES,FABSENTSUM,FSUPPLEMENTTIMES,FCHECKINTIMES,FLEAVETIMES,FLEAVESUM,FTRIPTIMES,FTRIPSUM,FSICKLEAVETIMES,FSICKLEAVESUM,FTHINGLEAVETIMES,FTHINGLEAVESUM,FANNUALLEAVETIMES,FANNUALLEAVESUM,FUNCHECKINTIMES,FOT1,FOT2,FOT3,FOT4,FOT5,FOT6) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

--UPDATE脚本--

update T_ATS_SUMCALC_BAK set FID=?,FSTAFFID=?,FPERSONID=?,FMGRCENTERID=?,FSCHEMEID=?,FLATETIMES=?,FLATESUM=?,FEARLYTIMES=?,FEARLYSUM=?,FABSENTTIMES=?,FABSENTSUM=?,FSUPPLEMENTTIMES=?,FCHECKINTIMES=?,FLEAVETIMES=?,FLEAVESUM=?,FTRIPTIMES=?,FTRIPSUM=?,FSICKLEAVETIMES=?,FSICKLEAVESUM=?,FTHINGLEAVETIMES=?,FTHINGLEAVESUM=?,FANNUALLEAVETIMES=?,FANNUALLEAVESUM=?,FUNCHECKINTIMES=?,FOT1=?,FOT2=?,FOT3=?,FOT4=?,FOT5=?,FOT6=? where FID=?

--delete脚本--

delete from T_ATS_SUMCALC_BAK where FID=?

--给字段加备注--

alter table T_ATS_SUMCALC_BAK comment '汇总计算归档表';

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

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

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

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

alter table T_ATS_SUMCALC_BAK modify column FSCHEMEID int default null  comment '汇总方案';

alter table T_ATS_SUMCALC_BAK modify column FLATETIMES int default 0  comment '迟到次数';

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

alter table T_ATS_SUMCALC_BAK modify column FEARLYTIMES int default 0  comment '早退次数';

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

alter table T_ATS_SUMCALC_BAK modify column FABSENTTIMES int default 0  comment '旷工次数';

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

alter table T_ATS_SUMCALC_BAK modify column FSUPPLEMENTTIMES int default 0  comment '补卡次数';

alter table T_ATS_SUMCALC_BAK modify column FCHECKINTIMES int default 0  comment '签卡次数';

alter table T_ATS_SUMCALC_BAK modify column FLEAVETIMES int default 0  comment '请假次数';

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

alter table T_ATS_SUMCALC_BAK modify column FTRIPTIMES int default 0  comment '出差次数';

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

alter table T_ATS_SUMCALC_BAK modify column FSICKLEAVETIMES int default 0  comment '病假次数';

alter table T_ATS_SUMCALC_BAK modify column FSICKLEAVESUM decimal(19,6) default 0  comment '病假总分钟数';

alter table T_ATS_SUMCALC_BAK modify column FTHINGLEAVETIMES int default 0  comment '事假次数';

alter table T_ATS_SUMCALC_BAK modify column FTHINGLEAVESUM decimal(19,6) default 0  comment '事假总分钟数';

alter table T_ATS_SUMCALC_BAK modify column FANNUALLEAVETIMES int default 0  comment '年假次数';

alter table T_ATS_SUMCALC_BAK modify column FANNUALLEAVESUM decimal(19,6) default 0  comment '年假总分钟数';

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

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

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

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

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

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

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