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