def installMaker():
with conn.cursor() as cursor:
installCurs = conn.cursor()
query = "SELECT row_to_json(fc) FROM (SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) as features FROM (SELECT 'Feature' as type, ST_AsGeoJSON(lg.the_geom)::json As geometry, row_to_json((SELECT l FROM (SELECT dp_otype, dp_ctype, dp_subtype, gid) as l )) as properties FROM installationjobs as lg) as f ) as fc"
installCurs.execute(query)
inVar = [w[0] for w in installCurs.fetchall()]
return json.dumps(inVar[0])
def installMaker():
with conn.cursor() as cursor:
installCurs = conn.cursor()
query = "SELECT row_to_json(fc) FROM (SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) as features FROM (SELECT 'Feature' as type, ST_AsGeoJSON(lg.the_geom)::json As geometry, row_to_json((SELECT l FROM (SELECT dp_otype, dp_ctype, dp_subtype, gid) as l )) as properties FROM installationjobs as lg) as f ) as fc"
installCurs.execute(query)
inVar = [w[0] for w in installCurs.fetchall()]
return json.dumps(inVar[0])
def calculate_Order_forDriver(username, status):
cursor = conn.cursor()
cursor.execute("SELECT id FROM driver WHERE username = '******'".format(username))
id = cursor.fetchone()
if status == 'work':
cursor = conn.cursor()
cursor.execute("SELECT count(id_order) FROM orders WHERE is_delivery = false and id_driver = {}".format(id[0]))
answer = cursor.fetchone()
else:
cursor = conn.cursor()
cursor.execute("SELECT count(id_order) FROM orders WHERE is_delivery = true and id_driver = {}".format(id[0]))
answer = cursor.fetchone()
return answer[0]
def login():
if current_user.is_authenticated:
return redirect(url_for('index'))
login_form = LoginForm()
print(login_form.username.data)
print('passw = {}'.format(login_form.password.data))
if login_form.validate_on_submit():
print('BBBBB')
cursor = conn.cursor()
cursor.execute("SELECT * FROM driver ")
driverData = cursor.fetchall()
username = login_form.username.data
password = login_form.password.data
for data in driverData:
if data[2] == username and data[3] == password:
user = User(data[0])
login_user(user, remember=login_form.remember_me.data)
next_page = request.args.get('next')
print(user.isadmin)
if user.isadmin:
return redirect(url_for('admin_panel', username=user.username))
if not next_page or url_parse(next_page).netloc != '':
next_page = url_for('index')
return redirect(next_page)
return render_template('login.html', title='Sign In', form=login_form)
def fromStrToID(driverStr, clientStr, productStr, weight, dateDelStr, statusStr, cityA_Str, cityB_Str):
list_id = {}
cursor = conn.cursor()
cursor.execute("SELECT id FROM driver WHERE username = '******'".format(driverStr))
driver_id = cursor.fetchone()
list_id['driver_id'] = driver_id[0]
cursor.execute("SELECT id_client FROM client WHERE nameofclient = '{}'".format(clientStr))
client_id = cursor.fetchone()
list_id['client_id'] = client_id[0]
list_id['city_A'] = cityA_Str
list_id['city_B'] = cityB_Str
if (statusStr == 'Доставлен'):
list_id['status'] = True
elif (statusStr == 'Не доставлен'):
list_id['status'] = False
list_id['date_delivery'] = dateDelStr
list_id['weight'] = float(weight)
cursor.execute("SELECT id_product FROM product WHERE nameofproduct = '{}'".format(productStr))
product_id = cursor.fetchone()
if product_id == None:
cursor.execute("INSERT INTO product(id_product, nameofproduct) VALUES ((SELECT MAX(id_product) + 1 FROM product),'{name_product}')".format(name_product = productStr))
conn.commit()
cursor.execute("SELECT id_product FROM product WHERE nameofproduct = '{}'".format(productStr))
product_id = cursor.fetchone()
list_id['product_id'] = product_id[0]
return list_id
def handler_new_member(m):
bot.delete_message(m.chat.id,
m.message_id) #Удалить сообщение, содержащее команду
id = m.json
id = id['new_chat_members']
for a in id:
id = a['id']
name = a['first_name']
last = f'<a href="tg://user?id={id}">{name}</a>'
bot.send_sticker(
m.chat.id,
'CAACAgIAAxkBAAJFW16Tj7HCIcjx9fPTf3WYtEXLG4EJAAIDAAOF-3IqNguusCQT_gEYBA'
) #Отправить стикер
bot.send_message(m.chat.id,
"Добро пожаловать, " + last +
"\nНапиши /help, чтобы мной воспользоваться",
parse_mode="HTML")
bot.export_chat_invite_link(chat_id)
#######################################################
writes(m)
cursor = conn.cursor()
date = (datetime.datetime.now()).strftime("%Y %m %d")
id_ = m.from_user.id
cursor.execute(
f"UPDATE top_users SET date_add = '{date}' WHERE user_id = {m.from_user.id};"
)
conn.commit()
def left_chat_member(m):
try:
bot.delete_message(
m.chat.id,
m.message_id) #Удалить сообщение о том что пользователь вышел
bot.export_chat_invite_link(chat_id)
first = f'<a href="tg://user?id={m.from_user.id}">{m.from_user.first_name}</a>'
last = f'<a href="tg://user?id={m.left_chat_member.id}">{m.left_chat_member.first_name}</a>'
if m.from_user.id != m.left_chat_member.id:
bot.send_sticker(
m.chat.id,
'CAACAgIAAxkBAAJMe1-lM12m7DQqSelOfsAs3qzBZbY7AAKPEQACPLPFBzLcyrxNSGysHgQ'
) #Отправить стикер
result = first + " кикнул(a) " + last
bot.send_message(m.chat.id, result, parse_mode=('HTML'))
else:
bot.send_sticker(
m.chat.id,
'CAACAgIAAxkBAAJMe1-lM12m7DQqSelOfsAs3qzBZbY7AAKPEQACPLPFBzLcyrxNSGysHgQ'
) #Отправить стикер
bot.send_message(
m.chat.id, last + ' покинул(a) нас',
parse_mode=("HTML")) #Уведомляем, что пользователь вышел
cursor = conn.cursor()
userid = m.left_chat_member.id
cursor.execute("DELETE FROM top_users WHERE user_id = " + str(userid) +
";")
conn.commit()
except:
bot.send_message(user_id, "Я - псих. И устал от вас, прощайте.")
def validate_carnumber(self, carnumber):
print(current_user.isadmin)
if not current_user.carnumber and not current_user.isadmin:
cursor = conn.cursor()
cursor.execute("SELECT carnumber FROM car")
carnumberList = cursor.fetchall()
flagNumber = True
flagID = False
for number in carnumberList:
if carnumber.data == number[0]:
flagNumber = False
cursor.execute(
"SELECT id_car from car WHERE carnumber = '{}'".format(
carnumber.data))
bla = cursor.fetchone()
idCar = bla[0]
cursor.execute("SELECT carnumber FROM driver")
id_curnumberList = cursor.fetchall()
for id in id_curnumberList:
if idCar == id[0]:
flagID = True
str = None
if flagNumber:
str = 'Такого номера машины нет в базе. Обратитесь к администратору'
if flagID:
str = 'Эта машина принадлежит другому водителю, введите другой номер'
if str:
raise ValidationError('{}'.format(str))
def getBrokenNode(network_name,brokenNode):
with conn.cursor() as cursor:
#bnCurs = conn.cursor()
query = "SELECT row_to_json(fc) FROM (SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) as features FROM (SELECT 'Feature' as type, ST_AsGeoJSON(lg.the_geom)::json As geometry, row_to_json((SELECT l FROM (SELECT dp_otype, dp_ctype, dp_subtype, gid) as l )) as properties FROM {0} as lg WHERE gid = {1} ) as f ) as fc;".format(network_name.strip("\'"), brokenNode)
cursor.execute(query)
inVar = (cursor.fetchone()[0])
return json.dumps(inVar)
def add_a_molecule(self, mid):
name = molecules.get_name(conn, mid)
mol = Experiment.MoleculeMatch(name, mid, self.N)
cursor = conn.cursor()
for p in self.experiment_peaks:
if p.is_pending():
frequency = peaks.get_frequency(conn, p.pid)
script = "SELECT name, mid, pid, MIN(ABS(frequency - {freq})) FROM" \
" (SELECT molecules.name, molecules.mid, peaks.pid, peaks.frequency" \
" FROM peaks JOIN molecules " \
" ON peaks.mid=molecules.mid" \
" WHERE molecules.mid={mid} AND ABS(peaks.frequency - {freq}) <= {thresh})" \
"".format(mid=mid, freq=frequency, thresh=self.match_threshold)
try:
cursor.execute(script)
except Exception as e:
cursor.close()
raise
rows = cursor.fetchone()
if rows is not None and rows[0] is not None:
row = rows[0]
name = row[0]
mid = row[1]
pid = row[2]
distance = row[3]
match = Match(name, mid, pid, 1, p.pid, 1)
mol.add_match(match)
self.molecule_matches[mid] = mol
return mol
def menuCustomers(custList):
with conn.cursor() as cursor:
#menuCurs = conn.cursor()
query = "SELECT row_to_json(fc) FROM (SELECT * FROM customers) fc WHERE fc.gid IN {0};".format(str(custList))
cursor.execute(query)
inVar = [w[0] for w in cursor.fetchall()]
return json.dumps(inVar)
def allCustomers():
with conn.cursor() as cursor:
#menuCurs = conn.cursor()
query = "SELECT row_to_json(fc) FROM (SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) as features FROM (SELECT 'Feature' as type, ST_AsGeoJSON(ST_Centroid(lg.the_geom))::json As geometry, row_to_json((SELECT l FROM (SELECT gid, firstname, lastname, address, gas_id, water_id, heating_id, dp_otype) as l )) as properties FROM customers as lg) as f ) as fc"
cursor.execute(query)
inVar = [w[0] for w in cursor.fetchall()][0]
return json.dumps(inVar)
def getBrokenCust(network_name,nodeSet):
with conn.cursor() as cursor:
#cursor = conn.cursor()
query = "SELECT row_to_json(fc) FROM (SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) as features FROM (SELECT 'Feature' as type, ST_AsGeoJSON(lg.the_geom)::json As geometry, row_to_json((SELECT l FROM (SELECT dp_otype, dp_ctype, dp_subtype, gid, cust_id) as l )) as properties FROM {0} as lg WHERE gid in {1}) as f ) as fc;".format(network_name.strip("\'"), str(nodeSet).replace('{','(').replace('}',')'))
cursor.execute(query)
inVar = (cursor.fetchone()[0])
return json.dumps(inVar)
def nodeGetter(filename):
with conn.cursor() as cursor:
#nodeCurs = conn.cursor()
query = "SELECT row_to_json(fc) FROM (SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) as features FROM (SELECT 'Feature' as type, ST_AsGeoJSON(lg.the_geom)::json As geometry, row_to_json((SELECT l FROM (SELECT dp_otype, dp_ctype, dp_subtype, gid) as l )) as properties FROM {0} as lg ) as f ) as fc;".format(filename)
cursor.execute(query)
inVar = (cursor.fetchone()[0])
return json.dumps(inVar)
def get_candidates(self, threshold):
frequency = self.frequency
cursor = conn.cursor()
# SQLite Script, that returns name, mid, and pid of matched known molecules in database
# that are within the threshold of the specified frequency and are
# ordered by the closeness of the frequencies to the specified frequency
# example = "SELECT molecules.name, molecules.mid, peaks.pid, ABS(peaks.frequency - {freq})" \
# " FROM peaks JOIN molecules" \
# " WHERE molecules.mid=peaks.mid AND molecules.category='known' AND ABS(peaks.frequency - {freq})<={t}" \
# " ORDER BY ABS(peaks.frequency - {freq} ) ASC".format(freq=frequency, t=threshold)
script = "SELECT name, mid, pid, MIN(ABS(frequency - {freq})) FROM" \
" (SELECT molecules.name, molecules.mid, peaks.pid, peaks.frequency" \
" FROM peaks JOIN molecules" \
" WHERE molecules.mid=peaks.mid AND molecules.category IN ('known', 'artifact') AND ABS(peaks.frequency - {freq})<={t}" \
" ORDER BY ABS(peaks.frequency - {freq} ) ASC)" \
" GROUP BY mid".format(freq=frequency, t=threshold)
try:
cursor.execute(script)
except Exception as e:
cursor.close()
raise
rows = cursor.fetchall()
return rows
def get_candidates(self, threshold):
frequency = self.frequency
cursor = conn.cursor()
# SQLite Script, that returns name, mid, and pid of matched known molecules in database
# that are within the threshold of the specified frequency and are
# ordered by the closeness of the frequencies to the specified frequency
# example = "SELECT molecules.name, molecules.mid, peaks.pid, ABS(peaks.frequency - {freq})" \
# " FROM peaks JOIN molecules" \
# " WHERE molecules.mid=peaks.mid AND molecules.category='known' AND ABS(peaks.frequency - {freq})<={t}" \
# " ORDER BY ABS(peaks.frequency - {freq} ) ASC".format(freq=frequency, t=threshold)
script = "SELECT name, mid, pid, MIN(ABS(frequency - {freq})) FROM" \
" (SELECT molecules.name, molecules.mid, peaks.pid, peaks.frequency" \
" FROM peaks JOIN molecules" \
" WHERE molecules.mid=peaks.mid AND molecules.category IN ('known', 'artifact') AND ABS(peaks.frequency - {freq})<={t}" \
" ORDER BY ABS(peaks.frequency - {freq} ) ASC)" \
" GROUP BY mid".format(freq=frequency, t=threshold)
try:
cursor.execute(script)
except Exception as e:
cursor.close()
raise
rows = cursor.fetchall()
return rows
def get_user_groupid_from_UG(usergroup_string):
cur = conn.cursor()
UGgrabbed = usergroup_string
cur.execute("select g.groups_id from grps g WHERE g.groups_name = '%s'" %
UGgrabbed)
account = cur.fetchall()
return account
def set_(m):
bot.delete_message(m.chat.id, m.message_id)
if m.from_user.id == 905933085:
info = m.text[4:].split()
cursor = conn.cursor()
cursor.execute("UPDATE top_users set message = " + info[1] +
" where user_id = " + str(info[0]))
def get_user_id_from_email(email):
cur = conn.cursor()
emailgrabbed = email
cur.execute("select u.users_id from users u WHERE u.users_email = '%s'" %
emailgrabbed)
account = cur.fetchall()
return account
def nodeBreaker(network_name, node_id):
with conn.cursor() as cursor:
arc_tbl = "\'" + tables[network_name]['arcs'] + "\'"
node_tbl = "\'" + tables[network_name]['nodes'] + "\'"
conn_tbl = "\'" + tables[network_name]['conn'] + "\'"
#brokenCurs = conn.cursor()
query = "SELECT * FROM nodeMaker({0}, {1}, {2}) WHERE nodemaker IS NOT NULL".format(
node_id, arc_tbl, tables[network_name]['station_id'])
try:
cursor.execute(query)
brokenNodes = [w[0][0] for w in cursor.fetchall()]
nodeSet = set()
for i in brokenNodes:
nodeSet = nodeSet.union(bfs(arc_tbl, int(node_id), i))
if nodeSet:
return json.dumps({
'Arc':
json.loads(getBrokenArc(arc_tbl, nodeSet)),
'Node':
json.loads(getBrokenNode(node_tbl, node_id)),
'Conn':
json.loads(getBrokenCust(conn_tbl, nodeSet))
})
else:
return "NULL"
except:
conn.rollback()
print(query)
return "NULL"
def add_a_molecule(self, mid):
name = molecules.get_name(conn, mid)
mol = Experiment.MoleculeMatch(name, mid, self.N)
cursor = conn.cursor()
for p in self.experiment_peaks:
if p.is_pending():
frequency = peaks.get_frequency(conn, p.pid)
script = "SELECT name, mid, pid, MIN(ABS(frequency - {freq})) FROM" \
" (SELECT molecules.name, molecules.mid, peaks.pid, peaks.frequency" \
" FROM peaks JOIN molecules " \
" ON peaks.mid=molecules.mid" \
" WHERE molecules.mid={mid} AND ABS(peaks.frequency - {freq}) <= {thresh})" \
"".format(mid=mid, freq=frequency, thresh=self.match_threshold)
try:
cursor.execute(script)
except Exception as e:
cursor.close()
raise
rows = cursor.fetchone()
if rows is not None and rows[0] is not None:
row = rows[0]
name = row[0]
mid = row[1]
pid = row[2]
distance = row[3]
match = Match(name, mid, pid, 1, p.pid, 1)
mol.add_match(match)
self.molecule_matches[mid] = mol
return mol
def allCustomers():
with conn.cursor() as cursor:
#menuCurs = conn.cursor()
query = "SELECT row_to_json(fc) FROM (SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) as features FROM (SELECT 'Feature' as type, ST_AsGeoJSON(ST_Centroid(lg.the_geom))::json As geometry, row_to_json((SELECT l FROM (SELECT gid, firstname, lastname, address, gas_id, water_id, heating_id, dp_otype) as l )) as properties FROM customers as lg) as f ) as fc"
cursor.execute(query)
inVar = [w[0] for w in cursor.fetchall()][0]
return json.dumps(inVar)
def get_groups():
cur = conn.cursor()
cur.execute("""
select groups_name from grps
""")
return cur.fetchall()
def createChoicesFor_client():
cursor = conn.cursor()
cursor.execute("SELECT nameofclient FROM client")
clients = cursor.fetchall()
clientsList = []
for client in clients:
clientsList.append(client[0])
return clientsList
def menuCustomers(custList):
with conn.cursor() as cursor:
#menuCurs = conn.cursor()
query = "SELECT row_to_json(fc) FROM (SELECT * FROM customers) fc WHERE fc.gid IN {0};".format(
str(custList))
cursor.execute(query)
inVar = [w[0] for w in cursor.fetchall()]
return json.dumps(inVar)
def validate_username(self, username):
cursor = conn.cursor()
cursor.execute("SELECT username FROM driver ")
loginList = cursor.fetchall()
for login in loginList:
if username.data == login[0]:
raise ValidationError(
'Этот username уже занят, попробуйте другой')
def nodeGetter(filename):
with conn.cursor() as cursor:
#nodeCurs = conn.cursor()
query = "SELECT row_to_json(fc) FROM (SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) as features FROM (SELECT 'Feature' as type, ST_AsGeoJSON(lg.the_geom)::json As geometry, row_to_json((SELECT l FROM (SELECT dp_otype, dp_ctype, dp_subtype, gid) as l )) as properties FROM {0} as lg ) as f ) as fc;".format(
filename)
cursor.execute(query)
inVar = (cursor.fetchone()[0])
return json.dumps(inVar)
def getBrokenNode(network_name, brokenNode):
with conn.cursor() as cursor:
#bnCurs = conn.cursor()
query = "SELECT row_to_json(fc) FROM (SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) as features FROM (SELECT 'Feature' as type, ST_AsGeoJSON(lg.the_geom)::json As geometry, row_to_json((SELECT l FROM (SELECT dp_otype, dp_ctype, dp_subtype, gid) as l )) as properties FROM {0} as lg WHERE gid = {1} ) as f ) as fc;".format(
network_name.strip("\'"), brokenNode)
cursor.execute(query)
inVar = (cursor.fetchone()[0])
return json.dumps(inVar)
def get_technology_partner_key(self, technology_partner):
cursor = conn.cursor()
cursor.execute(
"select id from panoptic.technology_partner where technology_partner = '{}';"
.format(technology_partner))
tech_partner = cursor.fetchone()
cursor.close()
return tech_partner[0] if tech_partner else 'NULL'
def getBrokenCust(network_name, nodeSet):
with conn.cursor() as cursor:
#cursor = conn.cursor()
query = "SELECT row_to_json(fc) FROM (SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) as features FROM (SELECT 'Feature' as type, ST_AsGeoJSON(lg.the_geom)::json As geometry, row_to_json((SELECT l FROM (SELECT dp_otype, dp_ctype, dp_subtype, gid, cust_id) as l )) as properties FROM {0} as lg WHERE gid in {1}) as f ) as fc;".format(
network_name.strip("\'"),
str(nodeSet).replace('{', '(').replace('}', ')'))
cursor.execute(query)
inVar = (cursor.fetchone()[0])
return json.dumps(inVar)
def get_state_ids(self):
cursor = conn.cursor()
cursor.execute(
"select place__key from panoptic.frt_place_link where frt__key = {} "
.format(self.id))
result = cursor.fetchall()
cursor.close()
state_ids = set([row[0] for row in result])
return state_ids
def delete_all():
try:
cursor = conn.cursor()
cursor.execute("DELETE FROM contacts")
close_connection(cursor)
return True
except Exception:
return False
def delete_one(id):
try:
cursor = conn.cursor()
cursor.execute("DELETE FROM contacts WHERE id=%s", (id,))
close_connection(cursor)
return True
except Exception:
return False
def createChoicesFor_driver():
cursor = conn.cursor()
cursor.execute("SELECT username FROM driver")
drivers = cursor.fetchall()
driversList = []
for driver in drivers:
driversList.append(driver[0])
print('driverList = {}'.format(driversList))
return driversList
def update_orderData(addOrderForm):
cursor = conn.cursor()
list_id = fromStrToID(addOrderForm.driver.data,addOrderForm.client.data, addOrderForm.product.data, addOrderForm.weight.data, addOrderForm.date_delivery.data,addOrderForm.status.data, addOrderForm.city_A.data, addOrderForm.city_B.data)
cursor.execute(
"INSERT INTO orders(id_order, id_driver, id_client, id_product, weight, datedelivery, is_delivery, point_a, point_b) "
"VALUES ((SELECT MAX(id_order) + 1 FROM orders),'{id_driver}','{id_client}', '{id_product}', '{weight}', '{datedelivery}', {is_delivery}, '{pointa}', '{pointb}')".format(
id_driver = list_id['driver_id'], id_client = list_id['client_id'], id_product = list_id['product_id'], weight = list_id['weight'], datedelivery = list_id['date_delivery'], is_delivery = list_id['status'], pointa = list_id['city_A'], pointb = list_id['city_B'])
)
conn.commit()
def get_place_id(self, state):
cursor = conn.cursor()
cursor.execute(
"select id from panoptic.place where state='{}'".format(state))
res = cursor.fetchone()
state = res[0] if res else None
cursor.close()
return state
def update(id, name, number):
try:
cursor = conn.cursor()
cursor.execute(
"UPDATE contacts SET name=%s, number=%s WHERE id=%s", (name, number, id,))
close_connection(cursor)
return True
except expression as identifier:
return False
def yrkesgrupper():
with conn.cursor() as cursor:
query = "SELECT yrkesgrupp_id, yrkesgrupp FROM h4s.yrkesgrupper"
cursor.execute(query)
result = list(
map(lambda x: {
'yrkesgrupp_id': x[0],
'yrkesgrupp': x[1]
}, cursor.fetchall()))
return jsonify(result)
def bfs(network_name,broken_node, node_id):
with conn.cursor() as cursor:
#bfsCurs = conn.cursor()
visited, queue = set([broken_node]), [node_id]
while queue:
vertex = queue.pop(0)
if vertex not in visited:
query = "SELECT * FROM neighbornodes({0}, {1})".format(vertex, network_name) ##Fix dynamic network
cursor.execute(query)
node_set = set([w[0] for w in cursor.fetchall()])
visited.add(vertex)
queue.extend(node_set - visited)
return visited
def heatmapMaker():
with conn.cursor() as cursor:
#heatCurs = conn.cursor()
query = "select gid from vatten_cust order by random() limit 50;"
cursor.execute(query)
gid_list = [w[0] for w in cursor.fetchall()]
gid_list.extend([90, 114, 109, 108, 93, 87, 78, 84, 99, 102, 105, 96, 120, 117, 72, 64, 69, 81, 75])
gid_list.extend([1188, 1200, 1218, 1215, 1221, 1194, 1191, 1203, 975, 1212, 1185, 1197, 1209, 1206])
gid_list.extend([631, 634, 637, 640, 643, 646, 649, 654])
query = "SELECT row_to_json(fc) FROM (SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) as features FROM (SELECT 'Feature' as type, ST_AsGeoJSON(lg.the_geom)::json As geometry, row_to_json((SELECT l FROM (SELECT dp_otype, dp_ctype, dp_subtype, gid, cust_id) as l )) as properties FROM vatten_cust as lg WHERE gid in {0}) as f ) as fc".format(str(gid_list).replace('[','(').replace(']',')'))
cursor.execute(query)
inVar = [w[0] for w in cursor.fetchall()]
return json.dumps(inVar[0])
def nodeBreaker(network_name, node_id):
with conn.cursor() as cursor:
arc_tbl = "\'"+tables[network_name]['arcs']+"\'"
node_tbl = "\'"+tables[network_name]['nodes']+"\'"
conn_tbl = "\'"+tables[network_name]['conn']+"\'"
#brokenCurs = conn.cursor()
query = "SELECT * FROM nodeMaker({0}, {1}, {2}) WHERE nodemaker IS NOT NULL".format(node_id, arc_tbl, tables[network_name]['station_id'])
try:
cursor.execute(query)
brokenNodes = [w[0][0] for w in cursor.fetchall()]
nodeSet = set()
for i in brokenNodes:
nodeSet = nodeSet.union(bfs(arc_tbl,int(node_id), i))
if nodeSet:
return json.dumps({'Arc':json.loads(getBrokenArc(arc_tbl, nodeSet)),'Node':json.loads(getBrokenNode(node_tbl, node_id)),'Conn':json.loads(getBrokenCust(conn_tbl, nodeSet))})
else:
return "NULL"
except:
conn.rollback()
print(query)
return "NULL"
