public async Task <DevExtremeTableData.ReportResponse> GetTable(DeviceOperationRequest input)
{
using (var conn = new SqlConnection(AppSettings.ConnectionString))
{
var result = new DevExtremeTableData.ReportResponse();
const string sql = "SELECT * FROM [DeviceHistories] h LEFT JOIN [auth_users] u on u.Id=h.UserId WHERE h.[DeviceId]=@DeviceId";
var items = (await conn.QueryAsync <DeviceHistory, User, DeviceOperationItemDto>(
sql, (history, historyUser) =>
{
return(new DeviceOperationItemDto
{
Id = history.Id,
User = historyUser.Name,
Date = history.UpdateDate,
Operation = GetOperationString(history.Operation)
});
}, new { DeviceId = input.DeviceId })).ToList();
result.Columns = new List <DevExtremeTableData.Column>
{
new DevExtremeTableData.Column("col0", "Пользователь", "string"),
new DevExtremeTableData.Column("col1", "Операция", "string"),
new DevExtremeTableData.Column("col2", "Дата", "date"),
};
result.Rows = new List <DevExtremeTableData.Row>();
if (items.Count > 0)
{
foreach (var item in items)
{
result.Rows.Add(new DevExtremeTableData.Row
{
Id = new DevExtremeTableData.RowId {
Id = item.Id, Type = 0
},
HasItems = false.ToString(),
Col0 = item.User,
Col1 = item.Operation,
Col2 = item.Date.ToStringDateTime()
});
}
}
result.Rows = DevExtremeTableUtils.DevExtremeTableFiltering(result.Rows, input.Filters);
result.Rows = DevExtremeTableUtils.DevExtremeTableSorting(result.Rows, input.Sortings);
result.Total = result.Rows.Count.ToString();
result.Paging(input.Paging);
return(result);
}
}
public async Task <DevExtremeTableData.ReportResponse> GetMigrationHistoryTable(CarriageMigrationHistoryRequest input)
{
using (var conn = new SqlConnection(AppSettings.ConnectionString))
{
var result = new DevExtremeTableData.ReportResponse();
const string sqlM = @"select * from [CarriageMigrations] m
left join [Trains] t on t.Id=m.TrainId
left join [Carriages] c on c.Id=m.CarriageId
left join [Stantions] s on s.Id=m.StantionId
where m.[CarriageId]=@Id order by m.[UpdateDate] desc";
var items = (await conn.QueryAsync <CarriageMigration, Train, Carriage, Stantion, CarriageMigration>(
sqlM,
(migr, train, carriage, stantion) =>
{
migr.Carriage = carriage;
migr.Train = train;
migr.Stantion = stantion;
return(migr);
}, new { Id = input.CarriageId })).ToList();
result.Columns = new List <DevExtremeTableData.Column>
{
new DevExtremeTableData.Column("col0", "Дата выцепления", "date"),
new DevExtremeTableData.Column("col1", "Станция привязки", "string")
//new DevExtremeTableData.Column("col2", "Дата возврата", "string"),
};
result.Rows = new List <DevExtremeTableData.Row>();
if (items.Count > 0)
{
foreach (var item in items)
{
result.Rows.Add(new DevExtremeTableData.Row
{
HasItems = false.ToString(),
Col0 = item.UpdateDate.ToStringDateTime(),
Col1 = item.Stantion.Name
});
}
}
result.Rows = DevExtremeTableUtils.DevExtremeTableFiltering(result.Rows, input.Filters);
result.Rows = DevExtremeTableUtils.DevExtremeTableSorting(result.Rows, input.Sortings);
result.Total = result.Rows.Count.ToString();
result.Paging(input.Paging);
return(result);
}
}
//для ящика
public async Task <DevExtremeTableData.ReportResponse> GetUserLabels(UserLabelsRequest input)
{
var result = new DevExtremeTableData.ReportResponse
{
Rows = new List <DevExtremeTableData.Row>(),
Columns = new List <DevExtremeTableData.Column>
{
new DevExtremeTableData.Column("col0", "ФИО", "string"),
new DevExtremeTableData.Column("col1", "Должность", "string"),
new DevExtremeTableData.Column("col2", "Табельный номер", "string"),
new DevExtremeTableData.Column("col3", "Бригада", "string"),
}
};
const string sql = "select * from [auth_users] u left join [Brigades] b on b.Id=u.BrigadeId where u.Isblocked=0 AND PersonPosition is not null and personnumber is not null";
var list = (await _db.Connection.QueryAsync <User, Brigade, UserLabelDto>(
sql, (user, brigade) =>
{
return(new UserLabelDto
{
Id = user.Id,
Name = user.Name,
Position = user.PersonPosition,
Barcode = user.PersonNumber,
BrigadeType = brigade == null? null : TaskRepository.BrigadeTypeToString(brigade.BrigadeType)
});
})).ToList();
foreach (var item in list)
{
result.Rows.Add(new DevExtremeTableData.Row
{
Id = new DevExtremeTableData.RowId {
Id = item.Id, Type = 0
},
ParentId = null,
HasItems = false.ToString(),
Col0 = item.Name,
Col1 = item.Position,
Col2 = item.Barcode,
Col3 = item.BrigadeType
});
}
result.Rows = DevExtremeTableUtils.DevExtremeTableFiltering(result.Rows, input.Filters);
result.Rows = DevExtremeTableUtils.DevExtremeTableSorting(result.Rows, input.Sortings);
result.Total = result.Rows.Count.ToString();
result.Paging(input.Paging);
return(result);
}
public async Task <DevExtremeTableData.ReportResponse> GetLocationsTable(DeviceValueRequest input)
{
using (var conn = new SqlConnection(AppSettings.ConnectionString))
{
var result = new DevExtremeTableData.ReportResponse();
const string sql = "SELECT * FROM [DeviceValues] WHERE [DeviceId]=@DeviceId";
var items = (await conn.QueryAsync <DeviceValue>(sql, new { DeviceId = input.DeviceId }))
.Select(o =>
new DeviceLocationItemDto
{
Id = o.Id,
Lat = o.Lat,
Lng = o.Lng,
Date = o.UpdateDate
}).ToList();
result.Columns = new List <DevExtremeTableData.Column>
{
new DevExtremeTableData.Column("col0", "Широта", "string"),
new DevExtremeTableData.Column("col1", "Долгота", "string"),
new DevExtremeTableData.Column("col2", "Дата", "date"),
};
result.Rows = new List <DevExtremeTableData.Row>();
if (items.Count > 0)
{
foreach (var item in items)
{
result.Rows.Add(new DevExtremeTableData.Row
{
Id = new DevExtremeTableData.RowId {
Id = item.Id, Type = 0
},
HasItems = false.ToString(),
Col0 = item.Lat.ToString(CultureInfo.CurrentCulture),
Col1 = item.Lng.ToString(CultureInfo.CurrentCulture),
Col2 = item.Date.ToStringDateTime()
});
}
}
result.Rows = DevExtremeTableUtils.DevExtremeTableFiltering(result.Rows, input.Filters);
result.Rows = DevExtremeTableUtils.DevExtremeTableSorting(result.Rows, input.Sortings);
result.Total = result.Rows.Count.ToString();
result.Paging(input.Paging);
return(result);
}
}
//для ящика
public async Task <DevExtremeTableData.ReportResponse> GetDeviceLabels(DeviceLabelsRequest input)
{
var result = new DevExtremeTableData.ReportResponse
{
Rows = new List <DevExtremeTableData.Row>(),
Columns = new List <DevExtremeTableData.Column>
{
new DevExtremeTableData.Column("col0", "Модель", "string"),
new DevExtremeTableData.Column("col1", "Серийный номер", "string"),
new DevExtremeTableData.Column("col2", "Номер ячейки", "string")
}
};
const string sql = "select * from [Devices] where CellNumber>0";
var devices = await _db.Connection.QueryAsync <Device>(sql);
var list = devices.Select(o => new DeviceLabelDto
{
Id = o.Id,
Model = o.Name,
Barcode = o.Serial,
CellNumber = o.CellNumber
}).ToList();
foreach (var item in list)
{
result.Rows.Add(new DevExtremeTableData.Row
{
Id = new DevExtremeTableData.RowId {
Id = item.Id, Type = 0
},
ParentId = null,
HasItems = false.ToString(),
Col0 = item.Model,
Col1 = item.Barcode,
Col2 = item.CellNumber.ToString()
});
}
result.Rows = DevExtremeTableUtils.DevExtremeTableFiltering(result.Rows, input.Filters);
result.Rows = DevExtremeTableUtils.DevExtremeTableSorting(result.Rows, input.Sortings);
result.Total = result.Rows.Count.ToString();
result.Paging(input.Paging);
return(result);
}
public async Task <DevExtremeTableData.ReportResponse> GetTable(TvPanelRequest input)
{
var result = new DevExtremeTableData.ReportResponse
{
Rows = new List <DevExtremeTableData.Row>(),
Columns = new List <DevExtremeTableData.Column>
{
new DevExtremeTableData.Column("col0", "Наименование", "string")
}
};
if (input.ParentId == null)
{
//1st level, boxes
const string sql = @"SELECT * FROM [TvBoxes]";
var boxes = await _db.Connection.QueryAsync <TVBox>(sql);
foreach (var box in boxes)
{
result.Rows.Add(new DevExtremeTableData.Row
{
Id = new DevExtremeTableData.RowId {
Id = box.Id, Type = (int)TvPanelLevelEnum.Box
},
ParentId = null,
HasItems = true.ToString(),
Col0 = box.Name
});
}
}
else
{
if (input.ParentId.Type == (int)TvPanelLevelEnum.Box)
{
//query from 1st level - for 2nd level data
const string sql = @"select * FROM [TvPanels] p left join [TvBoxes] b on b.Id=p.TVBoxId where b.Id=@Id";
var items = await _db.Connection.QueryAsync <TVPanel, TVBox, TVPanel>(sql,
(panel, box) =>
{
panel.TVBox = box;
return(panel);
},
new { Id = input.ParentId.Id }
);
foreach (var groupNum in items.GroupBy(o => o.Number))
{
var monNum = groupNum.Key;
result.Rows.Add(new DevExtremeTableData.Row
{
Id = new DevExtremeTableData.RowId {
Id = input.ParentId.Id, ScreenNum = monNum, Type = (int)TvPanelLevelEnum.Screen
},
ParentId = input.ParentId,
HasItems = true.ToString(),
Col0 = $"Экран {monNum}"
});
}
}
else if (input.ParentId.Type == (int)TvPanelLevelEnum.Screen && input.ParentId.ScreenNum.HasValue)
{
const string sql = @"select * FROM [TvPanels] p left join [TvBoxes] b on b.Id=p.TVBoxId where b.Id=@Id and p.Number=@Number";
var items = await _db.Connection.QueryAsync <TVPanel, TVBox, TVPanel>(sql,
(panel, box) =>
{
panel.TVBox = box;
return(panel);
},
new { Id = input.ParentId.Id, Number = input.ParentId.ScreenNum.Value }
);
foreach (var item in items)
{
result.Rows.Add(new DevExtremeTableData.Row
{
Id = new DevExtremeTableData.RowId {
Id = item.Id, Type = (int)TvPanelLevelEnum.Type
},
ParentId = input.ParentId,
HasItems = false.ToString(),
Col0 = ScreenTypeEnumToString(item.ScreenType)
});
}
}
}
result.Rows = DevExtremeTableUtils.DevExtremeTableFiltering(result.Rows, input.Filters);
result.Rows = DevExtremeTableUtils.DevExtremeTableSorting(result.Rows, input.Sortings);
result.Total = result.Rows.Count.ToString();
result.Paging(input.Paging);
return(result);
}
public async Task <DevExtremeTableData.ReportResponse> GetTable(DeviceRequest input)
{
var result = new DevExtremeTableData.ReportResponse();
using (var conn = new SqlConnection(AppSettings.ConnectionString))
{
var sql = @"select d.*,LastStatus.*,LastCharge.*,u.*,
(SELECT Count(*) FROM DeviceTasks t OUTER APPLY(SELECT TOP 1 * FROM DeviceTaskComments c WHERE t.Id = c.DeviceTaskId ORDER BY c.Date DESC) as LastComment WHERE LastComment.Status <> 2 And t.DeviceId = d.id) as Id
from [Devices] d
OUTER APPLY(SELECT TOP 1 * FROM DeviceHistories h WHERE h.DeviceId = d.Id ORDER BY h.UpdateDate DESC) as LastStatus
OUTER APPLY(SELECT TOP 1 * FROM DeviceValues v WHERE v.DeviceId = d.Id ORDER BY v.UpdateDate DESC) as LastCharge
left join [auth_users] u on u.Id=LastStatus.UserId";
var items = (await conn.QueryAsync <Device, DeviceHistory, DeviceValue, User, int, DeviceTableItemDto>(
sql, (device, history, value, historyUser, openTaskCnt) =>
{
var item = new DeviceTableItemDto
{
Id = device.Id,
Cell = device.CellNumber > 0 ? $"{device.CellNumber}" : "-",
Serial = device.Serial,
Model = device.Name,
ChargePercent = value?.Value,
IsDeleted = device.CellNumber <= 0,
InRepair = openTaskCnt > 0,
};
string status = "";
DateTime?dateIssued = null;
string lastUser = null;
switch (history?.Operation)
{
case DeviceOperation.Surrender:
status = "на месте";
lastUser = GetShortFio(historyUser?.Name);
break;
case DeviceOperation.Issue:
dateIssued = history.UpdateDate;
status = "отсутствует";
lastUser = GetShortFio(historyUser?.Name);
break;
}
item.Status = status;
item.DateIssued = dateIssued;
item.LastUser = lastUser;
return(item);
})).ToList();
result.Columns = new List <DevExtremeTableData.Column>
{
new DevExtremeTableData.Column("col0", "Ячейка", "number"),
new DevExtremeTableData.Column("col1", "Модель", "string"),
new DevExtremeTableData.Column("col2", "Серийный номер", "string"),
new DevExtremeTableData.Column("col3", "Статус", "string"),
new DevExtremeTableData.Column("col4", "Посл.пользователь", "string"),
new DevExtremeTableData.Column("col5", "В ремонте", "boolean"),
new DevExtremeTableData.Column("col6", "Дата выдачи", "date"),
new DevExtremeTableData.Column("col7", "Процент заряда", "string"),
new DevExtremeTableData.Column("col8", "Удалено", "boolean"),
};
result.Rows = new List <DevExtremeTableData.Row>();
if (items.Count > 0)
{
foreach (var item in items)
{
result.Rows.Add(new DevExtremeTableData.Row
{
Id = new DevExtremeTableData.RowId {
Id = item.Id, Type = 0
},
HasItems = false.ToString(),
Col0 = item.Cell,
Col1 = item.Model,
Col2 = item.Serial,
Col3 = item.Status,
Col4 = item.LastUser,
Col5 = item.InRepair.BoolToStringRussian(),
Col6 = item.DateIssued?.ToStringDateTime(),
Col7 = item.ChargePercent.ToString(),
Col8 = item.IsDeleted.BoolToStringRussian()
});
}
}
}
result.Rows = DevExtremeTableUtils.DevExtremeTableFiltering(result.Rows, input.Filters);
result.Rows = DevExtremeTableUtils.DevExtremeTableSorting(result.Rows, input.Sortings);
result.Total = result.Rows.Count.ToString();
result.Paging(input.Paging);
return(result);
}
public async Task <DevExtremeTableData.ReportResponse> GetTable(DeviceTaskRequest input)
{
using (var conn = new SqlConnection(AppSettings.ConnectionString))
{
var result = new DevExtremeTableData.ReportResponse();
const string sql = @"SELECT t.*,d.*,f.*,u.*,LastComment.* FROM [DeviceTasks] t
LEFT JOIN [Devices] d on d.Id=t.DeviceId
LEFT JOIN [DeviceFaults] f on f.Id=t.DeviceFaultId
LEFT JOIN [auth_users] u on u.Id=t.UserId
OUTER APPLY (SELECT TOP 1 * FROM [DeviceTaskComments] c WHERE t.Id = c.DeviceTaskId ORDER BY c.Date DESC) as LastComment
ORDER BY t.CreateDate DESC";
var items = (await conn.QueryAsync <DeviceTask, Device, DeviceFault, User, DeviceTaskComment, DeviceTaskItemDto>(
sql, (task, device, fault, user, comment) =>
{
return(new DeviceTaskItemDto
{
Id = task.Id,
Date = task.CreateDate,
Device = $"{device.Name} ({device.CellNumber})",
DeviceFault = fault.Name,
User = user.Name,
Status = TaskStatusEnumtoString(comment.Status)
});
})).ToList();
result.Columns = new List <DevExtremeTableData.Column>
{
new DevExtremeTableData.Column("col0", "ИД", "number"),
new DevExtremeTableData.Column("col1", "Статус", "string"),
new DevExtremeTableData.Column("col2", "Устройство", "string"),
new DevExtremeTableData.Column("col3", "Инициатор", "string"),
new DevExtremeTableData.Column("col4", "Дата", "date"),
new DevExtremeTableData.Column("col5", "Типовая неисправность", "string"),
};
result.Rows = new List <DevExtremeTableData.Row>();
if (items.Count > 0)
{
foreach (var item in items)
{
result.Rows.Add(new DevExtremeTableData.Row
{
Id = new DevExtremeTableData.RowId {
Id = item.Id, Type = 0
},
HasItems = false.ToString(),
Col0 = item.Id.ToString(),
Col1 = item.Status,
Col2 = item.Device,
Col3 = item.User,
Col4 = item.Date.ToStringDateTime(),
Col5 = item.DeviceFault
});
}
}
result.Rows = DevExtremeTableUtils.DevExtremeTableFiltering(result.Rows, input.Filters);
result.Rows = DevExtremeTableUtils.DevExtremeTableSorting(result.Rows, input.Sortings);
result.Total = result.Rows.Count.ToString();
result.Paging(input.Paging);
return(result);
}
}
//table
public async Task <DevExtremeTableData.ReportResponse> GetTable(DepoEventsRequest input)
{
var result = new DevExtremeTableData.ReportResponse();
var sql = @"select * from [DepoEvents] e
left join [Parkings] p on p.Id=e.ParkingId
left join [Inspections] i on i.Id=e.InspectionId
left join [auth_users] ui on ui.Id=i.UserId
left join [Brigades] b on b.Id=ui.BrigadeId
left join [Trains] t on t.Id=e.TrainId
left join [Routes] r on r.Id=e.RouteId
left join [auth_users] u on u.Id=e.UserId";
var items = (await _db.Connection.QueryAsync(sql,
new[]
{
typeof(DepoEvent), typeof(Parking), typeof(Inspection), typeof(User), typeof(Brigade), typeof(Train), typeof(Route), typeof(User)
},
BindToModel)).ToList();
result.Columns = new List <DevExtremeTableData.Column>
{
new DevExtremeTableData.Column("col0", "Место постановки", "string"),
new DevExtremeTableData.Column("col1", "Состав", "string"),
new DevExtremeTableData.Column("col2", "Маршрут", "string"),
new DevExtremeTableData.Column("col3", "Время захода в депо", "date"),
new DevExtremeTableData.Column("col4", "Время постановки на позицию", "string"),
new DevExtremeTableData.Column("col5", "Инспекция", "string"),
new DevExtremeTableData.Column("col6", "Время окончания ремонта", "date"),
new DevExtremeTableData.Column("col7", "Начало проверки под напряжением", "date"),
new DevExtremeTableData.Column("col8", "Окончание проверки под напряжением", "date"),
new DevExtremeTableData.Column("col9", "Проверяющий", "string"),
};
result.Rows = new List <DevExtremeTableData.Row>();
if (items.Count > 0)
{
foreach (var item in items)
{
result.Rows.Add(new DevExtremeTableData.Row
{
Id = new DevExtremeTableData.RowId {
Id = item.Id, Type = 0
},
HasItems = false.ToString(),
Col0 = item.Parking.Name,
Col1 = item.Train.Name,
Col2 = item.Route.Name,
Col3 = item.InTime.ToStringDateTime(),
Col4 = item.ParkingTime?.ToStringDateTime(),
Col5 = item.Inspection == null ? item.InspectionTxt : item.Inspection.Name,
Col6 = item.RepairStopTime?.ToStringDateTime(),
Col7 = item.TestStartTime?.ToStringDateTime(),
Col8 = item.TestStopTime?.ToStringDateTime(),
Col9 = item.TesterUser?.Name
});
}
}
result.Rows = DevExtremeTableUtils.DevExtremeTableFiltering(result.Rows, input.Filters);
result.Rows = DevExtremeTableUtils.DevExtremeTableSorting(result.Rows, input.Sortings);
result.Total = result.Rows.Count.ToString();
result.Paging(input.Paging);
return(result);
}