hourshours is_editable is_deletable

gistfile1.txt

CASE
/* LEAVE BRANCH */
WHEN cef.id IS NOT NULL THEN
    CASE
        WHEN cef.start_date IS NULL OR cef.end_date IS NULL
            THEN 'leave_has_no_period'
        WHEN DATEDIFF(DATE(cef.end_date), DATE(cef.start_date)) > 0
            THEN 'period_spans_multiple_days'
        WHEN EXISTS (
            SELECT 1
            FROM itemtype it
            WHERE it.id = cef.itemtype_id
              AND it.is_furlough_accodation = '1'
        )
            THEN 'leave_has_workflow'
        WHEN (
                 (SELECT hs.hours_approval FROM hourssettings hs LIMIT 1) = 1
                     AND (
                     (SELECT hs.approve_from FROM hourssettings hs LIMIT 1) IS NULL
                         OR DATE(cef.start_date) >= (SELECT hs.approve_from FROM hourssettings hs LIMIT 1)
                         OR DATE(cef.end_date)   >= (SELECT hs.approve_from FROM hourssettings hs LIMIT 1)
                     )
                 )
            AND (
                 EXISTS (
                     SELECT 1
                     FROM flat_hours_review_by_week w
                     WHERE w.crmemployee_id = h.crmemployee_id
                       AND w.start_date BETWEEN
                         DATE_SUB(DATE(cef.start_date), INTERVAL WEEKDAY(DATE(cef.start_date)) DAY)
                         AND DATE_ADD(DATE(cef.end_date),   INTERVAL (6 - WEEKDAY(DATE(cef.end_date))) DAY)
                       AND w.hoursstatus_id = 1
                 )
                     OR EXISTS (
                     SELECT 1
                     FROM employeehoursapproval eha
                     WHERE eha.crmemployee_id = h.crmemployee_id
                       AND eha.date BETWEEN DATE(cef.start_date) AND DATE(cef.end_date)
                       AND eha.hoursstatus_id = 1
                 )
                 )
            THEN
            CASE
                WHEN EXISTS (
                    SELECT 1
                    FROM flat_hours_review_by_week w
                    WHERE w.crmemployee_id = h.crmemployee_id
                      AND w.start_date BETWEEN
                        DATE_SUB(DATE(cef.start_date), INTERVAL WEEKDAY(DATE(cef.start_date)) DAY)
                        AND DATE_ADD(DATE(cef.end_date),   INTERVAL (6 - WEEKDAY(DATE(cef.end_date))) DAY)
                      AND w.hoursstatus_id = 1
                )
                    THEN 'period_has_approved_weeks'
                ELSE 'period_has_approved_days'
                END
        WHEN NOT (
            (SELECT hs.hours_approval FROM hourssettings hs LIMIT 1) = 1
                AND (
                (SELECT hs.approve_from FROM hourssettings hs LIMIT 1) IS NULL
                    OR DATE(cef.start_date) >= (SELECT hs.approve_from FROM hourssettings hs LIMIT 1)
                    OR DATE(cef.end_date)   >= (SELECT hs.approve_from FROM hourssettings hs LIMIT 1)
                )
            )
            AND DATE(cef.start_date) < CURDATE()
            THEN 'period_starts_in_past'
        ELSE NULL
        END

/* ABSENCE BRANCH */
WHEN cea.id IS NOT NULL THEN
    CASE
        WHEN cea.start_date IS NULL OR cea.end_date IS NULL
            THEN 'absence_has_no_period'
        WHEN DATEDIFF(DATE(cea.end_date), DATE(cea.start_date)) > 0
            THEN 'period_spans_multiple_days'
        WHEN (
                 (SELECT hs.hours_approval FROM hourssettings hs LIMIT 1) = 1
                     AND (
                     (SELECT hs.approve_from FROM hourssettings hs LIMIT 1) IS NULL
                         OR DATE(cea.start_date) >= (SELECT hs.approve_from FROM hourssettings hs LIMIT 1)
                         OR DATE(cea.end_date)   >= (SELECT hs.approve_from FROM hourssettings hs LIMIT 1)
                     )
                 )
            AND (
                 EXISTS (
                     SELECT 1
                     FROM flat_hours_review_by_week w
                     WHERE w.crmemployee_id = h.crmemployee_id
                       AND w.start_date BETWEEN
                         DATE_SUB(DATE(cea.start_date), INTERVAL WEEKDAY(DATE(cea.start_date)) DAY)
                         AND DATE_ADD(DATE(cea.end_date),   INTERVAL (6 - WEEKDAY(DATE(cea.end_date))) DAY)
                       AND w.hoursstatus_id = 1
                 )
                     OR EXISTS (
                     SELECT 1
                     FROM employeehoursapproval eha
                     WHERE eha.crmemployee_id = h.crmemployee_id
                       AND eha.date BETWEEN DATE(cea.start_date) AND DATE(cea.end_date)
                       AND eha.hoursstatus_id = 1
                 )
                 )
            THEN
            CASE
                WHEN EXISTS (
                    SELECT 1
                    FROM flat_hours_review_by_week w
                    WHERE w.crmemployee_id = h.crmemployee_id
                      AND w.start_date BETWEEN
                        DATE_SUB(DATE(cea.start_date), INTERVAL WEEKDAY(DATE(cea.start_date)) DAY)
                        AND DATE_ADD(DATE(cea.end_date),   INTERVAL (6 - WEEKDAY(DATE(cea.end_date))) DAY)
                      AND w.hoursstatus_id = 1
                )
                    THEN 'period_has_approved_weeks'
                ELSE 'period_has_approved_days'
                END
        WHEN NOT (
            (SELECT hs.hours_approval FROM hourssettings hs LIMIT 1) = 1
                AND (
                (SELECT hs.approve_from FROM hourssettings hs LIMIT 1) IS NULL
                    OR DATE(cea.start_date) >= (SELECT hs.approve_from FROM hourssettings hs LIMIT 1)
                    OR DATE(cea.end_date)   >= (SELECT hs.approve_from FROM hourssettings hs LIMIT 1)
                )
            )
            AND DATE(cea.start_date) < CURDATE()
            THEN 'period_starts_in_past'
        ELSE NULL
        END

/* NORMAL HOURS BRANCH */
ELSE
    CASE
        WHEN ((SELECT hs.hours_approval FROM hourssettings hs LIMIT 1) = 1 AND h.hoursstatus_id = 1)
            THEN 'approved_hours'
        WHEN h.invoicestatus IN ('invoiced','flushed')
            THEN 'invoiced_or_flushed'
        WHEN pro.projectsstatus_id <> 1
            THEN 'project_closed'
        WHEN (h.projectsproject_id < 0 AND itemt.is_furlough_accodation = '1')
            THEN 'internal_requires_workflow'
        WHEN (h.projectsproject_id < 0 AND h.start_date < CONCAT(CURDATE(),' 00:00:00'))
            THEN 'internal_in_past'
        WHEN EXISTS (SELECT 1 FROM invoicesservicehourshours ish WHERE ish.hourshours_id = h.id)
            THEN 'used_in_invoice'
        ELSE NULL
        END
END AS is_editable,
CASE
/* LEAVE BRANCH (delete checks: has period + manage rule) */
WHEN cef.id IS NOT NULL THEN
    CASE
        WHEN cef.start_date IS NULL OR cef.end_date IS NULL
            THEN 'leave_has_no_period'
        WHEN (
                 (SELECT hs.hours_approval FROM hourssettings hs LIMIT 1) = 1
                     AND (
                     (SELECT hs.approve_from FROM hourssettings hs LIMIT 1) IS NULL
                         OR DATE(cef.start_date) >= (SELECT hs.approve_from FROM hourssettings hs LIMIT 1)
                         OR DATE(cef.end_date)   >= (SELECT hs.approve_from FROM hourssettings hs LIMIT 1)
                     )
                 )
            AND (
                 EXISTS (
                     SELECT 1
                     FROM flat_hours_review_by_week w
                     WHERE w.crmemployee_id = h.crmemployee_id
                       AND w.start_date BETWEEN
                         DATE_SUB(DATE(cef.start_date), INTERVAL WEEKDAY(DATE(cef.start_date)) DAY)
                         AND DATE_ADD(DATE(cef.end_date),   INTERVAL (6 - WEEKDAY(DATE(cef.end_date))) DAY)
                       AND w.hoursstatus_id = 1
                 )
                     OR EXISTS (
                     SELECT 1
                     FROM employeehoursapproval eha
                     WHERE eha.crmemployee_id = h.crmemployee_id
                       AND eha.date BETWEEN DATE(cef.start_date) AND DATE(cef.end_date)
                       AND eha.hoursstatus_id = 1
                 )
                 )
            THEN
            CASE
                WHEN EXISTS (
                    SELECT 1
                    FROM flat_hours_review_by_week w
                    WHERE w.crmemployee_id = h.crmemployee_id
                      AND w.start_date BETWEEN
                        DATE_SUB(DATE(cef.start_date), INTERVAL WEEKDAY(DATE(cef.start_date)) DAY)
                        AND DATE_ADD(DATE(cef.end_date),   INTERVAL (6 - WEEKDAY(DATE(cef.end_date))) DAY)
                      AND w.hoursstatus_id = 1
                )
                    THEN 'period_has_approved_weeks'
                ELSE 'period_has_approved_days'
                END
        WHEN NOT (
            (SELECT hs.hours_approval FROM hourssettings hs LIMIT 1) = 1
                AND (
                (SELECT hs.approve_from FROM hourssettings hs LIMIT 1) IS NULL
                    OR DATE(cef.start_date) >= (SELECT hs.approve_from FROM hourssettings hs LIMIT 1)
                    OR DATE(cef.end_date)   >= (SELECT hs.approve_from FROM hourssettings hs LIMIT 1)
                )
            )
            AND DATE(cef.start_date) < CURDATE()
            THEN 'period_starts_in_past'
        ELSE NULL
        END

/* ABSENCE BRANCH (delete checks: manage rule only) */
WHEN cea.id IS NOT NULL THEN
    CASE
        WHEN (
                 (SELECT hs.hours_approval FROM hourssettings hs LIMIT 1) = 1
                     AND (
                     (SELECT hs.approve_from FROM hourssettings hs LIMIT 1) IS NULL
                         OR DATE(cea.start_date) >= (SELECT hs.approve_from FROM hourssettings hs LIMIT 1)
                         OR DATE(cea.end_date)   >= (SELECT hs.approve_from FROM hourssettings hs LIMIT 1)
                     )
                 )
            AND (
                 EXISTS (
                     SELECT 1
                     FROM flat_hours_review_by_week w
                     WHERE w.crmemployee_id = h.crmemployee_id
                       AND w.start_date BETWEEN
                         DATE_SUB(DATE(cea.start_date), INTERVAL WEEKDAY(DATE(cea.start_date)) DAY)
                         AND DATE_ADD(DATE(cea.end_date),   INTERVAL (6 - WEEKDAY(DATE(cea.end_date))) DAY)
                       AND w.hoursstatus_id = 1
                 )
                     OR EXISTS (
                     SELECT 1
                     FROM employeehoursapproval eha
                     WHERE eha.crmemployee_id = h.crmemployee_id
                       AND eha.date BETWEEN DATE(cea.start_date) AND DATE(cea.end_date)
                       AND eha.hoursstatus_id = 1
                 )
                 )
            THEN
            CASE
                WHEN EXISTS (
                    SELECT 1
                    FROM flat_hours_review_by_week w
                    WHERE w.crmemployee_id = h.crmemployee_id
                      AND w.start_date BETWEEN
                        DATE_SUB(DATE(cea.start_date), INTERVAL WEEKDAY(DATE(cea.start_date)) DAY)
                        AND DATE_ADD(DATE(cea.end_date),   INTERVAL (6 - WEEKDAY(DATE(cea.end_date))) DAY)
                      AND w.hoursstatus_id = 1
                )
                    THEN 'period_has_approved_weeks'
                ELSE 'period_has_approved_days'
                END
        WHEN NOT (
            (SELECT hs.hours_approval FROM hourssettings hs LIMIT 1) = 1
                AND (
                (SELECT hs.approve_from FROM hourssettings hs LIMIT 1) IS NULL
                    OR DATE(cea.start_date) >= (SELECT hs.approve_from FROM hourssettings hs LIMIT 1)
                    OR DATE(cea.end_date)   >= (SELECT hs.approve_from FROM hourssettings hs LIMIT 1)
                )
            )
            AND DATE(cea.start_date) < CURDATE()
            THEN 'period_starts_in_past'
        ELSE NULL
        END

/* NORMAL HOURS BRANCH (same “locked” reasons) */
ELSE
    CASE
        WHEN ((SELECT hs.hours_approval FROM hourssettings hs LIMIT 1) = 1 AND h.hoursstatus_id = 1)
            THEN 'approved_hours'
        WHEN h.invoicestatus IN ('invoiced','flushed')
            THEN 'invoiced_or_flushed'
        WHEN pro.projectsstatus_id <> 1
            THEN 'project_closed'
        WHEN (h.projectsproject_id < 0 AND itemt.is_furlough_accodation = '1')
            THEN 'internal_requires_workflow'
        WHEN (h.projectsproject_id < 0 AND h.start_date < CONCAT(CURDATE(),' 00:00:00'))
            THEN 'internal_in_past'
        WHEN EXISTS (SELECT 1 FROM invoicesservicehourshours ish WHERE ish.hourshours_id = h.id)
            THEN 'used_in_invoice'
        ELSE NULL
        END
END AS is_deletable