E2V Self Hosted Migration: Difference between revisions

From CasperTech Wiki
Jump to navigation Jump to search
mNo edit summary
 
(7 intermediate revisions by the same user not shown)
Line 1: Line 1:
{|align=right
  |__TOC__
  |}
='''<span style="color:#00528c">Requirements</span>'''=
='''<span style="color:#00528c">Requirements</span>'''=


Line 5: Line 9:
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.


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 '''[secondlife:///app/agent/828f7198-42e5-41b4-bd60-926a33ea067b/about CasperHelp Resident]''' and follow the instructions that will arrive with a web link.


='''<span style="color:#00528c">Migrating Sales Data</span>'''=
='''<span style="color:#00528c">E2V Self-Hosted Data: Sales</span>'''=


== Export Sales From Your Database ==
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 20: Line 25:
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';
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';
</syntaxhighlight>
</syntaxhighlight>
== Import Sales to CasperVend ==


To import the file, go [https://caspervend.casperdns.com/import.php here] and click "Start new import", then "Upload CSV". Then select the file you wish to upload.
To import the file, go [https://caspervend.casperdns.com/import.php here] and click "Start new import", then "Upload CSV". Then select the file you wish to upload.
Line 39: Line 46:
Then hit "Upload Now". The upload may take some time, please be patient!
Then hit "Upload Now". The upload may take some time, please be patient!


='''<span style="color:#00528c">E2V Self-Hosted Data: Store Credit</span>'''=


='''<span style="color:#00528c">Migrating Store Credit</span>'''=
== Export Store Credit Data From Your Database ==
 
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.


Line 53: Line 60:
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';
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';
</syntaxhighlight>
</syntaxhighlight>
== Import Store Credit to CasperVend ==


To import the file, go [https://caspervend.casperdns.com/import.php here] and click "Start new import", then "Upload CSV".  Then select the file you wish to upload.
To import the file, go [https://caspervend.casperdns.com/import.php here] and click "Start new import", then "Upload CSV".  Then select the file you wish to upload.

Latest revision as of 14:51, 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 and follow the instructions that will arrive with a web link.

E2V Self-Hosted Data: Sales

Export Sales From Your Database

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';

Import Sales to CasperVend

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!

E2V Self-Hosted Data: Store Credit

Export Store Credit Data From Your Database

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';

Import Store Credit to CasperVend

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.