29aa2382fa14c0dfbc6977f5d9c40e83d84ce5c8.svn-base 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Text;
  4. using FirebirdSql.Data.FirebirdClient;
  5. using System.Data;
  6. using System.Collections;
  7. namespace FPER
  8. {
  9. /*******************************************************************************************
  10. * TB_REPEATER(중계기) 설정 관련 Dac
  11. *******************************************************************************************/
  12. public class DacRepeaterConfig : DacBase
  13. {
  14. public DacRepeaterConfig(int ID)
  15. : base(ID) // cyim 2015.7.30 데이타베이스 접속 루틴 변경
  16. {
  17. //try {
  18. // base.Connect();
  19. //}
  20. //catch (Exception ex) {
  21. // Util.UErrorMessage(ex, 0, 0);
  22. //}
  23. }
  24. ~DacRepeaterConfig()
  25. {
  26. //try {
  27. // base.DisConnect();
  28. //}
  29. //catch (Exception ex) {
  30. // Util.UErrorMessage(ex, 0, 0);
  31. //}
  32. }
  33. //중계기조회
  34. public DataTable Repeater_Select(int RECEIVER_ID, int BOARD_ID, int LOOP_NO, int REPEATER_ID, String UseFlag)
  35. {
  36. DataTable dt = null;
  37. try
  38. {
  39. String SQL = "";
  40. //RECEIVER_ID INTEGER NOT NULL, /* RECEIVER ID (1 - 32) */
  41. //COMM_ID INTEGER NOT NULL, /* COMM ID (1 - 4) */
  42. //BOARD_ID INTEGER NOT NULL, /* BOARD ID (1 - 16) */
  43. //LOOP_NO INTEGER NOT NULL, /* LOOP NO. (0 - 1) */
  44. //REPEATER_ID INTEGER NOT NULL, /* REPEATER ID (1 - 126) */
  45. //REPEATER_NAME VARCHAR(40), /* REPEATER NAME */
  46. //REPEATER_TYPE VARCHAR(1), /* REPEATER TYPE */
  47. // /* '1':2X2, '2':4X4 */
  48. // /* '4': 아날로그연감지기 */
  49. // /* '5': 아날로그열감지기 */
  50. //USE_FLAG VARCHAR(1), /* USE FLAG (Y/N) */
  51. if (REPEATER_ID < 1) // Repeater ID가 0이면.. 전체 중계기 값을 가져옴.
  52. {
  53. SQL += "SELECT REPEATER_ID,REPEATER_NAME,REPEATER_TYPE,USE_FLAG,CUT_FLAG,POSITION_CODE";
  54. SQL += " FROM TB_REPEATER WHERE RECEIVER_ID = {0} AND COMM_ID = 1 AND BOARD_ID = {1} AND LOOP_NO = {2} ";
  55. if (UseFlag != null) SQL += " AND USE_FLAG = '" + UseFlag + "'";
  56. SQL += " ORDER BY REPEATER_ID";
  57. SQL = String.Format(SQL, RECEIVER_ID, BOARD_ID, LOOP_NO);
  58. }
  59. else
  60. {
  61. SQL += "SELECT REPEATER_ID,REPEATER_NAME,REPEATER_TYPE,USE_FLAG,CUT_FLAG,POSITION_CODE";
  62. SQL += " FROM TB_REPEATER WHERE RECEIVER_ID = {0} AND COMM_ID = 1 AND BOARD_ID = {1} AND LOOP_NO = {2} AND REPEATER_ID = {3}";
  63. if (UseFlag != null) SQL += " AND USE_FLAG = '" + UseFlag + "'";
  64. SQL = String.Format(SQL, RECEIVER_ID, BOARD_ID, LOOP_NO, REPEATER_ID);
  65. }
  66. DataSet dao = Select(SQL);
  67. dt = dao.Tables[0];
  68. }
  69. catch (Exception ex)
  70. {
  71. Util.UErrorMessage(ex, 0, 0);
  72. throw ex;
  73. }
  74. return dt;
  75. }
  76. public DataTable SelectRepeater(int RECEIVER_ID)
  77. {
  78. DataTable dt = null;
  79. try
  80. {
  81. String SQL = "";
  82. //RECEIVER_ID INTEGER NOT NULL, /* RECEIVER ID (1 - 32) */
  83. //COMM_ID INTEGER NOT NULL, /* COMM ID (1 - 4) */
  84. //BOARD_ID INTEGER NOT NULL, /* BOARD ID (1 - 16) */
  85. //LOOP_NO INTEGER NOT NULL, /* LOOP NO. (0 - 1) */
  86. //REPEATER_ID INTEGER NOT NULL, /* REPEATER ID (1 - 126) */
  87. //REPEATER_NAME VARCHAR(40), /* REPEATER NAME */
  88. //REPEATER_TYPE VARCHAR(1), /* REPEATER TYPE */
  89. // /* '1':2X2, '2':4X4 */
  90. // /* '4': 아날로그연감지기 */
  91. // /* '5': 아날로그열감지기 */
  92. //USE_FLAG VARCHAR(1), /* USE FLAG (Y/N) */
  93. SQL += "SELECT REPEATER_ID,REPEATER_NAME,REPEATER_TYPE,BOARD_ID,LOOP_NO,USE_FLAG,CUT_FLAG,POSITION_CODE";
  94. SQL += " FROM TB_REPEATER WHERE RECEIVER_ID = {0} AND COMM_ID = 1 ";
  95. SQL += " AND USE_FLAG = 'Y'";
  96. SQL += " ORDER BY REPEATER_ID";
  97. SQL = String.Format(SQL, RECEIVER_ID);
  98. DataSet dao = Select(SQL);
  99. dt = dao.Tables[0];
  100. }
  101. catch (Exception ex)
  102. {
  103. Util.UErrorMessage(ex, 0, 0);
  104. throw ex;
  105. }
  106. return dt;
  107. }
  108. public DataTable SelectRepeater2(int RECEIVER_ID)
  109. {
  110. DataTable dt = null;
  111. try
  112. {
  113. String SQL = "";
  114. //RECEIVER_ID INTEGER NOT NULL, /* RECEIVER ID (1 - 32) */
  115. //COMM_ID INTEGER NOT NULL, /* COMM ID (1 - 4) */
  116. //BOARD_ID INTEGER NOT NULL, /* BOARD ID (1 - 16) */
  117. //LOOP_NO INTEGER NOT NULL, /* LOOP NO. (0 - 1) */
  118. //REPEATER_ID INTEGER NOT NULL, /* REPEATER ID (1 - 126) */
  119. //REPEATER_NAME VARCHAR(40), /* REPEATER NAME */
  120. //REPEATER_TYPE VARCHAR(1), /* REPEATER TYPE */
  121. // /* '1':2X2, '2':4X4 */
  122. // /* '4': 아날로그연감지기 */
  123. // /* '5': 아날로그열감지기 */
  124. //USE_FLAG VARCHAR(1), /* USE FLAG (Y/N) */
  125. SQL += "SELECT R.REPEATER_ID, R.REPEATER_NAME, R.REPEATER_TYPE, R.BOARD_ID, R.LOOP_NO, R.CUT_FLAG, R.POSITION_CODE, P.POSITION_NAME";
  126. SQL += " FROM TB_REPEATER R LEFT OUTER JOIN TB_POSITION_CODE P ON R.POSITION_CODE = P.POSITION_CODE ";
  127. SQL += " WHERE RECEIVER_ID = {0} AND COMM_ID = 1 ";
  128. SQL += " AND USE_FLAG = 'Y'";
  129. SQL += " ORDER BY REPEATER_ID";
  130. SQL = String.Format(SQL, RECEIVER_ID);
  131. DataSet dao = Select(SQL);
  132. dt = dao.Tables[0];
  133. }
  134. catch (Exception ex)
  135. {
  136. Util.UErrorMessage(ex, 0, 0);
  137. throw ex;
  138. }
  139. return dt;
  140. }
  141. // 실제로 테이블이 존재하는지 체크하는 함수
  142. public bool Table_Exist(string TABLE_NAME)
  143. {
  144. DataTable dt = null;
  145. try
  146. {
  147. string SQL = "select rdb$relation_name ";
  148. SQL = SQL + "from rdb$relations ";
  149. SQL = SQL + "where rdb$system_flag=0 ";
  150. SQL = SQL + "and rdb$relation_name = '" + TABLE_NAME + "'";
  151. SQL = String.Format(SQL);
  152. DataSet dao = Select(SQL);
  153. dt = dao.Tables[0];
  154. if (dt.Rows.Count != 0)
  155. return true;
  156. }
  157. catch (Exception ex)
  158. {
  159. Util.UErrorMessage(ex, 0, 0);
  160. }
  161. return false;
  162. }
  163. // cyim 2015.7.2 해쉬테이블로 아날로그 감지기 레벨을 아이디별로 저장해둔다 : 아날로그 감지기 레벨값
  164. public DataTable SelectRepeater_AnalogDetecterLevel(int RECEIVER_ID)
  165. {
  166. DataTable dt = null;
  167. if (Table_Exist("TB_ANALOG_DEVICE_2") == true) // cyim 2015.7.10 테이블이 존재하는지 먼저 살펴보도록함
  168. {
  169. try
  170. {
  171. String SQL = "";
  172. SQL += "SELECT R.RECEIVER_ID, R.BOARD_ID, R.LOOP_NO, R.REPEATER_ID, R.REPEATER_NAME, R.REPEATER_TYPE, R.CUT_FLAG, P.FAULT_VALUE, P.PREFIRE_VALUE, P.FIRED_VALUE, P.FIREN_VALUE ";
  173. SQL += " FROM TB_REPEATER R LEFT OUTER JOIN TB_ANALOG_DEVICE_2 P ";
  174. SQL += " ON (R.RECEIVER_ID = P.RECEIVER_ID AND R.COMM_ID = P.COMM_ID AND R.BOARD_ID = P.BOARD_ID AND R.LOOP_NO = P.LOOP_NO AND R.REPEATER_ID = P.REPEATER_ID) ";
  175. SQL += " WHERE R.RECEIVER_ID = {0} AND R.COMM_ID = 1 ";
  176. SQL += " AND R.USE_FLAG = 'Y' AND (R.REPEATER_TYPE = 4 OR R.REPEATER_TYPE = 5)";
  177. // cyim 2016.06.15 : 아날로그 감지기 출력 포인트 고려 : 레벨값은 입력포인트만 고려한다
  178. // 출력포인트가 있을경우에는 레벨값 조회시 중계기 아이디가 중복되어 버린다
  179. SQL += " AND INOUT_TYPE ='I' ";
  180. SQL = String.Format(SQL, RECEIVER_ID);
  181. DataSet dao = Select(SQL);
  182. dt = dao.Tables[0];
  183. }
  184. catch (Exception ex)
  185. {
  186. Util.UErrorMessage(ex, 0, 0);
  187. }
  188. }
  189. return dt;
  190. }
  191. //중계기저장
  192. public void Repeater_Clear(int RECEIVER_ID)
  193. {
  194. try
  195. {
  196. //String SQL = "UPDATE TB_REPEATER SET USE_FLAG = 'N' WHERE RECEIVER_ID = {0} AND COMM_ID = 1";
  197. //SQL = String.Format(SQL, RECEIVER_ID);
  198. String SQL = "DELETE FROM TB_DEVICE WHERE RECEIVER_ID = {0} AND COMM_ID = 1";
  199. SQL = String.Format(SQL, RECEIVER_ID);
  200. ExecuteNonQuery(SQL);
  201. SQL = "DELETE FROM TB_REPEATER WHERE RECEIVER_ID = {0} AND COMM_ID = 1";
  202. SQL = String.Format(SQL, RECEIVER_ID);
  203. ExecuteNonQuery(SQL);
  204. }
  205. catch (Exception ex)
  206. {
  207. Util.UErrorMessage(ex, 0, 0);
  208. throw ex;
  209. }
  210. }
  211. //중계기저장
  212. public void Repeater_Update(QUERYMode cmd, int RECEIVER_ID, int BOARD_ID, int LOOP_NO, int REPEATER_ID
  213. , String REPEATER_NAME, String REPEATER_TYPE, String USE_FLAG)
  214. {
  215. try
  216. {
  217. DataTable dt = Repeater_Select(RECEIVER_ID, BOARD_ID, LOOP_NO, REPEATER_ID, null);
  218. if (cmd == QUERYMode.insert || cmd == QUERYMode.update || cmd == QUERYMode.InsertAndUpdate)
  219. {
  220. String SQL = "";
  221. if (dt.Rows.Count > 0)
  222. {
  223. SQL = "UPDATE TB_REPEATER SET REPEATER_NAME = '{0}'";
  224. SQL += ", REPEATER_TYPE = '{1}', USE_FLAG = '{2}'";
  225. SQL += ", UPDATE_USER_ID = 'FPER', UPDATE_TIME = current_timestamp";
  226. SQL += " WHERE RECEIVER_ID = {3} AND COMM_ID = 1 AND BOARD_ID = {4} AND LOOP_NO = {5} AND REPEATER_ID = {6}";
  227. SQL = String.Format(SQL, REPEATER_NAME, REPEATER_TYPE, USE_FLAG, RECEIVER_ID, BOARD_ID, LOOP_NO, REPEATER_ID);
  228. }
  229. else
  230. {
  231. SQL = "INSERT INTO TB_REPEATER ( ";
  232. SQL += " RECEIVER_ID,COMM_ID,BOARD_ID,LOOP_NO,REPEATER_ID,REPEATER_NAME,REPEATER_TYPE,USE_FLAG,CUT_FLAG,UPDATE_USER_ID,UPDATE_TIME";
  233. SQL += ") VALUES ({0},1,{1},{2},{3},'{4}','{5}','{6}','N','FPER',current_timestamp)";
  234. SQL = String.Format(SQL, RECEIVER_ID, BOARD_ID, LOOP_NO, REPEATER_ID, REPEATER_NAME, REPEATER_TYPE, USE_FLAG);
  235. }
  236. ExecuteNonQuery(SQL);
  237. }
  238. else if (cmd == QUERYMode.delete)
  239. {
  240. //if (dt.Rows.Count < 1)
  241. //{
  242. // throw new Exception("해당 자료를 찾을수 없습니다.");
  243. //}
  244. String SQL = "DELETE FROM TB_REPEATER WHERE RECEIVER_ID = {0} AND COMM_ID = 1 AND BOARD_ID = {1} AND LOOP_NO = {2} AND REPEATER_ID = {3}";
  245. SQL = String.Format(SQL, RECEIVER_ID, BOARD_ID, LOOP_NO, REPEATER_ID);
  246. ExecuteNonQuery(SQL);
  247. }
  248. }
  249. catch (Exception ex)
  250. {
  251. Util.UErrorMessage(ex, 0, 0);
  252. throw ex;
  253. }
  254. }
  255. public void Repeater_Cut_Update(int RECEIVER_ID, int BOARD_ID, int LOOP_NO, int REPEATER_ID, string CUT_FLAG)
  256. {
  257. try
  258. {
  259. DataTable dt = Repeater_Select(RECEIVER_ID, BOARD_ID, LOOP_NO, REPEATER_ID, null);
  260. String SQL = "";
  261. if (dt.Rows.Count > 0)
  262. {
  263. SQL = "UPDATE TB_REPEATER SET CUT_FLAG = '{0}'";
  264. SQL += ", UPDATE_USER_ID = 'FPER', UPDATE_TIME = current_timestamp";
  265. SQL += " WHERE RECEIVER_ID = {1} AND COMM_ID = 1 AND BOARD_ID = {2} AND LOOP_NO = {3} AND REPEATER_ID = {4}";
  266. SQL = String.Format(SQL, CUT_FLAG, RECEIVER_ID, BOARD_ID, LOOP_NO, REPEATER_ID);
  267. ExecuteNonQuery(SQL);
  268. }
  269. else
  270. {
  271. throw new Exception("해당 자료를 찾을수 없습니다.");
  272. }
  273. }
  274. catch (Exception ex)
  275. {
  276. Util.UErrorMessage(ex, 0, 0);
  277. throw ex;
  278. }
  279. }
  280. //LOOP조회리스트 전체추가
  281. public ArrayList Repeater_ListALL(int RECEIVER_ID, int BOARD_ID, int LOOP_NO)
  282. {
  283. ArrayList ary = new ArrayList();
  284. ary.Add(new cboitem("-1", "전체"));
  285. try
  286. {
  287. DataTable dt = Repeater_Select(RECEIVER_ID, BOARD_ID, LOOP_NO, 0, "Y");
  288. if (dt.Rows.Count > 0)
  289. {
  290. foreach (DataRow dr in dt.Rows)
  291. {
  292. //REPEATER_ID,REPEATER_NAME,REPEATER_TYPE
  293. String REPEATER_ID = Convert.ToString(dr["REPEATER_ID"]);
  294. String REPEATER_NAME = Convert.ToString(dr["REPEATER_NAME"]);
  295. String REPEATER_TYPE = Convert.ToString(dr["REPEATER_TYPE"]);
  296. ary.Add(new cboitem(REPEATER_ID, REPEATER_ID));
  297. }
  298. }
  299. }
  300. catch (Exception ex)
  301. {
  302. Util.UErrorMessage(ex, 0, 0);
  303. throw ex;
  304. }
  305. return ary;
  306. }
  307. }
  308. }