/**
* get the revenue per day for the Transaction Admin page Reports Tab
*
* @access public
* @param string $period
* @return \stdClass[]
*/
public function get_revenue_per_day_report($period = '-1 month')
{
$sql_date = $this->convert_datetime_for_query('TXN_timestamp', date('Y-m-d H:i:s', strtotime($period)), 'Y-m-d H:i:s', 'UTC');
EE_Registry::instance()->load_helper('DTT_Helper');
$query_interval = EEH_DTT_Helper::get_sql_query_interval_for_offset($this->get_timezone(), 'TXN_timestamp');
$results = $this->_get_all_wpdb_results(array(array('TXN_timestamp' => array('>=', $sql_date)), 'group_by' => 'txnDate', 'order_by' => array('TXN_timestamp' => 'ASC')), OBJECT, array('txnDate' => array('DATE(' . $query_interval . ')', '%s'), 'revenue' => array('SUM(Transaction.TXN_paid)', '%d')));
return $results;
}
/**
* This returns a wpdb->results Array of all DTT month and years matching the incoming query params and grouped by month and year.
* @param array $where_params Array of query_params as described in the comments for EEM_Base::get_all()
* @param string $evt_active_status A string representing the evt active status to filter the months by.
* Can be:
* - '' = no filter
* - upcoming = Published events with at least one upcoming datetime.
* - expired = Events with all datetimes expired.
* - active = Events that are published and have at least one datetime that starts before now and ends after now.
* - inactive = Events that are either not published.
* @return wpdb results array
*/
public function get_dtt_months_and_years($where_params, $evt_active_status = '')
{
$current_time_for_DTT_EVT_start = $this->current_time_for_query('DTT_EVT_start');
$current_time_for_DTT_EVT_end = $this->current_time_for_query('DTT_EVT_end');
switch ($evt_active_status) {
case 'upcoming':
$where_params['Event.status'] = 'publish';
//if there are already query_params matching DTT_EVT_start then we need to modify that to add them.
if (isset($where_params['DTT_EVT_start'])) {
$where_params['DTT_EVT_start*****'] = $where_params['DTT_EVT_start'];
}
$where_params['DTT_EVT_start'] = array('>', $current_time_for_DTT_EVT_start);
break;
case 'expired':
if (isset($where_params['Event.status'])) {
unset($where_params['Event.status']);
}
//get events to exclude
$exclude_query[0] = array_merge($where_params, array('DTT_EVT_end' => array('>', $current_time_for_DTT_EVT_end)));
//first get all events that have datetimes where its not expired.
$event_ids = $this->_get_all_wpdb_results($exclude_query, OBJECT_K, 'Datetime.EVT_ID');
$event_ids = array_keys($event_ids);
if (isset($where_params['DTT_EVT_end'])) {
$where_params['DTT_EVT_end****'] = $where_params['DTT_EVT_end'];
}
$where_params['DTT_EVT_end'] = array('<', $current_time_for_DTT_EVT_end);
$where_params['Event.EVT_ID'] = array('NOT IN', $event_ids);
break;
case 'active':
$where_params['Event.status'] = 'publish';
if (isset($where_params['DTT_EVT_start'])) {
$where_params['Datetime.DTT_EVT_start******'] = $where_params['DTT_EVT_start'];
}
if (isset($where_params['Datetime.DTT_EVT_end'])) {
$where_params['Datetime.DTT_EVT_end*****'] = $where_params['DTT_EVT_end'];
}
$where_params['DTT_EVT_start'] = array('<', $current_time_for_DTT_EVT_start);
$where_params['DTT_EVT_end'] = array('>', $current_time_for_DTT_EVT_end);
break;
case 'inactive':
if (isset($where_params['Event.status'])) {
unset($where_params['Event.status']);
}
if (isset($where_params['OR'])) {
$where_params['AND']['OR'] = $where_params['OR'];
}
if (isset($where_params['DTT_EVT_end'])) {
$where_params['AND']['DTT_EVT_end****'] = $where_params['DTT_EVT_end'];
unset($where_params['DTT_EVT_end']);
}
if (isset($where_params['DTT_EVT_start'])) {
$where_params['AND']['DTT_EVT_start'] = $where_params['DTT_EVT_start'];
unset($where_params['DTT_EVT_start']);
}
$where_params['AND']['Event.status'] = array('!=', 'publish');
break;
}
$query_params[0] = $where_params;
$query_params['group_by'] = array('dtt_year', 'dtt_month');
$query_params['order_by'] = array('DTT_EVT_start' => 'DESC');
$query_interval = EEH_DTT_Helper::get_sql_query_interval_for_offset($this->get_timezone(), 'DTT_EVT_start');
$columns_to_select = array('dtt_year' => array('YEAR(' . $query_interval . ')', '%s'), 'dtt_month' => array('MONTHNAME(' . $query_interval . ')', '%s'), 'dtt_month_num' => array('MONTH(' . $query_interval . ')', '%s'));
return $this->_get_all_wpdb_results($query_params, OBJECT, $columns_to_select);
}
/**
* get the number of registrations per day for the Registration Admin page Reports Tab.
* (doesn't utilize models because it's a fairly specialized query)
* @access public
* @param $period string which can be passed to php's strtotime function (eg "-1 month")
* @return stdClass[] with properties regDate and total
*/
public function get_registrations_per_day_report($period = '-1 month')
{
$sql_date = $this->convert_datetime_for_query('REG_date', date("Y-m-d H:i:s", strtotime($period)), 'Y-m-d H:i:s', 'UTC');
$where = array('REG_date' => array('>=', $sql_date), 'STS_ID' => array('!=', EEM_Registration::status_id_incomplete));
if (!EE_Registry::instance()->CAP->current_user_can('ee_read_others_registrations', 'reg_per_day_report')) {
$where['Event.EVT_wp_user'] = get_current_user_id();
}
EE_Registry::instance()->load_helper('DTT_Helper');
$query_interval = EEH_DTT_Helper::get_sql_query_interval_for_offset($this->get_timezone(), 'REG_date');
$results = $this->_get_all_wpdb_results(array($where, 'group_by' => 'regDate', 'order_by' => array('REG_date' => 'ASC')), OBJECT, array('regDate' => array('DATE(' . $query_interval . ')', '%s'), 'total' => array('count(REG_ID)', '%d')));
return $results;
}
/**
* Get the number of registrations per day including the count of registrations for each Registration Status.
* Note: EEM_Registration::status_id_incomplete registrations are excluded from the results.
*
* @param string $period
*
* @return stdClass[] with properties Registration_REG_date and a column for each registration status as the STS_ID
* (i.e. RAP)
*/
public function get_registrations_per_day_and_per_status_report($period = '-1 month')
{
global $wpdb;
$registration_table = $wpdb->prefix . 'esp_registration';
$event_table = $wpdb->posts;
$sql_date = date("Y-m-d H:i:s", strtotime($period));
//prepare the query interval for displaying offset
$query_interval = EEH_DTT_Helper::get_sql_query_interval_for_offset($this->get_timezone(), 'dates.REG_date');
//inner date query
$inner_date_query = "SELECT DISTINCT REG_date from {$registration_table} ";
$inner_where = " WHERE";
//exclude events not authored by user if permissions in effect
if (!EE_Registry::instance()->CAP->current_user_can('ee_read_others_registrations', 'reg_per_event_report')) {
$inner_date_query .= "LEFT JOIN {$event_table} ON ID = EVT_ID";
$inner_where .= " post_author = " . get_current_user_id() . " AND";
}
$inner_where .= " REG_date >= '{$sql_date}'";
$inner_date_query .= $inner_where;
//start main query
$select = "SELECT DATE({$query_interval}) as Registration_REG_date, ";
$join = '';
$join_parts = array();
$select_parts = array();
//loop through registration stati to do parts for each status.
foreach (EEM_Registration::reg_status_array() as $STS_ID => $STS_code) {
if ($STS_ID === EEM_Registration::status_id_incomplete) {
continue;
}
$select_parts[] = "COUNT({$STS_code}.REG_ID) as {$STS_ID}";
$join_parts[] = "{$registration_table} AS {$STS_code} ON {$STS_code}.REG_date = dates.REG_date AND {$STS_code}.STS_ID = '{$STS_ID}'";
}
//setup the selects
$select .= implode(', ', $select_parts);
$select .= " FROM ({$inner_date_query}) AS dates LEFT JOIN ";
//setup the joins
$join .= implode(" LEFT JOIN ", $join_parts);
//now let's put it all together
$query = $select . $join . ' GROUP BY Registration_REG_date';
//and execute it
$results = $wpdb->get_results($query, ARRAY_A);
return $results;
}
