06fd128111d7251edd041eb7fde6f9cb7a1a453c.svn-base 28 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Text;
  4. using System.Data;
  5. using System.Collections;
  6. using System.Diagnostics;
  7. namespace FPER
  8. {
  9. //그룹타입정의
  10. public struct code_GroupIO
  11. {
  12. public const String Input = "I";
  13. public const String Output = "O";
  14. public const String Unknown = "";
  15. }
  16. class DacMap : DacBase
  17. {
  18. public DacMap(int ID)
  19. : base(ID)// cyim 2015.7.30 데이타베이스 접속 루틴 변경
  20. {
  21. //try {
  22. // Connect();
  23. //}
  24. //catch (Exception ex) {
  25. // Util.UErrorMessage(ex, 0, 0);
  26. //}
  27. }
  28. ~DacMap()
  29. {
  30. //try {
  31. // DisConnect();
  32. //}
  33. //catch (Exception ex) {
  34. // Util.UErrorMessage(ex, 0, 0);
  35. //}
  36. }
  37. //그룹조회
  38. public DataTable Group_Select(String GROUP_ID, String GROUP_TYPE)
  39. {
  40. DataTable dt = null;
  41. try
  42. {
  43. String SQL = "SELECT * FROM TB_GROUP ";
  44. /*
  45. if (GROUP_ID != null || GROUP_TYPE != code_GroupIO.Unknown)
  46. {
  47. SQL += " WHERE ";
  48. if (GROUP_ID != null)
  49. {
  50. SQL += String.Format(" GROUP_ID = '{0}'", GROUP_ID);
  51. if (GROUP_TYPE != code_GroupIO.Unknown) SQL += String.Format(" AND GROUP_TYPE = '{0}'", GROUP_TYPE);
  52. }
  53. else
  54. {
  55. if (GROUP_TYPE != code_GroupIO.Unknown) SQL += String.Format(" GROUP_TYPE = '{0}'", GROUP_TYPE);
  56. }
  57. }
  58. */
  59. SQL += " ORDER BY GROUP_ID";
  60. DataSet dao = Select(SQL);
  61. dt = dao.Tables[0];
  62. }
  63. catch (Exception ex)
  64. {
  65. Util.UErrorMessage(ex, 0, 0);
  66. throw ex;
  67. }
  68. return dt;
  69. }
  70. // cyim 2014.8.14 그룹정보는 수신기아이디에 따라 출력됨
  71. public DataTable Group_Select(String GROUP_ID, String GROUP_TYPE, string Receiver_ID)
  72. {
  73. DataTable dt = null;
  74. try
  75. {
  76. String SQL = "SELECT * FROM TB_GROUP ";
  77. /*
  78. if (GROUP_ID != null || GROUP_TYPE != code_GroupIO.Unknown)
  79. {
  80. SQL += " WHERE ";
  81. if (GROUP_ID != null)
  82. {
  83. SQL += String.Format(" GROUP_ID = '{0}'", GROUP_ID);
  84. if (GROUP_TYPE != code_GroupIO.Unknown) SQL += String.Format(" AND GROUP_TYPE = '{0}'", GROUP_TYPE);
  85. }
  86. else
  87. {
  88. if (GROUP_TYPE != code_GroupIO.Unknown) SQL += String.Format(" GROUP_TYPE = '{0}'", GROUP_TYPE);
  89. }
  90. }
  91. */
  92. SQL += " WHERE RECEIVER_ID = '" + Receiver_ID + "'";
  93. SQL += " ORDER BY GROUP_ID";
  94. DataSet dao = Select(SQL);
  95. dt = dao.Tables[0];
  96. }
  97. catch (Exception ex)
  98. {
  99. Util.UErrorMessage(ex, 0, 0);
  100. throw ex;
  101. }
  102. return dt;
  103. }
  104. public void Group_Update(QUERYMode cmd, String GROUP_ID, String GROUP_TYPE, String GROUP_NAME)
  105. {
  106. try
  107. {
  108. DataTable dt = Group_Select(GROUP_ID, code_GroupIO.Unknown);
  109. if (cmd == QUERYMode.insert)
  110. {
  111. if (dt.Rows.Count > 0)
  112. {
  113. throw new Exception("이미 해당 자료가 존재합니다. 수정모드로 작업하여 주십시요.");
  114. }
  115. String SQL = "INSERT INTO TB_GROUP (GROUP_ID,GROUP_NAME,GROUP_TYPE,UPDATE_USER_ID,UPDATE_TIME)";
  116. SQL += " VALUES ('{0}','{1}','{2}','FPER',current_timestamp)";
  117. SQL = String.Format(SQL, GROUP_ID, GROUP_NAME, GROUP_TYPE);
  118. ExecuteNonQuery(SQL);
  119. }
  120. else if (cmd == QUERYMode.update)
  121. {
  122. if (dt.Rows.Count < 1)
  123. {
  124. throw new Exception("해당 자료를 찾을수 없습니다.");
  125. }
  126. String SQL = "UPDATE TB_GROUP SET GROUP_NAME = '{0}'";
  127. SQL += ", GROUP_TYPE = '{1}'";
  128. SQL += ", UPDATE_USER_ID = 'FPER', UPDATE_TIME = current_timestamp";
  129. SQL += " WHERE GROUP_ID = '{2}'";
  130. SQL = String.Format(SQL, GROUP_NAME, GROUP_TYPE, GROUP_ID);
  131. ExecuteNonQuery(SQL);
  132. }
  133. else if (cmd == QUERYMode.delete)
  134. {
  135. if (dt.Rows.Count < 1)
  136. {
  137. throw new Exception("해당 자료를 찾을수 없습니다.");
  138. }
  139. String SQL = "DELETE FROM TB_GROUP WHERE GROUP_ID = '{0}'";
  140. SQL = String.Format(SQL, GROUP_ID);
  141. ExecuteNonQuery(SQL);
  142. SQL = "DELETE FROM TB_GROUP_DEVICE WHERE GROUP_ID = '{0}'";
  143. SQL = String.Format(SQL, GROUP_ID);
  144. ExecuteNonQuery(SQL);
  145. SQL = "DELETE FROM TB_MAP_PROCESS WHERE GROUP_ID = '{0}'";
  146. SQL = String.Format(SQL, GROUP_ID);
  147. ExecuteNonQuery(SQL);
  148. }
  149. }
  150. catch (Exception ex)
  151. {
  152. Util.UErrorMessage(ex, 0, 0);
  153. throw ex;
  154. }
  155. }
  156. //그룹조회
  157. public DataTable Group_Modal_Select(String GROUP_TYPE, String SEARCH_KEY)
  158. {
  159. DataTable dt = null;
  160. try
  161. {
  162. String SQL = "SELECT * FROM TB_GROUP ";
  163. //if (GROUP_TYPE != code_GroupIO.Unknown)
  164. //{
  165. // SQL += String.Format(" WHERE GROUP_TYPE = '{0}'", GROUP_TYPE);
  166. // if (SEARCH_KEY != null)
  167. // {
  168. // SQL += String.Format(" AND GROUP_NAME LIKE '%{0}%' ", SEARCH_KEY);
  169. // }
  170. //}
  171. //else if (SEARCH_KEY != null)
  172. {
  173. SQL += String.Format(" WHERE GROUP_NAME LIKE '%{0}%' ", SEARCH_KEY);
  174. }
  175. SQL += " ORDER BY GROUP_ID";
  176. DataSet dao = Select(SQL);
  177. dt = dao.Tables[0];
  178. }
  179. catch (Exception ex)
  180. {
  181. Util.UErrorMessage(ex, 0, 0);
  182. throw ex;
  183. }
  184. return dt;
  185. }
  186. //그룹회로 리스트조회
  187. public DataTable Group_Device_Select(String GROUP_ID, int RECEIVER_ID)
  188. {
  189. DataTable dt = null;
  190. try
  191. {
  192. String SQL = "SELECT A.*,B.DEVICE_NAME,B.DEVICE_TYPE,C.DEVICE_TYPE_NAME,B.POSITION_CODE,D.POSITION_NAME ";
  193. SQL += " FROM TB_GROUP_DEVICE A";
  194. SQL += " LEFT OUTER JOIN TB_DEVICE B ON A.RECEIVER_ID = B.RECEIVER_ID AND A.COMM_ID = B.COMM_ID ";
  195. SQL += " AND A.BOARD_ID = B.BOARD_ID AND A.LOOP_NO = B.LOOP_NO AND A.REPEATER_ID = B.REPEATER_ID ";
  196. SQL += " AND A.DEVICE_ID = B.DEVICE_ID AND A.INOUT_TYPE = B.INOUT_TYPE";
  197. SQL += " LEFT OUTER JOIN TB_DEVICE_TYPE C ON B.DEVICE_TYPE = C.DEVICE_TYPE";
  198. SQL += " LEFT OUTER JOIN TB_POSITION_CODE D ON B.POSITION_CODE = D.POSITION_CODE";
  199. SQL += String.Format(" WHERE A.GROUP_ID = '{0}' AND A.RECEIVER_ID = {1} ", GROUP_ID, RECEIVER_ID);
  200. SQL += " ORDER BY A.COMM_ID,A.BOARD_ID,A.LOOP_NO,A.REPEATER_ID,A.DEVICE_ID";
  201. DataSet dao = Select(SQL);
  202. dt = dao.Tables[0];
  203. }
  204. catch (Exception ex)
  205. {
  206. Util.UErrorMessage(ex, 0, 0);
  207. throw ex;
  208. }
  209. return dt;
  210. }
  211. //그룹회로 리스트조회
  212. public DataTable Group_Device_Select(String GROUP_ID,
  213. int RECEIVER_ID, int COMM_ID, int BOARD_ID, int LOOP_NO, int REPEATER_ID, int DEVICE_ID, String INOUT_TYPE)
  214. {
  215. DataTable dt = null;
  216. try
  217. {
  218. String SQL = "SELECT A.*,B.DEVICE_NAME,B.DEVICE_TYPE,C.DEVICE_TYPE_NAME FROM TB_GROUP_DEVICE A";
  219. SQL += " LEFT OUTER JOIN TB_DEVICE B ON A.RECEIVER_ID = B.RECEIVER_ID AND A.COMM_ID = B.COMM_ID ";
  220. SQL += " AND A.BOARD_ID = B.BOARD_ID AND A.LOOP_NO = B.LOOP_NO AND A.REPEATER_ID = B.REPEATER_ID ";
  221. SQL += " AND A.DEVICE_ID = B.DEVICE_ID AND A.INOUT_TYPE = B.INOUT_TYPE";
  222. SQL += " LEFT OUTER JOIN TB_DEVICE_TYPE C ON B.DEVICE_TYPE = C.DEVICE_TYPE";
  223. SQL += String.Format(" WHERE A.GROUP_ID = '{0}' AND A.RECEIVER_ID = {1} AND A.COMM_ID = {2} ", GROUP_ID, RECEIVER_ID, COMM_ID);
  224. SQL += String.Format(" AND A.BOARD_ID = {0} AND A.LOOP_NO = {1}", BOARD_ID, LOOP_NO);
  225. SQL += String.Format(" AND A.REPEATER_ID = {0} AND A.DEVICE_ID = {1} AND A.INOUT_TYPE = '{2}'", REPEATER_ID, DEVICE_ID, INOUT_TYPE);
  226. DataSet dao = Select(SQL);
  227. dt = dao.Tables[0];
  228. }
  229. catch (Exception ex)
  230. {
  231. Util.UErrorMessage(ex, 0, 0);
  232. throw ex;
  233. }
  234. return dt;
  235. }
  236. //맵조회
  237. public DataTable Map_Select(int MAP_NO)
  238. {
  239. DataTable dt = null;
  240. try
  241. {
  242. String SQL = "SELECT * FROM TB_MAP ";
  243. if (MAP_NO > 0) SQL += String.Format(" WHERE MAP_NO = {0} ", MAP_NO);
  244. SQL += " ORDER BY MAP_NO";
  245. DataSet dao = Select(SQL);
  246. dt = dao.Tables[0];
  247. }
  248. catch (Exception ex)
  249. {
  250. Util.UErrorMessage(ex, 0, 0);
  251. throw ex;
  252. }
  253. return dt;
  254. }
  255. //맵조회
  256. public DataTable Map_Select(String MAP_ID)
  257. {
  258. DataTable dt = null;
  259. try
  260. {
  261. String SQL = "SELECT * FROM TB_MAP ";
  262. if (MAP_ID != null) SQL += String.Format(" WHERE MAP_ID = '{0}' ", MAP_ID);
  263. SQL += " ORDER BY MAP_NO";
  264. DataSet dao = Select(SQL);
  265. dt = dao.Tables[0];
  266. }
  267. catch (Exception ex)
  268. {
  269. Util.UErrorMessage(ex, 0, 0);
  270. throw ex;
  271. }
  272. return dt;
  273. }
  274. //맵조회
  275. public int Map_Max_Select()
  276. {
  277. int MAP_NO = 0;
  278. try
  279. {
  280. String SQL = "SELECT MAX(MAP_NO) FROM TB_MAP ";
  281. DataSet dao = Select(SQL);
  282. DataTable dt = dao.Tables[0];
  283. if (dt.Rows.Count > 0)
  284. {
  285. foreach (DataRow dr in dt.Rows)
  286. {
  287. MAP_NO = Util.StrToInt(dr[0], 0);
  288. }
  289. }
  290. }
  291. catch (Exception ex)
  292. {
  293. Util.UErrorMessage(ex, 0, 0);
  294. throw ex;
  295. }
  296. return ++MAP_NO;
  297. }
  298. //맵조회
  299. public int Map_Process_Seq_Max_Select(int MAP_NO, String MAP_TYPE)
  300. {
  301. int SEQ_NO = 0;
  302. try
  303. {
  304. String SQL = "SELECT MAX(SEQ_NO) FROM TB_MAP_PROCESS ";
  305. SQL += String.Format(" WHERE MAP_NO = {0} AND MAP_TYPE = '{1}'", MAP_NO, MAP_TYPE);
  306. DataSet dao = Select(SQL);
  307. DataTable dt = dao.Tables[0];
  308. if (dt.Rows.Count > 0)
  309. {
  310. foreach (DataRow dr in dt.Rows)
  311. {
  312. SEQ_NO = Util.StrToInt(dr[0], 0);
  313. }
  314. }
  315. }
  316. catch (Exception ex)
  317. {
  318. Util.UErrorMessage(ex, 0, 0);
  319. throw ex;
  320. }
  321. return ++SEQ_NO;
  322. }
  323. //맵 SEQUENCE 조회
  324. public DataTable Map_Process_Select(int MAP_NO, String MAP_TYPE, int SEQ_NO)
  325. {
  326. DataTable dt = null;
  327. try
  328. {
  329. String SQL = "SELECT a.*";
  330. SQL += " ,CASE a.PROCESS_TYPE WHEN 'D' THEN b.DEVICE_NAME ELSE c.GROUP_NAME END AS PROCESS_NAME";
  331. SQL += " FROM TB_MAP_PROCESS a";
  332. SQL += " LEFT OUTER JOIN TB_DEVICE b ON a.RECEIVER_ID = b.RECEIVER_ID AND a.COMM_ID = b.COMM_ID ";
  333. SQL += " AND a.BOARD_ID = b.BOARD_ID AND a.LOOP_NO = b.LOOP_NO";
  334. SQL += " AND a.REPEATER_ID = b.REPEATER_ID AND a.DEVICE_ID = b.DEVICE_ID";
  335. SQL += " AND a.INOUT_TYPE = b.INOUT_TYPE ";
  336. SQL += " LEFT OUTER JOIN TB_GROUP c ON a.GROUP_ID = c.GROUP_ID";
  337. SQL += String.Format(" WHERE a.MAP_NO = {0} AND a.MAP_TYPE = '{1}'", MAP_NO, MAP_TYPE);
  338. if (SEQ_NO > 0) SQL += String.Format(" AND a.SEQ_NO = {0} ", SEQ_NO);
  339. SQL += " ORDER BY a.SEQ_NO";
  340. DataSet dao = Select(SQL);
  341. dt = dao.Tables[0];
  342. }
  343. catch (Exception ex)
  344. {
  345. Util.UErrorMessage(ex, 0, 0);
  346. throw ex;
  347. }
  348. return dt;
  349. }
  350. //맵 회로 조회
  351. public DataTable Map_Process_Select(int MAP_NO, String MAP_TYPE, int RECEIVER_ID, int COMM_ID, int BOARD_ID, int LOOP_NO
  352. , int REPEATER_ID, int DEVICE_ID, string INOUT_TYPE)
  353. {
  354. DataTable dt = null;
  355. try
  356. {
  357. String SQL = "SELECT a.*";
  358. SQL += " ,CASE a.PROCESS_TYPE WHEN 'D' THEN b.DEVICE_NAME ELSE c.GROUP_NAME END AS PROCESS_NAME";
  359. SQL += " FROM TB_MAP_PROCESS a";
  360. SQL += " LEFT OUTER JOIN TB_DEVICE b ON a.RECEIVER_ID = b.RECEIVER_ID AND a.COMM_ID = b.COMM_ID ";
  361. SQL += " AND a.BOARD_ID = b.BOARD_ID AND a.LOOP_NO = b.LOOP_NO";
  362. SQL += " AND a.REPEATER_ID = b.REPEATER_ID AND a.DEVICE_ID = b.DEVICE_ID";
  363. SQL += " AND a.INOUT_TYPE = b.INOUT_TYPE ";
  364. SQL += " LEFT OUTER JOIN TB_GROUP c ON a.GROUP_ID = c.GROUP_ID";
  365. SQL += String.Format(" WHERE a.MAP_NO = {0} AND a.MAP_TYPE = '{1}'", MAP_NO, MAP_TYPE);
  366. SQL += String.Format(" AND a.PROCESS_TYPE = 'D' AND a.RECEIVER_ID = {0}", RECEIVER_ID);
  367. SQL += String.Format(" AND a.COMM_ID = {0} AND a.RECEIVER_ID = {1}", COMM_ID, BOARD_ID);
  368. SQL += String.Format(" AND a.LOOP_NO = {0} AND a.REPEATER_ID = {1}", LOOP_NO, REPEATER_ID);
  369. SQL += String.Format(" AND a.DEVICE_ID = {0} AND a.INOUT_TYPE = '{1}'", DEVICE_ID, INOUT_TYPE);
  370. SQL += " ORDER BY a.SEQ_NO";
  371. DataSet dao = Select(SQL);
  372. dt = dao.Tables[0];
  373. }
  374. catch (Exception ex)
  375. {
  376. Util.UErrorMessage(ex, 0, 0);
  377. throw ex;
  378. }
  379. return dt;
  380. }
  381. //맵 그룹 조회
  382. public DataTable Map_Process_Select(int MAP_NO, String MAP_TYPE, string GROUP_ID)
  383. {
  384. DataTable dt = null;
  385. try
  386. {
  387. String SQL = "SELECT a.*";
  388. SQL += " ,CASE a.PROCESS_TYPE WHEN 'D' THEN b.DEVICE_NAME ELSE c.GROUP_NAME END AS PROCESS_NAME";
  389. SQL += " FROM TB_MAP_PROCESS a";
  390. SQL += " LEFT OUTER JOIN TB_DEVICE b ON a.RECEIVER_ID = b.RECEIVER_ID AND a.COMM_ID = b.COMM_ID ";
  391. SQL += " AND a.BOARD_ID = b.BOARD_ID AND a.LOOP_NO = b.LOOP_NO";
  392. SQL += " AND a.REPEATER_ID = b.REPEATER_ID AND a.DEVICE_ID = b.DEVICE_ID";
  393. SQL += " AND a.INOUT_TYPE = b.INOUT_TYPE ";
  394. SQL += " LEFT OUTER JOIN TB_GROUP c ON a.GROUP_ID = c.GROUP_ID";
  395. SQL += String.Format(" WHERE a.MAP_NO = {0} AND a.MAP_TYPE = '{1}'", MAP_NO, MAP_TYPE);
  396. SQL += String.Format(" AND a.PROCESS_TYPE = 'G' AND a.GROUP_ID = '{0}'", GROUP_ID);
  397. SQL += " ORDER BY a.SEQ_NO";
  398. DataSet dao = Select(SQL);
  399. dt = dao.Tables[0];
  400. }
  401. catch (Exception ex)
  402. {
  403. Util.UErrorMessage(ex, 0, 0);
  404. throw ex;
  405. }
  406. return dt;
  407. }
  408. //매핑 맵 가져오기
  409. public DataTable Mapping_Map_Select(String MAP_TYPE, int RECEIVER_ID, String MAP_ID)
  410. {
  411. DataTable dt = null;
  412. try
  413. {
  414. String SQL = "SELECT a.MAP_NO,a.MAP_ID,a.MAP_NAME";
  415. SQL += " ,b.MAP_TYPE,b.SEQ_NO,b.PROCESS_TYPE,b.GROUP_ID";
  416. SQL += " ,CASE b.PROCESS_TYPE WHEN 'D' THEN b.COMM_ID ELSE d.COMM_ID END AS COMM_ID";
  417. SQL += " ,CASE b.PROCESS_TYPE WHEN 'D' THEN b.BOARD_ID ELSE d.BOARD_ID END AS BOARD_ID";
  418. SQL += " ,CASE b.PROCESS_TYPE WHEN 'D' THEN b.LOOP_NO ELSE d.LOOP_NO END AS LOOP_NO";
  419. SQL += " ,CASE b.PROCESS_TYPE WHEN 'D' THEN b.REPEATER_ID ELSE d.REPEATER_ID END AS REPEATER_ID";
  420. SQL += " ,CASE b.PROCESS_TYPE WHEN 'D' THEN b.DEVICE_ID ELSE d.DEVICE_ID END AS DEVICE_ID";
  421. SQL += " ,c.GROUP_NAME,c.GROUP_TYPE";
  422. SQL += " FROM TB_MAP a";
  423. SQL += " JOIN TB_MAP_PROCESS b ON a.MAP_NO = b.MAP_NO";
  424. SQL += " LEFT OUTER JOIN TB_GROUP c ON b.GROUP_ID = c.GROUP_ID";
  425. SQL += " LEFT OUTER JOIN TB_GROUP_DEVICE d ON c.GROUP_ID = d.GROUP_ID";
  426. SQL += String.Format(" WHERE b.MAP_TYPE = '{0}' AND b.RECEIVER_ID = {1}", MAP_TYPE, RECEIVER_ID);
  427. if (MAP_ID != null) SQL += String.Format(" AND a.MAP_ID = '{0}'", MAP_ID);
  428. SQL += " ORDER BY a.MAP_ID,b.MAP_TYPE";
  429. SQL += " ,CASE b.PROCESS_TYPE WHEN 'D' THEN b.COMM_ID ELSE d.COMM_ID END";
  430. SQL += " ,CASE b.PROCESS_TYPE WHEN 'D' THEN b.BOARD_ID ELSE d.BOARD_ID END ";
  431. SQL += " ,CASE b.PROCESS_TYPE WHEN 'D' THEN b.LOOP_NO ELSE d.LOOP_NO END ";
  432. SQL += " ,CASE b.PROCESS_TYPE WHEN 'D' THEN b.REPEATER_ID ELSE d.REPEATER_ID END ";
  433. SQL += " ,CASE b.PROCESS_TYPE WHEN 'D' THEN b.DEVICE_ID ELSE d.DEVICE_ID END ";
  434. DataSet dao = Select(SQL);
  435. dt = dao.Tables[0];
  436. }
  437. catch (Exception ex)
  438. {
  439. Util.UErrorMessage(ex, 0, 0);
  440. throw ex;
  441. }
  442. return dt;
  443. }
  444. //그룹 회로설정값 저장
  445. public void Group_Device_Update(QUERYMode cmd, String GROUP_ID, int RECEIVER_ID, int COMM_ID, int BOARD_ID
  446. , int LOOP_NO, int REPEATER_ID, int DEVICE_ID, String INOUT_TYPE)
  447. {
  448. try
  449. {
  450. if (cmd == QUERYMode.insert || cmd == QUERYMode.update || cmd == QUERYMode.InsertAndUpdate)
  451. {
  452. if (DEVICE_ID < 1) return;
  453. String SQL = "";
  454. DataTable dt = Group_Device_Select(GROUP_ID, RECEIVER_ID, COMM_ID, BOARD_ID, LOOP_NO, REPEATER_ID, DEVICE_ID, INOUT_TYPE);
  455. if (dt.Rows.Count < 1)
  456. {
  457. SQL = "INSERT INTO TB_GROUP_DEVICE ( ";
  458. SQL += " GROUP_ID,RECEIVER_ID,COMM_ID,BOARD_ID,LOOP_NO,REPEATER_ID,DEVICE_ID,INOUT_TYPE,UPDATE_USER_ID,UPDATE_TIME,GROUP_RECEIVER_ID,USE_FLAG";// ksd, 2012.02.21, db table change update
  459. SQL += ") VALUES ('{0}',{1},{2},{3},{4},{5},{6},'{7}','FPER',current_timestamp,{8},'Y')";// ksd, 2012.02.21, db table change update
  460. SQL = String.Format(SQL, GROUP_ID, RECEIVER_ID, COMM_ID, BOARD_ID, LOOP_NO, REPEATER_ID, DEVICE_ID, INOUT_TYPE, RECEIVER_ID);
  461. ExecuteNonQuery(SQL);
  462. }
  463. }
  464. else if (cmd == QUERYMode.delete)
  465. {
  466. String SQL = "";
  467. if (DEVICE_ID < 1)
  468. {
  469. SQL = "DELETE FROM TB_GROUP_DEVICE WHERE RECEIVER_ID = {0} AND GROUP_ID = '{1}' ";
  470. SQL = String.Format(SQL, RECEIVER_ID, GROUP_ID);
  471. }
  472. else
  473. {
  474. SQL = "DELETE FROM TB_GROUP_DEVICE WHERE RECEIVER_ID = {0} AND GROUP_ID = '{1}' AND COMM_ID = {2} ";
  475. SQL += " AND BOARD_ID = {3} AND LOOP_NO = {4} AND REPEATER_ID = {5} AND DEVICE_ID = {6} AND INOUT_TYPE = '{7}'";
  476. SQL = String.Format(SQL, RECEIVER_ID, GROUP_ID, COMM_ID, BOARD_ID, LOOP_NO, REPEATER_ID, DEVICE_ID, INOUT_TYPE);
  477. }
  478. ExecuteNonQuery(SQL);
  479. }
  480. }
  481. catch (Exception ex)
  482. {
  483. Util.UErrorMessage(ex, 0, 0);
  484. throw ex;
  485. }
  486. }
  487. public void Map_Update(QUERYMode cmd, int MAP_NO, String MAP_ID, String MAP_NAME)
  488. {
  489. try
  490. {
  491. DataTable dt = Map_Select(MAP_NO);
  492. if (cmd == QUERYMode.insert)
  493. {
  494. if (dt.Rows.Count > 0)
  495. {
  496. throw new Exception("이미 해당 자료가 존재합니다. 수정모드로 작업하여 주십시요.");
  497. }
  498. String SQL = "INSERT INTO TB_MAP (MAP_NO,MAP_ID,MAP_NAME,UPDATE_USER_ID,UPDATE_TIME)";
  499. SQL += " VALUES ({0},'{1}','{2}','FPER',current_timestamp)";
  500. SQL = String.Format(SQL, MAP_NO, MAP_ID, MAP_NAME);
  501. ExecuteNonQuery(SQL);
  502. }
  503. else if (cmd == QUERYMode.update)
  504. {
  505. if (dt.Rows.Count < 1)
  506. {
  507. throw new Exception("해당 자료를 찾을수 없습니다.");
  508. }
  509. String SQL = "UPDATE TB_MAP SET MAP_NAME = '{0}'";
  510. SQL += ", MAP_ID = '{1}'";
  511. SQL += ", UPDATE_USER_ID = 'FPER', UPDATE_TIME = current_timestamp";
  512. SQL += " WHERE MAP_NO = '{2}'";
  513. SQL = String.Format(SQL, MAP_NAME, MAP_ID, MAP_NO);
  514. ExecuteNonQuery(SQL);
  515. }
  516. else if (cmd == QUERYMode.delete)
  517. {
  518. if (dt.Rows.Count < 1)
  519. {
  520. throw new Exception("해당 자료를 찾을수 없습니다.");
  521. }
  522. String SQL = "DELETE FROM TB_MAP WHERE MAP_NO = {0}";
  523. SQL = String.Format(SQL, MAP_NO);
  524. ExecuteNonQuery(SQL);
  525. SQL = "DELETE FROM TB_MAP_PROCESS WHERE MAP_NO = '{0}'";
  526. SQL = String.Format(SQL, MAP_NO);
  527. ExecuteNonQuery(SQL);
  528. }
  529. }
  530. catch (Exception ex)
  531. {
  532. Util.UErrorMessage(ex, 0, 0);
  533. throw ex;
  534. }
  535. }
  536. public void Map_Process_Update(QUERYMode cmd, int MAP_NO, String MAP_TYPE, int SEQ_NO, String PROCESS_TYPE, String GROUP_ID
  537. , int RECEIVER_ID, int COMM_ID, int BOARD_ID, int LOOP_NO, int REPEATER_ID, int DEVICE_ID, string INOUT_TYPE)
  538. {
  539. try
  540. {
  541. DataTable dt = null;
  542. if (PROCESS_TYPE.Equals("D"))
  543. dt = Map_Process_Select(MAP_NO, MAP_TYPE, RECEIVER_ID, COMM_ID, BOARD_ID, LOOP_NO, REPEATER_ID, DEVICE_ID, INOUT_TYPE);
  544. else if (PROCESS_TYPE.Equals("G"))
  545. dt = Map_Process_Select(MAP_NO, MAP_TYPE, GROUP_ID);
  546. else
  547. dt = Map_Process_Select(MAP_NO, MAP_TYPE, SEQ_NO);
  548. if (cmd == QUERYMode.InsertAndUpdate || cmd == QUERYMode.insert || cmd == QUERYMode.update)
  549. {
  550. if (dt.Rows.Count > 0)
  551. {
  552. throw new Exception("이미 등록된 자료입니다..");
  553. //String SQL = "UPDATE TB_MAP_PROCESS SET PROCESS_TYPE = '{0}'";
  554. //SQL += ", GROUP_ID = '{1}', RECEIVER_ID = '{1}', COMM_ID = '{2}'";
  555. //SQL += ", BOARD_ID = '{3}', LOOP_NO = '{4}', REPEATER_ID = '{5}'";
  556. //SQL += ", DEVICE_ID = '{6}', INOUT_TYPE = '{7}'";
  557. //SQL += ", UPDATE_USER_ID = 'FPER', UPDATE_TIME = current_timestamp";
  558. //SQL += " WHERE MAP_NO = '{8}' AND MAP_TYPE = '{9}' AND SEQ_NO = {10}";
  559. //SQL = String.Format(SQL, PROCESS_TYPE, GROUP_ID, RECEIVER_ID, COMM_ID, BOARD_ID, LOOP_NO, REPEATER_ID, DEVICE_ID, INOUT_TYPE
  560. // , MAP_NO, MAP_TYPE, SEQ_NO);
  561. //ExecuteNonQuery(SQL);
  562. }
  563. else
  564. {
  565. String SQL = "INSERT INTO TB_MAP_PROCESS (MAP_NO,MAP_TYPE,SEQ_NO,PROCESS_TYPE,GROUP_ID";
  566. SQL += ",RECEIVER_ID,COMM_ID,BOARD_ID,LOOP_NO,REPEATER_ID,DEVICE_ID,INOUT_TYPE,UPDATE_USER_ID,UPDATE_TIME, MAP_RECEIVER_ID,USE_FLAG)";// ksd, 2012.02.21, db table change update
  567. SQL += " VALUES ({0},'{1}',{2},'{3}','{4}'";
  568. SQL += ",{5},{6},{7},{8},{9},{10},'{11}','FPER',current_timestamp,{5},'Y')";// ksd, 2012.02.21, db table change update
  569. SQL = String.Format(SQL, MAP_NO, MAP_TYPE, SEQ_NO, PROCESS_TYPE, GROUP_ID
  570. , RECEIVER_ID, COMM_ID, BOARD_ID, LOOP_NO, REPEATER_ID, DEVICE_ID, INOUT_TYPE);
  571. ExecuteNonQuery(SQL);
  572. }
  573. }
  574. else if (cmd == QUERYMode.delete)
  575. {
  576. if (dt.Rows.Count < 1)
  577. {
  578. throw new Exception("해당 자료를 찾을수 없습니다.");
  579. }
  580. String SQL = "DELETE FROM TB_MAP_PROCESS ";
  581. SQL += " WHERE MAP_NO = {0} AND MAP_TYPE = '{1}' AND SEQ_NO = {2}";
  582. SQL = String.Format(SQL, MAP_NO, MAP_TYPE, SEQ_NO);
  583. ExecuteNonQuery(SQL);
  584. }
  585. }
  586. catch (Exception ex)
  587. {
  588. Util.UErrorMessage(ex, 0, 0);
  589. throw ex;
  590. }
  591. }
  592. public void Map_Clear()
  593. {
  594. try
  595. {
  596. String SQL = "DELETE FROM TB_GROUP_DEVICE ";
  597. ExecuteNonQuery(SQL);
  598. SQL = "DELETE FROM TB_GROUP ";
  599. ExecuteNonQuery(SQL);
  600. SQL = "DELETE FROM TB_MAP ";
  601. ExecuteNonQuery(SQL);
  602. SQL = "DELETE FROM TB_MAP_PROCESS ";
  603. ExecuteNonQuery(SQL);
  604. }
  605. catch (Exception ex)
  606. {
  607. Util.UErrorMessage(ex, 0, 0);
  608. throw ex;
  609. }
  610. }
  611. public void Group_Update(QUERYMode cmd, String s_GROUP_ID, String s_GROUP_NAME, String s_GROUP_TYPE, String GROUP_ID, String GROUP_NAME, String GROUP_TYPE)
  612. {
  613. try
  614. {
  615. DataTable dt = Group_Select(GROUP_ID, code_GroupIO.Unknown);
  616. if (cmd == QUERYMode.insert)
  617. {
  618. if (dt.Rows.Count > 0)
  619. {
  620. throw new Exception("이미 해당 자료가 존재합니다. 수정모드로 작업하여 주십시요.");
  621. }
  622. String SQL = "INSERT INTO TB_GROUP (" + s_GROUP_ID + "," + s_GROUP_NAME + "," + s_GROUP_TYPE + ",UPDATE_USER_ID,UPDATE_TIME)";
  623. SQL += " VALUES ('{0}','{1}','{2}','FPER',current_timestamp)";
  624. SQL = String.Format(SQL, GROUP_ID, GROUP_NAME, GROUP_TYPE);
  625. ExecuteNonQuery(SQL);
  626. }
  627. }
  628. catch (Exception ex)
  629. {
  630. Util.UErrorMessage(ex, 0, 0);
  631. //throw ex;
  632. }
  633. }
  634. }
  635. }