Symfony Profiler

SELECT
  DISTINCT cd.*,
  (
    CASE WHEN cd.object_ref_table = 'users' THEN (
      SELECT
        CONCAT(u.firstname, ' ', u.lastname)
      FROM
        gfc_dm_users u
      WHERE
        u.id_user = cd.object_ref_uid
    ) WHEN cd.object_ref_table = 'institution' THEN (
      SELECT
        i.company
      FROM
        gfc_dm_institution i
      WHERE
        i.id_institution = cd.object_ref_uid
    ) WHEN cd.object_ref_table = 'location' THEN (
      SELECT
        i.company
      FROM
        gfc_dm_location i
      WHERE
        i.id_location = cd.object_ref_uid
    ) WHEN cd.object_ref_table = 'partner' THEN (
      SELECT
        p.company
      FROM
        gfc_dm_partner p
      WHERE
        p.id_partner = cd.object_ref_uid
    ) WHEN cd.object_ref_table = 'projects' THEN (
      SELECT
        pr.title
      FROM
        gfc_dm_projects pr
      WHERE
        pr.uid = cd.object_ref_uid
    ) ELSE 'Mandant nicht gefunden' END
  ) AS company,
  (
    CASE WHEN cd.ref_table = 'users' THEN (
      SELECT
        u.color
      FROM
        gfc_dm_users u
      WHERE
        u.id_user = cd.ref_uid
    ) ELSE 'rgb(239, 239, 239)' END
  ) AS color,
  CONCAT(
    cd.object_ref_table, '###', cd.object_ref_uid
  ) AS object_reference_key,
  cdmm2.accepted as eventAccepted
FROM
  gfc_dm_calendar_dates cd
  LEFT JOIN gfc_dm_calendar_dates_mm cdmm ON cd.id_date = cdmm.id_date
  LEFT JOIN gfc_dm_calendar_dates_mm cdmm2 ON cd.id_date = cdmm2.id_date
  AND cdmm2.ref_table = 'users'
  AND cdmm2.ref_uid = 44
WHERE
  cd.deleted = 0
  AND (
    cd.ref_table = 'users'
    AND cd.ref_uid IN (44)
    OR (
      cdmm.ref_table = 'users'
      AND cdmm.ref_uid IN (44)
    )
  )
  AND (
    (
      cd.recur = 0
      AND cd.start >= '2024-12-23 00:00:00'
      AND cd.start <= '2024-12-28 23:59:59'
    )
    OR (
      cd.recur = 1
      AND (
        cd.rcfrom >= 1734908400
        OR cd.rcuntil >= 1734908400
      )
    )
  )

SELECT DISTINCT cd.*,
                ( CASE
                        WHEN cd.object_ref_table = 'users' THEN ( SELECT CONCAT(u.firstname, ' ', u.lastname ) FROM gfc_dm_users u WHERE u.id_user = cd.object_ref_uid   ) 
                        WHEN cd.object_ref_table = 'institution' THEN ( SELECT i.company FROM gfc_dm_institution i WHERE i.id_institution = cd.object_ref_uid  ) 
                        WHEN cd.object_ref_table = 'location' THEN ( SELECT i.company FROM gfc_dm_location i WHERE i.id_location = cd.object_ref_uid  ) 
                        WHEN cd.object_ref_table = 'partner' THEN (SELECT p.company FROM gfc_dm_partner p WHERE p.id_partner = cd.object_ref_uid  ) 
                        WHEN cd.object_ref_table = 'projects' THEN (SELECT pr.title FROM gfc_dm_projects pr WHERE pr.uid = cd.object_ref_uid  ) 
                        ELSE 'Mandant nicht gefunden' 
                    END ) AS company,
                    ( CASE
                        WHEN cd.ref_table = 'users' THEN ( SELECT u.color FROM gfc_dm_users u WHERE u.id_user = cd.ref_uid   ) 
                        ELSE 'rgb(239, 239, 239)'
                    END ) AS color,
                    CONCAT(cd.object_ref_table, '###', cd.object_ref_uid) AS object_reference_key, cdmm2.accepted as eventAccepted
                FROM gfc_dm_calendar_dates cd
                LEFT JOIN gfc_dm_calendar_dates_mm cdmm ON cd.id_date = cdmm.id_date
                LEFT JOIN gfc_dm_calendar_dates_mm cdmm2 ON cd.id_date = cdmm2.id_date AND cdmm2.ref_table = 'users' AND cdmm2.ref_uid = 44 
                WHERE cd.deleted = 0 AND ( cd.ref_table = 'users' AND cd.ref_uid IN (44) OR ( cdmm.ref_table = 'users' AND cdmm.ref_uid IN (44) ) )
                AND ( (cd.recur = 0 AND cd.start >= '2024-12-23 00:00:00' AND cd.start <= '2024-12-28 23:59:59') 
                OR (cd.recur = 1 AND ( cd.rcfrom >= 1734908400 OR cd.rcuntil >= 1734908400 ))
                )
                ;

SELECT
  'project_tasks' AS task_table,
  'uid' AS task_uid_type,
  t.*,
  UNIX_TIMESTAMP(t.date_add) AS date_add_ts,
  UNIX_TIMESTAMP(t.start_date) AS start_date_ts,
  UNIX_TIMESTAMP(t.end_date) AS end_date_ts,
  UNIX_TIMESTAMP(t.due_by) AS due_by_ts,
  tmm.*,
  p.title AS p_title,
  p.description AS p_descriptin,
  (
    CASE WHEN t.ref_table = 'project_phases_steps' THEN (
      SELECT
        title
      FROM
        gfc_dm_project_phases_steps pps
      WHERE
        t.ref_uid = pps.uid
    ) ELSE 'Keine Zuordnung' END
  ) AS p_step,
  t.uid AS uid,
  COUNT(DISTINCT id_file) AS file_count,
  COUNT(DISTINCT ptm.id_message) AS comment_count
FROM
  gfc_dm_project_tasks_mm tmm
  JOIN gfc_dm_project_tasks t ON tmm.ref_table = 'project_tasks'
  AND tmm.ref_uid = t.uid
  JOIN gfc_dm_projects p ON t.id_project = p.uid
  LEFT JOIN gfc_dm_files f ON t.uid = f.ref_uid
  AND f.ref_table = 'project_tasks'
  LEFT JOIN gfc_dm_project_tasks_message ptm ON t.uid = ptm.id_task
WHERE
  t.deleted = 0
  AND (
    (
      UNIX_TIMESTAMP(t.due_by) >= '1734908400'
      AND UNIX_TIMESTAMP(t.due_by) <= '1735426799'
    )
    OR (
      UNIX_TIMESTAMP(t.start_date) >= '1734908400'
      AND UNIX_TIMESTAMP(t.start_date) <= '1735426799'
    )
    OR (
      UNIX_TIMESTAMP(t.end_date) >= '1734908400'
      AND UNIX_TIMESTAMP(t.end_date) <= '1735426799'
    )
    OR (
      UNIX_TIMESTAMP(t.start_date) <= '1734908400'
      AND UNIX_TIMESTAMP(t.end_date) >= '1735426799'
    )
  )
  AND tmm.ref_user_table = 'users'
  AND tmm.id_user IN (44)
GROUP BY
  t.uid
ORDER BY
  t.date_add DESC

SELECT 'project_tasks' AS task_table, 'uid' AS task_uid_type, t.*, UNIX_TIMESTAMP(t.date_add) AS date_add_ts, UNIX_TIMESTAMP(t.start_date) AS start_date_ts, UNIX_TIMESTAMP(t.end_date) AS end_date_ts, UNIX_TIMESTAMP(t.due_by) AS due_by_ts,  tmm.*, p.title AS p_title, p.description AS p_descriptin,
                                        ( CASE
                                            WHEN t.ref_table = 'project_phases_steps' THEN ( SELECT title FROM gfc_dm_project_phases_steps pps WHERE t.ref_uid = pps.uid  ) 
                                            ELSE 'Keine Zuordnung' 
                                        END ) AS p_step,
                                        t.uid AS uid, COUNT(DISTINCT id_file) AS file_count, COUNT(DISTINCT ptm.id_message) AS comment_count
                                    FROM gfc_dm_project_tasks_mm tmm
                                    JOIN gfc_dm_project_tasks t ON tmm.ref_table = 'project_tasks' AND tmm.ref_uid = t.uid
                                    JOIN gfc_dm_projects p ON t.id_project = p.uid
                                    LEFT JOIN gfc_dm_files f ON t.uid = f.ref_uid AND f.ref_table = 'project_tasks'
                                    LEFT JOIN gfc_dm_project_tasks_message ptm ON t.uid = ptm.id_task
                                    WHERE t.deleted = 0 
                                      AND ( 
                                            ( UNIX_TIMESTAMP(t.due_by)  >= '1734908400' AND UNIX_TIMESTAMP(t.due_by)  <= '1735426799')
                                            OR (UNIX_TIMESTAMP(t.start_date)  >= '1734908400' AND UNIX_TIMESTAMP(t.start_date)  <= '1735426799')
                                            OR (UNIX_TIMESTAMP(t.end_date)  >= '1734908400' AND UNIX_TIMESTAMP(t.end_date)  <= '1735426799')
                                            OR (UNIX_TIMESTAMP(t.start_date)  <= '1734908400' AND UNIX_TIMESTAMP(t.end_date)  >= '1735426799') 
                                        )
                                     
                                    AND tmm.ref_user_table = 'users' AND tmm.id_user IN (44)   GROUP BY t.uid ORDER BY t.date_add DESC
                                    ;

SELECT
  *
FROM
  gfc_dm_options
WHERE
  option_name = :option

SELECT * FROM gfc_dm_options WHERE option_name='sys_config';

SELECT
  'sys_process' AS task_table,
  p.title AS p_title,
  sp.title AS sp_title,
  ps.*,
  UNIX_TIMESTAMP(ps.due_by) AS due_by_ts,
  UNIX_TIMESTAMP(ps.start_date) AS start_date_ts,
  UNIX_TIMESTAMP(ps.end_date) AS end_date_ts,
  UNIX_TIMESTAMP(ps.date_add) AS date_add_ts,
  UNIX_TIMESTAMP(ps.date_upd) AS date_upd_ts,
  pmm.ref_table,
  pmm.ref_uid,
  pmm.relation_type,
  COUNT(DISTINCT f.id_file) AS file_count,
  COUNT(DISTINCT ptm.id_message) AS comment_count,
  MAX(ptm.date_add) AS tm_last_change,
  MAX(f.date_add) AS f_last_change
FROM
  gfc_dm_process_steps_mm pmm
  JOIN gfc_dm_process_steps ps ON pmm.id_process_step = ps.uid
  AND ps.deleted = 0
  JOIN gfc_dm_process p ON ps.id_process = p.uid
  AND p.process_status = 'public'
  AND p.deleted = 0
  JOIN gfc_dm_sys_process sp ON p.id_sys_process = sp.uid
  LEFT JOIN gfc_dm_files f ON ps.uid = f.ref_uid
  AND f.ref_table = 'process_steps'
  LEFT JOIN gfc_dm_process_steps_message ptm ON ps.uid = ptm.id_task
WHERE
  (
    (
      UNIX_TIMESTAMP(ps.due_by) >= '1734908400'
      AND UNIX_TIMESTAMP(ps.due_by) <= '1735426799'
    )
    OR (
      UNIX_TIMESTAMP(ps.start_date) >= '1734908400'
      AND UNIX_TIMESTAMP(ps.start_date) <= '1735426799'
    )
    OR (
      UNIX_TIMESTAMP(ps.end_date) >= '1734908400'
      AND UNIX_TIMESTAMP(ps.end_date) <= '1735426799'
    )
    OR (
      UNIX_TIMESTAMP(ps.start_date) <= '1734908400'
      AND UNIX_TIMESTAMP(ps.end_date) >= '1735426799'
    )
  )
  AND pmm.ref_table = 'users'
  AND pmm.ref_uid = 44
  AND ps.status NOT IN (0, 1, 5)
GROUP BY
  ps.uid

SELECT 'sys_process' AS task_table, p.title AS p_title, sp.title AS sp_title, ps.*, UNIX_TIMESTAMP(ps.due_by) AS due_by_ts, UNIX_TIMESTAMP(ps.start_date) AS start_date_ts, UNIX_TIMESTAMP(ps.end_date) AS end_date_ts, UNIX_TIMESTAMP(ps.date_add) AS date_add_ts, UNIX_TIMESTAMP(ps.date_upd) AS date_upd_ts, pmm.ref_table, pmm.ref_uid, pmm.relation_type,
                                            COUNT(DISTINCT f.id_file) AS file_count, COUNT(DISTINCT ptm.id_message) AS comment_count, MAX(ptm.date_add) AS tm_last_change, MAX(f.date_add) AS f_last_change
                                            FROM gfc_dm_process_steps_mm pmm
                                            JOIN gfc_dm_process_steps ps ON pmm.id_process_step = ps.uid  AND ps.deleted = 0
                                            JOIN gfc_dm_process p ON ps.id_process = p.uid AND p.process_status = 'public' AND p.deleted = 0
                                            JOIN gfc_dm_sys_process sp ON p.id_sys_process = sp.uid 
                                            LEFT JOIN gfc_dm_files f ON ps.uid = f.ref_uid AND f.ref_table = 'process_steps'
                                            LEFT JOIN gfc_dm_process_steps_message ptm ON ps.uid = ptm.id_task
                                            WHERE  ( 
                                            ( UNIX_TIMESTAMP(ps.due_by)  >= '1734908400' AND UNIX_TIMESTAMP(ps.due_by)  <= '1735426799')
                                            OR (UNIX_TIMESTAMP(ps.start_date)  >= '1734908400' AND UNIX_TIMESTAMP(ps.start_date)  <= '1735426799')
                                            OR (UNIX_TIMESTAMP(ps.end_date)  >= '1734908400' AND UNIX_TIMESTAMP(ps.end_date)  <= '1735426799')
                                            OR (UNIX_TIMESTAMP(ps.start_date)  <= '1734908400' AND UNIX_TIMESTAMP(ps.end_date)  >= '1735426799') 
                                        ) AND
                                                  pmm.ref_table = 'users' AND pmm.ref_uid = 44 AND ps.status NOT IN (0,1,5) GROUP BY ps.uid  ;

SELECT
  CONCAT(t.ref_table, t.ref_uid) AS identifier,
  t.*,
  UNIX_TIMESTAMP(t.due_by) AS due_by_ts,
  UNIX_TIMESTAMP(t.start_date) AS start_date_ts,
  UNIX_TIMESTAMP(t.end_date) AS end_date_ts,
  (
    CASE WHEN t.ref_table = 'location' THEN (
      SELECT
        CONCAT(
          l.company, '#-#', l.id_institution,
          '#-#', l.id_location, '#-#', l.color
        )
      FROM
        gfc_dm_location l
      WHERE
        l.id_location = t.ref_uid
    ) WHEN t.ref_table = 'institution' THEN (
      SELECT
        CONCAT(
          i.company, '#-#', i.id_institution,
          '#-#0#-#', i.color
        )
      FROM
        gfc_dm_institution i
      WHERE
        i.id_institution = t.ref_uid
    ) WHEN t.ref_table = 'partner' THEN (
      SELECT
        CONCAT(
          p.company, '#-#0#-#0#-#', p.color
        )
      FROM
        gfc_dm_partner p
      WHERE
        p.id_partner = t.ref_uid
    ) ELSE 'Meine Aufgaben#-#0#-#0#-##000' END
  ) AS task_info
FROM
  gfc_dm_tasks t
  LEFT JOIN gfc_dm_tasks_mm tmm ON tmm.ref_table = 'tasks'
  AND tmm.ref_uid = t.id_task
WHERE
  t.deleted = 0
  AND (
    (
      UNIX_TIMESTAMP(t.due_by) >= '1734908400'
      AND UNIX_TIMESTAMP(t.due_by) <= '1735426799'
    )
    OR (
      UNIX_TIMESTAMP(t.start_date) >= '1734908400'
      AND UNIX_TIMESTAMP(t.start_date) <= '1735426799'
    )
    OR (
      UNIX_TIMESTAMP(t.end_date) >= '1734908400'
      AND UNIX_TIMESTAMP(t.end_date) <= '1735426799'
    )
    OR (
      UNIX_TIMESTAMP(t.start_date) <= '1734908400'
      AND UNIX_TIMESTAMP(t.end_date) >= '1735426799'
    )
  )
  AND (
    (
      tmm.ref_user_table = 'users'
      AND tmm.id_user IN (44)
    )
    OR t.id_user IN (44)
  )
GROUP BY
  t.id_task
ORDER BY
  t.date_add DESC

SELECT CONCAT(t.ref_table, t.ref_uid) AS identifier, t.*, UNIX_TIMESTAMP(t.due_by) AS due_by_ts, UNIX_TIMESTAMP(t.start_date) AS start_date_ts, UNIX_TIMESTAMP(t.end_date) AS end_date_ts,
       
                                                ( CASE
                                                    WHEN t.ref_table = 'location' THEN ( SELECT CONCAT(l.company, '#-#', l.id_institution, '#-#', l.id_location, '#-#', l.color) FROM gfc_dm_location l WHERE l.id_location = t.ref_uid   ) 
                                                    WHEN t.ref_table = 'institution' THEN ( SELECT CONCAT(i.company, '#-#', i.id_institution, '#-#0#-#', i.color) FROM gfc_dm_institution i WHERE i.id_institution = t.ref_uid  ) 
                                                    WHEN t.ref_table = 'partner' THEN (SELECT CONCAT(p.company, '#-#0#-#0#-#', p.color) FROM gfc_dm_partner p WHERE p.id_partner = t.ref_uid  ) 
                                                    ELSE 'Meine Aufgaben#-#0#-#0#-##000' 
                                                END ) AS task_info
                                        FROM gfc_dm_tasks t
                                        LEFT JOIN  gfc_dm_tasks_mm tmm  ON tmm.ref_table = 'tasks' AND tmm.ref_uid = t.id_task
                                        WHERE t.deleted = 0 
                                        AND ( 
                                            ( UNIX_TIMESTAMP(t.due_by)  >= '1734908400' AND UNIX_TIMESTAMP(t.due_by)  <= '1735426799')
                                            OR (UNIX_TIMESTAMP(t.start_date)  >= '1734908400' AND UNIX_TIMESTAMP(t.start_date)  <= '1735426799')
                                            OR (UNIX_TIMESTAMP(t.end_date)  >= '1734908400' AND UNIX_TIMESTAMP(t.end_date)  <= '1735426799')
                                            OR (UNIX_TIMESTAMP(t.start_date)  <= '1734908400' AND UNIX_TIMESTAMP(t.end_date)  >= '1735426799') 
                                        )
                                        AND ( ( tmm.ref_user_table = 'users' AND tmm.id_user IN (44) )
                                            OR t.id_user IN (44)  )
                                        
                                        GROUP BY t.id_task ORDER BY t.date_add DESC               
                                        ;

SELECT
  DISTINCT li.uid AS location_inventory_id,
  limm.*,
  UNIX_TIMESTAMP(li.planning_goal_date) AS planning_goal_date_ts,
  UNIX_TIMESTAMP(li.execution_start_date) AS execution_start_date_ts,
  UNIX_TIMESTAMP(li.execution_end_date) AS execution_end_date_ts,
  li.*,
  l.company
FROM
  gfc_dm_location_inventory_mm limm
  JOIN gfc_dm_location_inventory li ON limm.id_location = li.id_location
  JOIN gfc_dm_location l ON limm.id_location = l.id_location
WHERE
  limm.ref_table = 'users'
  AND limm.ref_uid IN (44)
  AND (
    (
      li.execution_start_date >= '2024-12-23 00:00:00'
      AND li.execution_start_date <= '2024-12-28 23:59:59'
    )
    OR (
      li.execution_end_date >= '2024-12-23 00:00:00'
      AND li.execution_end_date <= '2024-12-28 23:59:59'
    )
    OR (
      li.execution_start_date <= '2024-12-23 00:00:00'
      AND li.execution_end_date >= '2024-12-28 23:59:59'
    )
  )

SELECT DISTINCT li.uid AS location_inventory_id, limm.*, UNIX_TIMESTAMP(li.planning_goal_date) AS planning_goal_date_ts, UNIX_TIMESTAMP(li.execution_start_date) AS execution_start_date_ts, UNIX_TIMESTAMP(li.execution_end_date) AS execution_end_date_ts, li.*, l.company
                                            FROM gfc_dm_location_inventory_mm limm
                                            JOIN gfc_dm_location_inventory li ON limm.id_location = li.id_location 
                                            JOIN gfc_dm_location l ON limm.id_location = l.id_location 
                                            WHERE limm.ref_table = 'users' AND limm.ref_uid IN (44) 
                                            AND (
                                            (li.execution_start_date  >= '2024-12-23 00:00:00' AND li.execution_start_date  <= '2024-12-28 23:59:59')
                                            OR (li.execution_end_date  >= '2024-12-23 00:00:00' AND li.execution_end_date  <= '2024-12-28 23:59:59')
                                            OR (li.execution_start_date  <= '2024-12-23 00:00:00' AND li.execution_end_date  >= '2024-12-28 23:59:59')
                                            )
                                            ;

SELECT
  *
FROM
  gfc_dm_users
WHERE
  deleted = 0
  AND id_user IN(44)

SELECT *
                    FROM gfc_dm_users
                    WHERE deleted = 0  AND id_user IN(44);

SELECT
  DISTINCT t.*,
  (
    CASE WHEN t.ref_table = 'institution' THEN (
      SELECT
        i.company
      FROM
        gfc_dm_institution i
      WHERE
        i.id_institution = t.ref_uid
    ) WHEN t.ref_table = 'location' THEN (
      SELECT
        l.company
      FROM
        gfc_dm_location l
      WHERE
        l.id_location = t.ref_uid
    ) WHEN t.ref_table = 'partner' THEN (
      SELECT
        p.company
      FROM
        gfc_dm_partner p
      WHERE
        p.id_partner = t.ref_uid
    ) ELSE '' END
  ) AS company
FROM
  gfc_dm_tickets t
  JOIN gfc_dm_tickets_mm tmm ON t.id_ticket = tmm.id_ticket
  AND tmm.ref_table = 'users'
  AND tmm.ref_uid IN (44)
WHERE
  (
    t.due_by >= '2024-12-23 00:00:00'
    AND t.due_by <= '2024-12-28 23:59:59'
  )

SELECT DISTINCT t.*,
                                        ( CASE
                                            WHEN t.ref_table = 'institution' THEN ( SELECT i.company FROM gfc_dm_institution i WHERE i.id_institution = t.ref_uid  ) 
                                            WHEN t.ref_table = 'location' THEN (SELECT l.company FROM gfc_dm_location l WHERE l.id_location = t.ref_uid  ) 
                                            WHEN t.ref_table = 'partner' THEN (SELECT p.company FROM gfc_dm_partner p WHERE p.id_partner = t.ref_uid  ) 
                                            ELSE '' 
                                        END ) AS company
                                        FROM gfc_dm_tickets t 
                                        JOIN gfc_dm_tickets_mm tmm ON t.id_ticket = tmm.id_ticket AND tmm.ref_table = 'users' AND tmm.ref_uid IN (44) 
                                        WHERE (t.due_by  >= '2024-12-23 00:00:00'
                                        AND t.due_by  <= '2024-12-28 23:59:59')
                                        ;

SELECT
  DISTINCT tep.*,
  t.id_ticket,
  t.status,
  t.priority,
  t.subject,
  UNIX_TIMESTAMP(on_site_date_end) AS on_site_date_end_ts,
  (
    CASE WHEN t.ref_table = 'institution' THEN (
      SELECT
        i.company
      FROM
        gfc_dm_institution i
      WHERE
        i.id_institution = t.ref_uid
    ) WHEN t.ref_table = 'location' THEN (
      SELECT
        l.company
      FROM
        gfc_dm_location l
      WHERE
        l.id_location = t.ref_uid
    ) WHEN t.ref_table = 'partner' THEN (
      SELECT
        p.company
      FROM
        gfc_dm_partner p
      WHERE
        p.id_partner = t.ref_uid
    ) ELSE '' END
  ) AS company
FROM
  gfc_dm_tickets t
  JOIN gfc_dm_tickets_mm tmm ON t.id_ticket = tmm.id_ticket
  AND tmm.ref_table = 'users'
  AND tmm.ref_uid IN (44)
  JOIN gfc_dm_tickets_execution_periods tep ON t.id_ticket = tep.id_ticket
WHERE
  (
    tep.execution_start >= '2024-12-23 00:00:00'
    AND tep.execution_start <= '2024-12-28 23:59:59'
  )
  OR (
    tep.execution_end >= '2024-12-23 00:00:00'
    AND tep.execution_end <= '2024-12-28 23:59:59'
  )
  OR (
    tep.execution_start <= '2024-12-23 00:00:00'
    AND tep.execution_end >= '2024-12-28 23:59:59'
  )
  OR (
    tep.on_site_date >= '2024-12-23 00:00:00'
    AND tep.on_site_date <= '2024-12-28 23:59:59'
  )

SELECT DISTINCT tep.*, t.id_ticket, t.status, t.priority, t.subject, UNIX_TIMESTAMP(on_site_date_end) AS on_site_date_end_ts,
                                        ( CASE
                                            WHEN t.ref_table = 'institution' THEN ( SELECT i.company FROM gfc_dm_institution i WHERE i.id_institution = t.ref_uid  ) 
                                            WHEN t.ref_table = 'location' THEN (SELECT l.company FROM gfc_dm_location l WHERE l.id_location = t.ref_uid  ) 
                                            WHEN t.ref_table = 'partner' THEN (SELECT p.company FROM gfc_dm_partner p WHERE p.id_partner = t.ref_uid  ) 
                                            ELSE '' 
                                        END ) AS company
                                        FROM gfc_dm_tickets t 
                                        JOIN gfc_dm_tickets_mm tmm ON t.id_ticket = tmm.id_ticket AND tmm.ref_table = 'users' AND tmm.ref_uid IN (44) 
                                        JOIN gfc_dm_tickets_execution_periods tep ON t.id_ticket = tep.id_ticket 
                                        WHERE (tep.execution_start  >= '2024-12-23 00:00:00' AND tep.execution_start  <= '2024-12-28 23:59:59')
                                        OR (tep.execution_end  >= '2024-12-23 00:00:00' AND tep.execution_end  <= '2024-12-28 23:59:59')
                                        OR (tep.execution_start  <= '2024-12-23 00:00:00' AND tep.execution_end  >= '2024-12-28 23:59:59')
                                        OR (tep.on_site_date  >= '2024-12-23 00:00:00' AND tep.on_site_date  <= '2024-12-28 23:59:59')
                                        ;

SELECT
  ud.*,
  ud.endday as end,
  ud.startday as start,
  UNIX_TIMESTAMP(ud.startday) AS startday_ts,
  UNIX_TIMESTAMP(ud.endday) AS endday_ts,
  YEAR(ud.startday) AS year,
  u.gender,
  u.academic_title,
  u.firstname,
  u.lastname,
  u.color
FROM
  gfc_dm_users_holidays ud
  JOIN gfc_dm_users u ON ud.id_user = u.id_user
WHERE
  ud.id_user IN (44)
  AND (
    (
      ud.startday >= '2024-12-23 00:00:00'
      AND ud.startday <= '2024-12-28 23:59:59'
    )
    OR (
      ud.endday >= '2024-12-23 00:00:00'
      AND ud.endday <= '2024-12-28 23:59:59'
    )
    OR (
      ud.startday <= '2024-12-23 00:00:00'
      AND ud.endday >= '2024-12-28 23:59:59'
    )
  )
ORDER BY
  ud.startday

SELECT ud.*, ud.endday as end, ud.startday as start, UNIX_TIMESTAMP(ud.startday) AS startday_ts, UNIX_TIMESTAMP(ud.endday) AS endday_ts, YEAR(ud.startday) AS year, u.gender, u.academic_title, u.firstname, u.lastname, u.color
                                            FROM gfc_dm_users_holidays ud
                                            JOIN  gfc_dm_users u ON ud.id_user = u.id_user
                                            WHERE ud.id_user IN (44)
                                            AND ( ( ud.startday >= '2024-12-23 00:00:00' AND ud.startday <= '2024-12-28 23:59:59')
                                             OR ( ud.endday >= '2024-12-23 00:00:00' AND ud.endday <= '2024-12-28 23:59:59')
                                             OR ( ud.startday <= '2024-12-23 00:00:00' AND ud.endday >= '2024-12-28 23:59:59')
                                             )
                                            ORDER BY ud.startday;

SELECT
  li.uid AS location_inventory_id,
  limm.*,
  UNIX_TIMESTAMP(li.planning_goal_date) AS planning_goal_date_ts,
  UNIX_TIMESTAMP(li.execution_start_date) AS execution_start_date_ts,
  UNIX_TIMESTAMP(li.execution_end_date) AS execution_end_date_ts,
  li.*,
  l.company
FROM
  gfc_dm_location_inventory_mm limm
  JOIN gfc_dm_location_inventory li ON limm.id_location = li.id_location
  JOIN gfc_dm_location l ON limm.id_location = l.id_location
WHERE
  limm.ref_table = 'users'
  AND limm.ref_uid IN (44)
  AND (
    li.planning_goal_date >= '2024-12-23 00:00:00'
    AND li.planning_goal_date <= '2024-12-28 23:59:59'
  )

SELECT li.uid AS location_inventory_id, limm.*, UNIX_TIMESTAMP(li.planning_goal_date) AS planning_goal_date_ts, UNIX_TIMESTAMP(li.execution_start_date) AS execution_start_date_ts, UNIX_TIMESTAMP(li.execution_end_date) AS execution_end_date_ts, li.*, l.company
                                        FROM gfc_dm_location_inventory_mm limm
                                        JOIN gfc_dm_location_inventory li ON limm.id_location = li.id_location 
                                        JOIN gfc_dm_location l ON limm.id_location = l.id_location 
                                        WHERE limm.ref_table = 'users' AND limm.ref_uid IN (44) 
                                        AND (li.planning_goal_date  >= '2024-12-23 00:00:00'
                                            AND li.planning_goal_date  <= '2024-12-28 23:59:59')
                                        ;

SELECT
  *
FROM
  gfc_dm_users_working_hours
WHERE
  id_user = :uid

SELECT * FROM gfc_dm_users_working_hours WHERE id_user = 44;

SELECT
  *
FROM
  gfc_dm_users_mm
WHERE
  ref_table = 'user_groups'
  AND id_user = :uid

SELECT *
                                FROM gfc_dm_users_mm
                                WHERE ref_table = 'user_groups' AND id_user = 44;

SELECT
  o.*,
  (
    CASE WHEN o.ref_table = 'institution' THEN (
      SELECT
        i.company
      FROM
        gfc_dm_institution i
      WHERE
        i.id_institution = o.ref_uid
    ) WHEN o.ref_table = 'location' THEN (
      SELECT
        l.company
      FROM
        gfc_dm_location l
      WHERE
        l.id_location = o.ref_uid
    ) ELSE 'Mandant nicht gefunden' END
  ) AS company,
  p.company AS partner,
  p.color
FROM
  gfc_dm_orders o
  LEFT JOIN gfc_dm_partner p ON o.id_partner = p.id_partner
WHERE
  (
    (
      o.delivery_date <= '2024-12-28 23:59:59'
      AND o.delivery_date_end >= '2024-12-23 00:00:00'
    )
    OR (
      o.delivery_date >= '2024-12-23 00:00:00'
      AND o.delivery_date <= '2024-12-28 23:59:59'
    )
  )

SELECT o.*, 
                                         ( CASE
                                            WHEN o.ref_table = 'institution' THEN ( SELECT i.company FROM gfc_dm_institution i WHERE i.id_institution = o.ref_uid  ) 
                                            WHEN o.ref_table = 'location' THEN (SELECT l.company FROM gfc_dm_location l WHERE l.id_location = o.ref_uid  ) 
                                            ELSE 'Mandant nicht gefunden' 
                                        END ) AS company,
                                        p.company AS partner, p.color
                                        FROM gfc_dm_orders o
                                        LEFT JOIN gfc_dm_partner p ON o.id_partner = p.id_partner
                                        WHERE ( (o.delivery_date  <= '2024-12-28 23:59:59'
                                        AND o.delivery_date_end  >= '2024-12-23 00:00:00')
                                        OR (o.delivery_date  >= '2024-12-23 00:00:00'
                                        AND o.delivery_date  <= '2024-12-28 23:59:59')
                                        )
                                        ;

SELECT
  sc.*
FROM
  gfc_dm_sys_catalog sc
WHERE
  sc.deleted = 0
ORDER BY
  sc.id_group,
  sc.order_key,
  sc.title

SELECT sc.*
                FROM gfc_dm_sys_catalog sc
                WHERE sc.deleted = 0 ORDER BY sc.id_group, sc.order_key, sc.title ;

SELECT
  *
FROM
  gfc_dm_sys_collector_themes_fields
WHERE
  deleted = 0
  AND id_collector_theme = 1
ORDER BY
  order_key

SELECT * FROM gfc_dm_sys_collector_themes_fields WHERE deleted = 0 AND id_collector_theme = 1 ORDER BY order_key;

SELECT
  *
FROM
  gfc_dm_sys_collector_themes_fields
WHERE
  deleted = 0
  AND id_collector_theme = 2
ORDER BY
  order_key

SELECT * FROM gfc_dm_sys_collector_themes_fields WHERE deleted = 0 AND id_collector_theme = 2 ORDER BY order_key;

SELECT
  *
FROM
  gfc_dm_sys_collector_themes_fields
WHERE
  deleted = 0
  AND id_collector_theme = 3
ORDER BY
  order_key

SELECT * FROM gfc_dm_sys_collector_themes_fields WHERE deleted = 0 AND id_collector_theme = 3 ORDER BY order_key;

SELECT
  *
FROM
  gfc_dm_sys_collector_themes

SELECT * FROM gfc_dm_sys_collector_themes ;