「休假LINE BOT」:修訂間差異
新頁面: ==查休假人員== <SQL> SELECT UD_BRANCHS,DO_EMP,UD_NAME,NAME FROM DayOffLog A JOIN UserData B ON DO_EMP=UD_ID JOIN DayOff C ON A.DO_TYPE=C.ID WHERE Convert(varchar,getdate(),112) B... |
|||
| (未顯示同一使用者於中間所作的 1 次修訂) | |||
| 第1行: | 第1行: | ||
==查打卡異常人員== | |||
==查休假人員== | ==查休假人員== | ||
<SQL> | <SQL> | ||
| 第7行: | 第9行: | ||
WHERE Convert(varchar,getdate(),112) BETWEEN Convert(varchar,DO_FDATE,112) AND Convert(varchar,DO_TDATE,112) | WHERE Convert(varchar,getdate(),112) BETWEEN Convert(varchar,DO_FDATE,112) AND Convert(varchar,DO_TDATE,112) | ||
ORDER BY UD_BRANCHS ASC,DO_EMP ASC | ORDER BY UD_BRANCHS ASC,DO_EMP ASC | ||
</SQL> | |||
==查當時未打卡人員== | |||
<SQL> | |||
SELECT RTRIM(UD_BRANCHS)+'\t'+RTRIM(UD_IDA)+'\t'+RTRIM(UD_NAME)+'\t' MEMBER | |||
FROM | |||
(SELECT RTRIM(UD_ID) UD_IDA FROM UserData WHERE UD_INDATE IS NOT NULL AND UD_OUTDATE IS NULL | |||
AND UD_ID<>'A0032' AND UD_ID<>'B0192')X | |||
LEFT JOIN | |||
(SELECT DO_EMP FROM DayOffLog WHERE '".$today."' BETWEEN Convert(varchar,DO_FDATE,112) | |||
AND Convert(varchar,DO_TDATE,112) )Y | |||
ON DO_EMP=UD_IDA | |||
LEFT JOIN | |||
CardLog ON UD_IDA=CL_UDID AND Convert(varchar,CL_TIME,112)= :str | |||
JOIN UserData ON UD_IDA=UD_ID | |||
WHERE DO_EMP IS NULL AND CL_TIME IS NULL | |||
</SQL> | </SQL> | ||
於 2019年1月11日 (五) 16:25 的最新修訂
查打卡異常人員
查休假人員
<SQL> SELECT UD_BRANCHS,DO_EMP,UD_NAME,NAME FROM DayOffLog A JOIN UserData B ON DO_EMP=UD_ID JOIN DayOff C ON A.DO_TYPE=C.ID WHERE Convert(varchar,getdate(),112) BETWEEN Convert(varchar,DO_FDATE,112) AND Convert(varchar,DO_TDATE,112) ORDER BY UD_BRANCHS ASC,DO_EMP ASC </SQL>
查當時未打卡人員
<SQL> SELECT RTRIM(UD_BRANCHS)+'\t'+RTRIM(UD_IDA)+'\t'+RTRIM(UD_NAME)+'\t' MEMBER FROM (SELECT RTRIM(UD_ID) UD_IDA FROM UserData WHERE UD_INDATE IS NOT NULL AND UD_OUTDATE IS NULL AND UD_ID<>'A0032' AND UD_ID<>'B0192')X LEFT JOIN (SELECT DO_EMP FROM DayOffLog WHERE '".$today."' BETWEEN Convert(varchar,DO_FDATE,112) AND Convert(varchar,DO_TDATE,112) )Y ON DO_EMP=UD_IDA LEFT JOIN CardLog ON UD_IDA=CL_UDID AND Convert(varchar,CL_TIME,112)= :str JOIN UserData ON UD_IDA=UD_ID WHERE DO_EMP IS NULL AND CL_TIME IS NULL </SQL>