forked from evilnapsis/inventio-lite
-
Notifications
You must be signed in to change notification settings - Fork 0
/
schema.sql
133 lines (117 loc) · 3.95 KB
/
schema.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
create database inventiolite;
use inventiolite;
create table user(
id int not null auto_increment primary key,
name varchar(50) not null,
lastname varchar(50) not null,
username varchar(50),
email varchar(255) not null,
password varchar(60) not null,
image varchar(255),
is_active boolean not null default 1,
is_admin boolean not null default 0,
created_at datetime not null
);
insert into user(name,lastname,email,password,is_active,is_admin,created_at) value ("Administrador", "","admin","90b9aa7e25f80cf4f64e990b78a9fc5ebd6cecad",1,1,NOW());
create table category(
id int not null auto_increment primary key,
image varchar(255),
name varchar(50) not null,
description text not null,
created_at datetime not null
);
create table product(
id int not null auto_increment primary key,
image varchar(255),
barcode varchar(50) not null,
name varchar(50) not null,
description text not null,
inventary_min int not null default 10,
price_in float not null,
price_out float,
unit varchar(255) not null,
presentation varchar(255) not null,
user_id int not null,
category_id int,
created_at datetime not null,
is_active boolean not null default 1,
foreign key (category_id) references category(id),
foreign key (user_id) references user(id)
);
/*
person kind
1.- Client
2.- Provider
*/
create table person(
id int not null auto_increment primary key,
image varchar(255) not null,
name varchar(255) not null,
lastname varchar(50) not null,
company varchar(50) not null,
address1 varchar(50) not null,
address2 varchar(50) not null,
phone1 varchar(50) not null,
phone2 varchar(50) not null,
email1 varchar(50) not null,
email2 varchar(50) not null,
kind int,
created_at datetime not null
);
create table operation_type(
id int not null auto_increment primary key,
name varchar(50) not null
);
insert into operation_type (name) value ("entrada");
insert into operation_type (name) value ("salida");
create table box(
id int not null auto_increment primary key,
created_at datetime not null
);
create table sell(
id int not null auto_increment primary key,
person_id int ,
user_id int ,
operation_type_id int default 2,
box_id int,
total double,
cash double,
discount double,
foreign key (box_id) references box(id),
foreign key (operation_type_id) references operation_type(id),
foreign key (user_id) references user(id),
foreign key (person_id) references person(id),
created_at datetime not null
);
create table operation(
id int not null auto_increment primary key,
product_id int not null,
q float not null,
operation_type_id int not null,
sell_id int,
created_at datetime not null,
foreign key (product_id) references product(id),
foreign key (operation_type_id) references operation_type(id),
foreign key (sell_id) references sell(id)
);
/*
configuration kind
1.- Boolean
2.- Text
3.- Number
*/
create table configuration(
id int not null auto_increment primary key,
short varchar(255) not null unique,
name varchar(255) not null unique,
kind int not null,
val varchar(255) not null
);
insert into configuration(short,name,kind,val) value("title","Titulo del Sistema",2,"Inventio Lite");
insert into configuration(short,name,kind,val) value("use_image_product","Utilizar Imagenes en los productos",1,0);
insert into configuration(short,name,kind,val) value("active_clients","Activar clientes",1,0);
insert into configuration(short,name,kind,val) value("active_providers","Activar proveedores",1,0);
insert into configuration(short,name,kind,val) value("active_categories","Activar categorias",1,0);
insert into configuration(short,name,kind,val) value("active_reports_word","Activar reportes en Word",1,0);
insert into configuration(short,name,kind,val) value("active_reports_excel","Activar reportes en Excel",1,0);
insert into configuration(short,name,kind,val) value("active_reports_pdf","Activar reportes en PDF",1,0);