D.CAMP 리뉴얼

h1. View 테이블

<pre>

CREATE VIEW project_summary_view AS

select t1.*,

       IFNULL(t2.worker_cnt, 0) as worker_cnt, IFNULL(t2.insp_cnt, 0) as insp_cnt, IFNULL(t2.uploader_cnt, 0) as uploader_cnt

from (

    select p.project_id,

       p.project_nm,

       p.data_type,

       count(pd.data_id) as tot_cnt,

       iFNULL(SUM(case when data_status_ccd = 'ING' and data_dtl_status_ccd = 'ASSIGN' then 1 else 0 end) , 0) as assign_done_cnt,

       iFNULL(SUM(case when data_status_ccd = 'WAIT' and data_dtl_status_ccd = 'WAIT' then 1 else 0 end), 0)  as assign_wait_cnt ,

       iFNULL(SUM(case when data_status_ccd = 'ISSUE' then 1 else 0 end) , 0) as issue_cnt ,

       iFNULL(SUM(case when data_status_ccd = 'DONE' and data_dtl_status_ccd = 'DONE' then 1 else 0 end), 0)  as done_cnt,

       iFNULL(SUM(case when data_type='TABLE' and data_status_ccd = 'ING' and data_dtl_status_ccd = 'APPR' then 1 else 0 end), 0)  as appr_cnt

from project p

left join project_data pd on p.project_id = pd.project_id

group by p.project_id

order by p.project_id

              ) t1

left join (

    select project_id,

           sum(case when user_role = 'WORKER' then 1 else 0 end) as worker_cnt,

           sum(case when user_role = 'INSPECTOR' then 1 else 0 end) as insp_cnt,

           sum(case when user_role = 'UPLOADER' then 1 else 0 end) as uploader_cnt

    from project_user_assigned pua

    join users u on u.user_seq = pua.user_seq

    group by project_id

    order by project_id

) t2 on t1.project_id = t2.project_id ;

</pre>