5 use List::MoreUtils qw(any);
6 use List::Util qw(first);
7 use Scalar::Util qw(blessed);
11 use SL::MoreCommon qw(listify);
14 $main::lxdebug->enter_sub();
19 my $myconfig = \%main::myconfig;
20 my $form = $main::form;
22 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
25 if ($params{module}) {
26 $where = 'WHERE module = ?';
27 push @values, $params{module};
31 SELECT *, date_trunc('seconds', localtimestamp) AS current_timestamp
32 FROM custom_variable_configs $where ORDER BY sortkey
35 $::form->{CVAR_CONFIGS} = {} unless 'HASH' eq ref $::form->{CVAR_CONFIGS};
36 if (!$::form->{CVAR_CONFIGS}->{$params{module}}) {
37 my $configs = selectall_hashref_query($form, $dbh, $query, @values);
39 foreach my $config (@{ $configs }) {
40 if ($config->{type} eq 'select') {
41 $config->{OPTIONS} = [ map { { 'value' => $_ } } split(m/\#\#/, $config->{options}) ];
43 } elsif ($config->{type} eq 'number') {
44 $config->{precision} = $1 if ($config->{options} =~ m/precision=(\d+)/i);
46 } elsif ($config->{type} eq 'textfield') {
47 $config->{width} = 30;
48 $config->{height} = 5;
49 $config->{width} = $1 if ($config->{options} =~ m/width=(\d+)/i);
50 $config->{height} = $1 if ($config->{options} =~ m/height=(\d+)/i);
52 } elsif ($config->{type} eq 'text') {
53 $config->{maxlength} = $1 if ($config->{options} =~ m/maxlength=(\d+)/i);
57 $self->_unpack_flags($config);
59 my $cvar_config = SL::DB::CustomVariableConfig->new(id => $config->{id})->load;
60 @{$config->{'partsgroups'}} = map {$_->id} @{$cvar_config->partsgroups};
63 $::form->{CVAR_CONFIGS}->{$params{module}} = $configs;
66 $main::lxdebug->leave_sub();
68 return $::form->{CVAR_CONFIGS}->{$params{module}};
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();
88 sub get_custom_variables {
89 $main::lxdebug->enter_sub();
94 Common::check_params(\%params, qw(module));
96 my $myconfig = \%main::myconfig;
97 my $form = $main::form;
99 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
101 my $trans_id = $params{trans_id} ? 'OR (v.trans_id = ?) ' : '';
103 my $sub_module = $params{sub_module} ? $params{sub_module} : '';
106 qq|SELECT text_value, timestamp_value, timestamp_value::date AS date_value, number_value, bool_value
107 FROM custom_variables
108 WHERE (config_id = ?) AND (trans_id = ?) AND (sub_module = ?)|;
109 my $h_var = prepare_query($form, $dbh, $q_var);
111 my $custom_variables = $self->get_configs(module => $params{module});
113 foreach my $cvar (@{ $custom_variables }) {
114 if ($cvar->{type} eq 'textfield') {
118 $cvar->{width} = $1 if ($cvar->{options} =~ m/width=(\d+)/i);
119 $cvar->{height} = $1 if ($cvar->{options} =~ m/height=(\d+)/i);
121 } elsif ($cvar->{type} eq 'text') {
122 $cvar->{maxlength} = $1 if ($cvar->{options} =~ m/maxlength=(\d+)/i);
124 } elsif ($cvar->{type} eq 'number') {
125 $cvar->{precision} = $1 if ($cvar->{options} =~ m/precision=(\d+)/i);
127 } elsif ($cvar->{type} eq 'select') {
128 $cvar->{OPTIONS} = [ map { { 'value' => $_ } } split(m/\#\#/, $cvar->{options}) ];
131 my ($act_var, $valid);
132 if ($params{trans_id}) {
133 my @values = (conv_i($cvar->{id}), conv_i($params{trans_id}), $sub_module);
135 do_statement($form, $h_var, $q_var, @values);
136 $act_var = $h_var->fetchrow_hashref();
138 $valid = $self->get_custom_variables_validity(config_id => $cvar->{id}, trans_id => $params{trans_id});
140 $valid = !$cvar->{flag_defaults_to_invalid};
144 $cvar->{value} = $cvar->{type} eq 'date' ? $act_var->{date_value}
145 : $cvar->{type} eq 'timestamp' ? $act_var->{timestamp_value}
146 : $cvar->{type} eq 'number' ? $act_var->{number_value}
147 : $cvar->{type} eq 'customer' ? $act_var->{number_value}
148 : $cvar->{type} eq 'vendor' ? $act_var->{number_value}
149 : $cvar->{type} eq 'part' ? $act_var->{number_value}
150 : $cvar->{type} eq 'bool' ? $act_var->{bool_value}
151 : $act_var->{text_value};
152 $cvar->{valid} = $valid;
154 $cvar->{valid} = $valid // 1;
156 if ($cvar->{type} eq 'date') {
157 if ($cvar->{default_value} eq 'NOW') {
158 $cvar->{value} = $cvar->{current_date};
160 $cvar->{value} = $cvar->{default_value};
163 } elsif ($cvar->{type} eq 'timestamp') {
164 if ($cvar->{default_value} eq 'NOW') {
165 $cvar->{value} = $cvar->{current_timestamp};
167 $cvar->{value} = $cvar->{default_value};
170 } elsif ($cvar->{type} eq 'bool') {
171 $cvar->{value} = $cvar->{default_value} * 1;
173 } elsif ($cvar->{type} eq 'number') {
174 $cvar->{value} = $cvar->{default_value} * 1 if ($cvar->{default_value} ne '');
177 $cvar->{value} = $cvar->{default_value};
181 if ($cvar->{type} eq 'number') {
182 $cvar->{value} = $form->format_amount($myconfig, $cvar->{value} * 1, $cvar->{precision});
183 } elsif ($cvar->{type} eq 'customer') {
184 require SL::DB::Customer;
185 $cvar->{value} = SL::DB::Manager::Customer->find_by(id => $cvar->{value} * 1);
186 } elsif ($cvar->{type} eq 'vendor') {
187 require SL::DB::Vendor;
188 $cvar->{value} = SL::DB::Manager::Vendor->find_by(id => $cvar->{value} * 1);
189 } elsif ($cvar->{type} eq 'part') {
190 require SL::DB::Part;
191 $cvar->{value} = SL::DB::Manager::Part->find_by(id => $cvar->{value} * 1);
197 $main::lxdebug->leave_sub();
199 return $custom_variables;
202 sub save_custom_variables {
203 $main::lxdebug->enter_sub();
208 Common::check_params(\%params, qw(module trans_id variables));
210 my $myconfig = \%main::myconfig;
211 my $form = $main::form;
213 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
215 my @configs = $params{configs} ? @{ $params{configs} } : grep { $_->{module} eq $params{module} } @{ CVar->get_configs() };
218 qq|DELETE FROM custom_variables
220 AND (config_id IN (SELECT DISTINCT id
221 FROM custom_variable_configs
223 my @values = (conv_i($params{trans_id}), $params{module});
225 if ($params{sub_module}) {
226 $query .= qq| AND (sub_module = ?)|;
227 push @values, $params{sub_module};
230 do_query($form, $dbh, $query, @values);
233 qq|INSERT INTO custom_variables (config_id, sub_module, trans_id, bool_value, timestamp_value, text_value, number_value)
234 VALUES (?, ?, ?, ?, ?, ?, ?)|;
235 my $sth = prepare_query($form, $dbh, $query);
237 foreach my $config (@configs) {
238 my @values = (conv_i($config->{id}), "$params{sub_module}", conv_i($params{trans_id}));
240 my $value = $params{variables}->{"$params{name_prefix}cvar_$config->{name}$params{name_postfix}"};
242 if (($config->{type} eq 'text') || ($config->{type} eq 'textfield') || ($config->{type} eq 'select')) {
243 push @values, undef, undef, $value, undef;
245 } elsif (($config->{type} eq 'date') || ($config->{type} eq 'timestamp')) {
246 push @values, undef, conv_date($value), undef, undef;
248 } elsif ($config->{type} eq 'number') {
249 push @values, undef, undef, undef, conv_i($form->parse_amount($myconfig, $value));
251 } elsif ($config->{type} eq 'bool') {
252 push @values, $value ? 't' : 'f', undef, undef, undef;
253 } elsif (any { $config->{type} eq $_ } qw(customer vendor part)) {
254 push @values, undef, undef, undef, $value * 1;
257 do_statement($form, $sth, $query, @values);
259 if ($params{save_validity}) {
260 my $valid_index = "$params{name_prefix}cvar_$config->{name}$params{name_postfix}_valid";
261 $self->save_custom_variables_validity(trans_id => $params{trans_id},
262 config_id => $config->{id},
263 validity => ($params{variables}{$valid_index} || $params{always_valid} ? 1 : 0)
270 $dbh->commit() unless $params{dbh};
272 $main::lxdebug->leave_sub();
276 $main::lxdebug->enter_sub(2);
281 Common::check_params(\%params, qw(variables));
283 my $myconfig = \%main::myconfig;
284 my $form = $main::form;
286 my %options = ( name_prefix => "$params{name_prefix}",
287 name_postfix => "$params{name_postfix}",
288 hide_non_editable => $params{hide_non_editable},
289 show_disabled_message => $params{show_disabled_message},
292 # should this cvar be filtered by partsgroups?
293 foreach my $var (@{ $params{variables} }) {
294 if ($var->{flag_partsgroup_filter}) {
295 if (!$params{partsgroup_id} || (!grep {$params{partsgroup_id} == $_} @{ $var->{partsgroups} })) {
296 $var->{partsgroup_filtered} = 1;
300 $var->{HTML_CODE} = $form->parse_html_template('amcvar/render_inputs', { var => $var, %options });
301 $var->{VALID_BOX} = $form->parse_html_template('amcvar/render_checkboxes', { var => $var, %options });
304 # needed for customer_vendor_picker
305 $::request->{layout}->add_javascripts('autocomplete_customer.js');
307 $main::lxdebug->leave_sub(2);
310 sub render_search_options {
311 $main::lxdebug->enter_sub();
316 Common::check_params(\%params, qw(variables));
318 my $myconfig = \%main::myconfig;
319 my $form = $main::form;
321 $params{hidden_cvar_filters} = $myconfig->{hide_cvar_search_options};
323 $params{include_prefix} = 'l_' unless defined($params{include_prefix});
324 $params{include_value} ||= '1';
325 $params{filter_prefix} ||= '';
327 my $filter = $form->parse_html_template('amcvar/search_filter', \%params);
328 my $include = $form->parse_html_template('amcvar/search_include', \%params);
330 $main::lxdebug->leave_sub();
332 return ($filter, $include);
335 sub build_filter_query {
336 $main::lxdebug->enter_sub();
341 Common::check_params(\%params, qw(module trans_id_field filter));
343 my $myconfig = \%main::myconfig;
344 my $form = $main::form;
346 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
348 my $configs = $self->get_configs(%params);
350 my (@where, @values);
352 foreach my $config (@{ $configs }) {
353 next unless ($config->{searchable});
355 my $name = "cvar_$config->{name}";
357 my (@sub_values, @sub_where, $not);
359 if (($config->{type} eq 'text') || ($config->{type} eq 'textfield')) {
360 next unless ($params{filter}->{$name});
362 push @sub_where, qq|cvar.text_value ILIKE ?|;
363 push @sub_values, '%' . $params{filter}->{$name} . '%'
365 } elsif ($config->{type} eq 'select') {
366 next unless ($params{filter}->{$name});
368 push @sub_where, qq|cvar.text_value = ?|;
369 push @sub_values, $params{filter}->{$name};
371 } elsif (($config->{type} eq 'date') || ($config->{type} eq 'timestamp')) {
372 my $name_from = "${name}_from";
373 my $name_to = "${name}_to";
375 if ($params{filter}->{$name_from}) {
376 push @sub_where, qq|cvar.timestamp_value >= ?|;
377 push @sub_values, conv_date($params{filter}->{$name_from});
380 if ($params{filter}->{$name_to}) {
381 push @sub_where, qq|cvar.timestamp_value <= ?|;
382 push @sub_values, conv_date($params{filter}->{$name_to});
385 } elsif ($config->{type} eq 'number') {
386 next if ($params{filter}->{$name} eq '');
388 my $f_op = $params{filter}->{"${name}_qtyop"};
394 } elsif ($f_op eq '=/=') {
398 } elsif ($f_op eq '<') {
402 } elsif ($f_op eq '<=') {
406 } elsif (($f_op eq '>') || ($f_op eq '>=')) {
413 push @sub_where, qq|cvar.number_value $op ?|;
414 push @sub_values, $form->parse_amount($myconfig, $params{filter}->{$name});
416 } elsif ($config->{type} eq 'bool') {
417 next unless ($params{filter}->{$name});
419 $not = 'NOT' if ($params{filter}->{$name} eq 'no');
420 push @sub_where, qq|COALESCE(cvar.bool_value, false) = TRUE|;
421 } elsif (any { $config->{type} eq $_ } qw(customer vendor)) {
422 next unless $params{filter}->{$name};
424 my $table = $config->{type};
425 push @sub_where, qq|cvar.number_value * 1 IN (SELECT id FROM $table WHERE name ILIKE ?)|;
426 push @sub_values, "%$params{filter}->{$name}%";
427 } elsif ($config->{type} eq 'part') {
428 next unless $params{filter}->{$name};
430 push @sub_where, qq|cvar.number_value * 1 IN (SELECT id FROM parts WHERE partnumber ILIKE ?)|;
431 push @sub_values, "%$params{filter}->{$name}%";
435 add_token(\@sub_where, \@sub_values, col => 'cvar.sub_module', val => $params{sub_module} || '');
440 FROM custom_variables cvar
441 LEFT JOIN custom_variable_configs cvarcfg ON (cvar.config_id = cvarcfg.id)
442 WHERE (cvarcfg.module = ?)
444 AND (cvar.trans_id = $params{trans_id_field})
445 AND | . join(' AND ', map { "($_)" } @sub_where) . qq|)|;
446 push @values, $params{module}, conv_i($config->{id}), @sub_values;
450 my $query = join ' AND ', @where;
452 $main::lxdebug->leave_sub();
454 return ($query, @values);
457 sub add_custom_variables_to_report {
458 $main::lxdebug->enter_sub();
463 Common::check_params(\%params, qw(module trans_id_field column_defs data configs));
465 my $myconfig = \%main::myconfig;
466 my $form = $main::form;
467 my $locale = $main::locale;
469 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
471 my $configs = [ grep { $_->{includeable} && $params{column_defs}->{"cvar_$_->{name}"}->{visible} } @{ $params{configs} } ];
473 if (!scalar(@{ $params{data} }) || ! scalar(@{ $configs })) {
474 $main::lxdebug->leave_sub();
478 # allow sub_module to be a coderef or a fixed value
479 if (ref $params{sub_module} ne 'CODE') {
480 my $sub_module = "$params{sub_module}";
481 $params{sub_module} = sub { $sub_module };
484 my %cfg_map = map { $_->{id} => $_ } @{ $configs };
485 my @cfg_ids = keys %cfg_map;
488 qq|SELECT text_value, timestamp_value, timestamp_value::date AS date_value, number_value, bool_value, config_id
489 FROM custom_variables
490 WHERE (config_id IN (| . join(', ', ('?') x scalar(@cfg_ids)) . qq|))
492 AND (sub_module = ?)|;
493 my $sth = prepare_query($form, $dbh, $query);
495 foreach my $row (@{ $params{data} }) {
496 do_statement($form, $sth, $query, @cfg_ids, conv_i($row->{$params{trans_id_field}}), $params{sub_module}->($row));
498 while (my $ref = $sth->fetchrow_hashref()) {
499 my $cfg = $cfg_map{$ref->{config_id}};
501 $row->{"cvar_$cfg->{name}"} =
502 $cfg->{type} eq 'date' ? $ref->{date_value}
503 : $cfg->{type} eq 'timestamp' ? $ref->{timestamp_value}
504 : $cfg->{type} eq 'number' ? $form->format_amount($myconfig, $ref->{number_value} * 1, $cfg->{precision})
505 : $cfg->{type} eq 'customer' ? (SL::DB::Manager::Customer->find_by(id => 1*$ref->{number_value}) || SL::DB::Customer->new)->name
506 : $cfg->{type} eq 'vendor' ? (SL::DB::Manager::Vendor->find_by(id => 1*$ref->{number_value}) || SL::DB::Vendor->new)->name
507 : $cfg->{type} eq 'part' ? (SL::DB::Manager::Part->find_by(id => 1*$ref->{number_value}) || SL::DB::Part->new)->partnumber
508 : $cfg->{type} eq 'bool' ? ($ref->{bool_value} ? $locale->text('Yes') : $locale->text('No'))
509 : $ref->{text_value};
515 $main::lxdebug->leave_sub();
518 sub get_field_format_list {
519 $main::lxdebug->enter_sub();
524 Common::check_params(\%params, qw(module));
526 my $myconfig = \%main::myconfig;
527 my $form = $main::form;
529 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
531 my $configs = $self->get_configs(%params);
533 my $date_fields = [];
534 my $number_fields = {};
536 foreach my $config (@{ $configs }) {
537 my $name = "$params{prefix}cvar_$config->{name}";
539 if ($config->{type} eq 'date') {
540 push @{ $date_fields }, $name;
542 } elsif ($config->{type} eq 'number') {
543 $number_fields->{$config->{precision}} ||= [];
544 push @{ $number_fields->{$config->{precision}} }, $name;
548 $main::lxdebug->leave_sub();
550 return ($date_fields, $number_fields);
553 sub save_custom_variables_validity {
554 $main::lxdebug->enter_sub();
559 Common::check_params(\%params, qw(config_id trans_id validity));
561 my $myconfig = \%main::myconfig;
562 my $form = $main::form;
564 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
566 my (@where, @values);
567 add_token(\@where, \@values, col => "config_id", val => $params{config_id}, esc => \&conv_i);
568 add_token(\@where, \@values, col => "trans_id", val => $params{trans_id}, esc => \&conv_i);
570 my $where = scalar @where ? "WHERE " . join ' AND ', @where : '';
571 my $query = qq|DELETE FROM custom_variables_validity $where|;
573 do_query($form, $dbh, $query, @values);
576 qq|INSERT INTO custom_variables_validity (config_id, trans_id)
578 my $sth = prepare_query($form, $dbh, $query);
580 unless ($params{validity}) {
581 foreach my $config_id (listify($params{config_id})) {
582 foreach my $trans_id (listify($params{trans_id})) {
583 do_statement($form, $sth, $query, conv_i($config_id), conv_i($trans_id));
590 $dbh->commit() unless $params{dbh};
592 $main::lxdebug->leave_sub();
595 sub get_custom_variables_validity {
596 $main::lxdebug->enter_sub(2);
601 Common::check_params(\%params, qw(config_id trans_id));
603 my $myconfig = \%main::myconfig;
604 my $form = $main::form;
606 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
608 my $query = qq|SELECT id FROM custom_variables_validity WHERE config_id = ? AND trans_id = ? LIMIT 1|;
610 my ($invalid) = selectfirst_array_query($form, $dbh, $query, conv_i($params{config_id}), conv_i($params{trans_id}));
612 $main::lxdebug->leave_sub(2);
617 sub custom_variables_validity_by_trans_id {
618 $main::lxdebug->enter_sub(2);
623 return sub { 0 } unless $params{trans_id};
625 my $myconfig = \%main::myconfig;
626 my $form = $main::form;
628 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
630 my $query = qq|SELECT DISTINCT config_id FROM custom_variables_validity WHERE trans_id = ?|;
632 my %invalids = map { +($_->{config_id} => 1) } selectall_hashref_query($form, $dbh, $query, $params{trans_id});
634 $main::lxdebug->leave_sub(2);
636 return sub { !$invalids{+shift} };
640 my ($self, $value, $config) = @_;
642 return $::form->parse_amount(\%::myconfig, $value) if $config->{type} eq 'number';
643 return DateTime->from_lxoffice($value) if $config->{type} eq 'date';
644 return !ref $value ? SL::DB::Manager::Customer->find_by(id => $value * 1) : $value if $config->{type} eq 'customer';
645 return !ref $value ? SL::DB::Manager::Vendor->find_by(id => $value * 1) : $value if $config->{type} eq 'vendor';
646 return !ref $value ? SL::DB::Manager::Part->find_by(id => $value * 1) : $value if $config->{type} eq 'part';
650 sub format_to_template {
651 my ($self, $value, $config) = @_;
652 # stupid template expects everything formated. except objects
653 # do not use outside of print routines for legacy templates
655 return $::form->format_amount(\%::myconfig, $value) if $config->{type} eq 'number';
656 return $value->to_lxoffice if $config->{type} eq 'date' && blessed $value && $value->can('to_lxoffice');
666 SL::CVar.pm - Custom Variables module
670 # dealing with configs
672 my $all_configs = CVar->get_configs()
674 # dealing with custom vars
676 CVar->get_custom_variables(module => 'ic')
680 Suppose the following scenario:
682 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.
684 Validity is assumed. If you modify validity, you actually save B<invalidity>.
685 Invalidity is saved as a function of config_id, and the trans_id
687 In the naive way, disable an attribute for a specific id (simple)