일단 하기는 하였는데, 말이 안된다고 생각함! 문제가 뭐냐 하면은 모니터링 데이터를 제가 가공하는것이 아니라, 누군가 가동해 놓은 데이터를 가지고 실시간으로 전달해주는 행단위로 입력된것으로 데이터를 뽑습니다.
위와 같은 화면을 뽑기 위해서 처리해야 될 일은 많지만 이미 내가 만들지 않았고, 있는 정보를 가지고 처리를 해야 합니다.
위와 같은 형식으로 행단위로 엄청나게 쌓이기 시작합니다. 매일 몇초 단위로 데이터가 계속 들어오는 상태입니다.
1개의 Table를 가지고 데이터를 뽑는게 이렇게 힘들것이라고 이번에 처음 느꼈습니다.
※ 가능하면 2번 다시 하고 싶지 않음
이제 이름별로 해서 가동률을 한번 뽑아보도록 하겠습니다. MySQL에서 다음의 쿼리문들 사용하였습니다.
위의 화면에 필요한 부분이 아래의 Query 입니다. 보기보다 쉽죠? 만들어 내는대는 하루지만 정리해 놓고 보면은 쉽게 보여집니다.
// 이름별 가동율
SELECT A.MACHINE_NAME,
CAST(IFNULL(B.MACHINE_PCNT, 0) AS UNSIGNED ) AS MACHINE_PRODUCT_CNT,
MIN(C.MACHINE_DATETIME_MIN) AS MACHINE_TIME_MIN,
MAX(C.MACHINE_DATETIME_MAX) AS MACHINE_TIME_MAX,
CAST(IFNULL(C.USE_TIME, '0') AS UNSIGNED ) AS USE_TIME,
CAST(IFNULL(E.USE_TIME_STOP, '0') AS UNSIGNED ) AS USE_TIME_STOP,
CAST(SUM(DATETIME_SPAN) AS UNSIGNED) AS TIME_TOTAL,
IFNULL(((IFNULL(C.USE_TIME, 0) / IFNULL(D.TIME_TOTAL, 0)) * 100), 0) AS MACHINE_RATE
FROM PLC_MONITORING AS A
LEFT OUTER JOIN (
SELECT MACHINE_NAME, SUM(PRODUCT_RESULT) AS MACHINE_PCNT FROM PLC_MONITORING_PRODUCT WHERE START_DATETIME >= '".$startDate."' AND END_DATETIME <= '".$finishDate."' GROUP BY MACHINE_NAME
) AS B ON B.MACHINE_NAME = A.MACHINE_NAME
LEFT OUTER JOIN (
SELECT MACHINE_NAME, MIN(MACHINE_DATETIME) AS MACHINE_DATETIME_MIN, MAX(MACHINE_DATETIME) AS MACHINE_DATETIME_MAX,
CAST(SUM(DATETIME_SPAN) AS UNSIGNED) AS USE_TIME
FROM PLC_MONITORING
WHERE MACHINE_STATUS = 'OPERATE' AND MACHINE_DATETIME >= '".$startDate."' AND MACHINE_DATETIME <= '".$finishDate."'
GROUP BY MACHINE_NAME
) AS C ON C.MACHINE_NAME = A.MACHINE_NAME
LEFT OUTER JOIN (
SELECT MACHINE_NAME, CAST(SUM(DATETIME_SPAN) AS UNSIGNED) AS TIME_TOTAL
FROM PLC_MONITORING
WHERE MACHINE_DATETIME >= '".$startDate."' AND MACHINE_DATETIME <= '".$finishDate."'
GROUP BY MACHINE_NAME
) AS D ON D.MACHINE_NAME = A.MACHINE_NAME
LEFT OUTER JOIN (
SELECT MACHINE_NAME, CAST(SUM(DATETIME_SPAN) AS UNSIGNED) AS USE_TIME_STOP
FROM PLC_MONITORING
WHERE MACHINE_STATUS != 'OPERATE' AND MACHINE_DATETIME >= '".$startDate."' AND MACHINE_DATETIME <= '".$finishDate."'
GROUP BY MACHINE_NAME
) AS E ON E.MACHINE_NAME = A.MACHINE_NAME
WHERE A.MACHINE_DATETIME >= '".$startDate."' AND A.MACHINE_DATETIME <= '".$finishDate."'
GROUP BY A.MACHINE_NAME
위에 사진에 보면은 가동시간, 비가동 시간이 존재하는데 쿼리문에서는 ‘초’ 단위로 뽑기 때문에 우리가 알고 있는 시간으로 변환을 해줘야 합니다.
foreach($rs as $key=>$value) {
$reData[$key] = $value;
// 가동시간이 분단위 인것을 시:분 단위로 변환
if($value['USE_TIME']) {
if( $value['USE_TIME'] > 86400) {
$reData[$key]['USE_TIME'] = gmdate('j일 G시간 i분', $value['USE_TIME']);
} else {
$reData[$key]['USE_TIME'] = gmdate('G시간 i분', $value['USE_TIME']);
}
}
// 가동시간이 분단위 인것을 시:분 단위로 변환
if($value['USE_TIME_STOP']) {
if( $value['USE_TIME_STOP'] > 86400) {
$reData[$key]['USE_TIME_STOP'] = gmdate('j일 G시간 i분', $value['USE_TIME_STOP']);
} else {
$reData[$key]['USE_TIME_STOP'] = gmdate('G시간 i분', $value['USE_TIME_STOP']);
}
}
}
보기 좋게 시간 계산을 하고 표에 나도도록 이쁘게 배열을 만들어서 담습니다. 그러면은 View에서 사용할대는 반복문을 써서 쉽게 데이터가 도출 될 수 있습니다.
다음으로 해야 할것이 시간대 단위로 데이터를 뽑아 오는것입니다. 종합이 있으면은 세부 사항도 있어야 하지 않을까? 하는 요청에 의해서 만들어진 것입니다.
기록된 table에서 기록된 시간을 가지고 장난을 쳐서 데이터를 뽑아 낸것입니다. 정말 재밌게 만든 쿼리 문이 아닐까 싶습니다.
SELECT A.MACHINE_NAME,
CAST(DATE_FORMAT(A.MACHINE_DATETIME, '%H') AS UNSIGNED) AS MACHINE_TIME,
CAST(IFNULL(B.HOUR_USE_TIME, 0) AS UNSIGNED) AS HOUR_USE_TIME,
CAST(IFNULL(D.HOUR_USE_TIME_STOP, 0) AS UNSIGNED) AS HOUR_USE_TIME_STOP,
CAST(IFNULL(C.HOUR_USE_TIME_TOTAL, 0) AS UNSIGNED) AS HOUR_USE_TIME_TOTAL,
(CAST(IFNULL(C.HOUR_USE_TIME_TOTAL, 0) AS UNSIGNED) - CAST(IFNULL(D.HOUR_USE_TIME_STOP, 0) AS UNSIGNED)) AS D,
IFNULL(((CAST(IFNULL(B.HOUR_USE_TIME, 0) AS UNSIGNED) / (CAST(IFNULL(C.HOUR_USE_TIME_TOTAL, 0) AS UNSIGNED)))* 100), 0) AS MACHINE_RATE
FROM PLC_MONITORING AS A
LEFT OUTER JOIN (
SELECT MACHINE_NAME, CAST(DATE_FORMAT(MACHINE_DATETIME, '%H') AS UNSIGNED) AS MACHINE_TIME, CAST(SUM(DATETIME_SPAN) AS UNSIGNED) AS HOUR_USE_TIME
FROM PLC_MONITORING
WHERE MACHINE_STATUS = 'OPERATE' AND MACHINE_DATETIME >= '".$startDate."' AND MACHINE_DATETIME <= '".$finishDate."'
GROUP BY MACHINE_NAME, FLOOR(SUBSTR(DATE_FORMAT(MACHINE_DATETIME, '%H'), 1, 2) / 1)
) AS B ON B.MACHINE_NAME = A.MACHINE_NAME AND B.MACHINE_TIME = DATE_FORMAT(A.MACHINE_DATETIME, '%H')
LEFT OUTER JOIN (
SELECT MACHINE_NAME, CAST(DATE_FORMAT(MACHINE_DATETIME, '%H') AS UNSIGNED) AS MACHINE_TIME, CAST(SUM(DATETIME_SPAN) AS UNSIGNED) AS HOUR_USE_TIME_TOTAL
FROM PLC_MONITORING
WHERE MACHINE_DATETIME >= '".$startDate."' AND MACHINE_DATETIME <= '".$finishDate."'
GROUP BY MACHINE_NAME, FLOOR(SUBSTR(DATE_FORMAT(MACHINE_DATETIME, '%H'), 1, 2) / 1)
) AS C ON C.MACHINE_NAME = A.MACHINE_NAME AND C.MACHINE_TIME = DATE_FORMAT(A.MACHINE_DATETIME, '%H')
LEFT OUTER JOIN (
SELECT MACHINE_NAME, CAST(DATE_FORMAT(MACHINE_DATETIME, '%H') AS UNSIGNED) AS MACHINE_TIME, CAST(SUM(DATETIME_SPAN) AS UNSIGNED) AS HOUR_USE_TIME_STOP
FROM PLC_MONITORING
WHERE MACHINE_STATUS != 'OPERATE' AND MACHINE_DATETIME >= '".$startDate."' AND MACHINE_DATETIME <= '".$finishDate."'
GROUP BY MACHINE_NAME, FLOOR(SUBSTR(DATE_FORMAT(MACHINE_DATETIME, '%H'), 1, 2) / 1)
) AS D ON D.MACHINE_NAME = A.MACHINE_NAME AND D.MACHINE_TIME = DATE_FORMAT(A.MACHINE_DATETIME, '%H')
WHERE A.MACHINE_DATETIME >= '".$startDate."' AND A.MACHINE_DATETIME <= '".$finishDate."'
GROUP BY A.MACHINE_NAME, FLOOR(SUBSTR(DATE_FORMAT(A.MACHINE_DATETIME, '%H'), 1, 2) / 1)
이제 각각의 표로 만들어진 부분은 어느정도 Data가 추출되었습니다. 하지만 가장 처음 보여드린 화면에서 보면은 차트가 있습니다. 이 chart가 말썽인게 각기 다른값을 대입할 수 있지만 서로 항목이 일치해야 각각의 위치에서 잘 보여지기에 또 다시 시작 시간과 종료 시간을 구하게 됩니다.
참 화려한 차트의 그래프가 아름답기(?)까지 합니다만 위에 차트를 쓰기 위해서는 정말 많은 고생을 거쳤습니다.
SELECT MIN(CAST(DATE_FORMAT(MACHINE_DATETIME, '%H') AS UNSIGNED) ) AS MACHINE_TIME_MIN, MAX(CAST(DATE_FORMAT(MACHINE_DATETIME, '%H') AS UNSIGNED) ) AS MACHINE_TIME_MAX
FROM PLC_MONITORING WHERE `MACHINE_DATETIME` >= '".$startDate."' AND `MACHINE_DATETIME` <= '".$finishDate."'
이렇게 천제적인 시간대를 추출하고 나면은 각 항목의 시간대와 맞지 않습니다. 이제 서로간에 맞도록 처리를 해줘야 합니다. 이 부분은 도저히 DB의 힘을 빌려서 처리를 할수가 없어서 PHP로 처리하였지만 사용하시는 언어에 따라서 처리하시면 될것이라고 생각을 합니다.
foreach($machineRowsData as $rows) {
$machineName = (STRING) $rows['MACHINE_NAME'];
$machineTime = (INT) $rows['MACHINE_TIME'];
// 데이터 기준점 통일 작업(차트에 사용을 위해서)
for($i=$machineDataMinMax[0]['MACHINE_TIME_MIN']; $i <= $machineDataMinMax[0]['MACHINE_TIME_MAX']; $i++) {
$keyText = $i.'시';
if( $machineTime == $i) {
$reData[$machineName][$keyText]['MACHINE_RATE'] = number_format($rows['MACHINE_RATE'],2);
} else {
if( empty($reData[$machineName][$keyText]['MACHINE_RATE']) ) {
$reData[$machineName][$keyText]['MACHINE_RATE'] = (INT) 0;
}
}
}
}
내부적으로 프로그램 처리를 거치고 나면은 이제 배열형식으로 아름답게 시간대별로 없는 데이터는 0으로 들어가고 있으면은 각 항목에 값이 들어가게 됩니다.
이런 과정을 거쳐서 차트에 맞춰서 또다시 표현을 해주면은 바로 데이터가 항목에 맞춰서 들어가는 과정을 거치게 됩니다.
3개 항목의 데이터를 도출하기 위해서 DB에서 난관도 봉착하고 모든것을 처리하는 시간도 오래거렸지만 일단 도출에 성공했습니다!!!
물론 누군가 보면은 허접할 수 있지만 데이터를 뽑는 기초를 만들었다는것에 의의를 두기로 하여서 좀 더 멋지게 만들어서 도출 할 수 있는 날까지 정진을 할려고 합니다.
전반적으로 모든것을 보여지지 않지만 DB의 쿼리문 자체는 일단 원본그대로이며, 시간단위지만 단위를 일 단위 월 단위로 변경을 할 수도 있어서 한개만 만들어 주면은 다양하게 쓰일 일이 있을것이라고 생각을 합니다.