Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- create database phy
- go
- USE phy
- GO
- /****** Object: Table [dbo].[Absence] Script Date: 20/04/2017 16:11:29 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[Absence](
- [Date_Debut] [datetime] NOT NULL,
- [Date_Fin] [datetime] NOT NULL,
- [Justifiée] [bit] NULL,
- [Id_Employée] [int] NOT NULL,
- [Justification] [nvarchar](50) NULL,
- [id_absence] [int] IDENTITY(1,1) NOT NULL,
- CONSTRAINT [PK_Absence] PRIMARY KEY CLUSTERED
- (
- [id_absence] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[Bilan de Transfert] Script Date: 20/04/2017 16:11:29 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[Bilan de Transfert](
- [DCDtoDCL] [bit] NULL,
- [Déplacement] [bit] NULL,
- [Passisetopdebout] [bit] NULL,
- [PcouchéetePassise] [bit] NULL,
- [Station_allongée] [bit] NULL,
- [Satyion_assise] [bit] NULL,
- [Id_Patient] [int] NOT NULL,
- [id_bilan] [int] IDENTITY(1,1) NOT NULL,
- CONSTRAINT [PK_Bilan de Transfert] PRIMARY KEY CLUSTERED
- (
- [id_bilan] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[Bilan des troubles associés] Script Date: 20/04/2017 16:11:29 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[Bilan des troubles associés](
- [Constipation] [bit] NULL,
- [Psychisme du patient] [bit] NULL,
- [Transipiration excessive] [bit] NULL,
- [Troubles sphinctériens] [bit] NULL,
- [id_bilan] [int] IDENTITY(1,1) NOT NULL,
- [Id_Patient] [int] NOT NULL,
- CONSTRAINT [PK_Bilan des troubles associés_1] PRIMARY KEY CLUSTERED
- (
- [id_bilan] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[Bilan morphologique] Script Date: 20/04/2017 16:11:29 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[Bilan morphologique](
- [Cicatrices] [bit] NULL,
- [Cyphose dorsale] [bit] NULL,
- [Ecchymose] [bit] NULL,
- [Équinisme] [bit] NULL,
- [Flessume de la hanche] [bit] NULL,
- [Flessume du genou] [bit] NULL,
- [Griff des orteils] [bit] NULL,
- [Oedeme] [bit] NULL,
- [Scoliose] [bit] NULL,
- [Syndrome d'algodystrophie] [bit] NULL,
- [Id_bilan] [int] IDENTITY(1,1) NOT NULL,
- CONSTRAINT [PK_Bilan morphologique] PRIMARY KEY CLUSTERED
- (
- [Id_bilan] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[Bilan musculaire] Script Date: 20/04/2017 16:11:29 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[Bilan musculaire](
- [Abdomen] [varchar](50) NULL,
- [Mbr inf] [varchar](50) NULL,
- [Mbr sup] [varchar](50) NULL,
- [Musc resp] [varchar](50) NULL,
- [Tronc thorax] [varchar](50) NULL,
- [Id_Patient] [int] NULL,
- [Id_Bilan] [int] IDENTITY(1,1) NOT NULL,
- CONSTRAINT [PK_Bilan musculaire] PRIMARY KEY CLUSTERED
- (
- [Id_Bilan] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[Bilan réspiratiore] Script Date: 20/04/2017 16:11:29 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[Bilan réspiratiore](
- [Mpt_mamellonnaire] [varchar](50) NULL,
- [Mpt_ss_axillaire] [varchar](50) NULL,
- [Mpt_sternal] [varchar](50) NULL,
- [Id_Patient] [int] NULL,
- [id_bilan] [int] IDENTITY(1,1) NOT NULL,
- CONSTRAINT [PK_Bilan réspiratiore] PRIMARY KEY CLUSTERED
- (
- [id_bilan] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[Bilanfonctionnel] Script Date: 20/04/2017 16:11:29 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[Bilanfonctionnel](
- [Coedination_de_mouvement] [bit] NULL,
- [Descente_descalier] [bit] NULL,
- [équilibre_antéro_post] [bit] NULL,
- [équilibre_poussé_laterale] [bit] NULL,
- [Main_bouche] [bit] NULL,
- [Main_nuque] [bit] NULL,
- [Main_oreille_oppssée] [bit] NULL,
- [Main_poche_arriéere] [bit] NULL,
- [Marche_terrain_accidenté] [bit] NULL,
- [Marcheterrain_normale] [bit] NULL,
- [Montée_escalier] [bit] NULL,
- [Id_Patient] [int] NULL,
- [id_Bilan] [int] IDENTITY(1,1) NOT NULL,
- CONSTRAINT [PK_Bilanfonctionnel] PRIMARY KEY CLUSTERED
- (
- [id_Bilan] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[Compte] Script Date: 20/04/2017 16:11:29 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[Compte](
- [Login] [nvarchar](50) NOT NULL,
- [PW] [nvarchar](50) NOT NULL,
- [Grade] [varchar](50) NULL,
- [Id_Emploée] [int] NULL,
- [Email] [varchar](50) NOT NULL,
- [UserId] [int] IDENTITY(1,1) NOT NULL,
- [UserUID] [uniqueidentifier] NOT NULL,
- [User] [nchar](10) NULL,
- [id_employé] [int] NULL,
- CONSTRAINT [PK_Compte] PRIMARY KEY CLUSTERED
- (
- [UserId] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
- CONSTRAINT [uk_email] UNIQUE NONCLUSTERED
- (
- [Email] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
- CONSTRAINT [unique_login] UNIQUE NONCLUSTERED
- (
- [Login] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[Congé] Script Date: 20/04/2017 16:11:29 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[Congé](
- [Date_D] [datetime] NOT NULL,
- [Date_F] [datetime] NOT NULL,
- [Type] [varchar](50) NULL,
- [Id_Employée] [int] NOT NULL,
- [Id_Congé] [int] IDENTITY(1,1) NOT NULL,
- CONSTRAINT [PK_Congé] PRIMARY KEY CLUSTERED
- (
- [Id_Congé] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[Diagnostique] Script Date: 20/04/2017 16:11:29 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[Diagnostique](
- [Anamnése] [varchar](50) NULL,
- [Diagnostique_etiologique] [varchar](50) NULL,
- [Lésion_oelebrale] [bit] NULL,
- [Lésion_rachidienne] [varchar](50) NULL,
- [Lésion_associés] [varchar](50) NULL,
- [Traitement_chirugicale] [varchar](50) NULL,
- [Traitement_médicale] [varchar](50) NULL,
- [Traitement_orthopédique] [varchar](50) NULL,
- [Id_Patient] [int] NOT NULL,
- [id_bilan] [int] IDENTITY(1,1) NOT NULL,
- CONSTRAINT [PK_Diagnostique] PRIMARY KEY CLUSTERED
- (
- [id_bilan] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[Employée] Script Date: 20/04/2017 16:11:29 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[Employée](
- [Id_Employée] [int] IDENTITY(1,1) NOT NULL,
- [Nom] [varchar](50) NULL,
- [Prénom] [varchar](50) NULL,
- [Adresse] [varchar](50) NULL,
- [Sexe] [char](1) NULL,
- [Age] [int] NULL,
- [Cin] [varchar](50) NULL,
- [Tele] [varchar](50) NULL,
- [Date d'entrée] [datetime] NULL,
- [Grade] [varchar](50) NULL,
- [Salaire] [money] NULL,
- CONSTRAINT [PK_Employée] PRIMARY KEY CLUSTERED
- (
- [Id_Employée] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[Envirennement du patient et de mode de vie] Script Date: 20/04/2017 16:11:29 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[Envirennement du patient et de mode de vie](
- [Age_des_anfants] [int] NULL,
- [Appareillage] [varchar](50) NULL,
- [Conduite] [bit] NULL,
- [Escaliers] [bit] NULL,
- [Loisirs] [varchar](50) NULL,
- [Milleu_urbain] [bit] NULL,
- [vit_en_appartement] [bit] NULL,
- [vit_seul] [bit] NULL,
- [Id_Bilan] [int] IDENTITY(1,1) NOT NULL,
- [Id_Patient] [int] NOT NULL,
- CONSTRAINT [PK_Envirennement du patient et de mode de vie] PRIMARY KEY CLUSTERED
- (
- [Id_Bilan] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[Facture] Script Date: 20/04/2017 16:11:29 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[Facture](
- [Date_Facturation] [datetime] NOT NULL,
- [Intervention] [varchar](50) NULL,
- [Montant_Global] [money] NULL,
- [Nbr_Séances] [int] NULL,
- [Observations] [varchar](50) NULL,
- [Prix_Unitaire] [money] NULL,
- [Id_Employée] [int] NULL,
- [id_Patient] [int] NULL,
- [id_facture] [int] IDENTITY(1,1) NOT NULL,
- CONSTRAINT [PK_Facture] PRIMARY KEY CLUSTERED
- (
- [id_facture] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[Local_Produit] Script Date: 20/04/2017 16:11:29 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[Local_Produit](
- [id_locale] [int] NOT NULL,
- [id_produit] [int] NOT NULL,
- CONSTRAINT [PK_Local_Produit] PRIMARY KEY CLUSTERED
- (
- [id_locale] ASC,
- [id_produit] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[Locale] Script Date: 20/04/2017 16:11:29 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[Locale](
- [Id_Locale] [int] IDENTITY(1,1) NOT NULL,
- [Nom] [varchar](50) NULL,
- [Adresse] [varchar](50) NULL,
- [Tele] [varchar](50) NULL,
- CONSTRAINT [PK_Locale] PRIMARY KEY CLUSTERED
- (
- [Id_Locale] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[Locale_employé] Script Date: 20/04/2017 16:11:29 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[Locale_employé](
- [id_employé] [int] NOT NULL,
- [id_local] [int] NOT NULL,
- CONSTRAINT [PK_Locale_employé] PRIMARY KEY CLUSTERED
- (
- [id_employé] ASC,
- [id_local] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[Machine] Script Date: 20/04/2017 16:11:29 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[Machine](
- [id_machine] [int] IDENTITY(1,1) NOT NULL,
- [id_salle] [int] NULL,
- [nommachine] [nchar](15) NULL,
- [id_rdv] [int] NULL,
- CONSTRAINT [PK_Machine] PRIMARY KEY CLUSTERED
- (
- [id_machine] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[Patient] Script Date: 20/04/2017 16:11:29 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[Patient](
- [id_Patient] [int] IDENTITY(1,1) NOT NULL,
- [Nom] [varchar](50) NULL,
- [Prénom] [varchar](50) NULL,
- [Adresse] [varchar](50) NULL,
- [Sexe] [varchar](50) NULL,
- [Age] [int] NULL,
- [CIN] [varchar](50) NULL,
- [Tele] [varchar](50) NULL,
- [Couverture Medicale] [varchar](50) NULL,
- [Médecin taitant] [varchar](50) NULL,
- [Poid] [int] NULL,
- [Profession] [varchar](50) NULL,
- [Taille] [int] NULL,
- CONSTRAINT [PK_Patient] PRIMARY KEY CLUSTERED
- (
- [id_Patient] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[Produit] Script Date: 20/04/2017 16:11:29 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[Produit](
- [Id_Produit] [int] IDENTITY(1,1) NOT NULL,
- [Nom] [varchar](50) NULL,
- [Type] [varchar](50) NULL,
- [Qte] [int] NULL,
- [Prix Unitaire] [money] NULL,
- [Date achat] [datetime] NULL,
- CONSTRAINT [PK_Produit] PRIMARY KEY CLUSTERED
- (
- [Id_Produit] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[RDV] Script Date: 20/04/2017 16:11:29 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[RDV](
- [Date rdv] [datetime] NOT NULL,
- [Id_Employée] [int] NULL,
- [Id_Patient] [int] NULL,
- [Durée] [int] NULL,
- [Type_soin] [int] NULL,
- [Prix] [money] NULL,
- [id_rdv] [int] IDENTITY(1,1) NOT NULL,
- CONSTRAINT [PK_RDV_1] PRIMARY KEY CLUSTERED
- (
- [id_rdv] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- /****** Object: Table [dbo].[Salle] Script Date: 20/04/2017 16:11:29 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[Salle](
- [id_salle] [int] IDENTITY(1,1) NOT NULL,
- [nom_salle] [nchar](15) NULL,
- [spécialité] [nchar](15) NULL,
- [id_local] [int] NULL,
- CONSTRAINT [PK_Salle] PRIMARY KEY CLUSTERED
- (
- [id_salle] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- ALTER TABLE [dbo].[Compte] ADD CONSTRAINT [DF_Compte_UserUID] DEFAULT (newid()) FOR [UserUID]
- GO
- ALTER TABLE [dbo].[Absence] WITH CHECK ADD CONSTRAINT [FK_Absence_emp] FOREIGN KEY([Id_Employée])
- REFERENCES [dbo].[Employée] ([Id_Employée])
- GO
- ALTER TABLE [dbo].[Absence] CHECK CONSTRAINT [FK_Absence_emp]
- GO
- ALTER TABLE [dbo].[Bilan de Transfert] WITH CHECK ADD CONSTRAINT [FK_Bilan de Transfert_Patient] FOREIGN KEY([Id_Patient])
- REFERENCES [dbo].[Patient] ([id_Patient])
- GO
- ALTER TABLE [dbo].[Bilan de Transfert] CHECK CONSTRAINT [FK_Bilan de Transfert_Patient]
- GO
- ALTER TABLE [dbo].[Bilan des troubles associés] WITH CHECK ADD CONSTRAINT [FK_Bilandes trouble associés_Patient] FOREIGN KEY([Id_Patient])
- REFERENCES [dbo].[Patient] ([id_Patient])
- GO
- ALTER TABLE [dbo].[Bilan des troubles associés] CHECK CONSTRAINT [FK_Bilandes trouble associés_Patient]
- GO
- ALTER TABLE [dbo].[Bilan morphologique] WITH CHECK ADD CONSTRAINT [FK_Bilan morphologique_Patient] FOREIGN KEY([Id_bilan])
- REFERENCES [dbo].[Patient] ([id_Patient])
- GO
- ALTER TABLE [dbo].[Bilan morphologique] CHECK CONSTRAINT [FK_Bilan morphologique_Patient]
- GO
- ALTER TABLE [dbo].[Bilan musculaire] WITH CHECK ADD CONSTRAINT [FK_Bilan musculaire_Patient] FOREIGN KEY([Id_Patient])
- REFERENCES [dbo].[Patient] ([id_Patient])
- GO
- ALTER TABLE [dbo].[Bilan musculaire] CHECK CONSTRAINT [FK_Bilan musculaire_Patient]
- GO
- ALTER TABLE [dbo].[Bilan réspiratiore] WITH CHECK ADD CONSTRAINT [FK_Bilan réspiratiore_Patient] FOREIGN KEY([Id_Patient])
- REFERENCES [dbo].[Patient] ([id_Patient])
- GO
- ALTER TABLE [dbo].[Bilan réspiratiore] CHECK CONSTRAINT [FK_Bilan réspiratiore_Patient]
- GO
- ALTER TABLE [dbo].[Bilanfonctionnel] WITH CHECK ADD CONSTRAINT [FK_Bilanfonctionnel_Patient] FOREIGN KEY([Id_Patient])
- REFERENCES [dbo].[Patient] ([id_Patient])
- GO
- ALTER TABLE [dbo].[Bilanfonctionnel] CHECK CONSTRAINT [FK_Bilanfonctionnel_Patient]
- GO
- ALTER TABLE [dbo].[Compte] WITH CHECK ADD CONSTRAINT [FK_Compte_Employée] FOREIGN KEY([Id_Emploée])
- REFERENCES [dbo].[Employée] ([Id_Employée])
- GO
- ALTER TABLE [dbo].[Compte] CHECK CONSTRAINT [FK_Compte_Employée]
- GO
- ALTER TABLE [dbo].[Congé] WITH CHECK ADD CONSTRAINT [FK_Congé_Employée] FOREIGN KEY([Id_Employée])
- REFERENCES [dbo].[Employée] ([Id_Employée])
- GO
- ALTER TABLE [dbo].[Congé] CHECK CONSTRAINT [FK_Congé_Employée]
- GO
- ALTER TABLE [dbo].[Diagnostique] WITH CHECK ADD CONSTRAINT [FK_Diagnostique_Patient] FOREIGN KEY([Id_Patient])
- REFERENCES [dbo].[Patient] ([id_Patient])
- GO
- ALTER TABLE [dbo].[Diagnostique] CHECK CONSTRAINT [FK_Diagnostique_Patient]
- GO
- ALTER TABLE [dbo].[Envirennement du patient et de mode de vie] WITH CHECK ADD CONSTRAINT [FK_Envirennement du patient et de mode de vie_Patient] FOREIGN KEY([Id_Patient])
- REFERENCES [dbo].[Patient] ([id_Patient])
- GO
- ALTER TABLE [dbo].[Envirennement du patient et de mode de vie] CHECK CONSTRAINT [FK_Envirennement du patient et de mode de vie_Patient]
- GO
- ALTER TABLE [dbo].[Facture] WITH CHECK ADD CONSTRAINT [FK_Facture_Employée] FOREIGN KEY([Id_Employée])
- REFERENCES [dbo].[Employée] ([Id_Employée])
- GO
- ALTER TABLE [dbo].[Facture] CHECK CONSTRAINT [FK_Facture_Employée]
- GO
- ALTER TABLE [dbo].[Facture] WITH CHECK ADD CONSTRAINT [FK_Facture_Patient] FOREIGN KEY([id_Patient])
- REFERENCES [dbo].[Patient] ([id_Patient])
- GO
- ALTER TABLE [dbo].[Facture] CHECK CONSTRAINT [FK_Facture_Patient]
- GO
- ALTER TABLE [dbo].[Local_Produit] WITH CHECK ADD CONSTRAINT [FK_Local_Produit_Locale] FOREIGN KEY([id_produit])
- REFERENCES [dbo].[Produit] ([Id_Produit])
- GO
- ALTER TABLE [dbo].[Local_Produit] CHECK CONSTRAINT [FK_Local_Produit_Locale]
- GO
- ALTER TABLE [dbo].[Local_Produit] WITH CHECK ADD CONSTRAINT [FK_Local_Produit_Locale1] FOREIGN KEY([id_locale])
- REFERENCES [dbo].[Locale] ([Id_Locale])
- GO
- ALTER TABLE [dbo].[Local_Produit] CHECK CONSTRAINT [FK_Local_Produit_Locale1]
- GO
- ALTER TABLE [dbo].[Locale_employé] WITH CHECK ADD CONSTRAINT [FK_employé] FOREIGN KEY([id_employé])
- REFERENCES [dbo].[Employée] ([Id_Employée])
- GO
- ALTER TABLE [dbo].[Locale_employé] CHECK CONSTRAINT [FK_employé]
- GO
- ALTER TABLE [dbo].[Locale_employé] WITH CHECK ADD CONSTRAINT [FK_Locale] FOREIGN KEY([id_local])
- REFERENCES [dbo].[Locale] ([Id_Locale])
- GO
- ALTER TABLE [dbo].[Locale_employé] CHECK CONSTRAINT [FK_Locale]
- GO
- ALTER TABLE [dbo].[Machine] WITH CHECK ADD CONSTRAINT [FK_Machine_rdv] FOREIGN KEY([id_rdv])
- REFERENCES [dbo].[RDV] ([id_rdv])
- GO
- ALTER TABLE [dbo].[Machine] CHECK CONSTRAINT [FK_Machine_rdv]
- GO
- ALTER TABLE [dbo].[Machine] WITH CHECK ADD CONSTRAINT [FK_Machine_Salle] FOREIGN KEY([id_salle])
- REFERENCES [dbo].[Salle] ([id_salle])
- ON UPDATE SET NULL
- ON DELETE SET NULL
- GO
- ALTER TABLE [dbo].[Machine] CHECK CONSTRAINT [FK_Machine_Salle]
- GO
- ALTER TABLE [dbo].[RDV] WITH CHECK ADD CONSTRAINT [FK_RDV_Employée] FOREIGN KEY([Id_Employée])
- REFERENCES [dbo].[Employée] ([Id_Employée])
- GO
- ALTER TABLE [dbo].[RDV] CHECK CONSTRAINT [FK_RDV_Employée]
- GO
- ALTER TABLE [dbo].[RDV] WITH CHECK ADD CONSTRAINT [id_RDV_Patient] FOREIGN KEY([Id_Patient])
- REFERENCES [dbo].[Patient] ([id_Patient])
- GO
- ALTER TABLE [dbo].[RDV] CHECK CONSTRAINT [id_RDV_Patient]
- GO
- ALTER TABLE [dbo].[Salle] WITH CHECK ADD CONSTRAINT [FK_Salle_Local] FOREIGN KEY([id_local])
- REFERENCES [dbo].[Locale] ([Id_Locale])
- ON UPDATE SET NULL
- ON DELETE SET NULL
- GO
- ALTER TABLE [dbo].[Salle] CHECK CONSTRAINT [FK_Salle_Local]
- GO
- ALTER TABLE [dbo].[Compte] WITH CHECK ADD CONSTRAINT [CK_pw] CHECK ((len([PW])>(5)))
- GO
- ALTER TABLE [dbo].[Compte] CHECK CONSTRAINT [CK_pw]
- GO
- create PROCEDURE AMS_EMP
- @Action VARCHAR(10)
- ,@Id_emp int = null
- ,@Nom varchar(50) = NULL
- ,@Prénom VARCHAR(50) = NULL
- ,@Adresse VARCHAR(50) = NULL
- ,@sexe CHAR(1) = NULL
- ,@Age int = NULL
- ,@Cin varchar(20) = Null
- ,@Tele VARCHAR(50) = NULL
- ,@Datee date = NULL
- ,@grade VARCHAR(50) = NULL
- ,@Salaire money = NULL
- ,@id_loc int = NULL
- AS
- BEGIN
- SET NOCOUNT ON;
- --SELECT
- IF @Action = 'SELECT'
- BEGIN
- SELECT e.Nom,e.Prénom,e.Adresse,e.Sexe,e.Age,e.Cin,e.Tele 'Téléphone',e.[Date d'entrée],e.Grade,e.Salaire,l.Nom,e.Id_Employée
- FROM Employée e left join Locale_employé le on e.Id_Employée = le.id_employé right join Locale l on l.Id_Locale=le.id_local
- END
- --INSERT
- IF @Action = 'INSERT'
- BEGIN
- INSERT INTO Employée
- VALUES (@Nom,@Prénom,@Adresse,@sexe,@Age,@cin,@tele,@Datee,@grade,@Salaire)
- END
- --UPDATE
- IF @Action = 'UPDATE'
- BEGIN
- UPDATE Employée
- SET nom =@Nom,prénom = @Prénom, Adresse=@Adresse, sexe = @sexe, age = @Age , cin = @cin, tele = @tele, [Date d'entrée] = @Datee, grade = @grade, salaire = @Salaire
- WHERE Id_Employée = @id_emp
- END
- --DELETE
- IF @Action = 'DELETE'
- BEGIN
- DELETE FROM Employée
- WHERE Id_Employée = @Id_emp
- END
- END
- --
- go
- create PROCEDURE AMS_Compte
- @Action VARCHAR(10)
- ,@userID int = NULL
- ,@Login varchar(50) = null
- ,@pw varchar(50) = NULL
- ,@Grade VARCHAR(50) = NULL
- ,@Id_emploée int = NULL
- ,@Email varchar(50) = NULL
- ,@UserUID int = NULL
- AS
- BEGIN
- SET NOCOUNT ON;
- --SELECT
- IF @Action = 'SELECT'
- BEGIN
- SELECT c.UserId, c.[Login],c.PW 'Mot de passe', c.Grade, c.Email ,c.UserUID,e.Nom+''+e.Prénom 'Nom et Prénom',c.Id_Emploée
- FROM Compte c left join employée e on e.Id_Employée = c.Id_Emploée
- END
- --INSERT
- IF @Action = 'INSERT'
- BEGIN
- INSERT INTO Compte
- ([Login],pw,Grade,Id_emploée,email)
- VALUES (@Login,@pw,@Grade,@Id_emploée,@email)
- END
- --UPDATE
- IF @Action = 'UPDATE'
- BEGIN
- UPDATE Compte
- SET pw=@pw,Grade=@Grade,Id_emploée=@Id_emploée,email=Email
- WHERE [Login] = @Login
- END
- --DELETE
- IF @Action = 'DELETE'
- BEGIN
- DELETE FROM Compte
- WHERE [Login] = @Login
- END
- END
- exec AMS_EMP 'insert',null,'mimoun','marzouki','Oujda','F',25,'f45252','056314242','121212','jsdmf',200000,1
- --
- go
- create PROCEDURE AMS_ABSENCE
- @Action VARCHAR(10)
- ,@id_congé int = null
- ,@id_employée int = null
- ,@Date_debut date = null
- ,@Date_fin date = NULL
- ,@Justifiée bit = NULL
- ,@Justification VARCHAR(50) = NULL
- AS
- BEGIN
- SET NOCOUNT ON;
- --SELECT
- IF @Action = 'SELECT'
- BEGIN
- SELECT e.Nom+''+e.Prénom as 'Nom Complet',a.Date_Debut 'Date début',a.Date_Fin 'Date fin', Justifiée,Justification,e.Id_Employée
- FROM Absence a left join Employée e on e.Id_Employée = a.Id_Employée
- END
- --INSERT
- IF @Action = 'INSERT'
- BEGIN
- INSERT INTO Absence
- VALUES (@Date_debut,@Date_fin,@Justifiée,@id_employée,@Justification)
- END
- --UPDATE
- IF @Action = 'UPDATE'
- BEGIN
- UPDATE Absence
- SET Date_debut = @Date_debut,Date_fin = @Date_fin,Id_Employée = @id_employée,Justification= @Justification
- WHERE Id_Employée = @id_employée
- END
- --DELETE
- IF @Action = 'DELETE'
- BEGIN
- DELETE FROM Absence
- WHERE Id_Employée = @id_employée
- END
- END
- go
- create PROCEDURE AMS_Congé
- @Action VARCHAR(10)
- ,@date_d date = null
- ,@Date_f date = NULL
- ,@type varchar(50) = NULL
- ,@Id_Employée VARCHAR(50) = NULL
- AS
- BEGIN
- SET NOCOUNT ON;
- --SELECT
- IF @Action = 'SELECT'
- BEGIN
- SELECT e.Nom+''+e.Prénom as 'Nom Complet',C.Date_D 'Date début',c.Date_F 'Date fin',[type],e.Id_Employée
- FROM Congé C left join Employée e on e.Id_Employée = c.Id_Employée
- END
- --INSERT
- IF @Action = 'INSERT'
- BEGIN
- INSERT INTO Congé
- VALUES (@Date_d,@Date_f,@type,@id_employée)
- END
- --UPDATE
- IF @Action = 'UPDATE'
- BEGIN
- UPDATE congé
- SET Date_d = @date_d, Date_f = @date_f ,[type] = @type
- WHERE Id_Employée = @id_employée
- END
- --DELETE
- IF @Action = 'DELETE'
- BEGIN
- DELETE FROM Congé
- WHERE Id_Employée = @id_employée
- END
- END
- go
- create PROCEDURE AMS_Facture
- @Action VARCHAR(10)
- ,@date_facturation datetime = NULL
- ,@intervention varchar(50) = null
- ,@montant_global money = NULL
- ,@nbr_séances int = NULL
- ,@Observations varchar(50) = null
- ,@Prix_Unitaire money = NULL
- ,@Email varchar(50) = NULL
- ,@id_employée int = NULL
- ,@id_patient int = NULL
- ,@id_facture int = NULL
- AS
- BEGIN
- SET NOCOUNT ON;
- --SELECT
- IF @Action = 'SELECT'
- BEGIN
- SELECT f.id_Patient, f.id_facture, f.Id_Employée,f.Date_Facturation,f.Intervention,f.Nbr_Séances,f.Observations,f.Prix_Unitaire
- FROM employée e left join Facture f on e.Id_Employée = F.Id_Employée right join patient p on p.id_Patient=f.id_Patient
- END
- --INSERT
- IF @Action = 'INSERT'
- BEGIN
- INSERT INTO Facture
- (Date_Facturation,Intervention,Nbr_Séances,Observations,Prix_Unitaire,Id_Employée,id_Patient)
- VALUES (@Date_Facturation,@Intervention,@Nbr_Séances,@Observations,@Prix_Unitaire,@Id_Employée,@id_Patient)
- END
- --UPDATE
- IF @Action = 'UPDATE'
- BEGIN
- UPDATE Facture
- SET Date_Facturation=@Date_Facturation,Intervention=@intervention,Nbr_Séances=@nbr_séances,Observations=@Observations,Prix_Unitaire=@Prix_Unitaire,Id_Employée=@id_employée,id_Patient=@id_patient
- WHERE id_facture=@id_facture
- END
- --DELETE
- IF @Action = 'DELETE'
- BEGIN
- DELETE FROM Facture
- WHERE id_facture=@id_facture
- END
- END
- --
- go
- create PROCEDURE AMS_Patient
- @Action VARCHAR(10)
- ,@id_Patient int = NULL
- ,@Nom varchar(50) = null
- ,@Prénom varchar(50) = null
- ,@Adresse varchar(50) = NULL
- ,@Sexe varchar(50) = NULL
- ,@Age int = null
- ,@CIN varchar(50) = NULL
- ,@Tele varchar(50) = NULL
- ,@Couverture_Médicale varchar(50) = NULL
- ,@Médecin_traitant varchar(50) = NULL
- ,@Poid int = NULL
- ,@Profession varchar(50) = NULL
- ,@Taille int = NULL
- AS
- BEGIN
- SET NOCOUNT ON;
- --SELECT
- IF @Action = 'SELECT'
- BEGIN
- SELECT p.id_Patient, p.Nom, p.Prénom,p.Adresse,p.Sexe,p.Age,p.CIN,p.Tele'Téléphone',p.[Couverture Medicale],p.Poid,p.Profession,p.Taille
- FROM Patient p
- END
- --INSERT
- IF @Action = 'INSERT'
- BEGIN
- INSERT INTO Patient
- (Nom, Prénom, Adresse, Sexe, Age, CIN, Tele,[Couverture Medicale],Poid,Profession,Taille)
- VALUES (@Nom, @Prénom, @Adresse, @Sexe, @Age, @CIN, @Tele, @Couverture_Médicale, @Poid, @Profession, @Taille)
- END
- --UPDATE
- IF @Action = 'UPDATE'
- BEGIN
- UPDATE patient
- SET Nom=@Nom, Prénom=@Nom, Adresse=@Adresse, Sexe= @Sexe, Age =@Age, CIN =@CIN, Tele=@Tele,[Couverture Medicale] =@Couverture_Médicale,Poid= @Poid,Profession = @Profession,Taille =@Taille
- WHERE id_Patient=@id_Patient
- END
- --DELETE
- IF @Action = 'DELETE'
- BEGIN
- DELETE FROM patient
- WHERE id_Patient=@id_Patient
- END
- END
- --
- go
- create PROCEDURE AMS_Produit
- @Action VARCHAR(10)
- ,@id_Produit int = NULL
- ,@Nom varchar(50) = null
- ,@type varchar(50) = null
- ,@qte int = NULL
- ,@PrixUnitaire money = NULL
- ,@Dateachat datetime null
- AS
- BEGIN
- SET NOCOUNT ON;
- --SELECT
- IF @Action = 'SELECT'
- BEGIN
- SELECT p.Id_Produit, p.Nom, p.[Type],p.Qte,p.[Prix Unitaire],p.[Date achat]
- FROM Produit p
- END
- --INSERT
- IF @Action = 'INSERT'
- BEGIN
- INSERT INTO Produit
- (Nom,[Type],Qte,[Prix Unitaire],[Date achat])
- VALUES (@Nom, @type,@qte,@PrixUnitaire,@Dateachat)
- END
- --UPDATE
- IF @Action = 'UPDATE'
- BEGIN
- UPDATE Produit
- SET Nom=@Nom, [Type]=@type,Qte=@qte,[Prix Unitaire]=@PrixUnitaire,[Date achat]=@Dateachat
- WHERE Id_Produit=@id_Produit
- END
- --DELETE
- IF @Action = 'DELETE'
- BEGIN
- DELETE FROM Produit
- WHERE Id_Produit=@id_Produit
- END
- END
- --
- go
- create PROCEDURE AMS_RDV
- @Action VARCHAR(10)
- ,@id_rdv int
- ,@id_employé int = null
- ,@id_patient int = null
- ,@daterdv datetime = NULL
- ,@durée float = NULL
- ,@type_soin varchar(25) = NULL
- ,@prix money null
- AS
- BEGIN
- SET NOCOUNT ON;
- --SELECT
- IF @Action = 'SELECT'
- BEGIN
- SELECT r.id_rdv, r.Durée, r.Id_Employée,r.Id_Patient,r.Prix,r.Type_soin,r.[Date rdv]
- FROM rdv r
- END
- --INSERT
- IF @Action = 'INSERT'
- BEGIN
- INSERT INTO RDV
- ([Date rdv],Id_Employée,Id_Patient,Durée,Type_soin,Prix)
- VALUES (@daterdv,@id_employé,@id_patient,@durée,@type_soin,@prix)
- END
- --UPDATE
- IF @Action = 'UPDATE'
- BEGIN
- UPDATE RDV
- SET [Date rdv] = @daterdv,Id_Employée=@id_employé,Id_Patient= @id_patient,Durée=@Durée,Type_soin=@type_soin,prix =@Prix
- WHERE id_rdv = @id_rdv
- END
- --DELETE
- IF @Action = 'DELETE'
- BEGIN
- DELETE FROM RDV
- WHERE @id_rdv=id_rdv
- END
- END
- --
- go
- create PROCEDURE AMS_Local
- @Action VARCHAR(10)
- ,@id_locale int = null
- ,@Nom varchar(50) = NULL
- ,@Adresse varchar(50) = NULL
- ,@Tele VARCHAR(50) = NULL
- AS
- BEGIN
- SET NOCOUNT ON;
- --SELECT
- IF @Action = 'SELECT'
- BEGIN
- SELECT Id_Locale,nom,Adresse, Tele'Téléphone'
- FROM Locale L
- END
- --INSERT
- IF @Action = 'INSERT'
- BEGIN
- INSERT INTO Locale
- VALUES (@nom,@Adresse,@tele)
- END
- --UPDATE
- IF @Action = 'UPDATE'
- BEGIN
- UPDATE Locale
- SET nom=@nom,Adresse=@Adresse,Tele= @tele
- WHERE Id_Locale = @id_locale
- END
- --DELETE
- IF @Action = 'DELETE'
- BEGIN
- DELETE FROM Locale
- WHERE Id_Locale = @id_locale
- END
- END
- go
- create PROCEDURE AMS_salle
- @Action VARCHAR(10)
- ,@id_salle int = null
- ,@nom_salle varchar(15) = NULL
- ,@spécialité varchar(15) = NULL
- ,@id_local int = NULL
- AS
- BEGIN
- SET NOCOUNT ON;
- --SELECT
- IF @Action = 'SELECT'
- BEGIN
- SELECT s.id_salle,id_local,s.nom_salle
- FROM salle s right join locale l on s.id_local=l.Id_Locale
- END
- --INSERT
- IF @Action = 'INSERT'
- BEGIN
- INSERT INTO Salle
- VALUES (@nom_salle,@spécialité,@id_local)
- END
- --UPDATE
- IF @Action = 'UPDATE'
- BEGIN
- UPDATE salle
- SET nom_salle=@nom_salle,spécialité=@spécialité
- WHERE id_local = @id_local
- END
- --DELETE
- IF @Action = 'DELETE'
- BEGIN
- DELETE FROM Salle
- WHERE id_salle= @id_salle
- END
- END
- go
- create PROCEDURE AMS_Machine
- @Action VARCHAR(10)
- ,@id_machine int = null
- ,@id_salle int = NULL
- ,@nommachine varchar(50) = NULL
- ,@id_rdv int = NULL
- AS
- BEGIN
- SET NOCOUNT ON;
- --SELECT
- IF @Action = 'SELECT'
- BEGIN
- SELECT id_machine,s.id_salle,id_rdv,l.nom,s.nom_salle,nommachine
- FROM Machine m left join salle s on s.id_salle=m.id_salle
- right join locale l on s.id_local=l.Id_Locale
- END
- --INSERT
- IF @Action = 'INSERT'
- BEGIN
- INSERT INTO Machine
- VALUES (@id_salle,@nommachine,@id_rdv)
- END
- --UPDATE
- IF @Action = 'UPDATE'
- BEGIN
- UPDATE machine
- SET id_salle= @id_salle,nommachine = @nommachine,id_rdv=@id_rdv
- WHERE id_machine = @id_machine
- END
- --DELETE
- IF @Action = 'DELETE'
- BEGIN
- DELETE FROM Machine
- WHERE id_machine= @id_machine
- END
- END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement