Question about files uploaded for expensereport

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Question about files uploaded for expensereport

dolibarr-dev mailing list

Hello dev,

i was looking for a SQL request to get all files connected to a expense report ... something like that (for example if my expense report is id=2)

SELECT * FROM `llx_ecm_files` WHERE `src_object_id` = 2 and src_object_type='expensereport'

This request send me some informations but not all of them as i expected ... some of files are not in the result, so i switch to debug log level and here is the sql request that main dolibarr code make:

SELECT rowid, label, entity, filename, filepath,
      fullpath_orig, keywords, cover, gen_or_uploaded, extraparams,
      date_c, date_m, fk_user_c, fk_user_m, acl, position, share FROM
      llx_ecm_files WHERE filepath = 'expensereport/(PROV2)' AND entity
      = 1

damned, do i have to make the same ? or is there something else ?

After some investigations here is what i have now in my database (my PROV2 is now "validated" ans his name is "ER1907-0002"): database filepath stay refers to PROV2 ... i'm in trouble :-/

MariaDB [dolibarr]> SELECT filename, filepath FROM       llx_ecm_files WHERE filepath = 'expensereport/(PROV2)' AND entity       = 1

;+------------------------------------------------+-----------------------+
| filename                                       | filepath              |
+------------------------------------------------+-----------------------+
| (PROV2)-20190106-tickets_autoroute_janvier.jpg | expensereport/(PROV2) |
| (PROV2).pdf                                    | expensereport/(PROV2) |
+------------------------------------------------+-----------------------+


Éric

-- 
Éric Seigne             | CAP-REL*
[hidden email]  | +33 (0)6 987 444 01
https://cap-rel.fr      | *Réseau Expertise Linux

_______________________________________________
Dolibarr-dev mailing list
[hidden email]
https://lists.nongnu.org/mailman/listinfo/dolibarr-dev
Reply | Threaded
Open this post in threaded view
|

Re: Question about files uploaded for expensereport

Laurent Destailleur (aka Eldy)
The goal of field src_object_id is to be able, in a future, to get list of all files with a sql request instead of a directory scan. However, for the moment, the field is filled only when files is "generated", not when it is "uploaded"
So you can't use it yet.
Solution is like you do
SELECT filename, filepath FROM       llx_ecm_files WHERE filepath = 'expensereport/(PROV2)' AND entity       = 1
When you validate an object the directory (PROVxxx) is renamed. But for the moment the index in llx_ecm_files is not updated. In most cases, this is not a problem, because when the user go on the page documents, the index of files (llx_ecm_files) is automatically updated/fixed. But if you manipulate date from API, there is nobody going on page to update indexes. So your request return nothing.
So i pushed a fix with this commit: 9a7113f4f5295b610e853beb3549257aad0413e3 in v10 (will be available with v10.0.1). This fix update the index file table (table llx_ecm_files) when you validate an object and this object change his reference (from PROV... to final reference).







Le ven. 26 juil. 2019 à 09:51, Eric Seigne via Dolibarr-dev <[hidden email]> a écrit :

Hello dev,

i was looking for a SQL request to get all files connected to a expense report ... something like that (for example if my expense report is id=2)

SELECT * FROM `llx_ecm_files` WHERE `src_object_id` = 2 and src_object_type='expensereport'

This request send me some informations but not all of them as i expected ... some of files are not in the result, so i switch to debug log level and here is the sql request that main dolibarr code make:

SELECT rowid, label, entity, filename, filepath,
      fullpath_orig, keywords, cover, gen_or_uploaded, extraparams,
      date_c, date_m, fk_user_c, fk_user_m, acl, position, share FROM
      llx_ecm_files WHERE filepath = 'expensereport/(PROV2)' AND entity
      = 1

damned, do i have to make the same ? or is there something else ?

After some investigations here is what i have now in my database (my PROV2 is now "validated" ans his name is "ER1907-0002"): database filepath stay refers to PROV2 ... i'm in trouble :-/

MariaDB [dolibarr]> SELECT filename, filepath FROM       llx_ecm_files WHERE filepath = 'expensereport/(PROV2)' AND entity       = 1

;+------------------------------------------------+-----------------------+
| filename                                       | filepath              |
+------------------------------------------------+-----------------------+
| (PROV2)-20190106-tickets_autoroute_janvier.jpg | expensereport/(PROV2) |
| (PROV2).pdf                                    | expensereport/(PROV2) |
+------------------------------------------------+-----------------------+


Éric

-- 
Éric Seigne             | CAP-REL*
[hidden email]  | +33 (0)6 987 444 01
https://cap-rel.fr      | *Réseau Expertise Linux
_______________________________________________
Dolibarr-dev mailing list
[hidden email]
https://lists.nongnu.org/mailman/listinfo/dolibarr-dev


--
------------------------------------------------------------------------------------
Google+: https://plus.google.com/+LaurentDestailleur-Open-Source-Expert/
Facebook: https://www.facebook.com/Destailleur.Laurent
------------------------------------------------------------------------------------
* Dolibarr (Project leader): https://www.dolibarr.org (make a donation for Dolibarr project via Paypal: [hidden email])
* AWStats (Author) : http://awstats.sourceforge.net (make a donation for AWStats project via Paypal: [hidden email])
* AWBot (Author) : http://awbot.sourceforge.net
* CVSChangeLogBuilder (Author) : http://cvschangelogb.sourceforge.net



_______________________________________________
Dolibarr-dev mailing list
[hidden email]
https://lists.nongnu.org/mailman/listinfo/dolibarr-dev