f7ce37affd7c0e78c74e4d019c0590bd9e11f21f.svn-base 66 KB

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