public static DataTable getLastAWStable(string tableName, bool writeCachable = false)
{
lock (lockKey)
{
string ckey = "lastAWSiNFO#" + tableName;
DataTable table = MyCacheManager.Get(ckey) as DataTable;
if (table == null)
{
string select = string.Format(@"select * from(
SELECT ROW_NUMBER() OVER(PARTITION BY obtid ORDER BY ddatetime DESC) LEV,s.* from( select * from (
select * from {0} where WD3SMAXDF>=0 ORDER BY ddatetime desc
) where ROWNUM < 5000 ) s
) WHERE LEV=1", tableName);
table = OracleHelp.ExecuteDataTable(select, T_LOCALOBTDAYD.Tunnel.connString);
if (writeCachable)
{
MyCacheManager.Insert(ckey, table, DateTime.Now.AddMinutes(1), System.Web.Caching.CacheItemPriority.High, null, (string key, object value, CacheItemRemovedReason reason) =>
{
if (reason == CacheItemRemovedReason.Expired)
{
getLastAWStable(key.Split('#')[1], true);
}
});
}
}
return(table);
}
}
public static string GetHistory(string obtid, DateTime start, DateTime current, TimeMode timeMode, OBTField[] dataField, OBTField keyField)
{
OBTCODE obt = AWSCode.OBTCodeList[obtid];
StringBuilder sb = new StringBuilder();
string tableName = getTableName(start, obt.AREA, timeMode, keyField);
string selectField;
for (int i = 0; i < dataField.Length; i++)
{
if (i > 0)
{
sb.Append(",");
}
sb.Append(dataField[i].ToString());
}
selectField = sb.ToString();
sb.Clear();
sb.AppendFormat("select {0} from {1} where obtid=:obtId AND DDATETIME >= :dstart AND DDATETIME <= :dend and {2} is not null ORDER BY DDATETIME", selectField, tableName, keyField.ToString());
DataTable data = OracleHelp.ExecuteDataTable(sb.ToString(), T_LOCALOBTMIND.Tunnel.connString, new OracleParameter(":obtId", obtid), new OracleParameter(":dstart", start), new OracleParameter(":dend", current));
sb.Clear();
sb.Append("[");
int itemIndex;
int rows = 0;
foreach (DataRow row in data.Rows)
{
if (rows++ > 0)
{
sb.Append(",");
}
itemIndex = 0;
sb.Append("[");
foreach (var item in row.ItemArray)
{
if (itemIndex++ > 0)
{
sb.Append(",");
}
if (item is DateTime)
{
sb.Append("\"");
sb.Append(Utility.DateTimeToJson((DateTime)item));
sb.Append("\"");
}
else
{
sb.Append(item);
}
}
sb.Append("]");
}
sb.Append("]");
return(sb.ToString());
}
/// <summary>
/// 历史差变查询
/// </summary>
public static string GetHistory(string obtid, DateTime current, OBTField fieldName)
{
int offsetHours;
string field = getFieldName(fieldName, out offsetHours);
if (offsetHours == 0)
{
return(null);
}
DateTime start = current.AddHours(offsetHours);
OBTCODE obt = AWSCode.OBTCodeList[obtid];
string tableName = AWSItems.getTableName(start, obt.AREA, TimeMode.MINUTE);
StringBuilder sb = new StringBuilder();
sb.AppendFormat("select DDATETIME,{0} from {1} where obtid=:obtId AND DDATETIME >= :dstart AND DDATETIME<=:dend and {2} is not null ORDER BY DDATETIME", field, tableName, field);
DataTable data = OracleHelp.ExecuteDataTable(sb.ToString(), T_LOCALOBTMIND.Tunnel.connString, new OracleParameter(":obtId", obtid), new OracleParameter(":dstart", start), new OracleParameter(":dend", current));
List <AWSOffsetRow> offsetData = new List <AWSOffsetRow>();
foreach (DataRow row in data.Rows)
{
offsetData.Add(new AWSOffsetRow()
{
DDATETIME = (DateTime)row[0], VALUE = (decimal)row[1]
});
}
sb.Clear();
sb.Append("[");
if (offsetData.Count > 0)
{
int rows = 0;
decimal firstValue = offsetData[0].VALUE;
for (int i = 0; i < offsetData.Count; i++)
{
if (rows++ > 0)
{
sb.Append(",");
}
sb.Append("[");
sb.Append("\"");
sb.Append(Utility.DateTimeToJson(offsetData[i].DDATETIME));
sb.Append("\",");
sb.Append(offsetData[i].VALUE - firstValue);
sb.Append("]");
}
}
sb.Append("]");
return(sb.ToString());
}
/// <summary>
/// 自动站属性变值
/// </summary>
List <AWSFieldValue> GetOffsetFromDB(DateTime?date, OBTArea area, OBTField fieldName, int accuracy)
{
int offsetHours;
string field = getFieldName(fieldName, out offsetHours);
if (offsetHours == 0)
{
return(null);
}
DateTime checkTime;
if (date == null)
{
checkTime = DateTime.Now.AddMinutes(-5);
checkTime = checkTime.AddMinutes(-checkTime.Minute % 5);
checkTime = checkTime.AddSeconds(-checkTime.Second);
}
else
{
checkTime = date.Value;
}
List <AWSFieldValue> result = new List <AWSFieldValue>();
string tableName = AWSItems.getTableName(checkTime, area, TimeMode.MINUTE, fieldName);
StringBuilder sb = new StringBuilder();
sb.AppendFormat(@"SELECT A.obtid, (A.{0}-B.{1})/" + accuracy + " FROM (select obtid,{2} from {3} where ddatetime=:dnow and {4} is not null) A inner join(select obtid, {5} from {6} where ddatetime = :dbefore and {7} is not null) B ON A.obtid = B.obtid",
field, field, field, tableName, field, field, tableName, field);
DataTable data = OracleHelp.ExecuteDataTable(sb.ToString(), T_LOCALOBTMIND.Tunnel.connString, new OracleParameter(":dnow", checkTime), new OracleParameter(":dbefore", checkTime.AddHours(offsetHours)));
foreach (DataRow row in data.Rows)
{
if ((double)(decimal)row[1] < 9999)
{
result.Add(new AWSFieldValue()
{
TM = checkTime, ID = row[0].ToString(), V0 = (double)(decimal)row[1]
});
}
}
return(result);
}
List <AWSFieldValue> GetAWSInfoFromDB(DateTime date, OBTArea area, TimeMode aType, OBTField dataField, int accuracy)
{
List <AWSFieldValue> result = new List <AWSFieldValue>();
string tableName = AWSItems.getTableName(date, area, aType, dataField);
if (aType == TimeMode.DAY)
{
date = date.Date;
}
string seleceField = dataField.ToString();
string sql = string.Format("select OBTID,{0}/" + accuracy + " from {1} where DDATETIME=:ddate and {2} is not null", seleceField, tableName, seleceField, seleceField);
DataTable data = OracleHelp.ExecuteDataTable(sql, T_LOCALOBTMIND.Tunnel.connString, new OracleParameter(":ddate", date));
foreach (DataRow row in data.Rows)
{
result.Add(new AWSFieldValue()
{
ID = row[0].ToString(), V0 = (double)(decimal)row[1]
});
}
return(result);
}
/// <summary>
/// 风速
/// </summary>
/// <param name="date"></param>
/// <returns></returns>
public static List <AWSFieldValue> GetAWSWindFromDB(DateTime date, OBTArea area, TimeMode aType, OBTField dataField)
{
string wdf = null, wdd = null;
getQueryField(dataField, out wdf, out wdd);
string tableName = AWSItems.getTableName(date, area, aType);
List <AWSFieldValue> result = new List <AWSFieldValue>();
string selectSQL = string.Format("select OBTID,{0},{1} from {2} where DDATETIME=:ddate AND {3}>=0 AND {4}>=0 ORDER BY {5} DESC", wdf, wdd, tableName, wdf, wdd, wdf);
DataTable data = OracleHelp.ExecuteDataTable(selectSQL, T_LOCALOBTMIND.Tunnel.connString, new OracleParameter(":ddate", aType == TimeMode.DAY ? date.Date : date));
foreach (DataRow row in data.Rows)
{
if ((double)(decimal)row[1] < 9999)
{
result.Add(new AWSFieldValue()
{
ID = row[0].ToString(), V0 = (double)(decimal)row[1], V1 = (double)(decimal)row[2]
});
}
}
return(result);
}
/// <summary>
/// 自动站变温
/// </summary>
public static Dictionary <string, decimal> GetOffsetFromDB(DateTime date, OBTArea area, OBTField fieldName)
{
int offsetHours;
string field = getFieldName(fieldName, out offsetHours);
if (offsetHours == 0)
{
return(null);
}
Dictionary <string, decimal> result = new Dictionary <string, decimal>();
string tableName = AWSItems.getTableName(date, area, TimeMode.MINUTE);
StringBuilder sb = new StringBuilder();
sb.AppendFormat(@"SELECT A.obtid, A.{0}-B.{1} FROM (select obtid,{2} from {3} where ddatetime=:dnow and {4} is not null) A inner join(select obtid, {5} from {6} where ddatetime = :dbefore and {7} is not null) B ON A.obtid = B.obtid",
field, field, field, tableName, field, field, tableName, field);
DataTable data = OracleHelp.ExecuteDataTable(sb.ToString(), T_LOCALOBTMIND.Tunnel.connString, new OracleParameter(":dnow", date), new OracleParameter(":dbefore", date.AddHours(offsetHours)));
foreach (DataRow row in data.Rows)
{
result.Add(row[0].ToString(), (decimal)row[1]);
}
return(result);
}