src/Controller/ImportController2.php line 3293

Open in your IDE?
  1. <?php
  3. namespace App\Controller;
  5. use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
  6. use Symfony\Component\HttpFoundation\Response;
  7. use Symfony\Component\Routing\Annotation\Route;
  11. use GuzzleHttp\Psr7;
  12. use GuzzleHttp\Client;
  13. use App\Controller\shopware\ProductFlat;
  14. use GuzzleHttp\Exception\RequestException;
  15. use stdClass;
  16. use Symfony\Component\HttpFoundation\Request;
  17. // use Symfony\Component\HttpFoundation\Response;
  18. // use Symfony\Component\Routing\Annotation\Route;
  19. use Symfony\Component\HttpFoundation\JsonResponse;
  20. use Symfony\Component\Validator\Constraints\DateTime;
  21. use Symfony\Component\HttpFoundation\RedirectResponse;
  22. // use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
  25. # E-MAIL
  26. use Symfony\Component\Mailer\MailerInterface;
  27. use Symfony\Component\Mime\Email;
  30. class ImportController2 extends AbstractController
  31. {
  32.     private $client = NULL;
  33.     private $header = NULL;
  35.     private $base_url; // Shop URL
  36.     private $client_id; // SW ID
  37.     private $client_secret; // SW Secret
  38.     private $client_username; // SW Username
  39.     private $client_password; // SW Password
  40.     private $shopware_data; // Shopware Datensatz
  41.     private $shopware_data_live; // Shopware Datenbank LIVE
  42.     private $shopware_data_dump; // AKN Datenbank Dump
  43.     private $shopware_data_dump_live; // AKN Datenbank Dump LIVE
  45.     private $surface_id; // Eigenschaften Gruppe ID Oberfläche
  46.     private $materialgroup_id; // Eigenschaften Gruppe ID Materialgruppe
  47.     private $material_id; // Eigenschaften Gruppe ID Material
  48.     private $articlegroup_id; // Eigenschaften Gruppe ID Artikelgruppe
  49.     private $zulauf_id; // Kategorie ID Zulauf Bald Verfügbar
  50.     private $default_category_id; // Default Kategorie ID
  51.     private $category_layout_id; // Default Kategorie Layout ID
  52.     private $color_id; // Eigenschaften Gruppe ID Farbe
  53.     private $fault_exists_id; // Eigenschaften Gruppe ID Fehler vorhanden
  54.     private $thickness_id; // Eigenschaften Gruppe ID Materialstärke
  55.     private $length_id; // Eigenschaften Gruppe ID Länge
  56.     private $width_id; // Eigenschaften Gruppe ID Höhe
  57.     private $amount_id; // Eigenschaften Gruppe ID Anzahl
  58.     private $surfacearea_id; // Eigenschaften Gruppe ID Quadratmeter
  59.     private $meter_id; // Eigenschaften Gruppe ID Meter
  60.     private $stonenumber_id; // Eigenschaften Gruppe ID Steinnummer
  61.     private $sa_id; // Eigenschaften Gruppe ID Sa
  62.     private $squaremeterprice_id; // Eigenschaften Gruppe ID Quadratmeterpreis
  63.     private $tax_id; // ID der Defaultsteuern
  64.     private $currency_id; //ID der Defaultwährung
  65.     private $deliverytime_id; //ID der Defaultlieferzeit
  66.     private $saleschannel_id; //ID der Default Verkaufskanal
  67.     private $salutation_id; // ID der Default Ansprache (Keine Angabe)
  68.     private $payment_method_id; // ID der Default Zahlungsmethode (Nachname)
  69.     private $group_id; // ID der Default Kundengruppe
  70.     private $country_id; // ID des Default Landes (Deutschland)
  73.     private $default_image_filename; // Default Bildername
  74.     private $language_id;  // DEUTSCH
  76.     // private $steine_aus_aller_welt_category_id = 'a9bb98a9e33e4a969b71f48bd1702ab7';    // ID der Parent-Kategorie "Steine aus aller Welt"
  80.     function __construct() {
  82.         $this->base_url = $_ENV['base_url']; // Base URL
  83.         if(isset($_ENV['client_id'])){
  84.             $this->client_id = $_ENV['client_id']; // SW ID
  85.         }
  86.         if(isset($_ENV['client_secret'])){
  87.             $this->client_secret = $_ENV['client_secret']; // SW Secret
  88.         }
  89.         if(isset($_ENV['client_username'])){
  90.             $this->client_username = $_ENV['client_username']; // SW Username
  91.         }
  92.         if(isset($_ENV['client_password'])){
  93.             $this->client_password = $_ENV['client_password']; // SW Password
  94.         }
  95.         $this->shopware_data = $_ENV['shopware_data']; // Shopware Datensatz
  96.         $this->shopware_data_live = $_ENV['shopware_data_live']; // Shopware Datenbank LIVE
  97.         $this->shopware_data_dump = $_ENV['shopware_data_dump']; // AKN Datenbank Dump
  98.         $this->shopware_data_dump_live = $_ENV['shopware_data_dump_live']; // AKN Datenbank Dump LIVE
  100.         $this->surface_id = $_ENV['surface_id']; // Eigenschaften Gruppe ID Oberfläche
  101.         $this->materialgroup_id = $_ENV['materialgroup_id']; // Eigenschaften Gruppe ID Materialgruppe
  102.         $this->material_id = $_ENV['material_id']; // Eigenschaften Gruppe ID Material
  103.         $this->articlegroup_id = $_ENV['articlegroup_id']; // Eigenschaften Gruppe ID Artikelgruppe
  104.         $this->zulauf_id = $_ENV['zulauf_id']; // Kategorie ID Zulauf Bald Verfügbar
  105.         $this->default_category_id = $_ENV['default_category_id']; // Default Kategorie ID
  106.         $this->category_layout_id = $_ENV['category_layout_id']; // Default Kategorie Layout ID
  107.         $this->color_id = $_ENV['color_id']; // Eigenschaften Gruppe ID Farbe
  108.         $this->fault_exists_id = $_ENV['fault_exists_id']; // Eigenschaften Gruppe ID Fehler vorhanden
  109.         $this->thickness_id = $_ENV['thickness_id']; // Eigenschaften Gruppe ID Materialstärke
  110.         $this->blocknumber_id = $_ENV['blocknumber_id']; // Eigenschaften Gruppe ID Blocknummer
  111.         $this->default_image_filename = $_ENV['default_image_filename']; // Default Bildername
  112.         $this->language_id = $_ENV['language_id']; // ID der Defaultsprache
  113.         $this->length_id=$_ENV['length_id']; // Eigenschaften Gruppe ID Länge
  114.         $this->width_id=$_ENV['width_id']; // Eigenschaften Gruppe ID Höhe
  115.         $this->amount_id=$_ENV['amount_id']; // Eigenschaften Gruppe ID Anzahl
  116.         $this->surfacearea_id=$_ENV['surfacearea_id']; // Eigenschaften Gruppe ID Quadratmeter
  117.         $this->meter_id=$_ENV['meter_id']; // Eigenschaften Gruppe ID Meter
  118.         $this->stonenumber_id = $_ENV['stonenumber_id']; // Eigenschaften Gruppe ID Steinnummer
  119.         $this->sa_id=$_ENV['sa_id']; // Eigenschaften Gruppe ID Sa
  120.         $this->squaremeterprice_id=$_ENV['squaremeterprice_id']; // Eigenschaften Gruppe ID Quadratmeterpreis
  121.         $this->tax_id=$_ENV["tax_id"]; //ID der Defaultsteuern
  122.         $this->currency_id=$_ENV["currency_id"]; // ID der Defaultwährung
  123.         $this->deliverytime_id=$_ENV["deliverytime_id"]; //ID der Defaultlieferzeit
  124.         $this->saleschannel_id=$_ENV["saleschannel_id"]; //ID der Default Verkaufskanal
  125.         $this->salutation_id=$_ENV["salutation_id"]; // ID der Default Ansprache (Keine Angabe)
  126.         $this->payment_method_id=$_ENV["payment_method_id"]; // ID der Default Zahlungsmethode (Nachname)
  127.         $this->group_id=$_ENV["group_id"]; // ID der Default Kundengruppe
  128.         $this->country_id=$_ENV["country_id"]; // ID des Default Landes (Deutschland)
  129.     }
  132.     /**
  133.      * Route("/import", name="import")
  134.      */
  135.     public function index(): Response
  136.     {
  137.         return $this->render('import/index.html.twig', [
  138.             'controller_name' => 'ImportController',
  139.         ]);
  140.     }
  142.     /**
  143.      * ######## Verbindungsaufbau zur Shopware-API ########
  144.      * 
  145.      * @return array
  146.      */
  147.     private function client_connect()
  148.     {
  149.         // $client = new Client([ 'timeout' => 5.0, ]);        
  150.         $this->client = new Client([
  151.             'base_uri' => $this->base_url.'api',
  152.         ]);
  153.             // var_dump("123");exit;
  154.         
  155.             // return $this->render('update/index.html.twig', ["return" => ['user' => "segrd"]]);
  156.         if(isset($this->client_username) && isset($this->client_password)){
  157.             try{
  158.                 // var_dump("<pre>");
  159.                 $response = $this->client->request('POST', '/api/oauth/token', array( 
  160.                     // "debug" => true, 
  161.                     'allow_redirects'=>array('strict'=>true),
  162.                     "form_params" => array(
  163.                     "client_id"=> "administration",
  164.                     "grant_type"=> "password",
  165.                     "scopes"=> "write",
  166.                     "username"=> $this->client_username,
  167.                     "password"=> $this->client_password
  168.                     )
  169.                 ));
  171.                 // $response = $this->client->request('POST', '/api/oauth/token', array( "form_params" => array(
  172.                 //     "debug" => true, 
  173.                 //     "client_id"=> "administration",
  174.                 //     "grant_type"=> "password",
  175.                 //     "scopes"=> "write",
  176.                 //     "username"=> $this->client_username,
  177.                 //     "password"=> $this->client_password
  178.                 // )));
  179.             } catch (RequestException $e) {
  180.                 // echo Psr7\str($e->getRequest());
  181.                 // if ($e->hasResponse()) {
  182.                 //     echo Psr7\str($e->getResponse());
  183.                 // }
  184.                 if ($e->hasResponse()) {
  185.                     $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
  186.                 }else{
  187.                     $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
  188.                 }
  189.                 $response->headers->set('Content-Type', 'application/json');
  190.                 return $response;
  191.                 exit;
  192.             }
  193.         }else {
  194.             try{
  195.                 if(isset($this->client_secret) && isset($this->client_id)){
  196.                     $response = $this->client->request('POST', '/api/oauth/token', array( "form_params" => array(
  197.                         // "debug" => true, 
  198.                         'allow_redirects'=>array('strict'=>true),
  199.                         "grant_type" => "client_credentials",
  200.                         "client_id" =>  $this->client_id,
  201.                         "client_secret" =>  $this->client_secret
  202.                     )));
  203.                 }
  204.             } catch (RequestException $e) {
  205.                 // echo Psr7\str($e->getRequest());
  206.                 // if ($e->hasResponse()) {
  207.                 //     echo Psr7\str($e->getResponse());
  208.                 // }
  209.                 if ($e->hasResponse()) {
  210.                     $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
  211.                 }else{
  212.                     $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
  213.                 }
  214.                 $response->headers->set('Content-Type', 'application/json');
  215.                 return $response;
  216.                 exit;
  217.             }
  218.         }
  219.         $response_body = json_decode($response->getBody()->getContents());
  221.         //** Header used for every request
  222.         $this->header = array(
  223.             "Accept" => "application/json",
  224.             "Content-type" => "application/json",
  225.             "Authorization" => $response_body->token_type." ".$response_body->access_token,
  226.         );
  227.     }
  228.     private function shop_connect(){
  229.         // var_dump($this->getDoctrine()->getConnection( $this->shopware_data));exit;
  230.         // return $this->getDoctrine()->getConnection( $this->shopware_data);
  231.         return $this->getDoctrine()->getConnection( $this->shopware_data_live);
  232.     }
  234.     private function dump_connect(){
  235.         // var_dump($this->getDoctrine()->getConnection( $this->shopware_data));exit;
  236.         // return $this->getDoctrine()->getConnection( $this->shopware_data_dump);
  237.         return $this->getDoctrine()->getConnection( $this->shopware_data_dump_live);
  238.     }
  240.     /**
  241.      * get color property id for import
  242.      */
  243.     public function getColorID($mgr_id, $mar_id)
  244.     {
  245.         $this->client_connect();
  246.         $conn_shopware = $this->shop_connect();
  247.         $conn_akn = $this->dump_connect();
  249.         $sql = "SELECT *
  250.                 FROM materialcolors
  251.                 WHERE color_id != -1
  252.                 AND mgr_id = '".$mgr_id."'
  253.                 AND mar_id = '".$mar_id."'
  254.             ;";
  255.         $stmt = $conn_akn->prepare($sql);
  256.         $stmt->execute();
  257.         $colors = $stmt->fetchAll();
  258.         // return $this->render('update/index.html.twig', ["return" => ['success' => $colors ]]);
  259.         if(count($colors) < 1 && !isset($colors[0])){
  260.             // wenn farbe nicht gefunden überprüfe ob nur mit mar_id eine gefunden werden kann
  261.             $sql = "SELECT *
  262.                     FROM materialcolors
  263.                     WHERE color_id != -1
  264.                     AND mar_id = '".$mar_id."'
  265.                 ;";
  266.             $stmt = $conn_akn->prepare($sql);
  267.             $stmt->execute();
  268.             $colors = $stmt->fetchAll();
  269.         }
  270.         if(isset($colors[0]["color_id"]) && !empty($colors[0]["color_id"])){
  271.             $sql = "SELECT *
  272.                     FROM `keys`
  273.                     WHERE key_kind = 88
  274.                     AND key_number = '".$colors[0]["color_id"]."'
  275.                 ;";
  276.             $stmt = $conn_akn->prepare($sql);
  277.             $stmt->execute();
  278.             $key = $stmt->fetchAll();
  279. // return $this->render('update/index.html.twig', ["return" => ['success' => $key ]]);
  280.             if(isset($key[0]["key_text"]) && !empty($key[0]["key_text"])){
  281.                 // farbe wurde gefunden -> überprüfe ob als variante vorhanden
  282.                 $color = explode('#',$key[0]["key_text"]);
  283.                 $key[0]["key_text"] = trim($color[0]);
  284.                 // return $key[0]["key_text"];
  286.                 // $this->color_id;
  288.                 $sql = "SELECT 
  289.                         *
  290.                         , HEX(id) as id
  291.                         , HEX(language_id) as language_id
  292.                         , HEX(property_group_id) as property_group_id
  293.                     FROM property_group_option
  294.                     INNER JOIN property_group_option_translation  ON (property_group_option.id = property_group_option_translation.property_group_option_id)
  295.                     WHERE LOWER(HEX(property_group_id)) = '".$this->color_id."'
  296.                     AND `name` = '".$key[0]["key_text"]."'
  297.                     ;";
  298.                 $stmt = $conn_shopware->prepare($sql);
  299.                 $stmt->execute();
  300.                 $property_color = $stmt->fetchAll();
  302.                 // return $this->render('update/index.html.twig', ["return" => ['materials' => $materials, 'search_materials' => $search_materials]]);
  303.                 if(isset($property_color) && !empty($property_color) && count($property_color) > 0){
  304.                     //property vorhanden
  305.                     // var_dump("exist color ".$key[0]["key_text"]);
  306.                 }else{
  307.                     // var_dump("post color ".$key[0]["key_text"]);
  308.                     $post_property = $this->client->request('POST', '/api/property-group-option', array(
  309.                         'allow_redirects'=>array('strict'=>true),
  310.                         'headers' => $this->header,
  311.                         'body' => json_encode([
  312.                             'groupId' => $this->color_id,
  313.                             'name' => $key[0]["key_text"]
  314.                         ])
  315.                     ));
  317.                     $sql = "SELECT 
  318.                             *
  319.                             , LOWER(HEX(id)) as id
  320.                             , HEX(language_id) as language_id
  321.                             , HEX(property_group_id) as property_group_id
  322.                         FROM property_group_option
  323.                         INNER JOIN property_group_option_translation  ON (property_group_option.id = property_group_option_translation.property_group_option_id)
  324.                         WHERE LOWER(HEX(property_group_id)) = '".$this->color_id."'
  325.                         AND `name` = '".$key[0]["key_text"]."'
  326.                         ;";
  327.                     $stmt = $conn_shopware->prepare($sql);
  328.                     $stmt->execute();
  329.                     $property_color = $stmt->fetchAll();
  330.                 }
  331.                 $property_color[0]["id"] = strtolower($property_color[0]["id"]);
  332.                 // return $this->render('update/index.html.twig', ["return" => ['color' => $property_color ]]);
  333.                 if(isset($property_color[0]["id"]) && !empty($property_color[0]["id"])) {
  334.                     // var_dump("EXISTS");
  335.                     return $property_color[0]["id"];
  336.                 }
  338.             }
  339.         }
  340.         return null;
  341.     }
  342.     
  343.     /**
  344.      * get category id for import
  345.      */
  346.     public function getCategoryId($category, $materialgroup_id) //article[materialgroup_name]
  347.     {
  348.         // prüfe ob kategorie vorhanden ist, 
  349.         // var_dump($category);
  350.         try {
  351.             $search = $this->client->request('GET', '/api/category?filter[name]='.$category.'', array( 
  352.                 "headers" => $this->header,
  353.             ));
  354.         } catch (RequestException $e) {
  355.             // echo Psr7\str($e->getRequest());
  356.             // if ($e->hasResponse()) {
  357.             //     echo Psr7\str($e->getResponse());
  358.             // }
  359.             if ($e->hasResponse()) {
  360.                 $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
  361.             }else{
  362.                 $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
  363.             }
  364.             $response->headers->set('Content-Type', 'application/json');
  365.             return $response;
  366.             exit;
  367.         }
  368.         $categories = json_decode($search->getBody()->getContents(),true);
  370.         if(isset($categories["data"][0]["id"]) && !empty($categories["data"][0]["id"])){
  371.         }else{
  373.             $CategoryFlat = new \App\Controller\shopware\CategoryFlat();
  374.             $CategoryFlat->setType("page");
  375.             $CategoryFlat->setName($materialgroup["materialgroup_name"]);
  376.             $CategoryFlat->setCustomFields(array("custom_materialgroup_id" => $materialgroup_id));
  377.             $CategoryFlat->setCmsPageId($this->category_layout_id); 
  378.             $CategoryFlat->setParentId($this->default_category_id);
  380.             $post_category = $this->client->request('POST', '/api/category', array(
  381.                 // 'debug' => true, 
  382.                 'allow_redirects'=>array('strict'=>true),
  383.                 "headers" => $this->header,
  384.                 "body" => $CategoryFlat,
  385.             ));
  387.             try {
  388.                 $search = $this->client->request('GET', '/api/category?filter[name]='.$category.'', array( 
  389.                     "headers" => $this->header,
  390.                 ));
  391.             } catch (RequestException $e) {
  392.                 // echo Psr7\str($e->getRequest());
  393.                 // if ($e->hasResponse()) {
  394.                 //     echo Psr7\str($e->getResponse());
  395.                 // }
  396.                 if ($e->hasResponse()) {
  397.                     $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
  398.                 }else{
  399.                     $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
  400.                 }
  401.                 $response->headers->set('Content-Type', 'application/json');
  402.                 return $response;
  403.                 exit;
  404.             }
  405.             $categories = json_decode($search->getBody()->getContents(),true);
  406.         }
  407.         if(isset($categories["data"][0]["id"]) && !empty($categories["data"][0]["id"])){
  408.             return $categories["data"][0]["id"];
  409.         }else{
  410.             return $this->default_category_id; 
  411.         }
  412.     }
  416.     /**
  417.      * Fügt Attribute den eingelesenen Artikeln hinzu
  418.      * 
  419.      * Route("/add/attributes", name="add_attributes")
  420.      * 
  421.      * @return mixed
  422.      */
  423.     public function add_attributes()
  424.     {
  425.         // TODO suchtags über suchschlagwörter einbinden
  427.         $this->client_connect();
  428.         $conn_shopware = $this->shop_connect();
  429.         $step_count = 5;
  431.         $sql = "SELECT 
  432.             *
  433.             , LOWER(HEX(product_id)) as product_id
  434.             , HEX(product_version_id) as product_version_id
  435.             , HEX(language_id) as language_id
  436.             , JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_material_name') as custom_article_material_name
  437.             , JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_materialgroup_name') as custom_article_materialgroup_name
  438.             , JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_surface') as custom_article_surface
  439.             , JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_articlegroup_name') as custom_article_articlegroup_name
  440.             -- , JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_picture') as custom_article_picture
  441.             -- materialstärke
  442.         FROM product_translation
  443.         INNER JOIN product ON (product.id = product_id)
  444.         WHERE JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_attributes_added') IS NULL
  445.         AND LOWER(HEX(language_id)) = '2fbb5fe2e29a4d70aa5854ce7ce3e20b'
  446.         LIMIT 10
  447.         ;";
  448.         $stmt = $conn_shopware->prepare($sql);
  449.         $stmt->execute();
  450.         $products_sw = $stmt->fetchAll();
  452.         // return $this->render('update/index.html.twig', ["return" => ['attribute' => $products_sw ]]);
  453.         foreach($products_sw as $product){
  454.             // produkte abholen die flag nicht haben
  455.             // einzelne customfelder durchgehen und attribute zuordnen
  456.             // nicht vorhandene skippen
  457.             
  458.             
  459.             unset($attribute);
  460.             unset($attributsliste);
  461.             $attributsliste=array();
  462.             $idliste=array();
  463.             if(isset($product["custom_article_material_name"]) && !empty($product["custom_article_material_name"] && $product["custom_article_material_name"] != "null")){
  464.                 // array_push($attributsliste,str_replace('"','',$product["custom_article_material_name"]));
  465.                 // array_push($idliste,$this->material_id);
  466.                 // var_dump($this->material_id);
  467.                 $attributsliste[$this->material_id]=str_replace('"','',$product["custom_article_material_name"]);
  468.             }
  469.             if(isset($product["custom_article_materialgroup_name"]) && !empty($product["custom_article_materialgroup_name"] && $product["custom_article_materialgroup_name"] != "null")){
  470.                 // array_push($attributsliste,str_replace('"','',$product["custom_article_materialgroup_name"]));
  471.                 // array_push($idliste,$this->materialgroup_id);
  472.                 // var_dump($this->materialgroup_id);
  473.                 $attributsliste[$this->materialgroup_id]=str_replace('"','',$product["custom_article_materialgroup_name"]);
  474.             }
  475.             if(isset($product["custom_article_surface"]) && !empty($product["custom_article_surface"]) && $product["custom_article_surface"] != "null"){
  476.                 // array_push($attributsliste,str_replace('"','',$product["custom_article_surface"]));
  477.                 // array_push($idliste,$this->surface_id);
  478.                 // var_dump($product["custom_article_surface"]);
  479.                 $attributsliste[$this->surface_id]=str_replace('"','',$product["custom_article_surface"]);
  480.             }
  481.             if(isset($product["custom_article_articlegroup_name"]) && !empty($product["custom_article_articlegroup_name"] && $product["custom_article_articlegroup_name"] != "null")){
  482.                 // array_push($attributsliste,str_replace('"','',$product["custom_article_articlegroup_name"]));
  483.                 // array_push($idliste,$this->articlegroup_id);
  484.                 // var_dump($this->articlegroup_id);
  485.                 $attributsliste[$this->articlegroup_id]=str_replace('"','',$product["custom_article_articlegroup_name"]);
  486.             }
  487.             // return $this->render('update/index.html.twig', ["return" => ['attribute' => $attributsliste ]]);
  488.             $attribute=array();
  489.             foreach($attributsliste as $key => $attribut){
  490.                 $sql = "SELECT 
  491.                         *
  492.                         , LOWER(HEX(id)) as id
  493.                         , HEX(language_id) as language_id
  494.                         , HEX(property_group_id) as property_group_id
  495.                     FROM property_group_option
  496.                     INNER JOIN property_group_option_translation  ON (property_group_option.id = property_group_option_translation.property_group_option_id)
  497.                     -- WHERE LOWER(HEX(property_group_id)) = 'eadafb18e0c34aeb894dafddf1e107a0'
  498.                     WHERE `name` = '".$attribut."'
  499.                     AND LOWER(HEX(property_group_id)) = '".$key."'
  500.                     ;";
  501.                 $stmt = $conn_shopware->prepare($sql);
  502.                 $stmt->execute();
  503.                 $search_property = $stmt->fetchAll();
  504.                 // return $this->render('update/index.html.twig', ["return" => ['dearch' => $search_property ]]);
  505.                 if(isset($search_property) && is_array($search_property) && count($search_property) > 0) {
  506.                     array_push($attribute,array('id'=>$search_property[0]["id"]));
  507.                 }else{
  508.                     // Attribut nicht in shopware und muss hinzugefügt werden
  509.                     // var_dump("post ".$attribut." ".$key);exit;
  510.                     $post_product = $this->client->request('POST', '/api/property-group-option', array(
  511.                         'allow_redirects'=>array('strict'=>true),
  512.                         'headers' => $this->header,
  513.                         'body' => json_encode([
  514.                             'groupId' => $key,
  515.                             'name' => $attribut
  516.                         ])
  517.                     ));
  519.                     $sql = "SELECT 
  520.                             *
  521.                             , LOWER(HEX(id)) as id
  522.                             , HEX(language_id) as language_id
  523.                             , HEX(property_group_id) as property_group_id
  524.                         FROM property_group_option
  525.                         INNER JOIN property_group_option_translation  ON (property_group_option.id = property_group_option_translation.property_group_option_id)
  526.                         -- WHERE LOWER(HEX(property_group_id)) = 'eadafb18e0c34aeb894dafddf1e107a0'
  527.                         WHERE `name` = '".$attribut."'
  528.                         AND LOWER(HEX(property_group_id)) = '".$key."'
  529.                         ;";
  530.                     $stmt = $conn_shopware->prepare($sql);
  531.                     $stmt->execute();
  532.                     $search_property = $stmt->fetchAll();
  533.                     // return $this->render('update/index.html.twig', ["return" => ['dearch' => $search_property ]]);
  534.                     array_push($attribute,array('id'=>$search_property[0]["id"]));
  536.                 }
  537.                 
  538.             }
  539.             // return $this->render('update/index.html.twig', ["return" => ['attribute' => $attribute ]]);
  540.             if(isset($product["custom_article_materialgroup_id"]) && !empty($product["custom_article_materialgroup_id"]) &&
  541.                isset($product["custom_article_material_id"]) && !empty($product["custom_article_material_id"])){
  543.                 $farbid=$this->getColorID($product["custom_article_materialgroup_id"],$product["custom_article_material_id"]);
  544.                 
  545.                 var_dump($farbid);exit;
  546.             }
  547.             $ProductFlat = new \App\Controller\shopware\ProductFlat();
  548.             $ProductFlat->setProperties($attribute);
  549.             $ProductFlat->setCustomFields(array(
  550.                 "custom_article_attributes_added" => "1"
  551.             ));
  552.             try {
  553.                 $post_product = $this->client->request('PATCH', '/api/product/'.$product["product_id"], array(
  554.                     // 'debug' => true,
  555.                     'allow_redirects'=>array('strict'=>true),
  556.                     "headers" => $this->header,
  557.                     "body" => $ProductFlat
  558.                     // 'body' => json_encode([
  559.                     //     'properties' => array(
  560.                     //         $attribute
  561.                     //     )
  562.                     //     ,
  563.                     // 'customFields' => array(
  564.                     //         "custom_article_attributes_added" => "1"
  565.                     //     )
  566.                     // ])
  567.                 ));
  568.             } catch (RequestException $e) {
  569.                 // echo Psr7\str($e->getRequest());
  570.                 // if ($e->hasResponse()) {
  571.                 //     echo Psr7\str($e->getResponse());
  572.                 // }
  573.                 if ($e->hasResponse()) {
  574.                     $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
  575.                 }else{
  576.                     $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
  577.                 }
  578.                 $response->headers->set('Content-Type', 'application/json');
  579.                 return $response;
  580.                 exit;
  581.             }
  582.             // return $this->render('update/index.html.twig', ["return" => ['dearch' => $product ]]);
  583.             
  584.             // try{
  585.             //     $post_product = $this->client->request('PATCH', '/api/product/'.$product["product_id"], array(
  586.             //         'headers' => $this->header,
  587.             //         'body' => json_encode([
  588.             //             'customFields' => array(
  589.             //                 "custom_article_attributes_added" => "1"
  590.             //             )
  591.             //         ])
  592.             //     ));
  593.             // } catch (RequestException $e) {
  594.             //     echo Psr7\str($e->getRequest());
  595.             //     if ($e->hasResponse()) {
  596.             //         echo Psr7\str($e->getResponse());
  597.             //     }
  598.             //     exit;
  599.             // }
  600.             // return $this->render('update/index.html.twig', ["return" => ['surfaces' => $product["product_id"]]]);
  601.         }
  603.         
  604.         if(count($products_sw) < 11){
  605.             return $this->redirect('/add/attributes');
  606.         }else{
  607.             return $this->render('update/index.html.twig', ["return" => ['Attribute' => "done" ]]);
  608.         }
  609.         // return $this->render('update/index.html.twig', ["return" => ['attribute' => "fertig" ]]);
  611.     }
  613.     
  614.     /**
  615.      * Importiert Attribute in Shopware
  616.      * 
  617.      * Route("/import/attributes", name="import_attributes")
  618.      * 
  619.      * @return mixed
  620.      */
  621.     public function import_attributes()
  622.     {
  623.         $this->client_connect();
  624.         $conn_shopware = $this->shop_connect();
  626.         //gehe alle attribute durch und erstelle eigenschaften in entsprechenden gruppen
  628.         // wahlweise: aufruf wenn eigenschaften produkt zugeordnet werden sollen. nimmt eigenschaften entgegen,
  629.         // gleicht mit vorhandenen ab, erstellt neue und löscht überschüssige
  631.         // Oberflächen
  632.         $sql = "SELECT 
  633.                 DISTINCT surface_name
  634.                 FROM surfaces
  635.             ;";
  636.         $stmt = $conn_shopware->prepare($sql);
  637.         $stmt->execute();
  638.         $surfaces = $stmt->fetchAll();
  639.         $surfaces = array_map('current', $surfaces);
  641.         // $surface_id="0fcb8899440540a5aef143da8f82d096";
  642.         foreach($surfaces as $surface){
  644.             $sql = "SELECT 
  645.                     *
  646.                     , HEX(id) as id
  647.                     , HEX(language_id) as language_id
  648.                     , HEX(property_group_id) as property_group_id
  649.                 FROM property_group_option
  650.                 INNER JOIN property_group_option_translation  ON (property_group_option.id = property_group_option_translation.property_group_option_id)
  651.                 WHERE LOWER(HEX(property_group_id)) = '".$this->surface_id."'
  652.                 AND `name` = '".$surface."'
  653.                 ;";
  654.             $stmt = $conn_shopware->prepare($sql);
  655.             $stmt->execute();
  656.             $search_surfaces = $stmt->fetchAll();
  658.             // return $this->render('update/index.html.twig', ["return" => ['surfaces' => $surfaces, 'search_surfaces' => $search_surfaces]]);
  659.             
  660.             if(isset($search_surfaces) && is_array($search_surfaces) && count($search_surfaces) > 0) {
  661.             }else{
  662.                 if(isset($surface) && !empty($surface)){
  663.                 // return $this->render('update/index.html.twig', ["return" => ['surfaces' => $surface, 'search_surfaces' => $search_surfaces]]);
  664.                     $post_product = $this->client->request('POST', '/api/property-group-option', array(
  665.                         'allow_redirects'=>array('strict'=>true),
  666.                         'headers' => $this->header,
  667.                         'body' => json_encode([
  668.                             'groupId' => $this->surface_id,
  669.                             'name' => $surface
  670.                         ])
  671.                     ));
  672.                 }
  673.             }
  675.         }
  676.         return $this->render('update/index.html.twig', ["return" => ['surfaces' => "surfaces done" ]]);
  679.         // Materialgruppen
  680.         $sql = "SELECT 
  681.                 DISTINCT materialgroup_name
  682.                 FROM materialgroups
  683.             ;";
  684.             // OFFSET ".(($page - 1) * $step_count)."
  685.         $stmt = $conn_shopware->prepare($sql);
  686.         $stmt->execute();
  687.         $materialgroups = $stmt->fetchAll();
  688.         $materialgroups = array_map('current', $materialgroups);
  690.         // $materialgroup_id="4d73f7a67c184ca294e778772b860050";
  691.         foreach($materialgroups as $materialgroup){
  693.             $sql = "SELECT 
  694.                     *
  695.                     , HEX(id) as id
  696.                     , HEX(language_id) as language_id
  697.                     , HEX(property_group_id) as property_group_id
  698.                 FROM property_group_option
  699.                 INNER JOIN property_group_option_translation  ON (property_group_option.id = property_group_option_translation.property_group_option_id)
  700.                 WHERE LOWER(HEX(property_group_id)) = '".$this->materialgroup_id."'
  701.                 AND `name` = '".$materialgroup."'
  702.                 ;";
  703.             $stmt = $conn_shopware->prepare($sql);
  704.             $stmt->execute();
  705.             $search_materialgroups = $stmt->fetchAll();
  707.             // return $this->render('update/index.html.twig', ["return" => ['materialgroups' => $materialgroups, 'search_materialgroups' => $search_materialgroups]]);
  709.             if(isset($search_materialgroups) && is_array($search_materialgroups) && count($search_materialgroups) > 0) {
  710.             }else{
  711.                 if(isset($materialgroup) && !empty($materialgroup)){
  712.                     $post_product = $this->client->request('POST', '/api/property-group-option', array(
  713.                         'allow_redirects'=>array('strict'=>true),
  714.                         'headers' => $this->header,
  715.                         'body' => json_encode([
  716.                             'groupId' => $this->materialgroup_id,
  717.                             'name' => $materialgroup
  718.                         ])
  719.                     ));
  720.                 }
  721.             }
  722.         }
  723.         return $this->render('update/index.html.twig', ["return" => ['materialgroups' => "materialgroups done" ]]);
  725.         // Material
  726.         $sql = "SELECT 
  727.                 DISTINCT material_name
  728.                 FROM materials
  729.             ;";
  730.             // OFFSET ".(($page - 1) * $step_count)."
  731.         $stmt = $conn_shopware->prepare($sql);
  732.         $stmt->execute();
  733.         $materials = $stmt->fetchAll();
  734.         $materials = array_map('current', $materials);
  736.         // $material_id="ed4fe3bbe67941e4b4ed47c3308e32f2";
  737.         foreach($materials as $material){
  739.             $sql = "SELECT 
  740.                     *
  741.                     , HEX(id) as id
  742.                     , HEX(language_id) as language_id
  743.                     , HEX(property_group_id) as property_group_id
  744.                 FROM property_group_option
  745.                 INNER JOIN property_group_option_translation  ON (property_group_option.id = property_group_option_translation.property_group_option_id)
  746.                 WHERE LOWER(HEX(property_group_id)) = '".$this->material_id."'
  747.                 AND `name` = '".$material."'
  748.                 ;";
  749.             $stmt = $conn_shopware->prepare($sql);
  750.             $stmt->execute();
  751.             $search_materials = $stmt->fetchAll();
  753.             // return $this->render('update/index.html.twig', ["return" => ['materials' => $materials, 'search_materials' => $search_materials]]);
  755.             if(isset($search_materials) && is_array($search_materials) && count($search_materials) > 0) {
  756.             }else{
  757.                 if(isset($material) && !empty($material)){
  758.                     $post_product = $this->client->request('POST', '/api/property-group-option', array(
  759.                         'allow_redirects'=>array('strict'=>true),
  760.                         'headers' => $this->header,
  761.                         'body' => json_encode([
  762.                             'groupId' => $this->material_id,
  763.                             'name' => $material
  764.                             ])
  765.                         ));
  766.                 }
  767.             }
  768.         }
  769.         return $this->render('update/index.html.twig', ["return" => ['materials' => "materials done" ]]);
  771.         // Artikelgruppe
  772.         $sql = "SELECT 
  773.                 DISTINCT articlegroup_name
  774.                 FROM articlegroups
  775.             ;";
  776.         $stmt = $conn_shopware->prepare($sql);
  777.         $stmt->execute();
  778.         $articlegroups = $stmt->fetchAll();
  779.         $articlegroups = array_map('current', $articlegroups);
  781.         // $articlegroup_id="025caac2d61d49968dc84c2e291adef6";
  782.         foreach($articlegroups as $articlegroup){
  783.             $sql = "SELECT 
  784.                     *
  785.                     , HEX(id) as id
  786.                     , HEX(language_id) as language_id
  787.                     , HEX(property_group_id) as property_group_id
  788.                 FROM property_group_option
  789.                 INNER JOIN property_group_option_translation  ON (property_group_option.id = property_group_option_translation.property_group_option_id)
  790.                 WHERE LOWER(HEX(property_group_id)) = '".$this->articlegroup_id."'
  791.                 AND `name` = '".$articlegroup."'
  792.                 ;";
  793.             $stmt = $conn_shopware->prepare($sql);
  794.             $stmt->execute();
  795.             $search_articlegroups = $stmt->fetchAll();
  796.             // return $this->render('update/index.html.twig', ["return" => ['articlegroups' => $articlegroups, 'search_articlegroups' => $search_articlegroups]]);
  798.             if(isset($search_articlegroups) && is_array($search_articlegroups) && count($search_articlegroups) > 0) {
  799.             }else{
  800.                 $post_product = $this->client->request('POST', '/api/property-group-option', array(
  801.                     'allow_redirects'=>array('strict'=>true),
  802.                     'headers' => $this->header,
  803.                     'body' => json_encode([
  804.                         'groupId' => $this->articlegroup_id,
  805.                         'name' => $articlegroup
  806.                     ])
  807.                 ));
  808.             }
  809.         }
  810.         // return $this->render('update/index.html.twig', ["return" => ['articlegroups' => "articlegroups done" ]]);
  811.         return $this->render('update/index.html.twig', ["return" => ['surfaces' => $surfaces, 'materialgroups' => $materialgroups, 'materials' => $materials, 'articlegroups' => $articlegroups]]);
  812.     }
  813.     
  814.     /**
  815.      * Erwartet Storage-Array, returned formatierten Array der Eigenschaften für Variantenimport
  816.      */
  817.     public function get_variant_ids($storage)
  818.     {
  819.         $this->client_connect();
  820.         $conn_shopware = $this->shop_connect();
  821.         $conn_akn = $this->dump_connect();
  823.         $eigenschaften = array();
  824.         // Dicke
  825.         $dicke = $storage["thickness"];
  826.         $eigenschaften[$this->thickness_id]=$dicke;
  827.         
  828.         // Fehler j/n
  829.         // $fehler = "Nein";
  830.         // if( $storage["fault_id"] != 0 && !empty($storage["fault_id"]) && $storage["fault_corner"] != 0 && !empty($storage["fault_corner"])){
  831.         //     $fehler = "Ja";
  832.         // }
  833.         // $eigenschaften[$this->fault_exists_id]=$fehler;
  834.         
  835.         // Oberfläche
  836.         $oberflaeche = $storage["surface_name"];
  837.         $eigenschaften[$this->surface_id]=$oberflaeche;
  839.          // Artikelgruppe
  840.          if(isset($storage["articlegroup_name"]) && !empty($storage["articlegroup_name"])){
  841.             $artikelgruppe = $storage["articlegroup_name"];
  842.             $eigenschaften[$this->articlegroup_id]=$artikelgruppe;
  843.         }
  845.         // // Steinnummer
  846.         // $steinnummer = $storage["stone_number"];
  847.         // $eigenschaften[$this->stonenumber_id]=$steinnummer;
  849.         // Blocknummer
  850.         // if(isset($storage["block_number"]) && !empty($storage["block_number"])){
  851.         //     $blocknummer = $storage["block_number"];
  852.         //     $eigenschaften[$this->blocknumber_id]=$blocknummer;
  853.         // }
  854.         if(isset($storage["block_advice"]) && !empty($storage["block_advice"])){
  855.             $blocknummer = $storage["block_advice"];
  856.             //Zerlegt alle nummern in mat+oberflächenzahl+blocknummer und kürzt zeichen nach leerzeichen automatisch raus
  857.             // problemfall: keine oberflächennummer zwischen blocknummer und materialid
  858.             $blocknummer_zerlegt=array();
  859.             preg_match('/([a-zA-Z]+)([0-9,]+)([a-zA-Z0-9,]+)/', $storage["block_advice"], $blocknummer_zerlegt);
  860.             if(strlen(end($blocknummer_zerlegt))>1){
  861.                 $blocknummer = end($blocknummer_zerlegt);
  862.                 $eigenschaften[$this->blocknumber_id]=$blocknummer;
  863.             }
  864.         }
  866.         // // Materialfarbe
  867.         if(isset($storage["materialgroup_id"]) && !empty($storage["materialgroup_id"]) &&
  868.         isset($storage["material_id"]) && !empty($storage["material_id"])){
  869.             $sql = "SELECT *
  870.                     FROM materialcolors
  871.                     INNER JOIN `keys` ON (materialcolors.color_id = key_number)
  872.                     WHERE color_id != -1
  873.                     AND key_kind = 88
  874.                     AND mgr_id = '".$storage["materialgroup_id"]."'
  875.                     AND mar_id = '".$storage["material_id"]."'
  876.                 ;";
  877.             $stmt = $conn_akn->prepare($sql);
  878.             $stmt->execute();
  879.             $colors = $stmt->fetchAll();
  880.             // return $this->render('update/index.html.twig', ["return" => ['success' => $colors ]]);
  881.             if(isset($colors) && is_array($colors) && count($colors) > 0){
  882.                 $color = explode('#',$colors[0]["key_text"]);
  883.                 $colors[0]["key_text"] = trim($color[0]);
  884.                 $eigenschaften[$this->color_id] = $colors[0]["key_text"];
  885.             }
  886.         }
  888.         $attribute=array();
  889.         foreach($eigenschaften as $key => $eigenschaft){
  890.             $sql = "SELECT 
  891.                     *
  892.                     , LOWER(HEX(id)) as id
  893.                     , HEX(language_id) as language_id
  894.                     , HEX(property_group_id) as property_group_id
  895.                 FROM property_group_option
  896.                 INNER JOIN property_group_option_translation  ON (property_group_option.id = property_group_option_translation.property_group_option_id)
  897.                 -- WHERE LOWER(HEX(property_group_id)) = 'eadafb18e0c34aeb894dafddf1e107a0'
  898.                 WHERE `name` = '".$eigenschaft."'
  899.                 AND LOWER(HEX(property_group_id)) = '".$key."'
  900.                 ;";
  901.             $stmt = $conn_shopware->prepare($sql);
  902.             $stmt->execute();
  903.             $search_property = $stmt->fetchAll();
  904.             // var_dump($search_property);exit;
  905.             // return $this->render('update/index.html.twig', ["return" => ['dearch' => $search_property ]]);
  906.             
  907.             // array("optionId" => "d6c37799803148fe977e42d2fe7cc875",),
  908.             // array("optionId" => "340073aa01544985ab0d1f056c1f8422",)
  910.             
  911.             if(isset($search_property) && is_array($search_property) && count($search_property) > 0) {
  912.                 array_push($attribute,array('optionId'=>$search_property[0]["id"]));
  913.             }else{
  914.                 // Attribut nicht in shopware und muss hinzugefügt werden
  915.                 // var_dump($eigenschaften);
  916.                 // var_dump("post ".$eigenschaft." ".$key);
  917.                 $post_product = $this->client->request('POST', '/api/property-group-option', array(
  918.                     'allow_redirects'=>array('strict'=>true),
  919.                     'headers' => $this->header,
  920.                     'body' => json_encode([
  921.                         'groupId' => $key,
  922.                         'name' => $eigenschaft
  923.                     ])
  924.                 ));
  925.                 $optionId = $post_product->getHeaders(); 
  926.                 // var_dump($optionId);exit;
  927.                 $optionId =  str_replace($this->base_url.'api/property-group-option/','',$optionId["Location"]); // ID des erstellten produktes
  928.                 array_push($attribute,array('optionId'=>$optionId[0]));
  929.             }
  931.         }
  932.         // var_dump($attribute);exit;
  933.         return $attribute;
  934.     }
  939.     /**
  940.      * test
  941.      * 
  942.      * @Route("/teste", name="teste")
  943.      * 
  944.      * @return mixed
  945.      */
  946.     public function teste()
  947.     {
  948.         $this->client_connect();
  949.         $conn_shopware = $this->shop_connect();
  950.         // $conn_konfigurator = $this->konfig_connect();
  951.         // try {
  952.         //     $post_customer = $this->client->request('POST', '/api/sales-channel-payment-method', array(
  953.         //         // 'debug' => true,
  954.         //         'allow_redirects'=>array('strict'=>true),
  955.         //         'headers' => $this->header,
  956.         //         'body' => json_encode([
  957.         //             'salesChannelId' => "f8c383f75225488a8f6b89b7d64d6cf2",
  958.         //             'paymentMethodId' => "54008360b783458a9c82db74b1ca3f07"
  959.         //         ])
  960.         //     ));
  961.         //     $location = array_pop( $post_customer->getHeaders()['Location'] );
  962.         //     $payment_method_id = basename($location);
  963.         // } catch (RequestException $e) {
  964.         //     echo Psr7\str($e->getRequest());
  965.         //     if ($e->hasResponse()) {
  966.         //         echo Psr7\str($e->getResponse());
  967.         //     }
  968.         //     exit;
  969.         // }
  971.         $payment_method_id = "54008360b783458a9c82db74b1ca3f07";
  972.     //     $sql = "INSERT INTO sales_channel_payment_method (sales_channel_id, payment_method_id)
  973.     //     VALUES (UNHEX('f8c383f75225488a8f6b89b7d64d6cf2'), UNHEX('".$payment_method_id."'))
  974.     // ;";
  975.     // $stmt = $conn_shopware->prepare($sql);
  976.     // $stmt->execute();
  979.         dd($payment_method_id);
  981.         $ProductFlat = new \App\Controller\shopware\ProductFlat();
  982.         $ProductFlat->setActive(true);
  983.         $ProductFlat->setCategories(array(array("id" =>  $categoryId)));
  984.         $ProductFlat->setDeliveryTimeId($this->deliverytime_id);
  985.         $ProductFlat->setDescription($article["article_name_1"]);
  986.         $ProductFlat->setIsCloseout(true);
  987.         $ProductFlat->setMarkAsTopseller(false);
  988.         $ProductFlat->setName($material_name);
  989.         $ProductFlat->setPrice(array(array(
  990.             "currencyId" => $this->currency_id,
  991.             "net" =>  0,
  992.             "gross" => 0,
  993.             "linked" => false
  994.         )));
  995.         $ProductFlat->setProductNumber($article["article_id"]);
  996.         $ProductFlat->setPurchaseUnit(1);
  997.         // $ProductFlat->setRestockTime();
  998.         // $ProductFlat->setUnitId("ff7d882bd1da432bb4e05e21eda66114");
  999.         $ProductFlat->setTaxId($this->tax_id);
  1000.         $ProductFlat->setVisibilities(array(array(
  1001.             "salesChannelId" => $this->saleschannel_id,
  1002.             "visibility" => 30
  1003.         )));
  1004.         $ProductFlat->setCustomFields(array("custom_article_surface" => "testsurface",));
  1005.         $ProductFlat->setHeight($article["thickness"]);
  1006.         $ProductFlat->setLength($article["length"]);
  1007.         $ProductFlat->setWidth($article["width"]);
  1008.         $ProductFlat->setStock(0);
  1009.         // $ProductFlat->setMaxPurchase(0);
  1010.         if( isset($storage_option_list) && is_array($storage_option_list) && count($storage_option_list)>0){
  1011.             $ProductFlat->setConfiguratorGroupConfig(array(array(
  1012.                 "id" => "5eb85799b5914547adf6348ff2213d5e",
  1013.                 "representation" => "box",
  1014.                 "expressionForListings" => false
  1015.             )));
  1016.             $ProductFlat->setConfiguratorSettings(
  1017.                 // array("optionId" => "d6c37799803148fe977e42d2fe7cc875",),
  1018.                 // array("optionId" => "340073aa01544985ab0d1f056c1f8422",)
  1019.                 $storage_option_list
  1020.             );
  1021.         }
  1022.         // return $this->render('update/index.html.twig', ["return" => ['nummern' => $ProductFlat]]);
  1024.         $poststring = 'api/product';
  1025.         // var_dump("post ".$article["article_id"]);
  1026.         // var_dump("<pre>");
  1027.         if( isset($material_name) && !empty($material_name)){
  1028.             try {
  1029.                 $post_product = $this->client->request('PATCH', '/'.$poststring, array(
  1030.                     // "debug" => true,
  1031.                     'allow_redirects'=>array('strict'=>true),
  1032.                     "headers" => $this->header,
  1033.                     "body" => $ProductFlat
  1034.                 ));
  1035.             } catch (RequestException $e) {
  1036.                 // echo Psr7\str($e->getRequest());
  1037.                 // if ($e->hasResponse()) {
  1038.                 //     echo Psr7\str($e->getResponse());
  1039.                 // }
  1040.                 if ($e->hasResponse()) {
  1041.                     $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
  1042.                 }else{
  1043.                     $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
  1044.                 }
  1045.                 $response->headers->set('Content-Type', 'application/json');
  1046.                 return $response;
  1047.                 exit;
  1048.             }
  1049.             $post_product_id = $post_product->getHeaders(); 
  1050.             $post_product_id =  str_replace($this->base_url.$poststring.'/','',$post_product_id["Location"]); // ID des erstellten produktes
  1052.          }
  1055.         return $this->render('update/index.html.twig', ["return" => ['ID' => $groupId ]]);
  1057.         // $ProductFlat = new \App\Controller\shopware\ProductFlat();
  1058.         // $ProductFlat->setName("testX-Varianten");
  1059.         // $ProductFlat->setProductNumber("test_post");
  1060.         // $ProductFlat->setStock(10);
  1061.         // $ProductFlat->setPrice(array(array(
  1062.         //     "currencyId" => "b7d2554b0ce847cd82f3ac9bd1c0dfca",
  1063.         //     "gross" => 15,
  1064.         //     "net" =>  10,
  1065.         //     "linked" => false
  1066.         // )));
  1067.         // $ProductFlat->setTaxId("c5ddfa75e22b4889b6f628e1df0b347f");
  1068.         // $ProductFlat->setConfiguratorGroupConfig(array(array(
  1069.         //         "id" => "5eb85799b5914547adf6348ff2213d5e",
  1070.         //         "representation" => "box",
  1071.         //         "expressionForListings" => false
  1072.         // )));
  1073.         // $ProductFlat->setConfiguratorSettings(array(
  1074.         //     array("optionId" => "d6c37799803148fe977e42d2fe7cc875",),
  1075.         //     array("optionId" => "340073aa01544985ab0d1f056c1f8422",)));
  1076.                         
  1077.         // try {
  1078.         //     $post_product = $this->client->request('POST', '/api/product', array(
  1079.         //         "headers" => $this->header,
  1080.         //         "body" => $ProductFlat
  1081.         //         // "form_params" => array($variant_values)
  1082.         //     ));
  1083.         // } catch (RequestException $e) {
  1084.         //     echo "<pre>";
  1085.         //     echo Psr7\str($e->getRequest());
  1086.         //     if ($e->hasResponse()) {
  1087.         //         echo Psr7\str($e->getResponse());
  1088.         //     }
  1089.         //     exit;
  1090.         // }
  1091.         // $poststring = 'api/product';
  1092.         // $post_product_id = $post_product->getHeaders(); 
  1093.         // $post_product_id =  str_replace($this->base_url.$poststring.'/','',$post_product_id["Location"]); // ID des erstellten produktes
  1094.                    
  1095.         // $ProductFlat = new \App\Controller\shopware\ProductFlat();
  1096.         // $ProductFlat->setName("testX-Varianten 1");
  1097.         // $ProductFlat->setParentId($post_product_id[0]);
  1098.         // $ProductFlat->setProductNumber("test_post_1");
  1099.         // $ProductFlat->setStock(10);
  1100.         // $ProductFlat->setOptions(array(array("id"=> "d6c37799803148fe977e42d2fe7cc875")));
  1101.             
  1102.         // try {
  1103.         //     $post_product = $this->client->request('POST', '/api/product', array(
  1104.         //         "headers" => $this->header,
  1105.         //         "body" => $ProductFlat
  1106.         //     ));
  1107.         // } catch (RequestException $e) {
  1108.         //     echo "<pre>";
  1109.         //     echo Psr7\str($e->getRequest());
  1110.         //     if ($e->hasResponse()) {
  1111.         //         echo Psr7\str($e->getResponse());
  1112.         //     }
  1113.         //     exit;
  1114.         // }
  1115.                               
  1116.         // $ProductFlat = new \App\Controller\shopware\ProductFlat();
  1117.         // // $ProductFlat->setActive(true);
  1118.         // $ProductFlat->setName("testX-Varianten 2");
  1119.         // $ProductFlat->setParentId($post_product_id[0]);
  1120.         // $ProductFlat->setProductNumber("test_post_2");
  1121.         // $ProductFlat->setStock(10);
  1122.         // $ProductFlat->setOptions(array(
  1123.         //     array("id"=> "340073aa01544985ab0d1f056c1f8422") //ID der Varianteneigenschaft
  1124.         // ));
  1125.             
  1126.         // try {
  1127.         //     $post_product = $this->client->request('POST', '/api/product', array(
  1128.         //         "headers" => $this->header,
  1129.         //         "body" => $ProductFlat
  1130.         //     ));
  1131.         // } catch (RequestException $e) {
  1132.         //     echo "<pre>";
  1133.         //     echo Psr7\str($e->getRequest());
  1134.         //     if ($e->hasResponse()) {
  1135.         //         echo Psr7\str($e->getResponse());
  1136.         //     }
  1137.         //     exit;
  1138.         // }
  1139.              
  1140.         
  1141.         $name= "9702_Nero Ass Classico geschliffen";
  1142.         // var_dump("<pre>");
  1143.         try {
  1144.             $search = $this->client->request('GET', '/api/media?filter[fileName]='.$name.'', array( 
  1145.             "headers" => $this->header,
  1146.             ));
  1147.         } catch (RequestException $e) {
  1148.             // echo Psr7\str($e->getRequest());
  1149.             // if ($e->hasResponse()) {
  1150.             //     echo Psr7\str($e->getResponse());
  1151.             // }
  1152.             if ($e->hasResponse()) {
  1153.                 $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
  1154.             }else{
  1155.                 $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
  1156.             }
  1157.             $response->headers->set('Content-Type', 'application/json');
  1158.             return $response;
  1159.             exit;
  1160.         }
  1161.         $search_picture = json_decode($search->getBody()->getContents(),true);
  1163.         $sql = "SELECT 
  1164.         *
  1165.         FROM materials 
  1166.         WHERE material_id = 'ERAM'
  1167.         AND materialgroup_id = 'GE'
  1168.         ;";
  1169.         // LIMIT ".$step_count." 
  1170.         // OFFSET ".(($page - 1) * $step_count)."
  1171.         $stmt = $conn_shopware->prepare($sql);
  1172.         $stmt->execute();
  1173.         $material = $stmt->fetchAll();
  1175.         return $this->render('update/index.html.twig', ["return" => ['nummern' => $material ]]);
  1177.         $sql = "SELECT 
  1178.                 *
  1179.                 ,articles.material_id as material_id
  1180.             FROM articles
  1181.             LEFT JOIN materials ON (materials.material_id = articles.material_id)
  1182.             LEFT JOIN materialgroups ON (materialgroups.materialgroup_id = articles.materialgroup_id)
  1183.             LEFT JOIN surfaces ON (surfaces.surface_id = articles.surface_id AND surfaces.materialgroup_id = articles.materialgroup_id)
  1184.             WHERE article_id = '00632'
  1185.             ORDER BY articles.id ASC
  1186.             ;";
  1187.         $stmt = $conn_shopware->prepare($sql);
  1188.         $stmt->execute();
  1189.         $articles = $stmt->fetchAll();
  1190.         // return $this->render('update/index.html.twig', ["return" => ['nummern' => $artikelnummern, 'zeit' => $articles]]);
  1192.         $sql = "SELECT 
  1193.                     *
  1194.             FROM storages 
  1195.             LEFT JOIN surfaces ON (surfaces.surface_id = storages.surface_id  AND storages.materialgroup_id = surfaces.materialgroup_id)
  1196.             LEFT JOIN articlegroups ON (articlegroups.articlegroup_id = storages.articlegroup_id)
  1197.             LEFT JOIN storagefaults ON storagefaults.fault_id = storages.fault_id 
  1198.             WHERE article_id = '".$articles[0]["article_id"]."'
  1199.             ORDER BY storages.id ASC
  1200.             ;";
  1201.         $stmt = $conn_shopware->prepare($sql);
  1202.         $stmt->execute();
  1203.         $storages = $stmt->fetchAll();
  1206.         return $this->render('update/index.html.twig', ["return" => ['shop' => $articles, 'storages' => $storages ]]);
  1207.         return $this->render('update/index.html.twig', ["return" => ['shop' => $post_product_id ]]);
  1211.         return $this->render('update/index.html.twig', ["return" => ['shop' => "true" ]]);
  1212.     }
  1223.     /**
  1224.      * Importiert Zulauf Produkte
  1225.      * 
  1226.      * @Route("/import/products_zulauf", name="import_products_zulauf")
  1227.      * 
  1228.      * @return mixed
  1229.      */
  1230.     public function import_products_zulauf() {
  1231.         $this->client_connect();
  1232.         $conn_shopware = $this->shop_connect();
  1233.         $conn_akn = $this->dump_connect();
  1234.         $step_count = 10;
  1235.         
  1236.         $sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,UpdateDatetime)
  1237.                 VALUES(?,?,?)";
  1238.         $stmtt = $conn_shopware->prepare($sql);
  1239.         $stmtt->execute(array( 
  1240.             "import/products_zulauf",
  1241.             "Start",
  1242.             date('Y-m-d H:i:s')
  1243.         ));
  1245.         # holt alle ZULAUF Artikel ab die eine passende Materialaufschlüsselung haben
  1246.         $sql = "SELECT *
  1247.             , articles.material_id as material_id
  1248.             , articles.materialgroup_id as materialgroup_id
  1249.             , storages.id as storage_id
  1250.             -- , CONCAT(storages.article_id, '_' ,storages.id) AS product_number
  1251.             -- , CONCAT('zulauf', '_', storages.material_id, '_', storages.materialgroup_id, '_', storages.id) AS product_number
  1252.             , storages.stone_number AS product_number
  1253.             , CONCAT('Zulauf_', storages.material_id, '_', storages.materialgroup_id) AS product_number_parent
  1254.             -- , COALESCE(NULLIF(storages.picture, ''), NULLIF(materials.picture, ''), 'default.jpg') AS picture   -- select picture
  1255.             , storages.picture AS storages_picture
  1256.             , materials.picture AS materials_picture
  1257.             , COALESCE(NULLIF(materials.picture, ''), 'default.jpg') AS picture_parent   -- select picture
  1258.             FROM articles
  1259.             LEFT JOIN materials ON (articles.material_id = materials.material_id AND articles.materialgroup_id = materials.materialgroup_id )
  1260.             -- LEFT JOIN articlegroups ON (articlegroups.articlegroup_id = articles.article_id)
  1261.             LEFT JOIN articlegroups ON (articles.articlegroup_id = articlegroups.articlegroup_id)
  1262.             LEFT JOIN materialgroups ON (materialgroups.materialgroup_id = articles.materialgroup_id)
  1263.             LEFT JOIN surfaces ON (surfaces.surface_id = articles.surface_id AND surfaces.materialgroup_id = articles.materialgroup_id)
  1264.             INNER JOIN storages ON (articles.article_id = storages.article_id)
  1265.             LEFT JOIN storagefaults ON (storages.fault_id = storagefaults.fault_id)
  1266.             WHERE storages.stone_number IS NOT NULL
  1267.             AND storages.storage = 'ZULAUF'
  1268.             AND storages.upload_done IS NULL
  1269.             AND material_name IS NOT NULL
  1270.             -- AND articles.material_id = 'BCCD'
  1271.             ORDER BY articles.id ASC
  1272.             LIMIT ".$step_count."
  1273.         ;";
  1275.         $stmt = $conn_akn->prepare($sql);
  1276.         $stmt->execute();
  1277.         $articles = $stmt->fetchAll();
  1278.         // dd($articles);
  1280.         // echo "<pre>"; var_dump($articles); exit;
  1282.         # NEXT STEP
  1283.         if(count($articles) >= $step_count) { 
  1284.             $nextPage = true;
  1285.         } else {
  1286.             $nextPage = false;
  1287.         }
  1289.         # PICTURE
  1290.         // dd($articles[0]['picture']);
  1291.         // $this->getParameter('kernel.project_dir')."/public/produktbilder"
  1294.         // $materialid = $articles[0]["material_id"];
  1295.         // $materialname = $articles[0]["material_name"];
  1296.         // $materialgruppenname = $articles[0]["materialgroup_name"];
  1297.         // $materialgruppenid = $articles[0]["materialgroup_id"];
  1298.         // $oberflaeche = $articles[0]["surface_name"];
  1299.         
  1303.         foreach($articles as $article) {
  1304.             $productParentId = "";
  1305.             $productId = "";
  1306.             // $productNumber = $article['article_id']
  1307.             // var_dump($productNumber);
  1310.             # REMOVE SUFFIX FROM PICTURE NAME
  1311.             // if(strpos($article['picture'], '-##-')) {
  1312.             //     $article['picture'] = substr($article['picture'], 0, -5);
  1313.             // }
  1314.             if(strpos($article['storages_picture'], '-##-')) {
  1315.                 $article['storages_picture'] = substr($article['picture'], 0, -5);
  1316.             }
  1317.             if(strpos($article['materials_picture'], '-##-')) {
  1318.                 $article['storages_picture'] = substr($article['picture'], 0, -5);
  1319.             }
  1320.             # END EDIT PICTURE NAME
  1323.             $storage_option_list = $this->get_variant_ids($article); //übergibt storage, returned formatierten array für variantenerstellung
  1326.             # CHECK IF PARENT PRODUCT ALREADY EXISTS ###################################
  1327.             // $sql = "SELECT *, LOWER(HEX(id)) as id
  1328.             $sql = "SELECT LOWER(HEX(product.id)) as id
  1329.                 FROM product_translation
  1330.                 INNER JOIN product ON (product.id = product_translation.product_id)
  1331.                 INNER JOIN product_category ON ( product_translation.product_id = product_category.product_id)
  1332.                 -- INNER JOIN category ON ( product_category.category_id = category.category_id)
  1333.                 -- INNER JOIN category_translation ON ( product_category.category_id = category_translation.category_id)
  1334.                 WHERE LOWER(HEX(product_category.category_id)) = '".$this->zulauf_id."'
  1335.                 -- AND JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_material_id') = '".$article['material_id']."'
  1336.                 -- AND JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_materialgroup_id')  = '".$article['materialgroup_id']."'
  1337.                 AND product.product_number = '". $article['product_number_parent'] . "'
  1338.                 AND parent_id IS NULL
  1339.                 LIMIT 1
  1340.             ;";
  1341.             $stmt = $conn_shopware->prepare($sql);
  1342.             $stmt->execute();
  1343.             $search_product = $stmt->fetchAll();
  1345.             # TODO: IF PRODUCT ALREADY EXISTS
  1347.             if(isset($search_product[0]["id"]) && !empty($search_product[0]["id"]) && count($search_product) > 0) {
  1348.                 $productParentId = $search_product[0]["id"];
  1349.             }
  1351.             # SET DATA FOR PRODUCT ###################################
  1352.             $ProductFlat = new \App\Controller\shopware\ProductFlat();
  1353.             $ProductFlat->setActive(true);
  1354.             // $ProductFlat->setCategories(array(array("id" => $this->getCategoryId($materialgruppenname, $materialgruppenid))));
  1355.             $ProductFlat->setCategories(array(array("id" => $this->zulauf_id)));
  1356.             $ProductFlat->setDeliveryTimeId($this->deliverytime_id);
  1357.             $ProductFlat->setDescription($article["article_name_1"]);
  1358.             $ProductFlat->setIsCloseout(false); // ABVERKAUF - Wichtig, da Anzahl = 0
  1359.             $ProductFlat->setMarkAsTopseller(false);
  1360.             // $ProductFlat->setName('Zulauf '.$article['product_number_parent'] .' ('.  date('Y-m-d H:m:s') .')');
  1361.             // $ProductFlat->setName('Zulauf '.$article['product_number_parent']);
  1362.             // $ProductFlat->setName('ZULAUF: ' . $article['product_number_parent']);
  1363.             $ProductFlat->setName('Zulauf: ' . $article['material_name']);
  1364.             
  1365.             // $ProductFlat->setName('Zulauf '.$materialname);
  1366.             // $ProductFlat->setName('toller lustiger testname');
  1367.             $ProductFlat->setPrice(array(array(
  1368.                 "currencyId" => $this->currency_id,
  1369.                 "net" =>  0,
  1370.                 "gross" => 0,
  1371.                 "linked" => false
  1372.             )));
  1374.             // $ProductFlat->setProductNumber($article['product_number']);
  1375.             // $ProductFlat->setProductNumber($article["materialgroup_id"]);
  1376.             $ProductFlat->setProductNumber($article['product_number_parent']);
  1378.             $ProductFlat->setPurchaseUnit(1);
  1379.             // $ProductFlat->setRestockTime();
  1380.             // $ProductFlat->setUnitId("ff7d882bd1da432bb4e05e21eda66114");
  1381.             $ProductFlat->setTaxId($this->tax_id);
  1384.             // $ProductFlat->setCustomFields();
  1385.             $custom_fields = array();
  1386.             // $custom_fields["custom_article_surface"] = $article["surface_name"];
  1387.             // $custom_fields["custom_article_search_word"] = $article["search_word"]; 
  1388.             // $custom_fields["custom_article_name_1"] = $article["article_name_1"];
  1389.             // $custom_fields["custom_article_name_2"] = $article["article_name_2"];
  1390.             // $custom_fields["custom_article_material_id"] = $article["material_id"];
  1391.             // $custom_fields["custom_article_materialgroup_id"] = $article["materialgroup_id"];
  1392.             // // $custom_fields["custom_article_material_name"] = $materialname;
  1393.             // $custom_fields["custom_article_material_name"] = $article['material_name'];
  1394.             // $custom_fields["custom_article_materialgroup_name"] = $article["materialgroup_name"];
  1395.             // $custom_fields["custom_article_picture"] = $article["picture"];
  1396.             // $custom_fields["custom_article_material_id"] = $article["material_id"];
  1397.             // $custom_fields["custom_article_materialgroup_id"] = $article["materialgroup_id"];
  1399.             $search = array("{", "}", "(", ")", "/", "\\", "@", ":");
  1400.             $replace = array("", "", "", "", "", "", "", "");
  1402.             $custom_fields["custom_article_surface"]            = str_replace($search, $replace, $article["surface_name"]);
  1403.             $custom_fields["custom_article_search_word"]        = str_replace($search, $replace, $article["search_word"]);
  1404.             $custom_fields["custom_article_name_1"]             = str_replace($search, $replace, $article["article_name_1"]);
  1405.             $custom_fields["custom_article_name_2"]             = str_replace($search, $replace, $article["article_name_2"]);
  1406.             $custom_fields["custom_article_material_id"]        = str_replace($search, $replace, $article["material_id"]);
  1407.             $custom_fields["custom_article_materialgroup_id"]   = str_replace($search, $replace, $article["materialgroup_id"]);
  1408.             $custom_fields["custom_article_material_name"]      = str_replace($search, $replace, $article['material_name']);
  1409.             $custom_fields["custom_article_materialgroup_name"] = str_replace($search, $replace, $article["materialgroup_name"]);
  1410.             $custom_fields["custom_article_picture"]            = str_replace($search, $replace, $article["picture"]);
  1411.             $custom_fields["custom_article_id"]                 = str_replace($search, $replace, $article["article_id"]);
  1412.             $custom_fields["custom_article_stone_number"]       = str_replace($search, $replace, $article["stone_number"]);
  1413.             $custom_fields["custom_article_block_number"]       = str_replace($search, $replace, $article["block_advice"]);
  1414.             $custom_fields["custom_article_search_word"]        = str_replace($search, $replace, $article["search_word"]);
  1415.             $custom_fields["custom_article_advice"]             = str_replace($search, $replace, $article["advice"]);
  1416.             $custom_fields["custom_article_storage"]            = str_replace($search, $replace, $article["storage"]);
  1417.             $custom_fields["custom_article_amount_2"]           = str_replace($search, $replace, $article["amount_2"]);
  1418.             $custom_fields["custom_article_articlegroup_name"]  = str_replace($search, $replace, $article["articlegroup_name"]);
  1419.             $custom_fields["custom_article_fault_id"]           = str_replace($search, $replace, $article["fault_name"]);
  1420.             $custom_fields["custom_article_fault_corner"]       = str_replace($search, $replace, $article["fault_corner"]);
  1421.             $custom_fields["custom_article_additional_advice"]  = str_replace($search, $replace, $article["additional_advice"]);
  1422.             
  1425.             $ProductFlat->setCustomFields(
  1426.                 $custom_fields
  1427.             );
  1429.             $ProductFlat->setHeight($article["thickness"]);
  1430.             $ProductFlat->setLength($article["length"]);
  1431.             $ProductFlat->setWidth($article["width"]);
  1432.             $ProductFlat->setStock(0);
  1433.             $ProductFlat->setMaxPurchase(0);
  1435.             $ProductFlat->setVisibilities(array(array(
  1436.                 "salesChannelId" => $this->saleschannel_id,
  1437.                 "visibility" => 30
  1438.             )));
  1442.             # UPDATE OR INSERT ###################################
  1443.             $poststring = 'api/product';
  1444.             
  1445.             
  1446.             $storage_option_list = $this->get_variant_ids($article); //übergibt storage, returned formatierten array für variantenerstellung
  1447.             
  1449.             # PARENT ###################################
  1450.             if( isset($article['material_name']) && trim($article['material_name']) != '') {
  1452.                 if($productParentId == '') {
  1453.                     # INSERT PARENT ARTICLE ##########################
  1455.                     if( isset($storage_option_list) && is_array($storage_option_list) && count($storage_option_list) > 0) {
  1456.                         # TODO: vorbereitung der Varianten
  1457.                         // $ProductFlat->setConfiguratorGroupConfig(array(array(
  1458.                         //     // "id" => "5eb85799b5914547adf6348ff2213d5e",
  1459.                         //     // "representation" => "box",
  1460.                         //     // "expressionForListings" => false
  1461.                         // )));
  1463.                         $ProductFlat->setConfiguratorGroupConfig(array(
  1464.                             array(
  1465.                                 "id" => $this->thickness_id,
  1466.                                 "representation" => "box",
  1467.                                 "expressionForListings" => false
  1468.                             ),
  1469.                             array(
  1470.                                 "id" => $this->articlegroup_id,
  1471.                                 "representation" => "box",
  1472.                                 "expressionForListings" => false
  1473.                             ),
  1474.                             array(
  1475.                                 "id" => $this->surface_id,
  1476.                                 "representation" => "box",
  1477.                                 "expressionForListings" => false
  1478.                             ),
  1479.                             array(
  1480.                                 "id" => $this->length_id,
  1481.                                 "representation" => "box",
  1482.                                 "expressionForListings" => false
  1483.                             ),
  1484.                             array(
  1485.                                 "id" => $this->width_id,
  1486.                                 "representation" => "box",
  1487.                                 "expressionForListings" => false
  1488.                             ),
  1489.                             array(
  1490.                                 "id" => $this->amount_id,
  1491.                                 "representation" => "box",
  1492.                                 "expressionForListings" => false
  1493.                             ),
  1494.                             array(
  1495.                                 "id" => $this->surfacearea_id,
  1496.                                 "representation" => "box",
  1497.                                 "expressionForListings" => false
  1498.                             ),
  1499.                             array(
  1500.                                 "id" => $this->meter_id,
  1501.                                 "representation" => "box",
  1502.                                 "expressionForListings" => false
  1503.                             ),
  1504.                             array(
  1505.                                 "id" => $this->sa_id,
  1506.                                 "representation" => "box",
  1507.                                 "expressionForListings" => false
  1508.                             ),
  1509.                             array(
  1510.                             "id" => $this->blocknumber_id,
  1511.                                 "representation" => "box",
  1512.                                 "expressionForListings" => false
  1513.                             ),
  1514.                             array(
  1515.                                 "id" => $this->material_id,
  1516.                                 "representation" => "box",
  1517.                                 "expressionForListings" => false
  1518.                             ),
  1519.                             array(
  1520.                                 "id" => $this->materialgroup_id,
  1521.                                 "representation" => "box",
  1522.                                 "expressionForListings" => false
  1523.                             ),
  1524.                             array(
  1525.                                 "id" => $this->fault_exists_id,
  1526.                                 "representation" => "box",
  1527.                                 "expressionForListings" => false
  1528.                             ),
  1529.                             array(
  1530.                                 "id" => $this->squaremeterprice_id,
  1531.                                 "representation" => "box",
  1532.                                 "expressionForListings" => false
  1533.                             ),
  1534.                             array(
  1535.                                 "id" => $this->stonenumber_id,
  1536.                                 "representation" => "box",
  1537.                                 "expressionForListings" => false
  1538.                             )
  1539.                         ));
  1543.                         # SET CONFIGURATOR SETTINGS
  1544.                         // dd($storage_option_list);
  1545.                         // $ProductFlat->setConfiguratorSettings($storage_option_list);
  1547.                         
  1548.                         // $sol_option=array();
  1549.                         // foreach($storage_option_list as $key => $sol) {
  1550.                         //     // $sol_option[$key]["productId"] = "";
  1551.                         //     $sol_option[$key]["id"] = $sol["optionId"];
  1552.                         // }
  1553.                         // $ProductFlat->setConfiguratorSettings($sol_option);
  1554.                         
  1555.                         try {
  1556.                             $post_product = $this->client->request('POST', '/'.$poststring, array(
  1557.                                 //    "debug" => true,
  1558.                                 'allow_redirects'=>array('strict'=>true),
  1559.                                 "headers" => $this->header,
  1560.                                 "body" => $ProductFlat
  1561.                             ));
  1562.                         } catch (RequestException $e) {
  1563.                             // echo Psr7\str($e->getRequest());
  1564.                             // if ($e->hasResponse()) {
  1565.                             //     echo Psr7\str($e->getResponse());
  1566.                             // }
  1567.                             if ($e->hasResponse()) {
  1568.                                 $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
  1569.                             }else{
  1570.                                 $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
  1571.                             }
  1572.                             $response->headers->set('Content-Type', 'application/json');
  1573.                             return $response;
  1574.                             exit;
  1575.                         }
  1577.                         # PARENT ID
  1578.                         $productParentId = $post_product->getHeaders(); 
  1579.                         $productParentId =  str_replace($this->base_url.$poststring.'/','',$productParentId["Location"]); // ID des erstellten produktes
  1580.                         $productParentId = $productParentId[0];
  1581.                     }
  1582.                 }
  1586.                 # INSERT VARIANT ##########################
  1587.                 $ProductFlat->setParentId($productParentId);
  1588.                 $ProductFlat->setProductNumber($article["product_number"]);
  1590.                 $ProductFlat->setConfiguratorGroupConfig(null);
  1591.                 $ProductFlat->setConfiguratorSettings(null);
  1593.                 // $ProductFlat->setName('Zulauf '.$article['product_number_parent']);
  1594.                 // $ProductFlat->setName($article['product_number_parent']);
  1595.                 $ProductFlat->setName('Zulauf: ' . $article['material_name']);
  1596.                 
  1597.                 # PICTURES AND IMAGES ##########################
  1598.                 
  1599.                 // # storages_picture
  1600.                 // $sql = "SELECT LOWER(HEX(id)) AS id
  1601.                 //     FROM media
  1602.                 //     WHERE file_name = '".pathinfo($article['storages_picture'], PATHINFO_FILENAME)."'
  1603.                 //     LIMIT 1
  1604.                 // ;";
  1605.                 // $stmt = $conn_shopware->prepare($sql);
  1606.                 // $stmt->execute();
  1607.                 // $pictures = $stmt->fetchAll();
  1609.                 // if(!(isset($pictures) && is_array($pictures) && count($pictures) > 0)) {
  1611.                 //     # MATERIALS_PICTURE
  1612.                 //     $sql = "SELECT LOWER(HEX(id)) AS id
  1613.                 //         FROM media
  1614.                 //         WHERE file_name = '".pathinfo($article['materials_picture'], PATHINFO_FILENAME)."'
  1615.                 //         LIMIT 1
  1616.                 //     ;";
  1617.                 //     $stmt = $conn_shopware->prepare($sql);
  1618.                 //     $stmt->execute();
  1619.                 //     $pictures = $stmt->fetchAll();
  1621.                 //     if(!(isset($pictures) && is_array($pictures) && count($pictures) > 0)) {
  1623.                 //         # DEFAULT_IMAGE
  1624.                 //         $sql = "SELECT LOWER(HEX(id)) AS id
  1625.                 //             FROM media
  1626.                 //             WHERE file_name = '".pathinfo($this->default_image_filename, PATHINFO_FILENAME)."'
  1627.                 //             LIMIT 1
  1628.                 //         ;";
  1629.                 //         $stmt = $conn_shopware->prepare($sql);
  1630.                 //         $stmt->execute();
  1631.                 //         $pictures = $stmt->fetchAll();
  1632.                 //     }
  1633.                 // }
  1635.                 // // $sql = "SELECT LOWER(HEX(id)) AS id
  1636.                 // //     FROM media
  1637.                 // //     WHERE file_name = '".pathinfo($article['picture'], PATHINFO_FILENAME)."'
  1638.                 // //     LIMIT 1
  1639.                 // // ;";
  1640.                 // // $stmt = $conn_shopware->prepare($sql);
  1641.                 // // $stmt->execute();
  1642.                 // // $pictures = $stmt->fetchAll();
  1644.                 // if(isset($pictures) && is_array($pictures) && count($pictures) > 0) {
  1645.                 //     $pictureId = $pictures[0]['id'];
  1647.                 //     # CHECK IF COVER IS ALREADY ASSIGNED
  1648.                 //     $sql = "SELECT LOWER(HEX(media.id)) AS media_id,
  1649.                 //         CONCAT (media.file_name, '.', media.file_extension) AS file_name
  1650.                 //         FROM product
  1651.                 //         INNER JOIN product_media ON ( product.cover = product_media.id)
  1652.                 //         INNER JOIN media ON (product_media.media_id = media.id)
  1653.                 //         WHERE LOWER(HEX(product.id)) = '".$productId."'
  1654.                 //         LIMIT 1
  1655.                 //     ;";
  1656.                 //     $stmt = $conn_shopware->prepare($sql);
  1657.                 //     $stmt->execute();
  1658.                 //     $cover_SELECT = $stmt->fetchAll();
  1661.                 //     if( !(isset($cover_SELECT) && is_array($cover_SELECT) && count($cover_SELECT) > 0)
  1662.                 //         || (isset($cover_SELECT['filename']) && strtolower( $article['materials_picture'] ) != strtolower( $cover_SELECT['filename']) ) ) {
  1663.                 //         # NO COVER ASSIGNED, OR DIFFERENT COVER:
  1665.                 //         $cover = new stdClass();
  1666.                 //         $cover->type = "product_media";
  1667.                 //         $cover->mediaId = $pictureId;
  1668.                 //         $ProductFlat->setCover($cover);
  1669.                         
  1670.                 //         $media = new stdClass();
  1671.                 //         $media->type = "product_media";
  1672.                 //         $media->mediaId = $pictureId;
  1673.                 //         $ProductFlat->setMedia(array($media));
  1674.         
  1675.                 //     }
  1677.                 // }
  1678.                 
  1679.                 if( isset($storage_option_list) && is_array($storage_option_list) && count($storage_option_list) > 0) {
  1680.                     $sol_option = [];
  1681.                     foreach($storage_option_list as $key => $sol){
  1682.                         $sol_option[$key]["id"] = $sol["optionId"];
  1683.                     }
  1684.                     $ProductFlat->setOptions( $sol_option );
  1688.                     $sql = "SELECT LOWER(HEX(product.id)) as id
  1689.                         FROM product
  1690.                         -- WHERE LOWER( product_number ) = LOWER('".$ProductFlat->getProductNumber()."')
  1691.                         WHERE product_number = ".$ProductFlat->getProductNumber()."
  1692.                         LIMIT 1
  1693.                     ;";
  1694.                     $stmt = $conn_shopware->prepare($sql);
  1695.                     $stmt->execute();
  1696.                     $product_SELECT = $stmt->fetchAll();
  1698.                     
  1700.                     if(isset($product_SELECT) && is_array($product_SELECT) && count($product_SELECT) > 0) {
  1701.                         # UPDATE
  1702.                         $ProductFlat->setVisibilities([]);
  1704.                         try {
  1705.                             $post_product = $this->client->request('PATCH', '/api/product/'.$product_SELECT[0]['id'], array(
  1706.                                 //    "debug" => true,
  1707.                                 'allow_redirects'=>array('strict' => true),
  1708.                                 "headers" => $this->header,
  1709.                                 "body" => $ProductFlat
  1710.                             ));
  1711.                         } catch (RequestException $e) {
  1712.                             // echo Psr7\str($e->getRequest());
  1713.                             // if ($e->hasResponse()) {
  1714.                             //     echo Psr7\str($e->getResponse());
  1715.                             // }
  1716.                             if ($e->hasResponse()) {
  1717.                                 $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
  1718.                             }else{
  1719.                                 $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
  1720.                             }
  1721.                             $response->headers->set('Content-Type', 'application/json');
  1722.                             return $response;
  1723.                             exit;
  1724.                         }
  1726.                         $productId = $product_SELECT[0]['id'];
  1728.                     } else {
  1729.                         # INSERT
  1730.                         try {
  1731.                             $post_product = $this->client->request('POST', '/api/product', array(
  1732.                                 //    "debug" => true,
  1733.                                 'allow_redirects'=>array('strict' => true),
  1734.                                 "headers" => $this->header,
  1735.                                 "body" => $ProductFlat
  1736.                             ));
  1737.                         } catch (RequestException $e) {
  1738.                             // echo Psr7\str($e->getRequest());
  1739.                             // if ($e->hasResponse()) {
  1740.                             //     echo Psr7\str($e->getResponse());
  1741.                             // }
  1742.                             if ($e->hasResponse()) {
  1743.                                 $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
  1744.                             }else{
  1745.                                 $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
  1746.                             }
  1747.                             $response->headers->set('Content-Type', 'application/json');
  1748.                             return $response;
  1749.                             exit;
  1750.                         }
  1751.                         // # PARENT ID
  1752.                         $productId = $post_product->getHeaders(); 
  1753.                         $productId = str_replace($this->base_url.$poststring.'/','',$productId["Location"]); // ID des erstellten produktes
  1754.                         $productId = $productId[0];
  1755.                     }
  1757.                     
  1758.                     //###*** Prüfe auf Bild und füge es hinzu, falls es nicht vorhanden ist.
  1759.                     if(isset( $article['storages_picture']) && !empty( $article['storages_picture']) &&  $article['storages_picture'] != ""){
  1760.                         $this->add_image($article['storages_picture'], $productId, false); 
  1761.                     }elseif(isset( $article['materials_picture']) && !empty( $article['materials_picture']) &&  $article['materials_picture'] != ""){
  1762.                         $this->add_image($article['materials_picture'], $productId, false); 
  1763.                     }else{
  1764.                         $this->add_image($this->default_image_filename, $productId, false);
  1765.                     }
  1767.                     // echo "<pre>"; var_dump($ProductFlat); exit;
  1769.                     # UPDATE KEYWORDS ###################
  1770.                     $keywords = [ $article['search_word'] ];
  1771.                     try {
  1772.                         $post_product = $this->client->request('PATCH', '/api/product/'.$productId, array(
  1773.                             //    "debug" => true,
  1774.                             'allow_redirects'=>array('strict' => true),
  1775.                             'headers' => $this->header,
  1776.                             'body' => json_encode([
  1777.                                 "customSearchKeywords"=> $keywords   // DAS IST KEIN MERGE: WERTE WERDEN KNALLHART ÜBERSCHRIEBEN
  1778.                             ])
  1779.                         ));
  1780.                     } catch (RequestException $e) {
  1781.                         // echo Psr7\str($e->getRequest());
  1782.                         // if ($e->hasResponse()) {
  1783.                         //     echo Psr7\str($e->getResponse());
  1784.                         // }
  1785.                         if ($e->hasResponse()) {
  1786.                             $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
  1787.                         }else{
  1788.                             $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
  1789.                         }
  1790.                         $response->headers->set('Content-Type', 'application/json');
  1791.                         return $response;
  1792.                         exit;
  1793.                     }
  1794.                     # END UPDATE KEYWORDS ###################
  1795.                 }
  1797.                 // echo "<pre>"; var_dump(__LINE__); exit;
  1799.                 # UPDATE PARENT, FEHLENDE OPTIONEN IN VARIANTENGENERATOR HINZUFÜGEN ##########################
  1800.                 $sql = "SELECT *
  1801.                     , LOWER(HEX(product_id)) as product_id
  1802.                     , LOWER(HEX(property_group_option_id)) as property_group_option_id
  1803.                     FROM product_configurator_setting
  1804.                     WHERE LOWER(HEX(product_id)) = '".$productParentId."'
  1805.                 ;";
  1806.                 $stmt = $conn_shopware->prepare($sql);
  1807.                 $stmt->execute();
  1808.                 $config_settings = $stmt->fetchAll();
  1810.                 $option_list = array_column($storage_option_list, 'optionId');
  1811.                 $config_list = array_column($config_settings, 'property_group_option_id');
  1813.                 foreach($option_list as $option) {
  1814.                     if(!in_array($option, $config_list)) {
  1815.                         try {
  1816.                             $post_product = $this->client->request('POST', '/api/product/'.$productParentId.'/configurator-settings', array(
  1817.                                 // "debug" => true,
  1818.                                 'allow_redirects'=>array('strict'=>true),
  1819.                                 "headers" => $this->header,
  1820.                                 // 'body' => $ProductFlat
  1821.                                 'body' => json_encode([
  1822.                                     "optionId"=>$option
  1823.                                     // 'name' => strval($i),
  1824.                                     // 'position' => $i
  1825.                                 ])
  1826.                             ));
  1827.                         } catch (RequestException $e) {
  1828.                             // echo Psr7\str($e->getRequest());
  1829.                             // if ($e->hasResponse()) {
  1830.                             //     echo Psr7\str($e->getResponse());
  1831.                             // }
  1832.                             if ($e->hasResponse()) {
  1833.                                 $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
  1834.                             }else{
  1835.                                 $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
  1836.                             }
  1837.                             $response->headers->set('Content-Type', 'application/json');
  1838.                             return $response;
  1839.                             exit;
  1840.                         }
  1841.                     }
  1842.                 }
  1848.                 # UPDATE DATETIME
  1849.                 $sqlt = "UPDATE storages
  1850.                     -- SET datetime_last_imported = '".date('Y-m-d H:i:s')."'
  1851.                     SET upload_done = 1
  1852.                     WHERE stone_number = '".$article['product_number']."'
  1853.                 ;";
  1854.                 $stmtt = $conn_akn->prepare($sqlt);
  1855.                 $stmtt->execute();
  1856.                 
  1858.             }
  1859.         }
  1861.         # NEXT PAGE
  1862.         if($nextPage == true){
  1863.             return $this->redirect('/import/products_zulauf');
  1864.         }
  1866.         # CLEAR CACHE ##########################
  1867.         $this->clear_cache();
  1869.         $sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,UpdateDatetime)
  1870.                 VALUES(?,?,?)";
  1871.         $stmtt = $conn_shopware->prepare($sql);
  1872.         $stmtt->execute(array( 
  1873.             "import/products_zulauf",
  1874.             "ENDE",
  1875.             date('Y-m-d H:i:s')
  1876.         ));
  1878.         # DONE
  1879.         // return json_encode(array( 'success' => true ));
  1880.         $response = new Response(json_encode(array('success' => true)));
  1881.         $response->headers->set('Content-Type', 'application/json');
  1882.         return $response;
  1883.         // return $this->render('update/index.html.twig', ["return" => ['success' => "Zulaufartikel importiert" ]]);
  1884.     }
  1888.     // public function import_products_zulauf_BACKUP() {
  1889.     //     $this->client_connect();
  1890.     //     $conn_shopware = $this->shop_connect();
  1891.     //     $conn_akn = $this->dump_connect();
  1892.     //     $step_count = 5;
  1895.     //     # holt alle ZULAUF Artikel ab die eine passende Materialaufschlüsselung haben
  1896.     //     $sql = "SELECT *
  1897.     //         , articles.material_id as material_id
  1898.     //         , articles.materialgroup_id as materialgroup_id
  1899.     //         , storages.id as storage_id
  1900.     //         -- , CONCAT(storages.article_id, '_' ,storages.id) AS product_number
  1901.     //         -- , CONCAT('zulauf', '_', storages.material_id, '_', storages.materialgroup_id, '_', storages.id) AS product_number
  1902.     //         , storages.stone_number AS product_number
  1903.     //         , CONCAT('Zulauf_', storages.material_id, '_', storages.materialgroup_id) AS product_number_parent
  1904.     //         -- , COALESCE(NULLIF(storages.picture, ''), NULLIF(materials.picture, ''), 'default.jpg') AS picture   -- select picture
  1905.     //         , storages.picture AS storages_picture
  1906.     //         , materials.picture AS materials_picture
  1907.     //         , COALESCE(NULLIF(materials.picture, ''), 'default.jpg') AS picture_parent   -- select picture
  1908.     //         FROM articles
  1909.     //         LEFT JOIN materials ON (articles.material_id = materials.material_id AND articles.materialgroup_id = materials.materialgroup_id )
  1910.     //         -- LEFT JOIN articlegroups ON (articlegroups.articlegroup_id = articles.article_id)
  1911.     //         LEFT JOIN articlegroups ON (articles.articlegroup_id = articlegroups.articlegroup_id)
  1912.     //         LEFT JOIN materialgroups ON (materialgroups.materialgroup_id = articles.materialgroup_id)
  1913.     //         LEFT JOIN surfaces ON (surfaces.surface_id = articles.surface_id AND surfaces.materialgroup_id = articles.materialgroup_id)
  1914.     //         INNER JOIN storages ON (articles.article_id = storages.article_id)
  1915.     //         LEFT JOIN storagefaults ON (storages.fault_id = storagefaults.fault_id)
  1916.     //         WHERE storages.stone_number IS NOT NULL
  1917.     //         AND storages.storage = 'ZULAUF'
  1918.     //         AND storages.upload_done IS NULL
  1919.     //         AND material_name IS NOT NULL
  1920.     //         -- AND articles.material_id = 'BCCD'
  1921.     //         ORDER BY articles.id ASC
  1922.     //         LIMIT ".$step_count."
  1923.     //     ;";
  1925.     //     $stmt = $conn_akn->prepare($sql);
  1926.     //     $stmt->execute();
  1927.     //     $articles = $stmt->fetchAll();
  1930.     //     # NEXT STEP
  1931.     //     if(count($articles) >= $step_count) { 
  1932.     //         $nextPage = true;
  1933.     //     } else {
  1934.     //         $nextPage = false;
  1935.     //     }
  1937.     //     # PICTURE
  1938.     //     // dd($articles[0]['picture']);
  1939.     //     // $this->getParameter('kernel.project_dir')."/public/produktbilder"
  1942.     //     // $materialid = $articles[0]["material_id"];
  1943.     //     // $materialname = $articles[0]["material_name"];
  1944.     //     // $materialgruppenname = $articles[0]["materialgroup_name"];
  1945.     //     // $materialgruppenid = $articles[0]["materialgroup_id"];
  1946.     //     // $oberflaeche = $articles[0]["surface_name"];
  1947.         
  1951.     //     foreach($articles as $article) {
  1952.     //         $productParentId = "";
  1953.     //         $productId = "";
  1954.     //         // $productNumber = $article['article_id']
  1955.     //         // var_dump($productNumber);
  1958.     //         # REMOVE SUFFIX FROM PICTURE NAME
  1959.     //         // if(strpos($article['picture'], '-##-')) {
  1960.     //         //     $article['picture'] = substr($article['picture'], 0, -5);
  1961.     //         // }
  1962.     //         if(strpos($article['storages_picture'], '-##-')) {
  1963.     //             $article['storages_picture'] = substr($article['picture'], 0, -5);
  1964.     //         }
  1965.     //         if(strpos($article['materials_picture'], '-##-')) {
  1966.     //             $article['storages_picture'] = substr($article['picture'], 0, -5);
  1967.     //         }
  1968.     //         # END EDIT PICTURE NAME
  1971.     //         $storage_option_list = $this->get_variant_ids($article); //übergibt storage, returned formatierten array für variantenerstellung
  1974.     //         # CHECK IF PARENT PRODUCT ALREADY EXISTS ###################################
  1975.     //         // $sql = "SELECT *, LOWER(HEX(id)) as id
  1976.     //         $sql = "SELECT LOWER(HEX(product.id)) as id
  1977.     //             FROM product_translation
  1978.     //             INNER JOIN product ON (product.id = product_translation.product_id)
  1979.     //             INNER JOIN product_category ON ( product_translation.product_id = product_category.product_id)
  1980.     //             -- INNER JOIN category ON ( product_category.category_id = category.category_id)
  1981.     //             -- INNER JOIN category_translation ON ( product_category.category_id = category_translation.category_id)
  1982.     //             WHERE LOWER(HEX(product_category.category_id)) = '".$this->zulauf_id."'
  1983.     //             -- AND JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_material_id') = '".$article['material_id']."'
  1984.     //             -- AND JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_materialgroup_id')  = '".$article['materialgroup_id']."'
  1985.     //             AND product.product_number = '". $article['product_number_parent'] . "'
  1986.     //             AND parent_id IS NULL
  1987.     //             LIMIT 1
  1988.     //         ;";
  1989.     //         $stmt = $conn_shopware->prepare($sql);
  1990.     //         $stmt->execute();
  1991.     //         $search_product = $stmt->fetchAll();
  1993.     //         # TODO: IF PRODUCT ALREADY EXISTS
  1995.     //         if(isset($search_product[0]["id"]) && !empty($search_product[0]["id"]) && count($search_product) > 0) {
  1996.     //             $productParentId = $search_product[0]["id"];
  1997.     //         }
  1999.     //         # SET DATA FOR PRODUCT ###################################
  2000.     //         $ProductFlat = new \App\Controller\shopware\ProductFlat();
  2001.     //         $ProductFlat->setActive(true);
  2002.     //         // $ProductFlat->setCategories(array(array("id" => $this->getCategoryId($materialgruppenname, $materialgruppenid))));
  2003.     //         $ProductFlat->setCategories(array(array("id" => $this->zulauf_id)));
  2004.     //         $ProductFlat->setDeliveryTimeId($this->deliverytime_id);
  2005.     //         $ProductFlat->setDescription($article["article_name_1"]);
  2006.     //         $ProductFlat->setIsCloseout(false); // ABVERKAUF - Wichtig, da Anzahl = 0
  2007.     //         $ProductFlat->setMarkAsTopseller(false);
  2008.     //         // $ProductFlat->setName('Zulauf '.$article['product_number_parent'] .' ('.  date('Y-m-d H:m:s') .')');
  2009.     //         // $ProductFlat->setName('Zulauf '.$article['product_number_parent']);
  2010.     //         // $ProductFlat->setName('ZULAUF: ' . $article['product_number_parent']);
  2011.     //         $ProductFlat->setName('Zulauf: ' . $article['material_name']);
  2012.             
  2013.     //         // $ProductFlat->setName('Zulauf '.$materialname);
  2014.     //         // $ProductFlat->setName('toller lustiger testname');
  2015.     //         $ProductFlat->setPrice(array(array(
  2016.     //             "currencyId" => "b7d2554b0ce847cd82f3ac9bd1c0dfca",
  2017.     //             "net" =>  0,
  2018.     //             "gross" => 0,
  2019.     //             "linked" => false
  2020.     //         )));
  2022.     //         // $ProductFlat->setProductNumber($article['product_number']);
  2023.     //         // $ProductFlat->setProductNumber($article["materialgroup_id"]);
  2024.     //         $ProductFlat->setProductNumber($article['product_number_parent']);
  2026.     //         $ProductFlat->setPurchaseUnit(1);
  2027.     //         // $ProductFlat->setRestockTime();
  2028.     //         // $ProductFlat->setUnitId("ff7d882bd1da432bb4e05e21eda66114");
  2029.     //         $ProductFlat->setTaxId("c5ddfa75e22b4889b6f628e1df0b347f");
  2032.     //         // $ProductFlat->setCustomFields();
  2033.     //         $custom_fields = array();
  2034.     //         // $custom_fields["custom_article_surface"] = $article["surface_name"];
  2035.     //         // $custom_fields["custom_article_search_word"] = $article["search_word"]; 
  2036.     //         // $custom_fields["custom_article_name_1"] = $article["article_name_1"];
  2037.     //         // $custom_fields["custom_article_name_2"] = $article["article_name_2"];
  2038.     //         // $custom_fields["custom_article_material_id"] = $article["material_id"];
  2039.     //         // $custom_fields["custom_article_materialgroup_id"] = $article["materialgroup_id"];
  2040.     //         // // $custom_fields["custom_article_material_name"] = $materialname;
  2041.     //         // $custom_fields["custom_article_material_name"] = $article['material_name'];
  2042.     //         // $custom_fields["custom_article_materialgroup_name"] = $article["materialgroup_name"];
  2043.     //         // $custom_fields["custom_article_picture"] = $article["picture"];
  2044.     //         // $custom_fields["custom_article_material_id"] = $article["material_id"];
  2045.     //         // $custom_fields["custom_article_materialgroup_id"] = $article["materialgroup_id"];
  2048.     //         $custom_fields["custom_article_surface"]            = $article["surface_name"];
  2049.     //         $custom_fields["custom_article_search_word"]        = $article["search_word"];
  2050.     //         $custom_fields["custom_article_name_1"]             = $article["article_name_1"];
  2051.     //         $custom_fields["custom_article_name_2"]             = $article["article_name_2"];
  2052.     //         $custom_fields["custom_article_material_id"]        = $article["material_id"];
  2053.     //         $custom_fields["custom_article_materialgroup_id"]   = $article["materialgroup_id"];
  2054.     //         $custom_fields["custom_article_material_name"]      = $article['material_name'];
  2055.     //         $custom_fields["custom_article_materialgroup_name"] = $article["materialgroup_name"];
  2056.     //         $custom_fields["custom_article_picture"]            = $article["picture"];
  2057.     //         $custom_fields["custom_article_id"]                 = $article["article_id"];
  2058.     //         $custom_fields["custom_article_stone_number"]       = $article["stone_number"];
  2059.     //         $custom_fields["custom_article_block_number"]       = $article["block_advice"];
  2060.     //         $custom_fields["custom_article_search_word"]        = $article["search_word"];
  2061.     //         $custom_fields["custom_article_advice"]             = $article["advice"];
  2062.     //         $custom_fields["custom_article_storage"]            = $article["storage"];
  2063.     //         $custom_fields["custom_article_amount_2"]           = $article["amount_2"];
  2064.     //         $custom_fields["custom_article_articlegroup_name"]  = $article["articlegroup_name"];
  2065.     //         $custom_fields["custom_article_fault_id"]           = $article["fault_name"];
  2066.     //         $custom_fields["custom_article_fault_corner"]       = $article["fault_corner"];
  2067.     //         $custom_fields["custom_article_additional_advice"]  = $article["additional_advice"];
  2069.     //         $ProductFlat->setCustomFields(
  2070.     //             $custom_fields
  2071.     //         );
  2073.     //         # TODO: Materialfarbe
  2074.     //         // //Materialfarbe
  2075.     //         // unset($farbe);
  2076.     //         // if(isset($storage["materialgroup_id"]) && !empty($storage["materialgroup_id"]) &&
  2077.     //         // isset($storage["material_id"]) && !empty($storage["material_id"])){
  2078.     //         //     $sql = "SELECT *
  2079.     //         //             FROM materialcolors
  2080.     //         //             INNER JOIN `keys` ON (materialcolors.color_id = key_number)
  2081.     //         //             WHERE color_id != -1
  2082.     //         //             AND key_kind = 88
  2083.     //         //             AND mgr_id = '".$storage["materialgroup_id"]."'
  2084.     //         //             AND mar_id = '".$storage["material_id"]."'
  2085.     //         //         ;";
  2086.     //         //     $stmt = $conn_shopware->prepare($sql);
  2087.     //         //     $stmt->execute();
  2088.     //         //     $colors = $stmt->fetchAll();
  2089.     //         //     // return $this->render('update/index.html.twig', ["return" => ['success' => $colors ]]);
  2090.     //         //     if(isset($colors) && is_array($colors) && count($colors) > 0){
  2091.     //         //         $farbe = $colors[0]["key_text"];
  2092.     //         //     }
  2093.     //         // }
  2094.     //         // if(isset($farbe) && !empty($farbe)){
  2095.     //         //     $custom_fields["custom_article_material_color"] = $farbe;
  2096.     //         // }
  2098.     //         $ProductFlat->setHeight($article["thickness"]);
  2099.     //         $ProductFlat->setLength($article["length"]);
  2100.     //         $ProductFlat->setWidth($article["width"]);
  2101.     //         $ProductFlat->setStock(0);
  2102.     //         $ProductFlat->setMaxPurchase(0);
  2104.     //         $ProductFlat->setVisibilities(array(array(
  2105.     //             "salesChannelId" => $this->saleschannel_id,
  2106.     //             "visibility" => 30
  2107.     //         )));
  2111.     //         # UPDATE OR INSERT ###################################
  2112.     //         $poststring = 'api/product';
  2113.             
  2114.             
  2115.     //         $storage_option_list = $this->get_variant_ids($article); //übergibt storage, returned formatierten array für variantenerstellung
  2116.             
  2118.     //         # PARENT ###################################
  2119.     //         if( isset($article['material_name']) && trim($article['material_name']) != '') {
  2121.     //             if($productParentId == '') {
  2122.     //                 # INSERT PARENT ARTICLE ##########################
  2124.     //                 if( isset($storage_option_list) && is_array($storage_option_list) && count($storage_option_list) > 0) {
  2125.     //                     # TODO: vorbereitung der Varianten
  2126.     //                     // $ProductFlat->setConfiguratorGroupConfig(array(array(
  2127.     //                     //     // "id" => "5eb85799b5914547adf6348ff2213d5e",
  2128.     //                     //     // "representation" => "box",
  2129.     //                     //     // "expressionForListings" => false
  2130.     //                     // )));
  2132.     //                     $ProductFlat->setConfiguratorGroupConfig(array(
  2133.     //                         // array(
  2134.     //                         //     "id" => $this->thickness_id,
  2135.     //                         //     "representation" => "box",
  2136.     //                         //     "expressionForListings" => false
  2137.     //                         // ),
  2138.     //                         // array(
  2139.     //                         //     "id" => $this->blocknumber_id,
  2140.     //                         //     "representation" => "box",
  2141.     //                         //     "expressionForListings" => false
  2142.     //                         // ),
  2143.     //                         // array(
  2144.     //                         //     "id" => $this->surface_id,
  2145.     //                         //     "representation" => "box",
  2146.     //                         //     "expressionForListings" => false
  2147.     //                         // ),
  2148.     //                         // array(
  2149.     //                         //     "id" => $this->fault_exists_id,
  2150.     //                         //     "representation" => "box",
  2151.     //                         //     "expressionForListings" => false
  2152.     //                         // ),
  2153.     //                         // array(
  2154.     //                         //     "id" => $this->color_id,
  2155.     //                         //     "representation" => "box",
  2156.     //                         //     "expressionForListings" => false
  2157.     //                         // )
  2158.     //                         array(
  2159.     //                             "id" => $this->articlegroup_id,
  2160.     //                             "representation" => "box",
  2161.     //                             "expressionForListings" => false
  2162.     //                         ),
  2163.     //                         array(
  2164.     //                             "id" => $this->surface_id,
  2165.     //                             "representation" => "box",
  2166.     //                             "expressionForListings" => false
  2167.     //                         ),
  2168.     //                         array(
  2169.     //                             "id" => $this->thickness_id,
  2170.     //                             "representation" => "box",
  2171.     //                             "expressionForListings" => false
  2172.     //                         ),
  2173.     //                         // array(
  2174.     //                         //     "id" => $this->stonenumber_id,
  2175.     //                         //     "representation" => "box",
  2176.     //                         //     "expressionForListings" => false
  2177.     //                         // ),
  2178.     //                         array(
  2179.     //                         "id" => $this->blocknumber_id,
  2180.     //                             "representation" => "box",
  2181.     //                             "expressionForListings" => false
  2182.     //                         ),
  2183.     //                         // array(
  2184.     //                         //     "id" => $this->fault_exists_id,
  2185.     //                         //     "representation" => "box",
  2186.     //                         //     "expressionForListings" => false
  2187.     //                         // ),
  2188.     //                         array(
  2189.     //                             "id" => $this->color_id,
  2190.     //                             "representation" => "box",
  2191.     //                             "expressionForListings" => false
  2192.     //                         )
  2193.     //                     ));
  2197.     //                     # SET CONFIGURATOR SETTINGS
  2198.     //                     // dd($storage_option_list);
  2199.     //                     // $ProductFlat->setConfiguratorSettings($storage_option_list);
  2201.                         
  2202.     //                     // $sol_option=array();
  2203.     //                     // foreach($storage_option_list as $key => $sol) {
  2204.     //                     //     // $sol_option[$key]["productId"] = "";
  2205.     //                     //     $sol_option[$key]["id"] = $sol["optionId"];
  2206.     //                     // }
  2207.     //                     // $ProductFlat->setConfiguratorSettings($sol_option);
  2208.                         
  2209.     //                     try {
  2210.     //                         $post_product = $this->client->request('POST', '/'.$poststring, array(
  2211.     //                             //    "debug" => true,
  2212.     //                             'allow_redirects'=>array('strict'=>true),
  2213.     //                             "headers" => $this->header,
  2214.     //                             "body" => $ProductFlat
  2215.     //                         ));
  2216.     //                     } catch (RequestException $e) {
  2217.     //                         echo Psr7\str($e->getRequest());
  2218.     //                         if ($e->hasResponse()) {
  2219.     //                             echo Psr7\str($e->getResponse());
  2220.     //                         }
  2221.     //                         exit;
  2222.     //                     }
  2224.     //                     # PARENT ID
  2225.     //                     $productParentId = $post_product->getHeaders(); 
  2226.     //                     $productParentId =  str_replace($this->base_url.$poststring.'/','',$productParentId["Location"]); // ID des erstellten produktes
  2227.     //                     $productParentId = $productParentId[0];
  2228.     //                 }
  2229.     //             }
  2231.     //             # PARENT COVER ##########################
  2232.     //             // TODO: picture_parent
  2236.     //             # INSERT VARIANT ##########################
  2237.     //             $ProductFlat->setParentId($productParentId);
  2238.     //             $ProductFlat->setProductNumber($article["product_number"]);
  2240.     //             $ProductFlat->setConfiguratorGroupConfig(null);
  2241.     //             $ProductFlat->setConfiguratorSettings(null);
  2243.     //             // $ProductFlat->setName('Zulauf '.$article['product_number_parent']);
  2244.     //             // $ProductFlat->setName($article['product_number_parent']);
  2245.     //             $ProductFlat->setName('Zulauf: ' . $article['material_name']);
  2246.                 
  2247.     //             $cover = new stdClass();
  2248.     //             $cover->type = "product_media";
  2249.     //             $cover->type = "product_media";
  2250.     //             $ProductFlat->setCover($cover);
  2251.                 
  2252.     //             if( isset($storage_option_list) && is_array($storage_option_list) && count($storage_option_list) > 0) {
  2253.     //                 $sol_option = [];
  2254.     //                 foreach($storage_option_list as $key => $sol){
  2255.     //                     $sol_option[$key]["id"] = $sol["optionId"];
  2256.     //                 }
  2257.     //                 $ProductFlat->setOptions( $sol_option );
  2261.     //                 $sql = "SELECT LOWER(HEX(product.id)) as id
  2262.     //                     FROM product
  2263.     //                     -- WHERE LOWER( product_number ) = LOWER('".$ProductFlat->getProductNumber()."')
  2264.     //                     WHERE product_number = ".$ProductFlat->getProductNumber()."
  2265.     //                     LIMIT 1
  2266.     //                 ;";
  2267.     //                 $stmt = $conn_shopware->prepare($sql);
  2268.     //                 $stmt->execute();
  2269.     //                 $product_SELECT = $stmt->fetchAll();
  2271.                     
  2273.     //                 if(isset($product_SELECT) && is_array($product_SELECT) && count($product_SELECT) > 0) {
  2274.     //                     # UPDATE
  2275.     //                     $ProductFlat->setVisibilities([]);
  2277.     //                     try {
  2278.     //                         $post_product = $this->client->request('PATCH', '/api/product/'.$product_SELECT[0]['id'], array(
  2279.     //                             //    "debug" => true,
  2280.     //                             'allow_redirects'=>array('strict' => true),
  2281.     //                             "headers" => $this->header,
  2282.     //                             "body" => $ProductFlat
  2283.     //                         ));
  2284.     //                     } catch (RequestException $e) {
  2285.     //                         echo Psr7\str($e->getRequest());
  2286.     //                         if ($e->hasResponse()) {
  2287.     //                             echo Psr7\str($e->getResponse());
  2288.     //                         }
  2289.     //                         exit;
  2290.     //                     }
  2292.     //                     $productId = $product_SELECT[0]['id'];
  2294.     //                 } else {
  2295.     //                     # INSERT
  2296.     //                     try {
  2297.     //                         $post_product = $this->client->request('POST', '/api/product', array(
  2298.     //                             //    "debug" => true,
  2299.     //                             'allow_redirects'=>array('strict' => true),
  2300.     //                             "headers" => $this->header,
  2301.     //                             "body" => $ProductFlat
  2302.     //                         ));
  2303.     //                     } catch (RequestException $e) {
  2304.     //                         echo Psr7\str($e->getRequest());
  2305.     //                         if ($e->hasResponse()) {
  2306.     //                             echo Psr7\str($e->getResponse());
  2307.     //                         }
  2308.     //                         exit;
  2309.     //                     }
  2310.     //                     // # PARENT ID
  2311.     //                     $productId = $post_product->getHeaders(); 
  2312.     //                     $productId = str_replace($this->base_url.$poststring.'/','',$productId["Location"]); // ID des erstellten produktes
  2313.     //                     $productId = $productId[0];
  2314.     //                 }
  2316.     //                 echo "<pre>"; var_dump($ProductFlat); exit;
  2318.     //                 # UPDATE KEYWORDS ###################
  2319.     //                 $keywords = [ $article['search_word'] ];
  2320.     //                 try {
  2321.     //                     $post_product = $this->client->request('PATCH', '/api/product/'.$productId, array(
  2322.     //                         //    "debug" => true,
  2323.     //                         'allow_redirects'=>array('strict' => true),
  2324.     //                         'headers' => $this->header,
  2325.     //                         'body' => json_encode([
  2326.     //                             "customSearchKeywords"=> $keywords   // DAS IST KEIN MERGE: WERTE WERDEN KNALLHART ÜBERSCHRIEBEN
  2327.     //                         ])
  2328.     //                     ));
  2329.     //                 } catch (RequestException $e) {
  2330.     //                     echo Psr7\str($e->getRequest());
  2331.     //                     if ($e->hasResponse()) {
  2332.     //                         echo Psr7\str($e->getResponse());
  2333.     //                     }
  2334.     //                     exit;
  2335.     //                 }
  2336.     //                 # END UPDATE KEYWORDS ###################
  2337.     //             }
  2339.     //             echo "<pre>"; var_dump(__LINE__); exit;
  2341.     //             # UPDATE PARENT, FEHLENDE OPTIONEN IN VARIANTENGENERATOR HINZUFÜGEN ##########################
  2342.     //             $sql = "SELECT *
  2343.     //                 , LOWER(HEX(product_id)) as product_id
  2344.     //                 , LOWER(HEX(property_group_option_id)) as property_group_option_id
  2345.     //                 FROM product_configurator_setting
  2346.     //                 WHERE LOWER(HEX(product_id)) = '".$productParentId."'
  2347.     //             ;";
  2348.     //             $stmt = $conn_shopware->prepare($sql);
  2349.     //             $stmt->execute();
  2350.     //             $config_settings = $stmt->fetchAll();
  2352.     //             $option_list = array_column($storage_option_list, 'optionId');
  2353.     //             $config_list = array_column($config_settings, 'property_group_option_id');
  2355.     //             foreach($option_list as $option) {
  2356.     //                 if(!in_array($option, $config_list)) {
  2357.     //                     try {
  2358.     //                         $post_product = $this->client->request('POST', '/api/product/'.$productParentId.'/configurator-settings', array(
  2359.     //                             // "debug" => true,
  2360.     //                             'allow_redirects'=>array('strict'=>true),
  2361.     //                             "headers" => $this->header,
  2362.     //                             // 'body' => $ProductFlat
  2363.     //                             'body' => json_encode([
  2364.     //                                 "optionId"=>$option
  2365.     //                                 // 'name' => strval($i),
  2366.     //                                 // 'position' => $i
  2367.     //                             ])
  2368.     //                         ));
  2369.     //                     } catch (RequestException $e) {
  2370.     //                         echo Psr7\str($e->getRequest());
  2371.     //                         if ($e->hasResponse()) {
  2372.     //                             echo Psr7\str($e->getResponse());
  2373.     //                         }
  2374.     //                         exit;
  2375.     //                     }
  2376.     //                 }
  2377.     //             }
  2380.     //             # PICTURES AND IMAGES ##########################
  2382.     //             # storages_picture
  2383.     //             $sql = "SELECT LOWER(HEX(id)) AS id
  2384.     //                 FROM media
  2385.     //                 WHERE file_name = '".pathinfo($article['storages_picture'], PATHINFO_FILENAME)."'
  2386.     //                 LIMIT 1
  2387.     //             ;";
  2388.     //             $stmt = $conn_shopware->prepare($sql);
  2389.     //             $stmt->execute();
  2390.     //             $pictures = $stmt->fetchAll();
  2392.     //             if(!(isset($pictures) && is_array($pictures) && count($pictures) > 0)) {
  2394.     //                 # MATERIALS_PICTURE
  2395.     //                 $sql = "SELECT LOWER(HEX(id)) AS id
  2396.     //                     FROM media
  2397.     //                     WHERE file_name = '".pathinfo($article['materials_picture'], PATHINFO_FILENAME)."'
  2398.     //                     LIMIT 1
  2399.     //                 ;";
  2400.     //                 $stmt = $conn_shopware->prepare($sql);
  2401.     //                 $stmt->execute();
  2402.     //                 $pictures = $stmt->fetchAll();
  2404.     //                 if(!(isset($pictures) && is_array($pictures) && count($pictures) > 0)) {
  2406.     //                     # DEFAULT_IMAGE
  2407.     //                     $sql = "SELECT LOWER(HEX(id)) AS id
  2408.     //                         FROM media
  2409.     //                         WHERE file_name = '".pathinfo($this->default_image_filename, PATHINFO_FILENAME)."'
  2410.     //                         LIMIT 1
  2411.     //                     ;";
  2412.     //                     $stmt = $conn_shopware->prepare($sql);
  2413.     //                     $stmt->execute();
  2414.     //                     $pictures = $stmt->fetchAll();
  2415.     //                 }
  2416.     //             }
  2418.     //             // $sql = "SELECT LOWER(HEX(id)) AS id
  2419.     //             //     FROM media
  2420.     //             //     WHERE file_name = '".pathinfo($article['picture'], PATHINFO_FILENAME)."'
  2421.     //             //     LIMIT 1
  2422.     //             // ;";
  2423.     //             // $stmt = $conn_shopware->prepare($sql);
  2424.     //             // $stmt->execute();
  2425.     //             // $pictures = $stmt->fetchAll();
  2427.     //             if(isset($pictures) && is_array($pictures) && count($pictures) > 0) {
  2428.     //                 $pictureId = $pictures[0]['id'];
  2430.     //                 # CHECK IF COVER IS ALREADY ASSIGNED
  2431.     //                 $sql = "SELECT LOWER(HEX(media.id)) AS media_id,
  2432.     //                     CONCAT (media.file_name, '.', media.file_extension) AS file_name
  2433.     //                     FROM product
  2434.     //                     INNER JOIN product_media ON ( product.cover = product_media.id)
  2435.     //                     INNER JOIN media ON (product_media.media_id = media.id)
  2436.     //                     WHERE LOWER(HEX(product.id)) = '".$productId."'
  2437.     //                     LIMIT 1
  2438.     //                 ;";
  2439.     //                 $stmt = $conn_shopware->prepare($sql);
  2440.     //                 $stmt->execute();
  2441.     //                 $cover_SELECT = $stmt->fetchAll();
  2444.     //                 if( !(isset($cover_SELECT) && is_array($cover_SELECT) && count($cover_SELECT) > 0)
  2445.     //                     || (isset($cover_SELECT['filename']) && strtolower( $article['materials_picture'] ) != strtolower( $cover_SELECT['filename']) ) ) {
  2446.     //                     # NO COVER ASSIGNED, OR DIFFERENT COVER:
  2448.     //                     # ASSIGN IMAGE
  2449.     //                     try{
  2450.     //                         $post_product = $this->client->request('POST', '/api/product-media/', array(
  2451.     //                             // 'debug' => true,
  2452.     //                             'allow_redirects'=>array('strict'=>true),
  2453.     //                             'headers' => $this->header,
  2454.     //                             'body' => json_encode([
  2455.     //                                 'productId' => $productId,
  2456.     //                                 'position' => 1,
  2457.     //                                 'media' => array(
  2458.     //                                     'id' => strtolower($pictureId)
  2459.     //                                 )
  2460.     //                             ])
  2461.     //                         ));
  2462.     //                         $location = array_pop( $post_product->getHeaders()['Location'] );
  2463.     //                         $productmediaId = basename($location);
  2464.     //                     } catch (RequestException $e) {
  2465.     //                         echo Psr7\str($e->getRequest());
  2466.     //                         if ($e->hasResponse()) {
  2467.     //                             echo Psr7\str($e->getResponse());
  2468.     //                         }
  2469.     //                         exit;
  2470.     //                     }
  2472.     //                     # ASSIGN COVER
  2473.     //                     try{
  2474.     //                         // var_dump("cover zuordnen");
  2475.     //                         $post_product = $this->client->request('PATCH', '/api/product/'.$productId, array(
  2476.     //                             // 'debug' => true,
  2477.     //                             'allow_redirects'=>array('strict'=>true),
  2478.     //                             'headers' => $this->header,
  2479.     //                             'body' => json_encode([
  2480.     //                                 'coverId' => strtolower($productmediaId)
  2481.     //                             ])
  2482.     //                         ));
  2483.     //                     } catch (RequestException $e) {
  2484.     //                         echo Psr7\str($e->getRequest());
  2485.     //                         if ($e->hasResponse()) {
  2486.     //                             echo Psr7\str($e->getResponse());
  2487.     //                         }
  2488.     //                         exit;
  2489.     //                     }
  2491.     //                 }
  2493.     //             }
  2497.     //             # UPDATE DATETIME
  2498.     //             $sqlt = "UPDATE storages
  2499.     //                 -- SET datetime_last_imported = '".date('Y-m-d H:i:s')."'
  2500.     //                 SET upload_done = 1
  2501.     //                 WHERE stone_number = '".$article['product_number']."'
  2502.     //             ;";
  2503.     //             $stmtt = $conn_akn->prepare($sqlt);
  2504.     //             $stmtt->execute();
  2505.                 
  2507.     //         }
  2508.     //     }
  2510.     //     # NEXT PAGE
  2511.     //     if($nextPage == true){
  2512.     //         return $this->redirect('/import/products_zulauf');
  2513.     //     }
  2515.     //     # CLEAR CACHE ##########################
  2516.     //     $this->clear_cache();
  2518.     //     # DONE
  2519.     //     $response = new Response(json_encode(array('success' => true)));
  2520.     //     $response->headers->set('Content-Type', 'application/json');
  2521.     //     return $response;
  2522.     //     // return $this->render('update/index.html.twig', ["return" => ['success' => "Zulaufartikel importiert" ]]);
  2523.     // }
  2527.     /**
  2528.      * @Route("/remove/products_zulauf", name="remove_products_zulauf")
  2529.      * @Route("/remove/products_zulauf/{start}")
  2530.      * 
  2531.      * FUNKTION:
  2532.      * Zulaufartikel ist jetzt normaler Artikel => update
  2533.      * Zulaufartikel wurde gelöscht => inaktiv
  2534.      * 
  2535.      * Diese Funktion ist auch bekannt unter dem Namen "Zulaufumwandler"
  2536.      * 
  2537.      * WICHTIGE INFORMATIONEN:
  2538.      * Der Parent einer Variante ist das Material. ABER: für ein Produkt und einen Zulaufartikel gibt es zwei unterschiedliche
  2539.      * Parents! Wird ein Zulaufartikel zu einem normalen Artikel kann also der Fall entstehen, dass der ParentArtikel noch nicht
  2540.      * generiert wurde!!!
  2541.      * 
  2542.      * 
  2543.      * @return mixed
  2544.      */
  2545.     public function remove_products_zulauf($start = 0) {
  2546.         $this->client_connect();
  2547.         $conn_shopware = $this->shop_connect();
  2548.         $conn_akn = $this->dump_connect();
  2549.         $step_count = 6;
  2551.         $sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,`Page`,UpdateDatetime)
  2552.                 VALUES(?,?,?,?)";
  2553.         $stmtt = $conn_shopware->prepare($sql);
  2554.         $stmtt->execute(array( 
  2555.             "remove/products_zulauf",
  2556.             "Start",
  2557.             $start,
  2558.             date('Y-m-d H:i:s')
  2559.         ));
  2562.         # GET ALL ZULAUFARTIKEL FROM SHOPWARE DATABASE
  2563.         $sql = "SELECT LOWER(HEX(product_category.product_id)) AS product_id,
  2564.             product_number AS product_number,
  2565.             JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_material_id') AS custom_article_material_id,
  2566.             JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_materialgroup_id') AS custom_article_materialgroup_id
  2567.             FROM product_category
  2568.             INNER JOIN product ON (product_category.product_id = product.id)
  2569.             INNER JOIN product_translation ON (product.id = product_translation.product_id)
  2570.             WHERE LOWER(HEX(product_category.category_id)) = '".$this->zulauf_id."'
  2571.             AND parent_id IS NOT NULL
  2572.             LIMIT ".$step_count."
  2573.             OFFSET ".$start."
  2574.         ;";
  2575.         $stmt = $conn_shopware->prepare($sql);
  2576.         $stmt->execute();
  2577.         $product_SELECT = $stmt->fetchAll();
  2579.         if(isset($product_SELECT) && is_array($product_SELECT) && count($product_SELECT) > 0) {
  2580.             # CHECK IF ZULAUFARTIKEL IS IN AKN DATABASE
  2581.             foreach($product_SELECT as $product) {
  2582.                 $sql = "SELECT *
  2583.                     FROM storages
  2584.                     WHERE stone_number = '".$product['product_number']."'
  2585.                 ;";
  2586.                 $stmt = $conn_akn->prepare($sql);
  2587.                 $stmt->execute();
  2588.                 $articles = $stmt->fetchAll();
  2591.                 if(isset($articles) && is_array($articles) && count($articles) > 0) {
  2592.                     # PRODUCT IS STILL ACTIVE
  2593.                     $article = array_pop($articles);
  2594.                     if( strtoupper($article['storage']) != 'ZULAUF') {
  2596.                         # ZULAUFARTIKEL IST JETZT NORMALER ARTIKEL => REMOVE CATEGORY
  2597.                         try {
  2598.                             $post_product = $this->client->request('DELETE', '/api/product/'.$product['product_id'].'/categories/'. $this->zulauf_id, array(
  2599.                                 'allow_redirects'=>array('strict'=>true),
  2600.                                 "headers" => $this->header,
  2601.                             ));
  2602.                         } catch (RequestException $e) {
  2603.                             // echo Psr7\str($e->getRequest());
  2604.                             // if ($e->hasResponse()) {
  2605.                             //     echo Psr7\str($e->getResponse());
  2606.                             // }
  2607.                             if ($e->hasResponse()) {
  2608.                                 $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
  2609.                             }else{
  2610.                                 $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
  2611.                             }
  2612.                             $response->headers->set('Content-Type', 'application/json');
  2613.                             return $response;
  2614.                             exit;
  2615.                         }
  2617.                         # SET NEW PARENT ######################
  2618.                         $sql = "SELECT LOWER(HEX(product.id)) as id, product_number
  2619.                             FROM product
  2620.                             WHERE product_number = '".$article['material_id'] . "_" . $article['materialgroup_id']."'
  2621.                             AND parent_id IS NULL
  2622.                             LIMIT 1
  2623.                         ;";
  2624.                         $stmt = $conn_shopware->prepare($sql);
  2625.                         $stmt->execute();
  2626.                         $parent_SEARCH = $stmt->fetchAll();
  2628.                         
  2629.                         if(isset($parent_SEARCH) && is_array($parent_SEARCH) && count($parent_SEARCH) > 0) {
  2630.                             # PARENT AS ARTICLE EXISTING
  2631.                             # PARENT ID
  2632.                             $productParentId = $parent_SEARCH[0]['id'];
  2633.                         } else {
  2634.                             # PARENT AS ARTICLE IS MISSING
  2635.                             # WE NEED TO ADD THE MISSING PARENT, BUT ONLY WITH THE NECESSARY VALUES ... THE PRODUCT UPDATE WILL BE THE NEXT FUNCTION RUN BY THE CRONJOB!
  2636.                             $ProductParentFlat = new \App\Controller\shopware\ProductFlat();
  2637.                             $ProductParentFlat->setActive(true);
  2638.                             $ProductParentFlat->setProductNumber($article['custom_article_material_id'] . '_' . $article['custom_article_materialgroup_id']);
  2640.                             $poststring = 'api/product';
  2642.                             try {
  2643.                                 $post_product = $this->client->request('POST', '/'.$poststring, array(
  2644.                                     //    "debug" => true,
  2645.                                     'allow_redirects'=>array('strict'=>true),
  2646.                                     "headers" => $this->header,
  2647.                                     "body" => $ProductParentFlat
  2648.                                 ));
  2649.                             } catch (RequestException $e) {
  2650.                                 // echo Psr7\str($e->getRequest());
  2651.                                 // if ($e->hasResponse()) {
  2652.                                 //     echo Psr7\str($e->getResponse());
  2653.                                 // }
  2654.                                 if ($e->hasResponse()) {
  2655.                                     $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
  2656.                                 }else{
  2657.                                     $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
  2658.                                 }
  2659.                             $response->headers->set('Content-Type', 'application/json');
  2660.                             return $response;
  2661.                                 exit;
  2662.                             }
  2663.     
  2664.                             # PARENT ID
  2665.                             $productParentId = $post_product->getHeaders(); 
  2666.                             $productParentId =  str_replace($this->base_url.$poststring.'/', '', $productParentId["Location"]); // ID des erstellten produktes
  2667.                             $productParentId = $productParentId[0];
  2668.                         }
  2669.                         # END SET NEW PARENT ######################
  2671.                         $ProductFlat = new \App\Controller\shopware\ProductFlat();
  2672.                         $ProductFlat->setProductNumber( $product['product_number']);
  2673.                         $ProductFlat->setActive(false);
  2674.                         $ProductFlat->setParentId($productParentId);
  2676.                         # UPDATE
  2677.                         try {
  2678.                             $post_product = $this->client->request('PATCH', '/api/product/'.$product['product_id'], array(
  2679.                                 //    "debug" => true,
  2680.                                 'allow_redirects'=>array('strict' => true),
  2681.                                 "headers" => $this->header,
  2682.                                 "body" => $ProductFlat
  2683.                             ));
  2684.                         } catch (RequestException $e) {
  2685.                             // echo Psr7\str($e->getRequest());
  2686.                             // if ($e->hasResponse()) {
  2687.                             //     echo Psr7\str($e->getResponse());
  2688.                             // }
  2689.                             if ($e->hasResponse()) {
  2690.                                 $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
  2691.                             }else{
  2692.                                 $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
  2693.                             }
  2694.                         $response->headers->set('Content-Type', 'application/json');
  2695.                         return $response;
  2696.                             exit;
  2697.                         }
  2700.                     }
  2701.                 } else {
  2702.                     # ZULAUFARTIKEL WURDE GELÖSCHT => DELETE PRODUCT
  2703.                     $ProductFlat = new \App\Controller\shopware\ProductFlat();
  2704.                     $ProductFlat->setActive(false);
  2705.                     $ProductFlat->setProductNumber( $product['product_number']);
  2707.                     try {
  2708.                         $post_product = $this->client->request('PATCH', '/api/product/'.$product['product_id'], array(
  2709.                             //    "debug" => true,
  2710.                             'allow_redirects'=>array('strict' => true),
  2711.                             "headers" => $this->header,
  2712.                             "body" => $ProductFlat
  2713.                         ));
  2714.                     } catch (RequestException $e) {
  2715.                         // echo Psr7\str($e->getRequest());
  2716.                         // if ($e->hasResponse()) {
  2717.                             //     echo Psr7\str($e->getResponse());
  2718.                             // }
  2719.                             if ($e->hasResponse()) {
  2720.                                 $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
  2721.                             }else{
  2722.                                 $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
  2723.                             }
  2724.                         $response->headers->set('Content-Type', 'application/json');
  2725.                         return $response;
  2726.                         exit;
  2727.                     }
  2728.                 }
  2730.             }
  2731.         }
  2735.         # NEXT PAGE ##########################
  2736.         if(count($product_SELECT) >= $step_count) { 
  2737.             $start = $start + $step_count;
  2738.             return $this->redirect('/remove/products_zulauf/'.$start);
  2739.         } else {
  2740.             // $nextPage = false;
  2741.         }
  2743.         
  2744.         # CLEAR CACHE ##########################
  2745.         $this->clear_cache();
  2747.         $sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,`Page`,UpdateDatetime)
  2748.                 VALUES(?,?,?,?)";
  2749.         $stmtt = $conn_shopware->prepare($sql);
  2750.         $stmtt->execute(array( 
  2751.             "remove/products_zulauf",
  2752.             "ENDE",
  2753.             $start,
  2754.             date('Y-m-d H:i:s')
  2755.         ));
  2757.         # DONE
  2758.         $response = new Response(json_encode(array('success' => true)));
  2759.         $response->headers->set('Content-Type', 'application/json');
  2760.         return $response;
  2761.         return $this->render('update/index.html.twig', ["return" => ['success' => "Zulaufartikel geloescht" ]]);
  2762.     }
  2764.     
  2766.     /**
  2767.      * UPDATE IMAGES FROM AKN TO SHOPWARE
  2768.      * 
  2769.      * @Route("/update/images", name="update_images")
  2770.      * 
  2771.      * @return mixed
  2772.      */
  2773.     public function update_images() {
  2774.         die('this was just a test!');
  2775.         // set_time_limit(200);
  2777.         $this->client_connect();
  2778.         $conn_shopware = $this->shop_connect();
  2780.         $media_folder_id = "a7916c4069d04139b299328b8bd5c90c";
  2783.         # GET ALL AKN-PRODUCTS
  2784.         $sql = "SELECT *
  2785.             , articles.material_id as material_id
  2786.             , articles.materialgroup_id as materialgroup_id
  2787.             , storages.id as storage_id
  2788.             -- , CONCAT(storages.article_id, '_' ,storages.id) AS product_number
  2789.             , CONCAT(storages.material_id, '_', storages.materialgroup_id, '_', storages.id) AS product_number
  2790.             , CONCAT(storages.material_id, '_', storages.materialgroup_id) AS product_number_parent
  2791.             , COALESCE(NULLIF(storages.picture, ''), NULLIF(materials.picture, ''), 'default.jpg') AS picture   -- select picture
  2792.             , COALESCE(NULLIF(materials.picture, ''), 'default.jpg') AS picture_parent   -- select picture
  2793.             FROM articles
  2794.             LEFT JOIN materials ON (articles.material_id = materials.material_id AND articles.materialgroup_id = materials.materialgroup_id )
  2795.             -- LEFT JOIN articlegroups ON (articlegroups.articlegroup_id = articles.article_id)
  2796.             LEFT JOIN articlegroups ON (articles.articlegroup_id = articlegroups.articlegroup_id)
  2797.             LEFT JOIN materialgroups ON (materialgroups.materialgroup_id = articles.materialgroup_id)
  2798.             LEFT JOIN surfaces ON (surfaces.surface_id = articles.surface_id AND surfaces.materialgroup_id = articles.materialgroup_id)
  2799.             INNER JOIN storages ON (articles.article_id = storages.article_id)
  2800.             LEFT JOIN storagefaults ON (storages.fault_id = storagefaults.fault_id)
  2801.             WHERE material_name IS NOT NULL
  2802.             AND articles.material_id = 'ASBL'
  2803.             -- AND storages.datetime_last_imported IS NULL -- not imported
  2804.             ORDER BY articles.id ASC
  2805.         ;";
  2808.         $stmt = $conn_shopware->prepare($sql);
  2809.         $stmt->execute();
  2810.         $articles = $stmt->fetchAll();
  2811.         // dd($articles);
  2814.         # REMOVE SUFFIX FROM PICTURE NAME
  2815.         foreach($articles as &$article) {
  2816.             if(strpos($article['picture'], '-##-')) {
  2817.                 $article['picture'] = substr($article['picture'], 0, -5);
  2818.             }
  2819.             // 1603_20181108_110050.jpg-
  2821.         }
  2822.         # END EDIT PICTURE NAME
  2825.         foreach($articles as $article) {
  2826.             # UPDATE PARENT
  2827.             $sql = "SELECT *,
  2828.                 LOWER(HEX(product.id)) as product_id,
  2829.                 JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_picture') AS custom_article_picture,
  2830.                 CONCAT(media.file_name, '.' , media.file_extension) AS picture
  2831.                 FROM product
  2832.                 INNER JOIN product_translation ON (product.id = product_translation.product_id)
  2833.                 LEFT JOIN product_media ON (product.cover = product_media.id)
  2834.                 INNER JOIN media ON (product_media.media_id = media.id)
  2835.                 WHERE JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_material_id') = '".$article['material_id']."'
  2836.                 AND JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_materialgroup_id') = '".$article['materialgroup_id']."'
  2837.                 -- AND JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_materialgroup_stone_number') = '".$article['stone_number']."'
  2838.                 -- AND JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_materialgroup_surface') = '".$article['surface_name']."'
  2839.                 -- AND JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_materialgroup_fault_id') = '".$article['fault_id']."'
  2840.                 -- AND JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_materialgroup_fault_corner') = '".$article['fault_corner']."'
  2841.                 AND product.width = '". floatval($article['width']) ."'
  2842.                 AND product.height = '". floatval($article['thickness']) ."'
  2843.                 AND product.length = '". floatval($article['length']) ."'
  2844.                 AND product.parent_id IS NOT NULL
  2845.                 -- JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_materialgroup_id') = 
  2846.                 -- WHERE product_number LIKE '".$article['product_number']."'
  2847.                 LIMIT 1
  2848.             ;";
  2849.             $stmt = $conn_shopware->prepare($sql);
  2850.             $stmt->execute();
  2851.             $product_SELECT = $stmt->fetchAll();
  2854.             if(isset($product_SELECT) && is_array($product_SELECT) && count($product_SELECT) > 0) {
  2855.                 $product = $product_SELECT[0];
  2857.                 if( strtolower($article['picture']) != strtolower($product['picture']) ) {
  2858.                     # PICTURE HAS TO BE UPDATED ###########################
  2859.                     $basename = $article['picture'];
  2860.                     $pathinfo = pathinfo( $article['picture'] );
  2861.                     $extension = $pathinfo['extension'];
  2862.                     $filename = $pathinfo['filename'];
  2865.                     # CHECK IF PICTURE IS ALREADY IN MEDIAPOOL
  2866.                     try {
  2867.                         $search = $this->client->request('GET', '/api/media?filter[fileName]='.$filename.'', array( 
  2868.                         "headers" => $this->header,
  2869.                         ));
  2870.                     } catch (RequestException $e) {
  2871.                         // echo Psr7\str($e->getRequest());
  2872.                         // if ($e->hasResponse()) {
  2873.                         //     echo Psr7\str($e->getResponse());
  2874.                         // }
  2875.                         if ($e->hasResponse()) {
  2876.                             $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
  2877.                         }else{
  2878.                             $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
  2879.                         }
  2880.                         $response->headers->set('Content-Type', 'application/json');
  2881.                         return $response;
  2882.                         exit;
  2883.                     }
  2884.                     $search_picture = json_decode($search->getBody()->getContents(), true);
  2886.                     if($search_picture['total'] == 0) {
  2887.                         # NEW PICTURE HAS TO BE UPLOADED
  2889.                         # CHECK IF IMAGE FILE EXISTS ON WEBSPACE
  2890.                         $path = '/public/produktbilder/'.$article['picture'];
  2891.                         if( !file_exists( $this->getParameter('kernel.project_dir').$path ) ) continue;
  2894.                         $mediaUrl = trim('https://'.$_SERVER['HTTP_HOST'].'/produktbilder/'.$basename);
  2895.                         $mediaUrl = str_replace(' ', '%20', $mediaUrl);
  2897.                         # UPLOAD PICTURE
  2898.                         $post_media = $this->client->post('/api/media', array(
  2899.                             // "debug" => true,
  2900.                             'allow_redirects'=>array('strict'=>true),
  2901.                             'headers' => $this->header,
  2902.                             'body' => json_encode([
  2903.                                 'mediaFolderId' => $media_folder_id
  2904.                             ])
  2905.                         ));
  2906.                         $location = array_pop( $post_media->getHeaders()['Location'] );
  2907.                         $mediaId = basename($location);
  2908.                         // dd($mediaId);
  2910.                         try {
  2911.                             $post_media = $this->client->request('POST', '/api/_action/media/'.$mediaId.'/upload?extension='.$extension.'&fileName='.$filename, array(
  2912.                                 // "debug" => true,
  2913.                                 'allow_redirects'=>array('strict'=>true),
  2914.                                 'headers' => $this->header,
  2915.                                 'body' => json_encode([
  2916.                                     'url' => $mediaUrl,
  2917.                                     'mediaFolderId' => $media_folder_id,
  2918.                                 ])
  2919.                             ));
  2920.                         } catch (RequestException $e) {
  2921.                             // echo Psr7\str($e->getRequest());
  2922.                             // if ($e->hasResponse()) {
  2923.                             //     echo Psr7\str($e->getResponse());
  2924.                             // }
  2925.                             if ($e->hasResponse()) {
  2926.                                 $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
  2927.                             }else{
  2928.                                 $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
  2929.                             }
  2930.                             $response->headers->set('Content-Type', 'application/json');
  2931.                             return $response;
  2932.                             exit;
  2933.                         }
  2935.                     } else {
  2936.                         # get media Id
  2937.                         try {
  2938.                             // $search = $this->client->request('GET', '/api/media?filter[fileName]='.$articleSW["custom_article_picture"].'', array( 
  2939.                             $search = $this->client->request('GET', '/api/media?filter[fileName]='.$filename.'', array( 
  2940.                                 'headers' => $this->header,
  2941.                             ));
  2942.                         } catch (RequestException $e) {
  2943.                             // echo Psr7\str($e->getRequest());
  2944.                             // if ($e->hasResponse()) {
  2945.                             //     echo Psr7\str($e->getResponse());
  2946.                             // }
  2947.                             if ($e->hasResponse()) {
  2948.                                 $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
  2949.                             }else{
  2950.                                 $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
  2951.                             }
  2952.                             $response->headers->set('Content-Type', 'application/json');
  2953.                             return $response;
  2954.                             exit;
  2955.                         }
  2956.                         $search_picture = json_decode($search->getBody()->getContents(),true);
  2957.                         $mediaId = $search_picture["data"][0]["id"];
  2958.                     }
  2962.                     # ASSIGN IMAGE
  2963.                     try{
  2964.                         $post_product = $this->client->request('POST', '/api/product-media/', array(
  2965.                             // 'debug' => true,
  2966.                             'allow_redirects'=>array('strict'=>true),
  2967.                             'headers' => $this->header,
  2968.                             'body' => json_encode([
  2969.                                 'productId' => $product['product_id'],
  2970.                                 'position' => 1,
  2971.                                 'media' => array(
  2972.                                     'id' => strtolower($mediaId)
  2973.                                 )
  2974.                             ])
  2975.                         ));
  2976.                         $location = array_pop( $post_product->getHeaders()['Location'] );
  2977.                         $productmediaId = basename($location);
  2978.                     } catch (RequestException $e) {
  2979.                         // echo Psr7\str($e->getRequest());
  2980.                         // if ($e->hasResponse()) {
  2981.                         //     echo Psr7\str($e->getResponse());
  2982.                         // }
  2983.                         if ($e->hasResponse()) {
  2984.                             $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
  2985.                         }else{
  2986.                             $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
  2987.                         }
  2988.                         $response->headers->set('Content-Type', 'application/json');
  2989.                         return $response;
  2990.                         exit;
  2991.                     }
  2994.                     # ASSIGN COVER
  2995.                     try{
  2996.                         // var_dump("cover zuordnen");
  2997.                         $post_product = $this->client->request('PATCH', '/api/product/'.$product['product_id'], array(
  2998.                             // 'debug' => true,
  2999.                             'allow_redirects'=>array('strict'=>true),
  3000.                             'headers' => $this->header,
  3001.                             'body' => json_encode([
  3002.                                 'coverId' => strtolower($productmediaId)
  3003.                             ])
  3004.                         ));
  3005.                     } catch (RequestException $e) {
  3006.                         // echo Psr7\str($e->getRequest());
  3007.                         // if ($e->hasResponse()) {
  3008.                         //     echo Psr7\str($e->getResponse());
  3009.                         // }
  3010.                         if ($e->hasResponse()) {
  3011.                             $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
  3012.                         }else{
  3013.                             $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
  3014.                         }
  3015.                         $response->headers->set('Content-Type', 'application/json');
  3016.                         return $response;
  3017.                         exit;
  3018.                     }
  3019.                 }
  3020.             }
  3021.         }
  3022.         dd(__LINE__);
  3025.     }
  3027.     /**
  3028.      * add oder update Bild bei Artikel
  3029.      * 
  3030.      */
  3031.     public function add_image($bildname, $produktId, $zusatzbild)
  3032.     {
  3033.         $this->client_connect();
  3034.         $conn_shopware = $this->shop_connect();
  3035.         if( empty(trim($bildname)) || empty(trim($produktId)) ){
  3036.             return false;
  3037.         }
  3038.         //hole gesetzte Mediafiles im Produkt ab
  3039.         $sql = "SELECT 
  3040.                 *
  3041.                 , LOWER(HEX(product_id)) as product_id
  3042.                 , LOWER(HEX(media_id)) as media_id
  3043.             FROM product_media
  3044.             INNER JOIN media ON (product_media.media_id = media.id)
  3045.             WHERE LOWER(HEX(product_id)) = '".$produktId."'
  3046.             ;";
  3047.         $stmt = $conn_shopware->prepare($sql);
  3048.         $stmt->execute();
  3049.         $mediaSW = $stmt->fetchAll();
  3051.         $bildname = str_replace('"','',$bildname);
  3052.         if(strpos($bildname,"-##-")){
  3053.             $bildname= substr($bildname,0,-5);
  3054.         }
  3055.         $bildname = str_replace('"','',$bildname);
  3056.         $article_file_new = preg_replace("/´|`|&/", ' ', $bildname);
  3057.         $article_file_new = preg_replace("/\s\s+/", ' ', $article_file_new);
  3058.         $article_file_new = str_replace(array('ü','ä','ö','+','ß'),array('ue','ae','oe','_','ss'),$article_file_new);
  3059.         $filename= explode(".", $article_file_new);
  3060.         $type = array_pop($filename);
  3061.         $filename = implode('.', $filename);
  3062.         $filename = trim(preg_replace("/\.+$/",'',$filename));
  3063.    
  3064.         // prüfen ob Bild schon vorhanden ist
  3065.         try {
  3066.             $search = $this->client->request('GET', '/api/media?filter[fileName]='.$filename.'', array( 
  3067.             "headers" => $this->header,
  3068.             ));
  3069.         } catch (RequestException $e) {
  3070.             // echo Psr7\str($e->getRequest());
  3071.             // if ($e->hasResponse()) {
  3072.             //     echo Psr7\str($e->getResponse());
  3073.             // }
  3074.             if ($e->hasResponse()) {
  3075.                 $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
  3076.             }else{
  3077.                 $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
  3078.             }
  3079.             $response->headers->set('Content-Type', 'application/json');
  3080.             return $response;
  3081.             exit;
  3082.         }
  3083.         $search_picture = json_decode($search->getBody()->getContents(),true);
  3085.         if($search_picture["total"] > 0){
  3086.             // return $this->render('update/index.html.twig', ["return" => ['search picture' => $search_picture, 'media url' => $mediaUrl ]]);
  3087.             $Id = $search_picture["data"][0]["id"];
  3088.             if($zusatzbild){
  3089.                 // prüfe ob verknüpfung zwischen bild und produkt existiert, wenn nicht erstelle sie
  3090.                 $sql = "SELECT 
  3091.                         *
  3092.                         , LOWER(HEX(product_id)) as product_id
  3093.                         , LOWER(HEX(media_id)) as media_id
  3094.                     FROM product_media
  3095.                     INNER JOIN media ON (product_media.media_id = media.id)
  3096.                     WHERE LOWER(HEX(product_id)) = '".$produktId."'
  3097.                     AND file_name = '".$filename."'
  3098.                     ;";
  3099.                 $stmt = $conn_shopware->prepare($sql);
  3100.                 $stmt->execute();
  3101.                 $product_media = $stmt->fetchAll();
  3103.                 if(isset($product_media) && !empty($product_media) && count($product_media) > 0){
  3104.                 }else{
  3105.                     try{
  3106.                         $post_product = $this->client->request('POST', '/api/product-media/', array(
  3107.                             // 'debug' => true,
  3108.                             'allow_redirects'=>array('strict'=>true),
  3109.                             'headers' => $this->header,
  3110.                             'body' => json_encode([
  3111.                                 'productId' => $produktId,
  3112.                                 'position' => 2,
  3113.                                 'media' => array(
  3114.                                     'id' => strtolower($Id)
  3115.                                 )
  3116.                             ])
  3117.                         ));
  3118.                         // $location = array_pop( $post_product->getHeaders()['Location'] );
  3119.                         // $productmediaId = basename($location);
  3120.                     } catch (RequestException $e) {
  3121.                         // echo Psr7\str($e->getRequest());
  3122.                         // if ($e->hasResponse()) {
  3123.                         //     echo Psr7\str($e->getResponse());
  3124.                         // }
  3125.                         if ($e->hasResponse()) {
  3126.                             $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
  3127.                         }else{
  3128.                             $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
  3129.                         }
  3130.                         $response->headers->set('Content-Type', 'application/json');
  3131.                         return $response;
  3132.                         exit;
  3133.                     }
  3134.                 }
  3135.             }else{
  3136.                 $sql = "DELETE
  3137.                     FROM product_media
  3138.                     WHERE LOWER(HEX(product_id)) = '".$produktId."'
  3139.                     ;";
  3140.                 $stmt = $conn_shopware->prepare($sql);
  3141.                 $stmt->execute(); 
  3143.                 try{
  3144.                     $post_product = $this->client->request('POST', '/api/product-media/', array(
  3145.                         // 'debug' => true,
  3146.                         'allow_redirects'=>array('strict'=>true),
  3147.                         'headers' => $this->header,
  3148.                         'body' => json_encode([
  3149.                             'productId' => $produktId,
  3150.                             'position' => 1,
  3151.                             'media' => array(
  3152.                                 'id' => strtolower($Id)
  3153.                             )
  3154.                         ])
  3155.                     ));
  3156.                     $location = array_pop( $post_product->getHeaders()['Location'] );
  3157.                     $productmediaId = basename($location);
  3158.                 } catch (RequestException $e) {
  3159.                     // echo Psr7\str($e->getRequest());
  3160.                     // if ($e->hasResponse()) {
  3161.                     //     echo Psr7\str($e->getResponse());
  3162.                     // }
  3163.                     if ($e->hasResponse()) {
  3164.                         $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
  3165.                     }else{
  3166.                         $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
  3167.                     }
  3168.                     $response->headers->set('Content-Type', 'application/json');
  3169.                     return $response;
  3170.                     exit;
  3171.                 }
  3173.                 // ADD COVER                   
  3174.                 try{
  3175.                     // return var_dump("cover zuordnen". $produktId." - ".$productmediaId); 
  3176.                     $post_product = $this->client->request('PATCH', '/api/product/'.$produktId, array(
  3177.                         // 'debug' => true,
  3178.                         'allow_redirects'=>array('strict'=>true),
  3179.                         'headers' => $this->header,
  3180.                         'body' => json_encode([
  3181.                             'coverId' => strtolower($productmediaId)
  3182.                         ])
  3183.                     ));
  3184.                 } catch (RequestException $e) {
  3185.                     // echo Psr7\str($e->getRequest());
  3186.                     // if ($e->hasResponse()) {
  3187.                     //     echo Psr7\str($e->getResponse());
  3188.                     // }
  3189.                     if ($e->hasResponse()) {
  3190.                         $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
  3191.                     }else{
  3192.                         $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
  3193.                     }
  3194.                     $response->headers->set('Content-Type', 'application/json');
  3195.                     return $response;
  3196.                     exit;
  3197.                 }
  3198.             }
  3199.         }
  3200.         return true;
  3201.     }
  3203.     public function clear_cache() {
  3204.         try {
  3205.             $post_product = $this->client->request('DELETE', '/api/_action/cache', array(
  3206.                 //    "debug" => true,
  3207.                 'allow_redirects'=>array('strict'=>true),
  3208.                 "headers" => $this->header,
  3209.             ));
  3210.         
  3211.         } catch (RequestException $e) {
  3212.             // echo Psr7\str($e->getRequest());
  3213.             // if ($e->hasResponse()) {
  3214.             //     echo Psr7\str($e->getResponse());
  3215.             // }
  3216.             if ($e->hasResponse()) {
  3217.                 $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
  3218.             }else{
  3219.                 $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
  3220.             }
  3221.             $response->headers->set('Content-Type', 'application/json');
  3222.             return $response;
  3223.             exit;
  3224.         }
  3225.     }
  3228.     /**
  3229.      * Importiert SQL
  3230.      * 
  3231.      * @Route("/import/sql", name="import_sql")
  3232.      * 
  3233.      * @return mixed
  3234.      */
  3235.     public function import_sql() {
  3236.         $this->client_connect();
  3237.         $conn_akn = $this->dump_connect();
  3238.         // $conn_shopware = $this->shop_connect();
  3239.         
  3240.         // $sql = "INSERT INTO pixel (pixel_datetime, pixel_function, pixel_text)
  3241.         //     VALUES (now(), '".__FUNCTION__."', 'sql import START')
  3242.         // ;";
  3243.         // $stmt = $conn_akn->prepare($sql);
  3244.         // $stmt->execute();
  3245.         
  3246.         
  3247.         $sql_file = $this->getParameter('kernel.project_dir').'/public/sql/import.sql';
  3248.         if( file_exists($sql_file)) {
  3249.             $sql_import = file_get_contents($sql_file);
  3250.             $sql_import = str_replace(["\n", "\r"], "", $sql_import);
  3251.             // $sql_import = str_replace("`", "", $sql_import);
  3252.             // $sql_import = str_replace(", group,", ", `group`,", $sql_import);
  3253.             // $sql_import = str_replace(", index,", ", `index`,", $sql_import);
  3254.             $sql_import = explode(';', $sql_import);
  3255.             
  3256.             foreach($sql_import as $query) {
  3257.                 if( trim($query) != '') {
  3258.                     
  3259.                     # DO SQL STUFF #################
  3260.                     
  3261.                     # SKIP
  3262.                     if( str_starts_with( strtolower( trim( $query ) ), 'use' )) continue;
  3263.                     // if( str_starts_with( strtolower( trim( $query ) ), 'truncate' )) continue;
  3264.                     $sql = $query.';';
  3265.                     // var_dump($query);
  3266.                     $stmt = $conn_akn->prepare($sql);
  3267.                     $stmt->execute();
  3268.                     // $data = $stmt->fetchAll();
  3270.                     # TODO: CATCH ERRORS
  3271.                 }
  3272.             }
  3273.         }
  3275.         // $sql = "INSERT INTO pixel (pixel_datetime, pixel_function, pixel_text)
  3276.         //     VALUES (now(), '".__FUNCTION__."', 'sql import DONE')
  3277.         // ;";
  3278.         // $stmt = $conn_akn->prepare($sql);
  3279.         // $stmt->execute();
  3281.         # DONE
  3282.         return $this->render('update/index.html.twig', ["return" => ['success' => "SQL importiert" ]]);
  3283.         
  3284.     }
  3286.     /**
  3287.      * Import Customers
  3288.      * 
  3289.      * @Route("/import/customers", name="import_customers")
  3290.      * 
  3291.      * @return mixed
  3292.      */
  3293.     public function import_customers() {
  3294.         $this->client_connect();
  3295.         $conn_shopware = $this->shop_connect();
  3296.         $conn_akn = $this->dump_connect();
  3297.         $step_count = 50;
  3299.         $sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,UpdateDatetime)
  3300.                 VALUES(?,?,?)";
  3301.         $stmtt = $conn_shopware->prepare($sql);
  3302.         $stmtt->execute(array( 
  3303.             "import/customers",
  3304.             "Start",
  3305.             date('Y-m-d H:i:s')
  3306.         ));
  3308.         # CONSTANTS
  3309.         $payment_method_id = $this->payment_method_id;   // Nachname
  3310.         // $salutation_id = 'f81f1e5f8dee45b2b719f56879544b08';    // Firma
  3311.         $salutation_id = $this->salutation_id;    // Keine Angabe
  3312.         $group_id = $this->group_id; // Standard Kundengruppe
  3313.         $country_id = $this->country_id; // Deutschland
  3316.         # GET ALL CUSTOMERS
  3317.         $sql = "SELECT *
  3318.         , customers.customer_number as customer_number
  3319.         , customers.email as customer_email
  3320.         , k1.key_text as price_list
  3321.         , k2.key_text as salesman
  3322.         , k3.key_text as salesarea
  3323.         , k4.key_text as tour
  3324.         , k5.key_text as payment_term
  3325.         , k6.key_text as delivery_term
  3326.         , k7.key_text as `group`
  3327.             FROM customers
  3328.             LEFT JOIN contacts ON (customers.customer_number = contacts.customer_number)
  3329.             LEFT JOIN `keys` as k1 ON (k1.key_number = customers.price_list AND k1.key_kind = 7)
  3330.             LEFT JOIN `keys` as k2 ON (k2.key_number = customers.salesman AND k2.key_kind = 8)
  3331.             LEFT JOIN `keys` as k3 ON (k3.key_number = customers.sales_area AND k3.key_kind = 9)
  3332.             LEFT JOIN `keys` as k4 ON (k4.key_number = customers.tour AND k4.key_kind = 52)
  3333.             LEFT JOIN `keys` as k5 ON (k5.key_number = customers.payment_term AND k5.key_kind = 4)
  3334.             LEFT JOIN `keys` as k6 ON (k6.key_number = customers.delivery_term AND k6.key_kind = 5)
  3335.             LEFT JOIN `keys` as k7 ON (k7.key_number = customers.group AND k7.key_kind = 3)
  3336.             WHERE customers.customer_number IS NOT NULL
  3337.             AND customers.street IS NOT NULL AND customers.street !=''
  3338.             AND customers.email IS NOT NULL AND customers.email !='' AND customers.email LIKE '%@%'
  3339.             AND name_1 IS NOT NULL AND name_1 != ''
  3340.             AND upload_done IS NULL
  3341.             -- AND customers.customer_number = '14254' -- #TODO: REMOVE THIS LINE!!!
  3342.             LIMIT ".$step_count."
  3343.         ;";
  3344.         $stmt = $conn_akn->prepare($sql);
  3345.         $stmt->execute();
  3346.         $customers_SELECT = $stmt->fetchAll();
  3348.         // return $this->render('update/index.html.twig', ["return" => [ 'zeit' => $customers_SELECT]]);
  3350.         foreach($customers_SELECT as $customer) {
  3351.             $CustomerFlat = new \App\Controller\shopware\CustomerFlat();
  3352.             $CustomerFlat->setActive(true);
  3354.             // $first_name = $customer['name_1'].' '.$customer['name_2'].' '.$customer['name_3'];
  3355.             // $last_name = 'Import-neu';
  3356.             // $company = '-';
  3357.             $first_name = $customer['name_1'];
  3358.             if(isset($customer['name_2']) && !empty($customer['name_2']) && $customer['name_2'] != null){
  3359.                 $last_name = $customer['name_2'];
  3360.             }else{
  3361.                 $last_name = "-";
  3362.             }
  3363.             if(isset($customer['name_3']) && !empty($customer['name_3']) && $customer['name_3'] != null){
  3364.                 $company = $customer['name_3'];
  3365.             }else{
  3366.                 $company = "-";
  3367.             }
  3369.             // Erzeuge Titel wenn noch nicht vorhanden
  3370.             if(isset($customer["title"]) && !empty($customer["title"]) && $customer["title"] != null){
  3371.                 $sql = "SELECT *
  3372.                     , LOWER(HEX(salutation_id)) as salutation_id
  3373.                     FROM salutation_translation
  3374.                     WHERE LOWER(HEX(language_id)) = '".$this->language_id."'
  3375.                     AND display_name = '".trim($customer["title"])."'
  3376.                     ;";
  3377.                 $stmt = $conn_shopware->prepare($sql);
  3378.                 $stmt->execute();
  3379.                 $salutation_SELECT = $stmt->fetchAll();
  3380.                     
  3381.                 if(isset($salutation_SELECT) && is_array($salutation_SELECT) && count($salutation_SELECT) > 0){
  3382.                     // wenns gesetzt ist 
  3383.                     $salutation_id = $salutation_SELECT[0]["salutation_id"];
  3384.                 }else{
  3385.                     // generiere salutation und setze id
  3386.                     try {
  3387.                         $post_customer = $this->client->request('POST', '/api/salutation', array(
  3388.                             // 'debug' => true,
  3389.                             'allow_redirects'=>array('strict'=>true),
  3390.                             'headers' => $this->header,
  3391.                             'body' => json_encode([
  3392.                                 'salutationKey' => strtolower($customer["title"]),
  3393.                                 'displayName' => $customer["title"],
  3394.                                 'letterName' => " "
  3395.                             ])
  3396.                         ));
  3397.                         $location = array_pop($post_customer->getHeaders()['Location'] );
  3398.                         $salutation_id = basename($location);
  3399.                     } catch (RequestException $e) {
  3400.                         // echo Psr7\str($e->getRequest());
  3401.                         // if ($e->hasResponse()) {
  3402.                         //     echo Psr7\str($e->getResponse());
  3403.                         // }
  3404.                         if ($e->hasResponse()) {
  3405.                             $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
  3406.                         }else{
  3407.                             $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
  3408.                         }
  3409.                         $response->headers->set('Content-Type', 'application/json');
  3410.                         return $response;
  3411.                         exit;
  3412.                     }
  3413.                 }
  3414.             }else{
  3415.                 $salutation_id = $this->salutation_id;    // Keine Angabe
  3416.             }
  3418.             // Erzeuge Standardzahlungsart wenn noch nicht vorhanden
  3419.             if(isset($customer['payment_term']) && !empty($customer['payment_term']) && $customer['payment_term'] != null){
  3420.                 $sql = "SELECT *
  3421.                     , LOWER(HEX(payment_method_id)) as payment_method_id
  3422.                     FROM payment_method_translation
  3423.                     WHERE LOWER(HEX(language_id)) = '".$this->language_id."'
  3424.                     AND name = '".trim($customer['payment_term'])."'
  3425.                     ;";
  3426.                 $stmt = $conn_shopware->prepare($sql);
  3427.                 $stmt->execute();
  3428.                 $payment_SELECT = $stmt->fetchAll();
  3429.                     
  3430.                 if(isset($payment_SELECT) && is_array($payment_SELECT) && count($payment_SELECT) > 0){
  3431.                     // wenns gesetzt ist 
  3432.                     $payment_method_id = $payment_SELECT[0]["payment_method_id"];
  3433.                 }else{
  3434.                     // generiere zahlungsart und setze id
  3435.                     try {
  3436.                         $post_customer = $this->client->request('POST', '/api/payment-method', array(
  3437.                             // 'debug' => true,
  3438.                             'allow_redirects'=>array('strict'=>true),
  3439.                             'headers' => $this->header,
  3440.                             'body' => json_encode([
  3441.                                 'name' => trim($customer['payment_term']),
  3442.                                 'active' => true
  3443.                             ])
  3444.                         ));
  3445.                         $location = array_pop( $post_customer->getHeaders()['Location'] );
  3446.                         $payment_method_id = basename($location);
  3447.                     } catch (RequestException $e) {
  3448.                         // echo Psr7\str($e->getRequest());
  3449.                         // if ($e->hasResponse()) {
  3450.                         //     echo Psr7\str($e->getResponse());
  3451.                         // }
  3452.                         if ($e->hasResponse()) {
  3453.                             $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
  3454.                         }else{
  3455.                             $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
  3456.                         }
  3457.                         $response->headers->set('Content-Type', 'application/json');
  3458.                         return $response;
  3459.                         exit;
  3460.                     }
  3462.                     $sql = "INSERT INTO sales_channel_payment_method (sales_channel_id, payment_method_id)
  3463.                         VALUES (UNHEX('".$this->saleschannel_id."'), UNHEX('".$payment_method_id."'))
  3464.                     ;";
  3465.                     $stmt = $conn_shopware->prepare($sql);
  3466.                     $stmt->execute();
  3467.                    
  3468.                 }
  3469.             }else{
  3470.                 $payment_method_id = $this->payment_method_id;    // Rechnung
  3471.             }
  3472.             // Ende Standardzahlungsart
  3474.             // // Erzeuge Standardlieferart wenn noch nicht vorhanden
  3475.             // if(isset($customer['payment_term']) && !empty($customer['payment_term']) && $customer['payment_term'] != null){
  3476.             //     $sql = "SELECT *
  3477.             //         , LOWER(HEX(payment_method_id)) as payment_method_id
  3478.             //         FROM payment_method_translation
  3479.             //         WHERE LOWER(HEX(language_id)) = '".$this->language_id."'
  3480.             //         AND name = '".trim($customer['payment_term'])."'
  3481.             //         ;";
  3482.             //     $stmt = $conn_shopware->prepare($sql);
  3483.             //     $stmt->execute();
  3484.             //     $payment_SELECT = $stmt->fetchAll();
  3485.                     
  3486.             //     if(isset($payment_SELECT) && is_array($payment_SELECT) && count($payment_SELECT) > 0){
  3487.             //         // wenns gesetzt ist 
  3488.             //         $payment_method_id = $payment_SELECT[0]["payment_method_id"];
  3489.             //     }else{
  3490.             //         // generiere zahlungsart und setze id
  3491.             //         try {
  3492.             //             $post_customer = $this->client->request('POST', '/api/payment-method', array(
  3493.             //                 // 'debug' => true,
  3494.             //                 'allow_redirects'=>array('strict'=>true),
  3495.             //                 'headers' => $this->header,
  3496.             //                 'body' => json_encode([
  3497.             //                     'name' => trim($customer['payment_term']),
  3498.             //                     'active' => true
  3499.             //                 ])
  3500.             //             ));
  3501.             //             $location = array_pop( $post_customer->getHeaders()['Location'] );
  3502.             //             $payment_method_id = basename($location);
  3503.             //         } catch (RequestException $e) {
  3504.             //             echo Psr7\str($e->getRequest());
  3505.             //             if ($e->hasResponse()) {
  3506.             //                 echo Psr7\str($e->getResponse());
  3507.             //             }
  3508.             //             exit;
  3509.             //         }
  3511.             //         $sql = "INSERT INTO sales_channel_payment_method (sales_channel_id, payment_method_id)
  3512.             //             VALUES (UNHEX('f8c383f75225488a8f6b89b7d64d6cf2'), UNHEX('".$payment_method_id."'))
  3513.             //         ;";
  3514.             //         $stmt = $conn_shopware->prepare($sql);
  3515.             //         $stmt->execute();
  3516.                     
  3517.             //     }
  3518.             // }else{
  3519.             //     $payment_method_id = $this->payment_method_id;    // Rechnung
  3520.             // }
  3521.             // // Ende Standardlieferart
  3523.             // Erzeuge Kundengruppe wenn noch nicht vorhanden
  3524.             if(isset($customer['group']) && !empty($customer['group']) && $customer['group'] != null){
  3525.                 $sql = "SELECT *
  3526.                     , LOWER(HEX(customer_group_id)) as customer_group_id
  3527.                     FROM customer_group_translation
  3528.                     WHERE LOWER(HEX(language_id)) = '".$this->language_id."'
  3529.                     AND name = '".trim($customer['group'])."'
  3530.                     ;";
  3531.                 $stmt = $conn_shopware->prepare($sql);
  3532.                 $stmt->execute();
  3533.                 $customer_group_SELECT = $stmt->fetchAll();
  3534.                     
  3535.                 if(isset($customer_group_SELECT) && is_array($customer_group_SELECT) && count($customer_group_SELECT) > 0){
  3536.                     // wenns gesetzt ist 
  3537.                     $group_id = $customer_group_SELECT[0]["customer_group_id"];
  3538.                 }else{
  3539.                     // generiere kundengruppe und setze id
  3540.                     try {
  3541.                         $post_customer = $this->client->request('POST', '/api/customer-group', array(
  3542.                             // 'debug' => true,
  3543.                             'allow_redirects'=>array('strict'=>true),
  3544.                             'headers' => $this->header,
  3545.                             'body' => json_encode([
  3546.                                 'name' => trim($customer['group'])
  3547.                             ])
  3548.                         ));
  3549.                         $location = array_pop( $post_customer->getHeaders()['Location'] );
  3550.                         $group_id = basename($location);
  3551.                     } catch (RequestException $e) {
  3552.                         // echo Psr7\str($e->getRequest());
  3553.                         // if ($e->hasResponse()) {
  3554.                         //     echo Psr7\str($e->getResponse());
  3555.                         // }
  3556.                         if ($e->hasResponse()) {
  3557.                             $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
  3558.                         }else{
  3559.                             $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
  3560.                         }
  3561.                         $response->headers->set('Content-Type', 'application/json');
  3562.                         return $response;
  3563.                         exit;
  3564.                     }
  3565.                 }
  3566.             }else{
  3567.                 $group_id = $this->group_id;    // Default Kundengruppe
  3568.             }
  3569.             // Ende Kundengruppe
  3571.             // return $this->render('update/index.html.twig', ["return" => [ 'zeit' => $group_id]]);
  3573.             # GET LANGUAGE ID
  3574.             $language_name = ( trim($customer['country_indicator']) == '' || $customer['country_indicator'] == 'DE') ? 'Deutsch' : 'English';
  3575.             $sql = "SELECT LOWER(HEX(id)) AS language_id, language.name
  3576.                 FROM language
  3577.                 WHERE language.name = '".$language_name."'
  3578.             ;";
  3579.             $stmt = $conn_shopware->prepare($sql);
  3580.             $stmt->execute();
  3581.             $language_SELECT = $stmt->fetchAll();
  3582.             $language_id = $language_SELECT[0]['language_id']??$this->language_id;
  3584.             $CustomerFlat->setLanguageId($language_id);
  3585.             # END GET LANGUAGE ID
  3587.             # THIS VALUES SHOULD NOT BE BLANK:
  3588.             $CustomerFlat->setGroupId($group_id);
  3589.             $CustomerFlat->setDefaultPaymentMethodId($payment_method_id);
  3590.             $CustomerFlat->setSalesChannelId($this->saleschannel_id);
  3591.             // $CustomerFlat->setDefaultBillingAddressId();
  3592.             // $CustomerFlat->setDefaultShippingAddressId();
  3593.             $CustomerFlat->setSalutationId($salutation_id);
  3594.             $CustomerFlat->setCustomerNumber($customer['customer_number']);
  3595.             $CustomerFlat->setLastName($last_name);
  3596.             # END THIS VALUES SHOULD NOT BE BLANK:
  3597.             
  3599.             $CustomerFlat->setFirstName(trim($first_name));
  3600.             if(strpos($customer['email'],"@")){
  3601.                 $CustomerFlat->setEmail($customer['email']);
  3602.             }elseif(strpos($customer['customer_email'],"@")){
  3603.                 $CustomerFlat->setEmail($customer['customer_email']);
  3604.             }else{
  3605.                  # UPDATE FAILED BECAUSE EMAIL IS INVALID
  3606.                 $sqlt = "UPDATE customers
  3607.                         SET upload_done = 1, upload_failed = 1
  3608.                         WHERE customer_number = '".$customer['customer_number']."'
  3609.                         ;";
  3610.                 $stmtt = $conn_akn->prepare($sqlt);
  3611.                 $stmtt->execute();
  3612.                 continue;
  3613.             }
  3614.             $CustomerFlat->setCompany($company);
  3617.             # CUSTOM FIELDS
  3618.             $CustomerFlat->setCustomFields(array(
  3619.                 'custom_customer_telefon_1' => $customer['telefon_1']??'',
  3620.                 'custom_customer_telefon_2' => $customer['telefon_2']??'',
  3621.                 'custom_customer_fax' => $customer['fax']??'',
  3622.                 'custom_customer_homepage' => $customer['homepage']??'',
  3623.                 'custom_customer_payment_term' => $customer['payment_term']??'',
  3624.                 'custom_customer_delivery_term' => $customer['delivery_term']??'',
  3625.                 'custom_customer_price_list' => $customer['price_list']??'',
  3626.                 'custom_customer_salesman' => $customer['salesman']??'',
  3627.                 'custom_customer_salesarea' => $customer['salesarea']??'',
  3628.                 'custom_customer_tour' => $customer['tour']??'',
  3629.                 'custom_customer_grouping' => $customer['grouping']??'',
  3630.             ));
  3631.             # END CUSTOM FIELDS
  3635.             # LIEFERADRESSE
  3636.             $CustomerShippingAddressFlat = new \App\Controller\shopware\CustomerAddressFlat();
  3637.             # GET COUNTRY ID
  3638.             if(trim($customer['country_indicator']) == '') $customer['country_indicator'] = 'DE';
  3639.             $sql = "SELECT LOWER(HEX(id)) AS country_id, country.iso
  3640.                 FROM country
  3641.                 -- WHERE country.iso = 'DE'
  3642.                 WHERE country.iso = '" . $customer['country_indicator'] . "'
  3643.             ;";
  3644.             $stmt = $conn_shopware->prepare($sql);
  3645.             $stmt->execute();
  3646.             $country_SELECT = $stmt->fetchAll();
  3647.             if(!isset($country_SELECT[0]['country_id']) || empty($country_SELECT[0]['country_id']) || $country_SELECT[0]['country_id'] == ''){
  3648.                 // dd($customer);
  3649.                  # UPDATE FAILED BECAUSE COUNTRY CODE IS INVALID
  3650.                  $sqlt = "UPDATE customers
  3651.                         SET upload_done = 1, upload_failed = 2
  3652.                         WHERE customer_number = '".$customer['customer_number']."'
  3653.                         ;";
  3654.                 $stmtt = $conn_akn->prepare($sqlt);
  3655.                 $stmtt->execute();
  3656.                 continue;
  3657.             }
  3658.             $country_id = $country_SELECT[0]['country_id']??$country_id;
  3660.             $CustomerShippingAddressFlat->setCountryId(strtolower($country_id));
  3661.             $CustomerShippingAddressFlat->setSalutationId($salutation_id);
  3662.             $CustomerShippingAddressFlat->setFirstName(trim($first_name));
  3663.             $CustomerShippingAddressFlat->setLastName($last_name);
  3664.             $CustomerShippingAddressFlat->setCompany($company);
  3665.             $CustomerShippingAddressFlat->setZipcode($customer['postcode']);
  3666.             $CustomerShippingAddressFlat->setCity($customer['place']);
  3667.             $CustomerShippingAddressFlat->setStreet($customer['street']);
  3668.             $CustomerShippingAddressFlat->setPhoneNumber($customer['telefon_1']);
  3669.             # END LIEFERADRESSE
  3673.             # CHECK IF CUSTOMER ALREADY EXISTS
  3674.             $sql = "SELECT LOWER(HEX(id)) AS customer_id
  3675.                 FROM customer
  3676.                 WHERE customer_number = '".$customer['customer_number']."'
  3677.                 LIMIT 1
  3678.             ;";
  3680.             $stmt = $conn_shopware->prepare($sql);
  3681.             $stmt->execute();
  3682.             $shopware_customer_SELECT = $stmt->fetchAll();
  3685.             if(!(isset($shopware_customer_SELECT) && is_array($shopware_customer_SELECT) && count($shopware_customer_SELECT) > 0)) {
  3686.                 # INSERT ##########################
  3687.                 $CustomerFlat->setDefaultShippingAddress($CustomerShippingAddressFlat);
  3688.                 // $CustomerFlat->setDefaultBillingAddress($CustomerShippingAddressFlat);
  3690.                 try {
  3691.                     $post_customer = $this->client->request('POST', '/api/customer', array(
  3692.                         // 'debug' => true,
  3693.                         'allow_redirects'=>array('strict'=>true),
  3694.                         'headers' => $this->header,
  3695.                         'body' => $CustomerFlat
  3696.                     ));
  3697.                 } catch (RequestException $e) {
  3698.                     // echo Psr7\str($e->getRequest());
  3699.                     // if ($e->hasResponse()) {
  3700.                     //     echo Psr7\str($e->getResponse());
  3701.                     // }
  3702.                     if ($e->hasResponse()) {
  3703.                         $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
  3704.                     }else{
  3705.                         $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
  3706.                     }
  3707.                     $response->headers->set('Content-Type', 'application/json');
  3708.                     return $response;
  3709.                     exit;
  3710.                 }
  3712.             } else {
  3713.                 $customer_id = strtolower($shopware_customer_SELECT[0]['customer_id']);
  3714.                 # UPDATE ##########################
  3715.                 try {
  3716.                     $post_product = $this->client->request('PATCH', '/api/customer/'.$customer_id.'', array(
  3717.                         "headers" => $this->header,
  3718.                         "body" => $CustomerFlat
  3719.                     ));
  3720.                 } catch (RequestException $e) {
  3721.                     // echo Psr7\str($e->getRequest());
  3722.                     // if ($e->hasResponse()) {
  3723.                     //     echo Psr7\str($e->getResponse());
  3724.                     // }
  3725.                     if ($e->hasResponse()) {
  3726.                         $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
  3727.                     }else{
  3728.                         $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
  3729.                     }
  3730.                     $response->headers->set('Content-Type', 'application/json');
  3731.                     return $response;
  3732.                     exit;
  3733.                 }
  3734.                 # UPDATE Adresse ##########################
  3735.                 # GET ADRESS-ID
  3736.                 $sql = "SELECT LOWer(HEX(id)) AS address_id
  3737.                     FROM customer_address
  3738.                     WHERE LOWER(HEX(customer_id)) = '".$customer_id."'
  3739.                 ;";
  3740.                 
  3741.                 $stmt = $conn_shopware->prepare($sql);
  3742.                 $stmt->execute();
  3743.                 $address_SELECT = $stmt->fetchAll();
  3745.                 if(isset($address_SELECT) && is_array($address_SELECT) && count($address_SELECT) > 0) {
  3746.                     $addresses = $address_SELECT;
  3748.                     # UPDATE Adresses with new values
  3749.                     foreach( $addresses as $address) {
  3750.                         $address_id = $address['address_id'];
  3752.                         try {
  3753.                             $post_product = $this->client->request('PATCH', '/api/customer/'.$customer_id.'/addresses/'.$address_id.'', array(
  3754.                                 "headers" => $this->header,
  3755.                                 "body" => $CustomerShippingAddressFlat
  3756.                             ));
  3757.                         } catch (RequestException $e) {
  3758.                             // echo Psr7\str($e->getRequest());
  3759.                             // if ($e->hasResponse()) {
  3760.                             //     echo Psr7\str($e->getResponse());
  3761.                             // }
  3762.                             if ($e->hasResponse()) {
  3763.                                 $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
  3764.                             }else{
  3765.                                 $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
  3766.                             }
  3767.                             $response->headers->set('Content-Type', 'application/json');
  3768.                             return $response;
  3769.                             exit;
  3770.                         }
  3771.                     }
  3772.                 }
  3774.             }
  3777.             # UPDATE DATETIME ##########################
  3778.             $sqlt = "UPDATE customers
  3779.                 -- SET datetime_last_imported = '".date('Y-m-d H:i:s')."'
  3780.                 SET upload_done = 1
  3781.                 WHERE customer_number = '".$customer['customer_number']."'
  3782.             ;";
  3783.             $stmtt = $conn_akn->prepare($sqlt);
  3784.             $stmtt->execute();
  3786.             // dd(__LINE__);
  3787.         }
  3790.         # NEXT PAGE ##########################
  3791.         if(count($customers_SELECT) >= $step_count) { 
  3792.             // $nextPage = true;
  3793.             return $this->redirect('/import/customers');
  3794.         } else {
  3795.             // $nextPage = false;
  3796.         }
  3798.         // setze 
  3799.         $sqlt = "UPDATE customers
  3800.                 SET upload_done = 1, upload_failed = 1
  3801.                 WHERE customer_number = ''
  3802.                 OR (upload_done IS NULL AND upload_failed IS NULL)
  3803.                 ;";
  3804.         $stmtt = $conn_akn->prepare($sqlt);
  3805.         $stmtt->execute();
  3807.         # CLEAR CACHE ##########################
  3808.         $this->clear_cache();
  3810.         $sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,UpdateDatetime)
  3811.                 VALUES(?,?,?)";
  3812.         $stmtt = $conn_shopware->prepare($sql);
  3813.         $stmtt->execute(array( 
  3814.             "import/customers",
  3815.             "ENDE",
  3816.             date('Y-m-d H:i:s')
  3817.         ));
  3819.         # DONE ##########################
  3820.         $response = new Response(json_encode(array('success' => true)));
  3821.         $response->headers->set('Content-Type', 'application/json');
  3822.         return $response;
  3823.         return $this->render('update/index.html.twig', ["return" => ['success' => "Kunden importiert" ]]);
  3824.     }
  3830.     /**
  3831.      * update_categories
  3832.      * 
  3833.      * @Route("/update/categories", name="update_categories")
  3834.      * @Route("/update/categories/{start}")
  3835.      * 
  3836.      * @return mixed
  3837.      */
  3838.     public function update_categories($start = 0) {
  3840.         $this->client_connect();
  3841.         $conn_shopware = $this->shop_connect();
  3842.         $step_count = 3;
  3845.         # GET ALL CATEGORIES
  3846.         $sql = "SELECT LOWER(HEX(id)) AS category_id, category_translation.name, active
  3847.             FROM category
  3848.             INNER JOIN category_translation ON (category.id = category_translation.category_id)
  3849.             WHERE LOWER(HEX(parent_id)) = '".$this->default_category_id."'
  3850.             LIMIT ".$step_count."
  3851.             OFFSET ".$start."
  3852.             ;";
  3853.             // -- WHERE LOWER(HEX(parent_id)) = '".$this->steine_aus_aller_welt_category_id."'
  3855.         $stmt = $conn_shopware->prepare($sql);
  3856.         $stmt->execute();
  3857.         $categories_SELECT = $stmt->fetchAll();
  3858.         // echo "<pre>"; var_dump($categories_SELECT); exit;
  3860.         if(isset($categories_SELECT) && is_array($categories_SELECT) && count($categories_SELECT) > 0) {
  3861.             $categories = $categories_SELECT;
  3863.             foreach($categories as $category) {
  3864.                 # SELECT PRODUCTS WITH CATEGORY
  3865.                 $sql = "SELECT LOWER(HEX(id)) AS category_id, category_translation.name
  3866.                     FROM category
  3867.                     INNER JOIN category_translation ON (category.id = category_translation.category_id)
  3868.                     WHERE LOWER(HEX(parent_id)) = '".$this->default_category_id."'
  3869.                 ;";
  3870.                 // -- WHERE LOWER(HEX(parent_id)) = '".$this->steine_aus_aller_welt_category_id."'
  3872.                 $sql = "SELECT product_number
  3873.                     FROM product
  3874.                     INNER JOIN product_category ON (product.id = product_category.product_id)
  3875.                     INNER JOIN category ON (product_category.category_id = category.id)
  3876.                     WHERE LOWER(HEX(category.id)) = '".$category['category_id']."'
  3877.                     LIMIT 3
  3878.                 ;";
  3879.         
  3880.                 $stmt = $conn_shopware->prepare($sql);
  3881.                 $stmt->execute();
  3882.                 $products_SELECT = $stmt->fetchAll();
  3884.                 # IF NO PRODUCT DISABLE CATEGORY
  3885.                 $CategoryFlat = new \App\Controller\shopware\CategoryFlat();
  3887.                 // if (isset($products_SELECT) && is_array($products_SELECT) && count($products_SELECT) > 0) {
  3888.                 // }
  3889.                 // if ($category["category_id"] == "91bb5a08283d40a3bf1ec0a4759660b1") {
  3890.                 //     echo "<pre>"; var_dump($category); var_dump($products_SELECT); exit;
  3891.                 // }
  3893.                 $commit = false;
  3894.                 if(isset($products_SELECT) && is_array($products_SELECT) && count($products_SELECT) > 0) {
  3895.                     # ACTIVATE
  3896.                     if ($category["active"] == 0) {
  3897.                         $CategoryFlat->setActive(true);
  3898.                         $commit = true;
  3899.                     }
  3900.                 } else {
  3901.                     # DISABLE
  3902.                     if ($category["active"] == 1) {
  3903.                         $CategoryFlat->setActive(false);
  3904.                         $commit = true;
  3905.                     }
  3906.                 }
  3907.                 // $CategoryFlat->setActive(true);
  3910.                 if ($commit == true) {
  3911.                     try {
  3912.                         $post_product = $this->client->request('PATCH', '/api/category/'.$category['category_id'], array(
  3913.                             //    "debug" => true,
  3914.                             'allow_redirects'=>array('strict' => true),
  3915.                             'headers' => $this->header,
  3916.                             'body' => $CategoryFlat,
  3917.                         ));
  3918.                     } catch (RequestException $e) {
  3919.                         // echo Psr7\str($e->getRequest());
  3920.                         // if ($e->hasResponse()) {
  3921.                         //     echo Psr7\str($e->getResponse());
  3922.                         // }
  3923.                         if ($e->hasResponse()) {
  3924.                             $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
  3925.                         }else{
  3926.                             $response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
  3927.                         }
  3928.                         $response->headers->set('Content-Type', 'application/json');
  3929.                         return $response;
  3930.                         exit;
  3931.                     }
  3932.                 }
  3933.             }
  3934.         }
  3936.         # NEXT PAGE ##########################
  3937.         if(count($categories_SELECT) >= $step_count) { 
  3938.             $start = $start + $step_count;
  3939.             return $this->redirect('/update/categories/'. $start);
  3940.         }
  3942.         # CLEAR CACHE ##########################
  3943.         $this->clear_cache();
  3945.         # DONE ##########################
  3947.         return $this->redirect('/categories/visibility');
  3949.         // $response = new Response(json_encode(array('success' => true)));
  3950.         // $response->headers->set('Content-Type', 'application/json');
  3951.         // return $response;
  3952.         
  3953.         // return $this->render('update/index.html.twig', ["return" => ['success' => "Kunden importiert" ]]);
  3955.     }
  3960.     /**
  3961.      * Pixelproductions testet
  3962.      * 
  3963.      * @Route("/pixel_test", name="pixel_test")
  3964.      * 
  3965.      * @return mixed
  3966.      */
  3967.     public function pixel_test() {
  3968.         die('No testing!');
  3970.         $directory = $this->getParameter('kernel.project_dir').'/public/produktbilder';
  3971.         $files = array_values(array_diff( scandir($directory), array('.','..')));
  3973.         $test_file = $files[0];     // 2319_P1030278 Nero Impala Rustenburg geflammt 2´cm 218070548.jpg
  3974.         // $test_file = html_entity_decode($test_file, ENT_QUOTES);
  3976.         // $test_file = preg_replace('/(\'|&#0*39;)/', '', $test_file);
  3978.         // $test_file = preg_replace("/´/", '', $test_file);
  3979.         $test_file = '2319_P1030278 Nero Impala Rustenburg geflammt 2´cm 218070548.jpg';
  3980.         $new_filename = preg_replace("/´/", '', $test_file);
  3981.         dd($new_filename);
  3983.     }
  3986.     /**
  3987.      * Pixelproductions testet
  3988.      * 
  3989.      * @Route("/pixel_mail", name="pixel_mail")
  3990.      * 
  3991.      * @return mixed
  3992.      */
  3993.     public function sendEmail(MailerInterface $mailer): Response {
  3995.         die('No EMails!');
  3997.         // var_dump( urlencode('bN+y6M=T8b4s') ); exit;    // bN%2By6M%3DT8b4s
  3998.         // var_dump( urlencode('helpdesk@pixelproductions.de') ); exit;    // helpdesk%40pixelproductions.de
  4002.         $email = (new Email())
  4003.             ->from('cw@pixelproductions.de')
  4004.             ->to('mo@pixelproductions.de')
  4005.             //->cc('cc@example.com')
  4006.             //->bcc('bcc@example.com')
  4007.             //->replyTo('fabien@example.com')
  4008.             //->priority(Email::PRIORITY_HIGH)
  4009.             ->subject('Time for Symfony Mailer!')
  4010.             ->text('Sending emails is fun again!')
  4011.             ->html('<p>See Twig integration for better HTML integration!</p>');
  4013.         $mailer->send($email);
  4015.         // ...
  4016.     }
  4018.  
  4022. }