package geospatial;

import org.voltdb.SQLStmt;
import org.voltdb.VoltProcedure;
import org.voltdb.VoltTable;

/**
 * Get statistics on ad requests for the most recent time period
 * in seconds, specified by the caller.
 *
 * These SQL statements make use of a materialized view so they
 * can execute data quickly.
 */
public class GetStats extends VoltProcedure {

    /*
     * Find out the ratio of successful bids to unsuccessful ones.
     */
    final SQLStmt selectHitsAndNonHits = new SQLStmt(
            "select "
            + "  case when advertiser_id is not null "
            + "    then 1 else 0 end as hit_or_no_hit, "
            + "  sum(request_count) "
            + "from requests_by_second_by_advertiser "
            + "where ts_second > dateadd(second, ?, current_timestamp) "
            + "group by hit_or_no_hit "
            + "order by hit_or_no_hit");

    /*
     * Find the top 5 advertisers of the past <n> seconds, ordered
     * by the sum of all the winning bids.
     */
    final SQLStmt selectRecentAdvertisers = new SQLStmt(
            "select "
            + "  adv.name as advertiser, "
            + "  sum(vw.sum_of_ad_revenue) total_revenue, "
            + "  sum(vw.request_count) as num_requests "
            + "from requests_by_second_by_advertiser as vw "
            + "inner join advertisers as adv "
            + "on vw.advertiser_id = adv.id "
            + "where ts_second > dateadd(second, ?, current_timestamp) "
            + "group by adv.name "
            + "order by total_revenue desc "
            + "limit 5");

    public VoltTable[] run(long numSeconds) {
        voltQueueSQL(selectHitsAndNonHits, -numSeconds);
        voltQueueSQL(selectRecentAdvertisers, -numSeconds);
        return voltExecuteSQL(true);
    }
}

@@ -1,8 +1,1141 @@
+/* This file is part of VoltDB.%0A * Copyright (C) 2008-2015 VoltDB Inc.%0A *%0A * Permission is hereby granted, free of charge, to any person obtaining%0A * a copy of this software and associated documentation files (the%0A * %22Software%22), to deal in the Software without restriction, including%0A * without limitation the rights to use, copy, modify, merge, publish,%0A * distribute, sublicense, and/or sell copies of the Software, and to%0A * permit persons to whom the Software is furnished to do so, subject to%0A * the following conditions:%0A *%0A * The above copyright notice and this permission notice shall be%0A * included in all copies or substantial portions of the Software.%0A *%0A * THE SOFTWARE IS PROVIDED %22AS IS%22, WITHOUT WARRANTY OF ANY KIND,%0A * EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF%0A * MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.%0A * IN NO EVENT SHALL THE AUTHORS BE LIABLE FOR ANY CLAIM, DAMAGES OR%0A * OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE,%0A * ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR%0A * OTHER DEALINGS IN THE SOFTWARE.%0A */%0A%0A
 package 

Address licensecheck issue