CREATE OR REPLACE FUNCTION public.sp_crud_factura(
    serie character varying,
    numero integer,
    tipo character varying,
    ubicacion integer,
    persona integer,
    total character varying,
    descripcion character varying,
    cod_contable character varying,
    codigo_v integer,
    estab_v integer,
    modulo integer,
    smodulo integer,
    descuento numeric,
    accion character varying,
    p_id_usuario integer)
  RETURNS character varying AS
$BODY$
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;

begin

	--select CAST(total AS numeric) into _total;
	_total := to_number(total,'9999999999.99');
	select CAST(descuento AS numeric) into _descuento;

	Case accion

		When 'f' then

			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;
				
				--select fac_numero + 1 into _numero from facturas where fac_serie = serie order by fac_numero desc limit 1;
				

				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 persona_id from afiliaciones where ubicacion_id=ubicacion;
					select numero_documento, apellido_paterno || ' '|| apellido_materno || ' '|| nombres, direccion, email into  _ruc, _razon_social, _direccion, _correo
						from personas
						--Where id=ubicacion;
						Where id=persona;
				end if;

				select upper(f_convnl(trunc(_total))) || ' CON '|| Case When _decimal_letras = '' Then '0' Else _decimal_letras End ||'/100 SOLES' into _total_letras;

				--
				--select coalesce(max(fac_numero),'0')+1 into _numero from facturas where fac_serie = serie;

				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,(select coalesce(max(fi.fac_numero),'0')+1 from facturas fi where fi.fac_serie = serie),now(),_razon_social,_direccion,_ruc,'', '',
						CAST(total AS numeric),0.00,0.00,((CAST(total AS numeric)/1.18)*0.18),CAST(total AS numeric),3003,_total_letras,'SOLES',18,0.000,'P','N',now(),now(),
						now(),now(),'',2, 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, '', '', '', '', CAST(descripcion AS numeric), p_id_usuario);

				idp := (SELECT currval('facturas_id_seq'));

			Else
				idp:=0;
			End if;
		When 'd' then

			if numero > 0 Then

				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,numero,tipo,ubicacion,1,descripcion,_total/1.18,(_total/1.18),(_total/1.18)*0.18,descuento,_total,10,cod_contable,0,'ZZ' );

				select vsm_precio into _total_sm from val_atencion_smodulos where vsm_vestab = estab_v and vsm_vnumero = codigo_v and vsm_modulo = modulo and vsm_smodulo = smodulo;

				_total_smr:= _total_sm;

				if _descuento <> 0 then
					_total_sm:= _total_sm - (_total_sm * _descuento)/100;
					_total_smr:= _total_sm;
				End if;

					
				if _total_sm <> _total  then
					
					update val_atencion_smodulos set vsm_costo= _total, vsm_precio = _total, vsm_facturado = 'S', vsm_fac_serie = serie, vsm_fac_numero = numero, vsm_fac_tipo = tipo,
					 vsm_cod_contable = cod_contable, vsm_estado_atencion = 'A'
					where vsm_vestab = estab_v and vsm_vnumero = codigo_v and vsm_modulo = modulo and vsm_smodulo = smodulo;
					
					select count(*) , sum(vsm_precio) into _cuenta_s, _total_sm from val_atencion_smodulos where vsm_vestab = estab_v and vsm_vnumero = codigo_v and vsm_facturado = 'S';
					select count(*) into _cuenta_t from val_atencion_smodulos where vsm_vestab = estab_v and vsm_vnumero = codigo_v;

					update val_atencion_modulos set vm_costo = _total_sm, vm_precio = _total_sm
						where vm_vestab = estab_v and vm_vnumero = codigo_v and vm_modulo = 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 = estab_v , val_codigo_i = codigo_v
						where val_codigo = codigo_v and val_estab = estab_v ;

					_id_valoriza_act := (sp_crud_valoriza_addms(codigo_v, estab_v, modulo, smodulo));

					_total_sm:= _total_smr - _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 = estab_v , val_codigo_i = codigo_v
					where val_codigo = _id_valoriza_act and val_estab = estab_v ;

					update val_atencion_modulos set vm_costo = _total_sm, vm_precio = _total_sm
					where vm_vestab = estab_v and vm_vnumero = _id_valoriza_act and vm_modulo = modulo;

					update val_atencion_smodulos set vsm_costo= _total_sm, vsm_precio = _total_sm, vsm_facturado = 'N', vsm_cod_contable = cod_contable, vsm_estado_atencion = 'G'
					where vsm_vestab = estab_v and vsm_vnumero = _id_valoriza_act and vsm_modulo = modulo and vsm_smodulo = smodulo;

					
				else

					update val_atencion_smodulos set vsm_costo= _total, vsm_precio = _total, vsm_facturado = 'S', vsm_fac_serie = serie, vsm_fac_numero = numero, vsm_fac_tipo = tipo,
					 vsm_cod_contable = cod_contable, vsm_estado_atencion = 'A'
					where vsm_vestab = estab_v and vsm_vnumero = codigo_v and vsm_modulo = modulo and vsm_smodulo = smodulo;

					select count(*) , sum(vsm_precio) into _cuenta_s, _total_sm 
					from val_atencion_smodulos where vsm_vestab = estab_v and vsm_vnumero = 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 = estab_v and vsm_vnumero = codigo_v;

					update val_atencion_modulos set vm_costo = _total_sm, vm_precio = _total_sm
						where vm_vestab = estab_v and vm_vnumero = codigo_v and vm_modulo = 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 = codigo_v and val_estab = estab_v ;				

					if _cuenta_s = _cuenta_t   then
						Update valorizaciones Set val_facturado = 'S'
						where val_codigo = codigo_v and val_estab =estab_v ;
					end if;

				end if;
			

				idp:=numero;

			Else
				idp:=0;
			End if;

	End Case;

	return idp;
/*
	EXCEPTION
	WHEN OTHERS THEN
        idp:=-1;

	return idp;
*/

/*

Select sp_crud_factura('T001',0, 'TK', 3070,924,'17.50','119','',0,0,0,0,0,'f',2);
Select sp_crud_factura('T001',6281, 'TK', 1,0,'17.50','LBSP SERVICIO','7040205',8824,1,1,5,50,'d',2);
Select sp_crud_factura('T001',6281, 'TK', 1,0,'17.50','LBSP SERVICIO','7040205',8824,1,1,4,50,'d',2);
Select sp_crud_factura('T001',6281, 'TK', 1,0,'17.50','LBSP SERVICIO','7040205',8824,1,1,1,50,'d',2);

Select sp_crud_factura(T001,0, TK, 3070,2449,10,121,,0,0,0,0,0,f,2)
Select sp_crud_factura('T001',2870, 'TK', 1,0,'2','LBCA SERVICIO','7040204',8390,1,1,4,0,'d',2);

select * from valorizaciones order by 1 desc limit 10;

select * from valorizaciones where  val_codigo = 8824 order by 1 desc limit 100;

select * from val_atencion_smodulos where vsm_vnumero = 9607  limit 100;

select * from factura_detalles where facd_numero = 6281
delete from factura_detalles where facd_numero = 6281
"9315"

select * from facturas;
select * from factura_detalles;

select * from valorizaciones

delete from ind_atenciones;
delete from ind_atencion_modulos;
delete from ind_atencion_smodulos;
delete from valorizaciones;
delete from val_atencion_modulos;
delete from val_atencion_smodulos;

TRUNCATE TABLE ind_atenciones RESTART IDENTITY;
TRUNCATE TABLE ind_atencion_modulos RESTART IDENTITY;
TRUNCATE TABLE ind_atencion_smodulos RESTART IDENTITY;

TRUNCATE TABLE valorizaciones RESTART IDENTITY;
TRUNCATE TABLE val_atencion_modulos RESTART IDENTITY;
TRUNCATE TABLE val_atencion_smodulos RESTART IDENTITY;

delete from facturas;
delete from factura_detalles;

TRUNCATE TABLE facturas RESTART IDENTITY;
TRUNCATE TABLE factura_detalles RESTART IDENTITY;

*/

end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
