CREATE DATABASE pys
go
USE [physio]
GO
/****** Object:  Table [dbo].[Absence]    Script Date: 20/04/2017 12:16:39 ******/
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 12:16:39 ******/
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 12:16:39 ******/
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 12:16:39 ******/
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 12:16:39 ******/
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 12:16:39 ******/
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 12:16:39 ******/
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 12:16:39 ******/
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 12:16:39 ******/
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 12:16:39 ******/
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 12:16:39 ******/
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 12:16:39 ******/
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 12:16:39 ******/
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 12:16:39 ******/
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 12:16:39 ******/
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 12:16:39 ******/
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 12:16:39 ******/
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 12:16:39 ******/
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 12:16:39 ******/
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 12:16:39 ******/
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 12:16:39 ******/
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


alter 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 l on e.Id_Locale = l.Id_Locale
      END

      --INSERT
    IF @Action = 'INSERT'
      BEGIN
            INSERT INTO Employée
            VALUES (@Nom,@Prénom,@Adresse,@sexe,@Age,@cin,@tele,@Datee,@grade,@Salaire,@id_loc)
      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,id_locale = @id_loc
            WHERE Id_Employée = @id_emp
      END

      --DELETE
    IF @Action = 'DELETE'
      BEGIN
            DELETE FROM Employée
            WHERE Id_Employée = @Id_emp
      END
END

exec AMS_EMP 'insert',null,'mimoun','marzouki','Oujda','F',25,'f45252','056314242','121212','jsdmf',200000,1

--
go
alter 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
create PROCEDURE AMS_Diagnostrique
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 [Login]=@Login,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_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= @tel
            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_rdv int = NULL
AS
BEGIN
      SET NOCOUNT ON;

      --SELECT
    IF @Action = 'SELECT'
      BEGIN
            SELECT s.id_salle,id_rdv,l.nom,s.nom_salle,nommachine
            FROM 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,id_machine = @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

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,id_machine = @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