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 - 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;