create function cl_get_call_log_win7_by_end_time(period_from timestamp with time zone, period_to timestamp with time zone, source_type integer, source_filter character varying, destination_type integer, destination_filter character varying, calls_type integer, call_time_filter_type integer, call_time_filter_from interval, call_time_filter_to interval, hide_pcalls boolean) returns SETOF cl_call_log_data
    language plpgsql
as
$$


DECLARE
call_info record;
	log_data cl_call_log_data;
	connected_log_data cl_call_log_data;
	prepared_src_filter character varying;
	prepared_dst_filter character varying;
	billing_roundup character varying;
BEGIN

	prepared_src_filter := source_filter;
	if source_type = 5 then
		prepared_src_filter := prepared_src_filter || '%';
	elsif source_type = 6 OR source_type = 8 then
		prepared_src_filter := '%' || prepared_src_filter || '%';
end if;

	prepared_dst_filter := destination_filter;
	if destination_type = 5 then
		prepared_dst_filter := prepared_dst_filter || '%';
	elsif destination_type = 6 OR destination_type = 8 then
		prepared_dst_filter := '%' || prepared_dst_filter || '%';
end if;

select into billing_roundup p.value from parameter p where p."name" = 'BILLINGROUNDUP';

for call_info in
		--
		with
			calls as (
				select distinct on (cl_calls.id)
					cl_calls.id
				from
					cl_calls
					join cl_segments segments on segments.call_id = cl_calls.id
					join cl_participants src_participants on src_participants.id = segments.src_part_id
					join cl_participants dst_participants on dst_participants.id = segments.dst_part_id
					join cl_party_info_view src_info on src_info.id = src_participants.info_id
					join cl_party_info_view dst_info on dst_info.id = dst_participants.info_id
				where
					cl_calls.end_time between period_from and period_to and
					case when calls_type = 0 then true when calls_type = 1 then is_answered = true when calls_type = 2 then is_answered = false end and
					case
						when source_type = 0 then true
						when source_type = 1 then src_info.internal
						when source_type = 2 then src_info.internal and src_info.dn = any(string_to_array(source_filter, ' '))
						when source_type = 3 then src_info.external
						when source_type = 8 then src_info.display_name ilike prepared_src_filter OR src_info.dn ilike prepared_src_filter
						when source_type in (4, 5, 6) then
							case
								when src_info.external then src_info.caller_number like prepared_src_filter
								when src_info.dn_type = 12 then src_info.display_name like prepared_src_filter
								else src_info.dn like prepared_src_filter
							end
					end and
					case
						when destination_type = 0 then true
						when destination_type = 1 then dst_info.internal
						when destination_type = 2 then dst_info.internal and dst_info.dn = any(string_to_array(destination_filter, ' '))
						when destination_type = 3 then dst_info.external
						when destination_type = 7 then src_participants.billing_group = destination_filter
						when destination_type = 8 then dst_info.display_name ilike prepared_dst_filter OR dst_info.dn ilike prepared_dst_filter
						when destination_type in (4, 5, 6) then
							case
								when dst_info.external then dst_info.caller_number like prepared_dst_filter
								when dst_info.dn_type = 12 then dst_info.display_name like prepared_dst_filter
								else dst_info.dn like prepared_dst_filter
							end
					end and
					case
						when call_time_filter_type = 0 then true
						when call_time_filter_type = 1 then cl_calls.ringing_dur between call_time_filter_from and call_time_filter_to
						when call_time_filter_type = 2 then cl_calls.talking_dur between call_time_filter_from and call_time_filter_to
						when call_time_filter_type = 3 then cl_calls.q_wait_dur between call_time_filter_from and call_time_filter_to
					end and
					case
						when hide_pcalls then not (cl_calls.is_answered = false and src_info.dn_type = 4) -- calls from queue is polling calls
						else true
					end
				group by cl_calls.id
				),
			segments as (
				select
					calls.id as call_id,
					seq_order,
					seq_group,
					cl_segments.start_time, cl_segments.end_time,
					src_part_id as src_part_id,
					dst_part_id as dst_part_id,
					cl_segments.type,
					action_id as act ,
					action_party_id as act_part_id
				from cl_segments join calls on calls.id = cl_segments.call_id)
select
    segments.call_id,
    seq_order,
    seq_group,
    segments.start_time as start_time,
    segments.end_time as end_time,
    type as seg_type,
    src_info.dn_type as src_dn_type,
    src_info.dn as src_dn,
    src_info.caller_number as src_caller_number,
    src_info.extended_display_name as src_display_name,
    src_participants.recording_url src_recording_url,
    dst_info.dn_type as dst_dn_type,
    dst_info.dn as dst_dn,
    dst_info.caller_number as dst_caller_number,
    dst_info.extended_display_name as dst_display_name,
    dst_participants.recording_url dst_recording_url,
    segments.act as act,
    act_info.dn_type as act_dn_type,
    act_info.dn as act_dn,
    act_info.caller_number as act_caller_number,
    act_info.extended_display_name as act_display_name,
    row_number() over (PARTITION BY segments.call_id order by segments.call_id, segments.seq_order desc nulls last) as subrow_desc_number
from
    segments
        join cl_participants src_participants on src_participants.id = segments.src_part_id
        join cl_participants dst_participants on dst_participants.id = segments.dst_part_id
        join cl_party_info_view src_info on src_info.id = src_participants.info_id
        join cl_party_info_view dst_info on dst_info.id = dst_participants.info_id
        left outer join (cl_participants act_participants join cl_party_info_view act_info on act_info.id = act_participants.info_id) on act_participants.id = segments.act_part_id
order by call_id, seq_order
         --
    loop
		log_data := (
				call_info.call_id,
		             call_info.seq_group,
		             call_info.start_time,
				call_info.src_dn_type,
		             call_info.src_dn,
		             call_info.src_caller_number,
		             call_info.src_display_name,
				call_info.dst_dn_type,
		             call_info.dst_dn,
		             call_info.dst_caller_number,
		             call_info.dst_display_name,
				call_info.act,
		             call_info.act_dn_type,
		             call_info.act_dn,
		             call_info.act_caller_number,
		             call_info.act_display_name,
                case when call_info.seg_type = 1 then call_info.end_time - call_info.start_time end,
                case when call_info.seg_type = 2 then call_info.end_time - call_info.start_time end
                );

log_data.answered := cl_is_answered_common(call_info.seg_type, log_data.source_type, log_data.destination_type, log_data.action_type);

		log_data.recording_url := coalesce(call_info.src_recording_url, call_info.dst_recording_url, '');

		log_data.subrow_desc_number = call_info.subrow_desc_number;
		if hide_pcalls and log_data.source_type = 4 and log_data.action_type = 12 then -- if queue call to smb, smb answers and queue transfer call to another then skip this segment
			continue;
end if;
	    if hide_pcalls and call_info.seg_type = 1 and log_data.action_type = 15 then -- Remove missed polling call queue ringing
			continue;
end if;

		if log_data.action_type = 1 and log_data.destination_type not in (2, 3) then
			connected_log_data := log_data;
else
			if connected_log_data.call_id is not null then
				log_data.ringing_duration := connected_log_data.ringing_duration;
				log_data.start_time = connected_log_data.start_time;

				if log_data.destination_type = 12 then
					log_data.destination_type := connected_log_data.destination_type;
					log_data.destination_dn := connected_log_data.destination_dn;
					log_data.destination_caller_id := connected_log_data.destination_caller_id;
					log_data.destination_display_name := connected_log_data.destination_display_name;
end if;

				connected_log_data.call_id := null;
end if;

			return next log_data;
end if;
end loop;
END;
$$;

alter function cl_get_call_log_win7_by_end_time(timestamp with time zone, timestamp with time zone, integer, varchar, integer, varchar, integer, integer, interval, interval, boolean) owner to phonesystem;

