def reports():
cursor.execute("""SELECT SUM(((Quantity*Rate) - Discount + Tax + Shipping_Cost)) as Result from SALES_DETAIL""")
total = [dict(TOTAL=row[0]) for row in cursor.fetchall()]
if request.method == 'POST':
query = """SELECT SUM(((Quantity*Rate) - Discount + Tax + Shipping_Cost)) from SALES_DETAIL natural join SALES_HEADER where Order_Date >%s and Order_Date < %s"""
cursor.execute(query,(request.form['startdate'],request.form['enddate']))
boundtotal = [dict(TOTAL=row[0]) for row in cursor.fetchall()]
print request.form['enddate']
return render_template('reports.html',total = total,boundtotal = boundtotal)
return render_template('reports.html',total = total)#,boundtotal = boundtotal)
def set_role(self):
read = '''
SELECT role_id FROM `users` WHERE username = %s && password = %s
'''
val = (self.username, self.password)
cursor.execute(read, val)
for data in cursor.fetchall():
self.role = data[0]
def get_movies_data(self):
read = 'SELECT * FROM movies'
cursor.execute(read)
a = cursor.fetchall()
for data in a:
self.temporary_data.append(data)
table = PrettyTable(['id', 'title', 'genre', 'company'])
table.add_rows(a)
return table
def get_companies_data(self):
read = 'SELECT * FROM companies'
cursor.execute(read)
a = cursor.fetchall()
for data in a:
self.temporary_data.append(data)
table = PrettyTable(['id', 'company name', 'address'])
table.add_rows(a)
return table
def get_chairs_data(self):
read = 'SELECT * FROM chairs'
cursor.execute(read)
a = cursor.fetchall()
for data in a:
self.temporary_data.append(data)
table = PrettyTable(['id', 'number', 'status'])
table.add_rows(a)
return table
def get_roles_data(self):
read = 'SELECT * FROM user_roles'
cursor.execute(read)
a = cursor.fetchall()
for data in a:
self.temporary_data.append(data)
table = PrettyTable(['id', 'nama roles'])
table.add_rows(a)
return table
def get_topping_data(self):
read = 'SELECT * FROM toppings'
cursor.execute(read)
a = cursor.fetchall()
for data in a:
self.temporary_data.append(data)
table = PrettyTable(['id', 'nama topping', 'harga'])
table.add_rows(a)
return table
def get_studio_data(self):
read = 'SELECT * FROM studios'
cursor.execute(read)
a = cursor.fetchall()
for data in a:
self.temporary_data.append(data)
table = PrettyTable(['id', 'studio', 'description'])
table.add_rows(a)
return table
def get_schedules_data(self):
read = 'SELECT * FROM schedules'
cursor.execute(read)
a = cursor.fetchall()
for data in a:
self.temporary_data.append(data)
table = PrettyTable(['id', 'date', 'start', 'ends', 'movie', 'studio'])
table.add_rows(a)
return table
def checkReviews():
query = """SELECT * FROM FEEDBACK WHERE ISBN = %s"""
cursor.execute(query,session['ISBN'])
entries = [dict(
CUSTOMER=row[0],
ISBN=row[1],
RATING= row[2],
COMMENTS=row[3]
) for row in cursor.fetchall()]
return render_template('reviews.html',entries = entries)
def get_users_data(self):
read = 'SELECT * FROM users'
cursor.execute(read)
a = cursor.fetchall()
for data in a:
self.temporary_data.append(data)
table = PrettyTable([
'id', 'name', 'username', 'password', ' gender', ' contact',
'address', 'role id'
])
table.add_rows(a)
return table
def get_schedules_today(self):
read = 'SELECT schedules.id_schedule, movies.title, schedules.date_schedule, schedules.start, schedules.end, studios.studio_name FROM schedules INNER JOIN movies ON schedules.film_code = movies.code_film INNER JOIN studios ON schedules.id_studio = studios.id_studio'
cursor.execute(read)
fetch = cursor.fetchall()
for data in fetch:
self.temporary_data.append(data)
table = PrettyTable([
'ID Jadwal', 'Nama Film', 'Tanggal Film', 'Mulai', 'Selesai',
'Studio'
])
table.add_rows(fetch)
return table
def payment():
query = '''SELECT (((Quantity*Rate) - Discount + Tax + Shipping_Cost)) as Result,Discount,Tax,Shipping_Cost,Rate from SALES_DETAIL WHERE Order_ID = ((SELECT max(Order_ID) FROM SALES_DETAIL))'''
cursor.execute(query)
total = [dict(
TOTAL=row[0],
DISCOUNT=row[1],
TAX=row[2],
SHIPPING = row[3],
RATE = row[4]
) for row in cursor.fetchall()]
#print total
#total = [dict(TOTAL = row[0]) for row in cursor.fetchall()]
if request.method == 'POST':
cardno = request.form['cardno']
expdate = request.form['expDate']
query = '''INSERT INTO BILLING (Billing_Date,ORDER_ID) VALUES (now(),(SELECT max(Order_ID) FROM SALES_DETAIL))'''
cursor.execute(query)
query = '''UPDATE BILLING SET Card_Number=%s,Card_expDate = %s WHERE Order_ID in (SELECT max(Order_ID) FROM SALES_DETAIL)'''
cursor.execute(query,(cardno,expdate))
connection.con.commit()
return redirect(url_for('Searchbooks'))
return render_template('Payment.html',total =total)
def add_user():
ulang = True
#memasukan nama
name = input('masukan nama : ')
#memasukan email
username = input('masukan username : '******'masukan password : '******'masukan password sekali lagi : ')
if password == password_confirm:
ulang = False
ulang = True
#memasukan gender
while ulang == True:
gender = input('masukan gender (L/P) : ').upper()
if gender == 'L' or gender == 'P':
ulang = False
else:
print('masukan sesuai format')
#memasukan kontak
contact = input('masukan kontak : ')
#memasukan alamat
alamat = input('masukan alamat : ')
#masukan role
read = 'SELECT * FROM user_roles'
cursor.execute(read)
print('id\tnama roles')
for data in cursor.fetchall():
print('{}\t{}'.format(data[0], data[1]))
role_id = int(input('masukan role id : '))
sql = 'INSERT INTO users (id_user, name, username, password, gender, contact, address, role_id) VALUES (NULL, %s, %s, %s, %s, %s, %s, %s)'
val = (name, username, password, gender, contact, alamat, role_id)
sqlQuery = (sql, val)
return sqlQuery
def retorna_pessoas():
cursor.execute("SELECT * FROM pessoa")
return cursor.fetchall()
def Searchbooks():
form = searchBooksForm()
query = """SELECT ISBN,Book_Description,Author_Name,Cost_Price,Stock,Book_Name,Cover_Image FROM ITEM"""
cursor.execute(query)
entries = [dict(
ISBN=row[0],
BOOKDES=row[1],
AUTHORNAME= row[2],
COSTPRICE=row[3],
STOCK=row[4],
BOOKNAME=row[5],
IMAGE=row[6]
) for row in cursor.fetchall()]
for i in range(len(entries)):
entries[i]['IMAGE'] = base64.encodestring(entries[i]['IMAGE'])
# entries[0]['IMAGE'] = base64.encodestring(entries[0]['IMAGE'])
# entries[1]['IMAGE'] = base64.encodestring(entries[1]['IMAGE'])
# entries[2]['IMAGE'] = base64.encodestring(entries[2]['IMAGE'])
# entries[3]['IMAGE'] = base64.encodestring(entries[3]['IMAGE'])
# entries[4]['IMAGE'] = base64.encodestring(entries[4]['IMAGE'])
# entries[5]['IMAGE'] = base64.encodestring(entries[5]['IMAGE'])
# entries[6]['IMAGE'] = base64.encodestring(entries[6]['IMAGE'])
# entries[7]['IMAGE'] = base64.encodestring(entries[7]['IMAGE'])
# entries[8]['IMAGE'] = base64.encodestring(entries[8]['IMAGE'])
# entries[9]['IMAGE'] = base64.encodestring(entries[9]['IMAGE'])
query = """SELECT ISBN,Book_Description,Author_Name,Cost_Price,Stock,Book_Name,Cover_Image FROM ITEM where ISBN in
(select ISBN from ITEM_MASTER where Category in
(select category from ITEM_MASTER where ISBN in
(Select ISBN from SALES_DETAIL Natural JOIN SALES_HEADER Natural Join ITEM_MASTER WHERE Customer_ID=%s)))"""
cursor.execute(query,session['username'])
suggestedentries = [dict(
ISBN=row[0],
BOOKDES=row[1],
AUTHORNAME= row[2],
COSTPRICE=row[3],
STOCK=row[4],
BOOKNAME=row[5],
IMAGE=row[6]
) for row in cursor.fetchall()]
# print len(suggestedentries)
for j in range(len(suggestedentries)):
suggestedentries[j]['IMAGE'] = base64.encodestring(suggestedentries[j]['IMAGE'])
# suggestedentries[0]['IMAGE'] = base64.encodestring(entries[0]['IMAGE'])
# suggestedentries[1]['IMAGE'] = base64.encodestring(entries[0]['IMAGE'])
# suggestedentries[2]['IMAGE'] = base64.encodestring(entries[0]['IMAGE'])
# suggestedentries[3]['IMAGE'] = base64.encodestring(entries[0]['IMAGE'])
# suggestedentries[4]['IMAGE'] = base64.encodestring(entries[0]['IMAGE'])
# suggestedentries[5]['IMAGE'] = base64.encodestring(entries[0]['IMAGE'])
# suggestedentries[6]['IMAGE'] = base64.encodestring(entries[0]['IMAGE'])
if request.method == 'POST':
if request.form['submitB'] == 'search':
book = request.form['book']
query = """SELECT ISBN,Book_Description,Author_Name,Cost_Price,Stock,Book_Name from ITEM where Book_Name like %s"""
cursor.execute(query,"%"+book+"%")
#cursor.execute(query)
entries = [dict(
ISBN=row[0],
BOOKDES=row[1],
AUTHORNAME= row[2],
COSTPRICE=row[3],
STOCK=row[4],
BOOKNAME=row[5]
) for row in cursor.fetchall()]
return render_template('showBooks.html', entries=entries,suggestedentries = suggestedentries)
return render_template('showBooks.html', entries=entries,suggestedentries = suggestedentries)