git.roojs.org Git - Pman.Xtuple/blob - pgsql/x-timewarp.sql

   1
   2
   3
   4 -- note this does now not drop the timewarp tables..
   5
   6 -- The time warp schema is for reporting - to make SG accounts look like HK...
   7
   8 -- DROP SCHEMA timewarp;
   9 -- SELECT timewarp.yearperiod_fill_all();
  10 -- SELECT timewarp.period_fill_all();
  11
  12 -- DELETE FROM timewarp.trialbal;
  13 -- SELECT timewarp.trialbal_fill_all();
  14
  15 CREATE SCHEMA timewarp;
  16
  17
  18 DROP VIEW   IF EXISTS  timewarp.trialbal ;
  19 DROP VIEW IF EXISTS timewarp.period;
  20 DROP VIEW  IF EXISTS   timewarp.yearperiod ;
  21
  22
  23
  24 DROP VIEW   IF EXISTS  timewarp.trialbalview ;
  25 DROP VIEW  IF EXISTS   timewarp.periodview;
  26 DROP VIEW  IF EXISTS   timewarp.yearperiodview ;
  27
  28
  29 -- flat copies..
  30 --CREATE OR REPLACE VIEW timewarp.accnt AS SELECT * FROM public.accnt;
  31
  32 -- DROP BOTH togetyer!
  33
  34
  35
  36 CREATE  VIEW timewarp.yearperiodview AS
  37
  38     SELECT
  39          yearperiod_id ,
  40          to_char(yearperiod_start , 'YYYY-05-DD')::date as yearperiod_start,
  41          to_char(yearperiod_end  , 'YYYY-04-DD')::date as yearperiod_end,
  42          yearperiod_closed
  43         FROM public.yearperiod;
  44
  45
  46 CREATE  VIEW timewarp.periodview AS
  47
  48     SELECT
  49         period_id,
  50         period_start,
  51         period_end,
  52         period_closed,
  53         period_freeze,
  54         period_initial,
  55         period_name,
  56
  57         (SELECT yearperiod_id FROM
  58             timewarp.yearperiodview
  59             WHERE
  60             period_start >= yearperiod_start AND
  61             period_start < yearperiod_end
  62         LIMIT 1) as  period_yearperiod_id,
  63
  64         floor((((period_number + 4) % 12) ) / 3) +1 as period_quarter ,
  65         ((period_number + 4) % 12) +1 as period_number
  66
  67     FROM
  68         public.period;
  69
  70
  71
  72 -- SELECT * from yearperiod  ORDER BY yearperiod_start ASC;
  73
  74
  75
  76
  77 -- find the qty available..
  78 CREATE OR REPLACE FUNCTION timewarp.trailbal_getvalue(integer, date, text)
  79     RETURNS  numeric(20, 2)
  80
  81 AS $BODY$
  82 DECLARE
  83     i_accnt_id ALIAS FOR $1;
  84     i_date ALIAS FOR $2;
  85     i_b_or_e ALIAS FOR $3;
  86     v_ret  numeric(20,2);
  87     i_yearstart DATE;
  88
  89 BEGIN
  90
  91     SELECT
  92             yearperiod_start
  93             INTO
  94             i_yearstart
  95         FROM
  96             timewarp.yearperiod
  97         WHERE
  98              yearperiod_start <= i_date
  99             AND
 100              yearperiod_end >= i_date
 101         ORDER BY
 102             yearperiod_start DESC
 103         LIMIT 1;
 104
 105
 106     --IF i_date  = i_yearstart AND
 107     --    i_b_or_e = 'S'
 108     --    THEN
 109     --    RETURN 0.00;
 110     --END IF;
 111
 112
 113     SELECT
 114             SUM(trialbal_credits - trialbal_debits)
 115         INTO
 116             v_ret
 117         FROM
 118             public.trialbal
 119         LEFT JOIN
 120             public.period
 121         ON
 122             trialbal_period_id = period_id
 123         where
 124             trialbal_accnt_id = i_accnt_id
 125         AND
 126             CASE WHEN
 127                 i_b_or_e = 'S'
 128             THEN
 129                 period_start < i_date
 130             ELSE
 131                 period_end <= i_date
 132             END
 133
 134         AND
 135
 136
 137              period_start >= i_yearstart;
 138
 139
 140
 141
 142     RETURN COALESCE(v_ret,0.00);
 143
 144             END;
 145 $BODY$
 146   LANGUAGE plpgsql VOLATILE
 147   COST 100;
 148
 149 ALTER FUNCTION  timewarp.trailbal_getvalue(integer, date, text)
 150   OWNER TO admin;
 151
 152
 153
 154 -- trailbal..
 155
 156 DROP VIEW timewarp.trialbalview ;
 157 CREATE  VIEW timewarp.trialbalview AS
 158
 159     SELECT
 160
 161         trialbal_id,
 162         trialbal_period_id,
 163         trialbal_accnt_id ,
 164
 165         -- begining and ending -- are the same for ASSETS and LIABILITIES
 166         CASE
 167             WHEN
 168                 accnt_type IN ('A','L','Q')
 169             THEN
 170
 171                 trialbal_beginning
 172             ELSE
 173                 -- if it's not a asset liability
 174                 -- then it 0's out at the start of each financial year.
 175                 timewarp.trailbal_getvalue( accnt_id, period_start , 'S')
 176              END
 177             as trialbal_beginning,
 178
 179
 180         CASE
 181             WHEN
 182                 accnt_type IN ('A','L','Q')
 183             THEN
 184                 trialbal_ending
 185
 186             ELSE
 187                 timewarp.trailbal_getvalue( accnt_id, period_end, 'E')
 188
 189             END
 190
 191             as trialbal_ending,
 192
 193         trialbal_credits ,
 194         trialbal_debits ,
 195         trialbal_dirty ,
 196
 197         -- only 208 ?
 198         -- FIXME -- this might need some more thought...
 199         trialbal_yearend
 200
 201     FROM
 202         public.trialbal
 203     LEFT JOIN
 204         public.accnt
 205     ON
 206         trialbal_accnt_id = accnt_id
 207     LEFT JOIN
 208         public.period
 209     ON
 210         trialbal_period_id = period_id ;
 211
 212
 213 -- select period_start, trialbal_beginning , trialbal_ending, trialbal_credits, trialbal_debits  from timewarp.trialbalview left join timewarp.period on trialbal_period_id  = period_id  where trialbal_accnt_id = 121 order by period_start ASC;
 214
 215 --  -- slase id = 135   - type ='R'
 216 --selecT
 217 --    trialbal_beginning,trialbal_ending, period_start
 218 --    from timewarp.trialbal LEFT JOIN period ON trialbal_period_id = period_id where trialbal_accnt_id = 135 order by period_start ASC;
 219 --
 220 --
 221 --
 222 --selecT
 223 --    trialbal_beginning,trialbal_ending, period_start, trialbal_yearend
 224 --    from  trialbal LEFT JOIN period ON trialbal_period_id = period_id where trialbal_accnt_id = 208 order by period_start ASC;
 225 --
 226
 227
 228 -- DROP TABLE IF EXISTS timewarp.period;
 229 CREATE TABLE timewarp.period
 230 (
 231     period_id integer NOT NULL,
 232     period_start date,
 233     period_end date,
 234     period_closed boolean,
 235     period_freeze boolean,
 236     period_initial boolean DEFAULT false,
 237     period_name text,
 238     period_yearperiod_id integer,
 239     period_quarter integer,
 240     period_number integer NOT NULL
 241 )
 242 WITH (
 243   OIDS=FALSE
 244 );
 245
 246 ALTER TABLE timewarp.period
 247   OWNER TO admin;
 248 GRANT ALL ON TABLE timewarp.period TO admin;
 249 GRANT ALL ON TABLE timewarp.period TO xtrole;
 250 COMMENT ON TABLE timewarp.period
 251   IS 'Store financial reports Accounting Periods information.';
 252
 253
 254 --  DROP TABLE IF EXISTS timewarp.yearperiod;
 255 CREATE TABLE timewarp.yearperiod
 256 (
 257     yearperiod_id integer NOT NULL,
 258     yearperiod_start date NOT NULL,
 259     yearperiod_end date NOT NULL,
 260     yearperiod_closed boolean NOT NULL DEFAULT false
 261 )
 262 WITH (
 263   OIDS=FALSE
 264 );
 265 ALTER TABLE timewarp.yearperiod
 266   OWNER TO admin;
 267 GRANT ALL ON TABLE timewarp.yearperiod TO admin;
 268 GRANT ALL ON TABLE timewarp.yearperiod TO xtrole;
 269 COMMENT ON TABLE timewarp.yearperiod
 270   IS 'Store financial reports Accounting Year Periods information.';
 271
 272 -- DROP TABLE IF EXISTS timewarp.trialbal;
 273 CREATE TABLE timewarp.trialbal
 274 (
 275     trialbal_id integer NOT NULL,
 276     trialbal_period_id integer,
 277     trialbal_accnt_id integer,
 278     trialbal_beginning numeric(20,2),
 279     trialbal_ending numeric(20,2),
 280     trialbal_credits numeric(20,2),
 281     trialbal_debits numeric(20,2),
 282     trialbal_dirty boolean,
 283     trialbal_yearend numeric(20,2) NOT NULL DEFAULT 0.00
 284 )
 285 WITH (
 286   OIDS=FALSE
 287 );
 288
 289
 290 ALTER TABLE timewarp.trialbal
 291   OWNER TO admin;
 292 GRANT ALL ON TABLE timewarp.trialbal TO admin;
 293 GRANT ALL ON TABLE timewarp.trialbal TO xtrole;
 294 COMMENT ON TABLE timewarp.trialbal
 295   IS 'Store financial reports Trial Balance information.';
 296
 297    -- testing..
 298 --SET search_path TO timewarp,public;
 299 --SELECT * FROM public.financialReport(14 , ARRAY[ 112] , 'Y' , true, NULL )
 300 --
 301
 302 GRANT ALL ON SCHEMA timewarp TO admin;
 303 GRANT ALL ON SCHEMA timewarp TO xtrole;
 304 GRANT ALL ON  timewarp.trialbalview TO admin;
 305 GRANT ALL ON   timewarp.trialbalview TO xtrole;
 306 GRANT ALL ON  timewarp.periodview TO admin;
 307 GRANT ALL ON   timewarp.periodview TO xtrole;
 308 GRANT ALL ON  timewarp.yearperiodview TO admin;
 309 GRANT ALL ON   timewarp.yearperiodview TO xtrole;
 310
 311
 312
 313
 314
 315
 316
 317
 318
 319
 320
 321
 322
 323
 324
 325
 326
 327
 328
 329
 330
 331