Show reports covering the period from "; /* Default to last month. */ list($y, $m, $d) = explode('-', date('Y-m-d', time())); $latest = "$y-$m-$d"; $now = mktime(0, 0, 0, $m, $d, $y); $first = mktime(0, 0, 0, $m, 1, $y); $last = $first - 86400; $date = date('Y-m-d', $last); if (is_null($to)) $to = $date; list($y, $m, $d) = explode('-', $date); $first = mktime(0, 0, 0, $m, 1, $y); if (is_null($from)) $from = date('Y-m-d', $first); $date = $first; for ($i = 0; $i < 2; $i++) { $date -= 86400; list ($y, $m, $d) = explode('-', date('Y-m-d', $date)); $date = mktime(0, 0, 0, $m, 1, $y); } $oldest = date('Y-m-d', $date); $then = $date; datepicker("from", $from, true, null, false, "to"); echo " to "; datepicker("to", $to, true, "from", false); submit("show_reports", "Show"); end_form(); } function check_report_dates($from, $to) { list($y, $m, $d) = explode('-', $from); if (! checkdate($m, $d, $y)) { echo "

Invalid report start date!

\n"; return false; } $start = mktime(0, 0, 0, $m, $d, $y); list($y, $m, $d) = explode('-', $to); if (! checkdate($m, $d, $y)) { echo "

Invalid report end date!

\n"; return false; } $end = mktime(0, 0, 0, $m, $d, $y); if ($end < $start) { echo "

Report end date is earlier than start date!

\n"; return false; } 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"; $q = new OrderQuery; $q->filterById($order_ids); $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())); $total += $row->getCount(); echo "\n"; } echo "\n"; echo " \n"; echo "\n"; echo "
%d%s
$totalTotal
\n"; } function show_reports($from, $to) { if (! check_report_dates($from, $to)) return; echo "

Showing reports for the period $from to $to.

\n"; /* 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); $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); if (! count($order_ids)) { echo "

No results!

\n"; return; } show_order_report($from, $order_state_ids); show_postcode_report($order_ids); show_parcel_report($order_ids); show_requester_report($order_ids); } if (count($parameters)) { if ($parameters[0] == "from") { $from = $parameters[1]; if ($parameters[2] == "to") $to = $parameters[3]; show_reports($from, $to); } } show_reports_form($from, $to); ?>