def make(cls, awm, customer_name, phone, address, delivery_fees, date_,
status, note, order_items): # order_items type: list of OrderItem
try:
customer = Customer.get_by_phone(phone)
if customer is None:
customer = Customer.new_customer(customer_name, phone, address)
with DatabaseManager() as db:
query = (
"insert into OrderTable "
"(AWM, customerID, customer_name, address, phone, delivery_fees, date, status, note) "
"values(?, ?, ?, ?, ?, ?, ?, ?, ?)")
db.execute(query,
(awm, customer.ID, customer_name, address, phone,
delivery_fees, str(date_), status, note))
db.commit()
with DatabaseManager() as db:
db.execute(
'select top 1 order_id from OrderTable order by order_id desc'
)
ID = db.fetchone()[0]
for item in order_items:
item.save(ID)
order = Order(ID, awm, customer_name, phone, address,
delivery_fees, date_, status, note, order_items)
cls.orders.append(order)
return order
except pyodbc.Error as err:
print('Error while saving order to the database')
print(err)
def delete(self):
try:
with DatabaseManager() as db:
db.execute("update ColorsTable set flag = 'deleted' where lenseID = ? and color_id = ?",
(self.lense_id, self.color_id))
db.commit()
except pyodbc.Error:
print('Error while deleting color from the database')
def delete(self):
try:
with DatabaseManager() as db:
db.execute("update LensesTable set flag = 'deleted' where lense_id = ?", self.ID)
db.commit()
self.load_lenses()
except pyodbc.Error:
print('Error while deleting Lense from the database')
def delete(self):
try:
with DatabaseManager() as db:
db.execute(
'delete from OrderDetails where orderID = ? and colorID = ?',
(self.order_id, self.color_id))
db.commit()
except pyodbc.Error:
print('Error while deleting order item from the database')
def delete(self):
try:
with DatabaseManager() as db:
db.execute(
'update CustomerTable set flag = ? where customer_id = ?',
('deleted', self.ID))
db.commit()
self.load_cutomers()
except pyodbc.Error:
print('Error while deleting customer from the database')
def save(self, order_id):
self.order_id = order_id
try:
with DatabaseManager() as db:
db.execute(
'insert into OrderDetails (orderID, colorID, quantity) values(?, ?, ?)',
(self.order_id, self.color_id, self.quantity))
db.commit()
except pyodbc.Error:
print('Error while adding order item to the database in add()')
def load_cutomers(cls):
cls.customers.clear()
try:
with DatabaseManager() as db:
db.execute("select * from CustomerTable where flag = 'live'")
rows = db.fetchall()
for row in rows:
cls.customers.append(Customer(*row[:-1]))
except pyodbc.Error:
print('Error while loading customers')
def load_colors(cls, lense_id):
colors_list = []
try:
with DatabaseManager() as db:
db.execute("select * from ColorsTable where lenseID = ? and flag = 'live'", lense_id)
colors_data = db.fetchall()
for row in colors_data:
colors_list.append(Color(*row[:-1]))
return colors_list
except pyodbc.Error as err:
print('Error while loading colors data from the database', err, sep='\n')
def edit(self, name, quantity):
try:
with DatabaseManager() as db:
db.execute('update ColorsTable set name = ?, quantity = ? where lenseID = ? and color_id = ?',
(name, quantity, self.lense_id, self.color_id))
db.commit()
self.name = name
self.quantity = quantity
except pyodbc.Error as err:
print('Error while updating color data in the database')
print(err)
def save(cls, lense_id, name, quantity):
try:
with DatabaseManager() as db:
db.execute('insert into ColorsTable (lenseID, name, quantity) values(?, ?, ?)',
(lense_id, name, quantity))
db.commit()
db.execute('select top 1 color_id from ColorsTable order by color_id desc')
color_id = db.fetchone()[0]
return Color(lense_id, color_id, name, quantity)
except pyodbc.Error as err:
print('Error while adding new color to the database')
print(err)
def update_prices(self, wholesale_price, selling_price):
current_date, max_date = self.get_dates()
with DatabaseManager() as db:
db.execute('update PriceDetails set end_date = ? where end_date = ?', (current_date, max_date))
db.commit()
sleep(1)
current_date, max_date = self.get_dates()
db.execute('insert into PriceDetails (lense_id, wholesale_price, selling_price, start_date, end_date) values(?, ?, ?, ?, ?)',
self.ID, wholesale_price, selling_price,current_date, max_date)
db.commit()
self.wholesale_price = wholesale_price
self.selling_price = selling_price
def edit(self, name, diameter):
# we will just edit the lenses data and the colors will be updated separately
try:
with DatabaseManager() as db:
db.execute('update LensesTable set name = ?, diameter = ? where lense_id = ?',
(name, diameter, self.ID))
db.commit()
self.name = name
self.diameter = diameter
except pyodbc.Error as err:
print('Error while updating lense data in the database')
print(err)
def new(cls, name, wholesale_price, selling_price, diameter, colors: list): # colors is a list of colornamedtuple objects
colors_list = []
current_date, max_date = cls.get_dates()
try:
with DatabaseManager() as db:
db.execute('insert into LensesTable (name, diameter) values(?, ?)',
(name, diameter))
db.commit()
db.execute('select top 1 lense_id from LensesTable order by lense_id desc')
id_ = db.fetchone()[0]
for color in colors:
colors_list.append(Color.save(id_, color.name, color.quantity))
with DatabaseManager() as db:
db.execute(f"insert into PriceDetails (lense_id, wholesale_price, selling_price, start_date, end_date) values(?, ?, ?, ?, ?)",
(id_, wholesale_price, selling_price, current_date, max_date))
db.commit()
cls.load_lenses()
return Lense(id_, name, diameter, colors_list)
except pyodbc.Error as err:
print('Error while adding new lense to the database')
print(err)
def get_prices(self):
current_datetime = datetime.now()
try:
with DatabaseManager() as db:
db.execute('select wholesale_price, selling_price from PriceDetails where ? between start_date and end_date and lense_id = ?',
(current_datetime, self.ID))
prices = db.fetchone()
if prices:
return prices
else:
return None, None
except pyodbc.Error:
print('Error while fetching the price from the database')
def load_lenses(cls):
cls.lenses.clear()
try:
with DatabaseManager() as db:
db.execute("select lense_id, name, diameter from LensesTable where flag = 'live'")
lenses_data = db.fetchall()
for row in lenses_data:
colors = cls.load_colors(row[0])
lense = Lense(*row, colors)
if lense.wholesale_price is not None and lense.selling_price is not None:
cls.lenses.append(lense)
except pyodbc.Error as err:
print('Error while loading lenses data from the database', err, sep='\n')
def edit(self, new_name, new_phone, new_address):
try:
with DatabaseManager() as db:
# db.execute('update CustomerTable set name = ?, phone = ?, address = ? where customer_id = ?',
# (new_name, new_phone, new_address, self.ID))
db.execute(
f"update CustomerTable set name = N'{new_name}', phone = N'{new_phone}', address = N'{new_address}' where customer_id = {self.ID}"
)
db.commit()
self.name = new_name
self.phone = new_phone
self.address = new_address
except pyodbc.Error as err:
print('error while editing the customer data')
print(str(err))
def new_customer(cls, name, phone, address) -> 'Customer':
try:
with DatabaseManager() as db:
# db.execute("insert into CustomerTable (name, phone, address) values(?, ?, ?)",
# (name, phone, address))
db.execute(
f"insert into CustomerTable (name, phone, address) values(N'{name}', N'{phone}', N'{address}')"
)
db.commit()
cls.load_cutomers()
except pyodbc.Error as err:
print('Error while adding new customer data to the database')
print(err)
finally:
return cls.get_by_phone(phone)