3 use vars qw(@ISA @EXPORT_OK $DEBUG $setup_hack %dbdef_cache);
4 use subs qw(reload_dbdef);
6 use DBIx::DBSchema 0.44; #for foreign keys with MATCH / ON DELETE/UPDATE
7 use DBIx::DBSchema::Table;
8 use DBIx::DBSchema::Column;
9 use DBIx::DBSchema::Index;
10 use DBIx::DBSchema::ForeignKey;
11 #can't use this yet, dependency bs #use FS::Conf;
14 @EXPORT_OK = qw( dbdef dbdef_dist reload_dbdef );
21 FS::Schema - Freeside database schema
25 use FS::Schema qw(dbdef dbdef_dist reload_dbdef);
27 $dbdef = reload_dbdef;
28 $dbdef = reload_dbdef "/non/standard/filename";
30 $dbdef_dist = dbdef_dist;
34 This class represents the database schema.
40 =item reload_dbdef([FILENAME])
42 Load a database definition (see L<DBIx::DBSchema>), optionally from a
43 non-default filename. This command is executed at startup unless
44 I<$FS::Schema::setup_hack> is true. Returns a DBIx::DBSchema object.
51 unless ( exists $dbdef_cache{$file} ) {
52 warn "[debug]$me loading dbdef for $file\n" if $DEBUG;
53 $dbdef_cache{$file} = DBIx::DBSchema->load( $file )
54 or die "can't load database schema from $file: $DBIx::DBSchema::errstr\n";
56 warn "[debug]$me re-using cached dbdef for $file\n" if $DEBUG;
58 $dbdef = $dbdef_cache{$file};
59 FS::Record::fk_methods_init();
64 Returns the current database definition (represents the current database,
65 assuming it is up-to-date). See L<DBIx::DBSchema>.
71 =item dbdef_dist [ DATASRC ]
73 Returns the current canoical database definition as defined in this file.
75 Optionally, pass a DBI data source to enable syntax specific to that database.
76 Currently, this enables "ENGINE=InnoDB" for MySQL databases.
81 my $datasrc = @_ && !ref($_[0]) ? shift : '';
82 my $opt = @_ ? shift : {};
84 my $local_options = '';
85 if ( $datasrc =~ /^dbi:mysql/i ) {
86 $local_options = 'ENGINE=InnoDB';
90 # create a dbdef object from the old data structure
93 my $tables_hashref = tables_hashref();
96 my $dbdef = new DBIx::DBSchema map {
102 while (@{$tables_hashref->{$tablename}{'columns'}}) {
103 #my($name, $type, $null, $length, $default, $local) =
105 splice @{$tables_hashref->{$tablename}{'columns'}}, 0, 6;
106 my %hash = map { $_ => shift @coldef }
107 qw( name type null length default local );
109 #can be removed once we depend on DBIx::DBSchema 0.39;
110 $hash{'type'} = 'LONGTEXT'
111 if $hash{'type'} =~ /^TEXT$/i && $datasrc =~ /^dbi:mysql/i;
113 unless ( defined $hash{'default'} ) {
114 warn "$tablename:\n".
115 join('', map "$_ => $hash{$_}\n", keys %hash) ;# $stop = <STDIN>;
118 push @columns, new DBIx::DBSchema::Column ( \%hash );
121 #false laziness w/sub indices in DBIx::DBSchema::DBD (well, sorta)
122 #and sub sql_create_table in DBIx::DBSchema::Table (slighty more?)
123 my $unique = $tables_hashref->{$tablename}{'unique'};
124 warn "missing index for $tablename\n" unless defined $tables_hashref->{$tablename}{'index'};
125 my @index = @{ $tables_hashref->{$tablename}{'index'} };
127 # kludge to avoid avoid "BLOB/TEXT column 'statustext' used in key
128 # specification without a key length".
129 # better solution: teach DBIx::DBSchema to specify a default length for
130 # MySQL indices on text columns, or just to support an index length at all
131 # so we can pass something in.
132 # best solution: eliminate need for this index in cust_main::retry_realtime
133 @index = grep { @{$_}[0] ne 'statustext' } @index
134 if $datasrc =~ /^dbi:mysql/i;
138 DBIx::DBSchema::Index->new({
139 'name' => $tablename. $indexnum++,
146 DBIx::DBSchema::Index->new({
147 'name' => $tablename. $indexnum++,
155 map DBIx::DBSchema::ForeignKey->new($_),
156 @{ $tables_hashref->{$tablename}{'foreign_keys'} || [] };
158 DBIx::DBSchema::Table->new({
160 primary_key => $tables_hashref->{$tablename}{'primary_key'},
161 columns => \@columns,
162 indices => \@indices,
163 foreign_keys => \@foreign_keys,
164 local_options => $local_options,
167 } keys %$tables_hashref;
170 warn "[debug]$me initial dbdef_dist created ($dbdef) with tables:\n";
171 warn "[debug]$me $_\n" foreach $dbdef->tables;
174 #add radius attributes to svc_acct
176 #my($svc_acct)=$dbdef->table('svc_acct');
179 #foreach $attribute (@attributes) {
180 # $svc_acct->addcolumn ( new DBIx::DBSchema::Column (
181 # 'radius_'. $attribute,
188 #foreach $attribute (@check_attributes) {
189 # $svc_acct->addcolumn( new DBIx::DBSchema::Column (
197 my $tables_hashref_torrus = tables_hashref_torrus();
199 #create history tables
201 grep { ! /^(clientapi|access_user)_session/
203 && ! /^log(_context)?$/
204 && ! /^(legacy_cust_history|cacti_page|template_image|access_user_log)$/
205 && ( ! /^queue(_arg|_depend|_stat)?$/ || ! $opt->{'queue-no_history'} )
207 && ! $tables_hashref_torrus->{$_}
211 my $tableobj = $dbdef->table($table)
212 or die "unknown table $table";
216 unless ( $table eq 'cust_event' || $table eq 'cdr' ) { #others?
218 my %indices = $tableobj->indices;
222 DBIx::DBSchema::Index->new({
223 'name' => 'h_'. $indices{$_}->name,
225 'columns' => [ @{$indices{$_}->columns} ],
231 $h_indices{"h_${table}_srckey"} =
232 DBIx::DBSchema::Index->new({
233 'name' => "h_${table}_srckey",
235 'columns' => [ 'history_action', #right?
236 $tableobj->primary_key,
240 $h_indices{"h_${table}_srckey2"} =
241 DBIx::DBSchema::Index->new({
242 'name' => "h_${table}_srckey2",
244 'columns' => [ 'history_date',
245 $tableobj->primary_key,
249 #necessary for queries that want to look at *who* made changes
250 $h_indices{"h_${table}_usernum"} =
251 DBIx::DBSchema::Index->new({
252 'name' => "h_${table}_usernum",
254 'columns' => [ 'history_usernum'],
257 # necessary because of the evil OR username for older data, be really nice if everything was just migrated to usernum and we could drop username
258 # This will not be helpful to mysql, but postgres smartly does a bitmap across both indexes, mysql will just use one
260 $h_indices{"h_${table}_user"} =
261 DBIx::DBSchema::Index->new({
262 'name' => "h_${table}_user",
264 'columns' => [ 'history_user'],
268 my $primary_key_col = $tableobj->column($tableobj->primary_key)
269 or die "$table: primary key declared as ". $tableobj->primary_key.
270 ", but no column of that name\n";
272 my $historynum_type = ( $tableobj->column($tableobj->primary_key)->type
273 =~ /^(bigserial|bigint|int8)$/i
278 my $h_tableobj = DBIx::DBSchema::Table->new( {
279 'name' => "h_$table",
280 'primary_key' => 'historynum',
281 'indices' => \%h_indices,
282 'local_options' => $local_options,
284 DBIx::DBSchema::Column->new( {
285 'name' => 'historynum',
286 'type' => $historynum_type,
287 'null' => 'NOT NULL',
292 DBIx::DBSchema::Column->new( {
293 'name' => 'history_date',
300 DBIx::DBSchema::Column->new( {
301 'name' => 'history_user',
308 DBIx::DBSchema::Column->new( {
309 'name' => 'history_usernum',
316 DBIx::DBSchema::Column->new( {
317 'name' => 'history_action',
319 'null' => 'NOT NULL',
325 my $column = $tableobj->column($_);
327 #clone so as to not disturb the original
328 $column = DBIx::DBSchema::Column->new( {
329 map { $_ => $column->$_() }
330 qw( name type null length default local )
333 if ( $column->type =~ /^(\w*)SERIAL$/i ) {
334 $column->type(uc($1).'INT');
335 $column->null('NULL');
337 #$column->default('')
338 # if $column->default =~ /^nextval\(/i;
339 #( my $local = $column->local ) =~ s/AUTO_INCREMENT//i;
340 #$column->local($local);
345 $dbdef->addtable($h_tableobj);
348 if ( $datasrc =~ /^dbi:mysql/i ) {
350 my $dup_lock_table = DBIx::DBSchema::Table->new( {
351 'name' => 'duplicate_lock',
352 'primary_key' => 'duplocknum',
353 'local_options' => $local_options,
355 DBIx::DBSchema::Column->new( {
356 'name' => 'duplocknum',
358 'null' => 'NOT NULL',
363 DBIx::DBSchema::Column->new( {
364 'name' => 'lockname',
366 'null' => 'NOT NULL',
372 'indices' => { 'duplicate_lock1' =>
373 DBIx::DBSchema::Index->new({
374 'name' => 'duplicate_lock1',
376 'columns' => [ 'lockname' ],
381 $dbdef->addtable($dup_lock_table);
389 #torrus tables http://torrus.org/reporting_setup.pod.html#create_sql_tables
390 sub tables_hashref_torrus {
394 # Collector export table. It usually grows at several megabytes
395 # per month, and is updated every 5 minutes
398 'id', 'serial', '', '', '', '',
399 'srv_date', 'date', '', '', '', '',#date and time of the data sample
400 'srv_time', 'time', '', '', '', '',
401 'serviceid', 'varchar', '', 64, '', '',#unique service ID per counter
402 'value', 'double precision', '', '', '', '',#collected rate or gauge value
403 'intvl', 'int', '', '', '', '', # collection interval - for counter volume calculation
405 'primary_key' => 'id',
407 'index' => [ ['srv_date'], ['srv_date', 'srv_time'], ['serviceid'], ],
410 #Tables for (currently monthly only) report contents.
411 #These are updated usually once per month, and read at the moment of
412 #rendering the report output (HTML now, PDF or XML or Excel or whatever
415 #DBIx::Sequence backend, theplatform-independent inplementation
417 'dbix_sequence_state' => {
419 'id', 'serial', '', '', '', '',
420 'dataset', 'varchar', '', 50, '', '',
421 'state_id', 'int', '', '', '', '',
423 'primary_key' => 'id',
424 #CONSTRAINT pk_dbix_sequence PRIMARY KEY (dataset, state_id)
425 'unique' => [ [ 'dataset', 'state_id' ], ],
429 'dbix_sequence_release' => {
431 'id', 'serial', '', '', '', '',
432 'dataset', 'varchar', '', 50, '', '',
433 'released_id', 'int', '', '', '', '',
435 'primary_key' => 'id',
436 #CONSTRAINT pk_dbi_release PRIMARY KEY (dataset, released_id)
437 'unique' => [ [ 'dataset', 'released_id', ] ],
441 #Each report is characterized by name, date and time.
442 #Monthly reports are automatically assigned 00:00 of the 1st day
443 #in the month. The report contains fields for every service ID
444 #defined across all datasource trees.
447 'id', 'serial', '', '', '', '',
448 'rep_date', 'date', '', '', '', '',#Start date of the report
449 'rep_time', 'time', '', '', '', '',#Start time of the report
450 'reportname', 'varchar', '', 64, '', '',#Report name, such as
452 'iscomplete', 'int', '', '', '', '',#0 when the report is in
453 # progress, 1 when it is ready
455 'primary_key' => 'id',
456 'unique' => [ [ qw(rep_date rep_time reportname) ] ],
457 'index' => [ [ 'rep_date' ] ],
460 #Each report contains fields. For each service ID,
461 #the report may contain several fields for various statistics.
462 #Each field contains information about the units of the value it
466 'id', 'serial', '', '', '', '',
467 'rep_id', 'int', 'NULL', '', '', '',
468 'name', 'varchar', '', 64, '', '',#name of the field,
470 'serviceid', 'varchar', '', 64, '', '',#service ID
471 'value', 'double precision', '', '', '', '',#Numeric value
472 'units', 'varchar', '', 64, \"''", '',#Units, such as bytes
475 'primary_key', => 'id',
476 'unique' => [ [ qw(rep_id name serviceid) ] ],
486 my $char_d = 80; #default maxlength for text fields
488 #my(@date_type) = ( 'timestamp', '', '' );
489 my @date_type = ( 'int', 'NULL', '' );
490 my @perl_type = ( 'text', 'NULL', '' );
491 my @money_type = ( 'decimal', '', '10,2' );
492 my @money_typen = ( 'decimal', 'NULL', '10,2' );
493 my @taxrate_type = ( 'decimal', '', '14,8' ); # requires pg 8 for
494 my @taxrate_typen = ( 'decimal', 'NULL', '14,8' ); # fs-upgrade to work
496 my $username_len = 64; #usernamemax config file
498 # name type nullability length default local
504 'agentnum', 'serial', '', '', '', '',
505 'agent', 'varchar', '', $char_d, '', '',
506 'typenum', 'int', '', '', '', '',
507 'ticketing_queueid', 'int', 'NULL', '', '', '',
508 'invoice_template', 'varchar', 'NULL', $char_d, '', '',
509 'agent_custnum', 'int', 'NULL', '', '', '',
510 'disabled', 'char', 'NULL', 1, '', '',
511 'username', 'varchar', 'NULL', $char_d, '', '',
512 '_password', 'varchar', 'NULL', $char_d, '', '',
513 'freq', 'int', 'NULL', '', '', '', #deprecated (never used)
514 'prog', @perl_type, '', '', #deprecated (never used)
516 'primary_key' => 'agentnum',
517 #'unique' => [ [ 'agent_custnum' ] ], #one agent per customer?
518 #insert is giving it a value, tho..
519 #'index' => [ ['typenum'], ['disabled'] ],
521 'index' => [ ['typenum'], ['disabled'], ['agent_custnum'] ],
523 { columns => [ 'typenum' ],
524 table => 'agent_type',
526 # 1. RT tables aren't part of our data structure, so
527 # we can't make sure Queue is created already
528 # 2. Future ability to plug in other ticketing systems
529 #{ columns => [ 'ticketing_queueid' ],
531 # references => [ 'id' ],
533 { columns => [ 'agent_custnum' ],
534 table => 'cust_main',
535 references => [ 'custnum' ],
540 'agent_pkg_class' => {
542 'agentpkgclassnum', 'serial', '', '', '', '',
543 'agentnum', 'int', '', '', '', '',
544 'classnum', 'int', 'NULL', '', '', '',
545 'commission_percent', 'decimal', '', '7,4', '', '',
547 'primary_key' => 'agentpkgclassnum',
548 'unique' => [ [ 'agentnum', 'classnum' ], ],
551 { columns => [ 'agentnum' ],
554 { columns => [ 'classnum' ],
555 table => 'pkg_class',
562 'typenum', 'serial', '', '', '', '',
563 'atype', 'varchar', '', $char_d, '', '',
564 'disabled', 'char', 'NULL', 1, '', '',
566 'primary_key' => 'typenum',
568 'index' => [ ['disabled'] ],
573 'typepkgnum', 'serial', '', '', '', '',
574 'typenum', 'int', '', '', '', '',
575 'pkgpart', 'int', '', '', '', '',
577 'primary_key' => 'typepkgnum',
578 'unique' => [ ['typenum', 'pkgpart'] ],
579 'index' => [ ['typenum'] ],
581 { columns => [ 'typenum' ],
582 table => 'agent_type',
584 { columns => [ 'pkgpart' ],
590 'agent_currency' => {
592 'agentcurrencynum', 'serial', '', '', '', '',
593 'agentnum', 'int', '', '', '', '',
594 'currency', 'char', '', 3, '', '',
596 'primary_key' => 'agentcurrencynum',
598 'index' => [ ['agentnum'] ],
600 { columns => [ 'agentnum' ],
608 'salesnum', 'serial', '', '', '', '',
609 'salesperson', 'varchar', '', $char_d, '', '',
610 'agentnum', 'int', 'NULL', '', '', '',
611 'sales_custnum', 'int', 'NULL', '', '', '',
612 'disabled', 'char', 'NULL', 1, '', '',
614 'primary_key' => 'salesnum',
616 'index' => [ ['salesnum'], ['disabled'] ],
618 { columns => [ 'agentnum' ],
621 { columns => [ 'sales_custnum' ],
622 table => 'cust_main',
623 references => [ 'custnum' ],
628 'sales_pkg_class' => {
630 'salespkgclassnum', 'serial', '', '', '', '',
631 'salesnum', 'int', '', '', '', '',
632 'classnum', 'int', 'NULL', '', '', '',
633 'commission_percent', 'decimal', '', '7,4', '', '',
634 'commission_duration', 'int', 'NULL', '', '', '',
636 'primary_key' => 'salespkgclassnum',
637 'unique' => [ [ 'salesnum', 'classnum' ], ],
640 { columns => [ 'salesnum' ],
643 { columns => [ 'classnum' ],
644 table => 'pkg_class',
649 'cust_attachment' => {
651 'attachnum', 'serial', '', '', '', '',
652 'custnum', 'int', '', '', '', '',
653 '_date', @date_type, '', '',
654 'otaker', 'varchar', 'NULL', 32, '', '',
655 'usernum', 'int', 'NULL', '', '', '',
656 'filename', 'varchar', '', 255, '', '',
657 'mime_type', 'varchar', '', $char_d, '', '',
658 'title', 'varchar', 'NULL', $char_d, '', '',
659 'body', 'blob', 'NULL', '', '', '',
660 'disabled', @date_type, '', '',
662 'primary_key' => 'attachnum',
664 'index' => [ ['custnum'], ['usernum'], ],
666 { columns => [ 'custnum' ],
667 table => 'cust_main',
669 { columns => [ 'usernum' ],
670 table => 'access_user',
678 'invnum', 'serial', '', '', '', '',
679 'custnum', 'int', '', '', '', '',
680 '_date', @date_type, '', '',
681 'charged', @money_type, '', '',
682 'currency', 'char', 'NULL', 3, '', '',
683 'invoice_terms', 'varchar', 'NULL', $char_d, '', '',
685 #customer balance info at invoice generation time
687 'previous_balance', @money_typen, '', '', #eventually not nullable
688 'billing_balance', @money_typen, '', '', #eventually not nullable
690 #deprecated (unused by now, right?)
691 'printed', 'int', '', '', '', '',
694 'closed', 'char', 'NULL', 1, '', '', #not yet used much
695 'statementnum', 'int', 'NULL', '', '', '', #invoice aggregate statements
696 'agent_invid', 'int', 'NULL', '', '', '', #(varchar?) importing legacy
697 'promised_date', @date_type, '', '',
699 'pending', 'char', 'NULL', 1, '', '',
701 'primary_key' => 'invnum',
702 'unique' => [ [ 'custnum', 'agent_invid' ] ], #agentnum? huh
703 'index' => [ ['custnum'], ['_date'], ['statementnum'],
707 { columns => [ 'custnum' ],
708 table => 'cust_main',
710 { columns => [ 'statementnum' ],
711 table => 'cust_statement',
716 'cust_bill_void' => {
719 'invnum', 'int', '', '', '', '',
720 'custnum', 'int', '', '', '', '',
721 '_date', @date_type, '', '',
722 'charged', @money_type, '', '',
723 'currency', 'char', 'NULL', 3, '', '',
724 'invoice_terms', 'varchar', 'NULL', $char_d, '', '',
726 #customer balance info at invoice generation time
727 'previous_balance', @money_typen, '', '', #eventually not nullable
728 'billing_balance', @money_typen, '', '', #eventually not nullable
731 'closed', 'char', 'NULL', 1, '', '', #not yet used much
732 'statementnum', 'int', 'NULL', '', '', '', #invoice aggregate statements
733 'agent_invid', 'int', 'NULL', '', '', '', #(varchar?) importing legacy
734 'promised_date', @date_type, '', '',
737 'void_date', @date_type, '', '',
738 'reason', 'varchar', 'NULL', $char_d, '', '',
739 'reasonnum', 'int', 'NULL', '', '', '',
740 'void_usernum', 'int', 'NULL', '', '', '',
742 'primary_key' => 'invnum',
743 'unique' => [ [ 'custnum', 'agent_invid' ] ], #agentnum? huh
744 'index' => [ ['custnum'], ['_date'], ['statementnum'],
745 ['agent_invid'], [ 'void_usernum' ],
748 { columns => [ 'custnum' ],
749 table => 'cust_main',
751 { columns => [ 'statementnum' ],
752 table => 'cust_statement', #_void? both?
754 { columns => [ 'reasonnum' ],
757 { columns => [ 'void_usernum' ],
758 table => 'access_user',
759 references => [ 'usernum' ],
764 #for importing invoices from a legacy system for display purposes only
765 # no effect upon balance
766 'legacy_cust_bill' => {
768 'legacyinvnum', 'serial', '', '', '', '',
769 'legacyid', 'varchar', 'NULL', $char_d, '', '',
770 'custnum', 'int', '', '', '', '',
771 '_date', @date_type, '', '',
772 'charged', @money_type, '', '',
773 'currency', 'char', 'NULL', 3, '', '',
774 'content_pdf', 'blob', 'NULL', '', '', '',
775 'content_html', 'text', 'NULL', '', '', '',
776 'locale', 'varchar', 'NULL', 16, '', '',
778 'primary_key' => 'legacyinvnum',
780 'index' => [ ['legacyid', 'custnum', 'locale' ], ],
782 { columns => [ 'custnum' ],
783 table => 'cust_main',
788 'legacy_cust_history' => {
790 'legacyhistorynum', 'serial', '', '', '', '',
791 'custnum', 'int', '', '', '', '',
792 'history_action', 'varchar', '', $char_d, '', '',
793 'history_date', @date_type, '', '',
794 'history_usernum', 'int', 'NULL', '', '', '',
795 'item', 'varchar', 'NULL', $char_d, '', '',
796 'description', 'varchar', 'NULL', 2*$char_d, '', '',
797 'change_data', 'text', 'NULL', '', '', '',
799 'primary_key' => 'legacyhistorynum',
801 'index' => [ ['custnum'], ['history_date'], ],
803 { columns => [ 'custnum' ],
804 table => 'cust_main',
806 { columns => [ 'history_usernum' ],
807 table => 'access_user',
808 references => [ 'usernum' ],
813 'cust_statement' => {
815 'statementnum', 'serial', '', '', '', '',
816 'custnum', 'int', '', '', '', '',
817 '_date', @date_type, '', '',
819 'primary_key' => 'statementnum',
821 'index' => [ ['custnum'], ['_date'], ],
823 { columns => [ 'custnum' ],
824 table => 'cust_main',
831 'eventpart', 'serial', '', '', '', '',
832 'agentnum', 'int', 'NULL', '', '', '',
833 'event', 'varchar', '', $char_d, '', '',
834 'eventtable', 'varchar', '', $char_d, '', '',
835 'check_freq', 'varchar', 'NULL', $char_d, '', '',
836 'weight', 'int', '', '', '', '',
837 'action', 'varchar', '', $char_d, '', '',
838 'disabled', 'char', 'NULL', 1, '', '',
840 'primary_key' => 'eventpart',
842 'index' => [ ['agentnum'], ['eventtable'], ['check_freq'],
846 { columns => [ 'agentnum' ],
852 'part_event_option' => {
854 'optionnum', 'serial', '', '', '', '',
855 'eventpart', 'int', '', '', '', '',
856 'optionname', 'varchar', '', $char_d, '', '',
857 'optionvalue', 'text', 'NULL', '', '', '',
859 'primary_key' => 'optionnum',
861 'index' => [ [ 'eventpart' ], [ 'optionname' ] ],
863 { columns => [ 'eventpart' ],
864 table => 'part_event',
869 'part_event_condition' => {
871 'eventconditionnum', 'serial', '', '', '', '',
872 'eventpart', 'int', '', '', '', '',
873 'conditionname', 'varchar', '', $char_d, '', '',
875 'primary_key' => 'eventconditionnum',
877 'index' => [ [ 'eventpart' ], [ 'conditionname' ] ],
879 { columns => [ 'eventpart' ],
880 table => 'part_event',
885 'part_event_condition_option' => {
887 'optionnum', 'serial', '', '', '', '',
888 'eventconditionnum', 'int', '', '', '', '',
889 'optionname', 'varchar', '', $char_d, '', '',
890 'optionvalue', 'text', 'NULL', '', '', '',
892 'primary_key' => 'optionnum',
894 'index' => [ [ 'eventconditionnum' ], [ 'optionname' ] ],
896 { columns => [ 'eventconditionnum' ],
897 table => 'part_event_condition',
902 'part_event_condition_option_option' => {
904 'optionoptionnum', 'serial', '', '', '', '',
905 'optionnum', 'int', '', '', '', '',
906 'optionname', 'varchar', '', $char_d, '', '',
907 'optionvalue', 'text', 'NULL', '', '', '',
909 'primary_key' => 'optionoptionnum',
911 'index' => [ [ 'optionnum' ], [ 'optionname' ] ],
913 { columns => [ 'optionnum' ],
914 table => 'part_event_condition_option',
921 'eventnum', 'serial', '', '', '', '',
922 'eventpart', 'int', '', '', '', '',
923 'tablenum', 'int', '', '', '', '',
924 '_date', @date_type, '', '',
925 'status', 'varchar', '', $char_d, '', '',
926 'statustext', 'text', 'NULL', '', '', '',
927 'no_action', 'char', 'NULL', 1, '', '',
929 'primary_key' => 'eventnum',
930 #no... there are retries now #'unique' => [ [ 'eventpart', 'invnum' ] ],
932 'index' => [ ['eventpart'], ['tablenum'], ['status'],
933 ['statustext'], ['_date'], ['no_action'],
936 { columns => [ 'eventpart' ],
937 table => 'part_event',
942 'cust_event_fee' => {
944 'eventfeenum', 'serial', '', '', '', '',
945 'eventnum', 'int', '', '', '', '',
946 'billpkgnum', 'int', 'NULL', '', '', '',
947 'feepart', 'int', '', '', '', '',
948 'nextbill', 'char', 'NULL', 1, '', '',
950 'primary_key' => 'eventfeenum', # I'd rather just use eventnum
951 'unique' => [ [ 'billpkgnum' ], [ 'eventnum' ] ], # one-to-one link
952 'index' => [ [ 'feepart' ] ],
954 { columns => [ 'eventnum' ],
955 table => 'cust_event',
957 #{ columns => [ 'billpkgnum' ],
958 # table => 'cust_bill_pkg',
960 { columns => [ 'feepart' ],
968 'billpkgnum', 'serial', '', '', '', '',
969 'invnum', 'int', '', '', '', '',
970 'pkgnum', 'int', '', '', '', '',
971 'pkgpart_override', 'int', 'NULL', '', '', '',
972 'setup', @money_type, '', '',
973 'unitsetup', @money_typen, '', '',
974 'setup_billed_currency', 'char', 'NULL', 3, '', '',
975 'setup_billed_amount', @money_typen, '', '',
976 'recur', @money_type, '', '',
977 'unitrecur', @money_typen, '', '',
978 'recur_billed_currency', 'char', 'NULL', 3, '', '',
979 'recur_billed_amount', @money_typen, '', '',
980 'sdate', @date_type, '', '',
981 'edate', @date_type, '', '',
982 'itemdesc', 'varchar', 'NULL', $char_d, '', '',
983 'itemcomment', 'varchar', 'NULL', $char_d, '', '',
984 'section', 'varchar', 'NULL', $char_d, '', '',
985 'freq', 'varchar', 'NULL', $char_d, '', '',
986 'quantity', 'int', 'NULL', '', '', '',
987 'hidden', 'char', 'NULL', 1, '', '',
988 'feepart', 'int', 'NULL', '', '', '',
990 'primary_key' => 'billpkgnum',
992 'index' => [ ['invnum'], [ 'pkgnum' ], [ 'itemdesc' ], ],
994 { columns => [ 'invnum' ],
995 table => 'cust_bill',
997 #pkgnum 0 and -1 are used for special things
998 #{ columns => [ 'pkgnum' ],
999 # table => 'cust_pkg',
1001 { columns => [ 'pkgpart_override' ],
1002 table => 'part_pkg',
1003 references => [ 'pkgpart' ],
1005 { columns => [ 'feepart' ],
1006 table => 'part_fee',
1011 'cust_bill_pkg_detail' => {
1013 'detailnum', 'serial', '', '', '', '',
1014 # bigserial? this table will eventually be as big as cdr...
1015 'billpkgnum', 'int', 'NULL', '', '', '', # should not be nullable
1016 'pkgnum', 'int', 'NULL', '', '', '', # deprecated
1017 'invnum', 'int', 'NULL', '', '', '', # deprecated
1018 'amount', 'decimal', 'NULL', '10,4', '', '',
1019 'format', 'char', 'NULL', 1, '', '',
1020 'classnum', 'int', 'NULL', '', '', '',
1021 'duration', 'int', 'NULL', '', 0, '',
1022 'phonenum', 'varchar', 'NULL', 255, '', '', # has to hold a service label
1023 'accountcode', 'varchar', 'NULL', 32, '', '',
1024 'startdate', @date_type, '', '',
1025 'regionname', 'varchar', 'NULL', $char_d, '', '',
1026 'detail', 'varchar', '', 255, '', '',
1028 'primary_key' => 'detailnum',
1030 'index' => [ [ 'billpkgnum' ], [ 'classnum' ],
1031 [ 'pkgnum', 'invnum' ],
1034 { columns => [ 'billpkgnum' ],
1035 table => 'cust_bill_pkg',
1037 #{ columns => [ 'pkgnum' ],
1038 # table => 'cust_pkg',
1040 #{ columns => [ 'invnum' ],
1041 # table => 'cust_bill',
1043 { columns => [ 'classnum' ],
1044 table => 'usage_class',
1049 'cust_bill_pkg_display' => {
1051 'billpkgdisplaynum', 'serial', '', '', '', '',
1052 'billpkgnum', 'int', '', '', '', '',
1053 'section', 'varchar', 'NULL', $char_d, '', '',
1054 #'unitsetup', @money_typen, '', '', #override the linked real one?
1055 #'unitrecur', @money_typen, '', '', #this too?
1056 'post_total', 'char', 'NULL', 1, '', '',
1057 'type', 'char', 'NULL', 1, '', '',
1058 'summary', 'char', 'NULL', 1, '', '',
1060 'primary_key' => 'billpkgdisplaynum',
1062 'index' => [ ['billpkgnum'], ],
1064 { columns => [ 'billpkgnum' ],
1065 table => 'cust_bill_pkg',
1070 'cust_bill_pkg_fee' => {
1072 'billpkgfeenum', 'serial', '', '', '', '',
1073 'billpkgnum', 'int', '', '', '', '',
1074 'base_invnum', 'int', '', '', '', '',
1075 'base_billpkgnum', 'int', 'NULL', '', '', '',
1076 'amount', @money_type, '', '',
1078 'primary_key' => 'billpkgfeenum',
1080 'index' => [ ['billpkgnum'],
1082 ['base_billpkgnum'],
1085 { columns => [ 'billpkgnum' ],
1086 table => 'cust_bill_pkg',
1091 'cust_bill_pkg_fee_void' => {
1093 'billpkgfeenum', 'serial', '', '', '', '',
1094 'billpkgnum', 'int', '', '', '', '',
1095 'base_invnum', 'int', '', '', '', '',
1096 'base_billpkgnum', 'int', 'NULL', '', '', '',
1097 'amount', @money_type, '', '',
1099 'primary_key' => 'billpkgfeenum',
1101 'index' => [ ['billpkgnum'],
1103 ['base_billpkgnum'],
1106 { columns => [ 'billpkgnum' ],
1107 table => 'cust_bill_pkg_void',
1113 'cust_bill_pkg_tax_location' => {
1115 'billpkgtaxlocationnum', 'serial', '', '', '', '',
1116 'billpkgnum', 'int', '', '', '', '',
1117 'taxnum', 'int', '', '', '', '',
1118 'taxtype', 'varchar', '', $char_d, '', '',
1119 'pkgnum', 'int', '', '', '', '', #redundant
1120 'locationnum', 'int', '', '', '', '', #redundant
1121 'amount', @money_type, '', '',
1122 'currency', 'char', 'NULL', 3, '', '',
1123 'taxable_billpkgnum', 'int', 'NULL', '', '', '',
1125 'primary_key' => 'billpkgtaxlocationnum',
1127 'index' => [ [ 'billpkgnum' ],
1131 [ 'taxable_billpkgnum' ],
1134 { columns => [ 'billpkgnum' ],
1135 table => 'cust_bill_pkg',
1137 #{ columns => [ 'pkgnum' ],
1138 # table => 'cust_pkg',
1139 #}, # taxes can apply to fees
1140 { columns => [ 'locationnum' ],
1141 table => 'cust_location',
1143 #{ columns => [ 'taxable_billpkgnum' ],
1144 # table => 'cust_bill_pkg',
1145 # references => [ 'billpkgnum' ],
1150 'cust_bill_pkg_tax_rate_location' => {
1152 'billpkgtaxratelocationnum', 'serial', '', '', '', '',
1153 'billpkgnum', 'int', '', '', '', '',
1154 'taxnum', 'int', '', '', '', '',
1155 'taxtype', 'varchar', '', $char_d, '', '',
1156 'locationtaxid', 'varchar', 'NULL', $char_d, '', '',
1157 'taxratelocationnum', 'int', '', '', '', '',
1158 'amount', @money_type, '', '',
1159 'currency', 'char', 'NULL', 3, '', '',
1160 'taxable_billpkgnum', 'int', 'NULL', '', '', '',
1161 'taxclass', 'varchar', 'NULL', 10, '', '',
1163 'primary_key' => 'billpkgtaxratelocationnum',
1165 'index' => [ ['billpkgnum'], ['taxnum'], ['taxratelocationnum'],
1166 ['taxable_billpkgnum'],
1169 { columns => [ 'billpkgnum' ],
1170 table => 'cust_bill_pkg',
1172 { columns => [ 'taxratelocationnum' ],
1173 table => 'tax_rate_location',
1175 #{ columns => [ 'taxable_billpkgnum' ],
1176 # table => 'cust_bill_pkg',
1177 # references => [ 'billpkgnum' ],
1182 'cust_bill_pkg_void' => {
1184 'billpkgnum', 'int', '', '', '', '',
1185 'invnum', 'int', '', '', '', '',
1186 'pkgnum', 'int', '', '', '', '',
1187 'pkgpart_override', 'int', 'NULL', '', '', '',
1188 'setup', @money_type, '', '',
1189 'recur', @money_type, '', '',
1190 #XXX a currency for a line item? or just one for the entire invoice
1191 #'currency', 'char', 'NULL', 3, '', '',
1192 'sdate', @date_type, '', '',
1193 'edate', @date_type, '', '',
1194 'itemdesc', 'varchar', 'NULL', $char_d, '', '',
1195 'itemcomment', 'varchar', 'NULL', $char_d, '', '',
1196 'section', 'varchar', 'NULL', $char_d, '', '',
1197 'freq', 'varchar', 'NULL', $char_d, '', '',
1198 'quantity', 'int', 'NULL', '', '', '',
1199 'unitsetup', @money_typen, '', '',
1200 'unitrecur', @money_typen, '', '',
1201 'hidden', 'char', 'NULL', 1, '', '',
1202 'feepart', 'int', 'NULL', '', '', '',
1204 'void_date', @date_type, '', '',
1205 'reason', 'varchar', 'NULL', $char_d, '', '',
1206 'reasonnum', 'int', 'NULL', '', '', '',
1207 'void_usernum', 'int', 'NULL', '', '', '',
1209 'primary_key' => 'billpkgnum',
1211 'index' => [ ['invnum'], ['pkgnum'], ['itemdesc'],
1215 { columns => [ 'invnum' ],
1216 table => 'cust_bill_void',
1218 { columns => [ 'reasonnum' ],
1221 #pkgnum 0 and -1 are used for special things
1222 #{ columns => [ 'pkgnum' ],
1223 # table => 'cust_pkg',
1225 { columns => [ 'pkgpart_override' ],
1226 table => 'part_pkg',
1227 references => [ 'pkgpart' ],
1229 { columns => [ 'void_usernum' ],
1230 table => 'access_user',
1231 references => [ 'usernum' ],
1236 'cust_bill_pkg_detail_void' => {
1238 'detailnum', 'int', '', '', '', '',
1239 'billpkgnum', 'int', 'NULL', '', '', '', # should not be nullable
1240 'pkgnum', 'int', 'NULL', '', '', '', # deprecated
1241 'invnum', 'int', 'NULL', '', '', '', # deprecated
1242 'amount', 'decimal', 'NULL', '10,4', '', '',
1243 'format', 'char', 'NULL', 1, '', '',
1244 'classnum', 'int', 'NULL', '', '', '',
1245 'duration', 'int', 'NULL', '', 0, '',
1246 'phonenum', 'varchar', 'NULL', 255, '', '',
1247 'accountcode', 'varchar', 'NULL', 32, '', '',
1248 'startdate', @date_type, '', '',
1249 'regionname', 'varchar', 'NULL', $char_d, '', '',
1250 'detail', 'varchar', '', 255, '', '',
1252 'primary_key' => 'detailnum',
1254 'index' => [ ['billpkgnum'], ['classnum'], ['pkgnum', 'invnum'] ],
1256 { columns => [ 'billpkgnum' ],
1257 table => 'cust_bill_pkg_void',
1259 #{ columns => [ 'pkgnum' ],
1260 # table => 'cust_pkg',
1262 #{ columns => [ 'invnum' ],
1263 # table => 'cust_bill',
1265 { columns => [ 'classnum' ],
1266 table => 'usage_class',
1271 'cust_bill_pkg_display_void' => {
1273 'billpkgdisplaynum', 'int', '', '', '', '',
1274 'billpkgnum', 'int', '', '', '', '',
1275 'section', 'varchar', 'NULL', $char_d, '', '',
1276 #'unitsetup', @money_typen, '', '', #override the linked real one?
1277 #'unitrecur', @money_typen, '', '', #this too?
1278 'post_total', 'char', 'NULL', 1, '', '',
1279 'type', 'char', 'NULL', 1, '', '',
1280 'summary', 'char', 'NULL', 1, '', '',
1282 'primary_key' => 'billpkgdisplaynum',
1284 'index' => [ ['billpkgnum'], ],
1286 { columns => [ 'billpkgnum' ],
1287 table => 'cust_bill_pkg_void',
1292 'cust_bill_pkg_tax_location_void' => {
1294 'billpkgtaxlocationnum', 'int', '', '', '', '',
1295 'billpkgnum', 'int', '', '', '', '',
1296 'taxnum', 'int', '', '', '', '',
1297 'taxtype', 'varchar', '', $char_d, '', '',
1298 'pkgnum', 'int', '', '', '', '',
1299 'locationnum', 'int', '', '', '', '', #redundant?
1300 'amount', @money_type, '', '',
1301 'currency', 'char', 'NULL', 3, '', '',
1302 'taxable_billpkgnum', 'int', 'NULL', '', '', '',
1304 'primary_key' => 'billpkgtaxlocationnum',
1306 'index' => [ ['billpkgnum'], ['taxnum'], ['pkgnum'],
1310 { columns => [ 'billpkgnum' ],
1311 table => 'cust_bill_pkg_void',
1313 { columns => [ 'locationnum' ],
1314 table => 'cust_location',
1316 #{ columns => [ 'taxable_billpkgnum' ],
1317 # table => 'cust_bill_pkg_void',
1318 # references => [ 'billpkgnum' ],
1323 'cust_bill_pkg_tax_rate_location_void' => {
1325 'billpkgtaxratelocationnum', 'int', '', '', '', '',
1326 'billpkgnum', 'int', '', '', '', '',
1327 'taxnum', 'int', '', '', '', '',
1328 'taxtype', 'varchar', '', $char_d, '', '',
1329 'locationtaxid', 'varchar', 'NULL', $char_d, '', '',
1330 'taxratelocationnum', 'int', '', '', '', '',
1331 'amount', @money_type, '', '',
1332 'currency', 'char', 'NULL', 3, '', '',
1334 'primary_key' => 'billpkgtaxratelocationnum',
1336 'index' => [ ['billpkgnum'], ['taxnum'], ['taxratelocationnum'] ],
1338 { columns => [ 'billpkgnum' ],
1339 table => 'cust_bill_pkg_void',
1341 { columns => [ 'taxratelocationnum' ],
1342 table => 'tax_rate_location',
1349 'crednum', 'serial', '', '', '', '',
1350 'custnum', 'int', '', '', '', '',
1351 '_date', @date_type, '', '',
1352 'amount',@money_type, '', '',
1353 'currency', 'char', 'NULL', 3, '', '',
1354 'otaker', 'varchar', 'NULL', 32, '', '',
1355 'usernum', 'int', 'NULL', '', '', '',
1356 'reason', 'text', 'NULL', '', '', '',
1357 'reasonnum', 'int', 'NULL', '', '', '',
1358 'addlinfo', 'text', 'NULL', '', '', '',
1359 'closed', 'char', 'NULL', 1, '', '',
1360 'pkgnum', 'int', 'NULL', '', '','',#desired pkgnum for pkg-balances
1361 'eventnum', 'int', 'NULL', '', '','',#triggering event for commission
1362 'commission_agentnum', 'int', 'NULL', '', '', '', #
1363 'commission_salesnum', 'int', 'NULL', '', '', '', #
1364 'commission_pkgnum', 'int', 'NULL', '', '', '', #
1365 'commission_invnum', 'int', 'NULL', '', '', '',
1366 'credbatch', 'varchar', 'NULL', $char_d, '', '',
1368 'primary_key' => 'crednum',
1370 'index' => [ ['custnum'], ['_date'], ['usernum'], ['eventnum'],
1371 ['commission_salesnum'], ['credbatch'],
1374 { columns => [ 'custnum' ],
1375 table => 'cust_main',
1377 { columns => [ 'usernum' ],
1378 table => 'access_user',
1380 { columns => [ 'reasonnum' ],
1383 { columns => [ 'pkgnum' ],
1384 table => 'cust_pkg',
1386 { columns => [ 'eventnum' ],
1387 table => 'cust_event',
1389 { columns => [ 'commission_agentnum' ],
1391 references => [ 'agentnum' ],
1393 { columns => [ 'commission_salesnum' ],
1395 references => [ 'salesnum' ],
1397 { columns => [ 'commission_pkgnum' ],
1398 table => 'cust_pkg',
1399 references => [ 'pkgnum' ],
1401 { columns => [ 'commission_invnum' ],
1402 table => 'cust_bill',
1403 references => [ 'invnum' ],
1408 'cust_credit_void' => {
1410 'crednum', 'serial', '', '', '', '',
1411 'custnum', 'int', '', '', '', '',
1412 '_date', @date_type, '', '',
1413 'amount',@money_type, '', '',
1414 'currency', 'char', 'NULL', 3, '', '',
1415 'otaker', 'varchar', 'NULL', 32, '', '',
1416 'usernum', 'int', 'NULL', '', '', '',
1417 'reason', 'text', 'NULL', '', '', '',
1418 'reasonnum', 'int', 'NULL', '', '', '',
1419 'addlinfo', 'text', 'NULL', '', '', '',
1420 'closed', 'char', 'NULL', 1, '', '',
1421 'pkgnum', 'int', 'NULL', '', '','',
1422 'eventnum', 'int', 'NULL', '', '','',
1423 'commission_agentnum', 'int', 'NULL', '', '', '',
1424 'commission_salesnum', 'int', 'NULL', '', '', '',
1425 'commission_pkgnum', 'int', 'NULL', '', '', '',
1426 'commission_invnum', 'int', 'NULL', '', '', '',
1428 'void_date', @date_type, '', '',
1429 'void_reason', 'varchar', 'NULL', $char_d, '', '',
1430 'void_reasonnum', 'int', 'NULL', '', '', '',
1431 'void_usernum', 'int', 'NULL', '', '', '',
1433 'primary_key' => 'crednum',
1435 'index' => [ ['custnum'], ['_date'], ['usernum'], ['eventnum'],
1436 ['commission_salesnum'],
1439 { columns => [ 'custnum' ],
1440 table => 'cust_main',
1442 { columns => [ 'usernum' ],
1443 table => 'access_user',
1445 { columns => [ 'reasonnum' ],
1448 { columns => [ 'pkgnum' ],
1449 table => 'cust_pkg',
1451 { columns => [ 'eventnum' ],
1452 table => 'cust_event',
1454 { columns => [ 'commission_agentnum' ],
1456 references => [ 'agentnum' ],
1458 { columns => [ 'commission_salesnum' ],
1460 references => [ 'salesnum' ],
1462 { columns => [ 'commission_pkgnum' ],
1463 table => 'cust_pkg',
1464 references => [ 'pkgnum' ],
1466 { columns => [ 'commission_invnum' ],
1467 table => 'cust_bill',
1468 references => [ 'invnum' ],
1470 { columns => [ 'void_reasonnum' ],
1472 references => [ 'reasonnum' ],
1474 { columns => [ 'void_usernum' ],
1475 table => 'access_user',
1476 references => [ 'usernum' ],
1482 'cust_credit_bill' => {
1484 'creditbillnum', 'serial', '', '', '', '',
1485 'crednum', 'int', '', '', '', '',
1486 'invnum', 'int', '', '', '', '',
1487 '_date', @date_type, '', '',
1488 'amount', @money_type, '', '',
1489 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances
1491 'primary_key' => 'creditbillnum',
1493 'index' => [ ['crednum'], ['invnum'] ],
1495 { columns => [ 'crednum' ],
1496 table => 'cust_credit',
1498 { columns => [ 'invnum' ],
1499 table => 'cust_bill',
1501 { columns => [ 'pkgnum' ],
1502 table => 'cust_pkg',
1507 'cust_credit_bill_pkg' => {
1509 'creditbillpkgnum', 'serial', '', '', '', '',
1510 'creditbillnum', 'int', '', '', '', '',
1511 'billpkgnum', 'int', '', '', '', '',
1512 'billpkgtaxlocationnum', 'int', 'NULL', '', '', '',
1513 'billpkgtaxratelocationnum', 'int', 'NULL', '', '', '',
1514 'amount', @money_type, '', '',
1515 'setuprecur', 'varchar', '', $char_d, '', '',
1516 'sdate', @date_type, '', '',
1517 'edate', @date_type, '', '',
1519 'primary_key' => 'creditbillpkgnum',
1521 'index' => [ [ 'creditbillnum' ],
1523 [ 'billpkgtaxlocationnum' ],
1524 [ 'billpkgtaxratelocationnum' ],
1527 { columns => [ 'creditbillnum' ],
1528 table => 'cust_credit_bill',
1530 { columns => [ 'billpkgnum' ],
1531 table => 'cust_bill_pkg',
1533 { columns => [ 'billpkgtaxlocationnum' ],
1534 table => 'cust_bill_pkg_tax_location',
1536 { columns => [ 'billpkgtaxratelocationnum' ],
1537 table => 'cust_bill_pkg_tax_rate_location',
1542 'cust_credit_source_bill_pkg' => {
1544 'creditsourcebillpkgnum', 'serial', '', '', '', '',
1545 'crednum', 'int', '', '', '', '',
1546 'billpkgnum', 'int', '', '', '', '',
1547 'amount', @money_type, '', '',
1548 'currency', 'char', 'NULL', 3, '', '',
1550 'primary_key' => 'creditsourcebillpkgnum',
1552 'index' => [ ['crednum'], ['billpkgnum'] ],
1554 { columns => ['billpkgnum'],
1555 table => 'cust_bill_pkg',
1557 { columns => ['crednum'],
1558 table => 'cust_credit',
1565 'custnum', 'serial', '', '', '', '',
1566 'agentnum', 'int', '', '', '', '',
1567 'salesnum', 'int', 'NULL', '', '', '',
1568 'agent_custid', 'varchar', 'NULL', $char_d, '', '',
1569 'classnum', 'int', 'NULL', '', '', '',
1570 'custbatch', 'varchar', 'NULL', $char_d, '', '',
1571 # 'titlenum', 'int', 'NULL', '', '', '',
1572 'last', 'varchar', '', 2*$char_d, '', '',
1573 # 'middle', 'varchar', 'NULL', $char_d, '', '',
1574 'first', 'varchar', '', $char_d, '', '',
1575 'ss', 'varchar', 'NULL', 11, '', '',
1576 'stateid', 'varchar', 'NULL', $char_d, '', '',
1577 'stateid_state', 'varchar', 'NULL', $char_d, '', '',
1578 'national_id', 'varchar', 'NULL', $char_d, '', '',
1579 'birthdate' ,@date_type, '', '',
1580 'spouse_last', 'varchar', 'NULL', 2*$char_d, '', '',
1581 'spouse_first', 'varchar', 'NULL', $char_d, '', '',
1582 'spouse_birthdate', @date_type, '', '',
1583 'anniversary_date', @date_type, '', '',
1584 'signupdate',@date_type, '', '',
1585 'dundate', @date_type, '', '',
1586 'company', 'varchar', 'NULL', $char_d, '', '',
1587 'address1', 'varchar', 'NULL', $char_d, '', '',
1588 'address2', 'varchar', 'NULL', $char_d, '', '',
1589 'city', 'varchar', 'NULL', $char_d, '', '',
1590 'county', 'varchar', 'NULL', $char_d, '', '',
1591 'state', 'varchar', 'NULL', $char_d, '', '',
1592 'zip', 'varchar', 'NULL', 10, '', '',
1593 'country', 'char', 'NULL', 2, '', '',
1594 'latitude', 'decimal', 'NULL', '10,7', '', '',
1595 'longitude','decimal', 'NULL', '10,7', '', '',
1596 'coord_auto', 'char', 'NULL', 1, '', '',
1597 'addr_clean', 'char', 'NULL', 1, '', '',
1598 'daytime', 'varchar', 'NULL', 20, '', '',
1599 'night', 'varchar', 'NULL', 20, '', '',
1600 'fax', 'varchar', 'NULL', 12, '', '',
1601 'mobile', 'varchar', 'NULL', 12, '', '',
1602 'ship_last', 'varchar', 'NULL', 2*$char_d, '', '',
1603 # 'ship_middle', 'varchar', 'NULL', $char_d, '', '',
1604 'ship_first', 'varchar', 'NULL', $char_d, '', '',
1605 'ship_company', 'varchar', 'NULL', $char_d, '', '',
1606 'ship_address1', 'varchar', 'NULL', $char_d, '', '',
1607 'ship_address2', 'varchar', 'NULL', $char_d, '', '',
1608 'ship_city', 'varchar', 'NULL', $char_d, '', '',
1609 'ship_county', 'varchar', 'NULL', $char_d, '', '',
1610 'ship_state', 'varchar', 'NULL', $char_d, '', '',
1611 'ship_zip', 'varchar', 'NULL', 10, '', '',
1612 'ship_country', 'char', 'NULL', 2, '', '',
1613 'ship_latitude', 'decimal', 'NULL', '10,7', '', '',
1614 'ship_longitude','decimal', 'NULL', '10,7', '', '',
1615 'ship_coord_auto', 'char', 'NULL', 1, '', '',
1616 'ship_addr_clean', 'char', 'NULL', 1, '', '',
1617 'ship_daytime', 'varchar', 'NULL', 20, '', '',
1618 'ship_night', 'varchar', 'NULL', 20, '', '',
1619 'ship_fax', 'varchar', 'NULL', 12, '', '',
1620 'ship_mobile', 'varchar', 'NULL', 12, '', '',
1621 'currency', 'char', 'NULL', 3, '', '',
1623 'geocode', 'varchar', 'NULL', 20, '', '',
1624 'censustract', 'varchar', 'NULL', 20, '', '', # 7 to save space?
1625 'censusyear', 'char', 'NULL', 4, '', '',
1626 'district', 'varchar', 'NULL', 20, '', '',
1627 'tax', 'char', 'NULL', 1, '', '',
1628 'otaker', 'varchar', 'NULL', 32, '', '',
1629 'usernum', 'int', 'NULL', '', '', '',
1630 'refnum', 'int', '', '', '', '',
1631 'referral_custnum', 'int', 'NULL', '', '', '',
1632 'comments', 'text', 'NULL', '', '', '',
1633 'spool_cdr','char', 'NULL', 1, '', '',
1634 'squelch_cdr','char', 'NULL', 1, '', '',
1635 'cdr_termination_percentage', 'decimal', 'NULL', '7,4', '', '',
1636 'invoice_terms', 'varchar', 'NULL', $char_d, '', '',
1637 'credit_limit', @money_typen, '', '',
1638 'credit_limit_currency', 'char', 'NULL', 3, '', '',
1639 'archived', 'char', 'NULL', 1, '', '',
1640 'email_csv_cdr', 'char', 'NULL', 1, '', '',
1641 'accountcode_cdr', 'char', 'NULL', 1, '', '',
1642 'billday', 'int', 'NULL', '', '', '',
1643 'prorate_day', 'int', 'NULL', '', '', '',
1644 'force_prorate_day', 'char', 'NULL', 1, '', '',
1645 'edit_subject', 'char', 'NULL', 1, '', '',
1646 'locale', 'varchar', 'NULL', 16, '', '',
1647 'calling_list_exempt', 'char', 'NULL', 1, '', '',
1648 'invoice_noemail', 'char', 'NULL', 1, '', '',
1649 'message_noemail', 'char', 'NULL', 1, '', '',
1650 'bill_locationnum', 'int', 'NULL', '', '', '',
1651 'ship_locationnum', 'int', 'NULL', '', '', '',
1652 'taxstatusnum', 'int', 'NULL', '', '', '',
1653 'complimentary', 'char', 'NULL', 1, '', '',
1654 'po_number', 'varchar', 'NULL', $char_d, '', '',
1655 'invoice_attn', 'varchar', 'NULL', $char_d, '', '',
1656 'invoice_ship_address', 'char', 'NULL', 1, '', '',
1657 'postal_invoice', 'char', 'NULL', 1, '', '',
1659 'primary_key' => 'custnum',
1660 'unique' => [ [ 'agentnum', 'agent_custid' ] ],
1661 #'index' => [ ['last'], ['company'] ],
1663 ['agentnum'], ['refnum'], ['classnum'], ['usernum'],
1665 [ 'referral_custnum' ],
1667 [ 'ship_locationnum' ],
1668 [ 'bill_locationnum' ],
1671 { columns => [ 'agentnum' ],
1674 { columns => [ 'salesnum' ],
1677 { columns => [ 'refnum' ],
1678 table => 'part_referral',
1680 { columns => [ 'classnum' ],
1681 table => 'cust_class',
1683 { columns => [ 'usernum' ],
1684 table => 'access_user',
1686 { columns => [ 'referral_custnum' ],
1687 table => 'cust_main',
1688 references => [ 'custnum' ],
1690 { columns => [ 'bill_locationnum' ],
1691 table => 'cust_location',
1692 references => [ 'locationnum' ],
1694 { columns => [ 'ship_locationnum' ],
1695 table => 'cust_location',
1696 references => [ 'locationnum' ],
1703 'custpaybynum', 'serial', '', '', '', '',
1704 'custnum', 'int', '', '', '', '',
1705 'weight', 'int', 'NULL', '', '', '',
1706 'payby', 'char', '', 4, '', '',
1707 'payinfo', 'varchar', 'NULL', 512, '', '',
1708 'paycardtype', 'varchar', 'NULL', $char_d, '', '',
1709 'paycvv', 'varchar', 'NULL', 512, '', '',
1710 'paymask', 'varchar', 'NULL', $char_d, '', '',
1711 #'paydate', @date_type, '', '',
1712 'paydate', 'varchar', 'NULL', 10, '', '',
1713 'paystart_month', 'int', 'NULL', '', '', '',
1714 'paystart_year', 'int', 'NULL', '', '', '',
1715 'payissue', 'varchar', 'NULL', 2, '', '',
1716 'payname', 'varchar', 'NULL', 2*$char_d, '', '',
1717 'paystate', 'varchar', 'NULL', $char_d, '', '',
1718 'paytype', 'varchar', 'NULL', $char_d, '', '',
1719 'payip', 'varchar', 'NULL', 15, '', '',
1720 'locationnum', 'int', 'NULL', '', '', '',
1722 'primary_key' => 'custpaybynum',
1724 'index' => [ [ 'custnum' ] ],
1726 { columns => [ 'custnum' ],
1727 table => 'cust_main',
1729 { columns => [ 'locationnum' ],
1730 table => 'cust_location',
1735 'contact_class' => {
1737 'classnum', 'serial', '', '', '', '',
1738 'classname', 'varchar', '', $char_d, '', '',
1739 'disabled', 'char', 'NULL', 1, '', '',
1741 'primary_key' => 'classnum',
1743 'index' => [ ['disabled'] ],
1748 'custcontactnum', 'serial', '', '', '', '',
1749 'custnum', 'int', '', '', '', '',
1750 'contactnum', 'int', '', '', '', '',
1751 'classnum', 'int', 'NULL', '', '', '',
1752 'comment', 'varchar', 'NULL', 255, '', '',
1753 'selfservice_access', 'char', 'NULL', 1, '', '',
1754 'invoice_dest', 'char', 'NULL', 1, '', '',
1756 'primary_key' => 'custcontactnum',
1757 'unique' => [ [ 'custnum', 'contactnum' ], ],
1758 'index' => [ [ 'custnum' ], [ 'contactnum' ], ],
1760 { columns => [ 'custnum' ],
1761 table => 'cust_main',
1763 { columns => [ 'contactnum' ],
1766 { columns => [ 'classnum' ],
1767 table => 'contact_class',
1772 'prospect_contact' => {
1774 'prospectcontactnum', 'serial', '', '', '', '',
1775 'prospectnum', 'int', '', '', '', '',
1776 'contactnum', 'int', '', '', '', '',
1777 'classnum', 'int', 'NULL', '', '', '',
1778 'comment', 'varchar', 'NULL', 255, '', '',
1780 'primary_key' => 'prospectcontactnum',
1781 'unique' => [ [ 'prospectnum', 'contactnum' ], ],
1782 'index' => [ [ 'prospectnum' ], [ 'contactnum' ], ],
1784 { columns => [ 'prospectnum' ],
1785 table => 'prospect_main',
1787 { columns => [ 'contactnum' ],
1790 { columns => [ 'classnum' ],
1791 table => 'contact_class',
1798 'contactnum', 'serial', '', '', '', '',
1799 'prospectnum', 'int', 'NULL', '', '', '', #deprecated, now prospect_contact table
1800 'custnum', 'int', 'NULL', '', '', '', #deprecated, now cust_contact table
1801 'locationnum', 'int', 'NULL', '', '', '', #not yet
1802 'classnum', 'int', 'NULL', '', '', '', #deprecated, now prospect_contact or cust_contact
1803 # 'titlenum', 'int', 'NULL', '', '', '', #eg Mr. Mrs. Dr. Rev.
1804 'last', 'varchar', '', $char_d, '', '',
1805 # 'middle', 'varchar', 'NULL', $char_d, '', '',
1806 'first', 'varchar', '', $char_d, '', '',
1807 'title', 'varchar', 'NULL', $char_d, '', '', #eg Head Bottle Washer
1808 'comment', 'varchar', 'NULL', 255, '', '', #depredated, now prospect_contact or cust_contact
1809 'selfservice_access', 'char', 'NULL', 1, '', '', #deprecated, now cust_contact
1810 '_password', 'varchar', 'NULL', $char_d, '', '',
1811 '_password_encoding', 'varchar', 'NULL', $char_d, '', '',
1812 'disabled', 'char', 'NULL', 1, '', '',
1813 'invoice_dest', 'char', 'NULL', 1, '', '',
1815 'primary_key' => 'contactnum',
1817 'index' => [ [ 'prospectnum' ], [ 'custnum' ], [ 'locationnum' ],
1818 [ 'last' ], [ 'first' ],
1821 { columns => [ 'prospectnum' ],
1822 table => 'prospect_main',
1824 { columns => [ 'custnum' ],
1825 table => 'cust_main',
1827 { columns => [ 'locationnum' ],
1828 table => 'cust_location',
1830 { columns => [ 'classnum' ],
1831 table => 'contact_class',
1836 'contact_phone' => {
1838 'contactphonenum', 'serial', '', '', '', '',
1839 'contactnum', 'int', '', '', '', '',
1840 'phonetypenum', 'int', '', '', '', '',
1841 'countrycode', 'varchar', '', 3, '', '',
1842 'phonenum', 'varchar', '', 14, '', '',
1843 'extension', 'varchar', 'NULL', 7, '', '',
1844 #?#'comment', 'varchar', '', $char_d, '', '',
1846 'primary_key' => 'contactphonenum',
1850 { columns => [ 'contactnum' ],
1853 { columns => [ 'phonetypenum' ],
1854 table => 'phone_type',
1861 'phonetypenum', 'serial', '', '', '', '',
1862 'typename', 'varchar', '', $char_d, '', '',
1863 'weight', 'int', '', '', '', '',
1865 'primary_key' => 'phonetypenum',
1866 'unique' => [ [ 'typename' ], ],
1870 'contact_email' => {
1872 'contactemailnum', 'serial', '', '', '', '',
1873 'contactnum', 'int', '', '', '', '',
1874 'emailaddress', 'varchar', '', $char_d, '', '',
1876 'primary_key' => 'contactemailnum',
1877 #'unique' => [ [ 'contactnum', 'emailaddress' ], ],
1878 'unique' => [ [ 'emailaddress' ], ],
1881 { columns => [ 'contactnum' ],
1887 'prospect_main' => {
1889 'prospectnum', 'serial', '', '', '', '',
1890 'agentnum', 'int', '', '', '', '',
1891 'company', 'varchar', 'NULL', $char_d, '', '',
1892 'add_date', @date_type, '', '',
1893 'disabled', 'char', 'NULL', 1, '', '',
1894 'custnum', 'int', 'NULL', '', '', '',
1895 'refnum', 'int', 'NULL', '', '', '',
1896 'taxstatusnum', 'int', 'NULL', '', '', '',
1898 'primary_key' => 'prospectnum',
1900 'index' => [ ['company'], ['agentnum'], ['disabled'], ['refnum'] ],
1902 { columns => [ 'agentnum' ],
1905 { columns => [ 'custnum' ],
1906 table => 'cust_main',
1908 { columns => [ 'refnum' ],
1909 table => 'part_referral',
1917 'quotationnum', 'serial', '', '', '', '',
1918 'prospectnum', 'int', 'NULL', '', '', '',
1919 'custnum', 'int', 'NULL', '', '', '',
1920 '_date', @date_type, '', '',
1921 'disabled', 'char', 'NULL', 1, '', '',
1922 'usernum', 'int', 'NULL', '', '', '',
1923 'close_date', @date_type, '', '',
1924 'confidence', 'int', 'NULL', '', '', '',
1925 #'total', @money_type, '', '',
1926 #'quotation_term', 'varchar', 'NULL', $char_d, '', '',
1928 'primary_key' => 'quotationnum',
1930 'index' => [ [ 'prospectnum' ], ['custnum'], ['disabled'], ],
1932 { columns => [ 'prospectnum' ],
1933 table => 'prospect_main',
1935 { columns => [ 'custnum' ],
1936 table => 'cust_main',
1938 { columns => [ 'usernum' ],
1939 table => 'access_user',
1944 'quotation_pkg' => {
1946 'quotationpkgnum', 'serial', '', '', '', '',
1947 'quotationnum', 'int', 'NULL', '', '', '', #shouldn't be null,
1949 'pkgpart', 'int', '', '', '', '',
1950 'locationnum', 'int', 'NULL', '', '', '',
1951 'start_date', @date_type, '', '',
1952 'contract_end', @date_type, '', '',
1953 'quantity', 'int', 'NULL', '', '', '',
1954 'waive_setup', 'char', 'NULL', 1, '', '',
1955 'unitsetup', @money_typen, '', '',
1956 'unitrecur', @money_typen, '', '',
1958 'primary_key' => 'quotationpkgnum',
1960 'index' => [ ['pkgpart'], ],
1962 { columns => [ 'quotationnum' ],
1963 table => 'quotation',
1965 { columns => [ 'pkgpart' ],
1966 table => 'part_pkg',
1968 { columns => [ 'locationnum' ],
1969 table => 'cust_location',
1974 'quotation_pkg_detail' => {
1976 'detailnum', 'serial', '', '', '', '',
1977 'quotationpkgnum', 'int', '', '', '', '',
1978 'format', 'char', 'NULL', 1, '', '', # not used for anything
1979 'detail', 'varchar', '', 255, '', '',
1980 'copy_on_order', 'char', 'NULL', 1, '', '', # 'Y' to copy when ordering
1982 'primary_key' => 'detailnum',
1984 'index' => [ [ 'quotationpkgnum' ] ],
1986 { columns => [ 'quotationpkgnum' ],
1987 table => 'quotation_pkg',
1988 references => [ 'quotationpkgnum' ],
1993 'quotation_pkg_discount' => {
1995 'quotationpkgdiscountnum', 'serial', '', '', '', '',
1996 'quotationpkgnum', 'int', '', '', '', '',
1997 'discountnum', 'int', '', '', '', '',
1998 'setuprecur', 'varchar', 'NULL', $char_d, '', '',
1999 'amount', @money_typen, '', '',
2000 #'end_date', @date_type, '', '',
2002 'primary_key' => 'quotationpkgdiscountnum',
2004 'index' => [ [ 'quotationpkgnum' ], ], #[ 'discountnum' ] ],
2006 { columns => [ 'quotationpkgnum' ],
2007 table => 'quotation_pkg',
2009 { columns => [ 'discountnum' ],
2010 table => 'discount',
2015 'quotation_pkg_tax' => {
2017 'quotationtaxnum', 'serial', '', '', '', '',
2018 'quotationpkgnum', 'int', '', '', '', '',
2019 'itemdesc', 'varchar', '', $char_d, '', '',
2020 'setup_amount', @money_type, '', '',
2021 'recur_amount', @money_type, '', '',
2023 'primary_key' => 'quotationtaxnum',
2025 'index' => [ [ 'quotationpkgnum' ] ],
2027 { columns => [ 'quotationpkgnum' ],
2028 table => 'quotation_pkg',
2033 'cust_location' => { #'location' now that its prospects too, but...
2035 'locationnum', 'serial', '', '', '', '',
2036 'prospectnum', 'int', 'NULL', '', '', '',
2037 'custnum', 'int', 'NULL', '', '', '',
2038 'locationname', 'varchar', 'NULL', $char_d, '', '',
2039 'address1', 'varchar', '', $char_d, '', '',
2040 'address2', 'varchar', 'NULL', $char_d, '', '',
2041 'city', 'varchar', 'NULL', $char_d, '', '',
2042 'county', 'varchar', 'NULL', $char_d, '', '',
2043 'state', 'varchar', 'NULL', $char_d, '', '',
2044 'zip', 'varchar', 'NULL', 10, '', '',
2045 'latitude', 'decimal', 'NULL', '10,7', '', '',
2046 'longitude', 'decimal', 'NULL', '10,7', '', '',
2047 'coord_auto', 'char', 'NULL', 1, '', '',
2048 'addr_clean', 'char', 'NULL', 1, '', '',
2049 'country', 'char', '', 2, '', '',
2050 'geocode', 'varchar', 'NULL', 20, '', '',
2051 'district', 'varchar', 'NULL', 20, '', '',
2052 'censustract', 'varchar', 'NULL', 20, '', '',
2053 'censusyear', 'char', 'NULL', 4, '', '',
2055 #used only in... DSL qualification? .CA DSL qualification?
2056 'location_type', 'varchar', 'NULL', 20, '', '',
2057 'location_number', 'varchar', 'NULL', 20, '', '',
2058 'location_kind', 'char', 'NULL', 1, '', '',
2060 'incorporated', 'char', 'NULL', 1, '', '',
2061 'disabled', 'char', 'NULL', 1, '', '',
2063 'primary_key' => 'locationnum',
2065 'index' => [ [ 'prospectnum' ], [ 'custnum' ],
2066 [ 'county' ], [ 'state' ], [ 'country' ], [ 'zip' ],
2067 [ 'city' ], [ 'district' ]
2070 { columns => [ 'prospectnum' ],
2071 table => 'prospect_main',
2073 { columns => [ 'custnum' ],
2074 table => 'cust_main',
2079 'cust_main_invoice' => {
2081 'destnum', 'serial', '', '', '', '',
2082 'custnum', 'int', '', '', '', '',
2083 'dest', 'varchar', '', $char_d, '', '',
2085 'primary_key' => 'destnum',
2087 'index' => [ ['custnum'], ],
2089 { columns => [ 'custnum' ],
2090 table => 'cust_main',
2095 'cust_main_credit_limit' => {
2097 'creditlimitnum', 'serial', '', '', '', '',
2098 'custnum', 'int', '', '', '', '',
2099 '_date', @date_type, '', '',
2100 'amount', @money_typen, '', '',
2101 #'amount_currency', 'char', 'NULL', 3, '', '',
2102 'credit_limit', @money_typen, '', '',
2103 #'credit_limit_currency', 'char', 'NULL', 3, '', '',
2105 'primary_key' => 'creditlimitnum',
2107 'index' => [ ['custnum'], ],
2109 { columns => [ 'custnum' ],
2110 table => 'cust_main',
2115 'cust_main_note' => {
2117 'notenum', 'serial', '', '', '', '',
2118 'custnum', 'int', '', '', '', '',
2119 'classnum', 'int', 'NULL', '', '', '',
2120 '_date', @date_type, '', '',
2121 'otaker', 'varchar', 'NULL', 32, '', '',
2122 'usernum', 'int', 'NULL', '', '', '',
2123 'comments', 'text', 'NULL', '', '', '',
2124 'sticky', 'int', '', '', 0, '',
2126 'primary_key' => 'notenum',
2128 'index' => [ [ 'custnum' ], [ '_date' ], [ 'usernum' ], ],
2130 { columns => [ 'custnum' ],
2131 table => 'cust_main',
2133 { columns => [ 'classnum' ],
2134 table => 'cust_note_class',
2136 { columns => [ 'usernum' ],
2137 table => 'access_user',
2142 'cust_note_class' => {
2144 'classnum', 'serial', '', '', '', '',
2145 'classname', 'varchar', '', $char_d, '', '',
2146 'disabled', 'char', 'NULL', 1, '', '',
2148 'primary_key' => 'classnum',
2150 'index' => [ ['disabled'] ],
2153 'cust_category' => {
2155 'categorynum', 'serial', '', '', '', '',
2156 'categoryname', 'varchar', '', $char_d, '', '',
2157 'weight', 'int', 'NULL', '', '', '',
2158 'disabled', 'char', 'NULL', 1, '', '',
2160 'primary_key' => 'categorynum',
2162 'index' => [ ['disabled'] ],
2167 'classnum', 'serial', '', '', '', '',
2168 'classname', 'varchar', '', $char_d, '', '',
2169 'categorynum', 'int', 'NULL', '', '', '',
2170 'tax', 'char', 'NULL', 1, '', '',
2171 'disabled', 'char', 'NULL', 1, '', '',
2173 'primary_key' => 'classnum',
2175 'index' => [ ['disabled'] ],
2177 { columns => [ 'categorynum' ],
2178 table => 'cust_category',
2185 'custtagnum', 'serial', '', '', '', '',
2186 'custnum', 'int', '', '', '', '',
2187 'tagnum', 'int', '', '', '', '',
2189 'primary_key' => 'custtagnum',
2190 'unique' => [ [ 'custnum', 'tagnum' ] ],
2191 'index' => [ [ 'custnum' ] ],
2193 { columns => [ 'custnum' ],
2194 table => 'cust_main',
2196 { columns => [ 'tagnum' ],
2197 table => 'part_tag',
2204 'tagnum', 'serial', '', '', '', '',
2205 'tagname', 'varchar', '', $char_d, '', '',
2206 'tagdesc', 'varchar', 'NULL', $char_d, '', '',
2207 'tagcolor', 'varchar', 'NULL', 6, '', '',
2208 'by_default', 'char', 'NULL', 1, '', '',
2209 'disabled', 'char', 'NULL', 1, '', '',
2211 'primary_key' => 'tagnum',
2212 'unique' => [], #[ [ 'tagname' ] ], #?
2213 'index' => [ [ 'disabled' ] ],
2216 'cust_main_exemption' => {
2218 'exemptionnum', 'serial', '', '', '', '',
2219 'custnum', 'int', '', '', '', '',
2220 'taxname', 'varchar', '', $char_d, '', '',
2221 'exempt_number', 'varchar', 'NULL', $char_d, '', '',
2222 #start/end dates? for reporting?
2224 'primary_key' => 'exemptionnum',
2226 'index' => [ [ 'custnum' ] ],
2228 { columns => [ 'custnum' ],
2229 table => 'cust_main',
2234 'cust_tax_adjustment' => {
2236 'adjustmentnum', 'serial', '', '', '', '',
2237 'custnum', 'int', '', '', '', '',
2238 'taxname', 'varchar', '', $char_d, '', '',
2239 'amount', @money_type, '', '',
2240 'currency', 'char', 'NULL', 3, '', '',
2241 'comment', 'varchar', 'NULL', $char_d, '', '',
2242 'billpkgnum', 'int', 'NULL', '', '', '',
2243 #more? no cust_bill_pkg_tax_location?
2245 'primary_key' => 'adjustmentnum',
2247 'index' => [ [ 'custnum' ], [ 'billpkgnum' ] ],
2249 { columns => [ 'custnum' ],
2250 table => 'cust_main',
2252 { columns => [ 'billpkgnum' ],
2253 table => 'cust_bill_pkg',
2258 'cust_main_county' => { #district+city+county+state+country are checked
2259 #off the cust_main_county for validation and to
2260 #provide a tax rate.
2262 'taxnum', 'serial', '', '', '', '',
2263 'district', 'varchar', 'NULL', 20, '', '',
2264 'city', 'varchar', 'NULL', $char_d, '', '',
2265 'county', 'varchar', 'NULL', $char_d, '', '',
2266 'state', 'varchar', 'NULL', $char_d, '', '',
2267 'country', 'char', '', 2, '', '',
2268 'taxclass', 'varchar', 'NULL', $char_d, '', '',
2269 'exempt_amount', @money_type, '', '',
2270 'exempt_amount_currency', 'char', 'NULL', 3, '', '',
2271 'tax', 'real', '', '', '', '', #tax %
2272 'taxname', 'varchar', 'NULL', $char_d, '', '',
2273 'setuptax', 'char', 'NULL', 1, '', '', # Y = setup tax exempt
2274 'recurtax', 'char', 'NULL', 1, '', '', # Y = recur tax exempt
2275 'source', 'varchar', 'NULL', $char_d, '', '',
2277 'primary_key' => 'taxnum',
2279 # 'unique' => [ ['taxnum'], ['state', 'county'] ],
2280 'index' => [ [ 'district' ], [ 'city' ], [ 'county' ], [ 'state' ],
2288 'taxnum', 'serial', '', '', '', '',
2289 'geocode', 'varchar', 'NULL', $char_d, '', '',#cch provides 10 char
2290 'data_vendor', 'varchar', 'NULL', $char_d, '', '',#auto update source
2291 'location', 'varchar', 'NULL', $char_d, '', '',#provided by tax authority
2292 'taxclassnum', 'int', 'NULL', '', '', '',
2293 'effective_date', @date_type, '', '',
2294 'tax', @taxrate_type, '', '', # tax %
2295 'excessrate', @taxrate_typen, '', '', # second tax %
2296 'taxbase', @money_typen, '', '', # amount at first tax rate
2297 'taxmax', @money_typen, '', '', # maximum about at both rates
2298 'usetax', @taxrate_typen, '', '', # tax % when non-local
2299 'useexcessrate', @taxrate_typen, '', '', # second tax % when non-local
2300 'unittype', 'int', 'NULL', '', '', '', # for fee
2301 'fee', @taxrate_typen, '', '', # amount tax per unit
2302 'excessfee', @taxrate_typen, '', '', # second amount tax per unit
2303 'feebase', @taxrate_typen, '', '', # units taxed at first rate
2304 'feemax', @taxrate_typen, '', '', # maximum number of unit taxed
2305 'maxtype', 'int', 'NULL', '', '', '', # indicator of how thresholds accumulate
2306 'taxname', 'varchar', 'NULL', $char_d, '', '', # may appear on invoice
2307 'taxauth', 'int', 'NULL', '', '', '', # tax authority
2308 'basetype', 'int', 'NULL', '', '', '', # indicator of basis for tax
2309 'passtype', 'int', 'NULL', '', '', '', # indicator declaring how item should be shown
2310 'passflag', 'char', 'NULL', 1, '', '', # Y = required to list as line item, N = Prohibited
2311 'setuptax', 'char', 'NULL', 1, '', '', # Y = setup tax exempt
2312 'recurtax', 'char', 'NULL', 1, '', '', # Y = recur tax exempt
2313 'inoutcity', 'char', 'NULL', 1, '', '', # '', 'I', or 'O'
2314 'inoutlocal', 'char', 'NULL', 1, '', '', # '', 'I', or 'O'
2315 'manual', 'char', 'NULL', 1, '', '', # Y = manually edited
2316 'disabled', 'char', 'NULL', 1, '', '', # Y = tax disabled
2318 'primary_key' => 'taxnum',
2320 'index' => [ ['taxclassnum'], ['data_vendor', 'geocode'] ],
2322 { columns => [ 'taxclassnum' ],
2323 table => 'tax_class',
2328 'tax_rate_location' => {
2330 'taxratelocationnum', 'serial', '', '', '', '',
2331 'data_vendor', 'varchar', 'NULL', $char_d, '', '',
2332 'geocode', 'varchar', '', 20, '', '',
2333 'city', 'varchar', 'NULL', $char_d, '', '',
2334 'county', 'varchar', 'NULL', $char_d, '', '',
2335 'state', 'char', 'NULL', 2, '', '',
2336 'country', 'char', 'NULL', 2, '', '',
2337 'disabled', 'char', 'NULL', 1, '', '',
2339 'primary_key' => 'taxratelocationnum',
2341 'index' => [ [ 'data_vendor', 'geocode', 'disabled' ] ],
2344 'cust_tax_location' => {
2346 'custlocationnum', 'serial', '', '', '', '',
2347 'data_vendor', 'varchar', 'NULL', $char_d, '', '', # update source
2348 'city', 'varchar', 'NULL', $char_d, '', '',
2349 'postalcity', 'varchar', 'NULL', $char_d, '', '',
2350 'county', 'varchar', 'NULL', $char_d, '', '',
2351 'zip', 'char', 'NULL', 5, '', '',
2352 'state', 'char', '', 2, '', '',
2353 'country', 'char', 'NULL', 2, '', '', # should not actually be NULL, but cch...
2354 'ziphi', 'char', 'NULL', 10, '', '',
2355 'ziplo', 'char', 'NULL', 10, '', '',
2356 'plus4hi', 'char', 'NULL', 4, '', '',
2357 'plus4lo', 'char', 'NULL', 4, '', '',
2358 'default_location','char', 'NULL', 1, '', '', # Y = default for zip
2359 'cityflag', 'char', 'NULL', 1, '', '', # I(n)/O(out)/B(oth)/NULL
2360 'geocode', 'varchar', '', 20, '', '',
2362 'primary_key' => 'custlocationnum',
2364 'index' => [ [ 'zip', 'plus4lo', 'plus4hi', 'ziphi', 'ziplo' ] ],
2369 'taxclassnum', 'serial', '', '', '', '',
2370 'data_vendor', 'varchar', 'NULL', $char_d, '', '',
2371 'taxclass', 'varchar', '', $char_d, '', '',
2372 'description', 'varchar', '', 2*$char_d, '', '',
2374 'primary_key' => 'taxclassnum',
2375 'unique' => [ [ 'data_vendor', 'taxclass' ] ],
2381 'taxstatusnum', 'serial', '', '', '', '',
2382 'data_vendor', 'varchar', 'NULL', $char_d, '', '',
2383 'taxstatus', 'varchar', '', $char_d, '', '',
2384 'description', 'varchar', '', $char_d, '', '',
2386 'primary_key' => 'taxstatusnum',
2387 'unique' => [ [ 'data_vendor', 'taxstatus' ] ],
2391 'cust_pay_pending' => {
2393 'paypendingnum', 'serial', '', '', '', '',
2394 'custnum', 'int', 'NULL', '', '', '',
2395 'paid', @money_type, '', '',
2396 'currency', 'char', 'NULL', 3, '', '',
2397 '_date', @date_type, '', '',
2398 'payby', 'char', '', 4, '', '',
2399 'payinfo', 'varchar', 'NULL', 512, '', '',
2400 'paymask', 'varchar', 'NULL', $char_d, '', '',
2401 'paydate', 'varchar', 'NULL', 10, '', '',
2402 'recurring_billing', 'varchar', 'NULL', $char_d, '', '',
2403 'payunique', 'varchar', 'NULL', $char_d, '', '', #separate paybatch "unique" functions from current usage
2405 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances
2406 'status', 'varchar', '', $char_d, '', '',
2407 'session_id', 'varchar', 'NULL', 1024, '', '', # SHA-512-hex
2408 'statustext', 'text', 'NULL', '', '', '',
2409 'gatewaynum', 'int', 'NULL', '', '', '',
2410 #'cust_balance', @money_type, '', '',
2411 'paynum', 'int', 'NULL', '', '', '',
2412 'void_paynum', 'int', 'NULL', '', '', '',
2413 'jobnum', 'bigint', 'NULL', '', '', '',
2414 'invnum', 'int', 'NULL', '', '', '',
2415 'manual', 'char', 'NULL', 1, '', '',
2416 'discount_term','int', 'NULL', '', '', '',
2417 'failure_status','varchar','NULL', 16, '', '',
2419 'primary_key' => 'paypendingnum',
2420 'unique' => [ [ 'payunique' ] ],
2421 'index' => [ [ 'custnum' ], [ 'status' ],
2422 ['paynum'], ['void_paynum'], ['jobnum'], ['invnum'],
2425 { columns => [ 'custnum' ],
2426 table => 'cust_main',
2428 { columns => [ 'pkgnum' ],
2429 table => 'cust_pkg',
2431 { columns => [ 'gatewaynum' ],
2432 table => 'payment_gateway',
2434 { columns => [ 'paynum' ],
2435 table => 'cust_pay',
2437 { columns => [ 'void_paynum' ],
2438 table => 'cust_pay_void',
2439 references => [ 'paynum' ],
2441 { columns => [ 'jobnum' ],
2444 { columns => [ 'invnum' ],
2445 table => 'cust_bill',
2452 'paynum', 'serial', '', '', '', '',
2453 'custnum', 'int', '', '', '', '',
2454 '_date', @date_type, '', '',
2455 'paid', @money_type, '', '',
2456 'currency', 'char', 'NULL', 3, '', '',
2457 'otaker', 'varchar', 'NULL', 32, '', '',
2458 'usernum', 'int', 'NULL', '', '', '',
2459 'payby', 'char', '', 4, '', '',
2460 'payinfo', 'varchar', 'NULL', 512, '', '',
2461 'paycardtype', 'varchar', 'NULL', $char_d, '', '',
2462 'paymask', 'varchar', 'NULL', $char_d, '', '',
2463 'paydate', 'varchar', 'NULL', 10, '', '',
2464 'paybatch', 'varchar', 'NULL', $char_d, '', '',#for auditing purposes
2465 'payunique', 'varchar', 'NULL', $char_d, '', '',#separate paybatch "unique" functions from current usage
2466 'closed', 'char', 'NULL', 1, '', '',
2467 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances
2468 'no_auto_apply', 'char', 'NULL', 1, '', '',
2470 # cash/check deposit info fields
2471 'bank', 'varchar', 'NULL', $char_d, '', '',
2472 'depositor', 'varchar', 'NULL', $char_d, '', '',
2473 'account', 'varchar', 'NULL', 20, '', '',
2474 'teller', 'varchar', 'NULL', 20, '', '',
2476 'batchnum', 'int', 'NULL', '', '', '',#pay_batch foreign key
2478 # credit card/EFT fields (formerly in paybatch)
2479 'gatewaynum', 'int', 'NULL', '', '', '', # payment_gateway FK
2480 'processor', 'varchar', 'NULL', $char_d, '', '', # module name
2481 'auth', 'varchar', 'NULL', 16, '', '', # CC auth number
2482 'order_number','varchar', 'NULL', 256, '', '', # transaction number
2484 'primary_key' => 'paynum',
2485 #i guess not now, with cust_pay_pending, if we actually make it here, we _do_ want to record it# 'unique' => [ [ 'payunique' ] ],
2486 'index' => [ ['custnum'], ['paybatch'], ['payby'], ['_date'],
2490 { columns => [ 'custnum' ],
2491 table => 'cust_main',
2493 { columns => [ 'usernum' ],
2494 table => 'access_user',
2496 { columns => [ 'pkgnum' ],
2497 table => 'cust_pkg',
2499 { columns => [ 'batchnum' ],
2500 table => 'pay_batch',
2502 { columns => [ 'gatewaynum' ],
2503 table => 'payment_gateway',
2508 'cust_pay_void' => {
2510 'paynum', 'int', '', '', '', '',
2511 'custnum', 'int', '', '', '', '',
2512 '_date', @date_type, '', '',
2513 'paid', @money_type, '', '',
2514 'currency', 'char', 'NULL', 3, '', '',
2515 'otaker', 'varchar', 'NULL', 32, '', '',
2516 'usernum', 'int', 'NULL', '', '', '',
2517 'payby', 'char', '', 4, '', '',
2518 'payinfo', 'varchar', 'NULL', 512, '', '',
2519 'paycardtype', 'varchar', 'NULL', $char_d, '', '',
2520 'paymask', 'varchar', 'NULL', $char_d, '', '',
2522 'paybatch', 'varchar', 'NULL', $char_d, '', '', #for auditing purposes.
2523 'closed', 'char', 'NULL', 1, '', '',
2524 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances
2526 # cash/check deposit info fields
2527 'bank', 'varchar', 'NULL', $char_d, '', '',
2528 'depositor', 'varchar', 'NULL', $char_d, '', '',
2529 'account', 'varchar', 'NULL', 20, '', '',
2530 'teller', 'varchar', 'NULL', 20, '', '',
2531 'batchnum', 'int', 'NULL', '', '', '', #pay_batch foreign key
2533 # credit card/EFT fields (formerly in paybatch)
2534 'gatewaynum', 'int', 'NULL', '', '', '', # payment_gateway FK
2535 'processor', 'varchar', 'NULL', $char_d, '', '', # module name
2536 'auth', 'varchar', 'NULL', 16, '', '', # CC auth number
2537 'order_number','varchar', 'NULL', 256, '', '', # transaction number
2540 'void_date', @date_type, '', '',
2541 'reason', 'varchar', 'NULL', $char_d, '', '',
2542 'reasonnum', 'int', 'NULL', '', '', '',
2543 'void_usernum', 'int', 'NULL', '', '', '',
2545 'primary_key' => 'paynum',
2547 'index' => [ ['custnum'], ['usernum'], ['void_usernum'] ],
2549 { columns => [ 'custnum' ],
2550 table => 'cust_main',
2552 { columns => [ 'usernum' ],
2553 table => 'access_user',
2555 { columns => [ 'pkgnum' ],
2556 table => 'cust_pkg',
2558 { columns => [ 'batchnum' ],
2559 table => 'pay_batch',
2561 { columns => [ 'gatewaynum' ],
2562 table => 'payment_gateway',
2564 { columns => [ 'reasonnum' ],
2567 { columns => [ 'void_usernum' ],
2568 table => 'access_user',
2569 references => [ 'usernum' ],
2574 'cust_bill_pay' => {
2576 'billpaynum', 'serial', '', '', '', '',
2577 'invnum', 'int', '', '', '', '',
2578 'paynum', 'int', '', '', '', '',
2579 'amount', @money_type, '', '',
2580 '_date', @date_type, '', '',
2581 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances
2583 'primary_key' => 'billpaynum',
2585 'index' => [ [ 'paynum' ], [ 'invnum' ] ],
2587 { columns => [ 'invnum' ],
2588 table => 'cust_bill',
2590 { columns => [ 'paynum' ],
2591 table => 'cust_pay',
2593 { columns => [ 'pkgnum' ],
2594 table => 'cust_pkg',
2599 'cust_bill_pay_batch' => {
2601 'billpaynum', 'serial', '', '', '', '',
2602 'invnum', 'int', '', '', '', '',
2603 'paybatchnum', 'int', '', '', '', '',
2604 'amount', @money_type, '', '',
2605 '_date', @date_type, '', '',
2607 'primary_key' => 'billpaynum',
2609 'index' => [ [ 'paybatchnum' ], [ 'invnum' ] ],
2611 { columns => [ 'invnum' ],
2612 table => 'cust_bill',
2614 { columns => [ 'paybatchnum' ],
2615 table => 'cust_pay_batch',
2620 'cust_bill_pay_pkg' => {
2622 'billpaypkgnum', 'serial', '', '', '', '',
2623 'billpaynum', 'int', '', '', '', '',
2624 'billpkgnum', 'int', '', '', '', '',
2625 'billpkgtaxlocationnum', 'int', 'NULL', '', '', '',
2626 'billpkgtaxratelocationnum', 'int', 'NULL', '', '', '',
2627 'amount', @money_type, '', '',
2628 'setuprecur', 'varchar', '', $char_d, '', '',
2629 'sdate', @date_type, '', '',
2630 'edate', @date_type, '', '',
2632 'primary_key' => 'billpaypkgnum',
2634 'index' => [ [ 'billpaynum' ], [ 'billpkgnum' ], ],
2636 { columns => [ 'billpaynum' ],
2637 table => 'cust_bill_pay',
2639 { columns => [ 'billpkgnum' ],
2640 table => 'cust_bill_pkg',
2642 { columns => [ 'billpkgtaxlocationnum' ],
2643 table => 'cust_bill_pkg_tax_location',
2645 { columns => [ 'billpkgtaxratelocationnum' ],
2646 table => 'cust_bill_pkg_tax_rate_location',
2651 'pay_batch' => { #batches of payments to an external processor
2653 'batchnum', 'serial', '', '', '', '',
2654 'agentnum', 'int', 'NULL', '', '', '',
2655 'payby', 'char', '', 4, '', '', # CARD/CHEK
2656 'status', 'char', 'NULL', 1, '', '',
2657 'download', @date_type, '', '',
2658 'upload', @date_type, '', '',
2659 'title', 'varchar', 'NULL',255, '', '',
2660 'processor_id', 'varchar', 'NULL',255, '', '',
2662 'primary_key' => 'batchnum',
2666 { columns => [ 'agentnum' ],
2672 'cust_pay_batch' => { #list of customers in current CARD/CHEK batch
2674 'paybatchnum', 'serial', '', '', '', '',
2675 'batchnum', 'int', '', '', '', '',
2676 'invnum', 'int', '', '', '', '',
2677 'custnum', 'int', '', '', '', '',
2678 'last', 'varchar', '', $char_d, '', '',
2679 'first', 'varchar', '', $char_d, '', '',
2680 'address1', 'varchar', '', $char_d, '', '',
2681 'address2', 'varchar', 'NULL', $char_d, '', '',
2682 'city', 'varchar', 'NULL', $char_d, '', '',
2683 'state', 'varchar', 'NULL', $char_d, '', '',
2684 'zip', 'varchar', 'NULL', 10, '', '',
2685 'country', 'char', '', 2, '', '',
2686 'payby', 'char', '', 4, '', '',
2687 'payinfo', 'varchar', 'NULL', 512, '', '',
2688 #'exp', @date_type, '', '',
2689 'exp', 'varchar', 'NULL', 11, '', '',
2690 'payname', 'varchar', 'NULL', $char_d, '', '',
2691 'paytype', 'varchar', 'NULL', $char_d, '', '',
2692 'amount', @money_type, '', '',
2693 'currency', 'char', 'NULL', 3, '', '',
2694 'status', 'varchar', 'NULL', $char_d, '', '',
2695 'failure_status','varchar', 'NULL', 16, '', '',
2696 'error_message', 'varchar', 'NULL', $char_d, '', '',
2698 'primary_key' => 'paybatchnum',
2700 'index' => [ ['batchnum'], ['invnum'], ['custnum'] ],
2702 { columns => [ 'batchnum' ],
2703 table => 'pay_batch',
2705 #{ columns => [ 'invnum' ],
2706 # table => 'cust_bill',
2708 { columns => [ 'custnum' ],
2709 table => 'cust_main',
2716 'formkey', 'varchar', '', 255, '', '',
2717 'formvalue', 'text', 'NULL', '', '', '',
2719 'primary_key' => 'formkey',
2726 'pkgnum', 'serial', '', '', '', '',
2727 'custnum', 'int', '', '', '', '',
2728 'pkgpart', 'int', '', '', '', '',
2729 'pkgbatch', 'varchar', 'NULL', $char_d, '', '',
2730 'contactnum', 'int', 'NULL', '', '', '',
2731 'locationnum', 'int', 'NULL', '', '', '',
2732 'otaker', 'varchar', 'NULL', 32, '', '',
2733 'usernum', 'int', 'NULL', '', '', '',
2734 'salesnum', 'int', 'NULL', '', '', '',
2735 'order_date', @date_type, '', '',
2736 'start_date', @date_type, '', '',
2737 'setup', @date_type, '', '',
2738 'bill', @date_type, '', '',
2739 'last_bill', @date_type, '', '',
2740 'susp', @date_type, '', '',
2741 'adjourn', @date_type, '', '',
2742 'resume', @date_type, '', '',
2743 'cancel', @date_type, '', '',
2744 'uncancel', @date_type, '', '',
2745 'uncancel_pkgnum', 'int', 'NULL', '', '', '',
2746 'expire', @date_type, '', '',
2747 'contract_end', @date_type, '', '',
2748 'dundate', @date_type, '', '',
2749 'change_date', @date_type, '', '',
2750 'change_pkgnum', 'int', 'NULL', '', '', '',
2751 'change_pkgpart', 'int', 'NULL', '', '', '',
2752 'change_locationnum', 'int', 'NULL', '', '', '',
2753 'change_custnum', 'int', 'NULL', '', '', '',
2754 'main_pkgnum', 'int', 'NULL', '', '', '',
2755 'pkglinknum', 'int', 'NULL', '', '', '',
2756 'no_auto', 'char', 'NULL', 1, '', '',
2757 'quantity', 'int', 'NULL', '', '', '',
2758 'agent_pkgid', 'varchar', 'NULL', $char_d, '', '',
2759 'waive_setup', 'char', 'NULL', 1, '', '',
2760 'recur_show_zero', 'char', 'NULL', 1, '', '',
2761 'setup_show_zero', 'char', 'NULL', 1, '', '',
2762 'change_to_pkgnum', 'int', 'NULL', '', '', '',
2763 'separate_bill', 'char', 'NULL', 1, '', '',
2765 'primary_key' => 'pkgnum',
2767 'index' => [ ['custnum'], ['pkgpart'], ['pkgbatch'],
2768 ['locationnum'], ['usernum'], ['agent_pkgid'],
2769 ['order_date'], [ 'start_date' ], ['setup'], ['bill'],
2770 ['last_bill'], ['susp'], ['adjourn'], ['resume'],
2771 ['cancel'], ['expire'], ['contract_end'],
2776 #['uncancel_pkgnum'],
2777 #['change_pkgnum'], ['change_locationnum'],
2778 #['change_custnum'],
2780 #['pkglinknum'], ['change_to_pkgnum'],
2783 { columns => [ 'custnum' ],
2784 table => 'cust_main',
2786 { columns => [ 'pkgpart' ],
2787 table => 'part_pkg',
2789 { columns => [ 'contactnum' ],
2792 { columns => [ 'locationnum' ],
2793 table => 'cust_location',
2795 { columns => [ 'usernum' ],
2796 table => 'access_user',
2798 { columns => [ 'salesnum' ],
2801 { columns => [ 'uncancel_pkgnum' ],
2802 table => 'cust_pkg',
2803 references => [ 'pkgnum' ],
2805 { columns => [ 'change_pkgnum' ],
2806 table => 'cust_pkg',
2807 references => [ 'pkgnum' ],
2809 { columns => [ 'change_pkgpart' ],
2810 table => 'part_pkg',
2811 references => [ 'pkgpart' ],
2813 { columns => [ 'change_locationnum' ],
2814 table => 'cust_location',
2815 references => [ 'locationnum' ],
2817 { columns => [ 'change_custnum' ],
2818 table => 'cust_main',
2819 references => [ 'custnum' ],
2821 { columns => [ 'main_pkgnum' ],
2822 table => 'cust_pkg',
2823 references => [ 'pkgnum' ],
2825 { columns => [ 'pkglinknum' ],
2826 table => 'part_pkg_link',
2828 { columns => [ 'change_to_pkgnum' ],
2829 table => 'cust_pkg',
2830 references => [ 'pkgnum' ],
2835 'cust_pkg_option' => {
2837 'optionnum', 'serial', '', '', '', '',
2838 'pkgnum', 'int', '', '', '', '',
2839 'optionname', 'varchar', '', $char_d, '', '',
2840 'optionvalue', 'text', 'NULL', '', '', '',
2842 'primary_key' => 'optionnum',
2844 'index' => [ [ 'pkgnum' ], [ 'optionname' ] ],
2846 { columns => [ 'pkgnum' ],
2847 table => 'cust_pkg',
2852 'cust_pkg_detail' => {
2854 'pkgdetailnum', 'serial', '', '', '', '',
2855 'pkgnum', 'int', '', '', '', '',
2856 'detail', 'varchar', '', 2*$char_d, '', '',
2857 'detailtype', 'char', '', 1, '', '', #"I"nvoice or "C"omment
2858 'weight', 'int', '', '', '', '',
2860 'primary_key' => 'pkgdetailnum',
2862 'index' => [ [ 'pkgnum', 'detailtype' ] ],
2864 { columns => [ 'pkgnum' ],
2865 table => 'cust_pkg',
2870 'cust_pkg_reason' => {
2872 'num', 'serial', '', '', '', '',
2873 'pkgnum', 'int', '', '', '', '',
2874 'reasonnum','int', '', '', '', '',
2875 'action', 'char', 'NULL', 1, '', '', #should not be nullable
2876 'otaker', 'varchar', 'NULL', 32, '', '',
2877 'usernum', 'int', 'NULL', '', '', '',
2878 'date', @date_type, '', '',
2880 'primary_key' => 'num',
2882 'index' => [ ['pkgnum'], ['reasonnum'], ['action'],&n