labtech Database

Parameters

Name	Type	Mode
SelectedClient	int	IN

Definition

BEGIN
  -- gather scores for device health checks
  DROP TEMPORARY TABLE IF EXISTS t_TargetCheckData;
  CREATE TEMPORARY TABLE t_TargetCheckData
  (SELECT cmp.ClientID, cmp.LocationID, lhr.TargetType, lhr.TargetID, lhr.HealthCheckGUID, hc.CheckCategoryID, hc.CheckOrder, hc.CheckName, lhr.DateGenerated, lhr.HealthScore, 
  lhr.HealthMaxScore, ROUND(lhr.HealthScore/lhr.HealthMaxScore*100, 0) HealthPct, '' GroupData
  FROM plugin_sap_latesthealthresults lhr JOIN plugin_sap_healthchecks hc ON lhr.HealthCheckGUID = hc.HealthCheckGUID
  JOIN computers cmp ON lhr.TargetID = cmp.ComputerID AND lhr.TargetType = 1
  WHERE cmp.ClientID = SelectedClient);

  -- gather scores for device health categories
  DROP TEMPORARY TABLE IF EXISTS t_TargetCategoryData;
  CREATE TEMPORARY TABLE t_TargetCategoryData
  (SELECT hd.ClientID, hd.LocationID, hd.TargetType, hd.TargetID, hd.CheckCategoryID, SUM(hd.HealthScore) HealthScore, SUM(hd.HealthMaxScore) HealthMaxScore, 
  ROUND((SUM(hd.HealthScore) / SUM(hd.HealthMaxScore)) * 100,0) HealthPct
  FROM t_TargetCheckData hd
  GROUP BY hd.ClientID, hd.LocationID, hd.TargetType, hd.TargetID, hd.CheckCategoryID);

  -- reassign Check Order and send results to plugin_sap_ReportCheckResults
  DELETE FROM plugin_sap_ReportCheckResults WHERE TargetType = 3 AND TargetID = SelectedClient;
  DELETE FROM plugin_sap_ReportCheckResults WHERE TargetType = 2 AND TargetID = ANY (SELECT LocationID FROM locations WHERE ClientID = SelectedClient);
  
  -- get the number of Checks to display by default on the report. If there is no value set, default to 5 checks per category.
  SET @ReportCheckDisplayCount = (SELECT IFNULL(sp.Value,5) ReportCheckDisplayCount FROM plugin_sap_properties sp WHERE sp.PropertyName = 'ReportCheckDisplayCount' LIMIT 1);
  SET @ScoringMethod = (SELECT IFNULL(sp.Value,0) ScoringMethod FROM plugin_sap_properties sp WHERE sp.PropertyName = 'HowToWeighScores' LIMIT 1);

  SET @grp = '';
  SET @counter = 1;
INSERT IGNORE INTO plugin_sap_ReportCheckResults
(TargetType, TargetID, HealthCheckGUID, CheckName, CheckCategoryID, CheckOrder, GroupCheckOrder, TargetHealthScore, TargetHealthScoreMax, TargetStraightScore, TargetWeightedScore, ScoringMethod, TargetGroupData)
SELECT temp1.TargetType, temp1.TargetID, temp1.HealthCheckGUID, temp1.CheckName, temp1.CheckCategoryID, temp1.CheckOrder, temp1.GroupCheckOrder, temp1.TargetHealthScore,
temp1.TargetHealthMaxScore, temp1.TargetStraightScore, temp1.TargetWeightedScore, temp1.ScoringMethod, temp1.GroupData FROM
(SELECT 
  3 TargetType, 
  tcd.ClientID TargetID, 
  tcd.HealthCheckGUID, 
  tcd.CheckName, 
  tcd.CheckCategoryID, 
  tcd.CheckOrder, 
  IF(tcd.CheckOrder >= @ReportCheckDisplayCount, @ReportCheckDisplayCount, tcd.CheckOrder) GroupCheckOrder, 
  SUM(tcd.HealthScore) TargetHealthScore, 
  SUM(tcd.HealthMaxScore) TargetHealthMaxScore, 
  ROUND(AVG(tcd.HealthPct),0) TargetStraightScore, 
  ROUND(SUM(tcd.HealthScore) / SUM(tcd.HealthMaxScore) * 100, 0) TargetWeightedScore, 
  @ScoringMethod ScoringMethod,
  @grp := CONCAT(tcd.ClientID, '-', tcd.CheckCategoryID) GroupData
FROM 
  t_TargetCheckData tcd
GROUP BY 
  tcd.ClientID, 
  tcd.HealthCheckGUID
ORDER BY 
  tcd.ClientID, 
  tcd.CheckCategoryID,
  tcd.CheckOrder) AS temp1
ON DUPLICATE KEY UPDATE
  TargetHealthScore = temp1.TargetHealthScore,
  TargetHealthScoreMax = temp1.TargetHealthMaxScore,
  TargetStraightScore =   temp1.TargetStraightScore,
  TargetWeightedScore = temp1.TargetWeightedScore;

  SET @grp = '';
  SET @counter = 1;      
INSERT IGNORE INTO plugin_sap_ReportCheckResults
(TargetType, TargetID, HealthCheckGUID, CheckName, CheckCategoryID, CheckOrder, GroupCheckOrder, TargetHealthScore, TargetHealthScoreMax, TargetStraightScore, TargetWeightedScore, ScoringMethod, TargetGroupData)
SELECT temp1.TargetType, temp1.TargetID, temp1.HealthCheckGUID, temp1.CheckName, temp1.CheckCategoryID, temp1.CheckOrder, temp1.GroupCheckOrder, temp1.TargetHealthScore,
temp1.TargetHealthMaxScore, temp1.TargetStraightScore, temp1.TargetWeightedScore, temp1.ScoringMethod, temp1.GroupData FROM
(SELECT 
  2 TargetType, 
  tcd.LocationID TargetID, 
  tcd.HealthCheckGUID, 
  tcd.CheckName, 
  tcd.CheckCategoryID, 
  tcd.CheckOrder, 
  IF(tcd.CheckOrder >= @ReportCheckDisplayCount, @ReportCheckDisplayCount, tcd.CheckOrder) GroupCheckOrder, 
  SUM(tcd.HealthScore) TargetHealthScore, 
  SUM(tcd.HealthMaxScore) TargetHealthMaxScore, 
  ROUND(AVG(tcd.HealthPct),0) TargetStraightScore, 
  ROUND(SUM(tcd.HealthScore) / SUM(tcd.HealthMaxScore) * 100, 0) TargetWeightedScore, 
  @ScoringMethod ScoringMethod,
  @grp := CONCAT(tcd.LocationID, '-', tcd.CheckCategoryID) GroupData
FROM 
  t_TargetCheckData tcd
GROUP BY 
  tcd.LocationID, 
  tcd.HealthCheckGUID
ORDER BY 
  tcd.LocationID, 
  tcd.CheckCategoryID,
  tcd.CheckOrder) AS temp1
ON DUPLICATE KEY UPDATE
  TargetHealthScore = temp1.TargetHealthScore,
  TargetHealthScoreMax = temp1.TargetHealthMaxScore,
  TargetStraightScore =   temp1.TargetStraightScore,
  TargetWeightedScore = temp1.TargetWeightedScore;

  -- send results to plugin_sap_ReportCategoryResults
  DELETE FROM plugin_sap_ReportCategoryResults WHERE TargetType = 3 AND TargetID = SelectedClient;
  DELETE FROM plugin_sap_ReportCategoryResults WHERE TargetType = 2 AND TargetID = ANY (SELECT LocationID FROM locations WHERE ClientID = SelectedClient);

  INSERT IGNORE INTO plugin_sap_ReportCategoryResults
  (`TargetType`,`TargetID`,`CheckCategoryID`,`TargetHealthScore`,`TargetHealthScoreMax`,`TargetStraightScore`,`TargetWeightedScore`,`ScoringMethod`)
SELECT
temp1.TargetType, temp1.TargetID, temp1.CheckCategoryID, temp1.TargetHealthScore, temp1.TargetHealthMaxScore, temp1.TargetStraightScore, temp1.TargetWeightedScore, temp1.ScoringMethod
FROM
  (SELECT 3 TargetType, tcd.ClientID TargetID, tcd.CheckCategoryID, SUM(tcd.HealthScore) TargetHealthScore, SUM(tcd.HealthMaxScore) TargetHealthMaxScore, 
  ROUND(AVG(tcd.HealthPct),0) TargetStraightScore, ROUND((SUM(tcd.HealthScore)/SUM(tcd.HealthMaxScore))*100,0) TargetWeightedScore, @ScoringMethod ScoringMethod
  FROM t_TargetCategoryData tcd
  GROUP BY tcd.ClientID, tcd.CheckCategoryID) AS temp1 ON DUPLICATE KEY UPDATE
  `TargetHealthScore`=temp1.TargetHealthScore,
  `TargetHealthScoreMax`=temp1.TargetHealthMaxScore,
  `TargetStraightScore`=temp1.TargetStraightScore,
  `TargetWeightedScore`=temp1.TargetWeightedScore;

  INSERT IGNORE INTO plugin_sap_ReportCategoryResults
  (`TargetType`,`TargetID`,`CheckCategoryID`,`TargetHealthScore`,`TargetHealthScoreMax`,`TargetStraightScore`,`TargetWeightedScore`,`ScoringMethod`)
SELECT
temp1.TargetType, temp1.TargetID, temp1.CheckCategoryID, temp1.TargetHealthScore, temp1.TargetHealthMaxScore, temp1.TargetStraightScore, temp1.TargetWeightedScore, temp1.ScoringMethod
FROM
  (SELECT 2 TargetType, tcd.LocationID TargetID, tcd.CheckCategoryID, SUM(tcd.HealthScore) TargetHealthScore, SUM(tcd.HealthMaxScore) TargetHealthMaxScore, 
  ROUND(AVG(tcd.HealthPct),0) TargetStraightScore, ROUND((SUM(tcd.HealthScore)/SUM(tcd.HealthMaxScore))*100,0) TargetWeightedScore, @ScoringMethod ScoringMethod
  FROM t_TargetCategoryData tcd
  GROUP BY tcd.LocationID, tcd.CheckCategoryID) AS temp1 ON DUPLICATE KEY UPDATE
  `TargetHealthScore`=temp1.TargetHealthScore,
  `TargetHealthScoreMax`=temp1.TargetHealthMaxScore,
  `TargetStraightScore`=temp1.TargetStraightScore,
  `TargetWeightedScore`=temp1.TargetWeightedScore;
     
  -- cleanup
  DROP TEMPORARY TABLE t_TargetCategoryData;
  DROP TEMPORARY TABLE t_TargetCheckData;
END

sp_GetSAPReportData

Parameters

Definition