def update_point_sales(dest_id, sales_id, day):
# update last dest
sql = route_records.updatePointSales('dest_id', dest_id, 'D', sales_id,
day)
print(sql)
pgsqlib.execScalar(sql)
if day <= 11:
# get sales
check_sales('P')
sales = get_sales()
new_sales_name = sales["sales_name"]
new_sales_id = sales["sales_id"]
new_ori_id = sales["ori_id"]
print(new_sales_name, new_sales_id, new_ori_id)
# insert point sales
sql = route_records.insertionPointSales(new_sales_name, new_sales_id,
day, new_ori_id)
print(sql)
pgsqlib.execScalar(sql)
else:
print("All day is done")
def check_sales(status):
ori = route_records.check_sales(status)
print(ori)
res = pgsqlib.fetch(ori)
if len(res) == 0:
print("sales sudah ganti")
sql = route_records.proc_sales_id()
pgsqlib.execScalar(sql)
else:
pass
def update_sales_queue(dest_id, sales_id, day):
ori = route_records.check_sales('Q')
print(ori)
res = pgsqlib.fetch(ori)
print("len update sales %s" % len(res))
if len(res) == 0:
#update tb_route_fc
sql = route_records.updatePointSales('dest_id', dest_id, 'D', sales_id,
day)
print(sql)
pgsqlib.execScalar(sql)
print("all sales has been used")
else:
pass
def main():
day = 0
while day <= 11:
print("day is %s" % day)
try:
initiate_route(day)
while check_point_sales() > 0:
recursive_extraction()
except IndexError:
pass
day += 1
#update tb_field_collector
sql = route_records.update_sales_status()
print(sql)
pgsqlib.execScalar(sql)
def initiate_route(day):
# update tb_sales status to process
sql = route_records.proc_sales_id()
print(sql)
pgsqlib.execScalar(sql)
sales = get_sales()
sales_id = sales["sales_id"]
sales_name = sales["sales_name"]
ori_id = sales["ori_id"]
sql = route_records.insertionPointSales(sales_name, sales_id, day, ori_id)
print(sql)
pgsqlib.execScalar(sql)
recursive_extraction()
def create_table():
sql = prep_records.create_tb_sales()
print (sql)
pgsqlib.execScalar(sql)
sql = prep_records.create_tb_points()
print (sql)
pgsqlib.execScalar(sql)
sql = prep_records.create_tb_route_directions()
print (sql)
pgsqlib.execScalar(sql)
sql = prep_records.create_tb_suggestion()
print (sql)
pgsqlib.execScalar(sql)
sql = prep_records.create_tb_route_sales()
print (sql)
pgsqlib.execScalar(sql)
def clustering(): data_constraint = [357,305] n = 0 while n <= 11: sql = prep_records.select_first_cluster_id() res = pgsqlib.fetch(sql) ori_id = res[0][0] if n == 5 or n == 11: limit_constraint = data_constraint[1] else: limit_constraint = data_constraint[0] sql = prep_records.update_cluster_table(n, ori_id, limit_constraint) print(sql) pgsqlib.execScalar(sql) sql = prep_records.insert_depo(n) print(sql) pgsqlib.execScalar(sql) n += 1
def data_prep(master_outlet_clean, area_branch, m_salesman):
create_table()
sql = prep_records.create_outlet_table(master_outlet_clean)
print (sql)
pgsqlib.execScalar(sql)
sql = prep_records.update_error_outlet(master_outlet_clean)
print (sql)
pgsqlib.execScalar(sql)
sql = prep_records.update_error_branch(master_outlet_clean, area_branch)
print (sql)
pgsqlib.execScalar(sql)
sql = prep_records.insert_tb_points(master_outlet_clean)
print (sql)
pgsqlib.execScalar(sql)
clustering()
sql = prep_records.insert_fsr(m_salesman)
print (sql)
pgsqlib.execScalar(sql)
sql = prep_records.update_fsr_ori()
print (sql)
pgsqlib.execScalar(sql)
def recursive_extraction():
#get sales detil
sales = get_ori_id()
sales_id = sales["sales_id"]
sales_name = sales["sales_name"]
ori_id = sales["ori_id"]
day = sales["day"]
if day <= 11:
try:
# get route suggestion
route = get_ori(ori_id, day)
ori_id = route["ori_id"]
ori_lat = route["ori_lat"]
ori_lon = route["ori_lon"]
dest_id = route["dest_id"]
dest_lat = route["dest_lat"]
dest_lon = route["dest_lon"]
distance = route["distance"]
duration = route["duration"]
polyline = route["polyline"]
# check constraint
durs = get_durs(sales_id, day)
diss = get_diss(sales_id, day)
durs += duration
print("durs ditambah duration is %s" % durs)
diss += distance
cons = routes_constraint(durs, diss, day)
enough = cons["enough"]
print("enough is %s" % enough)
durs = cons["durs"]
print("durs ditambah stop is %s" % durs)
diss = cons["diss"]
except KeyError:
print("keyError")
enough = "yes"
if enough != "yes":
print((sales_name, sales_id, day, ori_id, ori_lat, ori_lon,
dest_id, dest_lat, dest_lon, distance, duration, polyline))
sql = route_records.insertionPoints(sales_name, sales_id, day,
ori_id, ori_lat, ori_lon,
dest_id, dest_lat, dest_lon,
distance, duration, polyline)
print(sql)
pgsqlib.execScalar(sql)
# update flag tb_route_direction
sql = route_records.update_route_flags(ori_id)
print(sql)
pgsqlib.execScalar(sql)
#update tb_route_sales
sql = route_records.updatePointSales('dest_id', dest_id, 'D',
sales_id, day)
print(sql)
pgsqlib.execScalar(sql)
sql = route_records.updatePointSales('ori_id', dest_id, 'Q',
sales_id, day)
print(sql)
pgsqlib.execScalar(sql)
#repeat the process
recursive_extraction()
else:
#update status one sales_id to done
sql = route_records.update_sales_id(sales_id, 'D', 'P')
print(sql)
pgsqlib.execScalar(sql)
#update flag tb_route_direction
sql = route_records.update_route_flags(ori_id)
print(sql)
pgsqlib.execScalar(sql)
#update bulk tb_sales status
update_sales_queue(ori_id, sales_id, day)
#insert new point
update_point_sales(ori_id, sales_id, day)
print("Done processing sales id %s, day %s" % (sales_id, day))
else:
print("all day is done")