CategoriesForPeriod {
    label {To Categories for period}
    query {
        SELECT *
            FROM (
                SELECT  1 AS "level",
                        r.categoryName AS "categoryname",
                        r.categoryName AS "Category",
                        sum(r.hours) AS "Total Hours",
                        sum(r.hours) / (SELECT sum(a.hours) FROM timeentry a WHERE a.day >= :startday and a.day <= :endday) AS "Actual Percentage",
                        sum(r.hours) / 40.0 AS "Theoretical Percentage"
                    FROM timeentry r
                    WHERE r.day >= :startday and r.day <= :endday
                    GROUP BY r.categoryName
                UNION
                SELECT  2 AS "level",
                        max(r.categoryName) AS "categoryname",
                        ' ' AS "Category",
                        '------------' AS "Total Hours",
                        ' ' AS "Actual Percentage",
                        ' ' AS "Theoretical Percentage"
                FROM timeentry r
                WHERE r.day >= :startday and r.day <= :endday
                UNION
                SELECT  3 AS "level",
                        max(r.categoryName) AS "categoryname",
                        ' ' AS "Category",
                        sum(r.hours) AS "Total Hours",
                        ' ' AS "Actual Percentage",
                        ' ' AS "Theoretical Percentage"
                FROM timeentry r
                WHERE r.day >= :startday and r.day <= :endday
            )
            ORDER BY level, categoryName
    }
    outputColumns {
        {Category} {Total Hours} {Actual Percentage} {Theoretical Percentage}
    }
}

CategoriesSubcategoriesForPeriod {
    label {To Subcategories for period}
    query {
        SELECT *
            FROM (
                SELECT  1 AS "level",
                        r.categoryName AS "categoryname",
                        ' ' AS "subcategoryname",
                        r.categoryName AS "Category",
                        ' ' AS "Subcategory",
                        ' ' AS "Total Hours",
                        ' ' AS "Actual Percentage",
                        ' ' AS "Theoretical Percentage",
                        ' ' AS "In Category Percentage"
                    FROM timeentry r
                    WHERE r.day >= :startday and r.day <= :endday
                    GROUP BY r.categoryName
                UNION
                SELECT  2 AS "level",
                        r.categoryName AS "categoryname",
                        r.subcategoryName AS "subcategoryname",
                        ' ' AS "Category",
                        r.subcategoryName AS "Subcategory",
                        sum(r.hours) AS "Total Hours",
                        sum(r.hours) / (SELECT sum(a.hours) FROM timeentry a WHERE a.day >= :startday and a.day <= :endday) AS "Actual Percentage",
                        sum(r.hours) / 40.0 AS "Theoretical Percentage",
                        sum(r.hours) / (SELECT sum(a.hours) FROM timeentry a WHERE a.day >= :startday and a.day <= :endday and a.categoryname = r.categoryname) AS "In Category Percentage"
                    FROM timeentry r
                    WHERE r.day >= :startday and r.day <= :endday
                    GROUP BY r.categoryName, r.subcategoryname
                UNION
                SELECT  3 AS "level",
                        r.categoryName AS "categoryname",
                        ' ' AS "subcategoryname",
                        ' ' AS "Category",
                        '-----------------------' AS "Subcategory",
                        '------------' AS "Total Hours",
                        '-----------------' AS "Actual Percentage",
                        '----------------------' AS "Theoretical Percentage",
                        ' ' AS "In Category Percentage"
                FROM timeentry r
                WHERE r.day >= :startday and r.day <= :endday
                GROUP BY r.categoryName
                UNION
                SELECT  4 AS "level",
                        r.categoryName AS "categoryname",
                        ' ' AS "subcategoryname",
                        ' ' AS "Category",
                        ' ' AS "Subcategory",
                        sum(r.hours) AS "Total Hours",
                        sum(r.hours) / (SELECT sum(a.hours) FROM timeentry a WHERE a.day >= :startday and a.day <= :endday) AS "Actual Percentage",
                        sum(r.hours) / 40.0 AS "Theoretical Percentage",
                        ' ' AS "In Category Percentage"
                FROM timeentry r
                WHERE r.day >= :startday and r.day <= :endday
                GROUP BY r.categoryName
                UNION
                SELECT  5 AS "level",
                        max(r.categoryName) AS "categoryname",
                        ' ' AS "subcategoryname",
                        ' ' AS "Category",
                        ' ' AS "Subcategory",
                        '------------' AS "Total Hours",
                        ' ' AS "Actual Percentage",
                        ' ' AS "Theoretical Percentage",
                        ' ' AS "In Category Percentage"
                FROM timeentry r
                WHERE r.day >= :startday and r.day <= :endday
                UNION
                SELECT  6 AS "level",
                        max(r.categoryName) AS "categoryname",
                        ' ' AS "subcategoryname",
                        ' ' AS "Category",
                        ' ' AS "Subcategory",
                        sum(r.hours) AS "Total Hours",
                        ' ' AS "Actual Percentage",
                        ' ' AS "Theoretical Percentage",
                        ' ' AS "In Category Percentage"
                FROM timeentry r
                WHERE r.day >= :startday and r.day <= :endday
            )
        ORDER BY categoryName, level, subcategoryname
    }
    outputColumns {
        {Category} {Subcategory} {Total Hours} {Actual Percentage} {Theoretical Percentage} {In Category Percentage}
    }
}

DetailsForPeriod {
    label {Details for period}
    query {
        SELECT *
            FROM (
                SELECT  0 AS "level",
                        r.day AS "day",
                        r.day AS "Date",
                        ' ' AS "categoryname",
                        ' ' AS "subcategoryname",
                        ' ' AS "comments",
                        ' ' AS "Category",
                        ' ' AS "Subcategory",
                        ' ' AS "Details",
                        ' ' AS "Total Hours"
                    FROM timeentry r
                    WHERE r.day >= :startday and r.day <= :endday
                    GROUP BY r.categoryName
                UNION
                SELECT  1 AS "level",
                        r.day AS "day",
                        ' ' AS "Date",
                        r.categoryName AS "categoryname",
                        ' ' AS "subcategoryname",
                        ' ' AS "comments",
                        r.categoryName AS "Category",
                        ' ' AS "Subcategory",
                        ' ' AS "Details",
                        ' ' AS "Total Hours"
                    FROM timeentry r
                    WHERE r.day >= :startday and r.day <= :endday
                    GROUP BY r.categoryName
                UNION
                SELECT  2 AS "level",
                        r.day AS "day",
                        ' ' AS "Date",
                        r.categoryName AS "categoryname",
                        r.subcategoryName AS "subcategoryname",
                        ' ' AS "comments",
                        ' ' AS "Category",
                        r.subcategoryName AS "Subcategory",
                        ' ' AS "Details",
                        ' ' AS "Total Hours"
                    FROM timeentry r
                    WHERE r.day >= :startday and r.day <= :endday
                    GROUP BY r.categoryName, r.subcategoryname
                UNION
                SELECT  3 AS "level",
                        r.day AS "day",
                        ' ' AS "Date",
                        r.categoryName AS "categoryname",
                        r.subcategoryName AS "subcategoryname",
                        r.comments AS "comments",
                        ' ' AS "Category",
                        ' ' AS "Subcategory",
                        r.comments AS "Details",
                        sum(hours) AS "Total Hours"
                    FROM timeentry r
                    WHERE r.day >= :startday and r.day <= :endday
                    GROUP BY r.categoryName, r.subcategoryname, r.comments
                UNION
                SELECT  4 AS "level",
                        r.day AS "day",
                        ' ' AS "Date",
                        r.categoryName AS "categoryname",
                        r.subcategoryName AS "subcategoryname",
                        ' ' AS "comments",
                        ' ' AS "Category",
                        ' ' AS "Subcategory",
                        ' ' AS "Details",
                        '------------' AS "Total Hours"
                FROM timeentry r
                WHERE r.day >= :startday and r.day <= :endday
                GROUP BY r.categoryName, r.subcategoryname
                UNION
                SELECT  5 AS "level",
                        r.day AS "day",
                        ' ' AS "Date",
                        r.categoryName AS "categoryname",
                        r.subcategoryName AS "subcategoryname",
                        ' ' AS "comments",
                        ' ' AS "Category",
                        ' ' AS "Subcategory",
                        'Subcategory Total' AS "Details",
                        sum(hours) AS "Total Hours"
                FROM timeentry r
                WHERE r.day >= :startday and r.day <= :endday
                GROUP BY r.categoryName, r.subcategoryname
                UNION
                SELECT  6 AS "level",
                        r.day AS "day",
                        ' ' AS "Date",
                        r.categoryName AS "categoryname",
                        max(r.subcategoryName) AS "subcategoryname",
                        ' ' AS "comments",
                        ' ' AS "Category",
                        ' ' AS "Subcategory",
                        ' ' AS "Details",
                        '------------' AS "Total Hours"
                FROM timeentry r
                WHERE r.day >= :startday and r.day <= :endday
                GROUP BY r.categoryName
                UNION
                SELECT  7 AS "level",
                        r.day AS "day",
                        ' ' AS "Date",
                        r.categoryName AS "categoryname",
                        max(r.subcategoryName) AS "subcategoryname",
                        ' ' AS "comments",
                        ' ' AS "Category",
                        ' ' AS "Subcategory",
                        'Category Total' AS "Details",
                        sum(hours) AS "Total Hours"
                FROM timeentry r
                WHERE r.day >= :startday and r.day <= :endday
                GROUP BY r.categoryName
                UNION
                SELECT  8 AS "level",
                        max(r.day) AS "day",
                        ' ' AS "Date",
                        max(r.categoryName) AS "categoryname",
                        max(r.subcategoryName) AS "subcategoryname",
                        ' ' AS "comments",
                        ' ' AS "Category",
                        ' ' AS "Subcategory",
                        ' ' AS "Details",
                        '------------' AS "Total Hours"
                FROM timeentry r
                WHERE r.day >= :startday and r.day <= :endday
                UNION
                SELECT  9 AS "level",
                        max(r.day) AS "day",
                        ' ' AS "Date",
                        max(r.categoryName) AS "categoryname",
                        max(r.subcategoryName) AS "subcategoryname",
                        ' ' AS "comments",
                        ' ' AS "Category",
                        ' ' AS "Subcategory",
                        'Grand Total' AS "Details",
                        sum(hours) AS "Total Hours"
                FROM timeentry r
                WHERE r.day >= :startday and r.day <= :endday
            )
        ORDER BY day, categoryName, subcategoryname, level, comments
    }
    outputColumns {
        {Category} {Subcategory} {Details} {Total Hours}
    }
}

DailyDetailsForPeriod {
    label {Details by day}
    query {
        SELECT * FROM (
            SELECT  0 AS "level",
                    r.day AS "day",
                    ' ' AS "categoryname",
                    ' ' AS "subcategoryname",
                    ' ' AS "comments",
                    r.day AS "Day",
                    ' ' AS "Category",
                    ' ' AS "Subcategory",
                    ' ' AS "Details",
                    ' ' AS "Total Hours"
                FROM timeentry r
                WHERE r.day >= :startday and r.day <= :endday
                GROUP BY r.day
        UNION
            SELECT  1 AS "level",
                    r.day AS "day",
                    r.categoryname AS "categoryname",
                    ' ' AS "subcategoryname",
                    ' ' AS "comments",
                    ' ' AS "Day",
                    r.categoryname AS "Category",
                    ' ' AS "Subcategory",
                    ' ' AS "Details",
                    ' ' AS "Total Hours"
                FROM timeentry r
                WHERE r.day >= :startday and r.day <= :endday
                GROUP BY r.day, r.categoryname
        UNION
            SELECT  2 AS "level",
                    r.day AS "day",
                    r.categoryname AS "categoryname",
                    r.subcategoryname AS "subcategoryname",
                    ' ' AS "comments",
                    ' ' AS "Day",
                    ' ' AS "Category",
                    r.subcategoryname AS "Subcategory",
                    ' ' AS "Details",
                    ' ' AS "Total Hours"
                FROM timeentry r
                WHERE r.day >= :startday and r.day <= :endday
                GROUP BY r.day, r.categoryname, r.subcategoryname
        UNION
            SELECT  3 AS "level",
                    r.day AS "day",
                    r.categoryName AS "categoryname",
                    r.subcategoryName AS "subcategoryname",
                    r.comments AS "comments",
                    ' ' AS "Category",
                    ' ' AS "Subcategory",
                    ' ' AS "Day",
                    r.comments AS "Details",
                    sum(hours) AS "Total Hours"
                FROM timeentry r
                WHERE r.day >= :startday and r.day <= :endday
                GROUP BY r.day, r.categoryname, r.subcategoryname, r.comments
        UNION
            SELECT  4 AS "level",
                    r.day AS "day",
                    r.categoryName AS "categoryname",
                    r.subcategoryName AS "subcategoryname",
                    ' ' AS "comments",
                    ' ' AS "Day",
                    ' ' AS "Category",
                    ' ' AS "Subcategory",
                    ' ' AS "Details",
                    '------------' AS "Total Hours"
            FROM timeentry r
            WHERE r.day >= :startday and r.day <= :endday
            GROUP BY r.day, r.categoryName, r.subcategoryname
        UNION
            SELECT  5 AS "level",
                    r.day AS "day",
                    r.categoryName AS "categoryname",
                    r.subcategoryName AS "subcategoryname",
                    ' ' AS "comments",
                    ' ' AS "Day",
                    ' ' AS "Category",
                    ' ' AS "Subcategory",
                    'Subcategory Total' AS "Details",
                    sum(hours) AS "Total Hours"
            FROM timeentry r
            WHERE r.day >= :startday and r.day <= :endday
            GROUP BY r.day, r.categoryName, r.subcategoryname
        UNION
            SELECT  6 AS "level",
                    r.day AS "day",
                    r.categoryName AS "categoryname",
                    max(r.subcategoryName) AS "subcategoryname",
                    ' ' AS "comments",
                    ' ' AS "Day",
                    ' ' AS "Category",
                    ' ' AS "Subcategory",
                    ' ' AS "Details",
                    '------------' AS "Total Hours"
            FROM timeentry r
            WHERE r.day >= :startday and r.day <= :endday
            GROUP BY r.day, r.categoryName
        UNION
            SELECT  7 AS "level",
                    r.day AS "day",
                    r.categoryName AS "categoryname",
                    max(r.subcategoryName) AS "subcategoryname",
                    ' ' AS "comments",
                    ' ' AS "Day",
                    ' ' AS "Category",
                    ' ' AS "Subcategory",
                    'Category Total' AS "Details",
                    sum(hours) AS "Total Hours"
            FROM timeentry r
            WHERE r.day >= :startday and r.day <= :endday
            GROUP BY r.day, r.categoryName
        UNION
            SELECT  8 AS "level",
                    r.day AS "day",
                    max(r.categoryName) AS "categoryname",
                    max(r.subcategoryName) AS "subcategoryname",
                    ' ' AS "comments",
                    ' ' AS "Day",
                    ' ' AS "Category",
                    ' ' AS "Subcategory",
                    ' ' AS "Details",
                    '------------' AS "Total Hours"
            FROM timeentry r
            WHERE r.day >= :startday and r.day <= :endday
            GROUP BY r.day
        UNION
            SELECT  9 AS "level",
                    r.day AS "day",
                    max(r.categoryName) AS "categoryname",
                    max(r.subcategoryName) AS "subcategoryname",
                    ' ' AS "comments",
                    ' ' AS "Day",
                    ' ' AS "Category",
                    ' ' AS "Subcategory",
                    'Daily Total' AS "Details",
                    sum(hours) AS "Total Hours"
            FROM timeentry r
            WHERE r.day >= :startday and r.day <= :endday
            GROUP BY r.day
        UNION
            SELECT  10 AS "level",
                    max(r.day) AS "day",
                    max(r.categoryName) AS "categoryname",
                    max(r.subcategoryName) AS "subcategoryname",
                    ' ' AS "comments",
                    ' ' AS "Day",
                    ' ' AS "Category",
                    ' ' AS "Subcategory",
                    ' ' AS "Details",
                    '------------' AS "Total Hours"
            FROM timeentry r
            WHERE r.day >= :startday and r.day <= :endday
        UNION
            SELECT  11 AS "level",
                    max(r.day) AS "day",
                    max(r.categoryName) AS "categoryname",
                    max(r.subcategoryName) AS "subcategoryname",
                    ' ' AS "comments",
                    ' ' AS "Day",
                    ' ' AS "Category",
                    ' ' AS "Subcategory",
                    'Grand Total' AS "Details",
                    sum(hours) AS "Total Hours"
            FROM timeentry r
            WHERE r.day >= :startday and r.day <= :endday
        )
        ORDER BY day, categoryName, subcategoryname, level, comments
    }
    outputColumns {
        {Date} {Category} {Subcategory} {Details} {Total Hours}
    }
}
