/// <summary>
/// Updates the datapoints of specified name
/// </summary>
/// <param name="disabled">true-disabled , false - enabled</param>
/// <param name="opcServerName">datapoint server name</param>
/// <param name="sampleGrpId">sample group key</param>
/// <param name="DataPointName">datapoint name to be updated</param>
public void UpdateOPCDataPointByName(EtyOPCDataPoint etyDataPoint) //bool disabled, string opcServerName, double sampleGrpId,string DataPointName)
{
const string Function_Name = "UpdateOPCDataPointByName";
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
string localSQL = " UPDATE OPC_DT_PT "
+ " SET SAMPLE_GRP_ID = " + etyDataPoint.OPCSampleGrpId.ToString()
+ ",DISABLE = '" + DAOHelper.ChangeBoolToStr(etyDataPoint.Disabled) + "'"
+ " WHERE DATA_PT_NAME = '" + DAOHelper.convertGB2312To8859P1(etyDataPoint.OPCDataPointName.Trim()) + "'"
+ " AND DATA_PT_SERVER = '" + DAOHelper.convertGB2312To8859P1(etyDataPoint.OPCDataPointServer.Trim()) + "'";
//DBConnection.getInstance().ExecuteOracleNonQuery(localSQL, dbConn);
if (!SimpleDatabase.GetInstance().ExecuteNonQuery(localSQL))
{
string dpName = etyDataPoint.OPCDataPointName.Trim();
if (dpName.Contains(".Value"))
{
dpName = dpName.Remove(dpName.Length - 6);
}
//insert the datapoint into the OPC_dt_pt table.
localSQL = "INSERT INTO OPC_DT_PT (DATA_PT_HOST, DATA_PT_SERVER, KEYID, DATA_PT_NAME, DATA_PT_DESC, PKEYID,SAMPLE_GRP_ID,DISABLE)"
+ " (SELECT '127.0.0.1', 'TransActiveDataSource', PKEY,CONCAT(NAME,'.Value'),DESCRIPTION , PARENTKEY,"
+ etyDataPoint.OPCSampleGrpId.ToString()
+ ",'" + DAOHelper.ChangeBoolToStr(etyDataPoint.Disabled) + "'"
+ " FROM ENTITY WHERE TYPEKEY IN (SELECT PKEY FROM ENTITYTYPE WHERE NAME = 'DataPoint' )"
+ " AND NAME = '" + dpName + "')";
SimpleDatabase.GetInstance().ExecuteNonQuery(localSQL);
}
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
}
/// <summary>
/// Returns Unique Enabled Datapoints of specified servername and hostname,
/// which belongs to enabled OPC sample group
/// </summary>
/// <param name="hostName">DataPoint Hostname</param>
/// <param name="serverName">DataPoint ServerName</param>
/// <returns>DataPoint</returns>
public List <EtyOPCDataPoint> GetUniqueEnabledOPCDataPoint(string hostName, string serverName)
{
const string Function_Name = "GetUniqueEnabledOPCDataPoint";
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
List <EtyOPCDataPoint> etyDataPointList = new List <EtyOPCDataPoint>();
string localSQL = "SELECT DISTINCT dt.DATA_PT_NAME,dt.SAMPLE_GRP_ID " +
" FROM OPC_DT_PT dt, OPC_DT_SAMPLE_GRP grp " +
" WHERE dt.DATA_PT_HOST = '" + hostName.Trim() + "' AND dt.DATA_PT_SERVER = '" + serverName.Trim() + "' AND dt.DISABLE = 'N' AND dt.SAMPLE_GRP_ID = grp.SAMPLE_GRP_ID and grp.DISABLE = 'N' ";
System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL);
if (drReader != null)
{
try
{
while (drReader.Read())
{
EtyOPCDataPoint etyDataPoint = new EtyOPCDataPoint();
if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_NAME")))
{
etyDataPoint.OPCDataPointName = drReader["DATA_PT_NAME"].ToString();
}
if (!drReader.IsDBNull(drReader.GetOrdinal("SAMPLE_GRP_ID")))
{
etyDataPoint.OPCSampleGrpId = Convert.ToDouble(drReader["SAMPLE_GRP_ID"]);
}
etyDataPointList.Add(etyDataPoint);
}
}
catch (System.Exception ex)
{
LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString());
}
drReader.Close();
drReader.Dispose();
}
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
return(etyDataPointList);
}
/// <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 <EtyOPCDataPoint> GetDataPointByName(string opcServerName, string DataPointNameSubstr)
{
const string Function_Name = "GetDataPointByName";
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
List <EtyOPCDataPoint> etyDataPointList = new List <EtyOPCDataPoint>();
/* string localSQL = " SELECT KEYID,PKEYID,DATA_PT_NAME,DATA_PT_DESC,SAMPLE_GRP_ID " +
* " FROM OPC_DT_PT " +
* " WHERE DATA_PT_SERVER = '" + TrendingHelper.convertGB2312To8859P1(opcServerName.Trim()) + "'" +
* " And DATA_PT_NAME LIKE '%" + DAO.Trending.TrendingHelper.convertGB2312To8859P1(DataPointNameSubstr) + "%'";
*
* localSQL += " ORDER BY DATA_PT_NAME ";*/
/*string localSQL = " SELECT et.PKEY,et.PARENTKEY,CONCAT(et.NAME,'.Value')as NAME ,et.DESCRIPTION, dt.SAMPLE_GRP_ID FROM ENTITY et, OPC_DT_PT dt" +
* " WHERE TYPEKEY = (SELECT PKEY FROM ENTITYTYPE WHERE NAME = 'DataPoint')" +
* " AND UPPER(NAME) LIKE '%" + DAOHelper.convertGB2312To8859P1(DataPointNameSubstr) +"%'"+
* " AND et.PKEY = dt.KEYID(+)";*/
string localSQL = " SELECT et.PKEY,et.PARENTKEY,CONCAT(et.NAME,'.Value')as NAME ,et.DESCRIPTION, dt.SAMPLE_GRP_ID FROM ENTITY et LEFT JOIN OPC_DT_PT dt" +
" ON et.PKEY = dt.KEYID WHERE TYPEKEY = (SELECT PKEY FROM ENTITYTYPE WHERE NAME = 'DataPoint')" +
" AND UPPER(NAME) LIKE '%" + DAOHelper.convertGB2312To8859P1(DataPointNameSubstr) + "%'";
localSQL += " ORDER BY NAME ";
System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL);
if (drReader != null)
{
try
{
while (drReader.Read())
{
EtyOPCDataPoint etyDataPoint = new EtyOPCDataPoint();
if (!drReader.IsDBNull(drReader.GetOrdinal("PKEY")))
{
etyDataPoint.OPCDataPointId = Convert.ToDouble(drReader["PKEY"]);
}
if (!drReader.IsDBNull(drReader.GetOrdinal("PARENTKEY")))
{
etyDataPoint.OPCParentDataPointId = Convert.ToDouble(drReader["PARENTKEY"]);
}
if (!drReader.IsDBNull(drReader.GetOrdinal("NAME")))
{
etyDataPoint.OPCDataPointName = DAOHelper.convert8859P1ToGB2312(drReader["NAME"].ToString());
}
if (!drReader.IsDBNull(drReader.GetOrdinal("DESCRIPTION")))
{
etyDataPoint.OPCDataPointDesc = DAOHelper.convert8859P1ToGB2312(drReader["DESCRIPTION"].ToString());
}
if (!drReader.IsDBNull(drReader.GetOrdinal("SAMPLE_GRP_ID")))
{
etyDataPoint.OPCSampleGrpId = Convert.ToDouble(drReader["SAMPLE_GRP_ID"]);
}
etyDataPointList.Add(etyDataPoint);
}
}
catch (System.Exception ex)
{
LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString());
}
drReader.Close();
drReader.Dispose();
}
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
return(etyDataPointList);
}