--			BEGIN Creare si populare tabel SetariApp
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SetariApp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SetariApp]
GO

CREATE TABLE [dbo].[SetariApp] (
	[IdSetare] [int] NOT NULL ,
	[DenSetare] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Valoare] [bit] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SetariApp] WITH NOCHECK ADD 
	CONSTRAINT [PK_SetariApp] PRIMARY KEY  CLUSTERED 
	(
		[IdSetare]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[SetariApp] ADD 
	CONSTRAINT [DF_SetariApp_Valoare] DEFAULT (0) FOR [Valoare]
GO


Insert dbo.SetariApp(IdSetare, DenSetare, Valoare)
Values(1, 'FOCG_FiltrareUser', 0)

GO
Print 'Creare si actualizare tabel SetariApp'
--			  End Creare si populare tabel SetariApp

--                       	BEGIN Actualizare TRIGGER
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateModificari_DS___Insert]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[UpdateModificari_DS___Insert]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateModificari_DS___Update]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[UpdateModificari_DS___Update]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateModificari_DS___Delete]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[UpdateModificari_DS___Delete]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateModificari]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[UpdateModificari]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateModificari_Proceduri___Delete]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[UpdateModificari_Proceduri___Delete]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateModificari_Proceduri___Insert]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[UpdateModificari_Proceduri___Insert]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateModificari_Proceduri___Update]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[UpdateModificari_Proceduri___Update]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateModificari_Sectii___Delete]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[UpdateModificari_Sectii___Delete]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateModificari_Sectii___Insert]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[UpdateModificari_Sectii___Insert]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateModificari_Sectii___Update]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[UpdateModificari_Sectii___Update]
GO


SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE TRIGGER UpdateModificari_DS___Insert ON dbo.Pacient_DiagnosticeSecundare

FOR INSERT

AS

DECLARE @comments VarChar(255), @PermitLog bit

Select 	@PermitLog=a.Flag_Finalizat 
From 	dbo.Pacient_Fisa a Inner Join dbo.Pacient_DiagnosticeSecundare b On a.IdPacientFisa=b.IdPacientFisa Inner join Inserted c On b.IdPacientDiagnosticSecundar=c.IdPacientDiagnosticSecundar

If @PermitLog=1
   --memorare log IdDiagnosticSecundar
   Begin	
	SELECT @comments='IdDiagnosticSecundar'
	
	INSERT INTO 	dbo.Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela, IdLinie)
	   SELECT 	b.IdUtilizator, a.IdPacientFisa, @comments, Null, a.IdDiagnosticSecundar, 'I', 'Pacient_DiagnosticeSecundare', a.IdPacientDiagnosticSecundar
	   FROM 	Inserted a Inner Join dbo.Pacient_Fisa b On a.IdPacientFisa=b.IdPacientFisa
   End



















GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE TRIGGER UpdateModificari_DS___Update ON dbo.Pacient_DiagnosticeSecundare

FOR UPDATE

AS

DECLARE @comments VarChar(255), @PermitLog bit

Select 	@PermitLog=a.Flag_Finalizat 
From 	dbo.Pacient_Fisa a Inner Join dbo.Pacient_DiagnosticeSecundare b On a.IdPacientFisa=b.IdPacientFisa Inner join Inserted c On b.IdPacientDiagnosticSecundar=c.IdPacientDiagnosticSecundar

If @PermitLog=1
   Begin
	--memorare log IdDiagnosticSecundar
	IF UPDATE(IdDiagnosticSecundar)
	   Begin	
		SELECT @comments='IdDiagnosticSecundar'
		
		INSERT INTO 	dbo.Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela, IdLinie)
		   SELECT 	c.IdUtilizator, a.IdPacientFisa, @comments, b.IdDiagnosticSecundar, a.IdDiagnosticSecundar, 'U', 'Pacient_DiagnosticeSecundare', a.IdPacientDiagnosticSecundar
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientDiagnosticSecundar=b.IdPacientDiagnosticSecundar
				Inner Join dbo.Pacient_Fisa c On a.IdPacientFisa=c.IdPacientFisa
		   WHERE  	a.IdDiagnosticSecundar<>b.IdDiagnosticSecundar
	   End


   End

















GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE TRIGGER UpdateModificari_DS___Delete ON dbo.Pacient_DiagnosticeSecundare

FOR DELETE

AS

DECLARE @comments VarChar(255), @PermitLog bit

Select 	@PermitLog=a.Flag_Finalizat 
From 	dbo.Pacient_Fisa a Inner join Deleted b On a.IdPacientFisa=b.IdPacientFisa

 
If @PermitLog=1
   --memorare log IdDiagnosticSecundar
   Begin	
	SELECT @comments='IdDiagnosticSecundar'
	
	INSERT INTO 	dbo.Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela, IdLinie)
	   SELECT 	b.IdUtilizator, a.IdPacientFisa, @comments, a.IdDiagnosticSecundar, Null, 'D', 'Pacient_DiagnosticeSecundare', a.IdPacientDiagnosticSecundar
	   FROM 	Deleted a Inner Join dbo.Pacient_Fisa b On a.IdPacientFisa=b.IdPacientFisa
   End
























GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE TRIGGER UpdateModificari ON dbo.Pacient_Fisa

FOR UPDATE

AS

DECLARE @comments VarChar(255), @PermitLog bit

Select @PermitLog=a.Flag_Finalizat From dbo.Pacient_Fisa a Inner join Inserted b On a.IdPacientFisa=b.IdPacientFisa

If @PermitLog=1
   Begin
	--memorare log Caz_ID
	IF UPDATE(Caz_ID)
	   Begin	
		SELECT @comments='Caz_ID'
		
		INSERT INTO 	Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela)
		   SELECT 	a.IdUtilizator, a.IdPacientFisa, @comments, b.Caz_ID, a.Caz_ID, 'U', 'Pacient_Fisa'
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientFisa=b.IdPacientFisa
		   WHERE  	a.Caz_ID<>b.Caz_ID
	   End

	--memorare log IdAccident
	IF UPDATE(IdAccident)
	   Begin	
		SELECT @comments='IdAccident'
		
		INSERT INTO 	Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela)
		   SELECT 	a.IdUtilizator, a.IdPacientFisa, @comments, b.IdAccident, a.IdAccident, 'U', 'Pacient_Fisa'
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientFisa=b.IdPacientFisa
		   WHERE  	a.IdAccident<>b.IdAccident
	   End

	--memorare log IdSpital
	IF UPDATE(IdSpital)
	   Begin	
		SELECT @comments='IdSpital'
		
		INSERT INTO 	Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela)
		   SELECT 	a.IdUtilizator, a.IdPacientFisa, @comments, b.IdSpital, a.IdSpital, 'U', 'Pacient_Fisa'
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientFisa=b.IdPacientFisa
		   WHERE  	a.IdSpital<>b.IdSpital
	   End

	--memorare log IdSectie
	IF UPDATE(IdSectie)
	   Begin	
		SELECT @comments='IdSectie'
		
		INSERT INTO 	Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela)
		   SELECT 	a.IdUtilizator, a.IdPacientFisa, @comments, b.IdSectie, a.IdSectie, 'U', 'Pacient_Fisa'
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientFisa=b.IdPacientFisa
		   WHERE  	a.IdSectie<>b.IdSectie
	   End

	--memorare log NumarSectie
	IF UPDATE(NumarSectie)
	   Begin	
		SELECT @comments='NumarSectie'
		
		INSERT INTO 	Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela)
		   SELECT 	a.IdUtilizator, a.IdPacientFisa, @comments, b.NumarSectie, a.NumarSectie, 'U', 'Pacient_Fisa'
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientFisa=b.IdPacientFisa
		   WHERE  	a.NumarSectie<>b.NumarSectie
	   End

	--memorare log NFO
	IF UPDATE(NFO)
	   Begin	
		SELECT @comments='NFO'
		
		INSERT INTO 	Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela)
		   SELECT 	a.IdUtilizator, a.IdPacientFisa, @comments, b.NFO, a.NFO, 'U', 'Pacient_Fisa'
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientFisa=b.IdPacientFisa
		   WHERE  	a.NFO<>b.NFO
	   End

	--memorare log Nume
	IF UPDATE(Nume)
	   Begin	
		SELECT @comments='Nume'
		
		INSERT INTO 	Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela)
		   SELECT 	a.IdUtilizator, a.IdPacientFisa, @comments, b.Nume, a.Nume, 'U', 'Pacient_Fisa'
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientFisa=b.IdPacientFisa
		   WHERE  	IsNull(a.Nume,'')<>IsNull(b.Nume,'')
	   End


	--memorare log Prenume
	IF UPDATE(Prenume)
	   Begin	
		SELECT @comments='Prenume'
		
		INSERT INTO 	Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela)
		   SELECT 	a.IdUtilizator, a.IdPacientFisa, @comments, b.Prenume, a.Prenume, 'U', 'Pacient_Fisa'
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientFisa=b.IdPacientFisa
		   WHERE  	IsNull(a.Prenume,'')<>IsNull(b.Prenume,'')
	   End
	
	--memorare log CNP
	IF UPDATE(CNP)
	   Begin	
		SELECT @comments='CNP'
		
		INSERT INTO 	Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela)
		   SELECT 	a.IdUtilizator, a.IdPacientFisa, @comments, b.CNP, a.CNP, 'U', 'Pacient_Fisa'
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientFisa=b.IdPacientFisa
		   WHERE  	IsNull(a.CNP,'')<>IsNull(b.CNP,'')
	   End

	--memorare log IdJudet
	IF UPDATE(IdJudet)
	   Begin	
		SELECT @comments='IdJudet'
		
		INSERT INTO 	Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela)
		   SELECT 	a.IdUtilizator, a.IdPacientFisa, @comments, b.IdJudet, a.IdJudet, 'U', 'Pacient_Fisa'
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientFisa=b.IdPacientFisa
		   WHERE  	a.IdJudet<>b.IdJudet
	   End

	--memorare log IdLocalitate
	IF UPDATE(IdLocalitate)
	   Begin	
		SELECT @comments='IdLocalitate'
		
		INSERT INTO 	Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela)
		   SELECT 	a.IdUtilizator, a.IdPacientFisa, @comments, b.IdLocalitate, a.IdLocalitate, 'U', 'Pacient_Fisa'
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientFisa=b.IdPacientFisa
		   WHERE  	a.IdLocalitate<>b.IdLocalitate
	   End


	--memorare log DataNastere
	IF UPDATE(DataNastere)
	   Begin	
		SELECT @comments='DataNastere'
		
		INSERT INTO 	Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela)
		   SELECT 	a.IdUtilizator, a.IdPacientFisa, @comments, b.DataNastere, a.DataNastere, 'U', 'Pacient_Fisa'
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientFisa=b.IdPacientFisa
		   WHERE  	a.DataNastere<>b.DataNastere
	   End

	--memorare log DataInternare
	IF UPDATE(DataInternare)
	   Begin	
		SELECT @comments='DataInternare'
		
		INSERT INTO 	Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela)
		   SELECT 	a.IdUtilizator, a.IdPacientFisa, @comments, b.DataInternare, a.DataInternare, 'U', 'Pacient_Fisa'
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientFisa=b.IdPacientFisa
		   WHERE  	a.DataInternare<>b.DataInternare
	   End

	--memorare log OraInternare
	IF UPDATE(OraInternare)
	   Begin	
		SELECT @comments='OraInternare'
		
		INSERT INTO 	Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela)
		   SELECT 	a.IdUtilizator, a.IdPacientFisa, @comments, b.OraInternare, a.OraInternare, 'U', 'Pacient_Fisa'
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientFisa=b.IdPacientFisa
		   WHERE  	a.OraInternare<>b.OraInternare
	   End

	--memorare log DataExternare
	IF UPDATE(DataExternare)
	   Begin	
		SELECT @comments='DataExternare'
		
		INSERT INTO 	Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela)
		   SELECT 	a.IdUtilizator, a.IdPacientFisa, @comments, b.DataExternare, a.DataExternare, 'U', 'Pacient_Fisa'
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientFisa=b.IdPacientFisa
		   WHERE  	a.DataExternare<>b.DataExternare
	   End

	--memorare log OraExternare
	IF UPDATE(OraExternare)
	   Begin	
		SELECT @comments='OraExternare'
		
		INSERT INTO 	Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela)
		   SELECT 	a.IdUtilizator, a.IdPacientFisa, @comments, b.OraExternare, a.OraExternare, 'U', 'Pacient_Fisa'
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientFisa=b.IdPacientFisa
		   WHERE  	a.OraExternare<>b.OraExternare
	   End

	--memorare log IdTipAsigurare
	IF UPDATE(IdTipAsigurare)
	   Begin	
		SELECT @comments='IdTipAsigurare'
		
		INSERT INTO 	Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela)
		   SELECT 	a.IdUtilizator, a.IdPacientFisa, @comments, b.IdTipAsigurare, a.IdTipAsigurare, 'U', 'Pacient_Fisa'
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientFisa=b.IdPacientFisa
		   WHERE  	a.IdTipAsigurare<>b.IdTipAsigurare
	   End

	--memorare log IdCAS
	IF UPDATE(IdCAS)
	   Begin	
		SELECT @comments='IdCAS'
		
		INSERT INTO 	Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela)
		   SELECT 	a.IdUtilizator, a.IdPacientFisa, @comments, b.IdCAS, a.IdCAS, 'U', 'Pacient_Fisa'
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientFisa=b.IdPacientFisa
		   WHERE  	IsNull(a.IdCAS,0)<>IsNull(b.IdCAS,0)
	   End

	--memorare log IdJudetCAS
	IF UPDATE(IdJudetCAS)
	   Begin	
		SELECT @comments='IdJudetCAS'
		
		INSERT INTO 	Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela)
		   SELECT 	a.IdUtilizator, a.IdPacientFisa, @comments, b.IdJudetCAS, a.IdJudetCAS, 'U', 'Pacient_Fisa'
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientFisa=b.IdPacientFisa
		   WHERE  	IsNull(a.IdJudetCAS,0)<>IsNull(b.IdJudetCAS,0)
	   End

	--memorare log Sex
	IF UPDATE(Sex)
	   Begin	
		SELECT @comments='Sex'
		
		INSERT INTO 	Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela)
		   SELECT 	a.IdUtilizator, a.IdPacientFisa, @comments, b.Sex, a.Sex, 'U', 'Pacient_Fisa'
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientFisa=b.IdPacientFisa
		   WHERE  	a.Sex<>b.Sex
	   End

	--memorare log GreutateNastere
	IF UPDATE(GreutateNastere)
	   Begin	
		SELECT @comments='GreutateNastere'
		
		INSERT INTO 	Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela)
		   SELECT 	a.IdUtilizator, a.IdPacientFisa, @comments, b.GreutateNastere, a.GreutateNastere, 'U', 'Pacient_Fisa'
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientFisa=b.IdPacientFisa
		   WHERE  	a.GreutateNastere<>b.GreutateNastere
	   End

	--memorare log IdTipInternare
	IF UPDATE(IdTipInternare)
	   Begin	
		SELECT @comments='IdTipInternare'
		
		INSERT INTO 	Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela)
		   SELECT 	a.IdUtilizator, a.IdPacientFisa, @comments, b.IdTipInternare, a.IdTipInternare, 'U', 'Pacient_Fisa'
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientFisa=b.IdPacientFisa
		   WHERE  	a.IdTipInternare<>b.IdTipInternare
	   End

	--memorare log IdTipExternare
	IF UPDATE(IdTipExternare)
	   Begin	
		SELECT @comments='IdTipExternare'
		
		INSERT INTO 	Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela)
		   SELECT 	a.IdUtilizator, a.IdPacientFisa, @comments, b.IdTipExternare, a.IdTipExternare, 'U', 'Pacient_Fisa'
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientFisa=b.IdPacientFisa
		   WHERE  	a.IdTipExternare<>b.IdTipExternare
	   End

	--memorare log IdStareExternare
	IF UPDATE(IdStareExternare)
	   Begin	
		SELECT @comments='IdStareExternare'
		
		INSERT INTO 	Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela)
		   SELECT 	a.IdUtilizator, a.IdPacientFisa, @comments, b.IdStareExternare, a.IdStareExternare, 'U', 'Pacient_Fisa'
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientFisa=b.IdPacientFisa
		   WHERE  	a.IdStareExternare<>b.IdStareExternare
	   End

	--memorare log IdMedic
	IF UPDATE(IdMedic)
	   Begin	
		SELECT @comments='IdMedic'
		
		INSERT INTO 	Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela)
		   SELECT 	a.IdUtilizator, a.IdPacientFisa, @comments, b.IdMedic, a.IdMedic, 'U', 'Pacient_Fisa'
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientFisa=b.IdPacientFisa
		   WHERE  	a.IdMedic<>b.IdMedic
	   End

	--memorare log IdMedicOperator
	IF UPDATE(IdMedicOperator)
	   Begin	
		SELECT @comments='IdMedicOperator'
		
		INSERT INTO 	Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela)
		   SELECT 	a.IdUtilizator, a.IdPacientFisa, @comments, b.IdMedicOperator, a.IdMedicOperator, 'U', 'Pacient_Fisa'
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientFisa=b.IdPacientFisa
		   WHERE  	IsNull(a.IdMedicOperator,0)<>IsNull(b.IdMedicOperator,0)
	   End

	--memorare log IdMedicMana2
	IF UPDATE(IdMedicMana2)
	   Begin	
		SELECT @comments='IdMedicMana2'
		
		INSERT INTO 	Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela)
		   SELECT 	a.IdUtilizator, a.IdPacientFisa, @comments, b.IdMedicMana2, a.IdMedicMana2, 'U', 'Pacient_Fisa'
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientFisa=b.IdPacientFisa
		   WHERE  	IsNull(a.IdMedicMana2,0)<>IsNull(b.IdMedicMana2,0)
	   End

	--memorare log IdDiagnosticLaInternare
	IF UPDATE(IdDiagnosticLaInternare)
	   Begin	
		SELECT @comments='IdDiagnosticLaInternare'
		
		INSERT INTO 	Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela)
		   SELECT 	a.IdUtilizator, a.IdPacientFisa, @comments, b.IdDiagnosticLaInternare, a.IdDiagnosticLaInternare, 'U', 'Pacient_Fisa'
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientFisa=b.IdPacientFisa
		   WHERE  	IsNull(a.IdDiagnosticLaInternare,0)<>IsNull(b.IdDiagnosticLaInternare,0)
	   End

	--memorare log IdDiagnosticPrincipalCod1
	IF UPDATE(IdDiagnosticPrincipalCod1)
	   Begin	
		SELECT @comments='IdDiagnosticPrincipalCod1'
		
		INSERT INTO 	Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela)
		   SELECT 	a.IdUtilizator, a.IdPacientFisa, @comments, b.IdDiagnosticPrincipalCod1, a.IdDiagnosticPrincipalCod1, 'U', 'Pacient_Fisa'
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientFisa=b.IdPacientFisa
		   WHERE  	a.IdDiagnosticPrincipalCod1<>b.IdDiagnosticPrincipalCod1
	   End

	--memorare log IdDiagnosticPrincipalCod2
	IF UPDATE(IdDiagnosticPrincipalCod2)
	   Begin	
		SELECT @comments='IdDiagnosticPrincipalCod2'
		
		INSERT INTO 	Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela)
		   SELECT 	a.IdUtilizator, a.IdPacientFisa, @comments, b.IdDiagnosticPrincipalCod2, a.IdDiagnosticPrincipalCod2, 'U', 'Pacient_Fisa'
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientFisa=b.IdPacientFisa
		   WHERE  	IsNull(a.IdDiagnosticPrincipalCod2,0)<>IsNull(b.IdDiagnosticPrincipalCod2,0)
	   End

	--memorare log IdInterventieChirurgicala
	IF UPDATE(IdInterventieChirurgicala)
	   Begin	
		SELECT @comments='IdInterventieChirurgicala'
		
		INSERT INTO 	Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela)
		   SELECT 	a.IdUtilizator, a.IdPacientFisa, @comments, b.IdInterventieChirurgicala, a.IdInterventieChirurgicala, 'U', 'Pacient_Fisa'
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientFisa=b.IdPacientFisa
		   WHERE  	IsNull(a.IdInterventieChirurgicala,0)<>IsNull(b.IdInterventieChirurgicala,0)
	   End

	--memorare log DataInterventieChirurgicalaPrincipala
	IF UPDATE(DataInterventieChirurgicalaPrincipala)
	   Begin	
		SELECT @comments='DataInterventieChirurgicalaPrincipala'
		
		INSERT INTO 	Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela)
		   SELECT 	a.IdUtilizator, a.IdPacientFisa, @comments, b.DataInterventieChirurgicalaPrincipala, a.DataInterventieChirurgicalaPrincipala, 'U', 'Pacient_Fisa'
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientFisa=b.IdPacientFisa
		   WHERE  	IsNull(a.DataInterventieChirurgicalaPrincipala,'01/01/1900')<>IsNull(b.DataInterventieChirurgicalaPrincipala,'01/01/1900')
	   End

	--memorare log IdStareRecord
	IF UPDATE(IdStareRecord)
	   Begin	
		SELECT @comments='IdStareRecord'
		
		INSERT INTO 	Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela)
		   SELECT 	a.IdUtilizator, a.IdPacientFisa, @comments, b.IdStareRecord, a.IdStareRecord, 'U', 'Pacient_Fisa'
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientFisa=b.IdPacientFisa
		   WHERE  	a.IdStareRecord<>b.IdStareRecord
	   End

	--memorare log IdUtilizator
	IF UPDATE(IdUtilizator)
	   Begin	
		SELECT @comments='IdUtilizator'
		
		INSERT INTO 	Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela)
		   SELECT 	a.IdUtilizator, a.IdPacientFisa, @comments, b.IdUtilizator, a.IdUtilizator, 'U', 'Pacient_Fisa'
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientFisa=b.IdPacientFisa
		   WHERE  	a.IdUtilizator<>b.IdUtilizator
	   End

	--memorare log DataOperare, DataTrimitere, DataImport, Stamp: NU MA INTERESEAZA

   End





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE TRIGGER UpdateModificari_Proceduri___Delete ON dbo.Pacient_Proceduri

FOR DELETE

AS

DECLARE @comments VarChar(255), @PermitLog bit

Select 	@PermitLog=a.Flag_Finalizat From dbo.Pacient_Fisa a Inner join Deleted b On a.IdPacientFisa=b.IdPacientFisa

 
If @PermitLog=1
   Begin	
	--memorare log IdProcedura
	SELECT @comments='IdProcedura'
	
	INSERT INTO 	dbo.Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela, IdLinie)
	   SELECT 	b.IdUtilizator, a.IdPacientFisa, @comments, a.IdProcedura, Null, 'D', 'Pacient_Proceduri', a.IdPacientProcedura
	   FROM 	Deleted a Inner Join dbo.Pacient_Fisa b On a.IdPacientFisa=b.IdPacientFisa

	--memorare log IdProcedura
	SELECT @comments='Cantitate'
	
	INSERT INTO 	dbo.Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela, IdLinie)
	   SELECT 	b.IdUtilizator, a.IdPacientFisa, @comments, a.Cantitate, Null, 'D', 'Pacient_Proceduri', a.IdPacientProcedura
	   FROM 	Deleted a Inner Join dbo.Pacient_Fisa b On a.IdPacientFisa=b.IdPacientFisa
   End



























GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE TRIGGER UpdateModificari_Proceduri___Insert ON dbo.Pacient_Proceduri

FOR INSERT

AS

DECLARE @comments VarChar(255), @PermitLog bit

Select 	@PermitLog=a.Flag_Finalizat From dbo.Pacient_Fisa a Inner join Inserted b On a.IdPacientFisa=b.IdPacientFisa

If @PermitLog=1
   Begin	
	 --memorare log IdProcedura
	SELECT @comments='IdProcedura'
	
	INSERT INTO 	dbo.Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela, IdLinie)
	   SELECT 	b.IdUtilizator, a.IdPacientFisa, @comments, Null, a.IdProcedura, 'I', 'Pacient_Proceduri', a.IdPacientProcedura
	   FROM 	Inserted a Inner Join dbo.Pacient_Fisa b On a.IdPacientFisa=b.IdPacientFisa

	 --memorare log Cantitate
	SELECT @comments='Cantitate'
	
	INSERT INTO 	dbo.Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela, IdLinie)
	   SELECT 	b.IdUtilizator, a.IdPacientFisa, @comments, Null, a.Cantitate, 'I', 'Pacient_Proceduri', a.IdPacientProcedura
	   FROM 	Inserted a Inner Join dbo.Pacient_Fisa b On a.IdPacientFisa=b.IdPacientFisa
   End






















GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE TRIGGER UpdateModificari_Proceduri___Update ON dbo.Pacient_Proceduri

FOR UPDATE

AS

DECLARE @comments VarChar(255), @PermitLog bit

Select 	@PermitLog=a.Flag_Finalizat From dbo.Pacient_Fisa a Inner Join Inserted b On a.IdPacientFisa=b.IdPacientFisa

If @PermitLog=1
   Begin
	--memorare log IdProcedura
	IF UPDATE(IdProcedura)
	   Begin	
		SELECT @comments='IdProcedura'
		
		INSERT INTO 	dbo.Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela, IdLinie)
		   SELECT 	c.IdUtilizator, a.IdPacientFisa, @comments, b.IdProcedura, a.IdProcedura, 'U', 'Pacient_Procedura', a.IdPacientProcedura
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientProcedura=b.IdPacientProcedura
				Inner Join dbo.Pacient_Fisa c On a.IdPacientFisa=c.IdPacientFisa
		   WHERE  	a.IdProcedura<>b.IdProcedura
	   End

	--memorare log Cantitate
	IF UPDATE(Cantitate)
	   Begin	
		SELECT @comments='Cantitate'
		
		INSERT INTO 	dbo.Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela, IdLinie)
		   SELECT 	c.IdUtilizator, a.IdPacientFisa, @comments, b.Cantitate, a.Cantitate, 'U', 'Pacient_Procedura', a.IdPacientProcedura
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientProcedura=b.IdPacientProcedura
				Inner Join dbo.Pacient_Fisa c On a.IdPacientFisa=c.IdPacientFisa
		   WHERE  	a.Cantitate<>b.Cantitate
	   End
   End

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE TRIGGER UpdateModificari_Sectii___Delete ON dbo.Pacient_Sectii

FOR DELETE

AS

DECLARE @comments VarChar(255), @PermitLog bit

Select 	@PermitLog=a.Flag_Finalizat 
From 	dbo.Pacient_Fisa a Inner join Deleted b On a.IdPacientFisa=b.IdPacientFisa

 
If @PermitLog=1
   Begin	
	--memorare log IdSectie
	SELECT @comments='IdSectie'
	INSERT INTO 	dbo.Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela, IdLinie)
	   SELECT 	b.IdUtilizator, a.IdPacientFisa, @comments, a.IdSectie, Null, 'D', 'Pacient_Sectii', a.IdPacientSectie
	   FROM 	Deleted a Inner Join dbo.Pacient_Fisa b On a.IdPacientFisa=b.IdPacientFisa


	--memorare log NumarSectie
	SELECT @comments='NumarSectie'
	INSERT INTO 	dbo.Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela, IdLinie)
	   SELECT 	b.IdUtilizator, a.IdPacientFisa, @comments, a.NumarSectie, Null, 'D', 'Pacient_Sectii', a.IdPacientSectie
	   FROM 	Deleted a Inner Join dbo.Pacient_Fisa b On a.IdPacientFisa=b.IdPacientFisa

	--memorare log DataInternare
	SELECT @comments='DataInternare'
	INSERT INTO 	dbo.Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela, IdLinie)
	   SELECT 	b.IdUtilizator, a.IdPacientFisa, @comments, a.DataInternare, Null, 'D', 'Pacient_Sectii', a.IdPacientSectie
	   FROM 	Deleted a Inner Join dbo.Pacient_Fisa b On a.IdPacientFisa=b.IdPacientFisa

	--memorare log DataExternare
	SELECT @comments='DataExternare'
	INSERT INTO 	dbo.Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela, IdLinie)
	   SELECT 	b.IdUtilizator, a.IdPacientFisa, @comments, a.DataExternare, Null, 'D', 'Pacient_Sectii', a.IdPacientSectie
	   FROM 	Deleted a Inner Join dbo.Pacient_Fisa b On a.IdPacientFisa=b.IdPacientFisa

	--memorare log NrZile
	SELECT @comments='NrZile'
	INSERT INTO 	dbo.Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela, IdLinie)
	   SELECT 	b.IdUtilizator, a.IdPacientFisa, @comments, a.NrZile, Null, 'D', 'Pacient_Sectii', a.IdPacientSectie
	   FROM 	Deleted a Inner Join dbo.Pacient_Fisa b On a.IdPacientFisa=b.IdPacientFisa
   End


























GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE TRIGGER UpdateModificari_Sectii___Insert ON dbo.Pacient_Sectii

FOR INSERT

AS

DECLARE @comments VarChar(255), @PermitLog bit

Select 	@PermitLog=a.Flag_Finalizat From dbo.Pacient_Fisa a Inner Join Inserted b On a.IdPacientFisa=b.IdPacientFisa 

If @PermitLog=1
   Begin	
	--memorare log IdSectie
	SELECT @comments='IdSectie'
	INSERT INTO 	dbo.Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela, IdLinie)
	   SELECT 	b.IdUtilizator, a.IdPacientFisa, @comments, Null, a.IdSectie, 'I', 'Pacient_Sectii', a.IdPacientSectie
	   FROM 	Inserted a Inner Join dbo.Pacient_Fisa b On a.IdPacientFisa=b.IdPacientFisa

	--memorare log NumarSectie
	SELECT @comments='NumarSectie'
	INSERT INTO 	dbo.Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela, IdLinie)
	   SELECT 	b.IdUtilizator, a.IdPacientFisa, @comments, Null, a.NumarSectie, 'I', 'Pacient_Sectii', a.IdPacientSectie
	   FROM 	Inserted a Inner Join dbo.Pacient_Fisa b On a.IdPacientFisa=b.IdPacientFisa

	--memorare log DataInternare
	SELECT @comments='DataInternare'
	INSERT INTO 	dbo.Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela, IdLinie)
	   SELECT 	b.IdUtilizator, a.IdPacientFisa, @comments, Null, a.DataInternare, 'I', 'Pacient_Sectii', a.IdPacientSectie
	   FROM 	Inserted a Inner Join dbo.Pacient_Fisa b On a.IdPacientFisa=b.IdPacientFisa

	--memorare log DataExternare
	SELECT @comments='DataExternare'
	INSERT INTO 	dbo.Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela, IdLinie)
	   SELECT 	b.IdUtilizator, a.IdPacientFisa, @comments, Null, a.DataExternare, 'I', 'Pacient_Sectii', a.IdPacientSectie
	   FROM 	Inserted a Inner Join dbo.Pacient_Fisa b On a.IdPacientFisa=b.IdPacientFisa  

	--memorare log NrZile
	SELECT @comments='NrZile'
	INSERT INTO 	dbo.Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela, IdLinie)
	   SELECT 	b.IdUtilizator, a.IdPacientFisa, @comments, Null, a.NrZile, 'I', 'Pacient_Sectii', a.IdPacientSectie
	   FROM 	Inserted a Inner Join dbo.Pacient_Fisa b On a.IdPacientFisa=b.IdPacientFisa   
   End




















GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE TRIGGER UpdateModificari_Sectii___Update ON dbo.Pacient_Sectii

FOR UPDATE

AS

DECLARE @comments VarChar(255), @PermitLog bit

Select 	@PermitLog=a.Flag_Finalizat From dbo.Pacient_Fisa a Inner Join Inserted b On a.IdPacientFisa=b.IdPacientFisa

If @PermitLog=1
   Begin
	--memorare log IdSectie
	IF UPDATE(IdSectie)
	   Begin	
		SELECT @comments='IdSectie'
		
		INSERT INTO 	dbo.Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela, IdLinie)
		   SELECT 	c.IdUtilizator, a.IdPacientFisa, @comments, b.IdSectie, a.IdSectie, 'U', 'Pacient_Sectii', a.IdPacientSectie
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientSectie=b.IdPacientSectie
				Inner Join dbo.Pacient_Fisa c On a.IdPacientFisa=c.IdPacientFisa
		   WHERE  	a.IdSectie<>b.IdSectie
	   End

	--memorare log NumarSectie
	IF UPDATE(NumarSectie)
	   Begin	
		SELECT @comments='NumarSectie'
		
		INSERT INTO 	dbo.Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela, IdLinie)
		   SELECT 	c.IdUtilizator, a.IdPacientFisa, @comments, b.NumarSectie, a.NumarSectie, 'U', 'Pacient_Sectii', a.IdPacientSectie
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientSectie=b.IdPacientSectie
				Inner Join dbo.Pacient_Fisa c On a.IdPacientFisa=c.IdPacientFisa
		   WHERE  	a.NumarSectie<>b.NumarSectie
	   End

	--memorare log DataInternare
	IF UPDATE(DataInternare)
	   Begin	
		SELECT @comments='DataInternare'
		
		INSERT INTO 	dbo.Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela, IdLinie)
		   SELECT 	c.IdUtilizator, a.IdPacientFisa, @comments, b.DataInternare, a.DataInternare, 'U', 'Pacient_Sectii', a.IdPacientSectie
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientSectie=b.IdPacientSectie
				Inner Join dbo.Pacient_Fisa c On a.IdPacientFisa=c.IdPacientFisa
		   WHERE  	a.DataInternare<>b.DataInternare
	   End

	--memorare log DataExternare
	IF UPDATE(DataExternare)
	   Begin	
		SELECT @comments='DataExternare'
		
		INSERT INTO 	dbo.Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela, IdLinie)
		   SELECT 	c.IdUtilizator, a.IdPacientFisa, @comments, b.DataExternare, a.DataExternare, 'U', 'Pacient_Sectii', a.IdPacientSectie
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientSectie=b.IdPacientSectie
				Inner Join dbo.Pacient_Fisa c On a.IdPacientFisa=c.IdPacientFisa
		   WHERE  	a.DataExternare<>b.DataExternare
	   End

	--memorare log NrZile
	IF UPDATE(NrZile)
	   Begin	
		SELECT @comments='NrZile'
		
		INSERT INTO 	dbo.Lista_Modificari(IdUtilizator, IdPacientFisa, CampModificat, ValoareOld, ValoareNew, TipModificare, Tabela, IdLinie)
		   SELECT 	c.IdUtilizator, a.IdPacientFisa, @comments, b.NrZile, a.NrZile, 'U', 'Pacient_Sectii', a.IdPacientSectie
		   FROM 	Inserted a Inner Join Deleted b On a.IdPacientSectie=b.IdPacientSectie
				Inner Join dbo.Pacient_Fisa c On a.IdPacientFisa=c.IdPacientFisa
		   WHERE  	a.NrZile<>b.NrZile
	   End
   End



















GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
Print 'Recreere triggeri'
--				 END Actualizare TRIGGER


--				BEGIN Actualizare PROCEDURI

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Export___PacientDiagnosticeSecundare]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Export___PacientDiagnosticeSecundare]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Export___PacientFisa]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Export___PacientFisa]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Export___PacientGrouper]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Export___PacientGrouper]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Export___PacientProceduri]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Export___PacientProceduri]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Export___PacientSectii]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Export___PacientSectii]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[INCDS___PacientDiagnosticSecundar_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[INCDS___PacientDiagnosticSecundar_Update]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[INCDS___PacientFisa_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[INCDS___PacientFisa_Update]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[INCDS___PacientProcedura_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[INCDS___PacientProcedura_Update]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaCAS_SelectList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaCAS_SelectList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaDiagnosticeClase_SelectList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaDiagnosticeClase_SelectList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaDiagnosticeSubClase_SelectList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaDiagnosticeSubClase_SelectList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaDiagnostice_SelectList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaDiagnostice_SelectList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaJudete_SelectList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaJudete_SelectList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaMedici_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaMedici_Delete]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaMedici_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaMedici_Insert]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaMedici_Select]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaMedici_Select]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaMedici_SelectList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaMedici_SelectList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaMedici_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaMedici_Update]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaProceduriBlocuri_SelectList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaProceduriBlocuri_SelectList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaProceduriCapitole_SelectList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaProceduriCapitole_SelectList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaProceduri_SelectList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaProceduri_SelectList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaSectiiFiltrate_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaSectiiFiltrate_Delete]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaSectiiFiltrate_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaSectiiFiltrate_Insert]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaSectiiFiltrate_Select]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaSectiiFiltrate_Select]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaSectiiFiltrate_SelectList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaSectiiFiltrate_SelectList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaSectiiFiltrate_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaSectiiFiltrate_Update]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaSectii_SelectList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaSectii_SelectList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaSpecialitati_SelectList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaSpecialitati_SelectList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaSpitaleFiltrate_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaSpitaleFiltrate_Delete]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaSpitaleFiltrate_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaSpitaleFiltrate_Insert]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaSpitaleFiltrate_Select]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaSpitaleFiltrate_Select]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaSpitaleFiltrate_SelectList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaSpitaleFiltrate_SelectList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaSpitaleFiltrate_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaSpitaleFiltrate_Update]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaSpitale_SelectList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaSpitale_SelectList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaStareExternare_SelectList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaStareExternare_SelectList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaStareRecord_SelectList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaStareRecord_SelectList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaTipAsigurare_SelectList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaTipAsigurare_SelectList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaTipExternare_SelectList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaTipExternare_SelectList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaTipInternare_SelectList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaTipInternare_SelectList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaUtilizatori_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaUtilizatori_Delete]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaUtilizatori_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaUtilizatori_Insert]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaUtilizatori_Select]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaUtilizatori_Select]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaUtilizatori_SelectList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaUtilizatori_SelectList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ListaUtilizatori_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ListaUtilizatori_Update]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PacientDiagnosticSecundar_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PacientDiagnosticSecundar_Delete]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PacientDiagnosticSecundar_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PacientDiagnosticSecundar_Insert]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PacientDiagnosticSecundar_PacientFisaRelated]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PacientDiagnosticSecundar_PacientFisaRelated]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PacientDiagnosticSecundar_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PacientDiagnosticSecundar_Update]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PacientFisa_AnulareSelectieExport]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PacientFisa_AnulareSelectieExport]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PacientFisa_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PacientFisa_Delete]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PacientFisa_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PacientFisa_Insert]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PacientFisa_Select]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PacientFisa_Select]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PacientFisa_SelectList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PacientFisa_SelectList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PacientFisa_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PacientFisa_Update]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PacientProcedura_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PacientProcedura_Delete]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PacientProcedura_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PacientProcedura_Insert]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PacientProcedura_PacientFisaRelated]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PacientProcedura_PacientFisaRelated]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PacientProcedura_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PacientProcedura_Update]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PacientProcedura_Verificare]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PacientProcedura_Verificare]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PacientSectie_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PacientSectie_Delete]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PacientSectie_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PacientSectie_Insert]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PacientSectie_PacientFisaRelated]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PacientSectie_PacientFisaRelated]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PacientSectie_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PacientSectie_Update]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Rapoarte_SelectList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Rapoarte_SelectList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SetariApp_SelectList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SetariApp_SelectList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SetariApp_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SetariApp_Update]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rDiagnostice]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rDiagnostice]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rFoaieObservatie]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rFoaieObservatie]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rMedici]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rMedici]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rPacientiAnulati]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rPacientiAnulati]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rPacientiClasificare]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rPacientiClasificare]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rPacientiFinalizatiNefinalizati]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rPacientiFinalizatiNefinalizati]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rPacientiStareValidare]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rPacientiStareValidare]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rProceduri]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rProceduri]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rSectii]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rSectii]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rSpitale]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rSpitale]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rptVizualizareModificari]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rptVizualizareModificari]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rptVizualizareSectie]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rptVizualizareSectie]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rptVizualizareSectieCAS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rptVizualizareSectieCAS]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rptVizualizareSectieDecedati]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rptVizualizareSectieDecedati]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rptVizualizareSectieDiagnosticPrincipalDMS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rptVizualizareSectieDiagnosticPrincipalDMS]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rptVizualizareSectieDiagnosticeSecundare]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rptVizualizareSectieDiagnosticeSecundare]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rptVizualizareSectieInterventiePrincipalaDMS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rptVizualizareSectieInterventiePrincipalaDMS]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rptVizualizareSectiePacientProceduri]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rptVizualizareSectiePacientProceduri]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rptVizualizareSectiePacientSectie]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rptVizualizareSectiePacientSectie]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rptVizualizareSectieSex]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rptVizualizareSectieSex]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rptVizualizareSectieStareExternare]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rptVizualizareSectieStareExternare]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rptVizualizareSectieTipExternare]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rptVizualizareSectieTipExternare]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rptVizualizareSectieTipInternare]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rptVizualizareSectieTipInternare]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rptVizualizareSpital]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rptVizualizareSpital]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rptVizualizareSpitalCAS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rptVizualizareSpitalCAS]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rptVizualizareSpitalDecedati]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rptVizualizareSpitalDecedati]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rptVizualizareSpitalDecedatiMortalitate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rptVizualizareSpitalDecedatiMortalitate]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rptVizualizareSpitalDiagnosticPrincipalDMS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rptVizualizareSpitalDiagnosticPrincipalDMS]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rptVizualizareSpitalInterventiePrincipalaDMS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rptVizualizareSpitalInterventiePrincipalaDMS]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rptVizualizareSpitalPacientProceduri]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rptVizualizareSpitalPacientProceduri]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rptVizualizareSpitalPacientSectie]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rptVizualizareSpitalPacientSectie]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rptVizualizareSpitalSex]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rptVizualizareSpitalSex]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rptVizualizareSpitalStareExternare]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rptVizualizareSpitalStareExternare]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rptVizualizareSpitalTipExternare]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rptVizualizareSpitalTipExternare]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rptVizualizareSpitalTipInternare]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rptVizualizareSpitalTipInternare]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE Export___PacientDiagnosticeSecundare

(@IdSpital Int, 
 @DataExternareStart smalldatetime, 
 @DataExternareEnd smalldatetime,
 @Flag_SelectieExport bit,
 @StareValidare int
)

As 
	
	Select	a.Caz_ID, c.CodDiagnostic As CodDiagnosticSecundar, b.CodValiditate, b.NrInGrouper    
	From 	Pacient_Fisa a 
		Inner Join Pacient_DiagnosticeSecundare b On a.IdPacientFisa=b.IdPacientFisa
		Inner Join Lista_Diagnostice c On b.IdDiagnosticSecundar=c.IdDiagnostic
	Where 	a.IdSpital=@IdSpital And 
		a.DataExternare>=IsNull(@DataExternareStart,a.DataExternare) And 
		a.DataExternare<=IsNull(@DataExternareEnd,a.DataExternare) And 
		a.Flag_Finalizat=1 And 
		(a.Flag_SelectieExport=Case When @Flag_SelectieExport=1 Then @Flag_SelectieExport Else a.Flag_SelectieExport End) And
		(IsNull(a.StareValidare,-2)= Case When @StareValidare=-2 Then IsNull(a.StareValidare,-2) Else @StareValidare End)
	Order By a.Caz_ID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[Export___PacientDiagnosticeSecundare]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE Export___PacientFisa 

(@IdSpital Int, 
 @DataExternareStart smalldatetime, 
 @DataExternareEnd smalldatetime,
 @Flag_SelectieExport bit,
 @StareValidare int
)

As 

	Select	a.Caz_ID, a.IdAccident, k.Spital_ID, i.Sectie_ID, a.NumarSectie, a.NFO, a.Nume, a.Prenume, a.CNP, 
		a.IdJudet, a.IdLocalitate, 
		a.DataNastere, a.DataInternare, a.OraInternare, a.DataExternare, a.OraExternare,
		a.IdTipAsigurare, a.IdCAS, a.IdJudetCAS, 
		a.Sex, a.GreutateNastere, a.IdTipInternare, a.IdTipExternare, a.IdStareExternare, 
		c1.CodMedic, c1.IdSpecialitate, 
		c2.CodMedic As CodMedicOperator,
		c3.CodMedic As CodMedicMana2, 
		d.CodDiagnostic As CodDiagnosticLaInternare,
		e.CodDiagnostic As CodDiagnosticPrincipalCod1,
		g.CodDiagnostic As CodDiagnosticPrincipalCod2,
		f.CodProcedura As CodInterventieChirurgicala, 
		a.DataInterventieChirurgicalaPrincipala, a.IdStareRecord, 
		a.Flag_Finalizat, a.StareValidare, l.Utilizator, a.DataOperare
	FROM 	[dbo].[Pacient_Fisa] a
		Left Join [dbo].[Lista_Localitati] b On a.IdLocalitate=b.IdLocalitate and a.IdJudet=b.IdJudet
		Inner Join [dbo].[Lista_Medici] c1 On a.IdMedic=c1.IdMedic And a.IdSpital=c1.IdSpital
		Left Join [dbo].[Lista_Medici] c2 On a.IdMedicOperator=c2.IdMedic And a.IdSpital=c2.IdSpital
		Left Join [dbo].[Lista_Medici] c3 On a.IdMedicMana2=c3.IdMedic And a.IdSpital=c3.IdSpital
		Left Join [dbo].[Lista_Diagnostice] d On a.IdDiagnosticLaInternare=d.IdDiagnostic
		Inner Join [dbo].[Lista_Diagnostice] e On a.IdDiagnosticPrincipalCod1=e.IdDiagnostic 
		Left Join [dbo].[Lista_Diagnostice] g On a.IdDiagnosticPrincipalCod2=g.IdDiagnostic
		Left Join [dbo].[Nom_Proceduri] f On a.IdInterventieChirurgicala=f.IdProcedura
		Inner Join [dbo].[Lista_Sectii___Filtrat] h On a.IdSpital=h.IdSpital And a.IdSectie=h.IdSectie And a.NumarSectie=h.NumarSectie
		Inner Join [dbo].[Lista_Sectii] i On h.IdSectie=i.IdSectie
		Inner Join [dbo].[Lista_Spitale___Filtrat] j On a.IdSpital=j.IdSpital
		Inner Join [dbo].[Lista_Spitale] k On j.IdSpital=k.IdSpital
		Inner Join [dbo].[Lista_Utilizatori] l On a.IdUtilizator=l.IdUtilizator
	Where 	a.IdSpital=@IdSpital And 
		a.DataExternare>=IsNull(@DataExternareStart,a.DataExternare) And 
		a.DataExternare<=IsNull(@DataExternareEnd,a.DataExternare) And 
		a.Flag_Finalizat=1 And 
		(a.Flag_SelectieExport=Case When @Flag_SelectieExport=1 Then @Flag_SelectieExport Else a.Flag_SelectieExport End) And
		(IsNull(a.StareValidare,-2)= Case When @StareValidare=-2 Then IsNull(a.StareValidare,-2) Else @StareValidare End)
	Order By a.Caz_ID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[Export___PacientFisa]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE Export___PacientGrouper

(@IdSpital Int, 
 @DataExternareStart smalldatetime, 
 @DataExternareEnd smalldatetime,
 @Flag_SelectieExport bit,
 @StareValidare int
)

As 

	Select	a.Caz_ID, c.CodDRG, b.DRG_VR, b.Tarif, b.Validitate_DRG_COD, b.Validitate_IntPrinc,
		b.Validitate_DiagPrinc_Cod1, b.Validitate_DiagPrinc_Cod2, 
		b.fRC1, b.fRC2, b.fRC3, b.fRC4_D, b.fRC4_S, b.fRC5, b.Transfer_Reinternare, b.NrZileReinternare,
		b.fRC6, b.fRC7, b.fRC8, b.fRC9, b.fRC10, b.fRC11, b.fRC12, b.fRC13, b.fRC14, b.fRC15,
		b.fRF2, b.fRF3, b.fRF4, b.fRF5, b.fRF6, b.fRF7, b.fRF8, b.fRF9, b.fRF10
	From 	Pacient_Fisa a 
		Inner Join Pacient_Grouper b On a.IdPacientFisa=b.IdPacientFisa
		Left Join Lista_CoduriDRG c On b.IdDRG=c.IdDRG
	Where 	a.IdSpital=@IdSpital And 
		a.DataExternare>=IsNull(@DataExternareStart,a.DataExternare) And 
		a.DataExternare<=IsNull(@DataExternareEnd,a.DataExternare) And 
		a.Flag_Finalizat=1 And 
		(a.Flag_SelectieExport=Case When @Flag_SelectieExport=1 Then @Flag_SelectieExport Else a.Flag_SelectieExport End) And
		(IsNull(a.StareValidare,-2)= Case When @StareValidare=-2 Then IsNull(a.StareValidare,-2) Else @StareValidare End)
	Order By a.Caz_ID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[Export___PacientGrouper]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE Export___PacientProceduri

(@IdSpital Int, 
 @DataExternareStart smalldatetime, 
 @DataExternareEnd smalldatetime,
 @Flag_SelectieExport bit,
 @StareValidare int
)

As 

	Select	a.Caz_ID, c.CodProcedura, b.Cantitate,  b.CodValiditate, b.NrInGrouper    
	From 	Pacient_Fisa a 
		Inner Join Pacient_Proceduri b On a.IdPacientFisa=b.IdPacientFisa
		Inner Join Nom_Proceduri c On b.IdProcedura=c.IdProcedura
	Where 	a.IdSpital=@IdSpital And 
		a.DataExternare>=IsNull(@DataExternareStart,a.DataExternare) And 
		a.DataExternare<=IsNull(@DataExternareEnd,a.DataExternare) And 
		a.Flag_Finalizat=1 And 
		(a.Flag_SelectieExport=Case When @Flag_SelectieExport=1 Then @Flag_SelectieExport Else a.Flag_SelectieExport End) And
		(IsNull(a.StareValidare,-2)= Case When @StareValidare=-2 Then IsNull(a.StareValidare,-2) Else @StareValidare End)
	Order By a.Caz_ID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[Export___PacientProceduri]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE Export___PacientSectii

(@IdSpital Int, 
 @DataExternareStart smalldatetime, 
 @DataExternareEnd smalldatetime,
 @Flag_SelectieExport bit,
 @StareValidare int
)

As 


	Select	a.Caz_ID, d.Spital_ID, e.Sectie_ID, b.NumarSectie, b.DataInternare, b.DataExternare, b.NrZile  
	From 	Pacient_Fisa a 
		Inner Join Pacient_Sectii b On a.IdPacientFisa=b.IdPacientFisa
		Inner Join Lista_Sectii___Filtrat c On b.IdSpital=c.IdSpital And b.IdSectie=c.IdSectie And b.NumarSectie=c.NumarSectie
		Inner Join Lista_Spitale d On a.IdSpital=d.IdSpital
		Inner Join Lista_Sectii e On c.IdSectie=e.IdSectie
	Where 	a.IdSpital=@IdSpital And 
		a.DataExternare>=IsNull(@DataExternareStart,a.DataExternare) And 
		a.DataExternare<=IsNull(@DataExternareEnd,a.DataExternare) And 
		a.Flag_Finalizat=1 And 
		(a.Flag_SelectieExport=Case When @Flag_SelectieExport=1 Then @Flag_SelectieExport Else a.Flag_SelectieExport End) And
		(IsNull(a.StareValidare,-2)= Case When @StareValidare=-2 Then IsNull(a.StareValidare,-2) Else @StareValidare End)
	Order By a.Caz_ID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[Export___PacientSectii]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE INCDS___PacientDiagnosticSecundar_Update
( @Caz_ID varchar (52),
  @CodDiagnosticSecundar varchar(5),
  @CodValiditate varchar (8)
)
As 

	UPDATE [dbo].[Pacient_DiagnosticeSecundare] Set CodValiditate = @CodValiditate
	FROM 	[dbo].[Pacient_DiagnosticeSecundare]  a Inner Join [dbo].[Pacient_Fisa] b On a.IdPacientFisa=b.IdPacientFisa 
		inner Join [dbo].[Lista_Diagnostice] c On a.IdDiagnosticSecundar=c.IdDiagnostic
	WHERE b.Caz_ID=@Caz_ID And c.CodDiagnostic=@CodDiagnosticSecundar
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[INCDS___PacientDiagnosticSecundar_Update]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE INCDS___PacientFisa_Update
( 
  --parametrii pentru Pacient_Fisa
  @Caz_ID varchar (52) ,
  @StareValidare int ,
  @Data_Trimitere datetime ,

  --parametrii pentru Pacient_Grouper
  @CodDRG varchar(6) ,
  @DRG_VR decimal(24,7) ,
  @Tarif int , 
  @Validitate_DRG_COD tinyint ,
  @Validitate_IntPrinc varchar (8) ,
  @Validitate_DiagPrinc_Cod1 varchar (8) ,
  @Validitate_DiagPrinc_Cod2 varchar (8) ,
  @fRC1 bit ,
  @fRC2 bit ,
  @fRC3 bit ,
  @fRC4_D bit ,
  @fRC4_S bit ,
  @fRC5 bit ,
  @Transfer_Reinternare varchar (50) ,
  @NrZileReinternare smallint ,
  @fRC6 bit ,
  @fRC7 bit ,
  @fRC8 bit ,
  @fRC9 bit ,
  @fRC10 bit ,
  @fRC11 bit ,
  @fRC12 bit ,
  @fRC13 bit ,
  @fRC14 bit ,
  @fRC15 bit ,
  @fRF2 bit ,
  @fRF3 bit ,
  @fRF4 bit ,
  @fRF5 bit ,
  @fRF6 bit ,
  @fRF7 bit ,
  @fRF8 bit ,
  @fRF9 bit ,
  @fRF10 bit
)

As 
	--Update Pacient_Fisa
	UPDATE [dbo].[Pacient_Fisa] Set 
	    StareValidare = @StareValidare,
	    Data_Trimitere = @Data_Trimitere,
	    Data_Import = GetDate()
	WHERE Caz_ID = @Caz_ID
		
	--Update Pacient_Grouper
	UPDATE [dbo].[Pacient_Grouper] Set 
	    IdDRG = (Select b.IdDRG From [dbo].[Lista_CoduriDRG] b Where b.CodDRG=@CodDRG),
	    DRG_VR=@DRG_VR,
	    Tarif=@Tarif, 
	    Validitate_DRG_COD = @Validitate_DRG_COD,
	    Validitate_IntPrinc = @Validitate_IntPrinc,
	    Validitate_DiagPrinc_Cod1 = @Validitate_DiagPrinc_Cod1,
	    Validitate_DiagPrinc_Cod2 = @Validitate_DiagPrinc_Cod2,
	    fRC1 = @fRC1,
	    fRC2 = @fRC2,
	    fRC3 = @fRC3,
	    fRC4_D = @fRC4_D,
	    fRC4_S = @fRC4_S,
	    fRC5 = @fRC5,
	    Transfer_Reinternare = @Transfer_Reinternare,
	    NrZileReinternare = @NrZileReinternare,
	    fRC6 = @fRC6,
	    fRC7 = @fRC7,
	    fRC8 = @fRC8,
	    fRC9 = @fRC9,
	    fRC10 = @fRC10,
	    fRC11 = @fRC11,
	    fRC12 = @fRC12,
	    fRC13 = @fRC13,
	    fRC14 = @fRC14,
	    fRC15 = @fRC15,
	    fRF2 = @fRF2,
	    fRF3 = @fRF3,
	    fRF4 = @fRF4,
	    fRF5 = @fRF5,
	    fRF6 = @fRF6,
	    fRF7 = @fRF7,
	    fRF8 = @fRF8,
	    fRF9 = @fRF9,
	    fRF10 = @fRF10
	WHERE IdPacientFisa = (Select IdPacientFisa a From [dbo].[Pacient_Fisa] a Where a.Caz_ID=@Caz_ID)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[INCDS___PacientFisa_Update]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE INCDS___PacientProcedura_Update
( @Caz_ID varchar (52),
  @CodProcedura varchar(8),
  @CodValiditate varchar (8)
)
As 

	UPDATE [dbo].[Pacient_Proceduri] Set CodValiditate = @CodValiditate
	FROM 	[dbo].[Pacient_Proceduri]  a Inner Join [dbo].[Pacient_Fisa] b On a.IdPacientFisa=b.IdPacientFisa 
		Inner Join [dbo].[Nom_Proceduri] c On a.IdProcedura=c.IdProcedura
	WHERE b.Caz_ID=@Caz_ID And c.CodProcedura=@CodProcedura
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[INCDS___PacientProcedura_Update]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaCAS_SelectList

As 

SELECT TOP 1000 a.IdCAS, a.CASTip
FROM [dbo].[Lista_CAS] a

For XML AUTO
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaCAS_SelectList]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaDiagnosticeClase_SelectList

As 

SELECT TOP 1000 a.IdClasaDiagnostic, a.DenClasaDiagnostic
FROM [dbo].[Lista_Diagnostice___Clase] a

For XML AUTO
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaDiagnosticeClase_SelectList]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaDiagnosticeSubClase_SelectList

As 

SELECT TOP 1000 a.IdSubClasaDiagnostic, a.IdClasaDiagnostic, a.DenSubClasaDiagnostic
FROM [dbo].[Lista_Diagnostice___SubClase] a

For XML AUTO
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaDiagnosticeSubClase_SelectList]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaDiagnostice_SelectList

As 

SELECT a.IdDiagnostic, a.IdSubClasaDiagnostic, a.CodDiagnostic, a.NumeDiagnostic, b.DenSubClasaDiagnostic, b.IdClasaDiagnostic,
	c.DenClasaDiagnostic
FROM 	[dbo].[Lista_Diagnostice] a Inner Join [dbo].[Lista_Diagnostice___SubClase] b On a.IdSubClasaDiagnostic=b.IdSubClasaDiagnostic
	Inner Join [dbo].[Lista_Diagnostice___Clase] c On b.IdClasaDiagnostic=c.IdClasaDiagnostic
ORDER BY a.CodDiagnostic

For XML AUTO
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaDiagnostice_SelectList]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaJudete_SelectList

As 

SELECT TOP 1000 a.IdJudet, a.JudetNume
FROM [dbo].[Lista_Judete] a

For XML AUTO
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaJudete_SelectList]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaMedici_Delete(@IdMedic int)
As 

--daca exista foi de observatie pe aceasta sectie (Pacient_Fisa sau Pacient_Sectii), nu mai permit stergerea sectiei

If Exists(Select a.IdMedic From [dbo].[Lista_Medici] a Inner Join [dbo].[Pacient_Fisa] b On a.IdSpital=b.IdSpital And a.IdMedic=b.IdMedic Where a.IdMedic=@IdMedic) Or 
   Exists(Select a.IdMedic From [dbo].[Lista_Medici] a Inner Join [dbo].[Pacient_Fisa] b On a.IdSpital=b.IdSpital And a.IdMedic=b.IdMedicOperator Where a.IdMedic=@IdMedic) Or 
   Exists(Select a.IdMedic From [dbo].[Lista_Medici] a Inner Join [dbo].[Pacient_Fisa] b On a.IdSpital=b.IdSpital And a.IdMedic=b.IdMedicMana2 Where a.IdMedic=@IdMedic) 
	RaisError('Nu este permisa eliminarea acestui medic deoarece sunt fise pacienti completate cu acest medic.',16,1)
Else
	
	DELETE [dbo].[Lista_Medici] WHERE IdMedic=@IdMedic
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaMedici_Delete]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaMedici_Insert
( @AUTO_IdMedic int OUT,
  @AUTO_Stamp varchar(30) OUT,
  @IdSpital int,
  @CodMedic varchar (7) ,
  @DenMedic varchar(50) ,
  @IdSpecialitate int
)

As 
	If Exists(Select IdMedic From [dbo].[Lista_Medici] Where IdSpital=@IdSpital And CodMedic=@CodMedic)
		RaisError('Nu este permisa inserarea acestui medic deoarece exista deja un medic cu acest cod la acest spital.',16,1)
	Else
	   Begin
		INSERT INTO [dbo].[Lista_Medici] (IdSpital, CodMedic, DenMedic, IdSpecialitate)
		VALUES (@IdSpital, @CodMedic, @DenMedic, @IdSpecialitate)
	
		Select @AUTO_IdMedic = @@IDENTITY
		Select @AUTO_Stamp = CONVERT(varchar(30), CONVERT(INT, @@DBTS), 13)
	   End
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaMedici_Insert]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaMedici_Select (@IdMedic int)
As 
	SELECT TOP 1000 a.IdMedic, a.IdSpital, a.CodMedic, a.DenMedic, a.IdSpecialitate, CONVERT(varchar(30), CONVERT(INT, a.Stamp),13) Stamp  
	FROM [dbo].[Lista_Medici] a
	WHERE (a.IdMedic =  @IdMedic) 
	For XML AUTO
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaMedici_Select]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaMedici_SelectList
As 
	SELECT TOP 1000 a.IdMedic, a.IdSpital, a.CodMedic, a.DenMedic, a.IdSpecialitate, b.DenSpecialitate, CONVERT(varchar(30), CONVERT(INT, a.Stamp),13) Stamp  
	FROM [dbo].[Lista_Medici] a Inner Join [dbo].[Lista_Specialitati] b On a.IdSpecialitate=b.IdSpecialitate
	ORDER BY a.DenMedic
	For XML AUTO
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaMedici_SelectList]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaMedici_Update
( @IdMedic int ,
  @IdSpital int ,
  @CodMedic varchar (7) ,
  @DenMedic varchar (50) ,
  @IdSpecialitate int ,
  @Stamp varchar(30) OUT
)

As 
	If Exists(Select IdMedic From [dbo].[Lista_Medici] Where IdSpital=@IdSpital And CodMedic=@CodMedic And IdMedic<>@IdMedic)
		RaisError('Nu este permisa modificarea medicului deoarece un medic cu noul cod exista deja.',16,1)
	Else
	   Begin	
		--daca exista foi de observatie pe acest medic (IdMedic, IdMedicOperator, IdMedicMana2), nu mai permit modificarea sectiei, 
		--daca s-a ales alta sectie sau numar
		If (
		   @IdSpital<>(Select IdSpital From [dbo].[Lista_Medici] WHERE IdMedic=@IdMedic) or
		   @CodMedic<>(Select CodMedic From [dbo].[Lista_Medici] WHERE IdMedic=@IdMedic)
		   )	
		   AND
		   (
		   Exists(Select a.IdMedic From [dbo].[Lista_Medici] a Inner Join [dbo].[Pacient_Fisa] b On a.IdSpital=b.IdSpital And a.IdMedic=b.IdMedic Where a.IdMedic=@IdMedic) Or
		   Exists(Select a.IdMedic From [dbo].[Lista_Medici] a Inner Join [dbo].[Pacient_Fisa] b On a.IdSpital=b.IdSpital And a.IdMedic=b.IdMedicOperator Where a.IdMedic=@IdMedic) Or
		   Exists(Select a.IdMedic From [dbo].[Lista_Medici] a Inner Join [dbo].[Pacient_Fisa] b On a.IdSpital=b.IdSpital And a.IdMedic=b.IdMedicMana2 Where a.IdMedic=@IdMedic)
	   	   )	
			RaisError('Nu este permisa aceasta modificare deoarece sunt fise pacienti completate cu vechile valori ale medicului (Cod medic si spital de apartenenta).',16,1)
		Else	
		   Begin
			Declare @NewStamp varchar(30)
			Select 	@NewStamp=CONVERT(varchar(30), CONVERT(INT, Stamp),13) From [dbo].[Lista_Medici] Where IdMedic=@IdMedic
			
			If RTrim(@NewStamp) != RTrim(@Stamp)
				RAISERROR(51000, 16, 1)
			Else
				UPDATE [dbo].[Lista_Medici] Set 
				    IdSpital = @IdSpital, 
				    CodMedic = @CodMedic,
				    DenMedic = @DenMedic,
				    IdSpecialitate = @IdSpecialitate
				WHERE IdMedic = @IdMedic
			
			Select @Stamp=CONVERT(varchar(30), CONVERT(INT, @@DBTS),13)
		   End
	   End
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaMedici_Update]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaProceduriBlocuri_SelectList

As 

SELECT TOP 1000 a.CodBloc, a.CodCapitol, a.DenBloc
FROM [dbo].[Nom_Proceduri___Blocuri] a
For XML AUTO
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaProceduriBlocuri_SelectList]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaProceduriCapitole_SelectList

As 

SELECT TOP 1000 a.CodCapitol, a.DenCapitol
FROM [dbo].[Nom_Proceduri___Capitole] a

For XML AUTO
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaProceduriCapitole_SelectList]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaProceduri_SelectList

As 

SELECT a.IdProcedura, a.CodBloc, a.CodProcedura, a.DenProcedura, b.DenBloc, b.CodCapitol, c.DenCapitol
FROM 	[dbo].[Nom_Proceduri] a Inner Join [dbo].[Nom_Proceduri___Blocuri] b On a.CodBloc=b.CodBloc 
	Inner Join [dbo].[Nom_Proceduri___Capitole] c On b.CodCapitol=c.CodCapitol
ORDER BY a.CodProcedura 

For XML AUTO
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaProceduri_SelectList]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaSectiiFiltrate_Delete(@IdSectieFiltrat int)

As 

--daca exista foi de observatie pe aceasta sectie (Pacient_Fisa sau Pacient_Sectii), nu mai permit stergerea sectiei

If Exists(Select a.IdSectieFiltrat From [dbo].[Lista_Sectii___Filtrat] a Inner Join [dbo].[Pacient_Fisa] b On a.IdSpital=b.IdSpital And a.IdSectie=b.IdSectie And a.NumarSectie=b.NumarSectie Where a.IdSectieFiltrat=@IdSectieFiltrat) Or 
   Exists(Select a.IdSectieFiltrat From [dbo].[Lista_Sectii___Filtrat] a Inner Join [dbo].[Pacient_Sectii] b On a.IdSpital=b.IdSpital And a.IdSectie=b.IdSectie And a.NumarSectie=b.NumarSectie Where a.IdSectieFiltrat=@IdSectieFiltrat)
	RaisError('Nu este permisa eliminarea acestei sectii deoarece sunt fise pacienti completate cu aceasta sectie.',16,1)
Else
	
	DELETE [dbo].[Lista_Sectii___Filtrat] WHERE IdSectieFiltrat=@IdSectieFiltrat
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaSectiiFiltrate_Delete]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaSectiiFiltrate_Insert
( @AUTO_IdSectieFiltrat int OUT,
  @IdSpital int ,
  @IdSectie int ,
  @NumarSectie int ,
  @Implicit bit
)
As 
	If Exists(Select IdSectieFiltrat From [dbo].[Lista_Sectii___Filtrat] Where IdSpital=@IdSpital And IdSectie=@IdSectie And NumarSectie=@NumarSectie)
		RaisError('Nu este permisa inserarea acestei sectii, cu acest numar, pentru spitalul selectat, deoarece deja exista.',16,1)
	Else
	   Begin
		If @Implicit=1 And Exists(Select IdSectieFiltrat From [dbo].[Lista_Sectii___Filtrat] Where IdSpital=@IdSpital And Implicit=1)
			Update [dbo].[Lista_Sectii___Filtrat]  Set Implicit=0 Where IdSpital=@IdSpital And Implicit=1

		INSERT INTO [dbo].[Lista_Sectii___Filtrat] (IdSpital, IdSectie, NumarSectie, Implicit)
		VALUES (@IdSpital, @IdSectie, @NumarSectie, @Implicit)
	
		Select @AUTO_IdSectieFiltrat = @@IDENTITY
	   End
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaSectiiFiltrate_Insert]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaSectiiFiltrate_Select (@IdSectieFiltrat int)
As 
	SELECT TOP 1000 a.IdSectieFiltrat, a.IdSpital, a.IdSectie, b.SectieNume As DenSectie, a.NumarSectie, a.Implicit 
	FROM 	[dbo].[Lista_Sectii___Filtrat] a Inner Join [dbo].[Lista_Sectii] b On a.IdSectie=b.IdSectie
	WHERE a.IdSectieFiltrat=@IdSectieFiltrat
	For XML AUTO
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaSectiiFiltrate_Select]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaSectiiFiltrate_SelectList
As 
	SELECT TOP 1000 a.IdSectieFiltrat, a.IdSpital, a.IdSectie, b.SectieNume As DenSectie, a.NumarSectie, a.Implicit 
	FROM 	[dbo].[Lista_Sectii___Filtrat] a Inner Join [dbo].[Lista_Sectii] b On a.IdSectie=b.IdSectie
	For XML AUTO
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaSectiiFiltrate_SelectList]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaSectiiFiltrate_Update
( @IdSectieFiltrat int ,
  @IdSpital int ,
  @IdSectie int ,
  @NumarSectie int ,
  @Implicit bit
)

As 
	If Exists(Select IdSectieFiltrat From [dbo].[Lista_Sectii___Filtrat] Where IdSpital=@IdSpital And IdSectie=@IdSectie And NumarSectie=@NumarSectie And IdSectieFiltrat<>@IdSectieFiltrat)
		RaisError('Nu este permisa aceasta modificare deoarece conduce la o sectie cu acest numar deja introdusa pentru spitalul respectiv.',16,1)
	Else
	   Begin	
		--daca exista foi de observatie pe aceasta sectie (Pacient_Fisa sau Pacient_Sectii), nu mai permit modificarea sectiei, 
		--daca s-a ales alta sectie sau numar
		If (
		   @IdSpital<>(Select IdSpital From [dbo].[Lista_Sectii___Filtrat] WHERE IdSectieFiltrat=@IdSectieFiltrat) or
		   @IdSectie<>(Select IdSectie From [dbo].[Lista_Sectii___Filtrat] WHERE IdSectieFiltrat=@IdSectieFiltrat) or
		   @NumarSectie<>(Select NumarSectie From [dbo].[Lista_Sectii___Filtrat] WHERE IdSectieFiltrat=@IdSectieFiltrat)
		   )	
		   AND
		   (
		   Exists(Select a.IdSectieFiltrat From [dbo].[Lista_Sectii___Filtrat] a Inner Join [dbo].[Pacient_Fisa] b On a.IdSpital=b.IdSpital And a.IdSectie=b.IdSectie And a.NumarSectie=b.NumarSectie Where a.IdSectieFiltrat=@IdSectieFiltrat) Or
		   Exists(Select a.IdSectieFiltrat From [dbo].[Lista_Sectii___Filtrat] a Inner Join [dbo].[Pacient_Sectii] b On a.IdSpital=b.IdSpital And a.IdSectie=b.IdSectie And a.NumarSectie=b.NumarSectie Where a.IdSectieFiltrat=@IdSectieFiltrat)
	   	   )	
			RaisError('Nu este permisa aceasta modificare deoarece sunt fise pacienti completate cu vechile valori ale sectiei (Cod sectie, numar si spital de apartenenta).',16,1)
		Else	
		   Begin	
			If @Implicit=1 And Exists(Select IdSectieFiltrat From [dbo].[Lista_Sectii___Filtrat] Where IdSpital=@IdSpital And Implicit=1)
				Update [dbo].[Lista_Sectii___Filtrat]  Set Implicit=0 Where IdSpital=@IdSpital And Implicit=1
	
			UPDATE [dbo].[Lista_Sectii___Filtrat] Set 
			    IdSpital=@IdSpital,
			    IdSectie=@IdSectie,
			    NumarSectie=@NumarSectie,
			    Implicit=@Implicit
			WHERE IdSectieFiltrat=@IdSectieFiltrat
		   End
	   End
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaSectiiFiltrate_Update]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaSectii_SelectList

As 

	Select 	TOP 1000 a.IdSectie, a.Sectie_ID, a.SectieNume, a.Acut
	From 	[dbo].[Lista_Sectii] a 
	
	For XML AUTO
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaSectii_SelectList]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaSpecialitati_SelectList

As 

SELECT TOP 1000 a.IdSpecialitate, a.DenSpecialitate
FROM [dbo].[Lista_Specialitati] a

For XML AUTO
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaSpecialitati_SelectList]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaSpitaleFiltrate_Delete(@IdSpitalFiltrat int)
As 

--daca exista asocieri cu sectii nu mai permit stergerea spitalului
If Exists(Select IdSectieFiltrat From [dbo].[Lista_Sectii___Filtrat] Where IdSpital=(Select IdSpital From [dbo].[Lista_Spitale___Filtrat] WHERE IdSpitalFiltrat=@IdSpitalFiltrat))
	RaisError('Nu este permisa eliminarea acestui spital deoarece sunt sectii asociate deja.',16,1)
Else	
	DELETE [dbo].[Lista_Spitale___Filtrat] WHERE IdSpitalFiltrat=@IdSpitalFiltrat
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaSpitaleFiltrate_Delete]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaSpitaleFiltrate_Insert
( @AUTO_IdSpitalFiltrat int OUT,
  @IdSpital int ,
  @NumarPaturi smallint
)
As 
	If Exists(Select IdSpital From [dbo].[Lista_Spitale___Filtrat] Where IdSpital=@IdSpital)
		RaisError('Nu este permisa inserarea acestui spital deoarece spitalul cu acest cod exista deja.',16,1)
	Else
	   Begin
		INSERT INTO [dbo].[Lista_Spitale___Filtrat] (IdSpital, NumarPaturi)
		VALUES (@IdSpital, @NumarPaturi)
	
		Select @AUTO_IdSpitalFiltrat = @@IDENTITY
	   End
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaSpitaleFiltrate_Insert]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaSpitaleFiltrate_Select (@IdSpitalFiltrat int)
As 
	SELECT TOP 1000 a.IdSpitalFiltrat, a.IdSpital, b.SpitalDenumire As DenSpital, b.IdJudet, d.JudetNume As DenJudet, b.IdLocalitate, c.LocalitateNume As DenLocalitate, a.NumarPaturi 
	FROM 	[dbo].[Lista_Spitale___Filtrat] a Inner Join [dbo].[Lista_Spitale] b On a.IdSpital=b.IdSpital
		inner join [dbo].[Lista_Localitati] c On b.IdJudet=c.IdJudet And b.IdLocalitate=c.IdLocalitate
		inner join [dbo].[Lista_Judete] d On c.IdJudet=d.IdJudet 
	WHERE (a.IdSpitalFiltrat=@IdSpitalFiltrat) 

	For XML AUTO
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaSpitaleFiltrate_Select]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaSpitaleFiltrate_SelectList
As 
	SELECT TOP 1000 a.IdSpitalFiltrat, a.IdSpital, b.SpitalDenumire As DenSpital, b.IdJudet, d.JudetNume As DenJudet, b.IdLocalitate, c.LocalitateNume As DenLocalitate, a.NumarPaturi 
	FROM 	[dbo].[Lista_Spitale___Filtrat] a Inner Join [dbo].[Lista_Spitale] b On a.IdSpital=b.IdSpital
		inner join [dbo].[Lista_Localitati] c On b.IdJudet=c.IdJudet And b.IdLocalitate=c.IdLocalitate
		inner join [dbo].[Lista_Judete] d On c.IdJudet=d.IdJudet 
	For XML AUTO
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaSpitaleFiltrate_SelectList]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaSpitaleFiltrate_Update
( @IdSpitalFiltrat int ,
  @IdSpital int ,
  @NumarPaturi smallint
)

As 
	If Exists(Select IdSpital From [dbo].[Lista_Spitale___Filtrat] Where IdSpital=@IdSpital And IdSpitalFiltrat<>@IdSpitalFiltrat)
		RaisError('Nu este permisa modificarea spitalului deoarece spitalul cu noul cod exista deja.',16,1)
	Else
	   Begin	
		--daca exista asocieri cu sectii nu mai permit modificarea spitalului, daca s-a ales alt cod de spital
		If @IdSpital<>(Select IdSpital From [dbo].[Lista_Spitale___Filtrat] WHERE IdSpitalFiltrat=@IdSpitalFiltrat) AND Exists(Select IdSectieFiltrat From [dbo].[Lista_Sectii___Filtrat] Where IdSpital=(Select IdSpital From [dbo].[Lista_Spitale___Filtrat] WHERE IdSpitalFiltrat=@IdSpitalFiltrat))
			RaisError('Nu este permisa modificarea acestui spital deoarece sunt sectii asociate deja pe vechiul spital.',16,1)
		Else	
			UPDATE [dbo].[Lista_Spitale___Filtrat] Set IdSpital=@IdSpital, NumarPaturi=@NumarPaturi WHERE IdSpitalFiltrat=@IdSpitalFiltrat
	   End
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaSpitaleFiltrate_Update]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaSpitale_SelectList

As 

Select 	TOP 1000 a.IdSpital, a.Spital_ID, a.SpitalDenumire, a.IdSpitalCNAS, b.SpitalID_CNAS,
	b.Denumire_CNAS, b.IdJudet, c.JudetNume, a.IdLocalitate, d.LocalitateNume
From 	[dbo].[Lista_Spitale] a 
	Inner Join [dbo].[Lista_Spitale_CNAS] b On a.IdSpitalCNAS=b.IdSpitalCNAS
	Inner Join [dbo].[Lista_Judete] c On b.IdJudet=c.IdJudet
	Inner Join [dbo].[Lista_Localitati] d On a.IdLocalitate=d.IdLocalitate
For XML AUTO
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaSpitale_SelectList]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaStareExternare_SelectList

As 

SELECT TOP 1000 a.IdStareExternare, a.StareExternare
FROM [dbo].[Lista_StareExternare] a

For XML AUTO
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaStareExternare_SelectList]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaStareRecord_SelectList

As 

SELECT TOP 1000 a.IdStareRecord, a.StareRecord
FROM [dbo].[Lista_StareRecord] a

For XML AUTO
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaStareRecord_SelectList]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaTipAsigurare_SelectList

As 

SELECT TOP 1000 a.IdTipAsigurare, a.TipAsigurare
FROM [dbo].[Lista_TipAsigurare] a

For XML AUTO
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaTipAsigurare_SelectList]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaTipExternare_SelectList

As 

SELECT TOP 1000 a.IdTipExternare, a.TipExternare
FROM [dbo].[Lista_TipExternare] a

For XML AUTO
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaTipExternare_SelectList]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaTipInternare_SelectList

As 

SELECT TOP 1000 a.IdTipInternare, a.TipInternare
FROM [dbo].[Lista_TipInternare] a

For XML AUTO
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaTipInternare_SelectList]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaUtilizatori_Delete(@IdUtilizator int)
As 

--daca exista foi de observatie introduse de acest utilizator nu mai permit eliminarea sa
If Exists(Select IdUtilizator From [dbo].[Pacient_Fisa] Where IdUtilizator=@IdUtilizator)
	RaisError('Nu este permisa eliminarea acestui utilizator deoarece sunt foi de observatie introduse de acesta.',16,1)
Else	
	begin
		If (Select TipUser From [dbo].[Lista_Utilizatori] Where IdUtilizator=@IdUtilizator)='Admin'
			RaisError('Nu este permisa eliminarea utilizatorului de tip administrator.',16,1)
		Else
			DELETE [dbo].[Lista_Utilizatori] WHERE IdUtilizator=@IdUtilizator
	end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaUtilizatori_Delete]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaUtilizatori_Insert
( @AUTO_IdUtilizator int OUT,
  @Utilizator varchar(50) ,
  @Parola varchar(10) ,
  @TipUser varchar(50) 
)
As 
	If Exists(Select IdUtilizator From [dbo].[Lista_Utilizatori] Where Utilizator=@Utilizator)
		RaisError('Nu este permisa inserarea acestui utilizator, deoarece un utilizator cu acest nume deja exista.',16,1)
	Else
	   Begin
		INSERT INTO [dbo].[Lista_Utilizatori] (Utilizator, Parola, TipUser)
		VALUES (@Utilizator, @Parola, @TipUser)
	
		Select @AUTO_IdUtilizator = @@IDENTITY
	   End
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaUtilizatori_Insert]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaUtilizatori_Select (@IdUtilizator int)
As 
	SELECT TOP 1000 a.IdUtilizator, a.Utilizator, a.Parola, a.TipUser
	FROM 	[dbo].[Lista_Utilizatori] a 
	WHERE a.IdUtilizator=@IdUtilizator
	For XML AUTO
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaUtilizatori_Select]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaUtilizatori_SelectList
As 
	SELECT TOP 1000 a.IdUtilizator, a.Utilizator, a.Parola, a.TipUser
	FROM 	[dbo].[Lista_Utilizatori] a 
	Order By a.Utilizator	
	For XML AUTO
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaUtilizatori_SelectList]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE ListaUtilizatori_Update
( @IdUtilizator int ,
  @Utilizator varchar(50) ,
  @Parola varchar(10) ,
  @TipUser varchar(50)	
)

As 
	UPDATE [dbo].[Lista_Utilizatori] 
	Set Utilizator=@Utilizator, Parola=@Parola, TipUser=@TipUser  
	WHERE IdUtilizator=@IdUtilizator
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ListaUtilizatori_Update]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE PacientDiagnosticSecundar_Delete (@IdPacientDiagnosticSecundar int)

As 

	DELETE [dbo].[Pacient_DiagnosticeSecundare] WHERE IdPacientDiagnosticSecundar =  @IdPacientDiagnosticSecundar
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[PacientDiagnosticSecundar_Delete]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE PacientDiagnosticSecundar_Insert
( @AUTO_IdPacientDiagnosticSecundar int OUT,
  @IdPacientFisa int ,
  @IdDiagnosticSecundar int ,
  @CodValiditate varchar (8) ,
  @NrInGrouper tinyint  
)

As 
	INSERT INTO [dbo].[Pacient_DiagnosticeSecundare] (IdPacientFisa, IdDiagnosticSecundar, CodValiditate, NrInGrouper)
	VALUES (@IdPacientFisa, @IdDiagnosticSecundar, @CodValiditate, @NrInGrouper)

	Select @AUTO_IdPacientDiagnosticSecundar = @@IDENTITY
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[PacientDiagnosticSecundar_Insert]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE PacientDiagnosticSecundar_PacientFisaRelated (@IdPacientFisa int)

As 

	SELECT TOP 1000 a.IdPacientDiagnosticSecundar, a.IdPacientFisa, a.IdDiagnosticSecundar, b.CodDiagnostic As CodDiagnosticSecundar, b.NumeDiagnostic As DenDiagnosticSecundar, 
		a.CodValiditate, a.NrInGrouper
	FROM 	[dbo].[Pacient_DiagnosticeSecundare] a Inner Join [dbo].[Lista_Diagnostice] b On a.IdDiagnosticSecundar=b.IdDiagnostic
	WHERE a.IdPacientFisa =  @IdPacientFisa 
	ORDER BY a.IdPacientDiagnosticSecundar
	For XML AUTO
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[PacientDiagnosticSecundar_PacientFisaRelated]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE PacientDiagnosticSecundar_Update
( @IdPacientDiagnosticSecundar int ,
  @IdPacientFisa int ,
  @IdDiagnosticSecundar int ,
  @CodValiditate varchar (8) ,
  @NrInGrouper tinyint
)
As 

	UPDATE [dbo].[Pacient_DiagnosticeSecundare] Set 
	    IdPacientFisa = @IdPacientFisa,
	    IdDiagnosticSecundar = @IdDiagnosticSecundar,
	    CodValiditate = @CodValiditate,
	    NrInGrouper = @NrInGrouper 
	WHERE IdPacientDiagnosticSecundar =  @IdPacientDiagnosticSecundar
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[PacientDiagnosticSecundar_Update]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE PacientFisa_AnulareSelectieExport

As 
	UPDATE [dbo].[Pacient_Fisa]  Set Flag_SelectieExport=0
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[PacientFisa_AnulareSelectieExport]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE PacientFisa_Delete(@IdPacientFisa int)

As 

	--sterg Pacient_Grouper
	DELETE [dbo].[Pacient_Grouper] WHERE (IdPacientFisa=@IdPacientFisa)
	
	--sterg Pacient_Fisa
	DELETE [dbo].[Pacient_Fisa] WHERE (IdPacientFisa=@IdPacientFisa)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[PacientFisa_Delete]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE PacientFisa_Insert
( 
  --Parametrii pentru Pacient_Fisa
  @AUTO_IdPacientFisa int OUT,
  @AUTO_Stamp varchar(30) OUT,
  @Caz_ID char(52) ,
  @IdSpital int ,
  @IdSectie int ,
  @NumarSectie int ,
  @IdTip tinyint, 
  @NFO int ,
  @Nume varchar (20) ,
  @Prenume varchar (20) ,
  @CNP varchar (13) ,
  @IdJudet int ,
  @IdLocalitate int ,
  @DataNastere datetime ,
  @DataInternare datetime ,
  @OraInternare datetime ,
  @DataExternare datetime ,
  @OraExternare datetime ,
  @IdTipAsigurare tinyint ,
  @IdCAS int ,
  @IdJudetCAS int ,
  @Sex bit ,
  @GreutateNastere smallint ,
  @IdTipInternare tinyint ,
  @IdTipExternare tinyint ,
  @IdStareExternare tinyint ,
  @IdMedic int ,
  @IdMedicOperator int ,
  @IdMedicMana2 int ,
  @IdDiagnosticLaInternare int ,
  @IdDiagnosticPrincipalCod1 int ,
  @IdDiagnosticPrincipalCod2 int ,
  @IdInterventieChirurgicala int ,
  @DataInterventieChirurgicalaPrincipala datetime ,
  @IdStareRecord tinyint ,
  @Flag_SelectieExport bit ,
  @Flag_Finalizat bit ,
  @StareValidare int ,
  @IdUtilizator int ,
  @DataOperare datetime,  
  @Data_Trimitere datetime,
  @Data_Import datetime,

  --Parametrii pentru Pacient_Grouper
  @IdDRG int ,
  @DRG_VR decimal(24,7) ,
  @Tarif int , 
  @Validitate_DRG_COD tinyint ,
  @Validitate_IntPrinc varchar (8) ,
  @Validitate_DiagPrinc_Cod1 varchar (8) ,
  @Validitate_DiagPrinc_Cod2 varchar (8) ,
  @fRC1 bit ,
  @fRC2 bit ,
  @fRC3 bit ,
  @fRC4_D bit ,
  @fRC4_S bit ,
  @fRC5 bit ,
  @Transfer_Reinternare varchar (50) ,
  @NrZileReinternare smallint ,
  @fRC6 bit ,
  @fRC7 bit ,
  @fRC8 bit ,
  @fRC9 bit ,
  @fRC10 bit ,
  @fRC11 bit ,
  @fRC12 bit ,
  @fRC13 bit ,
  @fRC14 bit ,
  @fRC15 bit ,
  @fRF2 bit ,
  @fRF3 bit ,
  @fRF4 bit ,
  @fRF5 bit ,
  @fRF6 bit ,
  @fRF7 bit ,
  @fRF8 bit ,
  @fRF9 bit ,
  @fRF10 bit
)
As 
	--trebuie sa ma asigura ca aceasta foaie de observatie este unica ca numar, data externare, sectie externare, numar sectie externare si spital
	If Exists(Select IdPacientFisa From Pacient_Fisa Where IdSpital=@IdSpital And IdSectie=@IdSectie And NumarSectie=@NumarSectie And NFO=@NFO And DataExternare=@DataExternare)
		RaisError('Nu este permisa inserarea acestei foi de observatie, deoarece o foaie de observatie cu acest numar, data externare, spital si sectie (numar si cod) exista deja.',16,1)
	Else
	   Begin
		INSERT INTO [dbo].[Pacient_Fisa] 
		(Caz_ID, IdAccident, IdSpital, IdSectie, NumarSectie, NFO, Nume, Prenume, CNP, IdLocalitate, IdJudet, DataNastere, DataInternare, OraInternare, 
		 DataExternare, OraExternare, IdTipAsigurare, IdCAS, IdJudetCAS, Sex, GreutateNastere, IdTipInternare, IdTipExternare, IdStareExternare, 
		 IdMedic, IdMedicOperator, IdMedicMana2, IdDiagnosticLaInternare, IdDiagnosticPrincipalCod1, 
		 IdDiagnosticPrincipalCod2, IdInterventieChirurgicala, DataInterventieChirurgicalaPrincipala, IdStareRecord, Flag_SelectieExport, Flag_Finalizat, 
		 StareValidare, IdUtilizator, DataOperare, Data_Trimitere, Data_Import
		)
		VALUES (@Caz_ID,
  			  @IdTip,
			  @IdSpital,
			  @IdSectie,
			  @NumarSectie,
			  @NFO,
			  @Nume,
			  @Prenume,
			  @CNP,
			  @IdLocalitate,
			  @IdJudet,
			  @DataNastere,
			  @DataInternare,
			  @OraInternare,
			  @DataExternare,
			  @OraExternare,
			  @IdTipAsigurare,
			  @IdCAS,
			  @IdJudetCAS,
			  @Sex,
			  @GreutateNastere,
			  @IdTipInternare,
			  @IdTipExternare,
			  @IdStareExternare,
			  @IdMedic,
			  @IdMedicOperator,
			  @IdMedicMana2,
			  @IdDiagnosticLaInternare,
			  @IdDiagnosticPrincipalCod1,
			  @IdDiagnosticPrincipalCod2,
			  @IdInterventieChirurgicala,
			  @DataInterventieChirurgicalaPrincipala,
			  @IdStareRecord,
			  @Flag_SelectieExport,
			  @Flag_Finalizat,
			  @StareValidare,
			  @IdUtilizator,
			  @DataOperare,
			  @Data_Trimitere,
			  @Data_Import 
			  )
	
		  Select @AUTO_IdPacientFisa = @@IDENTITY
		  Select @AUTO_Stamp = CONVERT(varchar(30), CONVERT(INT, @@DBTS), 13)
	
		--insert si in Pacient_Grouper
		INSERT INTO [dbo].[Pacient_Grouper]
		(IdPacientFisa, IdDRG, DRG_VR, Tarif, Validitate_DRG_COD, Validitate_IntPrinc, Validitate_DiagPrinc_Cod1, Validitate_DiagPrinc_Cod2,
		 fRC1, fRC2, fRC3, fRC4_D, fRC4_S, fRC5, Transfer_Reinternare, NrZileReinternare, fRC6, fRC7, fRC8, fRC9, fRC10, fRC11, fRC12, fRC13,
		 fRC14, fRC15, fRF2, fRF3, fRF4, fRF5, fRF6, fRF7, fRF8, fRF9, fRF10
		)
		VALUES (@AUTO_IdPacientFisa ,
			  @IdDRG ,
			  @DRG_VR, 
		  	  @Tarif,
			  @Validitate_DRG_COD ,
			  @Validitate_IntPrinc ,
			  @Validitate_DiagPrinc_Cod1 ,
			  @Validitate_DiagPrinc_Cod2 ,
			  @fRC1 ,
			  @fRC2 ,
			  @fRC3 ,
			  @fRC4_D ,
			  @fRC4_S ,
			  @fRC5 ,
			  @Transfer_Reinternare ,
			  @NrZileReinternare ,
			  @fRC6 ,
			  @fRC7 ,
			  @fRC8 ,
			  @fRC9 ,
			  @fRC10 ,
			  @fRC11 ,
			  @fRC12 ,
			  @fRC13 ,
			  @fRC14 ,
			  @fRC15 ,
			  @fRF2 ,
			  @fRF3 ,
			  @fRF4 ,
			  @fRF5 ,
			  @fRF6 ,
			  @fRF7 ,
			  @fRF8 ,
			  @fRF9 ,
			  @fRF10
			)
	   End
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[PacientFisa_Insert]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE PacientFisa_Select (@IdPacientFisa int)
As 

	SELECT TOP 1000 a.IdPacientFisa, a.Caz_ID, a.IdSpital, k.Spital_ID, h.IdSectieFiltrat, a.IdSectie, i.Sectie_ID, 
		          a.NumarSectie, a.IdAccident As IdTip, a.NFO, a.Nume, a.Prenume, a.CNP, 
		          IsNull(a.IdJudet, -1) As IdJudet, IsNull(a.IdLocalitate, -1) As IdLocalitate, 
		          b.LocalitateUrbanRural As UR, a.DataNastere, a.DataInternare, a.OraInternare, 
		          a.DataExternare, a.OraExternare, 
		          Case When DateDiff(day, a.OraInternare, a.OraExternare)=0 Then 1 Else DateDiff(day, a.OraInternare, a.OraExternare) End As NrZileSpitalizare,
		          a.IdTipAsigurare, IsNull(a.IdCAS, -1) As IdCAS, IsNull(a.IdJudetCAS, -1) As IdJudetCAS, 
		          a.Sex, a.GreutateNastere, a.IdTipInternare, a.IdTipExternare, a.IdStareExternare, 
		          a.IdMedic, c.IdSpecialitate, IsNull(a.IdMedicOperator,-1) As IdMedicOperator, IsNull(a.IdMedicMana2, -1) As IdMedicMana2, 
		          a.IdDiagnosticLaInternare, d.CodDiagnostic As CodDiagnosticLaInternare, d.NumeDiagnostic As DenDiagnosticLaInternare,
		          a.IdDiagnosticPrincipalCod1, e.CodDiagnostic As CodDiagnosticPrincipalCod1, e.NumeDiagnostic As DenDiagnosticPrincipalCod1,
		          IsNull(a.IdDiagnosticPrincipalCod2, -1) As IdDiagnosticPrincipalCod2, 
		          a.IdInterventieChirurgicala,  f.CodProcedura As CodInterventieChirurgicala, f.DenProcedura As DenInterventieChirurgicala,
		          a.DataInterventieChirurgicalaPrincipala, a.IdStareRecord, 
		          a.Flag_SelectieExport, a.Flag_Finalizat, IsNull(a.StareValidare,-1) As StareValidare, a.IdUtilizator, a.DataOperare, a.Data_Trimitere, a.Data_Import,
	                       CONVERT(varchar(30), CONVERT(INT, a.Stamp),13) Stamp,
		          IsNull(x.IdDRG,-1) As IdDRG, IsNull(g.IdMDC,-1) As IdMDC, g.MC_COD As MC, x.DRG_VR, x.Tarif, x.Validitate_DRG_COD, x.Validitate_IntPrinc, 
		          x.Validitate_DiagPrinc_Cod1, x.Validitate_DiagPrinc_Cod2, x.fRC1, x.fRC2, x.fRC3, x.fRC4_D, x.fRC4_S, x.fRC5, x.Transfer_Reinternare, 
		          x.NrZileReinternare, x.fRC6, x.fRC7, x.fRC8, x.fRC9, x.fRC10, x.fRC11, x.fRC12, x.fRC13, x.fRC14, x.fRC15, x.fRF2, x.fRF3,
		          x.fRF4, x.fRF5, x.fRF6, x.fRF7, x.fRF8, x.fRF9, x.fRF10
	FROM 	[dbo].[Pacient_Fisa] a
		Inner Join  [dbo].[Pacient_Grouper] x On a.IdPacientFisa=x.IdPacientFisa
		Left Join [dbo].[Lista_Localitati] b On a.IdLocalitate=b.IdLocalitate and a.IdJudet=b.IdJudet
		Inner Join [dbo].[Lista_Medici] c On a.IdMedic=c.IdMedic And a.IdSpital=c.IdSpital
		Left Join [dbo].[Lista_Diagnostice] d On a.IdDiagnosticLaInternare=d.IdDiagnostic
		Inner Join [dbo].[Lista_Diagnostice] e On a.IdDiagnosticPrincipalCod1=e.IdDiagnostic 
		Left Join [dbo].[Nom_Proceduri] f On a.IdInterventieChirurgicala=f.IdProcedura
		Left Join [dbo].[Lista_CoduriDRG] g On x.IdDRG=g.IdDRG
		Inner Join [dbo].[Lista_Sectii___Filtrat] h On a.IdSpital=h.IdSpital And a.IdSectie=h.IdSectie And a.NumarSectie=h.NumarSectie
		Inner Join [dbo].[Lista_Sectii] i On h.IdSectie=i.IdSectie
		Inner Join [dbo].[Lista_Spitale___Filtrat] j On a.IdSpital=j.IdSpital
		Inner Join [dbo].[Lista_Spitale] k On j.IdSpital=k.IdSpital
	WHERE (a.IdPacientFisa =  @IdPacientFisa) 

	For XML AUTO
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[PacientFisa_Select]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE PacientFisa_SelectList

AS

	SELECT TOP 1000 a.IdPacientFisa, a.Caz_ID, a.IdSpital, k.Spital_ID, h.IdSectieFiltrat, a.IdSectie, i.Sectie_ID, a.NumarSectie, a.IdAccident As IdTip, a.NFO, a.Nume, a.Prenume, a.CNP, 
		          IsNull(a.IdJudet, -1) As IdJudet, IsNull(a.IdLocalitate, -1) As IdLocalitate, 
		          b.LocalitateUrbanRural As UR, a.DataNastere, a.DataInternare, a.OraInternare, 
		          a.DataExternare, a.OraExternare, 
		          Case When DateDiff(day, a.OraInternare, a.OraExternare)=0 Then 1 Else DateDiff(day, a.OraInternare, a.OraExternare) End As NrZileSpitalizare,
		          a.IdTipAsigurare, IsNull(a.IdCAS, -1) As IdCAS, IsNull(a.IdJudetCAS, -1) As IdJudetCAS, 
		          a.Sex, a.GreutateNastere, a.IdTipInternare, a.IdTipExternare, a.IdStareExternare, 
		          a.IdMedic, c.IdSpecialitate, IsNull(a.IdMedicOperator,-1) As IdMedicOperator, IsNull(a.IdMedicMana2, -1) As IdMedicMana2, 
		          a.IdDiagnosticLaInternare, d.CodDiagnostic As CodDiagnosticLaInternare, d.NumeDiagnostic As DenDiagnosticLaInternare,
		          a.IdDiagnosticPrincipalCod1, e.CodDiagnostic As CodDiagnosticPrincipalCod1, e.NumeDiagnostic As DenDiagnosticPrincipalCod1,
		          IsNull(a.IdDiagnosticPrincipalCod2, -1) As IdDiagnosticPrincipalCod2, 
		          a.IdInterventieChirurgicala,  f.CodProcedura As CodInterventieChirurgicala, f.DenProcedura As DenInterventieChirurgicala,
		          a.DataInterventieChirurgicalaPrincipala, a.IdStareRecord, 
		          a.Flag_SelectieExport, a.Flag_Finalizat, IsNull(a.StareValidare,-1) As StareValidare, a.IdUtilizator, a.DataOperare, a.Data_Trimitere, 
	                       CONVERT(varchar(30), CONVERT(INT, a.Stamp),13) Stamp  
	FROM 	[dbo].[Pacient_Fisa] a 
		Left Join [dbo].[Lista_Localitati] b On a.IdLocalitate=b.IdLocalitate and a.IdJudet=b.IdJudet
		Inner Join [dbo].[Lista_Medici] c On a.IdMedic=c.IdMedic And a.IdSpital=c.IdSpital
		Left Join [dbo].[Lista_Diagnostice] d On a.IdDiagnosticLaInternare=d.IdDiagnostic
		Inner Join [dbo].[Lista_Diagnostice] e On a.IdDiagnosticPrincipalCod1=e.IdDiagnostic 
		Left Join [dbo].[Nom_Proceduri] f On a.IdInterventieChirurgicala=f.IdProcedura
		Inner Join [dbo].[Lista_Sectii___Filtrat] h On a.IdSpital=h.IdSpital And a.IdSectie=h.IdSectie And a.NumarSectie=h.NumarSectie
		Inner Join [dbo].[Lista_Sectii] i On h.IdSectie=i.IdSectie
		Inner Join [dbo].[Lista_Spitale___Filtrat] j On a.IdSpital=j.IdSpital
		Inner Join [dbo].[Lista_Spitale] k On j.IdSpital=k.IdSpital


For XML AUTO
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[PacientFisa_SelectList]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE PacientFisa_Update
( 
  --parametrii pentru Pacient_Fisa
  @IdPacientFisa int ,
  @Caz_ID varchar (52) ,
  @IdSpital int ,
  @IdSectie int ,
  @NumarSectie int ,
  @IdTip tinyint, 
  @NFO int ,
  @Nume varchar (20) ,
  @Prenume varchar (20) ,
  @CNP varchar (13) ,
  @IdJudet int ,
  @IdLocalitate int ,
  @DataNastere datetime ,
  @DataInternare datetime ,
  @OraInternare datetime ,
  @DataExternare datetime ,
  @OraExternare datetime ,
  @IdTipAsigurare tinyint ,
  @IdCAS int ,
  @IdJudetCAS int ,
  @Sex bit ,
  @GreutateNastere smallint ,
  @IdTipInternare tinyint ,
  @IdTipExternare tinyint ,
  @IdStareExternare tinyint ,
  @IdMedic int ,
  @IdMedicOperator int ,
  @IdMedicMana2 int ,
  @IdDiagnosticLaInternare int ,
  @IdDiagnosticPrincipalCod1 int ,
  @IdDiagnosticPrincipalCod2 int ,
  @IdInterventieChirurgicala int ,
  @DataInterventieChirurgicalaPrincipala datetime ,
  @IdStareRecord tinyint ,
  @Flag_SelectieExport bit ,
  @Flag_Finalizat bit ,
  @StareValidare int ,
  @IdUtilizator int ,
  @DataOperare datetime ,
  @Data_Trimitere datetime ,
  @Data_Import datetime,
  @Stamp varchar(30) OUT,

  --parametrii pentru Pacient_Grouper
  @IdDRG int ,
  @DRG_VR decimal(24,7) ,
  @Tarif int , 
  @Validitate_DRG_COD tinyint ,
  @Validitate_IntPrinc varchar (8) ,
  @Validitate_DiagPrinc_Cod1 varchar (8) ,
  @Validitate_DiagPrinc_Cod2 varchar (8) ,
  @fRC1 bit ,
  @fRC2 bit ,
  @fRC3 bit ,
  @fRC4_D bit ,
  @fRC4_S bit ,
  @fRC5 bit ,
  @Transfer_Reinternare varchar (50) ,
  @NrZileReinternare smallint ,
  @fRC6 bit ,
  @fRC7 bit ,
  @fRC8 bit ,
  @fRC9 bit ,
  @fRC10 bit ,
  @fRC11 bit ,
  @fRC12 bit ,
  @fRC13 bit ,
  @fRC14 bit ,
  @fRC15 bit ,
  @fRF2 bit ,
  @fRF3 bit ,
  @fRF4 bit ,
  @fRF5 bit ,
  @fRF6 bit ,
  @fRF7 bit ,
  @fRF8 bit ,
  @fRF9 bit ,
  @fRF10 bit
)

As 

	--trebuie sa ma asigura ca modificarile aduse aceastei foi de observatie nu incalca constrangerea: numar, data externare, sectie externare, numar sectie externare si spital
	If Exists(Select IdPacientFisa From Pacient_Fisa Where IdSpital=@IdSpital And IdSectie=@IdSectie And NumarSectie=@NumarSectie And NFO=@NFO And DataExternare=@DataExternare And IdPacientFisa<>@IdPacientFisa)
		RaisError('Nu este permisa modificarea acestei foi de observatie, deoarece coduce la o foaie de observatie existenta deja cu acest numar, data externare, spital si sectie (numar si cod).',16,1)
	Else
	   Begin
		Declare @NewStamp varchar(30)
		Select 	@NewStamp=CONVERT(varchar(30), CONVERT(INT, Stamp),13) From [dbo].[Pacient_Fisa] 
		Where 	IdPacientFisa =  @IdPacientFisa
		
		If RTrim(@NewStamp) != RTrim(@Stamp)
			Begin
				RAISERROR(51000, 16, 1)
				ROLLBACK TRANSACTION
			End
		Else
			Begin
				--Update Pacient_Fisa
				UPDATE [dbo].[Pacient_Fisa] Set 
				    Caz_ID = @Caz_ID,
				    IdAccident=@IdTip, 
				    IdSpital = @IdSpital,
				    IdSectie = @IdSectie,
				    NumarSectie = @NumarSectie,
				    NFO = @NFO,
				    Nume = @Nume,
				    Prenume = @Prenume,
				    CNP = @CNP,
				    IdLocalitate = @IdLocalitate,
				    IdJudet = @IdJudet,
				    DataNastere = @DataNastere,
				    DataInternare = @DataInternare,
				    OraInternare = @OraInternare,
				    DataExternare = @DataExternare,
				    OraExternare = @OraExternare,
				    IdTipAsigurare = @IdTipAsigurare,
				    IdCAS = @IdCAS,
				    IdJudetCAS = @IdJudetCAS,
				    Sex = @Sex,
				    GreutateNastere = @GreutateNastere,
				    IdTipInternare = @IdTipInternare,
				    IdTipExternare = @IdTipExternare,
				    IdStareExternare = @IdStareExternare,
				    IdMedic = @IdMedic,
				    IdMedicOperator = @IdMedicOperator,
				    IdMedicMana2 = @IdMedicMana2,
				    IdDiagnosticLaInternare = @IdDiagnosticLaInternare,
				    IdDiagnosticPrincipalCod1 = @IdDiagnosticPrincipalCod1,
				    IdDiagnosticPrincipalCod2 = @IdDiagnosticPrincipalCod2,
				    IdInterventieChirurgicala = @IdInterventieChirurgicala,
				    DataInterventieChirurgicalaPrincipala = @DataInterventieChirurgicalaPrincipala,
				    IdStareRecord = @IdStareRecord,
				    Flag_SelectieExport = @Flag_SelectieExport,
				    Flag_Finalizat = @Flag_Finalizat,
				    StareValidare = @StareValidare,
				    IdUtilizator = @IdUtilizator,
				    DataOperare = @DataOperare, 
				    Data_Trimitere = @Data_Trimitere,
				    Data_Import = @Data_Import
				WHERE IdPacientFisa = @IdPacientFisa
		
				--Update Pacient_Grouper
				UPDATE [dbo].[Pacient_Grouper] Set 
				    IdDRG = @IdDRG,
				    DRG_VR=@DRG_VR,
		   		    Tarif=@Tarif, 
				    Validitate_DRG_COD = @Validitate_DRG_COD,
		
				    Validitate_IntPrinc = @Validitate_IntPrinc,
				    Validitate_DiagPrinc_Cod1 = @Validitate_DiagPrinc_Cod1,
				    Validitate_DiagPrinc_Cod2 = @Validitate_DiagPrinc_Cod2,
				    fRC1 = @fRC1,
				    fRC2 = @fRC2,
				    fRC3 = @fRC3,
				    fRC4_D = @fRC4_D,
				    fRC4_S = @fRC4_S,
				    fRC5 = @fRC5,
				    Transfer_Reinternare = @Transfer_Reinternare,
				    NrZileReinternare = @NrZileReinternare,
				    fRC6 = @fRC6,
				    fRC7 = @fRC7,
				    fRC8 = @fRC8,
				    fRC9 = @fRC9,
				    fRC10 = @fRC10,
				    fRC11 = @fRC11,
				    fRC12 = @fRC12,
				    fRC13 = @fRC13,
				    fRC14 = @fRC14,
				    fRC15 = @fRC15,
				    fRF2 = @fRF2,
				    fRF3 = @fRF3,
				    fRF4 = @fRF4,
				    fRF5 = @fRF5,
				    fRF6 = @fRF6,
				    fRF7 = @fRF7,
				    fRF8 = @fRF8,
				    fRF9 = @fRF9,
				    fRF10 = @fRF10
				WHERE IdPacientFisa = @IdPacientFisa
								
			End	
		
		Select @Stamp=CONVERT(varchar(30), CONVERT(INT, @@DBTS),13)
	   End
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[PacientFisa_Update]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE PacientProcedura_Delete 
 (@IdPacientProcedura int)
As 

	Delete [dbo].[Pacient_Proceduri] Where IdPacientProcedura=@IdPacientProcedura
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[PacientProcedura_Delete]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE PacientProcedura_Insert
( @AUTO_IdPacientProcedura int OUT,
  @IdPacientFisa int ,
  @IdProcedura int ,
  @Cantitate tinyint ,
  @CodValiditate varchar (8) ,
  @NrInGrouper tinyint
)

As 

	INSERT INTO [dbo].[Pacient_Proceduri] (IdPacientFisa, IdProcedura, Cantitate, CodValiditate, NrInGrouper)
	VALUES (@IdPacientFisa, @IdProcedura, @Cantitate, @CodValiditate, @NrInGrouper)

	Select @AUTO_IdPacientProcedura = @@IDENTITY
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[PacientProcedura_Insert]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE PacientProcedura_PacientFisaRelated (@IdPacientFisa int)

As 

	SELECT TOP 1000 a.IdPacientProcedura, a.IdPacientFisa, a.IdProcedura, b.CodProcedura, b.DenProcedura, 
		a.Cantitate, a.CodValiditate, a.NrInGrouper
	FROM 	[dbo].[Pacient_Proceduri] a Inner Join [dbo].[Nom_Proceduri] b On a.IdProcedura=b.IdProcedura
	WHERE a.IdPacientFisa =  @IdPacientFisa 
	ORDER BY a.IdPacientProcedura
	For XML AUTO
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[PacientProcedura_PacientFisaRelated]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE PacientProcedura_Update
( @IdPacientProcedura int ,
  @IdPacientFisa int ,
  @IdProcedura int ,
  @Cantitate tinyint ,
  @CodValiditate varchar (8) ,
  @NrInGrouper tinyint
)
As 
	UPDATE [dbo].[Pacient_Proceduri] Set 
	    IdPacientFisa = @IdPacientFisa,
	    IdProcedura = @IdProcedura,
	    Cantitate = @Cantitate,
	    CodValiditate = @CodValiditate,
	    NrInGrouper = @NrInGrouper 
	WHERE IdPacientProcedura =  @IdPacientProcedura
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[PacientProcedura_Update]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE PacientProcedura_Verificare
( 
  @CodProcedura varchar(8) OUT,
  @IdPacientFisa int
)
As 
	Set @CodProcedura=
	(
	Select 	Top 1 b.CodProcedura
	From 	dbo.Pacient_Proceduri a (NoLock) Inner Join Nom_Proceduri b On a.IdProcedura=b.IdProcedura
	Where 	a.IdPacientFisa=@IdPacientFisa
	Group By b.CodProcedura
	Having Count(*)>1
	)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[PacientProcedura_Verificare]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE PacientSectie_Delete (@IdPacientSectie int)

As 

	DELETE [dbo].[Pacient_Sectii] WHERE IdPacientSectie =  @IdPacientSectie
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[PacientSectie_Delete]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE PacientSectie_Insert
( @AUTO_IdPacientSectie int OUT,
  @IdPacientFisa int ,
  @IdSpital int ,
  @IdSectie int ,
  @NumarSectie int ,
  @DataInternare datetime ,
  @DataExternare datetime ,
  @NrZile int  
)

As 
	INSERT INTO [dbo].[Pacient_Sectii] (IdPacientFisa, IdSpital, IdSectie, NumarSectie, DataInternare, DataExternare, NrZile)
	VALUES (@IdPacientFisa, @IdSpital, @IdSectie, @NumarSectie, @DataInternare, @DataExternare, @NrZile)

	Select @AUTO_IdPacientSectie = @@IDENTITY
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[PacientSectie_Insert]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE PacientSectie_PacientFisaRelated (@IdPacientFisa int)

As 

	SELECT TOP 1000 a.IdPacientSectie, a.IdPacientFisa, a.IdSpital, b.IdSectieFiltrat, a.IdSectie, c.Sectie_ID, c.SectieNume As DenSectie, 
		          a.NumarSectie, a.DataInternare, a.DataExternare, a.NrZile 
	FROM 	[dbo].[Pacient_Sectii] a Inner Join [dbo].[Lista_Sectii___Filtrat] b On a.IdSpital=b.IdSpital AND a.IdSectie=b.IdSectie And a.NumarSectie=b.NumarSectie
		Inner Join [dbo].[Lista_Sectii] c On b.IdSectie=c.IdSectie
	WHERE a.IdPacientFisa =  @IdPacientFisa
	Order By a.IdPacientSectie 
	For XML AUTO
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[PacientSectie_PacientFisaRelated]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE PacientSectie_Update
( @IdPacientSectie int ,
  @IdPacientFisa int ,
  @IdSpital int ,  
  @IdSectie int ,
  @NumarSectie int ,
  @DataInternare datetime ,
  @DataExternare datetime ,
  @NrZile int
)
As 

	UPDATE [dbo].[Pacient_Sectii] Set 
	    IdPacientFisa = @IdPacientFisa,
	    IdSpital = @IdSpital, 
	    IdSectie = @IdSectie,
	    NumarSectie = @NumarSectie,
	    DataInternare = @DataInternare,
	    DataExternare = @DataExternare,
	    NrZile = @NrZile 
	WHERE IdPacientSectie =  @IdPacientSectie
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[PacientSectie_Update]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE Rapoarte_SelectList 
AS
Select 	a.IdRaport, a.Raport, a.IdParinte, a.TipRaport, a.Parametrii, a.Locatie, a.Observatii
From	[dbo].[Rapoarte] a
FOR XML AUTO
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[Rapoarte_SelectList]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE SetariApp_SelectList

As 

SELECT TOP 1000 a.IdSetare, a.DenSetare, a.Valoare
FROM [dbo].[SetariApp] a

For XML AUTO
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[SetariApp_SelectList]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE SetariApp_Update
( @IdSetare int,
  @DenSetare varchar(100),
  @Valoare bit 
)

As 
	Update 	[dbo].[SetariApp] 
	Set 	DenSetare=@DenSetare, Valoare=@Valoare 
	Where 	IdSetare=@IdSetare
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[SetariApp_Update]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rDiagnostice(@IdClasaDiagnostic int)

As 

	Select	c.DenClasaDiagnostic, b.DenSubClasaDiagnostic, a.CodDiagnostic, a.NumeDiagnostic
	From 	Lista_Diagnostice a Inner Join Lista_Diagnostice___Subclase b On a.IdSubClasaDiagnostic=b.IdSubClasaDiagnostic
		Inner Join Lista_Diagnostice___Clase c On b.IdClasaDiagnostic=c.IdClasaDiagnostic
	Where 	b.IdClasaDiagnostic=@IdClasaDiagnostic
	Order By b.DenSubClasaDiagnostic, a.CodDiagnostic
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rDiagnostice]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rFoaieObservatie 
(@IdSpital int, @IdSectie int, @NumarSectie int, @NFO int, @strDataExternare char(10))

AS

Set DateFormat 'mdy'

Declare @DataExternare smalldatetime
Set @DataExternare = convert(smalldatetime, @strDataExternare)


Select 	a.IdPacientFisa, a.Caz_ID, a.Sex, a.CNP, DateDiff(Year, a.DataNastere, a.DataExternare) As Varsta,  a.Nume, a.Prenume, 
	q.StareRecord, b1.Spital_ID As CodINCDS, b1.SpitalDenumire, 
	c1.SectieNume, Case When c1.Acut=1 Then 'Acuti' Else 'Cronici' End As TipSectie, a.NumarSectie, 
	a.NFO, a.DataExternare, a.DataInternare, k.TipInternare, i.StareExternare, j.TipExternare, l.TipAsigurare, 
	e.CodDiagnostic As CodDiagnosticLaInternare, e.NumeDiagnostic As DiagnosticInternareNume,
	x1.CodDiagnostic As CodDiagnosticPrincipalCOD1, x1.NumeDiagnostic As DiagnosticPrincipal1Nume, 
	Case When ( Left(Right(m.Validitate_DiagPrinc_Cod1,2),1) In ('2','6', 'A','D')  OR Right(m.Validitate_DiagPrinc_Cod1,1) In ('2','3','A','B') ) Then 'Invalid' Else '' End As Validitate_DiagPrinc_Cod1,
	x2.CodDiagnostic As CodDiagnosticPrincipalCOD2, x2.NumeDiagnostic As DiagnosticPrincipal2Nume, 
	Case When ( Left(Right(m.Validitate_DiagPrinc_Cod2,2),1) In ('2','6', 'A','D')  OR Right(m.Validitate_DiagPrinc_Cod2,1) In ('2','3','A','B') ) Then 'Invalid' Else '' End As Validitate_DiagPrinc_Cod2,
	h.CodProcedura As CodInterventieChirurgicalaPrincipala, h.DenProcedura As InterventieChirurgicalaPrincipalaNume, 
	Case When Right(m.Validitate_IntPrinc,1) In ('2','3','A','B') Then 'Invalid' Else '' End As Validitate_IntPrinc, 
	Case When a.StareValidare=1 Then 'Validat' When a.StareValidare=0 Then 'NeValidat' Else 'Nesupus validarii' End As StareValidare, 
	fRC1=0, m.fRC2, m.fRC3, m.fRC4_D, m.fRC4_S, m.fRC5, m.fRC6, m.fRC7, m.fRC8, m.fRC9, m.fRC10, m.fRC11, m.fRF2, m.fRF3, m.fRF5, m.fRF6, m.fRF7,
	n.CodDRG, n.DenDRG, Cast(n.RV_MD As Money) As RV_MD,
	d.Tip, d.Cod, d.Denumire, d.CodValiditate,
	a.DataOperare
From 	Pacient_Fisa a 
	Inner Join Lista_Spitale___Filtrat b On a.IdSpital=b.IdSpital Inner Join Lista_Spitale b1 On b.IdSpital=b1.IdSpital
	Inner Join Lista_Sectii___Filtrat c On a.IdSpital=c.IdSpital And a.IdSectie=c.IdSectie And a.NumarSectie=c.NumarSectie Inner Join Lista_Sectii c1 On c.IdSectie=c1.IdSectie
	Left Join Lista_Diagnostice e On a.IdDiagnosticLaInternare=e.IdDiagnostic
	Left Join Lista_Diagnostice___Cod1Cod2 f On a.IdDiagnosticPrincipalCod1=f.IdDiagnostic1 And IsNull(a.IdDiagnosticPrincipalCod2,0)=IsNull(f.IdDiagnostic2,0)
		Left Join Lista_Diagnostice x1 On f.IdDiagnostic1=x1.IdDiagnostic
		Left Join Lista_Diagnostice x2 On f.IdDiagnostic2=x2.IdDiagnostic
	Left Join Nom_Proceduri h On a.IdInterventieChirurgicala=h.IdProcedura
	Inner Join Lista_StareExternare i On a.IdStareExternare=i.IdStareExternare
	Inner Join Lista_TipExternare j On a.IdTipExternare=j.IdTipExternare
	Inner Join Lista_TipInternare k On a.IdTipInternare=k.IdTipInternare
	Inner Join Lista_TipAsigurare l On a.IdTipAsigurare=l.IdTipAsigurare
	Inner Join Pacient_Grouper m On a.IdPacientFisa=m.IdPacientFisa
	Left Join Lista_CoduriDRG n On m.IdDRG=n.IdDRG
	Inner Join Lista_StareRecord q On a.IdStareRecord=q.IdStareRecord
	Left Join
	(
		Select 	a.IdPacientFisa, a.Tip, a.Cod, a.Denumire, a.CodValiditate
		From
		(
		Select 	a.IdPacientFisa, Tip='0Proceduri', c.CodProcedura As Cod, c.DenProcedura As Denumire, b.Cantitate, 
			Case When Right(b.CodValiditate,1) In ('2','3','A','B') Then 'Invalid' Else '' End As CodValiditate 
		From 	Pacient_Fisa a Inner Join Pacient_Proceduri b On a.IdPacientFisa=b.IdPacientFisa Inner Join Nom_Proceduri c On b.IdProcedura=c.IdProcedura
		Where	a.IdSpital=@IdSpital And a.IdSectie=@IdSectie And a.NumarSectie=@NumarSectie And a.NFO=@NFO And a.DataExternare=@DataExternare

		Union All
		
		Select 	a.IdPacientFisa, Tip='1Diagnostice_Secundare', c.CodDiagnostic As Cod, c.NumeDiagnostic As Denumire, Null As Cantitate,
			Case When ( Left(Right(b.CodValiditate,2),1) In ('2','6', 'A','D') OR Right(b.CodValiditate,1) In ('2','3','A','B') ) Then 'Invalid' Else '' End As CodValiditate 
		From 	Pacient_Fisa a Inner Join Pacient_DiagnosticeSecundare b On a.IdPacientFisa=b.IdPacientFisa Inner Join Lista_Diagnostice c On b.IdDiagnosticSecundar=c.IdDiagnostic
		Where	a.IdSpital=@IdSpital And a.IdSectie=@IdSectie And a.NumarSectie=@NumarSectie And a.NFO=@NFO And a.DataExternare=@DataExternare
		) a
	) d On a.IdPacientFisa=d.IdPacientFisa

Where	a.IdSpital=@IdSpital And a.IdSectie=@IdSectie And a.NumarSectie=@NumarSectie And a.NFO=@NFO And a.DataExternare=@DataExternare
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rFoaieObservatie]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rMedici
As 

	Select 	a.IdSpital, c.Spital_ID, c.SpitalDenumire,
		a.IdMedic, a.CodMedic, a.DenMedic, a.IdSpecialitate, d.DenSpecialitate 
	from 	[dbo].[Lista_Medici] a Inner Join [dbo].[Lista_Spitale___Filtrat] b On a.IdSpital=b.IdSpital
		Inner Join [dbo].[Lista_Spitale] c On b.IdSpital=c.IdSpital
		Inner Join [dbo].[Lista_Specialitati] d On a.IdSpecialitate=d.IdSpecialitate
	Order By c.Spital_ID, a.DenMedic
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rMedici]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rPacientiAnulati (@IdSpital int, @strDataStart char(10), @strDataEnd char(10))

AS

Set DateFormat 'mdy'

Declare @DataStart smalldatetime, @DataEnd smalldatetime
Set @DataStart = convert(smalldatetime, @strDataStart)
Set @DataEnd = convert(smalldatetime, @strDataEnd)


Select 	a.IdPacientFisa, a.Caz_ID, b1.Spital_ID As CodINCDS, b1.SpitalDenumire, c1.Sectie_ID, c1.SectieNume, a.NumarSectie, 
	a.NFO, a.DataExternare, a.DataInternare
From 	[dbo].[Pacient_Fisa] a 
	Inner Join [dbo].[Lista_Spitale___Filtrat] b On a.IdSpital=b.IdSpital Inner Join [dbo].[Lista_Spitale] b1 On b.IdSpital=b1.IdSpital
	Inner Join [dbo].[Lista_Sectii___Filtrat] c On a.IdSpital=c.IdSpital And a.IdSectie=c.IdSectie And a.NumarSectie=c.NumarSectie Inner Join [dbo].[Lista_Sectii] c1 On c.IdSectie=c1.IdSectie
Where	a.IdSpital=@IdSpital And 
	(a.DataExternare between @DataStart And @DataEnd) And
	a.IdStareRecord=3
Order By c1.Sectie_ID, a.NumarSectie, a.DataExternare
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rPacientiAnulati]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rPacientiClasificare (@IdSpital int, @strDataStart char(10), @strDataEnd char(10))

AS

Set DateFormat 'mdy'

Declare @DataStart smalldatetime, @DataEnd smalldatetime
Set @DataStart = convert(smalldatetime, @strDataStart)
Set @DataEnd = convert(smalldatetime, @strDataEnd)


Select 	a.IdPacientFisa, a.Caz_ID, b1.Spital_ID As CodINCDS, b1.SpitalDenumire, c1.Sectie_ID, c1.SectieNume, a.NumarSectie, 
	a.NFO, a.DataExternare, a.DataInternare, a.StareValidare, d.StareRecord,
	f.CodDRG, f.DenDRG, f.MC_COD, e.DRG_VR, e.Tarif, f.IdMDC, g.DenMDC
From 	[dbo].[Pacient_Fisa] a 
	Inner Join [dbo].[Lista_Spitale___Filtrat] b On a.IdSpital=b.IdSpital Inner Join [dbo].[Lista_Spitale] b1 On b.IdSpital=b1.IdSpital
	Inner Join [dbo].[Lista_Sectii___Filtrat] c On a.IdSpital=c.IdSpital And a.IdSectie=c.IdSectie And a.NumarSectie=c.NumarSectie Inner Join [dbo].[Lista_Sectii] c1 On c.IdSectie=c1.IdSectie
	Inner Join [dbo].[Lista_StareRecord] d On a.IdStareRecord=d.IdstareRecord
	Inner Join [dbo].[Pacient_Grouper] e On a.IdPacientFisa=e.IdPacientFisa
	Left Join [dbo].[Lista_CoduriDRG] f On e.IdDRG=f.IdDRG
	Left Join [dbo].[Lista_CoduriMDC] g On f.IdMDC=g.IdMDC 
Where	a.IdSpital=@IdSpital And 
	(a.DataExternare between @DataStart And @DataEnd) And
	a.IdStareRecord<>3
Order By c1.Sectie_ID, a.NumarSectie, a.DataExternare
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rPacientiClasificare]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rPacientiFinalizatiNefinalizati (@IdSpital int, @strDataStart char(10), @strDataEnd char(10), @Finalizat int)

AS

Set DateFormat 'mdy'

Declare @DataStart smalldatetime, @DataEnd smalldatetime
Set @DataStart = convert(smalldatetime, @strDataStart)
Set @DataEnd = convert(smalldatetime, @strDataEnd)


Select 	a.IdPacientFisa, a.Caz_ID, b1.Spital_ID As CodINCDS, b1.SpitalDenumire, c1.Sectie_ID, c1.SectieNume, a.NumarSectie, 
	a.NFO, a.DataExternare, a.DataInternare, a.StareValidare, Cast(a.Flag_Finalizat As int) As Flag_Finalizat,
	a.Nume, a.Prenume
From 	[dbo].[Pacient_Fisa] a 
	Inner Join [dbo].[Lista_Spitale___Filtrat] b On a.IdSpital=b.IdSpital Inner Join [dbo].[Lista_Spitale] b1 On b.IdSpital=b1.IdSpital
	Inner Join [dbo].[Lista_Sectii___Filtrat] c On a.IdSpital=c.IdSpital And a.IdSectie=c.IdSectie And a.NumarSectie=c.NumarSectie Inner Join [dbo].[Lista_Sectii] c1 On c.IdSectie=c1.IdSectie
Where	a.IdSpital=@IdSpital And 
	(a.DataExternare between @DataStart And @DataEnd) And
	a.Flag_Finalizat=Case When @Finalizat=-2 Then a.Flag_Finalizat Else @Finalizat End And
	a.IdStareRecord<>3
Order By c1.Sectie_ID, a.NumarSectie, a.DataExternare
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rPacientiFinalizatiNefinalizati]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rPacientiStareValidare (@IdSpital int, @strDataStart char(10), @strDataEnd char(10), @StareValidare int)

AS

Set DateFormat 'mdy'

Declare @DataStart smalldatetime, @DataEnd smalldatetime
Set @DataStart = convert(smalldatetime, @strDataStart)
Set @DataEnd = convert(smalldatetime, @strDataEnd)


Select 	a.IdPacientFisa, a.Caz_ID, b1.Spital_ID As CodINCDS, b1.SpitalDenumire, c1.Sectie_ID, c1.SectieNume, a.NumarSectie, 
	a.NFO, a.DataExternare, a.DataInternare, IsNull(a.StareValidare,-2) As StareValidare, d.StareRecord, a.Flag_Finalizat,
	a.Nume, a.Prenume 
From 	[dbo].[Pacient_Fisa] a 
	Inner Join [dbo].[Lista_Spitale___Filtrat] b On a.IdSpital=b.IdSpital Inner Join [dbo].[Lista_Spitale] b1 On b.IdSpital=b1.IdSpital
	Inner Join [dbo].[Lista_Sectii___Filtrat] c On a.IdSpital=c.IdSpital And a.IdSectie=c.IdSectie And a.NumarSectie=c.NumarSectie Inner Join [dbo].[Lista_Sectii] c1 On c.IdSectie=c1.IdSectie
	Inner Join [dbo].[Lista_StareRecord] d On a.IdStareRecord=d.IdstareRecord
Where	a.IdSpital=@IdSpital And 
	(a.DataExternare between @DataStart And @DataEnd) And
	IsNull(a.StareValidare,-2)=Case When @StareValidare=-3 Then IsNull(a.StareValidare,-2) Else @StareValidare End And
	a.IdStareRecord<>3
Order By c1.Sectie_ID, a.NumarSectie, a.DataExternare
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rPacientiStareValidare]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rProceduri(@CodCapitol tinyint)

As 

	Select 	c.DenCapitol, b.DenBloc, a.CodProcedura, a.DenProcedura
	From 	[dbo].[Nom_Proceduri] a Inner Join [dbo].[Nom_Proceduri___Blocuri] b On a.CodBloc=b.CodBloc
		Inner Join [dbo].[Nom_Proceduri___Capitole] c On b.CodCapitol=c.CodCapitol
	Where  b.CodCapitol=@CodCapitol	
	Order By a.CodBloc, a.CodProcedura
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rProceduri]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rSectii
As 

	Select 	a.IdSpital, d.Spital_ID, d.SpitalDenumire,
		a.IdSectie, b.Sectie_ID, b.SectieNume, a.NumarSectie, a.Implicit 
	From 	[dbo].[Lista_Sectii___Filtrat] a Inner Join [dbo].[Lista_Sectii] b On a.IdSectie=b.IdSectie
		Inner Join [dbo].[Lista_Spitale___Filtrat] c On a.IdSpital=c.IdSpital
		Inner Join [dbo].[Lista_Spitale] d On c.IdSpital=d.IdSpital
	Order By d.Spital_ID, b.Sectie_ID, a.NumarSectie
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rSectii]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rSpitale
As 

	Select 	a.IdSpitalFiltrat, a.IdSpital, b.Spital_ID, e.SpitalID_CNAS, b.SpitalDenumire As DenSpital, b.IdJudet, d.JudetNume As DenJudet, b.IdLocalitate, c.LocalitateNume As DenLocalitate, a.NumarPaturi 
	From 	[dbo].[Lista_Spitale___Filtrat] a Inner Join [dbo].[Lista_Spitale] b On a.IdSpital=b.IdSpital
		Inner join [dbo].[Lista_Localitati] c On b.IdJudet=c.IdJudet And b.IdLocalitate=c.IdLocalitate
		Inner join [dbo].[Lista_Judete] d On c.IdJudet=d.IdJudet
		Inner Join [dbo].[Lista_Spitale_CNAS] e On b.IdSpitalCNAS=e.IdSpitalCNAS
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rSpitale]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rptVizualizareModificari(@IdSpital int, @strDataStart char(10), @strDataEnd char(10))

AS

Set DateFormat 'mdy'

Declare @DataStart smalldatetime, @DataEnd smalldatetime
Set @DataStart = convert(smalldatetime, @strDataStart)
Set @DataEnd = convert(smalldatetime, @strDataEnd)


Select 	d.Spital_ID, d.SpitalDenumire, 
	b.IdPacientFisa, b.Caz_ID, b.NFO, e.Sectie_ID, e.SectieNume, b.NumarSectie,
	b.DataInternare As DataInternareCurenta, b.DataExternare As DataExternareCurenta, 
	a.Tabela, a.IdLinie, a.CampModificat, 
	Case When CampModificat='IdProcedura' Then (Select x.CodProcedura From Nom_Proceduri x Where x.IdProcedura=a.ValoareOld)
	     When CampModificat like 'IdDiagnostic%' Then (Select y.CodDiagnostic From Lista_Diagnostice y Where y.IdDiagnostic=a.ValoareOld)	
	     When CampModificat='IdInterventieChirurgicala' Then (Select z.CodProcedura From Nom_Proceduri z Where z.IdProcedura=a.ValoareOld)
	     When CampModificat like 'IdMedic%' Then (Select v.CodMedic From Lista_Medici v Where v.IdMedic=a.ValoareOld)
	     When CampModificat='IdSectie' Then (Select w.Sectie_ID From Lista_Sectii w Where w.IdSectie=a.ValoareOld)
	     Else ValoareOld
	End As ValoareOld, 
	Case When CampModificat='IdProcedura' Then (Select x.CodProcedura From Nom_Proceduri x Where x.IdProcedura=a.ValoareNew)
	     When CampModificat like 'IdDiagnostic%' Then (Select y.CodDiagnostic From Lista_Diagnostice y Where y.IdDiagnostic=a.ValoareNew)	
	     When CampModificat='IdInterventieChirurgicala' Then (Select z.CodProcedura From Nom_Proceduri z Where z.IdProcedura=a.ValoareNew)
	     When CampModificat like 'IdMedic%' Then (Select v.CodMedic From Lista_Medici v Where v.IdMedic=a.ValoareNew)
	     When CampModificat='IdSectie' Then (Select w.Sectie_ID From Lista_Sectii w Where w.IdSectie=a.ValoareNew)
	     Else ValoareNew
	End As ValoareNew, 
	a.TipModificare,
	a.DataModificare, c.Utilizator   
	from Lista_Modificari a 
	Inner Join Pacient_Fisa b On a.IdPacientFisa=b.IdPacientFisa 
	Inner Join Lista_Utilizatori c On a.IdUtilizator=c.IdUtilizator
	Inner Join Lista_Spitale d On b.IdSpital=d.IdSpital
	Inner Join Lista_Sectii e On b.IdSectie=e.IdSectie
Where 	b.IdSpital=@IdSpital And 
	b.DataExternare between @DataStart And @DataEnd
Order By b.IdPacientFisa, a.DataModificare desc  
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rptVizualizareModificari]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rptVizualizareSectie (@strDataStart char(10) , @strDataEnd char(10))

AS

Set DateFormat 'mdy'

Declare @pDataStart smalldatetime, @pDataEnd smalldatetime
Set @pDataStart = convert(smalldatetime, @strDataStart)
Set @pDataEnd = convert(smalldatetime, @strDataEnd)
  

	Select	c.Spital_ID, c.SpitalDenumire, e.Sectie_ID, e.SectieNume, a.NumarSectie, a.StareValidare, Count(*) As NrCazuri  
	From 	dbo.Pacient_Fisa a 
		Inner Join dbo.Lista_Spitale___Filtrat b On a.IdSpital=b.IdSpital
		Inner Join dbo.Lista_Spitale c On b.IdSpital=c.IdSpital
		Inner Join dbo.Lista_Sectii___Filtrat d On a.IdSpital=d.IdSpital And a.IdSectie=d.IdSectie And a.NumarSectie=d.NumarSectie
		Inner Join dbo.Lista_Sectii e On d.IdSectie=e.IdSectie
	Where 	a.DataExternare between @pDataStart And @pDataEnd
		And a.Flag_Finalizat=1 -- pacientii finalizati
		And a.IdStareRecord<>3 --pacientii neanulati
	Group By c.Spital_ID, c.SpitalDenumire, e.Sectie_ID, e.SectieNume, a.NumarSectie, a.StareValidare
	Order By c.Spital_ID, e.Sectie_ID, a.NumarSectie
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rptVizualizareSectie]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rptVizualizareSectieCAS (@strDataStart char(10) , @strDataEnd char(10))

AS

Set DateFormat 'mdy'

Declare @pDataStart smalldatetime, @pDataEnd smalldatetime
Set @pDataStart = convert(smalldatetime, @strDataStart)
Set @pDataEnd = convert(smalldatetime, @strDataEnd)
  

	Select	c.Spital_ID, c.SpitalDenumire, e.Sectie_ID, e.SectieNume, 
		a.NumarSectie, a.IdTipAsigurare, f.TipAsigurare, a.IdCAS, g.CASTip, h.JudetNume,
		a.StareValidare, Count(*) As NrCazuri  
	From 	dbo.Pacient_Fisa a 
		Inner Join dbo.Lista_Spitale___Filtrat b On a.IdSpital=b.IdSpital
		Inner Join dbo.Lista_Spitale c On b.IdSpital=c.IdSpital
		Inner Join dbo.Lista_Sectii___Filtrat d On a.IdSpital=d.IdSpital And a.IdSectie=d.IdSectie And a.NumarSectie=d.NumarSectie
		Inner Join dbo.Lista_Sectii e On d.IdSectie=e.IdSectie
		Inner Join dbo.Lista_TipAsigurare f On a.IdTipAsigurare=f.IdTipAsigurare
		Left Join dbo.Lista_CAS g On a.IdCAS=g.IdCAS
		Left Join dbo.Lista_Judete h On a.IdJudetCAS=h.IdJudet
	Where 	a.DataExternare between @pDataStart And @pDataEnd
		And a.Flag_Finalizat=1 -- pacientii finalizati
		And a.IdStareRecord<>3 --pacientii neanulati
	Group By c.Spital_ID, c.SpitalDenumire, e.Sectie_ID, e.SectieNume, a.NumarSectie, a.IdTipAsigurare, f.TipAsigurare, a.IdCAS, g.CASTip, h.JudetNume, a.StareValidare
	Order By c.Spital_ID, e.Sectie_ID, a.NumarSectie, a.IdTipAsigurare, a.IdCAS, h.JudetNume
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rptVizualizareSectieCAS]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rptVizualizareSectieDecedati (@strDataStart char(10) , @strDataEnd char(10))

AS

Set DateFormat 'mdy'

Declare @pDataStart smalldatetime, @pDataEnd smalldatetime
Set @pDataStart = convert(smalldatetime, @strDataStart)
Set @pDataEnd = convert(smalldatetime, @strDataEnd)
  

	Select	c.Spital_ID, c.SpitalDenumire, e.Sectie_ID, e.SectieNume, 
		a.NumarSectie, a.IdTipExternare, f.TipExternare, a.StareValidare, Count(*) As NrCazuri  
	From 	dbo.Pacient_Fisa a 
		Inner Join dbo.Lista_Spitale___Filtrat b On a.IdSpital=b.IdSpital
		Inner Join dbo.Lista_Spitale c On b.IdSpital=c.IdSpital
		Inner Join dbo.Lista_Sectii___Filtrat d On a.IdSpital=d.IdSpital And a.IdSectie=d.IdSectie And a.NumarSectie=d.NumarSectie
		Inner Join dbo.Lista_Sectii e On d.IdSectie=e.IdSectie
		Inner Join dbo.Lista_TipExternare f On a.IdTipExternare=f.IdTipExternare
	Where 	a.DataExternare between @pDataStart And @pDataEnd
		And a.Flag_Finalizat=1      -- pacientii finalizati
		And a.IdStareRecord<>3  --pacientii neanulati
		And a.IdTipExternare=4    --decedat
	Group By c.Spital_ID, c.SpitalDenumire, e.Sectie_ID, e.SectieNume, a.NumarSectie, a.IdTipExternare, f.TipExternare, a.StareValidare 
	Order By c.Spital_ID, e.Sectie_ID, a.NumarSectie
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rptVizualizareSectieDecedati]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rptVizualizareSectieDiagnosticPrincipalDMS (@strDataStart char(10) , @strDataEnd char(10))

AS

Set DateFormat 'mdy'

Declare @pDataStart smalldatetime, @pDataEnd smalldatetime
Set @pDataStart = convert(smalldatetime, @strDataStart)
Set @pDataEnd = convert(smalldatetime, @strDataEnd)

Select 	a.Spital_ID, a.SpitalDenumire, a.Sectie_ID, a.SectieNume, a.NumarSectie, a.CodDiagnosticPrincipalCod1, a.DenDiagnosticPrincipalCod1,
	Count(a.IdPacientFisa) As NrCazuri, Avg(a.DS) As DMS, Sum(a.DS) As TotalDS
From   
(
	Select	a.IdPacientFisa, c.Spital_ID, c.SpitalDenumire, e.Sectie_ID, e.SectieNume, a.NumarSectie, 
		Case When DateDiff(d, a.DataInternare, a.DataExternare)=0 then 1 Else DateDiff(d, a.DataInternare, a.DataExternare) End As DS,
		g.CodDiagnostic As CodDiagnosticPrincipalCod1, g.NumeDiagnostic As DenDiagnosticPrincipalCod1
	From 	dbo.Pacient_Fisa a 
		Inner Join dbo.Lista_Spitale___Filtrat b On a.IdSpital=b.IdSpital
		Inner Join dbo.Lista_Spitale c On b.IdSpital=c.IdSpital
		Inner Join dbo.Lista_Sectii___Filtrat d On a.IdSpital=d.IdSpital And a.IdSectie=d.IdSectie And a.NumarSectie=d.NumarSectie
		Inner Join dbo.Lista_Sectii e On d.IdSectie=e.IdSectie
		Left Join dbo.Lista_Diagnostice___Cod1Cod2 f On a.IdDiagnosticPrincipalCod1=f.IdDiagnostic1 And IsNull(a.IdDiagnosticPrincipalCod2,0)=IsNull(f.IdDiagnostic2,0)
		Inner Join dbo.Lista_Diagnostice g On f.IdDiagnostic1=g.IdDiagnostic 
	Where 	a.DataExternare between @pDataStart And @pDataEnd
		And a.Flag_Finalizat=1      -- pacientii finalizati
		And a.IdStareRecord<>3  --pacientii neanulati
) a 
Group By a.Spital_ID, a.SpitalDenumire, a.Sectie_ID, a.SectieNume, a.NumarSectie, a.CodDiagnosticPrincipalCod1, a.DenDiagnosticPrincipalCod1
Order By a.Spital_ID, a.Sectie_ID, a.NumarSectie, Avg(a.DS) Desc
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rptVizualizareSectieDiagnosticPrincipalDMS]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rptVizualizareSectieDiagnosticeSecundare (@strDataStart char(10) , @strDataEnd char(10))

AS

Set DateFormat 'mdy'

Declare @pDataStart smalldatetime, @pDataEnd smalldatetime
Set @pDataStart = convert(smalldatetime, @strDataStart)
Set @pDataEnd = convert(smalldatetime, @strDataEnd)

Select 	a.Spital_ID, a.SpitalDenumire, a.Sectie_ID, a.SectieNume, a.NumarSectie, a.NrDgSec, Count(a.IdPacientFisa) As NrCazuri
From   
(
	Select	a.IdPacientFisa, c.Spital_ID, c.SpitalDenumire, e.Sectie_ID, e.SectieNume, a.NumarSectie,  
		Count(f.IdPacientDiagnosticSecundar) As NrDgSec
	From 	dbo.Pacient_Fisa a 
		Inner Join dbo.Lista_Spitale___Filtrat b On a.IdSpital=b.IdSpital
		Inner Join dbo.Lista_Spitale c On b.IdSpital=c.IdSpital
		Inner Join dbo.Lista_Sectii___Filtrat d On a.IdSpital=d.IdSpital And a.IdSectie=d.IdSectie And a.NumarSectie=d.NumarSectie
		Inner Join dbo.Lista_Sectii e On d.IdSectie=e.IdSectie
		Left Join dbo.Pacient_DiagnosticeSecundare f On a.IdPacientFisa=f.IdPacientFisa
	Where 	a.DataExternare between @pDataStart And @pDataEnd
		And a.Flag_Finalizat=1      -- pacientii finalizati
		And a.IdStareRecord<>3  --pacientii neanulati
	Group By a.IdPacientFisa, c.Spital_ID, c.SpitalDenumire, e.Sectie_ID, e.SectieNume, a.NumarSectie
) a 
Group By a.Spital_ID, a.SpitalDenumire, a.Sectie_ID, a.SectieNume, a.NumarSectie, a.NrDgSec 
Order By a.Spital_ID, a.Sectie_ID, a.NumarSectie, a.NrDgSec
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rptVizualizareSectieDiagnosticeSecundare]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rptVizualizareSectieInterventiePrincipalaDMS (@strDataStart char(10) , @strDataEnd char(10))

AS

Set DateFormat 'mdy'

Declare @pDataStart smalldatetime, @pDataEnd smalldatetime
Set @pDataStart = convert(smalldatetime, @strDataStart)
Set @pDataEnd = convert(smalldatetime, @strDataEnd)

Select 	a.Spital_ID, a.SpitalDenumire, a.Sectie_ID, a.SectieNume, a.NumarSectie, a.CodInterventiePrincipala, a.DenInterventiePrincipala,
	Count(a.IdPacientFisa) As NrCazuri, Avg(a.DS) As DMS, Sum(a.DS) As TotalDS
From   
(
	Select	a.IdPacientFisa, c.Spital_ID, c.SpitalDenumire, e.Sectie_ID, e.SectieNume, a.NumarSectie, 
		Case When DateDiff(d, a.DataInternare, a.DataExternare)=0 then 1 Else DateDiff(d, a.DataInternare, a.DataExternare) End As DS,
		f.CodProcedura As CodInterventiePrincipala, f.DenProcedura As DenInterventiePrincipala
	From 	dbo.Pacient_Fisa a 
		Inner Join dbo.Lista_Spitale___Filtrat b On a.IdSpital=b.IdSpital
		Inner Join dbo.Lista_Spitale c On b.IdSpital=c.IdSpital
		Inner Join dbo.Lista_Sectii___Filtrat d On a.IdSpital=d.IdSpital And a.IdSectie=d.IdSectie And a.NumarSectie=d.NumarSectie
		Inner Join dbo.Lista_Sectii e On d.IdSectie=e.IdSectie
		Left Join dbo.Nom_Proceduri f On a.IdInterventieChirurgicala=f.IdProcedura
	Where 	a.DataExternare between '01/01/2005' And '12/31/2005'
		And a.Flag_Finalizat=1      -- pacientii finalizati
		And a.IdStareRecord<>3  --pacientii neanulati
) a 
Group By a.Spital_ID, a.SpitalDenumire, a.Sectie_ID, a.SectieNume, a.NumarSectie, a.CodInterventiePrincipala, a.DenInterventiePrincipala
Order By a.Spital_ID, a.Sectie_ID, a.NumarSectie, Avg(a.DS) Desc
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rptVizualizareSectieInterventiePrincipalaDMS]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rptVizualizareSectiePacientProceduri (@strDataStart char(10) , @strDataEnd char(10))

AS

Set DateFormat 'mdy'

Declare @pDataStart smalldatetime, @pDataEnd smalldatetime
Set @pDataStart = convert(smalldatetime, @strDataStart)
Set @pDataEnd = convert(smalldatetime, @strDataEnd)
  

	Select	c.Spital_ID, c.SpitalDenumire, e.Sectie_ID, e.SectieNume, a.NumarSectie, 
		g.CodProcedura, g.DenProcedura, Count(*) As NrProceduri  
	From 	dbo.Pacient_Fisa a 
		Inner Join dbo.Lista_Spitale___Filtrat b On a.IdSpital=b.IdSpital
		Inner Join dbo.Lista_Spitale c On b.IdSpital=c.IdSpital
		Inner Join dbo.Lista_Sectii___Filtrat d On a.IdSpital=d.IdSpital And a.IdSectie=d.IdSectie And a.NumarSectie=d.NumarSectie
		Inner Join dbo.Lista_Sectii e On d.IdSectie=e.IdSectie
		Inner Join dbo.Pacient_Proceduri f On a.IdPacientFisa=f.IdPacientFisa
		Inner Join dbo.Nom_Proceduri g On f.IdProcedura=g.IdProcedura
	Where 	a.DataExternare between @pDataStart And @pDataEnd
		And a.Flag_Finalizat=1 -- pacientii finalizati
		And a.IdStareRecord<>3 --pacientii neanulati
	Group By c.Spital_ID, c.SpitalDenumire, e.Sectie_ID, e.SectieNume, a.NumarSectie, g.CodProcedura, g.DenProcedura
	Order By c.Spital_ID, e.Sectie_ID, a.NumarSectie, Count(*)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rptVizualizareSectiePacientProceduri]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rptVizualizareSectiePacientSectie (@strDataStart char(10) , @strDataEnd char(10))

AS

Set DateFormat 'mdy'

Declare @pDataStart smalldatetime, @pDataEnd smalldatetime
Set @pDataStart = convert(smalldatetime, @strDataStart)
Set @pDataEnd = convert(smalldatetime, @strDataEnd)
  

	Select	c.Spital_ID, c.SpitalDenumire, f.Sectie_ID, f.SectieNume, d.NumarSectie, Sum(d.NrZile) As NrZileSpitalizareSectie
	From 	dbo.Pacient_Fisa a 
		Inner Join dbo.Lista_Spitale___Filtrat b On a.IdSpital=b.IdSpital
		Inner Join dbo.Lista_Spitale c On b.IdSpital=c.IdSpital
		Inner Join dbo.Pacient_Sectii d On a.IdPacientFisa=d.IdPacientFisa
		Inner Join dbo.Lista_Sectii___Filtrat e On d.IdSpital=e.IdSpital And d.IdSectie=e.IdSectie And d.NumarSectie=e.NumarSectie
		Inner Join dbo.Lista_Sectii f On e.IdSectie=f.IdSectie
	Where 	a.DataExternare between @pDataStart And @pDataEnd
		And a.Flag_Finalizat=1 -- pacientii finalizati
		And a.IdStareRecord<>3 --pacientii neanulati
	Group By c.Spital_ID, c.SpitalDenumire, f.Sectie_ID, f.SectieNume, d.NumarSectie
	Order By c.Spital_ID, f.Sectie_ID, d.NumarSectie
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rptVizualizareSectiePacientSectie]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rptVizualizareSectieSex (@strDataStart char(10) , @strDataEnd char(10))

AS

Set DateFormat 'mdy'

Declare @pDataStart smalldatetime, @pDataEnd smalldatetime
Set @pDataStart = convert(smalldatetime, @strDataStart)
Set @pDataEnd = convert(smalldatetime, @strDataEnd)
  

	Select	c.Spital_ID, c.SpitalDenumire, e.Sectie_ID, e.SectieNume, 
		a.NumarSectie, a.StareValidare, a.Sex, Count(*) As NrCazuri  
	From 	dbo.Pacient_Fisa a 
		Inner Join dbo.Lista_Spitale___Filtrat b On a.IdSpital=b.IdSpital
		Inner Join dbo.Lista_Spitale c On b.IdSpital=c.IdSpital
		Inner Join dbo.Lista_Sectii___Filtrat d On a.IdSpital=d.IdSpital And a.IdSectie=d.IdSectie And a.NumarSectie=d.NumarSectie
		Inner Join dbo.Lista_Sectii e On d.IdSectie=e.IdSectie
	Where 	a.DataExternare between @pDataStart And @pDataEnd
		And a.Flag_Finalizat=1 -- pacientii finalizati
		And a.IdStareRecord<>3 --pacientii neanulati
	Group By c.Spital_ID, c.SpitalDenumire, e.Sectie_ID, e.SectieNume, a.NumarSectie, a.StareValidare, a.Sex
	Order By c.Spital_ID, e.Sectie_ID, a.NumarSectie,  a.Sex
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rptVizualizareSectieSex]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rptVizualizareSectieStareExternare (@strDataStart char(10) , @strDataEnd char(10))

AS

Set DateFormat 'mdy'

Declare @pDataStart smalldatetime, @pDataEnd smalldatetime
Set @pDataStart = convert(smalldatetime, @strDataStart)
Set @pDataEnd = convert(smalldatetime, @strDataEnd)
  

	Select	c.Spital_ID, c.SpitalDenumire, e.Sectie_ID, e.SectieNume, 
		a.NumarSectie, a.IdStareExternare, f.StareExternare, a.StareValidare, Count(*) As NrCazuri  
	From 	dbo.Pacient_Fisa a 
		Inner Join dbo.Lista_Spitale___Filtrat b On a.IdSpital=b.IdSpital
		Inner Join dbo.Lista_Spitale c On b.IdSpital=c.IdSpital
		Inner Join dbo.Lista_Sectii___Filtrat d On a.IdSpital=d.IdSpital And a.IdSectie=d.IdSectie And a.NumarSectie=d.NumarSectie
		Inner Join dbo.Lista_Sectii e On d.IdSectie=e.IdSectie
		Inner Join dbo.Lista_StareExternare f On a.IdStareExternare=f.IdStareExternare
	Where 	a.DataExternare between @pDataStart And @pDataEnd
		And a.Flag_Finalizat=1 -- pacientii finalizati
		And a.IdStareRecord<>3 --pacientii neanulati
	Group By c.Spital_ID, c.SpitalDenumire, e.Sectie_ID, e.SectieNume, a.NumarSectie, a.IdStareExternare, f.StareExternare, a.StareValidare
	Order By c.Spital_ID, e.Sectie_ID, a.NumarSectie, a.IdStareExternare
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rptVizualizareSectieStareExternare]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rptVizualizareSectieTipExternare (@strDataStart char(10) , @strDataEnd char(10))

AS

Set DateFormat 'mdy'

Declare @pDataStart smalldatetime, @pDataEnd smalldatetime
Set @pDataStart = convert(smalldatetime, @strDataStart)
Set @pDataEnd = convert(smalldatetime, @strDataEnd)
  

	Select	c.Spital_ID, c.SpitalDenumire, e.Sectie_ID, e.SectieNume, 
		a.NumarSectie, a.IdTipExternare, f.TipExternare, a.StareValidare, Count(*) As NrCazuri
	From 	dbo.Pacient_Fisa a 
		Inner Join dbo.Lista_Spitale___Filtrat b On a.IdSpital=b.IdSpital
		Inner Join dbo.Lista_Spitale c On b.IdSpital=c.IdSpital
		Inner Join dbo.Lista_Sectii___Filtrat d On a.IdSpital=d.IdSpital And a.IdSectie=d.IdSectie And a.NumarSectie=d.NumarSectie
		Inner Join dbo.Lista_Sectii e On d.IdSectie=e.IdSectie
		Inner Join dbo.Lista_TipExternare f On a.IdTipExternare=f.IdTipExternare
	Where 	a.DataExternare between @pDataStart And @pDataEnd
		And a.Flag_Finalizat=1 -- pacientii finalizati
		And a.IdStareRecord<>3 --pacientii neanulati
	Group By c.Spital_ID, c.SpitalDenumire, e.Sectie_ID, e.SectieNume, a.NumarSectie, a.IdTipExternare, f.TipExternare, a.StareValidare
	Order By c.Spital_ID, e.Sectie_ID, a.NumarSectie, a.IdTipExternare
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rptVizualizareSectieTipExternare]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rptVizualizareSectieTipInternare (@strDataStart char(10) , @strDataEnd char(10))

AS

Set DateFormat 'mdy'

Declare @pDataStart smalldatetime, @pDataEnd smalldatetime
Set @pDataStart = convert(smalldatetime, @strDataStart)
Set @pDataEnd = convert(smalldatetime, @strDataEnd)
  

	Select	c.Spital_ID, c.SpitalDenumire, e.Sectie_ID, e.SectieNume, 
		a.NumarSectie, a.IdTipInternare, f.TipInternare, a.StareValidare, Count(*) As NrCazuri  
	From 	dbo.Pacient_Fisa a 
		Inner Join dbo.Lista_Spitale___Filtrat b On a.IdSpital=b.IdSpital
		Inner Join dbo.Lista_Spitale c On b.IdSpital=c.IdSpital
		Inner Join dbo.Lista_Sectii___Filtrat d On a.IdSpital=d.IdSpital And a.IdSectie=d.IdSectie And a.NumarSectie=d.NumarSectie
		Inner Join dbo.Lista_Sectii e On d.IdSectie=e.IdSectie
		Inner Join dbo.Lista_TipInternare f On a.IdTipInternare=f.IdTipInternare
	Where 	a.DataExternare between @pDataStart And @pDataEnd
		And a.Flag_Finalizat=1 -- pacientii finalizati
		And a.IdStareRecord<>3 --pacientii neanulati
	Group By c.Spital_ID, c.SpitalDenumire, e.Sectie_ID, e.SectieNume, a.NumarSectie, a.IdTipInternare, f.TipInternare, a.StareValidare
	Order By c.Spital_ID, e.Sectie_ID, a.NumarSectie, a.IdTipInternare
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rptVizualizareSectieTipInternare]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rptVizualizareSpital (@strDataStart char(10) , @strDataEnd char(10))

AS

Set DateFormat 'mdy'

Declare @pDataStart smalldatetime, @pDataEnd smalldatetime
Set @pDataStart = convert(smalldatetime, @strDataStart)
Set @pDataEnd = convert(smalldatetime, @strDataEnd)
  

	Select	c.Spital_ID, c.SpitalDenumire, a.StareValidare, Count(*) As NrCazuri  
	From 	dbo.Pacient_Fisa a 
		Inner Join dbo.Lista_Spitale___Filtrat b On a.IdSpital=b.IdSpital
		Inner Join dbo.Lista_Spitale c On b.IdSpital=c.IdSpital
	Where 	a.DataExternare between @pDataStart And @pDataEnd
		And a.Flag_Finalizat=1 -- pacientii finalizati
		And a.IdStareRecord<>3 --pacientii neanulati
	Group By c.Spital_ID, c.SpitalDenumire, a.StareValidare
	Order By c.Spital_ID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rptVizualizareSpital]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rptVizualizareSpitalCAS (@strDataStart char(10) , @strDataEnd char(10))

AS

Set DateFormat 'mdy'

Declare @pDataStart smalldatetime, @pDataEnd smalldatetime
Set @pDataStart = convert(smalldatetime, @strDataStart)
Set @pDataEnd = convert(smalldatetime, @strDataEnd)
  

	Select	c.Spital_ID, c.SpitalDenumire, a.IdTipAsigurare, f.TipAsigurare, a.IdCAS, g.CASTip, h.JudetNume, a.StareValidare, Count(*) As NrCazuri  
	From 	dbo.Pacient_Fisa a 
		Inner Join dbo.Lista_Spitale___Filtrat b On a.IdSpital=b.IdSpital
		Inner Join dbo.Lista_Spitale c On b.IdSpital=c.IdSpital
		Inner Join dbo.Lista_TipAsigurare f On a.IdTipAsigurare=f.IdTipAsigurare
		Left Join dbo.Lista_CAS g On a.IdCAS=g.IdCAS
		Left Join dbo.Lista_Judete h On a.IdJudetCAS=h.IdJudet
	Where 	a.DataExternare between @pDataStart And @pDataEnd
		And a.Flag_Finalizat=1 -- pacientii finalizati
		And a.IdStareRecord<>3 --pacientii neanulati
	Group By c.Spital_ID, c.SpitalDenumire, a.IdTipAsigurare, f.TipAsigurare, a.IdCAS, g.CASTip, h.JudetNume, a.StareValidare 
	Order By c.Spital_ID, a.IdTipAsigurare, a.IdCAS, h.JudetNume
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rptVizualizareSpitalCAS]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rptVizualizareSpitalDecedati (@strDataStart char(10) , @strDataEnd char(10))

AS

Set DateFormat 'mdy'

Declare @pDataStart smalldatetime, @pDataEnd smalldatetime
Set @pDataStart = convert(smalldatetime, @strDataStart)
Set @pDataEnd = convert(smalldatetime, @strDataEnd)
  

	Select	c.Spital_ID, c.SpitalDenumire, a.IdStareExternare, f.StareExternare, a.StareValidare, Count(*) As NrCazuri  
	From 	dbo.Pacient_Fisa a 
		Inner Join dbo.Lista_Spitale___Filtrat b On a.IdSpital=b.IdSpital
		Inner Join dbo.Lista_Spitale c On b.IdSpital=c.IdSpital
		Inner Join dbo.Lista_StareExternare f On a.IdStareExternare=f.IdStareExternare
	Where 	a.DataExternare between @pDataStart And @pDataEnd
		And a.Flag_Finalizat=1      -- pacientii finalizati
		And a.IdStareRecord<>3  --pacientii neanulati
		And a.IdTipExternare=4    --decedat
	Group By c.Spital_ID, c.SpitalDenumire, a.IdStareExternare, f.StareExternare, a.StareValidare  
	Order By c.Spital_ID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rptVizualizareSpitalDecedati]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rptVizualizareSpitalDecedatiMortalitate (@strDataStart char(10) , @strDataEnd char(10))

AS

Set DateFormat 'mdy'

Declare @pDataStart smalldatetime, @pDataEnd smalldatetime
Set @pDataStart = convert(smalldatetime, @strDataStart)
Set @pDataEnd = convert(smalldatetime, @strDataEnd)
  

	Select	c.Spital_ID, c.SpitalDenumire, a.IdTipExternare, f.TipExternare, 
		Count(*) As NrCazuri,
		(Select Count(*) From Pacient_Fisa x Where x.IdSpital=a.IdSpital And x.DataExternare between @pDataStart And @pDataEnd And x.Flag_Finalizat=1 And x.IdStareRecord<>3) As NrTotalCazuri
	From 	dbo.Pacient_Fisa a 
		Inner Join dbo.Lista_Spitale___Filtrat b On a.IdSpital=b.IdSpital
		Inner Join dbo.Lista_Spitale c On b.IdSpital=c.IdSpital
		Inner Join dbo.Lista_TipExternare f On a.IdTipExternare=f.IdTipExternare
	Where 	a.DataExternare between @pDataStart And @pDataEnd
		And a.Flag_Finalizat=1      -- pacientii finalizati
		And a.IdStareRecord<>3  --pacientii neanulati
		And a.IdTipExternare=4    --decedat
	Group By a.IdSpital, c.Spital_ID, c.SpitalDenumire, a.IdTipExternare, f.TipExternare
	Order By c.Spital_ID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rptVizualizareSpitalDecedatiMortalitate]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rptVizualizareSpitalDiagnosticPrincipalDMS (@strDataStart char(10), @strDataEnd char(10))

AS

Set DateFormat 'mdy'

Declare @pDataStart smalldatetime, @pDataEnd smalldatetime
Set @pDataStart = convert(smalldatetime, @strDataStart)
Set @pDataEnd = convert(smalldatetime, @strDataEnd)

Select 	a.Spital_ID, a.SpitalDenumire, a.CodDiagnosticPrincipalCod1, a.DenDiagnosticPrincipalCod1, 
	Count(a.IdPacientFisa) As NrCazuri, Avg(a.DS) As DMS, Sum(a.DS) As TotalDS
From   
(
	Select	a.IdPacientFisa, c.Spital_ID, c.SpitalDenumire, 
		Case When DateDiff(d, a.DataInternare, a.DataExternare)=0 then 1 Else DateDiff(d, a.DataInternare, a.DataExternare) End As DS,
		g.CodDiagnostic As CodDiagnosticPrincipalCod1, g.NumeDiagnostic As DenDiagnosticPrincipalCod1
	From 	dbo.Pacient_Fisa a 
		Inner Join dbo.Lista_Spitale___Filtrat b On a.IdSpital=b.IdSpital
		Inner Join dbo.Lista_Spitale c On b.IdSpital=c.IdSpital
		Left Join dbo.Lista_Diagnostice___Cod1Cod2 f On a.IdDiagnosticPrincipalCod1=f.IdDiagnostic1 And IsNull(a.IdDiagnosticPrincipalCod2,0)=IsNull(f.IdDiagnostic2,0)
		Inner Join dbo.Lista_Diagnostice g On f.IdDiagnostic1=g.IdDiagnostic 
	Where 	a.DataExternare between @pDataStart And @pDataEnd
		And a.Flag_Finalizat=1      -- pacientii finalizati
		And a.IdStareRecord<>3  --pacientii neanulati
) a 
Group By a.Spital_ID, a.SpitalDenumire, a.CodDiagnosticPrincipalCod1, a.DenDiagnosticPrincipalCod1
Order By a.Spital_ID, Avg(a.DS) Desc
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rptVizualizareSpitalDiagnosticPrincipalDMS]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rptVizualizareSpitalInterventiePrincipalaDMS (@strDataStart char(10) , @strDataEnd char(10))

AS

Set DateFormat 'mdy'

Declare @pDataStart smalldatetime, @pDataEnd smalldatetime
Set @pDataStart = convert(smalldatetime, @strDataStart)
Set @pDataEnd = convert(smalldatetime, @strDataEnd)

Select 	a.Spital_ID, a.SpitalDenumire, a.CodInterventiePrincipala, a.DenInterventiePrincipala,
	Count(a.IdPacientFisa) As NrCazuri, Avg(a.DS) As DMS, Sum(a.DS) As TotalDS
From   
(
	Select	a.IdPacientFisa, c.Spital_ID, c.SpitalDenumire, 
		Case When DateDiff(d, a.DataInternare, a.DataExternare)=0 then 1 Else DateDiff(d, a.DataInternare, a.DataExternare) End As DS,
		f.CodProcedura As CodInterventiePrincipala, f.DenProcedura As DenInterventiePrincipala
	From 	dbo.Pacient_Fisa a 
		Inner Join dbo.Lista_Spitale___Filtrat b On a.IdSpital=b.IdSpital
		Inner Join dbo.Lista_Spitale c On b.IdSpital=c.IdSpital
		Left Join dbo.Nom_Proceduri f On a.IdInterventieChirurgicala=f.IdProcedura
	Where 	a.DataExternare between '01/01/2005' And '12/31/2005'
		And a.Flag_Finalizat=1      -- pacientii finalizati
		And a.IdStareRecord<>3  --pacientii neanulati
) a 
Group By a.Spital_ID, a.SpitalDenumire, a.CodInterventiePrincipala, a.DenInterventiePrincipala
Order By a.Spital_ID, Avg(a.DS) Desc
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rptVizualizareSpitalInterventiePrincipalaDMS]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rptVizualizareSpitalPacientProceduri (@strDataStart char(10) , @strDataEnd char(10))

AS

Set DateFormat 'mdy'

Declare @pDataStart smalldatetime, @pDataEnd smalldatetime
Set @pDataStart = convert(smalldatetime, @strDataStart)
Set @pDataEnd = convert(smalldatetime, @strDataEnd)
  

	Select	c.Spital_ID, c.SpitalDenumire, 
		g.CodProcedura, g.DenProcedura, Count(*) As NrProceduri  
	From 	dbo.Pacient_Fisa a 
		Inner Join dbo.Lista_Spitale___Filtrat b On a.IdSpital=b.IdSpital
		Inner Join dbo.Lista_Spitale c On b.IdSpital=c.IdSpital
		Inner Join dbo.Pacient_Proceduri f On a.IdPacientFisa=f.IdPacientFisa
		Inner Join dbo.Nom_Proceduri g On f.IdProcedura=g.IdProcedura
	Where 	a.DataExternare between @pDataStart And @pDataEnd
		And a.Flag_Finalizat=1 -- pacientii finalizati
		And a.IdStareRecord<>3 --pacientii neanulati
	Group By c.Spital_ID, c.SpitalDenumire, g.CodProcedura, g.DenProcedura
	Order By c.Spital_ID, Count(*)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rptVizualizareSpitalPacientProceduri]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rptVizualizareSpitalPacientSectie (@strDataStart char(10) , @strDataEnd char(10))

AS

Set DateFormat 'mdy'

Declare @pDataStart smalldatetime, @pDataEnd smalldatetime
Set @pDataStart = convert(smalldatetime, @strDataStart)
Set @pDataEnd = convert(smalldatetime, @strDataEnd)
  

	Select	c.Spital_ID, c.SpitalDenumire, 
		Sum(d.NrZile) As NrZileSpitalizareSectie,
		(Select Sum(DateDiff(d,x.DataInternare,x.DataExternare)) From Pacient_Fisa x Where x.IdSpital=a.IdSpital And x.DataExternare between @pDataStart And @pDataEnd And x.Flag_Finalizat=1 And x.IdStareRecord<>3) As NrZileSpitalizareFO
	From 	dbo.Pacient_Fisa a 
		Inner Join dbo.Lista_Spitale___Filtrat b On a.IdSpital=b.IdSpital
		Inner Join dbo.Lista_Spitale c On b.IdSpital=c.IdSpital
		Inner Join dbo.Pacient_Sectii d On a.IdPacientFisa=d.IdPacientFisa
	Where 	a.DataExternare between @pDataStart And @pDataEnd
		And a.Flag_Finalizat=1 -- pacientii finalizati
		And a.IdStareRecord<>3 --pacientii neanulati
	Group By a.IdSpital, c.Spital_ID, c.SpitalDenumire
	Order By c.Spital_ID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rptVizualizareSpitalPacientSectie]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rptVizualizareSpitalSex (@strDataStart char(10) , @strDataEnd char(10))

AS

Set DateFormat 'mdy'

Declare @pDataStart smalldatetime, @pDataEnd smalldatetime
Set @pDataStart = convert(smalldatetime, @strDataStart)
Set @pDataEnd = convert(smalldatetime, @strDataEnd)
  

	Select	c.Spital_ID, c.SpitalDenumire, a.StareValidare, a.Sex, Count(*) As NrCazuri  
	From 	dbo.Pacient_Fisa a 
		Inner Join dbo.Lista_Spitale___Filtrat b On a.IdSpital=b.IdSpital
		Inner Join dbo.Lista_Spitale c On b.IdSpital=c.IdSpital
	Where 	a.DataExternare between @pDataStart And @pDataEnd
		And a.Flag_Finalizat=1 -- pacientii finalizati
		And a.IdStareRecord<>3 --pacientii neanulati
	Group By c.Spital_ID, c.SpitalDenumire, a.StareValidare, a.Sex
	Order By c.Spital_ID, a.Sex
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rptVizualizareSpitalSex]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rptVizualizareSpitalStareExternare (@strDataStart char(10) , @strDataEnd char(10))

AS

Set DateFormat 'mdy'

Declare @pDataStart smalldatetime, @pDataEnd smalldatetime
Set @pDataStart = convert(smalldatetime, @strDataStart)
Set @pDataEnd = convert(smalldatetime, @strDataEnd)
  

	Select	c.Spital_ID, c.SpitalDenumire, a.IdStareExternare, f.StareExternare, a.StareValidare, Count(*) As NrCazuri  
	From 	dbo.Pacient_Fisa a 
		Inner Join dbo.Lista_Spitale___Filtrat b On a.IdSpital=b.IdSpital
		Inner Join dbo.Lista_Spitale c On b.IdSpital=c.IdSpital
		Inner Join dbo.Lista_StareExternare f On a.IdStareExternare=f.IdStareExternare
	Where 	a.DataExternare between @pDataStart And @pDataEnd
		And a.Flag_Finalizat=1 -- pacientii finalizati
		And a.IdStareRecord<>3 --pacientii neanulati
	Group By c.Spital_ID, c.SpitalDenumire, a.IdStareExternare, f.StareExternare, a.StareValidare
	Order By c.Spital_ID, a.IdStareExternare
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rptVizualizareSpitalStareExternare]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rptVizualizareSpitalTipExternare (@strDataStart char(10) , @strDataEnd char(10))

AS

Set DateFormat 'mdy'

Declare @pDataStart smalldatetime, @pDataEnd smalldatetime
Set @pDataStart = convert(smalldatetime, @strDataStart)
Set @pDataEnd = convert(smalldatetime, @strDataEnd)
  

	Select	c.Spital_ID, c.SpitalDenumire, a.IdTipExternare, f.TipExternare, a.StareValidare, Count(*) As NrCazuri
	From 	dbo.Pacient_Fisa a 
		Inner Join dbo.Lista_Spitale___Filtrat b On a.IdSpital=b.IdSpital
		Inner Join dbo.Lista_Spitale c On b.IdSpital=c.IdSpital
		Inner Join dbo.Lista_TipExternare f On a.IdTipExternare=f.IdTipExternare
	Where 	a.DataExternare between @pDataStart And @pDataEnd
		And a.Flag_Finalizat=1 -- pacientii finalizati
		And a.IdStareRecord<>3 --pacientii neanulati
	Group By c.Spital_ID, c.SpitalDenumire, a.IdTipExternare, f.TipExternare, a.StareValidare
	Order By c.Spital_ID, a.IdTipExternare
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rptVizualizareSpitalTipExternare]  TO [Operatori_AppRole]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE rptVizualizareSpitalTipInternare (@strDataStart char(10) , @strDataEnd char(10))

AS

Set DateFormat 'mdy'

Declare @pDataStart smalldatetime, @pDataEnd smalldatetime
Set @pDataStart = convert(smalldatetime, @strDataStart)
Set @pDataEnd = convert(smalldatetime, @strDataEnd)
  

	Select	c.Spital_ID, c.SpitalDenumire, a.IdTipInternare, f.TipInternare, a.StareValidare, Count(*) As NrCazuri  
	From 	dbo.Pacient_Fisa a 
		Inner Join dbo.Lista_Spitale___Filtrat b On a.IdSpital=b.IdSpital
		Inner Join dbo.Lista_Spitale c On b.IdSpital=c.IdSpital
		Inner Join dbo.Lista_TipInternare f On a.IdTipInternare=f.IdTipInternare
	Where 	a.DataExternare between @pDataStart And @pDataEnd
		And a.Flag_Finalizat=1 -- pacientii finalizati
		And a.IdStareRecord<>3 --pacientii neanulati
	Group By c.Spital_ID, c.SpitalDenumire, a.IdTipInternare, f.TipInternare, a.StareValidare
	Order By c.Spital_ID, a.IdTipInternare
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[rptVizualizareSpitalTipInternare]  TO [Operatori_AppRole]
GO

Print 'Recreare proceduri'
--				END Actualizare PROCEDURI

-- 			   BEGIN MODIFICARE TABEL PACIENT_PROCEDURI
DECLARE @defname VARCHAR(100), @cmd VARCHAR(1000)
SET @defname = (SELECT name FROM sysobjects so JOIN sysconstraints sc ON so.id = sc.constid WHERE object_name(so.parent_obj) = 'Pacient_Proceduri' AND so.xtype = 'D' AND sc.colid = (SELECT colid FROM syscolumns WHERE id = object_id('Pacient_Proceduri') AND name = 'IdPacientProceduraOld'))

If IsNull(@defname,'')<>''
   Begin
	SET @cmd = 'ALTER TABLE dbo.Pacient_Proceduri DROP CONSTRAINT ' + @defname
	EXEC(@cmd)
   End

If exists (Select * From dbo.syscolumns Where Id = object_id(N'[dbo].[Pacient_Proceduri]') and OBJECTPROPERTY(id, N'IsTable') = 1 and name='IdPacientProceduraOld')
Alter Table dbo.Pacient_Proceduri DROP COLUMN IdPacientProceduraOld
GO

If exists (Select * From dbo.sysconstraints Where Id = object_id(N'[dbo].[Pacient_Proceduri]') and OBJECTPROPERTY(id, N'IsTable') = 1 and Object_name(constid)='CU_IdPacientFisaIdProcedura')
Alter Table dbo.Pacient_Proceduri DROP CONSTRAINT CU_IdPacientFisaIdProcedura
Print 'Corectie tabel pacienti proceduri'
-- 			   END MODIFICARE TABEL PACIENT_PROCEDURI
GO


--			 BEGIN ACTUALIZARE RAPOARTE DACA ESTE CAZUL
If Not exists (Select * From dbo.Rapoarte Where IdRaport=13)
Insert dbo.Rapoarte(IdRaport, Raport, IdParinte, TipRaport, Parametrii, Locatie)
Values(13, 'Clasificare pe sectii', 1, 'F', Null, Null)

If Not exists (Select * From dbo.Rapoarte Where IdRaport=14)
Insert dbo.Rapoarte(IdRaport, Raport, IdParinte, TipRaport, Parametrii, Locatie)
Values(14, 'Clasificare pe spitale', 1, 'F', Null, Null)

If Not exists (Select * From dbo.Rapoarte Where IdRaport=15)
Insert dbo.Rapoarte(IdRaport, Raport, IdParinte, TipRaport, Parametrii, Locatie)
Values(15, 'Sectie_CAS', 13, 'R', 'strDataStart,,ControlPersonal|Data;strDataEnd,,ControlPersonal|Data;', 'SectieCAS.rpt')

If Not exists (Select * From dbo.Rapoarte Where IdRaport=16)
Insert dbo.Rapoarte(IdRaport, Raport, IdParinte, TipRaport, Parametrii, Locatie)
Values(16, 'Sectie_Decedati', 13, 'R', 'strDataStart,,ControlPersonal|Data;strDataEnd,,ControlPersonal|Data;', 'SectieDecedati.rpt')

If Not exists (Select * From dbo.Rapoarte Where IdRaport=17)
Insert dbo.Rapoarte(IdRaport, Raport, IdParinte, TipRaport, Parametrii, Locatie)
Values(17, 'Sectie_DiagnosticPrincipal_DMS', 13, 'R', 'strDataStart,,ControlPersonal|Data;strDataEnd,,ControlPersonal|Data;', 'SectieDiagnosticPrincipalDMS.rpt')

If Not exists (Select * From dbo.Rapoarte Where IdRaport=18)
Insert dbo.Rapoarte(IdRaport, Raport, IdParinte, TipRaport, Parametrii, Locatie)
Values(18, 'Sectie_InterventieChirurgicalaPrincipala_DMS', 13, 'R', 'strDataStart,,ControlPersonal|Data;strDataEnd,,ControlPersonal|Data;', 'SectieInterventieChirurgicalaPrincipalaDMS.rpt')

If Not exists (Select * From dbo.Rapoarte Where IdRaport=19)
Insert dbo.Rapoarte(IdRaport, Raport, IdParinte, TipRaport, Parametrii, Locatie)
Values(19, 'Sectie_NrCazuri', 13, 'R', 'strDataStart,,ControlPersonal|Data;strDataEnd,,ControlPersonal|Data;', 'SectieNrCazuri.rpt')

If Not exists (Select * From dbo.Rapoarte Where IdRaport=20)
Insert dbo.Rapoarte(IdRaport, Raport, IdParinte, TipRaport, Parametrii, Locatie)
Values(20, 'Sectie_NrDiagnosticeSecundare', 13, 'R', 'strDataStart,,ControlPersonal|Data;strDataEnd,,ControlPersonal|Data;', 'SectieDiagnosticeSecundare.rpt')

If Not exists (Select * From dbo.Rapoarte Where IdRaport=21)
Insert dbo.Rapoarte(IdRaport, Raport, IdParinte, TipRaport, Parametrii, Locatie)
Values(21, 'Sectie_NrZileSpitalizare', 13, 'R', 'strDataStart,,ControlPersonal|Data;strDataEnd,,ControlPersonal|Data;', 'SectieNrZileSpitalizare.rpt')

If Not exists (Select * From dbo.Rapoarte Where IdRaport=22)
Insert dbo.Rapoarte(IdRaport, Raport, IdParinte, TipRaport, Parametrii, Locatie)
Values(22, 'Sectie_Proceduri', 13, 'R', 'strDataStart,,ControlPersonal|Data;strDataEnd,,ControlPersonal|Data;', 'SectieProceduri.rpt')

If Not exists (Select * From dbo.Rapoarte Where IdRaport=23)
Insert dbo.Rapoarte(IdRaport, Raport, IdParinte, TipRaport, Parametrii, Locatie)
Values(23, 'Sectie_Sex', 13, 'R', 'strDataStart,,ControlPersonal|Data;strDataEnd,,ControlPersonal|Data;', 'SectieSex.rpt')

If Not exists (Select * From dbo.Rapoarte Where IdRaport=24)
Insert dbo.Rapoarte(IdRaport, Raport, IdParinte, TipRaport, Parametrii, Locatie)
Values(24, 'Sectie_StareExternare', 13, 'R', 'strDataStart,,ControlPersonal|Data;strDataEnd,,ControlPersonal|Data;', 'SectieStareExternare.rpt')

If Not exists (Select * From dbo.Rapoarte Where IdRaport=25)
Insert dbo.Rapoarte(IdRaport, Raport, IdParinte, TipRaport, Parametrii, Locatie)
Values(25, 'Sectie_TipExternare', 13, 'R', 'strDataStart,,ControlPersonal|Data;strDataEnd,,ControlPersonal|Data;', 'SectieTipExternare.rpt')

If Not exists (Select * From dbo.Rapoarte Where IdRaport=26)
Insert dbo.Rapoarte(IdRaport, Raport, IdParinte, TipRaport, Parametrii, Locatie)
Values(26, 'Sectie_TipInternare', 13, 'R', 'strDataStart,,ControlPersonal|Data;strDataEnd,,ControlPersonal|Data;', 'SectieTipInternare.rpt')

If Not exists (Select * From dbo.Rapoarte Where IdRaport=27)
Insert dbo.Rapoarte(IdRaport, Raport, IdParinte, TipRaport, Parametrii, Locatie)
Values(27, 'Spital_CAS', 14, 'R', 'strDataStart,,ControlPersonal|Data;strDataEnd,,ControlPersonal|Data;', 'SpitalCAS.rpt')

If Not exists (Select * From dbo.Rapoarte Where IdRaport=28)
Insert dbo.Rapoarte(IdRaport, Raport, IdParinte, TipRaport, Parametrii, Locatie)
Values(28, 'Spital_Decedati', 14, 'R', 'strDataStart,,ControlPersonal|Data;strDataEnd,,ControlPersonal|Data;', 'SpitalDecedati.rpt')

If Not exists (Select * From dbo.Rapoarte Where IdRaport=29)
Insert dbo.Rapoarte(IdRaport, Raport, IdParinte, TipRaport, Parametrii, Locatie)
Values(29, 'Spital_DiagnosticPrincipal_DMS', 14, 'R', 'strDataStart,,ControlPersonal|Data;strDataEnd,,ControlPersonal|Data;', 'SpitalDiagnosticPrincipalDMS.rpt')

If Not exists (Select * From dbo.Rapoarte Where IdRaport=30)
Insert dbo.Rapoarte(IdRaport, Raport, IdParinte, TipRaport, Parametrii, Locatie)
Values(30, 'Spital_InterventieChirurgicalaPrincipala_DMS', 14, 'R', 'strDataStart,,ControlPersonal|Data;strDataEnd,,ControlPersonal|Data;', 'SpitalInterventieChirurgicalaPrincipalaDMS.rpt')

If Not exists (Select * From dbo.Rapoarte Where IdRaport=31)
Insert dbo.Rapoarte(IdRaport, Raport, IdParinte, TipRaport, Parametrii, Locatie)
Values(31, 'Spital_Mortalitate', 14, 'R', 'strDataStart,,ControlPersonal|Data;strDataEnd,,ControlPersonal|Data;', 'SpitalMortalitate.rpt')

If Not exists (Select * From dbo.Rapoarte Where IdRaport=32)
Insert dbo.Rapoarte(IdRaport, Raport, IdParinte, TipRaport, Parametrii, Locatie)
Values(32, 'Spital_NrCazuri', 14, 'R', 'strDataStart,,ControlPersonal|Data;strDataEnd,,ControlPersonal|Data;', 'SpitalNrCazuri.rpt')

If Not exists (Select * From dbo.Rapoarte Where IdRaport=33)
Insert dbo.Rapoarte(IdRaport, Raport, IdParinte, TipRaport, Parametrii, Locatie)
Values(33, 'Spital_NrZileSpitalizare', 14, 'R', 'strDataStart,,ControlPersonal|Data;strDataEnd,,ControlPersonal|Data;', 'SpitalNrZileSpitalizare.rpt')

If Not exists (Select * From dbo.Rapoarte Where IdRaport=34)
Insert dbo.Rapoarte(IdRaport, Raport, IdParinte, TipRaport, Parametrii, Locatie)
Values(34, 'Spital_Proceduri', 14, 'R', 'strDataStart,,ControlPersonal|Data;strDataEnd,,ControlPersonal|Data;', 'SpitalProceduri.rpt')

If Not exists (Select * From dbo.Rapoarte Where IdRaport=35)
Insert dbo.Rapoarte(IdRaport, Raport, IdParinte, TipRaport, Parametrii, Locatie)
Values(35, 'Spital_Sex', 14, 'R', 'strDataStart,,ControlPersonal|Data;strDataEnd,,ControlPersonal|Data;', 'SpitalSex.rpt')

If Not exists (Select * From dbo.Rapoarte Where IdRaport=36)
Insert dbo.Rapoarte(IdRaport, Raport, IdParinte, TipRaport, Parametrii, Locatie)
Values(36, 'Spital_StareExternare', 14, 'R', 'strDataStart,,ControlPersonal|Data;strDataEnd,,ControlPersonal|Data;', 'SpitalStareExternare.rpt')

If Not exists (Select * From dbo.Rapoarte Where IdRaport=37)
Insert dbo.Rapoarte(IdRaport, Raport, IdParinte, TipRaport, Parametrii, Locatie)
Values(37, 'Spital_TipExternare', 14, 'R', 'strDataStart,,ControlPersonal|Data;strDataEnd,,ControlPersonal|Data;', 'SpitalTipExternare.rpt')

If Not exists (Select * From dbo.Rapoarte Where IdRaport=38)
Insert dbo.Rapoarte(IdRaport, Raport, IdParinte, TipRaport, Parametrii, Locatie)
Values(38, 'Spital_TipInternare', 14, 'R', 'strDataStart,,ControlPersonal|Data;strDataEnd,,ControlPersonal|Data;', 'SpitalTipInternare.rpt')

If Not exists (Select * From dbo.Rapoarte Where IdRaport=39)
Insert dbo.Rapoarte(IdRaport, Raport, IdParinte, TipRaport, Parametrii, Locatie)
Values(39, 'Administrator', 1, 'F', Null, Null)

If Not exists (Select * From dbo.Rapoarte Where IdRaport=40)
Insert dbo.Rapoarte(IdRaport, Raport, IdParinte, TipRaport, Parametrii, Locatie)
Values(40, 'Istoric modificari', 39, 'R', 'IdSpital,,MyCombo;strDataStart,,ControlPersonal|Data;strDataEnd,,ControlPersonal|Data;', 'VizualizareModificari.rpt')
Print 'Completare tabel Rapoarte'
--			  END ACTUALIZARE RAPOARTE DACA ESTE CAZUL
