-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathDAO.py
173 lines (125 loc) · 7.65 KB
/
DAO.py
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
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
from werkzeug import generate_password_hash, check_password_hash
from ConnectionHelper import ConnectionHelper
from math import cos, asin, sqrt
class DAO:
def __init__(self):
self.oi = 0
self.db = ConnectionHelper()
#GET USER
def getUserFromId(self, user_id):
username = self.db.run("select nome from usuario where usuario_id='"+user_id+"';")
return str(username[0][0])
def getLastNameFromId(self, user_id):
lastname = self.db.run("select sobrenome from usuario where usuario_id='"+user_id+"';")
return str(lastname[0][0])
def getEmailFromId(self, user_id):
email = self.db.run("select email from usuario where usuario_id='"+user_id+"';")
return str(email[0][0])
def getBirthDateFromId(self, user_id):
birth_date = self.db.run("select data_nasc from usuario where usuario_id='"+user_id+"';")
return str(birth_date[0][0])
def getGenderFromId(self, user_id):
gender = self.db.run("select sexo from usuario where usuario_id='"+user_id+"';")
return str(gender[0][0])
def getPasswordFromId(self, user_id):
password = self.db.run("select senha from usuario where usuario_id='"+user_id+"';")
return str(password[0][0])
#########################################################################################
#GET STORE
def getAddressFromId(self,store_id):
address = self.db.run("select endereco from loja where loja_id='"+store_id+"';")
return str(address[0][0])
def getPhoneFromId(self,store_id):
phone = self.db.run("select telefone from loja where loja_id='"+store_id+"';")
return str(phone[0][0])
def getStoreNameFromId(self, store_id):
storename = self.db.run("select nome from loja where loja_id='"+store_id+"';")
return str(storename[0][0])
def getStoreEmailFromId(self,store_id):
email = self.db.run("select email from loja where loja_id='"+store_id+"';")
return str(email[0][0])
def getStorePasswordFromId(self,store_id):
password = self.db.run("select senha from loja where loja_id='"+store_id+"';")
return str(password[0][0])
#########################################################################################
def validateUserPassword(self, email, password):
pass_check = self.db.run("select senha from usuario where email='"+email+"';")
if pass_check:
if check_password_hash(pass_check[0][0], password):
user_log = self.db.run("select usuario_id from usuario where email='"+email+"';")
return user_log[0][0]
return False
def validateStorePassword(self, email, password):
pass_check = self.db.run("select senha from loja where email='"+email+"';")
if pass_check:
if check_password_hash(pass_check[0][0], password):
store_log = self.db.run("select loja_id from loja where email='"+email+"';")
return store_log[0][0]
return False
def validateUserRegister(self, name, lastname, gender, email, password, birth_date):
hash_pass = generate_password_hash(password)
self.db.run("insert into usuario(nome,sobrenome,sexo,email,senha,data_nasc) VALUES('"+name+"','"+lastname+"','"+gender+"','"+email+"','"+hash_pass+"','"+birth_date+"');")
def validateStoreRegister(self, name, telephone, address, email, password, lat, lng):
hash_pass = generate_password_hash(password)
self.db.run("insert into loja(nome,telefone,endereco,email,senha,latitude,longitude) VALUES('"+name+"','"+telephone+"','"+address+"','"+email+"','"+hash_pass+"','"+lat+"','"+lng+"');")
def storeAddProduct(self, name, brand, price, stock, filePath, description, store_id):
self.db.run("insert into produto(nome,marca,preco, quantidade, path_foto, descricao, loja_loja_id) VALUES('"+name+"','"+brand+"','"+price+"','"+stock+"','"+filePath+"','"+description+"','"+str(store_id)+"');")
def searchProduct(self, name):
result = self.db.run("select p.nome, p.preco, path_foto, produto_id, latitude, longitude from produto p, loja l where p.loja_loja_id = l.loja_id and p.nome like '%"+name+"%' and p.quantidade > 0 and p.deletado IS NULL;")
return result
def searchProductByBrand(self, name, brand):
result = self.db.run("select p.nome, p.preco, path_foto, produto_id, latitude, longitude from produto p, loja l where p.loja_loja_id = l.loja_id and marca='"+brand+"' and p.nome like '%"+name+"%' and p.quantidade > 0;")
return result
def searchSoldOut(self, name):
result = self.db.run("select p.nome, p.preco, path_foto, produto_id, latitude, longitude from produto p, loja l where p.loja_loja_id = l.loja_id and p.nome like '%"+name+"%' and p.quantidade = 0 and p.deletado IS NULL;")
return result
def searchSoldOutByBrand(self, name, brand):
result = self.db.run("select p.nome, p.preco, path_foto, produto_id, latitude, longitude from produto p, loja l where p.loja_loja_id = l.loja_id and marca='"+brand+"' and p.nome like '%"+name+"%' and p.quantidade = 0 and p.deletado IS NULL;")
return result
def userBuyProduct(self, purchase_date, user_id, product_id, quantity):
self.db.run("insert into compra(data_compra, usuario_usuario_id, produto_produto_id, quantidade) VALUES('"+purchase_date+"','"+user_id+"','"+product_id+"','"+quantity+"');")
self.db.run("update produto set quantidade= quantidade - "+quantity+" where produto_id='"+product_id+"';")
def getProductById(self, product_id):
result = self.db.run("select * from produto p, loja l where p.loja_loja_id = l.loja_id and produto_id='"+product_id+"';")
return result
def getStoreProducts(self, store_id):
result = self.db.callproc('get_store_products',(store_id))
#result = self.db.run("call get_store_products ('"+store_id+"');")
return result
def isUserEmailAlreadyRegistered(self, email):
result = self.db.run("select email from usuario where email = '"+email+"';")
if result:
return True
else:
return False
def isStoreEmailAlreadyRegistered(self, email):
result = self.db.run("select email from loja where email = '"+email+"';")
if result:
return True
else:
return False
def editUser(self, user_id, name, lastname, email, birth_date, gender):
self.db.run("update usuario set nome='"+name+"', sobrenome='"+lastname+"',email='"+email+"', data_nasc='"+birth_date+"',sexo='"+gender+"' where usuario_id='"+user_id+"';")
def editProduct(self, product_id, name, brand, price, description, stock):
self.db.run("update produto set nome='"+name+"', marca='"+brand+"', preco='"+price+"', descricao='"+description+"',quantidade='"+stock+"' where produto_id='"+product_id+"';")
def editUserPassword(self, user_id, password):
hash_pass = generate_password_hash(password)
self.db.run("update usuario set senha='"+hash_pass+"' where usuario_id='"+user_id+"';")
def editStore(self, store_id, address, phone, storename, email):
self.db.run("update loja set endereco='"+address+"', telefone='"+phone+"',nome='"+storename+"',email='"+email+"' where loja_id='"+store_id+"';")
def editStorePassword(self, store_id, password):
hash_pass = generate_password_hash(password)
self.db.run("update loja set senha='"+hash_pass+"' where loja_id='"+store_id+"';")
def distance(self, lat1, lon1, lat2, lon2):
p = 0.017453292519943295
a = 0.5 - cos((lat2 - lat1) * p)/2 + cos(lat1 * p) * cos(lat2 * p) * (1 - cos((lon2 - lon1) * p)) / 2
distance = 12742 * asin(sqrt(a))
return distance #Km
def deleteProduct(self, product_id):
self.db.run("update produto set deletado = '"+"Y"+"' where produto_id = '"+product_id+"';")
def listBrand(self, name):
brand_list = self.db.run("select distinct marca from produto where nome like '%"+name+"%';")
return brand_list
def listCompras(self, storename):
result = self.db.run("select p.nome, c.quantidade, u.nome, c.data_compra from compra c,usuario u,loja l,produto p where c.usuario_usuario_id = u.usuario_id and c.produto_produto_id = p.produto_id and p.loja_loja_id = l.loja_id and l.nome='"+storename+"';")
return result