WS
Import Orders
To import your orders database to VirtualNewspaper system you have to upload two .CSV files to the FTP space we provided.
Rules to follow
- The fields must be delimited by a semicolon ";"
- The files must be named: YYYYMMDD_orders.csv and YYYYMMDD_order_details.csv
- The file must contain the values of all fields listed in the table (If you don't know the value of some fields use the appropriate "Default Value")
- The default numeric value is 0
- The default text value is the space character (don't omit the semicolon that contain them eg. ";;;;;")
- The default date value is ""1900-01-01""
- The default date/hour value is ""1900-01-01 00:00:00""
- The values must be listed respecting the order suggested in the table
- You need push the "enter" button to split a orders from the following
- There is should be no header row (columns names)
Structure table "orders" for file YYYYMMDD_orders.csv
(*) = mandatory
| FIELD | FORMAT | NOTE |
|---|---|---|
| id (*) | int(11) | Auto-incremental Id for the table "orders" |
| id_customer (*) | int(11) | In relation to the "id" field of the table "Customers". If it comes from Apple as anonymous you can leave this at "0" (in this case the field udid becomes mandatory) |
| number (*) | mediumtext | Order number. it an unique identifier used from the customers to refer their purchases. |
| date | date | Order date |
| payment | mediumtext | Equals id_payment |
| id_payment | int(11) | Payment method "id" from the table "Payments" |
| shipping | mediumtext | Shipping method in text |
| total | decimal(7,2) | Total of the order |
| discount | decimal(7,2) | Total of the discount |
| discount_percentage | decimal(7,2) | Discount percentage |
| discount_code | mediumtext | Discount code used for the order |
| shipping_amount | decimal(7,2) | Only for paper orders |
| weight | decimal(7,2) | Only for paper orders |
| confirmed | int(11) | "1" for confirmed / "0" for not confirmed; For order activated correctly set to "1"; |
| dispatched | int(11) | "1" for dispatched / "0" for not dispatched; For order activated correctly set to "1"; |
| surname | mediumtext | This information is needed for paper products, if the shipping address is different from that of the reference customer. |
| name | mediumtext | This information is needed for paper products, if the shipping address is different from that of the reference customer. |
| company_name | mediumtext | This information is needed for paper products, if the shipping address is different from that of the reference customer. |
| address | mediumtext | This information is needed for paper products, if the shipping address is different from that of the reference customer. |
| zip | mediumtext | This information is needed for paper products, if the shipping address is different from that of the reference customer. |
| city | mediumtext | This information is needed for paper products, if the shipping address is different from that of the reference customer. |
| region | mediumtext | This information is needed for paper products, if the shipping address is different from that of the reference customer. |
| country | mediumtext | This information is needed for paper products, if the shipping address is different from that of the reference customer. |
| telephone | mediumtext | This information is needed for paper products, if the shipping address is different from that of the reference customer. |
| note | mediumtext | Note for the order |
| invoice_request | varchar(20) | Virtualcom internal use |
| transactionid | varchar(20000) | Field that contains the receipt of purchase from Apple. |
| udid | varchar(40) | App Serial Number of the device used to purchase this order (web, tablets, smartphones) It's mandatory if the custemer_id field is "0" |
| exported | int(11) | Virtualcom internal use |
| cod_gesedi | varchar(60) | Virtualcom internal use |
| device | varchar(150) | Device used to purchase this order (web, ipad, iphone, android) |
| total_in_currency | decimal(7,2) | *Deprecated field* |
| id_gateway | int(10) | Payment gateway "id" from the table "Gateway" related the "id" of "Payments" table. |
| administrative_area_level_1_company | varchar(200) | This information is needed for paper products, if the shipping address is different from that of the reference customer. |
| administrative_area_level_2_company | varchar(200) | This information is needed for paper products, if the shipping address is different from that of the reference customer. |
| administrative_area_level_3_company | varchar(200) | This information is needed for paper products, if the shipping address is different from that of the reference customer. |
| formatted_address | varchar(500) | This information is needed for paper products, if the shipping address is different from that of the reference customer. |
| lat | varchar(50) | This information is needed for paper products, if the shipping address is different from that of the reference customer. |
| Ing | varchar(50) | This information is needed for paper products, if the shipping address is different from that of the reference customer. |
| date_time | datetime | Date and time of the order |
| apple_transaction_id | varchar(200) | Apple Transaction ID of the purchase |
| deleted | tinyint(1) | Virtualcom internal use |
| id_title | int(4) | Virtualcom internal use, for default set to "0" |
| divert | int(4) | Virtualcom internal use, for default set to "0" |
| date_divert | date | Virtualcom internal use, for default set to "1900-01-01 00:00:00" |
| cup | varchar(100) | Virtualcom internal use |
| cig | varchar(100) | Virtualcom internal use |
| split_payment | boolean | Virtualcom internal use |
Structure table "order_details" for file YYYYMMDD_order_details.csv
(*) = mandatory
| FIELD | FORMAT | NOTE |
|---|---|---|
| id (*) | int(11) | Auto-incremental Id for the table "order_details" |
| id_order (*) | int(11) | In relation to the "id" field of the "orders" table |
| articlecode | mediumtext | Contains the code of the product purchased, usually "ART-[product_id]" where [product_id] is the "id" from the "products" table. |
| article | mediumtext | Contains the text description of the product purchased |
| price | decimal(7,2) | Price of the product purchased |
| quantity | int(11) | Quantity of product purchased, usually "1" |
| total | decimal(7,2) | Total of purchase |
| id_article (*) | int(11) | "id" of the article purchased, in relation to the "id" field of the "products" table |
| active | int(11) | "1" if this order is active, "0" if isn't |
| expiration_date | date | Expiration date of the order |
| pseudoquota | int(11) | Virtualcom internal use |
| days | varchar(7) | Virtualcom internal use (valid only for paper products) |
| request_date | date | Request date of the order |
| activation_date | date | Activation date of the order |
| payment_date | date | Payment date of the order |
| month | int(11) | Number of months of the product purchased |
| editions_consultable | mediumtext | Contains the list of all the issues consultable. The format must be: "YYMMDD[pubblication_name];YYMMDD[pubblication_name];YYMMDD[pubblication_name];YYMMDD[pubblication_name];" |
| vat | int(11) | Vat rate for this purchase |
| grace_period | date | Virtualcom internal use, for default set to the same of the expiration_date |
| id_reminder | int(11) | Virtualcom internal use, for default set "0" |
Contents table "payments"
| id (INT 11) | code (VARCHAR 10) | label (VARCHAR 300) |
|---|---|---|
| "0" | Internal | Internal |
| "-1" | cc | Credit Card |
| "-2" | bp | Postal |
| "-3" | bb | Bank Transfer |
| "-4" | ph | Telephone |
| "-5" | ip | iTunes Purchase |
| "-6" | om | Free |
| "-7" | pos | Desk ATM |
| "-8" | sco | Desk Cash |
| "-9" | scc | Desk Credit Card |
| "-10" | gp | Google Play |
| "-11" | sa | Samsung Apps |
| "-12" | chq | Cheque |
Contents table "gateways"
| id (INT 11) | id_payments (INT 11) | label (VARCHAR 300) |
|---|---|---|
| 0 | "0" | generic |
| 1 | "-1" | Credit Card |
| 13 | "-2" | Postal |
| 14 | "-3" | Bank Transfer |
| 7 | "-4" | Telephone |
| 8 | "-5" | iTunes Purchase |
| 15 | "-7" | Desk ATM |
| 16 | "-8" | Desk Cash |
| 17 | "-9" | Desk Credit Card |
| 9 | "-10" | Google Play |
| 10 | "-11" | Samsung Apps |
| 18 | "-12" | Cheque |
Example for file YYYYMMDD_orders.csv with 3 orders
544912;2501897;"VNP-1382551951544912";"2013-10-23";"-5";-5;"Standard";1559.98;0.00;0.00;"";0.00;0.00;1;1;"";"";"";"";"";"";"";"";"";"";"0";"";"58dd5d64-b74f-4c2f-b848-d63b6c872f1a";1;"0";"ipadretina";0.00;8;"";"";"";"";"";"";"2013-10-23 20:12:31";"";0 604416;2735531;"VNP-1383101215604416";"2013-10-30";"-5";-5;"Standard";44.99;0.00;0.00;"";0.00;0.00;0;0;"";"";"";"";"";"";"";"";"";"";"0";"";"893e38eb-1019-468d-a9ea-83fa9e0c8a9b";1;"0";"ipadretina";0.00;8;"";"";"";"";"";"";"2013-10-30 03:46:55";"";0
604416;2739554;"VNP-1387284628788230";"2013-12-17";"-1";-1;"Standard";44.99;0.00;0.00;"";0.00;0.00;1;1;"";"";"";"";"";"";"";"";"";"";"0";"";"";1;"0";"web";0.00;1;"";"";"";"";"";"";"2013-12-17 00:00:00";"";0 843521;2736958;"VNP-1387550458843521";"2013-12-20";"-5";-5;"Standard";44.99;0.00;0.00;"";0.00;0.00;0;0;"";"";"";"";"";"";"";"";"";"";"0";"";"4a45884a-d6c7-4faa-96fa-cc15f40edb4f";1;"0";"ipad";0.00;8;"";"";"";"";"";"";"2013-12-20 15:40:58";"";0
843521;0;"VNP-1389243404865286";"2014-01-09";"-5";-5;"Standard";44.99;0.00;0.00;"";0.00;0.00;1;1;"";"";"";"";"";"";"";"";"";"";"0";"";"73867a7b-41f0-492d-8220-4690e477c4a5";0;"0";"ipad";0.00;8;"";"";"";"";"";"";"2014-01-09 05:56:44";"";0
Example for file YYYYMMDD_order_details.csv
563942;544912;"ART-153";"Product Description 1";29.99;2;59.98;153;1;"2014-10-22";12;"1234567";"2013-10-23";"2013-10-23";"2013-10-23";12;"131001pubblication_name_2;131102pubblication_name_2;131101pubblication_name_2;131201pubblication_name_2;140101pubblication_name_2;"";22
623441;604416;"ART-160";"Product Description 2";44.99;1;44.99;160;0;NULL;12;"1234567";"2013-10-30";NULL;NULL;12;NULL;21 807639;788230;"ART-158";"Product Description 3";44.99;1;44.99;158;1;"2014-12-16";12;"1234567";"2013-12-17";"2013-12-17";"2013-12-17";12;"";22
862959;843521;"ART-160";"Product Description 2";44.99;1;44.99;160;0;NULL;12;"1234567";"2013-12-20";NULL;NULL;12;NULL;22 884758;865286;"ART-160";"Product Description 2";44.99;1;44.99;160;1;"2015-01-08";12;"1234567";"2014-01-09";"2014-01-09";"2014-01-09";12;"140101pubblication_name_1;"";22
30/12/2015
30/12/2015