12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127 |
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Text;
- using System.Data.SqlClient;
- namespace IControls_FireManager
- {
- // SQL 쿼리문은 여기 클래스에서 전담한다
- public static class _Sql
- {
- ///
- /// 쿼리 실행 비포함
- ///
- // FireBird에서만 사용가능
- // 특정 Table에서 윗쪽부터 필요한 레코드 가져오기 SQL문
- public static string Get_ColumnFirstValue(string TableName, string Number, string RECEIVER_ID)
- {
- // 적용테이블 :
- // "TB_DEVICE" - RECEIVER_ID 있음
- string SQL = null;
- if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
- {
- SQL = "SELECT FIRST " + Number + " * " +
- "FROM " + TableName + ";";
- }
- else
- {
- SQL = "SELECT FIRST " + Number + " * " +
- "FROM " + TableName +
- " WHERE " + TableName + "." + "RECEIVER_ID" + " = '" + RECEIVER_ID + "';";
- }
- return SQL;
- }
- // 특정 Table의 특정 Column에 최대값 찾기 SQL문
- public static string Get_ColumnMaxValue(string TableName, string Column)
- {
- // 적용테이블 : 없음
- string SQL = "SELECT MAX(" + Column + ") " +
- "FROM " + TableName + ";";
- return SQL;
- }
- // 총 레코드 수 알기
- public static string Get_TableRecordCount(string TableName)
- {
- // 적용테이블 : 없음
- string SQL = "SELECT count(*) " +
- "FROM " + TableName + ";";
- return SQL;
- }
- // 특정 Table의 모든 Column 값 조회해오기
- public static string SearchAll_Table_Value(string TableName, string RECEIVER_ID)
- {
- // 적용테이블 :
- // "TB_DEVICE_TYPE" - RECEIVER_ID 없음
- // "TB_GROUP" "TB_RECEIVER" "TB_COMM" - RECEIVER_ID 있음
- string SQL = null;
- if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
- {
- SQL = "SELECT * " +
- "FROM " + TableName + ";";
- }
- else
- {
- SQL = "SELECT * " +
- "FROM " + TableName +
- " WHERE " + TableName + "." + "RECEIVER_ID" + " = '" + RECEIVER_ID + "';";
- }
- return SQL;
- }
- // 특정 Table의 모든 Column 값 조회해오기 (ORDER BY 사용)
- // FireBird는 insert를 시키면 레코드가 마지막에 들어가는게 아니라 이상하게 중간에 들어가게 됨...이해할수 없음..
- public static string SearchAll_Table_Value(string TableName, string Column, string RECEIVER_ID)
- {
- // 적용테이블 :
- // "TB_GROUP" "TB_MAP" - RECEIVER_ID 있음
- string SQL = null;
- if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
- {
- SQL = "SELECT * " +
- "FROM " + TableName + " ORDER BY " + Column + ";";
- }
- else
- {
- SQL = "SELECT * " +
- "FROM " + TableName +
- " WHERE " + TableName + "." + "RECEIVER_ID" + " = '" + RECEIVER_ID + "'" +
- " ORDER BY " + Column + ";";
- }
- return SQL;
- }
- // Where절을 이용하여 특정 Table의 모든 Column 값 조회해오기
- public static string SearchAll_Table_Value(string TableName, string Column, string Value, string RECEIVER_ID)
- {
- // 적용테이블 :
- // "TB_GROUP" "TB_MAP_PROCESS" - RECEIVER_ID 있음
- string SQL = null;
- if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
- {
- SQL = "SELECT * " +
- "FROM " + TableName + " " +
- "WHERE " + TableName + "." + Column + " = '" + Value + "';";
- }
- else
- {
- SQL = "SELECT * " +
- "FROM " + TableName + " " +
- "WHERE " + TableName + "." + "RECEIVER_ID" + " = '" + RECEIVER_ID + "' and " + TableName + "." + Column + " = '" + Value + "';";
- }
- return SQL;
- }
- // Where절을 이용하여 특정 Table의 모든 Column 값 조회해오기
- public static string SearchAll_Table_Value(string TableName, string Column1, string Value1, string Column2, string Value2, string RECEIVER_ID)
- {
- // 적용테이블 :
- // "TB_MAP_PROCESS" - RECEIVER_ID 있음
- string SQL = null;
- if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
- {
- SQL = "SELECT * " +
- "FROM " + TableName + " " +
- "WHERE " + TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "';";
- }
- else
- {
- SQL = "SELECT * " +
- "FROM " + TableName + " " +
- "WHERE " + TableName + "." + "RECEIVER_ID" + " = '" + RECEIVER_ID + "' and " + TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "';";
- }
- return SQL;
- }
- // Where절을 이용하여 특정 Table의 모든 Column 값 조회해오기
- public static string SearchAll_Table_Value_MAP_RECEIVER_ID(string TableName, string Column1, string Value1, string Column2, string Value2, string RECEIVER_ID)
- {
- // 적용테이블 :
- // "TB_MAP_PROCESS" - RECEIVER_ID 있음
- string SQL = null;
- if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
- {
- SQL = "SELECT * " +
- "FROM " + TableName + " " +
- "WHERE " + TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "';";
- }
- else
- {
- SQL = "SELECT * " +
- "FROM " + TableName + " " +
- "WHERE " + TableName + "." + "MAP_RECEIVER_ID" + " = '" + RECEIVER_ID + "' and " + TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "';";
- }
- return SQL;
- }
- // Where절을 이용하여 특정 Table의 모든 Column 값 조회해오기
- 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)
- {
- // 적용테이블 :
- // "TB_MAP_PROCESS" - RECEIVER_ID 있음
- string SQL = null;
- if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
- {
- SQL = "SELECT * " +
- "FROM " + TableName + " " +
- "WHERE " + TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "' and " + TableName + "." + Column3 + " = '" + Value3 + "' and " + TableName + "." + Column4 + " = '" + Value4 + "' and " + TableName + "." + Column5 + " = '" + Value5 + "' and " + TableName + "." + Column6 + " = '" + Value6 + "';";
- }
- else
- {
- SQL = "SELECT * " +
- "FROM " + TableName + " " +
- "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 + "';";
- }
- return SQL;
- }
- // TB_MAP_PROCESS 테이블에서 시컨스 최대값 가져오기
- public static string Search_MapProcess_MaxValue(string TableName, string Column1, string Value1, string Column2, string Value2, string MAP_RECEIVER_ID)
- {
- string SQL = "SELECT max(SEQ_NO) " +
- "FROM " + TableName + " " +
- "WHERE " + TableName + "." + "MAP_RECEIVER_ID" + " = '" + MAP_RECEIVER_ID + "' and " + TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "';";
- return SQL;
- }
- // Where절을 이용하여 특정 Table의 모든 Column 값 조회해오기
- public static string Search_Table(string TableName, string Column1, string Value1, string Column2, string Value2, string RECEIVER_ID)
- {
- // 적용테이블 :
- // "TB_GROUP" - RECEIVER_ID 있음
- string SQL = null;
- SQL += "SELECT * ";
- SQL += "FROM " + TableName + " ";
- if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
- {
- if (Value1.Length != 0 || Value2.Length != 0) SQL += "WHERE ";
- if (Value1.Length != 0) SQL += Column1 + " = '" + Value1 + "' ";
- if (Value1.Length != 0 && Value2.Length != 0) SQL += "AND ";
- if (Value2.Length != 0) SQL += Column2 + " like '%" + Value2 + "%' ";
- }
- else
- {
- if (Value1.Length == 0 && Value2.Length == 0)
- {
- SQL += "WHERE " + "RECEIVER_ID" + " = '" + RECEIVER_ID + "' ";
- }
- else
- {
- if (Value1.Length != 0 || Value2.Length != 0) SQL += "WHERE " + TableName + "." + "RECEIVER_ID" + " = '" + RECEIVER_ID + "' and ";
- if (Value1.Length != 0) SQL += Column1 + " = '" + Value1 + "' ";
- if (Value1.Length != 0 && Value2.Length != 0) SQL += "AND ";
- if (Value2.Length != 0) SQL += Column2 + " like '%" + Value2 + "%' ";
- }
- }
- return SQL;
- }
- // Where절을 이용하여 특정 Table의 모든 Column 값 조회해오기
- // TB_DEVICE 테이블에 특성화되어 있음
- public static string SearchDeviceName_Table_Value(string TableName, string Column1, string Value1, string Column2, int Value2,
- string Column3, int Value3, string Column4, int Value4,
- string Column5, int Value5, string Column6, int Value6,
- string Column7, string Value7, string RECEIVER_ID)
- {
- // 적용테이블 :
- // "TB_DEVICE" - RECEIVER_ID 있음
- string SQL = null;
- if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
- {
- SQL = "SELECT DEVICE_NAME " +
- "FROM " + TableName + " " +
- "WHERE " + TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "' and " +
- TableName + "." + Column3 + " = '" + Value3 + "' and " + TableName + "." + Column4 + " = '" + Value4 + "' and " +
- TableName + "." + Column5 + " = '" + Value5 + "' and " + TableName + "." + Column6 + " = '" + Value6 + "' and " +
- TableName + "." + Column7 + " = '" + Value7 + "';";
- }
- else
- {
- SQL = "SELECT DEVICE_NAME " +
- "FROM " + TableName + " " +
- "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 + "' and " +
- TableName + "." + Column7 + " = '" + Value7 + "';";
- }
- return SQL;
- }
- // Where절을 이용하여 특정 Table의 모든 Column 값 조회해오기
- // TB_GROUP_DEVICE 테이블에 특성화되어 있음
- public static string SearchAll_Table_Value(string TableName, string Column1, string Value1, string Column2, int Value2,
- string Column3, int Value3, string Column4, int Value4,
- string Column5, int Value5, string Column6, int Value6,
- string Column7, string Value7, string choice, string Group_Map_RECEIVER_ID, string RECEIVER_ID)
- {
- // 적용테이블 :
- // "TB_MAP_PROCESS" "TB_EMERGENCY_BROADCASTION" "TB_GROUP_DEVICE" - RECEIVER_ID 있음
- string SQL = null;
- if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
- {
- SQL = "SELECT * " +
- "FROM " + TableName + " " +
- "WHERE " + TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "' and " +
- TableName + "." + Column3 + " = '" + Value3 + "' and " + TableName + "." + Column4 + " = '" + Value4 + "' and " +
- TableName + "." + Column5 + " = '" + Value5 + "' and " + TableName + "." + Column6 + " = '" + Value6 + "' and " +
- TableName + "." + Column7 + " = '" + Value7 + "';";
- }
- else
- {
- SQL = "SELECT * " +
- "FROM " + TableName + " " +
- "WHERE " + TableName + "." + choice + " = '" + Group_Map_RECEIVER_ID + "' and " + 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 + "' and " +
- TableName + "." + Column7 + " = '" + Value7 + "';";
- }
- return SQL;
- }
- // TB_EMERGENCY_BROADCASTION 전용
- public static string SearchAll_TB_EMERGENCY_BROADCASTION(string TableName, string Column1, int Value1, string Column2, int Value2,
- string Column3, int Value3, string Column4, int Value4,
- string Column5, int Value5, string Column6, string Value6,
- string RECEIVER_ID)
- {
- // 적용테이블 :
- // "TB_MAP_PROCESS" "TB_EMERGENCY_BROADCASTION" "TB_GROUP_DEVICE" - RECEIVER_ID 있음
- string SQL = null;
- if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
- {
- SQL = "SELECT * " +
- "FROM " + TableName + " " +
- "WHERE " + TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "' and " +
- TableName + "." + Column3 + " = '" + Value3 + "' and " + TableName + "." + Column4 + " = '" + Value4 + "' and " +
- TableName + "." + Column5 + " = '" + Value5 + "' and " + TableName + "." + Column6 + " = '" + Value6 + "';";
- }
- else
- {
- SQL = "SELECT * " +
- "FROM " + TableName + " " +
- "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 + "';";
- }
- return SQL;
- }
- // IO 디바이스를 찾는데 특성화되어 있음
- 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 RECEIVER_ID)
- {
- // 적용테이블 :
- // "TB_DEVICE" - RECEIVER_ID 있음
- string SQL = null;
- if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
- {
- SQL = "SELECT * " +
- "FROM " + TableName + " " +
- "WHERE " + TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "' and " +
- TableName + "." + Column3 + " = '" + Value3 + "' and " + TableName + "." + Column4 + " = '" + Value4 + "' and " +
- TableName + "." + Column5 + " = '" + Value5 + "';";
- }
- else
- {
- SQL = "SELECT * " +
- "FROM " + TableName + " " +
- "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 + "';";
- }
- return SQL;
- }
- // 특정 Table의 특정 Column 값 조회해오기
- public static string Search_Column_Table_Value(string TableName, string ColumnName)
- {
- string SQL = "SELECT " + TableName + "." + ColumnName + " " +
- "FROM " + TableName + ";";
- return SQL;
- }
- // BOARD 테이블 접근
- public static string Search_BoardTable(string COMM_ID, string BOARD_ID, string USE_FLAG, string RECEIVER_ID)
- {
- // 수정
- string SQL = null;
- SQL += "SELECT * ";
- SQL += "FROM TB_BOARD ";
- SQL += "WHERE " + "RECEIVER_ID = '" + RECEIVER_ID + "' and ";
- SQL += " COMM_ID = '" + COMM_ID + "' ";
- if (BOARD_ID.Length != 0) SQL += "AND BOARD_ID = '" + BOARD_ID + "'";
- if (USE_FLAG.Length != 0) SQL += "AND USE_FLAG = '" + USE_FLAG + "'";
- SQL += " order by BOARD_ID ASC";
- return SQL;
- }
- // Config 테이블 접근
- public static string Search_ConfigTable(int RECEIVER_ID)
- {
- // 적용테이블 :
- // "TB_CONFIG" - RECEIVER_ID 있음 (자신의 아이디를 조회하는 테이블)
- string SQL = null;
- SQL += "SELECT * ";
- SQL += "FROM TB_CONFIG ";
- SQL += "WHERE RECEIVER_ID = " + RECEIVER_ID;
- return SQL;
- }
- // Repeater 테이블 접근
- public static string Search_RepeaterTable(string COMM_ID, string BOARD_ID, string LOOP_NO, string RECEIVER_ID)
- {
- // 적용테이블 :
- // "TB_REPEATER" - RECEIVER_ID 있음
- string SQL = null;
- if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
- {
- SQL += "SELECT * ";
- SQL += "FROM TB_REPEATER ";
- SQL += "WHERE COMM_ID = '" + COMM_ID + "' ";
- if (BOARD_ID.Length != 0) SQL += "AND BOARD_ID = '" + BOARD_ID + "'";
- if (LOOP_NO.Length != 0) SQL += "AND LOOP_NO = '" + LOOP_NO + "'";
- }
- else
- {
- SQL += "SELECT * ";
- SQL += "FROM TB_REPEATER ";
- SQL += "WHERE " + "TB_REPEATER" + "." + "RECEIVER_ID" + " = '" + RECEIVER_ID + "' and ";
- SQL += " COMM_ID = '" + COMM_ID + "' ";
- if (BOARD_ID.Length != 0) SQL += "AND BOARD_ID = '" + BOARD_ID + "'";
- if (LOOP_NO.Length != 0) SQL += "AND LOOP_NO = '" + LOOP_NO + "'";
- }
- return SQL;
- }
- // Device 테이블 접근
- // BOARD_ID에 값이 있을 경우 조회
- 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)
- {
- string SQL = null;
- 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 ";
- SQL += "FROM TB_DEVICE ";
- SQL += "LEFT OUTER JOIN TB_DEVICE_TYPE ON TB_DEVICE.DEVICE_TYPE = TB_DEVICE_TYPE.DEVICE_TYPE ";
- SQL += "LEFT OUTER JOIN TB_POSITION_CODE ON TB_DEVICE.POSITION_CODE = TB_POSITION_CODE.POSITION_CODE ";
- SQL += "WHERE TB_DEVICE.COMM_ID = '" + COMM_ID + "' ";
- if (RECEIVER_ID.Length != 0) SQL += "AND TB_DEVICE.RECEIVER_ID = '" + RECEIVER_ID + "' ";
- if (INOUT_TYPE.Length != 0) SQL += "AND TB_DEVICE.INOUT_TYPE = '" + INOUT_TYPE + "' ";
- if (BOARD_ID.Length != 0) SQL += "AND TB_DEVICE.BOARD_ID = '" + BOARD_ID + "' "; // 특정보드 아이디만
- if (LOOP_NO.Length != 0) SQL += "AND TB_DEVICE.LOOP_NO = '" + LOOP_NO + "' ";
- if (REPEATER_ID.Length != 0) SQL += "AND TB_DEVICE.REPEATER_ID = '" + REPEATER_ID + "' ";
- if (POSITION_CODE.Length != 0) SQL += "AND TB_POSITION_CODE.POSITION_NAME like '%" + POSITION_CODE + "%' ";
- if (DEVICE_TYPE_NAME.Length != 0) SQL += "AND TB_DEVICE_TYPE.DEVICE_TYPE_NAME like '%" + DEVICE_TYPE_NAME + "%' ";
- if (DEVICE_NAME.Length != 0) SQL += "AND TB_DEVICE.DEVICE_NAME like '%" + DEVICE_NAME + "%' ";
- return SQL;
- }
- // BOARD_ID에 값이 ""일 경우
- 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)
- {
- string SQL = null;
- 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 ";
- SQL += "FROM TB_DEVICE ";
- SQL += "LEFT OUTER JOIN TB_DEVICE_TYPE ON TB_DEVICE.DEVICE_TYPE = TB_DEVICE_TYPE.DEVICE_TYPE ";
- SQL += "LEFT OUTER JOIN TB_POSITION_CODE ON TB_DEVICE.POSITION_CODE = TB_POSITION_CODE.POSITION_CODE ";
- SQL += "WHERE TB_DEVICE.COMM_ID = '" + COMM_ID + "' ";
- if (RECEIVER_ID.Length != 0) SQL += "AND TB_DEVICE.RECEIVER_ID = '" + RECEIVER_ID + "' ";
- if (INOUT_TYPE.Length != 0) SQL += "AND TB_DEVICE.INOUT_TYPE = '" + INOUT_TYPE + "' ";
- if (BOARD_ID.Length != 0) SQL += "AND TB_DEVICE.BOARD_ID < " + BOARD_ID + " "; // 특정보드 아이디 하위범위
- if (LOOP_NO.Length != 0) SQL += "AND TB_DEVICE.LOOP_NO = '" + LOOP_NO + "' ";
- if (REPEATER_ID.Length != 0) SQL += "AND TB_DEVICE.REPEATER_ID = '" + REPEATER_ID + "' ";
- if (POSITION_CODE.Length != 0) SQL += "AND TB_POSITION_CODE.POSITION_NAME like '%" + POSITION_CODE + "%' ";
- if (DEVICE_TYPE_NAME.Length != 0) SQL += "AND TB_DEVICE_TYPE.DEVICE_TYPE_NAME like '%" + DEVICE_TYPE_NAME + "%' ";
- if (DEVICE_NAME.Length != 0) SQL += "AND TB_DEVICE.DEVICE_NAME like '%" + DEVICE_NAME + "%' ";
- return SQL;
- }
- // BOARD_ID에 값이 ""일 경우 // cyim 2016.12.05 : 수신기 모델별 내부 로직 변경
- 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)
- {
- string SQL = null;
- 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 ";
- SQL += "FROM TB_DEVICE ";
- SQL += "LEFT OUTER JOIN TB_DEVICE_TYPE ON TB_DEVICE.DEVICE_TYPE = TB_DEVICE_TYPE.DEVICE_TYPE ";
- SQL += "LEFT OUTER JOIN TB_POSITION_CODE ON TB_DEVICE.POSITION_CODE = TB_POSITION_CODE.POSITION_CODE ";
- SQL += "WHERE TB_DEVICE.COMM_ID = '" + COMM_ID + "' ";
- if (RECEIVER_ID.Length != 0) SQL += "AND TB_DEVICE.RECEIVER_ID = '" + RECEIVER_ID + "' ";
- if (INOUT_TYPE.Length != 0) SQL += "AND TB_DEVICE.INOUT_TYPE = '" + INOUT_TYPE + "' ";
- SQL += "AND TB_DEVICE.BOARD_ID > 0 "; // 보드 아이디 0 이상
- if (LOOP_NO.Length != 0) SQL += "AND TB_DEVICE.LOOP_NO = '" + LOOP_NO + "' ";
- if (REPEATER_ID.Length != 0) SQL += "AND TB_DEVICE.REPEATER_ID = '" + REPEATER_ID + "' ";
- if (POSITION_CODE.Length != 0) SQL += "AND TB_POSITION_CODE.POSITION_NAME like '%" + POSITION_CODE + "%' ";
- if (DEVICE_TYPE_NAME.Length != 0) SQL += "AND TB_DEVICE_TYPE.DEVICE_TYPE_NAME like '%" + DEVICE_TYPE_NAME + "%' ";
- if (DEVICE_NAME.Length != 0) SQL += "AND TB_DEVICE.DEVICE_NAME like '%" + DEVICE_NAME + "%' ";
- return SQL;
- }
- // 밑에 함수는 입력포인트의 속성이 화재이고, 비상방송이 매칭된것외에 전부 보여준다
- public static string Search_DEVICETable_With_EMERGENCY(
- 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,
- string AREA, string STAIR, string FLOOR, // 비상방송 조건 추가
- bool Mapping) // Device 테이블과 Emergency 테이블과 매핑되어있는지 조건 추가
- {
- string SQL = null;
- 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 ";
- // 입력 포인트를 위해 비상방송코드 정보까지 같이 출력한다
- SQL += ",TB_EMERGENCY_BROADCASTION.AREA_CODE, TB_EMERGENCY_BROADCASTION.STAIR_CODE, TB_EMERGENCY_BROADCASTION.FLOOR_CODE ";
- SQL += "FROM TB_DEVICE ";
- SQL += "LEFT OUTER JOIN TB_DEVICE_TYPE ON TB_DEVICE.DEVICE_TYPE = TB_DEVICE_TYPE.DEVICE_TYPE ";
- SQL += "LEFT OUTER JOIN TB_POSITION_CODE ON TB_DEVICE.POSITION_CODE = TB_POSITION_CODE.POSITION_CODE ";
- // 입력 포인트를 위해 Join 추가
- SQL += "LEFT OUTER JOIN TB_EMERGENCY_BROADCASTION ON (TB_DEVICE.RECEIVER_ID = TB_EMERGENCY_BROADCASTION.RECEIVER_ID ";
- SQL += " AND TB_EMERGENCY_BROADCASTION.COMM_ID = TB_DEVICE.COMM_ID AND TB_EMERGENCY_BROADCASTION.BOARD_ID = TB_DEVICE.BOARD_ID";
- SQL += " AND TB_EMERGENCY_BROADCASTION.LOOP_NO = TB_DEVICE.LOOP_NO AND TB_EMERGENCY_BROADCASTION.REPEATER_ID = TB_DEVICE.REPEATER_ID";
- SQL += " AND TB_EMERGENCY_BROADCASTION.DEVICE_ID = TB_DEVICE.DEVICE_ID AND TB_EMERGENCY_BROADCASTION.INOUT_TYPE = TB_DEVICE.INOUT_TYPE) ";
- SQL += "WHERE TB_DEVICE.COMM_ID = '" + COMM_ID + "' ";
- if (RECEIVER_ID.Length != 0) SQL += "AND TB_DEVICE.RECEIVER_ID = '" + RECEIVER_ID + "' ";
- if (INOUT_TYPE.Length != 0) SQL += "AND TB_DEVICE.INOUT_TYPE = '" + INOUT_TYPE + "' ";
- if (BOARD_ID == "15") // BOARD_ID_All : 통신카드 15번 카드를 제외하고 전부를 찾는 경우에는 파라미터값이 15로 들어온다, 특정 통신 카드를 찾을때는 통신카드 아이디가 파라미터 값이다.
- {
- if (BOARD_ID.Length != 0) SQL += "AND TB_DEVICE.BOARD_ID < " + BOARD_ID + " ";
- }
- else
- {
- if (BOARD_ID.Length != 0) SQL += "AND TB_DEVICE.BOARD_ID = '" + BOARD_ID + "' ";
- }
- if (LOOP_NO.Length != 0) SQL += "AND TB_DEVICE.LOOP_NO = '" + LOOP_NO + "' ";
- if (REPEATER_ID.Length != 0) SQL += "AND TB_DEVICE.REPEATER_ID = '" + REPEATER_ID + "' ";
- if (POSITION_CODE.Length != 0) SQL += "AND TB_POSITION_CODE.POSITION_NAME like '%" + POSITION_CODE + "%' ";
- if (DEVICE_TYPE_NAME.Length != 0) SQL += "AND TB_DEVICE_TYPE.DEVICE_TYPE_NAME like '%" + DEVICE_TYPE_NAME + "%' ";
- if (DEVICE_NAME.Length != 0) SQL += "AND TB_DEVICE.DEVICE_NAME like '%" + DEVICE_NAME + "%' ";
- // 비상방송 조회조건
- if (AREA.Length != 0) SQL += "AND TB_EMERGENCY_BROADCASTION.AREA_CODE = '" + AREA + "' ";
- if (STAIR.Length != 0) SQL += "AND TB_EMERGENCY_BROADCASTION.STAIR_CODE = '" + STAIR + "' ";
- if (FLOOR.Length != 0) SQL += "AND TB_EMERGENCY_BROADCASTION.FLOOR_CODE = '" + FLOOR + "' ";
- // 맵핑이 된것을 찾으려면
- if (Mapping == true)
- {
- SQL += " AND TB_EMERGENCY_BROADCASTION.COMM_ID = TB_DEVICE.COMM_ID AND TB_EMERGENCY_BROADCASTION.BOARD_ID = TB_DEVICE.BOARD_ID";
- SQL += " AND TB_EMERGENCY_BROADCASTION.LOOP_NO = TB_DEVICE.LOOP_NO AND TB_EMERGENCY_BROADCASTION.REPEATER_ID = TB_DEVICE.REPEATER_ID";
- SQL += " AND TB_EMERGENCY_BROADCASTION.DEVICE_ID = TB_DEVICE.DEVICE_ID AND TB_EMERGENCY_BROADCASTION.INOUT_TYPE = TB_DEVICE.INOUT_TYPE";
- }
- return SQL;
- }
- // 밑에 함수는 입력포인트의 속성이 화재이고, 비상방송이 매칭된것외에 전부 보여준다 // cyim 2016.12.05 : 수신기 모델별 내부 로직 변경
- public static string Search_DEVICETable_With_EMERGENCY_BigReceiver(
- 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,
- string AREA, string STAIR, string FLOOR, // 비상방송 조건 추가
- bool Mapping) // Device 테이블과 Emergency 테이블과 매핑되어있는지 조건 추가
- {
- string SQL = null;
- 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 ";
- // 입력 포인트를 위해 비상방송코드 정보까지 같이 출력한다
- SQL += ",TB_EMERGENCY_BROADCASTION.AREA_CODE, TB_EMERGENCY_BROADCASTION.STAIR_CODE, TB_EMERGENCY_BROADCASTION.FLOOR_CODE ";
- SQL += "FROM TB_DEVICE ";
- SQL += "LEFT OUTER JOIN TB_DEVICE_TYPE ON TB_DEVICE.DEVICE_TYPE = TB_DEVICE_TYPE.DEVICE_TYPE ";
- SQL += "LEFT OUTER JOIN TB_POSITION_CODE ON TB_DEVICE.POSITION_CODE = TB_POSITION_CODE.POSITION_CODE ";
- // 입력 포인트를 위해 Join 추가
- SQL += "LEFT OUTER JOIN TB_EMERGENCY_BROADCASTION ON (TB_DEVICE.RECEIVER_ID = TB_EMERGENCY_BROADCASTION.RECEIVER_ID ";
- SQL += " AND TB_EMERGENCY_BROADCASTION.COMM_ID = TB_DEVICE.COMM_ID AND TB_EMERGENCY_BROADCASTION.BOARD_ID = TB_DEVICE.BOARD_ID";
- SQL += " AND TB_EMERGENCY_BROADCASTION.LOOP_NO = TB_DEVICE.LOOP_NO AND TB_EMERGENCY_BROADCASTION.REPEATER_ID = TB_DEVICE.REPEATER_ID";
- SQL += " AND TB_EMERGENCY_BROADCASTION.DEVICE_ID = TB_DEVICE.DEVICE_ID AND TB_EMERGENCY_BROADCASTION.INOUT_TYPE = TB_DEVICE.INOUT_TYPE) ";
- SQL += "WHERE TB_DEVICE.COMM_ID = '" + COMM_ID + "' ";
- if (RECEIVER_ID.Length != 0) SQL += "AND TB_DEVICE.RECEIVER_ID = '" + RECEIVER_ID + "' ";
- if (INOUT_TYPE.Length != 0) SQL += "AND TB_DEVICE.INOUT_TYPE = '" + INOUT_TYPE + "' ";
- if (BOARD_ID == null || BOARD_ID.Length == 0)
- {
- SQL += "AND TB_DEVICE.BOARD_ID > 0 "; // cyim 2016.09.12 : 잘못된 쿼리 수정 (무조건 통신보드는 0이상 조회)
- }
- else
- {
- if (BOARD_ID.Length != 0) SQL += "AND TB_DEVICE.BOARD_ID = '" + BOARD_ID + "' ";
- }
- if (LOOP_NO.Length != 0) SQL += "AND TB_DEVICE.LOOP_NO = '" + LOOP_NO + "' ";
- if (REPEATER_ID.Length != 0) SQL += "AND TB_DEVICE.REPEATER_ID = '" + REPEATER_ID + "' ";
- if (POSITION_CODE.Length != 0) SQL += "AND TB_POSITION_CODE.POSITION_NAME like '%" + POSITION_CODE + "%' ";
- if (DEVICE_TYPE_NAME.Length != 0) SQL += "AND TB_DEVICE_TYPE.DEVICE_TYPE_NAME like '%" + DEVICE_TYPE_NAME + "%' ";
- if (DEVICE_NAME.Length != 0) SQL += "AND TB_DEVICE.DEVICE_NAME like '%" + DEVICE_NAME + "%' ";
- // 비상방송 조회조건
- if (AREA.Length != 0) SQL += "AND TB_EMERGENCY_BROADCASTION.AREA_CODE = '" + AREA + "' ";
- if (STAIR.Length != 0) SQL += "AND TB_EMERGENCY_BROADCASTION.STAIR_CODE = '" + STAIR + "' ";
- if (FLOOR.Length != 0) SQL += "AND TB_EMERGENCY_BROADCASTION.FLOOR_CODE = '" + FLOOR + "' ";
- // 맵핑이 된것을 찾으려면
- if (Mapping == true)
- {
- SQL += " AND TB_EMERGENCY_BROADCASTION.COMM_ID = TB_DEVICE.COMM_ID AND TB_EMERGENCY_BROADCASTION.BOARD_ID = TB_DEVICE.BOARD_ID";
- SQL += " AND TB_EMERGENCY_BROADCASTION.LOOP_NO = TB_DEVICE.LOOP_NO AND TB_EMERGENCY_BROADCASTION.REPEATER_ID = TB_DEVICE.REPEATER_ID";
- SQL += " AND TB_EMERGENCY_BROADCASTION.DEVICE_ID = TB_DEVICE.DEVICE_ID AND TB_EMERGENCY_BROADCASTION.INOUT_TYPE = TB_DEVICE.INOUT_TYPE";
- }
- return SQL;
- }
- // TB_GROUP_DEVICE 테이블 접근
- public static string Search_Gruop_DeviceTable(string DEVICE_GROUP, string RECEIVER_ID)
- {
- // 적용테이블 :
- // "TB_GROUP_DEVICE" - RECEIVER_ID 있음
- string SQL = null;
- if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
- {
- SQL += "SELECT TB_DEVICE_TYPE.DEVICE_TYPE_NAME, TB_POSITION_CODE.POSITION_NAME, TB_DEVICE.DEVICE_NAME, TB_GROUP_DEVICE.*, TB_DEVICE.RECEIVER_ID ";
- SQL += "FROM TB_GROUP_DEVICE ";
- 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 ";
- SQL += "AND TB_GROUP_DEVICE.BOARD_ID = TB_DEVICE.BOARD_ID AND TB_GROUP_DEVICE.LOOP_NO = TB_DEVICE.LOOP_NO ";
- SQL += "AND TB_GROUP_DEVICE.REPEATER_ID = TB_DEVICE.REPEATER_ID AND TB_GROUP_DEVICE.DEVICE_ID = TB_DEVICE.DEVICE_ID ";
- SQL += "AND TB_GROUP_DEVICE.INOUT_TYPE = TB_DEVICE.INOUT_TYPE ";
- SQL += "LEFT OUTER JOIN TB_DEVICE_TYPE ON TB_DEVICE.DEVICE_TYPE = TB_DEVICE_TYPE.DEVICE_TYPE ";
- SQL += "LEFT OUTER JOIN TB_POSITION_CODE ON TB_DEVICE.POSITION_CODE = TB_POSITION_CODE.POSITION_CODE ";
- SQL += "WHERE TB_GROUP_DEVICE.GROUP_ID = '" + DEVICE_GROUP + "' ";
- }
- else
- {
- SQL += "SELECT TB_DEVICE_TYPE.DEVICE_TYPE_NAME, TB_POSITION_CODE.POSITION_NAME, TB_DEVICE.DEVICE_NAME, TB_GROUP_DEVICE.*, TB_DEVICE.RECEIVER_ID ";
- SQL += "FROM TB_GROUP_DEVICE ";
- 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 ";
- SQL += "AND TB_GROUP_DEVICE.BOARD_ID = TB_DEVICE.BOARD_ID AND TB_GROUP_DEVICE.LOOP_NO = TB_DEVICE.LOOP_NO ";
- SQL += "AND TB_GROUP_DEVICE.REPEATER_ID = TB_DEVICE.REPEATER_ID AND TB_GROUP_DEVICE.DEVICE_ID = TB_DEVICE.DEVICE_ID ";
- SQL += "AND TB_GROUP_DEVICE.INOUT_TYPE = TB_DEVICE.INOUT_TYPE ";
- SQL += "LEFT OUTER JOIN TB_DEVICE_TYPE ON TB_DEVICE.DEVICE_TYPE = TB_DEVICE_TYPE.DEVICE_TYPE ";
- SQL += "LEFT OUTER JOIN TB_POSITION_CODE ON TB_DEVICE.POSITION_CODE = TB_POSITION_CODE.POSITION_CODE ";
- SQL += "WHERE TB_GROUP_DEVICE.RECEIVER_ID = '" + RECEIVER_ID + "' AND";
- SQL += " TB_GROUP_DEVICE.GROUP_ID = '" + DEVICE_GROUP + "' ";
- }
- return SQL;
- }
- // TB_MAP_PROCESS 테이블 접근
- public static string Search_Map_ProcessTable(string MAP_NO, string MAP_TYPE, string RECEIVER_ID)
- {
- // 적용테이블 :
- // "TB_MAP_PROCESS" - RECEIVER_ID 있음
- string SQL = null;
- if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
- {
- 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.* ";
- SQL += "FROM TB_MAP_PROCESS ";
- 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 ";
- SQL += "AND TB_MAP_PROCESS.BOARD_ID = TB_DEVICE.BOARD_ID AND TB_MAP_PROCESS.LOOP_NO = TB_DEVICE.LOOP_NO ";
- SQL += "AND TB_MAP_PROCESS.REPEATER_ID = TB_DEVICE.REPEATER_ID AND TB_MAP_PROCESS.DEVICE_ID = TB_DEVICE.DEVICE_ID ";
- SQL += "AND TB_MAP_PROCESS.INOUT_TYPE = TB_DEVICE.INOUT_TYPE ";
- SQL += "LEFT OUTER JOIN TB_DEVICE_TYPE ON TB_DEVICE.DEVICE_TYPE = TB_DEVICE_TYPE.DEVICE_TYPE ";
- SQL += "LEFT OUTER JOIN TB_POSITION_CODE ON TB_DEVICE.POSITION_CODE = TB_POSITION_CODE.POSITION_CODE ";
- SQL += "WHERE TB_MAP_PROCESS.MAP_NO = '" + MAP_NO + "' AND TB_MAP_PROCESS.MAP_TYPE = '" + MAP_TYPE + "' ";
- }
- else
- {
- 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.* ";
- SQL += "FROM TB_MAP_PROCESS ";
- 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 ";
- SQL += "AND TB_MAP_PROCESS.BOARD_ID = TB_DEVICE.BOARD_ID AND TB_MAP_PROCESS.LOOP_NO = TB_DEVICE.LOOP_NO ";
- SQL += "AND TB_MAP_PROCESS.REPEATER_ID = TB_DEVICE.REPEATER_ID AND TB_MAP_PROCESS.DEVICE_ID = TB_DEVICE.DEVICE_ID ";
- SQL += "AND TB_MAP_PROCESS.INOUT_TYPE = TB_DEVICE.INOUT_TYPE ";
- SQL += "LEFT OUTER JOIN TB_DEVICE_TYPE ON TB_DEVICE.DEVICE_TYPE = TB_DEVICE_TYPE.DEVICE_TYPE ";
- SQL += "LEFT OUTER JOIN TB_POSITION_CODE ON TB_DEVICE.POSITION_CODE = TB_POSITION_CODE.POSITION_CODE ";
- SQL += "WHERE TB_MAP_PROCESS.MAP_RECEIVER_ID = '" + RECEIVER_ID + "' AND";
- SQL += " TB_MAP_PROCESS.MAP_NO = '" + MAP_NO + "' AND TB_MAP_PROCESS.MAP_TYPE = '" + MAP_TYPE + "' ";
- }
- return SQL;
- }
- public static string Search_Map_ProcessTable(string MAP_NO, string MAP_TYPE, string PROCESS_TYPE, string RECEIVER_ID)
- {
- // 적용테이블 :
- // "TB_MAP_PROCESS" - RECEIVER_ID 있음
- string SQL = null;
- if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
- {
- 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.* ";
- SQL += "FROM TB_MAP_PROCESS ";
- 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 ";
- SQL += "AND TB_MAP_PROCESS.BOARD_ID = TB_DEVICE.BOARD_ID AND TB_MAP_PROCESS.LOOP_NO = TB_DEVICE.LOOP_NO ";
- SQL += "AND TB_MAP_PROCESS.REPEATER_ID = TB_DEVICE.REPEATER_ID AND TB_MAP_PROCESS.DEVICE_ID = TB_DEVICE.DEVICE_ID ";
- SQL += "AND TB_MAP_PROCESS.INOUT_TYPE = TB_DEVICE.INOUT_TYPE ";
- SQL += "LEFT OUTER JOIN TB_DEVICE_TYPE ON TB_DEVICE.DEVICE_TYPE = TB_DEVICE_TYPE.DEVICE_TYPE ";
- SQL += "LEFT OUTER JOIN TB_POSITION_CODE ON TB_DEVICE.POSITION_CODE = TB_POSITION_CODE.POSITION_CODE ";
- 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 + "' ";
- }
- else
- {
- 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.* ";
- SQL += "FROM TB_MAP_PROCESS ";
- 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 ";
- SQL += "AND TB_MAP_PROCESS.BOARD_ID = TB_DEVICE.BOARD_ID AND TB_MAP_PROCESS.LOOP_NO = TB_DEVICE.LOOP_NO ";
- SQL += "AND TB_MAP_PROCESS.REPEATER_ID = TB_DEVICE.REPEATER_ID AND TB_MAP_PROCESS.DEVICE_ID = TB_DEVICE.DEVICE_ID ";
- SQL += "AND TB_MAP_PROCESS.INOUT_TYPE = TB_DEVICE.INOUT_TYPE ";
- SQL += "LEFT OUTER JOIN TB_DEVICE_TYPE ON TB_DEVICE.DEVICE_TYPE = TB_DEVICE_TYPE.DEVICE_TYPE ";
- SQL += "LEFT OUTER JOIN TB_POSITION_CODE ON TB_DEVICE.POSITION_CODE = TB_POSITION_CODE.POSITION_CODE ";
- SQL += "WHERE TB_MAP_PROCESS.MAP_RECEIVER_ID = '" + RECEIVER_ID + "' AND";
- SQL += " TB_MAP_PROCESS.MAP_NO = '" + MAP_NO + "' AND TB_MAP_PROCESS.MAP_TYPE = '" + MAP_TYPE + "' AND TB_MAP_PROCESS.PROCESS_TYPE = '" + PROCESS_TYPE + "' ";
- }
- return SQL;
- }
- public static string Search_Board_Table(int ReceiverID, int CommID, int BoardID)
- {
- // 적용테이블 :
- // "TB_BOARD" - RECEIVER_ID 있음
- string SQL = null;
- SQL += "SELECT * ";
- SQL += "FROM TB_BOARD ";
- SQL += "WHERE RECEIVER_ID = " + ReceiverID.ToString() + " and COMM_ID = " + CommID.ToString() + " and BOARD_ID = " + BoardID.ToString();
- return SQL;
- }
- // cyim 2016.12.02 : 수신기 모델 설정 기능 추가
- public static string TB_RECEIVER_table_Insert(int iReceiverID, string sReceiverName, string sIP, int iEventPort, int iCommandPort, string sUseFlag, string iMODEL)
- {
- // 적용테이블 :
- // "TB_RECEIVER" - RECEIVER_ID 있음
- string SQL = null;
- SQL = "INSERT INTO TB_RECEIVER ";
- SQL += "(RECEIVER_ID,RECEIVER_NAME,IP_ADDRESS,EVENT_PORT_NO,COMMAND_PORT_NO,REDUNDANT_FLAG,USE_FLAG,MODEL,UPDATE_USER_ID,UPDATE_TIME)";
- SQL += " VALUES ({0},'{1}','{2}',{3},{4},'{5}','{6}','{7}','FireManager',current_timestamp)";
- SQL = String.Format(SQL, iReceiverID, sReceiverName, sIP, iEventPort, iCommandPort, "N", sUseFlag, iMODEL);
- return SQL;
- }
- public static string TB_COMM_table_Insert(int iReceiverID, int iCommID, string sCommName, int iCommLoop, string sCommPort, int iBaudRate, string sParity,
- int iDataBit, string sStopBit, int iTimeOut, int iDelay, int iDeltaVolt, int iRetryCount, string sUseFlag)
- {
- // 적용테이블 :
- // "TB_COMM" - RECEIVER_ID 있음
- string SQL = null;
- SQL = "INSERT INTO TB_COMM ( ";
- SQL += " RECEIVER_ID,COMM_ID,COMM_NAME,COMM_LOOP,COMM_TYPE,COMM_PORT";
- SQL += ",BAUD_RATE,PARITY,DATA_BITS,STOP_BITS";
- SQL += ",COMM_TIMEOUT,COMM_DELAY, DELTA_VOLT, RETRY_COUNT, LOG_FLAG, USE_FLAG,UPDATE_USER_ID,UPDATE_TIME";
- SQL += ") VALUES ({0},{1},'{2}','{3}','R','{4}',{5},'{6}',{7}, {8}, {9},{10}, {11}, {12}, 'N','{13}','FireManager',current_timestamp)";
- SQL = String.Format(SQL, iReceiverID, iCommID, sCommName, iCommLoop, sCommPort, iBaudRate, sParity.Substring(0, 1), iDataBit, sStopBit, iTimeOut, iDelay, iDeltaVolt, iRetryCount, sUseFlag);
- return SQL;
- }
- public static string TB_BOARD_table_Insert(int iReceiverID, int iCommID, int iBoardID, string sBoardName, string sCH1_UseFlag, string sCH2_UseFlag, string sUseFlag)
- {
- // 적용테이블 :
- // "TB_BOARD" - RECEIVER_ID 있음
- string SQL = null;
- SQL = "INSERT INTO TB_BOARD ( ";
- SQL += " RECEIVER_ID,COMM_ID,BOARD_ID,BOARD_NAME,LOOP0_USE_FLAG,LOOP1_USE_FLAG ";
- SQL += ",USE_FLAG, LOOP0_CUT_FLAG, LOOP1_CUT_FLAG, UPDATE_USER_ID,UPDATE_TIME";
- SQL += ") VALUES ({0}, {1}, {2},'{3}','{4}','{5}','{6}','N','N','FireManager',current_timestamp)";
- SQL = String.Format(SQL, iReceiverID, iCommID, iBoardID, sBoardName, sCH1_UseFlag, sCH2_UseFlag, sUseFlag);
- return SQL;
- }
- // cyim 2016.09.21 : TB_BOARD 에 루프 0~3 저장 하도록 한다 // cyim 2016.12.05 : 수신기 모델별 내부 로직 변경
- public static string TB_BOARD_table_Insert_BigReceiver(int iReceiverID, int iCommID, int iBoardID, string sBoardName,
- string sCH1_UseFlag, string sCH2_UseFlag, string sCH3_UseFlag, string sCH4_UseFlag, string sUseFlag)
- {
- // 적용테이블 :
- // "TB_BOARD" - RECEIVER_ID 있음
- string SQL = null;
- SQL = "INSERT INTO TB_BOARD ( ";
- SQL += " RECEIVER_ID,COMM_ID,BOARD_ID,BOARD_NAME,LOOP0_USE_FLAG,LOOP1_USE_FLAG,LOOP2_USE_FLAG,LOOP3_USE_FLAG ";
- SQL += ",USE_FLAG, LOOP0_CUT_FLAG, LOOP1_CUT_FLAG, LOOP2_CUT_FLAG, LOOP3_CUT_FLAG, UPDATE_USER_ID,UPDATE_TIME";
- SQL += ") VALUES ({0}, {1}, {2},'{3}','{4}','{5}','{6}','{7}','{8}','N','N','N','N','FireManager',current_timestamp)";
- SQL = String.Format(SQL, iReceiverID, iCommID, iBoardID, sBoardName, sCH1_UseFlag, sCH2_UseFlag, sCH3_UseFlag, sCH4_UseFlag, sUseFlag);
- return SQL;
- }
- 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)
- {
- // 적용테이블 :
- // "TB_CONFIG" - RECEIVER_ID 있음
- string SQL = null;
- SQL = "INSERT INTO TB_CONFIG (RECEIVER_ID,FIRE_ACCUMULATION_MODE,FIRE_ACCUMULATION_TIME";
- SQL += ",FIRE_PROCESS_MODE,SOUND_PROCESS_MODE,MASTER_USER_ID,MASTER_PASSWORD";
- SQL += ",BUILDING_NAME, JUMP_FIELD, MASTER_FLAG, UPDATE_USER_ID,TEST_RUN_FLAG, UPDATE_TIME)";
- SQL += " VALUES ({0},'{1}',{2},'{3}','N','{4}','{5}','{6}','{7}','{8}', 'FireManager','{9}',current_timestamp)";
- SQL = String.Format(SQL, iReceiverID, sFireAccumulationMode, iFireAccumulationTime
- , sFireProcessMode, sMASTER_USER_ID, sMASTER_PASSWORD
- , sBUILDING_NAME, sJumpField, sMASTER_FLAG, sTEST_RUN_FLAG); // 네트워크 수신기 설정 기능 추가 (true : 마스터, false : 슬레이브)
- return SQL;
- }
- // 특정 Table의 모든 레코드 삭제하기
- public static string Delete_Table(string TableName, string RECEIVER_ID)
- {
- // 적용테이블 :
- // "TB_BOARD" "TB_COMM" "TB_CONFIG" "TB_RECEIVER" "TB_DEVICE" "TB_REPEATER" "TB_EMERGENCY_BROADCASTION" - RECEIVER_ID 있음
- // "TB_POSITION_CODE" - RECEIVER_ID 없음
- string SQL = null;
- if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
- {
- SQL = "DELETE " +
- "FROM " + TableName + ";";
- }
- else
- {
- SQL = "DELETE " +
- "FROM " + TableName + " " +
- "WHERE " + TableName + "." + "RECEIVER_ID" + " = '" + RECEIVER_ID + "';";
- }
- return SQL;
- }
- // Where절을 이용하여 특정 Table의 레코드 삭제하기
- public static string Delete_Table(string TableName, string Column, string Value, string RECEIVER_ID)
- {
- // 적용테이블 :
- // "TB_GROUP" "TB_GROUP_DEVICE" "TB_MAP" "TB_MAP_PROCESS" - RECEIVER_ID 있음
- string SQL = null;
- if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
- {
- SQL = "DELETE " +
- "FROM " + TableName + " " +
- "WHERE " + TableName + "." + Column + " = '" + Value + "';";
- }
- else
- {
- SQL = "DELETE " +
- "FROM " + TableName + " " +
- "WHERE " + TableName + "." + "RECEIVER_ID" + " = '" + RECEIVER_ID + "' and " +
- TableName + "." + Column + " = '" + Value + "';";
- }
- return SQL;
- }
- // Where절을 이용하여 특정 Table의 레코드 삭제하기
- public static string Delete_Table(string TableName, string Column1, string Value1, string Column2, string Value2, string RECEIVER_ID)
- {
- // 적용테이블 :
- // "TB_MAP_PROCESS" - RECEIVER_ID 있음
- string SQL = null;
- if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
- {
- SQL = "DELETE " +
- "FROM " + TableName + " " +
- "WHERE " + TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "';";
- }
- else
- {
- SQL = "DELETE " +
- "FROM " + TableName + " " +
- "WHERE " + TableName + "." + "RECEIVER_ID" + " = '" + RECEIVER_ID + "' and " +
- TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "';";
- }
- return SQL;
- }
- // Where절을 이용하여 특정 Table의 레코드 삭제하기
- public static string Delete_Table(string TableName, string Column1, string Value1, string Column2, string Value2, string Column3, string Value3, string RECEIVER_ID)
- {
- // 적용테이블 :
- // "TB_MAP_PROCESS" - RECEIVER_ID 있음
- string SQL = null;
- if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
- {
- SQL = "DELETE " +
- "FROM " + TableName + " " +
- "WHERE " + TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "' and " +
- TableName + "." + Column3 + " = '" + Value3 + "';";
- }
- else
- {
- SQL = "DELETE " +
- "FROM " + TableName + " " +
- "WHERE " + TableName + "." + "RECEIVER_ID" + " = '" + RECEIVER_ID + "' and " +
- TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "' and " +
- TableName + "." + Column3 + " = '" + Value3 + "';";
- }
- return SQL;
- }
- // Where절을 이용하여 특정 Table의 레코드 삭제하기
- // TB_GROUP_DEVICE 테이블에 특성화되어 있음
- public static string Delete_Table(string TableName, string Column1, string Value1, string Column2, int Value2,
- string Column3, int Value3, string Column4, int Value4,
- string Column5, int Value5, string Column6, int Value6,
- string Column7, string Value7, string RECEIVER_ID)
- {
- // 적용테이블 :
- // "TB_EMERGENCY_BROADCASTION" "TB_GROUP_DEVICE" "TB_MAP_PROCESS" - RECEIVER_ID 있음
- string SQL = null;
- if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
- {
- SQL = "DELETE " +
- "FROM " + TableName + " " +
- "WHERE " + TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "' and " +
- TableName + "." + Column3 + " = '" + Value3 + "' and " + TableName + "." + Column4 + " = '" + Value4 + "' and " +
- TableName + "." + Column5 + " = '" + Value5 + "' and " + TableName + "." + Column6 + " = '" + Value6 + "' and " +
- TableName + "." + Column7 + " = '" + Value7 + "';";
- }
- else
- {
- SQL = "DELETE " +
- "FROM " + TableName + " " +
- "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 + "' and " +
- TableName + "." + Column7 + " = '" + Value7 + "';";
- }
- return SQL;
- }
- public static string Delete_Table(string TableName, string Column1, string Value1, string Column2, int Value2,
- string Column3, int Value3, string Column4, int Value4,
- string Column5, int Value5, string Column6, int Value6,
- string Column7, string Value7, string Column8, string Value8, string RECEIVER_ID)
- {
- // 적용테이블 :
- // "TB_EMERGENCY_BROADCASTION" "TB_GROUP_DEVICE" "TB_MAP_PROCESS" - RECEIVER_ID 있음
- string SQL = null;
- if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
- {
- SQL = "DELETE " +
- "FROM " + TableName + " " +
- "WHERE " + TableName + "." + Column1 + " = '" + Value1 + "' and " + TableName + "." + Column2 + " = '" + Value2 + "' and " +
- TableName + "." + Column3 + " = '" + Value3 + "' and " + TableName + "." + Column4 + " = '" + Value4 + "' and " +
- TableName + "." + Column5 + " = '" + Value5 + "' and " + TableName + "." + Column6 + " = '" + Value6 + "' and " +
- TableName + "." + Column7 + " = '" + Value7 + "' and " + TableName + "." + Column8 + " = '" + Value8 + "';";
- }
- else
- {
- SQL = "DELETE " +
- "FROM " + TableName + " " +
- "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 + "' and " +
- TableName + "." + Column7 + " = '" + Value7 + "' and " + TableName + "." + Column8 + " = '" + Value8 + "';";
- }
- return SQL;
- }
- // TB_DEVICE 전체 카운트
- public static string Count_TB_DEVICE(string COMM_ID, string BOARD_ID, string INOUT_TYPE, string RECEIVER_ID)
- {
- 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 + "' ";
- return SQL;
- }
- // cyim 2016.12.05 : 수신기 모델별 내부 로직 변경 // TB_DEVICE 전체 카운트
- public static string Count_TB_DEVICE_BigReceiver(string COMM_ID, string INOUT_TYPE, string RECEIVER_ID)
- {
- string SQL = "SELECT count(*) FROM TB_DEVICE WHERE TB_DEVICE.COMM_ID = '" + COMM_ID
- + "' AND TB_DEVICE.BOARD_ID > 0 "
- + " AND TB_DEVICE.INOUT_TYPE = '" + INOUT_TYPE + "' AND TB_DEVICE.RECEIVER_ID = '" + RECEIVER_ID + "' ";
- return SQL;
- }
- // 맵연동파일 생성용 쿼리
- public static string MapProcessFile_Create(string RECEIVER_ID)
- {
- // 맵연동설정파일 포맷
- // - 한개의 레코드 길이 : 40 Byte (개행문자 포함)
- // - 구분자 는 |
- // - 마지막에 개행문자 (CR/LF)
- // - 순서 : A.MAP_NO,A.MAP_TYPE (ASC)
- // 1. RECEIVER ID : 3 Bytes (3자리가 아닐 경우 왼쪽 0으로 채움)
- // 2. MAP NO. : 7 Bytes (7자리가 아닐 경우 왼쪽 0으로 채움) // cyim 2015.10.30 : 포맷변경 포함 5->7, 즉 2번째 Map no 가 7자리로 변경
- // 3. MAP TYPE : 1 Bytes (NULL일 경우 공백으로 채움)
- // 4. PROCESS TYPE : 1 Bytes (NULL일 경우 공백으로 채움)
- // 5. COMM. ID : 3 Bytes (3자리가 아닐 경우 왼쪽 0으로 채움)
- // 6. BOARD ID : 3 Bytes (3자리가 아닐 경우 왼쪽 0으로 채움)
- // 7. LOOP NO. : 3 Bytes (3자리가 아닐 경우 왼쪽 0으로 채움)
- // 8. REPEATER ID : 3 Bytes (3자리가 아닐 경우 왼쪽 0으로 채움)
- // 9. DEVICE ID : 3 Bytes (3자리가 아닐 경우 왼쪽 0으로 채움)
- // 10. IN/OUT TYPE : 1 Bytes (NULL일 경우 공백으로 채움)
- // 11. USE FLAG : 1 Bytes (NULL일 경우 공백으로 채움)
- // 12. CR/LF : 2 Bytes
- // ex) C 소스코드
- // // MAP DATA 구성
- // 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자리로 변경
- // nReceiverID,nMapNo,
- // (szMapType[0]!='\0'?szMapType[0]:' '),
- // (szProcessType[0]!='\0'?szProcessType[0]:' '),
- // nCommID,nBoardID,nLoopNo,nRepeaterID,nDeviceID,
- // (szInoutType[0]!='\0'?szInoutType[0]:' '),
- // (szUseFlag[0]!='\0'?szUseFlag[0]:' '));
- // ex) 001|0000001|I|D|001|001|000|002|001|I|Y| // cyim 2015.10.30 : 포맷변경 포함 5->7, 즉 2번째 Map no 가 7자리로 변경
- string SQL = "SELECT (CASE WHEN A.PROCESS_TYPE='G' THEN B.RECEIVER_ID ELSE A.RECEIVER_ID END) as RECEIVER_ID,";
- SQL = SQL + " A.MAP_NO,A.MAP_TYPE,A.PROCESS_TYPE,";
- SQL = SQL + " (CASE WHEN A.PROCESS_TYPE='G' THEN B.COMM_ID ELSE A.COMM_ID END) as COMM_ID,";
- SQL = SQL + " (CASE WHEN A.PROCESS_TYPE='G' THEN B.BOARD_ID ELSE A.BOARD_ID END) as BOARD_ID,";
- SQL = SQL + " (CASE WHEN A.PROCESS_TYPE='G' THEN B.LOOP_NO ELSE A.LOOP_NO END) as LOOP_NO,";
- SQL = SQL + " (CASE WHEN A.PROCESS_TYPE='G' THEN B.REPEATER_ID ELSE A.REPEATER_ID END) as REPEATER_ID,";
- SQL = SQL + " (CASE WHEN A.PROCESS_TYPE='G' THEN B.DEVICE_ID ELSE A.DEVICE_ID END) as DEVICE_ID,";
- SQL = SQL + " (CASE WHEN A.PROCESS_TYPE='G' THEN B.INOUT_TYPE ELSE A.INOUT_TYPE END) as INOUT_TYPE,";
- 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 ";
- SQL = SQL + " FROM TB_MAP_PROCESS A ";
- SQL = SQL + " LEFT OUTER JOIN TB_MAP C ON (A.MAP_RECEIVER_ID=C.RECEIVER_ID AND A.MAP_NO=C.MAP_NO) ";
- SQL = SQL + " LEFT OUTER JOIN TB_GROUP_DEVICE B ON (A.RECEIVER_ID=B.GROUP_RECEIVER_ID AND A.GROUP_ID=B.GROUP_ID) ";
- SQL = SQL + " ORDER BY A.MAP_RECEIVER_ID,A.MAP_NO,A.MAP_TYPE";
- return SQL;
- }
- // 테이블 존재 여부
- public static string Table_Exist(string TABLE_NAME)
- {
- string SQL = "select rdb$relation_name ";
- SQL = SQL + "from rdb$relations ";
- SQL = SQL + "where rdb$system_flag=0 ";
- SQL = SQL + "and rdb$relation_name = '" + TABLE_NAME + "'";
- return SQL;
- }
- // 아날로그 감지기 용 테이블 생성
- public static string Create_TB_DAYTIME()
- {
- string SQL = "CREATE TABLE \"TB_DAYTIME\"";
- SQL = SQL + "(";
- SQL = SQL + "\"START_HOUR\" SMALLINT NOT NULL, ";
- SQL = SQL + "\"START_MIN\" SMALLINT NOT NULL, ";
- SQL = SQL + "\"END_HOUR\" SMALLINT NOT NULL, ";
- SQL = SQL + "\"END_MIN\" SMALLINT NOT NULL ";
- SQL = SQL + ");";
- return SQL;
- }
- public static string Create_TB_ANALOG_DEVICE_2()
- {
- string SQL = "CREATE TABLE \"TB_ANALOG_DEVICE_2\"";
- SQL = SQL + "(";
- SQL = SQL + "\"RECEIVER_ID\" INTEGER NOT NULL, ";
- SQL = SQL + "\"COMM_ID\" INTEGER NOT NULL, ";
- SQL = SQL + "\"BOARD_ID\" INTEGER NOT NULL, ";
- SQL = SQL + "\"LOOP_NO\" INTEGER NOT NULL, ";
- SQL = SQL + "\"REPEATER_ID\" INTEGER NOT NULL, ";
- SQL = SQL + "\"DEVICE_ID\" INTEGER NOT NULL, ";
- SQL = SQL + "\"INOUT_TYPE\" VARCHAR(1) NOT NULL, ";
- SQL = SQL + "\"FAULT_VALUE\" INTEGER NOT NULL, ";
- SQL = SQL + "\"PREFIRE_VALUE\" INTEGER NOT NULL, ";
- SQL = SQL + "\"FIRED_VALUE\" INTEGER NOT NULL, ";
- SQL = SQL + "\"FIREN_VALUE\" INTEGER NOT NULL, ";
- SQL = SQL + "\"UPDATE_USER_ID\" VARCHAR(10), ";
- SQL = SQL + "\"UPDATE_TIME\" TIMESTAMP, ";
- SQL = SQL + "CONSTRAINT \"PK_TB_ANALOG_DEVICE2\" PRIMARY KEY (\"RECEIVER_ID\", \"COMM_ID\", \"BOARD_ID\", \"LOOP_NO\", \"REPEATER_ID\", \"DEVICE_ID\", \"INOUT_TYPE\") ";
- SQL = SQL + ");";
- return SQL;
- }
- // cyim 2016.12.15 : 프로젝트를 열때 강제 쿼리 (수신반용 테이블)
- // 수신반용 테이블 생성
- public static string Create_TB_USERS()
- {
- string SQL = "CREATE TABLE \"TB_USERS\"";
- SQL = SQL + "(";
- SQL = SQL + "\"USER_NO\" INTEGER NOT NULL, ";
- SQL = SQL + "\"USER_NAME\" VARCHAR(50), ";
- SQL = SQL + "\"USER_ID\" VARCHAR(50), ";
- SQL = SQL + "\"USER_PW\" VARCHAR(50), ";
- SQL = SQL + "\"USER_COMPANY\" VARCHAR(50), ";
- SQL = SQL + "\"USER_POSITION\" VARCHAR(50), ";
- SQL = SQL + "\"USER_PHONE\" VARCHAR(50), ";
- SQL = SQL + "\"REG_TIME\" TIMESTAMP, ";
- SQL = SQL + "PRIMARY KEY (\"USER_NO\") ";
- SQL = SQL + ");";
- return SQL;
- }
- public static string Create_TB_PUSH_DEVICE()
- {
- string SQL = "CREATE TABLE \"TB_PUSH_DEVICE\"";
- SQL = SQL + "(";
- SQL = SQL + "\"UUID\" VARCHAR(16), ";
- SQL = SQL + "\"REG_ID\" VARCHAR(500), ";
- SQL = SQL + "\"REG_TIME\" TIMESTAMP ";
- SQL = SQL + ");";
- return SQL;
- }
- public static string Create_TB_RECEIVER_ERROR_COUNT()
- {
- string SQL = "CREATE TABLE \"TB_RECEIVER_ERROR_COUNT\"";
- SQL = SQL + "(";
- SQL = SQL + "\"IP_ADDRESS\" VARCHAR(15), ";
- SQL = SQL + "\"INFO1\" INTEGER, ";
- SQL = SQL + "\"INFO2\" INTEGER, ";
- SQL = SQL + "\"INFO3\" INTEGER, ";
- SQL = SQL + "\"INFO4\" INTEGER, ";
- SQL = SQL + "\"INFO5\" INTEGER, ";
- SQL = SQL + "\"INFO6\" INTEGER, ";
- SQL = SQL + "\"UPDATE_TIME\" TIMESTAMP ";
- SQL = SQL + ");";
- return SQL;
- }
- public static string Create_TB_RECEIVER_ERROR_COUNT_DETAILS()
- {
- string SQL = "CREATE TABLE \"TB_RECEIVER_ERROR_COUNT_DETAILS\"";
- SQL = SQL + "(";
- SQL = SQL + "\"IP_ADDRESS\" VARCHAR(15), ";
- SQL = SQL + "\"ERROR_TYPE\" VARCHAR(4), ";
- SQL = SQL + "\"LOCATION\" VARCHAR(40), ";
- SQL = SQL + "\"DEVICE_NAME\" VARCHAR(40), ";
- SQL = SQL + "\"EVENT_TYPE\" VARCHAR(40), ";
- SQL = SQL + "\"DEVICE_NUMBER\" VARCHAR(40), ";
- SQL = SQL + "\"EVENT_TIME\" TIMESTAMP ";
- SQL = SQL + ");";
- return SQL;
- }
- }
- }
|