3 if (isset($_POST['show_reports'])) {
4 header(sprintf("Location: http%s://%s/%s/from/%s/to/%s", ($_SERVER['HTTPS']) ? "s" : "", $_SERVER['HTTP_HOST'], $module, $_POST['from'], $_POST['to']));
8 function show_reports_form($from = null, $to = null) {
9 form("noprint standout");
10 echo "<p>Show reports covering the period from ";
12 /* Default to last month. */
13 list($y, $m, $d) = explode('-', date('Y-m-d', time()));
15 $now = mktime(0, 0, 0, $m, $d, $y);
16 $first = mktime(0, 0, 0, $m, 1, $y);
17 $last = $first - 86400;
18 $date = date('Y-m-d', $last);
19 if (is_null($to)) $to = $date;
20 list($y, $m, $d) = explode('-', $date);
21 $first = mktime(0, 0, 0, $m, 1, $y);
22 if (is_null($from)) $from = date('Y-m-d', $first);
24 for ($i = 0; $i < 2; $i++) {
26 list ($y, $m, $d) = explode('-', date('Y-m-d', $date));
27 $date = mktime(0, 0, 0, $m, 1, $y);
29 $oldest = date('Y-m-d', $date);
32 echo "<select name=\"from\">\n";
33 for ($date = $then; $date <= $now; $date += 86400) {
34 option("from", date('Y-m-d', $date), date('j F Y', $date), $from);
36 echo "</select> to <select name=\"to\">\n";
37 for ($date = $then; $date <= $now; $date += 86400) {
38 option("to", date('Y-m-d', $date), date('j F Y', $date), $to);
42 submit("show_reports", "Show");
46 function check_report_dates($from, $to) {
47 list($y, $m, $d) = explode('-', $from);
48 if (! checkdate($m, $d, $y)) {
49 echo "<p>Invalid report start date!</p>\n";
52 $start = mktime(0, 0, 0, $m, $d, $y);
54 list($y, $m, $d) = explode('-', $to);
55 if (! checkdate($m, $d, $y)) {
56 echo "<p>Invalid report end date!</p>\n";
59 $end = mktime(0, 0, 0, $m, $d, $y);
62 echo "<p>Report end date is earlier than start date!</p>\n";
69 function show_order_report(&$order_state_ids) {
70 echo "<h3>Orders by week</h3>\n";
72 $q = new OrderStateQuery;
73 $q->filterById($order_state_ids);
74 $q->withColumn('yearweek(updated)', 'week');
75 $q->withColumn('count(*)', 'count');
76 $q->addGroupByColumn('week')->orderByUpdated();
82 echo "<table class=\"report\">\n";
83 foreach ($rows as $row) {
84 /* Convert week of year to date range. */
85 if (! $week_offset) $week_offset = $row->getWeek() - 1;
86 else $week = $row->getWeek() - $week_offset;
87 $total += $row->getCount();
88 /* Fill in missing weeks. XXX */
89 for ($missing_week = $last_week + 1; $missing_week < $week; $missing_week++) {
91 printf(" <td align=\"right\">0</td><td>Week %d</td>\n", $missing_week);
95 printf(" <td align=\"right\">%d</td><td>Week %d</td>\n", $row->getCount(), $week);
100 echo " <td align=\"right\" class=\"strong\">$total</td><td class=\"strong\">Total</td>\n";
105 function show_postcode_report(&$order_ids) {
106 echo "<h3>Orders by postcode</h3>\n";
109 No regex replace support in MySQL so we'll have to retrieve all records
110 and group the postcodes ourselves.
113 $q->filterById($order_ids);
114 $q->join("Beneficiary");
115 /* No foreign key so we need to list the two tables. */
116 $q->join("Beneficiary.Address");
117 /* Not a FoodOrder column so we need to ask for it explicitly. */
118 $q->withColumn('upper(postcode)', 'postcode');
122 $postcodes = array();
123 foreach ($rows as $row) {
124 $postcode = preg_replace('/\s*[0-9][A-Z]+$/', '', trim($row->getPostcode()));
125 if (! $postcode) $postcode = "Unknown";
126 $postcodes[$postcode]++;
131 echo "<table class=\"report\">\n";
132 foreach ($postcodes as $postcode => $count) {
134 printf(" <td align=\"right\">%d</td><td>%s</td>\n", $count, htmlspecialchars($postcode));
138 echo " <td align=\"right\" class=\"strong\">$total</td><td class=\"strong\">Total</td>\n";
143 function show_contents_report(&$order_ids, $parcel_size, $grand_total) {
144 global $parcel_sizes, $parcel_contents;
147 for ($i = count($parcel_sizes); $i < count($parcel_contents); $i++) {
149 $q->filterById($order_ids);
150 $q->where(sprintf("parcel & %d", $parcel_size));
151 $q->where(sprintf("parcel & %d", (1 << $i)));
152 $contents = $q->find();
153 $total += count($contents);
154 echo "<tr class=\"small\">\n";
155 printf(" <td align=\"right\">%d</td><td>%s</td>\n", count($contents), htmlspecialchars($parcel_contents[$i]));
159 /* No special contents. */
160 echo "<tr class=\"small\">\n";
161 printf(" <td align=\"right\">%d</td><td>%s no special contents</td>\n", $grand_total - $total, htmlspecialchars($parcel_sizes[$parcel_size >> 1]));
165 function show_parcel_report(&$order_ids) {
166 global $parcel_sizes;
167 echo "<h3>Orders by parcel type</h3>\n";
170 $q->filterById($order_ids);
171 $q->withColumn(sprintf("parcel & %d", (1 << count($parcel_sizes)) - 1), 'size');
172 $q->withColumn('count(*)', 'count');
173 $q->addGroupByColumn('size')->addAscendingOrderByColumn('size');
176 echo "<table class=\"report\">\n";
177 foreach ($rows as $row) {
179 $requester = get_contact_by_id($row->getRequesterId());
180 printf(" <td align=\"right\">%d</td><td>%s</td>\n", $row->getCount(), htmlspecialchars($parcel_sizes[$row->getSize() >> 1]));
181 $total += $row->getCount();
183 show_contents_report($order_ids, $row->getSize(), $row->getCount());
186 echo " <td align=\"right\" class=\"strong\">$total</td><td class=\"strong\">Total</td>\n";
191 function show_requester_report(&$order_ids) {
192 echo "<h3>Orders by referrer</h3>\n";
195 $q->filterById($order_ids);
196 $q->withColumn('count(*)', 'count');
197 $q->groupByRequesterId()->addDescendingOrderByColumn('count');
200 echo "<table class=\"report\">\n";
202 foreach ($rows as $row) {
204 $requester = get_contact_by_id($row->getRequesterId());
205 printf(" <td align=\"right\">%d</td><td>%s</td>\n", $row->getCount(), htmlspecialchars($requester->getDisplayname()));
206 $total += $row->getCount();
210 echo " <td align=\"right\" class=\"strong\">$total</td><td class=\"strong\">Total</td>\n";
215 function show_reports($from, $to) {
216 if (! check_report_dates($from, $to)) return;
218 echo "<p>Showing reports for the period <strong>$from</strong> to <strong>$to</strong>.</p>\n";
221 $order_ids = array();
222 $order_state_ids = array();
223 /* XXX: Order 51 changed to state delivered in May then updated in June. */
224 $dbh = Propel::getConnection();
225 $sth = $dbh->prepare("select * from OrderState o where updated=(select min(updated) from OrderState where order_id=o.order_id and state & " . $GLOBALS['STATE_DELIVERED'] . ") and updated between '$from' and '$to'");
227 $order_states = OrderStatePeer::populateObjects($sth);
228 foreach ($order_states as $order_state) {
229 $order_ids[] = $order_state->getOrderId();
230 $order_state_ids[] = $order_state->getId();
233 $q->filterById($order_ids);
235 if (! count($order_ids)) {
236 echo "<p>No results!</p>\n";
240 show_order_report($order_state_ids);
241 show_postcode_report($order_ids);
242 show_parcel_report($order_ids);
243 show_requester_report($order_ids);
246 if (count($parameters)) {
247 if ($parameters[0] == "from") {
248 $from = $parameters[1];
249 if ($parameters[2] == "to") $to = $parameters[3];
250 show_reports($from, $to);
253 show_reports_form($from, $to);