[Statistics] Demographics undefined subproject count inaccurate

[xlecours]

Describe the bug using raisinbread, the count for the Registered candidates in the 'Undefined' subproject column seems inaccurate. The candidate 300147 should be shown there because it has no subproject defined.

To Reproduce Steps to reproduce the behavior (attach screenshots if applicable):

1. Go to 'https://xavier-dev.loris.ca/statistics/'
2. Click on 'Demographic statistics'
3. Select Site=>Montreal, Project=>Pumpernickel
4. See Undefined column = 0 ;

What did you expect to happen? The count should be 1.

Looking at the database

mysql> select COUNT(DISTINCT(c.CandID)), s.subprojectid from candidate c LEFT JOIN session s ON (c.CandID = s.CandID) WHERE c.RegistrationCenterID = 2 AND c.RegistrationProjectID = 1 AND c.Active = 'Y' AND c.Entity_type = 'Human' GROUP BY s.subprojectid;
+---------------------------+--------------+
| COUNT(DISTINCT(c.CandID)) | subprojectid |
+---------------------------+--------------+
|                         1 |         NULL |
|                        78 |            1 |
|                        85 |            2 |
+---------------------------+--------------+

But the browser id showing

[xlecours]

All candidates that don't have a session also don't show in the sex breakdown.

[racostas]

Hi,

I have been thinking a bit on this. This are my findings so far. It seem the query currently been executed for retrieving the stats is (not exactly, just for better illustration):

select COUNT(DISTINCT(c.CandID)), s.subprojectid from candidate c LEFT JOIN session s ON (c.CandID = s.CandID) WHERE c.RegistrationCenterID = 2 AND s.ProjectID = 1 AND c.Active = 'Y' AND c.Entity_type = 'Human' GROUP BY s.subprojectid;

in place of:

select COUNT(DISTINCT(c.CandID)), s.subprojectid from candidate c LEFT JOIN session s ON (c.CandID = s.CandID) WHERE c.RegistrationCenterID = 2 AND c.RegistrationProjectID = 1 AND c.Active = 'Y' AND c.Entity_type = 'Human' GROUP BY s.subprojectid;

The difference in the counting is because in the current implement query the candidates are been taken into account for the project they have a session (which in my yet limited understanding of LORIS correspond to a visit?) and not for the project they were registered. This is the case for the subject in question. It was registered in project with id 2 but don’t have a session in this project (for this particular the subject doesn’t have a session at all).

My experience in LORIS is not yet that extensive so I’m wondering if we should count the subjects for the project they were registered or for the projects they have a session?

Regards.

[ridz1208]

@racostas your fix is a good idea please make a PR

[racostas]

outdated