def data_process(): """ Function: data_process() Description: 多进程来调用data_operate() Input: 无 Return: :return :无 Others:接收data_get()函数的返回值,根据得到的数据长度将数据分为4部分, 启动四个进程来执行data_operate()函数,将四部分数据分别分给四个进程来执行. """ start = datetime.datetime.now() print 'start transport' p = Pool() all, mac = data_get() num = all / 4 for i in range(3): p.apply_async(data_operate, args=(mac[num * i:num * (i + 1)], )) p.apply_async(data_operate, args=(mac[num * 3:all], )) p.close() p.join() sql = "delete from hive_data.inshop_time where flag=1" mysqlExecute(mysqlConnect(), sql) sql = "update hive_data.inshop_time set flag=3" mysqlExecute(mysqlConnect(), sql) data_analysis() end = datetime.datetime.now() print 'All subprocesses done,take time:' + str(end - start)[0:-7]
def save_result(): all_number, inshop_number = predict('day_number') hour_number, hour_number_inshop = predict('hour_number') week_number, week_number_inshop, temperature = predict('week_number') weather, holiday, weather_predict, holiday_predict, all_number1, inshop_number1 = get_data( 'day_number') hour_temperature, temperature, hour_number1, hour_number_inshop1 = get_data( 'hour_number') hour_temperature_str = "" hour_number_str = "" hour_number_inshop_str = "" for i in range(15): hour_temperature_str += (str(temperature[i]) + "#") hour_number_str += (str(int(hour_number[i])) + "#") hour_number_inshop_str += (str(int(hour_number_inshop[i])) + "#") #print hour_temperature_str[:-1],hour_number_str[:-1],hour_number_inshop_str[:-1] week_number_str = "" week_number_inshop_str = "" for i in range(7): #week_temperature_str+=(str(temperature[i])+"#") week_number_str += (str(int(week_number[i])) + "#") week_number_inshop_str += (str(int(week_number_inshop[i])) + "#") print week_number_str, week_number_inshop_str time = datetime.datetime.now() time = time + datetime.timedelta(days=+1) time = datetime.datetime.strftime(time, "%Y-%m-%d 00:00:00") sql = "insert into hive_data.data_predict(all_number,inshop_number,weather,holiday,time,temperature,week_temperature,hour_temperature,hour_number,hour_number_inshop,week_number,week_number_inshop) values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" % ( all_number, inshop_number, weather_predict, holiday_predict, time, temperature[0], '', hour_temperature_str[:-1], hour_number_str[:-1], hour_number_inshop_str[:-1], week_number_str[:-1], week_number_inshop_str[:-1]) print sql mysqlExecute(mysqlConnect(), sql)
def passenger_type(): time = datetime.datetime.now() time1 = time.strftime("%Y-%m-%d 08:00:00") sql = "select circle_time from data_set where phone_mac in(select distinct phone_mac " \ "from inshop_time where time between '%s' and '%s')" % (time1, time) result = mysqlExecute(mysqlConnect(), sql) old = float(len(result.fetchall())) sql = "select distinct phone_mac from data where time between '%s' and '%s'" % ( time1, time) result = mysqlExecute(mysqlConnect(), sql) all_number = float(len(result.fetchall())) new = all_number - old data = {'new': new, 'old': old, 'all': all_number} data = json.dumps(data) return data
def chart(): with open(os.path.abspath('..') + "/hive_count_all", "r") as fp: count = fp.readline() with open(os.path.abspath('..') + "/hive_count_all_last_week", "r") as fp: last_week = fp.readline() print last_week with open(os.path.abspath('..') + "/hive_count_all_this_week", "r") as fp: this_week = fp.readline() with open(os.path.abspath('..') + "/hive_count_all_this_week_in", "r") as fp: this_week_in = fp.readline() with open(os.path.abspath('..') + "/hive_count_all_last_week_in", "r") as fp: last_week_in = fp.readline() week_data = list() week = list() week_in = list() for i in range(7): try: time1 = getTime(i * 24 * 3600) sql = """select time_12 from hive_data.history_count WHERE time = "{}" """.format(time1 + ' 00:00:00') try: data = mysqlExecute(mysqlConnect(),sql)._rows[0][0] except Exception as e: data = 0 week_data.append(data) week.append(time1) sql = """select inshop_12 from hive_data.history_count WHERE time = "{}" """.format(time1 + ' 00:00:00') try: data_in = mysqlExecute(mysqlConnect(),sql)._rows[0][0] except Exception as e: data_in = 0 week_in.append(data_in) except Exception as e: print e week.append(time1) week_data.append(105) week_in.append(72) context = { 'all': count, 'last_week':last_week, 'this_week':this_week, 'week':week, 'week_data':week_data, 'week_in':week_in, 'last_week_in':last_week_in, 'this_week_in':this_week_in } print context return render_template('total.html',data=context)
def time_stay_query(time): """ Function: time_stay_query() Description: 根据时间查询驻店时长并按比例返回 Input: 时间。 Return: :return :json,包含四个时间的比例 Others: 无 """ time1 = datetime.datetime.strftime(time, "%Y-%m-%d 08:00:00") a = datetime.datetime.strftime(time, "%Y-%m-%d %H:%M:%S") sql = "select stay_time from inshop_time where flag=0 and time between '" + \ time1 + "' and '" + a + "'" result = mysqlExecute(mysqlConnect(), sql) s = result.fetchall() s = list(s) time1 = 0 time2 = 0 time3 = 0 time4 = 0 for i in s: if '1' == list(i)[0]: time1 += 1 elif '2' == list(i)[0]: time2 += 1 elif '3' == list(i)[0]: time3 += 1 else: time4 += 1 time = time4 + time3 + time2 + time1 if time == 0: time = 1 time1 = 1 data = {"a": time1, "b": time2, "c": time3, "d": time4} data = json.dumps(data) return data
def inshop_time(): """ Function: inshop_time() Description: 根据时间查询驻店时长。 Input: 查询时间范围。 Return: 包含查询结果的json串。 Others: 无。 """ sql = "Select time,inshop_8,inshop_12,inshop_15,inshop_18,inshop_21 from history_count" cur = mysqlExecute(mysqlConnect(), sql) data = list() for day in cur._rows: data.append([ datetime.strptime( str(day[0])[:11] + "08:00:00", "%Y-%m-%d %H:%M:%S"), day[1] ]) data.append([ datetime.strptime( str(day[0])[:11] + "12:00:00", "%Y-%m-%d %H:%M:%S"), day[2] ]) data.append([ datetime.strptime( str(day[0])[:11] + "15:00:00", "%Y-%m-%d %H:%M:%S"), day[3] ]) data.append([ datetime.strptime( str(day[0])[:11] + "18:00:00", "%Y-%m-%d %H:%M:%S"), day[4] ]) data.append([ datetime.strptime( str(day[0])[:11] + "21:00:00", "%Y-%m-%d %H:%M:%S"), day[5] ]) return json.dumps(data, cls=MyEncoder)
def passenger(command, time): """ Function: passenger() Description: 用于查询数据库,计算客流量,入店量和入店率. Input: command选择查询客流量或入店量或入店率,时间。 Return: :return :.json,包含两部分,查询结果和输入的时间 Others: 无 """ if command == "total_number": a = time sql = "select count(distinct phone_mac) from data where time between '%s' and '%s'" % ( str(time + datetime.timedelta(seconds=-3)), str(a)) result = mysqlExecute(mysqlConnect(), sql) s = result.fetchone()[0] data = {"a": s, "b": str(time)} data = json.dumps(data) return data elif command == "inshop_number": a = time sql = "select count(distinct phone_mac) from inshop_time where flag=1 and time between '%s' and '%s'" % ( str(time + datetime.timedelta(seconds=-10)), str(a)) result = mysqlExecute(mysqlConnect(), sql) s = result.fetchone()[0] data = {"a": s, "b": str(time)} data = json.dumps(data) print data return data elif command == "inshop_rate": a = time sql = "select count(distinct phone_mac) from data where time between '%s' and '%s'" % ( str(time + datetime.timedelta(seconds=-6)), str(a)) result = mysqlExecute(mysqlConnect(), sql) total_number = float(result.fetchone()[0]) if total_number == 0: data = {"a": 0, "b": str(time)} data = json.dumps(data) return data sql = "select count(distinct phone_mac) from data where phone_range<=10 and time between '%s' and '%s'" % ( str(time + datetime.timedelta(seconds=-6)), str(a)) result = mysqlExecute(mysqlConnect(), sql) inshop_number = float(result.fetchone()[0]) s = inshop_number / total_number data = {"a": s, "b": str(time)} data = json.dumps(data) return data
def data_operate(mac): """ Function: data_operate() Description: 计算每日的来访周期并存入数据库 Input: mac地址 Return: :return :无 Others:根据传入的mac地址来判断data_set中是否有该mac,若没有则将mac和时间插入data_set, 若有则根据新老时间来计算来访周期,删除旧数据,将mac,时间和来访周期存入表中. """ pattern = re.compile(r'\d+') time1 = time.strftime("%Y-%m-%d 00:00:00") print len(mac) for i in mac: # and exists(select * from data_set where phone_mac='%s') sql = "select time from data_set where phone_mac ='%s'" % (list(i)[0]) result = mysqlExecute(mysqlConnect(), sql) res = result.fetchone() if res is not None: circle_time = str(time - res[0]) match = pattern.match(circle_time) circle_time = match.group() sql = "delete from data_set where phone_mac='%s'" % list(i)[0] mysqlExecute(mysqlConnect(), sql) sql = "insert into data_set(phone_mac,time,circle_time) values('%s','%s','%s')" % ( list(i)[0], time1, circle_time) mysqlExecute(mysqlConnect(), sql) else: sql = "insert into data_set(phone_mac,time) values('%s','%s')" % ( list(i)[0], time1) mysqlExecute(mysqlConnect(), sql)
def get_data(flag): sql = "select lat,lon from hive_data.data limit 1" res = mysqlExecute(mysqlConnect(), sql) res = res.fetchone() #print res if flag == "day_number": time = datetime.datetime.now() time = datetime.datetime.strftime(time, "%Y%m%d") weather_predict = perdict_info.get_weather(res[0], res[1], 'tomorrow') holiday_predict = perdict_info.get_day_type(time) sql = "select all_number,inshop_number,weather,holiday from data_predict" result = mysqlExecute(mysqlConnect(), sql) result = result.fetchall() result = list(result) all_number = [] inshop_number = [] weather = [] holiday = [] for i in result: all_number.append(int(list(i)[0])) inshop_number.append(int(list(i)[1])) weather.append(int(list(i)[2])) holiday.append(int(list(i)[3])) return weather, holiday, weather_predict, holiday_predict, all_number, inshop_number elif flag == "hour_number": data = perdict_info.get_temperature(res[0], res[1], "tomorrow") temperature = [] for i in data: temperature.append(int(i)) sql = "select hour_temperature,hour_number,hour_number_inshop from data_predict" result = mysqlExecute(mysqlConnect(), sql) result = list(result.fetchall()) hour_temperature = [] hour_number = [] hour_number_inshop = [] for i in result[0][0].split('#'): hour_temperature.append(int(i)) for i in result[0][1].split('#'): hour_number.append(int(i)) for i in result[0][2].split('#'): hour_number_inshop.append(int(i)) return hour_temperature, temperature, hour_number, hour_number_inshop
def predict(flag): if flag == 'day_number': weather, holiday, weather_predict, holiday_predict, all_number, inshop_number = get_data( flag) return bp(weather, holiday, all_number, inshop_number, weather_predict, holiday_predict, 'day_number') elif flag == 'hour_number': hour_temperature, temperature, hour_number, hour_number_inshop = get_data( flag) return bp(hour_temperature, '', hour_number, hour_number_inshop, temperature, '', 'hour_number') elif flag == 'week_number': sql = "select lat,lon from hive_data.data limit 1" res = mysqlExecute(mysqlConnect(), sql) res = res.fetchone() weather_num, a, b, c, all_number, inshop_number = get_data( 'day_number') temp_predict, weather_predict = perdict_info.get_weektemp( res[0], res[1]) sql = "select temperature from data_predict" res = mysqlExecute(mysqlConnect(), sql) res = list(res.fetchall()) #print res temperature = [] for i in res: temperature.append(list(i)[0]) temp2 = [] for i in temperature: temp2.append(int(i)) all_num = [] inshop_num = [] for i in range(len(temp_predict)): alls, inshop = bp(temp2, weather_num, all_number, inshop_number, temp_predict[i], weather_predict[i], 'day_number') all_num.append(alls) inshop_num.append(inshop) return all_num, inshop_num, temp_predict[0]
def inshop1(): sql = "select * from data_set" cur = mysqlExecute(mysqlConnect(), sql) max = 0 max_name = list() print cur._rows[0] try: for i in cur._rows: if int(i[2]) >= max: if int(i[2]) == max: max_name.append(i[0]) else: max_name = [i[0],] max = int(i[2]) except Exception as e: print e ONE_PAGE_OF_DATA = 10 try: curPage = int(request.args.get('curPage')) pageType = str(request.args.get('pageType')) allPage = int(request.args.get('allPage')) except Exception: curPage = 1 allPage = 1 pageType = '' # 判断点击了【下一页】还是【上一页】 if pageType == 'pageDown': curPage += 1 elif pageType == 'pageUp': curPage -= 1 startPos = (curPage - 1) * ONE_PAGE_OF_DATA endPos = startPos + ONE_PAGE_OF_DATA allItemCount = len(cur._rows) posts = cur._rows[startPos:endPos] if curPage == 1 and allPage == 1: # 标记1 allPage = allItemCount // ONE_PAGE_OF_DATA remainPost = allItemCount % ONE_PAGE_OF_DATA if remainPost > 0: allPage += 1 context = {'items': posts, 'allPage': allPage, 'curPage': curPage, 'allItem': allItemCount, 'max':[max,max_name] } # print context return render_template('circle_time.html',data=context)
def deep_jump(): sql = "select stay_time from inshop_time where flag=0" result = mysqlExecute(mysqlConnect(), sql) result = result.fetchall() count_deep = 0 count_all = 0 count_jump = 0 for i in list(result): if '1' == list(i)[0] or '2' == list(i)[0]: count_jump += 1 count_all += 1 count_deep = count_all - count_jump data = {'deep': count_deep, 'jump': count_jump, 'all': count_all} data = json.dumps(data) return data
def data_get(): """ Function: data_get() Description: 查询inshop_time中当日flag为0的数据,每个mac地址查询一次 Input: 无 Return: :return :查询到的数据的个数和全部的数据 Others: 无 """ time1 = datetime.datetime.strftime(time, "%Y-%m-%d 00:00:00") time2 = datetime.datetime.strftime(time, "%Y-%m-%d 23:00:00") sql = "select distinct phone_mac from inshop_time where flag=0 and time between '%s' and '%s'" % ( time1, time2) result = mysqlExecute(mysqlConnect(), sql) macs = result.fetchall() mac = list(macs) print len(mac) return (len(mac), mac)
def active_rate(): sql = "select circle_time from data_set" result = mysqlExecute(mysqlConnect(), sql) result = result.fetchall() count1 = count2 = count3 = count4 = 0 for i in list(result): if 0 < int(list(i)[0]) <= 7: count1 += 1 elif int(list(i)[0]) in range(8, 15): count2 += 1 elif int(list(i)[0]) in range(15, 30): count3 += 1 elif int(list(i)[0]) >= 30: count4 += 1 data = {'high': count1, 'middle': count2, 'low': count3, 'sleep': count4} data = json.dumps(data) return data
def passenger(): """ Function: passenger() Description: 根据时间查询客流量,入店量和入店率。 Input: 查询时间范围和查询内容的标记。 Return: 包含查询结果的json串。 Others: 无。 """ sql = "Select time,time_8,time_12,time_15,time_18,time_21 from history_count" error = False try: cur = hiveExecute(hiveConnect(), sql) except Exception: error = True try: cur = mysqlExecute(mysqlConnect(), sql) except Exception: writeLog('无法从hive或者mysql数据库获取历史数据!', '[ERROR]') return None data = list() if error: temp = cur._rows else: temp = cur.fetch() for day in temp: data.append([ datetime.strptime( str(day[0])[:11] + "08:00:00", "%Y-%m-%d %H:%M:%S"), day[1] ]) data.append([ datetime.strptime( str(day[0])[:11] + "12:00:00", "%Y-%m-%d %H:%M:%S"), day[2] ]) data.append([ datetime.strptime( str(day[0])[:11] + "15:00:00", "%Y-%m-%d %H:%M:%S"), day[3] ]) data.append([ datetime.strptime( str(day[0])[:11] + "18:00:00", "%Y-%m-%d %H:%M:%S"), day[4] ]) data.append([ datetime.strptime( str(day[0])[:11] + "21:00:00", "%Y-%m-%d %H:%M:%S"), day[5] ]) return json.dumps(data, cls=MyEncoder)
def get_suggest(flag): """ Function: circle_time() Description: 获取预测信息并返回数据到网页。 Input: :param: str: 查询时间范围。 Return: :return: json: 包含查询结果的json串。 Others: 无。 """ time = datetime.now() time = time.strftime("%Y-%m-%d 00:00:00") sql = "select * from data_predict where time='%s'" % (time) result = mysqlExecute(mysqlConnect(), sql) result = result.fetchone() if flag == 'day': try: return list(result[0:4]) except Exception: return [-1, -1, -1] elif flag == 'hour': hour_number = [] hour_number_inshop = [] try: for i in list(result)[-2].split('#'): hour_number.append(int(i)) for i in list(result)[-1].split('#'): hour_number_inshop.append(int(i)) data = {'a': hour_number, 'b': hour_number_inshop} data = json.dumps(data) return data except Exception: return [-1, -1, -1] elif flag == 'week': week_number = [] week_number_inshop = [] try: for i in list(result)[-4].split('#'): week_number.append(int(i)) for i in list(result)[-3].split('#'): week_number_inshop.append(int(i)) data = {'a': week_number, 'b': week_number_inshop} data = json.dumps(data) return data except Exception: return [-1, -1, -1]
def visit_circle(): sql = "select circle_time from data_set" result = mysqlExecute(mysqlConnect(), sql) all_num = result.fetchall() count1 = count2 = count3 = count4 = count5 = count6 = count7 = count8 = count9 = count10 = count11 = 0 for i in list(all_num): if int(list(i)[0]) <= 3: count1 += 1 elif int(list(i)[0]) in range(4, 7): count2 += 1 elif int(list(i)[0]) in range(7, 10): count3 += 1 elif int(list(i)[0]) in range(10, 13): count4 += 1 elif int(list(i)[0]) in range(13, 16): count5 += 1 elif int(list(i)[0]) in range(16, 19): count6 += 1 elif int(list(i)[0]) in range(19, 22): count7 += 1 elif int(list(i)[0]) in range(22, 25): count8 += 1 elif int(list(i)[0]) in range(25, 28): count9 += 1 elif int(list(i)[0]) in range(28, 31): count10 += 1 else: count11 += 1 data = { 'a': count1, 'b': count2, 'c': count3, 'd': count4, 'e': count5, 'f': count6, 'g': count7, 'h': count8, 'i': count9, 'j': count10, 'k': count11} data = json.dumps(data) return data
def time_stay(jsonDecode, inshop_range, probe_id): """ Function: time_stay() Description: 接收实时数据,计算驻店时长并存储,10秒以下设为1,10-30秒设为2,30-60设为3,60以上为4. Input: 接收到的json。 Return: :return :无 Others: 计算方法:inshop_time表存储所有进入过店铺的mac,当接收一个新mac, 查询inshop_time表是否有flag为1的该mac的记录,如果表中没有该mac并且该mac在店铺内,将mac和time存入表,flag设为1, 如果表中有该mac并且该mac在店铺外,查询表中该mac对应的时间,并与接收到的mac时间做差,将查询到的mac更新,flag设为0, 并将对应的时间差的代号存入. """ inshop_range = int(inshop_range) pattern1 = re.compile(r'0:00:0' + '\d') pattern2 = re.compile(r'0:00:[12]' + '\d') pattern3 = re.compile(r'0:00:[345]' + '\d') sql = "select count(*) from inshop_time where phone_mac='%s' and flag=1" % jsonDecode['data']['mac'] res = mysqlExecute(mysqlConnect(), sql) result = res.fetchone()[0] if result == 0 and jsonDecode['data']['range'] <= inshop_range: sql = "insert into inshop_time (phone_mac,time,flag,p_range) values('{}','{}',1,{})".format( jsonDecode['data']['mac'], jsonDecode['data']['time'], inshop_range) mysqlExecute(mysqlConnect(), sql) elif result == 1 and jsonDecode['data']['range'] > inshop_range : sql = "select time from inshop_time where flag=1 and phone_mac='%s'" % jsonDecode[ 'data']['mac'] try: cur = mysqlExecute(mysqlConnect(), sql) old_time = cur.fetchone()[0] recent = datetime.datetime.strptime( str(jsonDecode['data']['time']), "%Y-%m-%d %H:%M:%S") circle = str(recent - old_time) if pattern1.match(circle): stage = 1 elif pattern2.match(circle): stage = 2 elif pattern3.match(circle): stage = 3 else: stage = 4 sql = "update inshop_time set stay_time='%s',flag=0, time_diff='%s' where flag=1 and phone_mac='%s'" % ( str(stage), str(circle),jsonDecode['data']['mac']) mysqlExecute(mysqlConnect(), sql) except Exception as e: print e
def data_analysis(): """ Function: data_analysis() Description: 获取每日的客流量,入店量,天气和当天是否是节假日并存入数据库,用于数据预测 Input: 无 Return: :return :无 Others:客流量和入店量根据data和inshop_time表来查询.天气,节假日和每日8:00到22:00温度调用predict_info 中的get_weather,get_day_type和get_temperature来获取,将每小时的气温和每小时的入店量,客流量转成字符串, 数据间用#间隔,数据存入data_predict表中 """ time1 = datetime.datetime.strftime(time, "%Y-%m-%d 08:00:00") time2 = datetime.datetime.strftime(time, "%Y-%m-%d 23:00:00") time3 = datetime.datetime.strftime(time, "%Y-%m-%d 00:00:00") time_day = datetime.datetime.strftime(time, "%Y%m%d") #time=(time + datetime.timedelta(days=+1)).strftime("%Y%m%d") sql = "select count(distinct phone_mac) from hive_data.data where time between '%s' and '%s'" % ( time1, time2) res = mysqlExecute(mysqlConnect(), sql) num = res.fetchone() all_num = num[0] sql = "select count(distinct phone_mac) from hive_data.inshop_time where time between '%s' and '%s'" % ( time1, time2) res = mysqlExecute(mysqlConnect(), sql) inshop_num = res.fetchone()[0] sql = "select count(phone_mac) from hive_data.inshop_time where time between '%s' and '%s'" % ( time1, time2) res = mysqlExecute(mysqlConnect(), sql) all_inshop_num = res.fetchone()[0] sql = "select lat,lon from hive_data.data limit 1" res = mysqlExecute(mysqlConnect(), sql) location = res.fetchone() print location weather = perdict_info.get_weather(location[0], location[1], 'today') # weather=predict_info.get_weather('40.8','116.1','today') holiday = perdict_info.get_day_type(time_day) temperature = perdict_info.get_temperature(location[0], location[1], 'today') if (temperature == "fail"): s = (time + datetime.timedelta(days=-1)).strftime("%Y-%m-%d 00:00:00") e = (time + datetime.timedelta(days=-1)).strftime("%Y-%m-%d 23:00:00") sql = "select hour_temperature from hive_data.data_predict where time between '%s' and '%s'" % ( s, e) result = mysqlExecute(mysqlConnect(), sql) result = result.fetchone()[0] temperature_str = result # temperature=predict_info.get_temperature('40.8','116.1') else: temperature_str = "" pattern = '\d+' for i in range(15): temperature1 = re.findall(pattern, temperature[i]) temperature_str += (temperature1[0] + "#") temperature_str = temperature_str[:-1] time_s1 = datetime.datetime.strftime(time, "%Y-%m-%d 08:00:00") time_e1 = datetime.datetime.strftime(time, "%Y-%m-%d 08:00:00") count = "" count1 = "" for i in range(8, 23): time_s = time_s1 time_e = time_e1 time_s = time_s.replace(time_s[-8:-6], str(i)) time_e = time_e.replace(time_e[-8:-6], str(i + 1)) sql = "select count(distinct phone_mac) from hive_data.data where time between '%s' and '%s'" % ( time_s, time_e) result = mysqlExecute(mysqlConnect(), sql) result = result.fetchone()[0] count += (str(result) + "#") count = count[:-1] for i in range(8, 23): time_s = time_s1 time_e = time_e1 time_s = time_s.replace(time_s[-8:-6], str(i)) time_e = time_e.replace(time_e[-8:-6], str(i + 1)) sql = "select count(distinct phone_mac) from hive_data.inshop_time where time between '%s' and '%s'" % ( time_s, time_e) result = mysqlExecute(mysqlConnect(), sql) result = result.fetchone()[0] count1 += (str(result) + "#") count1 = count1[:-1] sql = "delete from hive_data.data_predict where time='%s'" % time3 mysqlExecute(mysqlConnect(), sql) sql = "insert into hive_data.data_predict values('%s','%s','%s','%s','%s','%s'," \ "'%s','%s','%s')" % (all_num, inshop_num, weather, holiday, time3,'', temperature_str, count, count1) mysqlExecute(mysqlConnect(), sql)
def hot_map(): def getTime(delay=0, advance=0): return str( time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time() - delay + advance))) def Trilateration(point1,point2,point3): # Trilateration三边测量定位算法 def get_two_point(point1,point2): import math def sq(x): return float(x * x) R = float(point1['d']) R= 9 S=8 S = float(point2['d']) x = float(point1['x']) y = float(point1['y']) # target point for arm a = float(point2['x']) b = float(point2['y']) print "arm target:", a, b d = math.sqrt(sq(math.fabs(a-x)) + sq(math.fabs(b-y))) print "desitens:", d if d > (R+S) or d < (math.fabs(R-S)): print "This point can't be rached!" #return -1 exit if d == 0 and R==S : print "Can't rach arm point!" #return -2 exit A = (sq(R) - sq(S) + sq(d)) / (2 * d) h = math.sqrt(sq(R) - sq(A)) x2 = x + A * (a-x)/d y2 = y + A * (b-y)/d #print x2, y2 x3 = x2 - h * ( b - y ) / d y3 = y2 + h * ( a - x ) / d x4 = x2 + h * (b - y) / d y4 = y2 - h * (a - x) / d print "arm middle point:" print x3, y3 print x4, y4 config = getConfigure('probe_position') probe_x1 = config['probe_x1'] probe_x2 = config['probe_x2'] probe_x3 = config['probe_x3'] probe_y1 = config['probe_y1'] probe_y2 = config['probe_y2'] probe_y3 = config['probe_y3'] sql = "SELECT p_range FROM inshop_time WHERE time BETWEEN '{}' AND '{}' LIMIT 1".format(getTime(24 * 3600), getTime()) cur1 = mysqlExecute(mysqlConnect(), sql) sql = """SELECT phone_range ,probe_id FROM hive_data.data WHERE probe_id != 1 AND time BETWEEN '{}' AND '{}' LIMIT 1 """.format(getTime(24 * 3600), getTime()) cur = mysqlExecute(mysqlConnect(), sql) for i , j in zip(cur._rows, range(1)): try: range1 = cur1._rows[j][0] range2 = cur._rows[j][0] except Exception : j = 1 range1 = cur1._rows[j][0] range2 = cur._rows[j][0] point1 = { 'x': int(probe_x1), 'y': int(probe_y1), 'd': int(range1) } point2 = { 'x': int(probe_x2), 'y': int(probe_y2), 'd': int(i[0]) } point3 = { 'x': int(probe_x3), 'y': int(probe_y3), 'd': int(range2) } Trilateration(point1, point2, point3) data = [] return render_template('hot_map.html',data={"deep":data})