def selectQuery(table, **options):
reqStart = datetime.datetime.now()
print("*** Function selectQuery was called on '%s'" % reqStart)
user_columns = options.get('columns')
if user_columns:
columns = ','.join('{column}'.format(column=column) for column in user_columns)
else:
columns = "*"
like_str, equal_str, values = inputToQueryString(options.get('like'), options.get('equal'))
where = joinClauses(like_str, equal_str, options.get('where','1=1'))
limit = options.get('limit','100')
order = options.get('order', 'oid desc')
offset = options.get('offset','0')
hits = options.get('hits', False)
sum = options.get('sum',False)
#query = "SELECT {columns} FROM {table} WHERE {where} ORDER BY {order} LIMIT {limit} OFFSET {offset}".format(columns=columns, table=table, where=where, limit=limit, order=order, offset=offset)
query = "SELECT {columns} FROM {table} WHERE {where} LIMIT {limit} OFFSET {offset}".format(columns=columns, table=table, where=where, limit=limit, offset=offset)
countQuery = "SELECT count(1) from (SELECT {columns} FROM {table} WHERE {where}) as count".format(columns=columns, table=table, where=where)
print query
connection = pgsql.PGSql()
connection.connect()
data = False
if not hits:
print("SQL SELECT START: %s" % datetime.datetime.now())
data = connection.query(query, False)
print("SQL SELECT END: %s" % datetime.datetime.now())
if sum:
print("SQL COUNT START: %s" % datetime.datetime.now())
total = connection.query(countQuery,False)
print("SQL COUNT END: %s" % datetime.datetime.now())
connection.close()
reqEnd = datetime.datetime.now()
print('RESPONSE TIME: %s FOR DATA SQL QUERIES' % (str(reqEnd - reqStart)))
if data and sum:
return data,total
elif data:
return data
elif hits:
return total
else:
return False
def __init__(self):
self.personclass = Person.Person()
self.connection = pgsql.PGSql()
def __init__(self):
self.connection = pgsql.PGSql()
self.personclass = Person.Person()
self.apiBaseUrl = 'http://' + cfg.host + ':' + cfg.apiport + '/api'
def get(self):
connection = pgsql.PGSql()
if request.args:
for argument in request.args:
### ADDED GROUP BY TO AVOID DUPLICATES ###
if argument == 'name':
sqlSelect = ("SELECT oib OIB, establishment_name MUN_NAME, naziv MUNIC, concat(street,' ',street_number) ADDR, cide_establishment.id ESTABID, count(id_coordinated_inspection) CICOUNT, company_name FIRMA "
"FROM cide_establishment "
"LEFT JOIN cide_coordinated_inspection ON cide_coordinated_inspection.id_establishment = cide_establishment.id "
"LEFT JOIN rpot_postanskiured ON rpot_postanskiured.id = cide_establishment.city_id "
"WHERE establishment_name LIKE '%s' GROUP BY OIB,MUN_NAME,MUNIC,ADDR,ESTABID ORDER BY max(last_update) DESC NULLS LAST" % ((request.args.get(argument)).encode("utf-8")).replace('*', '%'))
connection.connect()
data = connection.query(sqlSelect)
#data = connection.query(sql=sqlSelect)
connection.close()
continue
elif argument == 'oib':
sqlSelect = ("SELECT oib OIB, establishment_name MUN_NAME, naziv MUNIC, concat(street,' ',street_number) ADDR, cide_establishment.id ESTABID, count(id_coordinated_inspection) CICOUNT, company_name FIRMA "
"FROM cide_establishment "
"LEFT JOIN cide_coordinated_inspection ON cide_coordinated_inspection.id_establishment = cide_establishment.id "
"LEFT JOIN rpot_postanskiured ON rpot_postanskiured.id = cide_establishment.city_id "
"WHERE oib LIKE '%s' GROUP BY OIB,MUN_NAME,MUNIC,ADDR,ESTABID ORDER BY max(last_update) DESC NULLS LAST" % ((request.args.get(argument)).encode("utf-8")).replace('*', '%'))
connection.connect()
data = connection.query(sql=sqlSelect)
connection.close()
continue
else:
return Response('{"message":"parameter %s not available"}' % str(argument),mimetype='application/json')
else:
#sqlSelect = ("select oib OIB,establishment_name MUN_NAME,city_id MUNIC,concat(street,' ',street_number) ADDR, id ESTABID from cide_establishment GROUP BY OIB,MUN_NAME,MUNIC,ADDR,ESTABID")
if 'ROLE_CIDE_ADMIN' in g.user[1] or 'ROLE_CIDE_COORDINATOR' in g.user[1]:
sqlSelect = ("select oib OIB,establishment_name MUN_NAME,naziv MUNIC,concat(street,' ',street_number) ADDR,cide_establishment.id ESTABID,count(id_coordinated_inspection) CICOUNT, company_name FIRMA "
"FROM cide_establishment "
"LEFT JOIN cide_coordinated_inspection ON cide_coordinated_inspection.id_establishment = cide_establishment.id "
"LEFT JOIN rpot_postanskiured ON rpot_postanskiured.id = cide_establishment.city_id "
"GROUP BY OIB,MUN_NAME,MUNIC,ADDR,ESTABID "
"ORDER BY max(last_update) DESC NULLS LAST")
else:
idpersonrole = self.personclass.getPersonRoleId(g.user)
id_inspection_type_select = "select id_inspection_type from cide_person_role where id_person_role = %s" % idpersonrole[2][0][0]
connection.connect()
id_inspection_type = connection.query(id_inspection_type_select)
connection.close()
sqlSelect = ("select oib OIB,establishment_name MUN_NAME,naziv MUNIC,concat(street,' ',street_number) ADDR,cide_establishment.id ESTABID,count(cide_coordinated_inspection.id_coordinated_inspection) CICOUNT, company_name FIRMA "
"FROM cide_establishment "
"LEFT JOIN cide_coordinated_inspection ON cide_coordinated_inspection.id_establishment = cide_establishment.id "
"LEFT JOIN rpot_postanskiured ON rpot_postanskiured.id = cide_establishment.city_id "
"LEFT JOIN cide_specific_inspection ON cide_coordinated_inspection.id_coordinated_inspection = cide_specific_inspection.id_coordinated_inspection "
"WHERE cide_specific_inspection.id_person_role in (select id_person_role from cide_person_role where id_inspection_type = %s ) "
"GROUP BY OIB,MUN_NAME,MUNIC,ADDR,ESTABID "
"ORDER BY max(cide_coordinated_inspection.last_update) DESC NULLS LAST" % id_inspection_type[0][0])
print sqlSelect
connection.connect()
data = connection.query(sqlSelect)
connection.close()
if data:
returnDataList = []
#returnDataList.append({"count": connection.numresult})
for row in data:
returnData = {}
returnData['oib'] = (row['oib'])
returnData['establishment_name'] = (row['mun_name'])
returnData['establishment_municipality'] = (row['munic'])
returnData['establishment_address'] = (row['addr'])
returnData['id'] = (hashids.encode(row['estabid']))
returnData['ci_count'] = row['cicount']
returnData['establishment_operator'] = row['firma']
returnDataList.append(returnData)
return Response(json.dumps(returnDataList,ensure_ascii=False), mimetype='application/json')
def __init__(self):
self.connection = pgsql.PGSql()