Export Subform to CSV

[israelwebdev]

Hi,

From the FormEdit screen, I'm trying to export a subform data to CSV.

I created some PHP code to query the subform records (not sure if that's necessary since the values are available in the FORM data).

What's the best way to achieve this?
Your CSV code puts the Temporary Table name into zzzsys_debug.deb_message->records, how can I do the same, if this is the way to go?

My PHP Code (name:ExportRegisters):

Code: Select all

$sql = "
	CREATE TABLE #TABLE_ID#
	SELECT FeeAmount as Amount,FeeCurrency as Currency, FeeType as `Type`,Memo,Rate, FeeDate as `Date`, Teller as `User` 
	FROM VaultFees WHERE vault_id = '#RECORD_ID#'
";

nuRunQuery($sql);

I was trying to call it from JS(below) using a method similar to one I have working for 'runprintbrowse', but 'runphp' returns a different object.
Am I close?

Code: Select all

    var P               = new nuCopyJSObject(nuFORM);
    P.call_type         = 'runphp';
    P.parent_record_id  = 'ExportRegisters';
    P.form_data         = nuGetData();

	var request = $.ajax({
		url      : "nuapi.php",
		type     : "POST",
		data     : {nuWindow : P},
                async    : false,
                success  : function(data) {
                    var obj          = $.parseJSON(data.DATA);
                    window.open('bitexport.php?i='+obj.id); //php page very similar to CSV code
                },
		dataType : "json"
		}).done(function(data){

            if(nuErrorMessage(data.ERRORS)){return;}
                        
	});

[admin]

israelwebdev,

Try using nuGetJSONSubform() http://wiki.nubuilder.net/nubuilderv3/i ... ormName.29

Steven

[israelwebdev]

Thanks for the tip.
I actually modified the code for my purposes. See below for anyone looking for a similar solution.

Custom Code:

Code: Select all

function bitExportRegister(){ 

  var str_method = "POST";    // by default uses POST
  var myForm = document.createElement("form");
  myForm.setAttribute("method", str_method);
  myForm.setAttribute("action", "bitexport.php");
  myForm.setAttribute("target", "_blank");
    var myInput = document.createElement("textarea");
    myInput.setAttribute("name", "j");
    myInput.setAttribute("style","display:none;");
    myInput.innerHTML =bitGetJSONSubform('VaultFees_subform');
    myForm.appendChild(myInput);
  document.body.appendChild(myForm);
  myForm.submit();
  return;
}

function bitGetJSONSubform(s){

    var q = $("[data-prefix='"+s+"0000']");
    var R = nuSubformArray(s, false);
    var S = Array();
    var C = Array();
    var n,d,i;

    for(i = 0 ; i < $(q).length ; i++){

        C.push(String($(q)[i].id).substr(s.length + Number(4)));
        
    }

    for(r = 0 ; r < R.length ; r++){
        
        var o = new bitObject(r);
        
        for(c = 0 ; c < C.length ; c++){//modified to remove images and column name prefix: Fee
          if(C[c].indexOf('Image')==-1)
            o[C[c].replace("Fee","")] = $('#' + R[r] + C[c]).val();
            
        }
        
        //o['nuDelete']     = $('#' + R[r] + '_nuDelete').prop('checked');
        //o['nuPrimaryKey'] = $('#' + R[r] + '_nuPrimaryKey').val();
        
        S.push(o);
    }
    
    return JSON.stringify(S);
    
}


function bitObject(i){
    //this.nuIndex = i;
}

excerpt from bitexport.php:

Code: Select all

<?php 

ob_start();
require_once('nucommon.php');
ob_end_clean();

header('Content-type: application/octet-stream');
header('Content-Disposition: attachment; filename=export.csv');
header('Cache-Control: no-cache');
	$jsonID = $_POST['j'];//echo $jsonID;
	$JSON           = json_decode($jsonID, true);
	$f = fopen('php://output', 'w');
	$firstLineKeys = false;
	foreach ($JSON as $line)
	{
		if (empty($firstLineKeys))
		{
			$firstLineKeys = array_keys($line);
			fputcsv($f, $firstLineKeys);
			$firstLineKeys = array_flip($firstLineKeys);
		}
		// Using array_merge is important to maintain the order of keys acording to the first element
		fputcsv($f, array_merge($firstLineKeys, $line));
	}

[admin]

.