X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2FReport%2FFCC_477.pm;h=af45b2daca966f8a32f6ec6ab309649f2d20c583;hb=56d10a0d13cc291eb5ee6197c35b10407794f89c;hp=f5d6a06ecc4578c196e3a5463fc69437b2c3d8fd;hpb=a3b7282fda1955c41e97b619ba4e27af39e8afaf;p=freeside.git diff --git a/FS/FS/Report/FCC_477.pm b/FS/FS/Report/FCC_477.pm index f5d6a06ec..af45b2dac 100644 --- a/FS/FS/Report/FCC_477.pm +++ b/FS/FS/Report/FCC_477.pm @@ -265,7 +265,8 @@ sub active_on { # "suspended as of some past date" is a complicated query.) my $date = shift; "cust_pkg.setup <= $date AND ". - "(cust_pkg.cancel IS NULL OR cust_pkg.cancel > $date)"; + "(cust_pkg.cancel IS NULL OR cust_pkg.cancel > $date) AND ". + "(cust_pkg.change_date IS NULL OR cust_pkg.change_date <= $date)" } sub is_fixed_broadband { @@ -276,28 +277,68 @@ sub is_mobile_broadband { "is_broadband::int = 1 AND technology::int IN( 80, 81, 82, 83, 84, 85, 86, 87, 88)" } + =item report SECTION, OPTIONS Returns the report section SECTION (see the C method for section -name strings) as an arrayref of arrayrefs. OPTIONS may contain the following: +name strings). OPTIONS may contain the following: - date: a timestamp value. Packages that were active on that date will be counted. - agentnum: limit to packages with this agent. -- detail: if true, the report will contain an additional column which contains -the keys of all objects aggregated in the row. - - ignore_quantity: if true, package quantities will be ignored (only distinct packages will be counted). +The result will be a hashref containing three parallel arrayrefs: +- "data", the columns required by the FCC. +- "detail", a list of the package numbers included in each row's aggregation +- "error", a hashref containing any error status strings in that row. Keys +are error identifiers, values are the messages to show the user. +as well as an informational item: +- "num_errors", the number of rows that contain errors + +=item report_data SECTION, OPTIONS + +Returns only the data, not the detail or error columns. This is the part that +will be submitted to the FCC. + =cut sub report { my $class = shift; my $section = shift; my %opt = @_; + $opt{detail} = 1; + + # add the error column + my $data = $class->report_data($section, %opt); + my $error = []; + my $detail = []; + my $check_method = $section.'_check'; + my $num_errors = 0; + foreach my $row (@$data) { + if ( $class->can($check_method) ) { # they don't all have these + my $eh = $class->$check_method( $row ); + $num_errors++ if keys(%$eh); + push $error, $eh + } + push @$detail, pop @$row; # this comes from the query + } + + return +{ + data => $data, + error => $error, + detail => $detail, + num_errors => $num_errors, + }; +} + +sub report_data { + my $class = shift; + my $section = shift; + my %opt = @_; my $method = $section.'_sql'; die "Report section '$section' is not implemented\n" @@ -307,7 +348,7 @@ sub report { warn $statement if $DEBUG; my $sth = dbh->prepare($statement); $sth->execute or die $sth->errstr; - $sth->fetchall_arrayref; + return $sth->fetchall_arrayref; } sub fbd_sql { @@ -355,8 +396,10 @@ sub fbs_sql { my $agentnum = $opt{agentnum}; my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)'; + my $censustract = "replace(cust_location.censustract, '.', '')"; + my @select = ( - 'cust_location.censustract', + "$censustract AS censustract", 'technology', 'broadband_downstream', 'broadband_upstream', @@ -381,8 +424,7 @@ sub fbs_sql { is_fixed_broadband() ); push @where, "cust_main.agentnum = $agentnum" if $agentnum; - my $group_by = 'cust_location.censustract, technology, '. - 'broadband_downstream, broadband_upstream '; + my $group_by = "$censustract, technology, broadband_downstream, broadband_upstream "; my $order_by = $group_by; "SELECT ".join(', ', @select) . " @@ -394,15 +436,44 @@ sub fbs_sql { } +sub fbs_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.'; + } + + #technology + if ( length($row->[1]) == 0 ) { + $e{'technology_null'} = 'The package has no technology type.'; + } + + #speeds + if ( length($row->[2]) == 0 or length($row->[3]) == 0 ) { + $e{'speed_null'} = 'The package is missing downstream or upstream speeds.'; + } elsif ( $row->[2] !~ /^\d*(\.\d+)?$/ or $row->[3] !~ /^\d*(\.\d+)?$/ ) { + $e{'speed_bad'} = 'The downstream and upstream speeds must be decimal numbers in Mbps.'; + } elsif ( $row->[2] == 0 or $row->[3] == 0 ) { + $e{'speed_zero'} = 'The downstream and upstream speeds cannot be zero.'; + } + + return \%e; +} + sub fvs_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)'; + my $censustract = "replace(cust_location.censustract, '.', '')"; my @select = ( - 'cust_location.censustract', + "$censustract AS censustract", # VoIP indicator (0 for non-VoIP, 1 for VoIP) 'COALESCE(is_voip, 0)', # number of lines/subscriptions @@ -426,7 +497,7 @@ sub fvs_sql { "(is_voip = 1 OR is_phone = 1)", ); push @where, "cust_main.agentnum = $agentnum" if $agentnum; - my $group_by = 'cust_location.censustract, COALESCE(is_voip, 0)'; + my $group_by = "$censustract, COALESCE(is_voip, 0)"; my $order_by = $group_by; "SELECT ".join(', ', @select) . " @@ -438,6 +509,19 @@ sub fvs_sql { } +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 = @_;