+=item ucfirst_cust_status
+
+=item ucfirst_status
+
+Returns the status with the first character capitalized.
+
+=cut
+
+sub ucfirst_status { shift->ucfirst_cust_status(@_); }
+
+sub ucfirst_cust_status {
+ my $self = shift;
+ ucfirst($self->cust_status);
+}
+
+=item statuscolor
+
+Returns a hex triplet color string for this customer's status.
+
+=cut
+
+use vars qw(%statuscolor);
+tie my %statuscolor, 'Tie::IxHash',
+ 'prospect' => '7e0079', #'000000', #black? naw, purple
+ 'active' => '00CC00', #green
+ 'inactive' => '0000CC', #blue
+ 'suspended' => 'FF9900', #yellow
+ 'cancelled' => 'FF0000', #red
+;
+
+sub statuscolor { shift->cust_statuscolor(@_); }
+
+sub cust_statuscolor {
+ my $self = shift;
+ $statuscolor{$self->cust_status};
+}
+
+=item tickets
+
+Returns an array of hashes representing the customer's RT tickets.
+
+=cut
+
+sub tickets {
+ my $self = shift;
+
+ my $num = $conf->config('cust_main-max_tickets') || 10;
+ my @tickets = ();
+
+ unless ( $conf->config('ticket_system-custom_priority_field') ) {
+
+ @tickets = @{ FS::TicketSystem->customer_tickets($self->custnum, $num) };
+
+ } else {
+
+ foreach my $priority (
+ $conf->config('ticket_system-custom_priority_field-values'), ''
+ ) {
+ last if scalar(@tickets) >= $num;
+ push @tickets,
+ @{ FS::TicketSystem->customer_tickets( $self->custnum,
+ $num - scalar(@tickets),
+ $priority,
+ )
+ };
+ }
+ }
+ (@tickets);
+}
+
+# Return services representing svc_accts in customer support packages
+sub support_services {
+ my $self = shift;
+ my %packages = map { $_ => 1 } $conf->config('support_packages');
+
+ grep { $_->pkg_svc && $_->pkg_svc->primary_svc eq 'Y' }
+ grep { $_->part_svc->svcdb eq 'svc_acct' }
+ map { $_->cust_svc }
+ grep { exists $packages{ $_->pkgpart } }
+ $self->ncancelled_pkgs;
+
+}
+
+=back
+
+=head1 CLASS METHODS
+
+=over 4
+
+=item statuses
+
+Class method that returns the list of possible status strings for customers
+(see L<the status method|/status>). For example:
+
+ @statuses = FS::cust_main->statuses();
+
+=cut
+
+sub statuses {
+ #my $self = shift; #could be class...
+ keys %statuscolor;
+}
+
+=item prospect_sql
+
+Returns an SQL expression identifying prospective cust_main records (customers
+with no packages ever ordered)
+
+=cut
+
+use vars qw($select_count_pkgs);
+$select_count_pkgs =
+ "SELECT COUNT(*) FROM cust_pkg
+ WHERE cust_pkg.custnum = cust_main.custnum";
+
+sub select_count_pkgs_sql {
+ $select_count_pkgs;
+}
+
+sub prospect_sql { "
+ 0 = ( $select_count_pkgs )
+"; }
+
+=item active_sql
+
+Returns an SQL expression identifying active cust_main records (customers with
+active recurring packages).
+
+=cut
+
+sub active_sql { "
+ 0 < ( $select_count_pkgs AND ". FS::cust_pkg->active_sql. "
+ )
+"; }
+
+=item inactive_sql
+
+Returns an SQL expression identifying inactive cust_main records (customers with
+no active recurring packages, but otherwise unsuspended/uncancelled).
+
+=cut
+
+sub inactive_sql { "
+ 0 = ( $select_count_pkgs AND ". FS::cust_pkg->active_sql. " )
+ AND
+ 0 < ( $select_count_pkgs AND ". FS::cust_pkg->inactive_sql. " )
+"; }
+
+=item susp_sql
+=item suspended_sql
+
+Returns an SQL expression identifying suspended cust_main records.
+
+=cut
+
+
+sub suspended_sql { susp_sql(@_); }
+sub susp_sql { "
+ 0 < ( $select_count_pkgs AND ". FS::cust_pkg->suspended_sql. " )
+ AND
+ 0 = ( $select_count_pkgs AND ". FS::cust_pkg->active_sql. " )
+"; }
+
+=item cancel_sql
+=item cancelled_sql
+
+Returns an SQL expression identifying cancelled cust_main records.
+
+=cut
+
+sub cancelled_sql { cancel_sql(@_); }
+sub cancel_sql {
+
+ my $recurring_sql = FS::cust_pkg->recurring_sql;
+ my $cancelled_sql = FS::cust_pkg->cancelled_sql;
+
+ "
+ 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
+=item uncancelled_sql
+
+Returns an SQL expression identifying un-cancelled cust_main records.
+
+=cut
+
+sub uncancelled_sql { uncancel_sql(@_); }
+sub uncancel_sql { "
+ ( 0 < ( $select_count_pkgs
+ AND ( cust_pkg.cancel IS NULL
+ OR cust_pkg.cancel = 0
+ )
+ )
+ OR 0 = ( $select_count_pkgs )
+ )
+"; }
+
+=item balance_sql
+
+Returns an SQL fragment to retreive the balance.
+
+=cut
+
+sub balance_sql { "
+ ( SELECT COALESCE( SUM(charged), 0 ) FROM cust_bill
+ WHERE cust_bill.custnum = cust_main.custnum )
+ - ( SELECT COALESCE( SUM(paid), 0 ) FROM cust_pay
+ WHERE cust_pay.custnum = cust_main.custnum )
+ - ( SELECT COALESCE( SUM(amount), 0 ) FROM cust_credit
+ WHERE cust_credit.custnum = cust_main.custnum )
+ + ( SELECT COALESCE( SUM(refund), 0 ) FROM cust_refund
+ WHERE cust_refund.custnum = cust_main.custnum )
+"; }
+
+=item balance_date_sql START_TIME [ END_TIME [ OPTION => VALUE ... ] ]
+
+Returns an SQL fragment to retreive the balance for this customer, only
+considering invoices with date earlier than START_TIME, and optionally not
+later than END_TIME (total_owed_date minus total_credited minus
+total_unapplied_payments).
+
+Times are specified as SQL fragments or numeric
+UNIX timestamps; see L<perlfunc/"time">). Also see L<Time::Local> and
+L<Date::Parse> for conversion functions. The empty string can be passed
+to disable that time constraint completely.
+
+Available options are:
+
+=over 4
+
+=item unapplied_date - set to true to disregard unapplied credits, payments and refunds outside the specified time period - by default the time period restriction only applies to invoices (useful for reporting, probably a bad idea for event triggering)
+
+=item total - set to true to remove all customer comparison clauses, for totals
+
+=item where - WHERE clause hashref (elements "AND"ed together) (typically used with the total option)
+
+=item join - JOIN clause (typically used with the total option)
+
+=item
+
+=back
+
+=cut
+
+sub balance_date_sql {
+ my( $class, $start, $end, %opt ) = @_;
+
+ my $owed = FS::cust_bill->owed_sql;
+ my $unapp_refund = FS::cust_refund->unapplied_sql;
+ my $unapp_credit = FS::cust_credit->unapplied_sql;
+ my $unapp_pay = FS::cust_pay->unapplied_sql;
+
+ my $j = $opt{'join'} || '';
+
+ my $owed_wh = $class->_money_table_where( 'cust_bill', $start,$end,%opt );
+ my $refund_wh = $class->_money_table_where( 'cust_refund', $start,$end,%opt );
+ my $credit_wh = $class->_money_table_where( 'cust_credit', $start,$end,%opt );
+ my $pay_wh = $class->_money_table_where( 'cust_pay', $start,$end,%opt );
+
+ " ( SELECT COALESCE(SUM($owed), 0) FROM cust_bill $j $owed_wh )
+ + ( SELECT COALESCE(SUM($unapp_refund), 0) FROM cust_refund $j $refund_wh )
+ - ( SELECT COALESCE(SUM($unapp_credit), 0) FROM cust_credit $j $credit_wh )
+ - ( SELECT COALESCE(SUM($unapp_pay), 0) FROM cust_pay $j $pay_wh )
+ ";
+
+}
+
+=item _money_table_where TABLE START_TIME [ END_TIME [ OPTION => VALUE ... ] ]
+
+Helper method for balance_date_sql; name (and usage) subject to change
+(suggestions welcome).
+
+Returns a WHERE clause for the specified monetary TABLE (cust_bill,
+cust_refund, cust_credit or cust_pay).
+
+If TABLE is "cust_bill" or the unapplied_date option is true, only
+considers records with date earlier than START_TIME, and optionally not
+later than END_TIME .
+
+=cut
+
+sub _money_table_where {
+ my( $class, $table, $start, $end, %opt ) = @_;
+
+ my @where = ();
+ push @where, "cust_main.custnum = $table.custnum" unless $opt{'total'};
+ if ( $table eq 'cust_bill' || $opt{'unapplied_date'} ) {
+ push @where, "$table._date <= $start" if defined($start) && length($start);
+ push @where, "$table._date > $end" if defined($end) && length($end);
+ }
+ push @where, @{$opt{'where'}} if $opt{'where'};
+ my $where = scalar(@where) ? 'WHERE '. join(' AND ', @where ) : '';
+
+ $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;
projects / freeside.git / blobdiff