-- Function: public.calculateexperiencenew(integer)

-- DROP FUNCTION public.calculateexperiencenew(integer);

CREATE OR REPLACE FUNCTION public.calculateexperiencenew(integer)
  RETURNS integer AS
$BODY$ 
Declare 
total numeric(14,4) := 0;   
r_Experience RECORD;  
        starttime date;
        endtime date;

        mindate date;
        maxdate date;
	olddate date;

        subtractvalue INT4 := 0;
        diff INT4 := 0;
        tevta_resume INT4 := 0;
	
 
Begin 

select into mindate start_date from experience where resume_id = $1 order by start_date limit 1;
select into maxdate end_date from experience where resume_id = $1 order by end_date desc limit 1;

if maxdate is null then    
     select into diff current_date - mindate;
     total = diff;      
else
     select into diff maxdate - mindate;
     total = diff;
end if;

for r_Experience IN select start_date,end_date from experience where resume_id = $1 order by start_date loop 

endtime = r_Experience.end_date;   
starttime  = r_Experience.start_date;
tevta_resume = $1;

if endtime is null then 
	endtime = current_date;
end if;

if olddate is null then 
	olddate = endtime;
end if;

if (starttime > olddate) then
	select into diff starttime - olddate;
	subtractvalue = subtractvalue + diff;
end if;
olddate = endtime;
END LOOP;
        total = total - subtractvalue;
        total = total / 365;
        update resume set experience = total where resume_id = $1;
        return tevta_resume; 
End;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.calculateexperiencenew(integer)
  OWNER TO jawad;