/**
* Format time expression parser expression using gmdate().
* @see http://php.net/manual/en/function.gmdate.php
* @see http://docs.collectiveaccess.org/wiki/Date_and_Time_Formats
*
* @param string $ps_date_expression valid TEP expression
* @param string $ps_format gmdate() format string
* @return null|string
*/
function caFormatGMDate($ps_date_expression, $ps_format = 'c')
{
$va_unix_timestamps = caDateToUnixTimestamps($ps_date_expression);
if (!is_numeric($va_unix_timestamps['start'])) {
return null;
}
return gmdate($ps_format, (int) $va_unix_timestamps['start']);
}
/**
* Get orders matching criteria specified by options
*
* @param array $pa_options An array of options:
* user_id =
* transaction_id =
* created_on = date range expression
* shipping_date =
* shipped_on_date =
* shipping_method =
* order_status =
* search =
* type =
* loan_checkout_date =
* loan_due_date =
* loan_return_date =
* is_overdue =
* is_outstanding =
* object_id =
* exclude = optional array of order_id's to omit from the returned list
*/
public function getOrders($pa_options = null)
{
$o_db = $this->getDb();
$vb_join_transactions = false;
$va_sql_wheres = $va_sql_values = array();
if (isset($pa_options['is_overdue']) && (bool) $pa_options['is_overdue']) {
$pa_options['type'] = 'L';
$va_sql_wheres[] = "(i.loan_due_date < ?)";
$va_sql_values[] = time();
$va_sql_wheres[] = "(i.loan_return_date IS NULL)";
}
if (isset($pa_options['exclude']) && is_array($pa_options['exclude'])) {
$va_sql_wheres[] = "(o.order_id NOT IN (?))";
$va_sql_values[] = $pa_options['exclude'];
}
if (isset($pa_options['is_outstanding']) && (bool) $pa_options['is_outstanding']) {
$pa_options['type'] = 'L';
$va_sql_wheres[] = "(i.loan_return_date IS NULL)";
}
if (!is_array($pa_options['order_status'])) {
if (isset($pa_options['order_status']) && strlen($pa_options['order_status'])) {
$pa_options['order_status'] = array((string) $pa_options['order_status']);
}
}
if (is_array($pa_options['order_status'])) {
foreach ($pa_options['order_status'] as $vn_i => $vs_s) {
if (!in_array($vs_s, $this->getFieldInfo('order_status', 'BOUNDS_CHOICE_LIST'))) {
unset($pa_options['order_status'][$vn_i]);
}
}
if (sizeof($pa_options['order_status'])) {
$va_sql_wheres[] = "(o.order_status IN (?))";
$va_sql_values[] = $pa_options['order_status'];
}
}
if (isset($pa_options['type']) && in_array($pa_options['type'], array('O', 'L'))) {
$va_sql_wheres[] = "(o.order_type = ?)";
$va_sql_values[] = (string) $pa_options['type'];
}
if (isset($pa_options['shipping_method']) && strlen($pa_options['shipping_method'])) {
$va_sql_wheres[] = "(o.shipping_method = ?)";
$va_sql_values[] = (string) $pa_options['shipping_method'];
}
if (isset($pa_options['user_id']) && strlen($pa_options['user_id'])) {
$va_sql_wheres[] = "(t.user_id = ?)";
$va_sql_values[] = (int) $pa_options['user_id'];
$vb_join_transactions = true;
}
if (isset($pa_options['transaction_id']) && strlen($pa_options['transaction_id'])) {
$va_sql_wheres[] = "(o.transaction_id = ?)";
$va_sql_values[] = (int) $pa_options['transaction_id'];
}
if (isset($pa_options['created_on']) && strlen($pa_options['created_on'])) {
if (is_array($va_dates = caDateToUnixTimestamps($pa_options['created_on']))) {
$va_sql_wheres[] = "(o.created_on BETWEEN ? AND ?)";
$va_sql_values[] = (double) $va_dates['start'];
$va_sql_values[] = (double) $va_dates['end'];
}
}
if (isset($pa_options['object_id']) && strlen($pa_options['object_id'])) {
$va_sql_wheres[] = "(i.object_id = ?)";
$va_sql_values[] = (int) $pa_options['object_id'];
}
if (isset($pa_options['loan_checkout_date']) && strlen($pa_options['loan_checkout_date'])) {
if (is_array($va_dates = caDateToUnixTimestamps($pa_options['loan_checkout_date']))) {
$va_sql_wheres[] = "(i.loan_checkout_date BETWEEN ? AND ?)";
$va_sql_values[] = (double) $va_dates['start'];
$va_sql_values[] = (double) $va_dates['end'];
}
}
if (isset($pa_options['loan_due_date']) && strlen($pa_options['loan_due_date'])) {
if (is_array($va_dates = caDateToUnixTimestamps($pa_options['loan_due_date']))) {
$va_sql_wheres[] = "(i.loan_due_date BETWEEN ? AND ?)";
$va_sql_values[] = (double) $va_dates['start'];
$va_sql_values[] = (double) $va_dates['end'];
}
}
if (isset($pa_options['loan_return_date']) && strlen($pa_options['loan_return_date'])) {
if (is_array($va_dates = caDateToUnixTimestamps($pa_options['loan_return_date']))) {
$va_sql_wheres[] = "(i.loan_return_date BETWEEN ? AND ?)";
$va_sql_values[] = (double) $va_dates['start'];
$va_sql_values[] = (double) $va_dates['end'];
}
}
if (isset($pa_options['shipping_date']) && strlen($pa_options['shipping_date'])) {
$o_tep = new TimeExpressionParser();
if ($o_tep->parse($pa_options['shipping_date'])) {
$va_dates = $o_tep->getUnixTimestamps();
$va_sql_wheres[] = "(o.shipping_date BETWEEN ? AND ?)";
$va_sql_values[] = (double) $va_dates['start'];
$va_sql_values[] = (double) $va_dates['end'];
}
}
if (isset($pa_options['shipped_on_date']) && strlen($pa_options['shipped_on_date'])) {
$o_tep = new TimeExpressionParser();
if ($o_tep->parse($pa_options['shipped_on_date'])) {
$va_dates = $o_tep->getUnixTimestamps();
$va_sql_wheres[] = "(o.shipped_on_date BETWEEN ? AND ?)";
$va_sql_values[] = (double) $va_dates['start'];
$va_sql_values[] = (double) $va_dates['end'];
}
}
if (isset($pa_options['search']) && strlen($pa_options['search'])) {
$o_search = new CommerceOrderSearch();
if ($qr_hits = $o_search->search($pa_options['search'])) {
$va_ids = array();
while ($qr_hits->nextHit()) {
$va_ids[] = $qr_hits->get('order_id');
}
if (sizeof($va_ids)) {
$va_sql_wheres[] = "(o.order_id IN (?))";
$va_sql_values[] = $va_ids;
} else {
$va_sql_wheres[] = "(o.order_id = 0)";
}
}
}
$vs_sql_wheres = '';
if (sizeof($va_sql_wheres)) {
$vs_sql_wheres = " AND " . join(" AND ", $va_sql_wheres);
}
// Get item additional fees
$qr_res = $o_db->query($vs_sql = "\n\t \t\tSELECT \n\t \t\t\to.order_id, i.item_id, i.additional_fees\n\t \t\tFROM ca_commerce_orders o\n\t \t\tLEFT JOIN ca_commerce_order_items AS i ON o.order_id = i.order_id\n\t \t\t" . ($vb_join_transactions ? "INNER JOIN ca_commerce_transactions AS t ON t.transaction_id = o.transaction_id" : "") . "\n\t \t\tWHERE\n\t \t\t\to.deleted = 0 {$vs_sql_wheres}\n\t \t\t\t\n\t \t", $va_sql_values);
$va_additional_fee_codes = $this->opo_client_services_config->getAssoc($this->get('order_type') == 'L' ? 'additional_loan_fees' : 'additional_order_item_fees');
$va_order_item_additional_fees = array();
while ($qr_res->nextRow()) {
$va_fees = caUnserializeForDatabase($qr_res->get('additional_fees'));
$vn_fee_total = 0;
foreach ($va_additional_fee_codes as $vs_code => $va_info) {
if (isset($va_fees[$vs_code])) {
$vn_fee_total += (double) $va_fees[$vs_code];
}
}
$va_order_item_additional_fees[$qr_res->get('order_id')] += $vn_fee_total;
}
// Get overdue items (only if type is set to [L]oan)
if (isset($pa_options['type']) && $pa_options['type'] == 'L') {
$qr_res = $o_db->query("\n\t\t\t\tSELECT \n\t\t\t\t\to.order_id, \n\t\t\t\t\tmin(i.loan_checkout_date) loan_checkout_date, min(i.loan_due_date) loan_due_date\n\t\t\t\tFROM ca_commerce_orders o\n\t\t\t\tINNER JOIN ca_commerce_order_items AS i ON o.order_id = i.order_id\n\t \t\t\t" . ($vb_join_transactions ? "INNER JOIN ca_commerce_transactions AS t ON t.transaction_id = o.transaction_id" : "") . "\n\t\t\t\tWHERE\n\t\t\t\t\to.deleted = 0 AND i.loan_return_date IS NULL\n\t\t\t\t\t{$vs_sql_wheres}\n\t\t\t\tGROUP BY o.order_id\n\t\t\t\t\t\n\t\t\t", $va_sql_values);
$va_due_dates = $va_overdue_dates = array();
$vn_t = time();
while ($qr_res->nextRow()) {
$vn_due_date = $qr_res->get('loan_due_date');
if ($vn_due_date > $vn_t) {
$va_due_dates[$qr_res->get('order_id')] = caFormatInterval($vn_due_date - $vn_t, 2);
} else {
$va_overdue_dates[$qr_res->get('order_id')] = caFormatInterval($vn_t - $vn_due_date, 2);
}
}
}
// Get item totals
$qr_res = $o_db->query($vs_sql = "\n\t \t\tSELECT \n\t \t\t\to.*, \n\t \t\t\tsum(i.fee) order_total_item_fees, \n\t \t\t\tsum(i.tax) order_total_item_tax, \n\t \t\t\t((o.shipping_cost) + (i.shipping_cost)) order_total_shipping, \n\t \t\t\t((o.handling_cost) + (i.handling_cost)) order_total_handling, \n\t \t\t\tcount(*) num_items, \n\t \t\t\tmin(i.loan_checkout_date) loan_checkout_date_start, min(i.loan_due_date) loan_due_date_start, min(i.loan_return_date) loan_return_date_start,\n\t \t\t\tmax(i.loan_checkout_date) loan_checkout_date_end, max(i.loan_due_date) loan_due_date_end, max(i.loan_return_date) loan_return_date_end\n\t \t\tFROM ca_commerce_orders o\n\t \t\tLEFT JOIN ca_commerce_order_items AS i ON o.order_id = i.order_id\n\t \t\t" . ($vb_join_transactions ? "INNER JOIN ca_commerce_transactions AS t ON t.transaction_id = o.transaction_id" : "") . "\n\t \t\tWHERE\n\t \t\t\to.deleted = 0 {$vs_sql_wheres}\n\t \t\tGROUP BY o.order_id\n\t \t\tORDER BY\n\t \t\t\to.created_on DESC\n\t \t\t\t\n\t \t", $va_sql_values);
//print $vs_sql."; ".print_r($va_sql_values, true);
$va_orders = array();
while ($qr_res->nextRow()) {
$va_order = $qr_res->getRow();
$va_order['order_number'] = date('mdY', $va_order['created_on']) . '-' . $va_order['order_id'];
// order additional fees
$vn_additional_order_fees = 0;
if (is_array($va_additional_fees = caUnserializeForDatabase($va_order['additional_fees']))) {
foreach ($va_additional_fees as $vs_code => $vn_fee) {
$vn_additional_order_fees += $vn_fee;
}
}
$va_order['order_total'] = $va_order['order_total_item_fees'] + $va_order['order_total_item_tax'] + $va_order['order_total_shipping'] + $va_order['order_total_handling'] + $vn_additional_order_fees + (double) $va_order_item_additional_fees[$qr_res->get('order_id')];
if (isset($va_overdue_dates[$va_order['order_id']])) {
$va_order['is_overdue'] = true;
$va_order['overdue_period'] = $va_overdue_dates[$va_order['order_id']];
} else {
if (isset($va_due_dates[$va_order['order_id']])) {
$va_order['is_overdue'] = false;
$va_order['due_period'] = $va_due_dates[$va_order['order_id']];
}
}
$va_orders[] = $va_order;
}
return $va_orders;
}
*
* CollectiveAccess is distributed in the hope that it will be useful, but
* WITHOUT ANY WARRANTIES whatsoever, including any implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
*
* This source code is free and modifiable under the terms of
* GNU General Public License. (http://www.gnu.org/copyleft/gpl.html). See
* the "license.txt" file for details, or visit the CollectiveAccess web site at
* http://www.CollectiveAccess.org
*
* ----------------------------------------------------------------------
*/
$va_stats = $this->getVar('stats');
$va_panels = $this->getVar('panels');
$ps_daterange = $this->getVar('daterange');
$va_dates = caDateToUnixTimestamps($ps_daterange);
$vs_daterange_proc = caGetLocalizedDateRange($va_dates[0], $va_dates[1], array('timeOmit' => true));
?>
<h1><?php
print _t('Statistics Dashboard');
?>
</h1>
<?php
print caFormTag($this->request, 'Index', 'libraryDashboardOptions', null, 'post', 'multipart/form-data', '_top', array('disableUnsavedChangesWarning' => true));
print _t('Dates') . ': ' . caHTMLTextInput('daterange', array('value' => $ps_daterange, 'class' => 'dateBg'), array('width' => '200px'));
?>
</form>
<br style="clear"/>
<div class="caLibraryDashboardPanel">
/**
* Return number of overdue checkouts that need to be returned
*
* @param string $ps_datetime Options date/time expression to return statistics for. If omitted current checkouts are considered. If provided, only checkouts out in the specified interval are counted.
* @param array $pa_options Array of options. Options include
* db = A Db instance to use for database operations. If omitted a new Db instance will be used. [Default=null]
* @return int
*/
public static function overdueCheckoutUserList($ps_datetime = null, $pa_options = null)
{
if (!($o_db = caGetOption('db', $pa_options, null))) {
$o_db = new Db();
}
if ($ps_datetime && is_array($va_dates = caDateToUnixTimestamps($ps_datetime))) {
$qr_res = $o_db->query("\n\t\t\t\tSELECT DISTINCT u.user_id, u.user_name, u.fname, u.lname, u.email\n\t\t\t\tFROM ca_object_checkouts c\n\t\t\t\tINNER JOIN ca_users AS u ON u.user_id = c.user_id\n\t\t\t\tWHERE\n\t\t\t\t\tc.checkout_date BETWEEN ? AND ?\n\t\t\t\t\tAND\n\t\t\t\t\tc.return_date IS NULL\n\t\t\t\t\tAND\n\t\t\t\t\tc.due_date < ?\n\t\t\t\t\tAND\n\t\t\t\t\tc.deleted = 0\n\t\t\t", array($va_dates[0], $va_dates[1], time()));
} else {
$qr_res = $o_db->query("\n\t\t\t\tSELECT DISTINCT u.user_id, u.user_name, u.fname, u.lname, u.email\n\t\t\t\tFROM ca_object_checkouts c\n\t\t\t\tINNER JOIN ca_users AS u ON u.user_id = c.user_id\n\t\t\t\tWHERE\n\t\t\t\t\tc.checkout_date IS NOT NULL\n\t\t\t\t\tAND\n\t\t\t\t\tc.return_date IS NULL\n\t\t\t\t\tAND\n\t\t\t\t\tc.due_date < ?\n\t\t\t\t\tAND\n\t\t\t\t\tc.deleted = 0\n\t\t\t", array(time()));
}
$va_list = array();
while ($qr_res->nextRow()) {
$va_list[] = $qr_res->getRow();
}
return $va_list;
}
