Re: Customer Code ?

--Boundary-00=_10wdEmXHtOqv0W8
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On Thursday 25 May 2006 20:50, David J wrote:
> Hi Stuart
> Thx for the suggestion.
> Is there some way we can select customers by their CODE,
> rather than by name, or part of name ? Isn't that why
> customers have codes ?

I've implemented this for a client of mine.  Replace sub get_name in 
$BASE/SL/Form.pm with this code, and in that first field, where you can put 
the customer or vendor name, you can also put a 'Customer Number' (I presume 
that's what you mean by code) -- The same rules of matching apply, if your 
search string is a substring of another customer or name, you'll have to 
click it off the results.

I'm not sure if there's a standard way to make changes in the .pm survive 
upgrades though (Form_custom.pm doesn't seem to work) ...

Jason

--Boundary-00=_10wdEmXHtOqv0W8
Content-Type: text/plain;
  charset="iso-8859-1";
  name="search-by-code.txt"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
	filename="search-by-code.txt"

#JAY: altered  query so that
# we can search on Customer or vendor Number as well as name

# this sub gets the id and name from $table
sub get_name {
  my ($self, $myconfig, $table, $transdate) = @_;

  # connect to database
  my $dbh = $self->dbconnect($myconfig);
  
  my $where;
  if ($transdate) {
    $where = qq|AND (startdate IS NULL OR startdate <= '$transdate')
                AND (enddate IS NULL OR enddate >= '$transdate')|;
  }
 
  my $name = $self->{$table};
  $name =~ s/\s+$//g;
  $name = $self->like(lc $name);
  
  # this is typically 'customer' or 'vendor'.
  my $codeField = $table.'number';

  my $query = qq|SELECT *
                 FROM $table
		 WHERE 
		   lower(name) LIKE '$name'
		   OR lower($codeField) LIKE '$name'
		 $where
		 ORDER BY name|;

  my $sth = $dbh->prepare($query);

  $sth->execute || $self->dberror($query);

  my $i = 0;
  @{ $self->{name_list} } = ();
  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
    push(@{ $self->{name_list} }, $ref);
    $i++;
  }
  $sth->finish;
  $dbh->disconnect;

  $i;
  
}

--Boundary-00=_10wdEmXHtOqv0W8
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline


--Boundary-00=_10wdEmXHtOqv0W8
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

_______________________________________________
sql-ledger-users mailing list
sql-ledger-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
https://lists.sourceforge.net/lists/listinfo/sql-ledger-users

--Boundary-00=_10wdEmXHtOqv0W8--