From 0397a2246116fbb87961200865983760fea6a82c Mon Sep 17 00:00:00 2001 From: Ivan Kohler Date: Thu, 26 Jul 2012 14:03:23 -0700 Subject: 23commit working from top level now i guess --- bin/23commit | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'bin') diff --git a/bin/23commit b/bin/23commit index b7b0c1e57..d64459cf0 100755 --- a/bin/23commit +++ b/bin/23commit @@ -19,7 +19,7 @@ die "no files!" unless @ARGV; #print < Date: Tue, 21 Aug 2012 18:45:10 -0700 Subject: add agent email, RT#18231 --- bin/agent_email | 30 ++++++++++++++++++++++++++++++ 1 file changed, 30 insertions(+) create mode 100755 bin/agent_email (limited to 'bin') diff --git a/bin/agent_email b/bin/agent_email new file mode 100755 index 000000000..2fe47c4ba --- /dev/null +++ b/bin/agent_email @@ -0,0 +1,30 @@ +#!/usr/bin/perl + +use strict; +use Getopt::Std; +use FS::UID qw(adminsuidsetup); + +&untaint_argv; #what it sounds like (eww) +use vars qw(%opt); +getopts("a:", \%opt); + +my $user = shift or die &usage; +adminsuidsetup $user; + +use FS::Cron::agent_email qw(agent_email); +agent_email(%opt); + +### +# subroutines +### + +sub untaint_argv { + foreach $_ ( $[ .. $#ARGV ) { #untaint @ARGV + #$ARGV[$_] =~ /^([\w\-\/]*)$/ || die "Illegal arguement \"$ARGV[$_]\""; + # Date::Parse + $ARGV[$_] =~ /^(.*)$/ || die "Illegal arguement \"$ARGV[$_]\""; + $ARGV[$_]=$1; + } +} + +1; -- cgit v1.2.1 From a9303743bcdfb2e440d8e80b76c3683e6089f8ea Mon Sep 17 00:00:00 2001 From: Ivan Kohler Date: Wed, 22 Aug 2012 22:28:47 -0700 Subject: vnes rate reimport, RT#18867 --- bin/v-rate-reimport | 172 ++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 172 insertions(+) create mode 100755 bin/v-rate-reimport (limited to 'bin') diff --git a/bin/v-rate-reimport b/bin/v-rate-reimport new file mode 100755 index 000000000..8b5305895 --- /dev/null +++ b/bin/v-rate-reimport @@ -0,0 +1,172 @@ +#!/usr/bin/perl + +use strict; +use DBI; +use FS::UID qw(adminsuidsetup); +use FS::rate_prefix; +use FS::rate_region; +use FS::rate_detail; +use FS::Record qw(qsearch qsearchs dbh); + +# #delete from rate; +# Create interstate and intrastate rate plans +# +# #delete from rate_detail; +# #delete from rate_region; +# #delete from rate_prefix; + +# Assumption: 1-to-1 relationship between rate_region and rate_prefix, with +# two rate_detail per rate_region: one for interstate; one for intrastate +# +# run the script, setting the appropriate values below. + +####### SET THESE! #################### + +my $DRY_RUN = 0; + +my $intra_ratenum = 5; +my $inter_ratenum = 6; +my $intra_class = 1; +my $inter_class = 2; +#my $file = "/home/levinse/domestic_interstate.xls"; +#my $file = "/home/ivan/vnes/New VNES Rate Table.xlsx"; +my $file = "/home/ivan/New VNES Rate Table.csv"; +#my $sheet_name = 'Sheet1'; +####################################### + +my $user = shift or die "no user specified"; +adminsuidsetup $user; + +local $SIG{HUP} = 'IGNORE'; +local $SIG{INT} = 'IGNORE'; +local $SIG{QUIT} = 'IGNORE'; +local $SIG{TERM} = 'IGNORE'; +local $SIG{TSTP} = 'IGNORE'; +local $SIG{PIPE} = 'IGNORE'; + +my $oldAutoCommit = $FS::UID::AutoCommit; +local $FS::UID::AutoCommit = 0; +my $dbhfs = dbh; + +#my $dbh = DBI->connect("DBI:Excel:file=$file") +# or die "can't connect: $DBI::errstr"; + +#my $sth = $dbh->prepare("select * from $sheet_name") +# or die "can't prepare: ". $dbh->errstr; +#$sth->execute +# or die "can't execute: ". $sth->errstr; + +use Text::CSV_XS; +my $csv = Text::CSV_XS->new or die Text::CSV->error_diag; + +open(my $fh, "<$file") or die $!; +my $header = scalar(<$fh>); #NPA, NXX, LATA, State, Intrastate, Interstate + +my @rp_cache = qsearch('rate_prefix', {} );# or die "can't cache rate_prefix"; +my %rp_cache = map { $_->npa => $_ } @rp_cache; + +sub fatal { + my $msg = shift; + $dbhfs->rollback; # if $oldAutoCommit; + die $msg; +} + +while ( my $row = $csv->getline($fh) ) { + + #my $lata = $row->{'lata'}; + #my $ocn = $row->{'ocn'}; + #my $state = $row->{'state'}; + #my $rate = $row->{'rate'}; + #my $npanxx = $row->{'lrn'}; + + #NPA, NXX, LATA, State, Intrastate, Interstate + my $npa = $row->[0]; + my $nxx = $row->[1]; + my $lata = $row->[2]; + my $state = $row->[3]; + ( my $intra_rate = $row->[4] ) =~ s/^\s*\$//; + ( my $inter_rate = $row->[5] ) =~ s/^\s*\$//; + + #in the new data, instead of being "$-", these are all identical to the + #rate from the immediatelly preceeding cell/NPANXX... probably an artifact + #rather than real rates then? so also skipping this import + #import + next if $lata == '99999'; + + my $error = ''; + + my $rp; + if ( $rp_cache{$npa.$nxx} ) { + $rp = $rp_cache{$npa.$nxx}; + } + else { + + #warn "inserting new rate_region / rate_prefix for $npa-$nxx\n"; + die "new rate_region / rate_prefix $npa-$nxx\n"; + + my $rr = new FS::rate_region { 'regionname' => $state }; + $error = $rr->insert; + fatal("can't insert rr") if $error; + + $rp = new FS::rate_prefix { 'countrycode' => '1', + 'npa' => $npa.$nxx, #$npanxx + #'ocn' => $ocn, + 'state' => $state, + 'latanum' => $lata, + 'regionnum' => $rr->regionnum, + }; + $error = $rp->insert; + fatal("can't insert rp") if $error; + $rp_cache{$npa.$nxx} = $rp; + } + + #use Data::Dumper; + #warn Dumper($rp); + + my %hash = ( 'dest_regionnum' => $rp->regionnum, ); + + my %intra_hash = ( 'ratenum' => $intra_ratenum, + 'intra_class' => $intra_class, + %hash, + ); + + my $intra_rd = qsearchs( 'rate_detail', \%intra_hash ) + || die; #new FS::rate_detail \%intra_hash; + + $intra_rd->min_included( 0 ); + $intra_rd->sec_granularity( 6 ); #60 + die if $intra_rd->min_charge > 0; + $intra_rd->min_charge( $intra_rate ); + + #$error = $intra_rd->ratedetailnum ? $intra_rd->replace : $intra_rd->insert; + $error = $intra_rd->replace; + fatal("can't insert/replace (intra) rd: $error") if $error; + + my %inter_hash = ( 'ratenum' => $inter_ratenum, + 'inter_class' => $inter_class, + %hash, + ); + + my $inter_rd = qsearchs( 'rate_detail', \%inter_hash ) + || die; #new FS::rate_detail \%inter_hash; + + $inter_rd->min_included( 0 ); + $inter_rd->sec_granularity( 6 ); #60 + die if $inter_rd->min_charge > 0; + $inter_rd->min_charge( $inter_rate ); + + #$error = $inter_rd->ratedetailnum ? $inter_rd->replace : $inter_rd->insert; + $error = $inter_rd->replace; + fatal("can't insert/replace (inter) rd: $error") if $error; +} +$csv->eof or $csv->error_diag (); +close $fh; + +if ( $DRY_RUN ) { + $dbhfs->rollback or die $dbhfs->errstr; # if $oldAutoCommit; +} else { + $dbhfs->commit or die $dbhfs->errstr; # if $oldAutoCommit; +} + +1; + -- cgit v1.2.1 From 0e17277ed0cc243452dfc07d1bd702502c7d50c3 Mon Sep 17 00:00:00 2001 From: Ivan Kohler Date: Tue, 28 Aug 2012 16:06:15 -0700 Subject: adding export scripts for ea, RT#19067 --- bin/cust_bill.export | 49 +++++++++++++++++++++ bin/cust_main.export | 109 +++++++++++++++++++++++++++++++++++++++++++++++ bin/cust_pkg.export | 61 ++++++++++++++++++++++++++ bin/svc_acct.export | 54 +++++++++++++++++++++++ bin/svc_broadband.export | 59 +++++++++++++++++++++++++ bin/svc_phone.export | 55 ++++++++++++++++++++++++ 6 files changed, 387 insertions(+) create mode 100755 bin/cust_bill.export create mode 100755 bin/cust_main.export create mode 100755 bin/cust_pkg.export create mode 100755 bin/svc_acct.export create mode 100755 bin/svc_broadband.export create mode 100755 bin/svc_phone.export (limited to 'bin') diff --git a/bin/cust_bill.export b/bin/cust_bill.export new file mode 100755 index 000000000..40c32e539 --- /dev/null +++ b/bin/cust_bill.export @@ -0,0 +1,49 @@ +#!/usr/bin/perl + +use strict; +use Text::CSV_XS; +use FS::UID qw(adminsuidsetup); +use FS::Record qw(qsearch); +use FS::cust_bill; +use Date::Format; + +my @fields = qw( + invnum + custnum +); + +push @fields, + { 'header' => 'Date', + 'callback' => sub { time2str('%x', shift->_date); }, + }, +; + +push @fields, qw( charged owed ); + +my $user = shift or die &usage; +adminsuidsetup $user; + +my $agentnum = shift or die &usage; + +my $csv = new Text::CSV_XS; + +$csv->combine( map { ref($_) ? $_->{'header'} : $_ } @fields ) or die; +print $csv->string."\n"; + +my @cust_bill = qsearch({ + 'table' => 'cust_bill', + 'addl_from' => 'LEFT JOIN cust_main USING ( custnum )', + 'hashref' => {}, + 'extra_sql' => "WHERE cust_main.agentnum = $agentnum", +}); + +foreach my $cust_bill ( @cust_bill ) { + $csv->combine( map { ref($_) ? &{$_->{'callback'}}($cust_bill) + : $cust_bill->$_() + } + @fields + ) or die; + print $csv->string."\n"; +} + +1; diff --git a/bin/cust_main.export b/bin/cust_main.export new file mode 100755 index 000000000..4adfeeb7c --- /dev/null +++ b/bin/cust_main.export @@ -0,0 +1,109 @@ +#!/usr/bin/perl + +use strict; +use Text::CSV_XS; +use FS::UID qw(adminsuidsetup); +use FS::Record qw(qsearch); +use FS::cust_main; + +my @fields = qw( + custnum + status + last + first + company + address1 + address2 + city + county + state + zip + country + daytime + night + mobile + fax + ship_address1 + ship_address2 + ship_city + ship_county + ship_state + ship_zip + ship_country + ship_daytime + ship_night + ship_mobile + ship_fax + invoicing_list_emailonly_scalar + payby + balance +); + +push @fields, + #Billing Type: Credit Card + { 'header' => 'Credit Card number', + 'callback' => sub { my $c_m = shift; + $c_m->payby =~ /^(CARD|DCRD)$/ ? $c_m->payinfo : '' ; + }, + }, + { 'header' => 'Expiration on card', + 'callback' => sub { my $c_m = shift; + return '' unless $c_m->payby =~ /^(CARD|DCRD)$/; + $c_m->paydate =~ /^(\d{4})-(\d{2})-\d{2}$/ or die; + return "$2/$1"; + }, + }, + { 'header' => 'Name on card', + 'callback' => sub { my $c_m = shift; + $c_m->payby =~ /^(CARD|DCRD)$/ ? $c_m->paydname : '' ; + }, + }, + + #Billing Type: Electronic check + { 'header' => 'ABA/Routing number', + 'callback' => sub { my $c_m = shift; + return '' unless $c_m->payby =~ /^(CHEK|DCHK)$/; + (split('@', $c_m->payinfo))[1]; + }, + }, + { 'header' => 'Account number', + 'callback' => sub { my $c_m = shift; + return '' unless $c_m->payby =~ /^(CHEK|DCHK)$/; + (split('@', $c_m->payinfo))[0]; + }, + }, + { 'header' => 'Account type', + 'callback' => sub { my $c_m = shift; + $c_m->payby =~ /^(CHEK|DCHK)$/ ? $c_m->paytype : ''; + }, + }, + { 'header' => 'Bank Name', + 'callback' => sub { my $c_m = shift; + $c_m->payby =~ /^(CHEK|DCHK)$/ ? $c_m->payname : ''; + }, + }, + +; + +my $user = shift or die &usage; +adminsuidsetup $user; + +my $agentnum = shift or die &usage; + +my $csv = new Text::CSV_XS; + +$csv->combine( map { ref($_) ? $_->{'header'} : $_ } @fields ) or die; +print $csv->string."\n"; + +my @cust_main = qsearch('cust_main', { 'agentnum'=>$agentnum }); + +foreach my $cust_main( @cust_main ) { + $csv->combine( map { ref($_) ? &{$_->{'callback'}}($cust_main) + : $cust_main->$_() + } + @fields + ) or die; + print $csv->string."\n"; +} + +1; diff --git a/bin/cust_pkg.export b/bin/cust_pkg.export new file mode 100755 index 000000000..f922e02f0 --- /dev/null +++ b/bin/cust_pkg.export @@ -0,0 +1,61 @@ +#!/usr/bin/perl + +use strict; +use Text::CSV_XS; +use FS::UID qw(adminsuidsetup); +use FS::Record qw(qsearch); +use FS::cust_pkg; +use Date::Format; + +my @fields = qw( + pkgnum + custnum + status + pkgpart +); + +push @fields, + { 'header' => 'Package', + 'callback' => sub { shift->part_pkg->pkg_comment('nopkgpart'=>1) }, + }, + map { + my $field = $_; + { 'header' => $field, + 'callback' => sub { my $d = shift->get($field) or return ''; + time2str('%x', $d); # %X", $d); + }, + }; + } qw( order_date start_date setup last_bill bill + adjourn susp resume + expire cancel uncancel + contract_end + ) +; + +my $user = shift or die &usage; +adminsuidsetup $user; + +my $agentnum = shift or die &usage; + +my $csv = new Text::CSV_XS; + +$csv->combine( map { ref($_) ? $_->{'header'} : $_ } @fields ) or die; +print $csv->string."\n"; + +my @cust_pkg = qsearch({ + 'table' => 'cust_pkg', + 'addl_from' => 'LEFT JOIN cust_main USING ( custnum )', + 'hashref' => {}, + 'extra_sql' => "WHERE cust_main.agentnum = $agentnum", +}); + +foreach my $cust_pkg ( @cust_pkg ) { + $csv->combine( map { ref($_) ? &{$_->{'callback'}}($cust_pkg) + : $cust_pkg->$_() + } + @fields + ) or die; + print $csv->string."\n"; +} + +1; diff --git a/bin/svc_acct.export b/bin/svc_acct.export new file mode 100755 index 000000000..dba4ac98d --- /dev/null +++ b/bin/svc_acct.export @@ -0,0 +1,54 @@ +#!/usr/bin/perl + +use strict; +use Text::CSV_XS; +use FS::UID qw(adminsuidsetup); +use FS::Record qw(qsearch); +use FS::svc_acct; + +my @fields = ( + { 'header' => 'pkgnum', + 'callback' => sub { shift->cust_svc->pkgnum; }, + }, + { 'header' => 'svcpart', + 'callback' => sub { shift->cust_svc->svcpart; }, + }, + { 'header' => 'Service', + 'callback' => sub { shift->cust_svc->part_svc->svc; }, + }, + qw( + username + _password + slipip + ) +); + +my $user = shift or die &usage; +adminsuidsetup $user; + +my $agentnum = shift or die &usage; + +my $csv = new Text::CSV_XS; + +$csv->combine( map { ref($_) ? $_->{'header'} : $_ } @fields ) or die; +print $csv->string."\n"; + +my @svc_acct = qsearch({ + 'table' => 'svc_acct', + 'addl_from' => 'LEFT JOIN cust_svc USING (svcnum) + LEFT JOIN cust_pkg USING (pkgnum) + LEFT JOIN cust_main USING ( custnum )', + 'hashref' => {}, + 'extra_sql' => "WHERE cust_main.agentnum = $agentnum", +}); + +foreach my $svc_acct ( @svc_acct ) { + $csv->combine( map { ref($_) ? &{$_->{'callback'}}($svc_acct) + : $svc_acct->$_() + } + @fields + ) or die; + print $csv->string."\n"; +} + +1; diff --git a/bin/svc_broadband.export b/bin/svc_broadband.export new file mode 100755 index 000000000..1d5c71318 --- /dev/null +++ b/bin/svc_broadband.export @@ -0,0 +1,59 @@ +#!/usr/bin/perl + +use strict; +use Text::CSV_XS; +use FS::UID qw(adminsuidsetup); +use FS::Record qw(qsearch); +use FS::svc_broadband; + +my @fields = ( + { 'header' => 'pkgnum', + 'callback' => sub { shift->cust_svc->pkgnum; }, + }, + { 'header' => 'svcpart', + 'callback' => sub { shift->cust_svc->svcpart; }, + }, + { 'header' => 'Service', + 'callback' => sub { shift->cust_svc->part_svc->svc; }, + }, + qw( + description + speed_up + speed_down + ip_addr + mac_addr + latitude + longitude + ) +); + +my $user = shift or die &usage; +adminsuidsetup $user; + +my $agentnum = shift or die &usage; + +my $csv = new Text::CSV_XS; + +$csv->combine( map { ref($_) ? $_->{'header'} : $_ } @fields ) or die; +print $csv->string."\n"; + +my @svc_broadband = qsearch({ + 'select' => 'svc_broadband.*', + 'table' => 'svc_broadband', + 'addl_from' => 'LEFT JOIN cust_svc USING (svcnum) + LEFT JOIN cust_pkg USING (pkgnum) + LEFT JOIN cust_main USING ( custnum )', + 'hashref' => {}, + 'extra_sql' => "WHERE cust_main.agentnum = $agentnum", +}); + +foreach my $svc_broadband ( @svc_broadband ) { + $csv->combine( map { ref($_) ? &{$_->{'callback'}}($svc_broadband) + : $svc_broadband->$_() + } + @fields + ) or die; + print $csv->string."\n"; +} + +1; diff --git a/bin/svc_phone.export b/bin/svc_phone.export new file mode 100755 index 000000000..aa0eb2082 --- /dev/null +++ b/bin/svc_phone.export @@ -0,0 +1,55 @@ +#!/usr/bin/perl + +use strict; +use Text::CSV_XS; +use FS::UID qw(adminsuidsetup); +use FS::Record qw(qsearch); +use FS::svc_phone; + +my @fields = ( + { 'header' => 'pkgnum', + 'callback' => sub { shift->cust_svc->pkgnum; }, + }, + { 'header' => 'svcpart', + 'callback' => sub { shift->cust_svc->svcpart; }, + }, + { 'header' => 'Service', + 'callback' => sub { shift->cust_svc->part_svc->svc; }, + }, + qw( + phonenum + pin + sip_password + phone_name + ) +); + +my $user = shift or die &usage; +adminsuidsetup $user; + +my $agentnum = shift or die &usage; + +my $csv = new Text::CSV_XS; + +$csv->combine( map { ref($_) ? $_->{'header'} : $_ } @fields ) or die; +print $csv->string."\n"; + +my @svc_phone = qsearch({ + 'table' => 'svc_phone', + 'addl_from' => 'LEFT JOIN cust_svc USING (svcnum) + LEFT JOIN cust_pkg USING (pkgnum) + LEFT JOIN cust_main USING ( custnum )', + 'hashref' => {}, + 'extra_sql' => "WHERE cust_main.agentnum = $agentnum", +}); + +foreach my $svc_phone ( @svc_phone ) { + $csv->combine( map { ref($_) ? &{$_->{'callback'}}($svc_phone) + : $svc_phone->$_() + } + @fields + ) or die; + print $csv->string."\n"; +} + +1; -- cgit v1.2.1 From 82d8565fbeaebd69177a3a14d833685ecb86a545 Mon Sep 17 00:00:00 2001 From: Mark Wells Date: Wed, 26 Sep 2012 00:53:05 -0700 Subject: tax calculation and reporting for new customer locations, #940 --- bin/tax_location.upgrade | 31 +++++++++++++++++++++++++++++++ 1 file changed, 31 insertions(+) create mode 100755 bin/tax_location.upgrade (limited to 'bin') diff --git a/bin/tax_location.upgrade b/bin/tax_location.upgrade new file mode 100755 index 000000000..814094551 --- /dev/null +++ b/bin/tax_location.upgrade @@ -0,0 +1,31 @@ +#!/usr/bin/perl + +use FS::UID qw(adminsuidsetup); +use FS::Record; +use FS::cust_bill_pkg; +use Date::Parse qw(str2time); +use Getopt::Std; +getopts('s:e:'); +my $username = shift @ARGV; + +if (!$username) { + print +"Usage: tax_location.upgrade [ -s START ] [ -e END ] username + +This script creates cust_bill_pkg_tax_location and cust_tax_exempt_pkg records +for existing sales tax records prior to the 3.0 cust_location changes. Changes +will be committed immediately; back up your data and run 'make +install-perl-modules' and 'freeside-upgrade' before running this script. +START and END specify an optional range of invoice dates to upgrade. + +"; + exit(1); +} + +my %opt; +$opt{s} = str2time($opt_s) if $opt_s; +$opt{e} = str2time($opt_e) if $opt_e; + +adminsuidsetup($username); +FS::cust_bill_pkg->upgrade_tax_location(%opt); +1; -- cgit v1.2.1