BadgeOS Reports Plugin

Well, after a bit of digging, I found the BUG in the code. The achievement-reports.php has some bad references to tables that don’t exist when using the report on a Multi-Site setup. Also the search string was not correct in the query. Below is the corrected code in case anyone else needs it.

$sql = "
SELECT     achievement.ID as ID,
				 achievement.post_title as title,
				 (
						 SELECT     COUNT(DISTINCT post.ID)
						 FROM       $wpdb->postmeta as meta
						 INNER JOIN $wpdb->posts as post
												ON post.ID = meta.post_id
									 INNER JOIN $wpdb->users as user
															ON post.post_author = user.ID
									 LEFT JOIN ".$wpdb->base_prefix."bp_groups_members AS groups_members
														ON groups_members.user_id = user.ID
									 LEFT JOIN ".$wpdb->base_prefix."bp_groups AS groups
															ON groups.id = groups_members.group_id
									 LEFT JOIN  $wpdb->usermeta AS usermeta
															ON usermeta.user_id = user.ID
						 WHERE      meta.meta_key = '_badgeos_log_achievement_id'
												AND meta.meta_value = achievement.ID
												AND post.post_title LIKE '%%unlocked%%'
												$group_filter
															$usermeta_filter
															$date_range
				 ) as earned_count,
				 (
						 SELECT     post.post_date
						 FROM       $wpdb->postmeta as meta
						 INNER JOIN $wpdb->posts as post
												ON post.ID = meta.post_id
						 WHERE      meta.meta_key = '_badgeos_log_achievement_id'
												AND meta.meta_value = achievement.ID
												AND post.post_title LIKE '%%unlocked%%'
												$date_range
						 LIMIT      1
				 ) as last_earned_date
FROM       $wpdb->posts as achievement
WHERE      achievement.post_type = '".$report->achievement->post_name."'";

    $report->data = $wpdb->get_results(
        $wpdb->prepare($sql),
        'ARRAY_A'
    );

I tried to use the updated code above but I am still not getting any records to display. I called you a couple of times but no response. I need this resolved ASAP please.

@saltalib

Since you are not getting any records, I would suggest checking the data in your database if possible by using the modified queries, but replacing the php variables with the appropriate values that match up with your database setup and achievements. This way you can confirm the data is there and connected properly. If you get records from that, then it could be an issue with the wpdb setting or something else with the values being passed in.

Hi Reuben! Is there a way I can contact you directly via phone please? My email is altalib[@]gmail.com. Thank you very much!!!

I have the same issue. THe plugin just doesn’t even work. Even with active users it just hangs up. Also the plug in keeps telling me to update and its current. I disabled. Enabled 1 month later and its not telling me it needs to updated it just doesnt work – period.

We finally got it to work by keeping the original SQL query code. But we removed this buddypress code:

   // If Buddy Press is activated, only then display Group Filters
    if( class_exists( 'BP_Groups_Group' ) && bp_is_active( 'groups' ) && class_exists("BadgeOS_Group_Management")){

        // Query filter for tables based on groups
        if(isset($_REQUEST['groups']) && !empty($_REQUEST['groups']))
        $group_filter = "AND groups.id = ".$_REQUEST['groups'];

        // Query filter for tables based on users
        if(badgeos_get_user_role()=="school_admin"){
            $usermeta_filter = "AND usermeta.meta_key = 'school_id' AND usermeta.meta_value =".get_current_user_id();
        }elseif(badgeos_get_user_role()=="author"){
            /*$usermeta_filter = " AND (usermeta.meta_key = 'school_id' AND usermeta.meta_value =".badgeos_get_school_id()."
                               OR usermeta.meta_key = 'teacher_id' AND usermeta.meta_value =".get_current_user_id().")";   */
            $usermeta_filter = " AND usermeta.meta_key = 'teacher_id' AND usermeta.meta_value =".get_current_user_id();
        }

        // Load Groups drop down based on user roles
        $user_role = badgeos_get_user_role( absint( get_current_user_id() ));
        $user_id = ($user_role=="author")?absint( get_current_user_id() ):'';
        $group_meta_query = ($user_role=="school_admin")?array ( array ('key' => 'school_id','value' => absint( get_current_user_id() ) ) ):'';

        $bp_public_groups = groups_get_groups(
            array(
                'user_id' => $user_id,
                'meta_query' => $group_meta_query,
                'orderby' => 'name',
                'order'   => 'ASC'
            )
        );

        $selected_id = isset( $_REQUEST['groups'] ) ? absint( $_REQUEST['groups'] ) : 0;

        if ( $bp_public_groups['total'] > 0 ) {
            $groups_drop_down = '<h3>Groups:</h3>';
            $groups_drop_down .= ' <select name="groups" id="groups" class="achievement-reports" onchange="this.form.submit()" style="max-width: 15%" form="report-filter">';
            $groups_drop_down .= '<option value="">' . __( 'All', 'badgeos-reports' ) . '</option>';
            foreach( $bp_public_groups['groups'] as $group ) {

                if($user_role=="author" && !groups_is_user_admin($user_id,$group->id))
                    continue;

                $groups_drop_down .= '<option value="' . absint( $group->id ) . '" ' . selected( $selected_id, $group->id, false ) . '>' . esc_attr( $group->name ) . '</option>';
            }
            $groups_drop_down .= '</select>';
        }
    }

For the update issue, I had to go inside the code to manually change its version to .3 so it doesn’t give me the update message every 2 minutes. Learning Times never responded to my multiple call attempts. But we finally got it to work on our own.