import os
import sys
import glob
import argparse
import math
import string
import copy as _copy
from collections import namedtuple
from operator import attrgetter
import openpyxl
try:
	from openpyxl.utils.cell import get_column_letter as get_column_letter
except:
	from openpyxl.utils import _get_column_letter as get_column_letter
# psstools.ImportPSSE(33)
# psstools.EnableExceptions()
import psstools
import psspy
import datetime
import xlrd
import xlwt
temp_sum = 0
WSCCArea = 15
test_index = 0 

_f, _i, _s = psspy._f, psspy._i, psspy._s 


def col2num(c):
    ###"""Return number corresponding to excel-style column."""
    number=-25
    for l in c:
        if not l in string.ascii_letters:
            return False
        number+=ord(l.upper())-64+25-1
		
    return number

class MPID_v0(object):  ## Updated by SDR
    def __init__(self, mpidName, loadmpid, tag, ISDtag, generators, loads, interfaceBus, MPID_buses, netFlow ):
		self.mpidName = mpidName
		self.loadmpid = loadmpid
		self.tag = tag
		self.ISDtag = ISDtag
		self.generators = generators
		self.loads = loads
		
		self.interfaceBus = interfaceBus
		self.MPID_buses = MPID_buses
		self.netFlow = netFlow
		
class MPID(object):  ## Updated by SDR
    # def __init__(self, mpidName, facilityName, loadmpid, tag, ISDtag, generators, loads, interfaceBus, MPID_buses, netFlow ):
    def __init__(self, mpidName, facilityName, lfTag, loadmpids, emmoOffer, loadOffer, locationType, generators, loads, loadDistributionFactor, interfaceBus, MPID_buses, netFlow ):
		self.mpidName = mpidName
		self.facilityName = facilityName
		self.lfTag = lfTag
		self.loadmpids = loadmpids
		self.emmoOffer = emmoOffer
		self.loadOffer = loadOffer 
		
		self.locationType = locationType
		
		self.generators = generators
		self.loads = loads
		self.loadDistributionFactor = loadDistributionFactor
		self.interfaceBus = interfaceBus
		self.MPID_buses = MPID_buses
		self.netFlow = netFlow
		
		
class loadObject(object):
	def __init__(self, bus, id):
		self.bus = bus
		self.id = id
		# self.pload = pload
		# self.qload = qload

class generatorObject(object):
	def __init__(self, bus, id):
		self.bus = bus
		self.id = id
		# self.pgen = pgen
		# self.pmax = pmax
		# self.pmin = pmin
		# self.qmax = qmax
		# self.qmin = qmin
		# self.mbase = mbase			

	
def determineDispatchMW(currentHourEMMO, mpidchecklist):
	# exportServices = [bc_exp, sk_exp, mt_exp]
	currentHourDispatchedMW = dict()
	# print mpidchecklist
	# raw_input()
	for mpidName in mpidchecklist:
		totalDispatchMW = 0
		foundTag = 0
		if len(currentHourEMMO) != 0:
			for eachrow in currentHourEMMO:
				if eachrow.assetName == mpidName:
					foundTag = 1
					totalDispatchMW = totalDispatchMW + eachrow.dispatchedPower
					# print totalDispatchMW
					
			currentHourDispatchedMW[mpidName] = totalDispatchMW
		else:
			currentHourDispatchedMW[mpidName] = totalDispatchMW
			
	return currentHourDispatchedMW

def determineAvailableMW(currentHourEMMO, mpidchecklist):
	# exportServices = [bc_exp, sk_exp, mt_exp]
	currentHourAvailableMW = dict()
	
	for mpidName in mpidchecklist:
		# currentSerial = row.serial
		# currentAssetName = row.assetName
		totalAvailableMW = 0
		foundTag = 0
		if len(currentHourEMMO) != 0:
			for eachrow in currentHourEMMO:
				if eachrow.assetName == mpidName:
					foundTag = 1
					totalAvailableMW = totalAvailableMW + eachrow.availablePower
					# print totalDispatchMW
					# raw_input()
			# currentHourExports.append(export(mpidName, totalExportMW))
			
			currentHourAvailableMW[mpidName] = totalAvailableMW
		else:
			currentHourAvailableMW[mpidName] = totalAvailableMW
			
	return currentHourAvailableMW
	
def determineHistoricalGrossLoad(mpidlist, currentHourDispatchedMW, selected_currentHourLoad, selected_currentHourTSS):
	###########################
	#####Updated by SDR on 21June2019
	#######################################
	Historic_grossLoad = dict()		
	for mpidName in mpidlist['SS_Gross_Net']:
		grossLoadMW = 0
		if selected_currentHourLoad[mpidName] < 1:
			if currentHourDispatchedMW[mpidName] > selected_currentHourTSS[mpidName]:
				grossLoadMW = currentHourDispatchedMW[mpidName] - selected_currentHourTSS[mpidName]
			else:
				grossLoadMW = 0
		else:
			grossLoadMW = selected_currentHourLoad[mpidName] + currentHourDispatchedMW[mpidName]
		Historic_grossLoad[mpidName] = grossLoadMW
	
	for mpidName in mpidlist['SS_Gross_Gross']:
		Historic_grossLoad[mpidName] = selected_currentHourLoad[mpidName]
	for mpidName in mpidlist['SS_Net_Gross']:
		Historic_grossLoad[mpidName] = selected_currentHourLoad[mpidName]
	
	return Historic_grossLoad
	
	
def process_HourlyHistoricData(MPIDs, currentHourEMMO, currentHourHistorical_EMMO, currentHourLoad, currentHourTSS, mpidlist):
	
	# allGenMpidlist = mpidlist['NSS'] + mpidlist['SS_Net_Net'] + mpidlist['SS_Gross_Gross'] + mpidlist['SS_Net_Gross'] + mpidlist['SS_Gross_Net']
	allGenMpidlist = mpidlist['NSS'] + mpidlist['SS_Net_Net'] + mpidlist['SS_Gross_Gross'] + mpidlist['SS_Net_Gross'] + mpidlist['SS_Gross_Net'] + mpidlist['Import_Export']
	
	currentHourDispatchedMW = determineDispatchMW(currentHourHistorical_EMMO, allGenMpidlist)
	currentHourAvailableMW = determineAvailableMW(currentHourEMMO, allGenMpidlist)
	
	selected_currentHourTSS = dict()
	for mpidName in mpidlist['SS_Gross_Net']:
		tssMW = 0
		for row in currentHourTSS:
			if row.mpidName == mpidName:
				tssMW = row.mwData
				
		selected_currentHourTSS[mpidName] = tssMW
		
	selected_currentHourLoad = dict()
	for mpidName in allGenMpidlist:
		loadMW = 0
		tag = "NOT FOUND"
		for mpid in MPIDs:
			if mpid.mpidName == mpidName:
				genMPID = mpid.mpidName
				loadMPIDs = mpid.loadmpids
				# enable_print()
				# print genMPID, loadMPIDs
				# print mpid.loadDistributionFactor
				# raw_input()
				if loadMPIDs != [''] :
					for loadMPID in loadMPIDs:
						for eachRecord in currentHourLoad:
							if eachRecord.mpidName == loadMPID:
								if mpid.loadDistributionFactor != '':
									loadMW = loadMW + eachRecord.mwData * mpid.loadDistributionFactor
								else:
									loadMW = loadMW + eachRecord.mwData * 1.0
								tag = "FOUND"
								# loadMW = loadMW + eachRecord.mwData * 0.5
				else:
					tag = "No On-site Load available"
		# enable_print()
		# print mpidName, loadMW, tag
		# raw_input()
		if tag == 'NOT FOUND':
			enable_print()
			print mpidName, " - Load Not Found in Load Data"
			raise ValueError
		else:
			selected_currentHourLoad[mpidName] = loadMW
	
	Historic_grossLoad = determineHistoricalGrossLoad(mpidlist, currentHourDispatchedMW, selected_currentHourLoad, selected_currentHourTSS)
	
	return currentHourDispatchedMW, currentHourAvailableMW, selected_currentHourTSS, selected_currentHourLoad, Historic_grossLoad 

def ProcessFiles(caseFiles, GenMappingFiles, EMMOFiles, Historical_EMMOFiles, loadDataFiles, loadMappingFiles, tssDataFiles, grossLoadDataFiles, dates, months, Selected_Lf_Process, calc_year):
	#intertie = ab_bc
	
	##voltageLimits = ReadVoltageLimits(voltageFilename)

	wb = xlwt.Workbook()

	monthlyMPIDMapping = dict()
	monthlyCases = dict()
	monthlyLoadMapping = dict()
	
	
	monthlyEMMO = dict()
	monthlyHistorical_EMMO = dict()
	monthlyLoad = dict()
	monthlyMvarLoad = dict()
	monthlyTSS = dict()
	monthlyGrossLoad = dict()
	intertiedefn_ab_bc = dict()
	intertiedefn_mt_bus = dict()
	intertiedefn_mt_busid = dict()
	intertiedefn_sk_bus = dict()
	intertiedefn_sk_busid = dict()

	timeNow = datetime.datetime.now()
	timeNow = timeNow.strftime("%d%b%Y-%H-%M")
	print "Simulation Start Time : ", timeNow
	enable_print()
	emmoupdateRow = 1
	
	mpidlist_nss = dict()
	mpidlist_ss_net_net = dict()
	mpidlist_ss_gross_net = dict()
	mpidlist_ss_net_gross = dict()
	mpidlist_ss_gross_gross = dict()
	mpidlist_import_export = dict()
	
	mpidlist_all = dict()
	mpidlist = dict()
	
	selected_DispatchedMW = dict()
	selected_AvailableMW = dict()
	selected_TSS = dict()
	selected_Load = dict()
	Historic_grossLoad = dict()
	
	grossLoadCalculation_ErrorLog = dict()
	count_success = dict()
	
	for month in months:
		logfilename = os.path.join(os.getcwd(), month+ "-%s.log" %timeNow)
		log = open(logfilename, "w")
		
		
		################ Monthly MPID Mapping ############
		GenMappingFile = selectMonthlyMPIDMapping(GenMappingFiles, month)
		# log_print(log)
		monthlyMPIDMapping[month] = CreateMPIDs(GenMappingFile, month)
		intertiedefn_ab_bc[month],intertiedefn_mt_bus[month], intertiedefn_mt_busid[month], intertiedefn_sk_bus[month], intertiedefn_sk_busid[month] = Createintertielst(GenMappingFile, month)
		# ################ Monthly Cases ############
		# monthlyCases[month] = selectMonthlyCase(caseFiles, month)
		
		# ############ Monthly Load Mapping ##############
		# loadMappingFile = SelectMonthlyLoadMapping(loadMappingFiles, month)
		# monthlyLoadMapping[month] = readLoadMapping(loadMappingFile)
		
		# #### Select EMMO ####
		EMMOFile = SelectMonthlyEMMO(EMMOFiles, month)
		monthlyEMMO[month] = readMonthlyEMMO(EMMOFile)
		# #### Select EMMO ####
		Historical_EMMOFile = SelectMonthlyEMMO(Historical_EMMOFiles, month)
		monthlyHistorical_EMMO[month] = readMonthlyEMMO(Historical_EMMOFile)
		
		###### Monthly load Data ##
		loadDataFile = SelectMonthlyLoad(loadDataFiles, month)
		monthlyLoad[month] = readMonthlyLoad(loadDataFile)
		
		# ###### Monthly load Mvar Data ##
		# # loadMvarDataFile = SelectMonthlyLoad(loadDataFiles, month)
		# monthlyMvarLoad[month] = readMonthly_MvarLoad(loadDataFile)
		
		### Monthly TSS Data ####
		tssDataFile = SelectMonthlyTSS(tssDataFiles, month)
		monthlyTSS[month] = readMonthlyTSS(tssDataFile)
		
		### Monthly gross Load ####
		# # grossLoadDataFile = SelectMonthlyGrossLoad(grossLoadDataFiles, month)
		# # monthlyGrossLoad[month] = readMonthlyGrossLoad(grossLoadDataFile)

		# # raw_input()
		count_success_s1 = 0
		count_attemptedCases = 0
		
		# generateReport = dict()
		
		selected_DispatchedMW[month] = dict()
		selected_AvailableMW[month] = dict()
		selected_TSS[month] = dict()
		selected_Load[month] = dict()
		Historic_grossLoad[month] = dict()
		# mpidchecklist = ["CNR5", "SCL1", "MEG1",]
		
		
		mpidlist_nss[month] = []
		mpidlist_ss_net_net[month] = []
		mpidlist_ss_gross_net[month] = []
		mpidlist_ss_net_gross[month] = []
		mpidlist_ss_gross_gross[month] = []
		mpidlist_import_export[month] = []
		mpidlist_all[month] = []
		
		mpidlist[month] = dict()
		count_success[month] = 0
		
		for MPID in monthlyMPIDMapping[month]:
			# print MPID.mpidName
			# raw_input()
			mpidlist_all[month].append(MPID.mpidName)
		
		
		for MPID in monthlyMPIDMapping[month]:
			# if MPID.ISDtag == "ISD":
			# print MPID.mpidName, MPID.locationType, MPID.emmoOffer, MPID.loadOffer
			
			if MPID.locationType == "Source Asset" or MPID.locationType == "Reversing POD":
				mpidlist_nss[month].append(MPID.mpidName)
			elif MPID.locationType == "Self-supply":
				if MPID.emmoOffer == "Net-to-grid" and MPID.loadOffer == "Net-to-grid":
					mpidlist_ss_net_net[month].append(MPID.mpidName)
				elif MPID.emmoOffer == "Net-to-grid" and MPID.loadOffer == "Gross":
					mpidlist_ss_net_gross[month].append(MPID.mpidName)
				elif MPID.emmoOffer == "Gross" and MPID.loadOffer == "Gross":
					mpidlist_ss_gross_gross[month].append(MPID.mpidName)
				elif MPID.emmoOffer == "Gross" and MPID.loadOffer == "Net-to-grid":
					mpidlist_ss_gross_net[month].append(MPID.mpidName)
			elif MPID.locationType == "Import" or MPID.locationType == "Export":
				mpidlist_import_export[month].append(MPID.mpidName)
		
		
		# print mpidlist_nss[month]
		# print mpidlist_ss_net_net[month]
		# print mpidlist_ss_net_gross[month]
		# print mpidlist_ss_gross_gross[month]
		# print mpidlist_ss_gross_net[month]
		# raw_input()
	
	
	# mpidchecklist = ["NOVAGEN15M", "SCTG"]
	for dt in dates:
		# dates = ["2015-DEC-01-14-00", "2015-DEC-01-03-00"]
		date = dt.strftime("%Y-%b-%d-%H-%M")
		log_date_time = dt.strftime("%Y/%m/%d %H:%M")
		
		
		year = date.split("-")[0]
		month = date.split("-")[1].upper()
		day = date.split("-")[2]
		hour = date.split("-")[3]
		min = date.split("-")[4]
		
		
		# global output_dir
		# output_dir = 'Output Cases'
		# output_dir = os.path.join(preparedCaseDir, day+"_"+month+"_"+year)
		# ensure_dir(output_dir)
		enable_print()
		print "Processing : " , date 
		
		# logfilename = os.path.join(output_dir, day+"_"+month+"_"+year+"_Hour_"+hour+".log")
		# log = open(logfilename, "w")
		log_print(log)
		# global log
		
		# tempcasename = day+"_"+month+"_"+year+"_Hour_"+hour+".sav"
		# outputCaseName = os.path.join(output_dir, tempcasename)
		# print outputCaseName
			
		year_emmo = year
		year_2 = str(int(year)-2)
		year_2_emmo = year_2
		
		
		if Selected_Lf_Process == "Forecast":
			time_emmo = day+month+year_emmo[2:]+":"+hour+":00:00"
			time_historical_emmo = day+month+year_2_emmo[2:]+":"+hour+":00:00"
			time_tss = day+month+year_2+":"+hour+":00:00"
		elif Selected_Lf_Process == "Module-C":
			time_emmo = day+month+year_emmo[2:]+":"+hour+":00:00"
			time_tss = day+month+year+":"+hour+":00:00"
		else:
			raise
		
		# # try:
		# enable_print()
		# print time_emmo
		# print time_historical_emmo
		# print time_tss
		# raw_input()
			
		
		try:
			currentHourEMMO, currentHourExports = createHourlyEMMO(monthlyEMMO[month], time_emmo)
			if Selected_Lf_Process == "Forecast":
				currentHourHistorical_EMMO, currentHourExports = createHourlyEMMO(monthlyHistorical_EMMO[month], time_historical_emmo)
			elif Selected_Lf_Process == "Module-C":
				currentHourHistorical_EMMO = currentHourEMMO
			
			currentHourTSS = createHourlyTSS(monthlyTSS[month], time_tss)
			
			time = day+month+year+":"+hour+":00:00"
			currentHourLoad = createHourlyLoad(monthlyLoad[month], time)
			MPIDs = monthlyMPIDMapping[month]
			global ab_bc
			global mtTie_genbus
			global mtTie_genId
			global skTie_bus
			global skTie_loadId

			ab_bc = intertiedefn_ab_bc[month]
			mtTie_genId = intertiedefn_mt_busid[month]
			mtTie_genbus = intertiedefn_mt_bus[month]
			skTie_bus = intertiedefn_sk_bus[month]
			skTie_loadId = intertiedefn_sk_busid[month]
			intertie = ab_bc
			mpidlist[month]['NSS'] = mpidlist_nss[month]
			mpidlist[month]['SS_Net_Net'] = mpidlist_ss_net_net[month]
			mpidlist[month]['SS_Gross_Net'] = mpidlist_ss_gross_net[month]
			mpidlist[month]['SS_Net_Gross'] = mpidlist_ss_net_gross[month]
			mpidlist[month]['SS_Gross_Gross'] = mpidlist_ss_gross_gross[month]
			mpidlist[month]['Import_Export'] = mpidlist_import_export[month]
			
			
			[currentHourDispatchedMW, currentHourAvailableMW, selected_currentHourTSS, selected_currentHourLoad, currentHour_HistoricGrossLoad] = process_HourlyHistoricData(MPIDs, currentHourEMMO, currentHourHistorical_EMMO, currentHourLoad, currentHourTSS, mpidlist[month])
			# [currentHourDispatchedMW, currentHourAvailableMW, selected_currentHourTSS, selected_currentHourLoad, currentHour_HistoricGrossLoad] = process_HourlyHistoricData(MPIDs, currentHourEMMO, currentHourHistorical_EMMO, currentHourLoad , currentHourTSS, mpidchecklist)
			
			selected_DispatchedMW[month][log_date_time] = currentHourDispatchedMW
			selected_AvailableMW[month][log_date_time] = currentHourAvailableMW
			selected_TSS[month][log_date_time] = selected_currentHourTSS
			selected_Load[month][log_date_time] = selected_currentHourLoad
			Historic_grossLoad[month][log_date_time] = currentHour_HistoricGrossLoad
			
			count_success[month] += 1
			
			
		except MissingDataError as m:
			grossLoadCalculation_ErrorLog[m.missingHour] = m.missingDataTag
			pass
			
	
	for month in months:
		if count_success[month] > 1:
			writeReport(calc_year, month, mpidlist[month], Historic_grossLoad[month], selected_DispatchedMW[month], selected_AvailableMW[month], selected_TSS[month], selected_Load[month])
		
	generateErrorReport(grossLoadCalculation_ErrorLog)
	# generateReport(selected_DispatchedMW, selected_AvailableMW, selected_TSS, selected_Load, mpidchecklist, Historic_grossLoad)
			
def writeReport(calc_year, month, mpidlist, Historic_grossLoad, selected_DispatchedMW, selected_AvailableMW, selected_TSS, selected_Load):
	
	wb1 = openpyxl.Workbook()
	ws1 = wb1.create_sheet("Gross Load MW")
	ws2 = wb1.create_sheet("Dispatched MW (D)")
	ws3 = wb1.create_sheet("Available MW (P)")
	ws4 = wb1.create_sheet("Load MW (L)")
	ws5 = wb1.create_sheet("TSS MW (T)")
	ws6 = wb1.create_sheet("Eligibility Check")
	ws_temp = wb1.create_sheet("Sheet")
	
	def writeGrossLoad():
		hours = sorted(Historic_grossLoad.keys())
		selectedMpids = mpidlist['SS_Gross_Net'] + mpidlist['SS_Gross_Gross'] + mpidlist['SS_Net_Gross']
		ws1['A1'] = "MPIDs"
		ws1['B1'] = "EMMO Offer/Load Offer"
		ws1['C1'] = "Hours"
		row=1
		col=4
		for hour in hours:
			cell = get_column_letter(col) + str(row)
			print cell, hour
			ws1[cell] = hour
			col=col+1
		
		row = 2
		for mpid in selectedMpids:
			col=4
			mpidIndex = 'A'+str(row)
			ws1[mpidIndex] = mpid
			for hour in hours:
				cell = get_column_letter(col) + str(row)
				print cell, mpid, hour
				# raw_input()
				ws1[cell] = Historic_grossLoad[hour][mpid]
				col = col + 1
			row = row+1
	
	def writeDispatchedMW():
		hours = sorted(selected_DispatchedMW.keys())
		# selectedMpids = mpidlist['NSS'] + mpidlist['SS_Net_Net'] + mpidlist['SS_Gross_Gross'] + mpidlist['SS_Net_Gross'] + mpidlist['SS_Gross_Net']
		selectedMpids = mpidlist['NSS'] + mpidlist['SS_Net_Net'] + mpidlist['SS_Gross_Gross'] + mpidlist['SS_Net_Gross'] + mpidlist['SS_Gross_Net'] + mpidlist['Import_Export']
		ws2['A1'] = "MPIDs"
		ws2['B1'] = "EMMO Offer/Load Offer"
		ws2['C1'] = "Hours"
		row=1
		col=4
		for hour in hours:
			cell = get_column_letter(col) + str(row)
			print cell, hour
			ws2[cell] = hour
			col=col+1
		
		row = 2
		for mpid in selectedMpids:
			col=4
			mpidIndex = 'A'+str(row)
			ws2[mpidIndex] = mpid
			for hour in hours:
				cell = get_column_letter(col) + str(row)
				print cell, mpid, hour
				# raw_input()
				ws2[cell] = selected_DispatchedMW[hour][mpid]
				col = col + 1
			row = row+1
	def writeAvailableMW():
		hours = sorted(selected_AvailableMW.keys())
		# selectedMpids = mpidlist['NSS'] + mpidlist['SS_Net_Net'] + mpidlist['SS_Gross_Gross'] + mpidlist['SS_Net_Gross'] + mpidlist['SS_Gross_Net']
		selectedMpids = mpidlist['NSS'] + mpidlist['SS_Net_Net'] + mpidlist['SS_Gross_Gross'] + mpidlist['SS_Net_Gross'] + mpidlist['SS_Gross_Net'] + mpidlist['Import_Export']
		ws3['A1'] = "MPIDs"
		ws3['B1'] = "EMMO Offer/Load Offer"
		ws3['C1'] = "Hours"
		row=1
		col=4
		for hour in hours:
			cell = get_column_letter(col) + str(row)
			print cell, hour
			ws3[cell] = hour
			col=col+1
		
		row = 2
		for mpid in selectedMpids:
			col=4
			mpidIndex = 'A'+str(row)
			ws3[mpidIndex] = mpid
			for hour in hours:
				cell = get_column_letter(col) + str(row)
				print cell, mpid, hour
				# raw_input()
				ws3[cell] = selected_AvailableMW[hour][mpid]
				col = col + 1
			row = row+1
	
	def writeLoadMW():
		hours = sorted(selected_Load.keys())
		selectedMpids = mpidlist['NSS'] + mpidlist['SS_Net_Net'] + mpidlist['SS_Gross_Gross'] + mpidlist['SS_Net_Gross'] + mpidlist['SS_Gross_Net']
		ws4['A1'] = "MPIDs"
		ws4['B1'] = "EMMO Offer/Load Offer"
		ws4['C1'] = "Hours"
		row=1
		col=4
		for hour in hours:
			cell = get_column_letter(col) + str(row)
			print cell, hour
			ws4[cell] = hour
			col=col+1
		
		row = 2
		for mpid in selectedMpids:
			col=4
			mpidIndex = 'A'+str(row)
			ws4[mpidIndex] = mpid
			for hour in hours:
				cell = get_column_letter(col) + str(row)
				print cell, mpid, hour
				# raw_input()
				ws4[cell] = selected_Load[hour][mpid]
				col = col + 1
			row = row+1
	
	def writeTssMW():
		hours = sorted(selected_TSS.keys())
		selectedMpids = mpidlist['SS_Gross_Net'] 
		ws5['A1'] = "MPIDs"
		ws5['B1'] = "EMMO Offer/Load Offer"
		ws5['C1'] = "Hours"
		row=1
		col=4
		for hour in hours:
			cell = get_column_letter(col) + str(row)
			print cell, hour
			ws5[cell] = hour
			col=col+1
		
		row = 2
		for mpid in selectedMpids:
			col=4
			mpidIndex = 'A'+str(row)
			ws5[mpidIndex] = mpid
			for hour in hours:
				cell = get_column_letter(col) + str(row)
				print cell, mpid, hour
				# raw_input()
				ws5[cell] = selected_TSS[hour][mpid]
				col = col + 1
			row = row+1
	
	
	
	
	def writeEligibilityReport():
		hours = sorted(selected_AvailableMW.keys())
		
		selectedMpids = mpidlist['NSS'] + mpidlist['SS_Net_Net']  
		ws6['A1'] = "MPIDs"
		ws6['B1'] = "EMMO Offer/Load Offer"
		ws6['C1'] = "Hours"
		row=1
		col=4
		for hour in hours:
			cell = get_column_letter(col) + str(row)
			print cell, hour
			ws6[cell] = hour
			col=col+1
		
		row = 2
		for mpid in selectedMpids:
			col=4
			mpidIndex = 'A'+str(row)
			ws6[mpidIndex] = mpid
			for hour in hours:
				cell = get_column_letter(col) + str(row)
				print cell, mpid, hour
				# raw_input()
				D = selected_DispatchedMW[hour][mpid]
				P = selected_AvailableMW[hour][mpid]
				L = selected_Load[hour][mpid]
				
				if P>=1:
					if L>0:
						if D>0:
							status = "Apply Load Reduction"
						else:
							status = "Apply Load Reduction"
					else:
						status = "L=0"
				else:
					status = "Use L"
						
				
				ws6[cell] = status
				col = col + 1
			row = row+1
	
	
	writeGrossLoad()
	writeDispatchedMW()
	writeAvailableMW()
	writeLoadMW()
	writeTssMW()
	writeEligibilityReport()
	
	
	month_dt = datetime.datetime.strptime(month, "%b")
	month_full = month_dt.strftime("%B")
	
	outputWB = "%sLF_Gross Load_%s.xlsx" %(calc_year, month_full)
	wb1.remove_sheet(ws_temp)
	wb1.save(outputWB)
			################################################

def generateErrorReport(grossLoadCalculation_ErrorLog):
	reportLog1 = open("GrossLoadCalculation_ErrorLog.txt", "a")
	reportLog1.write("Hours\tErrorTag\n")
	for hour in grossLoadCalculation_ErrorLog.keys():
		reportLog1.write(hour)
		reportLog1.write("\t")
		reportLog1.write(grossLoadCalculation_ErrorLog[hour])
		reportLog1.write("\n")
	reportLog1.close()

def generateReport(selected_DispatchedMW, selected_AvailableMW, selected_TSS, selected_Load, mpidchecklist, Historic_grossLoad):
	reportLog1 = open("Log_EMMO.txt", "w")
	reportLog2 = open("Log_TSS.txt", "w")
	reportLog3 = open("Log_LoadMW.txt", "w")
	reportLog4 = open("Log_AvailableMW.txt", "w")
	reportLog_GrossLoad = open("Historical Gross Load.txt", "w")
	
	reportLog1.write("Hour\t")
	reportLog2.write("Hour\t")
	reportLog3.write("Hour\t")
	reportLog4.write("Hour\t")
	reportLog_GrossLoad.write("Hour\t")
	
	for mpid in mpidchecklist:
		reportLog1.write("%s\t" %mpid) 
		reportLog2.write("%s\t" %mpid) 
		reportLog3.write("%s\t" %mpid) 
		reportLog4.write("%s\t" %mpid) 
		reportLog_GrossLoad.write("%s\t" %mpid) 
	
	reportLog1.write("\n")
	reportLog2.write("\n")
	reportLog3.write("\n")
	reportLog4.write("\n")
	reportLog_GrossLoad.write("\n")
	
	hours = sorted(selected_DispatchedMW.keys())
	for hour in hours:
		reportLog_GrossLoad.write("%s\t"  %hour)
		for mpid in mpidchecklist:
			reportLog_GrossLoad.write("%s\t"  %Historic_grossLoad[hour][mpid])
		reportLog_GrossLoad.write("\n")	
	
	
	# hours = sorted(hours, key=attrgetter('id'), reverse = True)
	for hour in hours:
		reportLog1.write("%s\t"  %hour)
		for mpid in mpidchecklist:
			reportLog1.write("%s\t"  %selected_DispatchedMW[hour][mpid])
		reportLog1.write("\n")
		
	
		
	
	# hours = selected_TSS.keys()
	for hour in hours:
		reportLog2.write("%s\t"  %hour)
		for mpid in mpidchecklist:
			reportLog2.write("%s\t"  %selected_TSS[hour][mpid])
		reportLog2.write("\n")
		
	# hours = selected_Load.keys()
	for hour in hours:
		reportLog3.write("%s\t"  %hour)
		for mpid in mpidchecklist:
			reportLog3.write("%s\t"  %selected_Load[hour][mpid])
		reportLog3.write("\n")
	
	for hour in hours:
		reportLog4.write("%s\t"  %hour)
		for mpid in mpidchecklist:
			reportLog4.write("%s\t"  %selected_AvailableMW[hour][mpid])
		reportLog4.write("\n")
	
	reportLog1.close()
	reportLog2.close()
	reportLog3.close()
	reportLog4.close()
	reportLog_GrossLoad.close()
	
	

class InsufficientSourceError(Exception):
	def __init__(self, load_AIES, gen_AIES, loss_total_AIES):
		print "INSUFFICIENT SOURCE ASSET"
		self.load_AIES = load_AIES
		self.gen_AIES = gen_AIES
		self.loss_total_AIES = loss_total_AIES

class MissingDataError(Exception):
	def __init__(self, missingDataTag, missingHour):
		print "MISSING DATA"
		self.missingDataTag = missingDataTag
		self.missingHour = missingHour

class MissingDataFileError(Exception):
	def __init__(self, missingDataTag):
		print "MISSING DATA"
		self.missingDataTag = missingDataTag
		
		
class iterationLimitError(Exception):
	def __init__(self, mismatch, iterationNumber, load_AIES, gen_AIES, loss_total_AIES):
		self.iterationNumber = iterationNumber
		self.mismatch = mismatch
		self.load_AIES = load_AIES
		self.gen_AIES = gen_AIES
		self.loss_total_AIES = loss_total_AIES

class solutionError(Exception):
	def __init__(self, solvedMsg, load_AIES, gen_AIES, loss_total_AIES):
		self.solvedMsg = solvedMsg
		self.load_AIES = load_AIES
		self.gen_AIES = gen_AIES
		self.loss_total_AIES = loss_total_AIES
		

class HvdcError(Exception):
	def __init__(self, load_AIES, gen_AIES, loss_total_AIES):
		print "HVDC ERROR"
		self.load_AIES = load_AIES
		self.gen_AIES = gen_AIES
		self.loss_total_AIES = loss_total_AIES
#####################
#############################
def selectMonthlyMPIDMapping(GenMappingFiles, month):
	print "Select Monthly Mapping..... "
	desiredFile = ''
	for excelFile in GenMappingFiles:
		filename = os.path.splitext(os.path.split(excelFile)[1])[0].lower()
		# enable_print()
		# print filename
		# raw_input()
		filename = filename.split("_")[2]
		if month.lower() in filename:
			desiredFile = excelFile
	# print desiredFile, month
	# raw_input()
	return desiredFile	

def Createintertielst(excelFile, month):
	# Updated - 26 Apr 2020
	print("Reading Intertie Mapping.....")
	wb = xlrd.open_workbook(excelFile)
	ws = wb.sheet_by_name('Intertie Mapping')
	from_bus = []
	to_bus = []
	tie_id = []
	for col_idx in range(1, ws.ncols):
		for row_idx in range(2, ws.nrows):
			tempVal = ws.cell(row_idx, col_idx).value
			if tempVal == "N/A" or tempVal == "" or tempVal == "n/a":
				tempVal = "N/A"
				if col_idx == 1:
					from_bus.append(tempVal)
				elif col_idx == 2:
					# if ws.cell(row_idx,col_idx).value != 'N/A':
					to_bus.append(tempVal)
				else:
					tie_id.append(str(tempVal))
			else:
				if col_idx == 1:
					from_bus.append(int(tempVal))
				elif col_idx == 2:
					# if ws.cell(row_idx,col_idx).value != 'N/A':
					to_bus.append(int(tempVal))
				else:
					tie_id.append(str(tempVal))
	
	ab_bc_intertie_info = [[from_bus[idx], to_bus[idx], tie_id[idx]] for idx in range(0, 3)]
	sk_intertie_bus = from_bus[4]
	sk_intertie_id = tie_id[4]
	mt_intertie_bus = from_bus[3]
	mt_intertie_id = tie_id[3]
	return ab_bc_intertie_info, mt_intertie_bus, mt_intertie_id, sk_intertie_bus, sk_intertie_id

def CreateMPIDs(excelFile, month): ## Updated by SDR
	print ("Reading Mapping .....")
	wb = xlrd.open_workbook(excelFile)
	ws = wb.sheet_by_name('Gen MPID Mapping') ## Updated
	MPIDs = []
	MPIDs_dict = dict()
	for row in range(1, ws.nrows):
		# tag = ws.cell(row, 2).value   ### Clumn C
		tag = str(ws.cell(row, col2num("C")).value)   ### Clumn C
		# if tag == 'LF MPID' or tag == 'LF MPID-SKIP' or tag == 'SKIP':
		if tag == 'LF' or tag == 'SKIP-LF':
			mpidName = str(ws.cell(row, col2num("A")).value)   ### Clumn B
			# print mpidName
			facilityName = str(ws.cell(row, col2num("B")).value)   ### Clumn B
			lfTag = str(ws.cell(row, col2num("C")).value)   ### Clumn B
			loadmpidNames  = str(ws.cell(row, col2num("D")).value)   ### Clumn B
			
			emmoOffer  = str(ws.cell(row, col2num("E")).value)   ### Clumn E
			loadOffer  = str(ws.cell(row, col2num("F")).value)   ### Clumn F
			
			locationType  = str(ws.cell(row, col2num("G")).value)   ### Clumn G
			# dosTag  = str(ws.cell(row, col2num("H")).value)   ### Clumn H
			
			# ISDtag = ws.cell(row, col2num("F")).value   ### Clumn C
			
			# associatedGens = [str(ws.cell(row, col).value) for col in range(6, 17)]
			associatedGens = [str(ws.cell(row, col).value) for col in range(col2num("H"), col2num("R")+1)]
			# associatedLoads = [str(ws.cell(row, col).value) for col in range(17, 20)]
			associatedLoads = [str(ws.cell(row, col).value) for col in range(col2num("S"), col2num("U")+1)]
			
			loadDistributionFactor  = ws.cell(row, col2num("V")).value
			
			# enable_print()
			# print loadDistributionFactor
			# raw_input()

			
			interfaceBus = (ws.cell(row, col2num("W")).value)
			if interfaceBus != '':
				interfaceBus = int(interfaceBus)
			mpid_subsystem = str((ws.cell(row, col2num("X")).value))
			buses = mpid_subsystem.split(",")
			netFlow = str((ws.cell(row, col2num("Y")).value))
			
			generators = []
			# if associatedGens != []:
			for gen in associatedGens:
				if gen != '':
					[bus, id] = gen.split("-")
					bus = int(bus)
					generators.append(generatorObject(bus, str(id)))
			
			loads = []
			# if associatedLoads != []:
			# for load in associatedLoads:
				# if load != '':
					# [bus, id] = load.split("-")
					# bus = int(bus)
					# loads.append(loadObject(bus, id))
			
			loadmpids = []
			loadmpidNames = loadmpidNames.strip(" ")
			loadmpids = loadmpidNames.split(", ")
			# enable_print()
			# print loadmpids
			# raw_input()
			MPID_buses = []
			for bus in buses:
				if bus != '':
					MPID_buses.append(int(bus.strip(" ")))
			if interfaceBus != '':
				MPID_buses.append(interfaceBus)
			# print mpidName, tag, ISDtag
			# print associatedGens
			# print associatedLoads 
			# print interfaceBus
			# print MPID_buses
			# print netFlow 
			# raw_input()
			temp = MPID(mpidName, facilityName, tag, loadmpids, emmoOffer, loadOffer, locationType, generators, loads, loadDistributionFactor, interfaceBus, MPID_buses, netFlow )
			MPIDs.append(temp)
			# MPIDs_dict[mpidName] = temp
	# sys.exit()    
			
	return MPIDs
###################################################
#####################################
###########



	
class loadmapdata(object):
	def __init__(self, genMPID, loadMPID, bus, id, isdtag):
		self.genMPID = genMPID
		self.loadMPID = loadMPID
		self.bus = bus
		self.id = id
		self.isdtag = isdtag
def readLoadMapping(excelFile):
	print "Reading Load Mapping File .... "
	wb = xlrd.open_workbook(excelFile)
	# ws = wb.sheet_by_name("Load Mapping (Review)")
	ws = wb.sheet_by_name("Load Mapping")
	loadMappingList = []
	for row in range(1, ws.nrows):
		genMPID = (ws.cell(row, 0).value)
		if type(genMPID) is float or type(genMPID) is int:
			genMPID = str(int(genMPID))
		else:
			genMPID = str(genMPID)
		
		loadMPID = str(ws.cell(row, 1).value)
		if type(loadMPID) is float or type(loadMPID) is int:
			loadMPID = str(int(loadMPID))
		else:
			loadMPID = str(loadMPID)
		
		# if type(mpidName) is float or type(mpidName) is int:
			# mpidName = str(int(mpidName))
		# print assetName
		# raw_input()
		if loadMPID != '':
			
			bus = int(ws.cell(row, 2).value)
			id = (ws.cell(row, 3).value)
			# print id, type(id)
			if type(id) is  float:
				id = str(int(id))
			elif type(id) is  int:
				id = str(id)
			else: ## type(id) is  unicode:
				id = str(id)
			isdtag = (ws.cell(row, col2num("E")).value)
			# print loadMPID, bus, id
			# raw_input()
			loadMappingList.append(loadmapdata(genMPID, loadMPID, bus, id, isdtag))
	# raw_input()
	return loadMappingList
######################################
####################################
		


	

 
def Write(output, mpid, data, caseName, wb, ws, writeStart):
	# wb = xlwt.Workbook()
	# ws = wb.add_sheet("Loss Factors")
	row = writeStart+1
	Header = "MPID Name,Initial Net Flow,Initial System Loss,Working MPID Internal Loss,Net system Loss,Marginal Unit,Final Net Flow,Final System Loss,Working MPID Internal Loss,Net system Loss,Marginal Unit,Loss Factor, MU Mismatch"

	if writeStart == 0:
		for col, value in enumerate(Header.split(",")):
			ws.write(0, col, value)
			# col += col
	
	# col = 0
	# print "MPID", mpid, "row", row 
	# sys.exit()
	# ws.write(row, 0, mpid)
	for col, value in enumerate(Header.split(",")):
		# ws.write(row, col, 'Test')
		ws.write(row, col, data[col])
		
	
	wb.save(output)
################################################
################################################
###################


#############################
#############################
class meritorderData(object):  ## Updated by SDR
	def __init__(self, time, serial, assetName, relatedImportAsset, assetShortName, Import_Export, blockNumber, flexibility, offerPrice, blockSize, availablePower, dispatchedPower, capabilityMW, totalDisp_eachMPID, P_export, P_import, P_netImportExport):
		self.time = time
		self.serial = serial
		self.assetName = assetName
		self.relatedImportAsset = relatedImportAsset
		self.assetShortName = assetShortName
		self.Import_Export = Import_Export
		self.blockNumber = blockNumber
		self.flexibility = flexibility
		self.offerPrice = offerPrice
		self.blockSize = blockSize
		self.availablePower = availablePower
		self.dispatchedPower = dispatchedPower
		self.capabilityMW = capabilityMW
		self.totalDisp_eachMPID = totalDisp_eachMPID
		self.P_export = P_export
		self.P_import = P_import
		self.P_netImportExport = P_netImportExport
		

def SelectMonthlyEMMO(EMMOFiles, month):
	desiredFile = ''
	for excelFile in EMMOFiles:
		filename = os.path.splitext(os.path.split(excelFile)[1])[0].lower()
		filename = filename.split("_")[2]
		if month.lower() in filename:
			desiredFile = excelFile
		# if month.lower() in os.path.splitext(os.path.split(excelFile)[1])[0].lower():
			# desiredFile = excelFile
			
	return desiredFile

class export(object):  ## Updated by SDR
	def __init__(self, mpidName, totalExportMW):
		self.mpidName = mpidName
		self.totalExportMW = totalExportMW
	
def createHourlyEMMO(monthlyEMMO, time):
	currentHourEMMO = []
	currentHourExports = dict()
	serial = 0
	for row in monthlyEMMO:
		if row.time == time:
			currentHourEMMO.append(row)
			serial += 1
			row.serial = serial
	# enable_print()
	# print len(currentHourEMMO)
	# raw_input()
	
	def determineMPIDTotalDispatch(currentHourEMMO):
		for row in currentHourEMMO:
			currentSerial = row.serial
			currentAssetName = row.assetName
			totalDispatch = 0
			for eachrow in currentHourEMMO:
				if eachrow.serial <= currentSerial and eachrow.assetName == currentAssetName : ## and eachrow.Import_Export != "E":
					totalDispatch = totalDispatch + eachrow.availablePower
				
			row.totalDisp_eachMPID = totalDispatch
			
		return currentHourEMMO
		
	
	
	def determineTotalExport(currentHourEMMO):
		exportServices = [bc_exp, sk_exp, mt_exp]
		currentHourExports = dict()
		for exportService in exportServices:
			# currentSerial = row.serial
			# currentAssetName = row.assetName
			totalExportMW = 0
			for eachrow in currentHourEMMO:
				if eachrow.Import_Export == "E" and eachrow.assetName == exportService:
					totalExportMW = totalExportMW + eachrow.availablePower
			# currentHourExports.append(export(mpidName, totalExportMW))
			currentHourExports[exportService] = totalExportMW
			
		return currentHourExports
	
	
	def processEMMO(currentHourEMMO, currentHourExports):
		# tie_list = [bc_imp, sk_imp, mt_imp]
		
		P_export = dict()
		P_import = dict()
		P_netImportExport = dict()
		
		P_export[bc_imp] = -currentHourExports[bc_exp]
		P_import[bc_imp] = 0
		P_netImportExport[bc_imp] = P_import[bc_imp] + P_export[bc_imp]
		
		P_export[sk_imp] = -currentHourExports[sk_exp]
		P_import[sk_imp] = 0
		P_netImportExport[sk_imp] = P_import[sk_imp] + P_export[sk_imp]
		
		P_export[mt_imp] = -currentHourExports[mt_exp]
		P_import[mt_imp] = 0
		P_netImportExport[mt_imp] = P_import[mt_imp] + P_export[mt_imp]
		
		for row in currentHourEMMO:
			# if row.relatedImportAsset == eachTie:
			if row.assetName == bc_imp:
				row.P_export = P_export[bc_imp]
				row.P_import = P_import[bc_imp] + row.availablePower
				row.P_netImportExport = row.P_import + row.P_export
			elif row.assetName == bc_exp:
				row.P_export = P_export[bc_imp] + row.availablePower
				row.P_import = P_import[bc_imp]
				row.P_netImportExport = row.P_import + row.P_export
				
			elif row.assetName == sk_imp:
				row.P_export = P_export[sk_imp]
				row.P_import = P_import[sk_imp] + row.availablePower
				row.P_netImportExport = row.P_import + row.P_export
			elif row.assetName == sk_exp:
				row.P_export = P_export[sk_imp] + row.availablePower
				row.P_import = P_import[sk_imp]
				row.P_netImportExport = row.P_import + row.P_export
				
			elif row.assetName == mt_imp:
				row.P_export = P_export[mt_imp]
				row.P_import = P_import[mt_imp] + row.availablePower
				row.P_netImportExport = row.P_import + row.P_export
			elif row.assetName == mt_exp:
				row.P_export = P_export[mt_imp] + row.availablePower
				row.P_import = P_import[mt_imp]
				row.P_netImportExport = row.P_import + row.P_export
			
			P_export[bc_imp] = row.P_export
			P_import[bc_imp] = row.P_import
			P_netImportExport[bc_imp] = row.P_netImportExport
			
			P_export[sk_imp] = row.P_export
			P_import[sk_imp] = row.P_import
			P_netImportExport[sk_imp] = row.P_netImportExport
			
			P_export[mt_imp] = row.P_export
			P_import[mt_imp] = row.P_import
			P_netImportExport[mt_imp] = row.P_netImportExport
		
				
		return currentHourEMMO
	
	if len(currentHourEMMO) != 0:
		currentHourEMMO = determineMPIDTotalDispatch(currentHourEMMO)
		currentHourExports = determineTotalExport(currentHourEMMO)
		currentHourEMMO = processEMMO(currentHourEMMO, currentHourExports)
	
	else:
		raise MissingDataError("EMMO", time)
		
		
	return currentHourEMMO, currentHourExports
			


def readMonthlyEMMO(excelFile): ## Updated by SDR
	wb = xlrd.open_workbook(excelFile)
	sheetName = "EMMO"
	ws = wb.sheet_by_name(sheetName)
	monthlyEMMOList = []
	for row in range(1, ws.nrows):
		# print "VAL :", col2num('A')
		assetName = str(ws.cell(row, col2num('E')).value)
		relatedImportAsset = ""
		P_export = 0
		P_import = 0 
		P_netImportExport = 0
			
		# print assetName
		# raw_input()
		#### here assetName means MPID Name in Mapping File
		# print assetName
		# raw_input()
		if assetName != 'N/A':
			if assetName == bc_exp:
				relatedImportAsset = bc_imp
			elif assetName == sk_exp:
				relatedImportAsset = sk_imp
			elif assetName == mt_exp:
				relatedImportAsset = mt_imp
			elif assetName == bc_imp:
				relatedImportAsset = bc_imp
			elif assetName == sk_imp:
				relatedImportAsset = sk_imp
			elif assetName == mt_imp:
				relatedImportAsset = mt_imp
			else:
			# relatedImportAsset == ""
				relatedImportAsset = ""
			
			
			time = ws.cell(row, col2num('B')).value
			# # t1 = time.split(":")[0]
			# # t2 = time.split(":")[1]
			# # t3 = time.split(":")[2]
			# # t4 = time.split(":")[3]
			
			# # t2 = str(int(t2) + 1)
			# # t2 = t2.zfill(2)
			
			# # time = t1 + ":" + t2 + ":" + t3 +":" + t4
			# serial = ws.cell(row, col2num('C')).value
			serial = ''
			assetShortName = str(ws.cell(row, col2num('D')).value)
			Import_Export = ws.cell(row, col2num('F')).value
			blockNumber = int(ws.cell(row, col2num('G')).value)
			flexibility = int(ws.cell(row, col2num('H')).value)
			offerPrice = float(ws.cell(row, col2num('I')).value )
			blockSize = int(ws.cell(row, col2num('J')).value)
			
			availablePower = float(ws.cell(row, col2num('K')).value)
			dispatchedPower = float(ws.cell(row, col2num('L')).value)
			capabilityMW = (ws.cell(row, col2num('M')).value)
			
			if Import_Export == "E":
				availablePower = -availablePower
				dispatchedPower = -dispatchedPower
			
			# totalDisp_eachMPID = ws.cell(row, col2num('N')).value
			totalDisp_eachMPID = 0
			# print time, serial, assetName, Import_Export, blockNumber, flexibility, offerPrice, blockSize, availablePower, dispatchedPower, capabilityMW, totalDisp_eachMPID
			# raw_input()
			monthlyEMMOList.append(meritorderData(time, serial, assetName, relatedImportAsset, assetShortName, Import_Export, blockNumber, flexibility, offerPrice, blockSize, availablePower, dispatchedPower, capabilityMW, totalDisp_eachMPID, P_export, P_import, P_netImportExport))
	return monthlyEMMOList
######################################
#######################################
def SelectMonthlyLoad(loadDataFiles, month):
	desiredFile = ''
	for excelFile in loadDataFiles:
		filename = os.path.splitext(os.path.split(excelFile)[1])[0].lower()
		filename = filename.split("_")[2]
		if month.lower() in filename:
			desiredFile = excelFile
		# if month.lower() in os.path.splitext(os.path.split(excelFile)[1])[0].lower():
			# desiredFile = excelFile
			
	return desiredFile

	
	
class loadData(object):  ## Updated by SDR
	def __init__(self, mpidName, mwData):
		self.mpidName = mpidName
		self.mwData = mwData
	
def readMonthlyLoad(excelFile):
	wb = xlrd.open_workbook(excelFile)
	ws = wb.sheet_by_name("Load (MW)")
	monthlyLoadData = []
	
	times = []
	# enable_print()
	# print ws.ncols
	# raw_input()
	for col in range(col2num('B'), ws.ncols):
		date = (ws.cell(0, col).value)
		# print date
		# raw_input()
		temp = xlrd.xldate.xldate_as_datetime(date, wb.datemode)
		# print temp.strftime("%d%b%Y:%H:%M:%S")
		
		dt = datetime.timedelta(hours=1)
		time = temp - dt
		time = time.strftime("%d%b%Y:%H:%M:%S")
		time = time.upper()
		# print time
		# raw_input(time)
		# year = temp[0]
		# month = temp[1]
		# day = temp[2]
		
		# hour = str(ws.cell(2, col).value)
		# time = year+month+day+":"+hour
		times.append(time)
	# raw_input("END")
	for row in range(3, ws.nrows):
		mpidName = str(ws.cell(row, 0).value)
		if type(mpidName) is float or type(mpidName) is int:
			mpidName = str(int(mpidName))
		else:
			mpidName = str(mpidName)
		# if type(mpidName) is int:
			# mpidName = str(int(mpidName))
			
		mpidName = mpidName.replace('MP_', '')
		# enable_print()
		# print mpidName
		# raw_input()
		mwData = dict()
		# raw_input()
		if mpidName != '':
			for col in range(col2num('B'), ws.ncols):
				k = col2num('B')
				# print row, col, [col-k]
				# print times[col-k]
				mwValue = ws.cell(row, col).value
				if mwValue != '':
					mwData[times[col-k]] = float(mwValue)
				else:
					mwData[times[col-k]] = 0
			# enable_print()
			# print mpidName, mwData
			# raw_input()
			monthlyLoadData.append(loadData(mpidName, mwData))
	# raw_input()		
	return monthlyLoadData

def createHourlyLoad(monthlyLoad, time):
	currentHourLoad = []
	if len(monthlyLoad) == 0:
		raise MissingDataFileError("Missing Monthly Load (MW) Data")
	for row in monthlyLoad:
		mpidName = row.mpidName
		
		if time in row.mwData.keys():
			mwData = row.mwData[time]
		else:
			raise MissingDataError("Load (MW) Data", time)
		
		currentHourLoad.append(loadData(mpidName, mwData))
	
	# if len(currentHourLoad) == 0:
		# raise MissingDataError("Load Data")
	
	
	return currentHourLoad
#############################################
	
	
#############################################




def SelectMonthlyTSS(tssDataFiles, month):
	desiredFile = ''
	for excelFile in tssDataFiles:
		filename = os.path.splitext(os.path.split(excelFile)[1])[0].lower()
		filename = filename.split("_")[2]
		if month.lower() in filename:
			desiredFile = excelFile
		# if month.lower() in os.path.splitext(os.path.split(excelFile)[1])[0].lower():
			# desiredFile = excelFile
			
	return desiredFile

class tssData(object):  ## Updated by SDR
	def __init__(self, mpidName, mwData):
		self.mpidName = mpidName
		self.mwData = mwData

def readMonthlyTSS(excelFile):
	wb = xlrd.open_workbook(excelFile)
	ws = wb.sheet_by_name("TSS")
	monthlyTSSData = []
	
	times = []
	for col in range(col2num('C'), ws.ncols):
	# for col in range(col2num('C'), col2num('AB')):
		date = str(ws.cell(0, col).value)
		if date != "":
			date = (ws.cell(0, col).value)
			temp = xlrd.xldate.xldate_as_datetime(date, wb.datemode)
			time = temp.strftime("%d%b%Y:%H:%M:%S")
			time = time.upper()
			
			# print time
			# hour = int(ws.cell(1, col).value)
			# hour = str(hour)
			# hour = hour.zfill(2)
			
			# print date, hour
			# raw_input()
			# # # hour = (ws.cell(1, col).value).strip(" ")
			# temp = date.split("-")
			# yr = temp[0]
			# month = temp[1]
			# day = temp[2]
			# dt = yr+"-"+month+"-"+day+"-"+hour
			
			
			# date_time = datetime.datetime.strptime(dt, "%Y-%m-%d-%H")
			# # temp = xlrd.xldate.xldate_as_datetime(date, wb.datemode)
			# time = date_time.strftime("%d%b%Y:%H:%M:%S")
			# time = time.upper()
			# print time
			# # raw_input(time)
			times.append(time)
	
	# raw_input("END-----")
	
	for row in range(2, ws.nrows):
		mpidName = str(ws.cell(row, 1).value)
		isdtag = str(ws.cell(row, 0).value)
		# mpidName = mpidName.replace('MP_', '')
		mwData = dict()
		# if mpidName != '' and (isdtag == 'ISD' or isdtag == 'ISD-New'):
		if mpidName != '' :
			for col in range(col2num('C'), ws.ncols):
				k = col2num('C')
				# print row, col, [col-k]
				# print mpidName, times[col-k], ws.cell(row, col).value
				mwValue = ws.cell(row, col).value
				if mwValue != '':
					mwData[times[col-k]] = ws.cell(row, col).value
				else:
					mwData[times[col-k]] = 0
			# print mpidName, mwData
				monthlyTSSData.append(tssData(mpidName, mwData))
	# raw_input()		
	return monthlyTSSData

	
def createHourlyTSS(monthlyTSS, time):
	currentHourTSS = []
	# enable_print()
	
	if len(monthlyTSS) == 0:
		raise MissingDataFileError("Missing Monthly TSS Data File")
	
	for row in monthlyTSS:
		mpidName = row.mpidName
		
		if time in row.mwData.keys():
			mwData = row.mwData[time]
		else:
			raise MissingDataError("TSS Data", time)
		
		currentHourTSS.append(tssData(mpidName, mwData))
		
	
		
	return currentHourTSS

################################
######################################
####################
def file_list_(source_path, ext):
	eu = ext.upper()
	base_names = [s for s in os.listdir(source_path) if s.upper().endswith(eu)]
	abs_paths = [os.path.abspath(os.path.join(source_path, s)) for s in base_names]
	return base_names, abs_paths

def ensure_dir(f):
	try:
		os.makedirs(f)
	except OSError:
		if not os.path.isdir(f):
			raise
			
def log_print(log):
	sys.stdout = log
def enable_print():
	sys.stdout = sys.__stdout__

def remove_duplicates(values):
	output = []
	seen = set()
	for value in values:
		# If value has not been encountered yet,
		# ... add it to both list and set.
		if value not in seen:
			output.append(value)
			seen.add(value)
	return output
	
	
	
# def ilf_main(caseFiles, GenMappingFiles, EMMOFiles, loadDataFiles, loadMappingFiles, tssDataFiles, dates, months):
# def ilf_main(dates, months):
def ilf_main(dates, months, year, inputdir, Selected_Lf_Process):

	enable_print()
	# psspy.psseinit(100000)

	global flowTolerance
	global roundingdecimalpoint
	global iterationLimit
	iterationLimit = 50
	flowTolerance = 0.005
	roundingdecimalpoint = 2
	
	#global ab_bc
	#ab_bc = [[329, 819, '87'], [232, 1501, '86'], [456, 90000, '01']]
	
	#global skTie_bus
	#global skTie_loadId
	#skTie_bus = 1473
	#skTie_loadId = '34'
	
	#global mtTie_genbus
	#global mtTie_genId
	#mtTie_bus = 80000
	#mtTie_genId = "1"
	
	global sk_exp
	global bc_exp
	global mt_exp
	global sk_imp
	global bc_imp
	global mt_imp
	
	sk_exp = "SPCEXP"
	bc_exp = "BCHEXP"
	mt_exp = "MTEXP"
	sk_imp = "SPCIMP"
	bc_imp = "BCHIMP"
	mt_imp = "120SIMP"
	# inputdir = r"C:\Users\sdr\Desktop\ilf\Input Files"
	inputdir = str(inputdir)
	
	dir_case = os.path.join(inputdir, 'Cases')
	dir_emmo = os.path.join(inputdir, 'EMMO')
	if Selected_Lf_Process == "Forecast":
		year_historical = year - 2
		dir_emmo_historical = os.path.join(inputdir, 'Historical EMMO')
	else:
		year_historical = year
		dir_emmo_historical = dir_emmo
	dir_genMapping = os.path.join(inputdir, 'Mapping Information')
	# dir_loadMapping = os.path.join(inputdir, 'Load Mapping')
	dir_tssData = os.path.join(inputdir, 'TSS Data')
	dir_loadData = os.path.join(inputdir, 'Load Data')
	dir_grossloadData = os.path.join(inputdir, 'Gross Load Data')
	
	# dir_case = os.path.join(os.getcwd(), 'Input Files\\Cases')
	# dir_emmo = os.path.join(os.getcwd(), 'Input Files\\EMMO')
	# dir_genMapping = os.path.join(os.getcwd(), 'Input Files\\Gen Mapping')
	# dir_loadMapping = os.path.join(os.getcwd(), 'Input Files\\Load Mapping')
	# dir_tssData = os.path.join(os.getcwd(), 'Input Files\\TSS Data')
	# dir_loadData = os.path.join(os.getcwd(), 'Input Files\\Load Data')
	
	fullMonths = []
	for date in dates:
		fullMonths.append(date.strftime("%B"))
	fullMonths = remove_duplicates(fullMonths)
	# year = 2015
	# print fullMonths
	# raw_input()
	
	GenMappingFiles = []
	EMMOFiles = []
	Historical_EMMOFiles = []
	caseFiles = []
	loadDataFiles = []
	loadMappingFiles = []
	tssDataFiles = []
	grossLoadDataFiles = []
	
	for each in fullMonths:
		GenMappingFile = os.path.join(dir_genMapping, '%sLF_Mapping Information_%s.xlsx' %(year,each))
		if os.path.isfile(GenMappingFile):
			GenMappingFiles.append(GenMappingFile)
		else:
			print "\nERROR ---- File Missing : ", GenMappingFile
			raise 
		
		EMMOFile = os.path.join(dir_emmo, '%sLF_EMMO_%s.xlsx' %(year,each))
		if os.path.isfile(EMMOFile):
			EMMOFiles.append(EMMOFile)
		else:
			print "\nERROR ---- File Missing : ", EMMOFile
			raise
		
		Historical_EMMOFile = os.path.join(os.path.join(dir_emmo_historical, '%sLF_EMMO_%s.xlsx' %(year_historical,each)))
		if os.path.isfile(Historical_EMMOFile):
			Historical_EMMOFiles.append(Historical_EMMOFile)
		else:
			print "\nERROR ---- File Missing : ", Historical_EMMOFile
			raise
		
		
		
		loadDataFile = os.path.join(dir_loadData, '%sLF_Load Data_%s.xlsx' %(year,each))
		if os.path.isfile(loadDataFile):
			loadDataFiles.append(loadDataFile)
		else:
			print "\nERROR ---- File Missing : ", loadDataFile
			raise
		
		tssDataFile = os.path.join(dir_tssData, '%sLF_TSS Volumes_%s.xlsx' %(year_historical,each))
		if os.path.isfile(tssDataFile):
			tssDataFiles.append(tssDataFile)
		else:
			print "\nERROR ---- File Missing : ", tssDataFile
			raise
	
	# dates = [datetime.datetime(2017,8,10,14,00), datetime.datetime(2017,8,20,06,00)]
	# dates = [datetime.datetime(2017,8,10,06,00), datetime.datetime(2017,8,15,06,00), datetime.datetime(2017,8,05,06,00)]
	# months = [desiredMonth,]
	print "========================================"
	print "Input Files : \n"
	# print caseFiles
	print "\nMappingFiles : ", 
	print GenMappingFiles
	
	print "\nEMMOFiles : "
	print EMMOFiles
	
	print "\nHistorical_EMMOFiles : "
	print Historical_EMMOFiles
	
	print "\nloadDataFiles : "
	print loadDataFiles
	
	print "\ntssDataFiles : "
	print tssDataFiles
	print "================================="
	
	
	ProcessFiles(caseFiles, GenMappingFiles, EMMOFiles, Historical_EMMOFiles, loadDataFiles, loadMappingFiles, tssDataFiles, grossLoadDataFiles, dates, months, Selected_Lf_Process, year)
	enable_print()
	
	
	print "++++++++++++++++++++++++++++++++++++ END OF PROGRAM +++++++++++++++++++++++++++++++++++"
	# sys.exit()
   
   
# months = ['MAY',]
# dates = [datetime.datetime(2018,5,2,9,00),]
# inputdir = r"C:\Users\sdr\Desktop\ILF\6139\Teshmont's Produced Data\6-Input Files"
# ilf_main(dates, months, inputdir)  



# main()   
# if __name__ == "__main__":
    # main()