module template generate a data csv file

@blindmime
10 years ago
772 posts

I have a template built which is generating the data for a CSV data file, but how would I make a link the user can click to download a file they can open in excel? There appears to be more involved than making a template and linking to it.

Any thoughts?
updated by @blindmime: 07/06/15 09:48:37AM

@michael
10 years ago
7,799 posts

If you made it as a .zip file that would download. If you uploaded it with jrFile that would allow downloads of it.

The problem might occur when its a file that the webserver/browser knows what to do with. If it was a .txt file it might open in the browser instead of downloading.

The way to force it to download is to set the headers in php then deliver the file. If your not looking to build a module, then check out jrFile to see if that can do what your after.

@brian
10 years ago
10,149 posts

yeah this will require a module view function - it's not something that can be done in a template - i.e.

function myModule_export_csv($_post, $_user, $_conf)
{
    // create your file...
    $file = myModule_function_to_create_csv_file();
 
    // use jrCore_send_download_file - it will automatically set all the right headers
    jrCore_send_download_file($file);
}

Let me know if that helps.

--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
updated by @brian: 05/07/15 02:36:32PM

@blindmime
10 years ago
772 posts

A little bit. This goes in profile.php? I'm not well versed in programming modules, to be honest. I know I probably need to go through the docs. Hoping it's possible to get to the heart of it though. I just need a link to open a csv file in excel with values from a template, if that makes sense. The light isn't on just yet -- can I get another clue or two?

@michael
10 years ago
7,799 posts

The thing with creating a module is you can't yet say "computer, make me a list of the info I need" like they do in StarTrek yet.

We're still at the stage of having to step by step walk the computer through each step of the situation in YES/NO question fashion.

So building a module is just like that; You have to go in super incremental steps that the computer can follow.

So while it may seam straight forward to want:

Quote: I have a template built which is generating the data for a CSV data file, but how would I make a link the user can click to download a file they can open in excel?

There is actually quite a few steps that need to be accurately outlined for the server to return the right information.

From your request I think this is your setup:
* data exists in a datastore that you (or your users) have put there via forms on your site.
* you want to retrieve that data from the database. (some of it, all of it, a subset of it).
* you want to format that data into a structure. (need to decide what that structure is going to look like)*
* once the data is structured you want to save it in a format that excel will recognize. (need to know if that is just a normal .csv file or something special)
* you need to create a mechanism to start of this retrieval process.

If you were planning on building a module to do it it would involve walking the server through the steps to get back what you want and format it the way your after.

If its just going to be a one-off thing, it might be easier to go into your database and choose to export the table your after as a .csv file (depending on if the structure is compatible with what your wanting to do.)

ACP -> TOOLS -> DEVELOPER TOOLS -> DATABASE ADMIN -> export -> CSV (check the table your after.)

Its not difficult to build the module your after but it does involve plodding along one step at a time.

Come on StarTrek, hurry up and get here.

@blindmime
10 years ago
772 posts

Thanks Michael.

The file needs to be dynamic, based on a jrCore_list call (or whatever is done via a module). It needs to be a comma separated value text file (a csv file). I could create such a thing in Jamroom 4 from a template (this is where I'm coming from, reference-wise).

I have the template working in jr5. The template is:

"Property","Month","Category","Task"<br>

{jrCore_list module="agTask" profile_id=$_profile_id order_by="_created asc" limit="10000" search1="task_complete = on" search2="task_invoiced = March" search3="task_entered = on" template="csv_list.tpl"}

And csv_list.tpl is

{jrCore_module_url module="agTask" assign="murl"}
{if isset($_items)}
{foreach from=$_items item="item"}
"{$item.task_week|date_format}","{$item.task_testcategory_0}{if strlen($item.task_testcategory_1) > 0} | {$item.task_testcategory_1}{/if}{if strlen($item.task_title) > 0} | {$item.task_title}{/if}","{$item.task_invoiced}","{$item.task_amount_invoiced}"<br>
{/foreach}
{/if}

If the page didn't also include a header and everything else, it would done.

I just need to know how to get that same thing in whatever jr5 does now.

@michael
10 years ago
7,799 posts

Take index.tpl from your skin, it will look like this

{jrCore_include template="header.tpl"}

// stuff

{jrCore_include template="footer.tpl"}

Its the header and footer includes that cause the header and footer to be included.

Save it as:
report.tpl
/skins/YOUR SKIN/report.tpl

"Property","Month","Category","Task"<br>

{jrCore_list module="agTask" profile_id=$_profile_id order_by="_created asc" limit="10000" search1="task_complete = on" search2="task_invoiced = March" search3="task_entered = on" template="csv_list.tpl"}

That will appear at:
your-site.com/report

@blindmime
10 years ago
772 posts

This is an Aparna-generated module.

report.tpl is, in fact, saved exactly as you suggested, yet it outputs a header and footer.

Could it be that templates use index.tpl as a framework?

This is index.tpl:

{jrCore_include template="header.tpl"}

<div class="block">

    <div class="title">
        <h1>{jrCore_lang module="agTask" id="10" default="Task"}</h1>
    </div>

    <div class="block_content">

        {jrCore_list module="agTask" order_by="_item_id numerical_desc" pagebreak="10" page=$_post.p pager=true}

    </div>

</div>

{jrCore_include template="footer.tpl"}

@michael
10 years ago
7,799 posts

bad job of explaining, sorry.

Try again: The index template has this line:

{jrCore_include template="header.tpl"}

That line includes the header. If you don't want the header, don't include that line. Same for the footer.

@blindmime
10 years ago
772 posts

But I do want it on the index page and other pages.

I just don't want it on the report page.

And I'm actually not sure if this is getting me to where I want to be. I want a link to "report" that downloads the csv file rather than opening it in the browser. Can I run the function Brian referenced earlier from a call in a template (so the correct headers are created?

Hope that makes sense.
updated by @blindmime: 06/03/15 08:49:04AM

@michael
10 years ago
7,799 posts

Crossed wires in this conversation.

If you want it to popup as a downloadable file, you'll have to do it via a module's view function so you can set the type of file to be downloadable instead of displayed.

--edit--
take a look at the jrDeveloper module, The way it does it is to first create the file and save it to disk then the download function looks like this:

//------------------------------
// download_skin
//------------------------------
function view_jrDeveloper_download_skin($_post, $_user, $_conf)
{
    jrUser_master_only();
    $mdir = jrCore_get_media_directory(0, FORCE_LOCAL);
    if (!isset($_post['_1']) || !is_file("{$mdir}/{$_post['_1']}")) {
        jrCore_set_form_notice('error', 'Invalid ZIP file');
        jrCore_location('referrer');
    }
    jrCore_send_download_file("{$mdir}/{$_post['_1']}");
    session_write_close();
    exit();
}

That is when a skin is packaged and needs to be downloaded. The steps are:
* get the file,
* send the file
* exit

But first you need to create the file, and write it to disk.

The view_jrDeveloper_package_skin_save() function has an example of that happening.
* create structure
* write to file.
updated by @michael: 06/04/15 01:44:34AM

@blindmime
10 years ago
772 posts

I can see creating a file a user can download when packaging a skin. That's a static event. But in my case, it's dynamic data that changes often. Envision house properties as the profiles and tasks performed on these properties as this module. Each download file is comprised of the tasks performed in a particular month, generated by a jrCore_list call. I guess I could save the file every time the module form is updated, but it's got to save a different file for each month which seems probably not a good way to do this.

If I could simply not generate a header and footer, I think I might get what I want by right-clicking and saving.

@blindmime
10 years ago
772 posts

I thought about enclosing the text in a textarea and then wondered if there was a way to download that and googled the method found here: http://runnable.com/U5HC9xtufQpsu5aj/use-javascript-to-save-textarea-as-a-txt-file

Seems to be working well.

@michael
10 years ago
7,799 posts

Hard to go into more detail than is already here without actually building the module.

You can pass in any variables you need to dynamically create the file, you could use a template to generate it that was part of the skin, or part of the module. There's a lot of ways.

Just got to go one step at a time.

That link wouldn't be my first choice method to do it, but if you like that way, that's fine too. Whatever works.

@michael
10 years ago
7,799 posts

Here is the module (attached).

To use it install it, the go to:
your-site.com/csv/create/mod=jrAudio/keys=audio_title,audio_genre

That will get you a CSV file with the audio moudules audio_title and audio_genre fields displaying on the screen.

Click the DOWNLOAD button on that page to download whats on the screen. Feel free to develop it further and distribute it.

updated by @michael: 06/05/15 12:19:56AM

@blindmime
10 years ago
772 posts

Wow -- thanks, Michael!

what would be url for audio files created in May, displaying the same keys?

@michael
10 years ago
7,799 posts

- good, I've tricked you into learning more development HAHA!

The index.php file controls url pages, so if it has a url, then the function is in the index.php file.
The include.php file is for functions that don't have a url.

In the index.php file for the url your-site.com/csv/create is the function view_xxCsv_create(). Thats the function that controls that url your looking at.

Your question is how do I change the search query in that function:

    $_sp  = array(
        'search'         => array(),
        'return_keys'    => $_keys,
        'skip_triggers'  => true,
        'privacy_check'  => false,
        'ignore_pending' => true,
        'limit'          => 500
    );
    $_rt  = jrCore_db_search_items($mod, $_sp);

To search for something?

and the answer is "You add it to the 'search' array request".

Can you see that in the code above?

'search'         => array(),

That's expecting the same structure as the jrCore_list template functions expect:

"{jrCore_list}"
http://www.jamroom.net/the-jamroom-network/documentation/development/89/jrcore-list

So "created in may" is what you want to turn into a number that your server can understand.

In the datastore the all items have a '_created' element as a timestamp so you need to figure out when the first of may is as a timestamp, and the end of may is as a timestamp and use that in your query.

Using this online converter to create an example we can know
http://www.unixtimestamp.com/index.php

1st MAY 2015 is 1430438400
1st JUNE 2015 is 1433116800

So thats the time period to use.

'search'         => array('_created > 1430438400', '_created < 1433116800'),

Is what you want.

You're probably going to want to take that as an input from the URL so you're going to need to set up a mechanism to transform what comes from the url into a timestamp. (or recorde a different date format for your things)

your-site.com/csv/create/mod=jrAudio/keys=audio_title,audio_genre/search1=_created-gt-1430438400/search2=_created-lt-1433116800

or something along those lines.

module template generate a data csv file

Tags