Hi,
I changed your code, basically the MySQL. This works for ProcessMaker 2.5, don't know about version 3.
With your SELECT you only get some files, not the ones with the original names from CONTENT table, the ones you get from:
Code: Select allSELECT * FROM wf_AT.CONTENT
where CON_CATEGORY = 'APP_DOC_FILENAME'
I just changed the SQL SELECT to match all the files, this suits my case but it might need some tweaking to suit all situations:
Code: Select allselect concat('/',folder1.FOLDER_NAME,'/',contents.CON_VALUE) as targetfile,
concat('/',documents.APP_UID,'/outdocs/',documents.APP_DOC_UID) as sourcefile
from APP_DOCUMENT as documents
left join APP_FOLDER as folder1 on folder1.FOLDER_UID = documents.FOLDER_UID
left join CONTENT as contents on contents.CON_ID = documents.APP_DOC_UID
where folder1.FOLDER_UID is not null and contents.CON_VALUE != ''
and folder1.FOLDER_NAME != 'whatever folder you want to remove from the query'
union
select concat('/',folder2.FOLDER_NAME,'/',folder1.FOLDER_NAME,'/',contents.CON_VALUE) as targetfile,
concat('/',documents.APP_UID,'/',documents.APP_DOC_UID) as sourcefile
from APP_DOCUMENT as documents
left join APP_FOLDER as folder1 on folder1.FOLDER_UID = documents.FOLDER_UID
left join APP_FOLDER as folder2 on folder2.FOLDER_UID = folder1.FOLDER_PARENT_UID
left join CONTENT as contents on contents.CON_ID = documents.APP_DOC_UID
where folder2.FOLDER_UID is not null and folder1.FOLDER_UID is not null and contents.CON_VALUE != ''
and folder2.FOLDER_NAME != 'whatever folder you want to remove from the query'
union
select concat('/',folder3.FOLDER_NAME,'/',folder2.FOLDER_NAME,'/',folder1.FOLDER_NAME,'/',contents.CON_VALUE) as targetfile,
concat('/',documents.APP_UID,'/',documents.APP_DOC_UID) as sourcefile
from APP_DOCUMENT as documents
left join APP_FOLDER as folder1 on folder1.FOLDER_UID = documents.FOLDER_UID
left join APP_FOLDER as folder2 on folder2.FOLDER_UID = folder1.FOLDER_PARENT_UID
left join APP_FOLDER as folder3 on folder3.FOLDER_UID = folder2.FOLDER_PARENT_UID
left join CONTENT as contents on contents.CON_ID = documents.APP_DOC_UID
where folder3.FOLDER_UID is not null and folder2.FOLDER_UID is not null and folder1.FOLDER_UID is not null and contents.CON_VALUE != ''
For this part:
concat('/',documents.APP_UID,'/outdocs/',documents.APP_DOC_UID) as sourcefile
the 'outdocs' is because the generated docs are saved under the folder 'outdocs'.
If you have several processes like me, and all of them save files you might do some tweaking to remove folders:
and folder1.FOLDER_NAME != 'whatever folder you want to remove from the query'
Please feel free to comment and improve the script.