projekt kivitendo

use SL::DB::Shop;

use SL::DB::ShopOrder;

use SL::DB::ShopOrderItem;

use SL::DB::ShopPart;

shops => 'shop',

shop_orders => 'shop_order',

shop_order_items => 'shop_order_item',

shop_parts => 'shop_part',

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

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

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

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

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

;

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

;

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

;

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

;

shop_parts => {

type => 'one to many',

class => 'SL::DB::ShopPart',

column_map => { id => 'part_id' },

manager_args => { with_objects => [ 'shop' ] },

},

# 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

# 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

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

# 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

-- @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)

);

-- @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;

-- @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();

-- @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;

-- @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;

-- @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();

-- @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

);

-- @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;

-- @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;

-- @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();