3 use List::Util qw(first);
7 use SL::MoreCommon qw(listify);
10 $main::lxdebug->enter_sub();
15 my $myconfig = \%main::myconfig;
16 my $form = $main::form;
18 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
21 if ($params{module}) {
22 $where = 'WHERE module = ?';
23 push @values, $params{module};
26 my $query = qq|SELECT * FROM custom_variable_configs $where ORDER BY sortkey|;
28 my $configs = selectall_hashref_query($form, $dbh, $query, @values);
30 foreach my $config (@{ $configs }) {
31 if ($config->{type} eq 'select') {
32 $config->{OPTIONS} = [ map { { 'value' => $_ } } split(m/\#\#/, $config->{options}) ];
34 } elsif ($config->{type} eq 'number') {
35 $config->{precision} = $1 if ($config->{options} =~ m/precision=(\d+)/i);
39 $self->_unpack_flags($config);
42 $main::lxdebug->leave_sub();
48 $main::lxdebug->enter_sub();
53 Common::check_params(\%params, qw(id));
55 my $myconfig = \%main::myconfig;
56 my $form = $main::form;
58 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
60 my $query = qq|SELECT * FROM custom_variable_configs WHERE id = ?|;
62 my $config = selectfirst_hashref_query($form, $dbh, $query, conv_i($params{id})) || { };
64 $self->_unpack_flags($config);
66 $main::lxdebug->leave_sub();
72 $main::lxdebug->enter_sub();
77 foreach my $flag (split m/:/, $config->{flags}) {
78 if ($flag =~ m/(.*?)=(.*)/) {
79 $config->{"flag_${1}"} = $2;
81 $config->{"flag_${flag}"} = 1;
85 $main::lxdebug->leave_sub();
89 $main::lxdebug->enter_sub();
94 Common::check_params(\%params, qw(module config));
96 my $myconfig = \%main::myconfig;
97 my $form = $main::form;
99 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
101 my $q_id = qq|SELECT nextval('custom_variable_configs_id')|;
102 my $h_id = prepare_query($form, $dbh, $q_id);
105 qq|INSERT INTO custom_variable_configs (name, description, type, default_value, options, searchable, includeable, included_by_default, module, flags, id, sortkey)
106 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
107 (SELECT COALESCE(MAX(sortkey) + 1, 1) FROM custom_variable_configs))|;
108 my $h_new = prepare_query($form, $dbh, $q_new);
111 qq|UPDATE custom_variable_configs SET
112 name = ?, description = ?,
113 type = ?, default_value = ?,
114 options = ?, searchable = ?,
115 includeable = ?, included_by_default = ?,
116 module = ?, flags = ?
118 my $h_update = prepare_query($form, $dbh, $q_update);
121 if ('ARRAY' eq ref $params{config}) {
122 @configs = @{ $params{config} };
124 @configs = ($params{config});
127 foreach my $config (@configs) {
128 my ($h_actual, $q_actual);
130 if (!$config->{id}) {
131 do_statement($form, $h_id, $q_id);
132 ($config->{id}) = $h_id->fetchrow_array();
138 $h_actual = $h_update;
139 $q_actual = $q_update;
142 do_statement($form, $h_actual, $q_actual, @{$config}{qw(name description type default_value options)},
143 $config->{searchable} ? 't' : 'f', $config->{includeable} ? 't' : 'f', $config->{included_by_default} ? 't' : 'f',
144 $params{module}, $config->{flags}, conv_i($config->{id}));
153 $main::lxdebug->leave_sub();
157 $main::lxdebug->enter_sub();
162 Common::check_params(\%params, qw(id));
164 my $myconfig = \%main::myconfig;
165 my $form = $main::form;
167 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
169 do_query($form, $dbh, qq|DELETE FROM custom_variables WHERE config_id = ?|, conv_i($params{id}));
170 do_query($form, $dbh, qq|DELETE FROM custom_variable_configs WHERE id = ?|, conv_i($params{id}));
174 $main::lxdebug->leave_sub();
177 sub get_custom_variables {
178 $main::lxdebug->enter_sub();
183 Common::check_params(\%params, qw(module));
185 my $myconfig = \%main::myconfig;
186 my $form = $main::form;
188 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
190 my $trans_id = $params{trans_id} ? 'OR (v.trans_id = ?) ' : '';
193 qq|SELECT id, name, description, type, default_value, options,
194 date_trunc('seconds', localtimestamp) AS current_timestamp, current_date AS current_date
195 FROM custom_variable_configs
200 qq|SELECT text_value, timestamp_value, timestamp_value::date AS date_value, number_value, bool_value
201 FROM custom_variables
202 WHERE (config_id = ?) AND (trans_id = ?)|;
203 $q_var .= qq| AND (sub_module = ?)| if $params{sub_module};
204 my $h_var = prepare_query($form, $dbh, $q_var);
206 my $custom_variables = selectall_hashref_query($form, $dbh, $q_cfg, $params{module});
208 foreach my $cvar (@{ $custom_variables }) {
209 if ($cvar->{type} eq 'textfield') {
213 $cvar->{width} = $1 if ($cvar->{options} =~ m/width=(\d+)/i);
214 $cvar->{height} = $1 if ($cvar->{options} =~ m/height=(\d+)/i);
216 } elsif ($cvar->{type} eq 'text') {
217 $cvar->{maxlength} = $1 if ($cvar->{options} =~ m/maxlength=(\d+)/i);
219 } elsif ($cvar->{type} eq 'number') {
220 $cvar->{precision} = $1 if ($cvar->{options} =~ m/precision=(\d+)/i);
222 } elsif ($cvar->{type} eq 'select') {
223 $cvar->{OPTIONS} = [ map { { 'value' => $_ } } split(m/\#\#/, $cvar->{options}) ];
227 if ($params{trans_id}) {
228 my @values = (conv_i($cvar->{id}), conv_i($params{trans_id}));
229 push @values, $params{sub_module} if $params{sub_module};
231 do_statement($form, $h_var, $q_var, @values);
232 $act_var = $h_var->fetchrow_hashref();
234 $act_var->{valid} = $self->get_custom_variables_validity(config_id => $cvar->{id}, trans_id => $params{trans_id});
238 $cvar->{value} = $cvar->{type} eq 'date' ? $act_var->{date_value}
239 : $cvar->{type} eq 'timestamp' ? $act_var->{timestamp_value}
240 : $cvar->{type} eq 'number' ? $act_var->{number_value}
241 : $cvar->{type} eq 'bool' ? $act_var->{bool_value}
242 : $act_var->{text_value};
243 $cvar->{valid} = $act_var->{valid};
245 if ($cvar->{type} eq 'date') {
246 if ($cvar->{default_value} eq 'NOW') {
247 $cvar->{value} = $cvar->{current_date};
249 $cvar->{value} = $cvar->{default_value};
252 } elsif ($cvar->{type} eq 'timestamp') {
253 if ($cvar->{default_value} eq 'NOW') {
254 $cvar->{value} = $cvar->{current_timestamp};
256 $cvar->{value} = $cvar->{default_value};
259 } elsif ($cvar->{type} eq 'bool') {
260 $cvar->{value} = $cvar->{default_value} * 1;
262 } elsif ($cvar->{type} eq 'number') {
263 $cvar->{value} = $cvar->{default_value} * 1 if ($cvar->{default_value} ne '');
266 $cvar->{value} = $cvar->{default_value};
270 if ($cvar->{type} eq 'number') {
271 $cvar->{value} = $form->format_amount($myconfig, $cvar->{value} * 1, $cvar->{precision});
277 $main::lxdebug->leave_sub();
279 return $custom_variables;
282 sub save_custom_variables {
283 $main::lxdebug->enter_sub();
288 Common::check_params(\%params, qw(module trans_id variables));
290 my $myconfig = \%main::myconfig;
291 my $form = $main::form;
293 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
295 my @configs = $params{configs} ? @{ $params{configs} } : grep { $_->{module} eq $params{module} } @{ CVar->get_configs() };
298 qq|DELETE FROM custom_variables
300 AND (config_id IN (SELECT DISTINCT id
301 FROM custom_variable_configs
303 my @values = (conv_i($params{trans_id}), $params{module});
305 if ($params{sub_module}) {
306 $query .= qq| AND (sub_module = ?)|;
307 push @values, $params{sub_module};
310 do_query($form, $dbh, $query, @values);
313 qq|INSERT INTO custom_variables (config_id, sub_module, trans_id, bool_value, timestamp_value, text_value, number_value)
314 VALUES (?, ?, ?, ?, ?, ?, ?)|;
315 my $sth = prepare_query($form, $dbh, $query);
317 foreach my $config (@configs) {
318 my @values = (conv_i($config->{id}), "$params{sub_module}", conv_i($params{trans_id}));
320 my $value = $params{variables}->{"$params{name_prefix}cvar_$config->{name}$params{name_postfix}"};
322 if (($config->{type} eq 'text') || ($config->{type} eq 'textfield') || ($config->{type} eq 'select')) {
323 push @values, undef, undef, $value, undef;
325 } elsif (($config->{type} eq 'date') || ($config->{type} eq 'timestamp')) {
326 push @values, undef, conv_date($value), undef, undef;
328 } elsif ($config->{type} eq 'number') {
329 push @values, undef, undef, undef, conv_i($form->parse_amount($myconfig, $value));
331 } elsif ($config->{type} eq 'bool') {
332 push @values, $value ? 't' : 'f', undef, undef, undef;
335 do_statement($form, $sth, $query, @values);
337 $self->save_custom_variables_validity(trans_id => $params{trans_id}, config_id => $config->{id},
338 validity => ($params{variables}->{"$params{name_prefix}cvar_$config->{name}$params{name_postfix}_valid"} ? 1 : 0)
346 $main::lxdebug->leave_sub();
350 $main::lxdebug->enter_sub();
355 Common::check_params(\%params, qw(variables));
357 my $myconfig = \%main::myconfig;
358 my $form = $main::form;
360 my %options = ( name_prefix => "$params{name_prefix}",
361 name_postfix => "$params{name_postfix}",
362 hide_non_editable => $params{hide_non_editable},
363 show_disabled_message => $params{show_disabled_message},
366 foreach my $var (@{ $params{variables} }) {
367 $var->{HTML_CODE} = $form->parse_html_template('amcvar/render_inputs', { var => $var, %options });
368 $var->{VALID_BOX} = $form->parse_html_template('amcvar/render_checkboxes', { var => $var, %options });
371 $main::lxdebug->leave_sub();
374 sub render_search_options {
375 $main::lxdebug->enter_sub();
380 Common::check_params(\%params, qw(variables));
382 my $myconfig = \%main::myconfig;
383 my $form = $main::form;
385 $params{include_prefix} = 'l_' unless defined($params{include_prefix});
386 $params{include_value} ||= '1';
388 my $filter = $form->parse_html_template('amcvar/search_filter', \%params);
389 my $include = $form->parse_html_template('amcvar/search_include', \%params);
391 $main::lxdebug->leave_sub();
393 return ($filter, $include);
396 sub build_filter_query {
397 $main::lxdebug->enter_sub();
402 Common::check_params(\%params, qw(module trans_id_field filter));
404 my $myconfig = \%main::myconfig;
405 my $form = $main::form;
407 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
409 my $configs = $self->get_configs(%params);
411 my (@where, @values);
413 foreach my $config (@{ $configs }) {
414 next unless ($config->{searchable});
416 my $name = "cvar_$config->{name}";
418 my (@sub_values, @sub_where, $not);
420 if (($config->{type} eq 'text') || ($config->{type} eq 'textfield')) {
421 next unless ($params{filter}->{$name});
423 push @sub_where, qq|cvar.text_value ILIKE ?|;
424 push @sub_values, '%' . $params{filter}->{$name} . '%'
426 } elsif ($config->{type} eq 'select') {
427 next unless ($params{filter}->{$name});
429 push @sub_where, qq|cvar.text_value = ?|;
430 push @sub_values, $params{filter}->{$name};
432 } elsif (($config->{type} eq 'date') || ($config->{type} eq 'timestamp')) {
433 my $name_from = "${name}_from";
434 my $name_to = "${name}_to";
436 if ($params{filter}->{$name_from}) {
437 push @sub_where, qq|cvar.timestamp_value >= ?|;
438 push @sub_values, conv_date($params{filter}->{$name_from});
441 if ($params{filter}->{$name_to}) {
442 push @sub_where, qq|cvar.timestamp_value <= ?|;
443 push @sub_values, conv_date($params{filter}->{$name_to});
446 } elsif ($config->{type} eq 'number') {
447 next if ($params{filter}->{$name} eq '');
449 my $f_op = $params{filter}->{"${name}_qtyop"};
455 } elsif ($f_op eq '=/=') {
459 } elsif ($f_op eq '<') {
463 } elsif ($f_op eq '<=') {
467 } elsif (($f_op eq '>') || ($f_op eq '>=')) {
474 push @sub_where, qq|cvar.number_value $op ?|;
475 push @sub_values, $form->parse_amount($myconfig, $params{filter}->{$name});
477 } elsif ($config->{type} eq 'bool') {
478 next unless ($params{filter}->{$name});
480 $not = 'NOT' if ($params{filter}->{$name} eq 'no');
481 push @sub_where, qq|COALESCE(cvar.bool_value, false) = TRUE|;
485 push @sub_where, qq|cvar.sub_module = ?|;
486 push @sub_values, "$params{sub_module}";
491 FROM custom_variables cvar
492 LEFT JOIN custom_variable_configs cvarcfg ON (cvar.config_id = cvarcfg.id)
493 WHERE (cvarcfg.module = ?)
495 AND (cvar.trans_id = $params{trans_id_field})
496 AND | . join(' AND ', map { "($_)" } @sub_where) . qq|)|;
497 push @values, $params{module}, conv_i($config->{id}), @sub_values;
501 my $query = join ' AND ', @where;
503 $main::lxdebug->leave_sub();
505 return ($query, @values);
508 sub add_custom_variables_to_report {
509 $main::lxdebug->enter_sub();
514 Common::check_params(\%params, qw(module trans_id_field column_defs data configs));
516 my $myconfig = \%main::myconfig;
517 my $form = $main::form;
518 my $locale = $main::locale;
520 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
522 my $configs = [ grep { $_->{includeable} && $params{column_defs}->{"cvar_$_->{name}"}->{visible} } @{ $params{configs} } ];
524 if (!scalar(@{ $params{data} }) || ! scalar(@{ $configs })) {
525 $main::lxdebug->leave_sub();
529 # allow sub_module to be a coderef or a fixed value
530 if (ref $params{sub_module} ne 'CODE') {
531 $params{sub_module} = sub { "$params{sub_module}" };
534 my %cfg_map = map { $_->{id} => $_ } @{ $configs };
535 my @cfg_ids = keys %cfg_map;
538 qq|SELECT text_value, timestamp_value, timestamp_value::date AS date_value, number_value, bool_value, config_id
539 FROM custom_variables
540 WHERE (config_id IN (| . join(', ', ('?') x scalar(@cfg_ids)) . qq|))
542 AND (sub_module = ?)|;
543 my $sth = prepare_query($form, $dbh, $query);
545 foreach my $row (@{ $params{data} }) {
546 do_statement($form, $sth, $query, @cfg_ids, conv_i($row->{$params{trans_id_field}}), $params{sub_module}->($row));
548 while (my $ref = $sth->fetchrow_hashref()) {
549 my $cfg = $cfg_map{$ref->{config_id}};
551 $row->{"cvar_$cfg->{name}"} =
552 $cfg->{type} eq 'date' ? $ref->{date_value}
553 : $cfg->{type} eq 'timestamp' ? $ref->{timestamp_value}
554 : $cfg->{type} eq 'number' ? $form->format_amount($myconfig, $ref->{number_value} * 1, $cfg->{precision})
555 : $cfg->{type} eq 'bool' ? ($ref->{bool_value} ? $locale->text('Yes') : $locale->text('No'))
556 : $ref->{text_value};
562 $main::lxdebug->leave_sub();
565 sub get_field_format_list {
566 $main::lxdebug->enter_sub();
571 Common::check_params(\%params, qw(module));
573 my $myconfig = \%main::myconfig;
574 my $form = $main::form;
576 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
578 my $configs = $self->get_configs(%params);
580 my $date_fields = [];
581 my $number_fields = {};
583 foreach my $config (@{ $configs }) {
584 my $name = "$params{prefix}cvar_$config->{name}";
586 if ($config->{type} eq 'date') {
587 push @{ $date_fields }, $name;
589 } elsif ($config->{type} eq 'number') {
590 $number_fields->{$config->{precision}} ||= [];
591 push @{ $number_fields->{$config->{precision}} }, $name;
595 $main::lxdebug->leave_sub();
597 return ($date_fields, $number_fields);
602 Suppose the following scenario:
604 You have a lot of parts in your database, and a set of properties cofigured. Now not every part has every of these properties, some combinations will just make no sense. In order to clean up your inputs a bit, you want to mark certain combinations as invalid, blocking them from modification and possibly display.
606 Validity is assumed. If you modify validity, you actually save B<invalidity>.
607 iNvalidity is saved as a function of config_id, and the trans_id
609 In the naive way, disable an attribute for a specific id (simple)
612 sub save_custom_variables_validity {
613 $main::lxdebug->enter_sub();
618 Common::check_params(\%params, qw(config_id trans_id validity));
620 my $myconfig = \%main::myconfig;
621 my $form = $main::form;
623 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
625 my (@where, @values);
626 add_token(\@where, \@values, col => "config_id", val => $params{config_id}, esc => \&conv_i);
627 add_token(\@where, \@values, col => "trans_id", val => $params{trans_id}, esc => \&conv_i);
629 my $where = scalar @where ? "WHERE " . join ' AND ', @where : '';
630 my $query = qq|DELETE FROM custom_variables_validity $where|;
632 do_query($form, $dbh, $query, @values);
635 qq|INSERT INTO custom_variables_validity (config_id, trans_id)
637 my $sth = prepare_query($form, $dbh, $query);
639 unless ($params{validity}) {
640 foreach my $config_id (listify $params{config_id}) {
641 foreach my $trans_id (listify $params{trans_id}) {
642 do_statement($form, $sth, $query, conv_i($config_id), conv_i($trans_id));
651 $main::lxdebug->leave_sub();
654 sub get_custom_variables_validity {
655 $main::lxdebug->enter_sub();
660 Common::check_params(\%params, qw(config_id trans_id));
662 my $myconfig = \%main::myconfig;
663 my $form = $main::form;
665 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
667 my $query = qq|SELECT COUNT(*) FROM custom_variables_validity WHERE config_id = ? AND trans_id = ?|;
669 my ($invalid) = selectfirst_array_query($form, $dbh, $query, conv_i($params{config_id}), conv_i($params{trans_id}));
671 $main::lxdebug->leave_sub();