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

SF.net SVN: ledger-smb:[4037] trunk



Revision: 4037
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=4037&view=rev
Author:   einhverfr
Date:     2011-11-23 08:37:35 +0000 (Wed, 23 Nov 2011)
Log Message:
-----------
Merging from 1.3

Added Paths:
-----------
    trunk/sql/upgrade/sql-ledger2ledgersmb-wip.sql

Property Changed:
----------------
    trunk/
    trunk/LedgerSMB/Scripts/admin.pm
    trunk/LedgerSMB/Scripts/employee.pm
    trunk/LedgerSMB/Scripts/payment.pm
    trunk/LedgerSMB/Scripts/setup.pm
    trunk/sql/upgrade/1.2-1.3-manual.sql


Property changes on: trunk
___________________________________________________________________
Modified: svn:mergeinfo
   - /branches/1.3:3711-4034
   + /branches/1.3:3711-4036


Property changes on: trunk/LedgerSMB/Scripts/admin.pm
___________________________________________________________________
Modified: svn:mergeinfo
   - /branches/1.3/LedgerSMB/Scripts/admin.pm:3901-4034
/branches/1.3/scripts/admin.pl:3711-3903
   + /branches/1.3/LedgerSMB/Scripts/admin.pm:3901-4036
/branches/1.3/scripts/admin.pl:3711-3903


Property changes on: trunk/LedgerSMB/Scripts/employee.pm
___________________________________________________________________
Modified: svn:mergeinfo
   - /branches/1.3/LedgerSMB/Scripts/employee.pm:3712-4034
/branches/1.3/scripts/employee.pl:3842-3843
   + /branches/1.3/LedgerSMB/Scripts/employee.pm:3712-4036
/branches/1.3/scripts/employee.pl:3842-3843


Property changes on: trunk/LedgerSMB/Scripts/payment.pm
___________________________________________________________________
Modified: svn:mergeinfo
   - /branches/1.3/LedgerSMB/Scripts/payment.pm:4010-4034
/branches/1.3/scripts/payment.pl:3711-4011
   + /branches/1.3/LedgerSMB/Scripts/payment.pm:4010-4036
/branches/1.3/scripts/payment.pl:3711-4011


Property changes on: trunk/LedgerSMB/Scripts/setup.pm
___________________________________________________________________
Modified: svn:mergeinfo
   - /branches/1.3/LedgerSMB/Scripts/setup.pm:3937-4034
/branches/1.3/scripts/setup.pl:3711-3967
   + /branches/1.3/LedgerSMB/Scripts/setup.pm:3937-4036
/branches/1.3/scripts/setup.pl:3711-3967


Property changes on: trunk/sql/upgrade/1.2-1.3-manual.sql
___________________________________________________________________
Modified: svn:mergeinfo
   - /branches/1.3/sql/upgrade/1.2-1.3-manual.sql:3712-4034
/branches/1.3/sql/upgrade/1.2-1.3.sql:3711-3851
/trunk/sql/upgrade/1.2-1.3.sql:858-3710
   + /branches/1.3/sql/upgrade/1.2-1.3-manual.sql:3712-4036
/branches/1.3/sql/upgrade/1.2-1.3.sql:3711-3851
/trunk/sql/upgrade/1.2-1.3.sql:858-3710

Copied: trunk/sql/upgrade/sql-ledger2ledgersmb-wip.sql (from rev 4036, branches/1.3/sql/upgrade/sql-ledger2ledgersmb-wip.sql)
===================================================================
--- trunk/sql/upgrade/sql-ledger2ledgersmb-wip.sql	                        (rev 0)
+++ trunk/sql/upgrade/sql-ledger2ledgersmb-wip.sql	2011-11-23 08:37:35 UTC (rev 4037)
@@ -0,0 +1,242 @@
+ALTER SCHEMA public RENAME TO orig;
+CREATE SCHEMA public;
+
+\i /usr/local/src/postgresql-8.2.6/contrib/tsearch2/tsearch2.sql
+\i /usr/local/src/postgresql-8.2.6/contrib/pg_trgm/pg_trgm.sql
+\i /usr/local/src/postgresql-8.2.6/contrib/tablefunc/tablefunc.sql
+\i /home/ledgersmb/ledger-smb/sql/Pg-database.sql
+\i /home/ledgersmb/ledger-smb/sql/modules/Account.sql
+\i /home/ledgersmb/ledger-smb/sql/modules/Session.sql
+\i /home/ledgersmb/ledger-smb/sql/modules/chart.sql
+\i /home/ledgersmb/ledger-smb/sql/modules/Business_type.sql
+\i /home/ledgersmb/ledger-smb/sql/modules/Location.sql
+\i /home/ledgersmb/ledger-smb/sql/modules/Company.sql
+\i /home/ledgersmb/ledger-smb/sql/modules/Customer.sql
+\i /home/ledgersmb/ledger-smb/sql/modules/Date.sql
+\i /home/ledgersmb/ledger-smb/sql/modules/Defaults.sql
+\i /home/ledgersmb/ledger-smb/sql/modules/Settings.sql
+\i /home/ledgersmb/ledger-smb/sql/modules/Employee.sql
+\i /home/ledgersmb/ledger-smb/sql/modules/Entity.sql
+\i /home/ledgersmb/ledger-smb/sql/modules/Payment.sql
+\i /home/ledgersmb/ledger-smb/sql/modules/Person.sql
+\i /home/ledgersmb/ledger-smb/sql/modules/Report.sql
+\i /home/ledgersmb/ledger-smb/sql/modules/Voucher.sql
+
+GRANT EXECUTE ON FUNCTION eca__list_notes(int) TO lsmb_paycom_eu__read_contact;
+GRANT INSERT ON eca_note TO lsmb_paycom_eu__edit_contact;
+
+ALTER TABLE orig.vendor ADD COLUMN entity_id int;
+ALTER TABLE orig.vendor ADD COLUMN company_id int;
+ALTER TABLE orig.vendor ADD COLUMN credit_id int;
+
+INSERT INTO chart SELECT * FROM orig.chart;
+-- The blank entities are tests and not used for anything anyway.
+
+INSERT INTO business SELECT * FROM orig.business;
+
+-- Importing vendors
+
+CREATE TEMPORARY TABLE entity_temp (name text, control_code text);
+
+INSERT INTO entity_temp (name, control_code)
+SELECT name, vendornumber
+FROM orig.vendor
+GROUP BY name, vendornumber;  
+
+
+INSERT INTO entity (name, control_code, entity_class)
+SELECT name, control_code, 1
+FROM entity_temp
+WHERE control_code IS NOT NULL
+	AND name IS NOT NULL AND name <> ''
+GROUP BY name, control_code;
+
+UPDATE orig.vendor SET entity_id = coalesce((SELECT id FROM entity WHERE (vendornumber like control_code), entity_id);
+
+insert into entity (id, name, entity_class) values (-1, 'UNKNOWN', 1);
+
+DELETE FROM entity_credit_account; -- necessary because the builtin inventory
+                                   -- control accounts have the same meta_number
+                                   -- CT
+
+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, arap_accno_id, 
+	payment_accno_id, startdate, enddate, threshold, 1
+FROM orig.vendor;
+
+create UNIQUE index entity_credit_account_meta_number_idx_u on entity_credit_account (meta_number );
+
+UPDATE orig.vendor SET credit_id = 
+	(SELECT id FROM entity_credit_account e 
+	WHERE e.meta_number = vendornumber AND vendor.entity_id = e.entity_id);
+
+
+-- companies
+
+INSERT INTO company (entity_id, legal_name, tax_id)
+SELECT entity_id, name, max(taxnumber) FROM orig.vendor 
+WHERE entity_id IS NOT NULL AND entity_id IN (select id from entity) GROUP BY entity_id, name;
+
+UPDATE orig.vendor SET company_id = (select id from company c where entity_id = vendor.entity_id);
+
+-- Moving to a UNION query
+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 orig.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 orig.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 orig.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 orig.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 orig.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;
+-- 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
+    orig.address 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 orig.vendor ov ON ov.id = oa.trans_id
+JOIN entity_credit_account eca ON (ov.credit_id = eca.id)
+GROUP BY eca.id;
+
+
+ -- notes 
+
+INSERT INTO eca_note(note_class, ref_key, note, vector)
+SELECT 3, credit_id, notes, '' FROM orig.vendor 
+WHERE notes IS NOT NULL AND credit_id IS NOT NULL;
+
+alter table orig.employee add entity_id int;
+
+update orig.employee set entity_id = 
+	(select entity_id from person where first_name = employee.name 
+	UNION 
+	select entity_id from users where username = lower(employee.login));
+
+-- batches, transactions, and vouchers
+insert into batch (id, control_code, description, approved_on, approved_by, 
+	created_on, created_by, batch_class_id, default_date)
+select b.id, b.batchnumber, b.description, b.apprdate, me.entity_id, transdate, 
+	ee.entity_id, bc.id, b.transdate
+FROM orig.br b
+LEFT JOIN orig.employee me ON (b.managerid = me.id)
+LEFT JOIN orig.employee ee ON (ee.id = b.employee_id)
+JOIN public.batch_class bc ON (b.batch = bc.class);
+
+
+
+insert into ap 
+(entity_credit_account, person_id,
+	id, invnumber, transdate, taxincluded, amount, netamount, paid, 
+	datepaid, duedate, invoice, ordnumber, curr, notes, quonumber, intnotes,
+	department_id, shipvia, language_code, ponumber, shippingpoint, 
+	on_hold, approved, reverse, terms, description)
+SELECT 
+	vendor.credit_id,
+	(select entity_id from orig.employee 
+		WHERE id = ap.employee_id),
+	ap.id, invnumber, transdate, ap.taxincluded, amount, netamount, paid, 
+	datepaid, duedate, invoice, ordnumber, ap.curr, ap.notes, quonumber, 
+	intnotes,
+	department_id, shipvia, ap.language_code, ponumber, shippingpoint, 
+	onhold, approved, case when amount < 0 then true else false end,
+	ap.terms, description
+FROM orig.ap JOIN orig.vendor ON (ap.vendor_id = vendor.id);
+
+INSERT INTO gl
+(id, reference, description, transdate, person_id, notes, approved, department_id)
+SELECT id, reference, description, transdate, 
+	(select max(id) from person where entity_id = (select entity_id from orig.employee
+          WHERE id = gl.employee_id)),
+	notes, approved, department_id FROM orig.gl;
+
+insert into voucher(trans_id, batch_id, id, batch_class)
+select min(v.trans_id), v.br_id, v.id, bc.id
+from orig.vr v
+JOIN orig.br b ON (v.br_id = b.id)
+JOIN public.batch_class bc ON (b.batch = bc.class)
+JOIN public.transactions t ON (t.id = v.trans_id)
+group by v.br_id, bc.id, v.id;
+
+-- acc_trans entries/financial line items
+
+INSERT INTO acc_trans
+(trans_id, chart_id, amount, transdate, source, cleared, fx_transaction, 
+	project_id, memo, approved, cleared_on, reconciled_on, 
+	voucher_id)
+SELECT trans_id, chart_id, amount, transdate, source,
+	CASE WHEN cleared IS NOT NULL THEN TRUE ELSE FALSE END, fx_transaction,
+	project_id, memo, approved, cleared, reconciled, vr_id
+	FROM orig.acc_trans;
+
+-- sequences
+
+select setval('voucher_id_seq', (select max(id) from orig.vr));
+
+SELECT setval('batch_id_seq', (select max(id) from orig.br));
+
+SELECT setval('id', max(id)) FROM transactions;
+
+INSERT INTO defaults (setting_key, value)
+SELECT fldname, fldvalue FROM orig.defaults
+WHERE fldname = 'currencies';
+
+

This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.