Power User/Developer
This section is for those of you who want to get more from uzERP by writing custom SQL for reports etc or playing with uzLETS.
[Here be Dragons](http://en.wikipedia.org/wiki/Here_be_dragons) - some of this stuff is pretty `toxic` if used incorrectly and could cause tears before bedtime. If things go wrong, you're on your own... you have been warned!!How do I…?
Find the GL period of a transaction given its date?
So you have a transaction, say an invoice or stock movement, with a date and you’d like to sort it by GL period to line up with the accounting periods rather than its calendar month. Here’s how for sales invoices:
SELECT invoice_number, invoice_date, year, period
FROM gl_periods p, si_header i
WHERE p.enddate = ( SELECT min(enddate)
FROM gl_periods z
WHERE z.period `<>` 0 z.enddate >= i.invoice_date
AND p.usercompanyid = z.usercompanyid)
ORDER BY invoice_number
Set up a view for use in Reporting?
By default, views in the public schema are not surfaced in the uzERP Report Writer. If you want to use a standard view for reporting then set up a new view under the reports schema - the example below takes the standard GL transactions view and makes it available for reporting
CREATE OR REPLACE VIEW reports.my_gl_transactions AS
SELECT id, docref, usercompanyid, glaccount_id, glcentre_id, glperiods_id,
trandate, source, "comment", reference, twincurrency_id, twinrate,
"type", twinvalue, "value", account, cost_centre, glperiod, twincurrency,
year_period
FROM gltransactionsoverview;
You can of course omit columns or add WHERE clauses to pare the data down.
Use this technique to create custom views from any data in the system - as long as the view is in the reports schema then it will be available for reporting.
PostgreSQL date sorting?
This looks a bit complicated, but the date_part - date_part(‘year’, invoice_date)||to_char(date_part(‘month’, invoice_date),‘FM09’) - could be encapsulated in a pgsql function.
SELECT date_part('year', invoice_date)||to_char(date_part('month', invoice_date),'FM09') as inv_year_month,
CASE WHEN date_part('year', current_date)||to_char(date_part('month', current_date),'FM09')=date_part('year',
invoice_date)||to_char(date_part('month', invoice_date),'FM09')
THEN 'current = '||net_value
WHEN date_part('year', current_date - interval '1
month')||to_char(date_part('month', current_date - interval '1
month'),'FM09')=date_part('year',
invoice_date)||to_char(date_part('month', invoice_date),'FM09')
THEN '1 Month = '||net_value
WHEN date_part('year', current_date - interval '2
month')||to_char(date_part('month', current_date - interval '2
month'),'FM09')=date_part('year',
invoice_date)||to_char(date_part('month', invoice_date),'FM09')
THEN '2 Month = '||net_value
END
FROM si_header;
Checking the date function, it does handle the differing day lengths in months, as follows:-
SELECT date_part('year', timestamp
'2011-01-04')||to_char(date_part('month', timestamp '2011-01-04'),'FM09')
, date_part('year', timestamp '2011-03-31'- interval '1
month')||to_char(date_part('month', timestamp '2011-03-31'- interval '1
month'),'FM09')
, date_part('year', timestamp '2011-04-01'- interval '1
month')||to_char(date_part('month', timestamp '2011-04-01'- interval '1
month'),'FM09');
In the above, taking one month off 31/03/2011 gives 201102; taking one month off 01/04/2011 gives 201103; i.e. it works on months not days.
SELECT date_part('year', current_date)||to_char(date_part('month',
current_date),'FM09')
, date_part('year', current_date - interval '1
month')||to_char(date_part('month', current_date - interval '1
month'),'FM09')
, date_part('year', current_date - interval '2
month')||to_char(date_part('month', current_date - interval '2
month'),'FM09')
, date_part('year', current_date - interval '3
month')||to_char(date_part('month', current_date - interval '3
month'),'FM09')
, date_part('year', current_date - interval '4
month')||to_char(date_part('month', current_date - interval '4
month'),'FM09')
, date_part('year', current_date - interval '5
month')||to_char(date_part('month', current_date - interval '5
month'),'FM09')
, date_part('year', current_date - interval '6
month')||to_char(date_part('month', current_date - interval '6
month'),'FM09')
, date_part('year', current_date - interval '7
month')||to_char(date_part('month', current_date - interval '7
month'),'FM09')
, date_part('year', current_date - interval '8
month')||to_char(date_part('month', current_date - interval '8
month'),'FM09')
, date_part('year', current_date - interval '9
month')||to_char(date_part('month', current_date - interval '9
month'),'FM09')
, date_part('year', current_date - interval '10
month')||to_char(date_part('month', current_date - interval '10
month'),'FM09')
, date_part('year', current_date - interval '11
month')||to_char(date_part('month', current_date - interval '11
month'),'FM09');
Put transactions into time buckets ?
This applies in something like an aged debt report (shown below) and uses some of the techniques highlighted above:
SELECT
sltransactionsoverview.customer,
sltransactionsoverview.transaction_date,
sltransactionsoverview.id, sltransactionsoverview.transaction_type, sltransactionsoverview.our_reference, sltransactionsoverview.ext_reference,
sltransactionsoverview.gross_value,
sltransactionsoverview.currency,
sltransactionsoverview.rate, sltransactionsoverview.description, sltransactionsoverview.payment_terms,
CASE
WHEN to_char(sltransactionsoverview.transaction_date::timestamp with time zone, 'YYYYMM'::text) = to_char('now'::text::date::timestamp with time zone, 'YYYYMM'::text)
THEN sltransactionsoverview.gross_value
ELSE 0::numeric
END AS current_gross,
CASE
WHEN to_char(sltransactionsoverview.transaction_date::timestamp with time zone, 'YYYYMM'::text) = to_char('now'::text::date - '1 mon'::interval, 'YYYYMM'::text)
THEN sltransactionsoverview.gross_value
ELSE 0::numeric
END AS m1_gross,
CASE
WHEN to_char(sltransactionsoverview.transaction_date::timestamp with time zone, 'YYYYMM'::text) = to_char('now'::text::date - '2 mons'::interval, 'YYYYMM'::text)
THEN sltransactionsoverview.gross_value
ELSE 0::numeric
END AS m2_gross,
CASE
WHEN to_char(sltransactionsoverview.transaction_date::timestamp with time zone, 'YYYYMM'::text) = to_char('now'::text::date - '3 mons'::interval, 'YYYYMM'::text)
THEN sltransactionsoverview.gross_value
ELSE 0::numeric
END AS m3_gross,
CASE
WHEN to_char(sltransactionsoverview.transaction_date::timestamp with time zone, 'YYYYMM'::text) = to_char('now'::text::date - '4 mons'::interval, 'YYYYMM'::text)
THEN sltransactionsoverview.gross_value
ELSE 0::numeric
END AS m4_gross,
CASE
WHEN to_char(sltransactionsoverview.transaction_date::timestamp with time zone, 'YYYYMM'::text) <= to_char('now'::text::date - '5 mons'::interval, 'YYYYMM'::text)
THEN sltransactionsoverview.gross_value
ELSE 0::numeric
END AS m5_gross
FROM sltransactionsoverview
LEFT JOIN slmaster_overview ON sltransactionsoverview.slmaster_id = slmaster_overview.id
LEFT JOIN companyoverview ON slmaster_overview.company_id = companyoverview.id
LEFT JOIN countries ON companyoverview.countrycode = countries.code
WHERE sltransactionsoverview.status::text `<>` 'P'::text
ORDER BY sltransactionsoverview.customer, sltransactionsoverview.transaction_date, sltransactionsoverview.id;