-- Function: deleteglseries(integer, text)

-- DROP FUNCTION deleteglseries(integer, text);

CREATE OR REPLACE FUNCTION undeleteglseries(integer, text)
  RETURNS boolean AS
$BODY$
DECLARE
  pSequence ALIAS FOR $1;
  pNotes ALIAS FOR $2;
  _trialbalid INTEGER;
  _count INTEGER;
  v_tmp INTEGER;  
  _r RECORD;

BEGIN

--  March through all of the G/L Transactions for the passed sequence
  FOR _r IN SELECT
            gltrans_id, gltrans_date,
            gltrans_accnt_id, gltrans_amount,
            gltrans_posted, gltrans_rec,
            accnt_closedpost, accnt_forwardupdate,
            period_id, period_closed,
            period_freeze
        FROM
            accnt, gltrans
            LEFT OUTER JOIN period ON (gltrans_date BETWEEN period_start AND period_end)
        WHERE
            (
                (gltrans_accnt_id=accnt_id)
             AND
                (gltrans_deleted)
             AND
                (gltrans_sequence=pSequence)
            ) LOOP


--  If we can post into a Trial Balance, do so
    IF ( (NOT _r.period_freeze) AND 
       ( (NOT _r.period_closed) OR (_r.accnt_closedpost) ) AND
       (  NOT _r.gltrans_rec) AND
       ( NOT _r.gltrans_posted ) ) THEN

--  Try to find an existing trialbal
      SELECT trialbal_id INTO _trialbalid
      FROM trialbal
      WHERE ( (trialbal_period_id=_r.period_id)
       AND (trialbal_accnt_id=_r.gltrans_accnt_id) );

      GET DIAGNOSTICS _count = ROW_COUNT;
      IF (_count > 0) THEN

--  We found a trialbal, update it with the G/L Transaction
--  Note - two stage update to avoid any funny value caching logic
        IF (_r.gltrans_amount > 0) THEN
          UPDATE trialbal
          SET trialbal_credits = (trialbal_credits + _r.gltrans_amount)
          WHERE (trialbal_id=_trialbalid);
        ELSE
          UPDATE trialbal
          SET trialbal_debits = (trialbal_debits + (_r.gltrans_amount * -1))
          WHERE (trialbal_id=_trialbalid);
        END IF;

        UPDATE trialbal
        SET trialbal_ending = (trialbal_beginning - trialbal_debits + trialbal_credits),
            trialbal_dirty=TRUE
        WHERE (trialbal_id=_trialbalid);

      ELSE
        RAISE EXCEPTION 'Can not delete G/L Series.  Trial balance record not found.';
      END IF;

--  Forward update if we should
      IF (_r.accnt_forwardupdate AND fetchmetricbool('ManualForwardUpdate')) THEN
        PERFORM forwardUpdateTrialBalance(_trialbalid);
      END IF;

--  Delete any bank reconciliation records if this was marked cleared but non reconciled

    SELECT bankrecitem_id INTO v_tmp FROM 
        bankrecitem
        WHERE ((bankrecitem_source='GL')
        AND (bankrecitem_source_id=_r.gltrans_id)) LIMIT 1;
    
    IF FOUND THEN
        RAISE EXCEPTION 'GL Series has ban reconcillation?? - this should not be possible for deleted ones!?';
    END IF;


    
--  Unflag any journals as posted as a result of this series
    UPDATE sltrans SET
      sltrans_posted=true,
      sltrans_gltrans_journalnumber=null
    FROM gltrans
    WHERE ((gltrans_sequence=pSequence)
    AND (sltrans_gltrans_journalnumber=gltrans_journalnumber));
    
--  Mark the G/L Transaction as deleted
      UPDATE gltrans SET
        gltrans_posted=true,
        gltrans_deleted=false,
        gltrans_notes=gltrans_notes || E'\n' || pNotes
      WHERE (gltrans_id=_r.gltrans_id);

    ELSIF (_r.period_freeze) THEN
        RAISE EXCEPTION 'Can not delete a G/L Transaction in a frozen period';
    ELSIF ((_r.period_closed) OR (NOT _r.accnt_closedpost)) THEN
        RAISE EXCEPTION 'Can not delete a G/L Transaction on account % in a closed period', formatGlAccount(gltrans_accnt_id);
    ELSIF (_r.gltrans_rec) THEN
        RAISE EXCEPTION 'Can not delete a G/L Transaction that has been reconciled';
    END IF;

  END LOOP;

  RETURN true;

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