CREATE OR REPLACE FUNCTION public.sp_crud_automatico_new(fecha character varying)
  RETURNS character varying AS
$BODY$
declare
	entradas_mes record;
	entradas record;
	idp integer;
	v_cant numeric;
	v_cant_dia numeric;
	_fecha varchar;
begin
		
		For entradas_mes In 
		select t1.id,t2.plan_tipo,t1.plan_id,coalesce(t2.plan_ubicacion,0)plan_ubicacion,persona_id,plan_precio,t2.periodo,
		(case 	when t2.plan_tipo='E' then 'ce' 
			when t2.plan_tipo='P' then 'cp' 
		end)accion
		,(case 	when t2.periodo='MENSUAL' then date_trunc('month', fecha::date)::date 
			when t2.periodo='SEMANAL' then fecha::date - (extract(dow from fecha::date)::int - 1) 
		end)plan_fecha
		from afiliaciones t1
		inner join plan_atenciones t2 on t1.plan_id=t2.id
		where t2.plan_tipo='P'
		And t1.estado='1'
		And coalesce(t1.titular_id,0)=0 
		Order by t1.id
		Loop
			select count(*) into v_cant
			from valorizaciones t1
			inner join val_atencion_modulos t2 on t1.val_estab = t2.vm_vestab And t1.val_codigo = t2.vm_vnumero
			inner join val_atencion_smodulos t3 on t2.vm_vestab = t3.vsm_vestab And t2.vm_vnumero = t3.vsm_vnumero And t2.vm_modulo = t3.vsm_modulo
			Where t1.val_estab=1 
			And t1.val_tipo=entradas_mes.plan_tipo 
			And t1.val_anulado='N' And t1.val_facturado='N' And t2.vm_estado='A' And t2.vm_eliminado='N' And t3.vsm_estado='A' And t3.vsm_eliminado='N'
			And t1.val_persona = entradas_mes.persona_id::integer 
			And t1.val_plan = entradas_mes.plan_id::integer 
			And coalesce(t1.val_ubicacion,0) = entradas_mes.plan_ubicacion::integer 
			And t1.val_tipo = 'P'
			And to_char(val_fecha,'dd/mm/YYYY')=to_char(entradas_mes.plan_fecha,'dd/mm/YYYY');

			if v_cant = 0 then
				idp:= (Select sp_crud_ind_atencion_valoriza_automatico(1,entradas_mes.plan_id::integer,entradas_mes.persona_id::integer,entradas_mes.plan_ubicacion::integer,entradas_mes.accion,entradas_mes.plan_fecha::varchar));
			End if;
		End Loop;
		
		_fecha = to_char((fecha::date - interval '1 day'),'dd/mm/YYYY');
		/*****EMPRESA**********/
		For entradas In 
		select t1.id,t1.val_tipo,t1.val_plan,t1.val_ubicacion,t1.val_fecha,
		t1.val_persona,t3.vsm_modulo,t3.vsm_smodulo,t1.val_total
		,coalesce(t4.periodo,'DIA')periodo
		,(case 	when t4.periodo='MENSUAL' then date_trunc('month', fecha::date)::date 
			when t4.periodo='SEMANAL' then fecha::date - (extract(dow from fecha::date)::int) 
		end)plan_fecha
		from valorizaciones t1
		inner join val_atencion_modulos t2 on t1.val_estab = t2.vm_vestab And t1.val_codigo = t2.vm_vnumero
		inner join val_atencion_smodulos t3 on t2.vm_vestab = t3.vsm_vestab And t2.vm_vnumero = t3.vsm_vnumero And t2.vm_modulo = t3.vsm_modulo
		inner join plan_atenciones t4 on t1.val_plan=t4.id
		Where t1.val_estab=1 And t1.val_anulado='N' /*And t1.val_facturado='S'*/
		And t2.vm_estado='A' And t2.vm_eliminado='N' And t3.vsm_estado='A' And t3.vsm_eliminado='N'
		And t1.val_estado_atencion='G'
		And t1.val_fecha<=_fecha::date
		And (case 	when t4.periodo='MENSUAL' then date_trunc('month', fecha::date)::date 
			when t4.periodo='SEMANAL' then fecha::date - (extract(dow from fecha::date)::int) 
		end)>=t1.val_fecha
		And t1.val_tipo = 'E'
		--And t1.val_codigo not in (select vsm_vnumero from val_atencion_smodulos where vsm_modulo=2 And vsm_smodulo in (1,2,6,11,19,20))
		And (t3.vsm_modulo in(1,3,4) or (t3.vsm_modulo=2 And t3.vsm_smodulo not in (1,2,6,11,19,20,50,21)))
		Order by t1.val_tipo,t1.id
		Loop
			select count(*) into v_cant_dia
			from valorizaciones t1
			inner join val_atencion_modulos t2 on t1.val_estab = t2.vm_vestab And t1.val_codigo = t2.vm_vnumero
			inner join val_atencion_smodulos t3 on t2.vm_vestab = t3.vsm_vestab And t2.vm_vnumero = t3.vsm_vnumero And t2.vm_modulo = t3.vsm_modulo
			left join plan_atenciones t4 on t1.val_plan=t4.id
			Where t1.val_estab=1 
			And t1.val_tipo=entradas.val_tipo 
			--And t1.val_estado_atencion='A'
			And t1.val_anulado='N' And t1.val_facturado='N' And t2.vm_estado='A' And t2.vm_eliminado='N' And t3.vsm_estado='A' And t3.vsm_eliminado='N'
			And t1.val_plan = entradas.val_plan::integer 
			And t1.val_ubicacion = entradas.val_ubicacion::integer 
			And to_char(t1.val_fecha,'dd/mm/YYYY')=to_char(entradas.plan_fecha,'dd/mm/YYYY');

			if v_cant_dia = 0 then
				idp:= (Select sp_crud_ind_atencion_valoriza_automatico(1,entradas.val_plan::integer,0,entradas.val_ubicacion::integer,'ce',entradas.plan_fecha::varchar));
				update valorizaciones set val_estado_atencion='A' Where id=entradas.id;
			End if;
		End Loop;
		
		/*****PERSONA**********/
		/*
		For entradas In 
		select t1.id,t1.val_tipo,t1.val_plan,t1.val_ubicacion,t1.val_fecha,
		t1.val_persona,t3.vsm_modulo,t3.vsm_smodulo,t1.val_total
		from valorizaciones t1
		inner join val_atencion_modulos t2 on t1.val_estab = t2.vm_vestab And t1.val_codigo = t2.vm_vnumero
		inner join val_atencion_smodulos t3 on t2.vm_vestab = t3.vsm_vestab And t2.vm_vnumero = t3.vsm_vnumero And t2.vm_modulo = t3.vsm_modulo
		Where t1.val_estab=1 And t1.val_anulado='N' 
		And t2.vm_estado='A' And t2.vm_eliminado='N' And t3.vsm_estado='A' And t3.vsm_eliminado='N'
		And t1.val_estado_atencion='G'
		And t1.val_fecha<=fecha::date
		And t1.val_plan=0
		And t1.val_tipo = 'P'
		And (t3.vsm_modulo in(1,3,4) or (t3.vsm_modulo=2 And t3.vsm_smodulo not in (1,2,6,11,19,20,50,21)))
		Order by t1.val_tipo,t1.id
		Loop
			select count(*) into v_cant_dia
			from valorizaciones t1
			inner join val_atencion_modulos t2 on t1.val_estab = t2.vm_vestab And t1.val_codigo = t2.vm_vnumero
			inner join val_atencion_smodulos t3 on t2.vm_vestab = t3.vsm_vestab And t2.vm_vnumero = t3.vsm_vnumero And t2.vm_modulo = t3.vsm_modulo
			Where t1.val_estab=1 
			And t1.val_tipo=entradas.val_tipo 
			And t1.val_plan=0
			And t1.val_anulado='N' And t1.val_facturado='N' And t2.vm_estado='A' And t2.vm_eliminado='N' And t3.vsm_estado='A' And t3.vsm_eliminado='N'
			And t1.val_persona = entradas.val_persona::integer 
			And t1.val_ubicacion = entradas.val_ubicacion::integer 
			And t3.vsm_modulo = entradas.vsm_modulo::integer 
			And t3.vsm_smodulo = entradas.vsm_smodulo::integer 
			And to_char(t1.val_fecha,'dd/mm/YYYY')=fecha;

			if v_cant_dia = 0 then
				idp:= (Select sp_crud_ind_atencion_valoriza_automatico_add(1,entradas.val_persona::integer,entradas.vsm_modulo::integer,entradas.vsm_smodulo::integer,entradas.val_ubicacion::integer,entradas.val_total::numeric,'obs',fecha));
				update valorizaciones set val_estado_atencion='A' Where id=entradas.id;
			End if;
		End Loop;
		*/
		
	return idp;
	/*EXCEPTION
	WHEN OTHERS THEN
        idp:=-1;
	return idp;*/
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
