RealTimeSimulator_HeatStorageSystem.py 78 KB

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