DELETE e1 FROM import_sextoys4all e1, import_sextoys4all e2 WHERE e1.description= e2.description AND e1.categories = e2.categories AND e1.sku <> e2.sku;

select 'sku','barcode','post_title','post_status','visibility','_regular_price',/*'_sale_price',*/'category','affili_productgroup'
,'tags','post_content','post_excerpt','image','type','_product_url','_button_text','kleur','affili_discreet','affili_shippingcost'
,'affili_orderbeforetime','affili_payment','affili_lastupdate','manufacturer','supplier' union all

select
CONCAT('NI',sku) as sku
,ean as barcode
,name as post_title
,'publish' as post_status
,'visible' as visibility
,price as _regular_price
#,fromPrice as _sale_price
,CASE
WHEN categories like '%Bed & Bathroom%' THEN 'drogist/Bed & Bathroom'
WHEN categories like '%Drogisterij%' THEN 'drogist'
WHEN categories like '%Fun & Games%' THEN 'Fun en Gadgets'
WHEN categories like '%Kits%' THEN 'Sextoys/Sextoy Sets'
WHEN categories like '%Leer Artikelen%' THEN 'Leer & Latex'
WHEN categories like '%Love machines%' THEN 'bdsm/sexschommels machines'
WHEN categories like '%Leer & Latex%' THEN 'bdsm/Leer & Latex'
WHEN categories like '%sexmachines%' THEN 'bdsm/sexschommels machines'
WHEN categories like '%sextoys/haar/masturbatie%' THEN 'sextoys/voor haar/masturbatie'
WHEN categories like '%sextoys/haar/vibrators%' THEN 'sextoys/vibrators'
WHEN categories like '%sextoys/voor hem/masturbatie%' THEN 'sextoys/voor hem/masturbatie'
WHEN categories like '%sextoys/voor hem/sexpoppen%' THEN 'sextoys/voor hem/sexpoppen'
ELSE 'Diversen'
END AS category
,CASE
WHEN categories like '%batterij%' or name like '%batterij%' THEN 'batterij'
WHEN categories like '%blinddoek%' or name like '%blinddoek%' THEN 'blinddoek'
WHEN categories like '%buttplug%' or name like '%buttplug%' THEN 'buttplug'
WHEN categories like '%cockring%' or name like '%cockring%' THEN 'cockring'
WHEN categories like '%condoom%' or name like '%condoom%' THEN 'condoom'
WHEN categories like '%dildo%' or name like '%dildo%' THEN 'dildo'
WHEN categories like '%glijmiddel%' or name like '%glijmiddel%' THEN 'glijmiddel'
WHEN categories like '%lingerie%' or name like '%lingerie%' THEN 'lingerie'
WHEN categories like '%lovedoll%' or name like '%lovedoll%' THEN 'sexpoppen'
WHEN categories like '%love doll%' or name like '%love doll%' THEN 'sexpoppen'
WHEN categories like '%masker%' or name like '%masker%' THEN 'masker'
WHEN categories like '%masturbator%' or name like '%masturbator%' THEN 'masturbator'
WHEN categories like '%opblaaspop%' or name like '%opblaaspop%' THEN 'sexpoppen'
WHEN categories like '%penispomp%' or name like '%penispomp%' THEN 'penispomp'
WHEN categories like '%penisverlenger%' or name like '%penisverlenger%' THEN 'penisverlenger'
WHEN categories like '%sexpomp%' or name like '%sexpomp%' THEN 'sexpomp'
WHEN categories like '%sexpop%' or name like '%sexpop%' THEN 'sexpoppen'
WHEN categories like '%strapon%' or name like '%strapon%' THEN 'strapon'
WHEN categories like '%toycleaner%' or name like '%toy cleaner%' or name like '%anti-bacteriële spray%' THEN 'toycleaner'
WHEN categories like '%vibrator%' or name like '%vibrator%' THEN 'vibrator'
ELSE ''
END AS affili_productgroup
,CASE
WHEN categories like '%batterij%' or name like '%batterij%' THEN 'batterij'
WHEN categories like '%blinddoek%' or name like '%blinddoek%' THEN 'blinddoek'
WHEN categories like '%buttplug%' or name like '%buttplug%' THEN 'buttplug'
WHEN categories like '%cockring%' or name like '%cockring%' THEN 'cockring'
WHEN categories like '%condoom%' or name like '%condoom%' THEN 'condoom'
WHEN categories like '%dildo%' or name like '%dildo%' THEN 'dildo'
WHEN categories like '%glijmiddel%' or name like '%glijmiddel%' THEN 'glijmiddel'
WHEN categories like '%lingerie%' or name like '%lingerie%' THEN 'lingerie'
WHEN categories like '%lovedoll%' or name like '%lovedoll%' THEN 'sexpop'
WHEN categories like '%love doll%' or name like '%love doll%' THEN 'sexpop'
WHEN categories like '%masker%' or name like '%masker%' THEN 'masker'
WHEN categories like '%masturbator%' or name like '%masturbator%' THEN 'masturbator'
WHEN categories like '%opblaaspop%' or name like '%opblaaspop%' THEN 'sexpop'
WHEN categories like '%penispomp%' or name like '%penispomp%' THEN 'penispomp'
WHEN categories like '%penisverlenger%' or name like '%penisverlenger%' THEN 'penisverlenger'
WHEN categories like '%sexpomp%' or name like '%sexpomp%' THEN 'sexpomp'
WHEN categories like '%sexpop%' or name like '%sexpop%' THEN 'sexpoppen'
WHEN categories like '%strapon%' or name like '%strapon%' THEN 'strapon'
WHEN categories like '%toycleaner%' or name like '%toy cleaner%' or name like '%anti-bacteriële spray%' THEN 'toycleaner'
WHEN categories like '%vibrator%' or name like '%vibrator%' THEN 'vibrator'
ELSE ''
END AS tags
,'' as post_content
,description as post_excerpt
,imageURL as image
,'external' as type
,productURL as _product_url
,'Koop bij sextoys4all' as _button_text
,CASE
WHEN name like '%aluminium%' THEN 'aluminium'
WHEN name like '%beige%' THEN 'beige'
WHEN name like '%black%' THEN 'zwart'
WHEN name like '%blue%' THEN 'blauw'
WHEN name like '%blauw%' THEN 'blauw'
WHEN name like '%brown%' THEN 'bruin'
WHEN name like '%bruin%' THEN 'bruin'
WHEN name like '%Donker blauw%' THEN 'blauw'
WHEN name like '%donker roze%' THEN 'heet roze'
WHEN name like '%fuchsia%' THEN 'heet roze'
WHEN name like '%goud%' THEN 'goud'
WHEN name like '%green%' THEN 'groen'
WHEN name like '%groen%' THEN 'groen'
WHEN name like '%grijs%' THEN 'grijs'
WHEN name like '%grijze%' THEN 'grijs'
WHEN name like '%grey%' THEN 'grijs'
WHEN name like '%huidskleur%' THEN 'huidskleur'
WHEN name like '%ivoor%' THEN 'ivoor'
WHEN name like '%Leger groen%' THEN 'groen'
WHEN name like '%Leopard%' THEN 'leopard'
WHEN name like '%magenta%' THEN 'magenta'
WHEN name like '%metaal%' THEN 'metaal'
WHEN name like '%neon%' THEN 'neon'
WHEN name like '%oranje%' THEN 'oranje'
WHEN name like '%orange%' THEN 'oranje'
WHEN name like '%roze%' THEN 'roze'
WHEN name like '%paars%' THEN 'paars'
WHEN name like '%pink%' THEN 'roze'
WHEN name like '%purple%' THEN 'paars'
WHEN name like '%red%' THEN 'rood'
WHEN name like '%rood%' THEN 'rood'
WHEN name like '%rode%' THEN 'rood'
WHEN name like '%rubber%' THEN 'rubber'
WHEN name like '%stylus%' THEN ''
WHEN name like '%transparant%' THEN 'transparant'
WHEN name like '%screenprotector%' THEN 'transparant'
WHEN name like '%transparant%' THEN 'transparent'
WHEN name like '%turquoise%' THEN 'blauw'
WHEN name like '%violet%' THEN 'paars'
WHEN name like '%wit%' THEN 'wit'
WHEN name like '%white%' THEN 'wit'
WHEN name like '%geel%' THEN 'geel'
WHEN name like '%zwart%' THEN 'zwart'
WHEN name like '%zilver%' THEN 'zilver'
ELSE ''
END AS kleur
,'♥ Discreet Besteld en Verpakt!' as affili_discreet
,IF(price < '64.99','♥ Verzendkosten € 5.95 (v.a. € 65,00 Gratis!)','♥ Tijdelijk Gratis Verzending!') as affili_shippingcost
,'♥ Snelle Levering!' as affili_orderbeforetime
,'♥ Betaal Achteraf/Ideal/Paypal/Creditcard' as affili_payment
,CURDATE() as affili_lastupdate
,brand as manufacturer
,'sextoys4all' as supplier

from import_sextoys4all 

/*
where article not like 'A0xxx%'
AND article not like 'A0xxx%'
*/

group by sku 
into outfile '/tmp/export_sextoys4all.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ';'
ESCAPED BY ''
LINES TERMINATED BY '\r\n';
