152 lines
4.1 KiB
Python
152 lines
4.1 KiB
Python
|
|
import connection
|
|
|
|
def getConn():
|
|
c = connection.conn()
|
|
return c
|
|
|
|
def closeConnection(connection,cursor):
|
|
if connection:
|
|
cursor.close()
|
|
connection.close()
|
|
# print("POstgres connection closed successfully")
|
|
# print("Connection was closed before")
|
|
|
|
|
|
|
|
|
|
# Insert a filter if it does not already exist.
|
|
def insertFilter(tid, sort, sort_value ):
|
|
if str(sort_value).strip().startswith("_"):
|
|
sort_value = "-" + (str(sort_value).strip())[1:]
|
|
if getFilterByParam(tid, sort, sort_value):
|
|
return False
|
|
|
|
connection = getConn()
|
|
cursor = connection.cursor()
|
|
|
|
postgres_insert_query = """ INSERT INTO filters (tid, sort, sort_value) VALUES (%s,%s,%s)"""
|
|
record_to_insert = (tid, sort, sort_value)
|
|
cursor.execute(postgres_insert_query, record_to_insert)
|
|
|
|
connection.commit()
|
|
count = cursor.rowcount
|
|
if count <1:
|
|
closeConnection(connection,cursor)
|
|
return False
|
|
print(count, "Record inserted successfully into sort table")
|
|
closeConnection(connection,cursor)
|
|
return True
|
|
|
|
def exists_area_for_user(tid):
|
|
connection = getConn()
|
|
cursor = connection.cursor()
|
|
|
|
postgreSQL_select_Query = "select * from filters where tid =" + str(tid) + " AND sort = 'A'"
|
|
|
|
cursor.execute(postgreSQL_select_Query)
|
|
publisher_records = cursor.fetchall()
|
|
|
|
closeConnection(connection,cursor)
|
|
return cursor.rowcount > 0
|
|
|
|
def exists_sub_area_for_user(tid):
|
|
connection = getConn()
|
|
cursor = connection.cursor()
|
|
|
|
postgreSQL_select_Query = "select * from filters where tid =" + str(tid) + " AND sort = 'S'"
|
|
|
|
cursor.execute(postgreSQL_select_Query)
|
|
publisher_records = cursor.fetchall()
|
|
|
|
closeConnection(connection,cursor)
|
|
|
|
return cursor.rowcount > 0
|
|
|
|
|
|
|
|
def get_area_from_tid(tid):
|
|
connection = getConn()
|
|
cursor = connection.cursor()
|
|
|
|
postgreSQL_select_Query = "select sort_value from filters where sort = 'A' and tid =" + str(tid)
|
|
|
|
cursor.execute(postgreSQL_select_Query)
|
|
publisher_records = cursor.fetchall()
|
|
|
|
closeConnection(connection,cursor)
|
|
return publisher_records
|
|
|
|
# Returns all the filters inserted by a user
|
|
def getFilters(tid):
|
|
connection = getConn()
|
|
cursor = connection.cursor()
|
|
|
|
postgreSQL_select_Query = "select * from filters where tid =" + str(tid) + " order by sort"
|
|
|
|
cursor.execute(postgreSQL_select_Query)
|
|
publisher_records = cursor.fetchall()
|
|
|
|
closeConnection(connection,cursor)
|
|
return publisher_records
|
|
|
|
# Returns all the filters inserted by a user
|
|
def getFilterByParam(tid,sort, sort_value):
|
|
connection = getConn()
|
|
cursor = connection.cursor()
|
|
|
|
postgreSQL_select_Query = """select * from filters where tid = %s and sort= %s and sort_value = %s"""
|
|
record_to_insert = (tid, sort, sort_value)
|
|
|
|
cursor.execute(postgreSQL_select_Query,record_to_insert)
|
|
publisher_records = cursor.fetchall()
|
|
filter_exists = cursor.rowcount > 0
|
|
closeConnection(connection,cursor)
|
|
return filter_exists
|
|
|
|
# Returns all the filters inserted by a user
|
|
def getAllFilters():
|
|
connection = getConn()
|
|
cursor = connection.cursor()
|
|
|
|
postgreSQL_select_Query = "select * from filters "
|
|
|
|
cursor.execute(postgreSQL_select_Query)
|
|
publisher_records = cursor.fetchall()
|
|
|
|
for row in publisher_records:
|
|
print( row, "\n")
|
|
|
|
closeConnection(connection,cursor)
|
|
return publisher_records
|
|
|
|
|
|
|
|
# Delete a single filter
|
|
def deleteFilter(telid, id):
|
|
connection = getConn()
|
|
cursor = connection.cursor()
|
|
|
|
# Delete first all the ticket
|
|
sql_delete_query = """Delete from tickets where fid = %s"""
|
|
cursor.execute(sql_delete_query, (id,))
|
|
connection.commit()
|
|
|
|
sql_delete_query = """Delete from filters where tid= %s and fid = %s"""
|
|
cursor.execute(sql_delete_query, (telid,id))
|
|
connection.commit()
|
|
count = cursor.rowcount
|
|
print(count, "Record deleted successfully ")
|
|
|
|
closeConnection(connection,cursor)
|
|
return count > 0
|
|
|
|
|
|
#insertFilter(965744443, 'competenza','itech')
|
|
#getUsers()
|
|
#isUserRegisterd(222222)
|
|
|
|
|
|
#getFilters(965744443)
|
|
#deleteFilter(8)
|