# This SQL script upgrades the core Zen Cart database structure from v1.2.0 to v1.2.1

#

# $Id: mysql_upgrade_zencart_120_to_121.sql 4243 2006-08-24 10:55:28Z drbyte $

#

 

## CONFIGURATION TABLE

UPDATE configuration set configuration_title = 'Define Conditions of Use' WHERE configuration_key = 'DEFINE_CONDITIONS_STATUS';

UPDATE configuration SET configuration_description = 'Automatically check to see if a new version of Zen-Cart is available. Enabling this can sometimes slow down the loading of Admin pages. (Displayed on main Index page after login, and Server Info page.)' WHERE configuration_key = 'SHOW_VERSION_UPDATE_IN_HEADER';

INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) VALUES ('Display Prices with Tax in Admin', 'DISPLAY_PRICE_WITH_TAX_ADMIN', 'false', 'Display prices with tax included (true) or add the tax at the end (false) in Admin(Invoices)', '1', '21', 'zen_cfg_select_option(array(\'true\', \'false\'), ', now());

UPDATE configuration SET configuration_description='Products Display Order by<br />0= Product ID<br />1= Product Name<br />2= Model<br />3= Price, Product Name<br />4= Price, Model<br />5= Product Name, Model<br />6= Product Sort Order', set_function='zen_cfg_select_drop_down(array(array(\'id\'=>\'0\', \'text\'=>\'Product ID\'), array(\'id\'=>\'1\', \'text\'=>\'Name\'), array(\'id\'=>\'2\', \'text\'=>\'Product Model\'), array(\'id\'=>\'3\', \'text\'=>\'Product Price - Name\'), array(\'id\'=>\'4\', \'text\'=>\'Product Price - Model\'), array(\'id\'=>\'5\', \'text\'=>\'Product Name - Model\'), array(\'id\'=>\'6\', \'text\'=>\'Product Sort Order\')),' WHERE configuration_key='PRODUCT_INFO_PREVIOUS_NEXT_SORT';

UPDATE configuration SET configuration_title = 'Enable phpBB linkage?', configuration_description = 'Should Zen Cart synchronize new account information to your (already-installed) phpBB forum?' WHERE configuration_key = 'PHPBB_LINKS_ENABLED';

INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) VALUES ('Customers Referral Status', 'CUSTOMERS_REFERRAL_STATUS', '0', 'Customers Referral Code is created from<br />0= Off<br />1= 1st Discount Coupon Code used<br />2= Customer can add during create account or edit if blank<br /><br />NOTE: Once the Customers Referral Code has been set it can only be changed in the Admin Customer', '5', '80', 'zen_cfg_select_option(array(\'0\', \'1\', \'2\'), ', now());

INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) VALUES ('Show Category Counts - Admin', 'SHOW_COUNTS_ADMIN', 'true', 'Show Category Counts in Admin?', '1', '130', 'zen_cfg_select_option(array(\'true\', \'false\'), ', now());

INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) VALUES ('Display "Newsletter Unsubscribe" Link?', 'SHOW_NEWSLETTER_UNSUBSCRIBE_LINK', 'true', 'Show "Newsletter Unsubscribe" link in the "Information" side-box?', '12', '70', 'zen_cfg_select_option(array(\'true\', \'false\'),', now());

 

 

## Table Structure updates to handle better use of decimal points

ALTER TABLE customers_basket CHANGE COLUMN customers_basket_quantity customers_basket_quantity FLOAT DEFAULT '0' NOT NULL;

ALTER TABLE orders_products CHANGE COLUMN products_quantity products_quantity FLOAT DEFAULT '0' NOT NULL;

ALTER TABLE products CHANGE COLUMN products_quantity products_quantity FLOAT DEFAULT '0' NOT NULL;

ALTER TABLE products CHANGE COLUMN products_ordered products_ordered FLOAT DEFAULT '0' NOT NULL;

ALTER TABLE products CHANGE COLUMN products_quantity_order_min products_quantity_order_min FLOAT DEFAULT '1' NOT NULL;

ALTER TABLE products CHANGE COLUMN products_quantity_order_units products_quantity_order_units FLOAT DEFAULT '1' NOT NULL;

ALTER TABLE products CHANGE COLUMN products_quantity_order_max products_quantity_order_max FLOAT DEFAULT '0' NOT NULL;

ALTER TABLE products_discount_quantity CHANGE COLUMN discount_qty discount_qty FLOAT DEFAULT '0' NOT NULL;

 

## Customers Table additions

ALTER TABLE customers ADD COLUMN customers_referral VARCHAR(32) NOT NULL default '';

 

## Add Coupon_Code to Order Table:

ALTER TABLE orders ADD COLUMN coupon_code varchar(32) NOT NULL default '' AFTER payment_method;

 

## LAYOUT BOXES TABLE

INSERT INTO layout_boxes (layout_template, layout_box_name, layout_box_status, layout_box_location, layout_box_sort_order, layout_box_sort_order_single, layout_box_status_single) VALUES ('default_template_settings', 'document_categories.php', 1, 0, 0, 0, 0);

INSERT INTO layout_boxes (layout_template, layout_box_name, layout_box_status, layout_box_location, layout_box_sort_order, layout_box_sort_order_single, layout_box_status_single) VALUES ('default_template_settings', 'music_genres.php', 1, 1, 0, 0, 0);

INSERT INTO layout_boxes (layout_template, layout_box_name, layout_box_status, layout_box_location, layout_box_sort_order, layout_box_sort_order_single, layout_box_status_single) VALUES ('default_template_settings', 'record_companies.php', 1, 1, 0, 0, 0);

UPDATE layout_boxes SET layout_box_status_single=127 WHERE layout_template = 'default_template_settings' AND layout_box_name = 'banner_box.php';

 

## Query Builder Update

UPDATE query_builder set query_string ='select c.customers_email_address, c.customers_lastname, c.customers_firstname from TABLE_CUSTOMERS c, TABLE_ORDERS o where c.customers_newsletter = \'1\' AND c.customers_id = o.customers_id and o.date_purchased < subdate(now(),INTERVAL 3 MONTH) GROUP BY c.customers_email_address order by c.customers_lastname, c.customers_firstname ASC' WHERE query_id='3';

UPDATE query_builder set query_string ='select c.customers_email_address, c.customers_lastname, c.customers_firstname from TABLE_CUSTOMERS c, TABLE_ORDERS o where c.customers_newsletter = \'1\' AND c.customers_id = o.customers_id and o.date_purchased > subdate(now(),INTERVAL 3 MONTH) GROUP BY c.customers_email_address order by c.customers_lastname, c.customers_firstname ASC' WHERE query_id='4';

UPDATE query_builder set query_string ='select c.customers_email_address, c.customers_lastname, c.customers_firstname from TABLE_CUSTOMERS c, TABLE_ORDERS o WHERE c.customers_id = o.customers_id and o.date_purchased > subdate(now(),INTERVAL 3 MONTH) GROUP BY c.customers_email_address order by c.customers_lastname, c.customers_firstname ASC' WHERE query_id='5';

 

## Reset Sales and Salemaker expiry dates if null or 0000-00-00

UPDATE specials SET expires_date='0001-01-01' where expires_date='0000-00-00';

UPDATE specials SET specials_date_available ='0001-01-01' where specials_date_available ='0000-00-00';

 

 

## PayPal IPN Updates

ALTER TABLE orders_products_attributes ADD products_options_id INT( 11 ) DEFAULT '0' NOT NULL;

ALTER TABLE orders_products_attributes ADD products_options_values_id INT( 11 ) DEFAULT '0' NOT NULL;

 

DROP TABLE IF EXISTS orders_session_info;

CREATE TABLE orders_session_info (

  txn_signature varchar(32) NOT NULL default '',

  orders_id int(11) NOT NULL default '0',

  sendto int(11) NOT NULL default '1',

  billto int(11) NOT NULL default '1',

  language varchar(32) NOT NULL default '',

  currency char(3) NOT NULL default '',

  firstname varchar(32) NOT NULL default '',

  lastname varchar(32) NOT NULL default '',

  content_type varchar(32) NOT NULL default '',

  PRIMARY KEY (txn_signature,orders_id),

  KEY idx_orders_session_info_txn_signature (txn_signature)

);

 

DROP TABLE IF EXISTS paypal;

CREATE TABLE paypal (

  paypal_ipn_id int(11) unsigned NOT NULL auto_increment,

  txn_type varchar(10) NOT NULL default '',

  reason_code varchar(15) default NULL,

  payment_type varchar(7) NOT NULL default '',

  payment_status varchar(17) NOT NULL default '',

  pending_reason varchar(14) default NULL,

  invoice varchar(64) default NULL,

  mc_currency char(3) NOT NULL default '',

  first_name varchar(32) NOT NULL default '',

  last_name varchar(32) NOT NULL default '',

  payer_business_name varchar(64) default NULL,

  address_name varchar(32) default NULL,

  address_street varchar(64) default NULL,

  address_city varchar(32) default NULL,

  address_state varchar(32) default NULL,

  address_zip varchar(10) default NULL,

  address_country varchar(64) default NULL,

  address_status varchar(11) default NULL,

  payer_email varchar(96) NOT NULL default '',

  payer_id varchar(32) NOT NULL default '',

  payer_status varchar(10) NOT NULL default '',

  payment_date datetime default NULL,

  business varchar(96) NOT NULL default '',

  receiver_email varchar(96) NOT NULL default '',

  receiver_id varchar(32) NOT NULL default '',

  txn_id varchar(17) NOT NULL default '',

  parent_txn_id varchar(17) default NULL,

  num_cart_items tinyint(4) unsigned NOT NULL default '1',

  mc_gross decimal(7,2) NOT NULL default '0.00',

  mc_fee decimal(7,2) NOT NULL default '0.00',

  payment_gross decimal(7,2) default NULL,

  payment_fee decimal(7,2) default NULL,

  settle_amount decimal(7,2) default NULL,

  settle_currency char(3) default NULL,

  exchange_rate decimal(4,2) default NULL,

  notify_version decimal(2,1) NOT NULL default '0.0',

  verify_sign varchar(128) NOT NULL default '',

  last_modified datetime default NULL,

  date_added datetime default NULL,

  memo text,

  PRIMARY KEY (paypal_ipn_id,txn_id),

  KEY idx_paypal_paypal_ipn_id (paypal_ipn_id)

);

 

DROP TABLE IF EXISTS paypal_payment_status;

CREATE TABLE paypal_payment_status (

  payment_status_id int(11) NOT NULL auto_increment,

  payment_status_name varchar(64) NOT NULL default '',

  PRIMARY KEY (payment_status_id)

);

INSERT INTO paypal_payment_status VALUES (1, 'Completed');

INSERT INTO paypal_payment_status VALUES (2, 'Pending');

INSERT INTO paypal_payment_status VALUES (3, 'Failed');

INSERT INTO paypal_payment_status VALUES (4, 'Denied');

INSERT INTO paypal_payment_status VALUES (5, 'Refunded');

INSERT INTO paypal_payment_status VALUES (6, 'Canceled_Reversal');

INSERT INTO paypal_payment_status VALUES (7, 'Reversed');

 

DROP TABLE IF EXISTS paypal_payment_status_history;

CREATE TABLE paypal_payment_status_history (

  payment_status_history_id int(11) NOT NULL auto_increment,

  paypal_ipn_id int(11) NOT NULL default '0',

  payment_status varchar(17) NOT NULL default '',

  pending_reason varchar(14) default NULL,

  date_added datetime NOT NULL default '0001-01-01 00:00:00',

  PRIMARY KEY (payment_status_history_id)

);

 

## These tables are obsolete after the new PayPal IPN is installed

DROP TABLE IF EXISTS paypal_ipn_address_status;

DROP TABLE IF EXISTS paypal_ipn_mc_currency;

DROP TABLE IF EXISTS paypal_ipn_payment_status;

DROP TABLE IF EXISTS paypal_ipn_payment_type;

DROP TABLE IF EXISTS paypal_ipn_pending_reason;

DROP TABLE IF EXISTS paypal_ipn_reason_code;

DROP TABLE IF EXISTS paypal_ipn_txn_type;

 

## Migrate Paypal IPN data from 1.2.0 format to 1.2.1 format:

#NEXT_X_ROWS_AS_ONE_COMMAND:1

INSERT INTO paypal

(paypal_ipn_id, txn_type, reason_code, payment_type, payment_status, pending_reason,

invoice, mc_currency, first_name, last_name, payer_business_name, address_name,

address_street, address_city, address_state, address_zip, address_country, address_status,

payer_email, payer_id, payer_status,

payment_date, business, receiver_email, receiver_id, txn_id,

num_cart_items, mc_gross, mc_fee, payment_gross, payment_fee, settle_amount,

exchange_rate, notify_version, verify_sign, date_added, memo )

SELECT p.paypal_ipn_id, p.txn_type, p.reason_code, p.payment_type, p.payment_status,

p.pending_reason, p.invoice, p.mc_currency,

p.first_name, p.last_name, p.payer_business_name, p.address_name, p.address_street,

p.address_city, p.address_state, p.address_zip, p.address_country, p.address_status,

p.payer_email, p.payer_id, p.payer_status,

p.payment_date, p.business, p.receiver_email, p.receiver_id, p.txn_id,

po.num_cart_items, po.mc_gross, po.mc_fee, po.payment_gross, po.payment_fee, po.settle_amount,

po.exchange_rate, p.notify_version, p.verify_sign, p.date_added, pm.memo

FROM (paypal_ipn p, paypal_ipn_orders_memo pm

LEFT JOIN paypal_ipn_orders po

ON p.paypal_ipn_id = po.paypal_ipn_id)

WHERE p.paypal_ipn_id = pm.paypal_ipn_id;

 

UPDATE paypal SET payment_status='Completed' where payment_status=1;

UPDATE paypal SET payment_status='Pending' where payment_status=2;

UPDATE paypal SET payment_status='Failed' where payment_status=3;

UPDATE paypal SET payment_status='Denied' where payment_status=4;

UPDATE paypal SET payment_status='Refunded' where payment_status=5;

UPDATE paypal SET payment_status='Cancelled' where payment_status=6;

UPDATE paypal SET payment_type='instant' where payment_type=1;

UPDATE paypal SET payment_type='echeck' where payment_type=2;

UPDATE paypal SET pending_reason='' where pending_reason=0;

UPDATE paypal SET pending_reason='echeck' where pending_reason=1;

UPDATE paypal SET pending_reason='multi-currency' where pending_reason=2;

UPDATE paypal SET pending_reason='intl' where pending_reason=3;

UPDATE paypal SET pending_reason='Verify' where pending_reason=4;

UPDATE paypal SET pending_reason='address' where pending_reason=5;

UPDATE paypal SET pending_reason='upgrade' where pending_reason=6;

UPDATE paypal SET pending_reason='unilateral' where pending_reason=7;

UPDATE paypal SET pending_reason='other' where pending_reason=8;

UPDATE paypal SET reason_code='' where reason_code=0;

UPDATE paypal SET reason_code='chargeback' where reason_code=1;

UPDATE paypal SET reason_code='guarantee' where reason_code=2;

UPDATE paypal SET reason_code='buyer_complaint' where reason_code=3;

UPDATE paypal SET reason_code='other' where reason_code=4;

UPDATE paypal SET txn_type='web_accept' where txn_type=1;

UPDATE paypal SET txn_type='cart' where txn_type=2;

UPDATE paypal SET txn_type='send_money' where txn_type=3;

UPDATE paypal SET txn_type='reversal' where txn_type=4;

UPDATE paypal SET mc_currency='USD' where mc_currency=1;

UPDATE paypal SET mc_currency='GBP' where mc_currency=2;

UPDATE paypal SET mc_currency='EUR' where mc_currency=3;

UPDATE paypal SET mc_currency='CAD' where mc_currency=4;

UPDATE paypal SET mc_currency='JPY' where mc_currency=5;

UPDATE paypal SET address_status='confirmed' where address_status=1;

UPDATE paypal SET address_status='unconfirmed' where address_status=2;

 

#NEXT_X_ROWS_AS_ONE_COMMAND:1

INSERT INTO paypal_payment_status_history

(paypal_ipn_id, payment_status, pending_reason, date_added)

SELECT paypal_ipn_id, payment_status, pending_reason, date_added

FROM paypal;

 

DROP TABLE IF EXISTS paypal_ipn;

DROP TABLE IF EXISTS paypal_ipn_orders;

DROP TABLE IF EXISTS paypal_ipn_orders_memo;

 

## END PAYPAL_IPN_MIGRATION

 

 

 

## THE FOLLOWING SHOULD BE THE "LAST" ITEMS IN THE FILE, so that if the upgrade fails prematurely, the version info is not updated.

UPDATE project_version SET project_version_major='1', project_version_minor='2.1', project_version_patch_major='', project_version_date_applied=now() WHERE project_version_key = 'Zen-Cart Main';

UPDATE project_version SET project_version_major='1', project_version_minor='2.1', project_version_patch_major='', project_version_date_applied=now() WHERE project_version_key = 'Zen-Cart Database';

 

## one final typo:

## the following fixes misspelled column name from 1.2.0:

ALTER TABLE product_types CHANGE COLUMN date_addded date_added datetime NOT NULL default '0001-01-01 00:00:00';

 

#####  END OF UPGRADE SCRIPT