X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2FSchema.pm;h=ab4752f4b4b4e66f6fc693db4bd2179ba2da1759;hp=e8b3309e2f670b4fd8d7aeba1858115b45adc83f;hb=c40ec6975d9d8275454eb9a1b076a4078d87c8df;hpb=f4f7bb4086cdaf2699c6d028b76e99b3e9be1e90

diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm
index e8b3309e2..ab4752f4b 100644
--- a/FS/FS/Schema.pm
+++ b/FS/FS/Schema.pm
@@ -347,7 +347,7 @@ sub tables_hashref_torrus {
       ],
       'primary_key' => 'id',
       'unique' => [],
-      'index'  => [ ['srv_date'], ['serviceid'], ],
+      'index'  => [ ['srv_date'], ['srv_date', 'srv_time'], ['serviceid'], ],
     },
 
     #Tables for (currently monthly only) report contents.
@@ -691,6 +691,7 @@ sub tables_hashref {
         'classnum', 'int', 'NULL', '', '', '',
         'duration', 'int', 'NULL', '',  0, '',
         'phonenum', 'varchar', 'NULL', 15, '', '',
+        'accountcode', 'varchar',  'NULL',      20, '', '',
         'regionname', 'varchar', 'NULL', $char_d, '', '',
         'detail',  'varchar', '', 255, '', '', 
       ],
@@ -870,6 +871,8 @@ sub tables_hashref {
         'credit_limit', @money_typen, '', '',
         'archived', 'char', 'NULL', 1, '', '',
         'email_csv_cdr', 'char', 'NULL', 1, '', '',
+        'accountcode_cdr', 'char', 'NULL', 1, '', '',
+        'billday',   'int', 'NULL', '', '', '',
       ],
       'primary_key' => 'custnum',
       'unique' => [ [ 'agentnum', 'agent_custid' ] ],
@@ -982,33 +985,35 @@ sub tables_hashref {
       'columns' => [
         'prospectnum',  'serial',     '',      '', '', '',
         'agentnum',        'int',     '',      '', '', '',
-        'company',     'varchar',     '', $char_d, '', '',
-        #'disabled',     'char', 'NULL',       1, '', '', 
+        'company',     'varchar', 'NULL', $char_d, '', '',
+        'add_date',   @date_type,                  '', '', 
+        'disabled',       'char', 'NULL',       1, '', '', 
+        'custnum',         'int', 'NULL',      '', '', '',
       ],
       'primary_key' => 'prospectnum',
       'unique'      => [],
-      'index'       => [ [ 'company' ], [ 'agentnum' ], ],
+      'index'       => [ [ 'company' ], [ 'agentnum' ], [ 'disabled' ] ],
     },
 
     #eventually use for billing & ship from cust_main too
     #for now, just cust_pkg locations
     'cust_location' => { #'location' now that its prospects too, but...
       'columns' => [
-        'locationnum',  'serial',     '',      '', '', '',
-        'prospectnum',     'int', 'NULL',      '', '', '',
-        'custnum',         'int', 'NULL',      '', '', '',
-        'address1',    'varchar',     '', $char_d, '', '', 
-        'address2',    'varchar', 'NULL', $char_d, '', '', 
-        'city',        'varchar',     '', $char_d, '', '', 
-        'county',      'varchar', 'NULL', $char_d, '', '', 
-        'state',       'varchar', 'NULL', $char_d, '', '', 
-        '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, '', '', 
+        'locationnum',      'serial',     '',      '', '', '',
+        'prospectnum',         'int', 'NULL',      '', '', '',
+        'custnum',             'int', 'NULL',      '', '', '',
+        'address1',        'varchar',     '', $char_d, '', '', 
+        'address2',        'varchar', 'NULL', $char_d, '', '', 
+        'city',            'varchar',     '', $char_d, '', '', 
+        'county',          'varchar', 'NULL', $char_d, '', '', 
+        'state',           'varchar', 'NULL', $char_d, '', '', 
+        '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'      => [],
@@ -1402,6 +1407,16 @@ sub tables_hashref {
       'index' => [ ['batchnum'], ['invnum'], ['custnum'] ],
     },
 
+    'fcc477map' => {
+      'columns' => [
+        'formkey',   'varchar',     '', 255, '', '',
+        'formvalue',    'text', 'NULL',  '', '', '',
+      ],
+      'primary_key' => 'formkey',
+      'unique'      => [],
+      'index'       => [],
+    },
+
     'cust_pkg' => {
       'columns' => [
         'pkgnum',           'serial',     '', '', '', '', 
@@ -1429,14 +1444,15 @@ sub tables_hashref {
         'no_auto',            'char', 'NULL',  1, '', '', 
         'quantity',            'int', 'NULL', '', '', '',
         'agent_pkgid',         'int', 'NULL', '', '', '',
+        'waive_setup',        'char', 'NULL',  1, '', '', 
       ],
       'primary_key' => 'pkgnum',
       'unique' => [],
       'index' => [ ['custnum'], ['pkgpart'], [ 'pkgbatch' ], [ 'locationnum' ],
                    [ 'usernum' ], [ 'agent_pkgid' ],
-                   [ 'start_date' ], ['setup'], ['last_bill'], ['bill'],
-                   ['susp'], ['adjourn'], ['expire'], ['cancel'],
-                   ['change_date'], ['order_date'],
+                   ['order_date'], [ 'start_date' ], ['setup'], ['bill'],
+                   ['last_bill'], ['susp'], ['adjourn'], ['cancel'],
+                   ['expire'], ['contract_end'], ['change_date'], ['no_auto'],
                  ],
     },
 
@@ -1518,6 +1534,7 @@ sub tables_hashref {
         'percent',    'decimal',     '',      '', '', '',
         'months',     'decimal', 'NULL',      '', '', '',
         'disabled',      'char', 'NULL',       1, '', '', 
+        'setup',      'char', 'NULL',       1, '', '', 
       ],
       'primary_key' => 'discountnum',
       'unique' => [],
@@ -1584,7 +1601,7 @@ sub tables_hashref {
       'index'       => [ [ 'svcnum' ], [ 'optionname' ] ],
     },
 
-    'part_pkg' => {
+   'part_pkg' => {
       'columns' => [
         'pkgpart',       'serial',    '',   '', '', '', 
         'pkg',           'varchar',   '',   $char_d, '', '', 
@@ -1614,7 +1631,7 @@ sub tables_hashref {
       'primary_key' => 'pkgpart',
       'unique' => [],
       'index' => [ [ 'promo_code' ], [ 'disabled' ], [ 'classnum' ],
-                   [ 'agentnum' ],
+                   [ 'agentnum' ], ['no_auto'],
                  ],
     },
 
@@ -1743,6 +1760,7 @@ sub tables_hashref {
         'svc',        'varchar',   '',   $char_d, '', '', 
         'svcdb',      'varchar',   '',   $char_d, '', '', 
         'disabled',   'char',  'NULL',   1, '', '', 
+        'preserve',   'char', 'NULL',  1, '', '',
       ],
       'primary_key' => 'svcpart',
       'unique' => [],
@@ -2001,6 +2019,66 @@ sub tables_hashref {
       'index' => [ ['svcnum'] ],
     },
 
+    'svc_dish' => {
+      'columns' => [
+        'svcnum',   'int',     '',     '', '', '',
+        'acctnum',  'varchar', '',     16, '', '',
+        'installdate', @date_type,         '', '', 
+        'note',     'text',    'NULL', '', '', '',
+      ],
+      'primary_key' => 'svcnum',
+      'unique' => [ ],
+      'index' => [ ],
+    },
+
+    'svc_hardware' => {
+      'columns' => [
+        'svcnum',   'int',     '',          '', '', '',
+        'typenum',  'int',     '',          '', '', '',
+        'serial',   'varchar', 'NULL', $char_d, '', '',
+        'ip_addr',  'varchar', 'NULL',      40, '', '',
+        'hw_addr',  'varchar', 'NULL',      12, '', '',
+        'smartcard','varchar', 'NULL',      30, '', '',
+        'statusnum','int',     'NULL',      '', '', '',
+        'note',     'text',    'NULL',      '', '', '',
+      ],
+      'primary_key' => 'svcnum',
+      'unique' => [ ],
+      'index' => [ ],
+    },
+
+    'hardware_class' => {
+      'columns' => [
+        'classnum',   'serial', '',      '', '', '',
+        'classname', 'varchar', '', $char_d, '', '',
+      ],
+      'primary_key' => 'classnum',
+      'unique' => [ ],
+      'index'  => [ ],
+    },
+
+    'hardware_type' => {
+      'columns' => [
+        'typenum',  'serial', '',      '', '', '',
+        'classnum',    'int', '',      '', '', '',
+        'model',   'varchar', '', $char_d, '', '',
+      ],
+      'primary_key' => 'typenum',
+      'unique' => [ ],
+      'index'  => [ ],
+    },
+
+    'hardware_status' => {
+      'columns' => [
+        'statusnum', 'serial', '',      '', '', '',
+        'label'    ,'varchar', '', $char_d, '', '',
+        'disabled',    'char', 'NULL',   1, '', '', 
+      ],
+      'primary_key' => 'statusnum',
+      'unique' => [ ],
+      'index'  => [ ],
+    },
+
     'domain_record' => {
       'columns' => [
         'recnum',    'serial',     '',  '', '', '', 
@@ -2259,7 +2337,7 @@ sub tables_hashref {
     'msgcat' => {
       'columns' => [
         'msgnum', 'serial', '', '', '', '', 
-        'msgcode', 'varchar', '', $char_d, '', '', 
+        'msgcode', 'varchar', '', 255, '', '', 
         'locale', 'varchar', '', 16, '', '', 
         'msg', 'text', '', '', '', '', 
       ],
@@ -2410,8 +2488,8 @@ sub tables_hashref {
 
     'svc_external' => {
       'columns' => [
-        'svcnum', 'int', '', '', '', '', 
-        'id',     'int', 'NULL', '', '', '', 
+        'svcnum',     'int',     '',      '', '', '', 
+        'id',      'bigint', 'NULL',      '', '', '', 
         'title',  'varchar', 'NULL', $char_d, '', '', 
       ],
       'primary_key' => 'svcnum',
@@ -2491,6 +2569,7 @@ sub tables_hashref {
         'ratetimenum',     'int', 'NULL',     '', '', '',
         'classnum',        'int', 'NULL',     '', '', '', 
         'cdrtypenum',      'int', 'NULL',     '', '', '',
+        'region_group', 'char', 'NULL',       1, '', '', 
       ],
       'primary_key' => 'ratedetailnum',
       'unique'      => [ [ 'ratenum', 'orig_regionnum', 'dest_regionnum' ] ],
@@ -2514,6 +2593,9 @@ sub tables_hashref {
         'countrycode', 'varchar',     '',  3, '', '', 
         'npa',         'varchar', 'NULL', 10, '', '', #actually the whole prefix
         'nxx',         'varchar', 'NULL',  3, '', '', #actually not used
+        'latanum',     'int',     'NULL',      '', '', '',
+        'state',       'char',    'NULL',       2, '', '', 
+        'ocn',         'char',    'NULL',       4, '', '', 
       ],
       'primary_key' => 'prefixnum',
       'unique'      => [],
@@ -2720,7 +2802,7 @@ sub tables_hashref {
         'disposition', 'varchar',  '',      45, \"''", '',
         'amaflags',    'int',      '',      '',     0, '',
         'accountcode', 'varchar',  '',      20, \"''", '',
-        'uniqueid',    'varchar',  '',      32, \"''", '',
+        'uniqueid',    'varchar',  '', $char_d, \"''", '',
         'userfield',   'varchar',  '',     255, \"''", '',
 
         'max_callers', 'int',  'NULL',      '',    '', '',
@@ -2729,7 +2811,7 @@ sub tables_hashref {
         # fields for unitel/RSLCOM/convergent that don't map well to asterisk
         # defaults
         # though these are now used elsewhere:
-        # charged_party, upstream_price, rated_price, carrierid
+        # charged_party, upstream_price, rated_price, carrierid, cdrtypenum
         ###
 
         #cdr_type: Usage = 1, S&E = 7, OC&C = 8
@@ -2738,7 +2820,7 @@ sub tables_hashref {
         'charged_party',       'varchar', 'NULL', $char_d, '', '',
 
         'upstream_currency',      'char', 'NULL',       3, '', '',
-        'upstream_price',      'decimal', 'NULL',  '10,2', '', '', 
+        'upstream_price',      'decimal', 'NULL',  '10,4', '', '', 
         'upstream_rateplanid',     'int', 'NULL',      '', '', '', #?
 
         # how it was rated internally...
@@ -2775,17 +2857,23 @@ sub tables_hashref {
         #an indexed place to put big numbers
         'cdrid',         'bigint',     'NULL',     '',  '', '', 
 
+        #for taqua accountcode rewriting, for starters
+        'sessionnum',       'int',    'NULL',      '', '', '',
+        'subscriber',   'varchar',    'NULL', $char_d, '', '',
+
         #old
-        'cdrbatch', 'varchar', 'NULL', 255, '', '',
+        'cdrbatch',     'varchar',    'NULL',     255, '', '',
         #new
-        'cdrbatchnum', 'int', 'NULL', '', '', '',
+        'cdrbatchnum',      'int',    'NULL',      '', '', '',
 
       ],
       'primary_key' => 'acctid',
       'unique' => [],
       'index' => [ [ 'calldate' ],
                    [ 'src' ], [ 'dst' ], [ 'dcontext' ], [ 'charged_party' ],
-                   [ 'accountcode' ], [ 'carrierid' ], [ 'cdrid' ],
+                   [ 'lastapp' ],
+                   ['accountcode'], ['uniqueid'], ['carrierid'], ['cdrid'],
+                   [ 'sessionnum' ], [ 'subscriber' ],
                    [ 'freesidestatus' ], [ 'freesiderewritestatus' ],
                    [ 'cdrbatch' ], [ 'cdrbatchnum' ],
                  ],
@@ -3032,6 +3120,8 @@ sub tables_hashref {
         'station',     'char',    'NULL',       4, '', '',
         'name',        'varchar', 'NULL', $char_d, '', '',
         'rate_center_abbrev', 'varchar', 'NULL', $char_d, '', '',
+        'latanum',      'int',     'NULL',      '', '', '',
+        'msanum',       'int', 'NULL', '', '', '',
         'ordernum',      'int',     'NULL',      '', '', '',
         'svcnum',      'int',     'NULL',      '', '', '',
         'availbatch', 'varchar',  'NULL', $char_d, '', '',
@@ -3057,6 +3147,26 @@ sub tables_hashref {
       'index'  => [],
     },
     
+    'msa' => {
+      'columns' => [
+        'msanum',    'int',      '',      '', '', '', 
+        'description',   'varchar',    '',      $char_d, '', '', 
+      ],
+      'primary_key' => 'msanum',
+      'unique' => [],
+      'index'  => [],
+    },
+    
+    'rate_center' => {
+      'columns' => [
+        'ratecenternum',    'serial',      '',      '', '', '', 
+        'description',   'varchar',    '',      $char_d, '', '', 
+      ],
+      'primary_key' => 'ratecenternum',
+      'unique' => [],
+      'index'  => [],
+    },
+
     'did_vendor' => {
       'columns' => [
         'vendornum',    'serial',      '',      '', '', '', 
@@ -3067,16 +3177,29 @@ sub tables_hashref {
       'index'  => [],
     },
     
-    'did_order' => {
+    'did_order_item' => {
       'columns' => [
-        'ordernum',    'serial',      '',      '', '', '', 
-        'vendornum',   'int',       '',      '', '', '', 
-        'vendor_order_id',   'varchar',  '',   $char_d, '', '', 
-        'msa',        'varchar', 'NULL', $char_d, '', '',
+        'orderitemnum',    'serial',      '',      '', '', '', 
+        'ordernum',    'int',      '',      '', '', '', 
+        'msanum',      'int',     'NULL',      '', '', '',
+        'npa',      'int',     'NULL',      '', '', '',
         'latanum',      'int',     'NULL',      '', '', '',
-        'rate_center',        'varchar', 'NULL', $char_d, '', '',
+        'ratecenternum',      'int',     'NULL',      '', '', '',
         'state',       'char',    'NULL',       2, '', '', 
         'quantity',      'int',     '',      '', '', '',
+        'custnum',   'int', 'NULL', '', '', '',
+      ],
+      'primary_key' => 'orderitemnum',
+      'unique' => [],
+      'index'  => [],
+    },
+
+    'did_order' => {
+      'columns' => [
+        'ordernum',    'serial',      '',      '', '', '', 
+        'vendornum',   'int',       '',      '', '', '', 
+        'vendor_order_id',   'varchar',  'NULL',   $char_d, '', '', 
+        'custnum',   'int', 'NULL', '', '', '',
         'submitted',      'int',     '',      '', '', '',
         'confirmed',      'int',     'NULL',      '', '', '',
         'received',      'int',     'NULL',      '', '', '',
@@ -3235,6 +3358,24 @@ sub tables_hashref {
       'index'       => [ ['agentnum'], ]
     },
 
+    'cust_msg' => {
+      'columns' => [
+        'custmsgnum', 'serial',     '',     '', '', '',
+        'custnum',       'int',     '',     '', '', '',
+        'msgnum',        'int', 'NULL',     '', '', '',
+        '_date',    @date_type,                 '', '',
+        'env_from',  'varchar', 'NULL',    255, '', '',
+        'env_to',    'varchar', 'NULL',    255, '', '',
+        'header',       'blob', 'NULL',     '', '', '',
+        'body',         'blob', 'NULL',     '', '', '',
+        'error',     'varchar', 'NULL',    255, '', '',
+        'status',    'varchar',     '',$char_d, '', '',
+      ],
+      'primary_key' => 'custmsgnum',
+      'unique'      => [ ],
+      'index'       => [ ['custnum'], ],
+    },
+
     'svc_cert' => {
       'columns' => [
         'svcnum',                'int',     '',      '', '', '', 
@@ -3266,14 +3407,27 @@ sub tables_hashref {
       'index'  => [], #recnum
     },
 
+    'areacode'  => {
+      'columns' => [
+        'areanum',   'serial',        '',      '', '', '',
+        'code',        'char',        '',       3, '', '', 
+        'country',     'char',    'NULL',       2, '', '',
+        'state',       'char',    'NULL',       2, '', '', 
+        'description','varchar',  'NULL',     255, '', '',
+      ], 
+      'primary_key' => 'areanum',
+      'unique' => [ [ 'areanum' ] ],
+      'index'  => [],
+    },
+
     %{ tables_hashref_torrus() },
 
     # tables of ours for doing torrus virtual port combining
     'torrus_srvderive' => {
       'columns' => [
-        'derivenum',  'serial', '', '', '', '',
-        'serviceid', 'varchar', '', 64, '', '', #srvexport / reportfields
-        #'func',      'varchar', '', $char_d, '', '',
+        'derivenum',     'serial',     '', '', '', '',
+        'serviceid',    'varchar',     '', 64, '', '', #srvexport / reportfields
+        'last_srv_date',   'date', 'NULL', '', '', '',
       ],
       'primary_key' => 'derivenum',
       'unique' => [ ['serviceid'] ],