Untitled

-- Patient table created
create table patient(
patientId serial not null primary key,
firstName varchar(50) not null ,
lastName varchar(50) not null ,
dob date,
isActive bool not null,
createdDate date, 
lastModifiedDate date
);

ALTER TABLE patient ALTER COLUMN isActive SET default true;

insert
	into
	patient (firstName,
	lastName,
	dob,
	isActive,
	createdDate,
	lastModifiedDate)
values ('ram',
'patel',
'05-6-2001',
true,
'01-06-2002',
'01-10-2002');

select
	*
from
	Patient;

-- AllergyMaster table created
create table allergyMaster(
allergyMasterId serial not null primary key,
code varchar not null unique,
name varchar not null,
createdDate date,
lastModifiedDate date
);

create table names(
name varchar unique,
id serial,
phonenumber int);

select * from allergymaster;


select * from allergymaster;

insert into allergymaster(Code,name,createdDate,lastModifiedDate)
values('44bb','polen Allergy','02-01-1965','10-06-1970');


-- PatientAllergy Table created
create table patientAllergy(
patientAllergyId serial not null primary key,
patientId int not null,
allergyMasterId int not null,
note varchar,
createdDate date,
lastModifiedDate date,
foreign key(patientId) references patient(patientId) on delete cascade,
foreign key(allergyMasterId) references allergyMaster(allergyMasterId) on delete cascade
);

alter table allergyMaster 
rename column createDate to createddate;

ALTER TABLE allergyMaster add PRIMARY KEY (patientID & );

select * from patientAllergy;

insert into patientAllergy(patientId,allergyMasterId,note,createdDate,lastModifiedDate)
values (4,2,'normal','01-02-2000','21-02-2001')




-- Create Patient.
create or replace function createPatient(
firstname  varchar(50),
lastname  varchar(50),
dob  date,
createddate date
)
returns table(patientid int)
as
$$

begin
	return query 
	insert into patient as p (firstName,	lastName,	dob, createdDate, lastModifiedDate)
    values (firstname,lastname,dob,createddate,createddate)
	returning p.patientid ;
	
end;
$$
language plpgsql;

select * from createPatient('ram','sidhhpura','05-02-2002','25-04-2009');



-- Create patient allergy
INSERT INTO patientAllergy (
    patientid,
    allergymasterid,
    note,
    createddate,
    lastmodifieddate 
)
VALUES
    (7,3,'serious','05-02-2002','05-02-2002'),
    (7,1,'serious','05-02-2002','05-02-2002');

  
-- Get Patient details by id
create or replace function getPatientById(pid int)
returns table(
patientid int,
firstName varchar(50),
lastName varchar(50),
dob date,
isActive bool,
createdData date, 
lastModifiedDate date
)
as
$$

begin
	return query 
	select p.patientid,p.firstName,p.lastName,p.dob,p.isActive,p.createddate ,p.lastmodifieddate  
	from patient as p
	where p.patientid = pid;
	
end;
$$
language plpgsql;

select * from getPatientById(3);


-- Get Patient with allergy
create or replace function getPatientByIdWithAllergy(pid int)
returns table(
patientid int,
firstName varchar(50),
lastName varchar(50),
dob date,
patientcreateddate date,
patientlastmodifieddate date,
allergymasterid int,
name varchar,
code varchar,
note varchar,
allergycreateddate date,
allergylastmodifieddate date,
isActive bool
)
as
$$

begin
	return query 
	select p.patientid,p.firstName,p.lastName,p.dob,p.createddate ,p.lastmodifieddate ,p2.allergymasterid ,a.name ,a.code,p2.note,p2.createddate ,p2.lastmodifieddate  ,p.isActive
	from patient as p
	left join patientallergy p2 on p.patientid = p2.patientid 
	left join allergymaster a on p2.allergymasterid =a.allergymasterid 
	where p.patientid = pid;
	
end;
$$
language plpgsql;

	select p.patientid,p.firstName,p.lastName,p.dob,p.isActive,p2.allergymasterid ,a.name ,a.code,p2.note 
	from patient as p
	inner join patientallergy p2 on p.patientid = p2.patientid 
	inner join allergymaster a on p2.allergymasterid =a.allergymasterid 
	where p.patientid = 3;

select * from getPatientByIdWithAllergy(3);


-- Get Patient list
create or replace function getPatientList(
	pagenumber int default 1,
	pagesize int default 5,
	sortby varchar default null
	)
	
	returns table(
	patientid int,
	firstName varchar(50),
	lastName varchar(50),
	dob date,
	patientcreateddate date,
	patientlastmodifieddate date,
	allergymasterid int,
	name varchar,
	code varchar,
	note varchar,
	allergycreateddate date,
	allergylastmodifieddate date,
	isActive bool
	) 
	as $$
	
	declare query1 text;
			query2 text;
			query3 text;
	
	begin
		query3='drop table patientdata;';
		execute query3;
	
		query1='
		create table patientdata as 
		select * from patient p 
		limit $2 offset ($1-1)*$2;';
		execute query1 using pagenumber, pagesize, sortby;
	
		query2='select p.patientid,p.firstName,p.lastName,p.dob,p.createddate ,p.lastmodifieddate ,p2.allergymasterid ,a.name ,a.code,p2.note,p2.createddate ,p2.lastmodifieddate  ,p.isActive
		from patientdata as p
		left join patientallergy p2 on p.patientid = p2.patientid 
		left join allergymaster a on p2.allergymasterid =a.allergymasterid  '
		||(case when sortby notnull then ' order by '|| $3 else ' order by p.patient_id ' end);
 		
		
	
	return QUERY execute query2 using pagenumber, pagesize, sortby ;
	end $$
language plpgsql;


-- to get sort data
select * from getPatientList(sortby=>'patientid',pagenumber=>1,pagesize=>1);




create table patientdata as 
select * from patient p 
limit 5 offset (1-1)*5

drop table patientdata ;

select p.patientid,p.firstName,p.lastName,p.dob,p.createddate ,p.lastmodifieddate ,p2.allergymasterid ,a.name ,a.code,p2.note,p2.createddate ,p2.lastmodifieddate  ,p.isActive
		from patientdata as p
		left join patientallergy p2 on p.patientid = p2.patientid 
		left join allergymaster a on p2.allergymasterid =a.allergymasterid
        
        
-- update patient details.
create or replace function updatePatient(
	patientid int ,
	firstname varchar,
	lastname varchar,
    dob date
	)
	
	returns table(
	pid int
	) 
	as $$
	
	declare query text;
	
	begin
		query='update patient
                set firstname=$2, lastname=$3, dob=$4, lastmodifieddate=current_date
                where patientid=$1;
                returning patientid';
 			
	return QUERY execute query using patientid,firstname,lastname,to_date(dob,'DD-MM-YYYY') ;
	end $$
language plpgsql;

-- create patient allergy.
create or replace function insertPatientAllergy(
	patientid int ,
	allergymasterid int
	)
	
	returns table(
	pid int
	) 
	as $$
	
	declare query text;
	
	begin
		query='insert into patientallergy (patientid,allergymasterid,createddate,lastmodifieddate) 
                values ($1,$2,current_date,current_date)
                returning patientid;';
 			
	return QUERY execute query using patientid,allergymasterid ;
	end $$
language plpgsql;

--delete patient allergy
create or replace function deletePatientAllergy(
	patientid int ,
	allergymasterid int
	)
	
	returns table(
	pid int
	) 
	as $$
	
	declare query text;
	
	begin
		query='delete from patientallergy
        where patientid=$1 and allergimasterid=$2
        returning patientid;';
 			
	return QUERY execute query using patientid,allergymasterid ;
	end $$
language plpgsql;

--deactivate patient
create or replace function deactivate(
	patientid int 
)
	returns table(
	pid int
	) 
	as $$
	
	declare query text;
	
	begin
		query='update patient
        set isactive=false
        where patientid=$1
        returning patientid;';
 			
	return QUERY execute query using patientid ;
	end $$
language plpgsql;

insert into patientallergy (patientid,allergymasterid,createddate,lastmodifieddate) 
values (4,2,current_date,lastmodifieddate=current_date);

select * from patientallergy;