formula results from the CPAN

formula

Gtk2-Ex-GroupBy

view release on metacpan or search on metacpan

my $groupby = Gtk2::Ex::GroupBy->new;
$groupby->set_model({
	'groupby' => [
		[
			'Product', 'Category'
		],
		[
			'State', 'Country'
		],
	],
	'formula'  => [
		[
			{ field => 'Revenue', formula => 'SUM of'},
			{ field => 'Expenses', formula => 'SUM of'},
			{ field => 'Margin', formula => 'AVG of'},
			{ field => 'Sales', formula => 'SUM of'},
		],
		[
			{ field => 'Costs', formula => 'SUM of'},
			{ field => 'Price', formula => 'AVG of'},
		],
	]
});
$groupby->signal_connect( 'changed' => 
	sub {
		#_make_sql($groupby->get_model);
	}
);
$groupby->signal_connect( 'closed' => 
	sub {

examples/group-by.pl view on Meta::CPAN

$window->signal_connect (destroy => sub { Gtk2->main_quit });
#$window->set_default_size(300, 400);
$window->add ($groupby->get_widget);
$window->show_all;
Gtk2->main;

sub _make_sql {
	my ($model, $table) = @_;
	print Dumper $model;
	my @group;
	my @formula;
	foreach my $x (@{$model->{'groupby'}->[0]}) {
		push @group, $x;
	}
	foreach my $x (@{$model->{'formula'}->[0]}) {
		my $f = $x->{'formula'};
		$f =~ s/ of$//;
		push @formula, $f.'('.$x->{'field'}.')';
	}
	my $groupstr = join ',', @group;
	my $formulastr = join ',', @formula;
	print Dumper \@group;
	print Dumper \@formula;
	my $query = "select $groupstr,$formulastr from $table group by $groupstr"
		if $groupstr;
	print Dumper $query;
}

lib/Gtk2/Ex/GroupBy.pm view on Meta::CPAN


sub set_model {
	my ($self, $data) = @_;
	# Add explicitly or else TiedRows are gonna bite you
	foreach my $x (@{$data->{'groupby'}->[0]}) {
		push @{$self->{groupby_list1}->{data}}, [$x];
	}
	foreach my $x (@{$data->{'groupby'}->[1]}) {
		push @{$self->{groupby_list2}->{data}}, [$x];
	}
	_populate($self->{formula_treeview1}, $self->{formula_model1}, $data->{'formula'}->[0]);
	_populate($self->{formula_treeview2}, $self->{formula_model2}, $data->{'formula'}->[1]);
	$self->_check_visibility;
}

sub get_model {
	my ($self) = @_;
	$self->_update_model;
	return $self->{model};
}

sub get_widget {

lib/Gtk2/Ex/GroupBy.pm view on Meta::CPAN

	my $groupby_list2 = Gtk2::Ex::Simple::List->new (
		'Choose Group By Fields from'    => 'text',
	);	

	my @column_types;
	$column_types[0] = 'Glib::String';
	$column_types[NAME__COLUMN] = 'Glib::String';
	$column_types[ORDER_COLUMN] = 'Glib::String';
	$column_types[MODEL_COLUMN] = 'Gtk2::ListStore';

	my $formula_model1 = Gtk2::ListStore->new (@column_types);   
	my $formula_treeview1= Gtk2::TreeView->new($formula_model1);
	$self->_add_combo($formula_treeview1, $formula_model1, 'Fields', 'Aggregation Formula');

	my $formula_model2 = Gtk2::ListStore->new (@column_types);   
	my $formula_treeview2= Gtk2::TreeView->new($formula_model2);
	$self->_add_combo($formula_treeview2, $formula_model2, 'Choose Fields from', 'Choose Formula from');

	$self->{groupby_list1} = $groupby_list1;
	$self->{groupby_list2} = $groupby_list2;
	$self->{formula_treeview1} = $formula_treeview1;
	$self->{formula_treeview2} = $formula_treeview2;	
	$self->{formula_model1} = $formula_model1;
	$self->{formula_model2} = $formula_model2;	
	
	($groupby_list1->get_column(0)->get_cell_renderers)[0]->set(xalign => 0.5);
	($groupby_list2->get_column(0)->get_cell_renderers)[0]->set(xalign => 0.5);

	$groupby_list1->get_column(0)->set_expand(TRUE);
	$groupby_list2->get_column(0)->set_expand(TRUE);

	$groupby_list1->get_column(0)->set_alignment(0.5);
	$groupby_list2->get_column(0)->set_alignment(0.5);

	($formula_treeview1->get_column(0)->get_cell_renderers)[0]->set(xalign => 0.5);
	($formula_treeview2->get_column(0)->get_cell_renderers)[0]->set(xalign => 0.5);
	($formula_treeview1->get_column(1)->get_cell_renderers)[0]->set(xalign => 0.5);
	($formula_treeview2->get_column(1)->get_cell_renderers)[0]->set(xalign => 0.5);

	$formula_treeview1->get_column(0)->set_expand(TRUE);
	$formula_treeview2->get_column(0)->set_expand(TRUE);
	$formula_treeview1->get_column(1)->set_expand(TRUE);
	$formula_treeview2->get_column(1)->set_expand(TRUE);

	$formula_treeview1->get_column(0)->set_alignment(0.5);
	$formula_treeview2->get_column(0)->set_alignment(0.5);
	$formula_treeview1->get_column(1)->set_alignment(0.5);
	$formula_treeview2->get_column(1)->set_alignment(0.5);

	# $formula_treeview2->get_column(0)->set_visible(FALSE);

	$groupby_list1->set_reorderable(TRUE);
	$formula_treeview1->set_reorderable(TRUE);
		
	$groupby_list1->get_selection->set_mode('multiple');
	$formula_treeview1->get_selection->set_mode('multiple');
	$groupby_list2->get_selection->set_mode('multiple');
	$formula_treeview2->get_selection->set_mode('multiple');

	_populate($formula_treeview1, $formula_model1, $self->{model}->{'formula'}->[0]);
	_populate($formula_treeview2, $formula_model2, $self->{model}->{'formula'}->[1]);
		
	my $buttonbox = $self->_pack_buttons;

	my $groupby_list1_none_label = Gtk2::Label->new;
	$groupby_list1_none_label->set_line_wrap(TRUE);

	$groupby_list1_none_label->set_markup('<span foreground="red">(Please add from the list Below)</span>');
	$self->{groupby_list1_none_label} = $groupby_list1_none_label;

	my $vbox11 = Gtk2::VBox->new(FALSE);
	$vbox11->pack_start ($groupby_list1, TRUE, TRUE, 0);
	$vbox11->pack_start ($groupby_list1_none_label, FALSE, FALSE, 0);
		
	my $groupby_list2_none_label = Gtk2::Label->new;
	$groupby_list2_none_label->set_markup('<span foreground="red">(Please add from the list Below)</span>');
	$self->{groupby_list2_none_label} = $groupby_list2_none_label;

	my $vbox12 = Gtk2::VBox->new(FALSE);
	$vbox12->pack_start ($formula_treeview1, TRUE, TRUE, 0);
	$vbox12->pack_start ($groupby_list2_none_label, FALSE, FALSE, 0);

	my $vbox21 = Gtk2::VBox->new(FALSE);
	$vbox21->pack_start ($groupby_list2, TRUE, TRUE, 0);
	
	my $vbox22 = Gtk2::VBox->new(FALSE);
	$vbox22->pack_start ($formula_treeview2, TRUE, TRUE, 0);	
	
	my $hbox1 = Gtk2::HBox->new(TRUE);
	$hbox1->pack_start ($vbox11, TRUE, TRUE, 0);	
	$hbox1->pack_start ($vbox12, TRUE, TRUE, 0);	

	my $hbox2 = Gtk2::HBox->new(TRUE);
	$hbox2->pack_start ($vbox21, TRUE, TRUE, 0);	
	$hbox2->pack_start ($vbox22, TRUE, TRUE, 0);	

	my $vbox = Gtk2::VBox->new(FALSE);

lib/Gtk2/Ex/GroupBy.pm view on Meta::CPAN

	
	$self->{groupby_list1}->get_selection->signal_connect ('changed' =>
		sub {
			return if $triggered;
			$triggered = TRUE;
			$removebuttonlabel->set_sensitive(TRUE);
			$addbuttonlabel->set_sensitive(FALSE);
			$removebutton->set_sensitive(TRUE);
			$addbutton->set_sensitive(FALSE);
			$self->{groupby_list2}->get_selection->unselect_all;
			$self->{formula_treeview1}->get_selection->unselect_all;
			$self->{formula_treeview2}->get_selection->unselect_all;
			$triggered = FALSE;
		}
	);

	$self->{groupby_list2}->get_selection->signal_connect ('changed' =>
		sub {
			return if $triggered;
			$triggered = TRUE;
			$removebuttonlabel->set_sensitive(FALSE);
			$addbuttonlabel->set_sensitive(TRUE);
			$removebutton->set_sensitive(FALSE);
			$addbutton->set_sensitive(TRUE);
			$self->{groupby_list1}->get_selection->unselect_all;
			$self->{formula_treeview1}->get_selection->unselect_all;
			$self->{formula_treeview2}->get_selection->unselect_all;
			$triggered = FALSE;
		}
	);

	$self->{formula_treeview1}->get_selection->signal_connect ('changed' =>
		sub {
			return if $triggered;
			$triggered = TRUE;
			$removebuttonlabel->set_sensitive(TRUE);
			$addbuttonlabel->set_sensitive(FALSE);
			$removebutton->set_sensitive(TRUE);
			$addbutton->set_sensitive(FALSE);
			$self->{groupby_list1}->get_selection->unselect_all;
			$self->{groupby_list2}->get_selection->unselect_all;
			$self->{formula_treeview2}->get_selection->unselect_all;
			$triggered = FALSE;
		}
	);

	$self->{formula_treeview2}->get_selection->signal_connect ('changed' =>
		sub {
			return if $triggered;
			$triggered = TRUE;
			$removebuttonlabel->set_sensitive(FALSE);			
			$removebutton->set_sensitive(FALSE);
			if ($#{@{$self->{groupby_list1}->{data}}} >= 0) {
				$addbuttonlabel->set_sensitive(TRUE);
				$addbutton->set_sensitive(TRUE);
			} else {
				$addbuttonlabel->set_sensitive(FALSE);
				$addbutton->set_sensitive(FALSE);			
			}
			$self->{groupby_list1}->get_selection->unselect_all;
			$self->{groupby_list2}->get_selection->unselect_all;
			$self->{formula_treeview1}->get_selection->unselect_all;
			$triggered = FALSE;
		}
	);	

	$addbutton->signal_connect ('button-release-event' => 
		sub {
			my $indices = _get_selected_indices($self->{formula_treeview2});
			if ($indices) {
				my @removeorder = reverse sort @$indices;
				my $data1 = _get_data($self->{formula_treeview1});
				my $data2 = _get_data($self->{formula_treeview2});
				foreach my $x (@removeorder) {
					push @$data1, splice (@$data2, $x, 1);
				}
				$self->{formula_treeview1}->set_no_show_all(FALSE);
				$self->{formula_treeview1}->show_all;
				_populate($self->{formula_treeview1}, $self->{formula_model1}, $data1);
				_populate($self->{formula_treeview2}, $self->{formula_model2}, $data2);
			} else {
				my @selected = $self->{groupby_list2}->get_selected_indices;
				my @removeorder = reverse sort @selected;
				foreach my $x (@removeorder) {
					push @{$self->{groupby_list1}->{data}}, 
						splice (@{$self->{groupby_list2}->{data}}, $x, 1);
				}
			}
			$self->_update_model;
			&{ $self->{signals}->{'changed'} } if $self->{signals}->{'changed'};

lib/Gtk2/Ex/GroupBy.pm view on Meta::CPAN

	);  

	$clearbutton->signal_connect ('clicked' => 
		sub {
			my $removeorder = [0..$#{@{$self->{groupby_list1}->{data}}}];
			foreach my $x (reverse sort @$removeorder) {
				push @{$self->{groupby_list2}->{data}}, 
					splice (@{$self->{groupby_list1}->{data}}, $x, 1);
			}
			if ($#{@{$self->{groupby_list1}->{data}}} < 0) {
				my $data1 = _get_data($self->{formula_treeview1});
				my $data2 = _get_data($self->{formula_treeview2});
				push @$data2, @$data1 if $data1;
				$data1 = undef;
				_populate($self->{formula_treeview1}, $self->{formula_model1}, $data1);
				_populate($self->{formula_treeview2}, $self->{formula_model2}, $data2);
			}
			$self->_update_model;
			&{ $self->{signals}->{'changed'} } if $self->{signals}->{'changed'};
			return FALSE;
		}
	);
	
	$removebutton->signal_connect ('clicked' => 
		sub {
			my $indices = _get_selected_indices($self->{formula_treeview1});
			if ($indices) {
				my @removeorder = reverse sort @$indices;
				my $data1 = _get_data($self->{formula_treeview1});
				my $data2 = _get_data($self->{formula_treeview2});
				foreach my $x (@removeorder) {
					push @$data2, splice (@$data1, $x, 1);
				}
				_populate($self->{formula_treeview1}, $self->{formula_model1}, $data1);
				_populate($self->{formula_treeview2}, $self->{formula_model2}, $data2);
			} else {
				my @selected = $self->{groupby_list1}->get_selected_indices;
				my @removeorder = reverse sort @selected;
				foreach my $x (@removeorder) {
					push @{$self->{groupby_list2}->{data}}, 
						splice (@{$self->{groupby_list1}->{data}}, $x, 1);
				}
				if ($#{@{$self->{groupby_list1}->{data}}} < 0) {
					my $data1 = _get_data($self->{formula_treeview1});
					my $data2 = _get_data($self->{formula_treeview2});
					push @$data2, @$data1 if $data1;
					$data1 = undef;
					_populate($self->{formula_treeview1}, $self->{formula_model1}, $data1);
					_populate($self->{formula_treeview2}, $self->{formula_model2}, $data2);
				}
			}
			$self->_update_model;
			&{ $self->{signals}->{'changed'} } if $self->{signals}->{'changed'};
			$removebuttonlabel->set_sensitive(FALSE);
			$addbuttonlabel->set_sensitive(FALSE);
			$removebutton->set_sensitive(FALSE);
			$addbutton->set_sensitive(FALSE);
			return FALSE;
		}

lib/Gtk2/Ex/GroupBy.pm view on Meta::CPAN

	my @data1;
	my @data2;
	foreach my $x (@{$self->{groupby_list1}->{data}}) {
		push @data1, $x->[0];
	}
	foreach my $x (@{$self->{groupby_list2}->{data}}) {
		push @data2, $x->[0];
	}
	$self->{model}->{'groupby'}->[0] = $#data1 >= 0 ? \@data1 : undef;
	$self->{model}->{'groupby'}->[1] = $#data2 >= 0 ? \@data2 : undef;
	$self->{model}->{'formula'}->[0] = _get_data($self->{formula_treeview1});
	$self->{model}->{'formula'}->[1] = _get_data($self->{formula_treeview2});
	$self->_check_visibility;
}

sub _get_selected_indices {
	my ($treeview) = @_;
	my @indices;
	my (@paths) = $treeview->get_selection->get_selected_rows;
	foreach my $path (@paths) {
		push @indices, $path->to_string;
	}

lib/Gtk2/Ex/GroupBy.pm view on Meta::CPAN

	return \@indices;
}

sub _get_data {
	my ($treeview) = @_;
	my $model = $treeview->get_model;
	my @data;
	for my $i(0..$model->iter_n_children-1) {
		my $col0 = $model->get($model->get_iter_from_string($i), 0);
		my $col1 = $model->get($model->get_iter_from_string($i), 2);
		push @data, { formula => $col0, field => $col1 };
	}
	return undef if $#data < 0;
	return \@data;
}

sub _add_combo {
	my ($self, $treeview, $model, $groupby_header, $formula_header) = @_;
	
	my $combo_renderer = Gtk2::CellRendererCombo->new;
	$combo_renderer->set (
		text_column => 0, # col in combo model with text to display
		editable => TRUE, # without this, it's just a text renderer
	); 
	$combo_renderer->signal_connect (edited => 
		sub {
			my ($cell, $text_path, $new_text) = @_;
			$model->set (
				$model->get_iter_from_string($text_path),
				ORDER_COLUMN, 
				$new_text
			);
			&{ $self->{signals}->{'changed'} } if $self->{signals}->{'changed'};
		}
	);
	$treeview->insert_column_with_attributes(
		-1, $formula_header, 
		$combo_renderer,
		text  => ORDER_COLUMN, 
		model => MODEL_COLUMN
	);
	$treeview->insert_column_with_attributes(
		-1, $groupby_header, 
		Gtk2::CellRendererText->new, 
		text => NAME__COLUMN
	);
}

sub _check_visibility {
	my ($self) = @_;
	if ($#{@{$self->{groupby_list1}->{data}}} >= 0) {
		$self->{groupby_list1_none_label}->hide;
		$self->{groupby_list1_none_label}->set_no_show_all(TRUE);
	} else {
		$self->{groupby_list1_none_label}->set_no_show_all(FALSE);
		$self->{groupby_list1_none_label}->show;	
	}
	if ($self->{formula_treeview1}->get_model->iter_n_children > 0) {
		$self->{groupby_list2_none_label}->hide;
		$self->{groupby_list2_none_label}->set_no_show_all(TRUE);
	} else {
		$self->{groupby_list2_none_label}->set_no_show_all(FALSE);
		$self->{groupby_list2_none_label}->show;	
	}
}

sub _populate{
	my ($treeview, $model, $temp) = @_;

lib/Gtk2/Ex/GroupBy.pm view on Meta::CPAN

	my $combomodel = Gtk2::ListStore->new('Glib::String');
	$combomodel->set($combomodel->append, 0, 'COUNT of');
	$combomodel->set($combomodel->append, 0, 'MAX of');
	$combomodel->set($combomodel->append, 0, 'MIN of');
	$combomodel->set($combomodel->append, 0, 'SUM of');
	$combomodel->set($combomodel->append, 0, 'AVG of');
	$combomodel->set($combomodel->append, 0, 'STDDEV of');
	$model->clear();
	my $i = 0;
	foreach my $data (@$temp) {
		$data->{formula} = $lookup_hash->{$data->{formula}};
		$data->{formula} = $combomodel->get(
			$combomodel->iter_nth_child (undef, $data->{formula})
			, 0
		);
		$model->set (
			$model->append,
			NAME__COLUMN, $data->{field},
			ORDER_COLUMN, $data->{formula},
			MODEL_COLUMN, $combomodel 
		);
	}
}

1;

__END__

=head1 NAME

( run in 0.550 second using v1.01-cache-2.11-cpan-3cd7ad12f66 )