USE fss1;

DROP PROCEDURE IF EXISTS user ;
DELIMITER ;;
CREATE PROCEDURE user () 
BEGIN
  SELECT @_iva := (value/100.0+1.0) FROM setup WHERE id="TAXPERCENT";

  SELECT 
 	id+0 as id,
	name,
	login,
	barras as password,
	essupervisor,
	crea_cliente,
	venta_en_linea,
	all_cust,
	lprint,
	control_vencimiento,
	pvp,
	multibodega,
	idbodega+0 as idbodega,
	emailuser,
	idsalesman+0 as idsalesman,
	porcdesc1,
	porcdesc2,
	porcdesc3,
	porcdesc4,
	0.0 AS porcdesc5,
	seriepv
  FROM user WHERE !borrado and !bloqueado and trim(barras)!="" ORDER BY 1 INTO OUTFILE "/temp/1801450253001/user.txt";
END
;;
DELIMITER ;

	
DROP PROCEDURE IF EXISTS catalogo ;
DELIMITER ;;
CREATE PROCEDURE catalogo () 
BEGIN
  SELECT 10 as tipo, id+0 as idsource, name, substr(trim(fono),1,15) as code, 0.0 as valor from bank where !istarjetacredito and trim(name)!=''
			UNION ALL
  SELECT 15 as tipo, id+0 as idsource, name, substr(trim(code),1,15) as code, 0.0 as valor from bodega where istrans 
  			UNION ALL
  SELECT 20 as tipo, orden+0 as idsource, name1 as name, substr(trim(expresion),1,15) as code, valor from stockcond where tipo=100 
  			UNION ALL
  SELECT 25 as tipo, id+0 as idsource, concat(ctr," - ",descrip) as name, substr(trim(ctr),1,15) as code, pret as valor from tsri_11 where ingresa='S'
  			UNION ALL
  SELECT 30 as tipo, id+0 as idsource, name, substr(trim(id),1,15) as code, 0.0 as valor from accountype where id<=10 OR id=16 or id=17 or id=31  
  			UNION ALL
  SELECT 35 as tipo, id+0 as idsource, name, substr(trim(grupo),1,15) as code, 0.0 as valor  from grupo where 1 
  			UNION ALL
  SELECT 40 as tipo, id+0 as idsource, name, substr(trim(codigo),1,15) as code, 0.0 as valor from brand where 1 
  			UNION ALL
  SELECT 45 as tipo, id+0 as idsource, name, substr(trim(ruc),1,15) as code, 0.0 as valor from supplier where aplica_pda 
  			UNION ALL
  SELECT 50 as tipo, id+0 as idsource, name, substr(trim(fono),1,15) as code, 0.0 as valor from bank where istarjetacredito and trim(name)!='' 
  			UNION ALL
  SELECT 55 as tipo, id+0 as idsource, name, substr(trim(codigo),1,15) as code, 0.0 as valor from tipovisitas where 1 
  			UNION ALL
  SELECT 60 as tipo, id+0 as idsource, name, substr(trim(code),1,15) as code, 0.0 as valor from salesman where 1 
  			UNION ALL
  SELECT 65 as tipo, id+0 as idsource, name, substr(trim(codigo),1,15) as code, 0.0 as valor from visitas where activo
  			UNION ALL
  SELECT 70 as tipo, id+0 as idsource, name, substr(trim(grupo),1,15) as code, 0.0 as valor from zonas where 1 
  INTO OUTFILE "/temp/1801450253001/catalogo.txt";
END
;;
DELIMITER ;


DROP PROCEDURE IF EXISTS item ;
DELIMITER ;;
CREATE PROCEDURE item () 
BEGIN

  SELECT @_iva := (value/100.0+1.0) FROM setup WHERE id="TAXPERCENT";

  SELECT 
 	id+0 as id,
	partnumber,
	name,
	round(price *if(iva,@_iva,1),6) as pvp1, 
	round(priceb*if(iva,@_iva,1),6) as pvp2,
    round(pricec*if(iva,@_iva,1),6) as pvp3,
	round(priced*if(iva,@_iva,1),6) as pvp4,
	round(pricee*if(iva,@_iva,1),6) as pvp5,
	iva,
	noteunidad as unidad,
	note as descripcion,
	if(unidadescaja<1,1,unidadescaja) as uc,
	pesoitem as peso,
	'' as cubicaje,
	idgrupo,
	idbrand,
	idsupplier
  FROM item ORDER BY 1 INTO OUTFILE "/temp/1801450253001/item.txt";
END
;;
DELIMITER ;

DROP PROCEDURE IF EXISTS customer ;
DELIMITER ;;
CREATE PROCEDURE customer () 
BEGIN 
  SELECT
	id+0 as id,	
	name,			
	ruc,	
	passweb,
	comercio,		
	address,		
	city,		
	email,		
	phone,		
	pvp,
	codigo,		
	opcionpvp,		
	idsalesman+0 as idsalesman,		
	idzona+0 as idzona,		
	note as notas,		
	secuencia,
	openaccount as cupo,
	tipo as tipocliente,
	diascredito,
	latitud as lat,
	longitud as lon 	
  FROM customer  order by 1 INTO OUTFILE "/temp/1801450253001/customer.txt" ;
END
;;
DELIMITER ;


DROP PROCEDURE IF EXISTS visitascustomer ;
DELIMITER ;;
CREATE PROCEDURE visitascustomer () 
BEGIN
  SELECT 
    idsalesman+0 as idsalesman,
 	idcustomer+0 as idcustomer,
	orden,
	idvisitas+0 as idvisitas
  FROM visitascustomer ORDER BY 1 INTO OUTFILE "/temp/1801450253001/visitascustomer.txt";
END
;;
DELIMITER ;


DROP PROCEDURE IF EXISTS stockbod ;
DELIMITER ;;
CREATE PROCEDURE stockbod () 
BEGIN
  SELECT 
    idbodega+0 as idbodega,
 	iditem+0 as iditem,
	cantidad as stock
  FROM ghost.stockbod ORDER BY 1 INTO OUTFILE "/temp/1801450253001/stockbod.txt";
END
;;
DELIMITER ;


DROP PROCEDURE IF EXISTS cxc ;
DELIMITER ;;
CREATE PROCEDURE cxc () 
BEGIN
SELECT	A.reference as referencia,
CAST(A.Date as char) as fecha, 
CAST(A.Due as char) as vence,
CAST(ROUND(A.Total,2) as char) as total, 
CAST(ROUND(A.Balance,2) as char) as saldo, 

CAST(ROUND((
select ROUND(SUM(d.valororiginal)+0.00,2) as n
		from detallechequesposf as d 
		left join chequesposf as c on d.id = c.id 
		where d.idaccountcuscuota = a.id and c.anulado = 0 and c.cobrado = 0 and c.devuelto=0
),2) AS CHAR) as ch,

CAST(ROUND((
select sum(d.monto) from detareceiv as d inner join receiv as rd on rd.id=d.id where !rd.void and instr('09.10',d.IDACCOUNTYPE) and d.idaccountcuscuota=a.id
),2) AS CHAR) as ret,

CAST(ROUND((
select sum(d.monto) from detareceiv as d inner join receiv as rd on rd.id=d.id where !rd.void and instr('08', d.IDACCOUNTYPE) and d.idaccountcuscuota=a.id
),2) AS CHAR) as ncven,

CAST(ROUND((
select sum(d.monto) from detareceiv as d inner join receiv as rd on rd.id=d.id where !rd.void and instr('16',d.IDACCOUNTYPE) and d.idaccountcuscuota=a.id
),2) AS CHAR) as dep,

CAST(ROUND((
select sum(d.monto) from detareceiv as d inner join receiv as rd on rd.id=d.id where !rd.void and instr('04',d.IDACCOUNTYPE) and d.idaccountcuscuota=a.id
),2) AS CHAR) as tr,

CAST(ROUND((
select sum(d.monto) from detareceiv as d inner join receiv as rd on rd.id=d.id where !rd.void and !instr('08.09.10.20.16.04',d.IDACCOUNTYPE) and d.idaccountcuscuota=a.id
),2) AS CHAR) as ef,

CAST(A.Cuota as char) as cuota,
M.idcustomer+0 as idcustomer,
M.idsalesman+0 as idsalesman,
A.id+0 as idCuota,
M.idinvoice+0 as idinvoice
FROM AccountCus AS M INNER JOIN AccountCusCuota AS A on M.id = a.idaccountcus 
		LEFT JOIN Accountype      AS C on A.IdAccountype = C.Id 
		LEFT JOIN invoice         AS I on M.idinvoice = i.id 
		LEFT JOIN Customer        AS Cust on M.IdCustomer = Cust.Id 
		LEFT JOIN Salesman        AS Sales on Cust.IdSalesman = Sales.id
WHERE  a.balance>0
ORDER BY M.idcustomer, A.date, A.reference, A.Cuota
INTO OUTFILE "/temp/1801450253001/cxc.txt";
END
;;
DELIMITER ;