DROP FUNCTION get_offers_for_domai n_v2 domainId INTEGER DROP TYPE IF

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
DROP FUNCTION get_offers_for_domain_v2(domainId INTEGER);
DROP TYPE IF EXISTS holder;
CREATE TYPE holder AS (creative_id VARCHAR, title VARCHAR, description VARCHAR, image VARCHAR, tracking_link VARCHAR, frequency INTEGER, var_ecpm DOUBLE PRECISION);
CREATE OR REPLACE FUNCTION get_offers_for_domain_v2(domainId INTEGER)
RETURNS SETOF holder
AS $$
DECLARE
r holder%rowtype;
var_creative record;
var_ecpm DOUBLE PRECISION DEFAULT 0.0;
BEGIN
FOR var_creative IN (
SELECT
*
FROM
push_creative
LEFT OUTER JOIN
creative_calculations
ON
push_creative.id=creative_calculations.creative_id
WHERE
push_creative.geo_id = (SELECT geo_id FROM push_domain WHERE id=domainId)
)
LOOP
<< outerblock >>
DECLARE
sends integer := 0;
revenue numeric := 0.0;
ecpm DOUBLE PRECISION := 0.0;
var_domain_stat record;
BEGIN
FOR var_domain_stat IN (
SELECT stats.sends, stats.revenue FROM stats WHERE creative_id=var_creative.id and domain_id=domainId ORDER BY stats.cre_date DESC
)
LOOP
sends := sends + var_domain_stat.sends;
revenue := revenue + var_domain_stat.revenue;
IF sends > 0 THEN
ecpm := (revenue/sends)*1000;
END IF;
IF sends > var_creative.learning_sends_per_domain THEN
EXIT;
END IF;
END LOOP;
var_ecpm := ecpm;
IF sends < var_creative.learning_sends_per_domain THEN
DECLARE
sends integer := outerblock.sends;
revenue numeric := outerblock.revenue;
ecpm DOUBLE PRECISION := 0.0;
var_global_stat record;
BEGIN
FOR var_global_stat IN (
SELECT stats.sends, stats.revenue FROM stats WHERE creative_id=var_creative.id and domain_id != domainId ORDER BY stats.cre_date DESC
)
LOOP
sends := sends + var_global_stat.sends;
revenue := revenue + var_global_stat.revenue;
RAISE NOTICE 'Quantity here is %', sends;
IF sends > 0 THEN
ecpm := (revenue/sends)*1000;
END IF;
IF sends > var_creative.learning_sends_per_geo THEN
EXIT;
END IF;
END LOOP;
var_ecpm := ecpm;
IF sends < var_creative.learning_sends_per_geo THEN
var_ecpm := 0;
ELSE
var_ecpm := (revenue/sends)*1000;
END IF;
END;
END IF;
END;
r.creative_id := var_creative.id;
r.title := var_creative.title;
r.description := var_creative.description;
r.image := var_creative.image;
r.tracking_link := var_creative.tracking_link;
r.frequency := var_creative.frequency;
r.var_ecpm := var_ecpm;
RETURN NEXT r;
END LOOP;
END;
$$
LANGUAGE plpgsql;
--RAISE NOTICE 'Quantity here is %', sends;
/*select * FROM get_offers_for_domain_v1(1);*/
select * FROM get_offers_for_domain_v2(1)