def update_category(args):
sql = """ Update nomenclature
set category = '{category}'
where id_nom = {id_nom}
"""
# print(sql)
Sql.exec(query=sql, args=args)
def add_new_point(new_point):
path_list = []
set_time(new_point)
set_rating(new_point)
sql = " INSERT INTO Geo (Name, X, Y, Type, Descript, Rating, Time) VALUES (\'{}\', {}, {}, \'{}\', \'{}\', {}, {}) RETURNING id".format(
new_point[names.NAME],
float(new_point[names.x]),
float(new_point[names.y]),
new_point[names.TYPE],
new_point[names.DESCRIPTION],
int(new_point[names.RATING]),
int(new_point[names.TIME]))
new_id = Sql.exec(query=sql)[0]['id']
sql_cross_join = """
select
geo.id new_id,
geo1.id other_id,
geo.x::text || ',' || geo.y::text new_xy,
geo1.x::text || ',' || geo1.y::text other_xy
from
geo
cross join geo geo1
where
geo.id = {}
""".format(new_id)
paths_points = Sql.exec(query=sql_cross_join)
for path in paths_points:
other_id = path['other_id']
data = [path['new_xy'], path['other_xy']]
answer = get_google(data) if new_id != other_id else 0
path_list.append('"{%s,%s,%s}"' % (new_id, other_id, answer))
path_list.append('"{%s,%s,%s}"' % (other_id, new_id, answer)) if new_id != other_id else None
data_insert = "'{%s}'" % ','.join(path_list)
sql_insert = """insert into geo_distance
(point_1, point_2, distance)
select
t._data[1],
t._data[2],
t._data[3]
from
(
select _un::integer[] _data from unnest({data_insert}::text[]) _un
) t
""".format(data_insert=data_insert)
Sql.exec(sql_insert)
def get_pair_touch(self):
"""
Метод получает из базы дистанцию для всех пар значений координат
:param coords: кортеж ID координат
"""
get_sql = """
with
elements as (
select unnest('{%s}'::integer[]) as id
),
get_pair as (
select b1.id as a_p, b2.id as b_p
from elements b1, elements b2
where b1.id <> b2.id
),
get_coord as (
select d.id, d.point_1, d.point_2, d.distance
from geo_distance d, get_pair pair
where (point_1, point_2) = (pair.a_p, pair.b_p)
or (point_1, point_2) = (pair.b_p, pair.a_p)
)
select * from get_coord order by point_1, point_2;
"""
text = ''
for i in self.id_list:
text += '{}, '.format(i) if i != self.id_list[-1] else '{}'.format(i)
#print(get_sql % (text))
self.dict_pair_touch = Sql.exec(query=get_sql % (text))
def get_list():
query = """
select *
from nomenclature
"""
# print(query)
return Sql.exec(query=query)
def get_filter_project(args):
query = """
select
distinct
upd.id_project
, p.title
, p.description
, p.description_full
, p.photo
, p.id_user
, p.budget::Text
, p.rate
, u.name
, u.photo
, category
from (
select distinct
upd.id_project
, array_agg(json_object(array['id_category', 'title', 'description', 'photo'], array[c.id_category::text, c.title::text, c.description::text, c.photo::text])::Text) as category
from user_project_doc upd
left join project_category pc on pc.id_project = upd.id_project
left join category c using(id_category)
where upd.id_project is not null
{id_user}
group by upd.id_project
) upd
left join project p using (id_project)
left join users u on p.id_user = u.id_user
where true
{id_category}
order by p.title
"""
return Sql.exec(query=query, args=args)
def update_profile(args):
query = """
update profile
set "description" = '{description}'
where "id_profile" = {id_profile}
"""
return Sql.exec(query=query, args=args)
def insert_print_form(args):
fields = ''
for f in names.print_form_fields:
if f == names.print_form_fields[0]:
fields += ' ' + f
else:
fields += ', ' + f
args[f] = args.get(f) or "''"
args['fields'] = fields
fields_values = ''
for f in names.print_form_fields:
if f == names.print_form_fields[0]:
fields_values += ' {' + f + '}'
else:
fields_values += ', {' + f + '}'
args['fields_values'] = fields_values
query = """
insert into "Организация" (
{fields}
)
VALUES (
%s
)
""" % fields_values
return Sql.exec(query=query, args=args)
def post_calendar(args):
"""
Занести запись в календарь
:param args:
:return:
"""
query = """
insert into calendar
(title,
description,
datebegin,
dateend,
id_user)
values ('{title}',
'{description}',
'{datebegin}'::timestamp,
'{dateend}'::timestamp,
{id_user})
returning
id_procedure
, title
, description
, link
, to_char(datebegin,'DD.MM.YY HH24:MI:SS') datebegin
, to_char(dateend,'DD.MM.YY HH24:MI:SS') dateend
"""
return Sql.exec(query=query, args=args)
def post_survey(args):
"""
Занести результаты теста
:param args:
:return:
"""
query = """
insert into survey
(eat,
dysphagia,
wash,
wash_teeth,
dress,
restroom,
id_user)
values ({eat},
{dysphagia},
{wash},
{wash_teeth},
{dress},
{restroom},
{id_user})
returning
id_survey,
eat,
dysphagia,
wash,
wash_teeth,
dress,
restroom,
id_user
"""
result = Sql.exec(query=query, args=args)
return result
def get_user_list_expired(args):
query = """
select *
from(
select
n.name
, n.code
, n.img
, dn.id_nom
, dn.gost
, dn.weight
, dn.storage_conditions
, dn.gmo
, dn.packing
, dn.energy
, expired_start::text
, expired_end::text
from user_nom
left join nomenclature n using(id_nom)
left join description_nom dn using(id_nom)
where id_user = {id_user}
and not "close"
order by name
) nom
"""
# print(query)
return Sql.exec(query=query, args=args)
def get_types():
get_sql = """select distinct type FROM geo"""
dict_type = Sql.exec(query=get_sql)
result = []
for d in dict_type:
result.append(d["type"])
return {"data": result}
def get_history_room(args):
"""
Получить истрию чата
:param args:
:return:
"""
query = """
with
messages as (
select
distinct m.id_room
, r.title
, m.id_message
, m.text_message
, m.id_user
, m.created_at
, u.name
from messages m
left join rooms r on r.id_room = m.id_room
left join users u on u.id_user = m.id_user
where m.id_room = {id_room}
)
select
id_room
, title
, id_message
, text_message
, id_user
, name
, to_char(created_at,'DD.MM.YY HH24:MI:SS') created_at
from messages
order by created_at
"""
return Sql.exec(query=query, args=args)
def get_notifications(args):
query = """
select notifications.* from notifications_users
join notifications on notifications."id_notification" = notifications_users."id_notification"
where "id_user" = {id_user}
"""
return Sql.exec(query=query, args=args)
def select_user(args):
if args[names.PAGE] == "client":
args[names.PAGE] = 0
if args[names.PAGE] == "employee":
args[names.PAGE] = 1
query = """
insert into "session"("session", "id_user")
select md5(random()::text || clock_timestamp()::text)::uuid
, "id_user"
from (
select (
select "id_user"
from "users"
where "login" = '{Login}'
and "password" = '{Password}'
and "privilege" = {Pages}
limit 1
) )"id_user"
where "id_user" is not null
returning "session" as "Session"
""".format(Login=args[names.LOGIN],
Password=args[names.PASSWORD],
Pages=args[names.PAGE])
# print(query)
try:
auth_data = Sql.exec(query=query)
except:
return errors.SQL_ERROR, None
if auth_data == errors.SQL_ERROR or auth_data[0] is None:
return errors.SQL_ERROR, None
else:
return errors.OK, auth_data[0]
def get_info_user(args):
query = """
select
dn.id_nom
, n.name
, n.code
, n.img
, n.shelf_life::text
, dn.gost
, dn.weight
, dn.storage_conditions
, dn.gmo
, dn.packing
, dn.energy
, expired_start::text
, expired_end::text
, id_user_nom
, expired
from user_nom
left join nomenclature n using(id_nom)
left join description_nom dn using(id_nom)
where n."id_nom" = {id_nom}
and "id_user" = {id_user}
"""
return Sql.exec(query=query, args=args)
def set_profile(args):
query = """
insert into person(
firstName,
lastName,
"role",
"birthDate",
company,
about,
photo,
city,
contacts
)
values (
{firstName} -- firstName
, {lastName} -- lastName
, coalesce({role}, 0) -- role
, coalesce({birthDay}, now()) -- birthDate
, {company} -- company
, {about} -- about
, {photo} -- photo
, {city} -- city
, {contacts} -- contacts
)
returning id
"""
return Sql.exec(query=query, args=args)
def delete_sales_user(args):
query = """
update sales_list
set close = True
where id_user = {id_user}
and id_sales = {id_sales}
"""
return Sql.exec(query=query, args=args)
def get_ads():
query = """
select
*
from ads
order by title
"""
return Sql.exec(query=query)
def get_investors():
query = '''
select *
from users
where inv = 1
order by name
'''
return Sql.exec(query=query)
def get_category(args):
query = """
select
*
from category
where "id_category" = {id_category}
"""
return Sql.exec(query=query, args=args)
def get_print_form(args):
query = """
select *
from users u
join "Организация" org on u.id_user = org.id_user
where u.id_user = '******'
"""
return Sql.exec(query=query, args=args)
def register_user(args):
query = """
insert into "users"("login", "password", "name")
VALUES ('{login}', '{password}', '{name}')
returning id_user
"""
print(query)
return Sql.exec(query=query, args=args)
def search_nom(args):
query = """
select *
from nomenclature
where "{field}" = '{query}'
"""
# print(query)
return Sql.exec(query=query, args=args)
def get_profile(args):
query = """
select *
from Person
where id = {user_id}
"""
print(query)
return Sql.exec(query=query, args=args)
def get_info(args):
query = """
select *
from nomenclature
where "id_nom" = {id_nom}
"""
# print(query)
return Sql.exec(query=query, args=args)
def get_budgets(args):
query = """
select
*
from budget
order by budget
"""
return Sql.exec(query=query, args=args)
def check_user(args):
query = """
select 1
from users
where "login" = '{login}'
and "password" = '{password}'
"""
return Sql.exec(query=query, args=args)
def get_document(args):
query = """
select
*
from document
where "id_document" = {id_document}
and lesson is False
"""
return Sql.exec(query=query, args=args)
def get_lessons():
query = """
select
*
from document
where lesson is True
order by title
"""
return Sql.exec(query=query)
def auth_user(args):
query = """
select id_user
from users
where "login" = '{login}'
and "password" = '{password}'
"""
# print(query)
return Sql.exec(query=query, args=args)
