泛微 Ecology9
泛微 Ecology9 获取人员待办量排名
使用SQL查询待办量为前十名的用户，用于展示预警，提升员工审批积极性。
SELECT TOP
10 ( SELECT lastname FROM hrmresource WHERE id = userid ) AS 用户名称,
COUNT ( requestid ) AS 流程待办数量
FROM
workflow_currentoperator
WHERE
workflowtype > 0
AND isremark IN ( '0', '1', '5', '7' )
AND islasttimes = 1
AND usertype = '0'
AND EXISTS ( SELECT 1 FROM hrmresource WHERE hrmresource.id= workflow_currentoperator.userid AND hrmresource.status IN ( 0, 1, 2, 3 ) )
AND EXISTS ( SELECT 1 FROM workflow_requestbase WHERE requestid = workflow_currentoperator.requestid AND ( deleted = 0 OR deleted IS NULL ) )
AND EXISTS ( SELECT 1 FROM workflow_base WHERE id = workflow_currentoperator.workflowid AND ( isvalid = 1 ) )
GROUP BY
userid
ORDER BY
COUNT ( requestid ) DESCEcology9 常用SQL大全
分部相关
正在使用分部
SELECT * FROM HrmSubCompany WHERE canceled IS NULL
部门相关
正在使用的部门信息
SELECT * FROM HrmDepartment WHERE canceled is NULL or canceled = 0
正在使用部门总数
SELECT count(*) FROM HrmDepartment WHERE canceled is NULL or canceled = 0
递归查询所有上级部门
WITH dept_h AS (
SELECT id,departmentname,supdepid, CAST(departmentname AS VARCHAR(MAX)) AS fullName FROM HrmDepartment WHERE (canceled IS NULL OR canceled = 0 ) AND supdepid = 0
UNION ALL
SELECT t1.id,t1.departmentname,t1.supdepid,CONCAT(t2.fullName,'>', t1.departmentname) FROM HrmDepartment AS t1
INNER JOIN dept_h AS t2 ON t1.supdepid = t2.id
WHERE t1.supdepid != 0 AND (canceled IS NULL OR canceled = 0)
)
SELECT * FROM dept_h
人事
每日实际工作时间+加班时间统计
-- MySQl代码
SELECT
t1.ID AS ID,
t1.WORKCODE AS WORKCODE,
t1.SUBCOMPANYID1 AS SUBCOMPANYID1,
t1.DEPARTMENTID AS DEPARTMENTID,
t1.DSPORDER AS dsporder,
t2.kqdate AS kqdate,
t2.attendancemins AS attendancemins,
t3.duration_min AS duration_min,
round( ( ( COALESCE ( t2.attendancemins, 0 ) + COALESCE ( t3.duration_min, 0 ) ) / 60 ), 2 ) AS total_hours,
dayofmonth ( CAST ( t2.kqdate AS DATE ) ) AS dd
FROM
(
(
hrmresource AS t1
INNER JOIN ( SELECT kq_format_total.kqdate AS kqdate, kq_format_total.resourceid AS resourceid, kq_format_total.attendancemins AS attendancemins FROM kq_format_total ) AS t2 ON ( ( t2.resourceid = t1.ID ) )
)
LEFT JOIN ( SELECT kq_flow_overtime.belongdate AS belongdate, kq_flow_overtime.resourceid AS resourceid, kq_flow_overtime.duration_min AS duration_min FROM kq_flow_overtime ) AS t3 ON ( ( ( t3.belongdate = t2.kqdate ) AND ( t3.resourceid = t2.resourceid ) ) )
)
ORDER BY
t1.DEPARTMENTID ASC,
t1.DSPORDER ASC,
t2.kqdate ASC
按照导入标准模板查询系统内所有人员信息
WITH dept_h AS (SELECT id, departmentname, supdepid, CAST(departmentname AS VARCHAR(MAX)) AS fullName
FROM HrmDepartment
WHERE (canceled IS NULL OR canceled = 0)
AND supdepid = 0
UNION ALL
SELECT t1.id, t1.departmentname, t1.supdepid, CONCAT(t2.fullName, '>', t1.departmentname)
FROM HrmDepartment AS t1
INNER JOIN dept_h AS t2 ON t1.supdepid = t2.id
WHERE t1.supdepid != 0
AND (canceled IS NULL OR canceled = 0))
SELECT N'分部名称' AS 分部,
dept.fullName AS 部门,
hr.workcode AS 工作编号,
hr.lastname AS 员工姓名,
hr.loginid AS 登录账号,
hr.password AS 密码,
-- hr.accounttype AS 账号类型,
-- hr.belongto AS 所属主账号,
0 AS 账号类型,
-1 AS 所属主账号,
CASE
hr.sex
WHEN 0 THEN
N'男'
WHEN 1 THEN
N'女'
END AS 性别,
hr.seclevel AS 安全等级,
jt.jobtitlename AS 岗位,
ja.jobactivityname AS 职务,
jg.jobgroupname AS 职务类型,
jc.name AS 职称,
hr.JOBLEVEL AS 职级,
hr2.lastname AS 直接上级,
jt.jobresponsibility AS 岗位职责,
hr3.lastname AS 助理,
CASE
hr.status
WHEN 0 THEN
N'试用'
WHEN 1 THEN
N'正式'
WHEN 2 THEN
N'临时'
WHEN 3 THEN
N'试用延期'
WHEN 4 THEN
N'解聘'
WHEN 5 THEN
N'离职'
WHEN 6 THEN
N'退休'
WHEN 7 THEN
N'无效'
END AS 状态,
hr.workroom AS 办公室,
hl.locationname AS 办公地点,
hr.extphone AS 办公电话,
hr.mobile AS 移动电话,
hr.telephone AS 其它电话,
hr.fax AS 传真,
hr.email AS 电子邮件,
N'简体中文' AS 系统语言,
hr.birthday AS 出生日期,
hr.folk AS 民族,
hr.nativeplace AS 籍贯,
hr.regresidentplace AS 户口,
hr.certificatenum AS 身份证号码,
CASE hr.maritalstatus
WHEN 1 THEN N'已婚'
WHEN 0 THEN N'未婚'
END AS 婚姻状况,
hr.policy AS 政治面貌,
hr.bememberdate AS 入团时间,
hr.bepartydate AS 入党时间,
CASE hr.islabouunion
WHEN 0 THEN N'否'
WHEN 1 THEN N'是'
END AS 工会会员,
hdl.name AS 学历,
hr.degree AS 学位,
CASE hr.healthinfo
WHEN 0 THEN N'优秀'
WHEN 1 THEN N'良好'
WHEN 2 THEN N'一般'
WHEN 3 THEN N'较差'
END AS 健康状况,
hr.height AS 身高,
hr.weight AS 体重,
huk.name AS 用工性质,
hr.startdate AS 合同开始日期,
hr.enddate AS 合同结束日期,
hr.probationenddate AS 试用期结束日期,
hr.companystartdate AS 入职日期,
hr.workstartdate AS 参加工作日期,
hr.residentplace AS 现居住地,
hr.homeaddress AS 家庭联系方式,
-- hr.tempresidentnumber AS 暂住证号码,
-- hr.accountname AS 工资账号户名,
-- hr.bankid1 AS 工资银行,
-- hr.accountid1 AS 工资账号,
-- hr.accumfundaccount AS 公积金账户,
NULL AS 暂住证号码,
NULL AS 工资账号户名,
NULL AS 工资银行,
NULL AS 工资账号,
NULL AS 公积金账户,
'' AS 显示顺序
FROM HrmResource AS hr
LEFT JOIN HrmJobTitles AS jt ON jt.id = hr.jobtitle
LEFT JOIN HrmJobActivities AS ja ON ja.id = jt.jobactivityid
LEFT JOIN HrmJobGroups AS jg ON ja.jobgroupid = jg.id
LEFT JOIN HrmJobCall AS jc ON hr.jobcall = jc.id
LEFT JOIN HrmResource AS hr2 ON hr.managerid = hr2.id
LEFT JOIN HrmResource AS hr3 ON hr.assistantid = hr3.id
LEFT JOIN HrmLocations AS hl ON hl.id = hr.locationid
LEFT JOIN hrmeducationlevel AS hdl ON hdl.id = hr.educationlevel
LEFT JOIN HrmUseKind AS huk ON huk.id = hr.usekind
LEFT JOIN dept_h as dept ON dept.id = hr.departmentid
WHERE hr.status in (0, 1, 2, 3);
摘抄自网络，便于检索查找。
