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 datepicker("from", $from, true, null, false, "to");
34 datepicker("to", $to, true, "from", false);
36 submit("show_reports", "Show");
40 function show_order_report($from, &$order_state_ids) {
41 echo "<h3>Orders by week</h3>\n";
43 /* Handle missing weeks at the start of the range. */
44 $dbh = Propel::getConnection();
45 $sth = $dbh->prepare("select yearweek(:from)");
46 $sth->execute(array(':from' => $from));
47 list($first_week) = $sth->fetch();
48 $year_offset = substr($first_week, 0, 4);
49 $week_offset = substr($first_week, 4, 2) - 1;
51 $q = new OrderStateQuery;
52 $q->filterById($order_state_ids);
53 $q->withColumn('yearweek(updated)', 'week');
54 $q->withColumn('count(*)', 'count');
55 $q->addGroupByColumn('week')->orderByUpdated();
60 echo "<table class=\"report\">\n";
61 foreach ($rows as $row) {
63 Convert week of year to date range.
64 Beware that week 201401 comes after 201352.
66 $yearweek = $row->getWeek();
67 $y = substr($yearweek, 0, 4);
68 $w = substr($yearweek, 4, 2);
69 $week = (($y - $year_offset) * 52) + ($w - $week_offset);
70 $total += $row->getCount();
71 /* Fill in missing weeks. XXX */
72 for ($missing_week = $last_week + 1; $missing_week < $week; $missing_week++) {
74 printf(" <td align=\"right\">0</td><td>Week %d</td>\n", $missing_week);
78 printf(" <td align=\"right\">%d</td><td>Week %d</td>\n", $row->getCount(), $week);
83 echo " <td align=\"right\" class=\"strong\">$total</td><td class=\"strong\">Total</td>\n";
88 function show_postcode_report(&$order_ids) {
89 echo "<h3>Orders by postcode</h3>\n";
92 No regex replace support in MySQL so we'll have to retrieve all records
93 and group the postcodes ourselves.
96 $q->filterById($order_ids);
97 $q->join("Beneficiary");
98 /* No foreign key so we need to list the two tables. */
99 $q->join("Beneficiary.Address");
100 /* Not a FoodOrder column so we need to ask for it explicitly. */
101 $q->withColumn('upper(postcode)', 'postcode');
105 $postcodes = array();
106 foreach ($rows as $row) {
107 $postcode = preg_replace('/\s*[0-9][A-Z]+$/', '', trim($row->getPostcode()));
108 if (! $postcode) $postcode = "Unknown";
109 $postcodes[$postcode]++;
114 echo "<table class=\"report\">\n";
115 foreach ($postcodes as $postcode => $count) {
117 printf(" <td align=\"right\">%d</td><td>%s</td>\n", $count, htmlspecialchars($postcode));
121 echo " <td align=\"right\" class=\"strong\">$total</td><td class=\"strong\">Total</td>\n";
126 function show_contents_report(&$order_ids, $parcel_size, $grand_total) {
127 global $parcel_sizes, $parcel_contents;
130 for ($i = count($parcel_sizes); $i < count($parcel_contents); $i++) {
132 $q->filterById($order_ids);
133 $q->where(sprintf("parcel & %d", $parcel_size));
134 $q->where(sprintf("parcel & %d", (1 << $i)));
135 $contents = $q->find();
136 $total += count($contents);
137 echo "<tr class=\"small\">\n";
138 printf(" <td align=\"right\">%d</td><td>%s</td>\n", count($contents), htmlspecialchars($parcel_contents[$i]));
142 /* No special contents. */
143 echo "<tr class=\"small\">\n";
144 printf(" <td align=\"right\">%d</td><td>%s no special contents</td>\n", $grand_total - $total, htmlspecialchars($parcel_sizes[$parcel_size >> 1]));
148 function show_parcel_report(&$order_ids) {
149 global $parcel_sizes;
150 echo "<h3>Orders by parcel type</h3>\n";
153 $q->filterById($order_ids);
154 $q->withColumn(sprintf("parcel & %d", (1 << count($parcel_sizes)) - 1), 'size');
155 $q->withColumn('count(*)', 'count');
156 $q->addGroupByColumn('size')->addAscendingOrderByColumn('size');
159 echo "<table class=\"report\">\n";
160 foreach ($rows as $row) {
162 $requester = get_contact_by_id($row->getRequesterId());
163 printf(" <td align=\"right\">%d</td><td>%s</td>\n", $row->getCount(), htmlspecialchars($parcel_sizes[$row->getSize() >> 1]));
164 $total += $row->getCount();
166 show_contents_report($order_ids, $row->getSize(), $row->getCount());
169 echo " <td align=\"right\" class=\"strong\">$total</td><td class=\"strong\">Total</td>\n";
174 function show_requester_report(&$order_ids) {
175 echo "<h3>Orders by referrer</h3>\n";
178 $q->filterById($order_ids);
179 $q->withColumn('count(*)', 'count');
180 $q->groupByRequesterId()->addDescendingOrderByColumn('count');
183 echo "<table class=\"report\">\n";
185 foreach ($rows as $row) {
187 $requester = get_contact_by_id($row->getRequesterId());
188 printf(" <td align=\"right\">%d</td><td>%s</td>\n", $row->getCount(), htmlspecialchars($requester->getDisplayname()));
189 $total += $row->getCount();
193 echo " <td align=\"right\" class=\"strong\">$total</td><td class=\"strong\">Total</td>\n";
198 function show_reports($from, $to) {
199 if (! check_dates('report', $from, $to)) return;
201 echo "<p>Showing reports for the period <strong>$from</strong> to <strong>$to</strong>.</p>\n";
204 $order_ids = array();
205 $order_state_ids = array();
206 /* XXX: Order 51 changed to state delivered in May then updated in June. */
207 $dbh = Propel::getConnection();
208 $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'");
210 $order_states = OrderStatePeer::populateObjects($sth);
212 foreach ($order_states as $order_state) {
213 $order_id = $order_state->getOrderId();
214 $order_ids[] = $order_id;
215 if (! $dups[$order_id]) $order_state_ids[] = $order_state->getId();
216 $dups[$order_id] = true;
219 $q->filterById($order_ids);
221 if (! count($order_ids)) {
222 echo "<p>No results!</p>\n";
226 show_order_report($from, $order_state_ids);
227 show_postcode_report($order_ids);
228 show_parcel_report($order_ids);
229 show_requester_report($order_ids);
232 if (count($parameters)) {
233 if ($parameters[0] == "from") {
234 $from = $parameters[1];
235 if ($parameters[2] == "to") $to = $parameters[3];
236 show_reports($from, $to);
239 show_reports_form($from, $to);