Example #1
0
        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)));
        }
Example #2
0
        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());
        }
Example #3
0
        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());
        }