CREATE PROCEDURE GETRASHODBYFACE(
    ID_PERIOD INTEGER,
    ID_FACE INTEGER)
RETURNS (
    ID INTEGER,
    OPERDATE DATE,
    TIMEMARKER DATE,
    SUMMA DOUBLE PRECISION,
    REST DOUBLE PRECISION,
    ID_CUR INTEGER,
    CURNAME CHAR( 5 ),
    COLOUR INTEGER,
    CURS DOUBLE PRECISION,
    ID_OFFICE INTEGER,
    OFFICE CHAR( 50 ),
    FIRM CHAR( 50 ),
    DEPART CHAR( 50 ),
    FIRM_FIRST CHAR( 50 ),
    DEPART_FIRST CHAR( 50 ),
    ID_FIRM INTEGER,
    ID_DEPART INTEGER,
    ID_FIRM_FIRST INTEGER,
    ID_DEPART_FIRST INTEGER,
    ISCASH SMALLINT,
    ISCASHSTR CHAR( 3 ),
    MEMO CHAR( 100 ),
    ID_DESTVENDOR INTEGER)
AS
declare variable allreports DOUBLE PRECISION;
declare variable restbefore DOUBLE PRECISION;
declare variable recperiod INTEGER;
begin
  if (:id_face =  55 ) then BEGIN /* Ветка только для расхода денег */ 
  FOR SELECT OPERS.ID, OPERS.OPERDATE, OPERS.TIMEMARKER, OPERS.SUMMA,
    OPERS.ID_CUR, Currency.SName, Currency.Colour, OPERS.CURS, OPERS.ID_OFFICE,
    Offices.Name, opers.id_firm, opers.id_depart, opers.id_firm_first,
    opers.id_depart_first, OPERS.ISCASH, OPERS.MEMO, OPERS.ID_DESTVENDOR,
    OPERS.ID_PERIOD
    FROM Opers, Offices, Currency
    WHERE OPERS.ID_FACE = :ID_FACE
      and OPERS.FACE_TYPE IN ( 0 , 3 )
      and Opers.ID_Period = :ID_Period
      and OPERS.SUMMA <  0 
      and OPERS.ID_Office = Offices.ID
      and OPERS.ID_Cur = Currency.ID
    PLAN JOIN (OPERS INDEX (OPERS_IDX5),
      CURRENCY INDEX (RDB$PRIMARY11),OFFICES INDEX (RDB$PRIMARY30))
     /*PLAN SORT (JOIN (OPERS INDEX (OPERS_IDX5),
      CURRENCY INDEX (RDB$PRIMARY11),OFFICES INDEX (RDB$PRIMARY30)))
    ORDER BY OPERS.OPERDATE, OPERS.TIMEMARKER*/ 
    INTO :ID, :OPERDATE, :TIMEMARKER, :SUMMA, :ID_CUR, :Curname, :COLOUR, :CURS,
    :Id_Office, :Office, :Id_Firm, :Id_Depart, :Id_Firm_First, :Id_Depart_First,
    :ISCASH, :MEMO, :ID_DESTVENDOR, :RecPeriod DO
    begin
      Summa = -:Summa;
       /* Проверка на остаток на начало периода не нужна проводкам этого периода */ 
      if (:RecPeriod < :ID_Period) then begin
         /* Остаток на начало текущего периода */ 
        SELECT Zero(sum(REPORTS.SUMMA))
          FROM REPORTS
          WHERE REPORTS.ID_OPER = :ID and REPORTS.ID_PERIOD < :Id_period
            and REPORTS.REPORT_TYPE =  0   /*<> 1*/   /* Выбрасываем удаленные */ 
          PLAN (REPORTS INDEX (REPORTS_IDX4))
          INTO :allreports;
        restbefore = DRound(:summa - :allreports,  2 );
      end
      else
        restbefore = :Summa;
       /* Оставались ли деньги на начало текущего периода */ 
       /*if (restbefore > 0) then begin*/ 
         /* Остаток на конец текущего периода */ 
        SELECT Zero(sum(REPORTS.SUMMA))
          FROM REPORTS
          WHERE REPORTS.ID_OPER = :ID and REPORTS.ID_PERIOD  /*<*/ = :Id_period
            and REPORTS.REPORT_TYPE =  0   /*<> 1*/   /* Выбрасываем удаленные */ 
          PLAN (REPORTS INDEX (REPORTS_IDX4))
          INTO :allreports;
        rest = DRound(:summa - :allreports,  2 );
      if ((restbefore <>  0 ) or (rest <  0 )) then begin
        if (iscash =  1 ) then
          iscashstr = '';
        else
          iscashstr = 'Б/Н';
        Firm = '';
        select name from firms where firms.id = :id_firm
          into :Firm;
        Firm_First = '';
        select name from firms where firms.id = :id_firm_first
          into :Firm_First;
        Depart = '';
        select name from departs where departs.id = :id_depart
          into :Depart;
        Depart_First = '';
        select name from departs where departs.id = :id_depart_first
          into :Depart_First;
        suspend;
      end
       /*end*/ 
    end
  END ELSE BEGIN  /* Ветка для прочих лиц */ 
  FOR SELECT OPERS.ID, OPERS.OPERDATE, OPERS.TIMEMARKER, OPERS.SUMMA,
    OPERS.ID_CUR, Currency.SName, Currency.Colour, OPERS.CURS, OPERS.ID_OFFICE,
    Offices.Name, opers.id_firm, opers.id_depart, opers.id_firm_first,
    opers.id_depart_first, OPERS.ISCASH, OPERS.MEMO, OPERS.ID_DESTVENDOR,
    OPERS.ID_PERIOD
    FROM Opers, Offices, Currency
    WHERE OPERS.ID_FACE = :ID_FACE
      and OPERS.FACE_TYPE IN ( 0 , 3 )
      and Opers.ID_Period <= :ID_Period
      and OPERS.SUMMA <  0 
      and OPERS.ID_Office = Offices.ID
      and OPERS.ID_Cur = Currency.ID
    PLAN JOIN (OPERS INDEX (OPERS_IDX5),
      CURRENCY INDEX (RDB$PRIMARY11),OFFICES INDEX (RDB$PRIMARY30))
     /*PLAN SORT (JOIN (OPERS INDEX (OPERS_IDX5),
      CURRENCY INDEX (RDB$PRIMARY11),OFFICES INDEX (RDB$PRIMARY30)))
    ORDER BY OPERS.OPERDATE, OPERS.TIMEMARKER*/ 
    INTO :ID, :OPERDATE, :TIMEMARKER, :SUMMA, :ID_CUR, :Curname, :COLOUR, :CURS,
    :Id_Office, :Office, :Id_Firm, :Id_Depart, :Id_Firm_First, :Id_Depart_First,
    :ISCASH, :MEMO, :ID_DESTVENDOR, :RecPeriod DO
    begin
      Summa = -:Summa;
       /* Проверка на остаток на начало периода не нужна проводкам этого периода */ 
      if (:RecPeriod < :ID_Period) then begin
         /* Остаток на начало текущего периода */ 
        SELECT Zero(sum(REPORTS.SUMMA))
          FROM REPORTS
          WHERE REPORTS.ID_OPER = :ID and REPORTS.ID_PERIOD < :Id_period
            and REPORTS.REPORT_TYPE =  0   /*<> 1*/   /* Выбрасываем удаленные */ 
          PLAN (REPORTS INDEX (REPORTS_IDX4))
          INTO :allreports;
        restbefore = DRound(:summa - :allreports,  2 );
      end
      else
        restbefore = :Summa;
       /* Оставались ли деньги на начало текущего периода */ 
       /*if (restbefore > 0) then begin*/ 
         /* Остаток на конец текущего периода */ 
        SELECT Zero(sum(REPORTS.SUMMA))
          FROM REPORTS
          WHERE REPORTS.ID_OPER = :ID and REPORTS.ID_PERIOD <= :Id_period
            and REPORTS.REPORT_TYPE =  0   /*<> 1*/   /* Выбрасываем удаленные */ 
          PLAN (REPORTS INDEX (REPORTS_IDX4))
          INTO :allreports;
        rest = DRound(:summa - :allreports,  2 );
      if ((restbefore <>  0 ) or (rest <  0 )) then begin
        if (iscash =  1 ) then
          iscashstr = '';
        else
          iscashstr = 'Б/Н';
        Firm = '';
        select name from firms where firms.id = :id_firm
          into :Firm;
        Firm_First = '';
        select name from firms where firms.id = :id_firm_first
          into :Firm_First;
        Depart = '';
        select name from departs where departs.id = :id_depart
          into :Depart;
        Depart_First = '';
        select name from departs where departs.id = :id_depart_first
          into :Depart_First;
        suspend;
      end
       /*end*/ 
    end
    END
end