


-- note this does now not drop the timewarp tables..

-- The time warp schema is for reporting - to make SG accounts look like HK...

-- DROP SCHEMA timewarp;
-- SELECT timewarp.yearperiod_fill_all();
-- SELECT timewarp.period_fill_all();

-- DELETE FROM timewarp.trialbal;
-- SELECT timewarp.trialbal_fill_all();

CREATE SCHEMA timewarp;


DROP VIEW   IF EXISTS  timewarp.trialbal ;
DROP VIEW IF EXISTS timewarp.period;
DROP VIEW  IF EXISTS   timewarp.yearperiod ;



DROP VIEW   IF EXISTS  timewarp.trialbalview ;
DROP VIEW  IF EXISTS   timewarp.periodview;
DROP VIEW  IF EXISTS   timewarp.yearperiodview ;


-- flat copies..
--CREATE OR REPLACE VIEW timewarp.accnt AS SELECT * FROM public.accnt;

-- DROP BOTH togetyer!



CREATE  VIEW timewarp.yearperiodview AS

    SELECT
         yearperiod_id ,
         to_char(yearperiod_start , 'YYYY-05-DD')::date as yearperiod_start,
         to_char(yearperiod_end  , 'YYYY-04-DD')::date as yearperiod_end,
         yearperiod_closed    
        FROM public.yearperiod;

 
CREATE  VIEW timewarp.periodview AS

    SELECT
        period_id,
        period_start,
        period_end,
        period_closed,
        period_freeze,
        period_initial,
        period_name,
        
        (SELECT yearperiod_id FROM
            timewarp.yearperiodview
            WHERE
            period_start >= yearperiod_start AND
            period_start < yearperiod_end
        LIMIT 1) as  period_yearperiod_id,
        
        floor((((period_number + 4) % 12) ) / 3) +1 as period_quarter ,
        ((period_number + 4) % 12) +1 as period_number
        
    FROM
        public.period;
        


-- SELECT * from yearperiod  ORDER BY yearperiod_start ASC;
    



-- find the qty available.. 
CREATE OR REPLACE FUNCTION timewarp.trailbal_getvalue(integer, date, text)
    RETURNS  numeric(20, 2) 
    
AS $BODY$
DECLARE
    i_accnt_id ALIAS FOR $1;
    i_date ALIAS FOR $2;
    i_b_or_e ALIAS FOR $3;
    v_ret  numeric(20,2);
    i_yearstart DATE;
      
BEGIN
    
    SELECT
            yearperiod_start
            INTO
            i_yearstart
        FROM
            timewarp.yearperiod
        WHERE
             yearperiod_start <= i_date 
            AND
             yearperiod_end >= i_date
        ORDER BY
            yearperiod_start DESC
        LIMIT 1;
        
    
    --IF i_date  = i_yearstart AND
    --    i_b_or_e = 'S'
    --    THEN
    --    RETURN 0.00;
    --END IF;
    
    
    SELECT
            SUM(trialbal_credits - trialbal_debits)
        INTO
            v_ret
        FROM
            public.trialbal
        LEFT JOIN
            public.period
        ON
            trialbal_period_id = period_id
        where
            trialbal_accnt_id = i_accnt_id
        AND
            CASE WHEN
                i_b_or_e = 'S'
            THEN
                period_start < i_date
            ELSE
                period_end <= i_date
            END
            
        AND
        
             
             period_start >= i_yearstart;
            
        
            
            
    RETURN COALESCE(v_ret,0.00);
            
            END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  timewarp.trailbal_getvalue(integer, date, text)
  OWNER TO admin;
          
       

-- trailbal..

DROP VIEW timewarp.trialbalview ;
CREATE  VIEW timewarp.trialbalview AS

    SELECT 
    
        trialbal_id,
        trialbal_period_id,
        trialbal_accnt_id ,
        
        -- begining and ending -- are the same for ASSETS and LIABILITIES
        CASE
            WHEN
                accnt_type IN ('A','L','Q')
            THEN
        
                trialbal_beginning
            ELSE
                -- if it's not a asset liability
                -- then it 0's out at the start of each financial year.
                timewarp.trailbal_getvalue( accnt_id, period_start , 'S')
             END   
            as trialbal_beginning,
        
        
        CASE
            WHEN
                accnt_type IN ('A','L','Q')
            THEN
                trialbal_ending
            
            ELSE
                timewarp.trailbal_getvalue( accnt_id, period_end, 'E')
            
            END
            
            as trialbal_ending,
         
        trialbal_credits ,
        trialbal_debits ,
        trialbal_dirty ,
        
        -- only 208 ?
        -- FIXME -- this might need some more thought...
        trialbal_yearend
    
    FROM
        public.trialbal
    LEFT JOIN
        public.accnt
    ON
        trialbal_accnt_id = accnt_id
    LEFT JOIN
        public.period
    ON
        trialbal_period_id = period_id ;
 

-- 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;
    
--  -- slase id = 135   - type ='R'      
--selecT
--    trialbal_beginning,trialbal_ending, period_start
--    from timewarp.trialbal LEFT JOIN period ON trialbal_period_id = period_id where trialbal_accnt_id = 135 order by period_start ASC;
--
--
--   
--selecT
--    trialbal_beginning,trialbal_ending, period_start, trialbal_yearend
--    from  trialbal LEFT JOIN period ON trialbal_period_id = period_id where trialbal_accnt_id = 208 order by period_start ASC;
--


-- DROP TABLE IF EXISTS timewarp.period;
CREATE TABLE timewarp.period
(
    period_id integer NOT NULL,
    period_start date,
    period_end date,
    period_closed boolean,
    period_freeze boolean,
    period_initial boolean DEFAULT false,
    period_name text,
    period_yearperiod_id integer,
    period_quarter integer,
    period_number integer NOT NULL
)
WITH (
  OIDS=FALSE
);

ALTER TABLE timewarp.period
  OWNER TO admin;
GRANT ALL ON TABLE timewarp.period TO admin;
GRANT ALL ON TABLE timewarp.period TO xtrole;
COMMENT ON TABLE timewarp.period
  IS 'Store financial reports Accounting Periods information.';


--  DROP TABLE IF EXISTS timewarp.yearperiod;
CREATE TABLE timewarp.yearperiod
(
    yearperiod_id integer NOT NULL,
    yearperiod_start date NOT NULL,
    yearperiod_end date NOT NULL,
    yearperiod_closed boolean NOT NULL DEFAULT false
)
WITH (
  OIDS=FALSE
);
ALTER TABLE timewarp.yearperiod
  OWNER TO admin;
GRANT ALL ON TABLE timewarp.yearperiod TO admin;
GRANT ALL ON TABLE timewarp.yearperiod TO xtrole;
COMMENT ON TABLE timewarp.yearperiod
  IS 'Store financial reports Accounting Year Periods information.';

-- DROP TABLE IF EXISTS timewarp.trialbal;
CREATE TABLE timewarp.trialbal
(
    trialbal_id integer NOT NULL,
    trialbal_period_id integer,
    trialbal_accnt_id integer,
    trialbal_beginning numeric(20,2),
    trialbal_ending numeric(20,2),
    trialbal_credits numeric(20,2),
    trialbal_debits numeric(20,2),
    trialbal_dirty boolean,
    trialbal_yearend numeric(20,2) NOT NULL DEFAULT 0.00
)
WITH (
  OIDS=FALSE
);


ALTER TABLE timewarp.trialbal
  OWNER TO admin;
GRANT ALL ON TABLE timewarp.trialbal TO admin;
GRANT ALL ON TABLE timewarp.trialbal TO xtrole;
COMMENT ON TABLE timewarp.trialbal
  IS 'Store financial reports Trial Balance information.';

   -- testing..
--SET search_path TO timewarp,public;
--SELECT * FROM public.financialReport(14 , ARRAY[ 112] , 'Y' , true, NULL )
--

GRANT ALL ON SCHEMA timewarp TO admin;
GRANT ALL ON SCHEMA timewarp TO xtrole;   
GRANT ALL ON  timewarp.trialbalview TO admin;
GRANT ALL ON   timewarp.trialbalview TO xtrole;
GRANT ALL ON  timewarp.periodview TO admin;
GRANT ALL ON   timewarp.periodview TO xtrole;
GRANT ALL ON  timewarp.yearperiodview TO admin;
GRANT ALL ON   timewarp.yearperiodview TO xtrole;
 
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
