/// <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>
/// 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>
/// 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>
/// 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>
/// Retrieve all data node from entity table which is the child data node of a given Entity.
/// </summary>
/// <param name="serverRootName">The given entity, whose child data node will be gotten.</param>
/// <returns></returns>
public Dictionary <ulong, EtyEntity> GetAllDataNodesByServerRootName(string serverRootName)
{
string Function_Name = "GetAllDataNodesByServerRootName";
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 IN (SELECT DISTINCT PKEY FROM ENTITY WHERE NAME = '" + DAOHelper.convertEscapeStringAndGB2312To8859P1(serverRootName) + "')" +
" AND TYPEKEY IN (SELECT PKEY FROM ENTITYTYPE WHERE NAME = 'DataNode' ) " +
" AND DELETED = 0 ";
/* if (checkLoc)
* {
* //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 += " 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>
/// 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);
}
/// <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>
/// retrieve a configuration from TRENDVIEWER_CONFIG by configuration name.
/// </summary>
/// <param name="configName">configuration name</param>
/// <returns></returns>
public List <EtyDataPoint> GetDPByCfgName(string configName)
{
string Function_Name = "GetDPByCfgName";
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
List <EtyDataPoint> dpList = new List <EtyDataPoint>();
string localSQL = " SELECT 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 " +
" FROM TRENDVIEWER_CONFIG WHERE CONFIG_NAME = '" + DAOHelper.convertEscapeStringAndGB2312To8859P1(configName) + "'";
// string localSQL = " SELECT c.DATA_PT_NAME,c.DATA_PT_TYPE,c.DATA_PT_COLOR,c.DATA_PT_SERVER,c.DATA_PT_ENABLED,c.DATA_PT_LBL_ENABLED, c.DATA_PT_LBL_NAME, c.DATA_PT_HOST,c.DATA_PT_VISIBLE, "
// + "o.keyid from trendviewer_config c, opc_dt_pt o where c.data_pt_name = o.data_pt_name " +
// " and c.CONFIG_NAME = '" + DAOHelper.convertGB2312To8859P1(configName) + "'";
System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL);
EtyDataPoint newEtyDP = null;
try
{
while (drReader != null && drReader.Read())
{
newEtyDP = new EtyDataPoint();
if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_NAME")))
{
newEtyDP.DPName = DAOHelper.convert8859P1ToGB2312(drReader["DATA_PT_NAME"].ToString());
}
if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_TYPE")))
{
newEtyDP.DPType = DAOHelper.ConvertLineTypeToEnum(drReader["DATA_PT_TYPE"].ToString());
}
if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_COLOR")))
{
newEtyDP.DPColor = drReader["DATA_PT_COLOR"].ToString();
}
if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_SERVER")))
{
newEtyDP.DPServer = drReader["DATA_PT_SERVER"].ToString();
}
if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_ENABLED")))
{
newEtyDP.DPEnabled = DAOHelper.ChangeStrToBool(drReader["DATA_PT_ENABLED"].ToString());
}
if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_LBL_ENABLED")))
{
newEtyDP.DPLblEnabled = DAOHelper.ChangeStrToBool(drReader["DATA_PT_LBL_ENABLED"].ToString());
}
if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_LBL_NAME")))
{
newEtyDP.DPLblName = DAOHelper.convert8859P1ToGB2312(drReader["DATA_PT_LBL_NAME"].ToString());
}
if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_HOST")))
{
newEtyDP.DPHost = drReader["DATA_PT_HOST"].ToString();
}
if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_VISIBLE")))
{
newEtyDP.DPVisible = DAOHelper.ChangeStrToBool(drReader["DATA_PT_VISIBLE"].ToString());
}
// if (!drOracle.IsDBNull(9))
// newEtyDP.PKey = (ulong)(drOracle.GetOracleDecimal(9).ToInt64());
dpList.Add(newEtyDP);
}
}
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(dpList);
}
/// <summary>
/// get dp log list for a historical dp to plot
/// </summary>
/// <param name="histDP">the given historical dp for charting</param>
/// <param name="startTime">start time</param>
/// <param name="endTime">end time</param>
/// <returns>trend log list</returns>
public List <EtyTrendLog> GetHistDPLogList(EtyHistDataPoint histDP, DateTime startTime, DateTime endTime)
{
string Function_Name = "GetHistDPLogList";
List <EtyTrendLog> res = new List <EtyTrendLog>();
string localSQL;
//changed by luxiangmei, to make the logic for historical mode the same as mixed mode.
// if (usedInMixMode) //used in mixed mode
// {
localSQL = " SELECT DATA_PT_DATE,DATA_PT_VALUE " +
" FROM TRENDVIEWER_LOG WHERE DATA_PT_NAME = '" + DAOHelper.convertEscapeStringAndGB2312To8859P1(histDP.DPName) + "' " +
" AND DATA_PT_SERVER = '" + DAOHelper.convertEscapeStringAndGB2312To8859P1(histDP.DPServer) + "' " +
" AND DATA_PT_HOST = '" + DAOHelper.convertEscapeStringAndGB2312To8859P1(histDP.DPHost) + "' " +
" AND DATA_PT_DATE >= @StartDateValue" +
" AND DATA_PT_DATE <= @EndDateValue" +
" ORDER BY DATA_PT_DATE";
// }
// else // used in history mode, will ignore "startTime" and "endTime"
// {
// localSQL = " SELECT DATA_PT_DATE,DATA_PT_VALUE " +
// " FROM TRENDVIEWER_LOG WHERE DATA_PT_NAME = '" + DAOHelper.convertGB2312To8859P1(histDP.DPName) + "' " +
// " AND DATA_PT_SERVER = '" + DAOHelper.convertGB2312To8859P1(histDP.DPServer) + "' " +
// " AND DATA_PT_HOST = '" + DAOHelper.convertGB2312To8859P1(histDP.DPHost) + "' " +
// " AND DATA_PT_DATE >= to_date(" + histDP.DPStartDateTime.ToString("yyyyMMdd") +
// "000000,'YYYYMMDDHH24MISS') " +
// " AND DATA_PT_DATE <= to_date(" + histDP.DPEndDateTime.ToString("yyyyMMdd") +
// "235959,'YYYYMMDDHH24MISS') " +
// " ORDER BY DATA_PT_DATE";
// }
SqlParameter parameter1 = new SqlParameter();
parameter1.ParameterName = "StartDateValue";
parameter1.DbType = DbType.DateTime;
DateTime dtStart = new DateTime(histDP.DPStartDateTime.Year, histDP.DPStartDateTime.Month, histDP.DPStartDateTime.Day,
startTime.Hour, startTime.Minute, startTime.Second);
parameter1.Value = dtStart;
parameter1.Direction = System.Data.ParameterDirection.Input;
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(parameter1);
SqlParameter parameter2 = new SqlParameter();
parameter2.ParameterName = "EndDateValue";
parameter2.DbType = DbType.DateTime;
DateTime dtEnd = new DateTime(histDP.DPEndDateTime.Year, histDP.DPEndDateTime.Month, histDP.DPEndDateTime.Day,
endTime.Hour, endTime.Minute, endTime.Second);
parameter2.Value = dtEnd;
parameter2.Direction = System.Data.ParameterDirection.Input;
parameters.Add(parameter2);
System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQueryWithParams(localSQL, parameters);
try
{
while (drReader != null && drReader.Read())
{
EtyTrendLog entity = new EtyTrendLog();
if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_DATE")))
{
DateTime dt;
if (DateTime.TryParse(drReader["DATA_PT_DATE"].ToString(), out dt))
{
entity.Data_PT_Time = dt;
}
}
if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_VALUE")))
{
entity.Data_PT_Value = Convert.ToDouble(drReader["DATA_PT_VALUE"]);
}
res.Add(entity);
}
}
catch (System.Exception ex)
{
LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString());
}
if (drReader != null)
{
drReader.Close();
drReader.Dispose();
}
return(res);
}
/// <summary>
/// get all formulas by a configuration name.
/// </summary>
/// <param name="configName">formula configuration name</param>
/// <returns>fomula list</returns>
public List <EtyFormula> GetFormulasByCfgName(string configName)
{
string Function_Name = "GetFormulasByCfgName";
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
List <EtyFormula> formulaList = new List <EtyFormula>();
string localSQL = " SELECT DATA_PT_EQUATION,DATA_PT_TYPE,DATA_PT_COLOR,DATA_PT_ENABLED,DATA_PT_LBL_ENABLED,DATA_PT_LBL_NAME " +
" FROM TRENDVIEWER_FORMULA_CONFIG WHERE CONFIG_NAME = '" + DAOHelper.convertEscapeStringAndGB2312To8859P1(configName) + "'";
System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL);
EtyFormula newEtyFormula = null;
try
{
while (drReader != null && drReader.Read())
{
newEtyFormula = new EtyFormula();
if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_EQUATION")))
{
newEtyFormula.DPEquation = drReader["DATA_PT_EQUATION"].ToString();
}
if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_TYPE")))
{
newEtyFormula.DPType = DAOHelper.ConvertLineTypeToEnum(drReader["DATA_PT_TYPE"].ToString());
}
if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_COLOR")))
{
newEtyFormula.DPColor = drReader["DATA_PT_COLOR"].ToString();
}
if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_ENABLED")))
{
newEtyFormula.DPEnabled = DAOHelper.ChangeStrToBool(drReader["DATA_PT_ENABLED"].ToString());
}
if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_LBL_ENABLED")))
{
newEtyFormula.DPLblEnabled = DAOHelper.ChangeStrToBool(drReader["DATA_PT_LBL_ENABLED"].ToString());
}
if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_LBL_NAME")))
{
newEtyFormula.DPLblName = DAOHelper.convert8859P1ToGB2312(drReader["DATA_PT_LBL_NAME"].ToString());
}
formulaList.Add(newEtyFormula);
}
}
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(formulaList);
}
/// <summary>
/// insert a historical datapoint configuration into table TRENDVIEWER_HIST_CONFIG,
/// if histDP.pkey=0, insert null into entitykey column (means no entity key is related to this datapoint)
/// </summary>
/// <param name="histDPList">the historical datapoint list included in a configuration</param>
/// <param name="grpName">the historical configuration name</param>
public bool InsertHistDPListToGrp(List <EtyHistDataPoint> histDPList, string grpName)
{
string Function_Name = "InsertHistDPListToGrp";
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
bool executeOK = false;
string localSQL = " INSERT INTO TRENDVIEWER_HIST_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_DT,DATA_PT_HOST,DATA_PT_DT_END, ENTITYKEY) " +
" VALUES( '" + DAOHelper.convertEscapeStringAndGB2312To8859P1(grpName) + "'";
string fullSQL;
foreach (EtyHistDataPoint datapoint in histDPList)
{
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) + "'"
+ ", @StartDateValue"
+ ", '" + datapoint.DPHost + "'"
+ ", @EndDateValue"
+ ",";
if (datapoint.EntityKey != 0)
{
fullSQL = fullSQL + datapoint.EntityKey.ToString() + ")";
}
else
{
fullSQL = fullSQL + "NULL )";
}
SqlParameter parameter1 = new SqlParameter();
parameter1.ParameterName = "StartDateValue";
parameter1.DbType = DbType.DateTime;
DateTime dtStart = new DateTime(datapoint.DPStartDateTime.Year, datapoint.DPStartDateTime.Month, datapoint.DPStartDateTime.Day,
0, 0, 0);
parameter1.Value = dtStart;
parameter1.Direction = System.Data.ParameterDirection.Input;
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(parameter1);
SqlParameter parameter2 = new SqlParameter();
parameter2.ParameterName = "EndDateValue";
parameter2.DbType = DbType.DateTime;
DateTime dtEnd = new DateTime(datapoint.DPEndDateTime.Year, datapoint.DPEndDateTime.Month, datapoint.DPEndDateTime.Day,
23, 59, 59);
parameter2.Value = dtEnd;
parameter2.Direction = System.Data.ParameterDirection.Input;
parameters.Add(parameter2);
executeOK = SimpleDatabase.GetInstance().ExecuteNonQueryWithParams(fullSQL, parameters);
fullSQL = "";
if (executeOK == false)
{
break;
}
}
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
return(executeOK);
}
/// <summary>
/// get historical datapoint configuration by config name
/// </summary>
/// <param name="configName">hist dp config name</param>
/// <returns>hist dp list in one group</returns>
public List <EtyHistDataPoint> GetHistDPByCfgName(string configName)
{
string Function_Name = "GetHistDPByCfgName";
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
List <EtyHistDataPoint> histDPList = new List <EtyHistDataPoint>();
string localSQL = " SELECT 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_DT," +
" DATA_PT_HOST,DATA_PT_DT_END ,ENTITYKEY " +
" FROM TRENDVIEWER_HIST_CONFIG WHERE CONFIG_NAME = '" + DAOHelper.convertEscapeStringAndGB2312To8859P1(configName) + "'";
System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL);
EtyHistDataPoint newEtyHistDP = null;
try
{
while (drReader != null && drReader.Read())
{
newEtyHistDP = new EtyHistDataPoint();
if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_NAME")))
{
newEtyHistDP.DPName = DAOHelper.convert8859P1ToGB2312(drReader["DATA_PT_NAME"].ToString());
}
if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_TYPE")))
{
newEtyHistDP.DPType = DAOHelper.ConvertLineTypeToEnum(drReader["DATA_PT_TYPE"].ToString());
}
if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_COLOR")))
{
newEtyHistDP.DPColor = drReader["DATA_PT_COLOR"].ToString();
}
if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_SERVER")))
{
newEtyHistDP.DPServer = drReader["DATA_PT_SERVER"].ToString();
}
if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_ENABLED")))
{
newEtyHistDP.DPEnabled = DAOHelper.ChangeStrToBool(drReader["DATA_PT_ENABLED"].ToString());
}
if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_LBL_ENABLED")))
{
newEtyHistDP.DPLblEnabled = DAOHelper.ChangeStrToBool(drReader["DATA_PT_LBL_ENABLED"].ToString());
}
if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_LBL_NAME")))
{
newEtyHistDP.DPLblName = DAOHelper.convert8859P1ToGB2312(drReader["DATA_PT_LBL_NAME"].ToString());
}
if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_DT")))
{
DateTime dt;
if (DateTime.TryParse(drReader["DATA_PT_DT"].ToString(), out dt))
{
newEtyHistDP.DPStartDateTime = dt;
}
}
if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_HOST")))
{
newEtyHistDP.DPHost = drReader["DATA_PT_HOST"].ToString();
}
if (!drReader.IsDBNull(drReader.GetOrdinal("DATA_PT_DT_END")))
{
DateTime dt;
if (DateTime.TryParse(drReader["DATA_PT_DT_END"].ToString(), out dt))
{
newEtyHistDP.DPEndDateTime = dt;
}
}
if (!drReader.IsDBNull((drReader.GetOrdinal("ENTITYKEY"))))
{
newEtyHistDP.EntityKey = Convert.ToUInt64(drReader["ENTITYKEY"]);
}
histDPList.Add(newEtyHistDP);
}
}
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(histDPList);
}
