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