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