public IHttpActionResult PQRS(FormDataCollection form) { try { string displayStart = form["start"]; string displayLength = form["length"]; string cliente = form["_cliente"]; string sqlWhere = ""; if (cliente.Trim() != "") { sqlWhere += "ClienteId like '%" + cliente + "%' AND "; } if (sqlWhere.Trim() != "") { sqlWhere = " WHERE " + sqlWhere; } sqlWhere = Fn.RemoveLastString(sqlWhere, "AND "); string sqlCount = ""; string sqlData = ""; if (sqlWhere.Trim() != "") { sqlCount = "select COUNT(Id) from PQRSView" + sqlWhere; sqlData = "SELECT * FROM (select Row_number() OVER( ORDER BY id DESC) AS Seq, * from PQRSView " + sqlWhere + $") AS T2 WHERE Seq BETWEEN {int.Parse(displayStart) + 1} AND {(int.Parse(displayStart) + int.Parse(displayLength))}"; } else { sqlCount = "select COUNT(Id) from PQRSView where 1 = 0"; sqlData = "SELECT * FROM (select Row_number() OVER( ORDER BY id DESC) AS Seq, * from PQRSView " + sqlWhere + $") AS T2 WHERE Seq BETWEEN 0 AND 0"; } int count = (int)db.FillScalarSql(sqlCount); var data = db.FillDynamicCollectionSql(sqlData); return(Ok(new SysDataTablePager() { draw = form["draw"], recordsTotal = count, recordsFiltered = count, data = data })); } catch (Exception ex) { return(InternalServerError(ex)); } }
public IHttpActionResult Index(FormDataCollection form) { try { string displayStart = form["start"]; string displayLength = form["length"]; string fecha = form["_fecha"]; string usuario = form["_usuario"]; string eventoId = form["_evento"]; string key = form["_key"]; string cliente = form["_cliente"]; string sqlWhere = ""; if (fecha.Trim() != "") { sqlWhere += "Fecha LIKE '%" + fecha + "%' AND "; } if (usuario.Trim() != "") { sqlWhere += "Usuario LIKE '%" + usuario + "%' AND "; } if (eventoId.Trim() != "") { sqlWhere += "EventoId = " + eventoId + " AND "; } if (key.Trim() != "") { sqlWhere += "[Key] LIKE '%" + key + "%' AND "; } if (cliente.Trim() != "") { sqlWhere += "Cliente LIKE '%" + cliente + "%' AND "; } if (sqlWhere.Trim() != "") { sqlWhere = "WHERE " + sqlWhere; } sqlWhere = Fn.RemoveLastString(sqlWhere, "AND "); string sqlCount = "SELECT COUNT(Id) FROM ( " + "SELECT A.Id, CONVERT(nvarchar, A.Fecha, 103) + ' ' + CONVERT(nvarchar, A.Fecha, 108) AS Fecha, A.Usuario, A.EventoId, A.[Key], A.Cliente " + "FROM dbo.Log AS A " + ") AS T " + sqlWhere; string sqlData = "SELECT * FROM ( " + "SELECT ROW_NUMBER() OVER (ORDER BY Id DESC) AS Seq, * FROM ( " + "SELECT A.Id, CONVERT(nvarchar, A.Fecha, 103) + ' ' + CONVERT(nvarchar, A.Fecha, 108) AS Fecha, A.Usuario, A.EventoId, E.Descripcion AS Evento, A.[Key], A.Cliente, CASE WHEN A.Data IS NULL THEN CONVERT(BIT, 0) ELSE CONVERT(BIT, 1) END AS HasData " + "FROM dbo.Log AS A " + "INNER JOIN dbo.Evento AS E ON A.EventoId=E.Id " + ") AS T " + sqlWhere + $") AS T2 WHERE Seq BETWEEN {int.Parse(displayStart) + 1} AND {(int.Parse(displayStart) + int.Parse(displayLength))}"; int count = (int)db.FillScalarSql(sqlCount); var data = db.FillDynamicCollectionSql(sqlData); return(Ok(new SysDataTablePager() { draw = form["draw"], recordsTotal = count, recordsFiltered = count, data = data })); } catch (Exception ex) { return(InternalServerError(ex)); } }
public IHttpActionResult Index(FormDataCollection form) { try { string displayStart = form["start"]; string displayLength = form["length"]; string id = form["_id"]; string asunto = form["_asunto"]; string persona = form["_persona"]; string cliente = form["_cliente"]; string prioridad = form["_prioridad"]; string analista = form["_analista"]; string fechacreacion = form["_fechacreacion"]; string estado = form["_estado"]; string nrotracking = form["_nrotracking"]; string sqlWhere = ""; if (id.Trim() != "") { sqlWhere += "Id = " + id + " AND "; } if (asunto.Trim() != "") { sqlWhere += "Asunto LIKE '%" + asunto + "%' AND "; } if (persona.Trim() != "") { sqlWhere += "Persona LIKE '%" + persona + "%' AND "; } if (cliente.Trim() != "") { sqlWhere += "Cliente LIKE '%" + cliente + "%' AND "; } if (prioridad.Trim() != "") { sqlWhere += "Prioridad LIKE '%" + prioridad + "%' AND "; } if (analista.Trim() != "") { sqlWhere += "Analista LIKE '%" + analista + "%' AND "; } if (fechacreacion.Trim() != "") { sqlWhere += "FechaCreacion LIKE '%" + fechacreacion + "%' AND "; } if (estado.Trim() != "") { sqlWhere += "Estado LIKE '%" + estado + "%' AND "; } if (nrotracking.Trim() != "") { sqlWhere += "NroTracking LIKE '%" + nrotracking + "%' AND "; } string clienteIds = string.Join(" , ", Seguridadcll.ClienteList.ToList().Select(e => "'" + e.ClienteID + "'")); if (clienteIds.Trim() != "") { sqlWhere += "ClienteId in (" + clienteIds + ") AND "; } else { sqlWhere += " 0 = 1 AND "; } if (sqlWhere.Trim() != "") { sqlWhere = " WHERE " + sqlWhere; } sqlWhere = Fn.RemoveLastString(sqlWhere, "AND "); string sqlCount = "SELECT COUNT(Id) FROM ( " + "SELECT D.Id,D.Asunto,D.ClienteId, D.ClienteId + ' - ' + C.ClienteRazonSocial AS Cliente, D.AnalistaId + ' - ' + U.UsuarioNombre AS Analista, " + " CONVERT(nvarchar, D.FechaCreacion, 103) +' ' + CONVERT(nvarchar, D.FechaCreacion, 108) AS FechaCreacion, " + " case D.Estado when 1 then 'Open' when 2 then 'In Process' when 3 then 'Completed' when 4 then 'Deleted' else 'Sin Estado' end as Estado, NroTracking," + " CASE D.TipoPersona when 1 then UE.UsuarioNombre when 2 then D.ClienteId + ' - ' + C.ClienteRazonSocial else D.Persona end as Persona, " + " CASE D.Prioridad when 1 then 'Baja' when 2 then 'Media' when 3 then 'Alta' end as Prioridad " + "FROM Novedad AS D " + "INNER JOIN Cliente AS C ON D.ClienteId = C.ClienteID " + "LEFT JOIN Usuario AS U ON D.AnalistaId = U.UsuarioId " + "LEFT JOIN Usuario AS UE ON D.Persona = UE.UsuarioId and D.TipoPersona = 1 " + ") as T" + sqlWhere; string sqlData = "SELECT * FROM (" + "SELECT ROW_NUMBER() OVER(ORDER BY Id DESC) AS Seq, * FROM ( " + "SELECT D.Id, D.Asunto,D.ClienteId, D.ClienteId + ' - ' + C.ClienteRazonSocial AS Cliente, D.AnalistaId + ' - ' + U.UsuarioNombre AS Analista, " + "CONVERT(nvarchar, D.FechaCreacion, 103) AS FechaCreacion, " + "case D.Estado when 1 then 'Open' when 2 then 'In Process' when 3 then 'Completed' when 4 then 'Deleted' else 'Sin Estado' end as Estado, NroTracking," + " CASE D.TipoPersona when 1 then UE.UsuarioNombre when 2 then D.ClienteId + ' - ' + C.ClienteRazonSocial else D.Persona end as Persona, " + " CASE D.Prioridad when 1 then 'Baja' when 2 then 'Media' when 3 then 'Alta' end as Prioridad " + "FROM Novedad AS D " + "INNER JOIN Cliente AS C ON D.ClienteId = C.ClienteID " + "LEFT JOIN Usuario AS U ON D.AnalistaId = U.UsuarioId " + "LEFT JOIN Usuario AS UE ON D.Persona = UE.UsuarioId and D.TipoPersona = 1 " + ") as T" + sqlWhere + $") AS T2 WHERE Seq BETWEEN {int.Parse(displayStart) + 1} AND {(int.Parse(displayStart) + int.Parse(displayLength))}"; int count = (int)db.FillScalarSql(sqlCount); var data = db.FillDynamicCollectionSql(sqlData); return(Ok(new SysDataTablePager() { draw = form["draw"], recordsTotal = count, recordsFiltered = count, data = data })); } catch (Exception ex) { return(InternalServerError(ex)); } // return InternalServerError(""); }