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)