def get_nameinfo_by_username(self, username):
sql_statement = "select Usertable.nameInfo from Usertable where Usertable.username='******';"
sql_statement = sql_statement.format(username)
result = run_sql_string_statement(sql_statement)
if len(result) == 0 or len(result[0]) == 0:
return ""
return result[0][0]
def insert_record_to_company(self, username, headquaters):
assert isinstance(username, str)
assert isinstance(headquaters, str)
sql_statement = "insert Company values('{0}', '{1}');"
sql_statement = sql_statement.format(username, headquaters)
result = run_sql_string_statement(sql_statement)
return len(result) == 0
def insert_record_to_government(self, username, jurisdiction):
assert isinstance(username, str)
assert isinstance(jurisdiction, str)
sql_statement = "insert GovernmentAgencies values('{0}', '{1}');"
sql_statement = sql_statement.format(username, jurisdiction)
result = run_sql_string_statement(sql_statement)
return len(result) == 0
def insert_record_to_municipalities(self, username, population):
assert isinstance(username, str)
assert isinstance(population, int)
sql_statement = "insert Municipalities values('{0}', {1});"
sql_statement = sql_statement.format(username, population)
result = run_sql_string_statement(sql_statement)
return len(result) == 0
def get_all_records(self):
'''
@rtype: tuple
'''
sql_statement = "select * from ERMS.Usertable;"
result = run_sql_string_statement(sql_statement)
return result
def insert(self, username, nameinfo, passwordinfo):
'''
@rtype: bool
'''
sql_statement = "insert Usertable(Usertable.username, Usertable.nameInfo, Usertable.passwordInfo) values('{0}', '{1}', '{2}');"
sql_statement = sql_statement.format(username, nameinfo, passwordinfo)
result = run_sql_string_statement(sql_statement)
return True if len(result) == 0 else False
def update_status_resource_id(self, resId, newstatus):
assert isinstance(resId, int)
assert isinstance(newstatus, str)
sql_statement = "update Resource set CurrentStatus='{0}' where ResourceID={1};"
sql_statement = sql_statement.format(newstatus, str(resId))
result = run_sql_string_statement(sql_statement)
def insert_record_to_individual(self, username, jobtitle, dateOfHired):
assert isinstance(username, str)
assert isinstance(jobtitle, str)
assert isinstance(dateOfHired, str)
sql_statement = "insert Individuals values('{0}', '{1}', '{2}');"
sql_statement = sql_statement.format(username, jobtitle, dateOfHired)
result = run_sql_string_statement(sql_statement)
return len(result) == 0
def get_record_by_username(self, username):
'''
@rtype: tuple
'''
sql_statement = "select * from ERMS.Usertable as t1 where t1.username='******';"
sql_statement = sql_statement.format(username)
result = run_sql_string_statement(sql_statement)
assert isinstance(result, tuple)
return result
def insert_cost_unit(self, cost_unit):
'''
@rtype: bool
'''
assert isinstance(cost_unit, str)
sql_statement = "insert CostUnit values('{0}');".format(cost_unit)
result = run_sql_string_statement(sql_statement)
print result
return len(result) == 0
def get_count_as_int(self):
'''
@rtype: int
'''
sql_statement = "select count(*) from Resource;"
result = run_sql_string_statement(sql_statement)
return int(result[0][0])
def insert_capability(self, capability):
'''
@rtype: bool
'''
assert isinstance(capability, list)
for item in capability:
sql_statement = "insert Capability values({0}, '{1}');".format(
self.resourceID, item)
result = run_sql_string_statement(sql_statement)
return True
def get_record_by_esfId(self, esfId):
'''
@rtype: str
@param esfId: int
'''
assert isinstance(esfId, int)
sql_statement = "select ESF.item from ERMS.ESF where EsfID={0};"
sql_statement = sql_statement.format(esfId)
result = run_sql_string_statement(sql_statement)
return result[0][0] if len(result) == 1 else ""
def insert_record(self, resid, incid):
'''
@rtype: bool
'''
assert isinstance(resid, int)
assert isinstance(incid, int)
sql_statement = "insert ERMS.SearchResource values({0},{1});"
sql_statement = sql_statement.format(resid, incid)
result = run_sql_string_statement(sql_statement)
print result
return len(result) == 0
def get_all_esf_records(self):
'''
@rtype: dict
'''
sql_statement = "select * from ERMS.ESF;"
result = run_sql_string_statement(sql_statement)
resp = dict()
for row in result:
resp[row[0]] = row[1]
return resp
def get_count(self):
'''
@rtype: int
'''
sql_statement = "SELECT COUNT(*) FROM ERMS.ResourceRequest;"
result = run_sql_string_statement(sql_statement)
if len(result) == 0 or len(result[0]) == 0:
return 0
return int(result[0][0])
def get_inciId_by_resourceId(self, resid):
'''
@rtype: int
'''
assert isinstance(resid, int)
sql_statement = "SELECT ERMS.SearchResource.IncidentID FROM ERMS.SearchResource where ERMS.SearchResource.ResourceID={0};"
sql_statement = sql_statement.format(str(resid))
result = run_sql_string_statement(sql_statement)
if len(result) == 0 or len(result[0]) == 0:
return ""
return result[0][0]
def get_title_info_from_government(self, username):
'''
@rtype: str
@param username: str
'''
assert isinstance(username, str)
sql_statement = "select jurisdiction from GovernmentAgencies where username='******';".format(
username)
result = run_sql_string_statement(sql_statement)
assert isinstance(result, tuple)
if len(result) == 1 and len(result[0]) == 1:
return result[0][0]
return ""
def get_title_info_from_municipalities(self, username):
'''
@rtype: str
@param username: str
'''
assert isinstance(username, str)
sql_statement = "select populationSize from Municipalities where username='******';".format(
username)
result = run_sql_string_statement(sql_statement)
assert isinstance(result, tuple)
if len(result) == 1 and len(result[0]) == 1:
return str(result[0][0])
return ""
def get_expectedreturndate_by_resourceId(self, resid):
'''
@rtype: str
'''
assert isinstance(resid, int)
sql_statement = "SELECT ERMS.ResourceRequest.ExpectedReturnDate FROM ERMS.ResourceRequest where ERMS.ResourceRequest.ResourceID={0};"
sql_statement = sql_statement.format(str(resid))
result = run_sql_string_statement(sql_statement)
if len(result) == 0 or len(result[0]) == 0:
return ""
return str(result[0][0])
def get_title_info_from_company(self, username):
'''
@rtype: str
@param username: str
'''
assert isinstance(username, str)
sql_statement = "select headquarteres from Company where username='******';".format(
username)
result = run_sql_string_statement(sql_statement)
assert isinstance(result, tuple)
if len(result) == 1 and len(result[0]) == 1:
return result[0][0]
return ""
def get_user_type_by_username(self, username):
'''
@rtype: str
@param username: str
'''
assert isinstance(username, str)
sql_statement = "select nameinfo from Usertable where username='******';".format(
username)
result = run_sql_string_statement(sql_statement)
assert isinstance(result, tuple)
if len(result) == 1 and len(result[0]) == 1:
return result[0][0]
return ""
def get_enddate_by_resourceId(self, resid):
'''
@rtype: str
'''
assert isinstance(resid, int)
sql_statement = "SELECT ERMS.RepairResource.endDate FROM ERMS.RepairResource where ERMS.RepairResource.ResourceID={0};"
sql_statement = sql_statement.format(str(resid))
result = run_sql_string_statement(sql_statement)
if len(result) == 0 or len(result[0]) == 0:
return ""
return str(result[0][0])
def insert_records(self, resId, repId, username, sd, ed):
'''
@rtype: bool
'''
assert isinstance(resId, int)
assert isinstance(repId, int)
assert isinstance(username, str)
assert isinstance(sd, str)
assert isinstance(ed, str)
sql_statement = "insert ERMS.RepairResource values({0},{1},'{2}','{3}','{4}');"
sql_statement = sql_statement.format(resId, repId, username, sd, ed)
result = run_sql_string_statement(sql_statement)
print result
return len(result) == 0
def get_count_by_resId(self, resid):
'''
@rtype: int
'''
sql_statement = "SELECT COUNT(*) FROM ERMS.RepairResource where ERMS.RepairResource.ResourceID={0};".format(
str(resid))
result = run_sql_string_statement(sql_statement)
if len(result) == 0 or len(result[0]) == 0:
return 0
return int(result[0][0])
def delete_by_resId(self, resid):
'''
@rtype: str
'''
assert isinstance(resid, int)
sql_statement = "delete from ERMS.RepairResource where ERMS.RepairResource.ResourceID={0};"
sql_statement = sql_statement.format(str(resid))
result = run_sql_string_statement(sql_statement)
if len(result) == 0 or len(result[0]) == 0:
return ""
return str(result[0][0])
def delete_by_resIdAndInciId(self, resid, incid):
'''
@rtype: str
'''
assert isinstance(resid, int)
assert isinstance(incid, int)
sql_statement = "delete from ERMS.ResourceRequest where ERMS.ResourceRequest.ResourceID={0} and ERMS.ResourceRequest.IncidentID={1};"
sql_statement = sql_statement.format(str(resid), str(incid))
result = run_sql_string_statement(sql_statement)
if len(result) == 0 or len(result[0]) == 0:
return ""
return str(result[0][0])
def get_all_capabilities_under_resourceID(self):
'''
@rtype: list
'''
sql_statement = "select Capability.CapabilitiyStr from Capability where Capability.ResourceID={0};".format(
str(self.resourceID))
result = run_sql_string_statement(sql_statement)
resp = list()
for row in result:
resp.append(row[0])
return resp
def get_resname_by_resourceId(self, resId):
'''
@rtype: str
'''
assert isinstance(resId, int)
sql_statement = "select Resource.ResourceName from Resource where Resource.ResourceID={0};"
sql_statement = sql_statement.format(str(resId))
result = run_sql_string_statement(sql_statement)
if len(result) == 0 or len(result[0]) == 0:
return ""
return str(result[0][0])
def get_username_by_incidentId(self, incidentId):
'''
@rtype: str
'''
assert isinstance(incidentId, int)
sql_statement = "select Incident.username from Incident where Incident.IncidentID={0};"
sql_statement = sql_statement.format(str(incidentId))
result = run_sql_string_statement(sql_statement)
if len(result) == 0 or len(result[0]) == 0:
return ""
return str(result[0][0])