X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Felements%2Fcust_pay_or_refund.html;h=bd0e9e33cd82e2935365349b8967fd90a1ce191f;hb=32e9b8146661a5bc6b340b09dacc8a70813555f6;hp=c6617b14267a631eb9485a2a176b143357ca9314;hpb=7ecfcfeda7f249f89534f01da509a51a6c44b082;p=freeside.git

diff --git a/httemplate/search/elements/cust_pay_or_refund.html b/httemplate/search/elements/cust_pay_or_refund.html
index c6617b142..bd0e9e33c 100755
--- a/httemplate/search/elements/cust_pay_or_refund.html
+++ b/httemplate/search/elements/cust_pay_or_refund.html
@@ -58,6 +58,7 @@ Examples:
 
                 'group_column'   => 'payby',
                 'group_label'    => 'payby_name',
+                'amount_field'   => $amount_field,
                 'subtotal'       => { $opt{amount_field} => "sum($amount_field)" },
                 'subtotal_row'   => [ 'Subtotal',
                                       sub { sprintf($money, $_[0]->$amount_field) },
@@ -67,6 +68,16 @@ Examples:
                                     ],
                 'show_combined'  => 1,
 &>
+<%shared>
+# canonicalize the payby subtype string to an SQL-quoted list
+my %cardtype_of = (
+  'VisaMC'    => q['VISA card', 'MasterCard'],
+  'Amex'      => q['American Express card'],
+  'Discover'  => q['Discover card'],
+  'Maestro'   => q['Switch', 'Solo', 'Laser'],
+  'Tokenized' => q['Tokenized'],
+);  
+</%shared>
 <%init>
 
 my %opt = @_;
@@ -77,7 +88,7 @@ my $conf = FS::Conf->new;
 my $money = ($conf->config('money_char') || '$') . '%.2f';
 
 die "access denied"
-  unless $curuser->access_right('Financial reports');
+  unless $curuser->access_right('Basic payment and refund reports');
 
 my $table = $opt{'table'} || 'cust_'.$opt{'thing'};
 
@@ -91,29 +102,30 @@ my $title = '';
 $title = 'Unapplied ' if $unapplied;
 $title .= "\u$name_singular Search Results";
 
-my $link = '';
-if (    ( $curuser->access_right('View invoices') #remove in 2.5 (2.7?)
-          || ($curuser->access_right('View payments') && $table =~ /^cust_pay/)
-          || ($curuser->access_right('View refunds') && $table eq 'cust_refund')
-        )
-     && ! $opt{'disable_link'}
-   )
-{
-
-  my $key;
-  my $q = '';
-  if ( $table eq 'cust_pay_void' ) {
-    $key = 'paynum';
-    $q .= 'void=1;';
-  } elsif ( $table eq /^cust_(\w+)$/ ) {
-    $key = $1.'num';
-  }
-  
-  if ( $key ) {
-    $q .= "$key=";
-    $link = [ "${p}view/$table.html?$q", $key ]
-  }
-}
+###NOT USED???
+#my $link = '';
+#if (    ( $curuser->access_right('View invoices') #remove in 2.5 (2.7?)
+#          || ($curuser->access_right('View payments') && $table =~ /^cust_pay/)
+#          || ($curuser->access_right('View refunds') && $table eq 'cust_refund')
+#        )
+#     && ! $opt{'disable_link'}
+#   )
+#{
+#
+#  my $key;
+#  my $q = '';
+#  if ( $table eq 'cust_pay_void' ) {
+#    $key = 'paynum';
+#    $q .= 'void=1;';
+#  } elsif ( $table eq /^cust_(\w+)$/ ) {
+#    $key = $1.'num';
+#  }
+#  
+#  if ( $key ) {
+#    $q .= "$key=";
+#    $link = [ "${p}view/$table.html?$q", $key ]
+#  }
+#}
 
 my $cust_link = sub {
   my $cust_thing = shift;
@@ -166,12 +178,18 @@ if ( $opt{'pre_header'} ) {
   push @sort_fields, @{ $opt{'pre_fields'} };
 }
 
-my $sub_receipt = sub {
+my $sub_receipt = $opt{'disable_link'} ? '' : sub {
   my $obj = shift;
   my $objnum = $obj->primary_key . '=' . $obj->get($obj->primary_key);
+  my $table = $obj->table;
+  my $void = '';
+  if ($table eq 'cust_pay_void') {
+    $table = 'cust_pay';
+    $void = ';void=1';
+  }
 
   include('/elements/popup_link_onclick.html',
-    'action'  => $p.'view/cust_pay.html?link=popup;'.$objnum,
+    'action'  => $p.'view/'.$table.'.html?link=popup;'.$objnum.$void,
     'actionlabel' => emt('Payment Receipt'),
   );
 };
@@ -187,14 +205,24 @@ push @fields, 'payby_payinfo_pretty',
 push @link_onclicks, $sub_receipt, '';
 push @sort_fields, 'paysort', $amount_field;
 
+# 4.x, to remain functional while the upgrade is running...
+my $sub_guess_cardtype = sub {
+  my $row = shift;
+  $row->paycardtype || (
+    ($row->payby eq 'CARD'  && $row->paymask !~ /N\/A/)
+    ? cardtype($row->paymask)
+    : ''
+  )
+};
+
 if ($opt{'show_card_type'}) {
   push @header, emt('Card Type');
   $align .= 'r';
   push @links, '';
-  push @fields, sub { 
-    (($_[0]->payby eq 'CARD') && ($_[0]->paymask !~ /N\/A/)) ? cardtype($_[0]->paymask) : ''
-  };
-  push @sort_fields, '';
+  push @fields, $sub_guess_cardtype;
+  # worst case, paycardtype isn't filled in yet and sorting by that column
+  # does nothing.
+  push @sort_fields, 'paycardtype';
 }
 
 if ( $unapplied ) {
@@ -211,7 +239,7 @@ push @links, '';
 push @fields, sub { time2str('%b %d %Y', shift->_date ) };
 push @sort_fields, '_date';
 
-if ($opt{'show_order_number'}) {
+if ($cgi->param('show_order_number')) {
   push @header, emt('Order Number');
   $align .= 'r';
   push @links, '';
@@ -305,28 +333,36 @@ if ( $cgi->param('magic') ) {
     if ( $cgi->param('payby') ) {
 
       my @all_payby_search = ();
-      foreach my $payby ( $cgi->param('payby') ) {
+      foreach my $payby_string ( $cgi->param('payby') ) {
 
-        $payby =~
-          /^(CARD|CHEK|BILL|CASH|PPAL|APPL|ANRD|PREP|WIRE|WEST|IDTP|EDI|MCRD|MCHK)(-(VisaMC|Amex|Discover|Maestro|Tokenized))?$/
-            or die "illegal payby $payby";
+        my $payby_search;
 
-        my $payby_search = "$table.payby = '$1'";
+        my ($payby, $subtype) = split('-', $payby_string);
+        # make sure it exists and is a transaction type
+        if ( FS::payby->payment_payby2longname($payby) ) {
+          $payby_search = "$table.payby = " . dbh->quote($payby);
+        } else {
+          die "illegal payby $payby_string";
+        }
 
-        if ( $3 ) {
+        if ( $subtype ) {
 
-          my $cardtype = $3;
+          my $in_cardtype = $cardtype_of{$subtype}
+            or die "unknown card type $subtype";
+          # will complete this phrase after alt_search
+          $payby_search .= " AND ( $table.paycardtype IN($in_cardtype)";
 
+          # 4.x transitional, to avoid breaking things while we upgrade
           my $similar_to = dbh->{Driver}->{Name} =~ /^mysql/i
                              ? 'REGEXP' #doesn't behave exactly the same, but
                                         #should work for our patterns
                              : 'SIMILAR TO';
 
-          my $search;
-          if ( $cardtype eq 'VisaMC' ) {
+          my $alt_search;
+          if ( $subtype eq 'VisaMC' ) {
 
             #avoid posix regexes for portability
-            $search =
+            $alt_search =
               # Visa
               " ( (     substring($table.payinfo from 1 for 1) = '4'     ".
               #   is not Switch
@@ -354,58 +390,73 @@ if ( $cgi->param('magic') ) {
               "   OR substring($table.payinfo from 1 for 2) $similar_to '2[3-6]' ".
               "   OR substring($table.payinfo from 1 for 3) $similar_to '27[0-1]' ".
               "   OR substring($table.payinfo from 1 for 4) = '2720' ".
+              "   OR substring($table.payinfo from 1 for 3) = '2[2-7]x' ".
               " ) ";
 
-          } elsif ( $cardtype eq 'Amex' ) {
+          } elsif ( $subtype eq 'Amex' ) {
 
-            $search =
+            $alt_search =
               " (    substring($table.payinfo from 1 for 2 ) = '34' ".
               "   OR substring($table.payinfo from 1 for 2 ) = '37' ".
               " ) ";
 
-          } elsif ( $cardtype eq 'Discover' ) {
+          } elsif ( $subtype eq 'Discover' ) {
 
             my $country = $conf->config('countrydefault') || 'US';
 
-            $search =
+            $alt_search =
               " (    substring($table.payinfo from 1 for 4 ) = '6011'  ".
+              "   OR substring($table.payinfo from 1 for 3 ) = '60x'   ".
               "   OR substring($table.payinfo from 1 for 2 ) = '65'    ".
-              "   OR substring($table.payinfo from 1 for 3 ) = '300'   ". # diner's 300-305
+
+              # diner's 300-305 / 3095
+              "   OR substring($table.payinfo from 1 for 3 ) = '300'   ".
               "   OR substring($table.payinfo from 1 for 3 ) = '301'   ".
               "   OR substring($table.payinfo from 1 for 3 ) = '302'   ".
               "   OR substring($table.payinfo from 1 for 3 ) = '303'   ".
               "   OR substring($table.payinfo from 1 for 3 ) = '304'   ".
               "   OR substring($table.payinfo from 1 for 3 ) = '305'   ".
-              "   OR substring($table.payinfo from 1 for 4 ) = '3095'  ". # diner's 3095
-              "   OR substring($table.payinfo from 1 for 2 ) = '36'    ". # diner's 36, 38, 39
+              "   OR substring($table.payinfo from 1 for 4 ) = '3095'  ".
+              "   OR substring($table.payinfo from 1 for 3 ) = '30x'   ".
+
+              # diner's 36, 38, 39
+              "   OR substring($table.payinfo from 1 for 2 ) = '36'    ".
               "   OR substring($table.payinfo from 1 for 2 ) = '38'    ".
               "   OR substring($table.payinfo from 1 for 2 ) = '39'    ".
+
               "   OR substring($table.payinfo from 1 for 3 ) = '644'   ".
               "   OR substring($table.payinfo from 1 for 3 ) = '645'   ".
               "   OR substring($table.payinfo from 1 for 3 ) = '646'   ".
               "   OR substring($table.payinfo from 1 for 3 ) = '647'   ".
               "   OR substring($table.payinfo from 1 for 3 ) = '648'   ".
               "   OR substring($table.payinfo from 1 for 3 ) = '649'   ".
+              "   OR substring($table.payinfo from 1 for 3 ) = '64x'   ".
+
+              # JCB cards in the 3528-3589 range identified as Discover inside US & territories (NOT Canada)
               ( $country =~ /^(US|PR|VI|MP|PW|GU)$/
-               ?" OR substring($table.payinfo from 1 for 4 ) = '3528'  ". # JCB cards in the 3528-3589 range identified as Discover inside US & territories (NOT Canada)
+               ?" OR substring($table.payinfo from 1 for 4 ) = '3528'  ".
                 " OR substring($table.payinfo from 1 for 4 ) = '3529'  ".
                 " OR substring($table.payinfo from 1 for 3 ) = '353'   ".
                 " OR substring($table.payinfo from 1 for 3 ) = '354'   ".
                 " OR substring($table.payinfo from 1 for 3 ) = '355'   ".
                 " OR substring($table.payinfo from 1 for 3 ) = '356'   ".
                 " OR substring($table.payinfo from 1 for 3 ) = '357'   ".
-                " OR substring($table.payinfo from 1 for 3 ) = '358'   "
+                " OR substring($table.payinfo from 1 for 3 ) = '358'   ".
+                " OR substring($table.payinfo from 1 for 3 ) = '35x'   "
                :""
               ).
+
+              #China Union Pay processed as Discover in US, Mexico and Caribbean
               ( $country =~ /^(US|MX|AI|AG|AW|BS|BB|BM|BQ|VG|KY|CW|DM|DO|GD|GP|JM|MQ|MS|BL|KN|LC|VC|MF|SX|TT|TC)$/
-               ?" OR substring($table.payinfo from 1 for 3 ) $similar_to '62[24-68]'   " #China Union Pay processed as Discover outside CN
+               ?" OR substring($table.payinfo from 1 for 3 ) $similar_to '62[24-68x]'   "
                :""
               ).
+
               " ) ";
 
-          } elsif ( $cardtype eq 'Maestro' ) {
+          } elsif ( $subtype eq 'Maestro' ) {
 
-            $search =
+            $alt_search =
               " (    substring($table.payinfo from 1 for 2 ) = '63'     ".
               "   OR substring($table.payinfo from 1 for 2 ) = '67'     ".
               "   OR substring($table.payinfo from 1 for 6 ) = '564182' ".
@@ -422,20 +473,27 @@ if ( $cgi->param('magic') ) {
               "      $similar_to '49118[1-2]'                             ".
               " ) ";
 
-          } elsif ( $cardtype eq 'Tokenized' ) {
+          } elsif ( $subtype eq 'Tokenized' ) {
+
+              $alt_search = " substring($table.payinfo from 1 for 2 ) = '99' ";
 
-            $search = " substring($table.payinfo from 1 for 2 ) = '99' ";
+          } else { # shouldn't happen if there's a $subtype
 
-          } else {
-            die "unknown card type $cardtype";
+            $alt_search = 'TRUE';
+ 
           }
 
-          my $masksearch = $search;
-          $masksearch =~ s/$table\.payinfo/$table.paymask/gi;
+          # alt_search is already paren'd if it contains OR.
+          # now make sure it works if they're encrypted.
+          my $masksearch = $alt_search;
+          $masksearch =~ s/$table.payinfo/$table.paymask/g;
+          $alt_search = "( ($table.paymask IS NOT NULL AND $masksearch)
+                          OR $alt_search )";
 
-          $payby_search = "( $payby_search AND ( $search OR ( $table.paymask IS NOT NULL AND $masksearch ) ) )";
+          # close paren here
+          $payby_search .= " OR ( $table.paycardtype IS NULL AND $alt_search ) )";
 
-        }
+        } # if $subtype
 
         push @all_payby_search, $payby_search;
 
@@ -445,6 +503,13 @@ if ( $cgi->param('magic') ) {
 
     }
 
+    if ( $cgi->param('paymask') ) {
+      $cgi->param('paymask') =~ /^\s*(\d+)\s*$/
+        or die "illegal paymask ". $cgi->param('paymask');
+      my $regexp = regexp_sql();
+      push @search, "$table.paymask $regexp '$1\$'";
+    } 
+
     if ( $cgi->param('payinfo') ) {
       $cgi->param('payinfo') =~ /^\s*(\d+)\s*$/
         or die "illegal payinfo ". $cgi->param('payinfo');
@@ -552,6 +617,19 @@ if ( $cgi->param('magic') ) {
     }
   }
 
+  #check for customer tags
+  my @tags;
+  foreach my $p ($cgi->param) {
+    if ($p =~ /^tagnum(\d+)/ && $1) {
+      $addl_from .= " LEFT JOIN cust_tag ON (cust_tag.custnum = cust_pay.custnum)" unless @tags;
+      push @tags, $1;
+    }
+  }
+  if (@tags) {
+    my $tags = join(',',@tags);
+    push @search, "cust_tag.tagnum in ($tags)";
+  }
+
   if ( $cgi->param('tax_names') ) {
     if ( dbh->{Driver}->{Name} =~ /^Pg/i ) {
 
@@ -575,6 +653,113 @@ if ( $cgi->param('magic') ) {
     }
   }
 
+  #customer location... total false laziness w/cust_main/Search.pm
+
+  my $current = '';
+  unless ( $cgi->param('location_history') ) {
+    $current = '
+      AND (    cust_location.locationnum IN ( cust_main.bill_locationnum,
+                                              cust_main.ship_locationnum
+                                            )
+            OR cust_location.locationnum IN (
+                 SELECT locationnum FROM cust_pkg
+                  WHERE cust_pkg.custnum = cust_main.custnum
+                    AND locationnum IS NOT NULL
+                    AND '. FS::cust_pkg->ncancelled_recurring_sql.'
+               )
+          )';
+  }
+
+
+  ##
+  # address
+  ##
+  if ( $cgi->param('address') ) {
+    my @values = $cgi->param('address');
+    my @orwhere;
+    foreach (grep /\S/, @values) {
+      my $address = dbh->quote('%'. lc($_). '%');
+      push @orwhere,
+        "LOWER(cust_location.address1) LIKE $address",
+        "LOWER(cust_location.address2) LIKE $address";
+    }
+    if (@orwhere) {
+      push @search, "EXISTS(
+        SELECT 1 FROM cust_location 
+        WHERE cust_location.custnum = cust_main.custnum
+          AND (".join(' OR ',@orwhere).")
+          $current
+        )";
+    }
+  }
+
+  ##
+  # city
+  ##
+  if ( $cgi->param('city') =~ /\S/ ) {
+    my $city = dbh->quote($cgi->param('city'));
+    push @search, "EXISTS(
+      SELECT 1 FROM cust_location
+      WHERE cust_location.custnum = cust_main.custnum
+        AND cust_location.city = $city
+        $current
+    )";
+  }
+
+  ##
+  # county
+  ##
+  if ( $cgi->param('county') =~ /\S/ ) {
+    my $county = dbh->quote($cgi->param('county'));
+    push @search, "EXISTS(
+      SELECT 1 FROM cust_location
+      WHERE cust_location.custnum = cust_main.custnum
+        AND cust_location.county = $county
+        $current
+    )";
+  }
+
+  ##
+  # state
+  ##
+  if ( $cgi->param('state') =~ /\S/ ) {
+    my $state = dbh->quote($cgi->param('state'));
+    push @search, "EXISTS(
+      SELECT 1 FROM cust_location
+      WHERE cust_location.custnum = cust_main.custnum
+        AND cust_location.state = $state
+        $current
+    )";
+  }
+
+  ##
+  # zipcode
+  ##
+  if ( $cgi->param('zip') =~ /\S/ ) {
+    my $zip = dbh->quote($cgi->param('zip') . '%');
+    push @search, "EXISTS(
+      SELECT 1 FROM cust_location
+      WHERE cust_location.custnum = cust_main.custnum
+        AND cust_location.zip LIKE $zip
+        $current
+    )";
+  }
+
+  ##
+  # country
+  ##
+  if ( $cgi->param('country') =~ /^(\w\w)$/ ) {
+    my $country = uc($1);
+    push @search, "EXISTS(
+      SELECT 1 FROM cust_location
+      WHERE cust_location.custnum = cust_main.custnum
+        AND cust_location.country = '$country'
+        $current
+    )";
+  }
+
+  #end of false laziness w/cust_main/Search.pm
+
   my $search = ' WHERE '. join(' AND ', @search);
 
   $count_query = "SELECT COUNT(*), SUM($table.$amount_field) ";