public static Rol[] obtenerRoles()
{
List <Rol> roles = new List <Rol>();
string query = "select * from Rol";
SqlDataReader dataReader = DBReader.getDataReader(query);
while (dataReader.Read())
{
Rol rol = new Rol();
rol.id = dataReader.GetInt32(0);
rol.nombre = dataReader.GetString(1);
roles.Add(rol);
}
dataReader.Close();
return(roles.ToArray());
}
public static Usuario[] obtenerUsuarios()
{
List <Usuario> usuarios = new List <Usuario>();
string query = "select U.UsuarioID, U.Cuenta, U.Nombre, R.Nombre from Usuario U inner join Rol R on U.RolID = R.RolID";
SqlDataReader dataReader = DBReader.getDataReader(query);
while (dataReader.Read())
{
Usuario usuario = new Usuario();
usuario.id = dataReader.GetInt32(0);
usuario.cuenta = dataReader.GetString(1);
usuario.nombre = dataReader.GetString(2);
usuario.rol = dataReader.GetString(3);
usuarios.Add(usuario);
}
dataReader.Close();
return(usuarios.ToArray());
}
public static void modificarUsuario(int idUsuario, string nombre, int idRol, string contra)
{
string query = "update Usuario set Nombre = @nombre, RolID = @rol";
if (contra != "")
{
query += ", Contrasenia = @contra";
}
query += " where UsuarioID = @id";
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("id", idUsuario),
new SqlParameter("nombre", nombre),
new SqlParameter("rol", idRol)
};
if (contra != "")
{
parameters.Add(new SqlParameter("contra", contra));
}
DBReader.execute(query, parameters);
}
public static DataTable dtUsuarios()
{
string query = "select U.UsuarioID, U.Cuenta as 'Usuario', U.Nombre, R.Nombre as 'Rol' from Usuario U inner join Rol R on U.RolID = R.RolID";
return(DBReader.getDataTable(query));
}
public static DataTable asociadosCategoria(bool A, bool B, bool C, bool D, bool F)
{
string query = "SELECT A.Codigo, A.Nombre, MAX(C.Categoria) 'Categoria' FROM Asociado A INNER JOIN Credito C ON A.AsociadoID = C.AsociadoID where C.Categoria in ";
if (B && C && D && F)
{
query += "('F', 'C', 'D', 'B')";
}
else if (B && C && D)
{
query += "('B', 'C', 'D')";
}
else if (B && C && F)
{
query += "('B', 'C', 'F')";
}
else if (F && C && D)
{
query += "('F', 'C', 'D')";
}
else if (B && C)
{
query += "('B', 'C')";
}
else if (B && D)
{
query += "('B', 'D')";
}
else if (B && F)
{
query += "('B', 'F')";
}
else if (C && D)
{
query += "('C', 'D')";
}
else if (C && F)
{
query += "('C', 'F')";
}
else if (D && F)
{
query += "('D', 'F')";
}
else if (B)
{
query += "('B')";
}
else if (C)
{
query += "('C')";
}
else if (D)
{
query += "('D')";
}
else if (F)
{
query += "('F')";
}
query += " group by A.Nombre, A.Codigo";
return(DBReader.getDataTable(query));
}
public static DataTable mosososHistoricos(int idPeriodo)
{
string query = "SELECT A.Codigo 'Asociado', A.Nombre, C.Codigo 'Credito', C.TieneMora 'Mora Actual', C.Saldo 'Saldo', CH.ValorMora 'Mora' FROM Asociado A INNER JOIN Credito C ON A.AsociadoID = C.AsociadoID INNER JOIN CreditoHistorico CH ON C.CreditoID = CH.CreditoID WHERE CH.TieneMora = 1 AND CH.PeriodoID = " + idPeriodo.ToString();
return(DBReader.getDataTable(query));
}
public static DataTable mososidadPorTipoCredito(int idPeriodo)
{
string query = "SELECT C.Tipo, COUNT(CH.CrediHistoricoID) 'Cantidad', SUM(CH.ValorMora) 'Monto' FROM Credito C INNER JOIN CreditoHistorico CH ON C.CreditoID = CH.CreditoID WHERE CH.TieneMora = 1 AND CH.PeriodoID = " + idPeriodo.ToString() + " GROUP BY C.Tipo";
return(DBReader.getDataTable(query));
}
public static DataTable morososVigentes(bool B, bool C, bool D, bool F)
{
string query = "SELECT A.Codigo, A.Nombre, MAX(C.Categoria) 'Categoria', SUM(C.ValorMora) 'Mora', SUM(C.Saldo) 'Saldo' FROM Asociado A INNER JOIN Credito C ON A.AsociadoID = C.AsociadoID WHERE C.TieneMora = 1 and C.Categoria in ";
if (B && C && D && F)
{
query += "('F', 'C', 'D', 'B')";
}
else if (B && C && D)
{
query += "('B', 'C', 'D')";
}
else if (B && C && F)
{
query += "('B', 'C', 'F')";
}
else if (F && C && D)
{
query += "('F', 'C', 'D')";
}
else if (B && C)
{
query += "('B', 'C')";
}
else if (B && D)
{
query += "('B', 'D')";
}
else if (B && F)
{
query += "('B', 'F')";
}
else if (C && D)
{
query += "('C', 'D')";
}
else if (C && F)
{
query += "('C', 'F')";
}
else if (D && F)
{
query += "('D', 'F')";
}
else if (B)
{
query += "('B')";
}
else if (C)
{
query += "('C')";
}
else if (D)
{
query += "('D')";
}
else if (F)
{
query += "('F')";
}
query += " group by A.Nombre, A.Codigo";
return(DBReader.getDataTable(query));
}
public static DataTable recuperacion(int idInicio, int idFin)
{
string query = "SELECT C.CreditoID, P.Monto FROM Credito C INNER JOIN Pago P ON C.CreditoID = P.CreditoID WHERE C.NuevoPlan = 1 AND MONTH(P.Fecha) >= (SELECT mes FROM Periodo WHERE PeriodoID = " + idInicio.ToString() + ") AND MONTH(P.Fecha) <= (SELECT mes FROM Periodo WHERE PeriodoID = " + idFin.ToString() + ") AND YEAR(P.Fecha) >= (SELECT anio FROM Periodo WHERE PeriodoID = " + idInicio.ToString() + ") AND YEAR(P.Fecha) <= (SELECT anio FROM Periodo WHERE PeriodoID = " + idFin.ToString() + ")";
return(DBReader.getDataTable(query));
}
public static DataTable morosidadCuota(int idInicio, int idFin)
{
string query = "SELECT P.PeriodoID, COUNT(CASE WHEN C.Cuota <= 25 THEN 1 ELSE null END) 'Cant0', COUNT(CASE WHEN C.Cuota > 25 AND C.Cuota <= 50 THEN 1 ELSE null END) 'Cant25', COUNT(CASE WHEN C.Cuota > 50 AND C.Cuota <= 75 THEN 1 ELSE null END) 'Cant50', COUNT(CASE WHEN C.Cuota > 75 AND C.Cuota <= 100 THEN 1 ELSE null END) 'Cant75', COUNT(CASE WHEN C.Cuota > 100 AND C.Cuota <= 125 THEN 1 ELSE null END) 'Cant100', COUNT(CASE WHEN C.Cuota > 125 THEN 1 ELSE null END) 'Cant125' FROM Credito C INNER JOIN CreditoHistorico CH ON C.CreditoID = CH.CreditoID INNER JOIN Periodo P ON CH.PeriodoID = P.PeriodoID WHERE CH.TieneMora = 1 AND P.PeriodoID >= " + idInicio.ToString() + " AND P.PeriodoID <= " + idFin.ToString() + " GROUP BY P.PeriodoID";
return(DBReader.getDataTable(query));
}
public static DataTable perdidasAsociados(int idPeriodo)
{
string query = "SELECT A.Codigo 'AsociadoCod', A.Nombre, C.Codigo 'CreditoCod', CH.ValorMora FROM Asociado A INNER JOIN Credito C ON A.AsociadoID = C.AsociadoID INNER JOIN CreditoHistorico CH ON C.CreditoID = CH.CreditoID INNER JOIN Periodo P ON CH.PeriodoID = P.PeriodoID WHERE CH.TieneMora = 1 AND P.PeriodoID = " + idPeriodo.ToString();
return(DBReader.getDataTable(query));
}
public static DataTable perdidasFaltaCobro(int idInicio, int idFin)
{
string query = "SELECT Pe.PeriodoID, C.Cuota, (SELECT MAX(Pa.Fecha) FROM Pago Pa WHERE Pa.CreditoID = C.CreditoID AND Pa.Fecha < EOMONTH(DATEFROMPARTS(Pe.Anio, Pe.Mes, 1))) 'UltimoPago' FROM CreditoHistorico CH INNER JOIN Periodo Pe ON CH.PeriodoID = Pe.PeriodoID INNER JOIN Credito C ON C.CreditoID = CH.CreditoID WHERE Pe.PeriodoID >= " + idInicio.ToString() + " AND Pe.PeriodoID <= " + idFin.ToString();
return(DBReader.getDataTable(query));
}
public static DataTable indiceMorosidad(int idInicio, int idFin)
{
string query = "SELECT P.PeriodoID, SUM(CH.Saldo) 'Saldo', SUM(CASE WHEN CH.TieneMora = 1 THEN CH.Saldo ELSE 0 END) 'SaldoMora' FROM CreditoHistorico CH INNER JOIN Periodo P ON CH.PeriodoID = P.PeriodoID WHERE P.PeriodoID >= " + idInicio.ToString() + " AND P.PeriodoID <= " + idFin.ToString() + " GROUP BY P.PeriodoID";
return(DBReader.getDataTable(query));
}