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)