projekt kivitendo

Dat query-Ding:
Wenn man $q_drcr ausgibt erhält man diese SQL-Abfrage. Grundproblem ist, dass die Datumsgrenze über datepaid im Nebenbuch passiert und deswegen eine Eingrenzung auf Teilzahlungen nicht möglich ist:

SELECT
         (SELECT SUM(ac.amount) * -1
          FROM acc_trans ac
          JOIN chart c ON (c.id = ac.chart_id)
..........
          WHERE  1 = 1  AND(ac.trans_id IN (SELECT id FROM ar WHERE datepaid>= '01.01.2016' AND datepaid<= '31.12.2016' UNION SELECT id FROM ap WHERE datepaid>= '01.01.2016' AND datepaid<= '31.12.2016' UNION SELECT id FROM gl WHERE transdate>= '01.01.2016' AND transdate<= '31.12.2016')) AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL).
............
............
............
          AND (ac.amount < 0)
          AND (c.accno = ?)) AS debit,

         (SELECT SUM(ac.amount)
          FROM acc_trans ac
          JOIN chart c ON (c.id = ac.chart_id)
..........
          WHERE  1 = 1  AND(ac.trans_id IN (SELECT id FROM ar WHERE datepaid>= '01.01.2016' AND datepaid<= '31.12.2016' UNION SELECT id FROM ap WHERE datepaid>= '01.01.2016' AND datepaid<= '31.12.2016' UNION SELECT id FROM gl WHERE transdate>= '01.01.2016' AND transdate<= '31.12.2016')) AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL).
............
............
............
          AND ac.amount > 0
          AND c.accno = ?) AS credit,
        (SELECT SUM(ac.amount)
         FROM acc_trans ac
         JOIN chart c ON (ac.chart_id = c.id)
.........
         WHERE  1 = 1  AND(ac.trans_id IN (SELECT id FROM ar WHERE datepaid>= '01.01.2016' AND datepaid<= '31.12.2016' UNION SELECT id FROM ap WHERE datepaid>= '01.01.2016' AND datepaid<= '31.12.2016' UNION SELECT id FROM gl WHERE transdate>= '01.01.2016' AND transdate<= '31.12.2016'))  AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL).
...........
...........
...........
         AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)) AS saldo,

        (SELECT SUM(ac.amount)
         FROM acc_trans ac
         JOIN chart c ON (ac.chart_id = c.id)
.........
         WHERE  1 = 1  AND(ac.trans_id IN (SELECT id FROM ar WHERE datepaid>= '01.01.2016' AND datepaid<= '31.12.2016' UNION SELECT id FROM ap WHERE datepaid>= '01.01.2016' AND datepaid<= '31.12.2016' UNION SELECT id FROM gl WHERE transdate>= '01.01.2016' AND transdate<= '31.12.2016')) AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL).
...........
...........
...........
         AND ac.amount > 0
         AND c.accno = ?) AS sum_credit,

        (SELECT SUM(ac.amount)
         FROM acc_trans ac
         JOIN chart c ON (ac.chart_id = c.id)
.........
         WHERE  1 = 1  AND(ac.trans_id IN (SELECT id FROM ar WHERE datepaid>= '01.01.2016' AND datepaid<= '31.12.2016' UNION SELECT id FROM ap WHERE datepaid>= '01.01.2016' AND datepaid<= '31.12.2016' UNION SELECT id FROM gl WHERE transdate>= '01.01.2016' AND transdate<= '31.12.2016')) AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL).
...........
...........
...........
         AND ac.amount < 0
         AND c.accno = ?) AS sum_debit,

        (SELECT max(ac.transdate) FROM acc_trans ac
        JOIN chart c ON (ac.chart_id = c.id)

Die richtige Eingrenzung wäre hier, über acc_trans.transdate für diesen Fall, dann werden die Teilzahlungen auch korrekt berechnet.
Das fiese Problem hieran ist, dass die Teilzahlungen entsprechend noch für die anzuzeigenden Konten angepasst werden müssen.
Die Summe über den Betrag welches auf dem Konto gebucht ist, ist somit schlicht falsch.
Richtigerweise müsste die Summe über die Zahlungen datumsgenau berechnet werden und diese Summe statt der Gesamt-Summe des Kontos angezeigt werden.

Also:
SKR03
1400 800,- am 20.6.
Za1 300,- am 24.6.
Za2 500,- am 20.7.

Die SuSa-Auswertung im Juni müsste 300,- auf 1400 angeben und
die SuSa-Auswertung im Juli müsste 500,- auf 1400 angeben.

Entweder lässt man die Abfrage jetzt so durchlaufen und patcht für den Fall Teilzahlungen bei Ist-Versteuerung noch eine Korrekturberechnung dran oder man muss prinzipiell die Berichte umschreiben.

Korrekturberechnung könnte in diesem Block weiter unten laufen:

  foreach my $accno (sort keys %trb) {

Ich scheu mich da eigentlich die Abfragen weiter zu flicken. Von daher an dieser Stelle erstmal die Frage, wie man den Bericht überhaupt in SQL abbilden kann.

Mein erster naiver Ansatz:

      qq| AND(ac.acc_trans_id IN (SELECT acc_trans_id FROM acc_trans WHERE transdate>= $fromdate AND transdate<= $todate AND chart_link ilike '%paid%'))

liefert korrekte Teilzahlungen für die Umlaufkonten, blendet aber natürlich alle wirklichen anderen Konten dann einfach aus.

Hat hier einer eine gute SQL-Idee wie man das abfragen kann?

Ich komm hier spontan auf nichts intelligentes, folgender Datenbestand:

select c.accno,c.description, ac.amount,transdate,trans_id,acc_trans_id,ac.chart_link from acc_trans ac left join chart c on (c.id = ac.chart_id);
 accno |            description             |   amount   | transdate  | trans_id | acc_trans_id |           chart_link            
-------+------------------------------------+------------+------------+----------+--------------+---------------------------------
 8400  | Erlöse 16%/19% USt.                |   64.00000 | 2016-07-12 |        2 |            2 | AR_amount:IC_sale:IC_income
 1776  | Umsatzsteuer 19 %                  |   12.16000 | 2016-07-12 |        2 |            3 | AR_tax:IC_taxpart:IC_taxservice
 1400  | Ford. a.Lieferungen und Leistungen |  -76.16000 | 2016-07-12 |        2 |            4 | AR
 1400  | Ford. a.Lieferungen und Leistungen | -938.91000 | 2016-07-20 |        3 |           13 | AR
 1776  | Umsatzsteuer 19 %                  |  149.91000 | 2016-07-20 |        3 |           14 | AR_tax:IC_taxpart:IC_taxservice
 8400  | Erlöse 16%/19% USt.                |  789.00000 | 2016-07-20 |        3 |           15 | AR_amount:IC_sale:IC_income
 1400  | Ford. a.Lieferungen und Leistungen |  300.00000 | 2016-06-08 |        3 |           16 | AR
 1000  | Kasse                              | -300.00000 | 2016-06-08 |        3 |           17 | AR_paid:AP_paid
 1400  | Ford. a.Lieferungen und Leistungen |  638.91000 | 2016-07-20 |        3 |           18 | AR
 1000  | Kasse                              | -638.91000 | 2016-07-20 |        3 |           19 | AR_paid:AP_paid
(10 rows)

In Pseudo-SQL bräuchte ich in etwa sowas:

(SELECT SUM(ac.amount) * -1 - SUM(ac.not_yet_paid)                                                                                                              
          FROM acc_trans ac
          JOIN chart c ON (c.id = ac.chart_id) WHERE 1=1 AND c.accno='1400');

Um mich und andere davor zu bewahren nochmal irgendwas mal eben schnell in RP.pm zu machen, hier das was ich bisher reingefrickelt habe:

sub get_accounts_g {
(..)

    $where .= $form->{cash_partial_payments} ?
      qq| AND(ac.trans_id IN (SELECT trans_id FROM acc_trans WHERE transdate>= $fromdate AND transdate<= $todate AND chart_link ilike '%paid%'))  AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL) | :
      qq| AND(ac.trans_id IN (SELECT id FROM ar WHERE datepaid>= $fromdate AND datepaid<= $todate UNION SELECT id FROM ap WHERE datepaid>= $fromdate AND datepaid<= $todate UNION SELECT id FROM gl WHERE transdate>= $fromdate AND transdate<= $todate)) AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL) |;
    $saldowhere .=  $form->{cash_partial_payments} ?
      qq| AND(ac.trans_id IN (SELECT trans_id FROM acc_trans WHERE transdate>= $fromdate AND transdate<= $todate AND chart_link ilike '%paid%'))  AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL) | :
 qq| AND(ac.trans_id IN (SELECT id FROM ar WHERE datepaid>= $fromdate AND datepaid<= $todate UNION SELECT id FROM ap WHERE datepaid>= $fromdate AND datepaid<= $todate UNION SELECT id FROM gl WHERE transdate>= $fromdate AND transdate<= $todate))  AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL) |;

    $sumwhere .=  $form->{cash_partial_payments} ?
      qq| AND(ac.trans_id IN (SELECT trans_id FROM acc_trans WHERE transdate>= $fromdate AND transdate<= $todate AND chart_link ilike '%paid%'))  AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL) | :
qq| AND(ac.trans_id IN (SELECT id FROM ar WHERE datepaid>= $fromdate AND datepaid<= $todate UNION SELECT id FROM ap WHERE datepaid>= $fromdate AND datepaid<= $todate UNION 
SELECT id FROM gl WHERE transdate>= $fromdate AND transdate<= $todate)) AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL) |;

(..)
    push @{ $form->{TB} }, $ref;

  }

  if ($form->{method} eq "cash" && $form->{cash_partial_payments}) {
    $main::lxdebug->message(0, 'hier' . Dumper($form->{TB}));
    # hotfix for #190. If we have cash report. we need to recalculate
    # the charts which have multiple payments, if they are not fully
    # paid in the selected report period

    # fetch all acc_trans trans_id entries, which have more than one payment
    # and have trans_id in the current period
    my $query_check_multiple_payments =
      qq|SELECT trans_id from acc_trans where chart_link ilike '%paid%'
          AND (NOT ob_transaction OR ob_transaction IS NULL)
          AND (NOT cb_transaction OR cb_transaction IS NULL)
          AND trans_id IN (SELECT trans_id from acc_trans
                            WHERE transdate >= $fromdate AND transdate <= $todate)
          GROUP BY trans_id having count (*) > 1|;

    my $prep_qcm_payments = prepare_query($form, $dbh, $query_check_multiple_payments);
    do_statement($form, $prep_qcm_payments, $query_check_multiple_payments);
    while (my $current_trans_id = $prep_qcm_payments->fetchrow_array) {
      my $query_get_single_payments =
       qq|SELECT amount, chart_id from acc_trans where trans_id = ?
           AND chart_link ilike '%paid%' AND transdate >= ? AND transdate <= ?|;
      my $prep_gs_payments = prepare_query($form, $dbh, $query_get_single_payments);
      do_statement($form, $prep_gs_payments, $query_get_single_payments,
                    $current_trans_id, $fromdate, $todate);
      my $paid_amount_current_period;
      while (my ($current_amount, $current_chart_id) = $prep_gs_payments->fetchrow_array) {
        $paid_amount_current_period +=  abs($current_amount);
      }
      # calculate percentage of total. fetch total from nebenbuch
      my $query_get_real_total =
       qq| (select amount from ar where id = ?) UNION (select amount from ap where id = ?)|;
      my $prep_get_real_total = prepare_query($form, $dbh, $query_get_real_total);
      do_statement($form, $prep_get_real_total,$query_get_real_total, $current_trans_id, $current_trans_id);
      my $real_total = $prep_get_real_total->fetchrow_array;

      # next if all entries are already in the current period
      $main::lxdebug->message(0, 'hier divison bei 0' . $real_total . 'mit:' . $current_trans_id) if ($real_total == 0);
      next if ($real_total == $paid_amount_current_period || $real_total == 0);

      # calcalute percentage
      my $percentage_paid = $paid_amount_current_period / $real_total;
      my $amount_not_in_period = $real_total - $paid_amount_current_period;

      # get all involved accnos for this transaction and distract percentage value
      my $query_get_all_accnos =
        qq|SELECT accno from chart where id in (SELECT chart_id from acc_trans WHERE trans_id = ?)|;
      my $prep_get_all_accnos = prepare_query($form, $dbh, $query_get_all_accnos);
      do_statement($form, $prep_get_all_accnos, $query_get_all_accnos, $current_trans_id);
      while (my $current_accno = $prep_get_all_accnos->fetchrow_array) {
        foreach $ref (@{ $form->{TB} }) {
          if ($current_accno eq $ref->{accno}) {
            $ref->{debit}  *= $percentage_paid unless $ref->{link} eq 'AR'; #=~ m/(AR|AP)/;
            $ref->{credit} *= $percentage_paid unless $ref->{link} eq 'AP'; #=~ m/(AR|AP)/;
            #$ref->{soll_saldo}  -= $amount_not_in_period;
            #$ref->{haben_saldo} -= $amount_not_in_period;
          #  $ref->{soll_kumuliert}  *= $percentage_paid;
          #  $ref->{haben_kumuliert} *= $percentage_paid;
          }
        }
      }
    }
  } # end hotfix #190

Wie man sieht ist es ein ätzendes Gewurschelt in längeren SQL-Queries, bzw. noch Schlimmer ein nachträgliches Berechnen der Teil-Zahlungen im Array.
Funktioniert nur für Zeiträume und nicht für die Salden und an der Stelle hab ich abgebrochen.

Falls nochmal die Anfrage kommen sollte, würde ich den Aufwand eher höher beziffern, da die Funktion einfach nochmal komplett überarbeitet werden muss.