Lieferbedingungen analog zu Zahlungsbedingungen eingeführt.

[mfinanz.git] / SL / IR.pm

diff --git a/SL/IR.pm b/SL/IR.pm
index 1f0ae58f7c647f85dac3df24d390b103237d4cc0..84ce10a17cad00641faf270583387f9670b1f15d 100644 (file)
--- a/SL/IR.pm
+++ b/SL/IR.pm
@@ -57,6 +57,7 @@ sub post_invoice {
   # connect to database, turn off autocommit
   my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig);
   $form->{defaultcurrency} = $form->get_default_currency($myconfig);
   # connect to database, turn off autocommit
   my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig);
   $form->{defaultcurrency} = $form->get_default_currency($myconfig);

+  my $defaultcurrency = $form->{defaultcurrency};

 
   my $ic_cvar_configs = CVar->get_configs(module => 'IC',
                                           dbh    => $dbh);
 
   my $ic_cvar_configs = CVar->get_configs(module => 'IC',
                                           dbh    => $dbh);


@@ -70,18 +71,16 @@ sub post_invoice {
 
   my $all_units = AM->retrieve_units($myconfig, $form);
 
 
   my $all_units = AM->retrieve_units($myconfig, $form);
 

+#markierung

   if (!$payments_only) {
     if ($form->{id}) {
       &reverse_invoice($dbh, $form);
     } else {
       ($form->{id}) = selectrow_query($form, $dbh, qq|SELECT nextval('glid')|);
   if (!$payments_only) {
     if ($form->{id}) {
       &reverse_invoice($dbh, $form);
     } else {
       ($form->{id}) = selectrow_query($form, $dbh, qq|SELECT nextval('glid')|);

-      do_query($form, $dbh, qq|INSERT INTO ap (id, invnumber) VALUES (?, '')|, $form->{id});
+      do_query($form, $dbh, qq|INSERT INTO ap (id, invnumber, currency_id) VALUES (?, '', (SELECT id FROM currencies WHERE name=?))|, $form->{id}, $form->{currency});

     }
   }
 
     }
   }
 

-  my ($currencies)    = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
-  my $defaultcurrency = (split m/:/, $currencies)[0];
-

   if ($form->{currency} eq $defaultcurrency) {
     $form->{exchangerate} = 1;
   } else {
   if ($form->{currency} eq $defaultcurrency) {
     $form->{exchangerate} = 1;
   } else {


@@ -216,14 +215,16 @@ sub post_invoice {
 
       # check if we sold the item already and
       # make an entry for the expense and inventory
 
       # check if we sold the item already and
       # make an entry for the expense and inventory

+      my $taxzone = $form->{taxzone_id} * 1;

       $query =
         qq|SELECT i.id, i.qty, i.allocated, i.trans_id, i.base_qty,
       $query =
         qq|SELECT i.id, i.qty, i.allocated, i.trans_id, i.base_qty,

-             p.inventory_accno_id, p.expense_accno_id, a.transdate
-           FROM invoice i, ar a, parts p
+             bg.inventory_accno_id, bg.expense_accno_id_${taxzone} AS expense_accno_id, a.transdate
+           FROM invoice i, ar a, parts p, buchungsgruppen bg

            WHERE (i.parts_id = p.id)
              AND (i.parts_id = ?)
              AND ((i.base_qty + i.allocated) > 0)
              AND (i.trans_id = a.id)
            WHERE (i.parts_id = p.id)
              AND (i.parts_id = ?)
              AND ((i.base_qty + i.allocated) > 0)
              AND (i.trans_id = a.id)

+             AND (p.buchungsgruppen_id = bg.id)

            ORDER BY transdate|;
            # ORDER BY transdate guarantees FIFO
 
            ORDER BY transdate|;
            # ORDER BY transdate guarantees FIFO
 


@@ -269,7 +270,7 @@ sub post_invoice {
 
             # allocated >= 0
             # add entry for inventory, this one is for the sold item
 
             # 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 (?, ?, ?, ?,
+            $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id, chart_link) VALUES (?, ?, ?, ?,

                                (SELECT taxkey_id
                                 FROM taxkeys
                                 WHERE chart_id= ?
                                (SELECT taxkey_id
                                 FROM taxkeys
                                 WHERE chart_id= ?


@@ -280,13 +281,13 @@ sub post_invoice {
                                 WHERE chart_id= ?
                                 AND startdate <= ?
                                 ORDER BY startdate DESC LIMIT 1),
                                 WHERE chart_id= ?
                                 AND startdate <= ?
                                 ORDER BY startdate DESC LIMIT 1),

-                               (SELECT chart_link FROM chart WHERE id = ?))|;
+                               (SELECT link FROM chart WHERE id = ?))|;

             @values = ($ref->{trans_id},  $ref->{inventory_accno_id}, $linetotal, $ref->{transdate}, $ref->{inventory_accno_id}, $ref->{transdate}, $ref->{inventory_accno_id}, $ref->{transdate},
                        $ref->{inventory_accno_id});
             do_query($form, $dbh, $query, @values);
 
 # add expense
             @values = ($ref->{trans_id},  $ref->{inventory_accno_id}, $linetotal, $ref->{transdate}, $ref->{inventory_accno_id}, $ref->{transdate}, $ref->{inventory_accno_id}, $ref->{transdate},
                        $ref->{inventory_accno_id});
             do_query($form, $dbh, $query, @values);
 
 # add expense

-            $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id) VALUES (?, ?, ?, ?,
+            $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id, chart_link) VALUES (?, ?, ?, ?,

                                 (SELECT taxkey_id
                                  FROM taxkeys
                                  WHERE chart_id= ?
                                 (SELECT taxkey_id
                                  FROM taxkeys
                                  WHERE chart_id= ?


@@ -297,7 +298,7 @@ sub post_invoice {
                                  WHERE chart_id= ?
                                  AND startdate <= ?
                                  ORDER BY startdate DESC LIMIT 1),
                                  WHERE chart_id= ?
                                  AND startdate <= ?
                                  ORDER BY startdate DESC LIMIT 1),

-                                (SELECT chart_link FROM chart WHERE id = ?))|;
+                                (SELECT link FROM chart WHERE id = ?))|;

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


@@ -684,8 +685,8 @@ sub post_invoice {
                 orddate      = ?, quodate     = ?, vendor_id     = ?, amount      = ?,
                 netamount    = ?, paid        = ?, duedate       = ?,
                 invoice      = ?, taxzone_id  = ?, notes         = ?, taxincluded = ?,
                 orddate      = ?, quodate     = ?, vendor_id     = ?, amount      = ?,
                 netamount    = ?, paid        = ?, duedate       = ?,
                 invoice      = ?, taxzone_id  = ?, notes         = ?, taxincluded = ?,

-                intnotes     = ?, curr        = ?, storno_id     = ?, storno      = ?,
-                cp_id        = ?, employee_id = ?, department_id = ?,
+                intnotes     = ?, storno_id   = ?, storno        = ?,
+                cp_id        = ?, employee_id = ?, department_id = ?, delivery_term_id = ?,

                 globalproject_id = ?, direct_debit = ?
               WHERE id = ?|;
   @values = (
                 globalproject_id = ?, direct_debit = ?
               WHERE id = ?|;
   @values = (


@@ -693,8 +694,8 @@ sub post_invoice {
       conv_date($form->{orddate}), conv_date($form->{quodate}),     conv_i($form->{vendor_id}),               $amount,
                 $netamount,                  $form->{paid},      conv_date($form->{duedate}),
             '1',                             $taxzone_id,                  $form->{notes},          $form->{taxincluded} ? 't' : 'f',
       conv_date($form->{orddate}), conv_date($form->{quodate}),     conv_i($form->{vendor_id}),               $amount,
                 $netamount,                  $form->{paid},      conv_date($form->{duedate}),
             '1',                             $taxzone_id,                  $form->{notes},          $form->{taxincluded} ? 't' : 'f',

-                $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}),
+                $form->{intnotes},           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->{delivery_term_id}),

          conv_i($form->{globalproject_id}),
                 $form->{direct_debit} ? 't' : 'f',
          conv_i($form->{id})
          conv_i($form->{globalproject_id}),
                 $form->{direct_debit} ? 't' : 'f',
          conv_i($form->{id})


@@ -766,6 +767,7 @@ sub post_invoice {
       dbh        => $dbh,
       from       => $transdate,
       to         => $transdate,
       dbh        => $dbh,
       from       => $transdate,
       to         => $transdate,

+      trans_id   => $form->{id},

     );
 
     $datev->export;
     );
 
     $datev->export;


@@ -922,8 +924,7 @@ sub retrieve_invoice {
                (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id)    AS income_accno,
                (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id)   AS expense_accno,
                (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id)    AS fxgain_accno,
                (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id)    AS income_accno,
                (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id)   AS expense_accno,
                (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id)    AS fxgain_accno,

-               (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id)    AS fxloss_accno,
-               d.curr AS currencies
+               (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id)    AS fxloss_accno

                $q_invdate
                FROM defaults d|;
   $ref = selectfirst_hashref_query($form, $dbh, $query);
                $q_invdate
                FROM defaults d|;
   $ref = selectfirst_hashref_query($form, $dbh, $query);


@@ -940,15 +941,13 @@ 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,
   $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, direct_debit
+                intnotes, (SELECT cu.name FROM currencies cu WHERE cu.id=ap.currency_id) AS currency, direct_debit,
+                delivery_term_id

               FROM ap
               WHERE id = ?|;
   $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{id}));
   map { $form->{$_} = $ref->{$_} } keys %$ref;
 
               FROM ap
               WHERE id = ?|;
   $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{id}));
   map { $form->{$_} = $ref->{$_} } keys %$ref;
 

-  # remove any trailing whitespace
-  $form->{currency} =~ s/\s*$//;
-

   $form->{exchangerate}  = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "sell");
 
   # get shipto
   $form->{exchangerate}  = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "sell");
 
   # get shipto


@@ -972,7 +971,7 @@ sub retrieve_invoice {
         i.id AS invoice_id,
         i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.parts_id AS id, i.unit, i.deliverydate, i.project_id, i.serialnumber,
         i.price_factor_id, i.price_factor, i.marge_price_factor, i.discount,
         i.id AS invoice_id,
         i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.parts_id AS id, i.unit, i.deliverydate, i.project_id, i.serialnumber,
         i.price_factor_id, i.price_factor, i.marge_price_factor, i.discount,

-        p.partnumber, p.inventory_accno_id AS part_inventory_accno_id, p.bin, pr.projectnumber, pg.partsgroup
+        p.partnumber, p.inventory_accno_id AS part_inventory_accno_id,  pr.projectnumber, pg.partsgroup

 
         FROM invoice i
         JOIN parts p ON (i.parts_id = p.id)
 
         FROM invoice i
         JOIN parts p ON (i.parts_id = p.id)


@@ -1089,22 +1088,20 @@ sub get_vendor {
     qq|SELECT
          v.id AS vendor_id, v.name AS vendor, v.discount as vendor_discount,
          v.creditlimit, v.terms, v.notes AS intnotes,
     qq|SELECT
          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.direct_debit,
+         v.email, v.cc, v.bcc, v.language_id, v.payment_id, v.delivery_term_id,
+         v.street, v.zipcode, v.city, v.country, v.taxzone_id, cu.name AS curr, v.direct_debit,

          $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
          b.description AS business
        FROM vendor v
        LEFT JOIN business b       ON (b.id = v.business_id)
        LEFT JOIN payment_terms pt ON (v.payment_id = pt.id)
          $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
          b.description AS business
        FROM vendor v
        LEFT JOIN business b       ON (b.id = v.business_id)
        LEFT JOIN payment_terms pt ON (v.payment_id = pt.id)

+       LEFT JOIN currencies cu    ON (v.currency_id = cu.id)

        WHERE 1=1 $where|;
   my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
   map { $params->{$_} = $ref->{$_} } keys %$ref;
 
        WHERE 1=1 $where|;
   my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
   map { $params->{$_} = $ref->{$_} } keys %$ref;
 

-  # remove any trailing whitespace
-  $form->{curr} =~ s/\s*$//;
-
-  # use vendor currency if not empty
-  $form->{currency} = $form->{curr} if $form->{curr};
+  # use vendor currency
+  $form->{currency} = $form->{curr};

 
   $params->{creditremaining} = $params->{creditlimit};
 
 
   $params->{creditremaining} = $params->{creditlimit};
 


@@ -1115,7 +1112,7 @@ sub get_vendor {
   $query = qq|SELECT o.amount,
                 (SELECT e.sell
                  FROM exchangerate e
   $query = qq|SELECT o.amount,
                 (SELECT e.sell
                  FROM exchangerate e

-                 WHERE (e.curr = o.curr)
+                 WHERE (e.currency_id = o.currency_id)

                    AND (e.transdate = o.transdate)) AS exch
               FROM oe o
               WHERE (o.vendor_id = ?) AND (o.quotation = '0') AND (o.closed = '0')|;
                    AND (e.transdate = o.transdate)) AS exch
               FROM oe o
               WHERE (o.vendor_id = ?) AND (o.quotation = '0') AND (o.closed = '0')|;


@@ -1218,7 +1215,9 @@ sub retrieve_item {
 
   my $transdate = "";
   if ($form->{type} eq "invoice") {
 
   my $transdate = "";
   if ($form->{type} eq "invoice") {

-    $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
+    $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
+               : $form->{invdate} ? $dbh->quote($form->{invdate})
+               : "current_date";

   } else {
     $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
   }
   } else {
     $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
   }


@@ -1229,7 +1228,7 @@ sub retrieve_item {
   my $query =
     qq|SELECT
          p.id, p.partnumber, p.description, p.lastcost AS sellprice, p.listprice,
   my $query =
     qq|SELECT
          p.id, p.partnumber, p.description, p.lastcost AS sellprice, p.listprice,

-         p.unit, p.assembly, p.bin, p.onhand, p.formel,
+         p.unit, p.assembly, p.onhand, p.formel,

          p.notes AS partnotes, p.notes AS longdescription, p.not_discountable,
          p.inventory_accno_id, p.price_factor_id,
 
          p.notes AS partnotes, p.notes AS longdescription, p.not_discountable,
          p.inventory_accno_id, p.price_factor_id,
 


@@ -1388,9 +1387,10 @@ sub vendor_details {
   # fax and phone and email as vendor*
   my $query =
     qq|SELECT ct.*, cp.*, ct.notes as vendornotes, phone as vendorphone, fax as vendorfax, email as vendoremail,
   # fax and phone and email as vendor*
   my $query =
     qq|SELECT ct.*, cp.*, ct.notes as vendornotes, phone as vendorphone, fax as vendorfax, email as vendoremail,

-         ct.curr AS currency
+         cu.name AS currency

        FROM vendor ct
        LEFT JOIN contacts cp ON (ct.id = cp.cp_cv_id)
        FROM vendor ct
        LEFT JOIN contacts cp ON (ct.id = cp.cp_cv_id)

+       LEFT JOIN currencies cu ON (ct.currency_id = cu.id)

        WHERE (ct.id = ?) $contact
        ORDER BY cp.cp_id
        LIMIT 1|;
        WHERE (ct.id = ?) $contact
        ORDER BY cp.cp_id
        LIMIT 1|;


@@ -1407,8 +1407,6 @@ sub vendor_details {
   }
 
   map { $form->{$_} = $ref->{$_} } keys %$ref;
   }
 
   map { $form->{$_} = $ref->{$_} } keys %$ref;

-  # remove any trailing whitespace
-  $form->{currency} =~ s/\s*$// if ($form->{currency});

 
   my $custom_variables = CVar->get_custom_variables('dbh'      => $dbh,
                                                     'module'   => 'CT',
 
   my $custom_variables = CVar->get_custom_variables('dbh'      => $dbh,
                                                     'module'   => 'CT',