_Sql.cs 56 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Text;
  4. using System.Data.SqlClient;
  5. namespace IControls_FireManager
  6. {
  7. // SQL 쿼리문은 여기 클래스에서 전담한다
  8. public static class _Sql
  9. {
  10. ///
  11. /// 쿼리 실행 비포함
  12. ///
  13. // FireBird에서만 사용가능
  14. // 특정 Table에서 윗쪽부터 필요한 레코드 가져오기 SQL문
  15. public static string Get_ColumnFirstValue(string TableName, string Number, string RECEIVER_ID)
  16. {
  17. // 적용테이블 :
  18. // "TB_DEVICE" - RECEIVER_ID 있음
  19. string SQL = null;
  20. if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
  21. {
  22. SQL = "SELECT FIRST " + Number + " * " +
  23. "FROM " + TableName + ";";
  24. }
  25. else
  26. {
  27. SQL = "SELECT FIRST " + Number + " * " +
  28. "FROM " + TableName +
  29. " WHERE " + TableName + "." + "RECEIVER_ID" + " = '" + RECEIVER_ID + "';";
  30. }
  31. return SQL;
  32. }
  33. // 특정 Table의 특정 Column에 최대값 찾기 SQL문
  34. public static string Get_ColumnMaxValue(string TableName, string Column)
  35. {
  36. // 적용테이블 : 없음
  37. string SQL = "SELECT MAX(" + Column + ") " +
  38. "FROM " + TableName + ";";
  39. return SQL;
  40. }
  41. // 총 레코드 수 알기
  42. public static string Get_TableRecordCount(string TableName)
  43. {
  44. // 적용테이블 : 없음
  45. string SQL = "SELECT count(*) " +
  46. "FROM " + TableName + ";";
  47. return SQL;
  48. }
  49. public static string DeleteEventLogDataTime(string StartTime, string EndTime)
  50. {
  51. String SQL = "DELETE FROM TB_EVENT_LOG ";
  52. SQL += " WHERE EVENT_TIME <= '{0}' and EVENT_TIME >= '{1}'";
  53. SQL = String.Format(SQL, EndTime, StartTime);
  54. return SQL;
  55. }
  56. // cyim 2015.2.13 로그는 2년 유지
  57. public static string DeleteCommandLogData(string StartTime, string EndTime)
  58. {
  59. String SQL = "DELETE FROM TB_COMMAND_LOG ";
  60. SQL += " WHERE COMMAND_TIME <= '{0}' and COMMAND_TIME >= '{1}'";
  61. SQL = String.Format(SQL, EndTime, StartTime);
  62. return SQL;
  63. }
  64. // 특정 Table의 모든 Column 값 조회해오기
  65. public static string SearchAll_Table_Value(string TableName, string RECEIVER_ID)
  66. {
  67. // 적용테이블 :
  68. // "TB_DEVICE_TYPE" - RECEIVER_ID 없음
  69. // "TB_GROUP" "TB_RECEIVER" "TB_COMM" - RECEIVER_ID 있음
  70. string SQL = null;
  71. if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
  72. {
  73. SQL = "SELECT * " +
  74. "FROM " + TableName + ";";
  75. }
  76. else
  77. {
  78. SQL = "SELECT * " +
  79. "FROM " + TableName +
  80. " WHERE " + TableName + "." + "RECEIVER_ID" + " = '" + RECEIVER_ID + "';";
  81. }
  82. return SQL;
  83. }
  84. // 특정 Table의 모든 Column 값 조회해오기 (ORDER BY 사용)
  85. // FireBird는 insert를 시키면 레코드가 마지막에 들어가는게 아니라 이상하게 중간에 들어가게 됨...이해할수 없음..
  86. public static string SearchAll_Table_Value(string TableName, string Column, string RECEIVER_ID)
  87. {
  88. // 적용테이블 :
  89. // "TB_GROUP" "TB_MAP" - RECEIVER_ID 있음
  90. string SQL = null;
  91. if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
  92. {
  93. SQL = "SELECT * " +
  94. "FROM " + TableName + " ORDER BY " + Column + ";";
  95. }
  96. else
  97. {
  98. SQL = "SELECT * " +
  99. "FROM " + TableName +
  100. " WHERE " + TableName + "." + "RECEIVER_ID" + " = '" + RECEIVER_ID + "'" +
  101. " ORDER BY " + Column + ";";
  102. }
  103. return SQL;
  104. }
  105. // Where절을 이용하여 특정 Table의 모든 Column 값 조회해오기
  106. public static string SearchAll_Table_Value(string TableName, string Column, string Value, string RECEIVER_ID)
  107. {
  108. // 적용테이블 :
  109. // "TB_GROUP" "TB_MAP_PROCESS" - RECEIVER_ID 있음
  110. string SQL = null;
  111. if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
  112. {
  113. SQL = "SELECT * " +
  114. "FROM " + TableName + " " +
  115. "WHERE " + TableName + "." + Column + " = '" + Value + "';";
  116. }
  117. else
  118. {
  119. SQL = "SELECT * " +
  120. "FROM " + TableName + " " +
  121. "WHERE " + TableName + "." + "RECEIVER_ID" + " = '" + RECEIVER_ID + "' and " + TableName + "." + Column + " = '" + Value + "';";
  122. }
  123. return SQL;
  124. }
  125. // Where절을 이용하여 특정 Table의 모든 Column 값 조회해오기
  126. public static string SearchAll_Table_Value(string TableName, string Column1, string Value1, string Column2, string Value2, string RECEIVER_ID)
  127. {
  128. // 적용테이블 :
  129. // "TB_MAP_PROCESS" - RECEIVER_ID 있음
  130. string SQL = null;
  131. if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
  132. {
  133. SQL = "SELECT * " +
  134. "FROM " + TableName + " " +
  135. "WHERE " + TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "';";
  136. }
  137. else
  138. {
  139. SQL = "SELECT * " +
  140. "FROM " + TableName + " " +
  141. "WHERE " + TableName + "." + "RECEIVER_ID" + " = '" + RECEIVER_ID + "' and " + TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "';";
  142. }
  143. return SQL;
  144. }
  145. // Where절을 이용하여 특정 Table의 모든 Column 값 조회해오기
  146. public static string SearchAll_Table_Value(string TableName, string Column1, string Value1, string Column2, string Value2, string Column3, string Value3, string Column4, string Value4, string Column5, string Value5, string Column6, string Value6, string RECEIVER_ID)
  147. {
  148. // 적용테이블 :
  149. // "TB_MAP_PROCESS" - RECEIVER_ID 있음
  150. string SQL = null;
  151. if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
  152. {
  153. SQL = "SELECT * " +
  154. "FROM " + TableName + " " +
  155. "WHERE " + TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "' and " + TableName + "." + Column3 + " = '" + Value3 + "' and " + TableName + "." + Column4 + " = '" + Value4 + "' and " + TableName + "." + Column5 + " = '" + Value5 + "' and " + TableName + "." + Column6 + " = '" + Value6 + "';";
  156. }
  157. else
  158. {
  159. SQL = "SELECT * " +
  160. "FROM " + TableName + " " +
  161. "WHERE " + TableName + "." + "RECEIVER_ID" + " = '" + RECEIVER_ID + "' and " + TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "' and " + TableName + "." + Column3 + " = '" + Value3 + "' and " + TableName + "." + Column4 + " = '" + Value4 + "' and " + TableName + "." + Column5 + " = '" + Value5 + "' and " + TableName + "." + Column6 + " = '" + Value6 + "';";
  162. }
  163. return SQL;
  164. }
  165. // TB_MAP_PROCESS 테이블에서 시컨스 최대값 가져오기
  166. public static string Search_MapProcess_MaxValue(string TableName, string Column1, string Value1, string Column2, string Value2, string MAP_RECEIVER_ID)
  167. {
  168. string SQL = "SELECT max(SEQ_NO) " +
  169. "FROM " + TableName + " " +
  170. "WHERE " + TableName + "." + "MAP_RECEIVER_ID" + " = '" + MAP_RECEIVER_ID + "' and " + TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "';";
  171. return SQL;
  172. }
  173. // Where절을 이용하여 특정 Table의 모든 Column 값 조회해오기
  174. public static string Search_Table(string TableName, string Column1, string Value1, string Column2, string Value2, string RECEIVER_ID)
  175. {
  176. // 적용테이블 :
  177. // "TB_GROUP" - RECEIVER_ID 있음
  178. string SQL = null;
  179. if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
  180. {
  181. SQL += "SELECT * ";
  182. SQL += "FROM " + TableName + " ";
  183. if (Value1.Length != 0 || Value2.Length != 0) SQL += "WHERE ";
  184. if (Value1.Length != 0) SQL += Column1 + " = '" + Value1 + "' ";
  185. if (Value1.Length != 0 && Value2.Length != 0) SQL += "AND ";
  186. if (Value2.Length != 0) SQL += Column2 + " like '%" + Value2 + "%' ";
  187. }
  188. else
  189. {
  190. SQL += "SELECT * ";
  191. SQL += "FROM " + TableName + " ";
  192. if (Value1.Length != 0 || Value2.Length != 0) SQL += "WHERE " + TableName + "." + "RECEIVER_ID" + " = '" + RECEIVER_ID + "' and ";
  193. if (Value1.Length != 0) SQL += Column1 + " = '" + Value1 + "' ";
  194. if (Value1.Length != 0 && Value2.Length != 0) SQL += "AND ";
  195. if (Value2.Length != 0) SQL += Column2 + " like '%" + Value2 + "%' ";
  196. }
  197. return SQL;
  198. }
  199. // Where절을 이용하여 특정 Table의 모든 Column 값 조회해오기
  200. // TB_DEVICE 테이블에 특성화되어 있음
  201. public static string SearchDeviceName_Table_Value(string TableName, string Column1, string Value1, string Column2, int Value2,
  202. string Column3, int Value3, string Column4, int Value4,
  203. string Column5, int Value5, string Column6, int Value6,
  204. string Column7, string Value7, string RECEIVER_ID)
  205. {
  206. // 적용테이블 :
  207. // "TB_DEVICE" - RECEIVER_ID 있음
  208. string SQL = null;
  209. if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
  210. {
  211. SQL = "SELECT DEVICE_NAME " +
  212. "FROM " + TableName + " " +
  213. "WHERE " + TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "' and " +
  214. TableName + "." + Column3 + " = '" + Value3 + "' and " + TableName + "." + Column4 + " = '" + Value4 + "' and " +
  215. TableName + "." + Column5 + " = '" + Value5 + "' and " + TableName + "." + Column6 + " = '" + Value6 + "' and " +
  216. TableName + "." + Column7 + " = '" + Value7 + "';";
  217. }
  218. else
  219. {
  220. SQL = "SELECT DEVICE_NAME " +
  221. "FROM " + TableName + " " +
  222. "WHERE " + TableName + "." + "RECEIVER_ID" + " = '" + RECEIVER_ID + "' and " +
  223. TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "' and " +
  224. TableName + "." + Column3 + " = '" + Value3 + "' and " + TableName + "." + Column4 + " = '" + Value4 + "' and " +
  225. TableName + "." + Column5 + " = '" + Value5 + "' and " + TableName + "." + Column6 + " = '" + Value6 + "' and " +
  226. TableName + "." + Column7 + " = '" + Value7 + "';";
  227. }
  228. return SQL;
  229. }
  230. // Where절을 이용하여 특정 Table의 모든 Column 값 조회해오기
  231. // TB_GROUP_DEVICE 테이블에 특성화되어 있음
  232. public static string SearchAll_Table_Value(string TableName, string Column1, string Value1, string Column2, int Value2,
  233. string Column3, int Value3, string Column4, int Value4,
  234. string Column5, int Value5, string Column6, int Value6,
  235. string Column7, string Value7, string choice, string Group_Map_RECEIVER_ID, string RECEIVER_ID)
  236. {
  237. // 적용테이블 :
  238. // "TB_MAP_PROCESS" "TB_EMERGENCY_BROADCASTION" "TB_GROUP_DEVICE" - RECEIVER_ID 있음
  239. string SQL = null;
  240. if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
  241. {
  242. SQL = "SELECT * " +
  243. "FROM " + TableName + " " +
  244. "WHERE " + TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "' and " +
  245. TableName + "." + Column3 + " = '" + Value3 + "' and " + TableName + "." + Column4 + " = '" + Value4 + "' and " +
  246. TableName + "." + Column5 + " = '" + Value5 + "' and " + TableName + "." + Column6 + " = '" + Value6 + "' and " +
  247. TableName + "." + Column7 + " = '" + Value7 + "';";
  248. }
  249. else
  250. {
  251. SQL = "SELECT * " +
  252. "FROM " + TableName + " " +
  253. "WHERE " + TableName + "." + choice + " = '" + Group_Map_RECEIVER_ID + "' and " + TableName + "." + "RECEIVER_ID" + " = '" + RECEIVER_ID + "' and " +
  254. TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "' and " +
  255. TableName + "." + Column3 + " = '" + Value3 + "' and " + TableName + "." + Column4 + " = '" + Value4 + "' and " +
  256. TableName + "." + Column5 + " = '" + Value5 + "' and " + TableName + "." + Column6 + " = '" + Value6 + "' and " +
  257. TableName + "." + Column7 + " = '" + Value7 + "';";
  258. }
  259. return SQL;
  260. }
  261. // TB_EMERGENCY_BROADCASTION 전용
  262. public static string SearchAll_TB_EMERGENCY_BROADCASTION(string TableName, string Column1, int Value1, string Column2, int Value2,
  263. string Column3, int Value3, string Column4, int Value4,
  264. string Column5, int Value5, string Column6, string Value6,
  265. string RECEIVER_ID)
  266. {
  267. // 적용테이블 :
  268. // "TB_MAP_PROCESS" "TB_EMERGENCY_BROADCASTION" "TB_GROUP_DEVICE" - RECEIVER_ID 있음
  269. string SQL = null;
  270. if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
  271. {
  272. SQL = "SELECT * " +
  273. "FROM " + TableName + " " +
  274. "WHERE " + TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "' and " +
  275. TableName + "." + Column3 + " = '" + Value3 + "' and " + TableName + "." + Column4 + " = '" + Value4 + "' and " +
  276. TableName + "." + Column5 + " = '" + Value5 + "' and " + TableName + "." + Column6 + " = '" + Value6 + "';";
  277. }
  278. else
  279. {
  280. SQL = "SELECT * " +
  281. "FROM " + TableName + " " +
  282. "WHERE " + TableName + "." + "RECEIVER_ID" + " = '" + RECEIVER_ID + "' and " +
  283. TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "' and " +
  284. TableName + "." + Column3 + " = '" + Value3 + "' and " + TableName + "." + Column4 + " = '" + Value4 + "' and " +
  285. TableName + "." + Column5 + " = '" + Value5 + "' and " + TableName + "." + Column6 + " = '" + Value6 + "';";
  286. }
  287. return SQL;
  288. }
  289. // IO 디바이스를 찾는데 특성화되어 있음
  290. public static string SearchAll_Table_Value(string TableName, string Column1, string Value1, string Column2, string Value2,
  291. string Column3, string Value3, string Column4, string Value4,
  292. string Column5, string Value5, string RECEIVER_ID)
  293. {
  294. // 적용테이블 :
  295. // "TB_DEVICE" - RECEIVER_ID 있음
  296. string SQL = null;
  297. if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
  298. {
  299. SQL = "SELECT * " +
  300. "FROM " + TableName + " " +
  301. "WHERE " + TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "' and " +
  302. TableName + "." + Column3 + " = '" + Value3 + "' and " + TableName + "." + Column4 + " = '" + Value4 + "' and " +
  303. TableName + "." + Column5 + " = '" + Value5 + "';";
  304. }
  305. else
  306. {
  307. SQL = "SELECT * " +
  308. "FROM " + TableName + " " +
  309. "WHERE " + TableName + "." + "RECEIVER_ID" + " = '" + RECEIVER_ID + "' and " +
  310. TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "' and " +
  311. TableName + "." + Column3 + " = '" + Value3 + "' and " + TableName + "." + Column4 + " = '" + Value4 + "' and " +
  312. TableName + "." + Column5 + " = '" + Value5 + "';";
  313. }
  314. return SQL;
  315. }
  316. // 특정 Table의 특정 Column 값 조회해오기
  317. public static string Search_Column_Table_Value(string TableName, string ColumnName)
  318. {
  319. string SQL = "SELECT " + TableName + "." + ColumnName + " " +
  320. "FROM " + TableName + ";";
  321. return SQL;
  322. }
  323. // BOARD 테이블 접근
  324. public static string Search_BoardTable(string COMM_ID, string BOARD_ID, string USE_FLAG, string RECEIVER_ID)
  325. {
  326. // 수정
  327. string SQL = null;
  328. SQL += "SELECT * ";
  329. SQL += "FROM TB_BOARD ";
  330. SQL += "WHERE " + "RECEIVER_ID = '" + RECEIVER_ID + "' and ";
  331. SQL += " COMM_ID = '" + COMM_ID + "' ";
  332. if (BOARD_ID.Length != 0) SQL += "AND BOARD_ID = '" + BOARD_ID + "'";
  333. if (USE_FLAG.Length != 0) SQL += "AND USE_FLAG = '" + USE_FLAG + "'";
  334. return SQL;
  335. }
  336. // Config 테이블 접근
  337. public static string Search_ConfigTable(int RECEIVER_ID)
  338. {
  339. // 적용테이블 :
  340. // "TB_CONFIG" - RECEIVER_ID 있음 (자신의 아이디를 조회하는 테이블)
  341. string SQL = null;
  342. SQL += "SELECT * ";
  343. SQL += "FROM TB_CONFIG ";
  344. SQL += "WHERE RECEIVER_ID = " + RECEIVER_ID;
  345. return SQL;
  346. }
  347. // Repeater 테이블 접근
  348. public static string Search_RepeaterTable(string COMM_ID, string BOARD_ID, string LOOP_NO, string RECEIVER_ID)
  349. {
  350. // 적용테이블 :
  351. // "TB_REPEATER" - RECEIVER_ID 있음
  352. string SQL = null;
  353. if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
  354. {
  355. SQL += "SELECT * ";
  356. SQL += "FROM TB_REPEATER ";
  357. SQL += "WHERE COMM_ID = '" + COMM_ID + "' ";
  358. if (BOARD_ID.Length != 0) SQL += "AND BOARD_ID = '" + BOARD_ID + "'";
  359. if (LOOP_NO.Length != 0) SQL += "AND LOOP_NO = '" + LOOP_NO + "'";
  360. }
  361. else
  362. {
  363. SQL += "SELECT * ";
  364. SQL += "FROM TB_REPEATER ";
  365. SQL += "WHERE " + "TB_REPEATER" + "." + "RECEIVER_ID" + " = '" + RECEIVER_ID + "' and ";
  366. SQL += " COMM_ID = '" + COMM_ID + "' ";
  367. if (BOARD_ID.Length != 0) SQL += "AND BOARD_ID = '" + BOARD_ID + "'";
  368. if (LOOP_NO.Length != 0) SQL += "AND LOOP_NO = '" + LOOP_NO + "'";
  369. }
  370. return SQL;
  371. }
  372. // Device 테이블 접근
  373. // BOARD_ID에 값이 있을 경우 조회
  374. public static string Search_DeviceTable(string COMM_ID, string INOUT_TYPE, string BOARD_ID, string LOOP_NO, string REPEATER_ID, string POSITION_CODE, string DEVICE_TYPE_NAME, string DEVICE_NAME, string RECEIVER_ID)
  375. {
  376. string SQL = null;
  377. SQL += "SELECT TB_DEVICE_TYPE.DEVICE_TYPE_NAME, TB_POSITION_CODE.POSITION_NAME, TB_DEVICE.DEVICE_NAME, TB_DEVICE.COMM_ID, TB_DEVICE.BOARD_ID, TB_DEVICE.LOOP_NO, TB_DEVICE.REPEATER_ID, TB_DEVICE.DEVICE_ID, TB_DEVICE.INOUT_TYPE, TB_DEVICE.RECEIVER_ID ";
  378. SQL += "FROM TB_DEVICE ";
  379. SQL += "LEFT OUTER JOIN TB_DEVICE_TYPE ON TB_DEVICE.DEVICE_TYPE = TB_DEVICE_TYPE.DEVICE_TYPE ";
  380. SQL += "LEFT OUTER JOIN TB_POSITION_CODE ON TB_DEVICE.POSITION_CODE = TB_POSITION_CODE.POSITION_CODE ";
  381. SQL += "WHERE TB_DEVICE.COMM_ID = '" + COMM_ID + "' ";
  382. if (RECEIVER_ID.Length != 0) SQL += "AND TB_DEVICE.RECEIVER_ID = '" + RECEIVER_ID + "' ";
  383. if (INOUT_TYPE.Length != 0) SQL += "AND TB_DEVICE.INOUT_TYPE = '" + INOUT_TYPE + "' ";
  384. if (BOARD_ID.Length != 0) SQL += "AND TB_DEVICE.BOARD_ID = '" + BOARD_ID + "' ";
  385. if (LOOP_NO.Length != 0) SQL += "AND TB_DEVICE.LOOP_NO = '" + LOOP_NO + "' ";
  386. if (REPEATER_ID.Length != 0) SQL += "AND TB_DEVICE.REPEATER_ID = '" + REPEATER_ID + "' ";
  387. if (POSITION_CODE.Length != 0) SQL += "AND TB_POSITION_CODE.POSITION_NAME like '%" + POSITION_CODE + "%' ";
  388. if (DEVICE_TYPE_NAME.Length != 0) SQL += "AND TB_DEVICE_TYPE.DEVICE_TYPE_NAME like '%" + DEVICE_TYPE_NAME + "%' ";
  389. if (DEVICE_NAME.Length != 0) SQL += "AND TB_DEVICE.DEVICE_NAME like '%" + DEVICE_NAME + "%' ";
  390. return SQL;
  391. }
  392. // BOARD_ID에 값이 ""일 경우
  393. public static string Search_DEVICETable(string COMM_ID, string INOUT_TYPE, string BOARD_ID, string LOOP_NO, string REPEATER_ID, string POSITION_CODE, string DEVICE_TYPE_NAME, string DEVICE_NAME, string RECEIVER_ID)
  394. {
  395. string SQL = null;
  396. SQL += "SELECT TB_DEVICE_TYPE.DEVICE_TYPE_NAME, TB_POSITION_CODE.POSITION_NAME, TB_DEVICE.DEVICE_NAME, TB_DEVICE.COMM_ID, TB_DEVICE.BOARD_ID, TB_DEVICE.LOOP_NO, TB_DEVICE.REPEATER_ID, TB_DEVICE.DEVICE_ID, TB_DEVICE.INOUT_TYPE, TB_DEVICE.RECEIVER_ID ";
  397. SQL += "FROM TB_DEVICE ";
  398. SQL += "LEFT OUTER JOIN TB_DEVICE_TYPE ON TB_DEVICE.DEVICE_TYPE = TB_DEVICE_TYPE.DEVICE_TYPE ";
  399. SQL += "LEFT OUTER JOIN TB_POSITION_CODE ON TB_DEVICE.POSITION_CODE = TB_POSITION_CODE.POSITION_CODE ";
  400. SQL += "WHERE TB_DEVICE.COMM_ID = '" + COMM_ID + "' ";
  401. if (RECEIVER_ID.Length != 0) SQL += "AND TB_DEVICE.RECEIVER_ID = '" + RECEIVER_ID + "' ";
  402. if (INOUT_TYPE.Length != 0) SQL += "AND TB_DEVICE.INOUT_TYPE = '" + INOUT_TYPE + "' ";
  403. if (BOARD_ID.Length != 0) SQL += "AND TB_DEVICE.BOARD_ID < " + BOARD_ID + " ";
  404. if (LOOP_NO.Length != 0) SQL += "AND TB_DEVICE.LOOP_NO = '" + LOOP_NO + "' ";
  405. if (REPEATER_ID.Length != 0) SQL += "AND TB_DEVICE.REPEATER_ID = '" + REPEATER_ID + "' ";
  406. if (POSITION_CODE.Length != 0) SQL += "AND TB_POSITION_CODE.POSITION_NAME like '%" + POSITION_CODE + "%' ";
  407. if (DEVICE_TYPE_NAME.Length != 0) SQL += "AND TB_DEVICE_TYPE.DEVICE_TYPE_NAME like '%" + DEVICE_TYPE_NAME + "%' ";
  408. if (DEVICE_NAME.Length != 0) SQL += "AND TB_DEVICE.DEVICE_NAME like '%" + DEVICE_NAME + "%' ";
  409. return SQL;
  410. }
  411. // 밑에 함수는 입력포인트의 속성이 화재이고, 비상방송이 매칭된것외에 전부 보여준다
  412. public static string Search_DEVICETable_With_EMERGENCY(
  413. string COMM_ID, string INOUT_TYPE, string BOARD_ID, string LOOP_NO, string REPEATER_ID,
  414. string POSITION_CODE, string DEVICE_TYPE_NAME, string DEVICE_NAME, string RECEIVER_ID,
  415. string AREA, string STAIR, string FLOOR, // 비상방송 조건 추가
  416. bool Mapping) // Device 테이블과 Emergency 테이블과 매핑되어있는지 조건 추가
  417. {
  418. string SQL = null;
  419. SQL += "SELECT TB_DEVICE_TYPE.DEVICE_TYPE_NAME, TB_POSITION_CODE.POSITION_NAME, TB_DEVICE.DEVICE_NAME, TB_DEVICE.COMM_ID, TB_DEVICE.BOARD_ID, TB_DEVICE.LOOP_NO, TB_DEVICE.REPEATER_ID, TB_DEVICE.DEVICE_ID, TB_DEVICE.INOUT_TYPE, TB_DEVICE.RECEIVER_ID ";
  420. // 입력 포인트를 위해 비상방송코드 정보까지 같이 출력한다
  421. SQL += ",TB_EMERGENCY_BROADCASTION.AREA_CODE, TB_EMERGENCY_BROADCASTION.STAIR_CODE, TB_EMERGENCY_BROADCASTION.FLOOR_CODE ";
  422. SQL += "FROM TB_DEVICE ";
  423. SQL += "LEFT OUTER JOIN TB_DEVICE_TYPE ON TB_DEVICE.DEVICE_TYPE = TB_DEVICE_TYPE.DEVICE_TYPE ";
  424. SQL += "LEFT OUTER JOIN TB_POSITION_CODE ON TB_DEVICE.POSITION_CODE = TB_POSITION_CODE.POSITION_CODE ";
  425. // 입력 포인트를 위해 Join 추가
  426. SQL += "LEFT OUTER JOIN TB_EMERGENCY_BROADCASTION ON (TB_DEVICE.RECEIVER_ID = TB_EMERGENCY_BROADCASTION.RECEIVER_ID ";
  427. SQL += " AND TB_EMERGENCY_BROADCASTION.COMM_ID = TB_DEVICE.COMM_ID AND TB_EMERGENCY_BROADCASTION.BOARD_ID = TB_DEVICE.BOARD_ID";
  428. SQL += " AND TB_EMERGENCY_BROADCASTION.LOOP_NO = TB_DEVICE.LOOP_NO AND TB_EMERGENCY_BROADCASTION.REPEATER_ID = TB_DEVICE.REPEATER_ID";
  429. SQL += " AND TB_EMERGENCY_BROADCASTION.DEVICE_ID = TB_DEVICE.DEVICE_ID AND TB_EMERGENCY_BROADCASTION.INOUT_TYPE = TB_DEVICE.INOUT_TYPE) ";
  430. SQL += "WHERE TB_DEVICE.COMM_ID = '" + COMM_ID + "' ";
  431. if (RECEIVER_ID.Length != 0) SQL += "AND TB_DEVICE.RECEIVER_ID = '" + RECEIVER_ID + "' ";
  432. if (INOUT_TYPE.Length != 0) SQL += "AND TB_DEVICE.INOUT_TYPE = '" + INOUT_TYPE + "' ";
  433. if (BOARD_ID == "15") // BOARD_ID_All : 통신카드 15번 카드를 제외하고 전부를 찾는 경우에는 파라미터값이 15로 들어온다, 특정 통신 카드를 찾을때는 통신카드 아이디가 파라미터 값이다.
  434. {
  435. if (BOARD_ID.Length != 0) SQL += "AND TB_DEVICE.BOARD_ID < " + BOARD_ID + " ";
  436. }
  437. else
  438. {
  439. if (BOARD_ID.Length != 0) SQL += "AND TB_DEVICE.BOARD_ID = '" + BOARD_ID + "' ";
  440. }
  441. if (LOOP_NO.Length != 0) SQL += "AND TB_DEVICE.LOOP_NO = '" + LOOP_NO + "' ";
  442. if (REPEATER_ID.Length != 0) SQL += "AND TB_DEVICE.REPEATER_ID = '" + REPEATER_ID + "' ";
  443. if (POSITION_CODE.Length != 0) SQL += "AND TB_POSITION_CODE.POSITION_NAME like '%" + POSITION_CODE + "%' ";
  444. if (DEVICE_TYPE_NAME.Length != 0) SQL += "AND TB_DEVICE_TYPE.DEVICE_TYPE_NAME like '%" + DEVICE_TYPE_NAME + "%' ";
  445. if (DEVICE_NAME.Length != 0) SQL += "AND TB_DEVICE.DEVICE_NAME like '%" + DEVICE_NAME + "%' ";
  446. // 비상방송 조회조건
  447. if (AREA.Length != 0) SQL += "AND TB_EMERGENCY_BROADCASTION.AREA_CODE = '" + AREA + "' ";
  448. if (STAIR.Length != 0) SQL += "AND TB_EMERGENCY_BROADCASTION.STAIR_CODE = '" + STAIR + "' ";
  449. if (FLOOR.Length != 0) SQL += "AND TB_EMERGENCY_BROADCASTION.FLOOR_CODE = '" + FLOOR + "' ";
  450. // 맵핑이 된것을 찾으려면
  451. if (Mapping == true)
  452. {
  453. SQL += " AND TB_EMERGENCY_BROADCASTION.COMM_ID = TB_DEVICE.COMM_ID AND TB_EMERGENCY_BROADCASTION.BOARD_ID = TB_DEVICE.BOARD_ID";
  454. SQL += " AND TB_EMERGENCY_BROADCASTION.LOOP_NO = TB_DEVICE.LOOP_NO AND TB_EMERGENCY_BROADCASTION.REPEATER_ID = TB_DEVICE.REPEATER_ID";
  455. SQL += " AND TB_EMERGENCY_BROADCASTION.DEVICE_ID = TB_DEVICE.DEVICE_ID AND TB_EMERGENCY_BROADCASTION.INOUT_TYPE = TB_DEVICE.INOUT_TYPE";
  456. }
  457. return SQL;
  458. }
  459. // TB_GROUP_DEVICE 테이블 접근
  460. public static string Search_Gruop_DeviceTable(string DEVICE_GROUP, string RECEIVER_ID)
  461. {
  462. // 적용테이블 :
  463. // "TB_GROUP_DEVICE" - RECEIVER_ID 있음
  464. string SQL = null;
  465. if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
  466. {
  467. SQL += "SELECT TB_DEVICE_TYPE.DEVICE_TYPE_NAME, TB_POSITION_CODE.POSITION_NAME, TB_DEVICE.DEVICE_NAME, TB_GROUP_DEVICE.*, TB_DEVICE.RECEIVER_ID ";
  468. SQL += "FROM TB_GROUP_DEVICE ";
  469. SQL += "LEFT OUTER JOIN TB_DEVICE ON TB_GROUP_DEVICE.RECEIVER_ID = TB_DEVICE.RECEIVER_ID AND TB_GROUP_DEVICE.COMM_ID = TB_DEVICE.COMM_ID ";
  470. SQL += "AND TB_GROUP_DEVICE.BOARD_ID = TB_DEVICE.BOARD_ID AND TB_GROUP_DEVICE.LOOP_NO = TB_DEVICE.LOOP_NO ";
  471. SQL += "AND TB_GROUP_DEVICE.REPEATER_ID = TB_DEVICE.REPEATER_ID AND TB_GROUP_DEVICE.DEVICE_ID = TB_DEVICE.DEVICE_ID ";
  472. SQL += "AND TB_GROUP_DEVICE.INOUT_TYPE = TB_DEVICE.INOUT_TYPE ";
  473. SQL += "LEFT OUTER JOIN TB_DEVICE_TYPE ON TB_DEVICE.DEVICE_TYPE = TB_DEVICE_TYPE.DEVICE_TYPE ";
  474. SQL += "LEFT OUTER JOIN TB_POSITION_CODE ON TB_DEVICE.POSITION_CODE = TB_POSITION_CODE.POSITION_CODE ";
  475. SQL += "WHERE TB_GROUP_DEVICE.GROUP_ID = '" + DEVICE_GROUP + "' ";
  476. }
  477. else
  478. {
  479. SQL += "SELECT TB_DEVICE_TYPE.DEVICE_TYPE_NAME, TB_POSITION_CODE.POSITION_NAME, TB_DEVICE.DEVICE_NAME, TB_GROUP_DEVICE.*, TB_DEVICE.RECEIVER_ID ";
  480. SQL += "FROM TB_GROUP_DEVICE ";
  481. SQL += "LEFT OUTER JOIN TB_DEVICE ON TB_GROUP_DEVICE.RECEIVER_ID = TB_DEVICE.RECEIVER_ID AND TB_GROUP_DEVICE.COMM_ID = TB_DEVICE.COMM_ID ";
  482. SQL += "AND TB_GROUP_DEVICE.BOARD_ID = TB_DEVICE.BOARD_ID AND TB_GROUP_DEVICE.LOOP_NO = TB_DEVICE.LOOP_NO ";
  483. SQL += "AND TB_GROUP_DEVICE.REPEATER_ID = TB_DEVICE.REPEATER_ID AND TB_GROUP_DEVICE.DEVICE_ID = TB_DEVICE.DEVICE_ID ";
  484. SQL += "AND TB_GROUP_DEVICE.INOUT_TYPE = TB_DEVICE.INOUT_TYPE ";
  485. SQL += "LEFT OUTER JOIN TB_DEVICE_TYPE ON TB_DEVICE.DEVICE_TYPE = TB_DEVICE_TYPE.DEVICE_TYPE ";
  486. SQL += "LEFT OUTER JOIN TB_POSITION_CODE ON TB_DEVICE.POSITION_CODE = TB_POSITION_CODE.POSITION_CODE ";
  487. SQL += "WHERE TB_GROUP_DEVICE.RECEIVER_ID = '" + RECEIVER_ID + "' AND";
  488. SQL += " TB_GROUP_DEVICE.GROUP_ID = '" + DEVICE_GROUP + "' ";
  489. }
  490. return SQL;
  491. }
  492. // TB_MAP_PROCESS 테이블 접근
  493. public static string Search_Map_ProcessTable(string MAP_NO, string MAP_TYPE, string RECEIVER_ID)
  494. {
  495. // 적용테이블 :
  496. // "TB_MAP_PROCESS" - RECEIVER_ID 있음
  497. string SQL = null;
  498. if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
  499. {
  500. SQL += "SELECT TB_DEVICE_TYPE.DEVICE_TYPE_NAME, TB_POSITION_CODE.POSITION_NAME, TB_DEVICE.DEVICE_NAME, TB_MAP_PROCESS.SEQ_NO, TB_MAP_PROCESS.PROCESS_TYPE, TB_MAP_PROCESS.GROUP_ID, TB_DEVICE.* ";
  501. SQL += "FROM TB_MAP_PROCESS ";
  502. SQL += "LEFT OUTER JOIN TB_DEVICE ON TB_MAP_PROCESS.RECEIVER_ID = TB_DEVICE.RECEIVER_ID AND TB_MAP_PROCESS.COMM_ID = TB_DEVICE.COMM_ID ";
  503. SQL += "AND TB_MAP_PROCESS.BOARD_ID = TB_DEVICE.BOARD_ID AND TB_MAP_PROCESS.LOOP_NO = TB_DEVICE.LOOP_NO ";
  504. SQL += "AND TB_MAP_PROCESS.REPEATER_ID = TB_DEVICE.REPEATER_ID AND TB_MAP_PROCESS.DEVICE_ID = TB_DEVICE.DEVICE_ID ";
  505. SQL += "AND TB_MAP_PROCESS.INOUT_TYPE = TB_DEVICE.INOUT_TYPE ";
  506. SQL += "LEFT OUTER JOIN TB_DEVICE_TYPE ON TB_DEVICE.DEVICE_TYPE = TB_DEVICE_TYPE.DEVICE_TYPE ";
  507. SQL += "LEFT OUTER JOIN TB_POSITION_CODE ON TB_DEVICE.POSITION_CODE = TB_POSITION_CODE.POSITION_CODE ";
  508. SQL += "WHERE TB_MAP_PROCESS.MAP_NO = '" + MAP_NO + "' AND TB_MAP_PROCESS.MAP_TYPE = '" + MAP_TYPE + "' ";
  509. }
  510. else
  511. {
  512. SQL += "SELECT TB_DEVICE_TYPE.DEVICE_TYPE_NAME, TB_POSITION_CODE.POSITION_NAME, TB_DEVICE.DEVICE_NAME, TB_MAP_PROCESS.SEQ_NO, TB_MAP_PROCESS.PROCESS_TYPE, TB_MAP_PROCESS.GROUP_ID, TB_DEVICE.* ";
  513. SQL += "FROM TB_MAP_PROCESS ";
  514. SQL += "LEFT OUTER JOIN TB_DEVICE ON TB_MAP_PROCESS.RECEIVER_ID = TB_DEVICE.RECEIVER_ID AND TB_MAP_PROCESS.COMM_ID = TB_DEVICE.COMM_ID ";
  515. SQL += "AND TB_MAP_PROCESS.BOARD_ID = TB_DEVICE.BOARD_ID AND TB_MAP_PROCESS.LOOP_NO = TB_DEVICE.LOOP_NO ";
  516. SQL += "AND TB_MAP_PROCESS.REPEATER_ID = TB_DEVICE.REPEATER_ID AND TB_MAP_PROCESS.DEVICE_ID = TB_DEVICE.DEVICE_ID ";
  517. SQL += "AND TB_MAP_PROCESS.INOUT_TYPE = TB_DEVICE.INOUT_TYPE ";
  518. SQL += "LEFT OUTER JOIN TB_DEVICE_TYPE ON TB_DEVICE.DEVICE_TYPE = TB_DEVICE_TYPE.DEVICE_TYPE ";
  519. SQL += "LEFT OUTER JOIN TB_POSITION_CODE ON TB_DEVICE.POSITION_CODE = TB_POSITION_CODE.POSITION_CODE ";
  520. SQL += "WHERE TB_MAP_PROCESS.MAP_RECEIVER_ID = '" + RECEIVER_ID + "' AND";
  521. SQL += " TB_MAP_PROCESS.MAP_NO = '" + MAP_NO + "' AND TB_MAP_PROCESS.MAP_TYPE = '" + MAP_TYPE + "' ";
  522. }
  523. return SQL;
  524. }
  525. public static string Search_Map_ProcessTable(string MAP_NO, string MAP_TYPE, string PROCESS_TYPE, string RECEIVER_ID)
  526. {
  527. // 적용테이블 :
  528. // "TB_MAP_PROCESS" - RECEIVER_ID 있음
  529. string SQL = null;
  530. if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
  531. {
  532. SQL += "SELECT TB_DEVICE_TYPE.DEVICE_TYPE_NAME, TB_POSITION_CODE.POSITION_NAME, TB_DEVICE.DEVICE_NAME, TB_MAP_PROCESS.SEQ_NO, TB_MAP_PROCESS.PROCESS_TYPE, TB_MAP_PROCESS.GROUP_ID, TB_DEVICE.* ";
  533. SQL += "FROM TB_MAP_PROCESS ";
  534. SQL += "LEFT OUTER JOIN TB_DEVICE ON TB_MAP_PROCESS.RECEIVER_ID = TB_DEVICE.RECEIVER_ID AND TB_MAP_PROCESS.COMM_ID = TB_DEVICE.COMM_ID ";
  535. SQL += "AND TB_MAP_PROCESS.BOARD_ID = TB_DEVICE.BOARD_ID AND TB_MAP_PROCESS.LOOP_NO = TB_DEVICE.LOOP_NO ";
  536. SQL += "AND TB_MAP_PROCESS.REPEATER_ID = TB_DEVICE.REPEATER_ID AND TB_MAP_PROCESS.DEVICE_ID = TB_DEVICE.DEVICE_ID ";
  537. SQL += "AND TB_MAP_PROCESS.INOUT_TYPE = TB_DEVICE.INOUT_TYPE ";
  538. SQL += "LEFT OUTER JOIN TB_DEVICE_TYPE ON TB_DEVICE.DEVICE_TYPE = TB_DEVICE_TYPE.DEVICE_TYPE ";
  539. SQL += "LEFT OUTER JOIN TB_POSITION_CODE ON TB_DEVICE.POSITION_CODE = TB_POSITION_CODE.POSITION_CODE ";
  540. SQL += "WHERE TB_MAP_PROCESS.MAP_NO = '" + MAP_NO + "' AND TB_MAP_PROCESS.MAP_TYPE = '" + MAP_TYPE + "' AND TB_MAP_PROCESS.PROCESS_TYPE = '" + PROCESS_TYPE + "' ";
  541. }
  542. else
  543. {
  544. SQL += "SELECT TB_DEVICE_TYPE.DEVICE_TYPE_NAME, TB_POSITION_CODE.POSITION_NAME, TB_DEVICE.DEVICE_NAME, TB_MAP_PROCESS.SEQ_NO, TB_MAP_PROCESS.PROCESS_TYPE, TB_MAP_PROCESS.GROUP_ID, TB_DEVICE.* ";
  545. SQL += "FROM TB_MAP_PROCESS ";
  546. SQL += "LEFT OUTER JOIN TB_DEVICE ON TB_MAP_PROCESS.RECEIVER_ID = TB_DEVICE.RECEIVER_ID AND TB_MAP_PROCESS.COMM_ID = TB_DEVICE.COMM_ID ";
  547. SQL += "AND TB_MAP_PROCESS.BOARD_ID = TB_DEVICE.BOARD_ID AND TB_MAP_PROCESS.LOOP_NO = TB_DEVICE.LOOP_NO ";
  548. SQL += "AND TB_MAP_PROCESS.REPEATER_ID = TB_DEVICE.REPEATER_ID AND TB_MAP_PROCESS.DEVICE_ID = TB_DEVICE.DEVICE_ID ";
  549. SQL += "AND TB_MAP_PROCESS.INOUT_TYPE = TB_DEVICE.INOUT_TYPE ";
  550. SQL += "LEFT OUTER JOIN TB_DEVICE_TYPE ON TB_DEVICE.DEVICE_TYPE = TB_DEVICE_TYPE.DEVICE_TYPE ";
  551. SQL += "LEFT OUTER JOIN TB_POSITION_CODE ON TB_DEVICE.POSITION_CODE = TB_POSITION_CODE.POSITION_CODE ";
  552. SQL += "WHERE TB_MAP_PROCESS.MAP_RECEIVER_ID = '" + RECEIVER_ID + "' AND";
  553. SQL += " TB_MAP_PROCESS.MAP_NO = '" + MAP_NO + "' AND TB_MAP_PROCESS.MAP_TYPE = '" + MAP_TYPE + "' AND TB_MAP_PROCESS.PROCESS_TYPE = '" + PROCESS_TYPE + "' ";
  554. }
  555. return SQL;
  556. }
  557. public static string Search_Board_Table(int ReceiverID, int CommID, int BoardID)
  558. {
  559. // 적용테이블 :
  560. // "TB_BOARD" - RECEIVER_ID 있음
  561. string SQL = null;
  562. SQL += "SELECT * ";
  563. SQL += "FROM TB_BOARD ";
  564. SQL += "WHERE RECEIVER_ID = " + ReceiverID.ToString() + " and COMM_ID = " + CommID.ToString() + " and BOARD_ID = " + BoardID.ToString();
  565. return SQL;
  566. }
  567. public static string TB_RECEIVER_table_Insert(int iReceiverID, string sReceiverName, string sIP, int iEventPort, int iCommandPort, string sUseFlag)
  568. {
  569. // 적용테이블 :
  570. // "TB_RECEIVER" - RECEIVER_ID 있음
  571. string SQL = null;
  572. SQL = "INSERT INTO TB_RECEIVER ";
  573. SQL += "(RECEIVER_ID,RECEIVER_NAME,IP_ADDRESS,EVENT_PORT_NO,COMMAND_PORT_NO,REDUNDANT_FLAG,USE_FLAG,UPDATE_USER_ID,UPDATE_TIME)";
  574. SQL += " VALUES ({0},'{1}','{2}',{3},{4},'{5}','{6}','FireManager',current_timestamp)";
  575. SQL = String.Format(SQL, iReceiverID, sReceiverName, sIP, iEventPort, iCommandPort, "N", sUseFlag);
  576. return SQL;
  577. }
  578. public static string TB_COMM_table_Insert(int iReceiverID, int iCommID, string sCommName, int iCommLoop, string sCommPort, int iBaudRate, string sParity,
  579. int iDataBit, string sStopBit, int iTimeOut, int iDelay, int iDeltaVolt, int iRetryCount, string sUseFlag)
  580. {
  581. // 적용테이블 :
  582. // "TB_COMM" - RECEIVER_ID 있음
  583. string SQL = null;
  584. SQL = "INSERT INTO TB_COMM ( ";
  585. SQL += " RECEIVER_ID,COMM_ID,COMM_NAME,COMM_LOOP,COMM_TYPE,COMM_PORT";
  586. SQL += ",BAUD_RATE,PARITY,DATA_BITS,STOP_BITS";
  587. SQL += ",COMM_TIMEOUT,COMM_DELAY, DELTA_VOLT, RETRY_COUNT, LOG_FLAG, USE_FLAG,UPDATE_USER_ID,UPDATE_TIME";
  588. SQL += ") VALUES ({0},{1},'{2}','{3}','R','{4}',{5},'{6}',{7}, {8}, {9},{10}, {11}, {12}, 'N','{13}','FireManager',current_timestamp)";
  589. SQL = String.Format(SQL, iReceiverID, iCommID, sCommName, iCommLoop, sCommPort, iBaudRate, sParity.Substring(0, 1), iDataBit, sStopBit, iTimeOut, iDelay, iDeltaVolt, iRetryCount, sUseFlag);
  590. return SQL;
  591. }
  592. public static string TB_BOARD_table_Insert(int iReceiverID, int iCommID, int iBoardID, string sBoardName, string sCH1_UseFlag, string sCH2_UseFlag, string sUseFlag)
  593. {
  594. // 적용테이블 :
  595. // "TB_BOARD" - RECEIVER_ID 있음
  596. string SQL = null;
  597. SQL = "INSERT INTO TB_BOARD ( ";
  598. SQL += " RECEIVER_ID,COMM_ID,BOARD_ID,BOARD_NAME,LOOP0_USE_FLAG,LOOP1_USE_FLAG ";
  599. SQL += ",USE_FLAG, LOOP0_CUT_FLAG, LOOP1_CUT_FLAG, UPDATE_USER_ID,UPDATE_TIME";
  600. SQL += ") VALUES ({0}, {1}, {2},'{3}','{4}','{5}','{6}','N','N','FireManager',current_timestamp)";
  601. SQL = String.Format(SQL, iReceiverID, iCommID, iBoardID, sBoardName, sCH1_UseFlag, sCH2_UseFlag, sUseFlag);
  602. return SQL;
  603. }
  604. public static string TB_CONFIG_table_Insert(int iReceiverID, string sFireAccumulationMode, int iFireAccumulationTime, string sFireProcessMode, string sMASTER_USER_ID, string sMASTER_PASSWORD, string sBUILDING_NAME, string sJumpField, string sMASTER_FLAG ,string sTEST_RUN_FLAG)
  605. {
  606. // 적용테이블 :
  607. // "TB_CONFIG" - RECEIVER_ID 있음
  608. string SQL = null;
  609. SQL = "INSERT INTO TB_CONFIG (RECEIVER_ID,FIRE_ACCUMULATION_MODE,FIRE_ACCUMULATION_TIME";
  610. SQL += ",FIRE_PROCESS_MODE,SOUND_PROCESS_MODE,MASTER_USER_ID,MASTER_PASSWORD";
  611. SQL += ",BUILDING_NAME, JUMP_FIELD, MASTER_FLAG, UPDATE_USER_ID,TEST_RUN_FLAG, UPDATE_TIME)";
  612. SQL += " VALUES ({0},'{1}',{2},'{3}','N','{4}','{5}','{6}','{7}','{8}', 'FireManager','{9}',current_timestamp)";
  613. SQL = String.Format(SQL, iReceiverID, sFireAccumulationMode, iFireAccumulationTime
  614. , sFireProcessMode, sMASTER_USER_ID, sMASTER_PASSWORD
  615. , sBUILDING_NAME, sJumpField, sMASTER_FLAG, sTEST_RUN_FLAG); // 네트워크 수신기 설정 기능 추가 (true : 마스터, false : 슬레이브)
  616. return SQL;
  617. }
  618. // 특정 Table의 모든 레코드 삭제하기
  619. public static string Delete_Table(string TableName, string RECEIVER_ID)
  620. {
  621. // 적용테이블 :
  622. // "TB_BOARD" "TB_COMM" "TB_CONFIG" "TB_RECEIVER" "TB_DEVICE" "TB_REPEATER" "TB_EMERGENCY_BROADCASTION" - RECEIVER_ID 있음
  623. // "TB_POSITION_CODE" - RECEIVER_ID 없음
  624. string SQL = null;
  625. if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
  626. {
  627. SQL = "DELETE " +
  628. "FROM " + TableName + ";";
  629. }
  630. else
  631. {
  632. SQL = "DELETE " +
  633. "FROM " + TableName + " " +
  634. "WHERE " + TableName + "." + "RECEIVER_ID" + " = '" + RECEIVER_ID + "';";
  635. }
  636. return SQL;
  637. }
  638. // Where절을 이용하여 특정 Table의 레코드 삭제하기
  639. public static string Delete_Table(string TableName, string Column, string Value, string RECEIVER_ID)
  640. {
  641. // 적용테이블 :
  642. // "TB_GROUP" "TB_GROUP_DEVICE" "TB_MAP" "TB_MAP_PROCESS" - RECEIVER_ID 있음
  643. string SQL = null;
  644. if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
  645. {
  646. SQL = "DELETE " +
  647. "FROM " + TableName + " " +
  648. "WHERE " + TableName + "." + Column + " = '" + Value + "';";
  649. }
  650. else
  651. {
  652. SQL = "DELETE " +
  653. "FROM " + TableName + " " +
  654. "WHERE " + TableName + "." + "RECEIVER_ID" + " = '" + RECEIVER_ID + "' and " +
  655. TableName + "." + Column + " = '" + Value + "';";
  656. }
  657. return SQL;
  658. }
  659. // Where절을 이용하여 특정 Table의 레코드 삭제하기
  660. public static string Delete_Table(string TableName, string Column1, string Value1, string Column2, string Value2, string RECEIVER_ID)
  661. {
  662. // 적용테이블 :
  663. // "TB_MAP_PROCESS" - RECEIVER_ID 있음
  664. string SQL = null;
  665. if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
  666. {
  667. SQL = "DELETE " +
  668. "FROM " + TableName + " " +
  669. "WHERE " + TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "';";
  670. }
  671. else
  672. {
  673. SQL = "DELETE " +
  674. "FROM " + TableName + " " +
  675. "WHERE " + TableName + "." + "RECEIVER_ID" + " = '" + RECEIVER_ID + "' and " +
  676. TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "';";
  677. }
  678. return SQL;
  679. }
  680. // Where절을 이용하여 특정 Table의 레코드 삭제하기
  681. public static string Delete_Table(string TableName, string Column1, string Value1, string Column2, string Value2, string Column3, string Value3, string RECEIVER_ID)
  682. {
  683. // 적용테이블 :
  684. // "TB_MAP_PROCESS" - RECEIVER_ID 있음
  685. string SQL = null;
  686. if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
  687. {
  688. SQL = "DELETE " +
  689. "FROM " + TableName + " " +
  690. "WHERE " + TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "' and " +
  691. TableName + "." + Column3 + " = '" + Value3 + "';";
  692. }
  693. else
  694. {
  695. SQL = "DELETE " +
  696. "FROM " + TableName + " " +
  697. "WHERE " + TableName + "." + "RECEIVER_ID" + " = '" + RECEIVER_ID + "' and " +
  698. TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "' and " +
  699. TableName + "." + Column3 + " = '" + Value3 + "';";
  700. }
  701. return SQL;
  702. }
  703. // Where절을 이용하여 특정 Table의 레코드 삭제하기
  704. // TB_GROUP_DEVICE 테이블에 특성화되어 있음
  705. public static string Delete_Table(string TableName, string Column1, string Value1, string Column2, int Value2,
  706. string Column3, int Value3, string Column4, int Value4,
  707. string Column5, int Value5, string Column6, int Value6,
  708. string Column7, string Value7, string RECEIVER_ID)
  709. {
  710. // 적용테이블 :
  711. // "TB_EMERGENCY_BROADCASTION" "TB_GROUP_DEVICE" "TB_MAP_PROCESS" - RECEIVER_ID 있음
  712. string SQL = null;
  713. if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
  714. {
  715. SQL = "DELETE " +
  716. "FROM " + TableName + " " +
  717. "WHERE " + TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "' and " +
  718. TableName + "." + Column3 + " = '" + Value3 + "' and " + TableName + "." + Column4 + " = '" + Value4 + "' and " +
  719. TableName + "." + Column5 + " = '" + Value5 + "' and " + TableName + "." + Column6 + " = '" + Value6 + "' and " +
  720. TableName + "." + Column7 + " = '" + Value7 + "';";
  721. }
  722. else
  723. {
  724. SQL = "DELETE " +
  725. "FROM " + TableName + " " +
  726. "WHERE " + TableName + "." + "RECEIVER_ID" + " = '" + RECEIVER_ID + "' and " +
  727. TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "' and " +
  728. TableName + "." + Column3 + " = '" + Value3 + "' and " + TableName + "." + Column4 + " = '" + Value4 + "' and " +
  729. TableName + "." + Column5 + " = '" + Value5 + "' and " + TableName + "." + Column6 + " = '" + Value6 + "' and " +
  730. TableName + "." + Column7 + " = '" + Value7 + "';";
  731. }
  732. return SQL;
  733. }
  734. public static string Delete_Table(string TableName, string Column1, string Value1, string Column2, int Value2,
  735. string Column3, int Value3, string Column4, int Value4,
  736. string Column5, int Value5, string Column6, int Value6,
  737. string Column7, string Value7, string Column8, string Value8, string RECEIVER_ID)
  738. {
  739. // 적용테이블 :
  740. // "TB_EMERGENCY_BROADCASTION" "TB_GROUP_DEVICE" "TB_MAP_PROCESS" - RECEIVER_ID 있음
  741. string SQL = null;
  742. if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
  743. {
  744. SQL = "DELETE " +
  745. "FROM " + TableName + " " +
  746. "WHERE " + TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "' and " +
  747. TableName + "." + Column3 + " = '" + Value3 + "' and " + TableName + "." + Column4 + " = '" + Value4 + "' and " +
  748. TableName + "." + Column5 + " = '" + Value5 + "' and " + TableName + "." + Column6 + " = '" + Value6 + "' and " +
  749. TableName + "." + Column7 + " = '" + Value7 + "' and " + TableName + "." + Column8 + " = '" + Value8 + "';";
  750. }
  751. else
  752. {
  753. SQL = "DELETE " +
  754. "FROM " + TableName + " " +
  755. "WHERE " + TableName + "." + "RECEIVER_ID" + " = '" + RECEIVER_ID + "' and " +
  756. TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "' and " +
  757. TableName + "." + Column3 + " = '" + Value3 + "' and " + TableName + "." + Column4 + " = '" + Value4 + "' and " +
  758. TableName + "." + Column5 + " = '" + Value5 + "' and " + TableName + "." + Column6 + " = '" + Value6 + "' and " +
  759. TableName + "." + Column7 + " = '" + Value7 + "' and " + TableName + "." + Column8 + " = '" + Value8 + "';";
  760. }
  761. return SQL;
  762. }
  763. // TB_DEVICE 전체 카운트
  764. public static string Count_TB_DEVICE(string COMM_ID, string BOARD_ID, string INOUT_TYPE, string RECEIVER_ID)
  765. {
  766. string SQL = "SELECT count(*) FROM TB_DEVICE WHERE TB_DEVICE.COMM_ID = '" + COMM_ID + "' AND TB_DEVICE.BOARD_ID < " + BOARD_ID + " AND TB_DEVICE.INOUT_TYPE = '" + INOUT_TYPE + "' AND TB_DEVICE.RECEIVER_ID = '" + RECEIVER_ID + "' ";
  767. return SQL;
  768. }
  769. // 맵연동파일 생성용 쿼리
  770. public static string MapProcessFile_Create(string RECEIVER_ID)
  771. {
  772. // 맵연동설정파일 포맷
  773. // - 한개의 레코드 길이 : 40 Byte (개행문자 포함)
  774. // - 구분자 는 |
  775. // - 마지막에 개행문자 (CR/LF)
  776. // - 순서 : A.MAP_NO,A.MAP_TYPE (ASC)
  777. // 1. RECEIVER ID : 3 Bytes (3자리가 아닐 경우 왼쪽 0으로 채움)
  778. // 2. MAP NO. : 7 Bytes (7자리가 아닐 경우 왼쪽 0으로 채움) // cyim 2015.10.30 : 포맷변경 포함 5->7, 즉 2번째 Map no 가 7자리로 변경
  779. // 3. MAP TYPE : 1 Bytes (NULL일 경우 공백으로 채움)
  780. // 4. PROCESS TYPE : 1 Bytes (NULL일 경우 공백으로 채움)
  781. // 5. COMM. ID : 3 Bytes (3자리가 아닐 경우 왼쪽 0으로 채움)
  782. // 6. BOARD ID : 3 Bytes (3자리가 아닐 경우 왼쪽 0으로 채움)
  783. // 7. LOOP NO. : 3 Bytes (3자리가 아닐 경우 왼쪽 0으로 채움)
  784. // 8. REPEATER ID : 3 Bytes (3자리가 아닐 경우 왼쪽 0으로 채움)
  785. // 9. DEVICE ID : 3 Bytes (3자리가 아닐 경우 왼쪽 0으로 채움)
  786. // 10. IN/OUT TYPE : 1 Bytes (NULL일 경우 공백으로 채움)
  787. // 11. USE FLAG : 1 Bytes (NULL일 경우 공백으로 채움)
  788. // 12. CR/LF : 2 Bytes
  789. // ex) C 소스코드
  790. // // MAP DATA 구성
  791. // sprintf (szMapData,"%03d|%07d|%c|%c|%03d|%03d|%03d|%03d|%03d|%c|%c|\r\n", // cyim 2015.10.30 : 포맷변경 포함 5->7, 즉 2번째 Map no 가 7자리로 변경
  792. // nReceiverID,nMapNo,
  793. // (szMapType[0]!='\0'?szMapType[0]:' '),
  794. // (szProcessType[0]!='\0'?szProcessType[0]:' '),
  795. // nCommID,nBoardID,nLoopNo,nRepeaterID,nDeviceID,
  796. // (szInoutType[0]!='\0'?szInoutType[0]:' '),
  797. // (szUseFlag[0]!='\0'?szUseFlag[0]:' '));
  798. // ex) 001|00001|I|D|001|001|000|002|001|I|Y|
  799. string SQL = "SELECT (CASE WHEN A.PROCESS_TYPE='G' THEN B.RECEIVER_ID ELSE A.RECEIVER_ID END) as RECEIVER_ID,";
  800. SQL = SQL + " A.MAP_NO,A.MAP_TYPE,A.PROCESS_TYPE,";
  801. SQL = SQL + " (CASE WHEN A.PROCESS_TYPE='G' THEN B.COMM_ID ELSE A.COMM_ID END) as COMM_ID,";
  802. SQL = SQL + " (CASE WHEN A.PROCESS_TYPE='G' THEN B.BOARD_ID ELSE A.BOARD_ID END) as BOARD_ID,";
  803. SQL = SQL + " (CASE WHEN A.PROCESS_TYPE='G' THEN B.LOOP_NO ELSE A.LOOP_NO END) as LOOP_NO,";
  804. SQL = SQL + " (CASE WHEN A.PROCESS_TYPE='G' THEN B.REPEATER_ID ELSE A.REPEATER_ID END) as REPEATER_ID,";
  805. SQL = SQL + " (CASE WHEN A.PROCESS_TYPE='G' THEN B.DEVICE_ID ELSE A.DEVICE_ID END) as DEVICE_ID,";
  806. SQL = SQL + " (CASE WHEN A.PROCESS_TYPE='G' THEN B.INOUT_TYPE ELSE A.INOUT_TYPE END) as INOUT_TYPE,";
  807. SQL = SQL + " (CASE WHEN A.USE_FLAG='Y' AND IIF(A.PROCESS_TYPE='G',B.USE_FLAG,'Y')='Y' AND C.USE_FLAG='Y' THEN 'Y' ELSE 'N' END) as USE_FLAG ";
  808. SQL = SQL + " FROM TB_MAP_PROCESS A ";
  809. SQL = SQL + " LEFT OUTER JOIN TB_MAP C ON (A.MAP_RECEIVER_ID=C.RECEIVER_ID AND A.MAP_NO=C.MAP_NO) ";
  810. SQL = SQL + " LEFT OUTER JOIN TB_GROUP_DEVICE B ON (A.MAP_RECEIVER_ID=B.GROUP_RECEIVER_ID AND A.GROUP_ID=B.GROUP_ID) ";
  811. SQL = SQL + " WHERE A.MAP_RECEIVER_ID = " + RECEIVER_ID;
  812. SQL = SQL + " ORDER BY A.MAP_RECEIVER_ID,A.MAP_NO,A.MAP_TYPE";
  813. return SQL;
  814. }
  815. // 테이블 존재 여부
  816. public static string Table_Exist(string TABLE_NAME)
  817. {
  818. string SQL = "select rdb$relation_name ";
  819. SQL = SQL + "from rdb$relations ";
  820. SQL = SQL + "where rdb$system_flag=0 ";
  821. SQL = SQL + "and rdb$relation_name = '" + TABLE_NAME + "'";
  822. return SQL;
  823. }
  824. // 아날로그 감지기 용 테이블 생성
  825. public static string Create_TB_DAYTIME()
  826. {
  827. string SQL = "CREATE TABLE \"TB_DAYTIME\"";
  828. SQL = SQL + "(";
  829. SQL = SQL + "\"START_HOUR\" SMALLINT NOT NULL, ";
  830. SQL = SQL + "\"START_MIN\" SMALLINT NOT NULL, ";
  831. SQL = SQL + "\"END_HOUR\" SMALLINT NOT NULL, ";
  832. SQL = SQL + "\"END_MIN\" SMALLINT NOT NULL ";
  833. SQL = SQL + ");";
  834. return SQL;
  835. }
  836. public static string Create_TB_ANALOG_DEVICE_2()
  837. {
  838. string SQL = "CREATE TABLE \"TB_ANALOG_DEVICE_2\"";
  839. SQL = SQL + "(";
  840. SQL = SQL + "\"RECEIVER_ID\" INTEGER NOT NULL, ";
  841. SQL = SQL + "\"COMM_ID\" INTEGER NOT NULL, ";
  842. SQL = SQL + "\"BOARD_ID\" INTEGER NOT NULL, ";
  843. SQL = SQL + "\"LOOP_NO\" INTEGER NOT NULL, ";
  844. SQL = SQL + "\"REPEATER_ID\" INTEGER NOT NULL, ";
  845. SQL = SQL + "\"DEVICE_ID\" INTEGER NOT NULL, ";
  846. SQL = SQL + "\"INOUT_TYPE\" VARCHAR(1) NOT NULL, ";
  847. SQL = SQL + "\"FAULT_VALUE\" INTEGER NOT NULL, ";
  848. SQL = SQL + "\"PREFIRE_VALUE\" INTEGER NOT NULL, ";
  849. SQL = SQL + "\"FIRED_VALUE\" INTEGER NOT NULL, ";
  850. SQL = SQL + "\"FIREN_VALUE\" INTEGER NOT NULL, ";
  851. SQL = SQL + "\"UPDATE_USER_ID\" VARCHAR(10), ";
  852. SQL = SQL + "\"UPDATE_TIME\" TIMESTAMP, ";
  853. SQL = SQL + "CONSTRAINT \"PK_TB_ANALOG_DEVICE2\" PRIMARY KEY (\"RECEIVER_ID\", \"COMM_ID\", \"BOARD_ID\", \"LOOP_NO\", \"REPEATER_ID\", \"DEVICE_ID\", \"INOUT_TYPE\") ";
  854. SQL = SQL + ");";
  855. return SQL;
  856. }
  857. }
  858. }