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; } } }