flexo

------------------------------------------------------------------------------- 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;BEGIN  SELECT actions.campaign_id    INTO a_campaign_id    FROM actions    WHERE v_action_id = id;  IF FOUND THEN    -- verify given action    PERFORM * FROM actions      INNER JOIN campaigns        ON actions.campaign_id = campaigns.id       AND campaigns.ad_type = v_ad_type      WHERE v_action_id = actions.id        AND v_site_id = site_id;    IF NOT FOUND THEN      RAISE EXCEPTION 'bad action id';    END IF;  ELSE    IF v_ad_type = 'banner' THEN      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 subscriptions              ON subscriptions.campaign_id = campaigns.id             AND subscriptions.site_id = v_site_id            INNER JOIN banners              ON banners.campaign_id = campaigns.id             AND banners.format_id = v_format_id        ) AS campaigns        ORDER BY random() LIMIT 1;    ELSE      SELECT id        INTO a_campaign_id        FROM fn_available_campaigns(v_site_id, v_ad_type, v_action, v_address)        ORDER BY random() LIMIT 1;    END IF;    IF NOT FOUND THEN      -- XXX: Display our banners or error messages here      RAISE NOTICE 'found no campaign';      RETURN 'nothing';    END IF;    INSERT INTO actions (id, site_id, campaign_id, address, compensated)      VALUES (v_action_id, v_site_id, a_campaign_id, v_address, FALSE);  END IF;  -- fetch necessary data for compensation  IF a_pay_per = v_action AND NOT a_compensated THEN    PERFORM fn_compensate(v_site_id, a_campaign_id, a_charge);    UPDATE actions SET compensated = 'true' WHERE id = v_action_id;  END IF;  -- fetch url for given action / ad_type  IF v_action = 'view' THEN    IF v_ad_type = 'banner' THEN      SELECT banners.img_href        INTO a_return        FROM banners        WHERE banners.campaign_id = a_campaign_id          AND banners.format_id = v_format_id        ORDER BY random() LIMIT 1;      RETURN a_return;    ELSIF v_ad_type = 'text_link' THEN      SELECT text_links.content        INTO a_return        FROM text_links        WHERE text_links.campaign_id = a_campaign_id        ORDER BY random() LIMIT 1;    END IF;  ELSIF v_action = 'click' THEN    SELECT campaigns.href      INTO a_return      FROM campaigns     WHERE campaigns.id = a_campaign_id;  END IF;  RETURN a_return;END;$PROC$ LANGUAGE plpgsql;