drop procedure if exists sp_ventas_registrar_ventas_masiva;
DELIMITER $$
CREATE  PROCEDURE sp_ventas_registrar_ventas_masiva()
    NO SQL
BEGIN

DECLARE jsonItems longtext;
DECLARE total_items int;
DECLARE i int;
DECLARE text_apoyo longtext;
DECLARE tipo_doc_v longtext;
DECLARE num_doc_v longtext;
DECLARE nombre_v longtext;
DECLARE direccion_v longtext;
DECLARE moneda_v longtext;
DECLARE tipo_cambio longtext;
DECLARE afecto_V longtext;
DECLARE no_afecto_V longtext;
DECLARE exonerado_V longtext;
DECLARE monto_sin_IGV decimal(25,2);
DECLARE monto_total_V longtext;
DECLARE monto_IGV decimal(25,2);
DECLARE cant_V longtext;
DECLARE nota_V longtext;
DECLARE creacion_V longtext;
DECLARE id_cliente int;
DECLARE id_temporal_sales_v int;
DECLARE expiracion_V longtext;

DECLARE cursorItems cursor for 
SELECT concat('{"items":{
    "1":{"code":"',cod_producto,'",
			"name":"',nombre_producto,'",
			"Quantity":"',cantidad,'",
			"Unit_price":"',total,'",
			"Affect_price":"',afecto,'",
			"Non_affected_price":"',inafecto,'",
			"Exonerated_price":"',exonerado,'",
			"Tax":"',ROUND(CONVERT(afecto, DECIMAL(25,2))*18/100, 2),'",
			"Item_tax":"',ROUND(CONVERT(afecto, DECIMAL(25,2))*18/100, 2),'",
			"Igv":"18",
			"Tax_ICBPER":"0",
			"Quantity_ICBPER":"0",
			"Subtotal":"',total,'",
			"tax_method":"0",
			"product_id":"0",
			"discount":"0.00",
			"item_discount":"0.00",
			"prr_discount":"0.00",
			"real_unit_price":"0.00",
			"currency_cost":"',moneda,'",
			"cost":"0"}
            }
  			}') as items, tipo_doc, num_doc, nombre, direccion,
            moneda, afecto, inafecto, exonerado, total,
            cantidad, nota, creacion, id, expiracion
FROM pro_ventas_importar
where estado = 1;

SELECT sell INTO tipo_cambio
FROM `tec_exchange` 
order by `id` DESC
limit 1;

SET i := 1;
SET total_items := 1;
SET jsonItems := ('');

SELECT count(*) INTO total_items from pro_ventas_importar where estado = 1;

open cursorItems;

WHILE i<=total_items DO

	FETCH cursorItems INTO text_apoyo, tipo_doc_v, num_doc_v, nombre_v, direccion_v, moneda_v, 
    afecto_V, no_afecto_V, exonerado_V, monto_total_V,
    cant_V, nota_V, creacion_V, id_temporal_sales_v, expiracion_V;
    
    SET jsonItems := concat(jsonItems, text_apoyo);
    
    SET id_cliente :=(SELECT `id` FROM `tec_customers` WHERE `cf1`= num_doc_v or `cf2`=num_doc_v);
    
    IF (id_cliente IS NULL)THEN
     
     	INSERT INTO `tec_customers`
        ( `name`, `cf1`, `cf2`, `customers_type_id`, `estado`, `person_type`, `document_type_id`, phone, email) 
        VALUES (nombre_v, num_doc_v, '', 1, 1, 1, 7, '', '');
        
        SET id_cliente :=  (SELECT LAST_INSERT_ID());
    
    END IF;
    
    SET monto_IGV := ROUND(CONVERT(afecto_V, DECIMAL(25,2))*18/100, 2);
    SET monto_sin_IGV :=ROUND(CONVERT(monto_total_V, DECIMAL(25,2)) - monto_IGV, 2);
    
    CALL sp_ventas_registrar_venta(
        STR_TO_DATE(creacion_V,'%d/%m/%Y %h:%i:%s'),
        1,
        1,
        id_cliente,
        nombre_v,
        1,
        moneda_v,
        tipo_cambio,
        afecto_V, 
        no_afecto_V, 
        exonerado_V, 
        monto_sin_IGV,
        0,
        0,
        0,
        0,
        monto_IGV,
        0,
        0,
        monto_IGV,
        0,
        0,
        monto_total_V,
        monto_total_V,
        monto_total_V,
        1,
        cant_V,
        0,
        1,
        1,
        STR_TO_DATE(creacion_V,'%d/%m/%Y %h:%i:%s'),
        nota_V,
        'DEBE',
        0,
        '',
        STR_TO_DATE(expiracion_V,'%d/%m/%Y'),
        3,
        '',
        '',
        '',
        '-',
        0,
        0,
        '',
        '',
        1,
        @p46, @p47,
        text_apoyo,
        2
    );
    
    UPDATE `pro_ventas_importar` 
	SET `estado`=2,`update_date`=CURRENT_TIMESTAMP
	WHERE `id`= id_temporal_sales_v;
    
    SET i = i +1;

END WHILE;

close cursorItems;

SELECT monto_total_V;


END$$
DELIMITER ;