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