-
Notifications
You must be signed in to change notification settings - Fork 0
/
VRDB.py
146 lines (128 loc) · 3.82 KB
/
VRDB.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
from flask import g
#TODO error handling
#TODO objectify?
#utility functions
def query_db(query, args=(), one=False):
cur = g.db.execute(query, args)
rv = [dict((cur.description[idx][0], value)
for idx, value in enumerate(row)) for row in cur.fetchall()]
return (rv[0] if rv else None) if one else rv
#customer functions
def customers():
return query_db(
"""
SELECT id, first_name, last_name, email, lead, follow, signup_date,
last_seen_date, note FROM customers
""")
def customer(customer_id):
return query_db(
"""
SELECT first_name, last_name, email, lead, follow, signup_date,
last_seen_date, note FROM customers WHERE id = ?
""", [customer_id], True)
# returns the new customer id
def customer_add(first_name, last_name, email, lead, follow, signup_date, note):
c = g.db.cursor()
c.execute(
"""
INSERT INTO customers
(first_name, last_name,
email, lead, follow,
signup_date, last_seen_date,
note)
VALUES
(?, ?,
?, ?, ?,
?, ?,
?)
""",
[first_name, last_name, email, lead, follow, signup_date, signup_date,
note])
g.db.commit()
return c.lastrowid
def customer_update(customer_id, first_name, last_name, email, lead, follow, signup_date, note):
c = g.db.custor()
c.execute(
"""
UPDATE customers
SET first_name = ?, last_name = ?
email = ?, lead = ?, follow = ?,
signup_date, note
""",
[first_name, last_name, email, lead, follow, signup_adte, note])
g.db.commit()
def customer_delete(customer_id):
c = g.db.cursor()
c.execute('DELETE FROM customers WHERE customer_id = ?', [customer_id])
g.db.commit()
#event functions
def events():
return query_db(
"""
SELECT id, name, date, price, description
FROM events
ORDER BY date DESC
""")
def event(event_id):
return query_db(
'SELECT id, name, date, price, description FROM events WHERE id = ?',
[event_id], True)
#returns new event id
def event_add(name, date, description):
c = g.db.cursor()
c.execute(
"""
INSERT INTO events (name, date, description)
VALUES (?, ?, ?)
""",
[name, date, description])
g.db.commit()
return c.lastrowid
def event_update(event_id, name, date, description):
c = g.db. cursor()
c.execute(
"""
UPDATE events
SET name = ?, date = ?, description = ?
WHERE event_id = ?
""",
[name, date, description, event_id])
g.db.commit()
def event_delete(event_id):
c = g.db.cursor()
c.execute('DELETE FROM events where event_id = ?', [event_id])
g.db.commit()
def event_attendence(event_id):
return query_db(
'SELECT customer_id, amount, coupon_id FROM event_attendence WHERE event_id = ?',
[event_id])
def event_attendence_add(event_id, customer_id, amount, entry_time = None, coupon_id = None, sku_id = None):
if entry_time is None:
entry_time = time.time()
c = g.db.cursor()
c.execute(
"""
INSERT INTO event_attendence
(event_id, customer_id, entry_time, amount, coupon_id, sku)
VALUES (?, ?, ?, ?, ?, ?)
""",
[event, customer_id, entry_time, amount, coupon_id, sku_id])
g.db.commit()
def event_attendence_update(event_id, customer_id, amount, entry_time, coupon_id, sku_id):
c = g.db.cursor()
c.execute(
"""
UPDATE event_attendence
SET entry_time = ?, amount = ?, coupon_id = ?, sku = ?
WHERE event_id = ? AND customer_id = ?
""",
[entry_time, amount, coupon_id, sku, event_id, customer_id])
g.db.commit()
def event_attendence_delete(event_id, customer_id):
c = g.db.cursor()
c.execute(
"""
DELETE FROM event_attendence WHERE event_id = ? and customer_id = ?
""",
[event_id, customer_id])
g.db.execute()