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