Public feed
#603327
------------------------------------------------------------------------------- fn_available_campaigns(site_id, ad_type, action_type, address)CREATE OR REPLACE FUNCTION fn_available_campaigns(INTEGER, TEXT, TEXT, INET)RETURNS SETOF campaigns AS $PROC$ SELECT campaigns.* FROM campaigns INNER JOIN subscriptions ON subscriptions.campaign_id = campaigns.id AND subscriptions.site_id = $1 WHERE campaigns.ad_type = $2 -- exclude banned campaigns EXCEPT SELECT campaigns.* FROM campaigns INNER JOIN actions ON campaigns.id = actions.campaign_id AND campaigns.pay_per = $3 AND actions.compensated AND actions.address = $4 AND now() - actions.created_at < campaigns.reload_ban;$PROC$ LANGUAGE SQL;------------------------------------------------------------------------------- fn_action()CREATE OR REPLACE FUNCTION fn_action( v_action_id IN INTEGER, v_site_id IN INTEGER, v_action IN TEXT, -- (view, click, lead, sale) v_ad_type IN TEXT, -- (banner, textlink, ...) v_format_id IN INTEGER, -- may be NULL v_address IN INET) RETURNS TEXT AS $PROC$DECLARE a_compensated BOOLEAN; a_pay_per TEXT; a_campaign_id INTEGER; a_charge NUMERIC(12,6); a_return TEXT;... SELECT campaigns.id INTO a_campaign_id FROM ( SELECT campaigns.id FROM fn_available_campaigns(v_site_id, v_ad_type, v_action, v_address) AS campaigns -- include only campaigns with banners of the given format INTERSECT SELECT DISTINCT ON(banners.campaign_id) campaigns.id FROM campaigns INNER JOIN banners ON banners.campaign_id = campaigns.id AND banners.format_id = v_format_id ) AS campaigns ORDER BY random() LIMIT 1;