프로젝트 SQL 저장

-- 과제 시퀀스 생성
CREATE SEQUENCE SEQ_TASK_NO;
-- 프로젝트 시퀀스 생성
CREATE SEQUENCE SEQ_PROJECT_NO;

-- DB 수정 삭제
ALTER TABLE OMEN.PROJECT_TASK RENAME COLUMN FIN_FLAG TO WORK_STATE;
ALTER TABLE OMEN.PROJECT_TASK MODIFY WORK_STATE VARCHAR2(30);

ALTER TABLE OMEN.PROJECT RENAME COLUMN STATE TO WORK_STATE;
ALTER TABLE OMEN.PROJECT MODIFY WORK_STATE VARCHAR2(30);

-- 결재예시
SELECT WORK_NO, TYPE_NO, TITLE , SEND_DATE , WORK_STATE , OPINION , TYPE_NAME, FILE_RENAME
FROM "WORK"
LEFT JOIN WORK_TYPE USING(TYPE_NO)
LEFT JOIN UPLOAD_FILE USING(WORK_NO)
WHERE MEM_NO = 2
AND TO_CHAR(SEND_DATE, 'YYYY-MM-DD') = TO_CHAR(SYSDATE, 'YYYY-MM-DD')
ORDER BY SEND_DATE DESC

-- 프로젝트 조회
SELECT PROJECT_NO AS WORK_NO ,'4' AS TYPE_NO, '프로젝트' AS TYPE_NAME, TITLE , SEND_DATE ,WORK_STATE, OPINION , FILE_RENAME
FROM PROJECT
LEFT JOIN UPLOAD_FILE USING(PROJECT_NO)
WHERE MEM_NO = 2
AND TO_CHAR(SEND_DATE, 'YYYY-MM-DD') = TO_CHAR(SYSDATE, 'YYYY-MM-DD')
ORDER BY SEND_DATE DESC

-- 과제 조회
SELECT TASK_NO AS WORK_NO ,'5' AS TYPE_NO, '과제' AS TYPE_NAME, TITLE , SEND_DATE ,WORK_STATE, OPINION , FILE_RENAME
FROM PROJECT_TASK
LEFT JOIN UPLOAD_FILE USING(TASK_NO)
WHERE MEM_NO = #{memNo}
AND TO_CHAR(SEND_DATE, 'YYYY-MM-DD') = TO_CHAR(SYSDATE, 'YYYY-MM-DD')
ORDER BY SEND_DATE DESC

INSERT INTO PROJECT_TASK (TASK_NO, PROJECT_NO, DEPT_NO) VALUES (
3, 2, (SELECT DEPT_NO FROM MEMBER WHERE MEM_NO = 5))

SELECT TITLE, PROJECT_NO
FROM PROJECT
WHERE DEPT_NO = 3

-- 구분을 위해 부서가 필요해서 추가
ALTER TABLE OMEN.PROJECT ADD DEPT_NO NUMBER;
ALTER TABLE OMEN.PROJECT_TASK ADD DEPT_NO NUMBER;

-- 프로젝트 생성
INSERT INTO PROJECT(PROJECT_NO, MEM_NO, TITLE, CONTENT,
SEND_DATE, "START", "END", "NEXT",
TEMP_SAVE, WORK_STATE,
APPROVAL_DATE, APPROVAL_LIST,
OPINION, DEPT_NO)
VALUES ( #{projectNo}, #{memNo}, #{title}, #{content},
DEFAULT, #{start}, #{end}, #{next},
NULL, '진행중',
NULL, NULL,
NULL, #{deptNo})
)

-- 생성일은 SYSDATE
ALTER TABLE OMEN.PROJECT MODIFY SEND_DATE DATE DEFAULT SYSDATE;

-- 부서 과제 목록 조회
SELECT PROJECT_NO , P.TITLE AS PROJECT_TITLE, TASK_NO, PT.TITLE AS TASK_TITLE
FROM PROJECT P
JOIN PROJECT_TASK PT USING (PROJECT_NO)
WHERE PT.DEPT_NO = 3
AND P.WORK_STATE = '진행중'
AND PT.WORK_STATE IS NULL
ORDER BY PT.TASK_NO ASC

-- 부서 프로젝트 목록 조회
SELECT PROJECT_NO , TITLE AS PROJECT_TITLE
FROM PROJECT
WHERE DEPT_NO = 3 AND WORK_STATE = '진행중'
ORDER BY PROJECT_NO ASC

-- 과제 업로드(실작성)
UPDATE PROJECT_TASK
SET MEM_NO = #{memNo},
CONTENT = #{},
SEND_DATE = SYSDATE,
"NEXT" = #{},
WORK_STATE = '진행중'
WHERE TASK_NO = #{taskNo}

UPDATE PROJECT_TASK
SET MEM_NO = 3 ,
CONTENT = '실험용',
SEND_DATE = SYSDATE,
"NEXT" = 2,
WORK_STATE = '진행중'
WHERE TASK_NO = 13