DICCIONARIO DE DATOS & GUÍA DE CONSULTA

Server=SRVINTRANET.TSA.INT;Database=APIREST;User Id=apirest_readonly;Password=Tecnasic2026.Readonly!;TrustServerCertificate=True;MultipleActiveResultSets=true;

import pyodbc

connection_string = (
    "DRIVER={ODBC Driver 18 for SQL Server};"
    "SERVER=SRVINTRANET.TSA.INT;"
    "DATABASE=APIREST;"
    "UID=apirest_readonly;"
    "PWD=Tecnasic2026.Readonly!;"
    "TrustServerCertificate=yes;"
)
conn = pyodbc.connect(connection_string)

let
    // Conexión directa a SQL Server con rol de lectura
    Origen = Sql.Database("SRVINTRANET.TSA.INT", "APIREST", [CreateNavigationProperties=false])
in
    Origen

-- Consulta de Proyectos activos con fecha de última sincronización
SELECT 
    p.ProjectId,
    p.ProjectCode,
    p.ProjectName,
    ISNULL(w.WorkspaceName, 'Proyectos Asignados') AS WorkspaceName,
    p.ProjectManager,
    p.PlannedFinish,
    p.Status,
    DATEADD(hour, -3, p.LastSync) AS LastSync_HoraChile -- Conversión de UTC a Chile
FROM dbo.Projects p WITH (NOLOCK)
LEFT JOIN dbo.Workspaces w WITH (NOLOCK) ON p.WorkspaceId = w.WorkspaceId
WHERE p.Status = 'Active'
ORDER BY p.ProjectCode;

-- Detalle semanal de horas planificadas por especialidad
SELECT 
    p.ProjectCode,
    p.ProjectName,
    h.PrimaryRole AS Especialidad,
    h.WeekKey AS CodigoSemana,
    h.WeekStartDate AS FechaLunesSemana,
    h.Hours AS HorasHombre,
    DATEPART(year, h.WeekStartDate) AS Anio,
    DATEPART(month, h.WeekStartDate) AS Mes
FROM dbo.ProjectRoleHoursWeekly h WITH (NOLOCK)
INNER JOIN dbo.Projects p WITH (NOLOCK) ON h.ProjectId = p.ProjectId
WHERE h.Hours > 0
ORDER BY p.ProjectCode, h.PrimaryRole, h.WeekStartDate;

-- Agrupación mensual de horas por proyecto y rol (ideal para gráficos de barras y curvas de dotación)
SELECT 
    p.ProjectCode,
    p.ProjectName,
    h.PrimaryRole AS Especialidad,
    FORMAT(h.WeekStartDate, 'yyyy-MM') AS PeriodoMes,
    SUM(h.Hours) AS TotalHorasMensuales,
    ROUND(SUM(h.Hours) / 180.0, 1) AS DotacionEquivalentePersonas -- Estimación a 180 hrs/mes
FROM dbo.ProjectRoleHoursWeekly h WITH (NOLOCK)
INNER JOIN dbo.Projects p WITH (NOLOCK) ON h.ProjectId = p.ProjectId
GROUP BY p.ProjectCode, p.ProjectName, h.PrimaryRole, FORMAT(h.WeekStartDate, 'yyyy-MM')
ORDER BY p.ProjectCode, PeriodoMes;

-- Desanidar los proyectos asociados a cada programa usando OPENJSON
SELECT 
    prog.ProgramCode,
    prog.ProgramName,
    p.ProjectCode,
    p.ProjectName,
    p.Status AS EstadoProyecto
FROM dbo.Programs prog WITH (NOLOCK)
CROSS APPLY OPENJSON(prog.LinkedProjectIdsJson) AS j
INNER JOIN dbo.Projects p WITH (NOLOCK) ON CAST(j.value AS int) = p.ProjectId
ORDER BY prog.ProgramCode, p.ProjectCode;

-- 1. Vista de Proyectos con Workspace
CREATE OR ALTER VIEW dbo.vw_BI_Proyectos
AS
SELECT 
    p.ProjectId,
    p.ProjectCode,
    p.ProjectName,
    ISNULL(w.WorkspaceName, 'Proyectos Asignados') AS WorkspaceName,
    p.ProjectManager,
    p.PlannedFinish,
    p.Status,
    p.LastSync
FROM dbo.Projects p WITH (NOLOCK)
LEFT JOIN dbo.Workspaces w WITH (NOLOCK) ON p.WorkspaceId = w.WorkspaceId;
GO

-- 2. Vista de Horas Semanales Desnormalizadas
CREATE OR ALTER VIEW dbo.vw_BI_HorasSemanales
AS
SELECT 
    h.Id,
    p.ProjectCode,
    p.ProjectName,
    h.PrimaryRole,
    h.WeekKey,
    h.WeekStartDate,
    h.Hours,
    h.SyncDate
FROM dbo.ProjectRoleHoursWeekly h WITH (NOLOCK)
INNER JOIN dbo.Projects p WITH (NOLOCK) ON h.ProjectId = p.ProjectId;
GO

-- 3. Vista de Estado de Última Sincronización
CREATE OR ALTER VIEW dbo.vw_BI_UltimaSincronizacion
AS
SELECT TOP 1
    Id,
    StartedAt,
    FinishedAt,
    ProjectsProcessed,
    Status,
    ErrorMessage
FROM dbo.SyncLogs WITH (NOLOCK)
ORDER BY StartedAt DESC;
GO