-- phpMyAdmin SQL Dump
-- version 2.9.1
-- http://www.phpmyadmin.net
--
-- Host: PhreeSoft, LLC
-- MySQL Server version: 5.0.26
-- PHP Version: 5.2.0
-- File Location: /modules/install/sql/current/tables.sql
-- --------------------------------------------------------
--
-- Table structure for table accounting_periods
--
CREATE TABLE accounting_periods (
period int(11) NOT NULL default '0',
fiscal_year int(11) NOT NULL default '0',
start_date date NOT NULL default '0000-00-00',
end_date date NOT NULL default '0000-00-00',
date_added date NOT NULL default '0000-00-00',
last_update timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (period)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Defines accounting periods and dates';
-- --------------------------------------------------------
--
-- Table structure for table accounts_history
--
CREATE TABLE accounts_history (
id int(11) NOT NULL auto_increment,
ref_id int(11) NOT NULL default '0',
acct_id int(11) NOT NULL default '0',
amount double NOT NULL default '0',
journal_id int(2) NOT NULL default '0',
purchase_invoice_id char(24) default NULL,
so_po_ref_id int(11) default NULL,
post_date datetime default NULL,
PRIMARY KEY (id),
KEY acct_id (acct_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Customer/Vendor Activity History' ;
-- --------------------------------------------------------
--
-- Table structure for table address_book
--
CREATE TABLE address_book (
address_id int(11) NOT NULL auto_increment,
ref_id int(11) NOT NULL default '0',
type char(2) NOT NULL default '',
primary_name varchar(32) NOT NULL default '',
contact varchar(32) NOT NULL default '',
address1 varchar(32) NOT NULL default '',
address2 varchar(32) NOT NULL default '',
city_town varchar(24) NOT NULL default '',
state_province varchar(24) NOT NULL default '',
postal_code varchar(10) NOT NULL default '',
country_code char(3) NOT NULL default '',
telephone1 VARCHAR(20) NULL DEFAULT '',
telephone2 VARCHAR(20) NULL DEFAULT '',
telephone3 VARCHAR(20) NULL DEFAULT '',
telephone4 VARCHAR(20) NULL DEFAULT '',
email VARCHAR(48) NULL DEFAULT '',
website VARCHAR(48) NULL DEFAULT '',
notes text,
PRIMARY KEY (address_id),
KEY customer_id (ref_id,type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;
-- --------------------------------------------------------
--
-- Table structure for table audit_log
--
CREATE TABLE audit_log (
id int(15) NOT NULL auto_increment,
action_date timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
user_id int(11) NOT NULL default '0',
reference_id varchar(32) NOT NULL default '',
action varchar(64) default NULL,
amount float(10,2) NOT NULL default '0.00',
PRIMARY KEY (id),
KEY idx_page_accessed_zen (reference_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;
-- --------------------------------------------------------
--
-- Table structure for table chart_of_accounts
--
CREATE TABLE chart_of_accounts (
id char(15) NOT NULL default '',
description char(64) NOT NULL default '',
subaccount enum('0','1') NOT NULL default '0',
primary_acct_id char(15) default NULL,
account_type tinyint(4) NOT NULL default '0',
next_ref char(15) NOT NULL default '',
account_inactive enum('0','1') NOT NULL default '0',
PRIMARY KEY (id),
KEY type (account_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;
-- --------------------------------------------------------
--
-- Table structure for table chart_of_accounts_history
--
CREATE TABLE chart_of_accounts_history (
id int(11) NOT NULL auto_increment,
period int(11) NOT NULL default '0',
account_id char(15) NOT NULL default '',
beginning_balance double NOT NULL default '0',
debit_amount double NOT NULL default '0',
credit_amount double NOT NULL default '0',
budget double NOT NULL default '0',
last_update date NOT NULL default '0000-00-00',
PRIMARY KEY (id),
KEY period (period),
KEY account_id (account_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Chart of Accounts Historical Data' ;
-- --------------------------------------------------------
--
-- Table structure for table chart_of_accounts_types
--
CREATE TABLE chart_of_accounts_types (
id tinyint(4) NOT NULL default '0',
description varchar(30) NOT NULL default '',
asset enum('0','1') NOT NULL default '0',
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- --------------------------------------------------------
--
-- Table structure for table configuration
--
CREATE TABLE configuration (
configuration_id int(11) NOT NULL auto_increment,
configuration_title text,
configuration_key varchar(255) NOT NULL default '',
configuration_value text,
configuration_description text,
configuration_group_id int(11) NOT NULL default '0',
sort_order int(5) default NULL,
last_modified datetime default NULL,
date_added datetime NOT NULL default '0001-01-01 00:00:00',
use_function text,
set_function text,
PRIMARY KEY (configuration_id),
UNIQUE KEY unq_config_key_zen (configuration_key),
KEY idx_cfg_grp_id_zen (configuration_group_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;
-- --------------------------------------------------------
--
-- Table structure for table configuration_group
--
CREATE TABLE configuration_group (
configuration_group_id int(11) NOT NULL auto_increment,
configuration_group_title varchar(64) NOT NULL default '',
configuration_group_description varchar(255) NOT NULL default '',
sort_order int(5) default NULL,
visible int(1) default '1',
PRIMARY KEY (configuration_group_id),
KEY idx_visible_zen (visible)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;
-- --------------------------------------------------------
--
-- Table structure for table contacts
--
CREATE TABLE contacts (
id int(11) NOT NULL auto_increment,
type char(1) NOT NULL default 'c',
short_name varchar(20) NOT NULL default '',
inactive enum('0','1') NOT NULL default '0',
contact_first varchar(32) default NULL,
contact_middle varchar(32) default NULL,
contact_last varchar(32) default NULL,
store_id varchar(15) NOT NULL default '',
gl_type_account varchar(15) NOT NULL default '',
gov_id_number varchar(16) NOT NULL default '',
dept_rep_id varchar(16) NOT NULL default '',
account_number varchar(16) NOT NULL default '',
special_terms varchar(32) NOT NULL default '0',
first_date date NOT NULL default '0000-00-00',
last_update date default NULL,
last_date_1 date default NULL,
last_date_2 date default NULL,
PRIMARY KEY (id),
KEY type (type),
KEY short_name (short_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;
-- --------------------------------------------------------
--
-- Table structure for table countries
--
CREATE TABLE countries (
countries_id int(11) NOT NULL auto_increment,
countries_name varchar(64) NOT NULL default '',
countries_iso_code_2 char(2) NOT NULL default '',
countries_iso_code_3 char(3) NOT NULL default '',
address_format_id int(11) NOT NULL default '0',
PRIMARY KEY (countries_id),
KEY idx_countries_name_zen (countries_name)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;
-- --------------------------------------------------------
--
-- Table structure for table currencies
--
CREATE TABLE currencies (
currencies_id int(11) NOT NULL auto_increment,
title varchar(32) NOT NULL default '',
code char(3) NOT NULL default '',
symbol_left varchar(24) default NULL,
symbol_right varchar(24) default NULL,
decimal_point char(1) default NULL,
thousands_point char(1) default NULL,
decimal_places char(1) default NULL,
value float(13,8) default NULL,
last_updated datetime default NULL,
PRIMARY KEY (currencies_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;
-- --------------------------------------------------------
--
-- Table structure for table current_status
--
CREATE TABLE current_status (
id int(11) NOT NULL auto_increment,
next_po_num varchar(16) NOT NULL default '1',
next_so_num varchar(16) NOT NULL default '1',
next_inv_num varchar(16) NOT NULL default '1',
next_check_num varchar(16) NOT NULL default '1',
next_deposit_num varchar(16) NOT NULL default '1',
next_shipment_num varchar(16) NOT NULL default '1',
next_cm_num varchar(16) NOT NULL default '1',
next_vcm_num varchar(16) NOT NULL default '1',
next_ap_quote_num varchar(16) NOT NULL default '1',
next_ar_quote_num varchar(16) NOT NULL default '1',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='System values for entry identification' ;
-- --------------------------------------------------------
--
-- Table structure for table data_security
--
CREATE TABLE data_security (
id int(11) NOT NULL auto_increment,
module varchar(32) NOT NULL DEFAULT '',
ref_1 int(11) NOT NULL DEFAULT '0',
ref_2 int(11) NOT NULL DEFAULT '0',
hint varchar(255) NOT NULL DEFAULT '',
enc_value varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Encryption data for storage';
-- --------------------------------------------------------
--
-- Table structure for table departments
--
CREATE TABLE departments (
id char(15) NOT NULL default '',
description varchar(30) NOT NULL default '',
subdepartment enum('0','1') NOT NULL default '0',
primary_dept_id char(15) default NULL,
department_type tinyint(4) NOT NULL default '0',
department_inactive enum('0','1') NOT NULL default '0',
PRIMARY KEY (id),
KEY type (department_type)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Departments for employee organization';
-- --------------------------------------------------------
--
-- Table structure for table departments_types
--
CREATE TABLE departments_types (
id int(11) NOT NULL auto_increment,
description varchar(30) NOT NULL default '',
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Type descriptions for the departments' ;
-- --------------------------------------------------------
--
-- Table structure for table import_export
--
CREATE TABLE import_export (
id int(11) NOT NULL auto_increment,
group_id char(3) NOT NULL default '',
custom enum('0','1') NOT NULL default '1',
security varchar(10) NOT NULL default '',
title varchar(32) NOT NULL default '',
description varchar(255) NOT NULL default '',
table_name varchar(32) NOT NULL default '',
primary_key_field varchar(32) NOT NULL default '',
params text,
criteria text,
options text,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Import-Export specifications' ;
-- --------------------------------------------------------
--
-- Table structure for table inventory
--
CREATE TABLE inventory (
id int(11) NOT NULL auto_increment,
sku varchar(15) NOT NULL default '',
inactive enum('0','1') NOT NULL default '0',
inventory_type char(2) NOT NULL default 'si',
description_short varchar(32) NOT NULL default '',
description_purchase varchar(255) default NULL,
image_with_path varchar(255) default NULL,
account_sales_income varchar(15) default NULL,
account_inventory_wage varchar(15) default '',
account_cost_of_sales varchar(15) default NULL,
item_taxable int(11) NOT NULL default '0',
item_cost float NOT NULL default '0',
cost_method enum('a','f','l') NOT NULL default 'f',
full_price float NOT NULL default '0',
item_weight float NOT NULL default '0',
quantity_on_hand float NOT NULL default '0',
quantity_on_order float NOT NULL default '0',
quantity_on_sales_order float NOT NULL default '0',
minimum_stock_level float NOT NULL default '0',
reorder_quantity float NOT NULL default '0',
vendor_id int(11) NOT NULL default '0',
lead_time int(3) NOT NULL default '1',
serialize enum('0','1') NOT NULL default '0',
creation_date datetime NOT NULL default '0000-00-00 00:00:00',
last_update datetime NOT NULL default '0000-00-00 00:00:00',
last_journal_date datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT = 'Primary inventory table';
-- --------------------------------------------------------
--
-- Table structure for table inventory_assy_list
--
CREATE TABLE inventory_assy_list (
id int(11) NOT NULL auto_increment,
ref_id int(11) NOT NULL default '0',
sku varchar(15) NOT NULL default '',
description varchar(32) NOT NULL default '',
qty float NOT NULL default '0',
PRIMARY KEY (id),
KEY ref_id (ref_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Holds assembly list for inventory items of type: as' ;
-- --------------------------------------------------------
--
-- Table structure for table inventory_categories
--
CREATE TABLE inventory_categories (
category_id int(3) NOT NULL auto_increment,
category_name varchar(32) NOT NULL default '',
sort_order int(2) NOT NULL default '0',
category_description varchar(80) NOT NULL default '',
PRIMARY KEY (category_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;
-- --------------------------------------------------------
--
-- Table structure for table inventory_cogs_owed
--
CREATE TABLE inventory_cogs_owed (
id int(11) NOT NULL auto_increment,
journal_main_id int(11) NOT NULL default '0',
store_id int(11) NOT NULL default '0',
sku varchar(15) NOT NULL default '',
qty float NOT NULL default '0',
post_date date NOT NULL default '0000-00-00',
PRIMARY KEY (id),
KEY sku (sku),
INDEX (store_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT = 'Stores inventory items not in stock for future posting';
-- --------------------------------------------------------
--
-- Table structure for table inventory_cogs_usage
--
CREATE TABLE inventory_cogs_usage (
id int(11) NOT NULL auto_increment,
journal_main_id int(11) NOT NULL default '0',
qty float NOT NULL default '0',
inventory_history_id int(11) NOT NULL default '0',
PRIMARY KEY (id),
INDEX (journal_main_id, inventory_history_id)
) ENGINE=innodb DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT = 'Relates inventory usage with inventory purchase history';
-- --------------------------------------------------------
--
-- Table structure for table inventory_fields
--
CREATE TABLE inventory_fields (
inv_field_id int(10) NOT NULL auto_increment,
entry_type varchar(20) NOT NULL default '',
field_name varchar(32) NOT NULL default '',
description varchar(64) NOT NULL default '',
category_id int(11) NOT NULL default '0',
params text,
PRIMARY KEY (inv_field_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;
-- --------------------------------------------------------
--
-- Table structure for table inventory_history
--
CREATE TABLE inventory_history (
id int(11) NOT NULL auto_increment,
ref_id int(11) NOT NULL default '0',
store_id int(11) NOT NULL default '0',
sku varchar(15) NOT NULL default '',
qty float NOT NULL default '0',
serialize_number varchar(24) NOT NULL default '',
remaining float NOT NULL default '0',
unit_cost float NOT NULL default '0',
post_date datetime default NULL,
PRIMARY KEY (id),
KEY sku (sku),
KEY ref_id (ref_id),
KEY remaining (remaining),
INDEX (store_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Inventory Purchase History Table';
-- --------------------------------------------------------
--
-- Table structure for table inventory_ms_list
--
CREATE TABLE inventory_ms_list (
id int(11) NOT NULL auto_increment,
sku varchar(15) NOT NULL default '',
attr_name_0 varchar(16) NULL,
attr_name_1 varchar(16) NULL,
attr_0 varchar(255) NULL,
attr_1 varchar(255) NULL,
PRIMARY KEY (id)
) ENGINE = InnoDB CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT = 'Holds inventory master stock attribute information';
-- --------------------------------------------------------
--
-- Table structure for table inventory_special_prices
--
CREATE TABLE inventory_special_prices (
id int(11) NOT NULL auto_increment,
inventory_id int(11) NOT NULL default '0',
price_sheet_id int(11) NOT NULL default '0',
price_levels varchar(255) NOT NULL default '',
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;
-- --------------------------------------------------------
--
-- Table structure for table journal_item
--
CREATE TABLE journal_item (
id int(11) NOT NULL auto_increment,
ref_id int(11) NOT NULL default '0',
so_po_item_ref_id int(11) default NULL,
gl_type char(3) NOT NULL default '',
reconciled BOOL NOT NULL DEFAULT '0',
sku varchar(15) default NULL,
qty float NOT NULL default '0',
description varchar(255) default NULL,
debit_amount double default '0',
credit_amount double default '0',
gl_account varchar(15) NOT NULL default '',
taxable int(11) NOT NULL default '0',
serialize enum('0','1') NOT NULL default '0',
serialize_number varchar(24) default NULL,
post_date date NOT NULL default '0000-00-00',
date_1 datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (id),
KEY ref_id (ref_id),
KEY reconciled (reconciled)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Journal Item Entry (many to one to journal_main)' ;
-- --------------------------------------------------------
--
-- Table structure for table journal_main
--
CREATE TABLE journal_main (
id int(11) NOT NULL auto_increment,
period int(2) NOT NULL default '0',
journal_id int(2) NOT NULL default '0',
post_date date NOT NULL default '0000-00-00',
store_id int(11) default '0',
description varchar(32) default NULL,
closed enum('0','1') NOT NULL default '0',
freight double default '0',
shipper_code varchar(16) NOT NULL default '',
terms varchar(32) default '0',
sales_tax double NOT NULL default '0',
tax_auths varchar(16) NOT NULL default '0',
total_amount double NOT NULL default '0',
currencies_code char(3) NOT NULL DEFAULT '',
currencies_value DOUBLE NOT NULL DEFAULT '1.0',
so_po_ref_id int(11) NOT NULL default '0',
purchase_invoice_id varchar(24) default NULL,
purch_order_id varchar(24) default NULL,
recur_id int(11) default NULL,
dept_rep_id varchar(16) default NULL,
waiting enum('0','1') NOT NULL default '0',
gl_acct_id varchar(15) default NULL,
bill_acct_id int(11) NOT NULL default '0',
bill_address_id int(11) NOT NULL default '0',
bill_primary_name varchar(32) default NULL,
bill_contact varchar(32) default NULL,
bill_address1 varchar(32) default NULL,
bill_address2 varchar(32) default NULL,
bill_city_town varchar(24) default NULL,
bill_state_province varchar(24) default NULL,
bill_postal_code varchar(10) default NULL,
bill_country_code char(3) default NULL,
bill_telephone1 varchar(20) default NULL,
bill_email varchar(48) default NULL,
ship_acct_id int(11) NOT NULL default '0',
ship_address_id int(11) NOT NULL default '0',
ship_primary_name varchar(32) default NULL,
ship_contact varchar(32) default NULL,
ship_address1 varchar(32) default NULL,
ship_address2 varchar(32) default NULL,
ship_city_town varchar(24) default NULL,
ship_state_province varchar(24) default NULL,
ship_postal_code varchar(24) default NULL,
ship_country_code char(3) default NULL,
ship_telephone1 varchar(20) default NULL,
ship_email varchar(48) default NULL,
terminal_date date default NULL,
drop_ship enum('0','1') NOT NULL default '0',
PRIMARY KEY (id),
KEY period (period),
KEY journal_id (journal_id),
KEY post_date (post_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Journal Entry Table for Headings' ;
-- --------------------------------------------------------
--
-- Table structure for table price_sheets
--
CREATE TABLE price_sheets (
id int(11) NOT NULL auto_increment,
sheet_name varchar(32) NOT NULL default '',
inactive enum('0','1') NOT NULL default '0',
revision float NOT NULL default '0',
effective_date date NOT NULL default '0000-00-00',
expiration_date date default NULL,
default_sheet enum('0','1') NOT NULL default '0',
default_levels varchar(255) NOT NULL default '',
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;
-- --------------------------------------------------------
--
-- Table structure for table project_version
--
CREATE TABLE project_version (
project_version_id tinyint(3) NOT NULL auto_increment,
project_version_key varchar(40) NOT NULL default '',
project_version_major varchar(20) NOT NULL default '',
project_version_minor varchar(20) NOT NULL default '',
project_version_comment varchar(250) NOT NULL default '',
project_version_date_applied datetime NOT NULL default '0001-01-01 01:01:01',
PRIMARY KEY (project_version_id),
UNIQUE KEY idx_project_version_key_zen (project_version_key)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Database Version Tracking' ;
-- --------------------------------------------------------
--
-- Table structure for table reconciliation
--
CREATE TABLE reconciliation (
id int(11) NOT NULL auto_increment,
period int(11) NOT NULL default '0',
gl_account varchar(15) NOT NULL default '',
statement_balance double NOT NULL default '0',
cleared_items text,
PRIMARY KEY (id),
KEY period (period)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;
-- --------------------------------------------------------
--
-- Table structure for table report_fields
--
CREATE TABLE report_fields (
id int(8) NOT NULL auto_increment,
reportid int(5) NOT NULL default '0',
entrytype varchar(15) NOT NULL default '',
seqnum int(3) NOT NULL default '0',
fieldname varchar(255) NOT NULL default '',
displaydesc varchar(25) NOT NULL default '',
visible enum('1','0') NOT NULL default '1',
columnbreak enum('1','0') NOT NULL default '1',
params text,
PRIMARY KEY (id),
KEY reportid (reportid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;
-- --------------------------------------------------------
--
-- Table structure for table reports
--
CREATE TABLE reports (
id int(5) NOT NULL auto_increment,
description varchar(30) NOT NULL default '',
narrative varchar(255) NOT NULL default '',
reporttype char(3) NOT NULL default 'rpt',
groupname varchar(9) NOT NULL default 'misc',
standard_report enum('1','0') NOT NULL default '1',
special_report varchar(32) NOT NULL default '0',
table1 varchar(25) NOT NULL default '',
table2 varchar(25) default NULL,
table2criteria varchar(75) default NULL,
table3 varchar(25) default NULL,
table3criteria varchar(75) default NULL,
table4 varchar(25) default NULL,
table4criteria varchar(75) default NULL,
table5 varchar(25) default NULL,
table5criteria varchar(75) default NULL,
table6 varchar(25) default NULL,
table6criteria varchar(75) default NULL,
PRIMARY KEY (id),
KEY name (description,groupname)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;
-- --------------------------------------------------------
--
-- Table structure for table shipping_log
--
CREATE TABLE shipping_log (
id int(11) NOT NULL auto_increment,
shipment_id int(11) NOT NULL default '0',
ref_id int(11) NOT NULL default '0',
reconciled enum('0','1') NOT NULL default '0',
carrier varchar(16) NOT NULL default '',
method varchar(8) NOT NULL default '',
ship_date date NOT NULL default '0000-00-00',
deliver_date date NOT NULL default '0000-00-00',
actual_date date NOT NULL default '0000-00-00',
tracking_id varchar(32) NOT NULL default '',
cost float NOT NULL default '0',
notes varchar(255) NOT NULL default '',
PRIMARY KEY (id),
KEY ref_id (ref_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;
-- --------------------------------------------------------
--
-- Table structure for table tax_authorities
--
CREATE TABLE tax_authorities (
tax_auth_id int(3) NOT NULL auto_increment,
description_short char(15) NOT NULL default '',
description_long char(64) NOT NULL default '',
account_id char(15) NOT NULL default '',
vendor_id int(5) NOT NULL default '0',
tax_rate float NOT NULL default '0',
PRIMARY KEY (tax_auth_id),
KEY description_short (description_short)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Sales Tax Authorities' ;
-- --------------------------------------------------------
--
-- Table structure for table tax_rates
--
CREATE TABLE tax_rates (
tax_rate_id int(3) NOT NULL auto_increment,
description_short varchar(15) NOT NULL default '',
description_long varchar(64) NOT NULL default '',
rate_accounts varchar(64) NOT NULL default '',
freight_taxable enum('0','1') NOT NULL default '0',
PRIMARY KEY (tax_rate_id),
KEY description_short (description_short)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Sales Tax Authorities' ;
-- --------------------------------------------------------
--
-- Table structure for table users
--
CREATE TABLE users (
admin_id int(11) NOT NULL auto_increment,
admin_name varchar(32) NOT NULL default '',
inactive enum('0','1') NOT NULL default '0',
display_name varchar(32) NOT NULL default '',
admin_email varchar(96) NOT NULL default '',
admin_pass varchar(40) NOT NULL default '',
account_id int(11) NOT NULL default '0',
admin_store_id int(11) NOT NULL default '0',
admin_prefs text,
admin_security text,
PRIMARY KEY (admin_id),
KEY idx_admin_name_zen (admin_name)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;
-- --------------------------------------------------------
--
-- Table structure for table users_profiles
--
CREATE TABLE users_profiles (
id int(11) NOT NULL auto_increment,
user_id int(11) NOT NULL default '0',
page_id varchar(32) NOT NULL default '',
module_id varchar(32) NOT NULL default '',
column_id int(3) NOT NULL default '0',
row_id int(3) NOT NULL default '0',
params text,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='User profile information' ;
-- --------------------------------------------------------
--
-- Table structure for table zones
--
CREATE TABLE zones (
zone_id int(11) NOT NULL auto_increment,
countries_iso_code_3 char(3) NOT NULL default '',
zone_code varchar(32) NOT NULL default '',
zone_name varchar(32) NOT NULL default '',
PRIMARY KEY (zone_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='State/province mapping to country ISO3 code' ;
==========================================================================================================================
==========================================================================================================================
--
-- Dumping data for table `address_book`
--
INSERT INTO `address_book` VALUES (1, 1, 'vm', 'Obscene Video', '', '69 Triple X Ave', '', 'Chatsworth', 'CA', '93245', 'USA', '800.345.5678', '', '', '', 'obsvid@obscenevideo.com', '', '');
INSERT INTO `address_book` VALUES (2, 2, 'cm', 'CompuHouse Computer Systems', '', '8086 Intel Ave', '', 'San jose', 'CA', '94354', 'USA', '800-555-1234', '', '', '', 'sales@compuhouse.com', '', '');
INSERT INTO `address_book` VALUES (3, 3, 'vm', 'Speedy Electronics, Inc.', '', '777 Lucky Street', 'Unit #2B', 'San Jose', 'CA', '92666', 'USA', '802-555-9876', '', '', '', 'custserv@speedyelec.com', '', '');
INSERT INTO `address_book` VALUES (4, 4, 'cm', 'Computer Repair Services', '', '12932 136th Ave.', 'Suite A', 'Denver', 'CO', '80021', 'USA', '303-555-5469', '', '', '', 'servive@comprepair.net', '', '');
INSERT INTO `address_book` VALUES (5, 5, 'vm', 'LCDisplays Corp.', '', '28973 Pixel Place', '', 'Los Angeles', 'CA', '90001', 'USA', '800-555-5548', '', '', '', 'cs@lcdisplays.com', '', '');
INSERT INTO `address_book` VALUES (6, 6, 'vm', 'Big Box Corp', '', '11 Square St', '', 'Longmont', 'CO', '80501', 'USA', '303-555-9652', '', '', '', 'big.box@yahoo.com', '', '');
INSERT INTO `address_book` VALUES (7, 7, 'cm', 'John Smith Jr.', '', '13546 Euclid Ave', '', 'Ontario', 'CA', '92775', 'USA', '818-555-1000', '', '', '', 'jsmith@aol.com', '', '');
INSERT INTO `address_book` VALUES (8, 8, 'cm', 'Jim Baker', '', '995 Maple Street', 'Unit #56', 'Northglenn', 'CO', '80234', 'USA', 'unlisted', '', '', '', 'jb@hotmail.com', '', '');
INSERT INTO `address_book` VALUES (9, 9, 'cm', 'Lisa Culver', '', '1005 Gillespie Dr', '', 'Boulder', 'CO', '80303', 'USA', '303-555-6677', '', '', '', 'lisa@myveryownemailaddress.net', '', '');
INSERT INTO `address_book` VALUES (10, 10, 'cm', 'Parts Locator LLC', '', '55 Sydney Hwy', '', 'Deerfield Beach', 'FL', '33445', 'USA', '215-555-0987', '', '', '', 'parts@partslocator.com', '', '');
INSERT INTO `address_book` VALUES (11, 11, 'vm', 'Accurate Input, LLC', '', '1111 Stuck Key Ave', '', 'Burbank', 'CA', '91505', 'USA', '800-555-1267', '', '818-555-5555', '', 'sales@accurate.com', 'www.AccurateInput.com', '');
INSERT INTO `address_book` VALUES (12, 12, 'vm', 'BackMeUp Systems, Inc', '', '1324 44th Ave.', '', 'New York', 'NY', '10019', 'USA', '212-555-9854', '', '', '', 'sales@backmeup.com', '', '');
INSERT INTO `address_book` VALUES (13, 13, 'vm', 'Closed Cases', 'Fernando', '23 Frontage Rd', '', 'New York', 'NY', '10019', 'USA', '888-555-6322', '800-555-5716', '', '', 'custserv@closedcases.net', '', '');
INSERT INTO `address_book` VALUES (14, 14, 'vm', 'MegaWatts Power Supplies', '', '11 Joules St.', '', 'Denver', 'CO', '80234', 'USA', '303-222-5617', '', '', '', 'help@hotmail.com', '', '');
INSERT INTO `address_book` VALUES (15, 15, 'vm', 'Slipped Disk Corp.', 'Accts. Receivable', '1234 Main St', 'Suite #1', 'La Verne', 'CA', '91750', 'USA', '714-555-0001', '', '', '', 'sales@slippedisks.com', '', '');
INSERT INTO `address_book` VALUES (16, 16, 'em', '', '', '123 Birch Ave', 'Apt 12', 'Anytown', 'CO', '80234', 'USA', '303-555-3451', '', '', '', 'john@mycompany.com', '', '');
INSERT INTO `address_book` VALUES (17, 17, 'em', '', '', '6541 First St', '', 'Anytown', 'CO', '80234', 'USA', '303-555-7426', '', '', '', 'nary@mycomapny.com', '', '');
--
-- Dumping data for table `contacts`
--
INSERT INTO `contacts` VALUES (1, 'v', 'Obscene Video', '', '', '', '', '', '2000', '', '', '', '3:1:10:30:2500.00', '2007-05-01', '2007-06-14', NULL, NULL);
INSERT INTO `contacts` VALUES (2, 'c', 'CompuHouse', '', '', '', '', '', '4000', '', '', '', '0::::2500.00', '2007-05-01', '2007-05-01', NULL, NULL);
INSERT INTO `contacts` VALUES (3, 'v', 'Speedy Electronics', '', '', '', '', '', '2000', '', '', '', '0::::2500.00', '2007-05-01', '2007-06-14', NULL, NULL);
INSERT INTO `contacts` VALUES (4, 'c', 'Computer Repair', '', '', '', '', '', '4000', '', '', '', '0::::2500.00', '2007-05-01', '2007-05-01', NULL, NULL);
INSERT INTO `contacts` VALUES (5, 'v', 'LCDisplays', '', '', '', '', '', '2000', '', '', '', '0::::2500.00', '2007-05-01', '2007-06-14', NULL, NULL);
INSERT INTO `contacts` VALUES (6, 'v', 'Big Box', '', '', '', '', '', '2000', '', '', '', '0::::2500.00', '2007-05-01', '2007-06-14', NULL, NULL);
INSERT INTO `contacts` VALUES (7, 'c', 'Smith, John', '', '', '', '', '', '4000', '', '', '', '0::::2500.00', '2007-05-01', '2007-05-01', NULL, NULL);
INSERT INTO `contacts` VALUES (8, 'c', 'JimBaker', '', '', '', '', '', '4000', '', '', '', '0::::2500.00', '2007-05-01', '2007-05-01', NULL, NULL);
INSERT INTO `contacts` VALUES (9, 'c', 'Culver', '', '', '', '', '', '4000', '', '', '', '0::::2500.00', '2007-05-01', '2007-05-01', NULL, NULL);
INSERT INTO `contacts` VALUES (10, 'c', 'PartsLocator', '', '', '', '', '', '4000', '', '', '', '3:0:10:30:2500.00', '2007-05-01', '2007-05-01', NULL, NULL);
INSERT INTO `contacts` VALUES (11, 'v', 'Accurate Input', '', '', '', '', '', '2000', '', '', 'SK200706', '3:0:10:30:2500.00', '2007-06-14', '2007-06-14', NULL, NULL);
INSERT INTO `contacts` VALUES (12, 'v', 'BackMeUp', '', '', '', '', '', '2000', '', '', '', '0::::2500.00', '2007-06-14', '2007-06-14', NULL, NULL);
INSERT INTO `contacts` VALUES (13, 'v', 'Closed Cases', '', '', '', '', '', '2000', '', '', '', '0::::2500.00', '2007-06-14', '2007-06-14', NULL, NULL);
INSERT INTO `contacts` VALUES (14, 'v', 'MegaWatts', '', '', '', '', '', '2000', '', '', 'MW20070301', '0::::2500.00', '2007-06-14', '2007-06-14', NULL, NULL);
INSERT INTO `contacts` VALUES (15, 'v', 'Slipped Disk', '', '', '', '', '', '2000', '', '', '', '0::::2500.00', '2007-06-14', '2007-06-14', NULL, NULL);
INSERT INTO `contacts` VALUES (16, 'e', 'John', '', 'John', '', 'Smith', '', 'b', '', 'Sales', '', '::::', '2007-07-30', '2007-07-30', NULL, NULL);
INSERT INTO `contacts` VALUES (17, 'e', 'Mary', '', 'Mary', '', 'Johnson', '', 'e', '', 'Accounting', '', '::::', '2007-07-30', '2007-07-30', NULL, NULL);
--
-- Dumping data for table `departments`
--
INSERT INTO `departments` VALUES ('Sales', 'Sales', '0', '', 2, '0');
INSERT INTO `departments` VALUES ('Administration', 'Administration and Operations', '0', '', 1, '0');
INSERT INTO `departments` VALUES ('Accounting', 'Accounting and Finance', '0', '', 1, '0');
INSERT INTO `departments` VALUES ('Shipping', 'Shipping Warehouse', '0', '', 4, '0');
INSERT INTO `departments` VALUES ('Receiving', 'Materials Receiving', '0', '', 4, '0');
--
-- Dumping data for table `departments_types`
--
INSERT INTO `departments_types` VALUES (1, 'Administration');
INSERT INTO `departments_types` VALUES (2, 'Sales and Marketing');
INSERT INTO `departments_types` VALUES (3, 'Manufacturing');
INSERT INTO `departments_types` VALUES (4, 'Shipping & Receiving');
--
-- Dumping data for table `inventory`
--
INSERT INTO `inventory` VALUES (1, 'AMD-3600-CPU', '0', 'si', 'AMD 3600+ Athlon CPU', 'AMD 3600+ Athlon CPU', 'demo/athlon.jpg', '4000', '1200', '5000', '1', 100, 'f', 150, 1, 0, 0, 0, 0, 0, 3, 1, '0', '2007-04-20 11:08:16', '2007-06-14 11:50:41', '0000-00-00 00:00:00');
INSERT INTO `inventory` VALUES (2, 'ASSY-BB', '0', 'lb', 'Labor - BB Computer Assy', 'Labor Cost - Assemble Bare Bones Computer', '', '4000', '6000', '5000', '1', 25, 'f', 0, 0, 0, 0, 0, 0, 0, 0, 1, '0', '2007-04-23 10:58:42', '2007-06-14 09:25:45', '0000-00-00 00:00:00');
INSERT INTO `inventory` VALUES (3, 'BOX-TW-322', '0', 'si', 'TW-322 Shipping Box', 'TW-322 Shipping Box - 12 x 12 x 12', '', '4000', '6800', '5000', '1', 1.35, 'f', 0, 0, 0, 0, 0, 15, 25, 0, 1, '0', '2007-04-23 15:40:02', '2007-06-14 09:27:33', '0000-00-00 00:00:00');
INSERT INTO `inventory` VALUES (4, 'BOX-TW-553', '0', 'si', 'TW-533 Shipping Box', 'TW-533 Shipping Box - 24 x 12 x 12', '', '4000', '6800', '5000', '1', 1.75, 'f', 0, 0, 0, 0, 0, 0, 0, 0, 1, '0', '2007-04-23 15:42:07', '2007-06-14 09:28:11', '0000-00-00 00:00:00');
INSERT INTO `inventory` VALUES (5, 'CASE-ALIEN', '0', 'si', 'Alien Case - Red', 'Closed Cases - Red Fulll Tower ATX case w/o power supply', 'demo/red_alien.jpg', '4000', '1200', '5000', '1', 47, 'f', 98.26, 11, 0, 0, 0, 2, 1, 13, 5, '0', '2007-04-23 15:51:21', '2007-06-14 11:57:12', '2007-06-07 16:08:34');
INSERT INTO `inventory` VALUES (6, 'DESC-WARR', '0', 'ai', 'Warranty Template', 'Warranty Template', '', '1000', '1000', '1000', '1', 0, 'f', 0, 0, 0, 0, 0, 0, 0, 0, 1, '0', '2007-04-23 16:10:48', '2007-04-23 16:11:13', '0000-00-00 00:00:00');
INSERT INTO `inventory` VALUES (7, 'DVD-RW', '0', 'si', 'DVD RW with Lightscribe', 'DVD RW with Lightscribe - 8x', 'demo/lightscribe.jpg', '4000', '1200', '5000', '1', 23.6, 'f', 45, 2, 0, 0, 0, 3, 1, 15, 14, '0', '2007-04-24 10:00:55', '2007-06-14 12:04:37', '0000-00-00 00:00:00');
INSERT INTO `inventory` VALUES (8, 'HD-150GB', '0', 'si', '150GB SATA Hard Drive', '150GB SATA Hard Drive - 7200 RPM', 'demo/150gb_sata.jpg', '4000', '1200', '5000', '1', 27, 'f', 56, 2, 0, 0, 0, 10, 15, 15, 30, '0', '2007-04-24 13:20:26', '2007-06-14 12:08:54', '2007-05-05 12:01:30');
INSERT INTO `inventory` VALUES (9, 'KB-128-ERGO', '0', 'si', 'KeysRus ergonomic keyboard', 'KeysRus ergonomic keyboard - Lighted for Gaming', 'demo/ergo_key.jpg', '4000', '1200', '5000', '1', 23.51, 'f', 56.88, 0, 0, 0, 0, 5, 10, 11, 1, '0', '2007-06-14 09:51:56', '2007-06-14 12:10:58', '0000-00-00 00:00:00');
INSERT INTO `inventory` VALUES (10, 'LCD-21-WS', '0', 'si', 'LCDisplays 21" LCD Monitor', 'LCDisplays 21" LCD Monitor - wide screen w/anti-glare finish, Black', 'demo/monitor.jpg', '4000', '1200', '5000', '1', 145.01, 'f', 189.99, 0, 0, 0, 0, 2, 1, 5, 3, '0', '2007-06-14 09:53:36', '2007-06-14 12:27:43', '0000-00-00 00:00:00');
INSERT INTO `inventory` VALUES (11, 'MB-ATI-K8', '0', 'si', 'ATI K8 Motherboard', 'ATI-K8-TW AMD socket 939 Motherboard for Athlon Processors', 'demo/mobo.jpg', '4000', '1200', '5000', '1', 125, 'f', 155.25, 1, 0, 0, 0, 5, 10, 3, 3, '0', '2007-06-14 09:55:41', '2007-06-14 12:35:22', '0000-00-00 00:00:00');
INSERT INTO `inventory` VALUES (12, 'MB-ATI-K8N', '0', 'si', 'ATI K8 Motherboard w/network', 'ATI-K8-TW AMD socket 939 Motherboard for Athlon Processors with network ports', 'demo/mobo.jpg', '4000', '1200', '5000', '1', 135, 'f', 176.94, 1.2, 0, 0, 0, 3, 10, 3, 3, '0', '2007-06-14 09:57:08', '2007-06-14 13:20:29', '0000-00-00 00:00:00');
INSERT INTO `inventory` VALUES (13, 'Mouse-S', '0', 'si', 'Serial Mouse - 300 DPI', 'Serial Mouse - 300 DPI', 'demo/serial_mouse.JPG', '4000', '1200', '5000', '1', 4.85, 'f', 13.99, 0.6, 0, 0, 0, 15, 25, 11, 1, '0', '2007-06-14 09:58:19', '2007-06-14 12:46:00', '0000-00-00 00:00:00');
INSERT INTO `inventory` VALUES (14, 'PC-2GB-120GB-21', '0', 'as', 'Computer 2GB-120GB-21', 'Fully assembled computer AMD/ATI 2048GB Ram/1282 GB HD/Red Case/ Monitor/ Keyboard/ Mouse', 'demo/complete_computer.jpg', '4000', '1200', '5000', '1', 0, 'f', 750, 21.3, 0, 0, 0, 0, 0, 0, 1, '0', '2007-06-14 09:59:37', '2007-06-14 13:30:47', '0000-00-00 00:00:00');
INSERT INTO `inventory` VALUES (15, 'PS-450W', '0', 'si', '450 Watt Silent Power Supply', '850 Watt Silent Power Supply - for use with Intel or AMD processors', 'demo/power_supply.jpg', '4000', '1200', '5000', '1', 86.26, 'f', 124.5, 4.7, 0, 0, 0, 10, 6, 14, 5, '0', '2007-06-14 10:01:00', '2007-06-14 12:59:18', '0000-00-00 00:00:00');
INSERT INTO `inventory` VALUES (16, 'RAM-2GB-0.2', '0', 'si', '2GB SDRAM', '2 GB PC3200 Memory Modules - for Athlon processors', 'demo/2gbram.jpg', '4000', '1200', '5000', '1', 56.25, 'f', 89.65, 0, 0, 0, 0, 8, 10, 3, 2, '0', '2007-06-14 10:02:31', '2007-06-14 13:09:17', '0000-00-00 00:00:00');
INSERT INTO `inventory` VALUES (17, 'VID-NV-512MB', '0', 'si', 'nVidia 512 MB Video Card', 'nVidea 512 MB Video Card - with SLI support', 'demo/nvidia_512.jpg', '4000', '1200', '5000', '1', 0, 'f', 300, 0.7, 0, 0, 0, 4, 5, 1, 4, '0', '2007-06-14 10:03:32', '2007-07-30 15:31:09', '0000-00-00 00:00:00');
INSERT INTO `inventory` VALUES (18, 'PC-BB-512', '0', 'as', 'Bare Bones Computer 2600+/2GB', 'Fully assembled bare bones computer AMD/ATI 512MB/2GB/Red Case', 'demo/barebones.jpg', '4000', '1200', '5000', '1', 0, 'f', 750, 21.3, 0, 0, 0, 0, 0, 0, 1, '0', '2007-06-14 10:06:27', '2007-06-14 13:30:59', '0000-00-00 00:00:00');
--
-- Dumping data for table `inventory_assy_list`
--
INSERT INTO `inventory_assy_list` VALUES (1, 14, 'LCD-21-WS', 'LCDisplays 21', 1);
INSERT INTO `inventory_assy_list` VALUES (2, 14, 'HD-150GB', '150GB SATA Hard Drive', 1);
INSERT INTO `inventory_assy_list` VALUES (3, 14, 'DVD-RW', 'DVD RW with Lightscribe', 1);
INSERT INTO `inventory_assy_list` VALUES (4, 14, 'VID-NV-512MB', 'nVidea 512 MB Video Card', 1);
INSERT INTO `inventory_assy_list` VALUES (5, 14, 'RAM-2GB-0.2', '2GB SDRAM', 2);
INSERT INTO `inventory_assy_list` VALUES (6, 14, 'AMD-3600-CPU', 'AMD 3600+ Athlon CPU', 1);
INSERT INTO `inventory_assy_list` VALUES (7, 14, 'MB-ATI-K8N', 'ATI K8 Motherboard w/network', 1);
INSERT INTO `inventory_assy_list` VALUES (8, 14, 'CASE-ALIEN', 'Alien Case - Red', 1);
INSERT INTO `inventory_assy_list` VALUES (9, 14, 'Mouse-S', 'Serial Mouse - 300 DPI', 1);
INSERT INTO `inventory_assy_list` VALUES (10, 14, 'KB-128-ERGO', 'KeysRus ergonomic keyboard', 1);
INSERT INTO `inventory_assy_list` VALUES (11, 18, 'RAM-2GB-0.2', '2GB SDRAM', 2);
INSERT INTO `inventory_assy_list` VALUES (12, 18, 'AMD-3600-CPU', 'AMD 3600+ Athlon CPU', 1);
INSERT INTO `inventory_assy_list` VALUES (13, 18, 'MB-ATI-K8N', 'ATI K8 Motherboard w/network', 1);
INSERT INTO `inventory_assy_list` VALUES (14, 18, 'CASE-ALIEN', 'Alien Case - Red', 1);
INSERT INTO `inventory_assy_list` VALUES (15, 18, 'VID-NV-512MB', 'nVidea 512 MB Video Card', 1);
--
-- Dumping data for table `tax_authorities`
--
INSERT INTO `tax_authorities` VALUES (1, 'City Tax', 'City Tax on Taxable Items', '2312', 0, 2.5);
INSERT INTO `tax_authorities` VALUES (2, 'State Tax', 'State Sales Tax Payable', '2316', 0, 5.1);
INSERT INTO `tax_authorities` VALUES (3, 'Special Dist', 'Special District Tax (RTD, etc)', '2316', 0, 1.1);
--
-- Dumping data for table `tax_rates`
--
INSERT INTO `tax_rates` VALUES (1, 'Local Tax', 'Local POS Tax', '1:2:3', '0');
INSERT INTO `tax_rates` VALUES (2, 'State Only', 'State Only Tax - Shipments', '2', '0');