1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357135813591360136113621363136413651366136713681369137013711372137313741375137613771378137913801381138213831384138513861387138813891390139113921393139413951396139713981399140014011402140314041405140614071408140914101411141214131414141514161417141814191420142114221423142414251426142714281429143014311432143314341435143614371438143914401441144214431444144514461447144814491450145114521453145414551456145714581459146014611462146314641465146614671468146914701471147214731474147514761477147814791480148114821483148414851486148714881489149014911492149314941495149614971498149915001501150215031504150515061507150815091510151115121513151415151516151715181519152015211522152315241525152615271528152915301531153215331534153515361537153815391540154115421543154415451546154715481549155015511552155315541555155615571558155915601561156215631564156515661567156815691570157115721573157415751576157715781579158015811582158315841585158615871588158915901591159215931594159515961597159815991600160116021603160416051606160716081609161016111612161316141615161616171618161916201621162216231624162516261627162816291630163116321633163416351636163716381639164016411642164316441645164616471648164916501651165216531654165516561657165816591660166116621663166416651666166716681669167016711672167316741675167616771678167916801681168216831684168516861687168816891690169116921693169416951696169716981699170017011702170317041705170617071708170917101711171217131714171517161717171817191720172117221723172417251726172717281729173017311732173317341735 |
- using System;
- using System.Collections.Generic;
- using System.Text;
- using FirebirdSql.Data.FirebirdClient;
- using System.Data;
- using System.Collections;
- using System.Diagnostics;
- namespace FPER
- {
- public class DacUIProcess : DacBase
- {
- static int iDACUiProcess = 0;
- public DacUIProcess(int ID)
- : base(ID)// cyim 2015.7.30 데이타베이스 접속 루틴 변경
- {
- //try {
- // base.Connect();
- //}
- //catch (Exception ex) {
- // Util.UErrorMessage(ex, 0, 0);
- //}
- iDACUiProcess++;
- // cyim_debug
- // Debug.WriteLine("<<<<<<< DacUIProcess 생성자 호출 <<<<<< " + Convert.ToString(iDACUiProcess));
- }
- ~DacUIProcess()
- {
- //try {
- // base.DisConnect();
- //}
- //catch (Exception ex) {
- // Util.UErrorMessage(ex, 0, 0);
- //}
- iDACUiProcess--;
- // cyim_debug
- // Debug.WriteLine("<<<<<<< DacUIProcess 소멸자 호출 <<<<<< " + Convert.ToString(iDACUiProcess));
- }
- //전체이벤트리스트
- public int Event_Log_RowCnt(int RECEIVER_ID)
- {
- int rowcnt = 0;
- try
- {
- String SQL = " SELECT COUNT(*) FROM TB_EVENT_LOG";
- DataSet dao = Select(SQL);
- DataTable dt = dao.Tables[0];
- if (dt.Rows.Count > 0)
- {
- DataRow dr = dt.Rows[0];
- rowcnt = Util.StrToInt(dr[0], 0);
- }
- SQL = " SELECT COUNT(*) FROM TB_COMMAND_LOG";
- DataSet dao2 = Select(SQL);
- DataTable dt2 = dao2.Tables[0];
- if (dt2.Rows.Count > 0)
- {
- DataRow dr2 = dt2.Rows[0];
- rowcnt += Util.StrToInt(dr2[0], 0);
- }
- }
- catch (Exception ex)
- {
- Util.UErrorMessage(ex, 0, 0);
- throw ex;
- }
- return rowcnt;
- }
- //전체이벤트리스트
- public int Event_Log_SelectAllCnt(int RECEIVER_ID, int page, int pageSize,
- String EVENT_TYPE, bool EVENTTIME_YES, DateTime FROM_DATE, DateTime TO_DATE,
- int COMM_ID, int BOARD_ID, int LOOP_NO, int REPEATER_ID, int DEVICE_ID, String INOUT_TYPE
- )
- {
- int rowcnt = 0;
- try
- {
- //String SQL = "";
- //SQL += " SELECT COUNT(*) ";
- //SQL += " FROM (";
- //SQL += " SELECT A.EVENT_ID,A.EVENT_TIME,A.EVENT_TYPE,A.RECEIVER_ID,A.COMM_ID,A.BOARD_ID";
- //SQL += " ,A.LOOP_NO,A.REPEATER_ID,A.DEVICE_ID,A.INOUT_TYPE,A.DATA_VALUE,A.CONFIRM_FLAG,A.CONFIRM_TIME";
- //SQL += " ,E.EVENT_TYPE_NAME,D.DEVICE_NAME,D.DEVICE_TYPE,D.CUT_FLAG,B.DEVICE_TYPE_NAME,C.POSITION_NAME";
- //SQL += " FROM TB_EVENT_LOG A";
- //SQL += " LEFT OUTER JOIN TB_EVENT_TYPE E ON A.EVENT_TYPE = E.EVENT_TYPE";
- //SQL += " LEFT OUTER JOIN TB_DEVICE D ON A.RECEIVER_ID=D.RECEIVER_ID AND A.COMM_ID=D.COMM_ID AND A.BOARD_ID=D.BOARD_ID";
- //SQL += " AND A.LOOP_NO=D.LOOP_NO AND A.REPEATER_ID=D.REPEATER_ID AND A.DEVICE_ID=D.DEVICE_ID AND A.INOUT_TYPE=D.INOUT_TYPE";
- //SQL += " LEFT OUTER JOIN TB_DEVICE_TYPE B ON D.DEVICE_TYPE = B.DEVICE_TYPE";
- //SQL += " LEFT OUTER JOIN TB_POSITION_CODE C ON D.POSITION_CODE = C.POSITION_CODE";
- int tick = Environment.TickCount;
- String SQL = "";// sdkim, 20110406, SQL optimisation
- SQL += " SELECT COUNT(*) ";
- SQL += " FROM (";
- SQL += " SELECT A.EVENT_ID,A.EVENT_TIME,A.EVENT_TYPE,A.RECEIVER_ID,A.COMM_ID,A.BOARD_ID";
- SQL += " ,A.LOOP_NO,A.REPEATER_ID,A.DEVICE_ID,A.INOUT_TYPE,A.DATA_VALUE";
- SQL += " FROM TB_EVENT_LOG A";
- SQL += string.Format(" WHERE A.RECEIVER_ID = {0}", RECEIVER_ID);
- if (EVENT_TYPE != null)
- {
- if (EVENT_TYPE.Length == 2) SQL += string.Format(" AND A.EVENT_TYPE = '{0}'", EVENT_TYPE);
- }
- if (EVENTTIME_YES)
- {
- String EVENT_TIME_FROM = String.Format("{0:yyyy}{0:MM}{0:dd}{0:HH}{0:mm}{0:ss}00000", FROM_DATE);
- String EVENT_TIME_TO = String.Format("{0:yyyy}{0:MM}{0:dd}{0:HH}{0:mm}{0:ss}99999", TO_DATE);
- SQL += string.Format(" AND A.EVENT_TIME >= '{0}' AND A.EVENT_TIME <= '{1}' ", EVENT_TIME_FROM, EVENT_TIME_TO);
- }
- if (COMM_ID > 0)
- {
- if (COMM_ID == 1) SQL += " AND (A.COMM_ID = 1 OR A.COMM_ID = 2)";
- else SQL += string.Format(" AND A.COMM_ID = {0} ", COMM_ID);
- if (INOUT_TYPE != null)
- {
- if (INOUT_TYPE != "")
- {
- if (COMM_ID == 1)
- {
- if (BOARD_ID > 0)
- {
- SQL += string.Format(" AND A.BOARD_ID = {0}", BOARD_ID);
- if (LOOP_NO == 0 || LOOP_NO == 1)
- {
- SQL += string.Format(" AND A.LOOP_NO = {0}", LOOP_NO);
- if (REPEATER_ID > 0)
- {
- SQL += string.Format(" AND A.REPEATER_ID = {0}", REPEATER_ID);
- if (DEVICE_ID > 0) SQL += string.Format(" AND A.DEVICE_ID = {0}", DEVICE_ID);
- }
- }
- }
- }
- else if (COMM_ID == 3)
- {
- if (BOARD_ID > 0)
- {
- SQL += string.Format(" AND A.BOARD_ID = {0}", BOARD_ID);
- if (DEVICE_ID > 0) SQL += string.Format(" AND A.DEVICE_ID = {0}", DEVICE_ID);
- }
- }
- else if (COMM_ID == 4)
- {
- if (DEVICE_ID > 0) SQL += string.Format(" AND A.DEVICE_ID = {0}", DEVICE_ID);
- }
- SQL += string.Format(" AND A.INOUT_TYPE = '{0}'", INOUT_TYPE);
- }
- }
- }
- // sdkim, 20110406, SQL optimisation
- //SQL += " UNION ALL";
- //SQL += " SELECT A.COMMAND_ID,A.COMMAND_TIME,A.COMMAND_TYPE,A.RECEIVER_ID,A.COMM_ID,A.BOARD_ID";
- //SQL += " ,A.LOOP_NO,A.REPEATER_ID,A.DEVICE_ID,A.INOUT_TYPE,0 AS DATA_VALUE,'Y' AS CONFIRM_FLAG,A.COMMAND_TIME AS CONFIRM_TIME";
- //SQL += " ,E.EVENT_TYPE_NAME,D.DEVICE_NAME,D.DEVICE_TYPE,D.CUT_FLAG,B.DEVICE_TYPE_NAME,C.POSITION_NAME";
- //SQL += " FROM TB_COMMAND_LOG A";
- //SQL += " LEFT OUTER JOIN TB_EVENT_TYPE E ON A.COMMAND_TYPE = E.EVENT_TYPE";
- //SQL += " LEFT OUTER JOIN TB_DEVICE D ON A.RECEIVER_ID=D.RECEIVER_ID AND A.COMM_ID=D.COMM_ID AND A.BOARD_ID=D.BOARD_ID";
- //SQL += " AND A.LOOP_NO=D.LOOP_NO AND A.REPEATER_ID=D.REPEATER_ID AND A.DEVICE_ID=D.DEVICE_ID AND A.INOUT_TYPE=D.INOUT_TYPE";
- //SQL += " LEFT OUTER JOIN TB_DEVICE_TYPE B ON D.DEVICE_TYPE = B.DEVICE_TYPE";
- //SQL += " LEFT OUTER JOIN TB_POSITION_CODE C ON D.POSITION_CODE = C.POSITION_CODE";
- //SQL += string.Format(" WHERE A.RECEIVER_ID = {0}", RECEIVER_ID);
- //if (EVENT_TYPE != null)
- //{
- // if (EVENT_TYPE.Length == 2) SQL += string.Format(" AND A.COMMAND_TYPE = '{0}'", EVENT_TYPE);
- //}
- //if (EVENTTIME_YES)
- //{
- // String EVENT_TIME_FROM = String.Format("{0:yyyy}{0:MM}{0:dd}{0:HH}{0:mm}{0:ss}00000", FROM_DATE);
- // String EVENT_TIME_TO = String.Format("{0:yyyy}{0:MM}{0:dd}{0:HH}{0:mm}{0:ss}99999", TO_DATE);
- // SQL += string.Format(" AND A.COMMAND_TIME >= '{0}' AND A.COMMAND_TIME <= '{1}' ", EVENT_TIME_FROM, EVENT_TIME_TO);
- //}
- //if (COMM_ID > 0)
- //{
- // if (COMM_ID == 1) SQL += " AND (A.COMM_ID = 1 OR A.COMM_ID = 2)";
- // else SQL += string.Format(" AND A.COMM_ID = {0} ", COMM_ID);
- // if (INOUT_TYPE != null)
- // {
- // if (INOUT_TYPE != "")
- // {
- // if (COMM_ID == 1)
- // {
- // if (BOARD_ID > 0)
- // {
- // SQL += string.Format(" AND A.BOARD_ID = {0}", BOARD_ID);
- // if (LOOP_NO == 0 || LOOP_NO == 1)
- // {
- // SQL += string.Format(" AND A.LOOP_NO = {0}", LOOP_NO);
- // if (REPEATER_ID > 0)
- // {
- // SQL += string.Format(" AND A.REPEATER_ID = {0}", REPEATER_ID);
- // if (DEVICE_ID > 0) SQL += string.Format(" AND A.DEVICE_ID = {0}", DEVICE_ID);
- // }
- // }
- // }
- // }
- // else if (COMM_ID == 3)
- // {
- // if (BOARD_ID > 0)
- // {
- // SQL += string.Format(" AND A.BOARD_ID = {0}", BOARD_ID);
- // if (DEVICE_ID > 0) SQL += string.Format(" AND A.DEVICE_ID = {0}", DEVICE_ID);
- // }
- // }
- // else if (COMM_ID == 4)
- // {
- // if (DEVICE_ID > 0) SQL += string.Format(" AND A.DEVICE_ID = {0}", DEVICE_ID);
- // }
- // SQL += string.Format(" AND A.INOUT_TYPE = '{0}'", INOUT_TYPE);
- // }
- // }
- //}
- SQL += " ) A";
- DataSet dao = Select(SQL);
- DataTable dt = dao.Tables[0];
- if (dt.Rows.Count > 0)
- {
- DataRow dr = dt.Rows[0];
- rowcnt = Util.StrToInt(dr[0], 0);
- }
- //Util.UDebugMessage(string.Format("[TEST] Event_Log_SelectAllCnt Proc Time = {0} rowcnt = {1}", Environment.TickCount - tick, rowcnt), 0, 0);
- }
- catch (Exception ex)
- {
- Util.UErrorMessage(ex, 0, 0);
- throw ex;
- }
- return rowcnt;
- }
- //전체이벤트리스트
- public DataTable Event_Log_SelectAll(int RECEIVER_ID, int page, int pageSize,
- String EVENT_TYPE, bool EVENTTIME_YES, DateTime FROM_DATE, DateTime TO_DATE,
- int COMM_ID, int BOARD_ID, int LOOP_NO, int REPEATER_ID, int DEVICE_ID, String INOUT_TYPE
- )
- {
- DataTable dt = null;
- try
- {
- //String SQL = "";
- //SQL += string.Format(" SELECT FIRST {0} SKIP {1} A.* ", pageSize, (page - 1) * pageSize);
- //SQL += " FROM (";
- //SQL += " SELECT A.EVENT_ID,A.EVENT_TIME,A.EVENT_TYPE,A.RECEIVER_ID,A.COMM_ID,A.BOARD_ID";
- //SQL += " ,A.LOOP_NO,A.REPEATER_ID,A.DEVICE_ID,A.INOUT_TYPE,A.DATA_VALUE,A.CONFIRM_FLAG,A.CONFIRM_TIME";
- //SQL += " ,E.EVENT_TYPE_NAME,D.DEVICE_NAME,D.DEVICE_TYPE,D.CUT_FLAG,B.DEVICE_TYPE_NAME,C.POSITION_NAME";
- //SQL += " FROM TB_EVENT_LOG A";
- //SQL += " LEFT OUTER JOIN TB_EVENT_TYPE E ON A.EVENT_TYPE = E.EVENT_TYPE";
- //SQL += " LEFT OUTER JOIN TB_DEVICE D ON A.RECEIVER_ID=D.RECEIVER_ID AND A.COMM_ID=D.COMM_ID AND A.BOARD_ID=D.BOARD_ID";
- //SQL += " AND A.LOOP_NO=D.LOOP_NO AND A.REPEATER_ID=D.REPEATER_ID AND A.DEVICE_ID=D.DEVICE_ID AND A.INOUT_TYPE=D.INOUT_TYPE";
- //SQL += " LEFT OUTER JOIN TB_DEVICE_TYPE B ON D.DEVICE_TYPE = B.DEVICE_TYPE";
- //SQL += " LEFT OUTER JOIN TB_POSITION_CODE C ON D.POSITION_CODE = C.POSITION_CODE";
- int tick = Environment.TickCount;
- String SQL = "";// sdkim, 20110406, SQL optimisation
- SQL += string.Format(" SELECT FIRST {0} SKIP {1} A.* ", pageSize, (page - 1) * pageSize);
- SQL += " FROM (";
- SQL += " SELECT A.EVENT_ID,A.EVENT_TIME,A.EVENT_TYPE,A.RECEIVER_ID,A.COMM_ID,A.BOARD_ID";
- SQL += " ,A.LOOP_NO,A.REPEATER_ID,A.DEVICE_ID,A.INOUT_TYPE,A.DATA_VALUE";
- SQL += " FROM TB_EVENT_LOG A";
- SQL += string.Format(" WHERE A.RECEIVER_ID = {0}", RECEIVER_ID);
- if (EVENT_TYPE != null)
- {
- if (EVENT_TYPE.Length == 2) SQL += string.Format(" AND A.EVENT_TYPE = '{0}'", EVENT_TYPE);
- }
- if (EVENTTIME_YES)
- {
- String EVENT_TIME_FROM = String.Format("{0:yyyy}{0:MM}{0:dd}{0:HH}{0:mm}{0:ss}00000", FROM_DATE);
- String EVENT_TIME_TO = String.Format("{0:yyyy}{0:MM}{0:dd}{0:HH}{0:mm}{0:ss}99999", TO_DATE);
- SQL += string.Format(" AND A.EVENT_TIME >= '{0}' AND A.EVENT_TIME <= '{1}' ", EVENT_TIME_FROM, EVENT_TIME_TO);
- }
- if (COMM_ID > 0)
- {
- if (COMM_ID == 1) SQL += " AND (A.COMM_ID = 1 OR A.COMM_ID = 2)";
- else SQL += string.Format(" AND A.COMM_ID = {0} ", COMM_ID);
- if (INOUT_TYPE != null)
- {
- if (INOUT_TYPE != "")
- {
- if (COMM_ID == 1)
- {
- if (BOARD_ID > 0)
- {
- SQL += string.Format(" AND A.BOARD_ID = {0}", BOARD_ID);
- if (LOOP_NO == 0 || LOOP_NO == 1)
- {
- SQL += string.Format(" AND A.LOOP_NO = {0}", LOOP_NO);
- if (REPEATER_ID > 0)
- {
- SQL += string.Format(" AND A.REPEATER_ID = {0}", REPEATER_ID);
- if (DEVICE_ID > 0) SQL += string.Format(" AND A.DEVICE_ID = {0}", DEVICE_ID);
- }
- }
- }
- }
- else if (COMM_ID == 3)
- {
- if (BOARD_ID > 0)
- {
- SQL += string.Format(" AND A.BOARD_ID = {0}", BOARD_ID);
- if (DEVICE_ID > 0) SQL += string.Format(" AND A.DEVICE_ID = {0}", DEVICE_ID);
- }
- }
- else if (COMM_ID == 4)
- {
- if (DEVICE_ID > 0) SQL += string.Format(" AND A.DEVICE_ID = {0}", DEVICE_ID);
- }
- SQL += string.Format(" AND A.INOUT_TYPE = '{0}'", INOUT_TYPE);
- }
- }
- }
- // sdkim, 20110406, SQL optimisation
- // SQL += " UNION ALL";
- // SQL += " SELECT A.COMMAND_ID,A.COMMAND_TIME,A.COMMAND_TYPE,A.RECEIVER_ID,A.COMM_ID,A.BOARD_ID";
- // SQL += " ,A.LOOP_NO,A.REPEATER_ID,A.DEVICE_ID,A.INOUT_TYPE,0 AS DATA_VALUE,'Y' AS CONFIRM_FLAG,A.COMMAND_TIME AS CONFIRM_TIME";
- //// SQL += " ,E.EVENT_TYPE_NAME,D.DEVICE_NAME,D.DEVICE_TYPE,D.CUT_FLAG,B.DEVICE_TYPE_NAME,C.POSITION_NAME";
- // SQL += " FROM TB_COMMAND_LOG A";
- //// SQL += " LEFT OUTER JOIN TB_EVENT_TYPE E ON A.COMMAND_TYPE = E.EVENT_TYPE";
- //// SQL += " LEFT OUTER JOIN TB_DEVICE D ON A.RECEIVER_ID=D.RECEIVER_ID AND A.COMM_ID=D.COMM_ID AND A.BOARD_ID=D.BOARD_ID";
- //// SQL += " AND A.LOOP_NO=D.LOOP_NO AND A.REPEATER_ID=D.REPEATER_ID AND A.DEVICE_ID=D.DEVICE_ID AND A.INOUT_TYPE=D.INOUT_TYPE";
- //// SQL += " LEFT OUTER JOIN TB_DEVICE_TYPE B ON D.DEVICE_TYPE = B.DEVICE_TYPE";
- //// SQL += " LEFT OUTER JOIN TB_POSITION_CODE C ON D.POSITION_CODE = C.POSITION_CODE";
- // SQL += string.Format(" WHERE A.RECEIVER_ID = {0}", RECEIVER_ID);
- // if (EVENT_TYPE != null)
- // {
- // if (EVENT_TYPE.Length == 2) SQL += string.Format(" AND A.COMMAND_TYPE = '{0}'", EVENT_TYPE);
- // }
- // if (EVENTTIME_YES)
- // {
- // String EVENT_TIME_FROM = String.Format("{0:yyyy}{0:MM}{0:dd}{0:HH}{0:mm}{0:ss}00000", FROM_DATE);
- // String EVENT_TIME_TO = String.Format("{0:yyyy}{0:MM}{0:dd}{0:HH}{0:mm}{0:ss}99999", TO_DATE);
- // SQL += string.Format(" AND A.COMMAND_TIME >= '{0}' AND A.COMMAND_TIME <= '{1}' ", EVENT_TIME_FROM, EVENT_TIME_TO);
- // }
- // if (COMM_ID > 0)
- // {
- // if (COMM_ID == 1) SQL += " AND (A.COMM_ID = 1 OR A.COMM_ID = 2)";
- // else SQL += string.Format(" AND A.COMM_ID = {0} ", COMM_ID);
- // if (INOUT_TYPE != null)
- // {
- // if (INOUT_TYPE != "")
- // {
- // if (COMM_ID == 1)
- // {
- // if (BOARD_ID > 0)
- // {
- // SQL += string.Format(" AND A.BOARD_ID = {0}", BOARD_ID);
- // if (LOOP_NO == 0 || LOOP_NO == 1)
- // {
- // SQL += string.Format(" AND A.LOOP_NO = {0}", LOOP_NO);
- // if (REPEATER_ID > 0)
- // {
- // SQL += string.Format(" AND A.REPEATER_ID = {0}", REPEATER_ID);
- // if (DEVICE_ID > 0) SQL += string.Format(" AND A.DEVICE_ID = {0}", DEVICE_ID);
- // }
- // }
- // }
- // }
- // else if (COMM_ID == 3)
- // {
- // if (BOARD_ID > 0)
- // {
- // SQL += string.Format(" AND A.BOARD_ID = {0}", BOARD_ID);
- // if (DEVICE_ID > 0) SQL += string.Format(" AND A.DEVICE_ID = {0}", DEVICE_ID);
- // }
- // }
- // else if (COMM_ID == 4)
- // {
- // if (DEVICE_ID > 0) SQL += string.Format(" AND A.DEVICE_ID = {0}", DEVICE_ID);
- // }
- // SQL += string.Format(" AND A.INOUT_TYPE = '{0}'", INOUT_TYPE);
- // }
- // }
- // }
- SQL += " ) A";
- SQL += " ORDER BY A.EVENT_TIME DESC";
- //SQL += " SELECT FIRST 1000 A.*,E.EVENT_TYPE_NAME,D.DEVICE_NAME,D.DEVICE_TYPE";
- //SQL += " ,D.CUT_FLAG,B.DEVICE_TYPE_NAME,C.POSITION_NAME";
- //SQL += " FROM TB_EVENT_LOG A";
- //SQL += " LEFT OUTER JOIN TB_EVENT_TYPE E ON A.EVENT_TYPE = E.EVENT_TYPE";
- //SQL += " LEFT OUTER JOIN TB_DEVICE D ON A.RECEIVER_ID=D.RECEIVER_ID AND A.COMM_ID=D.COMM_ID AND A.BOARD_ID=D.BOARD_ID";
- //SQL += " AND A.LOOP_NO=D.LOOP_NO AND A.REPEATER_ID=D.REPEATER_ID AND A.DEVICE_ID=D.DEVICE_ID AND A.INOUT_TYPE=D.INOUT_TYPE";
- //SQL += " LEFT OUTER JOIN TB_DEVICE_TYPE B ON D.DEVICE_TYPE = B.DEVICE_TYPE";
- //SQL += " LEFT OUTER JOIN TB_POSITION_CODE C ON D.POSITION_CODE = C.POSITION_CODE";
- //SQL += " WHERE A.RECEIVER_ID = {0} ";
- //SQL += " ORDER BY A.EVENT_TIME DESC";
- DataSet dao = Select(SQL);
- dt = dao.Tables[0];
- //Util.UDebugMessage(string.Format("[TEST] -Event_Log_SelectAll Proc Time = {0}", Environment.TickCount - tick), 0, 0);
- }
- catch (Exception ex)
- {
- Util.UErrorMessage(ex, 0, 0);
- throw ex;
- }
- return dt;
- }
- //이벤트ID로 검색 (사용안함)
- public DataTable Event_Log_Select(String EVENT_ID)
- {
- DataTable dt = null;
- try
- {
- //String SQL = "";
- //SQL += " SELECT A.*,D.DEVICE_NAME,D.DEVICE_TYPE";
- //SQL += " ,D.CUT_FLAG,B.DEVICE_TYPE_NAME,C.POSITION_NAME,E.EVENT_TYPE_NAME";
- //SQL += " FROM TB_EVENT_LOG A";
- //SQL += " LEFT OUTER JOIN TB_DEVICE D ON A.RECEIVER_ID=D.RECEIVER_ID AND A.COMM_ID=D.COMM_ID AND A.BOARD_ID=D.BOARD_ID";
- //SQL += " AND A.LOOP_NO=D.LOOP_NO AND A.REPEATER_ID=D.REPEATER_ID AND A.DEVICE_ID=D.DEVICE_ID AND A.INOUT_TYPE=D.INOUT_TYPE";
- //SQL += " LEFT OUTER JOIN TB_DEVICE_TYPE B ON D.DEVICE_TYPE = B.DEVICE_TYPE";
- //SQL += " LEFT OUTER JOIN TB_POSITION_CODE C ON D.POSITION_CODE = C.POSITION_CODE";
- //SQL += " LEFT OUTER JOIN TB_EVENT_TYPE E ON A.EVENT_TYPE = E.EVENT_TYPE";
- //SQL += " WHERE A.EVENT_ID = '{0}'";
- //SQL += " ORDER BY A.EVENT_TIME DESC";
- int tick = Environment.TickCount;
- String SQL = ""; // sdkim, 20110406, SQL optimisation with J.S.W
- SQL += " SELECT A.*, B.EVENT_TYPE_NAME";
- SQL += " FROM TB_EVENT_LOG A, TB_EVENT_TYPE B";
- SQL += " WHERE A.EVENT_ID = '{0}' and A.EVENT_TYPE = B.EVENT_TYPE";
- SQL += " ORDER BY A.EVENT_TIME DESC";
- SQL = String.Format(SQL, EVENT_ID);
- DataSet dao = Select(SQL);
- //
- dt = dao.Tables[0];
- //Util.UDebugMessage(string.Format("[TEST] -Event_Log_Select Proc Time = {0}", Environment.TickCount - tick), 0, 0);
- }
- catch (Exception ex)
- {
- Util.UErrorMessage(ex, 0, 0);
- throw ex;
- }
- return dt;
- }
- //회로 이벤트-확인받지 못한 이벤트
- public DataTable Event_Log_Select_NotConfirm(String EVENT_TYPE, int RECEIVER_ID, int COMM_ID, int BOARD_ID, int LOOP_NO,
- int REPEATER_ID, int DEVICE_ID, String INOUT_TYPE)
- {
- DataTable dt = null;
- try
- {
- String SQL = "";
- SQL += " SELECT A.EVENT_TYPE,A.EVENT_TIME,A.DEVICE_ID,D.DEVICE_NAME,D.DEVICE_TYPE,A.INOUT_TYPE";
- SQL += " ,D.CUT_FLAG,B.DEVICE_TYPE_NAME,C.POSITION_NAME";
- SQL += " ,A.DATA_VALUE,A.EVENT_REMARK";
- SQL += " FROM TB_EVENT_LOG A";
- SQL += " LEFT OUTER JOIN TB_DEVICE D ON A.RECEIVER_ID=D.RECEIVER_ID AND A.COMM_ID=D.COMM_ID AND A.BOARD_ID=D.BOARD_ID";
- SQL += " AND A.LOOP_NO=D.LOOP_NO AND A.REPEATER_ID=D.REPEATER_ID AND A.DEVICE_ID=D.DEVICE_ID AND A.INOUT_TYPE=D.INOUT_TYPE";
- SQL += " LEFT OUTER JOIN TB_DEVICE_TYPE B ON D.DEVICE_TYPE = B.DEVICE_TYPE";
- SQL += " LEFT OUTER JOIN TB_POSITION_CODE C ON D.POSITION_CODE = C.POSITION_CODE";
- SQL += " WHERE A.EVENT_TYPE = '{0}' AND A.RECEIVER_ID = {1} AND A.COMM_ID = {2} AND A.BOARD_ID = {3} AND A.LOOP_NO = {4} AND A.REPEATER_ID = {5}";
- SQL += " AND A.DEVICE_ID = {6} AND A.INOUT_TYPE = '{7}' ";
- SQL += " ORDER BY A.EVENT_TIME DESC";
- SQL = String.Format(SQL, EVENT_TYPE, RECEIVER_ID, COMM_ID, BOARD_ID, LOOP_NO, REPEATER_ID, DEVICE_ID, INOUT_TYPE);
- DataSet dao = Select(SQL);
- dt = dao.Tables[0];
- }
- catch (Exception ex)
- {
- Util.UErrorMessage(ex, 0, 0);
- throw ex;
- }
- return dt;
- }
- //회로 이벤트
- public DataTable Event_Log_Select(String EVENT_TYPE, int RECEIVER_ID, int COMM_ID, int BOARD_ID, int LOOP_NO,
- int REPEATER_ID, int DEVICE_ID, String INOUT_TYPE)
- {
- DataTable dt = null;
- try
- {
- String SQL = "";
- SQL += " SELECT A.EVENT_TYPE,A.EVENT_TIME,A.DEVICE_ID,D.DEVICE_NAME,D.DEVICE_TYPE,A.INOUT_TYPE";
- SQL += " ,D.CUT_FLAG,B.DEVICE_TYPE_NAME,C.POSITION_NAME";
- SQL += " ,A.DATA_VALUE,A.EVENT_REMARK";
- SQL += " FROM TB_EVENT_LOG A";
- SQL += " LEFT OUTER JOIN TB_DEVICE D ON A.RECEIVER_ID=D.RECEIVER_ID AND A.COMM_ID=D.COMM_ID AND A.BOARD_ID=D.BOARD_ID";
- SQL += " AND A.LOOP_NO=D.LOOP_NO AND A.REPEATER_ID=D.REPEATER_ID AND A.DEVICE_ID=D.DEVICE_ID AND A.INOUT_TYPE=D.INOUT_TYPE";
- SQL += " LEFT OUTER JOIN TB_DEVICE_TYPE B ON D.DEVICE_TYPE = B.DEVICE_TYPE";
- SQL += " LEFT OUTER JOIN TB_POSITION_CODE C ON D.POSITION_CODE = C.POSITION_CODE";
- SQL += " WHERE A.EVENT_TYPE = '{0}' AND A.RECEIVER_ID = {1} AND A.COMM_ID = {2} AND A.BOARD_ID = {3} AND A.LOOP_NO = {4} AND A.REPEATER_ID = {5}";
- SQL += " AND A.DEVICE_ID = {6} AND A.INOUT_TYPE = '{7}'";
- SQL += " ORDER BY A.EVENT_TIME DESC";
- SQL = String.Format(SQL, EVENT_TYPE, RECEIVER_ID, COMM_ID, BOARD_ID, LOOP_NO, REPEATER_ID, DEVICE_ID, INOUT_TYPE);
- DataSet dao = Select(SQL);
- dt = dao.Tables[0];
- }
- catch (Exception ex)
- {
- Util.UErrorMessage(ex, 0, 0);
- throw ex;
- }
- return dt;
- }
- //이벤트 확인저장
- public void Event_Log_UPDATE(QUERYMode cmd, String EVENT_ID, String CONFIRM_FLAG, String CONFIRM_REMARK)
- {
- try
- {
- DataTable dt = Event_Log_Select(EVENT_ID);
- if (cmd == QUERYMode.insert || cmd == QUERYMode.update || cmd == QUERYMode.InsertAndUpdate)
- {
- String SQL = "";
- if (dt.Rows.Count > 0)
- {
- SQL = "UPDATE TB_EVENT_LOG SET CONFIRM_FLAG = '{1}'";
- SQL += ", CONFIRM_REMARK = '{2}'";
- SQL += ", CONFIRM_PERSON = 'FPER', CONFIRM_TIME = current_timestamp";
- SQL += " WHERE EVENT_ID = '{0}'";
- SQL = String.Format(SQL, EVENT_ID, CONFIRM_FLAG, CONFIRM_REMARK);
- ExecuteNonQuery(SQL);
- }
- else
- {
- //SQL = "INSERT INTO TB_EVENT_LOG ( ";
- //SQL += " EVENT_TIME,EVENT_TYPE,RECEIVER_ID,COMM_ID,BOARD_ID,LOOP_NO,REPEATER_ID,DEVICE_ID,INOUT_TYPE,DATA_VALUE,EVENT_REMARK";
- //SQL += " ,CONFIRM_FLAG,CONFIRM_REMARK,CONFIRM_PERSON,CONFIRM_TIME";
- //SQL += ") VALUES ({0},1,{1},{2},{3},'{4}','{5}','{6}','N','FPER',current_timestamp)";
- //SQL = String.Format(SQL, RECEIVER_ID, BOARD_ID, LOOP_NO, REPEATER_ID, REPEATER_NAME, REPEATER_TYPE, USE_FLAG);
- }
- }
- else if (cmd == QUERYMode.delete)
- {
- if (dt.Rows.Count < 1)
- {
- throw new Exception("해당 자료를 찾을수 없습니다.");
- }
- String SQL = "DELETE FROM TB_EVENT_LOG ";
- SQL += " WHERE EVENT_ID = '{0}'";
- SQL = String.Format(SQL, EVENT_ID);
- ExecuteNonQuery(SQL);
- }
- }
- catch (Exception ex)
- {
- Util.UErrorMessage(ex, 0, 0);
- throw ex;
- }
- }
- //이벤트 로그 (사용안함)
- public string Event_TypeName_Select(String EVENT_TYPE)
- {
- string type_name = "";
- try
- {
- String SQL = "";
- SQL += " SELECT EVENT_TYPE,EVENT_TYPE_NAME";
- SQL += " FROM TB_EVENT_TYPE ";
- SQL += " WHERE EVENT_TYPE = '{0}' ";
- SQL = String.Format(SQL, EVENT_TYPE);
- DataSet dao = Select(SQL);
- DataTable dt = dao.Tables[0];
- if (dt.Rows.Count > 0)
- {
- DataRow dr = dt.Rows[0];
- type_name = dr["EVENT_TYPE_NAME"].ToString();
- }
- }
- catch (Exception ex)
- {
- Util.UErrorMessage(ex, 0, 0);
- throw ex;
- }
- return type_name;
- }
- //이벤트 타입리스트
- public DataTable Event_Type_Select(String EVENT_TYPE, MDIParent mdi) // cyim 2015.8.4 수신반을 위한 static 클래스 정리
- {
- DataTable dt = null;
- try
- {
- //String SQL = "";
- //if (EVENT_TYPE == null)
- //{
- // SQL += " SELECT EVENT_TYPE,EVENT_TYPE_NAME";
- // SQL += " FROM TB_EVENT_TYPE ";
- //}
- //else
- //{
- // SQL += " SELECT EVENT_TYPE,EVENT_TYPE_NAME";
- // SQL += " FROM TB_EVENT_TYPE ";
- // SQL += " WHERE EVENT_TYPE = '{0}' ";
- // SQL = String.Format(SQL, EVENT_TYPE);
- //}
- //DataSet dao = Select(SQL);
- //dt = dao.Tables[0];
- // cyim 2013.8.8 이벤트 타입명 해쉬테이블로 변환 (상단 모두 주석 처리)
- dt = mdi.EventTypeName.DataTable_EventType; // cyim 2015.8.4 수신반을 위한 static 클래스 정리
- }
- catch (Exception ex)
- {
- Util.UErrorMessage(ex, 0, 0);
- throw ex;
- }
- return dt;
- }
- public DataTable Event_Type_Table_Select(String EVENT_TYPE, MDIParent mdi) // cyim 2015.8.4 수신반을 위한 static 클래스 정리
- {
- // sdkim, 20110406, Event_Type Table Data Pre Load
- DataTable dt = null;
- try
- {
- //String SQL = "";
- //if (EVENT_TYPE == null)
- //{
- // SQL += " SELECT EVENT_TYPE,EVENT_TYPE_NAME";
- // SQL += " FROM TB_EVENT_TYPE ";
- //}
- //else
- //{
- // SQL += " SELECT EVENT_TYPE,EVENT_TYPE_NAME";
- // SQL += " FROM TB_EVENT_TYPE ";
- // SQL += " WHERE EVENT_TYPE = '{0}' ";
- // SQL = String.Format(SQL, EVENT_TYPE);
- //}
- //DataSet dao = Select(SQL);
- //dt = dao.Tables[0];
- // cyim 2013.8.8 이벤트 타입명 해쉬테이블로 변환 (상단 모두 주석 처리)
- dt = mdi.EventTypeName.DataTable_EventType; // cyim 2015.8.4 수신반을 위한 static 클래스 정리
- return dt;
- }
- catch (Exception ex)
- {
- Util.UErrorMessage(ex, 0, 0);
- return null;//throw ex;
- }
- }
- //command 조회
- public DataTable Command_Log_Select(String COMMAND_ID)
- {
- DataTable dt = null;
- try
- {
- String SQL = "";
- SQL += " SELECT A.*,D.DEVICE_NAME,D.DEVICE_TYPE";
- SQL += " ,D.CUT_FLAG,B.DEVICE_TYPE_NAME,C.POSITION_NAME";
- SQL += " FROM TB_COMMAND_LOG A";
- SQL += " LEFT OUTER JOIN TB_DEVICE D ON A.RECEIVER_ID=D.RECEIVER_ID AND A.COMM_ID=D.COMM_ID AND A.BOARD_ID=D.BOARD_ID";
- SQL += " AND A.LOOP_NO=D.LOOP_NO AND A.REPEATER_ID=D.REPEATER_ID AND A.DEVICE_ID=D.DEVICE_ID AND A.INOUT_TYPE=D.INOUT_TYPE";
- SQL += " LEFT OUTER JOIN TB_DEVICE_TYPE B ON D.DEVICE_TYPE = B.DEVICE_TYPE";
- SQL += " LEFT OUTER JOIN TB_POSITION_CODE C ON D.POSITION_CODE = C.POSITION_CODE";
- SQL += " WHERE A.COMMAND_ID = '{0}' ";
- SQL += " ORDER BY A.COMMAND_TIME DESC";
- SQL = String.Format(SQL, COMMAND_ID);
- DataSet dao = Select(SQL);
- dt = dao.Tables[0];
- }
- catch (Exception ex)
- {
- Util.UErrorMessage(ex, 0, 0);
- throw ex;
- }
- return dt;
- }
- //command 확인저장
- public void Command_Log_UPDATE(QUERYMode cmd, String COMMAND_ID, String COMMAND_TIME, String COMMAND_TYPE, int RECEIVER_ID
- , int COMM_ID, int BOARD_ID, int LOOP_NO, int REPEATER_ID, int DEVICE_ID, String INOUT_TYPE, String COMMAND_REMARK)
- {
- try
- {
- // cyim 2015.2.9 시작시 로그를 저장하는 경우 DB 부하가 걸림 제거
- // COMMAND_ID 는 날짜와시간을 조합한 아이디값이다. 중복될 확률은 미비하므로, 하위 루틴은 로그를 저장시에 부하가 걸릴 확률이 높다.
- //DataTable dt = Command_Log_Select(COMMAND_ID);
- if (cmd == QUERYMode.insert || cmd == QUERYMode.update || cmd == QUERYMode.InsertAndUpdate)
- {
- String SQL = "";
- // cyim 2015.2.9 시작시 로그를 저장하는 경우 DB 부하가 걸림 제거
- //if (dt.Rows.Count > 0)
- //{
- // //SQL = "UPDATE TB_COMMAND_LOG SET CONFIRM_FLAG = '{1}'";
- // //SQL += ", CONFIRM_REMARK = '{2}'";
- // //SQL += ", CONFIRM_PERSON = 'FPER', CONFIRM_TIME = current_timestamp";
- // //SQL += " WHERE COMMAND_ID = '{0}'";
- // //SQL = String.Format(SQL, COMMAND_ID, CONFIRM_REMARK);
- // //ExecuteNonQuery(SQL);
- //}
- //else
- {
- SQL = "INSERT INTO TB_COMMAND_LOG ( ";
- SQL += " COMMAND_ID,COMMAND_TIME,COMMAND_TYPE,RECEIVER_ID,COMM_ID,BOARD_ID,LOOP_NO,";
- SQL += " REPEATER_ID,DEVICE_ID,INOUT_TYPE,COMMAND_REMARK";
- SQL += ") VALUES ('{0}','{1}','{2}',{3},{4},{5},{6},{7},{8},'{9}','{10}')";
- SQL = String.Format(SQL,
- COMMAND_ID,
- COMMAND_TIME,
- COMMAND_TYPE,
- RECEIVER_ID,
- COMM_ID,
- BOARD_ID,
- LOOP_NO,
- REPEATER_ID,
- DEVICE_ID,
- INOUT_TYPE,
- COMMAND_REMARK);
- ExecuteNonQuery(SQL);
- }
- }
- else if (cmd == QUERYMode.delete)
- {
- // cyim 2015.2.9 시작시 로그를 저장하는 경우 DB 부하가 걸림 제거
- //if (dt.Rows.Count < 1)
- //{
- // throw new Exception("해당 자료를 찾을수 없습니다.");
- //}
- String SQL = "DELETE FROM TB_COMMAND_LOG ";
- SQL += " WHERE COMMAND_ID = '{0}'";
- SQL = String.Format(SQL, COMMAND_ID);
- ExecuteNonQuery(SQL);
- }
- }
- catch (Exception ex)
- {
- Util.UErrorMessage(ex, 0, 0);
- throw ex;
- }
- }
- public int GetEventLogCount(int receiverID)
- {
- int ret = -1;
- try
- {
- DataTable dt = null;
- String SQL = "";
- SQL += "SELECT COUNT (*)";
- SQL += " FROM TB_EVENT_LOG";
- SQL += " WHERE RECEIVER_ID = {0} ";
- SQL = String.Format(SQL, receiverID);
- DataSet dao = Select(SQL);
- dt = dao.Tables[0];
- if (dt.Rows.Count > 0)
- {
- DataRow dr = dt.Rows[0];
- }
- for (int i = 0; i < dt.Rows.Count; i++)
- {
- DataRow dr = dt.Rows[i];
- ret = Int32.Parse(dr["COUNT"].ToString());
- }
- }
- catch (Exception ex)
- {
- Util.UErrorMessage(ex, 0, 0);
- throw ex;
- }
- return ret;
- }
- public string[] GetEventLogData(int receiverID, int count)
- {
- string[] ret = new string[count];
- try
- {
- DataTable dt = null;
- String SQL = "";
- SQL += "SELECT FIRST {1} EVENT_ID ";
- SQL += " FROM TB_EVENT_LOG";
- SQL += " WHERE RECEIVER_ID = {0} ";
- SQL += " ORDER BY EVENT_TIME ";
- SQL = String.Format(SQL, receiverID, count);
- DataSet dao = Select(SQL);
- dt = dao.Tables[0];
- for (int i = 0; i < dt.Rows.Count; i++)
- {
- DataRow dr = dt.Rows[i];
- ret[i] = dr["EVENT_ID"].ToString();
- }
- }
- catch (Exception ex)
- {
- Util.UErrorMessage(ex, 0, 0);
- throw ex;
- }
- return ret;
- }
- public string GetEventLogTime(int receiverID, int count)
- {
- string ret = "";
- try
- {
- DataTable dt = null;
- String SQL = "";
- SQL += "SELECT FIRST 1 SKIP {1} EVENT_TIME ";
- SQL += " FROM TB_EVENT_LOG";
- SQL += " WHERE RECEIVER_ID = {0} ";
- SQL += " ORDER BY EVENT_TIME DESC";
- SQL = String.Format(SQL, receiverID, count);
- DataSet dao = Select(SQL);
- dt = dao.Tables[0];
- if (dt.Rows.Count > 0)
- {
- DataRow dr = dt.Rows[0];
- ret = dr["EVENT_TIME"].ToString();
- }
- }
- catch (Exception ex)
- {
- Util.UErrorMessage(ex, 0, 0);
- throw ex;
- }
- return ret;
- }
- public void AddEventLogData(EventLogInfo elog)
- {
- try
- {
- string event_time = String.Format("{0:yyyy}{0:MM}{0:dd}{0:HH}{0:mm}{0:ss}00000", elog.EventTime);
- string SQL = "";
- SQL = "INSERT INTO TB_EVENT_LOG ( ";
- SQL += " EVENT_ID,EVENT_TIME,EVENT_TYPE,RECEIVER_ID,COMM_ID,BOARD_ID,LOOP_NO,REPEATER_ID,DEVICE_ID,INOUT_TYPE,DATA_VALUE,SEND_FLAG";
- SQL += " ) VALUES ('{0}','{1}','{2}',{3},{4},{5},{6},{7},{8},'{9}',{10},'N')";
- SQL = String.Format(SQL, elog.EventId, event_time, elog.EventType, elog.ReceiverID, elog.CommId, elog.BoardNo, elog.LoopNo, elog.RepeaterNo, elog.DeviceId, elog.InoutType, elog.DataValue);
- ExecuteNonQuery(SQL);
- }
- catch (Exception ex)
- {
- Util.UErrorMessage(ex, 0, 0);
- throw ex;
- }
- }
- public void DeleteEventLogDataTime(string eventTime)
- {
- try
- {
- if (eventTime != "")
- {
- String SQL = "DELETE FROM TB_EVENT_LOG ";
- SQL += " WHERE EVENT_TIME < '{0}'";
- SQL = String.Format(SQL, eventTime);
- ExecuteNonQuery(SQL);
- }
- }
- catch (Exception ex)
- {
- Util.UErrorMessage(ex, 0, 0);
- throw ex;
- }
- }
- // cyim 2015.2.13 로그는 2년 유지
- public void DeleteCommandLogData(string COMMAND_TIME)
- {
- try
- {
- if (COMMAND_TIME != "")
- {
- String SQL = "DELETE FROM TB_COMMAND_LOG ";
- SQL += " WHERE COMMAND_TIME < '{0}'";
- SQL = String.Format(SQL, COMMAND_TIME);
- ExecuteNonQuery(SQL);
- }
- }
- catch (Exception ex)
- {
- Util.UErrorMessage(ex, 0, 0);
- throw ex;
- }
- }
- public void DeleteEventLogData(string eventID)
- {
- try
- {
- if (eventID != "")
- {
- String SQL = "DELETE FROM TB_EVENT_LOG ";
- SQL += " WHERE EVENT_ID = '{0}'";
- SQL = String.Format(SQL, eventID);
- ExecuteNonQuery(SQL);
- }
- }
- catch (Exception ex)
- {
- Util.UErrorMessage(ex, 0, 0);
- throw ex;
- }
- }
- public void DeleteEventLogData(string[] eventID)
- {
- try
- {
- if (eventID.Length > 0)
- {
- String SQL = "DELETE FROM TB_EVENT_LOG ";
- bool first = true;
- foreach (string eID in eventID)
- {
- if (first)
- {
- if (eID != "")
- {
- SQL += string.Format(" WHERE EVENT_ID = '{0}'", eID);
- first = false;
- }
- }
- else
- {
- if (eID != "")
- {
- SQL += string.Format(" AND EVENT_ID = '{0}'", eID);
- }
- }
- }
- ExecuteNonQuery(SQL);
- }
- }
- catch (Exception ex)
- {
- Util.UErrorMessage(ex, 0, 0);
- throw ex;
- }
- }
- //회로조회
- public DataTable Device_Select(int RECEIVER_ID, int COMM_ID, int BOARD_ID, int LOOP_NO, int REPEATER_ID, int DEVICE_ID, String INOUT_TYPE)
- {
- DataTable dt = null;
- try
- {
- String SQL = "";
- SQL += " SELECT A.DEVICE_ID,A.DEVICE_NAME,A.DEVICE_TYPE,A.INOUT_TYPE,A.CUT_FLAG,B.DEVICE_TYPE_NAME,A.POSITION_CODE,C.POSITION_NAME";
- SQL += " FROM TB_DEVICE A ";
- SQL += " LEFT OUTER JOIN TB_DEVICE_TYPE B ON A.DEVICE_TYPE = B.DEVICE_TYPE";
- SQL += " LEFT OUTER JOIN TB_POSITION_CODE C ON A.POSITION_CODE = C.POSITION_CODE";
- SQL += " WHERE A.RECEIVER_ID = {0} AND A.COMM_ID = {1} AND A.BOARD_ID = {2} AND A.LOOP_NO = {3} AND A.REPEATER_ID = {4}";
- SQL += " AND A.DEVICE_ID = {5} AND A.INOUT_TYPE = '{6}'";
- SQL = String.Format(SQL, RECEIVER_ID, COMM_ID, BOARD_ID, LOOP_NO, REPEATER_ID, DEVICE_ID, INOUT_TYPE);
- DataSet dao = Select(SQL);
- dt = dao.Tables[0];
- }
- catch (Exception ex)
- {
- Util.UErrorMessage(ex, 0, 0);
- throw ex;
- }
- return dt;
- }
- public DataTable SelectLastLog(int receviceID, int commID, int boardID, int loopNO, int repeaterID, int deviceID, string inoutType, string eventtype)
- {
- DataTable dt = null;
- try
- {
- String SQL = "";
- SQL += "SELECT FIRST 1 EVENT_ID,EVENT_TIME,EVENT_TYPE,RECEIVER_ID,COMM_ID,BOARD_ID,LOOP_NO,REPEATER_ID,DEVICE_ID,INOUT_TYPE,DATA_VALUE,EVENT_REMARK,SEND_FLAG";
- SQL += " FROM TB_EVENT_LOG";
- SQL += " WHERE EVENT_TYPE = '{0}' AND RECEIVER_ID = {1} AND COMM_ID = {2} AND BOARD_ID = {3} AND LOOP_NO = {4} AND REPEATER_ID = {5} AND DEVICE_ID = {6} AND INOUT_TYPE = '{7}'";
- SQL += " ORDER BY EVENT_TIME DESC";
- SQL = String.Format(SQL, eventtype, receviceID, commID, boardID, loopNO, repeaterID, deviceID, inoutType);
- DataSet dao = Select(SQL);
- dt = dao.Tables[0];
- }
- catch (Exception ex)
- {
- Util.UErrorMessage(ex, 0, 0);
- throw ex;
- }
- return dt;
- }
- public DataTable SelectLogEvnetID(string EventID)
- {
- DataTable dt = null;
- try
- {
- String SQL = "";
- SQL += "SELECT EVENT_ID,EVENT_TIME,EVENT_TYPE,RECEIVER_ID,COMM_ID,BOARD_ID,LOOP_NO,REPEATER_ID,DEVICE_ID,INOUT_TYPE,DATA_VALUE,EVENT_REMARK,SEND_FLAG";
- SQL += " FROM TB_EVENT_LOG";
- SQL += " WHERE EVENT_ID = '{0}' ";
- SQL = String.Format(SQL, EventID);
- DataSet dao = Select(SQL);
- dt = dao.Tables[0];
- }
- catch (Exception ex)
- {
- Util.UErrorMessage(ex, 0, 0);
- throw ex;
- }
- return dt;
- }
- public void AddEventLogData(string EVENT_ID, string EVENT_TIME, string EVENT_TYPE, int RECEIVER_ID, int COMM_ID, int BOARD_ID, int LOOP_NO, int REPEATER_ID, int DEVICE_ID, string INOUT_TYPE, int DATA_VALUE, string EVENT_REMARK, string SEND_FLAG)
- {
- try
- {
- string SQL = "";
- SQL = "INSERT INTO TB_EVENT_LOG ( ";
- SQL += " EVENT_ID,EVENT_TIME,EVENT_TYPE,RECEIVER_ID,COMM_ID,BOARD_ID,LOOP_NO,REPEATER_ID,DEVICE_ID,INOUT_TYPE,DATA_VALUE,EVENT_REMARK,SEND_FLAG";
- SQL += " ) VALUES ('{0}','{1}','{2}',{3},{4},{5},{6},{7},{8},'{9}',{10},'{11}','{12}')";
- SQL = String.Format(SQL, EVENT_ID, EVENT_TIME, EVENT_TYPE, RECEIVER_ID, COMM_ID, BOARD_ID, LOOP_NO, REPEATER_ID, DEVICE_ID, INOUT_TYPE, DATA_VALUE, EVENT_REMARK, SEND_FLAG);
- 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);
- throw ex;
- }
- return dt;
- }
- }
- public class LogType
- {
- public bool cnt = false;
- public bool time = false;
- public DateTime startTime;
- public DateTime endTime;
- public bool eventTypeDetail = false;
- public string eventTypeD = "";
- public bool fireOn = false;
- //public bool fireOff = false;
- public bool devOn = false;
- //public bool devOff = false;
- //public bool errorOn = false;
- //public bool errorOff = false;
- public bool controlLog = false;
- public bool boardError = false;
- public bool repeaterError = false;
- public bool devError = false;
- public int startIndex = 0;
- public int endIndex = 0;
- public bool location = false;
- private string SqlBase()
- {
- string sql = "";
- sql += "EVENT_ID,EVENT_TIME,EVENT_TYPE,RECEIVER_ID,COMM_ID,BOARD_ID,LOOP_NO,REPEATER_ID,DEVICE_ID,INOUT_TYPE,DATA_VALUE,EVENT_REMARK ";
- sql += "FROM TB_EVENT_LOG ";
- sql += "WHERE RECEIVER_ID > 0 ";
- return sql;
- }
- private string SqlTime()
- {
- string sql = "";
- if (time)
- {
- String strStartTime = String.Format("{0:yyyy}{0:MM}{0:dd}{0:HH}{0:mm}{0:ss}00000", startTime);
- String strEndTime = String.Format("{0:yyyy}{0:MM}{0:dd}{0:HH}{0:mm}{0:ss}99999", endTime);
- sql += string.Format(" AND EVENT_TIME >= '{0}' AND EVENT_TIME <= '{1}' ", strStartTime, strEndTime);
- }
- return sql;
- }
- // cyim 2013.10.10 : 1000건 조회 속도 개선 작업
- //private string COMMANDSqlTime(DateTime StartTime, DateTime EndTime)
- //{
- // string sql = "";
- // if (time)
- // {
- // String strStartTime = String.Format("{0:yyyy}{0:MM}{0:dd}{0:HH}{0:mm}{0:ss}00000", StartTime);
- // String strEndTime = String.Format("{0:yyyy}{0:MM}{0:dd}{0:HH}{0:mm}{0:ss}99999", EndTime);
- // sql += string.Format(" AND COMMAND_TIME >= '{0}' AND COMMAND_TIME <= '{1}' ", strStartTime, strEndTime);
- // }
- // return sql;
- //}
- private string SqlTime(DateTime StartTime, DateTime EndTime)
- {
- string sql = "";
- if (time)
- {
- String strStartTime = String.Format("{0:yyyy}{0:MM}{0:dd}{0:HH}{0:mm}{0:ss}00000", StartTime);
- String strEndTime = String.Format("{0:yyyy}{0:MM}{0:dd}{0:HH}{0:mm}{0:ss}99999", EndTime);
- sql += string.Format(" AND EVENT_TIME >= '{0}' AND EVENT_TIME <= '{1}' ", strStartTime, strEndTime);
- }
- return sql;
- }
- private string SqlCount()
- {
- string sql = "";
- sql += "COUNT(*) ";
- sql += "FROM TB_EVENT_LOG ";
- sql += "WHERE RECEIVER_ID > 0 ";
- return sql;
- }
- private string SqlIndex()
- {
- string sql = "";
- if (endIndex != startIndex)
- {
- sql += string.Format("FIRST {0} ", endIndex, startIndex);
- }
- return sql;
- }
- public int receiverID = -1;
- public int commID = -1; // 1,2 통신보드, , 3 IO 보드 4 키패드
- public int boardID = -1;// 0 : 키패드, 1~16 IO보드, 통신보드
- public int loopNo = -1; // 0, 1
- public int repeaterID = -1; // 1~127
- public int deviceID = -1; // 1~4
- public string ioType = "A"; // A: 전체, C 통신(수신기) , B 보드, L 루프, R 중계기, D 회로, I 입력 , O 출력 , E:계통 레벨 이외 전부
- private string SqlLocation()
- {
- string sql = "";
- if (this.location)
- {
- if (receiverID > 0)
- {
- sql += string.Format("AND RECEIVER_ID = {0} ", receiverID);
- }
- if (commID > 0)
- {
- sql += string.Format("AND COMM_ID = {0} ", commID);
- }
- if (boardID > 0)
- {
- sql += string.Format("AND BOARD_ID = {0} ", boardID);
- }
- if (loopNo > -1)
- {
- sql += string.Format("AND LOOP_NO = {0} ", loopNo);
- }
- if (repeaterID > 0)
- {
- sql += string.Format("AND REPEATER_ID = {0} ", repeaterID);
- }
- if (deviceID > 0)
- {
- sql += string.Format("AND DEVICE_ID = {0} ", deviceID);
- }
- if (ioType != "A")
- {
- switch (ioType)
- {
- case "L":
- sql += string.Format("AND ( INOUT_TYPE = 'B' OR INOUT_TYPE = 'L') ");
- break;
- case "D":
- sql += string.Format("AND ( INOUT_TYPE = 'I' OR INOUT_TYPE = 'O') ");
- break;
- case "E":
- sql += string.Format("AND COMM_ID != 1 ");
- break;
- default:
- sql += string.Format("AND INOUT_TYPE = '{0}' ", ioType);
- break;
- }
- }
- }
- return sql;
- }
- private string SqlEventTypeDetail()
- {
- string sql = "";
- sql += string.Format("AND EVENT_TYPE = '{0}' ", eventTypeD);
- return sql;
- }
- //private string SqlCOMMANDTypeDetail()
- //{
- // string sql = "";
- // sql += string.Format("AND COMMAND_TYPE = '{0}' ", eventTypeD);
- // return sql;
- //}
- private string SqlEventType()
- {
- string sql = "";
- if (fireOn && devOn && controlLog && boardError && repeaterError && devError)
- {
- }
- else if ((fireOn || devOn || controlLog || boardError || repeaterError || devError) == false)
- {
- }
- else
- {
- if (fireOn)
- {
- sql += "AND ( EVENT_TYPE = 'OA' "; //화재축적상태 발생
- sql += "OR EVENT_TYPE = 'OF' ";//화재상태 발생
- sql += "OR EVENT_TYPE = 'OT' "; //발신기설정
- sql += "OR EVENT_TYPE = 'XA' "; //화재축적상태 해제
- sql += "OR EVENT_TYPE = 'XF' ";//화재상태 해제
- sql += "OR EVENT_TYPE = 'XT' ";//발신기해제
- }
- if (devOn)
- {
- if (fireOn/* || fireOff*/)
- {
- sql += "OR ";
- }
- else
- {
- sql += "AND ( ";
- }
- sql += "EVENT_TYPE = 'OE' "; //설비작동상태 발생
- sql += "OR EVENT_TYPE = 'XE' "; //설비작동상태 해제
- sql += "OR EVENT_TYPE = 'EW' "; //설비출력상태 ON
- sql += "OR EVENT_TYPE = 'EF' "; //설비출력상태 OFF
- }
- if (controlLog)
- {
- if (fireOn /*|| fireOff*/ || devOn /*|| devOff || errorOn || errorOff*/)
- {
- sql += "OR ";
- }
- else
- {
- sql += "AND ( ";
- }
- sql += "EVENT_TYPE = 'OP' ";//전화설정
- sql += "OR EVENT_TYPE = 'XP' ";//전화해제
- sql += "OR EVENT_TYPE = 'SP' ";//화면좌표설정버튼
- sql += "OR EVENT_TYPE = 'MS' ";//주음향정지버튼
- sql += "OR EVENT_TYPE = 'LS' ";//지구음향정지버튼
- sql += "OR EVENT_TYPE = 'EB' ";//비상방송정지버튼
- sql += "OR EVENT_TYPE = 'RP' ";//예비전원시험버튼
- sql += "OR EVENT_TYPE = 'ED' ";//설비연동조작버튼 // cyim 2016.12.09 : IFC3300에서는 예비전원버튼 -> 설비전체연동조작버튼
- sql += "OR EVENT_TYPE = 'FR' ";//화재복구버튼
- sql += "OR EVENT_TYPE = 'EC' ";//설비연동화면버튼
- sql += "OR EVENT_TYPE = 'PO' ";//통신(POLLING) 시작
- sql += "OR EVENT_TYPE = 'PF' ";//통신(POLLING) 종료
- sql += "OR EVENT_TYPE = 'CC' ";//통신상태읽기
- sql += "OR EVENT_TYPE = 'CV' ";//전압상태읽기
- sql += "OR EVENT_TYPE = 'V2' ";//24 전압 변경
- sql += "OR EVENT_TYPE = 'DC' ";//설비작동차단
- sql += "OR EVENT_TYPE = 'CS' ";//환경설정
- sql += "OR EVENT_TYPE = 'TK' ";//키패드테스트
- sql += "OR EVENT_TYPE = 'VI' ";//통신프로그램버전정보
- sql += "OR EVENT_TYPE = 'BF' ";//부저정지
- sql += "OR EVENT_TYPE = 'EL' ";//이벤트리스트
- sql += "OR EVENT_TYPE = 'BS' ";//보드상태
- sql += "OR EVENT_TYPE = 'RA' ";//화재축적상태 재설정
- sql += "OR EVENT_TYPE = 'OU' ";//UI프로그램시작
- sql += "OR EVENT_TYPE = 'XU' ";//UI프로그램종료
- sql += "OR EVENT_TYPE = 'DS' ";//설비작동상태조회
- }
- if (boardError)
- {
- if (fireOn || devOn || controlLog)
- {
- sql += "OR ";
- }
- else
- {
- sql += "AND ( ";
- }
- sql += "EVENT_TYPE = 'XM' "; //통신모듈 통신이상
- sql += "OR EVENT_TYPE = 'XI' ";//I/O 모듈 통신이상
- sql += "OR EVENT_TYPE = 'XK' ";//키패드모듈 통신이상
- sql += "OR EVENT_TYPE = 'AV' ";//출력전압 이상
- sql += "OR EVENT_TYPE = 'AR' ";//예비전원 이상
- sql += "OR EVENT_TYPE = 'AA' ";//교류전원 이상
- sql += "OR EVENT_TYPE = 'OD' ";//데몬연결이상
- sql += "OR EVENT_TYPE = 'OB' ";//DB연결이상
- sql += "OR EVENT_TYPE = 'BA' ";//통신보드루프교류전원이상
- sql += "OR EVENT_TYPE = 'BR' ";//통신보드루프예비전원이상
- sql += "OR EVENT_TYPE = 'XC' ";//비상방송 통신이상
- sql += "OR EVENT_TYPE = 'OM' ";//통신모듈 통신정상
- sql += "OR EVENT_TYPE = 'OI' ";//I/O 모듈 통신정상
- sql += "OR EVENT_TYPE = 'OK' ";//키패드모듈 통신정상
- sql += "OR EVENT_TYPE = 'NV' ";//출력전압 정상
- sql += "OR EVENT_TYPE = 'NR' ";//예비전원 정상
- sql += "OR EVENT_TYPE = 'NA' ";//교류전원 정상
- sql += "OR EVENT_TYPE = 'XD' ";//데몬연결정상
- sql += "OR EVENT_TYPE = 'XB' ";//DB연결정상
- sql += "OR EVENT_TYPE = 'BN' ";//통신보드루프정상
- sql += "OR EVENT_TYPE = 'OC' ";//비상방송 통신정상
- }
- if (repeaterError)
- {
- if (fireOn || devOn || controlLog || boardError)
- {
- sql += "OR ";
- }
- else
- {
- sql += "AND ( ";
- }
- sql += " EVENT_TYPE = 'XR' ";//중계기 통신이상
- sql += "OR EVENT_TYPE = 'OR' ";//중계기 통신정상
- sql += "OR EVENT_TYPE = 'OX' ";//중계기전원이상
- sql += "OR EVENT_TYPE = 'XX' ";//중계기전원이상 해제
- }
- if (devError)
- {
- if (fireOn || devOn || controlLog || boardError || repeaterError)
- {
- sql += "OR ";
- }
- else
- {
- sql += "AND ( ";
- }
- sql += " EVENT_TYPE = 'OL' ";//중계기 단선발생
- sql += "OR EVENT_TYPE = 'XL' ";//중계기 단선복구
- }
- sql += " ) ";
- }
- return sql;
- }
- //private string SqlCOMMANDType()
- //{
- // string sql = "";
- // if (fireOn && devOn && controlLog && boardError && repeaterError && devError)
- // {
- // }
- // else if ((fireOn || devOn || controlLog || boardError || repeaterError || devError) == false)
- // {
- // }
- // else
- // {
- // if (fireOn)
- // {
- // sql += "AND ( COMMAND_TYPE = 'OA' "; //화재축적상태 발생
- // sql += "OR EVENT_TYPE = 'OF' ";//화재상태 발생
- // sql += "OR EVENT_TYPE = 'OT' "; //발신기설정
- // sql += "OR EVENT_TYPE = 'XA' "; //화재축적상태 해제
- // sql += "OR EVENT_TYPE = 'XF' ";//화재상태 해제
- // sql += "OR EVENT_TYPE = 'XT' ";//발신기해제
- // }
- // if (devOn)
- // {
- // if (fireOn/* || fireOff*/)
- // {
- // sql += "OR ";
- // }
- // else
- // {
- // sql += "AND ( ";
- // }
- // sql += "EVENT_TYPE = 'OE' "; //설비작동상태 발생
- // sql += "OR EVENT_TYPE = 'XE' "; //설비작동상태 해제
- // sql += "OR EVENT_TYPE = 'EW' "; //설비출력상태 ON
- // sql += "OR EVENT_TYPE = 'EF' "; //설비출력상태 OFF
- // }
- // if (controlLog)
- // {
- // if (fireOn /*|| fireOff*/ || devOn /*|| devOff || errorOn || errorOff*/)
- // {
- // sql += "OR ";
- // }
- // else
- // {
- // sql += "AND ( ";
- // }
- // sql += "EVENT_TYPE = 'OP' ";//전화설정
- // sql += "OR EVENT_TYPE = 'XP' ";//전화해제
- // sql += "OR EVENT_TYPE = 'SP' ";//화면좌표설정버튼
- // sql += "OR EVENT_TYPE = 'MS' ";//주음향정지버튼
- // sql += "OR EVENT_TYPE = 'LS' ";//지구음향정지버튼
- // sql += "OR EVENT_TYPE = 'EB' ";//비상방송정지버튼
- // sql += "OR EVENT_TYPE = 'RP' ";//예비전원시험버튼
- // sql += "OR EVENT_TYPE = 'ED' ";//설비연동조작버튼 // cyim 2016.12.09 : IFC3300에서는 예비전원버튼 -> 설비전체연동조작버튼
- // sql += "OR EVENT_TYPE = 'FR' ";//화재복구버튼
- // sql += "OR EVENT_TYPE = 'EC' ";//설비연동화면버튼
- // sql += "OR EVENT_TYPE = 'PO' ";//통신(POLLING) 시작
- // sql += "OR EVENT_TYPE = 'PF' ";//통신(POLLING) 종료
- // sql += "OR EVENT_TYPE = 'CC' ";//통신상태읽기
- // sql += "OR EVENT_TYPE = 'CV' ";//전압상태읽기
- // sql += "OR EVENT_TYPE = 'V2' ";//24 전압 변경
- // sql += "OR EVENT_TYPE = 'DC' ";//설비작동차단
- // sql += "OR EVENT_TYPE = 'CS' ";//환경설정
- // sql += "OR EVENT_TYPE = 'TK' ";//키패드테스트
- // sql += "OR EVENT_TYPE = 'VI' ";//통신프로그램버전정보
- // sql += "OR EVENT_TYPE = 'BF' ";//부저정지
- // sql += "OR EVENT_TYPE = 'EL' ";//이벤트리스트
- // sql += "OR EVENT_TYPE = 'BS' ";//보드상태
- // sql += "OR EVENT_TYPE = 'RA' ";//화재축적상태 재설정
- // sql += "OR EVENT_TYPE = 'OU' ";//UI프로그램시작
- // sql += "OR EVENT_TYPE = 'XU' ";//UI프로그램종료
- // sql += "OR EVENT_TYPE = 'DS' ";//설비작동상태조회
- // }
- // if (boardError)
- // {
- // if (fireOn || devOn || controlLog)
- // {
- // sql += "OR ";
- // }
- // else
- // {
- // sql += "AND ( ";
- // }
- // sql += "EVENT_TYPE = 'XM' "; //통신모듈 통신이상
- // sql += "OR EVENT_TYPE = 'XI' ";//I/O 모듈 통신이상
- // sql += "OR EVENT_TYPE = 'XK' ";//키패드모듈 통신이상
- // sql += "OR EVENT_TYPE = 'AV' ";//출력전압 이상
- // sql += "OR EVENT_TYPE = 'AR' ";//예비전원 이상
- // sql += "OR EVENT_TYPE = 'AA' ";//교류전원 이상
- // sql += "OR EVENT_TYPE = 'OD' ";//데몬연결이상////////////////////////////////////////
- // sql += "OR EVENT_TYPE = 'OB' ";//DB연결이상
- // sql += "OR EVENT_TYPE = 'BA' ";//통신보드루프교류전원이상
- // sql += "OR EVENT_TYPE = 'BR' ";//통신보드루프예비전원이상
- // sql += "OR EVENT_TYPE = 'XC' ";//비상방송 통신이상
- // sql += "OR EVENT_TYPE = 'OM' ";//통신모듈 통신정상
- // sql += "OR EVENT_TYPE = 'OI' ";//I/O 모듈 통신정상
- // sql += "OR EVENT_TYPE = 'OK' ";//키패드모듈 통신정상
- // sql += "OR EVENT_TYPE = 'NV' ";//출력전압 정상
- // sql += "OR EVENT_TYPE = 'NR' ";//예비전원 정상
- // sql += "OR EVENT_TYPE = 'NA' ";//교류전원 정상
- // sql += "OR EVENT_TYPE = 'XD' ";//데몬연결정상////////////////////////////////////////
- // sql += "OR EVENT_TYPE = 'XB' ";//DB연결정상
- // sql += "OR EVENT_TYPE = 'BN' ";//통신보드루프정상
- // sql += "OR EVENT_TYPE = 'OC' ";//비상방송 통신정상
- // }
- // if (repeaterError)
- // {
- // if (fireOn || devOn || controlLog || boardError)
- // {
- // sql += "OR ";
- // }
- // else
- // {
- // sql += "AND ( ";
- // }
- // sql += " EVENT_TYPE = 'XR' ";//중계기 통신이상
- // sql += "OR EVENT_TYPE = 'OR' ";//중계기 통신정상
- // sql += "OR EVENT_TYPE = 'OX' ";//중계기전원이상
- // sql += "OR EVENT_TYPE = 'XX' ";//중계기전원이상 해제
- // }
- // if (devError)
- // {
- // if (fireOn || devOn || controlLog || boardError || repeaterError)
- // {
- // sql += "OR ";
- // }
- // else
- // {
- // sql += "AND ( ";
- // }
- // sql += " EVENT_TYPE = 'OL' ";//중계기 단선발생
- // sql += "OR EVENT_TYPE = 'XL' ";//중계기 단선복구
- // }
- // sql += " ) ";
- // }
- // return sql;
- //}
- public string CreateSQLCount()
- {
- string sql = "SELECT ";
- sql += SqlCount();
- sql += SqlLocation();
- if (this.eventTypeDetail)
- {
- sql += SqlEventTypeDetail();
- }
- else
- {
- sql += SqlEventType();
- }
- sql += SqlTime();
- //Util.UDebugMessage("LOG SQL : " + sql, 0, 0);
- return sql;
- }
- // cyim 2013.9.17 데이타베이스 조회 컨셉변경 : 50000->30000
- public string CreateSQL()
- {
- string sql = "SELECT FIRST 30000 ";
- sql += SqlIndex();
- sql += SqlBase();
- sql += SqlLocation();
- if (this.eventTypeDetail)
- {
- sql += SqlEventTypeDetail();
- }
- else
- {
- sql += SqlEventType();
- }
- sql += SqlTime();
- sql += "ORDER BY EVENT_TIME DESC ";
- //Util.UDebugMessage("LOG SQL : " + sql, 0, 0);
- return sql;
- }
- // cyim 2013.9.17 데이타베이스 조회 컨셉변경 : 가져올 개수를 지정
- public string CreateSQL(int FirstCount)
- {
- if (FirstCount == 0) return null;
- string sql = "SELECT FIRST " + FirstCount.ToString() + " ";
- sql += SqlIndex();
- sql += SqlBase();
- sql += SqlLocation();
- if (this.eventTypeDetail)
- {
- sql += SqlEventTypeDetail();
- }
- else
- {
- sql += SqlEventType();
- }
- sql += SqlTime();
- sql += "ORDER BY EVENT_TIME DESC ";
- //Util.UDebugMessage("LOG SQL : " + sql, 0, 0);
- return sql;
- }
- // cyim 2013.10.10 : 1000건 조회 속도 개선 작업
- public string CreateSQL(int FirstCount, DateTime StartTime, DateTime EndTime)
- {
- if (FirstCount == 0) return null;
- string sql = "SELECT FIRST " + FirstCount.ToString() + " ";
- sql += SqlIndex();
- sql += SqlBase();
- sql += SqlLocation();
- if (this.eventTypeDetail)
- {
- sql += SqlEventTypeDetail();
- }
- else
- {
- sql += SqlEventType();
- }
- // cyim 2013.10.10 : 1000건 조회 속도 개선 작업
- sql += SqlTime(StartTime, EndTime);
- sql += "ORDER BY EVENT_TIME DESC ";
- //Util.UDebugMessage("LOG SQL : " + sql, 0, 0);
- return sql;
- }
- // cyim 2013.9.17 데이타베이스 조회 컨셉변경 : 가져올 데이타 개수를 미리 조회
- public string Get_EventLogDB_Count(DateTime StartTime, DateTime EndTime)
- {
- string sql = "SELECT COUNT(*) ";
- sql += SqlIndex();
- //sql += SqlBase();
- sql += "FROM TB_EVENT_LOG ";
- sql += "WHERE RECEIVER_ID > 0 ";
- sql += SqlLocation();
- if (this.eventTypeDetail)
- {
- sql += SqlEventTypeDetail();
- }
- else
- {
- sql += SqlEventType();
- }
- // cyim 2013.10.10 : 1000건 조회 속도 개선 작업
- sql += SqlTime(StartTime, EndTime);
- return sql;
- }
- //public string Get_COMMANDLogDB_Count(DateTime StartTime, DateTime EndTime)
- //{
- // string sql = "SELECT COUNT(*) ";
- // sql += SqlIndex();
- // //sql += SqlBase();
- // sql += "FROM TB_COMMAND_LOG ";
- // sql += "WHERE RECEIVER_ID > 0 ";
- // sql += SqlLocation();
- // if (this.eventTypeDetail)
- // {
- // sql += SqlCOMMANDTypeDetail();
- // }
- // //else
- // //{
- // // sql += SqlCOMMANDType();
- // //}
- // // cyim 2013.10.10 : 1000건 조회 속도 개선 작업
- // sql += COMMANDSqlTime(StartTime, EndTime);
- // return sql;
- //}
- }
- }
|