-sub get_tuple {
- $main::lxdebug->enter_sub();
-
- my ( $self, $myconfig, $form ) = @_;
-
- my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
-
- my $dbh = $form->dbconnect($myconfig);
- my $query =
- qq|SELECT ct.*, b.id AS business, cp.* | .
- qq|FROM $cv ct | .
- qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
- qq|LEFT JOIN contacts cp ON (ct.id = cp.cp_cv_id) | .
- qq|WHERE (ct.id = ?) | .
- qq|ORDER BY cp.cp_id LIMIT 1|;
- my $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
-
- my $ref = $sth->fetchrow_hashref(NAME_lc);
-
- map { $form->{$_} = $ref->{$_} } keys %$ref;
-
- $sth->finish;
- if ( $form->{salesman_id} ) {
- my $query =
- qq|SELECT ct.name AS salesman | .
- qq|FROM $cv ct | .
- qq|WHERE ct.id = ?|;
- ($form->{salesman}) =
- selectrow_query($form, $dbh, $query, $form->{salesman_id});
- }
-
- # check if it is orphaned
- my $arap = ( $form->{db} eq 'customer' ) ? "ar" : "ap";
- $query =
- qq|SELECT a.id | .
- qq|FROM $arap a | .
- qq|JOIN $cv ct ON (a.${cv}_id = ct.id) | .
- qq|WHERE ct.id = ? | .
- qq|UNION | .
- qq|SELECT a.id | .
- qq|FROM oe a | .
- qq|JOIN $cv ct ON (a.${cv}_id = ct.id) | .
- qq|WHERE ct.id = ?|;
- my ($dummy) = selectrow_query($form, $dbh, $query, $form->{id}, $form->{id});
- $form->{status} = "orphaned" unless ($dummy);
-
- $dbh->disconnect;
-
- $main::lxdebug->leave_sub();
-}
-
-sub populate_drop_down_boxes {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form, $provided_dbh) = @_;
-
- my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect($myconfig);
-
- # get business types
- $query = qq|SELECT id, description FROM business ORDER BY id|;
- $form->{all_business} = selectall_hashref_query($form, $dbh, $query);
-
- # get shipto address
- $query =
- qq|SELECT shipto_id, shiptoname, shiptodepartment_1 | .
- qq|FROM shipto WHERE (trans_id = ?) AND (module = 'CT')|;
- $form->{SHIPTO} = selectall_hashref_query($form, $dbh, $query, $form->{id});
-
- # get contacts
- $query = qq|SELECT cp_id, cp_name FROM contacts WHERE cp_cv_id = ?|;
- $form->{CONTACTS} = selectall_hashref_query($form, $dbh, $query, $form->{id});
-
- # get languages
- $query = qq|SELECT id, description FROM language ORDER BY id|;
- $form->{languages} = selectall_hashref_query($form, $dbh, $query);
-
- # get payment terms
- $query = qq|SELECT id, description FROM payment_terms ORDER BY sortkey|;
- $form->{payment_terms} = selectall_hashref_query($form, $dbh, $query);
-
- $dbh->disconnect() unless ($provided_dbh);
-
- $main::lxdebug->leave_sub();
-}
-
-sub query_titles_and_greetings {
- $main::lxdebug->enter_sub();
-
- my ( $self, $myconfig, $form ) = @_;
- my ( %tmp, $ref );
-
- my $dbh = $form->dbconnect($myconfig);
-
- $query =
- qq|SELECT DISTINCT(cp_greeting) | .
- qq|FROM contacts | .
- qq|WHERE cp_greeting ~ '[a-zA-Z]' | .
- qq|ORDER BY cp_greeting|;
- $form->{GREETINGS} = [ selectall_array_query($form, $dbh, $query) ];
-
- $query =
- qq|SELECT DISTINCT(greeting) | .
- qq|FROM customer | .
- qq|WHERE greeting ~ '[a-zA-Z]' | .
- qq|UNION | .
- qq|SELECT DISTINCT(greeting) | .
- qq|FROM vendor | .
- qq|WHERE greeting ~ '[a-zA-Z]' | .
- qq|ORDER BY greeting|;
- my %tmp;
- map({ $tmp{$_} = 1; } selectall_array_query($form, $dbh, $query));
- $form->{COMPANY_GREETINGS} = [ sort(keys(%tmp)) ];
-
- $query =
- qq|SELECT DISTINCT(cp_title) | .
- qq|FROM contacts | .
- qq|WHERE cp_title ~ '[a-zA-Z]'|;
- $form->{TITLES} = [ selectall_array_query($form, $dbh, $query) ];
-
- $query =
- qq|SELECT DISTINCT(cp_abteilung) | .
- qq|FROM contacts | .
- qq|WHERE cp_abteilung ~ '[a-zA-Z]'|;
- $form->{DEPARTMENT} = [ selectall_array_query($form, $dbh, $query) ];
-
- $dbh->disconnect();
- $main::lxdebug->leave_sub();
-}