CREATE OR REPLACE VIEW boxes_send_to_qc_view
    AS
    SELECT trunc(max_created, 'HH') AS created,
        COUNT (*) AS num_boxes_send_to_qc,
        qcreason
    FROM (
        SELECT boxid,
            MAX(created) AS max_created,
            qcreason
        FROM qcdwhitem
            WHERE
                qcreason IS NOT NULL AND
                qcreason <> 'OUT_OF_STOCK'
                AND ISUSEDFORQCDASHBOARDSTATISTIC = 0
        GROUP BY
            boxid,
            qcreason
        )
    GROUP BY
        trunc(max_created, 'HH'),
        qcreason
    ORDER BY trunc(max_created, 'HH') ASC;

CREATE OR REPLACE VIEW boxes_produced_view
    AS
    SELECT trunc(min_created, 'HH') AS created,
        COUNT (*) AS num_boxes_produced
    FROM (
        SELECT boxid,
            MIN(created) AS min_created
        FROM qcdwhitem
        WHERE
            isboxproduced = '1'
            AND ISUSEDFORQCDASHBOARDSTATISTIC = 0
        GROUP BY
            boxid
        )
    GROUP BY
        trunc(min_created, 'HH')
    ORDER BY trunc(min_created, 'HH') ASC;

CREATE OR REPLACE VIEW boxes_checked_view
    AS
    SELECT COUNT(*) AS num_boxes_checked,
        timechecked AS created
    FROM (
        SELECT DISTINCT boxid,
            trunc(created, 'HH') AS timechecked
        FROM stationload
        WHERE
            ROLE = 'QC'
            AND tuaction = 'TU_LEFT'
            AND ISUSEDFORQCDASHBOARDSTATISTIC = 0
        )
    GROUP BY
        timechecked
    ORDER BY timechecked ASC;

CREATE OR REPLACE VIEW boxes_with_errors_view
    AS
    SELECT COUNT(*) AS num_boxes_with_errors,
        timest AS created,
        qcreason
    FROM (
        SELECT DISTINCT boxid,
            qcreason,
            trunc(created, 'HH') AS timest
        FROM qcoperationdwhitem
        WHERE
            newdeliveredquantity <> requestedquantity
            AND ISUSEDFORQCDASHBOARDSTATISTIC = 0
        )
    GROUP BY
        timest,
        qcreason
    ORDER BY timest ASC

CREATE OR REPLACE VIEW qc_dashboard_statistics_view
    AS
    SELECT TO_DATE(TO_CHAR(dr.the_hour,'DD/MM/YYYY HH24:MI:SS'), 'DD/MM/YYYY HH24:MI:SS') AS CREATED,
        dr.qcreason AS QCREASON,
        NVL(to_qc.num_boxes_send_to_qc,0) AS NUMBOXESSENTTOQC,
        NVL(produced.num_boxes_produced,0) AS NUMBOXESPRODUCED,
        NVL(checked.num_boxes_checked,0) AS NUMBOXESCHECKED,
        NVL(with_errors.num_boxes_with_errors,0) AS NUMBOXESWITHERRORS
    FROM date_range_view dr
        LEFT OUTER JOIN boxes_send_to_qc_view to_qc
            ON to_qc.created = dr.the_hour
            AND to_qc.qcreason = dr.qcreason
        LEFT OUTER JOIN boxes_produced_view produced
            ON produced.created = dr.the_hour
        LEFT OUTER JOIN boxes_checked_view checked
            ON checked.created = dr.the_hour
        LEFT OUTER JOIN boxes_with_errors_view with_errors
            ON with_errors.created = dr.the_hour
            AND with_errors.qcreason = dr.qcreason
    ORDER BY dr.the_hour DESC;

   CREATE OR REPLACE PROCEDURE update_qc_dashboard
     AS
   BEGIN
         MERGE INTO QCDASHBOARDSTATISTICS s
              USING qc_dashboard_statistics_view v
              ON (v.qcreason = s.QCREASON AND v.created=s.CREATED)
              WHEN MATCHED THEN UPDATE
                  SET s.NUMBOXESSENTTOQC = s.NUMBOXESSENTTOQC + v.NUMBOXESSENTTOQC,
                  s.NUMBOXESPRODUCED = s.NUMBOXESPRODUCED + v.NUMBOXESPRODUCED,
                  s.NUMBOXESCHECKED = s.NUMBOXESCHECKED + v.NUMBOXESCHECKED,
                  s.NUMBOXESWITHERRORS = s.NUMBOXESWITHERRORS + v.NUMBOXESWITHERRORS,
                  s.BOXCHECKRATE = ROUND(NVL(s.NUMBOXESSENTTOQC / NULLIF(s.NUMBOXESPRODUCED,0),0) * 100),
                  s.CHECKEDBOXESERRORRATE = ROUND(NVL(s.NUMBOXESWITHERRORS / NULLIF(s.NUMBOXESCHECKED,0),0) * 100),
                  s.INTERNALERRORRATE = ROUND(NVL(s.NUMBOXESWITHERRORS / NULLIF(s.NUMBOXESPRODUCED,0),0) * 100)
              WHEN NOT MATCHED THEN INSERT(CREATED, QCREASON, NUMBOXESSENTTOQC, NUMBOXESPRODUCED, NUMBOXESCHECKED, BOXCHECKRATE, NUMBOXESWITHERRORS, CHECKEDBOXESERRORRATE, INTERNALERRORRATE)
              VALUES(v.CREATED, v.QCREASON, v.NUMBOXESSENTTOQC, v.NUMBOXESPRODUCED, v.NUMBOXESCHECKED, ROUND(NVL(v.NUMBOXESSENTTOQC / NULLIF( v.NUMBOXESPRODUCED,0),0) * 100), v.NUMBOXESWITHERRORS, ROUND(NVL(v.NUMBOXESWITHERRORS / NULLIF(v.NUMBOXESCHECKED,0),0) * 100), ROUND(NVL(v.NUMBOXESWITHERRORS / NULLIF(v.NUMBOXESPRODUCED,0),0) * 100));
         UPDATE STATIONLOAD SET ISUSEDFORQCDASHBOARDSTATISTIC = 1;
         UPDATE QCDWHITEM SET ISUSEDFORQCDASHBOARDSTATISTIC = 1;
         UPDATE QCOPERATIONDWHITEM SET ISUSEDFORQCDASHBOARDSTATISTIC = 1;
   END update_qc_dashboard;