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