/*
 | Job Scheduler Reporting Interface procedures for PostgreSQL
 | SOS GmbH, 2015-01-19, andreas.pueschel@sos-berlin.com
 | 
 | Intended as sample for customization
 | Works for PostgreSQL version 8.4 or higher
 |
 | Usage
 | 1) SELECT <fields> FROM REPORT_INSTALLED_JOB_OBJECTS('2014-01-01','2016-01-01');
 | 2) SELECT <fields> FROM REPORT_INSTALLED_ORDER_OBJECTS('2014-01-01','2016-01-01');
 | 3) SELECT <fields> FROM REPORT_EXECUTION_SUMMARY('2014-01-01','2016-01-01');
*/
CREATE OR REPLACE FUNCTION REPORT_INSTALLED_JOB_OBJECTS(start_date DATE, end_date DATE) RETURNS TABLE (
                                        "ID"                    BIGINT,
                                        "SCHEDULER_ID"          VARCHAR(100),
                                        "HOSTNAME"              VARCHAR(255),
                                        "JOB_CHAIN"             VARCHAR(255), 
                                        "JOB_CHAIN_BASENAME"    VARCHAR(100),
                                        "JOB_CHAIN_TITLE"       VARCHAR(255),
                                        "JOB_NAME"              VARCHAR(255), 
                                        "JOB_BASENAME"          VARCHAR(100),
                                        "JOB_TITLE"             VARCHAR(255),
                                        "CAUSE"                 VARCHAR(100),
                                        "IS_ACTIVE_CALENDAR"    INT,
                                        "IS_ORDER_JOB"          INT,
                                        "FILE_CREATED"          TIMESTAMP,
                                        "FILE_MODIFIED"         TIMESTAMP
) AS $$ 
BEGIN

  RETURN QUERY
    SELECT 
        row_number() over(ORDER BY ii."SCHEDULER_ID", ii."HOSTNAME", sub."JOB_CHAIN", ij."NAME") AS "ID",
        ii."SCHEDULER_ID",
        ii."HOSTNAME",
        sub."JOB_CHAIN",
        sub."JOB_CHAIN_BASENAME",
        sub."JOB_CHAIN_TITLE",
        ij."NAME"                       AS "JOB_NAME",
        ij."BASENAME"                   AS "JOB_BASENAME",
        ij."TITLE"                      AS "JOB_TITLE",
        rmc."MAPPED_CAUSE"              AS "CAUSE",
        CASE ij."IS_ORDER_JOB" 
                        WHEN 0 THEN CAST(ij."IS_RUNTIME_DEFINED" AS INT) 
                        WHEN 1 THEN CAST(sub."IS_RUNTIME_DEFINED" AS INT) END 
                                        AS "IS_ACTIVE_CALENDAR",
        CAST(ij."IS_ORDER_JOB" AS INT)  AS "IS_ORDER_JOB",
        if."FILE_CREATED",
        if."FILE_MODIFIED"
    FROM INVENTORY_JOBS ij
        JOIN INVENTORY_FILES     if ON ij."FILE_ID" = if."ID"
        JOIN INVENTORY_INSTANCES ii ON ii."ID"      = if."INSTANCE_ID"
        LEFT JOIN 
        (
            SELECT  ijc."NAME"          AS "JOB_CHAIN",
                    ijc."START_CAUSE"   AS "JOB_CHAIN_START_CAUSE",
                    ijc."TITLE"         AS "JOB_CHAIN_TITLE",
                    ijc."BASENAME"      AS "JOB_CHAIN_BASENAME",
                    ijcn."JOB_NAME",
                    ijcn."INSTANCE_ID",
                    io."IS_RUNTIME_DEFINED"
            FROM INVENTORY_JOB_CHAIN_NODES  ijcn
            JOIN INVENTORY_JOB_CHAINS       ijc ON ijc."ID"             = ijcn."JOB_CHAIN_ID"
            JOIN INVENTORY_ORDERS           io  ON io."JOB_CHAIN_NAME"  = ijc."NAME" AND ijc."INSTANCE_ID"=io."INSTANCE_ID"
	    GROUP BY ijc."NAME", ijc."START_CAUSE", ijc."TITLE", ijc."BASENAME", ijcn."JOB_NAME", ijcn."INSTANCE_ID", io."IS_RUNTIME_DEFINED"
       ) AS sub ON  sub."JOB_NAME"  = ij."NAME" AND sub."INSTANCE_ID" = ii."ID"
       LEFT JOIN REPORTING_MAP_CAUSES rmc ON rmc."CAUSE" = sub."JOB_CHAIN_START_CAUSE"
    WHERE if."FILE_CREATED" BETWEEN DATE(start_date) AND DATE(end_date)
    ORDER BY ii."SCHEDULER_ID", ii."HOSTNAME", sub."JOB_CHAIN", ij."NAME";
END;
$$ LANGUAGE plpgsql;

COMMIT;

CREATE OR REPLACE FUNCTION REPORT_INSTALLED_ORDER_OBJECTS(start_date DATE, end_date DATE) RETURNS TABLE (
                                        "ID"                    BIGINT,
                                        "SCHEDULER_ID"          VARCHAR(100),
                                        "HOSTNAME"              VARCHAR(255),
                                        "JOB_CHAIN"             VARCHAR(255), 
                                        "JOB_CHAIN_BASENAME"    VARCHAR(100),
                                        "JOB_CHAIN_TITLE"       VARCHAR(255),
                                        "ORDER_ID"              VARCHAR(255), 
                                        "ORDER_NAME"            VARCHAR(255), 
                                        "ORDER_BASENAME"        VARCHAR(255), 
                                        "ORDER_TITLE"           VARCHAR(255), 
                                        "CAUSE"                 VARCHAR(100),
                                        "IS_ACTIVE_CALENDAR"    INT,
                                        "FILE_CREATED"          TIMESTAMP,
                                        "FILE_MODIFIED"         TIMESTAMP
) AS $$ 
BEGIN

  RETURN QUERY
    SELECT 
	    io."ID",
        ii."SCHEDULER_ID",
        ii."HOSTNAME",
        ijc."NAME"                              AS "JOB_CHAIN",
        ijc."BASENAME"                          AS "JOB_CHAIN_BASENAME",
        ijc."TITLE"                             AS "JOB_CHAIN_TITLE",
		io."ORDER_ID",
		io."NAME"                               AS "ORDER_NAME",
		io."BASENAME"                           AS "ORDER_BASENAME",
		io."TITLE"                              AS "ORDER_TITLE",
        rmc."MAPPED_CAUSE"                      AS "CAUSE",
        CAST(io."IS_RUNTIME_DEFINED" AS INT)    AS "IS_ACTIVE_CALENDAR",
        if."FILE_CREATED",
        if."FILE_MODIFIED"
    FROM INVENTORY_ORDERS io
        JOIN INVENTORY_FILES             if ON io."FILE_ID" = if."ID"
        JOIN INVENTORY_INSTANCES         ii ON ii."ID"      = if."INSTANCE_ID"
        JOIN INVENTORY_JOB_CHAINS       ijc ON ijc."NAME"   = io."JOB_CHAIN_NAME" AND ijc."INSTANCE_ID"=io."INSTANCE_ID"
        LEFT JOIN REPORTING_MAP_CAUSES rmc ON rmc."CAUSE" = ijc."START_CAUSE"
    WHERE if."FILE_CREATED" BETWEEN DATE(start_date) AND DATE(end_date)
    ORDER BY ii."SCHEDULER_ID", ii."HOSTNAME", ijc."NAME", io."NAME";
END;
$$ LANGUAGE plpgsql;

COMMIT;


CREATE OR REPLACE FUNCTION REPORT_EXECUTION_SUMMARY(start_date DATE, end_date DATE) RETURNS TABLE ( 
                                         "ID"                    BIGINT,
                                         "SCHEDULER_ID"          VARCHAR(100),
                                         "HOSTNAME"              VARCHAR(255),
                                         "AGENT_URL"             VARCHAR(100),
                                         "JOB_CHAIN"             VARCHAR(255), 
                                         "JOB_NAME"              VARCHAR(255), 
                                         "JOB_BASENAME"          VARCHAR(100),
                                         "JOB_CHAIN_BASENAME"    VARCHAR(100),
                                         "JOB_TITLE"             VARCHAR(255),
                                         "JOB_CHAIN_TITLE"       VARCHAR(255),
                                         "CAUSE"                 VARCHAR(100),
                                         "IS_ACTIVE_CALENDAR"    INT,
                                         "START_TIME"            TIMESTAMP,
                                         "END_TIME"              TIMESTAMP,
                                         "START_DATE"            DATE,
                                         "END_DATE"              DATE
) AS $$ 
BEGIN

  RETURN QUERY
    SELECT
	    re."ID",
        re."SCHEDULER_ID",
        ii."HOSTNAME",
        re."AGENT_URL",
        rt."PARENT_NAME"           AS "JOB_CHAIN",
        re."NAME"                  AS "JOB_NAME",
        re."BASENAME"              AS "JOB_BASENAME",
        rt."PARENT_BASENAME"       AS "JOB_CHAIN_BASENAME",
        re."TITLE"                 AS "JOB_TITLE",
        rt."PARENT_TITLE"          AS "JOB_CHAIN_TITLE",
        rmc."MAPPED_CAUSE"         AS "CAUSE",
        CASE ij."IS_ORDER_JOB"  WHEN 0 THEN CAST(ij."IS_RUNTIME_DEFINED" AS INT) WHEN 1 THEN CAST(rt."IS_RUNTIME_DEFINED" AS INT) END
                                   AS "IS_ACTIVE_CALENDAR",
        re."START_TIME",
        re."END_TIME",
        DATE(re."START_TIME")      AS "START_DATE",
        DATE(re."END_TIME")        AS "END_DATE"
    FROM REPORTING_EXECUTIONS re
        JOIN REPORTING_TRIGGERS rt ON re."TRIGGER_ID" = rt."ID"
        LEFT JOIN INVENTORY_JOBS       ij ON ij."NAME"   = re."NAME"
        LEFT JOIN INVENTORY_INSTANCES  ii ON ii."ID"     = ij."INSTANCE_ID" AND rt."SCHEDULER_ID" = ii."SCHEDULER_ID"
        LEFT JOIN REPORTING_MAP_CAUSES   rmc ON rmc."CAUSE" = re."CAUSE"
    WHERE re."START_TIME" BETWEEN DATE(start_date) AND DATE(end_date)
    ORDER BY "SCHEDULER_ID", "HOSTNAME", "JOB_CHAIN", "JOB_NAME";

END;
$$ LANGUAGE plpgsql;

COMMIT;