fix voided invoice report vs. customer classes, similar to #37243
[freeside.git] / httemplate / search / svc_phone.cgi
1 <& elements/svc_Common.html,
2                  'title'             => "Phone number search results",
3                  'name'              => 'phone numbers',
4                  'query'             => $sql_query,
5                  'count_query'       => $count_query,
6                  'redirect'          => $redirect,
7                  'header'            => [ '#',
8                                           'Service',
9                                           'Country code',
10                                           'Phone number',
11                                           @header,
12                                           emt('Pkg. Status'),
13                                           FS::UI::Web::cust_header($cgi->param('cust_fields')),
14                                         ],
15                  'fields'            => [ 'svcnum',
16                                           'svc',
17                                           'countrycode',
18                                           'phonenum',
19                                           @fields,
20                                           sub {
21                                             $cust_pkg_cache{$_[0]->svcnum} ||= $_[0]->cust_svc->cust_pkg;
22                                             $cust_pkg_cache{$_[0]->svcnum}->ucfirst_status
23                                           },
24                                           \&FS::UI::Web::cust_fields,
25                                         ],
26                  'links'             => [ $link,
27                                           $link,
28                                           $link,
29                                           $link,
30                                           ( map '', @header ),
31                                           '', # pkg status
32                                           ( map { $_ ne 'Cust. Status' ? $link_cust : '' }
33                                                 FS::UI::Web::cust_header($cgi->param('cust_fields'))
34                                           ),
35                                         ],
36                  'align' => 'rlrr'.
37                             join('', map 'r', @header).
38                             'r'.
39                             FS::UI::Web::cust_aligns(),
40                  'color' => [ 
41                               '',
42                               '',
43                               '',
44                               '',
45                               ( map '', @header ),
46                               sub {
47                                 my $c = FS::cust_pkg::statuscolors;
48                                 $c->{$cust_pkg_cache{$_[0]->svcnum}->status };
49                               }, # pkg status
50                               FS::UI::Web::cust_colors(),
51                             ],
52                  'style' => [ 
53                               '',
54                               '',
55                               '',
56                               '',
57                               ( map '', @header ),
58                               'b',
59                               FS::UI::Web::cust_styles(),
60                             ],
61               
62 &>
63 <%init>
64
65 die "access denied"
66   unless $FS::CurrentUser::CurrentUser->access_right('List services');
67
68 my %cust_pkg_cache;
69
70 my $conf = new FS::Conf;
71
72 my @select = ();
73 my $orderby = 'ORDER BY svcnum';
74
75 my @header = ();
76 my @fields = ();
77 my $link = [ "${p}view/svc_phone.cgi?", 'svcnum' ];
78 my $redirect = $link;
79
80 my %search_hash = ();
81 my @extra_sql = ();
82
83 if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) {
84
85   $search_hash{'unlinked'} = 1
86     if $cgi->param('magic') eq 'unlinked';
87
88   if ( $cgi->param('sortby') =~ /^(\w+)$/ ) {
89     my $sortby = $1;
90     $orderby = "ORDER BY $sortby";
91   }
92
93   if ( $cgi->param('usage_total') ) {
94
95     my($beginning,$ending) = FS::UI::Web::parse_beginning_ending($cgi, 'usage');
96
97     $redirect = '';
98
99     #my $and_date = " AND startdate >= $beginning AND startdate <= $ending ";
100     my $and_date = " AND enddate >= $beginning AND enddate <= $ending ";
101
102     my $fromwhere = " FROM cdr WHERE cdr.svcnum = svc_phone.svcnum $and_date";
103
104     #more efficient to join against cdr just once... this will do for now
105     push @select, map { " ( SELECT SUM($_) $fromwhere ) AS $_ " }
106                       qw( billsec rated_price );
107
108     my $money_char = $conf->config('money_char') || '$';
109
110     push @header, 'Minutes', 'Billed';
111     push @fields, 
112       sub { sprintf('%.3f', shift->get('billsec') / 60 ); },
113       sub { $money_char. sprintf('%.2f', shift->get('rated_price') ); };
114
115     #XXX and termination... (this needs a config to turn on, not by default)
116     if ( 1 ) { # $conf->exists('cdr-termination_hack') { #}
117
118       my $f_w =
119         " FROM cdr_termination LEFT JOIN cdr USING ( acctid ) ".
120         " WHERE cdr.carrierid = CAST(svc_phone.phonenum AS BIGINT) ". # XXX connectone-specific, has to match svc_external.id :/
121         $and_date;
122
123       push @select,
124         " ( SELECT SUM(billsec) $f_w ) AS term_billsec ",
125         " ( SELECT SUM(cdr_termination.rated_price) $f_w ) AS term_rated_price";
126
127       push @header, 'Term Min', 'Term Billed';
128       push @fields,
129         sub { sprintf('%.3f', shift->get('term_billsec') / 60 ); },
130         sub { $money_char. sprintf('%.2f', shift->get('rated_price') ); };
131
132     }
133                  
134
135   }
136
137 } elsif ( $cgi->param('magic') =~ /^advanced$/ ) {
138
139   for (qw( agentnum custnum cust_status balance balance_days cust_fields )) {
140     $search_hash{$_} = $cgi->param($_) if length($cgi->param($_));
141   }
142
143   for (qw( payby pkgpart svcpart )) {
144     $search_hash{$_} = [ $cgi->param($_) ] if $cgi->param($_);
145   }
146
147 } elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) {
148   $search_hash{'svcpart'} = [ $1 ];
149   if ( defined($cgi->param('cancelled')) ) {
150     $search_hash{'cancelled'} = $cgi->param('cancelled') ? 1 : 0;
151   }
152 } else {
153   $cgi->param('phonenum') =~ /^([\d\- ]+)$/; 
154   my $phonenum = $1;
155   $phonenum =~ s/\D//g;
156   push @extra_sql, "phonenum = '$phonenum'";
157 }
158
159 $search_hash{'addl_select'} = \@select;
160 $search_hash{'order_by'} = $orderby;
161 $search_hash{'where'} = \@extra_sql;
162
163 my $sql_query = FS::svc_phone->search(\%search_hash);
164 my $count_query = delete($sql_query->{'count_query'});
165
166 #smaller false laziness w/svc_*.cgi here
167 my $link_cust = sub {
168   my $svc_x = shift;
169   $svc_x->custnum ? [ "${p}view/cust_main.cgi?", 'custnum' ] : '';
170 };
171
172 </%init>