/*Procedure to handle reports
*
* Report_id is retrieved from tblreports in order to find out what report you would like to process
* If any additional reports are needed then an else if and end if statement is required, following the way they are handled
* in the existing reports
*/

create or replace function reports (inreport_id integer,inadviser_id integer,inprovider_id integer,inintroducer_id integer,
inplangroup_id integer,inplantype_id integer,indatespecific_start date,indatespecific_end date,inchild24 date,inchild26 date) 
returns setof record as' 

declare myrec record;

begin 

--Pipeline and Commission report
if inreport_id=18 then 
select into myrec 
tblemployee.employee_first_name,tblemployee.employee_surname,tblnewbusiness.newbusiness_date_issued,
tblclients.client_first_name,tblclients.client_middle_names,tblclients.client_surname,tblplantypes.plantype_group,
tblplangroups.plangroups_group,tblproviders.provider_company, tblnewbusiness.newbusiness_policy_number, 
tblnewbusiness.newbusiness_sum_assured,tblnewbusiness.newbusiness_benefit, tblnewbusiness.newbusiness_premium, 
tblnewbusiness.newbusiness_brokerage,tblnewbusiness.newbusiness_comments 
from tblemployee,tblclients,tblproviders,tblplantypes,tblplangroups,tblnewbusiness,tblintroducers,tblintroducer,
tbladvisersplit,tblclientlist 

where inadviser_id=tblemployee.employee_ref and tblemployee.employee_ref=tbladvisersplit.adviser_ref and 
tbladvisersplit.newbusiness_ref=tblnewbusiness.newbusiness_ref and tblplantypes.plantypes_ref=tblnewbusiness.plantype_ref
and tblproviders.provider_ref=tblnewbusiness.provider_ref and tblclientlist.newbusiness_ref=tblnewbusiness.newbusiness_ref 
and tblclients.client_ref=tblclientlist.client_ref 
and inprovider_id=tblproviders.provider_ref and inintroducer_id=tblintroducers.introducer_ref and 
tblintroducers.introducer_ref=tblintroducer.introducer_ref and tblintroducer.newbusiness_ref=tblnewbusiness.newbusiness_ref and 
(indatespecific_start<tblnewbusiness.newbusiness_date_received and indatespecific_end>tblnewbusiness.newbusiness_date_received) 
 OR (inchild24<tblnewbusiness.newbusiness_date_received and inchild26>tblnewbusiness.newbusiness_date_received);


return next myrec;	

--task by provider
else if inreport_id=23 then 
select into myrec 
tblclientlist.clientlist_primary,tblcancelled.reasoncancelled_ref,tblproviders.provider_company,
tblproviders.provider_phone_number,tblproviders.provider_e_mail,tblclients.client_title,tblclients.client_first_name,
tblclients.client_middle_names,tblclients.client_surname,tblclients.client_dob,tblemployee.employee_first_name,
tblemployee.employee_surname,tblnewbusiness.newbusiness_policy_number,tblnewbusiness.newbusiness_brokerage,
tblnewbusiness.newbusiness_comments,tblplangroups.plangroups_group, tblplantypes.plantype_group, 
tblactionsrequired.actionsrequired_date_required,tblactions.actions_action,tblemployee.employee_first_name,
tblemployee.employee_surname,tblproviders.provider_notes,tblnewbusiness.newbusiness_delete_ 

from tblemployee,tblclients,tblproviders,tblplantypes,tblplangroups,tblnewbusiness,tblintroducers,tblintroducer,
tbladvisersplit,tblclientlist 

where tblproviders.provider_ref=tblnewbusiness.provider_ref and inprovider_id=tblproviders.provider_ref and 
tblcancelled.reasoncancelled_ref=null and tblnewbusiness.newbusiness_delete_=false and tblnewbusiness.newbusiness_lead_=false 
and tblnewbusiness.plangroup_ref=tblplangroups.plangroups_ref and tblnewbusiness.plantype_ref=tblplantypes.plantypes_ref 
and tblintroducer.newbusiness_ref=tblnewbusiness.ref and tblintroducer.introducers_ref=tblintroducers.introducer_ref and 
tblintroducers.introducer_ref=inintroducer_id and inplantype_id=tblplantypes.plantypes_ref and 
(inplangroup_id=tblplangroups.plangroups_ref and indatespecific_end>tblnewbusiness.newbusiness_date_recieved)
 OR (inchild24<tblnewbusiness.newbusiness_date_recieved and inchild26>tblnewbusiness.newbusiness_date_recieved);

return next myrec;
 
--exceptions
else if inreport_id=25 then 
select into myrec 
tblnewbusiness.newbusiness_renewal,tblcancelled.reasoncancelled_ref,tbladvisersplit.adviser_ref,tblturnover.newbusiness_ref,
tblnewbusiness.newbusiness_date_issued,tblclientlist.clientlist_primary,tblclients.client_first_name,
tblclients.client_surname,tblplantypes.plantype_group,tblproviders.provider_company,tblnewbusiness.newbusiness_policy_number,
tblnewbusiness.newbusiness_sum_assured,tblnewbusiness.newbusiness_benefit,tblnewbusiness.newbusiness_premium,
tblnewbusiness.newbusiness_brokerage,tblnewbusiness.newbusiness_comments,tblemployee.employee_first_name,
tblemployee.employee_surname,tblnewbusiness.newbusiness_delete_ 

from tblemployee,tblclients,tblproviders,tblplantypes,tblplangroups,tblnewbusiness,tblintroducers,tblintroducer,
tbladvisersplit,tblclientlist 

where 

tblclients.client_ref=tblientlist.client_ref and tblclientlist.newbusiness_ref=tblnewbusiness.newbusiness_ref and 
tblnewbusiness.plantype_ref=tblplantype.plantypes_ref and tblnewbusiness.plangroup_ref=tblplangroups_ref and 
tblnewbusiness.provider_ref=tblproviders.provider_ref and tblnewbusiness.newbusiness_ref=tbladvisersplit.newbusiness_ref 
and tbladvisersplit.adviser_ref=tblemployee.employee_ref and inemployee_id=tblemployee.employee_ref and 
intprovider_id=tblproviders.provider_ref and tblintroducer.newbusiness_ref=tblnewbusiness.newbusiness_ref and 
tblintroducer.introducers_ref=tblintroducers.introducer_ref and tblintroducers.introducer_ref=inintroducer_id and 
inplantype_id=tblplantypes.plantypes_ref and inplangroup_id=tblplangroups.plangroups_ref and 
(indatespecific_end>tblnewbusiness.newbusiness_date_recieved) OR (inchild24<tblnewbusiness.newbusiness_date_recieved and 
inchild26>tblnewbusiness.newbusiness_date_recieved);

return next myrec; 

--lead actions required
else if inreport_id=13 then 
select into myrec 
tblclientlist.clientlist_primary,tblcancelled.reasoncancelled_ref,tblnewbusiness.newbusiness_ref, 
tblnewbusiness.newbusiness_lead_,tblproviders.provider_e_mail,tblclients.client_title,tblclients.client_first_name,
tblclients.client_middle_names,tblclients.client_surname,tblclients.client_dob,tblemployee.employee_first_name,
tblemployee.employee_surname,tblnewbusiness.newbusiness_policy_number,tblnewbusiness.newbusiness_brokerage,
tblnewbusiness.lead_comments,tblplangroups.plangroups_group,tblplantypes.plantype_group,
tblactionsrequired.actionsrequired_date_required, tblactions.actions_action,tblemployee.employee_first_name,
tblemployee.employee_surname,tblnewbusiness.newbusiness_delete_,tblnewbusiness.newbusiness_lead_

from tblemployee,tblclients,tblproviders,tblplantypes,tblplangroups,tblnewbusiness,tblintroducers,tblintroducer,
tbladvisersplit,tblclientlist 

where tblclients.client_ref=tblientlist.client_ref and tblclientlist.newbusiness_ref=tblnewbusiness.newbusiness_ref and 
tblnewbusiness.plantype_ref=tblplantype.plantypes_ref and tblnewbusiness.plangroup_ref=tblplangroups_ref and 
tblnewbusiness.provider_ref=tblproviders.provider_ref and tblnewbusiness.newbusiness_ref=tbladvisersplit.newbusiness_ref 
and tbladvisersplit.adviser_ref=tblemployee.employee_ref and inemployee_id=tblemployee.employee_ref and 
intprovider_id=tblproviders.provider_ref and tblintroducer.newbusiness_ref=tblnewbusiness.newbusiness_ref and 
tblintroducer.introducers_ref=tblintroducers.introducer_ref and tblintroducers.introducer_ref=inintroducer_id and 
inplantype_id=tblplantypes.plantypes_ref and inplangroup_id=tblplangroups.plangroups_ref and 
(indatespecific_end>tblnewbusiness.newbusiness_date_recieved) OR (inchild24<tblnewbusiness.newbusiness_date_recieved and 
inchild26>tblnewbusiness.newbusiness_date_recieved);


return next myrec; 

end if;
end if;
end if; 
end if; 

end; 
' language 'plpgsql';