Bessere Formatierung

Verbessert die Formatierung von Commit 5c5e92bc37819dd9df952068b9e58b47dd0b0de2
(Großschreibung von SQL-Befehlen, Zeilenumbrüche bei langen Sub-Queries)

diff --git a/SL/AP.pm b/SL/AP.pm
index 091e1c27ffafd28225a196860cd67824adf46785..53607fff43761cfc06a749d5c3b39a4da9b94e7e 100644 (file)
--- a/SL/AP.pm
+++ b/SL/AP.pm
@@ -237,7 +237,13 @@ sub post_transaction {
       qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id) | .
       qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, | .
       qq|        (SELECT taxkey_id FROM chart WHERE accno = ?),| . 
       qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id) | .
       qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, | .
       qq|        (SELECT taxkey_id FROM chart WHERE accno = ?),| . 

-      qq|        (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id  FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|;
+      qq|        (SELECT tax_id| . 
+      qq|         FROM taxkeys| . 
+      qq|         WHERE chart_id= (SELECT id | . 
+      qq|                          FROM chart| . 
+      qq|                          WHERE accno = ?)| . 
+      qq|         AND startdate <= ?| . 
+      qq|         ORDER BY startdate DESC LIMIT 1))|;

     @values = ($form->{id}, $form->{AP_amounts}{payables}, $form->{payables},
                conv_date($form->{transdate}), $form->{AP_amounts}{payables}, $form->{AP_amounts}{payables}, conv_date($form->{transdate}));
     do_query($form, $dbh, $query, @values);
     @values = ($form->{id}, $form->{AP_amounts}{payables}, $form->{payables},
                conv_date($form->{transdate}), $form->{AP_amounts}{payables}, $form->{AP_amounts}{payables}, conv_date($form->{transdate}));
     do_query($form, $dbh, $query, @values);


@@ -286,7 +292,13 @@ sub post_transaction {
           qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, taxkey, tax_id) | .
           qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, | .
           qq|        (SELECT taxkey_id FROM chart WHERE accno = ?),| .
           qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, taxkey, tax_id) | .
           qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, | .
           qq|        (SELECT taxkey_id FROM chart WHERE accno = ?),| .

-          qq|        (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id  FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|;
+          qq|        (SELECT tax_id| . 
+          qq|         FROM taxkeys| . 
+          qq|         WHERE chart_id= (SELECT id | . 
+          qq|                          FROM chart| . 
+          qq|                          WHERE accno = ?)| . 
+          qq|         AND startdate <= ?| . 
+          qq|         ORDER BY startdate DESC LIMIT 1))|;

         @values = ($form->{id}, $form->{AP_payables}, $amount,
                    conv_date($form->{"datepaid_$i"}), $project_id,
                    $form->{AP_payables}, $form->{AP_payables}, conv_date($form->{"datepaid_$i"}));
         @values = ($form->{id}, $form->{AP_payables}, $amount,
                    conv_date($form->{"datepaid_$i"}), $project_id,
                    $form->{AP_payables}, $form->{AP_payables}, conv_date($form->{"datepaid_$i"}));


@@ -300,7 +312,13 @@ sub post_transaction {
         qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, project_id, taxkey, tax_id) | .
         qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?, ?, | .
         qq|        (SELECT taxkey_id FROM chart WHERE accno = ?), | . 
         qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, project_id, taxkey, tax_id) | .
         qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?, ?, | .
         qq|        (SELECT taxkey_id FROM chart WHERE accno = ?), | . 

-        qq|        (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id  FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|;
+        qq|        (SELECT tax_id| .
+        qq|         FROM taxkeys| . 
+        qq|         WHERE chart_id= (SELECT id | . 
+        qq|                          FROM chart| . 
+        qq|                          WHERE accno = ?)| . 
+        qq|         AND startdate <= ?| . 
+        qq|         ORDER BY startdate DESC LIMIT 1))|;

       @values = ($form->{id}, $form->{"AP_paid_account_$i"}, $form->{"paid_$i"},
                  conv_date($form->{"datepaid_$i"}), $gldate, $form->{"source_$i"},
                  $form->{"memo_$i"}, $project_id, $form->{"AP_paid_account_$i"},
       @values = ($form->{id}, $form->{"AP_paid_account_$i"}, $form->{"paid_$i"},
                  conv_date($form->{"datepaid_$i"}), $gldate, $form->{"source_$i"},
                  $form->{"memo_$i"}, $project_id, $form->{"AP_paid_account_$i"},


@@ -316,7 +334,13 @@ sub post_transaction {
           qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id, taxkey, tax_id) | .
           qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 't', 'f', ?, | .
           qq|        (SELECT taxkey_id FROM chart WHERE accno = ?), | .
           qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id, taxkey, tax_id) | .
           qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 't', 'f', ?, | .
           qq|        (SELECT taxkey_id FROM chart WHERE accno = ?), | .

-          qq|        (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id  FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|;
+          qq|        (SELECT tax_id| . 
+          qq|         FROM taxkeys| . 
+          qq|         WHERE chart_id= (SELECT id | . 
+          qq|                          FROM chart| . 
+          qq|                          WHERE accno = ?)| . 
+          qq|         AND startdate <= ?| . 
+          qq|         ORDER BY startdate DESC LIMIT 1))|;

         @values = ($form->{id}, $form->{"AP_paid_account_$i"}, $amount,
                    conv_date($form->{"datepaid_$i"}), $project_id,
                    $form->{"AP_paid_account_$i"},
         @values = ($form->{id}, $form->{"AP_paid_account_$i"}, $amount,
                    conv_date($form->{"datepaid_$i"}), $project_id,
                    $form->{"AP_paid_account_$i"},


@@ -335,7 +359,13 @@ sub post_transaction {
           qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id, taxkey, tax_id) | .
           qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 't', 'f', ?, | .
           qq|        (SELECT taxkey_id FROM chart WHERE accno = ?)| .
           qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id, taxkey, tax_id) | .
           qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 't', 'f', ?, | .
           qq|        (SELECT taxkey_id FROM chart WHERE accno = ?)| .

-          qq|        (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id  FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|;
+          qq|        (SELECT tax_id| . 
+          qq|         FROM taxkeys| . 
+          qq|         WHERE chart_id= (SELECT id | . 
+          qq|                          FROM chart| . 
+          qq|                          WHERE accno = ?)| . 
+          qq|         AND startdate <= ?| . 
+          qq|         ORDER BY startdate DESC LIMIT 1))|;

         @values = ($form->{id}, ($amount > 0) ?
                    $form->{fxgain_accno} : $form->{fxloss_accno},
                    $amount, conv_date($form->{"datepaid_$i"}), $project_id,
         @values = ($form->{id}, ($amount > 0) ?
                    $form->{fxgain_accno} : $form->{fxloss_accno},
                    $amount, conv_date($form->{"datepaid_$i"}), $project_id,

diff --git a/SL/AR.pm b/SL/AR.pm
index 735f6b9f4fa7a73be70f9fed85bc32d084fa276b..a953af44b435c2686da916c5244c058e0b539524 100644 (file)
--- a/SL/AR.pm
+++ b/SL/AR.pm
@@ -192,7 +192,13 @@ sub post_transaction {
     # add recievables
     $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id)
                  VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT taxkey_id FROM chart WHERE accno = ?),
     # add recievables
     $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id)
                  VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT taxkey_id FROM chart WHERE accno = ?),

-                 (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id  FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|;
+                 (SELECT tax_id 
+                  FROM taxkeys 
+                  WHERE chart_id= (SELECT id  
+                                   FROM chart 
+                                   WHERE accno = ?) 
+                  AND startdate <= ? 
+                  ORDER BY startdate DESC LIMIT 1))|;

     @values = (conv_i($form->{id}), $form->{AR_amounts}{receivables}, conv_i($form->{receivables}), conv_date($form->{transdate}),
                 $form->{AR_amounts}{receivables}, $form->{AR_amounts}{receivables}, conv_date($form->{transdate}));
     do_query($form, $dbh, $query, @values);
     @values = (conv_i($form->{id}), $form->{AR_amounts}{receivables}, conv_i($form->{receivables}), conv_date($form->{transdate}),
                 $form->{AR_amounts}{receivables}, $form->{AR_amounts}{receivables}, conv_date($form->{transdate}));
     do_query($form, $dbh, $query, @values);


@@ -233,7 +239,13 @@ sub post_transaction {
         # add receivable
         $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, taxkey, tax_id)
                      VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, (SELECT taxkey_id FROM chart WHERE accno = ?),
         # add receivable
         $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, taxkey, tax_id)
                      VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, (SELECT taxkey_id FROM chart WHERE accno = ?),

-                     (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id  FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|;
+                     (SELECT tax_id 
+                      FROM taxkeys 
+                      WHERE chart_id= (SELECT id 
+                                       FROM chart 
+                                       WHERE accno = ?) 
+                      AND startdate <= ? 
+                      ORDER BY startdate DESC LIMIT 1))|;

         @values = (conv_i($form->{id}), $form->{AR}{receivables}, $amount, conv_date($form->{"datepaid_$i"}), $project_id, $form->{AR}{receivables}, $form->{AR}{receivables}, conv_date($form->{"datepaid_$i"}));
         do_query($form, $dbh, $query, @values);
       }
         @values = (conv_i($form->{id}), $form->{AR}{receivables}, $amount, conv_date($form->{"datepaid_$i"}), $project_id, $form->{AR}{receivables}, $form->{AR}{receivables}, conv_date($form->{"datepaid_$i"}));
         do_query($form, $dbh, $query, @values);
       }


@@ -245,7 +257,13 @@ sub post_transaction {
         $amount = $form->{"paid_$i"} * -1;
         $query  = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, project_id, taxkey, tax_id)
                      VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?, ?, (SELECT taxkey_id FROM chart WHERE accno = ?),
         $amount = $form->{"paid_$i"} * -1;
         $query  = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, project_id, taxkey, tax_id)
                      VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?, ?, (SELECT taxkey_id FROM chart WHERE accno = ?),

-                     (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id  FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|;
+                     (SELECT tax_id 
+                      FROM taxkeys 
+                      WHERE chart_id= (SELECT id 
+                                       FROM chart 
+                                       WHERE accno = ?) 
+                      AND startdate <= ? 
+                      ORDER BY startdate DESC LIMIT 1))|;

         @values = (conv_i($form->{id}), $form->{AR}{"paid_$i"}, $amount, conv_date($form->{"datepaid_$i"}), $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $project_id, $form->{AR}{"paid_$i"},
                     $form->{AR}{"paid_$i"}, conv_date($form->{"datepaid_$i"}));
         do_query($form, $dbh, $query, @values);
         @values = (conv_i($form->{id}), $form->{AR}{"paid_$i"}, $amount, conv_date($form->{"datepaid_$i"}), $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $project_id, $form->{AR}{"paid_$i"},
                     $form->{AR}{"paid_$i"}, conv_date($form->{"datepaid_$i"}));
         do_query($form, $dbh, $query, @values);


@@ -256,7 +274,13 @@ sub post_transaction {
         if ($amount != 0) {
           $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id, taxkey, tax_id)
                        VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 't', 'f', ?, (SELECT taxkey_id FROM chart WHERE accno = ?),
         if ($amount != 0) {
           $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id, taxkey, tax_id)
                        VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 't', 'f', ?, (SELECT taxkey_id FROM chart WHERE accno = ?),

-                       (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id  FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|;
+                       (SELECT tax_id 
+                        FROM taxkeys 
+                        WHERE chart_id= (SELECT id  
+                                         FROM chart 
+                                         WHERE accno = ?) 
+                        AND startdate <= ? 
+                        ORDER BY startdate DESC LIMIT 1))|;

           @values = (conv_i($form->{id}), $form->{AR}{"paid_$i"}, $amount, conv_date($form->{"datepaid_$i"}), $project_id, $form->{AR}{"paid_$i"},
                     $form->{AR}{"paid_$i"}, conv_date($form->{"datepaid_$i"}));
           do_query($form, $dbh, $query, @values);
           @values = (conv_i($form->{id}), $form->{AR}{"paid_$i"}, $amount, conv_date($form->{"datepaid_$i"}), $project_id, $form->{AR}{"paid_$i"},
                     $form->{AR}{"paid_$i"}, conv_date($form->{"datepaid_$i"}));
           do_query($form, $dbh, $query, @values);


@@ -269,7 +293,13 @@ sub post_transaction {
           my $accno = ($amount > 0) ? $form->{fxgain_accno} : $form->{fxloss_accno};
           $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id, taxkey, tax_id)
                        VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 't', 'f', ?, (SELECT taxkey_id FROM chart WHERE accno = ?),
           my $accno = ($amount > 0) ? $form->{fxgain_accno} : $form->{fxloss_accno};
           $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id, taxkey, tax_id)
                        VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 't', 'f', ?, (SELECT taxkey_id FROM chart WHERE accno = ?),

-                       (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id  FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|;
+                       (SELECT tax_id 
+                        FROM taxkeys 
+                        WHERE chart_id= (SELECT id  
+                                         FROM chart 
+                                         WHERE accno = ?) 
+                        AND startdate <= ? 
+                        ORDER BY startdate DESC LIMIT 1))|;

           @values = (conv_i($form->{id}), $accno, $amount, conv_date($form->{"datepaid_$i"}), $project_id, $accno, $accno, conv_date($form->{"datepaid_$i"}));
           do_query($form, $dbh, $query, @values);
         }
           @values = (conv_i($form->{id}), $accno, $amount, conv_date($form->{"datepaid_$i"}), $project_id, $accno, $accno, conv_date($form->{"datepaid_$i"}));
           do_query($form, $dbh, $query, @values);
         }

diff --git a/SL/IR.pm b/SL/IR.pm
index b8a5faa309fff6ab0d9edf118ba8c7d1a1c63ac9..e1d1011aa0a3811f2b1e139cf6e36ccd1ddaa389 100644 (file)
--- a/SL/IR.pm
+++ b/SL/IR.pm
@@ -270,13 +270,25 @@ sub post_invoice {
             # allocated >= 0
             # add entry for inventory, this one is for the sold item
             $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id) VALUES (?, ?, ?, ?, (SELECT taxkey_id FROM chart WHERE id = ?),
             # allocated >= 0
             # add entry for inventory, this one is for the sold item
             $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id) VALUES (?, ?, ?, ?, (SELECT taxkey_id FROM chart WHERE id = ?),

-                                (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id  FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|;
+                                (SELECT tax_id 
+                                FROM taxkeys 
+                                WHERE chart_id= (SELECT id  
+                                                 FROM chart 
+                                                 WHERE accno = ?) 
+                                AND startdate <= ?
+                                ORDER BY startdate DESC LIMIT 1))|;

             @values = ($ref->{trans_id},  $ref->{inventory_accno_id}, $linetotal, $ref->{transdate}, $ref->{inventory_accno_id}, $ref->{inventory_accno_id}, $ref->{transdate});
             do_query($form, $dbh, $query, @values);
 
 # add expense
             $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id) VALUES (?, ?, ?, ?, (SELECT taxkey from tax WHERE chart_id = ?),
             @values = ($ref->{trans_id},  $ref->{inventory_accno_id}, $linetotal, $ref->{transdate}, $ref->{inventory_accno_id}, $ref->{inventory_accno_id}, $ref->{transdate});
             do_query($form, $dbh, $query, @values);
 
 # add expense
             $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id) VALUES (?, ?, ?, ?, (SELECT taxkey from tax WHERE chart_id = ?),

-                                (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id  FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|;
+                                (SELECT tax_id 
+                                 FROM taxkeys 
+                                 WHERE chart_id= (SELECT id  
+                                                  FROM chart 
+                                                  WHERE accno = ?) 
+                                 AND startdate <= ? 
+                                 ORDER BY startdate DESC LIMIT 1))|;

             @values = ($ref->{trans_id},  $ref->{expense_accno_id}, ($linetotal * -1), $ref->{transdate}, $ref->{expense_accno_id}, $ref->{expense_accno_id}, $ref->{transdate});
             do_query($form, $dbh, $query, @values);
           }
             @values = ($ref->{trans_id},  $ref->{expense_accno_id}, ($linetotal * -1), $ref->{transdate}, $ref->{expense_accno_id}, $ref->{expense_accno_id}, $ref->{transdate});
             do_query($form, $dbh, $query, @values);
           }


@@ -483,7 +495,13 @@ sub post_invoice {
       $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id, tax_id)
                   VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
                   (SELECT taxkey_id  FROM chart WHERE accno = ?), ?,
       $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id, tax_id)
                   VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
                   (SELECT taxkey_id  FROM chart WHERE accno = ?), ?,

-                  (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id  FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|;
+                  (SELECT tax_id 
+                   FROM taxkeys 
+                   WHERE chart_id= (SELECT id  
+                                    FROM chart 
+                                    WHERE accno = ?) 
+                   AND startdate <= ? 
+                   ORDER BY startdate DESC LIMIT 1))|;

       @values = ($trans_id, $accno, $form->{amount}{$trans_id}{$accno},
                  conv_date($form->{invdate}), $accno, $project_id, $accno, conv_date($form->{invdate}));
       do_query($form, $dbh, $query, @values);
       @values = ($trans_id, $accno, $form->{amount}{$trans_id}{$accno},
                  conv_date($form->{invdate}), $accno, $project_id, $accno, conv_date($form->{invdate}));
       do_query($form, $dbh, $query, @values);


@@ -523,7 +541,13 @@ sub post_invoice {
       $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id, tax_id)
                   VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
                           (SELECT taxkey_id FROM chart WHERE accno = ?), ?,
       $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id, tax_id)
                   VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
                           (SELECT taxkey_id FROM chart WHERE accno = ?), ?,

-                          (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id  FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|;
+                          (SELECT tax_id 
+                           FROM taxkeys 
+                           WHERE chart_id= (SELECT id  
+                                            FROM chart 
+                                            WHERE accno = ?) 
+                           AND startdate <= ? 
+                           ORDER BY startdate DESC LIMIT 1))|;

       @values = (conv_i($form->{id}), $form->{AP}, $amount,
                  $form->{"datepaid_$i"}, $form->{AP}, $project_id, $form->{AP}, conv_date($form->{"datepaid_$i"}));
       do_query($form, $dbh, $query, @values);
       @values = (conv_i($form->{id}), $form->{AP}, $amount,
                  $form->{"datepaid_$i"}, $form->{AP}, $project_id, $form->{AP}, conv_date($form->{"datepaid_$i"}));
       do_query($form, $dbh, $query, @values);


@@ -536,7 +560,12 @@ sub post_invoice {
       qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, taxkey, project_id, tax_id)
                 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
                 (SELECT taxkey_id FROM chart WHERE accno = ?), ?,
       qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, taxkey, project_id, tax_id)
                 VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
                 (SELECT taxkey_id FROM chart WHERE accno = ?), ?,

-                (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id  FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|;
+                (SELECT tax_id 
+                 FROM taxkeys 
+                 WHERE chart_id= (SELECT id  
+                                  FROM chart WHERE accno = ?) 
+                 AND startdate <= ? 
+                 ORDER BY startdate DESC LIMIT 1))|;

     @values = (conv_i($form->{id}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
                $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id, $accno, conv_date($form->{"datepaid_$i"}));
     do_query($form, $dbh, $query, @values);
     @values = (conv_i($form->{id}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
                $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id, $accno, conv_date($form->{"datepaid_$i"}));
     do_query($form, $dbh, $query, @values);


@@ -578,7 +607,13 @@ sub post_invoice {
 
       $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id, tax_id)
                   VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1', 0, ?,
 
       $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id, tax_id)
                   VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1', 0, ?,

-                  (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id  FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|;
+                  (SELECT tax_id 
+                   FROM taxkeys 
+                   WHERE chart_id= (SELECT id  
+                                   FROM chart 
+                                   WHERE accno = ?) 
+                   AND startdate <= ? 
+                   ORDER BY startdate DESC LIMIT 1))|;

       @values = (conv_i($form->{id}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $project_id, $accno, $form->{fx}{$accno}{$transdate});
       do_query($form, $dbh, $query, @values);
     }
       @values = (conv_i($form->{id}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $project_id, $accno, $form->{fx}{$accno}{$transdate});
       do_query($form, $dbh, $query, @values);
     }

diff --git a/SL/IS.pm b/SL/IS.pm
index 287a2613bab51a7e220a10b90b4817486af42906..196d6dff2d7ad2b9e162195c0d9806fc4e9656af 100644 (file)
--- a/SL/IS.pm
+++ b/SL/IS.pm
@@ -863,7 +863,13 @@ sub post_invoice {
         $query =
           qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id)
              VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
         $query =
           qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id)
              VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,

-                     (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id  FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1),
+                     (SELECT tax_id 
+                      FROM taxkeys 
+                      WHERE chart_id= (SELECT id  
+                                       FROM chart 
+                                       WHERE accno = ?) 
+                      AND startdate <= ? 
+                      ORDER BY startdate DESC LIMIT 1),

                      (SELECT taxkey_id  FROM chart WHERE accno = ?), ?)|;
         @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_date($taxdate), $accno, conv_i($project_id));
         do_query($form, $dbh, $query, @values);
                      (SELECT taxkey_id  FROM chart WHERE accno = ?), ?)|;
         @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_date($taxdate), $accno, conv_i($project_id));
         do_query($form, $dbh, $query, @values);


@@ -878,7 +884,13 @@ sub post_invoice {
         $query =
           qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id)
              VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
         $query =
           qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id)
              VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,

-                     (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id  FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1),
+                     (SELECT tax_id 
+                      FROM taxkeys 
+                      WHERE chart_id= (SELECT id 
+                                       FROM chart 
+                                       WHERE accno = ?) 
+                      AND startdate <= ? 
+                      ORDER BY startdate DESC LIMIT 1),

                      (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
         @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_date($taxdate), $accno, conv_i($project_id));
         do_query($form, $dbh, $query, @values);
                      (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
         @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_date($taxdate), $accno, conv_i($project_id));
         do_query($form, $dbh, $query, @values);


@@ -928,7 +940,13 @@ sub post_invoice {
         $query =
         qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id)
            VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
         $query =
         qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id)
            VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,

-                   (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id  FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1),
+                   (SELECT tax_id 
+                    FROM taxkeys 
+                    WHERE chart_id= (SELECT id  
+                                     FROM chart 
+                                     WHERE accno = ?) 
+                    AND startdate <= ? 
+                    ORDER BY startdate DESC LIMIT 1),

                    (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
         @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, conv_date($taxdate), $form->{AR}, $project_id);
         do_query($form, $dbh, $query, @values);
                    (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
         @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, conv_date($taxdate), $form->{AR}, $project_id);
         do_query($form, $dbh, $query, @values);


@@ -941,7 +959,13 @@ sub post_invoice {
       $query =
       qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, tax_id, taxkey, project_id)
          VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
       $query =
       qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, tax_id, taxkey, project_id)
          VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,

-                 (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id  FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1),
+                 (SELECT tax_id 
+                  FROM taxkeys 
+                  WHERE chart_id= (SELECT id  
+                                   FROM chart 
+                                   WHERE accno = ?) 
+                  AND startdate <= ? 
+                  ORDER BY startdate DESC LIMIT 1),

                  (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
       @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
                  $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, conv_date($taxdate), $accno, $project_id);
                  (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
       @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
                  $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, conv_date($taxdate), $accno, $project_id);


@@ -986,7 +1010,13 @@ sub post_invoice {
         $query =
           qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, tax_id, taxkey, project_id)
              VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
         $query =
           qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, tax_id, taxkey, project_id)
              VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',

-                 (SELECT tax_id FROM taxkeys WHERE chart_id= (SELECT id  FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1),
+                 (SELECT tax_id 
+                  FROM taxkeys 
+                  WHERE chart_id= (SELECT id  
+                                   FROM chart 
+                                   WHERE accno = ?) 
+                  AND startdate <= ? 
+                  ORDER BY startdate DESC LIMIT 1),

                  (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
         @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, conv_date($taxdate), $accno, conv_i($project_id));
         do_query($form, $dbh, $query, @values);
                  (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
         @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, conv_date($taxdate), $accno, conv_i($project_id));
         do_query($form, $dbh, $query, @values);

diff --git a/sql/Pg-upgrade2/add_tax_id_to_acc_trans.sql b/sql/Pg-upgrade2/add_tax_id_to_acc_trans.sql
index a071442bae0118ddcad6c746e72df7f7d1dcd377..a728d0653b0cd13ed7bae5a24ba0652ac9603d10 100644 (file)
--- a/sql/Pg-upgrade2/add_tax_id_to_acc_trans.sql
+++ b/sql/Pg-upgrade2/add_tax_id_to_acc_trans.sql
@@ -7,18 +7,18 @@
 
   --Spalte mit Werten füllen:
   UPDATE acc_trans ac SET tax_id=
 
   --Spalte mit Werten füllen:
   UPDATE acc_trans ac SET tax_id=

-                (select tk.tax_id from taxkeys tk 
-                      where tk.taxkey_id=ac.taxkey 
+                (SELECT tk.tax_id FROM taxkeys tk 
+                      WHERE tk.taxkey_id=ac.taxkey 

                       AND tk.startdate <= COALESCE(
                       AND tk.startdate <= COALESCE(

-                            (select ar.deliverydate from ar where ar.id=ac.trans_id), 
-                            (select ar.transdate from ar where ar.id=ac.trans_id), 
-                            (select ap.transdate from ap where ap.id=ac.trans_id), 
-                            (select gl.transdate from gl where gl.id=ac.trans_id), 
+                            (SELECT ar.deliverydate FROM ar WHERE ar.id=ac.trans_id), 
+                            (SELECT ar.transdate FROM ar WHERE ar.id=ac.trans_id), 
+                            (SELECT ap.transdate FROM ap WHERE ap.id=ac.trans_id), 
+                            (SELECT gl.transdate FROM gl WHERE gl.id=ac.trans_id), 

                             ac.transdate )
                             ac.transdate )

-                      order by startdate desc limit 1);
+                      ORDER BY startdate DESC LIMIT 1);

 
   --Spalten, die noch null sind (nur bei Einträgen möglich, wo auch taxkey null ist)
 
   --Spalten, die noch null sind (nur bei Einträgen möglich, wo auch taxkey null ist)

-  UPDATE acc_trans SET tax_id= (SELECT id FROM tax WHERE taxkey=0 LIMIT 1) where tax_id is null;
+  UPDATE acc_trans SET tax_id= (SELECT id FROM tax WHERE taxkey=0 LIMIT 1) WHERE tax_id IS NULL;

  
   --tax_id als Pflichtfeld definieren:
   ALTER TABLE acc_trans ALTER tax_id SET NOT NULL;
  
   --tax_id als Pflichtfeld definieren:
   ALTER TABLE acc_trans ALTER tax_id SET NOT NULL;