private void AnalyzeMonthSpliteSql(DataQueryParamObject DataQueryParam, DataStoreTableInfo DataStoreItem)
{
DateTime dtStart = Convert.ToDateTime(DataQueryParam.StartDate);
DateTime dtEnd = Convert.ToDateTime(DataQueryParam.EndDate);
int Months = (dtEnd.Year - dtStart.Year) * 12 + (dtEnd.Month - dtStart.Month) + 1;
int StartMonth = dtStart.Month;
int currYear = dtStart.Year;
string QueryTableNamePrex = $"[datastore]-[{ DataQueryParam.DeviceCode}]-[{ DataQueryParam.TableName}]";
for (int i = 0; i < Months; i++)
{
StatQueryCondition tempQueryCondition = new StatQueryCondition();
string startDate;
string endDate;
string QueryTableNameSuffix = $"-{dtStart.Year}-{dtStart.ToString("MM")}";
string tempQueryTableName = QueryTableNamePrex + QueryTableNameSuffix;
tempQueryCondition.TableName = tempQueryTableName;
if (i == 0)
{
startDate = $"{dtStart.Year}-{dtStart.Month}-{dtStart.Day} {dtStart.Hour}:{dtStart.Minute}:{dtStart.Second}";
}
else
{
startDate = $"{dtStart.Year}-{dtStart.Month}-01 00:00:00";
}
if (i == (Months - 1))
{
endDate = $"{dtStart.Year}-{dtStart.Month}-{dtEnd.Day} {dtEnd.Hour}:{dtEnd.Minute}:{dtEnd.Second}";
}
else
{
DateTime d1 = new DateTime(dtStart.Year, dtStart.Month, 1);
DateTime d2 = d1.AddMonths(1).AddDays(-1);
endDate = $"{dtStart.Year}-{dtStart.Month}-{d2.Day} 23:59:59";
}
dtStart = dtStart.AddMonths(1);
tempQueryCondition.StartDate = DataQueryParam.StartDate;
tempQueryCondition.EndDate = DataQueryParam.EndDate;
//tempQueryCondition.SelectCondition = $"between '{startDate}' and '{endDate}'";
statConditionList.Add(tempQueryCondition);
}
return;
}
private void AnalyzeDaySpliteSql(DataStatParamInfo DataQueryParam, DataStoreTableInfo DataStoreItem)
{
DateTime dtStart = Convert.ToDateTime(DataQueryParam.StartDate);
DateTime dtEnd = Convert.ToDateTime(DataQueryParam.EndDate);
DateTime d3 = Convert.ToDateTime(string.Format("{0}-{1}-{2}", dtStart.Year, dtStart.Month, dtStart.Day));
DateTime d4 = Convert.ToDateTime(string.Format("{0}-{1}-{2}", dtEnd.Year, dtEnd.Month, dtEnd.Day));
int days = (d4 - d3).Days;
int StartMonth = dtStart.Month;
int currYear = dtStart.Year;
string QueryTableNamePrex = $"[datastore]-[{ DataQueryParam.DeviceCode}]-[{ DataQueryParam.TableName}]";
for (int i = 0; i < days; i++)
{
StatQueryCondition tempQueryCondition = new StatQueryCondition();
string startDate;
string endDate;
if (i == 0)
{
startDate = $"{dtStart.Year}-{dtStart.Month}-{dtStart.Day} {dtStart.Hour}:{dtStart.Minute}:{dtStart.Second}";
}
else
{
startDate = $"{dtStart.Year}-{dtStart.Month}-{dtStart.Day} 00:00:00";
}
if (i == (days - 1))
{
endDate = $"{dtEnd.Year}-{dtEnd.Month}-{dtEnd.Day} {dtEnd.Hour}:{dtEnd.Minute}:{dtEnd.Second}";
}
else
{
endDate = $"{dtStart.Year}-{dtStart.Month}-{dtStart.Day} 23:59:59";
}
string QueryTableNameSuffix = $"-{dtStart.Year}-{dtStart.Month}-{dtStart.Day}";
string tempQueryTableName = QueryTableNamePrex + QueryTableNameSuffix;
tempQueryCondition.StartDate = startDate;
tempQueryCondition.EndDate = endDate;
//tempQueryCondition.SelectCondition = $"between '{startDate}'and '{endDate}'";
tempQueryCondition.TableName = tempQueryTableName;
dtStart.AddDays(1);
}
return;
}
private void AnalyzeDefaultSpliteSql(DataQueryParamObject DataQueryParam, DataStoreTableInfo DataStoreItem)
{
DateTime dtStart = Convert.ToDateTime(DataQueryParam.StartDate);
DateTime dtEnd = Convert.ToDateTime(DataQueryParam.EndDate);
StatQueryCondition tempQueryCondition = new StatQueryCondition();
tempQueryCondition.TableName = $"[datastore]-[{ DataQueryParam.DeviceCode}]-[{ DataQueryParam.TableName}]";
//tempQueryCondition.SelectCondition = $"between '{DataQueryParam.StartDate}' and '{DataQueryParam.EndDate}'";
tempQueryCondition.StartDate = DataQueryParam.StartDate;
tempQueryCondition.EndDate = DataQueryParam.EndDate;
statConditionList.Add(tempQueryCondition);
return;
}
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);
}
