De planitems in Bouw7 zijn beschikbaar via de API. Is het mogelijk om dit toe te voegen aan de connector?
De Apollo API’s van Bouw7 zijn op dit moment niet gekoppeld; zie ook New Apollo APIs for Bouw7 (Exact Online Bouw).
Uitsluitend de zogenaamde Heimdall API’s zijn gekoppeld.
Indien gewenst kan met een stuk Invantive UniversalSQL-code de Apollo-API uitgelezen worden zoals beschreven in Toegang vanuit Power BI tot bewakingscodes in Bouw7 - 5 van forums (onder kopje “De nieuwe project-security-links API is geen onderdeel van Bouw7 Apollo, maar van Heimdal”).
De data kan opgehaald worden met Basic authentication via het Bouw7-datamodel als 1 lange stroom van data door een applicatiemodule toe te voegen in Invantive Cloud met de volgende code:
declare
l_unique_id varchar2;
l_page_total pls_integer;
l_totalresults pls_integer;
l_page_size pls_integer := 1000;
l_page pls_integer;
l_done boolean;
l_retrieval_succesful boolean;
l_http_status_code int;
l_contents varchar2;
l_first boolean;
l_json varchar2;
begin
cloud_http.set_response_content_type('application/json');
cloud_http.set_response_status_code(200);
cloud_http.append_to_response_body_text('[ ');
l_first := true;
l_done := false;
l_totalresults := null;
l_page := 1;
--
while not l_done
loop
dbms_output.put_line('Retrieve plan items starting at page ' || to_char(l_page) || '.');
l_unique_id := to_char(newid());
insert into Bouw7.Native.NATIVEPLATFORMSCALARREQUESTS
( url
, HTTP_METHOD
, ORIG_SYSTEM_GROUP
, ORIG_SYSTEM_REFERENCE
, HTTP_DISK_CACHE_USE
, HTTP_DISK_CACHE_SAVE
, HTTP_DISK_CACHE_MAX_AGE_SEC
)
select 'https://apollo.bouw7.nl/search/plan-items?q='
|| urlencode
( 'createdAt > @("jan. 1970 at 00:00") limit '
|| to_char(l_page_size)
|| ' page '
|| to_char(l_page)
)
, 'GET'
, null
, l_unique_id
, true
, true
, 3600 * 20 /* 20 hours cache */
;
select SUCCESSFUL
, HTTP_STATUS_CODE
, result_text
into l_retrieval_succesful
, l_http_status_code
, l_contents
from Bouw7.Native.NATIVEPLATFORMSCALARREQUESTS
where orig_system_reference = l_unique_id
;
--
-- Any error returned?
--
if not l_retrieval_succesful
or l_http_status_code != 200
then
raise_application_error(-20163, 'Retrieval failed.');
else
for r
in
( select *
from jsontable
( 'items[*]'
passing l_contents
columns id int path 'id'
, name varchar2 path 'name'
, startDate datetime path 'startDate'
, endDate datetime path 'endDate'
, hours number path 'hours'
, requisite int path 'requisite'
, remark varchar2 path 'remark'
, color varchar2 path 'color'
, isProcessed boolean path 'isProcessed'
, createdAt datetime path 'createdAt'
, updatedAt datetime path 'updatedAt'
, project_id int path 'project.id'
, project_name varchar2 path 'project.name'
, project_number varchar2 path 'project.number'
, project_street_name varchar2 path 'project.streetName'
, project_building_number varchar2 path 'project.buildingNumber'
, project_city varchar2 path 'project.city'
, project_status_id varchar2 path 'project.status.id'
, project_status_name varchar2 path 'project.status.name'
)
)
loop
l_json := '{' || jsonelement("id", r.id, false)
|| ',' || jsonelement("name", r.name, false)
|| ',' || jsonelement("startDate", r.startDate, false)
|| ',' || jsonelement("hours", r.hours, false)
|| ',' || jsonelement("requisite", r.requisite, false)
|| ',' || jsonelement("remark", r.remark, false)
|| ',' || jsonelement("color", r.color, false)
|| ',' || jsonelement("isProcessed", r.isProcessed, false)
|| ',' || jsonelement("createdAt", r.createdAt, false)
|| ',' || jsonelement("updatedAt", r.updatedAt, false)
|| ',' || jsonelement("project_id", r.project_id, false)
|| ',' || jsonelement("project_name", r.project_name, false)
|| ',' || jsonelement("project_number", r.project_number, false)
|| ',' || jsonelement("project_street_name", r.project_street_name, false)
|| ',' || jsonelement("project_building_number", r.project_building_number, false)
|| ',' || jsonelement("project_city", r.project_city, false)
|| ',' || jsonelement("project_status_id", r.project_status_id, false)
|| ',' || jsonelement("project_status_name", r.project_status_name, false)
|| '}'
;
if l_first
then
l_first := false;
else
cloud_http.append_to_response_body_text(', ');
end if;
cloud_http.append_to_response_body_text(l_json);
end loop;
end if;
--
-- Request next page when there is one.
-- Number of rows in result set doesn't change after initial retrieval.
--
if l_totalresults is null
then
select t.rowTotal
, t.pageTotal
into l_totalresults
, l_page_total
from jsontable
( '__metadata'
passing l_contents
columns rowTotal int path 'rows.total'
, pageTotal int path 'page.total'
) t
;
end if;
--
l_page := l_page + 1;
l_done := l_page > l_page_total;
end loop;
cloud_http.append_to_response_body_text(']');
end;
De bijbehorende Power Query is:
let
Bron = Json.Document(Web.Contents("https://app-online.cloud/apps/<<GUID>>/databases/acme-projecten-bouw7/modules/<<GUID>>/", [Timeout=#duration(0, 0, 30, 0)])),
#"Geconverteerd naar tabel" = Table.FromList(Bron, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Column1 uitgevouwen" = Table.ExpandRecordColumn(#"Geconverteerd naar tabel", "Column1", {"id", "name", "startDate", "hours", "requisite", "remark", "color", "isProcessed", "createdAt", "updatedAt", "project_id", "project_name", "project_number", "project_street_name", "project_building_number", "project_city", "project_status_id", "project_status_name"}, {"id", "name", "startDate", "hours", "requisite", "remark", "color", "isProcessed", "createdAt", "updatedAt", "project_id", "project_name", "project_number", "project_street_name", "project_building_number", "project_city", "project_status_id", "project_status_name"}),
#"Type gewijzigd" = Table.TransformColumnTypes(#"Column1 uitgevouwen",{{"id", Int64.Type}, {"name", type text}, {"startDate", type datetime}, {"hours", Int64.Type}, {"requisite", Int64.Type}, {"remark", type any}, {"color", type text}, {"isProcessed", type logical}, {"createdAt", type datetime}, {"updatedAt", type datetime}, {"project_id", Int64.Type}, {"project_name", type text}, {"project_number", type text}, {"project_street_name", type text}, {"project_building_number", Int64.Type}, {"project_city", type text}, {"project_status_id", Int64.Type}, {"project_status_name", type text}})
in
#"Type gewijzigd"
Dit topic is 7 dagen na het laatste antwoord automatisch gesloten. Nieuwe antwoorden zijn niet meer toegestaan.