Reporting Tools & Export Issue

[linx79]

Hi,

We have created a form to capture some data and need to generate a report to list out the entered information. I am able to use the page to create a report like page but when I print out the report table is not well printed out.

Is there a way or any tools to allow me to create a proper Report for the user? Example, using crystal report to link to ZODB and extract the Plomino database that I have created?

Also, I have a export button with this script context.absolute_url()+"/exportXLS"

The problem is I export the entire content out in excel. Is there a way to export a filtered content based on a certain criteria?

Kindly help.

Thank you.

[ebrehault]

The easiest way is to export your data as CSV (and then process it in Crystal report or any other tool). You will find some information and code samples about CSV extraction here: http://www.plomino.net/how-to/export-documents-data-as-csv-or-xls

[linx79]

Hi ebrehault

Thank you for the reply. I am trying out the export script in the link you provide however I am getting this error.. Any idea what went wrong?

line 15, in action_ExportNCR_ncrExport_script UnicodeEncodeError: 'ascii' codec can't encode character u'\u2013' in position 11: ordinal not in range(128)

mY CODE is as below

db = context.getParentDatabase() all = db.getIndex().dbsearch({"Form": "NCRRegisForm"}) result = [] for doc in all: project = doc.ncrProject if not project == 'PPCSB/116': result.append([doc.ncrNo,doc.ncrProject,doc.ncrRDate,doc.ncrDesc]) context.REQUEST.RESPONSE.setHeader('content-type', 'text/csv; charset=utf-8') context.REQUEST.RESPONSE.setHeader("Content-Disposition", "attachment; filename=my_special_export.csv") context.REQUEST.RESPONSE.setBody(array_to_csv(result))

[ebrehault]

Your item values are Unicode, you need to encode the resulting string in UTF-8: context.REQUEST.RESPONSE.setBody(array_to_csv(result.encode('utf-8'))

[linx79]

How to convert? Kindly advice. . Sorry i am abit new to this. On 17 Sep 2014 17:58, "Eric BREHAULT" notifications@github.com wrote:

Your item values are Unicode, you need to encode the resulting string in UTF-8: context.REQUEST.RESPONSE.setBody(array_to_csv(result.encode('utf-8'))

— Reply to this email directly or view it on GitHub https://github.com/plomino/Plomino/issues/579#issuecomment-55872702.

[ebrehault]

As I have shown in my previous message: result.encode('utf-8')

[linx79]

Hi Eric,

I have replace the last line with this context.REQUEST.RESPONSE.setBody(array_to_csv(result.encode('utf-8')))

But I got this error message..

AttributeError: 'list' object has no attribute 'encode'

So I have changed the code to the following

db = context.getParentDatabase() all = db.getIndex().dbsearch({"Form": "NCRRegisForm"}) result = [] for doc in all: project = doc.ncrProject if not project == 'PPCSB/116': ncrNo = doc.ncrNo ncrProject =doc.ncrProject ncrRDate = doc.ncrRDate ncrDesc = doc.ncrDesc result.append([ncrNo.encode('utf-8'),ncrProject.encode('utf-8'),ncrRDate,ncrDesc.encode('utf-8')]) context.REQUEST.RESPONSE.setHeader('content-type', 'text/csv; charset=utf-8') context.REQUEST.RESPONSE.setHeader("Content-Disposition", "attachment; filename=my_special_export.csv") context.REQUEST.RESPONSE.setBody(array_to_csv(result))

No error appear and no file is exported.. did I miss something??

[ebrehault]

result is a list, that's not what you wnat to encode. You need to encode the output of array_to_csv so the right code is: context.REQUEST.RESPONSE.setBody(array_to_csv(result).encode('utf-8'))

[linx79]

Hi Eric,

Yes I know... I couldn't quite put that in because the date wouldnt be encoded.. that is why I encode the value one by one in the list. My problem now is there is no error message generated and no file is exported...

[ebrehault]

Just add some log traces so you can check in the server log what is going on. Example: Log(result)

[linx79]

Hi Eric,

I know the problem already. I insert the code directly into the button instead of going through the Agent. I now put the code into the Agent and the button I insert the code below context.getParentDatabase().absolute_url()+"/export_csr/runAgent"

I have one question, can I put in drop down menu for user to select data to export? How can I pass the drop down data to the Agent script? Thanks~

[ebrehault]

You can add parameters in the URL: export_csr/runAgent?param1=value1 and they can be read from the agent code like that: context.REQUEST.get('param1')

But you cannot make the action pass the value selected in a drop down (because the action url is generated server-side so the user selection cannot be known at this time). So you need to create a computed for display richtext field to implement it is javascript:

html="""
<button
    onclick="location.href='%s?param1='+$('#the_dropdown').val()">
Export</button>
""" % context.getParentDatabase().absolute_url()+"/export_csr/runAgent"
"""
return html

[linx79]

Dear Eric,

I have try with the code above but the export button is not showing.. is there a syntax error? May I know what is the triple quote is for? Thank you~

[ebrehault]

Triple quote allows to escape everything in Python:

>>> a=""" I can write "quotes" here
... and also newlines
... """
>>> a=" I can write "quotes" here
  File "<stdin>", line 1
    a=" I can write "quotes" here
                          ^
SyntaxError: invalid syntax

If your button does not show, inspect the HTML and see what you get.

[linx79]

HI Eric,

I have change it to this code.. which I think would fetch the same result.

projectValue = plominoDocument.ncrProject url = context.getParentDatabase().absolute_url()+"/export_csr/runAgent" ConfirmAction="if(confirm('Confirm_to_Export?'))" html="< input type=button value=Export onclick="+ConfirmAction+"{location.href='"+url+"?param1="+projectValue+"'}>" return html

The button wouldnt show if I add the projectValue at the end of the hyperlink as above. If I take it out, the button shows. Which means I cant seems to retrieve the value of the drop down menu..

FYI, this is a search page. Kindly help~~~~

[ebrehault]

If projectValue is actually a string, your formula should work, so maybe it isn't. Try with: projectValue="I am a string" and see if the button shows. If it does, then try to debug your value like this: Log(plominoDocument.ncrProject) to see what is actually returned. (your log will appear in the server log)

[linx79]

Hi Eric,

I know this sound a little dumb but may I know where can I see the server log? Thanks~

[ebrehault]

If you have launch your server in foreground, like this: bin/instance fg the log are written directly to the standard output. If it is launched as a background task, then you just need to read the file var/log/instance.log.

[linx79]

Hi Eric,

I think because its a search page, no data is saved thus documentPlomino.ncrProject will not return any value.

Thus I have change it to context.REQUEST.get("ncrProject")

And it works!!! Now I my challenge is to get it to import into the Crystal Report Array. Is there a way to export this csv file to a location?

Thank you for the help~

[ebrehault]

Ok you got right, if you are in a search page, indeed there is no actual document, so no items are saved, and all the submitted data is in REQUEST. About "exporting a csv file to a location", what do you mean exactly ? The most simple approach is generally to pull the data: you create an external script (in Python, Bash, or anything) that will call your Plomino url and save the result in a file.

[linx79]

What i need is to generate a crystal report with the csv file created. But it would not be convenient for the user to manually save it in the directory where the crystal report can access. Thus can the agent save it to the network directory? TQ

[ebrehault]

No, a PlominoAgent (like any other Plomino formula) is a PythonScript, so its code run in restricted mode, meaning it is restricted to known safe operations, and cannot execute any other operation (considering a PythonScript is a Python code that can be written through a web interface, the contrary would be major security flaw). Writing on a disk (or network drive) is not authorized. That is why you need to pull the data (and not push), using an external script like I said, or maybe by scripting CrystalReport.

[linx79]

Oic! I still need the export feature anyway, so its fine.

For the pull off data is there any reference for me to study so at least i know how is done to connect to the plomino database and fields?

[ebrehault]

On the Plomino side, there is nothing specific to do, you just need to call from the outside an url like: http://server/plone/db/theAgent/runAgent?param1=value1&param2=value2 possibly with an authentication. So it all depends on how you implement the external process.

To give you an example, a wget command would be:

wget --auth-no-challenge --http-user 'the_user' --http-password 'the_password' http://server/plone/db/theAgent/runAgent?param1=value1&param2=value2 -O /dev/null --read-timeout 1 --retry-connrefused 1 -t 1

[linx79]

Thanks Eric. I will try it out.

[linx79]

I am curious... is there a reporting tools for plomino (commercial)? Because the web report format is not printer friendly.

Thanks a million~

[ebrehault]

No there's no such tool. I usually build reports using D3.js and if I need a print rendering, I use CasperJS to run the web page on server-side and produce an screenshot.

[linx79]

Oh this is something new to me. What about printing of listing? If it's alot i can't produce a screenshot to print as it would be really small if data is alot.