From b2bedb6b5eff41a17ddb194df1c75213b841f3bb Mon Sep 17 00:00:00 2001
From: Werner Hahn <wh@futureworldsearch.net>
Date: Fri, 22 Sep 2017 02:19:56 +0200
Subject: [PATCH] WebshopApi: Shoptabellen

---
 SL/DB/Helper/ALL.pm                           |   4 +
 SL/DB/Helper/Mappings.pm                      |   4 +
 SL/DB/Manager/Shop.pm                         |  28 ++
 SL/DB/Manager/ShopOrder.pm                    |  15 +
 SL/DB/Manager/ShopOrderItem.pm                |  15 +
 SL/DB/Manager/ShopPart.pm                     |  16 ++
 SL/DB/MetaSetup/Shop.pm                       |  39 +++
 SL/DB/MetaSetup/ShopOrder.pm                  | 103 +++++++
 SL/DB/MetaSetup/ShopOrderItem.pm              |  34 +++
 SL/DB/MetaSetup/ShopPart.pm                   |  49 ++++
 SL/DB/Part.pm                                 |   6 +
 SL/DB/Shop.pm                                 |  72 +++++
 SL/DB/ShopOrder.pm                            | 259 ++++++++++++++++++
 SL/DB/ShopOrderItem.pm                        |  13 +
 SL/DB/ShopPart.pm                             | 111 ++++++++
 sql/Pg-upgrade2/shop_orders.sql               | 103 +++++++
 .../shop_orders_add_active_pricesource.sql    |   5 +
 sql/Pg-upgrade2/shop_orders_update_1.sql      |  21 ++
 sql/Pg-upgrade2/shop_orders_update_2.sql      |   6 +
 sql/Pg-upgrade2/shop_orders_update_3.sql      |   8 +
 sql/Pg-upgrade2/shop_parts.sql                |  28 ++
 sql/Pg-upgrade2/shops.sql                     |  21 ++
 sql/Pg-upgrade2/shops_1.sql                   |   9 +
 sql/Pg-upgrade2/shops_2.sql                   |   7 +
 sql/Pg-upgrade2/shops_3.sql                   |  14 +
 25 files changed, 990 insertions(+)
 create mode 100644 SL/DB/Manager/Shop.pm
 create mode 100644 SL/DB/Manager/ShopOrder.pm
 create mode 100644 SL/DB/Manager/ShopOrderItem.pm
 create mode 100644 SL/DB/Manager/ShopPart.pm
 create mode 100644 SL/DB/MetaSetup/Shop.pm
 create mode 100644 SL/DB/MetaSetup/ShopOrder.pm
 create mode 100644 SL/DB/MetaSetup/ShopOrderItem.pm
 create mode 100644 SL/DB/MetaSetup/ShopPart.pm
 create mode 100644 SL/DB/Shop.pm
 create mode 100644 SL/DB/ShopOrder.pm
 create mode 100644 SL/DB/ShopOrderItem.pm
 create mode 100644 SL/DB/ShopPart.pm
 create mode 100644 sql/Pg-upgrade2/shop_orders.sql
 create mode 100644 sql/Pg-upgrade2/shop_orders_add_active_pricesource.sql
 create mode 100644 sql/Pg-upgrade2/shop_orders_update_1.sql
 create mode 100644 sql/Pg-upgrade2/shop_orders_update_2.sql
 create mode 100644 sql/Pg-upgrade2/shop_orders_update_3.sql
 create mode 100644 sql/Pg-upgrade2/shop_parts.sql
 create mode 100644 sql/Pg-upgrade2/shops.sql
 create mode 100644 sql/Pg-upgrade2/shops_1.sql
 create mode 100644 sql/Pg-upgrade2/shops_2.sql
 create mode 100644 sql/Pg-upgrade2/shops_3.sql

diff --git a/SL/DB/Helper/ALL.pm b/SL/DB/Helper/ALL.pm
index f1059f44c..acc9f7e86 100644
--- a/SL/DB/Helper/ALL.pm
+++ b/SL/DB/Helper/ALL.pm
@@ -115,6 +115,10 @@ use SL::DB::SepaExport;
 use SL::DB::SepaExportItem;
 use SL::DB::SepaExportMessageId;
 use SL::DB::Shipto;
+use SL::DB::Shop;
+use SL::DB::ShopOrder;
+use SL::DB::ShopOrderItem;
+use SL::DB::ShopPart;
 use SL::DB::Status;
 use SL::DB::Tax;
 use SL::DB::TaxKey;
diff --git a/SL/DB/Helper/Mappings.pm b/SL/DB/Helper/Mappings.pm
index 5718d3eb9..c1327f5fb 100644
--- a/SL/DB/Helper/Mappings.pm
+++ b/SL/DB/Helper/Mappings.pm
@@ -195,6 +195,10 @@ my %kivitendo_package_names = (
   sepa_export_message_ids        => 'SepaExportMessageId',
   schema_info                    => 'schema_info',
   shipto                         => 'shipto',
+  shops                          => 'shop',
+  shop_orders                    => 'shop_order',
+  shop_order_items               => 'shop_order_item',
+  shop_parts                     => 'shop_part',
   status                         => 'status',
   tax                            => 'tax',
   taxkeys                        => 'tax_key',
diff --git a/SL/DB/Manager/Shop.pm b/SL/DB/Manager/Shop.pm
new file mode 100644
index 000000000..b7a19581e
--- /dev/null
+++ b/SL/DB/Manager/Shop.pm
@@ -0,0 +1,28 @@
+# This file has been auto-generated only because it didn't exist.
+# Feel free to modify it at will; it will not be overwritten automatically.
+
+package SL::DB::Manager::Shop;
+
+use strict;
+
+use SL::DB::Helper::Manager;
+use base qw(SL::DB::Helper::Manager);
+
+sub object_class { 'SL::DB::Shop' }
+
+use SL::DB::Helper::Sorted;
+
+__PACKAGE__->make_manager_methods;
+
+sub _sort_spec {
+  return ( default => [ 'sortkey', 1 ],
+           columns => { SIMPLE => 'ALL' } );
+}
+
+sub get_default {
+    return $_[0]->get_first(where => [ obsolete => 0 ], sort_by => 'sortkey');
+}
+
+1;
+
+1;
diff --git a/SL/DB/Manager/ShopOrder.pm b/SL/DB/Manager/ShopOrder.pm
new file mode 100644
index 000000000..3311f1b3c
--- /dev/null
+++ b/SL/DB/Manager/ShopOrder.pm
@@ -0,0 +1,15 @@
+# This file has been auto-generated only because it didn't exist.
+# Feel free to modify it at will; it will not be overwritten automatically.
+
+package SL::DB::Manager::ShopOrder;
+
+use strict;
+
+use SL::DB::Helper::Manager;
+use base qw(SL::DB::Helper::Manager);
+
+sub object_class { 'SL::DB::ShopOrder' }
+
+__PACKAGE__->make_manager_methods;
+
+1;
diff --git a/SL/DB/Manager/ShopOrderItem.pm b/SL/DB/Manager/ShopOrderItem.pm
new file mode 100644
index 000000000..3a279d663
--- /dev/null
+++ b/SL/DB/Manager/ShopOrderItem.pm
@@ -0,0 +1,15 @@
+# This file has been auto-generated only because it didn't exist.
+# Feel free to modify it at will; it will not be overwritten automatically.
+
+package SL::DB::Manager::ShopOrderItem;
+
+use strict;
+
+use SL::DB::Helper::Manager;
+use base qw(SL::DB::Helper::Manager);
+
+sub object_class { 'SL::DB::ShopOrderItem' }
+
+__PACKAGE__->make_manager_methods;
+
+1;
diff --git a/SL/DB/Manager/ShopPart.pm b/SL/DB/Manager/ShopPart.pm
new file mode 100644
index 000000000..696fdbe27
--- /dev/null
+++ b/SL/DB/Manager/ShopPart.pm
@@ -0,0 +1,16 @@
+# This file has been auto-generated only because it didn't exist.
+# Feel free to modify it at will; it will not be overwritten automatically.
+
+package SL::DB::Manager::ShopPart;
+#package SL::DB::Manager::ShopPart;
+
+use strict;
+
+use SL::DB::Helper::Manager;
+use base qw(SL::DB::Helper::Manager);
+
+sub object_class { 'SL::DB::ShopPart' }
+
+__PACKAGE__->make_manager_methods;
+
+1;
diff --git a/SL/DB/MetaSetup/Shop.pm b/SL/DB/MetaSetup/Shop.pm
new file mode 100644
index 000000000..7987e57cd
--- /dev/null
+++ b/SL/DB/MetaSetup/Shop.pm
@@ -0,0 +1,39 @@
+# This file has been auto-generated. Do not modify it; it will be overwritten
+# by rose_auto_create_model.pl automatically.
+package SL::DB::Shop;
+
+use strict;
+
+use parent qw(SL::DB::Object);
+
+__PACKAGE__->meta->table('shops');
+
+__PACKAGE__->meta->columns(
+  connector               => { type => 'text' },
+  description             => { type => 'text' },
+  id                      => { type => 'serial', not_null => 1 },
+  itime                   => { type => 'timestamp', default => 'now()' },
+  last_order_number       => { type => 'integer' },
+  login                   => { type => 'text' },
+  mtime                   => { type => 'timestamp', default => 'now()' },
+  obsolete                => { type => 'boolean', default => 'false', not_null => 1 },
+  orders_to_fetch         => { type => 'integer' },
+  password                => { type => 'text' },
+  path                    => { type => 'text', default => '/', not_null => 1 },
+  port                    => { type => 'integer' },
+  price_source            => { type => 'text' },
+  pricetype               => { type => 'text' },
+  protocol                => { type => 'text', default => 'http', not_null => 1 },
+  realm                   => { type => 'text' },
+  server                  => { type => 'text' },
+  sortkey                 => { type => 'integer' },
+  taxzone_id              => { type => 'integer' },
+  transaction_description => { type => 'text' },
+);
+
+__PACKAGE__->meta->primary_key_columns([ 'id' ]);
+
+__PACKAGE__->meta->allow_inline_column_values(1);
+
+1;
+;
diff --git a/SL/DB/MetaSetup/ShopOrder.pm b/SL/DB/MetaSetup/ShopOrder.pm
new file mode 100644
index 000000000..cd31c18b7
--- /dev/null
+++ b/SL/DB/MetaSetup/ShopOrder.pm
@@ -0,0 +1,103 @@
+# This file has been auto-generated. Do not modify it; it will be overwritten
+# by rose_auto_create_model.pl automatically.
+package SL::DB::ShopOrder;
+
+use strict;
+
+use parent qw(SL::DB::Object);
+
+__PACKAGE__->meta->table('shop_orders');
+
+__PACKAGE__->meta->columns(
+  amount                 => { type => 'numeric', precision => 15, scale => 5 },
+  billing_city           => { type => 'text' },
+  billing_company        => { type => 'text' },
+  billing_country        => { type => 'text' },
+  billing_department     => { type => 'text' },
+  billing_email          => { type => 'text' },
+  billing_fax            => { type => 'text' },
+  billing_firstname      => { type => 'text' },
+  billing_greeting       => { type => 'text' },
+  billing_lastname       => { type => 'text' },
+  billing_phone          => { type => 'text' },
+  billing_street         => { type => 'text' },
+  billing_vat            => { type => 'text' },
+  billing_zipcode        => { type => 'text' },
+  customer_city          => { type => 'text' },
+  customer_company       => { type => 'text' },
+  customer_country       => { type => 'text' },
+  customer_department    => { type => 'text' },
+  customer_email         => { type => 'text' },
+  customer_fax           => { type => 'text' },
+  customer_firstname     => { type => 'text' },
+  customer_greeting      => { type => 'text' },
+  customer_lastname      => { type => 'text' },
+  customer_newsletter    => { type => 'boolean' },
+  customer_phone         => { type => 'text' },
+  customer_street        => { type => 'text' },
+  customer_vat           => { type => 'text' },
+  customer_zipcode       => { type => 'text' },
+  delivery_city          => { type => 'text' },
+  delivery_company       => { type => 'text' },
+  delivery_country       => { type => 'text' },
+  delivery_department    => { type => 'text' },
+  delivery_email         => { type => 'text' },
+  delivery_fax           => { type => 'text' },
+  delivery_firstname     => { type => 'text' },
+  delivery_greeting      => { type => 'text' },
+  delivery_lastname      => { type => 'text' },
+  delivery_phone         => { type => 'text' },
+  delivery_street        => { type => 'text' },
+  delivery_vat           => { type => 'text' },
+  delivery_zipcode       => { type => 'text' },
+  host                   => { type => 'text' },
+  id                     => { type => 'serial', not_null => 1 },
+  itime                  => { type => 'timestamp', default => 'now()' },
+  kivi_customer_id       => { type => 'integer' },
+  mtime                  => { type => 'timestamp' },
+  netamount              => { type => 'numeric', precision => 15, scale => 5 },
+  obsolete               => { type => 'boolean', default => 'false', not_null => 1 },
+  order_date             => { type => 'timestamp' },
+  payment_description    => { type => 'text' },
+  payment_id             => { type => 'integer' },
+  positions              => { type => 'integer' },
+  remote_ip              => { type => 'text' },
+  sepa_account_holder    => { type => 'text' },
+  sepa_bic               => { type => 'text' },
+  sepa_iban              => { type => 'text' },
+  shipping_costs         => { type => 'numeric', precision => 15, scale => 5 },
+  shipping_costs_id      => { type => 'integer' },
+  shipping_costs_net     => { type => 'numeric', precision => 15, scale => 5 },
+  shop_c_billing_id      => { type => 'integer' },
+  shop_c_billing_number  => { type => 'text' },
+  shop_c_delivery_id     => { type => 'integer' },
+  shop_c_delivery_number => { type => 'text' },
+  shop_customer_comment  => { type => 'text' },
+  shop_customer_id       => { type => 'integer' },
+  shop_customer_number   => { type => 'text' },
+  shop_id                => { type => 'integer' },
+  shop_ordernumber       => { type => 'text' },
+  shop_trans_id          => { type => 'integer', not_null => 1 },
+  tax_included           => { type => 'boolean' },
+  transfer_date          => { type => 'date' },
+  transferred            => { type => 'boolean', default => 'false' },
+);
+
+__PACKAGE__->meta->primary_key_columns([ 'id' ]);
+
+__PACKAGE__->meta->allow_inline_column_values(1);
+
+__PACKAGE__->meta->foreign_keys(
+  kivi_customer => {
+    class       => 'SL::DB::Customer',
+    key_columns => { kivi_customer_id => 'id' },
+  },
+
+  shop => {
+    class       => 'SL::DB::Shop',
+    key_columns => { shop_id => 'id' },
+  },
+);
+
+1;
+;
diff --git a/SL/DB/MetaSetup/ShopOrderItem.pm b/SL/DB/MetaSetup/ShopOrderItem.pm
new file mode 100644
index 000000000..dfb2255a0
--- /dev/null
+++ b/SL/DB/MetaSetup/ShopOrderItem.pm
@@ -0,0 +1,34 @@
+# This file has been auto-generated. Do not modify it; it will be overwritten
+# by rose_auto_create_model.pl automatically.
+package SL::DB::ShopOrderItem;
+
+use strict;
+
+use parent qw(SL::DB::Object);
+
+__PACKAGE__->meta->table('shop_order_items');
+
+__PACKAGE__->meta->columns(
+  active_price_source => { type => 'text' },
+  description         => { type => 'text' },
+  id                  => { type => 'serial', not_null => 1 },
+  partnumber          => { type => 'text' },
+  position            => { type => 'integer' },
+  price               => { type => 'numeric', precision => 15, scale => 5 },
+  quantity            => { type => 'numeric', precision => 25, scale => 5 },
+  shop_order_id       => { type => 'integer' },
+  shop_trans_id       => { type => 'integer', not_null => 1 },
+  tax_rate            => { type => 'numeric', precision => 15, scale => 2 },
+);
+
+__PACKAGE__->meta->primary_key_columns([ 'id' ]);
+
+__PACKAGE__->meta->foreign_keys(
+  shop_order => {
+    class       => 'SL::DB::ShopOrder',
+    key_columns => { shop_order_id => 'id' },
+  },
+);
+
+1;
+;
diff --git a/SL/DB/MetaSetup/ShopPart.pm b/SL/DB/MetaSetup/ShopPart.pm
new file mode 100644
index 000000000..4dcf15983
--- /dev/null
+++ b/SL/DB/MetaSetup/ShopPart.pm
@@ -0,0 +1,49 @@
+# This file has been auto-generated. Do not modify it; it will be overwritten
+# by rose_auto_create_model.pl automatically.
+package SL::DB::ShopPart;
+
+use strict;
+
+use parent qw(SL::DB::Object);
+
+__PACKAGE__->meta->table('shop_parts');
+
+__PACKAGE__->meta->columns(
+  active              => { type => 'boolean', default => 'false', not_null => 1 },
+  active_price_source => { type => 'text' },
+  front_page          => { type => 'boolean', default => 'false', not_null => 1 },
+  id                  => { type => 'serial', not_null => 1 },
+  itime               => { type => 'timestamp', default => 'now()' },
+  last_update         => { type => 'timestamp' },
+  metatag_description => { type => 'text' },
+  metatag_keywords    => { type => 'text' },
+  metatag_title       => { type => 'text' },
+  mtime               => { type => 'timestamp' },
+  part_id             => { type => 'integer', not_null => 1 },
+  shop_category       => { type => 'array' },
+  shop_description    => { type => 'text' },
+  shop_id             => { type => 'integer', not_null => 1 },
+  show_date           => { type => 'date' },
+  sortorder           => { type => 'integer' },
+);
+
+__PACKAGE__->meta->primary_key_columns([ 'id' ]);
+
+__PACKAGE__->meta->unique_keys([ 'shop_id', 'part_id' ]);
+
+__PACKAGE__->meta->allow_inline_column_values(1);
+
+__PACKAGE__->meta->foreign_keys(
+  part => {
+    class       => 'SL::DB::Part',
+    key_columns => { part_id => 'id' },
+  },
+
+  shop => {
+    class       => 'SL::DB::Shop',
+    key_columns => { shop_id => 'id' },
+  },
+);
+
+1;
+;
diff --git a/SL/DB/Part.pm b/SL/DB/Part.pm
index 6ce6ed5b7..8036db532 100644
--- a/SL/DB/Part.pm
+++ b/SL/DB/Part.pm
@@ -54,6 +54,12 @@ __PACKAGE__->meta->add_relationships(
     query_args      => [ what_done => 'part' ],
     manager_args    => { sort_by => 'itime' },
   },
+  shop_parts     => {
+    type         => 'one to many',
+    class        => 'SL::DB::ShopPart',
+    column_map   => { id => 'part_id' },
+    manager_args => { with_objects => [ 'shop' ] },
+  },
 );
 
 __PACKAGE__->meta->initialize;
diff --git a/SL/DB/Shop.pm b/SL/DB/Shop.pm
new file mode 100644
index 000000000..63f988f03
--- /dev/null
+++ b/SL/DB/Shop.pm
@@ -0,0 +1,72 @@
+# This file has been auto-generated only because it didn't exist.
+# Feel free to modify it at will; it will not be overwritten automatically.
+
+package SL::DB::Shop;
+
+use strict;
+
+use SL::DB::MetaSetup::Shop;
+use SL::DB::Manager::Shop;
+use SL::DB::Helper::ActsAsList;
+use SL::Locale::String qw(t8);
+
+__PACKAGE__->meta->initialize;
+
+sub validate {
+  my ($self) = @_;
+
+  my @errors;
+
+  push @errors, $::locale->text('The description is missing.') unless $self->{description};
+  push @errors, $::locale->text('The path is missing.') unless $self->{path};
+
+  return @errors;
+}
+
+sub shops_dd {
+  my ( $self ) = @_;
+
+  my @shops_dd = ( { title => t8("all") ,   value =>'' } );
+  my $shops = SL::DB::Manager::Shop->get_all( where => [ obsolete => 0 ] );
+  my @tmp = map { { title => $_->{description}, value => $_->{id} } } @{ $shops } ;
+  push @shops_dd, @tmp;
+  return \@shops_dd;
+}
+
+1;
+
+__END__
+
+=pod
+
+=encoding utf-8
+
+=head1 NAME
+
+SL::DB::Shop - Model for the 'shops' table
+
+=head1 SYNOPSIS
+
+This is a standard Rose::DB::Object based model and can be used as one.
+
+=head1 METHODS
+
+=over 4
+
+=item C<validate>
+
+Returns an error if the shop description is missing
+
+=item C<shops_dd>
+
+Returns an array of hashes for dropdowns in filters
+
+=back
+
+=head1 AUTHORS
+
+Werner Hahn E<lt>wh@futureworldsearch.netE<gt>
+
+G. Richardson E<lt>grichardson@kivitendo-premium.deE<gt>
+
+=cut
diff --git a/SL/DB/ShopOrder.pm b/SL/DB/ShopOrder.pm
new file mode 100644
index 000000000..55dfc0640
--- /dev/null
+++ b/SL/DB/ShopOrder.pm
@@ -0,0 +1,259 @@
+# This file has been auto-generated only because it didn't exist.
+# Feel free to modify it at will; it will not be overwritten automatically.
+
+package SL::DB::ShopOrder;
+
+use strict;
+
+use SL::DBUtils;
+use SL::DB::Shop;
+use SL::DB::MetaSetup::ShopOrder;
+use SL::DB::Manager::ShopOrder;
+use SL::DB::Helper::LinkedRecords;
+use SL::Locale::String qw(t8);
+use Carp;
+
+__PACKAGE__->meta->add_relationships(
+  shop_order_items => {
+    class      => 'SL::DB::ShopOrderItem',
+    column_map => { id => 'shop_order_id' },
+    type       => 'one to many',
+  },
+);
+
+__PACKAGE__->meta->initialize;
+
+sub convert_to_sales_order {
+  my ($self, %params) = @_;
+
+  my $customer = delete $params{customer};
+  my $employee = delete $params{employee};
+  croak "param customer is missing" unless ref($customer) eq 'SL::DB::Customer';
+  croak "param employee is missing" unless ref($employee) eq 'SL::DB::Employee';
+
+  require SL::DB::Order;
+  require SL::DB::OrderItem;
+  require SL::DB::Part;
+  require SL::DB::Shipto;
+  my @error_report;
+
+  my @items = map{
+
+    my $part = SL::DB::Manager::Part->find_by(partnumber => $_->partnumber);
+
+    unless($part){
+      push @error_report, t8('Part with partnumber: #1 not found', $_->partnumber);
+    }else{
+      my $current_order_item = SL::DB::OrderItem->new(
+        parts_id            => $part->id,
+        description         => $part->description,
+        qty                 => $_->quantity,
+        sellprice           => $_->price,
+        unit                => $part->unit,
+        position            => $_->position,
+        active_price_source => $_->active_price_source,
+      );
+    }
+  }@{ $self->shop_order_items };
+
+  if(!scalar(@error_report)){
+
+    my $shipto_id;
+    if ($self->billing_firstname ne $self->delivery_firstname || $self->billing_lastname ne $self->delivery_lastname || $self->billing_city ne $self->delivery_city || $self->billing_street ne $self->delivery_street) {
+      if(my $address = SL::DB::Manager::Shipto->find_by( shiptoname   => $self->delivery_firstname . " " . $self->delivery_lastname,
+                                                         shiptostreet => $self->delivery_street,
+                                                         shiptocity   => $self->delivery_city,
+                                                        )) {
+        $shipto_id = $address->{shipto_id};
+      } else {
+        my $deliveryaddress = SL::DB::Shipto->new;
+        $deliveryaddress->assign_attributes(
+          shiptoname         => $self->delivery_firstname . " " . $self->delivery_lastname,
+          shiptodepartment_1 => $self->delivery_company,
+          shiptodepartment_2 => $self->delivery_department,
+          shiptostreet       => $self->delivery_street,
+          shiptozipcode      => $self->delivery_zipcode,
+          shiptocity         => $self->delivery_city,
+          shiptocountry      => $self->delivery_country,
+          trans_id           => $customer->id,
+          module             => "CT",
+        );
+        $deliveryaddress->save;
+        $shipto_id = $deliveryaddress->{shipto_id};
+      }
+    }
+
+    my $shop = SL::DB::Manager::Shop->find_by(id => $self->shop_id);
+    my $order = SL::DB::Order->new(
+      amount                  => $self->amount,
+      cusordnumber            => $self->shop_ordernumber,
+      customer_id             => $customer->id,
+      shipto_id               => $shipto_id,
+      orderitems              => [ @items ],
+      employee_id             => $employee->id,
+      intnotes                => $customer->notes,
+      salesman_id             => $employee->id,
+      taxincluded             => $self->tax_included,
+      payment_id              => $customer->payment_id,
+      taxzone_id              => $customer->taxzone_id,
+      currency_id             => $customer->currency_id,
+      transaction_description => $shop->transaction_description,
+      transdate               => DateTime->today_local
+    );
+     return $order;
+   }else{
+     my %error_order = (error   => 1,
+                        errors  => [ @error_report ],
+                       );
+     return \%error_order;
+   }
+};
+
+sub check_for_existing_customers {
+  my ($self, %params) = @_;
+
+  my $name     = $self->billing_lastname ne '' ? $self->billing_firstname . " " . $self->billing_lastname : '';
+  my $lastname = $self->billing_lastname ne '' ? "%" . $self->billing_lastname . "%"                      : '';
+  my $company  = $self->billing_company  ne '' ? "%" . $self->billing_company  . "%"                      : '';
+  my $street   = $self->billing_street   ne '' ?  $self->billing_street                                   : '';
+
+  # Fuzzysearch for street to find e.g. "Dorfstrasse - Dorfstr. - Dorfstraße"
+  my $fs_query = <<SQL;
+SELECT *
+FROM customer
+WHERE (
+   (
+    ( name ILIKE ? OR name ILIKE ? )
+      AND
+    zipcode ILIKE ?
+   )
+ OR
+   ( street % ?  AND zipcode ILIKE ?)
+ OR
+   email ILIKE ?
+) AND obsolete = 'F'
+SQL
+
+  my @values = ($lastname, $company, $self->billing_zipcode, $street, $self->billing_zipcode, $self->billing_email);
+
+  my $customers = SL::DB::Manager::Customer->get_objects_from_sql(
+    sql  => $fs_query,
+    args => \@values,
+  );
+
+  return $customers;
+}
+
+sub get_customer{
+  my ($self, %params) = @_;
+  my $shop = SL::DB::Manager::Shop->find_by(id => $self->shop_id);
+  my $customer_proposals = $self->check_for_existing_customers;
+  my $name = $self->billing_firstname . " " . $self->billing_lastname;
+  my $customer = 0;
+  if(!scalar(@{$customer_proposals})){
+    my %address = ( 'name'                  => $name,
+                    'department_1'          => $self->billing_company,
+                    'department_2'          => $self->billing_department,
+                    'street'                => $self->billing_street,
+                    'zipcode'               => $self->billing_zipcode,
+                    'city'                  => $self->billing_city,
+                    'email'                 => $self->billing_email,
+                    'country'               => $self->billing_country,
+                    'greeting'              => $self->billing_greeting,
+                    'fax'                   => $self->billing_fax,
+                    'phone'                 => $self->billing_phone,
+                    'ustid'                 => $self->billing_vat,
+                    'taxincluded_checked'   => $shop->pricetype eq "brutto" ? 1 : 0,
+                    'taxincluded'           => $shop->pricetype eq "brutto" ? 1 : 0,
+                    'pricegroup_id'         => (split '\/',$shop->price_source)[0] eq "pricegroup" ?  (split '\/',$shop->price_source)[1] : undef,
+                    'taxzone_id'            => $shop->taxzone_id,
+                    'currency'              => $::instance_conf->get_currency_id,
+                    #'payment_id'            => 7345,# TODO hardcoded
+                  );
+    $customer = SL::DB::Customer->new(%address);
+
+    $customer->save;
+    my $snumbers = "customernumber_" . $customer->customernumber;
+    SL::DB::History->new(
+                      trans_id    => $customer->id,
+                      snumbers    => $snumbers,
+                      employee_id => SL::DB::Manager::Employee->current->id,
+                      addition    => 'SAVED',
+                      what_done   => 'Shopimport',
+                    )->save();
+
+  }elsif(scalar(@{$customer_proposals}) == 1){
+    # check if the proposal is the right customer, could be different names under the same address. Depends on how first- and familyname is handled. Here is for customername = companyname or customername = "firstname familyname"
+    $customer = SL::DB::Manager::Customer->find_by( id       => $customer_proposals->[0]->id,
+                                                    name     => $name,
+                                                    email    => $self->billing_email,
+                                                    street   => $self->billing_street,
+                                                    zipcode  => $self->billing_zipcode,
+                                                    city     => $self->billing_city,
+                                                    obsolete => 'F',
+                                                  );
+  }
+
+  return $customer;
+}
+
+sub compare_to {
+  my ($self, $other) = @_;
+
+  return  1 if  $self->transfer_date && !$other->transfer_date;
+  return -1 if !$self->transfer_date &&  $other->transfer_date;
+
+  my $result = 0;
+  $result    = $self->transfer_date <=> $other->transfer_date if $self->transfer_date;
+  return $result || ($self->id <=> $other->id);
+}
+
+1;
+
+__END__
+
+=pod
+
+=encoding utf-8
+
+=head1 NAME
+
+SL::DB::ShopOrder - Model for the 'shop_orders' table
+
+=head1 SYNOPSIS
+
+This is a standard Rose::DB::Object based model and can be used as one.
+
+=head1 METHODS
+
+=over 4
+
+=item C<convert_to_sales_order>
+
+=item C<check_for_existing_customers>
+
+Inexact search for possible matches with existing customers in the database.
+
+Returns all found customers as an arrayref of SL::DB::Customer objects.
+
+=item C<get_customer>
+
+returns only one customer from the check_for_existing_customers if the return from it is 0 or 1 customer.
+
+When it is 0 get customer creates a new customer object of the shop order billing data and returns it
+
+=item C<compare_to>
+
+=back
+
+=head1 TODO
+
+some variables like payments could be better implemented. Transaction description is hardcoded
+
+=head1 AUTHORS
+
+Werner Hahn E<lt>wh@futureworldsearch.netE<gt>
+
+G. Richardson E<lt>grichardson@kivitendo-premium.deE<gt>
+
+=cut
diff --git a/SL/DB/ShopOrderItem.pm b/SL/DB/ShopOrderItem.pm
new file mode 100644
index 000000000..34333aadb
--- /dev/null
+++ b/SL/DB/ShopOrderItem.pm
@@ -0,0 +1,13 @@
+# This file has been auto-generated only because it didn't exist.
+# Feel free to modify it at will; it will not be overwritten automatically.
+
+package SL::DB::ShopOrderItem;
+
+use strict;
+
+use SL::DB::MetaSetup::ShopOrderItem;
+use SL::DB::Manager::ShopOrderItem;
+
+__PACKAGE__->meta->initialize;
+
+1;
diff --git a/SL/DB/ShopPart.pm b/SL/DB/ShopPart.pm
new file mode 100644
index 000000000..1df77fd89
--- /dev/null
+++ b/SL/DB/ShopPart.pm
@@ -0,0 +1,111 @@
+# This file has been auto-generated only because it didn't exist.
+# Feel free to modify it at will; it will not be overwritten automatically.
+
+package SL::DB::ShopPart;
+
+use strict;
+
+use SL::DBUtils;
+use SL::DB::MetaSetup::ShopPart;
+use SL::DB::Manager::ShopPart;
+use SL::DB::Helper::AttrHTML;
+#use SL::DB::Helper::ActsAsList;
+
+__PACKAGE__->meta->initialize;
+__PACKAGE__->attr_html('shop_description');
+
+sub get_tax_and_price {
+  my ( $self ) = @_;
+
+  require SL::DB::Part;
+  my $tax_n_price;
+  my ( $price_src_str, $price_src_id ) = split(/\//,$self->active_price_source);
+  my $price;
+  my $part;
+  if ($price_src_str eq "master_data") {
+    $part = SL::DB::Manager::Part->find_by( id => $self->part_id );
+    $price = $part->$price_src_id;
+  }else{
+    $part = SL::DB::Manager::Part->find_by( id => $self->part_id );
+    $price =  $part->prices->[0]->price;
+  }
+
+  my $taxrate;
+  my $dbh  = $::form->get_standard_dbh();
+  my $b_id = $part->buchungsgruppen_id;
+  my $t_id = $self->shop->taxzone_id;
+
+  my $sql_str = "SELECT a.rate AS taxrate from tax a
+  WHERE a.taxkey = (SELECT b.taxkey_id
+  FROM chart b LEFT JOIN taxzone_charts c ON b.id = c.income_accno_id
+  WHERE c.taxzone_id = $t_id
+  AND c.buchungsgruppen_id = $b_id)";
+
+  my $rate = selectall_hashref_query($::form, $dbh, $sql_str);
+  $taxrate = @$rate[0]->{taxrate}*100;
+
+  $tax_n_price->{price} = $price;
+  $tax_n_price->{tax}   = $taxrate;
+  return $tax_n_price;
+}
+
+sub get_images {
+  my ( $self ) = @_;
+
+  require SL::DB::ShopImage;
+  my $images = SL::DB::Manager::ShopImage->get_all( where => [ 'files.object_id' => $self->{part_id}, ], with_objects => 'file', sort_by => 'position' );
+  my @upload_img = ();
+  foreach my $img (@{ $images }) {
+    my $file               = SL::File->get(id => $img->file->id );
+    my ($path, $extension) = (split /\./, $file->file_name);
+    my $content            = File::Slurp::read_file($file->get_file);
+    my $temp ={ ( link        => 'data:' . $file->mime_type . ';base64,' . MIME::Base64::encode($content, ""), #$content, # MIME::Base64::encode($content),
+                  description => $img->file->title,
+                  position    => $img->position,
+                  extension   => $extension,
+                  path        => $path,
+                      )}    ;
+    push( @upload_img, $temp);
+  }
+  return @upload_img;
+}
+
+1;
+
+__END__
+
+=pod
+
+=encoding utf-8
+
+=head1 NAME
+
+SL::DB::ShopPart - Model for the 'shop_parts' table
+
+=head1 SYNOPSIS
+
+This is a standard Rose::DB::Object based model and can be used as one.
+
+=head1 METHODS
+
+=over 4
+
+=item C<get_tax_and_price>
+
+Returns the price and the taxrate for an shop_article
+
+=item C<get_images>
+
+Returns the images for the shop_article
+
+=back
+
+=head1 TODO
+
+Prices, pricesources, pricerules could be implemented
+
+=head1 AUTHORS
+
+Werner Hahn E<lt>wh@futureworldsearch.netE<gt>
+
+=cut
diff --git a/sql/Pg-upgrade2/shop_orders.sql b/sql/Pg-upgrade2/shop_orders.sql
new file mode 100644
index 000000000..70164fa64
--- /dev/null
+++ b/sql/Pg-upgrade2/shop_orders.sql
@@ -0,0 +1,103 @@
+-- @tag: shop_orders
+-- @description: Erstellen der Tabellen shop_orders und shop_order_items
+-- @depends: release_3_5_0 shops
+
+CREATE TABLE shop_orders (
+  id SERIAL PRIMARY KEY,
+  shop_trans_id integer NOT NULL, --id vom shop
+  shop_ordernumber TEXT, --Bestellnummer vom Shop
+  shop_data text,        -- store whole order as json
+  shop_customer_comment text, --Bestellkommentar des Kunden
+  amount numeric(15,5),  --Bruttogesamtbetrag
+  netamount numeric(15,5),--Nettogesamtbetrag
+  order_date timestamp, --Bestelldatum und Zeit
+  shipping_costs numeric(15,5),
+  shipping_costs_net numeric(15,5),
+  shipping_costs_id integer,
+  tax_included boolean,
+  payment_id integer, --Bezahlart
+  payment_description TEXT,  --Bezahlart
+  shop_id integer,               --welcher shop bei mehreren
+  host TEXT,             --Hostname vom Shop
+  remote_ip text,        --IP Besteller
+  transferred boolean DEFAULT FALSE,    -- übernommen
+  transfer_date date, -- Zeit wann übernommen
+  kivi_customer_id integer,  -- Kundenid von Tbl customer wenn übernommen
+  oe_transid integer,  -- id to
+-- Bestell-, Rechnungs- und Lieferadresse. !!Manche Shops bieten sowas!!
+-- In der Regel ist aber die Rechnungsadresse die Kundenadresse
+  -- Bestelldaten des Kunden
+  shop_customer_id integer,
+  shop_customer_number TEXT,
+  customer_lastname TEXT,
+  customer_firstname TEXT,
+  customer_company TEXT,
+  customer_street TEXT,
+  customer_zipcode TEXT,
+  customer_city TEXT,
+  customer_country TEXT,
+  customer_greeting TEXT,
+  customer_department TEXT,
+  customer_vat TEXT,
+  customer_phone TEXT,
+  customer_fax TEXT,
+  customer_email TEXT,
+  customer_newsletter boolean,
+  -- Rechnungsadresse
+  shop_c_billing_id integer,
+  shop_c_billing_number TEXT,
+  billing_lastname TEXT,
+  billing_firstname TEXT,
+  billing_company TEXT,
+  billing_street TEXT,
+  billing_zipcode TEXT,
+  billing_city TEXT,
+  billing_country TEXT,
+  billing_greeting TEXT,
+  billing_department TEXT,
+  billing_vat TEXT,
+  billing_phone TEXT,
+  billing_fax TEXT,
+  billing_email TEXT,
+
+  -- SEPA
+  sepa_account_holder TEXT,
+  sepa_iban TEXT,
+  sepa_bic TEXT,
+
+  -- Lieferadresse
+  shop_c_delivery_id integer,
+  shop_c_delivery_number TEXT,
+  delivery_lastname TEXT,
+  delivery_firstname TEXT,
+  delivery_company TEXT,
+  delivery_street TEXT,
+  delivery_zipcode TEXT,
+  delivery_city TEXT,
+  delivery_country TEXT,
+  delivery_greeting TEXT,
+  delivery_department TEXT,
+  delivery_vat TEXT,
+  delivery_phone TEXT,
+  delivery_fax TEXT,
+  delivery_email TEXT,
+
+  obsolete boolean DEFAULT FALSE NOT NULL,
+  positions integer,
+
+  itime timestamp DEFAULT now(),
+  mtime timestamp
+);
+
+CREATE TABLE shop_order_items (
+  id            SERIAL PRIMARY KEY,
+  shop_trans_id INTEGER NOT NULL, --id vom shop in shop-db? -> could use $order_item->shop_order->shop_trans_id instead
+  shop_order_id INTEGER REFERENCES shop_orders (id) ON DELETE CASCADE,
+  description   TEXT,  -- Artikelbezeichnung
+  partnumber    TEXT,
+  shop_id       INTEGER,
+  position      INTEGER,
+  tax_rate      NUMERIC(15,2),
+  quantity      NUMERIC(25,5),   -- qty in invoice and orderitems is real, doi is numeric(25,5)
+  price         NUMERIC(15,5)
+);
diff --git a/sql/Pg-upgrade2/shop_orders_add_active_pricesource.sql b/sql/Pg-upgrade2/shop_orders_add_active_pricesource.sql
new file mode 100644
index 000000000..8b258a755
--- /dev/null
+++ b/sql/Pg-upgrade2/shop_orders_add_active_pricesource.sql
@@ -0,0 +1,5 @@
+-- @tag: shop_orders_add_active_price_source
+-- @description: Erstellen der Tabellen shop_orders und shop_order_items
+-- @depends: release_3_5_0 shop_orders
+
+ALTER TABLE shop_order_items ADD COLUMN active_price_source TEXT;
diff --git a/sql/Pg-upgrade2/shop_orders_update_1.sql b/sql/Pg-upgrade2/shop_orders_update_1.sql
new file mode 100644
index 000000000..b5f205dbd
--- /dev/null
+++ b/sql/Pg-upgrade2/shop_orders_update_1.sql
@@ -0,0 +1,21 @@
+-- @tag: shop_orders_update_1
+-- @description: Ändern der Tabellen shop_orders und shop_order_items. Trigger für oe
+-- @depends: release_3_5_0 shop_orders shop_orders_add_active_price_source
+-- @ignore: 0
+
+ALTER TABLE shop_orders ADD FOREIGN KEY (shop_id) REFERENCES shops(id);
+ALTER TABLE shop_orders ADD FOREIGN KEY (kivi_customer_id) REFERENCES customer(id);
+ALTER TABLE shop_orders DROP COLUMN shop_data;
+ALTER TABLE shop_order_items DROP COLUMN shop_id;
+
+CREATE OR REPLACE FUNCTION update_shop_orders_on_delete_oe() RETURNS TRIGGER AS $$
+  BEGIN
+    UPDATE shop_orders SET oe_trans_id = NULL WHERE oe_trans_id = OLD.id;
+
+    RETURN OLD.id;
+  END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER after_delete_oe_trigger
+AFTER DELETE ON oe FOR EACH ROW EXECUTE
+PROCEDURE update_shop_orders_on_delete_oe();
diff --git a/sql/Pg-upgrade2/shop_orders_update_2.sql b/sql/Pg-upgrade2/shop_orders_update_2.sql
new file mode 100644
index 000000000..0d60b4c5d
--- /dev/null
+++ b/sql/Pg-upgrade2/shop_orders_update_2.sql
@@ -0,0 +1,6 @@
+-- @tag: shop_orders_update_2
+-- @description: Ändern der Tabellen shop_orders für Trigger spalte war falsch benannt
+-- @depends: shop_orders_update_1
+-- @ignore: 0
+
+ALTER TABLE shop_orders RENAME COLUMN oe_transid TO oe_trans_id;
diff --git a/sql/Pg-upgrade2/shop_orders_update_3.sql b/sql/Pg-upgrade2/shop_orders_update_3.sql
new file mode 100644
index 000000000..3f28406fd
--- /dev/null
+++ b/sql/Pg-upgrade2/shop_orders_update_3.sql
@@ -0,0 +1,8 @@
+-- @tag: shop_orders_update_3
+-- @description: Ändern der Tabellen shop_orders und shop_order_items. Trigger für oe
+-- @depends: shop_orders_update_1 shop_orders_update_2
+-- @ignore: 0
+
+ALTER TABLE shop_orders DROP COLUMN oe_trans_id;
+
+DROP FUNCTION update_shop_orders_on_delete_oe() CASCADE;
diff --git a/sql/Pg-upgrade2/shop_parts.sql b/sql/Pg-upgrade2/shop_parts.sql
new file mode 100644
index 000000000..8c627cd65
--- /dev/null
+++ b/sql/Pg-upgrade2/shop_parts.sql
@@ -0,0 +1,28 @@
+-- @tag: shop_parts
+-- @description: Add tables for part information for shop
+-- @charset: UTF-8
+-- @depends: release_3_5_0 shops
+-- @ignore: 0
+
+CREATE TABLE shop_parts (
+  id               SERIAL PRIMARY KEY,
+  shop_id          INTEGER NOT NULL REFERENCES shops(id),
+  part_id          INTEGER NOT NULL REFERENCES parts(id),
+  shop_description TEXT,
+  itime            TIMESTAMP DEFAULT now(),
+  mtime            TIMESTAMP,
+  last_update      TIMESTAMP,
+  show_date        DATE,   -- the starting date for displaying part in shop
+  sortorder        INTEGER,
+  front_page       BOOLEAN NOT NULL DEFAULT false,
+  active           BOOLEAN NOT NULL DEFAULT false,  -- rather than obsolete
+  shop_category TEXT[][],
+  active_price_source TEXT,
+  metatag_keywords TEXT,
+  metatag_description TEXT,
+  metatag_title TEXT,
+  UNIQUE (part_id, shop_id)  -- make sure a shop_part appears only once per shop and part
+);
+
+CREATE TRIGGER mtime_shop_parts BEFORE UPDATE ON shop_parts
+    FOR EACH ROW EXECUTE PROCEDURE set_mtime();
diff --git a/sql/Pg-upgrade2/shops.sql b/sql/Pg-upgrade2/shops.sql
new file mode 100644
index 000000000..840b095e0
--- /dev/null
+++ b/sql/Pg-upgrade2/shops.sql
@@ -0,0 +1,21 @@
+-- @tag: shops
+-- @description: Tabelle für Shops
+-- @depends: release_3_5_0 customer_klass_rename_to_pricegroup_id_and_foreign_key
+-- @ignore: 0
+
+CREATE TABLE shops (
+  id SERIAL PRIMARY KEY,
+  description text,
+  obsolete BOOLEAN NOT NULL DEFAULT false,
+  sortkey INTEGER,
+  connector text,     -- hardcoded options, e.g. xtcommerce, shopware
+  pricetype text,     -- netto/brutto
+  price_source text,  -- sellprice/listprice/lastcost or pricegroup id
+  taxzone_id INTEGER,
+  last_order_number INTEGER,
+  orders_to_fetch INTEGER,
+  url text,
+  port INTEGER,
+  login text,  -- "user" is reserved
+  password text
+);
diff --git a/sql/Pg-upgrade2/shops_1.sql b/sql/Pg-upgrade2/shops_1.sql
new file mode 100644
index 000000000..0b2c5a2f3
--- /dev/null
+++ b/sql/Pg-upgrade2/shops_1.sql
@@ -0,0 +1,9 @@
+-- @tag: shop_1
+-- @description: Add tables for part information for shop
+-- @charset: UTF-8
+-- @depends: shops
+-- @ignore: 0
+
+ALTER TABLE shops ADD COLUMN protocol TEXT NOT NULL DEFAULT 'http';
+ALTER TABLE shops ADD COLUMN path TEXT NOT NULL DEFAULT '/';
+ALTER TABLE shops RENAME COLUMN url TO server;
diff --git a/sql/Pg-upgrade2/shops_2.sql b/sql/Pg-upgrade2/shops_2.sql
new file mode 100644
index 000000000..b6828436b
--- /dev/null
+++ b/sql/Pg-upgrade2/shops_2.sql
@@ -0,0 +1,7 @@
+-- @tag: shop_2
+-- @description: Add tables for part information for shop
+-- @charset: UTF-8
+-- @depends: shops
+-- @ignore: 0
+
+ALTER TABLE shops ADD COLUMN realm TEXT;
diff --git a/sql/Pg-upgrade2/shops_3.sql b/sql/Pg-upgrade2/shops_3.sql
new file mode 100644
index 000000000..1be783055
--- /dev/null
+++ b/sql/Pg-upgrade2/shops_3.sql
@@ -0,0 +1,14 @@
+-- @tag: shop_3
+-- @description: Add columns itime and mtime and transaction_description for table shops
+-- @charset: UTF-8
+-- @depends: shops
+-- @ignore: 0
+
+ALTER TABLE shops ADD COLUMN transaction_description TEXT;
+ALTER TABLE shops ADD COLUMN itime timestamp DEFAULT now();
+ALTER TABLE shops ADD COLUMN mtime timestamp DEFAULT now();
+
+CREATE TRIGGER mtime_shops
+    BEFORE UPDATE ON shops
+    FOR EACH ROW
+    EXECUTE PROCEDURE set_mtime();
-- 
2.20.1