[OPS] Editorial Activity statistics are inaccurate

[alexxxmendonca]

Describe the bug
The statistics that are given in the Editorial Activity section are inaccurate.

Taking by example SciELO Preprints, this is what is shown today as overall Editorial Activity:

• It says 1856 submissions received but our Archive tab says more than that:
  
• It says 0 submissions accepted even though we currently have over 1400 preprints posted
• It says 1041 submissions "published" (correct wording should be "posted") even though we currently have over 1400 preprints posted

What application are you using?
OPS 3.3.0.8

PRs
stable-3_3_0

• pkp/pkp-lib#7709 Added skipped submissions to complement the editorial activity #7726
• pkp/pkp-lib#7709 Submodule update ##jonasraoni/bugfix/stable-3_3_0/7709-fix-statistics## ojs#3311
• pkp/pkp-lib#7709 Submodule update ##jonasraoni/bugfix/stable-3_3_0/7709-fix-statistics## omp#1072
• pkp/pkp-lib#7709 Added skipped submissions to complement the editorial activity ops#239

main

• pkp/pkp-lib#7709 Added skipped submissions to complement the editorial activity #7731
• pkp/pkp-lib#7709 Added skipped submissions to complement the editorial activity ojs#3312
• pkp/pkp-lib#7709 Added skipped submissions to complement the editorial activity omp#1073
• pkp/pkp-lib#7709 Added skipped submissions to complement the editorial activity ops#241

[jonasraoni]

I've just checked an OPS 3.3.0.8 instance and I can confirm the results are inaccurate.

[mfelczak]

@ajnyga is this something that you'll have a chance to tackle shortly? We have a hosted client affected as well and can provide a PR next week if it might be helpful.

[ajnyga]

@jonasraoni did you check OJS as well? Just seems a bit weird that for example the submissions received stat would be incorrect in OPS since that is mostly dealt with in the pkp-lib probably
maybe the args are not passed correctly...

[ajnyga]

@mfelczak of course go ahead, I will not have time to look at this in more detail before next week anyway

[NateWr]

Is it possible that there are submissions that were "declined" before the author finished submitting them? The stats code excludes submissions where the submission_progress column is not 0 (which it should be after the author finishes submitting).

There is also some code that excludes some cases that might arise from imported submissions:

pkp-lib/classes/services/queryBuilders/PKPStatsEditorialQueryBuilder.inc.php

Lines 368 to 384 in 5f17a8b

	// Exclude submissions when the date_submitted is later
	// than the first date_published. This prevents imported
	// submissions from being counted in editorial stats.
	$q->leftJoin('publications as pi', function ($q) {
	$q->where('pi.publication_id', function ($q) {
	$q->from('publications as pi2')
	->where('pi2.submission_id', '=', DB::raw('s.submission_id'))
	->where('pi2.status', '=', PKPSubmission::STATUS_PUBLISHED)
	->orderBy('pi2.date_published', 'ASC')
	->limit(1)
	->select('pi2.publication_id');
	});
	})
	->where(function ($q) {
	$q->whereNull('pi.date_published')
	->orWhere('s.date_submitted', '<', DB::raw('pi.date_published'));
	});

[alexxxmendonca]

Is it possible that there are submissions that were "declined" before the author finished submitting them?

Not in our case.

[ajnyga]

Has to be something in pkp-lib. This is from the current OJS main branch (I only got three submissions because the tests seem to crash locally in XAMPP sometimes)

Three submissions, two in editing and one published

The statistics say two submissions received.

Or is the published submission left out of total received with a reason?

[ajnyga]

At least applyCustomRange seems to be giving a javascript error. Maybe the daterange is not passed on correctly.

[jonasraoni]

I just started taking a look on it =]

About the submissions received, here's the culprit on my use case:

pkp-lib/classes/services/queryBuilders/PKPStatsEditorialQueryBuilder.inc.php

Line 383 in 480c151

->orWhere('s.date_submitted', '<', DB::raw('pi.date_published'));

According to the comment above this line, looks like this condition is trying to ignore imported submissions.

1. Does it make sense to not count imported items? Without context, it sounds a bit counterintuitive for me... If yes, then I think it would be better to have a flag for this.
2. The condition is currently error-prone due to the different data types (publications.date_published is a date and submissions.date_submitted is a datetime). This way if we submit something at yyyy-mm-dd-10:00 and publish it on the same day, the final condition will end up like yyyy-mm-dd-10:00 < yyyy-mm-dd-00:00, which will ignore a valid record 🤔

[jonasraoni]

About the accepted count... The code is looking for accepted editorial decisions, but no decision is created when a preprint is published (only the rejected ones receive a decision). I guess it should be this way, but just to confirm, isn't it needed to have an accepted decision?

[ajnyga]

Yes, we actually discussed this with Nate last week. It has just been left there when OPS was created based on OJS. There are currently not "accepted" editorial decision in OPS that the count could be based on. You can just decline a submission or post it online. I think you could remove that with the same PR?

[ajnyga]

I think it makes sense to ignore imported submissions. The reason for counting accepted/rejected is to get the percentage for approved submissions. The imported articles are all accepted so it affects the percentage in the wrong way because you never import rejected articles from those years.

BUT I think the problem with the accepted/rejected count is also that it counts all types of submissions. Not just reviewed articles. So an editorial is regarded as an accepted submission. Or at least I think it does. We would need a content type taxonomy to the system...

[NateWr]

The code is looking for accepted editorial decisions, but no decision is created when a preprint is published (only the rejected ones receive a decision)

In the main branch, I am removing these stats as part of #7265. If you want to remove them for stable-3_3_0, please do so in a separate commit that won't be forward-ported to main.

this condition is trying to ignore imported submissions

You can see the original bug report at #6011. This was well-tested against real data, so we do want to keep this in.

if we submit something at yyyy-mm-dd-10:00 and publish it on the same day, the final condition will end up like yyyy-mm-dd-10:00 < yyyy-mm-dd-00:00, which will ignore a valid record

That's a good point, and a use-case that only exists in OPS. I think we could safely change this condition to date_submitted > date_published without impacting OJS/OMP. But I'd love to see this tested against a real journal's data that has imported submissions, to make sure we aren't accidentally reintroducing the problem in #6011.

[jonasraoni]

@NateWr The PR is huge, if you can save me some time, confirm if the following is the way to go:

• Remove/omit the accepted and declined statistics in ops/3.3.0
• Do not add a declined decision
• Leave the accepted decision

Import:
What about adding an is_imported field to the submission? And in the report, I think it would be useful to add a note stating that N submissions were imported and discarded from the statistics.

Issue with filter:
I'll take a look on the #6011 and import a submission to see the outcome :)

[alexxxmendonca]

Remove/omit the accepted and declined statistics in ops/3.3.0

Hey all, I'm not sure I am following the conversation (forgive me if I'm not), but we do want to know the number of accepted and rejected submissions.

[NateWr]

The PR is huge

Which PR?

Remove/omit the accepted and declined statistics in ops/3.3.0

No, only remove the accepted statistics. In OPS, accepted = published, so we should only see numbers for Received, Declined, and Published.

Do not add a declined decision / Leave the accepted decision

Maybe I'm misunderstanding something, but you shouldn't need to touch the decisions at all. The one-line change to the conditional expression you mentioned should be enough to include preprints published on the same day as they are submitted.

we do want to know the number of accepted and rejected submissions

Yes, but in OPS it will be called Published and Declined.

[NateWr]

Sorry @jonasraoni, I realize you meant the PR for #7265. I didn't mean for you to crawl through that. Just reducing the stats to Received, Declined and Published in stable-3_3_0 should do the trick.

[alexxxmendonca]

Thanks @NateWr for the clarification!

[NateWr]

What about adding an is_imported field to the submission?

Can we talk about this as part of #7446? If we add something like this, I don't want to do it as part of this little issue. I'd prefer to think through how something like this might be used (or not) throughout the entire system. We might, for example, need to make distinctions between imports for different purposes (back catalogue vs. current but doesn't use the workflow).

[jonasraoni]

Ok!

Maybe I'm misunderstanding something, but you shouldn't need to touch the decisions at all

I just wanted to confirm if this decision part is fine, at a first sight I expected to have an accepted and a declined decision =]

[jonasraoni]

I've rebased and merged the remaining PRs.