package mmr.oldData.sql;

public class ProcedureSQL {

	private String str="";//"USE `mmr`; ";
	public ProcedureSQL() 
	{		
		//dropProcedure();
		rechercherPatientMedidocNom();	
		rechercherPatientMedidocPrenom();
		/*rechercherPatientMedidocPrenomNom();             
		rechercherPatientMedidocDateNaiss();
		rechercherPatientMedidocNiss();
		insertionDansTablePatient();
		insertionDansTableSoignant();
		insertionDansTableLocalite();	*/	
	}
	
	private void dropProcedure() {
		// TODO Auto-generated method stub
		
	}

	private void rechercherPatientMedidocNom()
	{
		str+=""+//"DROP PROCEDURE IF EXISTS `RECHERCHEPARNOM`; "+
		"CREATE PROCEDURE `RECHERCHEPARNOM`(IN `nom` VARCHAR(50)) "+ 
		"SELECT name, firstname, inv_date, niss "+
		"FROM PATIENT "+
		"INNER JOIN Donneesdiv ON fk_ID_DONNEESDIV = ID_DONNEESDIV "+
		"WHERE name LIKE @nom; ";
	}
	private void rechercherPatientMedidocPrenom()
	{
		str+=""+//"DROP PROCEDURE IF EXISTS `RECHERCHEPARPRENOM`; "+ 
		"CREATE PROCEDURE `RECHERCHEPARPRENOM`(IN `prenom` VARCHAR(50)) "+
		"SELECT name,firstname,inv_date,niss "+ 
		"FROM PATIENT "+ 
		"INNER JOIN Donneesdiv ON fk_ID_DONNEESDIV=ID_DONNEESDIV "+
		"WHERE FIRST_NAME like @prenom; ";
	}
	private void rechercherPatientMedidocPrenomNom()
	{
		str+="DROP PROCEDURE IF EXISTS `RECHERCHEPARPRENOMNOM`; " +
		"CREATE PROCEDURE `RECHERCHEPARPRENOMNOM`(IN `nom` VARCHAR(50), IN `prenom` VARCHAR(50)) " +
		"SELECT name,firstname,inv_date,niss "+
		"FROM PATIENT "+
		"INNER JOIN Donneesdiv ON fk_ID_DONNEESDIV=ID_DONNEESDIV "+
		"WHERE FIRST_NAME LIKE @prenom AND @nom; ";
	}
	private void rechercherPatientMedidocDateNaiss()
	{
		str+="DROP PROCEDURE IF EXISTS `RECHERCHEPARDATENAISS`; "+
		"CREATE PROCEDURE `RECHERCHEPARDATENAISS`(IN `dateNaiss` DATETIME) " +
		"SELECT name,firstname,inv_date,niss " +
		"FROM PATIENT " +
		"INNER JOIN Donneesdiv ON fk_ID_DONNEESDIV=ID_DONNEESDIV " +
		"WHERE inv_date = @dateNaiss; ";
	}
	private void rechercherPatientMedidocNiss()
	{
		str+="DROP PROCEDURE IF EXISTS `RECHERCHEPARNISS`; " +
		"CREATE PROCEDURE `RECHERCHEPARNISS` (IN `niss` VARCHAR(11)) " +
		"SELECT name,firstname,inv_date,niss " +
		"FROM PATIENT " +
		"INNER JOIN Donneesdiv ON fk_ID_DONNEESDIV=ID_DONNEESDIV " +
		"WHERE niss = @niss; ";
	}
	private void insertionDansTablePatient()
	{
		str+="DROP PROCEDURE IF EXISTS `INSERTIONPATIENT`; " +
		"CREATE PROCEDURE `INSERTIONPATIENT` (IN `niss` VARCHAR(11), IN `nompat` VARCHAR(50), IN `prenompat` VARCHAR(50), " +
		"IN `dateNaiss` DATE, IN `rue` VARCHAR(100), IN `numero` VARCHAR(10), IN `numArchive` NUMERIC, " +
		"IN `dateDeces` DATE, IN `causeDeces` VARCHAR(100), IN `fkSoignant` VARCHAR(50), IN `fkLoc` NUMERIC) "+
		"INSERT INTO PATIENT VALUE (@niss,@nompat, @prenompat,@dateNaiss,@rue, @numero, @numArchive, " +
		"@dateDeces,@causeDeces,@fkSoignant,@fkLoc); ";
	}
	private void insertionDansTableSoignant()
	{
		str+="DROP PROCEDURE IF EXISTS `INSERTIONSOIGNANT`; " +
		"CREATE PROCEDURE `INSERTIONSOIGNANT` (IN `idSoignant` VARCHAR(10), IN `nomsSoignant` VARCHAR(50), IN `prenomSoignant` VARCHAR(50)) "+
		"INSERT INTO `SOIGNANT` VALUE (@idSoignant, @nomSoignant, @prenomSoignant); ";
	}
	private void insertionDansTableLocalite()
	{
		str+="DROP PROCEDURE IF EXISTS `INSERTIONLOCALITE`; " +
		"CREATE PROCEDURE `INSERTIONLOCALITE` (IN `idloc` NUMERIC, IN `cp` NUMERIC, IN `ville` VARCHAR(50)) "+
		"INSERT INTO `LOCALITE` VALUE (@idloc,@cp, @ville); ";
	}
	public String toString(){return str;}
	
}