<?php
namespace App\Controller;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Annotation\Route;
use GuzzleHttp\Psr7;
use GuzzleHttp\Client;
use App\Controller\shopware\ProductFlat;
use GuzzleHttp\Exception\RequestException;
use stdClass;
use Symfony\Component\HttpFoundation\Request;
// use Symfony\Component\HttpFoundation\Response;
// use Symfony\Component\Routing\Annotation\Route;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\Validator\Constraints\DateTime;
use Symfony\Component\HttpFoundation\RedirectResponse;
// use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
# E-MAIL
use Symfony\Component\Mailer\MailerInterface;
use Symfony\Component\Mime\Email;
class ImportController2 extends AbstractController
{
private $client = NULL;
private $header = NULL;
private $base_url; // Shop URL
private $client_id; // SW ID
private $client_secret; // SW Secret
private $client_username; // SW Username
private $client_password; // SW Password
private $shopware_data; // Shopware Datensatz
private $shopware_data_live; // Shopware Datenbank LIVE
private $shopware_data_dump; // AKN Datenbank Dump
private $shopware_data_dump_live; // AKN Datenbank Dump LIVE
private $surface_id; // Eigenschaften Gruppe ID Oberfläche
private $materialgroup_id; // Eigenschaften Gruppe ID Materialgruppe
private $material_id; // Eigenschaften Gruppe ID Material
private $articlegroup_id; // Eigenschaften Gruppe ID Artikelgruppe
private $zulauf_id; // Kategorie ID Zulauf Bald Verfügbar
private $default_category_id; // Default Kategorie ID
private $category_layout_id; // Default Kategorie Layout ID
private $color_id; // Eigenschaften Gruppe ID Farbe
private $fault_exists_id; // Eigenschaften Gruppe ID Fehler vorhanden
private $thickness_id; // Eigenschaften Gruppe ID Materialstärke
private $length_id; // Eigenschaften Gruppe ID Länge
private $width_id; // Eigenschaften Gruppe ID Höhe
private $amount_id; // Eigenschaften Gruppe ID Anzahl
private $surfacearea_id; // Eigenschaften Gruppe ID Quadratmeter
private $meter_id; // Eigenschaften Gruppe ID Meter
private $stonenumber_id; // Eigenschaften Gruppe ID Steinnummer
private $sa_id; // Eigenschaften Gruppe ID Sa
private $squaremeterprice_id; // Eigenschaften Gruppe ID Quadratmeterpreis
private $tax_id; // ID der Defaultsteuern
private $currency_id; //ID der Defaultwährung
private $deliverytime_id; //ID der Defaultlieferzeit
private $saleschannel_id; //ID der Default Verkaufskanal
private $salutation_id; // ID der Default Ansprache (Keine Angabe)
private $payment_method_id; // ID der Default Zahlungsmethode (Nachname)
private $group_id; // ID der Default Kundengruppe
private $country_id; // ID des Default Landes (Deutschland)
private $default_image_filename; // Default Bildername
private $language_id; // DEUTSCH
// private $steine_aus_aller_welt_category_id = 'a9bb98a9e33e4a969b71f48bd1702ab7'; // ID der Parent-Kategorie "Steine aus aller Welt"
function __construct() {
$this->base_url = $_ENV['base_url']; // Base URL
if(isset($_ENV['client_id'])){
$this->client_id = $_ENV['client_id']; // SW ID
}
if(isset($_ENV['client_secret'])){
$this->client_secret = $_ENV['client_secret']; // SW Secret
}
if(isset($_ENV['client_username'])){
$this->client_username = $_ENV['client_username']; // SW Username
}
if(isset($_ENV['client_password'])){
$this->client_password = $_ENV['client_password']; // SW Password
}
$this->shopware_data = $_ENV['shopware_data']; // Shopware Datensatz
$this->shopware_data_live = $_ENV['shopware_data_live']; // Shopware Datenbank LIVE
$this->shopware_data_dump = $_ENV['shopware_data_dump']; // AKN Datenbank Dump
$this->shopware_data_dump_live = $_ENV['shopware_data_dump_live']; // AKN Datenbank Dump LIVE
$this->surface_id = $_ENV['surface_id']; // Eigenschaften Gruppe ID Oberfläche
$this->materialgroup_id = $_ENV['materialgroup_id']; // Eigenschaften Gruppe ID Materialgruppe
$this->material_id = $_ENV['material_id']; // Eigenschaften Gruppe ID Material
$this->articlegroup_id = $_ENV['articlegroup_id']; // Eigenschaften Gruppe ID Artikelgruppe
$this->zulauf_id = $_ENV['zulauf_id']; // Kategorie ID Zulauf Bald Verfügbar
$this->default_category_id = $_ENV['default_category_id']; // Default Kategorie ID
$this->category_layout_id = $_ENV['category_layout_id']; // Default Kategorie Layout ID
$this->color_id = $_ENV['color_id']; // Eigenschaften Gruppe ID Farbe
$this->fault_exists_id = $_ENV['fault_exists_id']; // Eigenschaften Gruppe ID Fehler vorhanden
$this->thickness_id = $_ENV['thickness_id']; // Eigenschaften Gruppe ID Materialstärke
$this->blocknumber_id = $_ENV['blocknumber_id']; // Eigenschaften Gruppe ID Blocknummer
$this->default_image_filename = $_ENV['default_image_filename']; // Default Bildername
$this->language_id = $_ENV['language_id']; // ID der Defaultsprache
$this->length_id=$_ENV['length_id']; // Eigenschaften Gruppe ID Länge
$this->width_id=$_ENV['width_id']; // Eigenschaften Gruppe ID Höhe
$this->amount_id=$_ENV['amount_id']; // Eigenschaften Gruppe ID Anzahl
$this->surfacearea_id=$_ENV['surfacearea_id']; // Eigenschaften Gruppe ID Quadratmeter
$this->meter_id=$_ENV['meter_id']; // Eigenschaften Gruppe ID Meter
$this->stonenumber_id = $_ENV['stonenumber_id']; // Eigenschaften Gruppe ID Steinnummer
$this->sa_id=$_ENV['sa_id']; // Eigenschaften Gruppe ID Sa
$this->squaremeterprice_id=$_ENV['squaremeterprice_id']; // Eigenschaften Gruppe ID Quadratmeterpreis
$this->tax_id=$_ENV["tax_id"]; //ID der Defaultsteuern
$this->currency_id=$_ENV["currency_id"]; // ID der Defaultwährung
$this->deliverytime_id=$_ENV["deliverytime_id"]; //ID der Defaultlieferzeit
$this->saleschannel_id=$_ENV["saleschannel_id"]; //ID der Default Verkaufskanal
$this->salutation_id=$_ENV["salutation_id"]; // ID der Default Ansprache (Keine Angabe)
$this->payment_method_id=$_ENV["payment_method_id"]; // ID der Default Zahlungsmethode (Nachname)
$this->group_id=$_ENV["group_id"]; // ID der Default Kundengruppe
$this->country_id=$_ENV["country_id"]; // ID des Default Landes (Deutschland)
}
/**
* Route("/import", name="import")
*/
public function index(): Response
{
return $this->render('import/index.html.twig', [
'controller_name' => 'ImportController',
]);
}
/**
* ######## Verbindungsaufbau zur Shopware-API ########
*
* @return array
*/
private function client_connect()
{
// $client = new Client([ 'timeout' => 5.0, ]);
$this->client = new Client([
'base_uri' => $this->base_url.'api',
]);
// var_dump("123");exit;
// return $this->render('update/index.html.twig', ["return" => ['user' => "segrd"]]);
if(isset($this->client_username) && isset($this->client_password)){
try{
// var_dump("<pre>");
$response = $this->client->request('POST', '/api/oauth/token', array(
// "debug" => true,
'allow_redirects'=>array('strict'=>true),
"form_params" => array(
"client_id"=> "administration",
"grant_type"=> "password",
"scopes"=> "write",
"username"=> $this->client_username,
"password"=> $this->client_password
)
));
// $response = $this->client->request('POST', '/api/oauth/token', array( "form_params" => array(
// "debug" => true,
// "client_id"=> "administration",
// "grant_type"=> "password",
// "scopes"=> "write",
// "username"=> $this->client_username,
// "password"=> $this->client_password
// )));
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
if ($e->hasResponse()) {
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
}else{
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
}
$response->headers->set('Content-Type', 'application/json');
return $response;
exit;
}
}else {
try{
if(isset($this->client_secret) && isset($this->client_id)){
$response = $this->client->request('POST', '/api/oauth/token', array( "form_params" => array(
// "debug" => true,
'allow_redirects'=>array('strict'=>true),
"grant_type" => "client_credentials",
"client_id" => $this->client_id,
"client_secret" => $this->client_secret
)));
}
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
if ($e->hasResponse()) {
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
}else{
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
}
$response->headers->set('Content-Type', 'application/json');
return $response;
exit;
}
}
$response_body = json_decode($response->getBody()->getContents());
//** Header used for every request
$this->header = array(
"Accept" => "application/json",
"Content-type" => "application/json",
"Authorization" => $response_body->token_type." ".$response_body->access_token,
);
}
private function shop_connect(){
// var_dump($this->getDoctrine()->getConnection( $this->shopware_data));exit;
// return $this->getDoctrine()->getConnection( $this->shopware_data);
return $this->getDoctrine()->getConnection( $this->shopware_data_live);
}
private function dump_connect(){
// var_dump($this->getDoctrine()->getConnection( $this->shopware_data));exit;
// return $this->getDoctrine()->getConnection( $this->shopware_data_dump);
return $this->getDoctrine()->getConnection( $this->shopware_data_dump_live);
}
/**
* get color property id for import
*/
public function getColorID($mgr_id, $mar_id)
{
$this->client_connect();
$conn_shopware = $this->shop_connect();
$conn_akn = $this->dump_connect();
$sql = "SELECT *
FROM materialcolors
WHERE color_id != -1
AND mgr_id = '".$mgr_id."'
AND mar_id = '".$mar_id."'
;";
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
$colors = $stmt->fetchAll();
// return $this->render('update/index.html.twig', ["return" => ['success' => $colors ]]);
if(count($colors) < 1 && !isset($colors[0])){
// wenn farbe nicht gefunden überprüfe ob nur mit mar_id eine gefunden werden kann
$sql = "SELECT *
FROM materialcolors
WHERE color_id != -1
AND mar_id = '".$mar_id."'
;";
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
$colors = $stmt->fetchAll();
}
if(isset($colors[0]["color_id"]) && !empty($colors[0]["color_id"])){
$sql = "SELECT *
FROM `keys`
WHERE key_kind = 88
AND key_number = '".$colors[0]["color_id"]."'
;";
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
$key = $stmt->fetchAll();
// return $this->render('update/index.html.twig', ["return" => ['success' => $key ]]);
if(isset($key[0]["key_text"]) && !empty($key[0]["key_text"])){
// farbe wurde gefunden -> überprüfe ob als variante vorhanden
$color = explode('#',$key[0]["key_text"]);
$key[0]["key_text"] = trim($color[0]);
// return $key[0]["key_text"];
// $this->color_id;
$sql = "SELECT
*
, HEX(id) as id
, HEX(language_id) as language_id
, HEX(property_group_id) as property_group_id
FROM property_group_option
INNER JOIN property_group_option_translation ON (property_group_option.id = property_group_option_translation.property_group_option_id)
WHERE LOWER(HEX(property_group_id)) = '".$this->color_id."'
AND `name` = '".$key[0]["key_text"]."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$property_color = $stmt->fetchAll();
// return $this->render('update/index.html.twig', ["return" => ['materials' => $materials, 'search_materials' => $search_materials]]);
if(isset($property_color) && !empty($property_color) && count($property_color) > 0){
//property vorhanden
// var_dump("exist color ".$key[0]["key_text"]);
}else{
// var_dump("post color ".$key[0]["key_text"]);
$post_property = $this->client->request('POST', '/api/property-group-option', array(
'allow_redirects'=>array('strict'=>true),
'headers' => $this->header,
'body' => json_encode([
'groupId' => $this->color_id,
'name' => $key[0]["key_text"]
])
));
$sql = "SELECT
*
, LOWER(HEX(id)) as id
, HEX(language_id) as language_id
, HEX(property_group_id) as property_group_id
FROM property_group_option
INNER JOIN property_group_option_translation ON (property_group_option.id = property_group_option_translation.property_group_option_id)
WHERE LOWER(HEX(property_group_id)) = '".$this->color_id."'
AND `name` = '".$key[0]["key_text"]."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$property_color = $stmt->fetchAll();
}
$property_color[0]["id"] = strtolower($property_color[0]["id"]);
// return $this->render('update/index.html.twig', ["return" => ['color' => $property_color ]]);
if(isset($property_color[0]["id"]) && !empty($property_color[0]["id"])) {
// var_dump("EXISTS");
return $property_color[0]["id"];
}
}
}
return null;
}
/**
* get category id for import
*/
public function getCategoryId($category, $materialgroup_id) //article[materialgroup_name]
{
// prüfe ob kategorie vorhanden ist,
// var_dump($category);
try {
$search = $this->client->request('GET', '/api/category?filter[name]='.$category.'', array(
"headers" => $this->header,
));
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
if ($e->hasResponse()) {
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
}else{
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
}
$response->headers->set('Content-Type', 'application/json');
return $response;
exit;
}
$categories = json_decode($search->getBody()->getContents(),true);
if(isset($categories["data"][0]["id"]) && !empty($categories["data"][0]["id"])){
}else{
$CategoryFlat = new \App\Controller\shopware\CategoryFlat();
$CategoryFlat->setType("page");
$CategoryFlat->setName($materialgroup["materialgroup_name"]);
$CategoryFlat->setCustomFields(array("custom_materialgroup_id" => $materialgroup_id));
$CategoryFlat->setCmsPageId($this->category_layout_id);
$CategoryFlat->setParentId($this->default_category_id);
$post_category = $this->client->request('POST', '/api/category', array(
// 'debug' => true,
'allow_redirects'=>array('strict'=>true),
"headers" => $this->header,
"body" => $CategoryFlat,
));
try {
$search = $this->client->request('GET', '/api/category?filter[name]='.$category.'', array(
"headers" => $this->header,
));
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
if ($e->hasResponse()) {
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
}else{
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
}
$response->headers->set('Content-Type', 'application/json');
return $response;
exit;
}
$categories = json_decode($search->getBody()->getContents(),true);
}
if(isset($categories["data"][0]["id"]) && !empty($categories["data"][0]["id"])){
return $categories["data"][0]["id"];
}else{
return $this->default_category_id;
}
}
/**
* Fügt Attribute den eingelesenen Artikeln hinzu
*
* Route("/add/attributes", name="add_attributes")
*
* @return mixed
*/
public function add_attributes()
{
// TODO suchtags über suchschlagwörter einbinden
$this->client_connect();
$conn_shopware = $this->shop_connect();
$step_count = 5;
$sql = "SELECT
*
, LOWER(HEX(product_id)) as product_id
, HEX(product_version_id) as product_version_id
, HEX(language_id) as language_id
, JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_material_name') as custom_article_material_name
, JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_materialgroup_name') as custom_article_materialgroup_name
, JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_surface') as custom_article_surface
, JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_articlegroup_name') as custom_article_articlegroup_name
-- , JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_picture') as custom_article_picture
-- materialstärke
FROM product_translation
INNER JOIN product ON (product.id = product_id)
WHERE JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_attributes_added') IS NULL
AND LOWER(HEX(language_id)) = '2fbb5fe2e29a4d70aa5854ce7ce3e20b'
LIMIT 10
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$products_sw = $stmt->fetchAll();
// return $this->render('update/index.html.twig', ["return" => ['attribute' => $products_sw ]]);
foreach($products_sw as $product){
// produkte abholen die flag nicht haben
// einzelne customfelder durchgehen und attribute zuordnen
// nicht vorhandene skippen
unset($attribute);
unset($attributsliste);
$attributsliste=array();
$idliste=array();
if(isset($product["custom_article_material_name"]) && !empty($product["custom_article_material_name"] && $product["custom_article_material_name"] != "null")){
// array_push($attributsliste,str_replace('"','',$product["custom_article_material_name"]));
// array_push($idliste,$this->material_id);
// var_dump($this->material_id);
$attributsliste[$this->material_id]=str_replace('"','',$product["custom_article_material_name"]);
}
if(isset($product["custom_article_materialgroup_name"]) && !empty($product["custom_article_materialgroup_name"] && $product["custom_article_materialgroup_name"] != "null")){
// array_push($attributsliste,str_replace('"','',$product["custom_article_materialgroup_name"]));
// array_push($idliste,$this->materialgroup_id);
// var_dump($this->materialgroup_id);
$attributsliste[$this->materialgroup_id]=str_replace('"','',$product["custom_article_materialgroup_name"]);
}
if(isset($product["custom_article_surface"]) && !empty($product["custom_article_surface"]) && $product["custom_article_surface"] != "null"){
// array_push($attributsliste,str_replace('"','',$product["custom_article_surface"]));
// array_push($idliste,$this->surface_id);
// var_dump($product["custom_article_surface"]);
$attributsliste[$this->surface_id]=str_replace('"','',$product["custom_article_surface"]);
}
if(isset($product["custom_article_articlegroup_name"]) && !empty($product["custom_article_articlegroup_name"] && $product["custom_article_articlegroup_name"] != "null")){
// array_push($attributsliste,str_replace('"','',$product["custom_article_articlegroup_name"]));
// array_push($idliste,$this->articlegroup_id);
// var_dump($this->articlegroup_id);
$attributsliste[$this->articlegroup_id]=str_replace('"','',$product["custom_article_articlegroup_name"]);
}
// return $this->render('update/index.html.twig', ["return" => ['attribute' => $attributsliste ]]);
$attribute=array();
foreach($attributsliste as $key => $attribut){
$sql = "SELECT
*
, LOWER(HEX(id)) as id
, HEX(language_id) as language_id
, HEX(property_group_id) as property_group_id
FROM property_group_option
INNER JOIN property_group_option_translation ON (property_group_option.id = property_group_option_translation.property_group_option_id)
-- WHERE LOWER(HEX(property_group_id)) = 'eadafb18e0c34aeb894dafddf1e107a0'
WHERE `name` = '".$attribut."'
AND LOWER(HEX(property_group_id)) = '".$key."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$search_property = $stmt->fetchAll();
// return $this->render('update/index.html.twig', ["return" => ['dearch' => $search_property ]]);
if(isset($search_property) && is_array($search_property) && count($search_property) > 0) {
array_push($attribute,array('id'=>$search_property[0]["id"]));
}else{
// Attribut nicht in shopware und muss hinzugefügt werden
// var_dump("post ".$attribut." ".$key);exit;
$post_product = $this->client->request('POST', '/api/property-group-option', array(
'allow_redirects'=>array('strict'=>true),
'headers' => $this->header,
'body' => json_encode([
'groupId' => $key,
'name' => $attribut
])
));
$sql = "SELECT
*
, LOWER(HEX(id)) as id
, HEX(language_id) as language_id
, HEX(property_group_id) as property_group_id
FROM property_group_option
INNER JOIN property_group_option_translation ON (property_group_option.id = property_group_option_translation.property_group_option_id)
-- WHERE LOWER(HEX(property_group_id)) = 'eadafb18e0c34aeb894dafddf1e107a0'
WHERE `name` = '".$attribut."'
AND LOWER(HEX(property_group_id)) = '".$key."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$search_property = $stmt->fetchAll();
// return $this->render('update/index.html.twig', ["return" => ['dearch' => $search_property ]]);
array_push($attribute,array('id'=>$search_property[0]["id"]));
}
}
// return $this->render('update/index.html.twig', ["return" => ['attribute' => $attribute ]]);
if(isset($product["custom_article_materialgroup_id"]) && !empty($product["custom_article_materialgroup_id"]) &&
isset($product["custom_article_material_id"]) && !empty($product["custom_article_material_id"])){
$farbid=$this->getColorID($product["custom_article_materialgroup_id"],$product["custom_article_material_id"]);
var_dump($farbid);exit;
}
$ProductFlat = new \App\Controller\shopware\ProductFlat();
$ProductFlat->setProperties($attribute);
$ProductFlat->setCustomFields(array(
"custom_article_attributes_added" => "1"
));
try {
$post_product = $this->client->request('PATCH', '/api/product/'.$product["product_id"], array(
// 'debug' => true,
'allow_redirects'=>array('strict'=>true),
"headers" => $this->header,
"body" => $ProductFlat
// 'body' => json_encode([
// 'properties' => array(
// $attribute
// )
// ,
// 'customFields' => array(
// "custom_article_attributes_added" => "1"
// )
// ])
));
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
if ($e->hasResponse()) {
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
}else{
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
}
$response->headers->set('Content-Type', 'application/json');
return $response;
exit;
}
// return $this->render('update/index.html.twig', ["return" => ['dearch' => $product ]]);
// try{
// $post_product = $this->client->request('PATCH', '/api/product/'.$product["product_id"], array(
// 'headers' => $this->header,
// 'body' => json_encode([
// 'customFields' => array(
// "custom_article_attributes_added" => "1"
// )
// ])
// ));
// } catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
// exit;
// }
// return $this->render('update/index.html.twig', ["return" => ['surfaces' => $product["product_id"]]]);
}
if(count($products_sw) < 11){
return $this->redirect('/add/attributes');
}else{
return $this->render('update/index.html.twig', ["return" => ['Attribute' => "done" ]]);
}
// return $this->render('update/index.html.twig', ["return" => ['attribute' => "fertig" ]]);
}
/**
* Importiert Attribute in Shopware
*
* Route("/import/attributes", name="import_attributes")
*
* @return mixed
*/
public function import_attributes()
{
$this->client_connect();
$conn_shopware = $this->shop_connect();
//gehe alle attribute durch und erstelle eigenschaften in entsprechenden gruppen
// wahlweise: aufruf wenn eigenschaften produkt zugeordnet werden sollen. nimmt eigenschaften entgegen,
// gleicht mit vorhandenen ab, erstellt neue und löscht überschüssige
// Oberflächen
$sql = "SELECT
DISTINCT surface_name
FROM surfaces
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$surfaces = $stmt->fetchAll();
$surfaces = array_map('current', $surfaces);
// $surface_id="0fcb8899440540a5aef143da8f82d096";
foreach($surfaces as $surface){
$sql = "SELECT
*
, HEX(id) as id
, HEX(language_id) as language_id
, HEX(property_group_id) as property_group_id
FROM property_group_option
INNER JOIN property_group_option_translation ON (property_group_option.id = property_group_option_translation.property_group_option_id)
WHERE LOWER(HEX(property_group_id)) = '".$this->surface_id."'
AND `name` = '".$surface."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$search_surfaces = $stmt->fetchAll();
// return $this->render('update/index.html.twig', ["return" => ['surfaces' => $surfaces, 'search_surfaces' => $search_surfaces]]);
if(isset($search_surfaces) && is_array($search_surfaces) && count($search_surfaces) > 0) {
}else{
if(isset($surface) && !empty($surface)){
// return $this->render('update/index.html.twig', ["return" => ['surfaces' => $surface, 'search_surfaces' => $search_surfaces]]);
$post_product = $this->client->request('POST', '/api/property-group-option', array(
'allow_redirects'=>array('strict'=>true),
'headers' => $this->header,
'body' => json_encode([
'groupId' => $this->surface_id,
'name' => $surface
])
));
}
}
}
return $this->render('update/index.html.twig', ["return" => ['surfaces' => "surfaces done" ]]);
// Materialgruppen
$sql = "SELECT
DISTINCT materialgroup_name
FROM materialgroups
;";
// OFFSET ".(($page - 1) * $step_count)."
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$materialgroups = $stmt->fetchAll();
$materialgroups = array_map('current', $materialgroups);
// $materialgroup_id="4d73f7a67c184ca294e778772b860050";
foreach($materialgroups as $materialgroup){
$sql = "SELECT
*
, HEX(id) as id
, HEX(language_id) as language_id
, HEX(property_group_id) as property_group_id
FROM property_group_option
INNER JOIN property_group_option_translation ON (property_group_option.id = property_group_option_translation.property_group_option_id)
WHERE LOWER(HEX(property_group_id)) = '".$this->materialgroup_id."'
AND `name` = '".$materialgroup."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$search_materialgroups = $stmt->fetchAll();
// return $this->render('update/index.html.twig', ["return" => ['materialgroups' => $materialgroups, 'search_materialgroups' => $search_materialgroups]]);
if(isset($search_materialgroups) && is_array($search_materialgroups) && count($search_materialgroups) > 0) {
}else{
if(isset($materialgroup) && !empty($materialgroup)){
$post_product = $this->client->request('POST', '/api/property-group-option', array(
'allow_redirects'=>array('strict'=>true),
'headers' => $this->header,
'body' => json_encode([
'groupId' => $this->materialgroup_id,
'name' => $materialgroup
])
));
}
}
}
return $this->render('update/index.html.twig', ["return" => ['materialgroups' => "materialgroups done" ]]);
// Material
$sql = "SELECT
DISTINCT material_name
FROM materials
;";
// OFFSET ".(($page - 1) * $step_count)."
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$materials = $stmt->fetchAll();
$materials = array_map('current', $materials);
// $material_id="ed4fe3bbe67941e4b4ed47c3308e32f2";
foreach($materials as $material){
$sql = "SELECT
*
, HEX(id) as id
, HEX(language_id) as language_id
, HEX(property_group_id) as property_group_id
FROM property_group_option
INNER JOIN property_group_option_translation ON (property_group_option.id = property_group_option_translation.property_group_option_id)
WHERE LOWER(HEX(property_group_id)) = '".$this->material_id."'
AND `name` = '".$material."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$search_materials = $stmt->fetchAll();
// return $this->render('update/index.html.twig', ["return" => ['materials' => $materials, 'search_materials' => $search_materials]]);
if(isset($search_materials) && is_array($search_materials) && count($search_materials) > 0) {
}else{
if(isset($material) && !empty($material)){
$post_product = $this->client->request('POST', '/api/property-group-option', array(
'allow_redirects'=>array('strict'=>true),
'headers' => $this->header,
'body' => json_encode([
'groupId' => $this->material_id,
'name' => $material
])
));
}
}
}
return $this->render('update/index.html.twig', ["return" => ['materials' => "materials done" ]]);
// Artikelgruppe
$sql = "SELECT
DISTINCT articlegroup_name
FROM articlegroups
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$articlegroups = $stmt->fetchAll();
$articlegroups = array_map('current', $articlegroups);
// $articlegroup_id="025caac2d61d49968dc84c2e291adef6";
foreach($articlegroups as $articlegroup){
$sql = "SELECT
*
, HEX(id) as id
, HEX(language_id) as language_id
, HEX(property_group_id) as property_group_id
FROM property_group_option
INNER JOIN property_group_option_translation ON (property_group_option.id = property_group_option_translation.property_group_option_id)
WHERE LOWER(HEX(property_group_id)) = '".$this->articlegroup_id."'
AND `name` = '".$articlegroup."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$search_articlegroups = $stmt->fetchAll();
// return $this->render('update/index.html.twig', ["return" => ['articlegroups' => $articlegroups, 'search_articlegroups' => $search_articlegroups]]);
if(isset($search_articlegroups) && is_array($search_articlegroups) && count($search_articlegroups) > 0) {
}else{
$post_product = $this->client->request('POST', '/api/property-group-option', array(
'allow_redirects'=>array('strict'=>true),
'headers' => $this->header,
'body' => json_encode([
'groupId' => $this->articlegroup_id,
'name' => $articlegroup
])
));
}
}
// return $this->render('update/index.html.twig', ["return" => ['articlegroups' => "articlegroups done" ]]);
return $this->render('update/index.html.twig', ["return" => ['surfaces' => $surfaces, 'materialgroups' => $materialgroups, 'materials' => $materials, 'articlegroups' => $articlegroups]]);
}
/**
* Erwartet Storage-Array, returned formatierten Array der Eigenschaften für Variantenimport
*/
public function get_variant_ids($storage)
{
$this->client_connect();
$conn_shopware = $this->shop_connect();
$conn_akn = $this->dump_connect();
$eigenschaften = array();
// Dicke
$dicke = $storage["thickness"];
$eigenschaften[$this->thickness_id]=$dicke;
// Fehler j/n
// $fehler = "Nein";
// if( $storage["fault_id"] != 0 && !empty($storage["fault_id"]) && $storage["fault_corner"] != 0 && !empty($storage["fault_corner"])){
// $fehler = "Ja";
// }
// $eigenschaften[$this->fault_exists_id]=$fehler;
// Oberfläche
$oberflaeche = $storage["surface_name"];
$eigenschaften[$this->surface_id]=$oberflaeche;
// Artikelgruppe
if(isset($storage["articlegroup_name"]) && !empty($storage["articlegroup_name"])){
$artikelgruppe = $storage["articlegroup_name"];
$eigenschaften[$this->articlegroup_id]=$artikelgruppe;
}
// // Steinnummer
// $steinnummer = $storage["stone_number"];
// $eigenschaften[$this->stonenumber_id]=$steinnummer;
// Blocknummer
// if(isset($storage["block_number"]) && !empty($storage["block_number"])){
// $blocknummer = $storage["block_number"];
// $eigenschaften[$this->blocknumber_id]=$blocknummer;
// }
if(isset($storage["block_advice"]) && !empty($storage["block_advice"])){
$blocknummer = $storage["block_advice"];
//Zerlegt alle nummern in mat+oberflächenzahl+blocknummer und kürzt zeichen nach leerzeichen automatisch raus
// problemfall: keine oberflächennummer zwischen blocknummer und materialid
$blocknummer_zerlegt=array();
preg_match('/([a-zA-Z]+)([0-9,]+)([a-zA-Z0-9,]+)/', $storage["block_advice"], $blocknummer_zerlegt);
if(strlen(end($blocknummer_zerlegt))>1){
$blocknummer = end($blocknummer_zerlegt);
$eigenschaften[$this->blocknumber_id]=$blocknummer;
}
}
// // Materialfarbe
if(isset($storage["materialgroup_id"]) && !empty($storage["materialgroup_id"]) &&
isset($storage["material_id"]) && !empty($storage["material_id"])){
$sql = "SELECT *
FROM materialcolors
INNER JOIN `keys` ON (materialcolors.color_id = key_number)
WHERE color_id != -1
AND key_kind = 88
AND mgr_id = '".$storage["materialgroup_id"]."'
AND mar_id = '".$storage["material_id"]."'
;";
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
$colors = $stmt->fetchAll();
// return $this->render('update/index.html.twig', ["return" => ['success' => $colors ]]);
if(isset($colors) && is_array($colors) && count($colors) > 0){
$color = explode('#',$colors[0]["key_text"]);
$colors[0]["key_text"] = trim($color[0]);
$eigenschaften[$this->color_id] = $colors[0]["key_text"];
}
}
$attribute=array();
foreach($eigenschaften as $key => $eigenschaft){
$sql = "SELECT
*
, LOWER(HEX(id)) as id
, HEX(language_id) as language_id
, HEX(property_group_id) as property_group_id
FROM property_group_option
INNER JOIN property_group_option_translation ON (property_group_option.id = property_group_option_translation.property_group_option_id)
-- WHERE LOWER(HEX(property_group_id)) = 'eadafb18e0c34aeb894dafddf1e107a0'
WHERE `name` = '".$eigenschaft."'
AND LOWER(HEX(property_group_id)) = '".$key."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$search_property = $stmt->fetchAll();
// var_dump($search_property);exit;
// return $this->render('update/index.html.twig', ["return" => ['dearch' => $search_property ]]);
// array("optionId" => "d6c37799803148fe977e42d2fe7cc875",),
// array("optionId" => "340073aa01544985ab0d1f056c1f8422",)
if(isset($search_property) && is_array($search_property) && count($search_property) > 0) {
array_push($attribute,array('optionId'=>$search_property[0]["id"]));
}else{
// Attribut nicht in shopware und muss hinzugefügt werden
// var_dump($eigenschaften);
// var_dump("post ".$eigenschaft." ".$key);
$post_product = $this->client->request('POST', '/api/property-group-option', array(
'allow_redirects'=>array('strict'=>true),
'headers' => $this->header,
'body' => json_encode([
'groupId' => $key,
'name' => $eigenschaft
])
));
$optionId = $post_product->getHeaders();
// var_dump($optionId);exit;
$optionId = str_replace($this->base_url.'api/property-group-option/','',$optionId["Location"]); // ID des erstellten produktes
array_push($attribute,array('optionId'=>$optionId[0]));
}
}
// var_dump($attribute);exit;
return $attribute;
}
/**
* test
*
* @Route("/teste", name="teste")
*
* @return mixed
*/
public function teste()
{
$this->client_connect();
$conn_shopware = $this->shop_connect();
// $conn_konfigurator = $this->konfig_connect();
// try {
// $post_customer = $this->client->request('POST', '/api/sales-channel-payment-method', array(
// // 'debug' => true,
// 'allow_redirects'=>array('strict'=>true),
// 'headers' => $this->header,
// 'body' => json_encode([
// 'salesChannelId' => "f8c383f75225488a8f6b89b7d64d6cf2",
// 'paymentMethodId' => "54008360b783458a9c82db74b1ca3f07"
// ])
// ));
// $location = array_pop( $post_customer->getHeaders()['Location'] );
// $payment_method_id = basename($location);
// } catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
// exit;
// }
$payment_method_id = "54008360b783458a9c82db74b1ca3f07";
// $sql = "INSERT INTO sales_channel_payment_method (sales_channel_id, payment_method_id)
// VALUES (UNHEX('f8c383f75225488a8f6b89b7d64d6cf2'), UNHEX('".$payment_method_id."'))
// ;";
// $stmt = $conn_shopware->prepare($sql);
// $stmt->execute();
dd($payment_method_id);
$ProductFlat = new \App\Controller\shopware\ProductFlat();
$ProductFlat->setActive(true);
$ProductFlat->setCategories(array(array("id" => $categoryId)));
$ProductFlat->setDeliveryTimeId($this->deliverytime_id);
$ProductFlat->setDescription($article["article_name_1"]);
$ProductFlat->setIsCloseout(true);
$ProductFlat->setMarkAsTopseller(false);
$ProductFlat->setName($material_name);
$ProductFlat->setPrice(array(array(
"currencyId" => $this->currency_id,
"net" => 0,
"gross" => 0,
"linked" => false
)));
$ProductFlat->setProductNumber($article["article_id"]);
$ProductFlat->setPurchaseUnit(1);
// $ProductFlat->setRestockTime();
// $ProductFlat->setUnitId("ff7d882bd1da432bb4e05e21eda66114");
$ProductFlat->setTaxId($this->tax_id);
$ProductFlat->setVisibilities(array(array(
"salesChannelId" => $this->saleschannel_id,
"visibility" => 30
)));
$ProductFlat->setCustomFields(array("custom_article_surface" => "testsurface",));
$ProductFlat->setHeight($article["thickness"]);
$ProductFlat->setLength($article["length"]);
$ProductFlat->setWidth($article["width"]);
$ProductFlat->setStock(0);
// $ProductFlat->setMaxPurchase(0);
if( isset($storage_option_list) && is_array($storage_option_list) && count($storage_option_list)>0){
$ProductFlat->setConfiguratorGroupConfig(array(array(
"id" => "5eb85799b5914547adf6348ff2213d5e",
"representation" => "box",
"expressionForListings" => false
)));
$ProductFlat->setConfiguratorSettings(
// array("optionId" => "d6c37799803148fe977e42d2fe7cc875",),
// array("optionId" => "340073aa01544985ab0d1f056c1f8422",)
$storage_option_list
);
}
// return $this->render('update/index.html.twig', ["return" => ['nummern' => $ProductFlat]]);
$poststring = 'api/product';
// var_dump("post ".$article["article_id"]);
// var_dump("<pre>");
if( isset($material_name) && !empty($material_name)){
try {
$post_product = $this->client->request('PATCH', '/'.$poststring, array(
// "debug" => true,
'allow_redirects'=>array('strict'=>true),
"headers" => $this->header,
"body" => $ProductFlat
));
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
if ($e->hasResponse()) {
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
}else{
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
}
$response->headers->set('Content-Type', 'application/json');
return $response;
exit;
}
$post_product_id = $post_product->getHeaders();
$post_product_id = str_replace($this->base_url.$poststring.'/','',$post_product_id["Location"]); // ID des erstellten produktes
}
return $this->render('update/index.html.twig', ["return" => ['ID' => $groupId ]]);
// $ProductFlat = new \App\Controller\shopware\ProductFlat();
// $ProductFlat->setName("testX-Varianten");
// $ProductFlat->setProductNumber("test_post");
// $ProductFlat->setStock(10);
// $ProductFlat->setPrice(array(array(
// "currencyId" => "b7d2554b0ce847cd82f3ac9bd1c0dfca",
// "gross" => 15,
// "net" => 10,
// "linked" => false
// )));
// $ProductFlat->setTaxId("c5ddfa75e22b4889b6f628e1df0b347f");
// $ProductFlat->setConfiguratorGroupConfig(array(array(
// "id" => "5eb85799b5914547adf6348ff2213d5e",
// "representation" => "box",
// "expressionForListings" => false
// )));
// $ProductFlat->setConfiguratorSettings(array(
// array("optionId" => "d6c37799803148fe977e42d2fe7cc875",),
// array("optionId" => "340073aa01544985ab0d1f056c1f8422",)));
// try {
// $post_product = $this->client->request('POST', '/api/product', array(
// "headers" => $this->header,
// "body" => $ProductFlat
// // "form_params" => array($variant_values)
// ));
// } catch (RequestException $e) {
// echo "<pre>";
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
// exit;
// }
// $poststring = 'api/product';
// $post_product_id = $post_product->getHeaders();
// $post_product_id = str_replace($this->base_url.$poststring.'/','',$post_product_id["Location"]); // ID des erstellten produktes
// $ProductFlat = new \App\Controller\shopware\ProductFlat();
// $ProductFlat->setName("testX-Varianten 1");
// $ProductFlat->setParentId($post_product_id[0]);
// $ProductFlat->setProductNumber("test_post_1");
// $ProductFlat->setStock(10);
// $ProductFlat->setOptions(array(array("id"=> "d6c37799803148fe977e42d2fe7cc875")));
// try {
// $post_product = $this->client->request('POST', '/api/product', array(
// "headers" => $this->header,
// "body" => $ProductFlat
// ));
// } catch (RequestException $e) {
// echo "<pre>";
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
// exit;
// }
// $ProductFlat = new \App\Controller\shopware\ProductFlat();
// // $ProductFlat->setActive(true);
// $ProductFlat->setName("testX-Varianten 2");
// $ProductFlat->setParentId($post_product_id[0]);
// $ProductFlat->setProductNumber("test_post_2");
// $ProductFlat->setStock(10);
// $ProductFlat->setOptions(array(
// array("id"=> "340073aa01544985ab0d1f056c1f8422") //ID der Varianteneigenschaft
// ));
// try {
// $post_product = $this->client->request('POST', '/api/product', array(
// "headers" => $this->header,
// "body" => $ProductFlat
// ));
// } catch (RequestException $e) {
// echo "<pre>";
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
// exit;
// }
$name= "9702_Nero Ass Classico geschliffen";
// var_dump("<pre>");
try {
$search = $this->client->request('GET', '/api/media?filter[fileName]='.$name.'', array(
"headers" => $this->header,
));
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
if ($e->hasResponse()) {
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
}else{
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
}
$response->headers->set('Content-Type', 'application/json');
return $response;
exit;
}
$search_picture = json_decode($search->getBody()->getContents(),true);
$sql = "SELECT
*
FROM materials
WHERE material_id = 'ERAM'
AND materialgroup_id = 'GE'
;";
// LIMIT ".$step_count."
// OFFSET ".(($page - 1) * $step_count)."
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$material = $stmt->fetchAll();
return $this->render('update/index.html.twig', ["return" => ['nummern' => $material ]]);
$sql = "SELECT
*
,articles.material_id as material_id
FROM articles
LEFT JOIN materials ON (materials.material_id = articles.material_id)
LEFT JOIN materialgroups ON (materialgroups.materialgroup_id = articles.materialgroup_id)
LEFT JOIN surfaces ON (surfaces.surface_id = articles.surface_id AND surfaces.materialgroup_id = articles.materialgroup_id)
WHERE article_id = '00632'
ORDER BY articles.id ASC
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$articles = $stmt->fetchAll();
// return $this->render('update/index.html.twig', ["return" => ['nummern' => $artikelnummern, 'zeit' => $articles]]);
$sql = "SELECT
*
FROM storages
LEFT JOIN surfaces ON (surfaces.surface_id = storages.surface_id AND storages.materialgroup_id = surfaces.materialgroup_id)
LEFT JOIN articlegroups ON (articlegroups.articlegroup_id = storages.articlegroup_id)
LEFT JOIN storagefaults ON storagefaults.fault_id = storages.fault_id
WHERE article_id = '".$articles[0]["article_id"]."'
ORDER BY storages.id ASC
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$storages = $stmt->fetchAll();
return $this->render('update/index.html.twig', ["return" => ['shop' => $articles, 'storages' => $storages ]]);
return $this->render('update/index.html.twig', ["return" => ['shop' => $post_product_id ]]);
return $this->render('update/index.html.twig', ["return" => ['shop' => "true" ]]);
}
/**
* Importiert Zulauf Produkte
*
* @Route("/import/products_zulauf", name="import_products_zulauf")
*
* @return mixed
*/
public function import_products_zulauf() {
$this->client_connect();
$conn_shopware = $this->shop_connect();
$conn_akn = $this->dump_connect();
$step_count = 10;
$sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,UpdateDatetime)
VALUES(?,?,?)";
$stmtt = $conn_shopware->prepare($sql);
$stmtt->execute(array(
"import/products_zulauf",
"Start",
date('Y-m-d H:i:s')
));
# holt alle ZULAUF Artikel ab die eine passende Materialaufschlüsselung haben
$sql = "SELECT *
, articles.material_id as material_id
, articles.materialgroup_id as materialgroup_id
, storages.id as storage_id
-- , CONCAT(storages.article_id, '_' ,storages.id) AS product_number
-- , CONCAT('zulauf', '_', storages.material_id, '_', storages.materialgroup_id, '_', storages.id) AS product_number
, storages.stone_number AS product_number
, CONCAT('Zulauf_', storages.material_id, '_', storages.materialgroup_id) AS product_number_parent
-- , COALESCE(NULLIF(storages.picture, ''), NULLIF(materials.picture, ''), 'default.jpg') AS picture -- select picture
, storages.picture AS storages_picture
, materials.picture AS materials_picture
, COALESCE(NULLIF(materials.picture, ''), 'default.jpg') AS picture_parent -- select picture
FROM articles
LEFT JOIN materials ON (articles.material_id = materials.material_id AND articles.materialgroup_id = materials.materialgroup_id )
-- LEFT JOIN articlegroups ON (articlegroups.articlegroup_id = articles.article_id)
LEFT JOIN articlegroups ON (articles.articlegroup_id = articlegroups.articlegroup_id)
LEFT JOIN materialgroups ON (materialgroups.materialgroup_id = articles.materialgroup_id)
LEFT JOIN surfaces ON (surfaces.surface_id = articles.surface_id AND surfaces.materialgroup_id = articles.materialgroup_id)
INNER JOIN storages ON (articles.article_id = storages.article_id)
LEFT JOIN storagefaults ON (storages.fault_id = storagefaults.fault_id)
WHERE storages.stone_number IS NOT NULL
AND storages.storage = 'ZULAUF'
AND storages.upload_done IS NULL
AND material_name IS NOT NULL
-- AND articles.material_id = 'BCCD'
ORDER BY articles.id ASC
LIMIT ".$step_count."
;";
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
$articles = $stmt->fetchAll();
// dd($articles);
// echo "<pre>"; var_dump($articles); exit;
# NEXT STEP
if(count($articles) >= $step_count) {
$nextPage = true;
} else {
$nextPage = false;
}
# PICTURE
// dd($articles[0]['picture']);
// $this->getParameter('kernel.project_dir')."/public/produktbilder"
// $materialid = $articles[0]["material_id"];
// $materialname = $articles[0]["material_name"];
// $materialgruppenname = $articles[0]["materialgroup_name"];
// $materialgruppenid = $articles[0]["materialgroup_id"];
// $oberflaeche = $articles[0]["surface_name"];
foreach($articles as $article) {
$productParentId = "";
$productId = "";
// $productNumber = $article['article_id']
// var_dump($productNumber);
# REMOVE SUFFIX FROM PICTURE NAME
// if(strpos($article['picture'], '-##-')) {
// $article['picture'] = substr($article['picture'], 0, -5);
// }
if(strpos($article['storages_picture'], '-##-')) {
$article['storages_picture'] = substr($article['picture'], 0, -5);
}
if(strpos($article['materials_picture'], '-##-')) {
$article['storages_picture'] = substr($article['picture'], 0, -5);
}
# END EDIT PICTURE NAME
$storage_option_list = $this->get_variant_ids($article); //übergibt storage, returned formatierten array für variantenerstellung
# CHECK IF PARENT PRODUCT ALREADY EXISTS ###################################
// $sql = "SELECT *, LOWER(HEX(id)) as id
$sql = "SELECT LOWER(HEX(product.id)) as id
FROM product_translation
INNER JOIN product ON (product.id = product_translation.product_id)
INNER JOIN product_category ON ( product_translation.product_id = product_category.product_id)
-- INNER JOIN category ON ( product_category.category_id = category.category_id)
-- INNER JOIN category_translation ON ( product_category.category_id = category_translation.category_id)
WHERE LOWER(HEX(product_category.category_id)) = '".$this->zulauf_id."'
-- AND JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_material_id') = '".$article['material_id']."'
-- AND JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_materialgroup_id') = '".$article['materialgroup_id']."'
AND product.product_number = '". $article['product_number_parent'] . "'
AND parent_id IS NULL
LIMIT 1
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$search_product = $stmt->fetchAll();
# TODO: IF PRODUCT ALREADY EXISTS
if(isset($search_product[0]["id"]) && !empty($search_product[0]["id"]) && count($search_product) > 0) {
$productParentId = $search_product[0]["id"];
}
# SET DATA FOR PRODUCT ###################################
$ProductFlat = new \App\Controller\shopware\ProductFlat();
$ProductFlat->setActive(true);
// $ProductFlat->setCategories(array(array("id" => $this->getCategoryId($materialgruppenname, $materialgruppenid))));
$ProductFlat->setCategories(array(array("id" => $this->zulauf_id)));
$ProductFlat->setDeliveryTimeId($this->deliverytime_id);
$ProductFlat->setDescription($article["article_name_1"]);
$ProductFlat->setIsCloseout(false); // ABVERKAUF - Wichtig, da Anzahl = 0
$ProductFlat->setMarkAsTopseller(false);
// $ProductFlat->setName('Zulauf '.$article['product_number_parent'] .' ('. date('Y-m-d H:m:s') .')');
// $ProductFlat->setName('Zulauf '.$article['product_number_parent']);
// $ProductFlat->setName('ZULAUF: ' . $article['product_number_parent']);
$ProductFlat->setName('Zulauf: ' . $article['material_name']);
// $ProductFlat->setName('Zulauf '.$materialname);
// $ProductFlat->setName('toller lustiger testname');
$ProductFlat->setPrice(array(array(
"currencyId" => $this->currency_id,
"net" => 0,
"gross" => 0,
"linked" => false
)));
// $ProductFlat->setProductNumber($article['product_number']);
// $ProductFlat->setProductNumber($article["materialgroup_id"]);
$ProductFlat->setProductNumber($article['product_number_parent']);
$ProductFlat->setPurchaseUnit(1);
// $ProductFlat->setRestockTime();
// $ProductFlat->setUnitId("ff7d882bd1da432bb4e05e21eda66114");
$ProductFlat->setTaxId($this->tax_id);
// $ProductFlat->setCustomFields();
$custom_fields = array();
// $custom_fields["custom_article_surface"] = $article["surface_name"];
// $custom_fields["custom_article_search_word"] = $article["search_word"];
// $custom_fields["custom_article_name_1"] = $article["article_name_1"];
// $custom_fields["custom_article_name_2"] = $article["article_name_2"];
// $custom_fields["custom_article_material_id"] = $article["material_id"];
// $custom_fields["custom_article_materialgroup_id"] = $article["materialgroup_id"];
// // $custom_fields["custom_article_material_name"] = $materialname;
// $custom_fields["custom_article_material_name"] = $article['material_name'];
// $custom_fields["custom_article_materialgroup_name"] = $article["materialgroup_name"];
// $custom_fields["custom_article_picture"] = $article["picture"];
// $custom_fields["custom_article_material_id"] = $article["material_id"];
// $custom_fields["custom_article_materialgroup_id"] = $article["materialgroup_id"];
$search = array("{", "}", "(", ")", "/", "\\", "@", ":");
$replace = array("", "", "", "", "", "", "", "");
$custom_fields["custom_article_surface"] = str_replace($search, $replace, $article["surface_name"]);
$custom_fields["custom_article_search_word"] = str_replace($search, $replace, $article["search_word"]);
$custom_fields["custom_article_name_1"] = str_replace($search, $replace, $article["article_name_1"]);
$custom_fields["custom_article_name_2"] = str_replace($search, $replace, $article["article_name_2"]);
$custom_fields["custom_article_material_id"] = str_replace($search, $replace, $article["material_id"]);
$custom_fields["custom_article_materialgroup_id"] = str_replace($search, $replace, $article["materialgroup_id"]);
$custom_fields["custom_article_material_name"] = str_replace($search, $replace, $article['material_name']);
$custom_fields["custom_article_materialgroup_name"] = str_replace($search, $replace, $article["materialgroup_name"]);
$custom_fields["custom_article_picture"] = str_replace($search, $replace, $article["picture"]);
$custom_fields["custom_article_id"] = str_replace($search, $replace, $article["article_id"]);
$custom_fields["custom_article_stone_number"] = str_replace($search, $replace, $article["stone_number"]);
$custom_fields["custom_article_block_number"] = str_replace($search, $replace, $article["block_advice"]);
$custom_fields["custom_article_search_word"] = str_replace($search, $replace, $article["search_word"]);
$custom_fields["custom_article_advice"] = str_replace($search, $replace, $article["advice"]);
$custom_fields["custom_article_storage"] = str_replace($search, $replace, $article["storage"]);
$custom_fields["custom_article_amount_2"] = str_replace($search, $replace, $article["amount_2"]);
$custom_fields["custom_article_articlegroup_name"] = str_replace($search, $replace, $article["articlegroup_name"]);
$custom_fields["custom_article_fault_id"] = str_replace($search, $replace, $article["fault_name"]);
$custom_fields["custom_article_fault_corner"] = str_replace($search, $replace, $article["fault_corner"]);
$custom_fields["custom_article_additional_advice"] = str_replace($search, $replace, $article["additional_advice"]);
$ProductFlat->setCustomFields(
$custom_fields
);
$ProductFlat->setHeight($article["thickness"]);
$ProductFlat->setLength($article["length"]);
$ProductFlat->setWidth($article["width"]);
$ProductFlat->setStock(0);
$ProductFlat->setMaxPurchase(0);
$ProductFlat->setVisibilities(array(array(
"salesChannelId" => $this->saleschannel_id,
"visibility" => 30
)));
# UPDATE OR INSERT ###################################
$poststring = 'api/product';
$storage_option_list = $this->get_variant_ids($article); //übergibt storage, returned formatierten array für variantenerstellung
# PARENT ###################################
if( isset($article['material_name']) && trim($article['material_name']) != '') {
if($productParentId == '') {
# INSERT PARENT ARTICLE ##########################
if( isset($storage_option_list) && is_array($storage_option_list) && count($storage_option_list) > 0) {
# TODO: vorbereitung der Varianten
// $ProductFlat->setConfiguratorGroupConfig(array(array(
// // "id" => "5eb85799b5914547adf6348ff2213d5e",
// // "representation" => "box",
// // "expressionForListings" => false
// )));
$ProductFlat->setConfiguratorGroupConfig(array(
array(
"id" => $this->thickness_id,
"representation" => "box",
"expressionForListings" => false
),
array(
"id" => $this->articlegroup_id,
"representation" => "box",
"expressionForListings" => false
),
array(
"id" => $this->surface_id,
"representation" => "box",
"expressionForListings" => false
),
array(
"id" => $this->length_id,
"representation" => "box",
"expressionForListings" => false
),
array(
"id" => $this->width_id,
"representation" => "box",
"expressionForListings" => false
),
array(
"id" => $this->amount_id,
"representation" => "box",
"expressionForListings" => false
),
array(
"id" => $this->surfacearea_id,
"representation" => "box",
"expressionForListings" => false
),
array(
"id" => $this->meter_id,
"representation" => "box",
"expressionForListings" => false
),
array(
"id" => $this->sa_id,
"representation" => "box",
"expressionForListings" => false
),
array(
"id" => $this->blocknumber_id,
"representation" => "box",
"expressionForListings" => false
),
array(
"id" => $this->material_id,
"representation" => "box",
"expressionForListings" => false
),
array(
"id" => $this->materialgroup_id,
"representation" => "box",
"expressionForListings" => false
),
array(
"id" => $this->fault_exists_id,
"representation" => "box",
"expressionForListings" => false
),
array(
"id" => $this->squaremeterprice_id,
"representation" => "box",
"expressionForListings" => false
),
array(
"id" => $this->stonenumber_id,
"representation" => "box",
"expressionForListings" => false
)
));
# SET CONFIGURATOR SETTINGS
// dd($storage_option_list);
// $ProductFlat->setConfiguratorSettings($storage_option_list);
// $sol_option=array();
// foreach($storage_option_list as $key => $sol) {
// // $sol_option[$key]["productId"] = "";
// $sol_option[$key]["id"] = $sol["optionId"];
// }
// $ProductFlat->setConfiguratorSettings($sol_option);
try {
$post_product = $this->client->request('POST', '/'.$poststring, array(
// "debug" => true,
'allow_redirects'=>array('strict'=>true),
"headers" => $this->header,
"body" => $ProductFlat
));
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
if ($e->hasResponse()) {
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
}else{
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
}
$response->headers->set('Content-Type', 'application/json');
return $response;
exit;
}
# PARENT ID
$productParentId = $post_product->getHeaders();
$productParentId = str_replace($this->base_url.$poststring.'/','',$productParentId["Location"]); // ID des erstellten produktes
$productParentId = $productParentId[0];
}
}
# INSERT VARIANT ##########################
$ProductFlat->setParentId($productParentId);
$ProductFlat->setProductNumber($article["product_number"]);
$ProductFlat->setConfiguratorGroupConfig(null);
$ProductFlat->setConfiguratorSettings(null);
// $ProductFlat->setName('Zulauf '.$article['product_number_parent']);
// $ProductFlat->setName($article['product_number_parent']);
$ProductFlat->setName('Zulauf: ' . $article['material_name']);
# PICTURES AND IMAGES ##########################
// # storages_picture
// $sql = "SELECT LOWER(HEX(id)) AS id
// FROM media
// WHERE file_name = '".pathinfo($article['storages_picture'], PATHINFO_FILENAME)."'
// LIMIT 1
// ;";
// $stmt = $conn_shopware->prepare($sql);
// $stmt->execute();
// $pictures = $stmt->fetchAll();
// if(!(isset($pictures) && is_array($pictures) && count($pictures) > 0)) {
// # MATERIALS_PICTURE
// $sql = "SELECT LOWER(HEX(id)) AS id
// FROM media
// WHERE file_name = '".pathinfo($article['materials_picture'], PATHINFO_FILENAME)."'
// LIMIT 1
// ;";
// $stmt = $conn_shopware->prepare($sql);
// $stmt->execute();
// $pictures = $stmt->fetchAll();
// if(!(isset($pictures) && is_array($pictures) && count($pictures) > 0)) {
// # DEFAULT_IMAGE
// $sql = "SELECT LOWER(HEX(id)) AS id
// FROM media
// WHERE file_name = '".pathinfo($this->default_image_filename, PATHINFO_FILENAME)."'
// LIMIT 1
// ;";
// $stmt = $conn_shopware->prepare($sql);
// $stmt->execute();
// $pictures = $stmt->fetchAll();
// }
// }
// // $sql = "SELECT LOWER(HEX(id)) AS id
// // FROM media
// // WHERE file_name = '".pathinfo($article['picture'], PATHINFO_FILENAME)."'
// // LIMIT 1
// // ;";
// // $stmt = $conn_shopware->prepare($sql);
// // $stmt->execute();
// // $pictures = $stmt->fetchAll();
// if(isset($pictures) && is_array($pictures) && count($pictures) > 0) {
// $pictureId = $pictures[0]['id'];
// # CHECK IF COVER IS ALREADY ASSIGNED
// $sql = "SELECT LOWER(HEX(media.id)) AS media_id,
// CONCAT (media.file_name, '.', media.file_extension) AS file_name
// FROM product
// INNER JOIN product_media ON ( product.cover = product_media.id)
// INNER JOIN media ON (product_media.media_id = media.id)
// WHERE LOWER(HEX(product.id)) = '".$productId."'
// LIMIT 1
// ;";
// $stmt = $conn_shopware->prepare($sql);
// $stmt->execute();
// $cover_SELECT = $stmt->fetchAll();
// if( !(isset($cover_SELECT) && is_array($cover_SELECT) && count($cover_SELECT) > 0)
// || (isset($cover_SELECT['filename']) && strtolower( $article['materials_picture'] ) != strtolower( $cover_SELECT['filename']) ) ) {
// # NO COVER ASSIGNED, OR DIFFERENT COVER:
// $cover = new stdClass();
// $cover->type = "product_media";
// $cover->mediaId = $pictureId;
// $ProductFlat->setCover($cover);
// $media = new stdClass();
// $media->type = "product_media";
// $media->mediaId = $pictureId;
// $ProductFlat->setMedia(array($media));
// }
// }
if( isset($storage_option_list) && is_array($storage_option_list) && count($storage_option_list) > 0) {
$sol_option = [];
foreach($storage_option_list as $key => $sol){
$sol_option[$key]["id"] = $sol["optionId"];
}
$ProductFlat->setOptions( $sol_option );
$sql = "SELECT LOWER(HEX(product.id)) as id
FROM product
-- WHERE LOWER( product_number ) = LOWER('".$ProductFlat->getProductNumber()."')
WHERE product_number = ".$ProductFlat->getProductNumber()."
LIMIT 1
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$product_SELECT = $stmt->fetchAll();
if(isset($product_SELECT) && is_array($product_SELECT) && count($product_SELECT) > 0) {
# UPDATE
$ProductFlat->setVisibilities([]);
try {
$post_product = $this->client->request('PATCH', '/api/product/'.$product_SELECT[0]['id'], array(
// "debug" => true,
'allow_redirects'=>array('strict' => true),
"headers" => $this->header,
"body" => $ProductFlat
));
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
if ($e->hasResponse()) {
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
}else{
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
}
$response->headers->set('Content-Type', 'application/json');
return $response;
exit;
}
$productId = $product_SELECT[0]['id'];
} else {
# INSERT
try {
$post_product = $this->client->request('POST', '/api/product', array(
// "debug" => true,
'allow_redirects'=>array('strict' => true),
"headers" => $this->header,
"body" => $ProductFlat
));
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
if ($e->hasResponse()) {
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
}else{
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
}
$response->headers->set('Content-Type', 'application/json');
return $response;
exit;
}
// # PARENT ID
$productId = $post_product->getHeaders();
$productId = str_replace($this->base_url.$poststring.'/','',$productId["Location"]); // ID des erstellten produktes
$productId = $productId[0];
}
//###*** Prüfe auf Bild und füge es hinzu, falls es nicht vorhanden ist.
if(isset( $article['storages_picture']) && !empty( $article['storages_picture']) && $article['storages_picture'] != ""){
$this->add_image($article['storages_picture'], $productId, false);
}elseif(isset( $article['materials_picture']) && !empty( $article['materials_picture']) && $article['materials_picture'] != ""){
$this->add_image($article['materials_picture'], $productId, false);
}else{
$this->add_image($this->default_image_filename, $productId, false);
}
// echo "<pre>"; var_dump($ProductFlat); exit;
# UPDATE KEYWORDS ###################
$keywords = [ $article['search_word'] ];
try {
$post_product = $this->client->request('PATCH', '/api/product/'.$productId, array(
// "debug" => true,
'allow_redirects'=>array('strict' => true),
'headers' => $this->header,
'body' => json_encode([
"customSearchKeywords"=> $keywords // DAS IST KEIN MERGE: WERTE WERDEN KNALLHART ÜBERSCHRIEBEN
])
));
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
if ($e->hasResponse()) {
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
}else{
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
}
$response->headers->set('Content-Type', 'application/json');
return $response;
exit;
}
# END UPDATE KEYWORDS ###################
}
// echo "<pre>"; var_dump(__LINE__); exit;
# UPDATE PARENT, FEHLENDE OPTIONEN IN VARIANTENGENERATOR HINZUFÜGEN ##########################
$sql = "SELECT *
, LOWER(HEX(product_id)) as product_id
, LOWER(HEX(property_group_option_id)) as property_group_option_id
FROM product_configurator_setting
WHERE LOWER(HEX(product_id)) = '".$productParentId."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$config_settings = $stmt->fetchAll();
$option_list = array_column($storage_option_list, 'optionId');
$config_list = array_column($config_settings, 'property_group_option_id');
foreach($option_list as $option) {
if(!in_array($option, $config_list)) {
try {
$post_product = $this->client->request('POST', '/api/product/'.$productParentId.'/configurator-settings', array(
// "debug" => true,
'allow_redirects'=>array('strict'=>true),
"headers" => $this->header,
// 'body' => $ProductFlat
'body' => json_encode([
"optionId"=>$option
// 'name' => strval($i),
// 'position' => $i
])
));
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
if ($e->hasResponse()) {
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
}else{
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
}
$response->headers->set('Content-Type', 'application/json');
return $response;
exit;
}
}
}
# UPDATE DATETIME
$sqlt = "UPDATE storages
-- SET datetime_last_imported = '".date('Y-m-d H:i:s')."'
SET upload_done = 1
WHERE stone_number = '".$article['product_number']."'
;";
$stmtt = $conn_akn->prepare($sqlt);
$stmtt->execute();
}
}
# NEXT PAGE
if($nextPage == true){
return $this->redirect('/import/products_zulauf');
}
# CLEAR CACHE ##########################
$this->clear_cache();
$sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,UpdateDatetime)
VALUES(?,?,?)";
$stmtt = $conn_shopware->prepare($sql);
$stmtt->execute(array(
"import/products_zulauf",
"ENDE",
date('Y-m-d H:i:s')
));
# DONE
// return json_encode(array( 'success' => true ));
$response = new Response(json_encode(array('success' => true)));
$response->headers->set('Content-Type', 'application/json');
return $response;
// return $this->render('update/index.html.twig', ["return" => ['success' => "Zulaufartikel importiert" ]]);
}
// public function import_products_zulauf_BACKUP() {
// $this->client_connect();
// $conn_shopware = $this->shop_connect();
// $conn_akn = $this->dump_connect();
// $step_count = 5;
// # holt alle ZULAUF Artikel ab die eine passende Materialaufschlüsselung haben
// $sql = "SELECT *
// , articles.material_id as material_id
// , articles.materialgroup_id as materialgroup_id
// , storages.id as storage_id
// -- , CONCAT(storages.article_id, '_' ,storages.id) AS product_number
// -- , CONCAT('zulauf', '_', storages.material_id, '_', storages.materialgroup_id, '_', storages.id) AS product_number
// , storages.stone_number AS product_number
// , CONCAT('Zulauf_', storages.material_id, '_', storages.materialgroup_id) AS product_number_parent
// -- , COALESCE(NULLIF(storages.picture, ''), NULLIF(materials.picture, ''), 'default.jpg') AS picture -- select picture
// , storages.picture AS storages_picture
// , materials.picture AS materials_picture
// , COALESCE(NULLIF(materials.picture, ''), 'default.jpg') AS picture_parent -- select picture
// FROM articles
// LEFT JOIN materials ON (articles.material_id = materials.material_id AND articles.materialgroup_id = materials.materialgroup_id )
// -- LEFT JOIN articlegroups ON (articlegroups.articlegroup_id = articles.article_id)
// LEFT JOIN articlegroups ON (articles.articlegroup_id = articlegroups.articlegroup_id)
// LEFT JOIN materialgroups ON (materialgroups.materialgroup_id = articles.materialgroup_id)
// LEFT JOIN surfaces ON (surfaces.surface_id = articles.surface_id AND surfaces.materialgroup_id = articles.materialgroup_id)
// INNER JOIN storages ON (articles.article_id = storages.article_id)
// LEFT JOIN storagefaults ON (storages.fault_id = storagefaults.fault_id)
// WHERE storages.stone_number IS NOT NULL
// AND storages.storage = 'ZULAUF'
// AND storages.upload_done IS NULL
// AND material_name IS NOT NULL
// -- AND articles.material_id = 'BCCD'
// ORDER BY articles.id ASC
// LIMIT ".$step_count."
// ;";
// $stmt = $conn_akn->prepare($sql);
// $stmt->execute();
// $articles = $stmt->fetchAll();
// # NEXT STEP
// if(count($articles) >= $step_count) {
// $nextPage = true;
// } else {
// $nextPage = false;
// }
// # PICTURE
// // dd($articles[0]['picture']);
// // $this->getParameter('kernel.project_dir')."/public/produktbilder"
// // $materialid = $articles[0]["material_id"];
// // $materialname = $articles[0]["material_name"];
// // $materialgruppenname = $articles[0]["materialgroup_name"];
// // $materialgruppenid = $articles[0]["materialgroup_id"];
// // $oberflaeche = $articles[0]["surface_name"];
// foreach($articles as $article) {
// $productParentId = "";
// $productId = "";
// // $productNumber = $article['article_id']
// // var_dump($productNumber);
// # REMOVE SUFFIX FROM PICTURE NAME
// // if(strpos($article['picture'], '-##-')) {
// // $article['picture'] = substr($article['picture'], 0, -5);
// // }
// if(strpos($article['storages_picture'], '-##-')) {
// $article['storages_picture'] = substr($article['picture'], 0, -5);
// }
// if(strpos($article['materials_picture'], '-##-')) {
// $article['storages_picture'] = substr($article['picture'], 0, -5);
// }
// # END EDIT PICTURE NAME
// $storage_option_list = $this->get_variant_ids($article); //übergibt storage, returned formatierten array für variantenerstellung
// # CHECK IF PARENT PRODUCT ALREADY EXISTS ###################################
// // $sql = "SELECT *, LOWER(HEX(id)) as id
// $sql = "SELECT LOWER(HEX(product.id)) as id
// FROM product_translation
// INNER JOIN product ON (product.id = product_translation.product_id)
// INNER JOIN product_category ON ( product_translation.product_id = product_category.product_id)
// -- INNER JOIN category ON ( product_category.category_id = category.category_id)
// -- INNER JOIN category_translation ON ( product_category.category_id = category_translation.category_id)
// WHERE LOWER(HEX(product_category.category_id)) = '".$this->zulauf_id."'
// -- AND JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_material_id') = '".$article['material_id']."'
// -- AND JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_materialgroup_id') = '".$article['materialgroup_id']."'
// AND product.product_number = '". $article['product_number_parent'] . "'
// AND parent_id IS NULL
// LIMIT 1
// ;";
// $stmt = $conn_shopware->prepare($sql);
// $stmt->execute();
// $search_product = $stmt->fetchAll();
// # TODO: IF PRODUCT ALREADY EXISTS
// if(isset($search_product[0]["id"]) && !empty($search_product[0]["id"]) && count($search_product) > 0) {
// $productParentId = $search_product[0]["id"];
// }
// # SET DATA FOR PRODUCT ###################################
// $ProductFlat = new \App\Controller\shopware\ProductFlat();
// $ProductFlat->setActive(true);
// // $ProductFlat->setCategories(array(array("id" => $this->getCategoryId($materialgruppenname, $materialgruppenid))));
// $ProductFlat->setCategories(array(array("id" => $this->zulauf_id)));
// $ProductFlat->setDeliveryTimeId($this->deliverytime_id);
// $ProductFlat->setDescription($article["article_name_1"]);
// $ProductFlat->setIsCloseout(false); // ABVERKAUF - Wichtig, da Anzahl = 0
// $ProductFlat->setMarkAsTopseller(false);
// // $ProductFlat->setName('Zulauf '.$article['product_number_parent'] .' ('. date('Y-m-d H:m:s') .')');
// // $ProductFlat->setName('Zulauf '.$article['product_number_parent']);
// // $ProductFlat->setName('ZULAUF: ' . $article['product_number_parent']);
// $ProductFlat->setName('Zulauf: ' . $article['material_name']);
// // $ProductFlat->setName('Zulauf '.$materialname);
// // $ProductFlat->setName('toller lustiger testname');
// $ProductFlat->setPrice(array(array(
// "currencyId" => "b7d2554b0ce847cd82f3ac9bd1c0dfca",
// "net" => 0,
// "gross" => 0,
// "linked" => false
// )));
// // $ProductFlat->setProductNumber($article['product_number']);
// // $ProductFlat->setProductNumber($article["materialgroup_id"]);
// $ProductFlat->setProductNumber($article['product_number_parent']);
// $ProductFlat->setPurchaseUnit(1);
// // $ProductFlat->setRestockTime();
// // $ProductFlat->setUnitId("ff7d882bd1da432bb4e05e21eda66114");
// $ProductFlat->setTaxId("c5ddfa75e22b4889b6f628e1df0b347f");
// // $ProductFlat->setCustomFields();
// $custom_fields = array();
// // $custom_fields["custom_article_surface"] = $article["surface_name"];
// // $custom_fields["custom_article_search_word"] = $article["search_word"];
// // $custom_fields["custom_article_name_1"] = $article["article_name_1"];
// // $custom_fields["custom_article_name_2"] = $article["article_name_2"];
// // $custom_fields["custom_article_material_id"] = $article["material_id"];
// // $custom_fields["custom_article_materialgroup_id"] = $article["materialgroup_id"];
// // // $custom_fields["custom_article_material_name"] = $materialname;
// // $custom_fields["custom_article_material_name"] = $article['material_name'];
// // $custom_fields["custom_article_materialgroup_name"] = $article["materialgroup_name"];
// // $custom_fields["custom_article_picture"] = $article["picture"];
// // $custom_fields["custom_article_material_id"] = $article["material_id"];
// // $custom_fields["custom_article_materialgroup_id"] = $article["materialgroup_id"];
// $custom_fields["custom_article_surface"] = $article["surface_name"];
// $custom_fields["custom_article_search_word"] = $article["search_word"];
// $custom_fields["custom_article_name_1"] = $article["article_name_1"];
// $custom_fields["custom_article_name_2"] = $article["article_name_2"];
// $custom_fields["custom_article_material_id"] = $article["material_id"];
// $custom_fields["custom_article_materialgroup_id"] = $article["materialgroup_id"];
// $custom_fields["custom_article_material_name"] = $article['material_name'];
// $custom_fields["custom_article_materialgroup_name"] = $article["materialgroup_name"];
// $custom_fields["custom_article_picture"] = $article["picture"];
// $custom_fields["custom_article_id"] = $article["article_id"];
// $custom_fields["custom_article_stone_number"] = $article["stone_number"];
// $custom_fields["custom_article_block_number"] = $article["block_advice"];
// $custom_fields["custom_article_search_word"] = $article["search_word"];
// $custom_fields["custom_article_advice"] = $article["advice"];
// $custom_fields["custom_article_storage"] = $article["storage"];
// $custom_fields["custom_article_amount_2"] = $article["amount_2"];
// $custom_fields["custom_article_articlegroup_name"] = $article["articlegroup_name"];
// $custom_fields["custom_article_fault_id"] = $article["fault_name"];
// $custom_fields["custom_article_fault_corner"] = $article["fault_corner"];
// $custom_fields["custom_article_additional_advice"] = $article["additional_advice"];
// $ProductFlat->setCustomFields(
// $custom_fields
// );
// # TODO: Materialfarbe
// // //Materialfarbe
// // unset($farbe);
// // if(isset($storage["materialgroup_id"]) && !empty($storage["materialgroup_id"]) &&
// // isset($storage["material_id"]) && !empty($storage["material_id"])){
// // $sql = "SELECT *
// // FROM materialcolors
// // INNER JOIN `keys` ON (materialcolors.color_id = key_number)
// // WHERE color_id != -1
// // AND key_kind = 88
// // AND mgr_id = '".$storage["materialgroup_id"]."'
// // AND mar_id = '".$storage["material_id"]."'
// // ;";
// // $stmt = $conn_shopware->prepare($sql);
// // $stmt->execute();
// // $colors = $stmt->fetchAll();
// // // return $this->render('update/index.html.twig', ["return" => ['success' => $colors ]]);
// // if(isset($colors) && is_array($colors) && count($colors) > 0){
// // $farbe = $colors[0]["key_text"];
// // }
// // }
// // if(isset($farbe) && !empty($farbe)){
// // $custom_fields["custom_article_material_color"] = $farbe;
// // }
// $ProductFlat->setHeight($article["thickness"]);
// $ProductFlat->setLength($article["length"]);
// $ProductFlat->setWidth($article["width"]);
// $ProductFlat->setStock(0);
// $ProductFlat->setMaxPurchase(0);
// $ProductFlat->setVisibilities(array(array(
// "salesChannelId" => $this->saleschannel_id,
// "visibility" => 30
// )));
// # UPDATE OR INSERT ###################################
// $poststring = 'api/product';
// $storage_option_list = $this->get_variant_ids($article); //übergibt storage, returned formatierten array für variantenerstellung
// # PARENT ###################################
// if( isset($article['material_name']) && trim($article['material_name']) != '') {
// if($productParentId == '') {
// # INSERT PARENT ARTICLE ##########################
// if( isset($storage_option_list) && is_array($storage_option_list) && count($storage_option_list) > 0) {
// # TODO: vorbereitung der Varianten
// // $ProductFlat->setConfiguratorGroupConfig(array(array(
// // // "id" => "5eb85799b5914547adf6348ff2213d5e",
// // // "representation" => "box",
// // // "expressionForListings" => false
// // )));
// $ProductFlat->setConfiguratorGroupConfig(array(
// // array(
// // "id" => $this->thickness_id,
// // "representation" => "box",
// // "expressionForListings" => false
// // ),
// // array(
// // "id" => $this->blocknumber_id,
// // "representation" => "box",
// // "expressionForListings" => false
// // ),
// // array(
// // "id" => $this->surface_id,
// // "representation" => "box",
// // "expressionForListings" => false
// // ),
// // array(
// // "id" => $this->fault_exists_id,
// // "representation" => "box",
// // "expressionForListings" => false
// // ),
// // array(
// // "id" => $this->color_id,
// // "representation" => "box",
// // "expressionForListings" => false
// // )
// array(
// "id" => $this->articlegroup_id,
// "representation" => "box",
// "expressionForListings" => false
// ),
// array(
// "id" => $this->surface_id,
// "representation" => "box",
// "expressionForListings" => false
// ),
// array(
// "id" => $this->thickness_id,
// "representation" => "box",
// "expressionForListings" => false
// ),
// // array(
// // "id" => $this->stonenumber_id,
// // "representation" => "box",
// // "expressionForListings" => false
// // ),
// array(
// "id" => $this->blocknumber_id,
// "representation" => "box",
// "expressionForListings" => false
// ),
// // array(
// // "id" => $this->fault_exists_id,
// // "representation" => "box",
// // "expressionForListings" => false
// // ),
// array(
// "id" => $this->color_id,
// "representation" => "box",
// "expressionForListings" => false
// )
// ));
// # SET CONFIGURATOR SETTINGS
// // dd($storage_option_list);
// // $ProductFlat->setConfiguratorSettings($storage_option_list);
// // $sol_option=array();
// // foreach($storage_option_list as $key => $sol) {
// // // $sol_option[$key]["productId"] = "";
// // $sol_option[$key]["id"] = $sol["optionId"];
// // }
// // $ProductFlat->setConfiguratorSettings($sol_option);
// try {
// $post_product = $this->client->request('POST', '/'.$poststring, array(
// // "debug" => true,
// 'allow_redirects'=>array('strict'=>true),
// "headers" => $this->header,
// "body" => $ProductFlat
// ));
// } catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
// exit;
// }
// # PARENT ID
// $productParentId = $post_product->getHeaders();
// $productParentId = str_replace($this->base_url.$poststring.'/','',$productParentId["Location"]); // ID des erstellten produktes
// $productParentId = $productParentId[0];
// }
// }
// # PARENT COVER ##########################
// // TODO: picture_parent
// # INSERT VARIANT ##########################
// $ProductFlat->setParentId($productParentId);
// $ProductFlat->setProductNumber($article["product_number"]);
// $ProductFlat->setConfiguratorGroupConfig(null);
// $ProductFlat->setConfiguratorSettings(null);
// // $ProductFlat->setName('Zulauf '.$article['product_number_parent']);
// // $ProductFlat->setName($article['product_number_parent']);
// $ProductFlat->setName('Zulauf: ' . $article['material_name']);
// $cover = new stdClass();
// $cover->type = "product_media";
// $cover->type = "product_media";
// $ProductFlat->setCover($cover);
// if( isset($storage_option_list) && is_array($storage_option_list) && count($storage_option_list) > 0) {
// $sol_option = [];
// foreach($storage_option_list as $key => $sol){
// $sol_option[$key]["id"] = $sol["optionId"];
// }
// $ProductFlat->setOptions( $sol_option );
// $sql = "SELECT LOWER(HEX(product.id)) as id
// FROM product
// -- WHERE LOWER( product_number ) = LOWER('".$ProductFlat->getProductNumber()."')
// WHERE product_number = ".$ProductFlat->getProductNumber()."
// LIMIT 1
// ;";
// $stmt = $conn_shopware->prepare($sql);
// $stmt->execute();
// $product_SELECT = $stmt->fetchAll();
// if(isset($product_SELECT) && is_array($product_SELECT) && count($product_SELECT) > 0) {
// # UPDATE
// $ProductFlat->setVisibilities([]);
// try {
// $post_product = $this->client->request('PATCH', '/api/product/'.$product_SELECT[0]['id'], array(
// // "debug" => true,
// 'allow_redirects'=>array('strict' => true),
// "headers" => $this->header,
// "body" => $ProductFlat
// ));
// } catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
// exit;
// }
// $productId = $product_SELECT[0]['id'];
// } else {
// # INSERT
// try {
// $post_product = $this->client->request('POST', '/api/product', array(
// // "debug" => true,
// 'allow_redirects'=>array('strict' => true),
// "headers" => $this->header,
// "body" => $ProductFlat
// ));
// } catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
// exit;
// }
// // # PARENT ID
// $productId = $post_product->getHeaders();
// $productId = str_replace($this->base_url.$poststring.'/','',$productId["Location"]); // ID des erstellten produktes
// $productId = $productId[0];
// }
// echo "<pre>"; var_dump($ProductFlat); exit;
// # UPDATE KEYWORDS ###################
// $keywords = [ $article['search_word'] ];
// try {
// $post_product = $this->client->request('PATCH', '/api/product/'.$productId, array(
// // "debug" => true,
// 'allow_redirects'=>array('strict' => true),
// 'headers' => $this->header,
// 'body' => json_encode([
// "customSearchKeywords"=> $keywords // DAS IST KEIN MERGE: WERTE WERDEN KNALLHART ÜBERSCHRIEBEN
// ])
// ));
// } catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
// exit;
// }
// # END UPDATE KEYWORDS ###################
// }
// echo "<pre>"; var_dump(__LINE__); exit;
// # UPDATE PARENT, FEHLENDE OPTIONEN IN VARIANTENGENERATOR HINZUFÜGEN ##########################
// $sql = "SELECT *
// , LOWER(HEX(product_id)) as product_id
// , LOWER(HEX(property_group_option_id)) as property_group_option_id
// FROM product_configurator_setting
// WHERE LOWER(HEX(product_id)) = '".$productParentId."'
// ;";
// $stmt = $conn_shopware->prepare($sql);
// $stmt->execute();
// $config_settings = $stmt->fetchAll();
// $option_list = array_column($storage_option_list, 'optionId');
// $config_list = array_column($config_settings, 'property_group_option_id');
// foreach($option_list as $option) {
// if(!in_array($option, $config_list)) {
// try {
// $post_product = $this->client->request('POST', '/api/product/'.$productParentId.'/configurator-settings', array(
// // "debug" => true,
// 'allow_redirects'=>array('strict'=>true),
// "headers" => $this->header,
// // 'body' => $ProductFlat
// 'body' => json_encode([
// "optionId"=>$option
// // 'name' => strval($i),
// // 'position' => $i
// ])
// ));
// } catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
// exit;
// }
// }
// }
// # PICTURES AND IMAGES ##########################
// # storages_picture
// $sql = "SELECT LOWER(HEX(id)) AS id
// FROM media
// WHERE file_name = '".pathinfo($article['storages_picture'], PATHINFO_FILENAME)."'
// LIMIT 1
// ;";
// $stmt = $conn_shopware->prepare($sql);
// $stmt->execute();
// $pictures = $stmt->fetchAll();
// if(!(isset($pictures) && is_array($pictures) && count($pictures) > 0)) {
// # MATERIALS_PICTURE
// $sql = "SELECT LOWER(HEX(id)) AS id
// FROM media
// WHERE file_name = '".pathinfo($article['materials_picture'], PATHINFO_FILENAME)."'
// LIMIT 1
// ;";
// $stmt = $conn_shopware->prepare($sql);
// $stmt->execute();
// $pictures = $stmt->fetchAll();
// if(!(isset($pictures) && is_array($pictures) && count($pictures) > 0)) {
// # DEFAULT_IMAGE
// $sql = "SELECT LOWER(HEX(id)) AS id
// FROM media
// WHERE file_name = '".pathinfo($this->default_image_filename, PATHINFO_FILENAME)."'
// LIMIT 1
// ;";
// $stmt = $conn_shopware->prepare($sql);
// $stmt->execute();
// $pictures = $stmt->fetchAll();
// }
// }
// // $sql = "SELECT LOWER(HEX(id)) AS id
// // FROM media
// // WHERE file_name = '".pathinfo($article['picture'], PATHINFO_FILENAME)."'
// // LIMIT 1
// // ;";
// // $stmt = $conn_shopware->prepare($sql);
// // $stmt->execute();
// // $pictures = $stmt->fetchAll();
// if(isset($pictures) && is_array($pictures) && count($pictures) > 0) {
// $pictureId = $pictures[0]['id'];
// # CHECK IF COVER IS ALREADY ASSIGNED
// $sql = "SELECT LOWER(HEX(media.id)) AS media_id,
// CONCAT (media.file_name, '.', media.file_extension) AS file_name
// FROM product
// INNER JOIN product_media ON ( product.cover = product_media.id)
// INNER JOIN media ON (product_media.media_id = media.id)
// WHERE LOWER(HEX(product.id)) = '".$productId."'
// LIMIT 1
// ;";
// $stmt = $conn_shopware->prepare($sql);
// $stmt->execute();
// $cover_SELECT = $stmt->fetchAll();
// if( !(isset($cover_SELECT) && is_array($cover_SELECT) && count($cover_SELECT) > 0)
// || (isset($cover_SELECT['filename']) && strtolower( $article['materials_picture'] ) != strtolower( $cover_SELECT['filename']) ) ) {
// # NO COVER ASSIGNED, OR DIFFERENT COVER:
// # ASSIGN IMAGE
// try{
// $post_product = $this->client->request('POST', '/api/product-media/', array(
// // 'debug' => true,
// 'allow_redirects'=>array('strict'=>true),
// 'headers' => $this->header,
// 'body' => json_encode([
// 'productId' => $productId,
// 'position' => 1,
// 'media' => array(
// 'id' => strtolower($pictureId)
// )
// ])
// ));
// $location = array_pop( $post_product->getHeaders()['Location'] );
// $productmediaId = basename($location);
// } catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
// exit;
// }
// # ASSIGN COVER
// try{
// // var_dump("cover zuordnen");
// $post_product = $this->client->request('PATCH', '/api/product/'.$productId, array(
// // 'debug' => true,
// 'allow_redirects'=>array('strict'=>true),
// 'headers' => $this->header,
// 'body' => json_encode([
// 'coverId' => strtolower($productmediaId)
// ])
// ));
// } catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
// exit;
// }
// }
// }
// # UPDATE DATETIME
// $sqlt = "UPDATE storages
// -- SET datetime_last_imported = '".date('Y-m-d H:i:s')."'
// SET upload_done = 1
// WHERE stone_number = '".$article['product_number']."'
// ;";
// $stmtt = $conn_akn->prepare($sqlt);
// $stmtt->execute();
// }
// }
// # NEXT PAGE
// if($nextPage == true){
// return $this->redirect('/import/products_zulauf');
// }
// # CLEAR CACHE ##########################
// $this->clear_cache();
// # DONE
// $response = new Response(json_encode(array('success' => true)));
// $response->headers->set('Content-Type', 'application/json');
// return $response;
// // return $this->render('update/index.html.twig', ["return" => ['success' => "Zulaufartikel importiert" ]]);
// }
/**
* @Route("/remove/products_zulauf", name="remove_products_zulauf")
* @Route("/remove/products_zulauf/{start}")
*
* FUNKTION:
* Zulaufartikel ist jetzt normaler Artikel => update
* Zulaufartikel wurde gelöscht => inaktiv
*
* Diese Funktion ist auch bekannt unter dem Namen "Zulaufumwandler"
*
* WICHTIGE INFORMATIONEN:
* Der Parent einer Variante ist das Material. ABER: für ein Produkt und einen Zulaufartikel gibt es zwei unterschiedliche
* Parents! Wird ein Zulaufartikel zu einem normalen Artikel kann also der Fall entstehen, dass der ParentArtikel noch nicht
* generiert wurde!!!
*
*
* @return mixed
*/
public function remove_products_zulauf($start = 0) {
$this->client_connect();
$conn_shopware = $this->shop_connect();
$conn_akn = $this->dump_connect();
$step_count = 6;
$sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,`Page`,UpdateDatetime)
VALUES(?,?,?,?)";
$stmtt = $conn_shopware->prepare($sql);
$stmtt->execute(array(
"remove/products_zulauf",
"Start",
$start,
date('Y-m-d H:i:s')
));
# GET ALL ZULAUFARTIKEL FROM SHOPWARE DATABASE
$sql = "SELECT LOWER(HEX(product_category.product_id)) AS product_id,
product_number AS product_number,
JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_material_id') AS custom_article_material_id,
JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_materialgroup_id') AS custom_article_materialgroup_id
FROM product_category
INNER JOIN product ON (product_category.product_id = product.id)
INNER JOIN product_translation ON (product.id = product_translation.product_id)
WHERE LOWER(HEX(product_category.category_id)) = '".$this->zulauf_id."'
AND parent_id IS NOT NULL
LIMIT ".$step_count."
OFFSET ".$start."
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$product_SELECT = $stmt->fetchAll();
if(isset($product_SELECT) && is_array($product_SELECT) && count($product_SELECT) > 0) {
# CHECK IF ZULAUFARTIKEL IS IN AKN DATABASE
foreach($product_SELECT as $product) {
$sql = "SELECT *
FROM storages
WHERE stone_number = '".$product['product_number']."'
;";
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
$articles = $stmt->fetchAll();
if(isset($articles) && is_array($articles) && count($articles) > 0) {
# PRODUCT IS STILL ACTIVE
$article = array_pop($articles);
if( strtoupper($article['storage']) != 'ZULAUF') {
# ZULAUFARTIKEL IST JETZT NORMALER ARTIKEL => REMOVE CATEGORY
try {
$post_product = $this->client->request('DELETE', '/api/product/'.$product['product_id'].'/categories/'. $this->zulauf_id, array(
'allow_redirects'=>array('strict'=>true),
"headers" => $this->header,
));
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
if ($e->hasResponse()) {
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
}else{
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
}
$response->headers->set('Content-Type', 'application/json');
return $response;
exit;
}
# SET NEW PARENT ######################
$sql = "SELECT LOWER(HEX(product.id)) as id, product_number
FROM product
WHERE product_number = '".$article['material_id'] . "_" . $article['materialgroup_id']."'
AND parent_id IS NULL
LIMIT 1
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$parent_SEARCH = $stmt->fetchAll();
if(isset($parent_SEARCH) && is_array($parent_SEARCH) && count($parent_SEARCH) > 0) {
# PARENT AS ARTICLE EXISTING
# PARENT ID
$productParentId = $parent_SEARCH[0]['id'];
} else {
# PARENT AS ARTICLE IS MISSING
# 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!
$ProductParentFlat = new \App\Controller\shopware\ProductFlat();
$ProductParentFlat->setActive(true);
$ProductParentFlat->setProductNumber($article['custom_article_material_id'] . '_' . $article['custom_article_materialgroup_id']);
$poststring = 'api/product';
try {
$post_product = $this->client->request('POST', '/'.$poststring, array(
// "debug" => true,
'allow_redirects'=>array('strict'=>true),
"headers" => $this->header,
"body" => $ProductParentFlat
));
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
if ($e->hasResponse()) {
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
}else{
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
}
$response->headers->set('Content-Type', 'application/json');
return $response;
exit;
}
# PARENT ID
$productParentId = $post_product->getHeaders();
$productParentId = str_replace($this->base_url.$poststring.'/', '', $productParentId["Location"]); // ID des erstellten produktes
$productParentId = $productParentId[0];
}
# END SET NEW PARENT ######################
$ProductFlat = new \App\Controller\shopware\ProductFlat();
$ProductFlat->setProductNumber( $product['product_number']);
$ProductFlat->setActive(false);
$ProductFlat->setParentId($productParentId);
# UPDATE
try {
$post_product = $this->client->request('PATCH', '/api/product/'.$product['product_id'], array(
// "debug" => true,
'allow_redirects'=>array('strict' => true),
"headers" => $this->header,
"body" => $ProductFlat
));
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
if ($e->hasResponse()) {
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
}else{
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
}
$response->headers->set('Content-Type', 'application/json');
return $response;
exit;
}
}
} else {
# ZULAUFARTIKEL WURDE GELÖSCHT => DELETE PRODUCT
$ProductFlat = new \App\Controller\shopware\ProductFlat();
$ProductFlat->setActive(false);
$ProductFlat->setProductNumber( $product['product_number']);
try {
$post_product = $this->client->request('PATCH', '/api/product/'.$product['product_id'], array(
// "debug" => true,
'allow_redirects'=>array('strict' => true),
"headers" => $this->header,
"body" => $ProductFlat
));
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
if ($e->hasResponse()) {
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
}else{
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
}
$response->headers->set('Content-Type', 'application/json');
return $response;
exit;
}
}
}
}
# NEXT PAGE ##########################
if(count($product_SELECT) >= $step_count) {
$start = $start + $step_count;
return $this->redirect('/remove/products_zulauf/'.$start);
} else {
// $nextPage = false;
}
# CLEAR CACHE ##########################
$this->clear_cache();
$sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,`Page`,UpdateDatetime)
VALUES(?,?,?,?)";
$stmtt = $conn_shopware->prepare($sql);
$stmtt->execute(array(
"remove/products_zulauf",
"ENDE",
$start,
date('Y-m-d H:i:s')
));
# DONE
$response = new Response(json_encode(array('success' => true)));
$response->headers->set('Content-Type', 'application/json');
return $response;
return $this->render('update/index.html.twig', ["return" => ['success' => "Zulaufartikel geloescht" ]]);
}
/**
* UPDATE IMAGES FROM AKN TO SHOPWARE
*
* @Route("/update/images", name="update_images")
*
* @return mixed
*/
public function update_images() {
die('this was just a test!');
// set_time_limit(200);
$this->client_connect();
$conn_shopware = $this->shop_connect();
$media_folder_id = "a7916c4069d04139b299328b8bd5c90c";
# GET ALL AKN-PRODUCTS
$sql = "SELECT *
, articles.material_id as material_id
, articles.materialgroup_id as materialgroup_id
, storages.id as storage_id
-- , CONCAT(storages.article_id, '_' ,storages.id) AS product_number
, CONCAT(storages.material_id, '_', storages.materialgroup_id, '_', storages.id) AS product_number
, CONCAT(storages.material_id, '_', storages.materialgroup_id) AS product_number_parent
, COALESCE(NULLIF(storages.picture, ''), NULLIF(materials.picture, ''), 'default.jpg') AS picture -- select picture
, COALESCE(NULLIF(materials.picture, ''), 'default.jpg') AS picture_parent -- select picture
FROM articles
LEFT JOIN materials ON (articles.material_id = materials.material_id AND articles.materialgroup_id = materials.materialgroup_id )
-- LEFT JOIN articlegroups ON (articlegroups.articlegroup_id = articles.article_id)
LEFT JOIN articlegroups ON (articles.articlegroup_id = articlegroups.articlegroup_id)
LEFT JOIN materialgroups ON (materialgroups.materialgroup_id = articles.materialgroup_id)
LEFT JOIN surfaces ON (surfaces.surface_id = articles.surface_id AND surfaces.materialgroup_id = articles.materialgroup_id)
INNER JOIN storages ON (articles.article_id = storages.article_id)
LEFT JOIN storagefaults ON (storages.fault_id = storagefaults.fault_id)
WHERE material_name IS NOT NULL
AND articles.material_id = 'ASBL'
-- AND storages.datetime_last_imported IS NULL -- not imported
ORDER BY articles.id ASC
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$articles = $stmt->fetchAll();
// dd($articles);
# REMOVE SUFFIX FROM PICTURE NAME
foreach($articles as &$article) {
if(strpos($article['picture'], '-##-')) {
$article['picture'] = substr($article['picture'], 0, -5);
}
// 1603_20181108_110050.jpg-
}
# END EDIT PICTURE NAME
foreach($articles as $article) {
# UPDATE PARENT
$sql = "SELECT *,
LOWER(HEX(product.id)) as product_id,
JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_picture') AS custom_article_picture,
CONCAT(media.file_name, '.' , media.file_extension) AS picture
FROM product
INNER JOIN product_translation ON (product.id = product_translation.product_id)
LEFT JOIN product_media ON (product.cover = product_media.id)
INNER JOIN media ON (product_media.media_id = media.id)
WHERE JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_material_id') = '".$article['material_id']."'
AND JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_materialgroup_id') = '".$article['materialgroup_id']."'
-- AND JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_materialgroup_stone_number') = '".$article['stone_number']."'
-- AND JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_materialgroup_surface') = '".$article['surface_name']."'
-- AND JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_materialgroup_fault_id') = '".$article['fault_id']."'
-- AND JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_materialgroup_fault_corner') = '".$article['fault_corner']."'
AND product.width = '". floatval($article['width']) ."'
AND product.height = '". floatval($article['thickness']) ."'
AND product.length = '". floatval($article['length']) ."'
AND product.parent_id IS NOT NULL
-- JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_materialgroup_id') =
-- WHERE product_number LIKE '".$article['product_number']."'
LIMIT 1
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$product_SELECT = $stmt->fetchAll();
if(isset($product_SELECT) && is_array($product_SELECT) && count($product_SELECT) > 0) {
$product = $product_SELECT[0];
if( strtolower($article['picture']) != strtolower($product['picture']) ) {
# PICTURE HAS TO BE UPDATED ###########################
$basename = $article['picture'];
$pathinfo = pathinfo( $article['picture'] );
$extension = $pathinfo['extension'];
$filename = $pathinfo['filename'];
# CHECK IF PICTURE IS ALREADY IN MEDIAPOOL
try {
$search = $this->client->request('GET', '/api/media?filter[fileName]='.$filename.'', array(
"headers" => $this->header,
));
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
if ($e->hasResponse()) {
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
}else{
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
}
$response->headers->set('Content-Type', 'application/json');
return $response;
exit;
}
$search_picture = json_decode($search->getBody()->getContents(), true);
if($search_picture['total'] == 0) {
# NEW PICTURE HAS TO BE UPLOADED
# CHECK IF IMAGE FILE EXISTS ON WEBSPACE
$path = '/public/produktbilder/'.$article['picture'];
if( !file_exists( $this->getParameter('kernel.project_dir').$path ) ) continue;
$mediaUrl = trim('https://'.$_SERVER['HTTP_HOST'].'/produktbilder/'.$basename);
$mediaUrl = str_replace(' ', '%20', $mediaUrl);
# UPLOAD PICTURE
$post_media = $this->client->post('/api/media', array(
// "debug" => true,
'allow_redirects'=>array('strict'=>true),
'headers' => $this->header,
'body' => json_encode([
'mediaFolderId' => $media_folder_id
])
));
$location = array_pop( $post_media->getHeaders()['Location'] );
$mediaId = basename($location);
// dd($mediaId);
try {
$post_media = $this->client->request('POST', '/api/_action/media/'.$mediaId.'/upload?extension='.$extension.'&fileName='.$filename, array(
// "debug" => true,
'allow_redirects'=>array('strict'=>true),
'headers' => $this->header,
'body' => json_encode([
'url' => $mediaUrl,
'mediaFolderId' => $media_folder_id,
])
));
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
if ($e->hasResponse()) {
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
}else{
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
}
$response->headers->set('Content-Type', 'application/json');
return $response;
exit;
}
} else {
# get media Id
try {
// $search = $this->client->request('GET', '/api/media?filter[fileName]='.$articleSW["custom_article_picture"].'', array(
$search = $this->client->request('GET', '/api/media?filter[fileName]='.$filename.'', array(
'headers' => $this->header,
));
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
if ($e->hasResponse()) {
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
}else{
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
}
$response->headers->set('Content-Type', 'application/json');
return $response;
exit;
}
$search_picture = json_decode($search->getBody()->getContents(),true);
$mediaId = $search_picture["data"][0]["id"];
}
# ASSIGN IMAGE
try{
$post_product = $this->client->request('POST', '/api/product-media/', array(
// 'debug' => true,
'allow_redirects'=>array('strict'=>true),
'headers' => $this->header,
'body' => json_encode([
'productId' => $product['product_id'],
'position' => 1,
'media' => array(
'id' => strtolower($mediaId)
)
])
));
$location = array_pop( $post_product->getHeaders()['Location'] );
$productmediaId = basename($location);
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
if ($e->hasResponse()) {
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
}else{
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
}
$response->headers->set('Content-Type', 'application/json');
return $response;
exit;
}
# ASSIGN COVER
try{
// var_dump("cover zuordnen");
$post_product = $this->client->request('PATCH', '/api/product/'.$product['product_id'], array(
// 'debug' => true,
'allow_redirects'=>array('strict'=>true),
'headers' => $this->header,
'body' => json_encode([
'coverId' => strtolower($productmediaId)
])
));
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
if ($e->hasResponse()) {
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
}else{
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
}
$response->headers->set('Content-Type', 'application/json');
return $response;
exit;
}
}
}
}
dd(__LINE__);
}
/**
* add oder update Bild bei Artikel
*
*/
public function add_image($bildname, $produktId, $zusatzbild)
{
$this->client_connect();
$conn_shopware = $this->shop_connect();
if( empty(trim($bildname)) || empty(trim($produktId)) ){
return false;
}
//hole gesetzte Mediafiles im Produkt ab
$sql = "SELECT
*
, LOWER(HEX(product_id)) as product_id
, LOWER(HEX(media_id)) as media_id
FROM product_media
INNER JOIN media ON (product_media.media_id = media.id)
WHERE LOWER(HEX(product_id)) = '".$produktId."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$mediaSW = $stmt->fetchAll();
$bildname = str_replace('"','',$bildname);
if(strpos($bildname,"-##-")){
$bildname= substr($bildname,0,-5);
}
$bildname = str_replace('"','',$bildname);
$article_file_new = preg_replace("/´|`|&/", ' ', $bildname);
$article_file_new = preg_replace("/\s\s+/", ' ', $article_file_new);
$article_file_new = str_replace(array('ü','ä','ö','+','ß'),array('ue','ae','oe','_','ss'),$article_file_new);
$filename= explode(".", $article_file_new);
$type = array_pop($filename);
$filename = implode('.', $filename);
$filename = trim(preg_replace("/\.+$/",'',$filename));
// prüfen ob Bild schon vorhanden ist
try {
$search = $this->client->request('GET', '/api/media?filter[fileName]='.$filename.'', array(
"headers" => $this->header,
));
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
if ($e->hasResponse()) {
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
}else{
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
}
$response->headers->set('Content-Type', 'application/json');
return $response;
exit;
}
$search_picture = json_decode($search->getBody()->getContents(),true);
if($search_picture["total"] > 0){
// return $this->render('update/index.html.twig', ["return" => ['search picture' => $search_picture, 'media url' => $mediaUrl ]]);
$Id = $search_picture["data"][0]["id"];
if($zusatzbild){
// prüfe ob verknüpfung zwischen bild und produkt existiert, wenn nicht erstelle sie
$sql = "SELECT
*
, LOWER(HEX(product_id)) as product_id
, LOWER(HEX(media_id)) as media_id
FROM product_media
INNER JOIN media ON (product_media.media_id = media.id)
WHERE LOWER(HEX(product_id)) = '".$produktId."'
AND file_name = '".$filename."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$product_media = $stmt->fetchAll();
if(isset($product_media) && !empty($product_media) && count($product_media) > 0){
}else{
try{
$post_product = $this->client->request('POST', '/api/product-media/', array(
// 'debug' => true,
'allow_redirects'=>array('strict'=>true),
'headers' => $this->header,
'body' => json_encode([
'productId' => $produktId,
'position' => 2,
'media' => array(
'id' => strtolower($Id)
)
])
));
// $location = array_pop( $post_product->getHeaders()['Location'] );
// $productmediaId = basename($location);
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
if ($e->hasResponse()) {
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
}else{
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
}
$response->headers->set('Content-Type', 'application/json');
return $response;
exit;
}
}
}else{
$sql = "DELETE
FROM product_media
WHERE LOWER(HEX(product_id)) = '".$produktId."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
try{
$post_product = $this->client->request('POST', '/api/product-media/', array(
// 'debug' => true,
'allow_redirects'=>array('strict'=>true),
'headers' => $this->header,
'body' => json_encode([
'productId' => $produktId,
'position' => 1,
'media' => array(
'id' => strtolower($Id)
)
])
));
$location = array_pop( $post_product->getHeaders()['Location'] );
$productmediaId = basename($location);
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
if ($e->hasResponse()) {
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
}else{
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
}
$response->headers->set('Content-Type', 'application/json');
return $response;
exit;
}
// ADD COVER
try{
// return var_dump("cover zuordnen". $produktId." - ".$productmediaId);
$post_product = $this->client->request('PATCH', '/api/product/'.$produktId, array(
// 'debug' => true,
'allow_redirects'=>array('strict'=>true),
'headers' => $this->header,
'body' => json_encode([
'coverId' => strtolower($productmediaId)
])
));
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
if ($e->hasResponse()) {
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
}else{
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
}
$response->headers->set('Content-Type', 'application/json');
return $response;
exit;
}
}
}
return true;
}
public function clear_cache() {
try {
$post_product = $this->client->request('DELETE', '/api/_action/cache', array(
// "debug" => true,
'allow_redirects'=>array('strict'=>true),
"headers" => $this->header,
));
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
if ($e->hasResponse()) {
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
}else{
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
}
$response->headers->set('Content-Type', 'application/json');
return $response;
exit;
}
}
/**
* Importiert SQL
*
* @Route("/import/sql", name="import_sql")
*
* @return mixed
*/
public function import_sql() {
$this->client_connect();
$conn_akn = $this->dump_connect();
// $conn_shopware = $this->shop_connect();
// $sql = "INSERT INTO pixel (pixel_datetime, pixel_function, pixel_text)
// VALUES (now(), '".__FUNCTION__."', 'sql import START')
// ;";
// $stmt = $conn_akn->prepare($sql);
// $stmt->execute();
$sql_file = $this->getParameter('kernel.project_dir').'/public/sql/import.sql';
if( file_exists($sql_file)) {
$sql_import = file_get_contents($sql_file);
$sql_import = str_replace(["\n", "\r"], "", $sql_import);
// $sql_import = str_replace("`", "", $sql_import);
// $sql_import = str_replace(", group,", ", `group`,", $sql_import);
// $sql_import = str_replace(", index,", ", `index`,", $sql_import);
$sql_import = explode(';', $sql_import);
foreach($sql_import as $query) {
if( trim($query) != '') {
# DO SQL STUFF #################
# SKIP
if( str_starts_with( strtolower( trim( $query ) ), 'use' )) continue;
// if( str_starts_with( strtolower( trim( $query ) ), 'truncate' )) continue;
$sql = $query.';';
// var_dump($query);
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
// $data = $stmt->fetchAll();
# TODO: CATCH ERRORS
}
}
}
// $sql = "INSERT INTO pixel (pixel_datetime, pixel_function, pixel_text)
// VALUES (now(), '".__FUNCTION__."', 'sql import DONE')
// ;";
// $stmt = $conn_akn->prepare($sql);
// $stmt->execute();
# DONE
return $this->render('update/index.html.twig', ["return" => ['success' => "SQL importiert" ]]);
}
/**
* Import Customers
*
* @Route("/import/customers", name="import_customers")
*
* @return mixed
*/
public function import_customers() {
$this->client_connect();
$conn_shopware = $this->shop_connect();
$conn_akn = $this->dump_connect();
$step_count = 50;
$sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,UpdateDatetime)
VALUES(?,?,?)";
$stmtt = $conn_shopware->prepare($sql);
$stmtt->execute(array(
"import/customers",
"Start",
date('Y-m-d H:i:s')
));
# CONSTANTS
$payment_method_id = $this->payment_method_id; // Nachname
// $salutation_id = 'f81f1e5f8dee45b2b719f56879544b08'; // Firma
$salutation_id = $this->salutation_id; // Keine Angabe
$group_id = $this->group_id; // Standard Kundengruppe
$country_id = $this->country_id; // Deutschland
# GET ALL CUSTOMERS
$sql = "SELECT *
, customers.customer_number as customer_number
, customers.email as customer_email
, k1.key_text as price_list
, k2.key_text as salesman
, k3.key_text as salesarea
, k4.key_text as tour
, k5.key_text as payment_term
, k6.key_text as delivery_term
, k7.key_text as `group`
FROM customers
LEFT JOIN contacts ON (customers.customer_number = contacts.customer_number)
LEFT JOIN `keys` as k1 ON (k1.key_number = customers.price_list AND k1.key_kind = 7)
LEFT JOIN `keys` as k2 ON (k2.key_number = customers.salesman AND k2.key_kind = 8)
LEFT JOIN `keys` as k3 ON (k3.key_number = customers.sales_area AND k3.key_kind = 9)
LEFT JOIN `keys` as k4 ON (k4.key_number = customers.tour AND k4.key_kind = 52)
LEFT JOIN `keys` as k5 ON (k5.key_number = customers.payment_term AND k5.key_kind = 4)
LEFT JOIN `keys` as k6 ON (k6.key_number = customers.delivery_term AND k6.key_kind = 5)
LEFT JOIN `keys` as k7 ON (k7.key_number = customers.group AND k7.key_kind = 3)
WHERE customers.customer_number IS NOT NULL
AND customers.street IS NOT NULL AND customers.street !=''
AND customers.email IS NOT NULL AND customers.email !='' AND customers.email LIKE '%@%'
AND name_1 IS NOT NULL AND name_1 != ''
AND upload_done IS NULL
-- AND customers.customer_number = '14254' -- #TODO: REMOVE THIS LINE!!!
LIMIT ".$step_count."
;";
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
$customers_SELECT = $stmt->fetchAll();
// return $this->render('update/index.html.twig', ["return" => [ 'zeit' => $customers_SELECT]]);
foreach($customers_SELECT as $customer) {
$CustomerFlat = new \App\Controller\shopware\CustomerFlat();
$CustomerFlat->setActive(true);
// $first_name = $customer['name_1'].' '.$customer['name_2'].' '.$customer['name_3'];
// $last_name = 'Import-neu';
// $company = '-';
$first_name = $customer['name_1'];
if(isset($customer['name_2']) && !empty($customer['name_2']) && $customer['name_2'] != null){
$last_name = $customer['name_2'];
}else{
$last_name = "-";
}
if(isset($customer['name_3']) && !empty($customer['name_3']) && $customer['name_3'] != null){
$company = $customer['name_3'];
}else{
$company = "-";
}
// Erzeuge Titel wenn noch nicht vorhanden
if(isset($customer["title"]) && !empty($customer["title"]) && $customer["title"] != null){
$sql = "SELECT *
, LOWER(HEX(salutation_id)) as salutation_id
FROM salutation_translation
WHERE LOWER(HEX(language_id)) = '".$this->language_id."'
AND display_name = '".trim($customer["title"])."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$salutation_SELECT = $stmt->fetchAll();
if(isset($salutation_SELECT) && is_array($salutation_SELECT) && count($salutation_SELECT) > 0){
// wenns gesetzt ist
$salutation_id = $salutation_SELECT[0]["salutation_id"];
}else{
// generiere salutation und setze id
try {
$post_customer = $this->client->request('POST', '/api/salutation', array(
// 'debug' => true,
'allow_redirects'=>array('strict'=>true),
'headers' => $this->header,
'body' => json_encode([
'salutationKey' => strtolower($customer["title"]),
'displayName' => $customer["title"],
'letterName' => " "
])
));
$location = array_pop($post_customer->getHeaders()['Location'] );
$salutation_id = basename($location);
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
if ($e->hasResponse()) {
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
}else{
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
}
$response->headers->set('Content-Type', 'application/json');
return $response;
exit;
}
}
}else{
$salutation_id = $this->salutation_id; // Keine Angabe
}
// Erzeuge Standardzahlungsart wenn noch nicht vorhanden
if(isset($customer['payment_term']) && !empty($customer['payment_term']) && $customer['payment_term'] != null){
$sql = "SELECT *
, LOWER(HEX(payment_method_id)) as payment_method_id
FROM payment_method_translation
WHERE LOWER(HEX(language_id)) = '".$this->language_id."'
AND name = '".trim($customer['payment_term'])."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$payment_SELECT = $stmt->fetchAll();
if(isset($payment_SELECT) && is_array($payment_SELECT) && count($payment_SELECT) > 0){
// wenns gesetzt ist
$payment_method_id = $payment_SELECT[0]["payment_method_id"];
}else{
// generiere zahlungsart und setze id
try {
$post_customer = $this->client->request('POST', '/api/payment-method', array(
// 'debug' => true,
'allow_redirects'=>array('strict'=>true),
'headers' => $this->header,
'body' => json_encode([
'name' => trim($customer['payment_term']),
'active' => true
])
));
$location = array_pop( $post_customer->getHeaders()['Location'] );
$payment_method_id = basename($location);
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
if ($e->hasResponse()) {
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
}else{
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
}
$response->headers->set('Content-Type', 'application/json');
return $response;
exit;
}
$sql = "INSERT INTO sales_channel_payment_method (sales_channel_id, payment_method_id)
VALUES (UNHEX('".$this->saleschannel_id."'), UNHEX('".$payment_method_id."'))
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
}
}else{
$payment_method_id = $this->payment_method_id; // Rechnung
}
// Ende Standardzahlungsart
// // Erzeuge Standardlieferart wenn noch nicht vorhanden
// if(isset($customer['payment_term']) && !empty($customer['payment_term']) && $customer['payment_term'] != null){
// $sql = "SELECT *
// , LOWER(HEX(payment_method_id)) as payment_method_id
// FROM payment_method_translation
// WHERE LOWER(HEX(language_id)) = '".$this->language_id."'
// AND name = '".trim($customer['payment_term'])."'
// ;";
// $stmt = $conn_shopware->prepare($sql);
// $stmt->execute();
// $payment_SELECT = $stmt->fetchAll();
// if(isset($payment_SELECT) && is_array($payment_SELECT) && count($payment_SELECT) > 0){
// // wenns gesetzt ist
// $payment_method_id = $payment_SELECT[0]["payment_method_id"];
// }else{
// // generiere zahlungsart und setze id
// try {
// $post_customer = $this->client->request('POST', '/api/payment-method', array(
// // 'debug' => true,
// 'allow_redirects'=>array('strict'=>true),
// 'headers' => $this->header,
// 'body' => json_encode([
// 'name' => trim($customer['payment_term']),
// 'active' => true
// ])
// ));
// $location = array_pop( $post_customer->getHeaders()['Location'] );
// $payment_method_id = basename($location);
// } catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
// exit;
// }
// $sql = "INSERT INTO sales_channel_payment_method (sales_channel_id, payment_method_id)
// VALUES (UNHEX('f8c383f75225488a8f6b89b7d64d6cf2'), UNHEX('".$payment_method_id."'))
// ;";
// $stmt = $conn_shopware->prepare($sql);
// $stmt->execute();
// }
// }else{
// $payment_method_id = $this->payment_method_id; // Rechnung
// }
// // Ende Standardlieferart
// Erzeuge Kundengruppe wenn noch nicht vorhanden
if(isset($customer['group']) && !empty($customer['group']) && $customer['group'] != null){
$sql = "SELECT *
, LOWER(HEX(customer_group_id)) as customer_group_id
FROM customer_group_translation
WHERE LOWER(HEX(language_id)) = '".$this->language_id."'
AND name = '".trim($customer['group'])."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$customer_group_SELECT = $stmt->fetchAll();
if(isset($customer_group_SELECT) && is_array($customer_group_SELECT) && count($customer_group_SELECT) > 0){
// wenns gesetzt ist
$group_id = $customer_group_SELECT[0]["customer_group_id"];
}else{
// generiere kundengruppe und setze id
try {
$post_customer = $this->client->request('POST', '/api/customer-group', array(
// 'debug' => true,
'allow_redirects'=>array('strict'=>true),
'headers' => $this->header,
'body' => json_encode([
'name' => trim($customer['group'])
])
));
$location = array_pop( $post_customer->getHeaders()['Location'] );
$group_id = basename($location);
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
if ($e->hasResponse()) {
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
}else{
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
}
$response->headers->set('Content-Type', 'application/json');
return $response;
exit;
}
}
}else{
$group_id = $this->group_id; // Default Kundengruppe
}
// Ende Kundengruppe
// return $this->render('update/index.html.twig', ["return" => [ 'zeit' => $group_id]]);
# GET LANGUAGE ID
$language_name = ( trim($customer['country_indicator']) == '' || $customer['country_indicator'] == 'DE') ? 'Deutsch' : 'English';
$sql = "SELECT LOWER(HEX(id)) AS language_id, language.name
FROM language
WHERE language.name = '".$language_name."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$language_SELECT = $stmt->fetchAll();
$language_id = $language_SELECT[0]['language_id']??$this->language_id;
$CustomerFlat->setLanguageId($language_id);
# END GET LANGUAGE ID
# THIS VALUES SHOULD NOT BE BLANK:
$CustomerFlat->setGroupId($group_id);
$CustomerFlat->setDefaultPaymentMethodId($payment_method_id);
$CustomerFlat->setSalesChannelId($this->saleschannel_id);
// $CustomerFlat->setDefaultBillingAddressId();
// $CustomerFlat->setDefaultShippingAddressId();
$CustomerFlat->setSalutationId($salutation_id);
$CustomerFlat->setCustomerNumber($customer['customer_number']);
$CustomerFlat->setLastName($last_name);
# END THIS VALUES SHOULD NOT BE BLANK:
$CustomerFlat->setFirstName(trim($first_name));
if(strpos($customer['email'],"@")){
$CustomerFlat->setEmail($customer['email']);
}elseif(strpos($customer['customer_email'],"@")){
$CustomerFlat->setEmail($customer['customer_email']);
}else{
# UPDATE FAILED BECAUSE EMAIL IS INVALID
$sqlt = "UPDATE customers
SET upload_done = 1, upload_failed = 1
WHERE customer_number = '".$customer['customer_number']."'
;";
$stmtt = $conn_akn->prepare($sqlt);
$stmtt->execute();
continue;
}
$CustomerFlat->setCompany($company);
# CUSTOM FIELDS
$CustomerFlat->setCustomFields(array(
'custom_customer_telefon_1' => $customer['telefon_1']??'',
'custom_customer_telefon_2' => $customer['telefon_2']??'',
'custom_customer_fax' => $customer['fax']??'',
'custom_customer_homepage' => $customer['homepage']??'',
'custom_customer_payment_term' => $customer['payment_term']??'',
'custom_customer_delivery_term' => $customer['delivery_term']??'',
'custom_customer_price_list' => $customer['price_list']??'',
'custom_customer_salesman' => $customer['salesman']??'',
'custom_customer_salesarea' => $customer['salesarea']??'',
'custom_customer_tour' => $customer['tour']??'',
'custom_customer_grouping' => $customer['grouping']??'',
));
# END CUSTOM FIELDS
# LIEFERADRESSE
$CustomerShippingAddressFlat = new \App\Controller\shopware\CustomerAddressFlat();
# GET COUNTRY ID
if(trim($customer['country_indicator']) == '') $customer['country_indicator'] = 'DE';
$sql = "SELECT LOWER(HEX(id)) AS country_id, country.iso
FROM country
-- WHERE country.iso = 'DE'
WHERE country.iso = '" . $customer['country_indicator'] . "'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$country_SELECT = $stmt->fetchAll();
if(!isset($country_SELECT[0]['country_id']) || empty($country_SELECT[0]['country_id']) || $country_SELECT[0]['country_id'] == ''){
// dd($customer);
# UPDATE FAILED BECAUSE COUNTRY CODE IS INVALID
$sqlt = "UPDATE customers
SET upload_done = 1, upload_failed = 2
WHERE customer_number = '".$customer['customer_number']."'
;";
$stmtt = $conn_akn->prepare($sqlt);
$stmtt->execute();
continue;
}
$country_id = $country_SELECT[0]['country_id']??$country_id;
$CustomerShippingAddressFlat->setCountryId(strtolower($country_id));
$CustomerShippingAddressFlat->setSalutationId($salutation_id);
$CustomerShippingAddressFlat->setFirstName(trim($first_name));
$CustomerShippingAddressFlat->setLastName($last_name);
$CustomerShippingAddressFlat->setCompany($company);
$CustomerShippingAddressFlat->setZipcode($customer['postcode']);
$CustomerShippingAddressFlat->setCity($customer['place']);
$CustomerShippingAddressFlat->setStreet($customer['street']);
$CustomerShippingAddressFlat->setPhoneNumber($customer['telefon_1']);
# END LIEFERADRESSE
# CHECK IF CUSTOMER ALREADY EXISTS
$sql = "SELECT LOWER(HEX(id)) AS customer_id
FROM customer
WHERE customer_number = '".$customer['customer_number']."'
LIMIT 1
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$shopware_customer_SELECT = $stmt->fetchAll();
if(!(isset($shopware_customer_SELECT) && is_array($shopware_customer_SELECT) && count($shopware_customer_SELECT) > 0)) {
# INSERT ##########################
$CustomerFlat->setDefaultShippingAddress($CustomerShippingAddressFlat);
// $CustomerFlat->setDefaultBillingAddress($CustomerShippingAddressFlat);
try {
$post_customer = $this->client->request('POST', '/api/customer', array(
// 'debug' => true,
'allow_redirects'=>array('strict'=>true),
'headers' => $this->header,
'body' => $CustomerFlat
));
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
if ($e->hasResponse()) {
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
}else{
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
}
$response->headers->set('Content-Type', 'application/json');
return $response;
exit;
}
} else {
$customer_id = strtolower($shopware_customer_SELECT[0]['customer_id']);
# UPDATE ##########################
try {
$post_product = $this->client->request('PATCH', '/api/customer/'.$customer_id.'', array(
"headers" => $this->header,
"body" => $CustomerFlat
));
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
if ($e->hasResponse()) {
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
}else{
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
}
$response->headers->set('Content-Type', 'application/json');
return $response;
exit;
}
# UPDATE Adresse ##########################
# GET ADRESS-ID
$sql = "SELECT LOWer(HEX(id)) AS address_id
FROM customer_address
WHERE LOWER(HEX(customer_id)) = '".$customer_id."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$address_SELECT = $stmt->fetchAll();
if(isset($address_SELECT) && is_array($address_SELECT) && count($address_SELECT) > 0) {
$addresses = $address_SELECT;
# UPDATE Adresses with new values
foreach( $addresses as $address) {
$address_id = $address['address_id'];
try {
$post_product = $this->client->request('PATCH', '/api/customer/'.$customer_id.'/addresses/'.$address_id.'', array(
"headers" => $this->header,
"body" => $CustomerShippingAddressFlat
));
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
if ($e->hasResponse()) {
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
}else{
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
}
$response->headers->set('Content-Type', 'application/json');
return $response;
exit;
}
}
}
}
# UPDATE DATETIME ##########################
$sqlt = "UPDATE customers
-- SET datetime_last_imported = '".date('Y-m-d H:i:s')."'
SET upload_done = 1
WHERE customer_number = '".$customer['customer_number']."'
;";
$stmtt = $conn_akn->prepare($sqlt);
$stmtt->execute();
// dd(__LINE__);
}
# NEXT PAGE ##########################
if(count($customers_SELECT) >= $step_count) {
// $nextPage = true;
return $this->redirect('/import/customers');
} else {
// $nextPage = false;
}
// setze
$sqlt = "UPDATE customers
SET upload_done = 1, upload_failed = 1
WHERE customer_number = ''
OR (upload_done IS NULL AND upload_failed IS NULL)
;";
$stmtt = $conn_akn->prepare($sqlt);
$stmtt->execute();
# CLEAR CACHE ##########################
$this->clear_cache();
$sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,UpdateDatetime)
VALUES(?,?,?)";
$stmtt = $conn_shopware->prepare($sql);
$stmtt->execute(array(
"import/customers",
"ENDE",
date('Y-m-d H:i:s')
));
# DONE ##########################
$response = new Response(json_encode(array('success' => true)));
$response->headers->set('Content-Type', 'application/json');
return $response;
return $this->render('update/index.html.twig', ["return" => ['success' => "Kunden importiert" ]]);
}
/**
* update_categories
*
* @Route("/update/categories", name="update_categories")
* @Route("/update/categories/{start}")
*
* @return mixed
*/
public function update_categories($start = 0) {
$this->client_connect();
$conn_shopware = $this->shop_connect();
$step_count = 3;
# GET ALL CATEGORIES
$sql = "SELECT LOWER(HEX(id)) AS category_id, category_translation.name, active
FROM category
INNER JOIN category_translation ON (category.id = category_translation.category_id)
WHERE LOWER(HEX(parent_id)) = '".$this->default_category_id."'
LIMIT ".$step_count."
OFFSET ".$start."
;";
// -- WHERE LOWER(HEX(parent_id)) = '".$this->steine_aus_aller_welt_category_id."'
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$categories_SELECT = $stmt->fetchAll();
// echo "<pre>"; var_dump($categories_SELECT); exit;
if(isset($categories_SELECT) && is_array($categories_SELECT) && count($categories_SELECT) > 0) {
$categories = $categories_SELECT;
foreach($categories as $category) {
# SELECT PRODUCTS WITH CATEGORY
$sql = "SELECT LOWER(HEX(id)) AS category_id, category_translation.name
FROM category
INNER JOIN category_translation ON (category.id = category_translation.category_id)
WHERE LOWER(HEX(parent_id)) = '".$this->default_category_id."'
;";
// -- WHERE LOWER(HEX(parent_id)) = '".$this->steine_aus_aller_welt_category_id."'
$sql = "SELECT product_number
FROM product
INNER JOIN product_category ON (product.id = product_category.product_id)
INNER JOIN category ON (product_category.category_id = category.id)
WHERE LOWER(HEX(category.id)) = '".$category['category_id']."'
LIMIT 3
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$products_SELECT = $stmt->fetchAll();
# IF NO PRODUCT DISABLE CATEGORY
$CategoryFlat = new \App\Controller\shopware\CategoryFlat();
// if (isset($products_SELECT) && is_array($products_SELECT) && count($products_SELECT) > 0) {
// }
// if ($category["category_id"] == "91bb5a08283d40a3bf1ec0a4759660b1") {
// echo "<pre>"; var_dump($category); var_dump($products_SELECT); exit;
// }
$commit = false;
if(isset($products_SELECT) && is_array($products_SELECT) && count($products_SELECT) > 0) {
# ACTIVATE
if ($category["active"] == 0) {
$CategoryFlat->setActive(true);
$commit = true;
}
} else {
# DISABLE
if ($category["active"] == 1) {
$CategoryFlat->setActive(false);
$commit = true;
}
}
// $CategoryFlat->setActive(true);
if ($commit == true) {
try {
$post_product = $this->client->request('PATCH', '/api/category/'.$category['category_id'], array(
// "debug" => true,
'allow_redirects'=>array('strict' => true),
'headers' => $this->header,
'body' => $CategoryFlat,
));
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
if ($e->hasResponse()) {
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()), 'response' => Psr7\str($e->getResponse()))));
}else{
$response = new Response(json_encode(array('request' => Psr7\str($e->getRequest()))));
}
$response->headers->set('Content-Type', 'application/json');
return $response;
exit;
}
}
}
}
# NEXT PAGE ##########################
if(count($categories_SELECT) >= $step_count) {
$start = $start + $step_count;
return $this->redirect('/update/categories/'. $start);
}
# CLEAR CACHE ##########################
$this->clear_cache();
# DONE ##########################
return $this->redirect('/categories/visibility');
// $response = new Response(json_encode(array('success' => true)));
// $response->headers->set('Content-Type', 'application/json');
// return $response;
// return $this->render('update/index.html.twig', ["return" => ['success' => "Kunden importiert" ]]);
}
/**
* Pixelproductions testet
*
* @Route("/pixel_test", name="pixel_test")
*
* @return mixed
*/
public function pixel_test() {
die('No testing!');
$directory = $this->getParameter('kernel.project_dir').'/public/produktbilder';
$files = array_values(array_diff( scandir($directory), array('.','..')));
$test_file = $files[0]; // 2319_P1030278 Nero Impala Rustenburg geflammt 2´cm 218070548.jpg
// $test_file = html_entity_decode($test_file, ENT_QUOTES);
// $test_file = preg_replace('/(\'|�*39;)/', '', $test_file);
// $test_file = preg_replace("/´/", '', $test_file);
$test_file = '2319_P1030278 Nero Impala Rustenburg geflammt 2´cm 218070548.jpg';
$new_filename = preg_replace("/´/", '', $test_file);
dd($new_filename);
}
/**
* Pixelproductions testet
*
* @Route("/pixel_mail", name="pixel_mail")
*
* @return mixed
*/
public function sendEmail(MailerInterface $mailer): Response {
die('No EMails!');
// var_dump( urlencode('bN+y6M=T8b4s') ); exit; // bN%2By6M%3DT8b4s
// var_dump( urlencode('helpdesk@pixelproductions.de') ); exit; // helpdesk%40pixelproductions.de
$email = (new Email())
->from('cw@pixelproductions.de')
->to('mo@pixelproductions.de')
//->cc('cc@example.com')
//->bcc('bcc@example.com')
//->replyTo('fabien@example.com')
//->priority(Email::PRIORITY_HIGH)
->subject('Time for Symfony Mailer!')
->text('Sending emails is fun again!')
->html('<p>See Twig integration for better HTML integration!</p>');
$mailer->send($email);
// ...
}
}