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}