From b2bedb6b5eff41a17ddb194df1c75213b841f3bb Mon Sep 17 00:00:00 2001 From: Werner Hahn 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 + +Returns an error if the shop description is missing + +=item C + +Returns an array of hashes for dropdowns in filters + +=back + +=head1 AUTHORS + +Werner Hahn Ewh@futureworldsearch.netE + +G. Richardson Egrichardson@kivitendo-premium.deE + +=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 = <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 + +=item C + +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 + +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 + +=back + +=head1 TODO + +some variables like payments could be better implemented. Transaction description is hardcoded + +=head1 AUTHORS + +Werner Hahn Ewh@futureworldsearch.netE + +G. Richardson Egrichardson@kivitendo-premium.deE + +=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 + +Returns the price and the taxrate for an shop_article + +=item C + +Returns the images for the shop_article + +=back + +=head1 TODO + +Prices, pricesources, pricerules could be implemented + +=head1 AUTHORS + +Werner Hahn Ewh@futureworldsearch.netE + +=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