public ActionResult Index(string fecha, string controlador, int?UsuarioId)
{
int empresaId = 0;
if (Session["EmpresaId"] != null)
{
Int32.TryParse(Session["EmpresaId"].ToString(), out empresaId);
}
// string usuId = User.Identity.GetUserId();
if (HttpContext.User.IsInRole("Cobrador"))
{
ViewBag.UsuarioId = new SelectList(db.usuario.Where(c => c.Estado == true && c.EmpresaId == empresaId && c.UsuarioId == UsuarioId).OrderBy(e => e.UsuNombre), "UsuarioId", "UsuNombre", UsuarioId);
}
else
{
ViewBag.UsuarioId = new SelectList(db.usuario.Where(c => c.Estado == true && c.EmpresaId == empresaId).OrderBy(e => e.UsuNombre), "UsuarioId", "UsuNombre", UsuarioId);
}
string strfecha = "";
// DateTime finMes = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month));
if (MiUtil.isDate(fecha))
{
ViewBag.CurrentFilter = fecha;
strfecha = MiUtil.fechaToSQL(DateTime.ParseExact(fecha, "dd/MM/yyyy", null), 0);
}
/*
* var q = @"SELECT cuota.CuotaId, cliente.Nit, cliente.Nombre, credito.CreditoId,credito.CreditoNro, cuota.Numero, cuota.Fecha, cuota.AbonoCapital, cuota.AbonoInteres,
* cuota.AbonoCapital + cuota.AbonoInteres + cuota.AjusteAbonoCapital+ cuota.AjusteAbonoInteres AS TotalCuota, SUM(IFNULL(abono.Valor, 0)) AS Abonos,
* (select count(0) from cuota where credito.CreditoId=cuota.CreditoId) as CantCuotas
* FROM credito INNER JOIN
* cuota ON credito.CreditoId = cuota.CreditoId LEFT OUTER JOIN
* cliente ON credito.ClienteId = cliente.ClienteId LEFT OUTER JOIN
* abono ON cuota.CuotaId = abono.CuotaId and abono.Estado=1";
* q=q+" WHERE (credito.Estado = 1) AND (Cuota.Fecha <= '"+strfecha+"') AND (cliente.EmpresaId = {0})";
* if(UsuarioId>0)
* q=q+" and credito.usuarioId='"+UsuarioId.ToString()+"'";
* q = q + " GROUP BY cuota.CuotaId, cliente.Nit, cliente.Nombre, credito.CreditoId, cuota.Numero, cuota.Fecha, ";
* q = q + " cuota.AbonoCapital, cuota.AbonoInteres, credito.Estado ORDER BY cliente.Nombre asc";
*/
ViewBag.controlador = controlador;
//var cxc = db.Database.SqlQuery<Cuotas>(q, empresaId);
//var final = from c in cxc where(c.Abonos < (c.AbonoCapital + c.AbonoInteres)) select c;
//return View(final.ToList());
return(View(getCuotasxCobrar(empresaId, fecha, UsuarioId)));
}
public IEnumerable <Cuotas> consulta(int empresaId, string iniMes, string finMes)
{
// int empresaId = Convert.ToInt32(Session["EmpresaId"]);
string strfecha1 = "", strfecha2 = "";
if (MiUtil.isDate(iniMes))
{
strfecha1 = MiUtil.fechaToSQL(DateTime.ParseExact(iniMes, "dd/MM/yyyy", null), 0);
}
if (MiUtil.isDate(finMes))
{
strfecha2 = MiUtil.fechaToSQL(DateTime.ParseExact(finMes, "dd/MM/yyyy", null), 0);
}
var q = @"SELECT cuota.CuotaId, cliente.Nit, cliente.Nombre, credito.CreditoId,credito.CreditoNro, cuota.Numero, cuota.Fecha,
cuota.AbonoCapital+IFNULL(cuota.AjusteAbonoCapital,0) as AbonoCapital,
cuota.AbonoInteres+IFNULL(cuota.AjusteAbonoInteres,0) as AbonoInteres,
cuota.AbonoCapital + cuota.AbonoInteres + IFNULL(cuota.AjusteAbonoCapital,0)+IFNULL(cuota.AjusteAbonoInteres,0) AS TotalCuota,
max(CASE WHEN Abono.Fecha IS NULL
THEN curdate() ELSE Abono.Fecha END) as FechaAbono,
SUM(IFNULL(abono.Valor, 0)) AS Abonos,
Cuota.AbonoCapital + Cuota.AbonoInteres+ IFNULL(Cuota.AjusteAbonoCapital,0)+IFNULL(Cuota.AjusteAbonoInteres,0)-SUM(CASE WHEN Abono.Valor IS NULL
THEN 0.0 ELSE Abono.Valor END) AS SaldoCuota
FROM credito INNER JOIN
cuota ON credito.CreditoId = cuota.CreditoId LEFT OUTER JOIN
cliente ON credito.ClienteId = cliente.ClienteId LEFT OUTER JOIN
abono ON cuota.CuotaId = abono.CuotaId AND Abono.Estado = 1";
q = q + " WHERE (credito.Estado = 1) AND ((Cuota.Fecha between '" + strfecha1 + "' and '" + strfecha2 + "')";
//q = q + " or ((abono.Fecha between '" + strfecha1 + "' and '" + strfecha2 + "')))";
q = q + " )";
q = q + " AND (cliente.EmpresaId = '" + empresaId.ToString() + "')";
q = q + " GROUP BY cuota.CuotaId, cliente.Nit, cliente.Nombre, credito.CreditoId, cuota.Numero, cuota.Fecha, ";
q = q + " cuota.AbonoCapital, cuota.AbonoInteres, credito.Estado ORDER BY credito.Fecha DESC";
var cp = db.Database.SqlQuery <Cuotas>(q);
var final = from c in cp where (c.Abonos + 1 > (c.AbonoCapital + c.AbonoInteres)) select c;
return(final.ToList());
}
public IEnumerable <Cuotas> getCuotasxCobrar(int empresaId, string fecha, int?UsuarioId)
{
string strfecha = "";
if (MiUtil.isDate(fecha))
{
ViewBag.CurrentFilter = fecha;
strfecha = MiUtil.fechaToSQL(DateTime.ParseExact(fecha, "dd/MM/yyyy", null), 0);
}
var q = @"SELECT cuota.CuotaId, cliente.Nit, cliente.Nombre, credito.CreditoId,credito.CreditoNro, cuota.Numero, cuota.Fecha,
cuota.AbonoCapital+ IFNULL(cuota.AjusteAbonoCapital,0) as AbonoCapital,
cuota.AbonoInteres+ IFNULL(cuota.AjusteAbonoInteres,0) as AbonoInteres,
cuota.AbonoCapital + cuota.AbonoInteres + IFNULL(cuota.AjusteAbonoCapital,0)+ IFNULL(cuota.AjusteAbonoInteres,0) AS TotalCuota,
SUM(IFNULL(abono.Valor, 0)) AS Abonos,
(select count(0) from cuota where credito.CreditoId=cuota.CreditoId) as CantCuotas
FROM credito INNER JOIN
cuota ON credito.CreditoId = cuota.CreditoId LEFT OUTER JOIN
cliente ON credito.ClienteId = cliente.ClienteId LEFT OUTER JOIN
abono ON cuota.CuotaId = abono.CuotaId and abono.Estado=1";
q = q + " WHERE (credito.Estado = 1) AND (Cuota.Fecha <= '" + strfecha + "') AND (cliente.EmpresaId = {0})";
if (UsuarioId > 0)
{
q = q + " and credito.usuarioId='" + UsuarioId.ToString() + "'";
}
q = q + " GROUP BY cuota.CuotaId, cliente.Nit, cliente.Nombre, credito.CreditoId, cuota.Numero, cuota.Fecha, ";
q = q + " cuota.AbonoCapital, cuota.AbonoInteres, credito.Estado ORDER BY cliente.Nombre DESC";
var cxc = db.Database.SqlQuery <Cuotas>(q, empresaId);
var final = from c in cxc where (c.Abonos < (c.AbonoCapital + c.AbonoInteres)) select c;
return(final.ToList());
}
