[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[4887] trunk/sql/upgrade
- Subject: SF.net SVN: ledger-smb:[4887] trunk/sql/upgrade
- From: ..hidden..
- Date: Mon, 11 Jun 2012 02:47:15 +0000
Revision: 4887
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=4887&view=rev
Author: einhverfr
Date: 2012-06-11 02:47:14 +0000 (Mon, 11 Jun 2012)
Log Message:
-----------
Initial 1.2-1.4 upgrade script. Removing pre-1.3.0 svn snapshot upgrade scripts and rc related upgrade scripts
Added Paths:
-----------
trunk/sql/upgrade/1.2-1.4.sql
Removed Paths:
-------------
trunk/sql/upgrade/1.2-1.3.sql
trunk/sql/upgrade/3308-menu_update.sql
trunk/sql/upgrade/rc1-rc2.sql
trunk/sql/upgrade/rc2-rc3.sql
trunk/sql/upgrade/rc3-rc4.sql
trunk/sql/upgrade/rc4-1.3.0.sql
trunk/sql/upgrade/svn/3206-invoice_order.sql
trunk/sql/upgrade/svn/3252-uniques.sql
trunk/sql/upgrade/svn/3263-menu_generate.sql
trunk/sql/upgrade/svn/3286-qty_type.sql
trunk/sql/upgrade/svn/3290-tax-account.sql
trunk/sql/upgrade/svn/3293-recon_payee.sql
trunk/sql/upgrade/svn/3297-employee_changes.sql
trunk/sql/upgrade/svn/3299-person_functions.sql
trunk/sql/upgrade/svn/3302-menu_changes.sql
trunk/sql/upgrade/svn/3308-menu_update.sql
trunk/sql/upgrade/svn/3310-taxform-function.sql
trunk/sql/upgrade/svn/3313-list_taxform_menu.sql
trunk/sql/upgrade/svn/3313-role-updates.sql
trunk/sql/upgrade/svn/3319-taxtable.-change.sql
trunk/sql/upgrade/svn/3326-new_custom_fields_funcs.sql
trunk/sql/upgrade/svn/3328-drop-location_result.sql
trunk/sql/upgrade/svn/3331-drop-bank-acct-save.sql
trunk/sql/upgrade/svn/3333-location-function-drop.sql
trunk/sql/upgrade/svn/3335-drop-location_saves.sql
trunk/sql/upgrade/svn/3353-add-assembly-fkeys.sql
trunk/sql/upgrade/svn/3355-yearend-fkey.sql
trunk/sql/upgrade/svn/3356-fkeys.sql
trunk/sql/upgrade/svn/3372-drop-location-delete.sql
trunk/sql/upgrade/svn/3383-menu-attributes.sql
trunk/sql/upgrade/svn/3386-drop-type.sql
trunk/sql/upgrade/svn/3410-menu-functions.sql
trunk/sql/upgrade/svn/3421-tax_tables.sql
trunk/sql/upgrade/svn/3422-tax_tables.sql
trunk/sql/upgrade/svn/3441-periods-view.sql
trunk/sql/upgrade/svn/3445-drop-save-user.sql
trunk/sql/upgrade/svn/3448-drop-location-search.sql
trunk/sql/upgrade/svn/3450-drop-user-function.sql
trunk/sql/upgrade/svn/3455-asset-schema.sql
trunk/sql/upgrade/svn/3467-assets.sql
trunk/sql/upgrade/svn/3470-asset-menu.sql
trunk/sql/upgrade/svn/3476-drop-unused-fields.sql
trunk/sql/upgrade/svn/3478-files.sql
trunk/sql/upgrade/svn/3479-inheritance_pkeys.sql
trunk/sql/upgrade/svn/3480-pkeys-2.sql
trunk/sql/upgrade/svn/3482-file_view_catelog.sql
trunk/sql/upgrade/svn/3495_drop_mime_func.sql
trunk/sql/upgrade/svn/3497-schema-changes.sql
trunk/sql/upgrade/svn/3509-drop-function.sql
trunk/sql/upgrade/svn/3532-drop-used-function.sql
trunk/sql/upgrade/svn/3534-drop_unused_function.sql
trunk/sql/upgrade/svn/3536-menu-update.sql
trunk/sql/upgrade/svn/3537-note-table.sql
trunk/sql/upgrade/svn/3538-asset-menu-fix.sql
trunk/sql/upgrade/svn/3547-constraints-upgrade.sql
trunk/sql/upgrade/svn/3551-asset-approval-menu.sql
trunk/sql/upgrade/svn/3560-settings.sql
trunk/sql/upgrade/svn/3572-letterhead-template-editing.sql
trunk/sql/upgrade/svn/3585-mime-type-data.sql
trunk/sql/upgrade/svn/3594-drop-batch-update.sql
trunk/sql/upgrade/svn/3613-drop-file_type.sql
trunk/sql/upgrade/svn/3626-more-mime-types.sql
trunk/sql/upgrade/svn/3637-dbversion-upgrade.sql
trunk/sql/upgrade/svn/3640-new-file-type.sql
trunk/sql/upgrade/svn/3679-drop-payment-function.sql
trunk/sql/upgrade/svn/3686-batch_post.sql
trunk/sql/upgrade/svn/3694-update_version.sql
trunk/sql/upgrade/svn/3703-grant-select-perm.sql
trunk/sql/upgrade/svn/3714-drop-recon-delete.sql
trunk/sql/upgrade/svn/3738-drop-session-transaction-id.sql
trunk/sql/upgrade/svn/3760-alter-asset_reports.sql
trunk/sql/upgrade/svn/3764-control_code_index.sql
trunk/sql/upgrade/svn/3808-language-table.sql
Deleted: trunk/sql/upgrade/1.2-1.3.sql
===================================================================
--- trunk/sql/upgrade/1.2-1.3.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/1.2-1.3.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,660 +0,0 @@
---Setup
-
--- 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 ?>'''
-
-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) = lower(: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) = lower(: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 and entity_class = 1
- and e.entity_id = vendor.entity_id);
-
-
-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 and entity_class = 2);
-
---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, name, country_id)
-select 'E-' || employeenumber, 3, name,
- (select id from country where lower(short_name) = lower(: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
- WHERE login IS NOT NULL;
-
-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.entity_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.3.0' WHERE setting_key = 'version';
-
-
-COMMIT;
---TODO: Translation migratiion. Partsgroups?
--- TODO: User/password Migration
Copied: trunk/sql/upgrade/1.2-1.4.sql (from rev 4878, trunk/sql/upgrade/1.2-1.3.sql)
===================================================================
--- trunk/sql/upgrade/1.2-1.4.sql (rev 0)
+++ trunk/sql/upgrade/1.2-1.4.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -0,0 +1,685 @@
+--Setup
+
+-- 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 ?>'''
+
+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;
+
+-- Buisness Reporting Units
+
+INSERT INTO business_unit (class_id, id, control_code, description
+ SELECT 1, id, role || id::text, description FROM lsmb12.department;
+
+
+--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) = lower(: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) = lower(: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 and entity_class = 1
+ and e.entity_id = vendor.entity_id);
+
+
+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 and entity_class = 2);
+
+--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, name, country_id)
+select 'E-' || employeenumber, 3, name,
+ (select id from country where lower(short_name) = lower(: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
+ WHERE login IS NOT NULL;
+
+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)
+ SELECT gl.id, reference, description, transdate, p.id, gl.notes
+ 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,
+ 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,
+ 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,
+ 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,
+ 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, memo, invoice_id, entry_id)
+ SELECT trans_id, a.id, amount, transdate, source, cleared,
+ fx_transaction, 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 business_unit_ac (entry_id, bu_class_id, bu_id)
+ SELECT ac.entry_id, 1, gl.department_id
+ FROM acc_trans ac
+ JOIN (select id, department_id from gl
+ UNION
+ SELECT id, department_id FROM ar
+ UNION
+ SELECT id, department_id FROM ap) gl ON ac.trans_id = gl.id
+ UNION
+ SELECT ac.entry_id, 2, ac.project_id + 1000
+ FROM lsmb12.acc_trans ac;
+
+INSERT INTO invoice (id, trans_id, parts_id, description, qty, allocated,
+ sellprice, fxsellprice, discount, assemblyitem, unit,
+ deliverydate, serialnumber, notes)
+ SELECT id, trans_id, parts_id, description, qty, allocated,
+ sellprice, fxsellprice, discount, assemblyitem, unit,
+ 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 business_unit_inv (entry_id, bu_class_id, bu_id)
+ SELECT inv.id, 1, gl.department_id
+ FROM invoice inv
+ JOIN (select id, department_id from gl
+ UNION
+ SELECT id, department_id FROM ar
+ UNION
+ SELECT id, department_id FROM ap) gl ON ac.trans_id = gl.id
+ UNION
+ SELECT inv.id, 2, ac.project_id + 1000
+ FROM lsmb12.invoice inv;
+
+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 business_unit (id,control_code, description, startdate, enddate,
+ credit_id)
+ SELECT p.id, projectnumber, description, p.startdate, p.enddate,
+ 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 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.entity_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, business_unit_id, parts_id, description, qty, allocated,
+ sellprice, fxsellprice, serialnumber, checkedin, checkedout,
+ person_id, notes)
+ SELECT j.id, project_id + 1000, 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.3.0' WHERE setting_key = 'version';
+
+
+COMMIT;
+--TODO: Translation migratiion. Partsgroups?
+-- TODO: User/password Migration
Deleted: trunk/sql/upgrade/3308-menu_update.sql
===================================================================
--- trunk/sql/upgrade/3308-menu_update.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/3308-menu_update.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,9 +0,0 @@
-
-insert into menu_attribute (node_id, attribute, value)
-values (49, 'l_first_name', '1');
-insert into menu_attribute (node_id, attribute, value)
-values (49, 'l_id', '1');
-insert into menu_attribute (node_id, attribute, value)
-values (49, 'l_startdate', '1');
-insert into menu_attribute (node_id, attribute, value)
-values (49, 'l_enddate', '1');
Deleted: trunk/sql/upgrade/rc1-rc2.sql
===================================================================
--- trunk/sql/upgrade/rc1-rc2.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/rc1-rc2.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1 +0,0 @@
-GRANT SELECT ON partsgroup_translation TO public;
Deleted: trunk/sql/upgrade/rc2-rc3.sql
===================================================================
--- trunk/sql/upgrade/rc2-rc3.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/rc2-rc3.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,2 +0,0 @@
-DROP FUNCTION reconciliation__delete_report(in_report_id int);
-ALTER TABLE session DROP transaction_id;
Deleted: trunk/sql/upgrade/rc3-rc4.sql
===================================================================
--- trunk/sql/upgrade/rc3-rc4.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/rc3-rc4.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,5 +0,0 @@
-alter table asset_report alter column entered_by
-set default person__get_my_entity_id();
-
-DROP TYPE asset_nbv_line CASCADE;
-ALTER TABLE entity ADD UNIQUE (control_code);
Deleted: trunk/sql/upgrade/rc4-1.3.0.sql
===================================================================
--- trunk/sql/upgrade/rc4-1.3.0.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/rc4-1.3.0.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,40 +0,0 @@
-INSERT INTO language (code, description)
-VALUES ('ar_EG', 'Arabic (Egypt)'),
- ('bg', 'Bulgarian'),
- ('ca', 'Catalan'),
- ('cs', 'Czech'),
- ('da', 'Danish'),
- ('de', 'German'),
- ('de_CH', 'German (Switzerland)'),
- ('el', 'Greek'),
- ('en', 'English'),
- ('es', 'Spanish'),
- ('es_CO', 'Spanish (Colombia)'),
- ('es_EC', 'Spanish (Ecuador)'),
- ('es_MX', 'Spanish (Mexico)'),
- ('es_PA', 'Spanish (Panama)'),
- ('es_PY', 'Spanish (Paraguay)'),
- ('es_VE', 'Spanish (Venezuela)'),
- ('et', 'Estonian'),
- ('fi', 'Finnish'),
- ('fr', 'French'),
- ('fr_BE', 'French (Belgium)'),
- ('fr_CA', 'French (Canada)'),
- ('hu', 'Hungarian'),
- ('id', 'Indonesian'),
- ('is', 'Icelandic'),
- ('it', 'Italian'),
- ('lt', 'Latvian'),
- ('nb', 'Norwegian'),
- ('nl', 'Dutch'),
- ('nl_BE', 'Dutch (Belgium)'),
- ('pl', 'Polish'),
- ('pt', 'Portuguese'),
- ('pt_BR', 'Portuguese (Brazil)'),
- ('ru', 'Russian'),
- ('sv', 'Swedish'),
- ('tr', 'Turkish'),
- ('uk', 'Ukranian'),
- ('zh_CN', 'Chinese (China)'),
- ('zh_TW', 'Chinese (Taiwan)');
-
Deleted: trunk/sql/upgrade/svn/3206-invoice_order.sql
===================================================================
--- trunk/sql/upgrade/svn/3206-invoice_order.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3206-invoice_order.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,2 +0,0 @@
-ALTER TABLE invoice ADD precision int;
-ALTER TABLE orderitems ADD precision int;
Deleted: trunk/sql/upgrade/svn/3252-uniques.sql
===================================================================
--- trunk/sql/upgrade/svn/3252-uniques.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3252-uniques.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,2 +0,0 @@
-ALTER TABLE ar ADD unique(invnumber);
-ALTER TABLE person ADD UNIQUE (entity_id);
Deleted: trunk/sql/upgrade/svn/3263-menu_generate.sql
===================================================================
--- trunk/sql/upgrade/svn/3263-menu_generate.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3263-menu_generate.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,118 +0,0 @@
-
-
-CREATE OR REPLACE FUNCTION menu_generate() RETURNS SETOF menu_item AS
-$$
-DECLARE
- item menu_item;
- arg menu_attribute%ROWTYPE;
-BEGIN
- FOR item IN
- SELECT n.position, n.id, c.level, n.label, c.path,
- to_args(array[ma.attribute, ma.value])
- FROM connectby('menu_node', 'id', 'parent', 'position', '0',
- 0, ',')
- c(id integer, parent integer, "level" integer,
- path text, list_order integer)
- JOIN menu_node n USING(id)
- JOIN menu_attribute ma ON (n.id = ma.node_id)
- WHERE n.id IN (select node_id FROM menu_acl
- WHERE pg_has_role(CASE WHEN role_name
- ilike 'public'
- THEN current_user
- ELSE role_name
- END, 'USAGE')
- GROUP BY node_id
- HAVING bool_and(CASE WHEN acl_type ilike 'DENY'
- THEN FALSE
- WHEN acl_type ilike 'ALLOW'
- THEN TRUE
- END))
- or exists (select cn.id, cc.path
- FROM connectby('menu_node', 'id', 'parent',
- 'position', '0', 0, ',')
- cc(id integer, parent integer,
- "level" integer, path text,
- list_order integer)
- JOIN menu_node cn USING(id)
- WHERE cn.id IN
- (select node_id FROM menu_acl
- WHERE pg_has_role(CASE WHEN role_name
- ilike 'public'
- THEN current_user
- ELSE role_name
- END, 'USAGE')
- GROUP BY node_id
- HAVING bool_and(CASE WHEN acl_type
- ilike 'DENY'
- THEN false
- WHEN acl_type
- ilike 'ALLOW'
- THEN TRUE
- END))
- and cc.path like c.path || '%')
- GROUP BY n.position, n.id, c.level, n.label, c.path, c.list_order
- ORDER BY c.list_order
-
- LOOP
- RETURN NEXT item;
- END LOOP;
-END;
-$$ language plpgsql;
-
-CREATE OR REPLACE FUNCTION menu_children(in_parent_id int) RETURNS SETOF menu_item
-AS $$
-declare
- item menu_item;
- arg menu_attribute%ROWTYPE;
-begin
- FOR item IN
- SELECT n.position, n.id, c.level, n.label, c.path,
- to_args(array[ma.attribute, ma.value])
- FROM connectby('menu_node', 'id', 'parent', 'position',
- in_parent_id, 1, ',')
- c(id integer, parent integer, "level" integer,
- path text, list_order integer)
- JOIN menu_node n USING(id)
- JOIN menu_attribute ma ON (n.id = ma.node_id)
- WHERE n.id IN (select node_id FROM menu_acl
- WHERE pg_has_role(CASE WHEN role_name
- ilike 'public'
- THEN current_user
- ELSE role_name
- END, 'USAGE')
- GROUP BY node_id
- HAVING bool_and(CASE WHEN acl_type ilike 'DENY'
- THEN FALSE
- WHEN acl_type ilike 'ALLOW'
- THEN TRUE
- END))
- or exists (select cn.id, cc.path
- FROM connectby('menu_node', 'id', 'parent',
- 'position', '0', 0, ',')
- cc(id integer, parent integer,
- "level" integer, path text,
- list_order integer)
- JOIN menu_node cn USING(id)
- WHERE cn.id IN
- (select node_id FROM menu_acl
- WHERE pg_has_role(CASE WHEN role_name
- ilike 'public'
- THEN current_user
- ELSE role_name
- END, 'USAGE')
- GROUP BY node_id
- HAVING bool_and(CASE WHEN acl_type
- ilike 'DENY'
- THEN false
- WHEN acl_type
- ilike 'ALLOW'
- THEN TRUE
- END))
- and cc.path like c.path || '%')
- GROUP BY n.position, n.id, c.level, n.label, c.path, c.list_order
- ORDER BY c.list_order
- LOOP
- return next item;
- end loop;
-end;
-$$ language plpgsql;
Deleted: trunk/sql/upgrade/svn/3286-qty_type.sql
===================================================================
--- trunk/sql/upgrade/svn/3286-qty_type.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3286-qty_type.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1 +0,0 @@
-alter table invoice alter qty type numeric;
Deleted: trunk/sql/upgrade/svn/3290-tax-account.sql
===================================================================
--- trunk/sql/upgrade/svn/3290-tax-account.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3290-tax-account.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,107 +0,0 @@
-
-ALTER TABLE account ADD tax bool not null default false;
-
-UPDATE account
- SET tax = true
- WHERE id IN (SELECT account_id
- FROM account_link
- WHERE description LIKE '%_tax'
- UNION
- SELECT chart_id
- FROM tax);
-
-DROP FUNCTION account_save
-(in_id int, in_accno text, in_description text, in_category char(1),
-in_gifi_accno text, in_heading int, in_contra bool, in_link text[]);
-
-CREATE OR REPLACE FUNCTION account_save
-(in_id int, in_accno text, in_description text, in_category char(1),
-in_gifi_accno text, in_heading int, in_contra bool, in_tax bool,
-in_link text[])
-RETURNS int AS $$
-DECLARE
- t_heading_id int;
- t_link record;
- t_id int;
-BEGIN
- -- check to ensure summary accounts are exclusive
- -- necessary for proper handling by legacy code
- FOR t_link IN SELECT description FROM account_link_description
- WHERE summary='t'
- LOOP
- IF t_link.description = ANY (in_link)
- and array_upper(in_link, 1) > 1 THEN
- RAISE EXCEPTION 'Invalid link settings: Summary';
- END IF;
- END LOOP;
- -- heading settings
- IF in_heading IS NULL THEN
- SELECT id INTO t_heading_id FROM account_heading
- WHERE accno < in_accno order by accno desc limit 1;
- ELSE
- t_heading_id := in_heading;
- END IF;
-
- -- don't remove custom links.
- DELETE FROM account_link
- WHERE account_id = in_id
- and description in ( select description
- from account_link_description
- where custom = 'f');
-
- UPDATE account
- SET accno = in_accno,
- description = in_description,
- category = in_category,
- gifi_accno = in_gifi_accno,
- heading = t_heading_id,
- contra = in_contra,
- tax = in_tax
- WHERE id = in_id;
-
- IF FOUND THEN
- t_id := in_id;
- ELSE
- INSERT INTO account (accno, description, category, gifi_accno,
- heading, contra, tax)
- VALUES (in_accno, in_description, in_category, in_gifi_accno,
- t_heading_id, in_contra, in_tax);
-
- t_id := currval('account_id_seq');
- END IF;
-
- FOR t_link IN
- select in_link[generate_series] AS val
- FROM generate_series(array_lower(in_link, 1),
- array_upper(in_link, 1))
- LOOP
- INSERT INTO account_link (account_id, description)
- VALUES (t_id, t_link.val);
- END LOOP;
-
-
- RETURN t_id;
-END;
-$$ language plpgsql;
-
-
-DROP VIEW chart CASCADE;
-
-CREATE VIEW chart AS
-SELECT id, accno, description, 'H' as charttype, NULL as category, NULL as link, NULL as account_heading, null as gifi_accno, false as contra, false as tax from account_heading UNION
-select c.id, c.accno, c.description, 'A' as charttype, c.category, concat_colon(l.description) as link, heading, gifi_accno, contra, tax from account c left join account_link l ON (c.id = l.account_id) group by c.id, c.accno, c.description, c.category, c.heading, c.gifi_accno, c.contra, c.tax;
-
-GRANT SELECT ON chart TO public;
-
-CREATE OR REPLACE RULE chart_i AS ON INSERT TO chart
-DO INSTEAD
-SELECT CASE WHEN new.charttype='H' THEN
- account_heading_save(new.id, new.accno, new.description, NULL)
-ELSE
- account_save(new.id, new.accno, new.description, new.category,
- new.gifi_accno, NULL,
- CASE WHEN new.contra IS NULL THEN FALSE ELSE new.contra END,
- CASE WHEN new.tax IS NULL THEN FALSE ELSE new.tax END,
- string_to_array(new.link, ':'))
-END;
-
Deleted: trunk/sql/upgrade/svn/3293-recon_payee.sql
===================================================================
--- trunk/sql/upgrade/svn/3293-recon_payee.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3293-recon_payee.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,32 +0,0 @@
-
-CREATE OR REPLACE VIEW recon_payee AS
- SELECT n.name AS payee, rr.id, rr.report_id, rr.scn, rr.their_balance, rr.our_balance, rr.errorcode, rr."user", rr.clear_time, rr.insert_time, rr.trans_type, rr.post_date, rr.ledger_id, rr.voucher_id, rr.overlook, rr.cleared
- FROM cr_report_line rr
- LEFT JOIN acc_trans ac ON rr.ledger_id = ac.entry_id
- LEFT JOIN gl ON ac.trans_id = gl.id
- LEFT JOIN (( SELECT ap.id, e.name
- FROM ap
- JOIN entity_credit_account eca ON ap.entity_credit_account = eca.id
- JOIN entity e ON eca.entity_id = e.id
-UNION
- SELECT ar.id, e.name
- FROM ar
- JOIN entity_credit_account eca ON ar.entity_credit_account = eca.id
- JOIN entity e ON eca.entity_id = e.id)
-UNION
- SELECT gl.id, gl.description
- FROM gl) n ON n.id = ac.trans_id;
-
-
-CREATE OR REPLACE FUNCTION reconciliation__report_details_payee (in_report_id INT) RETURNS setof recon_payee as $$
- DECLARE
- row recon_payee;
- BEGIN
- FOR row IN
- select * from recon_payee where report_id = in_report_id
- order by scn, post_date
- LOOP
- RETURN NEXT row;
- END LOOP;
- END;
-$$ language 'plpgsql';
\ No newline at end of file
Deleted: trunk/sql/upgrade/svn/3297-employee_changes.sql
===================================================================
--- trunk/sql/upgrade/svn/3297-employee_changes.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3297-employee_changes.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,36 +0,0 @@
-drop function employee__get(int);
-
-CREATE TYPE employee_result AS (
- entity_id int,
- person_id int,
- salutation text,
- first_name text,
- middle_name text,
- last_name text,
- startdate date,
- enddate date,
- role varchar(20),
- ssn text,
- sales bool,
- manager_id int,
- manager_first_name text,
- manager_last_name text,
- employeenumber varchar(32),
- dob date
-);
-
-CREATE OR REPLACE FUNCTION employee__get
-(in_entity_id integer)
-returns employee_result as
-$$
- SELECT p.entity_id, p.id, s.salutation,
- p.first_name, p.middle_name, p.last_name,
- ee.startdate, ee.enddate, ee.role, ee.ssn, ee.sales, ee.manager_id,
- mp.first_name, mp.last_name, ee.employeenumber, ee.dob
- FROM person p
- JOIN entity_employee ee on (ee.entity_id = p.entity_id)
-LEFT JOIN salutation s on (p.salutation_id = s.id)
-LEFT JOIN person mp ON ee.manager_id = p.entity_id
- WHERE p.entity_id = $1;
-$$ language sql;
-
Deleted: trunk/sql/upgrade/svn/3299-person_functions.sql
===================================================================
--- trunk/sql/upgrade/svn/3299-person_functions.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3299-person_functions.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,30 +0,0 @@
-CREATE OR REPLACE FUNCTION person__list_bank_account(in_entity_id int)
-RETURNS SETOF entity_bank_account AS
-$$
-DECLARE out_row entity_bank_account%ROWTYPE;
-BEGIN
- FOR out_row IN
- SELECT * from entity_bank_account where entity_id = in_entity_id
- LOOP
- RETURN NEXT out_row;
- END LOOP;
-END;
-$$ LANGUAGE PLPGSQL;
-
-CREATE OR REPLACE FUNCTION person__list_notes(in_entity_id int)
-RETURNS SETOF entity_note AS
-$$
-DECLARE out_row record;
-BEGIN
- FOR out_row IN
- SELECT *
- FROM entity_note
- WHERE ref_key = in_entity_id
- ORDER BY created
- LOOP
- RETURN NEXT out_row;
- END LOOP;
-END;
-$$ LANGUAGE PLPGSQL;
-
-
Deleted: trunk/sql/upgrade/svn/3302-menu_changes.sql
===================================================================
--- trunk/sql/upgrade/svn/3302-menu_changes.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3302-menu_changes.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,18 +0,0 @@
-update menu_attribute
- set node_id = 49,
- attribute = 'l_last_name',
- value = '1'
- WHERE id = 115;
-update menu_attribute
- set node_id = 49,
- attribute = 'l_employeenumber',
- value = '1'
- WHERE id = 116;
-update menu_attribute
- set node_id = 49,
- attribute = 'module',
- value = 'employee.pl'
- WHERE id = 118;
-
-
-
Deleted: trunk/sql/upgrade/svn/3308-menu_update.sql
===================================================================
--- trunk/sql/upgrade/svn/3308-menu_update.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3308-menu_update.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,9 +0,0 @@
-
-insert into menu_attribute (node_id, attribute, value)
-values (49, 'l_first_name', '1');
-insert into menu_attribute (node_id, attribute, value)
-values (49, 'l_id', '1');
-insert into menu_attribute (node_id, attribute, value)
-values (49, 'l_startdate', '1');
-insert into menu_attribute (node_id, attribute, value)
-values (49, 'l_enddate', '1');
Deleted: trunk/sql/upgrade/svn/3310-taxform-function.sql
===================================================================
--- trunk/sql/upgrade/svn/3310-taxform-function.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3310-taxform-function.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,3 +0,0 @@
-DROP FUNCTION tax_form__save(in_country_id int,
- in_form_name text, in_default_reportable bool);
-
Deleted: trunk/sql/upgrade/svn/3313-list_taxform_menu.sql
===================================================================
--- trunk/sql/upgrade/svn/3313-list_taxform_menu.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3313-list_taxform_menu.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,11 +0,0 @@
-SELECT menu_insert(217, 2, 'List Tax Forms');
-INSERT INTO menu_attribute(node_id, attribute, value)
- VALUES (currval('menu_node_id_seq')::int, 'module', 'taxform.pl');
-INSERT INTO menu_attribute(node_id, attribute, value)
- VALUES (currval('menu_node_id_seq')::int, 'action', 'list_all');
-
-SELECT menu_insert(217, 3, 'Reports');
-INSERT INTO menu_attribute(node_id, attribute, value)
- VALUES (currval('menu_node_id_seq')::int, 'module', 'taxform.pl');
-
-
Deleted: trunk/sql/upgrade/svn/3313-role-updates.sql
===================================================================
--- trunk/sql/upgrade/svn/3313-role-updates.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3313-role-updates.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,4 +0,0 @@
-
-INSERT INTO menu_acl (node_id, acl_type, role_name)
-SELECT id, 'allow', 'lsmb_' || current_database() ||'__tax_form_save'
- FROM menu_node WHERE parent = 217 and position in (2,3);
Deleted: trunk/sql/upgrade/svn/3319-taxtable.-change.sql
===================================================================
--- trunk/sql/upgrade/svn/3319-taxtable.-change.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3319-taxtable.-change.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1 +0,0 @@
-ALTER TABLE tax alter column validto set not null;
Deleted: trunk/sql/upgrade/svn/3326-new_custom_fields_funcs.sql
===================================================================
--- trunk/sql/upgrade/svn/3326-new_custom_fields_funcs.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3326-new_custom_fields_funcs.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,53 +0,0 @@
-
-
-CREATE OR REPLACE FUNCTION add_custom_field (VARCHAR, VARCHAR, VARCHAR)
-RETURNS BOOL AS
-'
-DECLARE
-table_name ALIAS FOR $1;
-new_field_name ALIAS FOR $2;
-field_datatype ALIAS FOR $3;
-
-BEGIN
- perform TABLE_ID FROM custom_table_catalog
- WHERE extends = table_name;
- IF NOT FOUND THEN
- BEGIN
- INSERT INTO custom_table_catalog (extends)
- VALUES (table_name);
- EXECUTE ''CREATE TABLE '' ||
- quote_ident(''custom_'' ||table_name) ||
- '' (row_id INT PRIMARY KEY)'';
- EXCEPTION WHEN duplicate_table THEN
- -- do nothing
- END;
- END IF;
- INSERT INTO custom_field_catalog (field_name, table_id)
- values (new_field_name, (SELECT table_id
- FROM custom_table_catalog
- WHERE extends = table_name));
- EXECUTE ''ALTER TABLE ''|| quote_ident(''custom_''||table_name) ||
- '' ADD COLUMN '' || quote_ident(new_field_name) || '' '' ||
- quote_ident(field_datatype);
- RETURN TRUE;
-END;
-' LANGUAGE PLPGSQL;
--- end function
-
-CREATE OR REPLACE FUNCTION drop_custom_field (VARCHAR, VARCHAR)
-RETURNS BOOL AS
-'
-DECLARE
-table_name ALIAS FOR $1;
-custom_field_name ALIAS FOR $2;
-BEGIN
- DELETE FROM custom_field_catalog
- WHERE field_name = custom_field_name AND
- table_id = (SELECT table_id FROM custom_table_catalog
- WHERE extends = table_name);
- EXECUTE ''ALTER TABLE '' || quote_ident(''custom_'' || table_name) ||
- '' DROP COLUMN '' || quote_ident(custom_field_name);
- RETURN TRUE;
-END;
-' LANGUAGE PLPGSQL;
--- end function
Deleted: trunk/sql/upgrade/svn/3328-drop-location_result.sql
===================================================================
--- trunk/sql/upgrade/svn/3328-drop-location_result.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3328-drop-location_result.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1 +0,0 @@
-drop type location_result cascade;
Deleted: trunk/sql/upgrade/svn/3331-drop-bank-acct-save.sql
===================================================================
--- trunk/sql/upgrade/svn/3331-drop-bank-acct-save.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3331-drop-bank-acct-save.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,6 +0,0 @@
-DROP FUNCTION eca__save_bank_account
-(in_entity_id int, in_credit_id int, in_bic text, in_iban text);
-
-DROP FUNCTION entity__save_bank_account
-(in_entity_id int, in_bic text, in_iban text);
-
Deleted: trunk/sql/upgrade/svn/3333-location-function-drop.sql
===================================================================
Deleted: trunk/sql/upgrade/svn/3335-drop-location_saves.sql
===================================================================
--- trunk/sql/upgrade/svn/3335-drop-location_saves.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3335-drop-location_saves.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,17 +0,0 @@
-DROP function eca__location_save(
- in_credit_id int, in_location_id int,
- in_location_class int, in_line_one text, in_line_two text,
- in_line_three text, in_city TEXT, in_state TEXT, in_mail_code text,
- in_country_code int);
-
-DROP function person__save_location(
- in_entity_id int,
- in_location_id int,
- in_location_class int,
- in_line_one text,
- in_line_two text,
- in_line_three text,
- in_city TEXT,
- in_state TEXT,
- in_mail_code text,
- in_country_code int);
Deleted: trunk/sql/upgrade/svn/3353-add-assembly-fkeys.sql
===================================================================
--- trunk/sql/upgrade/svn/3353-add-assembly-fkeys.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3353-add-assembly-fkeys.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,2 +0,0 @@
-ALTER TABLE assembly ADD foreign key (id) REFERENCES parts(id);
-ALTER TABLE assembly ADD foreign key (parts_id) REFERENCES parts(id);
Deleted: trunk/sql/upgrade/svn/3355-yearend-fkey.sql
===================================================================
--- trunk/sql/upgrade/svn/3355-yearend-fkey.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3355-yearend-fkey.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1 +0,0 @@
-ALTER TABLE yearend ADD FOREIGN KEY (trans_id) REFERENCES gl(id);
Deleted: trunk/sql/upgrade/svn/3356-fkeys.sql
===================================================================
--- trunk/sql/upgrade/svn/3356-fkeys.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3356-fkeys.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,11 +0,0 @@
-ALTER TABLE partscustomer ALTER COLUMN credit_id drop not null;
-
-ALTER TABLE partscustomer
-ADD foreign key (pricegroup_id) references pricegroup(id);
-
-CREATE TABLE parts_translation () INHERITS (translation);
-ALTER TABLE parts_translation ADD foreign key (trans_id) REFERENCES parts(id);
-
-CREATE TABLE project_translation () INHERITS (translation);
-ALTER TABLE project_translation
-ADD foreign key (trans_id) REFERENCES project(id);
Deleted: trunk/sql/upgrade/svn/3372-drop-location-delete.sql
===================================================================
--- trunk/sql/upgrade/svn/3372-drop-location-delete.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3372-drop-location-delete.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1 +0,0 @@
-DROP FUNCTION person__delete_location (in_entity_id INT, in_location_id INT);
Deleted: trunk/sql/upgrade/svn/3383-menu-attributes.sql
===================================================================
--- trunk/sql/upgrade/svn/3383-menu-attributes.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3383-menu-attributes.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,8 +0,0 @@
-insert into menu_attribute(node_id, attribute, value)
-values (87, 'module', 'ic.pl');
-
-insert into menu_attribute(node_id, attribute, value)
-values (86, 'searchitems', 'all');
-
-insert into menu_attribute(node_id, attribute, value)
-values (86, 'action', 'search');
Deleted: trunk/sql/upgrade/svn/3386-drop-type.sql
===================================================================
--- trunk/sql/upgrade/svn/3386-drop-type.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3386-drop-type.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1 +0,0 @@
-DROP TYPE tax_form_report_detail_item CASCADE;
Deleted: trunk/sql/upgrade/svn/3410-menu-functions.sql
===================================================================
--- trunk/sql/upgrade/svn/3410-menu-functions.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3410-menu-functions.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,117 +0,0 @@
-CREATE OR REPLACE FUNCTION menu_generate() RETURNS SETOF menu_item AS
-$$
-DECLARE
- item menu_item;
- arg menu_attribute%ROWTYPE;
-BEGIN
- FOR item IN
- SELECT n.position, n.id, c.level, n.label, c.path,
- to_args(array[ma.attribute, ma.value])
- FROM connectby('menu_node', 'id', 'parent', 'position', '0',
- 0, ',')
- c(id integer, parent integer, "level" integer,
- path text, list_order integer)
- JOIN menu_node n USING(id)
- JOIN menu_attribute ma ON (n.id = ma.node_id)
- WHERE n.id IN (select node_id FROM menu_acl
- WHERE pg_has_role(CASE WHEN role_name
- ilike 'public'
- THEN current_user
- ELSE role_name
- END, 'USAGE')
- GROUP BY node_id
- HAVING bool_and(CASE WHEN acl_type ilike 'DENY'
- THEN FALSE
- WHEN acl_type ilike 'ALLOW'
- THEN TRUE
- END))
- or exists (select cn.id, cc.path
- FROM connectby('menu_node', 'id', 'parent',
- 'position', '0', 0, ',')
- cc(id integer, parent integer,
- "level" integer, path text,
- list_order integer)
- JOIN menu_node cn USING(id)
- WHERE cn.id IN
- (select node_id FROM menu_acl
- WHERE pg_has_role(CASE WHEN role_name
- ilike 'public'
- THEN current_user
- ELSE role_name
- END, 'USAGE')
- GROUP BY node_id
- HAVING bool_and(CASE WHEN acl_type
- ilike 'DENY'
- THEN false
- WHEN acl_type
- ilike 'ALLOW'
- THEN TRUE
- END))
- and cc.path like c.path || ',%')
- GROUP BY n.position, n.id, c.level, n.label, c.path, c.list_order
- ORDER BY c.list_order
-
- LOOP
- RETURN NEXT item;
- END LOOP;
-END;
-$$ language plpgsql;
-
-CREATE OR REPLACE FUNCTION menu_children(in_parent_id int) RETURNS SETOF menu_item
-AS $$
-declare
- item menu_item;
- arg menu_attribute%ROWTYPE;
-begin
- FOR item IN
- SELECT n.position, n.id, c.level, n.label, c.path,
- to_args(array[ma.attribute, ma.value])
- FROM connectby('menu_node', 'id', 'parent', 'position',
- in_parent_id, 1, ',')
- c(id integer, parent integer, "level" integer,
- path text, list_order integer)
- JOIN menu_node n USING(id)
- JOIN menu_attribute ma ON (n.id = ma.node_id)
- WHERE n.id IN (select node_id FROM menu_acl
- WHERE pg_has_role(CASE WHEN role_name
- ilike 'public'
- THEN current_user
- ELSE role_name
- END, 'USAGE')
- GROUP BY node_id
- HAVING bool_and(CASE WHEN acl_type ilike 'DENY'
- THEN FALSE
- WHEN acl_type ilike 'ALLOW'
- THEN TRUE
- END))
- or exists (select cn.id, cc.path
- FROM connectby('menu_node', 'id', 'parent',
- 'position', '0', 0, ',')
- cc(id integer, parent integer,
- "level" integer, path text,
- list_order integer)
- JOIN menu_node cn USING(id)
- WHERE cn.id IN
- (select node_id FROM menu_acl
- WHERE pg_has_role(CASE WHEN role_name
- ilike 'public'
- THEN current_user
- ELSE role_name
- END, 'USAGE')
- GROUP BY node_id
- HAVING bool_and(CASE WHEN acl_type
- ilike 'DENY'
- THEN false
- WHEN acl_type
- ilike 'ALLOW'
- THEN TRUE
- END))
- and cc.path like c.path || ',%')
- GROUP BY n.position, n.id, c.level, n.label, c.path, c.list_order
- ORDER BY c.list_order
- LOOP
- return next item;
- end loop;
-end;
-$$ language plpgsql;
-
Deleted: trunk/sql/upgrade/svn/3421-tax_tables.sql
===================================================================
--- trunk/sql/upgrade/svn/3421-tax_tables.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3421-tax_tables.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,13 +0,0 @@
-CREATE TABLE tax_extended (
- account_id int references account(id),
- tx_id int references transactions(id),
- reference text not null,
- tax_basis numeric,
- rate numeric,
- tax_amount numeric,
- check (tax_amount = rate*tax_basis/100)
-);
-
-COMMENT ON TABLE tax_extended IS
-$$ This stores extended information for manual tax calculations.$$;
-
Deleted: trunk/sql/upgrade/svn/3422-tax_tables.sql
===================================================================
--- trunk/sql/upgrade/svn/3422-tax_tables.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3422-tax_tables.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,7 +0,0 @@
-ALTER TABLE tax_extended DROP COLUMN account_id;
-ALTER TABLE tax_extended DROP COLUMN tx_id;
-ALTER TABLE tax_extended DROP COLUMN reference;
-ALTER TABLE tax_extended DROP COLUMN tax_amount;
-ALTER TABLE tax_extended ADD entry_id int primary key;
-ALTER TABLE tax_extended
-ADD FOREIGN KEY(entry_id) references acc_trans(entry_id);
Deleted: trunk/sql/upgrade/svn/3441-periods-view.sql
===================================================================
--- trunk/sql/upgrade/svn/3441-periods-view.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3441-periods-view.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,12 +0,0 @@
-
-CREATE OR REPLACE VIEW periods AS
-SELECT 'ytd' as id, 'Year to Date' as label, now()::date as date_to,
- (extract('year' from now())::text || '-01-01')::date as date_from
-UNION
-SELECT 'last_year', 'Last Year',
- ((extract('YEAR' from now()) - 1)::text || '-12-31')::date as date_to,
- ((extract('YEAR' from now()) - 1)::text || '-01-01')::date as date_from
-;
-
-GRANT SELECT ON periods TO public;
-
Deleted: trunk/sql/upgrade/svn/3445-drop-save-user.sql
===================================================================
--- trunk/sql/upgrade/svn/3445-drop-save-user.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3445-drop-save-user.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,2 +0,0 @@
-DROP FUNCTION admin__save_user
-(in_id int,in_entity_id INT, in_username text, in_password TEXT);
Deleted: trunk/sql/upgrade/svn/3448-drop-location-search.sql
===================================================================
--- trunk/sql/upgrade/svn/3448-drop-location-search.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3448-drop-location-search.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,7 +0,0 @@
-DROP FUNCTION location_search
-(in_companyname varchar, in_address1 varchar, in_address2 varchar,
- in_city varchar, in_state varchar, in_zipcode varchar,
- in_country varchar);
-
-DROP TYPE location_result CASCADE;
-
Deleted: trunk/sql/upgrade/svn/3450-drop-user-function.sql
===================================================================
--- trunk/sql/upgrade/svn/3450-drop-user-function.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3450-drop-user-function.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1 +0,0 @@
-DROP FUNCTION admin__delete_user (in_username TEXT);
Deleted: trunk/sql/upgrade/svn/3455-asset-schema.sql
===================================================================
--- trunk/sql/upgrade/svn/3455-asset-schema.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3455-asset-schema.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,135 +0,0 @@
-
-CREATE TABLE asset_unit_class (
- id int not null unique,
- class text primary key
-);
-
-INSERT INTO asset_unit_class (id, class) values (1, 'time');
-INSERT INTO asset_unit_class (id, class) values (2, 'production');
--- production-based depreciation is unlikely to be supported initially
-
-CREATE TABLE asset_dep_method(
- id serial unique not null,
- method text primary key,
- sproc text not null unique,
- unit_label text not null,
- short_name text not null unique,
- unit_class int not null references asset_unit_class(id)
-);
-
-comment on column asset_dep_method.method IS
-$$ These are keyed to specific stored procedures. Currently only "straight_line" is supported$$;
-
-INSERT INTO asset_dep_method(method, unit_class, sproc, unit_label, short_name)
-values ('Annual Straight Line Daily', 1, 'asset_dep_straight_line_yr_d', 'in years', 'SLYD');
-
-
-INSERT INTO asset_dep_method(method, unit_class, sproc, unit_label, short_name)
-values ('Whole Month Straight Line', 1, 'asset_dep_straight_line_whl_m',
-'in months', 'SLMM');
-
-INSERT INTO asset_dep_method(method, unit_class, sproc, unit_label, short_name)
-values ('Annual Straight Line Daily', 1, 'asset_dep_straight_line_yr_m', 'in years', 'SLYM');
-
-CREATE TABLE asset_class (
- id serial not null unique,
- label text primary key,
- asset_account_id int references account(id),
- dep_account_id int references account(id),
- method int references asset_dep_method(id)
-);
-
-COMMENT ON TABLE asset_class IS $$
-The account fields here set the defaults for the individual asset items. They
-are non-authoritative.
-$$;
-
-CREATE TABLE asset_disposal_method (
- label text primary key,
- id serial unique,
- multiple int check (multiple in (1, 0, -1)),
- short_label char(1)
-);
-
-INSERT INTO asset_disposal_method (label, multiple, short_label)
-values ('Abandonment', '0', 'A');
-INSERT INTO asset_disposal_method (label, multiple, short_label)
-values ('Sale', '1', 'S');
-
-CREATE TABLE asset_rl_to_disposal_method (
- report_id int references asset_report(id),
- asset_id int references asset_item(id),
- disposal_method_id int references asset_disposal_method(id),
- percent_disposed numeric,
- primary key (report_id, asset_id, disposal_method_id)
-);
-
-CREATE TABLE asset_item (
- id serial primary key, -- needed due to possible null in natural key
- description text,
- tag text not null,
- purchase_value numeric,
- salvage_value numeric,
- usable_life numeric,
- purchase_date date not null,
- start_depreciation date not null,
- location_id int references warehouse(id),
- department_id int references department(id),
- invoice_id int references ap(id),
- asset_account_id int references account(id),
- dep_account_id int references account(id),
- exp_account_id int references account(id),
- obsolete_by int references asset_item(id),
- asset_class_id int references asset_class(id),
- unique (tag, obsolete_by) -- part 1 of natural key enforcement
-);
-
-CREATE UNIQUE INDEX asset_item_active_tag_u ON asset_item(tag)
- WHERE obsolete_by is null; -- part 2 of natural key enforcement
-
-COMMENT ON column asset_item.tag IS $$ This can be plugged into other routines to generate it automatically via ALTER TABLE .... SET DEFAULT.....$$;
-
-CREATE TABLE asset_note (
- foreign key (ref_key) references asset_item(id),
- check (note_class = 4)
-) inherits (note);
-
-INSERT INTO note_class (id, class) values (4, 'Asset');
-ALTER TABLE asset_note alter column note_class set default 4;
-
-CREATE TABLE asset_report_class (
- id int not null unique,
- class text primary key
-);
-
-INSERT INTO asset_report_class (id, class) values (1, 'depreciation');
-INSERT INTO asset_report_class (id, class) values (2, 'disposal');
-INSERT INTO asset_report_class (id, class) values (3, 'import');
-INSERT INTO asset_report_class (id, class) values (4, 'partial disposal');
-
-CREATE TABLE asset_report (
- id serial primary key,
- report_date date,
- gl_id bigint references gl(id) unique,
- asset_class bigint references asset_class(id),
- report_class int references asset_report_class(id),
- entered_by bigint not null references entity(id),
- approved_by bigint references entity(id),
- entered_at timestamp default now(),
- approved_at timestamp,
- depreciated_qty numeric,
- dont_approve bool default false,
- submitted bool not null default false
-);
-
-CREATE TABLE asset_report_line(
- asset_id bigint references asset_item(id),
- report_id bigint references asset_report(id),
- amount numeric,
- department_id int references department(id),
- warehouse_id int references warehouse(id),
- PRIMARY KEY(asset_id, report_id)
-);
-
-COMMENT ON COLUMN asset_report_line.department_id IS
-$$ In case assets are moved between departments, we have to store this here.$$;
Deleted: trunk/sql/upgrade/svn/3467-assets.sql
===================================================================
--- trunk/sql/upgrade/svn/3467-assets.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3467-assets.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,10 +0,0 @@
-BEGIN;
-INSERT INTO account_link_description (description, summary, custom)
-VALUES
-('Asset_Dep', FALSE, FALSE),
-('Fixed_Asset', FALSE, FALSE),
-('asset_expense', FALSE, FALSE),
-('asset_gain', FALSE, FALSE),
-('asset_loss', FALSE, FALSE);
-
-
Deleted: trunk/sql/upgrade/svn/3470-asset-menu.sql
===================================================================
--- trunk/sql/upgrade/svn/3470-asset-menu.sql 2012-06-11 02:12:22 UTC (rev 4886)
+++ trunk/sql/upgrade/svn/3470-asset-menu.sql 2012-06-11 02:47:14 UTC (rev 4887)
@@ -1,87 +0,0 @@
-BEGIN;
-
-CREATE TEMPORARY TABLE menu_track (token text, node_id int);
-
-INSERT INTO menu_track(node_id, token)
-values (menu_insert(0, 17, 'Fixed Assets'), 'asset_top');
-
-INSERT INTO menu_attribute (node_id, attribute, value)
-VALUES (currval('menu_node_id_seq')::int, 'menu', '1');
-
-INSERT INTO menu_track values ('asset_class', menu_insert((SELECT node_id from menu_track where token = 'asset_top'), 1, 'Asset Classes'));
-
-INSERT INTO menu_attribute (node_id, attribute, value)
-VALUES (currval('menu_node_id_seq')::int, 'menu', '1');
-
-INSERT INTO menu_track values ('asset_item', menu_insert((SELECT node_id from menu_track where token = 'asset_top'), 2, 'Assets'));
-
-INSERT INTO menu_attribute (node_id, attribute, value)
-VALUES (currval('menu_node_id_seq')::int, 'menu', '1');
-
-
-SELECT menu_insert((SELECT node_id from menu_track where token = 'asset_class'), 1, 'Add Class');
-INSERT INTO menu_attribute(node_id, attribute, value)
-VALUES (currval('menu_node_id_seq'), 'module', 'assets.pl');
-INSERT INTO menu_attribute(node_id, attribute, value)
-VALUES (currval('menu_node_id_seq'), 'action', 'asset_category_screen');
-
-SELECT menu_insert((SELECT node_id from menu_track where token = 'asset_class'), 2, 'List Classes');
-INSERT INTO menu_attribute(node_id, attribute, value)
-VALUES (currval('menu_node_id_seq'), 'module', 'assets.pl');
-INSERT INTO menu_attribute(node_id, attribute, value)
-VALUES (currval('menu_node_id_seq'), 'action', 'asset_category_search');
-
-
-SELECT menu_insert((SELECT node_id from menu_track where token = 'asset_item'), 1, 'Add Assets');
-INSERT INTO menu_attribute(node_id, attribute, value)
-VALUES (currval('menu_node_id_seq'), 'module', 'assets.pl');
-INSERT INTO menu_attribute(node_id, attribute, value)
-VALUES (currval('menu_node_id_seq'), 'action', 'asset_screen');
-
-SELECT menu_insert((SELECT node_id from menu_track where token = 'asset_items'), 2, 'Search Assets');
-INSERT INTO menu_attribute(node_id, attribute, value)
-VALUES (currval('menu_node_id_seq'), 'module', 'assets.pl');
-INSERT INTO menu_attribute(node_id, attribute, value)
-VALUES (currval('menu_node_id_seq'), 'action', 'asset_search');
-
-SELECT menu_insert(
- (SELECT id FROM menu_node WHERE parent = (select id from menu_node
- where position = 17
- and parent = 0)
- AND position=2), 3, 'Depreciate');
-
-INSERT INTO menu_attribute (node_id, attribute, value)
-values (currval('menu_node_id_seq'), 'module', 'asset.pl');
-INSERT INTO menu_attribute (node_id, attribute, value)
-values (currval('menu_node_id_seq'), 'action', 'new_report');
-INSERT INTO menu_attribute (node_id, attribute, value)
-values (currval('menu_node_id_seq'), 'depreciate', '1');
-
-SELECT menu_insert(
- (select id FROM menu_node
- where parent = (select id from menu_node
- where parent = 0 and position = 17
- and label = 'Fixed Assets')
- and position = 2),
- 2, 'Import');
-
-INSERT INTO menu_attribute (node_id, attribute, value)
-VALUES (currval('menu_node_id_seq')::int, 'module', 'asset.pl');
-INSERT INTO menu_attribute (node_id, attribute, value)
-VALUES (currval('menu_node_id_seq')::int, 'action', 'import');
-
-SELECT menu_insert(
- (SELECT id FROM menu_node WHERE parent = (select id from menu_node
- where position = 17
- and parent = 0)
- AND position=2), 10, 'Reports');
-
@@ Diff output truncated at 100000 characters. @@
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.