| 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592159315941595159615971598159916001601160216031604 | 
							- #!/usr/bin/env python
 
- # coding: utf-8
 
- import time
 
- import datetime
 
- import numpy as np
 
- import math
 
- from korean_lunar_calendar import KoreanLunarCalendar
 
- import configparser
 
- import pymssql
 
- from sklearn import ensemble
 
- from sklearn.model_selection import train_test_split
 
- ## Measure
 
- def MAPE(y_observed, y_pred):
 
-     return np.mean(np.abs((y_observed - y_pred) / y_observed)) * 100
 
- def MAE(y_observed, y_pred):
 
-     return np.mean(np.abs(y_observed - y_pred))
 
- def MBE(y_observed, y_pred):
 
-     return (np.sum((y_observed - y_pred))/(len(y_observed)*np.mean(y_observed)))*100
 
- def CVRMSE(y_observed, y_pred):
 
-     return (np.sqrt(np.mean((y_observed - y_pred)*(y_observed - y_pred)))/np.mean(y_observed))*100
 
- def Check_AlivedTimeStamp(RawData, ComparedData, idx_raw, idx_comp, unit):
 
-     if unit == 'daily':
 
-         if datetime.date(RawData[idx_raw].year, RawData[idx_raw].month, RawData[idx_raw].day) == datetime.date(ComparedData[idx_comp].year, ComparedData[idx_comp].month, ComparedData[idx_comp].day):
 
-             isAlived = True
 
-         else:
 
-             isAlived = False
 
-     elif unit == 'quarterly':
 
-         if datetime.datetime(RawData[idx_raw][4].year,RawData[idx_raw][4].month,RawData[idx_raw][4].day,RawData[idx_raw][4].hour,RawData[idx_raw][4].minute) == datetime.datetime(ComparedData[idx_comp].year, ComparedData[idx_comp].month, ComparedData[idx_comp].day,ComparedData[idx_comp].hour, ComparedData[idx_comp].minute):
 
-             isAlived = True
 
-         else:
 
-             isAlived = False
 
-     return isAlived
 
- def detect_unknown_duplicated_zero_data_for_faciilty(raw_Data, startday, lastday, Day_Period, OrgDataRes, isRecent):
 
- 	CumTime = datetime.datetime(int(startday.strftime('%Y')), int(startday.strftime('%m')), int(startday.strftime('%d')), 0, 0, 0)
 
- 	StandardTimeStamp_DayUnit = [CumTime]
 
- 	StandardTimeStamp_QuarterUnit = [CumTime]
 
- 	# Create intact time stamp 
 
- 	for idx_day in range(Day_Period):
 
- 		StandardTimeStamp_DayUnit.append(startday + datetime.timedelta(days=idx_day))
 
- 		if isRecent and idx_day == Day_Period-1:
 
- 			tmp_len = now.hour*4 + int(now.minute/15)
 
- 			for idx_time in range(tmp_len):
 
- 				CumTime += datetime.timedelta(minutes = 15)
 
- 				StandardTimeStamp_QuarterUnit.append(CumTime)
 
- 		else:
 
- 			for idx_time in range(OrgDataRes):
 
- 				CumTime += datetime.timedelta(minutes = 15)
 
- 				StandardTimeStamp_QuarterUnit.append(CumTime)
 
- 							
 
- 			
 
- 	### Extract data within day period
 
- 	Raw_Date=[]     # raw data (date)
 
- 	Raw_Value=[]    # raw data (value)
 
- 	for i in range(len(raw_Data)):
 
- 		if datetime.date(raw_Data[i][4].year,raw_Data[i][4].month,raw_Data[i][4].day) >= startday:
 
- 			if datetime.date(raw_Data[i][4].year,raw_Data[i][4].month,raw_Data[i][4].day) <= lastday:
 
- 				Raw_Date.append(raw_Data[i][4])
 
- 				Raw_Value.append(raw_Data[i][5])
 
- 			if datetime.date(raw_Data[i][4].year,raw_Data[i][4].month,raw_Data[i][4].day) > lastday:
 
- 				break
 
- 				
 
- 	Data_len=len(Raw_Date)
 
- 	if isRecent:
 
- 		DataAct_len = (Day_Period-1)*OrgDataRes + now.hour*4 + int(now.minute/15)+1
 
- 	else:
 
- 		DataAct_len = Day_Period*OrgDataRes
 
- 		
 
- 	### Unknown/duplicated data counts
 
- 	DataCount=[]
 
- 	for i in range(len(StandardTimeStamp_DayUnit)):
 
- 		cnt_unk=0   # Unknown data count
 
- 		for j in range(Data_len-1):
 
- 			if StandardTimeStamp_DayUnit[i] == datetime.date(Raw_Date[j].year,Raw_Date[j].month,Raw_Date[j].day):
 
- 				cnt_unk += 1
 
- 		if isRecent and i==len(StandardTimeStamp_DayUnit)-1:
 
- 			DataCount.append([StandardTimeStamp_DayUnit[i], now.hour*4 + int(now.minute/15) - cnt_unk])        
 
- 		else:
 
- 			DataCount.append([StandardTimeStamp_DayUnit[i], OrgDataRes-cnt_unk])
 
- 									
 
- 	DataCountMat=np.matrix(DataCount)
 
- 	######## 현재 DB 특성상 값이 중복되거나 시간테이블의 행 자체가 없는 경우가 있고, 이 데이터 1 step 앞뒤로 데이터가 비정상일 확률이 높으므로 비정상데이터 뿐만 아니라 앞뒤 1 step까지 NaN으로 처리함
 
- 	data_w_nan=[]
 
- 	idx=0
 
- 	idx2=0
 
- 	isBadData = False
 
- 	for i in range(DataAct_len): 
 
- 		if datetime.date(raw_Data[idx][4].year,raw_Data[idx][4].month,raw_Data[idx][4].day) >= startday and datetime.date(raw_Data[idx][4].year,raw_Data[idx][4].month,raw_Data[idx][4].day) <= lastday:
 
- 			if isBadData == True:
 
- 				data_w_nan.append(np.nan)
 
- 				isBadData=False
 
- 			elif Check_AlivedTimeStamp(raw_Data, StandardTimeStamp_QuarterUnit, idx, idx2, 'quarterly'):
 
- 				data_w_nan.append(raw_Data[idx][5])
 
- 			else:
 
- 				if i > 1:
 
- 					data_w_nan[-1]=np.nan
 
- 				data_w_nan.append(np.nan)
 
- 				#data_w_nan.append(np.nan)
 
- 				if raw_Data[idx+1][5] > 0 and Check_AlivedTimeStamp(raw_Data, StandardTimeStamp_QuarterUnit, idx+1, idx2+1, 'quarterly'):
 
- 					isBadData = True
 
- 				idx -= 1
 
- 			idx2 += 1
 
- 		idx += 1
 
- 	return StandardTimeStamp_QuarterUnit, data_w_nan, DataCountMat
 
- ### 예보데이터는 내일 데이터까지 확보해야하기때문에 리스트 수가 설비 데이터에 비해 하루 치가 더 많다
 
- def detect_unknown_duplicated_zero_data_for_WeatherForecast3h(raw_Data, startday, lastday, Day_Period):
 
- 	StandardTimeStamp_DayUnit = []
 
- 	# Create intact time stamp 
 
- 	for idx_day in range(Day_Period+1):
 
- 		StandardTimeStamp_DayUnit.append(startday + datetime.timedelta(days=idx_day))
 
- 		
 
- 	### Extract data within day period
 
- 	Raw_Value_max=[]    # raw data (value)
 
- 	Raw_Value_min=[]
 
- 	Raw_Value_mean=[]
 
- 	Raw_Date=[]     # raw data (date)
 
- 	tmp_data=[raw_Data[0][5]]
 
- 	for i in range(len(raw_Data)):        
 
- 		if i == len(raw_Data)-1:
 
- 			Raw_Date.append(raw_Data[i][4])
 
- 			Raw_Value_max.append(max(tmp_data))
 
- 			Raw_Value_min.append(min(tmp_data))
 
- 			Raw_Value_mean.append(np.mean(tmp_data))
 
- 		elif datetime.date(raw_Data[i][4].year,raw_Data[i][4].month,raw_Data[i][4].day) >= startday:
 
- 			if datetime.date(raw_Data[i][4].year,raw_Data[i][4].month,raw_Data[i][4].day) <= lastday + datetime.timedelta(days=1):
 
- 				if datetime.date(raw_Data[i][4].year,raw_Data[i][4].month,raw_Data[i][4].day) != datetime.date(raw_Data[i+1][4].year,raw_Data[i+1][4].month,raw_Data[i+1][4].day):
 
- 					Raw_Date.append(raw_Data[i][4])
 
- 					Raw_Value_max.append(max(tmp_data))
 
- 					Raw_Value_min.append(min(tmp_data))
 
- 					Raw_Value_mean.append(np.mean(tmp_data))
 
- 					tmp_data=[]
 
- 				tmp_data.append(raw_Data[i+1][5])
 
- 			if datetime.date(raw_Data[i][4].year,raw_Data[i][4].month,raw_Data[i][4].day) > lastday + datetime.timedelta(days=1):
 
- 				break
 
- 	Data_len=len(Raw_Date)
 
- 	### Unknown/duplicated data counts
 
- 	DataCount=[]
 
- 	for i in range(len(StandardTimeStamp_DayUnit)):
 
- 		cnt_unk=0   # Unknown data count
 
- 		for j in range(Data_len-1):
 
- 			if StandardTimeStamp_DayUnit[i] == datetime.date(Raw_Date[j].year,Raw_Date[j].month,Raw_Date[j].day):
 
- 				cnt_unk += 1
 
- 		DataCount.append([StandardTimeStamp_DayUnit[i], 1-cnt_unk])
 
- 	DataCountMat=np.matrix(DataCount)
 
- 	######## 현재 DB 특성상 값이 중복되거나 시간테이블의 행 자체가 없는 경우가 있고, 이 데이터 1 step 앞뒤로 데이터가 비정상일 확률이 높으므로 비정상데이터 뿐만 아니라 앞뒤 1 step까지 NaN으로 처리함
 
- 	MaxData_w_nan=[]
 
- 	MinData_w_nan=[]
 
- 	MeanData_w_nan=[]
 
- 	for i in range(len(StandardTimeStamp_DayUnit)):
 
- 		for j in range(len(Raw_Date)):
 
- 			if Check_AlivedTimeStamp(Raw_Date, StandardTimeStamp_DayUnit, j, i, 'daily'):
 
- 				MaxData_w_nan.append(Raw_Value_max[j])
 
- 				MinData_w_nan.append(Raw_Value_min[j])
 
- 				MeanData_w_nan.append(Raw_Value_mean[j])
 
- 				break
 
- 			elif j == len(Raw_Date)-1:
 
- 				MaxData_w_nan.append(np.nan)
 
- 				MinData_w_nan.append(np.nan)
 
- 				MeanData_w_nan.append(np.nan)
 
- 				
 
- 	return StandardTimeStamp_DayUnit, MaxData_w_nan, MinData_w_nan, MeanData_w_nan, DataCountMat
 
- ### Define day-type 
 
- def getDayName(year, month, day):
 
-     return ['MON','TUE','WED','THU','FRI','SAT','SUN'][datetime.date(year, month, day).weekday()]
 
- def getDayType(DateinDay, Period, SpecialHoliday):
 
-     DoW=[];    # Day of Week
 
-     for i in range(Period):
 
-         if DateinDay[i].year==2019 and DateinDay[i].month==5 and DateinDay[i].day==18:
 
-             DoW.append([5, DateinDay[i]])
 
-         elif getDayName(DateinDay[i].year,DateinDay[i].month,DateinDay[i].day) == 'MON':
 
-             DoW.append([1, DateinDay[i]])
 
-         elif getDayName(DateinDay[i].year,DateinDay[i].month,DateinDay[i].day) == 'TUE':
 
-             DoW.append([2, DateinDay[i]])
 
-         elif getDayName(DateinDay[i].year,DateinDay[i].month,DateinDay[i].day) == 'WED':
 
-             DoW.append([3, DateinDay[i]])
 
-         elif getDayName(DateinDay[i].year,DateinDay[i].month,DateinDay[i].day) == 'THU':
 
-             DoW.append([4, DateinDay[i]])
 
-         elif getDayName(DateinDay[i].year,DateinDay[i].month,DateinDay[i].day) == 'FRI':
 
-             DoW.append([5, DateinDay[i]])
 
-         elif getDayName(DateinDay[i].year,DateinDay[i].month,DateinDay[i].day) == 'SAT':
 
-             DoW.append([6, DateinDay[i]])
 
-         elif getDayName(DateinDay[i].year,DateinDay[i].month,DateinDay[i].day) == 'SUN':
 
-             DoW.append([7, DateinDay[i]])
 
-         for j in range(len(SpecialHoliday)):
 
-             if SpecialHoliday[j] == datetime.date(DateinDay[i].year,DateinDay[i].month,DateinDay[i].day):
 
-                 DoW[-1][0] = 8
 
-                 break
 
-     
 
-     ### W-W:1, N-W:2, W-N:3, N-N:4 ###
 
-     DayType=[]
 
-     for i in range(Period):
 
-         if i==0:
 
-             if DoW[i][0] <= 5:
 
-                 DayType.append([1, DateinDay[i]])
 
-             elif DoW[i][0] > 5:
 
-                 DayType.append([3, DateinDay[i]])
 
-         else:
 
-             if DoW[i-1][0] <= 5 and  DoW[i][0] <= 5:
 
-                 DayType.append([1, DateinDay[i]])
 
-             elif DoW[i-1][0] > 5 and DoW[i][0] <= 5:
 
-                 DayType.append([2, DateinDay[i]])
 
-             elif DoW[i-1][0] <= 5 and DoW[i][0] > 5:
 
-                 DayType.append([3, DateinDay[i]])
 
-             elif DoW[i-1][0] > 5 and DoW[i][0] > 5:
 
-                 DayType.append([4, DateinDay[i]])
 
-     return DoW, DayType
 
- if __name__ == "__main__" :
 
- 	Init = True
 
- 	## Check every 15min. in the infinite loop
 
- 	while True:
 
- 		now = datetime.datetime.now().now()
 
- 		## distinguish real time update and specific day
 
- 		## 자정에 생기는 인덱싱 문제로 0시에는 16분에 업데이트, 나머지는 15분에 한 번씩 업데이트
 
- 		if Init:
 
- 			prev_time_minute = now.minute - 1		## 알고리즘 중복 수행 방지 (알고리즘 수행시 1분이 안걸리기에 한타임에 알고리즘 한번만 동작시키기 위함)
 
- 		if (now.hour != 0 and now.minute%15 == 1 and now.second > 0 and now.second < 5) and prev_time_minute != now.minute:
 
- 			ActiveAlgorithm = True
 
- 			prev_time_minute = now.minute
 
- 		else:
 
- 			ActiveAlgorithm = False
 
- 			
 
- 		if ActiveAlgorithm or Init:
 
- 			
 
- 			## Loading .ini file
 
- 			myINI = configparser.ConfigParser()
 
- 			myINI.read("Config.ini", "utf-8" )
 
- 			# MSSQL Access
 
- 			conn = pymssql.connect(host=myINI.get('LocalDB_Info','ip_address'), user=myINI.get('LocalDB_Info','user_id'), password=myINI.get('LocalDB_Info','user_password'), database = myINI.get('LocalDB_Info','db_name'), autocommit=True)
 
- 			# Create Cursor from Connection
 
- 			cursor = conn.cursor()			
 
- 			# Execute SQL (Electric consumption)
 
- 			cursor.execute('SELECT * FROM BemsConfigData where SiteId = 1')
 
- 			
 
- 			rowDB_info = cursor.fetchone()
 
- 			
 
- 			conn.close()
 
- 			
 
- 			loadDBIP = rowDB_info[1]
 
- 			loadDBUserID = rowDB_info[2]
 
- 			loadDBUserPW = rowDB_info[3]
 
- 			loadDBName = rowDB_info[4]
 
- 			targetDBIP = rowDB_info[5]
 
- 			targetDBUserID = rowDB_info[6]
 
- 			targetDBUserPW = rowDB_info[7]
 
- 			targetDBName = rowDB_info[8]
 
- 			
 
- 			startday = datetime.date(int(rowDB_info[9].year), int(rowDB_info[9].month), int(rowDB_info[9].day))
 
- 			
 
- 			now=datetime.datetime.now().now()
 
- 			lastday = datetime.date(now.year, now.month, now.day)
 
- 			isRecent = True
 
- 			if startday < datetime.date(2020,4,8):
 
- 				print('[ERROR] 데이터 최소 시작 시점은 2020.04.08 입니다')
 
- 				startday = datetime.date(2020,4,9)
 
- 			elif startday > lastday:
 
- 				print('[ERROR] 예측 타깃 시작시점이 데이터 시작 시점보다 작을 수 없습니다')
 
- 			
 
- 			
 
- 			##############################################################################################
 
- 			## 기온, 습도 예보 데이터 로드
 
- 			# MSSQL 접속
 
- 			conn = pymssql.connect(host = targetDBIP, user = targetDBUserID, password = targetDBUserPW, database = targetDBName, autocommit=True)
 
- 			# Connection 으로부터 Cursor 생성
 
- 			cursor = conn.cursor()
 
- 			# SQL문 실행 (기온 예보)
 
- 			cursor.execute('SELECT * FROM BemsMonitoringPointWeatherForecasted where SiteId = 1 and Category = '+"'"+'Temperature'+"'"+' order by ForecastedDateTime desc')
 
- 			
 
- 			row = cursor.fetchone()
 
- 			rawWFTemperature = [row]
 
- 			while row:
 
- 				row = cursor.fetchone()
 
- 				if row == None:
 
- 					break
 
- 				if datetime.date(row[4].year,row[4].month,row[4].day) < startday:
 
- 					break
 
- 				rawWFTemperature.append(row)
 
- 			rawWFTemperature.reverse()
 
- 			# SQL문 실행 (습도 예보)
 
- 			cursor.execute('SELECT * FROM BemsMonitoringPointWeatherForecasted where SiteId = 1 and Category = '+"'"+'Humidity'+"'"+' order by ForecastedDateTime desc')
 
- 			
 
- 			row = cursor.fetchone()
 
- 			rawWFHumidity = [row]
 
- 			while row:
 
- 				row = cursor.fetchone()
 
- 				if row == None:
 
- 					break
 
- 				if datetime.date(row[4].year,row[4].month,row[4].day) < startday:
 
- 					break
 
- 				rawWFHumidity.append(row)
 
- 			rawWFHumidity.reverse()
 
- 			##############################################################################################
 
- 			startday = datetime.date(rawWFHumidity[0][4].year, rawWFHumidity[0][4].month, rawWFHumidity[0][4].day)		## 데이터 불러오는 DB가 선구축된다고 가정하여 예보데이터 기준으로 startday define
 
- 			DayPeriod = (lastday - startday).days + 1
 
- 			print('* StartDay :',startday,',', 'LastDay :', lastday,',','Current Time :', now, ',','Day period :', DayPeriod)
 
- 			# MSSQL 접속
 
- 			conn = pymssql.connect(host = loadDBIP, user = loadDBUserID, password = loadDBUserPW, database = loadDBName, autocommit=True)
 
- 			
 
- 			# Connection 으로부터 Cursor 생성
 
- 			cursor = conn.cursor()
 
- 			DataRes_96=96
 
- 			DataRes_24=24
 
- 			print('************ (Start) Load & pre-processing data !! ************')			
 
- 			# SQL문 실행 (축열조 축열량)
 
- 			cursor.execute('SELECT * FROM BemsMonitoringPointHistory15min where SiteId=1 and FacilityTypeId = 3 and FacilityCode = 4478 and PropertyId = 2 order by CreatedDateTime desc')
 
- 			
 
- 			row = cursor.fetchone()
 
- 			rawChillerCalAmount=[row]
 
- 			while row:
 
- 				row = cursor.fetchone()
 
- 				if row == None:
 
- 					break
 
- 				if datetime.date(row[4].year,row[4].month,row[4].day) < startday:
 
- 					break
 
- 				rawChillerCalAmount.append(row)
 
- 			rawChillerCalAmount.reverse()
 
- 			# SQL문 실행 (축열조 제빙운전상태)
 
- 			cursor.execute('SELECT * FROM BemsMonitoringPointHistory15min where SiteId=1 and FacilityTypeId = 3 and FacilityCode = 4478 and PropertyId = 16 order by CreatedDateTime desc')
 
- 			
 
- 			row = cursor.fetchone()
 
- 			rawChillerStatusIcing=[row]
 
- 			while row:
 
- 				row = cursor.fetchone()
 
- 				if row == None:
 
- 					break
 
- 				if datetime.date(row[4].year,row[4].month,row[4].day) < startday:
 
- 					break
 
- 				rawChillerStatusIcing.append(row)
 
- 			rawChillerStatusIcing.reverse()
 
- 			
 
- 			# SQL문 실행 (축열조 축단운전상태)
 
- 			cursor.execute('SELECT * FROM BemsMonitoringPointHistory15min where SiteId=1 and FacilityTypeId = 3 and FacilityCode = 4478 and PropertyId = 17 order by CreatedDateTime desc')
 
- 			
 
- 			row = cursor.fetchone()
 
- 			rawChillerStatusDeicing=[row]
 
- 			while row:
 
- 				row = cursor.fetchone()
 
- 				if row == None:
 
- 					break
 
- 				if datetime.date(row[4].year,row[4].month,row[4].day) < startday:
 
- 					break
 
- 				rawChillerStatusDeicing.append(row)
 
- 			rawChillerStatusDeicing.reverse()
 
- 			# SQL문 실행 (축열조 병렬운전상태)
 
- 			cursor.execute('SELECT * FROM BemsMonitoringPointHistory15min where SiteId=1 and FacilityTypeId = 3 and FacilityCode = 4478 and PropertyId = 18 order by CreatedDateTime desc')
 
- 			
 
- 			row = cursor.fetchone()
 
- 			rawChillerStatusParallel=[row]
 
- 			while row:
 
- 				row = cursor.fetchone()
 
- 				if row == None:
 
- 					break
 
- 				if datetime.date(row[4].year,row[4].month,row[4].day) < startday:
 
- 					break
 
- 				rawChillerStatusParallel.append(row)
 
- 			rawChillerStatusParallel.reverse()
 
- 			# SQL문 실행 (축열조 냉단운전상태)
 
- 			cursor.execute('SELECT * FROM BemsMonitoringPointHistory15min where SiteId=1 and FacilityTypeId = 3 and FacilityCode = 4478 and PropertyId = 19 order by CreatedDateTime desc')
 
- 			
 
- 			row = cursor.fetchone()
 
- 			rawChillerStatusRefOnly=[row]
 
- 			while row:
 
- 				row = cursor.fetchone()
 
- 				if row == None:
 
- 					break
 
- 				if datetime.date(row[4].year,row[4].month,row[4].day) < startday:
 
- 					break
 
- 				rawChillerStatusRefOnly.append(row)
 
- 			rawChillerStatusRefOnly.reverse()
 
- 			## 현재 2019, 2020년 냉동기 전력량 데이터가 없으므로 2년 전 데이터를 활용
 
- 			# SQL문 실행 (냉동기1 전력량)
 
- 			cursor.execute('SELECT * FROM BemsMonitoringPointHistory15min where SiteId=1 and FacilityTypeId = 2 and FacilityCode = 4479 and PropertyId = 11 order by CreatedDateTime desc')
 
- 			
 
- 			row = cursor.fetchone()
 
- 			rawRefPowerConsume1=[row]
 
- 			while row:
 
- 				row = cursor.fetchone()
 
- 				if row == None:
 
- 					break
 
- 				if datetime.date(row[4].year,row[4].month,row[4].day) < startday:
 
- 					break
 
- 				rawRefPowerConsume1.append(row)
 
- 			rawRefPowerConsume1.reverse()
 
- 			# SQL문 실행 (냉동기1 운전상태)
 
- 			cursor.execute('SELECT * FROM BemsMonitoringPointHistory15min where SiteId=1 and FacilityTypeId = 2 and FacilityCode = 4479 and PropertyId = 15 order by CreatedDateTime desc')
 
- 			
 
- 			row = cursor.fetchone()
 
- 			rawRefStatus1=[row]
 
- 			while row:
 
- 				row = cursor.fetchone()
 
- 				if row == None:
 
- 					break
 
- 				if datetime.date(row[4].year,row[4].month,row[4].day) < startday:
 
- 					break
 
- 				rawRefStatus1.append(row)
 
- 			rawRefStatus1.reverse()
 
- 			# SQL문 실행 (냉동기2 전력량)
 
- 			cursor.execute('SELECT * FROM BemsMonitoringPointHistory15min where SiteId=1 and FacilityTypeId = 2 and FacilityCode = 4480 and PropertyId = 11 order by CreatedDateTime desc')
 
- 			
 
- 			row = cursor.fetchone()
 
- 			rawRefPowerConsume2=[row]
 
- 			while row:
 
- 				row = cursor.fetchone()
 
- 				if row == None:
 
- 					break
 
- 				if datetime.date(row[4].year,row[4].month,row[4].day) < startday:
 
- 					break
 
- 				rawRefPowerConsume2.append(row)
 
- 			rawRefPowerConsume2.reverse()
 
- 			# SQL문 실행 (냉동기2 운전상태)
 
- 			cursor.execute('SELECT * FROM BemsMonitoringPointHistory15min where SiteId=1 and FacilityTypeId = 2 and FacilityCode = 4480 and PropertyId = 15 order by CreatedDateTime desc')
 
- 			
 
- 			row = cursor.fetchone()
 
- 			rawRefStatus2=[row]
 
- 			while row:
 
- 				row = cursor.fetchone()
 
- 				if row == None:
 
- 					break
 
- 				if datetime.date(row[4].year,row[4].month,row[4].day) < startday:
 
- 					break
 
- 				rawRefStatus2.append(row)
 
- 			rawRefStatus2.reverse()
 
- 			# SQL문 실행 (브라인 입구온도)
 
- 			cursor.execute('SELECT * FROM BemsMonitoringPointHistory15min where SiteId=1 and FacilityTypeId = 3 and FacilityCode = 4478 and PropertyId = 4 order by CreatedDateTime desc')
 
- 			
 
- 			row = cursor.fetchone()
 
- 			rawBrineInletTemperature=[row]
 
- 			while row:
 
- 				row = cursor.fetchone()
 
- 				if row == None:
 
- 					break
 
- 				if datetime.date(row[4].year,row[4].month,row[4].day) < startday:
 
- 					break
 
- 				rawBrineInletTemperature.append(row)
 
- 			rawBrineInletTemperature.reverse()
 
- 			# SQL문 실행 (브라인 출구온도)
 
- 			cursor.execute('SELECT * FROM BemsMonitoringPointHistory15min where SiteId=1 and FacilityTypeId = 3 and FacilityCode = 4478 and PropertyId = 3 order by CreatedDateTime desc')
 
- 			
 
- 			row = cursor.fetchone()
 
- 			rawBrineOutletTemperature=[row]
 
- 			while row:
 
- 				row = cursor.fetchone()
 
- 				if row == None:
 
- 					break
 
- 				if datetime.date(row[4].year,row[4].month,row[4].day) < startday:
 
- 					break
 
- 				rawBrineOutletTemperature.append(row)
 
- 			rawBrineOutletTemperature.reverse()
 
- 			# SQL문 실행 (브라인 혼합온도)
 
- 			cursor.execute('SELECT * FROM BemsMonitoringPointHistory15min where SiteId=1 and FacilityTypeId = 3 and FacilityCode = 4478 and PropertyId = 22 order by CreatedDateTime desc')
 
- 			
 
- 			row = cursor.fetchone()
 
- 			rawBrineMixedTemperature=[row]
 
- 			while row:
 
- 				row = cursor.fetchone()
 
- 				if row == None:
 
- 					break
 
- 				if datetime.date(row[4].year,row[4].month,row[4].day) < startday:
 
- 					break
 
- 				rawBrineMixedTemperature.append(row)
 
- 			rawBrineMixedTemperature.reverse()
 
- 			# SQL문 실행 (브라인 통과유량)
 
- 			cursor.execute('SELECT * FROM BemsMonitoringPointHistory15min where SiteId=1 and FacilityTypeId = 3 and FacilityCode = 4478 and PropertyId = 5 order by CreatedDateTime desc')
 
- 			
 
- 			row = cursor.fetchone()
 
- 			rawBrineFlowAmount=[row]
 
- 			while row:
 
- 				row = cursor.fetchone()
 
- 				if row == None:
 
- 					break
 
- 				if datetime.date(row[4].year,row[4].month,row[4].day) < startday:
 
- 					break
 
- 				rawBrineFlowAmount.append(row)
 
- 			rawBrineFlowAmount.reverse()
 
- 			# SQL문 실행 (정기휴일)
 
- 			cursor.execute('SELECT * FROM CmHoliday where SiteId = 1 and IsUse = 1')
 
- 			
 
- 			# 데이타 하나씩 Fetch하여 출력
 
- 			row = cursor.fetchone()
 
- 			regularHolidayData = [row]
 
- 			while row:
 
- 				row = cursor.fetchone()
 
- 				if row == None:
 
- 					break
 
- 				regularHolidayData.append(row)
 
- 			regularHolidayData = regularHolidayData[0:-1]
 
- 			# SQL문 실행 (비정기휴일)
 
- 			cursor.execute('SELECT * FROM CmHolidayCustom where SiteId = 1 and IsUse = 1')
 
- 			
 
- 			# 데이타 하나씩 Fetch하여 출력
 
- 			row = cursor.fetchone()
 
- 			suddenHolidayData = [row]
 
- 			while row:
 
- 				row = cursor.fetchone()
 
- 				if row == None:
 
- 					break
 
- 				suddenHolidayData.append(row)
 
- 			suddenHolidayData = suddenHolidayData[0:-1]
 
- 			##############################################################################################
 
- 			## 현재 2019, 2020년 냉동기 전력량 데이터가 없으므로 2년 전 데이터를 활용
 
- 			# SQL문 실행 (냉동기1 전력량), 2018
 
- 			cursor.execute('SELECT * FROM BemsMonitoringPointHistory15min where SiteId=1 and FacilityTypeId = 2 and FacilityCode = 4479 and PropertyId = 11 order by CreatedDateTime desc')
 
- 			
 
- 			row = cursor.fetchone()
 
- 			rawRefPowerConsume1_2018=[row]
 
- 			while row:
 
- 				row = cursor.fetchone()
 
- 				if row == None:
 
- 					break
 
- 				if datetime.date(row[4].year,row[4].month,row[4].day) < datetime.date(2018,1,1):
 
- 					break
 
- 				rawRefPowerConsume1_2018.append(row)
 
- 			rawRefPowerConsume1_2018.reverse()
 
- 			# SQL문 실행 (냉동기1 운전상태)
 
- 			cursor.execute('SELECT * FROM BemsMonitoringPointHistory15min where SiteId=1 and FacilityTypeId = 2 and FacilityCode = 4479 and PropertyId = 15 order by CreatedDateTime desc')
 
- 			
 
- 			row = cursor.fetchone()
 
- 			rawRefStatus1_2018=[row]
 
- 			while row:
 
- 				row = cursor.fetchone()
 
- 				if row == None:
 
- 					break
 
- 				if datetime.date(row[4].year,row[4].month,row[4].day) < datetime.date(2018,1,1):
 
- 					break
 
- 				rawRefStatus1_2018.append(row)
 
- 			rawRefStatus1_2018.reverse()
 
- 			# SQL문 실행 (냉동기2 전력량)
 
- 			cursor.execute('SELECT * FROM BemsMonitoringPointHistory15min where SiteId=1 and FacilityTypeId = 2 and FacilityCode = 4480 and PropertyId = 11 order by CreatedDateTime desc')
 
- 			
 
- 			row = cursor.fetchone()
 
- 			rawRefPowerConsume2_2018=[row]
 
- 			while row:
 
- 				row = cursor.fetchone()
 
- 				if row == None:
 
- 					break
 
- 				if datetime.date(row[4].year,row[4].month,row[4].day) < datetime.date(2018,1,1):
 
- 					break
 
- 				rawRefPowerConsume2_2018.append(row)
 
- 			rawRefPowerConsume2_2018.reverse()
 
- 			# SQL문 실행 (냉동기2 운전상태)
 
- 			cursor.execute('SELECT * FROM BemsMonitoringPointHistory15min where SiteId=1 and FacilityTypeId = 2 and FacilityCode = 4480 and PropertyId = 15 order by CreatedDateTime desc')
 
- 			
 
- 			row = cursor.fetchone()
 
- 			rawRefStatus2_2018=[row]
 
- 			while row:
 
- 				row = cursor.fetchone()
 
- 				if row == None:
 
- 					break
 
- 				if datetime.date(row[4].year,row[4].month,row[4].day) < datetime.date(2018,1,1):
 
- 					break
 
- 				rawRefStatus2_2018.append(row)
 
- 			rawRefStatus2_2018.reverse()
 
- 			
 
- 			##############################################################################################
 
- 			# 연결 끊기
 
- 			conn.close()
 
- 			## 휴일 데이터 DB에서 호출
 
- 			# 공휴일의 음력 계산
 
- 			calendar_convert = KoreanLunarCalendar()
 
- 			SpecialHoliday = []
 
- 			for i in range(lastday.year-startday.year+1):
 
- 				for j in range(len(regularHolidayData)):
 
- 					if regularHolidayData[j][3] == 1:
 
- 						if regularHolidayData[j][1] == 12 and regularHolidayData[j][2] == 30: ## 설 하루 전 연휴 계산을 위함
 
- 							calendar_convert.setLunarDate(startday.year+i-1, regularHolidayData[j][1], regularHolidayData[j][2], False)
 
- 							SpecialHoliday.append(datetime.date(int(calendar_convert.SolarIsoFormat().split(' ')[0].split('-')[0]), int(calendar_convert.SolarIsoFormat().split(' ')[0].split('-')[1]), int(calendar_convert.SolarIsoFormat().split(' ')[0].split('-')[2])))
 
- 						else:
 
- 							calendar_convert.setLunarDate(startday.year+i, regularHolidayData[j][1], regularHolidayData[j][2], False)
 
- 							SpecialHoliday.append(datetime.date(int(calendar_convert.SolarIsoFormat().split(' ')[0].split('-')[0]), int(calendar_convert.SolarIsoFormat().split(' ')[0].split('-')[1]), int(calendar_convert.SolarIsoFormat().split(' ')[0].split('-')[2])))
 
- 					else:
 
- 						SpecialHoliday.append(datetime.date(startday.year+i,regularHolidayData[j][1],regularHolidayData[j][2]))
 
- 			for i in range(len(suddenHolidayData)):
 
- 				if suddenHolidayData[i][1].year >= startday.year:
 
- 					SpecialHoliday.append(datetime.date(suddenHolidayData[i][1].year, suddenHolidayData[i][1].month, suddenHolidayData[i][1].day))
 
- 			SpecialHoliday=list(set(SpecialHoliday))
 
- 			##############################################################################################
 
- 			ChillerCalAmount_Date, ChillerCalAmount_w_nan, DataCountMat_ChillerCalAmount = detect_unknown_duplicated_zero_data_for_faciilty(rawChillerCalAmount, startday, lastday, DayPeriod, DataRes_96, isRecent)
 
- 			BrineMixedTemperature_Date, BrineMixedTemperature_w_nan, DataCountMat_BrineMixedTemperature = detect_unknown_duplicated_zero_data_for_faciilty(rawBrineMixedTemperature, startday, lastday, DayPeriod, DataRes_96, isRecent)
 
- 			BrineInletTemperature_Date, BrineInletTemperature_w_nan, DataCountMat_BrineInletTemperature = detect_unknown_duplicated_zero_data_for_faciilty(rawBrineInletTemperature, startday, lastday, DayPeriod, DataRes_96, isRecent)
 
- 			BrineOutletTemperature_Date, BrineOutletTemperature_w_nan, DataCountMat_BrineOutletTemperature = detect_unknown_duplicated_zero_data_for_faciilty(rawBrineOutletTemperature, startday, lastday, DayPeriod, DataRes_96, isRecent)
 
- 			BrineFlowAmount_Date, BrineFlowAmount_w_nan, DataCountMat_BrineFlowAmount = detect_unknown_duplicated_zero_data_for_faciilty(rawBrineFlowAmount, startday, lastday, DayPeriod, DataRes_96, isRecent)
 
- 						
 
- 			ChStatusIcing_Date, ChStatusIcing_w_nan, DataCountMat_ChStatusIcing = detect_unknown_duplicated_zero_data_for_faciilty(rawChillerStatusIcing, startday, lastday, DayPeriod, DataRes_96, isRecent)
 
- 			ChStatusDeicing_Date, ChStatusDeicing_w_nan, DataCountMat_ChStatusDeicing = detect_unknown_duplicated_zero_data_for_faciilty(rawChillerStatusDeicing, startday, lastday, DayPeriod, DataRes_96, isRecent)
 
- 			ChStatusParallel_Date, ChStatusParallel_w_nan, DataCountMat_ChStatusParallel = detect_unknown_duplicated_zero_data_for_faciilty(rawChillerStatusParallel, startday, lastday, DayPeriod, DataRes_96, isRecent)
 
- 			ChStatusRefOnly_Date, ChStatusRefOnly_w_nan, DataCountMat_ChStatusRefOnly = detect_unknown_duplicated_zero_data_for_faciilty(rawChillerStatusRefOnly, startday, lastday, DayPeriod, DataRes_96, isRecent)
 
- 			RefPowerConsume1_Date, RefPowerConsume1_w_nan, DataCountMat_RefPowerConsume1 = detect_unknown_duplicated_zero_data_for_faciilty(rawRefPowerConsume1, startday, lastday, DayPeriod, DataRes_96, isRecent)
 
- 			RefPowerConsume2_Date, RefPowerConsume2_w_nan, DataCountMat_RefPowerConsume2 = detect_unknown_duplicated_zero_data_for_faciilty(rawRefPowerConsume2, startday, lastday, DayPeriod, DataRes_96, isRecent)
 
- 			RefStatus1_Date, RefStatus1_w_nan, DataCountMat_RefStatus1 = detect_unknown_duplicated_zero_data_for_faciilty(rawRefStatus1, startday, lastday, DayPeriod, DataRes_96, isRecent)
 
- 			RefStatus2_Date, RefStatus2_w_nan, DataCountMat_RefStatus2 = detect_unknown_duplicated_zero_data_for_faciilty(rawRefStatus2, startday, lastday, DayPeriod, DataRes_96, isRecent)
 
- 			##############################################################################################
 
- 			## 2019, 2020년 냉동기 전력량이 없어서 2018년 데이터로 대체
 
- 			DayPeriod_2018 = (datetime.date(2018,12,31) - datetime.date(2018,1,1)).days + 1
 
- 			
 
- 			RefPowerConsume1_2018_Date, RefPowerConsume1_2018_w_nan, DataCountMat_RefPowerConsume1_2018 = detect_unknown_duplicated_zero_data_for_faciilty(rawRefPowerConsume1_2018, datetime.date(2018,1,1), datetime.date(2018,12,31), DayPeriod_2018, DataRes_96, isRecent)
 
- 			RefPowerConsume2_2018_Date, RefPowerConsume2_2018_w_nan, DataCountMat_RefPowerConsume2_2018 = detect_unknown_duplicated_zero_data_for_faciilty(rawRefPowerConsume2_2018, datetime.date(2018,1,1), datetime.date(2018,12,31), DayPeriod_2018, DataRes_96, isRecent)
 
- 			RefStatus1_Date_2018, RefStatus1_2018_w_nan, DataCountMat_RefStatus1_2018 = detect_unknown_duplicated_zero_data_for_faciilty(rawRefStatus1_2018, datetime.date(2018,1,1), datetime.date(2018,12,31), DayPeriod_2018, DataRes_96, isRecent)
 
- 			RefStatus2_2018_Date, RefStatus2_2018_w_nan, DataCountMat_RefStatus2_2018 = detect_unknown_duplicated_zero_data_for_faciilty(rawRefStatus2_2018, datetime.date(2018,1,1), datetime.date(2018,12,31), DayPeriod_2018, DataRes_96, isRecent)
 
- 				
 
- 			################# Using the power Consumption of Refrigerator in 2018 instead of 2020 #################
 
- 			#### 전력 소비량 계산
 
- 			_st=90*96
 
- 			_end=195*96
 
- 			period_2018=(_end-_st)/96
 
- 			RefStatus1_2018_w_nan_tmp=RefStatus1_2018_w_nan[_st:_end]
 
- 			RefPowerConsume1_2018_w_nan_tmp=RefPowerConsume1_2018_w_nan[_st:_end]
 
- 			RefStatus2_2018_w_nan_tmp=RefStatus2_2018_w_nan[_st:_end]
 
- 			RefPowerConsume2_2018_w_nan_tmp=RefPowerConsume2_2018_w_nan[_st:_end]
 
- 			
 
- 			### Estimation based on Statistical method
 
- 			X1 = []
 
- 			X2 = []
 
- 			Y1 = []
 
- 			Y2 = []
 
- 			TermNum = 96
 
- 			for i in range(TermNum, len(RefStatus1_2018_w_nan_tmp),TermNum):
 
- 				X1.append(RefStatus1_2018_w_nan_tmp[i-TermNum:i])
 
- 				X2.append(RefStatus2_2018_w_nan_tmp[i-TermNum:i])
 
- 				Y1.append(RefPowerConsume1_2018_w_nan_tmp[i-TermNum:i])
 
- 				Y2.append(RefPowerConsume2_2018_w_nan_tmp[i-TermNum:i])
 
- 			xTrain1, xTest1, yTrain1, yTest1 = train_test_split(X1, Y1, test_size=0.1, shuffle =False)
 
- 			xTrain2, xTest2, yTrain2, yTest2 = train_test_split(X2, Y2, test_size=0.1, shuffle =False)
 
- 			Y_tmp1=[]
 
- 			Y_tmp2=[]
 
- 			for i in range(len(xTrain1)):
 
- 				for j in range(TermNum):
 
- 					if xTrain1[i][j] == 1:
 
- 						Y_tmp1.append(yTrain1[i][j])
 
- 					if xTrain2[i][j] == 1:
 
- 						Y_tmp2.append(yTrain2[i][j])
 
- 			mean_RefConsume1=np.mean(Y_tmp1)      # 냉동기1 전력량 평균
 
- 			mean_RefConsume2=np.mean(Y_tmp2)      # 냉동기2 전력량 평균
 
- 			
 
- 			##############################################################################################
 
- 			##############################################################################################
 
- 			WFTemperature_Date, WFTemperatureMax_w_nan, WFTemperatureMin_w_nan, WFTemperatureMean_w_nan, DataCountMat_WFTemperature = detect_unknown_duplicated_zero_data_for_WeatherForecast3h(rawWFTemperature, startday, lastday, DayPeriod)
 
- 			WFHumidity_Date, WFHumidityMax_w_nan, WFHumidityMin_w_nan, WFHumidityMean_w_nan, DataCountMat_WFHumidity = detect_unknown_duplicated_zero_data_for_WeatherForecast3h(rawWFHumidity, startday, lastday, DayPeriod)
 
- 			RawDate = ChillerCalAmount_Date
 
- 			
 
- 			## 축열조 상태 변수 - 제빙운전:10, 축단운전:20, 병렬운전:30, 냉단운전:40, OFF:0
 
- 			Icing=10
 
- 			StorageOnly=20
 
- 			Parallel=30
 
- 			ChillerOnly=40
 
- 			Off=0
 
- 			ChillerStatus=[]
 
- 			for i in range(len(ChStatusIcing_Date)):
 
- 				if ChStatusIcing_w_nan[i]==1:
 
- 					ChillerStatus.append(Icing)
 
- 				elif ChStatusDeicing_w_nan[i]==1:
 
- 					ChillerStatus.append(StorageOnly)
 
- 				elif ChStatusParallel_w_nan[i]==1:
 
- 					ChillerStatus.append(Parallel)
 
- 				elif ChStatusRefOnly_w_nan[i]==1:
 
- 					ChillerStatus.append(ChillerOnly)
 
- 				elif ChStatusIcing_w_nan[i]==0 or ChStatusDeicing_w_nan[i]==0 or ChStatusParallel_w_nan[i]==0 or ChStatusRefOnly_w_nan[i]==0:
 
- 					ChillerStatus.append(Off)
 
- 				else:
 
- 					ChillerStatus.append(np.nan)
 
- 			## 축/방열량에 대해서 두가지 변수를 생성한다.
 
- 			## 첫번쨰는 사용자에게 상대적 열량을 보여주기 위해 0 < Q < max(Q) 사이의 값으로 구성된 열량
 
- 			## 두번쨰는 실질적 계산을 위해서 NaN이 포함된 날은 제외하고 학습하므로 NaN 구간의 축/방열량은 0으로 가정하고 산출
 
- 			## 축적 열량의 최대치 (정격용량) = 3060 USRT (=10,924.2 kW)일 때 100%
 
- 			max_q_accum_kWh = 3060*3.57
 
- 			q_accum_kWh=[0]
 
- 			nan_cnt=0
 
- 			nan_point=[]
 
- 			for i in range(len(ChillerStatus)):
 
- 				if math.isnan(ChillerStatus[i]):    # Nan의 경우 축열량을 0이라고 가정하고 진행
 
- 					q_accum_kWh.append(q_accum_kWh[-1])
 
- 					nan_cnt += 1
 
- 					nan_point.append(i)
 
- 				else:
 
- 					if ChillerStatus[i] == Icing and BrineInletTemperature_w_nan[i] < BrineMixedTemperature_w_nan[i]:
 
- 						q_accum_kWh.append(q_accum_kWh[-1] + (BrineFlowAmount_w_nan[i]*0.06*1.042*3.14*0.28*0.25)*(BrineMixedTemperature_w_nan[i]-BrineInletTemperature_w_nan[i]))
 
- 					elif ChillerStatus[i] == StorageOnly and BrineInletTemperature_w_nan[i] > BrineMixedTemperature_w_nan[i]:
 
- 						q_accum_kWh.append(q_accum_kWh[-1] - (BrineFlowAmount_w_nan[i]*0.06*1.042*3.14*0.28*0.25)*(BrineInletTemperature_w_nan[i]-BrineMixedTemperature_w_nan[i]))
 
- 					elif ChillerStatus[i] == Parallel and BrineInletTemperature_w_nan[i] > BrineMixedTemperature_w_nan[i]:
 
- 						q_accum_kWh.append(q_accum_kWh[-1] - (BrineFlowAmount_w_nan[i]*0.06*1.042*3.14*0.28*0.25)*(BrineInletTemperature_w_nan[i]-BrineMixedTemperature_w_nan[i]))
 
- 					else: #ChillerStatus[i] == Off or ChillerStatus[i] == ChillerOnly:
 
- 						q_accum_kWh.append(q_accum_kWh[-1])
 
- 										
 
- 					if q_accum_kWh[-1] < 0:
 
- 						q_accum_kWh[-1] = 0
 
- 					elif q_accum_kWh[-1] > max_q_accum_kWh:
 
- 						q_accum_kWh[-1] = max_q_accum_kWh
 
- 									
 
- 					if nan_cnt > 48:
 
- 						print('[Warning] Too many nan points exist (48 points sequentially)')
 
- 					nan_cnt = 0
 
- 			q_accum_kWh = q_accum_kWh[1:len(q_accum_kWh)]
 
- 			q_accum_percent=[]
 
- 			for i in range(len(q_accum_kWh)):
 
- 				q_accum_percent.append((q_accum_kWh[i]/max_q_accum_kWh)*100)
 
- 			CalAmount_prev = q_accum_percent[:len(q_accum_percent)-96]		## DB에 비어있는 이전 축열량이 있다면 채워주기 위함
 
- 			
 
- 			#################### Calculate the Gradient on Each Operation Mode ########################
 
- 			cnt_nan=0
 
- 			CalAmount_wo_nan=[]
 
- 			ChillerStatus_wo_nan=[]
 
- 			RefStatus1_wo_nan=[]
 
- 			RefStatus2_wo_nan=[]
 
- 			RefStatus_wo_nan=[]
 
- 			## 1: off,off, 2: on,off, 3: on,on
 
- 			for i in range(len(q_accum_percent)):
 
- 				if not np.isnan(q_accum_percent[i]) and not np.isnan(ChillerStatus[i]) and not np.isnan(RefStatus1_w_nan[i]) and not np.isnan(RefStatus2_w_nan[i]):
 
- 					CalAmount_wo_nan.append(q_accum_percent[i])
 
- 					ChillerStatus_wo_nan.append(ChillerStatus[i])
 
- 					RefStatus1_wo_nan.append(RefStatus1_w_nan[i])
 
- 					RefStatus2_wo_nan.append(RefStatus2_w_nan[i])
 
- 					RefStatus_wo_nan.append(RefStatus1_w_nan[i]+RefStatus2_w_nan[i])
 
- 					cnt_nan=0
 
- 				else:
 
- 					CalAmount_wo_nan.append(CalAmount_wo_nan[-1])
 
- 					ChillerStatus_wo_nan.append(0)
 
- 					RefStatus1_wo_nan.append(0)
 
- 					RefStatus2_wo_nan.append(0)
 
- 					RefStatus_wo_nan.append(0)
 
- 					cnt_nan+=1
 
- 					if cnt_nan>12:
 
- 						cnt_nan=0
 
- 						# print('There are many unknown data!')
 
- 			
 
- 			# 학습용 데이터로 사용
 
- 			train_size = int(len(ChillerStatus_wo_nan))
 
- 			## 나머지를 검증용 데이터로 사용
 
- 			## test_size = len(ChillerStatus_wo_nan) - train_size
 
- 			trainStatus = np.array(ChillerStatus_wo_nan[0:train_size])
 
- 			trainCalAmount = np.array(CalAmount_wo_nan[0:train_size])
 
- 			trainRefStatus1 = np.array(RefStatus1_wo_nan[0:train_size])
 
- 			trainRefStatus2 = np.array(RefStatus2_wo_nan[0:train_size])
 
- 			
 
- 			GradientCalAmount_mode_Icing = []
 
- 			GradientCalAmount_mode_StorageOnly = []
 
- 			GradientCalAmount_mode_Parallel = []
 
- 			GradientCalAmount_mode_ChillerOnly = []
 
- 			isNan_Point = False
 
- 			for i in range(len(trainStatus)):
 
- 				for j in range(len(nan_point)):
 
- 					if i == nan_point[j]:
 
- 						isNan_Point=True
 
- 						break
 
- 				if not isNan_Point:
 
- 					if trainStatus[i] == Icing and trainCalAmount[i] > trainCalAmount[i-1] and trainRefStatus1[i] == 1 and trainRefStatus2[i] == 1:
 
- 						GradientCalAmount_mode_Icing.append(trainCalAmount[i]-trainCalAmount[i-1])
 
- 					elif trainStatus[i] == StorageOnly and trainCalAmount[i] < trainCalAmount[i-1]:
 
- 						GradientCalAmount_mode_StorageOnly.append(trainCalAmount[i]-trainCalAmount[i-1])
 
- 					elif trainStatus[i] == Parallel and trainCalAmount[i] < trainCalAmount[i-1] and (trainRefStatus1[i] == 1 or trainRefStatus2[i] == 1):
 
- 						GradientCalAmount_mode_Parallel.append(trainCalAmount[i]-trainCalAmount[i-1])
 
- 					elif trainStatus[i] == ChillerOnly and trainRefStatus1[i] == 1 and trainRefStatus2[i] == 1:
 
- 						GradientCalAmount_mode_ChillerOnly.append(trainCalAmount[i]-trainCalAmount[i-1])
 
- 				isNan_Point = False
 
- 				
 
- 			GradientCalAmount_w3sigma_mode_Icing = []
 
- 			if len(GradientCalAmount_mode_Icing) != 0:
 
- 				max3sigma_mode_Icing = np.mean(GradientCalAmount_mode_Icing)+np.std(GradientCalAmount_mode_Icing)*3
 
- 				min3sigma_mode_Icing = np.mean(GradientCalAmount_mode_Icing)-np.std(GradientCalAmount_mode_Icing)*3
 
- 			GradientCalAmount_w3sigma_mode_StorageOnly = []
 
- 			if len(GradientCalAmount_mode_StorageOnly) != 0:
 
- 				max3sigma_mode_StorageOnly = np.mean(GradientCalAmount_mode_StorageOnly)+np.std(GradientCalAmount_mode_StorageOnly)*3
 
- 				min3sigma_mode_StorageOnly = np.mean(GradientCalAmount_mode_StorageOnly)-np.std(GradientCalAmount_mode_StorageOnly)*3
 
- 			GradientCalAmount_w3sigma_mode_Parallel = []
 
- 			if len(GradientCalAmount_mode_Parallel) != 0:
 
- 				max3sigma_mode_Parallel = np.mean(GradientCalAmount_mode_Parallel)+np.std(GradientCalAmount_mode_Parallel)*3
 
- 				min3sigma_mode_Parallel = np.mean(GradientCalAmount_mode_Parallel)-np.std(GradientCalAmount_mode_Parallel)*3
 
- 			GradientCalAmount_w3sigma_mode_ChillerOnly = []
 
- 			if len(GradientCalAmount_mode_ChillerOnly) != 0:
 
- 				max3sigma_mode_ChillerOnly = np.mean(GradientCalAmount_mode_ChillerOnly)+np.std(GradientCalAmount_mode_ChillerOnly)*3
 
- 				min3sigma_mode_ChillerOnly = np.mean(GradientCalAmount_mode_ChillerOnly)-np.std(GradientCalAmount_mode_ChillerOnly)*3
 
- 				
 
- 			for i in range(len(GradientCalAmount_mode_Icing)):
 
- 				if GradientCalAmount_mode_Icing[i] <= max3sigma_mode_Icing and GradientCalAmount_mode_Icing[i] >= min3sigma_mode_Icing:
 
- 					GradientCalAmount_w3sigma_mode_Icing.append(GradientCalAmount_mode_Icing[i])
 
- 					
 
- 			for i in range(len(GradientCalAmount_mode_StorageOnly)):
 
- 				if GradientCalAmount_mode_StorageOnly[i] <= max3sigma_mode_StorageOnly and GradientCalAmount_mode_StorageOnly[i] >= min3sigma_mode_StorageOnly:
 
- 					GradientCalAmount_w3sigma_mode_StorageOnly.append(GradientCalAmount_mode_StorageOnly[i])
 
- 					
 
- 			for i in range(len(GradientCalAmount_mode_Parallel)):
 
- 				if GradientCalAmount_mode_Parallel[i] <= max3sigma_mode_Parallel and GradientCalAmount_mode_Parallel[i] >= min3sigma_mode_Parallel:
 
- 					GradientCalAmount_w3sigma_mode_Parallel.append(GradientCalAmount_mode_Parallel[i])
 
- 					
 
- 			for i in range(len(GradientCalAmount_mode_ChillerOnly)):
 
- 				if GradientCalAmount_mode_ChillerOnly[i] <= max3sigma_mode_ChillerOnly and GradientCalAmount_mode_ChillerOnly[i] >= min3sigma_mode_ChillerOnly:
 
- 					GradientCalAmount_w3sigma_mode_ChillerOnly.append(GradientCalAmount_mode_ChillerOnly[i])
 
- 						
 
- 			#print(np.mean(GradientCalAmount_w3sigma_mode_Icing), np.mean(GradientCalAmount_w3sigma_mode_StorageOnly), np.mean(GradientCalAmount_w3sigma_mode_Parallel), np.mean(GradientCalAmount_w3sigma_mode_ChillerOnly))
 
- 			#print(np.std(GradientCalAmount_w3sigma_mode_Icing), np.std(GradientCalAmount_w3sigma_mode_StorageOnly), np.std(GradientCalAmount_w3sigma_mode_Parallel), np.std(GradientCalAmount_w3sigma_mode_ChillerOnly))
 
- 			print('************ (Finish) Load & pre-processing data !! ************')
 
- 			print('****************************************************************')
 
- 			#######################################################################################
 
- 			############################################################################################################
 
- 			#################### Prediction for the Degree of Daily Deicing ############################################
 
- 			## 	매일 21시~21시 15분 사이에 산출 및 DB 삽입
 
- 			
 
- 			if (now.hour == 21 and (now.minute > 0 or now.minute < 16)) or Init:
 
- 				print('************ (Start) The Degree of Daily Deicing is being predicted!! ************')
 
- 				DailyDeicingAmount = []
 
- 				DailyDeicingAmount_kWh = []
 
- 				idx = 0
 
- 				
 
- 				if isRecent and now.hour < 21:	## 21시를 전, 후로 익일 예상 방냉량이 업데이트
 
- 					_DayPeriod = DayPeriod-1
 
- 				else:
 
- 					_DayPeriod = DayPeriod
 
- 				for i in range(_DayPeriod):
 
- 					tmpAmount = []
 
- 					tmpAmount_kWh = []
 
- 					
 
- 					if i == 0:
 
- 						time_length = 4*21 # 첫번째 날은 저녁 9시까지 방냉량만 산출
 
- 					else:
 
- 						time_length = 96
 
- 					for time_idx in range(time_length):    
 
- 						if q_accum_percent[idx] > q_accum_percent[idx+1]:
 
- 							tmpAmount.append(q_accum_percent[idx]-q_accum_percent[idx+1])
 
- 							tmpAmount_kWh.append(q_accum_kWh[idx]-q_accum_kWh[idx+1])
 
- 						idx += 1
 
- 					if len(tmpAmount) > 0:
 
- 						DailyDeicingAmount.append(sum(tmpAmount))
 
- 						DailyDeicingAmount_kWh.append(sum(tmpAmount_kWh))
 
- 					else:
 
- 						DailyDeicingAmount.append(0)
 
- 						DailyDeicingAmount_kWh.append(0)
 
- 				DateinDay=[]
 
- 				for k in range(_DayPeriod):
 
- 					DateinDay.append(RawDate[k*DataRes_96])
 
- 				DoW, DayType = getDayType(DateinDay, _DayPeriod, SpecialHoliday)
 
- 				# Collect the normal data
 
- 				X = []
 
- 				Y = []
 
- 				_isnan = False
 
- 				for i in range(_DayPeriod):
 
- 					if DayType[i][0] < 3 and DailyDeicingAmount[i] > 0:     ## 평일이면서 축열조를 가동하고 결측값이 없는 날만 추출
 
- 						if i == _DayPeriod-1:
 
- 							time_len = int(len(ChillerStatus)%96)
 
- 						else:							
 
- 							time_len = DataRes_96
 
- 						for j in range(time_len):
 
- 							if math.isnan(ChillerStatus[i*DataRes_96+j]):
 
- 								_isnan = True
 
- 						if not _isnan:
 
- 							X.append([WFTemperatureMax_w_nan[i], WFTemperatureMin_w_nan[i], WFTemperatureMean_w_nan[i], WFHumidityMax_w_nan[i], WFHumidityMin_w_nan[i], WFHumidityMean_w_nan[i]])
 
- 							Y.append(DailyDeicingAmount[i])
 
- 						_isnan = False
 
- 					
 
- 				xTrain, xVal, yTrain, yVal = train_test_split(X, Y, test_size=0.001, shuffle = False)
 
- 				xTomorrow_WF = [WFTemperatureMax_w_nan[_DayPeriod], WFTemperatureMin_w_nan[_DayPeriod],WFTemperatureMean_w_nan[_DayPeriod], WFHumidityMax_w_nan[_DayPeriod], WFHumidityMin_w_nan[_DayPeriod], WFHumidityMean_w_nan[_DayPeriod]]
 
- 				#MSE의 변화를 확인하기 위하여 앙상블의 크기 범위에서 랜덤 포레스트 트레이닝
 
- 				maeOos = []
 
- 				Acc_CVRMSE = []
 
- 				Acc_MBE = []
 
- 				nTreeList = range(100, 200, 50)
 
- 				for iTrees in nTreeList:
 
- 					depth = None
 
- 					maxFeat = np.matrix(X).shape[1] #조정해볼 것
 
- 					DailyDeicing_RFModel = ensemble.RandomForestRegressor(n_estimators=iTrees,
 
- 									max_depth=depth, max_features=maxFeat,
 
- 									oob_score=False, random_state=42)
 
- 					DailyDeicing_RFModel.fit(xTrain, yTrain)
 
- 					#데이터 세트에 대한 MSE 누적
 
- 					prediction = DailyDeicing_RFModel.predict(xVal)
 
- 					
 
- 					maeOos.append(MAE(yVal, prediction))
 
- 					Acc_MBE.append(MBE(yVal, prediction))
 
- 					Acc_CVRMSE.append(CVRMSE(np.array(yVal), np.array(prediction)))
 
- 				#print('prediction', prediction)
 
- 				#print('yVal', yVal)
 
- 					
 
- 				#print("Validation Set of MAE : ",maeOos[-1])
 
- 				#print("Validation Set of CVRMSE : ", CVRMSE(yVal, prediction))
 
- 				#print("Validation Set of Aver. CVRMSE : ", np.mean(Acc_CVRMSE))
 
- 				PredictedDeIcingAmount = DailyDeicing_RFModel.predict([xTomorrow_WF])    ## 학습모델을 통한 익일 방냉량 예측
 
- 				PredictedDeIcingAmount_Tomorrow = round(PredictedDeIcingAmount[0],6)
 
- 				print('####################################################')
 
- 				print('## Estimated daily Deicing amount = ', PredictedDeIcingAmount_Tomorrow, ' % ##')
 
- 				print('####################################################')
 
- 				
 
- 				#### 익일 방냉량 DB 삽입
 
- 				### Day-ahead deicing amount is updated everyday
 
- 				# MSSQL Access
 
- 				conn = pymssql.connect(host = targetDBIP, user = targetDBUserID, password = targetDBUserPW, database = targetDBName, autocommit=True)
 
- 				# Create Cursor from Connection
 
- 				cursor = conn.cursor()
 
- 						
 
- 				if now.hour >= 21:			
 
- 					TargetDate = datetime.datetime(now.year,now.month,now.day,21,0,0) + datetime.timedelta(days=1)
 
- 				else:
 
- 					TargetDate = datetime.datetime(now.year,now.month,now.day,21,0,0)
 
- 				
 
- 				## Storage deicing amount
 
- 				cursor.execute("SELECT * FROM " + targetDBName + ".dbo.BemsMonitoringPointForecastingDayAhead where SiteId = 1 and FacilityTypeId = 3 and FacilityCode = 4478 and PropertyId = 0 and TargetDateTime = '" + TargetDate.strftime('%Y-%m-%d %H:00:00') + "' order by CreatedDateTime desc")	
 
- 								
 
- 				# 데이타 하나씩 Fetch하여 출력
 
- 				row = cursor.fetchone()
 
- 				rawData=[]
 
- 				while row:
 
- 					row = cursor.fetchone()
 
- 					rawData.append(row)	
 
- 				if rawData:			
 
- 					try:
 
- 						cursor.execute("UPDATE " + targetDBName + ".dbo.BemsMonitoringPointForecastingDayAhead set CreatedDateTime = '"+ datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') +"', ForecastedValue = " + str(PredictedDeIcingAmount_Tomorrow) + " where SiteId = 1 and FacilityTypeId = 3 and FacilityCode = 4478 and PropertyId = 0 and TargetDateTime = '"+ TargetDate.strftime('%Y-%m-%d %H:%M:00')+"'")
 
- 						
 
- 						print("* The prediction of Daily deicing amount was updated!! (Recommend)")
 
- 					except:
 
- 						print("[ERROR] There is an update error!! (Daily deicing amount)")
 
- 				else:
 
- 					try:
 
- 						cursor.execute("INSERT INTO " + targetDBName + ".dbo.BemsMonitoringPointForecastingDayAhead (SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,TargetDateTime,ForecastedValue) VALUES(1,3,4478,0,'" + datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') + "','" + TargetDate.strftime('%Y-%m-%d %H:%M:00') + "', "+ str(PredictedDeIcingAmount_Tomorrow) + ")" )
 
- 						
 
- 						print("* The prediction of daily deicing amount was inserted!! (Recommend)")
 
- 					except:
 
- 						print("[ERROR] There is an insert error!! (Daily deicing amount)")
 
- 				
 
- 								
 
- 				print('************ (Finish) The Degree of Daily Deicing is being predicted!! ************')
 
- 				print('***********************************************************************************')
 
- 				#######################################################################################
 
- 			##################################################################################################################################################
 
- 			################# Find Optimal Operating Schedule for predicted daily deicing amount #############################################################
 
- 			## 	15분 주기로 현상태 반영하여 업데이트
 
- 			
 
- 			print('************ (Start) Recommended operating schedule is being found!! ************')
 
- 			
 
- 			if now.hour >= 0 and now.hour < 21:
 
- 				simul_lth = 24*4 - (now.hour*4 + int(now.minute/15)) - 3*4 	## (15분 단위 카운트)
 
- 			else:
 
- 				simul_lth = 24*4 - (now.hour*4 +int(now.minute/15)) + 21*4
 
- 			# 이미 지난 시간(전날 9 pm 이후)에 대한 데이터 정리
 
- 			inputX_prev = ChillerStatus_wo_nan[len(ChillerStatus_wo_nan)-(96-simul_lth):len(ChillerStatus_wo_nan)]
 
- 			inputX_REF1_prev = RefStatus1_wo_nan[len(RefStatus1_wo_nan)-(96-simul_lth):len(RefStatus1_wo_nan)]
 
- 			inputX_REF2_prev = RefStatus2_wo_nan[len(RefStatus2_wo_nan)-(96-simul_lth):len(RefStatus2_wo_nan)]
 
- 			RecommendedCalAmount_prev = CalAmount_wo_nan[len(CalAmount_wo_nan)-(96-simul_lth):len(CalAmount_wo_nan)]
 
- 			
 
- 			print('* Current Amount : ', CalAmount_wo_nan[-1], '[%], ', 'Estimated Deicing Amount : ', PredictedDeIcingAmount_Tomorrow, '[%]')
 
- 			idx = 0
 
- 			TermNum = 96
 
- 			RecommendedCalAmount = [CalAmount_wo_nan[-1]]
 
- 			
 
- 			if now.hour >= 21 or now.hour < 6:
 
- 				while RecommendedCalAmount[-1] < PredictedDeIcingAmount_Tomorrow:
 
- 					idx += 1
 
- 					if idx >= simul_lth:
 
- 						print("* It should be fully operated")
 
- 						break
 
- 					inputX = []
 
- 					inputX_REF1 = []
 
- 					inputX_REF2 = []
 
- 					## 단순히 심야 운전만 고려하고 축냉량 시 제빙모드와 OFF만 고려하여 시뮬레이션 (다른 모드를 추가하여 구성할 수 있음) 
 
- 					## Off=0, Icing = 10, StorageOnly = 20, Parallel = 30, ChillerOnly = 40
 
- 					## 추천 방냉은 저녁 9시 이후부터 아침 6시 사이까지.... 중간에 사용하고 있는 부분에 대한 것은 어떻게 처리할지...고민해야함...낮에 축단운전을 하기에....
 
- 					for i in range(idx):
 
- 						inputX.append(Icing)
 
- 						inputX_REF1.append(1)
 
- 						inputX_REF2.append(1)
 
- 					for i in range(simul_lth-len(inputX)):
 
- 						inputX.append(0)
 
- 						inputX_REF1.append(0)
 
- 						inputX_REF2.append(0)
 
- 						
 
- 					RecommendedCalAmount = [CalAmount_wo_nan[-1]]
 
- 					for i in range(len(inputX)):
 
- 						if i == 1:
 
- 							RecommendedCalAmount = RecommendedCalAmount[-1]
 
- 						if inputX[i]==Icing:
 
- 							if inputX_REF1[i] + inputX_REF2[i]==2:
 
- 								RecommendedCalAmount.append(RecommendedCalAmount[-1]+np.mean(GradientCalAmount_w3sigma_mode_Icing))
 
- 							elif inputX_REF1[i] + inputX_REF2[i]==1:
 
- 								RecommendedCalAmount.append(RecommendedCalAmount[-1]+np.mean(GradientCalAmount_w3sigma_mode_Icing)/2)
 
- 							else:
 
- 								RecommendedCalAmount.append(RecommendedCalAmount[-1])
 
- 						elif inputX[i]==StorageOnly:
 
- 							RecommendedCalAmount.append(RecommendedCalAmount[-1]+np.mean(GradientCalAmount_w3sigma_mode_StorageOnly))
 
- 						elif inputX[i]==Parallel:
 
- 							if inputX_REF1[i] + inputX_REF2[i]==2:
 
- 								RecommendedCalAmount.append(RecommendedCalAmount[-1]+np.mean(GradientCalAmount_w3sigma_mode_Parallel)*2)
 
- 							elif inputX_REF1[i] + inputX_REF2[i]==1:
 
- 								RecommendedCalAmount.append(RecommendedCalAmount[-1]+np.mean(GradientCalAmount_w3sigma_mode_Parallel))
 
- 							else:
 
- 								RecommendedCalAmount.append(RecommendedCalAmount[-1])
 
- 						elif inputX[i]==ChillerOnly:
 
- 							if inputX_REF1[i] + inputX_REF2[i]==2:
 
- 								RecommendedCalAmount.append(RecommendedCalAmount[-1]+np.mean(GradientCalAmount_w3sigma_mode_ChillerOnly))
 
- 							elif inputX_REF1[i] + inputX_REF2[i]==1:
 
- 								RecommendedCalAmount.append(RecommendedCalAmount[-1]+np.mean(GradientCalAmount_w3sigma_mode_ChillerOnly)/2)
 
- 							else:
 
- 								RecommendedCalAmount.append(RecommendedCalAmount[-1])
 
- 						elif inputX[i]==0:
 
- 							RecommendedCalAmount.append(RecommendedCalAmount[-1])
 
- 						## 0이나 100을 넘어갔을 경우 보정 (현재 데이터에서 축열량은 % 단위이기 때문에)
 
- 						if RecommendedCalAmount[-1] >= 100:
 
- 							RecommendedCalAmount[-1] = 100
 
- 						elif RecommendedCalAmount[-1] <= 0:
 
- 							RecommendedCalAmount[-1] = 0
 
- 					#print('max.',np.max(RecommendedCalAmount[-1]))
 
- 					
 
- 			else:
 
- 				print("************ It is not time to operate the storage in icing mode ")
 
- 				
 
- 			if idx == 0:
 
- 				inputX = []
 
- 				inputX_REF1 = []
 
- 				inputX_REF2 = []
 
- 				RecommendedCalAmount = []
 
- 				for i in range(simul_lth):
 
- 					inputX.append(0)
 
- 					inputX_REF1.append(0)
 
- 					inputX_REF2.append(0)
 
- 					RecommendedCalAmount.append(CalAmount_wo_nan[-1])
 
- 			inputX = inputX_prev + inputX
 
- 			inputX_REF1 = inputX_REF1_prev + inputX_REF1
 
- 			inputX_REF2 = inputX_REF2_prev + inputX_REF2
 
- 			RecommendedCalAmount = RecommendedCalAmount_prev + RecommendedCalAmount
 
- 					
 
- 			#### 실제 및 추천 운전 스케쥴 DB 삽입 
 
- 			#### Recommended operating schedule is updated everyday
 
- 			# MSSQL Access
 
- 			conn = pymssql.connect(host = targetDBIP, user = targetDBUserID, password = targetDBUserPW, database = targetDBName, autocommit=True)
 
- 			# Create Cursor from Connection
 
- 			cursor = conn.cursor()
 
- 			
 
- 			# Execute SQL
 
- 			if now.hour >= 21:
 
- 				InitDate = datetime.datetime(now.year,now.month,now.day,21,0,0)
 
- 			else:
 
- 				InitDate = datetime.datetime(now.year,now.month,now.day,21,0,0)-datetime.timedelta(days=1)
 
- 				
 
- 			## Storage mode
 
- 			cursor.execute("SELECT * FROM " + targetDBName + ".dbo.BemsIceThermalStorageSimulation where SiteId = 1 and FacilityTypeId = 3 and FacilityCode = 4478 and PropertyId = 16 and SimulationCase = 0 and TargetDateTime >= '" + InitDate.strftime('%Y-%m-%d %H:00:00') + "' and TargetDateTime < '" + (InitDate + datetime.timedelta(days=1)).strftime('%Y-%m-%d %H:00:00') + "'  order by CreatedDateTime desc")
 
- 			
 
- 			# 데이타 하나씩 Fetch하여 출력
 
- 			row = cursor.fetchone()
 
- 			rawData=[]
 
- 			while row:
 
- 				row = cursor.fetchone()
 
- 				rawData.append(row)
 
- 			if rawData:			
 
- 				try:
 
- 					for i in range(TermNum):
 
- 						TmpDate = InitDate + datetime.timedelta(minutes=i*15)
 
- 						cursor.execute("UPDATE " + targetDBName + ".dbo.BemsIceThermalStorageSimulation set CreatedDateTime = '"+ datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') +"', SimulationValue = " + str(inputX[i]) + " where SiteId = 1 and FacilityTypeId = 3 and FacilityCode = 4478 and PropertyId = 16 and SimulationCase = 0 and TargetDateTime = '"+ TmpDate.strftime('%Y-%m-%d %H:%M:00')+"'")
 
- 						
 
- 					print("* The storage operating schedule was updated!! (Recommend)")
 
- 				except:
 
- 					print("[ERROR] There is an update error!! (Ice storage mode)")
 
- 			else:
 
- 				try:
 
- 					for i in range(TermNum):
 
- 						TmpDate = InitDate + datetime.timedelta(minutes=i*15)
 
- 						cursor.execute("INSERT INTO " + targetDBName + ".dbo.BemsIceThermalStorageSimulation (SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,TargetDateTime,SimulationValue,SimulationCase) VALUES(1,3,4478,16,'" + datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') + "','" + TmpDate.strftime('%Y-%m-%d %H:%M:00') + "', "+ str(inputX[i]) + ", 0)" )
 
- 						
 
- 					print("* The storage operating schedule was inserted!! (Recommend)")
 
- 				except:
 
- 					print("[ERROR] There is an insert error!! (Ice storage mode)")
 
- 			
 
- 			## REF1 status
 
- 			cursor.execute("SELECT * FROM " + targetDBName + ".dbo.BemsIceThermalStorageSimulation where SiteId = 1 and FacilityTypeId = 2 and FacilityCode = 4479 and PropertyId = 15 and SimulationCase = 0 and TargetDateTime >= '" + InitDate.strftime('%Y-%m-%d %H:00:00') + "' and TargetDateTime < '" + (InitDate + datetime.timedelta(days=1)).strftime('%Y-%m-%d %H:00:00') + "'  order by CreatedDateTime desc")		
 
- 			
 
- 			# 데이타 하나씩 Fetch하여 출력
 
- 			row = cursor.fetchone()
 
- 			rawData=[]
 
- 			while row:
 
- 				row = cursor.fetchone()
 
- 				rawData.append(row)	
 
- 			if rawData:			
 
- 				try:
 
- 					for i in range(TermNum):
 
- 						TmpDate = InitDate + datetime.timedelta(minutes=i*15)
 
- 						cursor.execute("UPDATE " + targetDBName + ".dbo.BemsIceThermalStorageSimulation set CreatedDateTime = '"+ datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') +"', SimulationValue = " + str(inputX_REF1[i]) + " where SiteId = 1 and FacilityTypeId = 2 and FacilityCode = 4479 and PropertyId = 15 and SimulationCase = 0 and TargetDateTime = '"+ TmpDate.strftime('%Y-%m-%d %H:%M:00')+"'")
 
- 									
 
- 					print("* The refrigerator1 status was updated!! (Recommend)")
 
- 				except:
 
- 					print("[Error] There is an update error!! (Recommended refrigerator1 status)")
 
- 			else:
 
- 				try:
 
- 					for i in range(TermNum):
 
- 						TmpDate = InitDate + datetime.timedelta(minutes=i*15)
 
- 						cursor.execute("INSERT INTO " + targetDBName + ".dbo.BemsIceThermalStorageSimulation (SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,TargetDateTime,SimulationValue,SimulationCase) VALUES(1,2,4479,15,'" + datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') + "','" + TmpDate.strftime('%Y-%m-%d %H:%M:00') + "', "+ str(inputX_REF1[i]) + ", 0)" )
 
- 						
 
- 					print("* The refrigerator1 status was inserted!! (Recommend)")
 
- 				except:
 
- 					print("[Error] There is an insert error!! (Recommended refrigerator1 status)")
 
- 			
 
- 			## REF1 power consume
 
- 			cursor.execute("SELECT * FROM " + targetDBName + ".dbo.BemsIceThermalStorageSimulation where SiteId = 1 and FacilityTypeId = 2 and FacilityCode = 4479 and PropertyId = 11 and SimulationCase = 0 and TargetDateTime >= '" + InitDate.strftime('%Y-%m-%d %H:00:00') + "' and TargetDateTime < '" + (InitDate + datetime.timedelta(days=1)).strftime('%Y-%m-%d %H:00:00') + "'  order by CreatedDateTime desc")		
 
- 			
 
- 			# 데이타 하나씩 Fetch하여 출력
 
- 			row = cursor.fetchone()
 
- 			rawData=[]
 
- 			while row:
 
- 				row = cursor.fetchone()
 
- 				rawData.append(row)	
 
- 			if rawData:			
 
- 				try:
 
- 					for i in range(TermNum):
 
- 						TmpDate = InitDate + datetime.timedelta(minutes=i*15)
 
- 						if inputX_REF1[i]==1:
 
- 							TmpComsume = mean_RefConsume1
 
- 						else:
 
- 							TmpComsume = 0
 
- 						cursor.execute("UPDATE " + targetDBName + ".dbo.BemsIceThermalStorageSimulation set CreatedDateTime = '"+ datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') +"', SimulationValue = " + str(TmpComsume) + " where SiteId = 1 and FacilityTypeId = 2 and FacilityCode = 4479 and PropertyId = 11 and SimulationCase = 0 and TargetDateTime = '"+ TmpDate.strftime('%Y-%m-%d %H:%M:00')+"'")
 
- 										
 
- 					print("* The recommended refrigerator1 power was updated!! (Recommend)")
 
- 				except:
 
- 					print("[ERROR] There is an update error!! (Recommended refrigerator1 power)")
 
- 			else:
 
- 				try:
 
- 					for i in range(TermNum):
 
- 						TmpDate = InitDate + datetime.timedelta(minutes=i*15)
 
- 						if inputX_REF1[i]==1:
 
- 							TmpComsume = mean_RefConsume1
 
- 						else:
 
- 							TmpComsume = 0
 
- 						cursor.execute("INSERT INTO " + targetDBName + ".dbo.BemsIceThermalStorageSimulation (SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,TargetDateTime,SimulationValue,SimulationCase) VALUES(1,2,4479,11,'" + datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') + "','" + TmpDate.strftime('%Y-%m-%d %H:%M:00') + "', "+ str(TmpComsume) + ", 0)" )
 
- 						
 
- 					print("* The recommended refrigerator1 power was inserted!! (Recommend)")
 
- 				except:
 
- 					print("[ERROR] There is an insert error!! (Recommended refrigerator1 power)")
 
- 					
 
- 			## REF2 status
 
- 			cursor.execute("SELECT * FROM " + targetDBName + ".dbo.BemsIceThermalStorageSimulation where SiteId = 1 and FacilityTypeId = 2 and FacilityCode = 4480 and PropertyId = 15 and SimulationCase = 0 and TargetDateTime >= '" + InitDate.strftime('%Y-%m-%d %H:00:00') + "' and TargetDateTime < '" + (InitDate + datetime.timedelta(days=1)).strftime('%Y-%m-%d %H:00:00') + "'  order by CreatedDateTime desc")	
 
- 						
 
- 			# 데이타 하나씩 Fetch하여 출력
 
- 			row = cursor.fetchone()
 
- 			rawData=[]
 
- 			while row:
 
- 				row = cursor.fetchone()
 
- 				rawData.append(row)	
 
- 			if rawData:			
 
- 				try:
 
- 					for i in range(TermNum):
 
- 						TmpDate = InitDate + datetime.timedelta(minutes=i*15)
 
- 						cursor.execute("UPDATE " + targetDBName + ".dbo.BemsIceThermalStorageSimulation set CreatedDateTime = '"+ datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') +"', SimulationValue = " + str(inputX_REF2[i]) + " where SiteId = 1 and FacilityTypeId = 2 and FacilityCode = 4480 and PropertyId = 15 and SimulationCase = 0 and TargetDateTime = '"+ TmpDate.strftime('%Y-%m-%d %H:%M:00')+"'")
 
- 						
 
- 					print("* The refrigerator2 status was updated!! (Recommend)")
 
- 				except:
 
- 					print("[ERROR] There is an update error!! (Recommended refrigerator2 status)")
 
- 			else:
 
- 				try:
 
- 					for i in range(TermNum):
 
- 						TmpDate = InitDate + datetime.timedelta(minutes=i*15)
 
- 						cursor.execute("INSERT INTO " + targetDBName + ".dbo.BemsIceThermalStorageSimulation (SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,TargetDateTime,SimulationValue,SimulationCase) VALUES(1,2,4480,15,'" + datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') + "','" + TmpDate.strftime('%Y-%m-%d %H:%M:00') + "', "+ str(inputX_REF2[i]) + ", 0)" )
 
- 						
 
- 					print("* The refrigerator2 status was inserted!! (Recommend)")
 
- 				except:
 
- 					print("[ERROR] There is an insert error!! (Recommended refrigerator2 status)")
 
- 				
 
- 			## REF2 power consume
 
- 			cursor.execute("SELECT * FROM " + targetDBName + ".dbo.BemsIceThermalStorageSimulation where SiteId = 1 and FacilityTypeId = 2 and FacilityCode = 4480 and PropertyId = 11 and SimulationCase = 0 and TargetDateTime >= '" + InitDate.strftime('%Y-%m-%d %H:00:00') + "' and TargetDateTime < '" + (InitDate + datetime.timedelta(days=1)).strftime('%Y-%m-%d %H:00:00') + "'  order by CreatedDateTime desc")		
 
- 			
 
- 			# 데이타 하나씩 Fetch하여 출력
 
- 			row = cursor.fetchone()
 
- 			rawData=[]
 
- 			while row:
 
- 				row = cursor.fetchone()
 
- 				rawData.append(row)	
 
- 			if rawData:			
 
- 				try:
 
- 					for i in range(TermNum):
 
- 						TmpDate = InitDate + datetime.timedelta(minutes=i*15)
 
- 						if inputX_REF2[i]==1:
 
- 							TmpComsume = mean_RefConsume2
 
- 						else:
 
- 							TmpComsume = 0
 
- 						cursor.execute("UPDATE " + targetDBName + ".dbo.BemsIceThermalStorageSimulation set CreatedDateTime = '"+ datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') +"', SimulationValue = " + str(TmpComsume) + " where SiteId = 1 and FacilityTypeId = 2 and FacilityCode = 4480 and PropertyId = 11 and SimulationCase = 0 and TargetDateTime = '"+ TmpDate.strftime('%Y-%m-%d %H:%M:00')+"'")
 
- 						
 
- 					print("* The recommended refrigerator2 power was updated!! (Recommend)")
 
- 				except:
 
- 					print("[ERROR] There is an update error!! (Recommended Refrigerator2 power)")
 
- 			else:
 
- 				try:
 
- 					for i in range(TermNum):
 
- 						TmpDate = InitDate + datetime.timedelta(minutes=i*15)
 
- 						if inputX_REF2[i]==1:
 
- 							TmpComsume = mean_RefConsume2
 
- 						else:
 
- 							TmpComsume = 0
 
- 						cursor.execute("INSERT INTO " + targetDBName + ".dbo.BemsIceThermalStorageSimulation (SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,TargetDateTime,SimulationValue,SimulationCase) VALUES(1,2,4480,11,'" + datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') + "','" + TmpDate.strftime('%Y-%m-%d %H:%M:00') + "', "+ str(TmpComsume) + ", 0)" )
 
- 						
 
- 					print("* The refrigerator2 power was inserted!! (Recommend)")
 
- 				except:
 
- 					print("[ERROR] There is an insert error!! (Recommended Refrigerator2 power)")
 
- 			
 
- 			## Thermal energy amount
 
- 			cursor.execute("SELECT * FROM " + targetDBName + ".dbo.BemsIceThermalStorageSimulation where SiteId = 1 and FacilityTypeId = 3 and FacilityCode = 4478 and PropertyId = 2 and SimulationCase = 0 and TargetDateTime >= '" + InitDate.strftime('%Y-%m-%d %H:00:00') + "' and TargetDateTime < '" + (InitDate + datetime.timedelta(days=1)).strftime('%Y-%m-%d %H:00:00') + "'  order by CreatedDateTime desc")
 
- 			
 
- 			# 데이타 하나씩 Fetch하여 출력
 
- 			row = cursor.fetchone()
 
- 			rawData=[]
 
- 			while row:
 
- 				row = cursor.fetchone()
 
- 				rawData.append(row)
 
- 			if rawData:			
 
- 				try:
 
- 					for i in range(TermNum):
 
- 						TmpDate = InitDate + datetime.timedelta(minutes=i*15)
 
- 						cursor.execute("UPDATE " + targetDBName + ".dbo.BemsIceThermalStorageSimulation set CreatedDateTime = '"+ datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') +"', SimulationValue = " + str(RecommendedCalAmount[i]) + " where SiteId = 1 and FacilityTypeId = 3 and FacilityCode = 4478 and PropertyId = 2 and SimulationCase = 0 and TargetDateTime = '"+ TmpDate.strftime('%Y-%m-%d %H:%M:00')+"'")
 
- 						
 
- 					print("* Thermal energy amount was updated!! (Recommend)")
 
- 				except:
 
- 					print("[ERROR] There is an update error!! (Recommended thermal energy amount)")
 
- 			else:
 
- 				try:
 
- 					for i in range(TermNum):
 
- 						TmpDate = InitDate + datetime.timedelta(minutes=i*15)
 
- 						cursor.execute("INSERT INTO " + targetDBName + ".dbo.BemsIceThermalStorageSimulation (SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,TargetDateTime,SimulationValue,SimulationCase) VALUES(1,3,4478,2,'" + datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') + "','" + TmpDate.strftime('%Y-%m-%d %H:%M:00') + "', "+ str(RecommendedCalAmount[i]) + ", 0)" )
 
- 						
 
- 					print("* Thermal energy amount was inserted!! (Recommend)")
 
- 				except:
 
- 					print("[ERROR] There is an insert error!! (Recommended thermal energy amount)")
 
- 			
 
- 			## 첫 실행시에만 동작
 
- 			if Init:
 
- 				## Thermal energy amount (과거 확인 후 축열량이 공백인 경우 채워주기)
 
- 				CalAmount_prev_tmp = CalAmount_prev[len(CalAmount_prev)-TermNum*5:]
 
- 				for d in range(5, 0, -1):		# 5일전까지
 
- 					InitDate_tmp = InitDate-datetime.timedelta(days=d)
 
- 					
 
- 					for m in range(TermNum):	# 1열씩 업데이트 (중간중간 공백인 경우를 고려)
 
- 						TmpDate = InitDate_tmp + datetime.timedelta(minutes=m*15)
 
- 						cursor.execute("SELECT * FROM " + targetDBName + ".dbo.BemsIceThermalStorageSimulation where SiteId = 1 and FacilityTypeId = 3 and FacilityCode = 4478 and PropertyId = 2 and SimulationCase = 0 and TargetDateTime = '" + TmpDate.strftime('%Y-%m-%d %H:%M:00') + "' order by CreatedDateTime desc")
 
- 						
 
- 						# 데이타 하나씩 Fetch하여 출력
 
- 						row = cursor.fetchone()
 
- 						if row:			
 
- 							try:
 
- 								cursor.execute("UPDATE " + targetDBName + ".dbo.BemsIceThermalStorageSimulation set CreatedDateTime = '"+ (datetime.datetime.now()-datetime.timedelta(minutes=d)).strftime('%Y-%m-%d %H:%M:%S') +"', SimulationValue = " + str(CalAmount_prev_tmp[(5-d)*TermNum+m]) + " where SiteId = 1 and FacilityTypeId = 3 and FacilityCode = 4478 and PropertyId = 2 and SimulationCase = 0 and TargetDateTime = '"+ TmpDate.strftime('%Y-%m-%d %H:%M:00')+"'")
 
- 								
 
- 							except:
 
- 								print("[ERROR] There is an update error!! (Recommended thermal energy amount)")
 
- 						else:
 
- 							try:
 
- 								cursor.execute("INSERT INTO " + targetDBName + ".dbo.BemsIceThermalStorageSimulation (SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,TargetDateTime,SimulationValue,SimulationCase) VALUES(1,3,4478,2,'" + (datetime.datetime.now()-datetime.timedelta(minutes=d)).strftime('%Y-%m-%d %H:%M:%S') + "','" + TmpDate.strftime('%Y-%m-%d %H:%M:00') + "', "+ str(CalAmount_prev_tmp[(5-d)*TermNum+m]) + ", 0)" )
 
- 								
 
- 							except:
 
- 								print("[ERROR] There is an insert error!! (Recommended thermal energy amount)")
 
- 									
 
- 			conn.close()
 
- 			
 
- 			print('************ (Finish) Recommended operating schedule is being found!! ************')
 
- 			print('**********************************************************************************')
 
- 			#######################################################################################
 
- 		##################################################################################################################################################
 
- 		################# Stochastic method for estimating the Variation of Ice Thermal Storage based on Operation Mode "for Simulation" #################
 
- 		#### 사용자 정의 데이터를 데이터 로드
 
- 		### 계속 체킹
 
- 		
 
- 		while True:
 
- 			now_ = datetime.datetime.now().now()
 
- 			## sleep 매분 2,6,10,... 초에만 동작
 
- 			if now_.second%4==2:
 
- 				break
 
- 			time.sleep(1)
 
- 							
 
- 		#time.sleep(2)
 
- 		#print('start time : ', now_)	
 
- 		# MSSQL Access
 
- 		conn = pymssql.connect(host = targetDBIP, user = targetDBUserID, password = targetDBUserPW, database = targetDBName, autocommit=True)
 
- 		# Create Cursor from Connection
 
- 		cursor = conn.cursor()
 
- 		# Execute SQL 
 
- 		cursor.execute('SELECT TOP 1 * FROM '+targetDBName+'.dbo.BemsIceThermalStorageSimulation where SiteId=1 and FacilityCode=4478 and PropertyId=16 and SimulationCase=1 order by CreatedDateTime desc')
 
- 		
 
- 		
 
- 		row = cursor.fetchone()
 
- 		conn.close()
 
- 		#print('end time : ', now_)	
 
- 		if Init:
 
- 			if row != None:				
 
- 				recentDateTime = row[4]
 
- 			else:
 
- 				recentDateTime = now_
 
- 			Init = False
 
- 			ActiveSimulator = False
 
- 		if row != None:
 
- 			if recentDateTime < row[4]:
 
- 				recentDateTime =  row[4]
 
- 				ActiveSimulator = True
 
- 			else:
 
- 				ActiveSimulator = False
 
- 				
 
- 		now_ = datetime.datetime.now().now()
 
- 		if now_.second%30 > 0 and now_.second%30 < 2:
 
- 			print('* Keep an eye on updating DB table every 2 seconds ... (This message appears every 30 seconds)')
 
- 		
 
- 		if ActiveSimulator:	
 
- 			print('************ (Start) Simulator! ************')
 
- 			time.sleep(2)
 
- 			# MSSQL Access
 
- 			conn = pymssql.connect(host = targetDBIP, user = targetDBUserID, password = targetDBUserPW, database = targetDBName, autocommit=True)
 
- 			# Create Cursor from Connection
 
- 			cursor = conn.cursor()
 
- 			# Execute SQL
 
- 			InitDate = datetime.datetime(now.year, now.month, now.day, now.hour, int(int(now.minute/15)*15),0)
 
- 			
 
- 			## Storage mode
 
- 			cursor.execute("SELECT * FROM " + targetDBName + ".dbo.BemsIceThermalStorageSimulation where SiteId = 1 and FacilityTypeId = 3 and FacilityCode = 4478 and PropertyId = 16 and SimulationCase = 1 and TargetDateTime >= '" + InitDate.strftime('%Y-%m-%d %H:%M:00') + "' and TargetDateTime < '" + (InitDate + datetime.timedelta(days=1)).strftime('%Y-%m-%d %H:00:00') + "'  order by TargetDateTime asc")			
 
- 			# 데이타 한꺼번에 Fetch
 
- 			rows = cursor.fetchall()			
 
- 			rawData_StorageMode = []
 
- 			for i in rows:
 
- 				rawData_StorageMode.append(i)
 
- 				
 
- 			time.sleep(1)	
 
- 			## REF1 status
 
- 			cursor.execute("SELECT * FROM " + targetDBName + ".dbo.BemsIceThermalStorageSimulation where SiteId = 1 and FacilityTypeId = 2 and FacilityCode = 4479 and PropertyId = 15 and SimulationCase = 1 and TargetDateTime >= '" + InitDate.strftime('%Y-%m-%d %H:%M:00') + "' and TargetDateTime < '" + (InitDate + datetime.timedelta(days=1)).strftime('%Y-%m-%d %H:00:00') + "'  order by TargetDateTime asc")			
 
- 			# 데이타 한꺼번에 Fetch
 
- 			rows = cursor.fetchall()			
 
- 			rawData_RefStatus1 = []
 
- 			for i in rows:
 
- 				rawData_RefStatus1.append(i)
 
- 							
 
- 			# rawData_RefStatus1=rawData_RefStatus1[:len(rawData_RefStatus1)-1]
 
- 			#rawData_RefStatus1=rawData_RefStatus1[:-2]
 
- 						
 
- 			time.sleep(1)
 
- 			## REF2 status
 
- 			cursor.execute("SELECT * FROM " + targetDBName + ".dbo.BemsIceThermalStorageSimulation where SiteId = 1 and FacilityTypeId = 2 and FacilityCode = 4480 and PropertyId = 15 and SimulationCase = 1 and TargetDateTime >= '" + InitDate.strftime('%Y-%m-%d %H:%M:00') + "' and TargetDateTime < '" + (InitDate + datetime.timedelta(days=1)).strftime('%Y-%m-%d %H:00:00') + "'  order by TargetDateTime asc")
 
- 			# 데이타 한꺼번에 Fetch
 
- 			rows = cursor.fetchall()
 
- 			rawData_RefStatus2 = []
 
- 			for i in rows:
 
- 				rawData_RefStatus2.append(i)
 
- 			# rawData_RefStatus2=rawData_RefStatus2[:len(rawData_RefStatus2)-1]
 
- 			# rawData_RefStatus2=rawData_RefStatus2[:-2]
 
- 							
 
- 			CustomizedStatus=[]
 
- 			for i in range(len(rawData_StorageMode)):
 
- 				CustomizedStatus.append(rawData_StorageMode[i][6])
 
- 			
 
- 			CustomizedRefStatus1=[]
 
- 			for i in range(len(rawData_RefStatus1)):
 
- 				CustomizedRefStatus1.append(rawData_RefStatus1[i][6])
 
- 			
 
- 			CustomizedRefStatus2 = []
 
- 			for i in range(len(rawData_RefStatus2)):
 
- 				CustomizedRefStatus2.append(rawData_RefStatus2[i][6])
 
- 				
 
- 			# 한번 더 데이터 불러오기 (가끔 제대로 로드 안되는 경우 있음)
 
- 			time.sleep(0.5)
 
- 			if len(CustomizedStatus) != len(CustomizedRefStatus1):
 
- 				## REF1 status
 
- 				cursor.execute("SELECT * FROM " + targetDBName + ".dbo.BemsIceThermalStorageSimulation where SiteId = 1 and FacilityTypeId = 2 and FacilityCode = 4479 and PropertyId = 15 and SimulationCase = 1 and TargetDateTime >= '" + InitDate.strftime('%Y-%m-%d %H:%M:00') + "' and TargetDateTime < '" + (InitDate + datetime.timedelta(days=1)).strftime('%Y-%m-%d %H:00:00') + "'  order by TargetDateTime asc")			
 
- 				# 데이타 한꺼번에 Fetch
 
- 				rows = cursor.fetchall()			
 
- 				rawData_RefStatus1 = []
 
- 				for i in rows:
 
- 					rawData_RefStatus1.append(i)
 
- 				
 
- 				CustomizedRefStatus1=[]
 
- 				for i in range(len(rawData_RefStatus1)):
 
- 					CustomizedRefStatus1.append(rawData_RefStatus1[i][6])
 
- 				
 
- 			time.sleep(0.5)
 
- 			if len(CustomizedStatus) != len(CustomizedRefStatus2):
 
- 				## REF2 status
 
- 				cursor.execute("SELECT * FROM " + targetDBName + ".dbo.BemsIceThermalStorageSimulation where SiteId = 1 and FacilityTypeId = 2 and FacilityCode = 4480 and PropertyId = 15 and SimulationCase = 1 and TargetDateTime >= '" + InitDate.strftime('%Y-%m-%d %H:%M:00') + "' and TargetDateTime < '" + (InitDate + datetime.timedelta(days=1)).strftime('%Y-%m-%d %H:00:00') + "'  order by TargetDateTime asc")
 
- 				# 데이타 한꺼번에 Fetch
 
- 				rows = cursor.fetchall()
 
- 				rawData_RefStatus2 = []
 
- 				for i in rows:
 
- 					rawData_RefStatus2.append(i)
 
- 				
 
- 				CustomizedRefStatus2 = []
 
- 				for i in range(len(rawData_RefStatus2)):
 
- 					CustomizedRefStatus2.append(rawData_RefStatus2[i][6])
 
- 			
 
- 			
 
- 			SimulCalAmount=[CalAmount_wo_nan[-1]]
 
- 			for i in range(len(CustomizedStatus)):
 
- 				if i == 1:
 
- 					SimulCalAmount = [SimulCalAmount[-1]]
 
- 				## 제빙운전은 두대로 운영되었으므로 평균값은 2대 운전 기준
 
- 				if CustomizedStatus[i] == Icing:
 
- 					if len(GradientCalAmount_w3sigma_mode_Icing) == 0:
 
- 						print('[Warning] There is no enough data (Icing)')
 
- 						SimulCalAmount.append(SimulCalAmount[-1])
 
- 					else:
 
- 						if CustomizedRefStatus1[i] + CustomizedRefStatus2[i] == 2:
 
- 							SimulCalAmount.append(SimulCalAmount[-1]+np.mean(GradientCalAmount_w3sigma_mode_Icing))
 
- 						elif CustomizedRefStatus1[i] + CustomizedRefStatus2[i] == 1:
 
- 							SimulCalAmount.append(SimulCalAmount[-1]+np.mean(GradientCalAmount_w3sigma_mode_Icing)/2)
 
- 						else:
 
- 							SimulCalAmount.append(SimulCalAmount[-1])
 
- 				## 축단운전은 냉동기가 운영되지 않음
 
- 				elif CustomizedStatus[i] == StorageOnly:
 
- 					if len(GradientCalAmount_w3sigma_mode_StorageOnly) == 0:
 
- 						print('[Warning] There is no enough data (Storage Only)')
 
- 						SimulCalAmount.append(SimulCalAmount[-1])
 
- 					else:
 
- 						SimulCalAmount.append(SimulCalAmount[-1]+np.mean(GradientCalAmount_w3sigma_mode_StorageOnly))
 
- 				## 병렬운전에서 축열조 변화량은 냉동기 상태와 상관없음
 
- 				elif CustomizedStatus[i] == Parallel:
 
- 					if len(GradientCalAmount_w3sigma_mode_Parallel) == 0:
 
- 						print('[Warning] There is no enough data (Parallel)')
 
- 						SimulCalAmount.append(SimulCalAmount[-1])
 
- 					else:
 
- 						SimulCalAmount.append(SimulCalAmount[-1]+np.mean(GradientCalAmount_w3sigma_mode_Parallel))
 
- 				## 냉단운전은 냉동기 두대로 운영되었으므로 축열량은 그대로
 
- 				elif CustomizedStatus[i] == ChillerOnly:
 
- 					if len(GradientCalAmount_w3sigma_mode_ChillerOnly) == 0:
 
- 						print('[Warning] There is no enough data (Chiller Only)')
 
- 						SimulCalAmount.append(SimulCalAmount[-1])
 
- 					else:
 
- 						SimulCalAmount.append(SimulCalAmount[-1])
 
- 				elif CustomizedStatus[i]==0:
 
- 					SimulCalAmount.append(SimulCalAmount[-1])
 
- 				if SimulCalAmount[-1] > 100:
 
- 					SimulCalAmount[-1] = 100
 
- 					CustomizedRefStatus1[i] = 0
 
- 					CustomizedRefStatus2[i] = 0
 
- 				elif SimulCalAmount[-1] < 0:
 
- 					SimulCalAmount[-1] = 0
 
- 					CustomizedRefStatus1[i] = 0
 
- 					CustomizedRefStatus2[i] = 0
 
- 							
 
- 			
 
- 			#### 시뮬레이션 결과 데이터 DB 삽입 	
 
- 			## Thermal energy amount
 
- 			for i in range(len(CustomizedStatus)):
 
- 				TmpDate = InitDate + datetime.timedelta(minutes=i*15)
 
- 				cursor.execute("SELECT * FROM " + targetDBName + ".dbo.BemsIceThermalStorageSimulation where SiteId = 1 and FacilityTypeId = 3 and FacilityCode = 4478 and PropertyId = 2 and SimulationCase = 1 and TargetDateTime = '" + TmpDate.strftime('%Y-%m-%d %H:%M:00') + "' ")
 
- 				
 
- 				# 데이타 하나씩 Fetch하여 출력
 
- 				row = cursor.fetchone()
 
- 				if row:
 
- 					try:
 
- 						cursor.execute("UPDATE " + targetDBName + ".dbo.BemsIceThermalStorageSimulation set CreatedDateTime = '"+ datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') +"', SimulationValue = " + str(SimulCalAmount[i]) + " where SiteId = 1 and FacilityTypeId = 3 and FacilityCode = 4478 and PropertyId = 2 and SimulationCase = 1 and TargetDateTime = '"+ TmpDate.strftime('%Y-%m-%d %H:%M:00')+"'")
 
- 						
 
- 						if i == len(CustomizedStatus)-1:
 
- 							print("* Thermal energy amount was updated!! (Simul)")
 
- 					except:
 
- 						print("[ERROR] There is an update error!! (Simulated thermal energy amount)")
 
- 					
 
- 				else:
 
- 					try:
 
- 						cursor.execute("INSERT INTO " + targetDBName + ".dbo.BemsIceThermalStorageSimulation (SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,TargetDateTime,SimulationValue,SimulationCase) VALUES(1,3,4478,2,'" + datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') + "','" + TmpDate.strftime('%Y-%m-%d %H:%M:00') + "', "+ str(SimulCalAmount[i]) + ", 1)" )
 
- 						
 
- 						if i == len(CustomizedStatus)-1:
 
- 							print("* Thermal energy amount was inserted!! (Simul)")
 
- 					except:
 
- 						print("[ERROR] There is an insert error!! (Simulated thermal energy amount)")
 
- 					
 
- 				
 
- 			
 
- 			## REF1 power consume			
 
- 			for i in range(len(CustomizedStatus)):
 
- 				TmpDate = InitDate + datetime.timedelta(minutes=i*15)
 
- 				cursor.execute("SELECT * FROM " + targetDBName + ".dbo.BemsIceThermalStorageSimulation where SiteId = 1 and FacilityTypeId = 2 and FacilityCode = 4479 and PropertyId = 11 and SimulationCase = 1 and TargetDateTime = '" + TmpDate.strftime('%Y-%m-%d %H:%M:00') + "' ")	
 
- 				
 
- 				if CustomizedRefStatus1[i]==1:
 
- 					TmpComsume = mean_RefConsume1
 
- 				else:
 
- 					TmpComsume = 0	
 
- 				# 데이타 하나씩 Fetch하여 출력
 
- 				row = cursor.fetchone()
 
- 				if row:
 
- 					try:
 
- 						cursor.execute("UPDATE " + targetDBName + ".dbo.BemsIceThermalStorageSimulation set CreatedDateTime = '"+ datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') +"', SimulationValue = " + str(TmpComsume) + " where SiteId = 1 and FacilityTypeId = 2 and FacilityCode = 4479 and PropertyId = 11 and SimulationCase = 1 and TargetDateTime = '"+ TmpDate.strftime('%Y-%m-%d %H:%M:00')+"'")
 
- 						
 
- 						if i == len(CustomizedStatus)-1:
 
- 							print("* The REF1 power comsumption was updated!! (Simul)")
 
- 					except:
 
- 						print("[ERROR] There is an update error!! (Simulated refrigerator1 power)")
 
- 					
 
- 				else:
 
- 					try:
 
- 						cursor.execute("INSERT INTO " + targetDBName + ".dbo.BemsIceThermalStorageSimulation (SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,TargetDateTime,SimulationValue,SimulationCase) VALUES(1,2,4479,11,'" + datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') + "','" + TmpDate.strftime('%Y-%m-%d %H:%M:00') + "', "+ str(TmpComsume) + ", 1)" )
 
- 						
 
- 						if i == len(CustomizedStatus)-1:
 
- 							print("* The REF1 power comsumption was inserted!! (Simul)")
 
- 					except:
 
- 						print("[ERROR] There is an insert error!! (Simulated refrigerator1 power)")		
 
- 			
 
- 			## REF2 power consume	
 
- 					
 
- 			for i in range(len(CustomizedStatus)):
 
- 				TmpDate = InitDate + datetime.timedelta(minutes=i*15)
 
- 				cursor.execute("SELECT * FROM " + targetDBName + ".dbo.BemsIceThermalStorageSimulation where SiteId = 1 and FacilityTypeId = 2 and FacilityCode = 4480 and PropertyId = 11 and SimulationCase = 1 and TargetDateTime = '" + TmpDate.strftime('%Y-%m-%d %H:%M:00') + "' ")
 
- 				
 
- 				if CustomizedRefStatus2[i]==1:
 
- 					TmpComsume = mean_RefConsume2
 
- 				else:
 
- 					TmpComsume = 0	
 
- 				# 데이타 하나씩 Fetch하여 출력
 
- 				row = cursor.fetchone()
 
- 				if row:
 
- 					try:
 
- 						cursor.execute("UPDATE " + targetDBName + ".dbo.BemsIceThermalStorageSimulation set CreatedDateTime = '"+ datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') +"', SimulationValue = " + str(TmpComsume) + " where SiteId = 1 and FacilityTypeId = 2 and FacilityCode = 4480 and PropertyId = 11 and SimulationCase = 1 and TargetDateTime = '"+ TmpDate.strftime('%Y-%m-%d %H:%M:00')+"'")
 
- 						
 
- 						if i == len(CustomizedStatus)-1:
 
- 							print("* The REF2 power comsumption was updated!! (Simul)")
 
- 					except:
 
- 						print("[ERROR] There is an update error!! (Simulated refrigerator2 power)")
 
- 					
 
- 				else:
 
- 					try:
 
- 						cursor.execute("INSERT INTO " + targetDBName + ".dbo.BemsIceThermalStorageSimulation (SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,TargetDateTime,SimulationValue,SimulationCase) VALUES(1,2,4480,11,'" + datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') + "','" + TmpDate.strftime('%Y-%m-%d %H:%M:00') + "', "+ str(TmpComsume) + ", 1)" )
 
- 						
 
- 						if i == len(CustomizedStatus)-1:
 
- 							print("* The REF2 power comsumption was inserted!! (Simul)")					
 
- 					except:
 
- 						print("[ERROR] There is an insert error!! (Simulated refrigerator2 power)")
 
- 			
 
- 			conn.close()
 
- 			print('************ (Finish) Simulator! ************')
 
- 			print('*********************************************')
 
- 			#######################################################################################
 
- 			
 
- 		
 
 
  |