


--- apply  stock ajdustment value
-- for HK
--  SELECT  setmetric('invfifo_start_date', '2012-05-01');

-- for SG
--  SELECT  setmetric('invfifo_start_date', '2011-10-01');

--basically inventory ajustments(HK HQ) <-> inventory asset
--
--
--== this should handle voids..
--
--== needs to void out our old adjustments.
--
--
--costcat_adjustment_accnt_id    | 228


-- test...
     
--SELECT invfifo_apply_gl_adjust(period_start) FROM (
--    SELECT period_start  FROM period WHERE NOT period_closed AND NOT period_freeze ORDER BY period_start ASC LIMIT 1) x;
--    
-- 
----   
--SELECT invfifo_apply_gl_adjust(transdate)  FROM (
--    select
--            distinct(invhist_transdate::date) as transdate
--        FROM
--            invdetailview
--        WHERE
--            invhist_transdate > (
--                SELECT period_start  FROM period
--                WHERE NOT period_closed AND NOT period_freeze ORDER BY period_start ASC LIMIT 1
--            ) 
--       
--        GROUP BY
--            invhist_transdate::date
--         ORDER BY
--            invhist_transdate::date ASC
--        ) y;
--
--      



--- this is a bit more complicated, as the AD's have been adjusted into different accounts.

-- for a single transaction:
-- we can use the gltrans_misc_id -> invhist_id


--> for an account = eg. 228
--> gltrans misc_id points to the invhist_id
--> if the accnt is +ve then

--what we need is a call to find out what the real fifo value for a tx was
--
--eg.
   --invfifo_apply_gl_adjust_from_trans( gltrans_amount, gltrans_misc )
CREATE OR REPLACE FUNCTION invfifo_apply_gl_adjust_from_trans(NUMERIC, INTEGER)
  RETURNS  NUMERIC
    
AS $BODY$
DECLARE     
   
    i_amount ALIAS FOR $1;
    i_misc_id ALIAS FOR $2;
    
    v_total NUMERIC;
BEGIN
   SELECT
            COALESCE(CASE WHEN invfifo_void = 0 THEN ROUND(ABS(invdetail_qty) * invfifo_landedunitcost,2) ELSE 0.0 END,0.0)
        INTO
            v_total
        FROM 
            invdetailview
        WHERE
            invhist_id = i_misc_id; -- check transtype???
    
    IF NOT FOUND THEN
        v_total =  0.0; -- should not happen..
    END IF;
    
    IF i_amount < 0 THEN
        v_total = v_total * -1;
    END IF;
    
    RETURN v_total;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION   invfifo_apply_gl_adjust_from_trans(NUMERIC, INTEGER)
  OWNER TO admin;    
        
--
--SELECT sum(gltrans_amount), sum(invfifo_apply_gl_adjust_from_trans(gltrans_amount, gltrans_misc_id)) FROM 
--   gltrans where gltrans_accnt_id = 228 AND gltrans_doctype='AD' AND gltrans_date <= '2011-05-01'  ;



CREATE OR REPLACE FUNCTION invfifo_apply_gl_adjust_all()
  RETURNS  TEXT
    
AS $BODY$
DECLARE        
    v_period_close_at_end INTEGER;
    v_start_date DATE;
    
BEGIN

        SELECT
            period_temp_open(period_start)
        INTO
            v_period_close_at_end
        FROM
            period
        LEFT JOIN
            yearperiod
        ON
            period_yearperiod_id = yearperiod_id
        WHERE
            NOT yearperiod_closed
        ORDER BY
            period_start ASC
        LIMIT 1;

        SELECT fetchMetricText('invfifo_start_date') INTO v_start_date;

        IF v_start_date IS NULL OR LENGTH(v_start_date::text) < 1 THEN
            RAISE EXCEPTION 'PERFORM setmetric(''invfifo_start_date'',  ''YOUR START DATE'');  -- do this! %', v_start_date;
        END IF;

        PERFORM 
                invfifo_apply_gl_adjust(transdate) 
        FROM (
                SELECT
                        DISTINCT(invhist_transdate::date) AS transdate
                FROM
                        invdetailview
                WHERE
                        invhist_transtype = 'AD'
                    AND
                        invhist_transdate::date >= v_start_date
            )  x;

        IF v_period_close_at_end > 0 THEN
            PERFORM period_temp_close(v_period_close_at_end);
        END IF;

        RETURN 'OK';

    
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  invfifo_apply_gl_adjust_all()
  OWNER TO admin;    
        




DROP FUNCTION IF EXISTS invfifo_apply_gl_adjust(DATE);
-- done on a per day basis?
CREATE OR REPLACE FUNCTION invfifo_apply_gl_adjust(DATE)
  RETURNS  NUMERIC
    
AS $BODY$
DECLARE        
    i_date  ALIAS FOR $1;
    v_date DATE;
    v_first_open DATE;
    
    
    v_adj_accnt_id  INTEGER;
    v_asset_accnt_id  INTEGER;
    
      v_gl_adjust_by NUMERIC;
    v_diff NUMERIC;
    v_day_total  NUMERIC;
    v_day_stdcost_total  NUMERIC;
    v_gl_day_total NUMERIC;
    
    v_old_value NUMERIC;
    v_rounding   NUMERIC;
    v_result INTEGER;
    v_resultbool BOOLEAN;
    
    v_glsequence INTEGER;
    v_start_date TEXT;
    v_period_close_at_end INTEGER;
    v_is_old BOOLEAN;
BEGIN
     
    
    
    -- if date is in the closed account area..
    -- we need to adjust the date to the last value, and do eveything before..
    
    -- whats the first open date..
    v_date := i_date;
    
    
    SELECT fetchMetricText('invfifo_start_date') INTO v_start_date;
    IF v_start_date IS NULL OR LENGTH(v_start_date) < 1 THEN
        RAISE EXCEPTION 'PERFORM setmetric(''invfifo_start_date'',  ''YOUR START DATE'');  -- do this! %', v_start_date;
    END IF;
    
    IF v_date < v_start_date::date THEN
        RAISE NOTICE 'ignoring transaction before start date';
        RETURN 0;
    END IF;
    
    SELECT
        yearperiod_start
    INTO
        v_first_open
    FROM
        yearperiod
    WHERE
        NOT yearperiod_closed

    ORDER BY
        yearperiod_start ASC
    LIMIT 1;
    
    
    SELECT
        costcat_adjustment_accnt_id,
        costcat_asset_accnt_id
    INTO
        v_adj_accnt_id,
        v_asset_accnt_id
    FROM
        costcat
    LIMIT 1;
    
                 
    IF v_first_open >= i_date THEN
        v_date = v_first_open ;
        
         
        v_is_old = true;
    ELSE
    
     
        v_is_old = false;      
    END IF;
      
       
     SELECT  COALESCE(SUM( ABS(adjust) ),0.0) + COALESCE(SUM( adjust ),0.0)  INTO v_gl_adjust_by 
                 
                FROM (
                    SELECT
                                gltrans_accnt_id as accnt_id,
                                ROUND(COALESCE(SUM(
                                    invfifo_apply_gl_adjust_from_trans(gltrans_amount, gltrans_misc_id) - gltrans_amount
                                    ),0.0), 2) as adjust
                            FROM 
                                gltrans
                            WHERE
                            gltrans_source != 'G/L'
                            AND
                            gltrans_doctype = 'AD'
                            AND
                            (
                                (
                                    v_is_old
                                    AND
                                    gltrans_date <= v_date
                                )
                                OR
                                (
                                    NOT v_is_old
                                    AND
                                    gltrans_date = v_date
                                
                                )
                            )
                            GROUP BY
                                gltrans_accnt_id
                    ) x;
            
    -- this needs to know the rounding error..
    
    
    -- we may have an existing adjustment!!????
--     if (v_gl_adjust_by = 0.0) THEN
--         RAISE NOTICE ' diff = % skipping',   v_gl_adjust_by ;
--         return 0;
--     END IF;
   
    -- do we have an existing adjustment?
     
    SELECT
            COALESCE(ROUND(SUM(ABS(gltrans_amount)),2),0.0)
        INTO
            v_old_value
        FROM
            gltrans
        WHERE
            gltrans_docnumber =  'FIFO-INVADJ-' || v_date
            AND
            gltrans_deleted = false;
        
    
    IF v_old_value = v_gl_adjust_by THEN
        RAISE NOTICE 'No change, diff = %',   v_gl_adjust_by ;
        return 0;
    END IF;
        
    
     
    RAISE NOTICE '%: old adjust=%  new adjust= %',
        v_date, v_old_value, v_gl_adjust_by;

    -- values do not match - delete old attempt..
     
    SELECT
            gltrans_sequence
    INTO
            v_result
    FROM
            gltrans
    WHERE
            gltrans_docnumber =  'FIFO-INVADJ-' || v_date
        AND
            gltrans_deleted = false
    LIMIT 1 ;
        
    IF FOUND THEN
            RAISE NOTICE 'Reversing old attempt';

            PERFORM invfifo_apply_gl_adjust_revert_fifo(v_date);
    
    END IF;
    
    IF v_gl_adjust_by = 0 THEN
        RAISE NOTICE 'No Adjustment';
        RETURN v_gl_adjust_by;
    END IF;

    RAISE NOTICE 'Inv adjustment is %', v_gl_adjust_by;
    
    SELECT period_temp_open(v_date) INTO v_period_close_at_end; 
    
    SELECT fetchGLSequence() INTO v_glsequence;
    

    SELECT SUM(CASE WHEN res < 1 THEN res - 1 ELSE 0 END) INTO v_result FROM
        (

            SELECT insertIntoGLSeries(
                                    v_glsequence,
                                    'G/L',
                                    'JE',
                                    'FIFO-INVADJ-' || v_date, 
                                    accnt_id, -- adjustment account
                                    adjust,
                                   v_date ) as res
                FROM (
                    SELECT
                                gltrans_accnt_id as accnt_id,
                                ROUND(COALESCE(SUM(
                                    invfifo_apply_gl_adjust_from_trans(gltrans_amount, gltrans_misc_id) - gltrans_amount
                                    ),0.0), 2) as adjust
                            FROM 
                                gltrans
                            WHERE
                             gltrans_source != 'G/L'
                            AND
                            gltrans_doctype = 'AD'
                            AND
                            (
                                (
                                    v_is_old
                                    AND
                                    gltrans_date <= v_date
                                )
                                OR
                                (
                                    NOT v_is_old
                                    AND
                                    gltrans_date = v_date
                                
                                )
                            )
                            GROUP BY
                                gltrans_accnt_id
                    ) x
            
            ) y;

    --  Expense = use diff *-1  for apply value.

    -- put the diff in cur gain/loss

    
    if (v_result < 0) THEN
        RAISE EXCEPTION 'insertIntoGLSeries SHIP ASS  failed';
    END IF;
    
    --- is there any remainder....
    RAISE NOTICE 'ref is FIFO-INVADJ-%', v_date;
    SELECT
            COALESCE(ROUND(SUM(ABS(glseries_amount)),2),0.0)
        INTO
            v_rounding
        FROM
            glseries
        WHERE
            glseries_docnumber =  'FIFO-INVADJ-' || v_date;

    RAISE NOTICE 'adjust total is %', v_rounding;
      
    SELECT
            COALESCE(ROUND(SUM(glseries_amount),2),0.0)
        INTO
            v_rounding
        FROM
            glseries
        WHERE
            glseries_docnumber =  'FIFO-INVADJ-' || v_date;

    RAISE NOTICE 'Rounding is %', v_rounding;

    IF v_rounding != 0.0 THEN
    
            SELECT insertIntoGLSeries(
                                    v_glsequence,
                                    'G/L',
                                    'JE',
                                    'FIFO-INVADJ-' || v_date, 
                                    fetchMetricValue('CurrencyGainLossAccount')::integer, 
                                    v_rounding * -1.0,
                                   v_date ) into v_result;
         if (v_result < 0) THEN
            RAISE EXCEPTION 'insertIntoGLSeries SHIP ASS  failed';
        END IF;

    END IF;
    

    UPDATE glseries
                SET glseries_notes='Fifo Day inventory adjustment for ' || v_date
                WHERE (glseries_sequence=v_glsequence);
    
    
    --RAISE NOTICE 'postGLSeriesNoSumm(%,COALESCE(NULL,fetchJournalNumber(''G/L''))) ', v_glsequence;
    
    SELECT postGLSeriesNoSumm(v_glsequence,COALESCE(NULL,fetchJournalNumber('G/L'))) INTO v_result;
    if (v_result < 1) THEN
        RAISE EXCEPTION 'post GL seriese failed %', v_result;
    END IF;

    IF v_period_close_at_end > 0 THEN
        PERFORM period_temp_close(v_period_close_at_end);
    END IF;
    
  RETURN v_gl_adjust_by;
    
    
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  invfifo_apply_gl_adjust(DATE)
  OWNER TO admin;    
        
 

CREATE OR REPLACE FUNCTION invfifo_apply_gl_adjust_revert_fifo(i_date DATE)
    RETURNS  INTEGER
    
AS $BODY$
DECLARE  
    v_first_open DATE;
    v_min_date DATE;
    v_max_date DATE;

    v_glsequence INTEGER;
    _r RECORD;
    v_result INTEGER;
    
    v_post BOOLEAN;
    v_period_close_at_end INTEGER;
    
    
    
BEGIN
    
    SELECT
        yearperiod_start
    INTO
        v_first_open
    FROM
        yearperiod
    WHERE
        NOT yearperiod_closed

    ORDER BY
        yearperiod_start ASC
    LIMIT 1;
    
    SELECT 
            MIN(gltrans_date),
            MAX(gltrans_date)
    INTO
            v_min_date,
            v_max_date
    FROM
            gltrans
    WHERE   
            gltrans_docnumber = 'FIFO-INVADJ-' || i_date
        AND
            gltrans_source = 'G/L'
        AND
           gltrans_doctype = 'JE'
        AND
            NOT gltrans_deleted
        AND
            gltrans_posted;
    
    
    IF (v_max_date >= v_first_open) THEN
        PERFORM invfifo_apply_gl_adjust_delete_je(i_date);
    END IF;
    
    IF (v_min_date < v_first_open) THEN
        
        SELECT period_temp_open(v_first_open)  INTO v_period_close_at_end;

        SELECT fetchGLSequence() INTO v_glsequence;

        v_post := false;

        FOR _r IN SELECT 
                    COALESCE(SUM(gltrans_amount * -1), 0) AS v_amount,
                    gltrans_accnt_id
            FROM
                    gltrans
            WHERE
                    gltrans_docnumber = 'FIFO-INVADJ-' || i_date
                AND
                    gltrans_source = 'G/L'
                AND
                   gltrans_doctype = 'JE'
                AND
                    NOT gltrans_deleted
                AND
                    gltrans_posted 
                AND
                    gltrans_date < v_first_open

            GROUP BY gltrans_accnt_id LOOP

            IF (_r.v_amount <> 0) THEN

                v_post := true;

                SELECT insertIntoGLSeries(
                            v_glsequence,
                            'G/L',
                            'JE',
                            'FIFO-INVADJ-' || v_first_open, 
                            _r.gltrans_accnt_id,
                            _r.v_amount,
                           v_first_open ) INTO v_result;

                IF (v_result < 1) THEN
                    RAISE EXCEPTION 'insertIntoGLSeries SHIP ASS  failed';
                END IF;
            END IF;
        END LOOP;

        IF (NOT v_post) THEN
            RETURN -1;
        END IF;

        UPDATE glseries
                SET glseries_notes='Fifo Day inventory adjustment for ' || v_date
                WHERE (glseries_sequence=v_glsequence);



        SELECT postGLSeriesNoSumm(v_glsequence,COALESCE(NULL,fetchJournalNumber('G/L'))) INTO v_result;

        if (v_result < 1) THEN
            RAISE EXCEPTION 'post GL seriese failed.';
        END IF;

        IF v_period_close_at_end > 0 THEN
            PERFORM period_temp_close(v_period_close_at_end);
        END IF;
    END IF;
       
    RETURN 0;
   
   END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  invfifo_apply_gl_adjust_revert_fifo(DATE )
  OWNER TO admin;    




CREATE OR REPLACE FUNCTION invfifo_apply_gl_adjust_delete_je(i_date DATE)
    RETURNS  INTEGER
    
AS $BODY$
DECLARE        
    v_first_open DATE;
    v_period_close_at_end INTEGER;
    v_ret INTEGER;
    
BEGIN  

    SELECT
        yearperiod_start
    INTO
        v_first_open
    FROM
        yearperiod
    WHERE
        NOT yearperiod_closed

    ORDER BY
        yearperiod_start ASC
    LIMIT 1;

    SELECT period_temp_open(v_first_open) INTO v_period_close_at_end; 
 
    PERFORM
        deleteGlSeries(gltrans_sequence, 'Journal edited by FIFO on ' || to_char(NOW(), 'Day Mon DD YYY') ) 
        FROM
            gltrans
        WHERE
            gltrans_docnumber = 'FIFO-INVADJ-' || i_date
        AND
            gltrans_source = 'G/L'
        AND
           gltrans_doctype = 'JE'
        AND
            NOT gltrans_deleted
        AND
            gltrans_posted
        AND
            gltrans_date >= v_first_open;
        
    IF v_period_close_at_end > 0 THEN
        PERFORM period_temp_close(v_period_close_at_end);
    END IF;
           
    RETURN 1;
   
   END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  invfifo_apply_gl_adjust_delete_je(DATE )
  OWNER TO admin;  


 
 
 -- different account!?!?
 -- opening balance..
 
-- 
-- invdetail_id             | 7537
--invdetail_transtype      | 
--invdetail_invhist_id     | 9070
--invdetail_location_id    | 160
--invdetail_qty            | 2.000000
--invdetail_comments       | 
--invdetail_qty_before     | 0.000000
--invdetail_qty_after      | 2.000000
--invdetail_invcitem_id    | 
--invdetail_expiration     | 2100-01-01
--invdetail_warrpurc       | 
--invdetail_ls_id          | 
--invhist_id               | 9070
--invhist_itemsite_id      | 1249
--invhist_transdate        | 2009-05-01 00:00:00+08
--invhist_transtype        | AD
--invhist_invqty           | 2.000000
--invhist_invuom           | EA
--invhist_ordnumber        | NS-IA-1-IA-1200899
--invhist_docnumber        | 
--invhist_qoh_before       | 879.000000
--invhist_qoh_after        | 881.000000
--invhist_unitcost         | 77.253000
--invhist_acct_id          | 
--invhist_xfer_warehous_id | 
--invhist_comments         | Miscellaneous Adjustment for item BA549213
--                         | Imported from Netsuite - InventoryAdjustment
--invhist_posted           | t
--invhist_imported         | 
--invhist_hasdetail        | t
--invhist_ordtype          | AD
--invhist_analyze          | t
--invhist_user             | admin
--invhist_created          | 2012-09-02 05:24:35.26078+08
--invhist_costmethod       | S
--invhist_value_before     | 67905.39
--invhist_value_after      | 68059.90
--invhist_series           | 7233
--invfifo_invdetail_id     | 7537
--invfifo_unitcost         | 77.253000
--invfifo_totalcost        | 154.506000
--invfifo_qty_before       | 0.000000
--invfifo_qty_after        | 2.000000
--invfifo_cost_before      | 0.000000
--invfifo_cost_after       | 154.506000
--invfifo_is_estimate      | f
--
-- 
-- 
--
---- transactions can affect customer deposits, customer credits 121
--
-- 
-- 
-- 
-- 
-- 
-- 
----
------ gltrans misc ids == invhist ids...
---- selecT gltrans_misc_id from gltrans where gltrans_accnt_id = 228 AND gltrans_doctype='AD' AND gltrans_date <= '2011-05-01' order by gltrans_date asc ;
----
----
--SELECT COALESCE(ROUND(SUM(ROUND(invdetail_qty * invhist_unitcost,2)),2),0.0) from invdetailview where invhist_id IN (
--    selecT gltrans_misc_id from gltrans where gltrans_accnt_id = 228 AND gltrans_doctype='AD' AND gltrans_date <= '2011-05-01' order by gltrans_date asc) ;
--
----selecT sum(gltrans_amount) from gltrans where gltrans_accnt_id = 228 AND gltrans_doctype='AD' AND gltrans_date <= '2011-05-01'  ;
----
----
--SELECT COALESCE(ROUND(SUM(ROUND(invdetail_qty * invhist_unitcost,2)),2),0.0)
--    from invdetailview where invhist_transdate::date  <= '2011-05-01'        AND  invhist_transtype = 'AD';  
----
--
--
--SELECT * from invdetailview where  invhist_transdate::date  <= '2011-05-01'        AND  invhist_transtype = 'AD' AND  invhist_id NOT IN (
--    selecT gltrans_misc_id from gltrans where gltrans_accnt_id = 121 AND gltrans_doctype='AD' AND gltrans_date <= '2011-05-01' order by gltrans_date asc) ;
--
--
--SELECT distinct(gltrans_accnt_id) FROM gltrans where gltrans_misc_id IN (
--    selecT gltrans_misc_id from gltrans where gltrans_accnt_id = 228 AND gltrans_doctype='AD' AND gltrans_date <= '2011-05-01' order by gltrans_date asc) ;

 --SELECT
 --       gltrans_accnt_id as accnt_id,
 --       ROUND(COALESCE(SUM( invfifo_apply_gl_adjust_from_trans(gltrans_amount, gltrans_misc_id) - gltrans_amount),0.0), 2) as adjust
 --   FROM 
 --       gltrans
 --   WHERE
 --   
 --   gltrans_doctype = 'AD'
 --   AND
 --   
 --     gltrans_date <= '2011-05-01' 
 --        
 --   
 --   GROUP BY
 --       gltrans_accnt_id;
 --       
        
        


-- SELECT invfifo_apply_gl_adjust(transdate) 
--  FROM (
--    select
--            distinct(invhist_transdate::date) as transdate
--        FROM
--            invdetailview
--            
--         WHERE
--             invhist_transtype = 'AD')  x
