def extract(self):
self.queryData = QueryData.QueryData()
self.queryData.connect()
result = utils.sqlHandler(queryData=self.queryData,
sqlType="query",
sql=extract_sql,
piece=10000)
self.queryData.close()
conn_load = QueryData.QueryData(host=self.cp.get("mysql", "host_2"),
user=self.cp.get("mysql", "user_2"),
pwd=self.cp.get("mysql", "pwd_2"),
dbname=self.cp.get(
"mysql", "dbname_2"))
conn_load.connect()
conn_load.update(delete_before_insert)
conn_load.commit()
time.sleep(2)
for item in result:
for key in item:
if type(item[key]) == str and item[key] != "null":
item[key] = "'" + item[key] + "'"
item["create_date"] = time.strftime(
"%Y-%m-%d", time.localtime(item["create_time"]))
item["create_date"] = "'" + item["create_date"] + "'"
conn_load.update(extract_load % item)
print("完成合同主题抽取、转换、存储......")
conn_load.commit()
conn_load.close()
def load(self):
# 计算创建日期在三天以内的静态
"""
整体过程是将处理后的主题类数据上传到数据库服务器
"""
try:
self.queryData = QueryData.QueryData(host=self.cp.get("mysql","host_2"),user=self.cp.get("mysql","user_2"),pwd=self.cp.get("mysql","pwd_2"),dbname=self.cp.get("mysql","dbname_2"))
self.queryData.connect()
conn = QueryData.QueryData()
conn.connect()
for hash_key in self.emp_result:
per_theme_data = self.emp_result[hash_key][0]
self.queryData.update(load_sql%per_theme_data)
self.queryData.commit()
self.calc_static_validity(conn,self.queryData)
print ("完成市场主题存储......")
# 数据存储之后，validity_for_once字段
self.queryData.close()
conn.close()
writelogging.logger.info("市场主题数据装载完成,本次%s条记录"%len(self.emp_result))
except Exception as e:
writelogging.logger.error("市场主题数据装载失败")
self.emp_result = {}
def addroute(start, end, dist):
source_to_dest = GraphLibrary.Route(start, end, dist)
dest_to_source = GraphLibrary.Route(end, start, dist)
GraphLibrary.route_list.append(source_to_dest)
GraphLibrary.route_list.append(dest_to_source)
QueryData.addhubcity(start)
QueryData.addhubcity(end)
def addroute(start, end, dist):
source_to_dest = GraphLibrary.Route(start, end, dist)
dest_to_source = GraphLibrary.Route(end, start, dist)
GraphLibrary.route_list.append(source_to_dest)
GraphLibrary.route_list.append(dest_to_source)
QueryData.addhubcity(start)
QueryData.addhubcity(end)
def extract(self):
try:
self.queryData = QueryData.QueryData()
self.queryData.connect()
result = utils.sqlHandler(queryData=self.queryData,sqlType="query",sql=extract_sql,piece = 3000)
self.queryData.close()
except Exception as e:
writelogging.logger.error("市场主题数据库连接失败,请检查用户名,密码")
try:
# 一下开始插入数据到另外一个数据库
self.queryData = QueryData.QueryData(host=self.cp.get("mysql","host_2"),user=self.cp.get("mysql","user_2"),pwd=self.cp.get("mysql","pwd_2"),dbname=self.cp.get("mysql","dbname_2"))
self.queryData.connect()
# 首先清除之前的错误数据
self.queryData.update(delete_before_insert)
self.queryData.commit()
for item in result:
if (not item["status"]):
item["status"] ="null"
item["emp_status"] = "null"
elif (item["status"] in self.emp_status):
item["emp_status"] = self.emp_status[item["status"]]
else:
item["emp_status"] = "null"
if (not item["validity"]):
item["validity"] = "null"
item["state"] = "null"
elif ( item["validity"] in self.status_dict):
item["state"] = self.status_dict[item["validity"]]
else:
item["state"] = "null"
if item["update_time"] == "null":
item["update_time"] = "0000-00-00 00:00:00"
if item["create_time"] == "null":
item["create_time"] = "0000-00-00 00:00:00"
if (item["description"] and (item["description"].find("爷")>=0 or item["description"].find("奶")>=0) ):
item["description"] = "非双亲陪同"
else:
item["description"]=" "
# 循环遍历每一项，如果为None,则改为null
# for key in item.keys():
# if (not item[key]):
# item[key]="null"
if (item["validity"]):
self.queryData.update(extract_insert_sql%item)
self.queryData.commit()
self.queryData.close()
print ("完成市场主题抽取......")
writelogging.logger.info("市场信息表抽取完成,一共%s个记录"%len(result))
except Exception as e:
writelogging.logger.error("市场主题数据抽取、数据库连接失败")
def TestaddRoute(self):
code = QueryData.nametocode("Los Angeles")
code2 = QueryData.nametocode("Seoul")
EditRoute.addroute(code, code2, "12435")
isValid = False
for route in GraphLibrary.route_list:
if route.startportcode == code and route.endportcode == code2:
isValid = True
break
self.assertTrue(isValid)
def TestremoveRoute(self):
EditRoute.removeroute("Los Angeles", "San Francisco")
isValid = True
code = QueryData.nametocode("Los Angeles")
code2 = QueryData.nametocode("San Francisco")
for route in GraphLibrary.route_list:
if route.startportcode == code and route.endportcode == code2:
isValid = False
break
self.assertTrue(isValid)
def test_addRoute(self):
code = QueryData.nametocode("Los Angeles")
code2 = QueryData.nametocode("Seoul")
EditRoute.addroute(code, code2, "12435")
isValid = False
for route in GraphLibrary.route_list:
if route.startportcode == code and route.endportcode == code2:
isValid = True
break
self.assertTrue(isValid)
def test_removeRoute(self):
EditRoute.removeroute("Los Angeles", "San Francisco")
isValid = False
code = QueryData.nametocode("Los Angeles")
code2 = QueryData.nametocode("San Francisco")
for route in GraphLibrary.route_list:
if route.startportcode == code and route.endportcode == code2:
isValid = True
break
self.assertTrue(isValid)
def parse_route(data):
for route in data["routes"]:
source_to_dest = GraphLibrary.Route(route["ports"][0],
route["ports"][1],
route["distance"])
dest_to_source = GraphLibrary.Route(route["ports"][1],
route["ports"][0],
route["distance"])
GraphLibrary.route_list.append(source_to_dest)
GraphLibrary.route_list.append(dest_to_source)
QueryData.addhubcity(route["ports"][0])
QueryData.addhubcity(route["ports"][1])
def changePredict(plant_num, collectedColor, idealColor):
# Save the full data set to a 2D list
dataArray = QueryData.collectData(plant_num)
waterVals = dataArray[0]
colorVals = dataArray[1]
# Find the difference between the ideal and collected color
difference = 1 - (1.0 * collectedColor / idealColor)
newWater = waterVals[-1]
# If the difference is significant in either direction, update based on correlation
if difference > 0.05:
slope = abs(regression(waterVals, colorVals))
addAmt = slope * abs(collectedColor - idealColor)
lastWater = waterVals[-1]
newWater = lastWater + addAmt
elif difference < -0.05:
slope = abs(regression(waterVals, colorVals))
addAmt = slope * abs(collectedColor - idealColor)
lastWater = waterVals[-1]
newWater = lastWater - addAmt
healthy = True
# Remove extreme irrigation volume changes
if newWater > 30:
while newWater > 7:
newWater -= 1.1837513
healthy = False
if newWater < 2:
while newWater < 5:
newWater += 0.391
healthy = False
return newWater, healthy
def routeinfo():
cityList = QueryData.citylist()
isValid = False
while not isValid:
print "Type the first valid city of new route."
startcity = raw_input()
isValid = startcity in cityList
if startcity == "exit" or startcity == "quit" or startcity == "q" or startcity == "0":
return
legs = []
stops = []
# Gather dest city
while (True):
isValid = False
while not isValid:
endcity = raw_input(
"Type the next valid city of route, or type exit to end")
isValid = endcity in cityList or endcity == "exit" or endcity == "q"
if not isValid:
print "Wrong city. Not counted."
if endcity == "exit" or endcity == "q":
break
stops.append(endcity)
startcode = QueryData.nametocode(startcity)
endcode = QueryData.nametocode(endcity)
for route in GraphLibrary.route_list:
if route.startportcode == startcode and \
route.endportcode == endcode:
dist = route.distance
legs.append(dist)
stops.append(endcode)
break
startcity = endcity
# after getting all the list
print "Total Distance = " + str(totaldistance(legs)) + " km"
print "Total Cost = $" + str(totalcost(legs))
print "Total Time = " + str(totaltime(legs, stops)) + " hrs"
def routeinfo():
cityList = QueryData.citylist()
isValid = False
while not isValid:
print "Type the first valid city of new route."
startcity = raw_input()
isValid = startcity in cityList
if startcity == "exit" or startcity == "quit" or startcity == "q" or startcity == "0":
return
legs = []
stops = []
# Gather dest city
while(True):
isValid = False
while not isValid:
endcity = raw_input("Type the next valid city of route, or type exit to end")
isValid = endcity in cityList or endcity == "exit" or endcity == "q"
if not isValid:
print "Wrong city. Not counted."
if endcity == "exit" or endcity == "q":
break
stops.append(endcity)
startcode = QueryData.nametocode(startcity)
endcode = QueryData.nametocode(endcity)
for route in GraphLibrary.route_list:
if route.startportcode == startcode and \
route.endportcode == endcode:
dist = route.distance
legs.append(dist)
stops.append(endcode)
break
startcity = endcity
# after getting all the list
print "Total Distance = " + str(totaldistance(legs)) + " km"
print "Total Cost = $" + str(totalcost(legs))
print "Total Time = " + str(totaltime(legs, stops)) + " hrs"
def removeroute(city, city2):
code = QueryData.nametocode(city)
code2 = QueryData.nametocode(city2)
isValid = False
for route in GraphLibrary.route_list:
if code in route.startportcode and code2 in route.endportcode:
isValid = True
del route
elif code2 in route.startportcode and code in route.startportcode:
isValid = True
del route
if isValid:
print "Remove of route between " + city + " and " + city2 + " is successful."
else:
print "Remove unsuccessful."
def extract(self):
queryData = QueryData.QueryData()
try:
queryData.connect()
result = queryData.query(sql)
queryData.close()
self.result = result
except Exception as e:
raise
def editnetwork():
while(True):
isValid = False
cityList = QueryData.citylist()
while(not isValid):
print "Type a valid city to edit, or type 0 or exit to quit"
user_city = raw_input()
isValid = user_city in cityList
if user_city == "exit" or user_city == "quit" or user_city == "q" or user_city == "0":
return
while(True):
s = "Type the number for corresponding options:\n" \
"1. Edit current city's code\n" \
"2. Edit current city's country\n" \
"3. Edit current city's continent\n" \
"4. Edit current city's timezone\n" \
"5. Edit current city's region\n" \
"6. Edit current city's population\n" \
"0. Quit"
print s
i = raw_input("Enter the number.")
i = int(i)
if i == 1:
newcode = raw_input("Type the new code of the city.")
editcode(user_city, newcode)
elif i == 2:
newcountry = raw_input("Type the new country of the city.")
editcountry(user_city, newcountry)
elif i == 3:
newcont = raw_input("Type the new continent of the city.")
editcontinent(user_city, newcont)
elif i == 4:
newzone = raw_input("Type the new timezone of the city.")
edittimezone(user_city, newzone)
elif i == 5:
newreg = raw_input("Type the new region of the city.")
editregion(user_city, newreg)
elif i == 6:
newpop = raw_input("Type the new population of the city.")
editpop(user_city, newpop)
elif i == 0:
break
else:
print "Invalid Number!"
def editnetwork():
while (True):
isValid = False
cityList = QueryData.citylist()
while (not isValid):
print "Type a valid city to edit, or type 0 or exit to quit"
user_city = raw_input()
isValid = user_city in cityList
if user_city == "exit" or user_city == "quit" or user_city == "q" or user_city == "0":
return
while (True):
s = "Type the number for corresponding options:\n" \
"1. Edit current city's code\n" \
"2. Edit current city's country\n" \
"3. Edit current city's continent\n" \
"4. Edit current city's timezone\n" \
"5. Edit current city's region\n" \
"6. Edit current city's population\n" \
"0. Quit"
print s
i = raw_input("Enter the number.")
i = int(i)
if i == 1:
newcode = raw_input("Type the new code of the city.")
editcode(user_city, newcode)
elif i == 2:
newcountry = raw_input("Type the new country of the city.")
editcountry(user_city, newcountry)
elif i == 3:
newcont = raw_input("Type the new continent of the city.")
editcontinent(user_city, newcont)
elif i == 4:
newzone = raw_input("Type the new timezone of the city.")
edittimezone(user_city, newzone)
elif i == 5:
newreg = raw_input("Type the new region of the city.")
editregion(user_city, newreg)
elif i == 6:
newpop = raw_input("Type the new population of the city.")
editpop(user_city, newpop)
elif i == 0:
break
else:
print "Invalid Number!"
def transform(self):
"""
1、从数据表中读取所有员工信息
2、循环遍历每个员工，分别计算每个指标，并存储在字典中,如{"A指标":"##","B指标":"##"}
3、将第2个步骤中的结果存储在数据库中，从而完成转换,插入数据的原则是没有插入，有则修改结果
"""
self.queryData = QueryData.QueryData(host=self.cp.get("mysql","host_2"),user=self.cp.get("mysql","user_2"),pwd=self.cp.get("mysql","pwd_2"),dbname=self.cp.get("mysql","dbname_2"))
self.queryData.connect()
# subSql = "select concat(emp_id,date(create_time)) from bi_sale_info where date(update_time) = date_sub(curdate(),interval 1 day)"
# result4update = self.queryData.query(subSql)
# result4update = [value["concat(emp_id,date(create_time))"] for value in result4update]
# result4update = tuple(result4update)
_sql = "select * from bi_sale_info where date(create_time) between '%s' and '%s'"%(start_date,end_date)
times = 0
while True:
# sql = _sql+" or concat(emp_id,date(create_time)) in"+str(result4update)+" limit %s,%s"%(times*limit,limit)
result_info = self.queryData.query(_sql+" limit %s, %s"%(times*limit,limit))
length = len(result_info)
for entry in result_info:
group = "%s%s%s"%(entry["emp_id"],entry["campus_name"],entry["create_time"][0:10])
hl_md5_obj = hashlib.md5(group.encode("utf-8"))
# 唯一键，标明订单属于哪一个员工，哪一个校区的订单
unique = hl_md5_obj.hexdigest()
if (unique not in self.emp_result.keys()):
self.emp_result[unique] =[{\
"emp_id":entry["emp_id"],\
"emp_name":entry["emp_cn_name"],\
"campus_name":entry["campus_name"],\
"create_date":entry["create_time"][0:10],\
"order_total":0,\
"order_valid_num":0,\
"order_invalid_num":0,\
"invite_total":0,\
"order_no_parents":0,\
"contact_total":0\
}]
everyGroup = self.emp_result[unique][0]
everyGroup["order_total"] +=1
if (entry["order_status"] in ["有效","再联系","邀约成功"]):
everyGroup["order_valid_num"] += 1
if (entry["order_status"] in ["无效"]):
everyGroup["order_invalid_num"] += 1
if (entry["order_status"] in ["邀约成功"]):
everyGroup["invite_total"] += 1
if (entry["order_status"] in ["再联系"]):
everyGroup["contact_total"] += 1
if (entry["description"] and entry["description"].find("非双亲")>=0 ):
everyGroup["order_no_parents"] += 1
times+=1
time.sleep(1)
if length < limit:
print ("完成市场主题转换......")
self.queryData.close()
break
def removecity(city):
del GraphLibrary.city_dictionary[city]
# since the city is removed, we have to remove any route
# which has the city as starting point
code = QueryData.nametocode(city)
for route in GraphLibrary.route_list:
if code in route.startportcode:
del route
def removecity(city):
del GraphLibrary.city_dictionary[city]
# since the city is removed, we have to remove any route
# which has the city as starting point
code = QueryData.nametocode(city)
for route in GraphLibrary.route_list:
if code in route.startportcode:
del route
def extract(self):
delete_before_insert = "delete from bi_visit_info where date(visit_time) between '%s' and '%s' " % (
start_date, end_date)
load_sql = "insert into bi_visit_info(student_id,visit_campus,visit_time,invite_role_id,department_name,v_ftm_id\
) values(%(student_id)s,'%(visit_campus)s','%(visit_time)s',%(invite_role_id)s,'%(department_name)s','%(v_ftm_id)s')\
on duplicate key update student_id=values(student_id),visit_campus=values(visit_campus),invite_role_id=values(invite_role_id),department_name=values(department_name),v_ftm_id=values(v_ftm_id)"
self.queryData = QueryData.QueryData()
self.queryData.connect()
resultSet = self.queryData.query(extract_sql)
resultSet = self.getTarget(resultSet)
conn_load = QueryData.QueryData(host=self.cp.get("mysql", "host_2"),
user=self.cp.get("mysql", "user_2"),
pwd=self.cp.get("mysql", "pwd_2"),
dbname=self.cp.get(
"mysql", "dbname_2"))
conn_load.connect()
conn_load.update(delete_before_insert)
conn_load.commit()
for item in resultSet:
# student_id,visit_campus,invite_role_id,creator_role_id
department_name = self.queryDept(item["invite_role_id"])
time.sleep(0.2)
item["department_name"] = ""
if department_name and department_name.get("data"):
item["department_name"] = department_name["data"][0]
if (not item["visit_campus"]):
item["visit_campus"] = item["source_campus"]
v_ftm_list = self.queryData.query(
"select v_ftm from v_student where student_id=%(student_id)s" %
item)
item["v_ftm_id"] = ""
v_ftm = v_ftm_list[0].get("v_ftm")
if v_ftm:
item["v_ftm_id"] = v_ftm
conn_load.update(load_sql % item)
print("完成visit主题抽取......")
conn_load.commit()
conn_load.close()
self.queryData.close()
def extract(self):
self.queryData = QueryData.QueryData()
self.queryData.connect()
conn_load = QueryData.QueryData(host=self.cp.get("mysql", "host_2"),
user=self.cp.get("mysql", "user_2"),
pwd=self.cp.get("mysql", "pwd_2"),
dbname=self.cp.get(
"mysql", "dbname_2"))
conn_load.connect()
conn_load.update(delete_before_insert)
conn_load.commit()
times = 0
while True:
result = self.queryData.query(
extract_sql % (start_date, end_date, times * limit))
total = len(result)
print("Tmk抽取数量 %s" % total)
for entry in result:
# 处理需要转换的数据
if entry["dept_id"] == 74:
entry["group_name"] = "成人组"
elif entry["dept_id"] == 75:
entry["group_name"] = "少儿组"
elif entry["dept_id"] == 76:
entry["group_name"] = "跟进组"
else:
entry["group_name"] = "null"
for field in entry:
value = entry[field]
if (type(value) == str and value != "null"):
entry[field] = "'" + value + "'"
conn_load.update(extract_load % entry)
times += 1
conn_load.commit()
time.sleep(1)
if (total < limit):
print("完成TMK主题抽取......")
self.queryData.close()
conn_load.close()
break
def shortestroute():
start = ""
finish = ""
cityList = QueryData.citylist()
isValid = False
while not isValid:
start = raw_input("Type a departing city or type exit to quit.")
isValid = start in cityList
if start == "q" or start == "exit":
return
isValid = False
while not isValid:
end = raw_input("Type an arriving city or type exit to quit.")
isValid = start in cityList
if start == "q" or start == "exit":
return
def shortestroute():
start = ""
finish = ""
cityList = QueryData.citylist()
isValid = False
while not isValid:
start = raw_input("Type a departing city or type exit to quit.")
isValid = start in cityList
if start == "q" or start == "exit":
return
isValid = False
while not isValid:
end = raw_input("Type an arriving city or type exit to quit.")
isValid = start in cityList
if start == "q" or start == "exit":
return
def load(self):
# delete_before_insert = "delete from bi_visit_theme where visit_date between '%s' and '%s' "%(start_date,end_date)
load_sql = "insert into bi_visit_theme(visit_campus,visit_num,visit_date) \
values(%(visit_campus)s,%(visit_num)s,%(visit_date)s)\
on duplicate key update visit_num = values(visit_num)"
conn = QueryData.QueryData(host=self.cp.get("mysql", "host_2"),
user=self.cp.get("mysql", "user_2"),
pwd=self.cp.get("mysql", "pwd_2"),
dbname=self.cp.get("mysql", "dbname_2"))
conn.connect()
for hash_key in self.visit_result:
per_theme_data = self.visit_result[hash_key][0]
for field in per_theme_data:
if type(per_theme_data[field]
) == str and per_theme_data[field] != "null":
per_theme_data[field] = "'" + per_theme_data[field] + "'"
conn.update(load_sql % per_theme_data)
self.visit_result = {}
print("完成visit主题存储......")
conn.commit()
conn.close()
def transform(self):
self.queryData = QueryData.QueryData(
host=self.cp.get("mysql", "host_2"),
user=self.cp.get("mysql", "user_2"),
pwd=self.cp.get("mysql", "pwd_2"),
dbname=self.cp.get("mysql", "dbname_2"))
self.queryData.connect()
sql = "select * from bi_visit_info where date(visit_time) between '%s' and '%s' " % (
start_date, end_date)
resultSet = self.queryData.query(sql)
for entry in resultSet:
source_data = "%s%s" % (entry["visit_campus"],
entry["visit_time"][0:10])
source_hash = hashlib.md5(source_data.encode("utf-8")).hexdigest()
if self.visit_result.get(source_hash) == None:
self.visit_result[source_hash]=[{\
"visit_date":entry["visit_time"][0:10],\
"visit_campus":entry["visit_campus"],\
"visit_num":0\
}]
load_entry = self.visit_result[source_hash][0]
load_entry["visit_num"] += 1
print("完成visit主题转换......")
def load(self):
load_insert = "insert into bi_tmk_theme(\
emp_id,emp_name,group_name,order_total,order_new,order_old,campus_name,invite_success_total,\
contact_total,order_invalid_num,calculate_date,order_unknown_num,order_valid_num)\
values(%(emp_id)s,%(emp_name)s,%(group_name)s,%(order_total)s,%(order_new)s,%(order_old)s,\
%(campus_name)s,%(invite_success_total)s,%(contact_total)s,%(order_invalid_num)s,%(calculate_date)s,\
%(order_unknown_num)s,%(order_valid_num)s)\
on duplicate key update emp_name=values(emp_name),\
group_name=values(group_name),\
order_total=values(order_total),\
order_new=values(order_new),\
order_old=values(order_old),\
campus_name=values(campus_name),\
invite_success_total=values(invite_success_total),\
contact_total=values(contact_total),\
order_invalid_num=values(order_invalid_num),\
order_unknown_num=values(order_unknown_num),\
order_valid_num=values(order_valid_num)"
conn = QueryData.QueryData(host=self.cp.get("mysql", "host_2"),
user=self.cp.get("mysql", "user_2"),
pwd=self.cp.get("mysql", "pwd_2"),
dbname=self.cp.get("mysql", "dbname_2"))
conn.connect()
# 目标表中，如果员工号，校区，时间，有一项不同，就需要insert,完全相同则是update
for hash_key in self.tmk_result:
per_theme_data = self.tmk_result[hash_key][0]
for field in per_theme_data:
if type(per_theme_data[field]
) == str and per_theme_data[field] != "null":
per_theme_data[field] = "'" + per_theme_data[field] + "'"
conn.update(load_insert % per_theme_data)
conn.commit()
print("完成TMK主题存储......")
conn.close()
writelogging.logger.info("TMK主题数据装载结束,本次%s条记录" % len(self.tmk_result))
self.tmk_result = {}
def TestaveragePop(self):
key = QueryData.averagecitysize()
self.assertEqual(key, 11796143)
def start(self):
MapParser.MapParser()
s = "Type the number for corresponding options:\n" \
"1. Get all the cities from CSAir\n" \
"2. Get a specific information about one city\n" \
"3. Get all kinds of stats\n" \
"4. Get a specific route\n" \
"5. Edit the current city's information\n" \
"6. Add/Remove cities and routes\n" \
"7. Save the changed list to JSON File\n" \
"8. Calculuate the total flight\n" \
"9. Update CSAir with new Champaign hub town\n" \
"10. Calculate the shortest route between to cities.\n" \
"0. Exit the program"
while True:
print s
i = raw_input("Enter the number.")
i = int(i)
if i == 1:
cityList = QueryData.citylist()
print "Here are the lists!"
for city in cityList:
print city
elif i == 2:
city_name = raw_input("Enter the city name.")
valid = QueryData.getcitydata(city_name)
if valid == False:
print "No such city."
elif i == 3:
QueryData.getstat()
elif i == 4:
city_code = raw_input("Enter the city code.")
valid = QueryData.getroute(city_code)
if valid == False:
print "No such code."
elif i == 5:
EditRoute.editnetwork()
elif i == 6:
EditRoute.addnetwork()
elif i == 7:
SaveFile.saveFile()
elif i == 8:
RouteInfo.routeinfo()
elif i == 9:
MapParser.newMapParser()
elif i == 10:
self.routeInfo.shortestroute()
elif i == 0:
print "See ya!"
break
else:
print "Invalid Number!"
def TestshortestLength(self):
key = QueryData.shortestflight()
self.assertEqual(key, 334)
def TestshortestLength(self):
key = QueryData.shortestflight()
self.assertEqual(key, 334)
__author__ = 'Aman' import QueryData #Uncomment the below to test the accuracy for the code. #print QueryData.testTree() #Uncomment the below if we want to query for a particular row. QueryData.query()
def TestaveragePop(self):
key = QueryData.averagecitysize()
self.assertEqual(key, 11796143)
"5. Edit the current city's information\n" \
"6. Add/Remove cities and routes\n" \
"7. Save the changed list to JSON File\n" \
"8. Calculuate the total flight\n" \
"9. Update CSAir with new Champaign hub town\n" \
"10. Calculate the shortest route between to cities.\n" \
"0. Exit the program"
while True:
print s
i = raw_input("Enter the number.")
i = int(i)
if i == 1:
cityList = QueryData.citylist()
print "Here are the lists!"
for city in cityList:
print city
elif i == 2:
city_name = raw_input("Enter the city name.")
valid = QueryData.getcitydata(city_name)
if valid == False :
print "No such city."
elif i == 3:
QueryData.getstat()
"5. Edit the current city's information\n" \
"6. Add/Remove cities and routes\n" \
"7. Save the changed list to JSON File\n" \
"8. Calculuate the total flight\n" \
"9. Update CSAir with new Champaign hub town\n" \
"10. Calculate the shortest route between to cities.\n" \
"0. Exit the program"
while True:
print s
i = raw_input("Enter the number.")
i = int(i)
if i == 1:
cityList = QueryData.citylist()
print "Here are the lists!"
for city in cityList:
print city
elif i == 2:
city_name = raw_input("Enter the city name.")
valid = QueryData.getcitydata(city_name)
if valid == False:
print "No such city."
elif i == 3:
QueryData.getstat()
def transform(self):
sql = "select * from bi_tmk_info\
where date(update_time) = '%s'"
unique = {}
# 用来查询有没有更早的新单
self.queryData = QueryData.QueryData()
self.queryData.connect()
conn_query = QueryData.QueryData(host=self.cp.get("mysql", "host_2"),
user=self.cp.get("mysql", "user_2"),
pwd=self.cp.get("mysql", "pwd_2"),
dbname=self.cp.get(
"mysql", "dbname_2"))
conn_query.connect()
# subSql = "select concat(emp_id,date(create_time)) from bi_tmk_info where date(update_time) = date_sub(curdate(),interval 1 day)"
# result4update = conn_query.query(subSql)
# result4update = [value["concat(emp_id,date(create_time))"] for value in result4update]
# # 避免因为一个结果生成类似("",)的元组,在sql中会产生错误
# result4update.extend(["查询无结果"+str(datetime.datetime.now())]*2)
# result4update = tuple(result4update)
global start_date
while start_date <= end_date:
result = conn_query.query(sql % start_date)
unique.update(self.dataDistinct(result))
start_date = datetime.datetime.strptime(
start_date, "%Y-%m-%d") + datetime.timedelta(days=1)
start_date = start_date.strftime("%Y-%m-%d")
time.sleep(1)
# 遍历所有记录,去最新数据，当天多个电话去重
for index, _entry in enumerate(unique.values()):
_entry = _entry[0]
source_data = ("%s%s%s" % (_entry["emp_id"], _entry["campus_name"],
_entry["update_time"][0:10]))
source_hash = hashlib.md5(source_data.encode("utf-8")).hexdigest()
if source_hash not in self.tmk_result:
self.tmk_result[source_hash] = [{\
"emp_id":_entry["emp_id"],\
"emp_name":_entry["emp_name"],\
"calculate_date":_entry["update_time"][0:10],\
"campus_name":_entry["campus_name"],\
"group_name":_entry["group_name"],\
"order_total":0,\
"order_new":0,\
"order_old":0,\
"invite_success_total":0,\
"contact_total":0,\
"order_invalid_num":0,\
"order_valid_num":0,\
"order_unknown_num":0\
}]
load_entry = self.tmk_result[source_hash][0]
load_entry["order_total"] += 1
if _entry["validity"] in ["邀约成功"]:
load_entry["invite_success_total"] += 1
if _entry["validity"] in ["再联系"]:
load_entry["contact_total"] += 1
if _entry["validity"] in ["无效"]:
load_entry["order_invalid_num"] += 1
if _entry["validity"] in ["未知"]:
load_entry["order_unknown_num"] += 1
if _entry["validity"] in ["有效"]:
load_entry["order_valid_num"] += 1
result = self.queryData.query(
"select count(*) count from v_invitation where student_id=%s and date(s_d_time)<'%s'"
% (_entry["student_id"], _entry["update_time"][0:10]))
if (not result[0]["count"]):
load_entry["order_new"] += 1
load_entry["order_old"] = load_entry["order_total"] - load_entry[
"order_new"]
self.queryData.close()
conn_query.close()
print("完成tmk主题转换")
def start(self):
MapParser.MapParser()
s = "Type the number for corresponding options:\n" \
"1. Get all the cities from CSAir\n" \
"2. Get a specific information about one city\n" \
"3. Get all kinds of stats\n" \
"4. Get a specific route\n" \
"5. Edit the current city's information\n" \
"6. Add/Remove cities and routes\n" \
"7. Save the changed list to JSON File\n" \
"8. Calculuate the total flight\n" \
"9. Update CSAir with new Champaign hub town\n" \
"10. Calculate the shortest route between to cities.\n" \
"0. Exit the program"
while True:
print s
i = raw_input("Enter the number.")
i = int(i)
if i == 1:
cityList = QueryData.citylist()
print "Here are the lists!"
for city in cityList:
print city
elif i == 2:
city_name = raw_input("Enter the city name.")
valid = QueryData.getcitydata(city_name)
if valid == False :
print "No such city."
elif i == 3:
QueryData.getstat()
elif i == 4:
city_code = raw_input("Enter the city code.")
valid = QueryData.getroute(city_code)
if valid == False :
print "No such code."
elif i == 5:
EditRoute.editnetwork()
elif i == 6:
EditRoute.addnetwork()
elif i == 7:
SaveFile.saveFile()
elif i == 8:
RouteInfo.routeinfo()
elif i == 9:
MapParser.newMapParser()
elif i == 10:
self.routeInfo.shortestroute()
elif i == 0:
print "See ya!"
break
else:
print "Invalid Number!"
def addnetwork():
while(True):
cityList = QueryData.citylist()
s = "Type the number for corresponding options:\n" \
"1. Remove a city\n" \
"2. Remove a route\n" \
"3. Add a city\n" \
"4. Add a route\n" \
"0. Quit"
print s
i = raw_input("Enter the number.")
i = int(i)
if i == 1:
isValid = False
while(not isValid):
print "Type a valid city to remove, or type 0 or exit to quit"
user_city = raw_input()
isValid = user_city in cityList
if user_city == "exit" or user_city == "quit" or user_city == "q" or user_city == "0":
return
removecity(user_city)
elif i == 2:
isValid = False
while(not isValid):
print "Type the first valid city of route."
user_city = raw_input()
isValid = user_city in cityList
if user_city == "exit" or user_city == "quit" or user_city == "q" or user_city == "0":
return
isValid = False
while(not isValid):
city2 = raw_input("Type the second valid city of route.")
isValid = city2 in cityList
removeroute(user_city, city2)
elif i == 3:
newcode = raw_input("Type the new code of city.")
newname = raw_input("Type the new name of city.")
newcountry = raw_input("Type the new country of city.")
newcont = raw_input("Type the new continent of city.")
newzone = raw_input("Type the new timezone of city.")
newcoord1 = raw_input("Type the Latitude of city.")
newcoord2 = raw_input("Type the longitude of city.")
newpop = raw_input("Type the population of city.")
newdist = raw_input("Type the distance of city.")
newreg = raw_input("Type the region of city.")
newpop = int(newpop)
newdist = int(newdist)
newreg = int(newreg)
if newpop > 0 and newdist > 0 and newreg > 0:
addcity(newcode, newname, newcountry, newcont, newzone, newcoord1, newcoord2, newpop, newreg)
else:
print "Invalid info on population/distance/region."
elif i == 4:
isValid = False
while not isValid:
print "Type the first valid city of new route, or type exit to quit."
city1 = raw_input()
isValid = city1 in cityList
if city1 == "exit" or city1 == "quit" or city1 == "q" or city1 == "0":
return
isValid = False
while not isValid:
city2 = raw_input("Type the second valid city of route.")
isValid = city2 in cityList
if city2 == "q" or city2 == "exit":
return
newdist = raw_input("Type the distance of city.")
newdist = int(newdist)
newstart = QueryData.nametocode(city1)
newend = QueryData.codetoname(city2)
if newdist > 0:
addroute(newstart, newend, newdist)
else:
print "Invalid distance."
elif i == 0:
break
else:
print "Invalid Number!"
def findW(plant_num, newTemp, newLight):
dataArray = QueryData.collectData(plant_num)
waterVals = dataArray[0]
colorVals = dataArray[1]
tempVals = dataArray[2]
lightVals = dataArray[3]
correlations = []
correlations.append(regression(waterVals, colorVals))
correlations.append(regression(tempVals, colorVals))
correlations.append(regression(lightVals, colorVals))
mseArray = []
wArray = []
# Use correlations to find predicted value and calculate loss accordingly
for x in range(0, len(waterVals)):
w = random.randint(0, 100)
w = float(w) / 10.0
totalArr = []
wArray.append(w)
for x in range(int(len(colorVals))):
total = correlations[0] * waterVals[x] + correlations[
1] * tempVals[x] + correlations[2] * lightVals[x]
totalArr.append(int(w * total))
mseArray.append(round(getMSE(totalArr, colorVals) / 10, 2))
# Utilize quadric regression to plot MSE values
x = Symbol('x')
A, B, C = np.polyfit(wArray, mseArray, 2)
y = A * x**2 + B * x + C
yprime = str(y.diff(x))
arr = yprime.split(' + ')
if len(arr) == 1:
arr = yprime.split(' - ')
# Parse and convert output to find ideal weight based on loss calculation
stra = str(arr[1])
strb = str(arr[0][0:-2])
converta = float(stra[0:10])
convertb = float(strb[0:10])
predictedW = float(converta) / (float(convertb))
idealCol = GetIdealColor.idealColor()
# Use weights to find theoretical predicted irrigation volume
temporary = float(idealCol) / predictedW
temporary -= (correlations[1] * newTemp + correlations[2] * newLight)
theoreticalX = temporary / correlations[0]
# Remove extreme irrigation volume changes
if theoreticalX < 0:
theoreticalX *= -1
healthy = True
if theoreticalX > 30:
while theoreticalX > 7:
theoreticalX -= 1.1837513
healthy = False
if theoreticalX < 2:
while theoreticalX < 5:
theoreticalX *= 1.23754
healthy = False
# Return theoretical irrigation volume and crop health status
return theoreticalX, healthy
def addnetwork():
while (True):
cityList = QueryData.citylist()
s = "Type the number for corresponding options:\n" \
"1. Remove a city\n" \
"2. Remove a route\n" \
"3. Add a city\n" \
"4. Add a route\n" \
"0. Quit"
print s
i = raw_input("Enter the number.")
i = int(i)
if i == 1:
isValid = False
while (not isValid):
print "Type a valid city to remove, or type 0 or exit to quit"
user_city = raw_input()
isValid = user_city in cityList
if user_city == "exit" or user_city == "quit" or user_city == "q" or user_city == "0":
return
removecity(user_city)
elif i == 2:
isValid = False
while (not isValid):
print "Type the first valid city of route."
user_city = raw_input()
isValid = user_city in cityList
if user_city == "exit" or user_city == "quit" or user_city == "q" or user_city == "0":
return
isValid = False
while (not isValid):
city2 = raw_input("Type the second valid city of route.")
isValid = city2 in cityList
removeroute(user_city, city2)
elif i == 3:
newcode = raw_input("Type the new code of city.")
newname = raw_input("Type the new name of city.")
newcountry = raw_input("Type the new country of city.")
newcont = raw_input("Type the new continent of city.")
newzone = raw_input("Type the new timezone of city.")
newcoord1 = raw_input("Type the Latitude of city.")
newcoord2 = raw_input("Type the longitude of city.")
newpop = raw_input("Type the population of city.")
newdist = raw_input("Type the distance of city.")
newreg = raw_input("Type the region of city.")
newpop = int(newpop)
newdist = int(newdist)
newreg = int(newreg)
if newpop > 0 and newdist > 0 and newreg > 0:
addcity(newcode, newname, newcountry, newcont, newzone,
newcoord1, newcoord2, newpop, newreg)
else:
print "Invalid info on population/distance/region."
elif i == 4:
isValid = False
while not isValid:
print "Type the first valid city of new route, or type exit to quit."
city1 = raw_input()
isValid = city1 in cityList
if city1 == "exit" or city1 == "quit" or city1 == "q" or city1 == "0":
return
isValid = False
while not isValid:
city2 = raw_input("Type the second valid city of route.")
isValid = city2 in cityList
if city2 == "q" or city2 == "exit":
return
newdist = raw_input("Type the distance of city.")
newdist = int(newdist)
newstart = QueryData.nametocode(city1)
newend = QueryData.codetoname(city2)
if newdist > 0:
addroute(newstart, newend, newdist)
else:
print "Invalid distance."
elif i == 0:
break
else:
print "Invalid Number!"
