Our humongous patch
Kristian Hoffmann
khoff at pc-intouch.com
Fri Oct 13 13:10:49 PDT 2000
Here's our patch against the current cvs tree. It includes:
Added svc_wireless and interfaces.
Added svc_www interface.
Added default A and CNAME record creation for svc_domain.
Added modifications for ascend RADIUS w/ MySQL patches.
radius attribute templates, logging, and auth are all done directly
with the database.
Removed cust_credit and merged it into cust_pay so credits would apply to
invoices after they were created. Because of this, we can remove
the cust_bill.owed field and calculate invoice balances on the fly.
This removes the ability to post a credit before an invoice is
generated. Included script fix-credits converts old-style credits
into new credits.
Added per package "bill now."
Added cust_notes for recording comments on a customer.
Added invoice_bcc configuration file.
Changed svc_acct_sm to do generic forwarding. (So sue us. We use exim
and this rocks! It reads our aliases directly from the
database) Perhaps there could be a config file to specify
what type of alias support to do.
Added reports for account usage via RADIUS logs in the database, customer
balance report, and invoice aging. We like your aging reports
better but figured we'd include ours anyway.
Added multi-tiered commission system. No interface yet. Included
freeside-commission script.
What the heck is meow besides the sound a cat makes?
Included is our patch as well as our current database structure.
-Kristian
<khoff at pc-intouch.com>
-------------- next part --------------
# MySQL dump 7.1
#
# Host: localhost Database: freeside
#--------------------------------------------------------
# Server version 3.22.32-log
#
# Table structure for table 'agent'
#
CREATE TABLE agent (
prog varchar(255),
typenum int(11) DEFAULT '0' NOT NULL,
freq int(11),
agent varchar(80) DEFAULT '' NOT NULL,
agentnum int(11) DEFAULT '0' NOT NULL auto_increment,
parent int(11) DEFAULT '0' NOT NULL,
PRIMARY KEY (agentnum),
KEY typenum (typenum),
KEY agent__typenum_index (typenum)
);
#
# Table structure for table 'agent_type'
#
CREATE TABLE agent_type (
typenum int(11) DEFAULT '0' NOT NULL,
atype varchar(80) DEFAULT '' NOT NULL,
PRIMARY KEY (typenum)
);
#
# Table structure for table 'agent_type_pkg'
#
CREATE TABLE agent_type_pkg (
typenum int(11) DEFAULT '0' NOT NULL,
pkgpart int(11) DEFAULT '0' NOT NULL,
tier int(11) DEFAULT '0' NOT NULL,
commission double(16,4) DEFAULT '0.0000' NOT NULL,
PRIMARY KEY (typenum,pkgpart,tier)
);
#
# Table structure for table 'cust_bill'
#
CREATE TABLE cust_bill (
_date int(11),
invnum int(11) DEFAULT '0' NOT NULL,
printed int(11) DEFAULT '0' NOT NULL,
charged decimal(10,2) DEFAULT '0.00' NOT NULL,
bogus_owed decimal(10,2) DEFAULT '0.00' NOT NULL,
custnum int(11) DEFAULT '0' NOT NULL,
com_paid int(11),
PRIMARY KEY (invnum),
KEY custnum (custnum),
KEY cust_bill__custnum_index (custnum)
);
#
# Table structure for table 'cust_bill_pkg'
#
CREATE TABLE cust_bill_pkg (
sdate int(11),
recur decimal(10,2) DEFAULT '0.00' NOT NULL,
setup decimal(10,2) DEFAULT '0.00' NOT NULL,
pkgnum int(11) DEFAULT '0' NOT NULL,
edate int(11),
invnum int(11) DEFAULT '0' NOT NULL,
UNIQUE pkgnum (pkgnum,invnum),
KEY invnum (invnum),
UNIQUE cust_bill_pkg__pkgnum_invnum_index (pkgnum,invnum),
KEY cust_bill_pkg__invnum_index (invnum)
);
#
# Table structure for table 'cust_credit'
#
CREATE TABLE cust_credit (
reason varchar(255) DEFAULT '' NOT NULL,
credited decimal(10,2) DEFAULT '0.00' NOT NULL,
amount decimal(10,2) DEFAULT '0.00' NOT NULL,
_date int(11),
otaker varchar(8) DEFAULT '' NOT NULL,
crednum int(11) DEFAULT '0' NOT NULL,
custnum int(11) DEFAULT '0' NOT NULL,
PRIMARY KEY (crednum),
KEY custnum (custnum),
KEY cust_credit__custnum_index (custnum)
);
#
# Table structure for table 'cust_main'
#
CREATE TABLE cust_main (
state varchar(80),
county varchar(80),
payby varchar(4) DEFAULT '' NOT NULL,
otaker varchar(8) DEFAULT '' NOT NULL,
ss varchar(11),
paydate varchar(10),
first varchar(80) DEFAULT '' NOT NULL,
payname varchar(80),
refnum int(11) DEFAULT '0' NOT NULL,
tax char(1),
daytime varchar(20),
custnum int(11) DEFAULT '0' NOT NULL,
zip varchar(10) DEFAULT '' NOT NULL,
last varchar(80) DEFAULT '' NOT NULL,
country char(2) DEFAULT '' NOT NULL,
payinfo varchar(16),
company varchar(80),
address1 varchar(80) DEFAULT '' NOT NULL,
address2 varchar(80),
city varchar(80) DEFAULT '' NOT NULL,
night varchar(20),
agentnum int(11) DEFAULT '0' NOT NULL,
fax varchar(12),
PRIMARY KEY (custnum),
KEY last (last),
KEY cust_main__last_index (last)
);
#
# Table structure for table 'cust_main_county'
#
CREATE TABLE cust_main_county (
state varchar(80),
county varchar(80),
country char(2) DEFAULT '' NOT NULL,
tax double(16,4) DEFAULT '0.0000' NOT NULL,
taxnum int(11) DEFAULT '0' NOT NULL,
PRIMARY KEY (taxnum)
);
#
# Table structure for table 'cust_main_invoice'
#
CREATE TABLE cust_main_invoice (
dest varchar(80) DEFAULT '' NOT NULL,
destnum int(11) DEFAULT '0' NOT NULL,
custnum int(11) DEFAULT '0' NOT NULL,
PRIMARY KEY (destnum),
KEY custnum (custnum),
KEY cust_main_invoice__custnum_index (custnum)
);
#
# Table structure for table 'cust_main_ref'
#
CREATE TABLE cust_main_ref (
custref int(11) DEFAULT '0' NOT NULL,
custnum int(11) DEFAULT '0' NOT NULL,
credited int(1) DEFAULT '0' NOT NULL,
pkgnum int(11) DEFAULT '0' NOT NULL,
KEY custnum (custnum),
KEY pkgnum (pkgnum)
);
#
# Table structure for table 'cust_notes'
#
CREATE TABLE cust_notes (
notenum int(11) DEFAULT '0' NOT NULL auto_increment,
notes blob,
custnum int(11) DEFAULT '0' NOT NULL,
notedate int(11),
PRIMARY KEY (notenum),
KEY custnum (custnum)
);
#
# Table structure for table 'cust_pay'
#
CREATE TABLE cust_pay (
paynum int(11) DEFAULT '0' NOT NULL,
paybatch varchar(80),
payby varchar(4) DEFAULT '' NOT NULL,
_date int(11),
invnum int(11) DEFAULT '0' NOT NULL,
payinfo varchar(16),
paid decimal(10,2) DEFAULT '0.00' NOT NULL,
credit tinyint(4) DEFAULT '0' NOT NULL,
PRIMARY KEY (paynum),
KEY invnum (invnum),
KEY cust_pay__invnum_index (invnum)
);
#
# Table structure for table 'cust_pay_batch'
#
CREATE TABLE cust_pay_batch (
trancode int(11) DEFAULT '0' NOT NULL,
state varchar(80) DEFAULT '' NOT NULL,
zip varchar(10) DEFAULT '' NOT NULL,
amount decimal(10,2) DEFAULT '0.00' NOT NULL,
country char(2) DEFAULT '' NOT NULL,
last varchar(80) DEFAULT '' NOT NULL,
address1 varchar(80) DEFAULT '' NOT NULL,
first varchar(80) DEFAULT '' NOT NULL,
address2 varchar(80),
city varchar(80) DEFAULT '' NOT NULL,
cardnum varchar(16) DEFAULT '' NOT NULL,
payname varchar(80),
exp int(11),
invnum int(11) DEFAULT '0' NOT NULL,
custnum int(11) DEFAULT '0' NOT NULL,
KEY invnum (invnum),
KEY custnum (custnum),
KEY cust_pay_batch__invnum_index (invnum),
KEY cust_pay_batch__custnum_index (custnum)
);
#
# Table structure for table 'cust_pkg'
#
CREATE TABLE cust_pkg (
setup int(11),
pkgnum int(11) DEFAULT '0' NOT NULL,
bill int(11),
cancel int(11),
otaker varchar(8) DEFAULT '' NOT NULL,
pkgpart int(11) DEFAULT '0' NOT NULL,
susp int(11),
expire int(11),
custnum int(11) DEFAULT '0' NOT NULL,
PRIMARY KEY (pkgnum),
KEY custnum (custnum),
KEY cust_pkg__custnum_index (custnum)
);
#
# Table structure for table 'cust_refund'
#
CREATE TABLE cust_refund (
reason varchar(80) DEFAULT '' NOT NULL,
refund decimal(10,2) DEFAULT '0.00' NOT NULL,
refundnum int(11) DEFAULT '0' NOT NULL,
payby varchar(4) DEFAULT '' NOT NULL,
_date int(11),
payinfo varchar(16),
otaker varchar(8) DEFAULT '' NOT NULL,
crednum int(11) DEFAULT '0' NOT NULL,
PRIMARY KEY (refundnum),
KEY crednum (crednum),
KEY cust_refund__crednum_index (crednum)
);
#
# Table structure for table 'cust_svc'
#
CREATE TABLE cust_svc (
pkgnum int(11) DEFAULT '0' NOT NULL,
svcpart int(11) DEFAULT '0' NOT NULL,
svcnum int(11) DEFAULT '0' NOT NULL,
PRIMARY KEY (svcnum),
KEY svcnum (svcnum),
KEY pkgnum (pkgnum),
KEY svcpart (svcpart),
KEY cust_svc__svcnum_index (svcnum),
KEY cust_svc__pkgnum_index (pkgnum),
KEY cust_svc__svcpart_index (svcpart)
);
#
# Table structure for table 'domain_record'
#
CREATE TABLE domain_record (
recnum int(11) DEFAULT '0' NOT NULL,
svcnum int(11) DEFAULT '0' NOT NULL,
reczone varchar(80) DEFAULT '' NOT NULL,
recaf char(2) DEFAULT '' NOT NULL,
rectype varchar(5) DEFAULT '' NOT NULL,
recdata varchar(80) DEFAULT '' NOT NULL,
PRIMARY KEY (recnum)
);
#
# Table structure for table 'part_pkg'
#
CREATE TABLE part_pkg (
recur varchar(255),
setup varchar(255),
pkgpart int(11) DEFAULT '0' NOT NULL,
pkg varchar(80) DEFAULT '' NOT NULL,
comment varchar(80) DEFAULT '' NOT NULL,
freq int(11) DEFAULT '0' NOT NULL,
PRIMARY KEY (pkgpart)
);
#
# Table structure for table 'part_referral'
#
CREATE TABLE part_referral (
referral varchar(80) DEFAULT '' NOT NULL,
refnum int(11) DEFAULT '0' NOT NULL,
PRIMARY KEY (refnum)
);
#
# Table structure for table 'part_svc'
#
CREATE TABLE part_svc (
svc_acct__active int(1) DEFAULT '1' NOT NULL,
svc_acct_sm__domuser varchar(80),
svc_acct__dir varchar(80),
svcpart int(11) DEFAULT '0' NOT NULL,
svc_acct_sm__maildest varchar(80),
svc_acct__uid varchar(80),
svc_acct__radius_attr text,
svc_acct__quota_flag char(1),
svc_acct__expire_date_flag char(1),
svc_acct__start_date_flag char(1),
svc_acct__passwd_opt varchar(100),
svc varchar(80) DEFAULT '' NOT NULL,
svc_acct__slipip_flag char(1),
svc_acct___password_flag char(1),
svc_acct__start_date date,
svc_acct__gid_flag char(1),
svcdb varchar(80) DEFAULT '' NOT NULL,
svc_acct__attr_template_flag char(1),
svc_acct__shell_flag char(1),
svc_acct__slipip varchar(80),
svc_acct__active_flag char(1),
svc_acct_sm__domuser_flag char(1),
svc_acct__popnum_flag char(1),
svc_acct__uid_flag char(1),
svc_acct__radius_attr_flag char(1),
svc_acct__passwd_opt_flag char(1),
svc_acct__popnum varchar(80),
svc_acct__username varchar(80),
svc_acct_sm__domsvc_flag char(1),
svc_acct__expire_date date,
svc_acct__quota varchar(80),
svc_acct_sm__domsvc varchar(80),
svc_domain__domain_flag char(1),
svc_acct___password varchar(80),
svc_acct__gid varchar(80),
svc_acct__finger_flag char(1),
svc_domain__domain varchar(80),
svc_acct__attr_template varchar(32),
svc_acct__shell varchar(80),
svc_acct__dir_flag char(1),
svc_acct__username_flag char(1),
svc_acct_sm__maildest_flag char(1),
svc_acct__finger varchar(80),
svc_wireless__speed int(8),
svc_wireless__speed_flag char(1),
svc_wireless__active int(1),
svc_wireless__active_flag char(1),
PRIMARY KEY (svcpart)
);
#
# Table structure for table 'pkg_svc'
#
CREATE TABLE pkg_svc (
pkgpart int(11) DEFAULT '0' NOT NULL,
svcpart int(11) DEFAULT '0' NOT NULL,
quantity int(11) DEFAULT '0' NOT NULL,
UNIQUE pkgpart (pkgpart,svcpart),
KEY pkgpart_2 (pkgpart),
UNIQUE pkg_svc__pkgpart_svcpart_index (pkgpart,svcpart),
KEY pkg_svc__pkgpart_index (pkgpart)
);
#
# Table structure for table 'prepay_credit'
#
CREATE TABLE prepay_credit (
prepaynum int(11) DEFAULT '0' NOT NULL,
identifier varchar(80) DEFAULT '' NOT NULL,
amount decimal(10,2) DEFAULT '0.00' NOT NULL,
PRIMARY KEY (prepaynum),
KEY identifier (identifier)
);
#
# Table structure for table 'radacct'
#
CREATE TABLE radacct (
User_Name char(32),
Start int(16),
Stop int(16),
NAS char(20) DEFAULT '' NOT NULL,
Session_Id char(32),
framed_addr char(16) DEFAULT '0.0.0.0' NOT NULL
);
#
# Table structure for table 'radius_acct_attr'
#
CREATE TABLE radius_acct_attr (
attr char(32) DEFAULT '' NOT NULL,
field char(32) DEFAULT '' NOT NULL,
PRIMARY KEY (attr,field)
);
#
# Table structure for table 'radius_attr_template'
#
CREATE TABLE radius_attr_template (
template_name varchar(32) DEFAULT '' NOT NULL,
radius_attr text,
comment text,
PRIMARY KEY (template_name)
);
#
# Table structure for table 'svc_acct'
#
CREATE TABLE svc_acct (
username varchar(16) DEFAULT '' NOT NULL,
uid int(11),
passwd_opt varchar(100),
expire_date date DEFAULT '2037-01-01',
quota varchar(80),
start_date date DEFAULT '1981-08-17',
slipip varchar(15),
_password varchar(25) DEFAULT '' NOT NULL,
gid int(11),
attr_template varchar(32),
shell varchar(80),
finger varchar(80),
svcnum int(11) DEFAULT '0' NOT NULL,
active int(1) DEFAULT '1',
dir varchar(80),
popnum int(11),
radius_attr text NOT NULL,
pgpkeyid varchar(80),
PRIMARY KEY (svcnum),
KEY username (username),
KEY svc_acct__username_index (username)
);
#
# Table structure for table 'svc_acct_pop'
#
CREATE TABLE svc_acct_pop (
exch char(3) DEFAULT '' NOT NULL,
state varchar(80) DEFAULT '' NOT NULL,
city varchar(80) DEFAULT '' NOT NULL,
ac char(3) DEFAULT '' NOT NULL,
popnum int(11) DEFAULT '0' NOT NULL,
loc varchar(4),
PRIMARY KEY (popnum)
);
#
# Table structure for table 'svc_acct_sm'
#
CREATE TABLE svc_acct_sm (
maildest varchar(80),
domsvc int(11) DEFAULT '0' NOT NULL,
svcnum int(11) DEFAULT '0' NOT NULL,
domuser varchar(80) DEFAULT '' NOT NULL,
PRIMARY KEY (svcnum),
UNIQUE domsvc (domsvc,domuser)
);
#
# Table structure for table 'svc_domain'
#
CREATE TABLE svc_domain (
domain varchar(80) DEFAULT '' NOT NULL,
svcnum int(11) DEFAULT '0' NOT NULL,
PRIMARY KEY (svcnum),
UNIQUE domain (domain),
UNIQUE svc_domain__domain_index (domain)
);
#
# Table structure for table 'svc_wireless'
#
CREATE TABLE svc_wireless (
svcnum int(11) DEFAULT '0' NOT NULL,
cellnum int(11),
speed int(8),
antenna varchar(32),
radio varchar(32),
router varchar(32),
installer int(11),
address varchar(32),
city varchar(32),
state char(2),
latitude varchar(16),
longitude varchar(16),
install_date int(11),
active int(1) DEFAULT '0' NOT NULL,
PRIMARY KEY (svcnum)
);
#
# Table structure for table 'svc_wireless_cell'
#
CREATE TABLE svc_wireless_cell (
cellnum int(11) DEFAULT '0' NOT NULL,
address varchar(32),
city varchar(32),
state char(2),
latitude varchar(16),
longitude varchar(16),
location varchar(32),
parent int(11) DEFAULT '0' NOT NULL,
channel int(2) DEFAULT '0' NOT NULL,
polarization char(1) DEFAULT 'V' NOT NULL,
PRIMARY KEY (cellnum)
);
#
# Table structure for table 'svc_www'
#
CREATE TABLE svc_www (
svcnum int(11) DEFAULT '0' NOT NULL,
recnum int(11) DEFAULT '0' NOT NULL,
usersvc int(11) DEFAULT '0' NOT NULL,
PRIMARY KEY (svcnum)
);
#
# Table structure for table 'type_pkgs'
#
CREATE TABLE type_pkgs (
pkgpart int(11) DEFAULT '0' NOT NULL,
typenum int(11) DEFAULT '0' NOT NULL,
UNIQUE typenum (typenum,pkgpart),
KEY typenum_2 (typenum),
UNIQUE type_pkgs__typenum_pkgpart_index (typenum,pkgpart),
KEY type_pkgs__typenum_index (typenum)
);
#
# Table structure for table 'wireless_mac'
#
CREATE TABLE wireless_mac (
svcnum int(11) DEFAULT '0' NOT NULL,
ip char(15) DEFAULT '' NOT NULL,
mac char(17) DEFAULT '00:00:00:00:00:00',
PRIMARY KEY (ip)
);
-------------- next part --------------
#!/usr/bin/perl
use FS::UID qw(adminsuidsetup);
use FS::Record qw(qsearch qsearchs);
use FS::cust_credit;
use FS::cust_bill;
use FS::cust_pay;
my $user = shift or die;
#my $custnum = shift or die;
adminsuidsetup($user);
foreach $cust_credit (qsearch( 'cust_credit', { } ) ) {
$credit = $cust_credit->credited;
foreach $cust_bill (qsearch( 'cust_bill', { 'custnum' => $cust_credit->custnum } ) ) {
if($cust_bill->owed > 0 and $credit > 0) {
$paid = sprintf( "%.2f", ($credit > $cust_bill->owed) ? $cust_bill->owed : $credit );
$cust_pay = new FS::cust_pay { 'invnum' => $cust_bill->invnum,
'paid' => $paid,
'_date' => $cust_credit->_date,
'payby' => 'BILL',
'payinfo' => $cust_credit->reason,
'credit' => 1 };
$error = $cust_pay->insert; warn $error if $error;
$credit -= $cust_pay->paid;
print "Fixed customer: " . $cust_bill->custnum . "\n";
}
}
$cust_credit->delete;
}
-------------- next part --------------
#!/usr/bin/perl -Tw
use Getopt::Std;
use FS::UID qw(adminsuidsetup);
use FS::Record qw(qsearch qsearchs);
use FS::cust_main;
use FS::cust_bill;
use FS::agent;
use FS::cust_bill_pkg;
use FS::cust_pkg;
use FS::agent_type;
use FS::agent_type_pkg;
my $user = shift or die &usage;
adminsuidsetup $user;
foreach $cust_bill (qsearch( 'cust_bill', { 'owed' => 0, com_paid => '' } ) ) {
$cust_main = qsearchs( 'cust_main', { 'custnum' => $cust_bill->custnum } );
$agent = qsearchs( 'agent', { 'agentnum' => $cust_main->agentnum } );
foreach $cust_bill_pkg (qsearch( 'cust_bill_pkg', { 'invnum' => $cust_bill->invnum } ) ) {
unless($cust_bill_pkg->pkgnum) { next };
$cust_pkg = qsearchs( 'cust_pkg', { 'pkgnum' => $cust_bill_pkg->pkgnum } ) or next;
if($cust_pkg->getfield('setup') < (time - 86400*365) ) { next; }
$agent_temp = $agent;
for ($tier = 0; $agent_temp->agentnum != -1; $tier++) {
$agent_type = qsearchs( 'agent_type', { 'typenum' => $agent_temp->typenum } ) or die "Invalid typenum in agent ".$agent_temp->agentnum;
if( $agent_type_pkg = qsearchs( 'agent_type_pkg', { 'typenum' => $agent_type->typenum,
'pkgpart' => $cust_pkg->pkgpart,
'tier' => $tier } ) ) {
$commission{join "\t", $agent_temp->agent, $cust_main->last, $cust_main->first} +=
$agent_type_pkg->commission * $cust_bill->charged;
}
$agent_temp = qsearchs( 'agent', { 'agentnum' => $agent_temp->parent } ) or die "Dangling agent.parent in agent ".$agent_temp->agentnum;
}
}
%hash = $cust_bill->hash;
$hash{'com_paid'} = time;
$new_cust_bill = new FS::cust_bill ( \%hash );
$new_cust_bill->replace($cust_bill);
}
foreach (keys %commission) {
# $agent = qsearchs( 'agent', { 'agentnum' => $_ } ) or die "Agent $_ was abducted by the Men in Black";
# print $agent->agent, "\t", $commission{$_}, "\n";
print $_, "\t", $commission{$_}, "\n";
}
sub usage {
die "Usage:\n\n commission [ -u ] user\n";
}
-------------- next part --------------
A non-text attachment was scrubbed...
Name: fs-1.2.3-20001012-cvs-pci.diff.gz
Type: application/octet-stream
Size: 25813 bytes
Desc:
Url : http://420.am/pipermail/freeside-users/attachments/20001013/7cb13e0b/fs-1.2.3-20001012-cvs-pci.diff.obj
More information about the freeside-users
mailing list