from gurobipy import GRB,Model,quicksum from math import sqrt from xlutils.copy import copy from xlrd import open_workbook import xlwt def printLists(PR_list,QP_list ,INVR_list,INVP_list,PS_list,BO_list): rb = open_workbook('output.xlsx') wb = copy(rb) ws = wb.get_sheet(0) #print out PR matrix for i in range(4,19): for j in range(2,12): ws.write(i, j, PR_list[j-2][i-4]) #print out QP matrix for i in range(4,19): for j in range(15,18): ws.write(i, j, QP_list[j-15][i-4]) #print out INVR matrix for i in range(24,39): ws.write(i, 15, INVR_list[i-24]) #print out INVP matrix for i in range(25,40): for j in range(2,5): ws.write(i, j, INVP_list[j-2][i-25]) #print out PS matrix for i in range(25,40): for j in range(9,12): ws.write(i, j, PS_list[j-9][i-25]) #print out BO matrix for i in range(25,40): for j in range(19,22): ws.write(i, j, BO_list[j-19][i-25]) wb.save('output1.xls') def isFeasible(m): status_code = {1:'LOADED', 2:'OPTIMAL', 3:'INFEASIBLE', 4:'INF_OR_UNBD', 5:'UNBOUNDED'} status = m.status if status == 2: return True elif status==5: print('Problem is unbounded') elif status==4: print('Problem is either infeasible or unbounded') else: print('Model is infeasible') return False #indices suppliers = 10 products = 3 times = 15 warehouses = 2 #input parameters (initialization) CR = [0 for s in range(suppliers)] HC = [0 for p in range(products)] HCR = 0 OFF = [0 for p in range(products)] DP = [[0 for t in range(times)] for p in range(products)] SC = [[0 for t in range(times)] for t in range(suppliers)] PCAP = [0 for p in range(products)] WC = 0 LT = [0 for p in range(products)] LTR = [0 for s in range(suppliers)] SIGMA= 0 sgm = [0 for p in range(products)] SIG = [0 for p in range(products)] BOC = [0 for p in range(products)] PRC = [0 for p in range(products)] PRC[0]=15 PRC[1]=14 PRC[2]=13 #getting input parameters wb = open_workbook('d.xlsx') ws = wb.sheet_by_index(0) #take misc data HCR = float(ws.cell_value(4,0)) WC = float(ws.cell_value(4,1)) SIGMA = float(ws.cell_value(4,2)) #take product data for k in range(products): PCAP[k] = float(ws.cell_value(4,6+k)) OFF[k] = float(ws.cell_value(5,6+k)) HC[k] = float(ws.cell_value(6,6+k)) LT[k] = int(ws.cell_value(7,6+k)) BOC[k] = float(ws.cell_value(8,6+k)) SIG[k] = float(ws.cell_value(9,6+k)) sgm[k] = float(ws.cell_value(10,6+k)) for l in range(times-3): DP[k][l+3] = float(ws.cell_value(11+l,6+k)) #take supplier data for x in range(suppliers): CR[x] = float(ws.cell_value(4+x,11)) LTR[x] = int(ws.cell_value(4+x,12)) #take supplier capacity for i in range(suppliers): for j in range(times): SC[i][j] = float(ws.cell_value(19+i,11+j)) ##MODEL #decision variables model = Model('trial') model.setParam('OutputFlag',False) PR = model.addVars(suppliers,times,vtype=GRB.INTEGER,lb=0, ub =GRB.INFINITY, name = ['PR_'+str(i+1)+'_'+str(j-3) for i in range(suppliers) for j in range(times)]) QP = model.addVars(products,times,vtype=GRB.INTEGER,lb=0, ub =GRB.INFINITY, name = ['QP_'+str(i+1)+'_'+str(j-3) for i in range(products) for j in range(times)]) INVR = model.addVars(times,vtype=GRB.INTEGER,lb=0, ub =GRB.INFINITY, name = ['INVR_'+str(i-3) for i in range(times)]) INVP = model.addVars(products,times,vtype=GRB.INTEGER,lb=0, ub =GRB.INFINITY, name = ['INVP_'+str(i+1)+'_'+str(j-3) for i in range(products) for j in range(times)]) PS = model.addVars(products,times,vtype=GRB.INTEGER,lb=0, ub =GRB.INFINITY, name = ['PS_'+str(i+1)+'_'+str(j-3) for i in range(products) for j in range(times)]) BO = model.addVars(products,times,vtype=GRB.INTEGER,lb=0, ub =GRB.INFINITY, name = ['BO_'+str(i+1)+'_'+str(j-3) for i in range(products) for j in range(times)]) UR = model.addVars(times,vtype=GRB.INTEGER,lb=0,ub=GRB.INFINITY,name=['UR_'+str(i+1) for i in range(times)]) UP = model.addVars(products,times,vtype=GRB.INTEGER,lb=0,ub=GRB.INFINITY,name=['UP_'+str(i+1) for i in range(products) for j in range(times)]) #objective function model.setObjective(quicksum(HCR*INVR[t] + quicksum(HC[p]*INVP[p,t] for p in range(products)) for t in range(times)) +quicksum(CR[s]*PR[s,t] for t in range(times) for s in range(suppliers)) +quicksum(QP[p,t]*PRC for p in range(products) for t in range(times)) +quicksum(BO[p,t]*BOC[p] for p in range(products) for t in range(times)),GRB.MINIMIZE) #constraints model.addConstrs(INVR[t] + quicksum(INVP[p,t] for p in range(products)) <= WC for t in range(times)) model.addConstrs(QP[p,t] <= PCAP[p] for p in range(products) for t in range(times)) model.addConstrs(PR[s,t]<=SC[s][t] for t in range(times) for s in range(suppliers)) model.addConstrs(BO[p,t]-BO[p,t-1] >= (DP[p][t+LT[p]]-PS[p,t+LT[p]]) for p in range(products) for t in range(1,times) if t+LT[p]=LT[p]) model.addConstrs(PS[p,t] >= DP[p][t]*SIG[p] for p in range(products) for t in range(times)) model.addConstrs(PS[p,t] <= DP[p][t] for p in range(products) for t in range(times)) model.addConstrs(INVP[p,0]==5000000 for p in range(products)) model.addConstr(INVR[0]==1000000) model.addConstrs(UP[p,t]==INVP[p,t-1]+QP[p,t-LT[p]] for p in range(products) for t in range(times) if t>=LT[p]) model.addConstrs(UR[t]==INVR[t]+PR[s,t-LTR[s]] for t in range(times) for s in range(suppliers) if t>=LTR[s]) model.write('Model.lp') model.optimize() if isFeasible(model): PR_list = [[PR[s,t].x for t in range(times)] for s in range(suppliers)] QP_list = [[QP[p,t].x for t in range(times)] for p in range(products)] INVR_list = [INVR[t].x for t in range(times)] INVP_list = [[INVP[p,t].x for t in range(times)] for p in range(products)] PS_list = [[PS[p,t].x for t in range(times)] for p in range(products)] BO_list = [[BO[p,t].x for t in range(times)] for p in range(products)] printLists(PR_list,QP_list,INVR_list,INVP_list,PS_list,BO_list)