Attachments list

Build and share your cool customized reports built w/ one sql, Chart.js and Lua

Moderator: Renato

User avatar
GabrieleV
MMEX Developer
Posts: 158
Joined: Wed Apr 09, 2014 7:45 pm
Are you a spam bot?: No
Location: Italy

Attachments list

Post by GabrieleV »

With this report you can see all attachments inserted in MMEX.
Currently links to open directly attachment on filename works only if you use a custom directory as attachments archive.
Attachment list.grm
Attachment list report
(1.59 KiB) Downloaded 498 times
SQL

Code: Select all

SELECT A.reftype AS Type, A.refid AS Nr,
C.transdate||' | '||AC.AccountName||' | '|| CURR.pfx_symbol||round(C.Transamount, 2) ||CURR.sfx_symbol AS Reference,
A.description AS Description, A.filename AS File,
(SELECT infovalue FROM infotable_v1 WHERE infoname = 'ATTACHMENTSFOLDER:Win' COLLATE NOCASE) AS BasePath,
"\"||A.reftype||"\"||A.filename AS FilePath, '' AS BasepathLUA
FROM attachment_v1 A
    INNER JOIN checkingaccount_v1 C ON C.transid = A.refid
    INNER JOIN accountlist_v1 AC ON C.accountid = AC.accountid
    INNER JOIN currencyformats_v1 CURR ON AC.currencyid = CURR.currencyid
ORDER BY A.reftype,A.refid,A.filename;
LUA

Code: Select all

function get_base_path (BasePath,FilePath)
    if BasePath == "DOCSDIR" then
    	BasePath = "#";
    elseif BasePath == "MMEXDIR" then
    	BasePath = "#";
    elseif BasePath == "DBDIR" then
    	BasePath = "#";
    else
    	BasePath = "file:///" .. BasePath .. FilePath;
	end
    return BasePath;
end

function handle_record(record)
    record:set("BasepathLUA", get_base_path(record:get("BasePath"),record:get("FilePath")));
end
TEMPLATE

Code: Select all

<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8" />
    <meta http - equiv = "Content-Type" content = "text/html" />
    <title><TMPL_VAR REPORTNAME></title>
    <script src = "Chart.js"></script>
    <link href = "master.css" rel = "stylesheet" />
</head>
<body>
<div class = "container">
<h3><TMPL_VAR REPORTNAME></h3>
<TMPL_VAR TODAY><hr>
<div class = "row">
<div class = "col-xs-2"></div>
<div class = "col-xs-8">
<table class = "table">
<thead>
    <tr>
        <th>Type</th>
        <th>Nr</th>
        <th>Reference</th>
        <th>Description</th>
        <th>File</th>
    </tr>
</thead>
<tbody>
    <TMPL_LOOP NAME=CONTENTS>
        <tr>
        <td><TMPL_VAR "Type"></td>
        <td class = "text-right"><TMPL_VAR "Nr"></td>
        <td><TMPL_VAR "Reference"></td>
        <td><TMPL_VAR "Description"></td>
	<td><a href="<TMPL_VAR "BasePathLUA">"><TMPL_VAR "File"></a></td>
        </tr>
    </TMPL_LOOP>
</tbody>
</table>
</div>
<TMPL_LOOP ERRORS>
    <hr>
    <TMPL_VAR ERROR>
</TMPL_LOOP>
</div>
</div>
</body>
<script>
<!--Format numbers-->
    function currency(n) { n = parseFloat(n); return isNaN(n) ? 0 : n.toFixed(2); }
    var elements = document.getElementsByClassName("money, text-right");
    for (var i = 0; i < elements.length; i++)
        { elements[i].innerHTML = "<TMPL_VAR PFX_SYMBOL>" + currency(elements[i].innerHTML) + "<TMPL_VAR SFX_SYMBOL>"; }
</script>
</html>
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Attachments list

Post by Nikolay »

Code: Select all

    <script src = "Chart.js"></script>
not needed
User avatar
GabrieleV
MMEX Developer
Posts: 158
Joined: Wed Apr 09, 2014 7:45 pm
Are you a spam bot?: No
Location: Italy

Re: Attachments list

Post by GabrieleV »

Yes, it was auto-added by template..
guanlisheng
MMEX Developer
Posts: 375
Joined: Wed Dec 21, 2011 5:58 am
Are you a spam bot?: No
Location: China
Contact:

Re: Attachments list

Post by guanlisheng »

Cool and could we add it to github as well.
Lisheng Guan,
Developer of MoneyManagerEX (http://moneymanagerex.org)
elwood1229
New MMEX User
Posts: 11
Joined: Sun Mar 23, 2014 2:59 am
Are you a spam bot?: No

Re: Attachments list

Post by elwood1229 »

This report is helpful and working well except for one thing. While it lists all of the transactions that have attachments, when I click on the hyperlink that I assume is designed to open the attachment, it comes up with a blank screen with only a small box with an "x" in it in the upper left hand corner. I do have the MMEX database in a folder called "MoneyManagerEx Mobile" which is located in my DropBox account. That may be causing the problem but I wasn't sure whether/how to change the path since it seems to be generated automatically in the Lua and I didn't want to mess anything up. Any directions would be appreciated.

I was also wondering if it would be possible somehow to make it possible to click on the transaction number (or something) and have it open the transaction associated with the attachment?

Thanks for your work.

Elwood
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Attachments list

Post by Nikolay »

Please check Options - Attachmens tab settins.
elwood1229
New MMEX User
Posts: 11
Joined: Sun Mar 23, 2014 2:59 am
Are you a spam bot?: No

Re: Attachments list

Post by elwood1229 »

Thank you for the response. I did check the settings under Options > Import/Export > Attachment Settings. I did in fact have the wrong folder specified but even after correcting that, it is still not working. When I selected the "systems subdirectory" option, nothing happens when I click on a link on the Attachment report. When I select "Money Manager Directory" option and click on a link, nothing happens. When I select "Database file directory" option, nothing happens--well, technically it scrolls back to the top of report. When I select "User Defined" option, and dig down to the "attachments\Transactions" folder (where I can see the attached pdfs in file explorer), and click on a link I get a page that shows "This page can't be displayed." I am not sure where I am going wrong on this.

Elwood
User avatar
GabrieleV
MMEX Developer
Posts: 158
Joined: Wed Apr 09, 2014 7:45 pm
Are you a spam bot?: No
Location: Italy

Re: Attachments list

Post by GabrieleV »

As I've written, report currently works only if you choose "User Defined".

In "User Defined" you have to set the path to top-level folder, so in your example:
dig down to the "attachments\Transactions" folder
you have to choose "attachments" folder, not "Transaction" folder that is auto-created by MMEX.

Please take attenction modifying attachment folder if you have already inserted attachments because,
if not well managed, it can breaks expected behaviour and file structure.
elwood1229
New MMEX User
Posts: 11
Joined: Sun Mar 23, 2014 2:59 am
Are you a spam bot?: No

Re: Attachments list

Post by elwood1229 »

Thanks again. I actually had the path set to the "Attachment's folder originally. I reset the path to that folder and am back to getting a blank screen with a small black box with an "x" in it at the upper left corner. I don't know if it will help, but I am attaching a screen shot with the option dialog open showing the path as it is set; behind the option dialog is the report screen after clicking on a link in the attachment report (path is set as shown in the options dialog). Sorry to be difficult, but I don't see what is wrong. BTW, when I click on the paper clip icon on a transaction with a file attached, the attachment pdf opens correctly with this setting.

Elwood
Attachments
attachmentoptions.jpg
(94.41 KiB) Downloaded 1023 times
User avatar
GabrieleV
MMEX Developer
Posts: 158
Joined: Wed Apr 09, 2014 7:45 pm
Are you a spam bot?: No
Location: Italy

Re: Attachments list

Post by GabrieleV »

Ok, attachments settings are correct.

If you right-click somewhere in the report and choose "view source" what did you see as href?
Post Reply