-CREATE SEQUENCE id
- start 10000;
--
+CREATE SEQUENCE id start 10000;
SELECT nextval ('id');
--
+CREATE SEQUENCE invoiceid;
+SELECT nextval ('invoiceid');
+--
+CREATE SEQUENCE orderitemsid MAXVALUE 100000 CYCLE;
+SELECT nextval ('orderitemsid');
+--
CREATE TABLE makemodel (
parts_id int,
- name text
+ make text,
+ model text
);
--
CREATE TABLE gl (
description text,
transdate date DEFAULT current_date,
employee_id int,
- notes text
+ notes text,
+ department_id int default 0
);
--
CREATE TABLE chart (
expense_accno_id int,
fxgain_accno_id int,
fxloss_accno_id int,
- invnumber text,
+ sinumber text,
sonumber text,
yearend varchar(5),
weightunit varchar(5),
curr text,
closedto date,
revtrans bool DEFAULT 'f',
- ponumber text
+ ponumber text,
+ sqnumber text,
+ rfqnumber text,
+ audittrail bool default 'f',
+ vinumber text,
+ employeenumber text,
+ partnumber text,
+ customernumber text,
+ vendornumber text
);
-INSERT INTO defaults (version) VALUES ('2.0.8');
+INSERT INTO defaults (version) VALUES ('2.4.3');
--
CREATE TABLE acc_trans (
trans_id int,
source text,
cleared bool DEFAULT 'f',
fx_transaction bool DEFAULT 'f',
- project_id int
+ project_id int,
+ memo text
);
--
CREATE TABLE invoice (
- id int DEFAULT nextval ( 'id' ),
+ id int DEFAULT nextval ( 'invoiceid' ),
trans_id int,
parts_id int,
description text,
assemblyitem bool DEFAULT 'f',
unit varchar(5),
project_id int,
- deliverydate date
-);
---
-CREATE TABLE vendor (
- id int DEFAULT nextval ( 'id' ),
- name varchar(35),
- addr1 varchar(35),
- addr2 varchar(35),
- addr3 varchar(35),
- addr4 varchar(35),
- contact varchar(35),
- phone varchar(20),
- fax varchar(20),
- email text,
- notes text,
- terms int2 DEFAULT 0,
- taxincluded bool,
- vendornumber text,
- cc text,
- bcc text
+ deliverydate date,
+ serialnumber text
);
--
CREATE TABLE customer (
- id int DEFAULT nextval ( 'id' ),
- name varchar(35),
- addr1 varchar(35),
- addr2 varchar(35),
- addr3 varchar(35),
- addr4 varchar(35),
- contact varchar(35),
+ id int default nextval('id'),
+ name varchar(64),
+ address1 varchar(32),
+ address2 varchar(32),
+ city varchar(32),
+ state varchar(32),
+ zipcode varchar(10),
+ country varchar(32),
+ contact varchar(64),
phone varchar(20),
fax varchar(20),
email text,
notes text,
discount float4,
- taxincluded bool,
- creditlimit float DEFAULT 0,
- terms int2 DEFAULT 0,
- customernumber text,
+ taxincluded bool default 'f',
+ creditlimit float default 0,
+ terms int2 default 0,
+ customernumber varchar(32),
cc text,
- bcc text
+ bcc text,
+ business_id int,
+ taxnumber varchar(32),
+ sic_code varchar(6),
+ iban varchar(34),
+ bic varchar(11),
+ employee_id int,
+ language_code varchar(6),
+ pricegroup_id int,
+ curr char(3),
+ startdate date,
+ enddate date
);
--
+--
CREATE TABLE parts (
id int DEFAULT nextval ( 'id' ),
partnumber text,
id int,
parts_id int,
qty float,
- bom bool
+ bom bool,
+ adj bool
);
--
CREATE TABLE ar (
notes text,
curr char(3),
ordnumber text,
- employee_id int
+ employee_id int,
+ till varchar(20),
+ quonumber text,
+ intnotes text,
+ department_id int default 0,
+ shipvia text,
+ language_code varchar(6)
);
--
CREATE TABLE ap (
ordnumber text,
curr char(3),
notes text,
- employee_id int
+ employee_id int,
+ till varchar(20),
+ quonumber text,
+ intnotes text,
+ department_id int default 0,
+ shipvia text,
+ language_code varchar(6)
);
--
CREATE TABLE partstax (
notes text,
curr char(3),
employee_id int,
- closed bool default 'f'
+ closed bool default 'f',
+ quotation bool default 'f',
+ quonumber text,
+ intnotes text,
+ department_id int default 0,
+ shipvia text,
+ language_code varchar(6)
);
--
CREATE TABLE orderitems (
discount float4,
unit varchar(5),
project_id int,
- reqdate date
+ reqdate date,
+ ship float4,
+ serialnumber text,
+ id int default nextval('orderitemsid')
);
--
CREATE TABLE exchangerate (
sell float8
);
--
-CREATE TABLE employee (
- id int DEFAULT nextval ('id'),
+create table employee (
+ id int default nextval('id'),
login text,
- name Varchar(35),
- addr1 varchar(35),
- addr2 varchar(35),
- addr3 varchar(35),
- addr4 varchar(35),
+ name varchar(64),
+ address1 varchar(32),
+ address2 varchar(32),
+ city varchar(32),
+ state varchar(32),
+ zipcode varchar(10),
+ country varchar(32),
workphone varchar(20),
homephone varchar(20),
startdate date default current_date,
enddate date,
- notes text
+ notes text,
+ role varchar(20),
+ sales bool default 'f',
+ email text,
+ sin varchar(20),
+ iban varchar(34),
+ bic varchar(11),
+ managerid int,
+ employeenumber varchar(32)
);
--
create table shipto (
trans_id int,
- shiptoname varchar(35),
- shiptoaddr1 varchar(35),
- shiptoaddr2 varchar(35),
- shiptoaddr3 varchar(35),
- shiptoaddr4 varchar(35),
- shiptocontact varchar(35),
+ shiptoname varchar(64),
+ shiptoaddress1 varchar(32),
+ shiptoaddress2 varchar(32),
+ shiptocity varchar(32),
+ shiptostate varchar(32),
+ shiptozipcode varchar(10),
+ shiptocountry varchar(32),
+ shiptocontact varchar(64),
shiptophone varchar(20),
shiptofax varchar(20),
shiptoemail text
);
--
-create table project (
+CREATE TABLE vendor (
+ id int default nextval('id'),
+ name varchar(64),
+ address1 varchar(32),
+ address2 varchar(32),
+ city varchar(32),
+ state varchar(32),
+ zipcode varchar(10),
+ country varchar(32),
+ contact varchar(64),
+ phone varchar(20),
+ fax varchar(20),
+ email text,
+ notes text,
+ terms int2 default 0,
+ taxincluded bool default 'f',
+ vendornumber varchar(32),
+ cc text,
+ bcc text,
+ gifi_accno varchar(30),
+ business_id int,
+ taxnumber varchar(32),
+ sic_code varchar(6),
+ discount float4,
+ creditlimit float default 0,
+ iban varchar(34),
+ bic varchar(11),
+ employee_id int,
+ language_code varchar(6),
+ pricegroup_id int,
+ curr char(3),
+ startdate date,
+ enddate date
+);
+--
+CREATE TABLE project (
id int default nextval('id'),
projectnumber text,
description text
);
--
-create table partsgroup (
+CREATE TABLE partsgroup (
id int default nextval('id'),
partsgroup text
);
--
+CREATE TABLE status (
+ trans_id int,
+ formname text,
+ printed bool default 'f',
+ emailed bool default 'f',
+ spoolfile text,
+ chart_id int
+);
+--
+CREATE TABLE department (
+ id int default nextval('id'),
+ description text,
+ role char(1) default 'P'
+);
+--
+-- department transaction table
+CREATE TABLE dpt_trans (
+ trans_id int,
+ department_id int
+);
+--
+-- business table
+CREATE TABLE business (
+ id int default nextval('id'),
+ description text,
+ discount float4
+);
+--
+-- SIC
+CREATE TABLE sic (
+ code varchar(6),
+ sictype char(1),
+ description text
+);
+--
+CREATE TABLE warehouse (
+ id int default nextval('id'),
+ description text
+);
+--
+CREATE TABLE inventory (
+ warehouse_id int,
+ parts_id int,
+ oe_id int,
+ orderitems_id int,
+ qty float4,
+ shippingdate date,
+ employee_id int
+);
+--
+CREATE TABLE yearend (
+ trans_id int,
+ transdate date
+);
+--
+CREATE TABLE partsvendor (
+ vendor_id int,
+ parts_id int,
+ partnumber text,
+ leadtime int2,
+ lastcost float,
+ curr char(3)
+);
+--
+CREATE TABLE pricegroup (
+ id int default nextval('id'),
+ pricegroup text
+);
+--
+CREATE TABLE partscustomer (
+ parts_id int,
+ customer_id int,
+ pricegroup_id int,
+ pricebreak float4,
+ sellprice float,
+ validfrom date,
+ validto date,
+ curr char(3)
+);
+--
+CREATE TABLE language (
+ code varchar(6),
+ description text
+);
+--
+CREATE TABLE audittrail (
+ trans_id int,
+ tablename text,
+ reference text,
+ formname text,
+ action text,
+ transdate timestamp default current_timestamp,
+ employee_id int
+);
+--
+CREATE TABLE translation (
+ trans_id int,
+ language_code varchar(6),
+ description text
+);
+--
projects / freeside.git / blobdiff