def get_byid(self, author_id):
try:
author = None
# Setup connection to the DB
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
args = [author_id]
# Calls the stored procedure
cursor.callproc('getAuthorByAuthorID', args)
# This loop iterates through the resultsets
for result in cursor.stored_results():
# This loop iterates through the rows in each resultset
for author_row in result.fetchall():
author = Author()
author.author_id = author_row[0]
author.first_name = author_row[1]
author.last_name = author_row[2]
# Close the connection to the DB
cursor.close()
conn.close()
except Error as error:
print(error)
except Exception as e:
print(e)
return author
def get_byusername(self, username):
u = None
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
args = [username]
cursor.callproc('getUserByUserName', args)
for result in cursor.stored_results():
user = result.fetchall()
for x in user:
u = User()
u.id = x[0]
u.password = x[1]
u.last_login = x[2]
u.is_superuser = x[3]
u.username = x[4]
u.first_name = x[5]
u.last_name = x[6]
u.email = x[7]
u.is_staff = x[8]
u.is_active = x[9]
u.date_joined = x[10]
conn.commit()
cursor.close()
conn.close()
except Error as error:
print(error)
except Exception as e:
print(e)
return u
def get_by_customer_id(self, customer_id):
all_payments = []
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
args = [customer_id]
cursor.callproc('getAllPaymentInfoByCustomerID', args)
for result in cursor.stored_results():
payments = result.fetchall()
for x in payments:
currentpayment = PaymentInfo()
currentpayment.card_id = x[0]
currentpayment.last_four = x[1]
currentpayment.expir_date = x[2]
currentpayment.card_issuer = x[3]
currentpayment.customer_id = x[4]
currentpayment.billing_address.address_id = x[5]
currentpayment.billing_address.street = x[6]
currentpayment.billing_address.city = x[7]
currentpayment.billing_address.state_code = x[8]
currentpayment.billing_address.zip_code = x[9]
currentpayment.billing_address.address_type = x[10]
all_payments.append(currentpayment)
cursor.close()
conn.close()
except Error as error:
print(error)
except Exception as e:
print(e)
return all_payments
def get_all(self, limit):
images = []
try:
# Setup connection to the DB
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
# Calls the stored procedure
cursor.callproc('getAllImages', (limit, ))
# This loop iterates through the resultsets
for result in cursor.stored_results():
# This loop iterates through the rows in each resultset
for image_row in result.fetchall():
image = Image()
image.image_id = image_row[0]
image.image_url = image_row[1]
image.caption = image_row[2]
image.book.book_id = image_row[3]
image.book.title = image_row[4]
image.book.inventory.retail_price = image_row[5]
image.book.inventory.quantity_ordered = image_row[6]
image.book.inventory.quantity_on_hand = image_row[7]
images.append(image)
# Close the connection to the DB
cursor.close()
conn.close()
except Error as error:
print(error)
except Exception as e:
print(e)
return images
def get_byid(self, order_id):
try:
# Setup connection to the DB
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
bookorders = []
args = [order_id]
# Calls the stored procedure
cursor.callproc('getBooksOnOrder', args)
bdao = BookDao()
# This loop iterates through the resultsets
for result in cursor.stored_results():
# This loop iterates through the rows in each resultset
for x in result.fetchall():
bookorder = BookOrder()
bookorder.order_id = x[0]
bookorder.book = bdao.get_byid(x[1])
bookorder.quantity = x[2]
bookorder.price = x[3]
bookorder.discount = x[4]
bookorders.append(bookorder)
# Close the connection to the DB
cursor.close()
conn.close()
except Error as error:
print(error)
except Exception as e:
print(e)
return bookorders
def getJanuaryRevenue(self):
try:
totalSum = None
# Setup connection to the DB
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
# Calls the stored procedure
cursor.callproc('getJanuaryRevenue')
# This loop iterates through the resultsets
for result in cursor.stored_results():
# This loop iterates through the rows in each resultset
for x in result.fetchall():
totalSum = x[0]
# Close the connection to the DB
cursor.close()
conn.close()
except Error as error:
print(error)
except Exception as e:
print(e)
return totalSum
def getRepeatCustomers(self):
currentinfos = []
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.callproc('getRepeatCustomers')
print('hit')
for result in cursor.stored_results():
customers = result.fetchall()
for x in customers:
currentinfo = CustomerInfo()
currentinfo.customer_id = x[0]
currentinfo.number_of_orders = x[1]
currentinfos.append(currentinfo)
cursor.close()
conn.close()
except Error as error:
print(error)
except Exception as e:
print(e)
return currentinfos
def get_all_CAddress(self):
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.callproc('getAllCustomerInfo')
all_customer_info = []
for result in cursor.stored_results():
customers = result.fetchall()
for x in customers:
currentinfo = CustomerInfo()
currentinfo.customer_id = x[0]
currentinfo.work_phone = x[1]
currentinfo.home_phone = x[2]
a = CustomerAddress()
a.address_id = x[3]
a.street = x[4]
a.city = x[5]
a.state_code = x[6]
a.address_type = x[8]
currentinfo.set_address(a)
all_customer_info.append(currentinfo)
cursor.close()
conn.close()
except Error as error:
print(error)
except Exception as e:
print(e)
return all_customer_info
def get_all(self):
publishers = []
try:
# Setup connection to the DB
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
# Calls the stored procedure
cursor.callproc('getAllPublishers')
# This loop iterates through the resultsets
for result in cursor.stored_results():
# This loop iterates through the rows in each resultset
for publisher_row in result.fetchall():
publisher = Publisher()
publisher.publisher_id = publisher_row[0]
publisher.company_name = publisher_row[1]
publisher.city = publisher_row[2]
publisher.state_code = publisher_row[3]
publisher.zip_code = publisher_row[4]
publisher.phone_number = publisher_row[5]
publisher.contact_name = publisher_row[6]
publishers.append(publisher)
# Close the connection to the DB
cursor.close()
conn.close()
except Error as error:
print(error)
except Exception as e:
print(e)
return publishers
def get_byid(self, publisher_id):
publisher = None
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
args = [publisher_id]
cursor.callproc('getPublisherByPublisherID', args)
for result in cursor.stored_results():
# This loop iterates through the rows in each resultset
for publisher_row in result.fetchall():
publisher = Publisher()
publisher.publisher_id = publisher_row[0]
publisher.company_name = publisher_row[1]
publisher.city = publisher_row[2]
publisher.state_code = publisher_row[3]
publisher.zip_code = publisher_row[4]
publisher.phone_number = publisher_row[5]
publisher.contact_name = publisher_row[6]
# Close the connection to the DB
cursor.close()
conn.close()
except Error as error:
print(error)
except Exception as e:
print(e)
return publisher
def getTotalInventoryByPublisherID(self, publisher_id):
try:
totalSum = None
# Setup connection to the DB
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
args = [publisher_id]
# Calls the stored procedure
cursor.callproc('getTotalInventoryByPublisherID', args)
# This loop iterates through the resultsets
for result in cursor.stored_results():
# This loop iterates through the rows in each resultset
for x in result.fetchall():
totalSum = x[0]
# Close the connection to the DB
cursor.close()
conn.close()
except Error as error:
print(error)
except Exception as e:
print(e)
return totalSum
def get_all(self):
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.callproc('getAllUsers')
for result in cursor.stored_results():
user = result.fetchall()
users = []
for x in user:
u = User()
u.id = x[0]
u.password = x[1]
u.last_login = x[2]
u.is_superuser = x[3]
u.username = x[4]
u.first_name = x[5]
u.last_name = x[6]
u.email = x[7]
u.is_staff = x[8]
u.is_active = x[9]
u.date_joined = x[10]
users.append(u)
conn.commit()
cursor.close()
conn.close()
except Error as error:
print(error)
except Exception as e:
print(e)
return users
def get_by_customer_and_type(self, p_customer_id, p_address_type):
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
args = (p_customer_id, p_address_type)
cursor.callproc('getAddressByCustomerAndType', args)
all_customer_address = []
for result in cursor.stored_results():
customers = result.fetchall()
for x in customers:
currentAddress = CustomerAddress()
currentAddress.address_id = x[0]
currentAddress.street = x[1]
currentAddress.city = x[2]
currentAddress.state_code = x[3]
currentAddress.zip_code = x[4]
currentAddress.customer_id = x[5]
currentAddress.address_type = x[6]
all_customer_address.append(currentAddress)
cursor.close()
conn.close()
except Error as error:
print(error)
except Exception as e:
print(e)
return all_customer_address
def get_by_address_id(
self,
p_address_id,
p_customer_id,
):
all_payments = []
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
args = [p_address_id, p_customer_id]
cursor.callproc('getPaymentInfoByAddressID', args)
for result in cursor.stored_results():
payments = result.fetchall()
for x in payments:
currentpayment = PaymentInfo()
currentpayment.card_id = x[0]
currentpayment.last_four = x[2]
currentpayment.expir_date = x[3]
currentpayment.card_issuer = x[4]
currentpayment.customer_id = x[5]
currentpayment.billing_address_id = x[6]
all_payments.append(currentpayment)
cursor.close()
conn.close()
except Error as error:
print(error)
except Exception as e:
print(e)
return all_payments
def get_byid(self, book_id):
images = []
try:
# Setup connection to the DB
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
# Calls the stored procedure
cursor.callproc('getImageByBookID', (book_id, ))
# This loop iterates through the resultsets
for result in cursor.stored_results():
# This loop iterates through the rows in each resultset
for image_row in result.fetchall():
image = Image()
image.image_id = image_row[0]
image.image_url = image_row[1]
image.caption = image_row[2]
image.book.book_id = image_row[3]
images.append(image)
# Close the connection to the DB
cursor.close()
conn.close()
except Error as error:
print(error)
except Exception as e:
print(e)
return images
def getJanuaryOrders(self):
orders = []
try:
# Setup connection to the DB
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
# Calls the stored procedure
cursor.callproc('getJanuaryOrders')
udao = UserDao()
cadao = CustomerAddressDao()
pdao = PaymentInfoDao()
# This loop iterates through the resultsets
for result in cursor.stored_results():
# This loop iterates through the rows in each resultset
for x in result.fetchall():
order = RetailOrder()
order.order_id = x[0]
order.date_ordered = x[1]
order.total_price = x[2]
order.discount = x[3]
order.customer = x[4]
orders.append(order)
# Close the connection to the DB
cursor.close()
conn.close()
except Error as error:
print(error)
except Exception as e:
print(e)
return orders
def create(self, p_retail_order):
order_id = 0
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
# Create an order in the retail order table of the DB
args = (p_retail_order.customer.customer_id,
p_retail_order.shipping_address.address_id,
p_retail_order.card.card_id, p_retail_order.discount)
cursor.callproc('createOrder', args)
order_id = cursor.lastrowid
conn.commit()
# Get the order ID of the order just created
result = next(cursor.stored_results())
order_id_row = result.fetchone()
order_id = order_id_row[0]
cursor.close()
conn.close()
except Error as error:
print(error)
except Exception as e:
print(e)
return order_id
def getOrdersByMnthYr(self, month, year):
orders = []
try:
# Setup connection to the DB
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
args = (month, year)
# Calls the stored procedure
cursor.callproc('getOrdersByMnthYr', args)
# This loop iterates through the resultsets
for result in cursor.stored_results():
# This loop iterates through the rows in each resultset
for x in result.fetchall():
order = RetailOrder()
order.order_id = x[0]
order.date_ordered = x[1]
order.total_price = x[2]
order.discount = x[3]
order.customer = x[4]
orders.append(order)
# Close the connection to the DB
cursor.close()
conn.close()
except Error as error:
print(error)
except Exception as e:
print(e)
return orders
def get_byid(self, id):
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
args = [id]
cursor.callproc('getAddressByAddressID', args)
currentAddress = CustomerAddress()
for result in cursor.stored_results():
customers = result.fetchall()
for x in customers:
currentAddress.address_id = x[0]
currentAddress.street = x[1]
currentAddress.city = x[2]
currentAddress.state_code = x[3]
currentAddress.zip_code = x[4]
currentAddress.customer_id = x[5]
currentAddress.address_type = x[6]
cursor.close()
conn.close()
except Error as error:
print(error)
except Exception as e:
print(e)
return currentAddress
def get_byid(self, order_id):
try:
# Setup connection to the DB
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
order = None
args = [order_id]
cadao = CustomerAddressDao()
pdao = PaymentInfoDao()
# Calls the stored procedure
cursor.callproc('getRetailOrderByOrderID', args)
# This loop iterates through the resultsets
for result in cursor.stored_results():
# This loop iterates through the rows in each resultset
for x in result.fetchall():
order = RetailOrder()
order.order_id = x[0]
order.date_ordered = x[1]
order.discount = x[2]
order.total_price = x[3]
order.status = x[4]
u = User()
u.id = x[5]
u.first_name = x[6]
u.last_name = x[7]
order.customer = u
p = PaymentInfo()
p.card_id = x[8]
p.last_four = x[9]
p.card_issuer = x[10]
order.card = p
a = CustomerAddress()
a.address_id = x[11]
a.street = x[12]
a.city = x[13]
a.state_code = x[14]
a.zip_code = x[15]
order.shipping_address = a
# Close the connection to the DB
cursor.close()
conn.close()
except Error as error:
print(error)
except Exception as e:
print(e)
return order
def get_all(self):
allBooks = []
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.callproc('getAllBooks')
for result in cursor.stored_results():
books = result.fetchall()
for x in books:
currentbook = Book()
currentbook.set_book_id(x[0])
currentbook.set_isbn13(x[1])
currentbook.set_isbn10(x[2])
currentbook.set_title(x[3])
currentbook.set_copyRightDate(x[4])
currentbook.set_type(x[5])
currentbook.set_edition(x[6])
currentbook.set_numberOfPages(x[7])
genre = Genre()
genre.genre_id = x[8]
genre.genre = x[14]
author = Author()
author.author_id = x[9]
author.first_name = x[12]
author.last_name = x[13]
publisher = Publisher()
publisher.publisher_id = x[10]
publisher.company_name = x[11]
currentbook.set_genre(genre)
currentbook.set_author(author)
currentbook.set_publisher(publisher)
currentbook.inventory.quantity_on_hand = x[15]
currentbook.inventory.quantity_ordered = x[16]
currentbook.inventory.cost = x[17]
currentbook.inventory.retail_price = x[18]
allBooks.append(currentbook)
conn.commit()
cursor.close()
conn.close()
except Error as error:
print(error)
return allBooks
def delete(self, p_customer):
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
args = [p_customer.address_id, p_customer.customer_id]
cursor.callproc('deleteCustomerAddress', args)
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def get_byid(self,book_id):
book = Book()
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
args = [book_id]
cursor.callproc('getBookByID', args)
# This gets the first resultset
result = next(cursor.stored_results())
# This gets the first row in the resultset
book_row = result.fetchone()
book.set_book_id(book_row[0])
book.set_isbn13(book_row[1])
book.set_isbn10(book_row[2])
book.set_title(book_row[3])
book.set_copyRightDate(book_row[4])
book.set_type(book_row[5])
book.set_edition(book_row[6])
book.set_numberOfPages(book_row[7])
genre = Genre()
genre.genre_id = book_row[8]
genre.genre = book_row[14]
author = Author()
author.author_id = book_row[9]
author.first_name = book_row[12]
author.last_name = book_row[13]
publisher = Publisher()
publisher.publisher_id = book_row[10]
publisher.company_name = book_row[11]
book.set_genre(genre)
book.set_author(author)
book.set_publisher(publisher)
book.inventory.quantity_on_hand = book_row[15]
book.inventory.quantity_ordered = book_row[16]
book.inventory.cost = book_row[17]
book.inventory.retail_price = book_row[18]
cursor.close()
conn.close()
except Error as error:
print(error)
except Exception as e:
print(e)
return book
def delete(self, b):
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
args = [b]
cursor.callproc('deleteBook', args)
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def delete(self, p_payment):
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
args = [p_payment.card_id]
cursor.callproc('deletePaymentInfo', args)
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def deactivateUser(self, id):
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
args = [id]
cursor.callproc('deactivateUser', args)
conn.commit()
cursor.close()
conn.close()
except Error as error:
print(error)
except Exception as e:
print(e)
def updateLastLogin(self, id):
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
args = [id]
cursor.callproc('updateLastLogin', args)
conn.commit()
cursor.close()
conn.close()
except Error as error:
print(error)
except Exception as e:
print(e)
def update(self, p_customer):
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
args = (p_customer.address_id, p_customer.street, p_customer.city,
p_customer.state_code, p_customer.zip_code,
p_customer.customer_id, p_customer.address_type)
cursor.callproc('updateCustomerAddress', args)
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def create(self, image):
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
args = (image.image_url, image.caption, image.book.book_id)
cursor.callproc('createImage', args)
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def delete(self, p_user):
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
args = (p_user.id)
cursor.callproc('deleteUser', args)
conn.commit()
cursor.close()
conn.close()
except Error as error:
print(error)
except Exception as e:
print(e)
