Closed Bug 948100 Opened 11 years ago Closed 10 years ago

[dev] Report on IARC ratings

Categories

(Marketplace Graveyard :: General, defect, P2)

Avenir
x86_64
Windows 7
defect

Tracking

(Not tracked)

RESOLVED FIXED

People

(Reporter: kward, Unassigned)

References

Details

Attachments

(2 files)

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.
Assignee: nobody → robhudson.mozbugs
Priority: -- → P2
This is just going to be an SQL query we can ask ops to run the day before - no need for any fancy coding.
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;
(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;
Blocks: 941780
No longer blocks: 928533
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.
Flags: needinfo?(robhudson.mozbugs)
(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
Flags: needinfo?(robhudson.mozbugs)
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;
Rob, is this extract completed? Can you get it to Jessilyn so that she can send the email to developers?
Flags: needinfo?(robhudson.mozbugs)
Blocks: 960788
Blocks: 960791
Blocks: 960792
Blocks: 960793
Blocks: 960794
Blocks: 960796
Blocks: 960798
Blocks: 960799
Blocks: 960801
Blocks: 960802
Blocks: 960803
Blocks: 960804
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.
Flags: needinfo?(robhudson.mozbugs)
(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.
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.
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.
yep, we only want published; approved but unpublished; pending review.  User disabled apps also, unless re-enabling forces them to get a rating.
Depends on: 975579
Blocks: 996086
Blocks: 996087
Blocks: 996088
Blocks: 996089
Assignee: robhudson.mozbugs → nobody
Blocks: 1015283
Status: NEW → RESOLVED
Closed: 10 years ago
Resolution: --- → FIXED
You need to log in before you can comment on or make changes to this bug.

Attachment

General

Created:
Updated:
Size: