public async Task <NewEquipmentUIPaging> GetEquipment(int modelId, int parentId, int skip, int limit)
{
using (var conn = new SqlConnection(AppSettings.ConnectionString))
{
_logger.LogTrace(
$"start GetEquipmentWithCheckLists modelId = {modelId}, parentId = {parentId}, skip = {skip}, limit = {limit}");
List <EquipmentModel> result;
int count = 0;
if (parentId == 0)
{
var sql = Sql.SqlQueryCach["Equipment.GetEquipmentModelByModelAndParentNull"];
result = (await conn.QueryAsync <EquipmentModel>(
sql,
new { model_id = modelId, skip = skip, limit = limit, parent_id = parentId })).ToList();
var sqlc = Sql.SqlQueryCach["Equipment.CountEquipmentModelByModelAndParentNull"];
count = (await conn.QueryAsync <int>(sqlc, new { model_id = modelId })).FirstOrDefault();
}
else
{
var sql = Sql.SqlQueryCach["Equipment.GetEquipmentModelByModelAndParent"];
result = (await conn.QueryAsync <EquipmentModel>(
sql,
new { model_id = modelId, skip = skip, limit = limit, parent_id = parentId })).ToList();
var sqlc = Sql.SqlQueryCach["Equipment.CountEquipmentModelByModelAndParent"];
count = (await conn.QueryAsync <int>(sqlc, new { model_id = modelId, parent_id = parentId }))
.FirstOrDefault();
}
var list = new List <EquipmentUI>();
foreach (var eqm in result)
{
var eqWithCheckLists = await GetEquipmentModelById(eqm.Id);
eqWithCheckLists.ParentId = eqm.ParentId;
eqWithCheckLists.Id = eqm.Id;
eqWithCheckLists.ModelId = eqm.ModelId;
eqWithCheckLists.IsMark = eqm.IsMark;
list.Add(eqWithCheckLists);
}
var sqlrM = new ModelRepository(_logger);
var model = await sqlrM.GetById(modelId);
var ret = new NewEquipmentUIPaging
{
Data = list.ToArray(),
Model = model,
Total = count
};
return(ret);
}
}
public async Task <EquipmentUIPaging> GetEquipmentWithCheckLists(int modelId, int parentId, int skip, int limit,
string filter)
{
using (var conn = new SqlConnection(AppSettings.ConnectionString))
{
var all = await GetEquipmentWithCheckLists(modelId, parentId, 0, Int32.MaxValue);
var filters = JsonConvert.DeserializeObject <Other.Other.FilterBody[]>(filter);
var list = new List <CheckListEquipmentUI>();
foreach (var itemAll in all.Data)
{
if (itemAll.Equipment.Name.ToLower().Contains(filters.FirstOrDefault().Value.ToLower()))
{
list.Add(itemAll);
}
}
var listSkiplimit = new List <CheckListEquipmentUI>();
if (skip < list.Count)
{
if (limit + skip > list.Count)
{
limit = list.Count - skip;
}
for (int i = skip; i < limit + skip; i++)
{
listSkiplimit.Add(list[i]);
}
}
var sqlrM = new ModelRepository(_logger);
var model = await sqlrM.GetById(modelId);
var result = new EquipmentUIPaging
{
Data = listSkiplimit.ToArray(),
Model = model,
Total = list.Count
};
return(result);
}
}
public async Task <ReportPagingUI> GetReportEquipmentTaskHistory(int skip, int limit, string filter, string orderby)
{
using (var conn = new SqlConnection(AppSettings.ConnectionString))
{
CreateFilter(filter, out string sqlfilter);
CreateOrderBy(orderby, out string sqlorderby);
var result = (await conn.QueryAsync <TrainTaskReport1>(
$"{ReportCommon.Sql2Select} {ReportCommon.Sql2Common} {sqlfilter} {sqlorderby} {ReportCommon.Sql2End}",
new[]
{
typeof(TrainTaskReport1), typeof(Carriage), typeof(Train), typeof(EquipmentModel),
typeof(Equipment)
},
objects =>
{
var task = (TrainTaskReport1)objects[0];
task.Carriage = (Carriage)objects[1];
task.Carriage.Train = (Train)objects[2];
task.EquipmentModel = (EquipmentModel)objects[3];
task.EquipmentModel.Equipment = (Equipment)objects[4];
return(task);
}, new { skip = skip, limit = limit }
)).ToArray();
var sqlc = $"{TaskCommon.SqlCount} {TaskCommon.SqlCommon}";
var count = conn.ExecuteScalar <int>(sqlc);
var columns = new List <ReportColumnsUI>
{
new ReportColumnsUI
{
Name = "Номер",
Type = "int",
Alias = "TaskId"
},
new ReportColumnsUI
{
Name = "Состав",
Type = "string",
Alias = "TrainName"
},
new ReportColumnsUI
{
Name = "Вагон",
Type = "string",
Alias = "CarriageName"
},
new ReportColumnsUI
{
Name = "Наименование оборудования",
Type = "string",
Alias = "EquipmentName"
},
new ReportColumnsUI
{
Name = "Тип инцидента",
Type = "string",
Alias = "TaskTypeId"
},
new ReportColumnsUI
{
Name = "Время создания инцидента",
Type = "date",
Alias = "CreateDate"
},
new ReportColumnsUI
{
Name = "Время закрытия инцидента",
Type = "date",
Alias = "CloseDate"
},
};
var sqlRmodel = new ModelRepository(_logger);
foreach (var item in result)
{
var carriageTypeId = (int)(await sqlRmodel.GetById(item.Carriage.ModelId)).ModelType;
if (carriageTypeId == 0)
{
item.CarriageTypeString = Other.Other.CarriageTypeString.HeadVagon;
}
if (carriageTypeId == 1)
{
item.CarriageTypeString = Other.Other.CarriageTypeString.TractionVagon;
}
if (carriageTypeId == 2)
{
item.CarriageTypeString = Other.Other.CarriageTypeString.TrailerVagon;
}
}
var ret = new ReportPagingUI
{
Columns = columns.ToArray(),
Rows = result.Select(row => new List <string>
{
row.Id.ToString(),
row.Carriage.Train.Name,
row.Carriage.Serial + " (" + row.Carriage.Number + ", " + row.CarriageTypeString + ")",
row.EquipmentModel.Equipment.Name,
_TaskTypeEnumToString(row.TaskType),
row.CreateDate.ToString(),
row.UpdateDate.ToString()
})
.Select(vals => new ReportValuesUI
{
Values = vals.ToArray()
}).ToArray(),
Total = count
};
return(ret);
}
}