import os
import sys
import xlrd
import shutil
# import xlutils
import openpyxl
from openpyxl.styles import Font, PatternFill
from openpyxl.styles.colors import RED, BLUE, GREEN, YELLOW, DARKGREEN

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 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, munit2, comments, solutionCheck, color, comment, totalSystemLoad1, totalDemand1):
		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 = munit1
		self.munit2 = munit2
		self.comments = comments
		self.solutionCheck = solutionCheck
		self.color = color
		self.comment = comment
		self.totalSystemLoad1 = totalSystemLoad1
		self.totalDemand1 = totalDemand1

def writeResults(resultsData, outputWB, output_Final, errorCodes, monthRange):
	wb_readonly = openpyxl.load_workbook(outputWB, data_only = True)
	# 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
	winfo_max_row = winfo.max_row
	winfo_start = 8
	print "\nNumber of MPIDs = ", winfo_max_row-8+1
	# raw_input()
	
	mpids = []
	
	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)
	
	########### 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 = 2			
		# for col in range(starting_col, ending_col+1):
		
		if calculationYear %4 == 0:
			ending_col = 8760+1+24
		else:
			ending_col = 8760+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)
			date = date.strftime("%Y/%m/%d %H:%M")
			# print wsrd2[cellIndex].fill
			# raw_input()
			
			# date = roundTime(date, roundTo=60)
			# date = date.strftime("%Y/%m/%d %H:%M")
			value_row = 3
			loss1_cell = get_column_letter(col) + str(value_row)
			mu_cell = get_column_letter(col) + str(value_row+1)
			# 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":
					# w2[loss1_cell] = eachData.Loss1
					wsrd2[loss1_cell] = eachData.Loss1
					# w2[mu_cell] = eachData.munit1
					wsrd2[mu_cell] = eachData.munit1
					wsrd2[totalDemand1_cell] = eachData.totalDemand1
					# else:
						# w2[loss1_cell] = ""
						# w2[mu_cell] = ""
					# w2[loss1_cell].fill = customcolors["GREEN"]
					# wsrd2[loss1_cell].fill = customcolors["GREEN"]
					# w2[mu_cell].fill = customcolors["GREEN"]
					# wsrd2[mu_cell].fill = customcolors["GREEN"]
					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 = 3			
		sum_ntgs = dict()
		saveInitialVolume = dict()
		
		starting_col = 2
		ending_col = 2880
		
		if calculationYear %4 == 0:
			if monthRange == "Jan-Apr":
				starting_col = 2
				ending_col = 2880+24
			elif monthRange == "May-Aug":
				starting_col = 2881+24
				ending_col = 5832+24
			elif monthRange == "Sep-Dec":
				starting_col = 5833+24
				ending_col = 8761+24
			else:
				starting_col = 2
				ending_col = 2880+24
		else:
			if monthRange == "Jan-Apr":
				starting_col = 2
				ending_col = 2880
			elif monthRange == "May-Aug":
				starting_col = 2881
				ending_col = 5832
			elif monthRange == "Sep-Dec":
				starting_col = 5833
				ending_col = 8761
			else:
				starting_col = 2
				ending_col = 2880
			
		print ending_col
		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)
			date = (wsrd3[cellIndex].value)
			# date = roundTime(date, roundTo=60)
			date = date.strftime("%Y/%m/%d %H:%M")
			# print(col, cellIndex, 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 = 4
				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.comment == "XA-UNSOL2" or eachData.comment == "XA-INSUF2":
										wsrd3[cell] = eachData.comment
									else:
										wsrd3[cell] = eachData.Loss2
										wsrd1[cell] = eachData.ntg1 - eachData.ntg2
										sum_ntg += eachData.ntg1 - eachData.ntg2
								else:
									# if eachData.color == "YELLOW":
									wsrd3[cell] = eachData.comment
									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.comment == "XA-UNSOL2" or eachData.comment == "XA-INSUF2":
										wsrd3[cell] = eachData.comment
									else:
										wsrd3[cell] = eachData.Loss2
										wsrd1[cell] = eachData.ntg1 - eachData.ntg2
										sum_ntg += eachData.ntg1 - eachData.ntg2
									
								else:
									# if eachData.color == "YELLOW":
									wsrd3[cell] = eachData.comment
									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 = 4
				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
					
						
		# 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):
# 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):
				# for col in range(0, sheet_xls.ncols):
				mpidName = ws.cell(row, 0).value
				ntg1 = ws.cell(row, 1).value
				ntg2 = ws.cell(row, 10).value
				
				total_loss1 = ws.cell(row, 2).value
				Loss1 = ws.cell(row, 4).value
				
				total_loss2 = ws.cell(row, 11).value
				Loss2 = ws.cell(row, 13).value
				
				munit1 = ws.cell(row, 5).value
				munit2 = ws.cell(row, 14).value
				comments = ws.cell(row, 21).value
				solutionCheck = ws.cell(row, 22).value
				totalSystemLoad1 = ws.cell(row, 23).value
				totalDemand1 = totalSystemLoad1 + total_loss1
				# ntg = ''
				# if ntg2 != '-':
					# ntg = ntg1 - ntg2 
				# else:
					# ntg = ntg1
				
				# if Loss2 == '-':
					# Loss2 = ''
				# if munit2 == '-':
					# munit2 = 'N/A'
				
				# print comments
				colorCode = ""
				# if "Error Ocurred while preparing State-2" in comments:
					# color = "RED"
				# if "Initial NTG is less than 1 MW" in comments:
				if "NTG < 1 MW" in comments:
					colorCode = "YELLOW"
					yellowcounts += 1 
					comment = "XS-NODISP"
				elif "State-2 Successful" in comments:
					colorCode = "GREEN"
					greencounts += 1 
					# comment = "XS-NODISP"
				elif "XS-UNSOL2" in  comments:
					colorCode = "RED"
					redcounts += 1
					# comment = "XS-UNSOL2"
					comment = "XS-UNSOL2"
					UNSOL2tag = "ON" 
				elif "XA-INSUF2" in comments:
					colorCode = "BLUE"
					bluecounts += 1
					# comment = "XS-UNSOL2"
					comment = "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, munit2, comments, solutionCheck, colorCode, comment, totalSystemLoad1, totalDemand1))
			
			if INSUF2tag == "ON" :
				for ilfresult in ilfresults:
					if ilfresult.color == "GREEN":
						ilfresult.comment = "XA-INSUF2"
					elif ilfresult.color == "RED":
						ilfresult.comment = "XA-INSUF2"
						ilfresult.color = "GREEN"
			else:
				if UNSOL2tag == "ON" :
					for ilfresult in ilfresults:
						if ilfresult.color == "GREEN":
							ilfresult.comment = "XA-UNSOL2"
						
						
						
						
			fullresults[date_time] = ilfresults
		
		# 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 - " %calculationYear + timeNow
		writeResults(fullresults, templateFile, output_Final, errorCodes, monthRange)
	else:
		print "\nNo State-2 result files found"
	
	print "===================END OF PROGRAM===================="
	
	
# main()
	
	
	
	
	
	
	
	
	
	
	
	