private long GetQueryRecordsetCount()
{
long RecordsetCount = 0;
if (mysqlCnn == null)
{
LoggerManager.Log.Info("没有从MySql连接池获取连接对象！\n");
return(-1);
}
for (int i = 0; i < queryConditionList.Count; i++)
{
string cmdStr = $"select COUNT(*) from `{queryConditionList[i].TableName}` where `DataTime` {queryConditionList[i].SelectCondition}";
long tempCount = 0;
MySqlDataReader dataReader = mysqlCnn.ExecuteReader(CommandType.Text, cmdStr, null);
if (dataReader == null)
{
continue;
}
while (dataReader.Read())
{
tempCount = dataReader.GetUInt32(0);
}
dataReader.Close();
queryConditionList[i].offset = RecordsetCount;
queryConditionList[i].count = tempCount;
RecordsetCount += tempCount;
}
return(RecordsetCount);
}
private List <RecordsetDataStat> GetQueryRecordset()
{
List <RecordsetDataStat> tableData = new List <RecordsetDataStat>();
string statSqlPrefix = "";
string statSqlSuffix = "";
int RecordsetCount = 0;
long pageSize = dataStatParam.PageSize;
if (mysqlCnn == null)
{
LoggerManager.Log.Info("MySql连接对象为空！\n");
return(null);
}
switch (dataStatParam.StatType)
{
case STAT_TYPE.STAT_YEAR:
statSqlPrefix = $"select year(DataTime) AS 'Year',FieldValue ";
statSqlSuffix = "GROUP by YEAR(DataTime)";
break;
case STAT_TYPE.STAT_MONTH:
statSqlPrefix = $"select year(DataTime) AS 'Year',month(DataTime) AS 'Month',FieldValue ";
statSqlSuffix = "GROUP by YEAR(DataTime),MONTH(DataTime)";
break;
case STAT_TYPE.STAT_QUARTER:
statSqlPrefix = $"select year(DataTime) AS 'Year',quarter(DataTime) AS 'Quarter',FieldValue ";
statSqlSuffix = "GROUP by YEAR(DataTime),QUARTER(DataTime)";
break;
case STAT_TYPE.STAT_WEEK:
statSqlPrefix = $"select year(DataTime) AS 'Year',month(DataTime) AS 'Month',week(DataTime) AS 'Week',FieldValue ";
statSqlSuffix = "GROUP by YEAR(DataTime),MONTH(DataTime),WEEK(DataTime)";
break;
case STAT_TYPE.STAT_DAY:
statSqlPrefix = $"select year(DataTime) AS 'Year',month(DataTime) AS 'Month',day(DataTime) AS 'Day',FieldValue ";
statSqlSuffix = "GROUP by YEAR(DataTime),MONTH(DataTime),DAY(DataTime)";
break;
case STAT_TYPE.STAT_HOUR:
statSqlPrefix = $"select year(DataTime) AS 'Year',month(DataTime) AS 'Month',day(DataTime) AS 'Day',hour(DataTime) AS 'Hour',FieldValue ";
statSqlSuffix = "GROUP by YEAR(DataTime),MONTH(DataTime),DAY(DataTime),HOUR(DataTime)";
break;
}
for (int i = 0; i < statConditionList.Count; i++)
{
StatQueryCondition tempStatCondition = statConditionList[i];
string cmdStr = null;
//if (i == statConditionList.Count-1)
//{
// DateTime dtEnd = Convert.ToDateTime(tempStatCondition.EndDate);
// dtEnd = dtEnd.AddHours(1);
// tempStatCondition.EndDate = $"{dtEnd.Year}-{dtEnd.Month}-{dtEnd.Day} {dtEnd.Hour}:{dtEnd.Minute}:{dtEnd.Second}"; ;
//}
cmdStr = statSqlPrefix + $"from `{tempStatCondition.TableName}` where `DataTime` ";
cmdStr += $"between '{tempStatCondition.StartDate}' and '{tempStatCondition.EndDate}' " + statSqlSuffix;
//switch (dataStatParam.StatType)
//{
// case STAT_TYPE.STAT_YEAR:
// cmdStr = $"select year(DataTime) AS 'Year',FieldValue from `{tempStatCondition.TableName}` where `DataTime` ";
// cmdStr += $"between '{tempStatCondition.StartDate}' and '{tempStatCondition.EndDate}'";
// cmdStr += $"Order by YEAR(DataTime)";
// break;
// case STAT_TYPE.STAT_MONTH:
// cmdStr = $"select year(DataTime) AS 'Year',month(DataTime) AS 'Month',FieldValue from `{tempStatCondition.TableName}` where `DataTime` ";
// cmdStr += $"between '{tempStatCondition.StartDate}' and '{tempStatCondition.EndDate}'";
// cmdStr += $"GROUP by YEAR(DataTime),MONTH(DataTime)";
// break;
// case STAT_TYPE.STAT_QUARTER:
// cmdStr = $"select year(DataTime) AS 'Year',quarter(DataTime) AS 'Quarter',FieldValue from `{tempStatCondition.TableName}` where `DataTime` ";
// cmdStr += $"between '{tempStatCondition.StartDate}' and '{tempStatCondition.EndDate}'";
// cmdStr += $"GROUP by YEAR(DataTime),QUARTER(DataTime)";
// break;
// case STAT_TYPE.STAT_WEEK:
// cmdStr = $"select year(DataTime) AS 'Year',month(DataTime) AS 'Month',week(DataTime) AS 'Week',FieldValue from `{tempStatCondition.TableName}` where `DataTime` ";
// cmdStr += $"between '{tempStatCondition.StartDate}' and '{tempStatCondition.EndDate}'";
// cmdStr += $"GROUP by YEAR(DataTime),MONTH(DataTime),WEEK(DataTime)";
// break;
// case STAT_TYPE.STAT_DAY:
// cmdStr = $"select year(DataTime) AS 'Year',month(DataTime) AS 'Month',day(DataTime) AS 'Day',FieldValue from `{tempStatCondition.TableName}` where `DataTime` ";
// cmdStr += $"between '{tempStatCondition.StartDate}' and '{tempStatCondition.EndDate}'";
// cmdStr += $"GROUP by YEAR(DataTime),MONTH(DataTime),DAY(DataTime)";
// break;
// case STAT_TYPE.STAT_HOUR:
// cmdStr = $"select year(DataTime) AS 'Year',month(DataTime) AS 'Month',day(DataTime) AS 'Day',hour(DataTime) AS 'Hour',FieldValue from `{tempStatCondition.TableName}` where `DataTime` ";
// cmdStr += $"between '{tempStatCondition.StartDate}' and '{tempStatCondition.EndDate}'";
// cmdStr += $"GROUP by YEAR(DataTime),MONTH(DataTime),DAY(DataTime),HOUR(DataTime)";
// break;
//}
MySqlDataReader dataReader = mysqlCnn.ExecuteReader(CommandType.Text, cmdStr, null);
while (dataReader.Read())
{
RecordsetDataStat newRecordsetData = GetStatRecordData(dataReader);
//newRecordsetData.DataTime = dataReader["DataTime"] == null ? "" : ((DateTime)dataReader["DataTime"]).ToString("yyyy-MM-dd hh:mm:ss");
////newRecordsetData.FieldValue = dataReader["FieldValue"] == null ? "" : dataReader["FieldValue"].ToString();
//string tempFieldValue = dataReader["FieldValue"] == null ? "" : dataReader["FieldValue"].ToString();
//string[] arrayFieldString = tempFieldValue.Split(',');
//float[] arrayFieldValue = new float[arrayFieldString.Length];
//for (int fieldIndex = 0; fieldIndex < arrayFieldValue.Length; fieldIndex++)
//{
// string tempValue = arrayFieldString[fieldIndex].Trim();
// if (string.IsNullOrEmpty(tempValue))
// arrayFieldValue[fieldIndex] = 0;
// else
// {
// try
// {
// arrayFieldValue[fieldIndex] = float.Parse(tempValue);
// }
// catch (Exception ex)
// {
// arrayFieldValue[fieldIndex] = 0;
// }
// }
//}
//newRecordsetData.FieldValue = arrayFieldValue;
if (RecordsetCount > 1)
{
float[] arrayFieldValue = tableData[RecordsetCount - 1].FieldValue;
for (int fileIndex = 0; fileIndex < arrayFieldValue.Length; fileIndex++)
{
if (newRecordsetData.FieldValue[fileIndex] >= arrayFieldValue[fileIndex])
{
arrayFieldValue[fileIndex] = newRecordsetData.FieldValue[fileIndex] - arrayFieldValue[fileIndex];
}
else
{
LoggerManager.Log.Error($"Company<{dataStatParam.CompanyCode}>,Device <{ dataStatParam.DeviceCode}>,Table <{ dataStatParam.TableName}> 数据统计错误！");
}
}
}
tableData.Add(newRecordsetData);
RecordsetCount++;
}
dataReader.Close();
//获取统计的最后一条数据记录
if (i == statConditionList.Count - 1)
{
string lastRecordSqlStr = statSqlPrefix + $" from `{tempStatCondition.TableName}` where `DataTime` between '{tempStatCondition.StartDate}' and '{tempStatCondition.EndDate}' "; lastRecordSqlStr += "ORDER BY `DataTime` DESC LIMIT 1";
MySqlDataReader lastDataReader = mysqlCnn.ExecuteReader(CommandType.Text, lastRecordSqlStr, null);
RecordsetDataStat lastRecordsetData = null;
while (lastDataReader.Read())
{
lastRecordsetData = GetStatRecordData(lastDataReader);
}
lastDataReader.Close();
float[] arrayFieldValue = tableData[RecordsetCount - 1].FieldValue;
for (int fileIndex = 0; fileIndex < arrayFieldValue.Length; fileIndex++)
{
if (lastRecordsetData.FieldValue[fileIndex] >= arrayFieldValue[fileIndex])
{
arrayFieldValue[fileIndex] = lastRecordsetData.FieldValue[fileIndex] - arrayFieldValue[fileIndex];
}
else
{
LoggerManager.Log.Error($"Company<{dataStatParam.CompanyCode}>,Device <{ dataStatParam.DeviceCode}>,Table <{ dataStatParam.TableName}> 数据统计错误！");
}
}
}
}
return(tableData);
}
