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)