[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

SF.net SVN: ledger-smb:[4558] addons/1.3



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.