+=item states_hash COUNTRY
+
+Returns a list of key/value pairs containing state (or other sub-country
+division) abbriviations and names.
+
+=cut
+
+use FS::Record qw(qsearch);
+use Locale::SubCountry;
+
+sub states_hash {
+ my($country) = @_;
+
+ my @states =
+# sort
+ map { s/[\n\r]//g; $_; }
+ map { $_->state; }
+ qsearch({
+ 'select' => 'state',
+ 'table' => 'cust_main_county',
+ 'hashref' => { 'country' => $country },
+ 'extra_sql' => 'GROUP BY state',
+ });
+
+ #it could throw a fatal "Invalid country code" error (for example "AX")
+ my $subcountry = eval { new Locale::SubCountry($country) }
+ or return ( '', '(n/a)' );
+
+ #"i see your schwartz is as big as mine!"
+ map { ( $_->[0] => $_->[1] ) }
+ sort { $a->[1] cmp $b->[1] }
+ map { [ $_ => state_label($_, $subcountry) ] }
+ @states;
+}
+
+=item counties STATE COUNTRY
+
+Returns a list of counties for this state and country.
+
+=cut
+
+sub counties {
+ my( $state, $country ) = @_;
+
+ sort map { s/[\n\r]//g; $_; }
+ map { $_->county }
+ qsearch({
+ 'select' => 'DISTINCT county',
+ 'table' => 'cust_main_county',
+ 'hashref' => { 'state' => $state,
+ 'country' => $country,
+ },
+ });
+}
+
+=item state_label STATE COUNTRY_OR_LOCALE_SUBCOUNRY_OBJECT
+
+=cut
+
+sub state_label {
+ my( $state, $country ) = @_;
+
+ unless ( ref($country) ) {
+ $country = eval { new Locale::SubCountry($country) }
+ or return'(n/a)';
+
+ }
+
+ # US kludge to avoid changing existing behaviour
+ # also we actually *use* the abbriviations...
+ my $full_name = $country->country_code eq 'US'
+ ? ''
+ : $country->full_name($state);
+
+ $full_name = '' if $full_name eq 'unknown';
+ $full_name =~ s/\(see also.*\)\s*$//;
+ $full_name .= " ($state)" if $full_name;
+
+ $full_name || $state || '(n/a)';
+
+}
+
+=item card_types
+
+Returns a hash reference of the accepted credit card types. Keys are shorter
+identifiers and values are the longer strings used by the system (see
+L<Business::CreditCard>).
+
+=cut
+
+#$conf from above
+
+sub card_types {
+ my $conf = new FS::Conf;
+
+ my %card_types = (
+ #displayname #value (Business::CreditCard)
+ "VISA" => "VISA card",
+ "MasterCard" => "MasterCard",
+ "Discover" => "Discover card",
+ "American Express" => "American Express card",
+ "Diner's Club/Carte Blanche" => "Diner's Club/Carte Blanche",
+ "enRoute" => "enRoute",
+ "JCB" => "JCB",
+ "BankCard" => "BankCard",
+ "Switch" => "Switch",
+ "Solo" => "Solo",
+ );
+ my @conf_card_types = grep { ! /^\s*$/ } $conf->config('card-types');
+ if ( @conf_card_types ) {
+ #perhaps the hash is backwards for this, but this way works better for
+ #usage in selfservice
+ %card_types = map { $_ => $card_types{$_} }
+ grep {
+ my $d = $_;
+ grep { $card_types{$d} eq $_ } @conf_card_types
+ }
+ keys %card_types;
+ }
+
+ \%card_types;
+}
+
+=item prune_applications OPTION_HASH
+
+Removes applications of credits to refunds in the event that the database
+is corrupt and either the credits or refunds are missing (see
+L<FS::cust_credit>, L<FS::cust_refund>, and L<FS::cust_credit_refund>).
+If the OPTION_HASH contains the element 'dry_run' then a report of
+affected records is returned rather than actually deleting the records.
+
+=cut
+
+sub prune_applications {
+ my $options = shift;
+ my $dbh = dbh
+
+ local $DEBUG = 1 if exists($options->{debug});
+ my $ccr = <<EOW;
+ WHERE
+ 0 = (select count(*) from cust_credit
+ where cust_credit_refund.crednum = cust_credit.crednum)
+ or
+ 0 = (select count(*) from cust_refund
+ where cust_credit_refund.refundnum = cust_refund.refundnum)
+EOW
+ my $ccb = <<EOW;
+ WHERE
+ 0 = (select count(*) from cust_credit
+ where cust_credit_bill.crednum = cust_credit.crednum)
+ or
+ 0 = (select count(*) from cust_bill
+ where cust_credit_bill.invnum = cust_bill.invnum)
+EOW
+ my $cbp = <<EOW;
+ WHERE
+ 0 = (select count(*) from cust_bill
+ where cust_bill_pay.invnum = cust_bill.invnum)
+ or
+ 0 = (select count(*) from cust_pay
+ where cust_bill_pay.paynum = cust_pay.paynum)
+EOW
+ my $cpr = <<EOW;
+ WHERE
+ 0 = (select count(*) from cust_pay
+ where cust_pay_refund.paynum = cust_pay.paynum)
+ or
+ 0 = (select count(*) from cust_refund
+ where cust_pay_refund.refundnum = cust_refund.refundnum)
+EOW
+
+ my %strays = (
+ 'cust_credit_refund' => { clause => $ccr,
+ link1 => 'crednum',
+ link2 => 'refundnum',
+ },
+# 'cust_credit_bill' => { clause => $ccb,
+# link1 => 'crednum',
+# link2 => 'refundnum',
+# },
+# 'cust_bill_pay' => { clause => $cbp,
+# link1 => 'crednum',
+# link2 => 'refundnum',
+# },
+# 'cust_pay_refund' => { clause => $cpr,
+# link1 => 'crednum',
+# link2 => 'refundnum',
+# },
+ );
+
+ if ( exists($options->{dry_run}) ) {
+ my @response = ();
+ foreach my $table (keys %strays) {
+ my $clause = $strays{$table}->{clause};
+ my $link1 = $strays{$table}->{link1};
+ my $link2 = $strays{$table}->{link2};
+ my @rec = qsearch($table, {}, '', $clause);
+ my $keyname = $rec[0]->primary_key if $rec[0];
+ foreach (@rec) {
+ push @response, "$table " .$_->$keyname . " claims attachment to ".
+ "$link1 " . $_->$link1 . " and $link2 " . $_->$link2 . "\n";
+ }
+ }
+ return (@response);
+ } else {
+ foreach (keys %strays) {
+ my $statement = "DELETE FROM $_ " . $strays{$_}->{clause};
+ warn $statement if $DEBUG;
+ my $sth = $dbh->prepare($statement)
+ or die $dbh->errstr;
+ $sth->execute
+ or die $sth->errstr;
+ }
+ return ();
+ }
+}
+
+=back
+