usYdm5W7

1
2
3CREATE OR REPLACE FUNCTION  mlm.add_turn_over( user_id integer,  month integer,  year integer,  sum money)
4RETURNS void as
5$$
6DECLARE table_name CHARACTER VARYING = 'mlm.monthly_turn_over_' || month || '_' || year;
7BEGIN
8execute(
9	'CREATE TABLE IF NOT EXISTS ' || table_name ||	
10'(
11  user_id integer NOT NULL,
12  personal_turn_over money,
13  group_turn_over money,
14  current_level_id integer,
15  last_pay_date timestamp with time zone,
16  CONSTRAINT pkey_monthlyturnover' || month || '_' || year ||' PRIMARY KEY (user_id)
17)
18	INSERT INTO '|| table_name ||'(
19            user_id, personal_turn_over)
20    VALUES ('|| user_id ||', '|| sum ||');
21
22	UPDATE '|| table_name || ' SET personal_turn_over = personal_turn_over + '|| sum || ' 
23
24	WITH RECURSIVE r AS (	        
25            SELECT user_id, head_user_id, current_level_id, earned_points
26	        FROM mlm.hierarchy WHERE user_id = @userId
27	        UNION ALL 
28	        SELECT h.user_id, h.head_user_id, h.current_level_id, h.earned_points
29	        FROM  mlm.hierarchy  h
30	        INNER JOIN r on  r.head_user_id = h.user_id)
31            UPDATE   FROM r
32
33');
34
35END;
36
37$$ LANGUAGE plpgsql;
38
39
40  SELECT mlm.add_turn_over(
41    1,
42    1,
43    1,
44    cast(1.0 as money) 
45);