From 82515b2d93dc5632f24d6e0b6f8f05f3fd19fbb0 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Sven=20Sch=C3=B6ling?= Date: Mon, 30 Aug 2010 12:19:28 +0200 Subject: [PATCH] RDBO Support. MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Script, Models und Manager übernommen und angeglichen. Ein SQL Schema Update, dass die meisten vorausgesetzten Normalisierungen enthält mit eingefügt. --- SL/DB.pm | 54 +++++++ SL/DB/Assembly.pm | 23 +++ SL/DB/Bin.pm | 13 ++ SL/DB/Business.pm | 15 ++ SL/DB/Chart.pm | 9 ++ SL/DB/Customer.pm | 27 ++++ SL/DB/DeliveryOrder.pm | 40 ++++++ SL/DB/DeliveryOrderItem.pm | 20 +++ SL/DB/DeliveryOrderItemsStock.pm | 17 +++ SL/DB/GLTransaction.pm | 14 ++ SL/DB/Helpers/ALL.pm | 55 +++++++ SL/DB/Helpers/AttrDate.pm | 42 ++++++ SL/DB/Helpers/AttrNumber.pm | 37 +++++ SL/DB/Helpers/AttrPercent.pm | 37 +++++ SL/DB/Helpers/ConventionManager.pm | 29 ++++ SL/DB/Helpers/Manager.pm | 27 ++++ SL/DB/Helpers/Mappings.pm | 82 +++++++++++ SL/DB/Helpers/Metadata.pm | 18 +++ SL/DB/Invoice.pm | 62 ++++++++ SL/DB/InvoiceItem.pm | 27 ++++ SL/DB/Manager/DeliveryOrder.pm | 22 +++ SL/DB/Manager/Invoice.pm | 24 ++++ SL/DB/Manager/Order.pm | 24 ++++ SL/DB/Manager/Part.pm | 63 ++++++++ SL/DB/Manager/PurchaseInvoice.pm | 23 +++ SL/DB/Manager/TransferType.pm | 27 ++++ SL/DB/MetaSetup/Assembly.pm | 30 ++++ SL/DB/MetaSetup/Bin.pm | 33 +++++ SL/DB/MetaSetup/Business.pm | 28 ++++ SL/DB/MetaSetup/Chart.pm | 40 ++++++ SL/DB/MetaSetup/Customer.pm | 65 +++++++++ SL/DB/MetaSetup/DeliveryOrder.pm | 69 +++++++++ SL/DB/MetaSetup/DeliveryOrderItem.pm | 65 +++++++++ SL/DB/MetaSetup/DeliveryOrderItemsStock.pm | 48 +++++++ SL/DB/MetaSetup/GLTransaction.pm | 44 ++++++ SL/DB/MetaSetup/Invoice.pm | 84 +++++++++++ SL/DB/MetaSetup/InvoiceItem.pm | 57 ++++++++ SL/DB/MetaSetup/Order.pm | 66 +++++++++ SL/DB/MetaSetup/OrderItem.pm | 55 +++++++ SL/DB/MetaSetup/Part.pm | 59 ++++++++ SL/DB/MetaSetup/PriceFactor.pm | 23 +++ SL/DB/MetaSetup/Printer.pm | 23 +++ SL/DB/MetaSetup/Project.pm | 29 ++++ SL/DB/MetaSetup/PurchaseInvoice.pm | 72 ++++++++++ SL/DB/MetaSetup/Shipto.pm | 37 +++++ SL/DB/MetaSetup/TransferType.pm | 27 ++++ SL/DB/MetaSetup/Unit.pm | 31 ++++ SL/DB/MetaSetup/Vendor.pm | 65 +++++++++ SL/DB/MetaSetup/Warehouse.pm | 27 ++++ SL/DB/Object.pm | 154 ++++++++++++++++++++ SL/DB/Order.pm | 106 ++++++++++++++ SL/DB/OrderItem.pm | 25 ++++ SL/DB/Part.pm | 158 +++++++++++++++++++++ SL/DB/PriceFactor.pm | 31 ++++ SL/DB/Printer.pm | 9 ++ SL/DB/Project.pm | 31 ++++ SL/DB/PurchaseInvoice.pm | 21 +++ SL/DB/Shipto.pm | 23 +++ SL/DB/TransferType.pm | 17 +++ SL/DB/Unit.pm | 41 ++++++ SL/DB/VC.pm | 53 +++++++ SL/DB/Vendor.pm | 27 ++++ SL/DB/Warehouse.pm | 28 ++++ scripts/rose_auto_create_model.pl | 154 ++++++++++++++++++++ sql/Pg-upgrade2/schema_nomalization_1.sql | 38 +++++ 65 files changed, 2824 insertions(+) create mode 100644 SL/DB.pm create mode 100644 SL/DB/Assembly.pm create mode 100644 SL/DB/Bin.pm create mode 100644 SL/DB/Business.pm create mode 100644 SL/DB/Chart.pm create mode 100644 SL/DB/Customer.pm create mode 100644 SL/DB/DeliveryOrder.pm create mode 100644 SL/DB/DeliveryOrderItem.pm create mode 100644 SL/DB/DeliveryOrderItemsStock.pm create mode 100644 SL/DB/GLTransaction.pm create mode 100644 SL/DB/Helpers/ALL.pm create mode 100644 SL/DB/Helpers/AttrDate.pm create mode 100644 SL/DB/Helpers/AttrNumber.pm create mode 100644 SL/DB/Helpers/AttrPercent.pm create mode 100644 SL/DB/Helpers/ConventionManager.pm create mode 100644 SL/DB/Helpers/Manager.pm create mode 100644 SL/DB/Helpers/Mappings.pm create mode 100644 SL/DB/Helpers/Metadata.pm create mode 100644 SL/DB/Invoice.pm create mode 100644 SL/DB/InvoiceItem.pm create mode 100644 SL/DB/Manager/DeliveryOrder.pm create mode 100644 SL/DB/Manager/Invoice.pm create mode 100644 SL/DB/Manager/Order.pm create mode 100644 SL/DB/Manager/Part.pm create mode 100644 SL/DB/Manager/PurchaseInvoice.pm create mode 100644 SL/DB/Manager/TransferType.pm create mode 100644 SL/DB/MetaSetup/Assembly.pm create mode 100644 SL/DB/MetaSetup/Bin.pm create mode 100644 SL/DB/MetaSetup/Business.pm create mode 100644 SL/DB/MetaSetup/Chart.pm create mode 100644 SL/DB/MetaSetup/Customer.pm create mode 100644 SL/DB/MetaSetup/DeliveryOrder.pm create mode 100644 SL/DB/MetaSetup/DeliveryOrderItem.pm create mode 100644 SL/DB/MetaSetup/DeliveryOrderItemsStock.pm create mode 100644 SL/DB/MetaSetup/GLTransaction.pm create mode 100644 SL/DB/MetaSetup/Invoice.pm create mode 100644 SL/DB/MetaSetup/InvoiceItem.pm create mode 100644 SL/DB/MetaSetup/Order.pm create mode 100644 SL/DB/MetaSetup/OrderItem.pm create mode 100644 SL/DB/MetaSetup/Part.pm create mode 100644 SL/DB/MetaSetup/PriceFactor.pm create mode 100644 SL/DB/MetaSetup/Printer.pm create mode 100644 SL/DB/MetaSetup/Project.pm create mode 100644 SL/DB/MetaSetup/PurchaseInvoice.pm create mode 100644 SL/DB/MetaSetup/Shipto.pm create mode 100644 SL/DB/MetaSetup/TransferType.pm create mode 100644 SL/DB/MetaSetup/Unit.pm create mode 100644 SL/DB/MetaSetup/Vendor.pm create mode 100644 SL/DB/MetaSetup/Warehouse.pm create mode 100644 SL/DB/Object.pm create mode 100644 SL/DB/Order.pm create mode 100644 SL/DB/OrderItem.pm create mode 100644 SL/DB/Part.pm create mode 100644 SL/DB/PriceFactor.pm create mode 100644 SL/DB/Printer.pm create mode 100644 SL/DB/Project.pm create mode 100644 SL/DB/PurchaseInvoice.pm create mode 100644 SL/DB/Shipto.pm create mode 100644 SL/DB/TransferType.pm create mode 100644 SL/DB/Unit.pm create mode 100644 SL/DB/VC.pm create mode 100644 SL/DB/Vendor.pm create mode 100644 SL/DB/Warehouse.pm create mode 100755 scripts/rose_auto_create_model.pl create mode 100644 sql/Pg-upgrade2/schema_nomalization_1.sql diff --git a/SL/DB.pm b/SL/DB.pm new file mode 100644 index 000000000..18f8696b9 --- /dev/null +++ b/SL/DB.pm @@ -0,0 +1,54 @@ +package SL::DB; + +use strict; + +use Carp; +use Data::Dumper; +use English qw(-no_match_vars); +use Rose::DB; + +use base qw(Rose::DB); + +__PACKAGE__->use_private_registry; + +my (%_db_registered, %_initial_sql_executed); + +sub create { + my $domain = shift || SL::DB->default_domain; + my $type = shift || SL::DB->default_type; + + if ($type eq 'LXOFFICE') { + $domain = 'LXEMPTY' unless %::myconfig && $::myconfig{dbname}; + $type = join $SUBSCRIPT_SEPARATOR, map { $::myconfig{$_} } qw(dbdriver dbname dbhost dbport dbuser dbpasswd) if %::myconfig; + } + + _register_db($domain, $type); + + my $db = __PACKAGE__->new_or_cached(domain => $domain, type => $type); + + return $db; +} + +sub _register_db { + my $domain = shift; + my $type = shift; + + my $idx = "${domain}::${type}"; + return if $_db_registered{$idx}; + + $_db_registered{$idx} = 1; + + __PACKAGE__->register_db(domain => $domain, + type => $type, + driver => $::myconfig{dbdriver} || 'Pg', + database => $::myconfig{dbname}, + host => $::myconfig{dbhost}, + port => $::myconfig{dbport} || 5432, + username => $::myconfig{dbuser}, + password => $::myconfig{dbpasswd}, + connect_options => { pg_enable_utf8 => $::locale && $::locale->is_utf8, + }, + ); +} + +1; diff --git a/SL/DB/Assembly.pm b/SL/DB/Assembly.pm new file mode 100644 index 000000000..2bc9c16b8 --- /dev/null +++ b/SL/DB/Assembly.pm @@ -0,0 +1,23 @@ +# 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::Assembly; + +use strict; + +use SL::DB::MetaSetup::Assembly; + +__PACKAGE__->meta->add_relationships( + part => { + type => 'one to one', + class => 'SL::DB::Part', + column_map => { parts_id => 'id' }, + }, +); + +# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all. +__PACKAGE__->meta->make_manager_class; + +__PACKAGE__->meta->initialize; + +1; diff --git a/SL/DB/Bin.pm b/SL/DB/Bin.pm new file mode 100644 index 000000000..d9d29b4ff --- /dev/null +++ b/SL/DB/Bin.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::Bin; + +use strict; + +use SL::DB::MetaSetup::Bin; + +# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all. +__PACKAGE__->meta->make_manager_class; + +1; diff --git a/SL/DB/Business.pm b/SL/DB/Business.pm new file mode 100644 index 000000000..be9120e3c --- /dev/null +++ b/SL/DB/Business.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::Business; + +use strict; + +use SL::DB::MetaSetup::Business; + +__PACKAGE__->attr_percent('discount', places => -2); + +# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all. +__PACKAGE__->meta->make_manager_class; + +1; diff --git a/SL/DB/Chart.pm b/SL/DB/Chart.pm new file mode 100644 index 000000000..2d865b55d --- /dev/null +++ b/SL/DB/Chart.pm @@ -0,0 +1,9 @@ +package SL::DB::Chart; + +use strict; + +use SL::DB::MetaSetup::Chart; + +__PACKAGE__->meta->make_manager_class; + +1; diff --git a/SL/DB/Customer.pm b/SL/DB/Customer.pm new file mode 100644 index 000000000..fd5f66ef6 --- /dev/null +++ b/SL/DB/Customer.pm @@ -0,0 +1,27 @@ +package SL::DB::Customer; + +use strict; + +use SL::DB::MetaSetup::Customer; + +use SL::DB::VC; + +__PACKAGE__->meta->add_relationship( + shipto => { + type => 'one to many', + class => 'SL::DB::Shipto', + column_map => { id => 'trans_id' }, + manager_args => { sort_by => 'lower(shipto.shiptoname)' }, + query_args => [ 'shipto.module' => 'CT' ], + }, + business => { + type => 'one to one', + class => 'SL::DB::Business', + column_map => { business_id => 'id' }, + }, +); + +__PACKAGE__->meta->make_manager_class; +__PACKAGE__->meta->initialize; + +1; diff --git a/SL/DB/DeliveryOrder.pm b/SL/DB/DeliveryOrder.pm new file mode 100644 index 000000000..0dacf9a2a --- /dev/null +++ b/SL/DB/DeliveryOrder.pm @@ -0,0 +1,40 @@ +package SL::DB::DeliveryOrder; + +use strict; + +use SL::DB::MetaSetup::DeliveryOrder; +use SL::DB::Manager::DeliveryOrder; +use SL::DB::Order; + +use List::Util qw(first); + +for my $field (qw(transdate reqdate)) { + __PACKAGE__->attr_date($field); +} + +__PACKAGE__->meta->add_relationship(orderitems => { type => 'one to many', + class => 'SL::DB::DeliveryOrderItem', + column_map => { id => 'trans_id' }, + manager_args => { with_objects => [ 'part' ] } + }, + ); + +__PACKAGE__->meta->initialize; + +# methods + +sub sales_order { + my $self = shift; + my %params = @_; + + my $orders = SL::DB::Manager::Order->get_all( + query => [ + ordnumber => $self->ordnumber, + @{ $params{query} || [] }, + ], + ); + + return first { $_->is_type('sales_order') } @{ $orders }; +} + +1; diff --git a/SL/DB/DeliveryOrderItem.pm b/SL/DB/DeliveryOrderItem.pm new file mode 100644 index 000000000..8b836ee15 --- /dev/null +++ b/SL/DB/DeliveryOrderItem.pm @@ -0,0 +1,20 @@ +package SL::DB::DeliveryOrderItem; + +use strict; + +use SL::DB::MetaSetup::DeliveryOrderItem; + +for my $field (qw(qty sellprice discount base_qty lastcost price_factor marge_price_factor)) { + __PACKAGE__->attr_number($field, places => -2); +} + +__PACKAGE__->meta->make_manager_class; + +# methods + +sub part { + # canonial alias for parts. + return shift->parts; +} + +1; diff --git a/SL/DB/DeliveryOrderItemsStock.pm b/SL/DB/DeliveryOrderItemsStock.pm new file mode 100644 index 000000000..33e3f885f --- /dev/null +++ b/SL/DB/DeliveryOrderItemsStock.pm @@ -0,0 +1,17 @@ +# 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::DeliveryOrderItemsStock; + +use strict; + +use SL::DB::MetaSetup::DeliveryOrderItemsStock; + +for my $field (qw(qty)) { + __PACKAGE__->attr_number($field, places => -2); +} + +# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all. +__PACKAGE__->meta->make_manager_class; + +1; diff --git a/SL/DB/GLTransaction.pm b/SL/DB/GLTransaction.pm new file mode 100644 index 000000000..c4ca221be --- /dev/null +++ b/SL/DB/GLTransaction.pm @@ -0,0 +1,14 @@ +package SL::DB::GLTransaction; + +use strict; + +use SL::DB::MetaSetup::GLTransaction; + +for my $field (qw(transdate gldate)) { + __PACKAGE__->attr_date($field); +} + +# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all. +__PACKAGE__->meta->make_manager_class; + +1; diff --git a/SL/DB/Helpers/ALL.pm b/SL/DB/Helpers/ALL.pm new file mode 100644 index 000000000..d55613b04 --- /dev/null +++ b/SL/DB/Helpers/ALL.pm @@ -0,0 +1,55 @@ +package SL::DB::Helpers::ALL; + +use strict; + +use SL::DB::Assembly; +use SL::DB::Bin; +use SL::DB::Business; +use SL::DB::Chart; +use SL::DB::Customer; +use SL::DB::DeliveryOrder; +use SL::DB::DeliveryOrderItem; +use SL::DB::DeliveryOrderItemsStock; +use SL::DB::GLTransaction; +use SL::DB::Invoice; +use SL::DB::InvoiceItem; +use SL::DB::Order; +use SL::DB::OrderItem; +use SL::DB::Part; +use SL::DB::PriceFactor; +use SL::DB::Printer; +use SL::DB::Project; +use SL::DB::PurchaseInvoice; +use SL::DB::Shipto; +use SL::DB::TransferType; +use SL::DB::Unit; +use SL::DB::Vendor; +use SL::DB::Warehouse; + +1; + +__END__ + +=pod + +=head1 NAME + +SL::DB::Helpers::ALL: Dependency-only package for all SL::DB::* modules + +=head1 SYNOPSIS + + use SL::DB::Helpers::ALL; + +=head1 DESCRIPTION + +This module depends on all modules in SL/DB/*.pm for the convenience +of being able to write a simple \C and +having everything loaded. This is supposed to be used only in the +Lx-Office console. Normal modules should C only the modules they +actually need. + +=head1 AUTHOR + +Moritz Bunkus Em.bunkus@linet-services.deE + +=cut diff --git a/SL/DB/Helpers/AttrDate.pm b/SL/DB/Helpers/AttrDate.pm new file mode 100644 index 000000000..fa6338870 --- /dev/null +++ b/SL/DB/Helpers/AttrDate.pm @@ -0,0 +1,42 @@ +package SL::DB::Helpers::AttrDate; + +use strict; + +use Carp; +use English; + +sub define { + my $package = shift; + my $attribute = shift; + my %params = @_; + + $params{places} = 2 if !defined($params{places}); + + my $code = <parse_date(\\\%::myconfig, \@_); + \$self->${attribute}(DateTime->new(year => \$yy, month => \$mm, day => \$dd)); + } else { + \$self->${attribute}(undef); + } + } + + return \$self->${attribute} ? \$::locale->reformat_date({ dateformat => 'yy-mm-dd' }, \$self->${attribute}->ymd, \$::myconfig{dateformat}) : undef; +} + +1; +CODE + + eval $code; + croak "Defining '${attribute}_as_number' failed: $EVAL_ERROR" if $EVAL_ERROR; + + return 1; +} + +1; diff --git a/SL/DB/Helpers/AttrNumber.pm b/SL/DB/Helpers/AttrNumber.pm new file mode 100644 index 000000000..98acf192c --- /dev/null +++ b/SL/DB/Helpers/AttrNumber.pm @@ -0,0 +1,37 @@ +package SL::DB::Helpers::AttrNumber; + +use strict; + +use Carp; +use English; + +sub define { + my $package = shift; + my $attribute = shift; + my %params = @_; + + $params{places} = 2 if !defined($params{places}); + + my $code = <${attribute}(\$::form->parse_amount(\\\%::myconfig, \$_[0])); + } + + return \$::form->format_amount(\\\%::myconfig, \$self->${attribute}, $params{places}); +} + +1; +CODE + + eval $code; + croak "Defining '${attribute}_as_number' failed: $EVAL_ERROR" if $EVAL_ERROR; + + return 1; +} + +1; diff --git a/SL/DB/Helpers/AttrPercent.pm b/SL/DB/Helpers/AttrPercent.pm new file mode 100644 index 000000000..24f816404 --- /dev/null +++ b/SL/DB/Helpers/AttrPercent.pm @@ -0,0 +1,37 @@ +package SL::DB::Helpers::AttrPercent; + +use strict; + +use Carp; +use English; + +sub define { + my $package = shift; + my $attribute = shift; + my %params = @_; + + $params{places} = 2 if !defined($params{places}); + + my $code = <${attribute}(\$::form->parse_amount(\\\%::myconfig, \$_[0]) / 100); + } + + return \$::form->format_amount(\\\%::myconfig, 100 * \$self->${attribute}, $params{places}); +} + +1; +CODE + + eval $code; + croak "Defining '${attribute}_as_number' failed: $EVAL_ERROR" if $EVAL_ERROR; + + return 1; +} + +1; diff --git a/SL/DB/Helpers/ConventionManager.pm b/SL/DB/Helpers/ConventionManager.pm new file mode 100644 index 000000000..1d9cd8415 --- /dev/null +++ b/SL/DB/Helpers/ConventionManager.pm @@ -0,0 +1,29 @@ +package SL::DB::Helpers::ConventionManager; + +use strict; + +use Rose::DB::Object::ConventionManager; + +use base qw(Rose::DB::Object::ConventionManager); + +sub auto_manager_class_name { + my $self = shift; + my $object_class = shift || $self->meta->class; + + my @parts = split m/::/, $object_class; + my $last = pop @parts; + + return join('::', @parts, 'Manager', $last); +} + +# Base name used for 'make_manager_class', e.g. 'get_all', +# 'update_all' +sub auto_manager_base_name { + return 'all'; +} + +sub auto_manager_base_class { + return 'SL::DB::Helpers::Manager'; +} + +1; diff --git a/SL/DB/Helpers/Manager.pm b/SL/DB/Helpers/Manager.pm new file mode 100644 index 000000000..4399f5db0 --- /dev/null +++ b/SL/DB/Helpers/Manager.pm @@ -0,0 +1,27 @@ +package SL::DB::Helpers::Manager; + +use strict; + +use Rose::DB::Object::Manager; +use base qw(Rose::DB::Object::Manager); + +sub make_manager_methods { + my $class = shift; + my @params = scalar(@_) ? @_ : qw(all); + return $class->SUPER::make_manager_methods(@params); +} + +sub find_by { + my $class = shift; + + return if !@_; + return $class->get_all(query => [ @_ ], limit => 1)->[0]; +} + +sub get_first { + shift->get_all( + limit => 1, + )->[0]; +} + +1; diff --git a/SL/DB/Helpers/Mappings.pm b/SL/DB/Helpers/Mappings.pm new file mode 100644 index 000000000..0ba43d9f1 --- /dev/null +++ b/SL/DB/Helpers/Mappings.pm @@ -0,0 +1,82 @@ +package SL::DB::Helpers::Mappings; + +use strict; + +# threse will not be managed as Rose::DB models, because they are not normalized +# significant changes are needed to get them done. +my @lxoffice_blacklist_permanent = qw( + acc_trans audittrail customertax datev defaults department dpt_trans + exchangerate finanzamt follow_up_access gifi inventory leads licenseinvoice + makemodel partsgroup partstax prices record_links rmaitems status tax_zones + todo_user_config translation translation_payment_terms units_language + vendortax); + +# these are not managed _yet_, but will hopefully at some point. +# if you are confident that one of these works, remove it here. +my @lxoffice_blacklist_temp = qw( + bank_accounts buchungsgruppen contacts custom_variable_configs + custom_variables custom_variables_validity drafts dunning dunning_config + employee follow_up_links follow_ups generic_translations history_erp language + license notes payment_terms pricegroup rma schema_info sepa_export + sepa_export_items tax taxkeys +); + +my @lxoffice_blacklist = (@lxoffice_blacklist_permanent, @lxoffice_blacklist_temp); + +# map table names to their models. +# unlike rails we have no singular<->plural magic. +# remeber: tables should be named as the plural of the model name. +my %lxoffice_package_names = ( + ar => 'invoice', + ap => 'purchase_invoice', + delivery_orders => 'delivery_order', + delivery_order_items => 'delivery_order_item', + gl => 'GLTransaction', + invoice => 'invoice_item', + orderitems => 'order_item', + oe => 'order', + parts => 'part', + price_factors => 'price_factor', + printers => 'Printer', + units => 'unit', +); + +sub get_blacklist { + return LXOFFICE => \@lxoffice_blacklist; +} + +sub get_package_names { + return LXOFFICE => \%lxoffice_package_names; +} + +1; + +__END__ + +=head1 NAME + +SL::DB::Helpers::Mappings - Rose Table <-> Model mapping information + +=head1 SYNOPSIS + + use SL::DB::Helpers::Mappings qw(@blacklist %table2model); + +=head1 DESCRIPTION + +This modul stores table <-> model mappings used by the +L script. If you add a new table that has +custom mappings, add it here. + +=head1 BUGS + +nothing yet + +=head1 SEE ALSO + +L + +=head1 AUTHOR + +Sven Schöling + +=cut diff --git a/SL/DB/Helpers/Metadata.pm b/SL/DB/Helpers/Metadata.pm new file mode 100644 index 000000000..0c1f37419 --- /dev/null +++ b/SL/DB/Helpers/Metadata.pm @@ -0,0 +1,18 @@ +package SL::DB::Helpers::Metadata; + +use strict; + +use Rose::DB::Object::Metadata; +use SL::DB::Helpers::ConventionManager; + +use base qw(Rose::DB::Object::Metadata); + +sub convention_manager_class { + return 'SL::DB::Helpers::ConventionManager'; +} + +sub default_manager_base_class { + return 'SL::DB::Helpers::Manager'; +} + +1; diff --git a/SL/DB/Invoice.pm b/SL/DB/Invoice.pm new file mode 100644 index 000000000..cc488829f --- /dev/null +++ b/SL/DB/Invoice.pm @@ -0,0 +1,62 @@ +# 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::Invoice; + +use strict; + +use List::Util qw(first); + +use SL::DB::MetaSetup::Invoice; +use SL::DB::Manager::Invoice; + +__PACKAGE__->attr_number($_, places => -2) for qw(amount netamount paid marge_total marge_percent taxamount); +__PACKAGE__->attr_date($_) for qw(transdate gldate datepaid duedate deliverydate orddate quodate); +__PACKAGE__->attr_percent($_) for qw(abschlag_percentage); + +__PACKAGE__->meta->add_relationship( + invoiceitems => { + type => 'one to many', + class => 'SL::DB::InvoiceItem', + column_map => { id => 'trans_id' }, + manager_args => { + with_objects => [ 'part' ] + } + }, +); + +__PACKAGE__->meta->initialize; + +# methods + +# it is assumed, that ordnumbers are unique here. +sub first_order_by_ordnumber { + my $self = shift; + + my $orders = SL::DB::Manager::Order->get_all( + query => [ + ordnumber => $self->ordnumber, + + ], + ); + + return first { $_->is_type('sales_order') } @{ $orders }; +} + +sub abschlag_percentage { + my $self = shift; + my $order = $self->first_order_by_ordnumber or return; + my $order_amount = $order->netamount or return; + return $self->abschlag + ? $self->netamount / $order_amount + : undef; +} + +sub taxamount { + my $self = shift; + die 'not a setter method' if @_; + + return $self->amount - $self->netamount; +} + +1; diff --git a/SL/DB/InvoiceItem.pm b/SL/DB/InvoiceItem.pm new file mode 100644 index 000000000..20afb033f --- /dev/null +++ b/SL/DB/InvoiceItem.pm @@ -0,0 +1,27 @@ +package SL::DB::InvoiceItem; + +use strict; + +use SL::DB::MetaSetup::InvoiceItem; + +for my $field (qw( + qty allocated sellprice fxsellprice discount base_qty marge_total + marge_percent lastcost price_factor marge_price_factor +)) { + __PACKAGE__->attr_number($field, places => -2); +} + +__PACKAGE__->meta->add_relationship( + part => { + type => 'one to one', + class => 'SL::DB::Part', + column_map => { parts_id => 'id' }, + } +); + +# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all. +__PACKAGE__->meta->make_manager_class; + +__PACKAGE__->meta->initialize; + +1; diff --git a/SL/DB/Manager/DeliveryOrder.pm b/SL/DB/Manager/DeliveryOrder.pm new file mode 100644 index 000000000..f63987e88 --- /dev/null +++ b/SL/DB/Manager/DeliveryOrder.pm @@ -0,0 +1,22 @@ +package SL::DB::Manager::DeliveryOrder; + +use strict; + +use SL::DB::Helpers::Manager; +use base qw(SL::DB::Helpers::Manager); + +sub object_class { 'SL::DB::DeliveryOrder' } + +__PACKAGE__->make_manager_methods; + +sub type_filter { + my $class = shift; + my $type = lc(shift || ''); + + return ('!customer_id' => undef) if $type eq 'sales_delivery_order'; + return ('!vendor_id' => undef) if $type eq 'purchase_delivery_order'; + + die "Unknown type $type"; +} + +1; diff --git a/SL/DB/Manager/Invoice.pm b/SL/DB/Manager/Invoice.pm new file mode 100644 index 000000000..025f7f2d4 --- /dev/null +++ b/SL/DB/Manager/Invoice.pm @@ -0,0 +1,24 @@ +package SL::DB::Manager::Invoice; + +use strict; + +use base qw(SL::DB::Helpers::Manager); + +sub object_class { 'SL::DB::Invoice' } + +__PACKAGE__->make_manager_methods; + +sub type_filter { + my $class = shift; + my $type = lc(shift || ''); + + return (or => [ invoice => 0, invoice => undef ]) if $type eq 'ar_transaction'; + return (and => [ invoice => 1, amount => { ge => 0 }, or => [ storno => 0, storno => undef ] ]) if $type eq 'invoice'; + return (and => [ invoice => 1, amount => { lt => 0 }, or => [ storno => 0, storno => undef ] ]) if $type eq 'credit_note'; + return (and => [ invoice => 1, amount => { lt => 0 }, storno => 1 ]) if $type =~ m/(?:invoice_)?storno/; + return (and => [ invoice => 1, amount => { ge => 0 }, storno => 1 ]) if $type eq 'credit_note_storno'; + + die "Unknown type $type"; +} + +1; diff --git a/SL/DB/Manager/Order.pm b/SL/DB/Manager/Order.pm new file mode 100644 index 000000000..451a43aaf --- /dev/null +++ b/SL/DB/Manager/Order.pm @@ -0,0 +1,24 @@ +package SL::DB::Manager::Order; + +use strict; + +use SL::DB::Helpers::Manager; +use base qw(SL::DB::Helpers::Manager); + +sub object_class { 'SL::DB::Order' } + +__PACKAGE__->make_manager_methods; + +sub type_filter { + my $class = shift; + my $type = lc(shift || ''); + + return (and => [ '!customer_id' => undef, quotation => 1 ]) if $type eq 'sales_quotation'; + return (and => [ '!vendor_id' => undef, quotation => 1 ]) if $type eq 'request_quotation'; + return (and => [ '!customer_id' => undef, or => [ quotation => 0, quotation => undef ] ]) if $type eq 'sales_order'; + return (and => [ '!vendor_id' => undef, or => [ quotation => 0, quotation => undef ] ]) if $type eq 'purchase_order'; + + die "Unknown type $type"; +} + +1; diff --git a/SL/DB/Manager/Part.pm b/SL/DB/Manager/Part.pm new file mode 100644 index 000000000..1564b9b79 --- /dev/null +++ b/SL/DB/Manager/Part.pm @@ -0,0 +1,63 @@ +package SL::DB::Manager::Part; + +use strict; + +use SL::DB::Helpers::Manager; +use base qw(SL::DB::Helpers::Manager); + +use Carp; +use SL::DBUtils; + +sub object_class { 'SL::DB::Part' } + +__PACKAGE__->make_manager_methods; + +sub type_filter { + my $class = shift; + my $type = lc(shift || ''); + + if ($type =~ m/^part/) { + return (and => [ or => [ assembly => 0, assembly => undef ], + '!inventory_accno_id' => 0, + '!inventory_accno_id' => undef, + ]); + + } elsif ($type =~ m/^service/) { + return (and => [ or => [ assembly => 0, assembly => undef ], + or => [ inventory_accno_id => 0, inventory_accno_id => undef ], + ]); + + } elsif ($type =~ m/^assembl/) { + return (assembly => 1); + + } + + return (); +} + +sub get_ordered_qty { + my $class = shift; + my @part_ids = @_; + + return () unless @part_ids; + + my $placeholders = join ',', ('?') x @part_ids; + my $query = < 0) + GROUP BY oi.parts_id +SQL + + my %qty_by_id = map { $_->{parts_id} => $_->{qty} * 1 } @{ selectall_hashref_query($::form, $class->object_class->init_db->dbh, $query, @part_ids) }; + map { $qty_by_id{$_} ||= 0 } @part_ids; + + return %qty_by_id; +} + +1; diff --git a/SL/DB/Manager/PurchaseInvoice.pm b/SL/DB/Manager/PurchaseInvoice.pm new file mode 100644 index 000000000..c717a047f --- /dev/null +++ b/SL/DB/Manager/PurchaseInvoice.pm @@ -0,0 +1,23 @@ +package SL::DB::Manager::PurchaseInvoice; + +use strict; + +use SL::DB::Helpers::Manager; +use base qw(SL::DB::Helpers::Manager); + +sub object_class { 'SL::DB::PurchaseInvoice' } + +__PACKAGE__->make_manager_methods; + +sub type_filter { + my $class = shift; + my $type = lc(shift || ''); + + return (or => [ invoice => 0, invoice => undef ]) if $type eq 'ap_transaction'; + return (and => [ invoice => 1, or => [ storno => 0, storno => undef ] ]) if $type eq 'invoice'; + return (and => [ invoice => 1, storno => 1 ]) if $type =~ m/(?:invoice_)?storno/; + + die "Unknown type $type"; +} + +1; diff --git a/SL/DB/Manager/TransferType.pm b/SL/DB/Manager/TransferType.pm new file mode 100644 index 000000000..82bcc5fa1 --- /dev/null +++ b/SL/DB/Manager/TransferType.pm @@ -0,0 +1,27 @@ +package SL::DB::Manager::TransferType; + +use strict; + +use base qw(SL::DB::Helpers::Manager); + +use Carp; + +sub object_class { 'SL::DB::TransferType' } + +__PACKAGE__->make_manager_methods; + +# class functions + +sub get_all_in { + return shift()->get_all( query => [ direction => 'in', ] ); +} + +sub get_all_out { + return shift()->get_all( query => [ direction => 'out', ] ); +} + +sub get_all_transfer { + return shift()->get_all( query => [ direction => 'transfer', ] ); +} + +1; diff --git a/SL/DB/MetaSetup/Assembly.pm b/SL/DB/MetaSetup/Assembly.pm new file mode 100644 index 000000000..408eea7af --- /dev/null +++ b/SL/DB/MetaSetup/Assembly.pm @@ -0,0 +1,30 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::Assembly; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'assembly', + + columns => [ + id => { type => 'integer' }, + parts_id => { type => 'integer' }, + qty => { type => 'float', precision => 4 }, + bom => { type => 'boolean' }, + itime => { type => 'timestamp', default => 'now()' }, + mtime => { type => 'timestamp' }, + variable => { type => 'boolean', default => 'false', not_null => 1, remarks => 'true if this part of the assembly is variable, and can be modified' }, + item_id => { type => 'integer', remarks => 'used to group parts in an assembly for exclusive options' }, + assembly_id => { type => 'serial', not_null => 1 }, + ], + + primary_key_columns => [ 'assembly_id' ], + + allow_inline_column_values => 1, +); + +1; +; diff --git a/SL/DB/MetaSetup/Bin.pm b/SL/DB/MetaSetup/Bin.pm new file mode 100644 index 000000000..ff225207f --- /dev/null +++ b/SL/DB/MetaSetup/Bin.pm @@ -0,0 +1,33 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::Bin; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'bin', + + columns => [ + id => { type => 'integer', not_null => 1, sequence => 'id' }, + warehouse_id => { type => 'integer', not_null => 1 }, + description => { type => 'text' }, + itime => { type => 'timestamp', default => 'now()' }, + mtime => { type => 'timestamp' }, + ], + + primary_key_columns => [ 'id' ], + + allow_inline_column_values => 1, + + foreign_keys => [ + warehouse => { + class => 'SL::DB::Warehouse', + key_columns => { warehouse_id => 'id' }, + }, + ], +); + +1; +; diff --git a/SL/DB/MetaSetup/Business.pm b/SL/DB/MetaSetup/Business.pm new file mode 100644 index 000000000..243d524f4 --- /dev/null +++ b/SL/DB/MetaSetup/Business.pm @@ -0,0 +1,28 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::Business; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'business', + + columns => [ + id => { type => 'integer', not_null => 1, sequence => 'id' }, + description => { type => 'text' }, + discount => { type => 'float', precision => 4 }, + customernumberinit => { type => 'text' }, + salesman => { type => 'boolean', default => 'false' }, + itime => { type => 'timestamp', default => 'now()' }, + mtime => { type => 'timestamp' }, + ], + + primary_key_columns => [ 'id' ], + + allow_inline_column_values => 1, +); + +1; +; diff --git a/SL/DB/MetaSetup/Chart.pm b/SL/DB/MetaSetup/Chart.pm new file mode 100644 index 000000000..c6cce7158 --- /dev/null +++ b/SL/DB/MetaSetup/Chart.pm @@ -0,0 +1,40 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::Chart; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'chart', + + columns => [ + id => { type => 'integer', not_null => 1, sequence => 'id' }, + accno => { type => 'text', not_null => 1 }, + description => { type => 'text' }, + charttype => { type => 'character', default => 'A', length => 1 }, + category => { type => 'character', length => 1 }, + link => { type => 'text' }, + gifi_accno => { type => 'text' }, + taxkey_id => { type => 'integer' }, + pos_ustva => { type => 'integer' }, + pos_bwa => { type => 'integer' }, + pos_bilanz => { type => 'integer' }, + pos_eur => { type => 'integer' }, + datevautomatik => { type => 'boolean', default => 'false' }, + itime => { type => 'timestamp', default => 'now()' }, + mtime => { type => 'timestamp' }, + new_chart_id => { type => 'integer' }, + valid_from => { type => 'date' }, + ], + + primary_key_columns => [ 'id' ], + + unique_key => [ 'accno' ], + + allow_inline_column_values => 1, +); + +1; +; diff --git a/SL/DB/MetaSetup/Customer.pm b/SL/DB/MetaSetup/Customer.pm new file mode 100644 index 000000000..93f63b7ee --- /dev/null +++ b/SL/DB/MetaSetup/Customer.pm @@ -0,0 +1,65 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::Customer; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'customer', + + columns => [ + id => { type => 'integer', not_null => 1, sequence => 'id' }, + name => { type => 'varchar', length => 75, not_null => 1 }, + department_1 => { type => 'varchar', length => 75 }, + department_2 => { type => 'varchar', length => 75 }, + street => { type => 'varchar', length => 75 }, + zipcode => { type => 'varchar', length => 10 }, + city => { type => 'varchar', length => 75 }, + country => { type => 'varchar', length => 75 }, + contact => { type => 'varchar', length => 75 }, + phone => { type => 'varchar', length => 30 }, + fax => { type => 'varchar', length => 30 }, + homepage => { type => 'text' }, + email => { type => 'text' }, + notes => { type => 'text' }, + discount => { type => 'float', precision => 4 }, + taxincluded => { type => 'boolean' }, + creditlimit => { type => 'numeric', default => '0', precision => 5, scale => 15 }, + terms => { type => 'integer', default => '0' }, + customernumber => { type => 'text' }, + cc => { type => 'text' }, + bcc => { type => 'text' }, + business_id => { type => 'integer' }, + taxnumber => { type => 'text' }, + account_number => { type => 'varchar', length => 15 }, + bank_code => { type => 'varchar', length => 10 }, + bank => { type => 'text' }, + language => { type => 'varchar', length => 5 }, + datevexport => { type => 'integer' }, + itime => { type => 'timestamp', default => 'now()' }, + mtime => { type => 'timestamp' }, + obsolete => { type => 'boolean', default => 'false' }, + username => { type => 'varchar', length => 50 }, + user_password => { type => 'text' }, + salesman_id => { type => 'integer' }, + c_vendor_id => { type => 'text' }, + klass => { type => 'integer', default => '0' }, + language_id => { type => 'integer' }, + payment_id => { type => 'integer' }, + taxzone_id => { type => 'integer', default => '0', not_null => 1 }, + greeting => { type => 'text' }, + ustid => { type => 'varchar', length => 14 }, + direct_debit => { type => 'boolean', default => 'false' }, + iban => { type => 'varchar', length => 100 }, + bic => { type => 'varchar', length => 100 }, + ], + + primary_key_columns => [ 'id' ], + + allow_inline_column_values => 1, +); + +1; +; diff --git a/SL/DB/MetaSetup/DeliveryOrder.pm b/SL/DB/MetaSetup/DeliveryOrder.pm new file mode 100644 index 000000000..fc159fb7f --- /dev/null +++ b/SL/DB/MetaSetup/DeliveryOrder.pm @@ -0,0 +1,69 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::DeliveryOrder; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'delivery_orders', + + columns => [ + id => { type => 'integer', not_null => 1, sequence => 'id' }, + donumber => { type => 'text', not_null => 1 }, + ordnumber => { type => 'text' }, + transdate => { type => 'date', default => 'now()' }, + vendor_id => { type => 'integer' }, + customer_id => { type => 'integer' }, + reqdate => { type => 'date' }, + shippingpoint => { type => 'text' }, + notes => { type => 'text' }, + intnotes => { type => 'text' }, + employee_id => { type => 'integer' }, + closed => { type => 'boolean', default => 'false' }, + delivered => { type => 'boolean', default => 'false' }, + cusordnumber => { type => 'text' }, + oreqnumber => { type => 'text' }, + department_id => { type => 'integer' }, + shipvia => { type => 'text' }, + cp_id => { type => 'integer' }, + language_id => { type => 'integer' }, + shipto_id => { type => 'integer' }, + globalproject_id => { type => 'integer' }, + salesman_id => { type => 'integer' }, + transaction_description => { type => 'text' }, + is_sales => { type => 'boolean' }, + itime => { type => 'timestamp', default => 'now()' }, + mtime => { type => 'timestamp' }, + notes_bottom => { type => 'text' }, + taxzone_id => { type => 'integer' }, + taxincluded => { type => 'boolean' }, + terms => { type => 'integer' }, + curr => { type => 'character', length => 3 }, + ], + + primary_key_columns => [ 'id' ], + + allow_inline_column_values => 1, + + foreign_keys => [ + customer => { + class => 'SL::DB::Customer', + key_columns => { customer_id => 'id' }, + }, + + globalproject => { + class => 'SL::DB::Project', + key_columns => { globalproject_id => 'id' }, + }, + + vendor => { + class => 'SL::DB::Vendor', + key_columns => { vendor_id => 'id' }, + }, + ], +); + +1; +; diff --git a/SL/DB/MetaSetup/DeliveryOrderItem.pm b/SL/DB/MetaSetup/DeliveryOrderItem.pm new file mode 100644 index 000000000..6bdfe4300 --- /dev/null +++ b/SL/DB/MetaSetup/DeliveryOrderItem.pm @@ -0,0 +1,65 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::DeliveryOrderItem; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'delivery_order_items', + + columns => [ + id => { type => 'integer', not_null => 1, sequence => 'delivery_order_items_id' }, + delivery_order_id => { type => 'integer', not_null => 1 }, + parts_id => { type => 'integer', not_null => 1 }, + description => { type => 'text' }, + qty => { type => 'numeric', precision => 5, scale => 25 }, + sellprice => { type => 'numeric', precision => 5, scale => 15 }, + discount => { type => 'float', precision => 4 }, + project_id => { type => 'integer' }, + reqdate => { type => 'date' }, + serialnumber => { type => 'text' }, + ordnumber => { type => 'text' }, + transdate => { type => 'text' }, + cusordnumber => { type => 'text' }, + unit => { type => 'varchar', length => 20 }, + base_qty => { type => 'float', precision => 4 }, + longdescription => { type => 'text' }, + lastcost => { type => 'numeric', precision => 5, scale => 15 }, + price_factor_id => { type => 'integer' }, + price_factor => { type => 'numeric', default => 1, precision => 5, scale => 15 }, + marge_price_factor => { type => 'numeric', default => 1, precision => 5, scale => 15 }, + itime => { type => 'timestamp', default => 'now()' }, + mtime => { type => 'timestamp' }, + ], + + primary_key_columns => [ 'id' ], + + allow_inline_column_values => 1, + + foreign_keys => [ + delivery_order => { + class => 'SL::DB::DeliveryOrder', + key_columns => { delivery_order_id => 'id' }, + }, + + parts => { + class => 'SL::DB::Part', + key_columns => { parts_id => 'id' }, + }, + + price_factor_obj => { + class => 'SL::DB::PriceFactor', + key_columns => { price_factor_id => 'id' }, + }, + + project => { + class => 'SL::DB::Project', + key_columns => { project_id => 'id' }, + }, + ], +); + +1; +; diff --git a/SL/DB/MetaSetup/DeliveryOrderItemsStock.pm b/SL/DB/MetaSetup/DeliveryOrderItemsStock.pm new file mode 100644 index 000000000..99a2925d7 --- /dev/null +++ b/SL/DB/MetaSetup/DeliveryOrderItemsStock.pm @@ -0,0 +1,48 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::DeliveryOrderItemsStock; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'delivery_order_items_stock', + + columns => [ + id => { type => 'integer', not_null => 1, sequence => 'id' }, + delivery_order_item_id => { type => 'integer', not_null => 1 }, + qty => { type => 'numeric', not_null => 1, precision => 5, scale => 15 }, + unit => { type => 'varchar', length => 20, not_null => 1 }, + warehouse_id => { type => 'integer', not_null => 1 }, + bin_id => { type => 'integer', not_null => 1 }, + chargenumber => { type => 'text' }, + itime => { type => 'timestamp', default => 'now()' }, + mtime => { type => 'timestamp' }, + bestbefore => { type => 'date' }, + ], + + primary_key_columns => [ 'id' ], + + allow_inline_column_values => 1, + + foreign_keys => [ + bin => { + class => 'SL::DB::Bin', + key_columns => { bin_id => 'id' }, + }, + + delivery_order_item => { + class => 'SL::DB::DeliveryOrderItem', + key_columns => { delivery_order_item_id => 'id' }, + }, + + warehouse => { + class => 'SL::DB::Warehouse', + key_columns => { warehouse_id => 'id' }, + }, + ], +); + +1; +; diff --git a/SL/DB/MetaSetup/GLTransaction.pm b/SL/DB/MetaSetup/GLTransaction.pm new file mode 100644 index 000000000..a7d25f7bf --- /dev/null +++ b/SL/DB/MetaSetup/GLTransaction.pm @@ -0,0 +1,44 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::GLTransaction; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'gl', + + columns => [ + id => { type => 'integer', not_null => 1, sequence => 'glid' }, + reference => { type => 'text' }, + description => { type => 'text' }, + transdate => { type => 'date', default => 'now' }, + gldate => { type => 'date', default => 'now' }, + employee_id => { type => 'integer' }, + notes => { type => 'text' }, + department_id => { type => 'integer', default => '0' }, + taxincluded => { type => 'boolean' }, + itime => { type => 'timestamp', default => 'now()' }, + mtime => { type => 'timestamp' }, + type => { type => 'text' }, + storno => { type => 'boolean', default => 'false' }, + storno_id => { type => 'integer' }, + ob_transaction => { type => 'boolean' }, + cb_transaction => { type => 'boolean' }, + ], + + primary_key_columns => [ 'id' ], + + allow_inline_column_values => 1, + + foreign_keys => [ + storno_obj => { + class => 'SL::DB::GLTransaction', + key_columns => { storno_id => 'id' }, + }, + ], +); + +1; +; diff --git a/SL/DB/MetaSetup/Invoice.pm b/SL/DB/MetaSetup/Invoice.pm new file mode 100644 index 000000000..3d7e93af9 --- /dev/null +++ b/SL/DB/MetaSetup/Invoice.pm @@ -0,0 +1,84 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::Invoice; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'ar', + + columns => [ + id => { type => 'integer', not_null => 1, sequence => 'glid' }, + invnumber => { type => 'text', not_null => 1 }, + transdate => { type => 'date', default => 'now' }, + gldate => { type => 'date', default => 'now' }, + customer_id => { type => 'integer' }, + taxincluded => { type => 'boolean' }, + amount => { type => 'numeric', precision => 5, scale => 15 }, + netamount => { type => 'numeric', precision => 5, scale => 15 }, + paid => { type => 'numeric', precision => 5, scale => 15 }, + datepaid => { type => 'date' }, + duedate => { type => 'date' }, + deliverydate => { type => 'date' }, + invoice => { type => 'boolean', default => 'false' }, + shippingpoint => { type => 'text' }, + terms => { type => 'integer', default => '0' }, + notes => { type => 'text' }, + curr => { type => 'character', length => 3 }, + ordnumber => { type => 'text' }, + employee_id => { type => 'integer' }, + quonumber => { type => 'text' }, + cusordnumber => { type => 'text' }, + intnotes => { type => 'text' }, + department_id => { type => 'integer', default => '0' }, + shipvia => { type => 'text' }, + itime => { type => 'timestamp', default => 'now()' }, + mtime => { type => 'timestamp' }, + cp_id => { type => 'integer' }, + language_id => { type => 'integer' }, + payment_id => { type => 'integer' }, + delivery_customer_id => { type => 'integer' }, + delivery_vendor_id => { type => 'integer' }, + storno => { type => 'boolean', default => 'false' }, + taxzone_id => { type => 'integer' }, + shipto_id => { type => 'integer' }, + type => { type => 'text' }, + dunning_config_id => { type => 'integer' }, + orddate => { type => 'date' }, + quodate => { type => 'date' }, + globalproject_id => { type => 'integer' }, + salesman_id => { type => 'integer' }, + transaction_description => { type => 'text' }, + storno_id => { type => 'integer' }, + marge_total => { type => 'numeric', precision => 5, scale => 15 }, + marge_percent => { type => 'numeric', precision => 5, scale => 15 }, + notes_bottom => { type => 'text' }, + donumber => { type => 'text' }, + ], + + primary_key_columns => [ 'id' ], + + allow_inline_column_values => 1, + + foreign_keys => [ + customer => { + class => 'SL::DB::Customer', + key_columns => { customer_id => 'id' }, + }, + + globalproject => { + class => 'SL::DB::Project', + key_columns => { globalproject_id => 'id' }, + }, + + storno_obj => { + class => 'SL::DB::Invoice', + key_columns => { storno_id => 'id' }, + }, + ], +); + +1; +; diff --git a/SL/DB/MetaSetup/InvoiceItem.pm b/SL/DB/MetaSetup/InvoiceItem.pm new file mode 100644 index 000000000..9a851e057 --- /dev/null +++ b/SL/DB/MetaSetup/InvoiceItem.pm @@ -0,0 +1,57 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::InvoiceItem; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'invoice', + + columns => [ + id => { type => 'integer', not_null => 1, sequence => 'invoiceid' }, + trans_id => { type => 'integer' }, + parts_id => { type => 'integer' }, + description => { type => 'text' }, + qty => { type => 'float', precision => 4 }, + allocated => { type => 'float', precision => 4 }, + sellprice => { type => 'numeric', precision => 5, scale => 15 }, + fxsellprice => { type => 'numeric', precision => 5, scale => 15 }, + discount => { type => 'float', precision => 4 }, + assemblyitem => { type => 'boolean', default => 'false' }, + project_id => { type => 'integer' }, + deliverydate => { type => 'date' }, + serialnumber => { type => 'text' }, + itime => { type => 'timestamp', default => 'now()' }, + mtime => { type => 'timestamp' }, + pricegroup_id => { type => 'integer' }, + ordnumber => { type => 'text' }, + transdate => { type => 'text' }, + cusordnumber => { type => 'text' }, + unit => { type => 'varchar', length => 20 }, + base_qty => { type => 'float', precision => 4 }, + subtotal => { type => 'boolean', default => 'false' }, + longdescription => { type => 'text' }, + marge_total => { type => 'numeric', precision => 5, scale => 15 }, + marge_percent => { type => 'numeric', precision => 5, scale => 15 }, + lastcost => { type => 'numeric', precision => 5, scale => 15 }, + price_factor_id => { type => 'integer' }, + price_factor => { type => 'numeric', default => 1, precision => 5, scale => 15 }, + marge_price_factor => { type => 'numeric', default => 1, precision => 5, scale => 15 }, + ], + + primary_key_columns => [ 'id' ], + + allow_inline_column_values => 1, + + foreign_keys => [ + parts => { + class => 'SL::DB::Part', + key_columns => { parts_id => 'id' }, + }, + ], +); + +1; +; diff --git a/SL/DB/MetaSetup/Order.pm b/SL/DB/MetaSetup/Order.pm new file mode 100644 index 000000000..e0aa5711d --- /dev/null +++ b/SL/DB/MetaSetup/Order.pm @@ -0,0 +1,66 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::Order; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'oe', + + columns => [ + id => { type => 'integer', not_null => 1, sequence => 'id' }, + ordnumber => { type => 'text', not_null => 1 }, + transdate => { type => 'date', default => 'now' }, + vendor_id => { type => 'integer' }, + customer_id => { type => 'integer' }, + amount => { type => 'numeric', precision => 5, scale => 15 }, + netamount => { type => 'numeric', precision => 5, scale => 15 }, + reqdate => { type => 'date' }, + taxincluded => { type => 'boolean' }, + shippingpoint => { type => 'text' }, + notes => { type => 'text' }, + curr => { type => 'character', length => 3 }, + employee_id => { type => 'integer' }, + closed => { type => 'boolean', default => 'false' }, + quotation => { type => 'boolean', default => 'false' }, + quonumber => { type => 'text' }, + cusordnumber => { type => 'text' }, + intnotes => { type => 'text' }, + department_id => { type => 'integer', default => '0' }, + itime => { type => 'timestamp', default => 'now()' }, + mtime => { type => 'timestamp' }, + shipvia => { type => 'text' }, + cp_id => { type => 'integer' }, + language_id => { type => 'integer' }, + payment_id => { type => 'integer' }, + delivery_customer_id => { type => 'integer' }, + delivery_vendor_id => { type => 'integer' }, + taxzone_id => { type => 'integer' }, + proforma => { type => 'boolean', default => 'false' }, + shipto_id => { type => 'integer' }, + delivered => { type => 'boolean', default => 'false' }, + globalproject_id => { type => 'integer' }, + salesman_id => { type => 'integer' }, + transaction_description => { type => 'text' }, + marge_total => { type => 'numeric', precision => 5, scale => 15 }, + marge_percent => { type => 'numeric', precision => 5, scale => 15 }, + notes_bottom => { type => 'text' }, + project_manager_id => { type => 'integer' }, + ], + + primary_key_columns => [ 'id' ], + + allow_inline_column_values => 1, + + foreign_keys => [ + globalproject => { + class => 'SL::DB::Project', + key_columns => { globalproject_id => 'id' }, + }, + ], +); + +1; +; diff --git a/SL/DB/MetaSetup/OrderItem.pm b/SL/DB/MetaSetup/OrderItem.pm new file mode 100644 index 000000000..3f048c34f --- /dev/null +++ b/SL/DB/MetaSetup/OrderItem.pm @@ -0,0 +1,55 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::OrderItem; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'orderitems', + + columns => [ + trans_id => { type => 'integer' }, + parts_id => { type => 'integer' }, + description => { type => 'text' }, + qty => { type => 'float', precision => 4 }, + sellprice => { type => 'numeric', precision => 5, scale => 15 }, + discount => { type => 'float', precision => 4 }, + project_id => { type => 'integer' }, + reqdate => { type => 'date' }, + ship => { type => 'float', precision => 4 }, + serialnumber => { type => 'text' }, + id => { type => 'integer', not_null => 1, sequence => 'orderitemsid' }, + itime => { type => 'timestamp', default => 'now()' }, + mtime => { type => 'timestamp' }, + pricegroup_id => { type => 'integer' }, + ordnumber => { type => 'text' }, + transdate => { type => 'text' }, + cusordnumber => { type => 'text' }, + unit => { type => 'varchar', length => 20 }, + base_qty => { type => 'float', precision => 4 }, + subtotal => { type => 'boolean', default => 'false' }, + longdescription => { type => 'text' }, + marge_total => { type => 'numeric', precision => 5, scale => 15 }, + marge_percent => { type => 'numeric', precision => 5, scale => 15 }, + lastcost => { type => 'numeric', precision => 5, scale => 15 }, + price_factor_id => { type => 'integer' }, + price_factor => { type => 'numeric', default => 1, precision => 5, scale => 15 }, + marge_price_factor => { type => 'numeric', default => 1, precision => 5, scale => 15 }, + ], + + primary_key_columns => [ 'id' ], + + allow_inline_column_values => 1, + + foreign_keys => [ + parts => { + class => 'SL::DB::Part', + key_columns => { parts_id => 'id' }, + }, + ], +); + +1; +; diff --git a/SL/DB/MetaSetup/Part.pm b/SL/DB/MetaSetup/Part.pm new file mode 100644 index 000000000..f80ad78e9 --- /dev/null +++ b/SL/DB/MetaSetup/Part.pm @@ -0,0 +1,59 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::Part; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'parts', + + columns => [ + id => { type => 'integer', not_null => 1, sequence => 'id' }, + partnumber => { type => 'text', not_null => 1 }, + description => { type => 'text' }, + listprice => { type => 'numeric', precision => 5, scale => 15 }, + sellprice => { type => 'numeric', precision => 5, scale => 15 }, + lastcost => { type => 'numeric', precision => 5, scale => 15 }, + priceupdate => { type => 'date', default => 'now' }, + weight => { type => 'float', precision => 4 }, + notes => { type => 'text' }, + makemodel => { type => 'boolean', default => 'false' }, + assembly => { type => 'boolean', default => 'false' }, + alternate => { type => 'boolean', default => 'false' }, + rop => { type => 'float', precision => 4 }, + inventory_accno_id => { type => 'integer' }, + income_accno_id => { type => 'integer' }, + expense_accno_id => { type => 'integer' }, + bin => { type => 'text' }, + shop => { type => 'boolean', default => 'false' }, + obsolete => { type => 'boolean', default => 'false' }, + bom => { type => 'boolean', default => 'false' }, + image => { type => 'text' }, + drawing => { type => 'text' }, + microfiche => { type => 'text' }, + partsgroup_id => { type => 'integer' }, + ve => { type => 'integer' }, + gv => { type => 'numeric', precision => 5, scale => 15 }, + itime => { type => 'timestamp', default => 'now()' }, + mtime => { type => 'timestamp' }, + unit => { type => 'varchar', length => 20, not_null => 1 }, + formel => { type => 'text' }, + not_discountable => { type => 'boolean', default => 'false' }, + buchungsgruppen_id => { type => 'integer' }, + payment_id => { type => 'integer' }, + ean => { type => 'text' }, + price_factor_id => { type => 'integer' }, + onhand => { type => 'numeric', default => '0', precision => 5, scale => 25 }, + stockable => { type => 'boolean', default => 'false' }, + has_sernumber => { type => 'boolean', default => 'false' }, + ], + + primary_key_columns => [ 'id' ], + + allow_inline_column_values => 1, +); + +1; +; diff --git a/SL/DB/MetaSetup/PriceFactor.pm b/SL/DB/MetaSetup/PriceFactor.pm new file mode 100644 index 000000000..2eeb5967a --- /dev/null +++ b/SL/DB/MetaSetup/PriceFactor.pm @@ -0,0 +1,23 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::PriceFactor; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'price_factors', + + columns => [ + id => { type => 'integer', not_null => 1, sequence => 'id' }, + description => { type => 'text' }, + factor => { type => 'numeric', precision => 5, scale => 15 }, + sortkey => { type => 'integer' }, + ], + + primary_key_columns => [ 'id' ], +); + +1; +; diff --git a/SL/DB/MetaSetup/Printer.pm b/SL/DB/MetaSetup/Printer.pm new file mode 100644 index 000000000..fc2446a1b --- /dev/null +++ b/SL/DB/MetaSetup/Printer.pm @@ -0,0 +1,23 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::Printer; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'printers', + + columns => [ + id => { type => 'integer', not_null => 1, sequence => 'id' }, + printer_description => { type => 'text', not_null => 1 }, + printer_command => { type => 'text' }, + template_code => { type => 'text' }, + ], + + primary_key_columns => [ 'id' ], +); + +1; +; diff --git a/SL/DB/MetaSetup/Project.pm b/SL/DB/MetaSetup/Project.pm new file mode 100644 index 000000000..6b3ab9105 --- /dev/null +++ b/SL/DB/MetaSetup/Project.pm @@ -0,0 +1,29 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::Project; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'project', + + columns => [ + id => { type => 'integer', not_null => 1, sequence => 'id' }, + projectnumber => { type => 'text' }, + description => { type => 'text' }, + itime => { type => 'timestamp', default => 'now()' }, + mtime => { type => 'timestamp' }, + active => { type => 'boolean', default => 'true' }, + ], + + primary_key_columns => [ 'id' ], + + unique_key => [ 'projectnumber' ], + + allow_inline_column_values => 1, +); + +1; +; diff --git a/SL/DB/MetaSetup/PurchaseInvoice.pm b/SL/DB/MetaSetup/PurchaseInvoice.pm new file mode 100644 index 000000000..0ea9d10fa --- /dev/null +++ b/SL/DB/MetaSetup/PurchaseInvoice.pm @@ -0,0 +1,72 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::PurchaseInvoice; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'ap', + + columns => [ + id => { type => 'integer', not_null => 1, sequence => 'glid' }, + invnumber => { type => 'text', not_null => 1 }, + transdate => { type => 'date', default => 'now' }, + gldate => { type => 'date', default => 'now' }, + vendor_id => { type => 'integer' }, + taxincluded => { type => 'boolean', default => 'false' }, + amount => { type => 'numeric', precision => 5, scale => 15 }, + netamount => { type => 'numeric', precision => 5, scale => 15 }, + paid => { type => 'numeric', precision => 5, scale => 15 }, + datepaid => { type => 'date' }, + duedate => { type => 'date' }, + invoice => { type => 'boolean', default => 'false' }, + ordnumber => { type => 'text' }, + curr => { type => 'character', length => 3 }, + notes => { type => 'text' }, + employee_id => { type => 'integer' }, + quonumber => { type => 'text' }, + intnotes => { type => 'text' }, + department_id => { type => 'integer', default => '0' }, + itime => { type => 'timestamp', default => 'now()' }, + mtime => { type => 'timestamp' }, + shipvia => { type => 'text' }, + cp_id => { type => 'integer' }, + language_id => { type => 'integer' }, + payment_id => { type => 'integer' }, + storno => { type => 'boolean', default => 'false' }, + taxzone_id => { type => 'integer' }, + type => { type => 'text' }, + orddate => { type => 'date' }, + quodate => { type => 'date' }, + globalproject_id => { type => 'integer' }, + transaction_description => { type => 'text' }, + storno_id => { type => 'integer' }, + notes_bottom => { type => 'text' }, + ], + + primary_key_columns => [ 'id' ], + + allow_inline_column_values => 1, + + foreign_keys => [ + globalproject => { + class => 'SL::DB::Project', + key_columns => { globalproject_id => 'id' }, + }, + + storno_obj => { + class => 'SL::DB::PurchaseInvoice', + key_columns => { storno_id => 'id' }, + }, + + vendor => { + class => 'SL::DB::Vendor', + key_columns => { vendor_id => 'id' }, + }, + ], +); + +1; +; diff --git a/SL/DB/MetaSetup/Shipto.pm b/SL/DB/MetaSetup/Shipto.pm new file mode 100644 index 000000000..0943c0448 --- /dev/null +++ b/SL/DB/MetaSetup/Shipto.pm @@ -0,0 +1,37 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::Shipto; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'shipto', + + columns => [ + trans_id => { type => 'integer' }, + shiptoname => { type => 'varchar', length => 75 }, + shiptodepartment_1 => { type => 'varchar', length => 75 }, + shiptodepartment_2 => { type => 'varchar', length => 75 }, + shiptostreet => { type => 'varchar', length => 75 }, + shiptozipcode => { type => 'varchar', length => 75 }, + shiptocity => { type => 'varchar', length => 75 }, + shiptocountry => { type => 'varchar', length => 75 }, + shiptocontact => { type => 'varchar', length => 75 }, + shiptophone => { type => 'varchar', length => 30 }, + shiptofax => { type => 'varchar', length => 30 }, + shiptoemail => { type => 'text' }, + itime => { type => 'timestamp', default => 'now()' }, + mtime => { type => 'timestamp' }, + module => { type => 'text' }, + shipto_id => { type => 'integer', not_null => 1, sequence => 'id' }, + ], + + primary_key_columns => [ 'shipto_id' ], + + allow_inline_column_values => 1, +); + +1; +; diff --git a/SL/DB/MetaSetup/TransferType.pm b/SL/DB/MetaSetup/TransferType.pm new file mode 100644 index 000000000..1f018bfd4 --- /dev/null +++ b/SL/DB/MetaSetup/TransferType.pm @@ -0,0 +1,27 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::TransferType; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'transfer_type', + + columns => [ + id => { type => 'integer', not_null => 1, sequence => 'id' }, + direction => { type => 'varchar', length => 10, not_null => 1 }, + description => { type => 'text' }, + sortkey => { type => 'integer' }, + itime => { type => 'timestamp', default => 'now()' }, + mtime => { type => 'timestamp' }, + ], + + primary_key_columns => [ 'id' ], + + allow_inline_column_values => 1, +); + +1; +; diff --git a/SL/DB/MetaSetup/Unit.pm b/SL/DB/MetaSetup/Unit.pm new file mode 100644 index 000000000..c22946283 --- /dev/null +++ b/SL/DB/MetaSetup/Unit.pm @@ -0,0 +1,31 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::Unit; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'units', + + columns => [ + name => { type => 'varchar', length => 20, not_null => 1 }, + base_unit => { type => 'varchar', length => 20 }, + factor => { type => 'numeric', precision => 5, scale => 20 }, + type => { type => 'varchar', length => 20 }, + sortkey => { type => 'integer', not_null => 1 }, + ], + + primary_key_columns => [ 'name' ], + + foreign_keys => [ + unit => { + class => 'SL::DB::Unit', + key_columns => { base_unit => 'name' }, + }, + ], +); + +1; +; diff --git a/SL/DB/MetaSetup/Vendor.pm b/SL/DB/MetaSetup/Vendor.pm new file mode 100644 index 000000000..6d858e59f --- /dev/null +++ b/SL/DB/MetaSetup/Vendor.pm @@ -0,0 +1,65 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::Vendor; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'vendor', + + columns => [ + id => { type => 'integer', not_null => 1, sequence => 'id' }, + name => { type => 'varchar', length => 75, not_null => 1 }, + department_1 => { type => 'varchar', length => 75 }, + department_2 => { type => 'varchar', length => 75 }, + street => { type => 'varchar', length => 75 }, + zipcode => { type => 'varchar', length => 10 }, + city => { type => 'varchar', length => 75 }, + country => { type => 'varchar', length => 75 }, + contact => { type => 'varchar', length => 75 }, + phone => { type => 'varchar', length => 30 }, + fax => { type => 'varchar', length => 30 }, + homepage => { type => 'text' }, + email => { type => 'text' }, + notes => { type => 'text' }, + terms => { type => 'integer', default => '0' }, + taxincluded => { type => 'boolean' }, + vendornumber => { type => 'text' }, + cc => { type => 'text' }, + bcc => { type => 'text' }, + gifi_accno => { type => 'text' }, + business_id => { type => 'integer' }, + taxnumber => { type => 'text' }, + discount => { type => 'float', precision => 4 }, + creditlimit => { type => 'numeric', precision => 5, scale => 15 }, + account_number => { type => 'varchar', length => 15 }, + bank_code => { type => 'varchar', length => 10 }, + bank => { type => 'text' }, + language => { type => 'varchar', length => 5 }, + datevexport => { type => 'integer' }, + itime => { type => 'timestamp', default => 'now()' }, + mtime => { type => 'timestamp' }, + obsolete => { type => 'boolean', default => 'false' }, + username => { type => 'varchar', length => 50 }, + user_password => { type => 'varchar', length => 12 }, + salesman_id => { type => 'integer' }, + v_customer_id => { type => 'text' }, + language_id => { type => 'integer' }, + payment_id => { type => 'integer' }, + taxzone_id => { type => 'integer', default => '0', not_null => 1 }, + greeting => { type => 'text' }, + ustid => { type => 'varchar', length => 14 }, + direct_debit => { type => 'boolean', default => 'false' }, + iban => { type => 'varchar', length => 100 }, + bic => { type => 'varchar', length => 100 }, + ], + + primary_key_columns => [ 'id' ], + + allow_inline_column_values => 1, +); + +1; +; diff --git a/SL/DB/MetaSetup/Warehouse.pm b/SL/DB/MetaSetup/Warehouse.pm new file mode 100644 index 000000000..0638b06cf --- /dev/null +++ b/SL/DB/MetaSetup/Warehouse.pm @@ -0,0 +1,27 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::Warehouse; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'warehouse', + + columns => [ + id => { type => 'integer', not_null => 1, sequence => 'id' }, + description => { type => 'text' }, + itime => { type => 'timestamp', default => 'now()' }, + mtime => { type => 'timestamp' }, + sortkey => { type => 'integer' }, + invalid => { type => 'boolean' }, + ], + + primary_key_columns => [ 'id' ], + + allow_inline_column_values => 1, +); + +1; +; diff --git a/SL/DB/Object.pm b/SL/DB/Object.pm new file mode 100644 index 000000000..1a39431c6 --- /dev/null +++ b/SL/DB/Object.pm @@ -0,0 +1,154 @@ +package SL::DB::Object; + +use strict; + +use Readonly; +use Rose::DB::Object; +use List::MoreUtils qw(any); + +use SL::DB; +use SL::DB::Helpers::AttrNumber; +use SL::DB::Helpers::AttrDate; +use SL::DB::Helpers::AttrPercent; +use SL::DB::Helpers::Metadata; +use SL::DB::Helpers::Manager; + +use base qw(Rose::DB::Object); + +sub new { + my $class = shift; + my $self = $class->SUPER::new(); + + $self->_assign_attributes(@_) if $self; + + return $self; +} + +sub init_db { + my $class_or_self = shift; + my $class = ref($class_or_self) || $class_or_self; + my $type = 'LXOFFICE'; + + return SL::DB::create(undef, $type); +} + +sub meta_class { + return 'SL::DB::Helpers::Metadata'; +} + +sub _get_manager_class { + my $class_or_self = shift; + my $class = ref($class_or_self) || $class_or_self; + + return $class->meta->convention_manager->auto_manager_class_name($class); +} + +Readonly my %text_column_types => (text => 1, char => 1, varchar => 1); + +sub assign_attributes { + my $self = shift; + my %attributes = @_; + + my $pk = ref($self)->meta->primary_key; + delete @attributes{$pk->column_names} if $pk; + + return $self->_assign_attributes(%attributes); +} + +sub _assign_attributes { + my $self = shift; + my %attributes = @_; + + my %types = map { $_->name => $_->type } ref($self)->meta->columns; + + while (my ($attribute, $value) = each %attributes) { + my $type = lc($types{$attribute} || 'text'); + $value = $type eq 'boolean' ? ($value ? 't' : 'f') + : $text_column_types{$type} ? $value + : ($value || undef); + $self->$attribute($value); + } + + return $self; +} + +sub update_attributes { + my $self = shift; + + $self->assign_attributes(@_)->save; + + return $self; +} + +sub attr_number { + SL::DB::Helpers::AttrNumber::define(@_); +} + +sub attr_date { + SL::DB::Helpers::AttrDate::define(@_); +} + +sub attr_percent { + SL::DB::Helpers::AttrPercent::define(@_); +} + +1; + +__END__ + +=pod + +=head1 NAME + +SL::DB::Object: Base class for all of our model classes + +=head1 DESCRIPTION + +This is the base class from which all other model classes are +derived. It contains functionality and settings required for all model +classes. + +Several functions (e.g. C, C) in this +class are used for setting up the classes / base classes used for all +model instances. They overwrite the functions from +L. + +=head1 FUNCTIONS + +=over 4 + +=item assign_attributes %attributes + +=item _assign_attributes %attributes + +Assigns all elements from C<%attributes> to the columns by calling +their setter functions. The difference between the two functions is +that C protects primary key columns while +C<_assign_attributes> doesn't. + +Both functions handle values that are empty strings by replacing them +with C for non-text columns. This allows the calling functions +to use data from HTML forms as the input for C +without having to remove empty strings themselves (think of +e.g. select boxes with an empty option which should be turned into +C in the database). + +=item update_attributes %attributes + +Assigns the attributes from C<%attributes> by calling the +C function and saves the object afterwards. Returns +the object itself. + +=item _get_manager_class + +Returns the manager package for the object or class that it is called +on. Can be used from methods in this package for getting the actual +object's manager. + +=back + +=head1 AUTHOR + +Moritz Bunkus Em.bunkus@linet-services.deE + +=cut diff --git a/SL/DB/Order.pm b/SL/DB/Order.pm new file mode 100644 index 000000000..309b5061f --- /dev/null +++ b/SL/DB/Order.pm @@ -0,0 +1,106 @@ +package SL::DB::Order; + +use strict; + +use SL::RecordLinks; + +use SL::DB::MetaSetup::Order; +use SL::DB::Manager::Order; +use SL::DB::Invoice; + +__PACKAGE__->attr_number($_, places => -2) for qw(amount netamount marge_total marge_percent); +__PACKAGE__->attr_date($_) for qw(transdate reqdate); + +__PACKAGE__->meta->add_relationship( + orderitems => { + type => 'one to many', + class => 'SL::DB::OrderItem', + column_map => { id => 'trans_id' }, + manager_args => { + with_objects => [ 'part' ] + } + } +); + +__PACKAGE__->meta->initialize; + +# methods + +sub type { + my $self = shift; + + return 'sales_order' if $self->customer_id && ! $self->quotation; + return 'purchase_order' if $self->vendor_id && ! $self->quotation; + return 'sales_quotation' if $self->customer_id && $self->quotation; + return 'request_quotation' if $self->vendor_id && $self->quotation; + + return; +} + +sub is_type { + return shift->type eq shift; +} + +sub invoices { + my $self = shift; + my %params = @_; + + if ($self->quotation) { + return []; + } else { + return SL::DB::Manager::Invoice->get_all( + query => [ + ordnumber => $self->ordnumber, + @{ $params{query} || [] }, + ] + ); + } +} + +sub abschlag_invoices { + return shift()->invoices(query => [ abschlag => 1 ]); +} + +sub end_invoice { + return shift()->invoices(query => [ abschlag => 0 ]); +} + +1; + +__END__ + +=head1 NAME + +SL::DB::Order - Order Datenbank Objekt. + +=head1 FUNCTIONS + +=head2 type + +Returns one of the following string types: + +=over 4 + +=item saes_order + +=item purchase_order + +=item sales_quotation + +=item request_quotation + +=back + +=head2 is_type TYPE + +Rreturns true if the order is of the given type. + +=head1 BUGS + +Nothing here yet. + +=head1 AUTHOR + + Sven Schöling + +=cut diff --git a/SL/DB/OrderItem.pm b/SL/DB/OrderItem.pm new file mode 100644 index 000000000..94d2bc81d --- /dev/null +++ b/SL/DB/OrderItem.pm @@ -0,0 +1,25 @@ +package SL::DB::OrderItem; + +use strict; + +use SL::DB::MetaSetup::OrderItem; + +__PACKAGE__->meta->add_relationship( + part => { + type => 'one to one', + class => 'SL::DB::Part', + column_map => { parts_id => 'id' }, + } +); + +# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all. +__PACKAGE__->meta->make_manager_class; + +__PACKAGE__->meta->initialize; + +sub is_price_update_available { + my $self = shift; + return $self->origprice > $self->part->sellprice; +} + +1; diff --git a/SL/DB/Part.pm b/SL/DB/Part.pm new file mode 100644 index 000000000..6ea2be9b1 --- /dev/null +++ b/SL/DB/Part.pm @@ -0,0 +1,158 @@ +package SL::DB::Part; + +use strict; + +use Carp; +use SL::DBUtils; +use SL::DB::MetaSetup::Part; +use SL::DB::Manager::Part; + +__PACKAGE__->attr_number('lastcost', places => -2); +__PACKAGE__->attr_number('listprice', places => -2); +__PACKAGE__->attr_number('sellprice', places => -2); + +__PACKAGE__->meta->add_relationships( + unit_obj => { + type => 'one to one', + class => 'SL::DB::Unit', + column_map => { unit => 'name' }, + }, + assemblies => { + type => 'one to many', + class => 'SL::DB::Assembly', + column_map => { id => 'id' }, + }, +); + +__PACKAGE__->meta->initialize; + +sub is_type { + my $self = shift; + my $type = lc(shift || ''); + + if ($type =~ m/^part/) { + return !$self->assembly && $self->inventory_accno_id ? 1 : 0; + + } elsif ($type =~ m/^service/) { + return !$self->inventory_accno_id && !$self->assembly ? 1 : 0; + + } elsif ($type =~ m/^assembl/) { + return $self->assembly ? 1 : 0; + + } + + confess "Unknown type parameter '$type'"; +} + +sub get_sellprice_info { + my $self = shift; + my %params = @_; + + confess "Missing part id" unless $self->id; + + my $object = $self->load; + + return { sellprice => $object->sellprice, + price_factor_id => $object->price_factor_id }; +} + +sub get_ordered_qty { + my $self = shift; + my %result = SL::DB::Manager::Part->get_ordered_qty($self->id); + + return $result{ $self->id }; +} + +sub get_uncommissioned_qty { + my $self = shift; + my %params = @_; + + confess "Missing part id" unless $self->id; + + my $query = < 0)), + 0) + - + COALESCE((SELECT SUM(i.qty) AS qty + FROM inventory i + LEFT JOIN warehouse wh ON (i.warehouse_id = wh.id) + WHERE (i.parts_id = ?) + AND COALESCE(wh.commission)), + 0) + AS qty +SQL + + my $result = selectfirst_hashref_query($::form, $self->dbh, $query, $self->id, $self->id); + return $result ? $result->{qty} : 0; +} + +sub available_units { + shift->unit_obj->convertible_units; +} + +1; + +__END__ + +=pod + +=head1 NAME + +SL::DB::Part: Model for the 'parts' table + +=head1 SYNOPSIS + +This is a standard Rose::DB::Object based model and can be used as one. + +=head1 FUNCTIONS + +=over 4 + +=item is_type $type + +Tests if the current object is a part, a service or an +assembly. C<$type> must be one of the words 'part', 'service' or +'assembly' (their plurals are ok, too). + +Returns 1 if the requested type matches, 0 if it doesn't and +Ces if an unknown C<$type> parameter is encountered. + +=item get_sellprice_info %params + +Retrieves the C and C for a part under +different conditions and returns a hash reference with those two keys. + +If C<%params> contains a key C then a project price list +will be consulted if one exists for that project. In this case the +parameter C is evaluated as well: if a price list entry +has been created for this country then it will be used. Otherwise an +entry without a country set will be used. + +If none of the above conditions is met then the information from +C<$self> is used. + +=item get_ordered_qty %params + +Retrieves the quantity that has been ordered from a vendor but that +has not been delivered yet. Only open purchase orders are considered. + +=item get_uncommissioned_qty %params + +Retrieves the quantity that has been ordered by a customer but that +has not been commissioned yet. Only open sales orders are considered. + +=back + +=head1 AUTHOR + +Moritz Bunkus Em.bunkus@linet-services.deE + +=cut diff --git a/SL/DB/PriceFactor.pm b/SL/DB/PriceFactor.pm new file mode 100644 index 000000000..6680a12f3 --- /dev/null +++ b/SL/DB/PriceFactor.pm @@ -0,0 +1,31 @@ +package SL::DB::PriceFactor; + +use strict; + +use SL::DB::MetaSetup::PriceFactor; + +__PACKAGE__->meta->make_manager_class; + +1; + +__END__ + +=pod + +=head1 NAME + +SL::DB::PriceFactor: Model for the 'price_factors' table + +=head1 SYNOPSIS + +This is a standard Rose::DB::Object based model and can be used as one. + +=head1 FUNCTIONS + +None so far. + +=head1 AUTHOR + +Moritz Bunkus Em.bunkus@linet-services.deE + +=cut diff --git a/SL/DB/Printer.pm b/SL/DB/Printer.pm new file mode 100644 index 000000000..2c3eaccf8 --- /dev/null +++ b/SL/DB/Printer.pm @@ -0,0 +1,9 @@ +package SL::DB::Printer; + +use strict; + +use SL::DB::MetaSetup::Printer; + +__PACKAGE__->meta->make_manager_class; + +1; diff --git a/SL/DB/Project.pm b/SL/DB/Project.pm new file mode 100644 index 000000000..4b93c5ac7 --- /dev/null +++ b/SL/DB/Project.pm @@ -0,0 +1,31 @@ +package SL::DB::Project; + +use strict; + +use SL::DB::MetaSetup::Project; + +__PACKAGE__->meta->make_manager_class; + +1; + +__END__ + +=pod + +=head1 NAME + +SL::DB::Project: Model for the 'project' table + +=head1 SYNOPSIS + +This is a standard Rose::DB::Object based model and can be used as one. + +=head1 FUNCTIONS + +None so far. + +=head1 AUTHOR + +Moritz Bunkus Em.bunkus@linet-services.deE + +=cut diff --git a/SL/DB/PurchaseInvoice.pm b/SL/DB/PurchaseInvoice.pm new file mode 100644 index 000000000..a924b2e86 --- /dev/null +++ b/SL/DB/PurchaseInvoice.pm @@ -0,0 +1,21 @@ +package SL::DB::PurchaseInvoice; + +use strict; + +use SL::DB::MetaSetup::PurchaseInvoice; +use SL::DB::Manager::PurchaseInvoice; + +for my $field (qw(transdate gldate datepaid duedate orddate quodate)) { + __PACKAGE__->attr_date($field); +} + +__PACKAGE__->meta->add_relationship(invoiceitems => { type => 'one to many', + class => 'SL::DB::InvoiceItem', + column_map => { id => 'trans_id' }, + manager_args => { with_objects => [ 'part' ] } + }, + ); + +__PACKAGE__->meta->initialize; + +1; diff --git a/SL/DB/Shipto.pm b/SL/DB/Shipto.pm new file mode 100644 index 000000000..98b7ca139 --- /dev/null +++ b/SL/DB/Shipto.pm @@ -0,0 +1,23 @@ +package SL::DB::Shipto; + +use strict; +use Readonly; + +use SL::DB::MetaSetup::Shipto; + +Readonly our @SHIPTO_VARIABLES => qw(shiptoname shiptostreet shiptozipcode shiptocity shiptocountry shiptocontact + shiptophone shiptofax shiptoemail shiptodepartment_1 shiptodepartment_2); + +__PACKAGE__->meta->make_manager_class; + +sub displayable_id { + my $self = shift; + my $text = join('; ', grep { $_ } (map({ $self->$_ } qw(shiptoname shiptostreet)), + join(' ', grep { $_ } + map { $self->$_ } + qw(shiptozipcode shiptocity)))); + + return $text; +} + +1; diff --git a/SL/DB/TransferType.pm b/SL/DB/TransferType.pm new file mode 100644 index 000000000..9d13a865b --- /dev/null +++ b/SL/DB/TransferType.pm @@ -0,0 +1,17 @@ +# 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::TransferType; + +use strict; + +use SL::DB::MetaSetup::TransferType; +use SL::DB::Manager::TransferType; + +# methods + +sub description_t8 { + return $::locale->text(shift()->description); +} + +1; diff --git a/SL/DB/Unit.pm b/SL/DB/Unit.pm new file mode 100644 index 000000000..6a197337c --- /dev/null +++ b/SL/DB/Unit.pm @@ -0,0 +1,41 @@ +# 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::Unit; + +use strict; + +use SL::DB::MetaSetup::Unit; + +# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all. +__PACKAGE__->meta->make_manager_class; + +__PACKAGE__->meta->add_relationships( + base => { + type => 'one to one', + class => 'SL::DB::Unit', + column_map => { base_unit => 'name' }, + }, +); + +__PACKAGE__->meta->initialize; + +#methods + +sub unit_class { + my $self = shift; + + return $self if !$self->base_unit || $self->name eq $self->base_unit; + return $self->base->unit_class; +} + +sub convertible_units { + my $self = shift; + return [ + sort { $a->sortkey <=> $b->sortkey } + grep { $_->unit_class->name eq $self->unit_class->name } + @{ SL::DB::Manager::Unit->get_all } + ]; +} + +1; diff --git a/SL/DB/VC.pm b/SL/DB/VC.pm new file mode 100644 index 000000000..44b2dbb09 --- /dev/null +++ b/SL/DB/VC.pm @@ -0,0 +1,53 @@ +package SL::DB::VC; + +use strict; + +require Exporter; +use SL::DBUtils; + +our @ISA = qw(Exporter); +our @EXPORT = qw(get_credit_remaining); + +sub get_credit_remaining { + my $vc = shift; + my ($type, $arap) = ref $vc eq 'SL::DB::Customer' ? ('customer', 'ar') : ('vendor', 'ap'); + my %params = @_; + + my $credit_remaining = $vc->creditlimit || 0; + + my $query = <dbh, $query, $vc->id); + $credit_remaining -= $amount_unpaid; + + $query = < ?)|; + push @values, $params{exclude_order_id}; + } + + my $sth = prepare_execute_query($::form, $vc->dbh, $query, $vc->id, @values); + + while (my ($amount, $exch) = $sth->fetchrow_array) { + $credit_remaining -= $amount * ($exch || 1); + } + $sth->finish; + + return $credit_remaining; +} + +1; diff --git a/SL/DB/Vendor.pm b/SL/DB/Vendor.pm new file mode 100644 index 000000000..11e23fe51 --- /dev/null +++ b/SL/DB/Vendor.pm @@ -0,0 +1,27 @@ +package SL::DB::Vendor; + +use strict; + +use SL::DB::MetaSetup::Vendor; + +use SL::DB::VC; + +__PACKAGE__->meta->add_relationship( + shipto => { + type => 'one to many', + class => 'SL::DB::Shipto', + column_map => { id => 'trans_id' }, + manager_args => { sort_by => 'lower(shipto.shiptoname)' }, + query_args => [ 'shipto.module' => 'CT' ], + }, + business => { + type => 'one to one', + class => 'SL::DB::Business', + column_map => { business_id => 'id' }, + }, +); + +__PACKAGE__->meta->make_manager_class; +__PACKAGE__->meta->initialize; + +1; diff --git a/SL/DB/Warehouse.pm b/SL/DB/Warehouse.pm new file mode 100644 index 000000000..b8ddf7187 --- /dev/null +++ b/SL/DB/Warehouse.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::Warehouse; + +use strict; + +use SL::DB::MetaSetup::Warehouse; +use SL::DB::Manager::Warehouse; + +__PACKAGE__->meta->add_relationship( + bins => { + type => 'one to many', + class => 'SL::DB::Bin', + column_map => { id => 'warehouse_id' }, + } +); + +# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all. +#__PACKAGE__->meta->make_manager_class; + +__PACKAGE__->meta->initialize; + +sub first_bin { + return shift()->bins->[0]; +} + +1; diff --git a/scripts/rose_auto_create_model.pl b/scripts/rose_auto_create_model.pl new file mode 100755 index 000000000..b1a704089 --- /dev/null +++ b/scripts/rose_auto_create_model.pl @@ -0,0 +1,154 @@ +#!/usr/bin/perl + +use strict; + +BEGIN { + unshift @INC, "modules/override"; # Use our own versions of various modules (e.g. YAML). + push @INC, "modules/fallback"; # Only use our own versions of modules if there's no system version. +} + +use CGI qw( -no_xhtml); +use Data::Dumper; +use English qw( -no_match_vars ); +use List::MoreUtils qw(any); + +use SL::Auth; +use SL::DBUtils; +use SL::DB; +use SL::Form; +use SL::Locale; +use SL::LXDebug; +use SL::DB::Helpers::ALL; +use SL::DB::Helpers::Mappings; + +our $form; +our $cgi; +our $auth; + +our $script = __FILE__; +$script =~ s:.*/::; + +$OUTPUT_AUTOFLUSH = 1; +$Data::Dumper::Sortkeys = 1; + +our $meta_path = "SL/DB/MetaSetup"; + +sub setup { + if (@ARGV < 2) { + print "Usage: $PROGRAM_NAME login table1[=package1] [table2[=package2] ...]\n"; + print " or $PROGRAM_NAME login [--all|-a] [--sugar|-s]\n"; + exit 1; + } + + my $login = shift @ARGV; + + $::userspath = "users"; + $::templates = "templates"; + $::sendmail = "| /usr/sbin/sendmail -t"; + + $::lxdebug = LXDebug->new(); + + require "config/lx-erp.conf"; + require "config/lx-erp-local.conf" if -f "config/lx-erp-local.conf"; + + # locale messages + $::locale = Locale->new("de"); + $::form = new Form; + $::cgi = new CGI(''); + $::auth = SL::Auth->new(); + + $::user = User->new($login); + + %::myconfig = $auth->read_user($login); + $form->{script} = 'rose_meta_data.pl'; + $form->{login} = $login; + + map { $form->{$_} = $::myconfig{$_} } qw(stylesheet charset); + + mkdir $meta_path unless -d $meta_path; +} + +sub process_table { + my @spec = split(/=/, shift, 2); + my $table = $spec[0]; + my $package = ucfirst($spec[1] || $spec[0]); + $package =~ s/_+(.)/uc($1)/ge; + my $meta_file = "${meta_path}/${package}.pm"; + my $file = "SL/DB/${package}.pm"; + + my $definition = eval <meta->table('$table'); + __PACKAGE__->meta->auto_initialize; + + __PACKAGE__->meta->perl_class_definition(indent => 2); # , braces => 'bsd' +CODE + + if ($EVAL_ERROR) { + print STDERR "Error in execution for table '$table': $EVAL_ERROR"; + return; + } + + $definition =~ s/::AUTO::/::/g; + + my $file_exists = -f $meta_file; + + open(OUT, ">$meta_file") || die; + print OUT <$file") || die; + print OUT <meta->make_manager_class; + +1; +CODE + close OUT; + + print "File '$file' created as well.\n"; + } +} + +setup(); + +my %blacklist = SL::DB::Helpers::Mappings->get_blacklist; +my %package_names = SL::DB::Helpers::Mappings->get_package_names; + +my @tables = (); +if (($ARGV[0] eq '--all') || ($ARGV[0] eq '-a') || ($ARGV[0] eq '--sugar') || ($ARGV[0] eq '-s')) { + my ($type, $prefix) = ($ARGV[0] eq '--sugar') || ($ARGV[0] eq '-s') ? ('SUGAR', 'sugar_') : ('LXOFFICE', ''); + my $db = SL::DB::create(undef, $type); + @tables = map { $package_names{$type}->{$_} ? "${_}=" . $package_names{$type}->{$_} : $prefix ? "${_}=${prefix}${_}" : $_ } + grep { my $table = $_; !any { $_ eq $table } @{ $blacklist{$type} } } + $db->list_tables; + +} else { + @tables = @ARGV; +} + +foreach my $table (@tables) { + # add default model name unless model name is given or no defaults exists + $table .= '=' . $package_names{LXOFFICE}->{lc $table} if $table !~ /=/ && $package_names{LXOFFICE}->{lc $table}; + + process_table($table); +} diff --git a/sql/Pg-upgrade2/schema_nomalization_1.sql b/sql/Pg-upgrade2/schema_nomalization_1.sql new file mode 100644 index 000000000..2a5411808 --- /dev/null +++ b/sql/Pg-upgrade2/schema_nomalization_1.sql @@ -0,0 +1,38 @@ +-- @tag: schema_normalization_1 +-- @description: Datenbankschema Normalisierungen +-- @depends: release_2_6_1 + +-- assembly-id +CREATE SEQUENCE assembly_assembly_id_seq; +ALTER TABLE assembly ADD COLUMN assembly_id INTEGER; +UPDATE assembly SET assembly_id = nextval('assembly_assembly_id_seq'); +ALTER TABLE assembly ADD PRIMARY KEY( assembly_id ); +ALTER TABLE assembly ALTER assembly_id SET DEFAULT nextval('assembly_assembly_id_seq'); + +-- shipto_primary_key +ALTER TABLE shipto ALTER COLUMN shipto_id SET NOT NULL; +ALTER TABLE shipto ADD PRIMARY KEY (shipto_id); + +-- oe_vc_foreign_keys +--ALTER TABLE oe ADD FOREIGN KEY (customer_id) REFERENCES customer (id); +--ALTER TABLE oe ADD FOREIGN KEY (vendor_id) REFERENCES vendor (id); + +-- orderitems_primary_key +ALTER TABLE orderitems ADD PRIMARY KEY (id); + +-- part_unit_not_null +UPDATE parts SET unit = 'Stck' WHERE unit IS NULL; +ALTER TABLE parts ALTER COLUMN unit SET NOT NULL; + +-- makemodel_id_column +ALTER TABLE makemodel ADD COLUMN tmp integer; +UPDATE makemodel SET tmp = make::integer WHERE COALESCE(make, '') <> ''; +ALTER TABLE makemodel DROP COLUMN make; +ALTER TABLE makemodel RENAME COLUMN tmp TO make; + +CREATE SEQUENCE makemodel_id_seq; +ALTER TABLE makemodel ADD COLUMN id integer; +ALTER TABLE makemodel ALTER COLUMN id SET DEFAULT nextval('makemodel_id_seq'); +UPDATE makemodel SET id = nextval('makemodel_id_seq'); +ALTER TABLE makemodel ALTER COLUMN id SET NOT NULL; +ALTER TABLE makemodel ADD PRIMARY KEY (id); -- 2.20.1