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); }