0c5758f9c7cee6c2b5615a8a2d3eb731541271eb.svn-base 71 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357135813591360136113621363136413651366136713681369137013711372137313741375137613771378137913801381138213831384138513861387138813891390139113921393139413951396139713981399140014011402140314041405140614071408140914101411141214131414141514161417141814191420142114221423142414251426142714281429143014311432143314341435143614371438143914401441144214431444144514461447144814491450145114521453145414551456145714581459146014611462146314641465146614671468146914701471147214731474147514761477147814791480148114821483148414851486148714881489149014911492149314941495149614971498149915001501150215031504150515061507150815091510151115121513151415151516151715181519152015211522152315241525152615271528152915301531153215331534153515361537153815391540154115421543154415451546154715481549155015511552155315541555155615571558155915601561156215631564156515661567156815691570157115721573157415751576157715781579158015811582158315841585158615871588158915901591159215931594159515961597159815991600160116021603160416051606160716081609161016111612161316141615161616171618161916201621162216231624162516261627162816291630163116321633163416351636163716381639164016411642164316441645164616471648164916501651165216531654165516561657165816591660166116621663166416651666166716681669167016711672167316741675167616771678167916801681168216831684168516861687168816891690169116921693169416951696169716981699170017011702170317041705170617071708170917101711171217131714171517161717171817191720172117221723172417251726172717281729173017311732173317341735
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Text;
  4. using FirebirdSql.Data.FirebirdClient;
  5. using System.Data;
  6. using System.Collections;
  7. using System.Diagnostics;
  8. namespace FPER
  9. {
  10. public class DacUIProcess : DacBase
  11. {
  12. static int iDACUiProcess = 0;
  13. public DacUIProcess(int ID)
  14. : base(ID)// cyim 2015.7.30 데이타베이스 접속 루틴 변경
  15. {
  16. //try {
  17. // base.Connect();
  18. //}
  19. //catch (Exception ex) {
  20. // Util.UErrorMessage(ex, 0, 0);
  21. //}
  22. iDACUiProcess++;
  23. // cyim_debug
  24. // Debug.WriteLine("<<<<<<< DacUIProcess 생성자 호출 <<<<<< " + Convert.ToString(iDACUiProcess));
  25. }
  26. ~DacUIProcess()
  27. {
  28. //try {
  29. // base.DisConnect();
  30. //}
  31. //catch (Exception ex) {
  32. // Util.UErrorMessage(ex, 0, 0);
  33. //}
  34. iDACUiProcess--;
  35. // cyim_debug
  36. // Debug.WriteLine("<<<<<<< DacUIProcess 소멸자 호출 <<<<<< " + Convert.ToString(iDACUiProcess));
  37. }
  38. //전체이벤트리스트
  39. public int Event_Log_RowCnt(int RECEIVER_ID)
  40. {
  41. int rowcnt = 0;
  42. try
  43. {
  44. String SQL = " SELECT COUNT(*) FROM TB_EVENT_LOG";
  45. DataSet dao = Select(SQL);
  46. DataTable dt = dao.Tables[0];
  47. if (dt.Rows.Count > 0)
  48. {
  49. DataRow dr = dt.Rows[0];
  50. rowcnt = Util.StrToInt(dr[0], 0);
  51. }
  52. SQL = " SELECT COUNT(*) FROM TB_COMMAND_LOG";
  53. DataSet dao2 = Select(SQL);
  54. DataTable dt2 = dao2.Tables[0];
  55. if (dt2.Rows.Count > 0)
  56. {
  57. DataRow dr2 = dt2.Rows[0];
  58. rowcnt += Util.StrToInt(dr2[0], 0);
  59. }
  60. }
  61. catch (Exception ex)
  62. {
  63. Util.UErrorMessage(ex, 0, 0);
  64. throw ex;
  65. }
  66. return rowcnt;
  67. }
  68. //전체이벤트리스트
  69. public int Event_Log_SelectAllCnt(int RECEIVER_ID, int page, int pageSize,
  70. String EVENT_TYPE, bool EVENTTIME_YES, DateTime FROM_DATE, DateTime TO_DATE,
  71. int COMM_ID, int BOARD_ID, int LOOP_NO, int REPEATER_ID, int DEVICE_ID, String INOUT_TYPE
  72. )
  73. {
  74. int rowcnt = 0;
  75. try
  76. {
  77. //String SQL = "";
  78. //SQL += " SELECT COUNT(*) ";
  79. //SQL += " FROM (";
  80. //SQL += " SELECT A.EVENT_ID,A.EVENT_TIME,A.EVENT_TYPE,A.RECEIVER_ID,A.COMM_ID,A.BOARD_ID";
  81. //SQL += " ,A.LOOP_NO,A.REPEATER_ID,A.DEVICE_ID,A.INOUT_TYPE,A.DATA_VALUE,A.CONFIRM_FLAG,A.CONFIRM_TIME";
  82. //SQL += " ,E.EVENT_TYPE_NAME,D.DEVICE_NAME,D.DEVICE_TYPE,D.CUT_FLAG,B.DEVICE_TYPE_NAME,C.POSITION_NAME";
  83. //SQL += " FROM TB_EVENT_LOG A";
  84. //SQL += " LEFT OUTER JOIN TB_EVENT_TYPE E ON A.EVENT_TYPE = E.EVENT_TYPE";
  85. //SQL += " LEFT OUTER JOIN TB_DEVICE D ON A.RECEIVER_ID=D.RECEIVER_ID AND A.COMM_ID=D.COMM_ID AND A.BOARD_ID=D.BOARD_ID";
  86. //SQL += " AND A.LOOP_NO=D.LOOP_NO AND A.REPEATER_ID=D.REPEATER_ID AND A.DEVICE_ID=D.DEVICE_ID AND A.INOUT_TYPE=D.INOUT_TYPE";
  87. //SQL += " LEFT OUTER JOIN TB_DEVICE_TYPE B ON D.DEVICE_TYPE = B.DEVICE_TYPE";
  88. //SQL += " LEFT OUTER JOIN TB_POSITION_CODE C ON D.POSITION_CODE = C.POSITION_CODE";
  89. int tick = Environment.TickCount;
  90. String SQL = "";// sdkim, 20110406, SQL optimisation
  91. SQL += " SELECT COUNT(*) ";
  92. SQL += " FROM (";
  93. SQL += " SELECT A.EVENT_ID,A.EVENT_TIME,A.EVENT_TYPE,A.RECEIVER_ID,A.COMM_ID,A.BOARD_ID";
  94. SQL += " ,A.LOOP_NO,A.REPEATER_ID,A.DEVICE_ID,A.INOUT_TYPE,A.DATA_VALUE";
  95. SQL += " FROM TB_EVENT_LOG A";
  96. SQL += string.Format(" WHERE A.RECEIVER_ID = {0}", RECEIVER_ID);
  97. if (EVENT_TYPE != null)
  98. {
  99. if (EVENT_TYPE.Length == 2) SQL += string.Format(" AND A.EVENT_TYPE = '{0}'", EVENT_TYPE);
  100. }
  101. if (EVENTTIME_YES)
  102. {
  103. String EVENT_TIME_FROM = String.Format("{0:yyyy}{0:MM}{0:dd}{0:HH}{0:mm}{0:ss}00000", FROM_DATE);
  104. String EVENT_TIME_TO = String.Format("{0:yyyy}{0:MM}{0:dd}{0:HH}{0:mm}{0:ss}99999", TO_DATE);
  105. SQL += string.Format(" AND A.EVENT_TIME >= '{0}' AND A.EVENT_TIME <= '{1}' ", EVENT_TIME_FROM, EVENT_TIME_TO);
  106. }
  107. if (COMM_ID > 0)
  108. {
  109. if (COMM_ID == 1) SQL += " AND (A.COMM_ID = 1 OR A.COMM_ID = 2)";
  110. else SQL += string.Format(" AND A.COMM_ID = {0} ", COMM_ID);
  111. if (INOUT_TYPE != null)
  112. {
  113. if (INOUT_TYPE != "")
  114. {
  115. if (COMM_ID == 1)
  116. {
  117. if (BOARD_ID > 0)
  118. {
  119. SQL += string.Format(" AND A.BOARD_ID = {0}", BOARD_ID);
  120. if (LOOP_NO == 0 || LOOP_NO == 1)
  121. {
  122. SQL += string.Format(" AND A.LOOP_NO = {0}", LOOP_NO);
  123. if (REPEATER_ID > 0)
  124. {
  125. SQL += string.Format(" AND A.REPEATER_ID = {0}", REPEATER_ID);
  126. if (DEVICE_ID > 0) SQL += string.Format(" AND A.DEVICE_ID = {0}", DEVICE_ID);
  127. }
  128. }
  129. }
  130. }
  131. else if (COMM_ID == 3)
  132. {
  133. if (BOARD_ID > 0)
  134. {
  135. SQL += string.Format(" AND A.BOARD_ID = {0}", BOARD_ID);
  136. if (DEVICE_ID > 0) SQL += string.Format(" AND A.DEVICE_ID = {0}", DEVICE_ID);
  137. }
  138. }
  139. else if (COMM_ID == 4)
  140. {
  141. if (DEVICE_ID > 0) SQL += string.Format(" AND A.DEVICE_ID = {0}", DEVICE_ID);
  142. }
  143. SQL += string.Format(" AND A.INOUT_TYPE = '{0}'", INOUT_TYPE);
  144. }
  145. }
  146. }
  147. // sdkim, 20110406, SQL optimisation
  148. //SQL += " UNION ALL";
  149. //SQL += " SELECT A.COMMAND_ID,A.COMMAND_TIME,A.COMMAND_TYPE,A.RECEIVER_ID,A.COMM_ID,A.BOARD_ID";
  150. //SQL += " ,A.LOOP_NO,A.REPEATER_ID,A.DEVICE_ID,A.INOUT_TYPE,0 AS DATA_VALUE,'Y' AS CONFIRM_FLAG,A.COMMAND_TIME AS CONFIRM_TIME";
  151. //SQL += " ,E.EVENT_TYPE_NAME,D.DEVICE_NAME,D.DEVICE_TYPE,D.CUT_FLAG,B.DEVICE_TYPE_NAME,C.POSITION_NAME";
  152. //SQL += " FROM TB_COMMAND_LOG A";
  153. //SQL += " LEFT OUTER JOIN TB_EVENT_TYPE E ON A.COMMAND_TYPE = E.EVENT_TYPE";
  154. //SQL += " LEFT OUTER JOIN TB_DEVICE D ON A.RECEIVER_ID=D.RECEIVER_ID AND A.COMM_ID=D.COMM_ID AND A.BOARD_ID=D.BOARD_ID";
  155. //SQL += " AND A.LOOP_NO=D.LOOP_NO AND A.REPEATER_ID=D.REPEATER_ID AND A.DEVICE_ID=D.DEVICE_ID AND A.INOUT_TYPE=D.INOUT_TYPE";
  156. //SQL += " LEFT OUTER JOIN TB_DEVICE_TYPE B ON D.DEVICE_TYPE = B.DEVICE_TYPE";
  157. //SQL += " LEFT OUTER JOIN TB_POSITION_CODE C ON D.POSITION_CODE = C.POSITION_CODE";
  158. //SQL += string.Format(" WHERE A.RECEIVER_ID = {0}", RECEIVER_ID);
  159. //if (EVENT_TYPE != null)
  160. //{
  161. // if (EVENT_TYPE.Length == 2) SQL += string.Format(" AND A.COMMAND_TYPE = '{0}'", EVENT_TYPE);
  162. //}
  163. //if (EVENTTIME_YES)
  164. //{
  165. // String EVENT_TIME_FROM = String.Format("{0:yyyy}{0:MM}{0:dd}{0:HH}{0:mm}{0:ss}00000", FROM_DATE);
  166. // String EVENT_TIME_TO = String.Format("{0:yyyy}{0:MM}{0:dd}{0:HH}{0:mm}{0:ss}99999", TO_DATE);
  167. // SQL += string.Format(" AND A.COMMAND_TIME >= '{0}' AND A.COMMAND_TIME <= '{1}' ", EVENT_TIME_FROM, EVENT_TIME_TO);
  168. //}
  169. //if (COMM_ID > 0)
  170. //{
  171. // if (COMM_ID == 1) SQL += " AND (A.COMM_ID = 1 OR A.COMM_ID = 2)";
  172. // else SQL += string.Format(" AND A.COMM_ID = {0} ", COMM_ID);
  173. // if (INOUT_TYPE != null)
  174. // {
  175. // if (INOUT_TYPE != "")
  176. // {
  177. // if (COMM_ID == 1)
  178. // {
  179. // if (BOARD_ID > 0)
  180. // {
  181. // SQL += string.Format(" AND A.BOARD_ID = {0}", BOARD_ID);
  182. // if (LOOP_NO == 0 || LOOP_NO == 1)
  183. // {
  184. // SQL += string.Format(" AND A.LOOP_NO = {0}", LOOP_NO);
  185. // if (REPEATER_ID > 0)
  186. // {
  187. // SQL += string.Format(" AND A.REPEATER_ID = {0}", REPEATER_ID);
  188. // if (DEVICE_ID > 0) SQL += string.Format(" AND A.DEVICE_ID = {0}", DEVICE_ID);
  189. // }
  190. // }
  191. // }
  192. // }
  193. // else if (COMM_ID == 3)
  194. // {
  195. // if (BOARD_ID > 0)
  196. // {
  197. // SQL += string.Format(" AND A.BOARD_ID = {0}", BOARD_ID);
  198. // if (DEVICE_ID > 0) SQL += string.Format(" AND A.DEVICE_ID = {0}", DEVICE_ID);
  199. // }
  200. // }
  201. // else if (COMM_ID == 4)
  202. // {
  203. // if (DEVICE_ID > 0) SQL += string.Format(" AND A.DEVICE_ID = {0}", DEVICE_ID);
  204. // }
  205. // SQL += string.Format(" AND A.INOUT_TYPE = '{0}'", INOUT_TYPE);
  206. // }
  207. // }
  208. //}
  209. SQL += " ) A";
  210. DataSet dao = Select(SQL);
  211. DataTable dt = dao.Tables[0];
  212. if (dt.Rows.Count > 0)
  213. {
  214. DataRow dr = dt.Rows[0];
  215. rowcnt = Util.StrToInt(dr[0], 0);
  216. }
  217. //Util.UDebugMessage(string.Format("[TEST] Event_Log_SelectAllCnt Proc Time = {0} rowcnt = {1}", Environment.TickCount - tick, rowcnt), 0, 0);
  218. }
  219. catch (Exception ex)
  220. {
  221. Util.UErrorMessage(ex, 0, 0);
  222. throw ex;
  223. }
  224. return rowcnt;
  225. }
  226. //전체이벤트리스트
  227. public DataTable Event_Log_SelectAll(int RECEIVER_ID, int page, int pageSize,
  228. String EVENT_TYPE, bool EVENTTIME_YES, DateTime FROM_DATE, DateTime TO_DATE,
  229. int COMM_ID, int BOARD_ID, int LOOP_NO, int REPEATER_ID, int DEVICE_ID, String INOUT_TYPE
  230. )
  231. {
  232. DataTable dt = null;
  233. try
  234. {
  235. //String SQL = "";
  236. //SQL += string.Format(" SELECT FIRST {0} SKIP {1} A.* ", pageSize, (page - 1) * pageSize);
  237. //SQL += " FROM (";
  238. //SQL += " SELECT A.EVENT_ID,A.EVENT_TIME,A.EVENT_TYPE,A.RECEIVER_ID,A.COMM_ID,A.BOARD_ID";
  239. //SQL += " ,A.LOOP_NO,A.REPEATER_ID,A.DEVICE_ID,A.INOUT_TYPE,A.DATA_VALUE,A.CONFIRM_FLAG,A.CONFIRM_TIME";
  240. //SQL += " ,E.EVENT_TYPE_NAME,D.DEVICE_NAME,D.DEVICE_TYPE,D.CUT_FLAG,B.DEVICE_TYPE_NAME,C.POSITION_NAME";
  241. //SQL += " FROM TB_EVENT_LOG A";
  242. //SQL += " LEFT OUTER JOIN TB_EVENT_TYPE E ON A.EVENT_TYPE = E.EVENT_TYPE";
  243. //SQL += " LEFT OUTER JOIN TB_DEVICE D ON A.RECEIVER_ID=D.RECEIVER_ID AND A.COMM_ID=D.COMM_ID AND A.BOARD_ID=D.BOARD_ID";
  244. //SQL += " AND A.LOOP_NO=D.LOOP_NO AND A.REPEATER_ID=D.REPEATER_ID AND A.DEVICE_ID=D.DEVICE_ID AND A.INOUT_TYPE=D.INOUT_TYPE";
  245. //SQL += " LEFT OUTER JOIN TB_DEVICE_TYPE B ON D.DEVICE_TYPE = B.DEVICE_TYPE";
  246. //SQL += " LEFT OUTER JOIN TB_POSITION_CODE C ON D.POSITION_CODE = C.POSITION_CODE";
  247. int tick = Environment.TickCount;
  248. String SQL = "";// sdkim, 20110406, SQL optimisation
  249. SQL += string.Format(" SELECT FIRST {0} SKIP {1} A.* ", pageSize, (page - 1) * pageSize);
  250. SQL += " FROM (";
  251. SQL += " SELECT A.EVENT_ID,A.EVENT_TIME,A.EVENT_TYPE,A.RECEIVER_ID,A.COMM_ID,A.BOARD_ID";
  252. SQL += " ,A.LOOP_NO,A.REPEATER_ID,A.DEVICE_ID,A.INOUT_TYPE,A.DATA_VALUE";
  253. SQL += " FROM TB_EVENT_LOG A";
  254. SQL += string.Format(" WHERE A.RECEIVER_ID = {0}", RECEIVER_ID);
  255. if (EVENT_TYPE != null)
  256. {
  257. if (EVENT_TYPE.Length == 2) SQL += string.Format(" AND A.EVENT_TYPE = '{0}'", EVENT_TYPE);
  258. }
  259. if (EVENTTIME_YES)
  260. {
  261. String EVENT_TIME_FROM = String.Format("{0:yyyy}{0:MM}{0:dd}{0:HH}{0:mm}{0:ss}00000", FROM_DATE);
  262. String EVENT_TIME_TO = String.Format("{0:yyyy}{0:MM}{0:dd}{0:HH}{0:mm}{0:ss}99999", TO_DATE);
  263. SQL += string.Format(" AND A.EVENT_TIME >= '{0}' AND A.EVENT_TIME <= '{1}' ", EVENT_TIME_FROM, EVENT_TIME_TO);
  264. }
  265. if (COMM_ID > 0)
  266. {
  267. if (COMM_ID == 1) SQL += " AND (A.COMM_ID = 1 OR A.COMM_ID = 2)";
  268. else SQL += string.Format(" AND A.COMM_ID = {0} ", COMM_ID);
  269. if (INOUT_TYPE != null)
  270. {
  271. if (INOUT_TYPE != "")
  272. {
  273. if (COMM_ID == 1)
  274. {
  275. if (BOARD_ID > 0)
  276. {
  277. SQL += string.Format(" AND A.BOARD_ID = {0}", BOARD_ID);
  278. if (LOOP_NO == 0 || LOOP_NO == 1)
  279. {
  280. SQL += string.Format(" AND A.LOOP_NO = {0}", LOOP_NO);
  281. if (REPEATER_ID > 0)
  282. {
  283. SQL += string.Format(" AND A.REPEATER_ID = {0}", REPEATER_ID);
  284. if (DEVICE_ID > 0) SQL += string.Format(" AND A.DEVICE_ID = {0}", DEVICE_ID);
  285. }
  286. }
  287. }
  288. }
  289. else if (COMM_ID == 3)
  290. {
  291. if (BOARD_ID > 0)
  292. {
  293. SQL += string.Format(" AND A.BOARD_ID = {0}", BOARD_ID);
  294. if (DEVICE_ID > 0) SQL += string.Format(" AND A.DEVICE_ID = {0}", DEVICE_ID);
  295. }
  296. }
  297. else if (COMM_ID == 4)
  298. {
  299. if (DEVICE_ID > 0) SQL += string.Format(" AND A.DEVICE_ID = {0}", DEVICE_ID);
  300. }
  301. SQL += string.Format(" AND A.INOUT_TYPE = '{0}'", INOUT_TYPE);
  302. }
  303. }
  304. }
  305. // sdkim, 20110406, SQL optimisation
  306. // SQL += " UNION ALL";
  307. // SQL += " SELECT A.COMMAND_ID,A.COMMAND_TIME,A.COMMAND_TYPE,A.RECEIVER_ID,A.COMM_ID,A.BOARD_ID";
  308. // SQL += " ,A.LOOP_NO,A.REPEATER_ID,A.DEVICE_ID,A.INOUT_TYPE,0 AS DATA_VALUE,'Y' AS CONFIRM_FLAG,A.COMMAND_TIME AS CONFIRM_TIME";
  309. //// SQL += " ,E.EVENT_TYPE_NAME,D.DEVICE_NAME,D.DEVICE_TYPE,D.CUT_FLAG,B.DEVICE_TYPE_NAME,C.POSITION_NAME";
  310. // SQL += " FROM TB_COMMAND_LOG A";
  311. //// SQL += " LEFT OUTER JOIN TB_EVENT_TYPE E ON A.COMMAND_TYPE = E.EVENT_TYPE";
  312. //// SQL += " LEFT OUTER JOIN TB_DEVICE D ON A.RECEIVER_ID=D.RECEIVER_ID AND A.COMM_ID=D.COMM_ID AND A.BOARD_ID=D.BOARD_ID";
  313. //// SQL += " AND A.LOOP_NO=D.LOOP_NO AND A.REPEATER_ID=D.REPEATER_ID AND A.DEVICE_ID=D.DEVICE_ID AND A.INOUT_TYPE=D.INOUT_TYPE";
  314. //// SQL += " LEFT OUTER JOIN TB_DEVICE_TYPE B ON D.DEVICE_TYPE = B.DEVICE_TYPE";
  315. //// SQL += " LEFT OUTER JOIN TB_POSITION_CODE C ON D.POSITION_CODE = C.POSITION_CODE";
  316. // SQL += string.Format(" WHERE A.RECEIVER_ID = {0}", RECEIVER_ID);
  317. // if (EVENT_TYPE != null)
  318. // {
  319. // if (EVENT_TYPE.Length == 2) SQL += string.Format(" AND A.COMMAND_TYPE = '{0}'", EVENT_TYPE);
  320. // }
  321. // if (EVENTTIME_YES)
  322. // {
  323. // String EVENT_TIME_FROM = String.Format("{0:yyyy}{0:MM}{0:dd}{0:HH}{0:mm}{0:ss}00000", FROM_DATE);
  324. // String EVENT_TIME_TO = String.Format("{0:yyyy}{0:MM}{0:dd}{0:HH}{0:mm}{0:ss}99999", TO_DATE);
  325. // SQL += string.Format(" AND A.COMMAND_TIME >= '{0}' AND A.COMMAND_TIME <= '{1}' ", EVENT_TIME_FROM, EVENT_TIME_TO);
  326. // }
  327. // if (COMM_ID > 0)
  328. // {
  329. // if (COMM_ID == 1) SQL += " AND (A.COMM_ID = 1 OR A.COMM_ID = 2)";
  330. // else SQL += string.Format(" AND A.COMM_ID = {0} ", COMM_ID);
  331. // if (INOUT_TYPE != null)
  332. // {
  333. // if (INOUT_TYPE != "")
  334. // {
  335. // if (COMM_ID == 1)
  336. // {
  337. // if (BOARD_ID > 0)
  338. // {
  339. // SQL += string.Format(" AND A.BOARD_ID = {0}", BOARD_ID);
  340. // if (LOOP_NO == 0 || LOOP_NO == 1)
  341. // {
  342. // SQL += string.Format(" AND A.LOOP_NO = {0}", LOOP_NO);
  343. // if (REPEATER_ID > 0)
  344. // {
  345. // SQL += string.Format(" AND A.REPEATER_ID = {0}", REPEATER_ID);
  346. // if (DEVICE_ID > 0) SQL += string.Format(" AND A.DEVICE_ID = {0}", DEVICE_ID);
  347. // }
  348. // }
  349. // }
  350. // }
  351. // else if (COMM_ID == 3)
  352. // {
  353. // if (BOARD_ID > 0)
  354. // {
  355. // SQL += string.Format(" AND A.BOARD_ID = {0}", BOARD_ID);
  356. // if (DEVICE_ID > 0) SQL += string.Format(" AND A.DEVICE_ID = {0}", DEVICE_ID);
  357. // }
  358. // }
  359. // else if (COMM_ID == 4)
  360. // {
  361. // if (DEVICE_ID > 0) SQL += string.Format(" AND A.DEVICE_ID = {0}", DEVICE_ID);
  362. // }
  363. // SQL += string.Format(" AND A.INOUT_TYPE = '{0}'", INOUT_TYPE);
  364. // }
  365. // }
  366. // }
  367. SQL += " ) A";
  368. SQL += " ORDER BY A.EVENT_TIME DESC";
  369. //SQL += " SELECT FIRST 1000 A.*,E.EVENT_TYPE_NAME,D.DEVICE_NAME,D.DEVICE_TYPE";
  370. //SQL += " ,D.CUT_FLAG,B.DEVICE_TYPE_NAME,C.POSITION_NAME";
  371. //SQL += " FROM TB_EVENT_LOG A";
  372. //SQL += " LEFT OUTER JOIN TB_EVENT_TYPE E ON A.EVENT_TYPE = E.EVENT_TYPE";
  373. //SQL += " LEFT OUTER JOIN TB_DEVICE D ON A.RECEIVER_ID=D.RECEIVER_ID AND A.COMM_ID=D.COMM_ID AND A.BOARD_ID=D.BOARD_ID";
  374. //SQL += " AND A.LOOP_NO=D.LOOP_NO AND A.REPEATER_ID=D.REPEATER_ID AND A.DEVICE_ID=D.DEVICE_ID AND A.INOUT_TYPE=D.INOUT_TYPE";
  375. //SQL += " LEFT OUTER JOIN TB_DEVICE_TYPE B ON D.DEVICE_TYPE = B.DEVICE_TYPE";
  376. //SQL += " LEFT OUTER JOIN TB_POSITION_CODE C ON D.POSITION_CODE = C.POSITION_CODE";
  377. //SQL += " WHERE A.RECEIVER_ID = {0} ";
  378. //SQL += " ORDER BY A.EVENT_TIME DESC";
  379. DataSet dao = Select(SQL);
  380. dt = dao.Tables[0];
  381. //Util.UDebugMessage(string.Format("[TEST] -Event_Log_SelectAll Proc Time = {0}", Environment.TickCount - tick), 0, 0);
  382. }
  383. catch (Exception ex)
  384. {
  385. Util.UErrorMessage(ex, 0, 0);
  386. throw ex;
  387. }
  388. return dt;
  389. }
  390. //이벤트ID로 검색 (사용안함)
  391. public DataTable Event_Log_Select(String EVENT_ID)
  392. {
  393. DataTable dt = null;
  394. try
  395. {
  396. //String SQL = "";
  397. //SQL += " SELECT A.*,D.DEVICE_NAME,D.DEVICE_TYPE";
  398. //SQL += " ,D.CUT_FLAG,B.DEVICE_TYPE_NAME,C.POSITION_NAME,E.EVENT_TYPE_NAME";
  399. //SQL += " FROM TB_EVENT_LOG A";
  400. //SQL += " LEFT OUTER JOIN TB_DEVICE D ON A.RECEIVER_ID=D.RECEIVER_ID AND A.COMM_ID=D.COMM_ID AND A.BOARD_ID=D.BOARD_ID";
  401. //SQL += " AND A.LOOP_NO=D.LOOP_NO AND A.REPEATER_ID=D.REPEATER_ID AND A.DEVICE_ID=D.DEVICE_ID AND A.INOUT_TYPE=D.INOUT_TYPE";
  402. //SQL += " LEFT OUTER JOIN TB_DEVICE_TYPE B ON D.DEVICE_TYPE = B.DEVICE_TYPE";
  403. //SQL += " LEFT OUTER JOIN TB_POSITION_CODE C ON D.POSITION_CODE = C.POSITION_CODE";
  404. //SQL += " LEFT OUTER JOIN TB_EVENT_TYPE E ON A.EVENT_TYPE = E.EVENT_TYPE";
  405. //SQL += " WHERE A.EVENT_ID = '{0}'";
  406. //SQL += " ORDER BY A.EVENT_TIME DESC";
  407. int tick = Environment.TickCount;
  408. String SQL = ""; // sdkim, 20110406, SQL optimisation with J.S.W
  409. SQL += " SELECT A.*, B.EVENT_TYPE_NAME";
  410. SQL += " FROM TB_EVENT_LOG A, TB_EVENT_TYPE B";
  411. SQL += " WHERE A.EVENT_ID = '{0}' and A.EVENT_TYPE = B.EVENT_TYPE";
  412. SQL += " ORDER BY A.EVENT_TIME DESC";
  413. SQL = String.Format(SQL, EVENT_ID);
  414. DataSet dao = Select(SQL);
  415. //
  416. dt = dao.Tables[0];
  417. //Util.UDebugMessage(string.Format("[TEST] -Event_Log_Select Proc Time = {0}", Environment.TickCount - tick), 0, 0);
  418. }
  419. catch (Exception ex)
  420. {
  421. Util.UErrorMessage(ex, 0, 0);
  422. throw ex;
  423. }
  424. return dt;
  425. }
  426. //회로 이벤트-확인받지 못한 이벤트
  427. public DataTable Event_Log_Select_NotConfirm(String EVENT_TYPE, int RECEIVER_ID, int COMM_ID, int BOARD_ID, int LOOP_NO,
  428. int REPEATER_ID, int DEVICE_ID, String INOUT_TYPE)
  429. {
  430. DataTable dt = null;
  431. try
  432. {
  433. String SQL = "";
  434. SQL += " SELECT A.EVENT_TYPE,A.EVENT_TIME,A.DEVICE_ID,D.DEVICE_NAME,D.DEVICE_TYPE,A.INOUT_TYPE";
  435. SQL += " ,D.CUT_FLAG,B.DEVICE_TYPE_NAME,C.POSITION_NAME";
  436. SQL += " ,A.DATA_VALUE,A.EVENT_REMARK";
  437. SQL += " FROM TB_EVENT_LOG A";
  438. SQL += " LEFT OUTER JOIN TB_DEVICE D ON A.RECEIVER_ID=D.RECEIVER_ID AND A.COMM_ID=D.COMM_ID AND A.BOARD_ID=D.BOARD_ID";
  439. SQL += " AND A.LOOP_NO=D.LOOP_NO AND A.REPEATER_ID=D.REPEATER_ID AND A.DEVICE_ID=D.DEVICE_ID AND A.INOUT_TYPE=D.INOUT_TYPE";
  440. SQL += " LEFT OUTER JOIN TB_DEVICE_TYPE B ON D.DEVICE_TYPE = B.DEVICE_TYPE";
  441. SQL += " LEFT OUTER JOIN TB_POSITION_CODE C ON D.POSITION_CODE = C.POSITION_CODE";
  442. SQL += " WHERE A.EVENT_TYPE = '{0}' AND A.RECEIVER_ID = {1} AND A.COMM_ID = {2} AND A.BOARD_ID = {3} AND A.LOOP_NO = {4} AND A.REPEATER_ID = {5}";
  443. SQL += " AND A.DEVICE_ID = {6} AND A.INOUT_TYPE = '{7}' ";
  444. SQL += " ORDER BY A.EVENT_TIME DESC";
  445. SQL = String.Format(SQL, EVENT_TYPE, RECEIVER_ID, COMM_ID, BOARD_ID, LOOP_NO, REPEATER_ID, DEVICE_ID, INOUT_TYPE);
  446. DataSet dao = Select(SQL);
  447. dt = dao.Tables[0];
  448. }
  449. catch (Exception ex)
  450. {
  451. Util.UErrorMessage(ex, 0, 0);
  452. throw ex;
  453. }
  454. return dt;
  455. }
  456. //회로 이벤트
  457. public DataTable Event_Log_Select(String EVENT_TYPE, int RECEIVER_ID, int COMM_ID, int BOARD_ID, int LOOP_NO,
  458. int REPEATER_ID, int DEVICE_ID, String INOUT_TYPE)
  459. {
  460. DataTable dt = null;
  461. try
  462. {
  463. String SQL = "";
  464. SQL += " SELECT A.EVENT_TYPE,A.EVENT_TIME,A.DEVICE_ID,D.DEVICE_NAME,D.DEVICE_TYPE,A.INOUT_TYPE";
  465. SQL += " ,D.CUT_FLAG,B.DEVICE_TYPE_NAME,C.POSITION_NAME";
  466. SQL += " ,A.DATA_VALUE,A.EVENT_REMARK";
  467. SQL += " FROM TB_EVENT_LOG A";
  468. SQL += " LEFT OUTER JOIN TB_DEVICE D ON A.RECEIVER_ID=D.RECEIVER_ID AND A.COMM_ID=D.COMM_ID AND A.BOARD_ID=D.BOARD_ID";
  469. SQL += " AND A.LOOP_NO=D.LOOP_NO AND A.REPEATER_ID=D.REPEATER_ID AND A.DEVICE_ID=D.DEVICE_ID AND A.INOUT_TYPE=D.INOUT_TYPE";
  470. SQL += " LEFT OUTER JOIN TB_DEVICE_TYPE B ON D.DEVICE_TYPE = B.DEVICE_TYPE";
  471. SQL += " LEFT OUTER JOIN TB_POSITION_CODE C ON D.POSITION_CODE = C.POSITION_CODE";
  472. SQL += " WHERE A.EVENT_TYPE = '{0}' AND A.RECEIVER_ID = {1} AND A.COMM_ID = {2} AND A.BOARD_ID = {3} AND A.LOOP_NO = {4} AND A.REPEATER_ID = {5}";
  473. SQL += " AND A.DEVICE_ID = {6} AND A.INOUT_TYPE = '{7}'";
  474. SQL += " ORDER BY A.EVENT_TIME DESC";
  475. SQL = String.Format(SQL, EVENT_TYPE, RECEIVER_ID, COMM_ID, BOARD_ID, LOOP_NO, REPEATER_ID, DEVICE_ID, INOUT_TYPE);
  476. DataSet dao = Select(SQL);
  477. dt = dao.Tables[0];
  478. }
  479. catch (Exception ex)
  480. {
  481. Util.UErrorMessage(ex, 0, 0);
  482. throw ex;
  483. }
  484. return dt;
  485. }
  486. //이벤트 확인저장
  487. public void Event_Log_UPDATE(QUERYMode cmd, String EVENT_ID, String CONFIRM_FLAG, String CONFIRM_REMARK)
  488. {
  489. try
  490. {
  491. DataTable dt = Event_Log_Select(EVENT_ID);
  492. if (cmd == QUERYMode.insert || cmd == QUERYMode.update || cmd == QUERYMode.InsertAndUpdate)
  493. {
  494. String SQL = "";
  495. if (dt.Rows.Count > 0)
  496. {
  497. SQL = "UPDATE TB_EVENT_LOG SET CONFIRM_FLAG = '{1}'";
  498. SQL += ", CONFIRM_REMARK = '{2}'";
  499. SQL += ", CONFIRM_PERSON = 'FPER', CONFIRM_TIME = current_timestamp";
  500. SQL += " WHERE EVENT_ID = '{0}'";
  501. SQL = String.Format(SQL, EVENT_ID, CONFIRM_FLAG, CONFIRM_REMARK);
  502. ExecuteNonQuery(SQL);
  503. }
  504. else
  505. {
  506. //SQL = "INSERT INTO TB_EVENT_LOG ( ";
  507. //SQL += " EVENT_TIME,EVENT_TYPE,RECEIVER_ID,COMM_ID,BOARD_ID,LOOP_NO,REPEATER_ID,DEVICE_ID,INOUT_TYPE,DATA_VALUE,EVENT_REMARK";
  508. //SQL += " ,CONFIRM_FLAG,CONFIRM_REMARK,CONFIRM_PERSON,CONFIRM_TIME";
  509. //SQL += ") VALUES ({0},1,{1},{2},{3},'{4}','{5}','{6}','N','FPER',current_timestamp)";
  510. //SQL = String.Format(SQL, RECEIVER_ID, BOARD_ID, LOOP_NO, REPEATER_ID, REPEATER_NAME, REPEATER_TYPE, USE_FLAG);
  511. }
  512. }
  513. else if (cmd == QUERYMode.delete)
  514. {
  515. if (dt.Rows.Count < 1)
  516. {
  517. throw new Exception("해당 자료를 찾을수 없습니다.");
  518. }
  519. String SQL = "DELETE FROM TB_EVENT_LOG ";
  520. SQL += " WHERE EVENT_ID = '{0}'";
  521. SQL = String.Format(SQL, EVENT_ID);
  522. ExecuteNonQuery(SQL);
  523. }
  524. }
  525. catch (Exception ex)
  526. {
  527. Util.UErrorMessage(ex, 0, 0);
  528. throw ex;
  529. }
  530. }
  531. //이벤트 로그 (사용안함)
  532. public string Event_TypeName_Select(String EVENT_TYPE)
  533. {
  534. string type_name = "";
  535. try
  536. {
  537. String SQL = "";
  538. SQL += " SELECT EVENT_TYPE,EVENT_TYPE_NAME";
  539. SQL += " FROM TB_EVENT_TYPE ";
  540. SQL += " WHERE EVENT_TYPE = '{0}' ";
  541. SQL = String.Format(SQL, EVENT_TYPE);
  542. DataSet dao = Select(SQL);
  543. DataTable dt = dao.Tables[0];
  544. if (dt.Rows.Count > 0)
  545. {
  546. DataRow dr = dt.Rows[0];
  547. type_name = dr["EVENT_TYPE_NAME"].ToString();
  548. }
  549. }
  550. catch (Exception ex)
  551. {
  552. Util.UErrorMessage(ex, 0, 0);
  553. throw ex;
  554. }
  555. return type_name;
  556. }
  557. //이벤트 타입리스트
  558. public DataTable Event_Type_Select(String EVENT_TYPE, MDIParent mdi) // cyim 2015.8.4 수신반을 위한 static 클래스 정리
  559. {
  560. DataTable dt = null;
  561. try
  562. {
  563. //String SQL = "";
  564. //if (EVENT_TYPE == null)
  565. //{
  566. // SQL += " SELECT EVENT_TYPE,EVENT_TYPE_NAME";
  567. // SQL += " FROM TB_EVENT_TYPE ";
  568. //}
  569. //else
  570. //{
  571. // SQL += " SELECT EVENT_TYPE,EVENT_TYPE_NAME";
  572. // SQL += " FROM TB_EVENT_TYPE ";
  573. // SQL += " WHERE EVENT_TYPE = '{0}' ";
  574. // SQL = String.Format(SQL, EVENT_TYPE);
  575. //}
  576. //DataSet dao = Select(SQL);
  577. //dt = dao.Tables[0];
  578. // cyim 2013.8.8 이벤트 타입명 해쉬테이블로 변환 (상단 모두 주석 처리)
  579. dt = mdi.EventTypeName.DataTable_EventType; // cyim 2015.8.4 수신반을 위한 static 클래스 정리
  580. }
  581. catch (Exception ex)
  582. {
  583. Util.UErrorMessage(ex, 0, 0);
  584. throw ex;
  585. }
  586. return dt;
  587. }
  588. public DataTable Event_Type_Table_Select(String EVENT_TYPE, MDIParent mdi) // cyim 2015.8.4 수신반을 위한 static 클래스 정리
  589. {
  590. // sdkim, 20110406, Event_Type Table Data Pre Load
  591. DataTable dt = null;
  592. try
  593. {
  594. //String SQL = "";
  595. //if (EVENT_TYPE == null)
  596. //{
  597. // SQL += " SELECT EVENT_TYPE,EVENT_TYPE_NAME";
  598. // SQL += " FROM TB_EVENT_TYPE ";
  599. //}
  600. //else
  601. //{
  602. // SQL += " SELECT EVENT_TYPE,EVENT_TYPE_NAME";
  603. // SQL += " FROM TB_EVENT_TYPE ";
  604. // SQL += " WHERE EVENT_TYPE = '{0}' ";
  605. // SQL = String.Format(SQL, EVENT_TYPE);
  606. //}
  607. //DataSet dao = Select(SQL);
  608. //dt = dao.Tables[0];
  609. // cyim 2013.8.8 이벤트 타입명 해쉬테이블로 변환 (상단 모두 주석 처리)
  610. dt = mdi.EventTypeName.DataTable_EventType; // cyim 2015.8.4 수신반을 위한 static 클래스 정리
  611. return dt;
  612. }
  613. catch (Exception ex)
  614. {
  615. Util.UErrorMessage(ex, 0, 0);
  616. return null;//throw ex;
  617. }
  618. }
  619. //command 조회
  620. public DataTable Command_Log_Select(String COMMAND_ID)
  621. {
  622. DataTable dt = null;
  623. try
  624. {
  625. String SQL = "";
  626. SQL += " SELECT A.*,D.DEVICE_NAME,D.DEVICE_TYPE";
  627. SQL += " ,D.CUT_FLAG,B.DEVICE_TYPE_NAME,C.POSITION_NAME";
  628. SQL += " FROM TB_COMMAND_LOG A";
  629. SQL += " LEFT OUTER JOIN TB_DEVICE D ON A.RECEIVER_ID=D.RECEIVER_ID AND A.COMM_ID=D.COMM_ID AND A.BOARD_ID=D.BOARD_ID";
  630. SQL += " AND A.LOOP_NO=D.LOOP_NO AND A.REPEATER_ID=D.REPEATER_ID AND A.DEVICE_ID=D.DEVICE_ID AND A.INOUT_TYPE=D.INOUT_TYPE";
  631. SQL += " LEFT OUTER JOIN TB_DEVICE_TYPE B ON D.DEVICE_TYPE = B.DEVICE_TYPE";
  632. SQL += " LEFT OUTER JOIN TB_POSITION_CODE C ON D.POSITION_CODE = C.POSITION_CODE";
  633. SQL += " WHERE A.COMMAND_ID = '{0}' ";
  634. SQL += " ORDER BY A.COMMAND_TIME DESC";
  635. SQL = String.Format(SQL, COMMAND_ID);
  636. DataSet dao = Select(SQL);
  637. dt = dao.Tables[0];
  638. }
  639. catch (Exception ex)
  640. {
  641. Util.UErrorMessage(ex, 0, 0);
  642. throw ex;
  643. }
  644. return dt;
  645. }
  646. //command 확인저장
  647. public void Command_Log_UPDATE(QUERYMode cmd, String COMMAND_ID, String COMMAND_TIME, String COMMAND_TYPE, int RECEIVER_ID
  648. , int COMM_ID, int BOARD_ID, int LOOP_NO, int REPEATER_ID, int DEVICE_ID, String INOUT_TYPE, String COMMAND_REMARK)
  649. {
  650. try
  651. {
  652. // cyim 2015.2.9 시작시 로그를 저장하는 경우 DB 부하가 걸림 제거
  653. // COMMAND_ID 는 날짜와시간을 조합한 아이디값이다. 중복될 확률은 미비하므로, 하위 루틴은 로그를 저장시에 부하가 걸릴 확률이 높다.
  654. //DataTable dt = Command_Log_Select(COMMAND_ID);
  655. if (cmd == QUERYMode.insert || cmd == QUERYMode.update || cmd == QUERYMode.InsertAndUpdate)
  656. {
  657. String SQL = "";
  658. // cyim 2015.2.9 시작시 로그를 저장하는 경우 DB 부하가 걸림 제거
  659. //if (dt.Rows.Count > 0)
  660. //{
  661. // //SQL = "UPDATE TB_COMMAND_LOG SET CONFIRM_FLAG = '{1}'";
  662. // //SQL += ", CONFIRM_REMARK = '{2}'";
  663. // //SQL += ", CONFIRM_PERSON = 'FPER', CONFIRM_TIME = current_timestamp";
  664. // //SQL += " WHERE COMMAND_ID = '{0}'";
  665. // //SQL = String.Format(SQL, COMMAND_ID, CONFIRM_REMARK);
  666. // //ExecuteNonQuery(SQL);
  667. //}
  668. //else
  669. {
  670. SQL = "INSERT INTO TB_COMMAND_LOG ( ";
  671. SQL += " COMMAND_ID,COMMAND_TIME,COMMAND_TYPE,RECEIVER_ID,COMM_ID,BOARD_ID,LOOP_NO,";
  672. SQL += " REPEATER_ID,DEVICE_ID,INOUT_TYPE,COMMAND_REMARK";
  673. SQL += ") VALUES ('{0}','{1}','{2}',{3},{4},{5},{6},{7},{8},'{9}','{10}')";
  674. SQL = String.Format(SQL,
  675. COMMAND_ID,
  676. COMMAND_TIME,
  677. COMMAND_TYPE,
  678. RECEIVER_ID,
  679. COMM_ID,
  680. BOARD_ID,
  681. LOOP_NO,
  682. REPEATER_ID,
  683. DEVICE_ID,
  684. INOUT_TYPE,
  685. COMMAND_REMARK);
  686. ExecuteNonQuery(SQL);
  687. }
  688. }
  689. else if (cmd == QUERYMode.delete)
  690. {
  691. // cyim 2015.2.9 시작시 로그를 저장하는 경우 DB 부하가 걸림 제거
  692. //if (dt.Rows.Count < 1)
  693. //{
  694. // throw new Exception("해당 자료를 찾을수 없습니다.");
  695. //}
  696. String SQL = "DELETE FROM TB_COMMAND_LOG ";
  697. SQL += " WHERE COMMAND_ID = '{0}'";
  698. SQL = String.Format(SQL, COMMAND_ID);
  699. ExecuteNonQuery(SQL);
  700. }
  701. }
  702. catch (Exception ex)
  703. {
  704. Util.UErrorMessage(ex, 0, 0);
  705. throw ex;
  706. }
  707. }
  708. public int GetEventLogCount(int receiverID)
  709. {
  710. int ret = -1;
  711. try
  712. {
  713. DataTable dt = null;
  714. String SQL = "";
  715. SQL += "SELECT COUNT (*)";
  716. SQL += " FROM TB_EVENT_LOG";
  717. SQL += " WHERE RECEIVER_ID = {0} ";
  718. SQL = String.Format(SQL, receiverID);
  719. DataSet dao = Select(SQL);
  720. dt = dao.Tables[0];
  721. if (dt.Rows.Count > 0)
  722. {
  723. DataRow dr = dt.Rows[0];
  724. }
  725. for (int i = 0; i < dt.Rows.Count; i++)
  726. {
  727. DataRow dr = dt.Rows[i];
  728. ret = Int32.Parse(dr["COUNT"].ToString());
  729. }
  730. }
  731. catch (Exception ex)
  732. {
  733. Util.UErrorMessage(ex, 0, 0);
  734. throw ex;
  735. }
  736. return ret;
  737. }
  738. public string[] GetEventLogData(int receiverID, int count)
  739. {
  740. string[] ret = new string[count];
  741. try
  742. {
  743. DataTable dt = null;
  744. String SQL = "";
  745. SQL += "SELECT FIRST {1} EVENT_ID ";
  746. SQL += " FROM TB_EVENT_LOG";
  747. SQL += " WHERE RECEIVER_ID = {0} ";
  748. SQL += " ORDER BY EVENT_TIME ";
  749. SQL = String.Format(SQL, receiverID, count);
  750. DataSet dao = Select(SQL);
  751. dt = dao.Tables[0];
  752. for (int i = 0; i < dt.Rows.Count; i++)
  753. {
  754. DataRow dr = dt.Rows[i];
  755. ret[i] = dr["EVENT_ID"].ToString();
  756. }
  757. }
  758. catch (Exception ex)
  759. {
  760. Util.UErrorMessage(ex, 0, 0);
  761. throw ex;
  762. }
  763. return ret;
  764. }
  765. public string GetEventLogTime(int receiverID, int count)
  766. {
  767. string ret = "";
  768. try
  769. {
  770. DataTable dt = null;
  771. String SQL = "";
  772. SQL += "SELECT FIRST 1 SKIP {1} EVENT_TIME ";
  773. SQL += " FROM TB_EVENT_LOG";
  774. SQL += " WHERE RECEIVER_ID = {0} ";
  775. SQL += " ORDER BY EVENT_TIME DESC";
  776. SQL = String.Format(SQL, receiverID, count);
  777. DataSet dao = Select(SQL);
  778. dt = dao.Tables[0];
  779. if (dt.Rows.Count > 0)
  780. {
  781. DataRow dr = dt.Rows[0];
  782. ret = dr["EVENT_TIME"].ToString();
  783. }
  784. }
  785. catch (Exception ex)
  786. {
  787. Util.UErrorMessage(ex, 0, 0);
  788. throw ex;
  789. }
  790. return ret;
  791. }
  792. public void AddEventLogData(EventLogInfo elog)
  793. {
  794. try
  795. {
  796. string event_time = String.Format("{0:yyyy}{0:MM}{0:dd}{0:HH}{0:mm}{0:ss}00000", elog.EventTime);
  797. string SQL = "";
  798. SQL = "INSERT INTO TB_EVENT_LOG ( ";
  799. SQL += " EVENT_ID,EVENT_TIME,EVENT_TYPE,RECEIVER_ID,COMM_ID,BOARD_ID,LOOP_NO,REPEATER_ID,DEVICE_ID,INOUT_TYPE,DATA_VALUE,SEND_FLAG";
  800. SQL += " ) VALUES ('{0}','{1}','{2}',{3},{4},{5},{6},{7},{8},'{9}',{10},'N')";
  801. SQL = String.Format(SQL, elog.EventId, event_time, elog.EventType, elog.ReceiverID, elog.CommId, elog.BoardNo, elog.LoopNo, elog.RepeaterNo, elog.DeviceId, elog.InoutType, elog.DataValue);
  802. ExecuteNonQuery(SQL);
  803. }
  804. catch (Exception ex)
  805. {
  806. Util.UErrorMessage(ex, 0, 0);
  807. throw ex;
  808. }
  809. }
  810. public void DeleteEventLogDataTime(string eventTime)
  811. {
  812. try
  813. {
  814. if (eventTime != "")
  815. {
  816. String SQL = "DELETE FROM TB_EVENT_LOG ";
  817. SQL += " WHERE EVENT_TIME < '{0}'";
  818. SQL = String.Format(SQL, eventTime);
  819. ExecuteNonQuery(SQL);
  820. }
  821. }
  822. catch (Exception ex)
  823. {
  824. Util.UErrorMessage(ex, 0, 0);
  825. throw ex;
  826. }
  827. }
  828. // cyim 2015.2.13 로그는 2년 유지
  829. public void DeleteCommandLogData(string COMMAND_TIME)
  830. {
  831. try
  832. {
  833. if (COMMAND_TIME != "")
  834. {
  835. String SQL = "DELETE FROM TB_COMMAND_LOG ";
  836. SQL += " WHERE COMMAND_TIME < '{0}'";
  837. SQL = String.Format(SQL, COMMAND_TIME);
  838. ExecuteNonQuery(SQL);
  839. }
  840. }
  841. catch (Exception ex)
  842. {
  843. Util.UErrorMessage(ex, 0, 0);
  844. throw ex;
  845. }
  846. }
  847. public void DeleteEventLogData(string eventID)
  848. {
  849. try
  850. {
  851. if (eventID != "")
  852. {
  853. String SQL = "DELETE FROM TB_EVENT_LOG ";
  854. SQL += " WHERE EVENT_ID = '{0}'";
  855. SQL = String.Format(SQL, eventID);
  856. ExecuteNonQuery(SQL);
  857. }
  858. }
  859. catch (Exception ex)
  860. {
  861. Util.UErrorMessage(ex, 0, 0);
  862. throw ex;
  863. }
  864. }
  865. public void DeleteEventLogData(string[] eventID)
  866. {
  867. try
  868. {
  869. if (eventID.Length > 0)
  870. {
  871. String SQL = "DELETE FROM TB_EVENT_LOG ";
  872. bool first = true;
  873. foreach (string eID in eventID)
  874. {
  875. if (first)
  876. {
  877. if (eID != "")
  878. {
  879. SQL += string.Format(" WHERE EVENT_ID = '{0}'", eID);
  880. first = false;
  881. }
  882. }
  883. else
  884. {
  885. if (eID != "")
  886. {
  887. SQL += string.Format(" AND EVENT_ID = '{0}'", eID);
  888. }
  889. }
  890. }
  891. ExecuteNonQuery(SQL);
  892. }
  893. }
  894. catch (Exception ex)
  895. {
  896. Util.UErrorMessage(ex, 0, 0);
  897. throw ex;
  898. }
  899. }
  900. //회로조회
  901. public DataTable Device_Select(int RECEIVER_ID, int COMM_ID, int BOARD_ID, int LOOP_NO, int REPEATER_ID, int DEVICE_ID, String INOUT_TYPE)
  902. {
  903. DataTable dt = null;
  904. try
  905. {
  906. String SQL = "";
  907. SQL += " SELECT A.DEVICE_ID,A.DEVICE_NAME,A.DEVICE_TYPE,A.INOUT_TYPE,A.CUT_FLAG,B.DEVICE_TYPE_NAME,A.POSITION_CODE,C.POSITION_NAME";
  908. SQL += " FROM TB_DEVICE A ";
  909. SQL += " LEFT OUTER JOIN TB_DEVICE_TYPE B ON A.DEVICE_TYPE = B.DEVICE_TYPE";
  910. SQL += " LEFT OUTER JOIN TB_POSITION_CODE C ON A.POSITION_CODE = C.POSITION_CODE";
  911. SQL += " WHERE A.RECEIVER_ID = {0} AND A.COMM_ID = {1} AND A.BOARD_ID = {2} AND A.LOOP_NO = {3} AND A.REPEATER_ID = {4}";
  912. SQL += " AND A.DEVICE_ID = {5} AND A.INOUT_TYPE = '{6}'";
  913. SQL = String.Format(SQL, RECEIVER_ID, COMM_ID, BOARD_ID, LOOP_NO, REPEATER_ID, DEVICE_ID, INOUT_TYPE);
  914. DataSet dao = Select(SQL);
  915. dt = dao.Tables[0];
  916. }
  917. catch (Exception ex)
  918. {
  919. Util.UErrorMessage(ex, 0, 0);
  920. throw ex;
  921. }
  922. return dt;
  923. }
  924. public DataTable SelectLastLog(int receviceID, int commID, int boardID, int loopNO, int repeaterID, int deviceID, string inoutType, string eventtype)
  925. {
  926. DataTable dt = null;
  927. try
  928. {
  929. String SQL = "";
  930. SQL += "SELECT FIRST 1 EVENT_ID,EVENT_TIME,EVENT_TYPE,RECEIVER_ID,COMM_ID,BOARD_ID,LOOP_NO,REPEATER_ID,DEVICE_ID,INOUT_TYPE,DATA_VALUE,EVENT_REMARK,SEND_FLAG";
  931. SQL += " FROM TB_EVENT_LOG";
  932. SQL += " WHERE EVENT_TYPE = '{0}' AND RECEIVER_ID = {1} AND COMM_ID = {2} AND BOARD_ID = {3} AND LOOP_NO = {4} AND REPEATER_ID = {5} AND DEVICE_ID = {6} AND INOUT_TYPE = '{7}'";
  933. SQL += " ORDER BY EVENT_TIME DESC";
  934. SQL = String.Format(SQL, eventtype, receviceID, commID, boardID, loopNO, repeaterID, deviceID, inoutType);
  935. DataSet dao = Select(SQL);
  936. dt = dao.Tables[0];
  937. }
  938. catch (Exception ex)
  939. {
  940. Util.UErrorMessage(ex, 0, 0);
  941. throw ex;
  942. }
  943. return dt;
  944. }
  945. public DataTable SelectLogEvnetID(string EventID)
  946. {
  947. DataTable dt = null;
  948. try
  949. {
  950. String SQL = "";
  951. SQL += "SELECT EVENT_ID,EVENT_TIME,EVENT_TYPE,RECEIVER_ID,COMM_ID,BOARD_ID,LOOP_NO,REPEATER_ID,DEVICE_ID,INOUT_TYPE,DATA_VALUE,EVENT_REMARK,SEND_FLAG";
  952. SQL += " FROM TB_EVENT_LOG";
  953. SQL += " WHERE EVENT_ID = '{0}' ";
  954. SQL = String.Format(SQL, EventID);
  955. DataSet dao = Select(SQL);
  956. dt = dao.Tables[0];
  957. }
  958. catch (Exception ex)
  959. {
  960. Util.UErrorMessage(ex, 0, 0);
  961. throw ex;
  962. }
  963. return dt;
  964. }
  965. public void AddEventLogData(string EVENT_ID, string EVENT_TIME, string EVENT_TYPE, int RECEIVER_ID, int COMM_ID, int BOARD_ID, int LOOP_NO, int REPEATER_ID, int DEVICE_ID, string INOUT_TYPE, int DATA_VALUE, string EVENT_REMARK, string SEND_FLAG)
  966. {
  967. try
  968. {
  969. string SQL = "";
  970. SQL = "INSERT INTO TB_EVENT_LOG ( ";
  971. SQL += " EVENT_ID,EVENT_TIME,EVENT_TYPE,RECEIVER_ID,COMM_ID,BOARD_ID,LOOP_NO,REPEATER_ID,DEVICE_ID,INOUT_TYPE,DATA_VALUE,EVENT_REMARK,SEND_FLAG";
  972. SQL += " ) VALUES ('{0}','{1}','{2}',{3},{4},{5},{6},{7},{8},'{9}',{10},'{11}','{12}')";
  973. SQL = String.Format(SQL, EVENT_ID, EVENT_TIME, EVENT_TYPE, RECEIVER_ID, COMM_ID, BOARD_ID, LOOP_NO, REPEATER_ID, DEVICE_ID, INOUT_TYPE, DATA_VALUE, EVENT_REMARK, SEND_FLAG);
  974. ExecuteNonQuery(SQL);
  975. }
  976. catch (Exception ex)
  977. {
  978. Util.UErrorMessage(ex, 0, 0);
  979. throw ex;
  980. }
  981. }
  982. public DataTable SelectSQL(string SQL)
  983. {
  984. DataTable dt = null;
  985. try
  986. {
  987. DataSet dao = Select(SQL);
  988. dt = dao.Tables[0];
  989. }
  990. catch (Exception ex)
  991. {
  992. Util.UErrorMessage(ex, 0, 0);
  993. throw ex;
  994. }
  995. return dt;
  996. }
  997. }
  998. public class LogType
  999. {
  1000. public bool cnt = false;
  1001. public bool time = false;
  1002. public DateTime startTime;
  1003. public DateTime endTime;
  1004. public bool eventTypeDetail = false;
  1005. public string eventTypeD = "";
  1006. public bool fireOn = false;
  1007. //public bool fireOff = false;
  1008. public bool devOn = false;
  1009. //public bool devOff = false;
  1010. //public bool errorOn = false;
  1011. //public bool errorOff = false;
  1012. public bool controlLog = false;
  1013. public bool boardError = false;
  1014. public bool repeaterError = false;
  1015. public bool devError = false;
  1016. public int startIndex = 0;
  1017. public int endIndex = 0;
  1018. public bool location = false;
  1019. private string SqlBase()
  1020. {
  1021. string sql = "";
  1022. sql += "EVENT_ID,EVENT_TIME,EVENT_TYPE,RECEIVER_ID,COMM_ID,BOARD_ID,LOOP_NO,REPEATER_ID,DEVICE_ID,INOUT_TYPE,DATA_VALUE,EVENT_REMARK ";
  1023. sql += "FROM TB_EVENT_LOG ";
  1024. sql += "WHERE RECEIVER_ID > 0 ";
  1025. return sql;
  1026. }
  1027. private string SqlTime()
  1028. {
  1029. string sql = "";
  1030. if (time)
  1031. {
  1032. String strStartTime = String.Format("{0:yyyy}{0:MM}{0:dd}{0:HH}{0:mm}{0:ss}00000", startTime);
  1033. String strEndTime = String.Format("{0:yyyy}{0:MM}{0:dd}{0:HH}{0:mm}{0:ss}99999", endTime);
  1034. sql += string.Format(" AND EVENT_TIME >= '{0}' AND EVENT_TIME <= '{1}' ", strStartTime, strEndTime);
  1035. }
  1036. return sql;
  1037. }
  1038. // cyim 2013.10.10 : 1000건 조회 속도 개선 작업
  1039. //private string COMMANDSqlTime(DateTime StartTime, DateTime EndTime)
  1040. //{
  1041. // string sql = "";
  1042. // if (time)
  1043. // {
  1044. // String strStartTime = String.Format("{0:yyyy}{0:MM}{0:dd}{0:HH}{0:mm}{0:ss}00000", StartTime);
  1045. // String strEndTime = String.Format("{0:yyyy}{0:MM}{0:dd}{0:HH}{0:mm}{0:ss}99999", EndTime);
  1046. // sql += string.Format(" AND COMMAND_TIME >= '{0}' AND COMMAND_TIME <= '{1}' ", strStartTime, strEndTime);
  1047. // }
  1048. // return sql;
  1049. //}
  1050. private string SqlTime(DateTime StartTime, DateTime EndTime)
  1051. {
  1052. string sql = "";
  1053. if (time)
  1054. {
  1055. String strStartTime = String.Format("{0:yyyy}{0:MM}{0:dd}{0:HH}{0:mm}{0:ss}00000", StartTime);
  1056. String strEndTime = String.Format("{0:yyyy}{0:MM}{0:dd}{0:HH}{0:mm}{0:ss}99999", EndTime);
  1057. sql += string.Format(" AND EVENT_TIME >= '{0}' AND EVENT_TIME <= '{1}' ", strStartTime, strEndTime);
  1058. }
  1059. return sql;
  1060. }
  1061. private string SqlCount()
  1062. {
  1063. string sql = "";
  1064. sql += "COUNT(*) ";
  1065. sql += "FROM TB_EVENT_LOG ";
  1066. sql += "WHERE RECEIVER_ID > 0 ";
  1067. return sql;
  1068. }
  1069. private string SqlIndex()
  1070. {
  1071. string sql = "";
  1072. if (endIndex != startIndex)
  1073. {
  1074. sql += string.Format("FIRST {0} ", endIndex, startIndex);
  1075. }
  1076. return sql;
  1077. }
  1078. public int receiverID = -1;
  1079. public int commID = -1; // 1,2 통신보드, , 3 IO 보드 4 키패드
  1080. public int boardID = -1;// 0 : 키패드, 1~16 IO보드, 통신보드
  1081. public int loopNo = -1; // 0, 1
  1082. public int repeaterID = -1; // 1~127
  1083. public int deviceID = -1; // 1~4
  1084. public string ioType = "A"; // A: 전체, C 통신(수신기) , B 보드, L 루프, R 중계기, D 회로, I 입력 , O 출력 , E:계통 레벨 이외 전부
  1085. private string SqlLocation()
  1086. {
  1087. string sql = "";
  1088. if (this.location)
  1089. {
  1090. if (receiverID > 0)
  1091. {
  1092. sql += string.Format("AND RECEIVER_ID = {0} ", receiverID);
  1093. }
  1094. if (commID > 0)
  1095. {
  1096. sql += string.Format("AND COMM_ID = {0} ", commID);
  1097. }
  1098. if (boardID > 0)
  1099. {
  1100. sql += string.Format("AND BOARD_ID = {0} ", boardID);
  1101. }
  1102. if (loopNo > -1)
  1103. {
  1104. sql += string.Format("AND LOOP_NO = {0} ", loopNo);
  1105. }
  1106. if (repeaterID > 0)
  1107. {
  1108. sql += string.Format("AND REPEATER_ID = {0} ", repeaterID);
  1109. }
  1110. if (deviceID > 0)
  1111. {
  1112. sql += string.Format("AND DEVICE_ID = {0} ", deviceID);
  1113. }
  1114. if (ioType != "A")
  1115. {
  1116. switch (ioType)
  1117. {
  1118. case "L":
  1119. sql += string.Format("AND ( INOUT_TYPE = 'B' OR INOUT_TYPE = 'L') ");
  1120. break;
  1121. case "D":
  1122. sql += string.Format("AND ( INOUT_TYPE = 'I' OR INOUT_TYPE = 'O') ");
  1123. break;
  1124. case "E":
  1125. sql += string.Format("AND COMM_ID != 1 ");
  1126. break;
  1127. default:
  1128. sql += string.Format("AND INOUT_TYPE = '{0}' ", ioType);
  1129. break;
  1130. }
  1131. }
  1132. }
  1133. return sql;
  1134. }
  1135. private string SqlEventTypeDetail()
  1136. {
  1137. string sql = "";
  1138. sql += string.Format("AND EVENT_TYPE = '{0}' ", eventTypeD);
  1139. return sql;
  1140. }
  1141. //private string SqlCOMMANDTypeDetail()
  1142. //{
  1143. // string sql = "";
  1144. // sql += string.Format("AND COMMAND_TYPE = '{0}' ", eventTypeD);
  1145. // return sql;
  1146. //}
  1147. private string SqlEventType()
  1148. {
  1149. string sql = "";
  1150. if (fireOn && devOn && controlLog && boardError && repeaterError && devError)
  1151. {
  1152. }
  1153. else if ((fireOn || devOn || controlLog || boardError || repeaterError || devError) == false)
  1154. {
  1155. }
  1156. else
  1157. {
  1158. if (fireOn)
  1159. {
  1160. sql += "AND ( EVENT_TYPE = 'OA' "; //화재축적상태 발생
  1161. sql += "OR EVENT_TYPE = 'OF' ";//화재상태 발생
  1162. sql += "OR EVENT_TYPE = 'OT' "; //발신기설정
  1163. sql += "OR EVENT_TYPE = 'XA' "; //화재축적상태 해제
  1164. sql += "OR EVENT_TYPE = 'XF' ";//화재상태 해제
  1165. sql += "OR EVENT_TYPE = 'XT' ";//발신기해제
  1166. }
  1167. if (devOn)
  1168. {
  1169. if (fireOn/* || fireOff*/)
  1170. {
  1171. sql += "OR ";
  1172. }
  1173. else
  1174. {
  1175. sql += "AND ( ";
  1176. }
  1177. sql += "EVENT_TYPE = 'OE' "; //설비작동상태 발생
  1178. sql += "OR EVENT_TYPE = 'XE' "; //설비작동상태 해제
  1179. sql += "OR EVENT_TYPE = 'EW' "; //설비출력상태 ON
  1180. sql += "OR EVENT_TYPE = 'EF' "; //설비출력상태 OFF
  1181. }
  1182. if (controlLog)
  1183. {
  1184. if (fireOn /*|| fireOff*/ || devOn /*|| devOff || errorOn || errorOff*/)
  1185. {
  1186. sql += "OR ";
  1187. }
  1188. else
  1189. {
  1190. sql += "AND ( ";
  1191. }
  1192. sql += "EVENT_TYPE = 'OP' ";//전화설정
  1193. sql += "OR EVENT_TYPE = 'XP' ";//전화해제
  1194. sql += "OR EVENT_TYPE = 'SP' ";//화면좌표설정버튼
  1195. sql += "OR EVENT_TYPE = 'MS' ";//주음향정지버튼
  1196. sql += "OR EVENT_TYPE = 'LS' ";//지구음향정지버튼
  1197. sql += "OR EVENT_TYPE = 'EB' ";//비상방송정지버튼
  1198. sql += "OR EVENT_TYPE = 'RP' ";//예비전원시험버튼
  1199. sql += "OR EVENT_TYPE = 'ED' ";//설비연동조작버튼 // cyim 2016.12.09 : IFC3300에서는 예비전원버튼 -> 설비전체연동조작버튼
  1200. sql += "OR EVENT_TYPE = 'FR' ";//화재복구버튼
  1201. sql += "OR EVENT_TYPE = 'EC' ";//설비연동화면버튼
  1202. sql += "OR EVENT_TYPE = 'PO' ";//통신(POLLING) 시작
  1203. sql += "OR EVENT_TYPE = 'PF' ";//통신(POLLING) 종료
  1204. sql += "OR EVENT_TYPE = 'CC' ";//통신상태읽기
  1205. sql += "OR EVENT_TYPE = 'CV' ";//전압상태읽기
  1206. sql += "OR EVENT_TYPE = 'V2' ";//24 전압 변경
  1207. sql += "OR EVENT_TYPE = 'DC' ";//설비작동차단
  1208. sql += "OR EVENT_TYPE = 'CS' ";//환경설정
  1209. sql += "OR EVENT_TYPE = 'TK' ";//키패드테스트
  1210. sql += "OR EVENT_TYPE = 'VI' ";//통신프로그램버전정보
  1211. sql += "OR EVENT_TYPE = 'BF' ";//부저정지
  1212. sql += "OR EVENT_TYPE = 'EL' ";//이벤트리스트
  1213. sql += "OR EVENT_TYPE = 'BS' ";//보드상태
  1214. sql += "OR EVENT_TYPE = 'RA' ";//화재축적상태 재설정
  1215. sql += "OR EVENT_TYPE = 'OU' ";//UI프로그램시작
  1216. sql += "OR EVENT_TYPE = 'XU' ";//UI프로그램종료
  1217. sql += "OR EVENT_TYPE = 'DS' ";//설비작동상태조회
  1218. }
  1219. if (boardError)
  1220. {
  1221. if (fireOn || devOn || controlLog)
  1222. {
  1223. sql += "OR ";
  1224. }
  1225. else
  1226. {
  1227. sql += "AND ( ";
  1228. }
  1229. sql += "EVENT_TYPE = 'XM' "; //통신모듈 통신이상
  1230. sql += "OR EVENT_TYPE = 'XI' ";//I/O 모듈 통신이상
  1231. sql += "OR EVENT_TYPE = 'XK' ";//키패드모듈 통신이상
  1232. sql += "OR EVENT_TYPE = 'AV' ";//출력전압 이상
  1233. sql += "OR EVENT_TYPE = 'AR' ";//예비전원 이상
  1234. sql += "OR EVENT_TYPE = 'AA' ";//교류전원 이상
  1235. sql += "OR EVENT_TYPE = 'OD' ";//데몬연결이상
  1236. sql += "OR EVENT_TYPE = 'OB' ";//DB연결이상
  1237. sql += "OR EVENT_TYPE = 'BA' ";//통신보드루프교류전원이상
  1238. sql += "OR EVENT_TYPE = 'BR' ";//통신보드루프예비전원이상
  1239. sql += "OR EVENT_TYPE = 'XC' ";//비상방송 통신이상
  1240. sql += "OR EVENT_TYPE = 'OM' ";//통신모듈 통신정상
  1241. sql += "OR EVENT_TYPE = 'OI' ";//I/O 모듈 통신정상
  1242. sql += "OR EVENT_TYPE = 'OK' ";//키패드모듈 통신정상
  1243. sql += "OR EVENT_TYPE = 'NV' ";//출력전압 정상
  1244. sql += "OR EVENT_TYPE = 'NR' ";//예비전원 정상
  1245. sql += "OR EVENT_TYPE = 'NA' ";//교류전원 정상
  1246. sql += "OR EVENT_TYPE = 'XD' ";//데몬연결정상
  1247. sql += "OR EVENT_TYPE = 'XB' ";//DB연결정상
  1248. sql += "OR EVENT_TYPE = 'BN' ";//통신보드루프정상
  1249. sql += "OR EVENT_TYPE = 'OC' ";//비상방송 통신정상
  1250. }
  1251. if (repeaterError)
  1252. {
  1253. if (fireOn || devOn || controlLog || boardError)
  1254. {
  1255. sql += "OR ";
  1256. }
  1257. else
  1258. {
  1259. sql += "AND ( ";
  1260. }
  1261. sql += " EVENT_TYPE = 'XR' ";//중계기 통신이상
  1262. sql += "OR EVENT_TYPE = 'OR' ";//중계기 통신정상
  1263. sql += "OR EVENT_TYPE = 'OX' ";//중계기전원이상
  1264. sql += "OR EVENT_TYPE = 'XX' ";//중계기전원이상 해제
  1265. }
  1266. if (devError)
  1267. {
  1268. if (fireOn || devOn || controlLog || boardError || repeaterError)
  1269. {
  1270. sql += "OR ";
  1271. }
  1272. else
  1273. {
  1274. sql += "AND ( ";
  1275. }
  1276. sql += " EVENT_TYPE = 'OL' ";//중계기 단선발생
  1277. sql += "OR EVENT_TYPE = 'XL' ";//중계기 단선복구
  1278. }
  1279. sql += " ) ";
  1280. }
  1281. return sql;
  1282. }
  1283. //private string SqlCOMMANDType()
  1284. //{
  1285. // string sql = "";
  1286. // if (fireOn && devOn && controlLog && boardError && repeaterError && devError)
  1287. // {
  1288. // }
  1289. // else if ((fireOn || devOn || controlLog || boardError || repeaterError || devError) == false)
  1290. // {
  1291. // }
  1292. // else
  1293. // {
  1294. // if (fireOn)
  1295. // {
  1296. // sql += "AND ( COMMAND_TYPE = 'OA' "; //화재축적상태 발생
  1297. // sql += "OR EVENT_TYPE = 'OF' ";//화재상태 발생
  1298. // sql += "OR EVENT_TYPE = 'OT' "; //발신기설정
  1299. // sql += "OR EVENT_TYPE = 'XA' "; //화재축적상태 해제
  1300. // sql += "OR EVENT_TYPE = 'XF' ";//화재상태 해제
  1301. // sql += "OR EVENT_TYPE = 'XT' ";//발신기해제
  1302. // }
  1303. // if (devOn)
  1304. // {
  1305. // if (fireOn/* || fireOff*/)
  1306. // {
  1307. // sql += "OR ";
  1308. // }
  1309. // else
  1310. // {
  1311. // sql += "AND ( ";
  1312. // }
  1313. // sql += "EVENT_TYPE = 'OE' "; //설비작동상태 발생
  1314. // sql += "OR EVENT_TYPE = 'XE' "; //설비작동상태 해제
  1315. // sql += "OR EVENT_TYPE = 'EW' "; //설비출력상태 ON
  1316. // sql += "OR EVENT_TYPE = 'EF' "; //설비출력상태 OFF
  1317. // }
  1318. // if (controlLog)
  1319. // {
  1320. // if (fireOn /*|| fireOff*/ || devOn /*|| devOff || errorOn || errorOff*/)
  1321. // {
  1322. // sql += "OR ";
  1323. // }
  1324. // else
  1325. // {
  1326. // sql += "AND ( ";
  1327. // }
  1328. // sql += "EVENT_TYPE = 'OP' ";//전화설정
  1329. // sql += "OR EVENT_TYPE = 'XP' ";//전화해제
  1330. // sql += "OR EVENT_TYPE = 'SP' ";//화면좌표설정버튼
  1331. // sql += "OR EVENT_TYPE = 'MS' ";//주음향정지버튼
  1332. // sql += "OR EVENT_TYPE = 'LS' ";//지구음향정지버튼
  1333. // sql += "OR EVENT_TYPE = 'EB' ";//비상방송정지버튼
  1334. // sql += "OR EVENT_TYPE = 'RP' ";//예비전원시험버튼
  1335. // sql += "OR EVENT_TYPE = 'ED' ";//설비연동조작버튼 // cyim 2016.12.09 : IFC3300에서는 예비전원버튼 -> 설비전체연동조작버튼
  1336. // sql += "OR EVENT_TYPE = 'FR' ";//화재복구버튼
  1337. // sql += "OR EVENT_TYPE = 'EC' ";//설비연동화면버튼
  1338. // sql += "OR EVENT_TYPE = 'PO' ";//통신(POLLING) 시작
  1339. // sql += "OR EVENT_TYPE = 'PF' ";//통신(POLLING) 종료
  1340. // sql += "OR EVENT_TYPE = 'CC' ";//통신상태읽기
  1341. // sql += "OR EVENT_TYPE = 'CV' ";//전압상태읽기
  1342. // sql += "OR EVENT_TYPE = 'V2' ";//24 전압 변경
  1343. // sql += "OR EVENT_TYPE = 'DC' ";//설비작동차단
  1344. // sql += "OR EVENT_TYPE = 'CS' ";//환경설정
  1345. // sql += "OR EVENT_TYPE = 'TK' ";//키패드테스트
  1346. // sql += "OR EVENT_TYPE = 'VI' ";//통신프로그램버전정보
  1347. // sql += "OR EVENT_TYPE = 'BF' ";//부저정지
  1348. // sql += "OR EVENT_TYPE = 'EL' ";//이벤트리스트
  1349. // sql += "OR EVENT_TYPE = 'BS' ";//보드상태
  1350. // sql += "OR EVENT_TYPE = 'RA' ";//화재축적상태 재설정
  1351. // sql += "OR EVENT_TYPE = 'OU' ";//UI프로그램시작
  1352. // sql += "OR EVENT_TYPE = 'XU' ";//UI프로그램종료
  1353. // sql += "OR EVENT_TYPE = 'DS' ";//설비작동상태조회
  1354. // }
  1355. // if (boardError)
  1356. // {
  1357. // if (fireOn || devOn || controlLog)
  1358. // {
  1359. // sql += "OR ";
  1360. // }
  1361. // else
  1362. // {
  1363. // sql += "AND ( ";
  1364. // }
  1365. // sql += "EVENT_TYPE = 'XM' "; //통신모듈 통신이상
  1366. // sql += "OR EVENT_TYPE = 'XI' ";//I/O 모듈 통신이상
  1367. // sql += "OR EVENT_TYPE = 'XK' ";//키패드모듈 통신이상
  1368. // sql += "OR EVENT_TYPE = 'AV' ";//출력전압 이상
  1369. // sql += "OR EVENT_TYPE = 'AR' ";//예비전원 이상
  1370. // sql += "OR EVENT_TYPE = 'AA' ";//교류전원 이상
  1371. // sql += "OR EVENT_TYPE = 'OD' ";//데몬연결이상////////////////////////////////////////
  1372. // sql += "OR EVENT_TYPE = 'OB' ";//DB연결이상
  1373. // sql += "OR EVENT_TYPE = 'BA' ";//통신보드루프교류전원이상
  1374. // sql += "OR EVENT_TYPE = 'BR' ";//통신보드루프예비전원이상
  1375. // sql += "OR EVENT_TYPE = 'XC' ";//비상방송 통신이상
  1376. // sql += "OR EVENT_TYPE = 'OM' ";//통신모듈 통신정상
  1377. // sql += "OR EVENT_TYPE = 'OI' ";//I/O 모듈 통신정상
  1378. // sql += "OR EVENT_TYPE = 'OK' ";//키패드모듈 통신정상
  1379. // sql += "OR EVENT_TYPE = 'NV' ";//출력전압 정상
  1380. // sql += "OR EVENT_TYPE = 'NR' ";//예비전원 정상
  1381. // sql += "OR EVENT_TYPE = 'NA' ";//교류전원 정상
  1382. // sql += "OR EVENT_TYPE = 'XD' ";//데몬연결정상////////////////////////////////////////
  1383. // sql += "OR EVENT_TYPE = 'XB' ";//DB연결정상
  1384. // sql += "OR EVENT_TYPE = 'BN' ";//통신보드루프정상
  1385. // sql += "OR EVENT_TYPE = 'OC' ";//비상방송 통신정상
  1386. // }
  1387. // if (repeaterError)
  1388. // {
  1389. // if (fireOn || devOn || controlLog || boardError)
  1390. // {
  1391. // sql += "OR ";
  1392. // }
  1393. // else
  1394. // {
  1395. // sql += "AND ( ";
  1396. // }
  1397. // sql += " EVENT_TYPE = 'XR' ";//중계기 통신이상
  1398. // sql += "OR EVENT_TYPE = 'OR' ";//중계기 통신정상
  1399. // sql += "OR EVENT_TYPE = 'OX' ";//중계기전원이상
  1400. // sql += "OR EVENT_TYPE = 'XX' ";//중계기전원이상 해제
  1401. // }
  1402. // if (devError)
  1403. // {
  1404. // if (fireOn || devOn || controlLog || boardError || repeaterError)
  1405. // {
  1406. // sql += "OR ";
  1407. // }
  1408. // else
  1409. // {
  1410. // sql += "AND ( ";
  1411. // }
  1412. // sql += " EVENT_TYPE = 'OL' ";//중계기 단선발생
  1413. // sql += "OR EVENT_TYPE = 'XL' ";//중계기 단선복구
  1414. // }
  1415. // sql += " ) ";
  1416. // }
  1417. // return sql;
  1418. //}
  1419. public string CreateSQLCount()
  1420. {
  1421. string sql = "SELECT ";
  1422. sql += SqlCount();
  1423. sql += SqlLocation();
  1424. if (this.eventTypeDetail)
  1425. {
  1426. sql += SqlEventTypeDetail();
  1427. }
  1428. else
  1429. {
  1430. sql += SqlEventType();
  1431. }
  1432. sql += SqlTime();
  1433. //Util.UDebugMessage("LOG SQL : " + sql, 0, 0);
  1434. return sql;
  1435. }
  1436. // cyim 2013.9.17 데이타베이스 조회 컨셉변경 : 50000->30000
  1437. public string CreateSQL()
  1438. {
  1439. string sql = "SELECT FIRST 30000 ";
  1440. sql += SqlIndex();
  1441. sql += SqlBase();
  1442. sql += SqlLocation();
  1443. if (this.eventTypeDetail)
  1444. {
  1445. sql += SqlEventTypeDetail();
  1446. }
  1447. else
  1448. {
  1449. sql += SqlEventType();
  1450. }
  1451. sql += SqlTime();
  1452. sql += "ORDER BY EVENT_TIME DESC ";
  1453. //Util.UDebugMessage("LOG SQL : " + sql, 0, 0);
  1454. return sql;
  1455. }
  1456. // cyim 2013.9.17 데이타베이스 조회 컨셉변경 : 가져올 개수를 지정
  1457. public string CreateSQL(int FirstCount)
  1458. {
  1459. if (FirstCount == 0) return null;
  1460. string sql = "SELECT FIRST " + FirstCount.ToString() + " ";
  1461. sql += SqlIndex();
  1462. sql += SqlBase();
  1463. sql += SqlLocation();
  1464. if (this.eventTypeDetail)
  1465. {
  1466. sql += SqlEventTypeDetail();
  1467. }
  1468. else
  1469. {
  1470. sql += SqlEventType();
  1471. }
  1472. sql += SqlTime();
  1473. sql += "ORDER BY EVENT_TIME DESC ";
  1474. //Util.UDebugMessage("LOG SQL : " + sql, 0, 0);
  1475. return sql;
  1476. }
  1477. // cyim 2013.10.10 : 1000건 조회 속도 개선 작업
  1478. public string CreateSQL(int FirstCount, DateTime StartTime, DateTime EndTime)
  1479. {
  1480. if (FirstCount == 0) return null;
  1481. string sql = "SELECT FIRST " + FirstCount.ToString() + " ";
  1482. sql += SqlIndex();
  1483. sql += SqlBase();
  1484. sql += SqlLocation();
  1485. if (this.eventTypeDetail)
  1486. {
  1487. sql += SqlEventTypeDetail();
  1488. }
  1489. else
  1490. {
  1491. sql += SqlEventType();
  1492. }
  1493. // cyim 2013.10.10 : 1000건 조회 속도 개선 작업
  1494. sql += SqlTime(StartTime, EndTime);
  1495. sql += "ORDER BY EVENT_TIME DESC ";
  1496. //Util.UDebugMessage("LOG SQL : " + sql, 0, 0);
  1497. return sql;
  1498. }
  1499. // cyim 2013.9.17 데이타베이스 조회 컨셉변경 : 가져올 데이타 개수를 미리 조회
  1500. public string Get_EventLogDB_Count(DateTime StartTime, DateTime EndTime)
  1501. {
  1502. string sql = "SELECT COUNT(*) ";
  1503. sql += SqlIndex();
  1504. //sql += SqlBase();
  1505. sql += "FROM TB_EVENT_LOG ";
  1506. sql += "WHERE RECEIVER_ID > 0 ";
  1507. sql += SqlLocation();
  1508. if (this.eventTypeDetail)
  1509. {
  1510. sql += SqlEventTypeDetail();
  1511. }
  1512. else
  1513. {
  1514. sql += SqlEventType();
  1515. }
  1516. // cyim 2013.10.10 : 1000건 조회 속도 개선 작업
  1517. sql += SqlTime(StartTime, EndTime);
  1518. return sql;
  1519. }
  1520. //public string Get_COMMANDLogDB_Count(DateTime StartTime, DateTime EndTime)
  1521. //{
  1522. // string sql = "SELECT COUNT(*) ";
  1523. // sql += SqlIndex();
  1524. // //sql += SqlBase();
  1525. // sql += "FROM TB_COMMAND_LOG ";
  1526. // sql += "WHERE RECEIVER_ID > 0 ";
  1527. // sql += SqlLocation();
  1528. // if (this.eventTypeDetail)
  1529. // {
  1530. // sql += SqlCOMMANDTypeDetail();
  1531. // }
  1532. // //else
  1533. // //{
  1534. // // sql += SqlCOMMANDType();
  1535. // //}
  1536. // // cyim 2013.10.10 : 1000건 조회 속도 개선 작업
  1537. // sql += COMMANDSqlTime(StartTime, EndTime);
  1538. // return sql;
  1539. //}
  1540. }
  1541. }