/ - Diff - projekt kivitendo - wissen

« Zurück | Weiter »

Revision 7679dfa4

Von Sven Schöling vor mehr als 17 Jahren hinzugefügt

ID 7679dfa4ba64c4cc24a027fa5bdfd5e616ca92a8
Vorgänger e1e19a48
Nachfolger d7c969e4

Bugfix 624 und 93.
G+V sollte jetzt keine Betraege mehr anzeigen sondern die richtigen Werte. Sind sie negativ sollte der Buchhalter sich Gedanken machen.

     #=====================================================================
     ###=====================================================================
     # LX-Office ERP
     # Copyright (C) 2004
     # Based on SQL-Ledger Version 2.1.9
-...
     package RP;
     use SL::DBUtils;
     use Data::Dumper;
     sub balance_sheet {
       $main::lxdebug->enter_sub();
-...
              $dpt_join
              WHERE $where
                $glwhere
                $dpt_from
                $dpt_where
                $category
                  AND NOT ((c.link = 'AR') OR (c.link = 'AP'))
                $project
-...
       if ($fromdate) {
         $fromdate = conv_dateq($fromdate);
         if ($form->{method} eq 'cash') {
           $subwhere .= " AND (transdate >= $fromdate)";
           $glwhere = " AND (ac.transdate >= $fromdate)";
           $prwhere = " AND (ar.transdate >= $fromdate)";
           $subwhere .= " AND (transdate    >= $fromdate)";
           $glwhere   = " AND (ac.transdate >= $fromdate)";
           $prwhere   = " AND (ar.transdate >= $fromdate)";
         } else {
           $where .= " AND (ac.transdate >= $fromdate)";
           $where    .= " AND (ac.transdate >= $fromdate)";
+        }
+      }
       if ($todate) {
         $todate = conv_dateq($todate);
         $where    .= " AND (ac.transdate <= $todate)";
         $subwhere .= " AND (transdate <= $todate)";
         $prwhere  .= " AND (ar.transdate <= $todate)";
         $subwhere   .= " AND (transdate    <= $todate)";
         $where      .= " AND (ac.transdate <= $todate)";
         $prwhere    .= " AND (ar.transdate <= $todate)";
+      }
       if ($department_id) {
-...
       if ($form->{method} eq 'cash') {
         $query =
           qq|SELECT sum(ac.amount) AS amount, c.$category
           qq|
            SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category
              FROM acc_trans ac
              JOIN chart c ON (c.id = ac.chart_id)
              JOIN ar a ON (a.id = ac.trans_id)
              $dpt_join
              WHERE $where
                $dpt_where
                AND ac.trans_id IN
+                 (
                    SELECT trans_id
                    FROM acc_trans
                    JOIN chart ON (chart_id = id)
                    WHERE (link LIKE '%AR_paid%')
                      $subwhere
+                 )
              WHERE $where $dpt_where
                AND ac.trans_id IN ( SELECT trans_id FROM acc_trans JOIN chart ON (chart_id = id) WHERE (link LIKE '%AR_paid%') $subwhere)
                $project
              GROUP BY c.$category
              GROUP BY c.$category
              UNION
              SELECT sum(ac.amount) AS amount, c.$category
              SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category
              FROM acc_trans ac
              JOIN chart c ON (c.id = ac.chart_id)
              JOIN ap a ON (a.id = ac.trans_id)
              $dpt_join
              WHERE $where
                $dpt_where
                AND ac.trans_id IN
+                 (
                    SELECT trans_id
                    FROM acc_trans
                    JOIN chart ON (chart_id = id)
                    WHERE (link LIKE '%AP_paid%')
                      $subwhere
+                 )
              WHERE $where $dpt_where
                AND ac.trans_id IN ( SELECT trans_id FROM acc_trans JOIN chart ON (chart_id = id) WHERE (link LIKE '%AP_paid%') $subwhere)
                $project
              GROUP BY c.$category
              GROUP BY c.$category
              UNION
              SELECT sum(ac.amount) AS amount, c.$category
              SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category
              FROM acc_trans ac
              JOIN chart c ON (c.id = ac.chart_id)
              JOIN gl a ON (a.id = ac.trans_id)
              $dpt_join
              WHERE $where
                $glwhere
                $dpt_from
              WHERE $where $dpt_where $glwhere
                AND NOT ((c.link = 'AR') OR (c.link = 'AP'))
                $project
              GROUP BY c.$category |;
              $project_union
             GROUP BY c.$category
             |;
         if ($form->{project_id}) {
           $query .= qq|
           $project_union = qq|
              UNION
              SELECT SUM(ac.sellprice * ac.qty) AS amount, c.$category
              SELECT SUM(ac.sellprice * ac.qty * chart_category_to_sgn(c.category)) AS amount, c.$category
              FROM invoice ac
              JOIN ar a ON (a.id = ac.trans_id)
              JOIN parts p ON (ac.parts_id = p.id)
              JOIN chart c on (p.income_accno_id = c.id)
              $dpt_join
              WHERE (c.category = 'I')
                $prwhere
                $dpt_where
                AND ac.trans_id IN
+                 (
                    SELECT trans_id
                    FROM acc_trans
                    JOIN chart ON (chart_id = id)
                    WHERE (link LIKE '%AR_paid%')
                      $subwhere
+                 )
              WHERE (c.category = 'I') $prwhere $dpt_where
                AND ac.trans_id IN ( SELECT trans_id FROM acc_trans JOIN chart ON (chart_id = id) WHERE (link LIKE '%AR_paid%') $subwhere)
                $project
              GROUP BY c.$category
              GROUP BY c.$category
              UNION
              SELECT SUM(ac.sellprice) AS amount, c.$category
              SELECT SUM(ac.sellprice * chart_category_to_sgn(c.category)) AS amount, c.$category
              FROM invoice ac
              JOIN ap a ON (a.id = ac.trans_id)
              JOIN parts p ON (ac.parts_id = p.id)
              JOIN chart c on (p.expense_accno_id = c.id)
              $dpt_join
              WHERE (c.category = 'E') $prwhere
                $dpt_where
                AND ac.trans_id IN
+                 (
                    SELECT trans_id
                    FROM acc_trans
                    JOIN chart ON (chart_id = id)
                    WHERE (link LIKE '%AP_paid%')
                      $subwhere
+                 )
              WHERE (c.category = 'E') $prwhere $dpt_where
                AND ac.trans_id IN ( SELECT trans_id FROM acc_trans JOIN chart ON (chart_id = id) WHERE (link LIKE '%AP_paid%') $subwhere)
              $project
              GROUP BY c.$category |;
              GROUP BY c.$category
              |;
+        }
       } else {                      # if ($form->{method} eq 'cash')
-...
+        }
         $query = qq|
             SELECT sum(ac.amount) AS amount, c.$category
             SELECT sum(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category
             FROM acc_trans ac
             JOIN chart c ON (c.id = ac.chart_id)
             $dpt_join
-...
           $query .= qq|
             UNION
             SELECT SUM(ac.sellprice * ac.qty) AS amount, c.$category
             SELECT SUM(ac.sellprice * ac.qty * chart_category_to_sgn(c.category)) AS amount, c.$category
             FROM invoice ac
             JOIN ar a ON (a.id = ac.trans_id)
             JOIN parts p ON (ac.parts_id = p.id)
-...
             UNION
             SELECT SUM(ac.sellprice * ac.qty) * -1 AS amount, c.$category
             SELECT SUM(ac.sellprice * ac.qty * chart_category_to_sgn(c.category)) AS amount, c.$category
             FROM invoice ac
             JOIN ap a ON (a.id = ac.trans_id)
             JOIN parts p ON (ac.parts_id = p.id)
-...
       my $accno;
       my $ref;
       #print $query;
       my $sth = prepare_execute_query($form, $dbh, $query);
       while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
         if ($ref->{amount} < 0) {
           $ref->{amount} *= -1;
+        }
         if ($category eq "pos_bwa") {
           if ($last_period) {
             $form->{ $ref->{$category} }{kumm} += $ref->{amount};

            $dpt_join
            WHERE 1 = 1
            $where
            $dpt_from
            $dpt_where
            $project
            GROUP BY tk.pos_ustva
-...
            $dpt_join
            WHERE 1 = 1
            $where
            $dpt_from
            $dpt_where
            $project
            GROUP BY tk.pos_ustva

     -- @tag: chart_category_to_sgn
     -- @description: Fuegt eine Hilfsfunktion ein mit der die interne Reprasentation der Konten (Haben positiv) in die Mehrungsrepraesentation gewandelt werden kann.
     -- @depends:
     CREATE OR REPLACE FUNCTION chart_category_to_sgn(CHARACTER(1))
     RETURNS INTEGER
     LANGUAGE SQL
     AS 'SELECT  1 WHERE $1 IN (''I'', ''L'', ''Q'')
           UNION
         SELECT -1 WHERE $1 IN (''E'', ''A'')';

Auch abrufbar als: Unified diff

Projekt

Allgemein

Profil

projekt kivitendo

Revision 7679dfa4

Von Sven Schöling vor mehr als 17 Jahren hinzugefügt