[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[3694] trunk

Subject: SF.net SVN: ledger-smb:[3694] trunk
From: ..hidden..
Date: Fri, 09 Sep 2011 20:41:42 +0000
Revision: 3694
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=3694&view=rev
Author:   einhverfr
Date:     2011-09-09 20:41:41 +0000 (Fri, 09 Sep 2011)
Log Message:
-----------
Final db changes for RC1
Also migration tool complete, but needs more testing.

Modified Paths:
--------------
    trunk/LedgerSMB/Form.pm
    trunk/LedgerSMB/Template/HTML.pm
    trunk/LedgerSMB/Template/LaTeX.pm
    trunk/LedgerSMB.pm
    trunk/UI/login.html
    trunk/VERSION
    trunk/scripts/setup.pl
    trunk/sql/Pg-database.sql
    trunk/sql/upgrade/1.2-1.3.sql
    trunk/t/99-versioning.t

Added Paths:
-----------
    trunk/UI/setup/complete.html
    trunk/UI/setup/upgrade_info.html
    trunk/sql/upgrade/1.2-1.3-manual.sql
    trunk/sql/upgrade/3694-update_version.sql

Removed Paths:
-------------
    trunk/sql/upgrade/1.2-1.3-work-in-progress.sql

Modified: trunk/LedgerSMB/Form.pm
===================================================================
--- trunk/LedgerSMB/Form.pm	2011-09-02 17:56:14 UTC (rev 3693)
+++ trunk/LedgerSMB/Form.pm	2011-09-09 20:41:41 UTC (rev 3694)
@@ -150,8 +150,8 @@
     #menubar will be deprecated, replaced with below
     $self->{lynx} = 1 if ( ( defined $self->{path} ) && ( $self->{path} =~ /lynx/i ) );
 
-    $self->{version}   = "1.2.99";
-    $self->{dbversion} = "1.2.99";
+    $self->{version}   = "1.3.0 rc1";
+    $self->{dbversion} = "1.3.0";
 
     bless $self, $type;
 

Modified: trunk/LedgerSMB/Template/HTML.pm
===================================================================
--- trunk/LedgerSMB/Template/HTML.pm	2011-09-02 17:56:14 UTC (rev 3693)
+++ trunk/LedgerSMB/Template/HTML.pm	2011-09-09 20:41:41 UTC (rev 3694)
@@ -88,7 +88,9 @@
 sub escape {
     my $vars = shift @_;
     if (defined $vars){
-        return escapeHTML($vars);
+        $vars = escapeHTML($vars);
+        $vars =~ s|\n|<br />|gm;
+        return $vars;
     }
     return undef;
 }

Modified: trunk/LedgerSMB/Template/LaTeX.pm
===================================================================
--- trunk/LedgerSMB/Template/LaTeX.pm	2011-09-02 17:56:14 UTC (rev 3693)
+++ trunk/LedgerSMB/Template/LaTeX.pm	2011-09-09 20:41:41 UTC (rev 3694)
@@ -100,6 +100,7 @@
     if (defined $vars){
             $vars =~ s/([&\$\\_<>~^#\%\{\}])/\\$1/g;
             $vars =~ s/"(.*)"/``$1''/gs;
+            $vars =~ s/\n/\n\n/gm;
     }
     return $vars;
 }

Modified: trunk/LedgerSMB.pm
===================================================================
--- trunk/LedgerSMB.pm	2011-09-02 17:56:14 UTC (rev 3693)
+++ trunk/LedgerSMB.pm	2011-09-09 20:41:41 UTC (rev 3694)
@@ -220,7 +220,7 @@
 $CGI::Simple::POST_MAX = -1;
 
 package LedgerSMB;
-our $VERSION = '1.2.99';
+our $VERSION = '1.3.0 rc1';
 
 my $logger = Log::Log4perl->get_logger('LedgerSMB');
 
@@ -238,7 +238,7 @@
     $logger->debug("Begin LedgerSMB.pm");
 
     $self->{version} = $VERSION;
-    $self->{dbversion} = "1.2.99";
+    $self->{dbversion} = "1.3.0";
     
     bless $self, $type;
     $logger->debug("LedgerSMB::new: \$argstr = $argstr");

Modified: trunk/UI/login.html
===================================================================
--- trunk/UI/login.html	2011-09-02 17:56:14 UTC (rev 3693)
+++ trunk/UI/login.html	2011-09-09 20:41:41 UTC (rev 3694)
@@ -27,7 +27,7 @@
 		<div class="login">
 			<div class="login" align="center">
 				<a href="http://www.ledgersmb.org/"; target="_top"><img src="images/ledgersmb.png" class="logo" alt="LedgerSMB Logo" /></a>
-				<h1 class="login" align="center">Version SVN Trunk</h1>
+				<h1 class="login" align="center"><?lsmb text("LedgerSMB [_1]", VERSION) ?></h1>
 				<div align="center">
 					<div id="credentials"></div>
 					<div id="company_div">
@@ -35,7 +35,7 @@
 						<div class="label">
 							<label for="company">
 							<?lsmb text('Company') 
-							?>
+							?>:
 							</label>
 						</div>
 						<div class="input">

Added: trunk/UI/setup/complete.html
===================================================================
--- trunk/UI/setup/complete.html	                        (rev 0)
+++ trunk/UI/setup/complete.html	2011-09-09 20:41:41 UTC (rev 3694)
@@ -0,0 +1,13 @@
+<?lsmb INCLUDE "ui-header.html" 
+stylesheet="ledgersmb.css"
+include_stylesheet=["UI/setup/stylesheet.css"]
+include_script = ['UI/setup/login.js', 'UI/util.js'] ?>
+<?lsmb PROCESS elements.html ?>
+<body>
+<h2><?lsmb text('Database Management Console') ?></h2>
+<div class="listtop"><?lsmb text('Database Operation Complete') ?></div>
+<p><?lsmb text('This database operation has completed successfully.  LedgerSMB may now be used.'); ?></p>
+<p><a href="setup.pl"><?lsmb text('Return to setup') ?></a></p>
+<p><a href="login.pl"><?lsmb text('Start Using LedgerSMB') ?></a></p>
+</body>
+</html>

Added: trunk/UI/setup/upgrade_info.html
===================================================================
--- trunk/UI/setup/upgrade_info.html	                        (rev 0)
+++ trunk/UI/setup/upgrade_info.html	2011-09-09 20:41:41 UTC (rev 3694)
@@ -0,0 +1,57 @@
+<?lsmb INCLUDE "ui-header.html" 
+stylesheet="ledgersmb.css"
+include_stylesheet=["UI/setup/stylesheet.css"] ?>
+<?lsmb PROCESS elements.html ?>
+<body>
+<div class="listtop"><?lsmb text('Enter User') ?></div>
+<form action="setup.pl" method="POST" name="new_user">
+<?lsmb INCLUDE input element_data = {
+    name = 'database'
+    type = 'hidden'
+   value = database
+} ?>
+<div class="form">
+<div class="input_row">
+<?lsmb INCLUDE input element_data = {
+                            name  = 'contribdir'
+                            type  = 'text'
+                           label  = text('Contrib Dir') #'
+                            class = 'directory'
+} ?>
+</div>
+<div class="input_row">
+<?lsmb INCLUDE input element_data = {
+                            name  = 'default_country'
+                            type  = 'text'
+                           label  = text('Default Country') #'
+                            class = 'country'
+} ?>
+</div>
+<div class="input_row">
+<?lsmb INCLUDE input element_data = {
+                            name  = 'default_ar'
+                            type  = 'text'
+                           label  = text('Default AR') #'
+                            class = 'accno'
+} ?>
+</div>
+<div class="input_row">
+<?lsmb INCLUDE input element_data = {
+                            name  = 'default_ap'
+                            type  = 'text'
+                           label  = text('Default AP') #'
+                            class = 'accno'
+} ?>
+</div>
+<div class="input_row">
+<?lsmb INCLUDE button element_data = {
+    text = text('Upgrade') 
+    name = 'action'
+   value = 'run_upgrade'
+    type = 'submit'
+   class = 'submit'
+} ?>
+</div>
+</form>
+</body>
+</html>

Modified: trunk/VERSION
===================================================================
--- trunk/VERSION	2011-09-02 17:56:14 UTC (rev 3693)
+++ trunk/VERSION	2011-09-09 20:41:41 UTC (rev 3694)
@@ -1 +1 @@
-1.2.99
+1.3.0 rc1

Modified: trunk/scripts/setup.pl
===================================================================
--- trunk/scripts/setup.pl	2011-09-02 17:56:14 UTC (rev 3693)
+++ trunk/scripts/setup.pl	2011-09-09 20:41:41 UTC (rev 3694)
@@ -156,37 +156,48 @@
     my $locale = $request->{_locale};
 
     my @pre_upgrade_checks = (
-       {query => "SELECT count(*), customernumber from customer
+       {query => "select id, customernumber, name, address1, city, state, zipcode
+                   from customer where customernumber in (SELECT customernumber from customer
                    GROUP BY customernumber
-                   HAVING count(*) > 1",
+                   HAVING count(*) > 1)",
          name => $locale->text('Unique Customernumber'),
          cols => ['customernumber', 'name', 'address1', 'city', 'state', 'zip'],
-         edit => 'customernumber'},
+         edit => 'customernumber',
+        table => 'customer'},
 
-       {query => "SELECT count(*), vendornumber from vendor
+       {query => "SELECT id, vendornumber, name, address1, city, state, zipcode
+                   FROM vendor WHERE vendornumber IN 
+                   (SELECT vendornumber from vendor
                    GROUP BY vendornumber
-                   HAVING count(*) > 1",
+                   HAVING count(*) > 1)",
          name => $locale->text('Unique Vendornumber'),
          cols => ['vendornumber', 'name', 'address1', 'city', 'state', 'zip'],
-         edit => 'vendornumber'},
+         edit => 'vendornumber',
+        table => 'vendor'},
 
        {query => 'SELECT * FROM employee where employeenumber IS NULL',
          name => $locale->text('No null employeenumber'),
          cols => ['login', 'name', 'employeenumber'],
-         edit => 'employeenumber'},
+         edit => 'employeenumber',
+        table => 'employee'},
 
-       {query => "select partnumber, count(*) from parts 
+       {query => "select * from parts where obsolete is not true 
+                  and partnumber in 
+                  (select partnumber from parts 
                   WHERE obsolete is not true
-                  group by partnumber having count(*) > 1",
+                  group by partnumber having count(*) > 1)",
          name => $locale->text('Unique nonobsolete partnumbers'),
          cols => ['partnumber', 'description', 'sellprice'],
-         edit => 'partnumber'},
+         edit => 'partnumber',
+        table => 'parts'},
 
-       {query => 'SELECT invnumber, count(*) from ar
-                   group by invnumber having count(*) > 1',
+       {query => 'SELECT * from ar where invnumber in (
+                   select invnumber from ar
+                   group by invnumber having count(*) > 1)',
          name => $locale->text('Unique AR Invoice numbers'),
          cols =>  ['invnumber', 'transdate', 'amount', 'netamount', 'paid'],
-         edit =>  'invnumber'},
+         edit =>  'invnumber',
+        table =>  'ar'},
     );
     for my $check (@pre_upgrade_checks){
         my $sth = $request->{dbh}->prepare($check->{query});
@@ -214,17 +225,25 @@
     );
     my $rows = [];
     my $count = 1;
-    my $hiddens = {};
+    my $hiddens = {table => $check->{table},
+                    edit => $check->{edit},
+                database => $request->{database}};
+    my $header = {};
+    for (@{$check->{cols}}){
+        $header->{$_} = $_;
+    }
     while (my $row = $sth->fetchrow_hashref('NAME_lc')){
           $row->{$check->{'edit'}} = 
                     { input => {
                                 name => "$check->{edit}_$row->{id}",
                                 value => $row->{$check->{'edit'}},
                                 type => 'text',
-                                size => 10,
+                                size => 15,
                     },
           };
+          push @$rows, $row;
           $hiddens->{"id_$count"} = $row->{id},
+          ++$count;
     }
     $hiddens->{count} = $count;
     my $buttons = [
@@ -236,6 +255,7 @@
     ];
     $template->render({
            form     => $request,
+           heading  => $header,
            columns  => $check->{cols},
            rows     => $rows,
            hiddens  => $hiddens,
@@ -243,6 +263,40 @@
     });
 }
 
+=item fix_tests
+
+Handles input from the failed test function and then re-runs the migrate db 
+script.
+
+=cut
+
+sub fix_tests{
+    my ($request) = @_;
+    my $creds = LedgerSMB::Auth::get_credentials();
+    # ENVIRONMENT NECESSARY
+    $ENV{PGUSER} = $creds->{login};
+    $ENV{PGPASSWORD} = $creds->{password};
+    $ENV{PGDATABASE} = $request->{database};
+
+    # Credentials set above via environment variables --CT
+    $request->{dbh} = DBI->connect("dbi:Pg:dbname=$request->{database}");
+    my $locale = $request->{_locale};
+
+    my $table = $request->{dbh}->quote_identifier($request->{table});
+    my $edit = $request->{dbh}->quote_identifier($request->{edit});
+    my $sth = $request->{dbh}->prepare(
+            "UPDATE $table SET $edit = ? where id = ?"
+    );
+    
+    for my $count (1 .. $request->{count}){
+        my $id = $request->{"id_$count"};
+        $sth->execute($request->{"$request->{edit}_$id"}, $id) ||
+            $request->error($sth->errstr);
+    }
+    $request->{dbh}->commit;
+    upgrade($request);
+}
+
 =item create_db
 
  Beginning of the new database workflow
@@ -439,6 +493,62 @@
     $template->render($request);
 }
 
+=item run_upgrade
+
+Runs the actual upgrade script.
+
+=cut
+
+sub run_upgrade {
+    my ($request) = @_;
+    my $creds = LedgerSMB::Auth::get_credentials();
+    my $database = LedgerSMB::Database->new(
+               {username => $creds->{username},
+            company_name => $request->{database},
+                password => $creds->{password}}
+    );
+
+    # ENVIRONMENT NECESSARY
+    $ENV{PGUSER} = $creds->{login};
+    $ENV{PGPASSWORD} = $creds->{password};
+    $ENV{PGDATABASE} = $request->{database};
+
+    # Credentials set above via environment variables --CT
+    $request->{dbh} = DBI->connect("dbi:Pg:dbname=$request->{database}");
+    my $dbh = $request->{dbh};
+    $dbh->do('ALTER SCHEMA public RENAME TO lsmb12');
+    $dbh->do('CREATE SCHEMA PUBLIC');
+    # Copying contrib script loading for now
+    my $rc = 0;
+    my $temp = $LedgerSMB::Sysconfig::temp;
+     my @contrib_scripts = qw(pg_trgm tsearch2 tablefunc);
+
+     for my $contrib (@contrib_scripts){
+         my $rc2;
+         $rc2=system("psql -f $ENV{PG_CONTRIB_DIR}/$contrib.sql >> $temp/dblog_stdout 2>>$temp/dblog_stderr");
+         $rc ||= $rc2
+     }
+     my $rc2 = system("psql -f sql/Pg-database.sql >> $temp/dblog_stdout 2>>$temp/dblog_stderr");
+     
+     $rc ||= $rc2;
+
+    $database->load_modules('LOADORDER');
+    my $dbtemplate = LedgerSMB::Template->new(
+        user => {}, 
+        template => 'sql/upgrade/1.2-1.3.sql',
+        no_auto_output => 1,
+        format => 'text' );
+    $dbtemplate->render($request);
+    $rc2 = system("psql -f $dbtemplate->{rendered} >> $temp/dblog_stdout 2>>$temp/dblog_stderr");
+    $rc ||= $rc2;
+    my $template = LedgerSMB::Template->new(
+                   path => 'UI/setup',
+                   template => 'new_user',
+                   format => 'HTML',
+     );
+     $template->render($request);
+}
+
 =item cancel
 
 Cancels work.  If the confirm is set to no, returns to the credential screen

Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql	2011-09-02 17:56:14 UTC (rev 3693)
+++ trunk/sql/Pg-database.sql	2011-09-09 20:41:41 UTC (rev 3694)
@@ -1697,6 +1697,15 @@
 
 COMMENT ON TABLE project_translation IS
 $$ Translation information for projects.$$;
+
+CREATE TABLE partsgroup_translation 
+(PRIMARY KEY (trans_id, language_code)) INHERITS (translation);
+ALTER TABLE partsgroup_translation 
+ADD foreign key (trans_id) REFERENCES partsgroup(id);
+
+COMMENT ON TABLE partsgroup_translation IS
+$$ Translation information for partsgroups.$$;
+
 --
 CREATE TABLE user_preference (
     id int PRIMARY KEY REFERENCES users(id),

Copied: trunk/sql/upgrade/1.2-1.3-manual.sql (from rev 3693, trunk/sql/upgrade/1.2-1.3-work-in-progress.sql)
===================================================================
--- trunk/sql/upgrade/1.2-1.3-manual.sql	                        (rev 0)
+++ trunk/sql/upgrade/1.2-1.3-manual.sql	2011-09-09 20:41:41 UTC (rev 3694)
@@ -0,0 +1,679 @@
+--Setup
+
+-- When moved to an interface, these will all be specified and preprocessed.
+\set lsmbdir `pwd`
+\set contribdir '/usr/share/pgsql/contrib'
+\set default_country '''us'''
+\set ar '''1200'''
+\set ap '''2100'''
+
+-- This will be moved out of this part.
+ALTER SCHEMA public RENAME TO lsmb12;
+CREATE SCHEMA public;
+
+\cd :contribdir
+\i pg_trgm.sql
+\i tsearch2.sql
+\i tablefunc.sql
+
+\cd :lsmbdir
+
+-- Full module load should be part of upgrade wizard, at this stage.
+\i sql/Pg-database.sql
+\i sql/modules/Setting.sql
+\i sql/modules/Location.sql
+\i sql/modules/Account.sql
+\i sql/modules/Payment.sql
+\i sql/modules/Person.sql
+\i sql/modules/Reconciliation.sql
+BEGIN;
+
+-- adding mapping info for import.
+
+ALTER TABLE lsmb12.vendor ADD COLUMN entity_id int;
+ALTER TABLE lsmb12.vendor ADD COLUMN company_id int;
+ALTER TABLE lsmb12.vendor ADD COLUMN credit_id int;
+
+ALTER TABLE lsmb12.customer ADD COLUMN entity_id int;
+ALTER TABLE lsmb12.customer ADD COLUMN company_id int;
+ALTER TABLE lsmb12.customer ADD COLUMN credit_id int;
+
+
+--Accounts
+INSERT INTO account_heading(id, accno, description)
+SELECT id, accno, description
+  FROM lsmb12.chart WHERE charttype = 'H';
+
+SELECT account_save(id, accno, description, category, gifi_accno, NULL, contra, 
+                    CASE WHEN link like '%tax%' THEN true ELSE false END, 
+                    string_to_array(link,':'))
+  FROM lsmb12.chart 
+ WHERE charttype = 'A';
+--Entity
+
+INSERT INTO entity (name, control_code, entity_class, country_id)
+SELECT name, 'V-' || vendornumber, 1, 
+       (select id from country 
+         where lower(short_name)  = :default_country)
+FROM lsmb12.vendor
+GROUP BY name, vendornumber;
+
+INSERT INTO entity (name, control_code, entity_class, country_id)
+SELECT name, 'C-' || customernumber, 2, 
+       (select id from country 
+         where lower(short_name)  =  :default_country)
+FROM lsmb12.customer
+GROUP BY name, customernumber;
+
+UPDATE lsmb12.vendor SET entity_id = (SELECT id FROM entity WHERE 'V-' || vendornumber = control_code);
+
+UPDATE lsmb12.customer SET entity_id = coalesce((SELECT min(id) FROM entity WHERE 'C-' || customernumber = control_code), entity_id);
+
+--Entity Credit Account
+
+INSERT INTO entity_credit_account
+(entity_id, meta_number, business_id, creditlimit, ar_ap_account_id, 
+	cash_account_id, startdate, enddate, threshold, entity_class)
+SELECT entity_id, vendornumber, business_id, creditlimit, 
+       (select id from account where accno = :ap), 
+	NULL, startdate, enddate, 0, 1
+FROM lsmb12.vendor WHERE entity_id IS NOT NULL;
+
+UPDATE lsmb12.vendor SET credit_id = 
+	(SELECT id FROM entity_credit_account e 
+	WHERE e.meta_number = vendornumber);
+
+
+INSERT INTO entity_credit_account
+(entity_id, meta_number, business_id, creditlimit, ar_ap_account_id, 
+	cash_account_id, startdate, enddate, threshold, entity_class)
+SELECT entity_id, customernumber, business_id, creditlimit,
+       (select id from account where accno = :ar),
+	NULL, startdate, enddate, 0, 2
+FROM lsmb12.customer WHERE entity_id IS NOT NULL;
+
+UPDATE lsmb12.customer SET credit_id = 
+	(SELECT id FROM entity_credit_account e 
+	WHERE e.meta_number = customernumber AND customer.entity_id = e.entity_id);
+
+--Company
+
+INSERT INTO company (entity_id, legal_name, tax_id)
+SELECT entity_id, name, max(taxnumber) FROM lsmb12.vendor 
+WHERE entity_id IS NOT NULL AND entity_id IN (select id from entity) GROUP BY entity_id, name;
+
+UPDATE lsmb12.vendor SET company_id = (select id from company c where entity_id = vendor.entity_id);
+
+INSERT INTO company (entity_id, legal_name, tax_id)
+SELECT entity_id, name, max(taxnumber) FROM lsmb12.customer
+WHERE entity_id IS NOT NULL AND entity_id IN (select id from entity) GROUP BY entity_id, name;
+
+UPDATE lsmb12.customer SET company_id = (select id from company c where entity_id = customer.entity_id);
+
+-- Contact
+
+insert into eca_to_contact (credit_id, contact_class_id, contact,description) 
+select v.credit_id, 1, v.phone, 'Primary phone: '||max(v.contact) as description
+from lsmb12.vendor v 
+where v.company_id is not null and v.phone is not null 
+       and v.phone ~ '[[:alnum:]_]'::text 
+group by v.credit_id, v.phone
+UNION
+select v.credit_id, 12, v.email, 
+       'email address: '||max(v.contact) as description 
+from lsmb12.vendor v 
+where v.company_id is not null and v.email is not null 
+       and v.email ~ '[[:alnum:]_]'::text 
+group by v.credit_id, v.email
+UNION
+select v.credit_id, 12, v.cc, 'Carbon Copy email address' as description 
+from lsmb12.vendor v 
+where v.company_id is not null and v.cc is not null 
+      and v.cc ~ '[[:alnum:]_]'::text 
+group by v.credit_id, v.cc
+UNION 
+select v.credit_id, 12, v.bcc, 'Blind Carbon Copy email address' as description 
+from lsmb12.vendor v 
+where v.company_id is not null and v.bcc is not null 
+       and v.bcc ~ '[[:alnum:]_]'::text 
+group by v.credit_id, v.bcc
+UNION
+    select v.credit_id, 9, v.fax, 'Fax number' as description 
+from lsmb12.vendor v 
+where v.company_id is not null and v.fax is not null 
+      and v.fax ~ '[[:alnum:]_]'::text 
+group by v.credit_id, v.fax;
+
+insert into eca_to_contact (credit_id, contact_class_id, contact,description) 
+select v.credit_id, 1, v.phone, 'Primary phone: '||max(v.contact) as description
+from lsmb12.customer v 
+where v.company_id is not null and v.phone is not null 
+       and v.phone ~ '[[:alnum:]_]'::text 
+group by v.credit_id, v.phone
+UNION
+select v.credit_id, 12, v.email, 
+       'email address: '||max(v.contact) as description 
+from lsmb12.customer v 
+where v.company_id is not null and v.email is not null 
+       and v.email ~ '[[:alnum:]_]'::text 
+group by v.credit_id, v.email
+UNION
+select v.credit_id, 12, v.cc, 'Carbon Copy email address' as description 
+from lsmb12.customer v 
+where v.company_id is not null and v.cc is not null 
+      and v.cc ~ '[[:alnum:]_]'::text 
+group by v.credit_id, v.cc
+UNION 
+select v.credit_id, 12, v.bcc, 'Blind Carbon Copy email address' as description 
+from lsmb12.customer v 
+where v.company_id is not null and v.bcc is not null 
+       and v.bcc ~ '[[:alnum:]_]'::text 
+group by v.credit_id, v.bcc
+UNION
+    select v.credit_id, 9, v.fax, 'Fax number' as description 
+from lsmb12.customer v 
+where v.company_id is not null and v.fax is not null 
+      and v.fax ~ '[[:alnum:]_]'::text 
+group by v.credit_id, v.fax;
+
+
+-- addresses
+
+INSERT INTO public.country (id, name, short_name) VALUES (-1, 'Invalid Country', 'XX');
+
+INSERT INTO eca_to_location(credit_id, location_class, location_id)
+SELECT eca.id, 1,
+    min(location_save(NULL,
+
+    case 
+        when oa.address1 = '' then 'Null' 
+        when oa.address1 is null then 'Null'
+        else oa.address1 
+    end,
+    oa.address2, 
+    NULL,
+    case 
+        when oa.city !~ '[[:alnum:]_]' then 'Invalid' 
+        when oa.city is null then 'Null' 
+        else oa.city 
+    end,
+    case 
+        when oa.state !~ '[[:alnum:]_]' then 'Invalid' 
+        when oa.state is null then 'Null' 
+        else oa.state 
+    end,
+    case 
+        when oa.zipcode !~ '[[:alnum:]_]' then 'Invalid' 
+        when oa.zipcode is null then 'Null' 
+        else oa.zipcode 
+    end,
+    coalesce(c.id, -1)
+    ))
+FROM country c
+RIGHT OUTER JOIN
+     lsmb12.vendor oa
+ON
+    lower(trim(both ' ' from c.name)) = lower( trim(both ' ' from oa.country))
+OR
+
+    lower(trim(both ' ' from c.short_name)) = lower( trim(both ' ' from oa.country))
+JOIN entity_credit_account eca ON (oa.credit_id = eca.id)
+GROUP BY eca.id;
+
+INSERT INTO eca_to_location(credit_id, location_class, location_id)
+SELECT eca.id, 1,
+    min(location_save(NULL,
+
+    case 
+        when oa.address1 = '' then 'Null' 
+        when oa.address1 is null then 'Null'
+        else oa.address1 
+    end,
+    oa.address2, 
+    NULL,
+    case 
+        when oa.city !~ '[[:alnum:]_]' then 'Invalid' 
+        when oa.city is null then 'Null' 
+        else oa.city 
+    end,
+    case 
+        when oa.state !~ '[[:alnum:]_]' then 'Invalid' 
+        when oa.state is null then 'Null' 
+        else oa.state 
+    end,
+    case 
+        when oa.zipcode !~ '[[:alnum:]_]' then 'Invalid' 
+        when oa.zipcode is null then 'Null' 
+        else oa.zipcode 
+    end,
+    coalesce(c.id, -1)
+    ))
+FROM country c
+RIGHT OUTER JOIN
+     lsmb12.customer oa
+ON
+    lower(trim(both ' ' from c.name)) = lower( trim(both ' ' from oa.country))
+OR
+
+    lower(trim(both ' ' from c.short_name)) = lower( trim(both ' ' from oa.country))
+JOIN entity_credit_account eca ON (oa.credit_id = eca.id)
+GROUP BY eca.id;
+
+-- Shipto
+
+INSERT INTO eca_to_location(credit_id, location_class, location_id)
+SELECT eca.id, 2,
+    min(location_save(NULL,
+
+    case 
+        when oa.shiptoaddress1 = '' then 'Null' 
+        when oa.shiptoaddress1 is null then 'Null'
+        else oa.shiptoaddress1 
+    end,
+    oa.shiptoaddress2, 
+    NULL,
+    case 
+        when oa.shiptocity !~ '[[:alnum:]_]' then 'Invalid' 
+        when oa.shiptocity is null then 'Null' 
+        else oa.shiptocity 
+    end,
+    case 
+        when oa.shiptostate !~ '[[:alnum:]_]' then 'Invalid' 
+        when oa.shiptostate is null then 'Null' 
+        else oa.shiptostate 
+    end,
+    case 
+        when oa.shiptozipcode !~ '[[:alnum:]_]' then 'Invalid' 
+        when oa.shiptozipcode is null then 'Null' 
+        else oa.shiptozipcode 
+    end,
+    coalesce(c.id, -1)
+    ))
+FROM country c
+RIGHT OUTER JOIN
+     lsmb12.shipto oa
+ON
+    lower(trim(both ' ' from c.name)) = lower( trim(both ' ' from oa.shiptocountry))
+OR
+
+    lower(trim(both ' ' from c.short_name)) = lower( trim(both ' ' from oa.shiptocountry))
+JOIN lsmb12.vendor ov ON (oa.trans_id = ov.id)
+JOIN entity_credit_account eca ON (ov.credit_id = eca.id)
+GROUP BY eca.id;
+
+INSERT INTO eca_to_location(credit_id, location_class, location_id)
+SELECT eca.id, 2,
+    min(location_save(NULL,
+
+    case 
+        when oa.shiptoaddress1 = '' then 'Null' 
+        when oa.shiptoaddress1 is null then 'Null'
+        else oa.shiptoaddress1 
+    end,
+    oa.shiptoaddress2, 
+    NULL,
+    case 
+        when oa.shiptocity !~ '[[:alnum:]_]' then 'Invalid' 
+        when oa.shiptocity is null then 'Null' 
+        else oa.shiptocity 
+    end,
+    case 
+        when oa.shiptostate !~ '[[:alnum:]_]' then 'Invalid' 
+        when oa.shiptostate is null then 'Null' 
+        else oa.shiptostate 
+    end,
+    case 
+        when oa.shiptozipcode !~ '[[:alnum:]_]' then 'Invalid' 
+        when oa.shiptozipcode is null then 'Null' 
+        else oa.shiptozipcode 
+    end,
+    coalesce(c.id, -1)
+    ))
+FROM country c
+RIGHT OUTER JOIN
+     lsmb12.shipto oa
+ON
+    lower(trim(both ' ' from c.name)) = lower( trim(both ' ' from oa.shiptocountry))
+OR
+
+    lower(trim(both ' ' from c.short_name)) = lower( trim(both ' ' from oa.shiptocountry))
+JOIN lsmb12.customer ov ON (oa.trans_id = ov.id)
+JOIN entity_credit_account eca ON (ov.credit_id = eca.id)
+GROUP BY eca.id;
+ 
+INSERT INTO eca_note(note_class, ref_key, note, vector)
+SELECT 3, credit_id, notes, '' FROM lsmb12.vendor 
+WHERE notes IS NOT NULL AND credit_id IS NOT NULL;
+
+INSERT INTO eca_note(note_class, ref_key, note, vector)
+SELECT 3, credit_id, notes, '' FROM lsmb12.customer
+WHERE notes IS NOT NULL AND credit_id IS NOT NULL;
+
+UPDATE entity SET country_id = 
+(select country_id FROM location l 
+   JOIN eca_to_location e2l ON l.id = e2l.location_id
+        AND e2l.location_class = 1
+   JOIN entity_credit_account eca ON e2l.credit_id = eca.id
+  WHERE eca.entity_id = entity_id
+        AND l.country_id > -1
+  LIMIT 1)
+WHERE id IN
+(select eca.entity_id FROM location l 
+   JOIN eca_to_location e2l ON l.id = e2l.location_id
+        AND e2l.location_class = 1
+   JOIN entity_credit_account eca ON e2l.credit_id = eca.id
+  WHERE eca.entity_id = entity_id
+       aND l.country_id > -1);
+
+INSERT INTO pricegroup
+SELECT * FROM lsmb12.pricegroup;
+
+ALTER TABLE lsmb12.employee ADD entity_id int;
+
+INSERT INTO entity(control_code, entity_class, country_id)
+select 'E-' || employeenumber, 3,
+        (select id from country where lower(short_name) = :default_country)
+FROM lsmb12.employee;
+
+UPDATE lsmb12.employee set entity_id = 
+       (select id from entity where 'E-'||employeenumber = control_code);
+
+INSERT INTO person (first_name, last_name, entity_id) 
+select name, name, entity_id FROM lsmb12.employee;
+
+INSERT INTO users (entity_id, username)
+     SELECT entity_id, login FROM lsmb12.employee em;
+
+INSERT 
+  INTO entity_employee(entity_id, startdate, enddate, role, ssn, sales,
+       employeenumber, dob, manager_id)
+SELECT entity_id, startdate, enddate, role, ssn, sales, employeenumber, dob,
+       (select entity_id from lsmb12.employee where id = em.managerid)
+  FROM lsmb12.employee em;
+
+
+
+-- must rebuild this table due to changes since 1.2
+
+INSERT INTO makemodel
+SELECT * FROM lsmb12.makemodel;
+
+INSERT INTO gifi
+SELECT * FROM lsmb12.gifi;
+
+UPDATE defaults 
+   SET value = (select value from lsmb12.defaults src 
+                 WHERE src.setting_key = defaults.setting_key)
+ WHERE setting_key IN (select setting_key FROM lsmb12.defaults);
+
+
+INSERT INTO parts SELECT * FROM lsmb12.parts;
+
+INSERT INTO assembly SELECT * FROM lsmb12.assembly;
+
+ALTER TABLE gl DISABLE TRIGGER gl_audit_trail;
+
+INSERT INTO gl(id, reference, description, transdate, person_id, notes, 
+               department_id)
+    SELECT gl.id, reference, description, transdate, p.id, gl.notes, 
+           department_id
+      FROM lsmb12.gl 
+ LEFT JOIN lsmb12.employee em ON gl.employee_id = em.id
+ LEFT JOIN person p ON em.entity_id = p.id;
+
+ALTER TABLE gl ENABLE TRIGGER gl_audit_trail;
+
+ALTER TABLE ar DISABLE TRIGGER ar_audit_trail;
+
+INSERT INTO ar(id, invnumber, transdate, taxincluded, amount, 
+            netamount, paid, datepaid, duedate, invoice, shippingpoint, terms,
+            notes, curr, ordnumber, person_id, till, quonumber, intnotes, 
+            department_id, shipvia, language_code, ponumber, 
+            entity_credit_account)
+     SELECT ar.id, invnumber, transdate, ar.taxincluded, amount, netamount, 
+            paid, datepaid, duedate, invoice, shippingpoint, ar.terms, ar.notes,
+            ar.curr, ordnumber, em.entity_id, till, quonumber, intnotes, 
+            department_id, shipvia, ar.language_code, ponumber, credit_id
+       FROM lsmb12.ar
+       JOIN lsmb12.customer c ON c.id = ar.customer_id
+  LEFT JOIN lsmb12.employee em ON em.id = ar.employee_id;
+
+ALTER TABLE ar ENABLE TRIGGER ar_audit_trail;
+
+ALTER TABLE ap DISABLE TRIGGER ap_audit_trail;
+
+INSERT INTO ap(id, invnumber, transdate, taxincluded, amount, 
+            netamount, paid, datepaid, duedate, invoice, shippingpoint, terms,
+            notes, curr, ordnumber, person_id, till, quonumber, intnotes, 
+            department_id, shipvia, language_code, ponumber, 
+            entity_credit_account)
+     SELECT ap.id, invnumber, transdate, ap.taxincluded, amount, netamount, 
+            paid, datepaid, duedate, invoice, shippingpoint, ap.terms, ap.notes,
+            ap.curr, ordnumber, em.entity_id, till, quonumber, intnotes, 
+            department_id, shipvia, ap.language_code, ponumber, credit_id
+       FROM lsmb12.ap
+       JOIN lsmb12.vendor c ON c.id = ap.vendor_id
+  LEFT JOIN lsmb12.employee em ON em.id = ap.employee_id;
+
+ALTER TABLE ap ENABLE TRIGGER ap_audit_trail;
+
+INSERT INTO acc_trans(trans_id, chart_id, amount, transdate, source, cleared,
+            fx_transaction, project_id, memo, invoice_id, entry_id)
+     SELECT trans_id, a.id, amount, transdate, source, cleared,
+            fx_transaction, project_id, memo, invoice_id, entry_id
+       FROM lsmb12.acc_trans
+       JOIN lsmb12.chart ON acc_trans.chart_id = chart.id
+       JOIN account a ON chart.accno = a.accno; 
+
+INSERT INTO invoice (id, trans_id, parts_id, description, qty, allocated,
+            sellprice, fxsellprice, discount, assemblyitem, unit, project_id,
+            deliverydate, serialnumber, notes)
+    SELECT  id, trans_id, parts_id, description, qty, allocated,
+            sellprice, fxsellprice, discount, assemblyitem, unit, project_id,
+            deliverydate, serialnumber, notes
+       FROM lsmb12.invoice;
+
+INSERT INTO partstax (parts_id, chart_id)
+     SELECT parts_id, a.id
+       FROM lsmb12.partstax pt
+       JOIN lsmb12.chart ON chart.id = pt.chart_id
+       JOIN account a ON chart.accno = a.accno;
+
+INSERT INTO tax(chart_id, rate, taxnumber, validto, pass, taxmodule_id)
+     SELECT a.id, t.rate, t.taxnumber, 
+            coalesce(t.validto::timestamp, 'infinity'), pass, taxmodule_id
+       FROM lsmb12.tax t
+       JOIN lsmb12.chart c ON (t.chart_id = c.id)
+       JOIN account a ON (a.accno = c.accno);
+
+INSERT INTO customertax (customer_id, chart_id)
+     SELECT c.credit_id,  a.id
+       FROM lsmb12.customertax pt
+       JOIN lsmb12.customer c ON (pt.customer_id = c.id)
+       JOIN lsmb12.chart ON chart.id = pt.chart_id
+       JOIN account a ON chart.accno = a.accno; 
+
+INSERT INTO vendortax (vendor_id, chart_id)
+     SELECT c.credit_id,  a.id
+       FROM lsmb12.vendortax pt       
+       JOIN lsmb12.vendor c ON (pt.vendor_id = c.id)
+       JOIN lsmb12.chart ON chart.id = pt.chart_id
+       JOIN account a ON chart.accno = a.accno;
+
+INSERT 
+  INTO oe(id, ordnumber, transdate, amount, netamount, reqdate, taxincluded,
+       shippingpoint, notes, curr, person_id, closed, quotation, quonumber,
+       intnotes, department_id, shipvia, language_code, ponumber, terms,
+       entity_credit_account, oe_class_id)
+SELECT oe.id,  ordnumber, transdate, amount, netamount, reqdate, oe.taxincluded,
+       shippingpoint, oe.notes, oe.curr, p.id, closed, quotation, quonumber,
+       intnotes, department_id, shipvia, oe.language_code, ponumber, oe.terms,
+       coalesce(c.credit_id, v.credit_id),
+       case 
+           when c.id is not null and quotation is not true THEN 1
+           WHEN v.id is not null and quotation is not true THEN 2
+           when c.id is not null and quotation is true THEN 3
+           WHEN v.id is not null and quotation is true THEN 4
+       end
+  FROM lsmb12.oe
+  LEFT JOIN lsmb12.customer c ON c.id = oe.customer_id
+  LEFT JOIN lsmb12.vendor v ON v.id = oe.vendor_id
+  LEFT JOIN lsmb12.employee e ON oe.employee_id = e.id
+  LEFT JOIN person p ON e.entity_id = p.id;
+
+INSERT INTO orderitems(id, trans_id, parts_id, description, qty, sellprice,
+            discount, unit, project_id, reqdate, ship, serialnumber, notes)
+     SELECT id, trans_id, parts_id, description, qty, sellprice,
+            discount, unit, project_id, reqdate, ship, serialnumber, notes
+       FROM lsmb12.orderitems;
+
+INSERT INTO exchangerate select * from lsmb12.exchangerate;
+
+INSERT INTO project (id, projectnumber, description, startdate, enddate,
+            parts_id, production, completed, credit_id)
+     SELECT p.id, projectnumber, description, p.startdate, p.enddate,
+            parts_id, production, completed, c.credit_id
+       FROM lsmb12.project p
+       JOIN lsmb12.customer c ON p.customer_id = c.id;
+
+INSERT INTO partsgroup SELECT * FROM lsmb12.partsgroup;
+
+INSERT INTO status SELECT * FROM lsmb12.status;
+
+INSERT INTO department SELECT * FROM lsmb12.department;
+
+INSERT INTO business SELECT * FROM lsmb12.business;
+
+INSERT INTO sic SELECT * FROM lsmb12.sic;
+
+INSERT INTO warehouse SELECT * FROM lsmb12.warehouse;
+
+INSERT INTO inventory(entity_id, warehouse_id, parts_id, trans_id,
+            orderitems_id, qty, shippingdate, entry_id)
+     SELECT e.entity_id, warehouse_id, parts_id, trans_id,
+            orderitems_id, qty, shippingdate, i.entry_id
+       FROM lsmb12.inventory i
+       JOIN lsmb12.employee e ON i.employee_id = e.id;
+
+INSERT INTO yearend (trans_id, transdate) SELECT * FROM lsmb12.yearend;
+
+INSERT INTO partsvendor(credit_id, parts_id, partnumber, leadtime, lastcost,
+            curr, entry_id)
+     SELECT v.credit_id, parts_id, partnumber, leadtime, lastcost,
+            pv.curr, entry_id
+       FROM lsmb12.partsvendor pv
+       JOIN lsmb12.vendor v ON v.id = pv.vendor_id;
+
+INSERT INTO partscustomer(parts_id, credit_id, pricegroup_id, pricebreak,
+            sellprice, validfrom, validto, curr, entry_id)
+     SELECT parts_id, credit_id, pv.pricegroup_id, pricebreak,
+            sellprice, validfrom, validto, pv.curr, entry_id
+       FROM lsmb12.partscustomer pv
+       JOIN lsmb12.customer v ON v.id = pv.customer_id;
+
+INSERT INTO language SELECT * FROM lsmb12.language;
+
+INSERT INTO audittrail(trans_id, tablename, reference, formname, action,
+            transdate, person_id, entry_id)
+     SELECT trans_id, tablename, reference, formname, action,
+            transdate, p.id, entry_id
+       FROM lsmb12.audittrail a
+       JOIN lsmb12.employee e ON a.employee_id = e.id
+       JOIN person p on e.entity_id = p.entity_id;
+
+INSERT INTO user_preference(id)
+     SELECT id from users;
+
+INSERT INTO recurring SELECT * FROM lsmb12.recurring;
+
+INSERT INTO recurringemail SELECT * FROM lsmb12.recurringemail;
+
+INSERT INTO recurringprint SELECT * FROM lsmb12.recurringprint;
+
+INSERT INTO jcitems(id, project_id, parts_id, description, qty, allocated,
+            sellprice, fxsellprice, serialnumber, checkedin, checkedout,
+            person_id, notes)
+     SELECT j.id,  project_id, parts_id, description, qty, allocated,
+            sellprice, fxsellprice, serialnumber, checkedin, checkedout,
+            p.id, j.notes
+       FROM lsmb12.jcitems j
+       JOIN lsmb12.employee e ON j.employee_id = e.id
+       JOIN person p ON e.entity_id = p.entity_id;
+
+INSERT INTO  custom_table_catalog  SELECT * FROM lsmb12. custom_table_catalog;
+
+INSERT INTO  custom_field_catalog  SELECT * FROM lsmb12. custom_field_catalog;
+
+INSERT INTO parts_translation SELECT * FROM lsmb12.translation where trans_id in (select id from parts);
+
+INSERT INTO partsgroup_translation SELECT * FROM lsmb12.translation where trans_id in
+ (select id from partsgroup);
+
+INSERT INTO project_translation SELECT * FROM lsmb12.translation where trans_id in
+ (select id from project);
+
+SELECT setval('id', max(id)) FROM transactions;
+
+ SELECT setval('acc_trans_entry_id_seq', max(entry_id)) FROM acc_trans;
+ SELECT setval('partsvendor_entry_id_seq', max(entry_id)) FROM partsvendor;
+ SELECT setval('inventory_entry_id_seq', max(entry_id)) FROM inventory;
+ SELECT setval('partscustomer_entry_id_seq', max(entry_id)) FROM partscustomer;
+ SELECT setval('audittrail_entry_id_seq', max(entry_id)) FROM audittrail;
+ SELECT setval('account_id_seq', max(id)) FROM account;
+ SELECT setval('account_heading_id_seq', max(id)) FROM account_heading;
+ SELECT setval('account_checkpoint_id_seq', max(id)) FROM account_checkpoint;
+ SELECT setval('pricegroup_id_seq', max(id)) FROM pricegroup;
+ SELECT setval('country_id_seq', max(id)) FROM country;
+ SELECT setval('country_tax_form_id_seq', max(id)) FROM country_tax_form;
+ SELECT setval('asset_dep_method_id_seq', max(id)) FROM asset_dep_method;
+ SELECT setval('asset_class_id_seq', max(id)) FROM asset_class;
+ SELECT setval('entity_class_id_seq', max(id)) FROM entity_class;
+ SELECT setval('asset_item_id_seq', max(id)) FROM asset_item;
+ SELECT setval('asset_disposal_method_id_seq', max(id)) FROM asset_disposal_method;
+ SELECT setval('users_id_seq', max(id)) FROM users;
+ SELECT setval('entity_id_seq', max(id)) FROM entity;
+ SELECT setval('company_id_seq', max(id)) FROM company;
+ SELECT setval('location_id_seq', max(id)) FROM location;
+ SELECT setval('open_forms_id_seq', max(id)) FROM open_forms;
+ SELECT setval('location_class_id_seq', max(id)) FROM location_class;
+ SELECT setval('asset_report_id_seq', max(id)) FROM asset_report;
+ SELECT setval('salutation_id_seq', max(id)) FROM salutation;
+ SELECT setval('person_id_seq', max(id)) FROM person;
+ SELECT setval('contact_class_id_seq', max(id)) FROM contact_class;
+ SELECT setval('entity_credit_account_id_seq', max(id)) FROM entity_credit_account;
+ SELECT setval('entity_bank_account_id_seq', max(id)) FROM entity_bank_account;
+ SELECT setval('note_class_id_seq', max(id)) FROM note_class;
+ SELECT setval('note_id_seq', max(id)) FROM note;
+ SELECT setval('batch_class_id_seq', max(id)) FROM batch_class;
+ SELECT setval('batch_id_seq', max(id)) FROM batch;
+ SELECT setval('invoice_id_seq', max(id)) FROM invoice;
+ SELECT setval('voucher_id_seq', max(id)) FROM voucher;
+ SELECT setval('parts_id_seq', max(id)) FROM parts;
+ SELECT setval('taxmodule_taxmodule_id_seq', max(taxmodule_id)) FROM taxmodule;
+ SELECT setval('taxcategory_taxcategory_id_seq', max(taxcategory_id)) FROM taxcategory;
+ SELECT setval('oe_id_seq', max(id)) FROM oe;
+ SELECT setval('orderitems_id_seq', max(id)) FROM orderitems;
+ SELECT setval('business_id_seq', max(id)) FROM business;
+ SELECT setval('warehouse_id_seq', max(id)) FROM warehouse;
+ SELECT setval('partsgroup_id_seq', max(id)) FROM partsgroup;
+ SELECT setval('project_id_seq', max(id)) FROM project;
+ SELECT setval('department_id_seq', max(id)) FROM department;
+ SELECT setval('jcitems_id_seq', max(id)) FROM jcitems;
+ SELECT setval('payment_type_id_seq', max(id)) FROM payment_type;
+ SELECT setval('custom_table_catalog_table_id_seq', max(table_id)) FROM custom_table_catalog;
+ SELECT setval('custom_field_catalog_field_id_seq', max(field_id)) FROM custom_field_catalog;
+ SELECT setval('menu_node_id_seq', max(id)) FROM menu_node;
+ SELECT setval('menu_attribute_id_seq', max(id)) FROM menu_attribute;
+ SELECT setval('menu_acl_id_seq', max(id)) FROM menu_acl;
+ SELECT setval('pending_job_id_seq', max(id)) FROM pending_job;
+ SELECT setval('new_shipto_id_seq', max(id)) FROM new_shipto;
+ SELECT setval('payment_id_seq', max(id)) FROM payment;
+ SELECT setval('cr_report_id_seq', max(id)) FROM cr_report;
+ SELECT setval('cr_report_line_id_seq', max(id)) FROM cr_report_line;
+
+UPDATE defaults SET value = '1.2.99' WHERE setting_key = 'version';
+
+
+COMMIT;
+--TODO:  Translation migratiion.  Partsgroups?
+-- TODO:  User/password Migration

Deleted: trunk/sql/upgrade/1.2-1.3-work-in-progress.sql
===================================================================
--- trunk/sql/upgrade/1.2-1.3-work-in-progress.sql	2011-09-02 17:56:14 UTC (rev 3693)
+++ trunk/sql/upgrade/1.2-1.3-work-in-progress.sql	2011-09-09 20:41:41 UTC (rev 3694)
@@ -1,671 +0,0 @@
---Setup
-
--- When moved to an interface, these will all be specified and preprocessed.
-\set lsmbdir `pwd`
-\set contribdir '/usr/share/pgsql/contrib'
-\set default_country '''us'''
-\set ar '''1200'''
-\set ap '''2100'''
-
--- This will be moved out of this part.
-ALTER SCHEMA public RENAME TO lsmb12;
-CREATE SCHEMA public;
-
-\cd :contribdir
-\i pg_trgm.sql
-\i tsearch2.sql
-\i tablefunc.sql
-
-\cd :lsmbdir
-
--- Full module load should be part of upgrade wizard, at this stage.
-\i sql/Pg-database.sql
-\i sql/modules/Setting.sql
-\i sql/modules/Location.sql
-\i sql/modules/Account.sql
-\i sql/modules/Payment.sql
-\i sql/modules/Person.sql
-\i sql/modules/Reconciliation.sql
-BEGIN;
-
--- adding mapping info for import.
-
-ALTER TABLE lsmb12.vendor ADD COLUMN entity_id int;
-ALTER TABLE lsmb12.vendor ADD COLUMN company_id int;
-ALTER TABLE lsmb12.vendor ADD COLUMN credit_id int;
-
-ALTER TABLE lsmb12.customer ADD COLUMN entity_id int;
-ALTER TABLE lsmb12.customer ADD COLUMN company_id int;
-ALTER TABLE lsmb12.customer ADD COLUMN credit_id int;
-
-
---Accounts
-INSERT INTO account_heading(id, accno, description)
-SELECT id, accno, description
-  FROM lsmb12.chart WHERE charttype = 'H';
-
-SELECT account_save(id, accno, description, category, gifi_accno, NULL, contra, 
-                    CASE WHEN link like '%tax%' THEN true ELSE false END, 
-                    string_to_array(link,':'))
-  FROM lsmb12.chart 
- WHERE charttype = 'A';
---Entity
-
-INSERT INTO entity (name, control_code, entity_class, country_id)
-SELECT name, 'V-' || vendornumber, 1, 
-       (select id from country 
-         where lower(short_name)  = :default_country)
-FROM lsmb12.vendor
-GROUP BY name, vendornumber;
-
-INSERT INTO entity (name, control_code, entity_class, country_id)
-SELECT name, 'C-' || customernumber, 2, 
-       (select id from country 
-         where lower(short_name)  =  :default_country)
-FROM lsmb12.customer
-GROUP BY name, customernumber;
-
-UPDATE lsmb12.vendor SET entity_id = (SELECT id FROM entity WHERE 'V-' || vendornumber = control_code);
-
-UPDATE lsmb12.customer SET entity_id = coalesce((SELECT min(id) FROM entity WHERE 'C-' || customernumber = control_code), entity_id);
-
---Entity Credit Account
-
-INSERT INTO entity_credit_account
-(entity_id, meta_number, business_id, creditlimit, ar_ap_account_id, 
-	cash_account_id, startdate, enddate, threshold, entity_class)
-SELECT entity_id, vendornumber, business_id, creditlimit, 
-       (select id from account where accno = :ap), 
-	NULL, startdate, enddate, 0, 1
-FROM lsmb12.vendor WHERE entity_id IS NOT NULL;
-
-UPDATE lsmb12.vendor SET credit_id = 
-	(SELECT id FROM entity_credit_account e 
-	WHERE e.meta_number = vendornumber);
-
-
-INSERT INTO entity_credit_account
-(entity_id, meta_number, business_id, creditlimit, ar_ap_account_id, 
-	cash_account_id, startdate, enddate, threshold, entity_class)
-SELECT entity_id, customernumber, business_id, creditlimit,
-       (select id from account where accno = :ar),
-	NULL, startdate, enddate, 0, 2
-FROM lsmb12.customer WHERE entity_id IS NOT NULL;
-
-UPDATE lsmb12.customer SET credit_id = 
-	(SELECT id FROM entity_credit_account e 
-	WHERE e.meta_number = customernumber AND customer.entity_id = e.entity_id);
-
---Company
-
-INSERT INTO company (entity_id, legal_name, tax_id)
-SELECT entity_id, name, max(taxnumber) FROM lsmb12.vendor 
-WHERE entity_id IS NOT NULL AND entity_id IN (select id from entity) GROUP BY entity_id, name;
-
-UPDATE lsmb12.vendor SET company_id = (select id from company c where entity_id = vendor.entity_id);
-
-INSERT INTO company (entity_id, legal_name, tax_id)
-SELECT entity_id, name, max(taxnumber) FROM lsmb12.customer
-WHERE entity_id IS NOT NULL AND entity_id IN (select id from entity) GROUP BY entity_id, name;
-
-UPDATE lsmb12.customer SET company_id = (select id from company c where entity_id = customer.entity_id);
-
--- Contact
-
-insert into eca_to_contact (credit_id, contact_class_id, contact,description) 
-select v.credit_id, 1, v.phone, 'Primary phone: '||max(v.contact) as description
-from lsmb12.vendor v 
-where v.company_id is not null and v.phone is not null 
-       and v.phone ~ '[[:alnum:]_]'::text 
-group by v.credit_id, v.phone
-UNION
-select v.credit_id, 12, v.email, 
-       'email address: '||max(v.contact) as description 
-from lsmb12.vendor v 
-where v.company_id is not null and v.email is not null 
-       and v.email ~ '[[:alnum:]_]'::text 
-group by v.credit_id, v.email
-UNION
-select v.credit_id, 12, v.cc, 'Carbon Copy email address' as description 
-from lsmb12.vendor v 
-where v.company_id is not null and v.cc is not null 
-      and v.cc ~ '[[:alnum:]_]'::text 
-group by v.credit_id, v.cc
-UNION 
-select v.credit_id, 12, v.bcc, 'Blind Carbon Copy email address' as description 
-from lsmb12.vendor v 
-where v.company_id is not null and v.bcc is not null 
-       and v.bcc ~ '[[:alnum:]_]'::text 
-group by v.credit_id, v.bcc
-UNION
-    select v.credit_id, 9, v.fax, 'Fax number' as description 
-from lsmb12.vendor v 
-where v.company_id is not null and v.fax is not null 
-      and v.fax ~ '[[:alnum:]_]'::text 
-group by v.credit_id, v.fax;
-
-insert into eca_to_contact (credit_id, contact_class_id, contact,description) 
-select v.credit_id, 1, v.phone, 'Primary phone: '||max(v.contact) as description
-from lsmb12.customer v 
-where v.company_id is not null and v.phone is not null 
-       and v.phone ~ '[[:alnum:]_]'::text 
-group by v.credit_id, v.phone
-UNION
-select v.credit_id, 12, v.email, 
-       'email address: '||max(v.contact) as description 
-from lsmb12.customer v 
-where v.company_id is not null and v.email is not null 
-       and v.email ~ '[[:alnum:]_]'::text 
-group by v.credit_id, v.email
-UNION
-select v.credit_id, 12, v.cc, 'Carbon Copy email address' as description 
-from lsmb12.customer v 
-where v.company_id is not null and v.cc is not null 
-      and v.cc ~ '[[:alnum:]_]'::text 
-group by v.credit_id, v.cc
-UNION 
-select v.credit_id, 12, v.bcc, 'Blind Carbon Copy email address' as description 
-from lsmb12.customer v 
-where v.company_id is not null and v.bcc is not null 
-       and v.bcc ~ '[[:alnum:]_]'::text 
-group by v.credit_id, v.bcc
-UNION
-    select v.credit_id, 9, v.fax, 'Fax number' as description 
-from lsmb12.customer v 
-where v.company_id is not null and v.fax is not null 
-      and v.fax ~ '[[:alnum:]_]'::text 
-group by v.credit_id, v.fax;
-
-
--- addresses
-
-INSERT INTO public.country (id, name, short_name) VALUES (-1, 'Invalid Country', 'XX');
-
-INSERT INTO eca_to_location(credit_id, location_class, location_id)
-SELECT eca.id, 1,
-    min(location_save(NULL,
-
-    case 
-        when oa.address1 = '' then 'Null' 
-        when oa.address1 is null then 'Null'
-        else oa.address1 
-    end,
-    oa.address2, 
-    NULL,
-    case 
-        when oa.city !~ '[[:alnum:]_]' then 'Invalid' 
-        when oa.city is null then 'Null' 
-        else oa.city 
-    end,
-    case 
-        when oa.state !~ '[[:alnum:]_]' then 'Invalid' 
-        when oa.state is null then 'Null' 
-        else oa.state 
-    end,
-    case 
-        when oa.zipcode !~ '[[:alnum:]_]' then 'Invalid' 
-        when oa.zipcode is null then 'Null' 
-        else oa.zipcode 
-    end,
-    coalesce(c.id, -1)
-    ))
-FROM country c
-RIGHT OUTER JOIN
-     lsmb12.vendor oa
-ON
-    lower(trim(both ' ' from c.name)) = lower( trim(both ' ' from oa.country))
-OR
-
-    lower(trim(both ' ' from c.short_name)) = lower( trim(both ' ' from oa.country))
-JOIN entity_credit_account eca ON (oa.credit_id = eca.id)
-GROUP BY eca.id;
-
-INSERT INTO eca_to_location(credit_id, location_class, location_id)
-SELECT eca.id, 1,
-    min(location_save(NULL,
-
-    case 
-        when oa.address1 = '' then 'Null' 
-        when oa.address1 is null then 'Null'
-        else oa.address1 
-    end,
-    oa.address2, 
-    NULL,
-    case 
-        when oa.city !~ '[[:alnum:]_]' then 'Invalid' 
-        when oa.city is null then 'Null' 
-        else oa.city 
-    end,
-    case 
-        when oa.state !~ '[[:alnum:]_]' then 'Invalid' 
-        when oa.state is null then 'Null' 
-        else oa.state 
-    end,
-    case 
-        when oa.zipcode !~ '[[:alnum:]_]' then 'Invalid' 
-        when oa.zipcode is null then 'Null' 
-        else oa.zipcode 
-    end,
-    coalesce(c.id, -1)
-    ))
-FROM country c
-RIGHT OUTER JOIN
-     lsmb12.customer oa
-ON
-    lower(trim(both ' ' from c.name)) = lower( trim(both ' ' from oa.country))
-OR
-
-    lower(trim(both ' ' from c.short_name)) = lower( trim(both ' ' from oa.country))
-JOIN entity_credit_account eca ON (oa.credit_id = eca.id)
-GROUP BY eca.id;
-
--- Shipto
-
-INSERT INTO eca_to_location(credit_id, location_class, location_id)
-SELECT eca.id, 2,
-    min(location_save(NULL,
-
-    case 
-        when oa.shiptoaddress1 = '' then 'Null' 
-        when oa.shiptoaddress1 is null then 'Null'
-        else oa.shiptoaddress1 
-    end,
-    oa.shiptoaddress2, 
-    NULL,
-    case 
-        when oa.shiptocity !~ '[[:alnum:]_]' then 'Invalid' 
-        when oa.shiptocity is null then 'Null' 
-        else oa.shiptocity 
-    end,
-    case 
-        when oa.shiptostate !~ '[[:alnum:]_]' then 'Invalid' 
-        when oa.shiptostate is null then 'Null' 
-        else oa.shiptostate 
-    end,
-    case 
-        when oa.shiptozipcode !~ '[[:alnum:]_]' then 'Invalid' 
-        when oa.shiptozipcode is null then 'Null' 
-        else oa.shiptozipcode 
-    end,
-    coalesce(c.id, -1)
-    ))
-FROM country c
-RIGHT OUTER JOIN
-     lsmb12.shipto oa
-ON
-    lower(trim(both ' ' from c.name)) = lower( trim(both ' ' from oa.shiptocountry))
-OR
-
-    lower(trim(both ' ' from c.short_name)) = lower( trim(both ' ' from oa.shiptocountry))
-JOIN lsmb12.vendor ov ON (oa.trans_id = ov.id)
-JOIN entity_credit_account eca ON (ov.credit_id = eca.id)
-GROUP BY eca.id;
-
-INSERT INTO eca_to_location(credit_id, location_class, location_id)
-SELECT eca.id, 2,
-    min(location_save(NULL,
-
-    case 
-        when oa.shiptoaddress1 = '' then 'Null' 
-        when oa.shiptoaddress1 is null then 'Null'
-        else oa.shiptoaddress1 
-    end,
-    oa.shiptoaddress2, 
-    NULL,
-    case 
-        when oa.shiptocity !~ '[[:alnum:]_]' then 'Invalid' 
-        when oa.shiptocity is null then 'Null' 
-        else oa.shiptocity 
-    end,
-    case 
-        when oa.shiptostate !~ '[[:alnum:]_]' then 'Invalid' 
-        when oa.shiptostate is null then 'Null' 
-        else oa.shiptostate 
-    end,
-    case 
-        when oa.shiptozipcode !~ '[[:alnum:]_]' then 'Invalid' 
-        when oa.shiptozipcode is null then 'Null' 
-        else oa.shiptozipcode 
-    end,
-    coalesce(c.id, -1)
-    ))
-FROM country c
-RIGHT OUTER JOIN
-     lsmb12.shipto oa
-ON
-    lower(trim(both ' ' from c.name)) = lower( trim(both ' ' from oa.shiptocountry))
-OR
-
-    lower(trim(both ' ' from c.short_name)) = lower( trim(both ' ' from oa.shiptocountry))
-JOIN lsmb12.customer ov ON (oa.trans_id = ov.id)
-JOIN entity_credit_account eca ON (ov.credit_id = eca.id)
-GROUP BY eca.id;
- 
-INSERT INTO eca_note(note_class, ref_key, note, vector)
-SELECT 3, credit_id, notes, '' FROM lsmb12.vendor 
-WHERE notes IS NOT NULL AND credit_id IS NOT NULL;
-
-INSERT INTO eca_note(note_class, ref_key, note, vector)
-SELECT 3, credit_id, notes, '' FROM lsmb12.customer
-WHERE notes IS NOT NULL AND credit_id IS NOT NULL;
-
-UPDATE entity SET country_id = 
-(select country_id FROM location l 
-   JOIN eca_to_location e2l ON l.id = e2l.location_id
-        AND e2l.location_class = 1
-   JOIN entity_credit_account eca ON e2l.credit_id = eca.id
-  WHERE eca.entity_id = entity_id
-        AND l.country_id > -1
-  LIMIT 1)
-WHERE id IN
-(select eca.entity_id FROM location l 
-   JOIN eca_to_location e2l ON l.id = e2l.location_id
-        AND e2l.location_class = 1
-   JOIN entity_credit_account eca ON e2l.credit_id = eca.id
-  WHERE eca.entity_id = entity_id
-       aND l.country_id > -1);
-
-INSERT INTO pricegroup
-SELECT * FROM lsmb12.pricegroup;
-
-ALTER TABLE lsmb12.employee ADD entity_id int;
-
-INSERT INTO entity(control_code, entity_class, country_id)
-select 'E-' || employeenumber, 3,
-        (select id from country where lower(short_name) = :default_country)
-FROM lsmb12.employee;
-
-UPDATE lsmb12.employee set entity_id = 
-       (select id from entity where 'E-'||employeenumber = control_code);
-
-INSERT INTO person (first_name, last_name, entity_id) 
-select name, name, entity_id FROM lsmb12.employee;
-
-INSERT INTO users (entity_id, username)
-     SELECT entity_id, login FROM lsmb12.employee em;
-
-INSERT 
-  INTO entity_employee(entity_id, startdate, enddate, role, ssn, sales,
-       employeenumber, dob, manager_id)
-SELECT entity_id, startdate, enddate, role, ssn, sales, employeenumber, dob,
-       (select entity_id from lsmb12.employee where id = em.managerid)
-  FROM lsmb12.employee em;
-
-
-
--- must rebuild this table due to changes since 1.2
-
-INSERT INTO makemodel
-SELECT * FROM lsmb12.makemodel;
-
-INSERT INTO gifi
-SELECT * FROM lsmb12.gifi;
-
-UPDATE defaults 
-   SET value = (select value from lsmb12.defaults src 
-                 WHERE src.setting_key = defaults.setting_key)
- WHERE setting_key IN (select setting_key FROM lsmb12.defaults);
-
-
-INSERT INTO parts SELECT * FROM lsmb12.parts;
-
-INSERT INTO assembly SELECT * FROM lsmb12.assembly;
-
-ALTER TABLE gl DISABLE TRIGGER gl_audit_trail;
-
-INSERT INTO gl(id, reference, description, transdate, person_id, notes, 
-               department_id)
-    SELECT gl.id, reference, description, transdate, p.id, gl.notes, 
-           department_id
-      FROM lsmb12.gl 
- LEFT JOIN lsmb12.employee em ON gl.employee_id = em.id
- LEFT JOIN person p ON em.entity_id = p.id;
-
-ALTER TABLE gl ENABLE TRIGGER gl_audit_trail;
-
-ALTER TABLE ar DISABLE TRIGGER ar_audit_trail;
-
-INSERT INTO ar(id, invnumber, transdate, taxincluded, amount, 
-            netamount, paid, datepaid, duedate, invoice, shippingpoint, terms,
-            notes, curr, ordnumber, person_id, till, quonumber, intnotes, 
-            department_id, shipvia, language_code, ponumber, 
-            entity_credit_account)
-     SELECT ar.id, invnumber, transdate, ar.taxincluded, amount, netamount, 
-            paid, datepaid, duedate, invoice, shippingpoint, ar.terms, ar.notes,
-            ar.curr, ordnumber, em.entity_id, till, quonumber, intnotes, 
-            department_id, shipvia, ar.language_code, ponumber, credit_id
-       FROM lsmb12.ar
-       JOIN lsmb12.customer c ON c.id = ar.customer_id
-  LEFT JOIN lsmb12.employee em ON em.id = ar.employee_id;
-
-ALTER TABLE ar ENABLE TRIGGER ar_audit_trail;
-
-ALTER TABLE ap DISABLE TRIGGER ap_audit_trail;
-
-INSERT INTO ap(id, invnumber, transdate, taxincluded, amount, 
-            netamount, paid, datepaid, duedate, invoice, shippingpoint, terms,
-            notes, curr, ordnumber, person_id, till, quonumber, intnotes, 
-            department_id, shipvia, language_code, ponumber, 
-            entity_credit_account)
-     SELECT ap.id, invnumber, transdate, ap.taxincluded, amount, netamount, 
-            paid, datepaid, duedate, invoice, shippingpoint, ap.terms, ap.notes,
-            ap.curr, ordnumber, em.entity_id, till, quonumber, intnotes, 
-            department_id, shipvia, ap.language_code, ponumber, credit_id
-       FROM lsmb12.ap
-       JOIN lsmb12.vendor c ON c.id = ap.vendor_id
-  LEFT JOIN lsmb12.employee em ON em.id = ap.employee_id;
-
-ALTER TABLE ap ENABLE TRIGGER ap_audit_trail;
-
-INSERT INTO acc_trans(trans_id, chart_id, amount, transdate, source, cleared,
-            fx_transaction, project_id, memo, invoice_id, entry_id)
-     SELECT trans_id, a.id, amount, transdate, source, cleared,
-            fx_transaction, project_id, memo, invoice_id, entry_id
-       FROM lsmb12.acc_trans
-       JOIN lsmb12.chart ON acc_trans.chart_id = chart.id
-       JOIN account a ON chart.accno = a.accno; 
-
-INSERT INTO invoice (id, trans_id, parts_id, description, qty, allocated,
-            sellprice, fxsellprice, discount, assemblyitem, unit, project_id,
-            deliverydate, serialnumber, notes)
-    SELECT  id, trans_id, parts_id, description, qty, allocated,
-            sellprice, fxsellprice, discount, assemblyitem, unit, project_id,
-            deliverydate, serialnumber, notes
-       FROM lsmb12.invoice;
-
-INSERT INTO partstax (parts_id, chart_id)
-     SELECT parts_id, a.id
-       FROM lsmb12.partstax pt
-       JOIN lsmb12.chart ON chart.id = pt.chart_id
-       JOIN account a ON chart.accno = a.accno;
-
-INSERT INTO tax(chart_id, rate, taxnumber, validto, pass, taxmodule_id)
-     SELECT a.id, t.rate, t.taxnumber, 
-            coalesce(t.validto::timestamp, 'infinity'), pass, taxmodule_id
-       FROM lsmb12.tax t
-       JOIN lsmb12.chart c ON (t.chart_id = c.id)
-       JOIN account a ON (a.accno = c.accno);
-
-INSERT INTO customertax (customer_id, chart_id)
-     SELECT c.credit_id,  a.id
-       FROM lsmb12.customertax pt
-       JOIN lsmb12.customer c ON (pt.customer_id = c.id)
-       JOIN lsmb12.chart ON chart.id = pt.chart_id
-       JOIN account a ON chart.accno = a.accno; 
-
-INSERT INTO vendortax (vendor_id, chart_id)
-     SELECT c.credit_id,  a.id
-       FROM lsmb12.vendortax pt       
-       JOIN lsmb12.vendor c ON (pt.vendor_id = c.id)
-       JOIN lsmb12.chart ON chart.id = pt.chart_id
-       JOIN account a ON chart.accno = a.accno;
-
-INSERT 
-  INTO oe(id, ordnumber, transdate, amount, netamount, reqdate, taxincluded,
-       shippingpoint, notes, curr, person_id, closed, quotation, quonumber,
-       intnotes, department_id, shipvia, language_code, ponumber, terms,
-       entity_credit_account, oe_class_id)
-SELECT oe.id,  ordnumber, transdate, amount, netamount, reqdate, oe.taxincluded,
-       shippingpoint, oe.notes, oe.curr, p.id, closed, quotation, quonumber,
-       intnotes, department_id, shipvia, oe.language_code, ponumber, oe.terms,
-       coalesce(c.credit_id, v.credit_id),
-       case 
-           when c.id is not null and quotation is not true THEN 1
-           WHEN v.id is not null and quotation is not true THEN 2
-           when c.id is not null and quotation is true THEN 3
-           WHEN v.id is not null and quotation is true THEN 4
-       end
-  FROM lsmb12.oe
-  LEFT JOIN lsmb12.customer c ON c.id = oe.customer_id
-  LEFT JOIN lsmb12.vendor v ON v.id = oe.vendor_id
-  LEFT JOIN lsmb12.employee e ON oe.employee_id = e.id
-  LEFT JOIN person p ON e.entity_id = p.id;
-
-INSERT INTO orderitems(id, trans_id, parts_id, description, qty, sellprice,
-            discount, unit, project_id, reqdate, ship, serialnumber, notes)
-     SELECT id, trans_id, parts_id, description, qty, sellprice,
-            discount, unit, project_id, reqdate, ship, serialnumber, notes
-       FROM lsmb12.orderitems;
-
-INSERT INTO exchangerate select * from lsmb12.exchangerate;
-
-INSERT INTO project (id, projectnumber, description, startdate, enddate,
-            parts_id, production, completed, credit_id)
-     SELECT p.id, projectnumber, description, p.startdate, p.enddate,
-            parts_id, production, completed, c.credit_id
-       FROM lsmb12.project p
-       JOIN lsmb12.customer c ON p.customer_id = c.id;
-
-INSERT INTO partsgroup SELECT * FROM lsmb12.partsgroup;
-
-INSERT INTO status SELECT * FROM lsmb12.status;
-
-INSERT INTO department SELECT * FROM lsmb12.department;
-
-INSERT INTO business SELECT * FROM lsmb12.business;
-
-INSERT INTO sic SELECT * FROM lsmb12.sic;
-
-INSERT INTO warehouse SELECT * FROM lsmb12.warehouse;
-
-INSERT INTO inventory(entity_id, warehouse_id, parts_id, trans_id,
-            orderitems_id, qty, shippingdate, entry_id)
-     SELECT e.entity_id, warehouse_id, parts_id, trans_id,
-            orderitems_id, qty, shippingdate, i.entry_id
-       FROM lsmb12.inventory i
-       JOIN lsmb12.employee e ON i.employee_id = e.id;
-
-INSERT INTO yearend (trans_id, transdate) SELECT * FROM lsmb12.yearend;
-
-INSERT INTO partsvendor(credit_id, parts_id, partnumber, leadtime, lastcost,
-            curr, entry_id)
-     SELECT v.credit_id, parts_id, partnumber, leadtime, lastcost,
-            pv.curr, entry_id
-       FROM lsmb12.partsvendor pv
-       JOIN lsmb12.vendor v ON v.id = pv.vendor_id;
-
-INSERT INTO partscustomer(parts_id, credit_id, pricegroup_id, pricebreak,
-            sellprice, validfrom, validto, curr, entry_id)
-     SELECT parts_id, credit_id, pv.pricegroup_id, pricebreak,
-            sellprice, validfrom, validto, pv.curr, entry_id
-       FROM lsmb12.partscustomer pv
-       JOIN lsmb12.customer v ON v.id = pv.customer_id;
-
-INSERT INTO language SELECT * FROM lsmb12.language;
-
-INSERT INTO audittrail(trans_id, tablename, reference, formname, action,
-            transdate, person_id, entry_id)
-     SELECT trans_id, tablename, reference, formname, action,
-            transdate, p.id, entry_id
-       FROM lsmb12.audittrail a
-       JOIN lsmb12.employee e ON a.employee_id = e.id
-       JOIN person p on e.entity_id = p.entity_id;
-
-INSERT INTO user_preference(id)
-     SELECT id from users;
-
-INSERT INTO recurring SELECT * FROM lsmb12.recurring;
-
-INSERT INTO recurringemail SELECT * FROM lsmb12.recurringemail;
-
-INSERT INTO recurringprint SELECT * FROM lsmb12.recurringprint;
-
-INSERT INTO jcitems(id, project_id, parts_id, description, qty, allocated,
-            sellprice, fxsellprice, serialnumber, checkedin, checkedout,
-            person_id, notes)
-     SELECT j.id,  project_id, parts_id, description, qty, allocated,
-            sellprice, fxsellprice, serialnumber, checkedin, checkedout,
-            p.id, j.notes
-       FROM lsmb12.jcitems j
-       JOIN lsmb12.employee e ON j.employee_id = e.id
-       JOIN person p ON e.entity_id = p.entity_id;
-
-INSERT INTO  custom_table_catalog  SELECT * FROM lsmb12. custom_table_catalog;
-
-INSERT INTO  custom_field_catalog  SELECT * FROM lsmb12. custom_field_catalog;
-
-SELECT setval('id', max(id)) FROM transactions;
-
- SELECT setval('acc_trans_entry_id_seq', max(entry_id)) FROM acc_trans;
- SELECT setval('partsvendor_entry_id_seq', max(entry_id)) FROM partsvendor;
- SELECT setval('inventory_entry_id_seq', max(entry_id)) FROM inventory;
- SELECT setval('partscustomer_entry_id_seq', max(entry_id)) FROM partscustomer;
- SELECT setval('audittrail_entry_id_seq', max(entry_id)) FROM audittrail;
- SELECT setval('account_id_seq', max(id)) FROM account;
- SELECT setval('account_heading_id_seq', max(id)) FROM account_heading;
- SELECT setval('account_checkpoint_id_seq', max(id)) FROM account_checkpoint;
- SELECT setval('pricegroup_id_seq', max(id)) FROM pricegroup;
- SELECT setval('country_id_seq', max(id)) FROM country;
- SELECT setval('country_tax_form_id_seq', max(id)) FROM country_tax_form;
- SELECT setval('asset_dep_method_id_seq', max(id)) FROM asset_dep_method;
- SELECT setval('asset_class_id_seq', max(id)) FROM asset_class;
- SELECT setval('entity_class_id_seq', max(id)) FROM entity_class;
- SELECT setval('asset_item_id_seq', max(id)) FROM asset_item;
- SELECT setval('asset_disposal_method_id_seq', max(id)) FROM asset_disposal_method;
- SELECT setval('users_id_seq', max(id)) FROM users;
- SELECT setval('entity_id_seq', max(id)) FROM entity;
- SELECT setval('company_id_seq', max(id)) FROM company;
- SELECT setval('location_id_seq', max(id)) FROM location;
- SELECT setval('open_forms_id_seq', max(id)) FROM open_forms;
- SELECT setval('location_class_id_seq', max(id)) FROM location_class;
- SELECT setval('asset_report_id_seq', max(id)) FROM asset_report;
- SELECT setval('salutation_id_seq', max(id)) FROM salutation;
- SELECT setval('person_id_seq', max(id)) FROM person;
- SELECT setval('contact_class_id_seq', max(id)) FROM contact_class;
- SELECT setval('entity_credit_account_id_seq', max(id)) FROM entity_credit_account;
- SELECT setval('entity_bank_account_id_seq', max(id)) FROM entity_bank_account;
- SELECT setval('note_class_id_seq', max(id)) FROM note_class;
- SELECT setval('note_id_seq', max(id)) FROM note;
- SELECT setval('batch_class_id_seq', max(id)) FROM batch_class;
- SELECT setval('batch_id_seq', max(id)) FROM batch;
- SELECT setval('invoice_id_seq', max(id)) FROM invoice;
- SELECT setval('voucher_id_seq', max(id)) FROM voucher;
- SELECT setval('parts_id_seq', max(id)) FROM parts;
- SELECT setval('taxmodule_taxmodule_id_seq', max(taxmodule_id)) FROM taxmodule;
- SELECT setval('taxcategory_taxcategory_id_seq', max(taxcategory_id)) FROM taxcategory;
- SELECT setval('oe_id_seq', max(id)) FROM oe;
- SELECT setval('orderitems_id_seq', max(id)) FROM orderitems;
- SELECT setval('business_id_seq', max(id)) FROM business;
- SELECT setval('warehouse_id_seq', max(id)) FROM warehouse;
- SELECT setval('partsgroup_id_seq', max(id)) FROM partsgroup;
- SELECT setval('project_id_seq', max(id)) FROM project;
- SELECT setval('department_id_seq', max(id)) FROM department;
- SELECT setval('jcitems_id_seq', max(id)) FROM jcitems;
- SELECT setval('payment_type_id_seq', max(id)) FROM payment_type;
- SELECT setval('custom_table_catalog_table_id_seq', max(table_id)) FROM custom_table_catalog;
- SELECT setval('custom_field_catalog_field_id_seq', max(field_id)) FROM custom_field_catalog;
- SELECT setval('menu_node_id_seq', max(id)) FROM menu_node;
- SELECT setval('menu_attribute_id_seq', max(id)) FROM menu_attribute;
- SELECT setval('menu_acl_id_seq', max(id)) FROM menu_acl;
- SELECT setval('pending_job_id_seq', max(id)) FROM pending_job;
- SELECT setval('new_shipto_id_seq', max(id)) FROM new_shipto;
- SELECT setval('payment_id_seq', max(id)) FROM payment;
- SELECT setval('cr_report_id_seq', max(id)) FROM cr_report;
- SELECT setval('cr_report_line_id_seq', max(id)) FROM cr_report_line;
-
-UPDATE defaults SET value = '1.2.99' WHERE setting_key = 'version';
-
-
-COMMIT;
---TODO:  Translation migratiion.  Partsgroups?
--- TODO:  User/password Migration

Modified: trunk/sql/upgrade/1.2-1.3.sql
===================================================================
--- trunk/sql/upgrade/1.2-1.3.sql	2011-09-02 17:56:14 UTC (rev 3693)
+++ trunk/sql/upgrade/1.2-1.3.sql	2011-09-09 20:41:41 UTC (rev 3694)
@@ -1,34 +1,677 @@
-/*
-ALTER SCHEMA PUBLIC RENAME TO lsmb12;
-CREATE SCHEMA PUBLIC;
+--Setup
 
-\i sql/Pg-database.sql
+-- When moved to an interface, these will all be specified and preprocessed.
+\set default_country '''<?lsmb default_country ?>'''
+\set ar '''<?lsmb default_ar ?>'''
+\set ap '''<?lsmb default_ap ?>'''
 
+-- This will be moved out of this part.
+ALTER SCHEMA public RENAME TO lsmb12;
+CREATE SCHEMA public;
 
+\cd :contribdir
+\i pg_trgm.sql
+\i tsearch2.sql
+\i tablefunc.sql
+
+\cd :lsmbdir
+
+-- Full module load should be part of upgrade wizard, at this stage.
+\i sql/Pg-database.sql
+\i sql/modules/Setting.sql
+\i sql/modules/Location.sql
+\i sql/modules/Account.sql
+\i sql/modules/Payment.sql
+\i sql/modules/Person.sql
+\i sql/modules/Reconciliation.sql
 BEGIN;
 
-ALTER TABLE employee RENAME TO employees;
+-- adding mapping info for import.
 
-CREATE TABLE locations ( 
-	id SERIAL PRIMARY KEY,
-	companyname text,
-	address1 text,
-	address2 text,
-	city text,
-	state text,
-	country text,
-	zipcode text
-);	
+ALTER TABLE lsmb12.vendor ADD COLUMN entity_id int;
+ALTER TABLE lsmb12.vendor ADD COLUMN company_id int;
+ALTER TABLE lsmb12.vendor ADD COLUMN credit_id int;
 
-CREATE SEQUENCE employees_id_seq;
-SELECT setval('employees_id_seq', (select max(id) + 1 FROM employees));
+ALTER TABLE lsmb12.customer ADD COLUMN entity_id int;
+ALTER TABLE lsmb12.customer ADD COLUMN company_id int;
+ALTER TABLE lsmb12.customer ADD COLUMN credit_id int;
 
-ALTER TABLE employees ADD COLUMN locations_id integer;
-ALTER TABLE employees ADD FOREIGN KEY (locations_id) REFERENCES locations(id);
-ALTER TABLE employees ALTER COLUMN id DROP DEFAULT;
-ALTER TABLE employees ALTER COLUMN id SET DEFAULT  nextval('employees_id_seq');
 
-DROP RULE employee_id_track_i ON employees; -- no longer needed
+--Accounts
+INSERT INTO account_heading(id, accno, description)
+SELECT id, accno, description
+  FROM lsmb12.chart WHERE charttype = 'H';
 
+SELECT account_save(id, accno, description, category, gifi_accno, NULL, contra, 
+                    CASE WHEN link like '%tax%' THEN true ELSE false END, 
+                    string_to_array(link,':'))
+  FROM lsmb12.chart 
+ WHERE charttype = 'A';
+--Entity
+
+INSERT INTO entity (name, control_code, entity_class, country_id)
+SELECT name, 'V-' || vendornumber, 1, 
+       (select id from country 
+         where lower(short_name)  = :default_country)
+FROM lsmb12.vendor
+GROUP BY name, vendornumber;
+
+INSERT INTO entity (name, control_code, entity_class, country_id)
+SELECT name, 'C-' || customernumber, 2, 
+       (select id from country 
+         where lower(short_name)  =  :default_country)
+FROM lsmb12.customer
+GROUP BY name, customernumber;
+
+UPDATE lsmb12.vendor SET entity_id = (SELECT id FROM entity WHERE 'V-' || vendornumber = control_code);
+
+UPDATE lsmb12.customer SET entity_id = coalesce((SELECT min(id) FROM entity WHERE 'C-' || customernumber = control_code), entity_id);
+
+--Entity Credit Account
+
+INSERT INTO entity_credit_account
+(entity_id, meta_number, business_id, creditlimit, ar_ap_account_id, 
+	cash_account_id, startdate, enddate, threshold, entity_class)
+SELECT entity_id, vendornumber, business_id, creditlimit, 
+       (select id from account where accno = :ap), 
+	NULL, startdate, enddate, 0, 1
+FROM lsmb12.vendor WHERE entity_id IS NOT NULL;
+
+UPDATE lsmb12.vendor SET credit_id = 
+	(SELECT id FROM entity_credit_account e 
+	WHERE e.meta_number = vendornumber);
+
+
+INSERT INTO entity_credit_account
+(entity_id, meta_number, business_id, creditlimit, ar_ap_account_id, 
+	cash_account_id, startdate, enddate, threshold, entity_class)
+SELECT entity_id, customernumber, business_id, creditlimit,
+       (select id from account where accno = :ar),
+	NULL, startdate, enddate, 0, 2
+FROM lsmb12.customer WHERE entity_id IS NOT NULL;
+
+UPDATE lsmb12.customer SET credit_id = 
+	(SELECT id FROM entity_credit_account e 
+	WHERE e.meta_number = customernumber AND customer.entity_id = e.entity_id);
+
+--Company
+
+INSERT INTO company (entity_id, legal_name, tax_id)
+SELECT entity_id, name, max(taxnumber) FROM lsmb12.vendor 
+WHERE entity_id IS NOT NULL AND entity_id IN (select id from entity) GROUP BY entity_id, name;
+
+UPDATE lsmb12.vendor SET company_id = (select id from company c where entity_id = vendor.entity_id);
+
+INSERT INTO company (entity_id, legal_name, tax_id)
+SELECT entity_id, name, max(taxnumber) FROM lsmb12.customer
+WHERE entity_id IS NOT NULL AND entity_id IN (select id from entity) GROUP BY entity_id, name;
+
+UPDATE lsmb12.customer SET company_id = (select id from company c where entity_id = customer.entity_id);
+
+-- Contact
+
+insert into eca_to_contact (credit_id, contact_class_id, contact,description) 
+select v.credit_id, 1, v.phone, 'Primary phone: '||max(v.contact) as description
+from lsmb12.vendor v 
+where v.company_id is not null and v.phone is not null 
+       and v.phone ~ '[[:alnum:]_]'::text 
+group by v.credit_id, v.phone
+UNION
+select v.credit_id, 12, v.email, 
+       'email address: '||max(v.contact) as description 
+from lsmb12.vendor v 
+where v.company_id is not null and v.email is not null 
+       and v.email ~ '[[:alnum:]_]'::text 
+group by v.credit_id, v.email
+UNION
+select v.credit_id, 12, v.cc, 'Carbon Copy email address' as description 
+from lsmb12.vendor v 
+where v.company_id is not null and v.cc is not null 
+      and v.cc ~ '[[:alnum:]_]'::text 
+group by v.credit_id, v.cc
+UNION 
+select v.credit_id, 12, v.bcc, 'Blind Carbon Copy email address' as description 
+from lsmb12.vendor v 
+where v.company_id is not null and v.bcc is not null 
+       and v.bcc ~ '[[:alnum:]_]'::text 
+group by v.credit_id, v.bcc
+UNION
+    select v.credit_id, 9, v.fax, 'Fax number' as description 
+from lsmb12.vendor v 
+where v.company_id is not null and v.fax is not null 
+      and v.fax ~ '[[:alnum:]_]'::text 
+group by v.credit_id, v.fax;
+
+insert into eca_to_contact (credit_id, contact_class_id, contact,description) 
+select v.credit_id, 1, v.phone, 'Primary phone: '||max(v.contact) as description
+from lsmb12.customer v 
+where v.company_id is not null and v.phone is not null 
+       and v.phone ~ '[[:alnum:]_]'::text 
+group by v.credit_id, v.phone
+UNION
+select v.credit_id, 12, v.email, 
+       'email address: '||max(v.contact) as description 
+from lsmb12.customer v 
+where v.company_id is not null and v.email is not null 
+       and v.email ~ '[[:alnum:]_]'::text 
+group by v.credit_id, v.email
+UNION
+select v.credit_id, 12, v.cc, 'Carbon Copy email address' as description 
+from lsmb12.customer v 
+where v.company_id is not null and v.cc is not null 
+      and v.cc ~ '[[:alnum:]_]'::text 
+group by v.credit_id, v.cc
+UNION 
+select v.credit_id, 12, v.bcc, 'Blind Carbon Copy email address' as description 
+from lsmb12.customer v 
+where v.company_id is not null and v.bcc is not null 
+       and v.bcc ~ '[[:alnum:]_]'::text 
+group by v.credit_id, v.bcc
+UNION
+    select v.credit_id, 9, v.fax, 'Fax number' as description 
+from lsmb12.customer v 
+where v.company_id is not null and v.fax is not null 
+      and v.fax ~ '[[:alnum:]_]'::text 
+group by v.credit_id, v.fax;
+
+
+-- addresses
+
+INSERT INTO public.country (id, name, short_name) VALUES (-1, 'Invalid Country', 'XX');
+
+INSERT INTO eca_to_location(credit_id, location_class, location_id)
+SELECT eca.id, 1,
+    min(location_save(NULL,
+
+    case 
+        when oa.address1 = '' then 'Null' 
+        when oa.address1 is null then 'Null'
+        else oa.address1 
+    end,
+    oa.address2, 
+    NULL,
+    case 
+        when oa.city !~ '[[:alnum:]_]' then 'Invalid' 
+        when oa.city is null then 'Null' 
+        else oa.city 
+    end,
+    case 
+        when oa.state !~ '[[:alnum:]_]' then 'Invalid' 
+        when oa.state is null then 'Null' 
+        else oa.state 
+    end,
+    case 
+        when oa.zipcode !~ '[[:alnum:]_]' then 'Invalid' 
+        when oa.zipcode is null then 'Null' 
+        else oa.zipcode 
+    end,
+    coalesce(c.id, -1)
+    ))
+FROM country c
+RIGHT OUTER JOIN
+     lsmb12.vendor oa
+ON
+    lower(trim(both ' ' from c.name)) = lower( trim(both ' ' from oa.country))
+OR
+
+    lower(trim(both ' ' from c.short_name)) = lower( trim(both ' ' from oa.country))
+JOIN entity_credit_account eca ON (oa.credit_id = eca.id)
+GROUP BY eca.id;
+
+INSERT INTO eca_to_location(credit_id, location_class, location_id)
+SELECT eca.id, 1,
+    min(location_save(NULL,
+
+    case 
+        when oa.address1 = '' then 'Null' 
+        when oa.address1 is null then 'Null'
+        else oa.address1 
+    end,
+    oa.address2, 
+    NULL,
+    case 
+        when oa.city !~ '[[:alnum:]_]' then 'Invalid' 
+        when oa.city is null then 'Null' 
+        else oa.city 
+    end,
+    case 
+        when oa.state !~ '[[:alnum:]_]' then 'Invalid' 
+        when oa.state is null then 'Null' 
+        else oa.state 
+    end,
+    case 
+        when oa.zipcode !~ '[[:alnum:]_]' then 'Invalid' 
+        when oa.zipcode is null then 'Null' 
+        else oa.zipcode 
+    end,
+    coalesce(c.id, -1)
+    ))
+FROM country c
+RIGHT OUTER JOIN
+     lsmb12.customer oa
+ON
+    lower(trim(both ' ' from c.name)) = lower( trim(both ' ' from oa.country))
+OR
+
+    lower(trim(both ' ' from c.short_name)) = lower( trim(both ' ' from oa.country))
+JOIN entity_credit_account eca ON (oa.credit_id = eca.id)
+GROUP BY eca.id;
+
+-- Shipto
+
+INSERT INTO eca_to_location(credit_id, location_class, location_id)
+SELECT eca.id, 2,
+    min(location_save(NULL,
+
+    case 
+        when oa.shiptoaddress1 = '' then 'Null' 
+        when oa.shiptoaddress1 is null then 'Null'
+        else oa.shiptoaddress1 
+    end,
+    oa.shiptoaddress2, 
+    NULL,
+    case 
+        when oa.shiptocity !~ '[[:alnum:]_]' then 'Invalid' 
+        when oa.shiptocity is null then 'Null' 
+        else oa.shiptocity 
+    end,
+    case 
+        when oa.shiptostate !~ '[[:alnum:]_]' then 'Invalid' 
+        when oa.shiptostate is null then 'Null' 
+        else oa.shiptostate 
+    end,
+    case 
+        when oa.shiptozipcode !~ '[[:alnum:]_]' then 'Invalid' 
+        when oa.shiptozipcode is null then 'Null' 
+        else oa.shiptozipcode 
+    end,
+    coalesce(c.id, -1)
+    ))
+FROM country c
+RIGHT OUTER JOIN
+     lsmb12.shipto oa
+ON
+    lower(trim(both ' ' from c.name)) = lower( trim(both ' ' from oa.shiptocountry))
+OR
+
+    lower(trim(both ' ' from c.short_name)) = lower( trim(both ' ' from oa.shiptocountry))
+JOIN lsmb12.vendor ov ON (oa.trans_id = ov.id)
+JOIN entity_credit_account eca ON (ov.credit_id = eca.id)
+GROUP BY eca.id;
+
+INSERT INTO eca_to_location(credit_id, location_class, location_id)
+SELECT eca.id, 2,
+    min(location_save(NULL,
+
+    case 
+        when oa.shiptoaddress1 = '' then 'Null' 
+        when oa.shiptoaddress1 is null then 'Null'
+        else oa.shiptoaddress1 
+    end,
+    oa.shiptoaddress2, 
+    NULL,
+    case 
+        when oa.shiptocity !~ '[[:alnum:]_]' then 'Invalid' 
+        when oa.shiptocity is null then 'Null' 
+        else oa.shiptocity 
+    end,
+    case 
+        when oa.shiptostate !~ '[[:alnum:]_]' then 'Invalid' 
+        when oa.shiptostate is null then 'Null' 
+        else oa.shiptostate 
+    end,
+    case 
+        when oa.shiptozipcode !~ '[[:alnum:]_]' then 'Invalid' 
+        when oa.shiptozipcode is null then 'Null' 
+        else oa.shiptozipcode 
+    end,
+    coalesce(c.id, -1)
+    ))
+FROM country c
+RIGHT OUTER JOIN
+     lsmb12.shipto oa
+ON
+    lower(trim(both ' ' from c.name)) = lower( trim(both ' ' from oa.shiptocountry))
+OR
+
+    lower(trim(both ' ' from c.short_name)) = lower( trim(both ' ' from oa.shiptocountry))
+JOIN lsmb12.customer ov ON (oa.trans_id = ov.id)
+JOIN entity_credit_account eca ON (ov.credit_id = eca.id)
+GROUP BY eca.id;
+ 
+INSERT INTO eca_note(note_class, ref_key, note, vector)
+SELECT 3, credit_id, notes, '' FROM lsmb12.vendor 
+WHERE notes IS NOT NULL AND credit_id IS NOT NULL;
+
+INSERT INTO eca_note(note_class, ref_key, note, vector)
+SELECT 3, credit_id, notes, '' FROM lsmb12.customer
+WHERE notes IS NOT NULL AND credit_id IS NOT NULL;
+
+UPDATE entity SET country_id = 
+(select country_id FROM location l 
+   JOIN eca_to_location e2l ON l.id = e2l.location_id
+        AND e2l.location_class = 1
+   JOIN entity_credit_account eca ON e2l.credit_id = eca.id
+  WHERE eca.entity_id = entity_id
+        AND l.country_id > -1
+  LIMIT 1)
+WHERE id IN
+(select eca.entity_id FROM location l 
+   JOIN eca_to_location e2l ON l.id = e2l.location_id
+        AND e2l.location_class = 1
+   JOIN entity_credit_account eca ON e2l.credit_id = eca.id
+  WHERE eca.entity_id = entity_id
+       aND l.country_id > -1);
+
+INSERT INTO pricegroup
+SELECT * FROM lsmb12.pricegroup;
+
+ALTER TABLE lsmb12.employee ADD entity_id int;
+
+INSERT INTO entity(control_code, entity_class, country_id)
+select 'E-' || employeenumber, 3,
+        (select id from country where lower(short_name) = :default_country)
+FROM lsmb12.employee;
+
+UPDATE lsmb12.employee set entity_id = 
+       (select id from entity where 'E-'||employeenumber = control_code);
+
+INSERT INTO person (first_name, last_name, entity_id) 
+select name, name, entity_id FROM lsmb12.employee;
+
+INSERT INTO users (entity_id, username)
+     SELECT entity_id, login FROM lsmb12.employee em;
+
+INSERT 
+  INTO entity_employee(entity_id, startdate, enddate, role, ssn, sales,
+       employeenumber, dob, manager_id)
+SELECT entity_id, startdate, enddate, role, ssn, sales, employeenumber, dob,
+       (select entity_id from lsmb12.employee where id = em.managerid)
+  FROM lsmb12.employee em;
+
+
+
+-- must rebuild this table due to changes since 1.2
+
+INSERT INTO makemodel
+SELECT * FROM lsmb12.makemodel;
+
+INSERT INTO gifi
+SELECT * FROM lsmb12.gifi;
+
+UPDATE defaults 
+   SET value = (select value from lsmb12.defaults src 
+                 WHERE src.setting_key = defaults.setting_key)
+ WHERE setting_key IN (select setting_key FROM lsmb12.defaults);
+
+
+INSERT INTO parts SELECT * FROM lsmb12.parts;
+
+INSERT INTO assembly SELECT * FROM lsmb12.assembly;
+
+ALTER TABLE gl DISABLE TRIGGER gl_audit_trail;
+
+INSERT INTO gl(id, reference, description, transdate, person_id, notes, 
+               department_id)
+    SELECT gl.id, reference, description, transdate, p.id, gl.notes, 
+           department_id
+      FROM lsmb12.gl 
+ LEFT JOIN lsmb12.employee em ON gl.employee_id = em.id
+ LEFT JOIN person p ON em.entity_id = p.id;
+
+ALTER TABLE gl ENABLE TRIGGER gl_audit_trail;
+
+ALTER TABLE ar DISABLE TRIGGER ar_audit_trail;
+
+INSERT INTO ar(id, invnumber, transdate, taxincluded, amount, 
+            netamount, paid, datepaid, duedate, invoice, shippingpoint, terms,
+            notes, curr, ordnumber, person_id, till, quonumber, intnotes, 
+            department_id, shipvia, language_code, ponumber, 
+            entity_credit_account)
+     SELECT ar.id, invnumber, transdate, ar.taxincluded, amount, netamount, 
+            paid, datepaid, duedate, invoice, shippingpoint, ar.terms, ar.notes,
+            ar.curr, ordnumber, em.entity_id, till, quonumber, intnotes, 
+            department_id, shipvia, ar.language_code, ponumber, credit_id
+       FROM lsmb12.ar
+       JOIN lsmb12.customer c ON c.id = ar.customer_id
+  LEFT JOIN lsmb12.employee em ON em.id = ar.employee_id;
+
+ALTER TABLE ar ENABLE TRIGGER ar_audit_trail;
+
+ALTER TABLE ap DISABLE TRIGGER ap_audit_trail;
+
+INSERT INTO ap(id, invnumber, transdate, taxincluded, amount, 
+            netamount, paid, datepaid, duedate, invoice, shippingpoint, terms,
+            notes, curr, ordnumber, person_id, till, quonumber, intnotes, 
+            department_id, shipvia, language_code, ponumber, 
+            entity_credit_account)
+     SELECT ap.id, invnumber, transdate, ap.taxincluded, amount, netamount, 
+            paid, datepaid, duedate, invoice, shippingpoint, ap.terms, ap.notes,
+            ap.curr, ordnumber, em.entity_id, till, quonumber, intnotes, 
+            department_id, shipvia, ap.language_code, ponumber, credit_id
+       FROM lsmb12.ap
+       JOIN lsmb12.vendor c ON c.id = ap.vendor_id
+  LEFT JOIN lsmb12.employee em ON em.id = ap.employee_id;
+
+ALTER TABLE ap ENABLE TRIGGER ap_audit_trail;
+
+INSERT INTO acc_trans(trans_id, chart_id, amount, transdate, source, cleared,
+            fx_transaction, project_id, memo, invoice_id, entry_id)
+     SELECT trans_id, a.id, amount, transdate, source, cleared,
+            fx_transaction, project_id, memo, invoice_id, entry_id
+       FROM lsmb12.acc_trans
+       JOIN lsmb12.chart ON acc_trans.chart_id = chart.id
+       JOIN account a ON chart.accno = a.accno; 
+
+INSERT INTO invoice (id, trans_id, parts_id, description, qty, allocated,
+            sellprice, fxsellprice, discount, assemblyitem, unit, project_id,
+            deliverydate, serialnumber, notes)
+    SELECT  id, trans_id, parts_id, description, qty, allocated,
+            sellprice, fxsellprice, discount, assemblyitem, unit, project_id,
+            deliverydate, serialnumber, notes
+       FROM lsmb12.invoice;
+
+INSERT INTO partstax (parts_id, chart_id)
+     SELECT parts_id, a.id
+       FROM lsmb12.partstax pt
+       JOIN lsmb12.chart ON chart.id = pt.chart_id
+       JOIN account a ON chart.accno = a.accno;
+
+INSERT INTO tax(chart_id, rate, taxnumber, validto, pass, taxmodule_id)
+     SELECT a.id, t.rate, t.taxnumber, 
+            coalesce(t.validto::timestamp, 'infinity'), pass, taxmodule_id
+       FROM lsmb12.tax t
+       JOIN lsmb12.chart c ON (t.chart_id = c.id)
+       JOIN account a ON (a.accno = c.accno);
+
+INSERT INTO customertax (customer_id, chart_id)
+     SELECT c.credit_id,  a.id
+       FROM lsmb12.customertax pt
+       JOIN lsmb12.customer c ON (pt.customer_id = c.id)
+       JOIN lsmb12.chart ON chart.id = pt.chart_id
+       JOIN account a ON chart.accno = a.accno; 
+
+INSERT INTO vendortax (vendor_id, chart_id)
+     SELECT c.credit_id,  a.id
+       FROM lsmb12.vendortax pt       
+       JOIN lsmb12.vendor c ON (pt.vendor_id = c.id)
+       JOIN lsmb12.chart ON chart.id = pt.chart_id
+       JOIN account a ON chart.accno = a.accno;
+
+INSERT 
+  INTO oe(id, ordnumber, transdate, amount, netamount, reqdate, taxincluded,
+       shippingpoint, notes, curr, person_id, closed, quotation, quonumber,
+       intnotes, department_id, shipvia, language_code, ponumber, terms,
+       entity_credit_account, oe_class_id)
+SELECT oe.id,  ordnumber, transdate, amount, netamount, reqdate, oe.taxincluded,
+       shippingpoint, oe.notes, oe.curr, p.id, closed, quotation, quonumber,
+       intnotes, department_id, shipvia, oe.language_code, ponumber, oe.terms,
+       coalesce(c.credit_id, v.credit_id),
+       case 
+           when c.id is not null and quotation is not true THEN 1
+           WHEN v.id is not null and quotation is not true THEN 2
+           when c.id is not null and quotation is true THEN 3
+           WHEN v.id is not null and quotation is true THEN 4
+       end
+  FROM lsmb12.oe
+  LEFT JOIN lsmb12.customer c ON c.id = oe.customer_id
+  LEFT JOIN lsmb12.vendor v ON v.id = oe.vendor_id
+  LEFT JOIN lsmb12.employee e ON oe.employee_id = e.id
+  LEFT JOIN person p ON e.entity_id = p.id;
+
+INSERT INTO orderitems(id, trans_id, parts_id, description, qty, sellprice,
+            discount, unit, project_id, reqdate, ship, serialnumber, notes)
+     SELECT id, trans_id, parts_id, description, qty, sellprice,
+            discount, unit, project_id, reqdate, ship, serialnumber, notes
+       FROM lsmb12.orderitems;
+
+INSERT INTO exchangerate select * from lsmb12.exchangerate;
+
+INSERT INTO project (id, projectnumber, description, startdate, enddate,
+            parts_id, production, completed, credit_id)
+     SELECT p.id, projectnumber, description, p.startdate, p.enddate,
+            parts_id, production, completed, c.credit_id
+       FROM lsmb12.project p
+       JOIN lsmb12.customer c ON p.customer_id = c.id;
+
+INSERT INTO partsgroup SELECT * FROM lsmb12.partsgroup;
+
+INSERT INTO status SELECT * FROM lsmb12.status;
+
+INSERT INTO department SELECT * FROM lsmb12.department;
+
+INSERT INTO business SELECT * FROM lsmb12.business;
+
+INSERT INTO sic SELECT * FROM lsmb12.sic;
+
+INSERT INTO warehouse SELECT * FROM lsmb12.warehouse;
+
+INSERT INTO inventory(entity_id, warehouse_id, parts_id, trans_id,
+            orderitems_id, qty, shippingdate, entry_id)
+     SELECT e.entity_id, warehouse_id, parts_id, trans_id,
+            orderitems_id, qty, shippingdate, i.entry_id
+       FROM lsmb12.inventory i
+       JOIN lsmb12.employee e ON i.employee_id = e.id;
+
+INSERT INTO yearend (trans_id, transdate) SELECT * FROM lsmb12.yearend;
+
+INSERT INTO partsvendor(credit_id, parts_id, partnumber, leadtime, lastcost,
+            curr, entry_id)
+     SELECT v.credit_id, parts_id, partnumber, leadtime, lastcost,
+            pv.curr, entry_id
+       FROM lsmb12.partsvendor pv
+       JOIN lsmb12.vendor v ON v.id = pv.vendor_id;
+
+INSERT INTO partscustomer(parts_id, credit_id, pricegroup_id, pricebreak,
+            sellprice, validfrom, validto, curr, entry_id)
+     SELECT parts_id, credit_id, pv.pricegroup_id, pricebreak,
+            sellprice, validfrom, validto, pv.curr, entry_id
+       FROM lsmb12.partscustomer pv
+       JOIN lsmb12.customer v ON v.id = pv.customer_id;
+
+INSERT INTO language SELECT * FROM lsmb12.language;
+
+INSERT INTO audittrail(trans_id, tablename, reference, formname, action,
+            transdate, person_id, entry_id)
+     SELECT trans_id, tablename, reference, formname, action,
+            transdate, p.id, entry_id
+       FROM lsmb12.audittrail a
+       JOIN lsmb12.employee e ON a.employee_id = e.id
+       JOIN person p on e.entity_id = p.entity_id;
+
+INSERT INTO user_preference(id)
+     SELECT id from users;
+
+INSERT INTO recurring SELECT * FROM lsmb12.recurring;
+
+INSERT INTO recurringemail SELECT * FROM lsmb12.recurringemail;
+
+INSERT INTO recurringprint SELECT * FROM lsmb12.recurringprint;
+
+INSERT INTO jcitems(id, project_id, parts_id, description, qty, allocated,
+            sellprice, fxsellprice, serialnumber, checkedin, checkedout,
+            person_id, notes)
+     SELECT j.id,  project_id, parts_id, description, qty, allocated,
+            sellprice, fxsellprice, serialnumber, checkedin, checkedout,
+            p.id, j.notes
+       FROM lsmb12.jcitems j
+       JOIN lsmb12.employee e ON j.employee_id = e.id
+       JOIN person p ON e.entity_id = p.entity_id;
+
+INSERT INTO  custom_table_catalog  SELECT * FROM lsmb12. custom_table_catalog;
+
+INSERT INTO  custom_field_catalog  SELECT * FROM lsmb12. custom_field_catalog;
+
+INSERT INTO parts_translation SELECT * FROM lsmb12.translation where trans_id in (select id from parts);
+
+INSERT INTO partsgroup_translation SELECT * FROM lsmb12.translation where trans_id in
+ (select id from partsgroup);
+
+INSERT INTO project_translation SELECT * FROM lsmb12.translation where trans_id in
+ (select id from project);
+
+SELECT setval('id', max(id)) FROM transactions;
+
+ SELECT setval('acc_trans_entry_id_seq', max(entry_id)) FROM acc_trans;
+ SELECT setval('partsvendor_entry_id_seq', max(entry_id)) FROM partsvendor;
+ SELECT setval('inventory_entry_id_seq', max(entry_id)) FROM inventory;
+ SELECT setval('partscustomer_entry_id_seq', max(entry_id)) FROM partscustomer;
+ SELECT setval('audittrail_entry_id_seq', max(entry_id)) FROM audittrail;
+ SELECT setval('account_id_seq', max(id)) FROM account;
+ SELECT setval('account_heading_id_seq', max(id)) FROM account_heading;
+ SELECT setval('account_checkpoint_id_seq', max(id)) FROM account_checkpoint;
+ SELECT setval('pricegroup_id_seq', max(id)) FROM pricegroup;
+ SELECT setval('country_id_seq', max(id)) FROM country;
+ SELECT setval('country_tax_form_id_seq', max(id)) FROM country_tax_form;
+ SELECT setval('asset_dep_method_id_seq', max(id)) FROM asset_dep_method;
+ SELECT setval('asset_class_id_seq', max(id)) FROM asset_class;
+ SELECT setval('entity_class_id_seq', max(id)) FROM entity_class;
+ SELECT setval('asset_item_id_seq', max(id)) FROM asset_item;
+ SELECT setval('asset_disposal_method_id_seq', max(id)) FROM asset_disposal_method;
+ SELECT setval('users_id_seq', max(id)) FROM users;
+ SELECT setval('entity_id_seq', max(id)) FROM entity;
+ SELECT setval('company_id_seq', max(id)) FROM company;
+ SELECT setval('location_id_seq', max(id)) FROM location;
+ SELECT setval('open_forms_id_seq', max(id)) FROM open_forms;
+ SELECT setval('location_class_id_seq', max(id)) FROM location_class;
+ SELECT setval('asset_report_id_seq', max(id)) FROM asset_report;
+ SELECT setval('salutation_id_seq', max(id)) FROM salutation;
+ SELECT setval('person_id_seq', max(id)) FROM person;
+ SELECT setval('contact_class_id_seq', max(id)) FROM contact_class;
+ SELECT setval('entity_credit_account_id_seq', max(id)) FROM entity_credit_account;
+ SELECT setval('entity_bank_account_id_seq', max(id)) FROM entity_bank_account;
+ SELECT setval('note_class_id_seq', max(id)) FROM note_class;
+ SELECT setval('note_id_seq', max(id)) FROM note;
+ SELECT setval('batch_class_id_seq', max(id)) FROM batch_class;
+ SELECT setval('batch_id_seq', max(id)) FROM batch;
+ SELECT setval('invoice_id_seq', max(id)) FROM invoice;
+ SELECT setval('voucher_id_seq', max(id)) FROM voucher;
+ SELECT setval('parts_id_seq', max(id)) FROM parts;
+ SELECT setval('taxmodule_taxmodule_id_seq', max(taxmodule_id)) FROM taxmodule;
+ SELECT setval('taxcategory_taxcategory_id_seq', max(taxcategory_id)) FROM taxcategory;
+ SELECT setval('oe_id_seq', max(id)) FROM oe;
+ SELECT setval('orderitems_id_seq', max(id)) FROM orderitems;
+ SELECT setval('business_id_seq', max(id)) FROM business;
+ SELECT setval('warehouse_id_seq', max(id)) FROM warehouse;
+ SELECT setval('partsgroup_id_seq', max(id)) FROM partsgroup;
+ SELECT setval('project_id_seq', max(id)) FROM project;
+ SELECT setval('department_id_seq', max(id)) FROM department;
+ SELECT setval('jcitems_id_seq', max(id)) FROM jcitems;
+ SELECT setval('payment_type_id_seq', max(id)) FROM payment_type;
+ SELECT setval('custom_table_catalog_table_id_seq', max(table_id)) FROM custom_table_catalog;
+ SELECT setval('custom_field_catalog_field_id_seq', max(field_id)) FROM custom_field_catalog;
+ SELECT setval('menu_node_id_seq', max(id)) FROM menu_node;
+ SELECT setval('menu_attribute_id_seq', max(id)) FROM menu_attribute;
+ SELECT setval('menu_acl_id_seq', max(id)) FROM menu_acl;
+ SELECT setval('pending_job_id_seq', max(id)) FROM pending_job;
+ SELECT setval('new_shipto_id_seq', max(id)) FROM new_shipto;
+ SELECT setval('payment_id_seq', max(id)) FROM payment;
+ SELECT setval('cr_report_id_seq', max(id)) FROM cr_report;
+ SELECT setval('cr_report_line_id_seq', max(id)) FROM cr_report_line;
+
+UPDATE defaults SET value = '1.2.99' WHERE setting_key = 'version';
+
+
 COMMIT;
-*/
+--TODO:  Translation migratiion.  Partsgroups?
+-- TODO:  User/password Migration

Added: trunk/sql/upgrade/3694-update_version.sql
===================================================================
--- trunk/sql/upgrade/3694-update_version.sql	                        (rev 0)
+++ trunk/sql/upgrade/3694-update_version.sql	2011-09-09 20:41:41 UTC (rev 3694)
@@ -0,0 +1,9 @@
+UPDATE defaults SET value = '1.3.0' where setting_key = 'version';
+
+CREATE TABLE partsgroup_translation
+(PRIMARY KEY (trans_id, language_code)) INHERITS (translation);
+ALTER TABLE partsgroup_translation 
+ADD foreign key (trans_id) REFERENCES partsgroup(id);
+
+COMMENT ON TABLE partsgroup_translation IS
+$$ Translation information for partsgroups.$$;

Modified: trunk/t/99-versioning.t
===================================================================
--- trunk/t/99-versioning.t	2011-09-02 17:56:14 UTC (rev 3693)
+++ trunk/t/99-versioning.t	2011-09-09 20:41:41 UTC (rev 3694)
@@ -38,6 +38,8 @@
 my $age;
 SKIP: {
 	skip 'LedgerSMB is trunk', 1 if $lsmb->{version} =~ /trunk$/i;
+        $lsmb->{version} =~ s/(\d+\.\d+\.\d+)\D.*/$1/;
+        $lsmb->{dbversion} =~ s/(\d+\.\d+\.\d+)\D.*/$1/;
 	@dparts = split /\./, $lsmb->{dbversion};
 	@lparts = split /\./, $lsmb->{version};
 	$age = 0;
@@ -57,6 +59,8 @@
 }
 SKIP: {
 	skip 'Form is trunk', 1 if $form->{version} =~ /trunk$/i;
+        $form->{version} =~ s/(\d+\.\d+\.\d+)\D.*/$1/;
+        $form->{dbversion} =~ s/(\d+\.\d+\.\d+)\D.*/$1/;
 	@dparts = split /\./, $form->{dbversion};
 	@lparts = split /\./, $form->{version};
 	$age = 0;

This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
Prev by Date: SF.net SVN: ledger-smb:[3693] trunk/LedgerSMB/AA.pm
Next by Date: SF.net SVN: ledger-smb:[3695] trunk/LedgerSMB/IS.pm
Previous by thread: SF.net SVN: ledger-smb:[3693] trunk/LedgerSMB/AA.pm
Next by thread: SF.net SVN: ledger-smb:[3695] trunk/LedgerSMB/IS.pm
Index(es):
- Date
- Thread