--1.- [repvendedores.cvs] Nombres de vendedores sospechosos (los 10 con mayor afectación)
SELECT TOP 10 tb1.id_empleado, tb1.nombre_empleado,sum(tb1.productos) as ingresados,sum(tb1.permitidos) as permitidos, sum(dinero) as Dinero from
(select dbo.fecha_trabajo_formato102(com.fecha_trabajo) as fecha,tb1.id_comanda,emp.nombre_empleado + ' ' + apellido_paterno + ' ' + apellido_materno as nombre_empleado,nombre_seccion,tb1.productos,(tb1.numero*tb2.amici) permitidos, (tb1.productos - (tb1.numero*tb2.amici)) AS DIFERENCIA ,
case nombre_seccion 
when 'BEBIDA' then (28.15 * (tb1.productos - (tb1.numero*tb2.amici)))
when 'CUBIERTO' then (13 * (tb1.productos - (tb1.numero*tb2.amici)))
when 'CAFE/TE' then (19.13 * (tb1.productos - (tb1.numero*tb2.amici)))
when 'POSTRE' then (54.49 * (tb1.productos - (tb1.numero*tb2.amici)))
when 'FUERTE' THEN (75.65 * (tb1.productos - (tb1.numero*tb2.amici)))
WHEN 'ENTRADA' THEN (40 * (tb1.productos - (tb1.numero*tb2.amici)))
ELSE (43.49 * (tb1.productos - (tb1.numero*tb2.amici)))
end AS DINERO,emp.id_empleado
from 
(select id_comanda,nombre_seccion,count(*) as productos ,numero from secciones_paquete sec inner join productosxseccion pxs on sec.id_seccion_paquete = pxs.id_seccion_paquete
inner join productosxcuenta pxc on pxs.id_producto = pxc.id_producto where sec.id_producto=794 and id_tipo_anulacion is null
group by id_comanda,nombre_seccion,numero) tb1 inner join (select id_comanda,count(*) as amici from productosxcuenta where id_producto=794 and id_tipo_anulacion is null group by id_comanda) tb2
on tb1.id_comanda=tb2.id_comanda inner join comanda com on tb1.id_comanda = com.id_comanda inner join empleado emp on com.id_empleado = emp.id_empleado
where tb1.productos > (tb1.numero*tb2.amici)) TB1
group by tb1.nombre_empleado,tb1.id_empleado
order by DINERO DESC,nombre_empleado

--2.- [repcuentaafectadas.cvs] Reporte de cuentas afectadas x día (resumen) ordenadas por folio con hora apertura, numero de mesas y formas de pago, reimpresiones, c/cancelaciones, c/cortesias
SELECT tb1.fecha,tb1.id_comanda,tb1.nombre_empleado,sum(tb1.productos) - sum(tb1.permitidos) as prod_no_pagados, sum(dinero) as Dinero, tb1.num_mesa, tb1.fecha_apertura,tb1.cuentas,tb1.canthuespedes,tb1.impresiones,tb1.anulaciones from
(select dbo.fecha_trabajo_formato102(com.fecha_trabajo) as fecha,tb1.id_comanda,emp.nombre_empleado + ' ' + apellido_paterno + ' ' + apellido_materno as nombre_empleado,nombre_seccion,tb1.productos,(tb1.numero*tb2.amici) permitidos, (tb1.productos - (tb1.numero*tb2.amici)) AS DIFERENCIA ,
case nombre_seccion 
when 'BEBIDA' then (28.15 * (tb1.productos - (tb1.numero*tb2.amici)))
when 'CUBIERTO' then (13 * (tb1.productos - (tb1.numero*tb2.amici)))
when 'CAFE/TE' then (19.13 * (tb1.productos - (tb1.numero*tb2.amici)))
when 'POSTRE' then (54.49 * (tb1.productos - (tb1.numero*tb2.amici)))
when 'FUERTE' THEN (75.65 * (tb1.productos - (tb1.numero*tb2.amici)))
WHEN 'ENTRADA' THEN (40 * (tb1.productos - (tb1.numero*tb2.amici)))
ELSE (43.49 * (tb1.productos - (tb1.numero*tb2.amici)))
end AS DINERO, num_mesa, fecha_apertura, cu.cuentas, cu.impresiones, anu.anulaciones, canthuespedes
from 
(select id_comanda,nombre_seccion,count(*) as productos ,numero from secciones_paquete sec inner join productosxseccion pxs on sec.id_seccion_paquete = pxs.id_seccion_paquete
inner join productosxcuenta pxc on pxs.id_producto = pxc.id_producto where sec.id_producto=794 and id_tipo_anulacion is null
group by id_comanda,nombre_seccion,numero) tb1 inner join (select id_comanda,count(*) as amici from productosxcuenta where id_producto=794 and id_tipo_anulacion is null group by id_comanda) tb2
on tb1.id_comanda=tb2.id_comanda inner join comanda com on tb1.id_comanda = com.id_comanda inner join empleado emp on com.id_empleado = emp.id_empleado
inner join (select id_comanda,count(*) as cuentas, sum(num_impresiones) as impresiones from cuenta group by id_comanda) cu on cu.id_comanda = com.id_comanda
left join (select id_comanda,count(*) as anulaciones from productosxcuenta where id_tipo_anulacion is not null group by id_comanda) anu
on anu.id_comanda = com.id_comanda
where tb1.productos > (tb1.numero*tb2.amici) and emp.id_empleado=347) TB1
group by tb1.fecha,tb1.id_comanda,tb1.nombre_empleado,tb1.num_mesa,tb1.fecha_apertura,tb1.cuentas,tb1.impresiones,tb1.anulaciones,tb1.canthuespedes
order by tb1.fecha, nombre_empleado

--3.- [repmesasatendidas.cvs] Reporte de mesas atendidas por los 10 vendedor mas sospechosos del ultimo mes (hora apertura, numero de mesa, folio, vendedor, monto, huespedes)
select fecha_apertura,num_mesa,com.id_comanda,emp.nombre_empleado,sum(total) as monto,sum(canthuespedes) as huespedes 
from comanda com inner join empleado emp on com.id_empleado=emp.id_empleado 
inner join cuenta cu on com.id_comanda = cu.id_comanda
where com.id_empleado in (
select id_empleado from (SELECT TOP 10 tb1.id_empleado, sum(dinero) as Dinero from
(select dbo.fecha_trabajo_formato102(com.fecha_trabajo) as fecha,tb1.id_comanda,emp.nombre_empleado + ' ' + apellido_paterno + ' ' + apellido_materno as nombre_empleado,nombre_seccion,tb1.productos,(tb1.numero*tb2.amici) permitidos, (tb1.productos - (tb1.numero*tb2.amici)) AS DIFERENCIA ,
case nombre_seccion 
when 'BEBIDA' then (28.15 * (tb1.productos - (tb1.numero*tb2.amici)))
when 'CUBIERTO' then (13 * (tb1.productos - (tb1.numero*tb2.amici)))
when 'CAFE/TE' then (19.13 * (tb1.productos - (tb1.numero*tb2.amici)))
when 'POSTRE' then (54.49 * (tb1.productos - (tb1.numero*tb2.amici)))
when 'FUERTE' THEN (75.65 * (tb1.productos - (tb1.numero*tb2.amici)))
WHEN 'ENTRADA' THEN (40 * (tb1.productos - (tb1.numero*tb2.amici)))
ELSE (43.49 * (tb1.productos - (tb1.numero*tb2.amici)))
end AS DINERO,emp.id_empleado
from 
(select id_comanda,nombre_seccion,count(*) as productos ,numero from secciones_paquete sec inner join productosxseccion pxs on sec.id_seccion_paquete = pxs.id_seccion_paquete
inner join productosxcuenta pxc on pxs.id_producto = pxc.id_producto where sec.id_producto=794 and id_tipo_anulacion is null
group by id_comanda,nombre_seccion,numero) tb1 inner join (select id_comanda,count(*) as amici from productosxcuenta where id_producto=794 and id_tipo_anulacion is null group by id_comanda) tb2
on tb1.id_comanda=tb2.id_comanda inner join comanda com on tb1.id_comanda = com.id_comanda inner join empleado emp on com.id_empleado = emp.id_empleado
where tb1.productos > (tb1.numero*tb2.amici)) TB1
group by tb1.id_empleado
order by DINERO DESC) emp

)
and fecha_apertura > '2008-01-06'
group by fecha_apertura,num_mesa,com.id_comanda,emp.nombre_empleado
order by fecha_apertura


--Reporte de horas de afectación
select fecha,min(fecha_cierre),max(fecha_cierre) from
(
SELECT tb1.fecha,tb1.id_comanda,tb1.nombre_empleado,sum(tb1.productos) - sum(tb1.permitidos) as prod_no_pagados, sum(dinero) as Dinero, tb1.num_mesa, tb1.fecha_apertura,tb1.fecha_cierre,tb1.cuentas,tb1.canthuespedes,tb1.impresiones,tb1.anulaciones from
(select dbo.fecha_trabajo_formato102(com.fecha_trabajo) as fecha,tb1.id_comanda,emp.nombre_empleado + ' ' + apellido_paterno + ' ' + apellido_materno as nombre_empleado,nombre_seccion,tb1.productos,(tb1.numero*tb2.amici) permitidos, (tb1.productos - (tb1.numero*tb2.amici)) AS DIFERENCIA ,
case nombre_seccion 
when 'BEBIDA' then (28.15 * (tb1.productos - (tb1.numero*tb2.amici)))
when 'CUBIERTO' then (13 * (tb1.productos - (tb1.numero*tb2.amici)))
when 'CAFE/TE' then (19.13 * (tb1.productos - (tb1.numero*tb2.amici)))
when 'POSTRE' then (54.49 * (tb1.productos - (tb1.numero*tb2.amici)))
when 'FUERTE' THEN (75.65 * (tb1.productos - (tb1.numero*tb2.amici)))
WHEN 'ENTRADA' THEN (40 * (tb1.productos - (tb1.numero*tb2.amici)))
ELSE (43.49 * (tb1.productos - (tb1.numero*tb2.amici)))
end AS DINERO, num_mesa, fecha_apertura, fecha_cierre,cu.cuentas, cu.impresiones, anu.anulaciones, canthuespedes
from 
(select id_comanda,nombre_seccion,count(*) as productos ,numero from secciones_paquete sec inner join productosxseccion pxs on sec.id_seccion_paquete = pxs.id_seccion_paquete
inner join productosxcuenta pxc on pxs.id_producto = pxc.id_producto where sec.id_producto=794 and id_tipo_anulacion is null
group by id_comanda,nombre_seccion,numero) tb1 inner join (select id_comanda,count(*) as amici from productosxcuenta where id_producto=794 and id_tipo_anulacion is null group by id_comanda) tb2
on tb1.id_comanda=tb2.id_comanda inner join comanda com on tb1.id_comanda = com.id_comanda inner join empleado emp on com.id_empleado = emp.id_empleado
inner join (select id_comanda,count(*) as cuentas, sum(num_impresiones) as impresiones from cuenta group by id_comanda) cu on cu.id_comanda = com.id_comanda
left join (select id_comanda,count(*) as anulaciones from productosxcuenta where id_tipo_anulacion is not null group by id_comanda) anu
on anu.id_comanda = com.id_comanda
where tb1.productos > (tb1.numero*tb2.amici)) TB1
group by tb1.fecha,tb1.id_comanda,tb1.nombre_empleado,tb1.num_mesa,tb1.fecha_apertura,tb1.cuentas,tb1.impresiones,tb1.anulaciones,tb1.canthuespedes,tb1.fecha_cierre
) dat
group by fecha
order by fecha