import os
import sys
import xlrd
import shutil
import string
# import xlutils
import openpyxl

from openpyxl.styles import Font, PatternFill
from openpyxl.styles.colors import RED, BLUE, GREEN, YELLOW, DARKGREEN

from openpyxl.utils import get_column_letter, column_index_from_string

import datetime, time
from datetime import timedelta as td

# 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


redfill = PatternFill("solid", fgColor=RED)
bluefill = PatternFill("solid", fgColor=BLUE)
greenfill = PatternFill("solid", fgColor=GREEN)
yellowfill = PatternFill("solid", fgColor=YELLOW)
dgreenfill = PatternFill("solid", fgColor=DARKGREEN)
customcolors = {"RED": redfill, "GREEN": greenfill, "BLUE": bluefill, "YELLOW":yellowfill, "DARKGREEN":dgreenfill}

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 col2num(c):
	column = column_index_from_string(c)-1
	return column


def roundTime(dt=None, roundTo=60):
	"""Round a datetime object to any time lapse in seconds
	dt : datetime.datetime object, default now.
	roundTo : Closest number of seconds to round to, default 1 minute.
	"""
	if dt == None : 
		dt = datetime.datetime.now()
	seconds = (dt.replace(tzinfo=None) - dt.min).seconds
	rounding = (seconds+roundTo/2) // roundTo * roundTo
	
	return dt + datetime.timedelta(0,rounding-seconds,-dt.microsecond)






def ensure_dir(f):
	try:
		os.makedirs(f)
	except OSError:
		if not os.path.isdir(f):
			raise

class outputData(object):  ## Updated by SDR
	def __init__(self, mpidName, ntg1, ntg2, total_loss1, Loss1, total_loss2, Loss2, munit1_mpidName, munit1_blockNumber, munit1_offerPrice, munit1_availablePower, munit2_mpidName, comments, solutionCheck, color, workbookCode, s1_totalLoad, s1_totalGen, s1_total_emmoDispatch):
		
		self.mpidName = mpidName
		self.ntg1 = ntg1
		self.ntg2 = ntg2
		self.total_loss1 = total_loss1
		self.Loss1 = Loss1
		self.total_loss2 = total_loss2
		self.Loss2 = Loss2
		
		self.munit1_mpidName = munit1_mpidName
		self.munit1_blockNumber = munit1_blockNumber
		self.munit1_offerPrice = munit1_offerPrice
		self.munit1_availablePower = munit1_availablePower
		
		self.munit2_mpidName = munit2_mpidName
		
		self.comments = comments
		self.solutionCheck = solutionCheck
		self.color = color
		self.workbookCode = workbookCode
		
		self.s1_totalLoad = s1_totalLoad
		self.s1_totalGen = s1_totalGen
		self.s1_total_emmoDispatch = s1_total_emmoDispatch

def writeResults(resultsData, outputWB, output_Final, errorCodes, monthRange, calculationYear, winfo_max_row):
	wb_readonly = openpyxl.load_workbook(outputWB, data_only = True)
	print "Writing Results ...... "
	# wb = openpyxl.load_workbook(outputWB, data_only = False)

	# w1 = wb.get_sheet_by_name("8(5) Volume Increase (MW)")
	# w2 = wb.get_sheet_by_name("8(4) Initial Losses (MW)")
	# w3 = wb.get_sheet_by_name("8(5) Redispatched Losses (MW)")
	
	# winfo = wb.get_sheet_by_name("Facility Info Details")
	winfo = wb_readonly.get_sheet_by_name("5(1) Locations")
	
	wsrd1 = wb_readonly.get_sheet_by_name("8(5) Redispatched Volumes (MW)")
	wsrd2 = wb_readonly.get_sheet_by_name("8(4) Initial Losses (MW)")
	wsrd3 = wb_readonly.get_sheet_by_name("8(5) Redispatched Losses (MW)")
	wsrd4 = wb_readonly.get_sheet_by_name("8(4) Initial Volumes (MW)")
	
	
	# winfo_max_row = 122
	# print winfo.max_row
	# raw_input()
	# winfo_max_row = winfo.max_row
	winfo_start = 10
	print "\nNumber of MPIDs = ", winfo_max_row-9
	# raw_input()
	
	mpids = []
	
	# global date_row
	# global value_row
	
	# date_row = 9
	# value_row = 10
	
	
	for row in range(winfo_start, winfo_max_row+1):
		cellindex = 'A' + str(row)
		temp_mpidname = winfo[cellindex].value
		if type(temp_mpidname) is int or type(temp_mpidname) is float:
			temp_mpidname = str(int(temp_mpidname))
		else:
			temp_mpidname = str(temp_mpidname)
		mpids.append(temp_mpidname)
		
		
		# print temp_mpidname
		# raw_input()
	
	########### Write into 'Loss-1' Sheet - Loss 1 and MU1
	def writeLoss1(sum_ntgs):
		print "\n===Writing into Loss-1 Sheet....."
		starting_col =  2
		# ending_col = wsrd2.max_column-1
		date_row = 9			
		# for col in range(starting_col, ending_col+1):
		
		if calculationYear < 2017:
			if calculationYear %4 == 0:
				ending_col = col2num("LXY")+1+24
			else:
				ending_col = col2num("LXY")+1
		else:
			ending_col = col2num("LXY")+1
		
		# for col in range(starting_col, 8762):
		for col in range(starting_col, ending_col+1):
			cellIndex = get_column_letter(col) + str(date_row)
			date = (wsrd2[cellIndex].value)
			
			# print date.day, date.month, date.year, date.hour, date.minute
			date = roundTime(date, roundTo=60)
			# print date
			date = date.strftime("%Y/%m/%d %H") + ":00"
			# print(col, cellIndex, date)
			# print date
			# raw_input()
			
			
			# date = roundTime(date, roundTo=60)
			# date = date.strftime("%Y/%m/%d %H:%M")
			value_row = 10
			loss1_cell = get_column_letter(col) + str(value_row)
			# mu_cell = get_column_letter(col) + str(value_row+1)
			munit1_mpidName_cell = get_column_letter(col) + str(value_row+1)
			munit1_blockNumber_cell = get_column_letter(col) + str(value_row+2)
			munit1_offerPrice_cell = get_column_letter(col) + str(value_row+3)
			munit1_availablePower_cell = get_column_letter(col) + str(value_row+4)
			s1_total_emmoDispatch_cell = get_column_letter(col) + str(value_row+5)
			s1_totalGen_cell = get_column_letter(col) + str(value_row+6)
			
			# print date
			# raw_input()
			# totalNTG_cell = get_column_letter(col) + str(value_row+2)
			# totalDemand1_cell = get_column_letter(col) + str(value_row+2)
			if date in resultsData.keys():
				# print date
				data = resultsData[date]
				# wsrd2[totalNTG_cell] = sum_ntgs[date]
				
				for eachData in data:
					# if eachData.color == "GREEN":
					wsrd2[loss1_cell] = eachData.Loss1
					wsrd2[munit1_mpidName_cell] = eachData.munit1_mpidName
					wsrd2[munit1_blockNumber_cell] = eachData.munit1_blockNumber
					wsrd2[munit1_offerPrice_cell] = eachData.munit1_offerPrice
					wsrd2[munit1_availablePower_cell] = eachData.munit1_availablePower
					
					wsrd2[s1_total_emmoDispatch_cell] = eachData.s1_total_emmoDispatch
					wsrd2[s1_totalGen_cell] = eachData.s1_totalGen
					# wsrd2[totalDemand1_cell] = eachData.totalDemand1
					
					break
				
	
	
	########### Write into 'Loss-2' Sheet########################
	def writeNtg_Loss2():
		print "\n===Writing into NTG and Loss-2 Sheet....."
		# starting_col =  2
		# ending_col = wsrd3.max_column-1
		date_row = 9			
		sum_ntgs = dict()
		saveInitialVolume = dict()
		
		starting_col = col2num("B")+1
		ending_col = col2num("DFU")+1
		
		if calculationYear < 2017:
			if calculationYear %4 == 0:
				if monthRange == "Jan-Apr":
					starting_col = col2num("B")+1
					ending_col = col2num("DFU")+1+24
				elif monthRange == "May-Aug":
					starting_col = col2num("DFV")+1+24
					ending_col = col2num("HPI")+1+24
				elif monthRange == "Sep-Dec":
					starting_col = col2num("HPJ")+1+24
					ending_col = col2num("LXY")+1+24
				else:
					starting_col = col2num("B")+1
					ending_col = col2num("DFU")+1+24
			else:
				if monthRange == "Jan-Apr":
					starting_col = col2num("B")+1
					ending_col = col2num("DFU")+1
				elif monthRange == "May-Aug":
					starting_col = col2num("DFV")+1
					ending_col = col2num("HPI")+1
				elif monthRange == "Sep-Dec":
					starting_col = col2num("HPJ")+1
					ending_col = col2num("LXY")+1
				else:
					starting_col = col2num("B")+1
					ending_col = col2num("DFU")+1
		else:
			if monthRange == "Jan-Apr":
				starting_col = col2num("B")+1
				ending_col = col2num("DFU")+1
			elif monthRange == "May-Aug":
				starting_col = col2num("DFV")+1
				ending_col = col2num("HPI")+1
			elif monthRange == "Sep-Dec":
				starting_col = col2num("HPJ")+1
				ending_col = col2num("LXY")+1
			else:
				starting_col = col2num("B")+1
				ending_col = col2num("DFU")+1
		# raw_input()
		# print starting_col
		# print ending_col
		# raw_input()
		for col in range(starting_col, ending_col+1):
		# for col in range(starting_col, 8762):
		# for col in range(2, 2881):  ### Jan-Apr
		# for col in range(2881, 5833): ### May-Aug
 		# for col in range(5833, 8762): ### Sep-Dec
			cellIndex = get_column_letter(col) + str(date_row)
			# print cellIndex
			# raw_input()
			date = (wsrd3[cellIndex].value)
			# print date.day, date.month, date.year, date.hour, date.minute
			date = roundTime(date, roundTo=60)
			# print date
			date = date.strftime("%Y/%m/%d %H") + ":00"
			# print(col, cellIndex, date)
			# print date
			# raw_input()
		
			
			
			if date in resultsData.keys():
				# print date
				# raw_input("FOUND")
				data = resultsData[date]
				lf_mpids = []
				sum_ntg = 0 
				saveInitialVolume = dict()
				for eachData in data:
					lf_mpids.append(eachData.mpidName)
				
				value_row = 10
				for mpid in mpids:
					cell = get_column_letter(col) + str(value_row)
					if mpid in lf_mpids:
						# if mpid == "CES2":
							# mpid = "CES1"
						for eachData in data:
							if eachData.mpidName == mpid:
								# if mpid in data.keys():
								if eachData.color == "GREEN":
									# w3[cell] = eachData.Loss2
									if eachData.workbookCode == "XA-UNSOL2" or eachData.workbookCode == "XA-INSUF2":
										wsrd3[cell] = eachData.workbookCode
									else:
										wsrd3[cell] = eachData.Loss2
										# wsrd1[cell] = eachData.ntg1 - eachData.ntg2
										wsrd1[cell] = eachData.ntg1
										# sum_ntg += eachData.ntg1 - eachData.ntg2
										sum_ntg += eachData.ntg1
								else:
									# if eachData.color == "YELLOW":
									wsrd3[cell] = eachData.workbookCode
									wsrd1[cell] = ""
									# w3[cell] = ""
									# wsrd3[cell] = ""
								if eachData.ntg1 > 0:
									wsrd4[cell] = eachData.ntg1
								else:
									wsrd4[cell] = ""
								
								saveInitialVolume[mpid] = wsrd4[cell].value
								# w3[cell].fill = customcolors[eachData.color]
								# wsrd3[cell].fill = customcolors[eachData.color]
								break
					elif mpid == "CES1/CES2":
						for eachData in data:
							if eachData.mpidName == "CES1":
								# if mpid in data.keys():
								if eachData.color == "GREEN":
									if eachData.workbookCode == "XA-UNSOL2" or eachData.workbookCode == "XA-INSUF2":
										wsrd3[cell] = eachData.workbookCode
									else:
										wsrd3[cell] = eachData.Loss2
										# wsrd1[cell] = eachData.ntg1 - eachData.ntg2
										wsrd1[cell] = eachData.ntg1
										# sum_ntg += eachData.ntg1 - eachData.ntg2
										sum_ntg += eachData.ntg1
									
								else:
									# if eachData.color == "YELLOW":
									wsrd3[cell] = eachData.workbookCode
									wsrd1[cell] = ""
									# w3[cell] = ""
									# wsrd3[cell] = ""
								if eachData.ntg1 > 0:
									wsrd4[cell] = eachData.ntg1
								else:
									wsrd4[cell] = ""
								saveInitialVolume[mpid] = wsrd4[cell].value
								# w3[cell].fill = customcolors[eachData.color]
								# wsrd3[cell].fill = customcolors[eachData.color]
								break
					else:
						if mpid == "BCHEXP" or mpid == "SPCEXP" or mpid == "MTEXP":
							wsrd3[cell] = ''
						else:
							wsrd3[cell] = "XS-MISSIN" 
							# wsrd3[cell] = '' 
					value_row += 1
				sum_ntgs[date] = sum_ntg
			
			elif date in errorCodes.keys():
				value_row = 10
				for mpid in mpids:
					cell = get_column_letter(col) + str(value_row)
					if mpid == "BCHEXP" or mpid == "SPCEXP" or mpid == "MTEXP":
						wsrd3[cell] = ''
					else:
						wsrd3[cell] = errorCodes[date]
						
						
					if col > 2:
						cell_ = get_column_letter(col-1) + str(value_row)
						wsrd4[cell] = wsrd4[cell_].value
					else:
						wsrd4[cell] = ""
					value_row += 1
			else:
				continue
				# print date, "Not Found"
						
		# starting_col = 2
		# for col in range(starting_col, 8762):
			# # print "==================="
			# value_row = 4
			# cell = get_column_letter(col) + str(value_row)
			# # print wsrd3[cell].value
			# if wsrd3[cell].value == "XA-UNSOL1":
				# # raw_input()
				# for mpid in mpids:
					# if col > 2:
						# cell_ = get_column_letter(col-1) + str(value_row)
						# # print cell_, wsrd4[cell_].value, 
						# # raw_input()
						# wsrd4[cell] = wsrd4[cell_].value
						
					# else:
						# wsrd4[cell] = ""
					# value_row += 1
			
			
		
		return sum_ntgs
	# writeNTG()
	# wb.save(output_Final + "_NTG.xlsx")

	
	# wb.save(output_Final + "_NTG_Loss1.xlsx")
	# raw_input("......")
	sum_ntgs = writeNtg_Loss2()
	writeLoss1(sum_ntgs)
	# wb.save(output_Final + ".xlsx")
	# raw_input("......")
	# wb.remove_sheet(w1)
	# wb.remove_sheet(w2)
	# wb.save(output_Final + "_Loss2.xlsx")
	# wb_readonly.save(output_Final + "_Loss2.xlsx")
	print "------Saving -----"
	wb_readonly.save(output_Final + ".xlsx")
	
	
	
	
	
def progress(toPrint):
	# time.sleep(1)
	sys.stdout.write("\r%s" %toPrint)
	sys.stdout.flush()

def adjust_DST(fullresults, dst_day1, dst_day2):
	hour = "02"
	dst_day1 = dst_day1 + " 02:00"
	dst_day2 = dst_day2 + " 02:00"
	# date_time = date.strftime("%Y/%m/%d %H:%M")
	
	
	

def collectresultFiles(sourceDir):
	# sourceDir =  os.path.join(os.getcwd(), "State-2 Results")
	# sourceDir =  os.path.join(os.getcwd(), r"2017 LF Results")
	# sourceDir =  os.path.dirname(sourceDir)
	sourceDir =  os.path.normpath(sourceDir)
	print "LF Results Path : ", 
	print sourceDir

	roots = []
	
	for root, dirs, files in os.walk(sourceDir):
		roots.append(root)
		# print root

	resultfiles = []
	
	for root in roots:
		filenames, filepaths = file_list_(root, ".xls")
		for file in filepaths:
			# shutil.copy(file, outDir)
			resultfiles.append(file)

	print "Number of output files : ", len(resultfiles)
	
	# raw_input(" ========================== ")
	print " ========================== "
	resultfiles.sort()
	# for result in resultfiles:
		# print result
		# raw_input()
	return resultfiles	

def collectErrorLogFiles(sourceDir):
	# sourceDir =  os.path.join(os.getcwd(), "State-2 Results")
	# sourceDir =  os.path.join(os.getcwd(), r"2017 LF Results")
	# sourceDir =  os.path.dirname(sourceDir)
	sourceDir =  os.path.normpath(sourceDir)
	print "LF Error Log Files Path : ", 
	print sourceDir

	roots = []
	
	for root, dirs, files in os.walk(sourceDir):
		roots.append(root)
		# print root

	resultfiles = []
	
	for root in roots:
		filenames, filepaths = file_list_(root, ".log")
		for file in filepaths:
			# shutil.copy(file, outDir)
			resultfiles.append(file)

	print "Number of Error Log files : ", len(resultfiles)
	
	# raw_input(" ========================== ")
	print " ========================== "
	resultfiles.sort()
	# for result in resultfiles:
		# print result
		# raw_input()
	errorLogFiles = resultfiles	
	return errorLogFiles	
	
def readState1ErrorLogfiles(logfiles):
	
	
	errorCodes = dict()

	for logfile in logfiles:
		
		with open(logfile) as f: 
			data = f.readlines() 
			
		for line in data:
			# print line
			# raw_input()
			words = line.split()
			dt = words[0]
			_time = words[1]
			dt_time = dt + " " + _time
			errorCode = words[2]
			
			# date = datetime.datetime.strptime(dt, "%d %b %Y %H")
			date_time = datetime.datetime.strptime(dt_time, "%Y/%m/%d %H:%M")
			date_time = date_time.strftime("%Y/%m/%d %H:%M")
			print date_time, errorCode
			errorCodes[date_time] = errorCode
			# raw_input()
	
	# print state1_munits
	return errorCodes
			

	
def ilf_main(sourceDir, templateFile, state1ErrorLogDir, monthRange, calculationYear, WB_maxRow):
# def main():
	
	# global calculationYear
	# calculationYear = 2016
	resultfiles = collectresultFiles(sourceDir)
	errorLogFiles = collectErrorLogFiles(state1ErrorLogDir)
	errorCodes = readState1ErrorLogfiles(errorLogFiles)
	# # sourceDir = r"C:\Users\sdr\Desktop\ILF SW Simulation\10 April 2017\DEC\State-2 1-5 -----\Outputs-10Apr2017-17-05"
	# dir = r"Results"
	# filelocation = os.path.join(os.getcwd(), dir)
	# filenames, filepaths = file_list_(filelocation, ".xls")
	# print filepaths
	# # raw_input()
	
	
	fullresults = dict()
	redcounts = 0
	yellowcounts = 0
	greencounts = 0 
	bluecounts = 0
	if len(resultfiles) != 0:
		print "\nCollecting Results....."
		# for filename, filepath in zip(filenames, filepaths):
		for result in resultfiles:
			# excelFile = "01_DEC_2017_Hour_00.xls"
			tempfilename = os.path.basename(result).split(".xls")[0]
			[day, month, year, temp, hour] = tempfilename.split("_")
			# print day, month, year, temp, hour
			# raw_input()
			dt = day+" "+month+" "+year+" "+hour 
			date = datetime.datetime.strptime(dt, "%d %b %Y %H")
			date_time = date.strftime("%Y/%m/%d %H:%M")
			# raw_input(date_time)
			
			progress(date_time)
			# raw_input()
			# ensure_dir(outDir)
			# print result
			wb = xlrd.open_workbook(result)
			ws = wb.sheet_by_name("Results")
			
			ilfresults = []
			UNSOL2tag = "OFF"
			INSUF2tag = "OFF"
			for row in range(1, ws.nrows):
				
				mpidName = ws.cell(row, col2num("A")).value
				
				################################### Redispatched State Info Extraction #############
				
				ntg1 = ws.cell(row, col2num("B")).value
				total_loss1 = ws.cell(row, col2num("E")).value
				Loss1 = ws.cell(row, col2num("G")).value
				
				munit1_mpidName = ws.cell(row, col2num("H")).value
				munit1_serialNumber = ws.cell(row, col2num("I")).value
				munit1_blockNumber = ws.cell(row, col2num("J")).value
				
				munit1_offerPrice = ws.cell(row, col2num("K")).value
				############# In 2018 EMMO blank cells found in offer price for FH1 and RB5; AESO decided to report "NA" in the workbook
				if munit1_offerPrice == '':
					# munit1_offerPrice = "NA"
					print munit1_mpidName, munit1_offerPrice
					raise
				
				
				munit1_availablePower = ws.cell(row, col2num("L")).value
				munit1_reserve = ws.cell(row, col2num("M")).value
				
				
				
				s1_case_totalLoad = ws.cell(row, col2num("R")).value
				s1_case_Generation = ws.cell(row, col2num("S")).value
				
				s1_btfGen_tobeExcluded = ws.cell(row, col2num("T")).value
				s1_btfLoad_tobeExcluded = s1_btfGen_tobeExcluded
				 
				s1_bcImport = ws.cell(row, col2num("U")).value
				s1_skImport = ws.cell(row, col2num("W")).value
				s1_mtImport = ws.cell(row, col2num("Y")).value
				
				s1_bcExport = ws.cell(row, col2num("V")).value
				s1_skExport = ws.cell(row, col2num("X")).value
				s1_mtExport = ws.cell(row, col2num("Z")).value
				
				maxExport_Offered = (ws.cell(row, col2num("AA")).value)
				curtailedExport = -(maxExport_Offered - (s1_bcExport + s1_skExport + s1_mtExport))
				
				s1_totalGen = s1_case_Generation + s1_bcImport + s1_skImport + s1_mtImport - s1_btfGen_tobeExcluded
				s1_totalLoad = s1_case_totalLoad + s1_bcExport + s1_skExport + s1_mtExport - s1_btfLoad_tobeExcluded
				s1_total_emmoDispatch = s1_totalGen + curtailedExport
				
				################################### Redispatched State Info Extraction #############
				ntg2 = ws.cell(row, col2num("AB")).value
				total_loss2 = ws.cell(row, col2num("AD")).value
				Loss2 = ws.cell(row, col2num("AF")).value
				
				munit2_mpidName = ws.cell(row, col2num("AG")).value
				munit2_serialNumber = ws.cell(row, col2num("AH")).value
				munit2_blockNumber = ws.cell(row, col2num("AI")).value
				munit2_offerPrice = ws.cell(row, col2num("AJ")).value
				munit2_availablePower = ws.cell(row, col2num("AK")).value
				munit2_reserve = ws.cell(row, col2num("AL")).value
				
				solutionCheck = ws.cell(row, col2num("BA")).value
				comments = ws.cell(row, col2num("BB")).value
				
				s2_bcTieMismatch = ws.cell(row, col2num("BF")).value
				s2_skTieMismatch = ws.cell(row, col2num("BG")).value
				s2_mtTieMismatch = ws.cell(row, col2num("BH")).value
				
				# print "ntg2, total_loss2, Loss2, munit2_mpidName, munit2_serialNumber, s2_bcTieMismatch"
				# print ntg2, total_loss2, Loss2, munit2_mpidName, munit2_serialNumber, s2_bcTieMismatch
				# raw_input()
				
				
				if solutionCheck == 'SOLVED':
					if abs(s2_bcTieMismatch) > 1 or abs(s2_skTieMismatch) > 1 or abs(s2_mtTieMismatch) > 1:
						print "================================================================"
						print "Tie Mismatch in Redisp State is Out of Limit (> 1 MW) .... !!!! "
						print "s2_bcTieMismatch 	s2_skTieMismatch	s2_mtTieMismatch"
						print s2_bcTieMismatch, "\t", s2_skTieMismatch, "\t", s2_mtTieMismatch
						print "================================================================"
						# raise 
				
				
				colorCode = ""
				# if "Error Ocurred while preparing State-2" in comments:
					# color = "RED"
				# if "Initial NTG is less than 1 MW" in comments:
				workbookCode = solutionCheck
				# print workbookCode
				# raw_input()
				if solutionCheck == "XS-NODISP":
					colorCode = "YELLOW"
					yellowcounts += 1 
					workbookCode = "XS-NODISP"
				elif solutionCheck == "SOLVED":
					colorCode = "GREEN"
					greencounts += 1 
					workbookCode = "XS-NODISP"
				elif solutionCheck == "XS-UNSOL2":
					colorCode = "RED"
					redcounts += 1
					# workbookCode = "XS-UNSOL2"
					workbookCode = "XS-UNSOL2"
					UNSOL2tag = "ON" 
				elif solutionCheck =="XS-INSUF2":
					colorCode = "BLUE"
					bluecounts += 1
					# workbookCode = "XS-UNSOL2"
					workbookCode = "XA-INSUF2"
					INSUF2tag = "ON" 
				# print colorCode
				# print mpidName, ntg1, ntg2, Loss1, Loss2, munit1, munit2, comments, solutionCheck, colorCode
				
				# raw_input()
				ilfresults.append(outputData(mpidName, ntg1, ntg2, total_loss1, Loss1, total_loss2, Loss2, 
				munit1_mpidName, munit1_blockNumber, munit1_offerPrice, munit1_availablePower, 
				munit2_mpidName, comments, solutionCheck, colorCode, workbookCode, s1_totalLoad, s1_totalGen, s1_total_emmoDispatch))
			
			if INSUF2tag == "ON" :
				for ilfresult in ilfresults:
					if ilfresult.color == "GREEN":
						ilfresult.workbookCode = "XA-INSUF2"
					elif ilfresult.color == "RED":
						ilfresult.workbookCode = "XA-INSUF2"
						ilfresult.color = "GREEN"
			else:
				if UNSOL2tag == "ON" :
					for ilfresult in ilfresults:
						if ilfresult.color == "GREEN":
							ilfresult.workbookCode = "XA-UNSOL2"
						
						
			# for ilfresult in ilfresults:
				# print ilfresult.mpidName, ilfresult.ntg1, ilfresult.ntg2, ilfresult.workbookCode
			# print date_time			
			fullresults[date_time] = ilfresults
			# raw_input(".....")
		# print "\nredcounts, yellowcounts, greencounts : ", redcounts, yellowcounts, greencounts
		
		# dst_day1 = "2017/3/12"
		# dst_day2 = "2017/11/05"
		# adjust_DST(fullresults, dst_day1, dst_day2)
		
		
		timeNow = datetime.datetime.now()
		timeNow = timeNow.strftime("%Y%b%d-%H%M")
		# outputWB = "2017 LF Results Template.xlsx"
		# outputWB = templateFile
		output_Final = "%s LF Results - %s " %(calculationYear, monthRange) + timeNow
		writeResults(fullresults, templateFile, output_Final, errorCodes, monthRange, calculationYear, WB_maxRow)
	else:
		print "\nNo State-2 result files found"
	
	print "===================END OF PROGRAM===================="
	
	
# main()
	
	
	
	
	
	
	
	
	
	
	
	