
/*tabla URG_TURNOS*/
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='URG_TURNOS')
CREATE TABLE dbo.URG_TURNOS (
  id_turno int IDENTITY(1, 1) NOT NULL,
  documento varchar(20) COLLATE Modern_Spanish_CI_AS NULL,
  tipo_doc varchar(3) COLLATE Modern_Spanish_CI_AS NULL,
  nomc varchar(120) COLLATE Modern_Spanish_CI_AS NULL,
  genero varchar(2) COLLATE Modern_Spanish_CI_AS NULL,
  fecha_nac datetime NULL,
  prioridad varchar(2) COLLATE Modern_Spanish_CI_AS NULL,
  eps varchar(30) COLLATE Modern_Spanish_CI_AS NULL,
  fectrurno datetime CONSTRAINT DF_URG_TURNOS_fectrurno DEFAULT getdate() NULL,
  fecllamado datetime NULL,
  fecfinatencion datetime NULL,
  fecanula datetime NULL,
  usuario varchar(30) COLLATE Modern_Spanish_CI_AS NULL,
  estado int CONSTRAINT DF_URG_TURNOS_estado DEFAULT 1 NULL,
  consultorio varchar(30) COLLATE Modern_Spanish_CI_AS NULL,
  llamado smallint CONSTRAINT DF_URG_TURNOS_llamado DEFAULT 0 NULL,
  conIng int NULL,
  CONSTRAINT PK_URG_TURNOS PRIMARY KEY CLUSTERED (id_turno)
    WITH (
      PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF,
      ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
ON [PRIMARY]
GO


IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Proc_ExtraeDatosCedulaV2')
BEGIN
	DROP PROCEDURE Proc_ExtraeDatosCedulaV2;
END
GO

CREATE PROCEDURE [dbo].[Proc_ExtraeDatosCedulaV2]
(
	@Documento VARCHAR(20),
	@TpDoc  CHAR(2),
	@Apellido1 VARCHAR(20),
	@Apellido2 VARCHAR(20),
	@Nombre1 VARCHAR(20),
	@Nombre2 VARCHAR(20),
	@Genero VARCHAR(2),
	@FechaNacimiento DATETIME,
	@priority CHAR(1),
    @Nit Varchar(20),
	@emp char(2),
    @sed char(9),
    @mennit Varchar(50),
    @mtucod CHAR(1),
    @mtcodp CHAR(1),
    @clapro CHAR(2),
    @pabellon CHAR(3),
	@tdate CHAR(3)
)
AS
BEGIN
	SET NOCOUNT ON;
	--DECLARE	@Nit Varchar(20)
	--DECLARE	@mennit Varchar(50)
	--DECLARE	@mtucod CHAR(1)
	--DECLARE	@mtcodp CHAR(1)
	DECLARE	@IngCsc smallint
	DECLARE @MPOrd SMALLINT
	DECLARE @MPNomc VARCHAR(80)
	DECLARE @edad smallint
	DECLARE @fecnac datetime
	
	SET @FechaNacimiento = @FechaNacimiento+' 00:00:00.000'
	set @fecnac = CAST(@FechaNacimiento as datetime)
	SET @MPNomc =  @Nombre1+' '+@Nombre2+' '+@Apellido1+' '+@Apellido2
	-- ==========================================================================================
-- SELECCIONA PRIORIDAD DE ACUERDO A LA EDAD
-- ==========================================================================================
	SELECT @edad = DATEDIFF(YEAR,@FechaNacimiento,GETDATE()) -(CASE  WHEN DATEADD(YY,DATEDIFF(YEAR,@FechaNacimiento,GETDATE()),@FechaNacimiento)>GETDATE() THEN 1 ELSE 0 END)
	IF(@edad < 12)
	BEGIN
		SET @priority = 'P'
	END
	IF(@edad > 60)
	BEGIN
		SET @priority = 'P'
	END

	-- ================================================================================	
	-- TRAE EL CONTRATO DE LA EPS DE LA TABLA URG_CONTRATOS BASE DE DATOS NUESTRA
	-- ================================================================================
	 --   SET @Nit = '111111111-1'
		--SET @mennit = 'PORCONFIRMAR' 
        
		--SET @Nit = ''
		--SET @mennit = '' 
		--SET @mtucod = '1' 
		--SET @mtcodp = 'A'
        

	-- ================================================================================
	--   INGRESA AQUI SI EL PACIENTE YA EXISTE EN HOSVITAL
	-- ================================================================================

	IF EXISTS(SELECT TOP 1 mpcedu FROM Clinica_pruebas.dbo.capbas  WHERE (mpcedu = @Documento) AND (mptdoc = @TpDoc)) 
	BEGIN
	    -- -----------------------------------------------------------------------------------------------------------------------------------------------
		-- VALIDA SI TIENE CONTRATO ASOCIADO EN LA TABLA MAEPAC, SI NO EXISTE CONTRATO CREA UNA GENERICO
		-- -----------------------------------------------------------------------------------------------------------------------------------------------
			IF NOT EXISTS (SELECT TOP 1 * FROM Clinica_pruebas.dbo.maepac WHERE (mpcedu = @documento) AND (mptdoc = @TpDoc) AND (MENNIT =@mennit))
			BEGIN
				
				-- TRAE EL ULTIMO ORDEN
				SELECT @MPOrd = MAX(MPOrd) FROM Clinica_pruebas.dbo.maepac WHERE (mpcedu = @documento) AND (mptdoc = @TpDoc)
				SET @MPOrd = @MPOrd+1

				-- CREA CONTRATO EN MAEPAC
				INSERT INTO Clinica_pruebas.dbo.maepac (mpcedu,mptdoc,mennit,mtucod,mtcodp,mpnoca,mpcuom,mpstatus,mpacmo,mpord,ultctvprx,mpresexe,mpfchafl) 
				VALUES (@documento,@TpDoc,@mennit,@mtucod,@mtcodp,'',CONVERT(VARCHAR(10), GETDATE(), @tdate),'A',0,@MPOrd,0,'',NULL)
			END 
		-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
		--	CONSULTA ULTIMO INGRESO (SE DEBE VALIDAR SI TIENE INGRESOS, SI TIENE SE TRAE EL ULTIMO INGRESO Y SE SUMA 1, SI NO SE CREA EL INGRESO CON INGRESO NRO 1
		-- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
		IF NOT EXISTS (SELECT TOP 1 IngCsc FROM Clinica_pruebas.dbo.INGRESOS WHERE MPCedu = @documento AND MPTDoc = @TpDoc order by IngCsc desc)
		BEGIN
			
			SET @IngCsc = 1
			--CREA INGRESO
			INSERT INTO Clinica_pruebas.dbo.ingresos (mpcedu,mptdoc,clapro,ingcsc,ingfecadm,ingnit,ingusrreg,ingfac,ingdoc,ingextest,ingestsld,ingips,ingfecegr,ingdxtip,ingdocresp,ingfchm,ingmedesp,ingdxtip1,ingdxtip2,inghorobs,mpcodp,ingdoaco,ingmunre,ingesmt,ingctvac,inginslc,ingcodpeg,ingnumcit,ingcscn,ingsege,inguctvep,ingfchanu,ingatnact,ingulcmop,ingtdoresp,	ingcaue,ingtip,ingcnsplt,	inghostto,ingindcap,ingfehatu,ingnroan1,ingnroan2,	ingessat,ingfsadtr,	ingindren,ingindate,ingfectur,ingriecod,ingricnde,ingricode,ingatees,inggrupo) 
			VALUES (@documento,@TpDoc,@clapro,@IngCsc,GETDATE(),@mennit,'CUE9F3Q8',0,0,0,0,0,'1753-01-01',0,0,'1753-01-01',0,0,0,0,@pabellon,0,0,1,0,'N',0,0,0,0,0,'1753-01-01',@clapro,1,'CC',0,'GN',0,0,'N','1753-01-01',0,0,0,'1753-01-01','N','N','1753-01-01',0,	0,0,9,'ND')
		END
		ELSE
		BEGIN
			
			SELECT TOP 1 @IngCsc = IngCsc FROM Clinica_pruebas.dbo.INGRESOS WHERE MPCedu = @Documento AND MPTDoc = @TpDoc order by IngCsc desc
			SET @IngCsc =  @IngCsc + 1
			--CREA INGRESO
			INSERT INTO Clinica_pruebas.dbo.ingresos (mpcedu,mptdoc,clapro,ingcsc,ingfecadm,ingnit,ingusrreg,ingfac,ingdoc,ingextest,ingestsld,ingips,ingfecegr,ingdxtip,ingdocresp,ingfchm,ingmedesp,ingdxtip1,ingdxtip2,inghorobs,mpcodp,ingdoaco,ingmunre,ingesmt,ingctvac,inginslc,ingcodpeg,ingnumcit,ingcscn,ingsege,inguctvep,ingfchanu,ingatnact,ingulcmop,ingtdoresp,	ingcaue,ingtip,ingcnsplt,	inghostto,ingindcap,ingfehatu,ingnroan1,ingnroan2,	ingessat,ingfsadtr,	ingindren,ingindate,ingfectur,ingriecod,ingricnde,ingricode,ingatees,inggrupo) 
			VALUES (@documento,@TpDoc,@clapro,@IngCsc,GETDATE(),@mennit,'CUE9F3Q8',0,0,0,0,0,'1753-01-01',0,0,'1753-01-01',0,0,0,0,@pabellon,0,0,1,0,'N',0,0,0,0,0,'1753-01-01',@clapro,1,'CC',0,'GN',0,0,'N','1753-01-01',0,0,0,'1753-01-01','N','N','1753-01-01',0,	0,0,9,'ND')
		END 
		
		-- ---------------------------------------------------------------------------------------------------
		-- CREA TEMPORAL DE FACTURACION (CREA REGISTRO EN LA TABLA TMPFAC)
		-- ---------------------------------------------------------------------------------------------------
		INSERT INTO Clinica_pruebas.dbo.tmpfac(tfcedu,tftdoc,tmctving,tfmeni,tffchi,tffces,tfhori,micodi,sofchvini,sofchacc,socodm,sofchvfin,socodmcnd,sotpoec,socodme,tfviai,tfesmi,tfesms,mpfesh,tfnore,tfests,tfcaue,tfsege,tffchm,tfhoro,tffchs,tfvnpu,tfuing,tfccodpab,tfuscp,scccod,sccemp,clapro,tfulcac,tfestp,tftotp,tftots,tfvaab,tfvapu,tfvpau,tfvdsc,tfuscs,tfucsa,tfucsn,tftpeaut,tfvlraut,tfccodcns,tfvlrimpt,tfcntqx,refatmat,refatnum,tmctvact,tfdocrep,tfipsent,tfdocaco,tfmunres,tfesmt,tfaperes,claproi,tfnore2,tfaperes2,tfvpoco,tfvlrtiv)
		VALUES(@documento,@TpDoc,@IngCsc,@mennit,CONVERT(char(10), GETDATE(), @tdate),CONVERT(char(10), GETDATE(), @tdate),CONVERT(char(8), GETDATE(), 108),0,'1753-01-01',	'1753-01-01',0,'1753-01-01',0,0,0,0,0,0,'1753-01-01','.',0,0,0,'1753-01-01',0,'1753-01-01',0,'CUE9F3Q8',@pabellon,0,@sed,@emp,@clapro,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,'.',@clapro,'.','.',0,0)
		

	END -- FIN IF CUANDO SI EXISTE PACIENTE



	-- ==================================================================================================================================
	--   DE OTRO MODO INGRESA AQUI SI EL PACIENTE NO EXISTE EN HOSVITAL
	-- ===================================================================================================================================
	ELSE
	BEGIN
		SET @IngCsc = 1
		-- ----------------------
		-- CREA PACIENTE
		-- ---------------------
		INSERT INTO Clinica_pruebas.dbo.capbas (mpcedu,mptdoc,mpnom1,mpnom2,mpape1,mpape2,mpfchn,mpsexo,mdcodd,mdcodm,mdcodb,mpestc,mpnhic,mpucod,mpfcha,mpnomc,mpfalta,mptmpres,mppstnuc,mpnivedu,mpingmen,mpdocint,mplgexp, mpctvoacte,mpctvoatn,mpultctpr,mpusrprf,mppacnn,mpctvmed,mpsemcsis,mpnivees,mpsmctcm,mptipafi,mpcalafi, mdcodmnac,mdcoddnac,mpestpac,mpbeips,mdcodbe,mpctvges,mpfchdef,mpcscinm,mpconnac,mpvives,mpocuante,mpfotpac,mpcodsegtr,mpcodtra,mpindder,mpindizq,mpfecact) 
		VALUES (@Documento,@TpDoc,@Nombre1,@Nombre2,@Apellido1,@Apellido2,@fecnac,@Genero,76,1,1,'S',@Documento,'CUE9F3Q8',GETDATE(),@MPNomc,0,0,0,1,0,0,'',0,2,0,'N','N',0,0,'C',0,0,0,1,76,'S',0,0,0,'1753-01-01',0,0,0,0,CONVERT(VARBINARY, '0x'),0,0,CONVERT(VARBINARY, '0x'),CONVERT(VARBINARY, '0x'),'1753-01-01')
		

		-- ------------------------
		-- CREA CONTRATO
		-- -----------------------
		INSERT INTO Clinica_pruebas.dbo.maepac (mpcedu,mptdoc,mennit,mtucod,mtcodp,mpnoca,mpcuom,mpstatus,mpacmo,mpord,ultctvprx,mpresexe,mpfchafl) 
		VALUES (@documento,@TpDoc,@mennit,@mtucod,@mtcodp,'',CONVERT(VARCHAR(10), GETDATE(), @tdate),'A',0,1,0,'',NULL)
		
		
		-- ----------------------
		-- CREA INGRESO
		-- ---------------------
		INSERT INTO Clinica_pruebas.dbo.ingresos (mpcedu,mptdoc,clapro,ingcsc,ingfecadm,ingnit,ingusrreg,ingfac,ingdoc,ingextest,ingestsld,ingips,ingfecegr,ingdxtip,ingdocresp,ingfchm,ingmedesp,ingdxtip1,ingdxtip2,inghorobs,mpcodp,ingdoaco,ingmunre,ingesmt,ingctvac,inginslc,ingcodpeg,ingnumcit,ingcscn,ingsege,inguctvep,ingfchanu,ingatnact,ingulcmop,ingtdoresp,	ingcaue,ingtip,ingcnsplt,	inghostto,ingindcap,ingfehatu,ingnroan1,ingnroan2,	ingessat,ingfsadtr,	ingindren,ingindate,ingfectur,ingriecod,ingricnde,ingricode,ingatees,inggrupo) 
		VALUES (@documento,@TpDoc,@clapro,1,GETDATE(),@mennit,'CUE9F3Q8',0,0,0,0,0,'1753-01-01',0,0,'1753-01-01',0,0,0,0,@pabellon,0,0,1,0,'N',0,0,0,0,0,'1753-01-01',@clapro,1,'CC',0,'GN',0,0,'N','1753-01-01',0,0,0,'1753-01-01','N','N','1753-01-01',0,0,0,9,'ND')
		
		
		-- ---------------------------------------------------------------------------------------------------
		--CREA TEMPORAL DE FACTURACION (CREA REGISTRO EN LA TABLA MAEPAC)
		-- --------------------------------------------------------------------------------------------------
		INSERT INTO Clinica_pruebas.dbo.tmpfac(tfcedu,tftdoc,tmctving,tfmeni,tffchi,tffces,tfhori,micodi,sofchvini,sofchacc,socodm,sofchvfin,socodmcnd,sotpoec,socodme,tfviai,tfesmi,tfesms,mpfesh,tfnore,tfests,tfcaue,tfsege,tffchm,tfhoro,tffchs,tfvnpu,tfuing,tfccodpab,tfuscp,scccod,sccemp,clapro,tfulcac,tfestp,tftotp,tftots,tfvaab,tfvapu,tfvpau,tfvdsc,tfuscs,tfucsa,tfucsn,tftpeaut,tfvlraut,tfccodcns,tfvlrimpt,tfcntqx,refatmat,refatnum,tmctvact,tfdocrep,tfipsent,tfdocaco,tfmunres,tfesmt,tfaperes,claproi,tfnore2,tfaperes2,tfvpoco,tfvlrtiv)
		VALUES(@documento,@TpDoc,1,@mennit,CONVERT(char(10), GETDATE(), @tdate),CONVERT(char(10), GETDATE(), @tdate),CONVERT(char(8), GETDATE(), 108),0,'1753-01-01',	'1753-01-01',0,'1753-01-01',0,0,0,0,0,0,'1753-01-01','.',0,0,0,'1753-01-01',0,'1753-01-01',0,'CUE9F3Q8',@pabellon,0,@sed,@emp,@clapro,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,'.',@clapro,'.','.',0,0)
		
	END -- FIN ELSE CUANDO NO EXISTE PACIENTE 

	-- ================================================================================	
	--  INSERTA DATOS EN LA TABLA URG_TURNOS
	-- ================================================================================		
	INSERT INTO [dbo].[URG_TURNOS]  ([documento],[tipo_doc],[nomc],[genero],[fecha_nac],[prioridad],[eps],[conIng]) 
	VALUES(@documento,@TpDoc,@MPNomc,@Genero,@fecnac,@priority,@Nit,@IngCsc)

	-- ===============================================================================
	-- VALIDACION DE DATOS
	-- ===============================================================================
	--SELECT
	--			@documento AS Cedula,	
	--			@Apellido1+' '+@Apellido2+' '+@Nombre1+' '+@Nombre2 AS Nombre,
	--			@Genero AS Genero,
	--			@FecNac AS Fecha_Nacimiento,
	--			@TpDoc AS Tipo_Documento,
	--			@priority AS Prioridad,
	--			@Nit AS NIT,
	--			@mennit AS MENNIT,
	--			@mtucod AS mtucod,
	--			@mtcodp AS mtcodp,
	--			@MPOrd as Orden_Contrato,
	--			@edad as edad,
	--			@MPNomc as nombre_completo

END -- FIN PROCEDIMIENTO ALMACENADO



IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Proc_validaIngreso')
BEGIN
	DROP PROCEDURE Proc_validaIngreso;
END
GO

CREATE PROCEDURE [dbo].Proc_validaIngreso
(
	@doc varchar(20),
	@tpdoc varchar(3)
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ingcsc AS INT
DECLARE @fchsal AS DATETIME

   SELECT TOP 1 
   @ingcsc = MAX(ingcsc),  
   @fchsal = CONVERT(VARCHAR(10), ingfecegr, 103) 
   FROM HVT_OSPEDALE.dbo.INGRESOS 
   WHERE MPcedu = @doc AND MPTDoc = @tpdoc
   --WHERE MPcedu = '16549606' AND MPTDoc = 'CC'
   GROUP BY  ingfecegr ORDER BY 1 DESC
   

	IF (@fchsal = '01/01/1753')
	BEGIN
		SELECT '1' AS Existe
	END
	ELSE
	BEGIN
		SELECT '0' AS Existe
	END
END
GO

