set @semanas = '-1,0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20';
use base_comedor;
create table nueva as

/* venta alimentos becarios por turno*/
select date_format(vn_fecha, "%d-%m-%Y") as fecha,
ar_cuenta_ventas as cuenta,
concat(ar_descripcion, " becarios, sem ", ss_semana, ", ", substr(sv_hostname,1,3),"-",vn_sesion_id) as descripcion,
0 as cargo,
round(sum(dt_cantidad*dt_por_pagar),2) as abono,
vn_sesion_id as sesion,
vn_servidor_id as servidor,
ar_contabilidad as contabilidad
from detalles
inner join productos on dt_producto_id=id_producto
inner join ventas on id_venta=dt_id_venta and vn_servidor_id=dt_servidor_id
inner join areas on id_area = pr_area_id
inner join sesiones on vn_sesion_id = id_sesion and ss_servidor_id = vn_servidor_id
inner join servidores on ss_servidor_id = id_servidor
where dt_por_pagar!=0 and ss_control = 10 and FIND_IN_SET(ss_semana, @semanas) > 0
group by 1,2,3,6,7,8

union all

/* compra alimentos becarios por turno - área */
select date_format(vn_fecha,"%d-%m-%Y") as fecha,
ar_cuenta_alimentos as cuenta,
concat(pr_descripcion, ", sem ", ss_semana, ", ", substr(sv_hostname,1,3),"-",vn_sesion_id) as descripcion, round(sum(dt_cantidad*dt_por_pagar),2) as cargo,
0 as abono,
vn_sesion_id as sesion,
vn_servidor_id as servidor,
ar_contabilidad as contabilidad
from detalles
inner join productos on dt_producto_id=id_producto
inner join ventas on id_venta=dt_id_venta and vn_servidor_id=dt_servidor_id
inner join areas on dt_por_pagar_area = id_area
inner join sesiones on vn_sesion_id = id_sesion and ss_servidor_id = vn_servidor_id
inner join servidores on ss_servidor_id = id_servidor
where dt_por_pagar!=0 and ss_control = 10 and FIND_IN_SET(ss_semana, @semanas) > 0
group by 1,2,3,6,7,8

union all

/* cargo en caja Y */
select date_format(vn_fecha,"%d-%m-%Y") as fecha,
cn_cuenta_caja_y as cuenta,
concat(pr_descripcion, ", sem ", ss_semana, ", ", substr(sv_hostname,1,3),"-",vn_sesion_id) as descripcion, round(sum(dt_cantidad*dt_por_pagar),2) as cargo,
0 as abono,
vn_sesion_id as sesion,
vn_servidor_id as servidor,
b.ar_contabilidad as contabilidad
from detalles
inner join productos on dt_producto_id=id_producto
inner join ventas on id_venta=dt_id_venta and vn_servidor_id=dt_servidor_id
inner join areas as a on dt_por_pagar_area = a.id_area
inner join areas as b on pr_area_id= b.id_area
inner join sesiones on vn_sesion_id = id_sesion and ss_servidor_id = vn_servidor_id
inner join contabilidades  on id_contabilidad = b.ar_contabilidad
inner join servidores on ss_servidor_id = id_servidor
where dt_por_pagar!=0 and ss_control = 10 and a.ar_contabilidad != b.ar_contabilidad and FIND_IN_SET(ss_semana, @semanas) > 0
group by 1,2,3,6,7,8

union all

/* abono en caja Y */
select date_format(vn_fecha,"%d-%m-%Y") as fecha,
cn_cuenta_caja_y as cuenta,
concat(pr_descripcion, ", sem ", ss_semana, ", ", substr(sv_hostname,1,3),"-",vn_sesion_id) as descripcion,
0 as cargo,
round(sum(dt_cantidad*dt_por_pagar),2) as abono,
vn_sesion_id as sesion,
vn_servidor_id as servidor,
a.ar_contabilidad as contabilidad
from detalles inner join productos on dt_producto_id=id_producto
inner join ventas on id_venta=dt_id_venta and vn_servidor_id=dt_servidor_id
inner join areas as a on dt_por_pagar_area = a.id_area
inner join areas as b on pr_area_id= b.id_area
inner join sesiones on vn_sesion_id = id_sesion and ss_servidor_id = vn_servidor_id
inner join contabilidades on id_contabilidad = a.ar_contabilidad
inner join servidores on ss_servidor_id = id_servidor
where dt_por_pagar!=0 and ss_control = 10 and a.ar_contabilidad != b.ar_contabilidad and FIND_IN_SET(ss_semana, @semanas) > 0
group by 1,2,3,6,7,8

union all

/* ventas */
select date_format(vn_fecha, "%d-%m-%Y") as fecha,
ar_cuenta_ventas as cuenta,
concat("Ventas ", ar_descripcion, ", sem ", ss_semana, ", ", substr(sv_hostname,1,3),"-",vn_sesion_id) as descripcion,
0 as cargo,
round(sum(dt_cantidad*dt_precio),2) as abono,
vn_sesion_id as sesion,
vn_servidor_id as servidor,
ar_contabilidad as contabilidad
from detalles
inner join productos on dt_producto_id=id_producto
inner join ventas on id_venta=dt_id_venta and vn_servidor_id=dt_servidor_id
inner join areas on id_area = pr_area_id
inner join sesiones on id_sesion =vn_sesion_id and ss_servidor_id = vn_servidor_id
inner join servidores on ss_servidor_id = id_servidor
where dt_precio!=0 and ss_control = 10 and FIND_IN_SET(ss_semana, @semanas) > 0
group by 1,2,3,6,7,8

union all

/* abono en caja-Y de ventas */
select date_format(vn_fecha, "%d-%m-%Y") as fecha,
cn_cuenta_caja_y as cuenta,
concat("Ventas ", ar_descripcion, ", sem ", ss_semana, ", ", substr(sv_hostname,1,3),"-",vn_sesion_id) as descripcion,
round(sum(dt_cantidad*dt_precio),2) as cargo,
0 as abono,
vn_sesion_id as sesion,
vn_servidor_id as servidor,
ar_contabilidad as contabilidad
from detalles
inner join productos on dt_producto_id=id_producto
inner join ventas on id_venta=dt_id_venta and vn_servidor_id=dt_servidor_id
inner join areas on id_area = pr_area_id
inner join sesiones on id_sesion =vn_sesion_id and ss_servidor_id = vn_servidor_id
inner join contabilidades on id_contabilidad = ar_contabilidad
inner join servidores on ss_servidor_id = id_servidor
where dt_precio!=0 and ss_control = 10 and FIND_IN_SET(ss_semana, @semanas) > 0
group by 1,2,3,6,7,8

union all

/* movimientos, cargos o abonos */
select
date_format(ss_abre,"%d-%m-%Y") as fecha,
mv_codigo as cuenta,
concat(mv_descripcion,", sem ", ss_semana, ", ", substr(sv_hostname,1,3),"-",id_sesion) as descripcion,
if(substr(mv_codigo,1,1)="5",mv_monto,0) as cargo,
if(substr(mv_codigo,1,1)="4",mv_monto,0) as abono,
mv_sesion_id as sesion,
mv_servidor_id as servidor,
ar_contabilidad as contabilidad
from movimientos
inner join areas on mv_area = id_area
inner join sesiones on mv_sesion_id = id_sesion and mv_servidor_id = ss_servidor_id
inner join servidores on ss_servidor_id = id_servidor
where ss_control = 10 and FIND_IN_SET(ss_semana, @semanas) > 0

union all
/* caja-Y de movimientos, cargos o abonos */
select
date_format(ss_abre,"%d-%m-%Y") as fecha,
cn_cuenta_caja_y as cuenta,
concat(mv_descripcion,", sem ", ss_semana, ", ", substr(sv_hostname,1,3),"-",id_sesion) as descripcion,
if(substr(mv_codigo,1,1)="4",mv_monto,0) as cargo,
if(substr(mv_codigo,1,1)="5",mv_monto,0) as abono,
mv_sesion_id as sesion,
mv_servidor_id as servidor,
ar_contabilidad as contabilidad
from movimientos
inner join areas on mv_area = id_area
inner join sesiones on mv_sesion_id = id_sesion and mv_servidor_id = ss_servidor_id
inner join contabilidades on id_contabilidad = ar_contabilidad
inner join servidores on ss_servidor_id = id_servidor
where ss_control = 10  and FIND_IN_SET(ss_semana, @semanas) > 0

union all

/* diferencia entre corte inicial y final */
select
date_format(ss_abre,"%d-%m-%Y") as fecha,
cj_cuenta_diferencias as cuenta,
concat("Diferencia caja inicial y final, sem ", ss_semana, ", ", substr(sv_hostname,1,3),"-",id_sesion) as descripcion,
if(ss_inicio-ss_en_caja<0, -ss_inicio + ss_en_caja,0) as cargo,
if(ss_inicio-ss_en_caja>0, ss_inicio- ss_en_caja,0) as abono,
id_sesion as sesion,
ss_servidor_id as servidor,
cj_contabilidad as contabilidad
from sesiones
inner join cajas on ss_caja_id = id_caja
inner join servidores on ss_servidor_id = id_servidor
where ss_inicio-ss_en_caja != 0 and ss_control = 10 and FIND_IN_SET(ss_semana, @semanas) > 0

union all

/* diferencia entre corte inicial y final */
select
date_format(ss_abre,"%d-%m-%Y") as fecha,
cn_cuenta_caja_y as cuenta,
concat("Diferencia caja inicial y final, sem ", ss_semana, ", ", substr(sv_hostname,1,3),"-",id_sesion) as descripcion,
if(ss_inicio-ss_en_caja>0, ss_inicio- ss_en_caja,0) as cargo,
if(ss_inicio-ss_en_caja<0, -ss_inicio + ss_en_caja,0) as abono,
id_sesion as sesion,
ss_servidor_id as servidor,
cj_contabilidad as contabilidad
from sesiones
inner join cajas on ss_caja_id = id_caja
inner join contabilidades on id_contabilidad = cj_contabilidad
inner join servidores on ss_servidor_id = id_servidor
where ss_inicio-ss_en_caja != 0 and ss_control = 10 and FIND_IN_SET(ss_semana, @semanas) > 0
order by 8,6,3;

/* diferencia en el corte */
insert into nueva
select date_format(ss_abre,"%d-%m-%Y") as fecha,
cn_cuenta_caja_y as cuenta,
concat("Diferencia corte sem ", ss_semana, ", ", substr(sv_hostname,1,3),"-",id_sesion) as descripcion,
if(ss_en_bolsa - a.teorico>0, ss_en_bolsa - a.teorico, 0) as cargo,
if(ss_en_bolsa - a.teorico<0, -ss_en_bolsa + a.teorico, 0) as abono,
id_sesion as sesion,
ss_servidor_id as servidor,
cj_contabilidad as contabilidad
from sesiones
inner join
   (select sesion, servidor, -sum(cargo)+sum(abono) as teorico from nueva where substr(cuenta,1,1) in ("4","5") group by 1,2)
      as a on id_sesion= a.sesion and ss_servidor_id = a.servidor
inner join cajas on ss_caja_id = id_caja
inner join contabilidades on id_contabilidad = cj_contabilidad
inner join servidores on ss_servidor_id = id_servidor
where ss_servidor_id != 3;

/* diferencia en el corte */
insert into nueva
select date_format(ss_abre,"%d-%m-%Y") as fecha,
cj_cuenta_diferencias as cuenta,
concat("Diferencia corte sem ", ss_semana, ", ", substr(sv_hostname,1,3),"-",id_sesion) as descripcion,
if(ss_en_bolsa - a.teorico<0, -ss_en_bolsa + a.teorico, 0) as cargo,
if(ss_en_bolsa - a.teorico>0, ss_en_bolsa - a.teorico, 0) as abono,
id_sesion as sesion,
ss_servidor_id as servidor,
cj_contabilidad as contabilidad
from sesiones
inner join
   (select sesion, servidor, -sum(cargo)+sum(abono) as teorico from nueva where substr(cuenta,1,1) in ("4","5") group by 1,2)
       as a on id_sesion= a.sesion and ss_servidor_id = a.servidor
inner join cajas on ss_caja_id = id_caja
inner join servidores on ss_servidor_id = id_servidor
where ss_servidor_id != 3;

/* exporta a contabilidad café */
select servidor*1000+sesion as asiento, fecha, cuenta, descripcion, cargo, abono
into outfile 'cafe.txt'
fields terminated by '\t'
lines terminated by '\n'
from nueva where contabilidad = 1
order by 1;

/* exporta a contabilidad comedor */
select servidor*1000+sesion as asiento, fecha, cuenta, descripcion, cargo, abono
into outfile 'comedor.txt'
fields terminated by '\t'
lines terminated by '\n'
from nueva where contabilidad = 2
order by 1;

/* exporta a contabilidad hidroponia */
select servidor*1000+sesion as asiento, fecha, cuenta, descripcion, cargo, abono
into outfile 'hidroponia.txt'
fields terminated by '\t'
lines terminated by '\n'
from nueva where contabilidad = 3
order by 1;

/* exporta a contabilidad panito */
select servidor*1000+sesion as asiento, fecha, cuenta, descripcion, cargo, abono
into outfile 'panito.txt'
fields terminated by '\t'
lines terminated by '\n'
from nueva where contabilidad = 4
order by 1;
