-=head1 CLASS METHODS
-
-=item in_county_sql OPTIONS
-
-Returns an SQL expression to test membership in a cust_main_county
-geographic area. By default, this requires district, city, county,
-state, and country to match exactly. Pass "ornull => 1" to allow
-partial matches where some fields are NULL in the cust_main_county
-record but not in the location.
-
-Pass "param => 1" to receive a parameterized expression (rather than
-one that requires a join to cust_main_county) and a list of parameter
-names in order.
-
-=cut
-
-sub in_county_sql {
- # replaces FS::cust_pkg::location_sql
- my ($class, %opt) = @_;
- my $ornull = $opt{ornull} ? ' OR ? IS NULL' : '';
- my $x = $ornull ? 3 : 2;
- my @fields = (('district') x 3,
- ('city') x 3,
- ('county') x $x,
- ('state') x $x,
- 'country');
-
- my $text = (driver_name =~ /^mysql/i) ? 'char' : 'text';
-
- my @where = (
- "cust_location.district = ? OR ? = '' OR CAST(? AS $text) IS NULL",
- "cust_location.city = ? OR ? = '' OR CAST(? AS $text) IS NULL",
- "cust_location.county = ? OR (? = '' AND cust_location.county IS NULL) $ornull",
- "cust_location.state = ? OR (? = '' AND cust_location.state IS NULL ) $ornull",
- "cust_location.country = ?"
- );
- my $sql = join(' AND ', map "($_)\n", @where);
- if ( $opt{param} ) {
- return $sql, @fields;
- }
- else {
- # do the substitution here
- foreach (@fields) {
- $sql =~ s/\?/cust_main_county.$_/;
- $sql =~ s/cust_main_county.$_ = ''/cust_main_county.$_ IS NULL/;
- }
- return $sql;
- }
-}
-
-=back
-