Differential D23639 Diff 66771 community/donations/statistics/index.php

Changeset View

Standalone View

View Options

community/donations/statistics/index.php

1<?php 1​<?php
2 // Don't show the global donation square, this is private anyway 2​ // Don't show the global donation square, this is private anyway
3 $page_disablekdeevdonatebutton = true; 3​ $page_disablekdeevdonatebutton = true;
4 4​
5 $page_title="Donations Statistics"; 5​ $page_title="Donations Statistics";
6 include "header.inc"; 6​ require("config.php");
7 7​ require('../../../aether/config.php');
8 require("donations_auth.inc"); 8​ $pageConfig = array_merge($pageConfig, [
9​ 'title' => "Donations Statistics"
10​ ]);
11​ require('../../../aether/header.php');
12​ $site_root = "../../";
13​ echo '<main class="container">';
9 14​
10 function standard_deviation($aValues) 15​ function standard_deviation($aValues)
11 { 16​ {
17​ if (count($aValues) === 0) {
18​ return 0;
19​ }
12 $fMean = array_sum($aValues) / count($aValues); 20​ $fMean = array_sum($aValues) / count($aValues);
13 $fVariance = 0.0; 21​ $fVariance = 0.0;
14 foreach ($aValues as $i) 22​ foreach ($aValues as $i)
15 { 23​ {
16 $fVariance += pow($i - $fMean, 2); 24​ $fVariance += pow($i - $fMean, 2);
17 } 25​ }
18 $fVariance /= count($aValues); 26​ $fVariance /= count($aValues);
19 return (float) sqrt($fVariance); 27​ return (float) sqrt($fVariance);
20 } 28​ }
21 29​
22 function endsWith($haystack, $needle) 30​ function endsWith($haystack, $needle)
23 { 31​ {
24 return substr($haystack, -strlen($needle)) === $needle; 32​ return substr($haystack, -strlen($needle)) === $needle;
25 } 33​ }
26 34​
27 echo "<h2>Per Month</h2>"; 35​ echo "<h2>Per Month</h2>";
28 36​
37​ $dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
38​
29 for ($year = date("Y", time()); $year > 2010; $year--) { 39​ for ($year = date("Y", time()); $year > 2010; $year--) {
30 echo "<table border=1 width=\"600\">"; 40​ echo "<table border=1 width=\"600\">";
31 echo "<tr><th colspan=7>".$year."</th></tr>"; 41​ echo "<tr><th colspan=7>".$year."</th></tr>";
32 echo "<tr><th width=100>Month</th> 42​ echo "<tr><th width=100>Month</th>
33 <th width=100>Number of Donations</th> 43​ <th width=100>Number of Donations</th>
34 <th width=100>Total Amount</th> 44​ <th width=100>Total Amount</th>
35 <th width=100>Average Donation</th> 45​ <th width=100>Average Donation</th>
36 <th width=100>Donation StdDev</th> 46​ <th width=100>Donation StdDev</th>
37 <th width=100>Max Donation</th> 47​ <th width=100>Max Donation</th>
38 <th width=100>Min Donation</th> 48​ <th width=100>Min Donation</th>
39 </tr>"; 49​ </tr>";
40 $year_total = 0; 50​ $year_total = 0;
41 $year_number = 0; 51​ $year_number = 0;
42 $year_max = -1; 52​ $year_max = -1;
43 $year_min = INF; 53​ $year_min = INF;
44 $year_all_amounts = array(); 54​ $year_all_amounts = array();
45 55​
56​ $stmt = $dbConnection->prepare('SELECT amount, UNIX_TIMESTAMP(date) AS date_t FROM donations WHERE date >= :begin AND date <= :end');
57​ $stmtCount = $dbConnection->prepare('SELECT COUNT(*) FROM donations WHERE date >= :begin AND date <= :end');
58​
46 for ($month = 1; $month <= 12 ; $month++) { 59​ for ($month = 1; $month <= 12 ; $month++) {
47 $month < 10 ? $month_s = "0".$month : $month_s = $month; 60​ $month < 10 ? $month_s = "0".$month : $month_s = $month;
48 $query = "select amount,unix_timestamp(date) as date_t from donations "; 61​
49 $query .= "where date >= '".$year."-".$month_s."-01' and "; 62​ $stmtCount->execute([
50 $query .= "date <= '".$year."-".$month_s."-31 23:59:59'"; 63​ 'begin' => $year.'-'.$month_s.'-01',
51 $q = mysql_query($query,$sq); 64​ 'end' => $year.'-'.$month_s.'-31 23:59:59',
52 echo mysql_error(); 65​ ]);
53 if (mysql_num_rows($q) == 0) { 66​
67​ if ($stmtCount->fetchColumn() === 0) {
54 continue; 68​ continue;
55 } 69​ }
70​
71​ $stmt->execute([
72​ 'begin' => $year.'-'.$month_s.'-01',
73​ 'end' => $year.'-'.$month_s.'-31 23:59:59',
74​ ]);
56 $total = 0; 75​ $total = 0;
57 $number = 0; 76​ $number = 0;
58 $max = -1; 77​ $max = -1;
59 $min = INF; 78​ $min = INF;
60 $all_amounts = array(); 79​ $all_amounts = array();
61 80​
62 while ($row = mysql_fetch_array($q)) { 81​ while ($row = $stmt->fetch()) {
63 $amount = $row["amount"]; 82​ $amount = $row["amount"];
64 $total += $amount; 83​ $total += $amount;
65 $number += 1; 84​ $number += 1;
66 $max = max($amount, $max); 85​ $max = max($amount, $max);
67 $min = min($amount, $min); 86​ $min = min($amount, $min);
68 $all_amounts[] = $amount; 87​ $all_amounts[] = $amount;
69 88​
70 $year_total += $amount; 89​ $year_total += $amount;
71 $year_number += 1; 90​ $year_number += 1;
72 $year_max = max($amount, $year_max); 91​ $year_max = max($amount, $year_max);
73 $year_min = min($amount, $year_min); 92​ $year_min = min($amount, $year_min);
74 $year_all_amounts[] = $amount; 93​ $year_all_amounts[] = $amount;
75 } 94​ }
76 echo "<tr><td>".date("F", mktime(0,0,0,$month,1,$year))."</td> 95​ echo "<tr><td>".date("F", mktime(0,0,0,$month,1,$year))."</td>
77 <td align=right>".$number."</td> 96​ <td align=right>".$number."</td>
78 <td align=right>".$total."</td> 97​ <td align=right>".$total."</td>
79 <td align=right>".round($total/$number, 2)."</td> 98​ <td align=right>".($number !== 0 ? round($total/$number, 2) : 0)."</td>
80 <td align=right>".round(standard_deviation($all_amounts),2)."</td> 99​ <td align=right>".round(standard_deviation($all_amounts),2)."</td>
81 <td align=right>".$max."</td> 100​ <td align=right>".$max."</td>
82 <td align=right>".$min."</td> 101​ <td align=right>".$min."</td>
83 </tr>"; 102​ </tr>";
84 } 103​ }
85 echo "<tr><td>Year Total</td> 104​ echo "<tr><td>Year Total</td>
86 <td align=right>".$year_number."</td> 105​ <td align=right>".$year_number."</td>
87 <td align=right>".$year_total."</td> 106​ <td align=right>".$year_total."</td>
88 <td align=right>".round($year_total/$year_number, 2)."</td> 107​ <td align=right>".($year_number !== 0 ? round($year_total/$year_number, 2) : 0)."</td>
89 <td align=right>".round(standard_deviation($year_all_amounts),2)."</td> 108​ <td align=right>".round(standard_deviation($year_all_amounts),2)."</td>
90 <td align=right>".$year_max."</td> 109​ <td align=right>".$year_max."</td>
91 <td align=right>".$year_min."</td> 110​ <td align=right>".$year_min."</td>
92 </tr>"; 111​ </tr>";
93 echo "</table><br>"; 112​ echo "</table><br>";
94 } 113​ }
95 114​
96?> 115​?>
Show All 34 Lines142 <form action="index.php#perurl" method="post">
131 </select> 150​ </select>
132 <div id="fromto" style="display: none"> 151​ <div id="fromto" style="display: none">
133 From: <input id="from" type='date' name="from" size="10"/> 152​ From: <input id="from" type='date' name="from" size="10"/>
134 To: <input id="to" type='date' name="to" size="10"/> 153​ To: <input id="to" type='date' name="to" size="10"/>
135 </div> 154​ </div>
136 <input type="submit" value="Change"> 155​ <input type="submit" value="Change">
137 </form> 156​ </form>
138<?php 157​<?php
139 $query = "select DISTINCT donate_url from donations"; 158​ $query = "SELECT DISTINCT donate_url FROM donations";
140 $period = "today"; 159​ $period = "today";
141 if (isset($_POST["period"])) { 160​ if (isset($_POST["period"])) {
142 $period = $_POST["period"]; 161​ $period = $_POST["period"];
143 } 162​ }
163​ $q = NULL;
144 164​
145 if ($period == "today") { 165​ if ($period === "today") {
146 $query .= " where DATE(date) = CURDATE()"; 166​ $query .= ' WHERE DATE(date) = CURDATE()';
147 } else if ($period == "yesterday") { 167​ $q = $dbConnection->query($query);
148 $query .= " where DATE(date) = SUBDATE(CURDATE(), 1)"; 168​ } else if ($period === "yesterday") {
149 } else if ($period == "month") { 169​ $query .= ' WHERE DATE(date) = SUBDATE(CURDATE(), 1)';
150 $query .= " where YEAR(date) = YEAR(NOW()) and MONTH(date) = MONTH(NOW())"; 170​ $q = $dbConnection->query($query);
151 } else if ($period == "custom") { 171​ } else if ($period === "month") {
152 $query .= " where date >= '".$_POST["from"]."' and date <= '".$_POST["to"]." 23:59:59'"; 172​ $query .= ' WHERE YEAR(date) = YEAR(NOW()) AND MONTH(date) = MONTH(NOW())';
153 } 173​ $q = $dbConnection->query($query);
154 $q = mysql_query($query,$sq); 174​ } else if ($period === "custom") {
155 echo mysql_error(); 175​ $query .= ' WHERE date >= :from and date <= :to';
156 176​ $q = $dbConnection->prepare($query);
157 $urls = array(); 177​ $q->execute([
158 while ($row = mysql_fetch_array($q)) { 178​ 'from' => $_POST['from'],
159 if ($row[0] == "") 179​ 'to' => $_POST['to'] . ' 23:59:59',
180​ ]);
181​ } else if ($period === "all") {
182​ $q = $dbConnection->query($query);
183​ } else {
184​ echo "bug: $period";
185​ }
186​
187​ $urls = [];
188​ while ($row = $q->fetch()) {
189​ if ($row[0] === "") {
160 continue; 190​ continue;
191​ }
161 192​
162 $query2 = "select amount from donations where donate_url = '".$row[0]."'"; 193​ $query2 = 'SELECT amount FROM donations where donate_url = :donate_url';
163 if ($period == "today") { 194​ if ($period === 'today') {
164 $query2 .= " and DATE(date) = CURDATE()"; 195​ $query2 .= ' AND DATE(date) = CURDATE()';
165 } else if ($period == "yesterday") { 196​ $q2 = $dbConnection->prepare($query2);
166 $query2 .= " and DATE(date) = SUBDATE(CURDATE(), 1)"; 197​ $q2->execute([
167 } else if ($period == "month") { 198​ 'donate_url' => $row[0],
168 $query2 .= " and YEAR(date) = YEAR(NOW()) and MONTH(date) = MONTH(NOW())"; 199​ ]);
169 } else if ($period == "custom") { 200​ } else if ($period === 'yesterday') {
170 $query2 .= " and date >= '".$_POST["from"]."' and date <= '".$_POST["to"]." 23:59:59'"; 201​ $query2 .= ' AND DATE(date) = SUBDATE(CURDATE(), 1)';
202​ $q2 = $dbConnection->prepare($query2);
203​ $q2->execute([
204​ 'donate_url' => $row[0],
205​ ]);
206​ } else if ($period === 'month') {
207​ $query2 .= ' AND YEAR(date) = YEAR(NOW()) and MONTH(date) = MONTH(NOW())';
208​ $q2 = $dbConnection->prepare($query2);
209​ $q2->execute([
210​ 'donate_url' => $row[0],
211​ ]);
212​ } else if ($period === 'custom') {
213​ $query2 .= ' AND date >= :from AND date <= :to';
214​ $q2 = $dbConnection->prepare($query2);
215​ $q2->execute([
216​ 'donate_url' => $row[0],
217​ 'from' => $_POST['from'],
218​ 'to' => $_POST['to'] . ' 23:59:59',
219​ ]);
220​ } else if ($period === "all") {
221​ $q2 = $dbConnection->prepare($query2);
222​ $q2->execute([
223​ 'donate_url' => $row[0],
224​ ]);
225​ } else {
226​ echo "bug: $period";
171 } 227​ }
172 $q2 = mysql_query($query2,$sq); ​
173 echo mysql_error(); ​
174 228​
175 $total = 0; 229​ $total = 0;
176 $number = 0; 230​ $number = 0;
177 $max = -1; 231​ $max = -1;
178 $min = INF; 232​ $min = INF;
179 $all_amounts = array(); 233​ $all_amounts = array();
180 while ($row2 = mysql_fetch_array($q2)) { 234​ while ($row2 = $q2->fetch()) {
181 $amount = $row2["amount"]; 235​ $amount = $row2["amount"];
182 $total += $amount; 236​ $total += $amount;
183 $number += 1; 237​ $number += 1;
184 $max = max($amount, $max); 238​ $max = max($amount, $max);
185 $min = min($amount, $min); 239​ $min = min($amount, $min);
186 $all_amounts[] = $amount; 240​ $all_amounts[] = $amount;
187 } 241​ }
188 $urls[$row[0]] = array( $total, $number, $max, $min, $all_amounts ); 242​ $urls[$row[0]] = array( $total, $number, $max, $min, $all_amounts );
▲ Show 20 Lines • Show All 47 Lines • ▼ Show 20 Line(s)289 echo "<tr><td>".$url."</td>
236 <td align=right>".$value[1]."</td> 290​ <td align=right>".$value[1]."</td>
237 <td align=right>".$value[0]."</td> 291​ <td align=right>".$value[0]."</td>
238 <td align=right>".round($value[0]/$value[1], 2)."</td> 292​ <td align=right>".round($value[0]/$value[1], 2)."</td>
239 <td align=right>".round(standard_deviation($value[4]),2)."</td> 293​ <td align=right>".round(standard_deviation($value[4]),2)."</td>
240 <td align=right>".$value[2]."</td> 294​ <td align=right>".$value[2]."</td>
241 <td align=right>".$value[3]."</td> 295​ <td align=right>".$value[3]."</td>
242 </tr>"; 296​ </tr>";
243 } 297​ }
244 echo "</table><br>"; 298​ echo "</table><br></main>";
245 299​ include "../../../aether/footer.php";
246 include "footer.inc"; ​
247?> 300​?>
248 301​