-- aim is to flag the invfifo_void field with if
-- shipments have been returned, so that fifo calcs will ignore any values.





-- called with cohead_id and itemsite_id


-- fill



-- reset..

-- update invfifo SET invfifo_void = 0 where  invfifo_invdetail_id IN (SELECT invdetail_id FROM invdetailview WHERE invhist_ordtype='SO' and invfifo_void !=0);
-- SELECT invfifo_cohead_void_flag_order(cohead_id) FROM cohead;


-- validation
-- total without voided should match total all together

 --SELECT SUM(invdetail_qty) FROM invdetailview where invhist_ordtype = 'SO';
 --SELECT SUM(invdetail_qty) FROM invdetailview where invhist_ordtype = 'SO' and invfifo_void = 0;
 --SELECT SUM(abs(invdetail_qty)) FROM invdetailview where invhist_ordtype = 'SO' and invfifo_void != 0;
 --

--SELECT invhist_ordnumber, aftervoid, beforevoid FROM
--
--    (SELECT invhist_ordnumber, SUM( CASE WHEN invfifo_void != 0 THEN 0 ELSE invdetail_qty END ) as aftervoid,
--        SUM(invdetail_qty) as beforevoid FROM invdetailview WHERE invhist_ordtype = 'SO'    
--        GROUP BY invhist_ordnumber
--    ) x WHERE aftervoid != beforevoid ;


CREATE OR REPLACE FUNCTION invfifo_cohead_void_flag_order_force(i_cohead_id integer)
    RETURNS  boolean
    
AS $BODY$
DECLARE        
           
    v_tmp INTEGER;
    v_bool BOOLEAN; 
    v_checksum NUMERIC;
    v_cohead_number text;
    v_ret boolean;
BEGIN
    
    UPDATE invfifo SET invfifo_void = 0 WHERE invfifo_cohead_id = i_cohead_id;
    
    SELECT invfifo_cohead_void_flag_order(i_cohead_id ) INTO v_ret;
    
    RETURN v_ret;

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




CREATE OR REPLACE FUNCTION invfifo_cohead_void_flag_order(i_cohead_id integer)
    RETURNS  boolean
    
AS $BODY$
DECLARE        
           
    v_tmp INTEGER;
    v_bool BOOLEAN; 
    v_checksum NUMERIC;
    v_cohead_number text;
BEGIN
    
    -- this is just way to slow...
    
    --SELECT
    --        invfifo_cohead_check_id()
    --    INTO
    --        v_bool;
    --
    --IF NOT v_bool THEN
    --    -- slowish.. 0.5s
    --    PERFORM invfifo_cohead_fix_id(invdetail_id)
    --        FROM
    --            invdetailview
    --        WHERE
    --             invhist_ordtype  ='SO'
    --             AND
    --             invfifo_cohead_id = 0;
    --             
    --END IF;
    --
    SELECT
            cohead_number
        INTO
            v_cohead_number
        FROM
            cohead
        WHERE
            cohead_id = i_cohead_id;
    
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Error finding cohead_id %', i_cohead_id;
    END IF;
    --
    --
    PERFORM invfifo_cohead_void_flag_tx( invdetail_id, invhist_ordnumber, invhist_itemsite_id, invdetail_qty)
        FROM
            invdetailview
        WHERE 
            
                invhist_ordtype = 'SO'
            AND
                invfifo_cohead_id = i_cohead_id
            AND
                invdetail_qty > 0
             
            ORDER BY
                invdetail_id ASC;
 
 
    -- do a sanity check.. - might be a bit slow... - find the total sum of voided transactions = eg. +v and -ve must be 0
    SELECT 
                COALESCE(SUM(invdetail_qty), 0.0)
            INTO
                v_checksum
             FROM 
                invdetailview
             WHERE 
                 
                     invhist_ordtype = 'SO'
                AND
                    invfifo_cohead_id = i_cohead_id
                 AND
                     invfifo_void != 0  ;
    
    if (v_checksum != 0.0) THEN
        -- reset all the foid flags, then try again..
    
        UPDATE
                invfifo
            set
                invfifo_void = 0
            where
                invfifo_invdetail_id in (
                    select invdetail_id
                        from invdetailview
                        where invhist_ordnumber like v_cohead_number || '%'
                );
        PERFORM invfifo_cohead_void_flag_tx( invdetail_id, invhist_ordnumber, invhist_itemsite_id, invdetail_qty)
        FROM
            invdetailview
        WHERE 
            
                invhist_ordtype = 'SO'
            AND
                invfifo_cohead_id = i_cohead_id
            AND
                invdetail_qty > 0
             
            ORDER BY
                invdetail_id ASC;
         
    END IF;
 
 
    SELECT 
                COALESCE(SUM(invdetail_qty), 0.0)
            INTO
                v_checksum
             FROM 
                invdetailview
             WHERE 
                 
                     invhist_ordtype = 'SO'
                AND
                    invfifo_cohead_id = i_cohead_id
                 AND
                     invfifo_void != 0  ;
    
    if (v_checksum != 0.0) THEN
         RAISE EXCEPTION 'order %  did not balance, %', v_cohead_number, v_checksum ;
    END IF;
 
 
    RETURN TRUE;
     
 END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  invfifo_cohead_void_flag_order(integer )
  OWNER TO admin;
          



-- test: US10026-1
-- SELECT invfifo_cohead_void_flag( 9740, 3116);

-- test: HK10003-7
-- SELECT invfifo_cohead_void_flag( 9647, 3239);

 

CREATE OR REPLACE FUNCTION invfifo_cohead_void_flag(i_cohead_id integer,i_itemsite_id integer)
    RETURNS  boolean
    
AS $BODY$
DECLARE        
          
  
    v_cohead_number text;
    
    v_tmp_a text;
    v_tmp_b NUMERIC;
    v_tmp_c NUMERIC;

BEGIN
    
    SELECT
            cohead_number
        INTO
            v_cohead_number
        FROM
            cohead
        WHERE
            cohead_id = i_cohead_id;
    
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Error finding cohead_id %', i_cohead_id;
    END IF;
    
    -- only perform on voided records..

    PERFORM invfifo_cohead_void_flag_tx( invdetail_id, invhist_ordnumber, i_itemsite_id, invdetail_qty)
        FROM
            (SELECT 
                invdetail_id, invhist_ordnumber, invdetail_qty
                FROM 
                invdetailview
                WHERE 
                    
                        invhist_ordtype = 'SO'
                    AND
                    (
                        invhist_ordnumber LIKE v_cohead_number || '-%'
                        OR
                        invhist_ordnumber = v_cohead_number
                    )
                    AND
                        invhist_itemsite_id = i_itemsite_id
                    AND
                        invdetail_qty > 0
                    ORDER BY
                        invdetail_id ASC
            ) x;
 
 
    -- validation.
    
        
    SELECT
        invhist_ordnumber,
        aftervoid,
        beforevoid
        INTO
        v_tmp_a,
        v_tmp_b,
        v_tmp_c
        
        FROM
    
        (SELECT
            invhist_ordnumber,
            SUM( CASE WHEN invfifo_void != 0 THEN 0 ELSE invdetail_qty END ) as aftervoid,
            SUM(invdetail_qty) as beforevoid
            FROM invdetailview
            WHERE
                invhist_ordtype = 'SO'
            AND
            (
                invhist_ordnumber LIKE v_cohead_number || '-%'
                OR
                invhist_ordnumber = v_cohead_number
            )
            AND
                invhist_itemsite_id = i_itemsite_id
        
            GROUP BY invhist_ordnumber
        ) x
            WHERE aftervoid != beforevoid
        LIMIT 1;
    IF FOUND THEN
        --RAISE NOTICE 'quanties differ when applying void to %', v_tmp_a;
    END IF;
    


 
 
    RETURN TRUE;
     
 END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  invfifo_cohead_void_flag(integer, integer )
  OWNER TO admin;
          

--- invdetail_id , order number, 
 
CREATE OR REPLACE FUNCTION invfifo_cohead_void_flag_tx(INTEGER, TEXT,INTEGER, NUMERIC)
    RETURNS  boolean
    
AS $BODY$
DECLARE        
          

    i_invdetail_id  ALIAS FOR $1;
    i_order_number ALIAS FOR $2;
    i_itemsite_id  ALIAS FOR $3;
    i_qty ALIAS FOR $4;
    v_qty_balance NUMERIC;
       v_void_of INTEGER;
       v_location_id INTEGER;
BEGIN
    
    
    if (i_qty > 0) THEN
        
        --- got return - look for issues
        
        -- then it is always void!!!
        
        -- do we already have a void refreence for this?!?
        
        SELECT
                invfifo_void,
                invdetail_location_id 
            INTO
                v_void_of,
                v_location_id
            FROM
                invdetailview
            WHERE
                invdetail_id = i_invdetail_id;
        
        IF v_void_of > 0 THEN
            PERFORM invfifo_update_from_void(v_void_of, i_invdetail_id, true);
            PERFORM invfifo_update_from_void(i_invdetail_id, v_void_of, true);
            return true;
        END IF;
    
            -- determine what it reverses...
    
            SELECT
                    invdetail_id
                INTO
                    v_void_of
                FROM
                    invdetailview
                WHERE
                
                    invhist_ordtype = 'SO'
                AND
                
                    invhist_ordnumber = i_order_number
                AND
                    invhist_itemsite_id = i_itemsite_id
                AND
                    invdetail_location_id = v_location_id
                    
                AND
                    invdetail_qty = -1 * i_qty
                AND
                    (invfifo_void = 0 OR invfifo_void = -1)
                ORDER BY
                    -- FAVOUR REVERSING bad shipments first..
                    CASE WHEN invhist_comments LIKE 'Return%' THEN 1 ELSE 0 END DESC,
                
                    invdetail_id ASC
                LIMIT 1;
                    
            IF NOT FOUND THEN
                RETURN false;
            END IF;
            
--             RAISE EXCEPTION 'flagging issue as void %', v_void_of;
            PERFORM invfifo_update_from_void(v_void_of, i_invdetail_id,true);
--             RAISE NOTICE 'flagging return as void %', i_invdetail_id;
            PERFORM invfifo_update_from_void(i_invdetail_id, v_void_of,true);
            
    
          
            return true;
        
        
        
    END IF;
     
    
    RETURN FALSE;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  invfifo_cohead_void_flag_tx(INTEGER, TEXT,INTEGER, NUMERIC)
  OWNER TO admin;
          
-- see if it works.. 

--
--SELECT invfifo_cohead_void_flag(10260, 3404);
--
--
--SELECT count(invdetail_id) FROM invdetailview where invfifo_void != 0;