-- DROP FUNCTION public.sp_crud_factura_moneda_v2(varchar, varchar, int4, int4, varchar, int4, int4, int4, _varchar);

CREATE OR REPLACE FUNCTION public.sp_crud_factura_moneda_v2(serie character varying, tipo character varying, ubicacion integer, persona integer, total character varying, p_id_usuario integer, p_id_caja integer, p_id_moneda integer, p_detalle character varying[])
 RETURNS character varying
 LANGUAGE plpgsql
AS $function$
declare
	_numero integer;
	idp integer;
	_ruc character varying;
	_razon_social character varying;
	_direccion character varying;
	_correo character varying;
	_total numeric;
	_total_letras character varying;
	_decimal_letras character varying;
	_total_sm numeric;
	_nombres character varying;
	_id_valoriza integer;
	_id_valoriza_act integer;

	_cuenta_s integer;
	_cuenta_t integer;

	_total_smr numeric;
	_descuento numeric;
	_moneda character varying;
	
	v_id_factura integer;
	
	v_item integer;
	v_total numeric;
	v_descuento numeric;
	v_descripcion character varying; 
	v_cod_contable character varying; 
	v_codigo_v integer;
	v_estab_v integer;
	v_modulo integer;
	v_smodulo integer;
	v_numero integer;
	v_numero_ integer;

begin

	total := to_number(total,'9999999999.99');
				
	if p_id_moneda = 2 then
		_moneda:='SOLES';
	else
		_moneda:='DOLARES';
	end if;

	if ubicacion > 0 Then

		if trunc(_total) = 0 Then
			select substr(CAST(_total AS varchar),3) into _decimal_letras;
		else
			select substr(CAST(mod(_total,trunc(_total)) AS varchar),3) into _decimal_letras;
		End if;

		if tipo = 'FT' Then
			select t2.ruc, t2.razon_social, (case when t2.direccion = 'Direccion' then '' else t2.direccion end) direccion, t2.email into  _ruc,_razon_social, _direccion, _correo
			from ubicacion_trabajos t1
			inner join empresas t2 on t1.ubicacion_empresa_id=t2.id
			Where t1.id=ubicacion;
		end if;

		if tipo = 'BV' or tipo = 'TK' Then

			select numero_documento, apellido_paterno || ' '|| apellido_materno || ' '|| nombres, direccion, email into  _ruc, _razon_social, _direccion, _correo
			from personas
			Where id=persona;
		end if;

		select upper(f_convnl(trunc(_total))) || ' CON '|| Case When _decimal_letras = '' Then '0' Else _decimal_letras End ||'/100 '||_moneda into _total_letras;
		
		select coalesce(max(fi.fac_numero),'0')+1 into v_numero_ from facturas fi where fi.fac_serie = serie;
		
		if (select count(id) from facturas f where f.fac_serie = serie And f.fac_numero=v_numero_)=0 then
			 
		if (select count(id) from facturas f where f.fac_serie = serie And fac_cod_tributario=_ruc And f.fac_total=total::numeric and f.fac_fecha BETWEEN NOW() - INTERVAL '10 SECONDS' AND NOW())=0 then
		
			Insert Into facturas (fac_serie, fac_numero, fac_fecha, fac_destinatario, fac_direccion, fac_cod_tributario, fac_serie_guia,fac_nro_guia, fac_total_grav, fac_total_inaf, fac_total_exo, fac_impuesto,
					fac_total, fac_cod_emp, fac_letras, fac_moneda, fac_impuesto_factor, fac_tipo_cambio, fac_estado_pago, fac_anulado, fac_fecha_pago, fac_fecha_recepcion, fac_fecha_vencimiento,
					fac_fecha_programado, fac_observacion, fac_moneda_id, fac_tipo, fac_forma_pago, fac_afecta, fac_cerrado, fac_tipo_documento,fac_serie_ncnd ,fac_numero_ncnd ,fac_tipo_ncnd,
					fac_solictante,fac_orden_compra,  fac_total_anticipo, fac_total_descuentos, fac_desc_globales,fac_monto_perce, fac_monto_detrac, fac_porc_detrac, fac_totalconperce, fac_tipo_guia,
					fac_serie_refer, fac_nro_refer, fac_tipo_refer, fac_codtipo_ncnd, fac_motivo_ncnd, fac_correo_des, fac_tipo_operacion, fac_base_perce, fac_tipo_emision, fac_ope_gratuitas,
					fac_subtotal, fac_codigoBBSS_detrac, fac_cuenta_detrac, fac_notas, fac_condPago, fac_caja_id, id_usuario)
			Values (serie,v_numero_,now(),_razon_social,_direccion,_ruc,'', NULL,
					CAST(total AS numeric),0.00,0.00,((CAST(total AS numeric)/1.18)*0.18),CAST(total AS numeric),3003,_total_letras,_moneda,18,0.000,'P','N',now(),now(),
					now(),now(),'',p_id_moneda, tipo, 1, '', 'S',6,'',0,'','','',0.00, 0.00, 0.00, 0.00, 0.00, 0, CAST(total AS numeric), '', '', '', '', '', '', _correo, '01',
					CAST(total AS numeric), 'SINCRONO', 0, CAST(total AS numeric)/1.18, '', '', '', '', p_id_caja, p_id_usuario) RETURNING id into v_id_factura;
			
			select f.fac_numero into v_numero from facturas f Where f.id=v_id_factura limit 1;
				
			if array_length(p_detalle, 1)>0 then 
				for i in array_lower(p_detalle, 1) .. array_upper(p_detalle, 1) loop 
						
					v_item := p_detalle[i][1]::integer;
					v_total := p_detalle[i][2]::numeric;
					v_descuento := p_detalle[i][3]::numeric;
					v_descripcion := p_detalle[i][4]; 
					v_cod_contable := p_detalle[i][5]; 
					v_codigo_v := p_detalle[i][6]::integer;
					v_estab_v := p_detalle[i][7]::integer;
					v_modulo := p_detalle[i][8]::integer;
					v_smodulo := p_detalle[i][9]::integer;
					
					if v_cod_contable='0' then v_cod_contable=''; end if;
				
					Insert Into factura_detalles(facd_serie, facd_numero, facd_tipo, facd_item, facd_cantidad, facd_descripcion,
					facd_pu, facd_pu_conIGV, facd_igv_total, facd_descuento, facd_importe,
					facd_afect_igv, facd_cod_contable, facd_valor_gratu, facd_unidad)
					Values (serie,v_numero,tipo,v_item,1,v_descripcion,v_total/1.18,(v_total/1.18),(v_total/1.18)*0.18,v_descuento,v_total,10,v_cod_contable,0,'ZZ' );
					
					select vsm_precio into _total_sm 
					from val_atencion_smodulos 
					where vsm_vestab = v_estab_v and vsm_vnumero = v_codigo_v and vsm_modulo = v_modulo and vsm_smodulo = v_smodulo;
	
					_total_smr:= _total_sm;
	
					if v_descuento <> 0 then
						_total_sm:= _total_sm - (_total_sm * v_descuento)/100;
						_total_smr:= _total_sm;
					End if;
						
					if _total_sm <> v_total  then
						
						update val_atencion_smodulos set vsm_costo= v_total, vsm_precio = v_total, vsm_facturado = 'S', vsm_fac_serie = serie, vsm_fac_numero = v_numero, vsm_fac_tipo = tipo,
						 vsm_cod_contable = v_cod_contable, vsm_estado_atencion = 'A'
						where vsm_vestab = v_estab_v and vsm_vnumero = v_codigo_v and vsm_modulo = v_modulo and vsm_smodulo = v_smodulo;
						
						select count(*) , sum(vsm_precio) into _cuenta_s, _total_sm from val_atencion_smodulos where vsm_vestab = v_estab_v and vsm_vnumero = v_codigo_v and vsm_facturado = 'S';
						select count(*) into _cuenta_t from val_atencion_smodulos where vsm_vestab = v_estab_v and vsm_vnumero = v_codigo_v;
	
						update val_atencion_modulos set vm_costo = _total_sm, vm_precio = _total_sm
							where vm_vestab = v_estab_v and vm_vnumero = v_codigo_v and vm_modulo = v_modulo;
	
						Update valorizaciones Set val_total = _total_sm, val_impuesto = (_total_sm/1.18) * 0.18, val_subtotal = _total_sm/1.18, val_estab_i = v_estab_v , val_codigo_i = v_codigo_v
							where val_codigo = v_codigo_v and val_estab = v_estab_v ;
	
						_id_valoriza_act := (sp_crud_valoriza_addms(v_codigo_v, v_estab_v, v_modulo, v_smodulo));
	
						_total_sm:= _total_smr - v_total;
	
						Update valorizaciones Set val_total = _total_sm, val_impuesto = (_total_sm/1.18) * 0.18, val_subtotal = _total_sm/1.18, val_estab_i = v_estab_v , val_codigo_i = v_codigo_v
						where val_codigo = _id_valoriza_act and val_estab = v_estab_v ;
	
						update val_atencion_modulos set vm_costo = _total_sm, vm_precio = _total_sm
						where vm_vestab = v_estab_v and vm_vnumero = _id_valoriza_act and vm_modulo = v_modulo;
	
						update val_atencion_smodulos set vsm_costo= _total_sm, vsm_precio = _total_sm, vsm_facturado = 'N', vsm_cod_contable = v_cod_contable, vsm_estado_atencion = 'G'
						where vsm_vestab = v_estab_v and vsm_vnumero = _id_valoriza_act and vsm_modulo = v_modulo and vsm_smodulo = v_smodulo;
						
					else
	
						update val_atencion_smodulos set vsm_costo= v_total, vsm_precio = v_total, vsm_facturado = 'S', vsm_fac_serie = serie, vsm_fac_numero = v_numero, vsm_fac_tipo = tipo,
						vsm_cod_contable = v_cod_contable, vsm_estado_atencion = 'A'
						where vsm_vestab = v_estab_v and vsm_vnumero = v_codigo_v and vsm_modulo = v_modulo and vsm_smodulo = v_smodulo;
	
						select count(*) , sum(vsm_precio) into _cuenta_s, _total_sm 
						from val_atencion_smodulos where vsm_vestab = v_estab_v and vsm_vnumero = v_codigo_v and vsm_facturado = 'S' and vsm_modulo::varchar||vsm_smodulo::varchar!='223';
						
						select count(*) into _cuenta_t from val_atencion_smodulos where vsm_vestab = v_estab_v and vsm_vnumero = v_codigo_v;
	
						update val_atencion_modulos set vm_costo = _total_sm, vm_precio = _total_sm
						where vm_vestab = v_estab_v and vm_vnumero = v_codigo_v and vm_modulo = v_modulo;
						
						Update valorizaciones Set val_total = _total_sm, val_impuesto = (_total_sm/1.18) * 0.18, val_subtotal = _total_sm/1.18
						where val_codigo = v_codigo_v and val_estab = v_estab_v ;				
	
						if _cuenta_s = _cuenta_t then
							Update valorizaciones Set val_facturado = 'S'
							where val_codigo = v_codigo_v and val_estab =v_estab_v ;
						end if;
	
					end if;
				
				end loop;
			end if;
		
		else 
			
			select id into v_id_factura from facturas f where f.fac_serie = serie And fac_cod_tributario=_ruc And f.fac_total=total::numeric and f.fac_fecha BETWEEN NOW() - INTERVAL '5 MINUTE' AND NOW() order by id desc limit 1;
		
		End if;
		
		else 
			
			select id into v_id_factura from facturas f where f.fac_serie = serie And f.fac_numero=v_numero_ order by id desc limit 1;
		
		End if;
	
	End if;

	return v_id_factura;

end;
$function$
;
