The below php script collects get request from a form where users select hardcoded templates along with a date range to query site alarm information.

Simplify and improve the below but while improving the code improve the queries if you see the need, you need to write it into several short sections and not all on one:

= 2';
 $table = 'Events.Alarm';
} else {
 $filtersql\_sev = '';
 $table = 'Historical.AlarmHistory';
}
######handle for Keyword parameters##############################
if (empty($keyword)) {
 #$filtersql\_Keyword = " AND ( AdditionalSummary LIKE '%".$keyword."%' || Summary LIKE '%".$keyword."%')";
 $filtersql\_Keyword = '';
} else {
 #$filtersql\_Keyword = '';
 $filtersql\_Keyword = " AND ( AdditionalSummary LIKE '%" . $keyword . "%' || Summary LIKE '%" . $keyword . "%')";
}
###################################################

#Templates
if ($rep == 'site\_option') {
 $filtersql = "(SiteCode LIKE '%" . $sitecode . "%' or SiteID LIKE '%" . $sitecode . "%')";
}
#########
if ($rep == 'node\_option') {
 $filtersql = "Node like '%" . $node . "%'" . $filtersql\_Keyword;
 if (filter\_var($node, FILTER\_VALIDATE\_IP)) { #Check if value is and IPADDRESS
 $filtersql = "IPAddress = '" . $node . "'" . $filtersql\_Keyword;
 }
}
#########
if ($rep == 'class\_option') {
 $filtersql = "Class = " . $class . $filtersql\_Keyword;
}
#########
if ($rep == 'region\_option') {
 $filtersql = "Region = " . $region . $filtersql\_Keyword;
 ##SETTING CLUSTER REGIONS
 if ($region == 100) {
 $filtersql = "Region > 1 and Region < 20" . $filtersql\_Keyword;
 }
 if ($region == 101) {
 $filtersql = "Region in (21,20,22,37,38,39,40)" . $filtersql\_Keyword;
 }
}

########
if ($rep == 'power\_site\_option') {
 $filtersql = "((Class in (9500,9503,10003,2011) and (
AdditionalSummary LIKE '%GEN%RUN%' OR
AdditionalSummary LIKE '%GEN%SET%' OR
Summary LIKE '%GEN%RUN%' OR
Summary LIKE '%Gen%Run%' OR
CAPERCAFlag = 15 OR
)) AND (SiteCode LIKE '%" . $sitecode . "%' or SiteID LIKE '%" . $sitecode . "%')";
}
########
#Duty Report
if ($rep == 'duty\_option' && $duty == 1) {
 $filtersql = "(OriginalSeverity IN (3,4,5) and Region = 1 )" . $filtersql\_Keyword;
}
if ($rep == 'duty\_option' && $duty == 2) {
 $filtersql = "(OriginalSeverity IN (3,4,5) and Region IN (9,26,25,15,5,6,4,18))" . $filtersql\_Keyword;
}
if ($rep == 'duty\_option' && $duty == 3) {
 $filtersql = "(OriginalSeverity IN (3,4,5) and Region IN (22,3,17,12,16,11,8,19,13,14,7,10) )" . $filtersql\_Keyword;
}
if ($rep == 'duty\_option' && $duty == 4) {
 $filtersql = "(OriginalSeverity IN (3,4,5) and Region IN (21,20,2))" . $filtersql\_Keyword;
}
#############################
echo 'DEBUG information';
echo '  
';
// Validate date and time stamps
$timestamp1 = strtotime($date1);
if (is\_timestamp($timestamp1)) {
 echo 'TIME OK: ' . $timestamp1;
} else {
 echo 'TIME NOT OK';
}
echo '  
';
$timestamp2 = strtotime($date2);
if (is\_timestamp($timestamp2)) {
 echo ' TIME OK: ' . $timestamp2;
} else {
 echo 'TIME NOT OK';
}
function is\_timestamp($timestamp) {
 if (strtotime(date('d-m-Y H:i:s', $timestamp)) === (int)$timestamp) {
 return $timestamp;
 } else return false;
}
###########################
#SET SQL FOR ENGINEER REPORT

# Reuseable Variables
$Region = "case
 when Region = 0 then 'Unclassified'
 when Region = 1 then 'Jamaica'
 when Region = 2 then 'Barbados'
 ";
$OrginalSeverity = "case
 when OriginalSeverity = 0 then 'Clear'
 when OriginalSeverity = 2 then 'Info'
 when OriginalSeverity = 1 then 'Inteterminate'
 when OriginalSeverity = 3 then 'Minor'
 when OriginalSeverity = 4 then 'Major'
 when OriginalSeverity = 5 then 'Critical' ";
$Severity = "case
 when Severity = 0 then 'Clear'
 when Severity = 2 then 'Info'
 when Severity = 1 then 'Inteterminate'
 when Severity = 3 then 'Minor'
 when Severity = 4 then 'Major'
 when Severity = 5 then 'Critical' ";

if ($rep == 'engineer\_option') {
 $engineer\_sql = "select 
$Severity end as Severity ,
$OrginalSeverity end as OriginalSeverity ,
$Region end as Region,
Table1.AlarmId,Acktime,fullname,
Table1.Node,
Table1.SiteName,
Table1.SiteCode,
from\_unixtime(Table1.FirstReported) as FirstReported,
Table1.TicketID,
Table1.Summary,
Table1.AdditionalSummary, 
Table1.ClearedTime,
case 
when ClearedTime > 0 then 
time\_format(sec\_to\_time(timestampdiff(second,from\_unixtime(Table1.FirstReported), Table1.ClearedTime)),'%Hh %im %ss')
when ClearedTime is null then time\_format(sec\_to\_time(timestampdiff(second,from\_unixtime(Table1.FirstReported), now())),'%Hh %im %ss') 
end as Duration,
 case when 
 ClearedTime > 0 then
timestampdiff(second,from\_unixtime(Table1.FirstReported), Table1.ClearedTime)/60
 when ClearedTime is null then 
timestampdiff(second,from\_unixtime(Table1.FirstReported), now())/60
 end as DDuration, Class
from 
$table Table1 
inner join 
(select AlarmId, MAX(StateChange) StateChange 
from $table 
where (FirstReported > " . $timestamp1 . " and FirstReported < " . $timestamp2 . " and OriginalSeverity > 1 )
GROUP BY AlarmId ) Table2 
ON Table1.AlarmId = Table2.AlarmId 
AND Table1.StateChange=Table2.StateChange " . $filtersql\_sev . " inner join Monolith.AuthUsers table3 on OwnerID = table3.userid and UserName = '$engineeid' Order by Table1.FirstReported desc;";
}

#
아래 PHP 스크립트는 사용자가 날짜 범위와 함께 하드코딩된 템플릿을 선택하여 사이트 알람 정보를 쿼리하는 양식에서 get 요청을 수집합니다.아래를 단순화하여 개선하되, 쿼리가 필요하다면 코드를 개선하되 한 번에 모두 작성하지 말고 여러 개의 짧은 섹션으로 나눠서 작성해야 합니다:= 2'; table = 'Events.Alarm';} else { 필터스퀘어\_sev = ''; table = 'Historical.AlarmHistory';}######핸들 키워드 매개변수##############################if (empty($keyword)) { #$filtersql\_Keyword = " AND ( AdditionalSummary LIKE '%".$keyword."%' || Summary LIKE '%".$keyword."%')"; $filtersql\_Keyword = '';} else { #$filtersql\_Keyword = ''; $filtersql\_Keyword = " AND ( AdditionalSummary LIKE '%" . 키워드 . "%' || Summary LIKE '%" . 키워드 . "%')";}####################################################템플릿if ($rep == '사이트\_옵션') { filtersql = "(SiteCode LIKE '%" . $sitecode . "%' 또는 SiteID LIKE '%" . $sitecode . "%')";}#########if ($rep == 'node\_option') { filtersql = "노드 같은 '%' . 노드 . "%'" . $filtersql\_키워드; if (filter\_var($node, FILTER\_VALIDATE\_IP)) { #값이 및 IPADDRESS인지 확인 $filtersql = "IPAddress = '" . 노드 . "'" . 필터스쿼드\_키워드; }}#########if ($rep == 'class\_option') { $filtersql = "Class = " . $class . $filtersql\_Keyword;}#########if ($rep == 'region\_option') { filtersql = "Region = " . $region . 필터스클\_키워드; ##클러스터 지역 설정 if ($region == 100) { $filtersql = "Region > 1 and Region < 20" . 필터스클\_키워드; } if ($region == 101) { $filtersql = "Region in (21,20,22,37,38,39,40)" . 필터스클\_키워드; }}########if ($rep == 'power\_site\_option') { $filtersql = "((9500,9503,10003,2011)의 클래스 및 (AdditionalSummary LIKE '%GEN%RUN%' ORAdditionalSummary LIKE '%GEN%SET%' ORSummary LIKE '%GEN%RUN%' ORSummary LIKE '%Gen%Run%' ORCAPERCAFlag = 15 OR)) AND (SiteCode LIKE '%" . $sitecode . "%' 또는 SiteID LIKE '%" . $sitecode . "%')";}#########듀티 보고서if ($rep == 'duty\_option' && $duty == 1) { $filtersql = "(OriginalSeverity IN (3,4,5) and Region = 1 )" . filtersql\_Keyword;}if ($rep == 'duty\_option' && $duty == 2) { filtersql = "(OriginalSeverity IN (3,4,5) and Region IN (9,26,25,15,5,6,4,18))" . $filtersql\_Keyword;}if ($rep == 'duty\_option' && $duty == 3) { $filtersql = "(OriginalSeverity IN (3,4,5) and Region IN (22,3,17,12,16,11,8,19,13,14,7,10) )" . 필터스퀠\_키워드;}if ($rep == 'duty\_option' && $duty == 4) { $filtersql = "(OriginalSeverity IN (3,4,5) 및 Region IN (21,20,2))" . 필터스퀘어\_키워드;}#############################echo 'DEBUG 정보';echo '  ';// 날짜 및 타임스탬프 유효성 검사timestamp1 = strtotime($date1);if (is\_timestamp($timestamp1)) { echo 'TIME OK: ' . $timestamp1;} else { echo 'TIME NOT OK';}echo '  ';timestamp2 = strtotime($date2);if (is\_timestamp($timestamp2)) { echo ' TIME OK: ' . $timestamp2;} else { echo 'TIME NOT OK';}함수 is\_timestamp($timestamp) { if (strtotime(date('d-m-Y H:i:s', $timestamp)) === (int)$timestamp) { return $timestamp; } else return false;}############################엔지니어 보고서용 SQL 설정# 재사용 가능한 변수Region = "case when Region = 0이면 '분류되지 않음' when Region = 1이면 '자메이카' when Region = 2이면 '바베이도스' ";OriginalSeverity = "case when OriginalSeverity = 0이면 'Clear' when OriginalSeverity = 2이면 '정보' when OriginalSeverity = 1이면 '미정' when OriginalSeverity = 3이면 '경미' OriginalSeverity = 4이면 '중대' 원래 심각도 = 5이면 '심각' ";Severity = "case Severity = 0이면 'Clear' 심각도 = 2이면 '정보' 심각도 = 1이면 '미결정' 심각도 = 3이면 '경미' 심각도 = 4이면 '심각' 심각도 = 5이면 '심각' ";if ($rep == 'engineer\_option') { engineer\_sql = "select 심각도 끝은 심각도 ,OriginalSeverity 끝을 OriginalSeverity로 선택 ,Region 끝을 Region으로 선택합니다,Table1.AlarmId,Acktime,fullname,Table1.Node,Table1.SiteName,Table1.SiteCode,from\_unixtime(Table1.FirstReported)를 FirstReported로 끝냅니다,Table1.TicketID,Table1.Summary,Table1.AdditionalSummary, Table1.ClearedTime,case ClearedTime > 0이면 time\_format(sec\_to\_time(timestampdiff(second,from\_unixtime(Table1.FirstReported), Table1.ClearedTime)),'%Hh %im %ss')ClearedTime이 null이면 time\_format(sec\_to\_time(timestampdiff(second,from\_unixtime(Table1.FirstReported), now())),'%Hh %im %ss') Duration으로 끝냅니다, 경우  ClearedTime > 0 thentimestampdiff(second,from\_unixtime(Table1.FirstReported), Table1.ClearedTime)/60 ClearedTime이 null이면 timestampdiff(second,from\_unixtime(Table1.FirstReported), now())/60 DDuration, Classfrom $table Table1 내부 조인 (select AlarmId, MAX(StateChange) StateChange from $table where (FirstReported > " . $timestamp1 . " 및 FirstReported < " . $timestamp2 . " 및 OriginalSeverity > 1 )GROUP BY AlarmId ) 