/** * 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; }