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,
createdData date, 
lastModifiedDate date
);

ALTER TABLE patient ALTER COLUMN isActive SET default true;

insert
	into
	patient (firstName,
	lastName,
	dob,
	isActive,
	createdDate,
	lastModifiedDate)
values ('Rithin',
'Prem',
'9-9-2000',
true,
'8-03-2001',
'8-12-2001');

select
	*
from
	Patient;


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

insert into allergymaster(Code,name,createdDate,lastModifiedDate) values('11BB','Pet Allergy','22-01-1952','10-06-1956');


-- PatientAllergy Table created
create table patientAllergy(
patientAllergyId serial not null primary key,
patientId int not null,
allergyMasterId int not null,
note varchar,
createDate 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;

insert into patientAllergy(patientId,allergyMasterId,note,createdDate,lastModifiedDate)
values (3,1,'serious','01-03-2002','21-02-2003')




-- 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
	left join patientallergy p2 on p.patientid = p2.patientid 
	left 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 str text;
	
	begin
		str='
		create table patientdata as 
		select * from patient p 
		limit 5 offset (1-1)*5;
		'||' \n '||'
		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)
		||(' limit $2 offset ($1-1)*$2; ')||' \n '
 
		||'drop table patientdata;';
	return QUERY execute str using pagenumber, pagesize, sortby ;
	end $$
language plpgsql;


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




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