dba/functions.sql

   1 -- APMS Utility Functions
   2
   3 -- Returns the name of the entity type
   4 CREATE or REPLACE FUNCTION get_entity_type_name( TEXT ) RETURNS TEXT AS '
   5   SELECT CASE
   6             WHEN $1 = ''T'' THEN ''Debtor''
   7             WHEN $1 = ''C'' THEN ''Creditor''
   8             WHEN $1 = ''L'' THEN ''Company''
   9             WHEN $1 = ''P'' THEN ''Property''
  10             WHEN $1 = ''J'' THEN ''Project''
  11             WHEN $1 = ''A'' THEN ''Asset''
  12             ELSE ''UNKNOWN''
  13          END;
  14 ' LANGUAGE SQL IMMUTABLE STRICT;
  15
  16 -- Returns the internal name of the entity type
  17 CREATE or REPLACE FUNCTION get_entity_type_internal_name( TEXT ) RETURNS TEXT AS '
  18   SELECT CASE
  19             WHEN $1 = ''T'' THEN ''tenant''
  20             WHEN $1 = ''C'' THEN ''creditor''
  21             WHEN $1 = ''L'' THEN ''company''
  22             WHEN $1 = ''P'' THEN ''property''
  23             WHEN $1 = ''J'' THEN ''project''
  24             WHEN $1 = ''A'' THEN ''asset''
  25             ELSE ''UNKNOWN''
  26          END;
  27 ' LANGUAGE SQL IMMUTABLE STRICT;
  28
  29 -- Returns a link for the document reference, if possible
  30 CREATE or REPLACE FUNCTION get_docref_link( TEXT, TEXT ) RETURNS TEXT AS '
  31   SELECT CASE
  32             WHEN $2 = ''VCHR'' THEN ''<a href="/view.php?t=voucher&id=''||$1||''" title="View the creditor voucher that created this document">''||$1||''</a>''
  33             WHEN $2 = ''CHEQ'' THEN ''<a href="/view.php?t=cheque&id=''||$1||''" title="View the cheque that created this document">''||$1||''</a>''
  34             WHEN $2 = ''INVC'' THEN ''<a href="/view.php?t=invoice&id=''||$1||''" title="View the debtor invoice that created this document">''||$1||''</a>''
  35             ELSE $1
  36          END;
  37 ' LANGUAGE SQL IMMUTABLE STRICT;
  38
  39
  40 -- Returns the name of the entity, depending on the type
  41 CREATE or REPLACE FUNCTION get_entity_name( TEXT, INT4 ) RETURNS TEXT AS '
  42   SELECT CASE
  43             WHEN $1 = ''T'' THEN (SELECT name FROM tenant WHERE tenantcode=$2)
  44             WHEN $1 = ''C'' THEN (SELECT name FROM creditor WHERE creditorcode=$2)
  45             WHEN $1 = ''L'' THEN (SELECT legalname FROM company WHERE companycode=$2)
  46             WHEN $1 = ''P'' THEN (SELECT name FROM property WHERE propertycode=$2)
  47             WHEN $1 = ''J'' THEN (SELECT name FROM project WHERE projectcode=$2)
  48             ELSE $1 || $2::text
  49          END;
  50 ' LANGUAGE SQL STABLE;
  51
  52 -- Returns the name of the entity, depending on the type
  53 CREATE or REPLACE FUNCTION get_account_name( NUMERIC ) RETURNS TEXT AS $$
  54   SELECT name FROM chartofaccount WHERE accountcode = $1
  55 $$ LANGUAGE SQL IMMUTABLE STRICT;
  56
  57
  58 CREATE or REPLACE FUNCTION previous_balance( TEXT, INT, NUMERIC, INT, INT ) RETURNS NUMERIC AS '
  59 DECLARE
  60   et ALIAS FOR $1;
  61   ec ALIAS FOR $2;
  62   ac ALIAS FOR $3;
  63   mcode ALIAS FOR $4;
  64   years ALIAS FOR $5;
  65   result FLOAT8;
  66 BEGIN
  67   SELECT balance INTO result
  68           FROM accountbalance ab
  69                  JOIN month oldm USING ( monthcode )
  70                  JOIN month newm ON newm.startdate = (oldm.startdate + (years::text||'' years'')::interval)
  71          WHERE entitytype = et AND entitycode = ec AND accountcode = ac
  72            AND newm.monthcode = mcode;
  73   IF NOT FOUND THEN
  74     RETURN 0::numeric;
  75   END IF;
  76   RETURN result;
  77 END;
  78 ' LANGUAGE plpgsql STABLE;
  79
  80
  81
  82 CREATE or REPLACE FUNCTION previous_summary( TEXT, INT, NUMERIC, INT, INT ) RETURNS NUMERIC AS $$
  83 DECLARE
  84   et ALIAS FOR $1;
  85   ec ALIAS FOR $2;
  86   ac ALIAS FOR $3;
  87   mcode ALIAS FOR $4;
  88   years ALIAS FOR $5;
  89   result NUMERIC;
  90   year_end NUMERIC;
  91   mth INT4;
  92 BEGIN
  93   SELECT oldm.monthcode INTO mth FROM month oldm JOIN month newm ON newm.startdate = (oldm.startdate + (years::text||' years')::interval)
  94                         WHERE newm.monthcode = mcode;
  95   IF NOT FOUND THEN
  96     RAISE NOTICE 'No month record found (%).', mcode;
  97     RETURN NULL;
  98   END IF;
  99
 100   SELECT sum(balance) INTO result FROM accountbalance ab
 101          WHERE entitytype = et AND entitycode = ec AND accountcode = ac AND monthcode <= mth;
 102   IF NOT FOUND OR result IS NULL THEN
 103     result := 0::numeric;
 104   END IF;
 105
 106   SELECT accountcode INTO year_end FROM chartofaccount LEFT JOIN accountgroup ag USING(accountgroupcode)
 107          WHERE accountcode = ac AND grouptype = 'P';
 108   IF NOT FOUND THEN
 109     RETURN result;
 110   END IF;
 111
 112   SELECT sum(amount) INTO year_end FROM accttran
 113                LEFT JOIN document USING (batchcode,documentcode)
 114          WHERE entitytype = et AND entitycode = ec AND accountcode = ac AND monthcode = mth
 115            AND documenttype = 'YEND';
 116   IF FOUND AND year_end IS NOT NULL THEN
 117     result := result - year_end;
 118   END IF;
 119
 120   RETURN result;
 121 END;
 122 $$ LANGUAGE plpgsql STABLE;
 123
 124
 125
 126 CREATE or REPLACE FUNCTION year_end_balance( TEXT, INT, INT ) RETURNS SETOF accountbalance AS $$
 127 DECLARE
 128   et ALIAS FOR $1;
 129   ec ALIAS FOR $2;
 130   fy ALIAS FOR $3;
 131   abrec RECORD;
 132   fy_last_month INT;
 133   yend_reversals NUMERIC;
 134   result accountbalance%ROWTYPE;
 135 BEGIN
 136
 137   SELECT max(monthcode) INTO fy_last_month FROM month WHERE financialyearcode = fy;
 138
 139   result.entitytype := et;
 140   result.entitycode := ec;
 141   result.monthcode  := fy_last_month;
 142   result.notecode   := NULL;
 143
 144   FOR abrec IN
 145       SELECT accountcode, sum(balance) AS balance, sum(budget) AS budget, sum(revisedbudget) AS revisedbudget, accountgroup.grouptype
 146           FROM month LEFT JOIN accountbalance USING (monthcode)
 147                RIGHT JOIN chartofaccount USING(accountcode)
 148                LEFT JOIN accountgroup USING(accountgroupcode)
 149           WHERE (month.financialyearcode = fy OR (grouptype != 'P' AND month.financialyearcode < fy) )
 150             AND accountbalance.entitytype = et AND accountbalance.entitycode = ec
 151           GROUP BY accountgroup.grouptype, accountcode
 152   LOOP
 153     SELECT sum(amount) INTO yend_reversals
 154          FROM accttran JOIN document USING (batchcode,documentcode)
 155          WHERE accttran.entitytype = et AND accttran.entitycode = ec AND accttran.accountcode = abrec.accountcode
 156            AND documenttype = 'YEND' AND accttran.monthcode = fy_last_month
 157          GROUP BY accountcode;
 158     result.balance       := coalesce(abrec.balance,0) - coalesce(yend_reversals,0);
 159     result.budget        := coalesce(abrec.budget,0);
 160     result.revisedbudget := coalesce(abrec.revisedbudget,0);
 161     IF result.balance != 0 OR result.budget != 0 OR result.revisedbudget != 0 THEN
 162       result.accountcode   := abrec.accountcode;
 163       RETURN NEXT result;
 164     END IF;
 165   END LOOP;
 166
 167   -- That caught most of them, but for the ones that got zeroed out, and which have no budget
 168   -- or revised budget, the accountbalance records get removed by a trigger, so we need a special
 169   -- pass through looking at anything which had YEND transactions, and which now doesn't exist!
 170   FOR abrec IN
 171     SELECT accttran.accountcode, sum(amount) AS balance, 0::numeric AS budget, 0::numeric AS revisedbudget, ag.grouptype
 172          FROM accttran LEFT JOIN document USING (batchcode,documentcode)
 173                LEFT JOIN chartofaccount USING(accountcode)
 174                LEFT JOIN accountgroup ag USING(accountgroupcode)
 175          WHERE entitytype = et AND entitycode = ec
 176            AND documenttype = 'YEND' AND monthcode = fy_last_month AND grouptype = 'P'
 177            AND NOT EXISTS( SELECT 1 FROM month LEFT JOIN accountbalance USING (monthcode)
 178                                               RIGHT JOIN chartofaccount USING(accountcode)
 179                                                LEFT JOIN accountgroup USING(accountgroupcode)
 180                             WHERE month.financialyearcode = fy AND accountgroup.grouptype = 'P' AND accountbalance.entitytype = accttran.entitytype
 181                               AND accountbalance.entitycode = accttran.entitycode AND accountbalance.accountcode = accttran.accountcode )
 182          GROUP BY ag.grouptype, accountcode
 183   LOOP
 184     result.balance       := 0 - coalesce(abrec.balance,0);
 185     IF result.balance != 0 THEN
 186       result.budget        := 0;
 187       result.revisedbudget := 0;
 188       result.accountcode   := abrec.accountcode;
 189       RETURN NEXT result;
 190     END IF;
 191   END LOOP;
 192
 193   RETURN;
 194 END;
 195 $$ LANGUAGE plpgsql STABLE;
 196
 197
 198 CREATE or REPLACE FUNCTION debtor_statistics( IN tcode INT, IN mfrom INT, IN mto INT,
 199   OUT tenant_name TEXT,
 200   OUT total_paid NUMERIC,
 201   OUT average_month NUMERIC,
 202   OUT times INT,
 203   OUT average_days NUMERIC,
 204   OUT balance NUMERIC,
 205   OUT balance_days INT )
 206  RETURNS RECORD AS '
 207 DECLARE
 208   debtors_control NUMERIC;
 209   start_balance NUMERIC;
 210   total_days INT;
 211   groups RECORD;
 212   group_count NUMERIC;
 213   group_unpaid_days INT;
 214   group_amount NUMERIC;
 215   group_earliest DATE;
 216   group_last DATE;
 217   period_start DATE;
 218   period_end DATE;
 219 BEGIN
 220   SELECT accountcode INTO debtors_control FROM office JOIN officecontrolaccount USING (officecode) where officecontrolaccount.name = ''DEBTORS'' AND office.thisoffice;
 221
 222   SELECT name INTO tenant_name FROM tenant WHERE tenantcode=tcode;
 223
 224   SELECT sum(amount), count(amount) INTO total_paid, times FROM accttran
 225          WHERE entitytype = ''T'' AND entitycode = tcode
 226            AND accountcode = debtors_control
 227            AND monthcode >= mfrom AND monthcode <= mto
 228            AND amount > 0;
 229
 230   SELECT total_paid / count(*), min(startdate), max(enddate) INTO average_month, period_start, period_end
 231           FROM month WHERE monthcode >= mfrom AND monthcode <= mto;
 232
 233   SELECT sum(balance) INTO start_balance FROM accountbalance
 234          WHERE entitytype = ''T'' AND entitycode = tcode
 235            AND accountcode = debtors_control
 236            AND monthcode < mfrom;
 237
 238   total_days := 0;
 239   group_count := 0;
 240   balance_days := 0;
 241
 242   FOR groups IN SELECT DISTINCT closinggroup FROM accttran
 243           WHERE entitytype = ''T'' AND entitycode = tcode
 244             AND accountcode = debtors_control
 245             AND monthcode >= mfrom AND monthcode <= mto
 246   LOOP
 247     group_count := group_count + 1;
 248
 249     SELECT date INTO group_earliest FROM accttran
 250           WHERE entitytype = ''T'' AND entitycode = tcode
 251             AND accountcode = debtors_control
 252             AND closinggroup = groups.closinggroup
 253           ORDER BY date LIMIT 1;
 254
 255     SELECT date INTO group_last FROM accttran
 256           WHERE entitytype = ''T'' AND entitycode = tcode
 257             AND accountcode = debtors_control
 258             AND closinggroup = groups.closinggroup
 259           ORDER BY date DESC LIMIT 1;
 260
 261     total_days := total_days + (group_last - group_earliest);
 262     IF group_last > period_end THEN
 263       group_unpaid_days := period_end - group_earliest;
 264       IF group_unpaid_days > balance_days THEN
 265         balance_days := group_unpaid_days;
 266       END IF;
 267     END IF;
 268
 269   END LOOP;
 270
 271   average_days := CASE WHEN group_count = 0 THEN 0 ELSE total_days::numeric / group_count END;
 272
 273 END;
 274 ' LANGUAGE plpgsql STABLE;
 275
 276 -- Retrieve all phone numbers and nicely format them into a single string
 277 -- FIXME: This assumes local STD dialling is preceded by a '0' and other unwarranted things
 278 CREATE or REPLACE FUNCTION get_person_phones( INT, TEXT, TEXT ) RETURNS TEXT AS '
 279 DECLARE
 280   in_personcode ALIAS FOR $1;
 281   loc_ccode ALIAS FOR $2;
 282   loc_cstd ALIAS FOR $3;
 283   phonelist TEXT;
 284   phone RECORD;
 285 BEGIN
 286   phonelist := '''';
 287   FOR phone IN SELECT phonetype, ccountrycode, cstdcode, number FROM phonedetail
 288           WHERE personcode = in_personcode
 289   LOOP
 290     phonelist := phonelist
 291               || (CASE WHEN phonelist = '''' THEN '''' ELSE '', '' END)
 292               || phone.phonetype || '': ''
 293               || (CASE
 294                     WHEN loc_ccode = phone.ccountrycode THEN
 295                       (CASE WHEN loc_cstd = phone.cstdcode THEN '''' ELSE ''0'' || phone.cstdcode || '' '' END)
 296                     ELSE
 297                       ''+'' || phone.ccountrycode || ''('' || phone.cstdcode || '')''
 298                  END)
 299               || phone.number;
 300   END LOOP;
 301   RETURN phonelist;
 302 END;
 303 ' LANGUAGE plpgsql STRICT;
 304
 305
 306 CREATE or REPLACE FUNCTION get_office_setting( TEXT ) RETURNS TEXT AS $$
 307   SELECT setvalue FROM office JOIN officesettings USING ( officecode ) WHERE setname = $1 AND thisoffice;
 308 $$ LANGUAGE sql STRICT IMMUTABLE;
 309
 310
 311 CREATE or REPLACE FUNCTION date_series( DATE, INT4, INT4 ) RETURNS SETOF DATE AS $$
 312 DECLARE
 313   base_date ALIAS FOR $1;
 314   repeats   ALIAS FOR $2;
 315   period    ALIAS FOR $3;
 316   this_date DATE;
 317   counter INT;
 318   gap  INTERVAL;
 319 BEGIN
 320   this_date := base_date;
 321   counter   := 0;
 322   gap       := (period::text || ' days')::interval;
 323
 324   WHILE counter < repeats LOOP
 325     RETURN NEXT this_date;
 326     counter := counter + 1;
 327     this_date := this_date + gap;
 328   END LOOP;
 329   RETURN;
 330 END;
 331 $$ LANGUAGE plpgsql STRICT IMMUTABLE;
 332
 333
 334 DROP TYPE day_balance_type CASCADE;
 335 CREATE TYPE day_balance_type AS (
 336   day DATE,
 337   balance NUMERIC
 338 );
 339
 340
 341 CREATE or REPLACE FUNCTION daily_balance( CHAR, INT4, NUMERIC, DATE, INT4 ) RETURNS SETOF day_balance_type AS $$
 342 DECLARE
 343   et ALIAS FOR $1;
 344   ec ALIAS FOR $2;
 345   ac ALIAS FOR $3;
 346   base_date ALIAS FOR $4;
 347   days      ALIAS FOR $5;
 348   result   day_balance_type%ROWTYPE;
 349   delta    RECORD;
 350   starting NUMERIC;
 351   mth      INT4;
 352 BEGIN
 353   SELECT monthcode INTO mth FROM month WHERE month.startdate <= base_date AND month.enddate >= base_date;
 354   IF NOT FOUND THEN
 355     RAISE NOTICE 'No month record covering start of period (%).', base_date;
 356     RETURN;
 357   END IF;
 358
 359   SELECT sum(balance) INTO starting FROM accountbalance WHERE entitytype = et AND entitycode = ec AND accountcode = ac AND monthcode < mth;
 360   IF NOT FOUND OR starting IS NULL THEN
 361     starting := 0;
 362   END IF;
 363   result.day := base_date - '1 day'::interval;
 364   result.balance := starting;
 365
 366   SELECT sum(amount) INTO starting FROM accttran WHERE entitytype = et AND entitycode = ec AND accountcode = ac AND monthcode >= mth AND date < base_date;
 367   IF FOUND THEN
 368     result.balance := result.balance + COALESCE(starting,0);
 369   END IF;
 370
 371   FOR delta IN
 372     SELECT date_series, sum(amount) FROM date_series( base_date, days, 1)
 373                               LEFT JOIN accttran ON (accttran.date = date_series AND entitytype = et AND entitycode = ec AND accountcode = ac AND monthcode >= mth)
 374                               GROUP BY date_series ORDER BY date_series
 375   LOOP
 376     result.day := delta.date_series;
 377     result.balance := result.balance + COALESCE(delta.sum, 0);
 378     RETURN NEXT result;
 379   END LOOP;
 380
 381   RETURN;
 382
 383 END;
 384 $$ LANGUAGE plpgsql STRICT IMMUTABLE;