C# Przeliczanie struktury drzewiastej

0

Jest sobie struktura w bazie danych
9-010-011
-012
-100
-200-01
-02
-400

Typowy plan kont
Każde ostatnie "konto" ma zapisy które muszą być przeliczone jako pierwsze.
Do tej pory robiłem to pojedynczym sql

UPDATE plankont1 AS plan SET  
  saldown5=(SELECT CASE WHEN ((coalesce(SUM(kwotawn),0)+coalesce(plan.otwarciewn,0))-(coalesce(SUM(kwotama),0)+coalesce(plan.otwarciema,0)))=0 THEN 0   
                        WHEN ((coalesce(SUM(kwotawn),0)+coalesce(plan.otwarciewn,0))-(coalesce(SUM(kwotama),0)+coalesce(plan.otwarciema,0)))>0 THEN 
                              (coalesce(SUM(kwotawn),0)+coalesce(plan.otwarciewn,0))-(coalesce(SUM(kwotama),0)+coalesce(plan.otwarciema,0))   
                        WHEN ((coalesce(SUM(kwotawn),0)+coalesce(plan.otwarciewn,0))-(coalesce(SUM(kwotama),0)+coalesce(plan.otwarciema,0)))<0 THEN 0  
                   END from ksiegizapisy1 where rpad(numerkonta,10, 'X') LIKE CASE WHEN kontonad='9'  THEN plan.numerkonta||'%'                                                                                          
                                                                                   WHEN kontonad<>'9' THEN rpad(plan.numerkonta,10, 'X')||'%'                                                                                     
                                                                              END                                                 
                                                                              and ksiegizapisy1.rok=2018 and ksiegizapisy1.miesiac<=:mies),
  saldoma5=(SELECT CASE WHEN ((coalesce(SUM(kwotawn),0)+coalesce(plan.otwarciewn,0))-(coalesce(SUM(kwotama),0)+coalesce(plan.otwarciema,0)))=0 THEN 0   
                        WHEN ((coalesce(SUM(kwotawn),0)+coalesce(plan.otwarciewn,0))-(coalesce(SUM(kwotama),0)+coalesce(plan.otwarciema,0)))>0 THEN 0   
                        WHEN ((coalesce(SUM(kwotawn),0)+coalesce(plan.otwarciewn,0))-(coalesce(SUM(kwotama),0)+coalesce(plan.otwarciema,0)))<0 THEN 
                              (coalesce(SUM(kwotama),0)+coalesce(plan.otwarciema,0))-(coalesce(SUM(kwotawn),0)+coalesce(plan.otwarciewn,0)) 
                   END from ksiegizapisy1 where rpad(numerkonta,10, 'X') LIKE CASE WHEN kontonad='9'  THEN plan.numerkonta||'%'                                                                                          
                                                                                   WHEN kontonad<>'9' THEN rpad(plan.numerkonta,10, 'X')||'%'                                                                                     
                                                                              END                                                 
                                                                              and ksiegizapisy1.rok=2018 and ksiegizapisy1.miesiac<=:mies)  ,
  obrotywn5= (SELECT coalesce(SUM(kwotawn),0)                 
  from ksiegizapisy1 where rpad(numerkonta,10, 'X') LIKE                                                                                     
      CASE WHEN kontonad='9'  THEN plan.numerkonta||'%'                                                                                          
           WHEN kontonad<>'9' THEN rpad(plan.numerkonta,10, 'X')||'%'                                                                                          
           END                                                                                     
      and ksiegizapisy1.rok=:rok and ksiegizapisy1.miesiac=:mies)  ,
  obrotyma5= (SELECT coalesce(SUM(kwotama),0)                 
  from ksiegizapisy1 where rpad(numerkonta,10, 'X') LIKE                                                                                     
      CASE WHEN kontonad='9'  THEN plan.numerkonta||'%'                                                                                          
           WHEN kontonad<>'9' THEN rpad(plan.numerkonta,10, 'X')||'%'                                                                                          
           END                                                                                     
      and ksiegizapisy1.rok=:rok and ksiegizapisy1.miesiac=:mies)  ,
  obrotynarawn5= (SELECT coalesce(SUM(kwotawn),0)                 
  from ksiegizapisy1 where rpad(numerkonta,10, 'X') LIKE                                                                                     
      CASE WHEN kontonad='9'  THEN plan.numerkonta||'%'                                                                                          
           WHEN kontonad<>'9' THEN rpad(plan.numerkonta,10, 'X')||'%'                                                                                          
           END                                                                                     
           and ksiegizapisy1.rok=:rok and ksiegizapisy1.miesiac<=:mies)  ,
  obrotynarama5= (SELECT coalesce(SUM(kwotama),0)                 
  from ksiegizapisy1 where rpad(numerkonta,10, 'X') LIKE                                                                                     
      CASE WHEN kontonad='9'  THEN plan.numerkonta||'%'                                                                                          
           WHEN kontonad<>'9' THEN rpad(plan.numerkonta,10, 'X')||'%'                                                                                          
           END                                                                                     
           and ksiegizapisy1.rok=:rok and ksiegizapisy1.miesiac<=:mies)  WHERE rok=:rok 
and (
 numerkonta = '202-1'
 OR numerkonta = '221'
 OR numerkonta = '400'
 OR numerkonta = '202'
);

Jest dość długi i skomplikowany
Query returned successfully: 0 rows affected, 171 msec execution time.

Pytanie czy można zrobić to lepiej/szybciej? Może powinno się to zrobić lepiej.
A może odwzorować na obiekty c#?

Struktura tabeli plankont

CREATE TABLE plankont1
(
  id serial NOT NULL,
  numerkonta character varying(15),
  nazwakonta character varying(200),
  kontonad character varying(15),
  typkonta character varying(20),
  persaldo boolean DEFAULT false,
  otwarciewn numeric(22,2) DEFAULT 0,
  otwarciema numeric(22,2) DEFAULT 0,
  saldown1 numeric(22,2) DEFAULT 0,
  saldoma1 numeric(22,2) DEFAULT 0,
...
  saldown12 numeric(22,2) DEFAULT 0,
  saldoma12 numeric(22,2) DEFAULT 0,
  obrotywn1 numeric(22,2) DEFAULT 0,
  obrotyma1 numeric(22,2) DEFAULT 0,
...
  obrotywn12 numeric(22,2) DEFAULT 0,
  obrotyma12 numeric(22,2) DEFAULT 0,
  obrotynarawn1 numeric(11,2) DEFAULT 0,
  obrotynarama1 numeric(11,2) DEFAULT 0,
...
  obrotynarawn12 numeric(11,2) DEFAULT 0,
  obrotynarama12 numeric(11,2) DEFAULT 0,
  rok integer
)

Struktura tabeli ksiegizapisy1

CREATE TABLE ksiegizapisy1
(
  id serial NOT NULL,
  main_id integer,
  numerkonta character varying(15),
  idd character varying(60),
  miesiac integer,
  rok integer,
  numerdokumentu character varying(40),
  data date,
  kontrachent character varying(255),
  opis character varying(256),
  kontoprze character varying(15),
  kwotawn numeric(15,2),
  kwotama numeric(15,2),
  bufor character varying(15),
  dok_nag_id integer,
  dok_cialo_id integer
)
0

Rozbij konto na analityki w odrębnych kolumnach.

0

Mógłbyś coś szerzej?

0

@karol75: zapytanie jest dosc skomplikowane, watpie zeby komus chcialo sie analizowac zapytanie co do najdrobniejszego szczegolu bez informacji co to ma robic ;)

Opisz dokladnie co chcesz osiagnac (user case) wtedy mozna podpowiedziec czy po stornie bazy czy w C# czy co ;)

0
fasadin napisał(a):

@karol75: zapytanie jest dosc skomplikowane, watpie zeby komus chcialo sie analizowac zapytanie co do najdrobniejszego szczegolu bez informacji co to ma robic ;)

To się tylko wydaje skomplikowane większość to warunki i zabezpieczenie przed wartością null oraz znakiem '9'

Typowy plan kont z księgowości
w skrócie jest to drzewo
korzeń to "9"
które na swoje gałęzie np "100","200","130"
każde z nich może mieć liście lub kolejne gałęzie (dla uproszczenia do 4 poziomów)

korzeń jest sumą gałęzi "100" itd
"100" jest sumą liści lub kolejnych gałęzi
Zadanie przeliczyć sumę zapisów w tabeli księgizapisy1 (odpowiednik liści) i przypisać do gałęzi najniższego poziomu.

SELECT CASE WHEN ((COALESCE(SUM(kwotawn),0)+COALESCE(plan.otwarciewn,0))-(COALESCE(SUM(kwotama),0)+COALESCE(plan.otwarciema,0)))=0 THEN 0   
                        WHEN ((COALESCE(SUM(kwotawn),0)+COALESCE(plan.otwarciewn,0))-(COALESCE(SUM(kwotama),0)+COALESCE(plan.otwarciema,0)))>0 THEN 
                              (COALESCE(SUM(kwotawn),0)+COALESCE(plan.otwarciewn,0))-(COALESCE(SUM(kwotama),0)+COALESCE(plan.otwarciema,0))   
                        WHEN ((COALESCE(SUM(kwotawn),0)+COALESCE(plan.otwarciewn,0))-(COALESCE(SUM(kwotama),0)+COALESCE(plan.otwarciema,0)))<0 THEN 0  
                   END FROM ksiegizapisy1 WHERE rpad(numerkonta,10, 'X') LIKE CASE WHEN kontonad='9'  THEN plan.numerkonta||'%'                                                                                          
                                                                                   WHEN kontonad<>'9' THEN rpad(plan.numerkonta,10, 'X')||'%'                                                                                     
                                                                              END 

w uproszczeniu

SELECT (SUM(kwotawn)+plan.otwarciewn-SUM(kwotama)+plan.otwarciema) AS wynik
       FROM ksiegizapisy1 WHERE warunki

jeżeli wynik większy od zera, równy, mniejszy wtedy odpowiednie when

w warunkach kluczowe jest odpowiedni początek w nazwie numeru konta, sumuje się gdy numer konta na 1 pozycji <>'9'
(Przykładowe numery kont
"200-1"
"202-2-3-1"
pierwsza sekcja maksymalnie do 3 znaków pozostałe bez ograniczeń, rozdzielacz to '-')

przy czym każda gałąź ma zawsze 6 pól
saldown,saldoma,
obrotywn, obrotyma,
obrotywnnara, obrotymanara

Saldo może przyjmować 3 "stany"
dla strony wn

wynik=((SUM(kwotawn)+plan.otwarciewn)-(SUM(kwotama)+plan.otwarciema))
CASE WHEN wynik=0 THEN 0   
     WHEN wynik>0 THEN ((SUM(kwotawn)+plan.otwarciewn)-(SUM(kwotama)+plan.otwarciema))   
     WHEN wynik<0 THEN 0  

dla strony ma
odwrotnie

wynik=((SUM(kwotawn)+plan.otwarciewn)-(SUM(kwotama)+plan.otwarciema))
CASE WHEN wynik=0 THEN 0   
     WHEN wynik>0 THEN 0
     WHEN wynik<0 THEN wynik  

w przypadku gdy wynik = 0 obydwie strony dostają 0.

dalej należy sumować gałęzie wyższego poziomu.

1 użytkowników online, w tym zalogowanych: 0, gości: 1