using System; using System.Collections.Generic; using System.Text; using FirebirdSql.Data.FirebirdClient; using System.Data; using System.Collections; using System.IO; using System.Data.Common; using System.Diagnostics; namespace FPER { /******************************************************************************************* * TB_RECEIVER(¼ö½Å±â) ¼³Á¤ °ü·Ã Dac *******************************************************************************************/ public class DacReceiverConfig : DacBase , IDisposable { public int ReceiverID = 0; public DacReceiverConfig(int ID) : base(ID)// cyim 2015.7.30 µ¥ÀÌŸº£À̽º Á¢¼Ó ·çƾ º¯°æ { //try { // base.Connect(); //} //catch (Exception ex) { // Util.UErrorMessage(ex, 0, 0); //} ReceiverID = ID; } ~DacReceiverConfig() { try { base.DisConnect(); } catch (Exception ex) { Util.UErrorMessage(ex, 0, 0); } } // cyim 2017.01.02 : Memory leak public void Dispose() { DisConnect(); } // // cyim 2016.04.06 : °¨Áö±âÀÇ ³·½Ã°£´ë ¼³Á¤ // // Å×À̺í Á¶È¸ public DataRowCollection TB_DAYTIME_Exist() { DataTable dt = null; try { String SQL = Table_Exist_SQL("TB_DAYTIME"); DataSet dao = Select(SQL); dt = dao.Tables[0]; return dt.Rows; } catch (Exception ex) { Util.UErrorMessage(ex, 0, 0); } return null; } // Å×À̺í Á¸Àç ¿©ºÎ public string Table_Exist_SQL(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 DataRowCollection TB_DAYTIME_Get() { DataTable dt = null; try { String SQL = "SELECT * FROM TB_DAYTIME"; DataSet dao = Select(SQL); dt = dao.Tables[0]; return dt.Rows; } catch (Exception ex) { Util.UErrorMessage(ex, 0, 0); } return null; } // Å×ÀÌºí ¾²±â public void TB_DAYTIME_Set(string Start_Hour,string Start_Minute, string End_Hour, string End_Minute) { try { String SQL = "insert into TB_DAYTIME (START_HOUR,START_MIN,END_HOUR,END_MIN) values " + string.Format("({0},{1},{2},{3})", Start_Hour, Start_Minute, End_Hour, End_Minute); ExecuteNonQuery(SQL); } catch (Exception ex) { Util.UErrorMessage(ex, 0, 0); } } // Å×ÀÌºí »èÁ¦ public void TB_DAYTIME_Init() { try { String SQL = "Delete from TB_DAYTIME"; ExecuteNonQuery(SQL); } catch (Exception ex) { Util.UErrorMessage(ex, 0, 0); } } // cyim 2013.8.7 Ÿ¼ö½Å±â È­Àç Á¤º¸ ÆË¾÷â Ãâ·Â (Ÿ¼ö½Å±â Á¤º¸¸¦ ¸ðµÎ °¡Á®¿Í¾ß µÈ´Ù) public DataTable Receiver_Select() { DataTable dt = null; try { String SQL = ""; SQL += "SELECT RECEIVER_ID,RECEIVER_NAME,IP_ADDRESS FROM TB_RECEIVER "; DataSet dao = Select(SQL); dt = dao.Tables[0]; } catch (Exception ex) { Util.UErrorMessage(ex, 0, 0); throw ex; } return dt; } // cyim 2017.01.24 : »ç¿ëÁßÀÎ ÆßÇÁ¸¸ Ç¥½ÃÇϵµ·Ï ÇÑ´Ù public DataTable Pump_Select(int num) // num 1 -> 9, 8 -> 16 { DataTable dt = null; try { DataSet dao = Select(string.Format("select * from TB_DEVICE where COMM_ID ='3' and DEVICE_ID='{0}'", 8 + num)); dt = dao.Tables[0]; } catch { ; } return dt; } public DataTable Receiver_Select(String RECEIVER_ID, String UseFlag) { DataTable dt = null; try { String SQL = ""; if (RECEIVER_ID == null) { SQL += "SELECT RECEIVER_ID,RECEIVER_NAME,IP_ADDRESS,EVENT_PORT_NO,COMMAND_PORT_NO"; SQL += ",REDUNDANT_FLAG,USE_FLAG FROM TB_RECEIVER "; if (UseFlag != null) SQL += " WHERE USE_FLAG = '" + UseFlag + "'"; SQL += " ORDER BY RECEIVER_ID"; } else { SQL += "SELECT RECEIVER_ID,RECEIVER_NAME,IP_ADDRESS,EVENT_PORT_NO,COMMAND_PORT_NO"; SQL += ",REDUNDANT_FLAG,USE_FLAG FROM TB_RECEIVER "; if (UseFlag != null) { SQL += " WHERE USE_FLAG = '" + UseFlag + "' AND RECEIVER_ID = '{0}'"; } else { SQL += " WHERE RECEIVER_ID = '{0}'"; } SQL = String.Format(SQL, RECEIVER_ID); } DataSet dao = Select(SQL); dt = dao.Tables[0]; } catch (Exception ex) { Util.UErrorMessage(ex, 0, 0); throw ex; } return dt; } public int ReceiverID_Max() { int ReceiverId = 0; try { DataSet dao = Select("SELECT MAX(RECEIVER_ID) FROM TB_RECEIVER"); DataTable dt = dao.Tables[0]; if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { if (!dr[0].ToString().Equals("")) ReceiverId = int.Parse(dr[0].ToString()); } } } catch (Exception ex) { Util.UErrorMessage(ex, 0, 0); throw ex; } return ++ReceiverId; } //public DataTable ReceiverUse_List() //{ // DataTable dt = null; // try // { // String SQL = ""; // SQL += "SELECT RECEIVER_ID,RECEIVER_NAME FROM TB_RECEIVER WHERE USE_FLAG = 'Y' ORDER BY RECEIVER_ID"; // DataSet dao = Select(SQL); // dt = dao.Tables[0]; // } // catch (Exception ex) // { // throw ex; // } // return dt; //} public void Receiver_Clear() { try { String SQL = "DELETE FROM TB_RECEIVER "; ExecuteNonQuery(SQL); } catch (Exception ex) { Util.UErrorMessage(ex, 0, 0); throw ex; } } public void Receiver_Update(QUERYMode cmd, String RECEIVER_ID, String RECEIVER_NAME, String IP_ADDRESS, String EVENT_PORT_NO, String COMMAND_PORT_NO, String REDUNDANT_FLAG, String USE_FLAG) { try { DataTable dt = Receiver_Select(RECEIVER_ID, null); if (cmd == QUERYMode.insert) { if (dt.Rows.Count > 0) { throw new Exception("ÀÌ¹Ì ÇØ´ç ÀÚ·á°¡ Á¸ÀçÇÕ´Ï´Ù. ¼öÁ¤¸ðµå·Î ÀÛ¾÷ÇÏ¿© Áֽʽÿä."); } String SQL = "INSERT INTO TB_RECEIVER (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}','FPER',current_timestamp)"; SQL = String.Format(SQL, RECEIVER_ID, RECEIVER_NAME, IP_ADDRESS, EVENT_PORT_NO, COMMAND_PORT_NO, REDUNDANT_FLAG, USE_FLAG); ExecuteNonQuery(SQL); //Åë½Åº¸µå ¹é·çÇÁ »ç¿ë¿©ºÎ¸¦ ¼öÁ¤ÇØ ÁØ´Ù.. DacCommConfig dacCommConfig = new DacCommConfig(ReceiverID);// cyim 2015.7.30 µ¥ÀÌŸº£À̽º Á¢¼Ó ·çƾ º¯°æ dacCommConfig.Comm_FlagUpdate(QUERYMode.InsertAndUpdate, int.Parse(RECEIVER_ID), COMMLOOP.BackLoop, REDUNDANT_FLAG); } else if (cmd == QUERYMode.update) { if (dt.Rows.Count < 1) { throw new Exception("ÇØ´ç ÀڷḦ ãÀ»¼ö ¾ø½À´Ï´Ù."); } String SQL = "UPDATE TB_RECEIVER SET RECEIVER_NAME = '{0}'"; SQL += ", IP_ADDRESS = '{1}', EVENT_PORT_NO = '{2}'"; SQL += ", COMMAND_PORT_NO = '{3}', REDUNDANT_FLAG = '{4}', USE_FLAG = '{5}'"; SQL += ", UPDATE_USER_ID = 'FPER', UPDATE_TIME = current_timestamp"; SQL += " WHERE RECEIVER_ID = {6}"; SQL = String.Format(SQL, RECEIVER_NAME, IP_ADDRESS, EVENT_PORT_NO, COMMAND_PORT_NO, REDUNDANT_FLAG, USE_FLAG, RECEIVER_ID); ExecuteNonQuery(SQL); //Åë½Åº¸µå ¹é·çÇÁ »ç¿ë¿©ºÎ¸¦ ¼öÁ¤ÇØ ÁØ´Ù.. DacCommConfig dacCommConfig = new DacCommConfig(ReceiverID);// cyim 2015.7.30 µ¥ÀÌŸº£À̽º Á¢¼Ó ·çƾ º¯°æ dacCommConfig.Comm_FlagUpdate(QUERYMode.InsertAndUpdate, int.Parse(RECEIVER_ID), COMMLOOP.BackLoop, REDUNDANT_FLAG); } else if (cmd == QUERYMode.delete) { if (dt.Rows.Count < 1) { throw new Exception("ÇØ´ç ÀڷḦ ãÀ»¼ö ¾ø½À´Ï´Ù."); } //if (int.Parse(RECEIVER_ID) == myReceiverID) //{ // throw new Exception("ÇØ´ç ¼ö½Å±â´Â »èÁ¦ÇÒ¼ö ¾ø½À´Ï´Ù."); //} String SQL = "UPDATE TB_RECEIVER SET USE_FLAG = 'N' "; SQL += ", UPDATE_USER_ID = 'FPER', UPDATE_TIME = current_timestamp"; SQL += " WHERE RECEIVER_ID = {0}"; //String SQL = "DELETE FROM TB_RECEIVER WHERE RECEIVER_ID = {0}"; SQL = String.Format(SQL, RECEIVER_ID); ExecuteNonQuery(SQL); } } catch (Exception ex) { Util.UErrorMessage(ex, 0, 0); throw ex; } } //ȯ°æ¼³Á¤Á¤º¸ Àоî¿È public DataTable Config_Select(int RECEIVER_ID) { DataTable dt = null; try { String SQL = ""; // cyim 2013.6.27 ¿îÀü¼³Á¤½Ã¿¡ ½Ã¿îÀü ¸ðµå üũ¹Ú½º Ãß°¡ ¹× ¸ÞÀÎ UI Ç¥½Ã (TEST_RUN_FLAG Ãß°¡ ¹× ±âº»°ªÀº N) SQL += "SELECT FIRE_ACCUMULATION_MODE,FIRE_ACCUMULATION_TIME,FIRE_PROCESS_MODE,SOUND_PROCESS_MODE"; // cyim 2013.6.27 ¿îÀü¼³Á¤½Ã¿¡ ½Ã¿îÀü ¸ðµå üũ¹Ú½º Ãß°¡ ¹× ¸ÞÀÎ UI Ç¥½Ã // cyim 2013.7.29 È­Àç½ÃÈ­¸éÀüȯ Á¤º¸ µ¥ÀÌŸº£À̽º ÀúÀå ¹× Àбâ // cyim 2014.8.11 È­¸é¿¡ ¸¶½ºÅÍÀÎÁö ½½·¹À̺êÀÎÁö Ç¥½Ã SQL += ",MASTER_USER_ID,MASTER_PASSWORD,BUILDING_NAME,BACKGROUND_IMAGE,TEST_RUN_FLAG,JUMP_FIELD,MASTER_FLAG ,RECEIVER_ID FROM TB_CONFIG ";// cyim 2015.7.31 TB_CONFIG Å×À̺í Á¤º¸¸¦ ÀÐÀ»¶§ ¼ö½Å±â ¾ÆÀ̵𵵠°°ÀÌ Á¶È¸ÇÏ°Ô ÇÑ´Ù // cyim 2015.7.31 ¼ö½Å¹ÝÀÇ °æ¿ì TB_CONFIG Å×À̺íÀ» ÀÐÀ»¶§¸¸ ¼ö½Å±â ¾ÆÀ̵𸦠¼±ÅÃÇÏÁö ¾Ê°Ô 0À¸·Î Á¶È¸ÇÑ´Ù if(RECEIVER_ID!=0) SQL += " WHERE RECEIVER_ID = '{0}'"; SQL = String.Format(SQL, RECEIVER_ID); DataSet dao = Select(SQL); dt = dao.Tables[0]; } catch (Exception ex) { //Debug.WriteLine(" Config Select Exception.. "); // Util.UErrorMessage(ex,0,0); throw ex; } return dt; } //ȯ°æÁ¤º¸ ÀúÀå public void Config_Update(QUERYMode cmd, int RECEIVER_ID, String FIRE_ACCUMULATION_MODE, int FIRE_ACCUMULATION_TIME , String FIRE_PROCESS_MODE, String SOUND_PROCESS_MODE, String MASTER_USER_ID, String MASTER_PASSWORD , String BUILDING_NAME, string BACKGROUND_IMAGE, string TEST_RUN_FLAG, string JUMP_FIELD) { try { DataTable dt = Config_Select(RECEIVER_ID); if (dt.Rows.Count == 0) { String 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"; // cyim 2013.6.27 ¿îÀü¼³Á¤½Ã¿¡ ½Ã¿îÀü ¸ðµå üũ¹Ú½º Ãß°¡ ¹× ¸ÞÀÎ UI Ç¥½Ã (TEST_RUN_FLAG Ãß°¡ ¹× ±âº»°ªÀº N) // cyim 2013.7.29 È­Àç½ÃÈ­¸éÀüȯ Á¤º¸ µ¥ÀÌŸº£À̽º ÀúÀå ¹× Àбâ SQL += ",BUILDING_NAME,BACKGROUND_IMAGE,UPDATE_USER_ID,TEST_RUN_FLAG,JUMP_FIELD UPDATE_TIME)"; SQL += " VALUES ({0},'{1}',{2},'{3}','{4}','{5}','{6}','{7}','{8}','FPER','{9}',current_timestamp,'{10}')"; SQL = String.Format(SQL, RECEIVER_ID, FIRE_ACCUMULATION_MODE, FIRE_ACCUMULATION_TIME , FIRE_PROCESS_MODE, SOUND_PROCESS_MODE, MASTER_USER_ID, MASTER_PASSWORD, BUILDING_NAME, BACKGROUND_IMAGE, TEST_RUN_FLAG, JUMP_FIELD); ExecuteNonQuery(SQL); //ExecuteNonQueryParam(SQL, BACKGROUND_IMAGE); } else { String SQL = "UPDATE TB_CONFIG SET FIRE_ACCUMULATION_MODE = '{0}',FIRE_ACCUMULATION_TIME={1},FIRE_PROCESS_MODE='{2}' "; SQL += ", SOUND_PROCESS_MODE = '{3}', MASTER_USER_ID = '{4}',MASTER_PASSWORD = '{5}'"; // cyim 2013.6.27 ¿îÀü¼³Á¤½Ã¿¡ ½Ã¿îÀü ¸ðµå üũ¹Ú½º Ãß°¡ ¹× ¸ÞÀÎ UI Ç¥½Ã // cyim 2013.7.29 È­Àç½ÃÈ­¸éÀüȯ Á¤º¸ µ¥ÀÌŸº£À̽º ÀúÀå ¹× Àбâ SQL += ", BUILDING_NAME = '{6}', BACKGROUND_IMAGE = '{7}' , TEST_RUN_FLAG = '{9}' , JUMP_FIELD = '{10}'"; SQL += ", UPDATE_USER_ID = 'FPER', UPDATE_TIME = current_timestamp"; SQL += " WHERE RECEIVER_ID = {8}"; SQL = String.Format(SQL, FIRE_ACCUMULATION_MODE, FIRE_ACCUMULATION_TIME, FIRE_PROCESS_MODE , SOUND_PROCESS_MODE, MASTER_USER_ID, MASTER_PASSWORD, BUILDING_NAME, BACKGROUND_IMAGE, RECEIVER_ID, TEST_RUN_FLAG, JUMP_FIELD); ExecuteNonQuery(SQL); // ExecuteNonQueryParam(SQL, BACKGROUND_IMAGE); } } catch (Exception ex) { Util.UErrorMessage(ex, 0, 0); throw ex; } } //ÀÔÃâ·Â ȸ·Î ŸÀÔ Á¶È¸ public DataTable Device_Type_Select(String SYMBOL_TYPE, String DEVICE_TYPE) { DataTable dt = null; try { String SQL = ""; SQL += "SELECT SYMBOL_TYPE,DEVICE_TYPE,DEVICE_TYPE_NAME,USE_FLAG "; SQL += " FROM TB_DEVICE_TYPE WHERE SYMBOL_TYPE = '{0}' AND DEVICE_TYPE = '{1}'"; SQL += " ORDER BY DEVICE_TYPE"; SQL = String.Format(SQL, SYMBOL_TYPE, DEVICE_TYPE); DataSet dao = Select(SQL); dt = dao.Tables[0]; } catch (Exception ex) { Util.UErrorMessage(ex, 0, 0); throw ex; } return dt; } //ÀÔÃâ·Â ȸ·Î ŸÀÔ Clear public void Device_Type_Clear(String SYMBOL_TYPE) { try { String SQL = "UPDATE TB_DEVICE_TYPE SET USE_FLAG = 'N' "; SQL += " ,UPDATE_USER_ID = 'FPER', UPDATE_TIME = current_timestamp"; SQL += " WHERE SYMBOL_TYPE = '{0}'"; SQL = String.Format(SQL, SYMBOL_TYPE); ExecuteNonQuery(SQL); } catch (Exception ex) { Util.UErrorMessage(ex, 0, 0); throw ex; } } //ÀÔÃâ·Â ȸ·Î ŸÀÔ ÀúÀå public void Device_Type_Update(QUERYMode cmd, String DEVICE_TYPE, String DEVICE_TYPE_NAME, String SYMBOL_TYPE , String USE_FLAG) { try { DataTable dt = Device_Type_Select(SYMBOL_TYPE, DEVICE_TYPE); if (dt.Rows.Count == 0) { String SQL = "INSERT INTO TB_DEVICE_TYPE (DEVICE_TYPE,DEVICE_TYPE_NAME"; SQL += ",SYMBOL_TYPE,USE_FLAG,UPDATE_USER_ID,UPDATE_TIME)"; SQL += " VALUES ('{0}','{1}','{2}','{3}','FPER',current_timestamp)"; SQL = String.Format(SQL, DEVICE_TYPE, DEVICE_TYPE_NAME, SYMBOL_TYPE, USE_FLAG); ExecuteNonQuery(SQL); } else { String SQL = "UPDATE TB_DEVICE_TYPE SET DEVICE_TYPE_NAME = '{0}',SYMBOL_TYPE='{1}',USE_FLAG='{2}' "; SQL += ", UPDATE_USER_ID = 'FPER', UPDATE_TIME = current_timestamp"; SQL += " WHERE DEVICE_TYPE = '{3}'"; SQL = String.Format(SQL, DEVICE_TYPE_NAME, SYMBOL_TYPE, USE_FLAG, DEVICE_TYPE); ExecuteNonQuery(SQL); } } catch (Exception ex) { Util.UErrorMessage(ex, 0, 0); throw ex; } } //// Àбâ //public DataTable SelectSql(string SQL) //{ // DataTable dt = null; // try // { // DataSet dao = Select(SQL); // dt = dao.Tables[0]; // } // catch (Exception ex) // { // Util.UErrorMessage(ex, 0, 0); // } // return dt; //} // ¾²±â public void ExecuteSql(string SQL) { if (this.Connect()) { FbTransaction transaction = connection.BeginTransaction(); try { FbCommand command = new FbCommand(SQL, connection, transaction); command.CommandType = CommandType.Text; command.CommandTimeout = 5; command.ExecuteNonQuery(); transaction.Commit(); command.Dispose(); } catch (Exception ex) { //Util.UErrorMessage(ex, 0, 0); transaction.Rollback(); throw ex; } finally { this.DisConnect(); } } } // //ȯ°æÅ×ÀÌºí¿¡ À̹ÌÁö ÀúÀå ////UPDATEBLOB table_name //// SET BLOB_Çʵå = :lb_data //// WHERE ID = :ls_code; ////--Àоî¿À±â-- ////SELECTBLOB BLOB_Çʵå //// INTO :lb_data //// FROM table_name //// WHERE ID = :ls_code; // public void ConfigImage_Update(QUERYMode cmd, int RECEIVER_ID, byte[] bytes) // { // FbTransaction transaction = connection.BeginTransaction(); // try // { // String SQL = "UPDATE TB_CONFIG SET BACKGROUND_IMAGE = ?"; // SQL += ", UPDATE_USER_ID = 'FPER', UPDATE_TIME = current_timestamp"; // SQL += string.Format(" WHERE RECEIVER_ID = {0}", RECEIVER_ID); // FbCommand command = new FbCommand(SQL, connection, transaction); // FbParameter param = command.CreateParameter(); // param.FbDbType = FbDbType.Binary; // param.SourceColumn = "BACKGROUND_IMAGE"; // param.Value = bytes; // command.CommandType = CommandType.Text; // command.Parameters.Add(param); // command.ExecuteNonQuery(); // transaction.Commit(); // command.Dispose(); // } // catch (Exception ex) // { // transaction.Rollback(); // throw ex; // } // } } /*******************************************************************************************/ }