def Getfirstactived(model,channel,spec,occurdate):
try:
sql = 'SELECT COUNT(*) as num from first_active WHERE model= '+str(model)+' and spec='+str(spec)+' and channel='+str(channel)+' and occurdate='+str(occurdate)
print sql
result, querydata = OperateSQL("device_server").query_get(sql)
print querydata
if result and len(querydata)>0:
"update tbl_online_info set imei= '"+imei+"' where id = '" +id+"'"
else:
sql = " select udid , imei from device_account where udid = '"+str(udid)+ "' limit 1;"
result, querydata = OperateSQL("online_server").query_get(sql)
print querydata
if result:
for one_item in querydata:
if not one_item["imei"] :
#print ip_num
return ""
if len(one_item["imei"]) == 0 :
return ""
insert_sql = '''
INSERT into device_udid_imei(udid,imei)VALUES("'''+str(udid)+'''" ,"'''+one_item["imei"]+'''")
'''
insert_succ,insert_result = OperateSQL("bs_rom_data_server").query_update(insert_sql)
if not insert_succ:
print('insert gsm err')
return one_item["imei"]
return ""
except Exception , e:
print e
return ""
def change_cdma_addr():
get_succ,get_result=OperateSQL("bs_rom_data_server").query_get('SELECT * from global_gsm where city like "%省" ')
if not get_succ:
print("err")
# print get_result
#打印表中的多少数据
for var in get_result:
address =var['area'].replace(' ', '')
province,city,district = place_cut(address)
update_sql = '''
UPDATE global_gsm SET province="'''+province+'''" , city="'''+city+'''" , district="'''+district+'''" WHERE cid = "'''+str(var["cid"])+'''" and lac = "'''+str(var["lac"])+'''" and mcc = "'''+str(var["mcc"])+'''" and mnc = "'''+str(var["mnc"])+'''"
'''
update_succ,update_result = OperateSQL("bs_rom_data_server").query_update(update_sql)
def change_ip_addr():
get_succ,get_result=OperateSQL("bs_rom_data_server").query_get('select * from global_ip_addr where province="贵省"')
if not get_succ:
print("err")
# print get_result
#打印表中的多少数据
for var in get_result:
address =var['area'].replace(' ', '')
province,city,district = place_cut(address)
update_sql = '''
UPDATE global_ip_addr SET province="'''+province+'''" , city="'''+city+'''" , district="'''+district+'''" WHERE start_ip = "'''+str(var["start_ip"])+'''"
'''
update_succ,update_result = OperateSQL("bs_rom_data_server").query_update(update_sql)
def executeActStat():
del_sql = "DELETE FROM device_act_stat_week"
get_succ,get_result=OperateSQL("bs_rom_data_server").query_update(del_sql)
del_sql = "DELETE FROM device_act_stat_month"
get_succ,get_result=OperateSQL("bs_rom_data_server").query_update(del_sql)
try:
insert_sql_week = "insert into device_act_stat_week select stat_date,stat_key, province,count(*) as act_num from (select date_sub(create_date,INTERVAL WEEKDAY(create_date) DAY) stat_date ,model as stat_key, province, udid from tbl_online_info group by stat_date, province,udid) as temp_a group by stat_key, stat_date,province;"
get_succ,get_result=OperateSQL("bs_rom_data_server").query_update(insert_sql_week)
insert_sql_month = "insert into device_act_stat_month select stat_date,stat_key, province,count(*) as act_num from (select concat(date_format(LAST_DAY(create_date),'%Y-%m-'),'01') stat_date , model as stat_key, province, udid from tbl_online_info group by stat_date, province,udid) as temp_a group by stat_key, stat_date,province;"
get_succ,get_result=OperateSQL("bs_rom_data_server").query_update(insert_sql_month)
except Exception , e:
print e
def insert_jizhan(is_gsm,mnc,mcc,cid,lac,province,city,district,area,country):
if is_gsm:
insert_sql = '''
INSERT into global_gsm(mnc,mcc,cid,lac,province,city,district,country,area)VALUES("'''+mnc+'''" ,"'''+mcc+'''" ,"'''+cid+'''" ,"'''+lac+'''","'''+province+'''" ,"'''+city+'''" ,"'''+district+'''","'''+country+'''","'''+area+'''")
'''
insert_succ,insert_result = OperateSQL("bs_rom_data_server").query_update(insert_sql)
if not insert_succ:
print('insert gsm err')
else:
insert_sql = '''
INSERT into global_cdma(sid,nid,bid,province,city,district,country,area)VALUES("'''+mnc+'''" ,"'''+cid+'''" ,"'''+lac+'''","'''+province+'''" ,"'''+city+'''" ,"'''+district+'''","'''+country+'''","'''+area+'''")
'''
insert_succ,insert_result = OperateSQL("bs_rom_data_server").query_update(insert_sql)
if not insert_succ:
print('insert cmda err')
def update_package():
sql="select * from temp_module_20180514 where id>=23690"
issuccess,query_data = OperateSQL('package_server').query_get(sql)
# print (query_data)
if issuccess:
for var in query_data:
types,sub_type,display_name,app_tags = get_mi_app(var['src_package_name'])
# print(types,sub_type,display_name,app_tags)
update_sql = '''
UPDATE temp_module_20180514 SET category="'''+types+'''",sub_category="'''+sub_type+'''" ,display_name="'''+display_name+'''" , app_tags="'''+app_tags+'''" WHERE id = "'''+str(var["id"])+'''"
'''
# print(update_sql)
update_succ,update_result = OperateSQL("package_server").query_update(update_sql)
if not update_succ:
print('update err')
def statistics():
sql = "select * from st_device_day limit 100 ;"
while True:
result, querydata = OperateSQL("device_server").query_get(sql)
if result:
for one_item in querydata:
if len(querydata) == 0:
# executeActStat()
break
def executeActStat():
del_sql = "DELETE FROM device_act_stat_week"
get_succ,get_result=OperateSQL("bs_rom_data_server").query_update(del_sql)
del_sql = "DELETE FROM device_act_stat_month"
get_succ,get_result=OperateSQL("bs_rom_data_server").query_update(del_sql)
try:
insert_sql_week = "insert into device_act_stat_week select stat_date,stat_key, province,count(*) as act_num from (select date_sub(create_date,INTERVAL WEEKDAY(create_date) DAY) stat_date ,model as stat_key, province, udid from tbl_online_info group by stat_date, province,udid) as temp_a group by stat_key, stat_date,province;"
get_succ,get_result=OperateSQL("bs_rom_data_server").query_update(insert_sql_week)
insert_sql_month = "insert into device_act_stat_month select stat_date,stat_key, province,count(*) as act_num from (select concat(date_format(LAST_DAY(create_date),'%Y-%m-'),'01') stat_date , model as stat_key, province, udid from tbl_online_info group by stat_date, province,udid) as temp_a group by stat_key, stat_date,province;"
get_succ,get_result=OperateSQL("bs_rom_data_server").query_update(insert_sql_month)
except Exception , e:
print e
def do_insert(row_data):
try:
insert_sql = """INSERT INTO `ip_addr_copy_new` (`start_ip`,`end_ip`, `area`,`remark`,`start_num`,`end_num`,`province`,`city`,`district`) VALUES ( %s, %s, %s,%s, %s, %s,%s, %s, %s )"""
get_succ, get_result = OperateSQL("device_server").insert(
insert_sql, row_data)
except Exception as e:
print(row_data)
print(e)
def statistics():
sql = "select * from tbl_online_info where (province is null or province = '' or province= '错误') limit 100;"
while True:
result, querydata = OperateSQL("device_server").query_get(sql)
print sql
#print result, querydata
if result:
for one_item in querydata:
province = GetAreaByIP(one_item["ipaddr"])
update_sql = "update tbl_online_info set province = '" + province + "' where id = " + str(
one_item["id"])
succe, update_result = OperateSQL(
"device_server").query_update(update_sql)
print succe
print update_sql
if len(querydata) == 0:
executeActStat()
break
def getchannelByiemi(imei):
sql = "select imei0,channel,spec,model from first_active where imei0 = '" + imei + "';"
print sql
result, querydata = OperateSQL("bs_rom_data_server").query_get(sql)
if result and len(querydata) > 0:
for one_item in querydata:
# spec ='{0}+{1} {2}'.format(one_item["memory"],one_item["storage"],one_item["color"])
# channel=one_item.get("customer_type","")
return one_item.get('channel'), one_item.get("spec")
return '', ''
def statistics():
sql = "select * from tbl_online_info where (province is null or province = '' ) and create_date>='2018-05-07' limit 10000 ;"
while True:
result, querydata = OperateSQL("bs_rom_data_server").query_get(sql)
print sql
#print result, querydata
if result:
for one_item in querydata:
# channel = ''
# spec=''
province = GetAreaByIP(one_item["ipaddr"])
# imei = GetImeiByUdid(one_item["udid"])
# channel,spec=getchannelByiemi(one_item["imei"])
update_sql = "update tbl_online_info set province='" + province + "' where id = " + str(
one_item["id"])
# update_sql = "update tbl_online_info set imei= '"+one_item["imei"]+"',channel='"+channel+"',spec='"+spec+"',province='"+province+"' where id = " + str(one_item["id"])
succe, update_result = OperateSQL(
"bs_rom_data_server").query_update(update_sql)
# print succe
# print update_sql
if len(querydata) == 0:
# executeActStat()
break
def change_address():
get_succ, get_result = OperateSQL("bs_rom_data_server").query_get(
'select * from first_active where province="宁夏" limit 100')
if not get_succ:
print("err")
print(get_result)
#打印表中的多少数据
for var in get_result:
province = ""
city = ""
district = ""
address = var.get('area', '')
print address
province, city, district = place_cut(str(address))
out = '%s|%s|%s' % (province.decode('utf8'), city.decode('utf8'),
district.decode('utf8'))
print out
# print province,city,district
update_sql = '''
UPDATE first_active SET location_state=1,province="''' + province + '''" , city="''' + city + '''" , district="''' + district + '''" WHERE id = "''' + str(
var["id"]) + '''"
'''
update_succ, update_result = OperateSQL(
"bs_rom_data_server").query_update(update_sql)
def GetAreaByIP(ip_addr):
try:
ip_num = getipnum(ip_addr)
if ip_num == 0:
return "错误"
sql = " select * from global_ipaddr where start_num < "+str(ip_num)+" and end_num > "+ str(ip_num) + ";"
result, querydata = OperateSQL("bs_rom_data_server").query_get(sql)
#print querydata
if result :
for one_item in querydata:
if not one_item["province"] :
#print ip_num
return "其他"
if len(one_item["province"]) == 0 :
return "其他"
return one_item["province"]
return "其他"
except Exception , e:
print e
return "错误"
def ip_cell_get(ip):
print('use IP get cell address')
province = ""
city = ""
district = ""
area = ""
num_ip=ip2int(str(ip))
print num_ip
sql='''
SELECT area,province,city,district FROM global_ip_addr WHERE start_num<="''' + str(num_ip)+'''" AND end_num>="''' + str(num_ip)+'''"
'''
search_succ,search_result = OperateSQL("bs_rom_data_server").query_get(sql)
if not search_succ:
print('ip search err')
return "","","","",""
print(search_result)
if search_result:
print search_result
country = '中国'
area = search_result[0]["area"].replace(' ','')
province,city,district = place_cut(area)
# province = search_result[0]["province"]
# city = search_result[0]["city"]
# district = search_result[0]["district"]
if area == "局域网":
province = "上海市"
city = "上海市"
district = "浦东新区"
if city is None:
city=""
if province is None:
province = ""
if district is None:
district = ""
print(area,province,city,district)
return country,area,province,city,district
def handle_first_data_controller():
get_succ,get_result=OperateSQL("bs_rom_data_server").query_get('select * from handle_first_active where city not like "%市" and LENGTH(city)>=4 and LENGTH(city)<=9 and city not like "%州" ')
if not get_succ:
print("err")
# print get_result
#打印表中的多少数据
for var in get_result:
get_succ = False
is_gsm = True
mcc = None
mnc = None
cid = None
lac = None
imsi = None
result = None
province = None
city = None
district = None
area = None
mcc = var['mcc']
cid = var['cid']
mnc = var['mnc']
lac = var['lac']
if cid=='-1':
is_gsm = False
if len(var['cdmasid'])>0:
mnc = var['cdmasid']
lac = var['cdmanid']
cid = var['cdmabid']
sql = 'select * from global_cdma where sid='+mnc+' and nid='+lac+' and bid='+cid+' '
print(sql)
get_succ,result=OperateSQL("bs_rom_data_server").query_get(sql)
if not get_succ:
result=None
print("select cmda err")
else:
sql = 'select * from global_gsm where mnc='+mnc+' and lac='+lac+' and cid='+cid+' and mcc='+mcc+' '
print(sql)
get_succ,result=OperateSQL("bs_rom_data_server").query_get(sql)
# print(get_succ,result)
if not get_succ:
result=None
print("select gsm err")
if result is None or len(result)==0:
if var['imsi0']!='0':
imsi=var['imsi0']
else:
imsi = var['imsi1']
gaode_rep = gaode_cell_get(mcc,mnc,lac,cid,var['imei0'],imsi,is_gsm)
print(gaode_rep)
if gaode_rep and gaode_rep['result']['type']!='0' and 'desc' in gaode_rep['result'].keys():
address = gaode_rep['result']['desc'].replace(' ', '')
_,_,district = place_cut(address)
print(address,district)
province = gaode_rep['result'].get('province','')
city = gaode_rep['result'].get('city','')
country = gaode_rep['result'].get('country','')
update_sql = '''
UPDATE handle_first_active SET location_state=1,country="'''+country+'''" ,province="'''+province+'''" , city="'''+city+'''" , district="'''+district+'''",area="'''+gaode_rep['result']['desc']+'''" WHERE id = "'''+str(var["id"])+'''"
'''
update_succ,update_result = OperateSQL("bs_rom_data_server").query_update(update_sql)
if update_succ:
print(update_succ)
insert_jizhan(is_gsm,mnc,mcc,cid,lac,province,city,district,gaode_rep['result']['desc'],country)
else:
update_sql = '''
UPDATE handle_first_active SET location_state=2 WHERE id = "'''+str(var["id"])+'''"
'''
update_succ,update_result = OperateSQL("bs_rom_data_server").query_update(update_sql)
update_succ = False
elif var['gps'] is not None and var['gps']!='':
gps_rep=gps_cell_get(var['gps'])
if gps_rep:
address=gps_rep['regeocode']['formatted_address'].replace(' ', '')
province,city,district = place_cut(address)
country = gps_rep['regeocode']['addressComponent']['country']
update_sql = '''
UPDATE handle_first_active SET location_state=1,country="'''+country+'''" ,province="'''+province+'''" , city="'''+city+'''" , district="'''+district+'''",area="'''+address+'''" WHERE id = "'''+str(var["id"])+'''"
'''
update_succ,update_result = OperateSQL("bs_rom_data_server").query_update(update_sql)
if not update_succ:
print('update by GPS err')
else:
country,area,province,city,district=ip_cell_get(var['ip'])
update_sql = '''
UPDATE handle_first_active SET location_state=1,country="'''+country+'''" ,province="'''+province+'''" , city="'''+city+'''" , district="'''+district+'''",area="'''+area+'''" WHERE id = "'''+str(var["id"])+'''"
'''
update_succ,update_result = OperateSQL("bs_rom_data_server").query_update(update_sql)
if not update_succ:
print('update by IP err')
else:
country,area,province,city,district=ip_cell_get(var['ip'])
update_sql = '''
UPDATE handle_first_active SET location_state=1,country="'''+country+'''" ,province="'''+province+'''" , city="'''+city+'''" , district="'''+district+'''",area="'''+area+'''" WHERE id = "'''+str(var["id"])+'''"
'''
update_succ,update_result = OperateSQL("bs_rom_data_server").query_update(update_sql)
if not update_succ:
print('update by IP err')
else:
if len(result)>0:
address = result[0]['area'].replace(' ', '')
province,city,district = place_cut(address)
print "test"
print city
# district = result[0]['district']
# province = result[0]['province']
# city = result[0]['city']
country = result[0]['country']
update_sql = '''
UPDATE handle_first_active SET location_state=1,country="'''+country+'''" ,province="'''+province+'''" , city="'''+city+'''" , district="'''+district+'''",area="'''+address+'''" WHERE id = "'''+str(var["id"])+'''"
'''
update_succ,update_result = OperateSQL("bs_rom_data_server").query_update(update_sql)
def process_main():
sql_cmd = '''
SELECT *
FROM global_ipaddr_copy
WHERE start_ip="27.98.248.0"
'''
get_succ, get_result = OperateSQL("device_server").query_get(sql_cmd)
if not get_succ:
print "err"
count = 0
for onedata in get_result:
print onedata["area"]
area = onedata["area"]
if "省" in area:
count += 1
areas = re.split("省|州|市|县|区|市", area)
province = areas[0] + "省"
print province
city = ""
if len(areas) >= 2:
if len(areas[1]) > 0:
if "州" in area:
city = areas[1] + "州"
else:
city = areas[1] + "市"
print city
district = ""
if len(areas) >= 3:
if len(areas[2]) > 0:
if "县" in area:
district = areas[2] + "县"
elif "区" in area:
district = areas[2] + "区"
else:
district = areas[2] + "市"
print len(district)
update_sql = '''
UPDATE global_ipaddr_copy SET province="''' + province + '''" , city="''' + city + '''" , district="''' + district + '''" WHERE start_ip = "''' + str(
onedata["start_ip"]) + '''"
'''
print update_sql
update_succ, update_result = OperateSQL(
"device_server").query_update(update_sql)
if "北京" in area or "上海" in area or "重庆" in area or "天津" in area:
count += 1
areas = re.split("市|区|县|镇", area)
province = areas[0] + "市"
print province
city = ""
if len(areas) >= 2:
if len(areas[1]) > 0:
if "区" in area:
city = areas[1] + "区"
if "县" in area:
city = areas[1] + "县"
print city
district = ""
if len(areas) >= 3:
if len(areas[2]) > 0:
district = areas[2] + "镇"
print len(district)
update_sql = '''
UPDATE global_ipaddr_copy SET province="''' + province + '''" , city="''' + city + '''" , district="''' + district + '''" WHERE start_ip = "''' + str(
onedata["start_ip"]) + '''"
'''
print update_sql
update_succ, update_result = OperateSQL(
"device_server").query_update(update_sql)
if "内蒙古" in area:
count += 1
areas = re.split("内蒙古|盟|市|旗|市|区|县", area)
province = "内蒙古自治区"
city = ""
if len(areas) >= 2:
if len(areas[1]) > 0:
if "市" in area:
city = areas[1] + "市"
if "盟" in area:
city = areas[1] + "盟"
district = ""
if len(areas) >= 3:
if len(areas[2]) > 0:
if "县" in area:
district = areas[2] + "县"
elif "区" in area:
district = areas[2] + "区"
elif "市" in area:
district = areas[2] + "市"
else:
district = areas[2] + "旗"
update_sql = '''
UPDATE global_ipaddr_copy SET province="''' + province + '''" , city="''' + city + '''" , district="''' + district + '''" WHERE start_ip = "''' + str(
onedata["start_ip"]) + '''"
'''
print update_sql
update_succ, update_result = OperateSQL(
"device_server").query_update(update_sql)
if "新疆" in area:
count += 1
areas = re.split("新疆|州|地区|市|市|县", area)
province = "新疆维吾尔自治区"
city = ""
if len(areas) >= 2:
if len(areas[1]) > 0:
if "州" in area:
city = areas[1] + "州"
elif "地区" in area:
city = areas[1] + "地区"
else:
city = areas[1] + "市"
district = ""
if len(areas) >= 3:
if len(areas[2]) > 0:
if "县" in area:
district = areas[2] + "县"
else:
district = areas[2] + "市"
update_sql = '''
UPDATE global_ipaddr_copy SET province="''' + province + '''" , city="''' + city + '''" , district="''' + district + '''" WHERE start_ip = "''' + str(
onedata["start_ip"]) + '''"
'''
print update_sql
update_succ, update_result = OperateSQL(
"device_server").query_update(update_sql)
if "广西" in area:
count += 1
areas = re.split("广西|市|县|区|市", area)
province = "广西壮族自治区"
print province
city = ""
if len(areas) >= 2:
if len(areas[1]) > 0:
city = areas[1] + "市"
print city
district = ""
if len(areas) >= 3:
if len(areas[2]) > 0:
if "县" in area:
district = areas[2] + "县"
elif "区" in area:
district = areas[2] + "区"
else:
district = areas[2] + "市"
print len(district)
update_sql = '''
UPDATE global_ipaddr_copy SET province="''' + province + '''" , city="''' + city + '''" , district="''' + district + '''" WHERE start_ip = "''' + str(
onedata["start_ip"]) + '''"
'''
print update_sql
update_succ, update_result = OperateSQL(
"device_server").query_update(update_sql)
if "宁夏" in area:
count += 1
areas = re.split("宁夏|市|县|区|市", area)
province = "宁夏回族自治区"
print province
city = ""
if len(areas) >= 2:
if len(areas[1]) > 0:
city = areas[1] + "市"
print city
district = ""
if len(areas) >= 3:
if len(areas[2]) > 0:
if "县" in area:
district = areas[2] + "县"
elif "区" in area:
district = areas[2] + "区"
else:
district = areas[2] + "市"
print len(district)
update_sql = '''
UPDATE global_ipaddr_copy SET province="''' + province + '''" , city="''' + city + '''" , district="''' + district + '''" WHERE start_ip = "''' + str(
onedata["start_ip"]) + '''"
'''
print update_sql
update_succ, update_result = OperateSQL(
"device_server").query_update(update_sql)
if "西藏" in area:
count += 1
areas = re.split("西藏|州|地区|市|市|县", area)
province = "西藏自治区"
city = ""
if len(areas) >= 2:
if len(areas[1]) > 0:
if "州" in area:
city = areas[1] + "州"
elif "地区" in area:
city = areas[1] + "地区"
else:
city = areas[1] + "市"
district = ""
if len(areas) >= 3:
if len(areas[2]) > 0:
if "县" in area:
district = areas[2] + "县"
else:
district = areas[2] + "市"
update_sql = '''
UPDATE global_ipaddr_copy SET province="''' + province + '''" , city="''' + city + '''" , district="''' + district + '''" WHERE start_ip = "''' + str(
onedata["start_ip"]) + '''"
'''
print update_sql
update_succ, update_result = OperateSQL(
"device_server").query_update(update_sql)
print "count:", count
print "OVER"