#!/usr/bin/python
# -*- coding: utf-8 -*-

import math
import numpy as np
import json
import pickle
import os.path
from os import path
import shutil
import subprocess
import pymongo
import uuid
import pandas as pd
from pulp import *
import excelrd
from flask import Flask, request, jsonify
from flask_cors import CORS
import mysql.connector
import requests
import time
import secrets
import string
from datetime import datetime
from pulp import LpStatus, LpStatusInfeasible, LpStatusUnbounded, LpStatusNotSolved, LpStatusUndefined
import urllib.request
import sys
import requests


app = Flask(__name__)
CORS(app)

#CORS(app, resources={r"/": {"origins": ""}})

UPLOAD_FOLDER = 'Backend'
ALLOWED_EXTENSIONS = {'xlsx', 'xls'}

app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER
stop_process = False

def count_distinct_months(input_str):
    months_list = [month.strip() for month in input_str.split(',')]
    unique_months_count = len(set(months_list))
    return unique_months_count

def generate_random_id(length=14):
    alphabet = string.ascii_letters + string.digits
    random_id = ''.join(secrets.choice(alphabet) for _ in range(length))
    return random_id

def connect_to_database():
    host = 'localhost'
    user = 'root'
    password = ''
    database = 'Ladakh'
    connection = mysql.connector.connect(
        host=host, user=user, password=password, database=database
    )
    return connection


def allowed_file(filename):
    return '.' in filename and filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS


@app.route('/')
def hello():
    return 'Hi, PDS!'


@app.route('/get_users', methods=['GET'])
def get_users():
    if request.method == 'GET':
        connection = connect_to_database()
        user_list = []

        if connection.is_connected():
            cursor = connection.cursor()
            query = 'SELECT * FROM login WHERE 1'
            cursor.execute(query)
            user = cursor.fetchall()
            connection.close()

            if user:
                for row in user:
                    temp = {'username': row[0], 'password': row[1], '_id': row[2]}
                    user_list.append(temp)
                return jsonify(user_list)
            else:
                return jsonify(user_list)
        else:
            return jsonify(user_list)

@app.route('/extract_db', methods=['POST'])
def extract_db():
    if request.method == 'POST':
        connection = connect_to_database()
        warehouse_data = []
        fps_data = []
        all_data = {}
        dcp_data = []
        applicableCount = request.form.get('applicable')

        if connection.is_connected():
            cursor = connection.cursor()
            query = "SELECT * FROM warehouse WHERE active='1' and warehousetype != 'FCI'"
            cursor.execute(query)
            user = cursor.fetchall()
            
            if user:
                for row in user:
                    temp = {'State Name':'','WH_District': row[0], 'WH_Name': row[1], 'WH_ID': row[2], 'Type of WH': row[3], 'WH_Lat': row[5], 'WH_Long': row[6], 'Storage_Capacity': row[7], 'Owned/Rented':'', 'quantity of Wheat stored (Quintals)':''}
                    warehouse_data.append(temp)
                    
        if connection.is_connected():
            cursor = connection.cursor()
            query = "SELECT * FROM dcp WHERE active='1'"
            cursor.execute(query)
            user = cursor.fetchall()
                
            if user:
                for row in user:
                    temp1 = {'State Name':'','WH_District': row[0], 'WH_Name': row[1], 'WH_ID': row[2], 'Type': row[3], 'WH_Lat': row[4], 'WH_Long': row[5], 'Capacity': row[6], 'Processing':row[9], 'quantity of Wheat stored (Quintals)':''}
                    dcp_data.append(temp1)
            
                    
            cursor = connection.cursor()
            query = "SELECT * FROM fps WHERE active='1'"
            cursor.execute(query)
            user = cursor.fetchall()
            connection.close()

            if user:
                for row in user:
                    temp = {'State Name':'','FPS_District': row[0], 'FPS_Name': row[1], 'FPS_ID': row[2], 'Motorable/Non-Motorable': row[3], 'FPS_Lat': row[4], 'FPS_Long': row[5], 'Allocation_Wheat': float(row[6])*int(applicableCount), 'Allocation_Rice': float(row[8])*int(applicableCount),'Allocation_FRice': float(row[9])*int(applicableCount), 'FPS_Tehsil':''}
                    fps_data.append(temp)
                    #print(fps_data)
                
            all_data["warehouse"] = warehouse_data
            all_data["fps"] = fps_data
            all_data["dcp"] = dcp_data
            json_file_path = 'output.json'
            with open(json_file_path, 'w') as json_file:
                json.dump(all_data, json_file, indent=2)
        else:
            json_file_path = 'output.json'
            with open(json_file_path, 'w') as json_file:
                json.dump(all_data, json_file, indent=2)
        
        json_file_path = 'output.json'
        with open(json_file_path, 'r') as json_file:
            data = json.load(json_file)

        wh = pd.DataFrame(data['warehouse'])
        fps = pd.DataFrame(data['fps'])
        dcp = pd.DataFrame(data['dcp'])
        wh = wh.loc[:,["State Name","WH_District",'WH_Name',"WH_ID","Type of WH",'WH_Lat',"WH_Long","Storage_Capacity","Owned/Rented","quantity of Wheat stored (Quintals)"]]
        fps = fps.loc[:,["State Name","FPS_District",'FPS_Name',"FPS_ID","Motorable/Non-Motorable",'FPS_Lat',"FPS_Long","Allocation_Wheat","Allocation_Rice","Allocation_FRice","FPS_Tehsil"]]
        

        # Rename the columns to make them valid Python identifiers
        column_mapping = {
            'Type of WH': 'Type',
            'Storage_Capacity': 'Storage_Capacity',
            'WH_District': 'WH_District',
            'WH_ID': 'WH_ID',
            'WH_Lat': 'WH_Lat',
            'WH_Long': 'WH_Long',
            'WH_Name': 'WH_Name'
        }

        wh.rename(columns=column_mapping, inplace=True)
        wh.rename(columns=column_mapping, inplace=True)
        wh_filtered = wh[wh["Type"] != 'fci']
        
        def convert_to_numeric(value):
            try:
                return pd.to_numeric(value)
            except ValueError:
                return value

        # Apply the function to the DataFrame
        print(dcp)
        wh_filtered['WH_ID'] = wh['WH_ID'].apply(convert_to_numeric)
        fps['FPS_ID'] = fps['FPS_ID'].apply(convert_to_numeric)
        dcp['WH_ID'] = dcp['WH_ID'].apply(convert_to_numeric)

        # Save DataFrames to Excel file
        with pd.ExcelWriter('Backend//Data_1.xlsx') as writer:
            wh_filtered.to_excel(writer, sheet_name='A.1 Warehouse', index=False)
            fps.to_excel(writer, sheet_name='A.2 FPS', index=False)
            dcp.to_excel(writer, sheet_name='A.3 Mill', index=False)

        return {"success":1}
        
@app.route('/extract_data', methods=['POST'])
def extract_data():
    if request.method == 'POST':
        try:
            connection = connect_to_database()
            tablename = ""
            data = []
            fci_data=[]
            dcp_data=[]
            
            if connection.is_connected():
                cursor = connection.cursor()
                query = "SELECT id FROM optimised_table ORDER BY last_updated DESC LIMIT 1"
                cursor.execute(query)
                ids = cursor.fetchall()
                for id_ in ids:
                    tablename = "optimiseddata_" + id_[0]
            

            if connection.is_connected():
                cursor = connection.cursor()
                query = "SELECT * FROM fci WHERE active='1'"
                cursor.execute(query)
                user = cursor.fetchall()
                
                if user:
                    for row in user:
                        temp1 = {'State Name':'','WH_District': row[0], 'WH_Name': row[1], 'WH_ID': row[2], 'Type of WH': row[3], 'WH_Lat': row[4], 'WH_Long': row[5], 'Allotment_Wheat': row[6], 'Allotment_Rice':row[9], 'Allotment_FRice':row[10],'quantity of Wheat stored (Quintals)':''}
                        fci_data.append(temp1)
                        
                        
            
                
                cursor = connection.cursor()
                query = "SELECT * FROM {}".format(tablename)
                
                cursor.execute(query)
                result = cursor.fetchall()
                columns = ["From","From ID", "From name", "from district", "from lat", "from long","commodity","quantity"]
                tableData = [columns]
                

                for row in result:
                    #print(row)
                    if row[20] != "" and row[20] is not None:
                        id = row[20]
                        query_warehouse = "SELECT latitude, longitude, district,from FROM warehouse WHERE id=%s"
                        cursor.execute(query_warehouse, (id,))
                        result_warehouse = cursor.fetchone()
                        if result_warehouse:
                            row = list(row)
                            row[6], row[7], row[5] = result_warehouse
                            row[3] = row[20]
                            row[4] = row[22]
                            row[17] = row[26]
                    elif row[21] != "" and row[21] is not None and row[19] == "yes":
                        id = row[21]
                        query_warehouse = "SELECT latitude, longitude, district,from FROM warehouse WHERE id=%s"
                        cursor.execute(query_warehouse, (id,))
                        result_warehouse = cursor.fetchone()
                        if result_warehouse:
                            row = list(row)
                            row[6], row[7], row[5],row[1] = result_warehouse
                            row[3] = row[21]
                            row[4] = row[23]
                            row[17] = row[27]
                          

                    #tableData.append(list(row))
                    data.append({
                                "From":row[1],
                                "From ID": row[3],
                                "From name": row[4],
                                "from district": row[5],
                                "from lat": row[6],
                                "from long": row[7],
                                "commodity":row[15],
                                "quantity": row[16]
                            })
                response = {}
                response['status'] = 1
                response['data'] = data
                response['fci_data'] = fci_data
                json_file_path = 'output_fci.json'
                with open(json_file_path, 'w') as json_file:
                    json.dump(response, json_file, indent=2)
                    
                json_file_path = 'output_fci.json'
                with open(json_file_path, 'r') as json_file:
                   data = json.load(json_file)
                    
                
                wh = pd.DataFrame(data['data'])
                fci = pd.DataFrame(data['fci_data'])   
                
                
                wh = wh.loc[:,["From","From ID","From name",'from district',"from lat","from long","commodity","quantity"]]
                fci = fci.loc[:,["State Name","WH_District",'WH_Name',"WH_ID","Type of WH",'WH_Lat',"WH_Long","Storage_Capacity"]]    
               

                column_mapping = {
                            'From ID': 'SW_ID',
                            'From name': 'SW_Name',
                            'from district': 'SW_District',
                            'from lat': 'SW_lat',
                            'from long': 'SW_Long',
                            'From': 'SW_Type',
                           
                        }                
                wh.rename(columns=column_mapping, inplace=True)
                wh['quantity'] = wh['quantity'].apply(pd.to_numeric, errors='coerce')
                
                wh.rename(columns=column_mapping, inplace=True)
                wh['quantity'] = wh['quantity'].apply(pd.to_numeric, errors='coerce')
                
                has_bf = 'Atta' in wh['Demand_Atta'].unique()
                has_rf = 'Rice' in wh['Demand_Rice'].unique()
                has_wheat = 'FRice' in wh['Demand_FRice'].unique()
                
                
                wh = wh.pivot_table(index=['SW_ID', 'SW_Name', 'SW_District', 'SW_lat', 'SW_Long','SW_Type'], columns='commodity', values='quantity', aggfunc='sum').reset_index()
                wh.fillna(0, inplace=True)
                wh.index.name = None
                
                
                
                # Rename only if present, otherwise add as 0
                if has_bf:
                    wh.rename(columns={'Atta': 'Demand_Atta'}, inplace=True)
                else:
                    wh['Demand_Atta'] = 0

                if has_rf:
                    wh.rename(columns={'Rice': 'Demand_Rice'}, inplace=True)
                else:
                    wh['Demand_Rice'] = 0
                    
                if has_wheat:
                    wh.rename(columns={'FRice': 'Demand_FRice'}, inplace=True)
                else:
                    wh['Demand_FRice'] = 0    
                
                def convert_to_numeric(value):
                    try:
                        return pd.to_numeric(value)
                    except ValueError:
                        return value
                        
                # Apply the function to the DataFrame
                wh['SW_ID'] = wh['SW_ID'].apply(convert_to_numeric)
                fci['WH_ID'] = fci['WH_ID'].apply(convert_to_numeric)
                
                    
                with pd.ExcelWriter('Backend//Data_2.xlsx') as writer:
                    wh.to_excel(writer, sheet_name='A.1 Warehouse', index=False)
                    fci.to_excel(writer, sheet_name='A.2 FCI', index=False)
                    
                return response
            else:
                return {"success": 0, "message": "Database connection failed"}
        except Exception as e:
            return {"success": 0, "message": str(e)}
    else:
        return {"success": 0, "message": "Invalid request method"}
        
@app.route('/fetchdatafromsql', methods=['GET'])        
def fetch_data_from_sql():
    if request.method == 'GET':
        connection = connect_to_database()
        if connection.is_connected():
            cursor = connection.cursor()
            query = "SELECT * FROM optimised_table"
            cursor.execute(query)
            data = cursor.fetchall()
            cursor.close()
            connection.close()
            df = pd.DataFrame(data, columns=['id', 'month', 'year', 'applicable', 'data', 'last_updated', 'rolled_out', 'cost'])
            df_first_4_columns = df[['id', 'month', 'year', 'applicable']]
            # Convert selected columns to JSON string
            json_data = df_first_4_columns.to_json(orient='records')
            return json_data
        else:
            #print("Error: Unable to connect to the database")
            return jsonify({"error": "Unable to connect to the database"})
    else:
        return jsonify({"error": "Request method is not GET"})

@app.route('/uploadConfigExcel', methods=['POST'])
def upload_config_excel():
    data = {}
    try:
        file = request.files['uploadFile']
        if file and allowed_file(file.filename):
            file_path = os.path.join(app.config['UPLOAD_FOLDER'], 'Data_1.xlsx')
            os.makedirs(app.config['UPLOAD_FOLDER'], exist_ok=True)
            file.save(file_path)
            data['status'] = 1
            df = pd.read_excel(file_path)
        else:
            data['status'] = 0
            data['message'] = 'Invalid file. Only .xlsx or .xls files are allowed.'
    except Exception as e:
        data['status'] = 0
        data['message'] = 'Error uploading file'
        
        
    input = pd.ExcelFile('Backend//Data_1.xlsx')
    node1 = pd.read_excel(input,sheet_name="A.1 Warehouse")
    node2 = pd.read_excel(input,sheet_name="A.2 FPS")
    dist = [[0 for a in range(len(node2["FPS_ID"]))] for b in range(len(node1["WH_ID"]))]
    phi_1 = []
    phi_2 = []
    delta_phi = []
    delta_lambda = []
    R = 6371 

    for i in node1.index:
        for j in node2.index:
            phi_1=math.radians(node1["WH_Lat"][i])
            phi_2=math.radians(node2["FPS_Lat"][j])
            delta_phi=math.radians(node2["FPS_Lat"][j]-node1["WH_Lat"][i])
            delta_lambda=math.radians(node2["FPS_Long"][j]-node1["WH_Long"][i])
            x=math.sin(delta_phi / 2.0) ** 2 + math.cos(phi_1) * math.cos(phi_2) * math.sin(delta_lambda / 2.0) ** 2
            y=2 * math.atan2(math.sqrt(x), math.sqrt(1 - x))
            dist[i][j]=R*y
            
    dist=np.transpose(dist)
    df3 = pd.DataFrame(data = dist, index = node2['FPS_ID'], columns = node1['WH_ID'])
    df3.to_excel('Backend//Distance_Matrix.xlsx', index=True)
    return jsonify(data)



@app.route('/getfcidata', methods=['POST'])
def fci_data():
    try:
        usn = pd.ExcelFile('Backend//Data_1.xlsx')
        fci = pd.read_excel(usn, sheet_name='A.1 Warehouse', index_col=None)
        fps = pd.read_excel(usn, sheet_name='A.2 FPS', index_col=None)
        mill = pd.read_excel(usn, sheet_name='A.3 Mill', index_col=None)
       
        warehouse_no = fci['WH_ID'].nunique()
        fps_no = fps['FPS_ID'].nunique()
        mill_no = mill['WH_ID'].nunique()
        combined_districts = pd.concat([fci['WH_District'],fps['FPS_District']])
        districts_no = combined_districts.nunique()
        total_demand = float(fps['Allocation_Wheat'].sum())
        total_demand_rice = float(fps['Allocation_Rice'].sum())
        total_demand_frice = float(fps['Allocation_FRice'].sum())
        total_supply = float(fci['Storage_Capacity'].sum())
        total_processing = float(mill['Processing'].sum())

        result = {'Warehouse_No': warehouse_no, 'FPS_No': fps_no,'Mill_No':mill_no, 'Total_Demand': total_demand,'Total_Demand_Rice': total_demand_rice, 'Total_Supply': total_supply,'Total_Processing':total_processing, 'District_Count': districts_no,'Total_Demand_FRice': total_demand_frice}
        return jsonify(result)
        #print(result)
    except Exception as e:
        return jsonify({'status': 0, 'message': str(e)})

@app.route('/getfcidataleg1', methods=['POST'])
def fci_dataleg1():
    try:
        usn = pd.ExcelFile('Backend//Data_2.xlsx')
        wh = pd.read_excel(usn, sheet_name='A.1 Warehouse', index_col=None)
        fci = pd.read_excel(usn, sheet_name='A.2 FCI', index_col=None)
        #print("Ruby1")
        warehouse_no = fci['WH_ID'].nunique()
        fps_no = wh["SW_ID"].nunique()
        combined_districts = pd.concat([fci['WH_District'],wh['SW_District']])
        districts_no = combined_districts.nunique()
        total_demand = float(wh['Allocation_Wheat'].sum())
        total_demand_rice = float(wh['Allocation_Rice'].sum())
        total_demand_frice = float(wh['Allocation_FRice'].sum())
        total_supply = float(fci['Storage_Capacity'].sum())
        #print("Ruby")

        result = {'Warehouse_No': warehouse_no, 'FPS_No': fps_no, 'Total_Demand': total_demand, 'Total_Supply': total_supply, 'District_Count': districts_no,'Total_Demand_Rice': total_demand_rice,'Total_Demand_FRice': total_demand_frice,}
        
        return jsonify(result)
    except Exception as e:
        return jsonify({'status': 0, 'message': str(e)})


@app.route('/getGraphData', methods=['POST'])
def graph_data():
    try:
        usn = pd.ExcelFile('Backend//Data_1.xlsx')
        FCI = pd.read_excel(usn, sheet_name='A.1 Warehouse', index_col=None)
        FPS = pd.read_excel(usn, sheet_name='A.2 FPS', index_col=None)
        Mill = pd.read_excel(usn, sheet_name='A.3 Mill', index_col=None)


        
        District_Capacity = {}
        for i in range(len(FCI["WH_District"])):
            District_Name = FCI["WH_District"][i]
            if District_Name not in District_Capacity:
                District_Capacity[District_Name] = float(FCI["Storage_Capacity"][i])
            else:
                District_Capacity[District_Name] += float(FCI["Storage_Capacity"][i])

        District_Millprocessing = {}
        for i in range(len(Mill["WH_District"])):
            District_Name = Mill["WH_District"][i]
            if District_Name not in District_Millprocessing:
                District_Millprocessing[District_Name] = float(Mill["Processing"][i])
            else:
                District_Millprocessing[District_Name] += float(Mill["Processing"][i])

                
        District_Demand = {}
        for i in range(len(FPS["FPS_District"])):
            District_Name_FPS = FPS["FPS_District"][i]
            if District_Name_FPS not in District_Demand:
                District_Demand[District_Name_FPS] = float(FPS["Allocation_Wheat"][i])
            else:
                District_Demand[District_Name_FPS] += float(FPS["Allocation_Wheat"][i])
                
        District_Demand_Rice = {}
        for i in range(len(FPS["FPS_District"])):
            District_Name_FPS = FPS["FPS_District"][i]
            if District_Name_FPS not in District_Demand_Rice:
                District_Demand_Rice[District_Name_FPS] = float(FPS["Allocation_Rice"][i])
            else:
                District_Demand_Rice[District_Name_FPS] += float(FPS["Allocation_Rice"][i])
        
        District_Demand_FRice = {}
        for i in range(len(FPS["FPS_District"])):
            District_Name_FPS = FPS["FPS_District"][i]
            if District_Name_FPS not in District_Demand_FRice:
                District_Demand_FRice[District_Name_FPS] = float(FPS["Allocation_FRice"][i])
            else:
                District_Demand_FRice[District_Name_FPS] += float(FPS["Allocation_FRice"][i])
                
        District_Demand_Total = {}
        for i in range(len(FPS["FPS_District"])):
            District_Name_FPS = FPS["FPS_District"][i]
            if District_Name_FPS not in District_Demand_Total:
                District_Demand_Total[District_Name_FPS] = float(FPS["Allocation_Wheat"][i])+float(FPS["Allocation_Rice"][i])+float(FPS["Allocation_FRice"][i])
            else:
                District_Demand_Total[District_Name_FPS] += float(FPS["Allocation_Wheat"][i])+float(FPS["Allocation_Rice"][i])+float(FPS["Allocation_FRice"][i])

                
        District_Name = []
        District_Name2=[]
        District_Name = [i for i in District_Demand_Total if i not in District_Capacity]
        District_Name2 = [i for i in District_Demand_Total if i in District_Capacity and District_Demand_Total[i] >= District_Capacity[i]]
        District_Name_1 = {}
        District_Name_1['District_Name_All'] = District_Name + District_Name2
        District_Name3 = [i for i in District_Demand_Total if i in District_Capacity and District_Demand_Total[i] <= District_Capacity[i]]

        


        
        combined_data = {'District_Demand': District_Demand, 'District_Capacity': District_Capacity, 'District_Name': District_Name_1,'District_Demand_Rice': District_Demand_Rice,'District_Demand_FRice': District_Demand_FRice,'Mill_Processing':District_Millprocessing}
        #print(combined_data)
        
        return jsonify(combined_data)
    except Exception as e:
        return jsonify({'status': 0, 'message': str(e)})
        
@app.route('/getGraphDataleg1', methods=['POST'])
def graph_dataleg1():
    try:
        usn = pd.ExcelFile('Backend//Data_2.xlsx')
        wh = pd.read_excel(usn, sheet_name='A.1 Warehouse', index_col=None)
        fci = pd.read_excel(usn, sheet_name='A.2 FCI', index_col=None)
        
        


        
        District_Capacity = {}
        for i in range(len(fci["WH_District"])):
            District_Name = fci["WH_District"][i]
            if District_Name not in District_Capacity:
                District_Capacity[District_Name] = float(fci["Storage_Capacity"][i])
            else:
                District_Capacity[District_Name] += float(fci["Storage_Capacity"][i])
        

        District_Demand = {}
        for i in range(len(wh["SW_District"])):
            District_Name_FPS = wh["SW_District"][i]
            if District_Name_FPS not in District_Demand:
                District_Demand[District_Name_FPS] = float(wh["Allocation_Wheat"][i])
            else:
                District_Demand[District_Name_FPS] += float(wh["Allocation_Wheat"][i])
                
       
                
        District_Demand_Rice = {}
        for i in range(len(wh["SW_District"])):
            District_Name_FPS = wh["SW_District"][i]
            if District_Name_FPS not in District_Demand_Rice:
                District_Demand_Rice[District_Name_FPS] = float(wh["Allocation_Rice"][i])
            else:
                District_Demand_Rice[District_Name_FPS] += float(wh["Allocation_Rice"][i])
                
        District_Demand_FRice = {}
        for i in range(len(wh["SW_District"])):
            District_Name_FPS = wh["SW_District"][i]
            if District_Name_FPS not in District_Demand_FRice:
                District_Demand_FRice[District_Name_FPS] = float(wh["Allocation_FRice"][i])
            else:
                District_Demand_FRice[District_Name_FPS] += float(wh["Allocation_FRice"][i])
       
        
        District_Demand_Total = {}
        for i in range(len(wh["SW_District"])):
            District_Name_FPS = wh["SW_District"][i]
            if District_Name_FPS not in District_Demand_Total:
                District_Demand_Total[District_Name_FPS] = float(wh["Allocation_Wheat"][i])+float(wh["Allocation_Rice"][i]) +float(wh["Allocation_FRice"][i])
            else:
                District_Demand_Total[District_Name_FPS] += float(wh["Allocation_Wheat"][i])+float(wh["Allocation_Rice"][i])+ +float(wh["Allocation_FRice"][i])
                
                
        District_Name = []
        District_Name2=[]
        District_Name = [i for i in District_Demand_Total if i not in District_Capacity]
        District_Name2 = [i for i in District_Demand_Total if i in District_Capacity and District_Demand_Total[i] >= District_Capacity[i]]
        District_Name_1 = {}
        District_Name_1['District_Name_All'] = District_Name + District_Name2
        District_Name3 = [i for i in District_Demand_Total if i in District_Capacity and District_Demand_Total[i] <= District_Capacity[i]]

        


        
        combined_data = {'District_Demand': District_Demand, 'District_Capacity': District_Capacity, 'District_Name': District_Name_1,'District_Demand_Rice': District_Demand_Rice,'District_Demand_FRice': District_Demand_FRice,}
        
        
        
        return jsonify(combined_data)
    except Exception as e:
        return jsonify({'status': 0, 'message': str(e)})



def check_id_exists(connection, random_id):
    cursor = connection.cursor()
    query = "SELECT COUNT(*) FROM optimised_table WHERE id = %s"
    cursor.execute(query, (random_id,))
    result = cursor.fetchone()[0]
    return result > 0
    
def check_id_exists_leg1(connection, random_id):
    cursor = connection.cursor()
    query = "SELECT COUNT(*) FROM optimised_table_leg1 WHERE id = %s"
    cursor.execute(query, (random_id,))
    result = cursor.fetchone()[0]
    return result > 0   

def check_year_month_exists(connection, month, year):
    cursor = connection.cursor()
    query = "SELECT COUNT(*) FROM optimised_table WHERE month = %s and year = %s"
    cursor.execute(query, (month,year,))
    result = cursor.fetchone()[0]
    return result > 0
    
def check_year_month_exists_leg1(connection, month, year):
    cursor = connection.cursor()
    query = "SELECT COUNT(*) FROM optimised_table_leg1 WHERE month = %s and year = %s"
    cursor.execute(query, (month,year,))
    result = cursor.fetchone()[0]
    return result > 0

def get_year_month_exists(connection, month, year):
    cursor = connection.cursor()
    query = "SELECT id FROM optimised_table WHERE month = %s and year = %s"
    cursor.execute(query, (month,year,))
    result = cursor.fetchone()
    return result[0] if result else None
    
def get_year_month_exists_leg1(connection, month, year):
    cursor = connection.cursor()
    query = "SELECT id FROM optimised_table_leg1 WHERE month = %s and year = %s"
    cursor.execute(query, (month,year,))
    result = cursor.fetchone()
    return result[0] if result else None

#@app.route('/saveToDatabase', methods=['GET'])
def save_to_database(month, year, applicable):
    connection = connect_to_database()
    random_id = generate_random_id()
    while (check_id_exists(connection,random_id)):
        random_id = generate_random_id()
    table_name = "optimiseddata_" + str(random_id)
    warehouse_table = "warehouse_" + str(random_id)
    fps_table = "fps_" + str(random_id)
    dcp_table = "dcp_" + str(random_id)
    if connection.is_connected():
        cursor = connection.cursor()
        current_datetime = datetime.now()
        formatted_datetime = current_datetime.strftime("%Y-%m-%d %H:%M:%S")
        if(check_year_month_exists(connection, month, year)):
            existingid = get_year_month_exists(connection, month, year);
            sql = "UPDATE optimised_table set applicable='" + applicable + "', last_updated='" + formatted_datetime + "' WHERE id='" + existingid + "'"; 
            table_name = "optimiseddata_" + str(existingid)
            warehouse_table = "warehouse_" + str(existingid)
            fps_table = "fps_" + str(existingid)
            dcp_table = "dcp_" + str(existingid)
            cursor.execute(sql)
        else:
            sql = "INSERT INTO optimised_table (id, month, year, applicable,last_updated) VALUES ('" + random_id + "','" + month + "','" + year + "','" + applicable + "','" + formatted_datetime + "')";
            cursor.execute(sql)
        
        connection.commit()
        warehouse_drop_query = 'DROP TABLE IF EXISTS ' + warehouse_table;
        cursor.execute(warehouse_drop_query)
        connection.commit()
        create_warehouse_query = ("CREATE TABLE " + warehouse_table + " (district VARCHAR(100) NOT NULL, name VARCHAR(100) NOT NULL, id VARCHAR(100) NOT NULL, warehousetype VARCHAR(100) NOT NULL, type VARCHAR(100) NOT NULL, latitude VARCHAR(100) NOT NULL, longitude VARCHAR(100) NOT NULL, storage VARCHAR(100) NOT NULL, uniqueid VARCHAR(100) NOT NULL, active VARCHAR(10) NOT NULL DEFAULT '1')")
        cursor.execute(create_warehouse_query)
        connection.commit()
        copy_warehouse_data = ("INSERT INTO " + warehouse_table + " SELECT * FROM warehouse WHERE active='1'")
        cursor.execute(copy_warehouse_data)
        connection.commit()
        
        fps_drop_query = 'DROP TABLE IF EXISTS ' + fps_table;
        cursor.execute(fps_drop_query)
        create_fps_query = ("CREATE TABLE " + fps_table + " (district VARCHAR(100) NOT NULL, name VARCHAR(100) NOT NULL, id VARCHAR(100) NOT NULL, type VARCHAR(100) NOT NULL, latitude VARCHAR(100) NOT NULL, longitude VARCHAR(100) NOT NULL,demand VARCHAR(100) NOT NULL,uniqueid VARCHAR(100) NOT NULL,demand_rice VARCHAR(100) NOT NULL, demand_frice VARCHAR(100) NOT NULL, active VARCHAR(10) NOT NULL DEFAULT '1')")
        cursor.execute(create_fps_query)
        connection.commit()
        copy_fps_data = ("INSERT INTO " + fps_table + " SELECT * FROM fps WHERE active='1'")
        cursor.execute(copy_fps_data)
        connection.commit()
        
        dcp_drop_query = 'DROP TABLE IF EXISTS ' + dcp_table;
        cursor.execute(dcp_drop_query)
        create_dcp_query = ("CREATE TABLE " + dcp_table + " (district VARCHAR(100) NOT NULL, name VARCHAR(100) NOT NULL, id VARCHAR(100) NOT NULL, type VARCHAR(100) NOT NULL, latitude VARCHAR(100) NOT NULL, longitude VARCHAR(100) NOT NULL,demand VARCHAR(100) NOT NULL,uniqueid VARCHAR(100) NOT NULL, active VARCHAR(100) NOT NULL, demand_rice VARCHAR(10) NOT NULL DEFAULT '1')")
        cursor.execute(create_dcp_query)
        connection.commit()
        copy_dcp_data = ("INSERT INTO " + dcp_table + " SELECT * FROM dcp WHERE active='1'")
        cursor.execute(copy_dcp_data)
        connection.commit()
        
        excel_file_path = 'Backend//Result_Sheet.xlsx'
        columns_to_fetch = ['Scenario','From','From_State','From_ID','From_Name','From_District','From_Lat','From_Long','To','To_State','To_ID','To_Name', 'To_District', 'To_Lat', 'To_Long','commodity','quantity','Distance']
        df = pd.read_excel(excel_file_path)
        selected_data = df[columns_to_fetch]
        sql = 'DROP TABLE IF EXISTS ' + table_name;
        cursor.execute(sql)
        connection.commit()
        
        sql = "CREATE TABLE " + table_name + " ( scenario VARCHAR(150) NOT NULL, `from` VARCHAR(150) NOT NULL,from_state VARCHAR(150) NOT NULL, from_id VARCHAR(150) NOT NULL, from_name VARCHAR(150) NOT NULL, from_district VARCHAR(150) NOT NULL, from_lat VARCHAR(150) NOT NULL,from_long VARCHAR(150) NOT NULL, `to` VARCHAR(150) NOT NULL,to_state VARCHAR(150) NOT NULL,to_id VARCHAR(150) NOT NULL, to_name VARCHAR(150) NOT NULL, to_district VARCHAR(150) NOT NULL, to_lat VARCHAR(150) NOT NULL, to_long VARCHAR(150) NOT NULL, commodity VARCHAR(150) NOT NULL,quantity VARCHAR(150) NOT NULL, distance VARCHAR(150) NOT NULL, approve_admin VARCHAR(100) , approve_district VARCHAR(100) , new_id_admin VARCHAR(100), new_id_district VARCHAR(100) , new_name_admin VARCHAR(100) , new_name_district VARCHAR(10) , reason_admin VARCHAR(255) , reason_district VARCHAR(255), new_distance_admin VARCHAR(100), new_distance_district VARCHAR(100), district_change_approve VARCHAR(100), status VARCHAR(100) )";
        cursor.execute(sql)
        connection.commit()
        
        for (index, row) in selected_data.iterrows():
            sql = 'INSERT INTO ' + table_name + ' (`scenario`, `from`, `from_state`, `from_id`, `from_name`, `from_district`, `from_lat`, `from_long`, `to`, `to_state`, `to_id`, `to_name`, `to_district`, `to_lat`, `to_long`, `commodity`, `quantity`, `distance`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
            values = tuple(row)
            cursor.execute(sql, values)
            connection.commit()
 
    if connection.is_connected():
        cursor.close()
        connection.close()
    return jsonify({'status': 1})
    
def save_to_database_leg1(month, year, applicable):
    connection = connect_to_database()
    random_id = generate_random_id()
    while (check_id_exists_leg1(connection,random_id)):
        random_id = generate_random_id()
    table_name = "optimiseddata_leg1_" + str(random_id)
    warehouse_table = "warehouse_leg1_" + str(random_id)
    fci_table = "fci_leg1_" + str(random_id)
    if connection.is_connected():
        cursor = connection.cursor()
        current_datetime = datetime.now()
        formatted_datetime = current_datetime.strftime("%Y-%m-%d %H:%M:%S")
        if(check_year_month_exists_leg1(connection, month, year)):
            existingid = get_year_month_exists_leg1(connection, month, year);
            sql = "UPDATE optimised_table_leg1 set applicable='" + applicable + "', last_updated='" + formatted_datetime + "' WHERE id='" + existingid + "'"; 
            #print(sql)
            table_name = "optimiseddata_leg1_" + str(existingid)
            warehouse_table = "warehouse_leg1_" + str(existingid)
            fci_table = "fci_leg1_" + str(existingid)
            cursor.execute(sql)
        else:
            sql = "INSERT INTO optimised_table_leg1 (id, month, year, applicable,last_updated) VALUES ('" + random_id + "','" + month + "','" + year + "','" + applicable + "','" + formatted_datetime + "')";
            cursor.execute(sql)
        
        connection.commit()
        warehouse_drop_query = 'DROP TABLE IF EXISTS ' + warehouse_table;
        #print(warehouse_drop_query)
        cursor.execute(warehouse_drop_query)
        connection.commit()
        create_warehouse_query = ("CREATE TABLE " + warehouse_table + " (district VARCHAR(100) NOT NULL, name VARCHAR(100) NOT NULL, id VARCHAR(100) NOT NULL, warehousetype VARCHAR(100) NOT NULL, type VARCHAR(100) NOT NULL, latitude VARCHAR(100) NOT NULL, longitude VARCHAR(100) NOT NULL, storage VARCHAR(100) NOT NULL, uniqueid VARCHAR(100) NOT NULL, active VARCHAR(10) NOT NULL DEFAULT '1')")
        cursor.execute(create_warehouse_query)
        connection.commit()
        copy_warehouse_data = ("INSERT INTO " + warehouse_table + " SELECT * FROM warehouse WHERE active='1' AND warehousetype<>'fci'")
        cursor.execute(copy_warehouse_data)
        connection.commit()
        
        fci_drop_query = 'DROP TABLE IF EXISTS ' + fci_table;
        cursor.execute(fci_drop_query)
        create_fci_query = ("CREATE TABLE " + fci_table + " (district VARCHAR(100) NOT NULL, name VARCHAR(100) NOT NULL, id VARCHAR(100) NOT NULL, warehousetype VARCHAR(100) NOT NULL, type VARCHAR(100) NOT NULL, latitude VARCHAR(100) NOT NULL, longitude VARCHAR(100) NOT NULL, storage VARCHAR(100) NOT NULL, uniqueid VARCHAR(100) NOT NULL, active VARCHAR(10) NOT NULL DEFAULT '1')")
        cursor.execute(create_fci_query)
        connection.commit()
        copy_fci_data = ("INSERT INTO " + fci_table + " SELECT * FROM warehouse WHERE active='1' AND warehousetype='fci'")
        cursor.execute(copy_fci_data)
        connection.commit()
        
        excel_file_path = 'Backend//Result_Sheet_leg1.xlsx'
        ##print("****************")
        #print(excel_file_path)
        #print("****************")
        columns_to_fetch = ['Scenario','From','From_State','From_ID','From_Name','From_District','From_Lat','From_Long','To','To_State','To_ID','To_Name', 'To_District', 'To_Lat', 'To_Long','commodity','quantity','Distance']
        df = pd.read_excel(excel_file_path)
        selected_data = df[columns_to_fetch]
        sql = 'DROP TABLE IF EXISTS ' + table_name;
        cursor.execute(sql)
        connection.commit()
        
        sql = "CREATE TABLE " + table_name + " ( scenario VARCHAR(150) NOT NULL, `from` VARCHAR(150) NOT NULL,from_state VARCHAR(150) NOT NULL, from_id VARCHAR(150) NOT NULL, from_name VARCHAR(150) NOT NULL, from_district VARCHAR(150) NOT NULL, from_lat VARCHAR(150) NOT NULL,from_long VARCHAR(150) NOT NULL, `to` VARCHAR(150) NOT NULL,to_state VARCHAR(150) NOT NULL,to_id VARCHAR(150) NOT NULL, to_name VARCHAR(150) NOT NULL, to_district VARCHAR(150) NOT NULL, to_lat VARCHAR(150) NOT NULL, to_long VARCHAR(150) NOT NULL, commodity VARCHAR(150) NOT NULL,quantity VARCHAR(150) NOT NULL, distance VARCHAR(150) NOT NULL, approve_admin VARCHAR(100) , approve_district VARCHAR(100) , new_id_admin VARCHAR(100), new_id_district VARCHAR(100) , new_name_admin VARCHAR(100) , new_name_district VARCHAR(10) , reason_admin VARCHAR(255) , reason_district VARCHAR(255), new_distance_admin VARCHAR(100), new_distance_district VARCHAR(100), district_change_approve VARCHAR(100), status VARCHAR(100) )";
        cursor.execute(sql)
        connection.commit()
        
        for (index, row) in selected_data.iterrows():
            sql = 'INSERT INTO ' + table_name + ' (`scenario`, `from`, `from_state`, `from_id`, `from_name`, `from_district`, `from_lat`, `from_long`, `to`, `to_state`, `to_id`, `to_name`, `to_district`, `to_lat`, `to_long`, `commodity`, `quantity`, `distance`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
            values = tuple(row)
            cursor.execute(sql, values)
            connection.commit()
 
    if connection.is_connected():
        cursor.close()
        connection.close()
    return jsonify({'status': 1})




#@app.route('/saveMonthlyData', methods=['POST'])
def save_monthly_data(month, year, data):
    connection = connect_to_database()
    table_name = "optimised_table"
    
    try:
        if connection.is_connected():
            cursor = connection.cursor()

            # Check if data for the given year and month already exists
            sql_check = "SELECT id FROM " + table_name + " WHERE year = %s AND month = %s"
            cursor.execute(sql_check, (year, month))
            existing_data = cursor.fetchone()

            if existing_data:
                # Update existing data
                sql_update = "UPDATE " + table_name + " SET data = %s WHERE id = %s"
                values_update = (data, existing_data[0])
                cursor.execute(sql_update, values_update)
            else:
                # Insert new data
                random_id = str(uuid.uuid4())
                sql_insert = "INSERT INTO " + table_name + " (month, year, data, id) VALUES (%s, %s, %s, %s)"
                values_insert = (month, year, data, random_id)
                cursor.execute(sql_insert, values_insert)
            connection.commit()
    except mysql.connector.Error as err:
        # Handle the error, print or log it
        print(f"Error: {err}")
        return jsonify({'status': 0, 'error': str(err)})
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
    
    return jsonify({'status': 1})


def save_monthly_data_leg1(month, year, data):
    connection = connect_to_database()
    table_name = "optimised_table_leg1"
    
    try:
        if connection.is_connected():
            cursor = connection.cursor()

            # Check if data for the given year and month already exists
            sql_check = "SELECT id FROM " + table_name + " WHERE year = %s AND month = %s"
            cursor.execute(sql_check, (year, month))
            existing_data = cursor.fetchone()

            if existing_data:
                # Update existing data
                sql_update = "UPDATE " + table_name + " SET data = %s WHERE id = %s"
                values_update = (data, existing_data[0])
                cursor.execute(sql_update, values_update)
            else:
                # Insert new data
                random_id = str(uuid.uuid4())
                sql_insert = "INSERT INTO " + table_name + " (month, year, data, id) VALUES (%s, %s, %s, %s)"
                values_insert = (month, year, data, random_id)
                cursor.execute(sql_insert, values_insert)
            connection.commit()
    except mysql.connector.Error as err:
        # Handle the error, print or log it
        print(f"Error: {err}")
        return jsonify({'status': 0, 'error': str(err)})
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
    
    return jsonify({'status': 1})


@app.route('/readMonthlyData', methods=['POST'])
def get_monthly_data():
    try:
        connection = connect_to_database()
        table_name = "optimised_table"

        if connection.is_connected():
            cursor = connection.cursor()

            # Retrieve all data from the monthlydata table
            sql_select_all = "SELECT year, month, data FROM " + table_name
            cursor.execute(sql_select_all)
            data_rows = cursor.fetchall()

            # Convert data to a list of dictionaries
            columns = [column[0] for column in cursor.description]
            result = [dict(zip(columns, row)) for row in data_rows]

    except mysql.connector.Error as err:
        # Handle the error, print or log it
        print(f"Error: {err}")
        return jsonify({'status': 0, 'error': str(err)})
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

    return jsonify({'status': 1, 'data': result})
   
@app.route('/processCancel', methods=['POST'])
def processCancel():
    global stop_process
    stop_process = True
    data = {}
    data['status'] = 0
    data['message'] = "process stopped"
    json_data = json.dumps(data)
    json_object = json.loads(json_data)
    return json.dumps(json_object, indent=1)

@app.route('/processFile', methods=['POST'])
def processFile():
    global stop_process
    stop_process = False
    scenario_type = request.form.get('type')
    if scenario_type == "intra":
        message = 'DataFile file is incorrect'
        try:
            USN = pd.ExcelFile('Backend//Data_1.xlsx')
            month = request.form.get('month')        
            year = request.form.get('year')
            applicable = request.form.get('applicable')
        except Exception as e:
            data = {}
            data['status'] = 0
            data['message'] = message
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)
        #print("shjh")
        input = pd.ExcelFile('Backend//Data_1.xlsx')
        node1 = pd.read_excel(input,sheet_name="A.1 Warehouse")
        node2 = pd.read_excel(input,sheet_name="A.2 FPS")
        node1["concatenate"]= node1['WH_Lat'].astype(str) + ',' + node1['WH_Long'].astype(str)
        node2 = pd.read_excel(input,sheet_name="A.2 FPS")
        node2["concatenate1"]= node2['FPS_Lat'].astype(str) + ',' + node2['FPS_Long'].astype(str)
        Distance = pd.ExcelFile('Backend//Distance_Initial_L2.xlsx')
        DistanceBing = pd.read_excel(Distance,sheet_name="BG_BG")
        Warehouse = pd.read_excel(Distance,sheet_name="Warehouse")
        FPS = pd.read_excel(Distance,sheet_name="FPS")
        node1 = node1[['WH_ID', 'WH_Lat', 'WH_Long','concatenate']]
        War = pd.merge(node1, Warehouse, on='WH_ID')
        df1_w = War[War['concatenate'] != War['Lat_Long']]
        Warehouse_ID = df1_w['WH_ID'].unique()
        node2 = node2[['FPS_ID', 'FPS_Lat', 'FPS_Long','concatenate1']]
        FPS1 = pd.merge(node2, FPS, on='FPS_ID')
        df1_f = FPS1[FPS1['concatenate1'] != FPS1['Lat_Long']]
        FPS_ID = df1_f['FPS_ID'].unique()
        BG_BG = pd.read_excel(Distance,sheet_name="BG_BG")
        Distance1 = BG_BG.drop(columns=BG_BG.columns[BG_BG.columns.isin(Warehouse_ID)])
        Distance2 =Distance1.T
        Distance3 = Distance2.drop(columns=Distance2.columns[Distance2.columns.isin(FPS_ID)])
        Distance3 = Distance3.T
        with pd.ExcelWriter('Backend//Goa_Distance_L21.xlsx') as writer:
            Distance3.to_excel(writer, sheet_name='BG_BG',index=False)
        Warehouse_ID_series = pd.Series(Warehouse_ID)
        unique_in_df1 = node1[~node1['WH_ID'].isin(Warehouse['WH_ID'])]['WH_ID']
        combined_unique_series = pd.concat([unique_in_df1, Warehouse_ID_series])
        unique_combined = combined_unique_series.unique()
        #print(unique_combined )

        FPS_ID_series = pd.Series(FPS_ID)
        unique_in_df2 = node2[~node2['FPS_ID'].isin(FPS['FPS_ID'])]['FPS_ID']
        combined_unique_series_FPS = pd.concat([unique_in_df2, FPS_ID_series])
        unique_combined_FPS = combined_unique_series_FPS.unique()
        #print(unique_combined_FPS  )

        Warehouse_New = node1[node1['WH_ID'].isin(unique_combined)][['WH_ID', 'WH_Lat', 'WH_Long', 'concatenate']]
        with pd.ExcelWriter('Backend//Template_War_FPS.xlsx') as writer:
            Warehouse_New.to_excel(writer, sheet_name='Warehouse',index=False)
            node2.to_excel(writer, sheet_name='FPS',index=False)

        FPS_New = node2[node2['FPS_ID'].isin(unique_combined_FPS)][['FPS_ID', 'FPS_Lat', 'FPS_Long', 'concatenate1']]
        with pd.ExcelWriter('Backend//Template_FPS_Warehouse.xlsx') as writer:
             node1.to_excel(writer, sheet_name='Warehouse',index=False)
             FPS_New.to_excel(writer, sheet_name='FPS',index=False)   

        Distance = pd.ExcelFile("Backend//Goa_Distance_L21.xlsx")#Input Parameter sheet
        distance_new=pd.read_excel(Distance,'BG_BG')
        
        # USN=pd.ExcelFile("Backend//Template_War_FPS.xlsx")
        
        # df1 = pd.read_excel(USN,'Warehouse')#Input FCI_Sheet
        # df4 = pd.read_excel(USN,'FPS')#Input FPS_Sheet
        # source3 = df1['WH_ID']    #FCI is source and FPS is destination
        # destination3 = df4['FPS_ID']
        # dist3 = [[0 for a in range(len(destination3))] for b in range(len(source3))] #Transport matrix for FCI_FPS
        # BingMapsKey = "AhqrM7qhDZ_CmsQOjjzfikLVludo4uzr39wdDLXY10jV2u4I5PYOssgCACaWery8" #Bing Map Key

        # #Distance Calculation for FCI_FPS
        # if df1.empty or df4.empty:
        #    distance_new.to_excel("Backend//Distance_Matrix.xlsx",sheet_name='BG_BG',index=False)
        # else:  
        #     for i in df1.index:
        #         for j in df4.index:
        #             origin=df1.loc[i]["concatenate"]
        #             dest=df4.loc[j]["concatenate1"]
        #             response=requests.get("https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="+ origin + "&destinations="+ dest  +"&travelMode=driving&key=" + BingMapsKey)
        #             resp=response.json()
        #             #print(resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance'])
        #             dist3[i][j]=resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance']
                
        #     dist3=np.transpose(dist3)
        #     dist4=np.transpose(dist3)
        #     df6 = pd.DataFrame (dist4, columns = df4['FPS_ID'], index = df1['WH_ID'])
        #     df6.transpose().to_excel("Backend//Result_9.xlsx",sheet_name='IG_FPS')
        #     file1 = pd.ExcelFile("Backend//Goa_Distance_L21.xlsx")
        #     file2 = pd.ExcelFile("Backend//Result_9.xlsx")
        #     df1 = file1.parse('BG_BG')  # Assuming the data is in 'Sheet1'
        #     df2 = file2.parse('IG_FPS')
        #     df1.set_index('FPS_ID', inplace=True)
        #     df2.set_index('FPS_ID', inplace=True)
        #     result = df1.add(df2, fill_value=0)
        #     result.reset_index(inplace=True)
        #     with pd.ExcelWriter('Backend//Goa_Distance_New.xlsx') as writer:
        #          result.to_excel(writer, sheet_name='BG_BG',index=False)
        #     USN = pd.ExcelFile("Backend//Goa_Distance_New.xlsx")  # Input Parameter sheet
        #     result = pd.read_excel(USN, 'BG_BG')  # Input FCI_Sheet
        #     result.set_index('FPS_ID', inplace=True)
        #     result2 = result.drop(index=unique_combined_FPS)
        #     result2.reset_index(inplace=True)
        #     result2.to_excel("Backend//Distance_Matrix.xlsx", sheet_name='BG_BG', index=False)

#-------------------------------------------------------------------------------------------------------
        def get_token():
            auth_url = 'https://kerala.pmgatishakti.gov.in/PMGatishaktiApiService/authenticate'
            
            auth_payload = {
                "username": "DFPD_C",
                "password": "W9Vtb8WKkt3"
            }
            response = requests.post(auth_url, json=auth_payload)
            if response.status_code == 200:
                return response.json()['token']
            else:
                raise Exception(f"Error getting token: {response.status_code} - {response.text}")

           
        USN = pd.ExcelFile("Backend//Template_War_FPS.xlsx")
        df1 = pd.read_excel(USN, 'Warehouse') 
        df4 = pd.read_excel(USN, 'FPS')  

        source3 = df1['WH_ID']  
        destination3 = df4['FPS_ID']
        dist3 = np.zeros((len(source3), len(destination3)))  
        BingMapsKey = "AhDBAf8zi_2llbdYS0n4GKYX6Z5L-hl89QX1V0VWTfXhPVH5jk9tg2wehciKelun"  

        def calculate_distance_function():                         
            USN=pd.ExcelFile("Backend//Template_War_FPS.xlsx")
        
            df1 = pd.read_excel(USN,'Warehouse')#Input FCI_Sheet
            df4 = pd.read_excel(USN,'FPS')#Input FPS_Sheet
            source3 = df1['WH_ID']    #FCI is source and FPS is destination
            destination3 = df4['FPS_ID']
            dist3 = [[0 for a in range(len(destination3))] for b in range(len(source3))] #Transport matrix for FCI_FPS
            BingMapsKey = "AhqrM7qhDZ_CmsQOjjzfikLVludo4uzr39wdDLXY10jV2u4I5PYOssgCACaWery8" #Bing Map Key

            #Distance Calculation for FCI_FPS
            if df1.empty or df4.empty:
               distance_new.to_excel("Backend//Distance_Matrix.xlsx",sheet_name='BG_BG',index=False)
            else:  
                for i in df1.index:
                    for j in df4.index:
                        origin=df1.loc[i]["concatenate"]
                        dest=df4.loc[j]["concatenate1"]
                        response=requests.get("https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="+ origin + "&destinations="+ dest  +"&travelMode=driving&key=" + BingMapsKey)
                        resp=response.json()
                        #print(resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance'])
                        dist3[i][j]=resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance']
                    
                dist3=np.transpose(dist3)
                dist4=np.transpose(dist3)
                df6 = pd.DataFrame (dist4, columns = df4['FPS_ID'], index = df1['WH_ID'])
                df6.transpose().to_excel("Backend//Result_9.xlsx",sheet_name='IG_FPS')
                file1 = pd.ExcelFile("Backend//Goa_Distance_L21.xlsx")
                file2 = pd.ExcelFile("Backend//Result_9.xlsx")
                df1 = file1.parse('BG_BG')  # Assuming the data is in 'Sheet1'
                df2 = file2.parse('IG_FPS')
                df1.set_index('FPS_ID', inplace=True)
                df2.set_index('FPS_ID', inplace=True)
                result = df1.add(df2, fill_value=0)
                result.reset_index(inplace=True)
                with pd.ExcelWriter('Backend//Goa_Distance_New.xlsx') as writer:
                     result.to_excel(writer, sheet_name='BG_BG',index=False)
                USN = pd.ExcelFile("Backend//Goa_Distance_New.xlsx")  # Input Parameter sheet
                result = pd.read_excel(USN, 'BG_BG')  # Input FCI_Sheet
                result.set_index('FPS_ID', inplace=True)
                result2 = result.drop(index=unique_combined_FPS)
                result2.reset_index(inplace=True)
                result2.to_excel("Backend//Distance_Matrix.xlsx", sheet_name='BG_BG', index=False)
            return True  

        if not calculate_distance_function():
            token = get_token() 
            gatishakti_url = "https://nctdelhi.pmgatishakti.gov.in/PMGatishaktiApiService/dfpdapi/roaddistance"

            for i in df1.index:
                for j in df4.index:
                    origin = df1.loc[i]["concatenate"]
                    dest = df4.loc[j]["concatenate1"]
                    headers = {
                        'Authorization': f'Bearer {token}'
                    }
                    payload = {
                        "origin": origin,
                        "destination": dest
                    }
                    response = requests.post(gatishakti_url, json=payload, headers=headers)
                    if response.status_code == 200:
                        travel_distance = response.json().get('distance', 0)  # Adjust based on actual response structure
                        dist3[i][j] = travel_distance
                        
            df6 = pd.DataFrame(dist3, columns=df4['FPS_ID'], index=df1['WH_ID'])

            df6.transpose().to_excel("Backend//Result_9.xlsx",sheet_name='IG_FPS')
            file1 = pd.ExcelFile("Backend//Goa_Distance_L21.xlsx")
            file2 = pd.ExcelFile("Backend//Result_9.xlsx")
            df1 = file1.parse('BG_BG')  # Assuming the data is in 'Sheet1'
            df2 = file2.parse('IG_FPS')
            df1.set_index('FPS_ID', inplace=True)
            df2.set_index('FPS_ID', inplace=True)
            result = df1.add(df2, fill_value=0)
            result.reset_index(inplace=True)
            with pd.ExcelWriter('Backend//Goa_Distance_New.xlsx') as writer:
                    result.to_excel(writer, sheet_name='BG_BG',index=False)
            USN = pd.ExcelFile("Backend//Goa_Distance_New.xlsx")  # Input Parameter sheet
            result = pd.read_excel(USN, 'BG_BG')  # Input FCI_Sheet
            result.set_index('FPS_ID', inplace=True)
            result2 = result.drop(index=unique_combined_FPS)
            result2.reset_index(inplace=True)
            result2.to_excel("Backend//Distance_Matrix.xlsx", sheet_name='BG_BG', index=False)
#--------------------------------------------------------------------------------------------------
        
        Distance1 = pd.ExcelFile("Backend//Distance_Matrix.xlsx")#Input Parameter sheet
        distance_new1=pd.read_excel(Distance1,'BG_BG')

        # USN = pd.ExcelFile("Backend//Template_FPS_Warehouse.xlsx")#Input Parameter sheet
        # df1 = pd.read_excel(USN,'Warehouse')#Input FCI_Sheet
        # df4 = pd.read_excel(USN,'FPS')#Input FPS_Sheet
        # source3 = df1['WH_ID']    #FCI is source and FPS is destination
        # destination3 = df4['FPS_ID']
        # dist3 = [[0 for a in range(len(destination3))] for b in range(len(source3))] #Transport matrix for FCI_FPS
        # BingMapsKey = "AhqrM7qhDZ_CmsQOjjzfikLVludo4uzr39wdDLXY10jV2u4I5PYOssgCACaWery8" #Bing Map Key

        # #Distance Calculation for FCI_FPS
        # if df1.empty or df4.empty:
        #    distance_new1.to_excel("Backend//concatenated_result.xlsx",sheet_name='IG_FPS',index=False,)
        # else:
        #     for i in df1.index:
        #         for j in df4.index:
        #             origin=df1.loc[i]["concatenate"]
        #             dest=df4.loc[j]["concatenate1"]
        #             response=requests.get("https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="+ origin + "&destinations="+ dest  +"&travelMode=driving&key=" + BingMapsKey)
        #             resp=response.json()
        #             #print(resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance'])
        #             dist3[i][j]=resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance']
                
        #     dist3=np.transpose(dist3)
        #     dist4=np.transpose(dist3)
        #     df6 = pd.DataFrame (dist4, columns = df4['FPS_ID'], index = df1['WH_ID'])
        #     df6.transpose().to_excel("Backend//Result_91.xlsx",sheet_name='IG_FPS')

        #     file1 = pd.ExcelFile("Backend//Distance_Matrix.xlsx")
        #     file2 = pd.ExcelFile("Backend//Result_91.xlsx")
        #     df1 = file1.parse('BG_BG')  # Assuming the data is in 'Sheet1'
        #     df2 = file2.parse('IG_FPS')
        #     df1.set_index('FPS_ID', inplace=True)
        #     df2.set_index('FPS_ID', inplace=True)
        #     result = pd.concat([df1, df2], axis=0)
        #     # Reset the index to avoid duplicate index values
        #     result.reset_index(inplace=True)
        #     # Save the resulting DataFrame to an Excel file
        #     result.to_excel('Backend//concatenated_result.xlsx', index=False,sheet_name='IG_FPS')

#-------------------------------------------------------------------------------------------------------
        def get_token():
            auth_url = 'https://kerala.pmgatishakti.gov.in/PMGatishaktiApiService/authenticate'
        
            auth_payload = {
                "username": "DFPD_C",
                "password": "W9Vtb8WKkt3"
            }
            response = requests.post(auth_url, json=auth_payload)
            if response.status_code == 200:
                return response.json()['token']
            else:
                raise Exception(f"Error getting token: {response.status_code} - {response.text}")

        USN = pd.ExcelFile("Backend//Template_FPS_Warehouse.xlsx")
        df1 = pd.read_excel(USN, 'Warehouse') 
        df4 = pd.read_excel(USN, 'FPS')  

        source3 = df1['WH_ID']  
        destination3 = df4['FPS_ID']
        dist3 = np.zeros((len(source3), len(destination3)))  
        BingMapsKey = "AhDBAf8zi_2llbdYS0n4GKYX6Z5L-hl89QX1V0VWTfXhPVH5jk9tg2wehciKelun"  

        def calculate_distance_function():                 
            USN = pd.ExcelFile("Backend//Template_FPS_Warehouse.xlsx")

            df1 = pd.read_excel(USN,'Warehouse')
            df4 = pd.read_excel(USN,'FPS')
            source3 = df1['WH_ID'] 
            destination3 = df4['FPS_ID']
            dist3 = [[0 for a in range(len(destination3))] for b in range(len(source3))] #Transport matrix for FCI_FPS
            BingMapsKey = "AhqrM7qhDZ_CmsQOjjzfikLVludo4uzr39wdDLXY10jV2u4I5PYOssgCACaWery8" #Bing Map Key

            if df1.empty or df4.empty:
               distance_new1.to_excel("Backend//concatenated_result.xlsx",sheet_name='IG_FPS',index=False,)
            else:
                for i in df1.index:
                    for j in df4.index:
                        origin=df1.loc[i]["concatenate"]
                        dest=df4.loc[j]["concatenate1"]
                        response=requests.get("https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="+ origin + "&destinations="+ dest  +"&travelMode=driving&key=" + BingMapsKey)
                        resp=response.json()
                        #print(resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance'])
                        dist3[i][j]=resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance']
                    
                dist3=np.transpose(dist3)
                dist4=np.transpose(dist3)
                df6 = pd.DataFrame (dist4, columns = df4['FPS_ID'], index = df1['WH_ID'])
                df6.transpose().to_excel("Backend//Result_91.xlsx",sheet_name='IG_FPS')

                file1 = pd.ExcelFile("Backend//Distance_Matrix.xlsx")
                file2 = pd.ExcelFile("Backend//Result_91.xlsx")
                df1 = file1.parse('BG_BG')  # Assuming the data is in 'Sheet1'
                df2 = file2.parse('IG_FPS')
                df1.set_index('FPS_ID', inplace=True)
                df2.set_index('FPS_ID', inplace=True)
                result = pd.concat([df1, df2], axis=0)
                # Reset the index to avoid duplicate index values
                result.reset_index(inplace=True)
                # Save the resulting DataFrame to an Excel file
                result.to_excel('Backend//concatenated_result.xlsx', index=False,sheet_name='IG_FPS')
            return True  

        if not calculate_distance_function():
            token = get_token() 
            gatishakti_url = "https://nctdelhi.pmgatishakti.gov.in/PMGatishaktiApiService/dfpdapi/roaddistance"

            for i in df1.index:
                for j in df4.index:
                    origin = df1.loc[i]["concatenate"]
                    dest = df4.loc[j]["concatenate1"]
                    headers = {
                        'Authorization': f'Bearer {token}'
                    }
                    payload = {
                        "origin": origin,
                        "destination": dest
                    }
                    response = requests.post(gatishakti_url, json=payload, headers=headers)
                    if response.status_code == 200:
                        travel_distance = response.json().get('distance', 0)  # Adjust based on actual response structure
                        dist3[i][j] = travel_distance
                        
            df6 = pd.DataFrame(dist3, columns=df4['FPS_ID'], index=df1['WH_ID'])

            df6.transpose().to_excel("Backend//Result_9.xlsx",sheet_name='IG_FPS')
            file1 = pd.ExcelFile("Backend//Distance_Matrix.xlsx")
            file2 = pd.ExcelFile("Backend//Result_91.xlsx")
            df1 = file1.parse('BG_BG')  
            df2 = file2.parse('IG_FPS')
            df1.set_index('FPS_ID', inplace=True)
            df2.set_index('FPS_ID', inplace=True)
            result = pd.concat([df1, df2], axis=0)
            result.reset_index(inplace=True)
            result.to_excel('Backend//concatenated_result.xlsx', index=False,sheet_name='IG_FPS')
#--------------------------------------------------------------------------------------------------

        input = pd.ExcelFile('Backend//Data_1.xlsx')
        node1 = pd.read_excel(input,sheet_name="A.1 Warehouse")
        node2 = pd.read_excel(input,sheet_name="A.2 FPS")
        wh_ids = node1['WH_ID'].unique()
        fps_ids = node2['FPS_ID'].unique()
        matrix = pd.DataFrame(0, index=fps_ids, columns=wh_ids)
        output_file = 'Backend//matrix_output.xlsx'
        matrix.to_excel(output_file, index=True)
        dataframe1 = pd.read_excel(output_file)

        columnsList = dataframe1.columns
        columnsList = columnsList[1:]
        rowsList = dataframe1[dataframe1.columns[0]]
        Cost = pd.ExcelFile("Backend//concatenated_result.xlsx")
        PC_Mill = pd.read_excel(Cost,sheet_name="IG_FPS")
        distanceRows = PC_Mill[PC_Mill.columns[0]]

        counter = 0

        result = []
        result.append(dataframe1.columns)

        for i in rowsList:
            temp = []
            temp.append(i)
            for j in columnsList:
                counter = 0
                for k in distanceRows:
                    if k==i:
                        try:
                            temp.append(PC_Mill.at[counter, j])
                        except:
                            #print(counter)
                            print(j)
                        break
                    counter = counter + 1
            result.append(temp)
        result_transposed = list(map(list, zip(*result)))


        import xlsxwriter

        workbook = xlsxwriter.Workbook('Backend//Distance_Matrix_Final.xlsx')
        worksheet = workbook.add_worksheet()

        row = 0

        for col, data in enumerate(result_transposed):
            worksheet.write_column(row, col, data)

        workbook.close()

        file_path = 'Backend//Distance_Matrix_Final.xlsx'
        excel_file = pd.ExcelFile(file_path)

        # Load the specific sheet into a DataFrame
        df = excel_file.parse('Sheet1')

        # Rename the column
        df = df.rename(columns={'Unnamed: 0': 'FPS_ID'})

        # Save the DataFrame back to an Excel file
        with pd.ExcelWriter(file_path, engine='openpyxl') as writer:
             df.to_excel(writer, sheet_name='BG_BG', index=False)
        
        input = pd.ExcelFile('Backend//Data_1.xlsx')
        node3 = pd.read_excel(input,sheet_name="A.3 Mill")
        node2 = pd.read_excel(input,sheet_name="A.2 FPS")
        node3["concatenate"]= node3['WH_Lat'].astype(str) + ',' + node3['WH_Long'].astype(str)
        node2 = pd.read_excel(input,sheet_name="A.2 FPS")
        node2["concatenate1"]= node2['FPS_Lat'].astype(str) + ',' + node2['FPS_Long'].astype(str)
        Distance = pd.ExcelFile('Backend//Distance_Initial_L21.xlsx')
        DistanceBing = pd.read_excel(Distance,sheet_name="BG_BG")
        Warehouse = pd.read_excel(Distance,sheet_name="Warehouse")
        FPS = pd.read_excel(Distance,sheet_name="FPS")
        node3 = node3[['WH_ID', 'WH_Lat', 'WH_Long','concatenate']]
        War = pd.merge(node3, Warehouse, on='WH_ID')
        df1_w = War[War['concatenate'] != War['Lat_Long']]
        Warehouse_ID = df1_w['WH_ID'].unique()
        node2 = node2[['FPS_ID', 'FPS_Lat', 'FPS_Long','concatenate1']]
        FPS1 = pd.merge(node2, FPS, on='FPS_ID')
        df1_f = FPS1[FPS1['concatenate1'] != FPS1['Lat_Long']]
        FPS_ID = df1_f['FPS_ID'].unique()
        BG_BG = pd.read_excel(Distance,sheet_name="BG_BG")
        Distance1 = BG_BG.drop(columns=BG_BG.columns[BG_BG.columns.isin(Warehouse_ID)])
        Distance2 =Distance1.T
        Distance3 = Distance2.drop(columns=Distance2.columns[Distance2.columns.isin(FPS_ID)])
        Distance3 = Distance3.T
        with pd.ExcelWriter('Backend//Goa_Distance_L211.xlsx') as writer:
            Distance3.to_excel(writer, sheet_name='BG_BG',index=False)
        Warehouse_ID_series = pd.Series(Warehouse_ID)
        unique_in_df1 = node3[~node3['WH_ID'].isin(Warehouse['WH_ID'])]['WH_ID']
        combined_unique_series = pd.concat([unique_in_df1, Warehouse_ID_series])
        unique_combined = combined_unique_series.unique()
        #print(unique_combined )

        FPS_ID_series = pd.Series(FPS_ID)
        unique_in_df2 = node2[~node2['FPS_ID'].isin(FPS['FPS_ID'])]['FPS_ID']
        combined_unique_series_FPS = pd.concat([unique_in_df2, FPS_ID_series])
        unique_combined_FPS = combined_unique_series_FPS.unique()
        #print(unique_combined_FPS  )

        Warehouse_New = node3[node3['WH_ID'].isin(unique_combined)][['WH_ID', 'WH_Lat', 'WH_Long', 'concatenate']]
        with pd.ExcelWriter('Backend//Template_War_FPS1.xlsx') as writer:
            Warehouse_New.to_excel(writer, sheet_name='Warehouse',index=False)
            node2.to_excel(writer, sheet_name='FPS',index=False)

        FPS_New = node2[node2['FPS_ID'].isin(unique_combined_FPS)][['FPS_ID', 'FPS_Lat', 'FPS_Long', 'concatenate1']]
        with pd.ExcelWriter('Backend//Template_FPS_Warehouse1.xlsx') as writer:
             node3.to_excel(writer, sheet_name='Warehouse',index=False)
             FPS_New.to_excel(writer, sheet_name='FPS',index=False)

        Distance = pd.ExcelFile("Backend//Goa_Distance_L211.xlsx")#Input Parameter sheet
        distance_new=pd.read_excel(Distance,'BG_BG')

        # USN=pd.ExcelFile("Backend//Template_War_FPS1.xlsx")

        # df1 = pd.read_excel(USN,'Warehouse')#Input FCI_Sheet
        # df4 = pd.read_excel(USN,'FPS')#Input FPS_Sheet
        # source3 = df1['WH_ID']    #FCI is source and FPS is destination
        # destination3 = df4['FPS_ID']
        # dist3 = [[0 for a in range(len(destination3))] for b in range(len(source3))] #Transport matrix for FCI_FPS
        # BingMapsKey = "AhqrM7qhDZ_CmsQOjjzfikLVludo4uzr39wdDLXY10jV2u4I5PYOssgCACaWery8" #Bing Map Key

        # #Distance Calculation for FCI_FPS
        # if df1.empty or df4.empty:
        #    distance_new.to_excel("Backend//Distance_Matrix1.xlsx",sheet_name='BG_BG',index=False)
        # else:  
        #     for i in df1.index:
        #         for j in df4.index:
        #             origin=df1.loc[i]["concatenate"]
        #             dest=df4.loc[j]["concatenate1"]
        #             response=requests.get("https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="+ origin + "&destinations="+ dest  +"&travelMode=driving&key=" + BingMapsKey)
        #             resp=response.json()
        #             #print(resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance'])
        #             dist3[i][j]=resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance']

        #     dist3=np.transpose(dist3)
        #     dist4=np.transpose(dist3)
        #     df6 = pd.DataFrame (dist4, columns = df4['FPS_ID'], index = df1['WH_ID'])
        #     df6.transpose().to_excel("Backend//Result_91.xlsx",sheet_name='IG_FPS')
        #     file1 = pd.ExcelFile("Backend//Goa_Distance_L211.xlsx")
        #     file2 = pd.ExcelFile("Backend//Result_91.xlsx")
        #     df1 = file1.parse('BG_BG')  # Assuming the data is in 'Sheet1'
        #     df2 = file2.parse('IG_FPS')
        #     df1.set_index('FPS_ID', inplace=True)
        #     df2.set_index('FPS_ID', inplace=True)
        #     result = df1.add(df2, fill_value=0)
        #     result.reset_index(inplace=True)
        #     with pd.ExcelWriter('Backend//Goa_Distance_New1.xlsx') as writer:
        #          result.to_excel(writer, sheet_name='BG_BG',index=False)
        #     USN = pd.ExcelFile("Backend//Goa_Distance_New1.xlsx")  # Input Parameter sheet
        #     result = pd.read_excel(USN, 'BG_BG')  # Input FCI_Sheet
        #     result.set_index('FPS_ID', inplace=True)
        #     result2 = result.drop(index=unique_combined_FPS)
        #     result2.reset_index(inplace=True)
        #     result2.to_excel("Backend//Distance_Matrix1.xlsx", sheet_name='BG_BG', index=False)

#-------------------------------------------------------------------------------------------------------
        def get_token():
            auth_url = 'https://kerala.pmgatishakti.gov.in/PMGatishaktiApiService/authenticate'
        
            auth_payload = {
                "username": "DFPD_C",
                "password": "W9Vtb8WKkt3"
            }
            response = requests.post(auth_url, json=auth_payload)
            if response.status_code == 200:
                return response.json()['token']
            else:
                raise Exception(f"Error getting token: {response.status_code} - {response.text}")

           
        USN=pd.ExcelFile("Backend//Template_War_FPS1.xlsx")
        df1 = pd.read_excel(USN, 'Warehouse') 
        df4 = pd.read_excel(USN, 'FPS')  

        source3 = df1['WH_ID']  
        destination3 = df4['FPS_ID']
        dist3 = np.zeros((len(source3), len(destination3)))  
        BingMapsKey = "AhDBAf8zi_2llbdYS0n4GKYX6Z5L-hl89QX1V0VWTfXhPVH5jk9tg2wehciKelun"  

        def calculate_distance_function():                 
            USN=pd.ExcelFile("Backend//Template_War_FPS1.xlsx")

            df1 = pd.read_excel(USN,'Warehouse')#Input FCI_Sheet
            df4 = pd.read_excel(USN,'FPS')#Input FPS_Sheet
            source3 = df1['WH_ID']    #FCI is source and FPS is destination
            destination3 = df4['FPS_ID']
            dist3 = [[0 for a in range(len(destination3))] for b in range(len(source3))] #Transport matrix for FCI_FPS
            BingMapsKey = "AhqrM7qhDZ_CmsQOjjzfikLVludo4uzr39wdDLXY10jV2u4I5PYOssgCACaWery8" #Bing Map Key

            #Distance Calculation for FCI_FPS
            if df1.empty or df4.empty:
               distance_new.to_excel("Backend//Distance_Matrix1.xlsx",sheet_name='BG_BG',index=False)
            else:  
                for i in df1.index:
                    for j in df4.index:
                        origin=df1.loc[i]["concatenate"]
                        dest=df4.loc[j]["concatenate1"]
                        response=requests.get("https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="+ origin + "&destinations="+ dest  +"&travelMode=driving&key=" + BingMapsKey)
                        resp=response.json()
                        #print(resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance'])
                        dist3[i][j]=resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance']

                dist3=np.transpose(dist3)
                dist4=np.transpose(dist3)
                df6 = pd.DataFrame (dist4, columns = df4['FPS_ID'], index = df1['WH_ID'])
                df6.transpose().to_excel("Backend//Result_91.xlsx",sheet_name='IG_FPS')
                file1 = pd.ExcelFile("Backend//Goa_Distance_L211.xlsx")
                file2 = pd.ExcelFile("Backend//Result_91.xlsx")
                df1 = file1.parse('BG_BG')  # Assuming the data is in 'Sheet1'
                df2 = file2.parse('IG_FPS')
                df1.set_index('FPS_ID', inplace=True)
                df2.set_index('FPS_ID', inplace=True)
                result = df1.add(df2, fill_value=0)
                result.reset_index(inplace=True)
                with pd.ExcelWriter('Backend//Goa_Distance_New1.xlsx') as writer:
                     result.to_excel(writer, sheet_name='BG_BG',index=False)
                USN = pd.ExcelFile("Backend//Goa_Distance_New1.xlsx")  # Input Parameter sheet
                result = pd.read_excel(USN, 'BG_BG')  # Input FCI_Sheet
                result.set_index('FPS_ID', inplace=True)
                result2 = result.drop(index=unique_combined_FPS)
                result2.reset_index(inplace=True)
                result2.to_excel("Backend//Distance_Matrix1.xlsx", sheet_name='BG_BG', index=False)
            return True  

        if not calculate_distance_function():
            token = get_token() 
            gatishakti_url = "https://nctdelhi.pmgatishakti.gov.in/PMGatishaktiApiService/dfpdapi/roaddistance"

            for i in df1.index:
                for j in df4.index:
                    origin = df1.loc[i]["concatenate"]
                    dest = df4.loc[j]["concatenate1"]
                    headers = {
                        'Authorization': f'Bearer {token}'
                    }
                    payload = {
                        "origin": origin,
                        "destination": dest
                    }
                    response = requests.post(gatishakti_url, json=payload, headers=headers)
                    if response.status_code == 200:
                        travel_distance = response.json().get('distance', 0)  # Adjust based on actual response structure
                        dist3[i][j] = travel_distance
                        
            df6 = pd.DataFrame(dist3, columns=df4['FPS_ID'], index=df1['WH_ID'])

            df6.transpose().to_excel("Backend//Result_9.xlsx",sheet_name='IG_FPS')
            file1 = pd.ExcelFile("Backend//Goa_Distance_L211.xlsx")
            file2 = pd.ExcelFile("Backend//Result_91.xlsx")
            df1 = file1.parse('BG_BG')  # Assuming the data is in 'Sheet1'
            df2 = file2.parse('IG_FPS')
            df1.set_index('FPS_ID', inplace=True)
            df2.set_index('FPS_ID', inplace=True)
            result = df1.add(df2, fill_value=0)
            result.reset_index(inplace=True)
            with pd.ExcelWriter('Backend//Goa_Distance_New1.xlsx') as writer:
                    result.to_excel(writer, sheet_name='BG_BG',index=False)
            USN = pd.ExcelFile("Backend//Goa_Distance_New1.xlsx")  # Input Parameter sheet
            result = pd.read_excel(USN, 'BG_BG')  # Input FCI_Sheet
            result.set_index('FPS_ID', inplace=True)
            result2 = result.drop(index=unique_combined_FPS)
            result2.reset_index(inplace=True)
            result2.to_excel("Backend//Distance_Matrix1.xlsx", sheet_name='BG_BG', index=False)
#--------------------------------------------------------------------------------------------------

        Distance1 = pd.ExcelFile("Backend//Distance_Matrix1.xlsx")#Input Parameter sheet
        distance_new1=pd.read_excel(Distance1,'BG_BG')

        # USN = pd.ExcelFile("Backend//Template_FPS_Warehouse1.xlsx")#Input Parameter sheet
        # df1 = pd.read_excel(USN,'Warehouse')#Input FCI_Sheet
        # df4 = pd.read_excel(USN,'FPS')#Input FPS_Sheet
        # source3 = df1['WH_ID']    #FCI is source and FPS is destination
        # destination3 = df4['FPS_ID']
        # dist3 = [[0 for a in range(len(destination3))] for b in range(len(source3))] #Transport matrix for FCI_FPS
        # BingMapsKey = "AhqrM7qhDZ_CmsQOjjzfikLVludo4uzr39wdDLXY10jV2u4I5PYOssgCACaWery8" #Bing Map Key

        # #Distance Calculation for FCI_FPS
        # if df1.empty or df4.empty:
        #    distance_new1.to_excel("Backend//concatenated_result1.xlsx",sheet_name='IG_FPS',index=False,)
        # else:
        #     for i in df1.index:
        #         for j in df4.index:
        #             origin=df1.loc[i]["concatenate"]
        #             dest=df4.loc[j]["concatenate1"]
        #             response=requests.get("https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="+ origin + "&destinations="+ dest  +"&travelMode=driving&key=" + BingMapsKey)
        #             resp=response.json()
        #             #print(resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance'])
        #             dist3[i][j]=resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance']

        #     dist3=np.transpose(dist3)
        #     dist4=np.transpose(dist3)
        #     df6 = pd.DataFrame (dist4, columns = df4['FPS_ID'], index = df1['WH_ID'])
        #     df6.transpose().to_excel("Backend//Result_91.xlsx",sheet_name='IG_FPS')

        #     file1 = pd.ExcelFile("Backend//Distance_Matrix1.xlsx")
        #     file2 = pd.ExcelFile("Backend//Result_91.xlsx")
        #     df1 = file1.parse('BG_BG')  # Assuming the data is in 'Sheet1'
        #     df2 = file2.parse('IG_FPS')
        #     df1.set_index('FPS_ID', inplace=True)
        #     df2.set_index('FPS_ID', inplace=True)
        #     result = pd.concat([df1, df2], axis=0)
        #     # Reset the index to avoid duplicate index values
        #     result.reset_index(inplace=True)
        #     # Save the resulting DataFrame to an Excel file
        #     result.to_excel('Backend//concatenated_result1.xlsx', index=False,sheet_name='IG_FPS')

#-------------------------------------------------------------------------------------------------------
        def get_token():
            auth_url = 'https://kerala.pmgatishakti.gov.in/PMGatishaktiApiService/authenticate'
        
            auth_payload = {
                "username": "DFPD_C",
                "password": "W9Vtb8WKkt3"
            }
            response = requests.post(auth_url, json=auth_payload)
            if response.status_code == 200:
                return response.json()['token']
            else:
                raise Exception(f"Error getting token: {response.status_code} - {response.text}")

           
        USN = pd.ExcelFile("Backend//Template_FPS_Warehouse1.xlsx")
        df1 = pd.read_excel(USN, 'Warehouse') 
        df4 = pd.read_excel(USN, 'FPS')  

        source3 = df1['WH_ID']  
        destination3 = df4['FPS_ID']
        dist3 = np.zeros((len(source3), len(destination3)))  
        BingMapsKey = "AhDBAf8zi_2llbdYS0n4GKYX6Z5L-hl89QX1V0VWTfXhPVH5jk9tg2wehciKelun"  

        def calculate_distance_function():                 
            USN = pd.ExcelFile("Backend//Template_FPS_Warehouse1.xlsx")#Input Parameter sheet

            df1 = pd.read_excel(USN,'Warehouse')#Input FCI_Sheet
            df4 = pd.read_excel(USN,'FPS')#Input FPS_Sheet
            source3 = df1['WH_ID']    #FCI is source and FPS is destination
            destination3 = df4['FPS_ID']
            dist3 = [[0 for a in range(len(destination3))] for b in range(len(source3))] #Transport matrix for FCI_FPS
            BingMapsKey = "AhqrM7qhDZ_CmsQOjjzfikLVludo4uzr39wdDLXY10jV2u4I5PYOssgCACaWery8" #Bing Map Key

            #Distance Calculation for FCI_FPS
            if df1.empty or df4.empty:
               distance_new1.to_excel("Backend//concatenated_result1.xlsx",sheet_name='IG_FPS',index=False,)
            else:
                for i in df1.index:
                    for j in df4.index:
                        origin=df1.loc[i]["concatenate"]
                        dest=df4.loc[j]["concatenate1"]
                        response=requests.get("https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="+ origin + "&destinations="+ dest  +"&travelMode=driving&key=" + BingMapsKey)
                        resp=response.json()
                        #print(resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance'])
                        dist3[i][j]=resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance']

                dist3=np.transpose(dist3)
                dist4=np.transpose(dist3)
                df6 = pd.DataFrame (dist4, columns = df4['FPS_ID'], index = df1['WH_ID'])
                df6.transpose().to_excel("Backend//Result_91.xlsx",sheet_name='IG_FPS')

                file1 = pd.ExcelFile("Backend//Distance_Matrix1.xlsx")
                file2 = pd.ExcelFile("Backend//Result_91.xlsx")
                df1 = file1.parse('BG_BG')  # Assuming the data is in 'Sheet1'
                df2 = file2.parse('IG_FPS')
                df1.set_index('FPS_ID', inplace=True)
                df2.set_index('FPS_ID', inplace=True)
                result = pd.concat([df1, df2], axis=0)
                # Reset the index to avoid duplicate index values
                result.reset_index(inplace=True)
                # Save the resulting DataFrame to an Excel file
                result.to_excel('Backend//concatenated_result1.xlsx', index=False,sheet_name='IG_FPS')
            return True  

        if not calculate_distance_function():
            token = get_token() 
            gatishakti_url = "https://nctdelhi.pmgatishakti.gov.in/PMGatishaktiApiService/dfpdapi/roaddistance"

            for i in df1.index:
                for j in df4.index:
                    origin = df1.loc[i]["concatenate"]
                    dest = df4.loc[j]["concatenate1"]
                    headers = {
                        'Authorization': f'Bearer {token}'
                    }
                    payload = {
                        "origin": origin,
                        "destination": dest
                    }
                    response = requests.post(gatishakti_url, json=payload, headers=headers)
                    if response.status_code == 200:
                        travel_distance = response.json().get('distance', 0)  # Adjust based on actual response structure
                        dist3[i][j] = travel_distance
                        
            df6 = pd.DataFrame(dist3, columns=df4['FPS_ID'], index=df1['WH_ID'])

            df6.transpose().to_excel("Backend//Result_9.xlsx",sheet_name='IG_FPS')
            file1 = pd.ExcelFile("Backend//Distance_Matrix1.xlsx")
            file2 = pd.ExcelFile("Backend//Result_91.xlsx")
            df1 = file1.parse('BG_BG')  # Assuming the data is in 'Sheet1'
            df2 = file2.parse('IG_FPS')
            df1.set_index('FPS_ID', inplace=True)
            df2.set_index('FPS_ID', inplace=True)
            result = pd.concat([df1, df2], axis=0)
            # Reset the index to avoid duplicate index values
            result.reset_index(inplace=True)
            # Save the resulting DataFrame to an Excel file
            result.to_excel('Backend//concatenated_result1.xlsx', index=False,sheet_name='IG_FPS')
#--------------------------------------------------------------------------------------------------

        input = pd.ExcelFile('Backend//Data_1.xlsx')
        node3 = pd.read_excel(input,sheet_name="A.3 Mill")
        node2 = pd.read_excel(input,sheet_name="A.2 FPS")
        wh_ids = node3['WH_ID'].unique()
        fps_ids = node2['FPS_ID'].unique()
        matrix = pd.DataFrame(0, index=fps_ids, columns=wh_ids)
        output_file = 'Backend//matrix_output1.xlsx'
        matrix.to_excel(output_file, index=True)
        dataframe1 = pd.read_excel(output_file)

        columnsList = dataframe1.columns
        columnsList = columnsList[1:]
        rowsList = dataframe1[dataframe1.columns[0]]
        Cost = pd.ExcelFile("Backend//concatenated_result1.xlsx")
        PC_Mill = pd.read_excel(Cost,sheet_name="IG_FPS")
        distanceRows = PC_Mill[PC_Mill.columns[0]]

        counter = 0

        result = []
        result.append(dataframe1.columns)

        for i in rowsList:
            temp = []
            temp.append(i)
            for j in columnsList:
                counter = 0
                for k in distanceRows:
                    if k==i:
                        try:
                            temp.append(PC_Mill.at[counter, j])
                        except:
                            #print(counter)
                            print(j)
                        break
                    counter = counter + 1
            result.append(temp)
        result_transposed = list(map(list, zip(*result)))


        import xlsxwriter

        workbook = xlsxwriter.Workbook('Backend//Distance_Matrix_Final1.xlsx')
        worksheet = workbook.add_worksheet()

        row = 0

        for col, data in enumerate(result_transposed):
            worksheet.write_column(row, col, data)

        workbook.close()

        file_path = 'Backend//Distance_Matrix_Final1.xlsx'
        excel_file = pd.ExcelFile(file_path)

        # Load the specific sheet into a DataFrame
        df = excel_file.parse('Sheet1')

        # Rename the column
        df = df.rename(columns={'Unnamed: 0': 'FPS_ID'})

        # Save the DataFrame back to an Excel file
        with pd.ExcelWriter(file_path, engine='openpyxl') as writer:
             df.to_excel(writer, sheet_name='BG_IG', index=False)

        
        
        file_path = 'Backend/Distance_Matrix_Final.xlsx'
        file_path1 = 'Backend/Distance_Matrix_Final1.xlsx'

        # Read the two sheets into DataFrames
        df1 = pd.read_excel(file_path, sheet_name='BG_BG')
        df2 = pd.read_excel(file_path1, sheet_name='BG_IG')

        result = pd.merge(df1, df2, on='FPS_ID')

        # File path to save the resulting DataFrame
        output_path = 'Backend/Merged_Result.xlsx'

        # Save the resulting DataFrame to a new Excel file
        result.to_excel(output_path, sheet_name='BG_BG', index=False)
                
        USN = pd.ExcelFile('Backend//Data_1.xlsx')
        FCI = pd.read_excel(USN, sheet_name='A.1 Warehouse', index_col=None)
        FPS = pd.read_excel(USN, sheet_name='A.2 FPS', index_col=None)
        Mill = pd.read_excel(USN, sheet_name='A.3 Mill', index_col=None)

        FCI['WH_District'] = FCI['WH_District'].apply(lambda x: x.replace(' ', ''))
        FPS['FPS_District'] = FPS['FPS_District'].apply(lambda x: x.replace(' ', ''))
        Mill['WH_District'] = Mill['WH_District'].apply(lambda x: x.replace(' ', ''))
        
        
        model = LpProblem('Supply-Demand-Problem', LpMinimize)

        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)

        Variable1 = []
        for i in range(len(Mill['WH_ID'])):
            for j in range(len(FPS['FPS_ID'])):
                Variable1.append(str(Mill['WH_ID'][i]) + '_'
                                 + str(Mill['WH_District'][i]) + '_'
                                 + str(FPS['FPS_ID'][j]) + '_'
                                 + str(FPS['FPS_District'][j]) + '_Atta')
                                 
        

        # Variables for Wheat from lEVEL2 TO FPS

        DV_Variables1 = LpVariable.matrix('X', Variable1, cat='float',
                lowBound=0)
        Allocation1 = np.array(DV_Variables1).reshape(len(Mill['WH_ID']),
                len(FPS['FPS_ID']))
        
        
        
        
        Variable2 = []
        for i in range(len(FCI['WH_ID'])):
            for j in range(len(FPS['FPS_ID'])):
                Variable2.append(str(FCI['WH_ID'][i]) + '_'
                                 + str(FCI['WH_District'][i]) + '_'
                                 + str(FPS['FPS_ID'][j]) + '_'
                                 + str(FPS['FPS_District'][j]) + '_Rice')
                                 
        

        # Variables for Wheat from lEVEL2 TO FPS

        DV_Variables2 = LpVariable.matrix('X', Variable2, cat='float',
                lowBound=0)
        Allocation2 = np.array(DV_Variables2).reshape(len(FCI['WH_ID']),
                len(FPS['FPS_ID']))
                
        Variable3 = []
        for i in range(len(FCI['WH_ID'])):
            for j in range(len(FPS['FPS_ID'])):
                Variable3.append(str(FCI['WH_ID'][i]) + '_'
                                 + str(FCI['WH_District'][i]) + '_'
                                 + str(FPS['FPS_ID'][j]) + '_'
                                 + str(FPS['FPS_District'][j]) + '_FRice')
                                 
        

        # Variables for Wheat from lEVEL2 TO FPS

        DV_Variables3 = LpVariable.matrix('X', Variable3, cat='float',
                lowBound=0)
        Allocation3 = np.array(DV_Variables3).reshape(len(FCI['WH_ID']),
                len(FPS['FPS_ID']))
        
        

        Variable1I = []
        Allocation1I = []
        for i in range(len(FCI['WH_ID'])):
            for j in range(len(FPS['FPS_ID'])):
                Variable1I.append(str(FCI['WH_ID'][i]) + '_'
                                  + str(FCI['WH_District'][i]) + '_'
                                  + str(FPS['FPS_ID'][j]) + '_'
                                  + str(FPS['FPS_District'][j]) + '_Wheat1')

    #    Variables for Wheat from IG TO FPS

        DV_Variables1I = LpVariable.matrix('X', Variable1I, cat='Binary',lowBound=0)
        Allocation1I = np.array(DV_Variables1I).reshape(len(FCI['WH_ID']),len(FPS['FPS_ID']))

        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)

        for i in range(len(FPS['FPS_ID'])):
             model += lpSum(Allocation1I[k][i] for k in range(len(FCI['WH_ID']))) <= 1

        for i in range(len(FCI['WH_ID'])):
             for j in range(len(FPS['FPS_ID'])):
                model += Allocation2[i][j] <= 1000000 * Allocation1I[i][j]
                
        for i in range(len(FCI['WH_ID'])):
             for j in range(len(FPS['FPS_ID'])):
                model += Allocation3[i][j] <= 1000000 * Allocation1I[i][j]
                
        Variable2I = []
        Allocation2I = []
        for i in range(len(Mill['WH_ID'])):
            for j in range(len(FPS['FPS_ID'])):
                Variable2I.append(str(Mill['WH_ID'][i]) + '_'
                                  + str(Mill['WH_District'][i]) + '_'
                                  + str(FPS['FPS_ID'][j]) + '_'
                                  + str(FPS['FPS_District'][j]) + '_Wheat1')

    #    Variables for Wheat from IG TO FPS

        DV_Variables2I = LpVariable.matrix('X', Variable2I, cat='Binary',lowBound=0)
        Allocation2I = np.array(DV_Variables2I).reshape(len(Mill['WH_ID']),len(FPS['FPS_ID']))
        
        
        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)

        for i in range(len(FPS['FPS_ID'])):
             model += lpSum(Allocation2I[k][i] for k in range(len(Mill['WH_ID']))) <= 1

        for i in range(len(Mill['WH_ID'])):
             for j in range(len(FPS['FPS_ID'])):
                model += Allocation1[i][j] <= 1000000 * Allocation2I[i][j]

        
        
        
        District_Capacity = {}
        for i in range(len(FCI["WH_District"])):
            District_Name = FCI["WH_District"][i]
            if District_Name not in District_Capacity:
                District_Capacity[District_Name] = int(FCI["Storage_Capacity"][i])
            else:
                District_Capacity[District_Name] += int(FCI["Storage_Capacity"][i])
 
        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)

        District_Demand = {}
        for i in range(len(FPS["FPS_District"])):
            District_Name_FPS = FPS["FPS_District"][i]
            if District_Name_FPS not in District_Demand:
                District_Demand[District_Name_FPS] = float(FPS["Allocation_Rice"][i])  + float(FPS["Allocation_FRice"][i])
            else:
                District_Demand[District_Name_FPS] += float(FPS["Allocation_Rice"][i])+ float(FPS["Allocation_FRice"][i])
                
        

        
        District_Name = []
        District_Name2=[]
        District_Name = [i for i in District_Demand if i not in District_Capacity]
        District_Name4 = [i for i in District_Capacity if i not in District_Demand]
        District_Name2 = [i for i in District_Demand if i in District_Capacity and District_Demand[i] >= District_Capacity[i]]
        District_Name_1 = {}
        District_Name_1['District_Name_All'] = District_Name + District_Name2
        District_Name3 = [i for i in District_Demand if i in District_Capacity and District_Demand[i] <= District_Capacity[i]]
        
        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)  
            
        name1 = []
        lst1 = []
        for j in range(len(DV_Variables2)):
            name1 = str(DV_Variables2[j])
            lst1 = name1.split("_")
            if lst1[2] in District_Name3 and lst1[4] in District_Name3 and lst1[2]!=lst1[4]:
                model+=DV_Variables2[j]==0
                #print(DV_Variables1[j]==0)
                
        name2 = []
        lst2 = []
        for j in range(len(DV_Variables2)):
            name2 = str(DV_Variables2[j])
            lst2 = name2.split("_")
            if lst2[2] in District_Name2 and lst2[4] in District_Name3:
                model+=DV_Variables2[j]==0
                #print(DV_Variables1[j]==0)
                
        name3 = []
        lst3 = []
        for j in range(len(DV_Variables2)):
            name3 = str(DV_Variables2[j])
            lst3 = name3.split("_")
            if lst3[2] in District_Name2 and lst3[4] in District_Name2 and lst3[2]!=lst3[4]:
                model+=DV_Variables2[j]==0
                #print(DV_Variables1[j]==0)

        name4 = []
        lst4 = []
        for j in range(len(DV_Variables2)):
            name4 = str(DV_Variables2[j])
            lst4 = name4.split("_")
            if lst4[2] in District_Name4 and lst4[4] in District_Name3:
                model+=DV_Variables2[j]==0
                #print(DV_Variables1[j]==0)
        
        name5 = []
        lst5 = []
        for j in range(len(DV_Variables3)):
            name5 = str(DV_Variables3[j])
            lst5 = name5.split("_")
            if lst5[2] in District_Name3 and lst5[4] in District_Name3 and lst5[2]!=lst5[4]:
                model+=DV_Variables3[j]==0
                #print(DV_Variables1[j]==0)
                
        name6 = []
        lst6 = []
        for j in range(len(DV_Variables3)):
            name6 = str(DV_Variables3[j])
            lst6 = name6.split("_")
            if lst6[2] in District_Name2 and lst6[4] in District_Name3:
                model+=DV_Variables3[j]==0
                #print(DV_Variables1[j]==0)
                
        name7 = []
        lst7 = []
        for j in range(len(DV_Variables3)):
            name7 = str(DV_Variables3[j])
            lst7 = name7.split("_")
            if lst7[2] in District_Name2 and lst7[4] in District_Name2 and lst7[2]!=lst7[4]:
                model+=DV_Variables3[j]==0
                #print(DV_Variables1[j]==0)

        name8 = []
        lst8 = []
        for j in range(len(DV_Variables3)):
            name8 = str(DV_Variables3[j])
            lst8= name8.split("_")
            if lst8[2] in District_Name4 and lst8[4] in District_Name3:
                model+=DV_Variables3[j]==0
                #print(DV_Variables1[j]==0)
                
        
        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)
            
            
        District_Mill = {}
        for i in range(len(Mill["WH_District"])):
            District_Name = Mill["WH_District"][i]
            if District_Name not in District_Mill:
                District_Mill[District_Name] = int(Mill["Processing"][i])
            else:
                District_Mill[District_Name] += int(Mill["Processing"][i])
 
        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)

        District_Demand_Wheat = {}
        for i in range(len(FPS["FPS_District"])):
            District_Name_FPS = FPS["FPS_District"][i]
            if District_Name_FPS not in District_Demand_Wheat:
                District_Demand_Wheat[District_Name_FPS] = float(FPS["Allocation_Wheat"][i])  
            else:
                District_Demand_Wheat[District_Name_FPS] += float(FPS["Allocation_Wheat"][i])
                
        

        
        District_Name_wheat = []
        District_Name2_wheat=[]
        District_Name_wheat = [i for i in District_Demand_Wheat if i not in District_Mill]
        District_Name4_wheat = [i for i in District_Mill if i not in District_Demand_Wheat]
        District_Name2_wheat = [i for i in District_Demand_Wheat if i in District_Mill and District_Demand_Wheat[i] >= District_Mill[i]]
        District_Name_1_wheat = {}
        District_Name_1_wheat['District_Name_All'] = District_Name_wheat + District_Name2_wheat
        District_Name3_wheat = [i for i in District_Demand_Wheat if i in District_Mill and District_Demand_Wheat[i] <= District_Mill[i]]
        
        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)  
            
        name9 = []
        lst9 = []
        for j in range(len(DV_Variables1)):
            name9 = str(DV_Variables1[j])
            lst9 = name9.split("_")
            if lst9[2] in District_Name3_wheat and lst9[4] in District_Name3_wheat and lst9[2]!=lst9[4]:
                model+=DV_Variables1[j]==0
                #print(DV_Variables1[j]==0)
                
        name10 = []
        lst10 = []
        for j in range(len(DV_Variables1)):
            name10 = str(DV_Variables1[j])
            lst10 = name10.split("_")
            if lst10[2] in District_Name4_wheat and lst10[4] in District_Name3_wheat:
                model+=DV_Variables1[j]==0
                #print(DV_Variables1[j]==0)
                
        name11 = []
        lst11 = []
        for j in range(len(DV_Variables1)):
            name11 = str(DV_Variables1[j])
            lst11 = name11.split("_")
            if lst11[2] in District_Name2_wheat and lst11[4] in District_Name2_wheat and lst11[2]!=lst11[4]:
                model+=DV_Variables1[j]==0
                #print(DV_Variables1[j]==0)

        name12 = []
        lst12 = []
        for j in range(len(DV_Variables1)):
            name12 = str(DV_Variables1[j])
            lst12 = name12.split("_")
            if lst12[2] in District_Name4_wheat and lst12[4] in District_Name3_wheat:
                model+=DV_Variables1[j]==0
            
        WKB = excelrd.open_workbook('Backend//Distance_Matrix_Final.xlsx')
        Sheet1 = WKB.sheet_by_index(0)
        WKB = excelrd.open_workbook('Backend//Distance_Matrix_Final1.xlsx')
        Sheet2 = WKB.sheet_by_index(0)
        
        PC_Mill = []
        for col in range(Sheet1.nrows):
            if col==0:
                continue
            temp = []
            for row in range (Sheet1.ncols):
                if row==0:
                    continue
                temp.append(Sheet1.cell_value(col,row))
            PC_Mill.append(temp)

        FCI_FPS = [[ PC_Mill[j][i] for j in range(len( PC_Mill))] for i in range(len( PC_Mill[0]))]
        
        PC_Mill1 = []
        for col in range(Sheet2.nrows):
            if col==0:
                continue
            temp = []
            for row in range (Sheet2.ncols):
                if row==0:
                    continue
                temp.append(Sheet2.cell_value(col,row))
            PC_Mill1.append(temp)

        FCI_FPS1 = [[ PC_Mill1[j][i] for j in range(len( PC_Mill1))] for i in range(len( PC_Mill1[0]))]
        
        
        

        allCombination1 = []
        allCombination2 = []
        allCombination3 = []
        


        for i in range(len(FCI_FPS)):
            for j in range(len(FPS['FPS_ID'])):
                allCombination2.append(Allocation2[i][j] * FCI_FPS[i][j])
       
        for i in range(len(FCI_FPS)):
            for j in range(len(FPS['FPS_ID'])):
                allCombination3.append(Allocation3[i][j] * FCI_FPS[i][j])
                       
        for i in range(len(FCI_FPS1)):
            for j in range(len(FPS['FPS_ID'])):
                allCombination1.append(Allocation1[i][j] * FCI_FPS1[i][j])
                
        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)

        model += lpSum(allCombination1 + allCombination2 + allCombination3)

        # Demand Constraints for Wheat
        
        

        for i in range(len(FPS['FPS_ID'])):
            model += ((lpSum(Allocation1[j][i] for j in range(len(Mill['WH_ID'
                           ])))) >= FPS['Allocation_Wheat'][i])
                           
        for i in range(len(FPS['FPS_ID'])):
            model += ((lpSum(Allocation2[j][i] for j in range(len(FCI['WH_ID'
                           ])))) >= FPS['Allocation_Rice'][i])
                           
        for i in range(len(FPS['FPS_ID'])):
            model += ((lpSum(Allocation3[j][i] for j in range(len(FCI['WH_ID'
                           ])))) >= FPS['Allocation_FRice'][i])
                           
                           
        for i in range(len(FPS['FPS_ID'])):
            model += ((lpSum(Allocation1[j][i] for j in range(len(Mill['WH_ID'
                           ])))) <= FPS['Allocation_Wheat'][i])
                           
        for i in range(len(FPS['FPS_ID'])):
            model += ((lpSum(Allocation2[j][i] for j in range(len(FCI['WH_ID'
                           ])))) <= FPS['Allocation_Rice'][i])
                           
        for i in range(len(FPS['FPS_ID'])):
            model += ((lpSum(Allocation3[j][i] for j in range(len(FCI['WH_ID'
                           ])))) <= FPS['Allocation_FRice'][i])

        # Supply Constraints for Warehouses

        for i in range(len(FCI['WH_ID'])):
            model += (((lpSum(Allocation2[i][j] for j in range(len(FPS['FPS_ID'
                           ]))))  + lpSum(Allocation3[i][j] for j in range(len(FPS['FPS_ID'
                           ])))) <= FCI['Storage_Capacity'][i])
                           
        for i in range(len(Mill['WH_ID'])):
            model += ((lpSum(Allocation1[i][j] for j in range(len(FPS['FPS_ID'
                           ]))))   <= Mill['Processing'][i])

       # Calling CBC_CMB Solver

        model.solve(CPLEX_CMD(options=['set mip tolerances mipgap 0.01']))
        #model.prob.solve(CPLEX_CMD(options=["set mip tolerances mipgap 0.03","set emphasis memory y"]))
        #model.solve(CPLEX_CMD(options=['set mip tolerances mipgap 0.03',"set emphasis memory y"]))
        #model.solve(PULP_CBC_CMD())
        
        status = LpStatus[model.status]
        if status == LpStatusInfeasible or status == LpStatusUnbounded or status == LpStatusNotSolved or status == LpStatusUndefined:
           print("Problem is infeasible or unbounded.")
           data = {}
           data['status'] = 0
           data['message'] = "Infeasible or Unbounded Solution"
           json_data = json.dumps(data)
           json_object = json.loads(json_data)
           return json.dumps(json_object, indent=1)
 
        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)

        #model.solve(PULP_CBC_CMD())
        
        
        

        data = {}

        Output_File = open('Backend//Inter_District1.csv', 'w')
        for v in model.variables():
            if v.value() > 0:
                Output_File.write(v.name + '\t' + str(v.value()) + '\n')

        Output_File = open('Backend//Inter_District1.csv', 'w')
        for v in model.variables():
            if v.value() > 0:
                Output_File.write(v.name + '\t' + str(v.value()) + '\n')


        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)

        df9 = pd.read_csv('Backend//Inter_District1.csv',header=None)
        df9.columns = ['Tagging']
        df9[[
            'Var',
            'WH_ID',
            'W_D',
            'FPS_ID',
            'FPS_D',
            'commodity_Value',
            ]] = df9[df9.columns[0]].str.split('_', n=6, expand=True)
        del df9[df9.columns[0]]
        df9[['commodity', 'Values']] = df9['commodity_Value'
                ].str.split('\\t', n=1, expand=True)
        del df9['commodity_Value']
        df9 = df9.drop(np.where(df9['commodity'] == 'Wheat1')[0])
        
        def convert_to_numeric(value):
            try:
                return pd.to_numeric(value)
            except ValueError:
                return value
        
        
        df9['WH_ID'] = df9['WH_ID'].apply(convert_to_numeric)
        df9['FPS_ID'] = df9['FPS_ID'].apply(convert_to_numeric)
        
        df9.to_excel('Backend//Tagging_Sheet_Pre.xlsx', sheet_name='BG_FPS')
        df31 = pd.read_excel('Backend//Tagging_Sheet_Pre.xlsx')
        
        
        USN = pd.ExcelFile('Backend//Data_1.xlsx')
        Depot = pd.read_excel(USN, sheet_name='A.1 Warehouse', index_col=None)
        FPS = pd.read_excel(USN, sheet_name='A.2 FPS', index_col=None)
        
        Mill = pd.read_excel(USN, sheet_name='A.3 Mill', index_col=None)
        
        columns_to_include = ["WH_District","WH_Name","WH_ID",	"Type",	"WH_Lat",	"WH_Long"]
        df1_selected = Depot[columns_to_include]
        df2_selected = Mill[columns_to_include]
        
        FCI = pd.concat([df1_selected, df2_selected], ignore_index=True)
        
        
        df4 = pd.merge(df31, FCI, on='WH_ID', how='inner')
        #df4 = pd.merge(df31, FCI, on='WH_ID', how='inner')
        df4 = df4[[
            'WH_ID',
            'Type',
            'WH_Name',
            'WH_District',
            'WH_Lat',
            'WH_Long',
            'FPS_ID',
            'Values',
            'commodity',

            ]]
        df4 = pd.merge(df4, FPS, on='FPS_ID', how='inner')
        df51 = df4[[
            'WH_ID',
            'Type',
            'WH_Name',
            'WH_District',
            'WH_Lat',
            'WH_Long',
            'FPS_ID',
            'FPS_Name',
            'FPS_District',
            'FPS_Lat',
            'FPS_Long',
            'Values',
            'commodity',

            
            ]]
        df51.insert(0, 'Scenario', 'Optimized')
        df51.insert(2, 'From_State', 'Ladakh')
        df51.insert(7, 'To', 'FPS')
        df51.insert(8, 'To_State', 'Ladakh')
  
        df51.rename(columns={
            'WH_ID': 'From_ID',
            'WH_Name': 'From_Name',
            'WH_Lat': 'From_Lat',
            'WH_Long': 'From_Long',
            }, inplace=True)
        df51.rename(columns={
            'FPS_ID': 'To_ID',
            'FPS_Name': 'To_Name',
            'FPS_Lat': 'To_Lat',
            'FPS_Long': 'To_Long',
            'Values': 'quantity',
            
            }, inplace=True)
        df51.rename(columns={'WH_District': 'From_District',
                              'Type':'From',
                   'FPS_District': 'To_District'}, inplace=True)
        df51 = df51.loc[:, [
            'Scenario',
            'From',
            'From_State',
            'From_District',
            'From_ID',
            'From_Name',
            'From_Lat',
            'From_Long',
            'To',
            'To_ID',
            'To_Name',
            'To_State',
            'To_District',
            'To_Lat',
            'To_Long',
            'commodity',
            'quantity',
            ]]
        
        
                
        def convert_to_numeric(value):
            try:
                return pd.to_numeric(value)
            except ValueError:
                return value
                
        df_combined = pd.concat([df51])
        df_combined1 = df_combined[df_combined['quantity'] != 0]
        df_combined1['From_ID'] = df_combined1['From_ID'].apply(convert_to_numeric)
        df_combined1['To_ID'] = df_combined1['To_ID'].apply(convert_to_numeric)

        df_combined1.to_excel('Backend//Tagging_Sheet_Pre11.xlsx', sheet_name='BG_FPS1')
        
        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)  
        
        data1 = pd.ExcelFile("Backend//Tagging_Sheet_Pre11.xlsx")
        df5 = pd.read_excel(data1,sheet_name="BG_FPS1")

        Cost = pd.ExcelFile("Backend//Merged_Result.xlsx")
        BG_BG = pd.read_excel(Cost,sheet_name="BG_BG")
        
        Distance_BG_BG = {}
        column_list_BG_BG = list(BG_BG.columns.astype(str))
        row_list_BG_BG = list(BG_BG.iloc[:, 0].astype(str))

        for ind in df5.index:
            from_code = df5['From_ID'][ind]
            to_code = df5['To_ID'][ind]
            from_code_str = str(from_code)
            to_code_str = str(to_code)
            
            if to_code_str in row_list_BG_BG and from_code_str in column_list_BG_BG:
                index_i = row_list_BG_BG.index(to_code_str)
                index_j = column_list_BG_BG.index(from_code_str)
                key = to_code_str + "_" + from_code_str
                Distance_BG_BG[key] = BG_BG.iloc[index_i, index_j] 

                
        df5["Tagging"] = df5['To_ID'].astype(str) + '_' + df5['From_ID'].astype(str)
        df5['Distance'] = df5['Tagging'].map(Distance_BG_BG)
        df5.fillna('shallu', inplace=True)
        df5.to_excel('Backend//Result_Sheet12.xlsx', sheet_name='Warehouse_FPS', index=False)        

        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)            
                         
        # Result_Sheet1=pd.ExcelFile("Backend//Result_Sheet12.xlsx")
        # df6= pd.read_excel(Result_Sheet1,sheet_name="Warehouse_FPS")
        # df7=df6.loc[df6['Distance'] == "shallu"]
        # source3 = df7['From_ID']  # FCI is the source and FPS is the destination
        # destination3 = df7['To_ID']
        # BingMapsKey = "ApBZRxLaI7CNkPkE1E9mrJCh3l02nRLXax2m0s5ajUIAMdwuJWLN7oMWVtCHQYzr"  # Bing Map Key
        # df7["Warehouse_lat_long"]= df7['From_Lat'].astype(str) + ',' + df7['From_Long'].astype(str)
        # df7["FPS_lat_long"]= df7['To_Lat'].astype(str) + ',' + df7['To_Long'].astype(str)

        # #df8=df7["From_ID","To_ID","Warehouse_lat_long","FPS_lat_long"]
        # df8 = df7[['From_ID', 'To_ID', 'Warehouse_lat_long', 'FPS_lat_long']]
        # source3 = df8['From_ID']
        # destination3 = df8['To_ID']
        # dist3 = [0 for _ in range(len(destination3))]  # Transport matrix for FCI_FPS
        # BingMapsKey = "ApBZRxLaI7CNkPkE1E9mrJCh3l02nRLXax2m0s5ajUIAMdwuJWLN7oMWVtCHQYzr"  # Bing Map Key

        # dist3 = []  # Initialize an empty list for distances

        # for index, row in df8.iterrows():
        #     origin = row["Warehouse_lat_long"]
        #     dest = row["FPS_lat_long"]
        #     max_retries = 3
        #     retries = 0
        #     while retries < max_retries:
        #         try:
        #             response = requests.get(
        #                 "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins=" + origin + "&destinations=" + dest +
        #                 "&travelMode=driving&key=" + BingMapsKey)
        #             resp = response.json()

        #             # Append a new element to dist3 for the current index
        #             dist3.append(resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance'])

        #             # Display the output for each iteration
        #             #print(f"Origin: {origin}, Destination: {dest}, Distance: {dist3[-1]}")
        #             break  # Successful response, exit the retry loop
        #         except (requests.ConnectionError, requests.Timeout):
        #             retries += 1
        #             #print(f"Attempt {retries} failed. Retrying...")
        #             time.sleep(1)  # Wait for 1 second before retrying

        # #print("Final distances:", dist3)

        # if stop_process==True:
        #     data = {}
        #     data['status'] = 0
        #     data['message'] = "Process Stopped"
        #     json_data = json.dumps(data)
        #     json_object = json.loads(json_data)
        #     return json.dumps(json_object, indent=1)

        # df7["Distance"]=dist3
        # df7.drop(['Warehouse_lat_long', 'FPS_lat_long'], axis=1)
        # df9=df6.loc[df6['Distance'] != "shallu"]
        # df9 = df9.loc[:, [
        #         'Scenario',
        #         'From',
        #         'From_State',
        #         'From_District',
        #         'From_ID',
        #         'From_Name',
        #         'From_Lat',
        #         'From_Long',
        #         'To',
        #         'To_ID',
        #         'To_Name',
        #         'To_State',
        #         'To_District',
        #         'To_Lat',
        #         'To_Long',
        #         'commodity',
        #         'quantity',
        #          "Distance",]]
        # df7 = df7.loc[:, [
        #         'Scenario',
        #         'From',
        #         'From_State',
        #         'From_District',
        #         'From_ID',
        #         'From_Name',
        #         'From_Lat',
        #         'From_Long',
        #         'To',
        #         'To_ID',
        #         'To_Name',
        #         'To_State',
        #         'To_District',
        #         'To_Lat',
        #         'To_Long',
        #         'commodity',
        #         'quantity',
        #        "Distance"]]
        
        
        # df10 = pd.concat([df9, df7], ignore_index=True)
        
        # df10.to_excel('Backend//Result_Sheet.xlsx',
        #              sheet_name='Warehouse_FPS')

# ----------------------------------------------------------------------------------------------------------------------------------------------
        Result_Sheet1 = pd.ExcelFile("Backend//Result_Sheet12.xlsx")
        df6 = pd.read_excel(Result_Sheet1, sheet_name="Warehouse_FPS")
        Result_Sheet1.close()

        df7 = df6.loc[df6['Distance'] == "shallu"]

        auth_url = 'https://kerala.pmgatishakti.gov.in/PMGatishaktiApiService/authenticate'
        
        auth_payload = {
            "username": "DFPD_C",
            "password": "W9Vtb8WKkt3"
        }

        FILE_PATH = 'distanceIndent.json'

        def get_token():
            response = requests.post(auth_url, json=auth_payload)
            if response.status_code == 200:
                token = response.json()['token']
                if token : 
                    return token
                else: 
                    return False
            else:
                return False

        response_data = []


        def process_batch(df_batch):
            token = get_token()    
            time.sleep(15)
            headers = {
                'Authorization': f'Bearer {token}',
            }

            data = {
                "parameter": [{
                    "src_lng": row["From_Long"],
                    "src_lat": row["From_Lat"],
                    "dest_lng": row["To_Long"],
                    "dest_lat": row["To_Lat"]
                } for _, row in df_batch.iterrows()]
            }
            
            with open(FILE_PATH, 'w') as f:
                json.dump(data, f, indent=4)

            with open(FILE_PATH, 'rb') as f:
                files = {'LatsLongsFile': f}

                response = requests.post(distance_url, headers=headers, files=files)
                
                return response

        def process_single(row):
            token = get_token()
            time.sleep(2)
            headers = {
                'Authorization': f'Bearer {token}',
            }

            data = {
                "parameter": [{
                    "src_lng": row["From_Long"],
                    "src_lat": row["From_Lat"],
                    "dest_lng": row["To_Long"],
                    "dest_lat": row["To_Lat"]
                }]
            }
            
            with open(FILE_PATH, 'w') as f:
                json.dump(data, f, indent=4)

            with open(FILE_PATH, 'rb') as f:
                files = {'LatsLongsFile': f}

                response = requests.post(distance_url, headers=headers, files=files)
                return response
                    
        batch_size = 30
        total_rows = len(df7)
        num_batches = (total_rows + batch_size - 1) // batch_size
                    
        dist3 = []

        for batch_num in range(num_batches):
            start_idx = batch_num * batch_size
            end_idx = min((batch_num + 1) * batch_size, total_rows)
            df_batch = df7.iloc[start_idx:end_idx]
            
            response = process_batch(df_batch)
            if response.status_code == 200:
                response_json = response.json()
                if 'data' in response_json and all('distance' in row_data for row_data in response_json['data']):
                    for row_data, (_, row) in zip(response_json['data'], df_batch.iterrows()):
                        distance = row_data['distance']
                        dist3.append(distance)
                else : 
                    source3 = df_batch['From_ID']  
                    destination3 = df_batch['To_ID']
                    BingMapsKey = "AirsAsdRojcMdt_sHviub_uETiraB-on6DR3Q_AVyrOCedF0FkITbpVVSDKx6hjT"  # Bing Map Key
                    df_batch["Warehouse_lat_long"]= df7['From_Lat'].astype(str) + ',' + df7['From_Long'].astype(str)
                    df_batch["FPS_lat_long"]= df7['To_Lat'].astype(str) + ',' + df7['To_Long'].astype(str)

                    df8 = df_batch[['From_ID', 'To_ID', 'Warehouse_lat_long', 'FPS_lat_long']]
                    source3 = df8['From_ID']
                    destination3 = df8['To_ID']

                    for index, row in df8.iterrows():
                        origin = row["Warehouse_lat_long"]
                        dest = row["FPS_lat_long"]
                        max_retries = 3
                        retries = 0
                        while retries < max_retries:
                            try:
                                response = requests.get(
                                    "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins=" + origin + "&destinations=" + dest +
                                    "&travelMode=driving&key=" + BingMapsKey)
                                resp = response.json()
                                dist3.append(resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance'])
                                break  
                            except (requests.ConnectionError, requests.Timeout):
                                retries += 1
                                time.sleep(1) 
            else: 
                source3 = df_batch['From_ID']  
                destination3 = df_batch['To_ID']
                BingMapsKey = "AirsAsdRojcMdt_sHviub_uETiraB-on6DR3Q_AVyrOCedF0FkITbpVVSDKx6hjT"  # Bing Map Key
                df_batch["Warehouse_lat_long"]= df7['From_Lat'].astype(str) + ',' + df7['From_Long'].astype(str)
                df_batch["FPS_lat_long"]= df7['To_Lat'].astype(str) + ',' + df7['To_Long'].astype(str)

                df8 = df_batch[['From_ID', 'To_ID', 'Warehouse_lat_long', 'FPS_lat_long']]
                source3 = df8['From_ID']
                destination3 = df8['To_ID']

                for index, row in df8.iterrows():
                    origin = row["Warehouse_lat_long"]
                    dest = row["FPS_lat_long"]
                    max_retries = 3
                    retries = 0
                    while retries < max_retries:
                        try:
                            response = requests.get(
                                "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins=" + origin + "&destinations=" + dest +
                                "&travelMode=driving&key=" + BingMapsKey)
                            resp = response.json()

                            dist3.append(resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance'])
                            break 
                        except (requests.ConnectionError, requests.Timeout):
                            retries += 1
                            time.sleep(1)

        df7["Distance"]=dist3
        df9=df6.loc[df6['Distance'] != "shallu"]
        df9 = df9.loc[:, [
                'Scenario',
                'From',
                'From_State',
                'From_District',
                'From_ID',
                'From_Name',
                'From_Lat',
                'From_Long',
                'To',
                'To_ID',
                'To_Name',
                'To_State',
                'To_District',
                'To_Lat',
                'To_Long',
                'commodity',
                'quantity',
                'Distance']]
        df7 = df7.loc[:, [
                'Scenario',
                'From',
                'From_State',
                'From_District',
                'From_ID',
                'From_Name',
                'From_Lat',
                'From_Long',
                'To',
                'To_ID',
                'To_Name',
                'To_State',
                'To_District',
                'To_Lat',
                'To_Long',
                'commodity',
                'quantity',
                'Distance']]

        df10 = pd.concat([df9, df7], ignore_index=True)
        result = ((df10['quantity']) * df10['Distance']).sum()

        df10.to_excel('Backend//Result_Sheet.xlsx', sheet_name='Warehouse_FPS', index=False)
# ---------------------------------------------------------------------------------------------------------------------------------------------
                     
        Result_Sheet_Final=pd.ExcelFile("Backend//Result_Sheet.xlsx")
        sf= pd.read_excel(Result_Sheet_Final,sheet_name="Warehouse_FPS")
        sf1 = sf[sf['From'].str.lower() == 'mill']

        
        data["Scenario"]="Intra"
        data["Scenario_Baseline"] = "Baseline"
        
        data["From"]="Mill"
        data["From_Baseline"] = "Mill"
        
        data["To"]="FPS"
        data["To_Baseline"] = "FPS"
        
        data["WH_Used"] = sf1['From_ID'].nunique()
        data["WH_Used_Baseline"] = "2"
        
        data["FPS_Used"] = sf1['To_ID'].nunique()
        data["FPS_Used_Baseline"] = "384"
        
        data['Demand'] =sf1["quantity"].astype(float).sum()
        data['Demand_Baseline'] = "6,628"
        
        result = ((sf1['quantity']) * sf1['Distance']).sum()
        data['Total_QKM'] = float(result)
        data['Total_QKM_Baseline'] = "5,75,860"
        
        Total_Demand = sf1["quantity"].astype(float).sum()
        
        data['Average_Distance'] = float(round(result, 2)) / Total_Demand
        data['Average_Distance_Baseline'] = "86.88"
        
        
        sf2 = sf[sf['From'].str.lower() != 'mill']

        
        data["Scenario1"]="Intra"
        data["Scenario_Baseline1"] = "Baseline"
        
        data["From1"]="Depot"
        data["From_Baseline1"] = "Depot"
        
        data["To1"]="FPS"
        data["To_Baseline1"] = "FPS"
        
        data["WH_Used1"] = sf2['From_ID'].nunique()
        data["WH_Used_Baseline1"] = "2"
        
        data["FPS_Used1"] = sf2['To_ID'].nunique()
        data["FPS_Used_Baseline1"] = "384"
        
        data['Demand1'] =sf2["quantity"].astype(float).sum()
        data['Demand_Baseline1'] = "9,346"
        
        result1 = ((sf2['quantity']) * sf2['Distance']).sum()
        data['Total_QKM1'] = float(result1)
        data['Total_QKM_Baseline1'] = "7,05,086"
        
        Total_Demand1 = sf2["quantity"].astype(float).sum()
        
        data['Average_Distance1'] = float(round(result1, 2)) / Total_Demand1
        data['Average_Distance_Baseline1'] = "75.44"


        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)                     

        save_to_database(month, year, applicable)
        save_monthly_data(month, year, float(result))
        
        file_open = open('plantuml_file.txt', 'w')
        file_open.write('scale 600 width\n')
        file_open.write('scale 400 height\n')
        file_open.write('skinparam sequenceMessageAlign center\n')
        file_open.write('skinparam sequenceArrowThickness 3\n')
        file_open.write('skinparam backgroundColor #FFFFFF\n')
        file_open.write('hide footbox\n')
        file_open.write('title <font color=#000000 size=20> Allocation Movement \n')
        file_open.write('skinparam sequence{\n')
        file_open.write('ParticipantBorderColor none\n')
        file_open.write('ParticipantBackgroundColor #004699\n')
        file_open.write('ParticipantFontName calibri\n')
        file_open.write('ParticipantFontSize 15\n')
        file_open.write('ParticipantFontColor #ffffff\n')
        file_open.write('}\n')
        file_open.write('participant "FCI\\n<size:40><&globe>" as FCI order  1 \n')
        file_open.write('participant "FPS\\n<size:40><&vertical-align-top>" as FPS order 2 \n')
        file_open.write('participant "FCI\\n<size:40><&globe>" as FCI order  1 \n')
        file_open.write('participant "FPS\\n<size:40><&vertical-align-top>" as FPS order 2 \n')
        
        
        file_open.close()
        command = 'python -m plantuml plantuml_file.txt'
        subprocess.run(command, shell=True)

        json_data = json.dumps(data)
        json_object = json.loads(json_data)

        if os.path.exists('ouputPickle.pkl'):
            os.remove('ouputPickle.pkl')

        # open pickle file
        dbfile1 = open('ouputPickle.pkl', 'ab')
        
    else:
        message = 'DataFile file is incorrect'
        try:
            USN = pd.ExcelFile('Backend//Data_1.xlsx')
            month = request.form.get('month')        
            year = request.form.get('year')
            applicable = request.form.get('applicable')
        except Exception as e:
            data = {}
            data['status'] = 0
            data['message'] = message
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)
        #print("shjh")
        input = pd.ExcelFile('Backend//Data_1.xlsx')
        node1 = pd.read_excel(input,sheet_name="A.1 Warehouse")
        node2 = pd.read_excel(input,sheet_name="A.2 FPS")
        node1["concatenate"]= node1['WH_Lat'].astype(str) + ',' + node1['WH_Long'].astype(str)
        node2 = pd.read_excel(input,sheet_name="A.2 FPS")
        node2["concatenate1"]= node2['FPS_Lat'].astype(str) + ',' + node2['FPS_Long'].astype(str)
        Distance = pd.ExcelFile('Backend//Distance_Initial_L2.xlsx')
        DistanceBing = pd.read_excel(Distance,sheet_name="BG_BG")
        Warehouse = pd.read_excel(Distance,sheet_name="Warehouse")
        FPS = pd.read_excel(Distance,sheet_name="FPS")
        node1 = node1[['WH_ID', 'WH_Lat', 'WH_Long','concatenate']]
        War = pd.merge(node1, Warehouse, on='WH_ID')
        df1_w = War[War['concatenate'] != War['Lat_Long']]
        Warehouse_ID = df1_w['WH_ID'].unique()
        node2 = node2[['FPS_ID', 'FPS_Lat', 'FPS_Long','concatenate1']]
        FPS1 = pd.merge(node2, FPS, on='FPS_ID')
        df1_f = FPS1[FPS1['concatenate1'] != FPS1['Lat_Long']]
        FPS_ID = df1_f['FPS_ID'].unique()
        BG_BG = pd.read_excel(Distance,sheet_name="BG_BG")
        Distance1 = BG_BG.drop(columns=BG_BG.columns[BG_BG.columns.isin(Warehouse_ID)])
        Distance2 =Distance1.T
        Distance3 = Distance2.drop(columns=Distance2.columns[Distance2.columns.isin(FPS_ID)])
        Distance3 = Distance3.T
        with pd.ExcelWriter('Backend//Goa_Distance_L21.xlsx') as writer:
            Distance3.to_excel(writer, sheet_name='BG_BG',index=False)
        Warehouse_ID_series = pd.Series(Warehouse_ID)
        unique_in_df1 = node1[~node1['WH_ID'].isin(Warehouse['WH_ID'])]['WH_ID']
        combined_unique_series = pd.concat([unique_in_df1, Warehouse_ID_series])
        unique_combined = combined_unique_series.unique()
        #print(unique_combined )

        FPS_ID_series = pd.Series(FPS_ID)
        unique_in_df2 = node2[~node2['FPS_ID'].isin(FPS['FPS_ID'])]['FPS_ID']
        combined_unique_series_FPS = pd.concat([unique_in_df2, FPS_ID_series])
        unique_combined_FPS = combined_unique_series_FPS.unique()
        #print(unique_combined_FPS  )

        Warehouse_New = node1[node1['WH_ID'].isin(unique_combined)][['WH_ID', 'WH_Lat', 'WH_Long', 'concatenate']]
        with pd.ExcelWriter('Backend//Template_War_FPS.xlsx') as writer:
            Warehouse_New.to_excel(writer, sheet_name='Warehouse',index=False)
            node2.to_excel(writer, sheet_name='FPS',index=False)

        FPS_New = node2[node2['FPS_ID'].isin(unique_combined_FPS)][['FPS_ID', 'FPS_Lat', 'FPS_Long', 'concatenate1']]
        with pd.ExcelWriter('Backend//Template_FPS_Warehouse.xlsx') as writer:
             node1.to_excel(writer, sheet_name='Warehouse',index=False)
             FPS_New.to_excel(writer, sheet_name='FPS',index=False)

        

        Distance = pd.ExcelFile("Backend//Goa_Distance_L21.xlsx")#Input Parameter sheet
        distance_new=pd.read_excel(Distance,'BG_BG')
        
        # USN=pd.ExcelFile("Backend//Template_War_FPS.xlsx")
        
        # df1 = pd.read_excel(USN,'Warehouse')#Input FCI_Sheet
        # df4 = pd.read_excel(USN,'FPS')#Input FPS_Sheet
        # source3 = df1['WH_ID']    #FCI is source and FPS is destination
        # destination3 = df4['FPS_ID']
        # dist3 = [[0 for a in range(len(destination3))] for b in range(len(source3))] #Transport matrix for FCI_FPS
        # BingMapsKey = "AhqrM7qhDZ_CmsQOjjzfikLVludo4uzr39wdDLXY10jV2u4I5PYOssgCACaWery8" #Bing Map Key

        # #Distance Calculation for FCI_FPS
        # if df1.empty or df4.empty:
        #    distance_new.to_excel("Backend//Distance_Matrix.xlsx",sheet_name='BG_BG',index=False)
        # else:  
        #     for i in df1.index:
        #         for j in df4.index:
        #             origin=df1.loc[i]["concatenate"]
        #             dest=df4.loc[j]["concatenate1"]
        #             response=requests.get("https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="+ origin + "&destinations="+ dest  +"&travelMode=driving&key=" + BingMapsKey)
        #             resp=response.json()
        #             #print(resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance'])
        #             dist3[i][j]=resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance']
                
        #     dist3=np.transpose(dist3)
        #     dist4=np.transpose(dist3)
        #     df6 = pd.DataFrame (dist4, columns = df4['FPS_ID'], index = df1['WH_ID'])
        #     df6.transpose().to_excel("Backend//Result_9.xlsx",sheet_name='IG_FPS')
        #     file1 = pd.ExcelFile("Backend//Goa_Distance_L21.xlsx")
        #     file2 = pd.ExcelFile("Backend//Result_9.xlsx")
        #     df1 = file1.parse('BG_BG')  # Assuming the data is in 'Sheet1'
        #     df2 = file2.parse('IG_FPS')
        #     df1.set_index('FPS_ID', inplace=True)
        #     df2.set_index('FPS_ID', inplace=True)
        #     result = df1.add(df2, fill_value=0)
        #     result.reset_index(inplace=True)
        #     with pd.ExcelWriter('Backend//Goa_Distance_New.xlsx') as writer:
        #          result.to_excel(writer, sheet_name='BG_BG',index=False)
        #     USN = pd.ExcelFile("Backend//Goa_Distance_New.xlsx")  # Input Parameter sheet
        #     result = pd.read_excel(USN, 'BG_BG')  # Input FCI_Sheet
        #     result.set_index('FPS_ID', inplace=True)
        #     result2 = result.drop(index=unique_combined_FPS)
        #     result2.reset_index(inplace=True)
        #     result2.to_excel("Backend//Distance_Matrix.xlsx", sheet_name='BG_BG', index=False)

#-------------------------------------------------------------------------------------------------------
        def get_token():
            auth_url = 'https://kerala.pmgatishakti.gov.in/PMGatishaktiApiService/authenticate'
            auth_payload = {
                "username": "DFPD_C",
                "password": "W9Vtb8WKkt3"
            }
            response = requests.post(auth_url, json=auth_payload)
            if response.status_code == 200:
                return response.json()['token']
            else:
                raise Exception(f"Error getting token: {response.status_code} - {response.text}")

           
        USN=pd.ExcelFile("Backend//Template_War_FPS.xlsx")
        df1 = pd.read_excel(USN, 'Warehouse') 
        df4 = pd.read_excel(USN, 'FPS')  

        source3 = df1['WH_ID']  
        destination3 = df4['FPS_ID']
        dist3 = np.zeros((len(source3), len(destination3)))  
        BingMapsKey = "AhDBAf8zi_2llbdYS0n4GKYX6Z5L-hl89QX1V0VWTfXhPVH5jk9tg2wehciKelun"  

        def calculate_distance_function():                 
            USN=pd.ExcelFile("Backend//Template_War_FPS.xlsx")
        
            df1 = pd.read_excel(USN,'Warehouse')#Input FCI_Sheet
            df4 = pd.read_excel(USN,'FPS')#Input FPS_Sheet
            source3 = df1['WH_ID']    #FCI is source and FPS is destination
            destination3 = df4['FPS_ID']
            dist3 = [[0 for a in range(len(destination3))] for b in range(len(source3))] #Transport matrix for FCI_FPS
            BingMapsKey = "AhqrM7qhDZ_CmsQOjjzfikLVludo4uzr39wdDLXY10jV2u4I5PYOssgCACaWery8" #Bing Map Key

            #Distance Calculation for FCI_FPS
            if df1.empty or df4.empty:
               distance_new.to_excel("Backend//Distance_Matrix.xlsx",sheet_name='BG_BG',index=False)
            else:  
                for i in df1.index:
                    for j in df4.index:
                        origin=df1.loc[i]["concatenate"]
                        dest=df4.loc[j]["concatenate1"]
                        response=requests.get("https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="+ origin + "&destinations="+ dest  +"&travelMode=driving&key=" + BingMapsKey)
                        resp=response.json()
                        #print(resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance'])
                        dist3[i][j]=resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance']
                    
                dist3=np.transpose(dist3)
                dist4=np.transpose(dist3)
                df6 = pd.DataFrame (dist4, columns = df4['FPS_ID'], index = df1['WH_ID'])
                df6.transpose().to_excel("Backend//Result_9.xlsx",sheet_name='IG_FPS')
                file1 = pd.ExcelFile("Backend//Goa_Distance_L21.xlsx")
                file2 = pd.ExcelFile("Backend//Result_9.xlsx")
                df1 = file1.parse('BG_BG')  # Assuming the data is in 'Sheet1'
                df2 = file2.parse('IG_FPS')
                df1.set_index('FPS_ID', inplace=True)
                df2.set_index('FPS_ID', inplace=True)
                result = df1.add(df2, fill_value=0)
                result.reset_index(inplace=True)
                with pd.ExcelWriter('Backend//Goa_Distance_New.xlsx') as writer:
                     result.to_excel(writer, sheet_name='BG_BG',index=False)
                USN = pd.ExcelFile("Backend//Goa_Distance_New.xlsx")  # Input Parameter sheet
                result = pd.read_excel(USN, 'BG_BG')  # Input FCI_Sheet
                result.set_index('FPS_ID', inplace=True)
                result2 = result.drop(index=unique_combined_FPS)
                result2.reset_index(inplace=True)
                result2.to_excel("Backend//Distance_Matrix.xlsx", sheet_name='BG_BG', index=False)
            return True  

        if not calculate_distance_function():
            token = get_token() 
            gatishakti_url = "https://nctdelhi.pmgatishakti.gov.in/PMGatishaktiApiService/dfpdapi/roaddistance"

            for i in df1.index:
                for j in df4.index:
                    origin = df1.loc[i]["concatenate"]
                    dest = df4.loc[j]["concatenate1"]
                    headers = {
                        'Authorization': f'Bearer {token}'
                    }
                    payload = {
                        "origin": origin,
                        "destination": dest
                    }
                    response = requests.post(gatishakti_url, json=payload, headers=headers)
                    if response.status_code == 200:
                        travel_distance = response.json().get('distance', 0)  # Adjust based on actual response structure
                        dist3[i][j] = travel_distance
                        
            df6 = pd.DataFrame(dist3, columns=df4['FPS_ID'], index=df1['WH_ID'])

            df6.transpose().to_excel("Backend//Result_9.xlsx",sheet_name='IG_FPS')
            file1 = pd.ExcelFile("Backend//Goa_Distance_L21.xlsx")
            file2 = pd.ExcelFile("Backend//Result_9.xlsx")
            df1 = file1.parse('BG_BG')  # Assuming the data is in 'Sheet1'
            df2 = file2.parse('IG_FPS')
            df1.set_index('FPS_ID', inplace=True)
            df2.set_index('FPS_ID', inplace=True)
            result = df1.add(df2, fill_value=0)
            result.reset_index(inplace=True)
            with pd.ExcelWriter('Backend//Goa_Distance_New.xlsx') as writer:
                 result.to_excel(writer, sheet_name='BG_BG',index=False)
            USN = pd.ExcelFile("Backend//Goa_Distance_New.xlsx")  # Input Parameter sheet
            result = pd.read_excel(USN, 'BG_BG')  # Input FCI_Sheet
            result.set_index('FPS_ID', inplace=True)
            result2 = result.drop(index=unique_combined_FPS)
            result2.reset_index(inplace=True)
            result2.to_excel("Backend//Distance_Matrix.xlsx", sheet_name='BG_BG', index=False)
#--------------------------------------------------------------------------------------------------
        
        Distance1 = pd.ExcelFile("Backend//Distance_Matrix.xlsx")#Input Parameter sheet
        distance_new1=pd.read_excel(Distance1,'BG_BG')

        # USN = pd.ExcelFile("Backend//Template_FPS_Warehouse.xlsx")#Input Parameter sheet
        # df1 = pd.read_excel(USN,'Warehouse')#Input FCI_Sheet
        # df4 = pd.read_excel(USN,'FPS')#Input FPS_Sheet
        # source3 = df1['WH_ID']    #FCI is source and FPS is destination
        # destination3 = df4['FPS_ID']
        # dist3 = [[0 for a in range(len(destination3))] for b in range(len(source3))] #Transport matrix for FCI_FPS
        # BingMapsKey = "AhqrM7qhDZ_CmsQOjjzfikLVludo4uzr39wdDLXY10jV2u4I5PYOssgCACaWery8" #Bing Map Key

        # #Distance Calculation for FCI_FPS
        # if df1.empty or df4.empty:
        #    distance_new1.to_excel("Backend//concatenated_result.xlsx",sheet_name='IG_FPS',index=False,)
        # else:
        #     for i in df1.index:
        #         for j in df4.index:
        #             origin=df1.loc[i]["concatenate"]
        #             dest=df4.loc[j]["concatenate1"]
        #             response=requests.get("https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="+ origin + "&destinations="+ dest  +"&travelMode=driving&key=" + BingMapsKey)
        #             resp=response.json()
        #             #print(resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance'])
        #             dist3[i][j]=resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance']
                
        #     dist3=np.transpose(dist3)
        #     dist4=np.transpose(dist3)
        #     df6 = pd.DataFrame (dist4, columns = df4['FPS_ID'], index = df1['WH_ID'])
        #     df6.transpose().to_excel("Backend//Result_91.xlsx",sheet_name='IG_FPS')

        #     file1 = pd.ExcelFile("Backend//Distance_Matrix.xlsx")
        #     file2 = pd.ExcelFile("Backend//Result_91.xlsx")
        #     df1 = file1.parse('BG_BG')  # Assuming the data is in 'Sheet1'
        #     df2 = file2.parse('IG_FPS')
        #     df1.set_index('FPS_ID', inplace=True)
        #     df2.set_index('FPS_ID', inplace=True)
        #     result = pd.concat([df1, df2], axis=0)
        #     # Reset the index to avoid duplicate index values
        #     result.reset_index(inplace=True)
        #     # Save the resulting DataFrame to an Excel file
        #     result.to_excel('Backend//concatenated_result.xlsx', index=False,sheet_name='IG_FPS')

#-------------------------------------------------------------------------------------------------------
        def get_token():
            auth_url = 'https://kerala.pmgatishakti.gov.in/PMGatishaktiApiService/authenticate'
            auth_payload = {
                "username": "DFPD_C",
                "password": "W9Vtb8WKkt3"
            }
            response = requests.post(auth_url, json=auth_payload)
            if response.status_code == 200:
                return response.json()['token']
            else:
                raise Exception(f"Error getting token: {response.status_code} - {response.text}")
  
        USN = pd.ExcelFile("Backend//Template_FPS_Warehouse.xlsx")
        df1 = pd.read_excel(USN, 'Warehouse') 
        df4 = pd.read_excel(USN, 'FPS')  

        source3 = df1['WH_ID']  
        destination3 = df4['FPS_ID']
        dist3 = np.zeros((len(source3), len(destination3)))  
        BingMapsKey = "AhDBAf8zi_2llbdYS0n4GKYX6Z5L-hl89QX1V0VWTfXhPVH5jk9tg2wehciKelun"  

        def calculate_distance_function():                         
            USN = pd.ExcelFile("Backend//Template_FPS_Warehouse.xlsx")#Input Parameter sheet

            df1 = pd.read_excel(USN,'Warehouse')#Input FCI_Sheet
            df4 = pd.read_excel(USN,'FPS')#Input FPS_Sheet
            source3 = df1['WH_ID']    #FCI is source and FPS is destination
            destination3 = df4['FPS_ID']
            dist3 = [[0 for a in range(len(destination3))] for b in range(len(source3))] #Transport matrix for FCI_FPS
            BingMapsKey = "AhqrM7qhDZ_CmsQOjjzfikLVludo4uzr39wdDLXY10jV2u4I5PYOssgCACaWery8" #Bing Map Key

            #Distance Calculation for FCI_FPS
            if df1.empty or df4.empty:
                distance_new1.to_excel("Backend//concatenated_result.xlsx",sheet_name='IG_FPS',index=False,)
            else:
                for i in df1.index:
                    for j in df4.index:
                        origin=df1.loc[i]["concatenate"]
                        dest=df4.loc[j]["concatenate1"]
                        response=requests.get("https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="+ origin + "&destinations="+ dest  +"&travelMode=driving&key=" + BingMapsKey)
                        resp=response.json()
                        #print(resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance'])
                        dist3[i][j]=resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance']
                    
                dist3=np.transpose(dist3)
                dist4=np.transpose(dist3)
                df6 = pd.DataFrame (dist4, columns = df4['FPS_ID'], index = df1['WH_ID'])
                df6.transpose().to_excel("Backend//Result_91.xlsx",sheet_name='IG_FPS')

                file1 = pd.ExcelFile("Backend//Distance_Matrix.xlsx")
                file2 = pd.ExcelFile("Backend//Result_91.xlsx")
                df1 = file1.parse('BG_BG')  # Assuming the data is in 'Sheet1'
                df2 = file2.parse('IG_FPS')
                df1.set_index('FPS_ID', inplace=True)
                df2.set_index('FPS_ID', inplace=True)
                result = pd.concat([df1, df2], axis=0)
                # Reset the index to avoid duplicate index values
                result.reset_index(inplace=True)
                # Save the resulting DataFrame to an Excel file
                result.to_excel('Backend//concatenated_result.xlsx', index=False,sheet_name='IG_FPS')
            return True  

        if not calculate_distance_function():
            token = get_token() 
            gatishakti_url = "https://nctdelhi.pmgatishakti.gov.in/PMGatishaktiApiService/dfpdapi/roaddistance"

            for i in df1.index:
                for j in df4.index:
                    origin = df1.loc[i]["concatenate"]
                    dest = df4.loc[j]["concatenate1"]
                    headers = {
                        'Authorization': f'Bearer {token}'
                    }
                    payload = {
                        "origin": origin,
                        "destination": dest
                    }
                    response = requests.post(gatishakti_url, json=payload, headers=headers)
                    if response.status_code == 200:
                        travel_distance = response.json().get('distance', 0)  # Adjust based on actual response structure
                        dist3[i][j] = travel_distance
                        
            df6 = pd.DataFrame(dist3, columns=df4['FPS_ID'], index=df1['WH_ID'])

            df6.transpose().to_excel("Backend//Result_9.xlsx",sheet_name='IG_FPS')
            file1 = pd.ExcelFile("Backend//Distance_Matrix.xlsx")
            file2 = pd.ExcelFile("Backend//Result_91.xlsx")
            df1 = file1.parse('BG_BG')  # Assuming the data is in 'Sheet1'
            df2 = file2.parse('IG_FPS')
            df1.set_index('FPS_ID', inplace=True)
            df2.set_index('FPS_ID', inplace=True)
            result = pd.concat([df1, df2], axis=0)
            # Reset the index to avoid duplicate index values
            result.reset_index(inplace=True)
            # Save the resulting DataFrame to an Excel file
            result.to_excel('Backend//concatenated_result.xlsx', index=False,sheet_name='IG_FPS')
#--------------------------------------------------------------------------------------------------

        input = pd.ExcelFile('Backend//Data_1.xlsx')
        node1 = pd.read_excel(input,sheet_name="A.1 Warehouse")
        node2 = pd.read_excel(input,sheet_name="A.2 FPS")
        wh_ids = node1['WH_ID'].unique()
        fps_ids = node2['FPS_ID'].unique()
        matrix = pd.DataFrame(0, index=fps_ids, columns=wh_ids)
        output_file = 'Backend//matrix_output.xlsx'
        matrix.to_excel(output_file, index=True)
        dataframe1 = pd.read_excel(output_file)

        columnsList = dataframe1.columns
        columnsList = columnsList[1:]
        rowsList = dataframe1[dataframe1.columns[0]]
        Cost = pd.ExcelFile("Backend//concatenated_result.xlsx")
        PC_Mill = pd.read_excel(Cost,sheet_name="IG_FPS")
        distanceRows = PC_Mill[PC_Mill.columns[0]]

        counter = 0

        result = []
        result.append(dataframe1.columns)

        for i in rowsList:
            temp = []
            temp.append(i)
            for j in columnsList:
                counter = 0
                for k in distanceRows:
                    if k==i:
                        try:
                            temp.append(PC_Mill.at[counter, j])
                        except:
                            #print(counter)
                            print(j)
                        break
                    counter = counter + 1
            result.append(temp)
        result_transposed = list(map(list, zip(*result)))


        import xlsxwriter

        workbook = xlsxwriter.Workbook('Backend//Distance_Matrix_Final.xlsx')
        worksheet = workbook.add_worksheet()

        row = 0

        for col, data in enumerate(result_transposed):
            worksheet.write_column(row, col, data)

        workbook.close()

        file_path = 'Backend//Distance_Matrix_Final.xlsx'
        excel_file = pd.ExcelFile(file_path)

        # Load the specific sheet into a DataFrame
        df = excel_file.parse('Sheet1')

        # Rename the column
        df = df.rename(columns={'Unnamed: 0': 'FPS_ID'})

        # Save the DataFrame back to an Excel file
        with pd.ExcelWriter(file_path, engine='openpyxl') as writer:
             df.to_excel(writer, sheet_name='BG_BG', index=False)
        
        input = pd.ExcelFile('Backend//Data_1.xlsx')
        node3 = pd.read_excel(input,sheet_name="A.3 Mill")
        node2 = pd.read_excel(input,sheet_name="A.2 FPS")
        node3["concatenate"]= node3['WH_Lat'].astype(str) + ',' + node3['WH_Long'].astype(str)
        node2 = pd.read_excel(input,sheet_name="A.2 FPS")
        node2["concatenate1"]= node2['FPS_Lat'].astype(str) + ',' + node2['FPS_Long'].astype(str)
        Distance = pd.ExcelFile('Backend//Distance_Initial_L21.xlsx')
        DistanceBing = pd.read_excel(Distance,sheet_name="BG_BG")
        Warehouse = pd.read_excel(Distance,sheet_name="Warehouse")
        FPS = pd.read_excel(Distance,sheet_name="FPS")
        node3 = node3[['WH_ID', 'WH_Lat', 'WH_Long','concatenate']]
        War = pd.merge(node3, Warehouse, on='WH_ID')
        df1_w = War[War['concatenate'] != War['Lat_Long']]
        Warehouse_ID = df1_w['WH_ID'].unique()
        node2 = node2[['FPS_ID', 'FPS_Lat', 'FPS_Long','concatenate1']]
        FPS1 = pd.merge(node2, FPS, on='FPS_ID')
        df1_f = FPS1[FPS1['concatenate1'] != FPS1['Lat_Long']]
        FPS_ID = df1_f['FPS_ID'].unique()
        BG_BG = pd.read_excel(Distance,sheet_name="BG_BG")
        Distance1 = BG_BG.drop(columns=BG_BG.columns[BG_BG.columns.isin(Warehouse_ID)])
        Distance2 =Distance1.T
        Distance3 = Distance2.drop(columns=Distance2.columns[Distance2.columns.isin(FPS_ID)])
        Distance3 = Distance3.T
        with pd.ExcelWriter('Backend//Goa_Distance_L211.xlsx') as writer:
            Distance3.to_excel(writer, sheet_name='BG_BG',index=False)
        Warehouse_ID_series = pd.Series(Warehouse_ID)
        unique_in_df1 = node3[~node3['WH_ID'].isin(Warehouse['WH_ID'])]['WH_ID']
        combined_unique_series = pd.concat([unique_in_df1, Warehouse_ID_series])
        unique_combined = combined_unique_series.unique()
        #print(unique_combined )

        FPS_ID_series = pd.Series(FPS_ID)
        unique_in_df2 = node2[~node2['FPS_ID'].isin(FPS['FPS_ID'])]['FPS_ID']
        combined_unique_series_FPS = pd.concat([unique_in_df2, FPS_ID_series])
        unique_combined_FPS = combined_unique_series_FPS.unique()
        #print(unique_combined_FPS  )

        Warehouse_New = node3[node3['WH_ID'].isin(unique_combined)][['WH_ID', 'WH_Lat', 'WH_Long', 'concatenate']]
        with pd.ExcelWriter('Backend//Template_War_FPS1.xlsx') as writer:
            Warehouse_New.to_excel(writer, sheet_name='Warehouse',index=False)
            node2.to_excel(writer, sheet_name='FPS',index=False)

        FPS_New = node2[node2['FPS_ID'].isin(unique_combined_FPS)][['FPS_ID', 'FPS_Lat', 'FPS_Long', 'concatenate1']]
        with pd.ExcelWriter('Backend//Template_FPS_Warehouse1.xlsx') as writer:
             node3.to_excel(writer, sheet_name='Warehouse',index=False)
             FPS_New.to_excel(writer, sheet_name='FPS',index=False)

        
        Distance = pd.ExcelFile("Backend//Goa_Distance_L211.xlsx")#Input Parameter sheet
        distance_new=pd.read_excel(Distance,'BG_BG')

        # USN=pd.ExcelFile("Backend//Template_War_FPS1.xlsx")

        # df1 = pd.read_excel(USN,'Warehouse')#Input FCI_Sheet
        # df4 = pd.read_excel(USN,'FPS')#Input FPS_Sheet
        # source3 = df1['WH_ID']    #FCI is source and FPS is destination
        # destination3 = df4['FPS_ID']
        # dist3 = [[0 for a in range(len(destination3))] for b in range(len(source3))] #Transport matrix for FCI_FPS
        # BingMapsKey = "AhqrM7qhDZ_CmsQOjjzfikLVludo4uzr39wdDLXY10jV2u4I5PYOssgCACaWery8" #Bing Map Key

        # #Distance Calculation for FCI_FPS
        # if df1.empty or df4.empty:
        #    distance_new.to_excel("Backend//Distance_Matrix1.xlsx",sheet_name='BG_BG',index=False)
        # else:  
        #     for i in df1.index:
        #         for j in df4.index:
        #             origin=df1.loc[i]["concatenate"]
        #             dest=df4.loc[j]["concatenate1"]
        #             response=requests.get("https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="+ origin + "&destinations="+ dest  +"&travelMode=driving&key=" + BingMapsKey)
        #             resp=response.json()
        #             #print(resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance'])
        #             dist3[i][j]=resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance']

        #     dist3=np.transpose(dist3)
        #     dist4=np.transpose(dist3)
        #     df6 = pd.DataFrame (dist4, columns = df4['FPS_ID'], index = df1['WH_ID'])
        #     df6.transpose().to_excel("Backend//Result_91.xlsx",sheet_name='IG_FPS')
        #     file1 = pd.ExcelFile("Backend//Goa_Distance_L211.xlsx")
        #     file2 = pd.ExcelFile("Backend//Result_91.xlsx")
        #     df1 = file1.parse('BG_BG')  # Assuming the data is in 'Sheet1'
        #     df2 = file2.parse('IG_FPS')
        #     df1.set_index('FPS_ID', inplace=True)
        #     df2.set_index('FPS_ID', inplace=True)
        #     result = df1.add(df2, fill_value=0)
        #     result.reset_index(inplace=True)
        #     with pd.ExcelWriter('Backend//Goa_Distance_New1.xlsx') as writer:
        #          result.to_excel(writer, sheet_name='BG_BG',index=False)
        #     USN = pd.ExcelFile("Backend//Goa_Distance_New1.xlsx")  # Input Parameter sheet
        #     result = pd.read_excel(USN, 'BG_BG')  # Input FCI_Sheet
        #     result.set_index('FPS_ID', inplace=True)
        #     result2 = result.drop(index=unique_combined_FPS)
        #     result2.reset_index(inplace=True)
        #     result2.to_excel("Backend//Distance_Matrix1.xlsx", sheet_name='BG_BG', index=False)

#-------------------------------------------------------------------------------------------------------
        def get_token():
            auth_url = 'https://kerala.pmgatishakti.gov.in/PMGatishaktiApiService/authenticate'
            auth_payload = {
                "username": "DFPD_C",
                "password": "W9Vtb8WKkt3"
            }
            response = requests.post(auth_url, json=auth_payload)
            if response.status_code == 200:
                return response.json()['token']
            else:
                raise Exception(f"Error getting token: {response.status_code} - {response.text}")

           
        USN=pd.ExcelFile("Backend//Template_War_FPS1.xlsx")
        df1 = pd.read_excel(USN, 'Warehouse') 
        df4 = pd.read_excel(USN, 'FPS')  

        source3 = df1['WH_ID']  
        destination3 = df4['FPS_ID']
        dist3 = np.zeros((len(source3), len(destination3)))  
        BingMapsKey = "AhDBAf8zi_2llbdYS0n4GKYX6Z5L-hl89QX1V0VWTfXhPVH5jk9tg2wehciKelun"  

        def calculate_distance_function():                 
            USN=pd.ExcelFile("Backend//Template_War_FPS1.xlsx")

            df1 = pd.read_excel(USN,'Warehouse')#Input FCI_Sheet
            df4 = pd.read_excel(USN,'FPS')#Input FPS_Sheet
            source3 = df1['WH_ID']    #FCI is source and FPS is destination
            destination3 = df4['FPS_ID']
            dist3 = [[0 for a in range(len(destination3))] for b in range(len(source3))] #Transport matrix for FCI_FPS
            BingMapsKey = "AhqrM7qhDZ_CmsQOjjzfikLVludo4uzr39wdDLXY10jV2u4I5PYOssgCACaWery8" #Bing Map Key

            #Distance Calculation for FCI_FPS
            if df1.empty or df4.empty:
               distance_new.to_excel("Backend//Distance_Matrix1.xlsx",sheet_name='BG_BG',index=False)
            else:  
                for i in df1.index:
                    for j in df4.index:
                        origin=df1.loc[i]["concatenate"]
                        dest=df4.loc[j]["concatenate1"]
                        response=requests.get("https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="+ origin + "&destinations="+ dest  +"&travelMode=driving&key=" + BingMapsKey)
                        resp=response.json()
                        #print(resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance'])
                        dist3[i][j]=resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance']

                dist3=np.transpose(dist3)
                dist4=np.transpose(dist3)
                df6 = pd.DataFrame (dist4, columns = df4['FPS_ID'], index = df1['WH_ID'])
                df6.transpose().to_excel("Backend//Result_91.xlsx",sheet_name='IG_FPS')
                file1 = pd.ExcelFile("Backend//Goa_Distance_L211.xlsx")
                file2 = pd.ExcelFile("Backend//Result_91.xlsx")
                df1 = file1.parse('BG_BG')  # Assuming the data is in 'Sheet1'
                df2 = file2.parse('IG_FPS')
                df1.set_index('FPS_ID', inplace=True)
                df2.set_index('FPS_ID', inplace=True)
                result = df1.add(df2, fill_value=0)
                result.reset_index(inplace=True)
                with pd.ExcelWriter('Backend//Goa_Distance_New1.xlsx') as writer:
                     result.to_excel(writer, sheet_name='BG_BG',index=False)
                USN = pd.ExcelFile("Backend//Goa_Distance_New1.xlsx")  # Input Parameter sheet
                result = pd.read_excel(USN, 'BG_BG')  # Input FCI_Sheet
                result.set_index('FPS_ID', inplace=True)
                result2 = result.drop(index=unique_combined_FPS)
                result2.reset_index(inplace=True)
                result2.to_excel("Backend//Distance_Matrix1.xlsx", sheet_name='BG_BG', index=False)
            return True  

        if not calculate_distance_function():
            token = get_token() 
            gatishakti_url = "https://nctdelhi.pmgatishakti.gov.in/PMGatishaktiApiService/dfpdapi/roaddistance"

            for i in df1.index:
                for j in df4.index:
                    origin = df1.loc[i]["concatenate"]
                    dest = df4.loc[j]["concatenate1"]
                    headers = {
                        'Authorization': f'Bearer {token}'
                    }
                    payload = {
                        "origin": origin,
                        "destination": dest
                    }
                    response = requests.post(gatishakti_url, json=payload, headers=headers)
                    if response.status_code == 200:
                        travel_distance = response.json().get('distance', 0)  # Adjust based on actual response structure
                        dist3[i][j] = travel_distance
                        
            df6 = pd.DataFrame(dist3, columns=df4['FPS_ID'], index=df1['WH_ID'])

            df6.transpose().to_excel("Backend//Result_9.xlsx",sheet_name='IG_FPS')
            file1 = pd.ExcelFile("Backend//Goa_Distance_L211.xlsx")
            file2 = pd.ExcelFile("Backend//Result_91.xlsx")
            df1 = file1.parse('BG_BG')  # Assuming the data is in 'Sheet1'
            df2 = file2.parse('IG_FPS')
            df1.set_index('FPS_ID', inplace=True)
            df2.set_index('FPS_ID', inplace=True)
            result = df1.add(df2, fill_value=0)
            result.reset_index(inplace=True)
            with pd.ExcelWriter('Backend//Goa_Distance_New1.xlsx') as writer:
                 result.to_excel(writer, sheet_name='BG_BG',index=False)
            USN = pd.ExcelFile("Backend//Goa_Distance_New1.xlsx")  # Input Parameter sheet
            result = pd.read_excel(USN, 'BG_BG')  # Input FCI_Sheet
            result.set_index('FPS_ID', inplace=True)
            result2 = result.drop(index=unique_combined_FPS)
            result2.reset_index(inplace=True)
            result2.to_excel("Backend//Distance_Matrix1.xlsx", sheet_name='BG_BG', index=False)
#--------------------------------------------------------------------------------------------------

        Distance1 = pd.ExcelFile("Backend//Distance_Matrix1.xlsx")#Input Parameter sheet
        distance_new1=pd.read_excel(Distance1,'BG_BG')

        # USN = pd.ExcelFile("Backend//Template_FPS_Warehouse1.xlsx")#Input Parameter sheet
        # df1 = pd.read_excel(USN,'Warehouse')#Input FCI_Sheet
        # df4 = pd.read_excel(USN,'FPS')#Input FPS_Sheet
        # source3 = df1['WH_ID']    #FCI is source and FPS is destination
        # destination3 = df4['FPS_ID']
        # dist3 = [[0 for a in range(len(destination3))] for b in range(len(source3))] #Transport matrix for FCI_FPS
        # BingMapsKey = "AhqrM7qhDZ_CmsQOjjzfikLVludo4uzr39wdDLXY10jV2u4I5PYOssgCACaWery8" #Bing Map Key

        # #Distance Calculation for FCI_FPS
        # if df1.empty or df4.empty:
        #    distance_new1.to_excel("Backend//concatenated_result1.xlsx",sheet_name='IG_FPS',index=False,)
        # else:
        #     for i in df1.index:
        #         for j in df4.index:
        #             origin=df1.loc[i]["concatenate"]
        #             dest=df4.loc[j]["concatenate1"]
        #             response=requests.get("https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="+ origin + "&destinations="+ dest  +"&travelMode=driving&key=" + BingMapsKey)
        #             resp=response.json()
        #             #print(resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance'])
        #             dist3[i][j]=resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance']

        #     dist3=np.transpose(dist3)
        #     dist4=np.transpose(dist3)
        #     df6 = pd.DataFrame (dist4, columns = df4['FPS_ID'], index = df1['WH_ID'])
        #     df6.transpose().to_excel("Backend//Result_91.xlsx",sheet_name='IG_FPS')

        #     file1 = pd.ExcelFile("Backend//Distance_Matrix1.xlsx")
        #     file2 = pd.ExcelFile("Backend//Result_91.xlsx")
        #     df1 = file1.parse('BG_BG')  # Assuming the data is in 'Sheet1'
        #     df2 = file2.parse('IG_FPS')
        #     df1.set_index('FPS_ID', inplace=True)
        #     df2.set_index('FPS_ID', inplace=True)
        #     result = pd.concat([df1, df2], axis=0)
        #     # Reset the index to avoid duplicate index values
        #     result.reset_index(inplace=True)
        #     # Save the resulting DataFrame to an Excel file
        #     result.to_excel('Backend//concatenated_result1.xlsx', index=False,sheet_name='IG_FPS')

#-------------------------------------------------------------------------------------------------------
        def get_token():
            auth_url = 'https://kerala.pmgatishakti.gov.in/PMGatishaktiApiService/authenticate'
            auth_payload = {
                "username": "DFPD_C",
                "password": "W9Vtb8WKkt3"
            }
            response = requests.post(auth_url, json=auth_payload)
            if response.status_code == 200:
                return response.json()['token']
            else:
                raise Exception(f"Error getting token: {response.status_code} - {response.text}")
           
        USN = pd.ExcelFile("Backend//Template_FPS_Warehouse1.xlsx")
        df1 = pd.read_excel(USN, 'Warehouse') 
        df4 = pd.read_excel(USN, 'FPS')  

        source3 = df1['WH_ID']  
        destination3 = df4['FPS_ID']
        dist3 = np.zeros((len(source3), len(destination3)))  
        BingMapsKey = "AhDBAf8zi_2llbdYS0n4GKYX6Z5L-hl89QX1V0VWTfXhPVH5jk9tg2wehciKelun"  

        def calculate_distance_function():                         
            USN = pd.ExcelFile("Backend//Template_FPS_Warehouse1.xlsx")#Input Parameter sheet

            df1 = pd.read_excel(USN,'Warehouse')#Input FCI_Sheet
            df4 = pd.read_excel(USN,'FPS')#Input FPS_Sheet
            source3 = df1['WH_ID']    #FCI is source and FPS is destination
            destination3 = df4['FPS_ID']
            dist3 = [[0 for a in range(len(destination3))] for b in range(len(source3))] #Transport matrix for FCI_FPS
            BingMapsKey = "AhqrM7qhDZ_CmsQOjjzfikLVludo4uzr39wdDLXY10jV2u4I5PYOssgCACaWery8" #Bing Map Key

            #Distance Calculation for FCI_FPS
            if df1.empty or df4.empty:
               distance_new1.to_excel("Backend//concatenated_result1.xlsx",sheet_name='IG_FPS',index=False,)
            else:
                for i in df1.index:
                    for j in df4.index:
                        origin=df1.loc[i]["concatenate"]
                        dest=df4.loc[j]["concatenate1"]
                        response=requests.get("https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="+ origin + "&destinations="+ dest  +"&travelMode=driving&key=" + BingMapsKey)
                        resp=response.json()
                        #print(resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance'])
                        dist3[i][j]=resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance']

                dist3=np.transpose(dist3)
                dist4=np.transpose(dist3)
                df6 = pd.DataFrame (dist4, columns = df4['FPS_ID'], index = df1['WH_ID'])
                df6.transpose().to_excel("Backend//Result_91.xlsx",sheet_name='IG_FPS')

                file1 = pd.ExcelFile("Backend//Distance_Matrix1.xlsx")
                file2 = pd.ExcelFile("Backend//Result_91.xlsx")
                df1 = file1.parse('BG_BG')  # Assuming the data is in 'Sheet1'
                df2 = file2.parse('IG_FPS')
                df1.set_index('FPS_ID', inplace=True)
                df2.set_index('FPS_ID', inplace=True)
                result = pd.concat([df1, df2], axis=0)
                # Reset the index to avoid duplicate index values
                result.reset_index(inplace=True)
                # Save the resulting DataFrame to an Excel file
                result.to_excel('Backend//concatenated_result1.xlsx', index=False,sheet_name='IG_FPS')
            return True  

        if not calculate_distance_function():
            token = get_token() 
            gatishakti_url = "https://nctdelhi.pmgatishakti.gov.in/PMGatishaktiApiService/dfpdapi/roaddistance"

            for i in df1.index:
                for j in df4.index:
                    origin = df1.loc[i]["concatenate"]
                    dest = df4.loc[j]["concatenate1"]
                    headers = {
                        'Authorization': f'Bearer {token}'
                    }
                    payload = {
                        "origin": origin,
                        "destination": dest
                    }
                    response = requests.post(gatishakti_url, json=payload, headers=headers)
                    if response.status_code == 200:
                        travel_distance = response.json().get('distance', 0)  # Adjust based on actual response structure
                        dist3[i][j] = travel_distance
                        
            df6 = pd.DataFrame(dist3, columns=df4['FPS_ID'], index=df1['WH_ID'])

            df6.transpose().to_excel("Backend//Result_9.xlsx",sheet_name='IG_FPS')
            file1 = pd.ExcelFile("Backend//Sikkim_Distance_L21.xlsx")
            file2 = pd.ExcelFile("Backend//Result_9.xlsx")
            df1 = file1.parse('BG_BG') 
            df2 = file2.parse('IG_FPS')
            df1.set_index('FPS_ID', inplace=True)
            df2.set_index('FPS_ID', inplace=True)
            result = df1.add(df2, fill_value=0)
            result.reset_index(inplace=True)
            with pd.ExcelWriter('Backend//Sikkim_Distance_New.xlsx') as writer:
                    result.to_excel(writer, sheet_name='BG_BG',index=False)
            USN = pd.ExcelFile("Backend//Sikkim_Distance_New.xlsx")  
            result = pd.read_excel(USN, 'BG_BG')  
            result.set_index('FPS_ID', inplace=True)
            result2 = result.drop(index=unique_combined_FPS)
            result2.reset_index(inplace=True)
            result2.to_excel("Backend//Distance_Matrix.xlsx", sheet_name='BG_BG', index=False)
#--------------------------------------------------------------------------------------------------

        input = pd.ExcelFile('Backend//Data_1.xlsx')
        node3 = pd.read_excel(input,sheet_name="A.3 Mill")
        node2 = pd.read_excel(input,sheet_name="A.2 FPS")
        wh_ids = node3['WH_ID'].unique()
        fps_ids = node2['FPS_ID'].unique()
        matrix = pd.DataFrame(0, index=fps_ids, columns=wh_ids)
        output_file = 'Backend//matrix_output1.xlsx'
        matrix.to_excel(output_file, index=True)
        dataframe1 = pd.read_excel(output_file)

        columnsList = dataframe1.columns
        columnsList = columnsList[1:]
        rowsList = dataframe1[dataframe1.columns[0]]
        Cost = pd.ExcelFile("Backend//concatenated_result1.xlsx")
        PC_Mill = pd.read_excel(Cost,sheet_name="IG_FPS")
        distanceRows = PC_Mill[PC_Mill.columns[0]]

        counter = 0

        result = []
        result.append(dataframe1.columns)

        for i in rowsList:
            temp = []
            temp.append(i)
            for j in columnsList:
                counter = 0
                for k in distanceRows:
                    if k==i:
                        try:
                            temp.append(PC_Mill.at[counter, j])
                        except:
                            #print(counter)
                            print(j)
                        break
                    counter = counter + 1
            result.append(temp)
        result_transposed = list(map(list, zip(*result)))


        import xlsxwriter

        workbook = xlsxwriter.Workbook('Backend//Distance_Matrix_Final1.xlsx')
        worksheet = workbook.add_worksheet()

        row = 0

        for col, data in enumerate(result_transposed):
            worksheet.write_column(row, col, data)

        workbook.close()

        file_path = 'Backend//Distance_Matrix_Final1.xlsx'
        excel_file = pd.ExcelFile(file_path)

        # Load the specific sheet into a DataFrame
        df = excel_file.parse('Sheet1')

        # Rename the column
        df = df.rename(columns={'Unnamed: 0': 'FPS_ID'})

        # Save the DataFrame back to an Excel file
        with pd.ExcelWriter(file_path, engine='openpyxl') as writer:
             df.to_excel(writer, sheet_name='BG_IG', index=False)

        
        
        file_path = 'Backend/Distance_Matrix_Final.xlsx'
        file_path1 = 'Backend/Distance_Matrix_Final1.xlsx'

        # Read the two sheets into DataFrames
        df1 = pd.read_excel(file_path, sheet_name='BG_BG')
        df2 = pd.read_excel(file_path1, sheet_name='BG_IG')

        result = pd.merge(df1, df2, on='FPS_ID')

        # File path to save the resulting DataFrame
        output_path = 'Backend/Merged_Result.xlsx'

        # Save the resulting DataFrame to a new Excel file
        result.to_excel(output_path, sheet_name='BG_BG', index=False)
                
        USN = pd.ExcelFile('Backend//Data_1.xlsx')
        FCI = pd.read_excel(USN, sheet_name='A.1 Warehouse', index_col=None)
        FPS = pd.read_excel(USN, sheet_name='A.2 FPS', index_col=None)
        Mill = pd.read_excel(USN, sheet_name='A.3 Mill', index_col=None)

        FCI['WH_District'] = FCI['WH_District'].apply(lambda x: x.replace(' ', ''))
        FPS['FPS_District'] = FPS['FPS_District'].apply(lambda x: x.replace(' ', ''))
        Mill['WH_District'] = Mill['WH_District'].apply(lambda x: x.replace(' ', ''))
        
        
        model = LpProblem('Supply-Demand-Problem', LpMinimize)

        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)

        Variable1 = []
        for i in range(len(Mill['WH_ID'])):
            for j in range(len(FPS['FPS_ID'])):
                Variable1.append(str(Mill['WH_ID'][i]) + '_'
                                 + str(Mill['WH_District'][i]) + '_'
                                 + str(FPS['FPS_ID'][j]) + '_'
                                 + str(FPS['FPS_District'][j]) + '_Atta')
                                 
        

        # Variables for Wheat from lEVEL2 TO FPS

        DV_Variables1 = LpVariable.matrix('X', Variable1, cat='float',
                lowBound=0)
        Allocation1 = np.array(DV_Variables1).reshape(len(Mill['WH_ID']),
                len(FPS['FPS_ID']))
        
        
        
        
        Variable2 = []
        for i in range(len(FCI['WH_ID'])):
            for j in range(len(FPS['FPS_ID'])):
                Variable2.append(str(FCI['WH_ID'][i]) + '_'
                                 + str(FCI['WH_District'][i]) + '_'
                                 + str(FPS['FPS_ID'][j]) + '_'
                                 + str(FPS['FPS_District'][j]) + '_Rice')
                                 
        

        # Variables for Wheat from lEVEL2 TO FPS

        DV_Variables2 = LpVariable.matrix('X', Variable2, cat='float',
                lowBound=0)
        Allocation2 = np.array(DV_Variables2).reshape(len(FCI['WH_ID']),
                len(FPS['FPS_ID']))
                
        Variable3 = []
        for i in range(len(FCI['WH_ID'])):
            for j in range(len(FPS['FPS_ID'])):
                Variable3.append(str(FCI['WH_ID'][i]) + '_'
                                 + str(FCI['WH_District'][i]) + '_'
                                 + str(FPS['FPS_ID'][j]) + '_'
                                 + str(FPS['FPS_District'][j]) + '_FRice')
                                 
        

        # Variables for Wheat from lEVEL2 TO FPS

        DV_Variables3 = LpVariable.matrix('X', Variable3, cat='float',
                lowBound=0)
        Allocation3 = np.array(DV_Variables3).reshape(len(FCI['WH_ID']),
                len(FPS['FPS_ID']))
        
        

        Variable1I = []
        Allocation1I = []
        for i in range(len(FCI['WH_ID'])):
            for j in range(len(FPS['FPS_ID'])):
                Variable1I.append(str(FCI['WH_ID'][i]) + '_'
                                  + str(FCI['WH_District'][i]) + '_'
                                  + str(FPS['FPS_ID'][j]) + '_'
                                  + str(FPS['FPS_District'][j]) + '_Wheat1')

    #    Variables for Wheat from IG TO FPS

        DV_Variables1I = LpVariable.matrix('X', Variable1I, cat='Binary',lowBound=0)
        Allocation1I = np.array(DV_Variables1I).reshape(len(FCI['WH_ID']),len(FPS['FPS_ID']))

        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)

        for i in range(len(FPS['FPS_ID'])):
             model += lpSum(Allocation1I[k][i] for k in range(len(FCI['WH_ID']))) <= 1

        for i in range(len(FCI['WH_ID'])):
             for j in range(len(FPS['FPS_ID'])):
                model += Allocation2[i][j] <= 1000000 * Allocation1I[i][j]
                
        for i in range(len(FCI['WH_ID'])):
             for j in range(len(FPS['FPS_ID'])):
                model += Allocation3[i][j] <= 1000000 * Allocation1I[i][j]
                
        Variable2I = []
        Allocation2I = []
        for i in range(len(Mill['WH_ID'])):
            for j in range(len(FPS['FPS_ID'])):
                Variable2I.append(str(Mill['WH_ID'][i]) + '_'
                                  + str(Mill['WH_District'][i]) + '_'
                                  + str(FPS['FPS_ID'][j]) + '_'
                                  + str(FPS['FPS_District'][j]) + '_Wheat1')

    #    Variables for Wheat from IG TO FPS

        DV_Variables2I = LpVariable.matrix('X', Variable2I, cat='Binary',lowBound=0)
        Allocation2I = np.array(DV_Variables2I).reshape(len(Mill['WH_ID']),len(FPS['FPS_ID']))
        
        
        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)

        for i in range(len(FPS['FPS_ID'])):
             model += lpSum(Allocation2I[k][i] for k in range(len(Mill['WH_ID']))) <= 1

        for i in range(len(Mill['WH_ID'])):
             for j in range(len(FPS['FPS_ID'])):
                model += Allocation1[i][j] <= 1000000 * Allocation2I[i][j]

                 
        WKB = excelrd.open_workbook('Backend//Distance_Matrix_Final.xlsx')
        Sheet1 = WKB.sheet_by_index(0)
        WKB = excelrd.open_workbook('Backend//Distance_Matrix_Final1.xlsx')
        Sheet2 = WKB.sheet_by_index(0)
        
        PC_Mill = []
        for col in range(Sheet1.nrows):
            if col==0:
                continue
            temp = []
            for row in range (Sheet1.ncols):
                if row==0:
                    continue
                temp.append(Sheet1.cell_value(col,row))
            PC_Mill.append(temp)

        FCI_FPS = [[ PC_Mill[j][i] for j in range(len( PC_Mill))] for i in range(len( PC_Mill[0]))]
        
        PC_Mill1 = []
        for col in range(Sheet2.nrows):
            if col==0:
                continue
            temp = []
            for row in range (Sheet2.ncols):
                if row==0:
                    continue
                temp.append(Sheet2.cell_value(col,row))
            PC_Mill1.append(temp)

        FCI_FPS1 = [[ PC_Mill1[j][i] for j in range(len( PC_Mill1))] for i in range(len( PC_Mill1[0]))]
        
        
        

        allCombination1 = []
        allCombination2 = []
        allCombination3 = []
        


        for i in range(len(FCI_FPS)):
            for j in range(len(FPS['FPS_ID'])):
                allCombination2.append(Allocation2[i][j] * FCI_FPS[i][j])
       
        for i in range(len(FCI_FPS)):
            for j in range(len(FPS['FPS_ID'])):
                allCombination3.append(Allocation3[i][j] * FCI_FPS[i][j])
                       
        for i in range(len(FCI_FPS1)):
            for j in range(len(FPS['FPS_ID'])):
                allCombination1.append(Allocation1[i][j] * FCI_FPS1[i][j])
                
        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)

        model += lpSum(allCombination1+allCombination2+allCombination3)

        # Demand Constraints for Wheat
        
        

        for i in range(len(FPS['FPS_ID'])):
            model += ((lpSum(Allocation1[j][i] for j in range(len(Mill['WH_ID'
                           ])))) >= FPS['Allocation_Wheat'][i])
                           
        for i in range(len(FPS['FPS_ID'])):
            model += ((lpSum(Allocation2[j][i] for j in range(len(FCI['WH_ID'
                           ])))) >= FPS['Allocation_Rice'][i])
                           
        for i in range(len(FPS['FPS_ID'])):
            model += ((lpSum(Allocation3[j][i] for j in range(len(FCI['WH_ID'
                           ])))) >= FPS['Allocation_FRice'][i])
                           
                           
        for i in range(len(FPS['FPS_ID'])):
            model += ((lpSum(Allocation1[j][i] for j in range(len(Mill['WH_ID'
                           ])))) <= FPS['Allocation_Wheat'][i])
                           
        for i in range(len(FPS['FPS_ID'])):
            model += ((lpSum(Allocation2[j][i] for j in range(len(FCI['WH_ID'
                           ])))) <= FPS['Allocation_Rice'][i])
                           
        for i in range(len(FPS['FPS_ID'])):
            model += ((lpSum(Allocation3[j][i] for j in range(len(FCI['WH_ID'
                           ])))) <= FPS['Allocation_FRice'][i])

        # Supply Constraints for Warehouses

        for i in range(len(FCI['WH_ID'])):
            model += (((lpSum(Allocation2[i][j] for j in range(len(FPS['FPS_ID'
                           ]))))  + lpSum(Allocation3[i][j] for j in range(len(FPS['FPS_ID'
                           ])))) <= FCI['Storage_Capacity'][i])
                           
        for i in range(len(Mill['WH_ID'])):
            model += ((lpSum(Allocation1[i][j] for j in range(len(FPS['FPS_ID'
                           ]))))   <= Mill['Processing'][i])

       # Calling CBC_CMB Solver

        model.solve(CPLEX_CMD(options=['set mip tolerances mipgap 0.01']))
        #model.prob.solve(CPLEX_CMD(options=["set mip tolerances mipgap 0.03","set emphasis memory y"]))
        #model.solve(CPLEX_CMD(options=['set mip tolerances mipgap 0.03',"set emphasis memory y"]))
        #model.solve(PULP_CBC_CMD())
        
        status = LpStatus[model.status]
        if status == LpStatusInfeasible or status == LpStatusUnbounded or status == LpStatusNotSolved or status == LpStatusUndefined:
           print("Problem is infeasible or unbounded.")
           data = {}
           data['status'] = 0
           data['message'] = "Infeasible or Unbounded Solution"
           json_data = json.dumps(data)
           json_object = json.loads(json_data)
           return json.dumps(json_object, indent=1)
 
        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)

        #model.solve(PULP_CBC_CMD())
        
        
        

        data = {}

        Output_File = open('Backend//Inter_District1.csv', 'w')
        for v in model.variables():
            if v.value() > 0:
                Output_File.write(v.name + '\t' + str(v.value()) + '\n')

        Output_File = open('Backend//Inter_District1.csv', 'w')
        for v in model.variables():
            if v.value() > 0:
                Output_File.write(v.name + '\t' + str(v.value()) + '\n')


        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)

        df9 = pd.read_csv('Backend//Inter_District1.csv',header=None)
        df9.columns = ['Tagging']
        df9[[
            'Var',
            'WH_ID',
            'W_D',
            'FPS_ID',
            'FPS_D',
            'commodity_Value',
            ]] = df9[df9.columns[0]].str.split('_', n=6, expand=True)
        del df9[df9.columns[0]]
        df9[['commodity', 'Values']] = df9['commodity_Value'
                ].str.split('\\t', n=1, expand=True)
        del df9['commodity_Value']
        df9 = df9.drop(np.where(df9['commodity'] == 'Wheat1')[0])
        
        def convert_to_numeric(value):
            try:
                return pd.to_numeric(value)
            except ValueError:
                return value
        
        
        df9['WH_ID'] = df9['WH_ID'].apply(convert_to_numeric)
        df9['FPS_ID'] = df9['FPS_ID'].apply(convert_to_numeric)
        
        df9.to_excel('Backend//Tagging_Sheet_Pre.xlsx', sheet_name='BG_FPS')
        df31 = pd.read_excel('Backend//Tagging_Sheet_Pre.xlsx')
        
        
        USN = pd.ExcelFile('Backend//Data_1.xlsx')
        Depot = pd.read_excel(USN, sheet_name='A.1 Warehouse', index_col=None)
        FPS = pd.read_excel(USN, sheet_name='A.2 FPS', index_col=None)
        
        Mill = pd.read_excel(USN, sheet_name='A.3 Mill', index_col=None)
        
        columns_to_include = ["WH_District","WH_Name","WH_ID",	"Type",	"WH_Lat",	"WH_Long"]
        df1_selected = Depot[columns_to_include]
        df2_selected = Mill[columns_to_include]
        
        FCI = pd.concat([df1_selected, df2_selected], ignore_index=True)
        
        
        df4 = pd.merge(df31, FCI, on='WH_ID', how='inner')
        #df4 = pd.merge(df31, FCI, on='WH_ID', how='inner')
        df4 = df4[[
            'WH_ID',
            'Type',
            'WH_Name',
            'WH_District',
            'WH_Lat',
            'WH_Long',
            'FPS_ID',
            'Values',
            'commodity',

            ]]
        df4 = pd.merge(df4, FPS, on='FPS_ID', how='inner')
        df51 = df4[[
            'WH_ID',
            'Type',
            'WH_Name',
            'WH_District',
            'WH_Lat',
            'WH_Long',
            'FPS_ID',
            'FPS_Name',
            'FPS_District',
            'FPS_Lat',
            'FPS_Long',
            'Values',
            'commodity',

            
            ]]
        df51.insert(0, 'Scenario', 'Optimized')
        df51.insert(2, 'From_State', 'Ladakh')
        df51.insert(7, 'To', 'FPS')
        df51.insert(8, 'To_State', 'Ladakh')
  
        df51.rename(columns={
            'WH_ID': 'From_ID',
            'WH_Name': 'From_Name',
            'WH_Lat': 'From_Lat',
            'WH_Long': 'From_Long',
            }, inplace=True)
        df51.rename(columns={
            'FPS_ID': 'To_ID',
            'FPS_Name': 'To_Name',
            'FPS_Lat': 'To_Lat',
            'FPS_Long': 'To_Long',
            'Values': 'quantity',
            
            }, inplace=True)
        df51.rename(columns={'WH_District': 'From_District',
                              'Type':'From',
                   'FPS_District': 'To_District'}, inplace=True)
        df51 = df51.loc[:, [
            'Scenario',
            'From',
            'From_State',
            'From_District',
            'From_ID',
            'From_Name',
            'From_Lat',
            'From_Long',
            'To',
            'To_ID',
            'To_Name',
            'To_State',
            'To_District',
            'To_Lat',
            'To_Long',
            'commodity',
            'quantity',
            ]]
        
        
                
        def convert_to_numeric(value):
            try:
                return pd.to_numeric(value)
            except ValueError:
                return value
                
        df_combined = pd.concat([df51])
        df_combined1 = df_combined[df_combined['quantity'] != 0]
        df_combined1['From_ID'] = df_combined1['From_ID'].apply(convert_to_numeric)
        df_combined1['To_ID'] = df_combined1['To_ID'].apply(convert_to_numeric)

        df_combined1.to_excel('Backend//Tagging_Sheet_Pre11.xlsx', sheet_name='BG_FPS1')
        
        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)  
        
        data1 = pd.ExcelFile("Backend//Tagging_Sheet_Pre11.xlsx")
        df5 = pd.read_excel(data1,sheet_name="BG_FPS1")

        Cost = pd.ExcelFile("Backend//Merged_Result.xlsx")
        BG_BG = pd.read_excel(Cost,sheet_name="BG_BG")
        
        Distance_BG_BG = {}
        column_list_BG_BG = list(BG_BG.columns.astype(str))
        row_list_BG_BG = list(BG_BG.iloc[:, 0].astype(str))

        for ind in df5.index:
            from_code = df5['From_ID'][ind]
            to_code = df5['To_ID'][ind]
            from_code_str = str(from_code)
            to_code_str = str(to_code)
            
            if to_code_str in row_list_BG_BG and from_code_str in column_list_BG_BG:
                index_i = row_list_BG_BG.index(to_code_str)
                index_j = column_list_BG_BG.index(from_code_str)
                key = to_code_str + "_" + from_code_str
                Distance_BG_BG[key] = BG_BG.iloc[index_i, index_j] 
                
                
        df5["Tagging"] = df5['To_ID'].astype(str) + '_' + df5['From_ID'].astype(str)
        df5['Distance'] = df5['Tagging'].map(Distance_BG_BG)
        df5.fillna('shallu', inplace=True)
        df5.to_excel('Backend//Result_Sheet12.xlsx', sheet_name='Warehouse_FPS', index=False)        

        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)            
                         
        # Result_Sheet1=pd.ExcelFile("Backend//Result_Sheet12.xlsx")
        # df6= pd.read_excel(Result_Sheet1,sheet_name="Warehouse_FPS")
        # df7=df6.loc[df6['Distance'] == "shallu"]
        # source3 = df7['From_ID']  # FCI is the source and FPS is the destination
        # destination3 = df7['To_ID']
        # BingMapsKey = "ApBZRxLaI7CNkPkE1E9mrJCh3l02nRLXax2m0s5ajUIAMdwuJWLN7oMWVtCHQYzr"  # Bing Map Key
        # df7["Warehouse_lat_long"]= df7['From_Lat'].astype(str) + ',' + df7['From_Long'].astype(str)
        # df7["FPS_lat_long"]= df7['To_Lat'].astype(str) + ',' + df7['To_Long'].astype(str)

        # #df8=df7["From_ID","To_ID","Warehouse_lat_long","FPS_lat_long"]
        # df8 = df7[['From_ID', 'To_ID', 'Warehouse_lat_long', 'FPS_lat_long']]
        # source3 = df8['From_ID']
        # destination3 = df8['To_ID']
        # dist3 = [0 for _ in range(len(destination3))]  # Transport matrix for FCI_FPS
        # BingMapsKey = "ApBZRxLaI7CNkPkE1E9mrJCh3l02nRLXax2m0s5ajUIAMdwuJWLN7oMWVtCHQYzr"  # Bing Map Key

        # dist3 = []  # Initialize an empty list for distances

        # for index, row in df8.iterrows():
        #     origin = row["Warehouse_lat_long"]
        #     dest = row["FPS_lat_long"]
        #     max_retries = 3
        #     retries = 0
        #     while retries < max_retries:
        #         try:
        #             response = requests.get(
        #                 "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins=" + origin + "&destinations=" + dest +
        #                 "&travelMode=driving&key=" + BingMapsKey)
        #             resp = response.json()

        #             # Append a new element to dist3 for the current index
        #             dist3.append(resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance'])

        #             # Display the output for each iteration
        #             #print(f"Origin: {origin}, Destination: {dest}, Distance: {dist3[-1]}")
        #             break  # Successful response, exit the retry loop
        #         except (requests.ConnectionError, requests.Timeout):
        #             retries += 1
        #             #print(f"Attempt {retries} failed. Retrying...")
        #             time.sleep(1)  # Wait for 1 second before retrying

        # #print("Final distances:", dist3)

        # if stop_process==True:
        #     data = {}
        #     data['status'] = 0
        #     data['message'] = "Process Stopped"
        #     json_data = json.dumps(data)
        #     json_object = json.loads(json_data)
        #     return json.dumps(json_object, indent=1)

        # df7["Distance"]=dist3
        # df7.drop(['Warehouse_lat_long', 'FPS_lat_long'], axis=1)
        # df9=df6.loc[df6['Distance'] != "shallu"]
        # df9 = df9.loc[:, [
        #         'Scenario',
        #         'From',
        #         'From_State',
        #         'From_District',
        #         'From_ID',
        #         'From_Name',
        #         'From_Lat',
        #         'From_Long',
        #         'To',
        #         'To_ID',
        #         'To_Name',
        #         'To_State',
        #         'To_District',
        #         'To_Lat',
        #         'To_Long',
        #         'commodity',
        #         'quantity',
        #          "Distance",]]
        # df7 = df7.loc[:, [
        #         'Scenario',
        #         'From',
        #         'From_State',
        #         'From_District',
        #         'From_ID',
        #         'From_Name',
        #         'From_Lat',
        #         'From_Long',
        #         'To',
        #         'To_ID',
        #         'To_Name',
        #         'To_State',
        #         'To_District',
        #         'To_Lat',
        #         'To_Long',
        #         'commodity',
        #         'quantity',
        #        "Distance"]]
        
        # #print(df9.head())  # Print the first few rows
        # #df10 = df9.append([df7], ignore_index=True)
        # df10 = pd.concat([df9, df7], ignore_index=True)
        # #df10 = df9.append([df7],ignore_index=True)
        
        # df10 = pd.concat([df9, df7], ignore_index=True)
        
        # df10.to_excel('Backend//Result_Sheet.xlsx',
        #              sheet_name='Warehouse_FPS')

# ----------------------------------------------------------------------------------------------------------------------------------------------
        Result_Sheet1 = pd.ExcelFile("Backend//Result_Sheet12.xlsx")
        df6 = pd.read_excel(Result_Sheet1, sheet_name="Warehouse_FPS")
        Result_Sheet1.close()

        df7 = df6.loc[df6['Distance'] == "shallu"]

        auth_url = 'https://kerala.pmgatishakti.gov.in/PMGatishaktiApiService/authenticate'
        distance_url = 'https://kerala.pmgatishakti.gov.in/PMGatishaktiApiService/dfpdapi/roaddistance'

        auth_payload = {
            "username": "DFPD_C",
            "password": "W9Vtb8WKkt3"
        }

        FILE_PATH = 'distanceIndent.json'

        def get_token():
            response = requests.post(auth_url, json=auth_payload)
            if response.status_code == 200:
                token = response.json()['token']
                if token : 
                    return token
                else: 
                    return False
            else:
                return False

        response_data = []


        def process_batch(df_batch):
            token = get_token()    
            time.sleep(15)
            headers = {
                'Authorization': f'Bearer {token}',
            }

            data = {
                "parameter": [{
                    "src_lng": row["From_Long"],
                    "src_lat": row["From_Lat"],
                    "dest_lng": row["To_Long"],
                    "dest_lat": row["To_Lat"]
                } for _, row in df_batch.iterrows()]
            }
            
            with open(FILE_PATH, 'w') as f:
                json.dump(data, f, indent=4)

            with open(FILE_PATH, 'rb') as f:
                files = {'LatsLongsFile': f}

                response = requests.post(distance_url, headers=headers, files=files)
                
                return response

        def process_single(row):
            token = get_token()
            time.sleep(2)
            headers = {
                'Authorization': f'Bearer {token}',
            }

            data = {
                "parameter": [{
                    "src_lng": row["From_Long"],
                    "src_lat": row["From_Lat"],
                    "dest_lng": row["To_Long"],
                    "dest_lat": row["To_Lat"]
                }]
            }
            
            with open(FILE_PATH, 'w') as f:
                json.dump(data, f, indent=4)

            with open(FILE_PATH, 'rb') as f:
                files = {'LatsLongsFile': f}

                response = requests.post(distance_url, headers=headers, files=files)
                return response
                    
        batch_size = 30
        total_rows = len(df7)
        num_batches = (total_rows + batch_size - 1) // batch_size
                    
        dist3 = []

        for batch_num in range(num_batches):
            start_idx = batch_num * batch_size
            end_idx = min((batch_num + 1) * batch_size, total_rows)
            df_batch = df7.iloc[start_idx:end_idx]
            
            response = process_batch(df_batch)
            if response.status_code == 200:
                response_json = response.json()
                if 'data' in response_json and all('distance' in row_data for row_data in response_json['data']):
                    for row_data, (_, row) in zip(response_json['data'], df_batch.iterrows()):
                        distance = row_data['distance']
                        dist3.append(distance)
                else : 
                    source3 = df_batch['From_ID']  
                    destination3 = df_batch['To_ID']
                    BingMapsKey = "AirsAsdRojcMdt_sHviub_uETiraB-on6DR3Q_AVyrOCedF0FkITbpVVSDKx6hjT"  # Bing Map Key
                    df_batch["Warehouse_lat_long"]= df7['From_Lat'].astype(str) + ',' + df7['From_Long'].astype(str)
                    df_batch["FPS_lat_long"]= df7['To_Lat'].astype(str) + ',' + df7['To_Long'].astype(str)

                    df8 = df_batch[['From_ID', 'To_ID', 'Warehouse_lat_long', 'FPS_lat_long']]
                    source3 = df8['From_ID']
                    destination3 = df8['To_ID']

                    for index, row in df8.iterrows():
                        origin = row["Warehouse_lat_long"]
                        dest = row["FPS_lat_long"]
                        max_retries = 3
                        retries = 0
                        while retries < max_retries:
                            try:
                                response = requests.get(
                                    "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins=" + origin + "&destinations=" + dest +
                                    "&travelMode=driving&key=" + BingMapsKey)
                                resp = response.json()
                                dist3.append(resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance'])
                                break  
                            except (requests.ConnectionError, requests.Timeout):
                                retries += 1
                                time.sleep(1) 
            else: 
                source3 = df_batch['From_ID']  
                destination3 = df_batch['To_ID']
                BingMapsKey = "AirsAsdRojcMdt_sHviub_uETiraB-on6DR3Q_AVyrOCedF0FkITbpVVSDKx6hjT"  # Bing Map Key
                df_batch["Warehouse_lat_long"]= df7['From_Lat'].astype(str) + ',' + df7['From_Long'].astype(str)
                df_batch["FPS_lat_long"]= df7['To_Lat'].astype(str) + ',' + df7['To_Long'].astype(str)

                df8 = df_batch[['From_ID', 'To_ID', 'Warehouse_lat_long', 'FPS_lat_long']]
                source3 = df8['From_ID']
                destination3 = df8['To_ID']

                for index, row in df8.iterrows():
                    origin = row["Warehouse_lat_long"]
                    dest = row["FPS_lat_long"]
                    max_retries = 3
                    retries = 0
                    while retries < max_retries:
                        try:
                            response = requests.get(
                                "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins=" + origin + "&destinations=" + dest +
                                "&travelMode=driving&key=" + BingMapsKey)
                            resp = response.json()

                            dist3.append(resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance'])
                            break 
                        except (requests.ConnectionError, requests.Timeout):
                            retries += 1
                            time.sleep(1)

        df7["Distance"]=dist3
        df9=df6.loc[df6['Distance'] != "shallu"]
        df9 = df9.loc[:, [
                'Scenario',
                'From',
                'From_State',
                'From_District',
                'From_ID',
                'From_Name',
                'From_Lat',
                'From_Long',
                'To',
                'To_ID',
                'To_Name',
                'To_State',
                'To_District',
                'To_Lat',
                'To_Long',
                'commodity',
                'quantity',
                'Distance']]
        df7 = df7.loc[:, [
                'Scenario',
                'From',
                'From_State',
                'From_District',
                'From_ID',
                'From_Name',
                'From_Lat',
                'From_Long',
                'To',
                'To_ID',
                'To_Name',
                'To_State',
                'To_District',
                'To_Lat',
                'To_Long',
                'commodity',
                'quantity',
                'Distance']]

        df10 = pd.concat([df9, df7], ignore_index=True)
        result = ((df10['quantity']) * df10['Distance']).sum()

        df10.to_excel('Backend//Result_Sheet.xlsx', sheet_name='Warehouse_FPS', index=False)
# ---------------------------------------------------------------------------------------------------------------------------------------------
                     
        Result_Sheet_Final=pd.ExcelFile("Backend//Result_Sheet.xlsx")
        sf= pd.read_excel(Result_Sheet_Final,sheet_name="Warehouse_FPS")
        sf1 = sf[sf['From'].str.lower() == 'mill']

        
        data["Scenario"]="Inter"
        data["Scenario_Baseline"] = "Baseline"
        
        data["From"]="Mill"
        data["From_Baseline"] = "Mill"
        
        data["To"]="FPS"
        data["To_Baseline"] = "FPS"
        
        data["WH_Used"] = sf1['From_ID'].nunique()
        data["WH_Used_Baseline"] = "2"
        
        data["FPS_Used"] = sf1['To_ID'].nunique()
        data["FPS_Used_Baseline"] = "384"
        
        data['Demand'] =sf1["quantity"].astype(float).sum()
        data['Demand_Baseline'] = "6,628"
        
        result = ((sf1['quantity']) * sf1['Distance']).sum()
        data['Total_QKM'] = float(result)
        data['Total_QKM_Baseline'] = "5,75,860"
        
        Total_Demand = sf1["quantity"].astype(float).sum()
        
        data['Average_Distance'] = float(round(result, 2)) / Total_Demand
        data['Average_Distance_Baseline'] = "86.88"
        
        
        sf2 = sf[sf['From'].str.lower() != 'mill']

        
        data["Scenario1"]="Inter"
        data["Scenario_Baseline1"] = "Baseline"
        
        data["From1"]="Depot"
        data["From_Baseline1"] = "Depot"
        
        data["To1"]="FPS"
        data["To_Baseline1"] = "FPS"
        
        data["WH_Used1"] = sf2['From_ID'].nunique()
        data["WH_Used_Baseline1"] = "2"
        
        data["FPS_Used1"] = sf2['To_ID'].nunique()
        data["FPS_Used_Baseline1"] = "384"
        
        data['Demand1'] =sf2["quantity"].astype(float).sum()
        data['Demand_Baseline1'] = "9,346"
        
        result1 = ((sf2['quantity']) * sf2['Distance']).sum()
        data['Total_QKM1'] = float(result1)
        data['Total_QKM_Baseline1'] = "7,05,086"
        
        Total_Demand1 = sf2["quantity"].astype(float).sum()
        
        data['Average_Distance1'] = float(round(result1, 2)) / Total_Demand1
        data['Average_Distance_Baseline1'] = "75.44"


        
        
        
        
        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)                     

        save_to_database(month, year, applicable)
        save_monthly_data(month, year, float(result))
        
        
        
        json_data = json.dumps(data)
        json_object = json.loads(json_data)

        if os.path.exists('ouputPickle.pkl'):
            os.remove('ouputPickle.pkl')

        # open pickle file
        dbfile1 = open('ouputPickle.pkl', 'ab')

    # save pickle data
    pickle.dump(json_object, dbfile1)
    dbfile1.close()
    data['status'] = 1
    json_data = json.dumps(data)
    json_object = json.loads(json_data)
    return json.dumps(json_object, indent=1)
    
@app.route('/processFileleg1', methods=['POST'])
def processFile_leg1():
    global stop_process
    stop_process = False
    scenario_type = request.form.get('type')
    '''scenario_type="intra"'''
    if scenario_type == "intra":
        message = 'DataFile file is incorrect'
        try:
            USN = pd.ExcelFile('Backend//Data_2.xlsx')
            month = request.form.get('month')        
            year = request.form.get('year')
            applicable = request.form.get('applicable')
        except Exception as e:
            data = {}
            data['status'] = 0
            data['message'] = message
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)
        input = pd.ExcelFile('Backend//Data_2.xlsx')
        node1 = pd.read_excel(input,sheet_name="A.2 FCI")
        node2 = pd.read_excel(input,sheet_name="A.1 Warehouse")

        dist = [[0 for a in range(len(node2["SW_ID"]))] for b in range(len(node1["WH_ID"]))]
        phi_1 = []
        phi_2 = []
        delta_phi = []
        delta_lambda = []
        R = 6371 

        for i in node1.index:
            for j in node2.index:
                phi_1=math.radians(node1["WH_Lat"][i])
                phi_2=math.radians(node2["SW_lat"][j])
                delta_phi=math.radians(node2["SW_lat"][j]-node1["WH_Lat"][i])
                delta_lambda=math.radians(node2["SW_Long"][j]-node1["WH_Long"][i])
                x=math.sin(delta_phi / 2.0) ** 2 + math.cos(phi_1) * math.cos(phi_2) * math.sin(delta_lambda / 2.0) ** 2
                y=2 * math.atan2(math.sqrt(x), math.sqrt(1 - x))
                dist[i][j]=R*y
                
        dist=np.transpose(dist)
        df3 = pd.DataFrame(data = dist, index = node2['SW_ID'], columns = node1['WH_ID'])
        df3.to_excel('Backend//Distance_Matrix_Leg1.xlsx', index=True)

        WKB = excelrd.open_workbook('Backend//Distance_Matrix_Leg1.xlsx')
        Sheet1 = WKB.sheet_by_index(0)
        FCI = pd.read_excel(USN, sheet_name='A.2 FCI', index_col=None)
        WH = pd.read_excel(USN, sheet_name='A.1 Warehouse', index_col=None)

        FCI['WH_District'] = FCI['WH_District'].apply(lambda x: x.replace(' ', ''))
        WH['SW_District'] = WH['SW_District'].apply(lambda x: x.replace(' ', ''))
        

        Warehouse_No = []
        FPS_No = []
        Warehouse_No = FCI['WH_ID'].nunique()
        FPS_No = WH['SW_ID'].nunique()
        Warehouse_Count = {}

        FPS_Count = {}
        Warehouse_Count['Warehouse_Count'] = Warehouse_No
        FPS_Count['FPS_Count'] = FPS_No  # No of FPS

        Total_Supply = []
        Total_Supply_Warehouse = {}
        Total_Supply = FCI['Storage_Capacity'].sum()
        Total_Supply_Warehouse['Total_Supply_Warehouse'] = Total_Supply  # Total SUPPLY

        Total_Demand = []
        Total_Demand_FPS = {}
        Total_Demand = WH['Demand'].sum()
        Total_Demand_FPS['Total_Demand_Warehouse'] = Total_Demand  # Total demand

        
        District_Capacity = {}
        for i in range(len(FCI['WH_District'])):
            District_Name = FCI['WH_District'][i]
            if District_Name not in District_Capacity:
                District_Capacity[District_Name] = FCI['Storage_Capacity'][i]
            else:
                District_Capacity[District_Name] = FCI['Storage_Capacity'][i] + District_Capacity[District_Name]

        District_Demand = {}
        for i in range(len(WH['SW_District'])):
            District_Name_FPS = WH['SW_District'][i]
            if District_Name_FPS not in District_Demand:
                District_Demand[District_Name_FPS] = WH['Demand'][i]
            else:
                District_Demand[District_Name_FPS] = WH['Demand'][i] + District_Demand[District_Name_FPS]
        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)
        
        model = LpProblem('Supply-Demand-Problem', LpMinimize)

        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)

        Variable1 = []
        Variable2 = []
        for i in range(len(FCI['WH_ID'])):
            for j in range(len(WH['SW_ID'])):
                Variable1.append(str(FCI['WH_ID'][i]) + '_'
                                 + str(FCI['WH_District'][i]) + '_'
                                 + str(WH['SW_ID'][j]) + '_'
                                 + str(WH['SW_District'][j]) + '_Wheat')

        # Variables for Wheat from lEVEL2 TO FPS

        DV_Variables1 = LpVariable.matrix('X', Variable1, cat='float',
                lowBound=0)
        Allocation1 = np.array(DV_Variables1).reshape(len(FCI['WH_ID']),
                len(WH['SW_ID']))

        
        
        District_Capacity = {}
        for i in range(len(FCI["WH_District"])):
            District_Name = FCI["WH_District"][i]
            if District_Name not in District_Capacity:
                District_Capacity[District_Name] = float(FCI["Storage_Capacity"][i])
            else:
                District_Capacity[District_Name] += float(FCI["Storage_Capacity"][i])
 
        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)

        District_Demand = {}
        for i in range(len(WH["SW_District"])):
            District_Name_FPS = WH["SW_District"][i]
            if District_Name_FPS not in District_Demand:
                District_Demand[District_Name_FPS] = float(WH["Demand"][i])
            else:
                District_Demand[District_Name_FPS] += float(WH["Demand"][i])
        District_Name = []
        District_Name2=[]
        District_Name = [i for i in District_Demand if i not in District_Capacity]
        District_Name4 = [i for i in District_Capacity if i not in District_Demand]
        District_Name2 = [i for i in District_Demand if i in District_Capacity and District_Demand[i] >= District_Capacity[i]]
        District_Name_1 = {}
        District_Name_1['District_Name_All'] = District_Name + District_Name2
        District_Name3 = [i for i in District_Demand if i in District_Capacity and District_Demand[i] <= District_Capacity[i]]
        
        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)        
        name1 = []
        lst1 = []
        for j in range(len(DV_Variables1)):
            name1 = str(DV_Variables1[j])
            lst1 = name1.split("_")
            if lst1[2] in District_Name3 and lst1[4] in District_Name3 and lst1[2]!=lst1[4]:
                model+=DV_Variables1[j]==0
                #print(DV_Variables1[j]==0)
                
        name2 = []
        lst2 = []
        for j in range(len(DV_Variables1)):
            name2 = str(DV_Variables1[j])
            lst2 = name2.split("_")
            if lst2[2] in District_Name2 and lst2[4] in District_Name3:
                model+=DV_Variables1[j]==0
                #print(DV_Variables1[j]==0)
                
        name3 = []
        lst3 = []
        for j in range(len(DV_Variables1)):
            name3 = str(DV_Variables1[j])
            lst3 = name3.split("_")
            if lst3[2] in District_Name2 and lst3[4] in District_Name2 and lst3[2]!=lst3[4]:
                model+=DV_Variables1[j]==0
                #print(DV_Variables1[j]==0)

        name4 = []
        lst4 = []
        for j in range(len(DV_Variables1)):
            name4 = str(DV_Variables1[j])
            lst4 = name4.split("_")
            if lst4[2] in District_Name4 and lst4[4] in District_Name3:
                model+=DV_Variables1[j]==0
                #print(DV_Variables1[j]==0)

        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)


        PC_Mill = []
        for col in range(Sheet1.nrows):
            if col==0:
                continue
            temp = []
            for row in range (Sheet1.ncols):
                if row==0:
                    continue
                temp.append(Sheet1.cell_value(col,row))
            PC_Mill.append(temp)

        FCI_WH = [[ PC_Mill[j][i] for j in range(len( PC_Mill))] for i in range(len( PC_Mill[0]))]

        allCombination1 = []

        for i in range(len(FCI_WH)):
            for j in range(len(WH['SW_ID'])):
                allCombination1.append(Allocation1[i][j] * FCI_WH[i][j])

        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)

        model += lpSum(allCombination1)

        # Demand Constraints for Wheat

        for i in range(len(WH['SW_ID'])):
            model += lpSum(Allocation1[j][i] for j in range(len(FCI['WH_ID'
                           ]))) >= WH['Demand'][i]

        # Supply Constraints for Warehouses

        for i in range(len(FCI['WH_ID'])):
            model += lpSum(Allocation1[i][j] for j in range(len(WH['SW_ID'
                           ]))) <= FCI['Storage_Capacity'][i]

       # Calling CBC_CMB Solver

        #model.solve(CPLEX_CMD(options=['set mip tolerances mipgap 0.01']))
        #model.prob.solve(CPLEX_CMD(options=["set mip tolerances mipgap 0.03","set emphasis memory y"]))
        #model.solve(CPLEX_CMD(options=['set mip tolerances mipgap 0.03',"set emphasis memory y"]))
        model.solve(PULP_CBC_CMD())
        
        status = LpStatus[model.status]
        if status == LpStatusInfeasible or status == LpStatusUnbounded or status == LpStatusNotSolved or status == LpStatusUndefined:
           print("Problem is infeasible or unbounded.")
           data = {}
           data['status'] = 0
           data['message'] = "Infeasible or Unbounded Solution"
           json_data = json.dumps(data)
           json_object = json.loads(json_data)
           return json.dumps(json_object, indent=1)
 
        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)

        #model.solve(PULP_CBC_CMD())
        
        
        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)
        

        data = {}
        #data['status'] = 1
        #data['modelStatus'] = Status
        #data['totalCost'] = float(round(model.objective.value(),1))
        #data['original'] = float(round(total, 2))
        #data['percentageReduction'] = float(round((total
                #- model.objective.value()) / total, 4) * 100)
        #data['Average_Distance'] = float(round(model.objective.value(), 2)) / Total_Demand
        #data['Demand'] = int(FPS['Allocation_Wheat'].sum())

        
        Output_File = open('Backend//Inter_District1_leg1.csv', 'w')
        for v in model.variables():
            if v.value() > 0:
                Output_File.write(v.name + '\t' + str(v.value()) + '\n')

        Output_File = open('Backend//Inter_District1_leg1.csv', 'w')
        for v in model.variables():
            if v.value() > 0:
                Output_File.write(v.name + '\t' + str(v.value()) + '\n')


        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)

        df9 = pd.read_csv('Backend//Inter_District1_leg1.csv',header=None)
        df9.columns = ['Tagging']
        df9[[
            'Var',
            'WH_ID',
            'W_D',
            'SW_ID',
            'SW_D',
            'commodity_Value',
            ]] = df9[df9.columns[0]].str.split('_', n=6, expand=True)
        del df9[df9.columns[0]]
        df9[['commodity', 'Values']] = df9['commodity_Value'
                ].str.split('\\t', n=1, expand=True)
        del df9['commodity_Value']
        df9 = df9.drop(np.where(df9['commodity'] == 'Wheat1')[0])
        df9.to_excel('Backend//Tagging_Sheet_Pre_leg1.xlsx', sheet_name='BG_FPS')
        df31 = pd.read_excel('Backend//Tagging_Sheet_Pre_leg1.xlsx')
        
        df4 = pd.merge(df31, FCI, on='WH_ID', how='inner')
        #df4 = pd.merge(df31, FCI, on='WH_ID', how='inner')
        df4 = df4[[
            'WH_ID',
            'WH_Name',
            'WH_District',
            'WH_Lat',
            'WH_Long',
            'SW_ID',
            'Values',
            ]]
        df4 = pd.merge(df4, WH, on='SW_ID', how='inner')
        df51 = df4[[
            'WH_ID',
            'WH_Name',
            'WH_District',
            'WH_Lat',
            'WH_Long',
            'SW_ID',
            'SW_Name',
            'SW_District',
            'SW_lat',
            'SW_Long',
            'Values',
            ]]
        df51.insert(0, 'Scenario', 'Optimized')
        df51.insert(1, 'From', 'Depot')
        df51.insert(2, 'From_State', 'Ladakh')
        df51.insert(7, 'To', 'FPS')
        df51.insert(8, 'To_State', 'Ladakh')
        df51.insert(9, 'commodity', 'Wheat')
        df51.rename(columns={
            'WH_ID': 'From_ID',
            'WH_Name': 'From_Name',
            'WH_Lat': 'From_Lat',
            'WH_Long': 'From_Long',
            }, inplace=True)
        df51.rename(columns={
            'SW_ID': 'To_ID',
            'SW_Name': 'To_Name',
            'SW_lat': 'To_Lat',
            'SW_Long': 'To_Long',
            'Values' :'quantity'
            }, inplace=True)
        df51.rename(columns={'WH_District': 'From_District',
                   'SW_District': 'To_District'}, inplace=True)
        df51 = df51.loc[:, [
            'Scenario',
            'From',
            'From_State',
            'From_District',
            'From_ID',
            'From_Name',
            'From_Lat',
            'From_Long',
            'To',
            'To_ID',
            'To_Name',
            'To_State',
            'To_District',
            'To_Lat',
            'To_Long',
            'commodity',
            'quantity',
            ]]
        
        df51.to_excel('Backend//Tagging_Sheet_Pre11_leg1.xlsx', sheet_name='BG_FPS1')
        
        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)       
        
        data1 = pd.ExcelFile("Backend//Tagging_Sheet_Pre11_leg1.xlsx")
        df5 = pd.read_excel(data1,sheet_name="BG_FPS1")

        Cost = pd.ExcelFile("Backend//Distance_Punjab_Haversine3.xlsx")
        BG_BG = pd.read_excel(Cost,sheet_name="BG_BG")
        
        Distance_BG_BG = {}
        column_list_BG_BG = list(BG_BG.columns)
        #print(column_list_BG_BG)
        row_list_BG_BG = list(BG_BG.iloc[:, 0])
        #print(row_list_BG_BG )  
        for ind in df5.index:
            from_code= df5['From_ID'][ind] 
            to_code = df5['To_ID'][ind]
            if to_code in row_list_BG_BG and from_code in column_list_BG_BG:
                index_i = row_list_BG_BG.index(to_code)
                index_j = column_list_BG_BG.index(from_code)
                key = str(to_code) + "_" + str(from_code)
                Distance_BG_BG[key]= BG_BG.iloc[index_i , index_j]
                

        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)            
        
        #df5["Tagging"]=df5['To_ID']+ '_' + df5['From_ID']
        df5["Tagging"] = df5['To_ID'].astype(str) + '_' + df5['From_ID'].astype(str)
        df5['Distance'] = df5['Tagging'].map(Distance_BG_BG)
        df5 = df5.replace('',pd.NaT).fillna('shallu')
        d5=df5.loc[df5['Distance'] == "shallu"]
        df5.to_excel('Backend//Result_Sheet12.xlsx',
                         sheet_name='Warehouse_FPS')

        # Result_Sheet1=pd.ExcelFile("Backend//Result_Sheet12.xlsx")
        # df6= pd.read_excel(Result_Sheet1,sheet_name="Warehouse_FPS")
        # df7=df6.loc[df6['Distance'] == "shallu"]
        # source3 = df7['From_ID']  # FCI is the source and FPS is the destination
        # destination3 = df7['To_ID']
        # BingMapsKey = "ApBZRxLaI7CNkPkE1E9mrJCh3l02nRLXax2m0s5ajUIAMdwuJWLN7oMWVtCHQYzr"  # Bing Map Key
        # df7["Warehouse_lat_long"]= df7['From_Lat'].astype(str) + ',' + df7['From_Long'].astype(str)
        # df7["FPS_lat_long"]= df7['To_Lat'].astype(str) + ',' + df7['To_Long'].astype(str)

        # #df8=df7["From_ID","To_ID","Warehouse_lat_long","FPS_lat_long"]
        # df8 = df7[['From_ID', 'To_ID', 'Warehouse_lat_long', 'FPS_lat_long']]
        # source3 = df8['From_ID']
        # destination3 = df8['To_ID']
        # dist3 = [0 for _ in range(len(destination3))]  # Transport matrix for FCI_FPS
        # BingMapsKey = "ApBZRxLaI7CNkPkE1E9mrJCh3l02nRLXax2m0s5ajUIAMdwuJWLN7oMWVtCHQYzr"  # Bing Map Key

        # dist3 = []  # Initialize an empty list for distances

        # for index, row in df8.iterrows():
        #     origin = row["Warehouse_lat_long"]
        #     dest = row["FPS_lat_long"]
        #     max_retries = 3
        #     retries = 0
        #     while retries < max_retries:
        #         try:
        #             response = requests.get(
        #                 "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins=" + origin + "&destinations=" + dest +
        #                 "&travelMode=driving&key=" + BingMapsKey)
        #             resp = response.json()

        #             # Append a new element to dist3 for the current index
        #             dist3.append(resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance'])

        #             # Display the output for each iteration
        #             #print(f"Origin: {origin}, Destination: {dest}, Distance: {dist3[-1]}")
        #             break  # Successful response, exit the retry loop
        #         except (requests.ConnectionError, requests.Timeout):
        #             retries += 1
        #             #print(f"Attempt {retries} failed. Retrying...")
        #             time.sleep(1)  # Wait for 1 second before retrying

        # #print("Final distances:", dist3)

        # if stop_process==True:
        #     data = {}
        #     data['status'] = 0
        #     data['message'] = "Process Stopped"
        #     json_data = json.dumps(data)
        #     json_object = json.loads(json_data)
        #     return json.dumps(json_object, indent=1)

        # df7["Distance"]=dist3
        # df7.drop(['Warehouse_lat_long', 'FPS_lat_long'], axis=1)
        # df9=df6.loc[df6['Distance'] != "shallu"]
        # df9 = df9.loc[:, [
        #         'Scenario',
        #         'From',
        #         'From_State',
        #         'From_District',
        #         'From_ID',
        #         'From_Name',
        #         'From_Lat',
        #         'From_Long',
        #         'To',
        #         'To_ID',
        #         'To_Name',
        #         'To_State',
        #         'To_District',
        #         'To_Lat',
        #         'To_Long',
        #         'commodity',
        #         'quantity',
        #          "Distance",]]
        # df7 = df7.loc[:, [
        #         'Scenario',
        #         'From',
        #         'From_State',
        #         'From_District',
        #         'From_ID',
        #         'From_Name',
        #         'From_Lat',
        #         'From_Long',
        #         'To',
        #         'To_ID',
        #         'To_Name',
        #         'To_State',
        #         'To_District',
        #         'To_Lat',
        #         'To_Long',
        #         'commodity',
        #         'quantity',
        #        "Distance"]]
        
        # #print(df9.head())  # Print the first few rows
        # #df10 = df9.append([df7], ignore_index=True)
        # df10 = pd.concat([df9, df7], ignore_index=True)
        # #df10 = df9.append([df7],ignore_index=True)
        # result = (df10['quantity'] * df10['Distance']).sum()
        # #print(result)

        # df10.to_excel('Backend//Result_Sheet_leg1.xlsx',
        #              sheet_name='FCI_Warehouse')

# ----------------------------------------------------------------------------------------------------------------------------------------------
        Result_Sheet1 = pd.ExcelFile("Backend//Result_Sheet12.xlsx")
        df6 = pd.read_excel(Result_Sheet1, sheet_name="Warehouse_FPS")
        Result_Sheet1.close()

        df7 = df6.loc[df6['Distance'] == "shallu"]

        auth_url = 'https://kerala.pmgatishakti.gov.in/PMGatishaktiApiService/authenticate'
        distance_url = 'https://kerala.pmgatishakti.gov.in/PMGatishaktiApiService/dfpdapi/roaddistance'

        auth_payload = {
            "username": "DFPD_C",
            "password": "W9Vtb8WKkt3"
        }

        FILE_PATH = 'distanceIndent.json'

        def get_token():
            response = requests.post(auth_url, json=auth_payload)
            if response.status_code == 200:
                token = response.json()['token']
                if token : 
                    return token
                else: 
                    return False
            else:
                return False

        response_data = []


        def process_batch(df_batch):
            token = get_token()    
            time.sleep(15)
            headers = {
                'Authorization': f'Bearer {token}',
            }

            data = {
                "parameter": [{
                    "src_lng": row["From_Long"],
                    "src_lat": row["From_Lat"],
                    "dest_lng": row["To_Long"],
                    "dest_lat": row["To_Lat"]
                } for _, row in df_batch.iterrows()]
            }
            
            with open(FILE_PATH, 'w') as f:
                json.dump(data, f, indent=4)

            with open(FILE_PATH, 'rb') as f:
                files = {'LatsLongsFile': f}

                response = requests.post(distance_url, headers=headers, files=files)
                
                return response

        def process_single(row):
            token = get_token()
            time.sleep(2)
            headers = {
                'Authorization': f'Bearer {token}',
            }

            data = {
                "parameter": [{
                    "src_lng": row["From_Long"],
                    "src_lat": row["From_Lat"],
                    "dest_lng": row["To_Long"],
                    "dest_lat": row["To_Lat"]
                }]
            }
            
            with open(FILE_PATH, 'w') as f:
                json.dump(data, f, indent=4)

            with open(FILE_PATH, 'rb') as f:
                files = {'LatsLongsFile': f}

                response = requests.post(distance_url, headers=headers, files=files)
                return response
                    
        batch_size = 30
        total_rows = len(df7)
        num_batches = (total_rows + batch_size - 1) // batch_size
                    
        dist3 = []

        for batch_num in range(num_batches):
            start_idx = batch_num * batch_size
            end_idx = min((batch_num + 1) * batch_size, total_rows)
            df_batch = df7.iloc[start_idx:end_idx]
            
            response = process_batch(df_batch)
            if response.status_code == 200:
                response_json = response.json()
                if 'data' in response_json and all('distance' in row_data for row_data in response_json['data']):
                    for row_data, (_, row) in zip(response_json['data'], df_batch.iterrows()):
                        distance = row_data['distance']
                        dist3.append(distance)
                else : 
                    source3 = df_batch['From_ID']  
                    destination3 = df_batch['To_ID']
                    BingMapsKey = "AirsAsdRojcMdt_sHviub_uETiraB-on6DR3Q_AVyrOCedF0FkITbpVVSDKx6hjT"  # Bing Map Key
                    df_batch["Warehouse_lat_long"]= df7['From_Lat'].astype(str) + ',' + df7['From_Long'].astype(str)
                    df_batch["FPS_lat_long"]= df7['To_Lat'].astype(str) + ',' + df7['To_Long'].astype(str)

                    df8 = df_batch[['From_ID', 'To_ID', 'Warehouse_lat_long', 'FPS_lat_long']]
                    source3 = df8['From_ID']
                    destination3 = df8['To_ID']

                    for index, row in df8.iterrows():
                        origin = row["Warehouse_lat_long"]
                        dest = row["FPS_lat_long"]
                        max_retries = 3
                        retries = 0
                        while retries < max_retries:
                            try:
                                response = requests.get(
                                    "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins=" + origin + "&destinations=" + dest +
                                    "&travelMode=driving&key=" + BingMapsKey)
                                resp = response.json()
                                dist3.append(resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance'])
                                break  
                            except (requests.ConnectionError, requests.Timeout):
                                retries += 1
                                time.sleep(1) 
            else: 
                source3 = df_batch['From_ID']  
                destination3 = df_batch['To_ID']
                BingMapsKey = "AirsAsdRojcMdt_sHviub_uETiraB-on6DR3Q_AVyrOCedF0FkITbpVVSDKx6hjT"  # Bing Map Key
                df_batch["Warehouse_lat_long"]= df7['From_Lat'].astype(str) + ',' + df7['From_Long'].astype(str)
                df_batch["FPS_lat_long"]= df7['To_Lat'].astype(str) + ',' + df7['To_Long'].astype(str)

                df8 = df_batch[['From_ID', 'To_ID', 'Warehouse_lat_long', 'FPS_lat_long']]
                source3 = df8['From_ID']
                destination3 = df8['To_ID']

                for index, row in df8.iterrows():
                    origin = row["Warehouse_lat_long"]
                    dest = row["FPS_lat_long"]
                    max_retries = 3
                    retries = 0
                    while retries < max_retries:
                        try:
                            response = requests.get(
                                "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins=" + origin + "&destinations=" + dest +
                                "&travelMode=driving&key=" + BingMapsKey)
                            resp = response.json()

                            dist3.append(resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance'])
                            break 
                        except (requests.ConnectionError, requests.Timeout):
                            retries += 1
                            time.sleep(1)

        df7["Distance"]=dist3
        df9=df6.loc[df6['Distance'] != "shallu"]
        df9 = df9.loc[:, [
                'Scenario',
                'From',
                'From_State',
                'From_District',
                'From_ID',
                'From_Name',
                'From_Lat',
                'From_Long',
                'To',
                'To_ID',
                'To_Name',
                'To_State',
                'To_District',
                'To_Lat',
                'To_Long',
                'commodity',
                'quantity',
                'Distance']]
        df7 = df7.loc[:, [
                'Scenario',
                'From',
                'From_State',
                'From_District',
                'From_ID',
                'From_Name',
                'From_Lat',
                'From_Long',
                'To',
                'To_ID',
                'To_Name',
                'To_State',
                'To_District',
                'To_Lat',
                'To_Long',
                'commodity',
                'quantity',
                'Distance']]

        df10 = pd.concat([df9, df7], ignore_index=True)
        result = ((df10['quantity']) * df10['Distance']).sum()

        df10.to_excel('Backend//Result_Sheet_leg1.xlsx', sheet_name='FCI_Warehouse', index=False)
# ---------------------------------------------------------------------------------------------------------------------------------------------
        
        data["Scenario"]="Inter"
        data["Scenario_Baseline"] = "Baseline"
        
        data["WH_Used"] = df5['From_ID'].nunique()
        data["WH_Used_Baseline"] = "272"
        
        data["FPS_Used"] = df5['To_ID'].nunique()
        data["FPS_Used_Baseline"] = "17,781"
        
        data['Demand'] = float(WH['Demand'].sum())
        data['Demand_Baseline'] = float(WH['Demand'].sum())
        
        data['Total_QKM'] = float(result)
        data['Total_QKM_Baseline'] = "9,15,29,852"
        
        data['Average_Distance'] = float(round(result, 2)) / Total_Demand
        data['Average_Distance_Baseline'] = "10.52"

        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)                     

        save_to_database_leg1(month, year, applicable)
        save_monthly_data_leg1(month, year, float(result))
        
        
        json_data = json.dumps(data)
        json_object = json.loads(json_data)

        if os.path.exists('ouputPickle.pkl'):
            os.remove('ouputPickle.pkl')

        # open pickle file
        dbfile1 = open('ouputPickle.pkl', 'ab')
        
    else:
        message = 'DataFile file is incorrect'
        try:
            USN = pd.ExcelFile('Backend//Data_2.xlsx')
            month = request.form.get('month')        
            year = request.form.get('year')
            scenario_type = request.form.get('type')
            applicable = request.form.get('applicable')
        except Exception as e:
            data = {}
            data['status'] = 0
            data['message'] = message
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)
        input = pd.ExcelFile('Backend//Data_2.xlsx')
        node1 = pd.read_excel(input,sheet_name="A.2 FCI")
        node2 = pd.read_excel(input,sheet_name="A.1 Warehouse")

        dist = [[0 for a in range(len(node2["SW_ID"]))] for b in range(len(node1["WH_ID"]))]
        phi_1 = []
        phi_2 = []
        delta_phi = []
        delta_lambda = []
        R = 6371 

        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)

        for i in node1.index:
            for j in node2.index:
                phi_1=math.radians(node1["WH_Lat"][i])
                phi_2=math.radians(node2["SW_lat"][j])
                delta_phi=math.radians(node2["SW_lat"][j]-node1["WH_Lat"][i])
                delta_lambda=math.radians(node2["SW_Long"][j]-node1["WH_Long"][i])
                x=math.sin(delta_phi / 2.0) ** 2 + math.cos(phi_1) * math.cos(phi_2) * math.sin(delta_lambda / 2.0) ** 2
                y=2 * math.atan2(math.sqrt(x), math.sqrt(1 - x))
                dist[i][j]=R*y
                
        dist=np.transpose(dist)
        df3 = pd.DataFrame(data = dist, index = node2['SW_ID'], columns = node1['WH_ID'])
        df3.to_excel('Backend//Distance_Matrix_Leg1.xlsx', index=True)

        WKB = excelrd.open_workbook('Backend//Distance_Matrix_Leg1.xlsx')
        Sheet1 = WKB.sheet_by_index(0)
        USN = pd.ExcelFile('Backend//Data_2.xlsx')
        FCI = pd.read_excel(USN, sheet_name='A.2 FCI', index_col=None)
        WH = pd.read_excel(USN, sheet_name='A.1 Warehouse', index_col=None)

        

        Total_Demand = []
        Total_Demand_FPS = {}
        Total_Demand = WH['Allocation_Wheat'].sum() +  WH['Allocation_Rice'].sum() + WH['Allocation_FRice'].sum()
        Total_Demand_FPS['Total_Demand_Warehouse'] = Total_Demand  # Total demand

        
        
        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)

        
        model = LpProblem('Supply-Demand-Problem', LpMinimize)

        Variable4 = []
        for i in range(len(FCI['WH_ID'])):
            for j in range(len(WH['SW_ID'])):
                Variable4.append(str(FCI['WH_ID'][i]) + '_'
                                 + str(FCI['WH_District'][i]) + '_'
                                 + str(WH['SW_ID'][j]) + '_'
                                 + str(WH['SW_District'][j]) + '_Wheat')
                                 
        Variable5 = []
        for i in range(len(FCI['WH_ID'])):
            for j in range(len(WH['SW_ID'])):
                Variable5.append(str(FCI['WH_ID'][i]) + '_'
                                 + str(FCI['WH_District'][i]) + '_'
                                 + str(WH['SW_ID'][j]) + '_'
                                 + str(WH['SW_District'][j]) + '_Rice')
                                 
        Variable6 = []
        for i in range(len(FCI['WH_ID'])):
            for j in range(len(WH['SW_ID'])):
                Variable6.append(str(FCI['WH_ID'][i]) + '_'
                                 + str(FCI['WH_District'][i]) + '_'
                                 + str(WH['SW_ID'][j]) + '_'
                                 + str(WH['SW_District'][j]) + '_FRice')

        # Variables for Wheat from lEVEL2 TO FPS

        DV_Variables4 = LpVariable.matrix('X', Variable4, cat='float',
                lowBound=0)
        Allocation4 = np.array(DV_Variables4).reshape(len(FCI['WH_ID']),
                len(WH['SW_ID']))
                
        
                
        DV_Variables5 = LpVariable.matrix('Y', Variable5, cat='float',
                lowBound=0)
        Allocation5 = np.array(DV_Variables5).reshape(len(FCI['WH_ID']),
                len(WH['SW_ID']))
                
       
         
        DV_Variables6 = LpVariable.matrix('Y', Variable6, cat='float',
                lowBound=0)
        Allocation6 = np.array(DV_Variables6).reshape(len(FCI['WH_ID']),
                len(WH['SW_ID']))
        
        
        
        

        PC_Mill = []
        for col in range(Sheet1.nrows):
            if col==0:
                continue
            temp = []
            for row in range (Sheet1.ncols):
                if row==0:
                    continue
                temp.append(Sheet1.cell_value(col,row))
            PC_Mill.append(temp)

        FCI_Warehouse = [[ PC_Mill[j][i] for j in range(len( PC_Mill))] for i in range(len( PC_Mill[0]))]

        allCombination4 = []
        allCombination5 = []
        allCombination6 = []

        for i in range(len(FCI_Warehouse)):
            for j in range(len(WH['SW_ID'])):
                allCombination4.append(Allocation4[i][j] * FCI_Warehouse[i][j])
                
        for i in range(len(FCI_Warehouse)):
            for j in range(len(WH['SW_ID'])):
                allCombination5.append(Allocation5[i][j] * FCI_Warehouse[i][j])
        
        for i in range(len(FCI_Warehouse)):
            for j in range(len(WH['SW_ID'])):
                allCombination6.append(Allocation6[i][j] * FCI_Warehouse[i][j])
                
                

        model += lpSum(allCombination4 + allCombination5 + allCombination6)
        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)

        # Demand Constraints for Wheat

        for i in range(len(WH['SW_ID'])):
            model += (lpSum(Allocation4[j][i] for j in range(len(FCI['WH_ID'
                           ]))) >= WH['Demand_Wheat'][i]*1.495)            
            model += (lpSum(Allocation4[j][i] for j in range(len(FCI['WH_ID'
                           ]))) <= WH['Demand_Wheat'][i]**1.495)
                                          
        for i in range(len(FCI['WH_ID'])):
            model += ((lpSum(Allocation4[i][j] for j in range(len(WH['SW_ID'
                           ]))))  <= FCI['Allotment_Wheat'][i])                                  
                           
        for i in range(len(WH['SW_ID'])):
            model += (lpSum(Allocation5[j][i] for j in range(len(FCI['WH_ID'
                           ]))) >= WH['Demand_Rice'][i])
        
        for i in range(len(WH['SW_ID'])):
            model += (lpSum(Allocation5[j][i] for j in range(len(FCI['WH_ID'
                           ]))) <= WH['Demand_Rice'][i])
                                   
        for i in range(len(FCI['WH_ID'])):
            model += ((lpSum(Allocation5[i][j] for j in range(len(WH['SW_ID'
                           ]))))  <= FCI['Allotment_Rice'][i]                  
       
        for i in range(len(WH['SW_ID'])):
            model += (lpSum(Allocation6[j][i] for j in range(len(FCI['WH_ID'
                           ]))) >= WH['Demand_FRice'][i])
                           
        for i in range(len(WH['SW_ID'])):
            model += (lpSum(Allocation6[j][i] for j in range(len(FCI['WH_ID'
                           ]))) <= WH['Demand_FRice'][i])                   
        # Supply Constraints for Warehouses

        for i in range(len(FCI['WH_ID'])):
            model += ((lpSum(Allocation6[i][j] for j in range(len(WH['SW_ID'
                           ]))))  <= FCI['Allotment_FRice'][i])

       # Calling CBC_CMB Solver
        

        
        model.solve(CPLEX_CMD(options=['set mip tolerances mipgap 0.03',"set emphasis memory y"]))
       
       
        status = LpStatus[model.status]
        #print(status)
        #print("Total distance:", model.objective.value())
        if status == LpStatusInfeasible or status == LpStatusUnbounded or status == LpStatusNotSolved or status == LpStatusUndefined:
           print("Problem is infeasible or unbounded.")
           data = {}
           data['status'] = 0
           data['message'] = "Infeasible or Unbounded Solution"
           json_data = json.dumps(data)
           json_object = json.loads(json_data)
           return json.dumps(json_object, indent=1)

        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)

        #model.solve(PULP_CBC_CMD())
        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)        
        
        #print("Anmol")

        Original_Cost = 100000000
        total = Original_Cost

        data = {}
        #data['status'] = 1
        #data['modelStatus'] = Status
        #data['totalCost'] = float(round(model.objective.value(),1))
        #data['original'] = float(round(total, 2))
        #data['percentageReduction'] = float(round((total
                #- model.objective.value()) / total, 4) * 100)
        #data['Average_Distance'] = float(round(model.objective.value(), 2)) / Total_Demand
        #data['Demand'] = int(FPS['Allocation_Wheat'].sum())

        
        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)

        Output_File = open('Backend//Inter_District1_leg1.csv', 'w')
        for v in model.variables():
            if v.value() > 0:
                Output_File.write(v.name + '\t' + str(v.value()) + '\n')

        Output_File = open('Backend//Inter_District1_leg1.csv', 'w')
        for v in model.variables():
            if v.value() > 0:
                Output_File.write(v.name + '\t' + str(v.value()) + '\n')

        df9 = pd.read_csv('Backend//Inter_District1_leg1.csv',header=None)

        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)        

        df9.columns = ['Tagging']
        df9[[
            'Var',
            'WH_ID',
            'W_D',
            'SW_ID',
            'SW_D',
            'commodity_Value',
            ]] = df9[df9.columns[0]].str.split('_', n=6, expand=True)
        del df9[df9.columns[0]]
        df9[['commodity', 'Values']] = df9['commodity_Value'
                ].str.split('\\t', n=1, expand=True)
        del df9['commodity_Value']
        df9 = df9.drop(np.where(df9['commodity'] == 'Wheat1')[0])
        def convert_to_numeric(value):
            try:
                return pd.to_numeric(value)
            except ValueError:
                return value
        
        
        df9['WH_ID'] = df9['WH_ID'].apply(convert_to_numeric)
        df9['SW_ID'] = df9['SW_ID'].apply(convert_to_numeric)
        
        df9.to_excel('Backend//Tagging_Sheet_Pre_leg1.xlsx', sheet_name='BG_FPS')
        df31 = pd.read_excel('Backend//Tagging_Sheet_Pre_leg1.xlsx')
        df31['WH_ID'] = df31['WH_ID'].astype(str) 
        USN = pd.ExcelFile('Backend//Data_2.xlsx')
        WH = pd.read_excel(USN, sheet_name='A.1 Warehouse', index_col=None)
        FCI = pd.read_excel(USN, sheet_name='A.2 FCI', index_col=None)        # Convert to object type, adjust as needed
        

        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)

        df4 = pd.merge(df31, FCI, on='WH_ID', how='inner')
        #df4 = pd.merge(df31, FCI, on='WH_ID', how='inner')
        df4 = df4[[
            'WH_ID',
            'WH_Name',
            'WH_District',
            'WH_Lat',
            'WH_Long',
            'SW_ID',
            'commodity',
            'Values',
            ]]
        df4 = pd.merge(df4, WH, on='SW_ID', how='inner')
        df51 = df4[[
            'WH_ID',
            'WH_Name',
            'WH_District',
            'WH_Lat',
            'WH_Long',
            'SW_ID',
            'SW_Name',
            'SW_District',
            'SW_lat',
            'SW_Long',
            "SW_Type",
            'commodity',
            'Values',
            ]]
        df51.insert(0, 'Scenario', 'Optimized')
        df51.insert(1, 'From', 'FCI')
        df51.insert(2, 'From_State', 'Ladakh')
        df51.insert(8, 'To_State', 'Ladakh')
        
        df51.rename(columns={
            'WH_ID': 'From_ID',
            'WH_Name': 'From_Name',
            'WH_Lat': 'From_Lat',
            'WH_Long': 'From_Long',
             'SW_Type': 'To',
            }, inplace=True)
        df51.rename(columns={
            'SW_ID': 'To_ID',
            'SW_Name': 'To_Name',
            'SW_lat': 'To_Lat',
            'SW_Long': 'To_Long',
            'Values':'quantity',
            }, inplace=True)
        df51.rename(columns={'WH_District': 'From_District',
                   'SW_District': 'To_District'}, inplace=True)
        df51 = df51.loc[:, [
            'Scenario',
            'From',
            'From_State',
            'From_District',
            'From_ID',
            'From_Name',
            'From_Lat',
            'From_Long',
            'To',
            'To_ID',
            'To_Name',
            'To_State',
            'To_District',
            'To_Lat',
            'To_Long',
            'commodity',
            'quantity',
            ]]
        
        
        
        def convert_to_numeric(value):
            try:
                return pd.to_numeric(value)
            except ValueError:
                return value
                
        
        df51['From_ID'] = df51['From_ID'].apply(convert_to_numeric)
        df51['To_ID'] = df51['To_ID'].apply(convert_to_numeric) 
        
        df51.to_excel('Backend//Tagging_Sheet_Pre11_leg1.xlsx', sheet_name='BG_FPS1')
        data1 = pd.ExcelFile("Backend//Tagging_Sheet_Pre11_leg1.xlsx")
        df5 = pd.read_excel(data1,sheet_name="BG_FPS1")
        
        input = pd.ExcelFile('Backend//Data_2.xlsx')
        node1 = pd.read_excel(input,sheet_name="A.1 Warehouse")
        node1["concatenate"]= node1['SW_lat'].astype(str) + ',' + node1['SW_Long'].astype(str)
        
        node2 = pd.read_excel(input,sheet_name="A.2 FCI")
        node2["concatenate1"]= node2['WH_Lat'].astype(str) + ',' + node2['WH_Long'].astype(str)
        Distance = pd.ExcelFile('Backend//Distance_Intial_L1.xlsx')
        DistanceBing = pd.read_excel(Distance,sheet_name="BG_BG")
        Warehouse = pd.read_excel(Distance,sheet_name="Warehouse")
        FCI = pd.read_excel(Distance,sheet_name="FCI")
        node1 = node1[['SW_ID', 'SW_lat', 'SW_Long','concatenate']]
        War = pd.merge(node1, Warehouse, on='SW_ID')
        df1_w = War[War['concatenate'] != War['Lat_Long']]
        Warehouse_ID = df1_w['SW_ID'].unique()
        node2 = node2[['WH_ID', 'WH_Lat', 'WH_Long','concatenate1']]
        node2['WH_ID'] = node2['WH_ID'].astype(str)
        FCI['WH_ID'] = FCI['WH_ID'].astype(str)
        FPS1 = pd.merge(node2, FCI, on='WH_ID')
        df1_f = FPS1[FPS1['concatenate1'] != FPS1['Lat_Long']]
        FPS_ID = df1_f['WH_ID'].unique()
        BG_BG = pd.read_excel(Distance,sheet_name="BG_BG")
        Distance1 = BG_BG.drop(columns=BG_BG.columns[BG_BG.columns.isin(Warehouse_ID)])
        #print(Distance1)
        Distance2 =Distance1.T
        Distance3 = Distance2.drop(columns=Distance2.columns[Distance2.columns.isin(FPS_ID)])
        Distance3 = Distance3.T
        
        
        with pd.ExcelWriter('Backend//tamilnadu_Distance_L1.xlsx') as writer:
            Distance3.to_excel(writer, sheet_name='BG_BG',index=False)


            
        
        data1 = pd.ExcelFile("Backend//Tagging_Sheet_Pre11_Leg1.xlsx")
        df5 = pd.read_excel(data1,sheet_name="BG_FPS1")

        Cost = pd.ExcelFile("Backend//tamilnadu_Distance_L1.xlsx")
        BG_BG = pd.read_excel(Cost,sheet_name="BG_BG")
        
        Distance_BG_BG = {}
        column_list_BG_BG = list(BG_BG.columns.astype(str))
        row_list_BG_BG = list(BG_BG.iloc[:, 0].astype(str))

        for ind in df5.index:
            from_code = df5['From_ID'][ind]
            to_code = df5['To_ID'][ind]
            from_code_str = str(from_code)
            to_code_str = str(to_code)
            
            if to_code_str in row_list_BG_BG and from_code_str in column_list_BG_BG:
                index_i = row_list_BG_BG.index(to_code_str)
                index_j = column_list_BG_BG.index(from_code_str)
                key = to_code_str + "_" + from_code_str
                Distance_BG_BG[key] = BG_BG.iloc[index_i, index_j] 
                
                
        df5["Tagging"] = df5['To_ID'].astype(str) + '_' + df5['From_ID'].astype(str)
        df5['Distance'] = df5['Tagging'].map(Distance_BG_BG)
        df5.fillna('shallu', inplace=True)
        df5.to_excel('Backend//Result_Sheet12_Leg1.xlsx', sheet_name='Warehouse_FPS', index=False)        

        # Result_Sheet1=pd.ExcelFile("Backend//Result_Sheet12_Leg1.xlsx")
        # df6= pd.read_excel(Result_Sheet1,sheet_name="Warehouse_FPS")
        # df7=df6.loc[df6['Distance'] == "shallu"]
        # source3 = df7['From_ID']  # FCI is the source and FPS is the destination
        # destination3 = df7['To_ID']
        # BingMapsKey = "AuHc2b0CF6cE1h8QmWxYRPNRNwFuy4Rh5ziu6dDsi5gxnPoQHy0FBKO5Q-RMAIau"  # Bing Map Key
        # df7["Warehouse_lat_long"]= df7['From_Lat'].astype(str) + ',' + df7['From_Long'].astype(str)
        # df7["FPS_lat_long"]= df7['To_Lat'].astype(str) + ',' + df7['To_Long'].astype(str)

        # #df8=df7["From_ID","To_ID","Warehouse_lat_long","FPS_lat_long"]
        # df8 = df7[['From_ID', 'To_ID', 'Warehouse_lat_long', 'FPS_lat_long']]
        # source3 = df8['From_ID']
        # destination3 = df8['To_ID']
        # dist3 = [0 for _ in range(len(destination3))]  # Transport matrix for FCI_FPS
        # BingMapsKey = "AuHc2b0CF6cE1h8QmWxYRPNRNwFuy4Rh5ziu6dDsi5gxnPoQHy0FBKO5Q-RMAIau"  # Bing Map Key

        # dist3 = []  # Initialize an empty list for distances

        # if stop_process==True:
        #     data = {}
        #     data['status'] = 0
        #     data['message'] = "Process Stopped"
        #     json_data = json.dumps(data)
        #     json_object = json.loads(json_data)
        #     return json.dumps(json_object, indent=1)

        # for index, row in df8.iterrows():
        #     origin = row["Warehouse_lat_long"]
        #     dest = row["FPS_lat_long"]
        #     max_retries = 3
        #     retries = 0
        #     while retries < max_retries:
        #         try:
        #             response = requests.get(
        #                 "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins=" + origin + "&destinations=" + dest +
        #                 "&travelMode=driving&key=" + BingMapsKey)
        #             resp = response.json()

        #             # Append a new element to dist3 for the current index
        #             dist3.append(resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance'])

        #             # Display the output for each iteration
        #             #print(f"Origin: {origin}, Destination: {dest}, Distance: {dist3[-1]}")
        #             break  # Successful response, exit the retry loop
        #         except (requests.ConnectionError, requests.Timeout):
        #             retries += 1
        #             #print(f"Attempt {retries} failed. Retrying...")
        #             time.sleep(1)  # Wait for 1 second before retrying

        # #print("Final distances:", dist3)

        # if stop_process==True:
        #     data = {}
        #     data['status'] = 0
        #     data['message'] = "Process Stopped"
        #     json_data = json.dumps(data)
        #     json_object = json.loads(json_data)
        #     return json.dumps(json_object, indent=1)

        # df7["Distance"]=dist3
        # df7.drop(['Warehouse_lat_long', 'FPS_lat_long'], axis=1)
        # df9=df6.loc[df6['Distance'] != "shallu"]
        # df9 = df9.loc[:, [
        #         'Scenario',
        #         'From',
        #         'From_State',
        #         'From_District',
        #         'From_ID',
        #         'From_Name',
        #         'From_Lat',
        #         'From_Long',
        #         'To',
        #         'To_ID',
        #         'To_Name',
        #         'To_State',
        #         'To_District',
        #         'To_Lat',
        #         'To_Long',
        #         'commodity',
        #         'quantity',
        #          "Distance",]]
        # df7 = df7.loc[:, [
        #         'Scenario',
        #         'From',
        #         'From_State',
        #         'From_District',
        #         'From_ID',
        #         'From_Name',
        #         'From_Lat',
        #         'From_Long',
        #         'To',
        #         'To_ID',
        #         'To_Name',
        #         'To_State',
        #         'To_District',
        #         'To_Lat',
        #         'To_Long',
        #         'commodity',
        #         'quantity',
        #        "Distance"]]
        
        # #print(df9.head())  # Print the first few rows
        # #df10 = df9.append([df7], ignore_index=True)
        # df10 = pd.concat([df9, df7], ignore_index=True)
        # #df10 = df9.append([df7],ignore_index=True)
        # result = (df10['quantity'] * df10['Distance']).sum()
        # #print(result)

        # if stop_process==True:
        #     data = {}
        #     data['status'] = 0
        #     data['message'] = "Process Stopped"
        #     json_data = json.dumps(data)
        #     json_object = json.loads(json_data)
        #     return json.dumps(json_object, indent=1)

        # df10.to_excel('Backend//Result_Sheet_leg1.xlsx',
        #              sheet_name='Warehouse_FPS')

# ----------------------------------------------------------------------------------------------------------------------------------------------
        Result_Sheet1 = pd.ExcelFile("Backend//Result_Sheet12_Leg1.xlsx")
        df6 = pd.read_excel(Result_Sheet1, sheet_name="Warehouse_FPS")
        Result_Sheet1.close()

        df7 = df6.loc[df6['Distance'] == "shallu"]

        auth_url = 'https://kerala.pmgatishakti.gov.in/PMGatishaktiApiService/authenticate'
        distance_url = 'https://kerala.pmgatishakti.gov.in/PMGatishaktiApiService/dfpdapi/roaddistance'
        
        auth_payload = {
            "username": "DFPD_C",
            "password": "W9Vtb8WKkt3"
        }

        FILE_PATH = 'distanceIndent.json'

        def get_token():
            response = requests.post(auth_url, json=auth_payload)
            if response.status_code == 200:
                token = response.json()['token']
                if token : 
                    return token
                else: 
                    return False
            else:
                return False

        response_data = []


        def process_batch(df_batch):
            token = get_token()    
            time.sleep(15)
            headers = {
                'Authorization': f'Bearer {token}',
            }

            data = {
                "parameter": [{
                    "src_lng": row["From_Long"],
                    "src_lat": row["From_Lat"],
                    "dest_lng": row["To_Long"],
                    "dest_lat": row["To_Lat"]
                } for _, row in df_batch.iterrows()]
            }
            
            with open(FILE_PATH, 'w') as f:
                json.dump(data, f, indent=4)

            with open(FILE_PATH, 'rb') as f:
                files = {'LatsLongsFile': f}

                response = requests.post(distance_url, headers=headers, files=files)
                
                return response

        def process_single(row):
            token = get_token()
            time.sleep(2)
            headers = {
                'Authorization': f'Bearer {token}',
            }

            data = {
                "parameter": [{
                    "src_lng": row["From_Long"],
                    "src_lat": row["From_Lat"],
                    "dest_lng": row["To_Long"],
                    "dest_lat": row["To_Lat"]
                }]
            }
            
            with open(FILE_PATH, 'w') as f:
                json.dump(data, f, indent=4)

            with open(FILE_PATH, 'rb') as f:
                files = {'LatsLongsFile': f}

                response = requests.post(distance_url, headers=headers, files=files)
                return response
                    
        batch_size = 30
        total_rows = len(df7)
        num_batches = (total_rows + batch_size - 1) // batch_size
                    
        dist3 = []

        for batch_num in range(num_batches):
            start_idx = batch_num * batch_size
            end_idx = min((batch_num + 1) * batch_size, total_rows)
            df_batch = df7.iloc[start_idx:end_idx]
            
            response = process_batch(df_batch)
            if response.status_code == 200:
                response_json = response.json()
                if 'data' in response_json and all('distance' in row_data for row_data in response_json['data']):
                    for row_data, (_, row) in zip(response_json['data'], df_batch.iterrows()):
                        distance = row_data['distance']
                        dist3.append(distance)
                else : 
                    source3 = df_batch['From_ID']  
                    destination3 = df_batch['To_ID']
                    BingMapsKey = "AirsAsdRojcMdt_sHviub_uETiraB-on6DR3Q_AVyrOCedF0FkITbpVVSDKx6hjT"  # Bing Map Key
                    df_batch["Warehouse_lat_long"]= df7['From_Lat'].astype(str) + ',' + df7['From_Long'].astype(str)
                    df_batch["FPS_lat_long"]= df7['To_Lat'].astype(str) + ',' + df7['To_Long'].astype(str)

                    df8 = df_batch[['From_ID', 'To_ID', 'Warehouse_lat_long', 'FPS_lat_long']]
                    source3 = df8['From_ID']
                    destination3 = df8['To_ID']

                    for index, row in df8.iterrows():
                        origin = row["Warehouse_lat_long"]
                        dest = row["FPS_lat_long"]
                        max_retries = 3
                        retries = 0
                        while retries < max_retries:
                            try:
                                response = requests.get(
                                    "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins=" + origin + "&destinations=" + dest +
                                    "&travelMode=driving&key=" + BingMapsKey)
                                resp = response.json()
                                dist3.append(resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance'])
                                break  
                            except (requests.ConnectionError, requests.Timeout):
                                retries += 1
                                time.sleep(1) 
            else: 
                source3 = df_batch['From_ID']  
                destination3 = df_batch['To_ID']
                BingMapsKey = "AirsAsdRojcMdt_sHviub_uETiraB-on6DR3Q_AVyrOCedF0FkITbpVVSDKx6hjT"  # Bing Map Key
                df_batch["Warehouse_lat_long"]= df7['From_Lat'].astype(str) + ',' + df7['From_Long'].astype(str)
                df_batch["FPS_lat_long"]= df7['To_Lat'].astype(str) + ',' + df7['To_Long'].astype(str)

                df8 = df_batch[['From_ID', 'To_ID', 'Warehouse_lat_long', 'FPS_lat_long']]
                source3 = df8['From_ID']
                destination3 = df8['To_ID']

                for index, row in df8.iterrows():
                    origin = row["Warehouse_lat_long"]
                    dest = row["FPS_lat_long"]
                    max_retries = 3
                    retries = 0
                    while retries < max_retries:
                        try:
                            response = requests.get(
                                "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins=" + origin + "&destinations=" + dest +
                                "&travelMode=driving&key=" + BingMapsKey)
                            resp = response.json()

                            dist3.append(resp['resourceSets'][0]['resources'][0]['results'][0]['travelDistance'])
                            break 
                        except (requests.ConnectionError, requests.Timeout):
                            retries += 1
                            time.sleep(1)

        df7["Distance"]=dist3
        df9=df6.loc[df6['Distance'] != "shallu"]
        df9 = df9.loc[:, [
                'Scenario',
                'From',
                'From_State',
                'From_District',
                'From_ID',
                'From_Name',
                'From_Lat',
                'From_Long',
                'To',
                'To_ID',
                'To_Name',
                'To_State',
                'To_District',
                'To_Lat',
                'To_Long',
                'commodity',
                'quantity',
                'Distance']]
        df7 = df7.loc[:, [
                'Scenario',
                'From',
                'From_State',
                'From_District',
                'From_ID',
                'From_Name',
                'From_Lat',
                'From_Long',
                'To',
                'To_ID',
                'To_Name',
                'To_State',
                'To_District',
                'To_Lat',
                'To_Long',
                'commodity',
                'quantity',
                'Distance']]

        df10 = pd.concat([df9, df7], ignore_index=True)
        result = ((df10['quantity']) * df10['Distance']).sum()

        df10.to_excel('Backend//Result_Sheet_leg1.xlsx', sheet_name='Warehouse_FPS', index=False)
# ---------------------------------------------------------------------------------------------------------------------------------------------
                     
        Total_Demand=  float(WH['Allocation_Wheat'].sum()) + float(WH['Allocation_Rice'].sum())+ float(WH['Allocation_FRice'].sum())
        
        data["Scenario"]="Inter"
        data["Scenario_Baseline"] = "Baseline"
        
        data["WH_Used"] = df5['From_ID'].nunique()
        data["WH_Used_Baseline"] = "5"
        
        data["FPS_Used"] = df5['To_ID'].nunique()
        data["FPS_Used_Baseline"] = "4"
        
        data['Demand'] = round(float(WH['Allocation_Wheat'].sum()) + 
                       float(WH['Allocation_Rice'].sum()) + 
                       float(WH['Allocation_FRice'].sum()), 2)
        data['Demand_Baseline'] = "15,974.41"
        
        data['Total_QKM'] = float(result)
        data['Total_QKM_Baseline'] = "1,79,145.63"
        
        data['Average_Distance'] = float(round(result, 2)) / Total_Demand
        data['Average_Distance_Baseline'] = "11.21"

        if stop_process==True:
            data = {}
            data['status'] = 0
            data['message'] = "Process Stopped"
            json_data = json.dumps(data)
            json_object = json.loads(json_data)
            return json.dumps(json_object, indent=1)                     
    
        save_to_database_leg1(month, year, applicable)
        save_monthly_data_leg1(month, year, float(result))
        
        json_data = json.dumps(data)
        json_object = json.loads(json_data)

        if os.path.exists('ouputPickle.pkl'):
            os.remove('ouputPickle.pkl')

        # open pickle file
        dbfile1 = open('ouputPickle.pkl', 'ab')

    # save pickle data
    pickle.dump(json_object, dbfile1)
    dbfile1.close()
    data['status'] = 1
    json_data = json.dumps(data)
    json_object = json.loads(json_data)
    return json.dumps(json_object, indent=1)
    


if __name__ == "__main__":
    app.run(host='0.0.0.0', port=5000)
# -*- coding: utf-8 -*-

#!/usr/bin/python
# -*- coding: utf-8 -*-