httemplate/browse/part_referral.html

   1 <% include("/elements/header.html","Advertising source Listing" ) %>
   2
   3 Where a customer heard about your service. Tracked for informational purposes.
   4 <BR><BR>
   5
   6 <A HREF="<% $p %>edit/part_referral.html"><I>Add a new advertising source</I></A>
   7 <BR><BR>
   8
   9 <% include('/elements/table-grid.html') %>
  10 % my $bgcolor1 = '#eeeeee';
  11 %   my $bgcolor2 = '#ffffff';
  12 %   my $bgcolor = '';
  13
  14 <TR>
  15   <TH CLASS="grid" BGCOLOR="#cccccc" COLSPAN=2 ROWSPAN=2>Advertising source</TH>
  16 % if ( $show_agentnums ) {
  17
  18     <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Agent</TH>
  19 % }
  20
  21   <TH CLASS="grid" BGCOLOR="#cccccc" COLSPAN=<% scalar(keys %after) %>>Customers and Packages</TH>
  22 </TR>
  23 % for my $period ( keys %after ) {
  24
  25   <TH CLASS="grid" BGCOLOR="#cccccc"><FONT SIZE=-1><% $period %></FONT></TH>
  26 % }
  27
  28 </TR>
  29
  30 %foreach my $part_referral ( FS::part_referral->all_part_referral(1) ) {
  31 %
  32 %  if ( $bgcolor eq $bgcolor1 ) {
  33 %    $bgcolor = $bgcolor2;
  34 %  } else {
  35 %    $bgcolor = $bgcolor1;
  36 %  }
  37 %
  38 %  $a = 0;
  39
  40       <TR>
  41
  42         <TD CLASS="grid" BGCOLOR="<% $bgcolor %>">
  43 % if ( $part_referral->agentnum || $curuser->access_right('Edit global advertising sources') ) {
  44 %            $a++;
  45 %
  46
  47             <A HREF="<% $p %>edit/part_referral.html?<% $part_referral->refnum %>">
  48 % }
  49
  50           <% $part_referral->refnum %><% $a ? '</A>' : '' %></TD>
  51         <TD CLASS="grid" BGCOLOR="<% $bgcolor %>">
  52 % if ( $a ) {
  53
  54             <A HREF="<% $p %>edit/part_referral.html?<% $part_referral->refnum %>">
  55 % }
  56
  57           <% $part_referral->referral %><% $a ? '</A>' : '' %></TD>
  58 % if ( $show_agentnums ) {
  59
  60           <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><% $part_referral->agentnum ? $part_referral->agent->agent : '(global)' %></TD>
  61 % }
  62 % for my $period ( keys %after ) {
  63 %          my @param = ( $part_referral->refnum,
  64 %                        $today-$after{$period},
  65 %                        $today+$before{$period},
  66 %                      );
  67 %          $cust_sth->execute(@param) or die $cust_sth->errstr;
  68 %          my $num_cust = $cust_sth->fetchrow_arrayref->[0];
  69 %          $pkg_sth->execute(@param) or die $pkg_sth->errstr;
  70 %          my $num_pkg = $pkg_sth->fetchrow_arrayref->[0];
  71
  72           <TD CLASS="inv" BGCOLOR="<% $bgcolor %>" ALIGN="right">
  73             <TABLE CLASS="inv" CELLSPACING=0 CELLPADDING=0>
  74               <TR>
  75                 <TD ALIGN="right"><B><% $num_cust %></B></TD>
  76                 <TD ALIGN="left">customers</TD>
  77               </TR>
  78               <TR>
  79                 <TD ALIGN="right"><B><% $num_pkg %></B></TD>
  80                 <TD ALIGN="left">packages</TD>
  81               </TR>
  82             </TABLE>
  83           </TD>
  84 % }
  85
  86       </TR>
  87 % }
  88 %
  89 %  $cust_statement =~ s/AND refnum = \?//;
  90 %  $cust_sth = dbh->prepare($cust_statement)
  91 %    or die dbh->errstr;
  92 %  $pkg_statement =~ s/AND h_pkg_referral\.refnum = \?//;
  93 %  $pkg_sth = dbh->prepare($pkg_statement)
  94 %    or die dbh->errstr;
  95
  96       <TR>
  97         <TD BGCOLOR="#dddddd" ALIGN="center" COLSPAN=3><B>Total</B></TD>
  98 % for my $period ( keys %after ) {
  99 %          my @param = ( $today-$after{$period},
 100 %                        $today+$before{$period},
 101 %                      );
 102 %          $cust_sth->execute( @param ) or die $cust_sth->errstr;
 103 %          my $num_cust = $cust_sth->fetchrow_arrayref->[0];
 104 %          $pkg_sth->execute(@param) or die $pkg_sth->errstr;
 105 %          my $num_pkg = $pkg_sth->fetchrow_arrayref->[0];
 106
 107           <TD CLASS="inv" BGCOLOR="#dddddd" ALIGN="right">
 108             <TABLE CLASS="inv" CELLSPACING=0 CELLPADDING=0>
 109               <TR>
 110                 <TD ALIGN="right"><B><% $num_cust %></B></TD>
 111                 <TD ALIGN="left">customers</TD>
 112               </TR>
 113               <TR>
 114                 <TD ALIGN="right"><B><% $num_pkg %></B></TD>
 115                 <TD ALIGN="left">packages</TD>
 116               </TR>
 117             </TABLE>
 118           </TD>
 119
 120 % }
 121
 122       </TR>
 123     </TABLE>
 124   </BODY>
 125 </HTML>
 126 <%init>
 127
 128 die "access denied"
 129   unless $FS::CurrentUser::CurrentUser->access_right('Edit advertising sources')
 130       || $FS::CurrentUser::CurrentUser->access_right('Edit global advertising sources');
 131
 132 my $today = timelocal(0, 0, 0, (localtime(time))[3..5] );
 133
 134 tie my %after, 'Tie::IxHash',
 135   'Today'         =>        0,
 136   'Yesterday'     =>    86400, # 60sec * 60min * 24hrs
 137   'Past week'     =>   518400, # 60sec * 60min * 24hrs * 6days
 138   'Past 30 days'  =>  2505600, # 60sec * 60min * 24hrs * 29days
 139   'Past 60 days'  =>  5097600, # 60sec * 60min * 24hrs * 59days
 140   'Past 90 days'  =>  7689600, # 60sec * 60min * 24hrs * 89days
 141   'Past 6 months' => 15724800, # 60sec * 60min * 24hrs * 182days
 142   'Past year'     => 31486000, # 60sec * 60min * 24hrs * 364days
 143   'Total'         => $today,
 144 ;
 145 my %before = (
 146   'Today'         =>   86400, # 60sec * 60min * 24hrs
 147   'Yesterday'     =>       0,
 148   'Past week'     =>   86400, # 60sec * 60min * 24hrs
 149   'Past 30 days'  =>   86400, # 60sec * 60min * 24hrs
 150   'Past 60 days'  =>   86400, # 60sec * 60min * 24hrs
 151   'Past 90 days'  =>   86400, # 60sec * 60min * 24hrs
 152   'Past 6 months' =>   86400, # 60sec * 60min * 24hrs
 153   'Past year'     =>   86400, # 60sec * 60min * 24hrs
 154   'Total'         =>   86400, # 60sec * 60min * 24hrs
 155 );
 156
 157 my $curuser = $FS::CurrentUser::CurrentUser;
 158
 159 my $show_agentnums = ( scalar($curuser->agentnums) > 1 );
 160
 161 my $cust_statement = "SELECT COUNT(*) FROM h_cust_main
 162                        WHERE history_action = 'insert'
 163                          AND refnum = ?
 164                          AND history_date >= ?
 165                          AND history_date < ?
 166                          AND ". $curuser->agentnums_sql;
 167 my $cust_sth = dbh->prepare($cust_statement)
 168   or die dbh->errstr;
 169
 170 my $pkg_statement = "SELECT COUNT(*) FROM h_pkg_referral
 171                        LEFT JOIN cust_pkg  USING ( pkgnum )
 172                        LEFT JOIN cust_main USING ( custnum )
 173                        WHERE history_action = 'insert'
 174                          AND h_pkg_referral.refnum = ?
 175                          AND history_date >= ?
 176                          AND history_date < ?
 177                          AND ". $curuser->agentnums_sql;
 178 my $pkg_sth = dbh->prepare($pkg_statement)
 179   or die dbh->errstr;
 180
 181 </%init>