Untitled query #61908

This query is marked as a draft This query has been published by Huji.
SQL

SELECT DISTINCT
  log_id,
  log_id AS log_link_title,
  actor_name,
  CONCAT(':{{ns:', ar_namespace, '}}:', ar_title) AS link,
  comment_text,
  STR_TO_DATE(LEFT(log_timestamp, 8), '%Y%m%d'),
  'حذف تصویر' AS issue
FROM logging
JOIN actor
  ON log_actor = actor_id
JOIN comment
  ON log_comment_id = comment_id
JOIN user_groups ug
  ON actor_user = ug.ug_user
  AND ug.ug_group = 'eliminator'
JOIN archive
  ON log_page = ar_page_id
LEFT JOIN user_groups ug2
  ON actor_user = ug2.ug_user
  AND ug2.ug_group = 'image-reviewer'
WHERE
  log_type = 'delete'
  AND ar_namespace = 6
  AND log_timestamp >=
    DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 30 DAY), '%Y%m%d000000')
  AND ug2.ug_user IS NULL
UNION
SELECT DISTINCT
  log_id,
  log_id AS log_link_title,
  actor_name,
  CONCAT('کاربر:', log_title) AS link,
  comment_text,
  STR_TO_DATE(LEFT(log_timestamp, 8), '%Y%m%d'),
  'بستن غیرمجاز' AS issue
FROM logging
JOIN actor
  ON log_actor = actor_id
JOIN comment
  ON log_comment_id = comment_id
JOIN user_groups ug
  ON actor_user = ug.ug_user
JOIN user u2
  ON log_title = u2.user_name
JOIN user_groups ug2
  ON u2.user_id = ug2.ug_user
WHERE
  ug.ug_group = 'eliminator'
  AND log_type = 'block'
  AND ug2.ug_group IN (
    'sysop',
    'bureaucrat',
    'patroller',
    'autopatrol',
    'rollbacker'
  )
  AND log_timestamp >=
    DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 30 DAY), '%Y%m%d000000')
UNION
SELECT DISTINCT
  log_id,
  log_id AS log_link_title,
  actor_name,
  log_title AS link,
  comment_text,
  STR_TO_DATE(LEFT(log_timestamp, 8), '%Y%m%d'),
  'محافظت کامل' AS issue
FROM logging
JOIN actor
  ON log_actor = actor_id
JOIN comment
  ON log_comment_id = comment_id
JOIN user_groups
  ON actor_user = ug_user
WHERE
  ug_group = 'eliminator'
  AND log_type = 'protect'
  AND log_action = 'protect'
  AND log_params LIKE '%level%sysop%'
  AND log_timestamp >=
    DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 30 DAY), '%Y%m%d000000')
UNION
SELECT
  log_id,
  log_id AS log_link_title,
  actor_name,
  CONCAT('{{ns:' , log_namespace, '}}:', log_title) AS link,
  comment_text,
  STR_TO_DATE(LEFT(log_timestamp, 8), '%Y%m%d'),
  'احیای نسخهٔ حذف‌شده' AS issue
FROM logging
JOIN actor
  ON log_actor = actor_id
JOIN comment
  ON log_comment_id = comment_id
JOIN user_groups
  ON actor_user = ug_user
WHERE
  ug_group = 'eliminator'
  AND log_params LIKE '%nfield";i:0%'
  AND log_type='delete'
  AND log_action='revision'
  AND log_timestamp >=
    DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 30 DAY), '%Y%m%d000000')
UNION
SELECT
  log_id,
  log_id AS log_link_title,
  actor_name,
  CONCAT('{{ns:' , log_namespace, '}}:', log_title) AS link,
  comment_text,
  STR_TO_DATE(LEFT(log_timestamp, 8), '%Y%m%d'),
  'محافظت طولانی' AS issue
FROM (
  SELECT
    log_id,
    log_id AS log_id_title,
    actor_name,
    log_namespace,
    log_title,
    log_timestamp,
    log_comment_id,
    DATEDIFF(
      DATE(
        CONCAT(
          SUBSTRING(log_params, LOCATE('s:6:"expiry";', log_params) + 19, 4),
          SUBSTRING(log_params, LOCATE('s:6:"expiry";', log_params) + 23, 2),
          SUBSTRING(log_params, LOCATE('s:6:"expiry";', log_params) + 25, 2)
        )
      ),
      date(substring(log_timestamp, 1, 8))
    ) AS duration_days,
    CASE
      WHEN log_params LIKE '%s:6:"expiry";s:8:"infinity"%' THEN 1
      ELSE 0
    END AS duration_infinity
  FROM logging_userindex
  JOIN actor
    ON log_actor = actor_id
  JOIN user_groups
    ON actor_user = ug_user
    AND ug_group = 'eliminator'
  WHERE
    log_type = 'protect'
    AND log_action = 'protect'
) AS block_logs
JOIN comment_logging
  ON comment_id = log_comment_id
WHERE
  duration_days > 14
  OR duration_infinity = 1
ORDER BY log_id DESC
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query Stop Query
All SQL code is licensed under CC0 License.
SQL

Checking query status...
