public bool InsertOPCDataPoint(EtyDataLogDPTrend etyDPTrend)
{
const string Function_Name = "InsertOPCDataPoint";
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
string sEnabled = DAOHelper.GetEnabledStr(DAOHelper.ChangeBoolToStr(etyDPTrend.Disabled));
string dpName = etyDPTrend.OPCDataPointName.Trim();
if (dpName.Contains(".Value"))
{
dpName = dpName.Remove(dpName.Length - 6);
}
List <string> sqlStrings = new List <string>();
for (int i = 0; i < 2; i++)
{
string sql = "INSERT INTO DATALOG_DP_TREND (PKEY, ENTITY_KEY, DP_NAME, DP_GRP_ID,ENABLED)"
+ " (SELECT PKEY,PKEY,CONCAT(NAME,'.Value'),"
+ etyDPTrend.OPCSampleGrpId.ToString()
+ ",'" + sEnabled + "'"
+ " FROM ENTITY WHERE TYPEKEY IN (SELECT PKEY FROM ENTITYTYPE WHERE NAME = 'DataPoint' )"
+ " AND NAME = '" + dpName + "')";
sqlStrings.Add(sql);
}
List <SqlParameter> parameters = DAOHelper.CreateEnqueneParameters(sqlStrings);
return(SimpleDatabase.GetInstance().ExecuteEnqueneProcedure(parameters));
}
/// <summary>
/// retrieve all the configuration name for real-time charting.
/// </summary>
/// <returns></returns>
public List <string> GetAllDPGrpNames()
{
string Function_Name = "GetAllDPGrpNames";
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
List <string> dpGrpNames = new List <string>();
string localSQL = " SELECT DISTINCT CONFIG_NAME FROM TRENDVIEWER_CONFIG order by config_name ";
System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL);
if (drReader != null)
{
try
{
while (drReader.Read())
{
if (!drReader.IsDBNull(0))
{
dpGrpNames.Add(DAOHelper.convert8859P1ToGB2312(drReader["CONFIG_NAME"].ToString()));
}
}
}
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(dpGrpNames);
}
public string GetLocationNameFromKey(double locationKey)
{
string Function_Name = "GetLocationNameFromKey";
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
string locationName = "";
string localSQL = "SELECT NAME FROM LOCATION WHERE PKEY = " + locationKey.ToString();
System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL);
try
{
if (drReader != null && drReader.Read())
{
if (!drReader.IsDBNull(drReader.GetOrdinal("NAME")))
{
locationName = DAOHelper.convert8859P1ToGB2312(drReader["NAME"].ToString());
}
}
}
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(locationName);
}
/// <summary>
/// insert a new formula configuration to table TRENDVIEWER_FORMULA_CONFIG
/// </summary>
/// <param name="formulaList">the formula list included in the configuration</param>
/// <param name="grpName">the configuration name</param>
public bool InsertFormulaListToGrp(List <EtyFormula> formulaList, string grpName)
{
string Function_Name = "InsertFormulaListToGrp";
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
bool executeOK = false;
string localSQL = " INSERT INTO TRENDVIEWER_FORMULA_CONFIG(CONFIG_NAME,DATA_PT_EQUATION,DATA_PT_TYPE, " +
" DATA_PT_COLOR,DATA_PT_ENABLED,DATA_PT_LBL_ENABLED,DATA_PT_LBL_NAME) " +
" VALUES( '" + DAOHelper.convertEscapeStringAndGB2312To8859P1(grpName) + "'";
foreach (EtyFormula formula in formulaList)
{
executeOK = SimpleDatabase.GetInstance().ExecuteNonQuery(localSQL
+ ",'" + formula.DPEquation.Replace("'", "''") + "'"
+ ",'" + DAOHelper.ConvertLineTypeToDBString(formula.DPType) + "'"
+ ",'" + formula.DPColor + "'"
+ ",'" + DAOHelper.ChangeBoolToStr(formula.DPEnabled) + "'"
+ ",'" + DAOHelper.ChangeBoolToStr(formula.DPLblEnabled) + "'"
+ ",'" + DAOHelper.convertEscapeStringAndGB2312To8859P1(formula.DPLblName) + "'"
+ " ) ");
if (executeOK == false)
{
break;
}
}
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
return(executeOK);
}
/// <summary>
/// Updates the datapoints of specified name
/// </summary>
/// <param name="EtyDataLogDPTrend"></param>
public bool UpdateOPCDataPointByName(EtyDataLogDPTrend etyDPTrend)
{
const string Function_Name = "UpdateOPCDataPointByName";
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
string sEnabled = DAOHelper.GetEnabledStr(DAOHelper.ChangeBoolToStr(etyDPTrend.Disabled));
if (GetOPCDataPointByName(etyDPTrend.OPCDataPointName))
{
List <string> sqlStrings = new List <string>();
for (int i = 0; i < 2; i++)
{
string sql = " UPDATE DATALOG_DP_TREND "
+ " SET DP_GRP_ID = " + etyDPTrend.OPCSampleGrpId.ToString()
+ ",ENABLED = '" + sEnabled + "'"
+ " WHERE DP_NAME = '" + DAOHelper.convertEscapeStringAndGB2312To8859P1(etyDPTrend.OPCDataPointName.Trim()) + "'";
sqlStrings.Add(sql);
}
List <SqlParameter> parameters = DAOHelper.CreateEnqueneParameters(sqlStrings);
return(SimpleDatabase.GetInstance().ExecuteEnqueneProcedure(parameters));
}
else
{
return(InsertOPCDataPoint(etyDPTrend));
}
}
/// <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>
/// insert marker list to a marker group
/// </summary>
/// <param name="markerList">marker list</param>
/// <param name="grpName">marker group name</param>
public bool InsertMarkerListToGrp(List <EtyMarker> markerList, string grpName)
{
const string Function_Name = "InsertMarkerListToGrp";
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
bool executeOK = false;
string localSQL = " INSERT INTO TRENDVIEWER_MARKER(CONFIG_NAME,MARKER_NAME,MARKER_WIDTH,MARKER_BCOLOR,MARKER_VALUE,MARKER_ENABLED,MARKER_FCOLOR) " +
" VALUES( '" + DAOHelper.convertEscapeStringAndGB2312To8859P1(grpName) + "'";
foreach (EtyMarker marker in markerList)
{
string markerEnabledStr = "Y";
if (!marker.MarkerEnabled)
{
markerEnabledStr = "N";
}
executeOK = SimpleDatabase.GetInstance().ExecuteNonQuery(localSQL
+ ",'" + DAOHelper.convertEscapeStringAndGB2312To8859P1(marker.MarkerName) + "'"
+ ", " + marker.MarkerWidth.ToString()
+ ",'" + marker.MarkerBColor + "'"
+ ", " + marker.MarkerValue.ToString()
+ ",'" + markerEnabledStr + "'"
+ ",'" + marker.MarkerFColor + "'"
+ " ) ");
if (executeOK == false)
{
break;
}
}
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
return(executeOK);
}
/// <summary>
/// Returns Sample Group details of the specified Sample group Key.
/// </summary>
/// <param name="opcGrpID">Sample group key</param>
/// <returns>Sample Group entity</returns>
public EtyOPCSampleGroup GetOPCGrpByID(string opcGrpID)
{
string Function_Name = "GetOPCGrpByID";
LogHelper.Trace(CLASS_NAME, Function_Name, string.Format("Function_Entered with params - {0}", opcGrpID));
EtyOPCSampleGroup etyOPCSampleGrp = new EtyOPCSampleGroup();
string localSQL = " SELECT SAMPLE_GRP_NAME,SAMPLE_GRP_DESC,SAMPLE_INTERVAL,INTERVAL_TYPE,START_TIME,DELTA_VALUE,DISABLE "
+ " FROM OPC_DT_SAMPLE_GRP WHERE SAMPLE_GRP_ID = " + opcGrpID;
System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL);
try
{
while (drReader != null && drReader.Read())
{
if (!drReader.IsDBNull(drReader.GetOrdinal("SAMPLE_GRP_NAME")))
{
etyOPCSampleGrp.SampleGrpName = DAOHelper.convert8859P1ToGB2312(drReader["SAMPLE_GRP_NAME"].ToString());
}
if (!drReader.IsDBNull(drReader.GetOrdinal("SAMPLE_GRP_DESC")))
{
etyOPCSampleGrp.SampleGrpDescription = DAOHelper.convert8859P1ToGB2312(drReader["SAMPLE_GRP_DESC"].ToString());
}
if (!drReader.IsDBNull(drReader.GetOrdinal("SAMPLE_INTERVAL")))
{
etyOPCSampleGrp.Interval = Convert.ToInt32(drReader["SAMPLE_INTERVAL"]);
}
if (!drReader.IsDBNull(drReader.GetOrdinal("INTERVAL_TYPE")))
{
etyOPCSampleGrp.IntervalType = drReader["INTERVAL_TYPE"].ToString();
}
if (!drReader.IsDBNull(drReader.GetOrdinal("START_TIME")))
{
etyOPCSampleGrp.StartTime = drReader["START_TIME"].ToString();
}
if (!drReader.IsDBNull(drReader.GetOrdinal("DELTA_VALUE")))
{
etyOPCSampleGrp.DeltaValue = Convert.ToDouble(drReader["DELTA_VALUE"]);
}
if (!drReader.IsDBNull(drReader.GetOrdinal("DISABLE")))
{
etyOPCSampleGrp.Disabled = DAOHelper.ChangeStrToBool(drReader["DISABLE"].ToString());
}
}
}
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(etyOPCSampleGrp);
}
/// <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 the Root Data Nodes.
/// </summary>
/// <returns>Root Data Nodes</returns>
public List <EtyOPCDataNode> GetAllOPCDataNode(string serverRootName, string opcServerName)
{
const string Function_Name = "GetAllOPCDataNode";
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
List <EtyOPCDataNode> OPCDataNodeList = new List <EtyOPCDataNode>();
string localSQL = " SELECT KEYID,DT_NODE_NAME,DT_NODE_DESC " +
" FROM OPC_DT_NODE WHERE PKEYID IN (SELECT DISTINCT PKEY FROM ENTITY WHERE NAME = '" + serverRootName + "') AND DISABLE = 'N' AND DATA_PT_SERVER = '" + opcServerName
+ "' ORDER BY DT_NODE_NAME";
//sql for getting from entity table
/*string localSQL = " SELECT PKEY,NAME,DESCRIPTION" +
* " FROM ENTITY WHERE TYPEKEY = (SELECT PKEY FROM ENTITYTYPE WHERE NAME = 'DataNode')"+
* " AND PARENTKEY IN (SELECT DISTINCT PKEY FROM ENTITY WHERE TYPEKEY IN (SELECT PKEY FROM ENTITYTYPE WHERE NAME = 'SCADAROOT'))"+
* " ORDER BY NAME";*/
System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL);
if (drReader != null)
{
try
{
while (drReader.Read())
{
EtyOPCDataNode etyOPCDataNode = new EtyOPCDataNode();
if (!drReader.IsDBNull(drReader.GetOrdinal("KEYID")))
{
etyOPCDataNode.OPCDataNodeId = Convert.ToDouble(drReader["KEYID"]);
}
if (!drReader.IsDBNull(drReader.GetOrdinal("DT_NODE_NAME")))
{
etyOPCDataNode.OPCDataNodeName = DAOHelper.convert8859P1ToGB2312(drReader["DT_NODE_NAME"].ToString());
}
if (!drReader.IsDBNull(drReader.GetOrdinal("DT_NODE_DESC")))
{
etyOPCDataNode.OPCDataNodeDesc = DAOHelper.convert8859P1ToGB2312(drReader["DT_NODE_DESC"].ToString());
}
OPCDataNodeList.Add(etyOPCDataNode);
}
}
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(OPCDataNodeList);
}
/// <summary>
/// Returns the DataPoints based on the filterstring for specified server name.
/// e.g. for filterstring can include substring datapoint name to search.
/// </summary>
/// <param name="opcServerName">datapoint server name</param>
/// <param name="filterString">Where clause query string</param>
/// <returns>Datapoints</returns>
public List <EtyDataLogDPTrend> GetOPCDataPoint(string opcServerName, string filterString)
{
const string Function_Name = "GetOPCDataPoint";
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 et.TYPEKEY = (SELECT PKEY FROM ENTITYTYPE WHERE NAME = 'DataPoint')";
if (filterString.Trim() != "")
{
localSQL += filterString;
}
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>
/// Updates HAS_DT_PT flag of the specified sample Group
/// </summary>
/// <param name="opcGrpId">sample group key</param>
/// <param name="hasDP">flag value</param>
public void UpdateGrpDataPointFlag(double opcGrpId, bool hasDP)
{
string Function_Name = "UpdateGrpDataPointFlag";
LogHelper.Trace(CLASS_NAME, Function_Name, string.Format("Function_Entered with params - SampleGroup ID = {0}, Flag = {1}", opcGrpId, hasDP));
string localSQL = "UPDATE OPC_DT_SAMPLE_GRP SET HAS_DT_PT = '" + DAOHelper.ChangeBoolToStr(hasDP) + "'"
+ " WHERE SAMPLE_GRP_ID = " + opcGrpId.ToString();
SimpleDatabase.GetInstance().ExecuteNonQuery(localSQL);
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
}
/// <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>
/// Updates the DataPoints of specified sampleGroup.
/// </summary>
/// <param name="disabled">true - disabled, false - enabled</param>
/// <param name="opcServerName">datapoint server name</param>
/// <param name="sampleGrpId">datapoint Sample group key</param>
public void UpdateOPCDataPointByGrpId(bool disabled, string opcServerName, double sampleGrpId)
{
const string Function_Name = "UpdateOPCDataPointByGrpId";
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
string localSQL = " UPDATE OPC_DT_PT "
+ " SET SAMPLE_GRP_ID = ''"
+ ",DISABLE = '" + DAOHelper.ChangeBoolToStr(disabled) + "'"
+ " WHERE DATA_PT_SERVER = '" + DAOHelper.convertGB2312To8859P1(opcServerName.Trim()) + "'"
+ " AND SAMPLE_GRP_ID = " + sampleGrpId.ToString();
SimpleDatabase.GetInstance().ExecuteNonQuery(localSQL);
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
}
public bool UpdateTrendVersionNum()
{
string Function_Name = "UpdateTrendVersionNum";
List <string> sqlStrings = new List <string>();
for (int i = 0; i < 2; i++)
{
string sql = "UPDATE DATALOG_CONFIGSETTINGS SET VERSION_NUM = (VERSION_NUM+1) WHERE CONFIG_TYPE = 'Trending'";
sqlStrings.Add(sql);
}
List <SqlParameter> parameters = DAOHelper.CreateEnqueneParameters(sqlStrings);
bool bRes = true;
bRes = SimpleDatabase.GetInstance().ExecuteEnqueneProcedure(parameters);
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
return(bRes);
}
/// <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);
}
/// <summary>
/// Delete opc Sample Group by key
/// </summary>
/// <param name="opcGrpId">Sample group key</param>
public bool DeleteOPCSampleGroupById(string opcGrpId)
{
string Function_Name = "DeleteOPCSampleGroupById";
LogHelper.Trace(CLASS_NAME, Function_Name, string.Format("Function_Entered with params - SampleGroup ID = {0}", opcGrpId));
bool result = true;
SimpleDatabase.GetInstance().BeginTransaction();
try
{
List <string> sqlStrings = new List <string>();
for (int i = 0; i < 2; i++)
{
string sql = "DELETE FROM DATALOG_DP_GROUP_TREND WHERE PKEY = " + opcGrpId;
sqlStrings.Add(sql);
}
List <SqlParameter> parameters = DAOHelper.CreateEnqueneParameters(sqlStrings);
result = SimpleDatabase.GetInstance().ExecuteEnqueneProcedure(parameters);
}
catch (Exception localException)
{
LogHelper.Error(CLASS_NAME, Function_Name, localException.ToString());
}
if (result)
{
//update datapoint table also.
result = DatalogDPTrendDAO.GetInstance().DisableGrpDataPoint(Convert.ToDouble(opcGrpId));
}
if (result)
{
result = DatalogConfigSettingsDAO.GetInstance().UpdateTrendVersionNum();
}
if (!result)
{
SimpleDatabase.GetInstance().RollbackTransaction();
}
else
{
SimpleDatabase.GetInstance().CommitTransaction();
}
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
return(result);
}
/// <summary>
/// Disables and clears the sample group link of the datapoints belonging to specified sample group
/// </summary>
/// <param name="sampleGrpId">datapoint sample group key</param>
public bool DisableGrpDataPoint(double dpGrpId)
{
const string Function_Name = "DisableGrpDataPoint";
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
List <string> sqlStrings = new List <string>();
for (int i = 0; i < 2; i++)
{
string sql = "UPDATE DATALOG_DP_TREND SET ENABLED = 'N',DP_GRP_ID = '' WHERE DP_GRP_ID = " + dpGrpId;
sqlStrings.Add(sql);
}
List <SqlParameter> parameters = DAOHelper.CreateEnqueneParameters(sqlStrings);
bool bRes = true;
bRes = SimpleDatabase.GetInstance().ExecuteEnqueneProcedure(parameters);
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
return(bRes);
}
/// <summary>
/// Returns all OPC Sample Group key and Name map
/// </summary>
/// <returns>Sample group key-Name map</returns>
public Dictionary <string, string> GetAllOPCGrpIdAndName()
{
string Function_Name = "GetAllOPCGrpIdAndName";
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
Dictionary <string, string> opcGrpIDToNameDic = new Dictionary <string, string>();
string localSQL;
localSQL = "SELECT SAMPLE_GRP_ID, SAMPLE_GRP_NAME FROM OPC_DT_SAMPLE_GRP";
System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL);
try
{
while (drReader != null && drReader.Read())
{
string grpID = "", grpName = "";
if (!drReader.IsDBNull(drReader.GetOrdinal("SAMPLE_GRP_ID")))
{
grpID = drReader["SAMPLE_GRP_ID"].ToString();
}
if (!drReader.IsDBNull(drReader.GetOrdinal("SAMPLE_GRP_NAME")))
{
grpName = DAOHelper.convert8859P1ToGB2312(drReader["SAMPLE_GRP_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();
}
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
return(opcGrpIDToNameDic);
}
/// <summary>
/// Updates the given sample group entity.
/// </summary>
/// <param name="etyOPCGrp">sample group entity</param>
public void UpdateOPCGrpData(EtyOPCSampleGroup etyOPCGrp)
{
string Function_Name = "UpdateOPCGrpData";
LogHelper.Trace(CLASS_NAME, Function_Name, string.Format("Function_Entered with params - SampleGroup Name = {0}", etyOPCGrp.SampleGrpName));
string localSQL;
string sample_grp_name = DAOHelper.convertGB2312To8859P1(etyOPCGrp.SampleGrpName);
localSQL = " UPDATE OPC_DT_SAMPLE_GRP "
+ " SET SAMPLE_GRP_DESC = '" + DAOHelper.convertGB2312To8859P1(etyOPCGrp.SampleGrpDescription) + "'"
+ ",SAMPLE_INTERVAL = " + etyOPCGrp.Interval
+ ",INTERVAL_TYPE = '" + etyOPCGrp.IntervalType + "'"
+ ",START_TIME = '" + etyOPCGrp.StartTime + "'"
+ ",DELTA_VALUE = " + etyOPCGrp.DeltaValue
+ ",DISABLE = '" + DAOHelper.ChangeBoolToStr(etyOPCGrp.Disabled) + "'"
+ " WHERE SAMPLE_GRP_NAME ='" + sample_grp_name + "'";
SimpleDatabase.GetInstance().ExecuteNonQuery(localSQL);
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
}
/// <summary>
/// Updates the DataPoints of specified sampleGroup.
/// </summary>
/// <param name="disabled">true - disabled, false - enabled</param>
/// <param name="sampleGrpId">datapoint Sample group key</param>
public bool UpdateOPCDataPointByGrpId(bool disabled, double sampleGrpId)
{
const string Function_Name = "UpdateOPCDataPointByGrpId";
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
string sEnabled = DAOHelper.GetEnabledStr(DAOHelper.ChangeBoolToStr(disabled));
List <string> sqlStrings = new List <string>();
for (int i = 0; i < 2; i++)
{
string sql = " UPDATE DATALOG_DP_TREND "
+ " SET DP_GRP_ID = ''"
+ ",ENABLED = '" + sEnabled + "'"
+ " WHERE DP_GRP_ID = " + sampleGrpId.ToString();
sqlStrings.Add(sql);
}
List <SqlParameter> parameters = DAOHelper.CreateEnqueneParameters(sqlStrings);
bool bRes = true;
bRes = SimpleDatabase.GetInstance().ExecuteEnqueneProcedure(parameters);
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
return(bRes);
}
/// <summary>
/// insert a new configuration into TRENDVIEWER_CONFIG.
/// </summary>
/// <param name="datapointList">realtime dp list in a configuration</param>
/// <param name="grpName">configuration name</param>
public bool InsertDPListToGrp(List <EtyDataPoint> datapointList, string grpName)
{
string Function_Name = "InsertDPListToGrp";
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
bool executeOK = false;
string localSQL = " INSERT INTO TRENDVIEWER_CONFIG(CONFIG_NAME,DATA_PT_NAME,DATA_PT_TYPE, " +
" DATA_PT_COLOR,DATA_PT_SERVER,DATA_PT_ENABLED,DATA_PT_LBL_ENABLED,DATA_PT_LBL_NAME,DATA_PT_HOST,DATA_PT_VISIBLE) " +
" VALUES( '" + DAOHelper.convertEscapeStringAndGB2312To8859P1(grpName) + "'";
string fullSQL;
foreach (EtyDataPoint datapoint in datapointList)
{
fullSQL = localSQL
+ ",'" + DAOHelper.convertEscapeStringAndGB2312To8859P1(datapoint.DPName) + "'"
+ ",'" + DAOHelper.ConvertLineTypeToDBString(datapoint.DPType) + "'"
+ ",'" + datapoint.DPColor + "'"
+ ",'" + datapoint.DPServer + "'"
+ ",'" + DAOHelper.ChangeBoolToStr(datapoint.DPEnabled) + "'"
+ ",'" + DAOHelper.ChangeBoolToStr(datapoint.DPLblEnabled) + "'"
+ ",'" + DAOHelper.convertEscapeStringAndGB2312To8859P1(datapoint.DPLblName) + "'"
+ ",'" + datapoint.DPHost + "'"
+ ",'" + DAOHelper.ChangeBoolToStr(datapoint.DPVisible) + "'"
+ " ) ";
executeOK = SimpleDatabase.GetInstance().ExecuteNonQuery(fullSQL);
fullSQL = "";
if (executeOK == false)
{
break;
}
}
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
return(executeOK);
}
/// <summary>
/// Inserts new sample group into database
/// </summary>
/// <param name="etyOPCGrp">new sample group entity</param>
public bool InsertOPCGrpData(EtyDataLogDPGroupTrend etyDPGroupTrend)
{
string Function_Name = "InsertOPCGrpData";
LogHelper.Trace(CLASS_NAME, Function_Name, string.Format("Function_Entered with params - SampleGroup Name = {0}", etyDPGroupTrend.SampleGrpName));
SimpleDatabase.GetInstance().BeginTransaction();
// Get The max pkey from the database first and then insert the group with pkey value.
// To avoid having incorrect pkey value between stations , hence avoid concurrency problem
SqlStatements sqlQueries = new SqlStatements();
sqlQueries.OracleSql = DAOHelper.ORACLE_SEQ_FUNC_SQL;
sqlQueries.MySqlStr = DAOHelper.MYSQL_SEQ_FUNC_SQL;
System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQueryWithSqls(sqlQueries);
double nextPkey = 1;
if (drReader != null && drReader.Read())
{
if (drReader.FieldCount == 1)
{
nextPkey = Convert.ToDouble(drReader[0]);
}
}
if (drReader != null)
{
drReader.Close();
drReader.Dispose();
}
//
//
string enabled = DAOHelper.GetEnabledStr(DAOHelper.ChangeBoolToStr(etyDPGroupTrend.Disabled));
string startTime = "";
if (etyDPGroupTrend.StartTime.Length != 0)
{
startTime = DateTime.Parse(etyDPGroupTrend.StartTime).ToString("dd/MM/yyyy HH:mm:ss");
}
string sample_grp_name = DAOHelper.convertEscapeStringAndGB2312To8859P1(etyDPGroupTrend.SampleGrpName);
List <string> sqlStrings = new List <string>();
for (int i = 0; i < 2; i++)
{
string date_func = (i == 0) ? DAOHelper.ORACLE_DATE_FUNCTION : DAOHelper.MYSQL_DATE_FUNCTION;
string date_format = (i == 0) ? DAOHelper.ORACLE_DATE_FORMAT : DAOHelper.MYSQL_DATE_FORMAT;
if (startTime.Trim().Length != 0)
{
string sql = "INSERT INTO DATALOG_DP_GROUP_TREND "
+ " (PKEY,NAME,DESCRIPTION,SAMPLE_INTERVAL,START_TIME,DELTA_VALUE,ENABLED,INTERVAL_TYPE,LOCATION_KEY)"
+ " VALUES"
+ " (" + nextPkey + ",'" + sample_grp_name + "'"
+ ",'" + DAOHelper.convertEscapeStringAndGB2312To8859P1(etyDPGroupTrend.SampleGrpDescription) + "'"
+ "," + etyDPGroupTrend.Interval
+ "," + date_func + "('" + startTime + "','" + date_format
+ "')," + etyDPGroupTrend.DeltaValue
+ ",'" + enabled + "'"
+ ",'" + etyDPGroupTrend.IntervalType + "',"
+ LocationKeyHelper.GetInstance().LocationKey.ToString() + ")";
sqlStrings.Add(sql);
}
else
{
string sql = "INSERT INTO DATALOG_DP_GROUP_TREND "
+ " (PKEY,NAME,DESCRIPTION,SAMPLE_INTERVAL,DELTA_VALUE,ENABLED,INTERVAL_TYPE,LOCATION_KEY)"
+ " VALUES"
+ " (" + nextPkey + ",'" + sample_grp_name + "'"
+ ",'" + DAOHelper.convertEscapeStringAndGB2312To8859P1(etyDPGroupTrend.SampleGrpDescription) + "'"
+ "," + etyDPGroupTrend.Interval
+ "," + etyDPGroupTrend.DeltaValue
+ ",'" + enabled + "'"
+ ",'" + etyDPGroupTrend.IntervalType + "',"
+ LocationKeyHelper.GetInstance().LocationKey.ToString() + ")";
sqlStrings.Add(sql);
}
}
List <SqlParameter> parameters = DAOHelper.CreateEnqueneParameters(sqlStrings);
bool rRes = true;
rRes = SimpleDatabase.GetInstance().ExecuteEnqueneProcedure(parameters);
if (rRes)
{
rRes = DatalogConfigSettingsDAO.GetInstance().UpdateTrendVersionNum();
}
if (!rRes)
{
SimpleDatabase.GetInstance().RollbackTransaction();
}
else
{
SimpleDatabase.GetInstance().CommitTransaction();
}
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
return(rRes);
}
/// <summary>
/// Updates the given sample group entity.
/// </summary>
/// <param name="etyDPGroupTrend">sample group entity</param>
public bool UpdateOPCGrpData(EtyDataLogDPGroupTrend etyDPGroupTrend)
{
string Function_Name = "UpdateOPCGrpData";
LogHelper.Trace(CLASS_NAME, Function_Name, string.Format("Function_Entered with params - SampleGroup Name = {0}", etyDPGroupTrend.SampleGrpName));
SimpleDatabase.GetInstance().BeginTransaction();
string enabled = DAOHelper.GetEnabledStr(DAOHelper.ChangeBoolToStr(etyDPGroupTrend.Disabled));
string startTime = "";
if (etyDPGroupTrend.StartTime.Length != 0)
{
startTime = DateTime.Parse(etyDPGroupTrend.StartTime).ToString("dd/MM/yyyy HH:mm:ss");
}
string sample_grp_name = DAOHelper.convertEscapeStringAndGB2312To8859P1(etyDPGroupTrend.SampleGrpName);
List <string> sqlStrings = new List <string>();
for (int i = 0; i < 2; i++)
{
string date_func = (i == 0)?DAOHelper.ORACLE_DATE_FUNCTION :DAOHelper.MYSQL_DATE_FUNCTION;
string date_format = (i == 0)?DAOHelper.ORACLE_DATE_FORMAT :DAOHelper.MYSQL_DATE_FORMAT;
if (startTime.Trim().Length != 0)
{
string sql = " UPDATE DATALOG_DP_GROUP_TREND "
+ " SET DESCRIPTION = '" + DAOHelper.convertEscapeStringAndGB2312To8859P1(etyDPGroupTrend.SampleGrpDescription) + "'"
+ ",SAMPLE_INTERVAL = " + etyDPGroupTrend.Interval
+ ",INTERVAL_TYPE = '" + etyDPGroupTrend.IntervalType + "'"
+ ",START_TIME = " + date_func
+ "('" + startTime + "','" + date_format
+ "') ,DELTA_VALUE = " + etyDPGroupTrend.DeltaValue
+ ",ENABLED = '" + enabled + "'"
+ " WHERE NAME = '" + sample_grp_name + "' AND LOCATION_KEY = "
+ etyDPGroupTrend.LocationKey.ToString();
sqlStrings.Add(sql);
}
else
{
string sql = " UPDATE DATALOG_DP_GROUP_TREND "
+ " SET DESCRIPTION = '" + DAOHelper.convertEscapeStringAndGB2312To8859P1(etyDPGroupTrend.SampleGrpDescription) + "'"
+ ",SAMPLE_INTERVAL = " + etyDPGroupTrend.Interval
+ ",INTERVAL_TYPE = '" + etyDPGroupTrend.IntervalType + "'"
+ ",START_TIME = null, DELTA_VALUE = " + etyDPGroupTrend.DeltaValue
+ ",ENABLED = '" + enabled + "'"
+ " WHERE NAME = '" + sample_grp_name + "' AND LOCATION_KEY = "
+ etyDPGroupTrend.LocationKey.ToString();
sqlStrings.Add(sql);
}
}
List <SqlParameter> parameters = DAOHelper.CreateEnqueneParameters(sqlStrings);
bool rRes = true;
rRes = SimpleDatabase.GetInstance().ExecuteEnqueneProcedure(parameters);
if (rRes)
{
rRes = DatalogConfigSettingsDAO.GetInstance().UpdateTrendVersionNum();
}
if (!rRes)
{
SimpleDatabase.GetInstance().RollbackTransaction();
}
else
{
SimpleDatabase.GetInstance().CommitTransaction();
}
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
return(rRes);
}
/// <summary>
/// Returns Sample Group details of the specified Sample group Key.
/// </summary>
/// <param name="opcGrpID">Sample group key</param>
/// <returns>Sample Group entity</returns>
public EtyDataLogDPGroupTrend GetOPCGrpByID(double opcGrpID)
{
string Function_Name = "GetOPCGrpByID";
LogHelper.Trace(CLASS_NAME, Function_Name, string.Format("Function_Entered with params - {0}", opcGrpID));
EtyDataLogDPGroupTrend etyDpGroupTrend = new EtyDataLogDPGroupTrend();
string localSQL = " SELECT 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.PKEY = " + opcGrpID.ToString() + "AND LOC.PKEY = DP.LOCATION_KEY";
/* string LocationClauseStr = DAOHelper.CheckLocationAndAddSQL(COLUMN_LOCATION_KEY);
* if (LocationClauseStr.Length != 0)
* {
* localSQL += " AND" + LocationClauseStr;
* }*/
System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL);
try
{
while (drReader != null && drReader.Read())
{
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.ToInt32(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)))
{
etyDpGroupTrend.Disabled = !(DAOHelper.ChangeStrToBool(drReader[COLUMN_ENABLED].ToString()));
}
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());
}
}
}
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(etyDpGroupTrend);
}
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);
}
/// <summary>
/// delete a marker group
/// </summary>
/// <param name="grpName">the group name to be deleted</param>
public bool DeleteAllMarkerInGrp(string grpName)
{
const string Function_Name = "DeleteAllMarkerInGrp";
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
bool excuteOK = false;
string localSQL = " DELETE FROM TRENDVIEWER_MARKER WHERE CONFIG_NAME = '" + DAOHelper.convertEscapeStringAndGB2312To8859P1(grpName) + "'";
excuteOK = SimpleDatabase.GetInstance().ExecuteNonQuery(localSQL);
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
return(excuteOK);
}
/// <summary>
/// get marker list by a group name
/// </summary>
/// <param name="configName">the marker group name</param>
/// <returns>the marker list in the group</returns>
public List <EtyMarker> GetMarkersByCfgName(string configName)
{
const string Function_Name = "GetMarkersByCfgName";
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
List <EtyMarker> markerList = new List <EtyMarker>();
string localSQL = " SELECT CONFIG_NAME, MARKER_NAME,MARKER_WIDTH,MARKER_BCOLOR,MARKER_VALUE,MARKER_ENABLED,MARKER_FCOLOR" +
" FROM TRENDVIEWER_MARKER WHERE CONFIG_NAME = '" + DAOHelper.convertEscapeStringAndGB2312To8859P1(configName) + "'";
System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL);
EtyMarker newEtyMarker = null;
try
{
while (drReader != null && drReader.Read())
{
newEtyMarker = new EtyMarker();
if (!drReader.IsDBNull(drReader.GetOrdinal("CONFIG_NAME")))
{
newEtyMarker.ConfigName = DAOHelper.convert8859P1ToGB2312(drReader["CONFIG_NAME"].ToString());
}
if (!drReader.IsDBNull(drReader.GetOrdinal("MARKER_NAME")))
{
newEtyMarker.MarkerName = DAOHelper.convert8859P1ToGB2312(drReader["MARKER_NAME"].ToString());
}
if (!drReader.IsDBNull(drReader.GetOrdinal("MARKER_WIDTH")))
{
newEtyMarker.MarkerWidth = Convert.ToDouble(drReader["MARKER_WIDTH"]); //need test
}
if (!drReader.IsDBNull(drReader.GetOrdinal("MARKER_BCOLOR")))
{
newEtyMarker.MarkerBColor = drReader["MARKER_BCOLOR"].ToString();
}
if (!drReader.IsDBNull(drReader.GetOrdinal("MARKER_VALUE")))
{
newEtyMarker.MarkerValue = Convert.ToDouble(drReader["MARKER_VALUE"]);
}
if (!drReader.IsDBNull(drReader.GetOrdinal("MARKER_ENABLED")))
{
string markerEnabled = drReader["MARKER_ENABLED"].ToString();
if (markerEnabled == "Y")
{
newEtyMarker.MarkerEnabled = true;
}
else
{
newEtyMarker.MarkerEnabled = false;
}
}
if (!drReader.IsDBNull(drReader.GetOrdinal("MARKER_FCOLOR")))
{
newEtyMarker.MarkerFColor = drReader["MARKER_FCOLOR"].ToString();
}
markerList.Add(newEtyMarker);
}
}
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(markerList);
}