948100 - [dev] Report on IARC ratings

Status: RESOLVED FIXED

(Marketplace Graveyard :: General, defect, P2)

Description

[Karen Ward [:kward]]

Need a report showing which apps have and do not have an IARC rating. Since an IARC rating will be required for all apps effective March 31 AND we are beginning our communications to developers to get developers to take action on obtaining and posting their ratings in concert with the IARC implmentation (planned Dec 17). This will be used to follow up with developers who have not completed the task in Jan, Feb and March.  Request that we  have the report available by Jan 10, 2014.

Comment 1

[Wil Clouser [:clouserw]]

This is just going to be an SQL query we can ask ops to run the day before - no need for any fancy coding.

Comment 2

[Rob Hudson [:robhudson]]

Apps with IARC ratings:

SELECT addons.id, addons.app_slug FROM addons LEFT OUTER JOIN webapps_iarc_info AS iarc ON iarc.addon_id=addons.id WHERE addons.addontype_id=11 AND addons.inactive=false AND addons.status != 11 AND iarc.created IS NOT NULL;

Apps without IARC ratings:

SELECT addons.id, addons.app_slug FROM addons LEFT OUTER JOIN webapps_iarc_info AS iarc ON iarc.addon_id=addons.id WHERE addons.addontype_id=11 AND addons.inactive=false AND addons.status != 11 AND iarc.created IS NULL;

Comment 3

[Karen Ward [:kward]]

(In reply to Rob Hudson [:robhudson] from comment #2)
> Apps with IARC ratings:
> 
> SELECT addons.id, addons.app_slug FROM addons LEFT OUTER JOIN
> webapps_iarc_info AS iarc ON iarc.addon_id=addons.id WHERE
> addons.addontype_id=11 AND addons.inactive=false AND addons.status != 11 AND
> iarc.created IS NOT NULL;
> 
For apps WITHOUT IARC ratings, please include the Developer's email address.
> Apps without IARC ratings:
> 
> SELECT addons.id, addons.app_slug FROM addons LEFT OUTER JOIN
> webapps_iarc_info AS iarc ON iarc.addon_id=addons.id WHERE
> addons.addontype_id=11 AND addons.inactive=false AND addons.status != 11 AND
> iarc.created IS NULL;

Comment 4

[Caitlin Galimidi [:c8o]]

Rob - 
FYI - This report should not be pulled until just before the changes go live. (KWard)

Also - Wil mentioned above Ops running a query - is there any need to open a bug for that? Happy to, just let us know.

Comment 5

[Wil Clouser [:clouserw]]

(In reply to Caitlin Galimidi from comment #4)
> Also - Wil mentioned above Ops running a query - is there any need to open a
> bug for that? Happy to, just let us know.

This bug is fine

Comment 6

[Rob Hudson [:robhudson]]

Updated to include email address of app owner:


Apps with IARC ratings:

SELECT addons.id, addons.app_slug, users.email
FROM addons
LEFT JOIN addons_users ON addons_users.addon_id=addons.id
LEFT JOIN users ON addons_users.user_id=users.id
LEFT OUTER JOIN webapps_iarc_info AS iarc ON iarc.addon_id=addons.id
WHERE addons.addontype_id=11 AND addons.inactive=false AND addons.status != 11 AND iarc.created IS NOT NULL AND addons_users.role=5;

Apps without IARC ratings:

SELECT addons.id, addons.app_slug, users.email
FROM addons
LEFT JOIN addons_users ON addons_users.addon_id=addons.id
LEFT JOIN users ON addons_users.user_id=users.id
LEFT OUTER JOIN webapps_iarc_info AS iarc ON iarc.addon_id=addons.id
WHERE addons.addontype_id=11 AND addons.inactive=false AND addons.status != 11 AND iarc.created IS NULL AND addons_users.role=5;

Comment 7

[Karen Ward [:kward]]

Rob, is this extract completed? Can you get it to Jessilyn so that she can send the email to developers?

Comment 8

[Rob Hudson [:robhudson]]

Karen: Is this format useful for you? http://dpaste.com/1555429/

That's the apps with content ratings as of a few minutes ago. If that works I'll send both with and without via email, or let me know what you prefer. Thanks.

Comment 9

[Rob Hudson [:robhudson]]

(In reply to Rob Hudson [:robhudson] from comment #8)
> Karen: Is this format useful for you? http://dpaste.com/1555429/
> 
> That's the apps with content ratings as of a few minutes ago. If that works
> I'll send both with and without via email, or let me know what you prefer.
> Thanks.

I removed it. I don't want to leak developer emails on a public site. I'll email it.

Comment 10

[Karen Ward [:kward]]

Hi Rob - this extract has 4696 entries for apps without ratings.  We were only expecting about 2800.  David B will clarify scope of apps that should be in the report.

Comment 11

[David Bialer [:dbialer]]

TO clarify the query - as I think we are getting rejected apps. 

We should not include apps that are rejected or any app that would need to be resubmitted.  I think we want approved and pending apps, whether they are active or not.  Across all platforms.

The intent is to weed out all the test/fake apps.

Comment 12

[Andrew Williamson [:eviljeff]]

yep, we only want published; approved but unpublished; pending review.  User disabled apps also, unless re-enabling forces them to get a rating.

Comment 13

[Rob Hudson [:robhudson]]

Attachment: apps-with-iarc.sql

Comment 14

[Rob Hudson [:robhudson]]

Attachment: apps-without-iarc.sql