add -o flag to freeside-daily, to only bill for one month, RT#14904
[freeside.git] / 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>