

--- fills in the basic data from the invdetail table..
-- fired from the trigger...

-- only updates the qty's etc..


CREATE OR REPLACE FUNCTION invfifo_fill(integer)
    RETURNS  boolean

AS $BODY$
DECLARE
    i_invdetail_id  ALIAS FOR $1;
    v_ret boolean ;
BEGIN
    SELECT invfifo_fill(i_invdetail_id, false) INTO v_ret;
    RETURN v_ret;
   END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  invfifo_fill(integer )
  OWNER TO admin;
          


-- if this is called sequentially on a location or product, then there
-- is no need to update the following data, as it get's updated

     

CREATE OR REPLACE FUNCTION invfifo_fill(i_invdetail_id integer, i_skip_after boolean)
    RETURNS  boolean

AS $BODY$
DECLARE        
            
    
    r_fifo RECORD;
    r_invhist RECORD;
    
    v_costmethod TEXT;
    v_unitcost  NUMERIC;
    v_ret  NUMERIC;
    v_new_after NUMERIC;
    v_diff NUMERIC;
    v_last_id INTEGER;
    
    v_qty_before NUMERIC;
    v_qty_after NUMERIC;
    
    v_cost_before NUMERIC;
    v_change_qty NUMERIC;
    
    v_is_update BOOLEAN;
BEGIN
     
    -- does a fifo row exist?
    
    SELECT
            *
        INTO
            r_fifo
        FROM
            invfifo
        WHERE
            invfifo_invdetail_id = i_invdetail_id;
    
    
    
    
    
    
    v_is_update := false;
    -- already there..
    IF FOUND THEN
        -- should we do an update or anything?
        -- probably not.. we can trans invfifo's before calling this.
        
        
        -- make sure we have the correct amounts...
        v_is_update := true;
        
        
        
        
        --RETURN true;
    END IF;
    
    
    -- current record..
    
    SELECT
            *
        INTO
            r_invhist
        FROM
            invdetail
        LEFT JOIN
            invhist
        ON
            invdetail_invhist_id = invhist_id
        WHERE
            invdetail_id = i_invdetail_id;
    
    
    -- check if it's FIFO valued..
    SELECT
            itemsite_costmethod
        INTO
            v_costmethod
        FROM
            itemsite
        WHERE
            itemsite_id = r_invhist.invhist_itemsite_id;
        
    
    IF (v_costmethod != 'S' OR NOT fetchMetricBool('UseStandardAsFIFO')) THEN
        RETURN NEW;
    END IF;
    
    -- work out the stock before this..
    
    SELECT
            COALESCE(invfifo_qty_after, 0),
            COALESCE(invfifo_cost_after, 0)
        INTO
            v_qty_before,
            v_cost_before
        FROM
            invdetailview
        WHERE
            invdetail_location_id = r_invhist.invdetail_location_id
            AND
            (
                (
                    invhist_transdate < r_invhist.invhist_transdate
                )
                OR
                (   invhist_transdate = r_invhist.invhist_transdate
                    AND
                    invdetail_id < i_invdetail_id
                )
            )
            
            AND
                invhist_itemsite_id = r_invhist.invhist_itemsite_id
            AND
                -- if r_invhist.invdetail_qty
                (
                    (r_invhist.invdetail_qty > 0 AND  invdetail_qty > 0)
                    OR
                    (r_invhist.invdetail_qty < 0 AND  invdetail_qty < 0)
                )
            ORDER BY
                invhist_transdate DESC,
                invdetail_id DESC
            LIMIT 1;
      
    --RAISE NOTICE 'qty before = %', v_qty_before;
    v_diff := 0;
    v_qty_before := COALESCE(v_qty_before, 0.0);
    v_cost_before := COALESCE(v_cost_before, 0.0);
    
    v_change_qty := ABS(r_invhist.invdetail_qty);
    
    
    
    
    IF v_is_update THEN
        -- if the values are the same, then just return..
        
        
        IF (r_fifo.invfifo_void != 0) THEN
            v_change_qty = 0;
        END IF;
        
        
        IF (
            r_fifo.invfifo_qty_before = v_qty_before
            AND
            r_fifo.invfifo_qty_after = v_qty_before + v_change_qty
           ) THEN
            RETURN true;
        END IF;
        
        
        v_diff := (v_qty_before + v_change_qty )- r_fifo.invfifo_qty_before ;
        -- otherwise update..
        
        UPDATE
            invfifo
         
        SET 
         
            invfifo_qty_before =  v_qty_before,
            invfifo_qty_after  = v_qty_before + v_change_qty
             
        WHERE
            invfifo_invdetail_id = i_invdetail_id;
       
        
        
        
    
    
    ELSE
        v_diff :=   v_change_qty;
        
        INSERT INTO  invfifo (
            invfifo_invdetail_id,  invfifo_unitcost  , invfifo_totalcost,
            invfifo_cost_before, invfifo_cost_after,
            
            invfifo_qty_before, invfifo_qty_after,
            invfifo_is_estimate, invfifo_recalc_queued,
            
            invfifo_cust_id, invfifo_vend_id
        ) VALUES  (
            r_invhist.invdetail_id, 0, 0,
            v_cost_before, v_cost_before,  -- these are the same value - as by default we do not adjust the future..
            
            v_qty_before, v_qty_before + v_change_qty,
            false, true,
            
            null, null
        );
    END IF;
    -- next adjust quanties of future items by the qty.
    
    
    IF i_skip_after THEN
        RETURN true;
    END IF;
    
    IF v_diff = 0.0 THEN
        RETURN true;
    END IF;
    
    
    UPDATE
            invfifo
         
         SET 
         
            invfifo_qty_before = invfifo_qty_before +  v_diff,
            invfifo_qty_after  = invfifo_qty_after +  v_diff
             
         FROM
            invdetail, invhist
             
         WHERE
                invfifo_invdetail_id = invdetail_id
             AND
                invhist_id = invdetail_invhist_id
             AND
                invdetail_location_id = r_invhist.invdetail_location_id
             AND
                invhist_transdate > r_invhist.invhist_transdate
             AND
                invhist_itemsite_id = r_invhist.invhist_itemsite_id
             AND
                (
                    (r_invhist.invdetail_qty> 0 AND  invdetail_qty > 0)
                    OR
                    (r_invhist.invdetail_qty < 0 AND  invdetail_qty < 0)
                );
    
    
    -- at this point it should call the relivant updater ... to fill in values..
    -- that in turn will flag all the dirty values..
    
    
    -- this may cause a big slowdown?!?!
    --PERFORM invfifo_update_from_invdetail(i_invdetail_id);
    
    -- initially the trigger will not cause a update of all the dependant data..??
    -- it will probably only just flag stuff as dirty..
    -- we will need to run a call periodically to try and recalc stuff.
    
    
    RETURN true;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  invfifo_fill(integer, boolean )
  OWNER TO admin;
          
    
    
    
    