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 check_report_dates($from, $to) {
41 list($y, $m, $d) = explode('-', $from);
42 if (! checkdate($m, $d, $y)) {
43 echo "<p>Invalid report start date!</p>\n";
46 $start = mktime(0, 0, 0, $m, $d, $y);
48 list($y, $m, $d) = explode('-', $to);
49 if (! checkdate($m, $d, $y)) {
50 echo "<p>Invalid report end date!</p>\n";
53 $end = mktime(0, 0, 0, $m, $d, $y);
56 echo "<p>Report end date is earlier than start date!</p>\n";
63 function show_order_report(&$order_state_ids) {
64 echo "<h3>Orders by week</h3>\n";
66 $q = new OrderStateQuery;
67 $q->filterById($order_state_ids);
68 $q->withColumn('yearweek(updated)', 'week');
69 $q->withColumn('count(*)', 'count');
70 $q->addGroupByColumn('week')->orderByUpdated();
76 echo "<table class=\"report\">\n";
77 foreach ($rows as $row) {
78 /* Convert week of year to date range. */
79 if (! $week_offset) $week_offset = $row->getWeek() - 1;
80 else $week = $row->getWeek() - $week_offset;
81 $total += $row->getCount();
82 /* Fill in missing weeks. XXX */
83 for ($missing_week = $last_week + 1; $missing_week < $week; $missing_week++) {
85 printf(" <td align=\"right\">0</td><td>Week %d</td>\n", $missing_week);
89 printf(" <td align=\"right\">%d</td><td>Week %d</td>\n", $row->getCount(), $week);
94 echo " <td align=\"right\" class=\"strong\">$total</td><td class=\"strong\">Total</td>\n";
99 function show_postcode_report(&$order_ids) {
100 echo "<h3>Orders by postcode</h3>\n";
103 No regex replace support in MySQL so we'll have to retrieve all records
104 and group the postcodes ourselves.
107 $q->filterById($order_ids);
108 $q->join("Beneficiary");
109 /* No foreign key so we need to list the two tables. */
110 $q->join("Beneficiary.Address");
111 /* Not a FoodOrder column so we need to ask for it explicitly. */
112 $q->withColumn('upper(postcode)', 'postcode');
116 $postcodes = array();
117 foreach ($rows as $row) {
118 $postcode = preg_replace('/\s*[0-9][A-Z]+$/', '', trim($row->getPostcode()));
119 if (! $postcode) $postcode = "Unknown";
120 $postcodes[$postcode]++;
125 echo "<table class=\"report\">\n";
126 foreach ($postcodes as $postcode => $count) {
128 printf(" <td align=\"right\">%d</td><td>%s</td>\n", $count, htmlspecialchars($postcode));
132 echo " <td align=\"right\" class=\"strong\">$total</td><td class=\"strong\">Total</td>\n";
137 function show_contents_report(&$order_ids, $parcel_size, $grand_total) {
138 global $parcel_sizes, $parcel_contents;
141 for ($i = count($parcel_sizes); $i < count($parcel_contents); $i++) {
143 $q->filterById($order_ids);
144 $q->where(sprintf("parcel & %d", $parcel_size));
145 $q->where(sprintf("parcel & %d", (1 << $i)));
146 $contents = $q->find();
147 $total += count($contents);
148 echo "<tr class=\"small\">\n";
149 printf(" <td align=\"right\">%d</td><td>%s</td>\n", count($contents), htmlspecialchars($parcel_contents[$i]));
153 /* No special contents. */
154 echo "<tr class=\"small\">\n";
155 printf(" <td align=\"right\">%d</td><td>%s no special contents</td>\n", $grand_total - $total, htmlspecialchars($parcel_sizes[$parcel_size >> 1]));
159 function show_parcel_report(&$order_ids) {
160 global $parcel_sizes;
161 echo "<h3>Orders by parcel type</h3>\n";
164 $q->filterById($order_ids);
165 $q->withColumn(sprintf("parcel & %d", (1 << count($parcel_sizes)) - 1), 'size');
166 $q->withColumn('count(*)', 'count');
167 $q->addGroupByColumn('size')->addAscendingOrderByColumn('size');
170 echo "<table class=\"report\">\n";
171 foreach ($rows as $row) {
173 $requester = get_contact_by_id($row->getRequesterId());
174 printf(" <td align=\"right\">%d</td><td>%s</td>\n", $row->getCount(), htmlspecialchars($parcel_sizes[$row->getSize() >> 1]));
175 $total += $row->getCount();
177 show_contents_report($order_ids, $row->getSize(), $row->getCount());
180 echo " <td align=\"right\" class=\"strong\">$total</td><td class=\"strong\">Total</td>\n";
185 function show_requester_report(&$order_ids) {
186 echo "<h3>Orders by referrer</h3>\n";
189 $q->filterById($order_ids);
190 $q->withColumn('count(*)', 'count');
191 $q->groupByRequesterId()->addDescendingOrderByColumn('count');
194 echo "<table class=\"report\">\n";
196 foreach ($rows as $row) {
198 $requester = get_contact_by_id($row->getRequesterId());
199 printf(" <td align=\"right\">%d</td><td>%s</td>\n", $row->getCount(), htmlspecialchars($requester->getDisplayname()));
200 $total += $row->getCount();
204 echo " <td align=\"right\" class=\"strong\">$total</td><td class=\"strong\">Total</td>\n";
209 function show_reports($from, $to) {
210 if (! check_report_dates($from, $to)) return;
212 echo "<p>Showing reports for the period <strong>$from</strong> to <strong>$to</strong>.</p>\n";
215 $order_ids = array();
216 $order_state_ids = array();
217 /* XXX: Order 51 changed to state delivered in May then updated in June. */
218 $dbh = Propel::getConnection();
219 $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'");
221 $order_states = OrderStatePeer::populateObjects($sth);
222 foreach ($order_states as $order_state) {
223 $order_ids[] = $order_state->getOrderId();
224 $order_state_ids[] = $order_state->getId();
227 $q->filterById($order_ids);
229 if (! count($order_ids)) {
230 echo "<p>No results!</p>\n";
234 show_order_report($order_state_ids);
235 show_postcode_report($order_ids);
236 show_parcel_report($order_ids);
237 show_requester_report($order_ids);
240 if (count($parameters)) {
241 if ($parameters[0] == "from") {
242 $from = $parameters[1];
243 if ($parameters[2] == "to") $to = $parameters[3];
244 show_reports($from, $to);
247 show_reports_form($from, $to);