Custom Reporting

We're having the same issue. Getting data out to evaluate surveys is a pain in the butt. The CSV file is near useless in this respect. Due to the retained IDs when moving/deleting fields in the form, things can get even more confusing. In this case, you can't do a simple join.

Inherit of issues with wordpress, the language-tied sub-storage (php serialization) in the database is messy and hard to translate without PHP to unserialize the knot. Lucky for us, a developer with a keen sense of perl was able to sift through the trash and output something we could use for analytics.

This, obviously, is by no means a *real* solution. We'd *really* appreciate a custom reporting addition.

#!/usr/bin/perl

use strict;
use warnings;

use DBI;

my ($num) = @ARGV;

my $dsn = "DBI:mysql:database=wordpress-site;host=localhost;port=3306";
my $pw = "password"
my $dbh = DBI->connect($dsn, "root", $pw);

my $mexpr = "SELECT display_meta, entries_grid_meta FROM x_rg_form_meta WHERE form_id=?";
my $mdata = $dbh->selectrow_hashref($mexpr, {}, $num);

# weird encoding used by wordpress
# usage: ($rest_of_string, $result) = parsew $string;
sub parsew($);
sub parsew($)
{
	my ($str) = @_;
	if($str =~ s/^s:(\d+):"//)
	{
		my $n = $1;
		$str =~ s/^(.{$n})"//s
			or die "Format error at s:$n:\"$str";
		return ($str, $1);
	}
	elsif($str =~ s/^N//)
	{
		return ($str, undef);
	}
	elsif($str =~ s/^[bi]:(-?\d+)//)
	{
		return ($str, $1);
	}
	elsif($str =~ s/^a:(\d+):\{//)
	{
		my $n = $1 * 2;
		my @o = ();
		if($n > 0)
		{
			for(0..$n-1)
			{
				($str, my $item) = parsew $str;
				push @o, $item;
				$str =~ s/^;?//;
			}
		}
		$str =~ s/^\}//
			or die "Format error in array (length $n, ends with $str)";
		return $str, {@o};
	}
	else
	{
		die "Invalid field type: $str";
	}
}
(undef, my $metadata) = parsew $mdata->{display_meta};

my %fields = ();
for(values %{$metadata->{fields}})
{
	$fields{$_->{id}} = $_->{label};
}
#use Data::Dumper; print Dumper $metadata;

my $expr = "SELECT COUNT(*) cnt, floor(x_rg_lead_detail.field_number*1) field_number, lead_id, IFNULL(x_rg_lead_detail_long.value, x_rg_lead_detail.value) value FROM x_rg_lead_detail LEFT JOIN x_rg_lead_detail_long ON x_rg_lead_detail.id = x_rg_lead_detail_long.lead_detail_id WHERE form_id=? GROUP BY field_number, value ORDER BY field_number ASC, cnt DESC, value ASC";
my $data = $dbh->selectall_arrayref($expr, { Slice => {} }, $num);

my $lastfield = undef;
for(@$data)
{
	#$_->{field_number} =~ /^(\d*)/; # extract the "main" field number
	if(!defined $lastfield || $_->{field_number} ne $lastfield)
	{
		$lastfield = $_->{field_number};
		print "\n$fields{$lastfield}\n";
	}
	my $val = $_->{value};
	$val =~ s/\s+/ /g;
	print "\t$_->{cnt}\t$val\n";
}