public void CloseReader()
{
reader.Close();
reader.Dispose();
ReCloseTimes++;
HdisCommon.Log.Logging("{0}", "CloseReader()" + " ReCloseTimes:" + ReCloseTimes);
}
public virtual object getmodel(object key, string alias)
{
OrmAnalysis ormAnalysis;
EFWCoreLib.CoreFrame.DbProvider.AbstractDatabase _oleDb = GetDb();
ICacheManager _cache = GetCache();
string _pluginName = GetPluginName();
ormAnalysis = OrmAnalysisFactory.CreateOrmAnalysisObject(_oleDb.DbType);
ormAnalysis.Alias = alias;
ormAnalysis.Db = _oleDb;
ormAnalysis.entityAttrList = (List <EntityAttributeInfo>)EntityManager.GetAttributeInfo(_cache, _pluginName);
object value = null;
object keyVal = key == null?ormAnalysis.GetEntityDataKeyValue(this) : key;
string strsql = ormAnalysis.GetEntitySQL(this.GetType(), keyVal);
System.Data.IDataReader result = _oleDb.GetDataReader(strsql);
if (result.Read())
{
value = ToObject(result, this, alias);
}
result.Close();
result.Dispose();
return(value);
}
/// <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);
}
private static int LoadFromDbase(Shotgun.Database.IBaseDataClass2 dBase)
{
var sql = "SELECT cpls.id,cpls.cp_pool_id,tn.sp_trone_id,tod.trone_id,trone_order_id,priority,"
+ " ( cpls.status=1 and stn.status=1 and tn.status=1 and tod.disable=0 and stn.is_on_api=1) status"
+ " FROM daily_config.tbl_cp_pool_set cpls "
+ "left join tbl_cp_pool cpl on cpl.id= cpls.cp_pool_id "
+ "left join tbl_trone_order tod on cpls.trone_order_id= tod.id "
+ "left join tbl_trone tn on tn.id= tod.trone_id "
+ "left join tbl_sp_trone stn on stn.id= tn.sp_trone_id "
+ "where cpl.status=1 ";
var cmd = dBase.Command();
cmd.CommandText = sql;
System.Data.IDataReader rd = null;
int c = 0;
try
{
rd = dBase.ExecuteReader(cmd);
lock (cache.SyncRoot)
{
cache.ClearCache();
while (rd.Read())
{
PoolSetModel pm = new PoolSetModel();
pm.id = rd.GetInt32(0);
pm.cp_pool_id = rd.GetInt32(1);
pm.sp_trone_id = rd.GetInt32(2);
pm.trone_id = rd.GetInt32(3);
pm.trone_order_id = rd.GetInt32(4);
pm.priority = rd.GetInt32(5);
pm.status = rd.GetInt32(6);
cache.InsertItem(pm);
c++;
}
}
}
finally
{
if (rd != null)
{
rd.Dispose();
}
cmd.Dispose();
}
return(c);
}
/// <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>
/// Get all the historical datapoint configuration names
/// </summary>
/// <returns></returns>
public List <string> GetAllHistDPGrpNames()
{
string Function_Name = "GetAllHistDPGrpNames";
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
List <string> dpGrpNames = new List <string>();
string localSQL = " SELECT DISTINCT CONFIG_NAME FROM TRENDVIEWER_HIST_CONFIG order by config_name ";
System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQuery(localSQL);
try
{
while (drReader != null && drReader.Read())
{
if (!drReader.IsDBNull(0))
{
dpGrpNames.Add(DAOHelper.convert8859P1ToGB2312(drReader.GetString(0).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(dpGrpNames);
}
/// <summary>
/// Returns a list object of AvatarArchives. This is being used for wiredux
/// </summary>
/// <param name="Public"></param>
/// <returns></returns>
public List <AvatarArchive> GetAvatarArchives(bool isPublic)
{
List <AvatarArchive> returnValue = new List <AvatarArchive>();
try
{
System.Data.IDataReader RetVal = GD.QueryData("where IsPublic = 1", "avatararchives", "Name, Snapshot, IsPublic");
while (RetVal.Read())
{
AvatarArchive Archive = new AvatarArchive();
Archive.Name = RetVal["Name"].ToString();
Archive.Snapshot = RetVal["Snapshot"].ToString();
Archive.IsPublic = int.Parse(RetVal["IsPublic"].ToString());
returnValue.Add(Archive);
}
RetVal.Close();
RetVal.Dispose();
}
catch
{
}
GD.CloseDatabase();
return(returnValue);
}
/// <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>
/// 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>
// /// insert a record into TRENDVIEWER_LOG
// /// </summary>
// /// <param name="etyTrendLog">a record of trending log</param>
// /// <returns>true/false(successful/failed)</returns>
// public bool InsertTrendViewerLog(EtyTrendLog etyTrendLog)
// {
// string Function_Name = "InsertTrendViewerLog";
// LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
// bool result = false;
// /*string localSQL = " INSERT INTO TRENDVIEWER_LOG(DATA_PT_HOST," +
// "DATA_PT_SERVER,DATA_PT_NAME,DATA_PT_VALUE,DATA_PT_DATE) " +
// " VALUES( '" + etyTrendLog.Data_PT_Host + "'" +
// " ,'" + etyTrendLog.Data_PT_Server + "'" +
// " ,'" + etyTrendLog.Data_PT_Name + "'" +
// " ,'" + etyTrendLog.Data_PT_Value + "'" +
// " , to_date('" + etyTrendLog.Data_PT_Time.ToString("yyyyMMddHHmmss") + "','YYYYMMDDHH24MISS') " +
// " )";
// result = SimpleDatabase.getInstance().ExecuteAndReturnNonQuery(localSQL);*/
// string localSQL = " INSERT INTO TRENDVIEWER_LOG(DATA_PT_HOST," +
// "DATA_PT_SERVER,DATA_PT_NAME,DATA_PT_VALUE,DATA_PT_DATE) " +
// " VALUES( '" + etyTrendLog.Data_PT_Host + "'" +
// " ,'" + etyTrendLog.Data_PT_Server + "'" +
// " ,'" + etyTrendLog.Data_PT_Name + "'" +
// " ,'" + etyTrendLog.Data_PT_Value + "'" +
// " ," + SimpleDatabase.GetInstance().GetParameterDelimiter() + "DateValue)";
// SqlParameter parameter = new SqlParameter();
// parameter.ParameterName = "DateValue";
// parameter.DbType = DbType.DateTime;
// parameter.Value = etyTrendLog.Data_PT_Time;
// parameter.Direction = System.Data.ParameterDirection.Input;
// List<SqlParameter> parameters = new List<SqlParameter>();
// parameters.Add(parameter);
// result = SimpleDatabase.GetInstance().ExecuteNonQueryWithParams(localSQL, parameters);
//
// LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
// return result;
// }
/// <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 <EtyDataLogDPLogTrend> GetHistDPLogList(EtyHistDataPoint histDP, DateTime startTime, DateTime endTime)
{
string Function_Name = "GetHistDPLogList";
List <EtyDataLogDPLogTrend> res = new List <EtyDataLogDPLogTrend>();
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 CREATETIME,VALUE " +
" FROM DATALOG_DP_LOG_TREND WHERE ENTITY_KEY = " + histDP.EntityKey +
" AND CREATETIME >= @StartDateValue" +
" AND CREATETIME <= @EndDateValue" +
" ORDER BY CREATETIME";
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())
{
EtyDataLogDPLogTrend entity = new EtyDataLogDPLogTrend();
if (!drReader.IsDBNull(drReader.GetOrdinal("CREATETIME")))
{
DateTime dt;
if (DateTime.TryParse(drReader["CREATETIME"].ToString(), out dt))
{
entity.CreateTime = dt;
}
}
if (!drReader.IsDBNull(drReader.GetOrdinal("VALUE")))
{
entity.Value = Convert.ToDouble(drReader["VALUE"]);
}
res.Add(entity);
}
}
catch (System.Exception ex)
{
LogHelper.Error(CLASS_NAME, Function_Name, ex.ToString());
}
if (drReader != null)
{
drReader.Close();
drReader.Dispose();
//SimpleDatabase.GetInstance().CloseCurrentSession();
}
return(res);
}
/// <summary>
/// get train timetable data
/// </summary>
/// <param name="train id">train id</param>
/// <param name="isPlannedData">is planned data</param>
/// <returns>train time table</returns>
public List <TrainTimeTableData> GetTrainTimeTableData(bool isPlanned, int TrainServiceId, DateTime Date)
{
string FUNCTION_NAME = "GetTrainTimeTableData";
LogHelperCli.GetInstance().Log_Generic(CLASS_NAME + "." + FUNCTION_NAME, LogHelperCli.GetInstance().GetLineNumber(),
EDebugLevelManaged.DebugInfo, "Function Entered");
List <TrainTimeTableData> res;
res = GetCacheTrainTimeTableData(isPlanned, TrainServiceId);
if (res != null)
{
LogHelperCli.GetInstance().Log_Generic(CLASS_NAME + "." + FUNCTION_NAME, LogHelperCli.GetInstance().GetLineNumber(),
EDebugLevelManaged.DebugInfo, "Returning Cache Data");
return(res);
}
//if data returned is null, get data from database
res = new List <TrainTimeTableData>();
string localSQL;
if (isPlanned)
{
localSQL = " SELECT SERVICE_ID,ORDER_ID as STATION_ORDER_ID,A_TIME,D_TIME, DISPLAY_NAME " +
" FROM ATS_PLANNED_TRAIN_DATA, LOCATION WHERE " +
" SERVICE_ID = @TrainServiceId" +
" AND D_TIME >= @StartDateValue" +
" AND A_TIME <= @EndDateValue" +
" AND STATION_ID = PKEY" +
" AND STATION_ID NOT IN ( " + m_ExcludeStationList +
" ) ORDER BY SERVICE_ID, A_TIME";
}
else
{
localSQL = " SELECT SERVICE_ID,ORDER_ID as STATION_ORDER_ID,A_TIME,D_TIME, DISPLAY_NAME" +
" FROM ATS_PRACTICAL_TRAIN_DATA, LOCATION WHERE " +
" SERVICE_ID = @TrainServiceId" +
" AND D_TIME >= @StartDateValue" +
" AND A_TIME <= @EndDateValue" +
" AND STATION_ID = PKEY" +
" AND STATION_ID NOT IN ( " + m_ExcludeStationList +
" ) ORDER BY SERVICE_ID, A_TIME";
}
SqlParameter parameter1 = new SqlParameter();
parameter1.ParameterName = "TrainServiceId";
parameter1.DbType = DbType.Int32;
parameter1.Value = TrainServiceId;
parameter1.Direction = System.Data.ParameterDirection.Input;
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(parameter1);
SqlParameter parameter2 = new SqlParameter();
parameter2.ParameterName = "StartDateValue";
parameter2.DbType = DbType.DateTime;
parameter2.Value = Date;
parameter2.Direction = System.Data.ParameterDirection.Input;
parameters.Add(parameter2);
SqlParameter parameter3 = new SqlParameter();
parameter3.ParameterName = "EndDateValue";
parameter3.DbType = DbType.DateTime;
parameter3.Value = Date.AddHours(23).AddMinutes(59).AddSeconds(59);
parameter3.Direction = System.Data.ParameterDirection.Input;
parameters.Add(parameter3);
System.Data.IDataReader drReader = SimpleDatabase.GetInstance().ExecuteQueryWithParams(localSQL, parameters);
try
{
while (drReader != null && drReader.Read())
{
TrainTimeTableData timeTableData = new TrainTimeTableData();
if (!drReader.IsDBNull(drReader.GetOrdinal("A_TIME")))
{
DateTime dt;
if (DateTime.TryParse(drReader["A_TIME"].ToString(), out dt))
{
timeTableData.ArrTime = dt;
}
}
if (!drReader.IsDBNull(drReader.GetOrdinal("D_TIME")))
{
DateTime dt;
if (DateTime.TryParse(drReader["D_TIME"].ToString(), out dt))
{
timeTableData.DeptTime = dt;
}
}
if (!drReader.IsDBNull(drReader.GetOrdinal("SERVICE_ID")))
{
timeTableData.TrainServiceId = Convert.ToInt32(drReader["SERVICE_ID"]);
}
if (!drReader.IsDBNull(drReader.GetOrdinal("STATION_ORDER_ID")))
{
timeTableData.StationID = Convert.ToInt32(drReader["STATION_ORDER_ID"]);
}
if (!drReader.IsDBNull(drReader.GetOrdinal("DISPLAY_NAME")))
{
timeTableData.StationName = Convert.ToString(drReader["DISPLAY_NAME"]);
}
res.Add(timeTableData);
}
}
catch (System.Exception ex)
{
LogHelperCli.GetInstance().Log_Generic(CLASS_NAME + "." + FUNCTION_NAME, LogHelperCli.GetInstance().GetLineNumber(),
EDebugLevelManaged.DebugInfo, ex.ToString());
}
if (drReader != null)
{
drReader.Close();
drReader.Dispose();
//SimpleDatabase.GetInstance().CloseCurrentSession();
}
LogHelperCli.GetInstance().Log_Generic(CLASS_NAME + "." + FUNCTION_NAME, LogHelperCli.GetInstance().GetLineNumber(),
EDebugLevelManaged.DebugInfo, "Function Entered");
return(res);
}
/// <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 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);
}
public void Dispose()
{
_innerReader.Dispose();
}
