SELECT Serie,IdVenta,SUM(PrecioTotal- ctotal) AS 'Utilidad',SUM(ctotal) AS 'Costo' FROM VentasDetalle WHERE Serie = 'CUL-' AND IdVenta ='0094'  GROUP BY (IdVenta)

SELECT Serie,IdVenta,SUM(PrecioTotal- ctotal) AS 'Utilidad',SUM(ctotal) AS 'Costo' FROM VentasDetalle WHERE Activo ='1' AND Serie != 'COT-'   GROUP BY (IdVenta,Serie)

SELECT SUM(ctotal) AS 'Costo' FROM VentasDetalle WHERE Activo ='1'  GROUP BY (IdVenta)

SELECT Serie,IdVenta,PrecioTotal AS 'Precio',ctotal AS 'Costo',(PrecioTotal - ctotal) AS 'Utilidad',Fecha FROM VentasDetalle WHERE Fecha BETWEEN '2018-01-05' AND '2018-01-05' AND Serie != 'COT-' GROUP BY (IdVenta)

UPDATE employees
        LEFT JOIN
    merits ON employees.performance = merits.performance 
SET 
    salary = salary + salary * 0.015
WHERE
    merits.percentage IS NULL;

    Update Ventas 
    	JOIN VentasDetalle ON Ventas.FolioVenta = VentasDetalle.IdVenta AND Ventas.Serie = VentasDetalle.Serie
    	SET
   			Ventas.CostoTotal = (SELECT SUM(ctotal) AS 'Costo' FROM VentasDetalle WHERE Activo ='1' AND Ventas.FolioVenta = VentasDetalle.IdVenta AND Ventas.Serie = VentasDetalle.Serie  GROUP BY (IdVenta))

   			Update Ventas 
    	JOIN VentasDetalle ON Ventas.FolioVenta = VentasDetalle.IdVenta AND Ventas.Serie = VentasDetalle.Serie
    	SET
   			Ventas.Utilidad = (SELECT SUM(PrecioTotal- ctotal) AS 'Utilidad' FROM VentasDetalle WHERE Activo ='1' AND Ventas.FolioVenta = VentasDetalle.IdVenta AND Ventas.Serie = VentasDetalle.Serie  GROUP BY (VentasDetalle.IdVenta))

//REPORTE DE VENTAS

GENERAL

SELECT V.FechaVenta,V.Serie,V.FolioVenta,C.Nombre,V.Total AS 'Venta',V.CostoTotal AS 'Costo',V.Utilidad,V.Anticipo AS 'Cobrado',V.Saldo AS 'Saldo' FROM Ventas V
	JOIN Clientes C ON V.IdCliente = C.IdCliente
WHERE V.Activo = 1 AND Serie != 'COT-' ORDER BY V.Serie,V.FolioVenta

FISCAL

SELECT V.FechaVenta,V.Serie,V.FolioVenta,C.Nombre,V.Total AS 'Venta',V.CostoTotal AS 'Costo',V.Utilidad,V.Anticipo AS 'Cobrado',V.Saldo AS 'Saldo' FROM Ventas V
	JOIN Clientes C ON V.IdCliente = C.IdCliente
WHERE V.Activo = 1 AND Serie = 'CUL-'

NO FISCAL

SELECT V.FechaVenta,V.Serie,V.FolioVenta,C.Nombre,V.Total AS 'Venta',V.CostoTotal AS 'Costo',V.Utilidad,V.Anticipo AS 'Cobrado',V.Saldo AS 'Saldo' FROM Ventas V
	JOIN Clientes C ON V.IdCliente = C.IdCliente
WHERE V.Activo = 1 AND Serie = 'NVC-'


POR CLIENTE 

SELECT C.Nombre,SUM(V.Total) AS 'Venta',SUM(V.CostoTotal) AS 'Costo',SUM(V.Utilidad) AS 'Utilidad',SUM(V.Anticipo) AS 'Cobrado',SUM(V.Saldo) AS 'Saldo' FROM Ventas V
  JOIN Clientes C ON V.IdCliente = C.IdCliente
WHERE V.Activo = 1 AND Serie != 'COT-' GROUP BY V.IdCliente ORDER BY C.Nombre

PENDIENTES DE PAGO GRAL

SELECT V.FechaVenta,V.Serie,V.FolioVenta,C.Nombre,V.Total AS 'Venta',V.CostoTotal AS 'Costo',V.Utilidad,V.Anticipo AS 'Cobrado',V.Saldo AS 'Saldo' FROM Ventas V
	JOIN Clientes C ON V.IdCliente = C.IdCliente
WHERE V.Activo = 1 AND V.EstadoPago != 2 AND V.TipoOrden = 1 ORDER BY V.Serie,V.FolioVenta

PENDIENTES DE PAGO FISCAL

SELECT V.FechaVenta,V.Serie,V.FolioVenta,C.Nombre,V.Total AS 'Venta',V.CostoTotal AS 'Costo',V.Utilidad,V.Anticipo AS 'Cobrado',V.Saldo AS 'Saldo' FROM Ventas V
	JOIN Clientes C ON V.IdCliente = C.IdCliente
WHERE V.Activo = 1 AND V.EstadoPago != 2 AND V.TipoFolio = 1 AND TipoOrden = 1

PENDIENTES DE PAGO NO FISCAL

SELECT V.FechaVenta,V.Serie,V.FolioVenta,C.Nombre,V.Total AS 'Venta',V.CostoTotal AS 'Costo',V.Utilidad,V.Anticipo AS 'Cobrado',V.Saldo AS 'Saldo' FROM Ventas V
	JOIN Clientes C ON V.IdCliente = C.IdCliente
WHERE V.Activo = 1 AND V.EstadoPago != 2 AND V.TipoFolio = 2 AND TipoOrden = 1

PAGADAS GRAL

SELECT V.FechaVenta,V.Serie,V.FolioVenta,C.Nombre,V.Total AS 'Venta',V.CostoTotal AS 'Costo',V.Utilidad,V.Anticipo AS 'Cobrado',V.Saldo AS 'Saldo' FROM Ventas V
  JOIN Clientes C ON V.IdCliente = C.IdCliente
WHERE V.Activo = 1 AND V.EstadoPago = 2 AND V.TipoOrden = 1 ORDER BY V.Serie,V.FolioVenta

PAGADAS FISCAL

SELECT V.FechaVenta,V.Serie,V.FolioVenta,C.Nombre,V.Total AS 'Venta',V.CostoTotal AS 'Costo',V.Utilidad,V.Anticipo AS 'Cobrado',V.Saldo AS 'Saldo' FROM Ventas V
  JOIN Clientes C ON V.IdCliente = C.IdCliente
WHERE V.Activo = 1 AND V.EstadoPago = 2 AND V.TipoFolio = 1 AND TipoOrden = 1

PAGADAS NO FISCAL

SELECT V.FechaVenta,V.Serie,V.FolioVenta,C.Nombre,V.Total AS 'Venta',V.CostoTotal AS 'Costo',V.Utilidad,V.Anticipo AS 'Cobrado',V.Saldo AS 'Saldo' FROM Ventas V
  JOIN Clientes C ON V.IdCliente = C.IdCliente
WHERE V.Activo = 1 AND V.EstadoPago = 2 AND V.TipoFolio = 2 AND TipoOrden = 1


VENTAS CLIENTES DETALLE

SELECT C.Nombre AS 'Cliente', COUNT(*) AS 'Cantidad de ordenes', SUM(V.Total) AS 'Total', SUM(V.Anticipo) AS 'Cobrado', SUM(V.Saldo) AS 'Saldo', MAX(V.FechaVenta) AS 'Ultima compra' FROM Ventas V
JOIN Clientes C ON V.IdCliente = C.IdCliente
WHERE V.Activo = 1  AND V.TipoOrden = 1 
GROUP BY Cliente
ORDER BY Total DESC

Ventas por producto

SELECT Serie,IdVenta AS 'Folio',IF(TipoPrecio = 1,"Mostrador","Fotografo") AS 'Tipo de precio', Largo,Ancho,IF(Umed =1, "CM","IN") AS 'Unidad', CodProd AS 'Producto',PrecioMold AS 'Moldura', cmold AS 'Costo Moldura', PrecioCrisN As 'Cristal', ccn AS 'Costo Cristal', PrecioCrisA AS 'Cristal Anti', cca As 'Costo Cristal Anti', PrecioMl AS 'Maria Luisa', cml As 'Costo ML', PrecioRes AS 'Respaldo', cr As 'Costo Respaldo', PrecioPapel AS 'Papel Fotografico', cpap AS 'Costo Papel F.', PrecioText AS 'Textura', ctext AS 'Costo Textura', PrecioMaq AS 'Maquila', PrecioCanva As 'Canva', ccan AS 'Costo Canva', PrecioAcrilico AS 'Acrilico', cacr AS 'Costo Acrilico', PrecioDobleCara AS 'Doble cara', cdc AS 'Costo Doble cara',PrecioTotal AS 'Precio total', ctotal AS 'Costo Total' FROM VentasDetalle Where Activo = 1 AND Serie != 'COT-' ORDER BY Serie,IdVenta


Ventas por tipo de Cliente FISCAL/NOFISCAL

SELECT V.FechaVenta,V.Serie,V.FolioVenta,C.Nombre,IF(D.TipoPrecio = 1,'Mostrador','Fotografo') AS 'Tipo de precio',V.Total AS 'Venta',V.CostoTotal AS 'Costo',V.Utilidad,V.Anticipo AS 'Cobrado',V.Saldo AS 'Saldo',V.FechaBusq FROM Ventas V
            JOIN Clientes C ON V.IdCliente = C.IdCliente
            JOIN VentasDetalle D ON V.Serie = D.Serie AND V.FolioVenta = D.IdVenta
            WHERE V.Activo = 1  AND V.TipoOrden = '1' AND V.TipoFolio = '$tipop' AND V.FechaBusq BETWEEN '$desde' AND '$hasta' AND V.IdSucursal ='$sucbus' GROUP BY V.FechaBusq ORDER BY V.IdSucursal,V.Serie,V.FolioVenta 

SELECT IF(C.TipoCliente = 1,'Mostrador','Fotografo') AS 'Tipo de precio',SUM(V.Total) AS 'Venta',SUM(V.CostoTotal) AS 'Costo',SUM(V.Utilidad) AS 'Utilidad',SUM(V.Anticipo) AS 'Cobrado',SUM(V.Saldo) AS 'Saldo',V.FechaBusq,IF(V.IdSucursal = 1,'CULIACAN','MAZATLAN') AS 'SUCURSAL' FROM Ventas V
            JOIN Clientes C ON V.IdCliente = C.IdCliente
            WHERE V.Activo = 1  AND V.TipoOrden = '1' AND V.TipoFolio = '$tipop' AND V.FechaBusq BETWEEN '$desde' AND '$hasta' AND V.IdSucursal = '$sucbus' GROUP BY C.TipoCliente,V.FechaBusq ORDER BY V.FechaBusq ASC

Ventas por tipo GENERAL            
SELECT IF(C.TipoCliente = 1,'Mostrador','Fotografo') AS 'Tipo de precio',SUM(V.Total) AS 'Venta',SUM(V.CostoTotal) AS 'Costo',SUM(V.Utilidad) AS 'Utilidad',SUM(V.Anticipo) AS 'Cobrado',SUM(V.Saldo) AS 'Saldo',V.FechaBusq,IF(V.IdSucursal = 1,'CULIACAN','MAZATLAN') AS 'SUCURSAL' FROM Ventas V
            JOIN Clientes C ON V.IdCliente = C.IdCliente
            WHERE V.Activo = 1  AND V.TipoOrden = '1' GROUP BY V.IdSucursal,C.TipoCliente,V.FechaBusq ORDER BY V.FechaBusq,V.IdSucursal,C.TipoCliente ASC     

Ventas por tipo General sucursales

SELECT IF(C.TipoCliente = 1,'Mostrador','Fotografo') AS 'Tipo de precio',SUM(V.Total) AS 'Venta',SUM(V.CostoTotal) AS 'Costo',SUM(V.Utilidad) AS 'Utilidad',SUM(V.Anticipo) AS 'Cobrado',SUM(V.Saldo) AS 'Saldo',V.FechaBusq,IF(V.IdSucursal = 1,'CULIACAN','MAZATLAN') AS 'SUCURSAL' FROM Ventas V
            JOIN Clientes C ON V.IdCliente = C.IdCliente
            WHERE V.Activo = 1  AND V.TipoOrden = '1' AND V.IdSucursal = '$sucbus' GROUP BY V.IdSucursal,C.TipoCliente,V.FechaBusq ORDER BY V.FechaBusq,V.IdSucursal,C.TipoCliente ASC

Reporte por categorias
SELECT V.Serie,V.IdVenta AS 'Folio',IF(V.TipoPrecio = 1,'Mostrador','Fotografo') AS 'Tipo de precio', V.Largo,V.Ancho,IF(V.Umed =1, 'CM','IN') AS 'Unidad', V.CodProd AS 'Producto',V.PrecioMold AS 'Moldura', V.cmold AS 'Costo Moldura', V.PrecioCrisN As 'Cristal', V.ccn AS 'Costo Cristal', V.PrecioCrisA AS 'Cristal Anti', V.cca As 'Costo Cristal Anti', V.PrecioMl AS 'Maria Luisa', V.cml As 'Costo ML', V.PrecioRes AS 'Respaldo', V.cr As 'Costo Respaldo', V.PrecioPapel AS 'Papel Fotografico', V.cpap AS 'Costo Papel F.', V.PrecioText AS 'Textura', V.ctext AS 'Costo Textura', V.PrecioMaq AS 'Maquila', V.PrecioCanva As 'Canva', V.ccan AS 'Costo Canva', V.PrecioAcrilico AS 'Acrilico', V.cacr AS 'Costo Acrilico', V.PrecioDobleCara AS 'Doble cara', V.cdc AS 'Costo Doble cara',V.PrecioTotal AS 'Precio total', V.ctotal AS 'Costo Total',P.Nombre AS 'NomProd',P.IdCategoria AS 'Categoria' FROM VentasDetalle V
            JOIN Productos P ON V.CodProd = P.Codigo
            Where V.Activo = 1 AND V.Serie != 'COT-' AND V.Fecha BETWEEN '$desde' AND '$hasta' ORDER BY V.Serie,V.IdVenta



SELECT  V.CodProd AS 'Producto',SUM(V.PrecioMold) AS 'Moldura', SUM(V.cmold) AS 'Costo Moldura', SUM(V.PrecioCrisN) As 'Cristal', SUM(V.ccn AS 'Costo Cristal'), SUM(V.PrecioCrisA) AS 'Cristal Anti', SUM(V.cca) As 'Costo Cristal Anti', SUM(V.PrecioMl) AS 'Maria Luisa', SUM(V.cml) As 'Costo ML', SUM(V.PrecioRes) AS 'Respaldo', SUM(V.cr) As 'Costo Respaldo', SUM(V.PrecioPapel) AS 'Papel Fotografico', SUM(V.cpap) AS 'Costo Papel F.', SUM(V.PrecioText) AS 'Textura', SUM(V.ctext) AS 'Costo Textura', SUM(V.PrecioMaq) AS 'Maquila', SUM(V.PrecioCanva) As 'Canva', SUM(V.ccan) AS 'Costo Canva', SUM(V.PrecioAcrilico) AS 'Acrilico', SUM(V.cacr) AS 'Costo Acrilico', SUM(V.PrecioDobleCara) AS 'Doble cara', SUM(V.cdc) AS 'Costo Doble cara',SUM(V.PrecioTotal) AS 'Precio total', SUM(V.ctotal) AS 'Costo Total' FROM VentasDetalle V
            Where V.Activo = 1 AND V.Serie != 'COT-'


            ->getStyleByColumnAndRow($column, $row)
    ->getNumberFormat()
    ->setFormatCode('0.000%;[Red]-0.000%');



REPORTE DE INSUMOS

SELECT P.Nombre,I.Descripcion, I.PrecioCompra,I.UnidPaq, IF(I.UnidadMed = 2,'Area','Pieza') AS 'Unidad Medida',I.PorcDesp AS 'Desperdicio', (I.TotalUnit * I.UnidPaq) AS 'Costo Total'  FROM Insumos I 
JOIN Proveedor P ON I.IdProveedor = P.IdProveedor 
 WHERE I.Activo = 1 AND I.idtipo = 0 ORDER BY I.Descripcion ASC


Reporte de productos

SELECT P.Codigo,P.Nombre, C.Descripcion, P.Costtot,P.PrecioVenta AS 'Precio Fotografos',P.porc AS 'Porcentaje Fotografos',P.preciovm AS 'Precio Mostrador',P.porcm AS 'Porcentaje Mostrador'  FROM Productos P 
JOIN Categoria C ON C.IdCategoria = P.IdCategoria 
 WHERE P.Activo = 1  ORDER BY P.IdCategoria,P.Nombre ASC

