Rather large amount of changes to Pg-tables.sql and the 2.6.17-2.6.18 upgrade script. Changed all floats to numeric. If the float was a percent, it is just numeric. If the float was a cost, it is (10,2)

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@62 4979c152-3d1c-0410-bac9-87ea11338e46

2 files changed, 106 insertions, 44 deletions

diff --git a/sql/Pg-tables.sql b/sql/Pg-tables.sql
index 3f9b2a24..c826d357 100755
--- a/sql/Pg-tables.sql
+++ b/sql/Pg-tables.sql
@@ -75,7 +75,7 @@ INSERT INTO defaults (version) VALUES ('2.6.17');
 CREATE TABLE acc_trans (
   trans_id int,
   chart_id int NOT NULL,
-  amount float,
+  amount numeric(10,2),
   transdate date DEFAULT current_date,
   source text,
   cleared bool DEFAULT 'f',
@@ -90,11 +90,11 @@ CREATE TABLE invoice (
   trans_id int,
   parts_id int,
   description text,
-  qty float4,
-  allocated float4,
-  sellprice float,
-  fxsellprice float,
-  discount float4,
+  qty integer,
+  allocated integer,
+  sellprice numeric(10,2),
+  fxsellprice numeric(10,2),
+  discount float4, -- jd: check into this
   assemblyitem bool DEFAULT 'f',
   unit varchar(5),
   project_id int,
@@ -117,9 +117,9 @@ CREATE TABLE customer (
   fax varchar(20),
   email text,
   notes text,
-  discount float4,
+  discount numeric, 
   taxincluded bool default 'f',
-  creditlimit float default 0,
+  creditlimit numeric(10,2) default 0,
   terms int2 default 0,
   customernumber varchar(32),
   cc text,
@@ -143,17 +143,17 @@ CREATE TABLE parts (
   partnumber text,
   description text,
   unit varchar(5),
-  listprice float,
-  sellprice float,
-  lastcost float,
+  listprice numeric(10,2),
+  sellprice numeric(10,2),
+  lastcost numeric(10,2),
   priceupdate date DEFAULT current_date,
-  weight float4,
-  onhand float4 DEFAULT 0,
+  weight numeric,
+  onhand numeric DEFAULT 0,
   notes text,
   makemodel bool DEFAULT 'f',
   assembly bool DEFAULT 'f',
   alternate bool DEFAULT 'f',
-  rop float4,
+  rop float4, -- jd: what is this
   inventory_accno_id int,
   income_accno_id int,
   expense_accno_id int,
@@ -165,13 +165,13 @@ CREATE TABLE parts (
   microfiche text,
   partsgroup_id int,
   project_id int,
-  avgcost float
+  avgcost numeric(10,2)
 );
 --
 CREATE TABLE assembly (
   id int,
   parts_id int,
-  qty float,
+  qty numeric,
   bom bool,
   adj bool
 ) WITH OIDS;
@@ -182,9 +182,9 @@ CREATE TABLE ar (
   transdate date DEFAULT current_date,
   customer_id int,
   taxincluded bool,
-  amount float,
-  netamount float,
-  paid float,
+  amount numeric(10,2),
+  netamount numeric(10,2),
+  paid numeric(10,2),
   datepaid date,
   duedate date,
   invoice bool DEFAULT 'f',
@@ -209,9 +209,9 @@ CREATE TABLE ap (
   transdate date DEFAULT current_date,
   vendor_id int,
   taxincluded bool DEFAULT 'f',
-  amount float,
-  netamount float,
-  paid float,
+  amount numeric(10,2),
+  netamount numeric(10,2),
+  paid numeric(10,2),
   datepaid date,
   duedate date,
   invoice bool DEFAULT 'f',
@@ -237,7 +237,7 @@ CREATE TABLE partstax (
 --
 CREATE TABLE tax (
   chart_id int,
-  rate float,
+  rate numeric,
   taxnumber text,
   validto date
 );
@@ -258,8 +258,8 @@ CREATE TABLE oe (
   transdate date default current_date,
   vendor_id int,
   customer_id int,
-  amount float8,
-  netamount float8,
+  amount numeric(10,2),
+  netamount numeric(10,2),
   reqdate date,
   taxincluded bool,
   shippingpoint text,
@@ -282,13 +282,13 @@ CREATE TABLE orderitems (
   trans_id int,
   parts_id int,
   description text,
-  qty float4,
-  sellprice float8,
-  discount float4,
+  qty numeric,
+  sellprice numeric(10,2),
+  discount numeric,
   unit varchar(5),
   project_id int,
   reqdate date,
-  ship float4,
+  ship numeric,
   serialnumber text,
   notes text
 ) WITH OIDS;
@@ -296,8 +296,8 @@ CREATE TABLE orderitems (
 CREATE TABLE exchangerate (
   curr char(3),
   transdate date,
-  buy float8,
-  sell float8
+  buy numeric,
+  sell numeric
 );
 --
 create table employee (
@@ -364,8 +364,8 @@ CREATE TABLE vendor (
   business_id int,
   taxnumber varchar(32),
   sic_code varchar(6),
-  discount float4,
-  creditlimit float default 0,
+  discount numeric,
+  creditlimit numeric default 0,
   iban varchar(34),
   bic varchar(11),
   employee_id int,
@@ -383,8 +383,8 @@ CREATE TABLE project (
   startdate date,
   enddate date,
   parts_id int,
-  production float default 0,
-  completed float default 0,
+  production numeric default 0,
+  completed numeric default 0,
   customer_id int
 );
 --
@@ -417,7 +417,7 @@ CREATE TABLE dpt_trans (
 CREATE TABLE business (
   id int default nextval('id'),
   description text,
-  discount float4
+  discount numeric
 );
 --
 -- SIC
@@ -437,7 +437,7 @@ CREATE TABLE inventory (
   parts_id int,
   trans_id int,
   orderitems_id int,
-  qty float4,
+  qty numeric,
   shippingdate date,
   employee_id int
 ) WITH OIDS;
@@ -452,7 +452,7 @@ CREATE TABLE partsvendor (
   parts_id int,
   partnumber text,
   leadtime int2,
-  lastcost float,
+  lastcost numeric(10,2),
   curr char(3)
 );
 --
@@ -465,8 +465,8 @@ CREATE TABLE partscustomer (
   parts_id int,
   customer_id int,
   pricegroup_id int,
-  pricebreak float4,
-  sellprice float,
+  pricebreak numeric,
+  sellprice numeric(10,2),
   validfrom date,
   validto date,
   curr char(3)
@@ -524,10 +524,10 @@ CREATE TABLE jcitems (
   project_id int,
   parts_id int,
   description text,
-  qty float4,
-  allocated float4,
-  sellprice float8,
-  fxsellprice float8,
+  qty numeric,
+  allocated numeric,
+  sellprice numeric(10,2),
+  fxsellprice numeric(10,2),
   serialnumber text,
   checkedin timestamp with time zone,
   checkedout timestamp with time zone,
diff --git a/sql/Pg-upgrade-2.6.17-2.6.18.sql b/sql/Pg-upgrade-2.6.17-2.6.18.sql
index 4cf7c9e0..a0ad36d5 100644
--- a/sql/Pg-upgrade-2.6.17-2.6.18.sql
+++ b/sql/Pg-upgrade-2.6.17-2.6.18.sql
@@ -11,6 +11,68 @@ ALTER TABLE acc_trans ALTER COLUMN entry_id SET DEFAULT nextval('acctrans_entry_
 UPDATE acc_trans SET entry_id = nextval('acctrans_entry_id_seq');
 ALTER TABLE acc_trans ADD PRIMARY key (entry_id);
 
+-- Start changing floats
+ALTER TABLE acc_trans ALTER COLUMN amount TYPE numeric(10,2);
+
+-- This may break someone if they for some reason have an actual float type in the qty column
+ALTER TABLE invoice ALTER COLUMN qty TYPE numeric;
+
+ALTER TABLE invoice ALTER COLUMN allocated TYPE numeric;
+ALTER TABLE invoice ALTER COLUMN sellprice TYPE numeric(10,2);
+ALTER TABLE invoice ALTER COLUMN fxsellprice TYPE numeric(10,2);
+
+ALTER TABLE customer ALTER COLUMN discount TYPE numeric;
+ALTER TABLE customer ALTER COLUMN creditlimit TYPE numeric(10,2);
+
+ALTER TABLE parts ALTER COLUMN listprice TYPE numeric(10,2);
+ALTER TABLE parts ALTER COLUMN sellprice TYPE numeric(10,2);
+ALTER TABLE parts ALTER COLUMN lastcost TYPE numeric(10,2);
+ALTER TABLE parts ALTER COLUMN weight TYPE numeric;
+ALTER TABLE parts ALTER COLUMN onhand TYPE numeric;
+ALTER TABLE parts ALTER COLUMN avgcost TYPE numeric(10,2);
+
+ALTER TABLE assembly ALTER COLUMN qty TYPE numeric;
+
+ALTER TABLE ar ALTER COLUMN amount TYPE numeric(10,2);
+ALTER TABLE ar ALTER COLUMN netamount TYPE numeric(10,2);
+ALTER TABLE ar ALTER COLUMN paid TYPE numeric(10,2);
+
+ALTER TABLE ap ALTER COLUMN amount TYPE numeric(10,2);
+ALTER TABLE ap ALTER COLUMN netamount TYPE numeric(10,2);
+ALTER TABLE ap ALTER COLUMN paid TYPE numeric(10,2);
+
+ALTER TABLE tax ALTER COLUMN rate TYPE numeric;
+
+ALTER TABLE oe ALTER COLUMN amount TYPE numeric(10,2);
+ALTER TABLE oe ALTER COLUMN netamount TYPE numeric(10,2);
+
+ALTER TABLE orderitems ALTER COLUMN qty TYPE numeric;
+ALTER TABLE orderitems ALTER COLUMN sellprice TYPE numeric(10,2);
+ALTER TABLE orderitems ALTER COLUMN discount TYPE numeric;
+ALTER TABLE orderitems ALTER COLUMN ship TYPE numeric;
+
+ALTER TABLE exchangerate ALTER COLUMN buy TYPE numeric;
+ALTER TABLE exchangerate ALTER COLUMN sell TYPE numeric;
+
+ALTER TABLE vendor ALTER COLUMN discount TYPE numeric;
+ALTER TABLE vendor ALTER COLUMN creditlimit TYPE numeric; 
+
+ALTER TABLE project ALTER COLUMN production TYPE numeric; 
+ALTER TABLE project ALTER COLUMN completed TYPE numeric;
+
+ALTER TABLE business ALTER COLUMN discount TYPE numeric;
+
+ALTER TABLE inventory ALTER COLUMN qty TYPE numeric;
+
+ALTER TABLE partsvendor ALTER COLUMN lastcost TYPE numeric(10,2);
+
+ALTER TABLE partscustomer ALTER COLUMN pricebreak TYPE numeric;
+ALTER TABLE partscustomer ALTER COLUMN sellprice TYPE numeric(10,2);
+
+ALTER TABLE jcitems ALTER COLUMN qty TYPE numeric;
+ALTER TABLE jcitems ALTER COLUMN allocated TYPE numeric;
+ALTER TABLE jcitems ALTER COLUMN sellprice TYPE numeric(10,2);
+ALTER TABLE jcitems ALTER COLUMN fxsellprice TYPE numeric(10,2);
 
 -- The query rewrite rule necessary to notify the email app that a new report
 -- needs to be sent to the designated administrator.