create or replace function CalcBalance(integer) returns varchar as 
   # Параметр - код валюты баланса (ID согласно справочника spr_units)
   #  1 . Даты начала бухгалтерского года, начала и конца бухгалтерского периода
   spi_exec "select date_part(''year'', BBeginOfPeriodStable()) as cyear, to_char(BBeginOfPeriodStable(), ''mm dd yyyy'') as bbeginofperiod, to_char(BEndOfPeriodStable(), ''mm dd yyyy'') as bendofperiod"
   set dYearBegin "''1 1 $cyear''"
   set BBeginOfPeriod ''$bbeginofperiod''
   set BEndOfPeriod ''$bendofperiod''
   #  2 . Несуммированная таблица с игнорированием оборотов между балансовыми и забалансовыми счетами
   set stQuery "CREATE local temp table BalanceNS (bscode, analiticsns, oodns, ookns, tdns, tkns, ctdns, ctkns) AS  \
     SELECT bscode, analitics as analiticsns, (case when summa > 0 then summa else 0.0 end) as oodns, (case when summa < 0 then -summa else 0.0 end) as ookns, 0.0, 0.0, 0.0, 0.0 \
     FROM acc_rests where date_beg = $BBeginOfPeriod and id_unit = $1 UNION ALL  \
     SELECT bsdb as bscode, db_analitics as analiticsns, 0.0, 0.0, sum(case when datinp >= $BBeginOfPeriod then summa else 0.0 end) as tdns, 0.0, sum(summa) as ctdns, 0.0 \
     FROM acc_transactions \
     WHERE datinp >= $dYearBegin and datinp <= $BEndOfPeriod and (substr(bsdb, 1, 1) = ''0'' or substr(bskr, 1, 1) <> ''0'') and id_unit = $1 \
     GROUP BY bsdb, db_analitics UNION ALL \
     SELECT bskr as bscode, kr_analitics as analiticsns, 0.0, 0.0, 0.0, sum(case when datinp >= $BBeginOfPeriod then summa else 0.0 end) as tkns, 0.0, sum(summa) as ctkns \
     FROM acc_transactions \
     WHERE datinp >= $dYearBegin and datinp <= $BEndOfPeriod and (substr(bskr, 1, 1) = ''0'' or substr(bsdb, 1, 1) <> ''0'') and id_unit = $1 \
     GROUP BY bskr, kr_analitics"
   spi_exec $stQuery
   #  3 . Суммированная таблица с аналитиками
   #  3 . 1  Удаление предыдущих таблиц
   set stQuery {select tablename from pg_tables where tablename = ''balance'' and tableowner = getpgusername()}
   spi_exec $stQuery
   if {[info exists tablename]} then {
      spi_exec "drop table Balance cascade"
   }
   set stQuery {select tablename from pg_tables where tablename = ''balancean'' and tableowner = getpgusername()}
   spi_exec $stQuery
   if {[info exists tablename]} then {
      spi_exec "drop table BalanceAn cascade"
   }
   #  3 . 2  Создание текущей таблицы с форматированием аналитик согласно плана счетов
   set stQuery {CREATE local temp table BalanceAn (bscode, analitics, ood, ook, td, ctd, tk, ctk, nod, nok, cp, fturn, cr) AS \
      SELECT BalanceNS.bscode, AnaliticsFormat(analiticsns, fturn, analitic1, analitic2, analitic3, analitic4) as analitics, sum(oodns) as ood, sum(ookns) as ook, sum(tdns) as td, sum(ctdns) as ctd, sum(tkns) as tk, sum(ctkns) as ctk,  0 . 0 ,  0 . 0 , cp, fturn, cr \
      FROM BalanceNS, account_plan where BalanceNS.bscode = account_plan.bscode group by BalanceNS.bscode, AnaliticsFormat(analiticsns, fturn, analitic1, analitic2, analitic3, analitic4), cp, fturn, cr}
   spi_exec $stQuery
   spi_exec "drop table BalanceNS cascade"
   #  4 . Расчет остатков на конец периода по всем счетам и аналитикам
   set stQuery {update BalanceAn set nod = (case when ood - ook + td - tk >=  0  then ood - ook + td - tk else  0 . 0  end), \
         nok = (case when ood - ook + td - tk <  0  then -(ood - ook + td - tk) else  0 . 0  end)}
   spi_exec $stQuery
   #  5 . Создание таблицы оборотного баланса
   set stQuery {CREATE local temp table Balance (bscode, ood, ook, td, ctd, tk, ctk, nod, nok, cp, fturn, cr) AS \
      SELECT bscode, sum(ood) as ood, sum(ook) as ook, sum(td) as td, sum(ctd) as ctd, sum(tk) as tk, sum(ctk) as ctk, sum(nod) as nod, sum(nok) as nok, cp, fturn, cr \
      FROM BalanceAn group by bscode, cp, fturn, cr}
   spi_exec $stQuery
   #  6 . Корректировка остатков в зависимости от признаков счетов (account_plan.cp, account_plan.fturn)
   set stQuery {update Balance set \
      ood = (case when fturn and cp =  3  then ood else (case when cp =  1  or (cp =  3  and ood > ook) then ood - ook else  0 . 0  end) end), \
      ook = (case when fturn and cp =  3  then ook else (case when cp =  2  or (cp =  3  and ood < ook) then ook - ood else  0 . 0  end) end), \
      nod = (case when fturn and cp =  3  then nod else (case when cp =  1  or (cp =  3  and nod > nok) then nod - nok else  0 . 0  end) end), \
      nok = (case when fturn and cp =  3  then nok else (case when cp =  2  or (cp =  3  and nod < nok) then nok - nod else  0 . 0  end) end)}
   spi_exec $stQuery
   return BalanceAn,Balance
 language 'pltcl';