X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2FSchema.pm;h=fc22d6c4fc772633da0f28fb3d046860d1829056;hp=bacef72825b62f2cf14b5f18506939085b59cbbc;hb=9ad0cdfb307e346add9e3496cf3cbdc1eee4e7d8;hpb=87762d33fc08d6a063bc16edab550bdb42f34c9f

diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm
index bacef7282..fc22d6c4f 100644
--- a/FS/FS/Schema.pm
+++ b/FS/FS/Schema.pm
@@ -495,8 +495,44 @@ sub tables_hashref {
 
   my $username_len = 64; #usernamemax config file
 
-    # name type nullability length default local
+  # Return a hashref defining the entire application database schema
+  # Each key of the hashref contains a structure describing a database table
+  #
+  # table_name => {
+  #   columns      => [...],
+  #   primary_key  => 'column',
+  #   unique       => [column,column,...],
+  #   index        => [[column],[column,column],...],
+  #   foreign_keys => [{...},{...},...],
+  # }
+  #
+  #
+  # columns => [
+  #
+  #   'column_name',
+  #
+  #   'column_type',
+  #
+  #   'NULL' or '',   # 'NULL' : Allow null values
+  #                   # ''     : Disallow null values
+  #
+  #   'length',       # Column size value.  eg:
+  #                   # 40     : VARCHAR(40)
+  #                   # '10,2' : FLOAT(10,2)
+  #
+  #   'default',      # Default column value for a new record
+  #                   # (Unclear if setting this to '' results in a default
+  #                   #  value of NULL or empty string?)
+  #
+  #   '',             # local ?
+  #
+  #   name, type, nullability, length, default, local,
+  #   name, type, nullability, length, default, local,
+  #   ...
+  #
+  # ],
 
+  # name type nullability length default local
   return {
 
     'agent' => {
@@ -695,6 +731,7 @@ sub tables_hashref {
         'statementnum', 'int', 'NULL', '', '', '', #invoice aggregate statements
         'agent_invid',  'int', 'NULL', '', '', '', #(varchar?) importing legacy
         'promised_date', @date_type,       '', '',
+        'taxengine_request', 'text', 'NULL', '', '', '',
         
         'pending',     'char', 'NULL',  1, '', '',
       ],
@@ -732,6 +769,7 @@ sub tables_hashref {
         'statementnum', 'int', 'NULL', '', '', '', #invoice aggregate statements
         'agent_invid',  'int', 'NULL', '', '', '', #(varchar?) importing legacy
         'promised_date', @date_type,       '', '',
+        'taxengine_request', 'text', 'NULL', '', '', '',
 
         #void fields
         'void_date', @date_type, '', '', 
@@ -1620,6 +1658,8 @@ sub tables_hashref {
         'ship_mobile',   'varchar', 'NULL', 12, '', '', 
         'currency',         'char', 'NULL',  3, '', '',
 
+        'is_tokenized', 'char', 'NULL', '1', '', '',
+
         'geocode',  'varchar', 'NULL', 20,  '', '',
         'censustract', 'varchar', 'NULL', 20,  '', '', # 7 to save space?
         'censusyear', 'char', 'NULL', 4, '', '',
@@ -1655,6 +1695,7 @@ sub tables_hashref {
         'invoice_attn', 'varchar', 'NULL', $char_d, '', '',
         'invoice_ship_address', 'char', 'NULL', 1, '', '',
         'postal_invoice', 'char', 'NULL', 1, '', '',
+        'paymentreceipt_noemail', 'char', 'NULL', 1, '', '',
       ],
       'primary_key'  => 'custnum',
       'unique'       => [ [ 'agentnum', 'agent_custid' ] ],
@@ -1666,6 +1707,7 @@ sub tables_hashref {
                           [ 'archived' ],
                           [ 'ship_locationnum' ],
                           [ 'bill_locationnum' ],
+                          [ 'is_tokenized' ],
                         ],
       'foreign_keys' => [
                           { columns    => [ 'agentnum' ],
@@ -1718,10 +1760,11 @@ sub tables_hashref {
         'paytype',     'varchar', 'NULL',   $char_d, '', '', 
         'payip',       'varchar', 'NULL',        15, '', '', 
         'locationnum',     'int', 'NULL',        '', '', '',
+        'is_tokenized',   'char', 'NULL',         1, '', '', 
       ],
       'primary_key'  => 'custpaybynum',
       'unique'       => [],
-      'index'        => [ [ 'custnum' ] ],
+      'index'        => [ [ 'custnum' ], [ 'is_tokenized' ] ],
       'foreign_keys' => [
                           { columns    => [ 'custnum' ],
                             table      => 'cust_main',
@@ -1751,7 +1794,8 @@ sub tables_hashref {
         'classnum',              'int', 'NULL',  '', '', '',
         'comment',           'varchar', 'NULL', 255, '', '',
         'selfservice_access',   'char', 'NULL',   1, '', '',
-        'invoice_dest',         'char', 'NULL',       1, '', '',
+        'invoice_dest',         'char', 'NULL',   1, '', '', # Y or NULL
+        'message_dest',         'char', 'NULL',   1, '', '', # Y or NULL
       ],
       'primary_key'  => 'custcontactnum',
       'unique'       => [ [ 'custnum', 'contactnum' ], ],
@@ -1914,14 +1958,15 @@ sub tables_hashref {
     'quotation' => {
       'columns' => [
         #regular fields
-        'quotationnum',   'serial',     '', '', '', '', 
-        'prospectnum',       'int', 'NULL', '', '', '',
-        'custnum',           'int', 'NULL', '', '', '',
-        '_date',        @date_type,             '', '', 
-        'disabled',         'char', 'NULL',  1, '', '', 
-        'usernum',           'int', 'NULL', '', '', '',
-        'close_date',   @date_type,             '', '', 
-        'confidence',        'int', 'NULL', '', '', '',
+        'quotationnum',           'serial',     '', '',   '', '', 
+        'prospectnum',               'int', 'NULL', '',   '', '',
+        'custnum',                   'int', 'NULL', '',   '', '',
+        '_date',                @date_type,               '', '', 
+        'disabled',                 'char', 'NULL',  1,   '', '', 
+        'usernum',                   'int', 'NULL', '',   '', '',
+        'close_date',           @date_type,               '', '', 
+        'confidence',                'int', 'NULL', '',   '', '',
+        'quotation_description', 'varchar', 'NULL', '50', '', '',
         #'total',      @money_type,       '', '', 
         #'quotation_term', 'varchar', 'NULL', $char_d, '', '',
       ],
@@ -2273,6 +2318,7 @@ sub tables_hashref {
         'setuptax',    'char', 'NULL',       1, '', '', # Y = setup tax exempt
         'recurtax',    'char', 'NULL',       1, '', '', # Y = recur tax exempt
         'source',   'varchar', 'NULL', $char_d, '', '',
+        'charge_prediscount', 'char', 'NULL', 1, '', '', # Y = charge this tax pre discount
       ],
       'primary_key' => 'taxnum',
       'unique' => [],
@@ -2330,6 +2376,7 @@ sub tables_hashref {
         'taxratelocationnum', 'serial',  '',     '', '', '', 
         'data_vendor',        'varchar', 'NULL', $char_d, '', '',
         'geocode',            'varchar', '',     20,      '', '', 
+        'district',           'varchar', 'NULL', $char_d, '', '',
         'city',               'varchar', 'NULL', $char_d, '', '',
         'county',             'varchar', 'NULL', $char_d, '', '',
         'state',              'char',    'NULL',       2, '', '', 
@@ -2415,11 +2462,13 @@ sub tables_hashref {
         'manual',       'char',    'NULL',   1, '', '',
         'discount_term','int',     'NULL',  '', '', '',
         'failure_status','varchar','NULL',  16, '', '',
+        'is_tokenized',   'char', 'NULL',    1, '', '', 
       ],
       'primary_key'  => 'paypendingnum',
       'unique'       => [ [ 'payunique' ] ],
       'index'        => [ [ 'custnum' ], [ 'status' ],
                           ['paynum'], ['void_paynum'], ['jobnum'], ['invnum'],
+                          [ 'is_tokenized' ],
                         ],
       'foreign_keys' => [
                           { columns    => [ 'custnum' ],
@@ -2466,7 +2515,8 @@ sub tables_hashref {
         'closed',         'char', 'NULL',       1, '', '', 
         'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances
         'no_auto_apply',  'char', 'NULL',       1, '', '', 
-
+        'is_tokenized',   'char', 'NULL',       1, '', '', 
+          
         # cash/check deposit info fields
         'bank',        'varchar', 'NULL', $char_d, '', '',
         'depositor',   'varchar', 'NULL', $char_d, '', '',
@@ -2484,7 +2534,7 @@ sub tables_hashref {
       'primary_key'  => 'paynum',
       #i guess not now, with cust_pay_pending, if we actually make it here, we _do_ want to record it# 'unique' => [ [ 'payunique' ] ],
       'index'        => [ ['custnum'], ['paybatch'], ['payby'], ['_date'],
-                          ['usernum'],
+                          ['usernum'], ['is_tokenized'], 
                         ],
       'foreign_keys' => [
                           { columns    => [ 'custnum' ],
@@ -2522,6 +2572,7 @@ sub tables_hashref {
         'paybatch',    'varchar', 'NULL', $char_d, '', '', #for auditing purposes.
         'closed',        'char',  'NULL',       1, '', '', 
         'pkgnum', 'int',   'NULL', '', '', '', #desired pkgnum for pkg-balances
+        'is_tokenized',   'char', 'NULL',       1, '', '', 
 
         # cash/check deposit info fields
         'bank',       'varchar', 'NULL', $char_d, '', '',
@@ -2544,7 +2595,9 @@ sub tables_hashref {
       ],
       'primary_key'  => 'paynum',
       'unique'       => [],
-      'index'        => [ ['custnum'], ['usernum'], ['void_usernum'] ],
+      'index'        => [ ['custnum'], ['usernum'], ['void_usernum'],
+                          ['is_tokenized'],
+                        ],
       'foreign_keys' => [
                           { columns    => [ 'custnum' ],
                             table      => 'cust_main',
@@ -2685,6 +2738,7 @@ sub tables_hashref {
         'country',          'char',     '',       2, '', '', 
         'payby',            'char',     '',       4, '', '',
         'payinfo',       'varchar', 'NULL',     512, '', '', 
+        #'paymask',       'varchar', 'NULL', $char_d, '', '',
         #'exp',          @date_type,                  '', '',
         'exp',           'varchar', 'NULL',      11, '', '', 
         'payname',       'varchar', 'NULL', $char_d, '', '', 
@@ -2694,10 +2748,11 @@ sub tables_hashref {
         'status',        'varchar', 'NULL', $char_d, '', '', 
         'failure_status','varchar', 'NULL',      16, '', '',
         'error_message', 'varchar', 'NULL', $char_d, '', '',
+        'paycode',       'varchar', 'NULL', $char_d, '', '',
       ],
       'primary_key'  => 'paybatchnum',
       'unique'       => [],
-      'index'        => [ ['batchnum'], ['invnum'], ['custnum'] ],
+      'index'        => [ ['batchnum'], ['invnum'], ['custnum'],['status'] ],
       'foreign_keys' => [
                           { columns    => [ 'batchnum' ],
                             table      => 'pay_batch',
@@ -3086,10 +3141,11 @@ sub tables_hashref {
         'processor',  'varchar', 'NULL', $char_d, '', '', # module name
         'auth',       'varchar','NULL',16, '', '', # CC auth number
         'order_number', 'varchar','NULL',$char_d, '', '', # transaction number
+        'is_tokenized',   'char', 'NULL',    1, '', '', 
       ],
       'primary_key'  => 'refundnum',
       'unique'       => [],
-      'index'        => [ ['custnum'], ['_date'], [ 'usernum' ], ],
+      'index'        => [ ['custnum'], ['_date'], [ 'usernum' ], ['is_tokenized'] ],
       'foreign_keys' => [
                           { columns    => [ 'custnum' ],
                             table      => 'cust_main',
@@ -3216,7 +3272,7 @@ sub tables_hashref {
       'columns' => [
         'pkgpart',       'serial',    '',   '', '', '', 
         'pkgpartbatch',  'varchar', 'NULL', $char_d, '', '',
-        'pkg',           'varchar',   '',   $char_d, '', '', 
+        'pkg',           'varchar',   '',   104, '', '', 
         'comment',       'varchar', 'NULL', 2*$char_d, '', '', 
         'promo_code',    'varchar', 'NULL', $char_d, '', '', 
         'freq',          'varchar',   '',   $char_d, '', '', #billing frequency
@@ -4114,7 +4170,7 @@ sub tables_hashref {
         'note',     'text',    'NULL',      '', '', '',
       ],
       'primary_key'  => 'svcnum',
-      'unique'       => [],
+      'unique'       => [ ['typenum', 'serial'], ['ip_addr'], ['hw_addr'] ],
       'index'        => [],
       'foreign_keys' => [
                           { columns    => [ 'svcnum' ],
@@ -4837,6 +4893,9 @@ sub tables_hashref {
         'suid',                    'int', 'NULL',        '', '', '',
         'shared_svcnum',           'int', 'NULL',        '', '', '',
         'serviceid',           'varchar', 'NULL',        64, '', '',#srvexport/reportfields
+        'speed_test_up',           'int', 'NULL',        '', '', '',
+        'speed_test_down',         'int', 'NULL',        '', '', '',
+        'speed_test_latency',      'int', 'NULL',        '', '', '',
       ],
       'primary_key'  => 'svcnum',
       'unique'       => [ [ 'ip_addr' ], [ 'mac_addr' ] ],
@@ -4874,6 +4933,8 @@ sub tables_hashref {
         'height',     'decimal', 'NULL',      '', '', '', 
         'veg_height', 'decimal', 'NULL',      '', '', '', 
         'color',      'varchar', 'NULL',       6, '', '',
+        'up_rate_limit',        'int', 'NULL',      '', '', '',
+        'down_rate_limit',      'int', 'NULL',      '', '', '',
       ],
       'primary_key' => 'towernum',
       'unique'      => [ [ 'towername' ] ], # , 'agentnum' ] ],
@@ -4904,8 +4965,9 @@ sub tables_hashref {
         'east',         'decimal', 'NULL', '10,7', '', '',
         'south',        'decimal', 'NULL', '10,7', '', '',
         'north',        'decimal', 'NULL', '10,7', '', '',
-
         'title',        'varchar', 'NULL', $char_d,'', '',
+        'up_rate_limit',          'int', 'NULL',      '', '', '',
+        'down_rate_limit',        'int', 'NULL',      '', '', '',
      ],
       'primary_key'  => 'sectornum',
       'unique'       => [ [ 'towernum', 'sectorname' ], [ 'ip_addr' ], ],
@@ -5218,7 +5280,7 @@ sub tables_hashref {
         'ocn',         'char',    'NULL',       4, '', '', 
       ],
       'primary_key'  => 'prefixnum',
-      'unique'       => [],
+      'unique'       => [ [ 'countrycode', 'npa', 'nxx' ] ],
       'index'        => [ [ 'countrycode' ], [ 'npa' ], [ 'regionnum' ] ],
       'foreign_keys' => [
                           { columns    => [ 'regionnum' ],
@@ -5598,6 +5660,9 @@ sub tables_hashref {
         #NULL, done (or something)
         'freesidestatus', 'varchar',   'NULL',     32,   '', '', 
 
+        #additional status info (i.e. reason for "skipped" freesidestatus)
+        'freesidestatustext', 'text',  'NULL',     '',   '', '',
+
         #NULL, done (or something)
         'freesiderewritestatus', 'varchar',   'NULL',     32,   '', '', 
 
@@ -5608,9 +5673,6 @@ sub tables_hashref {
         'sessionnum',       'int',    'NULL',      '', '', '',
         'subscriber',   'varchar',    'NULL', $char_d, '', '',
 
-        #old
-        'cdrbatch',     'varchar',    'NULL',     255, '', '',
-        #new
         'cdrbatchnum',      'int',    'NULL',      '', '', '',
 
         # FK to cust_bill_pkg_detail; having a value here absolutely means
@@ -5621,12 +5683,13 @@ sub tables_hashref {
       'unique' => [],
       'index' => [ [ 'calldate' ],
                    [ 'src' ], [ 'dst' ], [ 'dcontext' ], [ 'charged_party' ],
-                   [ 'startdate' ],
+                   [ 'startdate' ], [ 'duration' ],
+                   [ 'cdrtypenum' ], [ 'calltypenum' ],
                    [ 'lastapp' ],
                    ['accountcode'], ['uniqueid'], ['carrierid'], ['cdrid'],
                    [ 'sessionnum' ], [ 'subscriber' ],
                    [ 'freesidestatus' ], [ 'freesiderewritestatus' ],
-                   [ 'cdrbatch' ], [ 'cdrbatchnum' ],
+                   [ 'cdrbatchnum' ],
                    [ 'src_ip_addr' ], [ 'dst_ip_addr' ], [ 'dst_term' ],
                    [ 'detailnum' ],
                  ],
@@ -5782,6 +5845,25 @@ sub tables_hashref {
                         ],
     },
 
+    'access_user_session_log' => {
+      'columns' => [
+        'sessionlognum', 'serial', '',      '', '', '',
+        'usernum',          'int', '',      '', '', '',
+        'start_date',  @date_type,              '', '',
+        'last_date',   @date_type,              '', '',
+        'logout_date', @date_type,              '', '',
+        'logout_type',  'varchar', '', $char_d, '', '',
+      ],
+      'primary_key' => 'sessionlognum',
+      'unique'       => [],
+      'index'        => [],
+      'foreign_keys' => [
+                          { columns    => [ 'usernum' ],
+                            table      => 'access_user',
+                          },
+                        ],
+    },
+
     'access_user' => {
       'columns' => [
         'usernum',             'serial',     '',      '', '', '',
@@ -5829,8 +5911,9 @@ sub tables_hashref {
 
     'access_group' => {
       'columns' => [
-        'groupnum',   'serial', '',      '', '', '',
-        'groupname', 'varchar', '', $char_d, '', '',
+        'groupnum',        'serial',     '',      '', '', '',
+        'groupname',      'varchar',     '', $char_d, '', '',
+        'session_timeout',    'int', 'NULL',      '', '', '',
       ],
       'primary_key' => 'groupnum',
       'unique' => [ [ 'groupname' ] ],
@@ -5894,6 +5977,7 @@ sub tables_hashref {
         'path',           'varchar',     '', 2*$char_d, '', '',
         '_date',                   @date_type,          '', '',
         'render_seconds',     'int', 'NULL',        '', '', '',
+        'pid',                'int', 'NULL',        '', '', '',
       ],
       'primary_key'  => 'lognum',
       'unique'       => [],
@@ -5981,6 +6065,9 @@ sub tables_hashref {
         'lnp_other_provider',         'varchar', 'NULL', $char_d, '', '',
         'lnp_other_provider_account', 'varchar', 'NULL', $char_d, '', '',
         'lnp_reject_reason',          'varchar', 'NULL', $char_d, '', '',
+        'lnp_portid',                 'varchar', 'NULL', $char_d, '', '',
+        'lnp_signature',                 'char', 'NULL',       1, '', '',
+        'lnp_bill',                      'char', 'NULL',       1, '', '',
         'sms_carrierid',                  'int', 'NULL',      '', '', '',
         'sms_account',                'varchar', 'NULL', $char_d, '', '',
         'max_simultaneous',               'int', 'NULL',      '', '', '',
@@ -6820,7 +6907,7 @@ sub tables_hashref {
         'mac_addr',  'varchar', 'NULL',      12, '', '', 
       ],
       'primary_key'  => 'svcnum',
-      'unique'       => [ ['serialnum'] , ['mac_addr'] ],
+      'unique'       => [ ['modelnum', 'serialnum'] , ['mac_addr'] ],
       'index'        => [],
       'foreign_keys' => [
                           { columns    => [ 'svcnum' ],
@@ -6978,6 +7065,7 @@ sub tables_hashref {
         'vendor_order_status',  'varchar', 'NULL', $char_d,  '', '',
         'endpoint_ip_addr',     'varchar', 'NULL', 40, '', '',
         'endpoint_mac_addr',    'varchar', 'NULL', 12, '', '',
+        'internal_circuit_id',  'varchar', 'NULL', 64, '', '',
       ],
       'primary_key' => 'svcnum',
       'unique'      => [],
@@ -7560,6 +7648,73 @@ sub tables_hashref {
       'foreign_keys'  => [],
     },
 
+    'realestate_unit' => {
+      'columns' => [
+        'realestatenum',    'serial',  '',     '',      '',  '',
+        'realestatelocnum', 'int',     '',     '',      '',  '',
+        'agentnum',         'int',     'NULL', '',      '',  '',
+        'unit_title',       'varchar', '',     $char_d, '',  '',
+        'disabled',         'char',    'NULL', 1,       '',  '',
+      ],
+      'primary_key'  => 'realestatenum',
+      'unique'       => [ ['unit_title'] ],
+      'index'        => [
+        ['agentnum'],
+        ['realestatelocnum'],
+        ['disabled'],
+        ['unit_title'],
+      ],
+      'foreign_keys' => [
+        {columns => ['agentnum'], table => 'agent'},
+        {columns => ['realestatelocnum'] => table => 'realestate_location'},
+      ],
+    },
+
+    'realestate_location' => {
+      'columns' => [
+        'realestatelocnum', 'serial',  '',     '',      '', '',
+        'agentnum',         'int',     'NULL', '',      '', '',
+        'location_title',   'varchar', '',     $char_d, '', '',
+        'address1',         'varchar', 'NULL', $char_d, '',  '',
+        'address2',         'varchar', 'NULL', $char_d, '',  '',
+        'city',             'varchar', 'NULL', $char_d, '',  '',
+        'state',            'varchar', 'NULL', $char_d, '',  '',
+        'zip',              'char',    'NULL', 5,       '',  '',
+        'disabled',         'char',    'NULL', 1,       '',  '',
+      ],
+      'primary_key'  => 'realestatelocnum',
+      'unique'       => [ ['location_title'] ],
+      'index'        => [ ['agentnum'], ['disabled'] ],
+      'foreign_keys' => [
+        {columns => ['agentnum'], table => 'agent'},
+      ],
+    },
+
+    'svc_realestate' => {
+      'columns' => [
+        'svcnum',        'serial',  '',     '',      '', '',
+        'realestatenum', 'int',     'NULL', '',      '', '',
+      ],
+      'primary_key' => 'svcnum',
+      'index'       => [],
+    },
+
+    'svc_group' => {
+      'columns' => [
+        'svcnum',          'int', '', '', '', '', 
+        'max_accounts',    'int', '', '', '', '',
+      ],
+      'primary_key'  => 'svcnum',
+      'unique'       => [],
+      'index'        => [],
+      'foreign_keys' => [
+                          { columns    => [ 'svcnum' ],
+                            table      => 'cust_svc',
+                          },
+                        ],
+    },
+
+
     # name type nullability length default local
 
     #'new_table' => {
@@ -7586,4 +7741,3 @@ L<DBIx::DBSchema>
 =cut
 
 1;
-