mysql_upgrade_zencart_120_to_121.sql

Zen Cart是真正的电子商务艺术

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