Noorse o met diagonale streep (Ø) wordt in het resultaat van de query getoond als underscore (_), hierdoor zijn tekeningen niet vindbaar voor de applicatie.
Query:
--
-- Dump all drawings from Exact Online to a folder.
--
-- DD-MM-YYYY Init Description
-- 22-11-2022 GLE Added version history.
--
local remark Maximum number of days to look back for newly added drawings.
local define LOOKBACK_DAYS "10"
local define DCT_PATH "\\Nas01\Access\Invantive\dct"
local define OUT_PATH "\\Nas01\Access\Invantive\out"
create or replace table dct@inmemorystorage
as
select dctx.attachments_document_item_code_attr
itemcode
, coalesce(normalize(dctx.attachments_document_item_code_attr, 60, false), 'none')
|| '-'
|| 'dct-'
|| to_char(dctx.attachments_document_number_attr)
|| '.'
|| coalesce(lower(regexp_replace(dctx.name, '[^.]*\.', '')), 'bin')
filename
, dctx.attachments_document_number_attr
hid
, dctx.attachments_document_item_id_attr
itemid
, dctx.attachments_document_item_code_attr
itemcode
, dctx.binarydata
, dctx.attachments_document_subject
subject
, dctx.name originalfilename
from exactonlinexml..documentattachmentsex
( Params_DateCreated_From => trunc(sysdate) - ${LOOKBACK_DAYS}
)
dctx
where dctx.attachments_document_item_id_attr is not null
--
-- Some items have multiple drawings attached. Make sure that when there
-- are multiple drawings that each one has a unique file name.
--
update DCT@InMemoryStorage
set filename = regexp_replace(filename, '(\.[a-z0-9]+)$', '-' || to_char(originalfilename) || '$1')
where filename
in
( select filename
from ( select filename, count(*) cnt
from dct@inmemorystorage
group
by filename
)
where cnt > 1
)
select filename
, binarydata
from dct@inmemorystorage
local export documents in binarydata to "${DCT_PATH}" filename column filename
create or replace table sor@inmemorystorage
as
select sor.*
, igp.code
itemgroupcode
, normalize(itm.code, 60, false)
itemcodedirectory
from exactonlinerest..shoporders sor
join exactonlinerest..itemsincremental itm
on itm.id = sor.item
join exactonlinerest..itemgroups igp
on igp.id = itm.itemgroup
select sor.shopordernumber
label 'Werkordernummer'
, sor.itemcode
, sor.itemcodedirectory
, sor.itemgroupcode
from sor@inmemorystorage
order
by sor.shopordernumber
local remark local export results as "${OUT_PATH}\shoporders.csv" format csv
select sor.shopordernumber label 'Werkordernummer'
, sor.itemcode
, sor.itemcodedirectory
, sor.itemgroupcode
, dct.filename
from sor@inmemorystorage
join dct@inmemorystorage dct
on dct.itemcode = sor.itemcode
local remark local export results as "${OUT_PATH}\shoporder-documents.csv" format csv