Console: Hilfsfunktion sql()

[mfinanz.git] / SL / IR.pm

diff --git a/SL/IR.pm b/SL/IR.pm
index 5ff85e5730f0171a5b6dce4f9d3c49d2ca442364..bd8bd591d2f44602c6448a1ce6dbb6ea87901d1f 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 = ?),
-                                (SELECT tax_id FROM taxkeys WHERE taxkey_id= (SELECT taxkey_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 = ?),
-                                (SELECT tax_id FROM taxkeys WHERE taxkey_id= (SELECT taxkey_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);
           }
@@ -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 = ?), ?,
-                  (SELECT tax_id FROM taxkeys WHERE taxkey_id= (SELECT taxkey_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);
@@ -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 = ?), ?,
-                          (SELECT tax_id FROM taxkeys WHERE taxkey_id= (SELECT taxkey_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);
@@ -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 = ?), ?,
-                (SELECT tax_id FROM taxkeys WHERE taxkey_id= (SELECT taxkey_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);
@@ -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, ?,
-                  (SELECT tax_id FROM taxkeys WHERE taxkey_id= (SELECT taxkey_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);
     }
@@ -623,7 +658,7 @@ sub post_invoice {
                 invoice      = ?, taxzone_id  = ?, notes         = ?, taxincluded = ?,
                 intnotes     = ?, curr        = ?, storno_id     = ?, storno      = ?,
                 cp_id        = ?, employee_id = ?, department_id = ?,
-                globalproject_id = ?
+                globalproject_id = ?, direct_debit = ?
               WHERE id = ?|;
   @values = (
                 $form->{invnumber},          $form->{ordnumber},           $form->{quonumber},      conv_date($form->{invdate}),
@@ -633,6 +668,7 @@ sub post_invoice {
                 $form->{intnotes},           $form->{currency},     conv_i($form->{storno_id}),     $form->{storno}      ? 't' : 'f',
          conv_i($form->{cp_id}),      conv_i($form->{employee_id}), conv_i($form->{department_id}),
          conv_i($form->{globalproject_id}),
+                $form->{direct_debit} ? 't' : 'f',
          conv_i($form->{id})
   );
   do_query($form, $dbh, $query, @values);
@@ -876,7 +912,7 @@ sub retrieve_invoice {
   $query = qq|SELECT cp_id, invnumber, transdate AS invdate, duedate,
                 orddate, quodate, globalproject_id,
                 ordnumber, quonumber, paid, taxincluded, notes, taxzone_id, storno, gldate,
-                intnotes, curr AS currency
+                intnotes, curr AS currency, direct_debit
               FROM ap
               WHERE id = ?|;
   $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{id}));
@@ -1026,7 +1062,7 @@ sub get_vendor {
          v.id AS vendor_id, v.name AS vendor, v.discount as vendor_discount,
          v.creditlimit, v.terms, v.notes AS intnotes,
          v.email, v.cc, v.bcc, v.language_id, v.payment_id,
-         v.street, v.zipcode, v.city, v.country, v.taxzone_id, v.curr,
+         v.street, v.zipcode, v.city, v.country, v.taxzone_id, v.curr, v.direct_debit,
          $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
          b.description AS business
        FROM vendor v