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(DataQueryParamObject 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++) { QueryCondition tempQueryCondition = new QueryCondition(); 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.SelectCondition = $"between '{startDate}'and '{endDate}'"; tempQueryCondition.TableName = tempQueryTableName; dtStart.AddDays(1); } return; }
public void InitQueryParams(DataQueryParamObject DataQueryParam, DataStoreTableInfo DataStoreItem) { int res = 0; QueryResultBase QueryResultObject = new QueryResultBase(); dataQueryParam = DataQueryParam; dataStoreItem = DataStoreItem; mysqlCnn = MySqlConnectPoolHelper.getPool().getConnection(); String DbName = $"`[iot]-[{DataQueryParam.CompanyCode}]`"; res = mysqlCnn.SelectDB(DbName); }
private void AnalyzeDefaultSpliteSql(DataQueryParamObject DataQueryParam, DataStoreTableInfo DataStoreItem) { DateTime dtStart = Convert.ToDateTime(DataQueryParam.StartDate); DateTime dtEnd = Convert.ToDateTime(DataQueryParam.EndDate); QueryCondition tempQueryCondition = new QueryCondition(); tempQueryCondition.TableName = $"[datastore]-[{ DataQueryParam.DeviceCode}]-[{ DataQueryParam.TableName}]"; tempQueryCondition.SelectCondition = $"between '{DataQueryParam.StartDate}' and '{DataQueryParam.EndDate}'"; queryConditionList.Add(tempQueryCondition); return; }
public HttpResponseMessage QueryHistoryDataExtend([FromBody] DataQueryParamObject QueryParams) { //DataQueryParamObject queryParam = null; QueryResultBase queryResult = null; if (QueryParams == null) { queryResult = new QueryResultBase(); queryResult.ResultCode = QueryResultCodeEnum.QUERY_ERROR_PARAM; return(HttpResponseExtension.toJson(JsonConvert.SerializeObject(queryResult))); } IotDataQueryInterface dataQueryInterfaceObject = new IotDataQueryInterface(); queryResult = dataQueryInterfaceObject.HandleQueryInterfaceExtend(QueryParams); return(HttpResponseExtension.toJson(JsonConvert.SerializeObject(queryResult))); }
public string[] GetQuerySqlList(DataQueryParamObject DataQueryParam, DataStoreConfig DataStoreItem) { string[] querySqlList = null; switch (DataStoreItem.SpliteTableType) { case SPLITE_TABLE_TYPE.SPLITE_DEFAULT: break; case SPLITE_TABLE_TYPE.SPLITE_MONTH: break; case SPLITE_TABLE_TYPE.SPLITE_YEAR: break; default: break; } return(querySqlList); }
public List <QueryCondition> GetQuerySqlList(DataQueryParamObject DataQueryParam, DataStoreTableInfo DataStoreItem) { switch (DataStoreItem.SpliteTableType) { case SPLITE_TABLE_TYPE.SPLITE_DEFAULT: AnalyzeDefaultSpliteSql(DataQueryParam, DataStoreItem); break; case SPLITE_TABLE_TYPE.SPLITE_MONTH: AnalyzeMonthSpliteSql(DataQueryParam, DataStoreItem); break; case SPLITE_TABLE_TYPE.SPLITE_YEAR: break; default: break; } return(queryConditionList); }
public QueryResultBase HandleQueryInterfaceExtend(DataQueryParamObject dataQueryParam) { QueryResultBase QueryResultObject = null; DataStoreTableInfo DataStoreConfigItem = CompanyManagerHelper.GetDeviceDataStoreConfigItem( dataQueryParam.CompanyCode, dataQueryParam.DeviceCode, dataQueryParam.TableName); if (DataStoreConfigItem == null) { QueryResultObject = new QueryResultBase(); QueryResultObject.ResultCode = QueryResultCodeEnum.QUERY_ERROR_NO_DATA_STORE_CONFIG; QueryResultObject.QueryData = null; return(QueryResultObject); } else { switch (dataQueryParam.DataQueryType) { case DATA_QUERY_TYPE.HISTORY_QUERY: QueryAlgorithm HistoryQueryObject = new QueryAlgorithm(); HistoryQueryObject.InitQueryParams(dataQueryParam, DataStoreConfigItem); QueryResultObject = HistoryQueryObject.GetQueryRecordsetData(true); HistoryQueryObject.ClearMySQLCnn(); break; default: break; } } return(QueryResultObject); }
private string[] AnalyzeMonthSpliteSql(DataQueryParamObject DataQueryParam, DataStoreConfig DataStoreItem) { string[] querySqlList = null; List <QueryCondition> queryConditionList = new List <QueryCondition>(); DateTime dtStart = Convert.ToDateTime(DataQueryParam.StartDate); DateTime dtEnd = Convert.ToDateTime(DataQueryParam.EndDate); int Months = (dtStart.Year - dtEnd.Year) * 12 + (dtStart.Month - dtEnd.Month) + 1; int StartMonth = dtStart.Month; int currYear = dtStart.Year; string QueryTableNamePrex = $"[datastore]-[{ DataStoreItem.DeviceCode}]-[{ DataStoreItem.DefaultTableName}]"; for (int i = 0; i < Months; i++) { QueryCondition tempQueryCondition = new QueryCondition(); string startDate; string endDate; int currMonth = StartMonth + i; if (currMonth > 12) { currYear++; currMonth = 1; } string QueryTableNameSuffix = $"-{currYear}-{currMonth}"; string tempQueryTableName = QueryTableNamePrex + QueryTableNameSuffix; tempQueryCondition.TableName = tempQueryTableName; if (i == 0) { startDate = $"{currYear}-{currMonth}-{dtStart.Day} {dtStart.Hour}:{dtStart.Minute}:{dtStart.Second}"; } else { startDate = $"{currYear}-{currMonth}-{dtStart.Day} 00:00:00"; } if (i == (Months - 1)) { endDate = $"{currYear}-{currMonth}-{dtEnd.Day} {dtEnd.Hour}:{dtEnd.Minute}:{dtEnd.Second}"; } else { DateTime d1 = new DateTime(currYear, currMonth, 1); DateTime d2 = d1.AddMonths(1).AddDays(-1); endDate = $"{currYear}-{currMonth}-{d2.Day} 23:59:59"; } tempQueryCondition.SelectCondition = $"between '{startDate} 'and '{endDate}'"; queryConditionList.Add(tempQueryCondition); } return(querySqlList); }
private string[] AnalyzeYearSpliteSql(DataQueryParamObject DataQueryParam, DataStoreConfig DataStoreItem) { string[] querySqlList = null; return(querySqlList); }