def client_handler(conn, addr, db_file):
sql = DBhandler.open_db(db_file)
print(pt(), 'Новый поток создан')
# Вход/регистрация
try:
sign(conn, sql, db_file)
except:
print(pt(), 'Клиент принудительно разорвал соединение')
sql.close()
print(pt(), 'Поток уничтожен')
return
while True:
req = conn.recv(2048)
if not req:
print(pt(), 'Клиент отключился')
break
req = pickle.loads(req)
if req[0] == 'send':
DBhandler.add_message(sql, req[1])
elif req[0] == 'view':
res = sqlreq(sql, req[1], req[2])
if not res:
resp = False
resp = pickle.dumps(resp)
conn.send(resp)
resp = pickle.dumps(res)
conn.send(resp)
elif req[0] == 'del':
print(pt(), 'Поступил запрос на удаление последнего сообщения от',
req[1])
DBhandler.del_last(sql, req[1])
print(pt(), 'Поток уничтожен')
sql.close()
def continueImport(self):
self.filename = filedialog.askopenfilename(
title="Select file",
filetypes=(("html files", "*.html"), ("all files", "*.*")))
if not self.filename:
return
name = str(self.filename).split("/")[-1]
if os.path.exists(
os.path.join(os.sys.path[0], "data", "dataBase",
name + ".db")):
db = os.path.join(os.sys.path[0], "data", "dataBase", name + ".db")
DBhandler.loadDB(db)
else:
location = "offline"
print("value is", name, location)
DBhandler.createDB(name, location)
self.updatDBLable()
chatParser.resetGlobal()
d = importCancel(self)
d.top.grab_set()
d.top.widgetName = "importCancel"
t1 = Thread(target=self.theadImport)
t1.start()
def get_my_promotion(shopid):
'''得到店铺已有的关键字推广,返回的格式：当一个商品的不同关键字的出价不一样时，在不同行中显示
[{"productid":"id","productname":"name",
"price":"price",
"keywords":["keyword1","keyword2"]},
{"productid":"id","productname":"name",
"keywords":["keyword1","keyword2"],
"price":"price"}
'''
#首先得到该店铺一条推广的记录：[(productid,productname,keyword,price)]
shop_promotions = DBhandler.shop_promotion(shopid)
if len(shop_promotions) < 1:
return "0"
#将数据转换为：{ productid:{price:[keyword1,keyword2]}}和{productid:productname}的格式
shop_promotion_format = dict()
for id, name, word, price in shop_promotions:
shop_promotion_format.setdefault(id, {})
shop_promotion_format[id].setdefault(price, [])
shop_promotion_format[id][price].append(word)
#将上述的格式转换为json的格式
js = '['
for id, item1 in shop_promotion_format.items():
for price, keywords in item1.items():
js += '{"productid":"' + id + '","productname":"' + DBhandler.get_productname(
id) + '","price":"' + str(price) + '","keywords":['
for keyword in keywords:
js += '"' + keyword + '",'
js = js[:-1] + ']},'
js = js[:-1] + ']'
return js
def recommend_on_itemsimilarity(customer_id, T0, N=20, beta=1):
'''基于物品相似度的推荐'''
#首先从数据库中得到物品的相似度列表 {物品：{相似物品：相似度}}
similarity = DBhandler.get_item_similarity()
#从数据库中得到该顾客的购买记录{购买的物品：时间，购买的物品：时间}
related_commodity = DBhandler.orders_based_on_customerId(customer_id)
#related_commodity = {'Snakes on a Plane': 4.5,'Superman Returns': 4.0,'You, Me and Dupree': 1.0}
#找到和购买的物品相似度较高的前N个产品
rec_commodities = {}
rec_reasons = {} #表示推荐的解释{推荐的物品：{曾经购买过的物品：相似度}}
for item, time in related_commodity.items():
for sim_item, sim in similarity[item].items():
if sim_item in related_commodity: continue #如果相似的物品是用户曾经购买过的话，直接跳过
#tmp = sim/(1+beta*abs(T0-time))
tmp = sim / (1 + beta * getIntervalDays(time, T0))
rec_commodities[sim_item] = rec_commodities.get(sim_item, 0) + tmp
#提供推荐的解释
rec_reasons.setdefault(sim_item, {})
rec_reasons[sim_item][item] = tmp
rec = sorted(rec_commodities.items(), key=lambda x: x[1], reverse=True)[:N]
rec_reason = [(item,
sorted(reasons.items(), key=lambda x: x[1],
reverse=True)[0][0])
for item, reasons in rec_reasons.items()]
return rec, rec_reason
def run_sql_string():
data = None
print request.json
if not request.json:
return jsonify({'successful': False, 'message': '请求的数据格式不正确'})
if request.method == 'POST':
json_sqlString = request.json['sql_string']
json_flg = request.json['flg']
if json_flg == 'select_one':
datas = dbs.get_one_sql(json_sqlString)
elif json_flg == 'select_all':
data = dbs.get_all_sql(json_sqlString)
print 'data====', data
datas = json.dumps(data, cls=DateEncoder)
elif json_flg == 'update':
dbs.update_sql(json_sqlString)
return Response(response=datas,
mimetype="application/json",
status=200)
# return jsonify({'success': True, 'message': u'成功响应', 'data': datas})
else:
return jsonify({
'success': False,
'message': u'请使用POST请求',
'data': None
})
def to_be_checked_headlines():
'''管理员界面显示所有的头条申请,返回的格式,当没有头条的时候返回“0”：
[{"shopname":"name","productid":"productid","productname":"productname",
"price":"99.9","date":"2018/05/06"},
{"shopname":"name","productid":"productid","productname":"productname",
"price":"99.9","date":"2018/05/06"}]'''
#得到头条的基本信息：[(id,price,date)]
headlines = DBhandler.get_to_be_checked_headlines()
if len(headlines) < 1:
return "0"
#将以上的信息组装成js的格式
js = '['
for id, price, date in headlines:
js += '{"shopname":"' + DBhandler.get_shopname(
id
) + '","productid":"' + id + '","productname":"' + DBhandler.get_productname(
id) + '",'
js += '"price":"' + str(price) + '","date":"' + date + '"},'
js = js[:-1] + ']'
return js
def sign(conn, db_conn, db_file):
while True:
sign = conn.recv(1024)
sign = pickle.loads(sign)
if sign[0] == 'up':
# Попытка добавления нового пользователя
print(pt(), 'Запрос на регистрацию')
r_id = DBhandler.add_user(db_conn, sign[1], sign[2])
if not r_id:
# Сообщение об ошибке
resp = (False, 'Такой пользователь уже существует!')
resp = pickle.dumps(resp)
conn.send(resp)
print(pt(), 'Запрос на регистрацию был отменён')
continue
resp = (True, 'Пользователь зарегистрирован')
resp = pickle.dumps(resp)
conn.send(resp)
break
elif sign[0] == 'in':
# Попытка залогиниться
print(pt(), 'Запрос на авторизацию')
cur_id = DBhandler.check_user(db_conn, sign[1], sign[2])
if not cur_id:
resp = (False, 'Неправильный логин и/или пароль!')
resp = pickle.dumps(resp)
conn.send(resp)
print(pt(), ' Запрос на авторизацию был отменён')
continue
resp = (True, 'Вход в систему завершён успешно')
resp = pickle.dumps(resp)
conn.send(resp)
print(pt(), 'Пользователь авторизирован')
break
def getRecommendation(customer_id, N=10, beta=1):
'''从订单表中得到该顾客的购买记录，如果购买的记录为0的话，根据他的性别、年龄来推荐和他相同的人喜爱的商品'''
customer_orders = DBhandler.orders_based_on_customerId(
customer_id) #{item:date}
#如果订单的数量大于0的话，根据基于时间的ItemCF和基于内容的推荐得到初始的推荐列表
rec_reason = None
if len(customer_orders) < 1:
personal_recommendation = recommend_on_features(customer_id,
T0=getCurrentDay(),
N=2 * N,
beta=beta)
else:
personal_recommendation, rec_reason = recommend_on_itemsimilarity(
customer_id, T0=getCurrentDay(), N=2 * N, beta=beta)
'''
personal_recommendation = [('Superman Returns', 1.0107142857142857),
('Snakes on a Plane', 0.9645604395604396),
('The Night Listener', 0.7967032967032968),
('You, Me and Dupree', 0.7130718954248366),
('Lady in the Water', 0.6102941176470589),
('Just My Luck', 0.46637426900584794)]'''
#对初始的推荐列表进行过滤：过滤掉不属于商品推广列表中的的商品和用户不喜欢的商品
#排序：根据相似度和推荐的出价来对推荐的结果加权，并据此进行排序，得到最终推荐的商品的ID
rec_commodities = DBhandler.get_recommend_commodities()
dislike_commodities = DBhandler.get_dislike_commodities(
customer_id) #用户不喜欢的商品
'''
rec_commodities = [('Superman Returns', 2),
('The Night Listener', 1),
('You, Me and Dupree', 1.5),
('Just My Luck',0.5)]'''
rec_commodities = {item: price
for item, price in rec_commodities} #将推荐的商品转换为字符串的形式
recommendation = dict()
for item, sim in personal_recommendation:
if item not in rec_commodities: continue
if item in dislike_commodities: continue
recommendation[item] = sim * rec_commodities[item]
rec_commodities_id = [
item for item, rank in sorted(
recommendation.items(), key=lambda x: x[1], reverse=True)
]
#得到商品的推荐理由
recommend_reason = {}
if rec_reason is not None:
for rec, reason in rec_reason:
recommend_reason[rec] = DBhandler.get_productname(reason)
#还需要一个方法将商品的ID转换为商品属性信息，并以json的格式返回
return getCommodityAttributes(rec_commodities_id, recommend_reason)
def loadDB(self):
dateBase = os.path.join(os.sys.path[0], "data", "dataBase")
self.filename = filedialog.askopenfilename(
initialdir=dateBase,
title="Select file",
filetypes=(("db files", "*.db"), ("all files", "*.*")))
if not self.filename:
return
DBhandler.loadDB(self.filename)
self.updatDBLable()
def updatDBLable(self):
message = "Game Name: " + DBhandler.getGameName(
) + "| URL: " + DBhandler.getURL()
if DBhandler.getURL() == "offline":
self.frame.offline.set(1)
self.frame.offline_checkBox['state'] = "disabled"
else:
self.frame.offline.set(0)
self.frame.offline_checkBox['state'] = "normal"
self.currentDB_string.set(message)
def event_processor(event_queue):
"""事件处理进程，消费者"""
while 1:
# 读队列会阻塞进程
event = event_queue.get()
time_at = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
if event['call_back'] != None:
if event['event_name'] == 'CHANNEL_CREATE':
sql = cc_sql.format(time_at, event['channal_uuid'])
logger.info('[sql]:........CHANNEL_CREATE........ %s' % sql)
db.update_sql(sql)
#更新fs_host 线路数+1
host_sql = chc_update_line.format(event['host_id'])
logger.info(
'[sql]:........CHANNEL_CREATE.. table...host line+1...... %s'
% host_sql)
db.update_sql(host_sql)
elif event['event_name'] == 'CHANNEL_ANSWER':
sql = ca_sql.format(time_at, event['channal_uuid'])
logger.info('[sql]:.........CHANNEL_ANSWER...... %s' % sql)
db.run_sql(sql)
elif event['event_name'] == 'CHANNEL_HANGUP_COMPLETE':
call_back = event['call_back']
host_id = event['host_id']
user_id = event['user_id']
call_id = event['call_id']
logger.info(
'[ call_back----> %s ,user_id---->%s, call_id---->%s]' %
(call_back, user_id, call_id))
logger.info('[ is_test----> %s ]' % event['is_test'])
if call_back != None and call_back == 'true':
try:
sql2 = chc_host_sql.format(int(host_id))
logger.info('[ sql2 :----> fs_host line_use - 1 ]%s ' %
sql2)
db.update_sql(sql2)
sql3 = chc_sql.format('finish', time_at,
event['Channel-Call-State'],
event['Hangup-Cause'],
event['channal_uuid'])
logger.info(
'[sql3 :..........CHANNEL_HANGUP_COMPLETE....... ]%s'
% sql3)
db.update_sql(sql3)
# 回调函数
callback_aliyun(event['channal_uuid'], user_id,
call_id)
except Exception as e:
logger.info('hangup_complete execute sql error %s ' %
e)
else:
print '[ ***** curent event is not platform ***** ]'
def trainTimeBasedItemCF(alpha=1, sim_counts=0.8):
'''训练基于时间的ItemCF模型：主要是得到物品的相似度表，然后更新到数据库中'''
#计算物品的相似度
##得到用户和物品的数据：{用户：{物品：日期}}
prefs = DBhandler.all_customer_orders()
##计算相似度
similarity = TimeBasedItemSimilarity(prefs,
alpha=alpha,
sim_counts=sim_counts)
#将物品的相似度更新到数据库中
DBhandler.update_item_similarity(similarity)
def sqlreq(conn, flag, args):
print(pt(), 'запрос на просмотр сообщений от пользователя ', args[0])
msgs = DBhandler.pull_messages(conn, flag, args[0], args[1], args[2])
if not msgs:
return False
print(pt(), 'Список сообщений был отправлен пользователю ', args[0])
return msgs
def analyzeByTagAndNameByDateRange(name, tagNameList, dateA, dateB, offline):
if not offline:
chatParser.addScrapParseToDB()
analyzeDB(
DBhandler.getMessagesByTagAndNameByDateRange(tagNameList, name, dateA,
dateB))
return returnStats()
def getCommodityAttributes(commodity_ids, recommend_reason):
'''根据给定的商品列表得到商品的属性信息，并以json的格式返回
commodity_ids : 列表，表示推荐的商品的ID
'''
#得到商品的属性：[ (商品id，名称，图片，店铺，价格) ]
attributes = DBhandler.get_recommend_commodities_attributes(commodity_ids)
'''
attributes = [(1,"苹果","/web/image/apple.png","涛哥的小店",1.5),
(2,"梨","/web/image/peach.png","涛哥的小店",0.5),
(3,"香蕉","/web/image/banana.png","涛哥的小店",1)]'''
#将商品信息转换为json格式
'''
[{"id":commodity_id, "name":name, "pic":picture_url, "price":price,"reason":"reason"},
{"id":commodity_id, "name":name, "pic":picture_url, "price":price,"reason":"reason"}
]
'''
has_reason = 0
if len(recommend_reason) > 0: has_reason = 1
js = "["
for i in range(len(attributes)):
item = attributes[i]
js += '{"id":"' + str(
item[0]) + '","name":"' + item[1] + '","pic":"' + item[
2] + '","shop":"' + item[3] + '","price":"' + str(
item[4]) + '"'
if (has_reason):
js += ',"reason":"' + recommend_reason[item[0]] + '"'
js += '},'
js = js[:-1] + ']'
return js
def recommend_on_features(customer_id, T0, N=20, beta=1):
'''根据他的性别、年龄来推荐和他相同的人喜爱的商品'''
#从数据库中找到和该用户特征相似的用户,得到相似用户的：购买商品、购买时间 { 用户：{购买物品：购买日期}}
prefs = DBhandler.orders_based_on_customer_features(customer_id)
#计算相似用户的商品的：流行度（基于时间的流行度）
return popularItem(prefs, T0=T0, N=N, beta=beta)
def can_use(self):
if not self.conn.connected:
return 'disconnect'
num = db.get_one_sql(get_free_line_sql.format(self.host_id))
if num <= 0:
return 'full'
return 'free'
def call(self, item):
task_id = item[3]
call_id = item[0]
uuid = str(item[2])
number = str(item[1])
is_success = self.fs_api(uuid=uuid,
number=number,
task_id=task_id,
call_id=call_id,
host_id=self.host_id,
gateway=self.gateway)
logger.info('[ call is_success = %s ]' % is_success)
if is_success:
db.update_sql(chc_host_sql.format(self.host_id))
else:
logger.info('self.bgapi.....retrun %s' % is_success)
def analyzeByTagToday(tagName, offline):
if not offline:
chatParser.addScrapParseToDB()
startToday = datetime(datetime.today().year,
datetime.today().month,
datetime.today().day)
analyzeDB(DBhandler.getMessagesWithTagsBYDate(tagName, startToday))
return returnStats()
def getPopularItem(N=10, beta=1):
'''返回在所有顾客中最热门的商品'''
prefs = DBhandler.all_customer_orders()
#计算相似用户的商品的：流行度（基于时间的流行度）
popular_item = popularItem(prefs, T0=getCurrentDay(), N=N, beta=beta)
items = [item for item, popularity in popular_item]
return getCommodityAttributes(items, {})
def count(self,options):
query = DBhandler.getQuery(options)
condition = query["condition"]
where = Encoder.enQuery(self,condition)
return self.db(where).count()
def analyzeByTagAndNameToday(name, tagNameList, offline):
if not offline:
chatParser.addScrapParseToDB()
startToday = datetime(datetime.today().year,
datetime.today().month,
datetime.today().day)
analyzeDB(
DBhandler.getMessagesByTagAndNameByDate(tagNameList, name, startToday))
return returnStats()
def analyzeToday(offline):
if not offline:
chatParser.addScrapParseToDB()
startToday = datetime(datetime.today().year,
datetime.today().month,
datetime.today().day)
analyzeDB(DBhandler.getRollresultDateTime(startToday))
return returnStats()
def get_current_headlines():
'''得到当前的头条,格式和上面的完全一样'''
headlines = DBhandler.current_headlines()
if len(headlines) < 1:
return "0"
#将以上的信息组装成js的格式
js = '['
for id, price, date in headlines:
js += '{"shopname":"' + DBhandler.get_shopname(
id
) + '","productid":"' + id + '","productname":"' + DBhandler.get_productname(
id) + '",'
js += '"price":"' + str(price) + '","date":"' + date + '"},'
js = js[:-1] + ']'
return js
def fs_api(self, uuid, number, task_id, flow_id, call_id, host_id,
gateway):
if not gateway:
gateway = 'sofia/gateway/gw1'
if self.conn.connected:
channel_vars = 'ignore_early_media=true,absolute_codec_string=g729,' \
'origination_uuid=%s,task_id=%s,flow_id=%s,call_id=%s,host_id=%s,is_test=%s' % \
(uuid, task_id, flow_id,call_id, host_id, '1')
command = "originate {%s}%s/%s &python(callappv2.Bot)" % (
channel_vars, gateway, number)
logger.error('Invoke fs api:\n%s' % command)
self.conn.bgapi(command)
try:
time_at = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
db.update_sql(update_call_sql.format(time_at, uuid))
except Exception as e:
print 'eror', e.message
return True
else:
return False
def find(self,options):
query = DBhandler.getQuery(options)
condition = query['condition']
order = query['order']
limit = query['limit']
where = Encoder.enQuery(self,condition)
rows = self.db(where).select(orderby=eval(order),limitby=limit)
return rows
def delete_keywords(js):
'''删除关键字推广，js的格式：
{"productid":"p201804281620","keywords":["新款"]}'''
#将上述格式转换为[(productid,keyword)]的格式
promotion = json.loads(js)
promotion_info_format = list()
productid = promotion["productid"]
for keyword in promotion["keywords"]:
promotion_info_format.append((productid, keyword))
#返回删除的结果
return DBhandler.delete_keywords(promotion_info_format)
def run_sql_string():
data = None
if not request.json:
return jsonify({'successful': False, 'message': '请求的数据格式不正确'})
if request.method == 'POST':
json_sqlString = request.json['sql_string']
json_flg = request.json['flg']
if json_flg == 'select_one':
data = dbs.get_one_sql(json_sqlString)
elif json_flg == 'select_all':
data = dbs.get_all_sql(json_sqlString)
# print 'data====',data
elif json_flg == 'update':
dbs.update_sql(json_sqlString)
return jsonify({'success': True, 'message': u'成功响应', 'data': data})
else:
return jsonify({
'success': False,
'message': u'请使用POST请求',
'data': None
})
def addToDb(chatContent):
global current, size, cancel, status
static.timeStamp = ""
x = 1
status = "Parsing data"
activeTags.clear()
activeTags.extend(DBhandler.getActiveTags())
for c in chatContent:
if (not cancel):
current = x
#test = c.attrs.get("data-messageid")
#print(test)
#print(activeTags)
s = c["class"]
if "rollresult" in s:
addRollresult(c)
elif "general" in s:
addGeneral(c)
pass
elif "emote" in s:
addEmote(c)
pass
else:
print("unknown message type: ", c)
x += 1
else:
print("chatPar has been canceled")
return
addActive()
status = "Adding messages data to DB"
print(status)
DBhandler.addManyToMessageTable(allMessage)
status = "Adding user data to DB"
print(status)
DBhandler.addManyToUserIDTable(allUserID)
status = "Adding Formula and Dice data to DB"
print(status)
DBhandler.addManyFormulaAndDice(allFormulaDice)
status = "Adding tags data to DB"
print(status)
DBhandler.addManyToTag(allTags)
status = "DONE"
def add_promotion_keywords(js):
'''为商品添加关键字，js的格式：
{"productid":"p201804281620",
"keywords":["新款","潮流"],
"price":"0.45"}'''
#首先要将数据转换为[(productid,keyword,price)]的格式
promotions = json.loads(js)
promotions_info_format = list()
productid = promotions["productid"]
price = promotions["price"]
for keyword in promotions["keywords"]:
promotions_info_format.append((productid, keyword, price))
#修改或插入推荐的信息：
state = DBhandler.add_alter_promotion(promotions_info_format)
while state == "0": #当删除不成功时，连续删除
#如果插入失败的话需要取消之前插入的索引
state = DBhandler.cancel_add_alter_keywords(promotions_info_format)
return state
def event_processor(event_queue):
"""事件处理进程，消费者"""
while 1:
# 读队列会阻塞进程
event = event_queue.get()
time_at = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
if event['event_name'] == 'CHANNEL_CREATE':
sql = cc_sql.format(time_at, event['channal_uuid'])
logger.info('[sql]:........CHANNEL_CREATE........ %s' % sql)
db.update_sql(sql)
#更新fs_host 线路数+1
host_sql = chc_update_line.format(event['host_id'])
logger.info(
'[sql]:........CHANNEL_CREATE.. table...host line+1...... %s' %
sql)
db.update_sql(host_sql)
elif event['event_name'] == 'CHANNEL_ANSWER':
sql = ca_sql.format(time_at, event['channal_uuid'])
logger.info('[sql]:.........CHANNEL_ANSWER...... %s' % sql)
db.run_sql(sql)
elif event['event_name'] == 'CHANNEL_HANGUP_COMPLETE':
sql = chc_sql.format('finish', time_at,
event['Channel-Call-State'],
event['Hangup-Cause'], event['channal_uuid'])
logger.info('[sql]:..........CHANNEL_HANGUP_COMPLETE....... %s' %
sql)
db.run_sql(sql)
task_id = event['task_id']
host_id = event['host_id']
logger.info('task_id....%s' % task_id)
if task_id != None:
logger.info('-------is_test------%s ' % event['is_test'])
# if event['is_test'] and event['is_test'] != '0':
sql = chc_host_sql.format(int(host_id))
logger.info('[execute sql]...%s' % sql)
db.run_sql(sql)
HttpClientPost(event['channal_uuid'])
def get_keywords():
'''得到推广的关键字，返回的格式：
[{"keywordid":"id","keyword":"keyword"},
{"keywordid":"id","keyword":"keyword"} ]'''
keywords = DBhandler.get_keywords()
js = '['
for id, keyword in keywords:
js += '{"keywordid":"' + id + '","keyword":"' + keyword + '"},'
js = js[:-1] + ']'
return js
def find(self,options):
simplequery = DBhandler.simple("materiales","4","id",">")
complexquery = DBhandler.complex("materiales","4","nombre",">",{'id':"2",'nombre':"'Cl'"})
#Default values
print "\n\n"
print "*****************************************************************************************************"
print ' Printing queries for testing purposes'
print "*****************************************************************************************************"
print '\n\nSimple query: \n'
print 'query: '
print simplequery
print 'response: '
print self.db.executesql(simplequery)
print '\nComplex query: \n'
print 'query: '
print complexquery
print 'response: '
print self.db.executesql(complexquery)
print "\n"
return self.db().select(self.table.ALL)