zMcRYmxr

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