CREATE PROCEDURE getPublisherReport (IN `start_date` DATE, IN `end_date` DATE, IN `pager_offset` INT, IN `pager_limit` INT, IN `sort` VARCHAR(255), IN `direction` CHAR(4))
  BEGIN
    SET @START_DATE = `start_date`;
    SET @END_DATE = `end_date`;
    SET @OFFSET = `pager_offset`;
    SET @LIMIT = `pager_limit`;

    SET @sql = CONCAT("
      SELECT
        publisher_name,
        publisher_id,
        SUM(impressions) as impressions,
        SUM(page_views) as page_views,
        SUM(pvs_ads) as pvs_ads,
        SUM(clicks) as clicks,
        SUM(clicks)/SUM(page_views)*100 as ctr,
        SUM(revenue)/100/SUM(clicks) as ecpc,
        SUM(revenue)/100/SUM(pvs_ads)*1000 as rpm,
        SUM(revenue)/100 as revenue,
        SUM(cost)/100 as cost,
        (SUM(revenue)-SUM(cost))/100 as profit
      FROM publisher_reports
      WHERE date >= ? AND date <= ?
      GROUP BY publisher_id
      ORDER BY ", sort," ",direction,"
      LIMIT ?, ?");

    PREPARE STMT FROM @sql;
    EXECUTE STMT USING @START_DATE, @END_DATE, @OFFSET, @LIMIT;
  END//