projekt kivitendo

use SL::DB::RequirementSpecAcceptanceStatus;

use SL::DB::RequirementSpecComplexity;

use SL::DB::RequirementSpecDependency;

use SL::DB::RequirementSpecItem;

use SL::DB::RequirementSpecPredefinedText;

use SL::DB::RequirementSpecRisk;

use SL::DB::RequirementSpecStatus;

use SL::DB::RequirementSpecTextBlock;

use SL::DB::RequirementSpecType;

use SL::DB::RequirementSpecVersion;

use SL::DB::RequirementSpec;

  requirement_spec_acceptance_statuses => 'RequirementSpecAcceptanceStatus',

  requirement_spec_complexities        => 'RequirementSpecComplexity',

  requirement_spec_item_dependencies   => 'RequirementSpecDependency',

  requirement_spec_items               => 'RequirementSpecItem',

  requirement_spec_predefined_texts    => 'RequirementSpecPredefinedText',

  requirement_spec_risks               => 'RequirementSpecRisk',

  requirement_spec_statuses            => 'RequirementSpecStatus',

  requirement_spec_text_blocks         => 'RequirementSpecTextBlock',

  requirement_spec_types               => 'RequirementSpecType',

  requirement_spec_versions            => 'RequirementSpecVersion',

  requirement_specs                    => 'RequirementSpec',

  hourly_rate               => { type => 'numeric', precision => 2, scale => 8 },

  accounting_method                             => { type => 'text' },

  address                                       => { type => 'text' },

  ap_changeable                                 => { type => 'integer', default => 2, not_null => 1 },

  ap_show_mark_as_paid                          => { type => 'boolean', default => 'true' },

  ar_changeable                                 => { type => 'integer', default => 2, not_null => 1 },

  ar_paid_accno_id                              => { type => 'integer' },

  ar_show_mark_as_paid                          => { type => 'boolean', default => 'true' },

  articlenumber                                 => { type => 'text' },

  assemblynumber                                => { type => 'text' },

  balance_startdate_method                      => { type => 'text' },

  bin_id                                        => { type => 'integer' },

  bin_id_ignore_onhand                          => { type => 'integer' },

  businessnumber                                => { type => 'text' },

  closedto                                      => { type => 'date' },

  cnnumber                                      => { type => 'text' },

  co_ustid                                      => { type => 'text' },

  coa                                           => { type => 'text' },

  company                                       => { type => 'text' },

  currency_id                                   => { type => 'integer', not_null => 1 },

  customernumber                                => { type => 'text' },

  datev_check_on_ap_transaction                 => { type => 'boolean', default => 'true' },

  datev_check_on_ar_transaction                 => { type => 'boolean', default => 'true' },

  datev_check_on_gl_transaction                 => { type => 'boolean', default => 'true' },

  datev_check_on_purchase_invoice               => { type => 'boolean', default => 'true' },

  datev_check_on_sales_invoice                  => { type => 'boolean', default => 'true' },

  dunning_ar                                    => { type => 'integer' },

  dunning_ar_amount_fee                         => { type => 'integer' },

  dunning_ar_amount_interest                    => { type => 'integer' },

  duns                                          => { type => 'text' },

  expense_accno_id                              => { type => 'integer' },

  fxgain_accno_id                               => { type => 'integer' },

  fxloss_accno_id                               => { type => 'integer' },

  gl_changeable                                 => { type => 'integer', default => 2, not_null => 1 },

  id                                            => { type => 'serial', not_null => 1 },

  income_accno_id                               => { type => 'integer' },

  inventory_accno_id                            => { type => 'integer' },

  inventory_system                              => { type => 'text' },

  invnumber                                     => { type => 'text' },

  ir_changeable                                 => { type => 'integer', default => 2, not_null => 1 },

  ir_show_mark_as_paid                          => { type => 'boolean', default => 'true' },

  is_changeable                                 => { type => 'integer', default => 2, not_null => 1 },

  is_show_mark_as_paid                          => { type => 'boolean', default => 'true' },

  itime                                         => { type => 'timestamp', default => 'now()' },

  language_id                                   => { type => 'integer' },

  max_future_booking_interval                   => { type => 'integer', default => 360 },

  mtime                                         => { type => 'timestamp' },

  normalize_part_descriptions                   => { type => 'boolean', default => 'true' },

  normalize_vc_names                            => { type => 'boolean', default => 'true' },

  parts_image_css                               => { type => 'text', default => 'border:0;float:left;max-width:250px;margin-top:20px:margin-right:10px;margin-left:10px;' },

  parts_listing_image                           => { type => 'boolean', default => 'true' },

  parts_show_image                              => { type => 'boolean', default => 'true' },

  payments_changeable                           => { type => 'integer', default => '0', not_null => 1 },

  pdonumber                                     => { type => 'text' },

  ponumber                                      => { type => 'text' },

  profit_determination                          => { type => 'text' },

  purchase_delivery_order_show_delete           => { type => 'boolean', default => 'true' },

  purchase_order_show_delete                    => { type => 'boolean', default => 'true' },

  requirement_spec_section_number_format        => { type => 'text', default => 'A00', not_null => 1 },

  requirement_spec_function_block_number_format => { type => 'text', default => 'FB000', not_null => 1 },

  revtrans                                      => { type => 'boolean', default => 'false' },

  rfqnumber                                     => { type => 'text' },

  rmanumber                                     => { type => 'text' },

  sales_delivery_order_show_delete              => { type => 'boolean', default => 'true' },

  sales_order_show_delete                       => { type => 'boolean', default => 'true' },

  sdonumber                                     => { type => 'text' },

  sepa_creditor_id                              => { type => 'text' },

  servicenumber                                 => { type => 'text' },

  show_bestbefore                               => { type => 'boolean', default => 'false' },

  show_weight                                   => { type => 'boolean', default => 'false', not_null => 1 },

  signature                                     => { type => 'text' },

  sonumber                                      => { type => 'text' },

  sqnumber                                      => { type => 'text' },

  taxnumber                                     => { type => 'text' },

  templates                                     => { type => 'text' },

  transfer_default                              => { type => 'boolean', default => 'true' },

  transfer_default_ignore_onhand                => { type => 'boolean', default => 'false' },

  transfer_default_use_master_default_bin       => { type => 'boolean', default => 'false' },

  vendornumber                                  => { type => 'text' },

  version                                       => { type => 'varchar', length => 8 },

  vertreter                                     => { type => 'boolean', default => 'false' },

  warehouse_id                                  => { type => 'integer' },

  warehouse_id_ignore_onhand                    => { type => 'integer' },

  webdav                                        => { type => 'boolean', default => 'false' },

  webdav_documents                              => { type => 'boolean', default => 'false' },

  weightunit                                    => { type => 'varchar', length => 5 },

# This file has been auto-generated. Do not modify it; it will be overwritten

# by rose_auto_create_model.pl automatically.

package SL::DB::RequirementSpec;

use strict;

use base qw(SL::DB::Object);

__PACKAGE__->meta->setup(

  table   => 'requirement_specs',

  columns => [

    id                      => { type => 'serial', not_null => 1 },

    type_id                 => { type => 'integer', not_null => 1 },

    status_id               => { type => 'integer', not_null => 1 },

    version_id              => { type => 'integer' },

    customer_id             => { type => 'integer', not_null => 1 },

    project_id              => { type => 'integer' },

    title                   => { type => 'text', not_null => 1 },

    hourly_rate             => { type => 'numeric', default => '0', not_null => 1, precision => 2, scale => 8 },

    net_sum                 => { type => 'numeric', default => '0', not_null => 1, precision => 2, scale => 12 },

    working_copy_id         => { type => 'integer' },

    previous_section_number => { type => 'integer', not_null => 1 },

    previous_fb_number      => { type => 'integer', not_null => 1 },

    is_template             => { type => 'boolean', default => 'false' },

    itime                   => { type => 'timestamp', default => 'now()' },

    mtime                   => { type => 'timestamp' },

  ],

  primary_key_columns => [ 'id' ],

  allow_inline_column_values => 1,

  foreign_keys => [

    customer => {

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

      key_columns => { customer_id => 'id' },

    },

    project => {

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

      key_columns => { project_id => 'id' },

    },

    status => {

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

      key_columns => { status_id => 'id' },

    },

    type => {

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

      key_columns => { type_id => 'id' },

    },

    version => {

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

      key_columns => { version_id => 'id' },

    },

    working_copy => {

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

      key_columns => { working_copy_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::RequirementSpecAcceptanceStatus;

use strict;

use base qw(SL::DB::Object);

__PACKAGE__->meta->setup(

  table   => 'requirement_spec_acceptance_statuses',

  columns => [

    id          => { type => 'serial', not_null => 1 },

    name        => { type => 'text', not_null => 1 },

    description => { type => 'text' },

    position    => { type => 'integer', not_null => 1 },

    itime       => { type => 'timestamp', default => 'now()' },

    mtime       => { type => 'timestamp' },

  ],

  primary_key_columns => [ 'id' ],

  unique_key => [ 'name', 'description' ],

  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::RequirementSpecComplexity;

use strict;

use base qw(SL::DB::Object);

__PACKAGE__->meta->setup(

  table   => 'requirement_spec_complexities',

  columns => [

    id          => { type => 'serial', not_null => 1 },

    description => { type => 'text', not_null => 1 },

    position    => { type => 'integer', not_null => 1 },

    itime       => { type => 'timestamp', default => 'now()' },

    mtime       => { type => 'timestamp' },

  ],

  primary_key_columns => [ 'id' ],

  unique_key => [ 'description' ],

  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::RequirementSpecDependency;

use strict;

use base qw(SL::DB::Object);

__PACKAGE__->meta->setup(

  table   => 'requirement_spec_item_dependencies',

  columns => [

    depending_item_id => { type => 'integer', not_null => 1 },

    depended_item_id  => { type => 'integer', not_null => 1 },

  ],

  primary_key_columns => [ 'depending_item_id', 'depended_item_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::RequirementSpecItem;

use strict;

use base qw(SL::DB::Object);

__PACKAGE__->meta->setup(

  table   => 'requirement_spec_items',

  columns => [

    id                   => { type => 'serial', not_null => 1 },

    requirement_spec_id  => { type => 'integer', not_null => 1 },

    parent_id            => { type => 'integer' },

    position             => { type => 'integer', not_null => 1 },

    fb_number            => { type => 'text', not_null => 1 },

    title                => { type => 'text' },

    description          => { type => 'text' },

    complexity_id        => { type => 'integer' },

    risk_id              => { type => 'integer' },

    time_estimation      => { type => 'numeric', default => '0', not_null => 1, precision => 2, scale => 12 },

    net_sum              => { type => 'numeric', default => '0', not_null => 1, precision => 2, scale => 12 },

    is_flagged           => { type => 'boolean', default => 'false', not_null => 1 },

    acceptance_status_id => { type => 'integer' },

    acceptance_text      => { type => 'text' },

    itime                => { type => 'timestamp', default => 'now()', not_null => 1 },

    mtime                => { type => 'timestamp' },

  ],

  primary_key_columns => [ 'id' ],

  allow_inline_column_values => 1,

  foreign_keys => [

    acceptance_status => {

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

      key_columns => { acceptance_status_id => 'id' },

    },

    complexity => {

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

      key_columns => { complexity_id => 'id' },

    },

    parent => {

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

      key_columns => { parent_id => 'id' },

    },

  ],

  relationships => [

    depended_items => {

      map_class => 'SL::DB::RequirementSpecDependency',

      map_from  => 'depending_item',

      map_to    => 'depended_item',

      type      => 'many to many',

    },

    depending_items => {

      map_class => 'SL::DB::RequirementSpecDependency',

      map_from  => 'depended_item',

      map_to    => 'depending_item',

      type      => 'many to many',

    },

  ],

);

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::RequirementSpecPredefinedText;

use strict;

use base qw(SL::DB::Object);

__PACKAGE__->meta->setup(

  table   => 'requirement_spec_predefined_texts',

  columns => [

    id          => { type => 'serial', not_null => 1 },

    description => { type => 'text', not_null => 1 },

    title       => { type => 'text', not_null => 1 },

    text        => { type => 'text', not_null => 1 },

    position    => { type => 'integer', not_null => 1 },

    itime       => { type => 'timestamp', default => 'now()' },

    mtime       => { type => 'timestamp' },

  ],

  primary_key_columns => [ 'id' ],

  unique_key => [ 'description' ],

  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::RequirementSpecRisk;

use strict;

use base qw(SL::DB::Object);

__PACKAGE__->meta->setup(

  table   => 'requirement_spec_risks',

  columns => [

    id          => { type => 'serial', not_null => 1 },

    description => { type => 'text', not_null => 1 },

    position    => { type => 'integer', not_null => 1 },

    itime       => { type => 'timestamp', default => 'now()' },

    mtime       => { type => 'timestamp' },

  ],

  primary_key_columns => [ 'id' ],

  unique_key => [ 'description' ],

  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::RequirementSpecStatus;

use strict;

use base qw(SL::DB::Object);

__PACKAGE__->meta->setup(

  table   => 'requirement_spec_statuses',

  columns => [

    id          => { type => 'serial', not_null => 1 },

    name        => { type => 'text', not_null => 1 },

    description => { type => 'text', not_null => 1 },

    position    => { type => 'integer', not_null => 1 },

    itime       => { type => 'timestamp', default => 'now()' },

    mtime       => { type => 'timestamp' },

  ],

  primary_key_columns => [ 'id' ],

  unique_key => [ 'name', 'description' ],

  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::RequirementSpecTextBlock;

use strict;

use base qw(SL::DB::Object);

__PACKAGE__->meta->setup(

  table   => 'requirement_spec_text_blocks',

  columns => [

    id                  => { type => 'serial', not_null => 1 },

    requirement_spec_id => { type => 'integer', not_null => 1 },

    title               => { type => 'text', not_null => 1 },

    text                => { type => 'text' },

    position            => { type => 'integer', not_null => 1 },

    output_position     => { type => 'integer', default => 1, not_null => 1 },

    itime               => { type => 'timestamp', default => 'now()', not_null => 1 },

    mtime               => { type => 'timestamp' },

  ],

  primary_key_columns => [ 'id' ],

  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::RequirementSpecType;

use strict;

use base qw(SL::DB::Object);

__PACKAGE__->meta->setup(

  table   => 'requirement_spec_types',

  columns => [

    id          => { type => 'serial', not_null => 1 },

    description => { type => 'text', not_null => 1 },

    position    => { type => 'integer', not_null => 1 },

    itime       => { type => 'timestamp', default => 'now()' },

    mtime       => { type => 'timestamp' },

  ],

  primary_key_columns => [ 'id' ],

  unique_key => [ 'description' ],

  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::RequirementSpecVersion;

use strict;

use base qw(SL::DB::Object);

__PACKAGE__->meta->setup(

  table   => 'requirement_spec_versions',

  columns => [

    id             => { type => 'serial', not_null => 1 },

    version_number => { type => 'integer' },

    description    => { type => 'text', not_null => 1 },

    comment        => { type => 'text' },

    order_date     => { type => 'date' },

    order_number   => { type => 'text' },

    order_id       => { type => 'integer' },

    itime          => { type => 'timestamp', default => 'now()' },

    mtime          => { type => 'timestamp' },

  ],

  primary_key_columns => [ 'id' ],

  allow_inline_column_values => 1,

  foreign_keys => [

    order => {

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

      key_columns => { order_id => 'id' },

    },

  ],

);

1;

;

package SL::DB::RequirementSpec;

use strict;

use SL::DB::MetaSetup::RequirementSpec;

use SL::Locale::String;

__PACKAGE__->meta->add_relationship(

  items          => {

    type         => 'one to many',

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

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

  },

  text_blocks    => {

    type         => 'one to many',

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

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

  },

);

# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all.

__PACKAGE__->meta->make_manager_class;

__PACKAGE__->meta->initialize;

sub validate {

  my ($self) = @_;

  my @errors;

  push @errors, t8('The title is missing.') if !$self->title;

  return @errors;

}

1;

package SL::DB::RequirementSpecAcceptanceStatus;

use strict;

use SL::DB::MetaSetup::RequirementSpecAcceptanceStatus;

use SL::DB::Helper::ActsAsList;

use SL::Locale::String;

# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all.

__PACKAGE__->meta->make_manager_class;

sub validate {

  my ($self) = @_;

  my @errors;

  push @errors, t8('The description is missing.') if !$self->description;

  return @errors;

}

1;

package SL::DB::RequirementSpecComplexity;

use strict;

use SL::DB::MetaSetup::RequirementSpecComplexity;

use SL::DB::Helper::ActsAsList;

use SL::Locale::String;

# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all.

__PACKAGE__->meta->make_manager_class;

sub validate {

  my ($self) = @_;

  my @errors;

  push @errors, t8('The description is missing.') if !$self->description;

  return @errors;

}

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::RequirementSpecDependency;

use strict;

use SL::DB::MetaSetup::RequirementSpecDependency;

# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all.

__PACKAGE__->meta->make_manager_class;

1;

package SL::DB::RequirementSpecItem;

use strict;

use SL::DB::MetaSetup::RequirementSpecItem;

# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all.

__PACKAGE__->meta->make_manager_class;

1;

package SL::DB::RequirementSpecPredefinedText;

use strict;

use SL::DB::MetaSetup::RequirementSpecPredefinedText;

use SL::DB::Helper::ActsAsList;

use SL::Locale::String;

# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all.

__PACKAGE__->meta->make_manager_class;

sub validate {

  my ($self) = @_;

  my @errors;

  push @errors, t8('The title is missing.')       if !$self->title;

  push @errors, t8('The description is missing.') if !$self->description;

  return @errors;

}

1;

package SL::DB::RequirementSpecRisk;

use strict;

use SL::DB::MetaSetup::RequirementSpecRisk;

use SL::DB::Helper::ActsAsList;

use SL::Locale::String;

# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all.

__PACKAGE__->meta->make_manager_class;

sub validate {

  my ($self) = @_;

  my @errors;

  push @errors, t8('The description is missing.') if !$self->description;

  return @errors;

}

1;

package SL::DB::RequirementSpecStatus;

use strict;

use SL::DB::MetaSetup::RequirementSpecStatus;

use SL::DB::Helper::ActsAsList;

use SL::Locale::String;

# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all.

__PACKAGE__->meta->make_manager_class;

sub validate {

  my ($self) = @_;

  my @errors;

  push @errors, t8('The name is missing.')        if !$self->name;

  push @errors, t8('The description is missing.') if !$self->description;

  return @errors;

}

1;

package SL::DB::RequirementSpecTextBlock;

use strict;

use SL::DB::MetaSetup::RequirementSpecTextBlock;

# ActsAsList does not support position arguments grouped by other

# columns, e.g. by the requirement_spec_id in this case. So we cannot

# use it yet.

# use SL::DB::Helper::ActsAsList;

use SL::Locale::String;

# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all.

__PACKAGE__->meta->make_manager_class;

sub validate {

  my ($self) = @_;

  my @errors;

  push @errors, t8('The title is missing.') if !$self->title;

  return @errors;

}

1;

package SL::DB::RequirementSpecType;

use strict;

use SL::DB::MetaSetup::RequirementSpecType;

use SL::DB::Helper::ActsAsList;

use SL::Locale::String;

# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all.

__PACKAGE__->meta->make_manager_class;

sub validate {

  my ($self) = @_;

  my @errors;

  push @errors, t8('The description is missing.') if !$self->description;

  return @errors;

}

1;

package SL::DB::RequirementSpecVersion;

use strict;

use SL::DB::MetaSetup::RequirementSpecVersion;

use SL::Locale::String;

# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all.

__PACKAGE__->meta->make_manager_class;

sub validate {

  my ($self) = @_;

  my @errors;

  push @errors, t8('The version number is missing.') if !$self->version_number;

  push @errors, t8('The description is missing.')    if !$self->description;

  return @errors;

}

1;

  'The title is missing.'       => 'Der Titel fehlt.',

  'The version number is missing.' => 'Die Versionsnummer fehlt.',

-- @tag: requirement_specs

-- @description: Pflichtenhefte

-- @depends: release_3_0_0

-- @charset: utf-8

-- Nur für Entwicklungszwecke:

-- DELETE FROM schema_info WHERE tag = 'requirement_specs';

-- BEGIN;

-- DROP TABLE requirement_spec_item_dependencies;

-- DROP TABLE requirement_spec_items;

-- DROP TABLE requirement_spec_text_blocks;

-- DROP TABLE requirement_specs;

-- DROP TABLE requirement_spec_versions;

-- DROP TABLE requirement_spec_predefined_texts;

-- DROP TABLE requirement_spec_types;

-- DROP TABLE requirement_spec_statuses;

-- DROP TABLE requirement_spec_risks;

-- DROP TABLE requirement_spec_complexities;

-- DROP TABLE requirement_spec_acceptance_statuses;

-- ALTER TABLE customer DROP COLUMN hourly_rate;

-- ALTER TABLE defaults DROP COLUMN requirement_spec_section_number_format;

-- ALTER TABLE defaults DROP COLUMN requirement_spec_function_block_number_format;

CREATE TABLE requirement_spec_acceptance_statuses (

       id          SERIAL,

       name        TEXT      NOT NULL,

       description TEXT,

       position    INTEGER   NOT NULL,

       itime       TIMESTAMP DEFAULT now(),

       mtime       TIMESTAMP,

       PRIMARY KEY (id),

       UNIQUE (name, description)

);

CREATE TRIGGER mtime_requirement_spec_acceptance_statuses BEFORE UPDATE ON requirement_spec_acceptance_statuses FOR EACH ROW EXECUTE PROCEDURE set_mtime();

INSERT INTO requirement_spec_acceptance_statuses (name, description, position) VALUES ('accepted',                          'Abgenommen',                                1);

INSERT INTO requirement_spec_acceptance_statuses (name, description, position) VALUES ('accepted_with_defects',             'Mit Mängeln abgenommen',                    2);

INSERT INTO requirement_spec_acceptance_statuses (name, description, position) VALUES ('accepted_with_defects_to_be_fixed', 'Mit noch zu behebenden Mängeln abgenommen', 3);

INSERT INTO requirement_spec_acceptance_statuses (name, description, position) VALUES ('not_accepted',                      'Nicht abgenommen',                          4);

CREATE TABLE requirement_spec_complexities (

       id          SERIAL,

       description TEXT      NOT NULL,

       position    INTEGER   NOT NULL,

       itime       TIMESTAMP DEFAULT now(),

       mtime       TIMESTAMP,

       PRIMARY KEY (id),

       UNIQUE (description)

);

CREATE TRIGGER mtime_requirement_spec_complexities BEFORE UPDATE ON requirement_spec_complexities FOR EACH ROW EXECUTE PROCEDURE set_mtime();

INSERT INTO requirement_spec_complexities (description, position) VALUES ('nicht bewertet',  1);

INSERT INTO requirement_spec_complexities (description, position) VALUES ('nur Anforderung', 2);

INSERT INTO requirement_spec_complexities (description, position) VALUES ('gering',          3);

INSERT INTO requirement_spec_complexities (description, position) VALUES ('mittel',          4);

INSERT INTO requirement_spec_complexities (description, position) VALUES ('hoch',            5);

CREATE TABLE requirement_spec_risks (

       id          SERIAL,

       description TEXT      NOT NULL,

       position    INTEGER   NOT NULL,

       itime       TIMESTAMP DEFAULT now(),

       mtime       TIMESTAMP,

       PRIMARY KEY (id),

       UNIQUE (description)

);

CREATE TRIGGER mtime_requirement_spec_risks BEFORE UPDATE ON requirement_spec_risks FOR EACH ROW EXECUTE PROCEDURE set_mtime();

INSERT INTO requirement_spec_risks (description, position) VALUES ('nicht bewertet',  1);

INSERT INTO requirement_spec_risks (description, position) VALUES ('nur Anforderung', 2);

INSERT INTO requirement_spec_risks (description, position) VALUES ('gering',          3);

INSERT INTO requirement_spec_risks (description, position) VALUES ('mittel',          4);

INSERT INTO requirement_spec_risks (description, position) VALUES ('hoch',            5);

CREATE TABLE requirement_spec_statuses (

       id          SERIAL,

       name        TEXT      NOT NULL,

       description TEXT      NOT NULL,

       position    INTEGER   NOT NULL,

       itime       TIMESTAMP DEFAULT now(),

       mtime       TIMESTAMP,

       PRIMARY KEY (id),

       UNIQUE (name, description)

);

CREATE TRIGGER mtime_requirement_spec_statuses BEFORE UPDATE ON requirement_spec_statuses FOR EACH ROW EXECUTE PROCEDURE set_mtime();

INSERT INTO requirement_spec_statuses (name, description, position) VALUES ('planning', 'In Planung',     1);

INSERT INTO requirement_spec_statuses (name, description, position) VALUES ('running',  'In Bearbeitung', 2);

INSERT INTO requirement_spec_statuses (name, description, position) VALUES ('done',     'Fertiggestellt', 3);

CREATE TABLE requirement_spec_types (

       id          SERIAL,

       description TEXT      NOT NULL,

       position    INTEGER   NOT NULL,

       itime       TIMESTAMP DEFAULT now(),

       mtime       TIMESTAMP,

       PRIMARY KEY (id),

       UNIQUE (description)

);

CREATE TRIGGER mtime_requirement_spec_types BEFORE UPDATE ON requirement_spec_types FOR EACH ROW EXECUTE PROCEDURE set_mtime();

INSERT INTO requirement_spec_types (description, position) VALUES ('Pflichtenheft', 1);

INSERT INTO requirement_spec_types (description, position) VALUES ('Konzept',       2);

CREATE TABLE requirement_spec_predefined_texts (

       id          SERIAL,

       description TEXT      NOT NULL,

       title       TEXT      NOT NULL,

       text        TEXT      NOT NULL,

       position    INTEGER   NOT NULL,

       itime       TIMESTAMP DEFAULT now(),

       mtime       TIMESTAMP,

       PRIMARY KEY (id),

       UNIQUE (description)

);

CREATE TRIGGER mtime_requirement_spec_predefined_texts BEFORE UPDATE ON requirement_spec_predefined_texts FOR EACH ROW EXECUTE PROCEDURE set_mtime();

CREATE TABLE requirement_spec_versions (

       id             SERIAL,

       version_number INTEGER,

       description    TEXT NOT NULL,

       comment        TEXT,

       order_date     DATE,

       order_number   TEXT,

       order_id       INTEGER,

       itime          TIMESTAMP DEFAULT now(),

       mtime          TIMESTAMP,

       PRIMARY KEY (id),

       FOREIGN KEY (order_id) REFERENCES oe (id)

);

CREATE TRIGGER mtime_requirement_spec_versions BEFORE UPDATE ON requirement_spec_versions FOR EACH ROW EXECUTE PROCEDURE set_mtime();

CREATE TABLE requirement_specs (

       id                      SERIAL,

       type_id                 INTEGER        NOT NULL,

       status_id               INTEGER        NOT NULL,

       version_id              INTEGER,

       customer_id             INTEGER        NOT NULL,

       project_id              INTEGER,

       title                   TEXT           NOT NULL,

       hourly_rate             NUMERIC(8, 2)  NOT NULL DEFAULT 0,

       net_sum                 NUMERIC(12, 2) NOT NULL DEFAULT 0,

       working_copy_id         INTEGER,

       previous_section_number INTEGER        NOT NULL,

       previous_fb_number      INTEGER        NOT NULL,

       is_template             BOOLEAN                 DEFAULT FALSE,

       itime                   TIMESTAMP               DEFAULT now(),

       mtime                   TIMESTAMP,

       PRIMARY KEY (id),

       FOREIGN KEY (type_id)         REFERENCES requirement_spec_types    (id),

       FOREIGN KEY (status_id)       REFERENCES requirement_spec_statuses (id),

       FOREIGN KEY (version_id)      REFERENCES requirement_spec_versions (id),

       FOREIGN KEY (working_copy_id) REFERENCES requirement_specs         (id),

       FOREIGN KEY (customer_id)     REFERENCES customer                  (id),

       FOREIGN KEY (project_id)      REFERENCES project                   (id)

);

CREATE TRIGGER mtime_requirement_specs BEFORE UPDATE ON requirement_specs FOR EACH ROW EXECUTE PROCEDURE set_mtime();

CREATE TABLE requirement_spec_text_blocks (

       id                  SERIAL,

       requirement_spec_id INTEGER   NOT NULL,

       title               TEXT      NOT NULL,

       text                TEXT,

       position            INTEGER   NOT NULL,

       output_position     INTEGER   NOT NULL DEFAULT 1,

       itime               TIMESTAMP NOT NULL DEFAULT now(),

       mtime               TIMESTAMP,

       PRIMARY KEY (id),

       FOREIGN KEY (requirement_spec_id) REFERENCES requirement_specs (id)

);

CREATE TRIGGER mtime_requirement_spec_text_blocks BEFORE UPDATE ON requirement_spec_text_blocks FOR EACH ROW EXECUTE PROCEDURE set_mtime();

CREATE TABLE requirement_spec_items (

       id                   SERIAL,

       requirement_spec_id  INTEGER        NOT NULL,

       item_type            TEXT           NOT NULL,

       parent_id            INTEGER,

       position             INTEGER        NOT NULL,

       fb_number            TEXT           NOT NULL,

       title                TEXT,

       description          TEXT,

       complexity_id        INTEGER,

       risk_id              INTEGER,

       time_estimation      NUMERIC(12, 2) NOT NULL DEFAULT 0,

       net_sum              NUMERIC(12, 2) NOT NULL DEFAULT 0,

       is_flagged           BOOLEAN        NOT NULL DEFAULT FALSE,

       acceptance_status_id INTEGER,

       acceptance_text      TEXT,

       itime                TIMESTAMP      NOT NULL DEFAULT now(),

       mtime                TIMESTAMP,

       PRIMARY KEY (id),

       FOREIGN KEY (requirement_spec_id)  REFERENCES requirement_specs (id),

       FOREIGN KEY (parent_id)            REFERENCES requirement_spec_items (id),

       FOREIGN KEY (complexity_id)        REFERENCES requirement_spec_complexities (id),

       FOREIGN KEY (risk_id)              REFERENCES requirement_spec_risks (id),

       FOREIGN KEY (acceptance_status_id) REFERENCES requirement_spec_acceptance_statuses (id),

       CONSTRAINT valid_item_type CHECK ((item_type = 'section') OR (item_type = 'function-block') OR (item_type = 'sub-function-block')),

       CONSTRAINT valid_parent_id_for_item_type CHECK (CASE

         WHEN (item_type = 'section') THEN parent_id IS NULL

         ELSE                              parent_id IS NOT NULL

       END)

);

CREATE TRIGGER mtime_requirement_spec_items BEFORE UPDATE ON requirement_spec_items FOR EACH ROW EXECUTE PROCEDURE set_mtime();

CREATE TABLE requirement_spec_item_dependencies (

       depending_item_id INTEGER NOT NULL,

       depended_item_id  INTEGER NOT NULL,

       PRIMARY KEY (depending_item_id, depended_item_id),

       FOREIGN KEY (depending_item_id) REFERENCES requirement_spec_items (id),

       FOREIGN KEY (depended_item_id)  REFERENCES requirement_spec_items (id)

);

ALTER TABLE customer ADD COLUMN hourly_rate NUMERIC(8, 2);

-- Trigger for updating time_estimation of function blocks from their

-- children (not for sections, not for sub function blocks).

CREATE OR REPLACE FUNCTION update_requirement_spec_item_time_estimation(item_id INTEGER) RETURNS BOOLEAN AS $$

  DECLARE

    item   RECORD;

    parent RECORD;

  BEGIN

    IF item_id IS NULL THEN

      RAISE DEBUG 'updateRSIE: item_id IS NULL';

      RETURN FALSE;

    END IF;

    SELECT * INTO item FROM requirement_spec_items WHERE id = item_id;

    RAISE DEBUG 'updateRSIE: item_id % parent_id %', item_id, item.parent_id;

    IF item.parent_id IS NULL THEN

      -- Don't do anything for sections.

      RAISE DEBUG 'updateRSIE: this is a section.';

      RETURN FALSE;

    END IF;

    SELECT * INTO parent FROM requirement_spec_items WHERE id = item.parent_id;

    RAISE DEBUG 'updateRSIE: parent_id of parent of item: %', parent.parent_id;

    IF parent.parent_id IS NOT NULL THEN

      -- Don't do anything for sub function blocks.

      RAISE DEBUG 'updateRSIE: this is sub function block.';

      RETURN FALSE;

    END IF;

    RAISE DEBUG 'updateRSIE: will do stuff now';

    UPDATE requirement_spec_items

      SET time_estimation = COALESCE((

        SELECT SUM(time_estimation)

        FROM requirement_spec_items

        WHERE parent_id = item_id

      ), 0)

      WHERE id = item_id;

    RETURN TRUE;

  END;

$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION requirement_spec_item_time_estimation_updater_trigger() RETURNS trigger AS $$

  BEGIN

    IF ((TG_OP = 'UPDATE') OR (TG_OP = 'DELETE')) THEN

      PERFORM update_requirement_spec_item_time_estimation(OLD.parent_id);

    END IF;

    IF ((TG_OP = 'UPDATE') OR (TG_OP = 'INSERT')) THEN

      PERFORM update_requirement_spec_item_time_estimation(NEW.parent_id);

    END IF;

    RETURN NULL;

  END;

$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS update_requirement_spec_item_time_estimation ON requirement_spec_items;

CREATE TRIGGER update_requirement_spec_item_time_estimation

AFTER INSERT OR UPDATE OR DELETE ON requirement_spec_items

FOR EACH ROW EXECUTE PROCEDURE requirement_spec_item_time_estimation_updater_trigger();

-- Trigger for deleting depending stuff if a requirement spec item is deleted.

CREATE OR REPLACE FUNCTION requirement_spec_item_before_delete_trigger() RETURNS trigger AS $$

  BEGIN

    RAISE DEBUG 'delete trig RSitem old id %', OLD.id;

    DELETE FROM requirement_spec_item_dependencies WHERE (depending_item_id = OLD.id) OR (depended_item_id = OLD.id);

    DELETE FROM requirement_spec_items             WHERE (parent_id         = OLD.id);

    RETURN OLD;

  END;

$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS delete_requirement_spec_item_dependencies ON requirement_spec_items;

CREATE TRIGGER delete_requirement_spec_item_dependencies

BEFORE DELETE ON requirement_spec_items

FOR EACH ROW EXECUTE PROCEDURE requirement_spec_item_before_delete_trigger();

-- Trigger for deleting depending stuff if a requirement spec is deleted.

CREATE OR REPLACE FUNCTION requirement_spec_delete_trigger() RETURNS trigger AS $$

  DECLARE

    tname TEXT;

  BEGIN

    tname := 'tmp_delete_reqspec' || OLD.id;

    IF TG_WHEN = 'AFTER' THEN

      RAISE DEBUG 'after trigger on %; deleting from versions', OLD.id;

      EXECUTE 'DELETE FROM requirement_spec_versions ' ||

              'WHERE id IN (SELECT version_id FROM ' || tname || ')';

      RAISE DEBUG '  dropping table';

      EXECUTE 'DROP TABLE ' || tname;

      RETURN OLD;

    END IF;

    RAISE DEBUG 'before delete trigger on %', OLD.id;

    EXECUTE 'CREATE TEMPORARY TABLE ' || tname || ' AS ' ||

      'SELECT DISTINCT version_id '     ||

      'FROM requirement_specs '         ||

      'WHERE (version_id IS NOT NULL) ' ||

      '  AND ((id = ' || OLD.id || ') OR (working_copy_id = ' || OLD.id || '))';

    RAISE DEBUG '  Updating version_id and items for %', OLD.id;

    UPDATE requirement_specs      SET version_id = NULL                        WHERE (id <> OLD.id) AND (working_copy_id = OLD.id);

    UPDATE requirement_spec_items SET item_type  = 'section', parent_id = NULL WHERE requirement_spec_id = OLD.id;

    RAISE DEBUG '  Deleting stuff for %', OLD.id;

    DELETE FROM requirement_spec_text_blocks WHERE (requirement_spec_id = OLD.id);

    DELETE FROM requirement_spec_items       WHERE (requirement_spec_id = OLD.id);

    DELETE FROM requirement_specs            WHERE (working_copy_id     = OLD.id);

    RAISE DEBUG '  And we out for %', OLD.id;

    RETURN OLD;

  END;

$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS delete_requirement_spec_dependencies ON requirement_specs;

CREATE TRIGGER delete_requirement_spec_dependencies

BEFORE DELETE ON requirement_specs

FOR EACH ROW EXECUTE PROCEDURE requirement_spec_delete_trigger();

DROP TRIGGER IF EXISTS after_delete_requirement_spec_dependencies ON requirement_specs;

CREATE TRIGGER after_delete_requirement_spec_dependencies

AFTER DELETE ON requirement_specs

FOR EACH ROW EXECUTE PROCEDURE requirement_spec_delete_trigger();

-- Add formats for section/function block numbers to defaults

ALTER TABLE defaults ADD   COLUMN requirement_spec_section_number_format        TEXT;

ALTER TABLE defaults ALTER COLUMN requirement_spec_section_number_format        SET DEFAULT 'A00';

ALTER TABLE defaults ADD   COLUMN requirement_spec_function_block_number_format TEXT;

ALTER TABLE defaults ALTER COLUMN requirement_spec_function_block_number_format SET DEFAULT 'FB000';

UPDATE defaults SET requirement_spec_section_number_format        = 'A00';

UPDATE defaults SET requirement_spec_function_block_number_format = 'FB000';

ALTER TABLE defaults ALTER COLUMN requirement_spec_section_number_format        SET NOT NULL;

ALTER TABLE defaults ALTER COLUMN requirement_spec_function_block_number_format SET NOT NULL;