From 524fe78f250d75cc54dba1dfb482ea4cdf574768 Mon Sep 17 00:00:00 2001 From: Iain Patterson Date: Mon, 5 Aug 2013 18:37:14 +0100 Subject: [PATCH] Report orders by week. --- lib/report.php | 50 ++++++++++++++++++++++++++++++++++++++++++++++++-- 1 file changed, 48 insertions(+), 2 deletions(-) diff --git a/lib/report.php b/lib/report.php index 4a632f8..2fabab4 100644 --- a/lib/report.php +++ b/lib/report.php @@ -66,6 +66,42 @@ return true; } + function show_order_report(&$order_state_ids) { + echo "

Orders by week

\n"; + + $q = new OrderStateQuery; + $q->filterById($order_state_ids); + $q->withColumn('yearweek(updated)', 'week'); + $q->withColumn('count(*)', 'count'); + $q->addGroupByColumn('week')->orderByUpdated(); + $rows = $q->find(); + $week_offset = 0; + $week = 1; + $last_week = 0; + $total = 0; + echo "\n"; + foreach ($rows as $row) { + /* Convert week of year to date range. */ + if (! $week_offset) $week_offset = $row->getWeek() - 1; + else $week = $row->getWeek() - $week_offset; + $total += $row->getCount(); + /* Fill in missing weeks. XXX */ + for ($missing_week = $last_week + 1; $missing_week < $week; $missing_week++) { + echo "\n"; + printf(" \n", $missing_week); + echo "\n"; + } + echo "\n"; + printf(" \n", $row->getCount(), $week); + echo "\n"; + $last_week = $week; + } + echo "\n"; + echo " \n"; + echo "\n"; + echo "
0Week %d
%dWeek %d
$totalTotal
\n"; + } + function show_requester_report(&$order_ids) { echo "

Orders by referrer

\n"; @@ -74,14 +110,19 @@ $q->withColumn('count(*)', 'count'); $q->groupByRequesterId()->addDescendingOrderByColumn('count'); $rows = $q->find(); + $total = 0; echo "\n"; /* XXX Join! */ foreach ($rows as $row) { echo "\n"; $requester = get_contact_by_id($row->getRequesterId()); - printf(" \n", $row->getCount(), htmlspecialchars($requester->getDisplayname())); + printf(" \n", $row->getCount(), htmlspecialchars($requester->getDisplayname())); + $total += $row->getCount(); echo "\n"; } + echo "\n"; + echo " \n"; + echo "\n"; echo "
%d %s%d%s
$totalTotal
\n"; } @@ -92,12 +133,16 @@ /* Get orders. */ $order_ids = array(); + $order_state_ids = array(); /* XXX: Order 51 changed to state delivered in May then updated in June. */ $dbh = Propel::getConnection(); $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'"); $sth->execute(); $order_states = OrderStatePeer::populateObjects($sth); - foreach ($order_states as $order_state) $order_ids[] = $order_state->getOrderId(); + foreach ($order_states as $order_state) { + $order_ids[] = $order_state->getOrderId(); + $order_state_ids[] = $order_state->getId(); + } $q = new OrderQuery; $q->filterById($order_ids); @@ -106,6 +151,7 @@ return; } + show_order_report($order_state_ids); show_requester_report($order_ids); } -- 2.7.4