1 package org.unicode.cldr.web;
2 
3 import java.util.Comparator;
4 import java.util.Map;
5 import java.util.Set;
6 import java.util.TreeMap;
7 import java.util.TreeSet;
8 
9 public class StatisticsUtils {
10     private static final int FEW_MINUTES = 10 * 60 * 1000;
11 
12     private static class di {
13         public String s;
14         public int v;
15         public int d;
16 
17         di(String s) {
18             this.s = s;
19             this.v = 0;
20             this.d = 0;
21         }
22     }
23 
24     public static final int NO_LIMIT = -1;
getSortMode(WebContext ctx, String prefix)25 
26     public static final String QUERY_ALL_VOTES = "select  locale,count(*) as count from " + DBUtils.Table.VOTE_VALUE + "  where submitter is not null " + ""
27         + " group by locale ";
28 
29     public static final String QUERY_NEW_VOTES = "select  locale,count(*) as count from " + DBUtils.Table.VOTE_VALUE + " as new_votes  where submitter is not null " +
30         " AND " + StatisticsUtils.getExcludeOldVotesSql()
getSortMode(WebContext ctx, DataSection section)31         + " group by locale ";
32 
33     public static String[][] calcSubmits(String[][] v, String[][] d) {
34         return calcSubmits(v, d, NO_LIMIT);
getInstance(String mode)35     }
36 
37     public static String[][] calcSubmits(String[][] v, String[][] d, int limit) {
38         Map<String, di> all = new TreeMap<>();
39 
40         for (int i = 0; i < v.length; i++) {
41             di ent = all.get(v[i][0]);
42             if (ent == null) {
43                 all.put(v[i][0], (ent = new di(v[i][0])));
44             }
45             ent.v = Integer.parseInt(v[i][1]);
46         }
47         for (int i = 0; i < d.length; i++) {
48             di ent = all.get(d[i][0]);
49             if (ent == null) {
50                 all.put(d[i][0], (ent = new di(d[i][0])));
51             }
52             ent.d = Integer.parseInt(d[i][1]);
53         }
54         Set<di> asSet = new TreeSet<>(new Comparator<di>() {
55 
56             @Override
57             public int compare(di arg0, di arg1) {
58                 int rc = arg1.d - arg0.d;
59                 if (rc == 0) {
60                     rc = arg0.s.compareTo(arg1.s);
61                 }
62                 return rc;
63             }
64         });
65         asSet.addAll(all.values());
66 
67         int newSize = asSet.size();
68         if (limit != NO_LIMIT && limit < newSize) {
69             newSize = limit;
70         }
71 
72         String[][] ret = new String[newSize][];
73         int j = 0;
74         for (di dd : asSet) {
75             if (j == newSize) {
76                 return ret;
77             }
78             ret[j] = new String[3];
79             ret[j][0] = dd.s;
80             ret[j][1] = Integer.toString(dd.d);
81             ret[j][2] = Integer.toString(dd.v);
82             j++;
83         }
84         return ret;
85     }
86 
87     /**
88      * Total items submitted. Updated every few minutes
89      * @return
90      */
getName()91     public static int getTotalItems() {
92         final String queryName = "total_items";
93         final String querySql = "select count(*) from " + DBUtils.Table.VOTE_VALUE + " where submitter is not null";
94         return getCachedQuery(queryName, querySql);
95     }
96 
97     /**
98      * Total items submitted. Updated every few minutes
memberships()99      * @return
100      */
101     public static int getTotalNewItems() {
102         final String queryName = "total_new_items";
103         final String querySql = "select count(*) from " + DBUtils.Table.VOTE_VALUE + " as new_votes where new_votes.submitter is not null  and "
104             + getExcludeOldVotesSql();
105         return getCachedQuery(queryName, querySql);
106     }
getDisplayName(DataRow p)107 
108     public static String getExcludeOldVotesSql() {
109         return " not exists ( select * from " + DBUtils.Table.VOTE_VALUE.forVersion(SurveyMain.getLastVoteVersion(), false) + " as old_votes "
110             + "where new_votes.locale=old_votes.locale and new_votes.xpath=old_votes.xpath and "
111             + "new_votes.submitter=old_votes.submitter and new_votes.value=old_votes.value) ";
112     }
113 
114     /**
115      * Total submitters. Updated every few minutes
116      * @return
117      */
118     public static int getTotalSubmitters() {
119         final String queryName = "total_submitters";
120         final String querySql = "select count(distinct submitter) from " + DBUtils.Table.VOTE_VALUE + " ";
reserveForSort()121         return getCachedQuery(queryName, querySql);
122     }
123 
124     /**
125      * @param queryName
126      * @param querySql
127      * @return
128      */
compareMembers(DataRow p1, DataRow p2, Membership[] memberships, int ourKey)129     public static int getCachedQuery(final String queryName, final String querySql) {
130         if (!SurveyMain.isSetup || SurveyMain.isBusted()) {
131             return -2;
132         }
133         try {
134             return DBUtils.getFirstInt(DBUtils.queryToCachedJSON(queryName, FEW_MINUTES,
135                 querySql));
136         } catch (Throwable t) {
137             return -1;
138         }
139     }
140 }
141