On 2 March 2016 at 09:33, [email protected] <[email protected]> wrote:
>
>
> Olá,
>>
>> Pagamentos em um estado pendente não podem ser faturados e são excluídos
>> da sessão "invoice runs section"....
>>
>>
>> Não consigo localizar o erro para corrigi-lo...
>>
>>
>> Podem dar uma luz? Se precisar de alguma info a mais posso fornecer.
>>
>> Obrigado!
>>
>> *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
>>
>>
>>
>>
>>
>
> Só um update:
> ao invés dele mostrar um número X onde iria me apresentar os jobs que tem
> algo para ser billable e faturado, ele mostra o número de jobs que tem algo
> billable mas não ainda faturado.
>
> COALESCE(s.status_type_id, -1)
>
> -1 é "all"
>
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";
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
