projekt kivitendo

  for my $i (1..$form->{rowcount}) {

    if ($form->{"curr_$i"} ne $form->{"old_curr_$i"}) {

      do_query($form, $dbh, $query);

    }

  }

  if (length($form->{new_curr}) > 0) {

    do_query($form, $dbh, $query);

  }

  $sth->finish;

  #Get currencies:

  $form->{CURRENCIES} = [];

  $sth->finish;

  #Which of them is the default currency?

  $sth   = $dbh->prepare($query);

  $sth->execute || $form->dberror($query);

    $query = qq|UPDATE ap SET

                invnumber = ?, transdate = ?, ordnumber = ?, vendor_id = ?, taxincluded = ?,

                amount = ?, duedate = ?, paid = ?, netamount = ?,

                globalproject_id = ?, direct_debit = ?

               WHERE id = ?|;

    @values = ($form->{invnumber}, conv_date($form->{transdate}),

    } else {

      $query = qq|SELECT nextval('glid')|;

      ($form->{id}) = selectrow_query($form, $dbh, $query);

      do_query($form, $dbh, $query, $form->{id}, $form->{employee_id}, $form->{currency});

      $form->{invnumber} = $form->update_defaults($myconfig, "invnumber", $dbh) unless $form->{invnumber};

    }

  my $arap = $form->{arap} eq "ar" ? "ar" : "ap";

  my $query =

     qq|FROM $arap a | .

     qq|ORDER BY a.id|;

  my $sth = prepare_execute_query($form, $dbh, $query,

                                  conv_i($form->{"${vc}_id"}),

    SELECT COUNT(*)

    FROM $arap

    WHERE (${vc}_id = ?)

      AND (amount <> paid)

SQL

  ($form->{openinvoices_other_currencies}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{"${vc}_id"}), "$form->{currency}");

        qq|SELECT $buysell | .

        qq|FROM exchangerate e | .

        qq|JOIN ${arap} a ON (a.transdate = e.transdate) | .

      my ($exchangerate) =

        selectrow_query($form, $dbh, $query,

                        $form->{currency}, $form->{"id_$i"});

  $sth->finish;

  #get name of currency instead of id:

  ($form->{curr}) = selectrow_query($form, $dbh, $query, conv_i($form->{curr}));

  if ( $form->{salesman_id} ) {

    qq|user_password = ?, | .

    qq|c_vendor_id = ?, | .

    qq|klass = ?, | .

    qq|taxincluded_checked = ? | .

    qq|WHERE id = ?|;

  my @values = (

    qq|  username = ?, | .

    qq|  user_password = ?, | .

    qq|  v_customer_id = ?, | .

    qq|WHERE id = ?|;

  my @values = (

    $form->{vendornumber},

  $query =

    qq|INSERT INTO ar (id,          invnumber, transdate, gldate, customer_id,

                       taxincluded, amount,    netamount, paid,   duedate,

                       employee_id)

       VALUES (

         ?,                     -- id

         -- duedate:

         (SELECT duedate FROM dunning WHERE dunning_id = ? LIMIT 1),

         'f',                   -- invoice

         ?,                     -- notes

         -- employee_id:

         (SELECT id FROM employee WHERE login = ?)

         ar.transdate,       ar.duedate,      ar.customer_id,

         ar.invnumber,       ar.ordnumber,    ar.cp_id,

         ar.amount,          ar.netamount,    ar.paid,

         ar.amount - ar.paid AS open_amount,

         ar.amount - ar.paid + da.fee + da.interest AS linetotal

         shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, closed = ?,

         delivered = ?, department_id = ?, language_id = ?, shipto_id = ?,

         globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?,

       WHERE id = ?|;

  @values = ($form->{donumber}, $form->{ordnumber},

         d.description AS department, dord.language_id,

         dord.shipto_id,

         dord.globalproject_id, dord.delivered, dord.transaction_description,

       FROM delivery_orders dord

       JOIN ${vc} cv ON (dord.${vc}_id = cv.id)

       LEFT JOIN employee e ON (dord.employee_id = e.id)

    $main::lxdebug->leave_sub();

    return;

  }

  my ($defaultcurrency) = selectrow_query($self, $dbh, $query);

    return;

  }

                 FOR UPDATE|;

  my $sth = prepare_execute_query($self, $dbh, $query, $curr, $transdate);

  if ($sth->fetchrow_array) {

    $query = qq|UPDATE exchangerate

                SET $set

                AND transdate = ?|;

  } else {

  }

  $sth->finish;

  do_query($self, $dbh, $query, $curr, $transdate);

    return 1;

  }

  my ($defaultcurrency) = selectrow_query($self, $dbh, $query);

  }

  $query = qq|SELECT e.$fld FROM exchangerate e

  my ($exchangerate) = selectrow_query($self, $dbh, $query, $curr, $transdate);

  my $dbh   = $self->get_standard_dbh($myconfig);

  my $query = qq|SELECT e.$fld FROM exchangerate e

  my ($exchangerate) = selectrow_query($self, $dbh, $query, $currency, $transdate);

  my $dbh      = $self->get_standard_dbh($myconfig);

  my @currencies =();

  my $sth = prepare_execute_query($self, $dbh, $query);

  my ($self, $myconfig) = @_;

  my $dbh      = $self->get_standard_dbh($myconfig);

  my ($defaultcurrency) = selectrow_query($self, $dbh, $query);

    $query =

      qq|SELECT

           a.cp_id, a.invnumber, a.transdate, a.${table}_id, a.datepaid,

           a.intnotes, a.department_id, a.amount AS oldinvtotal,

           a.paid AS oldtotalpaid, a.employee_id, a.gldate, a.type,

           a.globalproject_id, ${extra_columns}

    $query =

      qq|SELECT

           d.closedto, d.revtrans,

           (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

         FROM defaults d|;

    $query =

       qq|SELECT

            current_date AS transdate, d.closedto, d.revtrans,

            (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

          FROM defaults d|;

                    "a.department_id"         => "department_id",

                    "d.description"           => "department",

                    "ct.name"                 => $table,

                    "current_date + ct.terms" => "duedate",

    );

                        FROM $arap a

                        LEFT JOIN $table     ct ON (a.${table}_id = ct.id)

                        LEFT JOIN department d  ON (a.department_id = d.id)

                        WHERE a.id = ?|;

  my $ref          = selectfirst_hashref_query($self, $dbh, $query, $trans_id);

      &reverse_invoice($dbh, $form);

    } else {

      ($form->{id}) = selectrow_query($form, $dbh, qq|SELECT nextval('glid')|);

    }

  }

  $query = qq|SELECT cp_id, invnumber, transdate AS invdate, duedate,

                orddate, quodate, globalproject_id,

                ordnumber, quonumber, paid, taxincluded, notes, taxzone_id, storno, gldate,

              FROM ap

              WHERE id = ?|;

  $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{id}));

         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,

         $duedate + COALESCE(pt.terms_netto, 0) AS duedate,

         b.description AS business

       FROM vendor v

  $query = qq|SELECT o.amount,

                (SELECT e.sell

                 FROM exchangerate e

                   AND (e.transdate = o.transdate)) AS exch

              FROM oe o

              WHERE (o.vendor_id = ?) AND (o.quotation = '0') AND (o.closed = '0')|;

  # 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,

       FROM vendor ct

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

       WHERE (ct.id = ?) $contact

  my $query =

    qq|SELECT ct.*, cp.*, ct.notes as customernotes,

         ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail,

       FROM customer ct

       LEFT JOIN contacts cp on ct.id = cp.cp_cv_id

       WHERE (ct.id = ?) $where

      $query = qq|SELECT nextval('glid')|;

      ($form->{"id"}) = selectrow_query($form, $dbh, $query);

      do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"}, $form->{currency});

      if (!$form->{invnumber}) {

                amount      = ?, netamount     = ?, paid          = ?,

                duedate     = ?, deliverydate  = ?, invoice       = ?, shippingpoint = ?,

                shipvia     = ?, terms         = ?, notes         = ?, intnotes      = ?,

                department_id = ?, payment_id    = ?, taxincluded   = ?,

                type        = ?, language_id   = ?, taxzone_id    = ?, shipto_id     = ?,

                employee_id = ?, salesman_id   = ?, storno_id     = ?, storno        = ?,

           a.orddate, a.quodate, a.globalproject_id,

           a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,

           a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,

           a.employee_id, a.salesman_id, a.payment_id,

           a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,

           a.transaction_description, a.donumber, a.invnumber_for_credit_note,

         c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,

         c.email, c.cc, c.bcc, c.language_id, c.payment_id,

         c.street, c.zipcode, c.city, c.country,

         c.taxincluded_checked, c.direct_debit,

         $duedate + COALESCE(pt.terms_netto, 0) AS duedate,

         b.discount AS tradediscount, b.description AS business

  $query =

    qq|SELECT o.amount,

         (SELECT e.buy FROM exchangerate e

            AND e.transdate = o.transdate)

       FROM oe o

       WHERE o.customer_id = ?

    qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .

    qq|LEFT JOIN employee e ON (o.employee_id = e.id) | .

    qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | .

    qq|  AND ex.transdate = o.transdate) | .

    qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .

    qq|$periodic_invoices_joins | .

    $query = qq|SELECT nextval('id')|;

    ($form->{id}) = selectrow_query($form, $dbh, $query);

    do_query($form, $dbh, $query, $form->{id}, $form->{employee_id});

  }

    qq|UPDATE oe SET

         ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,

         customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,

         delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,

         taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,

         globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?

    $query =

      qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,

           o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,

           o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,

           o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,

           d.description AS department, o.payment_id, o.language_id, o.taxzone_id,

sub create_and_fill_table {

  my $self = shift;

  #Create an fill table currencies:

  $self->db_query($query);

  foreach my $item ( @_ ) {

    $self->db_query($query);

  }

    $self->db_query($query);

  }

  $self->db_query($query);

  #Set defaultcurrency:

  $self->db_query($query);

  $self->db_query($query);

  #Drop column 'curr':

              ALTER TABLE defaults DROP COLUMN curr;|;

  $self->db_query($query);

  #Set NOT NULL constraints:

  $self->db_query($query);

  #Set foreign keys:

  $self->db_query($query);

};

sub run {

  my ($self) = @_;

  $query = qq|SELECT curr FROM defaults|;

  my ($curr)     = $self->dbh->selectrow_array($query);