Files
KIN/inspection/capi_device_current_status.asp

162 lines
12 KiB
Plaintext

<!-- #include virtual = "include/header.asp" -->
<!-- #include virtual = "include/left.asp" -->
<%
Dim query1, query2
%>
<!-- Content Wrapper. Contains page content -->
<div class="content-wrapper">
<!-- Content Header (Page header) -->
<div class="content-header">
<h1>CAPI ±â±â ÇöȲ</h1>
</div>
<!-- /.content-header -->
<div class="content-body">
<div class="content">
<div class="container-fluid">
<div class="row">
<div class="col-12">
<h5>±âÁØÀÏ ÇöȲ</h5>
<%
query1 = ""
query1 = query1 & " SELECT "
query1 = query1 & " CONVERT(varchar(10), GETDATE(), 120) AS BASE_DT, "
query1 = query1 & " COUNT(*) AS TOTAL_CNT, "
query1 = query1 & " SUM(CASE WHEN USE_YN = 'Y' THEN 1 ELSE 0 END) AS USE_CNT, "
query1 = query1 & " SUM(CASE WHEN USE_YN = 'N' THEN 1 ELSE 0 END) AS AVAIL_CNT, "
query1 = query1 & " SUM(CASE WHEN USE_YN = 'X' AND DISUSE_RSN_CD = '01' THEN 1 ELSE 0 END) AS LOST_CNT, "
query1 = query1 & " SUM(CASE WHEN USE_YN = 'X' AND DISUSE_RSN_CD = '02' THEN 1 ELSE 0 END) AS BROKEN_CNT, "
query1 = query1 & " SUM(CASE WHEN USE_YN = 'X' AND DISUSE_RSN_CD = '03' THEN 1 ELSE 0 END) AS FAIL_CNT, "
query1 = query1 & " SUM(CASE WHEN USE_YN = 'X' AND DISUSE_RSN_CD = '04' THEN 1 ELSE 0 END) AS STOP_CNT "
query1 = query1 & " FROM project2.dbo.T_DEVICE_NEW "
adors.open query1, adoconn, adopenstatic, adlockreadonly
%>
<table class="table table-sm table-sm2 table-bordered table-head tr-colors" style="width:600px;">
<tr>
<td rowspan="2" style="text-align:center; vertical-align:middle; font-weight:bold;">±âÁØÀÏ</td>
<td rowspan="2" style="text-align:center; vertical-align:middle; font-weight:bold;">ÃѼö·®</td>
<td rowspan="2" style="text-align:center; vertical-align:middle; font-weight:bold;">»ç¿ëÁß</td>
<td rowspan="2" style="text-align:center; vertical-align:middle; font-weight:bold;">¹Ì»ç¿ë</td>
<td colspan="4" style="text-align:center; vertical-align:middle; font-weight:bold;">´ë¿©ºÒ°¡</td>
</tr>
<tr>
<td style="text-align:center; font-weight:bold;">ºÐ½Ç</td>
<td style="text-align:center; font-weight:bold;">ÆÄ¼Õ</td>
<td style="text-align:center; font-weight:bold;">°íÀå</td>
<td style="text-align:center; font-weight:bold;">Á¤Áö</td>
</tr>
<tr>
<td style="text-align:center;"><%=adors("BASE_DT")%></td>
<td style="text-align:center;"><%=adors("TOTAL_CNT")%></td>
<td style="text-align:center;"><%=adors("USE_CNT")%></td>
<td style="text-align:center;"><%=adors("AVAIL_CNT")%></td>
<td style="text-align:center;"><%=adors("LOST_CNT")%></td>
<td style="text-align:center;"><%=adors("BROKEN_CNT")%></td>
<td style="text-align:center;"><%=adors("FAIL_CNT")%></td>
<td style="text-align:center;"><%=adors("STOP_CNT")%></td>
</tr>
</table>
<% adors.close %>
<br>
<h5>ÇÁ·ÎÁ§Æ®º° ºÐÃâ ÇöȲ</h5>
<%
query2 = ""
query2 = query2 & " SELECT "
query2 = query2 & " D.P_CHILD_SN, "
query2 = query2 & " ISNULL(VPM.P_SUBJECT, '') AS P_SUBJECT, "
query2 = query2 & " COUNT(*) AS OUT_CNT, "
query2 = query2 & " SUM(CASE WHEN M.m_gubun = '53' THEN 1 ELSE 0 END) AS STAFF_CNT, "
query2 = query2 & " SUM(CASE WHEN M.m_gubun IN ('10','21','31','41','42','43','44','45','46','51','52') THEN 1 ELSE 0 END) AS SEOUL_CNT, "
query2 = query2 & " SUM(CASE WHEN M.m_gubun = '12' THEN 1 ELSE 0 END) AS BUSAN_CNT, "
query2 = query2 & " SUM(CASE WHEN M.m_gubun = '13' THEN 1 ELSE 0 END) AS DAEGU_CNT, "
query2 = query2 & " SUM(CASE WHEN M.m_gubun = '14' THEN 1 ELSE 0 END) AS DAEJEON_CNT, "
query2 = query2 & " SUM(CASE WHEN M.m_gubun = '15' THEN 1 ELSE 0 END) AS GWANGJU_CNT, "
query2 = query2 & " SUM(CASE WHEN M.m_gubun = '16' THEN 1 ELSE 0 END) AS GANGWON_CNT, "
query2 = query2 & " SUM(CASE WHEN M.m_gubun = '17' THEN 1 ELSE 0 END) AS JEJU_CNT "
query2 = query2 & " FROM project2.dbo.T_DEVICE_NEW D "
query2 = query2 & " LEFT JOIN project2.dbo.man M ON D.MAN_ID = M.new_id "
query2 = query2 & " LEFT JOIN project2.dbo.T_PROJECT_METHOD VPM ON D.P_CHILD_SN = VPM.p_child_sn "
query2 = query2 & " WHERE D.P_CHILD_SN <> '' "
query2 = query2 & " GROUP BY D.P_CHILD_SN, VPM.P_SUBJECT "
query2 = query2 & " ORDER BY D.P_CHILD_SN "
adors.open query2, adoconn, adopenstatic, adlockreadonly
Dim rowNo : rowNo = 0
Dim totalOut, totalStaff, totalSeoul, totalBusan, totalDaegu, totalDaejeon, totalGwangju, totalGangwon, totalJeju
totalOut = 0 : totalStaff = 0 : totalSeoul = 0 : totalBusan = 0 : totalDaegu = 0 : totalDaejeon = 0 : totalGwangju = 0 : totalGangwon = 0 : totalJeju = 0
%>
<table class="table table-sm table-sm2 table-bordered table-head tr-colors">
<tr>
<td rowspan="3" style="text-align:center; vertical-align:middle; font-weight:bold;">No</td>
<td rowspan="3" style="text-align:center; vertical-align:middle; font-weight:bold;">ÇÁ·ÎÁ§Æ® ÄÚµå</td>
<td rowspan="3" style="text-align:center; vertical-align:middle; font-weight:bold;">ÇÁ·ÎÁ§Æ®¸í</td>
<td rowspan="3" style="text-align:center; vertical-align:middle; font-weight:bold;">ºÐÃâ¼ö·®</td>
<td colspan="8" style="text-align:center; vertical-align:middle; font-weight:bold;">ºÐÃâó</td>
</tr>
<tr>
<td rowspan="2" style="text-align:center; vertical-align:middle; font-weight:bold;">Á÷¿ø</td>
<td rowspan="2" style="text-align:center; vertical-align:middle; font-weight:bold;">¼­¿ï</td>
<td colspan="6" style="text-align:center; vertical-align:middle; font-weight:bold;">Áö¹æ½Ç»ç</td>
</tr>
<tr>
<td style="text-align:center; font-weight:bold;">ºÎ»ê</td>
<td style="text-align:center; font-weight:bold;">´ë±¸</td>
<td style="text-align:center; font-weight:bold;">´ëÀü</td>
<td style="text-align:center; font-weight:bold;">±¤ÁÖ</td>
<td style="text-align:center; font-weight:bold;">°­¿ø</td>
<td style="text-align:center; font-weight:bold;">Á¦ÁÖ</td>
</tr>
<% Do While Not adors.Eof
rowNo = rowNo + 1
%>
<tr>
<td style="text-align:center;"><%=rowNo%></td>
<td style="text-align:center;"><%=adors("P_CHILD_SN")%></td>
<td style="text-align:left;"><%=adors("P_SUBJECT")%></td>
<td style="text-align:center;"><%=adors("OUT_CNT")%></td>
<td style="text-align:center;"><%=adors("STAFF_CNT")%></td>
<td style="text-align:center;"><%=adors("SEOUL_CNT")%></td>
<td style="text-align:center;"><%=adors("BUSAN_CNT")%></td>
<td style="text-align:center;"><%=adors("DAEGU_CNT")%></td>
<td style="text-align:center;"><%=adors("DAEJEON_CNT")%></td>
<td style="text-align:center;"><%=adors("GWANGJU_CNT")%></td>
<td style="text-align:center;"><%=adors("GANGWON_CNT")%></td>
<td style="text-align:center;"><%=adors("JEJU_CNT")%></td>
</tr>
<%
totalOut = totalOut + adors("OUT_CNT")
totalStaff = totalStaff + adors("STAFF_CNT")
totalSeoul = totalSeoul + adors("SEOUL_CNT")
totalBusan = totalBusan + adors("BUSAN_CNT")
totalDaegu = totalDaegu + adors("DAEGU_CNT")
totalDaejeon = totalDaejeon + adors("DAEJEON_CNT")
totalGwangju = totalGwangju + adors("GWANGJU_CNT")
totalGangwon = totalGangwon + adors("GANGWON_CNT")
totalJeju = totalJeju + adors("JEJU_CNT")
adors.MoveNext
Loop
adors.close
%>
<tr>
<td colspan="3" style="text-align:center; font-weight:bold;">ÃѰè</td>
<td style="text-align:center; font-weight:bold;"><%=totalOut%></td>
<td style="text-align:center; font-weight:bold;"><%=totalStaff%></td>
<td style="text-align:center; font-weight:bold;"><%=totalSeoul%></td>
<td style="text-align:center; font-weight:bold;"><%=totalBusan%></td>
<td style="text-align:center; font-weight:bold;"><%=totalDaegu%></td>
<td style="text-align:center; font-weight:bold;"><%=totalDaejeon%></td>
<td style="text-align:center; font-weight:bold;"><%=totalGwangju%></td>
<td style="text-align:center; font-weight:bold;"><%=totalGangwon%></td>
<td style="text-align:center; font-weight:bold;"><%=totalJeju%></td>
</tr>
</table>
</div>
</div>
</div>
</div>
</div>
</div>
<!-- /.content-wrapper -->
<!-- #include virtual = "include/footer.asp" -->