public async Task <Result <Groups> > List(BasicQuery query)
{
string route = $"{GetUrl("list")}{query.ToQueryString()}";
var response = await _restClientService.Get <Groups>(route);
return(ServiceHelper.MapResponse(response));
}
/// <summary>
/// 根据查询条件获取数据。
/// </summary>
/// <param name="basicQuery"></param>
/// <returns></returns>
public ResultCompare GetDateTongJiData(BasicQuery basicQuery)
{
var resultList = new ResultView.ResultCompare
{
ObjectName = new List <string>(),
Enery = new Dictionary <string, List <decimal> >(),
Dept = new List <string>()
};
List <Model.BaseLayerObject> objectList;
if (basicQuery.AreaType == AreaType.Area)
{
objectList = new BLL.BaseLayerObject().GetBaseLayerObjectList(
string.Format(" and layerobjectid = {0}", basicQuery.ObjectNum), "");
}
else
{
objectList = new BLL.BaseLayerObject().GetBaseFuncLayerObjectList(
string.Format(" and layerobjectid = {0}", basicQuery.ObjectNum), "");
}
EnergyContrast contst = new EnergyContrast();
var result = contst.GetQueryLineChart(basicQuery);
resultList.ObjectName = result.ObjectName;
if (basicQuery.ItemCode != "00000")
{
var itemList = new BLL.Itemcode().GetItemcodeList(" and ItemCodeNumber='" + basicQuery.ItemCode + "'", " order by ItemcodeID")[0];
resultList.Enery.Add(objectList[0].LayerObjectName, result.Enery[itemList.ItemCodeName]);
for (int i = 0; i < result.Dept.Count; i++)
{
resultList.Dept.Add(itemList.Unit);
}
}
else
{
var tempValue = new decimal[result.Enery[result.Enery.Keys.First <string>()].Count];
foreach (var re in result.Enery)
{
for (var i = 0; i < re.Value.Count; i++)
{
tempValue[i] += re.Value[i];
}
}
for (int i = 0; i < result.Dept.Count; i++)
{
resultList.Dept.Add("T");
}
resultList.Enery.Add(objectList[0].LayerObjectName, tempValue.ToList());
}
return(resultList);
}
public List <Record> Query(BasicQuery query)
{
if (!IsSafe(query.RecordType))
{
throw new NotSupportedException($"Query target name {query.RecordType} is unsafe");
}
foreach (var column in query.Columns)
{
if (!IsSafe(column))
{
throw new NotSupportedException($"Query column name {column} is unsafe");
}
}
foreach (var filter in query.Filters)
{
if (!IsSafe(filter.Column))
{
throw new NotSupportedException($"Query filter column name {filter} is unsafe");
}
}
foreach (var order in query.Orderings)
{
if (!IsSafe(order.Column))
{
throw new NotSupportedException($"Ordering column name {order.Column} is unsafe");
}
}
List <Record> output = new List <Record>();
NpgsqlCommand command = CreateCommandForQuery(query);
NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
output.Add(this.ReadToRecord(reader, EnsureIdColumn(query.Columns, query.RecordType), query.RecordType));
}
return(output);
}
public async Task <BasicQueryResult <RoleListDvo> > Load([FromQuery] BasicQuery query)
{
var result = appDbContext.Roles.AsQueryable().Take(query.PageSize).Skip(query.PageIndex * query.PageSize).ToList();
foreach (var item in result)
{
var menu = item.Menus.Where(menu => menu.Id == 1).FirstOrDefault();
item.Menus = new List <Menu> {
};
if (menu != null)
{
item.Menus.Add(menu);
}
}
var count = appDbContext.Roles.Count();
var data = mapper.Map <List <Role>, List <RoleListDvo> >(result);
return(new BasicQueryResult <RoleListDvo>()
{
Data = data, Total = count
});
}
/// <inheritdoc />
public async Task <QueryResult <MovieResponseModel> > GetMoviesGridAsync(BasicQuery request, CancellationToken cancellationToken)
{
var query = _context.MovieItems.Select(
x => new MovieResponseModel
{
Id = x.Id,
Title = x.Title,
Description = x.Description,
ReleaseDate = x.ReleaseDate
});
if (request.Filters != null)
{
query = ApplyFilters(request.Filters, query);
}
var totalCount = await query.CountAsync(cancellationToken);
if (request.Paging != null)
{
query = ApplyPagination(request.Paging, query);
}
if (request.Sort != null)
{
query = ApplySorting(request.Sort, query);
}
var data = await query.ToListAsync(cancellationToken);
var queryResult = new QueryResult <MovieResponseModel>
{
TotalCount = totalCount,
Data = data,
TotalPages = CountTotalPages(totalCount, request.Paging.PageSize)
};
return(queryResult);
}
public HttpResponseMessage SearchItem([FromUri] string requestedCollectionUri, [FromUri] string requestedProject, [FromUri] int count, [FromBody] BasicQuery query)
{
string type = Utils.GetTFSType(query.type);
string filter = query.query;
Uri collectionUri = new Uri(requestedCollectionUri);
TfsTeamProjectCollection tpc = new TfsTeamProjectCollection(collectionUri, new NetworkCredential(username, password, domain));
WorkItemStore workItemStore = tpc.GetService <WorkItemStore>();
WorkItemCollection queryResults = workItemStore.Query(
"Select [Title], [State], [Id], [Stack Rank] " +
"From Workitems " +
"Where [Work Item Type] = '" + type + "' and [State] != 'Removed' and [State] != 'Closed' and " +
"[State] != 'Resolved' and [Area Path] under 'RD\\Azure App Plat\\Logic Apps' and [Title] contains '" + filter + "' " +
"Order By [Stack Rank] Asc"
);
var response = (from WorkItem r in queryResults select new SimpleWorkItem {
Title = r.Title,
State = r.State,
id = r.Id,
AreaPath = r.AreaPath,
IterationPath = r.IterationPath,
Type = r.Type.Name
}).Take(count);
return(Request.CreateResponse(HttpStatusCode.OK, response));
}
public ResultBase Execute(CommandBase command)
{
data.Open();
ResultBase output = null;
switch (command)
{
case CreateRecordCommand _:
{
CreateRecordCommand createCommand = (CreateRecordCommand)command;
RecordType type = data.GetRecordType(createCommand.Target.Type);
List <string> sourceFieldNames = type.Fields.Select(t => t.Name).ToList();
foreach (string field in createCommand.Target.Data.Keys)
{
if (!sourceFieldNames.Contains(field))
{
throw new MissingFieldException($"Record with type {createCommand.Target.Type} doesn't have the column {field}");
}
}
if (createCommand.Target.Id == Guid.Empty)
{
createCommand.Target.Id = Guid.NewGuid();
}
createCommand.Target["createdon"] = DateTime.Now;
createCommand.Target["modifiedon"] = DateTime.Now;
Guid result = this.data.CreateRecord(createCommand.Target);
output = new CreateRecordResult()
{
RecordId = result
};
break;
}
case RetrieveRecordCommand _:
{
RetrieveRecordCommand retrieveCommand = (RetrieveRecordCommand)command;
RecordType type = data.GetRecordType(retrieveCommand.Type);
List <string> fieldsToGet;
if (retrieveCommand.AllFields)
{
fieldsToGet = EnsureIdColumn(type.FieldNames, retrieveCommand.Type);
}
else
{
string badFieldName = retrieveCommand.Fields.FirstOrDefault(f => !type.FieldNames.Contains(f));
if (badFieldName != null)
{
throw new Exception($"Record Type {retrieveCommand.Type} doesn't contain the field {badFieldName}");
}
fieldsToGet = retrieveCommand.Fields;
}
output = new RetrieveRecordResult()
{
Result = this.data.RetrieveRecord(retrieveCommand.Type, retrieveCommand.Id, fieldsToGet)
};
break;
}
case CreateRecordTypeCommand _:
{
CreateRecordTypeCommand createCommand = (CreateRecordTypeCommand)command;
createCommand.Target.Fields.Add(new PrimaryField()
{
Name = createCommand.Target.TableName + "Id"
});
createCommand.Target.Fields.Add(new DateTimeField()
{
Name = "createdon", Nullable = false
});
createCommand.Target.Fields.Add(new DateTimeField()
{
Name = "modifiedon", Nullable = false
});
this.data.CreateRecordType(createCommand.Target);
output = new CreateRecordTypeResult();
break;
}
case RetrieveAllCommand _:
{
RetrieveAllCommand retreiveCommand = (RetrieveAllCommand)command;
if (retreiveCommand.Columns == null)
{
retreiveCommand.Columns = new List <string>();
}
BasicQuery query = new BasicQuery()
{
Columns = EnsureIdColumn(retreiveCommand.Columns, retreiveCommand.RecordType),
RecordType = retreiveCommand.RecordType,
};
var result = this.data.Query(query);
output = new RetrieveAllResult()
{
Result = result
};
break;
}
case RetrieveRecordTypeCommand _:
{
RetrieveRecordTypeCommand retrievecommand = (RetrieveRecordTypeCommand)command;
var result = this.data.GetRecordType(retrievecommand.RecordType);
output = new RetrieveRecordTypeResult()
{
Type = result
};
break;
}
case DeleteRecordCommand _:
{
DeleteRecordCommand deleteCommand = (DeleteRecordCommand)command;
this.data.DeleteRecord(deleteCommand.Type, deleteCommand.Id);
output = new DeleteRecordResult();
break;
}
case UpdateRecordCommand _:
{
UpdateRecordCommand updateCommand = (UpdateRecordCommand)command;
this.data.UpdateRecord(updateCommand.Target);
output = new UpdateRecordResult();
break;
}
case RetrieveAllRecordTypesCommand _:
{
var allTypes = this.data.RetrieveAllRecordTypes();
output = new RetrieveAllRecordTypesResult()
{
RecordTypes = allTypes
};
break;
}
case AddFieldToRecordTypeCommand castedCommand:
{
this.data.AddFieldToRecordType(castedCommand.RecordType, castedCommand.Field);
output = new AddFieldToRecordTypeResult();
}
break;
case RemoveFieldFromRecordTypeCommand castedCommand:
{
this.data.RemoveFieldFromRecordType(castedCommand.RecordType, castedCommand.FieldName);
output = new RemoveFieldFromRecordTypeResult();
break;
}
case DeleteRecordTypeCommand castedCommand:
{
var recordType = this.data.GetRecordType(castedCommand.RecordType);
this.data.DeleteRecordType(recordType.RecordTypeId);
break;
}
case QueryRecordsCommand castedCommand:
{
var query = castedCommand.Query;
query.Columns = EnsureIdColumn(query.Columns, query.RecordType);
var result = this.data.Query(query);
output = new QueryRecordsResult()
{
Result = result
};
break;
}
default:
{
throw new Exception("Unknown command");
}
}
data.Close();
return(output);
}
static void Main(string[] args)
{
IDataSource data = new Postgres("Host=localhost; Port=5432; User Id=reuben; Password=password; Database=recore;");
DataService service = new DataService();
service.data = data;
Console.WriteLine(data.CheckInitialized());
if (!data.CheckInitialized())
{
data.Initialize();
}
RecordType CustomerType = new RecordType("Customer", "customer");
CustomerType.Fields.Add(new TextField()
{
Name = "name", Length = 50
});
service.Execute(new CreateRecordTypeCommand()
{
Target = CustomerType
});
RecordType logType = new RecordType("Log", "log");
logType.Fields.Add(new TextField()
{
Name = "name", Length = 50, Nullable = true
});
logType.Fields.Add(new NumberField()
{
Name = "number", Nullable = true
});
service.Execute(new CreateRecordTypeCommand()
{
Target = logType
});
Record firstCustomer = new Record()
{
Type = "customer",
Data = new Dictionary <string, object>()
{
["name"] = "First customer",
}
};
CreateRecordResult createResult = (CreateRecordResult)service.Execute(new CreateRecordCommand()
{
Target = firstCustomer
});
Record createdRecord = ((RetrieveRecordResult)service.Execute(new RetrieveRecordCommand()
{
AllFields = true, Id = createResult.RecordId, Type = "customer"
})).Result;
foreach (var item in createdRecord.Data)
{
Console.WriteLine($"{item.Key}:{item.Value}");
}
BasicQuery query = new BasicQuery()
{
RecordType = "customer",
Columns = new List <string>()
{
"name"
},
Filters = new List <QueryFilter>()
{
new QueryFilter()
{
Column = "name",
Operation = FilterOperation.Null
},
new QueryFilter()
{
Column = "name",
Operation = FilterOperation.NotNull
}
},
Orderings = new List <QueryOrdering>()
{
new QueryOrdering()
{
Column = "name",
Descending = false,
}
}
};
QueryRecordsCommand queryCommand = new QueryRecordsCommand()
{
Query = query
};
var queryResult = (QueryRecordsResult)service.Execute(queryCommand);
Console.WriteLine(queryResult.Result.Count);
var allTypes = data.GetAllTypes().ToList();
allTypes.Reverse();
foreach (var type in allTypes)
{
data.DeleteRecordType(type.RecordTypeId);
}
Console.WriteLine("All done");
Console.ReadLine();
}
public ResultItemCode GetQueryLineChart(BasicQuery query)
{
return(new NTS.WEB.BLL.Charts().GetQueryLineChart(query));
}
/// <summary>
///
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public ResultFeeapportion GetFeeApportionData(Queryfeeapportion query)
{
try
{
ExecuteProcess process = new ExecuteProcess();
process.ActionName = "";
process.ActionTime = System.DateTime.Now;
process.Success = true;
process.ExceptionMsg = "";
ResultFeeapportion feeApption = new ResultFeeapportion();
feeApption.FeeApportionType = "按比例分摊";
List <TB_Alloction_Config> config = GetAlloctionConfig(query);
if (config.Count == 0)
{
process.Success = false;
process.ExceptionMsg = "此区域费用分摊没有配置";
ResultFeeapportion feeApptionReturn = new ResultFeeapportion();
feeApption.ActionInfo = process;
return(feeApption);
}
Queryfeeapportion queryLastMonth = new Queryfeeapportion();
queryLastMonth.ItemCode = query.ItemCode;
queryLastMonth.ObjType = query.ObjType;
queryLastMonth.ObjectId = query.ObjectId;
queryLastMonth.StartTime = query.StartTime.AddMonths(-1);
List <TB_Alloction_Config> configLast = GetAlloctionConfig(queryLastMonth);
FeeApportionValClass feeappVal = new FeeApportionValClass();
FeeApportionChartsClass chartClass = new FeeApportionChartsClass();
// 数据。
List <SeriesClass> serialClass = new List <SeriesClass>();
if (config.Count > 0)
{
DateTime dtBegin = config[0].ALLoction_StartDate;
DateTime dtEnd = config[0].ALLoction_EndDate;
double feeDai = config[0].ALLoction_Fee;
feeappVal.ApportionVal = config[0].ALLoction_Fee;
//List<CostQueryModel> refModel = _feeApportion.GetCostQuery(query, dtBegin, dtEnd);
EnergyContrast energy = new EnergyContrast();
BasicQuery query2 = new BasicQuery();
query2.AreaType = query.ObjType;
query2.StartTime = config[0].ALLoction_StartDate;
query2.EndTime = config[0].ALLoction_EndDate;
query2.ObjectNum = query.ObjectId;
query2.QueryType = EnergyAnalyseQueryType.Default;
query2.Unit = 1;
var lineChart = energy.GetSingleItemCodeByObject(query2, query.ItemCode);
double befVal = 0;
befVal = (double)lineChart.Values.Sum();
AccessCommon acess = new AccessCommon();
decimal flPrice = acess.GetFeePrice(query.ItemCode);
befVal = befVal * (double)flPrice;
//foreach (var costQueryModel in refModel)
//{
// befVal += costQueryModel.TOTAL_COST;
//}
feeappVal.BeforeVal = Math.Round(befVal, 2);
feeappVal.TotalVal = Math.Round((befVal + feeappVal.ApportionVal), 2);
}
List <SeriesData> sidatas = new List <SeriesData>();
SeriesData seri = new SeriesData();
// 统计明细数据
SeriesData data1 = new SeriesData();
SeriesData data2 = new SeriesData();
List <SeriesClass> serClass1 = new List <SeriesClass>();
List <SeriesClass> serClass2 = new List <SeriesClass>();
int iCout = 0;
// 列表
List <FeeApportionListClass> appList = new List <FeeApportionListClass>();
foreach (TB_Alloction_Config conf in config)
{
FeeApportionListClass listClass = new FeeApportionListClass();
SeriesClass serial1 = new SeriesClass();
SeriesClass serial2 = new SeriesClass();
int objectId = conf.AreaID;
List <Model.BaseLayerObject> objectList;
string objectName = "";
TB_AreaInfo info = _accssCommon.GetAreaInfo(conf.AreaID);
if (info != null)
{
objectName = info.CName;
}
//Queryfeeapportion queryItem = new Queryfeeapportion();
//queryItem = query;
//queryItem.ObjectId = conf.AreaID;
//List<CostQueryModel> refModel = _feeApportion.GetCostQuery(queryItem, conf.ALLoction_StartDate, conf.ALLoction_EndDate);
double BefFee = 0;
//if (refModel.Count > 0)
//{
// for (int jCount = 0; jCount < refModel.Count; jCount++)
// {
// BefFee += refModel[jCount].TOTAL_COST;
// }
//}
EnergyContrast energy = new EnergyContrast();
BasicQuery query2 = new BasicQuery();
query2.AreaType = query.ObjType;
query2.StartTime = config[0].ALLoction_StartDate;
query2.EndTime = config[0].ALLoction_EndDate;
query2.ObjectNum = conf.AreaID;
query2.QueryType = EnergyAnalyseQueryType.Default;
query2.Unit = 1;
var lineChart2 = energy.GetSingleItemCodeByObject(query2, query.ItemCode);
BefFee = (double)lineChart2.Values.Sum();
AccessCommon acess = new AccessCommon();
decimal flPrice = acess.GetFeePrice(query.ItemCode);
BefFee = BefFee * (double)flPrice;
serial1.name = objectName;
serial2.name = objectName;
serial1.y = Math.Round(BefFee);
serial2.y = Math.Round((conf.ALLoction_Fee * conf.CfgPercent), 2);
serClass1.Add(serial1);
serClass2.Add(serial2);
FeeApportionListClass list1 = new FeeApportionListClass();
list1.Id = iCout + 1;
list1.Obj = objectName;
list1.Tm = query.StartTime.ToString("yyyy-MM");
list1.BeforeVal = Math.Round(BefFee, 2);
list1.ApportionVal = Math.Round((conf.ALLoction_Fee * conf.CfgPercent), 2);
list1.TotalVal = list1.BeforeVal + list1.ApportionVal;
appList.Add(list1);
iCout++;
}
data1.name = "分摊前费用";
data1.data = serClass1;
data2.name = "分摊费用";
data2.data = serClass2;
sidatas.Add(data1);
sidatas.Add(data2);
chartClass.series = sidatas;
feeApption.FeeApportionCharts = chartClass;
if (configLast.Count > 0)
{
DateTime dtBegin2 = configLast[0].ALLoction_StartDate;
DateTime dtEnd2 = configLast[0].ALLoction_EndDate;
double feeDai = configLast[0].ALLoction_Fee;
feeappVal.ApportionValLastMonth = configLast[0].ALLoction_Fee;
//List<CostQueryModel> refModel = _feeApportion.GetCostQuery(query, dtBegin2, dtEnd2);
double beforeApportionVal = 0;
//foreach (var costQueryModel in refModel)
//{
// beforeApportionVal += costQueryModel.TOTAL_COST;
//}
EnergyContrast energy = new EnergyContrast();
BasicQuery query2 = new BasicQuery();
query2.AreaType = query.ObjType;
query2.StartTime = configLast[0].ALLoction_StartDate;
query2.EndTime = configLast[0].ALLoction_EndDate;
query2.ObjectNum = query.ObjectId;
query2.QueryType = EnergyAnalyseQueryType.Default;
query2.Unit = 1;
var lineChart2 = energy.GetSingleItemCodeByObject(query2, query.ItemCode);
beforeApportionVal = (double)lineChart2.Values.Sum();
AccessCommon acess = new AccessCommon();
decimal flPrice = acess.GetFeePrice(query.ItemCode);
beforeApportionVal = Math.Round(beforeApportionVal * (double)flPrice, 2);
feeappVal.BeforeValLastMonth = beforeApportionVal;
feeappVal.TotalValLastMonth = beforeApportionVal + feeappVal.ApportionValLastMonth;
}
if (feeappVal.ApportionValLastMonth > 0)
{
double compare = (feeappVal.ApportionVal - feeappVal.ApportionValLastMonth) * 100 /
feeappVal.ApportionValLastMonth;
feeappVal.ApportionValCompare = Math.Round(compare, 2).ToString();
}
else
{
feeappVal.ApportionValCompare = "-";
}
if (feeappVal.BeforeValLastMonth > 0)
{
double compare = (feeappVal.BeforeVal - feeappVal.BeforeValLastMonth) * 100 / feeappVal.BeforeValLastMonth;
feeappVal.BeforeValCompare = Math.Round(compare, 2).ToString();
}
else
{
feeappVal.BeforeValCompare = "-";
}
if (feeappVal.TotalValLastMonth > 0)
{
double compare = (feeappVal.TotalVal - feeappVal.TotalValLastMonth) * 100 / feeappVal.TotalValLastMonth;
feeappVal.TotalValCompare = Math.Round(compare, 2).ToString();
}
else
{
feeappVal.TotalValCompare = "-";
}
FeeApportionTblClass tblClass = new FeeApportionTblClass();
tblClass.FeeApportionList = appList;
feeApption.FeeApportionTbl = tblClass;
feeApption.FeeApportionVal = feeappVal;
feeApption.ActionInfo = process;
return(feeApption);
}
catch (Exception ex)
{
ExecuteProcess process = new ExecuteProcess();
process.ActionName = "";
process.ActionTime = System.DateTime.Now;
process.Success = false;
process.ExceptionMsg = ex.Message;
ResultFeeapportion feeApption = new ResultFeeapportion();
feeApption.ActionInfo = process;
return(feeApption);
}
}
public ResultItemCode GetQueryLineChart(BasicQuery query)
{
#region 定义区
var resultList = new ResultView.ResultItemCode
{
ObjectName = new List <string>(),
Enery = new Dictionary <string, List <decimal> >(),
Dept = new List <string>()
};
#endregion
var dept = string.Empty;
var itList = new BLL.Itemcode().GetItemcodeList(" and ItemCodeNumber='" + query.ItemCode + "'", " order by ItemcodeID");
if (itList.Count > 0)
{
dept = itList[0].Unit;
}
dept = query.ItemCode == "00000" ? "T" : dept;
if (query.ItemCode == "00000")
{
// resultList.Dept = new List<string>() { "T" };
var itemList = new BLL.Itemcode().GetItemcodeList(" and ParentID=0", " order by ItemcodeID");
var eneryDataListTotal = new Dictionary <string, Dictionary <string, decimal> >();
var maxcode = "01000";
var count = 0;
foreach (var itemcode in itemList)
{
var lineChart = GetSingleItemCodeByObject(query, itemcode.ItemCodeNumber);
if (count < lineChart.Count)
{
count = lineChart.Count;
maxcode = itemcode.ItemCodeNumber;
}
eneryDataListTotal.Add(itemcode.ItemCodeNumber, lineChart);
// resultList.Enery.Add(itemcode.ItemCodeName, lineChart.DatePickEnery);
}
// 重新赋值
foreach (var itemcode in itemList)
{
var itemcode1 = itemcode;
foreach (var max in eneryDataListTotal[maxcode].Where(max => !eneryDataListTotal[itemcode1.ItemCodeNumber].ContainsKey(max.Key)))
{
eneryDataListTotal[itemcode.ItemCodeNumber].Add(max.Key, 0);
}
var temp = eneryDataListTotal[itemcode.ItemCodeNumber].OrderBy(p => Convert.ToDateTime(p.Key));
var tempOrder = temp.ToDictionary(keyValuePair => keyValuePair.Key, keyValuePair => keyValuePair.Value);
eneryDataListTotal[itemcode.ItemCodeNumber] = tempOrder;
resultList.Enery.Add(itemcode.ItemCodeName, eneryDataListTotal[itemcode.ItemCodeNumber].Values.ToList());
}
resultList.ObjectName = eneryDataListTotal[maxcode].Select(p => p.Key.ToString(CultureInfo.InvariantCulture)).ToList();
}
else
{
var item = new BLL.Itemcode().GetItemcodeList(" and ItemCodeNumber='" + query.ItemCode + "'", " order by ItemcodeID")[0];
var lineChart = GetSingleItemCodeByObject(query, query.ItemCode);
// resultList.Dept = new List<string>() {item.Unit};
if (lineChart != null)
{
resultList.ObjectName = lineChart.Select(p => p.Key.ToString()).ToList();
resultList.Enery.Add(item.ItemCodeName, lineChart.Select(p => decimal.Parse(p.Value.ToString())).ToList());
}
}
foreach (var baseLayerObject in resultList.ObjectName)
{
resultList.Dept.Add(dept);
}
return(resultList);
}
private NpgsqlCommand CreateCommandForQuery(BasicQuery query)
{
NpgsqlCommand command = new NpgsqlCommand()
{
Connection = this.connection
};
StringBuilder queryText = new StringBuilder();
queryText.Append("select ");
queryText.AppendJoin(',', query.Columns);
queryText.Append($" from {query.RecordType}");
if (query.Filters.Count != 0)
{
queryText.Append(" where ");
for (int i = 0; i < query.Filters.Count; i++)
{
var filter = query.Filters[i];
switch (filter.Operation)
{
case FilterOperation.Equal:
queryText.Append($"{filter.Column} = @{i}");
break;
case FilterOperation.GreaterThan:
queryText.Append($"{filter.Column} > @{i}");
break;
case FilterOperation.LessThan:
queryText.Append($"{filter.Column} < @{i}");
break;
case FilterOperation.NotEqual:
queryText.Append($"{filter.Column} <> @{i}");
break;
case FilterOperation.Null:
queryText.Append($"{filter.Column} is NULL");
break;
case FilterOperation.NotNull:
queryText.Append($"{filter.Column} is not NULL");
break;
default:
throw new NotSupportedException($"Operator {filter.Operation} is not supported");
}
if (!(filter.Operation == FilterOperation.NotNull || filter.Operation == FilterOperation.Null))
{
command.Parameters.Add(CreateParameter(i.ToString(), filter.Value));
}
// TODO: This is hardcoded to and. Should be changed in the future
if (i != query.Filters.Count - 1)
{
queryText.Append(" and ");
}
}
}
if (query.Orderings.Count != 0)
{
var convertedOrders = query.Orderings.Select(i => $"{i.Column} {(i.Descending ? "desc" : "asc")}");
queryText.Append($" order by {(string.Join(',', convertedOrders))}");
}
Console.WriteLine("Executing query " + queryText.ToString());
command.CommandText = queryText.ToString();
return(command);
}
private List <decimal> GetYuceData(BasicQuery basicQuery, QueryLoadForecast queryLoad)
{
List <ResultCompare> compReult = new List <ResultCompare>();
DateTime deBegin = new DateTime();
DateTime deEnd = new DateTime();
TimeSpan ts = queryLoad.EndTime - queryLoad.StartTime;
int Days = ts.Days;
#region 获取35%数据
if (Days == 0)
{
deBegin = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd 00:00:00")).AddDays(-10);
deEnd = queryLoad.StartTime;
// 循环前10天
for (DateTime begTime = deBegin; begTime < DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd 00:00:00")); begTime = begTime.AddDays(1))
{
BasicQuery baseQueryItem = basicQuery;
baseQueryItem.StartTime = begTime;
baseQueryItem.EndTime = begTime;
ResultCompare reultCom = GetDateTongJiData(baseQueryItem);
compReult.Add(reultCom);
}
}
else
{
if (queryLoad.StartTime.CompareTo(DateTime.Now) > 0)
{
BasicQuery baseQueryItem = basicQuery;
baseQueryItem.StartTime = DateTime.Now.AddDays(-(Days + 1));;
baseQueryItem.EndTime = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd")).AddDays(-1);
ResultCompare reultCom = GetDateTongJiData(baseQueryItem);
compReult.Add(reultCom);
}
else
{
BasicQuery baseQueryItem = basicQuery;
baseQueryItem.StartTime = queryLoad.StartTime.AddDays(-(Days + 1));;
baseQueryItem.EndTime = queryLoad.StartTime.AddDays(-1);
ResultCompare reultCom = GetDateTongJiData(baseQueryItem);
compReult.Add(reultCom);
}
}
#endregion
#region 获取65%数据。
//获取去年当天的预测值
BasicQuery baseQuery2 = basicQuery;
baseQuery2.StartTime = queryLoad.StartTime.AddYears(-1);
baseQuery2.EndTime = queryLoad.EndTime.AddYears(-1);
var resultList2 = GetDateTongJiData(baseQuery2);
List <decimal> lstAvg2 = new List <decimal>();
List <decimal> lstAvgMonth = GetAvgYuce(compReult, queryLoad);
if (Days == 0)
{
foreach (var d in resultList2.Enery)
{
List <decimal> lstDec = d.Value;
for (int i = 0; i < d.Value.Count; i++)
{
if (d.Value[i] > 0)
{
lstAvg2.Add(Math.Round((d.Value[i]), 2));
}
}
}
// 最终值。
List <decimal> lstAvgEnd = new List <decimal>();
for (int i = 0; i < lstAvgMonth.Count; i++)
{
if (lstAvg2.Count == 0)
{
lstAvgEnd.Add(lstAvgMonth[i]);
}
else
{
lstAvgEnd.Add(lstAvgMonth[i] * 35 / 100 + lstAvg2[i] * 65 / 100);
}
}
return(lstAvgEnd);
}
else
{
foreach (var d in resultList2.Enery)
{
List <decimal> lstDec = d.Value;
for (int i = 0; i < d.Value.Count; i++)
{
lstAvg2.Add(Math.Round((d.Value[i]), 2));
}
}
// 最终值。
List <decimal> lstAvgEnd = new List <decimal>();
for (int i = 0; i < lstAvgMonth.Count; i++)
{
if (lstAvgMonth.Count == Days + 1)
{
if (lstAvg2.Count == 0)
{
decimal avgDec = lstAvgMonth[i];
lstAvgEnd.Add(Math.Round(avgDec, 2));
}
else
{
decimal avgDec = lstAvgMonth[i] * 35 / 100 + lstAvg2[i] * 65 / 100;
lstAvgEnd.Add(Math.Round(avgDec, 2));
}
}
else
{
throw new Exception("没有历史数据，无法进行预测");
}
}
return(lstAvgEnd);
}
#endregion
}
public ResultLoadForecastMap GetLoadForecastChart(QueryLoadForecast loadCast)
{
try
{
ResultLoadForecastMap mapNew = new ResultLoadForecastMap();
ExecuteProcess proc = new ExecuteProcess();
proc.ActionName = "";
proc.ActionTime = DateTime.Now;
proc.ActionUser = "";
proc.ExceptionMsg = "";
proc.Success = true;
var basicQuery = new BasicQuery
{
EndTime = loadCast.EndTime,
StartTime = loadCast.StartTime,
ItemCode = loadCast.ItemCode,
Unit = loadCast.Particle,
AreaType = loadCast.ObjType,
ObjectNum = loadCast.ObjectId
};
// 获取数据
var resultList = GetDateTongJiData(basicQuery);
List <decimal> lstDecAvg = GetYuceData(basicQuery, loadCast);
List <SerialData> serData = new List <SerialData>();
foreach (var d in resultList.Enery)
{
SerialData ser1 = new SerialData();
ser1.name = "负荷预测值";
ser1.data = lstDecAvg;
serData.Add(ser1);
ser1 = new SerialData();
ser1.name = "能耗实际值";
TimeSpan ts = DateTime.Parse(System.DateTime.Now.ToString("yyyy-MM-dd")) - loadCast.StartTime;
int DayChas = ts.Days;
List <decimal> lstNew = d.Value;
if (DayChas == 0)
{
for (int iCount = 0; iCount < lstNew.Count; iCount++)
{
lstNew[iCount] = Math.Round(lstNew[iCount], 2);
}
}
else if (DayChas > 0)
{
for (int iCount = 0; iCount < lstNew.Count; iCount++)
{
if (iCount > DayChas)
{
lstNew[iCount] = 0;
}
else
{
lstNew[iCount] = Math.Round(lstNew[iCount], 2);
}
}
}
else
{
for (int i = 0; i < lstNew.Count; i++)
{
lstNew[i] = -9999;
}
}
ser1.data = lstNew;
serData.Add(ser1);
}
mapNew.ActionInfo = proc;
// 判断是否是将来当天数据。
if (loadCast.StartTime.CompareTo(DateTime.Now) > 0)
{
List <SerialData> serDataNew = new List <SerialData>();
serDataNew.Add(serData[0]);
mapNew.series = serDataNew;
}
else
{
mapNew.series = serData;
}
if (resultList.Dept.Count > 0)
{
mapNew.Unit = resultList.Dept[0].ToString();
}
List <ResultLoadForecastList> listFore = new List <ResultLoadForecastList>();
for (int i = 0; i < serData[0].data.Count; i++)
{
ResultLoadForecastList list = new ResultLoadForecastList();
list.Id = i + 1;
if (loadCast.StartTime.Date == loadCast.EndTime.Date)
{
string hour = "";
if (i < 10)
{
hour = "0" + i.ToString();
}
else
{
hour = i.ToString();
}
list.TimeArea = loadCast.StartTime.ToString("yyyy-MM-dd" + " " + hour + ":00:00");
}
else
{
list.TimeArea = loadCast.StartTime.AddDays(i).ToString("yyyy-MM-dd");
}
if (i < serData[1].data.Count)
{
list.History = Math.Round(serData[1].data[i], 2);
}
else
{
list.History = -9999;
}
list.ForeCast = Math.Round(serData[0].data[i], 2);
if (list.History == -9999)
{
list.Deviation = -9999;
}
else
{
list.Deviation = Math.Round((serData[0].data[i] - serData[1].data[i]), 2);
}
if (list.History == -9999)
{
list.Pecent = "--";
listFore.Add(list);
}
else
{
decimal dec1 = (serData[0].data[i] - serData[1].data[i]) * 100;
decimal dec2 = serData[1].data[i];
if (dec1 != 0 && dec2 != 0)
{
decimal pec = (serData[0].data[i] - serData[1].data[i]) * 100 / serData[1].data[i];
list.Pecent = Math.Round(pec, 2).ToString() + "%";
listFore.Add(list);
}
else
{
list.Pecent = "--";
listFore.Add(list);
}
}
}
mapNew.LoadForecast = listFore;
mapNew.HistoryTotal = 0;
Math.Round(serData[1].data.Sum(), 2);
for (int rCount = 0; rCount < serData[1].data.Count; rCount++)
{
if (serData[1].data[rCount] != -9999)
{
mapNew.HistoryTotal = mapNew.HistoryTotal + serData[1].data[rCount];
}
}
mapNew.ForeCastTotal = Math.Round(lstDecAvg.Sum(), 2);
return(mapNew);
}
catch (Exception ex)
{
ResultLoadForecastMap map2 = new ResultLoadForecastMap();
ExecuteProcess proc = new ExecuteProcess();
proc.ActionName = "";
proc.ActionTime = DateTime.Now;
proc.ActionUser = "";
proc.ExceptionMsg = ex.Message;
proc.Success = false;
map2.ActionInfo = proc;
return(map2);
}
}
public async Task <ActionResult <QueryResult <MovieResponseModel> > > GetMovieGridAsync(BasicQuery request, CancellationToken cancellationToken)
{
_logger.LogInformation("Call made to GetMovieGridAsync.");
return(await _movieService.GetMoviesGridAsync(request, cancellationToken));
}
public ResultItemCode GetTwoQueryLineChart(BasicQuery query, BasicQuery query2, int tabId)
{
return(new NTS.WEB.BLL.Charts().GetTwoQueryLineChart(query, query2, tabId));
}
public Dictionary <string, decimal> GetSingleItemCodeByObject(BasicQuery query, string itemcode)
{
// var result = new ResultIndexLineChart { DatePick = new List<string>(), DatePickEnery = new List<decimal>() };
var eneryDataList = new Dictionary <string, decimal>();
var model = new BaseQueryModel();
List <Model.BaseLayerObject> objectList = new List <Model.BaseLayerObject>();
if (query.AreaType == AreaType.Area)
{
objectList = new NTS.WEB.BLL.BaseLayerObject().GetBaseLayerObjectList(string.Format(" and layerobjectid={0}", query.ObjectNum), " order by LayerObjectID");
}
else
{
objectList = new NTS.WEB.BLL.BaseLayerObject().GetBaseFuncLayerObjectList(string.Format(" and layerobjectid={0}", query.ObjectNum), " order by LayerObjectID");
}
model.IsDevice = 0;
model.ObjectList = (from p in objectList select p.LayerObjectID).ToList <int>();
model.ItemCode = itemcode;
model.Unit = BaseTool.GetChartUnit(query.Unit);
model.Starttime = query.StartTime;
model.Endtime = query.EndTime;
model.areaType = query.AreaType;
var resList = _energCon.GetBaseEneryDataListNew(model);
foreach (var d in objectList.SelectMany(o => resList.BaseLayerObjectResults[o.LayerObjectID.ToString(CultureInfo.InvariantCulture)].Datas))
{
if (!eneryDataList.ContainsKey(d.DatePick))
{
switch (query.QueryType)
{
case EnergyAnalyseQueryType.Convert2Co2:
eneryDataList.Add(d.DatePick, d.Co2DataValue);
break;
case EnergyAnalyseQueryType.Convert2Coal:
eneryDataList.Add(d.DatePick, d.CoalDataValue);
break;
case EnergyAnalyseQueryType.Convert2Money:
eneryDataList.Add(d.DatePick, d.MoneyDataValue);
break;
default:
eneryDataList.Add(d.DatePick, d.DataValue);
break;
}
}
else
{
switch (query.QueryType)
{
case EnergyAnalyseQueryType.Convert2Co2:
eneryDataList[d.DatePick] += d.Co2DataValue;
break;
case EnergyAnalyseQueryType.Convert2Coal:
eneryDataList[d.DatePick] += d.CoalDataValue;
break;
case EnergyAnalyseQueryType.Convert2Money:
eneryDataList[d.DatePick] += d.MoneyDataValue;
break;
default:
eneryDataList[d.DatePick] += d.DataValue;
break;
}
}
}
//foreach (var e in eneryDataList)
//{
// result.DatePick.Add(e.Key);
// result.DatePickEnery.Add(e.Value);
//}
return(eneryDataList);
}
public ResultQueryPie GetQueryPieChart(BasicQuery query)
{
return(new NTS.WEB.BLL.Charts().GetQueryPieChart(query));
}
public ResultCompare GetAreaCompareChart(QueryOrderObjects queryObject)
{
#region 定义区
var resultList = new ResultView.ResultCompare
{
ObjectName = new List <string>(),
Enery = new Dictionary <string, List <decimal> >(),
Dept = new List <string>()
};
#endregion
var olist = queryObject.AreaIdLst.Aggregate(string.Empty, (current, q) => current + ("," + q.ToString()));
if (olist.Length > 0)
{
List <Model.BaseLayerObject> objectList;
if (queryObject.ObjType == AreaType.Area)
{
objectList =
new BLL.BaseLayerObject().GetBaseLayerObjectList(
string.Format(" and layerobjectid in ({0})", olist.Substring(1)), " order by LayerObjectID");
}
else
{
objectList =
new BLL.BaseLayerObject().GetBaseFuncLayerObjectList(
string.Format(" and layerobjectid in ({0})", olist.Substring(1)), " order by LayerObjectID");
}
foreach (var o in objectList)
{
var basicQuery = new BasicQuery
{
EndTime = queryObject.EndTime,
StartTime = queryObject.StartTime,
ItemCode = queryObject.ItemCode,
Unit = queryObject.Particle,
AreaType = queryObject.ObjType,
QueryType = queryObject.QueryType
};
basicQuery.ObjectNum = o.LayerObjectID;
var result = GetQueryLineChart(basicQuery);
//resultList.Dept = result.Dept;
resultList.ObjectName = result.ObjectName;
if (queryObject.ItemCode != "00000")
{
var itemList = new BLL.Itemcode().GetItemcodeList(" and ItemCodeNumber='" + queryObject.ItemCode + "'", " order by ItemcodeID")[0];
//resultList.Enery.Add(o.LayerObjectName, result.Enery[itemList.ItemCodeName]);
for (int i = 0; i < result.Dept.Count; i++)
{
resultList.Dept.Add(itemList.Unit);
}
resultList.Enery.Add(o.LayerObjectName, result.Enery[itemList.ItemCodeName].Select(p => p / decimal.Parse(o.AreaNum.ToString())).ToList());
}
else
{
var tempValue = new decimal[result.Enery[result.Enery.Keys.First <string>()].Count];
foreach (var re in result.Enery)
{
for (var i = 0; i < re.Value.Count; i++)
{
tempValue[i] += re.Value[i];
}
}
for (int i = 0; i < result.Dept.Count; i++)
{
resultList.Dept.Add("T");
}
resultList.Enery.Add(o.LayerObjectName, tempValue.ToList().Select(p => p / decimal.Parse(o.AreaNum.ToString())).ToList());
}
}
return(resultList);
}
return(null);
}
/// <summary>
/// 数据库获取导出数据列表
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public List <FeeApportionListClass> GetFeeApportDataList(Queryfeeapportion query)
{
Queryfeeapportion queryLastMonth = query;
List <TB_Alloction_Config> config = GetAlloctionConfig(query);
int iCout = 0;
// 列表
List <FeeApportionListClass> appList = new List <FeeApportionListClass>();
foreach (TB_Alloction_Config conf in config)
{
FeeApportionListClass listClass = new FeeApportionListClass();
SeriesClass serial1 = new SeriesClass();
SeriesClass serial2 = new SeriesClass();
int objectId = conf.AreaID;
List <Model.BaseLayerObject> objectList;
string objectName = "";
if (query.ObjType == AreaType.Area)
{
TB_AreaInfo info = _accssCommon.GetAreaInfo(conf.AreaID);
//objectList = new BLL.BaseLayerObject().GetBaseLayerObjectList(
// string.Format(" and layerobjectid ={0} ", conf.AreaID), " ");
if (info != null)
{
objectName = info.CName;
}
}
else
{
TB_AreaInfo info = _accssCommon.GetAreaInfo(conf.AreaID);
if (info != null)
{
objectName = info.CName;
}
}
Queryfeeapportion queryItem = new Queryfeeapportion();
queryItem = query;
queryItem.ObjectId = conf.AreaID;
double BefFee = 0;
//List<CostQueryModel> refModel = _feeApportion.GetCostQuery(queryItem, conf.ALLoction_StartDate, conf.ALLoction_EndDate);
//double BefFee = 0;
//if (refModel.Count > 0)
//{
// for (int jCount = 0; jCount < refModel.Count; jCount++)
// {
// BefFee += refModel[jCount].TOTAL_COST;
// }
//}
EnergyContrast energy = new EnergyContrast();
BasicQuery query2 = new BasicQuery();
query2.AreaType = query.ObjType;
query2.StartTime = config[0].ALLoction_StartDate;
query2.EndTime = config[0].ALLoction_EndDate;
query2.ObjectNum = query.ObjectId;
query2.QueryType = EnergyAnalyseQueryType.Default;
query2.Unit = 1;
var lineChart = energy.GetSingleItemCodeByObject(query2, query.ItemCode);
BefFee = (double)lineChart.Values.Sum();
AccessCommon acess = new AccessCommon();
decimal flPrice = acess.GetFeePrice(query.ItemCode);
BefFee = BefFee * (double)flPrice;
FeeApportionListClass list1 = new FeeApportionListClass();
list1.Id = iCout + 1;
list1.Obj = objectName;
list1.Tm = query.StartTime.ToString("yyyy-MM");
list1.BeforeVal = BefFee;
list1.ApportionVal = (conf.ALLoction_Fee * conf.CfgPercent) / 100;
list1.TotalVal = list1.BeforeVal + list1.ApportionVal;
appList.Add(list1);
iCout++;
}
return(appList);
}
