eventespresso / event-espresso-core

     * @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;

    }

    /**

     *        get the number of registrations per event  for the Registration Admin page Reports Tab

     *

     * @access        public

     * @param $period string which can be passed to php's strtotime function (eg "-1 month")

     * @return stdClass[] each with properties event_name, reg_limit, and total

     */

    public function get_registrations_per_event_report($period = '-1 month')

    {

        $date_sql = $this->convert_datetime_for_query('REG_date', date("Y-m-d H:i:s", strtotime($period)),

     * @return stdClass[] with properties `Registration_Event` and a column for each registration status as the STS_ID

     *                    (i.e. RAP)

     */

    public function get_registrations_per_event_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));

        //inner date query

        $inner_date_query = "SELECT DISTINCT EVT_ID, 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;

        //build main query

        $select = "SELECT Event.post_title as Registration_Event, ";

        $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.EVT_ID = dates.EVT_ID AND $STS_code.STS_ID = '$STS_ID' AND $STS_code.REG_date = dates.REG_date";

        }

        //setup the selects

        $select .= implode(', ', $select_parts);

        $select .= " FROM ($inner_date_query) AS dates LEFT JOIN $event_table as Event ON Event.ID = dates.EVT_ID LEFT JOIN ";

        //setup remaining joins

        $join .= implode(" LEFT JOIN ", $join_parts);

        //now put it all together

        $query = $select . $join . ' GROUP BY Registration_Event';

        //and execute

        $results = $wpdb->get_results($query, ARRAY_A);

        return $results;

    }

    /**

     * Returns the EE_Registration of the primary attendee on the transaction id provided

     *

     * @param int $TXN_ID

     * @return EE_Registration

     */

    public function get_primary_registration_for_transaction_ID($TXN_ID = 0)

    {

        if ( ! $TXN_ID) {