-sub get_vc {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
-
- my %arap = (invoice => 'ar',
- packing_list => 'ar',
- sales_order => 'oe',
- purchase_order => 'oe',
- sales_quotation => 'oe',
- request_quotation => 'oe',
- check => 'ap',
- receipt => 'ar');
-
- $query = qq|SELECT count(*)
- FROM (SELECT DISTINCT ON (vc.id) vc.id
- FROM $form->{vc} vc, $arap{$form->{type}} a, status s
- WHERE a.$form->{vc}_id = vc.id
- AND s.trans_id = a.id
- AND s.formname = '$form->{type}'
- AND s.spoolfile IS NOT NULL) AS total|;
-
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
- my ($count) = $sth->fetchrow_array;
- $sth->finish;
-
- # build selection list
- if ($count < $myconfig->{vclimit}) {
- $query = qq|SELECT DISTINCT ON (vc.id) vc.id, vc.name
- FROM $form->{vc} vc, $arap{$form->{type}} a, status s
- WHERE a.$form->{vc}_id = vc.id
- AND s.trans_id = a.id
- AND s.formname = '$form->{type}'
- AND s.spoolfile IS NOT NULL|;
- }
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- push @{ $form->{"all_$form->{vc}"} }, $ref;
- }