12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127 |
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Text;
- using System.Data.SqlClient;
- namespace IControls_FireManager
- {
-
- public static class _Sql
- {
-
-
-
-
-
- public static string Get_ColumnFirstValue(string TableName, string Number, string 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;
- }
-
- 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 SearchAll_Table_Value(string TableName, string 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;
- }
-
-
- public static string SearchAll_Table_Value(string TableName, string Column, string 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;
- }
-
- public static string SearchAll_Table_Value(string TableName, string Column, string Value, string 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;
- }
-
- public static string SearchAll_Table_Value(string TableName, string Column1, string Value1, string Column2, string Value2, string 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;
- }
-
- public static string SearchAll_Table_Value_MAP_RECEIVER_ID(string TableName, string Column1, string Value1, string Column2, string Value2, string 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;
- }
-
- 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)
- {
-
-
- 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;
- }
-
- 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;
- }
-
- public static string Search_Table(string TableName, string Column1, string Value1, string Column2, string Value2, string 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;
- }
-
-
- 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)
- {
-
-
- 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;
- }
-
-
- 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)
- {
-
-
- 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;
- }
-
- 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)
- {
-
-
- 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;
- }
-
- 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)
- {
-
-
- 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;
- }
-
- public static string Search_Column_Table_Value(string TableName, string ColumnName)
- {
- string SQL = "SELECT " + TableName + "." + ColumnName + " " +
- "FROM " + TableName + ";";
- return SQL;
- }
-
- 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;
- }
-
- public static string Search_ConfigTable(int RECEIVER_ID)
- {
-
-
- string SQL = null;
- SQL += "SELECT * ";
- SQL += "FROM TB_CONFIG ";
- SQL += "WHERE RECEIVER_ID = " + RECEIVER_ID;
- return SQL;
- }
-
- public static string Search_RepeaterTable(string COMM_ID, string BOARD_ID, string LOOP_NO, string 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;
- }
-
-
- 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(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_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 ";
- 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)
- {
- 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 ";
-
- 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")
- {
- 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;
- }
-
- 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)
- {
- 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 ";
-
- 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 ";
- }
- 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;
- }
-
- public static string Search_Gruop_DeviceTable(string DEVICE_GROUP, string 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;
- }
-
- public static string Search_Map_ProcessTable(string MAP_NO, string MAP_TYPE, string 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)
- {
-
-
- 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)
- {
-
-
- 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, string iMODEL)
- {
-
-
- 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)
- {
-
-
- 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)
- {
-
-
- 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_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)
- {
-
-
- 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)
- {
-
-
- 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);
- return SQL;
- }
-
- public static string Delete_Table(string TableName, string 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;
- }
-
- public static string Delete_Table(string TableName, string Column, string Value, string 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;
- }
-
- public static string Delete_Table(string TableName, string Column1, string Value1, string Column2, string Value2, string 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;
- }
-
- public static string Delete_Table(string TableName, string Column1, string Value1, string Column2, string Value2, string Column3, string Value3, string 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;
- }
-
-
- 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)
- {
-
-
- 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)
- {
-
-
- 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;
- }
-
- 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 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)
- {
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- 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;
- }
-
-
- 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;
- }
- }
- }
|