X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;ds=sidebyside;f=FS%2FFS%2Fcust_main.pm;h=1f2fe886bc7dd65a469b9b4c774fbfe4f8dd9490;hb=1203e278e2ec38fcf1468da2e4f10862004bebeb;hp=d4909167174f7f3fdd19da00a8fda3f3ad55fb3f;hpb=3b35ccbf226efe00c94f3a72dd1c7ed64d926a7c;p=freeside.git diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm index d49091671..1f2fe886b 100644 --- a/FS/FS/cust_main.pm +++ b/FS/FS/cust_main.pm @@ -4675,9 +4675,10 @@ the error, otherwise returns false. sub charge { my $self = shift; - my ( $amount, $pkg, $comment, $taxclass, $additional, $classnum ); + my ( $amount, $quantity, $pkg, $comment, $taxclass, $additional, $classnum ); if ( ref( $_[0] ) ) { $amount = $_[0]->{amount}; + $quantity = exists($_[0]->{quantity}) ? $_[0]->{quantity} : 1; $pkg = exists($_[0]->{pkg}) ? $_[0]->{pkg} : 'One-time charge'; $comment = exists($_[0]->{comment}) ? $_[0]->{comment} : '$'. sprintf("%.2f",$amount); @@ -4686,6 +4687,7 @@ sub charge { $additional = $_[0]->{additional}; }else{ $amount = shift; + $quantity = 1; $pkg = @_ ? shift : 'One-time charge'; $comment = @_ ? shift : '$'. sprintf("%.2f",$amount); $taxclass = @_ ? shift : ''; @@ -4738,8 +4740,9 @@ sub charge { } my $cust_pkg = new FS::cust_pkg ( { - 'custnum' => $self->custnum, - 'pkgpart' => $pkgpart, + 'custnum' => $self->custnum, + 'pkgpart' => $pkgpart, + 'quantity' => $quantity, } ); $error = $cust_pkg->insert; @@ -5100,7 +5103,7 @@ sub prospect_sql { " =item active_sql Returns an SQL expression identifying active cust_main records (customers with -no active recurring packages, but otherwise unsuspended/uncancelled). +active recurring packages). =cut @@ -5112,7 +5115,7 @@ sub active_sql { " =item inactive_sql Returns an SQL expression identifying inactive cust_main records (customers with -active recurring packages). +no active recurring packages, but otherwise unsuspended/uncancelled). =cut @@ -5148,17 +5151,16 @@ sub cancelled_sql { cancel_sql(@_); } sub cancel_sql { my $recurring_sql = FS::cust_pkg->recurring_sql; - #my $recurring_sql = " - # '0' != ( select freq from part_pkg - # where cust_pkg.pkgpart = part_pkg.pkgpart ) - #"; + my $cancelled_sql = FS::cust_pkg->cancelled_sql; " - 0 < ( $select_count_pkgs ) + 0 < ( $select_count_pkgs ) + AND 0 < ( $select_count_pkgs AND $recurring_sql AND $cancelled_sql ) AND 0 = ( $select_count_pkgs AND $recurring_sql AND ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 ) ) "; + } =item uncancel_sql @@ -5279,6 +5281,187 @@ sub _money_table_where { } +=item search_sql HASHREF + +(Class method) + +Returns a qsearch hash expression to search for parameters specified in HREF. +Valid parameters are + +=over 4 + +=item agentnum + +=item status + +=item cancelled_pkgs + +bool + +=item signupdate + +listref of start date, end date + +=item payby + +listref + +=item current_balance + +listref (list returned by FS::UI::Web::parse_lt_gt($cgi, 'current_balance')) + +=item cust_fields + +=item flattened_pkgs + +bool + +=back + +=cut + +sub search_sql { + my ($class, $params) = @_; + + my $dbh = dbh; + + my @where = (); + my $orderby; + + ## + # parse agent + ## + + if ( $params->{'agentnum'} =~ /^(\d+)$/ and $1 ) { + push @where, + "cust_main.agentnum = $1"; + } + + ## + # parse status + ## + + #prospect active inactive suspended cancelled + if ( grep { $params->{'status'} eq $_ } FS::cust_main->statuses() ) { + my $method = $params->{'status'}. '_sql'; + #push @where, $class->$method(); + push @where, FS::cust_main->$method(); + } + + ## + # parse cancelled package checkbox + ## + + my $pkgwhere = ""; + + $pkgwhere .= "AND (cancel = 0 or cancel is null)" + unless $params->{'cancelled_pkgs'}; + + ## + # dates + ## + + foreach my $field (qw( signupdate )) { + + next unless exists($params->{$field}); + + my($beginning, $ending) = @{$params->{$field}}; + + push @where, + "cust_main.$field IS NOT NULL", + "cust_main.$field >= $beginning", + "cust_main.$field <= $ending"; + + $orderby ||= "ORDER BY cust_main.$field"; + + } + + ### + # payby + ### + + my @payby = grep /^([A-Z]{4})$/, @{ $params->{'payby'} }; + if ( @payby ) { + push @where, '( '. join(' OR ', map "cust_main.payby = '$_'", @payby). ' )'; + } + + ## + # amounts + ## + + #my $balance_sql = $class->balance_sql(); + my $balance_sql = FS::cust_main->balance_sql(); + + push @where, map { s/current_balance/$balance_sql/; $_ } + @{ $params->{'current_balance'} }; + + ## + # setup queries, subs, etc. for the search + ## + + $orderby ||= 'ORDER BY custnum'; + + # here is the agent virtualization + push @where, $FS::CurrentUser::CurrentUser->agentnums_sql; + + my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : ''; + + my $addl_from = 'LEFT JOIN cust_pkg USING ( custnum ) '; + + my $count_query = "SELECT COUNT(*) FROM cust_main $extra_sql"; + + my $select = join(', ', + 'cust_main.custnum', + FS::UI::Web::cust_sql_fields($params->{'cust_fields'}), + ); + + my(@extra_headers) = (); + my(@extra_fields) = (); + + if ($params->{'flattened_pkgs'}) { + + if ($dbh->{Driver}->{Name} eq 'Pg') { + + $select .= ", array_to_string(array(select pkg from cust_pkg left join part_pkg using ( pkgpart ) where cust_main.custnum = cust_pkg.custnum $pkgwhere),'|') as magic"; + + }elsif ($dbh->{Driver}->{Name} =~ /^mysql/i) { + $select .= ", GROUP_CONCAT(pkg SEPARATOR '|') as magic"; + $addl_from .= " LEFT JOIN part_pkg using ( pkgpart )"; + }else{ + warn "warning: unknown database type ". $dbh->{Driver}->{Name}. + "omitting packing information from report."; + } + + my $header_query = "SELECT COUNT(cust_pkg.custnum = cust_main.custnum) AS count FROM cust_main $addl_from $extra_sql $pkgwhere group by cust_main.custnum order by count desc limit 1"; + + my $sth = dbh->prepare($header_query) or die dbh->errstr; + $sth->execute() or die $sth->errstr; + my $headerrow = $sth->fetchrow_arrayref; + my $headercount = $headerrow ? $headerrow->[0] : 0; + while($headercount) { + unshift @extra_headers, "Package ". $headercount; + unshift @extra_fields, eval q!sub {my $c = shift; + my @a = split '\|', $c->magic; + my $p = $a[!.--$headercount. q!]; + $p; + };!; + } + + } + + my $sql_query = { + 'table' => 'cust_main', + 'select' => $select, + 'hashref' => {}, + 'extra_sql' => $extra_sql, + 'order_by' => $orderby, + 'count_query' => $count_query, + 'extra_headers' => \@extra_headers, + 'extra_fields' => \@extra_fields, + }; + +} + =item fuzzy_search FUZZY_HASHREF [ HASHREF, SELECT, EXTRA_SQL, CACHE_OBJ ] Performs a fuzzy (approximate) search and returns the matching FS::cust_main @@ -5520,7 +5703,7 @@ sub smart_search { #getting complaints searches are not returning enough unless ( @cust_main && $skip_fuzzy || $conf->exists('disable-fuzzy') ) { - #still some false laziness w/ search/cust_main.cgi + #still some false laziness w/search_sql (was search/cust_main.cgi) #substring