From 600a0939e7e7e589dae4f4f5bfef3650728940b7 Mon Sep 17 00:00:00 2001 From: ivan Date: Wed, 8 Mar 2006 10:05:01 +0000 Subject: [PATCH] Add a new table for inventory with for DIDs/serials/etc., and an additional new table for inventory category (i.e. to distinguish DIDs, serials, MACs, etc.) --- FS/FS/Schema.pm | 41 ++++++- FS/FS/inventory_class.pm | 164 ++++++++++++++++++++++++++ FS/FS/inventory_item.pm | 126 ++++++++++++++++++++ FS/MANIFEST | 5 + FS/t/inventory_class.t | 5 + FS/t/inventory_item.t | 5 + bin/generate-table-module | 1 + htetc/handler.pl | 10 +- httemplate/edit/elements/edit.html | 118 ++++++++++++++++++ httemplate/edit/inventory_class.html | 9 ++ httemplate/edit/process/elements/process.html | 46 ++++++++ httemplate/edit/process/inventory_class.html | 4 + httemplate/search/elements/search.html | 80 ++++++++++++- httemplate/search/inventory_class.html | 58 +++++++++ httemplate/search/inventory_item.html | 35 ++++++ 15 files changed, 700 insertions(+), 7 deletions(-) create mode 100644 FS/FS/inventory_class.pm create mode 100644 FS/FS/inventory_item.pm create mode 100644 FS/t/inventory_class.t create mode 100644 FS/t/inventory_item.t create mode 100644 httemplate/edit/elements/edit.html create mode 100644 httemplate/edit/inventory_class.html create mode 100644 httemplate/edit/process/elements/process.html create mode 100644 httemplate/edit/process/inventory_class.html create mode 100644 httemplate/search/inventory_class.html create mode 100644 httemplate/search/inventory_item.html diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index 3ca599b49..a049b8bad 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -443,7 +443,7 @@ sub tables_hashref { #'index' => [ ['last'], ['company'] ], 'index' => [ ['last'], [ 'company' ], [ 'referral_custnum' ], [ 'daytime' ], [ 'night' ], [ 'fax' ], [ 'refnum' ], - [ 'county' ], [ 'state' ], [ 'country' ] + [ 'county' ], [ 'state' ], [ 'country' ], [ 'zip' ], ], }, @@ -1352,6 +1352,10 @@ sub tables_hashref { 'upstream_price', 'decimal', 'NULL', '10,2', '', '', 'upstream_rateplanid', 'int', 'NULL', '', '', '', #? + # how it was rated internally... + 'ratedetailnum', 'int', 'NULL', '', '', '', + 'rated_price', 'decimal', 'NULL', '10,2', '', '', + 'distance', 'decimal', 'NULL', '', '', '', 'islocal', 'int', 'NULL', '', '', '', # '', '', 0, '' instead? @@ -1373,7 +1377,7 @@ sub tables_hashref { # a svcnum... right..? 'svcnum', 'int', 'NULL', '', '', '', - #NULL, done, skipped, pushed_downstream (or something) + #NULL, done (or something) 'freesidestatus', 'varchar', 'NULL', 32, '', '', ], @@ -1412,6 +1416,39 @@ sub tables_hashref { 'index' => [], }, + #map upstream rateid (XXX or rateplanid?) to ours... + 'cdr_upstream_rate' => { # XXX or 'cdr_upstream_rateplan' ?? + 'columns' => [ + # XXX or 'upstream_rateplanid' ?? + 'upstream_rateid', 'int', 'NULL', '', '', '', + 'ratedetailnum', 'int', 'NULL', '', '', '', + ], + 'primary_key' => '', #XXX need a primary key + 'unique' => [ [ 'upstream_rateid' ] ], #unless we add another field, yeah + 'index' => [], + }, + + 'inventory_item' => { + 'columns' => [ + 'itemnum', 'serial', '', '', '', '', + 'classnum', 'int', '', '', '', '', + 'item', 'varchar', '', $char_d, '', '', + 'svcnum', 'int', 'NULL', '', '', '', + ], + 'primary_key' => 'itemnum', + 'unique' => [ [ 'classnum', 'item' ] ], + 'index' => [ [ 'classnum' ], [ 'svcnum' ] ], + }, + + 'inventory_class' => { + 'columns' => [ + 'classnum', 'serial', '', '', '', '', + 'classname', 'varchar', $char_d, '', '', '', + ], + 'primary_key' => 'classnum', + 'unique' => [], + 'index' => [], + }, }; diff --git a/FS/FS/inventory_class.pm b/FS/FS/inventory_class.pm new file mode 100644 index 000000000..04ee207d3 --- /dev/null +++ b/FS/FS/inventory_class.pm @@ -0,0 +1,164 @@ +package FS::inventory_class; + +use strict; +use vars qw( @ISA ); +use FS::Record qw( dbh qsearch qsearchs ); + +@ISA = qw(FS::Record); + +=head1 NAME + +FS::inventory_class - Object methods for inventory_class records + +=head1 SYNOPSIS + + use FS::inventory_class; + + $record = new FS::inventory_class \%hash; + $record = new FS::inventory_class { 'column' => 'value' }; + + $error = $record->insert; + + $error = $new_record->replace($old_record); + + $error = $record->delete; + + $error = $record->check; + +=head1 DESCRIPTION + +An FS::inventory_class object represents a class of inventory, such as "DID +numbers" or "physical equipment serials". FS::inventory_class inherits from +FS::Record. The following fields are currently supported: + +=over 4 + +=item classnum - primary key + +=item classname - Name of this class + + +=back + +=head1 METHODS + +=over 4 + +=item new HASHREF + +Creates a new inventory class. To add the class to the database, see +L<"insert">. + +Note that this stores the hash reference, not a distinct copy of the hash it +points to. You can ask the object for a copy with the I method. + +=cut + +# the new method can be inherited from FS::Record, if a table method is defined + +sub table { 'inventory_class'; } + +=item insert + +Adds this record to the database. If there is an error, returns the error, +otherwise returns false. + +=cut + +# the insert method can be inherited from FS::Record + +=item delete + +Delete this record from the database. + +=cut + +# the delete method can be inherited from FS::Record + +=item replace OLD_RECORD + +Replaces the OLD_RECORD with this one in the database. If there is an error, +returns the error, otherwise returns false. + +=cut + +# the replace method can be inherited from FS::Record + +=item check + +Checks all fields to make sure this is a valid inventory class. If there is +an error, returns the error, otherwise returns false. Called by the insert +and replace methods. + +=cut + +# the check method should currently be supplied - FS::Record contains some +# data checking routines + +sub check { + my $self = shift; + + my $error = + $self->ut_numbern('classnum') + || $self->ut_textn('classname') + ; + return $error if $error; + + $self->SUPER::check; +} + +=item num_avail + +Returns the number of available (unused/unallocated) inventory items of this +class (see L). + +=cut + +sub num_avail { + shift->num_sql('( svcnum IS NULL OR svcnum = 0 )'); +} + +sub num_sql { + my( $self, $sql ) = @_; + my $sql = "AND $sql" if length($sql); + my $statement = + "SELECT COUNT(*) FROM inventory_item WHERE classnum = ? $sql"; + my $sth = dbh->prepare($statement) or die dbh->errstr. " preparing $statement"; + $sth->execute($self->classnum) or die $sth->errstr. " executing $statement"; + $sth->fetchrow_arrayref->[0]; +} + +=item num_used + +Returns the number of used (allocated) inventory items of this class (see +L). + +=cut + +sub num_used { + shift->num_sql("svcnum IS NOT NULL AND svcnum > 0 "); +} + +=item num_total + +Returns the total number of inventory items of this class (see +L). + +=cut + +sub num_total { + shift->num_sql(''); +} + +=back + +=head1 BUGS + +=head1 SEE ALSO + +L, L, schema.html from the base documentation. + +=cut + +1; + diff --git a/FS/FS/inventory_item.pm b/FS/FS/inventory_item.pm new file mode 100644 index 000000000..5312d95f1 --- /dev/null +++ b/FS/FS/inventory_item.pm @@ -0,0 +1,126 @@ +package FS::inventory_item; + +use strict; +use vars qw( @ISA ); +use FS::Record qw( qsearch qsearchs ); +use FS::inventory_class; + +@ISA = qw(FS::Record); + +=head1 NAME + +FS::inventory_item - Object methods for inventory_item records + +=head1 SYNOPSIS + + use FS::inventory_item; + + $record = new FS::inventory_item \%hash; + $record = new FS::inventory_item { 'column' => 'value' }; + + $error = $record->insert; + + $error = $new_record->replace($old_record); + + $error = $record->delete; + + $error = $record->check; + +=head1 DESCRIPTION + +An FS::inventory_item object represents a specific piece of (real or virtual) +inventory, such as a specific DID or serial number. FS::inventory_item +inherits from FS::Record. The following fields are currently supported: + +=over 4 + +=item itemnum - primary key + +=item classnum - Inventory class (see L) + +=item item - Item identifier (unique within its inventory class) + +=item svcnum - Customer servcie (see L) + +=back + +=head1 METHODS + +=over 4 + +=item new HASHREF + +Creates a new item. To add the item to the database, see L<"insert">. + +Note that this stores the hash reference, not a distinct copy of the hash it +points to. You can ask the object for a copy with the I method. + +=cut + +# the new method can be inherited from FS::Record, if a table method is defined + +sub table { 'inventory_item'; } + +=item insert + +Adds this record to the database. If there is an error, returns the error, +otherwise returns false. + +=cut + +# the insert method can be inherited from FS::Record + +=item delete + +Delete this record from the database. + +=cut + +# the delete method can be inherited from FS::Record + +=item replace OLD_RECORD + +Replaces the OLD_RECORD with this one in the database. If there is an error, +returns the error, otherwise returns false. + +=cut + +# the replace method can be inherited from FS::Record + +=item check + +Checks all fields to make sure this is a valid item. If there is +an error, returns the error, otherwise returns false. Called by the insert +and replace methods. + +=cut + +# the check method should currently be supplied - FS::Record contains some +# data checking routines + +sub check { + my $self = shift; + + my $error = + $self->ut_numbern('itemnum') + || $self->ut_foreign_key('classnum', 'inventory_class', 'classnum' ) + || $self->ut_text('item') + || $self->ut_numbern('svcnum') + ; + return $error if $error; + + $self->SUPER::check; +} + +=back + +=head1 BUGS + +=head1 SEE ALSO + +L, schema.html from the base documentation. + +=cut + +1; + diff --git a/FS/MANIFEST b/FS/MANIFEST index f6f833589..6360d5303 100644 --- a/FS/MANIFEST +++ b/FS/MANIFEST @@ -262,6 +262,7 @@ t/part_pkg-sql_external.t t/part_pkg-sql_generic.t t/part_pkg-sqlradacct_hour.t t/part_pkg-subscription.t +t/part_pkg-voip_sqlradacct.t t/part_pkg-voip_cdr.t t/part_pop_local.t t/part_referral.t @@ -317,3 +318,7 @@ FS/cdr_type.pm t/cdr_type.t FS/cdr_carrier.pm t/cdr_carrier.t +FS/inventory_class.pm +t/inventory_class.t +FS/inventory_item.pm +t/inventory_item.t diff --git a/FS/t/inventory_class.t b/FS/t/inventory_class.t new file mode 100644 index 000000000..80b2fa210 --- /dev/null +++ b/FS/t/inventory_class.t @@ -0,0 +1,5 @@ +BEGIN { $| = 1; print "1..1\n" } +END {print "not ok 1\n" unless $loaded;} +use FS::inventory_class; +$loaded=1; +print "ok 1\n"; diff --git a/FS/t/inventory_item.t b/FS/t/inventory_item.t new file mode 100644 index 000000000..8ce9d677c --- /dev/null +++ b/FS/t/inventory_item.t @@ -0,0 +1,5 @@ +BEGIN { $| = 1; print "1..1\n" } +END {print "not ok 1\n" unless $loaded;} +use FS::inventory_item; +$loaded=1; +print "ok 1\n"; diff --git a/bin/generate-table-module b/bin/generate-table-module index fcc3f1d1f..b3204fa06 100755 --- a/bin/generate-table-module +++ b/bin/generate-table-module @@ -13,6 +13,7 @@ my %ut = ( #just guesses 'number' => 'float', 'varchar' => 'text', 'text' => 'text', + 'serial' => 'number', ); my $dbdef_table = dbdef_dist->table($table) diff --git a/htetc/handler.pl b/htetc/handler.pl index 1bbea16d1..15f9203f8 100644 --- a/htetc/handler.pl +++ b/htetc/handler.pl @@ -173,6 +173,8 @@ sub handler use FS::XMLRPC; use FS::payby; use FS::cdr; + use FS::inventory_class; + use FS::inventory_item; if ( %%%RT_ENABLED%%% ) { eval ' @@ -245,8 +247,10 @@ sub handler sub redirect { my( $location ) = @_; + warn 'redir1 $m='.$m; use vars qw($m); $m->clear_buffer; + warn 'redir3-prof'; #false laziness w/above if ( defined(@DBIx::Profile::ISA) ) { #profiling redirect @@ -263,10 +267,14 @@ sub handler ); dbh->{'private_profile'} = {}; - $m->abort(200); + warn 'redir9-prof'; + my $rv = $m->abort(200); + warn "redir10-prof: $rv"; + $rv; } else { #normal redirect + warn 'redir9-redirect'; $m->redirect($location); } diff --git a/httemplate/edit/elements/edit.html b/httemplate/edit/elements/edit.html new file mode 100644 index 000000000..ce6e2dbb1 --- /dev/null +++ b/httemplate/edit/elements/edit.html @@ -0,0 +1,118 @@ +<% + + # options example... + # + # 'name' => + # 'table' => + # #? 'primary_key' => #required when the dbdef doesn't know...??? + # 'labels' => { + # 'column' => 'Label', + # } + # + # listref - each item is a literal column name (or method) or (notyet) coderef + # if not specified all columns (except for the primary key) will be editable + # 'fields' => [ + # ] + # + # 'menubar' => '', #menubar arrayref + + my(%opt) = @_; + + #false laziness w/process.html + my $table = $opt{'table'}; + my $class = "FS::$table"; + my $pkey = dbdef->table($table)->primary_key; #? $opt{'primary_key'} || + my $fields = $opt{'fields'} + #|| [ grep { $_ ne $pkey } dbdef->table($table)->columns ]; + || [ grep { $_ ne $pkey } fields($table) ]; + + my $object; + if ( $cgi->param('error') ) { + + $object = $class->new( { + map { $_ => scalar($cgi->param($_)) } fields($table) + }); + + } elsif ( $cgi->keywords ) { #editing + + my( $query ) = $cgi->keywords; + $query =~ /^(\d+)$/; + $object = qsearchs( $table, { $pkey => $1 } ); + + } else { #adding + + $object = $class->new( {} ); + + } + + my $action = $object->$pkey() ? 'Edit' : 'Add'; + + my $title = "$action $opt{'name'}"; + + my @menubar = (); + if ( $opt{'menubar'} ) { + @menubar = @{ $opt{'menubar'} }; + } else { + @menubar = ( + 'Main menu' => $p, #eventually get rid of this when the ACL/UI update is done + "View all $opt{'name'}s" => "${p}search/$table.html", #eventually use Lingua::bs to pluralize + ); + } + +%> + + +<%= include("/elements/header.html", $title, + include( '/elements/menubar.html', @menubar ) + ) +%> + +<% if ( $cgi->param('error') ) { %> + Error: <%= $cgi->param('error') %> +

+<% } %> + +
+ +<%= ( $opt{labels} && exists $opt{labels}->{$pkey} ) + ? $opt{labels}->{$pkey} + : $pkey +%> +#<%= $object->$pkey() || "(NEW)" %> + +<%= ntable("#cccccc",2) %> + +<% foreach my $field ( @$fields ) { %> + + + + + <%= ( $opt{labels} && exists $opt{labels}->{$field} ) + ? $opt{labels}->{$field} + : $field + %> + + + <% + #just text in one size for now... eventually more options for + # uneditable, hidden, + + + + +<% } %> + + + +
+ +"> + +
+ +<%= include("/elements/footer.html") %> + diff --git a/httemplate/edit/inventory_class.html b/httemplate/edit/inventory_class.html new file mode 100644 index 000000000..5dde2e595 --- /dev/null +++ b/httemplate/edit/inventory_class.html @@ -0,0 +1,9 @@ +<%= include( 'elements/edit.html', + 'name' => 'Inventory Class', + 'table' => 'inventory_class', + 'labels' => { + 'classnum' => 'Class number', + 'classname' => 'Class name', + }, + ) +%> diff --git a/httemplate/edit/process/elements/process.html b/httemplate/edit/process/elements/process.html new file mode 100644 index 000000000..52c876720 --- /dev/null +++ b/httemplate/edit/process/elements/process.html @@ -0,0 +1,46 @@ +<% + + # options example... + # + # 'table' => + # #? 'primary_key' => #required when the dbdef doesn't know...??? + # #? 'fields' => [] + + my(%opt) = @_; + + #false laziness w/edit.html + my $table = $opt{'table'}; + my $class = "FS::$table"; + my $pkey = dbdef->table($table)->primary_key; #? $opt{'primary_key'} || + my $fields = $opt{'fields'} + #|| [ grep { $_ ne $pkey } dbdef->table($table)->columns ]; + || [ fields($table) ]; + + my $pkeyvalue = $cgi->param($pkey); + + my $old = qsearchs( $table, { $pkey => $pkeyvalue } ) if $pkeyvalue; + + my $new = $class->new( { + map { + $_, scalar($cgi->param($_)); + } @$fields + } ); + + my $error; + if ( $pkeyvalue ) { + $error = $new->replace($old); + } else { + warn $new; + $error = $new->insert; + warn $error; + $pkeyvalue = $new->getfield($pkey); + } + + if ( $error ) { + $cgi->param('error', $error); + print $cgi->redirect(popurl(2). "$table.html?". $cgi->query_string ); + } else { + print $cgi->redirect(popurl(3). "search/$table.html"); + } + +%> diff --git a/httemplate/edit/process/inventory_class.html b/httemplate/edit/process/inventory_class.html new file mode 100644 index 000000000..e30e74e7b --- /dev/null +++ b/httemplate/edit/process/inventory_class.html @@ -0,0 +1,4 @@ +<%= include( 'elements/process.html', + 'table' => 'inventory_class', + ) +%> diff --git a/httemplate/search/elements/search.html b/httemplate/search/elements/search.html index d19fb4acd..b14bded10 100644 --- a/httemplate/search/elements/search.html +++ b/httemplate/search/elements/search.html @@ -1,5 +1,73 @@ <% + # options example... + # (everything not commented required is optional) + # + # # basic options, required + # 'title' => 'Page title', + # 'name' => 'items', #name for the records returned + # + # # some HTML callbacks... + # 'menubar' => '', #menubar arrayref + # 'html_init' => '', #after the header/menubar and before the pager + # + # #literal SQL query string or qsearch hashref, required + # 'query' => { + # 'table' => 'tablename', + # #everything else is optional... + # 'hashref' => { 'field' => 'value', + # 'field' => { 'op' => '<', + # 'value' => '54', + # }, + # }, + # 'select' => '*', + # 'addl_from' => '', #'LEFT JOIN othertable USING ( key )', + # 'extra_sql' => '', #'AND otherstuff', #'WHERE onlystuff', + # + # + # }, + # # "select * from tablename"; + # + # #required unless 'query' is an SQL query string (shouldn't be...) + # 'count_query' => 'SELECT COUNT(*) FROM tablename', + # + # 'count_addl' => [], #additional count fields listref of sprintf strings + # # [ $money_char.'%.2f total paid', ], + # + # #listref of column labels, + # #required unless 'query' is an SQL query string + # # (if not specified the database column names will be used) + # 'header' => [ '#', 'Item' ], + # + # #listref - each item is a literal column name (or method) or coderef + # #if not specified all columns will be shown + # 'fields' => [ + # 'column', + # sub { my $row = shift; $row->column; }, + # ], + # + # #listref of column footers + # 'footer' => [], + # + # #listref - each item is the empty string, or a listref of ... + # 'links' => + # + # + # 'align' => 'lrc.', #one letter for each column, left/right/center/none + # # can also pass a listref with full values: + # # [ 'left', 'right', 'center', '' ] + # + # #listrefs... + # #currently only HTML, maybe eventually Excel too + # 'color' => [], + # 'size' => [], + # 'style' => [], + # + # #redirect if there's only one item... + # # listref of URL base and column name (or method) + # # or a coderef that returns the same + # 'redirect' => + my(%opt) = @_; #warn join(' / ', map { "$_ => $opt{$_}" } keys %opt ). "\n"; @@ -189,7 +257,9 @@ redirect( $url. $rows->[0]->$method() ); } else { ( my $xlsname = $opt{'name'} ) =~ s/\W//g; - $opt{'name'} =~ s/s$// if $total == 1; + #$opt{'name'} =~ s/s$// if $total == 1; + $opt{'name'} =~ s/((s)e)?s$/$2/ if $total == 1; #should use Lingua::bs + # to "depluralize" my @menubar = (); if ( $opt{'menubar'} ) { @@ -197,6 +267,8 @@ } else { @menubar = ( 'Main menu' => $p ); } + + %> <%= include( '/elements/header.html', $opt{'title'}, include( '/elements/menubar.html', @menubar ) @@ -239,7 +311,8 @@ <%= include('/elements/table-grid.html') %> - <% foreach my $header ( @$header ) { %> + <% + foreach my $header ( @$header ) { %> <%= $header %> <% } %> @@ -386,7 +459,6 @@ <% } %> - - + <%= include( '/elements/footer.html' ) %> <% } %> <% } %> diff --git a/httemplate/search/inventory_class.html b/httemplate/search/inventory_class.html new file mode 100644 index 000000000..1bf1bcbce --- /dev/null +++ b/httemplate/search/inventory_class.html @@ -0,0 +1,58 @@ +<% + +tie my %labels, 'Tie::IxHash', + 'num_avail' => 'Available', # (upload batch)', + 'num_used' => 'In use', #'Used', #'Allocated', + 'num_total' => 'Total', +; +my %inv_action_link = ( + 'num_avail' => 'eventually' +); +my %inv_action_label = ( + 'num_avail' => 'upload_batch' +); + +my $link = [ "${p}edit/inventory_class.html?", 'classnum' ]; + +%><%= include( 'elements/search.html', + 'title' => 'Inventory Classes', + 'name' => 'inventory classes', + 'menubar' => [ 'Add a new inventory class' => + $p.'edit/inventory_class.html', + ], + 'query' => { 'table' => 'inventory_class', }, + 'count_query' => 'SELECT COUNT(*) FROM inventory_class', + 'header' => [ '#', 'Inventory class', 'Inventory' ], + 'fields' => [ 'classnum', + 'classname', + sub { + #my $inventory_class = shift; + my $i_c = shift; + + [ map { + [ + { + 'data' => ''. $i_c->$_(). '', + 'align' => 'right', + }, + { + 'data' => $labels{$_}, + 'align' => 'left', + }, + { 'data' => ( exists($inv_action_link{$_}) + ? '('. $inv_action_label{$_}. ')' + : '' + ), + 'align' => 'left', + }, + ] + } keys %labels + ]; + }, + ], + 'links' => [ $link, + $link, + '', + ], + ) +%> diff --git a/httemplate/search/inventory_item.html b/httemplate/search/inventory_item.html new file mode 100644 index 000000000..ff7f1fadf --- /dev/null +++ b/httemplate/search/inventory_item.html @@ -0,0 +1,35 @@ +<% + +my $classnum = $cgi->param('classnum'); +$classnum =~ /^(\d+)$/ or eidiot "illegal agentnum $agentnum"; +$classnum = $1; +my $inventory_class = qsearchs('inventory_class', { 'classnum' => $classnum } ); + +my $count_query = + "SELECT COUNT(*) FROM inventory_class WHERE classnum = $classnum"; + +%><%= include( 'elements/search.html', + 'title' => $inventory_class->classname. ' Inventory', + + #less lame to use Lingua:: something to pluralize + 'name' => $inventory_class->classname. 's', + + 'query' => { + 'table' => 'inventory_item', + 'hashref' => { 'classnum' => $classnum }, + }, + + 'count_query' => $count_query, + + # XXX proper full service/customer link ala svc_acct + 'header' => [ '#', $inventory_class->classname, 'svcnum' ], + + 'fields' => [ + 'itemnum', + 'item', + 'svcnum', #XXX proper full service customer link ala svc_acct + # "unallocated" ? "available" ? + ], + + ) +%> -- 2.11.0