+ my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
+ my $censustract = "replace(cust_location.censustract, '.', '')";
+
+ my @select = (
+ "$censustract AS censustract",
+ # VoIP indicator (0 for non-VoIP, 1 for VoIP)
+ 'COALESCE(is_voip, 0)',
+ # number of lines/subscriptions
+ "SUM($q * (CASE WHEN is_voip = 1 THEN 1 ELSE phone_lines END))",
+ # consumer grade lines/subscriptions
+ "SUM($q * COALESCE(is_consumer,0) * (CASE WHEN is_voip = 1 THEN voip_sessions ELSE phone_lines END))",
+ );
+ push @select, "array_to_string(array_agg(pkgnum), ',')" if $opt{detail};
+
+ my $from = 'cust_pkg
+ JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
+ JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
+ JOIN part_pkg USING (pkgpart) '.
+ join_optionnames_int(qw(
+ is_phone is_voip is_consumer phone_lines voip_sessions
+ ))
+ ;
+
+ my @where = (
+ active_on($date),
+ "(is_voip = 1 OR is_phone = 1)",
+ );
+ push @where, "cust_main.agentnum = $agentnum" if $agentnum;
+ my $group_by = "$censustract, COALESCE(is_voip, 0)";
+ my $order_by = $group_by;
+
+ "SELECT ".join(', ', @select) . "
+ FROM $from
+ WHERE ".join(' AND ', @where)."
+ GROUP BY $group_by
+ ORDER BY $order_by
+ ";
+
+}
+
+sub fvs_check {
+ my $class = shift;
+ my $row = shift;
+ my %e;
+ #censustract
+ if ( length($row->[0]) == 0 ) {
+ $e{'censustract_null'} = 'The package location has no census tract.';
+ } elsif ($row->[0] !~ /^\d{11}$/) {
+ $e{'censustract_bad'} = 'The census tract must be exactly 11 digits.';
+ }
+ return \%e;
+}
+
+sub lts_sql {
+ my $class = shift;
+ my %opt = @_;
+ my $date = $opt{date} || time;
+ my $agentnum = $opt{agentnum};
+ my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';