$userType = $_SESSION["risk"]["user_type"];
$SQL = "WITH T_FEEDBACK AS (
SELECT S.CNO, F.FUNC_NO, MAX(FB.FEEDBACK_DATE) AS FEEDBACK_DATE
FROM JOB_SUBCON_INFO S
RIGHT OUTER JOIN JOB_SUBCON_FUNC F ON S.JNO = F.JNO AND F.CNO = S.CNO
LEFT OUTER JOIN RISK_ASSESSMENT_INFO I ON I.JNO = S.JNO
LEFT OUTER JOIN RISK_ASSESSMENT_DETAIL D ON I.ASSESSMENT_ID = D.ASSESSMENT_ID AND F.FUNC_NO = D.FUNC_NO AND D.IS_CHECK = 'Y'
LEFT OUTER JOIN RISK_FEEDBACK FB ON FB.ASSESSMENT_ID = I.ASSESSMENT_ID AND FB.CNO = S.CNO AND FB.ASSESSMENT_ITEM_ID = D.ASSESSMENT_ITEM_ID
WHERE S.JNO = :jno
AND I.SEQ = (SELECT MAX(SEQ) FROM RISK_ASSESSMENT_INFO WHERE JNO = :jno AND ASSESSMENT_TYPE = 'ASMT_REP')
GROUP BY S.CNO, F.FUNC_NO
ORDER BY CNO, FUNC_NO
)
SELECT ASSESS_SEQ, START_DATE, END_DATE, CNO, COMP_NAME, FUNC_NO, FUNC_NAME, FEEDBACK_DATE, SUBMIT_DATE, SUPERVISOR_MOD_DATE, SAFETY_MANAGER_MOD_DATE, SUPERINTENDENT_MOD_DATE, SORT_NO
FROM (
SELECT I.SEQ AS ASSESS_SEQ, I.START_DATE, I.END_DATE, S.CNO, S.COMP_NAME, F.FUNC_NO, Q.FUNC_NAME,
TO_CHAR(T.FEEDBACK_DATE, 'YYYY-MM-DD') AS FEEDBACK_DATE, TO_CHAR(A.SUBMIT_DATE, 'YYYY-MM-DD') AS SUBMIT_DATE, AI.AUTH, AI.SEQ, TO_CHAR(CAST(AI.MOD_DATE AS DATE), 'YYYY-MM-DD') AS MOD_DATE,
Q.SORT_NO
FROM RISK_ASSESSMENT_INFO I
RIGHT OUTER JOIN JOB_SUBCON_INFO S ON I.JNO = S.JNO
RIGHT OUTER JOIN JOB_SUBCON_FUNC F ON S.JNO = F.JNO AND S.CNO = F.CNO
INNER JOIN COMMON.COMM_FUNC_QHSE Q ON F.FUNC_NO = Q.FUNC_NO
INNER JOIN T_FEEDBACK T ON T.CNO = S.CNO AND T.FUNC_NO = F.FUNC_NO
INNER JOIN RISK_APPROVAL_TARGET A ON F.FUNC_NO = A.FUNC_NO AND S.CNO = A.CNO AND A.ASSESSMENT_ID = I.ASSESSMENT_ID
FULL OUTER JOIN APPROVAL_INFO AI ON A.APPROVAL_TARGET_ID = AI.APPROVAL_TARGET_ID AND AI.IS_SIGN = 'Y' AND AI.SIGN_KIND >= " . APP_STATUS_SBM . " ";
$SQL .= "WHERE I.JNO = :jno
AND I.ASSESSMENT_TYPE = 'ASMT_REP'
AND I.SEQ = (SELECT MAX(I.SEQ) FROM RISK_ASSESSMENT_INFO I WHERE I.JNO = :jno AND I.ASSESSMENT_TYPE = 'ASMT_REP') ";
if($userType == "SUB") {
$cno = $_SESSION["risk"]["cno"];
$SQL .= "AND S.CNO = {$cno}";
}
$SQL .= ")
PIVOT (
MAX(SEQ) AS SEQ , MAX(MOD_DATE) AS MOD_DATE FOR AUTH IN('SUPERVISOR' AS SUPERVISOR, 'SAFETY_MANAGER' AS SAFETY_MANAGER, 'SUPERINTENDENT' AS SUPERINTENDENT)
)
ORDER BY CNO, SORT_NO";
$params = array(
":jno" => $jno
);
$db->query($SQL, $params);SQL 짜는데만 하루 넘게 걸린 개미친놈 ㅜ
'웹 싱니 > 안전보건 시스템 (22.02 ~ 22.09 )' 카테고리의 다른 글
| DB설계서 (0) | 2022.12.21 |
|---|---|
| 위험성평가 - 피드백 (0) | 2022.12.21 |
| 위험성평가 항목편집 페이지 (0) | 2022.12.21 |
| 코드 관리 페이지 (0) | 2022.12.21 |
| 프로젝트 관리 - 편집 (0) | 2022.12.21 |
