X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2FSchema.pm;h=ecfb0e4d270264b2e8166d43b7d6b84ee3b9ee92;hp=a4c12aaff531fb6fcaa4a20d303e8942b8acdb76;hb=e3a2ca0a7ef6e0931283136321ba8c1494b37c19;hpb=7bbe939d139be7edd77378cfc9a2c3ec6287749d

diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm
index a4c12aaff..ecfb0e4d2 100644
--- a/FS/FS/Schema.pm
+++ b/FS/FS/Schema.pm
@@ -184,11 +184,15 @@ sub dbdef_dist {
   #  ));
   #}
 
+  my $tables_hashref_torrus = tables_hashref_torrus();
+
   #create history tables (false laziness w/create-history-tables)
   foreach my $table (
-    grep { ! /^clientapi_session/ }
-    grep { ! /^h_/ }
-    $dbdef->tables
+    grep {    ! /^clientapi_session/
+           && ! /^h_/
+           && ! $tables_hashref_torrus->{$_}
+         }
+      $dbdef->tables
   ) {
     my $tableobj = $dbdef->table($table)
       or die "unknown table $table";
@@ -325,6 +329,101 @@ sub dbdef_dist {
 
 }
 
+#torrus tables http://torrus.org/reporting_setup.pod.html#create_sql_tables
+sub tables_hashref_torrus {
+
+  return {
+
+    # Collector export table. It usually grows at several megabytes
+    # per month, and is updated every 5 minutes
+    'srvexport' => {
+      'columns' => [
+        'id',         'serial', '', '', '', '',
+        'srv_date',     'date', '', '', '', '',#date and time of the data sample
+        'srv_time',     'time', '', '', '', '',
+        'serviceid', 'varchar', '', 64, '', '',#unique service ID per counter
+        'value',     'double precision', '', '', '', '',#collected rate or gauge value
+        'intvl', 'int', '', '', '', '', # collection interval - for counter volume calculation
+      ],
+      'primary_key' => 'id',
+      'unique' => [],
+      'index'  => [ ['srv_date'], ['srv_date', 'srv_time'], ['serviceid'], ],
+    },
+
+    #Tables for (currently monthly only) report contents.
+    #These are updated usually once per month, and read at the moment of
+    #rendering the report output (HTML now, PDF or XML or Excel or whatever
+    #in the future)
+
+    #DBIx::Sequence backend, theplatform-independent inplementation
+    #of sequences
+    'dbix_sequence_state' => {
+      'columns' => [
+        'id',       'serial', '', '', '', '',
+        'dataset', 'varchar', '', 50, '', '',
+        'state_id',    'int', '', '', '', '',
+      ],
+      'primary_key' => 'id',
+      #CONSTRAINT pk_dbix_sequence PRIMARY KEY (dataset, state_id)
+      'unique' => [ [ 'dataset', 'state_id' ], ],
+      'index'  => [],
+    },
+
+    'dbix_sequence_release' => {
+      'columns' => [
+        'id',       'serial', '', '', '', '',
+        'dataset', 'varchar', '', 50, '', '',
+        'released_id', 'int', '', '', '', '',
+      ],
+      'primary_key' => 'id',
+      #CONSTRAINT pk_dbi_release PRIMARY KEY (dataset, released_id)
+      'unique' => [ [ 'dataset', 'released_id', ] ],
+      'index'  => [],
+    },
+
+    #Each report is characterized by name, date and time.
+    #Monthly reports are automatically assigned 00:00 of the 1st day
+    #in the month. The report contains fields for every service ID
+    #defined across all datasource trees.
+    'reports' => {
+      'columns' => [
+        'id',          'serial', '', '', '', '',
+        'rep_date',      'date', '', '', '', '',#Start date of the report
+        'rep_time',      'time', '', '', '', '',#Start time of the report
+        'reportname', 'varchar', '', 64, '', '',#Report name, such as
+                                                # MonthlyUsage
+        'iscomplete',     'int', '', '', '', '',#0 when the report is in
+                                                # progress, 1 when it is ready
+      ],
+      'primary_key' => 'id',
+      'unique' => [ [ qw(rep_date rep_time reportname) ] ],
+      'index'  => [ [ 'rep_date' ] ],
+    },
+
+    #Each report contains fields. For each service ID,
+    #the report may contain several fields for various statistics.
+    #Each field contains information about the units of the value it
+    #contains
+    'reportfields' => {
+      'columns' => [
+        'id',              'serial',     '', '',    '', '',
+        'rep_id',             'int', 'NULL', '',    '', '',
+        'name',           'varchar',     '', 64,    '', '',#name of the field,
+                                                           # such as AVG or MAX
+        'serviceid',      'varchar',     '', 64,    '', '',#service ID
+        'value', 'double precision',     '', '',    '', '',#Numeric value
+        'units',          'varchar',     '', 64, \"''", '',#Units, such as bytes
+                                                           # or Mbps
+      ],
+      'primary_key', => 'id',
+      'unique' => [ [ qw(rep_id name serviceid) ] ],
+      'index'  => [],
+    },
+
+  };
+
+}
+
 sub tables_hashref {
 
   my $char_d = 80; #default maxlength for text fields
@@ -352,8 +451,8 @@ sub tables_hashref {
         'invoice_template', 'varchar', 'NULL', $char_d, '', '',
         'agent_custnum',        'int', 'NULL',      '', '', '',
         'disabled',            'char', 'NULL',       1, '', '', 
-        'username',         'varchar', 'NULL', $char_d, '', '', #deprecated
-        '_password',        'varchar', 'NULL', $char_d, '', '', #deprecated
+        'username',         'varchar', 'NULL', $char_d, '', '',
+        '_password',        'varchar', 'NULL', $char_d, '', '',
         'freq',              'int', 'NULL', '', '', '', #deprecated (never used)
         'prog',                     @perl_type, '', '', #deprecated (never used)
       ],
@@ -906,6 +1005,10 @@ sub tables_hashref {
         'zip',         'varchar', 'NULL',      10, '', '', 
         'country',        'char',     '',       2, '', '', 
         'geocode',     'varchar', 'NULL',      20, '', '',
+        'location_type',     'varchar', 'NULL',      20, '', '',
+        'location_number',     'varchar', 'NULL',      20, '', '',
+        'location_kind',     'char', 'NULL',      1, '', '',
+        'disabled',      'char', 'NULL',   1, '', '', 
       ],
       'primary_key' => 'locationnum',
       'unique'      => [],
@@ -929,6 +1032,7 @@ sub tables_hashref {
       'columns' => [
         'notenum',  'serial',  '',     '', '', '', 
         'custnum',  'int',  '',     '', '', '', 
+        'classnum',      'int',     'NULL', '', '', '', 
         '_date',    @date_type, '', '', 
         'otaker',   'varchar', 'NULL',    32, '', '', 
         'usernum',   'int', 'NULL', '', '', '',
@@ -938,6 +1042,17 @@ sub tables_hashref {
       'unique' => [],
       'index' => [ [ 'custnum' ], [ '_date' ], [ 'usernum' ], ],
     },
+    
+    'cust_note_class' => {
+      'columns' => [
+        'classnum',    'serial',   '',      '', '', '', 
+        'classname',   'varchar',  '', $char_d, '', '', 
+        'disabled',    'char', 'NULL',       1, '', '', 
+      ],
+      'primary_key' => 'classnum',
+      'unique' => [],
+      'index' => [ ['disabled'] ],
+    },
 
     'cust_category' => {
       'columns' => [
@@ -1194,10 +1309,11 @@ sub tables_hashref {
         'reason',    'varchar',   'NULL', $char_d, '', '', 
         'otaker',   'varchar', 'NULL', 32, '', '', 
         'usernum',   'int', 'NULL', '', '', '',
+        'void_usernum',   'int', 'NULL', '', '', '',
       ],
       'primary_key' => 'paynum',
       'unique' => [],
-      'index' => [ [ 'custnum' ], [ 'usernum' ], ],
+      'index' => [ [ 'custnum' ], [ 'usernum' ], [ 'void_usernum' ] ],
     },
 
     'cust_bill_pay' => {
@@ -1295,6 +1411,7 @@ sub tables_hashref {
         'locationnum',         'int', 'NULL', '', '', '',
         'otaker',          'varchar', 'NULL', 32, '', '', 
         'usernum',             'int', 'NULL', '', '', '',
+        'order_date',     @date_type,             '', '', 
         'start_date',     @date_type,             '', '', 
         'setup',          @date_type,             '', '', 
         'bill',           @date_type,             '', '', 
@@ -1311,14 +1428,15 @@ sub tables_hashref {
         'manual_flag',        'char', 'NULL',  1, '', '', 
         'no_auto',            'char', 'NULL',  1, '', '', 
         'quantity',            'int', 'NULL', '', '', '',
+        'agent_pkgid',         'int', 'NULL', '', '', '',
       ],
       'primary_key' => 'pkgnum',
       'unique' => [],
       'index' => [ ['custnum'], ['pkgpart'], [ 'pkgbatch' ], [ 'locationnum' ],
-                   [ 'usernum' ],
+                   [ 'usernum' ], [ 'agent_pkgid' ],
                    [ 'start_date' ], ['setup'], ['last_bill'], ['bill'],
                    ['susp'], ['adjourn'], ['expire'], ['cancel'],
-                   ['change_date'],
+                   ['change_date'], ['order_date'],
                  ],
     },
 
@@ -1600,6 +1718,7 @@ sub tables_hashref {
         'svcpart',    'int',    '',   '', '', '', 
         'quantity',   'int',    '',   '', '', '', 
         'primary_svc','char', 'NULL',  1, '', '', 
+        'hidden',     'char', 'NULL',  1, '', '',
       ],
       'primary_key' => 'pkgsvcnum',
       'unique' => [ ['pkgpart', 'svcpart'] ],
@@ -1672,6 +1791,35 @@ sub tables_hashref {
       'unique' => [],
       'index' => [ [ 'npa', 'nxx' ], [ 'popnum' ] ],
     },
+    
+    'qual' => {
+      'columns' => [
+        'qualnum',  'serial',     '',     '', '', '', 
+        'custnum',    'int',     'NULL',     '', '', '',
+        'prospectnum',    'int',     'NULL',     '', '', '',
+        'locationnum',    'int',     'NULL',     '', '', '',
+	'phonenum',     'varchar', 'NULL',       24, '', '',
+        'exportnum',      'int', 'NULL', '', '', '', 
+        'vendor_qual_id',      'varchar', 'NULL', $char_d, '', '', 
+        'status',      'char', '', 1, '', '', 
+      ],
+      'primary_key' => 'qualnum',
+      'unique' => [],
+      'index' => [ [ 'locationnum' ], ['custnum'], ['prospectnum'],
+		    ['phonenum'], ['vendor_qual_id'] ],
+    },
+    
+    'qual_option' => {
+      'columns' => [
+        'optionnum', 'serial', '', '', '', '', 
+        'qualnum',  'int',     '',     '', '', '', 
+        'optionname', 'varchar', '', $char_d, '', '', 
+        'optionvalue', 'text', 'NULL', '', '', '', 
+      ],
+      'primary_key' => 'optionnum',
+      'unique' => [],
+      'index' => [],
+    },
 
     'svc_acct' => {
       'columns' => [
@@ -1700,6 +1848,8 @@ sub tables_hashref {
         'pbxsvc',    'int', 'NULL', '', '', '',
         'last_login',  @date_type, '', '', 
         'last_logout', @date_type, '', '', 
+        #cardfortress field(s)
+        'cf_privatekey',      'text', 'NULL',      '', '', '',
         #communigate pro fields (quota = MaxAccountSize)
         'cgp_aliases',     'varchar', 'NULL',     255, '', '',
         #settings
@@ -1769,11 +1919,14 @@ sub tables_hashref {
 	'setup_date',      @date_type, '', '',
 	'renewal_interval', 'int', 'NULL',       '', '', '',
 	'expiration_date', @date_type, '', '',
+	'au_registrant_name',       'varchar', 'NULL',  $char_d, '', '',
+	'au_eligibility_type',      'varchar', 'NULL',  $char_d, '', '',
         #communigate pro fields (quota = MaxAccountSize)
         'max_accounts',     'int', 'NULL',       '', '', '',
         'trailer',         'text', 'NULL',       '', '', '',
         'cgp_aliases',  'varchar', 'NULL',      255, '', '',
         'cgp_accessmodes','varchar','NULL',     255, '', '', #DomainAccessModes
+        'cgp_certificatetype','varchar','NULL', $char_d, '', '',
         #settings
         'acct_def_password_selfchange',   'char', 'NULL',       1,  '', '', 
         'acct_def_password_recover',      'char', 'NULL',       1, 'Y', '', 
@@ -1800,6 +1953,53 @@ sub tables_hashref {
       'unique' => [ ],
       'index' => [ ['domain'] ],
     },
+    
+    'svc_dsl' => {
+      'columns' => [
+        'svcnum',           'int',    '',        '', '', '',
+	'pushed',     'int', 'NULL',       '', '', '',
+	'desired_due_date',     'int', 'NULL',       '', '', '',
+	'due_date',     'int', 'NULL',       '', '', '',
+        'vendor_order_id',              'varchar', 'NULL', $char_d,  '', '',
+        'vendor_qual_id',              'varchar', 'NULL', $char_d,  '', '',
+        'vendor_order_type',   'varchar', 'NULL', $char_d,  '', '',
+        'vendor_order_status',   'varchar', 'NULL', $char_d,  '', '',
+        'first',              'varchar', 'NULL', $char_d,  '', '',
+        'last',              'varchar', 'NULL', $char_d,  '', '',
+        'company',              'varchar', 'NULL', $char_d,  '', '',
+	'phonenum',     'varchar', 'NULL',       24, '', '',
+        'loop_type',   'char', 'NULL',       1,  '', '', 
+        'local_voice_provider', 'varchar', 'NULL', $char_d,  '', '',
+        'circuitnum',              'varchar', 'NULL', $char_d,  '', '',
+        'rate_band',              'varchar', 'NULL', $char_d,  '', '',
+	'vpi',     'int', 'NULL',       '', '', '',
+	'vci',     'int', 'NULL',       '', '', '',
+        'isp_chg',   'char', 'NULL',       1,  '', '', 
+        'isp_prev',              'varchar', 'NULL', $char_d,  '', '',
+        'username',              'varchar', 'NULL', $char_d,  '', '',
+        'password',              'varchar', 'NULL', $char_d,  '', '',
+        'staticips',             'text', 'NULL', '',  '', '',
+        'monitored',   	'char', 'NULL',       1,  '', '', 
+	'last_pull',     'int', 'NULL',       '', '', '',
+      ],
+      'primary_key' => 'svcnum',
+      'unique' => [ ],
+      'index' => [ ['phonenum'], ['vendor_order_id'] ],
+    },
+    
+    'dsl_note' => {
+      'columns' => [
+        'notenum',           'serial',    '',        '', '', '',
+	'svcnum',     'int', '',       '', '', '',
+        'author',     'varchar', 'NULL', $char_d,  '', '',
+        'priority',   'char', 'NULL',       1,  '', '', 
+	'_date',     'int', 'NULL',       '', '', '',
+	'note',     'text', '',       '', '', '',
+      ],
+      'primary_key' => 'notenum',
+      'unique' => [ ],
+      'index' => [ ['svcnum'] ],
+    },
 
     'domain_record' => {
       'columns' => [
@@ -2023,6 +2223,7 @@ sub tables_hashref {
     'part_export' => {
       'columns' => [
         'exportnum', 'serial', '', '', '', '', 
+        'exportname', 'varchar', 'NULL', $char_d, '', '',
         'machine', 'varchar', '', $char_d, '', '', 
         'exporttype', 'varchar', '', $char_d, '', '', 
         'nodomain',     'char', 'NULL', 1, '', '', 
@@ -2153,6 +2354,7 @@ sub tables_hashref {
         'altitude', 'decimal', 'NULL', '', '', '', 
         'vlan_profile', 'varchar', 'NULL', $char_d, '', '', 
         'performance_profile', 'varchar', 'NULL', $char_d, '', '',
+        'plan_id', 'varchar', 'NULL', $char_d, '', '',
       ],
       'primary_key' => 'svcnum',
       'unique'      => [ [ 'mac_addr' ] ],
@@ -2242,6 +2444,18 @@ sub tables_hashref {
       'index'       => [ [ 'pkgpart' ], [ 'optionname' ] ],
     },
 
+    'part_pkg_vendor' => {
+      'columns' => [
+        'num', 'serial', '', '', '', '', 
+        'pkgpart', 'int', '', '', '', '', 
+        'exportnum', 'int', '', '', '', '', 
+        'vendor_pkg_id', 'varchar', '', $char_d, '', '', 
+      ],
+      'primary_key' => 'num',
+      'unique' => [ [ 'pkgpart', 'exportnum' ] ],
+      'index'       => [ [ 'pkgpart' ] ],
+    },
+    
     'part_pkg_report_option' => {
       'columns' => [
         'num',      'serial',   '',      '', '', '', 
@@ -2276,6 +2490,7 @@ sub tables_hashref {
         'sec_granularity', 'int',     '',     '', '', '', 
         'ratetimenum',     'int', 'NULL',     '', '', '',
         'classnum',        'int', 'NULL',     '', '', '', 
+        'cdrtypenum',      'int', 'NULL',     '', '', '',
       ],
       'primary_key' => 'ratedetailnum',
       'unique'      => [ [ 'ratenum', 'orig_regionnum', 'dest_regionnum' ] ],
@@ -2765,6 +2980,16 @@ sub tables_hashref {
         'pbxsvc',           'int', 'NULL',      '', '', '',
         'domsvc',           'int', 'NULL',      '', '', '', 
         'locationnum',      'int', 'NULL', '', '', '',
+        'forwarddst',     'varchar',     'NULL',      15, '', '', 
+        'email',           'varchar', 'NULL',  255, '', '', 
+        'lnp_status',   'varchar', 'NULL', $char_d, '', '',
+        'portable',   	'char', 'NULL',       1,  '', '', 
+        'lrn',     'char',     'NULL',      10, '', '', 
+	'lnp_desired_due_date',     'int', 'NULL',       '', '', '',
+	'lnp_due_date',     'int', 'NULL',       '', '', '',
+        'lnp_other_provider', 'varchar', 'NULL', $char_d,  '', '',
+        'lnp_other_provider_account', 'varchar', 'NULL', $char_d,  '', '',
+        'lnp_reject_reason', 'varchar', 'NULL', $char_d,  '', '',
       ],
       'primary_key' => 'svcnum',
       'unique' => [],
@@ -2789,7 +3014,7 @@ sub tables_hashref {
       'columns' => [
         'devicepart', 'serial',  '',      '', '', '',
         'devicename', 'varchar', '', $char_d, '', '',
-        #'classnum', #tie to an inventory class?
+        'inventory_classnum', 'int', 'NULL', '', '', '',
       ],
       'primary_key' => 'devicepart',
       'unique' => [ [ 'devicename' ] ], #?
@@ -2806,6 +3031,8 @@ sub tables_hashref {
         'nxx',         'char',    'NULL',       3, '', '', 
         'station',     'char',    'NULL',       4, '', '',
         'name',        'varchar', 'NULL', $char_d, '', '',
+        'rate_center_abbrev', 'varchar', 'NULL', $char_d, '', '',
+        'ordernum',      'int',     'NULL',      '', '', '',
         'svcnum',      'int',     'NULL',      '', '', '',
         'availbatch', 'varchar',  'NULL', $char_d, '', '',
       ],
@@ -2819,6 +3046,45 @@ sub tables_hashref {
                     [ 'availbatch' ],
                   ],
     },
+    
+    'lata' => {
+      'columns' => [
+        'latanum',    'int',      '',      '', '', '', 
+        'description',   'varchar',    '',      $char_d, '', '', 
+      ],
+      'primary_key' => 'latanum',
+      'unique' => [],
+      'index'  => [],
+    },
+    
+    'did_vendor' => {
+      'columns' => [
+        'vendornum',    'serial',      '',      '', '', '', 
+        'vendorname',   'varchar',        '',     $char_d, '', '', 
+      ],
+      'primary_key' => 'vendornum',
+      'unique' => [],
+      'index'  => [],
+    },
+    
+    'did_order' => {
+      'columns' => [
+        'ordernum',    'serial',      '',      '', '', '', 
+        'vendornum',   'int',       '',      '', '', '', 
+        'vendor_order_id',   'varchar',  '',   $char_d, '', '', 
+        'msa',        'varchar', 'NULL', $char_d, '', '',
+        'latanum',      'int',     'NULL',      '', '', '',
+        'rate_center',        'varchar', 'NULL', $char_d, '', '',
+        'state',       'char',    'NULL',       2, '', '', 
+        'quantity',      'int',     '',      '', '', '',
+        'submitted',      'int',     '',      '', '', '',
+        'confirmed',      'int',     'NULL',      '', '', '',
+        'received',      'int',     'NULL',      '', '', '',
+      ],
+      'primary_key' => 'ordernum',
+      'unique' => [ [ 'vendornum', 'vendor_order_id' ] ],
+      'index'  => [],
+    },
 
     'reason_type' => {
       'columns' => [
@@ -2969,6 +3235,73 @@ sub tables_hashref {
       'index'       => [ ['agentnum'], ]
     },
 
+    'svc_cert' => {
+      'columns' => [
+        'svcnum',                'int',     '',      '', '', '', 
+        'recnum',                'int', 'NULL',      '', '', '',
+        'privatekey',           'text', 'NULL',      '', '', '',
+        'csr',                  'text', 'NULL',      '', '', '',
+        'certificate',          'text', 'NULL',      '', '', '',
+        'cacert',               'text', 'NULL',      '', '', '',
+        'common_name',       'varchar', 'NULL', $char_d, '', '',
+        'organization',      'varchar', 'NULL', $char_d, '', '',
+        'organization_unit', 'varchar', 'NULL', $char_d, '', '',
+        'city',              'varchar', 'NULL', $char_d, '', '',
+        'state',             'varchar', 'NULL', $char_d, '', '',
+        'country',              'char', 'NULL',       2, '', '',
+        'cert_contact',      'varchar', 'NULL', $char_d, '', '',
+      ],
+      'primary_key' => 'svcnum',
+      'unique' => [],
+      'index'  => [], #recnum
+    },
+
+    'svc_port' => {
+      'columns' => [
+        'svcnum',                'int',     '',      '', '', '', 
+        'serviceid', 'varchar', '', 64, '', '', #srvexport / reportfields
+      ],
+      'primary_key' => 'svcnum',
+      'unique' => [],
+      'index'  => [], #recnum
+    },
+
+    'areacode'  => {
+      'columns' => [
+        'code',        'char',        '',       3, '', '', 
+        'country',     'char',    'NULL',       2, '', '',
+        'state',       'char',    'NULL',       2, '', '', 
+        'description','varchar',  'NULL',     255, '', '',
+      ], 
+      'primary_key' => 'code',
+      'unique' => [],
+      'index'  => [],
+    },
+
+    %{ tables_hashref_torrus() },
+
+    # tables of ours for doing torrus virtual port combining
+    'torrus_srvderive' => {
+      'columns' => [
+        'derivenum',     'serial',     '', '', '', '',
+        'serviceid',    'varchar',     '', 64, '', '', #srvexport / reportfields
+        'last_srv_date',   'date', 'NULL', '', '', '',
+      ],
+      'primary_key' => 'derivenum',
+      'unique' => [ ['serviceid'] ],
+      'index'  => [],
+    },
+
+    'torrus_srvderive_component' => {
+      'columns' => [
+        'componentnum', 'serial', '', '', '', '',
+        'derivenum',       'int', '', '', '', '',
+        'serviceid',   'varchar', '', 64, '', '', #srvexport / reportfields
+      ],
+      'primary_key' => 'componentnum',
+      'unique'      => [ [ 'derivenum', 'serviceid' ], ],
+      'index'       => [ [ 'derivenum', ], ],
+    },
 
 
     # name type nullability length default local