diff options
Diffstat (limited to 'bin')
73 files changed, 6791 insertions, 0 deletions
diff --git a/bin/add-history-records.pl b/bin/add-history-records.pl new file mode 100755 index 000000000..fbf9d09d9 --- /dev/null +++ b/bin/add-history-records.pl @@ -0,0 +1,139 @@ +#!/usr/bin/perl + +die "This is broken. Don't use it!\n"; + +use strict; +use FS::UID qw(adminsuidsetup); +use FS::Record qw(qsearchs qsearch); + +use Data::Dumper; + +my @tables = qw(svc_acct svc_broadband svc_domain svc_external svc_forward svc_www cust_svc domain_record); +#my @tables = qw(svc_www); + +my $user = shift or die &usage; +my $dbh = adminsuidsetup($user); + +my $dbdef = FS::Record::dbdef; + +foreach my $table (@tables) { + + my $h_table = 'h_' . $table; + my $cnt = 0; + my $t_cnt = 0; + + eval "use FS::${table}"; + die $@ if $@; + eval "use FS::${h_table}"; + die $@ if $@; + + print "Adding history records for ${table}...\n"; + + my $dbdef_table = $dbdef->table($table); + my $pkey = $dbdef_table->primary_key; + + foreach my $rec (qsearch($table, {})) { + + #my $h_rec = qsearchs( + # $h_table, + # { $pkey => $rec->getfield($pkey) }, + # eval "FS::${h_table}->sql_h_searchs(time)", + #); + + my $h_rec = qsearchs( + $h_table, + { $pkey => $rec->getfield($pkey) }, + "DISTINCT ON ( $pkey ) *", + "AND history_action = 'insert' ORDER BY $pkey ASC, history_date DESC", + '', + 'AS maintable', + ); + + unless ($h_rec) { + my $h_insert_rec = $rec->_h_statement('insert', 1); + #print $h_insert_rec . "\n"; + $dbh->do($h_insert_rec); + die $dbh->errstr if $dbh->err; + $dbh->commit or die $dbh->errstr; + $cnt++; + } + + + $t_cnt++; + + } + + print "History records inserted into $h_table: $cnt\n"; + print " Total records in $table: $t_cnt\n"; + + print "\n"; + +} + +foreach my $table (@tables) { + + my $h_table = 'h_' . $table; + my $cnt = 0; + + eval "use FS::${table}"; + die $@ if $@; + eval "use FS::${h_table}"; + die $@ if $@; + + print "Adding insert records for unmatched delete records on ${table}...\n"; + + my $dbdef_table = $dbdef->table($table); + my $pkey = $dbdef_table->primary_key; + + #SELECT * FROM h_svc_www + #DISTINCT ON ( $pkey ) ? + my $where = " + WHERE ${pkey} in ( + SELECT ${h_table}1.${pkey} + FROM ${h_table} as ${h_table}1 + WHERE ( + SELECT count(${h_table}2.${pkey}) + FROM ${h_table} as ${h_table}2 + WHERE ${h_table}2.${pkey} = ${h_table}1.${pkey} + AND ${h_table}2.history_action = 'delete' + ) > 0 + AND ( + SELECT count(${h_table}3.${pkey}) + FROM ${h_table} as ${h_table}3 + WHERE ${h_table}3.${pkey} = ${h_table}1.${pkey} + AND ( ${h_table}3.history_action = 'insert' + OR ${h_table}3.history_action = 'replace_new' ) + ) = 0 + GROUP BY ${h_table}1.${pkey})"; + + + my @h_recs = qsearch( + $h_table, { }, + "DISTINCT ON ( $pkey ) *", + $where, + '', + '' + ); + + foreach my $h_rec (@h_recs) { + #print "Adding insert record for deleted record with pkey='" . $h_rec->getfield($pkey) . "'...\n"; + my $class = 'FS::' . $table; + my $rec = $class->new({ $h_rec->hash }); + my $h_insert_rec = $rec->_h_statement('insert', 1); + #print $h_insert_rec . "\n"; + $dbh->do($h_insert_rec); + die $dbh->errstr if $dbh->err; + $dbh->commit or die $dbh->errstr; + $cnt++; + } + + print "History records inserted into $h_table: $cnt\n"; + +} + + + +sub usage { + die "Usage:\n add-history-records.pl user\n"; +} + diff --git a/bin/all-postal-no-email b/bin/all-postal-no-email new file mode 100755 index 000000000..ef5dff66b --- /dev/null +++ b/bin/all-postal-no-email @@ -0,0 +1,22 @@ +#!/usr/bin/perl -w + +use strict; +use FS::UID qw(adminsuidsetup); +use FS::Record qw(qsearch); +use FS::cust_main; + +my $user = shift or die &usage; +adminsuidsetup $user; + +foreach my $cust_main ( qsearch( 'cust_main', {} ) ) { + + print $cust_main->custnum. "\n"; + + $cust_main->invoicing_list( [ 'POST' ] ); + +} + +sub usage { + die "Usage:\n\n all-postal-no-email user\n"; +} + diff --git a/bin/apache.export b/bin/apache.export new file mode 100755 index 000000000..da2d73c1c --- /dev/null +++ b/bin/apache.export @@ -0,0 +1,93 @@ +#!/usr/bin/perl -w + +use strict; +use Getopt::Std; +#use File::Path; +use File::Rsync; +use Net::SSH qw(ssh); +use FS::UID qw(adminsuidsetup datasrc); +use FS::Record qw(qsearch qsearchs); +use FS::part_export; +use FS::cust_svc; +use FS::svc_www; + +use vars qw(%opt); +getopts("d", \%opt); + +my $user = shift or die &usage; +adminsuidsetup $user; + +#needs the export number in there somewhere too...? +my $spooldir = "/usr/local/etc/freeside/export.". datasrc. "/apache"; +mkdir $spooldir, 0700 unless -d $spooldir; + +my @exports = qsearch('part_export', { 'exporttype' => 'apache' } ); + +my $rsync = File::Rsync->new({ + rsh => 'ssh', +# dry_run => 1, +}); + +foreach my $export ( @exports ) { + + my $machine = $export->machine; + my $file = "$spooldir/$machine.conf"; + + warn "exporting apache configuration for $machine to $file\n" + if $opt{d}; + + open(HTTPD_CONF,">$file") or die "can't open $file: $!"; + + my $template = $export->option('template'); + + my @svc_www = $export->svc_x; + + foreach my $svc_www ( @svc_www ) { + use vars qw($zone $username $dir $email $config); + $zone = $svc_www->domain_record->zone; + $config = $svc_www->config; + if ( $svc_www->svc_acct ) { + $username = $svc_www->svc_acct->username; + $dir = $svc_www->svc_acct->dir; + $email = $svc_www->svc_acct->email; + } else { + $username = ''; + $dir = ''; + $email = ''; + } + + warn " adding configuration section for $zone\n" + if $opt{d}; + + print HTTPD_CONF eval(qq("$template")). "\n\n"; + } + + my $user = $export->option('user'); + my $httpd_conf = $export->option('httpd_conf'); + + warn "syncing $file to $httpd_conf on $machine\n" + if $opt{d}; + + $rsync->exec( { + src => $file, + dest => "$user\@$machine:$httpd_conf", + } ) or die "rsync to $machine failed: ". join(" / ", $rsync->err); + # warn $rsync->out; + + my $restart = $export->option('restart') || 'apachectl graceful'; + + warn "running restart command $restart on $machine\n" + if $opt{d}; + + ssh("root\@$machine", $restart); + +} + +close HTTPD_CONF; + +# ----- + +sub usage { + die "Usage:\n apache.export [ -d ] user\n"; +} + diff --git a/bin/artera.import b/bin/artera.import new file mode 100644 index 000000000..716dddad0 --- /dev/null +++ b/bin/artera.import @@ -0,0 +1,75 @@ +#!/usr/bin/perl -w + +use strict; + +use Text::CSV_XS; +use FS::UID qw(adminsuidsetup); +use FS::Record qw(qsearchs); +use FS::svc_external; +use FS::svc_domain; +use FS::svc_acct; + +$FS::svc_Common::noexport_hack = 1; + +my $svcpart = 30; + +my $user = shift + or die 'Usage:\n\n artera.import user <artera_active_orders.csv'; +adminsuidsetup $user; + +## + +my $csv = new Text::CSV_XS; + +my $header = scalar(<>); + +my( $num, $linked ) = ( 0, 0 ); + +while (<>) { + my $status = $csv->parse($_) + or die $csv->error_input; + my($serial, $keycode, $name, $ordernum, $email) = $csv->fields(); + #warn join(" - ", $serial, $keycode, $name, $ordernum, $email ). "\n"; + + $email =~ /^([^@]+)\@([^@]+)$/ + or die $email; + my($username, $domain) = ( $1, $2 ); + my $svc_domain = qsearchs('svc_domain', { 'domain' => $domain } ); + my $cust_svc = ''; + if ( $svc_domain ) { + my $svc_acct = qsearchs('svc_acct', { + 'username' => $username, + 'domsvc' => $svc_domain->svcnum, + } ); + $cust_svc = $svc_acct->cust_svc + if $svc_acct; + #} else { + # warn "can't find domain $domain\n"; + } + + my $exist = qsearchs('svc_external', { 'id' => $serial } ); + next if $exist; + + my $svc_external = new FS::svc_external { + 'svcpart' => $svcpart, + 'pkgnum' => ( $cust_svc ? $cust_svc->pkgnum : '' ), + 'id' => $serial, + 'title' => $keycode, + }; + #my $error = $svc_external->check; + my $error = $svc_external->insert; + if ( $cust_svc && $error =~ /^Already/ ) { + warn $error; + $svc_external->pkgnum(''); + $error = $svc_external->insert; + } + warn $error if $error; + + $num++; + $linked++ if $cust_svc; + #print "$num imported, $linked linked\n"; + +} + +print "$num imported, $linked linked\n"; + diff --git a/bin/backup-dvd b/bin/backup-dvd new file mode 100644 index 000000000..d0314b469 --- /dev/null +++ b/bin/backup-dvd @@ -0,0 +1,45 @@ +#!/bin/bash + +database="freeside" +DEVICE="/dev/hda" + +su freeside -c "pg_dump $database" >/var/backups/$database.sql + +DATE=$(date +%Y-%m-%d) + +#NOTE: These two paths must end in a / in +#order to correctly build up the other paths +#BACKUP_DIR="/backup/directory/" +BACKUP_DIR="/backup/" + #TEMP_BACKUP_FILES_DIR="/backup/temp/" + +BACKUP_FILE=$BACKUP_DIR"backup-"$DATE".tar.bz2" + #DATABASE_FILE=$TEMP_BACKUP_FILES_DIR"foo-"$DATE".sql" + + #These directories shouldn't end in a / although + #I don't think it will cause any problems if + #they do. There should be a space at the end though + #to ensure the database file gets concatenated correctly. + #SOURCE="/a/location /other/locations " $DATABASE_FILE + +#echo Removing old backup directories +rm -rf $BACKUP_DIR + #rm -rf $TEMP_BACKUP_FILES_DIR + +#echo Creating new backup directories +mkdir $BACKUP_DIR + #mkdir $TEMP_BACKUP_FILES_DIR + + #echo Creating database backup + #pg_dump -U username -f $DATABASE_FILE databaseName + +#echo Backing up $SOURCE to file $BACKUP_FILE +#tar -cvpl -f $BACKUP_FILE --anchored --exclude /backup / +tar -cjpl -f $BACKUP_FILE --anchored --exclude /backup / + + ##This is not necessary and possibly harmful for DVD+RW media + #echo Quick blanking media + #dvd+rw-format -blank /dev/hdc + +#echo Burning backup +growisofs -dvd-compat -Z $DEVICE -quiet -r -J $BACKUP_FILE diff --git a/bin/bill-as-nextmonth b/bin/bill-as-nextmonth new file mode 100755 index 000000000..813e84193 --- /dev/null +++ b/bin/bill-as-nextmonth @@ -0,0 +1,5 @@ +#!/bin/sh + +month=`date +%m` +nextmonth=`expr $month + 1` +/usr/local/bin/freeside-daily -d $nextmonth/1/`date +%Y` fs_daily diff --git a/bin/bill-as-nextmonth-BILL b/bin/bill-as-nextmonth-BILL new file mode 100755 index 000000000..91e943110 --- /dev/null +++ b/bin/bill-as-nextmonth-BILL @@ -0,0 +1,5 @@ +#!/bin/sh + +month=`date +%m` +nextmonth=`expr $month + 1` +/usr/local/bin/freeside-daily -d $nextmonth/1/`date +%Y` -p BILL fs_daily diff --git a/bin/bill-as-nextyear b/bin/bill-as-nextyear new file mode 100755 index 000000000..63c4ad2be --- /dev/null +++ b/bin/bill-as-nextyear @@ -0,0 +1,5 @@ +#!/bin/sh + +year=`date +%Y` +nextyear=`expr $year + 1` +/usr/local/bin/freeside-daily -d 1/1/$nextyear fs_daily diff --git a/bin/bill-as-nextyear-BILL b/bin/bill-as-nextyear-BILL new file mode 100755 index 000000000..0d77dd0d6 --- /dev/null +++ b/bin/bill-as-nextyear-BILL @@ -0,0 +1,5 @@ +#!/bin/sh + +year=`date +%Y` +nextyear=`expr $year + 1` +/usr/local/bin/freeside-daily -d 1/1/$nextyear -p BILL fs_daily diff --git a/bin/bill-for-nextmonth b/bin/bill-for-nextmonth new file mode 100755 index 000000000..e1a33764e --- /dev/null +++ b/bin/bill-for-nextmonth @@ -0,0 +1,5 @@ +#!/bin/sh + +month=`date +%m` +nextmonth=`expr $month + 1` +/usr/local/bin/freeside-daily -d $nextmonth/1/`date +%Y` -n fs_daily diff --git a/bin/bill-for-nextyear b/bin/bill-for-nextyear new file mode 100755 index 000000000..1430a5898 --- /dev/null +++ b/bin/bill-for-nextyear @@ -0,0 +1,5 @@ +#!/bin/sh + +year=`date +%Y` +nextyear=`expr $year + 1` +/usr/local/bin/freeside-daily -d 1/1/$nextyear -n fs_daily diff --git a/bin/bill-nextmonth b/bin/bill-nextmonth new file mode 100755 index 000000000..813e84193 --- /dev/null +++ b/bin/bill-nextmonth @@ -0,0 +1,5 @@ +#!/bin/sh + +month=`date +%m` +nextmonth=`expr $month + 1` +/usr/local/bin/freeside-daily -d $nextmonth/1/`date +%Y` fs_daily diff --git a/bin/bill-nextyear b/bin/bill-nextyear new file mode 100755 index 000000000..63c4ad2be --- /dev/null +++ b/bin/bill-nextyear @@ -0,0 +1,5 @@ +#!/bin/sh + +year=`date +%Y` +nextyear=`expr $year + 1` +/usr/local/bin/freeside-daily -d 1/1/$nextyear fs_daily diff --git a/bin/billco-upload b/bin/billco-upload new file mode 100644 index 000000000..ce4a43d5f --- /dev/null +++ b/bin/billco-upload @@ -0,0 +1,20 @@ +#!/bin/sh + +AGENTNUMS="1 2 3" + +date=`date +"%Y%m%d"` +dir="/usr/local/etc/freeside/export.DBI:Pg:dbname=freeside/cust_bill" +cd "$dir" + +for AGENTNUM in $AGENTNUMS; do + + for a in header detail; do + mv agentnum$AGENTNUM-$a.csv agentnum$AGENTNUM-$date-$a.csv + done + + zip agentnum$AGENTNUM-$date.zip agentnum$AGENTNUM-$date-header.csv agentnum$AGENTNUM-$date-detail.csv + + echo $dir/agentnum$AGENTNUM-$date.zip + +done + diff --git a/bin/bind.export b/bin/bind.export new file mode 100755 index 000000000..286e43a2d --- /dev/null +++ b/bin/bind.export @@ -0,0 +1,195 @@ +#!/usr/bin/perl -w + +use strict; +use File::Path; +use File::Rsync; +use Net::SSH qw(ssh); +use FS::UID qw(adminsuidsetup datasrc); +use FS::Record qw(qsearch qsearchs); +use FS::part_export; +use FS::cust_pkg; +use FS::cust_svc; +use FS::svc_domain; + +my $user = shift or die &usage; +adminsuidsetup $user; + +my $spooldir = "/usr/local/etc/freeside/export.". datasrc. "/bind"; +mkdir $spooldir, 0700 unless -d $spooldir; + +my @exports = qsearch('part_export', { 'exporttype' => 'bind' } ); +my @sexports = qsearch('part_export', { 'exporttype' => 'bind_slave' } ); + +my $rsync = File::Rsync->new({ + rsh => 'ssh', +# dry_run => 1, +}); + +foreach my $export ( @exports ) { + + my $machine = $export->machine; + my $prefix = "$spooldir/$machine"; + + my $bind_rel = $export->option('bind_release'); + my $ndc_cmd = $export->option('reload') + || ( ($bind_rel eq 'BIND9') ? 'rndc' : 'ndc' ); + my $minttl = $export->option('bind9_minttl'); + + #prevent old domain files from piling up + #rmtree "$prefix" or die "can't rmtree $prefix.db: $!"; + + mkdir $prefix, 0700 unless -d $prefix; + + open(NAMED_CONF,">$prefix/named.conf") + or die "can't open $prefix/named.conf: $!"; + + if ( -e "$prefix/named.conf.HEADER" ) { + open(CONF_HEADER,"<$prefix/named.conf.HEADER") + or die "can't open $prefix/named.conf.HEADER: $!"; + while (<CONF_HEADER>) { print NAMED_CONF $_; } + close CONF_HEADER; + } + + my $zonepath = $export->option('zonepath'); + $zonepath =~ s/\/$//; + + my @svc_domain = $export->svc_x; + + foreach my $svc_domain ( @svc_domain ) { + my $domain = $svc_domain->domain; + my @masters = qsearch('domain_record', { + 'svcnum' => $svc_domain->svcnum, + 'rectype' => '_mstr', + } ); + if ( @masters ) { + my $masters = join('; ', map { $_->recdata } @masters ); + + print NAMED_CONF <<END; +zone "$domain" { + type slave; + file "db.$domain"; + masters { $masters; }; +}; + +END + + } else { + + print NAMED_CONF <<END; +zone "$domain" { + type master; + file "$zonepath/db.$domain"; +}; + +END + + open (DB_MASTER,">$prefix/db.$domain") + or die "can't open $prefix/db.$domain: $!"; + + if ($bind_rel eq 'BIND9') { + print DB_MASTER "\$TTL $minttl\n\$ORIGIN $domain.\n"; + } + + my @domain_records = + qsearch('domain_record', { 'svcnum' => $svc_domain->svcnum } ); + foreach my $domain_record ( + sort { $b->rectype cmp $a->rectype } @domain_records + ) { + #if ( $domain_record->rectype eq 'SOA' ) { + # print DB_MASTER join("\t", $domain_record-> reczone + #} else { + print DB_MASTER join("\t", + map { $domain_record->getfield($_) } + qw( reczone recaf rectype recdata ) + ), "\n"; + #} + } + + close DB_MASTER; + + } + + } + + $rsync->exec( { + src => "$prefix/", + recursive => 1, + dest => "root\@$machine:$zonepath/", + exclude => [qw( *.import named.conf.HEADER named.conf )], + } ) or die "rsync to $machine failed: ". join(" / ", $rsync->err); + # warn $rsync->out; + + $rsync->exec( { + src => "$prefix/named.conf", + dest => "root\@$machine:". $export->option('named_conf'), + } ) or die "rsync to $machine failed: ". join(" / ", $rsync->err); +# warn $rsync->out; + + ssh("root\@$machine", "$ndc_cmd reload"); + +} + +close NAMED_CONF; + +foreach my $sexport ( @sexports ) { #false laziness with above + + my $machine = $sexport->machine; + my $prefix = "$spooldir/$machine"; + + my $bind_rel = $sexport->option('bind_release'); + my $ndc_cmd = ($bind_rel eq 'BIND9') ? 'rndc' : 'ndc'; + + #prevent old domain files from piling up + #rmtree "$prefix" or die "can't rmtree $prefix.db: $!"; + + mkdir $prefix, 0700 unless -d $prefix; + + open(NAMED_CONF,">$prefix/named.conf") + or die "can't open $prefix/named.conf: $!"; + + if ( -e "$prefix/named.conf.HEADER" ) { + open(CONF_HEADER,"<$prefix/named.conf.HEADER") + or die "can't open $prefix/named.conf.HEADER: $!"; + while (<CONF_HEADER>) { print NAMED_CONF $_; } + close CONF_HEADER; + } + + my $masters = $sexport->option('master'); + + #false laziness with freeside-sqlradius-reset + my @svc_domain = + map { qsearchs('svc_domain', { 'svcnum' => $_->svcnum } ) } + map { qsearch('cust_svc', { 'svcpart' => $_->svcpart } ) } + grep { qsearch('cust_svc', { 'svcpart' => $_->svcpart } ) } + $sexport->export_svc; + + foreach my $svc_domain ( @svc_domain ) { + my $domain = $svc_domain->domain; + print NAMED_CONF <<END; +zone "$domain" { + type slave; + file "db.$domain"; + masters { $masters; }; +}; + +END + + } + + $rsync->exec( { + src => "$prefix/named.conf", + dest => "root\@$machine:". $sexport->option('named_conf'), + } ) or die "rsync to $machine failed: ". join(" / ", $rsync->err); +# warn $rsync->out; + + ssh("root\@$machine", "$ndc_cmd reload"); + +} +close NAMED_CONF; + +# ----- + +sub usage { + die "Usage:\n bind.export user\n"; +} + diff --git a/bin/bind.import b/bin/bind.import new file mode 100755 index 000000000..1cdf5672c --- /dev/null +++ b/bin/bind.import @@ -0,0 +1,234 @@ +#!/usr/bin/perl -w +# +# REQUIRED: +# -p: part number for domains +# +# -n: named.conf file (or an include file with zones you want to import), +# for example root@ns.isp.com:/var/named/named.conf +# +# OPTIONAL: +# -d: dry-run, debug: don't insert any records, just dump debugging output +# -s: import slave zones as master. useful if you need to recreate your +# primary nameserver from a secondary +# -c dir: override patch for downloading zone files (for example, when +# downloading zone files from chrooted bind) +# +# need to manually put header in +# /usr/local/etc/freeside/export.<datasrc./bind/<machine>/named.conf.HEADER +# (or, nowadays, better just to include the file freeside exports) + +use strict; + +use vars qw($domain_svcpart); + +use Getopt::Std; +use Data::Dumper; +#use BIND::Conf_Parser; +#use DNS::ZoneParse 0.81; + +use Net::SCP qw(scp iscp); + +use FS::UID qw(adminsuidsetup datasrc); +use FS::Record qw(qsearch); #qsearchs); +#use FS::svc_acct_sm; +use FS::svc_domain; +use FS::domain_record; +#use FS::svc_acct; +#use FS::part_svc; + +use vars qw($opt_p $opt_n $opt_s $opt_c $opt_d); +getopts("p:n:sc:d"); + +my $user = shift or die &usage; +adminsuidsetup $user; + +$FS::svc_Common::noexport_hack = 1; +$FS::domain_record::noserial_hack = 1; + +use vars qw($spooldir); +$spooldir = "/usr/local/etc/freeside/export.". datasrc. "/bind"; +mkdir $spooldir unless -d $spooldir; + +$domain_svcpart = $opt_p; + +my $named_conf = $opt_n; + +use vars qw($named_machine $prefix); +$named_machine = (split(/:/, $named_conf))[0]; +my $pnamed_machine = $named_machine; +$pnamed_machine =~ s/^[\w\-]+\@//; +$prefix = "$spooldir/$pnamed_machine"; +mkdir $prefix unless -d $prefix; + +#iscp("$named_conf","$prefix/named.conf.import"); +scp("$named_conf","$prefix/named.conf.import"); + +## + +$FS::svc_domain::whois_hack=1; + +my $p = Parser->new; +$p->parse_file("$prefix/named.conf.import"); + +print "\nBIND import completed.\n"; + +## + +sub usage { + die "Usage:\n\n bind.import -p partnum -n \"user\@machine:/path/to/named.conf\" [ -s ] [ -c chroot_dir ] [ -f ] user\n"; +} + +######## +BEGIN { + + package Parser; + use BIND::Conf_Parser; + use vars qw(@ISA $named_dir); + @ISA = qw(BIND::Conf_Parser); + + $named_dir = 'COULD_NOT_FIND_NAMED_DIRECTORY_TRY_SETTING_-C_OPTION'; + sub handle_option { + my($self, $option, $argument) = @_; + return unless $option eq "directory"; + $named_dir = $argument; + #warn "found named dir: $named_dir\n"; + } + + sub handle_zone { + my($self, $name, $class, $type, $options) = @_; + return unless $class eq 'in'; + return if grep { $name eq $_ } (qw( + . localhost 127.in-addr.arpa 0.in-addr.arpa 255.in-addr.arpa + 0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.ip6.arpa + 0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.ip6.int + )); + + use FS::Record qw(qsearchs); + use FS::svc_domain; + + my $domain = + qsearchs('svc_domain', { 'domain' => $name } ) + || new FS::svc_domain( { + svcpart => $main::domain_svcpart, + domain => $name, + action => 'N', + } ); + unless ( $domain->svcnum ) { + my $error = $domain->insert; + die $error if $error; + } + + if ( $type eq 'slave' && !$main::opt_s ) { + + if ( $main::opt_d ) { + + use Data::Dumper; + print "$name: ". Dumper($options); + + } else { + + foreach my $master ( @{ $options->{masters} } ) { + my $domain_record = new FS::domain_record( { + 'svcnum' => $domain->svcnum, + 'reczone' => '@', + 'recaf' => 'IN', + 'rectype' => '_mstr', + 'recdata' => $master, + } ); + my $error = $domain_record->insert; + die $error if $error; + } + + } + + } elsif ( $type eq 'master' || ( $type eq 'slave' && $main::opt_s ) ) { + + my $file = $options->{file}; + + use File::Basename; + my $basefile = basename($file); + my $sourcefile = $file; + if ( $main::opt_c ) { + $sourcefile = "$main::opt_c/$sourcefile" if $main::opt_c; + } else { + $sourcefile = "$named_dir/$sourcefile" unless $file =~ /^\//; + } + + use Net::SCP qw(iscp scp); + #iscp("$main::named_machine:$sourcefile", + # "$main::prefix/$basefile.import"); + scp("$main::named_machine:$sourcefile", + "$main::prefix/$basefile.import"); + + use DNS::ZoneParse 0.84; + my $zone = DNS::ZoneParse->new("$main::prefix/$basefile.import"); + + my $dump = $zone->dump; + + if ( $main::opt_d ) { + + use Data::Dumper; + print "$name: ". Dumper($dump); + + } else { + + foreach my $rectype ( keys %$dump ) { + if ( $rectype =~ /^SOA$/i ) { + my $rec = $dump->{$rectype}; + $rec->{email} =~ s/\@/\./; + my $domain_record = new FS::domain_record( { + 'svcnum' => $domain->svcnum, + 'reczone' => $rec->{origin}, + 'recaf' => 'IN', + 'rectype' => $rectype, + 'recdata' => + $rec->{primary}. ' '. $rec->{email}. ' ( '. + join(' ', map $rec->{$_}, + qw( serial refresh retry expire minimumTTL ) ). + ' )', + } ); + my $error = $domain_record->insert; + die $error if $error; + } else { + #die $dump->{$rectype}; + + my $datasub; + if ( $rectype =~ /^MX$/i ) { + $datasub = sub { $_[0]->{priority}. ' '. $_[0]->{host}; }; + } elsif ( $rectype =~ /^TXT$/i ) { + $datasub = sub { $_[0]->{text}; }; + } else { + $datasub = sub { $_[0]->{host}; }; + } + + foreach my $rec ( @{ $dump->{$rectype} } ) { + my $domain_record = new FS::domain_record( { + 'svcnum' => $domain->svcnum, + 'reczone' => $rec->{name}, + 'recaf' => $rec->{class} || 'IN', + 'rectype' => $rectype, + 'recdata' => &{$datasub}($rec), + } ); + my $error = $domain_record->insert; + if ( $error ) { + warn "$error inserting ". + $rec->{name}. ' . '. $domain->domain. "\n"; + warn Dumper($rec); + #system('cat',"$main::prefix/$basefile.import"); + die; + } + } + } + } + + } + + #} else { + # die "unrecognized type $type\n"; + } + + } + +} +######### + diff --git a/bin/breakdown-bill-applications b/bin/breakdown-bill-applications new file mode 100644 index 000000000..44c3e36b0 --- /dev/null +++ b/bin/breakdown-bill-applications @@ -0,0 +1,25 @@ +#!/usr/bin/perl -w + +use strict; +use FS::UID qw(adminsuidsetup dbh); +use FS::Record qw( qsearch ); +use FS::cust_bill_pay; +use FS::cust_credit_bill; + +$FS::CurrentUser::upgrade_hack = 1; +adminsuidsetup(shift) or die "Usage: breakdown-bill-applications username\n"; + +#quick and dirty conversion script if you have enough memory to throw at it + +my @tables = qw( cust_bill_pay cust_credit_bill ); + +my @apps = (); +foreach my $table { + push @apps, qsearch($table, + + +) { + +} + +foreach my $cust_bill_ diff --git a/bin/bsdshell.export b/bin/bsdshell.export new file mode 100755 index 000000000..6e0d1037e --- /dev/null +++ b/bin/bsdshell.export @@ -0,0 +1,114 @@ +#!/usr/bin/perl -w + +# bsdshell export + +use strict; +use File::Rsync; +use Net::SSH qw(ssh); +use FS::UID qw(adminsuidsetup datasrc); +use FS::Record qw(qsearch qsearchs); +use FS::part_export; +use FS::cust_svc; +use FS::svc_acct; + +my @saltset = ( 'a'..'z' , 'A'..'Z' , '0'..'9' , '.' , '/' ); + +my $user = shift or die &usage; +adminsuidsetup $user; + +my $spooldir = "/usr/local/etc/freeside/export.". datasrc; +#my $spooldir = "/usr/local/etc/freeside/export.". datasrc. "/shell"; + +my @bsd_exports = qsearch('part_export', { 'exporttype' => 'bsdshell' } ); + +my $rsync = File::Rsync->new({ + rsh => 'ssh', +# dry_run => 1, +}); + +foreach my $export ( @bsd_exports ) { + my $machine = $export->machine; + my $prefix = "$spooldir/$machine"; + mkdir $prefix, 0700 unless -d $prefix; + + #LOCKING!!! + + ( open(MASTER,">$prefix/master.passwd") + #!!! and flock(MASTER,LOCK_EX|LOCK_NB) + ) or die "Can't open $prefix/master.passwd: $!"; + ( open(PASSWD,">$prefix/passwd") + #!!! and flock(PASSWD,LOCK_EX|LOCK_NB) + ) or die "Can't open $prefix/passwd: $!"; + + chmod 0644, "$prefix/passwd"; + chmod 0600, "$prefix/master.passwd"; + + my @svc_acct = $export->svc_x; + + next unless @svc_acct; + + foreach my $svc_acct ( sort { $a->uid <=> $b->uid } @svc_acct ) { + + my $password = $svc_acct->_password; + my $cpassword; + #if ( ( length($password) <= 8 ) + if ( ( length($password) <= 12 ) + && ( $password ne '*' ) + && ( $password ne '!!' ) + && ( $password ne '' ) + ) { + $cpassword=crypt($password, + $saltset[int(rand(64))].$saltset[int(rand(64))] + ); + # MD5 !!!! + } else { + $cpassword=$password; + } + + ### + # FORMAT OF THE PASSWD FILE HERE + print PASSWD join(":", + $svc_acct->username, + 'x', # "##". $username, + $svc_acct->uid, + $svc_acct->gid, + $svc_acct->finger, + $svc_acct->dir, + $svc_acct->shell, + ), "\n"; + + ### + # FORMAT OF FreeBSD MASTER PASSWD FILE HERE + print MASTER join(":", + $svc_acct->username, # User name + $cpassword, # Encrypted password + $svc_acct->uid, # User ID + $svc_acct->gid, # Group ID + "", # Login Class + "0", # Password Change Time + "0", # Password Expiration Time + $svc_acct->finger, # Users name + $svc_acct->dir, # Users home directory + $svc_acct->shell, # shell + ), "\n" ; + + } + + #!!! flock(MASTER,LOCK_UN); + #!!! flock(PASSWD,LOCK_UN); + close MASTER; + close PASSWD; + + $rsync->exec( { + src => "$prefix/passwd", + dest => "root\@$machine:/etc/passwd" + } ) or die "rsync to $machine failed: ". join(" / ", $rsync->err); + + $rsync->exec( { + src => "$prefix/master.passwd", + dest => "root\@$machine:/etc/master.passwd.new" + } ) or die "rsync to $machine failed: ". join(" / ", $rsync->err); + ssh("root\@$machine", "pwd_mkdb /etc/master.passwd.new"); + + # UNLOCK!! +} diff --git a/bin/cdr_calltype.import b/bin/cdr_calltype.import new file mode 100755 index 000000000..a998284f6 --- /dev/null +++ b/bin/cdr_calltype.import @@ -0,0 +1,41 @@ +#!/usr/bin/perl -w +# +# bin/cdr_calltype.import ivan ~ivan/convergent/newspecs/fixed_inbound/calltypes.csv + +use strict; +use FS::UID qw(dbh adminsuidsetup); +use FS::cdr_calltype; + +my $user = shift or die &usage; +adminsuidsetup $user; + +while (<>) { + + chomp; + my $line = $_; + + #$line =~ /^(\d+),"([^"]+)"$/ or do { + $line =~ /^(\d+),"([^"]+)"/ or do { + warn "unparsable line: $line\n"; + next; + }; + + my $cdr_calltype = new FS::cdr_calltype { + 'calltypenum' => $1, + 'calltypename' => $2, + }; + + #my $error = $cdr_calltype->check; + my $error = $cdr_calltype->insert; + if ( $error ) { + warn "********** $error FOR LINE: $line\n"; + dbh->commit; + #my $wait = scalar(<STDIN>); + } + +} + +sub usage { + "Usage:\n\ncdr_calltype.import username filename ...\n"; +} + diff --git a/bin/cdr_upstream_rate.import b/bin/cdr_upstream_rate.import new file mode 100755 index 000000000..fda3883b5 --- /dev/null +++ b/bin/cdr_upstream_rate.import @@ -0,0 +1,142 @@ +#!/usr/bin/perl -w +# +# Usage: bin/cdr_upstream_rate.import username ratenum filename +# +# records will be imported into cdr_upstream_rate, rate_detail and rate_region +# +# Example: bin/cdr_upstream_rate.import ivan 1 ~ivan/convergent/sample_rate_table.csv +# +# username: a freeside login (from /usr/local/etc/freeside/mapsecrets) +# ratenum: rate plan (FS::rate) created with the web UI +# filename: CSV file +# +# the following fields are currently used: +# - Class Code => cdr_upstream_rate.rateid +# - Description => rate_region.regionname +# (rate_detail->dest_region) +# - 1_rate => ( * 60 / 1_rate_seconds ) => rate_detail.min_charge +# - 1_rate_seconds => (used above) +# - 1_second_increment => rate_detail.sec_granularity +# +# the following fields are not (yet) used: +# - Flagfall => what's this for? +# +# - 1_cap_time => freeside doesn't have voip time caps yet... +# - 1_cap_cost => freeside doesn't have voip cost caps yet... +# - 1_repeat => not sure what this is for, sample data is all 0 +# +# - 2_rate => \ +# - 2_rate_seconds => | +# - 2_second_increment => | not sure what the second set of rate data +# - 2_cap_time => | is supposed to be for... +# - 2_cap_cost => | +# - 2_repeat => / +# +# - Carrier => probably not needed? +# - Start Date => not necessary? + +use strict; +use vars qw( $DEBUG ); +use Text::CSV_XS; +use FS::UID qw(dbh adminsuidsetup); +use FS::Record qw(qsearchs); +use FS::rate; +use FS::cdr_upstream_rate; +use FS::rate_detail; +use FS::rate_region; + +$DEBUG = 1; + +my $user = shift or die &usage; +adminsuidsetup $user; + +my $ratenum = shift or die &usage; + +my $rate = qsearchs( 'rate', { 'ratenum' => $ratenum } ); +die "rate plan $ratenum not found in rate table\n" + unless $rate; + +my $csv = new Text::CSV_XS; +my $hline = scalar(<>); +chomp($hline); +$csv->parse($hline) or die "can't parse header: $hline\n"; +my @header = $csv->fields(); + +$FS::UID::AutoCommit = 0; + +while (<>) { + + chomp; + my $line = $_; + +# #$line =~ /^(\d+),"([^"]+)"$/ or do { +# #} +# $line =~ /^(\d+),"([^"]+)"/ or do { +# warn "unparsable line: $line\n"; +# next; +# }; + + $csv->parse($line) or die "can't parse line: $line\n"; + my @line = $csv->fields(); + + my %hash = map { $_ => shift(@line) } @header; + + warn join('', map { "$_ => $hash{$_}\n" } keys %hash ) + if $DEBUG > 1; + + my $rate_region = new FS::rate_region { + 'regionname' => $hash{'Description'} + }; + + my $error = $rate_region->insert; + if ( $error ) { + dbh->rollback; + die "error inserting into rate_region: $error\n"; + } + my $dest_regionnum = $rate_region->regionnum; + warn "rate_region $dest_regionnum inserted\n" + if $DEBUG; + + my $rate_detail = new FS::rate_detail { + 'ratenum' => $ratenum, + 'dest_regionnum' => $dest_regionnum, + 'min_included' => 0, + #'min_charge', => sprintf('%.5f', 60 * $hash{'1_rate'} / $hash{'1_rate_seconds'} ), + 'min_charge', => sprintf('%.5f', $hash{'1_rate'} / + ( $hash{'1_rate_seconds'} / 60 ) + ), + 'sec_granularity' => $hash{'1_second_increment'}, + }; + $error = $rate_detail->insert; + if ( $error ) { + dbh->rollback; + die "error inserting into rate_detail: $error\n"; + } + my $ratedetailnum = $rate_detail->ratedetailnum; + warn "rate_detail $ratedetailnum inserted\n" + if $DEBUG; + + my $cdr_upstream_rate = new FS::cdr_upstream_rate { + 'upstream_rateid' => $hash{'Class Code'}, + 'ratedetailnum' => $rate_detail->ratedetailnum, + }; + $error = $cdr_upstream_rate->insert; + if ( $error ) { + dbh->rollback; + die "error inserting into cdr_upstream_rate: $error\n"; + } + warn "cdr_upstream_rate ". $cdr_upstream_rate->upstreamratenum. " inserted\n" + if $DEBUG; + + dbh->commit or die "can't commit: ". dbh->errstr; + + warn "\n" if $DEBUG; + +} + +dbh->commit or die "can't commit: ". dbh->errstr; + +sub usage { + "Usage:\n\ncdr_upstream_rate.import username ratenum filename\n"; +} + diff --git a/bin/create-fetchmailrc b/bin/create-fetchmailrc new file mode 100644 index 000000000..11bde0ce3 --- /dev/null +++ b/bin/create-fetchmailrc @@ -0,0 +1,47 @@ +#!/usr/bin/perl -w +# this quick hack helps you generate/maintain .fetchmailrc files from +# FS::acct_snarf data. it is run from a shellcommands export as: +# create-fetchmailrc $username $dir $snarf_machine1 $snarf_username1 $snarf__password1 $snarf_machine2 $snarf_username2 $snarf__password2 ... + +use strict; +use POSIX qw( setuid setgid ); + +my $header = <<END; +# Configuration created by create-fetchmailrc +set postmaster "postmaster" +set bouncemail +set no spambounce +set properties "" +set daemon 240 +END + +my $username = shift @ARGV or die "no username specified\n"; +my $homedir = shift @ARGV or die "no homedir specified\n"; +my $filename = "$homedir/.fetchmailrc"; + +my $gid = scalar(getgrnam($username)) or die "can't find $username's gid\n"; +my $uid = scalar(getpwnam($username)) or die "can't find $username's uid\n"; + +exit unless $ARGV[0]; + +open(FETCHMAILRC, ">$filename") or die "can't open $filename: $!\n"; +chown $uid, $gid, $filename or die "can't chown $uid.$gid $filename: $!\n"; +chmod 0600, $filename or die "can't chmod 600 $filename: $!\n"; +print FETCHMAILRC $header; + +while ($ARGV[0]) { + my( $s_machine, $s_username, $s_password ) = splice( @ARGV, 0, 3 ); + print FETCHMAILRC <<END; +poll $s_machine + user '$s_username' there with password '$s_password' is '$username' here +END +} + +close FETCHMAILRC; + +setgid($gid) or die "can't setgid $gid\n"; +setuid($uid) or die "can't setuid $uid\n"; +$ENV{HOME} = $homedir; + +system(qq(fetchmail -a -K --antispam "550,451" -d 180 -f $filename)); + diff --git a/bin/customer-faker b/bin/customer-faker new file mode 100755 index 000000000..d57e5e1de --- /dev/null +++ b/bin/customer-faker @@ -0,0 +1,122 @@ +#!/usr/bin/perl + +use strict; +use Getopt::Std; +use Data::Faker; +use Business::CreditCard; +use FS::UID qw(adminsuidsetup); +use FS::Record qw(qsearch); +use FS::cust_main; +use FS::cust_pkg; +use FS::svc_acct; + +my $agentnum = 1; +my $refnum = 1; + +my @pkgs = ( 2, 3, 4 ); +#my @pkgs = ( 4, 5, 6 ); +my $svcpart = 2; + +use vars qw( $opt_p ); +getopts('p:'); + +my $user = shift or die &usage; +my $num = shift or die &usage; +adminsuidsetup($user); + +my $onum = $num; +my $start = time; + +my @states = qw( AL AK AS AZ AR CA CO CT DE DC FL GA GU HI ID IL IN IA KS KY LA ME MD MA MI MN MS MO MT NE NV NH NJ NM NY NC ND MP OH OK OR PA PR RI SC SD TN TX UT VT VI VA WA WV WI WY ); +#FM MH + +until ( $num-- <= 0 ) { + + my $faker = new Data::Faker; + + my $cust_main = new FS::cust_main { + 'agentnum' => $agentnum, + 'refnum' => $refnum, + 'first' => $faker->first_name, + 'last' => $faker->last_name, + 'company' => ( $num % 2 ? $faker->company. ', '. $faker->company_suffix : '' ), #half with companies.. + 'address1' => $faker->street_address, + 'city' => 'Tofutown', #missing, so everyone is from tofutown# $faker->city, + #'state' => $faker->us_state_abbr, + 'state' => $states[ int(rand($#states)) ], + 'zip' => $faker->us_zip_code, + 'country' => 'US', + 'daytime' => $faker->phone_number, + 'night' => $faker->phone_number, + #forget it, these can have extensions# 'fax' => ( $num % 2 ? $faker->phone_number : '' ), #ditto + #bah, forget shipping addresses + 'payby' => 'BILL', + 'payip' => $faker->ip_address, + }; + + if ( $opt_p eq 'CARD' || ( !$opt_p && rand() > .33 ) ) { + $cust_main->payby('CARD'); + my $cardnum = '4123'. sprintf('%011u', int(rand(100000000000)) ); + $cust_main->payinfo( $cardnum. generate_last_digit($cardnum) ); + $cust_main->paydate( '2009-05-01' ); + } elsif ( $opt_p eq 'CHEK' || ( !$opt_p && rand() > .66 ) ) { + $cust_main->payby('CHEK'); + my $payinfo = sprintf('%7u@%09u', int(rand(10000000)), int(rand(1000000000)) ); + $cust_main->payinfo($payinfo); + $cust_main->payname( 'First International Bank of Testing' ); + } + + # could insert invoicing_list and other stuff too.. hell, could insert + # packages, services, more + # but i just wanted 10k customers to test the pager and this was good enough + # not anymore, here's some services and packages + + my $now = time; + my $year = 31556736; #60*60*24*365.24 + my $setup = $now - int(rand($year)); + + my $cust_pkg = new FS::cust_pkg { + 'pkgpart' => $pkgs[ int(rand(scalar(@pkgs))) ], + + #some dates in here would be nice + 'setup' => $setup, + #'last_bill' + #'bill' + #'susp' + #'expire' + #'cancel' + }; + + my $svc_acct = new FS::svc_acct { + 'svcpart' => $svcpart, + 'username' => $faker->username, + }; + + while ( qsearch( 'svc_acct', { 'username' => $svc_acct->username } ) ) { + my $username = $svc_acct->username; + $username++; + $svc_acct->username($username); + } + + use Tie::RefHash; + tie my %hash, 'Tie::RefHash', + $cust_pkg => [ $svc_acct ], + ; + + my $error = $cust_main->insert( \%hash ); + die $error if $error; + +} + +my $end = time; + +my $sec = $end-$start; +$sec=1 if $sec==0; +my $persec = $onum / $sec; +print "$onum customers inserted in $sec seconds ($persec customers/sec)\n"; + +#--- + +sub usage { + die "Usage:\n\n customer-faker [ -p payby ] user num_fakes\n"; +} diff --git a/bin/expand-country b/bin/expand-country new file mode 100755 index 000000000..c6f2a1f09 --- /dev/null +++ b/bin/expand-country @@ -0,0 +1,29 @@ +#!/usr/bin/perl -w + +use strict; +use Locale::SubCountry; +use FS::UID qw(adminsuidsetup); +use FS::Setup; +use FS::Record qw(qsearch); +use FS::cust_main_county; + +my $user = shift or die &usage; +my $country = shift or die &usage; + +adminsuidsetup($user); + +my @country = qsearch('cust_main_county', { 'country' => $country } ); +die "unknown country $country" unless (@country); +#die "$country already expanded" if scalar(@country) > 1; + +foreach my $cust_main_county ( @country ) { + my $error = $cust_main_county->delete; + die $error if $error; +} + +FS::Setup::_add_country($country); + +sub usage { + die "Usage:\n\n expand-country user countrycode\n"; +} + diff --git a/bin/explain-ar-total.sql b/bin/explain-ar-total.sql new file mode 100644 index 000000000..f1544303b --- /dev/null +++ b/bin/explain-ar-total.sql @@ -0,0 +1,976 @@ +EXPLAIN SELECT ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay + WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill + WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill LEFT JOIN cust_main USING ( custnum ) WHERE cust_bill._date > ( EXTRACT( EPOCH FROM now() ) - 2592000 ) AND ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay + WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill + WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) + + ( SELECT COALESCE(SUM(refund + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) + ,0 + ) + ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) + - ( SELECT COALESCE(SUM(amount + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_credit.crednum = cust_credit_refund.crednum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_bill + WHERE cust_credit.crednum = cust_credit_bill.crednum ) + ,0 + ) + ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) + - ( SELECT COALESCE(SUM(paid + - COALESCE( + ( SELECT SUM(amount) FROM cust_bill_pay + WHERE cust_pay.paynum = cust_bill_pay.paynum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_pay.paynum = cust_pay_refund.paynum ) + ,0 + ) + ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) + > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) + + ( SELECT COALESCE(SUM(refund + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) + ,0 + ) + ), 0) FROM cust_refund LEFT JOIN cust_main USING ( custnum ) WHERE cust_refund._date > ( EXTRACT( EPOCH FROM now() ) - 2592000 ) AND ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay + WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill + WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) + + ( SELECT COALESCE(SUM(refund + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) + ,0 + ) + ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) + - ( SELECT COALESCE(SUM(amount + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_credit.crednum = cust_credit_refund.crednum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_bill + WHERE cust_credit.crednum = cust_credit_bill.crednum ) + ,0 + ) + ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) + - ( SELECT COALESCE(SUM(paid + - COALESCE( + ( SELECT SUM(amount) FROM cust_bill_pay + WHERE cust_pay.paynum = cust_bill_pay.paynum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_pay.paynum = cust_pay_refund.paynum ) + ,0 + ) + ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) + > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) + - ( SELECT COALESCE(SUM(amount + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_credit.crednum = cust_credit_refund.crednum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_bill + WHERE cust_credit.crednum = cust_credit_bill.crednum ) + ,0 + ) + ), 0) FROM cust_credit LEFT JOIN cust_main USING ( custnum ) WHERE cust_credit._date > ( EXTRACT( EPOCH FROM now() ) - 2592000 ) AND ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay + WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill + WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) + + ( SELECT COALESCE(SUM(refund + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) + ,0 + ) + ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) + - ( SELECT COALESCE(SUM(amount + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_credit.crednum = cust_credit_refund.crednum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_bill + WHERE cust_credit.crednum = cust_credit_bill.crednum ) + ,0 + ) + ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) + - ( SELECT COALESCE(SUM(paid + - COALESCE( + ( SELECT SUM(amount) FROM cust_bill_pay + WHERE cust_pay.paynum = cust_bill_pay.paynum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_pay.paynum = cust_pay_refund.paynum ) + ,0 + ) + ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) + > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) + - ( SELECT COALESCE(SUM(paid + - COALESCE( + ( SELECT SUM(amount) FROM cust_bill_pay + WHERE cust_pay.paynum = cust_bill_pay.paynum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_pay.paynum = cust_pay_refund.paynum ) + ,0 + ) + ), 0) FROM cust_pay LEFT JOIN cust_main USING ( custnum ) WHERE cust_pay._date > ( EXTRACT( EPOCH FROM now() ) - 2592000 ) AND ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay + WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill + WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) + + ( SELECT COALESCE(SUM(refund + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) + ,0 + ) + ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) + - ( SELECT COALESCE(SUM(amount + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_credit.crednum = cust_credit_refund.crednum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_bill + WHERE cust_credit.crednum = cust_credit_bill.crednum ) + ,0 + ) + ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) + - ( SELECT COALESCE(SUM(paid + - COALESCE( + ( SELECT SUM(amount) FROM cust_bill_pay + WHERE cust_pay.paynum = cust_bill_pay.paynum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_pay.paynum = cust_pay_refund.paynum ) + ,0 + ) + ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) + > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) + AS balance_0_30, ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay + WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill + WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill LEFT JOIN cust_main USING ( custnum ) WHERE cust_bill._date <= ( EXTRACT( EPOCH FROM now() ) - 2592000 ) AND cust_bill._date > ( EXTRACT( EPOCH FROM now() ) - 5184000 ) AND ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay + WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill + WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) + + ( SELECT COALESCE(SUM(refund + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) + ,0 + ) + ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) + - ( SELECT COALESCE(SUM(amount + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_credit.crednum = cust_credit_refund.crednum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_bill + WHERE cust_credit.crednum = cust_credit_bill.crednum ) + ,0 + ) + ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) + - ( SELECT COALESCE(SUM(paid + - COALESCE( + ( SELECT SUM(amount) FROM cust_bill_pay + WHERE cust_pay.paynum = cust_bill_pay.paynum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_pay.paynum = cust_pay_refund.paynum ) + ,0 + ) + ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) + > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) + + ( SELECT COALESCE(SUM(refund + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) + ,0 + ) + ), 0) FROM cust_refund LEFT JOIN cust_main USING ( custnum ) WHERE cust_refund._date <= ( EXTRACT( EPOCH FROM now() ) - 2592000 ) AND cust_refund._date > ( EXTRACT( EPOCH FROM now() ) - 5184000 ) AND ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay + WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill + WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) + + ( SELECT COALESCE(SUM(refund + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) + ,0 + ) + ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) + - ( SELECT COALESCE(SUM(amount + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_credit.crednum = cust_credit_refund.crednum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_bill + WHERE cust_credit.crednum = cust_credit_bill.crednum ) + ,0 + ) + ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) + - ( SELECT COALESCE(SUM(paid + - COALESCE( + ( SELECT SUM(amount) FROM cust_bill_pay + WHERE cust_pay.paynum = cust_bill_pay.paynum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_pay.paynum = cust_pay_refund.paynum ) + ,0 + ) + ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) + > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) + - ( SELECT COALESCE(SUM(amount + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_credit.crednum = cust_credit_refund.crednum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_bill + WHERE cust_credit.crednum = cust_credit_bill.crednum ) + ,0 + ) + ), 0) FROM cust_credit LEFT JOIN cust_main USING ( custnum ) WHERE cust_credit._date <= ( EXTRACT( EPOCH FROM now() ) - 2592000 ) AND cust_credit._date > ( EXTRACT( EPOCH FROM now() ) - 5184000 ) AND ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay + WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill + WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) + + ( SELECT COALESCE(SUM(refund + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) + ,0 + ) + ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) + - ( SELECT COALESCE(SUM(amount + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_credit.crednum = cust_credit_refund.crednum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_bill + WHERE cust_credit.crednum = cust_credit_bill.crednum ) + ,0 + ) + ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) + - ( SELECT COALESCE(SUM(paid + - COALESCE( + ( SELECT SUM(amount) FROM cust_bill_pay + WHERE cust_pay.paynum = cust_bill_pay.paynum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_pay.paynum = cust_pay_refund.paynum ) + ,0 + ) + ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) + > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) + - ( SELECT COALESCE(SUM(paid + - COALESCE( + ( SELECT SUM(amount) FROM cust_bill_pay + WHERE cust_pay.paynum = cust_bill_pay.paynum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_pay.paynum = cust_pay_refund.paynum ) + ,0 + ) + ), 0) FROM cust_pay LEFT JOIN cust_main USING ( custnum ) WHERE cust_pay._date <= ( EXTRACT( EPOCH FROM now() ) - 2592000 ) AND cust_pay._date > ( EXTRACT( EPOCH FROM now() ) - 5184000 ) AND ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay + WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill + WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) + + ( SELECT COALESCE(SUM(refund + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) + ,0 + ) + ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) + - ( SELECT COALESCE(SUM(amount + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_credit.crednum = cust_credit_refund.crednum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_bill + WHERE cust_credit.crednum = cust_credit_bill.crednum ) + ,0 + ) + ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) + - ( SELECT COALESCE(SUM(paid + - COALESCE( + ( SELECT SUM(amount) FROM cust_bill_pay + WHERE cust_pay.paynum = cust_bill_pay.paynum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_pay.paynum = cust_pay_refund.paynum ) + ,0 + ) + ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) + > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) + AS balance_30_60, ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay + WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill + WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill LEFT JOIN cust_main USING ( custnum ) WHERE cust_bill._date <= ( EXTRACT( EPOCH FROM now() ) - 5184000 ) AND cust_bill._date > ( EXTRACT( EPOCH FROM now() ) - 7776000 ) AND ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay + WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill + WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) + + ( SELECT COALESCE(SUM(refund + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) + ,0 + ) + ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) + - ( SELECT COALESCE(SUM(amount + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_credit.crednum = cust_credit_refund.crednum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_bill + WHERE cust_credit.crednum = cust_credit_bill.crednum ) + ,0 + ) + ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) + - ( SELECT COALESCE(SUM(paid + - COALESCE( + ( SELECT SUM(amount) FROM cust_bill_pay + WHERE cust_pay.paynum = cust_bill_pay.paynum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_pay.paynum = cust_pay_refund.paynum ) + ,0 + ) + ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) + > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) + + ( SELECT COALESCE(SUM(refund + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) + ,0 + ) + ), 0) FROM cust_refund LEFT JOIN cust_main USING ( custnum ) WHERE cust_refund._date <= ( EXTRACT( EPOCH FROM now() ) - 5184000 ) AND cust_refund._date > ( EXTRACT( EPOCH FROM now() ) - 7776000 ) AND ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay + WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill + WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) + + ( SELECT COALESCE(SUM(refund + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) + ,0 + ) + ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) + - ( SELECT COALESCE(SUM(amount + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_credit.crednum = cust_credit_refund.crednum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_bill + WHERE cust_credit.crednum = cust_credit_bill.crednum ) + ,0 + ) + ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) + - ( SELECT COALESCE(SUM(paid + - COALESCE( + ( SELECT SUM(amount) FROM cust_bill_pay + WHERE cust_pay.paynum = cust_bill_pay.paynum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_pay.paynum = cust_pay_refund.paynum ) + ,0 + ) + ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) + > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) + - ( SELECT COALESCE(SUM(amount + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_credit.crednum = cust_credit_refund.crednum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_bill + WHERE cust_credit.crednum = cust_credit_bill.crednum ) + ,0 + ) + ), 0) FROM cust_credit LEFT JOIN cust_main USING ( custnum ) WHERE cust_credit._date <= ( EXTRACT( EPOCH FROM now() ) - 5184000 ) AND cust_credit._date > ( EXTRACT( EPOCH FROM now() ) - 7776000 ) AND ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay + WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill + WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) + + ( SELECT COALESCE(SUM(refund + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) + ,0 + ) + ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) + - ( SELECT COALESCE(SUM(amount + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_credit.crednum = cust_credit_refund.crednum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_bill + WHERE cust_credit.crednum = cust_credit_bill.crednum ) + ,0 + ) + ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) + - ( SELECT COALESCE(SUM(paid + - COALESCE( + ( SELECT SUM(amount) FROM cust_bill_pay + WHERE cust_pay.paynum = cust_bill_pay.paynum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_pay.paynum = cust_pay_refund.paynum ) + ,0 + ) + ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) + > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) + - ( SELECT COALESCE(SUM(paid + - COALESCE( + ( SELECT SUM(amount) FROM cust_bill_pay + WHERE cust_pay.paynum = cust_bill_pay.paynum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_pay.paynum = cust_pay_refund.paynum ) + ,0 + ) + ), 0) FROM cust_pay LEFT JOIN cust_main USING ( custnum ) WHERE cust_pay._date <= ( EXTRACT( EPOCH FROM now() ) - 5184000 ) AND cust_pay._date > ( EXTRACT( EPOCH FROM now() ) - 7776000 ) AND ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay + WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill + WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) + + ( SELECT COALESCE(SUM(refund + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) + ,0 + ) + ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) + - ( SELECT COALESCE(SUM(amount + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_credit.crednum = cust_credit_refund.crednum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_bill + WHERE cust_credit.crednum = cust_credit_bill.crednum ) + ,0 + ) + ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) + - ( SELECT COALESCE(SUM(paid + - COALESCE( + ( SELECT SUM(amount) FROM cust_bill_pay + WHERE cust_pay.paynum = cust_bill_pay.paynum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_pay.paynum = cust_pay_refund.paynum ) + ,0 + ) + ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) + > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) + AS balance_60_90, ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay + WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill + WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill LEFT JOIN cust_main USING ( custnum ) WHERE cust_bill._date <= ( EXTRACT( EPOCH FROM now() ) - 7776000 ) AND ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay + WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill + WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) + + ( SELECT COALESCE(SUM(refund + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) + ,0 + ) + ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) + - ( SELECT COALESCE(SUM(amount + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_credit.crednum = cust_credit_refund.crednum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_bill + WHERE cust_credit.crednum = cust_credit_bill.crednum ) + ,0 + ) + ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) + - ( SELECT COALESCE(SUM(paid + - COALESCE( + ( SELECT SUM(amount) FROM cust_bill_pay + WHERE cust_pay.paynum = cust_bill_pay.paynum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_pay.paynum = cust_pay_refund.paynum ) + ,0 + ) + ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) + > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) + + ( SELECT COALESCE(SUM(refund + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) + ,0 + ) + ), 0) FROM cust_refund LEFT JOIN cust_main USING ( custnum ) WHERE cust_refund._date <= ( EXTRACT( EPOCH FROM now() ) - 7776000 ) AND ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay + WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill + WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) + + ( SELECT COALESCE(SUM(refund + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) + ,0 + ) + ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) + - ( SELECT COALESCE(SUM(amount + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_credit.crednum = cust_credit_refund.crednum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_bill + WHERE cust_credit.crednum = cust_credit_bill.crednum ) + ,0 + ) + ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) + - ( SELECT COALESCE(SUM(paid + - COALESCE( + ( SELECT SUM(amount) FROM cust_bill_pay + WHERE cust_pay.paynum = cust_bill_pay.paynum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_pay.paynum = cust_pay_refund.paynum ) + ,0 + ) + ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) + > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) + - ( SELECT COALESCE(SUM(amount + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_credit.crednum = cust_credit_refund.crednum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_bill + WHERE cust_credit.crednum = cust_credit_bill.crednum ) + ,0 + ) + ), 0) FROM cust_credit LEFT JOIN cust_main USING ( custnum ) WHERE cust_credit._date <= ( EXTRACT( EPOCH FROM now() ) - 7776000 ) AND ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay + WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill + WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) + + ( SELECT COALESCE(SUM(refund + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) + ,0 + ) + ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) + - ( SELECT COALESCE(SUM(amount + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_credit.crednum = cust_credit_refund.crednum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_bill + WHERE cust_credit.crednum = cust_credit_bill.crednum ) + ,0 + ) + ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) + - ( SELECT COALESCE(SUM(paid + - COALESCE( + ( SELECT SUM(amount) FROM cust_bill_pay + WHERE cust_pay.paynum = cust_bill_pay.paynum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_pay.paynum = cust_pay_refund.paynum ) + ,0 + ) + ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) + > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) + - ( SELECT COALESCE(SUM(paid + - COALESCE( + ( SELECT SUM(amount) FROM cust_bill_pay + WHERE cust_pay.paynum = cust_bill_pay.paynum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_pay.paynum = cust_pay_refund.paynum ) + ,0 + ) + ), 0) FROM cust_pay LEFT JOIN cust_main USING ( custnum ) WHERE cust_pay._date <= ( EXTRACT( EPOCH FROM now() ) - 7776000 ) AND ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay + WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill + WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) + + ( SELECT COALESCE(SUM(refund + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) + ,0 + ) + ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) + - ( SELECT COALESCE(SUM(amount + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_credit.crednum = cust_credit_refund.crednum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_bill + WHERE cust_credit.crednum = cust_credit_bill.crednum ) + ,0 + ) + ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) + - ( SELECT COALESCE(SUM(paid + - COALESCE( + ( SELECT SUM(amount) FROM cust_bill_pay + WHERE cust_pay.paynum = cust_bill_pay.paynum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_pay.paynum = cust_pay_refund.paynum ) + ,0 + ) + ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) + > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) + AS balance_90_0, ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay + WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill + WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill LEFT JOIN cust_main USING ( custnum ) WHERE ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay + WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill + WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) + + ( SELECT COALESCE(SUM(refund + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) + ,0 + ) + ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) + - ( SELECT COALESCE(SUM(amount + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_credit.crednum = cust_credit_refund.crednum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_bill + WHERE cust_credit.crednum = cust_credit_bill.crednum ) + ,0 + ) + ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) + - ( SELECT COALESCE(SUM(paid + - COALESCE( + ( SELECT SUM(amount) FROM cust_bill_pay + WHERE cust_pay.paynum = cust_bill_pay.paynum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_pay.paynum = cust_pay_refund.paynum ) + ,0 + ) + ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) + > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) + + ( SELECT COALESCE(SUM(refund + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) + ,0 + ) + ), 0) FROM cust_refund LEFT JOIN cust_main USING ( custnum ) WHERE ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay + WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill + WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) + + ( SELECT COALESCE(SUM(refund + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) + ,0 + ) + ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) + - ( SELECT COALESCE(SUM(amount + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_credit.crednum = cust_credit_refund.crednum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_bill + WHERE cust_credit.crednum = cust_credit_bill.crednum ) + ,0 + ) + ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) + - ( SELECT COALESCE(SUM(paid + - COALESCE( + ( SELECT SUM(amount) FROM cust_bill_pay + WHERE cust_pay.paynum = cust_bill_pay.paynum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_pay.paynum = cust_pay_refund.paynum ) + ,0 + ) + ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) + > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) + - ( SELECT COALESCE(SUM(amount + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_credit.crednum = cust_credit_refund.crednum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_bill + WHERE cust_credit.crednum = cust_credit_bill.crednum ) + ,0 + ) + ), 0) FROM cust_credit LEFT JOIN cust_main USING ( custnum ) WHERE ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay + WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill + WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) + + ( SELECT COALESCE(SUM(refund + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) + ,0 + ) + ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) + - ( SELECT COALESCE(SUM(amount + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_credit.crednum = cust_credit_refund.crednum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_bill + WHERE cust_credit.crednum = cust_credit_bill.crednum ) + ,0 + ) + ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) + - ( SELECT COALESCE(SUM(paid + - COALESCE( + ( SELECT SUM(amount) FROM cust_bill_pay + WHERE cust_pay.paynum = cust_bill_pay.paynum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_pay.paynum = cust_pay_refund.paynum ) + ,0 + ) + ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) + > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) + - ( SELECT COALESCE(SUM(paid + - COALESCE( + ( SELECT SUM(amount) FROM cust_bill_pay + WHERE cust_pay.paynum = cust_bill_pay.paynum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_pay.paynum = cust_pay_refund.paynum ) + ,0 + ) + ), 0) FROM cust_pay LEFT JOIN cust_main USING ( custnum ) WHERE ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay + WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill + WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) + + ( SELECT COALESCE(SUM(refund + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) + ,0 + ) + ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) + - ( SELECT COALESCE(SUM(amount + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_refund + WHERE cust_credit.crednum = cust_credit_refund.crednum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_credit_bill + WHERE cust_credit.crednum = cust_credit_bill.crednum ) + ,0 + ) + ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) + - ( SELECT COALESCE(SUM(paid + - COALESCE( + ( SELECT SUM(amount) FROM cust_bill_pay + WHERE cust_pay.paynum = cust_bill_pay.paynum ) + ,0 + ) + - COALESCE( + ( SELECT SUM(amount) FROM cust_pay_refund + WHERE cust_pay.paynum = cust_pay_refund.paynum ) + ,0 + ) + ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) + > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) + AS balance_0_0 diff --git a/bin/find-overapplied b/bin/find-overapplied new file mode 100644 index 000000000..7973cef5b --- /dev/null +++ b/bin/find-overapplied @@ -0,0 +1,27 @@ +#!/usr/bin/perl -w + +use strict; +use Data::Dumper; +use FS::UID qw(adminsuidsetup); +use FS::Record qw(qsearch); +use FS::cust_credit; +use FS::cust_pay; + +my $user = shift or die &usage; +adminsuidsetup $user; + +my @credits = grep { $_->credited < 0 } qsearch('cust_credit', {}); +my @payments = grep { $_->unapplied < 0 } qsearch('cust_pay', {}); + +if ( @credits ) { + print scalar(@credits). " overapplied credits:\n". Dumper(@credits). "\n"; +} + +if ( @payments ) { + print scalar(@payments). " overapplied payments:\n". Dumper(@payments). "\n"; +} + +sub usage { + die "Usage:\n\n find-overapplied user\n"; +} + diff --git a/bin/fix-sequences b/bin/fix-sequences new file mode 100755 index 000000000..dc4abd751 --- /dev/null +++ b/bin/fix-sequences @@ -0,0 +1,69 @@ +#!/usr/bin/perl -Tw + +# run dbdef-create first! + +use strict; +use DBI; +use DBIx::DBSchema 0.26; +use DBIx::DBSchema::Table; +use DBIx::DBSchema::Column; +use DBIx::DBSchema::ColGroup::Unique; +use DBIx::DBSchema::ColGroup::Index; +use FS::UID qw(adminsuidsetup driver_name); +use FS::Record qw(dbdef); + +my $user = shift or die &usage; +my $dbh = adminsuidsetup $user; + +my $schema = dbdef(); + +#false laziness w/fs-setup +my @tables = scalar(@ARGV) + ? @ARGV + : grep { ! /^h_/ } $schema->tables; +foreach my $table ( @tables ) { + my $tableobj = $schema->table($table) + or die "unknown table $table (did you run dbdef-create?)\n"; + + my $primary_key = $tableobj->primary_key; + next unless $primary_key; + + my $col = $tableobj->column($primary_key); + + + next unless uc($col->type) eq 'SERIAL' + || ( driver_name eq 'Pg' + && defined($col->default) + && $col->default =~ /^nextval\(/i + ) + || ( driver_name eq 'mysql' + && defined($col->local) + && $col->local =~ /AUTO_INCREMENT/i + ); + + my $seq = "${table}_${primary_key}_seq"; + if ( driver_name eq 'Pg' + && defined($col->default) + && $col->default =~ /^nextval\('"(public\.)?(\w+_seq)"'::text\)$/ + ) { + $seq = $2; + } + + warn "fixing sequence for $table\n"; + + + my $sql = "SELECT setval( '$seq', + ( SELECT max($primary_key) FROM $table ) );"; + + #warn $col->default. " $seq\n$sql\n"; + $dbh->do( $sql ) or die $dbh->errstr; + +} + +$dbh->commit or die $dbh->errstr; +$dbh->disconnect or die $dbh->errstr; + +sub usage { + die "Usage:\n fix-sequences user [ table table ... ] \n"; +} + diff --git a/bin/freeside-init b/bin/freeside-init new file mode 100755 index 000000000..fe12931fc --- /dev/null +++ b/bin/freeside-init @@ -0,0 +1,60 @@ +#! /bin/sh +# +# start the freeside job queue daemon + +#PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin +DAEMON=/usr/local/bin/freeside-queued +NAME=freeside-queued +DESC="freeside job queue daemon" +USER="ivan" + +test -f $DAEMON || exit 0 + +set -e + +case "$1" in + start) + echo -n "Starting $DESC: " +# start-stop-daemon --start --quiet --pidfile /var/run/$NAME.pid -b -m\ +# --exec $DAEMON + $DAEMON $USER & + echo "$NAME." + ;; + stop) + echo -n "Stopping $DESC: " + start-stop-daemon --stop --quiet --pidfile /var/run/$NAME.pid \ + --exec $DAEMON + echo "$NAME." + rm /var/run/$NAME.pid + ;; + #reload) + # + # If the daemon can reload its config files on the fly + # for example by sending it SIGHUP, do it here. + # + # If the daemon responds to changes in its config file + # directly anyway, make this a do-nothing entry. + # + # echo "Reloading $DESC configuration files." + # start-stop-daemon --stop --signal 1 --quiet --pidfile \ + # /var/run/$NAME.pid --exec $DAEMON + #;; + restart|force-reload) + # + # If the "reload" option is implemented, move the "force-reload" + # option to the "reload" entry above. If not, "force-reload" is + # just the same as "restart". + # + $0 stop + sleep 1 + $0 start + ;; + *) + N=/etc/init.d/$NAME + # echo "Usage: $N {start|stop|restart|reload|force-reload}" >&2 + echo "Usage: $N {start|stop|restart|force-reload}" >&2 + exit 1 + ;; +esac + +exit 0 diff --git a/bin/freeside-migrate-events b/bin/freeside-migrate-events new file mode 100644 index 000000000..76643b886 --- /dev/null +++ b/bin/freeside-migrate-events @@ -0,0 +1,229 @@ +#!/usr/bin/perl -w + +use strict; + +use FS::UID qw(adminsuidsetup); +use FS::Record qw( qsearch ); +use FS::part_bill_event; +use FS::part_event; +use FS::cust_bill_event; +use FS::cust_event; + +my $user = shift or die &usage; +adminsuidsetup($user); + +my %plan2action = ( + 'fee' => 'fee', + 'fee_percent' => 'NOTYET', #XXX need fee_percent action + 'suspend' => 'suspend', + 'suspend-if-balance' => 'NOTYET', #XXX "if balance" becomes a balance condition + 'suspend-if-pkgpart' => 'suspend_if_pkgpart', + 'suspend-unless-pkgpart' => 'suspend_unless_pkgpart', + 'cancel' => 'cancel', + 'addpost' => 'addpost', + 'comp' => 'NOTYET', #XXX or N/A or something + 'credit' => 'NOTYET', + 'realtime-card' => 'cust_bill_realtime_card', + 'realtime-check' => 'cust_bill_realtime_check', + 'realtime-lec' => 'cust_bill_realtime_lec', + 'batch-card' => 'cust_bill_batch', + #?'retriable' => + 'send' => 'cust_bill_send', + 'send_email' => 'NOTYET', + 'send_alternate' => 'cust_bill_send_alternate', + 'send_if_newest' => 'cust_bill_send_if_newest', + 'send_agent' => 'cust_bill_send_agent', + 'send_csv_ftp' => 'cust_bill_send_csv_ftp', + 'spool_csv', => 'cust_bill_spool_csv', + 'bill' => 'bill', + 'apply' => 'apply', + 'collect' => 'collect', +); + + +foreach my $part_bill_event ( + qsearch({ + 'table' => 'part_bill_event', + }) +) { + + print $part_bill_event->event; + + my $action = $plan2action{ $part_bill_event->plan }; + + if ( $action eq 'NOTYET' ) { + warn "not migrating part_bill_event.eventpart ".$part_bill_event->eventpart. + "; ". $part_bill_event->plan. " plan not (yet) handled"; + next; + } elsif ( ! $action ) { + warn "not migrating part_bill_event.eventpart ".$part_bill_event->eventpart. + "; unknown plan ". $part_bill_event->plan; + next; + } + + my %plandata = map { /^(\w+) (.*)$/; ($1, $2); } + split(/\n/, $part_bill_event->plandata); + + #XXX may need to fudge some plandata2option names!!! + + my $part_event = new FS::part_event { + 'event' => $part_bill_event->event, + 'eventtable' => 'cust_bill', + 'check_freq' => $part_bill_event->freq || '1d', + 'weight' => $part_bill_event->weight, + 'action' => $action, + 'disabled' => $part_bill_event->disabled, + }; + + my $error = $part_event->insert(\%plandata); + die "error inserting part_event: $error\n" if $error; + + print ' '. $part_event->eventpart; + + my $once = new FS::part_event_condition { + 'eventpart' => $part_event->eventpart, + 'conditionname' => 'once' + }; + $error = $once->insert; + die $error if $error; + + my $balance = new FS::part_event_condition { + 'eventpart' => $part_event->eventpart, + 'conditionname' => 'balance' + }; + $error = $balance->insert( 'balance' => 0 ); + die $error if $error; + + my $cust_bill_owed = new FS::part_event_condition { + 'eventpart' => $part_event->eventpart, + 'conditionname' => 'cust_bill_owed' + }; + $error = $cust_bill_owed->insert( 'owed' => 0 ); + die $error if $error; + + my $payby = new FS::part_event_condition { + 'eventpart' => $part_event->eventpart, + 'conditionname' => 'payby' + }; + $error = $payby->insert( 'payby' => { $part_bill_event->payby => 1 } ); + die $error if $error; + + if ( $part_bill_event->seconds ) { + + my $age = new FS::part_event_condition { + 'eventpart' => $part_event->eventpart, + 'conditionname' => 'cust_bill_age' + }; + $error = $age->insert( 'age' => ($part_bill_event->seconds/86400 ).'d' ); + die $error if $error; + + } + + #my $derror = $part_bill_event->delete; + #die "error removing part_bill_event: $derror\n" if $derror; + + foreach my $cust_bill_event ( + qsearch({ + 'table' => 'cust_bill_event', + 'hashref' => { 'eventpart' => $part_bill_event->eventpart, }, + }) + ) { + + my $cust_event = new FS::cust_event { + 'eventpart' => $part_event->eventpart, + 'tablenum' => $cust_bill_event->invnum, + '_date' => $cust_bill_event->_date, + 'status' => $cust_bill_event->status, + 'statustext' => $cust_bill_event->statustext, + }; + + my $cerror = $cust_event->insert; + #die "error inserting cust_event: $cerror\n" if $cerror; + warn "error inserting cust_event: $cerror\n" if $cerror; + + #my $dcerror = $cust_bill_event->delete; + #die "error removing cust_bill_event: $dcerror\n" if $dcerror; + + print "."; + + } + + print "\n"; + +} + +sub usage { + die "Usage:\n freeside-migrate-events user\n"; +} + +=head1 NAME + +freeside-migrate-events - Migrates 1.7/1.8-style invoice events to + 1.9/2.0-style billing events + +=head1 SYNOPSIS + + freeside-migrate-events + +=head1 DESCRIPTION + +Migrates events from L<FS::part_bill_event> to L<FS::part_event> and friends, +and from L<FS::cust_bill_event> records to L<FS::cust_event> + +=head1 BUGS + +Doesn't migrate any action options yet. + +Doesn't translate option names that changed. + +Doesn't migrate reasons. + +Doesn't delete the old events (which is not a big deal, since the new code +won't run them...) + +=head1 SEE ALSO + +=cut + +1; + +__END__ + +#part_bill_event part_event +# +#eventpart n/a +#event event +#freq check_freq +#payby part_event_condition.conditionname = payby +#eventcode PARSE_WITH_REGEX (probably can just get from plandata) +#seconds part_event_condition.conditionname = cust_bill_age +#plandata PARSE_WITH_REGEX (along with eventcode, yuck) +#reason part_event_option.optionname = reason +#disabled disabled +# + + #these might help parse existing eventcode + + $c =~ /^\s*\$cust_main\->(suspend|cancel|invoicing_list_addpost|bill|collect)\(\);\s*("";)?\s*$/ + + or $c =~ /^\s*\$cust_bill\->(comp|realtime_(card|ach|lec)|batch_card|send)\((%options)*\);\s*$/ + + or $c =~ /^\s*\$cust_bill\->send(_if_newest)?\(\'[\w\-\s]+\'\s*(,\s*(\d+|\[\s*\d+(,\s*\d+)*\s*\])\s*,\s*'[\w\@\.\-\+]*'\s*)?\);\s*$/ + +# or $c =~ /^\s*\$cust_main\->apply_payments; \$cust_main->apply_credits; "";\s*$/ + or $c =~ /^\s*\$cust_main\->apply_payments_and_credits; "";\s*$/ + + or $c =~ /^\s*\$cust_main\->charge\( \s*\d*\.?\d*\s*,\s*\'[\w \!\@\#\$\%\&\(\)\-\+\;\:\"\,\.\?\/]*\'\s*\);\s*$/ + + or $c =~ /^\s*\$cust_main\->suspend_(if|unless)_pkgpart\([\d\,\s]*\);\s*$/ + + or $c =~ /^\s*\$cust_bill\->cust_suspend_if_balance_over\([\d\.\s]*\);\s*$/ + + or do { + #log + return "illegal eventcode: $c"; + }; + + } + + diff --git a/bin/freeside-session-kill b/bin/freeside-session-kill new file mode 100755 index 000000000..d5fd703f6 --- /dev/null +++ b/bin/freeside-session-kill @@ -0,0 +1,103 @@ +#!/usr/bin/perl -w + +use strict; +use vars qw($conf); +use Fcntl qw(:flock); +use FS::UID qw(adminsuidsetup datasrc dbh); +use FS::Record qw(dbdef qsearch fields); +use FS::session; +use FS::svc_acct; + +my $user = shift or die &usage; +adminsuidsetup $user; + +my $sessionlock = "/usr/local/etc/freeside/session-kill.lock.". datasrc; + +open(LOCK,"+>>$sessionlock") or die "Can't open $sessionlock: $!"; +select(LOCK); $|=1; select(STDOUT); +unless ( flock(LOCK,LOCK_EX|LOCK_NB) ) { + seek(LOCK,0,0); + my($pid)=<LOCK>; + chop($pid); + #no reason to start loct of blocking processes + die "Is another session kill process running under pid $pid?\n"; +} +seek(LOCK,0,0); +print LOCK $$,"\n"; + +$FS::UID::AutoCommit = 0; + +my $now = time; + +#uhhhhh + +use DBIx::DBSchema; +use DBIx::DBSchema::Table; #down this path lies madness +use DBIx::DBSchema::Column; + +my $dbdef = dbdef or die; +#warn $dbdef; +#warn $dbdef->{'tables'}; +#warn keys %{$dbdef->{'tables'}}; +my $session_table = $dbdef->table('session') or die; +my $svc_acct_table = $dbdef->table('svc_acct') or die; + +my $session_svc_acct = new DBIx::DBSchema::Table ( 'session,svc_acct', '', '', '', + map( DBIx::DBSchema::Column->new( "session.$_", + $session_table->column($_)->type, + $session_table->column($_)->null, + $session_table->column($_)->length, + ), $session_table->columns() ), + map( DBIx::DBSchema::Column->new( "svc_acct.$_", + $svc_acct_table->column($_)->type, + $svc_acct_table->column($_)->null, + $svc_acct_table->column($_)->length, + ), $svc_acct_table->columns ), +# map("svc_acct.$_", $svc_acct_table->columns), +); + +$dbdef->addtable($session_svc_acct); #madness, i tell you + +$FS::Record::DEBUG = 1; +my @session = qsearch('session,svc_acct', {}, '', ' WHERE '. join(' AND ', + 'svc_acct.svcnum = session.svcnum', + '( session.logout IS NULL OR session.logout = 0 )', + "( $now - session.login ) >= svc_acct.seconds" +). " FOR UPDATE" ); + +my $dbh = dbh; + +foreach my $join ( @session ) { + + my $session = new FS::session ( { + map { $_ => $join->{'Hash'}{"session.$_"} } fields('session') + } ); #see no evil + + my $svc_acct = new FS::svc_acct ( { + map { $_ => $join->{'Hash'}{"svc_acct.$_"} } fields('svc_acct') + } ); + + #false laziness w/ fs_session_server + my $nsession = new FS::session ( { $session->hash } ); + my $error = $nsession->replace($session); + if ( $error ) { + $dbh->rollback; + die $error; + } + my $time = $nsession->logout - $nsession->login; + my $new_svc_acct = new FS::svc_acct ( { $svc_acct->hash } ); + my $seconds = $new_svc_acct->seconds; + $seconds -= $time; + $seconds = 0 if $seconds < 0; + $new_svc_acct->seconds( $seconds ); + $error = $new_svc_acct->replace( $svc_acct ); + warn "can't debit time from ". $svc_acct->username. ": $error\n"; #don't want to rollback, though + #ssenizal eslaf + +} + +$dbh->commit or die $dbh->errstr; + +sub usage { + die "Usage:\n\n freeside-session-kill user\n"; +} diff --git a/bin/freeside-upgrade-unicode b/bin/freeside-upgrade-unicode new file mode 100755 index 000000000..c60336567 --- /dev/null +++ b/bin/freeside-upgrade-unicode @@ -0,0 +1,72 @@ +#!/bin/bash + +# based on example code from +# http://blog.larik.nl:80/articles/2006/03/13/upgrade-your-postgresql-databases-to-unicode +# by frodo larik / blog.larik.nl + +db=freeside + +# This script updates all dbs to use unicode + +dbhost='localhost' +username='freeside' +#odir=${HOME}/freeside_unicode_upgrade +odir=/home/ivan/FREESIDE_unicode_upgrade + +if [ "${db}X" == "X" ] +then + echo "I need a db for host ${dbhost} and username ${username} $db" + exit +fi + +if [ ! -d $odir ] +then + mkdir $odir || exit "Exit at mkdir" +fi + +#echo -n "Enter a comma-separated list of country codes to keep [US,CA]:" +#countries=`line` +#if [ "${countries}X" == "X" ] +#then +# countries='US,CA' +#fi + +echo "delete from cust_main_county where 0 = ( select count(*) from cust_main where cust_main_county.country = cust_main.country );" | su freeside -c 'psql freeside' + +dump_sql=${odir}/${db}_out.sql +conv_sql=${odir}/${db}_conv.sql +result_sql=${odir}/${db}_result.txt +sql_diff=${odir}/${db}.diff + +# 0. stop + +/etc/init.d/freeside stop || die "can't stop freeside" +/etc/init.d/apache stop || die "can't stop apache" +/etc/init.d/apache2 stop || die "can't stop apache" + +echo "Dumping $db database to $dump_sql" + +su $username -c "pg_dump --host=$dbhost --username=$username -D --format=p $db" >$dump_sql || exit "exit at pg_dump" + +echo "Removing invalid characters from the dump" + +iconv -c -f UTF-8 -t UTF-8 ${dump_sql} > ${conv_sql} || exit "exit at iconv" + +echo "*** Making a diff from the dump: check $sql_diff ***" + +diff $dump_sql $conv_sql > $sql_diff + +echo "Removing current database" + +su $freeside -c "dropdb --host=$dbhost --username=$username $db" || exit "exit at dropdb" + +echo "Creating a new databse" + +su freeside -c "createdb --encoding='unicode' --host=$dbhost --username=$username $db" || exit "exit at createdb" + +echo "Loading data into new database" +su freeside -c "psql -f $conv_sql -o $result_sql -h $dbhost -U $username $db" || exit "exit at psql ${extra_string}" + +# 99. +/etc/init.d/freeside start || die "oh no, can't start freeside" +/etc/init.d/apache start || die "oh no, can't start apache" diff --git a/bin/freeside.import b/bin/freeside.import new file mode 100644 index 000000000..fdfcc083e --- /dev/null +++ b/bin/freeside.import @@ -0,0 +1,146 @@ +#!/usr/bin/perl -w + +use strict; +use DBI; + +my $s_datasrc = 'DBI:mysql:host=ns1.enetonline.net;port=3307;user=ivan;dbname=freeside'; +my $s_dbuser = 'ivan'; +my $s_dbpass = ''; + +my $d_datasrc = 'DBI:Pg:dbname=freeside'; +my $d_dbuser = 'freeside'; +my $d_dbpass = ''; + +#my @tables = qw( +#addr_block +#agent +#agent_type +#cust_bill +#cust_bill_event +#cust_bill_pay +#cust_bill_pkg +#cust_bill_pkg_detail +#cust_credit +#cust_credit_bill +#cust_credit_refund +#cust_main +#cust_main_county +#cust_main_invoice +#cust_pay +#cust_pay_batch +#cust_pkg +#cust_refund +#cust_svc +#cust_tax_exempt +#domain_record +#export_svc +#h_addr_block +#h_agent +#h_agent_type +#h_cust_bill +#h_cust_bill_event +#h_cust_bill_pay +#h_cust_bill_pkg +#h_cust_bill_pkg_detail +#h_cust_credit +#h_cust_credit_bill +#h_cust_credit_refund +#h_cust_main +#h_cust_main_county +#h_cust_main_invoice +#h_cust_pay +#h_cust_pay_batch +#h_cust_pkg +#h_cust_refund +#h_cust_svc +#h_cust_tax_exempt +#h_domain_record +#h_export_svc +#h_msgcat +#h_nas +#h_part_bill_event +#h_part_export +#h_part_export_option +#h_part_pkg +#h_part_pop_local +#h_part_referral +#h_part_svc +#h_part_svc_column +#h_part_svc_router +#h_pkg_svc +#h_port +#h_prepay_credit +#h_queue +#h_queue_arg +#h_queue_depend +#h_radius_usergroup +#h_router +#h_router_field +#h_sb_field +#h_session +#h_svc_acct +#h_svc_acct_pop +#h_svc_broadband +#h_svc_domain +#h_svc_forward +#h_svc_www +#h_type_pkgs +#msgcat +#nas +#part_bill_event +#part_export +#part_export_option +#part_pkg + +my @tables = qw( +part_pop_local +part_referral +part_router_field +part_sb_field +part_svc +part_svc_column +part_svc_router +pkg_svc +port +prepay_credit +queue +queue_arg +queue_depend +radius_usergroup +router +router_field +sb_field +session +svc_acct +svc_acct_pop +svc_broadband +svc_domain +svc_forward +svc_www +type_pkgs +); + +my $s_dbh = DBI->connect($s_datasrc, $s_dbuser, $s_dbpass) or die $DBI::errstr; +my $d_dbh = DBI->connect($d_datasrc, $d_dbuser, $d_dbpass) or die $DBI::errstr; + +foreach my $table ( @tables ) { + $d_dbh->do("delete from $table"); + + my $s_sth = $s_dbh->prepare("select * from $table"); + $s_sth->execute or die $s_sth->errstr; + + my $row; + while ( $row = $s_sth->fetchrow_arrayref ) { + my $d_sth = $d_dbh->prepare( + "insert into $table ( ". + join(', ', @{$s_sth->{NAME}} ). + ' ) VALUES ( '. + join(', ', map { '?' } @{$s_sth->{NAME}} ). + ' )' + ) or die $d_dbh->errstr; + + $d_sth->execute(@$row) or die $d_sth->errstr; + + } +} + diff --git a/bin/fs-migrate-cust_tax_exempt b/bin/fs-migrate-cust_tax_exempt new file mode 100755 index 000000000..ede80b08e --- /dev/null +++ b/bin/fs-migrate-cust_tax_exempt @@ -0,0 +1,323 @@ +#!/usr/bin/perl -w + +use strict; +use Time::Local; +use Date::Format; +use Time::Duration; +use FS::UID qw(adminsuidsetup); +use FS::Record qw( qsearch dbh ); +use FS::cust_tax_exempt; +#use FS::cust_bill; +use FS::h_cust_bill; +use FS::h_cust_tax_exempt; +use FS::cust_bill_pkg; +use FS::cust_tax_exempt_pkg; +#use Data::Dumper; + +my $start = time; + +adminsuidsetup shift; + +my $fuz = 7; #seconds + + #site-specific rewrites +my %rewrite = ( + #cust_tax_exempt.exemptnum => { 'field' => 'newvalue', ... }, + '23' => { month=>10, year=>2005, invnum=>1640 }, + + #etc. +); + +my @cust_tax_exempt = qsearch('cust_tax_exempt', {} ); +my $num_cust_tax_exempt = scalar(@cust_tax_exempt); +my $num_cust_tax_exempt_migrated = 0; +my $total_cust_tax_exempt_migrated = 0; +my $num_cust_tax_exempt_pkg_migrated = 0; +my $total_cust_tax_exempt_pkg_migrated = 0; + +$FS::UID::AutoCommit = 0; + +foreach my $cust_tax_exempt ( @cust_tax_exempt ) { + + if ( exists $rewrite{ $cust_tax_exempt->exemptnum } ) { + my $hashref = $rewrite{ $cust_tax_exempt->exemptnum }; + $cust_tax_exempt->setfield($_, $hashref->{$_}) + foreach keys %$hashref; + } + + if ( $cust_tax_exempt->year < 1990 ) { + warn "exemption year is ". $cust_tax_exempt->year. + "; not migrating exemption ". $cust_tax_exempt->exemptnum. + ' for custnum '. $cust_tax_exempt->custnum. "\n\n"; + next; + } + + # also make sure cust_bill_pkg record dates contain the month/year +# my $mon = $cust_tax_exempt->month; +# my $year = $cust_tax_exempt->year; +# $mon--; +# my $edate_after = timelocal(0,0,0,1,$mon,$year); +# $mon++; +# if ( $mon >= 12 ) { $mon-=12; $year++ }; +# my $sdate_before = timelocal(0,0,0,1,$mon,$year); + + my $mon = $cust_tax_exempt->month; + my $year = $cust_tax_exempt->year; + if ( $mon >= 12 ) { $mon-=12; $year++ }; + my $sdate_before = timelocal(0,0,0,1,$mon,$year); + #$mon++; + #if ( $mon >= 12 ) { $mon-=12; $year++ }; + my $edate_after = timelocal(0,0,0,1,$mon,$year); + + # !! start a transaction? (yes, its started) + + my @h_cust_tax_exempt = qsearch({ + 'table' => 'h_cust_tax_exempt', + 'hashref' => { 'exemptnum' => $cust_tax_exempt->exemptnum }, + 'extra_sql' => " AND ( history_action = 'insert' + OR history_action = 'replace_new' ) + ORDER BY history_date ASC + ", + }); + + my $amount_so_far = 0; + my $num_cust_tax_exempt_pkg = 0; + my $total_cust_tax_exempt_pkg = 0; + H_CUST_TAX_EXEMPT: foreach my $h_cust_tax_exempt ( @h_cust_tax_exempt ) { + + my $amount = sprintf('%.2f', $h_cust_tax_exempt->amount - $amount_so_far ); + $amount_so_far += $amount; + +# print Dumper($h_cust_tax_exempt), "\n"; + + #find a matching cust_bill record + # (print time differences and choose a meaningful threshold, should work) + + my @h_cust_bill = (); + if ( $cust_tax_exempt->invnum ) { + #warn "following invnum ". $cust_tax_exempt->invnum. + # " kludge for cust_tax_exempt ". $cust_tax_exempt->exemptnum. "\n"; + + @h_cust_bill = qsearch({ + #'table' => 'cust_bill', + 'table' => 'h_cust_bill', + 'hashref' => { 'custnum' => $h_cust_tax_exempt->custnum, + 'invnum' => $cust_tax_exempt->invnum, + 'history_action' => 'insert', + }, + #'extra_sql' => + # ' AND history_date <= '. ( $h_cust_tax_exempt->history_date + $fuz ). + # ' AND history_date > '. ( $h_cust_tax_exempt->history_date - $fuz ), + }); + + } else { + + @h_cust_bill = qsearch({ + #'table' => 'cust_bill', + 'table' => 'h_cust_bill', + 'hashref' => { 'custnum' => $h_cust_tax_exempt->custnum, + 'history_action' => 'insert', + }, + 'extra_sql' => + ' AND history_date <= '. ( $h_cust_tax_exempt->history_date + $fuz ). + ' AND history_date > '. ( $h_cust_tax_exempt->history_date - $fuz ), + }); + + } + + if ( scalar(@h_cust_bill) != 1 ) { + warn ' '. scalar(@h_cust_bill). ' h_cust_bill records matching '. + 'h_cust_tax_exempt.historynum '. $h_cust_tax_exempt->historynum. + "; not migrating (adjust fuz factor?)\n"; + next; + } + + my $h_cust_bill = $h_cust_bill[0]; + +# print Dumper(@cust_bill), "\n\n"; + + # then find a matching cust_bill_pkg record with part_pkg.taxclass record + # that matches the one pointed to by cust_tax_exempt.taxnum + # (hopefully just one, see how many we can match automatically) + + my $cust_main_county = $cust_tax_exempt->cust_main_county; + my $taxclass = $cust_main_county->taxclass; + + my $hashref = { + 'custnum' => $cust_tax_exempt->custnum, + 'invnum' => $h_cust_bill->invnum, + 'pkgnum' => { op=>'>', value=>0, }, + }; + unless ( $cust_tax_exempt->invnum ) { + # also make sure cust_bill_pkg record dates contain the month/year + + #$hashref->{'sdate'} = { op=>'<', value=>$sdate_before }; + $hashref->{'sdate'} = { op=>'<=', value=>$sdate_before }; + + #$hashref->{'edate'} = { op=>'>', value=>$edate_after }; + $hashref->{'edate'} = { op=>'>=', value=>$edate_after }; + } + + if ( $cust_tax_exempt->billpkgnum ) { + $hashref->{'billpkgnum'} = $cust_tax_exempt->billpkgnum; + } + + my $extra_sql = 'ORDER BY billpkgnum'; + + $extra_sql = "AND taxclass = '$taxclass' $extra_sql" + unless $cust_tax_exempt->ignore_current_taxclass; + + my @cust_bill_pkg = qsearch({ + 'select' => 'cust_bill_pkg.*, part_pkg.freq', + 'table' => 'cust_bill_pkg', + 'addl_from' => 'LEFT JOIN cust_pkg using ( pkgnum ) '. + 'LEFT JOIN part_pkg using ( pkgpart ) ', + 'hashref' => $hashref, + 'extra_sql' => $extra_sql, + }); + + foreach my $cust_bill_pkg ( @cust_bill_pkg ) { + $cust_bill_pkg->exemptable_per_month( + sprintf('%.2f', + ( $cust_bill_pkg->setup + $cust_bill_pkg->recur ) + / + ( $cust_bill_pkg[0]->freq || 1 ) + ) + ); + } + + my(@cust_tax_exempt_pkg) = (); + if ( scalar(@cust_bill_pkg) == 1 + && $cust_bill_pkg[0]->exemptable_per_month >= $amount + ) + { + + my $cust_bill_pkg = $cust_bill_pkg[0]; + + # finally, create an appropriate cust_tax_exempt_pkg record + + push @cust_tax_exempt_pkg, new FS::cust_tax_exempt_pkg { + 'billpkgnum' => $cust_bill_pkg->billpkgnum, + 'taxnum' => $cust_tax_exempt->taxnum, + 'year' => $cust_tax_exempt->year, + 'month' => $cust_tax_exempt->month, + 'amount' => $amount, + }; + + } else { + +# warn ' '. scalar(@cust_bill_pkg). ' cust_bill_pkg records for invoice '. +# $h_cust_bill->invnum. +# "; not migrating h_cust_tax_exempt historynum ". +# $h_cust_tax_exempt->historynum. " for \$$amount\n"; +# warn " *** DIFFERENT DATES ***\n" +# if grep { $_->sdate != $cust_bill_pkg[0]->sdate +# || $_->edate != $cust_bill_pkg[0]->edate +# } @cust_bill_pkg; +# foreach ( @cust_bill_pkg ) { +# warn ' '. $_->billpkgnum. ': '. $_->setup. 's/'. $_->recur.'r'. +# ' '. time2str('%D', $_->sdate). '-'. time2str('%D', $_->edate). +# "\n"; +# } +# +# next; + + my $remaining = $amount; + foreach my $cust_bill_pkg ( @cust_bill_pkg ) { + last unless $remaining; + my $this_amount =sprintf('%.2f', + $remaining <= $cust_bill_pkg->exemptable_per_month + ? $remaining + : $cust_bill_pkg->exemptable_per_month + );; + + push @cust_tax_exempt_pkg, new FS::cust_tax_exempt_pkg { + 'billpkgnum' => $cust_bill_pkg->billpkgnum, + 'taxnum' => $cust_tax_exempt->taxnum, + 'year' => $cust_tax_exempt->year, + 'month' => $cust_tax_exempt->month, + 'amount' => $this_amount, + }; + + $remaining -= $this_amount; + + } + + } + + foreach my $cust_tax_exempt_pkg ( @cust_tax_exempt_pkg ) { + my $error = $cust_tax_exempt_pkg->insert; + #my $error = $cust_tax_exempt_pkg->check; + if ( $error ) { + warn "*** error inserting cust_tax_exempt_pkg record: $error\n"; + next; #not necessary.. H_CUST_TAX_EXEMPT; + + #not necessary, incorrect $total_cust_tax_exempt_pkg will error it out + # roll back at least the entire cust_tax_exempt transaction + # next CUST_TAX_EXEMPT; + } + + $num_cust_tax_exempt_pkg++; + + $total_cust_tax_exempt_pkg += $cust_tax_exempt_pkg->amount; + + } + + } + + $total_cust_tax_exempt_pkg = sprintf('%.2f', $total_cust_tax_exempt_pkg ); + + unless ( $total_cust_tax_exempt_pkg == $cust_tax_exempt->amount ) { + warn "total h_ amount $total_cust_tax_exempt_pkg != cust_tax_exempt.amount ". + $cust_tax_exempt->amount. + ";\n not migrating exemption ". $cust_tax_exempt->exemptnum. " for ". + $cust_tax_exempt->month. '/'. $cust_tax_exempt->year. + ' (custnum '. $cust_tax_exempt->custnum. ") ". + #"\n (sdate < ". time2str('%D', $sdate_before ). + "\n (sdate <= ". time2str('%D', $sdate_before ). " [$sdate_before]". + #' / edate > '. time2str('%D', $edate_after ). ')'. + ' / edate >= '. time2str('%D', $edate_after ). " [$edate_after])". + "\n\n"; + + # roll back at least the entire cust_tax_exempt transaction + dbh->rollback; + + # next CUST_TAX_EXEMPT; + next; + } + + # remove the cust_tax_exempt record + my $error = $cust_tax_exempt->delete; + if ( $error ) { + #roll back at least the entire cust_tax_exempt transaction + dbh->rollback; + + #next CUST_TAX_EXEMPT; + next; + } + + $num_cust_tax_exempt_migrated++; + $total_cust_tax_exempt_migrated += $cust_tax_exempt->amount; + + $num_cust_tax_exempt_pkg_migrated += $num_cust_tax_exempt_pkg; + $total_cust_tax_exempt_pkg_migrated += $total_cust_tax_exempt_pkg; + + # commit the transaction + dbh->commit; + +} + +$total_cust_tax_exempt_migrated = + sprintf('%.2f', $total_cust_tax_exempt_migrated ); +$total_cust_tax_exempt_pkg_migrated = + sprintf('%.2f', $total_cust_tax_exempt_pkg_migrated ); + +warn + "$num_cust_tax_exempt_migrated / $num_cust_tax_exempt (". + sprintf('%.2f', 100 * $num_cust_tax_exempt_migrated / $num_cust_tax_exempt). + '%) cust_tax_exempt records migrated ($'. $total_cust_tax_exempt_migrated. + ")\n to $num_cust_tax_exempt_pkg_migrated cust_tax_exempt_pkg records". + ' ($'. $total_cust_tax_exempt_pkg_migrated. ')'. + "\n in ". duration(time-$start). "\n" +; + diff --git a/bin/fs-migrate-part_svc b/bin/fs-migrate-part_svc new file mode 100755 index 000000000..b0f3ac57e --- /dev/null +++ b/bin/fs-migrate-part_svc @@ -0,0 +1,41 @@ +#!/usr/bin/perl + +use strict; +use FS::UID qw(adminsuidsetup); +use FS::Record qw(qsearch fields); +use FS::part_svc; + +my $user = shift or die &usage; +my $dbh = adminsuidsetup $user; + +my $oldAutoCommit = $FS::UID::AutoCommit; +local $FS::UID::AutoCommit = 0; + +foreach my $part_svc ( qsearch('part_svc', {} ) ) { + foreach my $field ( + grep { defined($part_svc->getfield($part_svc->svcdb.'__'.$_.'_flag') ) } + fields($part_svc->svcdb) + ) { + my $flag = $part_svc->getfield($part_svc->svcdb.'__'.$field.'_flag'); + if ( uc($flag) =~ /^([DF])$/ ) { + my $part_svc_column = new FS::part_svc_column { + 'svcpart' => $part_svc->svcpart, + 'columnname' => $field, + 'columnflag' => $1, + 'columnvalue' => $part_svc->getfield($part_svc->svcdb.'__'.$field), + }; + my $error = $part_svc_column->insert; + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + die $error; + } + } + } +} + +$dbh->commit or die $dbh->errstr; + +sub usage { + die "Usage:\n fs-migrate-part_svc user\n"; +} + diff --git a/bin/fs-migrate-payref b/bin/fs-migrate-payref new file mode 100755 index 000000000..158419706 --- /dev/null +++ b/bin/fs-migrate-payref @@ -0,0 +1,31 @@ +#!/usr/bin/perl + +use strict; +use FS::UID qw(adminsuidsetup); +use FS::Record qw(qsearch); +use FS::cust_pay; +use FS::cust_refund; + +my $user = shift or die &usage; +my $dbh = adminsuidsetup $user; + +# apply payments to invoices + +foreach my $cust_pay ( qsearch('cust_pay', {} ) ) { + my $error = $cust_pay->upgrade_replace; + warn $error if $error; +} + +# apply refunds to credits + +foreach my $cust_refund ( qsearch('cust_refund') ) { + my $error = $cust_refund->upgrade_replace; + warn $error if $error; +} + +# ? apply credits to invoices + +sub usage { + die "Usage:\n fs-migrate-payref user\n"; +} + diff --git a/bin/fs-migrate-svc_acct_sm b/bin/fs-migrate-svc_acct_sm new file mode 100755 index 000000000..07f7b611c --- /dev/null +++ b/bin/fs-migrate-svc_acct_sm @@ -0,0 +1,227 @@ +#!/usr/bin/perl -Tw +# +# jeff@cmh.net 01-Jul-20 + +#to delay loading dbdef until we're ready +#BEGIN { $FS::Record::setup_hack = 1; } + +use strict; +use Term::Query qw(query); +#use DBI; +#use DBIx::DBSchema; +#use DBIx::DBSchema::Table; +#use DBIx::DBSchema::Column; +#use DBIx::DBSchema::ColGroup::Unique; +#use DBIx::DBSchema::ColGroup::Index; +use FS::Conf; +use FS::UID qw(adminsuidsetup datasrc checkeuid getsecrets); +use FS::Record qw(qsearch qsearchs); +use FS::svc_domain; +use FS::svc_forward; +use vars qw( $conf $old_default_domain %part_domain_svc %part_acct_svc %part_forward_svc $svc_acct $svc_acct_sm $error); + +die "Not running uid freeside!" unless checkeuid(); + +my $user = shift or die &usage; +getsecrets($user); + +$conf = new FS::Conf; +$old_default_domain = $conf->config('domain'); + +#needs to match FS::Record +#my($dbdef_file) = "/usr/local/etc/freeside/dbdef.". datasrc; + +### +# This section would be the appropriate place to manipulate +# the schema & tables. +### + +## we need to add the domsvc to svc_acct +## we must add a svc_forward record.... +## I am thinking that the fields svcnum (int), destsvc (int), and +## dest (varchar (80)) are appropriate, with destsvc/dest an either/or +## much in the spirit of cust_main_invoice + +### +# massage the data +### + +my($dbh)=adminsuidsetup $user; + +$|=1; + +$FS::svc_Common::noexport_hack = 1; +$FS::svc_domain::whois_hack = 1; + +%part_domain_svc=map { $_->svcpart, $_ } qsearch('part_svc',{'svcdb'=>'svc_domain'}); +%part_acct_svc=map { $_->svcpart, $_ } qsearch('part_svc',{'svcdb'=>'svc_acct'}); +%part_forward_svc=map { $_->svcpart, $_ } qsearch('part_svc',{'svcdb'=>'svc_forward'}); + +die "No services with svcdb svc_domain!\n" unless %part_domain_svc; +die "No services with svcdb svc_acct!\n" unless %part_acct_svc; +die "No services with svcdb svc_forward!\n" unless %part_forward_svc; + +my($svc_domain) = qsearchs('svc_domain', { 'domain' => $old_default_domain }); +if (! $svc_domain || $svc_domain->domain != $old_default_domain) { + print <<EOF; + +Your database currently does not contain a svc_domain record for the +domain $old_default_domain. Would you like me to add one for you? +EOF + + my($response)=scalar(<STDIN>); + chop $response; + if ($response =~ /^[yY]/) { + print "\n\n", &menu_domain_svc, "\n", <<END; +I need to create new domain accounts. Which service shall I use for that? +END + my($domain_svcpart)=&getdomainpart; + + $svc_domain = new FS::svc_domain { + 'domain' => $old_default_domain, + 'svcpart' => $domain_svcpart, + 'action' => 'M', + }; +# $error=$svc_domain->insert && die "Error adding domain $old_default_domain: $error"; + $error=$svc_domain->insert; + die "Error adding domain $old_default_domain: $error" if $error; + }else{ + print <<EOF; + + This program cannot function properly until a svc_domain record matching +your conf_dir/domain file exists. +EOF + + exit 1; + } +} + +print "\n\n", &menu_acct_svc, "\n", <<END; +I may need to create some new pop accounts and set up forwarding to them +for some users. Which service shall I use for that? +END +my($pop_svcpart)=&getacctpart; + +print "\n\n", &menu_forward_svc, "\n", <<END; +I may need to create some new forwarding for some users. Which service +shall I use for that? +END +my($forward_svcpart)=&getforwardpart; + +sub menu_domain_svc { + ( join "\n", map "$_: ".$part_domain_svc{$_}->svc, sort keys %part_domain_svc ). "\n"; +} +sub menu_acct_svc { + ( join "\n", map "$_: ".$part_acct_svc{$_}->svc, sort keys %part_acct_svc ). "\n"; +} +sub menu_forward_svc { + ( join "\n", map "$_: ".$part_forward_svc{$_}->svc, sort keys %part_forward_svc ). "\n"; +} +sub getdomainpart { + $^W=0; # Term::Query isn't -w-safe + my $return = query "Enter part number:", 'irk', [ keys %part_domain_svc ]; + $^W=1; + $return; +} +sub getacctpart { + $^W=0; # Term::Query isn't -w-safe + my $return = query "Enter part number:", 'irk', [ keys %part_acct_svc ]; + $^W=1; + $return; +} +sub getforwardpart { + $^W=0; # Term::Query isn't -w-safe + my $return = query "Enter part number:", 'irk', [ keys %part_forward_svc ]; + $^W=1; + $return; +} + + +#migrate data + +my(@svc_accts) = qsearch('svc_acct', {}); +foreach $svc_acct (@svc_accts) { + my(@svc_acct_sms) = qsearch('svc_acct_sm', { + domuid => $svc_acct->getfield('uid'), + } + ); + + # Ok.. we've got the svc_acct record, and an array of svc_acct_sm's + # What do we do from here? + + # The intuitive: + # plop the svc_acct into the 'default domain' + # and then represent the svc_acct_sm's with svc_forwards + # they can be gussied up manually, later + # + # Perhaps better: + # when no svc_acct_sm exists, place svc_acct in 'default domain' + # when one svc_acct_sm exists, place svc_acct in corresponding + # domain & possibly create a svc_forward in 'default domain' + # when multiple svc_acct_sm's exists (in different domains) we'd + # better use the 'intuitive' approach. + # + # Specific way: + # as 'perhaps better,' but we may be able to guess which domain + # is correct by comparing the svcnum of domains to the username + # of the svc_acct + # + + # The intuitive way: + + my $def_acct = new FS::svc_acct ( { $svc_acct->hash } ); + $def_acct->setfield('domsvc' => $svc_domain->getfield('svcnum')); + $error = $def_acct->replace($svc_acct); + die "Error replacing svc_acct for " . $def_acct->username . " : $error" if $error; + + foreach $svc_acct_sm (@svc_acct_sms) { + + my($domrec)=qsearchs('svc_domain', { + svcnum => $svc_acct_sm->getfield('domsvc'), + }) || die "svc_acct_sm references invalid domsvc $svc_acct_sm->getfield('domsvc')\n"; + + if ($svc_acct_sm->getfield('domuser') =~ /^\*$/) { + + my($newdom) = new FS::svc_domain ( { $domrec->hash } ); + $newdom->setfield('catchall', $svc_acct->svcnum); + $newdom->setfield('action', "M"); + $error = $newdom->replace($domrec); + die "Error replacing svc_domain for (anything)@" . $domrec->domain . " : $error" if $error; + + } else { + + my($newacct) = new FS::svc_acct { + 'svcpart' => $pop_svcpart, + 'username' => $svc_acct_sm->getfield('domuser'), + 'domsvc' => $svc_acct_sm->getfield('domsvc'), + 'dir' => '/dev/null', + }; + $error = $newacct->insert; + die "Error adding svc_acct for " . $newacct->username . " : $error" if $error; + + my($newforward) = new FS::svc_forward { + 'svcpart' => $forward_svcpart, + 'srcsvc' => $newacct->getfield('svcnum'), + 'dstsvc' => $def_acct->getfield('svcnum'), + }; + $error = $newforward->insert; + die "Error adding svc_forward for " . $newacct->username ." : $error" if $error; + } + + $error = $svc_acct_sm->delete; + die "Error deleting svc_acct_sm for " . $svc_acct_sm->domuser ." : $error" if $error; + + }; + +}; + + +$dbh->commit or die $dbh->errstr; +$dbh->disconnect or die $dbh->errstr; + +print "svc_acct_sm records sucessfully migrated\n"; + +sub usage { + die "Usage:\n fs-migrate-svc_acct_sm user\n"; +} + diff --git a/bin/fs-radius-add-check b/bin/fs-radius-add-check new file mode 100755 index 000000000..4e4769e58 --- /dev/null +++ b/bin/fs-radius-add-check @@ -0,0 +1,68 @@ +#!/usr/bin/perl -Tw + +# quick'n'dirty hack of fs-setup to add radius attributes + +use strict; +use DBI; +use FS::UID qw(adminsuidsetup checkeuid getsecrets); +use FS::raddb; + +die "Not running uid freeside!" unless checkeuid(); + +my %attrib2db = + map { lc($FS::raddb::attrib{$_}) => $_ } keys %FS::raddb::attrib; + +my $user = shift or die &usage; +getsecrets($user); + +my $dbh = adminsuidsetup $user; + +### + +print "\n\n", <<END, ":"; +Enter the additional RADIUS check attributes you need to track for +each user, separated by whitespace. +END +my @attributes = map { $attrib2db{lc($_)} or die "unknown attribute $_"; } + split(" ",&getvalue); + +sub getvalue { + my($x)=scalar(<STDIN>); + chop $x; + $x; +} + +### + +my($char_d) = 80; #default maxlength for text fields + +### + +foreach my $attribute ( @attributes ) { + + my $statement = + "ALTER TABLE svc_acct ADD COLUMN rc_$attribute varchar($char_d) NULL"; + my $sth = $dbh->prepare( $statement ) + or warn "Error preparing $statement: ". $dbh->errstr; + my $rc = $sth->execute + or warn "Error executing $statement: ". $sth->errstr; + + $statement = + "ALTER TABLE h_svc_acct ADD COLUMN rc_$attribute varchar($char_d) NULL"; + $sth = $dbh->prepare( $statement ) + or warn "Error preparing $statement: ". $dbh->errstr; + $rc = $sth->execute + or warn "Error executing $statement: ". $sth->errstr; + +} + +$dbh->commit or die $dbh->errstr; + +$dbh->disconnect or die $dbh->errstr; + +print "\n\n", "Now you must run dbdef-create.\n\n"; + +sub usage { + die "Usage:\n fs-radius-add-check user\n"; +} + diff --git a/bin/fs-radius-add-reply b/bin/fs-radius-add-reply new file mode 100755 index 000000000..3de01374f --- /dev/null +++ b/bin/fs-radius-add-reply @@ -0,0 +1,69 @@ +#!/usr/bin/perl -Tw + +# quick'n'dirty hack of fs-setup to add radius attributes + +use strict; +use DBI; +use FS::UID qw(adminsuidsetup checkeuid getsecrets); +use FS::raddb; + +die "Not running uid freeside!" unless checkeuid(); + +my %attrib2db = + map { lc($FS::raddb::attrib{$_}) => $_ } keys %FS::raddb::attrib; + +my $user = shift or die &usage; +getsecrets($user); + +my $dbh = adminsuidsetup $user; + +### + +print "\n\n", <<END, ":"; +Enter the additional RADIUS reply attributes you need to track for +each user, separated by whitespace. +END +my @attributes = map { $attrib2db{lc($_)} or die "unknown attribute $_"; } + split(" ",&getvalue); + +sub getvalue { + my($x)=scalar(<STDIN>); + chop $x; + $x; +} + +### + +my($char_d) = 80; #default maxlength for text fields + +### + +foreach my $attribute ( @attributes ) { + + my $statement = + "ALTER TABLE svc_acct ADD COLUMN radius_$attribute varchar($char_d) NULL"; + my $sth = $dbh->prepare( $statement ) + or warn "Error preparing $statement: ". $dbh->errstr; + my $rc = $sth->execute + or warn "Error executing $statement: ". $sth->errstr; + + $statement = + "ALTER TABLE h_svc_acct ADD COLUMN radius_$attribute varchar($char_d) NULL"; + $sth = $dbh->prepare( $statement ) + or warn "Error preparing $statement: ". $dbh->errstr; + $rc = $sth->execute + or warn "Error executing $statement: ". $sth->errstr; + +} + +$dbh->commit or die $dbh->errstr; + +$dbh->disconnect or die $dbh->errstr; + +print "\n\n", "Now you must run dbdef-create.\n\n"; + +sub usage { + die "Usage:\n fs-radius-add-reply user\n"; +} + + diff --git a/bin/generate-prepay b/bin/generate-prepay new file mode 100755 index 000000000..cb4ba7fc6 --- /dev/null +++ b/bin/generate-prepay @@ -0,0 +1,35 @@ +#!/usr/bin/perl -w + +use strict; +use FS::UID qw(adminsuidsetup); +use FS::prepay_credit; + +require 5.004; #srand(time|$$); + +my $user = shift or die &usage; +&adminsuidsetup( $user ); + +my $amount = shift or die &usage; + +my $seconds = shift or die &usage; + +my $num_digits = shift or die &usage; + +my $num_entries = shift or die &usage; + +for ( 1 .. $num_entries ) { + my $identifier = join( '', map int(rand(10)), ( 1 .. $num_digits ) ); + my $prepay_credit = new FS::prepay_credit { + 'identifier' => $identifier, + 'amount' => $amount, + 'seconds' => $seconds, + }; + my $error = $prepay_credit->insert; + die $error if $error; + print "$identifier\n"; +} + +sub usage { + die "Usage:\n\n generate-prepay user amount seconds num_digits num_entries"; +} + diff --git a/bin/generate-raddb b/bin/generate-raddb new file mode 100755 index 000000000..af21c05a8 --- /dev/null +++ b/bin/generate-raddb @@ -0,0 +1,53 @@ +#!/usr/bin/perl + +# usage: generate-raddb radius-server/raddb/dictionary* >raddb.pm +# i.e.: generate-raddb ~/freeradius/freeradius-1.0.5/share/dictionary* ~/wirelessoceans/dictionary.ip3networks ~/wtxs/dictionary.mot.canopy >raddb.pm.new +print <<END; +package FS::raddb; +use vars qw(%attrib); + +%attrib = ( +END + +while (<>) { + next if /^(#|\s*$|\$INCLUDE\s+)/; + next if /^(VALUE|VENDOR|BEGIN\-VENDOR|END\-VENDOR)\s+/; + /^(ATTRIBUTE|ATTRIB_NMC)\s+([\w\-\/]+)\s+/ or die $_; + $attrib = $2; + $dbname = lc($2); + $dbname =~ s/[\-\/]/_/g; + $dbname = substr($dbname,0,24); + while ( exists $hash{$dbname} ) { + #warn $dbname; + $dbname =~ s/(.)$//; + my $w = $1; + $w =~ tr/_a-z0-9/a-z0-9_/; + $dbname = "$dbname$w"; + } + $hash{$dbname} = $attrib; + #print "$2\n"; +} + +foreach ( sort keys %hash ) { +# print "$_\n" if length($_)>24; +# print substr($_,0,24),"\n" if length($_)>24; +# $max = length($_) if length($_)>$max; +# have to fudge things since everything >24 is *not* unique + + #print " '". substr($_,0,24). "' => '$hash{$_}',\n"; + print " '$_' ". ( " " x (24-length($_) ) ). "=> '$hash{$_}',\n"; +} + +print <<END; + + #NETC.NET.AU (RADIATOR?) + 'authentication_type' => 'Authentication-Type', + + #wtxs (dunno) + #'radius_operator' => 'Radius-Operator', + +); + +1; +END + diff --git a/bin/generate-table-module b/bin/generate-table-module new file mode 100755 index 000000000..509feeded --- /dev/null +++ b/bin/generate-table-module @@ -0,0 +1,92 @@ +#!/usr/bin/perl + +use FS::Schema qw( dbdef_dist ); + +my $table = shift; + +### +# add a new FS/FS/table.pm +### + +my %ut = ( #just guesses + 'int' => 'number', + 'number' => 'float', + 'varchar' => 'text', + 'text' => 'text', + 'serial' => 'number', +); + +my $dbdef_table = dbdef_dist->table($table) + or die "define table in Schema.pm first"; +my $primary_key = $dbdef_table->primary_key; + +open(SRC,"<eg/table_template.pm") or die $!; +-e "FS/FS/$table.pm" and die "FS/FS/$table.pm already exists!"; +open(DEST,">FS/FS/$table.pm") or die $!; + +while (my $line = <SRC>) { + + $line =~ s/table_name/$table/g; + + if ( $line =~ /^=item\s+field\s+-\s+description\s*$/ ) { + + foreach my $column ( $dbdef_table->columns ) { + print DEST "=item $column\n\n"; + if ( $column eq $primary_key ) { + print DEST "primary key\n\n"; + } else { + print DEST "$column\n\n"; + } + } + next; + + } elsif ( $line=~ /^(\s*)\$self->ut_numbern\('primary_key'\)\s*/ ) { + + print DEST "$1\$self->ut_numbern('$primary_key')\n" + if $primary_key; + next; + + } elsif ( + $line =~ /^(\s*)\|\|\s+\$self->ut_number\('validate_other_fields'\)\s*/ + ) { + + foreach my $column ( grep { $_ ne $primary_key } $dbdef_table->columns ) { + my $ut = $ut{$dbdef_table->column($column)->type}; + $ut .= 'n' if $dbdef_table->column($column)->null; + print DEST "$1|| \$self->ut_$ut('$column')\n"; + } + next; + + } + + print DEST $line; +} + +close SRC; +close DEST; + +### +# add FS/t/table.t +### + +open(TEST,">FS/t/$table.t") or die $!; +print TEST <<ENDTEST; +BEGIN { \$| = 1; print "1..1\\n" } +END {print "not ok 1\\n" unless \$loaded;} +use FS::$table; +\$loaded=1; +print "ok 1\\n"; +ENDTEST +close TEST; + +### +# add them to MANIFEST +### + +system('cvs edit FS/MANIFEST'); + +open(MANIFEST,">>FS/MANIFEST") or die $!; +print MANIFEST "FS/$table.pm\n", + "t/$table.t\n"; +close MANIFEST; + diff --git a/bin/generate-tests b/bin/generate-tests new file mode 100755 index 000000000..73fd29ecb --- /dev/null +++ b/bin/generate-tests @@ -0,0 +1,21 @@ +#!/usr/bin/perl +@files = glob('FS/*.pm'); +foreach (@files) { +# warn $_; + chomp; + s/^FS\///; + $f=$_; + $f=~s/pm$/t/; + $m=$_; + $m=~s/\.pm$//; + open(TEST,">t/$f"); + print "t/$f\n"; + print TEST + 'BEGIN { $| = 1; print "1..1\n" }'. "\n". + 'END {print "not ok 1\n" unless $loaded;}'. "\n". + "use FS::$m;\n". + '$loaded=1;'. "\n". + 'print "ok 1\n";'. "\n" + ; + close TEST; +} diff --git a/bin/import-county-tax-rates b/bin/import-county-tax-rates new file mode 100755 index 000000000..05798c9a2 --- /dev/null +++ b/bin/import-county-tax-rates @@ -0,0 +1,30 @@ +#!/usr/bin/perl +# +# import-county-tax-rates username state country <filename.csv +# example: import-county-tax-rates ivan CA US <taxes.csv +# +# rates.csv: taxrate,county + +use FS::UID qw(adminsuidsetup); +use FS::cust_main_county; + +my $user = shift; +adminsuidsetup $user; + +my($state, $country) = (shift, shift); + +while (<>) { + my($tax, $county) = split(','); #half-ass CSV parser + + my $cust_main_county = new FS::cust_main_county { + 'county' => $county, + 'state' => $state, + 'country' => $country, + 'tax' => $tax, + }; + + my $error = $cust_main_county->insert; + #my $error = $cust_main_county->check; + die $error if $error; + +} diff --git a/bin/ispman.ldap.import b/bin/ispman.ldap.import new file mode 100755 index 000000000..7495f47f8 --- /dev/null +++ b/bin/ispman.ldap.import @@ -0,0 +1,114 @@ +#!/usr/bin/perl -w + +use strict; +use Net::LDAP::LDIF; + +use FS::UID qw(adminsuidsetup); +use FS::Record qw(qsearchs); +use FS::svc_domain; +use FS::svc_acct; + +my $user = shift or die; +adminsuidsetup($user); + +$FS::svc_Common::noexport_hack = 1; +$FS::svc_domain::whois_hack = 1; + +my $domain_svcpart = 1; +my $account_svcpart = 2; +my $mailbox_svcpart = 3; +my $fedweeknet_svcpart = 4; + +#my $ldif = +# Net::LDAP::LDIF->new( "ispman-06-23-04.ldif", "r", onerror => 'undef' ); +my $ldif = + Net::LDAP::LDIF->new( "ispman-06-23-04.ldif", "r", onerror => 'warn' ); + +#my %objectclass; + +my $acct = 0; +my $imported = 0; + +my $entry; +while ( $entry = $ldif->read_entry ) { + #warn "$entry\n"; + my %attributes = map { $_ => [ $entry->get_value( $_ ) ] } $entry->attributes; + + my $objectclass = join('/', @{$attributes{'objectclass'}} ); + + next unless $objectclass eq 'posixAccount/ispmanDomainUser/radiusprofile'; + + foreach my $attr ( keys %attributes ) { + print join( " => ", substr($attr.' 'x30,0,30), @{$attributes{ $attr }} ), "\n"; + #if ( $attr eq 'objectclass' ) { + # $objectclass{ join('/', @{$attributes{$attr}} ) }++; + #} + } + print "\n"; + + $acct++; + + my $email = $attributes{'maillocaladdress'}->[0]; + $email =~ /^(\w+)\@([\w\.\-]+)$/ or die $email; + die "$1 ne ". $attributes{'ispmanuserid'}->[0]. "\n" + unless lc($1) eq $attributes{'ispmanuserid'}->[0]; + my $username = lc($1); + my $domain = lc($2); + + my $svc_domain = qsearchs('svc_domain', { 'domain' => $domain } ) + || new FS::svc_domain { 'svcpart' => $domain_svcpart, + 'domain' => $domain, + 'action' => 'N', + }; + + unless ( $svc_domain->svcnum ) { + my $error = $svc_domain->insert; + if ( $error ) { + die "inserting domain: $error\n"; + } + } + + ( my $password = $attributes{'userpassword'}->[0] ) =~ s/^\{crypt\}//; + + # pick svcpart + my $svcpart = $account_svcpart; + if ( $domain eq 'fedweeknet.com' ) { + $svcpart = $fedweeknet_svcpart; + } elsif ( $attributes{'dialupaccess'}->[0] =~ /(false|no)/i ) { + $svcpart = $mailbox_svcpart; + } + + my $dir = $attributes{'homedirectory'}->[0]; + $dir =~ s/\s+//g; + $dir =~ s/\@/_/; + + my $svc_acct = new FS::svc_acct { + 'svcpart' => $svcpart, + 'username' => $username, + '_password' => $password, + 'finger' => $attributes{'cn'}->[0], + 'domsvc' => $svc_domain->svcnum, + 'shell' => $attributes{'loginshell'}->[0], + 'uid' => $attributes{'uidnumber'}->[0], + 'gid' => $attributes{'gidnumber'}->[0], + 'dir' => $dir, + 'quota' => $attributes{'mailquota'}->[0], + }; + my $error = $svc_acct->insert; + #my $error = $svc_acct->check; + + if ( $error ) { + warn "$error\n"; + } else { + $imported++; + } + +} + +print "$imported of $acct imported\n"; + +#print "\n\n"; + +#foreach ( sort { $objectclass{$b} <=> $objectclass{$a} } keys %objectclass ) { +# print "$objectclass{$_}: $_\n"; +#} diff --git a/bin/mapsecrets2access_user b/bin/mapsecrets2access_user new file mode 100755 index 000000000..945f130ef --- /dev/null +++ b/bin/mapsecrets2access_user @@ -0,0 +1,87 @@ +#!/usr/bin/perl -w + +use strict; +use File::Copy "cp"; +use FS::UID qw(adminsuidsetup); +use FS::CurrentUser; +use FS::AccessRight; +use FS::Record qw(qsearchs qsearch); +use FS::access_group; +use FS::access_user; +use FS::access_usergroup; +use FS::access_right; +use FS::access_groupagent; +use FS::agent; + +$FS::CurrentUser::upgrade_hack = 1; +my $user = shift or die &usage; +adminsuidsetup $user; + +my $supergroup = qsearchs('access_group', { 'groupname' => 'Superuser' } ); +unless ( $supergroup ) { + + $supergroup = new FS::access_group { 'groupname' => 'Superuser' }; + my $error = $supergroup->insert; + die $error if $error; + + foreach my $rightname ( FS::AccessRight->rights ) { + my $access_right = new FS::access_right { + 'righttype' => 'FS::access_group', + 'rightobjnum' => $supergroup->groupnum, + 'rightname' => $rightname, + }; + my $ar_error = $access_right->insert; + die $ar_error if $ar_error; + } + + foreach my $agent ( qsearch('agent', {} ) ) { + my $access_groupagent = new FS::access_groupagent { + 'groupnum' => $supergroup->groupnum, + 'agentnum' => $agent->agentnum, + }; + my $aga_error = $access_groupagent->insert; + die $aga_error if $aga_error; + } + +} +my $supergroupnum = $supergroup->groupnum; + +my $conf = new FS::Conf; +my $dir = $conf->base_dir; +my $mapsecrets = "$dir/mapsecrets"; +open(MAPSECRETS, "<$mapsecrets") or die "Can't open $mapsecrets: $!"; +while (<MAPSECRETS>) { + /([\w]+)\s+secrets\s*$/ or die "unparsable line in mapsecrets: $_"; + my $username = $1; + + next if qsearchs('access_user', { 'username' => $username } ); + + my $access_user = new FS::access_user { + 'username' => $username, + '_password' => 'notyet', + 'first' => 'Legacy', + 'last' => 'User', + }; + my $au_error = $access_user->insert; + die $au_error if $au_error; + + my $access_usergroup = new FS::access_usergroup { + 'usernum' => $access_user->usernum, + 'groupnum' => $supergroupnum, + }; + my $aug_error = $access_usergroup->insert; + die $aug_error if $aug_error; + +} +close MAPSECRETS; + +# okay to clobber mapsecrets now i guess +cp $mapsecrets, "$mapsecrets.bak$$"; +open(MAPSECRETS, ">$mapsecrets") or die $!; +print MAPSECRETS '* secrets'. "\n"; +close MAPSECRETS or die $!; + +sub usage { + die "Usage:\n mapsecrets2access_user user\n"; +} + diff --git a/bin/masonize b/bin/masonize new file mode 100755 index 000000000..509ef3ec8 --- /dev/null +++ b/bin/masonize @@ -0,0 +1,80 @@ +#!/usr/bin/perl + +foreach $file ( split(/\n/, `find . -depth -print`) ) { + next unless $file =~ /(cgi|html)$/; + open(F,$file) or die "can't open $file for reading: $!"; + @file = <F>; + #print "$file ". scalar(@file). "\n"; + close $file; + $newline = ''; #avoid prepending extraneous newlines + $all = join('',@file); + + $w = ''; + + $mode = 'html'; + while ( length($all) ) { + + if ( $mode eq 'html' ) { + + if ( $all =~ /^(.+?)(<%=?.*)$/s && $1 !~ /<%/s ) { + $w .= $1; + $all = $2; + next; + } elsif ( $all =~ /^<%=(.*)$/s ) { + $w .= '<%'; + $all = $1; + $mode = 'perlv'; + #die; + next; + } elsif ( $all =~ /^<%(.*)$/s ) { + $w .= $newline; $newline = "\n"; + $all = $1; + $mode = 'perlc'; + + #avoid newline prepend fix from borking indented first <% + $w =~ s/\n\s+\z/\n/; + $w .= "\n" if $w =~ /.+\z/; + + next; + } elsif ( $all !~ /<%/s ) { + $w .= $all; + last; + } else { + warn length($all); die; + } + die; + + } elsif ( $mode eq 'perlv' ) { + + if ( $all =~ /^(.*?%>)(.*)$/s ) { + $w .= $1; + $all=$2; + $mode = 'html'; + next; + } + die "unterminated <%= ??? (in $file):"; + + } elsif ( $mode eq 'perlc' ) { + + if ( $all =~ /^([^\n]*?)%>(.*)$/s ) { + $w .= "%$1\n"; + $all=$2; + $mode='html'; + next; + } + if ( $all =~ /^([^\n]*)\n(.*)$/s ) { + $w .= "%$1\n"; + $all=$2; + next; + } + + } else { die }; + + } + + system("chmod u+w $file"); + select W; $| = 1; select STDOUT; + open(W,">$file") or die "can't open $file for writing: $!"; + print W $w; + close W; +} diff --git a/bin/passwd.import b/bin/passwd.import new file mode 100755 index 000000000..8ab9e2ae3 --- /dev/null +++ b/bin/passwd.import @@ -0,0 +1,121 @@ +#!/usr/bin/perl -Tw + +use strict; +use vars qw(%part_svc); +use Date::Parse; +use Term::Query qw(query); +use Net::SCP qw(iscp); +use FS::UID qw(adminsuidsetup datasrc); +use FS::Record qw(qsearch qsearchs); +use FS::svc_acct; +use FS::part_svc; + +my $user = shift or die &usage; +adminsuidsetup $user; + +push @FS::svc_acct::shells, qw(/bin/sync /sbin/shutdown /bin/halt /sbin/halt); #others? + +my($spooldir)="/usr/local/etc/freeside/export.". datasrc; + +#$FS::svc_acct::nossh_hack = 1; +$FS::svc_Common::noexport_hack = 1; + +### + +%part_svc=map { $_->svcpart, $_ } qsearch('part_svc',{'svcdb'=>'svc_acct'}); + +die "No services with svcdb svc_acct!\n" unless %part_svc; + +print "\n\n", &menu_svc, "\n", <<END; +Enter part number to import. +END +my($shell_svcpart)=&getpart; + +print "\n\n", <<END; +Enter the location and name of your _user_ passwd file, for example +"mail.isp.com:/etc/passwd" or "nis.isp.com:/etc/global/passwd" +END +my($loc_passwd)=&getvalue(":"); +iscp("root\@$loc_passwd", "$spooldir/passwd.import"); + +print "\n\n", <<END; +Enter the location and name of your _user_ shadow file, for example +"mail.isp.com:/etc/shadow" or "bsd.isp.com:/etc/master.passwd" +END +my($loc_shadow)=&getvalue(":"); +iscp("root\@$loc_shadow", "$spooldir/shadow.import"); + +sub menu_svc { + ( join "\n", map "$_: ".$part_svc{$_}->svc, sort keys %part_svc ). "\n"; +} +sub getpart { + $^W=0; # Term::Query isn't -w-safe + my $return = query "Enter part number:", 'irk', [ keys %part_svc ]; + $^W=1; + $return; +} +sub getvalue { + my $prompt = shift; + $^W=0; # Term::Query isn't -w-safe + my $return = query $prompt, ''; + $^W=1; + $return; +} + +print "\n\n"; + +### + +open(PASSWD,"<$spooldir/passwd.import"); +open(SHADOW,"<$spooldir/shadow.import"); + +my(%password); +while (<SHADOW>) { + chop; + my($username,$password)=split(/:/); + #$password =~ s/^\!$/\*/; + #$password =~ s/\!+/\*SUSPENDED\* /; + $password =~ s/^NP$/\*/; + $password =~ s/^\*LK\*$/\*/; + $password{$username}=$password; +} + +while (<PASSWD>) { + chop; + my($username,$x,$uid,$gid,$finger,$dir,$shell) = split(/:/); + my $password = $password{$username}; + + my $svcpart = $shell_svcpart; + + #if ( qsearchs('svc_acct', { 'username' => $username } ) ) { + # warn "warning: $username already exists; skipping\n"; + # next; + #} + + my($svc_acct) = new FS::svc_acct ({ + 'svcpart' => $svcpart, + 'username' => $username, + '_password' => $password, + 'uid' => $uid, + 'gid' => $gid, + 'finger' => $finger, + 'dir' => $dir, + 'shell' => $shell, + #%{$allparam{$username}}, + }); + my($error); + $error=$svc_acct->insert; + if ( $error ) { + if ( $error =~ /duplicate/i ) { + warn "$username: $error"; + } else { + die "$username: $error"; + } + } + +} + +sub usage { + die "Usage:\n\n passwd.import user\n"; +} + diff --git a/bin/payment-faker b/bin/payment-faker new file mode 100755 index 000000000..03316e1c0 --- /dev/null +++ b/bin/payment-faker @@ -0,0 +1,54 @@ +#!/usr/bin/perl + +use Date::Parse; +use FS::UID qw(adminsuidsetup); +use FS::Record qw(qsearch); +use FS::cust_pay; +use FS::cust_credit; + +my $user; +$user = shift or die "usage: payment-faker $user"; +adminsuidsetup($user); + +for $month ( 1 .. 11 ) { + + print "month $month\n"; + + system(qq!freeside-daily -d "$month/1/2006" $user!); + + foreach my $cust_main ( qsearch('cust_main', {} ) ) { + next unless $cust_main->balance > 0; + my $item = ''; + if ( rand() > .95 ) { + $item = new FS::cust_credit { + 'amount' => $cust_main->balance, + '_date' => str2time("$month/1/2006"), + 'reason' => 'testing', + }; + } else { + + if ( rand() > .5 ) { + $payby = 'BILL'; + $payinfo = int(rand(10000)); + } else { + $payby = 'CARD'; + $payinfo = '4111111111111111'; + } + + $item = new FS::cust_pay { + 'paid' => $cust_main->balance, + '_date' => str2time("$month/1/2006"), + 'payby' => $payby, + 'payinfo' => $payinfo, + }; + } + + $item->custnum($cust_main->custnum); + my $error = $item->insert; + die $error if $error; + $cust_main->apply_payments; + $cust_main->apply_credits; + + } + +} diff --git a/bin/pg-readonly b/bin/pg-readonly new file mode 100644 index 000000000..ad69fbde2 --- /dev/null +++ b/bin/pg-readonly @@ -0,0 +1,24 @@ +#!/usr/bin/perl +# +# hack to update/add read-only permissions for a user on the db +# +# usage: pg-readonly freesideuser readonlyuser + +use strict; +use DBI; +use FS::UID qw(adminsuidsetup); +use FS::Record qw(dbdef); + +my $user = shift or die &usage; +my $rouser = shift or die &usage; + +my $dbh = adminsuidsetup $user; + +foreach my $table ( dbdef->tables ) { + $dbh->do("GRANT SELECT ON $table TO $rouser"); + $dbh->commit(); + if ( my $pkey = dbdef->table($table)->primary_key ) { + $dbh->do("GRANT SELECT ON ${table}_${pkey}_seq TO $rouser"); + $dbh->commit(); + } +} diff --git a/bin/pg-version b/bin/pg-version new file mode 100755 index 000000000..b6cddb612 --- /dev/null +++ b/bin/pg-version @@ -0,0 +1,13 @@ +#!/usr/bin/perl -w + +use strict; +use FS::UID qw(adminsuidsetup dbh); + +my $user = shift or die &usage; +adminsuidsetup($user); + +print "pg_server_version: ". dbh->{'pg_server_version'}. "\n"; + +sub usage { + "\n\nUsage: pg-version username\n"; +}; diff --git a/bin/pod2x b/bin/pod2x new file mode 100755 index 000000000..6b7153f96 --- /dev/null +++ b/bin/pod2x @@ -0,0 +1,148 @@ +#!/usr/bin/perl -w + +use strict; +use WWW::Mediawiki::Client; +#sub WWW::Mediawiki::Client::pagename_to_url { +# my ($self, $name, $action) = @_; +# WWW::Mediawiki::Client::URLConstructionException->throw( +# error => 'No action supplied.', +# ) unless $action; +# WWW::Mediawiki::Client::URLConstructionException->throw( +# error => "Page name $name ends with '.wiki'.", +# ) if $name =~ /.wiki$/; +# my $char = $self->space_substitute; +# $name =~ s/ /$char/; +# my $lang = $self->language_code; +# my $host = $self->host; +# $host =~ s/__LANG__/$lang/g; +# my $wiki_path = $self->wiki_path; +# $wiki_path =~ s/__LANG__/$lang/g; +# my $protocol = $self->protocol; +# return "$protocol://$host/$wiki_path?" . ACTION . "=$action&" . TITLE . "=$name" . '&wpRecreate=1'; +#} + +my $mw_username = 'ivan'; +chomp( my $mw_password = `cat .mw-password` ); + +my $site_perl = "./FS"; +#my $html = "Freeside:1.7:Documentation:Developer"; +my $html = "Freeside:1.9:Documentation:Developer"; + +foreach my $dir ( + $html, + map "$html/$_", qw( bin FS FS/UI FS/part_export FS/part_pkg + FS/part_event FS/part_event/Condition FS/part_event/Action + FS/ClientAPI FS/Cron FS/Misc FS/Report FS/Report/Table + FS/TicketSystem FS/UI + FS/SelfService + ) +) { + -d $dir or mkdir $dir; +} + +$|=1; + +die "Can't find $site_perl" unless -d $site_perl; +#die "Can't find $catman" unless -d $catman; +-d $html or mkdir $html; + +my $count = 0; + +#make some useless links +foreach my $file ( + glob("$site_perl/bin/freeside-*"), +) { + next if $file =~ /\.pod$/; + #symlink $file, "$file.pod"; # or die "link $file to $file.pod: $!"; + #system("cp $file $file.pod"); + -e "$file.pod" or system("cp $file $file.pod"); +} + +my $mvs = WWW::Mediawiki::Client->new( + 'host' => 'www.freeside.biz', + 'wiki_path' => 'mediawiki/index.php', + 'username' => $mw_username, + 'password' => $mw_password, + #'commit_message' => 'import from POD' + ); + +$mvs->do_login; + +my @files; +if ( @ARGV ) { + @files = @ARGV; +} else { + @files = ( + glob("$site_perl/*.pm"), + glob("$site_perl/*/*.pm"), + glob("$site_perl/*/*/*.pm"), + glob("$site_perl/*/*/*/*.pm"), + glob("$site_perl/bin/*.pod"), + glob("./fs_selfservice/FS-SelfService/*.pm"), + glob("./fs_selfservice/FS-SelfService/*/*.pm"), + ); + +} + +foreach my $file (@files) { + next if $file =~ /(^|\/)blib\//; + next if $file =~ /(^|\/)CVS\//; + #$file =~ /\/([\w\-]+)\.pm$/ or die "oops file $file"; + my $name; + if ( $file =~ /fs_\w+\/FS\-\w+\/(.*)\.pm$/ ) { + $name = "FS/$1"; + } elsif ( $file =~ /$site_perl\/(.*)\.(pm|pod)$/ ) { + $name = $1; + } else { + die "oops file $file"; + } + + #exit if $count++ == 10; + + my $htmlroot = join('/', map '..',1..(scalar($file =~ tr/\///)-2)) || '.'; + + system "pod2wiki --style mediawiki $file >$html/$name.rawwiki"; + + if ( -e "$html/$name.rawwiki" ) { + print "processing $name\n"; + } else { + print "skipping $name\n"; + next; + }; + + $mvs->do_update("$html/$name.wiki"); + + open(RAW, "<$html/$name.rawwiki") or die $!; + open(WIKI,">$html/$name.wiki" ) or die $!; + while (<RAW>) { + s/\[\[([^#p][^\]]*)\]\]/"[[$html\/". w_e($1). "|$1]]"/ge; + print WIKI $_; + } + close RAW; + close WIKI; + + print " uploading to ". $mvs->filename_to_pagename("$html/$name.wiki"). "\n"; + $mvs->commit_message( 'import from POD' ); + $mvs->do_commit("$html/$name.wiki"); + +} + +sub w_e { + my $s = shift; + $s =~ s/_/ /g; + $s =~ s/::/\//g; + $s =~ s/^freeside-/bin\/freeside-/g; + $s; +} + + +## system "pod2text $file >$catman/$name.txt"; +## +# system "pod2html --podroot=$site_perl --podpath=./FS:./FS/UI:.:./bin --norecurse --htmlroot=$htmlroot $file >$html/$name.html"; +# #system "pod2html --podroot=$site_perl --htmlroot=$htmlroot $file >$html/$name.html"; +## system "pod2html $file >$html/$name.html"; +## + +#remove the useless links +unlink glob("$site_perl/bin/*.pod"); + diff --git a/bin/postfix.export b/bin/postfix.export new file mode 100755 index 000000000..61380da59 --- /dev/null +++ b/bin/postfix.export @@ -0,0 +1,122 @@ +#!/usr/bin/perl -w + +use strict; +#use File::Path; +use File::Rsync; +use Net::SSH qw(ssh); +use FS::UID qw(adminsuidsetup datasrc); +use FS::Record qw(qsearch); # qsearchs); +use FS::part_export; +#use FS::cust_pkg; +use FS::cust_svc; +#use FS::svc_domain; + +my $user = shift or die &usage; +adminsuidsetup $user; + +my $spooldir = "/usr/local/etc/freeside/export.". datasrc. "/postfix"; +mkdir $spooldir, 0700 unless -d $spooldir; + +my @exports = qsearch('part_export', { 'exporttype' => 'postfix' } ); + +my $rsync = File::Rsync->new({ + rsh => 'ssh', +# dry_run => 1, +}); + +foreach my $export ( @exports ) { + + my $machine = $export->machine; + my $prefix = "$spooldir/$machine"; + mkdir $prefix, 0700 unless -d $prefix; + + #construct %domain hash + + my $mydomain = $export->option('mydomain'); + my %domain; + foreach my $svc_forward ( $export->svc_x ) { + + my( $username, $domain ); + my $srcsvc_acct = $svc_forward->srcsvc_acct; + if ( $srcsvc_acct ) { + ( $username, $domain ) = ( $srcsvc_acct->username, $srcsvc_acct->domain ); + } elsif ( $svc_forward->src =~ /^([^@]*)\@([^@]+)$/ ) { + ( $username, $domain ) = ( $1, $2 ); + } else { + die "bad svc_forward record? svcnum ". $svc_forward->svcnum. "\n"; + } + + my( $dusername, $ddomain ); + my $dstsvc_acct = $svc_forward->dstsvc_acct; + if ( $dstsvc_acct ) { + $dusername = $dstsvc_acct->username; + $ddomain = $dstsvc_acct->domain; + } elsif ( $svc_forward->dst =~ /([^@]+)\@([^@]+)$/ ) { + ( $dusername, $ddomain ) = ( $1, $2 ); + } else { + die "bad svc_forward record? svcnum ". $svc_forward->svcnum. "\n"; + } + my $dest; + if ( $ddomain eq $mydomain ) { + $dest = $dusername; + } else { + $dest = "$dusername\@$ddomain"; + } + + push @{$domain{$domain}{$username}}, $dest; + + } + + #write aliases + + my $aliases = delete $domain{$mydomain}; + open(ALIASES, ">$prefix/aliases") or die "can't open $prefix/aliases: $!"; + foreach my $alias ( keys %$aliases ) { + print ALIASES "$alias: ". join(',', @{ $aliases->{$alias} } ). "\n"; + } + close ALIASES; + + #write virtual + + open(VIRTUAL, ">$prefix/virtual") or die "can't open $prefix/virtual: $!"; + foreach my $domain ( keys %domain ) { + print VIRTUAL "$domain DOMAIN\n"; + #foreach my $virtual ( sort { $a ne '' <=> $b ne '' } keys %{$domain{$domain}} ) { + foreach my $virtual ( sort { ( ($b ne '') <=> ($a ne '') ) || $a cmp $b } keys %{$domain{$domain}} ) { + print VIRTUAL "$virtual\@$domain ". + join(',', @{ $domain{$domain}{$virtual} } ). "\n"; + } + print VIRTUAL "\n"; + } + close VIRTUAL; + + #rsync + + my $user = $export->option('user'); + $rsync->exec( { + src => "$prefix/aliases", + dest => "$user\@$machine:". $export->option('aliases'), + } ) or die "rsync to $machine failed: ". join(" / ", $rsync->err); +# warn $rsync->out; + + ssh("$user\@$machine", $export->option('newaliases') || 'newaliases'); +# ssh("$user\@$machine", "postfix reload"); + + $rsync->exec( { + src => "$prefix/virtual", + dest => "$user\@$machine:". $export->option('virtual'), + } ) or die "rsync to $machine failed: ". join(" / ", $rsync->err); +# warn $rsync->out; + ssh("$user\@$machine", $export->option('postmap') + || 'postmap hash:/etc/postfix/virtual'); + ssh("$user\@$machine", $export->option('reload') || 'postfix reload'); + +} + +# ----- + +sub usage { + die "Usage:\n postfix.export user\n"; +} + + diff --git a/bin/postfix_courierimap.import b/bin/postfix_courierimap.import new file mode 100755 index 000000000..12c138b49 --- /dev/null +++ b/bin/postfix_courierimap.import @@ -0,0 +1,137 @@ +#!/usr/bin/perl -Tw + +use strict; +use vars qw(%part_svc %domain_part_svc); +#use Date::Parse; +use DBI; +use Term::Query qw(query); +use FS::UID qw(adminsuidsetup); #datasrc +use FS::Record qw(qsearch qsearchs); +use FS::svc_acct; +use FS::part_svc; +use FS::svc_domain; + +my $user = shift or die &usage; +adminsuidsetup $user; + +#push @FS::svc_acct::shells, qw(/bin/sync /sbin/shutdown /bin/halt /sbin/halt); #others? + +$FS::svc_Common::noexport_hack = 1; +$FS::svc_domain::whois_hack = 1; + +### + +%part_svc=map { $_->svcpart, $_ } qsearch('part_svc',{'svcdb'=>'svc_acct'}); + +die "No services with svcdb svc_acct!\n" unless %part_svc; + +print "\n\n", &menu_svc, "\n", <<END; +Enter part number to import. +END +my $mailbox_svcpart = &getpart; + +%domain_part_svc = map { $_->svcpart, $_ } + qsearch('part_svc', { 'svcdb' => 'svc_domain'} ); + +die "No services with svcdb svc_domain!\n" unless %domain_part_svc; + +print "\n\n", &menu_domain_svc, "\n", <<END; +Enter part number for domains. +END +my $domain_svcpart = &getdomainpart; + +my $datasrc = &getvalue("\n\nEnter the DBI datasource:"); +my $db_user = &getvalue("\n\nEnter the database user:"); +my $db_pass = &getvalue("\n\nEnter the database password:"); + +sub menu_svc { + ( join "\n", map "$_: ".$part_svc{$_}->svc, sort keys %part_svc ). "\n"; +} +sub menu_domain_svc { + ( join "\n", map "$_: ".$domain_part_svc{$_}->svc, sort keys %domain_part_svc ). "\n"; +} +sub getpart { + $^W=0; # Term::Query isn't -w-safe + my $return = query "Enter part number:", 'irk', [ keys %part_svc ]; + $^W=1; + $return; +} +sub getdomainpart { + $^W=0; # Term::Query isn't -w-safe + my $return = query "Enter part number:", 'irk', [ keys %domain_part_svc ]; + $^W=1; + $return; +} +sub getvalue { + my $prompt = shift; + $^W=0; # Term::Query isn't -w-safe + my $return = query $prompt, ''; + $^W=1; + $return; +} + +print "\n\n"; + +### + +my $dbh = DBI->connect( $datasrc, $db_user, $db_pass ) + or die $DBI::errstr; + +my $sth = $dbh->prepare('SELECT username, password, crypt, name, domain FROM mailbox') + or die $dbh->errstr; +$sth->execute or die $sth->errstr; + +my $row; +while ( defined ( $row = $sth->fetchrow_arrayref ) ) { + my( $r_username, $password, $crypt, $finger, $r_domain ) = @$row; + + my( $username, $domain ); + if ( $r_username =~ /^([^@]+)\@([^@]+)$/ ) { + $username = $1; + $domain = $2; + } else { + $username = $r_username; + $domain = $r_domain; + } + my $svc_domain = qsearchs('svc_domain', { 'domain' => $domain } ) + || new FS::svc_domain { + 'domain' => $domain, + 'svcpart' => $domain_svcpart, + 'action' => 'N', + }; + unless ( $svc_domain->svcnum ) { + my $error = $svc_domain->insert; + if ( $error ) { + die "can't insert domain $domain: $error\n"; + } + } + + $password = $crypt if $password eq '*CRYPTED*'; + + $finger =~ s/Outdoor Power.*$/Outdoor Power/; + + my $svc_acct = new FS::svc_acct { + 'svcpart' => $mailbox_svcpart, + 'username' => $username, + 'domsvc' => $svc_domain->svcnum, + '_password' => $password, + 'finger' => $finger, + }; + + my $error = $svc_acct->insert; + #my $error = $svc_acct->check; + if ( $error ) { + if ( $error =~ /duplicate/i ) { + warn "$r_username / $r_domain: $error"; + } else { + die "$r_username / $r_domain: $error"; + } + } + +} + +sub usage { + die "Usage:\n\n postfix_courierimap.import user\n"; +} + + diff --git a/bin/print-schema b/bin/print-schema new file mode 100755 index 000000000..886e3250b --- /dev/null +++ b/bin/print-schema @@ -0,0 +1,7 @@ +#!/usr/bin/perl + +use DBIx::DBSchema; + +$l = load DBIx::DBSchema "/usr/local/etc/freeside/dbdef.DBI:Pg:dbname=freeside"; + +print $l->pretty_print, "\n"; diff --git a/bin/rate-us.import b/bin/rate-us.import new file mode 100755 index 000000000..66ac5de94 --- /dev/null +++ b/bin/rate-us.import @@ -0,0 +1,109 @@ +#!/usr/bin/perl -w + +use strict; +#use Spreadsheet::ParseExcel; +use DBI; +use FS::UID qw(adminsuidsetup); +use FS::rate_region; +use FS::rate_prefix; +use FS::rate_region; + +my $ratenum = 1; + +my $user = shift or usage(); +adminsuidsetup $user; + +sub usage { + #die "Usage:\n\n rate.import user rates.xls worksheet_name"; + die "Usage:\n\n rate.import user"; +} + +my %rate_region; + +foreach my $file ( 'areas and rates US.xls', + 'areas and rates US2.xls', + 'areas and rates US3.xls', + ) +{ + + my $dbh = DBI->connect("DBI:Excel:file=$file") + or die "can't connect: $DBI::errstr"; + + #my $table = shift or usage(); + my $table = 'Sheet1'; + my $sth = $dbh->prepare("select * from $table") + or die "can't prepare: ". $dbh->errstr; + $sth->execute + or die "can't execute: ". $sth->errstr; + + while ( my $row = $sth->fetchrow_hashref ) { + + #print join(' - ', map $row->{$_}, qw( rate_center Code Area_Prefix Rate ) ). "\n"; + + my $regionname = $row->{'rate_center'}; + $regionname =~ s/\xA0//g; + #$regionname =~ s/\xE9/e/g; #e with accent aigu + $regionname =~ s/(^\s+|\s+$)//; + $regionname .= ', USA'; + + my $prefix = $row->{'area_prefix'}; + $prefix =~ s/\xA0//g; + $prefix =~ s/\s$//; + #my $prefixprefix = ''; + #if ( $prefix =~ /^\s*(\d+)\s*\((.*)\)\s*$/ ) { + # $prefixprefix = $1; + # $prefix = $2; + #} elsif ( $prefix =~ /^\s*\((\d{3})\)\s*(.*)$/ ) { + # $prefixprefix = $1; + # $prefix = $2; + #} + + my @rate_prefix = map { + #warn $row->{'rate_center'}. ": $prefixprefix$_\n"; + new FS::rate_prefix { + 'countrycode' => '1', # $row->{'Country'} + #'npa' => $prefixprefix.$_, + 'npa' => $_, + }; + } + split(/\s*[;,]\s*/, $prefix); + + + my $dest_detail = new FS::rate_detail { + 'ratenum' => $ratenum, + 'min_included' => 0, + 'min_charge' => + sprintf('%.2f', $row->{'rate'} ), + 'sec_granularity' => 60, + }; + + unless ( exists $rate_region{$regionname} ) { + + my $rate_region = new FS::rate_region { + 'regionname' => $regionname, + }; + + my $error = $rate_region->insert( 'rate_prefix' => \@rate_prefix, + 'dest_detail' => [ $dest_detail ], + ); + die $error if $error; + + $rate_region{$regionname} = $rate_region->regionnum; + + } else { + + foreach my $rate_prefix ( @rate_prefix ) { + $rate_prefix->regionnum($rate_region{$regionname}); + my $error = $rate_prefix->insert; + die $error if $error; + } + + #$rate_detail->dest_regionnum($rate_region{$regionname}); + #$error = $rate_detail->insert; + #die $error if $error; + + } + + } + +} diff --git a/bin/rate.import b/bin/rate.import new file mode 100755 index 000000000..fdd756d72 --- /dev/null +++ b/bin/rate.import @@ -0,0 +1,95 @@ +#!/usr/bin/perl + +use strict; +#use Spreadsheet::ParseExcel; +use DBI; +use FS::UID qw(adminsuidsetup); +use FS::rate_region; +use FS::rate_prefix; +use FS::rate_region; + +my $ratenum = 1; + +my $user = shift or usage(); +adminsuidsetup $user; + +#my $file = shift or usage(); +my $file = 'areas and rates.xls'; +my $dbh = DBI->connect("DBI:Excel:file=$file") + or die "can't connect: $DBI::errstr"; + +#my $table = shift or usage(); +my $table = 'areas_and_rates'; +my $sth = $dbh->prepare("select * from $table") + or die "can't prepare: ". $dbh->errstr; +$sth->execute + or die "can't execute: ". $sth->errstr; + +sub usage { + #die "Usage:\n\n rate.import user rates.xls worksheet_name"; + die "Usage:\n\n rate.import user"; +} + +## + +while ( my $row = $sth->fetchrow_hashref ) { + + #print join(' - ', map $row->{$_}, qw( Country Code Area_Prefix Rate ) ). "\n"; + + my $regionname = $row->{'Country'}; + $regionname =~ s/\xA0//g; + $regionname =~ s/\xE9/e/g; #e with accent aigu + $regionname =~ s/(^\s+|\s+$)//; + + #next if $regionname =~ /Sweden Telia Mobile/; + + my $rate_region = new FS::rate_region { + 'regionname' => $regionname, + }; + + my $prefix = $row->{'Area_Prefix'}; + $prefix =~ s/\xA0//g; + $prefix =~ s/\s$//; + my $prefixprefix = ''; + if ( $prefix =~ /^\s*(\d+)\s*\((.*)\)\s*$/ ) { + $prefixprefix = $1; + $prefix = $2; + } elsif ( $prefix =~ /^\s*\((\d{3})\)\s*(.*)$/ ) { + $prefixprefix = $1; + $prefix = $2; + } + + my @rate_prefix = (); + if ( $prefix =~ /\d/ ) { + + @rate_prefix = map { + #warn $row->{'Country'}. ": $prefixprefix$_\n"; + new FS::rate_prefix { + 'countrycode' => $row->{'Code'}, + 'npa' => $prefixprefix.$_, + }; + } + split(/\s*[;,]\s*/, $prefix); + + } else { + @rate_prefix = ( new FS::rate_prefix { + 'countycode' => $row->{'Code'}, + 'npa' => '', + }; + ); + } + + my $dest_detail = new FS::rate_detail { + 'ratenum' => $ratenum, + 'min_included' => 0, + 'min_charge' => + sprintf('%.2f', $row->{'Rate'} ), + 'sec_granularity' => 60, + }; + + my $error = $rate_region->insert( 'rate_prefix' => \@rate_prefix, + 'dest_detail' => [ $dest_detail ], + ); + die $error if $error; + +} diff --git a/bin/reset-cust_credit-otaker b/bin/reset-cust_credit-otaker new file mode 100755 index 000000000..93002d05a --- /dev/null +++ b/bin/reset-cust_credit-otaker @@ -0,0 +1,88 @@ +#!/usr/bin/perl -w + +use strict; +use vars qw($opt_d); +use Getopt::Std; +use FS::UID qw(adminsuidsetup); +use FS::Record qw(qsearch qsearchs); +use FS::cust_credit; +use FS::h_cust_credit; + +getopts('d:'); + +my $user = shift or die &usage; +adminsuidsetup $user; + +die &usage + unless ($opt_d); + +$FS::Record::nowarn_identical = 1; + +if ( $opt_d ) { + $opt_d =~ /^(\d+)$/ or die "invalid date"; +} else { + die "no date specified\n"; +} + +my @cust_credit = qsearch('cust_credit', { otaker => $user } ); +die "no credits found\n" unless @cust_credit; + +my $cust_credit = new FS::cust_credit; +my @fields = grep { $_ !~ /^otaker|reason|reasonnum$/ } $cust_credit->fields; + +foreach my $cust_credit ( @cust_credit ) { + my %hash = $cust_credit->hash; + foreach (qw(otaker reason reasonnum)) { + delete $hash{$_}; + } + $hash{'history_action'} = 'replace_old'; + my $h_cust_credit = + qsearchs({ 'table' => 'h_cust_credit', + 'hashref' => \%hash, + 'select' => '*', + 'extra_sql' => " AND history_date <= $opt_d", + 'order_by' => 'ORDER BY history_date DESC LIMIT 1', + }); + if ($h_cust_credit) { + $cust_credit->otaker($h_cust_credit->otaker); + my $reason = $h_cust_credit->getfield('reason'); + if ($reason =~ /^\s*$/) { + $reason = '(none)'; + } + $cust_credit->otaker($h_cust_credit->otaker); + $cust_credit->reason($reason); + my $error = $cust_credit->replace + if $cust_credit->modified; + die "error replacing cust_credit: $error\n" + if $error; + }else{ + warn "Skipping credit.crednum ". $cust_credit->crednum; + } +} + +sub usage { + die "Usage:\n\n reset-cust_credit-otaker -d epoch_date user\n"; +} + +=head1 NAME + +reset-cust_credit-otaker - Command line tool to reset the otaker column for cust_credits to a previous value + +=head1 SYNOPSIS + + reset-cust_credit-otaker -d epoch_date user + +=head1 DESCRIPTION + + Sets the otaker column of the cust_credit records specified by user and + datespec to the value just prior to datespec. + + The reasonnum of the cust_credit record is also set to reason record + which matches the reason specified in the history. + +=head1 SEE ALSO + +L<FS::cust_credit>, L<FS::h_cust_credit>; + +=cut + diff --git a/bin/rollback b/bin/rollback new file mode 100755 index 000000000..7f83ef41a --- /dev/null +++ b/bin/rollback @@ -0,0 +1,38 @@ +#!/usr/bin/perl + +use FS::UID qw(adminsuidsetup); +use FS::Record qw(qsearch qsearchs fields); + +use FS::svc_acct; + +#cust_pkg pkgnum 240133 241206 replace_old +#cust_svc svcnum 31102 32083 delete +#svc_acct svcnum 37162 37652 delete +my($user, $table, $pkey, $start, $end, $action) = @ARGV; + +adminsuidsetup $user or die; + +#eval "use FS::h_$table;"; +#die $@ if $@; +eval "use FS::$table;"; +die $@ if $@; + +my @history = grep { $_->historynum <= $end } qsearch("h_$table", { 'historynum' => { op=>'>=', value=>$start }, history_action => $action } ); + +my %seen; +foreach my $h (@history) { + my $error; + if ( $action eq 'replace_old' ) { + my $old = qsearchs($table, { $pkey => $h->get($pkey) } ); + unless ( $old ) { die "can't find $table $pkey ". $h->get($pkey). "\n"; } + my $new = "FS::$table"->new( { map { $_ => $h->get($_) } fields($table) } ); + $error = $new->replace($old); + } elsif ( $action eq 'delete' ) { + next if $seen{$h->get($pkey)}++; + my $new = "FS::$table"->new( { map { $_ => $h->get($_) } fields($table) } ); + $error = $new->insert; + } else { + die "unknown action $action\n"; + } + die $error if $error; +} diff --git a/bin/rotate-cdrs b/bin/rotate-cdrs new file mode 100755 index 000000000..7bef0bbb0 --- /dev/null +++ b/bin/rotate-cdrs @@ -0,0 +1,38 @@ +#!/usr/bin/perl -w + +use strict; +use Fcntl qw(:flock); +use IO::File; + +my $dir = '/usr/local/etc/freeside/export/cdr'; +#chdir $dir; + +#XXX glob might not handle lots of args at some point... +foreach my $file ( glob("$dir/*/CDR*-spool.CSV") ) { + + $file =~ m{(\d+)/CDR(\d+)-spool.CSV$} + or die "guru meditation #54: can't parse filename: $file\n"; + my($custnum, $date) = ($1, $2); + + + my $alpha = 'A'; + while ( -e "$dir/$custnum/CDR$date$alpha.CSV" ) { + $alpha++; # A -> Z -> AA etc. + } + my $newfile = "$dir/$custnum/CDR$date$alpha.CSV"; + + rename $file, $newfile + or die "$! moving $file to $newfile\n"; + + use IO::File; + my $lock = new IO::File ">>$newfile" + or die "can't open $newfile: $!\n"; + sleep 1; #just in case. i guess there's still a *remotely* possible + #race condition, but i'm not losing any sleep over it... (rimshot) + flock($lock, LOCK_EX) + or die "can't lock $newfile: $!\n"; + #okay we've got the lock, any pending write should be done... + + print "$custnum: $newfile\n"; + +} diff --git a/bin/rt-drop-tables b/bin/rt-drop-tables new file mode 100755 index 000000000..b027542b3 --- /dev/null +++ b/bin/rt-drop-tables @@ -0,0 +1,29 @@ +#!/usr/bin/perl + +my @tables = qw( +Attachments +Queues +Links +Principals +Groups +ScripConditions +Transactions +Scrips +ACL +GroupMembers +CachedGroupMembers +Users +Tickets +ScripActions +Templates +TicketCustomFieldValues +CustomFields +CustomFieldValues +sessions +); + +foreach my $table ( @tables ) { + print "drop table $table;\n"; + print "drop sequence ${table}_id_seq;\n"; +} + diff --git a/bin/rt-update-links b/bin/rt-update-links new file mode 100644 index 000000000..75d554f48 --- /dev/null +++ b/bin/rt-update-links @@ -0,0 +1,36 @@ +#!/usr/bin/perl + +use FS::UID qw(adminsuidsetup); + +my( $olddb, $newdb ) = ( shift, shift ); + +$FS::CurrentUser::upgrade_hack = 1; +my $dbh = adminsuidsetup; + +my $statement = "select * from links where base like 'fsck.com-rt://$olddb/%' OR target like 'fsck.com-rt://$olddb/%'"; + +my $sth = $dbh->prepare($statement) or die $dbh->errstr; +$sth->execute or die $sth->errstr; + +while ( my $row = $sth->fetchrow_hashref ) { + + ( my $base = $row->{'base'} ) + =~ s(^fsck\.com-rt://$olddb/)(fsck.com-rt://$newdb/); + + ( my $target = $row->{'target'} ) + =~ s(^fsck\.com-rt://$olddb/)(fsck.com-rt://$newdb/); + + if ( $row->{'base'} ne $base || $row->{'target'} ne $target ) { + + my $update = 'UPDATE links SET base = ?, target = ? where id = ?'; + my @param = ( $base, $target, $row->{'id'} ); + + warn "$update : ". join(', ', @param). "\n"; + $dbh->do($update, {}, @param ); + + } + +} + +$dbh->commit; + diff --git a/bin/sendmail.import b/bin/sendmail.import new file mode 100644 index 000000000..ef745fc46 --- /dev/null +++ b/bin/sendmail.import @@ -0,0 +1,178 @@ +#!/usr/bin/perl -w + +use strict; +use Term::Query qw(query); +use Net::SCP qw(iscp); +use FS::UID qw(adminsuidsetup datasrc); +use FS::Record qw(qsearch qsearchs); +##use FS::svc_acct_sm; +#use FS::svc_domain; +#use FS::domain_record; +use FS::svc_acct; +##use FS::part_svc; +use FS::svc_forward; +use FS::svc_domain; + +my $user = shift or die &usage; +adminsuidsetup $user; + +#$FS::svc_Common::noexport_hack = 1; +#$FS::domain_record::noserial_hack = 1; + +use vars qw($defaultdomain); +$defaultdomain = '295.ca'; + +use vars qw(@svcpart $forward_svcpart); +@svcpart = qw( 2 4 ); +$forward_svcpart = 7; + +use vars qw($spooldir); +$spooldir = "/usr/local/etc/freeside/export.". datasrc. "/sendmail"; +mkdir($spooldir, 0755) unless -d $spooldir; + +print "\n\n", <<END; +Enter the location and name of your Sendmail aliases file, for example +"mail.isp.com:/etc/mail/aliases" +END +my($aliases)=&getvalue(":"); + +use vars qw($aliases_machine $aliases_prefix); +$aliases_machine = (split(/:/, $aliases))[0]; +$aliases_prefix = "$spooldir/$aliases_machine"; +mkdir($aliases_prefix, 0755) unless -d $aliases_prefix; + +#iscp("root\@$aliases","$aliases_prefix/aliases.import"); +iscp("ivan\@$aliases","$aliases_prefix/aliases.import"); + +print "\n\n", <<END; +Enter the location and name of your Sendmail virtusertable directory, for example +"mail.isp.com:/etc/mail/virtusertable" +END +my($virtusertable)=&getvalue(":"); + +use vars qw($virtusertable_machine $virtusertable_prefix); +$virtusertable_machine = (split(/:/, $virtusertable))[0]; +$virtusertable_prefix = "$spooldir/$virtusertable_machine"; +mkdir($virtusertable_prefix, 0755) unless -d $virtusertable_prefix; +mkdir("$virtusertable_prefix/virtusertable.import", 0755) + unless -d "$virtusertable_prefix/virtusertable.import"; + +#iscp("root\@$virtusertable/*","$aliases_prefix/virtusertable.import/"); +iscp("ivan\@$virtusertable/*","$aliases_prefix/virtusertable.import/"); + +sub getvalue { + my $prompt = shift; + $^W=0; # Term::Query isn't -w-safe + my $return = query $prompt, ''; + $^W=1; + $return; +} + +print "\n\n"; + +## + +foreach my $file ( + "$aliases_prefix/aliases.import", + glob("$aliases_prefix/virtusertable.import/*"), +) { + + warn "importing $file\n"; + + open(FILE,"<$file") or die $!; + while (<FILE>) { + next if /^\s*#/ || /^\s*$/; #skip comments & blank lines + + unless ( /^([\w\@\.\-]+)[:\s]\s*(.*\S)\s*$/ ) { + warn "Unparsable line: $_"; + next; + } + my($rawusername, $rawdest) = ($1, $2); + + my($username, $domain); + if ( $rawusername =~ /^([\w\-\.\&]*)\@([\w\.\-]+)$/ ) { + $username = $1; + $domain = $2; + } elsif ( $rawusername =~ /\@/ ) { + die "Unparsable username: $rawusername\n"; + } else { + $username = $rawusername; + $domain = $defaultdomain; + } + + #find svc_acct record or set $src + my($srcsvc, $src) = &svcnum_or_literal($username, $domain); + + foreach my $dest ( split(/,/, $rawdest) ) { + + my($dusername, $ddomain); + if ( $dest =~ /^([\w\-\.\&]+)\@([\w\.\-]+)$/ ) { + $dusername = $1; + $ddomain = $2; + } elsif ( $dest =~ /\@/ ) { + die "Unparsable username: $dest\n"; + } else { + $dusername = $dest; + $ddomain = $defaultdomain; + } + my($dstsvc, $dst) = &svcnum_or_literal($dusername, $ddomain); + + my $svc_forward = new FS::svc_forward ({ + svcpart => $forward_svcpart, + srcsvc => $srcsvc, + src => $src, + dstsvc => $dstsvc, + dst => $dst, + }); + my $error = $svc_forward->insert; + #my $error = $svc_forward->check; + if ( $error ) { + die "$rawusername: $rawdest: $error\n"; + } + } + + + } #next entry + +} #next file + +## + +sub svcnum_or_literal { + my($username, $domain) = @_; + + my $svc_domain = qsearchs('svc_domain', { 'domain' => $domain } ); + my $domsvc = $svc_domain ? $svc_domain->svcnum : ''; + + my @svc_acct = grep { my $svc_acct = $_; + grep { $svc_acct->cust_svc->svcpart == $_ } @svcpart + } + qsearch('svc_acct', { + 'username' => $username, + 'domsvc' => $domsvc, + }); + + if ( scalar(@svc_acct) > 1 ) { + die "multiple sources found for $username\@$domain !\n"; + } + + my( $svcnum, $literal ) = ('', ''); + if ( @svc_acct ) { + my $svc_acct = $svc_acct[0]; + $svcnum = $svc_acct->svcnum; + } else { + $literal = "$username\@$domain"; + } + + return( $svcnum, $literal ); + +} + +sub usage { + die "Usage:\n\n sendmail.import user\n"; +} + + + + + diff --git a/bin/sequences.reset b/bin/sequences.reset new file mode 100644 index 000000000..2dc1d3bb2 --- /dev/null +++ b/bin/sequences.reset @@ -0,0 +1,32 @@ +#!/usr/bin/perl + +use FS::UID qw(adminsuidsetup); +use FS::Record qw(dbdef dbh); + +my $user = shift; +adminsuidsetup $user or die; + +foreach my $table ( dbdef->tables ) { + my $primary_key = dbdef->table($table)->primary_key; + next unless $primary_key; + #my $local = dbdef->table($table)->column($primary_key)->local; + ##next unless $default =~ /nextval/; + #print "$local\n"; + + my $statement = "select setval('${table}_${primary_key}_seq', ( select max($primary_key) from $table ) )"; + + print "$statement;\n"; + next; + + my $sth = dbh->prepare($statement) or do { + warn dbh->errstr. " preparing $statement\n"; + next; + }; + $sth->execute or do { + warn dbh->errstr. " executing $statement\n"; + dbh->commit; + next; + } + +} + diff --git a/bin/shadow.reimport b/bin/shadow.reimport new file mode 100755 index 000000000..7957011eb --- /dev/null +++ b/bin/shadow.reimport @@ -0,0 +1,125 @@ +#!/usr/bin/perl -w +# +# -d: dry-run: make no changes +# -r: replace: overwrite existing passwords (otherwise only "*" passwords will +# be changed) +# -b: blowfish replace: overwrite existing passwords only if they are +# blowfish-encrypted + +use strict; +use vars qw(%part_svc); +use Getopt::Std; +use Term::Query qw(query); +use Net::SCP qw(iscp); +use FS::UID qw(adminsuidsetup datasrc); +use FS::Record qw(qsearch qsearchs); +use FS::svc_acct; +use FS::part_svc; + +use vars qw($opt_d $opt_r $opt_b); +getopts("drb"); + +my $user = shift or die &usage; +adminsuidsetup $user; + +push @FS::svc_acct::shells, qw(/bin/sync /sbin/shutdown /bin/halt /sbin/halt); #others? + +my($spooldir)="/usr/local/etc/freeside/export.". datasrc; + +#$FS::svc_acct::nossh_hack = 1; +$FS::svc_Common::noexport_hack = 1; + +### + +%part_svc=map { $_->svcpart, $_ } qsearch('part_svc',{'svcdb'=>'svc_acct'}); + +die "No services with svcdb svc_acct!\n" unless %part_svc; + +print "\n\n", &menu_svc, "\n", <<END; +Enter part number or part numbers to import. +END +my($shell_svcpart)=&getvalue; +my @shell_svcpart = split(/[,\s]+/, $shell_svcpart); + +print "\n\n", <<END; +Enter the location and name of your _user_ shadow file, for example +"mail.isp.com:/etc/shadow" or "bsd.isp.com:/etc/master.passwd" +END +my($loc_shadow)=&getvalue(":"); +iscp("root\@$loc_shadow", "$spooldir/shadow.import"); + +sub menu_svc { + ( join "\n", map "$_: ".$part_svc{$_}->svc, sort keys %part_svc ). "\n"; +} +sub getpart { + $^W=0; # Term::Query isn't -w-safe + my $return = query "Enter part number:", 'irk', [ keys %part_svc ]; + $^W=1; + $return; +} +sub getvalue { + my $prompt = shift; + $^W=0; # Term::Query isn't -w-safe + my $return = query $prompt, ''; + $^W=1; + $return; +} + +print "\n\n"; + +### + +open(SHADOW,"<$spooldir/shadow.import"); + +my($line, $updated); +while (<SHADOW>) { + $line++; + chop; + my($username,$password)=split(/:/); + +# my @svc_acct = grep { $_->cust_svc->svcpart == $shell_svcpart } +# qsearch('svc_acct', { 'username' => $username } ); + my @svc_acct = grep { + my $svcpart = $_->cust_svc->svcpart; + grep { $_ == $svcpart } @shell_svcpart; + } qsearch('svc_acct', { 'username' => $username } ); + + next unless @svc_acct; + + if ( scalar(@svc_acct) > 1 ) { + die "more than one $username found!\n"; + next; + } + + my $svc_acct = shift @svc_acct; + + next unless $svc_acct->_password eq '*' + || $opt_r + || ( $opt_b && $svc_acct->_password =~ /^\$2a?\$/ ); + + next if $svc_acct->username eq 'root'; + + next if $password eq 'NP' || $password eq '*LK*'; + + next if $svc_acct->_password eq $password; + next if $svc_acct->_password =~ /^\*SUSPENDED\*/; + + my $new_svc_acct = new FS::svc_acct( { $svc_acct->hash } ); + $new_svc_acct->_password($password); + #warn "$username: ". $svc_acct->_password. " -> $password\n"; + warn "changing password for $username\n"; + unless ( $opt_d ) { + my $error = $new_svc_acct->replace($svc_acct); + die "$username: $error" if $error; + } + + $updated++; + +} + +warn "$updated of $line passwords changed\n"; + +sub usage { + die "Usage:\n\n shadow.reimport [ -d ] [ -r ] user\n"; +} + diff --git a/bin/slony-setup b/bin/slony-setup new file mode 100755 index 000000000..0798c1a03 --- /dev/null +++ b/bin/slony-setup @@ -0,0 +1,109 @@ +#!/usr/bin/perl +# +# slony replication setup +# +# usage: slony-setup freesideuser + +use strict; +use DBI; +use FS::UID qw(adminsuidsetup); +use FS::Record qw(dbdef); + +my $user = shift or die "usage: slony-setup username\n"; +adminsuidsetup($user); + +#--- + +my $MASTERHOST = '192.168.20.10'; +my $SLAVEHOST = '192.168.20.50'; +#my $REPLICATIONUSER='pgsql'; +my $REPLICATIONUSER='postgres'; + +#-------- + +print <<END; + +#on slave: +useradd freeside +cp -pr /etc/skel /home/freeside +chown -R freeside /home/freeside + +su postgres -c 'createuser freeside' #n y n +su freeside -c 'createdb freeside' + +#on master: +su postgres -c 'createlang plpgsql freeside' + +pg_dump -s -U $REPLICATIONUSER -h $MASTERHOST freeside | psql -U $REPLICATIONUSER -h $SLAVEHOST freeside + +END + +#-------- + +#drop set ( id = 1, origin = 1); + +print <<END; +#on master: +slonik <<_EOF_ + +cluster name = freeside; +node 1 admin conninfo = 'dbname=freeside host=$MASTERHOST user=$REPLICATIONUSER'; +node 2 admin conninfo = 'dbname=freeside host=$SLAVEHOST user=$REPLICATIONUSER'; +init cluster ( id=1, comment = 'Master Node'); + +create set (id=1, origin=1, comment='All freeside tables'); + +END + +my $id = 1; + +foreach my $table ( dbdef->tables ) { + #next if $table =~ /^sql_/i; + print "set add table (set id=1, origin=1, id=". $id++. ", fully qualified name = 'public.$table' );\n"; + +} + +print <<END; + +store node (id=2, comment = 'Slave node'); +store path (server = 1, client = 2, conninfo='dbname=freeside host=$MASTERHOST user=$REPLICATIONUSER'); +store path (server = 2, client = 1, conninfo='dbname=freeside host=$SLAVEHOST user=$REPLICATIONUSER'); +store listen (origin=1, provider = 1, receiver =2); +store listen (origin=2, provider = 2, receiver =1); + +_EOF_ +END + +print <<END; + +### start slon processes (both machines) (this is debian-specific) +mkdir /etc/slony1/freeside + +cat >/etc/slony1/freeside/slon.conf <<_EOF_ +# Set the cluster name that this instance of slon is running against +# default is to read it off the command line +cluster_name='freeside' + +# Set slon's connection info, default is to read it off the command line +conn_info='host=localhost port=5432 dbname=freeside user=postgres' +_EOF_ + +/etc/init.d/slony1 start + +END + + +print <<END; +#on master: +slonik <<_EOF_ + +cluster name = freeside; + +node 1 admin conninfo = 'dbname=freeside host=$MASTERHOST user=$REPLICATIONUSER'; +node 2 admin conninfo = 'dbname=freeside host=$SLAVEHOST user=$REPLICATIONUSER'; + +subscribe set ( id = 1, provider = 1, receiver = 2, forward = no); + +_EOF_ +END + diff --git a/bin/sqlradius-norealm.reimport b/bin/sqlradius-norealm.reimport new file mode 100755 index 000000000..b7d016609 --- /dev/null +++ b/bin/sqlradius-norealm.reimport @@ -0,0 +1,113 @@ +#!/usr/bin/perl -Tw + +use strict; +use vars qw(%part_svc); +#use Date::Parse; +use DBI; +use Term::Query qw(query); +use FS::UID qw(adminsuidsetup); #datasrc +use FS::Record qw(qsearch qsearchs); +use FS::svc_acct; +use FS::part_svc; + +my $user = shift or die &usage; +adminsuidsetup $user; + +#push @FS::svc_acct::shells, qw(/bin/sync /sbin/shutdown /bin/halt /sbin/halt); #others? + +$FS::svc_Common::noexport_hack = 1; + +### + +%part_svc=map { $_->svcpart, $_ } qsearch('part_svc',{'svcdb'=>'svc_acct'}); + +die "No services with svcdb svc_acct!\n" unless %part_svc; + +print "\n\n", &menu_svc, "\n", <<END; +Enter part number to import. +END +my $sqlradius_svcpart = &getpart; + +my $datasrc = &getvalue("\n\nEnter the DBI datasource:"); +my $db_user = &getvalue("\n\nEnter the database user:"); +my $db_pass = &getvalue("\n\nEnter the database password:"); + +sub menu_svc { + ( join "\n", map "$_: ".$part_svc{$_}->svc, sort keys %part_svc ). "\n"; +} +sub getpart { + $^W=0; # Term::Query isn't -w-safe + my $return = query "Enter part number:", 'irk', [ keys %part_svc ]; + $^W=1; + $return; +} +sub getvalue { + my $prompt = shift; + $^W=0; # Term::Query isn't -w-safe + my $return = query $prompt, ''; + $^W=1; + $return; +} + +print "\n\n"; + +### + +my $dbh = DBI->connect( $datasrc, $db_user, $db_pass ) + or die $DBI::errstr; + +my $sth = $dbh->prepare('SELECT DISTINCT UserName FROM radcheck') + or die $dbh->errstr; +$sth->execute or die $sth->errstr; + +my $row; +while ( defined ( $row = $sth->fetchrow_arrayref ) ) { + my( $username ) = @$row; + + my( $password, $group ) = ( '', '', '' ); + + my $rc_sth = $dbh->prepare( + 'SELECT Attribute, Value'. + ' FROM radcheck'. + ' WHERE UserName = ?' + ) or die $dbh->errstr; + $rc_sth->execute($username) or die $rc_sth->errstr; + + foreach my $rc_row ( @{$rc_sth->fetchall_arrayref} ) { + my($attribute, $value) = @$rc_row; + if ( $attribute =~ /^((Crypt|User)-)?Password$/ ) { + $password = $value unless $password && !$1; + } else { + #handle other params! + } + } + + my @svc_acct = grep { $_->cust_svc->svcpart == $sqlradius_svcpart } + qsearch('svc_acct', { 'username' => $username, } ); + + #print "$r_username / $realm: $password / $finger: "; + print "$username: $password: "; + if ( scalar(@svc_acct) == 0 ) { + print "not found\n"; + next; + } elsif ( scalar(@svc_acct) > 1 ) { + print "multiple matches found?!?!\n"; + next; + } else { + #print "correcting password and name\n"; + print "correcting password\n"; + } + + my $svc_acct = $svc_acct[0]; + #my $new = new FS::svc_acct { $svc_acct->hash, '_password' => $password, 'finger' => $finger }; + my $new = new FS::svc_acct { $svc_acct->hash, '_password' => $password }; + my $error = $new->replace($svc_acct); + #my $error = $new->check; + die "$username: $error" if $error; + +} + +sub usage { + die "Usage:\n\n sqlradius-norealm.reimport user\n"; +} + diff --git a/bin/sqlradius.import b/bin/sqlradius.import new file mode 100644 index 000000000..e75f65b17 --- /dev/null +++ b/bin/sqlradius.import @@ -0,0 +1,152 @@ +#!/usr/bin/perl -Tw + +use strict; +use vars qw(%part_svc %domain_part_svc); +#use Date::Parse; +use DBI; +use Term::Query qw(query); +use FS::UID qw(adminsuidsetup); #datasrc +use FS::Record qw(qsearch qsearchs); +use FS::svc_acct; +use FS::part_svc; +use FS::svc_domain; + +my $user = shift or die &usage; +adminsuidsetup $user; + +#push @FS::svc_acct::shells, qw(/bin/sync /sbin/shutdown /bin/halt /sbin/halt); #others? + +$FS::svc_Common::noexport_hack = 1; +$FS::svc_domain::whois_hack = 1; + +### + +%part_svc=map { $_->svcpart, $_ } qsearch('part_svc',{'svcdb'=>'svc_acct'}); + +die "No services with svcdb svc_acct!\n" unless %part_svc; + +print "\n\n", &menu_svc, "\n", <<END; +Enter part number to import. +END +my $sqlradius_svcpart = &getpart; + +%domain_part_svc = map { $_->svcpart, $_ } + qsearch('part_svc', { 'svcdb' => 'svc_domain'} ); + +die "No services with svcdb svc_domain!\n" unless %domain_part_svc; + +print "\n\n", &menu_domain_svc, "\n", <<END; +Enter part number for domains. +END +my $domain_svcpart = &getdomainpart; + +my $datasrc = &getvalue("\n\nEnter the DBI datasource:"); +my $db_user = &getvalue("\n\nEnter the database user:"); +my $db_pass = &getvalue("\n\nEnter the database password:"); + +sub menu_svc { + ( join "\n", map "$_: ".$part_svc{$_}->svc, sort keys %part_svc ). "\n"; +} +sub menu_domain_svc { + ( join "\n", map "$_: ".$domain_part_svc{$_}->svc, sort keys %domain_part_svc ). "\n"; +} +sub getpart { + $^W=0; # Term::Query isn't -w-safe + my $return = query "Enter part number:", 'irk', [ keys %part_svc ]; + $^W=1; + $return; +} +sub getdomainpart { + $^W=0; # Term::Query isn't -w-safe + my $return = query "Enter part number:", 'irk', [ keys %domain_part_svc ]; + $^W=1; + $return; +} +sub getvalue { + my $prompt = shift; + $^W=0; # Term::Query isn't -w-safe + my $return = query $prompt, ''; + $^W=1; + $return; +} + +print "\n\n"; + +### + +my $dbh = DBI->connect( $datasrc, $db_user, $db_pass ) + or die $DBI::errstr; + +my $sth = $dbh->prepare('SELECT DISTINCT UserName, Realm FROM radcheck') + or die $dbh->errstr; +$sth->execute or die $sth->errstr; + +my $row; +while ( defined ( $row = $sth->fetchrow_arrayref ) ) { + my( $r_username, $realm ) = @$row; + + my( $username, $domain ); + if ( $r_username =~ /^([^@]+)\@([^@]+)$/ ) { + $username = $1; + $domain = $2; + } else { + $username = $r_username; + $domain = $realm; + } + my $svc_domain = qsearchs('svc_domain', { 'domain' => $domain } ) + || new FS::svc_domain { + 'domain' => $domain, + 'svcpart' => $domain_svcpart, + 'action' => 'N', + }; + unless ( $svc_domain->svcnum ) { + my $error = $svc_domain->insert; + if ( $error ) { + die "can't insert domain $domain: $error\n"; + } + } + + my( $password, $finger, $group ) = ( '', '', '' ); + + my $rc_sth = $dbh->prepare( + 'SELECT Attribute, Value, Name, GroupName'. + ' FROM radcheck'. + ' WHERE UserName = ? and Realm = ?' + ) or die $dbh->errstr; + $rc_sth->execute($r_username, $realm) or die $rc_sth->errstr; + + foreach my $rc_row ( @{$rc_sth->fetchall_arrayref} ) { + my($attribute, $value, $name, $groupname) = @$rc_row; + if ( $attribute =~ /^((User|Crypt)-)?Password$/ ) { + $password = $value; + $finger = $name; + $group = $groupname; + } else { + #handle other params! + } + } + + my $svc_acct = new FS::svc_acct { + 'svcpart' => $sqlradius_svcpart, + 'username' => $username, + 'domsvc' => $svc_domain->svcnum, + '_password' => $password, + 'finger' => $finger, + }; + + my $error = $svc_acct->insert; + #my $error = $svc_acct->check; + if ( $error ) { + if ( $error =~ /duplicate/i ) { + warn "$r_username / $realm: $error"; + } else { + die "$r_username / $realm: $error"; + } + } + +} + +sub usage { + die "Usage:\n\n sqlradius.import user\n"; +} + diff --git a/bin/sqlradius.reimport b/bin/sqlradius.reimport new file mode 100755 index 000000000..2218a3f13 --- /dev/null +++ b/bin/sqlradius.reimport @@ -0,0 +1,160 @@ +#!/usr/bin/perl -Tw + +use strict; +use vars qw(%part_svc %domain_part_svc); +#use Date::Parse; +use DBI; +use Term::Query qw(query); +use FS::UID qw(adminsuidsetup); #datasrc +use FS::Record qw(qsearch qsearchs); +use FS::svc_acct; +use FS::part_svc; +use FS::svc_domain; + +my $user = shift or die &usage; +adminsuidsetup $user; + +#push @FS::svc_acct::shells, qw(/bin/sync /sbin/shutdown /bin/halt /sbin/halt); #others? + +$FS::svc_Common::noexport_hack = 1; +$FS::svc_domain::whois_hack = 1; + +### + +%part_svc=map { $_->svcpart, $_ } qsearch('part_svc',{'svcdb'=>'svc_acct'}); + +die "No services with svcdb svc_acct!\n" unless %part_svc; + +print "\n\n", &menu_svc, "\n", <<END; +Enter part number to import. +END +my $sqlradius_svcpart = &getpart; + +%domain_part_svc = map { $_->svcpart, $_ } + qsearch('part_svc', { 'svcdb' => 'svc_domain'} ); + +die "No services with svcdb svc_domain!\n" unless %domain_part_svc; + +print "\n\n", &menu_domain_svc, "\n", <<END; +Enter part number for domains. +END +my $domain_svcpart = &getdomainpart; + +my $datasrc = &getvalue("\n\nEnter the DBI datasource:"); +my $db_user = &getvalue("\n\nEnter the database user:"); +my $db_pass = &getvalue("\n\nEnter the database password:"); + +sub menu_svc { + ( join "\n", map "$_: ".$part_svc{$_}->svc, sort keys %part_svc ). "\n"; +} +sub menu_domain_svc { + ( join "\n", map "$_: ".$domain_part_svc{$_}->svc, sort keys %domain_part_svc ). "\n"; +} +sub getpart { + $^W=0; # Term::Query isn't -w-safe + my $return = query "Enter part number:", 'irk', [ keys %part_svc ]; + $^W=1; + $return; +} +sub getdomainpart { + $^W=0; # Term::Query isn't -w-safe + my $return = query "Enter part number:", 'irk', [ keys %domain_part_svc ]; + $^W=1; + $return; +} +sub getvalue { + my $prompt = shift; + $^W=0; # Term::Query isn't -w-safe + my $return = query $prompt, ''; + $^W=1; + $return; +} + +print "\n\n"; + +### + +my $dbh = DBI->connect( $datasrc, $db_user, $db_pass ) + or die $DBI::errstr; + +my $sth = $dbh->prepare('SELECT DISTINCT UserName, Realm FROM radcheck') + or die $dbh->errstr; +$sth->execute or die $sth->errstr; + +my $row; +while ( defined ( $row = $sth->fetchrow_arrayref ) ) { + my( $r_username, $realm ) = @$row; + + my( $username, $domain ); + if ( $r_username =~ /^([^@]+)\@([^@]+)$/ ) { + $username = $1; + $domain = $2; + } else { + $username = $r_username; + $domain = $realm; + } + my $svc_domain = qsearchs('svc_domain', { 'domain' => $domain } ) + || new FS::svc_domain { + 'domain' => $domain, + 'svcpart' => $domain_svcpart, + 'action' => 'N', + }; + unless ( $svc_domain->svcnum ) { + die "new domain? wtf"; + my $error = $svc_domain->insert; + if ( $error ) { + die "can't insert domain $domain: $error\n"; + } + } + + #my( $password, $finger, $group ) = ( '', '', '' ); + my( $password, $group ) = ( '', '', '' ); + + my $rc_sth = $dbh->prepare( + 'SELECT Attribute, Value, Name, GroupName'. + ' FROM radcheck'. + ' WHERE UserName = ? and Realm = ?' + ) or die $dbh->errstr; + $rc_sth->execute($r_username, $realm) or die $rc_sth->errstr; + + foreach my $rc_row ( @{$rc_sth->fetchall_arrayref} ) { + my($attribute, $value, $name, $groupname) = @$rc_row; + if ( $attribute =~ /^((Crypt|User)-)?Password$/ ) { + $password = $value; + #$finger = $name; + $group = $groupname; + } else { + #handle other params! + } + } + + my @svc_acct = grep { $_->cust_svc->svcpart == $sqlradius_svcpart } + qsearch('svc_acct', { 'username' => $username, + 'domsvc' => $svc_domain->svcnum, } ); + + #print "$r_username / $realm: $password / $finger: "; + print "$r_username / $realm: $password: "; + if ( scalar(@svc_acct) == 0 ) { + print "not found\n"; + next; + } elsif ( scalar(@svc_acct) > 1 ) { + print "multiple matches found?!?!\n"; + next; + } else { + #print "correcting password and name\n"; + print "correcting password\n"; + } + + my $svc_acct = $svc_acct[0]; + #my $new = new FS::svc_acct { $svc_acct->hash, '_password' => $password, 'finger' => $finger }; + my $new = new FS::svc_acct { $svc_acct->hash, '_password' => $password }; + my $error = $new->replace($svc_acct); + #my $error = $new->check; + die "$r_username / $realm: $error" if $error; + +} + +sub usage { + die "Usage:\n\n sqlradius.reimport user\n"; +} + diff --git a/bin/strip-eps b/bin/strip-eps new file mode 100755 index 000000000..2c2d124d7 --- /dev/null +++ b/bin/strip-eps @@ -0,0 +1,20 @@ +#!/usr/bin/perl -w + +# Author: Andy Turner <andrew.turner@acadia.net> + +use strict; + +# The first line has some binary magic for file identification +# purposes. GhostScript doesn't like it. Strip it. +scalar <>; + +# Add a header so that we can use magic to determine the file type. +print "%!PS-Adobe-3.0 EPSF-3.0\n"; + +while (<>) { + print; + + # Illustrator Version 7 format EPS files have a bunch of binary gook + # after the "%%EOF" line. (% is a comment in PostScript, right?) + last if /^%%EOF/; +} diff --git a/bin/svc_acct.import b/bin/svc_acct.import new file mode 100755 index 000000000..aff26b943 --- /dev/null +++ b/bin/svc_acct.import @@ -0,0 +1,237 @@ +#!/usr/bin/perl -Tw + +use strict; +use vars qw(%part_svc); +use Date::Parse; +use Term::Query qw(query); +use Net::SCP qw(iscp); +use FS::UID qw(adminsuidsetup datasrc); +use FS::Record qw(qsearch); +use FS::svc_acct; +use FS::part_svc; + +my $user = shift or die &usage; +adminsuidsetup $user; + +push @FS::svc_acct::shells, qw(/bin/sync /sbin/shuddown /bin/halt); #others? + +my($spooldir)="/usr/local/etc/freeside/export.". datasrc; + +$FS::svc_acct::nossh_hack = 1; + +### + +%part_svc=map { $_->svcpart, $_ } qsearch('part_svc',{'svcdb'=>'svc_acct'}); + +die "No services with svcdb svc_acct!\n" unless %part_svc; + +print "\n\n", &menu_svc, "\n", <<END; +Most accounts probably have entries in passwd and users (with Port-Limit +nonexistant or 1). +END +my($ppp_svcpart)=&getpart; + +print "\n\n", &menu_svc, "\n", <<END; +Some accounts have entries in passwd and users, but with Port-Limit 2 (or +more). +END +my($isdn_svcpart)=&getpart; + +print "\n\n", &menu_svc, "\n", <<END; +Some accounts might have entries in users only (Port-Limit 1) +END +my($oppp_svcpart)=&getpart; + +print "\n\n", &menu_svc, "\n", <<END; +Some accounts might have entries in users only (Port-Limit >= 2) +END +my($oisdn_svcpart)=&getpart; + +print "\n\n", &menu_svc, "\n", <<END; +POP mail accounts have entries in passwd only, and have a particular shell. +END +my($pop_shell)=&getvalue("Enter that shell:"); +my($popmail_svcpart)=&getpart; + +print "\n\n", &menu_svc, "\n", <<END; +Everything else in passwd is a shell account. +END +my($shell_svcpart)=&getpart; + +print "\n\n", <<END; +Enter the location and name of your _user_ passwd file, for example +"mail.isp.com:/etc/passwd" or "nis.isp.com:/etc/global/passwd" +END +my($loc_passwd)=&getvalue(":"); +iscp("root\@$loc_passwd", "$spooldir/passwd.import"); + +print "\n\n", <<END; +Enter the location and name of your _user_ shadow file, for example +"mail.isp.com:/etc/shadow" or "bsd.isp.com:/etc/master.passwd" +END +my($loc_shadow)=&getvalue(":"); +iscp("root\@$loc_shadow", "$spooldir/shadow.import"); + +print "\n\n", <<END; +Enter the location and name of your radius "users" file, for example +"radius.isp.com:/etc/raddb/users" +END +my($loc_users)=&getvalue(":"); +iscp("root\@$loc_users", "$spooldir/users.import"); + +sub menu_svc { + ( join "\n", map "$_: ".$part_svc{$_}->svc, sort keys %part_svc ). "\n"; +} +sub getpart { + $^W=0; # Term::Query isn't -w-safe + my $return = query "Enter part number:", 'irk', [ keys %part_svc ]; + $^W=1; + $return; +} +sub getvalue { + my $prompt = shift; + $^W=0; # Term::Query isn't -w-safe + my $return = query $prompt, ''; + $^W=1; + $return; +} + +print "\n\n"; + +### + +open(PASSWD,"<$spooldir/passwd.import"); +open(SHADOW,"<$spooldir/shadow.import"); +open(USERS,"<$spooldir/users.import"); + +my(%upassword,%ip,%allparam); +my(%param,$username); +while (<USERS>) { + chop; + next if /^\s*$/; + next if /^\s*#/; + if ( /^\S/ ) { + /^(\w+)\s+(Auth-Type\s+=\s+Local,\s+)?Password\s+=\s+"([^"]+)"(,\s+Expiration\s+=\s+"([^"]*")\s*)?$/ + or die "1Unexpected line in users.import: $_"; + my($password,$expiration); + ($username,$password,$expiration)=(lc($1),$3,$5); + $password = '' if $password eq 'UNIX'; + $upassword{$username}=$password; + undef %param; + } else { + die "2Unexpected line in users.import: $_"; + } + while (<USERS>) { + chop; + if ( /^\s*$/ ) { + if ( defined $param{'radius_Framed_IP_Address'} ) { + $ip{$username} = $param{'radius_Framed_IP_Address'}; + delete $param{'radius_Framed_IP_Address'}; + } else { + $ip{$username} = '0e0'; + } + $allparam{$username}={ %param }; + last; + } elsif ( /^\s+([\w\-]+)\s=\s"?([\w\.\-\s]+)"?,?\s*$/ ) { + my($attribute,$value)=($1,$2); + $attribute =~ s/\-/_/g; + $param{'radius_'.$attribute}=$value; + } else { + die "3Unexpected line in users.import: $_"; + } + } +} +#? incase there isn't a terminating blank line ? +if ( defined $param{'radius_Framed_IP_Address'} ) { + $ip{$username} = $param{'radius_Framed_IP_Address'}; + delete $param{'radius_Framed_IP_Address'}; +} else { + $ip{$username} = '0e0'; +} +$allparam{$username}={ %param }; + +my(%password); +while (<SHADOW>) { + chop; + my($username,$password)=split(/:/); + #$password =~ s/^\!$/\*/; + #$password =~ s/\!+/\*SUSPENDED\* /; + $password{$username}=$password; +} + +while (<PASSWD>) { + chop; + my($username,$x,$uid,$gid,$finger,$dir,$shell)=split(/:/); + my($password)=$upassword{$username} || $password{$username}; + + my($maxb)=${$allparam{$username}}{'radius_Port_Limit'}; + my($svcpart); + if ( exists $upassword{$username} ) { + if ( $maxb >= 2 ) { + $svcpart = $isdn_svcpart + } elsif ( ! $maxb || $maxb == 1 ) { + $svcpart = $ppp_svcpart + } else { + die "Illegal Port-Limit in users ($username)!\n"; + } + } elsif ( $shell eq $pop_shell ) { + $svcpart = $popmail_svcpart; + } else { + $svcpart = $shell_svcpart; + } + + my($svc_acct) = new FS::svc_acct ({ + 'svcpart' => $svcpart, + 'username' => $username, + '_password' => $password, + 'uid' => $uid, + 'gid' => $gid, + 'finger' => $finger, + 'dir' => $dir, + 'shell' => $shell, + 'slipip' => $ip{$username}, + %{$allparam{$username}}, + }); + my($error); + $error=$svc_acct->insert; + die $error if $error; + + delete $allparam{$username}; + delete $upassword{$username}; +} + +#my($username); +foreach $username ( keys %upassword ) { + my($password)=$upassword{$username}; + + my($maxb)=${$allparam{$username}}{'radius_Port_Limit'} || 0; + my($svcpart); + if ( $maxb == 2 ) { + $svcpart = $oisdn_svcpart + } elsif ( ! $maxb || $maxb == 1 ) { + $svcpart = $oppp_svcpart + } else { + die "Illegal Port-Limit in users!\n"; + } + + my($svc_acct) = new FS::svc_acct ({ + 'svcpart' => $svcpart, + 'username' => $username, + '_password' => $password, + 'slipip' => $ip{$username}, + %{$allparam{$username}}, + }); + my($error); + $error=$svc_acct->insert; + die $error, if $error; + + delete $allparam{$username}; + delete $upassword{$username}; +} + +# + +sub usage { + die "Usage:\n\n svc_acct.import user\n"; +} + diff --git a/bin/svc_acct_pop.import b/bin/svc_acct_pop.import new file mode 100755 index 000000000..9e3d38bfe --- /dev/null +++ b/bin/svc_acct_pop.import @@ -0,0 +1,59 @@ +#!/usr/bin/perl + +use strict; +use Text::CSV_XS; +use FS::UID qw(adminsuidsetup); +use FS::svc_acct_pop; + +my @fields = qw( ac loc state city exch ); +my $fixup = sub { + my $hash = shift; + $hash->{ac} =~ /^\s*(\d{3})\s*$/; + $hash->{ac} = $1; + $hash->{loc} =~ /^\s*(\d{3})(\d{4})\s*$/; + $hash->{exch} = $1; + $hash->{loc} = $2; + $hash->{state} =~ /^\s*(\S{0,2})\s*$/; + $hash->{state} = $1; + $hash->{city} =~ /^\s*(.*?)\s*$/; + $hash->{city} = $1; + + }; + +my $user = shift or usage(); +adminsuidsetup $user; + +my $file = shift or usage(); +my $csv = new Text::CSV_XS; + +open(FH, $file) or die "cannot open $file: $!"; + +sub usage { + die "Usage:\n\n svc_acct_pop.import user popfile.csv\n\n"; +} + +### + +my $line; +while ( defined($line=<FH>) ) { + chomp $line; + + $line &= "\177" x length($line); # i hope this isn't really necessary + $csv->parse($line) + or die "cannot parse: " . $csv->error_input(); + + my @values = $csv->fields(); + my %hash; + foreach my $field (@fields) { + $hash{$field} = shift @values; + } + + &{$fixup}(\%hash); + + my $svc_acct_pop = new FS::svc_acct_pop { %hash }; + + #my $error = $svc_acct_pop->check; + my $error = $svc_acct_pop->insert; + die $error if $error; + +} diff --git a/bin/svc_broadband.renumber b/bin/svc_broadband.renumber new file mode 100755 index 000000000..980fa0099 --- /dev/null +++ b/bin/svc_broadband.renumber @@ -0,0 +1,84 @@ +#!/usr/bin/perl + +use strict; + +use FS::UID qw(adminsuidsetup); +use FS::Record qw(qsearch qsearchs); +use FS::svc_Common; +use FS::part_svc_router; +use FS::svc_broadband; +use FS::router; +use FS::addr_block; + +$FS::svc_Common::noexport_hack = 1; #Disable exports! + +my $user = shift if $ARGV[0] or die &usage; +adminsuidsetup($user); + +my $remapfile = shift if $ARGV[0] or die &usage; +my $old_blocknum = shift if $ARGV[0] or die &usage; +my $new_blocknum = shift if $ARGV[0] or die &usage; +my $old_svcnum = shift if $ARGV[0]; + +my %ipmap; + +open(REMAP, "<$remapfile") or die $!; +while (<REMAP>) { + next unless (/^([0-9\.]+)\s+([0-9\.]+)$/); + my ($old_ip, $new_ip) = ($1, $2); + $ipmap{$old_ip} = $new_ip; +} +close(REMAP); + +my @svcs; +if ($old_svcnum) { + @svcs = ( qsearchs('svc_broadband', { svcnum => $old_svcnum, + blocknum => $old_blocknum }) ); +} else { + @svcs = qsearch('svc_broadband', { blocknum => $old_blocknum }); +} + +foreach my $old_sb (@svcs) { + + my $old_ip = $old_sb->ip_addr; + my $new_ip = $ipmap{$old_ip}; + print "Renumbering ${old_ip} (${old_blocknum}) => ${new_ip} (${new_blocknum})...\n"; + + + my $new_sb = new FS::svc_broadband + { $old_sb->hash, + ip_addr => $new_ip, + blocknum => $new_blocknum, + svcpart => $old_sb->cust_svc->svcpart, + }; + + my $error = $new_sb->replace($old_sb); + die $error if $error; + +} + + + +exit(0); + +sub usage { + + my $usage = <<EOT; +Usage: + svc_broadband.renumber user remapfile old_blocknum new_blocknum [ svcnum ] + +remapfile format: +old_ip_address new_ip_address +... + +Example remapfile: +10.0.0.5 192.168.0.5 +10.0.0.20 192.168.0.20 +10.0.0.32 192.168.0.3 + +Warning: This assumes your routers have already been reconfigured with the + new addresses. Exports will not be run! + +EOT + +} diff --git a/bin/svc_domain.erase b/bin/svc_domain.erase new file mode 100755 index 000000000..435dd5fdd --- /dev/null +++ b/bin/svc_domain.erase @@ -0,0 +1,15 @@ +#!/usr/bin/perl -w + +use strict; +use FS::UID qw(adminsuidsetup); +use FS::Record qw(qsearch); + +use FS::domain_record; +use FS::svc_domain; + +adminsuidsetup(shift @ARGV) or die "Usage: svc_domain.erase user\n"; + +foreach my $record ( qsearch('domain_record',{}), qsearch('svc_domain', {} ) ) { + my $error = $record->delete; + die $error if $error; +} diff --git a/bin/sysvshell.export b/bin/sysvshell.export new file mode 100755 index 000000000..c13912c3f --- /dev/null +++ b/bin/sysvshell.export @@ -0,0 +1,112 @@ +#!/usr/bin/perl -w + +# sysvshell export + +use strict; +use File::Rsync; +use Net::SSH qw(ssh); +use FS::UID qw(adminsuidsetup datasrc); +use FS::Record qw(qsearch qsearchs); +use FS::part_export; +use FS::cust_svc; +use FS::svc_acct; + +my @saltset = ( 'a'..'z' , 'A'..'Z' , '0'..'9' , '.' , '/' ); + +my $user = shift or die &usage; +adminsuidsetup $user; + +my $spooldir = "/usr/local/etc/freeside/export.". datasrc; +#my $spooldir = "/usr/local/etc/freeside/export.". datasrc. "/shell"; + +my @sysv_exports = qsearch('part_export', { 'exporttype' => 'sysvshell' } ); + +my $rsync = File::Rsync->new({ + rsh => 'ssh', +# dry_run => 1, +}); + +foreach my $export ( @sysv_exports ) { + my $machine = $export->machine; + my $prefix = "$spooldir/$machine"; + mkdir $prefix, 0700 unless -d $prefix; + + #LOCKING!!! + + ( open(SHADOW,">$prefix/shadow") + #!!! and flock(SHADOW,LOCK_EX|LOCK_NB) + ) or die "Can't open $prefix/shadow: $!"; + ( open(PASSWD,">$prefix/passwd") + #!!! and flock(PASSWD,LOCK_EX|LOCK_NB) + ) or die "Can't open $prefix/passwd: $!"; + + chmod 0644, "$prefix/passwd"; + chmod 0600, "$prefix/shadow"; + + my @svc_acct = $export->svc_x; + + next unless @svc_acct; + + foreach my $svc_acct ( sort { $a->uid <=> $b->uid } @svc_acct ) { + + my $password = $svc_acct->_password; + my $cpassword; + #if ( ( length($password) <= 8 ) + if ( ( length($password) <= 12 ) + && ( $password ne '*' ) + && ( $password ne '!!' ) + && ( $password ne '' ) + ) { + $cpassword=crypt($password, + $saltset[int(rand(64))].$saltset[int(rand(64))] + ); + # MD5 !!!! + } else { + $cpassword=$password; + } + + ### + # FORMAT OF THE PASSWD FILE HERE + print PASSWD join(":", + $svc_acct->username, + 'x', # "##". $username, + $svc_acct->uid, + $svc_acct->gid, + $svc_acct->finger, + $svc_acct->dir, + $svc_acct->shell, + ), "\n"; + + ### + # FORMAT OF THE SHADOW FILE HERE + print SHADOW join(":", + $svc_acct->username, + $cpassword, + '', + '', + '', + '', + '', + '', + '', + ), "\n"; + + } + + #!!! flock(SHADOW,LOCK_UN); + #!!! flock(PASSWD,LOCK_UN); + close SHADOW; + close PASSWD; + + $rsync->exec( { + src => "$prefix/shadow", + dest => "root\@$machine:/etc/shadow" + } ) or die "rsync to $machine failed: ". join(" / ", $rsync->err); + + $rsync->exec( { + src => "$prefix/passwd", + dest => "root\@$machine:/etc/passwd" + } ) or die "rsync to $machine failed: ". join(" / ", $rsync->err); + + # UNLOCK!! +} |