X-Git-Url: http://git.iain.cx/?a=blobdiff_plain;f=lib%2Freport.php;h=999bbc50c4e3c78aa73c1255d6fc421b4b91acf0;hb=f02dbb704b8c6f04529bf9cae3b99d379f998567;hp=4a632f873373e4070356dff0be2b892cd27e13f1;hpb=fe9ea062f81d87aa972dfddcd70762108ead2cce;p=readifood.git diff --git a/lib/report.php b/lib/report.php index 4a632f8..999bbc5 100644 --- a/lib/report.php +++ b/lib/report.php @@ -29,15 +29,9 @@ $oldest = date('Y-m-d', $date); $then = $date; - echo " to \n"; + datepicker("from", $from, true, null, false, "to"); + echo " to "; + datepicker("to", $to, true, "from", false); submit("show_reports", "Show"); end_form(); @@ -66,6 +60,140 @@ return true; } + function show_order_report($from, &$order_state_ids) { + echo "

Orders by week

\n"; + + /* Handle missing weeks at the start of the range. */ + $dbh = Propel::getConnection(); + $sth = $dbh->prepare("select yearweek(:from)"); + $sth->execute(array(':from' => $from)); + list($first_week) = $sth->fetch(); + $year_offset = substr($first_week, 0, 4); + $week_offset = substr($first_week, 4, 2) - 1; + + $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 = 1; + $last_week = 0; + $total = 0; + echo "\n"; + foreach ($rows as $row) { + /* + Convert week of year to date range. + Beware that week 201401 comes after 201352. + */ + $yearweek = $row->getWeek(); + $y = substr($yearweek, 0, 4); + $w = substr($yearweek, 4, 2); + $week = (($y - $year_offset) * 52) + ($w - $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_postcode_report(&$order_ids) { + echo "

Orders by postcode

\n"; + + /* + No regex replace support in MySQL so we'll have to retrieve all records + and group the postcodes ourselves. + */ + $q = new OrderQuery; + $q->filterById($order_ids); + $q->join("Beneficiary"); + /* No foreign key so we need to list the two tables. */ + $q->join("Beneficiary.Address"); + /* Not a FoodOrder column so we need to ask for it explicitly. */ + $q->withColumn('upper(postcode)', 'postcode'); + $rows = $q->find(); + + $total = 0; + $postcodes = array(); + foreach ($rows as $row) { + $postcode = preg_replace('/\s*[0-9][A-Z]+$/', '', trim($row->getPostcode())); + if (! $postcode) $postcode = "Unknown"; + $postcodes[$postcode]++; + $total++; + } + ksort($postcodes); + + echo "\n"; + foreach ($postcodes as $postcode => $count) { + echo "\n"; + printf(" \n", $count, htmlspecialchars($postcode)); + echo "\n"; + } + echo "\n"; + echo " \n"; + echo "\n"; + echo "
%d%s
$totalTotal
\n"; + } + + function show_contents_report(&$order_ids, $parcel_size, $grand_total) { + global $parcel_sizes, $parcel_contents; + + $total = 0; + for ($i = count($parcel_sizes); $i < count($parcel_contents); $i++) { + $q = new OrderQuery; + $q->filterById($order_ids); + $q->where(sprintf("parcel & %d", $parcel_size)); + $q->where(sprintf("parcel & %d", (1 << $i))); + $contents = $q->find(); + $total += count($contents); + echo "\n"; + printf(" %d%s\n", count($contents), htmlspecialchars($parcel_contents[$i])); + echo "\n"; + } + + /* No special contents. */ + echo "\n"; + printf(" %d%s no special contents\n", $grand_total - $total, htmlspecialchars($parcel_sizes[$parcel_size >> 1])); + echo "\n"; + } + + function show_parcel_report(&$order_ids) { + global $parcel_sizes; + echo "

Orders by parcel type

\n"; + + $q = new OrderQuery; + $q->filterById($order_ids); + $q->withColumn(sprintf("parcel & %d", (1 << count($parcel_sizes)) - 1), 'size'); + $q->withColumn('count(*)', 'count'); + $q->addGroupByColumn('size')->addAscendingOrderByColumn('size'); + $rows = $q->find(); + $total = 0; + echo "\n"; + foreach ($rows as $row) { + echo "\n"; + $requester = get_contact_by_id($row->getRequesterId()); + printf(" \n", $row->getCount(), htmlspecialchars($parcel_sizes[$row->getSize() >> 1])); + $total += $row->getCount(); + echo "\n"; + show_contents_report($order_ids, $row->getSize(), $row->getCount()); + } + echo "\n"; + echo " \n"; + echo "\n"; + echo "
%d%s
$totalTotal
\n"; + } + function show_requester_report(&$order_ids) { echo "

Orders by referrer

\n"; @@ -74,14 +202,19 @@ $q->withColumn('count(*)', 'count'); $q->groupByRequesterId()->addDescendingOrderByColumn('count'); $rows = $q->find(); - echo "\n"; + $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 +225,19 @@ /* 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(); + $dups = array(); + foreach ($order_states as $order_state) { + $order_id = $order_state->getOrderId(); + $order_ids[] = $order_id; + if (! $dups[$order_id]) $order_state_ids[] = $order_state->getId(); + $dups[$order_id] = true; + } $q = new OrderQuery; $q->filterById($order_ids); @@ -106,6 +246,9 @@ return; } + show_order_report($from, $order_state_ids); + show_postcode_report($order_ids); + show_parcel_report($order_ids); show_requester_report($order_ids); }