RealTimeSimulator_HeatStorageSystem.py 78 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592
  1. #!/usr/bin/env python
  2. # coding: utf-8
  3. import time
  4. import datetime
  5. import numpy as np
  6. import math
  7. from korean_lunar_calendar import KoreanLunarCalendar
  8. import configparser
  9. import pymssql
  10. from sklearn import ensemble
  11. from sklearn.model_selection import train_test_split
  12. ## Measure
  13. def MAPE(y_observed, y_pred):
  14. return np.mean(np.abs((y_observed - y_pred) / y_observed)) * 100
  15. def MAE(y_observed, y_pred):
  16. return np.mean(np.abs(y_observed - y_pred))
  17. def MBE(y_observed, y_pred):
  18. return (np.sum((y_observed - y_pred))/(len(y_observed)*np.mean(y_observed)))*100
  19. def CVRMSE(y_observed, y_pred):
  20. return (np.sqrt(np.mean((y_observed - y_pred)*(y_observed - y_pred)))/np.mean(y_observed))*100
  21. def Check_AlivedTimeStamp(RawData, ComparedData, idx_raw, idx_comp, unit):
  22. if unit == 'daily':
  23. 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):
  24. isAlived = True
  25. else:
  26. isAlived = False
  27. elif unit == 'quarterly':
  28. 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):
  29. isAlived = True
  30. else:
  31. isAlived = False
  32. return isAlived
  33. def detect_unknown_duplicated_zero_data_for_faciilty(raw_Data, startday, lastday, Day_Period, OrgDataRes, isRecent):
  34. CumTime = datetime.datetime(int(startday.strftime('%Y')), int(startday.strftime('%m')), int(startday.strftime('%d')), 0, 0, 0)
  35. StandardTimeStamp_DayUnit = [CumTime]
  36. StandardTimeStamp_QuarterUnit = [CumTime]
  37. # Create intact time stamp
  38. for idx_day in range(Day_Period):
  39. StandardTimeStamp_DayUnit.append(startday + datetime.timedelta(days=idx_day))
  40. if isRecent and idx_day == Day_Period-1:
  41. tmp_len = now.hour*4 + int(now.minute/15)
  42. for idx_time in range(tmp_len):
  43. CumTime += datetime.timedelta(minutes = 15)
  44. StandardTimeStamp_QuarterUnit.append(CumTime)
  45. else:
  46. for idx_time in range(OrgDataRes):
  47. CumTime += datetime.timedelta(minutes = 15)
  48. StandardTimeStamp_QuarterUnit.append(CumTime)
  49. ### Extract data within day period
  50. Raw_Date=[] # raw data (date)
  51. Raw_Value=[] # raw data (value)
  52. for i in range(len(raw_Data)):
  53. if datetime.date(raw_Data[i][4].year,raw_Data[i][4].month,raw_Data[i][4].day) >= startday:
  54. if datetime.date(raw_Data[i][4].year,raw_Data[i][4].month,raw_Data[i][4].day) <= lastday:
  55. Raw_Date.append(raw_Data[i][4])
  56. Raw_Value.append(raw_Data[i][5])
  57. if datetime.date(raw_Data[i][4].year,raw_Data[i][4].month,raw_Data[i][4].day) > lastday:
  58. break
  59. Data_len = len(Raw_Date)
  60. if isRecent:
  61. DataAct_len = (Day_Period-1)*OrgDataRes + now.hour*4 + int(now.minute/15)+1
  62. else:
  63. DataAct_len = Day_Period*OrgDataRes
  64. ### Unknown/duplicated data counts
  65. DataCount=[]
  66. for i in range(len(StandardTimeStamp_DayUnit)):
  67. cnt_unk=0 # Unknown data count
  68. for j in range(Data_len-1):
  69. if StandardTimeStamp_DayUnit[i] == datetime.date(Raw_Date[j].year,Raw_Date[j].month,Raw_Date[j].day):
  70. cnt_unk += 1
  71. if isRecent and i==len(StandardTimeStamp_DayUnit)-1:
  72. DataCount.append([StandardTimeStamp_DayUnit[i], now.hour*4 + int(now.minute/15) - cnt_unk])
  73. else:
  74. DataCount.append([StandardTimeStamp_DayUnit[i], OrgDataRes-cnt_unk])
  75. DataCountMat=np.matrix(DataCount)
  76. ######## 현재 DB 특성상 값이 중복되거나 시간테이블의 행 자체가 없는 경우가 있고, 이 데이터 1 step 앞뒤로 데이터가 비정상일 확률이 높으므로 비정상데이터 뿐만 아니라 앞뒤 1 step까지 NaN으로 처리함
  77. data_w_nan=[]
  78. idx=0
  79. idx2=0
  80. isBadData = False
  81. for i in range(DataAct_len):
  82. 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:
  83. if isBadData == True:
  84. data_w_nan.append(np.nan)
  85. isBadData=False
  86. elif Check_AlivedTimeStamp(raw_Data, StandardTimeStamp_QuarterUnit, idx, idx2, 'quarterly'):
  87. data_w_nan.append(raw_Data[idx][5])
  88. else:
  89. if i > 1:
  90. data_w_nan[-1]=np.nan
  91. data_w_nan.append(np.nan)
  92. #data_w_nan.append(np.nan)
  93. if raw_Data[idx+1][5] > 0 and Check_AlivedTimeStamp(raw_Data, StandardTimeStamp_QuarterUnit, idx+1, idx2+1, 'quarterly'):
  94. isBadData = True
  95. idx -= 1
  96. idx2 += 1
  97. idx += 1
  98. return StandardTimeStamp_QuarterUnit, data_w_nan, DataCountMat
  99. ### 21시 후에는 예보데이터는 내일 데이터를 기반으로 하기에 설비 데이터보다 하루 뒤 시점 데이터를 가져온다.
  100. ### 21시 전에는 오늘 데이터를 가져오면 된다. (예보 데이터가 21시를 기점으로 업데이트되기 때문)
  101. def detect_unknown_duplicated_zero_data_for_WeatherForecast3h(raw_Data, startday, lastday, Day_Period):
  102. now = datetime.datetime.now().now()
  103. if now.hour > 21:
  104. Day_Period += 1
  105. lastday += datetime.timedelta(days=1)
  106. StandardTimeStamp_DayUnit = []
  107. # Create intact time stamp
  108. for idx_day in range(Day_Period):
  109. StandardTimeStamp_DayUnit.append(startday + datetime.timedelta(days=idx_day))
  110. ### Extract data within day period
  111. Raw_Value_max = [] # raw data (value)
  112. Raw_Value_min = []
  113. Raw_Value_mean = []
  114. Raw_Date = [] # raw data (date)
  115. tmp_data = [raw_Data[0][5]]
  116. for i in range(len(raw_Data)):
  117. if i == len(raw_Data)-1:
  118. Raw_Date.append(raw_Data[i][4])
  119. Raw_Value_max.append(max(tmp_data))
  120. Raw_Value_min.append(min(tmp_data))
  121. Raw_Value_mean.append(np.mean(tmp_data))
  122. elif datetime.date(raw_Data[i][4].year,raw_Data[i][4].month,raw_Data[i][4].day) >= startday:
  123. if datetime.date(raw_Data[i][4].year,raw_Data[i][4].month,raw_Data[i][4].day) <= lastday:
  124. 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):
  125. Raw_Date.append(raw_Data[i][4])
  126. Raw_Value_max.append(max(tmp_data))
  127. Raw_Value_min.append(min(tmp_data))
  128. Raw_Value_mean.append(np.mean(tmp_data))
  129. tmp_data=[]
  130. tmp_data.append(raw_Data[i+1][5])
  131. if datetime.date(raw_Data[i][4].year,raw_Data[i][4].month,raw_Data[i][4].day) > lastday:
  132. break
  133. Data_len = len(Raw_Date)
  134. ### Unknown/duplicated data counts
  135. DataCount=[]
  136. for i in range(len(StandardTimeStamp_DayUnit)):
  137. cnt_unk=0 # Unknown data count
  138. for j in range(Data_len-1):
  139. if StandardTimeStamp_DayUnit[i] == datetime.date(Raw_Date[j].year,Raw_Date[j].month,Raw_Date[j].day):
  140. cnt_unk += 1
  141. DataCount.append([StandardTimeStamp_DayUnit[i], 1-cnt_unk])
  142. DataCountMat=np.matrix(DataCount)
  143. ######## 현재 DB 특성상 값이 중복되거나 시간테이블의 행 자체가 없는 경우가 있고, 이 데이터 1 step 앞뒤로 데이터가 비정상일 확률이 높으므로 비정상데이터 뿐만 아니라 앞뒤 1 step까지 NaN으로 처리함
  144. MaxData_w_nan=[]
  145. MinData_w_nan=[]
  146. MeanData_w_nan=[]
  147. for i in range(len(StandardTimeStamp_DayUnit)):
  148. for j in range(len(Raw_Date)):
  149. if Check_AlivedTimeStamp(Raw_Date, StandardTimeStamp_DayUnit, j, i, 'daily'):
  150. MaxData_w_nan.append(Raw_Value_max[j])
  151. MinData_w_nan.append(Raw_Value_min[j])
  152. MeanData_w_nan.append(Raw_Value_mean[j])
  153. break
  154. elif j == len(Raw_Date)-1:
  155. MaxData_w_nan.append(np.nan)
  156. MinData_w_nan.append(np.nan)
  157. MeanData_w_nan.append(np.nan)
  158. return StandardTimeStamp_DayUnit, MaxData_w_nan, MinData_w_nan, MeanData_w_nan, DataCountMat
  159. ### Define day-type
  160. def getDayName(year, month, day):
  161. return ['MON','TUE','WED','THU','FRI','SAT','SUN'][datetime.date(year, month, day).weekday()]
  162. def getDayType(DateinDay, Period, SpecialHoliday):
  163. DoW=[]; # Day of Week
  164. for i in range(Period):
  165. if DateinDay[i].year==2019 and DateinDay[i].month==5 and DateinDay[i].day==18:
  166. DoW.append([5, DateinDay[i]])
  167. elif getDayName(DateinDay[i].year,DateinDay[i].month,DateinDay[i].day) == 'MON':
  168. DoW.append([1, DateinDay[i]])
  169. elif getDayName(DateinDay[i].year,DateinDay[i].month,DateinDay[i].day) == 'TUE':
  170. DoW.append([2, DateinDay[i]])
  171. elif getDayName(DateinDay[i].year,DateinDay[i].month,DateinDay[i].day) == 'WED':
  172. DoW.append([3, DateinDay[i]])
  173. elif getDayName(DateinDay[i].year,DateinDay[i].month,DateinDay[i].day) == 'THU':
  174. DoW.append([4, DateinDay[i]])
  175. elif getDayName(DateinDay[i].year,DateinDay[i].month,DateinDay[i].day) == 'FRI':
  176. DoW.append([5, DateinDay[i]])
  177. elif getDayName(DateinDay[i].year,DateinDay[i].month,DateinDay[i].day) == 'SAT':
  178. DoW.append([6, DateinDay[i]])
  179. elif getDayName(DateinDay[i].year,DateinDay[i].month,DateinDay[i].day) == 'SUN':
  180. DoW.append([7, DateinDay[i]])
  181. for j in range(len(SpecialHoliday)):
  182. if SpecialHoliday[j] == datetime.date(DateinDay[i].year,DateinDay[i].month,DateinDay[i].day):
  183. DoW[-1][0] = 8
  184. break
  185. ### W-W:1, N-W:2, W-N:3, N-N:4 ###
  186. DayType=[]
  187. for i in range(Period):
  188. if i==0:
  189. if DoW[i][0] <= 5:
  190. DayType.append([1, DateinDay[i]])
  191. elif DoW[i][0] > 5:
  192. DayType.append([3, DateinDay[i]])
  193. else:
  194. if DoW[i-1][0] <= 5 and DoW[i][0] <= 5:
  195. DayType.append([1, DateinDay[i]])
  196. elif DoW[i-1][0] > 5 and DoW[i][0] <= 5:
  197. DayType.append([2, DateinDay[i]])
  198. elif DoW[i-1][0] <= 5 and DoW[i][0] > 5:
  199. DayType.append([3, DateinDay[i]])
  200. elif DoW[i-1][0] > 5 and DoW[i][0] > 5:
  201. DayType.append([4, DateinDay[i]])
  202. return DoW, DayType
  203. if __name__ == "__main__" :
  204. Init = True
  205. ## Check every 15min. in the infinite loop
  206. while True:
  207. now = datetime.datetime.now().now()
  208. ## distinguish real time update and specific day
  209. ## 자정에 생기는 인덱싱 문제로 0시에는 16분에 업데이트, 나머지는 15분에 한 번씩 업데이트
  210. if Init:
  211. prev_time_minute = now.minute - 1 ## 알고리즘 중복 수행 방지 (알고리즘 수행시 1분이 안걸리기에 한타임에 알고리즘 한번만 동작시키기 위함)
  212. if (now.hour != 0 and now.minute%15 == 1 and now.second > 0 and now.second < 5) and prev_time_minute != now.minute:
  213. ActiveAlgorithm = True
  214. prev_time_minute = now.minute
  215. else:
  216. ActiveAlgorithm = False
  217. if ActiveAlgorithm or Init:
  218. ## Loading .ini file
  219. myINI = configparser.ConfigParser()
  220. myINI.read("Config.ini", "utf-8" )
  221. # MSSQL Access
  222. 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)
  223. # Create Cursor from Connection
  224. cursor = conn.cursor()
  225. # Execute SQL (Electric consumption)
  226. cursor.execute('SELECT * FROM BemsConfigData where SiteId = 1')
  227. rowDB_info = cursor.fetchone()
  228. conn.close()
  229. loadDBIP = rowDB_info[1]
  230. loadDBUserID = rowDB_info[2]
  231. loadDBUserPW = rowDB_info[3]
  232. loadDBName = rowDB_info[4]
  233. targetDBIP = rowDB_info[5]
  234. targetDBUserID = rowDB_info[6]
  235. targetDBUserPW = rowDB_info[7]
  236. targetDBName = rowDB_info[8]
  237. now=datetime.datetime.now().now()
  238. lastday = datetime.date(now.year, now.month, now.day)
  239. isRecent = True
  240. startday = datetime.date(2020,4,9)
  241. if startday < datetime.date(2020,4,8):
  242. print('[ERROR] 데이터 최소 시작 시점은 2020.04.08 입니다')
  243. startday = datetime.date(2020,4,9)
  244. elif startday > lastday:
  245. print('[ERROR] 예측 타깃 시작시점이 데이터 시작 시점보다 작을 수 없습니다')
  246. ##############################################################################################
  247. ## 기온, 습도 예보 데이터 로드
  248. # MSSQL 접속
  249. conn = pymssql.connect(host = targetDBIP, user = targetDBUserID, password = targetDBUserPW, database = targetDBName, autocommit=True)
  250. # Connection 으로부터 Cursor 생성
  251. cursor = conn.cursor()
  252. # SQL문 실행 (기온 예보)
  253. cursor.execute('SELECT * FROM BemsMonitoringPointWeatherForecasted where SiteId = 1 and Category = '+"'"+'Temperature'+"'"+' order by ForecastedDateTime desc')
  254. row = cursor.fetchone()
  255. rawWFTemperature = [row]
  256. while row:
  257. row = cursor.fetchone()
  258. if row == None:
  259. break
  260. if datetime.date(row[4].year,row[4].month,row[4].day) < startday:
  261. break
  262. rawWFTemperature.append(row)
  263. rawWFTemperature.reverse()
  264. # SQL문 실행 (습도 예보)
  265. cursor.execute('SELECT * FROM BemsMonitoringPointWeatherForecasted where SiteId = 1 and Category = '+"'"+'Humidity'+"'"+' order by ForecastedDateTime desc')
  266. row = cursor.fetchone()
  267. rawWFHumidity = [row]
  268. while row:
  269. row = cursor.fetchone()
  270. if row == None:
  271. break
  272. if datetime.date(row[4].year,row[4].month,row[4].day) < startday:
  273. break
  274. rawWFHumidity.append(row)
  275. rawWFHumidity.reverse()
  276. ##############################################################################################
  277. startday = datetime.date(rawWFHumidity[0][4].year, rawWFHumidity[0][4].month, rawWFHumidity[0][4].day) ## 데이터 불러오는 DB가 선구축된다고 가정하여 예보데이터 기준으로 startday define
  278. DayPeriod = (lastday - startday).days + 1
  279. print('* StartDay :',startday,',', 'LastDay :', lastday,',','Current Time :', now, ',','Day period :', DayPeriod)
  280. # MSSQL 접속
  281. conn = pymssql.connect(host = loadDBIP, user = loadDBUserID, password = loadDBUserPW, database = loadDBName, autocommit=True)
  282. # Connection 으로부터 Cursor 생성
  283. cursor = conn.cursor()
  284. DataRes_96=96
  285. DataRes_24=24
  286. print('************ (Start) Load & pre-processing data !! ************')
  287. # SQL문 실행 (축열조 축열량)
  288. cursor.execute('SELECT * FROM BemsMonitoringPointHistory15min where SiteId=1 and FacilityTypeId = 3 and FacilityCode = 4478 and PropertyId = 2 order by CreatedDateTime desc')
  289. row = cursor.fetchone()
  290. rawChillerCalAmount=[row]
  291. while row:
  292. row = cursor.fetchone()
  293. if row == None:
  294. break
  295. if datetime.date(row[4].year,row[4].month,row[4].day) < startday:
  296. break
  297. rawChillerCalAmount.append(row)
  298. rawChillerCalAmount.reverse()
  299. # SQL문 실행 (축열조 제빙운전상태)
  300. cursor.execute('SELECT * FROM BemsMonitoringPointHistory15min where SiteId=1 and FacilityTypeId = 3 and FacilityCode = 4478 and PropertyId = 16 order by CreatedDateTime desc')
  301. row = cursor.fetchone()
  302. rawChillerStatusIcing=[row]
  303. while row:
  304. row = cursor.fetchone()
  305. if row == None:
  306. break
  307. if datetime.date(row[4].year,row[4].month,row[4].day) < startday:
  308. break
  309. rawChillerStatusIcing.append(row)
  310. rawChillerStatusIcing.reverse()
  311. # SQL문 실행 (축열조 축단운전상태)
  312. cursor.execute('SELECT * FROM BemsMonitoringPointHistory15min where SiteId=1 and FacilityTypeId = 3 and FacilityCode = 4478 and PropertyId = 17 order by CreatedDateTime desc')
  313. row = cursor.fetchone()
  314. rawChillerStatusDeicing=[row]
  315. while row:
  316. row = cursor.fetchone()
  317. if row == None:
  318. break
  319. if datetime.date(row[4].year,row[4].month,row[4].day) < startday:
  320. break
  321. rawChillerStatusDeicing.append(row)
  322. rawChillerStatusDeicing.reverse()
  323. # SQL문 실행 (축열조 병렬운전상태)
  324. cursor.execute('SELECT * FROM BemsMonitoringPointHistory15min where SiteId=1 and FacilityTypeId = 3 and FacilityCode = 4478 and PropertyId = 18 order by CreatedDateTime desc')
  325. row = cursor.fetchone()
  326. rawChillerStatusParallel=[row]
  327. while row:
  328. row = cursor.fetchone()
  329. if row == None:
  330. break
  331. if datetime.date(row[4].year,row[4].month,row[4].day) < startday:
  332. break
  333. rawChillerStatusParallel.append(row)
  334. rawChillerStatusParallel.reverse()
  335. # SQL문 실행 (축열조 냉단운전상태)
  336. cursor.execute('SELECT * FROM BemsMonitoringPointHistory15min where SiteId=1 and FacilityTypeId = 3 and FacilityCode = 4478 and PropertyId = 19 order by CreatedDateTime desc')
  337. row = cursor.fetchone()
  338. rawChillerStatusRefOnly=[row]
  339. while row:
  340. row = cursor.fetchone()
  341. if row == None:
  342. break
  343. if datetime.date(row[4].year,row[4].month,row[4].day) < startday:
  344. break
  345. rawChillerStatusRefOnly.append(row)
  346. rawChillerStatusRefOnly.reverse()
  347. ## 현재 2019, 2020년 냉동기 전력량 데이터가 없으므로 2년 전 데이터를 활용
  348. # SQL문 실행 (냉동기1 전력량)
  349. cursor.execute('SELECT * FROM BemsMonitoringPointHistory15min where SiteId=1 and FacilityTypeId = 2 and FacilityCode = 4479 and PropertyId = 11 order by CreatedDateTime desc')
  350. row = cursor.fetchone()
  351. rawRefPowerConsume1=[row]
  352. while row:
  353. row = cursor.fetchone()
  354. if row == None:
  355. break
  356. if datetime.date(row[4].year,row[4].month,row[4].day) < startday:
  357. break
  358. rawRefPowerConsume1.append(row)
  359. rawRefPowerConsume1.reverse()
  360. # SQL문 실행 (냉동기1 운전상태)
  361. cursor.execute('SELECT * FROM BemsMonitoringPointHistory15min where SiteId=1 and FacilityTypeId = 2 and FacilityCode = 4479 and PropertyId = 15 order by CreatedDateTime desc')
  362. row = cursor.fetchone()
  363. rawRefStatus1=[row]
  364. while row:
  365. row = cursor.fetchone()
  366. if row == None:
  367. break
  368. if datetime.date(row[4].year,row[4].month,row[4].day) < startday:
  369. break
  370. rawRefStatus1.append(row)
  371. rawRefStatus1.reverse()
  372. # SQL문 실행 (냉동기2 전력량)
  373. cursor.execute('SELECT * FROM BemsMonitoringPointHistory15min where SiteId=1 and FacilityTypeId = 2 and FacilityCode = 4480 and PropertyId = 11 order by CreatedDateTime desc')
  374. row = cursor.fetchone()
  375. rawRefPowerConsume2=[row]
  376. while row:
  377. row = cursor.fetchone()
  378. if row == None:
  379. break
  380. if datetime.date(row[4].year,row[4].month,row[4].day) < startday:
  381. break
  382. rawRefPowerConsume2.append(row)
  383. rawRefPowerConsume2.reverse()
  384. # SQL문 실행 (냉동기2 운전상태)
  385. cursor.execute('SELECT * FROM BemsMonitoringPointHistory15min where SiteId=1 and FacilityTypeId = 2 and FacilityCode = 4480 and PropertyId = 15 order by CreatedDateTime desc')
  386. row = cursor.fetchone()
  387. rawRefStatus2=[row]
  388. while row:
  389. row = cursor.fetchone()
  390. if row == None:
  391. break
  392. if datetime.date(row[4].year,row[4].month,row[4].day) < startday:
  393. break
  394. rawRefStatus2.append(row)
  395. rawRefStatus2.reverse()
  396. # SQL문 실행 (브라인 입구온도)
  397. cursor.execute('SELECT * FROM BemsMonitoringPointHistory15min where SiteId=1 and FacilityTypeId = 3 and FacilityCode = 4478 and PropertyId = 4 order by CreatedDateTime desc')
  398. row = cursor.fetchone()
  399. rawBrineInletTemperature=[row]
  400. while row:
  401. row = cursor.fetchone()
  402. if row == None:
  403. break
  404. if datetime.date(row[4].year,row[4].month,row[4].day) < startday:
  405. break
  406. rawBrineInletTemperature.append(row)
  407. rawBrineInletTemperature.reverse()
  408. # SQL문 실행 (브라인 출구온도)
  409. cursor.execute('SELECT * FROM BemsMonitoringPointHistory15min where SiteId=1 and FacilityTypeId = 3 and FacilityCode = 4478 and PropertyId = 3 order by CreatedDateTime desc')
  410. row = cursor.fetchone()
  411. rawBrineOutletTemperature=[row]
  412. while row:
  413. row = cursor.fetchone()
  414. if row == None:
  415. break
  416. if datetime.date(row[4].year,row[4].month,row[4].day) < startday:
  417. break
  418. rawBrineOutletTemperature.append(row)
  419. rawBrineOutletTemperature.reverse()
  420. # SQL문 실행 (브라인 혼합온도)
  421. cursor.execute('SELECT * FROM BemsMonitoringPointHistory15min where SiteId=1 and FacilityTypeId = 3 and FacilityCode = 4478 and PropertyId = 22 order by CreatedDateTime desc')
  422. row = cursor.fetchone()
  423. rawBrineMixedTemperature=[row]
  424. while row:
  425. row = cursor.fetchone()
  426. if row == None:
  427. break
  428. if datetime.date(row[4].year,row[4].month,row[4].day) < startday:
  429. break
  430. rawBrineMixedTemperature.append(row)
  431. rawBrineMixedTemperature.reverse()
  432. # SQL문 실행 (브라인 통과유량)
  433. cursor.execute('SELECT * FROM BemsMonitoringPointHistory15min where SiteId=1 and FacilityTypeId = 3 and FacilityCode = 4478 and PropertyId = 5 order by CreatedDateTime desc')
  434. row = cursor.fetchone()
  435. rawBrineFlowAmount=[row]
  436. while row:
  437. row = cursor.fetchone()
  438. if row == None:
  439. break
  440. if datetime.date(row[4].year,row[4].month,row[4].day) < startday:
  441. break
  442. rawBrineFlowAmount.append(row)
  443. rawBrineFlowAmount.reverse()
  444. # SQL문 실행 (정기휴일)
  445. cursor.execute('SELECT * FROM CmHoliday where SiteId = 1 and IsUse = 1')
  446. # 데이타 하나씩 Fetch하여 출력
  447. row = cursor.fetchone()
  448. regularHolidayData = [row]
  449. while row:
  450. row = cursor.fetchone()
  451. if row == None:
  452. break
  453. regularHolidayData.append(row)
  454. regularHolidayData = regularHolidayData[0:-1]
  455. # SQL문 실행 (비정기휴일)
  456. cursor.execute('SELECT * FROM CmHolidayCustom where SiteId = 1 and IsUse = 1')
  457. # 데이타 하나씩 Fetch하여 출력
  458. row = cursor.fetchone()
  459. suddenHolidayData = [row]
  460. while row:
  461. row = cursor.fetchone()
  462. if row == None:
  463. break
  464. suddenHolidayData.append(row)
  465. suddenHolidayData = suddenHolidayData[0:-1]
  466. ##############################################################################################
  467. ## 현재 2019, 2020년 냉동기 전력량 데이터가 없으므로 2년 전 데이터를 활용
  468. # SQL문 실행 (냉동기1 전력량), 2018
  469. cursor.execute('SELECT * FROM BemsMonitoringPointHistory15min where SiteId=1 and FacilityTypeId = 2 and FacilityCode = 4479 and PropertyId = 11 order by CreatedDateTime desc')
  470. row = cursor.fetchone()
  471. rawRefPowerConsume1_2018=[row]
  472. while row:
  473. row = cursor.fetchone()
  474. if row == None:
  475. break
  476. if datetime.date(row[4].year,row[4].month,row[4].day) < datetime.date(2018,1,1):
  477. break
  478. rawRefPowerConsume1_2018.append(row)
  479. rawRefPowerConsume1_2018.reverse()
  480. # SQL문 실행 (냉동기1 운전상태)
  481. cursor.execute('SELECT * FROM BemsMonitoringPointHistory15min where SiteId=1 and FacilityTypeId = 2 and FacilityCode = 4479 and PropertyId = 15 order by CreatedDateTime desc')
  482. row = cursor.fetchone()
  483. rawRefStatus1_2018=[row]
  484. while row:
  485. row = cursor.fetchone()
  486. if row == None:
  487. break
  488. if datetime.date(row[4].year,row[4].month,row[4].day) < datetime.date(2018,1,1):
  489. break
  490. rawRefStatus1_2018.append(row)
  491. rawRefStatus1_2018.reverse()
  492. # SQL문 실행 (냉동기2 전력량)
  493. cursor.execute('SELECT * FROM BemsMonitoringPointHistory15min where SiteId=1 and FacilityTypeId = 2 and FacilityCode = 4480 and PropertyId = 11 order by CreatedDateTime desc')
  494. row = cursor.fetchone()
  495. rawRefPowerConsume2_2018=[row]
  496. while row:
  497. row = cursor.fetchone()
  498. if row == None:
  499. break
  500. if datetime.date(row[4].year,row[4].month,row[4].day) < datetime.date(2018,1,1):
  501. break
  502. rawRefPowerConsume2_2018.append(row)
  503. rawRefPowerConsume2_2018.reverse()
  504. # SQL문 실행 (냉동기2 운전상태)
  505. cursor.execute('SELECT * FROM BemsMonitoringPointHistory15min where SiteId=1 and FacilityTypeId = 2 and FacilityCode = 4480 and PropertyId = 15 order by CreatedDateTime desc')
  506. row = cursor.fetchone()
  507. rawRefStatus2_2018=[row]
  508. while row:
  509. row = cursor.fetchone()
  510. if row == None:
  511. break
  512. if datetime.date(row[4].year,row[4].month,row[4].day) < datetime.date(2018,1,1):
  513. break
  514. rawRefStatus2_2018.append(row)
  515. rawRefStatus2_2018.reverse()
  516. ##############################################################################################
  517. # 연결 끊기
  518. conn.close()
  519. ## 휴일 데이터 DB에서 호출
  520. # 공휴일의 음력 계산
  521. calendar_convert = KoreanLunarCalendar()
  522. SpecialHoliday = []
  523. for i in range(lastday.year-startday.year+1):
  524. for j in range(len(regularHolidayData)):
  525. if regularHolidayData[j][3] == 1:
  526. if regularHolidayData[j][1] == 12 and regularHolidayData[j][2] == 30: ## 설 하루 전 연휴 계산을 위함
  527. calendar_convert.setLunarDate(startday.year+i-1, regularHolidayData[j][1], regularHolidayData[j][2], False)
  528. 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])))
  529. else:
  530. calendar_convert.setLunarDate(startday.year+i, regularHolidayData[j][1], regularHolidayData[j][2], False)
  531. 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])))
  532. else:
  533. SpecialHoliday.append(datetime.date(startday.year+i,regularHolidayData[j][1],regularHolidayData[j][2]))
  534. for i in range(len(suddenHolidayData)):
  535. if suddenHolidayData[i][1].year >= startday.year:
  536. SpecialHoliday.append(datetime.date(suddenHolidayData[i][1].year, suddenHolidayData[i][1].month, suddenHolidayData[i][1].day))
  537. SpecialHoliday=list(set(SpecialHoliday))
  538. ##############################################################################################
  539. ChillerCalAmount_Date, ChillerCalAmount_w_nan, DataCountMat_ChillerCalAmount = detect_unknown_duplicated_zero_data_for_faciilty(rawChillerCalAmount, startday, lastday, DayPeriod, DataRes_96, isRecent)
  540. BrineMixedTemperature_Date, BrineMixedTemperature_w_nan, DataCountMat_BrineMixedTemperature = detect_unknown_duplicated_zero_data_for_faciilty(rawBrineMixedTemperature, startday, lastday, DayPeriod, DataRes_96, isRecent)
  541. BrineInletTemperature_Date, BrineInletTemperature_w_nan, DataCountMat_BrineInletTemperature = detect_unknown_duplicated_zero_data_for_faciilty(rawBrineInletTemperature, startday, lastday, DayPeriod, DataRes_96, isRecent)
  542. BrineOutletTemperature_Date, BrineOutletTemperature_w_nan, DataCountMat_BrineOutletTemperature = detect_unknown_duplicated_zero_data_for_faciilty(rawBrineOutletTemperature, startday, lastday, DayPeriod, DataRes_96, isRecent)
  543. BrineFlowAmount_Date, BrineFlowAmount_w_nan, DataCountMat_BrineFlowAmount = detect_unknown_duplicated_zero_data_for_faciilty(rawBrineFlowAmount, startday, lastday, DayPeriod, DataRes_96, isRecent)
  544. ChStatusIcing_Date, ChStatusIcing_w_nan, DataCountMat_ChStatusIcing = detect_unknown_duplicated_zero_data_for_faciilty(rawChillerStatusIcing, startday, lastday, DayPeriod, DataRes_96, isRecent)
  545. ChStatusDeicing_Date, ChStatusDeicing_w_nan, DataCountMat_ChStatusDeicing = detect_unknown_duplicated_zero_data_for_faciilty(rawChillerStatusDeicing, startday, lastday, DayPeriod, DataRes_96, isRecent)
  546. ChStatusParallel_Date, ChStatusParallel_w_nan, DataCountMat_ChStatusParallel = detect_unknown_duplicated_zero_data_for_faciilty(rawChillerStatusParallel, startday, lastday, DayPeriod, DataRes_96, isRecent)
  547. ChStatusRefOnly_Date, ChStatusRefOnly_w_nan, DataCountMat_ChStatusRefOnly = detect_unknown_duplicated_zero_data_for_faciilty(rawChillerStatusRefOnly, startday, lastday, DayPeriod, DataRes_96, isRecent)
  548. RefPowerConsume1_Date, RefPowerConsume1_w_nan, DataCountMat_RefPowerConsume1 = detect_unknown_duplicated_zero_data_for_faciilty(rawRefPowerConsume1, startday, lastday, DayPeriod, DataRes_96, isRecent)
  549. RefPowerConsume2_Date, RefPowerConsume2_w_nan, DataCountMat_RefPowerConsume2 = detect_unknown_duplicated_zero_data_for_faciilty(rawRefPowerConsume2, startday, lastday, DayPeriod, DataRes_96, isRecent)
  550. RefStatus1_Date, RefStatus1_w_nan, DataCountMat_RefStatus1 = detect_unknown_duplicated_zero_data_for_faciilty(rawRefStatus1, startday, lastday, DayPeriod, DataRes_96, isRecent)
  551. RefStatus2_Date, RefStatus2_w_nan, DataCountMat_RefStatus2 = detect_unknown_duplicated_zero_data_for_faciilty(rawRefStatus2, startday, lastday, DayPeriod, DataRes_96, isRecent)
  552. ##############################################################################################
  553. ## 2019, 2020년 냉동기 전력량이 없어서 2018년 데이터로 대체
  554. DayPeriod_2018 = (datetime.date(2018,12,31) - datetime.date(2018,1,1)).days + 1
  555. 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)
  556. 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)
  557. 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)
  558. 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)
  559. ################# Using the power Consumption of Refrigerator in 2018 instead of 2020 #################
  560. #### 전력 소비량 계산
  561. _st=90*96
  562. _end=195*96
  563. period_2018=(_end-_st)/96
  564. RefStatus1_2018_w_nan_tmp=RefStatus1_2018_w_nan[_st:_end]
  565. RefPowerConsume1_2018_w_nan_tmp=RefPowerConsume1_2018_w_nan[_st:_end]
  566. RefStatus2_2018_w_nan_tmp=RefStatus2_2018_w_nan[_st:_end]
  567. RefPowerConsume2_2018_w_nan_tmp=RefPowerConsume2_2018_w_nan[_st:_end]
  568. ### Estimation based on Statistical method
  569. X1 = []
  570. X2 = []
  571. Y1 = []
  572. Y2 = []
  573. TermNum = 96
  574. for i in range(TermNum, len(RefStatus1_2018_w_nan_tmp),TermNum):
  575. X1.append(RefStatus1_2018_w_nan_tmp[i-TermNum:i])
  576. X2.append(RefStatus2_2018_w_nan_tmp[i-TermNum:i])
  577. Y1.append(RefPowerConsume1_2018_w_nan_tmp[i-TermNum:i])
  578. Y2.append(RefPowerConsume2_2018_w_nan_tmp[i-TermNum:i])
  579. xTrain1, xTest1, yTrain1, yTest1 = train_test_split(X1, Y1, test_size=0.1, shuffle =False)
  580. xTrain2, xTest2, yTrain2, yTest2 = train_test_split(X2, Y2, test_size=0.1, shuffle =False)
  581. Y_tmp1=[]
  582. Y_tmp2=[]
  583. for i in range(len(xTrain1)):
  584. for j in range(TermNum):
  585. if xTrain1[i][j] == 1:
  586. Y_tmp1.append(yTrain1[i][j])
  587. if xTrain2[i][j] == 1:
  588. Y_tmp2.append(yTrain2[i][j])
  589. mean_RefConsume1=np.mean(Y_tmp1) # 냉동기1 전력량 평균
  590. mean_RefConsume2=np.mean(Y_tmp2) # 냉동기2 전력량 평균
  591. ##############################################################################################
  592. ##############################################################################################
  593. WFTemperature_Date, WFTemperatureMax_w_nan, WFTemperatureMin_w_nan, WFTemperatureMean_w_nan, DataCountMat_WFTemperature = detect_unknown_duplicated_zero_data_for_WeatherForecast3h(rawWFTemperature, startday, lastday, DayPeriod)
  594. WFHumidity_Date, WFHumidityMax_w_nan, WFHumidityMin_w_nan, WFHumidityMean_w_nan, DataCountMat_WFHumidity = detect_unknown_duplicated_zero_data_for_WeatherForecast3h(rawWFHumidity, startday, lastday, DayPeriod)
  595. RawDate = ChStatusIcing_Date
  596. ## 축열조 상태 변수 - 제빙운전:10, 축단운전:20, 병렬운전:30, 냉단운전:40, OFF:0
  597. Icing=10
  598. StorageOnly=20
  599. Parallel=30
  600. ChillerOnly=40
  601. Off=0
  602. ChillerStatus=[]
  603. for i in range(len(ChStatusIcing_Date)):
  604. if ChStatusIcing_w_nan[i]==1:
  605. ChillerStatus.append(Icing)
  606. elif ChStatusDeicing_w_nan[i]==1:
  607. ChillerStatus.append(StorageOnly)
  608. elif ChStatusParallel_w_nan[i]==1:
  609. ChillerStatus.append(Parallel)
  610. elif ChStatusRefOnly_w_nan[i]==1:
  611. ChillerStatus.append(ChillerOnly)
  612. elif ChStatusIcing_w_nan[i]==0 or ChStatusDeicing_w_nan[i]==0 or ChStatusParallel_w_nan[i]==0 or ChStatusRefOnly_w_nan[i]==0:
  613. ChillerStatus.append(Off)
  614. else:
  615. ChillerStatus.append(np.nan)
  616. ## 축/방열량에 대해서 두가지 변수를 생성한다.
  617. ## 첫번쨰는 사용자에게 상대적 열량을 보여주기 위해 0 < Q < max(Q) 사이의 값으로 구성된 열량
  618. ## 두번쨰는 실질적 계산을 위해서 NaN이 포함된 날은 제외하고 학습하므로 NaN 구간의 축/방열량은 0으로 가정하고 산출
  619. ## 축적 열량의 최대치 (정격용량) = 3060 USRT (=10,924.2 kW)일 때 100%
  620. max_q_accum_kWh = 3060*3.57
  621. q_accum_kWh=[0]
  622. nan_cnt=0
  623. nan_point=[]
  624. for i in range(len(ChillerStatus)):
  625. if math.isnan(ChillerStatus[i]): # Nan의 경우 축열량을 0이라고 가정하고 진행
  626. q_accum_kWh.append(q_accum_kWh[-1])
  627. nan_cnt += 1
  628. nan_point.append(i)
  629. else:
  630. if ChillerStatus[i] == Icing and BrineInletTemperature_w_nan[i] < BrineMixedTemperature_w_nan[i]:
  631. 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]))
  632. elif ChillerStatus[i] == StorageOnly and BrineInletTemperature_w_nan[i] > BrineMixedTemperature_w_nan[i]:
  633. 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]))
  634. elif ChillerStatus[i] == Parallel and BrineInletTemperature_w_nan[i] > BrineMixedTemperature_w_nan[i]:
  635. 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]))
  636. else: #ChillerStatus[i] == Off or ChillerStatus[i] == ChillerOnly:
  637. q_accum_kWh.append(q_accum_kWh[-1])
  638. if q_accum_kWh[-1] < 0:
  639. q_accum_kWh[-1] = 0
  640. elif q_accum_kWh[-1] > max_q_accum_kWh:
  641. q_accum_kWh[-1] = max_q_accum_kWh
  642. if nan_cnt > 48:
  643. print('[Warning] Too many nan points exist (48 points sequentially)')
  644. nan_cnt = 0
  645. q_accum_kWh = q_accum_kWh[1:len(q_accum_kWh)]
  646. q_accum_percent=[]
  647. for i in range(len(q_accum_kWh)):
  648. q_accum_percent.append((q_accum_kWh[i]/max_q_accum_kWh)*100)
  649. CalAmount_prev = q_accum_percent[:len(q_accum_percent)-96] ## DB에 비어있는 이전 축열량이 있다면 채워주기 위함
  650. #################### Calculate the Gradient on Each Operation Mode ########################
  651. cnt_nan=0
  652. CalAmount_wo_nan=[]
  653. ChillerStatus_wo_nan=[]
  654. RefStatus1_wo_nan=[]
  655. RefStatus2_wo_nan=[]
  656. RefStatus_wo_nan=[]
  657. ## 1: off,off, 2: on,off, 3: on,on
  658. for i in range(len(q_accum_percent)):
  659. 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]):
  660. CalAmount_wo_nan.append(q_accum_percent[i])
  661. ChillerStatus_wo_nan.append(ChillerStatus[i])
  662. RefStatus1_wo_nan.append(RefStatus1_w_nan[i])
  663. RefStatus2_wo_nan.append(RefStatus2_w_nan[i])
  664. RefStatus_wo_nan.append(RefStatus1_w_nan[i]+RefStatus2_w_nan[i])
  665. cnt_nan=0
  666. else:
  667. CalAmount_wo_nan.append(CalAmount_wo_nan[-1])
  668. ChillerStatus_wo_nan.append(0)
  669. RefStatus1_wo_nan.append(0)
  670. RefStatus2_wo_nan.append(0)
  671. RefStatus_wo_nan.append(0)
  672. cnt_nan+=1
  673. if cnt_nan>12:
  674. cnt_nan=0
  675. # print('There are many unknown data!')
  676. # 학습용 데이터로 사용
  677. train_size = int(len(ChillerStatus_wo_nan))
  678. ## 나머지를 검증용 데이터로 사용
  679. ## test_size = len(ChillerStatus_wo_nan) - train_size
  680. trainStatus = np.array(ChillerStatus_wo_nan[0:train_size])
  681. trainCalAmount = np.array(CalAmount_wo_nan[0:train_size])
  682. trainRefStatus1 = np.array(RefStatus1_wo_nan[0:train_size])
  683. trainRefStatus2 = np.array(RefStatus2_wo_nan[0:train_size])
  684. GradientCalAmount_mode_Icing = []
  685. GradientCalAmount_mode_StorageOnly = []
  686. GradientCalAmount_mode_Parallel = []
  687. GradientCalAmount_mode_ChillerOnly_1 = []
  688. GradientCalAmount_mode_ChillerOnly_2 = []
  689. isNan_Point = False
  690. for i in range(len(trainStatus)):
  691. for j in range(len(nan_point)):
  692. if i == nan_point[j]:
  693. isNan_Point=True
  694. break
  695. if not isNan_Point:
  696. if trainStatus[i] == Icing and trainCalAmount[i] > trainCalAmount[i-1] and trainRefStatus1[i] == 1 and trainRefStatus2[i] == 1:
  697. GradientCalAmount_mode_Icing.append(trainCalAmount[i]-trainCalAmount[i-1])
  698. elif trainStatus[i] == StorageOnly and trainCalAmount[i] < trainCalAmount[i-1]:
  699. GradientCalAmount_mode_StorageOnly.append(trainCalAmount[i]-trainCalAmount[i-1])
  700. elif trainStatus[i] == Parallel and trainCalAmount[i] < trainCalAmount[i-1] and trainRefStatus1[i] + trainRefStatus2[i] == 1:
  701. GradientCalAmount_mode_Parallel.append(trainCalAmount[i]-trainCalAmount[i-1])
  702. elif trainStatus[i] == ChillerOnly and trainRefStatus1[i] + trainRefStatus2[i] == 1:
  703. GradientCalAmount_mode_ChillerOnly_1.append(trainCalAmount[i]-trainCalAmount[i-1])
  704. elif trainStatus[i] == ChillerOnly and trainRefStatus1[i] + trainRefStatus2[i] == 2:
  705. GradientCalAmount_mode_ChillerOnly_2.append(trainCalAmount[i]-trainCalAmount[i-1])
  706. isNan_Point = False
  707. GradientCalAmount_w3sigma_mode_Icing = []
  708. if len(GradientCalAmount_mode_Icing) != 0:
  709. max3sigma_mode_Icing = np.mean(GradientCalAmount_mode_Icing)+np.std(GradientCalAmount_mode_Icing)*3
  710. min3sigma_mode_Icing = np.mean(GradientCalAmount_mode_Icing)-np.std(GradientCalAmount_mode_Icing)*3
  711. GradientCalAmount_w3sigma_mode_StorageOnly = []
  712. if len(GradientCalAmount_mode_StorageOnly) != 0:
  713. max3sigma_mode_StorageOnly = np.mean(GradientCalAmount_mode_StorageOnly)+np.std(GradientCalAmount_mode_StorageOnly)*3
  714. min3sigma_mode_StorageOnly = np.mean(GradientCalAmount_mode_StorageOnly)-np.std(GradientCalAmount_mode_StorageOnly)*3
  715. GradientCalAmount_w3sigma_mode_Parallel = []
  716. if len(GradientCalAmount_mode_Parallel) != 0:
  717. max3sigma_mode_Parallel = np.mean(GradientCalAmount_mode_Parallel)+np.std(GradientCalAmount_mode_Parallel)*3
  718. min3sigma_mode_Parallel = np.mean(GradientCalAmount_mode_Parallel)-np.std(GradientCalAmount_mode_Parallel)*3
  719. GradientCalAmount_w3sigma_mode_ChillerOnly_1 = []
  720. if len(GradientCalAmount_mode_ChillerOnly_1) != 0:
  721. max3sigma_mode_ChillerOnly_1 = np.mean(GradientCalAmount_mode_ChillerOnly_1)+np.std(GradientCalAmount_mode_ChillerOnly_1)*3
  722. min3sigma_mode_ChillerOnly_1 = np.mean(GradientCalAmount_mode_ChillerOnly_1)-np.std(GradientCalAmount_mode_ChillerOnly_1)*3
  723. GradientCalAmount_w3sigma_mode_ChillerOnly_2 = []
  724. if len(GradientCalAmount_mode_ChillerOnly_2) != 0:
  725. max3sigma_mode_ChillerOnly_2 = np.mean(GradientCalAmount_mode_ChillerOnly_2)+np.std(GradientCalAmount_mode_ChillerOnly_2)*3
  726. min3sigma_mode_ChillerOnly_2 = np.mean(GradientCalAmount_mode_ChillerOnly_2)-np.std(GradientCalAmount_mode_ChillerOnly_2)*3
  727. for i in range(len(GradientCalAmount_mode_Icing)):
  728. if GradientCalAmount_mode_Icing[i] <= max3sigma_mode_Icing and GradientCalAmount_mode_Icing[i] >= min3sigma_mode_Icing:
  729. GradientCalAmount_w3sigma_mode_Icing.append(GradientCalAmount_mode_Icing[i])
  730. for i in range(len(GradientCalAmount_mode_StorageOnly)):
  731. if GradientCalAmount_mode_StorageOnly[i] <= max3sigma_mode_StorageOnly and GradientCalAmount_mode_StorageOnly[i] >= min3sigma_mode_StorageOnly:
  732. GradientCalAmount_w3sigma_mode_StorageOnly.append(GradientCalAmount_mode_StorageOnly[i])
  733. for i in range(len(GradientCalAmount_mode_Parallel)):
  734. if GradientCalAmount_mode_Parallel[i] <= max3sigma_mode_Parallel and GradientCalAmount_mode_Parallel[i] >= min3sigma_mode_Parallel:
  735. GradientCalAmount_w3sigma_mode_Parallel.append(GradientCalAmount_mode_Parallel[i])
  736. for i in range(len(GradientCalAmount_mode_ChillerOnly_1)):
  737. if GradientCalAmount_mode_ChillerOnly_1[i] <= max3sigma_mode_ChillerOnly_1 and GradientCalAmount_mode_ChillerOnly_1[i] >= min3sigma_mode_ChillerOnly_1:
  738. GradientCalAmount_w3sigma_mode_ChillerOnly_1.append(GradientCalAmount_mode_ChillerOnly_1[i])
  739. for i in range(len(GradientCalAmount_mode_ChillerOnly_2)):
  740. if GradientCalAmount_mode_ChillerOnly_2[i] <= max3sigma_mode_ChillerOnly_2 and GradientCalAmount_mode_ChillerOnly_2[i] >= min3sigma_mode_ChillerOnly_2:
  741. GradientCalAmount_w3sigma_mode_ChillerOnly_2.append(GradientCalAmount_mode_ChillerOnly_2[i])
  742. #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))
  743. #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))
  744. print('************ (Finish) Load & pre-processing data !! ************')
  745. print('****************************************************************')
  746. #######################################################################################
  747. ############################################################################################################
  748. #################### Prediction for the Degree of Daily Deicing ############################################
  749. ## 매일 21시~21시 15분 사이에 산출 및 DB 삽입
  750. if (now.hour == 21 and (now.minute > 0 or now.minute < 16)) or Init:
  751. print('************ (Start) The Degree of Daily Deicing is being predicted!! ************')
  752. DailyDeicingAmount = []
  753. DailyDeicingAmount_kWh = []
  754. idx = 0
  755. if isRecent and now.hour < 21: ## 21시를 전, 후로 익일 예상 방냉량이 업데이트
  756. _DayPeriod = DayPeriod-1
  757. else:
  758. _DayPeriod = DayPeriod
  759. for i in range(_DayPeriod):
  760. tmpAmount = []
  761. tmpAmount_kWh = []
  762. if i == 0:
  763. time_length = 4*21 # 첫번째 날은 저녁 9시까지 방냉량만 산출
  764. else:
  765. time_length = 96
  766. for time_idx in range(time_length):
  767. if q_accum_percent[idx] > q_accum_percent[idx+1]:
  768. tmpAmount.append(q_accum_percent[idx]-q_accum_percent[idx+1])
  769. tmpAmount_kWh.append(q_accum_kWh[idx]-q_accum_kWh[idx+1])
  770. idx += 1
  771. if len(tmpAmount) > 0:
  772. DailyDeicingAmount.append(sum(tmpAmount))
  773. DailyDeicingAmount_kWh.append(sum(tmpAmount_kWh))
  774. else:
  775. DailyDeicingAmount.append(0)
  776. DailyDeicingAmount_kWh.append(0)
  777. DateinDay=[]
  778. for k in range(_DayPeriod):
  779. DateinDay.append(RawDate[k*DataRes_96])
  780. DoW, DayType = getDayType(DateinDay, _DayPeriod, SpecialHoliday)
  781. # Collect the normal data
  782. X = []
  783. Y = []
  784. _isnan = False
  785. for i in range(_DayPeriod):
  786. if DayType[i][0] < 3 and DailyDeicingAmount[i] > 0: ## 평일이면서 축열조를 가동하고 결측값이 없는 날만 추출
  787. if i == _DayPeriod-1:
  788. time_len = int(len(ChillerStatus)%96)
  789. else:
  790. time_len = DataRes_96
  791. for j in range(time_len):
  792. if math.isnan(ChillerStatus[i*DataRes_96+j]):
  793. _isnan = True
  794. if not _isnan:
  795. 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]])
  796. Y.append(DailyDeicingAmount[i])
  797. _isnan = False
  798. xTrain, xVal, yTrain, yVal = train_test_split(X, Y, test_size=0.001, shuffle = False)
  799. 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]]
  800. #MSE의 변화를 확인하기 위하여 앙상블의 크기 범위에서 랜덤 포레스트 트레이닝
  801. maeOos = []
  802. Acc_CVRMSE = []
  803. Acc_MBE = []
  804. nTreeList = range(100, 200, 50)
  805. for iTrees in nTreeList:
  806. depth = None
  807. maxFeat = np.matrix(X).shape[1] #조정해볼 것
  808. DailyDeicing_RFModel = ensemble.RandomForestRegressor(n_estimators=iTrees,
  809. max_depth=depth, max_features=maxFeat,
  810. oob_score=False, random_state=42)
  811. DailyDeicing_RFModel.fit(xTrain, yTrain)
  812. #데이터 세트에 대한 MSE 누적
  813. prediction = DailyDeicing_RFModel.predict(xVal)
  814. maeOos.append(MAE(yVal, prediction))
  815. Acc_MBE.append(MBE(yVal, prediction))
  816. Acc_CVRMSE.append(CVRMSE(np.array(yVal), np.array(prediction)))
  817. #print('prediction', prediction)
  818. #print('yVal', yVal)
  819. #print("Validation Set of MAE : ",maeOos[-1])
  820. #print("Validation Set of CVRMSE : ", CVRMSE(yVal, prediction))
  821. #print("Validation Set of Aver. CVRMSE : ", np.mean(Acc_CVRMSE))
  822. PredictedDeIcingAmount = DailyDeicing_RFModel.predict([xTomorrow_WF]) ## 학습모델을 통한 익일 방냉량 예측
  823. PredictedDeIcingAmount_Tomorrow = round(PredictedDeIcingAmount[0],6)
  824. print('####################################################')
  825. print('## Estimated daily Deicing amount = ', PredictedDeIcingAmount_Tomorrow, ' % ##')
  826. print('####################################################')
  827. #### 익일 방냉량 DB 삽입
  828. ### Day-ahead deicing amount is updated everyday
  829. # MSSQL Access
  830. conn = pymssql.connect(host = targetDBIP, user = targetDBUserID, password = targetDBUserPW, database = targetDBName, autocommit=True)
  831. # Create Cursor from Connection
  832. cursor = conn.cursor()
  833. if now.hour >= 21:
  834. TargetDate = datetime.datetime(now.year,now.month,now.day,21,0,0) + datetime.timedelta(days=1)
  835. else:
  836. TargetDate = datetime.datetime(now.year,now.month,now.day,21,0,0)
  837. ## Storage deicing amount
  838. 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")
  839. # 데이타 하나씩 Fetch하여 출력
  840. row = cursor.fetchone()
  841. rawData=[]
  842. while row:
  843. row = cursor.fetchone()
  844. rawData.append(row)
  845. if rawData:
  846. try:
  847. 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')+"'")
  848. print("* The prediction of Daily deicing amount was updated!! (Recommend)")
  849. except:
  850. print("[ERROR] There is an update error!! (Daily deicing amount)")
  851. else:
  852. try:
  853. 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) + ")" )
  854. print("* The prediction of daily deicing amount was inserted!! (Recommend)")
  855. except:
  856. print("[ERROR] There is an insert error!! (Daily deicing amount)")
  857. print('************ (Finish) The Degree of Daily Deicing is being predicted!! ************')
  858. print('***********************************************************************************')
  859. #######################################################################################
  860. ##################################################################################################################################################
  861. ################# Find Optimal Operating Schedule for predicted daily deicing amount #############################################################
  862. ## 15분 주기로 현상태 반영하여 업데이트
  863. print('************ (Start) Recommended operating schedule is being found!! ************')
  864. if now.hour >= 0 and now.hour < 21:
  865. simul_lth = 24*4 - (now.hour*4 + int(now.minute/15)) - 3*4 ## (15분 단위 카운트)
  866. else:
  867. simul_lth = 24*4 - (now.hour*4 +int(now.minute/15)) + 21*4
  868. # 이미 지난 시간(전날 9 pm 이후)에 대한 데이터 정리
  869. inputX_prev = ChillerStatus_wo_nan[len(ChillerStatus_wo_nan)-(96-simul_lth):len(ChillerStatus_wo_nan)]
  870. inputX_REF1_prev = RefStatus1_wo_nan[len(RefStatus1_wo_nan)-(96-simul_lth):len(RefStatus1_wo_nan)]
  871. inputX_REF2_prev = RefStatus2_wo_nan[len(RefStatus2_wo_nan)-(96-simul_lth):len(RefStatus2_wo_nan)]
  872. RecommendedCalAmount_prev = CalAmount_wo_nan[len(CalAmount_wo_nan)-(96-simul_lth):len(CalAmount_wo_nan)]
  873. print('* Current Amount : ', CalAmount_wo_nan[-1], '[%], ', 'Estimated Deicing Amount : ', PredictedDeIcingAmount_Tomorrow, '[%]')
  874. idx = 0
  875. TermNum = 96
  876. RecommendedCalAmount = [CalAmount_wo_nan[-1]]
  877. if now.hour >= 21 or now.hour < 6:
  878. while RecommendedCalAmount[-1] < PredictedDeIcingAmount_Tomorrow:
  879. idx += 1
  880. if idx >= simul_lth:
  881. print("* It should be fully operated")
  882. break
  883. inputX = []
  884. inputX_REF1 = []
  885. inputX_REF2 = []
  886. ## 단순히 심야 운전만 고려하고 축냉량 시 제빙모드와 OFF만 고려하여 시뮬레이션 (다른 모드를 추가하여 구성할 수 있음)
  887. ## Off=0, Icing = 10, StorageOnly = 20, Parallel = 30, ChillerOnly = 40
  888. ## 추천 방냉은 저녁 9시 이후부터 아침 6시 사이까지.... 중간에 사용하고 있는 부분에 대한 것은 어떻게 처리할지...고민해야함...낮에 축단운전을 하기에....
  889. for i in range(idx):
  890. inputX.append(Icing)
  891. inputX_REF1.append(1)
  892. inputX_REF2.append(1)
  893. for i in range(simul_lth-len(inputX)):
  894. inputX.append(0)
  895. inputX_REF1.append(0)
  896. inputX_REF2.append(0)
  897. RecommendedCalAmount = [CalAmount_wo_nan[-1]]
  898. for i in range(len(inputX)):
  899. if i == 1:
  900. RecommendedCalAmount = RecommendedCalAmount[-1]
  901. if inputX[i]==Icing:
  902. if inputX_REF1[i] + inputX_REF2[i]==2:
  903. RecommendedCalAmount.append(RecommendedCalAmount[-1]+np.mean(GradientCalAmount_w3sigma_mode_Icing))
  904. elif inputX_REF1[i] + inputX_REF2[i]==1:
  905. RecommendedCalAmount.append(RecommendedCalAmount[-1]+np.mean(GradientCalAmount_w3sigma_mode_Icing)/2)
  906. else:
  907. RecommendedCalAmount.append(RecommendedCalAmount[-1])
  908. elif inputX[i]==StorageOnly:
  909. RecommendedCalAmount.append(RecommendedCalAmount[-1]+np.mean(GradientCalAmount_w3sigma_mode_StorageOnly))
  910. elif inputX[i]==Parallel:
  911. if inputX_REF1[i] + inputX_REF2[i]==2:
  912. RecommendedCalAmount.append(RecommendedCalAmount[-1]+np.mean(GradientCalAmount_w3sigma_mode_Parallel)*2)
  913. elif inputX_REF1[i] + inputX_REF2[i]==1:
  914. RecommendedCalAmount.append(RecommendedCalAmount[-1]+np.mean(GradientCalAmount_w3sigma_mode_Parallel))
  915. else:
  916. RecommendedCalAmount.append(RecommendedCalAmount[-1])
  917. elif inputX[i]==ChillerOnly:
  918. if inputX_REF1[i] + inputX_REF2[i]==2:
  919. RecommendedCalAmount.append(RecommendedCalAmount[-1]+np.mean(GradientCalAmount_w3sigma_mode_ChillerOnly_2))
  920. elif inputX_REF1[i] + inputX_REF2[i]==1:
  921. RecommendedCalAmount.append(RecommendedCalAmount[-1]+np.mean(GradientCalAmount_w3sigma_mode_ChillerOnly_1))
  922. else:
  923. RecommendedCalAmount.append(RecommendedCalAmount[-1])
  924. elif inputX[i]==0:
  925. RecommendedCalAmount.append(RecommendedCalAmount[-1])
  926. ## 0이나 100을 넘어갔을 경우 보정 (현재 데이터에서 축열량은 % 단위이기 때문에)
  927. if RecommendedCalAmount[-1] >= 100:
  928. RecommendedCalAmount[-1] = 100
  929. elif RecommendedCalAmount[-1] <= 0:
  930. RecommendedCalAmount[-1] = 0
  931. #print('max.',np.max(RecommendedCalAmount[-1]))
  932. else:
  933. print("************ It is not time to operate the storage in icing mode ")
  934. if idx == 0:
  935. inputX = []
  936. inputX_REF1 = []
  937. inputX_REF2 = []
  938. RecommendedCalAmount = []
  939. for i in range(simul_lth):
  940. inputX.append(0)
  941. inputX_REF1.append(0)
  942. inputX_REF2.append(0)
  943. RecommendedCalAmount.append(CalAmount_wo_nan[-1])
  944. inputX = inputX_prev + inputX
  945. inputX_REF1 = inputX_REF1_prev + inputX_REF1
  946. inputX_REF2 = inputX_REF2_prev + inputX_REF2
  947. RecommendedCalAmount = RecommendedCalAmount_prev + RecommendedCalAmount
  948. #### 실제 및 추천 운전 스케쥴 DB 삽입
  949. #### Recommended operating schedule is updated everyday
  950. # MSSQL Access
  951. conn = pymssql.connect(host = targetDBIP, user = targetDBUserID, password = targetDBUserPW, database = targetDBName, autocommit=True)
  952. # Create Cursor from Connection
  953. cursor = conn.cursor()
  954. # Execute SQL
  955. if now.hour >= 21:
  956. InitDate = datetime.datetime(now.year,now.month,now.day,21,0,0)
  957. else:
  958. InitDate = datetime.datetime(now.year,now.month,now.day,21,0,0)-datetime.timedelta(days=1)
  959. ## Storage mode
  960. 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")
  961. # 데이타 하나씩 Fetch하여 출력
  962. row = cursor.fetchone()
  963. rawData=[]
  964. while row:
  965. row = cursor.fetchone()
  966. rawData.append(row)
  967. if rawData:
  968. try:
  969. for i in range(TermNum):
  970. TmpDate = InitDate + datetime.timedelta(minutes=i*15)
  971. 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')+"'")
  972. print("* The storage operating schedule was updated!! (Recommend)")
  973. except:
  974. print("[ERROR] There is an update error!! (Ice storage mode)")
  975. else:
  976. try:
  977. for i in range(TermNum):
  978. TmpDate = InitDate + datetime.timedelta(minutes=i*15)
  979. 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)" )
  980. print("* The storage operating schedule was inserted!! (Recommend)")
  981. except:
  982. print("[ERROR] There is an insert error!! (Ice storage mode)")
  983. ## REF1 status
  984. 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")
  985. # 데이타 하나씩 Fetch하여 출력
  986. row = cursor.fetchone()
  987. rawData=[]
  988. while row:
  989. row = cursor.fetchone()
  990. rawData.append(row)
  991. if rawData:
  992. try:
  993. for i in range(TermNum):
  994. TmpDate = InitDate + datetime.timedelta(minutes=i*15)
  995. 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')+"'")
  996. print("* The refrigerator1 status was updated!! (Recommend)")
  997. except:
  998. print("[Error] There is an update error!! (Recommended refrigerator1 status)")
  999. else:
  1000. try:
  1001. for i in range(TermNum):
  1002. TmpDate = InitDate + datetime.timedelta(minutes=i*15)
  1003. 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)" )
  1004. print("* The refrigerator1 status was inserted!! (Recommend)")
  1005. except:
  1006. print("[Error] There is an insert error!! (Recommended refrigerator1 status)")
  1007. ## REF1 power consume
  1008. 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")
  1009. # 데이타 하나씩 Fetch하여 출력
  1010. row = cursor.fetchone()
  1011. rawData=[]
  1012. while row:
  1013. row = cursor.fetchone()
  1014. rawData.append(row)
  1015. if rawData:
  1016. try:
  1017. for i in range(TermNum):
  1018. TmpDate = InitDate + datetime.timedelta(minutes=i*15)
  1019. if inputX_REF1[i]==1:
  1020. TmpComsume = mean_RefConsume1
  1021. else:
  1022. TmpComsume = 0
  1023. 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')+"'")
  1024. print("* The recommended refrigerator1 power was updated!! (Recommend)")
  1025. except:
  1026. print("[ERROR] There is an update error!! (Recommended refrigerator1 power)")
  1027. else:
  1028. try:
  1029. for i in range(TermNum):
  1030. TmpDate = InitDate + datetime.timedelta(minutes=i*15)
  1031. if inputX_REF1[i]==1:
  1032. TmpComsume = mean_RefConsume1
  1033. else:
  1034. TmpComsume = 0
  1035. 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)" )
  1036. print("* The recommended refrigerator1 power was inserted!! (Recommend)")
  1037. except:
  1038. print("[ERROR] There is an insert error!! (Recommended refrigerator1 power)")
  1039. ## REF2 status
  1040. 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")
  1041. # 데이타 하나씩 Fetch하여 출력
  1042. row = cursor.fetchone()
  1043. rawData=[]
  1044. while row:
  1045. row = cursor.fetchone()
  1046. rawData.append(row)
  1047. if rawData:
  1048. try:
  1049. for i in range(TermNum):
  1050. TmpDate = InitDate + datetime.timedelta(minutes=i*15)
  1051. 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')+"'")
  1052. print("* The refrigerator2 status was updated!! (Recommend)")
  1053. except:
  1054. print("[ERROR] There is an update error!! (Recommended refrigerator2 status)")
  1055. else:
  1056. try:
  1057. for i in range(TermNum):
  1058. TmpDate = InitDate + datetime.timedelta(minutes=i*15)
  1059. 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)" )
  1060. print("* The refrigerator2 status was inserted!! (Recommend)")
  1061. except:
  1062. print("[ERROR] There is an insert error!! (Recommended refrigerator2 status)")
  1063. ## REF2 power consume
  1064. 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")
  1065. # 데이타 하나씩 Fetch하여 출력
  1066. row = cursor.fetchone()
  1067. rawData=[]
  1068. while row:
  1069. row = cursor.fetchone()
  1070. rawData.append(row)
  1071. if rawData:
  1072. try:
  1073. for i in range(TermNum):
  1074. TmpDate = InitDate + datetime.timedelta(minutes=i*15)
  1075. if inputX_REF2[i]==1:
  1076. TmpComsume = mean_RefConsume2
  1077. else:
  1078. TmpComsume = 0
  1079. 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')+"'")
  1080. print("* The recommended refrigerator2 power was updated!! (Recommend)")
  1081. except:
  1082. print("[ERROR] There is an update error!! (Recommended Refrigerator2 power)")
  1083. else:
  1084. try:
  1085. for i in range(TermNum):
  1086. TmpDate = InitDate + datetime.timedelta(minutes=i*15)
  1087. if inputX_REF2[i]==1:
  1088. TmpComsume = mean_RefConsume2
  1089. else:
  1090. TmpComsume = 0
  1091. 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)" )
  1092. print("* The refrigerator2 power was inserted!! (Recommend)")
  1093. except:
  1094. print("[ERROR] There is an insert error!! (Recommended Refrigerator2 power)")
  1095. ## Thermal energy amount
  1096. 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")
  1097. # 데이타 하나씩 Fetch하여 출력
  1098. row = cursor.fetchone()
  1099. rawData=[]
  1100. while row:
  1101. row = cursor.fetchone()
  1102. rawData.append(row)
  1103. if rawData:
  1104. try:
  1105. for i in range(TermNum):
  1106. TmpDate = InitDate + datetime.timedelta(minutes=i*15)
  1107. 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')+"'")
  1108. print("* Thermal energy amount was updated!! (Recommend)")
  1109. except:
  1110. print("[ERROR] There is an update error!! (Recommended thermal energy amount)")
  1111. else:
  1112. try:
  1113. for i in range(TermNum):
  1114. TmpDate = InitDate + datetime.timedelta(minutes=i*15)
  1115. 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)" )
  1116. print("* Thermal energy amount was inserted!! (Recommend)")
  1117. except:
  1118. print("[ERROR] There is an insert error!! (Recommended thermal energy amount)")
  1119. ## 첫 실행시에만 동작
  1120. if Init:
  1121. ## Thermal energy amount (과거 확인 후 축열량이 공백인 경우 채워주기)
  1122. CalAmount_prev_tmp = CalAmount_prev[len(CalAmount_prev)-TermNum*5:]
  1123. for d in range(5, 0, -1): # 5일전까지
  1124. InitDate_tmp = InitDate-datetime.timedelta(days=d)
  1125. for m in range(TermNum): # 1열씩 업데이트 (중간중간 공백인 경우를 고려)
  1126. TmpDate = InitDate_tmp + datetime.timedelta(minutes=m*15)
  1127. 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")
  1128. # 데이타 하나씩 Fetch하여 출력
  1129. row = cursor.fetchone()
  1130. if row:
  1131. try:
  1132. 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')+"'")
  1133. except:
  1134. print("[ERROR] There is an update error!! (Recommended thermal energy amount)")
  1135. else:
  1136. try:
  1137. 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)" )
  1138. except:
  1139. print("[ERROR] There is an insert error!! (Recommended thermal energy amount)")
  1140. conn.close()
  1141. print('************ (Finish) Recommended operating schedule is being found!! ************')
  1142. print('**********************************************************************************')
  1143. #######################################################################################
  1144. ##################################################################################################################################################
  1145. ################# Stochastic method for estimating the Variation of Ice Thermal Storage based on Operation Mode "for Simulation" #################
  1146. #### 사용자 정의 데이터를 데이터 로드
  1147. ### 계속 체킹
  1148. while True:
  1149. now_ = datetime.datetime.now().now()
  1150. ## sleep 매분 2,6,10,... 초에만 동작
  1151. if now_.second%4==2:
  1152. break
  1153. time.sleep(1)
  1154. #time.sleep(2)
  1155. #print('start time : ', now_)
  1156. # MSSQL Access
  1157. conn = pymssql.connect(host = targetDBIP, user = targetDBUserID, password = targetDBUserPW, database = targetDBName, autocommit=True)
  1158. # Create Cursor from Connection
  1159. cursor = conn.cursor()
  1160. # Execute SQL
  1161. 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')
  1162. row = cursor.fetchone()
  1163. conn.close()
  1164. if Init:
  1165. if row != None:
  1166. recentDateTime = row[4]
  1167. else:
  1168. recentDateTime = now_
  1169. Init = False
  1170. ActiveSimulator = False
  1171. if row != None:
  1172. if recentDateTime < row[4]:
  1173. recentDateTime = row[4]
  1174. ActiveSimulator = True
  1175. else:
  1176. ActiveSimulator = False
  1177. now_ = datetime.datetime.now().now()
  1178. if now_.second%30 > 0 and now_.second%30 < 2:
  1179. print('* Keep an eye on updating DB table every 2 seconds ... (This message appears every 30 seconds)')
  1180. if ActiveSimulator:
  1181. print('************ (Start) Simulator! ************')
  1182. time.sleep(2)
  1183. # MSSQL Access
  1184. conn = pymssql.connect(host = targetDBIP, user = targetDBUserID, password = targetDBUserPW, database = targetDBName, autocommit=True)
  1185. # Create Cursor from Connection
  1186. cursor = conn.cursor()
  1187. InitDate = datetime.datetime(now.year, now.month, now.day, now.hour, int(int(now.minute/15)*15), 0)
  1188. FinalDate = datetime.datetime(now.year, now.month, now.day, 21, 0, 0)
  1189. TmpTime = InitDate
  1190. TimeLen = 0
  1191. while TmpTime < FinalDate:
  1192. TmpTime += datetime.timedelta(minutes=15)
  1193. TimeLen += 1
  1194. while True:
  1195. ## Storage mode
  1196. 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")
  1197. # 데이타 한꺼번에 Fetch
  1198. rows = cursor.fetchall()
  1199. rawData_StorageMode = []
  1200. for i in rows:
  1201. rawData_StorageMode.append(i)
  1202. ## REF1 status
  1203. 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")
  1204. # 데이타 한꺼번에 Fetch
  1205. rows = cursor.fetchall()
  1206. rawData_RefStatus1 = []
  1207. for i in rows:
  1208. rawData_RefStatus1.append(i)
  1209. ## REF2 status
  1210. 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")
  1211. # 데이타 한꺼번에 Fetch
  1212. rows = cursor.fetchall()
  1213. rawData_RefStatus2 = []
  1214. for i in rows:
  1215. rawData_RefStatus2.append(i)
  1216. CustomizedStatus=[]
  1217. for i in range(len(rawData_StorageMode)):
  1218. CustomizedStatus.append(rawData_StorageMode[i][6])
  1219. CustomizedRefStatus1=[]
  1220. for i in range(len(rawData_RefStatus1)):
  1221. CustomizedRefStatus1.append(rawData_RefStatus1[i][6])
  1222. CustomizedRefStatus2 = []
  1223. for i in range(len(rawData_RefStatus2)):
  1224. CustomizedRefStatus2.append(rawData_RefStatus2[i][6])
  1225. if TimeLen == len(CustomizedStatus) and TimeLen == len(CustomizedRefStatus1) and TimeLen == len(CustomizedRefStatus2):
  1226. break
  1227. time.sleep(1)
  1228. SimulCalAmount=[CalAmount_wo_nan[-1]]
  1229. for i in range(len(CustomizedStatus)):
  1230. if i == 1:
  1231. SimulCalAmount = [SimulCalAmount[-1]]
  1232. ## 제빙운전은 두대로 운영되었으므로 평균값은 2대 운전 기준
  1233. if CustomizedStatus[i] == Icing:
  1234. if len(GradientCalAmount_w3sigma_mode_Icing) == 0:
  1235. print('[Warning] There is no enough data (Icing)')
  1236. SimulCalAmount.append(SimulCalAmount[-1])
  1237. else:
  1238. if CustomizedRefStatus1[i] + CustomizedRefStatus2[i] == 2:
  1239. SimulCalAmount.append(SimulCalAmount[-1]+np.mean(GradientCalAmount_w3sigma_mode_Icing))
  1240. elif CustomizedRefStatus1[i] + CustomizedRefStatus2[i] == 1:
  1241. SimulCalAmount.append(SimulCalAmount[-1]+np.mean(GradientCalAmount_w3sigma_mode_Icing)/2)
  1242. else:
  1243. SimulCalAmount.append(SimulCalAmount[-1])
  1244. ## 축단운전은 냉동기가 운영되지 않음
  1245. elif CustomizedStatus[i] == StorageOnly:
  1246. if len(GradientCalAmount_w3sigma_mode_StorageOnly) == 0:
  1247. print('[Warning] There is no enough data (Storage Only)')
  1248. SimulCalAmount.append(SimulCalAmount[-1])
  1249. else:
  1250. SimulCalAmount.append(SimulCalAmount[-1]+np.mean(GradientCalAmount_w3sigma_mode_StorageOnly))
  1251. ## 병렬운전에서 축열조 변화량은 냉동기 상태와 상관없음
  1252. elif CustomizedStatus[i] == Parallel:
  1253. if len(GradientCalAmount_w3sigma_mode_Parallel) == 0:
  1254. print('[Warning] There is no enough data (Parallel)')
  1255. SimulCalAmount.append(SimulCalAmount[-1])
  1256. else:
  1257. SimulCalAmount.append(SimulCalAmount[-1]+np.mean(GradientCalAmount_w3sigma_mode_Parallel))
  1258. ## 냉단운전은 냉동기 두대로 운영되었으므로 축열량은 그대로
  1259. elif CustomizedStatus[i] == ChillerOnly:
  1260. if len(GradientCalAmount_w3sigma_mode_ChillerOnly_1) == 0:
  1261. print('[Warning] There is no enough data (Chiller Only_1)')
  1262. SimulCalAmount.append(SimulCalAmount[-1])
  1263. elif len(GradientCalAmount_w3sigma_mode_ChillerOnly_2) == 0:
  1264. print('[Warning] There is no enough data (Chiller Only_2)')
  1265. SimulCalAmount.append(SimulCalAmount[-1])
  1266. else:
  1267. SimulCalAmount.append(SimulCalAmount[-1])
  1268. elif CustomizedStatus[i]==0:
  1269. SimulCalAmount.append(SimulCalAmount[-1])
  1270. if SimulCalAmount[-1] > 100:
  1271. SimulCalAmount[-1] = 100
  1272. CustomizedRefStatus1[i] = 0
  1273. CustomizedRefStatus2[i] = 0
  1274. elif SimulCalAmount[-1] < 0:
  1275. SimulCalAmount[-1] = 0
  1276. CustomizedRefStatus1[i] = 0
  1277. CustomizedRefStatus2[i] = 0
  1278. #### 시뮬레이션 결과 데이터 DB 삽입
  1279. ## REF1 power consume
  1280. for i in range(len(CustomizedStatus)):
  1281. TmpDate = InitDate + datetime.timedelta(minutes=i*15)
  1282. 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') + "' ")
  1283. if CustomizedRefStatus1[i]==1:
  1284. TmpComsume = mean_RefConsume1
  1285. else:
  1286. TmpComsume = 0
  1287. # 데이타 하나씩 Fetch하여 출력
  1288. row = cursor.fetchone()
  1289. if row:
  1290. try:
  1291. 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')+"'")
  1292. if i == len(CustomizedStatus)-1:
  1293. print("* The REF1 power comsumption was updated!! (Simul)")
  1294. except:
  1295. print("[ERROR] There is an update error!! (Simulated refrigerator1 power)")
  1296. else:
  1297. try:
  1298. 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)" )
  1299. if i == len(CustomizedStatus)-1:
  1300. print("* The REF1 power comsumption was inserted!! (Simul)")
  1301. except:
  1302. print("[ERROR] There is an insert error!! (Simulated refrigerator1 power)")
  1303. ## REF2 power consume
  1304. for i in range(len(CustomizedStatus)):
  1305. TmpDate = InitDate + datetime.timedelta(minutes=i*15)
  1306. 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') + "' ")
  1307. if CustomizedRefStatus2[i]==1:
  1308. TmpComsume = mean_RefConsume2
  1309. else:
  1310. TmpComsume = 0
  1311. # 데이타 하나씩 Fetch하여 출력
  1312. row = cursor.fetchone()
  1313. if row:
  1314. try:
  1315. 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')+"'")
  1316. if i == len(CustomizedStatus)-1:
  1317. print("* The REF2 power comsumption was updated!! (Simul)")
  1318. except:
  1319. print("[ERROR] There is an update error!! (Simulated refrigerator2 power)")
  1320. else:
  1321. try:
  1322. 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)" )
  1323. if i == len(CustomizedStatus)-1:
  1324. print("* The REF2 power comsumption was inserted!! (Simul)")
  1325. except:
  1326. print("[ERROR] There is an insert error!! (Simulated refrigerator2 power)")
  1327. ## Thermal energy amount
  1328. for i in range(len(CustomizedStatus)):
  1329. TmpDate = InitDate + datetime.timedelta(minutes=i*15)
  1330. 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') + "' ")
  1331. # 데이타 하나씩 Fetch하여 출력
  1332. row = cursor.fetchone()
  1333. if row:
  1334. try:
  1335. 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')+"'")
  1336. if i == len(CustomizedStatus)-1:
  1337. print("* Thermal energy amount was updated!! (Simul)")
  1338. except:
  1339. print("[ERROR] There is an update error!! (Simulated thermal energy amount)")
  1340. else:
  1341. try:
  1342. 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)" )
  1343. if i == len(CustomizedStatus)-1:
  1344. print("* Thermal energy amount was inserted!! (Simul)")
  1345. except:
  1346. print("[ERROR] There is an insert error!! (Simulated thermal energy amount)")
  1347. conn.close()
  1348. print('************ (Finish) Simulator! ************')
  1349. print('*********************************************')
  1350. #######################################################################################