def n_users(access_code, email_phone, password): import iz_func import json db, cursor = iz_func.connect() list = [] sql = "select id,blocked,email,full_name,is_admin,password,phone from n_users where (email = '" + str( email_phone) + "' or phone = '" + str( email_phone) + "') and password = '******' " print('[sql1] :', sql) cursor.execute(sql) results2 = cursor.fetchall() for row2 in results2: id, blocked, email, full_name, is_admin, password, phone = row2 list.append({ 'id': id, 'blocked': blocked, 'email': email, 'full_name': full_name, 'is_admin': is_admin, 'password': password, 'phone': phone }) answer = json.dumps(list) #answer = "Отказано в доступе ..." return answer
def sendMessage(access_code): import iz_func import json # from bot import botSendMessage db, cursor = iz_func.connect() list = [] sql = "SELECT c.messanger_id, c.from FROM n_clients c WHERE id = '{}' LIMIT 1".format( request.form['user_id']) cursor.execute(sql) results = cursor.fetchall() if results[0][1] == 'tg': import telebot token = '1393957889:AAEy0SgED_mt0zooGfgsrUHsQvkoC_7THz0' bot = telebot.TeleBot(token) bot.send_message(results[0][0], request.form['message'], parse_mode='Markdown') else: send_message(results[0][0], request.form['message']) sql = "INSERT INTO n_messages (`content`,`user_id`,`from`) VALUES ('{}', '{}', '{}')".format( request.form['message'], request.form['user_id'], request.form['from']) cursor.execute(sql) db.commit() answer = "Сообщение отправленно" return answer
def Client(access_code, client_id): import iz_func import json db, cursor = iz_func.connect() list = [] sql = """SELECT c.id, c.name, c.phone, c.from, JSON_ARRAYAGG(JSON_OBJECT('content', m.content, 'date', m.date)) AS messages FROM n_clients c JOIN n_messages m ON (m.user_id = c.id AND m.from = 'client') WHERE c.id = {} GROUP BY c.id LIMIT 1 """.format(client_id) cursor.execute(sql) results2 = cursor.fetchall() for row2 in results2: id, name, phone, _from, messages = row2 list.append({ 'id': id, 'from': _from, 'name': name, 'phone': phone, 'messages': messages }) answer = json.dumps(list) return answer
def n_UpdateBranch(access_code, id, answers_type, is_request, title, to_branch, for_all): import iz_func import json db, cursor = iz_func.connect() # title = title.encode('utf8') # is_request = is_request.encode('utf8') # to_branch = to_branch.encode('utf8') # for_all = for_all.encode('utf8') sql = "UPDATE n_branch SET answers_type = '" + str( answers_type) + "' WHERE `id` = '" + str(id) + "'" cursor.execute(sql) db.commit() sql = "UPDATE n_branch SET is_request = '" + str(is_request).replace( '*!*', '/').replace('@!@', '?') + "' WHERE `id` = '" + str(id) + "'" cursor.execute(sql) db.commit() sql = "UPDATE n_branch SET title = '" + str(title).replace( '*!*', '/').replace('@!@', '?') + "' WHERE `id` = '" + str(id) + "'" cursor.execute(sql) db.commit() sql = "UPDATE n_branch SET to_branch = '" + to_branch + "' WHERE `id` = '" + str( id) + "'" cursor.execute(sql) db.commit() sql = "UPDATE n_branch SET for_all = '" + for_all + "' WHERE `id` = '" + str( id) + "'" cursor.execute(sql) db.commit() answer = "Branch обновлён" #answer = "Отказано в доступе ..." return answer
def n_update(access_code, id, full_name, is_admin, password, phone, email): import iz_func import json db, cursor = iz_func.connect() list = [] if is_admin == '0': is_admin = 'No' sql = "UPDATE n_users SET full_name = '" + str( full_name).strip() + "' WHERE `id` = '" + str(id) + "'" cursor.execute(sql) db.commit() sql = "UPDATE n_users SET is_admin = '" + str( is_admin).strip() + "' WHERE `id` = '" + str(id) + "'" cursor.execute(sql) db.commit() sql = "UPDATE n_users SET password = '******' WHERE `id` = '" + str(id) + "'" cursor.execute(sql) db.commit() sql = "UPDATE n_users SET phone = '" + str( phone).strip() + "' WHERE `id` = '" + str(id) + "'" cursor.execute(sql) db.commit() sql = "UPDATE n_users SET email = '" + str( email).strip() + "' WHERE `id` = '" + str(id) + "'" cursor.execute(sql) db.commit() answer = "Обновлено" return answer
def begin (namebot,proxy): import iz_func k = '\033[37m\033[44m\033[1m' s = '\033[0;37m' e = '\033[1;33m' r = '\033[31m\033[44m\033[1m' i = '\033[0;32m' n = '\033[1;35m' db,cursor = iz_func.connect (namebot) sql = "select id,set_ip_proxy,set_port_proxy,set_tip_proxy from bots where name = '"+str(namebot)+"';" cursor.execute(sql) data = cursor.fetchall() prt = '' set_ip_proxy = '' for rec in data: id,set_ip_proxy,set_port_proxy,set_tip_proxy = rec if set_ip_proxy != '': prt = set_ip_proxy+':'+set_port_proxy+'-'+set_tip_proxy else: print ('[+] Прокси не указано') exit (0) print ('{}╔══════════════════════════════════════════════════════════════════════════════════════════════════════════════════╗{}'.format(k,s)) print ('{}║ {}Всем привет!👋👋! {} {} ║{}'.format(k,r,k,iz_func.RealData,s)) print ('{}║ ║{}'.format(k,s)) print ('{}║ Название программы: {}{}{} ║{}'.format(k,e,namebot,k,s)) print ('{}║ Описание: {}Бот для работы с клиентами{} ║{}'.format(k,e,k,s)) print ('{}║ Версия {}23.2.0{} ║{}'.format(k,e,k,s)) print ('{}║ ║{}'.format(k,s)) print ('{}║ Proxy: {}{}{} ║{}'.format(k,e,prt,k,s)) print ('{}║ Автор: {}@izofen (https://t.me/izofen){} ║{}'.format(k,e,k,s)) print ('{}║ Админка: {}http://3dot14.ru/admin/pages/tables/profile.php{} ║{}'.format(k,e,k,s)) print ('{}║ Резервная копия дата: {}20 января 2020{} ║{}'.format(k,e,k,s)) print ('{}║ Текущих пользователей: {}10{} ║{}'.format(k,e,k,s)) print ('{}║ Статистика использования: {}10{} ║{}'.format(k,e,k,s)) print ('{}║ ║{}'.format(k,s)) print ('{}║ Оповещенние при новых пользователях {}Включено{} ║{}'.format(k,e,k,s)) print ('{}║ Оповещенние при перезапуске {}Включено{} ║{}'.format(k,e,k,s)) print ('{}║ Оповещенние при активности {}Включено{} ║{}'.format(k,e,k,s)) print ('{}║ ║{}'.format(k,s)) print ('{}║ Тестирование прокси: {}Включено{} ║{}'.format(k,e,k,s)) print ('{}║ Тестирование работы программы {}Включено{} ║{}'.format(k,e,k,s)) print ('{}║ Протоколирование работы {}Включено{} ║{}'.format(k,e,k,s)) print ('{}║ Реферальная программа {}Включено{} ║{}'.format(k,e,k,s)) print ('{}║ Прием оплаты {}Включено{} ║{}'.format(k,e,k,s)) print ('{}║ Интеграция AMO CRM {}Включено{} ║{}'.format(k,e,k,s)) print ('{}║ Интеграция Trello {}Включено{} ║{}'.format(k,e,k,s)) print ('{}║ Интеграция 1С Предприятие {}Включено{} ║{}'.format(k,e,k,s)) print ('{}║ Рассылка сообщения клиентам {}Включено{} ║{}'.format(k,e,k,s)) print ('{}║ ║{}'.format(k,s)) print ('{}║ Группа фанатов разработки: {}https://t.me/izofen{} ║{}'.format(k,e,k,s)) print ('{}║ Для остановки нажмите {}CTRL+C{} ║{}'.format(k,e,k,s)) print ('{}╚══════════════════════════════════════════════════════════════════════════════════════════════════════════════════╝{}'.format(k,s))
def blockUser(access_code, id): import iz_func import json db, cursor = iz_func.connect() sql = "UPDATE n_users SET blocked = 'Заблокирован' WHERE `id` = '" + str( id) + "'" print('[sql4] :', sql) cursor.execute(sql) db.commit() answer = 'Удален объект' return answer
def blockTemplate(access_code, id): import iz_func import json db, cursor = iz_func.connect() sql = "DELETE FROM n_template WHERE `id` = '" + str( id) + "' ORDER BY id LIMIT 1" print('[sql4] :', sql) cursor.execute(sql) db.commit() answer = 'Удален объект' return answer
def n_update_requestStatus(access_code, id, status): import iz_func import json db, cursor = iz_func.connect() sql = "UPDATE n_requests SET status = '" + str( status) + "' WHERE `id` = '" + str(id) + "'" print('[sql13] :', sql) cursor.execute(sql) db.commit() answer = 'Изменено' return answer
def n_DeleteAnswer(access_code, id): import iz_func import json db, cursor = iz_func.connect() sql = "DELETE FROM n_answers WHERE `id` = '" + str( id) + "' ORDER BY id LIMIT 1" cursor.execute(sql) db.commit() answer = "Ответ удалён" #answer = "Отказано в доступе ..." return answer
def n_CreateTemplate(access_code, title, to_branch): import iz_func import json db, cursor = iz_func.connect() # title = title.encode('utf8') sql = "INSERT INTO n_template (`title`,`to_branch`) VALUES ('{}','{}')".format( title.replace('*!*', '/').replace('@!@', '?'), to_branch) print('[sql5] :', sql) cursor.execute(sql) db.commit() lastid = cursor.lastrowid list = [] list.append(lastid) #answer = "Отказано в доступе ..." return json.dumps(list)
def n_tags(access_code): import iz_func import json db, cursor = iz_func.connect() list = [] sql = "select id,title from n_tags where 1=1" print('[sql8] :', sql) cursor.execute(sql) results2 = cursor.fetchall() for row2 in results2: id, title = row2 list.append([id, title]) answer = json.dumps(list) #answer = "Отказано в доступе ..." return answer
def n_getTemplateList(access_code): import iz_func import json db, cursor = iz_func.connect() list = [] sql = "select id,title,to_branch from n_template where 1=1" print(sql) cursor.execute(sql) results2 = cursor.fetchall() for row2 in results2: id, title, to_branch = row2 list.append({'id': id, 'title': title, 'to_branch': to_branch}) answer = json.dumps(list) #answer = "Отказано в доступе ..." return answer
def n_UpdateTemplate(access_code, id, title, to_branch): import iz_func import json db, cursor = iz_func.connect() # title = title.encode('utf8') sql = "UPDATE n_template SET title = '" + str(title).replace( '*!*', '/').replace('@!@', '?') + "' WHERE `id` = '" + str(id) + "'" cursor.execute(sql) db.commit() sql = "UPDATE n_branch SET to_branch = '" + to_branch + "' WHERE `id` = '" + str( id) + "'" cursor.execute(sql) db.commit() branch = "Шаблон изменён" #answer = "Отказано в доступе ..." return branch
def n_CreateAnswer(access_code, branch_id, content, tag_id, to_branch, position, documentolog_id): import iz_func import json db, cursor = iz_func.connect() # content = content.encode('utf8') if documentolog_id == 'null': documentolog_id = '' sql = "INSERT INTO n_answers (`branch_id`,`content`,`tag_id`,`to_branch`, `position`, `documentolog_id`) VALUES ('{}','{}','{}','{}','{}','{}')".format( branch_id, content.replace('*!*', '/').replace('@!@', '?'), tag_id, to_branch, position, documentolog_id) print('[sql6] :', sql) cursor.execute(sql) db.commit() lastid = cursor.lastrowid list = [] list.append(lastid) return json.dumps(list)
def DocumentologMessage(id): import iz_func import json db, cursor = iz_func.connect() form = request.get_json() sql = """SELECT c.messanger_id, c.from, c.id FROM n_requests r JOIN n_clients c ON c.id = r.client_id WHERE r.documentolog_id = '{}' ORDER BY r.id DESC LIMIT 1""".format( id) cursor.execute(sql) results = cursor.fetchall() sql = "INSERT INTO n_messages (`content`,`from`,`user_id`) VALUES (%s, 'bot', '{}')".format( results[0][2]) cursor.execute(sql, (form['message'])) db.commit() if len(results) == 0: response = app.response_class(response=json.dumps({ 'status': 400, 'message': 'No find request with this id' }), status=400, mimetype='application/json') return response if results[0][1] == 'tg': import telebot token = '1393957889:AAEy0SgED_mt0zooGfgsrUHsQvkoC_7THz0' bot = telebot.TeleBot(token) bot.send_message(results[0][0], form['message'], parse_mode='Markdown') else: send_message(results[0][0], form['message']) response = app.response_class(response=json.dumps({ 'status': 200, 'message': "successful" }), status=200, mimetype='application/json') return response
def searchUser(access_code): import iz_func import json db, cursor = iz_func.connect() list = [] sql = """SELECT id, full_name FROM n_users WHERE is_admin = 'No' AND blocked = 'No' AND (full_name LIKE '%{}%' OR phone LIKE '%{}%' OR email LIKE '%{}%') """.format(request.form['find'], request.form['find'], request.form['find']) cursor.execute(sql) results2 = cursor.fetchall() for row2 in results2: id, full_name = row2 list.append({'id': id, 'full_name': full_name}) answer = json.dumps(list) return answer
def lastBranches(access_code): import iz_func import json db, cursor = iz_func.connect() list = [] sql = """SELECT r.id, JSON_ARRAYAGG(JSON_OBJECT('branch_id', rc.branch_id, 'title', rc.title)) AS content FROM n_requests r JOIN n_request_content rc ON rc.request_id = r.id WHERE client_id = '{}' GROUP BY r.id ORDER BY r.id DESC LIMIT 1""".format(request.form['client_id']) cursor.execute(sql) results = cursor.fetchall() for row in results: id, content = row list.append({'id': id, 'content': content}) answer = json.dumps(list) return answer
def n_CreateBranch(access_code, answers_type, is_request, title, to_branch, for_all): import iz_func import json db, cursor = iz_func.connect() # title = title.encode('utf8') # is_request = is_request.encode('utf8') # to_branch = to_branch.encode('utf8') # for_all = for_all.encode('utf8') sql = "INSERT INTO n_branch (`answers_type`,`is_request`,`title`,`to_branch`,`for_all`) VALUES ('{}','{}','{}','{}','{}')".format( answers_type, is_request.replace('*!*', '/').replace('@!@', '?'), title.replace('*!*', '/').replace('@!@', '?'), to_branch, for_all) print('[sql5] :', sql) cursor.execute(sql) db.commit() lastid = cursor.lastrowid list = [] list.append(lastid) return json.dumps(list)
def n_createTag(access_code, title): import iz_func import json db, cursor = iz_func.connect() #list = [] #sql = "select id,title from n_tags where 1=1" #print (sql) #cursor.execute(sql) #results2 = cursor.fetchall() #for row2 in results2: # id,title = row2 # list.append ([id,title]) sql = "INSERT INTO n_tags (`title`) VALUES ('{}')".format(title) print('[sql7] :', sql) cursor.execute(sql) db.commit() lastid = cursor.lastrowid answer = [lastid] #answer = "Отказано в доступе ..." return answer
def KorrespondentyFields(access_code, id): import iz_func db, cursor = iz_func.connect() import requests from requests.auth import HTTPBasicAuth response = requests.get( "https://doc.nurotan.kz/webservice/json/chat_bot_spisok_korrespondenty", auth=HTTPBasicAuth("korrespondenty", "76M#;p&x6-gZxH5A7~Jg")) answer = response.json()["data"]["fields"] sql = """SELECT korrespondenty_id, COUNT(*) as len FROM korrespondenty WHERE district_id = '{}' GROUP BY korrespondenty_id ORDER BY len LIMIT 1;""".format(id) print(sql) cursor.execute(sql) results = cursor.fetchall() if results and len(results): return json.dumps({'fields': answer, 'current': results[0][0]}) else: return json.dumps({'fields': answer, 'current': 0})
def update_changeManager(access_code, id, user_id): import iz_func import json db, cursor = iz_func.connect() sql = "UPDATE n_clients SET user_id = '" + str( user_id) + "' WHERE id = '" + str(id) + "'" print('[sql11] :', sql) cursor.execute(sql) db.commit() #list = [] #sql = "select id,from,name,phone,user_id from n_clients where 1=1" #print (sql) #cursor.execute(sql) #results2 = cursor.fetchall() #for row2 in results2: # id,fromL,name,phone,user_id = row2 # list.append ([id,fromL,name,phone,user_id]) answer = 'Изменено' #answer = "Отказано в доступе ..." return answer
def n_addUset(access_code, full_name, is_admin, password, phone, email): import iz_func import json print('[+] Добавление пользователей') print('[+] access_code:', access_code) print('[+] full_name:', full_name) print('[+] is_admin:', is_admin) print('[+] password:'******'[+] phone:', phone) print('[+] email:', email) db, cursor = iz_func.connect() # full_name = full_name.encode('utf8') sql = "INSERT INTO n_users (`blocked`,`email`,`full_name`,`is_admin`,`password`,`phone`) VALUES ('{}','{}','{}','{}','{}','{}')".format( 'No', email.strip(), full_name.strip(), is_admin.strip(), password.strip(), phone.strip()) print('[sql5] :', sql) cursor.execute(sql) db.commit() lastid = cursor.lastrowid list = [] list.append(lastid) #answer = "Отказано в доступе ..." return json.dumps(list)
def n_getBranchList(access_code): import iz_func import json db, cursor = iz_func.connect() list = [] sql = """SET SESSION group_concat_max_len = 1000000""" cursor.execute(sql) sql = """SELECT b.id, b.for_all, b.title, b.is_request, b.answers_type, b.to_branch, CONCAT( '[', GROUP_CONCAT( JSON_OBJECT('id',a.id,'to_branch', a.to_branch,'content', a.content,'position',a.position, 'documentolog_id',a.documentolog_id) order by a.position ), ']' ) answers FROM n_branch b LEFT JOIN n_answers a ON a.branch_id = b.id GROUP BY b.id""" # JSON_ARRAYAGG(JSON_OBJECT('id',a.id,'to_branch', a.to_branch,'content', a.content, 'tag_id',a.tag_id, 'position',a.position, 'documentolog_id',a.documentolog_id)) AS answers print(sql) cursor.execute(sql) results2 = cursor.fetchall() for row2 in results2: id, for_all, title, is_request, answers_type, to_branch, answers = row2 list.append({ 'id': id, 'for_all': for_all, 'title': title, 'is_request': is_request, 'answers_type': answers_type, 'to_branch': to_branch, 'answers': answers }) answer = json.dumps(list) #answer = "Отказано в доступе ..." return answer
def n_UpdateAnswer(access_code, id, branch_id, content, tag_id, to_branch, position, documentolog_id): import iz_func import json db, cursor = iz_func.connect() # content = content.encode('utf8') sql = "UPDATE n_answers SET branch_id = '" + str( branch_id) + "' WHERE `id` = '" + str(id) + "'" cursor.execute(sql) db.commit() sql = "UPDATE n_answers SET content = '" + content.replace( '*!*', '/').replace('@!@', '?') + "' WHERE `id` = '" + str(id) + "'" cursor.execute(sql) db.commit() sql = "UPDATE n_answers SET tag_id = '" + str( tag_id) + "' WHERE `id` = '" + str(id) + "'" cursor.execute(sql) db.commit() sql = "UPDATE n_answers SET to_branch = '" + to_branch + "' WHERE `id` = '" + str( id) + "'" cursor.execute(sql) db.commit() sql = "UPDATE n_answers SET position = '" + position + "' WHERE `id` = '" + str( id) + "'" cursor.execute(sql) db.commit() if documentolog_id == 'null': documentolog_id = '' sql = "UPDATE n_answers SET documentolog_id = '" + documentolog_id + "' WHERE `id` = '" + str( id) + "'" cursor.execute(sql) db.commit() answer = "Ответ изменён" #answer = "Отказано в доступе ..." return answer
def userList(access_code, sort_by, sort_type): import iz_func import json db, cursor = iz_func.connect() list = [] sql = "select id,blocked,email,full_name,is_admin,password,phone from n_users where 1=1 ORDER BY " + str( sort_by) + " " + str(sort_type) + " " print('[sql2] :', sql) cursor.execute(sql) results2 = cursor.fetchall() for row2 in results2: id, blocked, email, full_name, is_admin, password, phone = row2 list.append({ 'id': id, 'blocked': blocked, 'email': email, 'full_name': full_name, 'is_admin': is_admin, 'password': password, 'phone': phone }) answer = json.dumps(list) #answer = "Отказано в доступе ..." return answer
#!/usr/bin/python # -*- coding: utf-8 # ОПИСАНИЕ БОТА: Информирование клиентов о новых поступлениях # АВТОР БОТА : Купинов Вадим # ТЕЛЕГРАММ АВТОРА : @izofen import time import iz_func namebot = "@BT_trader_bot" if __name__ == "__main__": db, cursor = iz_func.connect(namebot) sql = "select id,ip,login,password,port,status,time_start,time_finish,time_long,bot_used from proxy where bot_used = '" + str( namebot) + "';" cursor.execute(sql) data = cursor.fetchall() for rec in data: id, ip, login, password, port, status, time_start, time_finish, time_long, bot_used = rec print('[+]', ip, port) timestamp = int(time.time()) sql = "UPDATE proxy SET bot_used = '' WHERE id = '" + str(id) + "'" cursor.execute(sql) db.commit() sql = "UPDATE proxy SET time_finish = " + str( timestamp) + " WHERE id = '" + str(id) + "'" cursor.execute(sql) db.commit() time_long = timestamp - time_start if time_finish == 0: sql = "UPDATE proxy SET time_long = " + str(
def processing(self): import iz_func db, cursor = iz_func.connect() message = self.json['messages'][0] text = message['body'].split() # text[0].lower() if not message['fromMe']: id = message['chatId'] sql = """SELECT id, branch_id FROM n_clients WHERE messanger_id = '{}' LIMIT 1 """.format(id) cursor.execute(sql) results = cursor.fetchall() user = {} item = {'is_request': 0} if len(results) == 0: sql = "SELECT * FROM n_users WHERE is_admin = 'No' ORDER BY RAND() LIMIT 1" cursor.execute(sql) results2 = cursor.fetchall() sql = """INSERT INTO n_clients (`name`,`from`,`user_id`,`branch_id`,`messanger_id`) VALUES ('{}','wp','{}','{}','{}') """.format(message['senderName'], results2[0][0], 1, id) cursor.execute(sql) db.commit() user = {'id': cursor.lastrowid, 'branch_id': 1} sql = """SET SESSION group_concat_max_len = 1000000""" cursor.execute(sql) sql = """SELECT b.id, b.title, b.answers_type, CONCAT( '[', GROUP_CONCAT( JSON_OBJECT('id',a.id,'content', a.content) order by a.position ), ']' ) answers, b.is_request FROM n_branch b LEFT JOIN n_answers a ON a.branch_id = b.id WHERE b.id = 1 GROUP BY b.id LIMIT 1""" cursor.execute(sql) results2 = cursor.fetchall() item = { 'id': results2[0][0], 'title': results2[0][1], 'answers_type': results2[0][2], 'answers': json.loads(results2[0][3]), 'is_request': results2[0][4] } if item['answers_type'] != '2': i = 1 for answer in item['answers']: item['title'] += '\n' for j in str(i): item['title'] += addEmoji(j) item['title'] += replaceTextButton(answer['content']) i += 1 sql = "INSERT INTO n_messages (`content`,`from`,`user_id`) VALUES ('{}', 'bot', '{}')".format( item['title'], user['id']) cursor.execute(sql) db.commit() self.send_message(id, replaceText(item['title'])) else: sql = "INSERT INTO n_messages (`content`,`from`,`user_id`) VALUES ('{}', 'bot', '{}')".format( item['title'], user['id']) cursor.execute(sql) db.commit() self.send_message(id, replaceText(item['title'])) return {'status': 200} else: user = {'id': results[0][0], 'branch_id': results[0][1]} sql = "INSERT INTO n_messages (`content`,`from`,`user_id`) VALUES ('{}', 'client', '{}')".format( text[0].lower(), user['id']) cursor.execute(sql) db.commit() sql = """SET SESSION group_concat_max_len = 1000000""" cursor.execute(sql) sql = """SELECT b.id, b.answers_type, b.to_branch, CONCAT( '[', GROUP_CONCAT( JSON_OBJECT('id', a.id, 'to_branch', a.to_branch,'content', a.content, 'tag_id',a.tag_id, 'documentolog_id', a.documentolog_id) order by a.position ), ']' ) answers, b.is_request FROM n_branch b LEFT JOIN n_answers a ON a.branch_id = b.id WHERE b.id = {} GROUP BY b.id LIMIT 1""".format(user['branch_id']) cursor.execute(sql) results2 = cursor.fetchall() current = { 'id': results2[0][0], 'answers_type': results2[0][1], 'to_branch': results2[0][2], 'answers': json.loads(results2[0][3]), 'is_request': results2[0][4] } ans_i = 0 if current['answers_type'] == '2': if current['to_branch'] and current['to_branch'] != 'null': sql = """SET SESSION group_concat_max_len = 1000000""" cursor.execute(sql) sql = """SELECT b.id, b.title, b.answers_type, CONCAT( '[', GROUP_CONCAT( JSON_OBJECT('id', a.id, 'content', a.content) order by a.position ), ']' ) answers, b.is_request FROM n_branch b LEFT JOIN n_answers a ON a.branch_id = b.id WHERE b.id = {} GROUP BY b.id LIMIT 1""".format(current['to_branch']) cursor.execute(sql) results2 = cursor.fetchall() item = { 'id': results2[0][0], 'title': results2[0][1], 'answers_type': results2[0][2], 'answers': json.loads(results2[0][3]), 'is_request': results2[0][4] } else: sql = """SET SESSION group_concat_max_len = 1000000""" cursor.execute(sql) sql = """SELECT b.id, b.title, b.answers_type, CONCAT( '[', GROUP_CONCAT( JSON_OBJECT('id', a.id, 'content', a.content) order by a.position ), ']' ) answers, b.is_request FROM n_branch b LEFT JOIN n_answers a ON a.branch_id = b.id WHERE b.id = 1 GROUP BY b.id LIMIT 1""" cursor.execute(sql) results2 = cursor.fetchall() item = { 'id': results2[0][0], 'title': results2[0][1], 'answers_type': results2[0][2], 'answers': json.loads(results2[0][3]), 'is_request': results2[0][4] } if item['answers_type'] != '2': i = 1 for answer in item['answers']: item['title'] += '\n' for j in str(i): item['title'] += addEmoji(j) item['title'] += replaceTextButton(answer['content']) i += 1 sql = "INSERT INTO n_messages (`content`,`from`,`user_id`) VALUES ('{}', 'bot', '{}')".format( item['title'], user['id']) cursor.execute(sql) db.commit() self.send_message(id, replaceText(item['title'])) else: sql = "INSERT INTO n_messages (`content`,`from`,`user_id`) VALUES ('{}', 'bot', '{}')".format( item['title'], user['id']) cursor.execute(sql) db.commit() self.send_message(id, replaceText(item['title'])) else: ans_i = 1 for answer in current['answers']: if text[0].lower() == str(ans_i): if answer[ 'to_branch'] and answer['to_branch'] != 'null': sql = """SET SESSION group_concat_max_len = 1000000""" cursor.execute(sql) sql = """SELECT b.id, b.title, b.answers_type, CONCAT( '[', GROUP_CONCAT( JSON_OBJECT('id',a.id,'content', a.content) order by a.position ), ']' ) answers, b.is_request FROM n_branch b LEFT JOIN n_answers a ON a.branch_id = b.id WHERE b.id = {} GROUP BY b.id LIMIT 1""".format(answer['to_branch']) cursor.execute(sql) results2 = cursor.fetchall() item = { 'id': results2[0][0], 'title': results2[0][1], 'answers_type': results2[0][2], 'answers': json.loads(results2[0][3]), 'is_request': results2[0][4] } else: sql = """SET SESSION group_concat_max_len = 1000000""" cursor.execute(sql) sql = """SELECT b.id, b.title, b.answers_type, CONCAT( '[', GROUP_CONCAT( JSON_OBJECT('id',a.id,'content', a.content) order by a.position ), ']' ) answers, b.is_request FROM n_branch b LEFT JOIN n_answers a ON a.branch_id = b.id WHERE b.id = 1 GROUP BY b.id LIMIT 1""" cursor.execute(sql) results2 = cursor.fetchall() item = { 'id': results2[0][0], 'title': results2[0][1], 'answers_type': results2[0][2], 'answers': json.loads(results2[0][3]), 'is_request': results2[0][4] } if item['answers_type'] != '2': j = 1 for answer in item['answers']: item['title'] += '\n' for k in str(j): item['title'] += addEmoji(k) item['title'] += replaceTextButton( answer['content']) j += 1 sql = "INSERT INTO n_messages (`content`,`from`,`user_id`) VALUES ('{}', 'bot', '{}')".format( item['title'], user['id']) cursor.execute(sql) db.commit() self.send_message(id, replaceText(item['title'])) else: sql = "INSERT INTO n_messages (`content`,`from`,`user_id`) VALUES ('{}', 'bot', '{}')".format( item['title'], user['id']) cursor.execute(sql) db.commit() self.send_message(id, replaceText(item['title'])) break ans_i += 1 if current['is_request'] and current['is_request'] != 'undefined': sql = "SELECT id FROM n_requests WHERE client_id = {} AND status = 0".format( user['id']) cursor.execute(sql) results = cursor.fetchall() request = {} if len(results) > 0: request = {'id': results[0][0]} else: sql = "INSERT INTO n_requests (`status`,`checked`,`client_id`) VALUES (0,0,'{}')".format( user['id']) cursor.execute(sql) db.commit() request = {'id': cursor.lastrowid} sql = """SELECT rc.id FROM n_requests r JOIN n_request_content rc ON rc.request_id = r.id WHERE rc.title = '{}' AND r.status = 0 AND r.client_id = {} """.format(current['is_request'], user['id']) cursor.execute(sql) results = cursor.fetchall() if len(results) > 0: if ans_i == 0: sql = "UPDATE n_request_content SET answer = '{}' WHERE `id` = {}".format( replaceTextButton(text[0].lower()), results[0][0]) else: sql = "UPDATE n_request_content SET answer = '{}' WHERE `id` = {}".format( replaceTextButton( current['answers'][ans_i - 1]['content']), results[0][0]) sql = "UPDATE n_request_content SET documentolog_id = '{}' WHERE `id` = {}".format( current['answers'][ans_i - 1]['documentolog_id'], results[0][0]) cursor.execute(sql) db.commit() sql = "UPDATE n_request_content SET branch_id = '{}' WHERE `id` = {}".format( current['id'], results[0][0]) cursor.execute(sql) db.commit() else: if ans_i == 0: sql = "INSERT INTO n_request_content (`request_id`,`title`,`answer`, `branch_id`) VALUES ('{}','{}','{}','{}')".format( request['id'], current['is_request'], replaceTextButton(text[0].lower()), current['id']) else: sql = "INSERT INTO n_request_content (`request_id`,`title`,`answer`, `branch_id`, `documentolog_id`) VALUES ('{}','{}','{}','{}','{}')".format( request['id'], current['is_request'], replaceTextButton( current['answers'][ans_i - 1]['content']), current['id'], current['answers'][ans_i - 1]['documentolog_id']) cursor.execute(sql) db.commit() if item['is_request'] and item['is_request'] == 'undefined': sql = "SELECT id FROM n_requests WHERE client_id = '{}' AND status = '0'".format( user['id']) cursor.execute(sql) results = cursor.fetchall() if len(results) > 0: sql = "UPDATE n_requests SET status = '1' WHERE `id` = '" + str( results[0][0]) + "'" cursor.execute(sql) db.commit() sql = "UPDATE n_requests SET date = now() WHERE `id` = '" + str( results[0][0]) + "'" cursor.execute(sql) db.commit() sql = "UPDATE n_clients SET branch_id = " + str( item['id']) + " WHERE `id` = '" + str(user['id']) + "'" cursor.execute(sql) db.commit() return {'status': 200}
def n_messagesList(access_code): import iz_func import json db, cursor = iz_func.connect() list = [] sql = {} if request.form['last'] == '0': sql = """SELECT id, content, m.date, m.from FROM n_messages m WHERE m.user_id = {} ORDER BY m.id DESC LIMIT 20 OFFSET {} """.format(request.form['user_id'], request.form['page']) else: sql = """SELECT id, content, m.date, m.from FROM n_messages m WHERE m.user_id = {} AND m.id > {} """.format(request.form['user_id'], request.form['last']) cursor.execute(sql) results = cursor.fetchall() for row2 in results: id, content, date, _from = row2 if _from == 'client': sql = """SELECT c.id, c.name, c.phone FROM n_clients c WHERE c.id = {} LIMIT 1 """.format(request.form['user_id']) cursor.execute(sql) results2 = cursor.fetchall() list.append({ 'id': id, 'content': content, 'date': date, 'from': _from, 'user': { 'id': results2[0][0], 'name': results2[0][1], 'phone': results2[0][2], } }) elif _from != 'bot': sql = """SELECT u.id, u.full_name FROM n_users u WHERE u.id = {} LIMIT 1 """.format(_from) cursor.execute(sql) results2 = cursor.fetchall() list.append({ 'id': id, 'content': content, 'date': date, 'from': _from, 'user': { 'id': results2[0][0], 'name': results2[0][1], } }) else: list.append({ 'id': id, 'content': content, 'date': date, 'from': _from, 'user': { 'id': 0, 'name': 'bot', } }) def myconverter(o): if isinstance(o, datetime.datetime): return o.__str__() answer = json.dumps(list, default=myconverter) #answer = "Отказано в доступе ..." return answer
def n_update_requestChecked(access_code, id, checked): import iz_func import json db, cursor = iz_func.connect() if (checked == '3'): sql = """SELECT JSON_ARRAYAGG(JSON_OBJECT(0, rc.title, 1, rc.answer, 2, rc.documentolog_id)) AS content FROM n_requests r JOIN n_request_content rc ON rc.request_id = r.id WHERE r.id = '{}' GROUP BY r.id LIMIT 1""".format(id) cursor.execute(sql) results = cursor.fetchall() content = json.loads(results[0][0]) data = { 'Адрес': '', 'ФИО': '', 'Регион': '', 'Характер вопроса': '', 'Возраст': '', 'Вопрос': '', 'Телефон': '', 'Район': '', 'Пол': '', 'rajoni_gorodov': '', 'Статус обращающегося': '', 'Тип вопроса': '', 'Язык': '', 'Район города': '', 'Актуальные вопросы': '' } for item in content: if item['2']: data[item['0']] = {item['2']: item['1']} else: data[item['0']] = item['1'] if item['0'] == 'Район': sql = """INSERT INTO korrespondenty (korrespondenty_id, district_id) VALUES ('{}', '{}') """.format(request.form['korrespondenty_id'], item['2']) cursor.execute(sql) db.commit() sql = """SELECT c.from FROM n_requests r JOIN n_clients c ON c.id = r.client_id WHERE r.id = {} LIMIT 1;""".format(id) cursor.execute(sql) results = cursor.fetchall() if results[0][0] == 'tg': _from = { 'key': '38351f92-8e30-4172-bf3d-5c023319035e', 'value': 'Telegram' } else: _from = { 'key': '23ca40bc-6c67-4478-a5fd-5c023312007c', 'value': 'Whatsapp' } data = { 'dom': data['Адрес'].split(' ')[1] if len(data['Адрес'].split(' ')) >= 2 else None, 'familiya': data['ФИО'].split(' ')[1] if len(data['ФИО'].split(' ')) >= 2 else None, 'gorod': None, 'harakter_voprosa': data['Характер вопроса'] or None, 'imya': data['ФИО'].split(' ')[0] if len(data['ФИО'].split(' ')) >= 1 else None, 'kategoriya_vozrasta': data['Возраст'] or None, 'komu': { request.form['korrespondenty_id']: request.form['korrespondent'] }, 'kratkoe_soderzhanie': data['Вопрос'] or None, 'kvartira': data['Адрес'].split(' ')[2] if len(data['Адрес'].split(' ')) >= 3 else None, 'mobilnij_telefon': data['Телефон'] or None, 'oblast': data['Район'] or None, 'rajoni_gorodov': data['Район города'] or None, 'otchestvo': data['ФИО'].split(' ')[2] if len(data['ФИО'].split(' ')) >= 3 else None, 'pol': data['Пол'] or None, 'region': data['Регион'] or None, 'status_obrativshegosya_litsa': data['Статус обращающегося'] or None, 'tematika_voprosa': data['Тип вопроса'] or None, 'trebuet_otveta': { '1': 'Да' } if data['Статус обращающегося'] else { '2': 'Нет' }, 'ulitsa': data['Адрес'].split(' ')[0] if len(data['Адрес'].split(' ')) >= 1 else None, 'vlozheniya': [], 'yazik_dokumenta': data['Язык'] or None, 'whatsapp_telegram': _from['value'], 'istochnik_obrasheniya': { _from['key']: _from['value'] }, 'aktualnie_voprosi_ozekti_syraktar': data['Актуальные вопросы'] or None } print(json.dumps(data)) import requests from requests.auth import HTTPBasicAuth response = requests.post( 'https://doc.nurotan.kz/webservice/json/82be1ae3-5131-47ab-ae04-5fcc8a8a0161', auth=HTTPBasicAuth('Chatbot', "5+xcsV!r*;%aR5GY9F44"), data=json.dumps(data)) print(response.content) print( str(response.content).split( '950fff8d-b9a4-4920-8fff-5285deb002c8:')[1].split('":"')[0]) sql = "UPDATE n_requests SET documentolog_id = '" + str( response.content).split('950fff8d-b9a4-4920-8fff-5285deb002c8:')[ 1].split('":"')[0] + "' WHERE `id` = '" + id + "'" print('[sql13] :', sql) cursor.execute(sql) db.commit() sql = "UPDATE n_requests SET checked = '" + str( checked) + "' WHERE `id` = '" + str(id) + "'" print('[sql13] :', sql) cursor.execute(sql) db.commit() answer = 'Изменено' return answer