#!/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): 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 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) DataAct_len = (Day_Period-1)*OrgDataRes + now.hour*4 + int(now.minute/15)+1 ### 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 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 ### 21시 후에는 예보데이터는 내일 데이터를 기반으로 하기에 설비 데이터보다 하루 뒤 시점 데이터를 가져온다. ### 21시 전에는 오늘 데이터를 가져오면 된다. (예보 데이터가 21시를 기점으로 업데이트되기 때문) def detect_unknown_duplicated_zero_data_for_WeatherForecast3h(raw_Data, startday, lastday, Day_Period): now = datetime.datetime.now().now() if now.hour >= 21: Day_Period += 1 lastday += datetime.timedelta(days=1) StandardTimeStamp_DayUnit = [] # Create intact time stamp for idx_day in range(Day_Period): 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: 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: 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] now=datetime.datetime.now().now() lastday = datetime.date(now.year, now.month, now.day) startday = datetime.date(2020,4,9) 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) BrineMixedTemperature_Date, BrineMixedTemperature_w_nan, DataCountMat_BrineMixedTemperature = detect_unknown_duplicated_zero_data_for_faciilty(rawBrineMixedTemperature, startday, lastday, DayPeriod, DataRes_96) BrineInletTemperature_Date, BrineInletTemperature_w_nan, DataCountMat_BrineInletTemperature = detect_unknown_duplicated_zero_data_for_faciilty(rawBrineInletTemperature, startday, lastday, DayPeriod, DataRes_96) BrineOutletTemperature_Date, BrineOutletTemperature_w_nan, DataCountMat_BrineOutletTemperature = detect_unknown_duplicated_zero_data_for_faciilty(rawBrineOutletTemperature, startday, lastday, DayPeriod, DataRes_96) BrineFlowAmount_Date, BrineFlowAmount_w_nan, DataCountMat_BrineFlowAmount = detect_unknown_duplicated_zero_data_for_faciilty(rawBrineFlowAmount, startday, lastday, DayPeriod, DataRes_96) ChStatusIcing_Date, ChStatusIcing_w_nan, DataCountMat_ChStatusIcing = detect_unknown_duplicated_zero_data_for_faciilty(rawChillerStatusIcing, startday, lastday, DayPeriod, DataRes_96) ChStatusDeicing_Date, ChStatusDeicing_w_nan, DataCountMat_ChStatusDeicing = detect_unknown_duplicated_zero_data_for_faciilty(rawChillerStatusDeicing, startday, lastday, DayPeriod, DataRes_96) ChStatusParallel_Date, ChStatusParallel_w_nan, DataCountMat_ChStatusParallel = detect_unknown_duplicated_zero_data_for_faciilty(rawChillerStatusParallel, startday, lastday, DayPeriod, DataRes_96) ChStatusRefOnly_Date, ChStatusRefOnly_w_nan, DataCountMat_ChStatusRefOnly = detect_unknown_duplicated_zero_data_for_faciilty(rawChillerStatusRefOnly, startday, lastday, DayPeriod, DataRes_96) RefPowerConsume1_Date, RefPowerConsume1_w_nan, DataCountMat_RefPowerConsume1 = detect_unknown_duplicated_zero_data_for_faciilty(rawRefPowerConsume1, startday, lastday, DayPeriod, DataRes_96) RefPowerConsume2_Date, RefPowerConsume2_w_nan, DataCountMat_RefPowerConsume2 = detect_unknown_duplicated_zero_data_for_faciilty(rawRefPowerConsume2, startday, lastday, DayPeriod, DataRes_96) RefStatus1_Date, RefStatus1_w_nan, DataCountMat_RefStatus1 = detect_unknown_duplicated_zero_data_for_faciilty(rawRefStatus1, startday, lastday, DayPeriod, DataRes_96) RefStatus2_Date, RefStatus2_w_nan, DataCountMat_RefStatus2 = detect_unknown_duplicated_zero_data_for_faciilty(rawRefStatus2, startday, lastday, DayPeriod, DataRes_96) ############################################################################################## ## 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) 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) 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) 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) ################# 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 = ChStatusIcing_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_1 = [] GradientCalAmount_mode_ChillerOnly_2 = [] 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] + trainRefStatus2[i] == 1: GradientCalAmount_mode_Parallel.append(trainCalAmount[i]-trainCalAmount[i-1]) elif trainStatus[i] == ChillerOnly and trainRefStatus1[i] + trainRefStatus2[i] == 1: GradientCalAmount_mode_ChillerOnly_1.append(trainCalAmount[i]-trainCalAmount[i-1]) elif trainStatus[i] == ChillerOnly and trainRefStatus1[i] + trainRefStatus2[i] == 2: GradientCalAmount_mode_ChillerOnly_2.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_1 = [] if len(GradientCalAmount_mode_ChillerOnly_1) != 0: max3sigma_mode_ChillerOnly_1 = np.mean(GradientCalAmount_mode_ChillerOnly_1)+np.std(GradientCalAmount_mode_ChillerOnly_1)*3 min3sigma_mode_ChillerOnly_1 = np.mean(GradientCalAmount_mode_ChillerOnly_1)-np.std(GradientCalAmount_mode_ChillerOnly_1)*3 GradientCalAmount_w3sigma_mode_ChillerOnly_2 = [] if len(GradientCalAmount_mode_ChillerOnly_2) != 0: max3sigma_mode_ChillerOnly_2 = np.mean(GradientCalAmount_mode_ChillerOnly_2)+np.std(GradientCalAmount_mode_ChillerOnly_2)*3 min3sigma_mode_ChillerOnly_2 = np.mean(GradientCalAmount_mode_ChillerOnly_2)-np.std(GradientCalAmount_mode_ChillerOnly_2)*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_1)): if GradientCalAmount_mode_ChillerOnly_1[i] <= max3sigma_mode_ChillerOnly_1 and GradientCalAmount_mode_ChillerOnly_1[i] >= min3sigma_mode_ChillerOnly_1: GradientCalAmount_w3sigma_mode_ChillerOnly_1.append(GradientCalAmount_mode_ChillerOnly_1[i]) for i in range(len(GradientCalAmount_mode_ChillerOnly_2)): if GradientCalAmount_mode_ChillerOnly_2[i] <= max3sigma_mode_ChillerOnly_2 and GradientCalAmount_mode_ChillerOnly_2[i] >= min3sigma_mode_ChillerOnly_2: GradientCalAmount_w3sigma_mode_ChillerOnly_2.append(GradientCalAmount_mode_ChillerOnly_2[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 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_2)) elif inputX_REF1[i] + inputX_REF2[i]==1: RecommendedCalAmount.append(RecommendedCalAmount[-1]+np.mean(GradientCalAmount_w3sigma_mode_ChillerOnly_1)) 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(1) # 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() 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() InitDate = datetime.datetime(now.year, now.month, now.day, now.hour, int(int(now.minute/15)*15), 0) FinalDate = datetime.datetime(now.year, now.month, now.day, 21, 0, 0) TmpTime = InitDate TimeLen = 0 while TmpTime < FinalDate: TmpTime += datetime.timedelta(minutes=15) TimeLen += 1 while True: ## 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) ## 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) ## 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) 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]) if TimeLen == len(CustomizedStatus) and TimeLen == len(CustomizedRefStatus1) and TimeLen == len(CustomizedRefStatus2): break time.sleep(1) 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_1) == 0: print('[Warning] There is no enough data (Chiller Only_1)') SimulCalAmount.append(SimulCalAmount[-1]) elif len(GradientCalAmount_w3sigma_mode_ChillerOnly_2) == 0: print('[Warning] There is no enough data (Chiller Only_2)') 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 삽입 ## 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)") ## 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)") conn.close() print('************ (Finish) Simulator! ************') print('*********************************************') #######################################################################################