[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[4558] addons/1.3
- Subject: SF.net SVN: ledger-smb:[4558] addons/1.3
- From: ..hidden..
- Date: Sun, 25 Mar 2012 05:09:06 +0000
Revision: 4558
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=4558&view=rev
Author: einhverfr
Date: 2012-03-25 05:09:05 +0000 (Sun, 25 Mar 2012)
Log Message:
-----------
Moving over all business unit classes, etc. as add-on. Next for patches.
Added Paths:
-----------
addons/1.3/b_units/
addons/1.3/b_units/trunk/
addons/1.3/b_units/trunk/LedgerSMB/
addons/1.3/b_units/trunk/LedgerSMB/DBObject/
addons/1.3/b_units/trunk/LedgerSMB/DBObject/Business_Unit.pm
addons/1.3/b_units/trunk/LedgerSMB/DBObject/Business_Unit_Class.pm
addons/1.3/b_units/trunk/LedgerSMB/DBObject_Moose.pm
addons/1.3/b_units/trunk/business_unit.pl
addons/1.3/b_units/trunk/scripts/
addons/1.3/b_units/trunk/scripts/business_unit.pl
addons/1.3/b_units/trunk/sql/
addons/1.3/b_units/trunk/sql/modules/
addons/1.3/b_units/trunk/sql/modules/Business_Unit-roles.sql
addons/1.3/b_units/trunk/sql/modules/Business_Unit-tables.sql
addons/1.3/b_units/trunk/sql/modules/Business_Unit.sql
Copied: addons/1.3/b_units/trunk/LedgerSMB/DBObject/Business_Unit.pm (from rev 4557, trunk/LedgerSMB/DBObject/Business_Unit.pm)
===================================================================
--- addons/1.3/b_units/trunk/LedgerSMB/DBObject/Business_Unit.pm (rev 0)
+++ addons/1.3/b_units/trunk/LedgerSMB/DBObject/Business_Unit.pm 2012-03-25 05:09:05 UTC (rev 4558)
@@ -0,0 +1,199 @@
+=head1 NAME
+
+LedgerSMB::DBObject::Business_Unit_Class
+
+=head1 SYNOPSYS
+
+This holds the information as to the handling of classes of buisness units.
+Business units are reporting units which can be used to classify various line
+items of transactions in different ways and include handling for departments,
+funds, and projects.
+
+=cut
+
+package LedgerSMB::DBObject::Business_Unit;
+use Moose;
+use LedgerSMB::DBObject_Moose;
+extends 'LedgerSMB::DBObject_Moose';
+
+=head1 PROPERTIES
+
+=over
+
+=item id
+
+This is the internal id of the unit class. It is undef when the class has not
+yet been saved in the database
+
+=cut
+
+has 'id' => (is => 'rw', isa => 'Maybe[Int]');
+
+=item class_id
+
+Required. Internal id of class (1 for department, 2 for project, etc)
+
+=cut
+
+has 'class_id' => (is => 'ro', isa => 'Int', required => '1');
+
+=item control_code
+
+This is a textual reference to the business reporting unit. It must be unique
+to the business units of its class.
+
+=cut
+
+has 'control_code' => (is => 'ro', isa => 'Str', required => '1');
+
+=item description
+
+Textual description of the reporting unit.
+
+=cut
+
+has 'description' => (is => 'rw', isa => 'Maybe[Str]');
+
+=item start_date
+
+The first date the business reporting unit is valid. We use the PGDate class
+here for conversion to/from input and to/from strings for the db.
+
+=cut
+
+has 'start_date' => (is => 'rw', isa => 'Maybe[LedgerSMB::PGDate]');
+
+=item end_date
+
+The last date the business reporting unit is valid. We use the PGDate class
+here for conversion to/from input and to/from strings for the db.
+
+=cut
+
+has 'end_date' => (is => 'rw', isa => 'Maybe[LedgerSMB::PGDate]');
+
+=item parent_id
+
+The internal id of the parent, if applicable. undef means no parent.
+
+=cut
+
+has 'parent_id' => (is => 'rw', isa => 'Maybe[Int]');
+
+=item parent
+
+A reference to the parent business reporting unit
+
+=cut
+
+has 'parent' => (is => 'rw', isa => 'Maybe[LedgerSMB::DBObject::Business_Unit]');
+
+=item credit_id
+
+The internal id of the customer, vendor, employee, etc. attached to this
+unit.
+
+=cut
+
+has 'credit_id' => (is => 'rw', isa => 'Maybe[Int]');
+
+=item children
+
+The children of the current unit, if applicable, and desired.
+
+This is not set unless get_tree has already been called.
+
+=back
+
+=cut
+
+has 'children' => (is => 'rw', isa => 'Maybe[ArrayRef[LedgerSMB::DBObject::Business_Unit]]');
+
+=head1 METHODS
+
+=over
+
+=item get($id)
+
+Returns the business reporting unit referenced by the id.
+
+=cut
+
+sub get {
+ my ($self, $id) = @_;
+ my ($unit) = $self->call_procedure(procname => 'business_unit__get',
+ args => [$id]
+ );
+ $self->prepare_dbhash($unit);
+ return $self->new(%$unit);
+}
+
+=item save
+
+Saves the business reporting unit ot the database and updates changes to object.
+
+=cut
+
+sub save {
+ my ($self) = @_;
+ my ($ref) = $self->exec_method({funcname => 'business_unit__save'});
+ $self->prepare_dbhash($ref);
+ $self = $self->new($ref);
+ $self->dbh->commit;
+}
+
+=item list ($date, $class_id, $credit_id, $strict, $active_on)
+
+Lists all business reporting units active on $date, for $credit_id (or for all
+credit_ids), and of $class. Undef on date and credit_id match all rows.
+
+=cut
+
+sub list {
+ my ($self, $class_id, $credit_id, $strict, $active_on) = @_;
+ my @rows = $self->call_procedure(procname => 'business_unit__list_by_class',
+ args => [$class_id, $active_on,
+ $credit_id, $strict]);
+ for my $row(@rows){
+ $self->prepare_dbhash($row);
+ $row = $self->new($row);
+ }
+ return @rows;
+}
+
+=item delete
+
+Deletes the buisness reporting unit. A unit can only be deleted if it has no
+children and no transactions attached.
+
+=cut
+
+sub delete {
+ my ($self) = @_;
+ my ($ref) = $self->exec_method({funcname => 'business_unit__delete'});
+}
+
+=item search
+
+Returns a list of buisness reporting units matching search criteria.
+
+=item get_tree
+
+Retrieves children recursively from the database and populates children
+appropriately
+
+=item tree_to_list
+
+Returns tree as a list.
+
+=back
+
+=head1 COPYRIGHT
+
+Copyright (C) 2012 The LedgerSMB Core Team. This module may be used under the
+GNU GPL in accordance with the LICENSE file listed.
+
+=cut
+
+__PACKAGE__->meta->make_immutable;
+1;
Copied: addons/1.3/b_units/trunk/LedgerSMB/DBObject/Business_Unit_Class.pm (from rev 4557, trunk/LedgerSMB/DBObject/Business_Unit_Class.pm)
===================================================================
--- addons/1.3/b_units/trunk/LedgerSMB/DBObject/Business_Unit_Class.pm (rev 0)
+++ addons/1.3/b_units/trunk/LedgerSMB/DBObject/Business_Unit_Class.pm 2012-03-25 05:09:05 UTC (rev 4558)
@@ -0,0 +1,203 @@
+=head1 NAME
+
+LedgerSMB::DBObject::Business_Unit_Class
+
+=head1 SYNOPSYS
+
+This holds the information as to the handling of classes of buisness units.
+Business units are reporting units which can be used to classify various line
+items of transactions in different ways and include handling for departments,
+funds, and projects.
+
+=cut
+
+package LedgerSMB::DBObject::Business_Unit_Class;
+use Moose;
+use LedgerSMB::DBObject_Moose;
+use LedgerSMB::DBObject::App_Module;
+extends 'LedgerSMB::DBObject_Moose';
+
+=head1 PROPERTIES
+
+=over
+
+=item id
+
+This is the internal id of the unit class. It is undef when the class has not
+yet been saved in the database
+
+=cut
+
+has 'id' => (is => 'rw', isa => 'Maybe[Int]');
+
+=item label
+
+This is the human-readible label for the class. It must be unique among
+classes.
+
+=cut
+
+has 'label' => (is => 'rw', isa => 'Str');
+
+=item active bool
+
+If true, indicates that this will show up on screens. If not, it will be
+hidden.
+
+=cut
+
+has 'active' => (is => 'rw', isa => 'Bool');
+
+# Hmm should we move this to per-module restrictions? --CT
+
+=item modules bool
+
+If true, indicates that this will not show up on accounting transaction screens.
+this is indivated for CRM and other applications.
+
+=cut
+
+has 'modules' => (is => 'rw',
+ isa => 'ArrayRef[LedgerSMB::DBObject::App_Module]'
+);
+
+=item ordering
+
+The entry boxes (drop down or text entry) are set arranged from low to high
+by this field on the data entry screens.
+
+=cut
+
+has 'ordering' => (is => 'rw', isa => 'Int');
+
+=back
+
+=head1 METHODS
+
+=over
+
+=item get($id)
+
+returns the business unit class that corresponds to the id requested.
+
+=cut
+
+sub get {
+ my ($self, $id) = @_;
+ my @classes = $self->call_procedure(procname => 'business_unit_class__get',
+ args => [$id]
+ );
+ my $ref = shift @classes;
+ my @modules = $self->call_procedure(procname => 'business_unit_class__get_modules',
+ args => [$id]
+ );
+ $self->prepare_dbhash($ref);
+ my $class = $self->new(shift @classes);
+ $class->modules(..hidden..);
+}
+
+=item save
+
+Saves the existing buisness unit class to the database, and updates any fields
+changed in the process.
+
+=cut
+
+sub save {
+ my ($self) = @_;
+ my ($ref) = $self->exec_method({funcname => 'business_unit_class__save'});
+ $self->save_modules();
+ $self->prepare_dbhash($ref);
+ $self = $self->new(%$ref);
+ $self->dbh->commit;
+}
+
+=item save_modules
+
+This saves only the module permissions. This takes the list of modules and prepares an array for the saving and then saves the modules. This is broken off as a public
+interface because it makes it possible to activate/deactive regarding modules after the
+fact without changing anything else.
+
+=cut
+
+sub save_modules {
+ my ($self) = @_;
+ my $mod_ids = [];
+ for my $mod (@{$self->modules}){
+ push @$mod_ids, $mod->id;
+ }
+ $self->call_procedure(procname => 'business_unit_class__save_modules',
+ args => [$self->id, $mod_ids]
+ );
+}
+
+=item list
+
+Returns a list of all business unit classes.
+
+=cut
+
+sub list {
+ my ($self) = @_;
+ my @classes = $self->exec_method({funcname => 'business_unit__list_classes'});
+ for my $class (@classes){
+ $self->prepare_dbhash($class);
+ $class = $self->new(%$class);
+ my @modules = $self->call_procedure(procname => 'business_unit_class__get_modules',
+ args => [$class->id]
+ );
+ for my $m (@modules){
+ $self->prepare_dbhash($m);
+ $m = LedgerSMB::DBObject::App_Module->new($m);
+ }
+ $class->modules(..hidden..);
+ }
+ return @classes;
+}
+
+=item delete
+
+Deletes a business unit class. Such classes may not have business units attached.
+
+=cut
+
+sub delete {
+ my ($self) = @_;
+ my ($ref) = $self->exec_method({funcname => 'business_unit_class__delete'});
+}
+
+=back
+
+=head1 PREDEFINED CLASSES
+
+=over
+
+=item Department, ID: 1
+
+=item Project, ID: 2
+
+=item Job, ID: 3
+
+Used for manufacturing lots
+
+=item Fund, ID: 4
+
+Used by non-profits for funds accounting
+
+=item Customer, ID 5
+
+Used in some countries/industries for multi-customer receipts
+
+=item Vendor, ID 6
+
+Used in some countries/industries for multi-vendor payments
+
+=head1 COPYRIGHT
+
+Copyright (C) 2012 The LedgerSMB Core Team. This module may be used under the
+GNU GPL in accordance with the LICENSE file listed.
+
+=cut
+
+__PACKAGE__->meta->make_immutable;
+1;
Copied: addons/1.3/b_units/trunk/LedgerSMB/DBObject_Moose.pm (from rev 4557, trunk/LedgerSMB/DBObject_Moose.pm)
===================================================================
--- addons/1.3/b_units/trunk/LedgerSMB/DBObject_Moose.pm (rev 0)
+++ addons/1.3/b_units/trunk/LedgerSMB/DBObject_Moose.pm 2012-03-25 05:09:05 UTC (rev 4558)
@@ -0,0 +1,129 @@
+
+
+=head1 NAME
+
+LedgerSMB::DBObject - LedgerSMB class for building objects from db relations
+
+=head1 SYOPSIS
+
+This module creates object instances based on LedgerSMB's in-database ORM.
+
+=head1 METHODS
+
+=over
+
+=item new ($class, base => $LedgerSMB::hash)
+
+This is the base constructor for all child classes. It must be used with base
+argument because this is necessary for database connectivity and the like.
+
+Of course the base object can be any object that inherits LedgerSMB, so you can
+use any subclass of that. The per-session dbh is passed between the objects
+this way as is any information that is needed.
+
+=item exec_method
+
+($self, procname => $function_name, [args => ..hidden.., schema => $schema,
+continue_on_error=>$continue_on_error])
+
+Provides the basic mapping of parameters to the SQL stored procedure function
+arguments.
+
+If ..hidden.. is not defined, args are mapped from the object's properties,
+stripping them of their in_ prefix. If schema is provided, that is used
+instead of PostgreSQL's search path. If continue_on_error is provided and true,
+the operation will not raise an exception in the event of a database error, and
+it will be up to the application to handle any exceptions.
+
+=item _db_array_scalars(@elements) creates a db array from scalars.
+
+=item _db_array_literal(@elements) creates a multiple dimension db array from
+ preparsed db arrays or other data which does not need to be escaped.
+
+=cut
+
+package LedgerSMB::DBObject_Moose;
+use LedgerSMB::DBObject;
+use Moose;
+use Scalar::Util;
+use Log::Log4perl;
+use LedgerSMB::DBObject;
+
+my $logger = Log::Log4perl->get_logger('LedgerSMB::DBObject');
+
+sub __validate__ {}
+
+has 'dbh' => (is => 'ro', isa => 'DBI::db', required => '1');
+has '_roles' => (is => 'ro', isa => 'ArrayRef[Str]', required => '1');
+has '_user' => (is => 'ro', isa => 'LedgerSMB::User', required => '1');
+has '_locale' => (is => 'ro', isa => 'LedgerSMB::Locale', required => '1');
+has '_request' => (is => 'ro', isa => 'CGI::Simple', required => '1');
+
+sub prepare_dbhash {
+ my $self = shift;
+ my $target = shift;
+ for my $att (qw(dbh _roles _user _locale _request)){
+ if (!$target->{$att}){
+ $target->{$att} = $self->{$att};
+ }
+ }
+}
+
+# _to_dbobject
+#Private method used to convert to db object for purposes of
+#
+sub _to_dbobject {
+ my $self = shift @_;
+ return LedgerSMB::DBObject->new({base => $self});
+}
+
+=item set_ordering
+
+Sets the ordering used by default for specific functions called by exec_method
+
+=cut
+
+sub exec_method {
+ my $self = shift @_;
+ my $dbo = $self->_to_dbobject;
+ return $dbo->exec_method(@_);
+}
+
+=item run_custom_queries
+
+Backward-compatible with 1.2 custom query system for moving forward.
+
+=cut
+
+sub run_custom_queries {
+ my $self = shift @_;
+ my $dbo = $self->_to_dbobject;
+ return $dbo->run_custom_queries(@_);
+}
+
+sub call_procedure {
+ my $self = shift @_;
+ my $dbo = $self->_to_dbobject;
+ return $dbo->call_procedure(@_);
+}
+
+# Keeping this here due to common requirements
+sub is_allowed_role {
+ my $self = shift @_;
+ my $dbo = $self->_to_dbobject;
+ return $dbo->is_allowed_role(@_);
+}
+
+__PACKAGE__->meta->make_immutable;
+
+1;
+
+=back
+
+=head1 Copyright (C) 2007, The LedgerSMB core team.
+
+This file is licensed under the Gnu General Public License version 2, or at your
+option any later version. A copy of the license should have been included with
+your software.
+
+=cut
Copied: addons/1.3/b_units/trunk/business_unit.pl (from rev 4557, trunk/business_unit.pl)
===================================================================
--- addons/1.3/b_units/trunk/business_unit.pl (rev 0)
+++ addons/1.3/b_units/trunk/business_unit.pl 2012-03-25 05:09:05 UTC (rev 4558)
@@ -0,0 +1,8 @@
+#!/usr/bin/perl
+
+use FindBin;
+BEGIN {
+ lib->import($FindBin::Bin) unless $ENV{mod_perl}
+}
+
+require "lsmb-request.pl";
Copied: addons/1.3/b_units/trunk/scripts/business_unit.pl (from rev 4557, trunk/LedgerSMB/Scripts/business_unit.pm)
===================================================================
--- addons/1.3/b_units/trunk/scripts/business_unit.pl (rev 0)
+++ addons/1.3/b_units/trunk/scripts/business_unit.pl 2012-03-25 05:09:05 UTC (rev 4558)
@@ -0,0 +1,240 @@
+=head1 NAME
+
+LedgerSMB::Scripts::business_unit
+
+=cut
+
+package LedgerSMB::Scripts::business_unit;
+use LedgerSMB::DBObject::Business_Unit_Class;
+use LedgerSMB::DBObject::App_Module;
+use LedgerSMB::DBObject::Business_Unit;
+use LedgerSMB::Template;
+use Carp;
+
+$Carp::Verbose = 1;
+
+=head1 SYNOPSIS
+
+Workflow routines for LedgerSMB business reporting units
+
+=head1 FUNCTIONS
+
+All functions take a single $request object as their sole argument
+
+=over
+
+=item list_classes
+
+=cut
+
+sub list_classes {
+ my ($request) = @_;
+ my $bu_class = LedgerSMB::DBObject::Business_Unit_Class->new(%$request);
+ my $lsmb_modules = LedgerSMB::DBObject::App_Module->new(%$request);
+ @{$request->{classes}} = $bu_class->list;
+ @{$request->{modules}} = $lsmb_modules->list;
+ my $template = LedgerSMB::Template->new(
+ user =>$request->{_user},
+ locale => $request->{_locale},
+ path => 'UI/business_units',
+ template => 'list_classes',
+ format => 'HTML'
+ );
+ $template->render($request);
+}
+
+=item add
+
+Adds a new business unit. $request->{class_id} must be set.
+
+=cut
+
+sub add {
+ my ($request) = @_;
+ if (!$request->{class_id}){
+ $request->{class_id} = $request->{id};
+ }
+ $request->{id} = undef;
+ _display($request);
+}
+
+=item edit
+
+Edits an existing business unit. $request->{id} must be set.
+
+=cut
+
+sub edit {
+ my ($request) = @_;
+ $request->{control_code} = '';
+ $request->{class_id} = 0 unless $request->{class_id} = 0;
+ my $b_unit = LedgerSMB::DBObject::Business_Unit->new(%$request);
+ my $bu = $b_unit->get($request->{id});
+ _display($bu);
+}
+
+sub _display {
+ my ($request) = @_;
+ my $template = LedgerSMB::Template->new(
+ user =>$request->{_user},
+ locale => $request->{_locale},
+ path => 'UI/business_units',
+ template => 'edit',
+ format => 'HTML'
+ );
+ $template->render($request);
+
+}
+
+=item list
+
+Lists business units. The following properties of $request may be set:
+
+=over
+
+=item class_id (required)
+
+Lists units for appropriate class.
+
+=item active_on
+
+If set filters for units active on the date in question, inclusive of start/end
+dates
+
+=item credit_id
+
+If set, filters excludes those which are for customers/vendors other than than
+identified by this value.
+
+=item strict_credit_id
+
+If set, excludes those which are not associated with customers/vendors.
+
+=back
+
+=cut
+
+sub list {
+ my ($request) = @_;
+ $request->{control_code} = '';
+ $request->{class_id} = 0 unless $request->{class_id} = 0;
+ my $b_unit = LedgerSMB::DBObject::Business_Unit->new(%$request);
+ my $template = LedgerSMB::Template->new(
+ user =>$request->{_user},
+ locale => $request->{_locale},
+ path => 'UI',
+ template => 'form-dynatable',
+ format => 'HTML'
+ );
+ my $cols;
+ @$cols = qw(id control_code description start_date end_date);
+ my $heading = {
+ id => $request->{_locale}->text('ID'),
+ control_code => $request->{_locale}->text('Control Code'),
+ description => $request->{_locale}->text('Description'),
+ start_date => $request->{_locale}->text('Start Date'),
+ end_date => $request->{_locale}->text('End Date'),
+ };
+ my $rows;
+ @$rows = $b_unit->list($request->{id});
+ my $base_href= "business_unit.pl?action=edit";
+ for $row(@$rows){
+ $row->{control_code} = {text => $row->{control_code},
+ href => "$base_href&id=$row->{id}"};
+ }
+ $template->render({
+ form => $request,
+ heading => $heading,
+ rows => $rows,
+ columns => $cols,
+ });
+}
+
+=item delete
+
+Deletes an existing business unit. Only vaid for ones with no transactions or
+sub-units.
+
+$request->{id} must be set.
+
+=cut
+
+sub delete {
+ my ($request) = @_;
+ my $unit = LedgerSMB::DBObject::Business_Unit->new(%$request);
+ $unit->delete;
+ list($request);
+}
+
+=item delete_class
+
+Deletes an existing business unit class. Only valid of no units are of class.
+
+$request->{id} must be set.
+
+=cut
+
+sub delete_class {
+ my ($request) = @_;
+ my $bu_class = LedgerSMB::DBObject::Business_Unit_Class->new($request);
+ $bu_class->delete;
+ list_classes($request);
+}
+
+=item save
+
+Saves the existing unit. Standard properties of
+LedgerSMB::DBObject::Business_Unit must be set for $request.
+
+=cut
+
+sub save {
+ my ($request) = @_;
+ $request->{start_date} = LedgerSMB::PGDate->from_input($request->{start_date}, 0)
+ if defined $request->{start_date};
+ $request->{end_date} = LedgerSMB::PGDate->from_input($request->{end_date}, 0)
+ if defined $request->{end_date};
+ my $unit = LedgerSMB::DBObject::Business_Unit->new(%$request);
+ $unit->save;
+ edit($request);
+}
+
+=item save_class
+
+Saves the existing unit class. Standard properties for
+LedgerSMB::DBObject::Business_Unit_Class must be set for $request.
+
+=cut
+
+sub save_class {
+ my ($request) = @_;
+ my $lsmb_modules = LedgerSMB::DBObject::App_Module->new(%$request);
+ my @modules = $lsmb_modules->list;
+ my $modlist = [];
+ for my $mod (@modules){
+ if ($request->{"module_" . $mod->id}){
+ push @$modlist, $mod;
+ }
+ }
+ for my $key (qw(active non_accounting)){
+ if (!$request->{$key}){
+ $request->{$key} = 0;
+ }
+ }
+ my $bu_class = LedgerSMB::DBObject::Business_Unit_Class->new(%$request);
+ $bu_class->modules($modlist);
+ $bu_class->save;
+ list_classes($request);
+}
+
+=back
+
+=head COPYRIGHT
+
+Copyright (C) 2012 LedgerSMB core team. Redistribution and use of work is
+governed under the GNU General Public License, version 2 or at your option any
+later version.
+
+=cut
+
+1;
Added: addons/1.3/b_units/trunk/sql/modules/Business_Unit-roles.sql
===================================================================
--- addons/1.3/b_units/trunk/sql/modules/Business_Unit-roles.sql (rev 0)
+++ addons/1.3/b_units/trunk/sql/modules/Business_Unit-roles.sql 2012-03-25 05:09:05 UTC (rev 4558)
@@ -0,0 +1,3 @@
+GRANT ALL ON business_unit_class, business_unit, business_unit_ac,
+ business_unit_oitem, business_unit_inv
+TO public;
Added: addons/1.3/b_units/trunk/sql/modules/Business_Unit-tables.sql
===================================================================
--- addons/1.3/b_units/trunk/sql/modules/Business_Unit-tables.sql (rev 0)
+++ addons/1.3/b_units/trunk/sql/modules/Business_Unit-tables.sql 2012-03-25 05:09:05 UTC (rev 4558)
@@ -0,0 +1,102 @@
+BEGIN;
+
+CREATE TABLE lsmb_module (
+ id int not null unique,
+ label text primary key
+);
+
+COMMENT ON TABLE lsmb_module IS
+$$ This stores categories functionality into modules. Addons may add rows here, but
+the id should be hardcoded. As always 900-1000 will be reserved for internal use,
+and negative numbers will be reserved for testing.$$;
+
+INSERT INTO lsmb_module (id, label)
+VALUES (1, 'AR'),
+ (2, 'AP'),
+ (3, 'GL'),
+ (4, 'Entity'),
+ (5, 'Manufacturing'),
+ (6, 'Fixed Assets');
+
+
+CREATE TABLE business_unit_class (
+ id serial not null unique,
+ label text primary key,
+ active bool not null default false,
+ ordering int
+);
+
+COMMENT ON TABLE business_unit_class IS
+$$ Consolidates projects and departments, and allows this to be extended for
+funds accounting and other purposes.$$;
+
+INSERT INTO business_unit_class (id, label, active, ordering)
+VALUES (1, 'Department', '0', '10'),
+ (2, 'Project', '0', '20'),
+ (3, 'Job', '0', '30'),
+ (4, 'Fund', '0', '40'),
+ (5, 'Customer', '0', '50'),
+ (6, 'Vendor', '0', '60'),
+ (7, 'Lot', '0', 50);
+
+CREATE TABLE bu_class_to_module (
+ bu_class_id int references business_unit_class(id),
+ module_id int references lsmb_module(id),
+ primary key (bu_class_id, module_id)
+);
+
+INSERT INTO bu_class_to_module (bu_class_id, module_id)
+SELECT business_unit_class.id, lsmb_module.id
+ FROM business_unit_class
+ CROSS
+ JOIN lsmb_module; -- by default activate all existing business units on all modules
+
+
+CREATE TABLE business_unit (
+ id serial PRIMARY KEY,
+ class_id int not null references business_unit_class(id),
+ control_code text,
+ description text,
+ start_date date,
+ end_date date,
+ parent_id int references business_unit(id),
+ credit_id int references entity_credit_account(id),
+ UNIQUE(id, class_id), -- needed for foreign keys
+ UNIQUE(class_id, control_code)
+);
+
+CREATE TABLE job (
+ bu_id int primary key references business_unit(id),
+ parts_id int,
+ production numeric default 0,
+ completed numeric default 0
+);
+
+CREATE TABLE business_unit_ac (
+ entry_id int references acc_trans(entry_id),
+ class_id int references business_unit_class(id),
+ bu_id int,
+ primary key(bu_id, class_id, entry_id),
+ foreign key(class_id, bu_id) references business_unit(class_id, id)
+);
+
+CREATE TABLE business_unit_inv (
+ entry_id int references invoice(id),
+ class_id int references business_unit_class(id),
+ bu_id int,
+ primary key(bu_id, class_id, entry_id),
+ foreign key(class_id, bu_id) references business_unit(class_id, id)
+);
+
+CREATE TABLE business_unit_oitem (
+ entry_id int references orderitems(id),
+ class_id int references business_unit_class(id),
+ bu_id int,
+ primary key(bu_id, class_id, entry_id),
+ foreign key(class_id, bu_id) references business_unit(class_id, id)
+);
+
+COMMENT ON TABLE business_unit IS
+$$ Tracks Projects, Departments, Funds, Etc.$$;
+
+COMMIT;
Copied: addons/1.3/b_units/trunk/sql/modules/Business_Unit.sql (from rev 4557, trunk/sql/modules/Business_Unit.sql)
===================================================================
--- addons/1.3/b_units/trunk/sql/modules/Business_Unit.sql (rev 0)
+++ addons/1.3/b_units/trunk/sql/modules/Business_Unit.sql 2012-03-25 05:09:05 UTC (rev 4558)
@@ -0,0 +1,190 @@
+BEGIN;
+
+CREATE OR REPLACE FUNCTION business_unit__list_classes(in_active bool, in_module text)
+RETURNS SETOF business_unit_class AS
+$$
+
+SELECT bc.*
+ FROM business_unit_class bc
+ WHERE (active = $1 OR $1 IS NULL)
+ AND (id IN (select bu_class_id
+ FROM bu_class_to_module bcm
+ JOIN lsmb_module mod ON mod.id = bcm.module_id
+ WHERE lower(label) = lower($2))
+ OR $2 is null)
+ORDER BY ordering;
+
+$$ LANGUAGE SQL;
+
+COMMENT ON FUNCTION business_unit__list_classes(in_active bool, in_module text) IS
+$$ This function lists all business unit clases. If in_active is true, then
+only active classes are listed. If it is false then only inactive classes are
+listed. If it is null, then all classes are listed.$$;
+
+CREATE OR REPLACE FUNCTION business_unit_get(in_id int) RETURNS business_unit
+AS
+$$ SELECT * FROM business_unit WHERE id = $1; $$ LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION business_unit__list_by_class
+(in_business_unit_class_id int, in_active_on date, in_credit_id int,
+in_strict_credit bool)
+RETURNS SETOF business_unit AS
+$$
+BEGIN
+RETURN QUERY SELECT * FROM business_unit
+ WHERE (in_active_on BETWEEN coalesce(start_date, in_active_on)
+ AND coalesce(end_date, in_active_on)
+ OR in_active_on IS NULL)
+ AND (in_credit_id = credit_id
+ OR (credit_id IS NULL and in_strict_credit IS NOT TRUE)
+ OR (in_credit_id IS NULL))
+ AND class_id = in_business_unit_class_id
+ ORDER BY control_code;
+END;
+$$ LANGUAGE PLPGSQL;
+
+COMMENT ON FUNCTION business_unit__list_by_class
+(in_business_unit_class_id int, in_active_on date, in_credit_id int,
+in_strict_credit bool) IS
+$$ This function retUrns a list of all units (projects, departments, funds, etc)
+active on the in_active_on date, where in_credit_id matches the credit id of the
+customer or vendor requested, and where in_business_uni_class_id is the class id
+of the class of business units (1 for department, 2 for project, etc).
+
+With the exception of in_business_unit_class_id, the null matches all records.
+$$;
+
+DROP TYPE IF EXISTS business_unit_short CASCADE;
+
+CREATE TYPE business_unit_short AS (
+id int,
+control_code text,
+description text,
+start_date date,
+end_date date,
+parent_id int,
+path int[],
+level int
+);
+
+CREATE OR REPLACE FUNCTION business_unit__get_tree_for(in_id int)
+RETURNS SETOF business_unit_short AS
+$$
+WITH RECURSIVE tree (id, control_code, description, start_date, end_date,
+ parent_id, path, level)
+AS (
+ SELECT id, control_code, description, start_date, end_date, parent_id,
+ ARRAY[parent_id] AS path, 1 as level
+ FROM business_unit WHERE $1 = id
+ UNION
+ SELECT t.id, t.control_code, t.description, t.start_date, t.end_date,
+ t.parent_id,
+ t.path || bu.id AS path, t.level + 1 as level
+ FROM business_unit bu JOIN tree t ON t.parent_id = bu.id
+)
+SELECT * FROM tree ORDER BY path;
+$$ LANGUAGE SQL;
+
+COMMENT ON FUNCTION business_unit__get_tree_for(in_id int) IS
+$$ This function returns tree-related records with the root of the tree being
+the business unit of in_id. $$;
+
+CREATE OR REPLACE FUNCTION business_unit_class__save_modules
+(in_id int, in_mod_ids int[])
+RETURNS BOOL AS
+$$
+DELETE FROM bu_class_to_module WHERE bu_class_id = $1;
+
+INSERT INTO bu_class_to_module (bu_class_id, module_id)
+SELECT $1, unnest
+ FROM unnest($2);
+
+SELECT true;
+$$ LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION business_unit_class__get_modules(in_id int)
+RETURNS SETOF lsmb_module AS
+$$ SELECT * FROM lsmb_module
+ WHERE id IN (select module_id from bu_class_to_module where bu_class_id = $1)
+ ORDER BY id;
+$$ language sql;
+
+CREATE OR REPLACE FUNCTION business_unit_class__save
+(in_id int, in_label text, in_active bool, in_ordering int)
+RETURNS business_unit_class AS
+$$
+DECLARE retval business_unit_class;
+ t_id int;
+BEGIN
+
+t_id := in_id;
+UPDATE business_unit_class
+ SET label = in_label,
+ active = in_active,
+ ordering = in_ordering
+ WHERE id = in_id;
+
+IF NOT FOUND THEN
+
+ INSERT INTO business_unit_class (label, active, ordering)
+ VALUES (in_label, in_active, in_ordering);
+
+ t_id := currval('business_unit_class_id_seq');
+
+END IF;
+
+SELECT * INTO retval FROM business_unit_class WHERE id = t_id;
+
+RETURN retval;
+
+END;
+
+$$LANGUAGE PLPGSQL;
+
+CREATE OR REPLACE FUNCTION business_unit__save
+(in_id int, in_class_id int, in_control_code text, in_description text,
+in_start_date date, in_end_date date, in_parent_id int, in_credit_id int)
+RETURNS business_unit AS
+$$
+DECLARE retval business_unit;
+ t_id int;
+
+BEGIN
+
+UPDATE business_unit
+ SET class_id = in_class_id,
+ control_code = in_control_code,
+ description = in_description,
+ start_date = in_start_date,
+ end_date = in_end_date,
+ parent_id = in_parent_id,
+ credit_id = in_credit_id
+ WHERE id = in_id;
+
+
+IF FOUND THEN
+ t_id := in_id;
+ELSE
+ INSERT INTO business_unit
+ (class_id, control_code, description, start_date, end_date, parent_id,
+ credit_id)
+ VALUES (in_class_id, in_control_code, in_description, in_start_date,
+ in_end_date, in_parent_id, in_credit_id);
+ t_id := currval('business_unit_id_seq');
+END IF;
+
+RAISE NOTICE 'ID: %', t_id;
+
+SELECT * INTO retval FROM business_unit WHERE id = t_id;
+
+RAISE NOTICE 'ID: %', retval.id;
+
+RETURN retval;
+END;
+$$ LANGUAGE PLPGSQL;
+
+CREATE OR REPLACE FUNCTION business_unit__get(in_id int)
+RETURNS business_unit AS
+$$ SELECT * FROM business_unit where id = $1; $$ LANGUAGE SQL;
+
+COMMIT;
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.