123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959 |
- using System;
- using System.Collections.Generic;
- 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;
- }
- public static string DeleteEventLogDataTime(string StartTime, string EndTime)
- {
- String SQL = "DELETE FROM TB_EVENT_LOG ";
- SQL += " WHERE EVENT_TIME <= '{0}' and EVENT_TIME >= '{1}'";
- SQL = String.Format(SQL, EndTime, StartTime);
- return SQL;
- }
- // cyim 2015.2.13 로그는 2년 유지
- public static string DeleteCommandLogData(string StartTime, string EndTime)
- {
- String SQL = "DELETE FROM TB_COMMAND_LOG ";
- SQL += " WHERE COMMAND_TIME <= '{0}' and COMMAND_TIME >= '{1}'";
- SQL = String.Format(SQL, EndTime, StartTime);
- 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(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;
- if (RECEIVER_ID == null || RECEIVER_ID.Length == 0 || RECEIVER_ID == "0")
- {
- SQL += "SELECT * ";
- SQL += "FROM " + TableName + " ";
- 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
- {
- SQL += "SELECT * ";
- SQL += "FROM " + TableName + " ";
- 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 + "'";
- 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;
- }
- // 밑에 함수는 입력포인트의 속성이 화재이고, 비상방송이 매칭된것외에 전부 보여준다
- 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;
- }
- // 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;
- }
- public static string TB_RECEIVER_table_Insert(int iReceiverID, string sReceiverName, string sIP, int iEventPort, int iCommandPort, string sUseFlag)
- {
- // 적용테이블 :
- // "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,UPDATE_USER_ID,UPDATE_TIME)";
- SQL += " VALUES ({0},'{1}','{2}',{3},{4},'{5}','{6}','FireManager',current_timestamp)";
- SQL = String.Format(SQL, iReceiverID, sReceiverName, sIP, iEventPort, iCommandPort, "N", sUseFlag);
- 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;
- }
- 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;
- }
- // 맵연동파일 생성용 쿼리
- 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|00001|I|D|001|001|000|002|001|I|Y|
- 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.MAP_RECEIVER_ID=B.GROUP_RECEIVER_ID AND A.GROUP_ID=B.GROUP_ID) ";
- SQL = SQL + " WHERE A.MAP_RECEIVER_ID = " + RECEIVER_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;
- }
- }
- }
|