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

SF.net SVN: ledger-smb:[5246] trunk/sql



Revision: 5246
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=5246&view=rev
Author:   einhverfr
Date:     2012-11-23 08:13:55 +0000 (Fri, 23 Nov 2012)
Log Message:
-----------
Upgrade test run worked.  Now for a more major test run

Modified Paths:
--------------
    trunk/sql/Pg-database.sql
    trunk/sql/upgrade/1.2-1.4.sql

Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql	2012-11-23 05:03:18 UTC (rev 5245)
+++ trunk/sql/Pg-database.sql	2012-11-23 08:13:55 UTC (rev 5246)
@@ -2389,6 +2389,15 @@
 $$ This provides centralized support for insertions into audittrail.
 $$;
 
+CREATE TRIGGER gl_audit_trail AFTER INSERT OR UPDATE OR DELETE ON gl
+FOR EACH ROW EXECUTE PROCEDURE gl_audit_trail_append();
+
+CREATE TRIGGER ar_audit_trail AFTER INSERT OR UPDATE OR DELETE ON ar
+FOR EACH ROW EXECUTE PROCEDURE gl_audit_trail_append();
+
+CREATE TRIGGER ap_audit_trail AFTER INSERT OR UPDATE OR DELETE ON ap
+FOR EACH ROW EXECUTE PROCEDURE gl_audit_trail_append();
+
 CREATE TRIGGER je_audit_trail AFTER insert or update or delete ON journal_entry
 FOR EACH ROW EXECUTE PROCEDURE gl_audit_trail_append();
 

Modified: trunk/sql/upgrade/1.2-1.4.sql
===================================================================
--- trunk/sql/upgrade/1.2-1.4.sql	2012-11-23 05:03:18 UTC (rev 5245)
+++ trunk/sql/upgrade/1.2-1.4.sql	2012-11-23 08:13:55 UTC (rev 5246)
@@ -19,7 +19,7 @@
 
 -- Buisness Reporting Units
 
-INSERT INTO business_unit (class_id, id, control_code, description
+INSERT INTO business_unit (class_id, id, control_code, description)
      SELECT 1, id, role || id::text, description FROM lsmb12.department;
 
 
@@ -28,9 +28,9 @@
 SELECT id, accno, description
   FROM lsmb12.chart WHERE charttype = 'H';
 
-SELECT account_save(id, accno, description, category, gifi_accno, NULL, contra, 
+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,':'))
+                    string_to_array(link,':'), false, false)
   FROM lsmb12.chart 
  WHERE charttype = 'A';
 --Entity
@@ -396,7 +396,68 @@
  WHERE setting_key IN (select setting_key FROM lsmb12.defaults);
 
 
-INSERT INTO parts SELECT * FROM lsmb12.parts;
+INSERT INTO parts (  
+  id,
+  partnumber,
+  description,
+  unit,
+  listprice,
+  sellprice,
+  lastcost,
+  priceupdate,
+  weight,
+  onhand,
+  notes,
+  makemodel,
+  assembly,
+  alternate,
+  rop,
+  inventory_accno_id,
+  income_accno_id ,
+  expense_accno_id,
+  bin,
+  obsolete,
+  bom,
+  image,
+  drawing,
+  microfiche,
+  partsgroup_id,
+  avgcost
+)
+SELECT 
+  p.id,
+  partnumber,
+  p.description,
+  unit,
+  listprice,
+  sellprice,
+  lastcost,
+  priceupdate,
+  weight,
+  onhand,
+  notes,
+  makemodel,
+  assembly,
+  alternate,
+  rop,
+  inventory_accno.id,
+  income_accno.id ,
+  expense_accno.id,
+  bin,
+  p.obsolete,
+  bom,
+  image,
+  drawing,
+  microfiche,
+  partsgroup_id,
+  avgcost
+ FROM lsmb12.parts p
+ LEFT JOIN lsmb12.chart invc ON p.inventory_accno_id = invc.id
+ LEFT JOIN lsmb12.chart incc ON p.income_accno_id = incc.id
+ LEFT JOIN lsmb12.chart expc ON p.expense_accno_id = expc.id
+ LEFT JOIN account inventory_accno ON invc.accno = inventory_accno.accno
+ LEFT JOIN account income_accno ON incc.accno = income_accno.accno
+ LEFT JOIN account expense_accno ON expc.accno = expense_accno.accno;
 
 INSERT INTO assembly SELECT * FROM lsmb12.assembly;
 
@@ -444,6 +505,13 @@
 
 ALTER TABLE ap ENABLE TRIGGER ap_audit_trail;
 
+INSERT INTO business_unit (id,control_code, description, start_date, end_date,
+            credit_id, class_id)
+     SELECT p.id + 1000, projectnumber, description, p.startdate, p.enddate,
+            c.credit_id, 2
+       FROM lsmb12.project p
+  LEFT JOIN lsmb12.customer c ON p.customer_id = c.id;
+
 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,
@@ -452,17 +520,19 @@
        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) 
+INSERT INTO business_unit_ac (entry_id, class_id, bu_id) 
      SELECT ac.entry_id, 1, gl.department_id
        FROM acc_trans ac
-       JOIN (select id, department_id from gl
+       JOIN (select id, department_id from lsmb12.gl
               UNION
-             SELECT id, department_id FROM ar
+             SELECT id, department_id FROM lsmb12.ar
               UNION
-             SELECT id, department_id FROM ap) gl ON ac.trans_id = gl.id
+             SELECT id, department_id FROM lsmb12.ap) gl ON ac.trans_id = gl.id
+      WHERE gl.department_id is not null and gl.department_id <> 0
       UNION
      SELECT ac.entry_id, 2, ac.project_id + 1000
-       FROM lsmb12.acc_trans ac;
+       FROM lsmb12.acc_trans ac
+      WHERE ac.project_id IS NOT NULL;
 
 INSERT INTO invoice (id, trans_id, parts_id, description, qty, allocated,
             sellprice, fxsellprice, discount, assemblyitem, unit,
@@ -478,17 +548,19 @@
        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) 
+INSERT INTO business_unit_inv (entry_id, class_id, bu_id) 
      SELECT inv.id, 1, gl.department_id
        FROM invoice inv
-       JOIN (select id, department_id from gl
+       JOIN (select id, department_id from lsmb12.gl
               UNION
-             SELECT id, department_id FROM ar
+             SELECT id, department_id FROM lsmb12.ar
               UNION
-             SELECT id, department_id FROM ap) gl ON ac.trans_id = gl.id
+             SELECT id, department_id FROM lsmb12.ap) gl ON inv.trans_id = gl.id
+      WHERE gl.department_id is not null and gl.department_id <> 0
       UNION
-     SELECT inv.id, 2, ac.project_id + 1000
-       FROM lsmb12.invoice inv;
+     SELECT inv.id, 2, inv.project_id + 1000
+       FROM lsmb12.invoice inv
+      WHERE inv.project_id IS NOT NULL;
 
 INSERT INTO tax(chart_id, rate, taxnumber, validto, pass, taxmodule_id)
      SELECT a.id, t.rate, t.taxnumber, 
@@ -497,14 +569,13 @@
        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)
+INSERT INTO eca_tax (eca_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)
+       JOIN account a ON chart.accno = a.accno
+      UNION
      SELECT c.credit_id,  a.id
        FROM lsmb12.vendortax pt       
        JOIN lsmb12.vendor c ON (pt.vendor_id = c.id)
@@ -514,11 +585,11 @@
 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,
+       intnotes, 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,
+       intnotes, 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
@@ -533,20 +604,23 @@
   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)
+            discount, unit, reqdate, ship, serialnumber, notes)
      SELECT id, trans_id, parts_id, description, qty, sellprice,
-            discount, unit, project_id, reqdate, ship, serialnumber, notes
+            discount, unit, reqdate, ship, serialnumber, notes
        FROM lsmb12.orderitems;
 
+INSERT INTO business_unit_oitem (entry_id, class_id, bu_id) 
+     SELECT oi.id, 1, gl.department_id
+       FROM orderitems oi
+       JOIN lsmb12.oe gl ON oi.trans_id = gl.id
+      WHERE gl.department_id is not null and gl.department_id <> 0
+      UNION
+     SELECT oi.id, 2, oi.project_id + 1000
+       FROM lsmb12.orderitems oi
+      WHERE oi.project_id IS NOT NULL;
+
 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;
@@ -601,10 +675,10 @@
 
 INSERT INTO jcitems(id, business_unit_id, parts_id, description, qty, allocated,
             sellprice, fxsellprice, serialnumber, checkedin, checkedout,
-            person_id, notes)
+            person_id, notes, total)
      SELECT j.id,  project_id + 1000, parts_id, description, qty, allocated,
             sellprice, fxsellprice, serialnumber, checkedin, checkedout,
-            p.id, j.notes
+            p.id, j.notes, qty
        FROM lsmb12.jcitems j
        JOIN lsmb12.employee e ON j.employee_id = e.id
        JOIN person p ON e.entity_id = p.entity_id;
@@ -618,8 +692,9 @@
 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);
+INSERT INTO business_unit_translation (trans_id, description, language_code)
+SELECT trans_id + 1000, description, language_code 
+FROM lsmb12.translation where trans_id in (select id from lsmb12.project);
 
 SELECT setval('id', max(id)) FROM transactions;
 
@@ -665,8 +740,6 @@
  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;
@@ -674,13 +747,12 @@
  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';
+UPDATE defaults SET value = '1.3.999' WHERE setting_key = 'version';
 
 
 COMMIT;

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