public IList <ClienteProductoModel> ConsultarClientes(ClienteProductoModel clienteProductoModel)
{
try
{
iGenericDataAccess.OpenConnection();
//Se cambia para optimizar la consulta.
IList <VwNueListaClientesProducto> lista = iGenericDataAccess
.ExecuteQuery <VwNueListaClientesProducto>(
"SELECT " +
"total.Nombre as NombreCliente" +
", total.idCliente as IdCliente" +
" FROM" +
"(" +
" SELECT " +
" p.Nombre " +
" , ttC.idCliente " +
" , ROW_NUMBER() over(order by ttC.idCliente) as RowNumber " +
" , RANK()over(order by ttC.idCliente) as Rank " +
" FROM dbo.TiposTicketsClientes ttC " +
" INNER JOIN( " +
" SELECT " +
" pD.Valor " +
" FROM " +
" dbo.PerfilDatos pD " +
" Where ISNUMERIC(pD.Valor) = 1 " +
" AND pD.Opcion = 1169 " +
" AND pd.PersonaID = " + clienteProductoModel.GetIdUsuarioSesion() + " AND pd.PerfilID =" + clienteProductoModel.GetIdPerfilUsuarioSesion() + " " +
" ) as pD " +
" ON " +
" ttC.IdCliente = pD.Valor " +
" INNER JOIN dbo.nePersonas p " +
" ON p.PersonaID = pD.Valor and p.Tipo=212 " +
" ) as total" +
" where Rank = RowNumber AND total.Nombre LIKE \'%" + clienteProductoModel.NombreCliente + "%\'");
iGenericDataAccess.CloseConnection();
IList <ClienteProductoModel> clientesList = lista.Select(
x => new ClienteProductoModel()
{
IdCliente = x.IdCliente,
NombreCliente = x.NombreCliente
}).ToList();
return(clientesList);
}
catch (Exception e)
{
iGenericDataAccess.CloseConnection();
throw new DalException(CodesTickets.ERR_00_04, e);
}
}
public IList <ClienteProductoModel> ConsultarClientesConfigurarParametros(ClienteProductoModel clienteProductoModel)
{
try
{
iGenericDataAccess.OpenConnection();
IList <VwNueListaClientesProducto> lista = iGenericDataAccess.ExecuteQuery <VwNueListaClientesProducto>("SELECT " +
" total.Nombre as NombreCliente " +
" , total.idCliente as IdCliente " +
" FROM " +
" ( " +
" SELECT " +
" p.Nombre " +
" , pD.idCliente " +
" , ROW_NUMBER() over(order by pD.idCliente) as RowNumber " +
" , RANK()over(order by pD.idCliente) as Rank " +
" FROM( " +
" SELECT " +
" p.Nombre " +
" , p.PersonaID " +
" , p.Tipo " +
" From dbo.nePersonas p " +
" WHERE p.Tipo = 212 AND Nombre LIKE '%" + clienteProductoModel.NombreCliente + "%' " +
" ) as p " +
" INNER JOIN( " +
" SELECT " +
" pD.Valor as idCliente " +
" FROM " +
" dbo.PerfilDatos pD " +
" Where ISNUMERIC(pD.Valor) = 1 " +
" AND pD.Opcion = 1169 " +
" AND pD.PerfilID = " + clienteProductoModel.GetIdPerfilUsuarioSesion() +
" AND pD.PersonaID = " + clienteProductoModel.GetIdUsuarioSesion() + " " +
" ) as pD " +
" ON p.PersonaID = pD.idCliente " +
" ) as total " +
" where Rank = RowNumber ");
iGenericDataAccess.CloseConnection();
IList <ClienteProductoModel> listClienList = lista.Select(x => new ClienteProductoModel()
{
IdCliente = x.IdCliente,
NombreCliente = x.NombreCliente
}).ToList();
return(listClienList);
}
catch (Exception e)
{
iGenericDataAccess.CloseConnection();
throw new DalException(CodesConfigParamTickets.ERR_08_02, e);
}
}