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