/// <summary> /// Returns the DataPoints based specified datapoint name substring. /// </summary> /// <param name="opcServerName">Datapoint server name</param> /// <param name="DataPointNameSubstr"> Datapoint name substring to be queried</param> /// <returns>Datapoints</returns> public List <EtyDataLogDPTrend> GetDataPointByName(string opcServerName, string DataPointNameSubstr) { const string Function_Name = "GetDataPointByName"; LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered"); List <EtyDataLogDPTrend> etyDPTrendList = new List <EtyDataLogDPTrend>(); string localSQL = "SELECT et.PKEY AS ENTITY_KEY,CONCAT(et.NAME,'.Value')as DP_NAME ,DP.DP_GRP_ID AS DP_GRP_ID FROM ENTITY et LEFT JOIN DATALOG_DP_TREND DP" + " ON et.PKEY = DP.ENTITY_KEY WHERE TYPEKEY = (SELECT PKEY FROM ENTITYTYPE WHERE NAME = 'DataPoint')" + " AND UPPER(et.NAME) LIKE '%" + DAOHelper.convertEscapeStringAndGB2312To8859P1(DataPointNameSubstr) + "%'"; string LocationClauseStr = DAOHelper.CheckLocationAndAddSQL(COLUMN_ENTITY_LOCATIONKEY); if (LocationClauseStr.Length != 0) { localSQL += " AND" + LocationClauseStr; } localSQL += " ORDER BY DP_NAME "; System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL); if (drReader != null) { try { while (drReader.Read()) { EtyDataLogDPTrend etyDPTrend = new EtyDataLogDPTrend(); if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_ENTITY_KEY))) { etyDPTrend.EntityKey = Convert.ToDouble(drReader[COLUMN_ENTITY_KEY]); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_NAME))) { etyDPTrend.OPCDataPointName = DAOHelper.convert8859P1ToGB2312(drReader[COLUMN_NAME].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_SAMPLE_GRP_ID))) { etyDPTrend.OPCSampleGrpId = Convert.ToDouble(drReader[COLUMN_SAMPLE_GRP_ID]); } etyDPTrendList.Add(etyDPTrend); } } catch (System.Exception ex) { LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString()); } drReader.Close(); drReader.Dispose(); //SimpleDatabase.GetInstance().CloseCurrentSession(); } LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited"); return(etyDPTrendList); }
/// <summary> /// Returns all OPC Sample Group key and Name map /// </summary> /// <returns>Sample group key-Name map</returns> public Dictionary <string, string> GetAllOPCGrpIdAndName(bool checkLoc) { string Function_Name = "GetAllOPCGrpIdAndName"; LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered"); Dictionary <string, string> opcGrpIDToNameDic = new Dictionary <string, string>(); string localSQL; localSQL = "SELECT PKEY, NAME FROM DATALOG_DP_GROUP_TREND"; string LocationClauseStr = DAOHelper.CheckLocationAndAddSQL(COLUMN_LOCATION_KEY); if (LocationClauseStr.Length != 0 && checkLoc) { localSQL += " WHERE" + LocationClauseStr; } System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL); try { while (drReader != null && drReader.Read()) { string grpID = "", grpName = ""; if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_PKEY))) { grpID = drReader[COLUMN_PKEY].ToString(); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_NAME))) { grpName = DAOHelper.convert8859P1ToGB2312(drReader[COLUMN_NAME].ToString()); } opcGrpIDToNameDic.Add(grpID, grpName); } } catch (System.Exception ex) { LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString()); } if (drReader != null) { drReader.Close(); drReader.Dispose(); //SimpleDatabase.GetInstance().CloseCurrentSession(); } LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited"); return(opcGrpIDToNameDic); }
/// <summary> /// Returns the total sample group records count in table /// </summary> /// <returns>No of Sample group configured</returns> public int GetRowCount() { string Function_Name = "GetRowCount"; LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered"); string localSQL; int nRowCount = 0; localSQL = "SELECT COUNT(NAME) AS MAXROWS FROM DATALOG_DP_GROUP_TREND"; string LocationClauseStr = DAOHelper.CheckLocationAndAddSQL(COLUMN_LOCATION_KEY); if (LocationClauseStr.Length != 0) { localSQL += " WHERE" + LocationClauseStr; } System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL); try { if (drReader != null && drReader.Read()) { if (!drReader.IsDBNull(0)) { LogHelper.Debug(CLASS_NAME, Function_Name, "Function_Exited"); nRowCount = Convert.ToInt32(drReader["MAXROWS"]); } } } catch (System.Exception ex) { LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString()); } if (drReader != null) { drReader.Close(); drReader.Dispose(); //SimpleDatabase.GetInstance().CloseCurrentSession(); } LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited"); return(nRowCount); }
public List <EtyDataLogDPGroupTrend> GetAllOPCGrpsBySort(string sortColumn, string sortOrder) { string Function_Name = "GetAllOPCGrpsBySort"; LogHelper.Trace(CLASS_NAME, Function_Name, string.Format("Function_Entered with params - {0},{1}", sortColumn, sortOrder)); List <EtyDataLogDPGroupTrend> etyDPGrpTrendList = new List <EtyDataLogDPGroupTrend>(); string localSQL; localSQL = " SELECT DP.PKEY,DP.NAME,DP.DESCRIPTION,DP.SAMPLE_INTERVAL,DP.INTERVAL_TYPE,DP.START_TIME,DP.DELTA_VALUE,DP.ENABLED,DP.LOCATION_KEY,LOC.NAME AS LOCATION_NAME " + " FROM DATALOG_DP_GROUP_TREND DP, LOCATION LOC WHERE DP.LOCATION_KEY = LOC.PKEY"; string LocationClauseStr = DAOHelper.CheckLocationAndAddSQL(COLUMN_LOCATION_KEY); if (LocationClauseStr.Length != 0) { localSQL += " AND" + LocationClauseStr; } localSQL += " ORDER BY " + sortColumn; sortOrder = sortOrder.ToUpper(); if (sortOrder.Equals("ASCENDING") || sortOrder.Equals("ASC")) { localSQL += " ASC "; } else { localSQL += " DESC "; } System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL); try { while (drReader != null && drReader.Read()) { EtyDataLogDPGroupTrend etyDPGroupTrend = new EtyDataLogDPGroupTrend(); if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_PKEY))) { etyDPGroupTrend.SampleGrpID = Convert.ToDouble(drReader[COLUMN_PKEY]); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_NAME))) { etyDPGroupTrend.SampleGrpName = DAOHelper.convert8859P1ToGB2312(drReader[COLUMN_NAME].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_DESCRIPTION))) { etyDPGroupTrend.SampleGrpDescription = DAOHelper.convert8859P1ToGB2312(drReader[COLUMN_DESCRIPTION].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_SAMPLE_INTERVAL))) { etyDPGroupTrend.Interval = Convert.ToDouble(drReader[COLUMN_SAMPLE_INTERVAL]); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_INTERVAL_TYPE))) { etyDPGroupTrend.IntervalType = drReader[COLUMN_INTERVAL_TYPE].ToString(); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_START_TIME))) { etyDPGroupTrend.StartTime = drReader[COLUMN_START_TIME].ToString(); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_DELTA_VALUE))) { etyDPGroupTrend.DeltaValue = Convert.ToDouble(drReader[COLUMN_DELTA_VALUE]); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_ENABLED))) { if (drReader[COLUMN_ENABLED].ToString() == "Y") { etyDPGroupTrend.Disabled = false; } else { etyDPGroupTrend.Disabled = true; } } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_LOCATION_KEY))) { etyDPGroupTrend.LocationKey = Convert.ToDouble(drReader[COLUMN_LOCATION_KEY]); } if (!drReader.IsDBNull(drReader.GetOrdinal(LOCATION_NAME))) { etyDPGroupTrend.LocationName = DAOHelper.convert8859P1ToGB2312(drReader[LOCATION_NAME].ToString()); } etyDPGrpTrendList.Add(etyDPGroupTrend); } } catch (System.Exception ex) { LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString()); } if (drReader != null) { drReader.Close(); drReader.Dispose(); } LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited"); return(etyDPGrpTrendList); }
/// <summary> /// Get All child data point by a given entity pkey /// </summary> /// <param name="pkey">the parent entity pkey</param> /// <returns></returns> public Dictionary <ulong, EtyEntity> GetDataPointByDNPkey(ulong pkey) { string Function_Name = "GetDataPointByDNPkey"; LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered"); Dictionary <ulong, EtyEntity> res = new Dictionary <ulong, EtyEntity>(); // localSQL = " SELECT KEYID,DATA_PT_NAME,DATA_PT_DESC,DISABLE " + // " FROM OPC_DT_PT " + // " WHERE PKEYID = " + parentKeyID + // " AND DATA_PT_HOST = '" + opcServerHostBox.Text.Trim() + "' AND DATA_PT_SERVER = '" + serverNameBox.Text.Trim() + "' " + // " ORDER BY DATA_PT_NAME"; string localSQL = " SELECT PKEY,CONCAT(NAME,'.Value') as LONGNAME,DESCRIPTION " + " FROM ENTITY " + " WHERE PARENTKEY = " + pkey.ToString() + " AND TYPEKEY IN (SELECT PKEY FROM ENTITYTYPE WHERE NAME = 'DataPoint' ) " + " AND DELETED = 0 "; /*if (checkLoc) * { * localSQL = string.Concat(localSQL, " AND LOCATIONKEY=", locKey); * }*/ string LocationClauseStr = DAOHelper.CheckLocationAndAddSQL(COLUMN_LOCATIONKEY); if (LocationClauseStr.Length != 0) { localSQL += " AND" + LocationClauseStr; } localSQL = localSQL + " ORDER BY NAME"; System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL); try { while (drReader != null && drReader.Read()) { EtyEntity entity = new EtyEntity(); if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_PKEY))) { entity.Pkey = Convert.ToUInt64(drReader[COLUMN_PKEY]); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_LONGNAME))) { entity.Name = DAOHelper.convert8859P1ToGB2312(drReader[COLUMN_LONGNAME].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_DESCRIPTION))) { entity.Description = DAOHelper.convert8859P1ToGB2312(drReader[COLUMN_DESCRIPTION].ToString()); } res.Add(entity.Pkey, entity); } } catch (System.Exception ex) { LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString()); } if (drReader != null) { drReader.Close(); drReader.Dispose(); //SimpleDatabase.GetInstance().CloseCurrentSession(); } LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited"); return(res); }
/// <summary> /// Get all the child data node by an entity pkey /// </summary> /// <param name="pkey">the parent entity pkey</param> /// <returns></returns> public Dictionary <ulong, EtyEntity> GetDataNodeChildrenByPkey(ulong pkey) { string Function_Name = "GetDataNodeChildrenByPkey"; LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered"); Dictionary <ulong, EtyEntity> res = new Dictionary <ulong, EtyEntity>(); string localSQL = " SELECT PKEY,NAME,DESCRIPTION " + " FROM ENTITY " + " WHERE PARENTKEY = " + pkey.ToString() + " AND TYPEKEY IN (SELECT PKEY FROM ENTITYTYPE WHERE NAME = 'DataNode' ) " + " AND DELETED = 0 "; /* if (checkLoc) * { * //localSQL = localSQL + " AND LOCATIONKEY=" + locKey.ToString; * localSQL = string.Concat(localSQL, " AND LOCATIONKEY=", locKey); * }*/ string LocationClauseStr = DAOHelper.CheckLocationAndAddSQL(COLUMN_LOCATIONKEY); if (LocationClauseStr.Length != 0) { localSQL += " AND" + LocationClauseStr; } localSQL = localSQL + " ORDER BY NAME"; System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL); try { while (drReader != null && drReader.Read()) { EtyEntity entity = new EtyEntity(); if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_PKEY))) { entity.Pkey = Convert.ToUInt64(drReader[COLUMN_PKEY]); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_NAME))) { entity.Name = DAOHelper.convert8859P1ToGB2312(drReader[COLUMN_NAME].ToString()); } if (!drReader.IsDBNull(drReader.GetOrdinal(COLUMN_DESCRIPTION))) { entity.Description = DAOHelper.convert8859P1ToGB2312(drReader[COLUMN_DESCRIPTION].ToString()); } res.Add(entity.Pkey, entity); } } catch (System.Exception ex) { LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString()); } if (drReader != null) { drReader.Close(); drReader.Dispose(); //SimpleDatabase.GetInstance().CloseCurrentSession(); } LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited"); return(res); }