X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2FSchema.pm;h=54c84f75d18cbe9f653dd8fdd82862a3643b4e17;hb=fc330b9dabbf5094f791c63e44a1e25aa206abb6;hp=4f547725d06ea51e2e4cb5543724765812faabfe;hpb=7bfd0175b05d52b648e283fca4343aef79e851d7;p=freeside.git

diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm
index 4f547725d..54c84f75d 100644
--- a/FS/FS/Schema.pm
+++ b/FS/FS/Schema.pm
@@ -200,7 +200,7 @@ sub dbdef_dist {
     grep {    ! /^(clientapi|access_user)_session/
            && ! /^h_/
            && ! /^log(_context)?$/
-           && ( ! /^queue(_arg)?$/ || ! $opt->{'queue-no_history'} )
+           && ( ! /^queue(_arg|_depend|_stat)?$/ || ! $opt->{'queue-no_history'} )
            && ! $tables_hashref_torrus->{$_}
          }
       $dbdef->tables
@@ -662,6 +662,7 @@ sub tables_hashref {
         'invoice_terms', 'varchar', 'NULL', $char_d, '', '',
 
         #customer balance info at invoice generation time
+        #(deprecated)
         'previous_balance',   @money_typen, '', '',  #eventually not nullable
         'billing_balance',    @money_typen, '', '',  #eventually not nullable
 
@@ -1008,7 +1009,7 @@ sub tables_hashref {
 
     'cust_bill_pkg_detail' => {
       'columns' => [
-        'detailnum', 'serial', '', '', '', '', 
+        'detailnum', 'serial', '', '', '', '',
         'billpkgnum', 'int', 'NULL', '', '', '',        # should not be nullable
         'pkgnum',  'int', 'NULL', '', '', '',           # deprecated
         'invnum',  'int', 'NULL', '', '', '',           # deprecated
@@ -1617,6 +1618,8 @@ sub tables_hashref {
                           [ 'referral_custnum' ],
                           [ 'payby' ], [ 'paydate' ],
                           [ 'archived' ],
+                          [ 'ship_locationnum' ],
+                          [ 'bill_locationnum' ],
                         ],
       'foreign_keys' => [
                           { columns    => [ 'agentnum' ],
@@ -2232,7 +2235,7 @@ sub tables_hashref {
 
         'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances
         'status',       'varchar',     '', $char_d, '', '', 
-        'session_id',   'varchar', 'NULL', $char_d, '', '', #only need 32
+        'session_id',   'varchar', 'NULL', 1024, '', '', # SHA-512-hex
         'statustext',   'text',    'NULL',  '', '', '', 
         'gatewaynum',   'int',     'NULL',  '', '', '',
         #'cust_balance', @money_type,            '', '',
@@ -2573,7 +2576,7 @@ sub tables_hashref {
         'manual_flag',        'char', 'NULL',  1, '', '', 
         'no_auto',            'char', 'NULL',  1, '', '', 
         'quantity',            'int', 'NULL', '', '', '',
-        'agent_pkgid',         'int', 'NULL', '', '', '',
+        'agent_pkgid',     'varchar', 'NULL', $char_d, '', '',
         'waive_setup',        'char', 'NULL',  1, '', '', 
         'recur_show_zero',    'char', 'NULL',  1, '', '',
         'setup_show_zero',    'char', 'NULL',  1, '', '',
@@ -2584,9 +2587,17 @@ sub tables_hashref {
       'index'        => [ ['custnum'], ['pkgpart'], ['pkgbatch'],
                           ['locationnum'], ['usernum'], ['agent_pkgid'],
                           ['order_date'], [ 'start_date' ], ['setup'], ['bill'],
-                          ['last_bill'], ['susp'], ['adjourn'], ['cancel'],
-                          ['expire'], ['contract_end'], ['change_date'],
+                          ['last_bill'], ['susp'], ['adjourn'], ['resume'],
+                          ['cancel'], ['expire'], ['contract_end'],
+                          ['change_date'],
                           ['no_auto'],
+                          #['contactnum'],
+                          ['salesnum'],
+                          #['uncancel_pkgnum'],
+                          #['change_pkgnum'], ['change_locationnum'],
+                          #['change_custnum'],
+                          ['main_pkgnum'],
+                          #['pkglinknum'], ['change_to_pkgnum'],
                         ],
       'foreign_keys' => [
                           { columns    => [ 'custnum' ],
@@ -2733,7 +2744,7 @@ sub tables_hashref {
       'columns' => [
         'pkgusagenum', 'serial', '', '', '', '',
         'pkgnum',         'int', '', '', '', '',
-        'minutes',        'int', '', '', '', '',
+        'minutes',        'double precision', '', '', '', '',
         'pkgusagepart',   'int', '', '', '', '',
       ],
       'primary_key'  => 'pkgusagenum',
@@ -2754,7 +2765,7 @@ sub tables_hashref {
         'cdrusagenum', 'bigserial', '', '', '', '',
         'acctid',      'bigint',    '', '', '', '',
         'pkgusagenum', 'int',       '', '', '', '',
-        'minutes',     'int',       '', '', '', '',
+        'minutes',     'double precision',       '', '', '', '',
       ],
       'primary_key'  => 'cdrusagenum',
       'unique'       => [],
@@ -4032,7 +4043,7 @@ sub tables_hashref {
       ],
       'primary_key'  => 'prepaynum',
       'unique'       => [ ['identifier'] ],
-      'index'        => [],
+      'index'        => [ ['agentnum'] ],
       'foreign_keys' => [
                           { columns    => [ 'agentnum' ],
                             table      => 'agent',
@@ -4166,6 +4177,21 @@ sub tables_hashref {
                         ],
     },
 
+    'queue_stat' => {
+      'columns' => [
+        'statnum', 'bigserial',     '',  '', '', '',
+        'jobnum',     'bigint',     '',  '', '', '',
+        'job',       'varchar',     '', 512, '', '', 
+        'custnum',       'int', 'NULL',  '', '', '',
+        'insert_date', @date_type, '', '',
+        'start_date',  @date_type, '', '', 
+        'end_date',    @date_type, '', '', 
+      ],
+      'primary_key'  => 'statnum',
+      'unique'       => [], #[ ['jobnum'] ],
+      'index'        => [],
+    },
+
     'export_svc' => {
       'columns' => [
         'exportsvcnum' => 'serial', '', '', '', '', 
@@ -4500,6 +4526,7 @@ sub tables_hashref {
         'rssi',                    'int', 'NULL',        '', '', '',
         'suid',                    'int', 'NULL',        '', '', '',
         'shared_svcnum',           'int', 'NULL',        '', '', '',
+        'serviceid',           'varchar', 'NULL',        64, '', '',#srvexport/reportfields
       ],
       'primary_key'  => 'svcnum',
       'unique'       => [ [ 'ip_addr' ], [ 'mac_addr' ] ],
@@ -4545,16 +4572,16 @@ sub tables_hashref {
 
     'tower_sector' => {
       'columns' => [
-        'sectornum',   'serial',     '',      '', '', '',
-        'towernum',       'int',     '',      '', '', '',
-        'sectorname', 'varchar',     '', $char_d, '', '',
-        'ip_addr',    'varchar', 'NULL',      15, '', '',
-        'height',     'decimal', 'NULL',      '', '', '', 
-        'freq_mhz',       'int', 'NULL',      '', '', '',
-        'direction',      'int', 'NULL',      '', '', '',
-        'width',          'int', 'NULL',      '', '', '',
+        'sectornum',     'serial',     '',      '', '', '',
+        'towernum',         'int',     '',      '', '', '',
+        'sectorname',   'varchar',     '', $char_d, '', '',
+        'ip_addr',      'varchar', 'NULL',      15, '', '',
+        'height',       'decimal', 'NULL',      '', '', '', 
+        'freq_mhz',         'int', 'NULL',      '', '', '',
+        'direction',        'int', 'NULL',      '', '', '',
+        'width',            'int', 'NULL',      '', '', '',
         #downtilt etc? rfpath has profile files for devices/antennas you upload?
-        'range',      'decimal', 'NULL',      '', '', '',  #?
+        'sector_range', 'decimal', 'NULL',      '', '', '',  #?
       ],
       'primary_key'  => 'sectornum',
       'unique'       => [ [ 'towernum', 'sectorname' ], [ 'ip_addr' ], ],
@@ -4710,7 +4737,7 @@ sub tables_hashref {
       'columns' => [
         'pkgusagepart', 'serial',   '', '', '', '',
         'pkgpart',  'int',      '', '', '', '',
-        'minutes',  'int',      '', '', '', '',
+        'minutes',  'double precision',      '', '', '', '',
         'priority', 'int',  'NULL', '', '', '',
         'shared',   'char', 'NULL',  1, '', '',
         'rollover', 'char', 'NULL',  1, '', '',
@@ -4745,14 +4772,32 @@ sub tables_hashref {
                         ],
     },
 
+    'part_pkg_fcc_option' => {
+      'columns' => [
+        'num',        'serial', '', '', '', '',
+        'fccoptionname', 'varchar', '', $char_d, '', '',
+        'pkgpart',       'int', '', '', '', '',
+        'optionvalue',   'varchar', 'NULL', $char_d, '', '',
+      ],
+      'primary_key' => 'num',
+      'unique'      => [ [ 'fccoptionname', 'pkgpart' ] ],
+      'index'       => [],
+    },
+
     'rate' => {
       'columns' => [
-        'ratenum',  'serial', '', '', '', '', 
-        'ratename', 'varchar', '', $char_d, '', '', 
+        'ratenum',   'serial',     '',      '', '', '', 
+        'ratename', 'varchar',     '', $char_d, '', '', 
+        'agentnum',     'int', 'NULL',      '', '', '',
       ],
       'primary_key' => 'ratenum',
       'unique'      => [],
       'index'       => [],
+      'foreign_keys' => [
+                          { columns    => [ 'agentnum' ],
+                            table      => 'agent',
+                          },
+                        ],
     },
 
     'rate_detail' => {
@@ -4763,13 +4808,15 @@ sub tables_hashref {
         'dest_regionnum',  'int',     '',     '',      '', '', 
         'min_included',    'int',     '',     '',      '', '', 
         'conn_charge',     'decimal', '', '10,4', '0.0000', '',
+        'conn_cost',       'decimal', '', '10,4', '0.0000', '',
         'conn_sec',        'int',     '',     '',      '0', '',
         'min_charge',      'decimal', '', '10,5',       '', '',
+        'min_cost',        'decimal', '', '10,5','0.00000', '',
         'sec_granularity', 'int',     '',     '',       '', '', 
         'ratetimenum',     'int', 'NULL',     '',       '', '',
         'classnum',        'int', 'NULL',     '',       '', '', 
         'cdrtypenum',      'int', 'NULL',     '',       '', '',
-        'region_group', 'char', 'NULL',        1,       '', '', 
+        'region_group',   'char', 'NULL',      1,       '', '', 
       ],
       'primary_key'  => 'ratedetailnum',
       'unique'       => [ [ 'ratenum', 'orig_regionnum', 'dest_regionnum' ] ],
@@ -5049,11 +5096,12 @@ sub tables_hashref {
 
     'pkg_category' => {
       'columns' => [
-        'categorynum',   'serial',  '', '', '', '', 
-        'categoryname',  'varchar', '', $char_d, '', '', 
-        'weight',         'int', 'NULL',  '', '', '',
-        'condense',      'char', 'NULL',   1, '', '', 
-        'disabled',      'char', 'NULL',   1, '', '', 
+        'categorynum',        'serial',     '',      '', '', '', 
+        'categoryname',      'varchar',     '', $char_d, '', '', 
+        'weight',                'int', 'NULL',      '', '', '',
+        'ticketing_queueid',     'int', 'NULL',      '', '', '', 
+        'condense',             'char', 'NULL',       1, '', '', 
+        'disabled',             'char', 'NULL',       1, '', '', 
       ],
       'primary_key' => 'categorynum',
       'unique' => [],
@@ -5479,6 +5527,44 @@ sub tables_hashref {
       'index'  => [],
     },
 
+    'sched_item' => {
+      'columns' => [
+        'itemnum',   'serial',      '', '', '', '', 
+        'usernum',      'int',  'NULL', '', '', '', 
+        #'itemname', 'varchar', $char_d, '', '', '',
+        'disabled',    'char',  'NULL',  1, '', '', 
+      ],
+      'primary_key'  => 'itemnum',
+      'unique'       => [ [ 'usernum' ] ],
+      'index'        => [],
+      'foreign_keys' => [
+                          { columns    => [ 'usernum' ],
+                            table      => 'access_user',
+                          },
+                        ],
+    },
+
+    #'sched_item_class'
+
+    'sched_avail' => {
+      'columns' => [
+        'availnum',      'serial', '', '', '', '', 
+        'itemnum',          'int', '', '', '', '',
+        'wday',             'int', '', '', '', '',
+        'stime',            'int', '', '', '', '',
+        'etime',            'int', '', '', '', '',
+        'override_date',    @date_type,    '', '',
+      ],
+      'primary_key'  => 'availnum',
+      'unique'       => [],
+      'index'        => [],
+      'foreign_keys' => [
+                          { columns    => [ 'itemnum' ],
+                            table      => 'sched_item',
+                          },
+                        ],
+    },
+
     'svc_phone' => {
       'columns' => [
         'svcnum',                         'int',     '',      '', '', '', 
@@ -5504,6 +5590,8 @@ sub tables_hashref {
         'sms_carrierid',                  'int', 'NULL',      '', '', '',
         'sms_account',                'varchar', 'NULL', $char_d, '', '',
         'max_simultaneous',               'int', 'NULL',      '', '', '',
+        'e911_class',                    'char', 'NULL',       1, '', '',
+        'e911_type',                     'char', 'NULL',       1, '', '', 
       ],
       'primary_key'  => 'svcnum',
       'unique'       => [ [ 'sms_carrierid', 'sms_account'] ],
@@ -6160,7 +6248,7 @@ sub tables_hashref {
       'columns' => [
         'logcontextnum', 'serial', '', '', '', '',
         'lognum', 'int', '', '', '', '',
-        'context', 'varchar', '', 32, '', '',
+        'context', 'varchar', '', $char_d, '', '',
       ],
       'primary_key'  => 'logcontextnum',
       'unique'       => [ [ 'lognum', 'context' ] ],
@@ -6405,7 +6493,8 @@ sub tables_hashref {
       'columns' => [
         'vendbillnum',    'serial',     '',      '', '', '', 
         'vendnum',           'int',     '',      '', '', '', 
-        '_date',        @date_type,                  '', '', 
+        #'_date',        @date_type,                  '', '', 
+        '_date',     'int', '', '',                   '', '', 
         'charged',     @money_type,                  '', '', 
       ],
       'primary_key'  => 'vendbillnum',
@@ -6422,7 +6511,8 @@ sub tables_hashref {
       'columns' => [
         'vendpaynum',   'serial',    '',       '', '', '',
         'vendnum',         'int',    '',       '', '', '', 
-        '_date',     @date_type,                   '', '', 
+        #'_date',     @date_type,                   '', '', 
+        '_date',     'int', '', '',                   '', '', 
         'paid',      @money_type,                  '', '', 
       ],
       'primary_key'  => 'vendpaynum',
@@ -6531,6 +6621,130 @@ sub tables_hashref {
                         ],
     },
 
+    'export_batch' => {
+      'columns' => [
+        'batchnum',    'serial',     '',      '', '', '',
+        'exportnum',      'int',     '',      '', '', '',
+        '_date',          'int',     '',      '', '', '',
+        'status',     'varchar', 'NULL',      32, '', '',
+        'statustext',    'text', 'NULL',      '', '', '',
+      ],
+      'primary_key'  => 'batchnum',
+      'unique'       => [],
+      'index'        => [ [ 'exportnum' ], [ 'status' ] ],
+      'foreign_keys' => [
+                          { columns    => [ 'exportnum' ],
+                            table      => 'part_export',
+                            references => [ 'exportnum' ]
+                          },
+                        ],
+    },
+
+    'export_batch_item' => {
+      'columns' => [
+        'itemnum',     'serial',     '',      '', '', '',
+        'batchnum',       'int',     '',      '', '', '',
+        'svcnum',         'int',     '',      '', '', '',
+        'action',     'varchar',     '',      32, '', '',
+        'data',          'text', 'NULL',      '', '', '',
+        'frozen',        'char', 'NULL',       1, '', '',
+      ],
+      'primary_key'  => 'itemnum',
+      'unique'       => [],
+      'index'        => [ [ 'batchnum' ], [ 'svcnum' ] ],
+      'foreign_keys' => [
+                          { columns    => [ 'batchnum' ],
+                            table      => 'export_batch',
+                            references => [ 'batchnum' ]
+                          },
+                        ],
+    },
+
+    # lookup table for states, similar to msa and lata
+    'state' => {
+      'columns' => [
+        'statenum', 'int',  '', '', '', '', 
+        'country',  'char', '',  2, '', '',
+        'state',    'char', '', $char_d, '', '', 
+        'fips',     'char', '',  3, '', '',
+      ],
+      'primary_key' => 'statenum',
+      'unique' => [ [ 'country', 'state' ], ],
+      'index' => [],
+    },
+
+    # eventually link to tower/sector?
+    'deploy_zone' => {
+      'columns' => [
+        'zonenum',        'serial',  '',     '',      '', '',
+        'description',    'char',    'NULL', $char_d, '', '',
+        'agentnum',       'int',     '',     '',      '', '',
+        'dbaname',        'char',    'NULL', $char_d, '', '',
+        'zonetype',       'char',    '',     1,       '', '',
+        'technology',     'int',     '',     '',      '', '',
+        'spectrum',       'int',     'NULL', '',      '', '',
+        'adv_speed_up',   'decimal', '',     '10,3', '0', '',
+        'adv_speed_down', 'decimal', '',     '10,3', '0', '',
+        'cir_speed_up',   'decimal', '',     '10,3', '0', '',
+        'cir_speed_down', 'decimal', '',     '10,3', '0', '',
+        'is_broadband',   'char',    'NULL', 1,       '', '',
+        'is_voice',       'char',    'NULL', 1,       '', '',
+        'is_consumer',    'char',    'NULL', 1,       '', '',
+        'is_business',    'char',    'NULL', 1,       '', '',
+        'active_date',    @date_type,                 '', '',
+        'expire_date',    @date_type,                 '', '',
+      ],
+      'primary_key' => 'zonenum',
+      'unique' => [],
+      'index'  => [ [ 'agentnum' ] ],
+      'foreign_keys' => [
+                          { columns     => [ 'agentnum' ],
+                            table       => 'agent',
+                            references  => [ 'agentnum' ],
+                          },
+                        ],
+    },
+
+    'deploy_zone_block' => {
+      'columns' => [
+        'blocknum',       'serial',  '',     '',      '', '',
+        'zonenum',        'int',     '',     '',      '', '',
+        'censusblock',    'char',    '',     15,      '', '',
+        'censusyear',     'char',    '',      4,      '', '',
+      ],
+      'primary_key' => 'blocknum',
+      'unique' => [],
+      'index'  => [ [ 'zonenum' ] ],
+      'foreign_keys' => [
+                          { columns     => [ 'zonenum' ],
+                            table       => 'deploy_zone',
+                            references  => [ 'zonenum' ],
+                          },
+                        ],
+    },
+
+    'deploy_zone_vertex' => {
+      'columns' => [
+        'vertexnum',      'serial',  '',     '',      '', '',
+        'zonenum',        'int',     '',     '',      '', '',
+        'latitude',       'decimal', '',     '10,7',  '', '', 
+        'longitude',      'decimal', '',     '10,7',  '', '', 
+      ],
+      'primary_key' => 'vertexnum',
+      'unique' => [ ],
+      'index'  => [ ],
+      'foreign_keys' => [
+                          { columns     => [ 'zonenum' ],
+                            table       => 'deploy_zone',
+                            references  => [ 'zonenum' ],
+                          },
+                        ],
+    },
+
+
+
+
+
     # name type nullability length default local
 
     #'new_table' => {