Thread: Function fixing - PostgreSQL 9.2
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.
Let me know if I can provide any further information.
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?
Thank you
Function:
CREATE OR REPLACE FUNCTION "public"."g_status_types_jobs" ("client_id" bigint DEFAULT NULL::bigint, "output_order" character varying DEFAULT '-START_TIME'::character varying, "start_time" timestamp without time zone DEFAULT NULL::timestamp without time zone, "end_time" timestamp without time zone DEFAULT NULL::timestamp without time zone, "statuses" "text" DEFAULT NULL::"text", "status_types" "text" DEFAULT NULL::"text", "customer_id" bigint DEFAULT NULL::bigint, "user_id" bigint DEFAULT NULL::bigint, "recurrence_id" bigint DEFAULT NULL::bigint, "search_str" "text" DEFAULT NULL::"text", "unscheduled_is_desired" boolean DEFAULT false, "unassigned_is_desired" boolean DEFAULT false, "templated_status" boolean DEFAULT false, "by_job_ref" boolean DEFAULT false, "by_job_description" boolean DEFAULT false, "by_job_address" boolean DEFAULT false, "by_title" boolean DEFAULT false, "by_status" boolean DEFAULT false, "by_order_number" boolean DEFAULT false, "by_client" boolean DEFAULT false, "by_client_notes" boolean DEFAULT false, "by_billing_client" boolean DEFAULT false, "by_staff" boolean DEFAULT false, "by_notes_description" boolean DEFAULT false, "invoiceable_notes_only" boolean DEFAULT false) RETURNS TABLE("status_type_id" bigint, "jobs_count" bigint, "job_ids" "text", "status_type_data" "text") STABLE
AS $dbvis$
SELECT COALESCE(s.status_type_id, -1) AS status_type_id, CAST(ROUND(SUM(s.jobs_count)) AS BIGINT) AS jobs_count, -- we concatenate the lists from all the status labels. some nullif/substring trickery is required here CONCAT('{', STRING_AGG(NULLIF(SUBSTRING(s.job_ids FROM 2 FOR (CHAR_LENGTH(s.job_ids) - 2)), ''), (CASE WHEN (s.job_ids != '{}') THEN ',' ELSE '' END)), '}') AS job_ids, (CASE WHEN (COALESCE(s.status_type_id, -1) != -1) THEN STRING_AGG(CONCAT( CAST(s.status_id AS TEXT), E'\t', REPLACE(REPLACE(s.status_label, E'\t', '<tab>'), E'\n', '<lf>'), E'\t', CAST(s.status_is_default AS TEXT), E'\t', CAST(s.jobs_count AS TEXT), E'\t', CAST(s.job_ids AS TEXT) ), E'\n') ELSE null END) AS status_type_data FROM public.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) AS s GROUP BY s.status_type_id ;
$dbvis$ LANGUAGE sql
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?
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.David J.
On 1 March 2016 at 11:35, David G. Johnston <david.g.johnston@gmail.com> wrote: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.David J.Thank you David...Can you please show me how it would be with the new changes?
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.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.
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.
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";