<%@language=vbscript%>
<%
Response.Buffer=True

If Session("ID")=0 then
	Response.Redirect("http://www.grupo-mim.com")
	Response.End
Else
%>
<!--#include file="fpdf.asp"-->

<%
Dim Cn1, Rs1, Sql1
Dim Rs2, Sql2, Rs3, Sql3, Rs4, Sql4
Dim Empresa, Finicial, Ffinal, ClienteI, ClienteF

ClienteI = Request("ClienteI")
ClienteF = Request("ClienteF")

Finicial = Request("FechaI")
Ffinal = Request("FechaF")
Empresa = Cstr(Request("Empresa"))

Co1 = 1
Co2 = 33
Co3 = 254

Set Cn1 = Server.CreateObject( "ADODB.Connection" )
Cn1.Open "DRIVER={SQL Server};SERVER=172.17.0.5;UID=ADMIMSIS;PWD=ST2108GP;Database=" & Empresa &""

Set Rs1 = server.CreateObject("ADODB.Recordset")
Sql1="Select * from empresa"
Rs1.Open sql1,cn1,1,2

B1=rs1("Nombre")
B2=rs1("Direccion")
B3=rs1("Colonia")
B4=rs1("Poblacion")
B5=rs1("Telefono")
B6=rs1("Fax")
B7=rs1("website")

Set Rs2 = server.CreateObject("ADODB.Recordset")
SQL2="select nfaffacd, cliffacd, fecfface, nomcli, ucofface as Costo, (subfface*tcafface) as Subtotal, "&_
" (ivtfface*tcafface) as Iva, (totfface*tcafface) as total, stafface, (antifface * Tcafface) as Anticipo " &_
"from ffacdet, ffacenc, clientes where numfface=nfaffacd AND CODFFACD <> 'ANTICIPO'"&_
" and clacli = cliffacd and fecfface between '"& Finicial &"' and '"& Ffinal &"' and Clacli between '"& ClienteI &"' and '"& ClienteF &"' " &_
"group by nfaffacd, fecfface, cliffacd, nomcli, "&_
" ucofface, subfface, ivtfface, totfface, tcafface, stafface, antifface order by fecfface asc"
Rs2.Open Sql2,cn1,1,2

Set Rs3 = server.CreateObject("ADODB.Recordset")
SQL3="select * from subfac where fecfface between '"& Finicial &"' and '"& Ffinal &"' order by fecfface asc"
Rs3.Open Sql3,cn1,1,2
fd10 = cdbl(rs3("Costo"))
fd11 = cdbl(rs3("Subtotal"))
fd12 = cdbl(rs3("Iva"))
fd13 = cdbl(rs3("Total"))

Set pdf=CreateJsObject("FPDF")
pdf.CreatePDF "L", "mm", "Letter"
pdf.SetPath("fpdf/")
pdf.SetFillColor 13,16,121

pdf.Open()
pdf.LoadModels("Frdf")
pdf.SetLeftMargin(8.0)
pdf.AddPage()
pdf.SetFont "Arial","",8
pdf.Setautopagebreak true,20

Scosto = 0
Ssub = 0
Siva = 0 
Stotal = 0
Do while Not(RS2.EOF)



fecha = date()
fecha = FormatDateTime(fecha)
fd1 = Rs2("nfaffacd")
fd2 = Cstr(Rs2("Fecfface"))
fd3 = Rs2("Cliffacd")
fd4 = Rs2("Nomcli")
fd5 = cdbl(Rs2("Costo"))
Fd6 = cdbl(rs2("Subtotal"))
'Anticipo = Cdbl(Rs2("Anticipo")
'fd7 = cdbl(rs2("Desffacd"))
fd8 = cdbl(rs2("Iva"))
fd9 = cdbl(rs2("Total"))
fd14 = cstr(rs2("stafface"))
Anticipo = Cdbl(rs2("Anticipo"))


If Fd14 = "C" then
Fd5 = 0
Fd6 = 0
'Fd7 = 0
Fd8 = 0
Fd9 = 0
UtilF = 0 

else If Fd5  = 0 then
Fd5 = 0
UtilF = 100

else 
Fd5 = Fd5
Fd6 = Fd6
'Fd7 = Fd7
Fd8 = Fd8
Fd9 = Fd9
UtilF = (1 - (FD5/FD6)) * 100

end if
end If

pdf.Cell 12,4,fd1,0,0,"L",0
pdf.Cell 18,4,fd2,0,0,"L",0
pdf.Cell 4,4,fd14,0,0,"L",0
pdf.Cell 16,4,fd3,0,0,"L",0
pdf.Cell 76,4,left(fd4,36),0,0,"L",0
pdf.Cell 20,4,formatnumber(fd5),0,0,"R",0
pdf.Cell 20,4,formatnumber(fd6),0,0,"R",0
pdf.Cell 24,4,formatnumber(Anticipo),0,0,"R",0
pdf.Cell 20,4,formatnumber(UtilF) & "%" ,0,0,"R",0
pdf.Cell 27,4,formatnumber(fd8),0,0,"R",0
pdf.Cell 25,4,formatnumber(fd9),0,0,"R",0
pdf.Ln(4)

Scosto = Scosto + Fd5
Ssub = ssub + fd6
Siva = Siva + fd8
Stotal = stotal + fd9
Tanticipo = Tanticipo + Anticipo


RS2.Movenext
loop


pdf.Ln(4)
pdf.SetFont "Arial","B",8
pdf.cell 59,4,"  ",0,0,"l",0
pdf.cell 25,4,"T O T A L E S ",0,0,"R",0
pdf.cell 63,4,formatnumber(Scosto),0,0,"R",0
pdf.cell 20,4,formatnumber(ssub),0,0,"R",0
pdf.cell 24,4,formatnumber(Tanticipo),0,0,"R",0
pdf.cell 20,4,0,0,0,"R",0
pdf.cell 28,4,formatnumber(Siva),0,0,"R",0
pdf.cell 23,4,formatnumber(Stotal),0,0,"R",0

pdf.Output ()
End If
%>

using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.EntityFrameworkCore;
using MimPortal.Data;

namespace MimPortal.Pages.Clientes
{
    public class ClifacxmesbModel : PageModel
    {
        private readonly MimDbContext _context;

        public ClifacxmesbModel(MimDbContext context)
        {
            _context = context;
        }

        [BindProperty(SupportsGet = true)]
        public DateTime? FechaI { get; set; }

        [BindProperty(SupportsGet = true)]
        public DateTime? FechaF { get; set; }

        [BindProperty(SupportsGet = true)]
        public string? ClienteI { get; set; }

        [BindProperty(SupportsGet = true)]
        public string? ClienteF { get; set; }

        public List<FacturaItem> Facturas { get; set; } = new();

        public async Task OnGetAsync()
        {
            var sucursal = HttpContext.Session.GetString("Sucursal") ?? "MERIDA";
            if (!FechaI.HasValue || !FechaF.HasValue) return;

            var query = from d in _context.FFACDET
                        where d.Sucursal == sucursal
                        join e in _context.FFACENC on d.NFAFFACD equals e.NUMFFACE
                        join c in _context.CLIENTES on e.CLIFFACE equals c.CLACLI
                        where e.FECFFACE >= FechaI.Value && e.FECFFACE <= FechaF.Value
                              && d.CODFFACD != "ANTICIPO"
                        select new { e, c };

            if (!string.IsNullOrEmpty(ClienteI))
            {
                query = query.Where(x => string.Compare(x.c.CLACLI, ClienteI) >= 0);
            }

            if (!string.IsNullOrEmpty(ClienteF))
            {
                query = query.Where(x => string.Compare(x.c.CLACLI, ClienteF) <= 0);
            }

            var results = await query
                .GroupBy(x => new { x.e.NUMFFACE, x.e.CLIFFACE, x.e.FECFFACE, x.c.NOMCLI, x.e.UCOFFACE, x.e.SUBFFACE, x.e.IVTFFACE, x.e.TOTFFACE, x.e.TCAFFACE, x.e.STAFFACE })
                .Select(g => new FacturaItem
                {
                    NumFactura = g.Key.NUMFFACE ?? 0,
                    Cliente = g.Key.CLIFFACE ?? "",
                    NombreCliente = g.Key.NOMCLI ?? "",
                    Fecha = g.Key.FECFFACE ?? DateTime.MinValue,
                    Costo = g.Key.UCOFFACE ?? 0,
                    Subtotal = (g.Key.SUBFFACE ?? 0) * (g.Key.TCAFFACE ?? 1),
                    Iva = (g.Key.IVTFFACE ?? 0) * (g.Key.TCAFFACE ?? 1),
                    Total = (g.Key.TOTFFACE ?? 0) * (g.Key.TCAFFACE ?? 1),
                    Status = g.Key.STAFFACE ?? ""
                })
                .OrderBy(f => f.Fecha)
                .ToListAsync();

            Facturas = results;
        }

        public class FacturaItem
        {
            public int NumFactura { get; set; }
            public string Cliente { get; set; } = "";
            public string NombreCliente { get; set; } = "";
            public DateTime Fecha { get; set; }
            public decimal Costo { get; set; }
            public decimal Subtotal { get; set; }
            public decimal Iva { get; set; }
            public decimal Total { get; set; }
            public string Status { get; set; } = "";
        }
    }
}

@page
@model MimPortal.Pages.Clientes.ClifacxmesbModel
@{
    ViewData["Title"] = "Facturas por Mes/Cliente";
}
<div id="page-content">

<div class="card">
    <div class="card-header">
        <h5><i class="bi bi-calendar3"></i> Facturas por Mes/Cliente</h5>
    </div>
    <div class="card-body">
        <form method="get" class="row g-3 mb-4">
            <div class="col-md-2">
                <label class="form-label">Fecha Inicio</label>
                <input type="date" class="form-control" name="FechaI" value="@Model.FechaI?.ToString("yyyy-MM-dd")" />
            </div>
            <div class="col-md-2">
                <label class="form-label">Fecha Fin</label>
                <input type="date" class="form-control" name="FechaF" value="@Model.FechaF?.ToString("yyyy-MM-dd")" />
            </div>
            <div class="col-md-2">
                <label class="form-label">Cliente Inicio</label>
                <input type="text" class="form-control" name="ClienteI" value="@Model.ClienteI" placeholder="Desde" />
            </div>
            <div class="col-md-2">
                <label class="form-label">Cliente Fin</label>
                <input type="text" class="form-control" name="ClienteF" value="@Model.ClienteF" placeholder="Hasta" />
            </div>
            <div class="col-md-2 d-flex align-items-end">
                <button type="submit" class="btn btn-primary"><i class="bi bi-search"></i> Filtrar</button>
            </div>
        </form>

        @if (Model.Facturas.Any())
        {
            <div class="table-responsive">
                <table class="table table-hover table-sm">
                    <thead class="table-dark">
                        <tr>
                            <th>Factura</th>
                            <th>Cliente</th>
                            <th>Nombre</th>
                            <th>Fecha</th>
                            <th class="text-end">Costo</th>
                            <th class="text-end">Subtotal</th>
                            <th class="text-end">IVA</th>
                            <th class="text-end">Total</th>
                            <th>Status</th>
                        </tr>
                    </thead>
                    <tbody>
                        @foreach (var fac in Model.Facturas)
                        {
                            <tr>
                                <td>@fac.NumFactura</td>
                                <td>@fac.Cliente</td>
                                <td>@fac.NombreCliente</td>
                                <td>@fac.Fecha.ToString("dd/MM/yyyy")</td>
                                <td class="text-end">@fac.Costo.ToString("N2")</td>
                                <td class="text-end">@fac.Subtotal.ToString("N2")</td>
                                <td class="text-end">@fac.Iva.ToString("N2")</td>
                                <td class="text-end">@fac.Total.ToString("N2")</td>
                                <td>@fac.Status</td>
                            </tr>
                        }
                    </tbody>
                    <tfoot class="table-secondary fw-bold">
                        <tr>
                            <td colspan="4">TOTALES</td>
                            <td class="text-end">@Model.Facturas.Sum(f => f.Costo).ToString("N2")</td>
                            <td class="text-end">@Model.Facturas.Sum(f => f.Subtotal).ToString("N2")</td>
                            <td class="text-end">@Model.Facturas.Sum(f => f.Iva).ToString("N2")</td>
                            <td class="text-end">@Model.Facturas.Sum(f => f.Total).ToString("N2")</td>
                            <td></td>
                        </tr>
                    </tfoot>
                </table>
            </div>
        }
        else if (Model.FechaI.HasValue)
        {
            <div class="alert alert-info">No hay facturas en el período seleccionado.</div>
        }
    </div>
</div>
</div>