+ # pkg_classnum
+ # all_pkg_classnums
+ # any_pkg_status
+ if ( $params->{'pkg_classnum'} ) {
+ my @pkg_classnums = ref( $params->{'pkg_classnum'} ) ?
+ @{ $params->{'pkg_classnum'} } :
+ $params->{'pkg_classnum'};
+ @pkg_classnums = grep /^(\d+)$/, @pkg_classnums;
+
+ if ( @pkg_classnums ) {
+
+ my @pkg_where;
+ if ( $params->{'all_pkg_classnums'} ) {
+ push @pkg_where, "part_pkg.classnum = $_" foreach @pkg_classnums;
+ } else {
+ push @pkg_where,
+ 'part_pkg.classnum IN('. join(',', @pkg_classnums).')';
+ }
+ foreach (@pkg_where) {
+ my $select_pkg =
+ "SELECT 1 FROM cust_pkg JOIN part_pkg USING (pkgpart) WHERE ".
+ "cust_pkg.custnum = cust_main.custnum AND $_ ";
+ if ( not $params->{'any_pkg_status'} ) {
+ $select_pkg .= 'AND '.FS::cust_pkg->active_sql;
+ }
+ push @where, "EXISTS($select_pkg)";
+ }
+ }
+ }
+
+ ##
+ # contacts
+ ##
+ if (keys %{ $params->{'contacts'} }) {
+ my $contact_params = $params->{'contacts'};
+
+ if ($contact_params->{'contacts_firstname'} || $contact_params->{'contacts_lastname'}) {
+ my $first_query = " AND contact.first = '" . $contact_params->{'contacts_firstname'} . "'"
+ unless !$contact_params->{'contacts_firstname'};
+ my $last_query = " AND contact.last = '" . $contact_params->{'contacts_lastname'} . "'"
+ unless !$contact_params->{'contacts_lastname'};
+ push @where,
+ "EXISTS ( SELECT 1 FROM contact
+ JOIN cust_contact USING (contactnum)
+ WHERE cust_contact.custnum = cust_main.custnum
+ $first_query $last_query
+ ) ";
+ }
+
+ if ($contact_params->{'contacts_email'}) {
+ push @where,
+ "EXISTS ( SELECT 1 FROM contact_email
+ JOIN cust_contact USING (contactnum)
+ WHERE cust_contact.custnum = cust_main.custnum
+ AND contact_email.emailaddress = '" . $contact_params->{'contacts_email'} . "'
+ ) ";
+ }
+
+ if ($contact_params->{'contacts_homephone'} || $contact_params->{'contacts_workphone'} || $contact_params->{'contacts_mobilephone'}) {
+ foreach my $phone (qw( contacts_homephone contacts_workphone contacts_mobilephone )) {
+ (my $num = $contact_params->{$phone}) =~ s/\W//g;
+ if ( $num =~ /^1?(\d{3})(\d{3})(\d{4})(\d*)$/ ) { $contact_params->{$phone} = "$1$2$3"; }
+ }
+ my $home_query = " AND ( contact_phone.phonetypenum = '2' AND contact_phone.phonenum = '" . $contact_params->{'contacts_homephone'} . "' )"
+ unless !$contact_params->{'contacts_homephone'};
+ my $work_query = " AND ( contact_phone.phonetypenum = '1' AND contact_phone.phonenum = '" . $contact_params->{'contacts_workphone'} . "' )"
+ unless !$contact_params->{'contacts_workphone'};
+ my $mobile_query = " AND ( contact_phone.phonetypenum = '3' AND contact_phone.phonenum = '" . $contact_params->{'contacts_mobilephone'} . "' )"
+ unless !$contact_params->{'contacts_mobilephone'};
+ push @where,
+ "EXISTS ( SELECT 1 FROM contact_phone
+ JOIN cust_contact USING (contactnum)
+ WHERE cust_contact.custnum = cust_main.custnum
+ $home_query $work_query $mobile_query
+ ) ";
+ }
+}
+