//SqlConnection cn = new SqlConnection("Data Source=118.247.29.138\\SQLEXPRESS; Initial Catalog=EES.BBVA.2018v1; User ID = SA; Password=everis@2017;");
private void ListarData()
{
try
{
string query = "SELECT wf.[Name] as 'WorkFlow', (SELECT [Name] FROM [EES.BBVA.2018v1].[dbo].[States] WHERE [Id] = (SELECT [StateId] FROM [EES.BBVA.2018v1].[dbo].[StateActions] WHERE [Id] = (SELECT [StateActionId] FROM [EES.BBVA.2018v1].[dbo].[TicketHistories] WHERE [Id] = (SELECT MAX(th.[Id]) FROM [EES.BBVA.2018v1].[dbo].[TicketHistories] th WHERE th.[TicketId] = tk.[Id] AND th.[HistoryType] = 2)))) as 'Ultimo Estado','Tarea: ' as 'Ticket',tk.[Id],'Exped: ' as 'Exp',tk.[Description],st.[Name],tk.[StateId],tk.[ParentId],tk.[UserId],tk.[RobotVirtualMachineId]" +
",case when((tk.StateId = 1061) or(tk.StateId = 1135)) then concat(datediff(SECOND, dateadd(hour, -5, tk.[CreationDate]), dateadd(hour, -5, tk.[LastModified])) / 60, ':', datediff(SECOND, dateadd(hour, -5, tk.[LastModified]), dateadd(hour, -5, tk.[LastModified])) % 60) Else concat(datediff(SECOND, dateadd(hour, -5, tk.[CreationDate]), getdate()) / 60, ':', datediff(SECOND, dateadd(hour, -5, tk.[CreationDate]), getdate()) % 60) End as 'Creación' " +
",concat(datediff(SECOND, dateadd(hour, -5, tk.[LastModified]), getdate()) / 60, ':', datediff(SECOND, dateadd(hour, -5, tk.[LastModified]), getdate()) % 60) as 'Ejecución'" +
",tv.[Value] as 'Error',convert(varchar(20),dateadd(hour,-5, tk.[CreationDate]), 100) as 'CreationDate',convert(varchar(20),dateadd(hour,-5, tk.[LastModified]), 100) as 'LastModified',convert(varchar(10),tk.[CreationDate],103) as 'FechaCreacion',tk.[Priority] FROM [EES.BBVA.2018v1].[dbo].[Tickets] tk INNER JOIN [EES.BBVA.2018v1].[dbo].[States] st on st.[Id] = tk.[StateId] INNER JOIN [EES.BBVA.2018v1].[dbo].[Workflows] wf on wf.[Id] = st.[WorkflowId] LEFT JOIN [EES.BBVA.2018v1].[dbo].[TicketValues] tv on (tv.[TicketId] = tk.[Id] AND tv.[FieldId] = 34) " +
"WHERE wf.[Id] in (8,9) " +
"ORDER BY tk.id desc ";
p = new Paginar(query, "DataMember1", maximo_x_pagina);
dgvResultados.DataSource = p.cargar();
dgvResultados.DataMember = "datamember1";
actualizar();
/*
* SqlCommand cmd = new SqlCommand("SELECT wf.[Name] as 'WorkFlow', (SELECT [Name] FROM [EES.BBVA.2018v1].[dbo].[States] WHERE [Id] = (SELECT [StateId] FROM [EES.BBVA.2018v1].[dbo].[StateActions] WHERE [Id] = (SELECT [StateActionId] FROM [EES.BBVA.2018v1].[dbo].[TicketHistories] WHERE [Id] = (SELECT MAX(th.[Id]) FROM [EES.BBVA.2018v1].[dbo].[TicketHistories] th WHERE th.[TicketId] = tk.[Id] AND th.[HistoryType] = 2)))) as 'Ultimo Estado','Tarea: ' as 'Ticket',tk.[Id],'Exped: ' as 'Exp',tk.[Description],st.[Name],tk.[StateId],tk.[ParentId],tk.[UserId],tk.[RobotVirtualMachineId]" +
* ",case when((tk.StateId = 1061) or(tk.StateId = 1135)) then concat(datediff(SECOND, dateadd(hour, -5, tk.[CreationDate]), dateadd(hour, -5, tk.[LastModified])) / 60, ':', datediff(SECOND, dateadd(hour, -5, tk.[LastModified]), dateadd(hour, -5, tk.[LastModified])) % 60) Else concat(datediff(SECOND, dateadd(hour, -5, tk.[CreationDate]), getdate()) / 60, ':', datediff(SECOND, dateadd(hour, -5, tk.[CreationDate]), getdate()) % 60) End as 'Creación' " +
* ",concat(datediff(SECOND, dateadd(hour, -5, tk.[LastModified]), getdate()) / 60, ':', datediff(SECOND, dateadd(hour, -5, tk.[LastModified]), getdate()) % 60) as 'Ejecución'" +
* ",tv.[Value] as 'Error',convert(varchar(20),dateadd(hour,-5, tk.[CreationDate]), 100) as 'CreationDate',convert(varchar(20),dateadd(hour,-5, tk.[LastModified]), 100) as 'LastModified',tk.[Priority] FROM [EES.BBVA.2018v1].[dbo].[Tickets] tk INNER JOIN [EES.BBVA.2018v1].[dbo].[States] st on st.[Id] = tk.[StateId] INNER JOIN [EES.BBVA.2018v1].[dbo].[Workflows] wf on wf.[Id] = st.[WorkflowId] LEFT JOIN [EES.BBVA.2018v1].[dbo].[TicketValues] tv on (tv.[TicketId] = tk.[Id] AND tv.[FieldId] = 34) " +
* "WHERE wf.[Id] in (8,9) AND CAST(tk.[CreationDate] AS DATE) = CAST('2018-08-20' AS DATE) ORDER BY tk.id desc ", cn);
* SqlDataAdapter da = new SqlDataAdapter(cmd);
* DataTable dt = new DataTable();
* da.Fill(0,5,dt);
* dgvResultados.DataSource = dt;
* cargarGrid();
* cn.Close();
*/
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
public IActionResult ObterPedidos(int pagina, int quantidade)
{
try
{
var pedidos = _pedidoService.ObterPedidos();
var paginacao = Paginar <Pedido> .Pagine(pedidos, pagina, quantidade);
return(Ok(
new RetornoPaginacaoDto(
paginacao.TotalPaginas,
paginacao.QuantidadeTotal,
paginacao.Pagina,
paginacao.Dados
)
));
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
public IActionResult ObterProdutos(int pagina, int quantidadePagina, int?categoriaId = null)
{
try
{
var produtos = _produtoService.ObterProdutos(categoriaId);
var paginacao = Paginar <Produto> .Pagine(produtos, pagina, quantidadePagina);
return(Ok(
new RetornoPaginacaoDto(
paginacao.TotalPaginas,
paginacao.QuantidadeTotal,
paginacao.Pagina,
paginacao.Dados
)
));
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
public IActionResult ObterCategoriasPaginada(int pagina, int quantidadePagina)
{
try
{
var categorias = _categoriaService.ObterCategorias();
var paginacao = Paginar <Categoria> .Pagine(categorias, pagina, quantidadePagina);
return(Ok(
new RetornoPaginacaoDto(
paginacao.TotalPaginas,
paginacao.QuantidadeTotal,
paginacao.Pagina,
paginacao.Dados
)
));
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
public static IEnumerable <T> Select <T, TKey>(this DbSet <T, TKey> dbSet)
where T : IEntidade <TKey>, new()
{
var filtro = new ExpressionSerializer(new JsonSerializer()).SerializeText(dbSet.Filtro);
var paginacao = new Paginar <TKey>
{
Quantidade = -1,
Filtro = filtro
};
IList <T> lista = null;
dbSet.Db.Listar(paginacao)
.Match(
_ => throw new DesignException(_.Message),
e => { lista = e.Dados; });
foreach (var a in lista)
{
yield return(a);
}
}
