class Status:
def __init__(self):
self.db = DBHelper()
def create(self, statusId, statusName):
data, columns = self.db.fetch ("SELECT * FROM status WHERE status_id = '{}' ".format(statusId))
if len(data) > 0:
return {'Is Error': True, 'Error Message': "Status ID '{}' already exists. Cannot Create. ".format(statusId)}
else:
self.db.execute ("INSERT INTO status (status_id,status_name) VALUES ('{}' ,'{}')".format(statusId,statusName))
return {'Is Error': False, 'Error Message': ""}
def read(self, statusId):
data, columns = self.db.fetch ("SELECT status_id, status_name FROM status WHERE status_id = '{}' ".format(statusId))
if len(data) > 0:
retStatus = row_as_dict(data, columns)
else:
return ({'Is Error': True, 'Error Message': "Status ID '{}' not found. Cannot Read.".format(statusId)},{})
return ({'Is Error': False, 'Error Message': ""},retStatus)
def update(self, statusId, newstatusName):
data, columns = self.db.fetch ("SELECT * FROM status WHERE status_id = '{}' ".format(statusId))
if len(data) > 0:
self.db.execute ("UPDATE status SET status_name='{}' WHERE status_id='{}' ".format(newstatusName, statusId))
else:
return {'Is Error': True, 'Error Message': "Status ID '{}' not found. Cannot Update.".format(statusId)}
return {'Is Error': False, 'Error Message': ""}
def delete(self, statusId):
data, columns = self.db.fetch ("SELECT * FROM status WHERE status_id = '{}' ".format(statusId))
if len(data) > 0:
self.db.execute ("DELETE FROM status WHERE status_id='{}' ".format(statusId))
else:
return {'Is Error': True, 'Error Message': "Status ID '{}' not found. Cannot Delete".format(statusId)}
return {'Is Error': False, 'Error Message': ""}
def dump(self):
data, columns = self.db.fetch ('SELECT status_id as "Status_ID", status_name as "Status_Name" FROM status ')
return row_as_dict(data, columns)
class Repairlist:
def __init__(self):
self.db = DBHelper()
def __updateRepairlistTotal(self, repairId):
sql = (
"UPDATE repairlist SET "
" total = line_item.new_total "
" FROM (SELECT repair_id, SUM(price) as new_total FROM repair_item GROUP BY repair_id) line_item "
" WHERE repairlist.repair_id = line_item.repair_id "
" AND repairlist.repair_id = '{}' ".format(repairId))
self.db.execute(sql)
def __updateLineItem(self, repairId, repairLineTuplesList):
self.db.execute(
"DELETE FROM repair_item WHERE repair_id = '{}' ".format(repairId))
for lineItem in repairLineTuplesList:
self.db.execute(
"INSERT INTO repair_item (repair_id,user_id,item_id,amount_item,paid_amount,repair_date,description) VALUES ('{}','{}','{}','{}','{}',{},'{}')"
.format(repairId, lineItem["User ID"], lineItem["Item ID"],
lineItem["Amount Item"], lineItem["Paid Amount"],
lineItem["Repaid Date"], lineItem["Description"]))
self.__updateRepairlistTotal(repairId)
def create(self, repairId, statusId, maintenanceId, phone, imformDate,
acceptrDate, repairLineTuplesList):
data, columns = self.db.fetch(
"SELECT * FROM repairlist WHERE repair_id = '{}' ".format(
repairId))
if len(data) > 0:
return {
'Is Error':
True,
'Error Message':
"Repair ID '{}' already exists. Cannot Create. ".format(
repairId)
}
else:
self.db.execute(
"INSERT INTO repairlist (repair_id,status_id,maintenance_id,phone,inform_date,accept_date) VALUES ('{}' ,'{}','{}','{}',{},{})"
.format(repairId, statusId, maintenanceId, phone, imformDate,
acceptrDate))
self.__updateLineItem(repairId, repairLineTuplesList)
return {'Is Error': False, 'Error Message': ""}
def read(self, repairId):
data, columns = self.db.fetch(
"SELECT repair_id,status_id,maintenanace_id,phone,inform_date,accept_date,total FROM repairlist WHERE repair_id = '{}' "
.format(repairId))
if len(data) > 0:
retRepairlist = row_as_dict(data, columns)
else:
return ({
'Is Error':
True,
'Error Message':
"Repair ID '{}' not found. Cannot Read.".format(repairId)
}, {})
return ({'Is Error': False, 'Error Message': ""}, retRepairlist)
def update(self, repairId, newStatusId, newMaintenanceId, newPhone,
newImformDate, newAcceptrDate, newRepairLineTuplesList):
data, columns = self.db.fetch(
"SELECT * FROM repairlist WHERE repair_id = '{}' ".format(
repairId))
if len(data) > 0:
self.db.execute(
"UPDATE repairlist SET status_id = '{}', maintanence_id = '{}', phone='{}',inform_date={},accept_date={} WHERE repair_id = '{}' "
.format(newStatusId, newMaintenanceId, newPhone, newImformDate,
newAcceptrDate, repairId))
self.__updateLineItem(repairId, newRepairLineTuplesList)
else:
return {
'Is Error':
True,
'Error Message':
"Repair ID '{}' not found. Cannot Update.".format(repairId)
}
return {'Is Error': False, 'Error Message': ""}
def delete(self, repairId):
data, columns = self.db.fetch(
"SELECT * FROM repairlist WHERE repair_id = '{}' ".format(
repairId))
if len(data) > 0:
self.db.execute(
"DELETE FROM repairlist WHERE repair_id = '{}' ".format(
repairId))
self.db.execute(
"DELETE FROM repair_item WHERE repair_id = '{}' ".format(
repairId))
else:
return {
'Is Error':
True,
'Error Message':
"Repair ID '{}' not found. Cannot Delete".format(repairId)
}
return {'Is Error': False, 'Error Message': ""}
def dump(self):
db = DBHelper
data, columns = db.fetch(
'SELECT r.repair_id as "Repair ID", r.status_id as "Status ID", r.maintenance_id as "Maintenance ID" '
' , r.phone as "Phone", r.inform_date as "Inform Date",r.accept_date as "Accept Date", r.total as "Total"'
' , ri.user_id as "User ID", ri.item_id as "Item ID" '
' , ri.amount_item as "Amount Item", ri.paid_amount as "Paid Amount", ri.repair_date as "Repair Date", ri.description as "Description" '
' FROM repairlist r JOIN status s ON r.status_id = s.status_id '
' JOIN maintenance m ON r.maintenance_id = m.maintenance_id'
' JOIN repair_item ri ON r.repair_id = ri.repair_id '
' JOIN userlogin u ON ri.user_id = u.user_id '
' JOIN itemlist t ON ri.item_id = t.item_id'
' ')
return row_as_dict(data, columns)
def update_repairlist_line(self, repairId, userId, itemId, amountItem,
paidAmount, repairDate, description):
data, columns = self.db.fetch(
"SELECT * FROM repair_item WHERE repair_id = '{}' AND user_id = '{}' AND item_id = '{}' "
.format(repairId, userId, itemId))
if len(data) > 0:
self.db.execute(
"UPDATE repair_item SET amount_item = '{}', paid_amount = '{}', repair_date={}, decription ='{}' WHERE repair_id = '{}' AND user_id = '{}' AND item_id = '{}' "
.format(amountItem, paidAmount, repairDate, description,
repairId, userId, itemId))
self.__updateRepairlistTotal(repairId)
else:
return {
'Is Error':
True,
'Error Message':
"User ID '{}' AND Item ID '{}' not found in Repair ID '{}'. Cannot Update."
.format(userId, itemId, repairId)
}
return {'Is Error': False, 'Error Message': ""}
def delete_invoice_line(self, repairId, userId, itemId):
data, columns = self.db.fetch(
"SELECT * FROM repair_item WHERE repair_id = '{}' AND user_id = '{}' AND item_id = '{}' "
.format(repairId, userId, itemId))
if len(data) > 0:
self.db.execute(
"DELETE FROM repair_item WHERE repair_id = '{}' AND user_id = '{}' AND item_id = '{}' "
.format(repairId, userId, itemId))
self.__updateRepairlistTotal(repairId)
else:
return {
'Is Error':
True,
'Error Message':
"User ID '{}' AND Item ID '{}' not found in Repair ID '{}'. Cannot Update."
.format(userId, itemId, repairId)
}
return {'Is Error': False, 'Error Message': ""}
class Maintenance:
def __init__(self):
self.db = DBHelper()
def create(self, maintenanceId, maintenanceName, tel, workingDate,
workingTime):
data, columns = self.db.fetch(
"SELECT * FROM maintenance WHERE maintenance_id = '{}' ".format(
maintenanceId))
if len(data) > 0:
return {
'Is Error':
True,
'Error Message':
"Maintenance ID '{}' already exists. Cannot Create. ".format(
maintenanceId)
}
else:
self.db.execute(
"INSERT INTO maintenance (maintenance_id,maintenance_name,tel,working_date,working_time) VALUES ('{}' ,'{}','{}',{},{})"
.format(maintenanceId, maintenanceName, tel, workingDate,
workingTime))
return {'Is Error': False, 'Error Message': ""}
def read(self, maintenanceId):
data, columns = self.db.fetch(
"SELECT maintenance_id,maintenance_name,tel,working_date,working_time FROM maintenance WHERE maintenance_id = '{}' "
.format(maintenanceId))
if len(data) > 0:
retMaintenance = row_as_dict(data, columns)
else:
return ({
'Is Error':
True,
'Error Message':
"Maintenance ID '{}' not found. Cannot Read.".format(
maintenanceId)
}, {})
return ({'Is Error': False, 'Error Message': ""}, retMaintenance)
def update(self, maintenanceId, newmaintenanceName, newtel, newworkingDate,
newworkingTime):
data, columns = self.db.fetch(
"SELECT * FROM maintenance WHERE maintenance_id = '{}' ".format(
maintenanceId))
if len(data) > 0:
self.db.execute(
" UPDATE maintenance SET maintenance_name='{}',tel='{}',working_date= {} ,working_time = {} "
.format(newmaintenanceName, newtel, newworkingDate,
newworkingTime, maintenanceId))
else:
return {
'Is Error':
True,
'Error Message':
"Maintenance ID '{}' not found. Cannot Update.".format(
maintenanceId)
}
return {'Is Error': False, 'Error Message': ""}
def delete(self, maintenanceId):
data, columns = self.db.fetch(
"SELECT * FROM maintenance WHERE maintenance_id = '{}' ".format(
maintenanceId))
if len(data) > 0:
self.db.execute(
"DELETE FROM maintenance WHERE maintenance_id='{}' ".format(
maintenanceId))
else:
return {
'Is Error':
True,
'Error Message':
"Maintenance ID '{}' not found. Cannot Delete".format(
maintenanceId)
}
return {'Is Error': False, 'Error Message': ""}
def dump(self):
data, columns = self.db.fetch(
'SELECT maintenance_id as "Maintenance ID", maintenance.maintanance_name as "Maintenance Name", tel as "Tel", working_date as "Working Date", working_time as "Working Time" '
'FROM maintenance')
return row_as_dict(data, columns)
class Userlogin:
def __init__(self):
self.db = DBHelper()
def create(self, userId, password, userType, userName):
data, columns = self.db.fetch(
"SELECT * FROM userlogin WHERE user_id = '{}' ".format(userId))
if len(data) > 0:
return {
'Is Error':
True,
'Error Message':
"User ID '{}' already exists. Cannot Create. ".format(userId)
}
else:
self.db.execute(
"INSERT INTO userlogin (user_id,password,user_type,user_name) VALUES ('{}' ,'{}','{}','{}')"
.format(userId, password, userType, userName))
return {'Is Error': False, 'Error Message': ""}
def read(self, userId):
data, columns = self.db.fetch(
"SELECT user_id,password,user_type,user_name FROM userlogin WHERE user_id = '{}' "
.format(userId))
if len(data) > 0:
retUserlogin = row_as_dict(data, columns)
else:
return ({
'Is Error':
True,
'Error Message':
"User ID '{}' not found. Cannot Read.".format(userId)
}, {})
return ({'Is Error': False, 'Error Message': ""}, retUserlogin)
def update(self, userId, newpassword, newuserType, newuserName):
data, columns = self.db.fetch(
"SELECT * FROM userlogin WHERE user_id = '{}' ".format(userId))
if len(data) > 0:
self.db.execute(
" UPDATE userlogin SET password='******',user_type='{}',user_name='{}' "
.format(newpassword, newuserType, newuserName, userId))
else:
return {
'Is Error':
True,
'Error Message':
"User ID '{}' not found. Cannot Update.".format(userId)
}
return {'Is Error': False, 'Error Message': ""}
def delete(self, userId):
data, columns = self.db.fetch(
"SELECT * FROM userlogin WHERE user_id = '{}' ".format(userId))
if len(data) > 0:
self.db.execute(
"DELETE FROM userlogin WHERE user_id='{}' ".format(userId))
else:
return {
'Is Error':
True,
'Error Message':
"User ID '{}' not found. Cannot Delete".format(userId)
}
return {'Is Error': False, 'Error Message': ""}
def dump(self):
data, columns = self.db.fetch(
'SELECT user_id as "User ID",password as "Password", user_type as "User Type", user_name as "User Name" FROM userlogin '
)
return row_as_dict(data, columns)
class Itemlist:
def __init__(self):
self.db = DBHelper()
def create(self, itemId, itemType, itemName, quantity, price):
data, columns = self.db.fetch(
"SELECT * FROM itemlist WHERE item_id = '{}' ".format(itemId))
if len(data) > 0:
return {
'Is Error':
True,
'Error Message':
"Item ID '{}' already exists. Cannot Create. ".format(itemId)
}
else:
self.db.execute(
"INSERT INTO itemlist (item_id,item_type,item_name,quantity,price) VALUES ('{}' ,'{}','{}','{}','{}')"
.format(itemId, itemType, itemName, quantity, price))
return {'Is Error': False, 'Error Message': ""}
def read(self, itemId):
data, columns = self.db.fetch(
"SELECT item_id,item_type,item_name,quantity,price FROM itemlist WHERE item_id = '{}' "
.format(itemId))
if len(data) > 0:
retItemlist = row_as_dict(data, columns)
else:
return ({
'Is Error':
True,
'Error Message':
"Item ID '{}' not found. Cannot Read.".format(itemId)
}, {})
return ({'Is Error': False, 'Error Message': ""}, retItemlist)
def update(self, itemId, newitemType, newitemName, newQuantity, newPrice):
data, columns = self.db.fetch(
"SELECT * FROM itemlist WHERE item_id = '{}' ".format(itemId))
if len(data) > 0:
self.db.execute(
"UPDATE itemlist SET item_type='{}',item_name='{}',quantity='{}',price='{}' WHERE item_id='{}' "
.format(newitemType, newitemName, newQuantity, newPrice,
itemId))
else:
return {
'Is Error':
True,
'Error Message':
"Item ID '{}' not found. Cannot Update.".format(itemId)
}
return {'Is Error': False, 'Error Message': ""}
def delete(self, itemId):
data, columns = self.db.fetch(
"SELECT * FROM itemlist WHERE item_id = '{}' ".format(itemId))
if len(data) > 0:
self.db.execute(
"DELETE FROM itemlist WHERE item_id='{}' ".format(itemId))
else:
return {
'Is Error':
True,
'Error Message':
"Item ID '{}' not found. Cannot Delete".format(itemId)
}
return {'Is Error': False, 'Error Message': ""}
def dump(self):
data, columns = self.db.fetch(
'SELECT item_id as "Item ID",item_type as "Item Type", item_name as "Item Name", quantity as "Quantity", price as "Price" FROM itemlist '
)
return row_as_dict(data, columns)