public static List <AWSFieldValue> GetLastAWSInfo(OBTArea area, TimeMode aType, OBTField dataField)
{
string wdf = null, wdd = null;
getQueryField(dataField, out wdf, out wdd);
string tableName = AWSItems.getTableName(DateTime.Now, area, aType, dataField);
DataTable lastTable = AWSItems.getLastAWStable(tableName);
List <AWSFieldValue> result = new List <AWSFieldValue>();
foreach (DataRow row in lastTable.Rows)
{
var df = row[wdf];
if (df != DBNull.Value)
{
var dd = row[wdd];
if (dd != DBNull.Value && (double)(decimal)df < 9999)
{
AWSFieldValue aws = new AWSFieldValue();
aws.TM = (DateTime)row["DDATETIME"];
aws.ID = row["OBTID"].ToString();
aws.V0 = (double)(decimal)df;
aws.V1 = (double)(decimal)dd;
result.Add(aws);
}
}
}
return(result);
}
/// <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> GetLastAWSInfo(OBTArea area, TimeMode aType, OBTField dataField, int accuracy)
{
string tableName = AWSItems.getTableName(DateTime.Now, area, aType, dataField);
DataTable lastTable = AWSItems.getLastAWStable(tableName);
List <AWSFieldValue> result = new List <AWSFieldValue>();
foreach (DataRow row in lastTable.Rows)
{
var v0 = row[dataField.ToString()];
if (v0 != DBNull.Value && (double)(decimal)v0 / accuracy < 9999)
{
AWSFieldValue aws = new AWSFieldValue();
aws.TM = (DateTime)row["DDATETIME"];
aws.ID = row["OBTID"].ToString();
aws.V0 = (double)(decimal)v0 / accuracy;
result.Add(aws);
}
}
return(result);
}
/// <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);
}
/// <summary>
/// 补充滑动雨量
/// </summary>
void addOldRain(DateTime date, OBTArea area, TimeMode aType, List <AWSFieldValue> target, OBTField dataField)
{
string tableName = AWSItems.getTableName(date, area, aType, dataField);
string seleceField = dataField.ToString();
int sliderRainMinut = 0;
if (dataField == OBTField.R06M)
{
sliderRainMinut = 6;
}
else if (dataField == OBTField.R12M)
{
sliderRainMinut = 12;
}
else if (dataField == OBTField.R30M)
{
sliderRainMinut = 30;
}
else if (dataField == OBTField.R01H)
{
sliderRainMinut = 60;
}
else if (dataField == OBTField.R02H)
{
sliderRainMinut = 120;
}
else if (dataField == OBTField.R03H)
{
sliderRainMinut = 180;
}
else if (dataField == OBTField.R06H)
{
sliderRainMinut = 360;
}
else if (dataField == OBTField.R12H)
{
sliderRainMinut = 60 * 12;
}
else if (dataField == OBTField.R24H)
{
sliderRainMinut = 60 * 24;
}
else if (dataField == OBTField.R48H)
{
sliderRainMinut = 60 * 48;
}
else if (dataField == OBTField.R72H)
{
sliderRainMinut = 60 * 72;
}
if (sliderRainMinut > 0)
{
StringBuilder sb = new StringBuilder();
int noDataCounts = 0;
sb.AppendFormat("select DDATETIME,OBTID,{0} from {1} where DDATETIME>:ddate0 and {2}>0 and {2}<99999 and DDATETIME<:ddate1 and OBTID in(", seleceField, tableName, seleceField);
foreach (var code in AWSCode.OBTCodeList)
{
if (!target.Exists(t => t.ID == code.Key))
{
if (noDataCounts > 200)
{
break;
}
if (noDataCounts++ > 0)
{
sb.Append(",");
}
sb.Append("'");
sb.Append(code.Key);
sb.Append("'");
}
}
sb.Append(") order by DDATETIME desc");
if (noDataCounts > 0)
{
using (IDataReader reader = OracleHelp.ExecuteReader(sb.ToString(), T_LOCALOBTMIND.Tunnel.connString, new OracleParameter(":ddate0", date.AddMinutes(-sliderRainMinut)), new OracleParameter(":ddate1", date)))
{
string obtid;
int maxRead = 5000;
while (reader.Read())
{
if (--maxRead == 0)
{
break;
}
obtid = reader[1].ToString();
if (!target.Exists(t => t.ID == obtid))
{
double value = (double)(decimal)reader[2];
target.Add(new AWSFieldValue()
{
ID = obtid, V0 = value, TM = (DateTime)reader[0]
});
if (--noDataCounts == 0)
{
break;
}
}
}
}
}
}
}
