import.php

<?php

//$attribute = 'a:2:{s:8:"pa_color";a:6:{s:4:"name";s:8:"pa_color";s:5:"value";s:0:"";s:8:"position";s:1:"2";s:10:"is_visible";s:1:"1";s:12:"is_variation";s:1:"1";s:11:"is_taxonomy";s:1:"1";}s:7:"pa_size";a:6:{s:4:"name";s:7:"pa_size";s:5:"value";s:0:"";s:8:"position";s:1:"3";s:10:"is_visible";s:1:"1";s:12:"is_variation";s:1:"1";s:11:"is_taxonomy";s:1:"1";}}';
//echo '<pre>';
//var_dump(unserialize ($attribute));
//echo '</pre>';


define('IMPORT_BASE_URL', 'http://www.ndkstore.com/?post_type=product&p=');

$string = '';

$data =
    [
        'product' =>
            [
                'sku' => 'sku123456',
                'EAN' => '123456789LKJH',
                'title' => 'Product 3',
                'price' => 100,
                'content' => 'Product description',
                'short_desc' => 'Short desc',
                'price' => 100,
                'sale_price' => 90,
                'weight' => 0.2,
                'length' => 10.2,
                'width' => 11.3,
                'height' => 13.4
            ],
        'images' => [
            'http://www.ndkstore.com/wp-content/uploads/2019/09/tieucanhchungcu2.jpg',
            'http://www.ndkstore.com/wp-content/uploads/2019/09/tieucanhchungcu3.jpg'
        ],
        'size' => [
            'S' => [
                'price' => 100,
                'sale_price' => 90,
            ],
            'M' => [
                'price' => 110,
                'sale_price' => 99,
            ],
            'L' => [
                'price' => 120,
                'sale_price' => 110,
            ],
            'XL' => [
                'price' => 130,
                'sale_price' => 120,
            ],
        ],
        'color' => [
            'Black' => [
                'price' => 100,
                'sale_price' => 90,
            ],
            'White' => [
                'price' => 100,
                'sale_price' => 90,
            ],
        ],
        'categories' => [60],
        'tags' => [46, 50],

    ];

$size_mapping = [
    'S' => 85,
    'M' => 87,
    'L' => 86,
    'XL' => 88
];

$color_mapping = [
    'Black' => 82,
    'White' => 83,
];

function import_main_product( $data,  $string = '', $title = null, $type = null, $parent_post = null, $key = 0){

    if(!$title){
        $title = $data['product']['title'];
    }
    if(!$type){
        $type = 'product';
    }
    if($parent_post == null){
        $parent_post = 0;
    }

    $string .= "INSERT INTO `wp_posts` (`post_author`, `post_date`, `post_date_gmt`, `post_content`, `post_content_filtered`, `post_title`, `post_excerpt`, `post_status`, `post_type`, `comment_status`, `ping_status`, `post_password`, `post_name`, `to_ping`, `pinged`, `post_modified`, `post_modified_gmt`, `post_parent`, `menu_order`, `post_mime_type`, `guid`) 
	VALUES (1, '".date("Y-m-d H:i:s")."', '".date("Y-m-d H:i:s")."', '".addslashes($data['product']['content'])."', '', '".addslashes($title)."', '".addslashes($data['product']['short_desc'])."', 'publish', '".$type."', 'open', 'closed', '', '', '', '', '".date("Y-m-d H:i:s")."', '".date("Y-m-d H:i:s")."', ".$parent_post.", 0, '', '') ;";

    if($parent_post === 0){

        $string .= "SET @product_parent = (SELECT LAST_INSERT_ID());";


    }

    $string .= "SET @product_id = (SELECT LAST_INSERT_ID());";
    $string .= "SET @product_id_".$key." = (SELECT LAST_INSERT_ID());";

    $string .= "UPDATE `wp_posts` SET `guid` = concat('".IMPORT_BASE_URL."',@product_id) WHERE `ID` = @product_id ;";
    $string .= "UPDATE `wp_posts` SET `post_name` = concat('product-',@product_id) WHERE `ID` = @product_id ;";

    if (count($data['images']) > 0) {
        foreach ($data['images'] as $key => $image) {
            $string .= "INSERT INTO `wp_posts` (`post_author`, `post_date`, `post_date_gmt`, `post_content`, `post_content_filtered`, `post_title`, `post_excerpt`, `post_status`, `post_type`, `comment_status`, `ping_status`, `post_password`, `post_name`, `to_ping`, `pinged`, `post_modified`, `post_modified_gmt`, `post_parent`, `menu_order`, `post_mime_type`, `guid`) 
	VALUES (1, '" . date("Y-m-d H:i:s") . "', '" . date("Y-m-d H:i:s") . "', '', '', 'image_" . $data['product']['sku'] . "_" . $key . "', '', 'inherit', 'attachment', 'open', 'closed', '', 'image_" . $data['product']['sku'] . "_" . $key . "', '', '', '" . date("Y-m-d H:i:s") . "', '" . date("Y-m-d H:i:s") . "', @product_id, 0, 'image/jpeg', '" . addslashes($image) . "');";

            $string .= "SET @image" . $key . " = (SELECT LAST_INSERT_ID());";

            $string .= "INSERT INTO `wp_postmeta` (`post_id`, `meta_key`, `meta_value`) VALUES (@image" . $key . ", '_wp_attached_file', '" . $image . "'); ";

            //$string .= "INSERT INTO `wp_postmeta` (`post_id`, `meta_key`, `meta_value`) VALUES (9351, '_wp_attachment_metadata', 'a:5:{s:5:\\"width\\";i:500;s:6:\\"height\\";i:891;s:4:\\"file\\";s:44:\\"2019 / 09 / tieu - canh - ban - cong - nuoc - chung - cu . jpg\\";s:5:\\"sizes\\";a:7:{s:9:\\"thumbnail\\";a:4:{s:4:\\"file\\";s:44:\\"tieu - canh - ban - cong - nuoc - chung - cu - 280x280 . jpg\\";s:5:\\"width\\";i:280;s:6:\\"height\\";i:280;s:9:\\"mime - type\\";s:10:\\"image / jpeg\\";}s:6:\\"medium\\";a:4:{s:4:\\"file\\";s:44:\\"tieu - canh - ban - cong - nuoc - chung - cu - 224x400 . jpg\\";s:5:\\"width\\";i:224;s:6:\\"height\\";i:400;s:9:\\"mime - type\\";s:10:\\"image / jpeg\\";}s:5:\\"large\\";a:4:{s:4:\\"file\\";s:44:\\"tieu - canh - ban - cong - nuoc - chung - cu - 449x800 . jpg\\";s:5:\\"width\\";i:449;s:6:\\"height\\";i:800;s:9:\\"mime - type\\";s:10:\\"image / jpeg\\";}s:21:\\"woocommerce_thumbnail\\";a:5:{s:4:\\"file\\";s:44:\\"tieu - canh - ban - cong - nuoc - chung - cu - 300x300 . jpg\\";s:5:\\"width\\";i:300;s:6:\\"height\\";i:300;s:9:\\"mime - type\\";s:10:\\"image / jpeg\\";s:9:\\"uncropped\\";b:0;}s:29:\\"woocommerce_gallery_thumbnail\\";a:4:{s:4:\\"file\\";s:44:\\"tieu - canh - ban - cong - nuoc - chung - cu - 100x100 . jpg\\";s:5:\\"width\\";i:100;s:6:\\"height\\";i:100;s:9:\\"mime - type\\";s:10:\\"image / jpeg\\";}s:12:\\"shop_catalog\\";a:4:{s:4:\\"file\\";s:44:\\"tieu - canh - ban - cong - nuoc - chung - cu - 300x300 . jpg\\";s:5:\\"width\\";i:300;s:6:\\"height\\";i:300;s:9:\\"mime - type\\";s:10:\\"image / jpeg\\";}s:14:\\"shop_thumbnail\\";a:4:{s:4:\\"file\\";s:44:\\"tieu - canh - ban - cong - nuoc - chung - cu - 100x100 . jpg\\";s:5:\\"width\\";i:100;s:6:\\"height\\";i:100;s:9:\\"mime - type\\";s:10:\\"image / jpeg\\";}}s:10:\\"image_meta\\";a:12:{s:8:\\"aperture\\";s:1:\\"0\\";s:6:\\"credit\\";s:0:\\"\\";s:6:\\"camera\\";s:0:\\"\\";s:7:\\"caption\\";s:0:\\"\\";s:17:\\"created_timestamp\\";s:1:\\"0\\";s:9:\\"copyright\\";s:0:\\"\\";s:12:\\"focal_length\\";s:1:\\"0\\";s:3:\\"iso\\";s:1:\\"0\\";s:13:\\"shutter_speed\\";s:1:\\"0\\";s:5:\\"title\\";s:0:\\"\\";s:11:\\"orientation\\";s:1:\\"0\\";s:8:\\"keywords\\";a:0:{}}}') ";

        }
    }


    $string .= "INSERT INTO `wp_postmeta` (`meta_id`, `post_id`, `meta_key`, `meta_value`) VALUES
	(null, @product_id, 'total_sales', '0'),
	(null, @product_id, '_tax_status', 'taxable'),
	(null, @product_id, '_tax_class', ''),
	(null, @product_id, '_manage_stock', 'no'),
	(null, @product_id, '_backorders', 'no'),
	(null, @product_id, '_sold_individually', 'no'),
	(null, @product_id, '_virtual', 'no'),
	(null, @product_id, '_downloadable', 'no'),
	(null, @product_id, '_download_limit', '-1'),
	(null, @product_id, '_download_expiry', '-1'),
	(null, @product_id, '_stock', null),
	(null, @product_id, '_stock_status', 'instock'),
	(null, @product_id, '_wc_average_rating', '0'),
	(null, @product_id, '_wc_review_count', '0'),
	(null, @product_id, '_regular_price', '".$data['product']['price']."'),
	(null, @product_id, '_sale_price', '".$data['product']['sale_price']."'),
	(null, @product_id, '_featured', ''),
	(null, @product_id, '_weight', '".$data['product']['weight']."'),
	(null, @product_id, '_length', '".$data['product']['length']."'),
	(null, @product_id, '_width', '".$data['product']['width']."'),
	(null, @product_id, '_height', '".$data['product']['height']."'),
	(null, @product_id, '_sku', '".$data['product']['sku']."'),
	(null, @product_id, '_product_attributes', ''),
	(null, @product_id, '_sale_price_dates_from', ''),
	(null, @product_id, '_sale_price_dates_to', ''),
	(null, @product_id, '_price', '".$data['product']['price']."'),
	(null, @product_id, '_amazon_product_id', '".$data['product']['EAN']."'),
	(null, @product_id, '_ebay_ean', '".$data['product']['EAN']."'),
	(null, @product_id, '_amazon_id_type', 'EAN'),
	(null, @product_id, '_visibility', 'visible'),
	(null, @product_id, '_product_version', '3.7.0'),";


    if($parent_post === 0){

        $string .= "(null, @product_parent, '_product_attributes', 'a:2:{s:8:\"pa_color\";a:6:{s:4:\"name\";s:8:\"pa_color\";s:5:\"value\";s:0:\"\";s:8:\"position\";s:1:\"2\";s:10:\"is_visible\";s:1:\"1\";s:12:\"is_variation\";s:1:\"1\";s:11:\"is_taxonomy\";s:1:\"1\";}s:7:\"pa_size\";a:6:{s:4:\"name\";s:7:\"pa_size\";s:5:\"value\";s:0:\"\";s:8:\"position\";s:1:\"3\";s:10:\"is_visible\";s:1:\"1\";s:12:\"is_variation\";s:1:\"1\";s:11:\"is_taxonomy\";s:1:\"1\";}}'),";
        $string .= "(null, @product_parent, '_product_attributes', 'a:2:{s:8:\"pa_color\";a:6:{s:4:\"name\";s:8:\"pa_color\";s:5:\"value\";s:0:\"\";s:8:\"position\";s:1:\"2\";s:10:\"is_visible\";s:1:\"1\";s:12:\"is_variation\";s:1:\"1\";s:11:\"is_taxonomy\";s:1:\"1\";}s:7:\"pa_size\";a:6:{s:4:\"name\";s:7:\"pa_size\";s:5:\"value\";s:0:\"\";s:8:\"position\";s:1:\"3\";s:10:\"is_visible\";s:1:\"1\";s:12:\"is_variation\";s:1:\"1\";s:11:\"is_taxonomy\";s:1:\"1\";}}'),";
        $string .= "(null, @product_parent, 'wc_productdata_options', 'a:1:{i:0;a:9:{s:11:\"_bubble_new\";s:0:\"\";s:12:\"_bubble_text\";s:0:\"\";s:17:\"_custom_tab_title\";s:0:\"\";s:11:\"_custom_tab\";s:0:\"\";s:14:\"_product_video\";s:0:\"\";s:19:\"_product_video_size\";s:0:\"\";s:24:\"_product_video_placement\";s:0:\"\";s:12:\"_top_content\";s:0:\"\";s:15:\"_bottom_content\";s:0:\"\";}}'),";
    }
    // 'a:2:{s:8:"pa_color";a:6:{s:4:"name";s:8:"pa_color";s:5:"value";s:0:"";s:8:"position";s:1:"0";s:10:"is_visible";s:1:"1";s:12:"is_variation";s:1:"1";s:11:"is_taxonomy";s:1:"1";}s:7:"pa_size";a:6:{s:4:"name";s:7:"pa_size";s:5:"value";s:0:"";s:8:"position";s:1:"1";s:10:"is_visible";s:1:"1";s:12:"is_variation";s:1:"1";s:11:"is_taxonomy";s:1:"1";}}';

    //$string .= "INSERT INTO `wp_postmeta` (`meta_id`, `post_id`, `meta_key`, `meta_value`) VALUES ";
    if(count($data['images']) > 0) {
        $concact_images = [];
        foreach ($data['images'] as $key => $image) {
            if ($key == 0) {
                $string .= "(null, @product_id, '_thumbnail_id', @image" . $key . "),";
            }
            $concact_images[] = '@image' . $key;
        }
        $string .= "(null, @product_id, '_product_image_gallery', CONCAT(".implode(", ','," ,$concact_images)."));";
    }

    $string .= "REPLACE INTO `wp_wc_product_meta_lookup` (`product_id`, `sku`, `virtual`, `downloadable`, `min_price`, `max_price`, `onsale`, `stock_quantity`, `stock_status`, `rating_count`, `average_rating`, `total_sales`) 
          VALUES (@product_id, '', '0', '0', '".$data['product']['sale_price']."', '".$data['product']['price']."', '1', NULL, 'instock', '0', '0', '0'); ";

    return $string;
}

$string .= import_main_product($data);

foreach($color_mapping as $cid){
    $string .= "INSERT INTO `wp_term_relationships` (`object_id`, `term_taxonomy_id`) VALUES (@product_parent, ".$cid.");";
    $string .= "UPDATE `wp_term_taxonomy` SET `count` = (`count` + 1) WHERE `term_taxonomy_id` = ".$cid."; ";
}
foreach($size_mapping as $sid){
    $string .= "INSERT INTO `wp_term_relationships` (`object_id`, `term_taxonomy_id`) VALUES (@product_parent, ".$sid.");";
    $string .= "UPDATE `wp_term_taxonomy` SET `count` = (`count` + 1) WHERE `term_taxonomy_id` = ".$sid."; ";
}


$string .= "INSERT INTO `wp_term_relationships` (`object_id`, `term_taxonomy_id`) VALUES (@product_id, 4) ;";

$string .= "UPDATE `wp_term_taxonomy` SET `count` = (`count` + 1)  WHERE `term_taxonomy_id` = 4 ;";

if(count($data['categories']) > 0){
    foreach ( $data['categories'] as $cateId){
        $string .= "INSERT INTO `wp_term_relationships` (`object_id`, `term_taxonomy_id`) VALUES (@product_id, ".$cateId.");";
        $string .= "UPDATE `wp_term_taxonomy` SET `count` = (`count` + 1) WHERE `term_taxonomy_id` = ".$cateId."; ";
        $string .= "UPDATE `wp_termmeta` SET `meta_value` = (`meta_value` + 1) WHERE `term_id` = ".$cateId." AND `meta_key` = 'product_count_product_cat';";
    }
}

if(count($data['tags']) > 0){
    foreach ( $data['tags'] as $cateId){
        $string .= "INSERT INTO `wp_term_relationships` (`object_id`, `term_taxonomy_id`) VALUES (@product_id, ".$cateId.");";
        $string .= "UPDATE `wp_term_taxonomy` SET `count` = (`count` + 1) WHERE `term_taxonomy_id` = ".$cateId."; ";
        $string .= "UPDATE `wp_termmeta` SET `meta_value` = (`meta_value` + 1) WHERE `term_id` = ".$cateId." AND `meta_key` = 'product_count_product_tag';";
    }
}


$product_children = "";
if(count($data['size']) > 0){
    $i = 0;
    foreach ( $data['size'] as $key => $size){
        foreach ( $data['color'] as $key2 => $size) {
            $cateId = $size_mapping[$key];
            $data['product']['title'] = $data['product']['title'] . '-' . $key. '-' . $key2;
            $string .= import_main_product($data, '', $data['product']['title'], 'product_variation', '@product_parent', $i);

            $string .= "INSERT INTO `wp_term_relationships` (`object_id`, `term_taxonomy_id`) VALUES (@product_id, " . $cateId . ");";
            $string .= "UPDATE `wp_term_taxonomy` SET `count` = (`count` + 1) WHERE `term_taxonomy_id` = " . $cateId . "; ";

            $cateId2 = $color_mapping[$key2];
            $string .= "INSERT INTO `wp_term_relationships` (`object_id`, `term_taxonomy_id`) VALUES (@product_id, " . $cateId2 . ");";
            $string .= "UPDATE `wp_term_taxonomy` SET `count` = (`count` + 1) WHERE `term_taxonomy_id` = " . $cateId2 . "; ";


            $string .= "INSERT INTO `wp_postmeta` (`meta_id`, `post_id`, `meta_key`, `meta_value`) VALUES (null, @product_id, 'attribute_pa_size', '" . strtolower($key) . "');";
            $string .= "INSERT INTO `wp_postmeta` (`meta_id`, `post_id`, `meta_key`, `meta_value`) VALUES (null, @product_id, 'attribute_pa_color', '" . strtolower($key2) . "');";

            $product_children .= "'i:".$i.";i:', @product_id_".$i." ,';',";

            $i++;
        }

    }

}


$string .= "DELETE FROM `wp_options` WHERE `option_name` = CONCAT('_transient_wc_product_children_',@product_parent) ;";
$string .= "DELETE FROM `wp_options` WHERE `option_name` = CONCAT('_transient_timeout_wc_product_children_',@product_parent) ;";

if(!empty($product_children)){
    $string .= "INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES (CONCAT('_transient_wc_product_children_',@product_parent), CONCAT('a:2:{s:3:\"all\";a:".$i.":{',".$product_children."'}s:7:\"visible\";a:".$i.":{',".$product_children."'}}'), 'no') ON DUPLICATE KEY UPDATE `option_name` = VALUES(`option_name`), `option_value` = VALUES(`option_value`), `autoload` = VALUES(`autoload`) ;";
}
$string .= "INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES (CONCAT('_transient_timeout_wc_product_children_',@product_parent), '".(time()+3600)."', 'no') ON DUPLICATE KEY UPDATE `option_name` = VALUES(`option_name`), `option_value` = VALUES(`option_value`), `autoload` = VALUES(`autoload`); ";
$string .= ";";
echo $string;