def assign_closest_reps():
# loop through stores
for store in get_all_store_info():
closest_employee_dist = None
closest_employee_time = None
old_distance = 999999999999999
old_time = 9999999999999999
# loop through employees
for emp in get_all_employee_addresses():
emp_address = emp[2]
store_address = store[3]
data = calculate_distance.get_distance(emp_address, store_address)
distance = data["rows"][0]["elements"][0]["distance"]["value"]
time = data["rows"][0]["elements"][0]['duration']["value"]
if distance < old_distance:
closest_employee_dist = emp[0]
old_distance = distance
if time < old_time:
closest_employee_time = emp[0]
old_time = time
sql = "UPDATE stores " \
"SET closest_employee_dist=" + str(closest_employee_dist) + " " \
"WHERE store_id=" + str(store[0])
cur.execute(sql)
conn.commit()
sql = "UPDATE stores " \
"SET closest_employee_time=" + str(closest_employee_time) + " " \
"WHERE store_id=" + str(store[0])
cur.execute(sql)
conn.commit()
def set_closest_rep_data_emp(emp):
emp_add = get_employee_address(emp)
for store in get_all_store_info():
sql = "SELECT dist_val FROM distance_time WHERE store_id=" + str(store[0]) + " AND emp_id=" + str(emp)
results = cur.execute(sql)
if not results:
data = calculate_distance.get_distance(emp_add[0], store[3])
time_val = data["rows"][0]["elements"][0]["duration"]["value"]
dist_val = data["rows"][0]["elements"][0]["distance"]["value"]
time_text = data["rows"][0]["elements"][0]["duration"]["text"]
dist_text = data["rows"][0]["elements"][0]["distance"]["text"]
sql = "INSERT INTO distance_time (store_id, emp_id, time_val, dist_val, time_text, dist_text) VALUES(" \
"" + str(store[0]) + ", " + str(emp) + ", " + str(time_val) + ", " + str(dist_val) + ", " \
"\"" + time_text + "\", \"" + dist_text + "\")"
cur.execute(sql)
conn.commit()
write_to_json('employees')
def create_closest_rep_data():
# stores = get_all_stores()
# employees = get_all_employee_names()
for store in get_all_stores():
store_add = store[1]
for employee in get_all_employee_addresses():
if verify_new_data(store[0], employee[0]):
emp_add = employee[2]
data = calculate_distance.get_distance(emp_add, store_add)
time_val = data["rows"][0]["elements"][0]["duration"]["value"]
dist_val = data["rows"][0]["elements"][0]["distance"]["value"]
time_text = data["rows"][0]["elements"][0]["duration"]["text"]
dist_text = data["rows"][0]["elements"][0]["distance"]["text"]
sql = "INSERT INTO distance_time (store_id, emp_id, time_val, dist_val, time_text, dist_text) VALUES(" \
"" + str(store[0]) + ", " + str(employee[0]) + ", " + str(time_val) + ", " + str(dist_val) + ", "\
"\"" + time_text + "\", \"" + dist_text + "\")"
cur.execute(sql)
conn.commit()
def optimize_job_assignment():
for job in get_all_jobs():
old_distance = 9999999999999999
old_time = 9999999999999999
for emp in get_all_employee_addresses():
if is_rep_qualified(emp[3], job[3]):
data = calculate_distance.get_distance(emp[2], str(get_store_address(job[2])))
distance = data["rows"][0]["elements"][0]["distance"]["value"]
time = data["rows"][0]["elements"][0]['duration']["value"]
if distance < old_distance:
closest_employee_dist = emp[0]
old_distance = distance
if time < old_time:
closest_employee_time = emp[0]
old_time = time
if closest_employee_time:
sql = "UPDATE jobs SET closest_qual_t_emp=" + str(closest_employee_time) + " WHERE job_id=" + str(job[0])
cur.execute(sql)
conn.commit()
if closest_employee_dist:
sql = "UPDATE jobs SET closest_qual_d_emp=" + str(closest_employee_dist) + " WHERE job_id=" + str(job[0])
cur.execute(sql)
conn.commit()
write_to_json('jobs')
def response_spots(spots, visited_spots, lat, lng, skima_time, spots_amount):
response_list = []
visited_list = []
plan_list = []
for row in visited_spots:
spot_id = row[1]
visited_list.append(spot_id)
for row in spots:
spot_id = row[0]
name = row[1]
spot_lng = row[2]
spot_lat = row[3]
genre = row[4]
stay_time = row[5]
comment = row[6]
plan = row[7]
if spot_id in visited_list:
continue
else:
if lat is None or lng is None:
position = {"latitude": spot_lat, "longitude": spot_lng}
response = {
"spot_id": spot_id,
"name": name,
"position": position,
"genre": genre,
"stay_time": stay_time,
"comment": comment,
"plan": plan
}
response_list.append(response)
plan_list.append(plan)
elif ((skima_time - stay_time) / 2 * 60) >= get_distance(
lat, lng, spot_lat, spot_lng):
position = {"latitude": spot_lat, "longitude": spot_lng}
response = {
"spot_id": spot_id,
"name": name,
"position": position,
"genre": genre,
"stay_time": stay_time,
"comment": comment,
"plan": plan
}
response_list.append(response)
plan_list.append(plan)
if len(response_list) == 0:
return {"spots": response_list}
elif spots_amount is None:
if len(response_list) < 4:
random_list = tuple(
np.random.choice(response_list,
size=len(response_list),
replace=False,
p=weights_list(plan_list)))
return {"spots": random_list}
else:
random_list = tuple(
np.random.choice(response_list,
size=4,
replace=False,
p=weights_list(plan_list)))
return {"spots": random_list}
else:
if len(response_list) < spots_amount:
random_list = tuple(
np.random.choice(response_list,
size=len(response_list),
replace=False,
p=weights_list(plan_list)))
return {"spots": random_list}
else:
random_list = tuple(
np.random.choice(response_list,
size=spots_amount,
replace=False,
p=weights_list(plan_list)))
return {"spots": random_list}