Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION get_plan_score(faculty_id integer, from_date date, until_date date)
- RETURNS TABLE(
- fdate date ,
- udate date ,
- score int ) AS
- $BODY$
- DECLARE res integer;
- DECLARE unit integer;
- DECLARE StartDate DATE;
- DECLARE EndofYear DATE;
- DECLARE EndDate DATE;
- BEGIN
- StartDate = from_date ::DATE ;
- CREATE TEMP TABLE IF NOT EXISTS temp_plan_score_report(user_id integer,fdate date ,udate date ,score int) ON COMMIT DROP;
- WHILE (StartDate <= until_date) LOOP
- EndofYear = date_trunc('year', StartDate)+ interval '1 year'- interval '1 day';
- IF EndofYear > until_date THEN
- EndDate = until_date;
- ELSE
- EndDate = EndofYear;
- END IF;
- SELECT SUM(general_settings_granttypeamounts.amount) INTO unit
- FROM general_settings_granttypeamounts
- WHERE general_settings_granttypeamounts.grant_type= 34;
- SELECT SUM(grant_granttransaction.amount) INTO res
- FROM grant_granttransaction
- WHERE grant_granttransaction.date_created >= date(StartDate) AND
- grant_granttransaction.date_created <= date(EndOfYear) AND grant_granttransaction.faculty_info_id = $1
- AND grant_granttransaction.type = 17
- GROUP BY grant_granttransaction.faculty_info_id;
- IF res IS NULL THEN
- INSERT INTO temp_plan_score_report(user_id,fdate,udate,score)
- VALUES (faculty_id,StartDate,EndDate,0);
- ELSE
- INSERT INTO temp_plan_score_report(user_id,fdate,udate,score)
- VALUES (faculty_id,StartDate,EndDate, res/unit::float);
- END IF;
- StartDate = EndDate+1;
- END LOOP;
- RETURN QUERY SELECT * FROM temp_plan_score_report where temp_plan_score_report.faculty_info_id=user_id;
- END;$BODY$
- -------------------------------------------------------------
- LANGUAGE plpgsql VOLATILE
- COST 100;
- ALTER FUNCTION get_plan_score(integer, date, date)
- OWNER TO saed;
- COMMENT ON FUNCTION get_plan_score(integer, date, date) IS 'calculate score for practical plans after 92 based on their granttransaction record';
- -- SELECT * FROM get_plan_score(38585,'2013-02-09':: date ,'2015-05-09' :: date);
- -- SELECT * FROM get_plan_score(38585,'2000-02-09':: date ,'2005-05-09' :: date);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement