actividad 18

CREATE table paciente(
    idPaciente varchar(30) primary key,
    nombre varchar(45),
    apellido varchar(45),
    fechaNacimiento Date,
    EstadoCivil varchar(10),
    Profesion varchar(45),
    Telf int,
    sexo char,
    seguroMedico bool
 );

  CREATE table receta(
    idReceta int primary key auto_increment,
    dosis varchar(45),
    preescripcion varchar(45),
    paciente_id int,
    //farmaco_id int,
  
 );
CREATE table farmaco(
 idFarmaco int primary key auto_increment,
 nombre varchar(50),
 notas varchar(150),
 receta_id int,
);


 CREATE table cita(
    idCita int primary key auto_increment,
    fecha DATE,
    notas varchar(200),
    paciente_id int,
 );

 CREATE table consulta(
    idConsulta int primary key auto_increment,
    historiaClinica varchar(200),
    tratamiento varchar(200),
    diagnostico varchar(200),
    paciente_id int,
    foreign key(paciente_id) references Paciente(idPaciente)
 ); CREATE table paciente(
    idPaciente varchar(30) primary key,
    nombre varchar(45),
    apellido varchar(45),
    fechaNacimiento Date,
    EstadoCivil varchar(10),
    Profesion varchar(45),
    Telf int,
    sexo char,
    seguroMedico bool
 );

  CREATE table receta(
    idReceta int primary key auto_increment,
    farmaco_id int primary key auto_increment,
    dosis varchar(45),
    preescripcion varchar(45),
    paciente_id varchar(30),
    farmaco_id int,
 
 );
CREATE table farmaco(
 idFarmaco int primary key auto_increment,
 nombre varchar(50),
 notas varchar(150),
 receta_id int,
);


 CREATE table cita(
    idCita int primary key auto_increment,
    fecha DATE,
    notas varchar(100),
    paciente_id varchar(30),
 );

 CREATE table consulta(
    idConsulta int primary key auto_increment,
    historiaClinica varchar(100),
    tratamiento varchar(100),
    diagnostico varchar(150),
    paciente_id varchar(30),
 );

 select * from paciente;

 insert into paciente values('0911111111', 'Jose', 'Cevallos', '2013-07-01', 'Soltero', 'arquero', '234566', 'M', '1');
 insert into paciente values('0919666818', 'Daniel Andres', 'Paez', '1989-11-04', 'Soltero', 'Estudiante', '2884926', 'M', '0');
insert into paciente values('0921735627', 'Gabriela', 'Game', '1988-06-04', 'Soltero', 'estudiante', '983622669', 'F', '0');
insert into paciente values('0922222222', 'juan', 'perez', '2013-07-01', 'Soltero', 'estudiante', '2222222', 'M', '1');
delimiter //
create procedure nuevoPaciente
(in cedula varchar(30), in nombre varchar(30) , in apellido varchar(30),
in fechaNac date, in estCivil varchar(20),
in prof varchar(30), in telf int, in sexo char, in segMedico bool)
begin
insert into paciente values
(cedula,nombre,apellido,fechaNac,estCivil,prof,telf,sexo,segMedico);
end//
delimiter ;

delimiter //
create procedure BuscarPacientePorCedula
(in cedula varchar(30), out idPaciente varchar(30),  out nombre varchar(30),
out apellido varchar(30),out fechaNacimiento date, out estadoCivil varchar(15),
out profesion varchar(30),out telf int)
begin
select p.idPaciente into idPaciente from paciente p where p.idPaciente = cedula;
select p.nombre into nombre from paciente p where p.idPaciente = cedula;
select p.apellido into apellido from paciente p where p.idPaciente = cedula;
select p.fechaNacimiento into fechaNacimiento from paciente p where p.idPaciente = cedula;
select p.estadoCivil into estadoCivil from paciente p where p.idPaciente = cedula;
select p.profesion into profesion from paciente p where p.idPaciente = cedula;
select p.telf into telf from paciente p where p.idPaciente = cedula;
end//
delimiter ;
call BuscarPacientePorCedula('0919666818',@a,@b,@c,@d,@e,@f,@g);
drop procedure BuscarPacientePorCedula;

delimiter //
create procedure editarPaciente
(in cedula varchar(30), in nombre varchar(30) , in apellido varchar(30),
in fechaNac date, in estCivil varchar(20),
in prof varchar(30), in telf int, in sexo char, in segMedico bool)
begin
update paciente p set p.nombre=nombre,p.apellido=apellido,p.fechaNacimiento=fechaNac,
p.EstadoCivil=estCivil,p.profesion=prof,p.telf=telf,p.sexo=sexo,p.seguroMedico=segMedico
where p.idPaciente=cedula;
end//
delimiter ;
call editarPaciente('0912345678', 'Pepito', 'Paez', '1989-10-10', 'Soltero', 'Estudiante', '2223344', 'M', '0');
delimiter //
create procedure BuscarPacientePorApellido
(in apellidoin varchar(30), out idPaciente varchar(30),  out nombre varchar(30),
out apellido varchar(30),out fechaNacimiento date, out estadoCivil varchar(15),
out profesion varchar(30),out telf int)
begin
select p.idPaciente into idPaciente from paciente p where p.apellido = apellidoin;
select p.nombre into nombre from paciente p where p.apellido = apellidoin;
select p.apellido into apellido from paciente p where p.apellido = apellidoin;
select p.fechaNacimiento into fechaNacimiento from paciente p where p.apellido = apellidoin;
select p.estadoCivil into estadoCivil from paciente p where p.apellido = apellidoin;
select p.profesion into profesion from paciente p where p.apellido = apellidoin;
select p.telf into telf from paciente p where p.apellido = apellidoin;
end//
delimiter ;
delimiter //
create procedure seleccionarPacientePorApellido(in apellidoin varchar(30))
begin
select p.idPaciente,p.nombre,p.apellido from paciente p where p.apellido like concat('%',apellidoin,'%');
end//
delimiter ;
drop procedure BuscarPacientePorApellido;
call seleccionarPacientePorApellido('paez');
select * from paciente;
delete from paciente where idPaciente='998765432';
insert into paciente values('0912345678', 'Pepe', 'Paez', '1989-10-10', 'Soltero', 'Estudiante', '2223344', 'M', '0');


delimiter //
create procedure nuevoFarmaco
(in nombre varchar(50),in notas varchar(150))
begin
insert into farmaco values(farmaco.idFarmaco,nombre,notas,null);
end//
delimiter ;
call nuevoFarmaco('loratadina','para alergias');
select * from farmaco;

delimiter //
create procedure seleccionarFarmacos()
begin
select f.nombre,f.notas from farmaco f;
end//
delimiter ;
call seleccionarFarmacos();

delimiter //
create procedure cargarDatosFarmaco
(in nombre varchar(30),out idf int,out nombreF varchar(30),out notas varchar(150))
begin
select f.idFarmaco into idf from farmaco f where f.nombre = nombre;
select f.nombre into nombreF from farmaco f where f.nombre = nombre;
select f.notas into notas from farmaco f where f.nombre = nombre;
end//
delimiter ;
drop procedure cargarDatosFarmaco;
call cargarDatosFarmaco('alercet',@a,@b);
delimiter //
create procedure editarFarmacos
(in idFarmaco int, in nombre varchar(30), in notas varchar(150))
begin
update farmaco f set f.nombre=nombre, f.notas=notas where f.idFarmaco=idFarmaco;
end//
delimiter ;
drop procedure editarFarmacos;

delimiter //
create procedure seleccionarFarmacos()
begin
select f.idFarmaco,f.nombre,f.notas from farmaco f;
end//
delimiter ;
call seleccionarFarmacos();
drop procedure seleccionarFarmacos;

FARMACO_ID Y PACIENTE_ID EN RECETA FALTA HACERLE FOREIGN KEY

alter table receta drop foreign key paciente_id;
alter table receta drop foreign key farmaco_id;
alter table receta drop primary key, add primary key (idReceta, farmaco_id);
insert into receta values(receta.idReceta,3,'despues del almuerzo','0919666818',1);
insert into receta values(1,2,'despues de la merienda','0919666818',2);



delimiter //
create procedure cargarCitasPorCedula(in cedula varchar(10))
begin
select p.idPaciente,p.nombre,p.apellido,c.fecha,c.hora,c.historialClinico,
c.diagnostico,c.tratamiento
from paciente p inner join cita c on p.idPaciente = c.paciente_id
where p.idPaciente = cedula;
end//
delimiter ;
call cargarCitasPorCedula('0921735627');

delimiter //
create procedure cargarCitasPorFecha(in fechainicio date, in fechafin date)
begin
select p.idPaciente,p.nombre,p.apellido,c.fecha,c.hora,c.historialClinico,
c.diagnostico,c.tratamiento
from paciente p inner join cita c on p.idPaciente = c.paciente_id
where c.fecha between fechainicio and fechafin;
end//
delimiter ;
call cargarCitasPorFecha('2013-08-05','2013-08-06');

alter table cita add column historialClinico varchar(150);
alter table cita add column diagnostico varchar(150);
alter table cita add column tratamiento varchar(150);
drop table consulta;
select * from cita;
update cita set historialClinico='alergias' where idCita=1;
update cita set historialClinico='higado graso' where idCita=2;
update cita set historialClinico='hipertension' where idCita=3;
update cita set diagnostico='estrornudos, tos por acaros' where idCita=1;
update cita set diagnostico='dolor estomacal, gases' where idCita=2;
update cita set diagnostico='mareos y desmayos' where idCita=3;
update cita set tratamiento='vaporizaciones, inyecciones, no comer citricos' where idCita=1;
update cita set tratamiento='no comidas grasosas' where idCita=2;
update cita set tratamiento='bajar el nivel de sal en las comidas'where idCita=3;


delimiter //
create procedure guardarCita
(in fecha date, in paciente_id varchar(30),in hora time,
historialClinico varchar(150),in diagnostico varchar(150), in tratamiento varchar(150))
begin
insert into cita values (cita.idCita,fecha,paciente_id,hora,historialClinico,diagnostico,tratamiento);
end//
delimiter ;

delimiter //
create procedure cargarHistorialClinico(in  cedula varchar(30))
begin
select distinct c.historialClinico, c.diagnostico, c.tratamiento from paciente p,cita c where cedula = c.paciente_id;
end//
delimiter ;


delimiter //
create procedure obtenerIdCita()
begin
select max(idCita) from cita;
end//
delimiter ;

delimiter //
create procedure obtenerIdCitayIdPaciente()
begin
select max(idCita) as numCita,c.paciente_id,p.nombre,p.apellido from cita c,paciente p
where idCita = (select max(idCita) from cita) and c.paciente_id = p.idPaciente;
end//
delimiter ;
drop procedure obtenerIdCitayIdPaciente;
call obtenerIdCitayIdPaciente();

alter table receta add column cita_id int;
alter table farmaco drop column receta_id;
alter table farmaco add column numVecesUsado int;
update farmaco set numVecesUsado = 0 where idFarmaco>0;
update receta set cita_id = 2 where farmaco_id = 2;

delimiter //
create procedure cargarCitasExistentes()
begin
select c.fecha,c.hora,p.nombre,p.apellido from cita c inner join paciente p on c.paciente_id=p.idPaciente;
end//
delimiter ;

delimiter //
create procedure cargarRecetasPorNombre(in nombre varchar(30))
begin
select c.fecha,f.nombre,r.dosis,r.preescripcion from receta r,cita c,farmaco f,paciente p
where (p.nombre like nombre or p.apellido = nombre)
and r.cita_id = c.idCita and r.farmaco_id = f.idFarmaco and p.idPaciente = r.paciente_id;
end//
delimiter ;

delimiter //
create procedure cargarRecetasPorCedula(in cedula varchar(30))
begin
select c.fecha,f.nombre,r.dosis,r.preescripcion from receta r,cita c,farmaco f 
where r.paciente_id = cedula and r.cita_id = c.idCita and r.farmaco_id = f.idFarmaco;
end//
delimiter ;

insert into receta values(receta.idReceta,4,'antes del almuerzo',0911111111,4,2);

delimiter //
create procedure cargarFarmacos()
begin
select idFarmaco,nombre from farmaco order by nombre;
end//
delimiter ;
delimiter //
create procedure masrecetados()
begin
select f.nombre,count(r.farmaco_id) as numveces from farmaco f, receta r
where f.idFarmaco = r.farmaco_id group by f.nombre order by numveces desc limit 10 ;
end//
delimiter ;

ALTER TABLE t ENGINE = INNODB;
ALTER TABLE t ENGINE = MyISAM ;

delimiter //
create trigger vecesFarmaco
after insert on receta
for each row
begin
#if(new.farmaco_id=farmaco.idFarmaco)then
update farmaco
set farmaco.numVecesUsado = farmaco.numVecesUsado + 1 where new.farmaco_id = farmaco.idFarmaco;
#end if;
END;//
delimiter ;

delimiter //
create procedure recetaActual()
begin
select max(idReceta) as idreceta from receta;
end//
delimiter ;CREATE table Farmaco(
 idFarmaco int primary key,
 nombre varchar(50),
 notas varchar(150),
 dosis varchar(45),
 preescripcion varchar(45),
 receta_id int,
);
 CREATE table Receta(
    idReceta int primary key,
    paciente_id int,
);