WordPress.org
Get WordPress

Plugin Directory

Changeset 2148613


Ignore:
Timestamp:
08/30/2019 05:27:40 PM (7 years ago)
Author:
Will Brownsberger
Message:

dashboard count bullet proofing

File:
1 edited

Legend:

Unmodified
Added
Removed

  • wp-issues-crm/trunk/php/entity/class-wic-entity-dashboard.php

    r2147428 r2148613  
    102102        ";
    103103   
     104        // inner select returns unique record foreach post, grouped to avoid double counting if somehow dup meta_keys exist
    104105        $issue_sql = "
    105             SELECT m3.meta_value as user_id, count(m1.meta_id) as issues_open, sum(if(m2.meta_value < NOW() OR m2.meta_value is null, 1, 0) ) as issues_overdue
    106             FROM $post_table p
    107             INNER JOIN $post_meta_table m1 on p.id = m1.post_id
    108             LEFT JOIN $post_meta_table m2 on m2.post_id = m1.post_id and m2.meta_key = 'wic_data_review_date'
    109             LEFT JOIN $post_meta_table m3 on m3.post_id = m1.post_id and m3.meta_key = 'wic_data_issue_staff'
    110             WHERE m1.meta_key = 'wic_data_follow_up_status' AND m1.meta_value = 'open' AND ( post_status = 'publish' OR post_status = 'private' )
    111             GROUP BY m3.meta_value
     106            SELECT user_id, count(post_id) as issues_open, sum(if(review_date < NOW(), 1, 0)) as issues_overdue
     107            FROM
     108                (
     109                SELECT 
     110                    p.id as post_id,
     111                    min(if(m2.meta_value IS NULL, '', m2.meta_value)) as review_date,
     112                    max(if(m3.meta_value is null,0,m3.meta_value)) as user_id
     113                FROM $post_table p
     114                INNER JOIN $post_meta_table m1 on p.id = m1.post_id and m1.meta_key = 'wic_data_follow_up_status'
     115                LEFT JOIN $post_meta_table m2 on m2.post_id = m1.post_id and m2.meta_key = 'wic_data_review_date'
     116                LEFT JOIN $post_meta_table m3 on m3.post_id = m1.post_id and m3.meta_key = 'wic_data_issue_staff'
     117                WHERE m1.meta_value = 'open' AND ( post_status = 'publish' OR post_status = 'private' )
     118                GROUP BY p.ID
     119                ) open_posts
     120            GROUP BY user_id
    112121        ";
    113122
Note: See TracChangeset for help on using the changeset viewer.