17,377
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE HIPS_HR.HR_WORK_ATTEND
(
PERSONNEL_ID VARCHAR2(10) NOT NULL,
FREQUENCY VARCHAR2(10) NOT NULL,
WORKDAY VARCHAR2(8) NOT NULL,
WORKTIME DATE,
CLOSETIME DATE,
OVERTIMEFLG CHAR(1) NOT NULL,
UPDATE_TIME DATE,
UPDATE_USERID VARCHAR2(20),
CREATE_TIME DATE,
CREATE_USERID VARCHAR2(20),
EXCLUSIVEKEY VARCHAR2(100),
CONSTRAINT WORK_ATTEND_KEY PRIMARY KEY (PERSONNEL_ID, WORKDAY, FREQUENCY) USING INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 64K MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
LOGGING
)
PCTFREE 10
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 64K MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
NOCACHE
LOGGING
/
COMMENT ON TABLE HIPS_HR.HR_WORK_ATTEND IS '考勤信息表'
/
COMMENT ON COLUMN HIPS_HR.HR_WORK_ATTEND.PERSONNEL_ID IS '员工ID'
/
COMMENT ON COLUMN HIPS_HR.HR_WORK_ATTEND.FREQUENCY IS '班次'
/
COMMENT ON COLUMN HIPS_HR.HR_WORK_ATTEND.WORKDAY IS '工作日'
/
COMMENT ON COLUMN HIPS_HR.HR_WORK_ATTEND.WORKTIME IS '上班时间'
/
COMMENT ON COLUMN HIPS_HR.HR_WORK_ATTEND.CLOSETIME IS '下班时间'
/
COMMENT ON COLUMN HIPS_HR.HR_WORK_ATTEND.OVERTIMEFLG IS '是否加班 1:是 0:不是'
/
COMMENT ON COLUMN HIPS_HR.HR_WORK_ATTEND.UPDATE_TIME IS '更新时间'
/
COMMENT ON COLUMN HIPS_HR.HR_WORK_ATTEND.UPDATE_USERID IS '更新者'
/
COMMENT ON COLUMN HIPS_HR.HR_WORK_ATTEND.CREATE_TIME IS '创建时间'
/
COMMENT ON COLUMN HIPS_HR.HR_WORK_ATTEND.CREATE_USERID IS '创建者'
/
COMMENT ON COLUMN HIPS_HR.HR_WORK_ATTEND.EXCLUSIVEKEY IS '排他键'
/
CREATE TABLE HIPS_HR.HR_LEAVE
(
LEAVE_ID VARCHAR2(15) NOT NULL,
PERSONNEL_ID VARCHAR2(50),
FREQUENCY VARCHAR2(10),
LEAVE_DATE DATE,
LEAVE_LENGTH_DAY NUMBER(10,0),
LEAVE_LENGTH_HOUR NUMBER(10,0),
LEAVE_LENGTH_MIN NUMBER(10,0),
LEAVE_KSSJ DATE,
LEAVE_JSSJ DATE,
LEAVE_TYPE VARCHAR2(5),
LEAVE_REASON VARCHAR2(200),
STATUS VARCHAR2(200),
UPDATE_TIME DATE,
UPDATE_USERID VARCHAR2(20),
CREATE_TIME DATE,
CREATE_USERID VARCHAR2(20),
EXCLUSIVEKEY VARCHAR2(100) NOT NULL,
CONSTRAINT HR_LEAVE_PK PRIMARY KEY (LEAVE_ID) USING INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 64K MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
LOGGING
)
PCTFREE 10
MAXTRANS 255
TABLESPACE USERS
STORAGE(INITIAL 64K MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
NOCACHE
LOGGING
/
COMMENT ON TABLE HIPS_HR.HR_LEAVE IS '员工请假表'
/
COMMENT ON COLUMN HIPS_HR.HR_LEAVE.LEAVE_ID IS '请假编号'
/
COMMENT ON COLUMN HIPS_HR.HR_LEAVE.PERSONNEL_ID IS '用户ID'
/
COMMENT ON COLUMN HIPS_HR.HR_LEAVE.FREQUENCY IS '班次'
/
COMMENT ON COLUMN HIPS_HR.HR_LEAVE.LEAVE_DATE IS '请假日期'
/
COMMENT ON COLUMN HIPS_HR.HR_LEAVE.LEAVE_LENGTH_DAY IS '请假时长(天)'
/
COMMENT ON COLUMN HIPS_HR.HR_LEAVE.LEAVE_LENGTH_HOUR IS '请假时长(小时)'
/
COMMENT ON COLUMN HIPS_HR.HR_LEAVE.LEAVE_LENGTH_MIN IS '请假时长(分钟)'
/
COMMENT ON COLUMN HIPS_HR.HR_LEAVE.LEAVE_KSSJ IS '请假开始时间'
/
COMMENT ON COLUMN HIPS_HR.HR_LEAVE.LEAVE_JSSJ IS '请假结束时间'
/
COMMENT ON COLUMN HIPS_HR.HR_LEAVE.LEAVE_TYPE IS '请假类型'
/
COMMENT ON COLUMN HIPS_HR.HR_LEAVE.LEAVE_REASON IS '请假事由'
/
COMMENT ON COLUMN HIPS_HR.HR_LEAVE.STATUS IS '单据状态'
/
COMMENT ON COLUMN HIPS_HR.HR_LEAVE.UPDATE_TIME IS '更新时间'
/
COMMENT ON COLUMN HIPS_HR.HR_LEAVE.UPDATE_USERID IS '更新者'
/
COMMENT ON COLUMN HIPS_HR.HR_LEAVE.CREATE_TIME IS '创建时间'
/
COMMENT ON COLUMN HIPS_HR.HR_LEAVE.CREATE_USERID IS '创建者'
/
COMMENT ON COLUMN HIPS_HR.HR_LEAVE.EXCLUSIVEKEY IS '排他键(使用UUID进行排他)'
/
<!--考勤信息检索-->
<select id="searchAttendInfo" parameterClass="AttenceEntity" resultClass="WorkAttendEntity">
select
workAttend.PERSONNEL_ID as personnelId,
personnel.ATTENCE_ID as attenceId,
personnel.PERSONNEL_NAME as personnelName,
workAttend.WORKDAY as workDay,
workAttend.FREQUENCY as frequency,
workAttend.WORKTIME as workTime,
workAttend.CLOSETIME as closeTime,
workAttend.OVERTIMEFLG as overTimeFlg,
leave.LEAVE_ID as isLeave
from
HR_WORK_ATTEND workAttend
left join HR_PERSONNEL personnel on personnel.PERSONNEL_ID = workAttend.PERSONNEL_ID
left join (select count(LEAVE_ID) as LEAVE_ID,PERSONNEL_ID,LEAVE_KSSJ,LEAVE_JSSJ from hr_leave
group by PERSONNEL_ID,LEAVE_KSSJ,LEAVE_JSSJ) leave
on <![CDATA[workAttend.WORKDAY >= to_char(leave.LEAVE_KSSJ, 'YYYYMMDD')]]>
and <![CDATA[workAttend.WORKDAY <= to_char(leave.LEAVE_JSSJ, 'YYYYMMDD')]]>
and leave.PERSONNEL_ID = workAttend.PERSONNEL_ID
<dynamic prepend="where">
<isNotEmpty prepend="and" property="personnelName">
personnel.PERSONNEL_NAME like '%'||#personnelName#||'%'
</isNotEmpty>
<isNotEmpty prepend="and" property="attendYear">
workAttend.WORKDAY like #attendYear#||'%'
</isNotEmpty>
<isNotEmpty prepend="and" property="attendMonth">
substr(workAttend.WORKDAY,5,2) =#attendMonth#
</isNotEmpty>
<isNotEmpty prepend="and" property="attendDay">
substr(workAttend.WORKDAY,7,2) =#attendDay#
</isNotEmpty>
<isNotEmpty prepend="and" property="personnelId">
workAttend.PERSONNEL_ID = #personnelId#
</isNotEmpty>
<isNotEmpty prepend="and" property="attenceId">
personnel.ATTENCE_ID = #attenceId#
</isNotEmpty>
</dynamic>
order by workAttend.PERSONNEL_ID,workAttend.WORKDAY
</select>