>
>
>>> *Question:*
>>>>
>>>> Payments in a Pending state cannot be invoiced and are excluded from
>>>> the Invoice Runs section, but they are showing in the count mechanic.
>>>>
>>>> How can I solve this?
>>>>
>>>
>>> In 9.2 you probably need to convert the count into a conditional sum:
>>>
>>> SELECT sum(CASE WHEN <boolean> THEN 1 ELSE 0 END) FROM data;
>>>
>>> You can probably do the same with count since it excludes nulls.
>>>
>>> SELECT count(NULLIF(invoice_status, 'Pending') FROM invoices;
>>>
>>> 9.4 introduced a FILTER clause for Aggregate Expressions that can do
>>> this much more cleanly and efficiently.
>>>
>>> http://www.postgresql.org/docs/9.4/static/sql-expressions.html
>>>
>>> David J.
>>>
>>>
>>
> I barely scanned your original query - just read the description. I
> don't have the inclination - especially without a self-contained example -
> to make changes to it.
>
> David J.
>
>
>
>
Just an update - Hope this help you to get more data:
There is a number beside each color flag, and a number in the "sub status"
drop down. Instead of "the number of jobs that have billable items that can
be invoiced, it shows the "the number of jobs that have "something"
billable but not yet invoiced."
And since those payments cannot be invoiced alone, they won't show up in
the list.
This is the Query:
> public function search($type=self::SEARCH_TYPE_STATUS_TYPE, &$rawData =
> []){
> if($type === self::SEARCH_TYPE_STATUS_TYPE) {
> $fields = "
> stj.status_type_id,
> stj.jobs_count,
> stj.job_ids,
> (
> SELECT
> array_to_json(array_agg(srcs))
> FROM
> (
> -- property names in the json match column names in g_statuses_jobs()
> SELECT
> (srs.sr[1]::BIGINT) AS status_id,
> (srs.sr[2]::TEXT) AS status_label,
> (srs.sr[3]::BOOLEAN) AS status_is_default,
> (srs.sr[4]::BIGINT) AS jobs_count,
> (srs.sr[5]::JSON) AS job_ids
> FROM
> (SELECT regexp_split_to_array(regexp_split_to_table(stj.status_type_data,
> E'\n'), E'\t')) AS srs(sr)
> ) AS srcs
> ) AS status_type_json
> ";
> $searchFunction =
>
> 'g_status_types_jobs($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25)
> AS stj';
> $factory = new JobSearchStatusSummaryFactory();
> }else{
> $fields = '*';
> $searchFunction =
>
> "g_statuses_jobs($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25)";
> $factory = new JobSearchResultFactory();
> }
> $query = "SELECT
> $fields
> FROM $searchFunction";
