6 use List::MoreUtils qw(any);
7 use List::Util qw(first);
8 use Scalar::Util qw(blessed);
12 use SL::MoreCommon qw(listify);
13 use SL::Util qw(trim);
16 $main::lxdebug->enter_sub();
21 my $myconfig = \%main::myconfig;
22 my $form = $main::form;
24 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
27 if ($params{module}) {
28 $where = 'WHERE module = ?';
29 push @values, $params{module};
33 SELECT *, date_trunc('seconds', localtimestamp) AS current_timestamp
34 FROM custom_variable_configs $where ORDER BY sortkey
37 $::form->{CVAR_CONFIGS} = {} unless 'HASH' eq ref $::form->{CVAR_CONFIGS};
38 if (!$::form->{CVAR_CONFIGS}->{$params{module}}) {
39 my $configs = selectall_hashref_query($form, $dbh, $query, @values);
41 foreach my $config (@{ $configs }) {
42 if ($config->{type} eq 'select') {
43 $config->{OPTIONS} = [ map { { 'value' => $_ } } split(m/\#\#/, $config->{options}) ];
45 } elsif ($config->{type} eq 'number') {
46 $config->{precision} = $1 if ($config->{options} =~ m/precision=(\d+)/i);
48 } elsif ($config->{type} eq 'textfield') {
49 $config->{width} = 30;
50 $config->{height} = 5;
51 $config->{width} = $1 if ($config->{options} =~ m/width=(\d+)/i);
52 $config->{height} = $1 if ($config->{options} =~ m/height=(\d+)/i);
54 } elsif ($config->{type} eq 'text') {
55 $config->{maxlength} = $1 if ($config->{options} =~ m/maxlength=(\d+)/i);
59 $self->_unpack_flags($config);
61 my $cvar_config = SL::DB::CustomVariableConfig->new(id => $config->{id})->load;
62 @{$config->{'partsgroups'}} = map {$_->id} @{$cvar_config->partsgroups};
65 $::form->{CVAR_CONFIGS}->{$params{module}} = $configs;
68 $main::lxdebug->leave_sub();
70 return $::form->{CVAR_CONFIGS}->{$params{module}};
74 $main::lxdebug->enter_sub();
79 foreach my $flag (split m/:/, $config->{flags}) {
80 if ($flag =~ m/(.*?)=(.*)/) {
81 $config->{"flag_${1}"} = $2;
83 $config->{"flag_${flag}"} = 1;
87 $main::lxdebug->leave_sub();
90 sub get_custom_variables {
91 $main::lxdebug->enter_sub();
96 Common::check_params(\%params, qw(module));
98 my $myconfig = \%main::myconfig;
99 my $form = $main::form;
101 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
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}, sub_module => $params{sub_module});
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 $main::lxdebug->leave_sub(2);
307 sub render_search_options {
308 $main::lxdebug->enter_sub();
313 Common::check_params(\%params, qw(variables));
315 my $myconfig = \%main::myconfig;
316 my $form = $main::form;
318 $params{hidden_cvar_filters} = $myconfig->{hide_cvar_search_options};
320 $params{include_prefix} = 'l_' unless defined($params{include_prefix});
321 $params{include_value} ||= '1';
322 $params{filter_prefix} ||= '';
324 my $filter = $form->parse_html_template('amcvar/search_filter', \%params);
325 my $include = $form->parse_html_template('amcvar/search_include', \%params);
327 $main::lxdebug->leave_sub();
329 return ($filter, $include);
332 sub build_filter_query {
333 $main::lxdebug->enter_sub();
338 Common::check_params(\%params, qw(module trans_id_field filter));
340 my $myconfig = \%main::myconfig;
341 my $form = $main::form;
343 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
345 my $configs = $self->get_configs(%params);
347 my (@where, @values);
349 foreach my $config (@{ $configs }) {
350 next unless ($config->{searchable});
352 my $name = "cvar_$config->{name}";
354 my (@sub_values, @sub_where, $not);
356 if (($config->{type} eq 'text') || ($config->{type} eq 'textfield')) {
357 next unless ($params{filter}->{$name});
359 push @sub_where, qq|cvar.text_value ILIKE ?|;
360 push @sub_values, like($params{filter}->{$name});
362 } elsif ($config->{type} eq 'select') {
363 next unless ($params{filter}->{$name});
365 push @sub_where, qq|cvar.text_value = ?|;
366 push @sub_values, $params{filter}->{$name};
368 } elsif (($config->{type} eq 'date') || ($config->{type} eq 'timestamp')) {
369 my $name_from = "${name}_from";
370 my $name_to = "${name}_to";
372 if ($params{filter}->{$name_from}) {
373 push @sub_where, qq|cvar.timestamp_value >= ?|;
374 push @sub_values, conv_date($params{filter}->{$name_from});
377 if ($params{filter}->{$name_to}) {
378 push @sub_where, qq|cvar.timestamp_value <= ?|;
379 push @sub_values, conv_date($params{filter}->{$name_to});
382 } elsif ($config->{type} eq 'number') {
383 next if ($params{filter}->{$name} eq '');
385 my $f_op = $params{filter}->{"${name}_qtyop"};
391 } elsif ($f_op eq '=/=') {
395 } elsif ($f_op eq '<') {
399 } elsif ($f_op eq '<=') {
403 } elsif (($f_op eq '>') || ($f_op eq '>=')) {
410 push @sub_where, qq|cvar.number_value $op ?|;
411 push @sub_values, $form->parse_amount($myconfig, trim($params{filter}->{$name}));
413 } elsif ($config->{type} eq 'bool') {
414 next unless ($params{filter}->{$name});
416 $not = 'NOT' if ($params{filter}->{$name} eq 'no');
417 push @sub_where, qq|COALESCE(cvar.bool_value, false) = TRUE|;
418 } elsif (any { $config->{type} eq $_ } qw(customer vendor)) {
419 next unless $params{filter}->{$name};
421 my $table = $config->{type};
422 push @sub_where, qq|cvar.number_value * 1 IN (SELECT id FROM $table WHERE name ILIKE ?)|;
423 push @sub_values, like($params{filter}->{$name});
424 } elsif ($config->{type} eq 'part') {
425 next unless $params{filter}->{$name};
427 push @sub_where, qq|cvar.number_value * 1 IN (SELECT id FROM parts WHERE partnumber ILIKE ?)|;
428 push @sub_values, like($params{filter}->{$name});
432 add_token(\@sub_where, \@sub_values, col => 'cvar.sub_module', val => $params{sub_module} || '');
437 FROM custom_variables cvar
438 LEFT JOIN custom_variable_configs cvarcfg ON (cvar.config_id = cvarcfg.id)
439 WHERE (cvarcfg.module = ?)
441 AND (cvar.trans_id = $params{trans_id_field})
442 AND | . join(' AND ', map { "($_)" } @sub_where) . qq|)|;
443 push @values, $params{module}, conv_i($config->{id}), @sub_values;
447 my $query = join ' AND ', @where;
449 $main::lxdebug->leave_sub();
451 return ($query, @values);
454 sub add_custom_variables_to_report {
455 $main::lxdebug->enter_sub();
460 Common::check_params(\%params, qw(module trans_id_field column_defs data configs));
462 my $myconfig = \%main::myconfig;
463 my $form = $main::form;
464 my $locale = $main::locale;
466 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
468 my $configs = [ grep { $_->{includeable} && $params{column_defs}->{"cvar_$_->{name}"}->{visible} } @{ $params{configs} } ];
470 if (!scalar(@{ $params{data} }) || ! scalar(@{ $configs })) {
471 $main::lxdebug->leave_sub();
475 # allow sub_module to be a coderef or a fixed value
476 if (ref $params{sub_module} ne 'CODE') {
477 my $sub_module = "$params{sub_module}";
478 $params{sub_module} = sub { $sub_module };
481 my %cfg_map = map { $_->{id} => $_ } @{ $configs };
482 my @cfg_ids = keys %cfg_map;
485 qq|SELECT text_value, timestamp_value, timestamp_value::date AS date_value, number_value, bool_value, config_id
486 FROM custom_variables
487 WHERE (config_id IN (| . join(', ', ('?') x scalar(@cfg_ids)) . qq|))
489 AND (sub_module = ?)|;
490 my $sth = prepare_query($form, $dbh, $query);
492 foreach my $row (@{ $params{data} }) {
493 do_statement($form, $sth, $query, @cfg_ids, conv_i($row->{$params{trans_id_field}}), $params{sub_module}->($row));
495 while (my $ref = $sth->fetchrow_hashref()) {
496 my $cfg = $cfg_map{$ref->{config_id}};
498 $row->{"cvar_$cfg->{name}"} =
499 $cfg->{type} eq 'date' ? $ref->{date_value}
500 : $cfg->{type} eq 'timestamp' ? $ref->{timestamp_value}
501 : $cfg->{type} eq 'number' ? $form->format_amount($myconfig, $ref->{number_value} * 1, $cfg->{precision})
502 : $cfg->{type} eq 'customer' ? (SL::DB::Manager::Customer->find_by(id => 1*$ref->{number_value}) || SL::DB::Customer->new)->name
503 : $cfg->{type} eq 'vendor' ? (SL::DB::Manager::Vendor->find_by(id => 1*$ref->{number_value}) || SL::DB::Vendor->new)->name
504 : $cfg->{type} eq 'part' ? (SL::DB::Manager::Part->find_by(id => 1*$ref->{number_value}) || SL::DB::Part->new)->partnumber
505 : $cfg->{type} eq 'bool' ? ($ref->{bool_value} ? $locale->text('Yes') : $locale->text('No'))
506 : $ref->{text_value};
512 $main::lxdebug->leave_sub();
515 sub get_field_format_list {
516 $main::lxdebug->enter_sub();
521 Common::check_params(\%params, qw(module));
523 my $myconfig = \%main::myconfig;
524 my $form = $main::form;
526 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
528 my $configs = $self->get_configs(%params);
530 my $date_fields = [];
531 my $number_fields = {};
533 foreach my $config (@{ $configs }) {
534 my $name = "$params{prefix}cvar_$config->{name}";
536 if ($config->{type} eq 'date') {
537 push @{ $date_fields }, $name;
539 } elsif ($config->{type} eq 'number') {
540 $number_fields->{$config->{precision}} ||= [];
541 push @{ $number_fields->{$config->{precision}} }, $name;
545 $main::lxdebug->leave_sub();
547 return ($date_fields, $number_fields);
550 sub save_custom_variables_validity {
551 $main::lxdebug->enter_sub();
556 Common::check_params(\%params, qw(config_id trans_id validity));
558 my $myconfig = \%main::myconfig;
559 my $form = $main::form;
561 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
563 my (@where, @values);
564 add_token(\@where, \@values, col => "config_id", val => $params{config_id}, esc => \&conv_i);
565 add_token(\@where, \@values, col => "trans_id", val => $params{trans_id}, esc => \&conv_i);
567 my $where = scalar @where ? "WHERE " . join ' AND ', @where : '';
568 my $query = qq|DELETE FROM custom_variables_validity $where|;
570 do_query($form, $dbh, $query, @values);
573 qq|INSERT INTO custom_variables_validity (config_id, trans_id)
575 my $sth = prepare_query($form, $dbh, $query);
577 unless ($params{validity}) {
578 foreach my $config_id (listify($params{config_id})) {
579 foreach my $trans_id (listify($params{trans_id})) {
580 do_statement($form, $sth, $query, conv_i($config_id), conv_i($trans_id));
587 $dbh->commit() unless $params{dbh};
589 $main::lxdebug->leave_sub();
592 my %_validity_sub_module_mapping = (
593 orderitems => { table => 'orderitems', result_column => 'parts_id', trans_id_column => 'id', },
594 delivery_order_items => { table => 'delivery_order_items', result_column => 'parts_id', trans_id_column => 'id', },
595 invoice => { table => 'invoice', result_column => 'parts_id', trans_id_column => 'id', },
598 sub get_custom_variables_validity {
602 Common::check_params(\%params, qw(config_id trans_id));
604 my $myconfig = \%main::myconfig;
605 my $form = $main::form;
607 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
611 if ($params{sub_module}) {
612 my %mapping = %{ $_validity_sub_module_mapping{ $params{sub_module} } || croak("Invalid sub_module '" . $params{sub_module} . "'") };
615 FROM $mapping{table} mt
616 LEFT JOIN custom_variables_validity cvv ON (cvv.trans_id = mt.$mapping{result_column})
617 WHERE (cvv.config_id = ?)
618 AND (mt.$mapping{trans_id_column} = ?)
624 FROM custom_variables_validity
625 WHERE (config_id = ?)
631 my ($invalid) = selectfirst_array_query($form, $dbh, $query, conv_i($params{config_id}), conv_i($params{trans_id}));
636 sub custom_variables_validity_by_trans_id {
637 $main::lxdebug->enter_sub(2);
642 return sub { 0 } unless $params{trans_id};
644 my $myconfig = \%main::myconfig;
645 my $form = $main::form;
647 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
649 my $query = qq|SELECT DISTINCT config_id FROM custom_variables_validity WHERE trans_id = ?|;
651 my %invalids = map { +($_->{config_id} => 1) } selectall_hashref_query($form, $dbh, $query, $params{trans_id});
653 $main::lxdebug->leave_sub(2);
655 return sub { !$invalids{+shift} };
659 my ($self, $value, $config) = @_;
661 return $::form->parse_amount(\%::myconfig, $value) if $config->{type} eq 'number';
662 return DateTime->from_lxoffice($value) if $config->{type} eq 'date';
663 return !ref $value ? SL::DB::Manager::Customer->find_by(id => $value * 1) : $value if $config->{type} eq 'customer';
664 return !ref $value ? SL::DB::Manager::Vendor->find_by(id => $value * 1) : $value if $config->{type} eq 'vendor';
665 return !ref $value ? SL::DB::Manager::Part->find_by(id => $value * 1) : $value if $config->{type} eq 'part';
669 sub format_to_template {
670 my ($self, $value, $config) = @_;
671 # stupid template expects everything formated. except objects
672 # do not use outside of print routines for legacy templates
674 return $::form->format_amount(\%::myconfig, $value) if $config->{type} eq 'number';
675 return $value->to_lxoffice if $config->{type} eq 'date' && blessed $value && $value->can('to_lxoffice');
679 sub get_non_editable_ic_cvars {
680 $main::lxdebug->enter_sub(2);
684 Common::check_params(\%params, qw(form dbh row sub_module may_converted_from));
685 my $form = $params{form};
686 my $dbh = $params{dbh};
687 my $row = $params{row};
688 my $sub_module = $params{sub_module};
689 my $may_converted_from = $params{may_converted_from};
692 if (! $form->{"${sub_module}_id_${row}"}) {
694 foreach (@{ $may_converted_from }) {
695 if ($form->{"converted_from_${_}_id_$row"}) {
696 $cvars = CVar->get_custom_variables(dbh => $dbh,
699 trans_id => $form->{"converted_from_${_}_id_$row"},
705 # get values for CVars from master data for new items
707 $cvars = CVar->get_custom_variables(dbh => $dbh,
709 trans_id => $form->{"id_$row"},
713 # get values for CVars from custom_variables for existing items
714 $cvars = CVar->get_custom_variables(dbh => $dbh,
716 sub_module => $sub_module,
717 trans_id => $form->{"${sub_module}_id_${row}"},
720 # map only non-editable CVars to form
721 foreach (@{ $cvars }) {
722 next if $_->{flag_editable};
723 $form->{"ic_cvar_$_->{name}_$row"} = $_->{value}
726 $main::lxdebug->leave_sub(2);
735 SL::CVar.pm - Custom Variables module
739 # dealing with configs
741 my $all_configs = CVar->get_configs()
743 # dealing with custom vars
745 CVar->get_custom_variables(module => 'ic')
749 Suppose the following scenario:
751 You have a lot of parts in your database, and a set of properties configured. 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.
753 Validity is assumed. If you modify validity, you actually save B<invalidity>.
754 Invalidity is saved as a function of config_id, and the trans_id
756 In the naive way, disable an attribute for a specific id (simple)