eventespresso / event-espresso-core

     * @throws EE_Error

     *                    (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

        return $wpdb->get_results($query, ARRAY_A);

    }

    /**

     * @throws EE_Error

     *                    (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

        return $wpdb->get_results($query, ARRAY_A);

    }

    /**