110 lines
2.0 KiB
SQL
110 lines
2.0 KiB
SQL
|
|
CREATE TABLE materials (
|
|
id INTEGER NOT NULL,
|
|
code VARCHAR(80) NOT NULL,
|
|
spec VARCHAR(300),
|
|
unit VARCHAR(20),
|
|
price FLOAT,
|
|
active BOOLEAN,
|
|
PRIMARY KEY (id)
|
|
)
|
|
|
|
;
|
|
|
|
CREATE TABLE suppliers (
|
|
id INTEGER NOT NULL,
|
|
code VARCHAR(50),
|
|
name VARCHAR(200) NOT NULL,
|
|
contact VARCHAR(50),
|
|
phone VARCHAR(50),
|
|
tax_no VARCHAR(80),
|
|
bank_name VARCHAR(120),
|
|
bank_account VARCHAR(80),
|
|
payment_terms VARCHAR(120),
|
|
active BOOLEAN,
|
|
PRIMARY KEY (id)
|
|
)
|
|
|
|
;
|
|
|
|
CREATE TABLE users (
|
|
id INTEGER NOT NULL,
|
|
username VARCHAR(50) NOT NULL,
|
|
password_hash VARCHAR(200) NOT NULL,
|
|
name VARCHAR(50) NOT NULL,
|
|
role VARCHAR(20) NOT NULL,
|
|
active BOOLEAN,
|
|
created_at DATETIME,
|
|
PRIMARY KEY (id),
|
|
UNIQUE (username)
|
|
)
|
|
|
|
;
|
|
|
|
CREATE TABLE orders (
|
|
id INTEGER NOT NULL,
|
|
order_no VARCHAR(50) NOT NULL,
|
|
doc_type VARCHAR(20) NOT NULL,
|
|
supplier_name VARCHAR(200),
|
|
supplier_contact VARCHAR(50),
|
|
supplier_phone VARCHAR(50),
|
|
supplier_tax_no VARCHAR(80),
|
|
supplier_bank_name VARCHAR(120),
|
|
supplier_bank_account VARCHAR(80),
|
|
payment_terms VARCHAR(120),
|
|
our_company VARCHAR(200),
|
|
our_contact VARCHAR(50),
|
|
our_phone VARCHAR(50),
|
|
our_tax_no VARCHAR(80),
|
|
tax_rate VARCHAR(30),
|
|
order_date VARCHAR(20),
|
|
delivery_place VARCHAR(300),
|
|
note TEXT,
|
|
total_amount FLOAT,
|
|
status VARCHAR(20),
|
|
created_by INTEGER,
|
|
created_by_name VARCHAR(50),
|
|
created_at DATETIME,
|
|
submitted_at DATETIME,
|
|
reviewed_by VARCHAR(50),
|
|
reviewed_at DATETIME,
|
|
approved_by VARCHAR(50),
|
|
approved_at DATETIME,
|
|
reject_reason TEXT,
|
|
PRIMARY KEY (id),
|
|
FOREIGN KEY(created_by) REFERENCES users (id)
|
|
)
|
|
|
|
;
|
|
|
|
CREATE TABLE approval_logs (
|
|
id INTEGER NOT NULL,
|
|
order_id INTEGER,
|
|
action VARCHAR(50),
|
|
user_name VARCHAR(50),
|
|
comment TEXT,
|
|
created_at DATETIME,
|
|
PRIMARY KEY (id),
|
|
FOREIGN KEY(order_id) REFERENCES orders (id)
|
|
)
|
|
|
|
;
|
|
|
|
CREATE TABLE order_items (
|
|
id INTEGER NOT NULL,
|
|
order_id INTEGER,
|
|
seq INTEGER,
|
|
material_code VARCHAR(80),
|
|
material_spec VARCHAR(300),
|
|
unit VARCHAR(20),
|
|
qty FLOAT,
|
|
price FLOAT,
|
|
amount FLOAT,
|
|
delivery_date VARCHAR(30),
|
|
remark VARCHAR(200),
|
|
PRIMARY KEY (id),
|
|
FOREIGN KEY(order_id) REFERENCES orders (id)
|
|
)
|
|
|
|
;
|