-- relay method, that calls various updates based on the type of line.


--- updates the invfifo values for shipped and unitcost


-- test: SELECT invfifo_update_from_invdetail(11598); SELECT * from invdetailview where invdetail_id = 11598;



CREATE OR REPLACE FUNCTION invfifo_update_from_invdetail(integer)
    RETURNS  boolean
AS $BODY$
DECLARE    
i_invdetail_id  ALIAS FOR $1;
BEGIN
    RAISE EXCEPTION 'replaced with option version';
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  invfifo_update_from_invdetail(integer)
  OWNER TO admin;
 
-----------
   
CREATE OR REPLACE FUNCTION invfifo_update_from_invdetail(i_invdetail_id integer, i_update_after boolean)
    RETURNS  NUMERIC

AS $BODY$
DECLARE        
            
    v_qty_before NUMERIC;
    v_ret NUMERIC;
    r_invdetail RECORD;
    r_invdetail_rev RECORD;

    v_ts_start timestamp with time zone;

BEGIN
    -- RAISE NOTICE ' invfifo_update_from_invdetail  %',i_invdetail_id;

    
    v_ts_start = clock_timestamp();
    
    SELECT
            *
        INTO
            r_invdetail
        FROM
            invdetailview  
        WHERE
            invdetail_id    = i_invdetail_id;
    
    -- no value to calc..
    if (r_invdetail.invfifo_void > 0) THEN
        -- RAISE NOTICE ' calling void on %,%',i_invdetail_id, r_invdetail.invfifo_void;
        SELECT
            invfifo_update_from_void(i_invdetail_id, r_invdetail.invfifo_void, i_update_after)
            INTO v_ret;
            
        SELECT
            *
        INTO
            r_invdetail_rev
        FROM
            invdetailview  
        WHERE
            invdetail_id    = r_invdetail.invfifo_void;
            
        IF r_invdetail_rev.invfifo_void < 1 THEN
            RAISE NOTICE 'voiding reversal %', r_invdetail.invfifo_void;
            SELECT
                invfifo_update_from_void(r_invdetail.invfifo_void,i_invdetail_id, i_update_after)
                INTO v_ret;
            
        
        END IF;
    
    -- no value to cal    
            
        RETURN v_ret;
    END IF;
    
    -- should we fix broken voids here?
    -- if before/after same and not void... -then fill needs calling..
    
    -- validate the quantity - as if this is wrong it causes all sorts of problems.
     
    
    SELECT
            COALESCE(invfifo_qty_after, 0)
        INTO
            v_qty_before
        FROM
            invdetailview
        WHERE
            invdetail_location_id = r_invdetail.invdetail_location_id
            AND
            (
                (
                    invhist_transdate < r_invdetail.invhist_transdate
                )
                OR
                (   invhist_transdate = r_invdetail.invhist_transdate
                    AND
                    invdetail_id < i_invdetail_id
                )
            )
            
            AND
                invhist_itemsite_id = r_invdetail.invhist_itemsite_id
            AND
                -- if r_invhist.invdetail_qty
                (
                    (r_invdetail.invdetail_qty > 0 AND  invdetail_qty > 0)
                    OR
                    (r_invdetail.invdetail_qty < 0 AND  invdetail_qty < 0)
                )
            ORDER BY
                invhist_transdate DESC,
                invdetail_id DESC
            LIMIT 1;
             
    IF v_qty_before != r_invdetail.invfifo_qty_before THEN
        PERFORM invfifo_fill(i_invdetail_id, true);
    END IF;
     
    --RAISE NOTICE '% invfifo_update_from_invdetail (% : %/%) %s',
    --        clock_timestamp(), i_invdetail_id ,
    --        r_invdetail.invhist_transtype, r_invdetail.invhist_ordtype, r_invdetail.invdetail_qty;
    --
    IF (r_invdetail.invdetail_qty > 0) THEN      
           -- calculate the cost before.. (incremental cost)
            
           IF (r_invdetail.invhist_transtype = 'RP') THEN
               SELECT  invfifo_update_from_pohead(i_invdetail_id, i_update_after)
                    INTO v_ret;
              -- RAISE NOTICE 'TIME: % invfifo_update_from_invdetail (%)',
               --     clock_timestamp() - v_ts_start, i_invdetail_id ;
               RETURN v_ret;
               
           END IF;
           
          
           -- if it's a transfer
               -- then unit price is dependant on FIFO sale that the tx relates to.
               
           IF (r_invdetail.invhist_transtype = 'RL') THEN
               SELECT invfifo_update_from_transfer_in(i_invdetail_id, i_update_after)
                    INTO v_ret;
              -- RAISE NOTICE 'TIME: % invfifo_update_from_invdetail (%)',
              --      clock_timestamp() - v_ts_start, i_invdetail_id ;
               
               RETURN v_ret;
           END IF;
       
               
           -- what if it's a debit memo???
           
           -- if's it's a credit memo.
           IF (r_invdetail.invhist_transtype = 'RS' AND r_invdetail.invhist_ordtype = 'CM' ) THEN
                SELECT invfifo_update_from_credit_memo(i_invdetail_id, i_update_after)
                    INTO v_ret;
                --RAISE NOTICE 'TIME: % invfifo_update_from_invdetail (%)',
               --     clock_timestamp() - v_ts_start, i_invdetail_id ;
               
                RETURN v_ret;
           END IF;
           
           IF (r_invdetail.invhist_transtype = 'RS' AND r_invdetail.invhist_ordtype = 'SO' ) THEN
            -- This is a voided transaction..
            
                -- if void is not +ve then we need to know what it returned from..
                if (r_invdetail.invfifo_void > 0) THEN
                
                    SELECT invfifo_update_from_void(i_invdetail_id, r_invdetail.invfifo_void, i_update_after)
                        INTO v_ret;
                
                ELSE
                    UPDATE cohead set  cohead_fifo_has_error = true where cohead_number = split_part(r_invdetail.invhist_ordnumber, '-', 1);
                
                    --UPDATE invfifo SET invfifo_void = 0 WHERE
                    --    invfifo_invdetail_id IN
                    --        (
                    --            SELECT
                    --                    invdetail_id
                    --                FROM
                    --                    invdetailview
                    --                WHERE
                    --                    invhist_itemsite_id = r_invdetail.invhist_itemsite_id
                    --                AND
                    --                   invhist_ordnumber like split_part(r_invdetail.invhist_ordnumber, '-', 1) || '-%'
                    --                AND
                    --                    invhist_ordtype = 'SO'
                    --                    
                    --        );
                    --    
                    --    
                    --PERFORM invfifo_cohead_void_flag(cohead_id ,r_invdetail.invhist_itemsite_id) FROM
                    --    (SELECT cohead_id FROM cohead where cohead_number = split_part(r_invdetail.invhist_ordnumber, '-', 1) ) x;
                    --    
                    -- SELECT
                    --        *
                    --    INTO
                    --        r_invdetail
                    --    FROM
                    --        invdetailview  
                    --    WHERE
                    --        invdetail_id    = i_invdetail_id;
                    --
                    --
                END IF;
               -- RAISE NOTICE 'TIME: % invfifo_update_from_invdetail (%)',
               --     clock_timestamp() - v_ts_start, i_invdetail_id ;
               
                RETURN v_ret;
           END IF;
           
           
           IF (r_invdetail.invhist_transtype = 'SH' AND r_invdetail.invhist_ordtype = 'IN' ) THEN
                -- CAN BE RETurn from shipping.
               SELECT  invfifo_update_from_return_invoice(i_invdetail_id, i_update_after)
                    INTO v_ret;
               -- RAISE NOTICE 'TIME: % invfifo_update_from_invdetail (%)',
                --    clock_timestamp() - v_ts_start, i_invdetail_id ;
               
               RETURN v_ret;
           END IF;
           
           
           
           
        -- if it's an adjustment...
               -- then the invhist is really the only tx record of this..
           
           IF (r_invdetail.invhist_transtype = 'AD') THEN
               SELECT  invfifo_update_from_adjustment_in(i_invdetail_id, i_update_after)
                    INTO v_ret;
                --RAISE NOTICE 'TIME: % invfifo_update_from_invdetail (%)',
                --    clock_timestamp() - v_ts_start, i_invdetail_id ;
               
               RETURN v_ret;
           END IF;
           
   
           RAISE EXCEPTION 'Unknown transtype (IN) %, % %', r_invdetail.invhist_transtype,  r_invdetail.invhist_ordtype , i_invdetail_id;
           RETURN false;
       END IF;
        
       -- outgoing. 
        
        -- are these all based on FIFO values???
        -- but some 
        
        
        -- if's it's a shipment - record customer id...
        IF (r_invdetail.invhist_transtype = 'SH' AND r_invdetail.invhist_ordtype='SO') THEN
            SELECT invfifo_update_from_shipment(i_invdetail_id, i_update_after)
                 INTO v_ret;
            --RAISE NOTICE 'TIME: % invfifo_update_from_invdetail (%)',
             --       clock_timestamp() - v_ts_start, i_invdetail_id ;
               
            RETURN v_ret;
        END IF;
       
        IF (r_invdetail.invhist_transtype = 'SH' AND r_invdetail.invhist_ordtype='IN') THEN
           SELECT invfifo_update_from_invoice(i_invdetail_id, i_update_after)
                INTO v_ret;
            --RAISE NOTICE 'TIME: % invfifo_update_from_invdetail (%)',
            --        clock_timestamp() - v_ts_start, i_invdetail_id ;
               
            RETURN v_ret;
        END IF;
       
        IF (r_invdetail.invhist_transtype = 'RL') THEN
            -- this is handled by the transfer in
            
            SELECT
                    invfifo_update_from_transfer_in(invdetail_id,i_update_after)
                INTO
                    v_ret
                FROM
                    invdetailview
                WHERE
                    invhist_id = r_invdetail.invhist_id
                AND
                    invdetail_id != i_invdetail_id;
        
          -- PERFORM invfifo_update_from_transfer_out(i_invdetail_id);
           RETURN 0;
        END IF;
        -- return PO inventory..  -- SAME as a void transaction
        IF (r_invdetail.invhist_transtype = 'RP') THEN
           
            
            if (r_invdetail.invfifo_void < 1) THEN
                -- RAISE NOTICE 'performing void flag on ordnumber % , itemsite %', r_invdetail.invhist_ordnumber,  r_invdetail.invhist_itemsite_id;
                PERFORM invfifo_pohead_void_flag_order(pohead_id) FROM   pohead where pohead_number =  split_part(r_invdetail.invhist_ordnumber, '-', 1);
                
            END IF;
            
            --RAISE EXCEPTION 'need to add rp back';
           
            SELECT invfifo_update_from_return_inventory(i_invdetail_id,  i_update_after)
                INTO v_ret;
             --RAISE NOTICE 'TIME: % invfifo_update_from_invdetail (%)',
             --       clock_timestamp() - v_ts_start, i_invdetail_id ;
               
            RETURN v_ret;
        END IF;
        
        IF (r_invdetail.invhist_transtype = 'AD') THEN
            SELECT invfifo_update_from_adjustment_out(i_invdetail_id, i_update_after)
                 INTO v_ret;
            --RAISE NOTICE 'TIME: % invfifo_update_from_invdetail (%)',
             --       clock_timestamp() - v_ts_start, i_invdetail_id ;
               
            RETURN v_ret;
        END IF;
        -- return from shipping? alwasy
        IF (r_invdetail.invhist_transtype = 'RS') THEN
            -- can be
             
            -- ignore returned shipment with invalid values?!?!
            -- these should be voided and not happend.
            RETURN 0;
            
            
        END IF;
     
        RAISE EXCEPTION 'Unknonw transtype (OUT) % , invdetail_id = %',r_invdetail.invhist_transtype, i_invdetail_id ;
        RETURN 0;
        
      
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  invfifo_update_from_invdetail(integer,boolean)
  OWNER TO admin;
    
    

--SELECT invfifo_update_from_invdetail(invdetail_id,true) FROM invdetail order by invdetail_id DESC limit 10;

    
    
    