Получение данных из базы JIRA

Получение данных напрямую из базы данных JIRA с помощью Oracle SQL Developer и PL/SQL.

Все проекты

-- все проекты
SELECT DISTINCT
  Project.ID Id,
  Project.PKEY Key,
  Project.PNAME Name
FROM JIRA.PROJECT Project
ORDER BY Name;

Текущий Agile-спринт

-- текущий спринт
SELECT
  ID Id,
  Name Name,
  TO_DATE('1970-01-01', 'YYYY-MM-DD') + START_DATE / 86400000 + 3 / 24 StartDate,
  TO_DATE('1970-01-01', 'YYYY-MM-DD') + END_DATE / 86400000 + 3 / 24 ScheduledEndDate,
  TO_CHAR(TO_DATE('1970-01-01', 'YYYY-MM-DD') + START_DATE / 86400000 + 3 / 24,'DD.MM.YYYY HH24:MI') FormattedStartDate,
  TO_CHAR(TO_DATE('1970-01-01', 'YYYY-MM-DD') + END_DATE / 86400000 + 3 / 24,'DD.MM.YYYY HH24:MI') FormattedScheduledEndDate
FROM JIRA.AO_60DB71_SPRINT
WHERE 
  RAPID_VIEW_ID = 401 AND -- идентификатор Agile Board
  STARTED = 1 AND
  CLOSED = 0;

Все задачи указанного Agile-спринта

-- все задачи спринта
SELECT DISTINCT 
  Issue.Id Id
  , Issue.IssueNum Num
  , (Project.Pkey || '-' || Issue.IssueNum) Key
  , Issue.SUMMARY Subject
FROM
  JIRA.JIRAISSUE Issue
  JOIN JIRA.PROJECT Project ON Issue.PROJECT = Project.ID
  JOIN JIRA.CUSTOMFIELDVALUE FieldValue ON FieldValue.ISSUE = Issue.ID
  JOIN JIRA.CUSTOMFIELD Field ON FieldValue.CUSTOMFIELD = Field.ID
WHERE
  Field.CFNAME = 'Sprint'
  AND FieldValue.STRINGVALUE = '123'; -- идентификатор спринта

Выполненные задачи в рамках спринта

Получение списка выполненных задач в рамках указанного Agile-спринта. Отображается код задачи и название, а также звездочкой помечаются задачи, добавленные после начала спринта.

CLEAR SCREEN;
SET SERVEROUTPUT ON;
SET FEEDBACK OFF;

DECLARE
  boardId NUMBER := 401; -- Agile Board ID
  finishedCallPos NUMBER := 2; -- номер колонки на Agile Board, соответствующей выполненным задачам
  
  s_id VARCHAR(5) := '581'; -- ИД спринта
  
  s_startDateTS NUMBER := 1457591400000; -- дата начала спринта в виде TimeStamp
  s_startDate DATE := TO_DATE('1970-01-01', 'YYYY-MM-DD') + s_startDateTS / 86400000 + 3 / 24 ; -- дата начала спринта
  s_endDateTS NUMBER := 1457591400000; -- дата завершения спринта в виде TimeStamp
  s_endDate DATE := TO_DATE('1970-01-01', 'YYYY-MM-DD') + s_endDateTS / 86400000 + 3 / 24; -- дата завершения спринта
  
  TYPE IssueTypeRow IS RECORD(Id NUMBER, Num NUMBER, Key VARCHAR2(255), Subject VARCHAR2(255), AddAfter VARCHAR2(1));
  TYPE IssueTypeTable IS TABLE OF IssueTypeRow;
  Issues IssueTypeTable;
BEGIN

  SELECT DISTINCT
    Issue.ID Id
    , Issue.IssueNum Num
    , (Project.Pkey || '-' || Issue.IssueNum) Key
    , Issue.SUMMARY Subject
    , CASE WHEN AfterIssue.Id IS NULL THEN ' ' ELSE '*' END AfterIssue
  BULK COLLECT INTO Issues
  FROM
    JIRA.JIRAISSUE Issue
    INNER JOIN JIRA.CUSTOMFIELDVALUE FieldValue ON FieldValue.ISSUE = Issue.ID
    INNER JOIN JIRA.CUSTOMFIELD Field ON FieldValue.CUSTOMFIELD = Field.ID
    INNER JOIN JIRA.CHANGEGROUP ChangeGroup ON ChangeGroup.ISSUEID = Issue.ID
    INNER JOIN JIRA.CHANGEITEM ChangeItem ON ChangeItem.GROUPID = ChangeGroup.ID
    INNER JOIN JIRA.PROJECT Project ON Issue.Project = Project.Id
    LEFT JOIN (
    
    
      -- запросы "Выполнено", добавлено после начала спринта
      SELECT DISTINCT Issue.Id Id
      FROM 
        ( -- все выполненные задачи спринта
          SELECT DISTINCT Issue.ID Id
          FROM
            JIRA.JIRAISSUE Issue
            INNER JOIN JIRA.CUSTOMFIELDVALUE FieldValue ON FieldValue.ISSUE = Issue.ID
            INNER JOIN JIRA.CUSTOMFIELD Field ON FieldValue.CUSTOMFIELD = Field.ID
            INNER JOIN JIRA.CHANGEGROUP ChangeGroup ON ChangeGroup.ISSUEID = Issue.ID
            INNER JOIN JIRA.CHANGEITEM ChangeItem ON ChangeItem.GROUPID = ChangeGroup.ID
            INNER JOIN JIRA.PROJECT Project ON Issue.Project = Project.Id
          WHERE
            Field.CFNAME             = 'Sprint'
            AND FieldValue.STRINGVALUE   = s_id
            AND ChangeItem.Id = (
              SELECT MAX(JIRA.CHANGEITEM.ID)
              FROM JIRA.CHANGEITEM
                INNER JOIN JIRA.CHANGEGROUP ON JIRA.CHANGEITEM.GROUPID = JIRA.CHANGEGROUP.ID
              WHERE 
                JIRA.CHANGEGROUP.ISSUEID = Issue.Id
                AND JIRA.CHANGEITEM.FIELDTYPE = 'jira'
                AND JIRA.CHANGEITEM.FIELD = 'status'
                AND JIRA.CHANGEGROUP.CREATED < s_endDate    
            )
            AND TO_NUMBER(ChangeItem.NEWVALUE) IN (
              SELECT JIRA.AO_60DB71_COLUMNSTATUS.STATUS_ID
              FROM JIRA.AO_60DB71_COLUMN
                INNER JOIN JIRA.AO_60DB71_COLUMNSTATUS ON JIRA.AO_60DB71_COLUMN.ID = JIRA.AO_60DB71_COLUMNSTATUS.COLUMN_ID
              WHERE 
                JIRA.AO_60DB71_COLUMN.RAPID_VIEW_ID = 401
                AND JIRA.AO_60DB71_COLUMN.POS         = finishedCallPos
            )
        ) Issue
        INNER JOIN JIRA.CHANGEGROUP ChangeGroup ON ChangeGroup.ISSUEID = Issue.Id
        INNER JOIN JIRA.CHANGEITEM ChangeItem ON ChangeItem.GROUPID = ChangeGroup.ID
      WHERE
        ChangeItem.Field = 'Sprint'
        AND ChangeGroup.Created > s_startDate
        AND ChangeGroup.Created < s_endDate
    
    
    ) AfterIssue ON Issue.Id = AfterIssue.Id
  WHERE
    Field.CFNAME             = 'Sprint'
    AND FieldValue.STRINGVALUE   = s_id
    AND ChangeItem.Id = (
      SELECT MAX(JIRA.CHANGEITEM.ID)
      FROM JIRA.CHANGEITEM
        INNER JOIN JIRA.CHANGEGROUP ON JIRA.CHANGEITEM.GROUPID = JIRA.CHANGEGROUP.ID
      WHERE 
        JIRA.CHANGEGROUP.ISSUEID = Issue.Id
        AND JIRA.CHANGEITEM.FIELDTYPE = 'jira'
        AND JIRA.CHANGEITEM.FIELD = 'status'
        AND JIRA.CHANGEGROUP.CREATED < s_endDate    
    )
    AND TO_NUMBER(ChangeItem.NEWVALUE) IN (
      SELECT JIRA.AO_60DB71_COLUMNSTATUS.STATUS_ID
      FROM JIRA.AO_60DB71_COLUMN
        INNER JOIN JIRA.AO_60DB71_COLUMNSTATUS ON JIRA.AO_60DB71_COLUMN.ID = JIRA.AO_60DB71_COLUMNSTATUS.COLUMN_ID
      WHERE 
        JIRA.AO_60DB71_COLUMN.RAPID_VIEW_ID = boardId
        AND JIRA.AO_60DB71_COLUMN.POS         = finishedCallPos
    ) 
  ORDER BY Num;
  
  DBMS_OUTPUT.PUT_LINE('Количество задач: ' || Issues.COUNT);
  FOR i IN Issues.FIRST..Issues.LAST LOOP
    DBMS_OUTPUT.PUT_LINE(Issues(i).Key || ' ' ||  Issues(i).AddAfter || ' ' || Issues(i).Subject);
  END LOOP;
  
END;

About the author

Добавить комментарий

Сказать спасибо

Способ платежа:

Подписаться на обновления

Укажите свой e-mail чтобы получать уведомления о новых статьях.

Присоединиться к еще 1 подписчику