328 lines
27 KiB
Transact-SQL
328 lines
27 KiB
Transact-SQL
USE [SIGPC]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[PA_DESGLOCEGESTIONESTABLA] Script Date: 1/21/2021 9:01:45 AM ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
|
||
|
||
|
||
|
||
-- =============================================
|
||
-- Author: Juan Pablo Monge Delgado CR-ATESA 13/07/2020
|
||
-- Create date: 14/02/2018
|
||
-- Description: Carga el último consecutivo obtenido por sigla
|
||
-- Modificado: Juan Pablo Monge Delgado CR-ATESA 13/07/2020
|
||
-- Actualiza el consecutivo en la tabla dependencia
|
||
-- Construye el nombre del documento
|
||
-- Se agrega codigo para el manejo de las transacciones y el aislamiento de recursos para evitar lectura sucia o fantasma.
|
||
-- =============================================
|
||
|
||
|
||
|
||
ALTER PROCEDURE [dbo].[PA_DESGLOCEGESTIONESTABLA]
|
||
@p_status VARCHAR(2) = '' OUTPUT
|
||
,@p_message VARCHAR(500) = '' OUTPUT
|
||
, @Anno INT
|
||
,@Mes INT
|
||
,@OtroMes INT
|
||
|
||
|
||
WITH EXECUTE AS OWNER
|
||
AS
|
||
|
||
|
||
|
||
BEGIN
|
||
--CONFIGURACION DE LA CONSULTA
|
||
SET NOCOUNT ON --ACTIVA EL CONTEO DE REGISTROS QUE SE VERAN AFECTADOS EN LA TRANSACCION
|
||
SET XACT_ABORT ON --SI UNA INSTRUCCION TRANSACT-SQL GENERA UN ERROR EN TIEMPO DE EJECUCION, SE TERMINA TODA LA TRANSACCION Y SE REVIERTE
|
||
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ; --NO PERMITE LECTURA DEL REGISTRO ACTUAL, NI MODIFICACION O INSERCION DE OTRO REGISTRO QUE PUDIESE TENER CLAVE DUPLICADA
|
||
BEGIN TRY
|
||
--DECLARACION DE VARIABLES
|
||
DECLARE @ErrorMessage NVARCHAR(4000);
|
||
DECLARE @ErrorSeverity INT;
|
||
DECLARE @ErrorState INT;
|
||
|
||
|
||
|
||
BEGIN TRANSACTION --EN CASO DE REALIZAR MODIFICACIONES A LA BASE DE DATOS INSERT UPDATE DELETE
|
||
|
||
DECLARE @Colums VARCHAR(MAX);
|
||
DECLARE @query VARCHAR(MAX);
|
||
DECLARE @SomeString varchar(8000) = '';
|
||
DECLARE @cnt INT = @Mes;
|
||
|
||
|
||
CREATE TABLE #TablaTemporal (
|
||
[Total en SIGDD] [varchar](200) NOT NULL,
|
||
[Cancelada] [varchar](200) NOT NULL,
|
||
[Bloqueada] [varchar](200) NOT NULL,
|
||
[Ingresadas en SIGDD] [varchar](200)NOT NULL,
|
||
[Finalizada] [varchar](200) NOT NULL,
|
||
[Pendiente] [varchar](200) NOT NULL,
|
||
[% de atencion] [varchar](200) NOT NULL,
|
||
[% pendientes de resolver][varchar](200) NOT NULL,
|
||
[Fecha] [varchar](100) NOT NULL
|
||
);
|
||
|
||
|
||
|
||
DROP TABLE t_rpt_desglocegestiones
|
||
|
||
|
||
CREATE TABLE [dbo].[t_rpt_desglocegestiones](
|
||
[PKDESGLOCEGESTIONES] [int] IDENTITY(1,1) NOT NULL,
|
||
[CANCELADADESGLOCEGESTIONES] [int] NOT NULL,
|
||
[BLOQUEADADESGLOCEGESTIONES] [int] NOT NULL,
|
||
[FINALIZADADESGLOCEGESTIONES] [int] NOT NULL,
|
||
[PENDIENTEDESGLOCEGESTIONES] [int] NOT NULL,
|
||
[MESDESGLOCEGESTIONES] [int] NOT NULL,
|
||
[ANNODESGLOCEGESTIONES] [int] NOT NULL,
|
||
[STATUSDESGLOCEGESTIONES] [bit] NOT NULL,
|
||
CONSTRAINT [PK_t_rpt_desglocegestiones] PRIMARY KEY CLUSTERED
|
||
(
|
||
[PKDESGLOCEGESTIONES] ASC
|
||
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
||
) ON [PRIMARY]
|
||
|
||
INSERT INTO [dbo].[t_rpt_desglocegestiones]
|
||
([CANCELADADESGLOCEGESTIONES]
|
||
,[BLOQUEADADESGLOCEGESTIONES]
|
||
,[FINALIZADADESGLOCEGESTIONES]
|
||
,[PENDIENTEDESGLOCEGESTIONES]
|
||
,[MESDESGLOCEGESTIONES]
|
||
,[ANNODESGLOCEGESTIONES]
|
||
,[STATUSDESGLOCEGESTIONES])
|
||
SELECT
|
||
(SELECT COUNT(PKSOLICITUD) FROM dbo.t_dis_solicitud A INNER JOIN
|
||
dbo.t_sgm_programacionsolicitud ON dbo.t_sgm_programacionsolicitud.FKSOLICITUD = A.PKSOLICITUD AND FKESTADOPROGRAMACION = 5 AND PKPROGRAMACIONSOLICITUD = (SELECT MAX(PKPROGRAMACIONSOLICITUD) FROM t_sgm_programacionsolicitud D WHERE D.FKSOLICITUD = A.PKSOLICITUD) --BLOQUEADAS
|
||
WHERE MONTH(A.FECHAINSERTASOLICITUD) = MONTH(B.FECHAINSERTASOLICITUD) AND YEAR(A.FECHAINSERTASOLICITUD) = YEAR(B.FECHAINSERTASOLICITUD) --AND A.FKDEPENDENCIASIGDD = B.FKDEPENDENCIASIGDD
|
||
) AS CANCELADAS,
|
||
(SELECT COUNT(PKSOLICITUD) FROM dbo.t_dis_solicitud A INNER JOIN
|
||
dbo.t_sgm_programacionsolicitud ON dbo.t_sgm_programacionsolicitud.FKSOLICITUD = A.PKSOLICITUD AND FKESTADOPROGRAMACION = 6 AND PKPROGRAMACIONSOLICITUD = (SELECT MAX(PKPROGRAMACIONSOLICITUD) FROM t_sgm_programacionsolicitud D WHERE D.FKSOLICITUD = A.PKSOLICITUD) --BLOQUEADAS
|
||
WHERE MONTH(A.FECHAINSERTASOLICITUD) = MONTH(B.FECHAINSERTASOLICITUD) AND YEAR(A.FECHAINSERTASOLICITUD) = YEAR(B.FECHAINSERTASOLICITUD) --AND A.FKDEPENDENCIASIGDD = B.FKDEPENDENCIASIGDD
|
||
) AS BLOQUEADAS,
|
||
(SELECT COUNT(PKSOLICITUD) FROM dbo.t_dis_solicitud A INNER JOIN
|
||
dbo.t_sgm_programacionsolicitud ON dbo.t_sgm_programacionsolicitud.FKSOLICITUD = A.PKSOLICITUD AND FKESTADOPROGRAMACION = 3 AND PKPROGRAMACIONSOLICITUD = (SELECT MAX(PKPROGRAMACIONSOLICITUD) FROM t_sgm_programacionsolicitud D WHERE D.FKSOLICITUD = A.PKSOLICITUD)--FINALIZADAS
|
||
WHERE MONTH(A.FECHAINSERTASOLICITUD) = MONTH(B.FECHAINSERTASOLICITUD) AND YEAR(A.FECHAINSERTASOLICITUD) = YEAR(B.FECHAINSERTASOLICITUD) --AND A.FKDEPENDENCIASIGDD = B.FKDEPENDENCIASIGDD
|
||
) AS FINALIZADAS,
|
||
(SELECT COUNT(PKSOLICITUD) FROM dbo.t_dis_solicitud A INNER JOIN
|
||
dbo.t_sgm_programacionsolicitud ON dbo.t_sgm_programacionsolicitud.FKSOLICITUD = A.PKSOLICITUD AND FKESTADOPROGRAMACION NOT IN (3,5,6) AND PKPROGRAMACIONSOLICITUD = (SELECT MAX(PKPROGRAMACIONSOLICITUD) FROM t_sgm_programacionsolicitud D WHERE D.FKSOLICITUD = A.PKSOLICITUD)--PENDIENTES
|
||
WHERE MONTH(A.FECHAINSERTASOLICITUD) = MONTH(B.FECHAINSERTASOLICITUD) AND YEAR(A.FECHAINSERTASOLICITUD) = YEAR(B.FECHAINSERTASOLICITUD) --AND A.FKDEPENDENCIASIGDD = B.FKDEPENDENCIASIGDD
|
||
) AS PENDIENTES,
|
||
--count(PKSOLICITUD) CANTIDADSOLICITUDES,
|
||
MONTH(FECHAINSERTASOLICITUD) AS MES,
|
||
YEAR(FECHAINSERTASOLICITUD) AS AÑO,
|
||
1
|
||
FROM dbo.t_sgm_programacionsolicitud INNER JOIN
|
||
dbo.t_dis_solicitud B ON dbo.t_sgm_programacionsolicitud.FKSOLICITUD = B.PKSOLICITUD AND PKPROGRAMACIONSOLICITUD = (SELECT MAX(PKPROGRAMACIONSOLICITUD) FROM t_sgm_programacionsolicitud C WHERE C.FKSOLICITUD = B.PKSOLICITUD) LEFT OUTER JOIN
|
||
dbo.t_adm_tipodisponibilidad ON B.FKTIPODISPONIBILIDAD = dbo.t_adm_tipodisponibilidad.PKTIPODISPONIBILIDAD LEFT OUTER JOIN
|
||
dbo.t_adm_tipoinspeccion ON B.FKTIPOINSPECCION = dbo.t_adm_tipoinspeccion.PKTIPOINSPECCION LEFT OUTER JOIN
|
||
dbo.t_adm_dependenciasigdd ON B.FKDEPENDENCIASIGDD = dbo.t_adm_dependenciasigdd.PKDEPENDENCIASIGDD LEFT OUTER JOIN
|
||
dbo.t_adm_distrito INNER JOIN
|
||
dbo.t_adm_canton ON dbo.t_adm_distrito.FKCANTON = dbo.t_adm_canton.PKCANTON INNER JOIN
|
||
dbo.t_adm_provincia ON dbo.t_adm_canton.FKPROVINCIA = dbo.t_adm_provincia.PKPROVINCIA INNER JOIN
|
||
dbo.t_adm_region ON dbo.t_adm_distrito.FKREGION = dbo.t_adm_region.PKREGION ON B.FKDISTRITO = dbo.t_adm_distrito.PKDISTRITO
|
||
|
||
|
||
WHERE (dbo.t_adm_distrito.ASTATEDISTRITO = 1)
|
||
AND (dbo.t_adm_region.ASTATEREGION = 1)
|
||
AND (dbo.t_adm_canton.ASTATECANTON = 1)
|
||
AND (dbo.t_adm_provincia.ASTATEPROVINCIA = 1)
|
||
AND (dbo.t_adm_tipodisponibilidad.ASTATETIPODISPONIBILIDAD = 1)
|
||
AND (dbo.t_adm_tipoinspeccion.ASTATETIPOINSPECCION = 1)
|
||
|
||
GROUP BY MONTH(FECHAINSERTASOLICITUD),YEAR(FECHAINSERTASOLICITUD)
|
||
ORDER BY YEAR(FECHAINSERTASOLICITUD)ASC,MONTH(FECHAINSERTASOLICITUD)ASC
|
||
|
||
--WAITFOR DELAY '00:00:05'
|
||
|
||
|
||
|
||
IF (@OtroMes = 0 OR @OtroMes IS NULL OR @OtroMes < @Mes) AND @Mes != 0
|
||
BEGIN
|
||
SET @OtroMes = @Mes
|
||
END
|
||
ELSE IF (@OtroMes = 0 OR @OtroMes IS NULL) AND (@Mes = 0 OR @Mes IS NULL)
|
||
BEGIN
|
||
SET @Mes = 1
|
||
SET @OtroMes = 12
|
||
SET @cnt =@Mes
|
||
|
||
END
|
||
|
||
|
||
WHILE @cnt <= @OtroMes
|
||
BEGIN
|
||
SELECT @Colums = FORMAT(CAST('01/'+CONVERT(varchar,@cnt )+'/2000' as date) , 'MMM')
|
||
|
||
IF NOT EXISTS( SELECT [MESDESGLOCEGESTIONES] FROM [SIGPC].[dbo].[t_rpt_desglocegestiones] WHERE [MESDESGLOCEGESTIONES] = @cnt AND [ANNODESGLOCEGESTIONES] = @Anno)
|
||
BEGIN
|
||
|
||
INSERT INTO [dbo].[t_rpt_desglocegestiones]
|
||
([CANCELADADESGLOCEGESTIONES]
|
||
,[BLOQUEADADESGLOCEGESTIONES]
|
||
,[FINALIZADADESGLOCEGESTIONES]
|
||
,[PENDIENTEDESGLOCEGESTIONES]
|
||
,[MESDESGLOCEGESTIONES]
|
||
,[ANNODESGLOCEGESTIONES]
|
||
,[STATUSDESGLOCEGESTIONES])
|
||
VALUES
|
||
(0
|
||
,0
|
||
,0
|
||
,0
|
||
,@cnt
|
||
,@Anno
|
||
,1)
|
||
--SET @Colums = FORMAT(CAST('01/'+CONVERT(varchar,@cnt)+'/2000' as date) , 'MMM')
|
||
|
||
END
|
||
|
||
--PRINT @Colums
|
||
SET @SomeString += '['+ @Colums +'],';
|
||
SET @Colums = ''
|
||
|
||
SET @cnt = @cnt + 1;
|
||
END;
|
||
|
||
|
||
--SELECT *FROM [t_rpt_desglocegestiones]
|
||
|
||
IF @SomeString != ''
|
||
BEGIN
|
||
SET @SomeString = substring(@SomeString, 1, (len(@SomeString) - 1))
|
||
INSERT INTO #TablaTemporal (
|
||
[Total en SIGDD] ,
|
||
[Cancelada] ,
|
||
[Bloqueada] ,
|
||
[Ingresadas en SIGDD] ,
|
||
[Finalizada] ,
|
||
[Pendiente] ,
|
||
[% de atencion] ,
|
||
[% pendientes de resolver] ,
|
||
[Fecha]
|
||
|
||
) SELECT
|
||
[CANCELADADESGLOCEGESTIONES] +[BLOQUEADADESGLOCEGESTIONES] + [FINALIZADADESGLOCEGESTIONES]+[PENDIENTEDESGLOCEGESTIONES]
|
||
,[CANCELADADESGLOCEGESTIONES]
|
||
,[BLOQUEADADESGLOCEGESTIONES]
|
||
, [FINALIZADADESGLOCEGESTIONES]+[PENDIENTEDESGLOCEGESTIONES]
|
||
,[FINALIZADADESGLOCEGESTIONES]
|
||
,[PENDIENTEDESGLOCEGESTIONES]
|
||
,CASE
|
||
WHEN (([CANCELADADESGLOCEGESTIONES] +[BLOQUEADADESGLOCEGESTIONES] + [FINALIZADADESGLOCEGESTIONES]+[PENDIENTEDESGLOCEGESTIONES])-[CANCELADADESGLOCEGESTIONES] -[BLOQUEADADESGLOCEGESTIONES] ) = 0 THEN 0
|
||
WHEN (([CANCELADADESGLOCEGESTIONES] +[BLOQUEADADESGLOCEGESTIONES] + [FINALIZADADESGLOCEGESTIONES]+[PENDIENTEDESGLOCEGESTIONES])-[CANCELADADESGLOCEGESTIONES] -[BLOQUEADADESGLOCEGESTIONES] ) != 0
|
||
THEN
|
||
CONVERT(DECIMAL(10,2),CONVERT(DECIMAL(10,2),[FINALIZADADESGLOCEGESTIONES]) * 100 / CONVERT(DECIMAL(10,2),(([CANCELADADESGLOCEGESTIONES] +[BLOQUEADADESGLOCEGESTIONES] + [FINALIZADADESGLOCEGESTIONES]+[PENDIENTEDESGLOCEGESTIONES]) - [CANCELADADESGLOCEGESTIONES] - [BLOQUEADADESGLOCEGESTIONES]) ) )
|
||
END
|
||
, CASE
|
||
WHEN (([CANCELADADESGLOCEGESTIONES] +[BLOQUEADADESGLOCEGESTIONES] + [FINALIZADADESGLOCEGESTIONES]+[PENDIENTEDESGLOCEGESTIONES])-[CANCELADADESGLOCEGESTIONES] -[BLOQUEADADESGLOCEGESTIONES] ) = 0 THEN 0
|
||
WHEN (([CANCELADADESGLOCEGESTIONES] +[BLOQUEADADESGLOCEGESTIONES] + [FINALIZADADESGLOCEGESTIONES]+[PENDIENTEDESGLOCEGESTIONES])-[CANCELADADESGLOCEGESTIONES] -[BLOQUEADADESGLOCEGESTIONES] ) != 0
|
||
THEN
|
||
CONVERT(DECIMAL(10,2),CONVERT(DECIMAL(10,2),PENDIENTEDESGLOCEGESTIONES) * 100 / CONVERT(DECIMAL(10,2),(([CANCELADADESGLOCEGESTIONES] +[BLOQUEADADESGLOCEGESTIONES] + [FINALIZADADESGLOCEGESTIONES]+[PENDIENTEDESGLOCEGESTIONES]) - [CANCELADADESGLOCEGESTIONES] - [BLOQUEADADESGLOCEGESTIONES]) ) )
|
||
END
|
||
, FORMAT(CAST('01/'+CONVERT(varchar,[MESDESGLOCEGESTIONES] )+'/2000' as date) , 'MMM')
|
||
FROM
|
||
[SIGPC].[dbo].[t_rpt_desglocegestiones]
|
||
WHERE
|
||
[ANNODESGLOCEGESTIONES] = @Anno AND [MESDESGLOCEGESTIONES] != 0
|
||
|
||
|
||
set @query = '
|
||
SELECT
|
||
*,
|
||
CASE
|
||
WHEN Estado = ''Total en SIGDD'' THEN 1
|
||
WHEN Estado = ''Cancelada'' THEN 2
|
||
WHEN Estado = ''Bloqueada'' THEN 3
|
||
WHEN Estado = ''Ingresadas en SIGDD'' THEN 4
|
||
WHEN Estado = ''Finalizada'' THEN 5
|
||
WHEN Estado = ''Pendiente'' THEN 6
|
||
WHEN Estado = ''% de atencion'' THEN 7
|
||
WHEN Estado = ''% pendientes de resolver'' THEN 8
|
||
END AS [Order]
|
||
FROM #TablaTemporal
|
||
UNPIVOT ([Value] FOR Estado IN (
|
||
[Total en SIGDD] ,
|
||
[Cancelada] ,
|
||
[Bloqueada] ,
|
||
[Ingresadas en SIGDD] ,
|
||
[Finalizada] ,
|
||
[Pendiente],
|
||
[% de atencion] ,
|
||
[% pendientes de resolver]
|
||
|
||
|
||
)) Unp
|
||
pivot(
|
||
max([Value])
|
||
for [Fecha] IN ('+@SomeString+')
|
||
) Piv
|
||
ORDER BY [Order]
|
||
'
|
||
|
||
execute(@query)
|
||
|
||
|
||
|
||
END;
|
||
ELSE
|
||
BEGIN
|
||
|
||
SELECT '' AS 'Estado',
|
||
'' AS 'ene.',
|
||
'' AS 'feb.',
|
||
'' AS 'mar.',
|
||
'' AS 'abr.',
|
||
'' AS 'may.',
|
||
'' AS 'jun.',
|
||
'' AS 'jul.',
|
||
'' AS 'ago.',
|
||
'' AS 'sep.',
|
||
'' AS 'oct.',
|
||
'' AS 'nov.',
|
||
'' AS 'dic.',
|
||
'' AS 'Order'
|
||
FROM #TablaTemporal
|
||
END;
|
||
|
||
|
||
|
||
|
||
--EN CASO DE OCUPAR SUBIR UN ERROR PERSONALIZADO UTILIZAR EL RAISERROR
|
||
--EJEMPLO
|
||
--SELECT @ErrorMessage = 'El consecutivo ya existe en la base de datos. Consecutivo actual:' + convert( NVARCHAR(100), @CONSECUTIVODOCUMENTO)
|
||
-- SET @p_status = '99'
|
||
-- SET @p_message = @ErrorMessage
|
||
|
||
-- RAISERROR(@ErrorMessage,14,16)
|
||
|
||
COMMIT TRANSACTION --CONVIERTE LOS CAMBIOS DE LA TRANSACCION EN PARTE PERMANENTE DE LA BD Y LIBERA LOS RECURSOS POR EJEMPLO BLOQUEOS
|
||
|
||
END TRY
|
||
BEGIN CATCH
|
||
|
||
IF @@TRANCOUNT > 0
|
||
ROLLBACK TRANSACTION
|
||
|
||
SET @p_status = '99'
|
||
|
||
SET @p_message = 'Error Número: '+CONVERT(VARCHAR(10),ERROR_NUMBER())+', '+ERROR_MESSAGE()
|
||
|
||
|
||
|
||
SELECT @ErrorMessage = ERROR_MESSAGE(),
|
||
@ErrorSeverity = ERROR_SEVERITY(),
|
||
@ErrorState = ERROR_STATE();
|
||
RAISERROR (@ErrorMessage, -- Message text.
|
||
@ErrorSeverity, -- Severity.
|
||
@ErrorState -- State.
|
||
);
|
||
|
||
|
||
END CATCH
|
||
END
|
||
|