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_contents_report(&$order_ids, $parcel_size, $grand_total) {
106 global $parcel_sizes, $parcel_contents;
109 for ($i = count($parcel_sizes); $i < count($parcel_contents); $i++) {
111 $q->filterById($order_ids);
112 $q->where(sprintf("parcel & %d", $parcel_size));
113 $q->where(sprintf("parcel & %d", (1 << $i)));
114 $contents = $q->find();
115 $total += count($contents);
116 echo "<tr class=\"small\">\n";
117 printf(" <td align=\"right\">%d</td><td>%s</td>\n", count($contents), htmlspecialchars($parcel_contents[$i]));
121 /* No special contents. */
122 echo "<tr class=\"small\">\n";
123 printf(" <td align=\"right\">%d</td><td>%s no special contents</td>\n", $grand_total - $total, htmlspecialchars($parcel_sizes[$parcel_size >> 1]));
127 function show_parcel_report(&$order_ids) {
128 global $parcel_sizes;
129 echo "<h3>Orders by parcel type</h3>\n";
132 $q->filterById($order_ids);
133 $q->withColumn(sprintf("parcel & %d", (1 << count($parcel_sizes)) - 1), 'size');
134 $q->withColumn('count(*)', 'count');
135 $q->addGroupByColumn('size')->addAscendingOrderByColumn('size');
138 echo "<table class=\"report\">\n";
139 foreach ($rows as $row) {
141 $requester = get_contact_by_id($row->getRequesterId());
142 printf(" <td align=\"right\">%d</td><td>%s</td>\n", $row->getCount(), htmlspecialchars($parcel_sizes[$row->getSize() >> 1]));
143 $total += $row->getCount();
145 show_contents_report($order_ids, $row->getSize(), $row->getCount());
148 echo " <td align=\"right\" class=\"strong\">$total</td><td class=\"strong\">Total</td>\n";
153 function show_requester_report(&$order_ids) {
154 echo "<h3>Orders by referrer</h3>\n";
157 $q->filterById($order_ids);
158 $q->withColumn('count(*)', 'count');
159 $q->groupByRequesterId()->addDescendingOrderByColumn('count');
162 echo "<table class=\"report\">\n";
164 foreach ($rows as $row) {
166 $requester = get_contact_by_id($row->getRequesterId());
167 printf(" <td align=\"right\">%d</td><td>%s</td>\n", $row->getCount(), htmlspecialchars($requester->getDisplayname()));
168 $total += $row->getCount();
172 echo " <td align=\"right\" class=\"strong\">$total</td><td class=\"strong\">Total</td>\n";
177 function show_reports($from, $to) {
178 if (! check_report_dates($from, $to)) return;
180 echo "<p>Showing reports for the period <strong>$from</strong> to <strong>$to</strong>.</p>\n";
183 $order_ids = array();
184 $order_state_ids = array();
185 /* XXX: Order 51 changed to state delivered in May then updated in June. */
186 $dbh = Propel::getConnection();
187 $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'");
189 $order_states = OrderStatePeer::populateObjects($sth);
190 foreach ($order_states as $order_state) {
191 $order_ids[] = $order_state->getOrderId();
192 $order_state_ids[] = $order_state->getId();
195 $q->filterById($order_ids);
197 if (! count($order_ids)) {
198 echo "<p>No results!</p>\n";
202 show_order_report($order_state_ids);
203 show_parcel_report($order_ids);
204 show_requester_report($order_ids);
207 if (count($parameters)) {
208 if ($parameters[0] == "from") {
209 $from = $parameters[1];
210 if ($parameters[2] == "to") $to = $parameters[3];
211 show_reports($from, $to);
214 show_reports_form($from, $to);