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)