Notes

from calendar import month_name
from flask import Flask, render_template, request, send_file, send_from_directory, Response, flash,redirect
import pandas as pd
from demand import *
import os
from collections import deque
from models import db, DailyDemand
# from flask_sqlalchemy import SQLAlchemy

app = Flask( __name__ )
#app.config['UPLOAD_FOLDER'] = 'files'
app.config['DOWNLOAD_FOLDER'] = 'Results'
app.secret_key = os.urandom(12)
# app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///Demand.db'
# app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# db.init_app(app)


# app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite://db.sqlite3'
# app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# db = SQLAlchemy(app)

# class Upload(db.Model):
# id = db.Column(db.Integer, primary_key=True)
# filename = db.Column(db.String(50))
# file_data = db.Column(db.LargeBinary)

d = {}

# @app.before_first_request
# def create_table():
# db.create_all()

@app.route('/monthly_demand', methods=['GET', 'POST'])
def monthly_inputs():

if request.method == 'POST':

global d

file = d['file']
monthly_demand = d['monthly_demand']
IS_demand_dic = d['IS_demand']

month_name = request.form["select_month"]
d['current_month'] = month_name
routes = set(request.form.getlist('Route'))

if 'Routes' in d:
d['Routes'][month_name] = routes
else:
Routes = {}
Routes[month_name] = routes
d['Routes'] = Routes


if month_name in IS_demand_dic: #in rb terms

IS_demand = IS_demand_dic[month_name]
else:
IS_demand = 0
# MBR_percentage = int(d['MBR'])
RB_CB_Ratio = float(d['ratio'])
Plant_demand = monthly_demand[month_name] # in cb terms


#data = pd.read_excel('result.xlsx')
data = Route_Optimization(file, Plant_demand, IS_demand, RB_CB_Ratio)
d['alloc'] = data[1]
d['remaining_plant_demand'] = data[2]
d['remaining_IS_demand'] = data[3]
d['MBR_stakeholders'] = data[4]
data = data[0]
d['result_combinations'] = data


titles = data.columns.values
rows = [list(data[i].values) for i in titles]
rows = np.array(rows).T.tolist()
length = len(data)
name = 'monthly_demand'




return render_template('data.html',name = name, rows = rows, titles = data.columns.values, length=length, month_name = month_name)

return render_template('Monthly_demand_inputs.html')




@app.route('/create', methods=['GET', 'POST'])
def create_row():
if request.method == 'GET':

global d

month_name = d['current_month']
month_dic = {'Jan' : '01', 'Feb' : '02', 'Mar' : '03', 'Apr' : '04', 'May' : '05', 'Jun' : '06', 'Jul' : '07', 'Aug' : '08', 'Sep' : '09', 'Oct' : '10', 'Nov' : '11', 'Dec' : '12' }
month_number = month_dic[month_name]

if 'month_day' not in d or d['month_day'] == 0:
d['month_day'] = 1




if int(month_number) >= 4:
year = '2023'
else:
year = '2024'


if len(str(d['month_day'])) == 1:
month_day = '0' + str(d['month_day'])
else:
month_day = str(d['month_day'])

date = month_day + '-' + month_number + '-' + year

d['current_date'] = date






return render_template('create.html', date = date)

if request.method == 'POST':

date = d['current_date']
d['month_day'] += 1
try:
initial_inventory_plant = int(request.form['initial_inventory_plant'])
except:
if 'initial_inventory_plant' in d:
initial_inventory_plant = d['initial_inventory_plant']
else:
initial_inventory_plant = 0

daily_plant_consumption = int(request.form['daily_consumption_plant'])
IS_buildup = int(request.form['IS_buildup'])
TRI = int(request.form['TRI'])



if 'prev_order_qty_from_plant' in d:
prev_order_qty_from_plant = d['prev_order_qty_from_plant']
else:
prev_order_qty_from_plant = 0

if 'prev_order_qty_transit' in d:
prev_order_qty_transit = d['prev_order_qty_transit']
else:
prev_order_qty_transit = 0

if 'order_qty_from_plant_deque' in d:
order_qty_from_plant_deque = d['order_qty_from_plant_deque']
else:
order_qty_from_plant_deque = deque()

if 'order_qty_IS_deque' in d:
order_qty_IS_deque = d['order_qty_IS_deque']
else:
order_qty_IS_deque = deque()

if 'prev_transit_IS' in d:
prev_transit_IS = d['prev_transit_IS']
else:
prev_transit_IS = 0

if 'beginning_inventory_IS' in d:
beginning_inventory_IS = d['beginning_inventory_IS']
else:
beginning_inventory_IS = 0

result = row_wise_daily_demand(date, initial_inventory_plant, beginning_inventory_IS, daily_plant_consumption, IS_buildup, TRI, prev_order_qty_from_plant, prev_order_qty_transit, order_qty_from_plant_deque, order_qty_IS_deque, prev_transit_IS)

if result == -1:
return render_template('error_daily.html')
total_order_qty_grower, transit_qty_IS, ending_inventory_IS, delivered_to_IS,beginning_inventory_IS, order_qty_transit, order_qty_from_plant, Ending_Inventory_Plant_CB_Storage, order_delivery_at_plant, order_qty_from_plant_deque, order_qty_IS_deque, supplied_from_IS = result[0], result[1], result[2], result[3], result[4], result[5], result[6], result[7], result[8], result[9], result[10], result[11]

d['prev_order_qty_from_plant'] = order_qty_from_plant
d['prev_order_qty_transit'] = order_qty_transit
d['order_qty_from_plant_deque'] = order_qty_from_plant_deque
d['order_qty_IS_deque'] = order_qty_IS_deque
d['prev_transit_IS'] = transit_qty_IS
d['initial_inventory_plant'] = Ending_Inventory_Plant_CB_Storage
d['beginning_inventory_IS'] = ending_inventory_IS

row = DailyDemand(
date = date,
Initial_inventory_plant = initial_inventory_plant,
Daily_plant_consumption = daily_plant_consumption,
Delivery_at_CB_storage = order_delivery_at_plant,
Ending_inventory_plant = Ending_Inventory_Plant_CB_Storage,
TRI_at_plant = TRI,
Order_from_plant = order_qty_from_plant,
transit_plant = order_qty_transit,
Initial_inventory_IS = beginning_inventory_IS,
Delivered_to_IS = delivered_to_IS,
Supplied_from_IS = supplied_from_IS,
Ending_inventory_IS = ending_inventory_IS,
transit_IS = transit_qty_IS,
IS_buildup = IS_buildup,
Total_order_grower = total_order_qty_grower
)


db.session.add(row)
db.session.commit()
return redirect('/show')

@app.route('/show', methods=['GET', 'POST'])
def show():
rows = DailyDemand.query.all()
return render_template('datalist.html', rows = rows)

@app.route('/<int:id>/delete', methods=['GET', 'POST'])
def delete(id):
global d
last_row = DailyDemand.query.order_by(-DailyDemand.id).first()

if request.method == 'POST':
if id == last_row.id:
db.session.delete(last_row)
db.session.commit()

if 'month_day' in d:
d['month_day'] -=1
return redirect('/show')
abort(404)
else:
return render_template('delete_error.html')
return render_template('delete.html')

@app.route('/<int:id>/edit', methods=['GET', 'POST'])
def edit(id):
global d
last_row = DailyDemand.query.order_by(-DailyDemand.id).first()

if id != last_row.id:
return render_template('edit_error.html')
else:
if request.method == 'POST':


last_row.Initial_inventory_plant = int(request.form['initial_inventory_plant'])
last_row.Daily_plant_consumption = int(request.form["daily_consumption_plant"])
last_row.Delivery_at_CB_storage = int(request.form["delivery_at_plant"])
last_row.Ending_inventory_plant = int(request.form["ending_inventory_plant"])
last_row.TRI_at_plant = int(request.form["TRI"])
last_row.Order_from_plant = int(request.form["order_from_plant"])
last_row.transit_plant = int(request.form["order_transit_plant"])
last_row.Initial_inventory_IS = int(request.form["beginning_inventory_IS"])
last_row.Delivered_to_IS = int(request.form["delivered_to_IS"])
last_row.Supplied_from_IS = int(request.form["supplied_from_IS"])
last_row.Ending_inventory_IS = int(request.form["ending_inventory_IS"])
last_row.transit_IS = int(request.form["transit_IS"])
last_row.IS_buildup = int(request.form["IS_buildup"])
last_row.Total_order_grower = int(request.form["total_order_grower"])

d['prev_order_qty_from_plant'] = last_row.Order_from_plant
d['prev_order_qty_transit'] = last_row.transit_plant

if 'order_qty_from_plant_deque' in d:
d['order_qty_from_plant_deque'].pop()
d['order_qty_from_plant_deque'].append(last_row.Order_from_plant)

if 'order_qty_IS_deque' in d:
d['order_qty_IS_deque'].pop()
d['order_qty_IS_deque'].append(last_row.Delivered_to_IS)
d['prev_transit_IS'] = last_row.transit_IS
d['initial_inventory_plant'] = last_row.Ending_inventory_plant
d['beginning_inventory_IS'] = last_row.Ending_inventory_IS

db.session.commit()

flash('Data got Updated')

return redirect('/show')

return render_template('edit.html', row = last_row)


















@app.route('/', methods=['GET', 'POST'])
def index():
return render_template('index.html')

@app.route('/data', methods=['GET', 'POST'])
def data():
if request.method == 'POST':

#file = request.form['file']
file = request.files['file']
file.save(file.filename)
#print('*****************************************',os.path.join(app.config['UPLOAD_FOLDER'],file.filename))
#file.save(os.path.abspath(os.path.dirname(__file__)),app.config['UPLOAD_FOLDER'])
#df = pd.read_excel(file)
#d = int(request.form['demand'])
Annual_demand = int(request.form['Annual_demand'])
RB_CB_Ratio = float(request.form['ratio'])
# MBR_percentage = int(request.form['MBR_percentage'])

data = Demand_Planning(file, Annual_demand, RB_CB_Ratio)

#print('*************************************',file)

global d

d['monthly_demand'] = data[1]
d['IS_demand'] = data[2]
data = data[0]

d['file'] = file.filename
d['data'] = data
# d['MBR'] = MBR_percentage
d['ratio'] = RB_CB_Ratio
d['Annual_demand'] = data




#data = Route_Optimization(file,Plant_demand, IS_demand, RB_CB_Ratio, MBR_percentage)


#data = pd.read_excel('result.xlsx')
titles = data.columns.values
rows = [list(data[i].values) for i in titles]
length = len(data)
data = data.T
row0 = data.columns.values
rows.insert(0,row0)
rows = np.array(rows).T.tolist()
data = data.T
name = 'annual_demand'


return render_template('data.html',name = name, rows = rows, titles = data.columns.values, length=length)

else:

data = d['Annual_demand']
titles = data.columns.values
rows = [list(data[i].values) for i in titles]
length = len(data)
data = data.T
row0 = data.columns.values
rows.insert(0,row0)
rows = np.array(rows).T.tolist()
data = data.T
name = 'annual_demand'


return render_template('data.html',name = name, rows = rows, titles = data.columns.values, length=length)


@app.route('/ex_monthly_demand', methods=['GET', 'POST'])
def data2():
#if request.method == 'POST':

#file = request.form['file']
#file = request.files['file']
#file.save(os.path.join(app.config['UPLOAD_FOLDER'],file.filename))
#file.save(os.path.abspath(os.path.dirname(__file__)),app.config['UPLOAD_FOLDER'])
#df = pd.read_excel(file)
#d = int(request.form['demand'])
#Annual_demand = int(request.form['Annual_demand'])
#RB_CB_Ratio = float(request.form['ratio'])
#MBR_percentage = int(request.form['MBR_percentage'])





global d

file = d['file']
data = d['data']
data = data.T
#print('*************************************',file)
#file = open(str(file))


IS_demand = int(data['IS Buildup Order Qty (in RB)'][0]) #in rb terms
# MBR_percentage = int(d['MBR'])
RB_CB_Ratio = float(d['ratio'])
Plant_demand = int(data['Monthly Demand'][0]) # in cb terms


#data = pd.read_excel('result.xlsx')
data = Route_Optimization(file, Plant_demand, IS_demand, RB_CB_Ratio)
d['alloc'] = data[1]
d['remaining_plant_demand'] = data[2]
d['remaining_IS_demand'] = data[3]
d['MBR_stakeholders'] = data[4]
data = data[0]
d['result_combinations'] = data

titles = data.columns.values
rows = [list(data[i].values) for i in titles]
rows = np.array(rows).T.tolist()
length = len(data)
name = 'monthly_demand'




return render_template('data.html',name = name, rows = rows, titles = data.columns.values, length=length)

@app.route('/new_monthly_demand', methods=['GET', 'POST'])
def data3():
#if request.method == 'POST':

#file = request.form['file']
#file = request.files['file']
#file.save(os.path.join(app.config['UPLOAD_FOLDER'],file.filename))
#file.save(os.path.abspath(os.path.dirname(__file__)),app.config['UPLOAD_FOLDER'])
#df = pd.read_excel(file)
#d = int(request.form['demand'])
#Annual_demand = int(request.form['Annual_demand'])
#RB_CB_Ratio = float(request.form['ratio'])
#MBR_percentage = int(request.form['MBR_percentage'])





global d

file = d['file']
data = d['data']
data = data.T
#print('*************************************',file)
#file = open(str(file))


IS_demand = int(data['IS Buildup Order Qty (in RB)'][0]) #in rb terms
# MBR_percentage = int(d['MBR'])
RB_CB_Ratio = float(d['ratio'])
Plant_demand = int(data['Monthly Demand'][0]) # in cb terms


#data = pd.read_excel('result.xlsx')
data = new_Route_Optimization(file, Plant_demand, IS_demand, RB_CB_Ratio)

d['alloc'] = data[2]
d['remaining_plant_demand'] = data[3]
d['remaining_IS_demand'] = data[4]
d['MBR_stakeholders'] = data[5]
data = [data[0],data[1]]
d['result_combinations'] = data

titles1 = data[0].columns.values
titles2 = data[1].columns.values
rows1 = [list(data[0][i].values) for i in titles1]
rows2 = [list(data[1][i].values) for i in titles2]
rows1 = np.array(rows1).T.tolist()
rows2 = np.array(rows2).T.tolist()
length1 = len(data[0])
length2 = len(data[1])
name = 'monthly_demand'




return render_template('new_data.html',name = name, rows1 = rows1, rows2 = rows2, titles1 = data[0].columns.values, titles2 = data[1].columns.values, length1=length1, length2=length2)


@app.route('/allocation', methods=['GET', 'POST'])
def Allocation_dashboard():
global d
file = d['file']
alloc = d['alloc']
result = d['result_combinations']
MBR = d['MBR_stakeholders']


data = Allocation(result,alloc, MBR)

titles = data.columns.values
rows = [list(data[i].values) for i in titles]
rows = np.array(rows).T.tolist()
length = len(data)
name = 'Allocation'

return render_template('data.html',name = name, rows = rows, titles = data.columns.values, length=length)

@app.route('/dashboard', methods=['GET', 'POST'])
def final_dashboard():
global d

file = d['file']
df1 = d['result_combinations'][0]
df2 = d['result_combinations'][1]
# df = d['result_combinations']
remaining_plant_demand = d['remaining_plant_demand']
remaining_IS_demand = d['remaining_IS_demand']

data = dashboard2(file, df1, df2, remaining_plant_demand, remaining_IS_demand)
# data = dashboard(file, df, remaining_plant_demand, remaining_IS_demand)

titles = data.columns.values
rows = [list(data[i].values) for i in titles]
rows = np.array(rows).T.tolist()
length = len(data)
name = 'dashboard'

return render_template('data.html',name = name, rows = rows, titles = data.columns.values, length=length)

@app.route('/download', methods=['GET', 'POST'])
def download():

filename = 'result.xlsx'
#print(filename,'*******************************************************************')
excelDownload = open(filename,'rb').read()
#return send_from_directory(path, filename=filename, as_attachment=True)
#return send_file(path, as_attachment=True)

return Response(
excelDownload,
mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
headers={"Content-disposition":
"attachment; filename=result.xlsx"})

@app.route('/daily_demand', methods=['GET', 'POST'])
def Daily_Demand():

global d

# APRIL MONTH INPUT
Month_Name = 'Apr'
Month_number = 4
year = 2023
is_First_Month = True
Initial_Inventory_Plant_CB_Storage = 19500
beginning_inventory_IS = 0
Daily_Plant_Consumption = 1134 #originally it will be calculated everyday and will reflect on db daily, but for sake of simple calculation we are taking the constant value
TRI = 6000
order_qty_from_plant_deque = deque()
order_qty_IS_deque = deque()
prev_order_qty_from_plant = 0
prev_order_qty_transit = 0
Routes = {1,2,3}
order_qty_IS_Buildup = 174
prev_transit_IS = 0

result = calculate_daily_demand(Month_Name, Month_number, year, is_First_Month, Initial_Inventory_Plant_CB_Storage,beginning_inventory_IS, Daily_Plant_Consumption, TRI, order_qty_from_plant_deque, order_qty_IS_deque, prev_order_qty_from_plant, prev_order_qty_transit,order_qty_IS_Buildup, Routes, prev_transit_IS)
data = result[0]
d['daily_route_demand'] = data['Total Order Qty - To Growers'][0]

data = data.T
titles = data.columns.values
rows = [list(data[i].values) for i in titles]
length = len(data)
data = data.T
row0 = data.columns.values
rows.insert(0,row0)
rows = np.array(rows).T.tolist()
data = data.T
name = 'daily'

return render_template('data.html',name = name, rows = rows, titles = data.columns.values, length=length)

@app.route('/<int:id>/daily_demand_routes', methods=['GET', 'POST'])
def Daily_Demand_Routes(id):
global d

row = DailyDemand.query.filter_by(id=id).first()
date = row.date
daily_demand = row.Order_from_plant
file = d['file']
MBR_percentage = d['MBR']
RB_CB_Ratio = d['ratio']
IS_demand = row.IS_buildup
data = daily_demand_route_calculation(file, daily_demand, MBR_percentage, RB_CB_Ratio, IS_demand)



titles = data.columns.values
rows = [list(data[i].values) for i in titles]
rows = np.array(rows).T.tolist()
length = len(data)

name = 'daily_routes'








return render_template('data.html',name = name, rows = rows, titles = data.columns.values, length=length, date=date)








if __name__ == '__main__':
app.run( host='0.0.0.0', port=443, debug = True )