
INSERT INTO pr_exclude_value
(
  pr_exclude_value_no     /* 除外データ値NO */
  ,pr_exclude_no          /* 除外データNO */
  ,pr_exclude_item_no     /* 除外データ項目No */
  ,pr_exclude_item_value  /* 除外データ項目値 */
)
SELECT
  NULL AS pr_exclude_value_no     /* 除外データ値NO */
  ,it.pr_exclude_no                  /* 除外データNO */
  ,it.item_no pr_exclude_item_no  /* 除外データ項目No */
  ,IFNULL(it.value, 0) pr_exclude_item_value /* 除外データ項目値 */
FROM (
  SELECT 
    it.pr_exclude_item_no item_no
    ,dv.pr_exclude_no pr_exclude_no
    ,dv.value value
  FROM pr_exclude_item it /* PR除外データ項目 */
    ,(
      SELECT 
        cd.pr_exclude_no
        ,SUM(dv.pr_data_value) value

      FROM pr_data_value dv      /* PR基礎データ値 */

      INNER JOIN pr_data_item di /* PR基礎データ項目 */
        ON di.pr_data_item_no = dv.pr_data_item_no /* PR基礎データ項目No */
        AND di.server_no = :server_no11
        AND di.pr_data_item_id = 'ac_energy_total' /* PR基礎データ項目ID : AC電力量 */

      INNER JOIN pr_exclude_condition cd
        ON cd.server_no = di.server_no
        AND (
          :pr_exclude_no11 IS NULL 
          OR
          (:pr_exclude_no12 IS NOT NULL 
            AND cd.pr_exclude_no = :pr_exclude_no13) )
        AND cd.pr_exclude_start >= :start1
        AND cd.pr_exclude_end < :end1
        -- AND cd.pr_exclude_reason_cd = reason_cd1

      WHERE dv.pr_data_ts >= cd.pr_exclude_start /* PR基礎データ値取得日時 datetime */
        AND dv.pr_data_ts < cd.pr_exclude_end + INTERVAL 1 MINUTE

      GROUP BY pr_exclude_no
    ) dv
  WHERE it.server_no = :server_no12
    AND it.pr_exclude_item_id = 'ac_energy' /* AC電力量 */

  UNION ALL

  SELECT 
    it.pr_exclude_item_no item_no
    ,dv.pr_exclude_no pr_exclude_no
    ,dv.value value
  FROM pr_exclude_item it /* PR除外データ項目 */
    ,(
      SELECT 
        dv.pr_exclude_no
        ,MAX(dv.value) value
      FROM (
        SELECT 
          cd.pr_exclude_no
          ,dv.pr_data_ts
          ,(
            SELECT AVG(dv2.pr_data_value) value
            FROM pr_data_value dv2      /* PR基礎データ値 */
            WHERE dv2.pr_data_item_no = di.pr_data_item_no
              AND dv2.pr_data_ts <= dv.pr_data_ts
              AND dv2.pr_data_ts > (dv.pr_data_ts - INTERVAL 11 MINUTE)
          ) value

        FROM pr_data_value dv      /* PR基礎データ値 */

        INNER JOIN pr_data_item di /* PR基礎データ項目 */
          ON di.pr_data_item_no = dv.pr_data_item_no /* PR基礎データ項目No */
          AND di.server_no = :server_no21
          AND di.pr_data_item_id = 'wind_speed' /* PR基礎データ項目ID : 風速 */

        INNER JOIN pr_exclude_condition cd
          ON cd.server_no = di.server_no
          AND (
            :pr_exclude_no21 IS NULL 
            OR
            (:pr_exclude_no22 IS NOT NULL 
              AND cd.pr_exclude_no = :pr_exclude_no23) )
          AND cd.pr_exclude_start >= :start2
          AND cd.pr_exclude_end < :end2
          -- AND cd.pr_exclude_reason_cd = reason_cd2

        WHERE dv.pr_data_ts >= cd.pr_exclude_start /* PR基礎データ値取得日時 datetime */
          AND dv.pr_data_ts < cd.pr_exclude_end + INTERVAL 1 MINUTE
      ) dv
      GROUP BY dv.pr_exclude_no
    ) dv
  WHERE it.server_no = :server_no22
    AND it.pr_exclude_item_id = 'wind_max' /* 最大風速 */

  UNION ALL

  SELECT 
    it.pr_exclude_item_no item_no
    ,dv.pr_exclude_no pr_exclude_no
    ,dv.value value
  FROM pr_exclude_item it /* PR除外データ項目 */
    ,(
      SELECT 
        dv.pr_exclude_no
        ,MIN(dv.value) value
      FROM (
        SELECT 
          cd.pr_exclude_no
          ,dv.pr_data_ts
          ,(
            SELECT AVG(dv2.pr_data_value) value
            FROM pr_data_value dv2      /* PR基礎データ値 */
            WHERE dv2.pr_data_item_no = di.pr_data_item_no
              AND dv2.pr_data_ts <= dv.pr_data_ts
              AND dv2.pr_data_ts > (dv.pr_data_ts - INTERVAL 11 MINUTE)
          ) value

        FROM pr_data_value dv      /* PR基礎データ値 */

        INNER JOIN pr_data_item di /* PR基礎データ項目 */
          ON di.pr_data_item_no = dv.pr_data_item_no /* PR基礎データ項目No */
          AND di.server_no = :server_no31
          AND di.pr_data_item_id = 'wind_speed' /* PR基礎データ項目ID : 風速 */

        INNER JOIN pr_exclude_condition cd
          ON cd.server_no = di.server_no
          AND (
            :pr_exclude_no31 IS NULL 
            OR
            (:pr_exclude_no32 IS NOT NULL 
              AND cd.pr_exclude_no = :pr_exclude_no33) )
          AND cd.pr_exclude_start >= :start3
          AND cd.pr_exclude_end < :end3
          -- AND cd.pr_exclude_reason_cd = reason_cd3

        WHERE dv.pr_data_ts >= cd.pr_exclude_start /* PR基礎データ値取得日時 datetime */
          AND dv.pr_data_ts < cd.pr_exclude_end + INTERVAL 1 MINUTE
      ) dv
      GROUP BY dv.pr_exclude_no
    ) dv
  WHERE it.server_no = :server_no32
    AND it.pr_exclude_item_id = 'wind_min' /* 最小風速 */

  UNION ALL

  SELECT 
    it.pr_exclude_item_no item_no
    ,dv.pr_exclude_no pr_exclude_no
    ,dv.value value
  FROM pr_exclude_item it /* PR除外データ項目 */
    ,(
      SELECT 
        cd.pr_exclude_no
        ,MAX(dv.pr_data_value) value

      FROM pr_data_value dv      /* PR基礎データ値 */

      INNER JOIN pr_data_item di /* PR基礎データ項目 */
        ON di.pr_data_item_no = dv.pr_data_item_no /* PR基礎データ項目No */
        AND di.server_no = :server_no41
        AND di.pr_data_item_id = 'pcs_output_min' /* PR基礎データ項目ID : 最低PCS出力 ※最低PCS出力の遷移の中で最大を検出する */

      INNER JOIN pr_exclude_condition cd
        ON cd.server_no = di.server_no
        AND (
          :pr_exclude_no41 IS NULL 
          OR
          (:pr_exclude_no42 IS NOT NULL 
            AND cd.pr_exclude_no = :pr_exclude_no43) )
        AND cd.pr_exclude_start >= :start4
        AND cd.pr_exclude_end < :end4
        -- AND cd.pr_exclude_reason_cd = reason_cd4

      WHERE dv.pr_data_ts >= cd.pr_exclude_start /* PR基礎データ値取得日時 datetime */
        AND dv.pr_data_ts < cd.pr_exclude_end + INTERVAL 1 MINUTE

      GROUP BY cd.pr_exclude_no
    ) dv
  WHERE it.server_no = :server_no42
    AND it.pr_exclude_item_id = 'pcs_max' /* 最低PCS出力最大 */

  UNION ALL

  SELECT 
    it.pr_exclude_item_no item_no
    ,dv.pr_exclude_no pr_exclude_no
    ,dv.value value
  FROM pr_exclude_item it /* PR除外データ項目 */
    ,(
      SELECT 
        cd.pr_exclude_no
        ,MIN(dv.pr_data_value) value

      FROM pr_data_value dv      /* PR基礎データ値 */

      INNER JOIN pr_data_item di /* PR基礎データ項目 */
        ON di.pr_data_item_no = dv.pr_data_item_no /* PR基礎データ項目No */
        AND di.server_no = :server_no51
        AND di.pr_data_item_id = 'pcs_output_min' /* PR基礎データ項目ID : 最低PCS出力 */

      INNER JOIN pr_exclude_condition cd
        ON cd.server_no = di.server_no
        AND (
          :pr_exclude_no51 IS NULL 
          OR
          (:pr_exclude_no52 IS NOT NULL 
            AND cd.pr_exclude_no = :pr_exclude_no53) )
        AND cd.pr_exclude_start >= :start5
        AND cd.pr_exclude_end < :end5
        -- AND cd.pr_exclude_reason_cd = reason_cd5

      WHERE dv.pr_data_ts >= cd.pr_exclude_start /* PR基礎データ値取得日時 datetime */
        AND dv.pr_data_ts < cd.pr_exclude_end + INTERVAL 1 MINUTE

      GROUP BY cd.pr_exclude_no
    ) dv
  WHERE it.server_no = :server_no52
    AND it.pr_exclude_item_id = 'pcs_min' /* 最低PCS出力最小 */

  UNION ALL

  SELECT 
    it.pr_exclude_item_no item_no
    ,dv.pr_exclude_no pr_exclude_no
    ,dv.value value
  FROM pr_exclude_item it /* PR除外データ項目 */
    ,(
      SELECT 
        cd.pr_exclude_no
        ,MAX(dv.pr_data_value) value

      FROM pr_data_value dv      /* PR基礎データ値 */

      INNER JOIN pr_data_item di /* PR基礎データ項目 */
        ON di.pr_data_item_no = dv.pr_data_item_no /* PR基礎データ項目No */
        AND di.server_no = :server_no61
        AND di.pr_data_item_id = 'grid_volt' /* PR基礎データ項目ID : 系統電圧 */

      INNER JOIN pr_exclude_condition cd
        ON cd.server_no = di.server_no
        AND (
          :pr_exclude_no61 IS NULL 
          OR
          (:pr_exclude_no62 IS NOT NULL 
            AND cd.pr_exclude_no = :pr_exclude_no63) )
        AND cd.pr_exclude_start >= :start6
        AND cd.pr_exclude_end < :end6
        -- AND cd.pr_exclude_reason_cd = reason_cd6

      WHERE dv.pr_data_ts >= cd.pr_exclude_start /* PR基礎データ値取得日時 datetime */
        AND dv.pr_data_ts < cd.pr_exclude_end + INTERVAL 1 MINUTE

      GROUP BY cd.pr_exclude_no
    ) dv
  WHERE it.server_no = :server_no62
    AND it.pr_exclude_item_id = 'volt_max' /* 系統電圧最大 */

  UNION ALL

  SELECT 
    it.pr_exclude_item_no item_no
    ,dv.pr_exclude_no pr_exclude_no
    ,dv.value value
  FROM pr_exclude_item it /* PR除外データ項目 */
    ,(
      SELECT 
        cd.pr_exclude_no
        ,MIN(dv.pr_data_value) value

      FROM pr_data_value dv      /* PR基礎データ値 */

      INNER JOIN pr_data_item di /* PR基礎データ項目 */
        ON di.pr_data_item_no = dv.pr_data_item_no /* PR基礎データ項目No */
        AND di.server_no = :server_no71
        AND di.pr_data_item_id = 'grid_volt' /* PR基礎データ項目ID : 系統電圧 */

      INNER JOIN pr_exclude_condition cd
        ON cd.server_no = di.server_no
        AND (
          :pr_exclude_no71 IS NULL 
          OR
          (:pr_exclude_no72 IS NOT NULL 
            AND cd.pr_exclude_no = :pr_exclude_no73) )
        AND cd.pr_exclude_start >= :start7
        AND cd.pr_exclude_end < :end7
        -- AND cd.pr_exclude_reason_cd = reason_cd7

      WHERE dv.pr_data_ts >= cd.pr_exclude_start /* PR基礎データ値取得日時 datetime */
        AND dv.pr_data_ts < cd.pr_exclude_end + INTERVAL 1 MINUTE

      GROUP BY cd.pr_exclude_no
    ) dv
  WHERE it.server_no = :server_no72
    AND it.pr_exclude_item_id = 'volt_min' /* 系統電圧最小 */

) it
ON DUPLICATE KEY UPDATE 
  pr_exclude_no = it.pr_exclude_no /* 除外データNO */
  ,pr_exclude_item_no = it.item_no  /* 除外データ項目No */
  ,pr_exclude_item_value = IFNULL(it.value, 0)  /* 除外データ項目値 */
