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