projekt kivitendo

      SELECT oe.globalproject_id

      FROM oe

      WHERE (oe.globalproject_id IS NOT NULL)

        AND     COALESCE(oe.closed,    FALSE)

    )

EOSQL

    }

    $orders = SL::DB::Manager::Order->get_all(where        => [customer_id      => $tr->customer_id,

                                                               globalproject_id => $project_id, ],

                                              with_objects => ['orderitems']);

  my $title          = "Alle offenen Auftragsbestätigungen mit Liefertermin vor mindestens $days_delta Werktagen haben eine Lieferung.";

  my $latest_reqdate = DateTime->today_local->subtract_businessdays(days => $days_delta);

                                                                        or              => [closed    => undef, closed    => 0],

                                                                        reqdate         => {le => $latest_reqdate},

                                                                        transdate       => {ge => $self->start_date},]);

  my $latest_transdate = DateTime->today_local->subtract_businessdays(days => $days_delta);

                                                                          or              => [closed    => undef, closed    => 0],

                                                                          transdate       => {le => $latest_transdate},

                                                                          transdate       => {ge => $self->start_date},]);

  my $latest_transdate = DateTime->today_local->subtract_businessdays(days => $days_delta);

                                                                          or              => [closed    => undef, closed    => 0],

                                                                          transdate       => {le => $latest_transdate},

                                                                          transdate       => {ge => $self->start_date},]);

  my $latest_transdate = DateTime->today_local->subtract_businessdays(days => $days_delta);

                                                                          or              => [closed    => undef, closed    => 0],

                                                                          transdate       => {le => $latest_transdate},

                                                                          transdate       => {ge => $self->start_date},]);

        qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .

        qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .

        $pg_join .

    }

    if ( $form->{l_quonumber} ) {

        qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .

        qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .

        $pg_join .

    }

  }

use SL::DB::AccTransaction;

use SL::DB::Invoice;

use SL::DB::Order;

use SL::DB::EmailJournal;

use SL::DB::Letter;

use SL::DB;

    $orders = SL::DB::Manager::Order->get_all(

      query   => [

                   customer_id => $cv,

                 ],

      sort_by => 'transdate DESC',

    );

    $orders = SL::DB::Manager::Order->get_all(

      query   => [

                   vendor_id => $cv,

                 ],

      sort_by => 'transdate DESC',

    );

    $query = <<SQL;

WITH

oe_emails_customer

     FROM record_links rc

LEFT JOIN oe oe      ON rc.from_id = oe.id

LEFT JOIN customer c ON oe.customer_id = c.id

SELECT ej.*,

 CASE

 END AS type,

 CASE

 END    AS recordnumber,

 oec.id AS record_id

     FROM email_journal ej

    $query = <<SQL;

WITH

oe_emails_vendor

     FROM record_links rc

LEFT JOIN oe oe ON rc.from_id = oe.id

LEFT JOIN vendor c ON oe.vendor_id = c.id

SELECT ej.*,

 CASE

 END AS type,

 CASE

 END   AS recordnumber,

oec.id AS record_id

     FROM email_journal ej

sub delivery_plan_query_linked_items {

  my ($self) = @_;

  my $employee_id = SL::DB::Manager::Employee->current->id;

  my $oe_owner = $_[0]->all_edit_right ? '' : " oe.employee_id = $employee_id AND";

  [

  'order.closed' => 0,

  # filter by shipped_qty < qty, read from innermost to outermost

  'id' => [ \"

      FROM orderitems oi, oe, record_links rl, delivery_order_items doi

      WHERE

        oe.id = oi.trans_id AND

        NOT oe.closed AND

        $oe_owner

        doi.id = rl.to_id AND

    SELECT oi.id FROM orderitems oi, oe

    WHERE

      oe.id = oi.trans_id AND

      NOT oe.closed AND

      $oe_owner

      NOT EXISTS (

  my $sales_quotations   = SL::DB::Manager::Order->get_all(where => [id            => $::form->{ids},

                                                                     or             => [closed => 0, closed => undef],

  my $request_quotations = SL::DB::Manager::Order->get_all(where => [id            => $::form->{ids},

                                                                     or             => [closed => 0, closed => undef],

  $::auth->assert('sales_quotation_edit')   if scalar @$sales_quotations;

  $::auth->assert('request_quotation_edit') if scalar @$request_quotations;

  $order   = SL::DB::Order->new(id => $::form->{id})->load(with => [ 'orderitems', 'orderitems.part' ]) if $::form->{id};

  $order ||= SL::DB::Order->new(orderitems  => [],

                                record_type => $self->type,

                                currency_id => $::instance_conf->get_currency_id(),);

  my $cv_id_method = $self->cv . '_id';

  my $is_new = !$self->order->id;

  my $objects_to_close = scalar @converted_from_oe_ids

                       : undef;

  my $items_to_delete  = scalar @{ $self->item_ids_to_delete || [] }

  my $order    = $self->rs_order->order;

  my $sections = $self->requirement_spec->sections_sorted;

    return $self->js->flash('error', t8("You do not have the permissions to access this function."))->render;

  }

    globalproject_id        => $self->requirement_spec->project_id,

    transdate               => DateTime->today_local,

    reqdate                 => $reqdate,

    orderitems              => [ @orderitems, @add_items ],

    customer_id             => $customer->id,

    taxincluded             => $customer->taxincluded,

sub init_all_orders {

  my $orders = SL::DB::Manager::Order->get_all(query => [or             => [ closed    => 0, closed    => undef, id => $_[0]->time_recording->order_id ],

  return [ map { [$_->id, sprintf("%s %s", $_->number, $_->customervendor->name) ] } sort { $a->number <=> $b->number } @{$orders||[]} ];

}

  $sort_dir = $sort_dir * 1 ? 1 : -1;

  my %numbers = ( 'SL::DB::SalesProcess'    => sub { $_[0]->id },

                  'SL::DB::DeliveryOrder'   => sub { $_[0]->donumber },

                  'SL::DB::Invoice'         => sub { $_[0]->invnumber },

                  'SL::DB::PurchaseInvoice' => sub { $_[0]->invnumber },

    FROM orderitems oi

    LEFT JOIN oe ON (oi.trans_id = oe.id)

    WHERE (oi.parts_id IN ($placeholders))

      AND (NOT COALESCE(oe.closed,    FALSE))

      AND (NOT COALESCE(oe.delivered, FALSE))

    GROUP BY oi.parts_id

SQL

         AND e.transdate = o.transdate)

    FROM oe o

    WHERE (o.${type}_id = ?)

      AND NOT COALESCE(o.closed,    FALSE)

SQL

  $form->{DO} = selectall_hashref_query($form, $dbh, $query, @values);

  if (scalar @{ $form->{DO} }) {

    $query =

      qq|SELECT id

         FROM oe

    my $sth = prepare_query($form, $dbh, $query);

sub _create_sales_order_or_quotation {

  my (%params) = @_;

  die "illegal type" unless $record_type eq SALES_ORDER_TYPE() or $record_type eq SALES_QUOTATION_TYPE();

  my $orderitems = delete $params{orderitems} // _create_two_items($record_type);

  die "illegal customer" unless ref($customer) eq 'SL::DB::Customer';

  my $record = SL::DB::Order->new(

    customer_id  => delete $params{customer_id} // $customer->id,

    taxzone_id   => delete $params{taxzone_id}  // $customer->taxzone->id,

    currency_id  => delete $params{currency_id} // $::instance_conf->get_currency_id,

    employee_id  => delete $params{employee_id} // SL::DB::Manager::Employee->current->id,

    salesman_id  => delete $params{employee_id} // SL::DB::Manager::Employee->current->id,

    transdate    => delete $params{transdate}   // DateTime->today,

    orderitems   => $orderitems,

  );

  $record->assign_attributes(%params) if %params;

sub _create_purchase_order_or_quotation {

  my (%params) = @_;

  die "illegal type" unless $record_type eq PURCHASE_ORDER_TYPE() or $record_type eq REQUEST_QUOTATION_TYPE();

  my $orderitems = delete $params{orderitems} // _create_two_items($record_type);

  _check_items($orderitems, $record_type);

  die "illegal vendor" unless ref($vendor) eq 'SL::DB::Vendor';

  my $record = SL::DB::Order->new(

    vendor_id    => delete $params{vendor_id}   // $vendor->id,

    taxzone_id   => delete $params{taxzone_id}  // $vendor->taxzone->id,

    currency_id  => delete $params{currency_id} // $::instance_conf->get_currency_id,

    taxincluded  => delete $params{taxincluded} // 0,

    transdate    => delete $params{transdate}   // DateTime->today,

    'closed'     => undef,

    orderitems   => $orderitems,

  );

  $record->assign_attributes(%params) if %params;

       ) AS ioi ON ioi.parts_id = p.id|,

    apoe       =>

      q|LEFT JOIN (

       ) AS apoe ON ((ioi.trans_id = apoe.id) AND (ioi.ioi = apoe.ioi))|,

    cv         =>

      q|LEFT JOIN (

     description  => 'p.',

     qty          => 'ioi.',

     serialnumber => 'ioi.',

     cv           => 'cv.',

     "ioi.id"     => ' ',

     "ioi.ioi"    => ' ',

  my $bsooqr        = any { $form->{$_} } @oe_flags;

  my @bsooqr_tokens = ();

  push @select_tokens, @deliverydate_flags                                    if $bsooqr && $form->{l_deliverydate};

  push @select_tokens, $q_assembly_lastcost                                   if $form->{l_assembly} && $form->{l_lastcost};

  push @bsooqr_tokens, q|module = 'ir' AND NOT ioi.assemblyitem|              if $form->{bought};

  push @bsooqr_tokens, q|module = 'is' AND NOT ioi.assemblyitem|              if $form->{sold};

  push @where_tokens, join ' OR ', map { "($_)" } @bsooqr_tokens              if $bsooqr;

  $joins_needed{partsgroup}  = 1;

                 WHERE (e.currency_id = o.currency_id)

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

              FROM oe o

  my $sth = prepare_execute_query($form, $dbh, $query, $vid);

  while (my ($amount, $exch) = $sth->fetchrow_array()) {

    $exch = 1 unless $exch;

            AND e.transdate = o.transdate)

       FROM oe o

       WHERE o.customer_id = ?

         AND o.closed = '0'|;

  my $sth = prepare_execute_query($form, $dbh, $query, $cid);

    LEFT JOIN buchungsgruppen bg ON (p.buchungsgruppen_id                     = bg.id)

    LEFT JOIN partsgroup pg      ON (p.partsgroup_id                          = pg.id)

    LEFT JOIN employee e         ON (COALESCE(oe.salesman_id, oe.employee_id) = e.id)

      AND NOT COALESCE(oe.closed,    FALSE)

      AND (oe.id NOT IN (SELECT oe_id FROM periodic_invoices_configs WHERE periodicity <> 'o'))

SQL

  }

  my @where = (

    or             => [ closed    => undef, closed    => 0, ],

  );

  push @where, (reqdate => { ge => $params{after}->clone })  if $params{after};

  my $query;

  my $ordnumber = 'ordnumber';

  my @values;

  my $where;

  my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell';

  if ($form->{type} =~ /_quotation$/) {

    $ordnumber = 'quonumber';

  } elsif ($form->{type} eq 'purchase_quotation_intake') {

    $ordnumber = 'quonumber';

  } elsif ($form->{type} eq 'sales_order') {

    $periodic_invoices_columns = qq| , COALESCE(pcfg.active, 'f') AS periodic_invoices |;

    $periodic_invoices_joins   = qq| LEFT JOIN periodic_invoices_configs pcfg ON (o.id = pcfg.oe_id) |;

       )| .

    qq|$periodic_invoices_joins | .

    $phone_notes_join .

  if ($form->{department_id}) {

    $query .= qq| AND o.department_id = ?|;

       LEFT JOIN customer c ON (oe.customer_id = c.id)

       LEFT JOIN vendor v   ON (oe.vendor_id   = v.id)

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

         AND (COALESCE(closed,    FALSE) = FALSE)

         AND ((oe.employee_id = ?) OR (oe.salesman_id = ?))

         AND NOT (oe.reqdate ISNULL)

  SL::DB->client->with_transaction(sub {

    my $sth      = prepare_query($form, $dbh, $query);

    do_statement($form, $sth, $query, conv_i($params{to_id}));

      return 1;

    }

    foreach my $from_id (@{ $params{from_id} }) {

      $from_id = conv_i($from_id);

      do_statement($form, $sth, $query, $from_id);

    }

    $sth->finish();

  use SL::DB::Order;

  use SL::Presenter::Record qw(grouped_record_list);

  my $records   = $quotation->linked_records(direction => 'to');

  my $html      = grouped_record_list($records);

  croak "Unknown display type '$params{display}'" unless $params{display} =~ m/^(?:inline|table-cell)$/;

  if (! delete $params{no_link}) {

    my $action  = $::instance_conf->get_feature_experimental_order

                ? 'controller.pl?action=Order/edit'

sub new_order {

  my %params  = @_;

  return SL::DB::Order->new(

    customer_id => $customer->id,

    currency_id => $currency_id,

    employee_id => $employee->id,

    salesman_id => $employee->id,

    taxzone_id  => $taxzone->id,

    %params,

  )->save;

}

is @$links, 0, 'no dangling link after delete';

# can we distinguish between types?

$o2 = new_order();

$o1->link_to_record($o2);

is $links->[0]->id, $o1->id, 'query restricted retrieve 1';

is @$links, 0, 'query restricted retrieve 2';

# try bidirectional linking

reset_basic_sales_records();

reset_basic_purchase_records();

is(SL::DB::Manager::DeliveryOrder->get_all_count(), 2, 'number of delivery orders before delete ok');

is(SL::DB::Manager::Reclamation->get_all_count(), 2, 'number of reclamations before delete ok');

# is(SL::DB::Manager::Invoice->get_all_count(), 1, 'number of invoices before delete ok'); # no purchase_invoice was created

  ok($record_history->snumbers =~ m/_/, "history snumbers of record " . $record_history->snumbers . " ok");

};

# is(SL::DB::Manager::Invoice->get_all_count(), 0, 'number of invoices after delete ok');

is(SL::DB::Manager::Reclamation->get_all_count(), 0, 'number of orders after delete ok');

SL::DB::OrderVersion->new(oe_id => $sales_order1->id, version => 1, final_version => 1)->save;

SL::Model::Record->increment_subversion($sales_order1);

is($sales_order1->ordnumber, "ord-01-2", "ordnumber after increment_subversion ok");

note "testing new_from_workflow for quotation";

SELECT extract(YEAR FROM oe.transdate) AS "Jahr", SUM(oe.amount) AS "Angebotssumme"

FROM oe

LEFT JOIN employee ON (oe.employee_id = employee.id)

  AND (employee.login = <%Benutzer-Login%>)

GROUP BY "Jahr"

ORDER BY "Jahr"

SELECT extract(YEAR FROM oe.transdate) AS "Jahr", SUM(oe.amount) AS "Angebotssumme"

FROM oe

LEFT JOIN employee ON (oe.employee_id = employee.id)

  AND (employee.login = <%Benutzer-Login%>)

GROUP BY "Jahr"

ORDER BY "Jahr"