def login():
conn = psycopg2.connect(
database="project1",
user="******",
host="localhost",
port="5432"
)
cur = conn.cursor()
x = request.form['username']
y = request.form['password']
cur.execute( "SELECT id FROM users WHERE username = %s AND password = %s ",
(x,y)
)
userid = str(cur.fetchone()[0])
cur.execute("SELECT role FROM users Where id = %s ",
(userid)
)
role = cur.fetchone()[0]
session['userid'] = userid
if role is '1':
return redirect('/admin')
if role is '2':
return redirect('/user')
if role is '3':
return redirect('/superuser')
def getPrincipalAgent(self):
try:
#Test to see if the Agent instance has a value for principal_agent_name
if self.principal_agent_name == None:
raise ValueError('Invalid Principal Agent Name')
else:
full_name = self.principal_agent_name.split()
result = None
if len(full_name) == 2:
cur.execute(
""" SELECT a.agent_id
FROM agents a
WHERE UPPER( a.first_name ) = UPPER( %s )
AND UPPER( a.last_name ) = UPPER( %s )""",
(cleanForSQL(full_name[0]), cleanForSQL(full_name[1])))
result = cur.fetchone()
if result is None:
self.principal_agent_id = None
else:
self.principal_agent_id = result[0]
return True
except (Exception, psycopg2.DatabaseError, ValueError) as error:
print("Error in Getting Principal Agent ID for Agency " +
self.name + "\n" + error)
return False
def storeFeatures(id, object_type, feature):
"""
Stores a list of tuple of features inside the object_features table. For each feature inside the features tuple, it first queries the features_lkp table to check if that feature already exists inside the table.
If it does, it retrieves that feature's feature_id. Otherwise, it inserts that feature into the features_lkp table and returns the newly inserted feature's feature_id.
It then uses the feature's feature_id and the id of the object to create a linking row inside the object_features table
Keyword arguements:
id -- ID of the Object that the features are being linked against.
object_type -- Object Type of the Object that the features are being linked against.
features -- Tuple of features that are going to be linked against the object.
"""
try:
#Only insert if the feature doesn't exist.
check_feature_exists = f"SELECT feature_id FROM features_lkp WHERE UPPER( description ) = '{str.upper( feature )}'"
cur.execute(check_feature_exists, "")
row = cur.fetchone()
if row == None:
feature_id = returnNextSerialID('object_features', 'feature_id')
#We need to store the new feature inside the features table.
cur.execute(
f""" INSERT INTO features_lkp( feature_id, description) VALUES( {feature_id}, '{cleanForSQL(feature)}' )""",
"")
else:
feature_id = row[0]
#Once we've acquired the feature_id...
object_features_insert_statement = f""" INSERT INTO object_features( id, object_type, feature_id, entered_when, entered_who )
VALUES( {id}, {object_type}, {feature_id}, current_timestamp, 1 )"""
cur.execute(object_features_insert_statement, "")
return True
except (Exception, psycopg2.DatabaseError) as error:
print("Error in INSERTING Feature " + feature + "\n" + "Error: " +
error)
return False
def checkAndStoreDocType(self, commit):
try:
if not checkRowExists(
f"SELECT 1 FROM document_type WHERE UPPER(description) = '{self.document_type_description}'"
):
document_type = returnNextSerialID('document_type',
'document_type')
cur.execute(
f""" INSERT INTO document_type(description)
VALUES( '{self.document_type_description}' )""",
"")
if commit:
conn.commit()
self.document_type = document_type
else:
cur.execute(
f"SELECT document_type FROM document_type WHERE UPPER(description) = '{self.document_type_description}'",
"")
result = cur.fetchone()
self.document_type = result[0]
except (Exception, psycopg2.DatabaseError) as error:
print("Error in Checking Description of File with File ID " +
str(self.file_id) + " with Document Type Description " +
self.document_type_description + "\n" + "Error: " + error)
return None
def QueryWithSingleValue( tableName, searchColumn, searchValue, valueColumn, searchRequiresQuotes ):
"""
This Function retrieves a single value from a Sql Query built from the various passed parameters.
The format is:
SELECT (valueColumn) FROM (tableName) WHERE (searchColumn) = (appended_search_value)
If there are multiple values returned from the query, only the very first one is returned.
Keyword arguements:
tableName -- Name of the Table.
searchColumn -- Comparison Column(against searchValue)
searchValue -- Value we are using in the search.
valueColumn -- Column that holds the value we are searching for.
searchRequiresQuotes -- If True, a set of quotes is appended to the searchValue.
"""
try:
if searchRequiresQuotes:
appended_search_value = cleanForSQL( searchValue )
appended_search_value = VarIf(searchRequiresQuotes, "'" + appended_search_value + "'", appended_search_value )
query = f"SELECT {valueColumn} FROM {tableName} WHERE {searchColumn} = {appended_search_value}"
cur.execute( query, "")
row = cur.fetchone()
if row is None:
return None
else:
return row[0]
except(Exception, psycopg2.DatabaseError) as error:
print(error)
def get_flair_if_exists(guildid, flair):
cur.execute(
"SELECT * from flairmap WHERE discord_server=? AND dgg_flair=?",
(guildid, flair['name']))
row = cur.fetchone()
if row:
return row
else:
return None
async def refresh_flair_to_role(guild, flair):
# get the record
cur.execute(
"SELECT * from flairmap WHERE discord_server=? AND dgg_flair=?",
(guild.id, flair['name']))
row = cur.fetchone()
# what time is it, right now (in UTC)
now = datetime.now(timezone.utc).isoformat()
# get the role from the ID the db returned
role = guild.get_role(row[1]) # 1 = discord_role
if role is None:
# if the role disappeared, just delete the record and re-add
logger.warn(
"refresh_flair_to_role() flair {0[2]} is supposed to be in DB as ID {0[1]} but does not exist. Removing DB entry and re-adding"
.format(row))
cur.execute(
"DELETE from flairmap WHERE discord_server=? AND dgg_flair=?",
(guild.id, flair['name']))
await create_new_flair_to_role(guild, flair)
else:
# we need to make sure the name and color are still matching
if role.name != flair['label'] or str(
role.color) != flair['color'].lower():
logger.warn(
"refresh_flair_to_role() flair {0[2]} has been edited, reverting changes to reflect API truth"
.format(row))
if flair['color'] == "":
color_hex = int("0x000000", 16)
else:
color_hex = int(flair['color'].replace("#", "0x"), 16)
color = client.discord.Color(color_hex)
udrole = await role.edit(name=flair['label'],
color=color,
hoist=True)
# update the database
cur.execute(
"UPDATE flairmap SET last_updated=? WHERE discord_role=?",
(now, row[1]))
logger.info(
"refresh_flair_to_role() flair {0[2]} with ID {0[1]} is ok, no changes required"
.format(row))
cur.execute("UPDATE flairmap SET last_refresh=? WHERE discord_role=?",
(now, row[1]))
con.commit()
def checkRowExists( query ):
"""
Function checks to see if a particular row exists inside the query parameter. The query parameter should be formatted in this fashion:
SELECT 1 FROM (tableName) WHERE (columnName) = (valueToFind)
Keyword arguements:
query -- The query that checks to see if a particular row exists (as described above).
"""
try:
cur.execute( query, "" )
return cur.fetchone() is not None
except(Exception, psycopg2.DatabaseError ) as error:
print (error)
print( "Offending Query " + query )
def storeContactDetails(self, commit):
"""
This Function stores a new set of contact details into the contact_details table.
It first checks to see if the contact_details_id variable has been obtained for the class instance, if it has not, it's retrieved from the contact_details_type_lkp table.
if that contact_details_type doesn't exist yet, its inserted into the contact_details_type_lkp table.
It then stores the details into the contact_details table.
:param self: Contact Details Instance.
:param commit: If True, then the insert is instantly commited. Otherwise no commit is done.
...
:return: True if the insert succeeds, False otherwise.
"""
try:
#First, check to see if the contact_details_type has already been stored inside the contact_details_type_lkp table
if self.contact_details_id is None:
if not checkRowExists(
f"SELECT 1 FROM contact_details_type_lkp WHERE UPPER( description ) = UPPER( '{self.contact_details_type}' ) "
):
self.contact_details_id = returnNextSerialID(
'contact_details_type_lkp', 'contact_details_type')
#If the contact_details_type hasn't been stored...
contact_details_type_insert_statement = f"""INSERT INTO contact_details_type_lkp( description )
VALUES( '{self.contact_details_type}' ) """
cur.execute(contact_details_type_insert_statement, "")
else:
#Get the matching contact_details_id from the contact_details_type_lkp table.
cur.execute(
f"SELECT contact_details_type FROM contact_details_type_lkp WHERE description = '{self.contact_details_type}'",
"")
row = cur.fetchone()
self.contact_details_id = row[0]
#Once we've gotten the contact_details_id....
cur.execute(
f""" INSERT INTO contact_details( contact_details_type, object_id, object_type, details, entered_when )
VALUES( {self.contact_details_id}, {self.object_id}, {self.object_type}, '{self.details}', current_timestamp ) """,
"")
if commit:
conn.commit()
return True
except (Exception, psycopg2.DatabaseError) as error:
print("Error in INSERTING Contact Details Type " +
str(self.contact_details_type) + " for Object with ID " +
str(self.object_id) + " with Object Type " +
str(self.object_type) + "\n" + "Error: " + error)
return False
def requestLimitChange(client):
cur.execute(
f"SELECT status FROM limit_increase_request ORDER BY request_id DESC"
) #
status = cur.fetchone()[0]
print(status, 'type:', type(status))
checkStatusRequest(client, status)
if status != "análise":
new_request = input(
'\nDeseja solicitar nova mudança de limite? (S/N): ').upper()
if new_request == 'S':
checkStatusRequest(client, None)
getOption(client)
def getRequestByClient(client):
cur.execute(
f"SELECT * FROM limit_increase_request WHERE client_id = {client.getId()}"
)
request = cur.fetchone()
if request is not None:
dictionary = returnRequestDictionary(request)
request = Request(dictionary['id'], dictionary['client_id'],
dictionary['admin_id'], dictionary['new_income'],
dictionary['new_monthly_limit'],
dictionary['new_available_credit'],
dictionary['status'])
return request
return -1
def getKeyValue( key ):
"""
This Function retrieves the keyvalue of a particular keyword inside the keytable table.
After retrieving the keyvalue, it then incremenets that keyvalue (by the keyword's incval).
Keyword arguements:
key -- Keyword Name.
"""
try:
query = f"SELECT k.key_value FROM keytable k WHERE k.key_name = '{key}'"
cur.execute( query, "")
row = cur.fetchone()
# After retrieving the ID, update it according it to the value of k.inc_val for that row.
update_query = f"UPDATE keytable SET key_value = key_value + incval WHERE key_name = '{key}'"
cur.execute( update_query, "")
return row[0]
except(Exception, psycopg2.DatabaseError) as error:
print(error)
def getAdmin(user, password):
cur.execute(f"SELECT * FROM administrator WHERE name = '{user}';")
data = cur.fetchone()
if data is not None:
dictionary = returnAdminDictionary(data)
salt = bytes(dictionary['salt'])
key = bytes(dictionary['key'])
new_key = hashPassword(password, salt)
if new_key == key:
admin = Admin(dictionary['id'], dictionary['name'], salt, key)
return admin
return -1
def getClientByCreditCard():
credit_card = input('\nNúmero do cartão: ')
cur.execute(f"SELECT * FROM client WHERE credit_card = '{credit_card}';")
data = cur.fetchone()
if data is not None:
dictionary = returnClientDictionary(data)
client = Client(dictionary['id'], dictionary['name'],
dictionary['cpf'], dictionary['phone'],
dictionary['income'], dictionary['credit_card'],
dictionary['monthly_limit'],
dictionary['available_credit'], dictionary['salt'],
dictionary['key'])
return client
return -1
def returnSuburbInformation(self):
"""
This Function information about the suburb represented by self.suburb_id.
This returns both the state and postcode of the suburb in a tuple.
Index[0] = Postcode
Index[1] = State
Keyword arguements:
self -- Keyword Name.
"""
try:
cur.execute(
f"SELECT s.postcode, s.state FROM suburbs s WHERE s.suburb_id = {self.suburb_id}",
"")
result = cur.fetchone()
suburb_tuple = (result[0], result[1])
return suburb_tuple
except (Exception, psycopg2.DatabaseError) as error:
print(error)
def returnNextSerialID( tableName, serialColumn ):
"""
Function returns the next id for a serial type column inside the database. (1 is returned if there are no rows inside the table)
Keyword arguements:
tableName -- Name of table where the serialColumn resides.
serialColumn -- Name of the Serial Column.
"""
try:
#To avoid getting an error, we need to check to see if there are any rows in the table.
serial_search_query = f"""SELECT MAX({serialColumn}) FROM {tableName}"""
cur.execute( serial_search_query, "" )
row = cur.fetchone()
if row[0] != None:
return row[0] + 1
else:
return 1
except(Exception, psycopg2.DatabaseError) as error:
print("Offending Query: " + serial_search_query)
print(error)
def getClient(credit_card, password):
cur.execute(f"SELECT * FROM client WHERE credit_card = '{credit_card}';")
data = cur.fetchone()
if data is not None:
dictionary = returnClientDictionary(data)
salt = dictionary['salt']
key = dictionary['key']
new_key = hashPassword(password, salt)
if new_key == key:
client = Client(dictionary['id'], dictionary['name'],
dictionary['cpf'], dictionary['phone'],
dictionary['income'], dictionary['credit_card'],
dictionary['monthly_limit'],
dictionary['available_credit'], salt, key)
return client
return -1
def storeFullListing(listing_id):
try:
#Get thje listingObject via the getListing Function
listingObject = getListing(listing_id)
#Once we've obtained the listingObject, store it.
new_listing_id = StoreListings(listingObject)
DB_Agent_ID = []
#If the listing is successfully stored...
#Check to see if it was advertised by an agency, and if it was, check to see if that agency and its agents have already been stored.
if 'advertiserIdentifiers' in listingObject and listingObject[
'advertiserIdentifiers']['advertiserType'] == 'agency':
new_agency = False
#Get the Agents who organized the listing and place it into a seperate array.
agent_listings_ids = listingObject['advertiserIdentifiers'].get(
'contactIds')
advertiserObject = listingObject['advertiserIdentifiers']
if not checkRowExists(
f"SELECT 1 FROM agencies WHERE domain_agency_id = {advertiserObject['advertiserId']} "
):
#Create a new Agency Object here.
agencyObject = getAgency(advertiserObject['advertiserId'])
#Seperate parts of the agency object into several different dictionaries to make life easier.
agencyProfile = agencyObject['profile']
listingAgency = Agency(
agencyProfile.get('agencyBanner'),
agencyProfile.get('agencyWebsite'),
agencyProfile.get('agencyLogoStandard'),
agencyProfile['mapLongitude'],
agencyProfile['mapLatitude'],
agencyProfile['agencyDescription'],
agencyProfile['numberForSale'],
agencyProfile['numberForRent'], agencyObject['name'],
agencyObject['id'],
agencyObject['details']['principalName'], agencyObject,
agencyObject['contactDetails'],
agencyObject['details']['streetAddress1'],
agencyObject['details']['streetAddress2'],
agencyObject['details']['suburb'], None, None, None,
agencyObject['details']['state'],
agencyObject['details']['postcode'])
new_agency_id = listingAgency.storeAgency()
if new_agency_id is None:
raise RuntimeError(
"Error in Function storeAgency for Agency " +
agencyObject['name'])
if 'agents' in agencyObject:
for agent in agencyObject['agents']:
#Check to see if the Agent has already been stored.
if not checkRowExists(
f"SELECT 1 FROM agents WHERE domain_agent_id = {agent['id']}"
):
agentObject = Agent(agent['email'],
agent['firstName'],
agent['lastName'],
agent.get('profileText'),
agent.get('mugshot_url'),
agent['id'], new_agency_id,
agent.get('facebook_url'),
agent.get('twitter_url'),
agent.get('phone'),
agent.get('photo'))
new_agent_id = agentObject.storeAgent(False)
if new_agent_id is None:
raise RuntimeError(
"Error in Function storeAgent for Agent " +
agent['firstName'] + " " +
agent['lastName'])
#We only need to do this if there was Agency that created this listing.
if agent_listings_ids is not None and agent[
'id'] in agent_listings_ids:
DB_Agent_ID.append(new_agent_id)
elif agent_listings_ids is not None and agent[
'id'] in agent_listings_ids:
#Get the ID of the Agent and append it to the DB_Agent_ID Contact
cur.execute(
""" SELECT a.agent_id
FROM agents a
WHERE a.domain_agent_id = %s
AND a.cnewrev IS NULL """,
(agent['id']))
agent_id = cur.fetchone()
DB_Agent_ID.append(agent_id)
new_agency = True
#If we are here, that means that the listing was advertised by an Agency.
if not new_agency:
#If we didn't save a new_agency, we need to get the agency_id/agent_ids from the database/
cur.execute(
""" SELECT a.agency_id
FROM agencies a
WHERE a.domain_agency_id = %s
AND a.cnewrev IS NULL """,
(advertiserObject['advertiserId'], ))
new_agency_id = cur.fetchone()[0]
if agent_listings_ids is not None:
for agent in agent_listings_ids:
#Agency acquired, now we need to copy the query from before and get the agentids
cur.execute(
""" SELECT a.agent_id
FROM agents a
WHERE a.domain_agent_id = %s """,
(agent, ))
holder_value = cur.fetchone()
if holder_value is not None:
agent_id = holder_value[0]
else:
#We need to create a new Agent in that case.
new_agent_object = getAgent(agent)
if new_agent_object is not None:
newAgentObject = Agent(
new_agent_object['email'],
new_agent_object['firstName'],
new_agent_object['lastName'],
new_agent_object.get('profileText'),
new_agent_object.get('mugshot_url'),
new_agent_object['agencyId'],
new_agency_id,
new_agent_object.get('facebook_url'),
new_agent_object.get('twitter_url'),
new_agent_object.get('phone'),
new_agent_object.get('photo'))
agent_id = newAgentObject.storeAgent(False)
else:
raise Exception(
"Unable to get New Agent Data for Agent " +
str(agent))
DB_Agent_ID.append(agent_id)
if DB_Agent_ID is None:
cur.execute(
""" INSERT INTO agent_listings( agency_id, listings_id, entered_when )
VALUES( %s, %s, current_timestamp )""",
(new_agency_id, new_listing_id))
else:
for agent_id in DB_Agent_ID:
cur.execute(
""" INSERT INTO agent_listings( agency_id, agent_id, listings_id, entered_when )
VALUES( %s, %s, %s, current_timestamp ) """,
(new_agency_id, agent_id, new_listing_id))
#Once the advertiser has been saved, you want to store any linked property details inside the propery table.
listing_address = listingObject['addressParts']['displayAddress']
#We don't want to store the address against the listing. Instead, we want to store it to the property that is linked to the listing.
propertyObject = getPropertyFromListing(listing_address)
if len(propertyObject) != 0:
#We need to get the first property object when this returns...
propertyStore = Property.initFromObject(
getPropertyInfo(propertyObject[0]['id']))
new_property_id = propertyStore.saveProperty(False)
cur.execute(
""" INSERT INTO properties_listings( property_id, listings_id, entered_when )
VALUES( %s, %s, current_timestamp ) """,
(new_property_id, new_listing_id))
return True
except (Exception, psycopg2.DatabaseError) as error:
print(error)
print("Original Exception Message" +
(error.__cause__ or error.__context__))
return False
def checkRequests(admin):
print('\nAnalise o pedido de mudança no limite de crédito dos clientes!')
cur.execute(
"SELECT * FROM limit_increase_request WHERE status = 'análise';")
requests = cur.fetchall()
if len(requests) > 0:
print('\nCartão | Cliente')
for request in requests:
cur.execute(
f"SELECT name, credit_card FROM client WHERE client_id = {request[1]}"
)
client_data = cur.fetchone()
print(f'{client_data[1]} {client_data[0]}')
verify = input(
'\nVocê deseja análisar a mudança de limite de crédito de algum cliente? (S/N): '
).upper()
if verify == 'S':
client = getClientByCreditCard()
if client != -1:
request = getRequestByClient(client)
if request != -1:
print(f'\nDados da Requisição do {client.getName()}:')
print(f'\nRenda Atual: R${client.getIncome():.2f}')
print(
f'Limite de Crédito Atual: R${client.getMonthlyLimit():.2f}'
)
print(f'\nNova Renda: R${request.getNewIncome():.2f}')
print(
f'Novo Limite de Crédito: R${request.getNewMonthlyLimit():.2f}'
)
approval = input(
f'\nVocê deseja aprovar essa requisição feita pelo cliente, {client.getName()}? (S/N): '
).upper()
if approval == 'S':
status = 'aprovado'
cur.execute(
f"""UPDATE client SET income = {request.getNewIncome()}, monthly_limit = {request.getNewMonthlyLimit()},
available_credit = {request.getNewAvailableCredit()} WHERE client_id = {client.getId()};"""
)
print(
'\nRequisição Aprovada! Novo limite de crédito definido.'
)
else:
status = 'negado'
print('\nRequisição Negada!')
cur.execute(
f"UPDATE limit_increase_request SET admin_id = {admin.getId()}, status = '{status}' WHERE request_id = {request.getId()};"
)
print('real status:', status)
conn.commit()
else:
print('\nNenhum cliente encontrado! Tente novamente.')
else:
print('\nNão há nenhuma requisição no momento!')
getOption(admin)
