E2V Self Hosted Migration: Difference between revisions

From CasperTech Wiki
Jump to navigation Jump to search
No edit summary
mNo edit summary
Line 1: Line 1:
To migrate data from an E2V "Self Hosted" solution, you'll need access to your database server.
='''<span style="color:#00528c">Requirements</span>'''=
 
To migrate data from an E2V "Self Hosted" solution, you will need access to your database server.


This guide assumes you're using MySQL, PerconaDB or MariaDB, and you have unrestricted access to your server.
This guide assumes you're using MySQL, PerconaDB or MariaDB, and you have unrestricted access to your server.
Line 5: Line 7:
If you run into any problems at all, please get in touch by sending an in-world IM to CasperHelp Resident.
If you run into any problems at all, please get in touch by sending an in-world IM to CasperHelp Resident.


 
='''<span style="color:#00528c">Migrating Sales Data</span>'''=
== Migrating sales data ==


The following MySQL command will create a file compatible with our importer. Be sure to replace /tmp/sales.csv with the location that you want to output the file to.
The following MySQL command will create a file compatible with our importer. Be sure to replace /tmp/sales.csv with the location that you want to output the file to.
Line 39: Line 40:




== Migrating store credit ==
='''<span style="color:#00528c">Migrating Store Credit</span>'''=


The following MySQL command will create a file compatible with our importer. Be sure to replace /tmp/credits.csv with the location that you want to output the file to.
The following MySQL command will create a file compatible with our importer. Be sure to replace /tmp/credits.csv with the location that you want to output the file to.

Revision as of 14:44, 20 September 2018

Requirements

To migrate data from an E2V "Self Hosted" solution, you will need access to your database server.

This guide assumes you're using MySQL, PerconaDB or MariaDB, and you have unrestricted access to your server.

If you run into any problems at all, please get in touch by sending an in-world IM to CasperHelp Resident.

Migrating Sales Data

The following MySQL command will create a file compatible with our importer. Be sure to replace /tmp/sales.csv with the location that you want to output the file to.

SELECT 'date', 'custname', 'custkey', 'item', 'region', 'cost', 'paytype', 'recipname', 'recipkey' UNION ALL SELECT FROM_UNIXTIME(`date`), `custname`, `custkey`, `item`, `region`, `cost`, `paytype`, IF (`recipname` = '', `custname`, `recipname`), IF (`recipkey` = '', `custkey`, `recipkey`) FROM `sales` INTO OUTFILE '/tmp/sales.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

If you have multiple stores, add WHERE `storeid` = (your store id), like so:

SELECT 'date', 'custname', 'custkey', 'item', 'region', 'cost', 'paytype', 'recipname', 'recipkey' UNION ALL SELECT FROM_UNIXTIME(`date`), `custname`, `custkey`, `item`, `region`, `cost`, `paytype`, IF (`recipname` = '', `custname`, `recipname`), IF (`recipkey` = '', `custkey`, `recipkey`) FROM `sales` WHERE `storeid` = 1 INTO OUTFILE '/tmp/sales.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

To import the file, go here and click "Start new import", then "Upload CSV". Then select the file you wish to upload.

Then, choose "Sales Data" and hit Next, then choose "Those are field labels!" and hit next again.

Match the fields as follows:

date -> Date/Time -> YYYY-MM-DD (ISO) --> Second Life Time custname -> Purchaser Name custkey -> Purchaser UUID item -> Product Name (note you may have to deselect this from one of the other fields before it is available). region -> Location cost -> Gross paytype -> -- IGNORE -- recipname -> Recipient Name recipkey -> Recipient UUID

Then hit "Upload Now". The upload may take some time, please be patient!


Migrating Store Credit

The following MySQL command will create a file compatible with our importer. Be sure to replace /tmp/credits.csv with the location that you want to output the file to.

SELECT 'custkey', 'custname', 'balance' UNION ALL SELECT `custkey`, `custname`, `balance` FROM `credits` INTO OUTFILE '/tmp/credits.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

If you have multiple stores, add WHERE `storeid` = (your store id), like so:

SELECT 'custkey', 'custname', 'balance' UNION ALL SELECT `custkey`, `custname`, `balance` FROM `credits` WHERE `storeid` = 1 INTO OUTFILE '/tmp/credits.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

To import the file, go here and click "Start new import", then "Upload CSV". Then select the file you wish to upload.

Then, choose "Store Credit" and hit Next, then choose "Those are field labels!" and hit next again.

Match the fields as follows:

custkey -> Purchaser UUID cutname -> Purchaser Name balance -> Balance

Then select one of the options ("Add" is recommended), and choose Upload Now. The upload may take some time, please be patient!

Important: By default, CasperVend refunds customer balances on purchase - this is usually unfamiliar to E2V users. To fix this, please go to the Customers tab and unselect that option.