# Pegar imagem postgres na versão 16.1 como base
FROM postgres:16.1

# Atualizar e instalar cron e vim
RUN apt-get update && apt-get install -y cron vim

# Definir fuso horário do cron para São Paulo
RUN echo "America/Sao_Paulo" > /etc/timezone

# Definir fuso horário do container para São Paulo
RUN ln -sf /usr/share/zoneinfo/America/Sao_Paulo /etc/localtime

# Copiar o arquivo crontab com as configurações de agendamento
COPY crontab /var/spool/cron/crontabs/root

# Copiar os scripts shell a serem executados pelo crontab
COPY drop_view.sh /tmp
COPY create_view.sh /tmp

# Dar permissão de execução aos arquivos
RUN chmod +x /tmp/drop_view.sh
RUN chmod +x /tmp/create_view.sh

docker build -t postgres-cron-vim:16.1 .

CREATE FUNCTION cont_dias_sprint(sprint_name VARCHAR)
RETURNS SETOF NUMERIC AS $$
-- Selecionado o número de dias da Sprint menos 1 para divisão na coluna de planejado
SELECT COUNT(intervalo) - 1
FROM generate_series(
    -- Gerando série de datas entre o dia de início e dia fim da sprint
    (SELECT "startDate" FROM public.sprints WHERE name = sprint_name),
    (SELECT "endDate" FROM public.sprints WHERE name = sprint_name),
    INTERVAL '1 day'
) AS intervalo
-- Extraindo os dias da semana e filtrando para retirar sábado e domingo
WHERE EXTRACT(dow FROM intervalo) NOT IN (6, 0);
$$ LANGUAGE sql;

CREATE FUNCTION estimado_total(sprint_name VARCHAR)
RETURNS SETOF NUMERIC AS $$
-- Somando a estimativa para cada card e trazendo relação com a sprint
SELECT SUM((CAST((i.fields -> 'timeoriginalestimate') AS INTEGER))/3600)
FROM public.issues AS i
JOIN public.sprint_issues AS si ON i.key = si.key
JOIN public.sprints AS s ON si."sprintId" = s.Id
WHERE s.name = sprint_name
GROUP BY s.name;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION planejado_ideal(sprint_name VARCHAR)
RETURNS TABLE (dia DATE, planejado NUMERIC) AS $$

DECLARE
  -- Declarando variável a ser utilizada no loop começando com o valor estimado total para a sprint 
  current_value NUMERIC(8,6) := estimado_total(sprint_name);
  -- Declarando variável de incremento
  increment_value NUMERIC(8,6) := estimado_total(sprint_name) / cont_dias_sprint(sprint_name);

BEGIN

  FOR dia IN
    -- Loop para percorrer os dias da sprint
    SELECT intervalo
    FROM generate_series(
        (SELECT "startDate" FROM public.sprints WHERE name = sprint_name),
        (SELECT "endDate" FROM public.sprints WHERE name = sprint_name),
        INTERVAL '1 day') AS intervalo
    WHERE EXTRACT(dow FROM intervalo) NOT IN (6, 0)
  LOOP
    -- Começando pelo primeiro valor como o estimado total da sprint
    planejado := current_value;
    RETURN NEXT;
    -- Atualizando próximo valor da coluna sendo o anterior menos o incremento
    current_value := current_value - increment_value;
  END LOOP;

  RETURN;

END;

$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION horas_restantes(sprint_name VARCHAR, prev_horas_entregues NUMERIC, datas DATE)
RETURNS TABLE(dia DATE, horas_restantes NUMERIC) AS $$

DECLARE
    -- Definindo cursor para percorrer todos os dias de cada sprint
    tbl_cursor CURSOR FOR (
        SELECT s.name AS sprint, data.data :: DATE
        FROM public.sprints AS s
        CROSS JOIN generate_series(s."startDate", s."endDate", interval '1 day') AS data
        WHERE EXTRACT(dow FROM data.data) NOT IN (6, 0) AND s.name = sprint_name
        ORDER BY data
    );
    tbl_row RECORD;
    -- Definindo valor inicial como estimado total para a sprint
    horas_restantes NUMERIC := estimado_total(sprint_name);
    -- Definindo variáveis de controle para que a cada iteração possam ser selecionadas as horas entregues do dia anterior
    horas_entregues NUMERIC := 0;
    current_value NUMERIC := 0;

BEGIN

    OPEN tbl_cursor;

    LOOP
        -- Definindo critério de parada para o cursor
        FETCH FROM tbl_cursor INTO tbl_row;
        EXIT WHEN NOT FOUND;
        -- Selecionando as horas entregues
        SELECT COALESCE(SUM((issues.fields ->> 'timeoriginalestimate')::INTEGER / 3600), 0)
        INTO horas_entregues
        FROM issues
        WHERE to_date(issues.fields ->> 'resolutiondate', 'YYYY-MM-DD') = tbl_row.data;
        -- Subtraindo o valor da coluna horas restantes pelo valor de horas entregues
        horas_restantes := horas_restantes - current_value;
        -- Armazenando o valor de horas entregues na data x, para que na próxima interação seja feita a 
        -- subtração por esse valor, que representará o valor de horas entregues na data anterior (x-1)
        current_value := horas_entregues;
        -- Retornando a data e o valor de horas_restante a cada iteração
        RETURN QUERY SELECT tbl_row.data, horas_restantes;
    END LOOP;

    CLOSE tbl_cursor;

END;

$$ LANGUAGE plpgsql;

CREATE OR REPLACE VIEW public.vw_dadosburndown AS
WITH dados_sprint AS (
    SELECT 
        s.name AS sprint, 
        data.data :: DATE
    FROM public.sprints AS s
    CROSS JOIN generate_series(s."startDate", s."endDate", INTERVAL '1 day') AS data
    WHERE EXTRACT(dow FROM data.data) NOT IN (6, 0)
    ORDER BY data
),
dados_horas_entregues AS(
    SELECT 
        SUM((CAST((fields -> 'timeoriginalestimate') AS INTEGER))/3600) AS horas_entregues,
        to_date(fields ->> 'resolutiondate', 'YYYY-MM-DD') AS data_encerramento
    FROM public.issues
    GROUP BY data_encerramento
),
prev_dados_tempo_gasto AS(
    SELECT
        CAST((jsonb_array_elements(fields -> 'worklog' -> 'worklogs') -> 'timeSpentSeconds') AS INTEGER)/3600 AS tempo_gasto,
        to_date(jsonb_array_elements(fields -> 'worklog' -> 'worklogs') ->> 'updated', 'YYYY-MM-DD') AS dia_gasto
    FROM public.issues
),
dados_tempo_gasto AS(   
    SELECT
        dia_gasto,
        SUM(tempo_gasto) AS tempo_gasto
    FROM prev_dados_tempo_gasto
    GROUP BY dia_gasto
)
SELECT
    ds.sprint,
    ds.data,
    dhe.horas_entregues,
    dtg.tempo_gasto,
    plan.planejado,
    he.horas_restantes
FROM dados_sprint AS ds
LEFT JOIN LATERAL planejado_ideal(ds.sprint) AS plan ON ds.data = plan.dia
LEFT JOIN dados_horas_entregues AS dhe ON ds.data = dhe.data_encerramento
LEFT JOIN dados_tempo_gasto AS dtg ON ds.data = dtg.dia_gasto
JOIN horas_restantes(ds.sprint, dhe.horas_entregues, ds.data) AS he ON ds.data = he.dia
ORDER BY ds.sprint,ds.data;

#!/bin/bash

# Definindo as variáveis de ambiente para o PostgreSQL
export PGHOST=localhost
export PGUSER=user
export PGPASSWORD=XXXXX
export PGDATABASE=DashboardScrum

# Executando o comando para deletar a view
psql -c "DROP VIEW public.vw_dadosburndown;"

#!/bin/bash

# Definindo as variáveis de ambiente para o PostgreSQL
export PGHOST=localhost
export PGUSER=gpb
export PGPASSWORD=gpb5579
export PGDATABASE=DashboardScrum

# Váriavél com a criação da view
VIEW_DEFINITION="
CREATE OR REPLACE VIEW public.vw_dadosburndown AS
WITH dados_sprint AS (
  SELECT 
      s.name AS sprint, 
      data.data :: DATE
  FROM public.sprints AS s
  CROSS JOIN generate_series(s.\"startDate\", s.\"endDate\", INTERVAL '1 day') AS data
  WHERE EXTRACT(dow FROM data.data) NOT IN (6, 0)
  ORDER BY data
),
dados_horas_entregues AS(
  SELECT 
    SUM((CAST((fields -> 'timeoriginalestimate') AS INTEGER))/3600) AS horas_entregues,
    to_date(fields ->> 'resolutiondate', 'YYYY-MM-DD') AS data_encerramento
  FROM public.issues
  GROUP BY data_encerramento
),
prev_dados_tempo_gasto AS(
  SELECT
    CAST((jsonb_array_elements(fields -> 'worklog' -> 'worklogs') -> 'timeSpentSeconds') AS INTEGER)/3600 AS tempo_gasto,
    to_date(jsonb_array_elements(fields -> 'worklog' -> 'worklogs') ->> 'updated', 'YYYY-MM-DD') AS dia_gasto
  FROM public.issues
),
dados_tempo_gasto AS( 
  SELECT
    dia_gasto,
    SUM(tempo_gasto) AS tempo_gasto
  FROM prev_dados_tempo_gasto
  GROUP BY dia_gasto
)
SELECT
  ds.sprint,
  ds.data,
  dhe.horas_entregues,
  dtg.tempo_gasto,
  plan.planejado,
  he.horas_restantes
FROM dados_sprint AS ds
LEFT JOIN LATERAL planejado_ideal(ds.sprint) AS plan ON ds.data = plan.dia
LEFT JOIN dados_horas_entregues AS dhe ON ds.data = dhe.data_encerramento
LEFT JOIN dados_tempo_gasto AS dtg ON ds.data = dtg.dia_gasto
JOIN horas_restantes(ds.sprint, dhe.horas_entregues, ds.data) AS he ON ds.data = he.dia
ORDER BY ds.sprint,ds.data;"

# Executando o comando de criação da view
psql -c "$VIEW_DEFINITION"

0 3 * * 1-5 /tmp/drop_view.sh >> /tmp/cron_drop.log 2>&1
0 5 * * 1-5 /tmp/create_view.sh >> /tmp/cron_create.log 2>&1

service cron start

Dashboard Scrum¶

Sumário

1. Problema de negócio ¶

2. Proposta de solução ¶

2.1 Ferramentas e custo ¶

3. Configurando a fonte no Airbyte ¶

4. Preparando o banco de dados ¶

4.1 Utilização do Docker ¶

4.2 Criação do Dockerfile ¶

4.3 Criando imagem e container no Docker ¶

4.4 Acessando o banco de dados ¶

5. Configurando o destino no Airbyte ¶

6. Configurando a conexão no Airbyte ¶

7. Realizando transformação nos dados ¶

7.1 Função que retorna a contagem de dias da sprint ¶

7.2 Função que retorna o estimado total da sprint ¶

7.3 Função que determina linha de planejado ideal ¶

7.4 Função que calcula horas restantes ¶

7.5 View ¶

8. Visualizando os dados ¶

8.1 Conexão no Power Bi ¶

8.2 Gráfico Burndown com Tempo Gasto ¶

9. Atualização dos dados ¶

9.1 Comando para deletar view (drop_view.sh) ¶

9.2 Comando para criar view (create_view.sh) ¶

9.3 Comando de agendamento no CRON (crontab) ¶

9.4 Inicializando o CRON no container ¶

9.5 Agendando atualização no Airbyte ¶

10. Conclusão ¶

Entre em contato¶