1
BEGIN
2
DECLARE EXIT HANDLER FOR SQLEXCEPTION
3
BEGIN
4
ROLLBACK;
5
DROP TABLE IF EXISTS HotfixPatchSummaryDataTemp, HotfixPatchSummaryDataOld;
6
RESIGNAL;
7
END;
8
START TRANSACTION;
9
DROP TABLE IF EXISTS HotfixPatchSummaryDataTemp, HotfixPatchSummaryDataOld;
10
CREATE TABLE IF NOT EXISTS HotfixPatchSummaryDataTemp LIKE HotfixPatchSummaryData;
11
INSERT INTO HotfixPatchSummaryDataTemp
12
(HotfixId,OS,PatchesStatCurrentSuccess,PatchesStatCurrentFailed,PatchesStatCurrentUnattempted,PatchesCurrentCompliance,PatchApprovalCount,Last_Updated)
13
( SELECT
14
Hotfix.HotfixId, Hotfix.OS,
15
SUM(IF(HotfixData.IsThirdParty = 0, Installed, IF(Hotfix.Version = '', IF(Hotfix.InstalledVersion >= ProductVersionControl.LatestVersion, 1, 0), IF(Hotfix.InstalledVersion = Hotfix.Version, 1, 0)))) AS PatchesStatCurrentSuccess,
16
SUM(IF(HotfixData.IsThirdParty = 0, IF(Installed = 1, 0, ABS(Failed)), IF(Hotfix.Version = '', IF(Hotfix.InstalledVersion >= ProductVersionControl.LatestVersion, 0, ABS(Failed)), IF(Hotfix.InstalledVersion = Hotfix.Version, 0, ABS(Failed))))) AS PatchesStatCurrentFailed,
17
SUM(IF(HotfixData.IsThirdParty = 0, IF(Installed = 0 AND Failed = 0, 1, 0), IF(Hotfix.Version = '', IF(Hotfix.InstalledVersion < ProductVersionControl.LatestVersion AND Failed = 0, 1, 0), IF(Hotfix.InstalledVersion != Hotfix.Version AND Failed = 0, 1, 0)))) AS PatchesStatCurrentUnattempted,
18
IFNULL(ROUND(SUM(IF(HotfixData.isThirdParty = 0, Installed, IF(Hotfix.Version = '', IF(Hotfix.InstalledVersion >= ProductVersionControl.LatestVersion, 1, 0), IF(Hotfix.InstalledVersion = Hotfix.Version, 1, 0)))) / COUNT(Approved) * 100, 2), 0) AS PatchesCurrentCompliance,
19
(SELECT COUNT(ApprovalPolicyId) FROM PatchApprovalSettings pas WHERE Approval > 0 AND pas.HotfixID = Hotfix.HotfixID AND IF(HotfixData.IsThirdParty = 0, pas.OS = Hotfix.OS, pas.OS = '')) AS PatchApprovalCount,
20
Hotfix.Last_Date AS Last_Updated
21
FROM Hotfix
22
LEFT JOIN ProductVersionControl ON HotFix.HotfixId = ProductVersionControl.LTProductKey
23
JOIN HotfixData ON Hotfix.HotfixId = HotfixData.LTProductKey AND
24
(Hotfix.OS = HotfixData.OS OR (HotfixData.IsThirdParty = 1 AND
25
IF(Hotfix.Version = '', HotfixData.Version = ProductVersionControl.LatestVersion, Hotfix.Version = HotfixData.Version)))
26
WHERE Last_Date > (SELECT IFNULL(MAX(Last_Updated), STR_TO_DATE('0000-00-00 00:00:00', '%Y-%m-%d %T')) FROM HotfixPatchSummaryData) AND
27
Approved = 2 AND (HotfixData.IsThirdParty = 0 OR Hotfix.Action = 1 OR Hotfix.InstalledVersion > '')
28
GROUP BY HotfixData.LTProductKey, IF(HotfixData.IsThirdParty = 0, HotfixData.OS, '')
29
)
30
ON DUPLICATE KEY UPDATE
31
PatchesStatCurrentSuccess = VALUES(PatchesStatCurrentSuccess),
32
PatchesStatCurrentFailed = VALUES(PatchesStatCurrentFailed),
33
PatchesStatCurrentUnattempted = VALUES(PatchesStatCurrentUnattempted),
34
PatchesCurrentCompliance = VALUES(PatchesCurrentCompliance),
35
PatchApprovalCount = VALUES(PatchApprovalCount),
36
Last_Updated = VALUES(Last_Updated);
37
RENAME TABLE HotfixPatchSummaryData TO HotfixPatchSummaryDataOld, HotfixPatchSummaryDataTemp TO HotfixPatchSummaryData;
38
DROP TABLE HotfixPatchSummaryDataOld;
39
COMMIT;
40
END