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

//
// Returns the heat map data (winning contestant by state) for display on nthe Live Statistics dashboard.
//

package voter;

import java.util.ArrayList;

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

/**
 * This procedure returns two VoltTables as results.  The first is a list of states,
 * the contestant numbers of the winner in that state and the total number of votes for that
 * contestant in that state.  The second is a table of contestants and all votes
 * for that contestant globally.
 */
public class GetStateHeatmap extends VoltProcedure {

    public final SQLStmt stateHeatMap = new SQLStmt(
                                                    "SELECT STATE, CONTESTANT_NUMBER, NUM_VOTES "
                                                  + "FROM ( SELECT STATE, NUM_VOTES, CONTESTANT_NUMBER, "
                                                  + "       RANK() OVER ( PARTITION BY STATE ORDER BY NUM_VOTES DESC ) VRANK "
                                                  + "       FROM V_VOTES_BY_CONTESTANT_NUMBER_STATE ) AS SUB "
                                                  + "WHERE SUB.VRANK = 1; ");
    public final SQLStmt contestantTotals = new SQLStmt("SELECT * from V_VOTES_BY_CONTESTANT_NUMBER;");

    public VoltTable[] run() {
        voltQueueSQL(stateHeatMap);
        voltQueueSQL(contestantTotals);
        VoltTable[] result = voltExecuteSQL();
        return result;
    }
}

@@ -1894,52 +1894,323 @@
    %22
+  
 SELECT 
-STATE, CONTESTANT_NUMBER, NUM_VOTES %22
+state%22                                                     +%0A                                                    %22       , contestant_number%22                                         +%0A                                                    %22       , num_votes%22                                                 +
 %0A   
@@ -2256,19 +2256,23 @@
         
-+
+ 
  %22
+    
 FROM ( S
@@ -2281,46 +2281,305 @@
 ECT 
-STATE, NUM_VOTES, CONTESTANT_NUMBER, %22
+state%22                                            +%0A                                                    %22                , contestant_number%22                                +%0A                                                    %22                , num_votes%22                                        +
 %0A   
@@ -2617,33 +2617,33 @@
                 
-+
+ 
  %22       RANK() 
@@ -2635,16 +2635,27 @@
 %22       
+  
+       , 
 RANK() O
@@ -2674,50 +2674,158 @@
 ION 
-BY STATE ORDER BY NUM_VOTES DESC ) VRANK %22
+by state %22                +%0A                                                    %22                                ORDER BY num_votes DESC ) AS vrank%22 +
 %0A   
@@ -2871,19 +2871,25 @@
         
-+
+ 
  %22
+      
        F
@@ -2896,53 +2896,59 @@
 ROM 
-V_VOTES_BY_CONTESTANT_NUMBER_STATE ) AS SUB %22
+v_votes_by_contestant_number_state ) AS sub%22      +
 %0A   
@@ -2998,32 +2998,34 @@
     
-+
+ 
  %22
+   
 WHERE 
-SUB.VRANK
+sub.vrank
  = 1;
- 
 %22);%0A

Reformat the SQL query for stateHeatMap in GetStateHeatMap.