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>0</td><td>Week %d</td>\n", $missing_week);
95 printf(" <td>%d</td><td>Week %d</td>\n", $row->getCount(), $week);
100 echo " <td class=\"strong\">$total</td><td class=\"strong\">Total</td>\n";
105 function show_size_report(&$order_ids) {
106 global $parcel_sizes;
107 echo "<h3>Orders by parcel size</h3>\n";
110 $q->filterById($order_ids);
111 $q->withColumn(sprintf("parcel & %d", (1 << count($parcel_sizes)) - 1), 'size');
112 $q->withColumn('count(*)', 'count');
113 $q->addGroupByColumn('size')->addAscendingOrderByColumn('size');
116 echo "<table class=\"report\">\n";
117 foreach ($rows as $row) {
119 $requester = get_contact_by_id($row->getRequesterId());
120 printf(" <td>%d</td><td>%s</td>\n", $row->getCount(), htmlspecialchars($parcel_sizes[$row->getSize() >> 1]));
121 $total += $row->getCount();
125 echo " <td class=\"strong\">$total</td><td class=\"strong\">Total</td>\n";
130 function show_requester_report(&$order_ids) {
131 echo "<h3>Orders by referrer</h3>\n";
134 $q->filterById($order_ids);
135 $q->withColumn('count(*)', 'count');
136 $q->groupByRequesterId()->addDescendingOrderByColumn('count');
139 echo "<table class=\"report\">\n";
141 foreach ($rows as $row) {
143 $requester = get_contact_by_id($row->getRequesterId());
144 printf(" <td>%d</td><td>%s</td>\n", $row->getCount(), htmlspecialchars($requester->getDisplayname()));
145 $total += $row->getCount();
149 echo " <td class=\"strong\">$total</td><td class=\"strong\">Total</td>\n";
154 function show_reports($from, $to) {
155 if (! check_report_dates($from, $to)) return;
157 echo "<p>Showing reports for the period <strong>$from</strong> to <strong>$to</strong>.</p>\n";
160 $order_ids = array();
161 $order_state_ids = array();
162 /* XXX: Order 51 changed to state delivered in May then updated in June. */
163 $dbh = Propel::getConnection();
164 $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'");
166 $order_states = OrderStatePeer::populateObjects($sth);
167 foreach ($order_states as $order_state) {
168 $order_ids[] = $order_state->getOrderId();
169 $order_state_ids[] = $order_state->getId();
172 $q->filterById($order_ids);
174 if (! count($order_ids)) {
175 echo "<p>No results!</p>\n";
179 show_order_report($order_state_ids);
180 show_size_report($order_ids);
181 show_requester_report($order_ids);
184 if (count($parameters)) {
185 if ($parameters[0] == "from") {
186 $from = $parameters[1];
187 if ($parameters[2] == "to") $to = $parameters[3];
188 show_reports($from, $to);
191 show_reports_form($from, $to);