httemplate/search/unprovisioned_services.html

   1 <& elements/search.html,
   2               'title'         => 'Unprovisioned Service Search Results',
   3               'name' => 'packages with unprovisioned services',
   4               'query'         => {
   5                                    'table'   => 'cust_pkg',
   6                                    'hashref' => {},
   7                                    'select'  => join(', ',
   8                                        'cust_pkg.*',
   9                                        'pkg_svc.*',
  10
  11                                        # everything fails gloriously otherwise
  12                                        'cust_pkg.custnum as cust_main_custnum',
  13
  14                                        FS::UI::Web::cust_sql_fields(),
  15                                    ),
  16                                    'extra_sql' => $search,
  17                                    'addl_from' => $addl_from,
  18                                  },
  19               'count_query'   => $count_query,
  20               'header'          => [ 'Package',
  21                                      'Unprovisioned Services',
  22                                     FS::UI::Web::cust_header(),
  23                                    ],
  24               'fields'   => [ sub {
  25                                 my $cust_pkg = shift;
  26                                 $cust_pkg->pkg_label;
  27                               },
  28                               sub {
  29                                 my $cust_pkg = shift;
  30                                 my @available_part_svc = $cust_pkg->available_part_svc;
  31                                 my $out = '';
  32                                 foreach my $part_svc ( @available_part_svc ) {
  33                                     $out .= $part_svc->svc . ' ('
  34                                             . $part_svc->num_avail . ')<BR>'
  35                                         if grep{ $part_svc->svcpart eq $_ } @svcpart;
  36                                 }
  37                                 $out;
  38                               },
  39                               \&FS::UI::Web::cust_fields,
  40                             ],
  41               'align'    => 'll'.FS::UI::Web::cust_aligns(),
  42               'links'    => [
  43                               '',
  44                               '',
  45                               ( map { $_ ne 'Cust. Status' ? $link_cust : '' }
  46                                   FS::UI::Web::cust_header()
  47                               ),
  48                             ],
  49               'color' =>    [ '',
  50                               '',
  51                               FS::UI::Web::cust_colors(),
  52                             ],
  53               'style' =>    [ '',
  54                               '',
  55                               FS::UI::Web::cust_styles(),
  56                             ],
  57
  58 &>
  59 <%init>
  60
  61 die "access denied"
  62   unless $FS::CurrentUser::CurrentUser->access_right('List services');
  63
  64 my $svcpart_limit = "";
  65 my @svcpart = $cgi->param('svcpart');
  66 foreach my $svcpart ( @svcpart ) {
  67     die 'invalid svcpart' if $svcpart !~ /^\d+$/;
  68 }
  69 $svcpart_limit = " and pkg_svc.svcpart in (". join(',',@svcpart) . ")"
  70     if scalar(@svcpart);
  71
  72 my $search = " where cust_pkg.cancel is null and pkg_svc.quantity > 0 and "
  73             . " pkg_svc.quantity > (select count(1) from cust_svc where "
  74             . " cust_svc.pkgnum = cust_pkg.pkgnum and "
  75             . " cust_svc.svcpart = pkg_svc.svcpart) $svcpart_limit";
  76
  77 my $addl_from = " join pkg_svc using (pkgpart) ".
  78             FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg');
  79
  80 # this was very painful to derive but it appears correct
  81 #select cust_pkg.custnum,cust_pkg.pkgpart,cust_pkg.pkgnum, pkg_svc.svcpart from cust_pkg join
  82 #pkg_svc using (pkgpart) where cancel is null and pkg_svc.quantity > 0 and pkg_svc.quantity >
  83 #(select count(1) from cust_svc where cust_svc.pkgnum = cust_pkg.pkgnum and cust_svc.svcpart =
  84 #pkg_svc.svcpart) order by pkgnum, svcpart;
  85
  86 my $count_query = "select count(*) from cust_pkg $addl_from $search";
  87
  88 my $link_cust = sub {
  89   return [ "${p}view/cust_main.cgi?", 'custnum' ];
  90 };
  91
  92 </%init>