We are now retrieving a subset of the Freshdesk tickets, but badly miss the SearchTickets table function.
For now, we have solved it by a procedure and a download module to retrieve the tickets of a specific month into an Excel sheet for use by Power BI.
Is it possible to add the Search Tickets API (and remove query from Tickets)?
Procedure
create or replace view LessTickets
as
select *
from Tickets(updated_since => add_months(trunc(sysdateutc, -1), -4))
;
create or replace procedure instantiate_tickets
( l_date date
)
--
-- Instantiate tickets created on a specific date.
--
as
l_orig_system_group guid;
l_max_call_per_grp_per_day number := 150;
begin
l_orig_system_group := newid();
--
-- Always 30 per page, and retrieve at most 300 records (page 1..10).
--
insert into nativeplatformscalarrequests@fdk
( url
, orig_system_group
)
select 'https://acme.freshdesk.com/api/v2/search/tickets?query="group_id:' || grp.id || ' AND created_at:''' || to_char(l_date, 'YYYY-MM-DD') || '''"&page=' || to_char(rge.value)
, l_orig_system_group
from groups grp
join range(ceil(l_max_call_per_grp_per_day / 30))@datadictionary rge
where grp.name like 'SC%'
;
insert into Tickets@InMemoryStorage
( company_id
, subject
, id
, created_at
, email
, group_id
, phone
)
select jte.company_id
, jte.subject
, jte.id
, jte.created_at
, jte.email
, jte.group_id
, jte.phone
from nativeplatformscalarrequests@fdk nst
join jsontable
( 'results[*]'
passing nst.result_text
columns altref varchar2 path 'custom_fields.altref'
, associated_tickets_count int64 path 'associated_tickets_count'
, business_impact varchar2 path 'custom_fields.business_impact'
, cf_analysis_category varchar2 path 'custom_fields.cf_analysis_category'
, cf_is_this_tickets_content_suitable_for_one_of_intacts_content_hubs varchar2 path 'custom_fields.cf_is_this_tickets_content_suitable_for_one_of_intacts_content_hubs'
, company_id int64 path 'company_id'
, company_name varchar2 path 'company.name'
, created_at datetime path 'created_at'
, deleted char path 'deleted'
, description varchar2 path 'description'
, description_html varchar2 path 'description_html'
, description_text varchar2 path 'description_text'
, due_by datetime path 'due_by'
, email varchar2 path 'email'
, email_config_id int64 path 'email_config_id'
, facebook_id varchar2 path 'facebook_id'
, fr_due_by datetime path 'fr_due_by'
, fr_escalated char path 'fr_escalated'
, group_id int64 path 'group_id'
, id int64 path 'id'
, internal_agent_id int64 path 'internal_agent_id'
, internal_group_id int64 path 'internal_group_id'
, is_escalated char path 'is_escalated'
, name varchar2 path 'name'
, nr_due_by datetime path 'nr_due_by'
, nr_escalated char path 'nr_escalated'
, phone varchar2 path 'phone'
, priority int16 path 'priority'
, product_id int64 path 'product_id'
, requester_email varchar2 path 'requester.email'
, requester_id varchar2 path 'requester_id'
, requester_mobile varchar2 path 'requester.mobile'
, requester_name varchar2 path 'requester.name'
, requester_phone varchar2 path 'requester.phone'
, requester_twitter_id varchar2 path 'requester.twitter_id'
, responder_id int64 path 'responder_id'
, source int16 path 'source'
, spam varchar2 path 'spam'
, stats_agent_responded_at datetime path 'stats.agent_responded_at'
, stats_closed_at datetime path 'stats.closed_at'
, stats_first_responded_at datetime path 'stats.first_responded_at'
, stats_pending_since datetime path 'stats.pending_since'
, stats_reopened_at datetime path 'stats.reopened_at'
, stats_requester_responded_at datetime path 'stats.requester_responded_at'
, stats_resolved_at datetime path 'stats.resolved_at'
, stats_status_updated_at datetime path 'stats.status_updated_at'
, status int16 path 'status'
, subject varchar2 path 'subject'
, tfs__ontime varchar2 path 'custom_fields.tfs__ontime'
, ticket_type varchar2 path 'ticket_type'
, to_emails varchar2 path 'to_emails'
, twitter_id varchar2 path 'twitter_id'
, type varchar2 path 'type'
, updated_at datetime path 'updated_at'
, version_number varchar2 path 'custom_fields.version_number'
) jte
where nst.orig_system_group = l_orig_system_group
and nst.result_text != '{"results":[],"total":0}'
;
end;
HTML Module
declare
p_run boolean;
--
l_workbook excel_workbook;
l_worksheet excel_worksheet;
l_xlsx_filename varchar2;
l_binary blob;
l_year number;
l_month number;
begin
--
-- Get parameters.
--
p_run := cast(cloud_http.get_request_form_value('p_run') as boolean);
--
if coalesce(p_run, false) = false
then
--
-- Enter parameters manually (currently solely a button to start synchronization).
--
cloud_http.set_use_template(true);
cloud_http.set_template_step_name(translate_resources('{res:itgen_parameters}'));
cloud_http.append_line_to_response_body_text('<form method="post" enctype="multipart/form-data">');
cloud_http.append_line_to_response_body_text('<input type="hidden" id="p_run" name="p_run" value="true"/>');
cloud_http.append_line_to_response_body_text('<ul>');
cloud_http.append_line_to_response_body_text('<li><label for="p_year">Year</label></li><li><input type="number" id="p_year" name="p_year" value="' || to_char(year(add_months(sysdateutc, -1))) || '" required/></li>');
cloud_http.append_line_to_response_body_text('<li><label for="p_month">Month</label></li><li><input type="number" id="p_month" name="p_month" value="' || to_char(month(add_months(sysdateutc, -1))) || '" required/></li>');
cloud_http.append_line_to_response_body_text('<li><input type="submit" value="Download"/></li>');
cloud_http.append_line_to_response_body_text('</ul>');
cloud_http.append_line_to_response_body_text('</form>');
else
--
-- Download tickets from Freshdesk.
--
l_year := to_number(cloud_http.get_request_form_value('p_year'));
l_month := to_number(cloud_http.get_request_form_value('p_month'));
l_xlsx_filename := 'download-tickets-' || to_char(l_year) || '-' || to_char(l_month) || '.xlsx';
l_workbook := excel.new();
--
-- Add a worksheet with a title.
--
l_worksheet := excel.add_worksheet
( l_workbook
, 'Parameters'
);
excel.set_cell_contents(l_worksheet, 'A1', 'Download Tickets from Freshdesk using Invantive Cloud created in a month');
excel.set_cell_contents(l_worksheet, 'A3', 'Created (UTC)');
excel.set_cell_contents(l_worksheet, 'B3', sysdateutc);
excel.set_cell_contents(l_worksheet, 'A4', 'Year');
excel.set_cell_contents(l_worksheet, 'B4', l_year);
excel.set_cell_contents(l_worksheet, 'A5', 'Month');
excel.set_cell_contents(l_worksheet, 'B5', l_month);
--
l_worksheet := excel.add_worksheet
( l_workbook
, 'data'
);
create or replace table Tickets@InMemoryStorage
( company_id int64
, subject varchar2
, id int64
, created_at datetime
, email varchar2
, group_id int64
, phone varchar2
);
--
for r
in
( select day_date
from calendar@datadictionary
where year_number = l_year
and month_number = l_month
order
by day_date
)
loop
instantiate_tickets(r.day_date);
--
excel.fill_using_query(l_worksheet, 'select * from Tickets@inmemorystorage');
end loop;
--
-- Retrieve the resulting XLSX-file.
--
l_binary := excel.export_to_xlsx(l_workbook);
--
-- Return the XLSX file to the user.
--
cloud_http.set_response_body_binary(l_binary);
cloud_http.set_response_content_type('application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
cloud_http.set_response_header_value('Content-Disposition', 'attachment; filename="' || l_xlsx_filename || '"');
end if;
end;