Symfony Profiler

<?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 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 ImportController 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 Datenbank
private $shopware_data_live; // Shopware Datenbank LIVE
private $shopware_data_dump; // AKN Datenbank Dump
private $shopware_data_dump_live; // AKN Datenbank Dump LIVE
private $property_group_id; // Eigenschaften Gruppe ID Varianten
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 $material_category_layout_id; //Default Materialkategorie Layout ID
private $category_layout_id; // Default Kategorie Layout ID
// private $default_online_id; // Default Onlinelager 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 $blocknumber_id; // Eigenschaften Gruppe ID Blocknummer
private $stonenumber_id; // Eigenschaften Gruppe ID Steinnummer
private $default_image_filename; // Default Bildername
private $language_id; // ID der Defaultsprache
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 $sa_id; // Eigenschaften Gruppe ID Sa
private $squaremeterprice_id; // Eigenschaften Gruppe ID Quadratmeterpreis
private $artikeldaten_innen_id; // Eigenschaften Gruppe ID Innen
private $artikeldaten_aussen_id; // Eigenschaften Gruppe ID Außen
private $artikeldaten_ids; // Liste der Artikeldaten EIgenschaften
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 $media_folder_id; // ID des Mediafolders für den Bilderupload
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 Datenbank
$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->default_online_id = $_ENV['default_online_id']; // Default Onlinelager ID
$this->category_layout_id = $_ENV['category_layout_id']; // Default Kategorie Layout ID
$this->material_category_layout_id = $_ENV["material_category_layout_id"]; // Default Materialkategorie 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->stonenumber_id = $_ENV['stonenumber_id']; // Eigenschaften Gruppe ID Steinnummer
$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->sa_id=$_ENV['sa_id']; // Eigenschaften Gruppe ID Sa
$this->squaremeterprice_id=$_ENV['squaremeterprice_id']; // Eigenschaften Gruppe ID Quadratmeterpreis
$this->artikeldaten_innen_id=$_ENV['artikeldaten_innen_id']; // Eigenschaften Gruppe ID Innen
$this->artikeldaten_aussen_id=$_ENV['artikeldaten_aussen_id']; // Eigenschaften Gruppe ID Außen
$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)
$this->media_folder_id=$_ENV["media_folder_id"]; // ID des Mediafolders für den Bilderupload
$this->artikeldaten_ids=array();
array_push($this->artikeldaten_ids,$_ENV['artikeldaten_innen_kuechen_ja']); // Eigenschaft Innen Küchen Ja ID
array_push($this->artikeldaten_ids,$_ENV['artikeldaten_innen_kuechen_nein']); // Eigenschaft Innen Küchen Nein ID
array_push($this->artikeldaten_ids,$_ENV['artikeldaten_innen_fenster_ja']); // Eigenschaft Innen Fenster Ja ID
array_push($this->artikeldaten_ids,$_ENV['artikeldaten_innen_fenster_nein']); // Eigenschaft Innen Fenster Nein ID
array_push($this->artikeldaten_ids,$_ENV['artikeldaten_innen_bad_ja']); // Eigenschaft Innen Bad Ja ID
array_push($this->artikeldaten_ids,$_ENV['artikeldaten_innen_bad_nein']); // Eigenschaft Innen Bad Nein ID
array_push($this->artikeldaten_ids,$_ENV['artikeldaten_innen_fussboden_ja']); // Eigenschaft Innen Fußboden Ja ID
array_push($this->artikeldaten_ids,$_ENV['artikeldaten_innen_fussboden_nein']); // Eigenschaft Innen Fußboden Nein ID
array_push($this->artikeldaten_ids,$_ENV['artikeldaten_innen_boden_ja']); // Eigenschaft Innen Boden Ja ID
array_push($this->artikeldaten_ids,$_ENV['artikeldaten_innen_boden_nein']); // Eigenschaft Innen Boden Nein ID
array_push($this->artikeldaten_ids,$_ENV['artikeldaten_aussen_boden_ja']); // Eigenschaft Außen Boden Ja ID
array_push($this->artikeldaten_ids,$_ENV['artikeldaten_aussen_boden_nein']); // Eigenschaft Außen Boden Nein ID
array_push($this->artikeldaten_ids,$_ENV['artikeldaten_aussen_fassade_ja']); // Eigenschaft Außen Fassade Ja ID
array_push($this->artikeldaten_ids,$_ENV['artikeldaten_aussen_fassade_nein']); // Eigenschaft Außen Fassade Nein ID
array_push($this->artikeldaten_ids,$_ENV['artikeldaten_aussen_fenster_ja']); // Eigenschaft Außen Fenster Ja ID
array_push($this->artikeldaten_ids,$_ENV['artikeldaten_aussen_fenster_nein']); // Eigenschaft Außen Fenster Nein ID
array_push($this->artikeldaten_ids,$_ENV['artikeldaten_aussen_stufen_ja']); // Eigenschaft Außen Stufen Ja ID
array_push($this->artikeldaten_ids,$_ENV['artikeldaten_aussen_stufen_nein']); // Eigenschaft Außen Stufen Nein ID
array_push($this->artikeldaten_ids,$_ENV['artikeldaten_aussen_salz_ja']); // Eigenschaft Außen Salz Ja ID
array_push($this->artikeldaten_ids,$_ENV['artikeldaten_aussen_salz_nein']); // Eigenschaft Außen Salz Nein ID
}
/**
* @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',
]);
// if(isset($this->client_username) && isset($this->client_password)){
if(isset($this->client_secret) && isset($this->client_id)){
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;
}
}else {
try{
if(isset($this->client_username) && isset($this->client_password)){
// 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
)
));
}
} 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');
dd($response);
return $response;
exit;
}
}
// exit;
$response_body = json_decode($response->getBody()->getContents());
//** Header used for every request
$this->header = array(
"Accept" => "application/json",
"Content-type" => "application/json",
// "indexing-behavior" => "use-queue-indexing",
"Authorization" => $response_body->token_type." ".$response_body->access_token,
);
}
private function shop_connect(){
// return $this->getDoctrine()->getConnection( $this->shopware_data); // DEV
return $this->getDoctrine()->getConnection( $this->shopware_data_live); // LIVE
}
private function dump_connect(){
// return $this->getDoctrine()->getConnection( $this->shopware_data_dump); // DEV
return $this->getDoctrine()->getConnection( $this->shopware_data_dump_live); // LIVE
}
/**
* Importiert Kategorien anhand der Materialgruppen
*
* @Route("/import/category", name="import_category")
*
* @return mixed
*/
public function import_category()
{
$this->client_connect();
$conn_shopware = $this->shop_connect();
$conn_akn = $this->dump_connect();
$sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,UpdateDatetime)
VALUES(?,?,?)";
$stmtt = $conn_shopware->prepare($sql);
$stmtt->execute(array(
"import/category",
"Start",
date('Y-m-d H:i:s')
));
$sql = "SELECT
*
, m.id as materialsid
FROM materials AS m
LEFT JOIN materialgroups AS mg ON (mg.materialgroup_id = m.materialgroup_id)
WHERE upload_done IS NULL
ORDER BY material_name DESC
;";
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
$materialgroups = $stmt->fetchAll();
// dd($materialgroups);
foreach($materialgroups as $material){
$kategorie=$this->checkCategory($material["materialgroup_name"], $material["materialgroup_id"], $material["material_name"], $material["material_id"], $material["picture"],true);
$sqlt = "UPDATE materials SET
upload_done = ?
WHERE id = '".$material["materialsid"]."';";
$stmtt = $conn_akn->prepare($sqlt);
$stmtt->execute(array(1));
}
$sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,UpdateDatetime)
VALUES(?,?,?)";
$stmtt = $conn_shopware->prepare($sql);
$stmtt->execute(array(
"import/category",
"ENDE",
date('Y-m-d H:i:s')
));
$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" => ['Materialgruppen' => "done"]]);
}
/**
* 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();
$color = explode('#',$key[0]["key_text"]);
$farbe = trim($color[0]);
// return $this->render('update/index.html.twig', ["return" => ['success' => $key ]]);
if(isset($farbe) && !empty($farbe)){ // überprüfe ob als Variante vorhanden ist
$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` = '".$farbe."'
;";
$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
}else{
$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' => $farbe
])
));
$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` = '".$farbe."'
;";
$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"])) {
return $property_color[0]["id"];
}
}
}
return null;
}
/**
* Prüfe ob materialsettings weitere Kategorien beinhält und gib Liste der entsprechenden IDs zurück
*/
public function getAdditionalCategories($materialgroup_id, $material_id){
$conn_akn = $this->dump_connect();
$additionalCategories=array();
$sql = "SELECT
mgr_id
, mar_id
, m1.material_name AS material_name
, amgr_id
, amar_id
, m2.material_name AS material_name_match
, m2.picture AS picture
, materialgroups.materialgroup_name
FROM materialsettings
INNER JOIN materials AS m1 ON (m1.materialgroup_id = materialsettings.mgr_id AND m1.material_id = materialsettings.mar_id)
INNER JOIN materials AS m2 ON (m2.materialgroup_id = materialsettings.amgr_id AND m2.material_id = materialsettings.amar_id)
LEFT JOIN materialgroups ON (materialgroups.materialgroup_id = materialsettings.amgr_id)
WHERE mgr_id = '".$materialgroup_id."'
AND mar_id = '".$material_id."'
;";
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
$categoryMatch = $stmt->fetchAll();
foreach($categoryMatch as $category){
if(isset($category["material_name_match"]) && !empty($category["material_name_match"]) && $category["material_name_match"] != ''){
$catId = $this->checkCategory($category["materialgroup_name"], $category["amgr_id"], $category["material_name_match"], $category["amar_id"], $category["picture"],false);
if($catId != NULL){
array_push($additionalCategories,$catId);
}
}
}
// dd($additionalCategories);
if(empty($additionalCategories)){
return null;
}else{
return $additionalCategories;
}
}
/**
* check if categories exist, if not create them and return id of material category
* pattern: materialgroup -> material -> article:block_number
*/
public function checkCategory($materialgroup_name, $materialgroup_id, $material_name, $material_id, $material_picture, $update){
$conn_shopware = $this->shop_connect();
$matgroup_id = null;
$mat_id = null;
$picture_id=null;
if(empty(trim($materialgroup_name)) || empty(trim($materialgroup_id)) ||
empty(trim($material_name)) || empty(trim($material_id))){
return null;
}
try {
$search = $this->client->request('GET', '/api/category?filter[name]='.$materialgroup_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;
}
$categories = json_decode($search->getBody()->getContents(),true);
$sql = "SELECT *
, LOWER(HEX(id)) as id
, LOWER(HEX(category_id)) as category_id
, LOWER(HEX(parent_id)) as parent_id
FROM category
INNER JOIN category_translation ON (category.id = category_translation.category_id)
WHERE LOWER(HEX(language_id)) = '".$this->language_id."'
AND JSON_EXTRACT(category_translation.custom_fields,'$.custom_materialgroup_id') = '".$materialgroup_id."'
AND name = '".$materialgroup_name."'
AND LOWER(HEX(parent_id)) = '".$this->default_category_id."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$group_category = $stmt->fetchAll();
if(isset($material_picture) && !empty($material_picture)){
$picture= explode(".", $material_picture);
$type = array_pop($picture);
$picture = implode('.', $picture);
$picture = trim(preg_replace("/\.+$/",'',$picture));
try {
$search = $this->client->request('GET', '/api/media?filter[fileName]='.$picture.'', 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);
// dd($search_picture);
if($search_picture["total"] > 0){
// return $this->render('update/index.html.twig', ["return" => ['search picture' => $search_picture, 'media url' => $mediaUrl ]]);
$picture_id = $search_picture["data"][0]["id"];
}
// dd($picture_id);
}
// dd($group_category);
if(!is_array($group_category) || empty($group_category) || count($group_category) < 1){ //erstelle Hauptkategorie wenn noch nicht vorhanden
$CategoryFlat = new \App\Controller\shopware\CategoryFlat();
$CategoryFlat->setType("page");
$CategoryFlat->setName($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,
));
$location = array_pop( $post_category->getHeaders()['Location'] );
$matgroup_id = basename($location);
}else{ // prüfe Material
$matgroup_id = $group_category[0]["id"];
}
$sql = "SELECT
*
, LOWER(HEX(id)) as id
, LOWER(HEX(category_id)) as category_id
, LOWER(HEX(parent_id)) as parent_id
FROM category
INNER JOIN category_translation ON (category.id = category_translation.category_id)
WHERE LOWER(HEX(language_id)) = '".$this->language_id."'
AND JSON_EXTRACT(category_translation.custom_fields,'$.custom_material_id') = '".$material_id."'
AND name = '".$material_name."'
AND LOWER(HEX(parent_id)) = '".$matgroup_id."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$material_category = $stmt->fetchAll();
// dd($material_category);
if(!is_array($material_category) || empty($material_category) || count($material_category) < 1){ //erstelle Kategorie
$CategoryFlat = new \App\Controller\shopware\CategoryFlat();
$CategoryFlat->setType("page");
$CategoryFlat->setName($material_name);
if($picture_id!= null && !empty($picture_id)){
$CategoryFlat->setMediaId($picture_id);
}
$CategoryFlat->setCustomFields(array("custom_material_id" => $material_id));
$CategoryFlat->setCmsPageId($this->material_category_layout_id);
$CategoryFlat->setParentId($matgroup_id);
$post_category = $this->client->request('POST', '/api/category', array(
// 'debug' => true,
'allow_redirects'=>array('strict'=>true),
"headers" => $this->header,
"body" => $CategoryFlat,
));
$location = array_pop( $post_category->getHeaders()['Location'] );
$mat_id = basename($location);
}else{ // prüfe Material
if($update==true){
$CategoryFlat = new \App\Controller\shopware\CategoryFlat();
$CategoryFlat->setName($material_name);
if($picture_id!= null && !empty($picture_id)){
$CategoryFlat->setMediaId($picture_id);
}
$CategoryFlat->setCustomFields(array("custom_material_id" => $material_id));
$CategoryFlat->setCmsPageId($this->material_category_layout_id);
$CategoryFlat->setParentId($matgroup_id);
// return $this->render('update/index.html.twig', ["return" => [ 'Kategorien' => $CategoryFlat]]);
try {
$post_category = $this->client->request('PATCH', '/api/category/'.$material_category[0]["id"].'', array(
'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;
}
}
$mat_id = $material_category[0]["id"];
}
// dd($mat_id);
return $mat_id;
}
/**
* get category id for import
*/
public function getCategoryId($category, $materialgroup_id) //article[materialgroup_name]
{
// prüfe ob kategorie vorhanden ist,
if(empty(trim($category)) || empty(trim($materialgroup_id)) ){
return null;
}
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($category);
$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;
}
}
/**
* add oder update Bild bei Artikel
*
*/
public function add_image($bildname, $produktId, $zusatzbild)
{
// dd($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 clean_url($string) {
$entities = array('%20','%21', '%2A', '%27', '%28', '%29', '%3B', '%3A', '%40', '%26', '%3D', '%2B', '%24', '%2C', '%2F', '%3F', '%25', '%23', '%5B', '%5D','ae','oe','ue');
$replacements = array(" ","!", "*", "'", "(", ")", ";", ":", "@", "&", "=", "+", "$", ",", "/", "?", "%", "#", "[", "]","ä","ö","ü");
$string = str_replace($entities, $replacements, $string);
return $string;
}
/**
* Bildimport
*
* @Route("/import/images", name="import_images")
*
* @return mixed
*/
public function import_images()
{
// exit;
$this->client_connect();
$conn_shopware = $this->shop_connect();
$step_count = 100;
$picturetypes = array("jpg", "JPG", "jpeg", "png", "PNG", "webp", "gif", "svg", "bmp", "tiff", "tif", "eps");
$valid_ext = array('png','jpeg','jpg');
$sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,UpdateDatetime)
VALUES(?,?,?)";
$stmtt = $conn_shopware->prepare($sql);
$stmtt->execute(array(
"import/images",
"Start",
date('Y-m-d H:i:s')
));
// Namen aller Bilddateien vom FTP holen
$article_files = scandir($this->getParameter('kernel.project_dir')."/public/komprimiert");
$article_files = array_diff($article_files, array('.','..'));
// var_dump($article_files);exit;
// Alle Produktbilder in SW
if(isset($article_files) && !empty($article_files) && count($article_files) > 0){
$sql = "SELECT
file_name
, file_extension
FROM media
ORDER BY file_name
-- LIMIT ".$step_count."
;";
// --WHERE LOWER(HEX(media_folder_id)) = '".$this->media_folder_id."'
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$article = $stmt->fetchAll();
if(count($article_files) > $step_count){
$article_files = array_slice($article_files, 0, $step_count);
}
// return $this->render('update/index.html.twig', ["return" => ['folder' => $article_files, 'sw' => $article ]]);
foreach($article_files as $article_file){
// var_dump($article_file);
// dd($article_files);
// $article_file = '2319_P1030278 Nero Im+pala Rus`tenburg ä ö ü gefla&mmt 2´cm 218070548.jpg';
$article_file_new = preg_replace("/´|`|&/", ' ', $article_file);
$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));
$article_file_new = $filename.".".$type;
rename($this->getParameter('kernel.project_dir')."/public/komprimiert/".$article_file,$this->getParameter('kernel.project_dir')."/public/komprimiert/".$filename.".".$type);
//#### DATEIKOMPRIMIERUNG
// $location = $this->getParameter('kernel.project_dir')."/public/komprimiert/".$article_file_new;
// $file_extension = pathinfo($location, PATHINFO_EXTENSION);
// $file_extension = strtolower($file_extension);
// // return $this->render('update/index.html.twig', ["return" => ['location' => $location ]]);
// if(in_array($file_extension,$valid_ext)){
// $info = getimagesize($location);
// if ($info['mime'] == 'image/jpeg')
// $image = imagecreatefromjpeg($location);
// elseif ($info['mime'] == 'image/png')
// $image = imagecreatefrompng($location);
// imagejpeg($image, $location, 60); // image, path, quality 1-100
// }else{
// echo "Invalid file type.";
// }
// return $this->render('update/index.html.twig', ["return" => [ 'article' => $article_files]]);
if(in_array($type,$picturetypes)){
$article_file_clean = $this->clean_url($article_file_new);
// $mediaUrl = trim('https://'.$_SERVER['HTTP_HOST'].'/komprimiert/'.$article_file_clean);
$mediaUrl = trim('https://'.$_SERVER['HTTP_HOST'].'/komprimiert/'.$article_file_clean);
$mediaUrl = str_replace(' ','%20',$mediaUrl);
$mediaUrl = str_replace('ä','ae',$mediaUrl);
$mediaUrl = str_replace('ö','oe',$mediaUrl);
$mediaUrl = str_replace('ü','ue',$mediaUrl);
$mediaUrl = str_replace('ß','ss',$mediaUrl);
if(!in_array($filename,array_column($article, 'file_name') )){ //trim column?
// Bild noch nicht vorhanden -> Lade hoch
// return $this->render('update/index.html.twig', ["return" => [ 'article' => $this->header]]);
$post_media = $this->client->post('/api/media', array(
// "debug" => true,
'allow_redirects'=>array('strict'=>true),
'headers' => $this->header,
'body' => json_encode([
'mediaFolderId' => $this->media_folder_id
])
));
$location = array_pop( $post_media->getHeaders()['Location'] );
$mediaId = basename($location);
try{
$post_media = $this->client->request('POST', '/api/_action/media/'.$mediaId.'/upload?extension='.$type.'&fileName='.$filename, array(
// "debug" => true,
'allow_redirects'=>array('strict'=>true),
'headers' => $this->header,
'body' => json_encode([
'url' => $mediaUrl,
'mediaFolderId' => $this->media_folder_id,
])
));
} catch (RequestException $e) {
//TODO error report in tabelle schreiben
// dd("test3");
// 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;
}
//move file in uploaded folder
// move_uploaded_file($this->getParameter('kernel.project_dir')."/public/komprimiert/".$article_file,$this->getParameter('kernel.project_dir')."/public/bilder_hochgeladen/".$article_file);
}else{
// bild ist nicht im bildordner vorhanden
try {
$search = $this->client->request('GET', '/api/media?filter[fileName]='.$filename.'', array(
// $search = $this->client->request('GET', '/api/media/6d98e8d9d2184172a5acc4a32095e843', array(
"headers" => $this->header,
));
} catch (RequestException $e) {
// //TODO error report in tabelle schreiben
// dd("test2");
// 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);
// return $this->render('update/index.html.twig', ["return" => ['last product id' => $search_picture ]]);
if(isset($search_picture) && !empty($search_picture) && count($search_picture) > 0){
$post_product_id = $search_picture["data"][0]["id"];
if(count($search_picture) > 1){
// suche datensatz mit der richtigen dateiendung
foreach($search_picture["data"] as $search_picture_file){
if($search_picture_file["fileExtension"] == $type){
$post_product_id = $search_picture_file["id"];
continue;
}
}
}
try{
// $post_product = $this->client->request('POST', 'api/_action/media/'.$search_picture["data"][0]["id"].'/upload?extension='.$type.'&fileName='.$filename, array(
$post_product = $this->client->request('POST', 'api/_action/media/'.$post_product_id.'/upload?extension='.$type.'&fileName='.$filename, array(
// 'debug' => true,
'allow_redirects'=>array('strict'=>true),
'headers' => $this->header,
'body' => json_encode([
'url' => $mediaUrl,
'mediaFolderId' => $this->media_folder_id,
])
));
} catch (RequestException $e) {
// //TODO error report in tabelle schreiben
// dd($mediaUrl);
// 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{
//TODO report, nicht unterstützter filetype
// var_dump("filetype not supportet! ".$type);exit;
}
//delete file in uploaded folder
// exit;
// move_uploaded_file($this->getParameter('kernel.project_dir')."/public/komprimiert/".$article_file,$this->getParameter('kernel.project_dir')."/public/bilder_hochgeladen/".$article_file);
// dd($article_file_new);
unlink($this->getParameter('kernel.project_dir')."/public/komprimiert/".$article_file_new);
}
if(count($article) >= $step_count){
return $this->redirect('/import/images');
}
}else{
$sql = "SELECT MAX(UpdateDatetime) as lastdate FROM pixel_dates
WHERE FunctionName = 'import/images'
AND StartEnde = 'ENDE'";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$date = $stmt->fetchAll();
// dd($date[0]["lastdate"], date('Y-m-d H:i:s', strtotime(date('Y-m-d H:i:s'). '-3 minutes')));
if($date[0]["lastdate"]<date('Y-m-d H:i:s', strtotime(date('Y-m-d H:i:s'). '-7 hours'))){
// Setze Endzeit nur Wenn letzter Durchlauf 7 Stunden her ist.
$sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,UpdateDatetime)
VALUES(?,?,?)";
$stmtt = $conn_shopware->prepare($sql);
$stmtt->execute(array(
"import/images",
"ENDE",
date('Y-m-d H:i:s')
));
}
return $this->redirect('/copy/image');
}
// return $this->render('update/index.html.twig', ["return" => ['last product id' => count($articlesSW) ]]);
$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" => ['Bilderimport' => "done" ]]);
}
/**
* Fügt Attribute den eingelesenen Artikeln hinzu
*
* @Route("/add/attributes", name="add_attributes")
*
* @return mixed
*/
public function add_attributes()
{
$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)) = '".$this->language_id."'
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"]);
}
$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
));
} 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 ]]);
}
if(count($products_sw) < 11){
return $this->redirect('/add/attributes');
}else{
return $this->render('update/index.html.twig', ["return" => ['Attribute' => "done" ]]);
}
}
/**
* Importiert Attribute in Shopware
*
* @Route("/import/attributes", name="import_attributes")
*
* @return mixed
*/
public function import_attributes()
{
$this->client_connect();
$conn_shopware = $this->shop_connect();
$conn_akn = $this->dump_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_akn->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_akn->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_akn->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_akn->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();
// Materialname
if(isset($storage["material_name"]) && !empty($storage["material_name"])){
$eigenschaften[$this->material_id]=$storage["material_name"];
}
// Materialgruppenname
if(isset($storage["materialgroup_name"]) && !empty($storage["materialgroup_name"])){
$eigenschaften[$this->materialgroup_id]=$storage["materialgroup_name"];
}
// Steinnummer
if(isset($storage["stone_number"]) && !empty($storage["stone_number"])){
$eigenschaften[$this->stonenumber_id]=$storage["stone_number"];
}
// Dicke
if(isset($storage["thickness"]) && !empty($storage["thickness"])){
$eigenschaften[$this->thickness_id]=$storage["thickness"];
}
// Artikeldaten Innen/Außen
if(isset($storage["material_id"]) && !empty($storage["material_id"]) && isset($storage["materialgroup_id"]) && !empty($storage["materialgroup_id"])){
$sql = "SELECT *
FROM artikeldaten
WHERE material_group = '".$storage["materialgroup_id"]."'
AND material = '".$storage["material_id"]."'
LIMIT 1
;";
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
$artikeldaten = $stmt->fetchAll();
// dd($storage["material_id"],$storage["materialgroup_id"],$artikeldaten);
if(isset($artikeldaten) && is_array($artikeldaten) && count($artikeldaten) > 0){
$eigenschaften[$this->artikeldaten_innen_id] = array();
$eigenschaften[$this->artikeldaten_aussen_id] = array();
$artikeldaten[0] = array_values($artikeldaten[0]);
$i = 0;
foreach($artikeldaten[0] as $key => $artikeldaten_select){
if($key == 0 || $key == 1 || $key == 2 || $key == 13){
continue;
}
if($key < 8){
if($artikeldaten_select == 1){
array_push($eigenschaften[$this->artikeldaten_innen_id], $this->artikeldaten_ids[$i]);
}elseif($artikeldaten_select == 0){
array_push($eigenschaften[$this->artikeldaten_innen_id], $this->artikeldaten_ids[$i+1]);
}
}else{
if($artikeldaten_select == 1){
array_push($eigenschaften[$this->artikeldaten_aussen_id], $this->artikeldaten_ids[$i]);
}elseif($artikeldaten_select == 0){
array_push($eigenschaften[$this->artikeldaten_aussen_id], $this->artikeldaten_ids[$i+1]);
}
}
$i = $i+2;
}
// dd($artikeldaten[0], $eigenschaften[$this->artikeldaten_innen_id], $eigenschaften[$this->artikeldaten_aussen_id]);
}
}
// Länge
if(isset($storage["length"]) && !empty($storage["length"])){
$eigenschaften[$this->length_id]=$storage["length"];
}
// Höhe
if(isset($storage["width"]) && !empty($storage["width"])){
$eigenschaften[$this->width_id]=$storage["width"];
}
// Anzahl
if(isset($storage["amount_1"]) && !empty($storage["amount_1"])){
$eigenschaften[$this->amount_id]=strval(intval($storage["amount_1"]));
}
// Quadratmeterpreis
if(isset($storage["price"]) && !empty($storage["price"])){
$eigenschaften[$this->squaremeterprice_id]=$storage["price"];
}
// Quadratmeter oder Meter oder Sa
if(isset($storage["einheit_2"]) && !empty($storage["einheit_2"]) && isset($storage["amount_2"]) && !empty($storage["amount_2"])){
// wenn einheit 2 gesetzt ist prüfe welchen inhalt und ordne entsprechend die id zu 1/2/14
if(trim($storage["einheit_2"]) == "m²"){
// Quadratmeter
$eigenschaften[$this->surfacearea_id]=strval(round($storage["amount_2"],2));
}elseif(trim($storage["einheit_2"]) == "m"){
// Meter 5ce33389c17e4f298ef14a3d1f0230f7
$eigenschaften[$this->meter_id]=strval(round($storage["amount_2"],2));
}elseif(trim($storage["einheit_2"]) == "Sa"){
// Sa b7b4812b66e7482e83e0d12b016710cd
$eigenschaften[$this->sa_id]=strval(round($storage["amount_2"],2));
}
}
// if(isset($storage["amount_2"]) && !empty($storage["amount_2"])){
// $eigenschaften[$this->surfacearea_id]=strval(round($storage["amount_2"],2));
// }
// Oberfläche
if(isset($storage["surface_name"]) && !empty($storage["surface_name"])){
$eigenschaften[$this->surface_id]=$storage["surface_name"];
}
// Artikelgruppe
if(isset($storage["articlegroup_name"]) && !empty($storage["articlegroup_name"])){
$eigenschaften[$this->articlegroup_id]=$storage["articlegroup_name"];
}
// Fehler j/n
$fehler = "Nein";
if( isset($storage["fault_name"]) && strlen($storage["fault_name"]) > 0 && !empty($storage["fault_name"]) && isset($storage["fault_corner"]) && strlen($storage["fault_corner"]) > 0 && !empty($storage["fault_corner"])){
$eigenschaften[$this->fault_exists_id]=$storage["fault_name"].": ".$storage["fault_corner"];
}else{
$eigenschaften[$this->fault_exists_id]="Nein";
}
// 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
$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;
}
}
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();
// if(isset($colors) && is_array($colors) && count($colors) > 0){
// $color = explode('#',$colors[0]["key_text"]);
// $eigenschaften[$this->color_id] = trim($color[0]);
// }
if(isset($colors) && is_array($colors) && count($colors) > 0){
$eigenschaften[$this->color_id] = array();
foreach($colors as $colors_select){
unset($color);
$color = explode('#',$colors_select["key_text"]);
array_push($eigenschaften[$this->color_id], trim($color[0]));
}
// dd($color);
// dd($eigenschaften[$this->color_id]);
}
}
$attribute=array();
foreach($eigenschaften as $key => $eigenschaft){
if($key==$this->color_id){
// wenn key color ist dann geh kompletten array damit durch
foreach($eigenschaft as $farbeigenschaft){
$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` = '".$farbeigenschaft."'
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($eigenschaft." post ".$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' => $farbeigenschaft
])
));
$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]));
}
}
}elseif($key==$this->artikeldaten_innen_id||$key==$this->artikeldaten_aussen_id){
foreach($eigenschaft as $artikeldateneigenschaft){
$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 LOWER(HEX(id)) = '".$artikeldateneigenschaft."'
AND LOWER(HEX(property_group_id)) = '".$key."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$search_property = $stmt->fetchAll();
// var_dump($search_property);exit;
if(isset($search_property) && is_array($search_property) && count($search_property) > 0) {
array_push($attribute,array('optionId'=>$search_property[0]["id"]));
}
}
}else{
$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($key);
// var_dump($eigenschaft." post ".$key);
try{
$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( $post_product->getHeaders());
// $location = basename(array_pop( $post_product->getHeaders()['Location'] ));
// dd($location, $key, $eigenschaft);exit;
$sql = "SELECT
*
, LOWER(HEX(property_group_option_id)) as property_group_option_id
FROM property_group_option_translation
WHERE `name` = '".$eigenschaft."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$search_property = $stmt->fetchAll();
if(isset($search_property) && is_array($search_property) && count($search_property)>0){
array_push($attribute,array('optionId'=>$search_property[0]['property_group_option_id']));
}
// $optionId = str_replace($this->base_url.'api/property-group-option/','',$optionId["Location"]); // ID des erstellten produktes
// array_push($attribute,array('optionId'=>$location));
// array_push($attribute,array('optionId'=>$optionId[0]));
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
// exit;
}
}
}
}
// var_dump($attribute);exit;
return $attribute;
}
/**
* Prüft Sichtbarkeit generierter Kategorien und blendet diese entsprechend der Produkte ein/aus
*
* @Route("/categories/visibility", name="categories_visibility")
*
* @return mixed
*/
public function categories_visibility()
{
$this->client_connect();
$conn_shopware = $this->shop_connect();
$sql = "SELECT
*
, LOWER(HEX(category_id)) as category_id
FROM category_translation
INNER JOIN category ON (category.id = category_id)
WHERE path LIKE '%".$this->default_category_id."%'
AND LOWER(HEX(language_id)) = '".$this->language_id."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$categories = $stmt->fetchAll();
// return $this->render('update/index.html.twig', ["return" => [ 'sort' => $categories]]);
foreach($categories as $category){
$sql = "SELECT
count(id) as count
FROM product
WHERE category_tree LIKE '%".$category['category_id']."%'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$product = $stmt->fetchAll();
// return $this->render('update/index.html.twig', ["return" => [ 'sort' => $product]]);
unset($CategoryFlat);
if($product[0]["count"]>0){
// Produkt in Kategorie vorhanden
if($category["active"] != 1){
// setze aktiv
$CategoryFlat = new \App\Controller\shopware\CategoryFlat();
$CategoryFlat->setActive(true);
}
}else{
// kein Produkt in Kategorie vorhanden
// verhindere inaktiv setzen der default Kategorie
if($category["active"] != 0 && $category['category_id'] != $this->default_category_id){
// setze inaktiv
$CategoryFlat = new \App\Controller\shopware\CategoryFlat();
$CategoryFlat->setActive(false);
}
}
// return $this->render('update/index.html.twig', ["return" => [ 'Kategorien' => $CategoryFlat]]);
if(isset($CategoryFlat)){
try {
$post_category = $this->client->request('PATCH', '/api/category/'.$category['category_id'].'', array(
'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;
}
}
}
return $this->render('update/index.html.twig', ["return" => [ 'Kategorien' => "Kategorien Aktualisiert"]]);
}
/**
* Kopiert die Bilddateien von AKN in den produktbilder Ordner zur weiteren Verarbeitung
*
* @Route("/copy/image", name="copy_image")
*
* @return mixed
*/
public function copy_image(){
// exit;
$conn_shopware = $this->shop_connect();
// $location = "/var/www/vhosts/pixelproductions.de/sw6.pixelproductions.de/akn_upload/Bilder"; // DEV
// $location = "/home/aknnadbt/shop.akn-natursteine.de/UPLOAD/Bilder"; // LIVE
// $location = "/var/www/vhosts/akn-natursteine.de/shop.akn-natursteine.de/UPLOAD/Bilder"; // LIVE NACH UMZUG
$location = "/var/www/vhosts/swconnect.akn-natursteine.de/httpdocs/public/UPLOAD/Bilder"; // LIVE NACH IONOS UMZUG
$scan = scandir($location);
// return $this->render('update/index.html.twig', ["return" => [ 'zeit' => $scan]]);
$target_location = $this->getParameter('kernel.project_dir')."/public/produktbilder";
$target = scandir($target_location);
$scan = array_diff($scan, $target);
// Wenn produktbilder leer und akn_upload nicht leer -> kopiere Bilder in produktbilder
if(count($target)<4 && count($scan)>2){
// dd($target,$scan);
// TODO timestamp 1d für bilder
$sql = "SELECT MAX(UpdateDatetime) as lastdate FROM pixel_dates
WHERE FunctionName = 'import/images'
AND StartEnde = 'ENDE'";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$date = $stmt->fetchAll();
// dd($date[0]["lastdate"], date('Y-m-d H:i:s', strtotime(date('Y-m-d H:i:s'). '-0 hours')));
if ($date[0]["lastdate"]< date('Y-m-d H:i:s', strtotime(date('Y-m-d H:i:s'). '-5 hours'))){
foreach($scan as $scan_file){
copy($location."/".$scan_file,$target_location."/".$scan_file);
}
}else{
// finished
$response = new Response(json_encode(array('success' => true, 'last Date' => $date[0]["lastdate"])));
$response->headers->set('Content-Type', 'application/json');
return $response;
}
// dd("hey");
}
// dd($target,$scan);
//#### DATEIKOMPRIMIERUNG
$article_files = scandir($this->getParameter('kernel.project_dir')."/public/produktbilder");
$article_files = array_diff($article_files, array('.','..','Thumbs.db'));
$valid_ext = array('png','jpeg','jpg');
$article_files = array_slice($article_files,0,100);
if(count($article_files)>0){
foreach($article_files as $baseimage){
$location = $this->getParameter('kernel.project_dir')."/public/produktbilder/".$baseimage;
$destination = $this->getParameter('kernel.project_dir')."/public/komprimiert/".$baseimage;
$file_extension = pathinfo($location, PATHINFO_EXTENSION);
$file_extension = strtolower($file_extension);
// return $this->render('update/index.html.twig', ["return" => ['location' => $location ]]);
try{
if(in_array($file_extension,$valid_ext) && !file_exists($destination)){
$info = @getimagesize($location);
if($info == false){ //Datei kann nicht gelesen werden oder ist 0 Bytes groß
unlink($location);
}else{
if ($info['mime'] == 'image/jpeg')
$image = imagecreatefromjpeg($location);
elseif ($info['mime'] == 'image/png')
$image = imagecreatefrompng($location);
imagejpeg($image, $destination, 60); // image, path, quality 1-100
}
// unlink($location);
}
// else{
// echo "Invalid file type.".$location;
// }
unlink($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;
}
}
return $this->redirect('/copy/image');
}
// return $this->render('update/index.html.twig', ["return" => [ 'article' => $article_files]]);
return $this->redirect('/import/images');
// $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" => [ 'zeit' =>$target, 'scan' => $scan]]);
// return $this->render('update/index.html.twig', ["return" => [ 'zeit' => 'done']]);
}
/**
* Kopiert die Bilddateien von AKN in den produktbilder Ordner zur weiteren Verarbeitung
*
* @Route("/copy/image2", name="copy_image2")
*
* @return mixed
*/
public function copy_image2(){
exit;
// $location = "/var/www/vhosts/pixelproductions.de/sw6.pixelproductions.de/akn_upload/Bilder"; // DEV
// $location = "/home/aknnadbt/shop.akn-natursteine.de/UPLOAD/Bilder"; // LIVE
$location = "/var/www/vhosts/akn-natursteine.de/shop.akn-natursteine.de/UPLOAD/Bilder"; // LIVE NACH UMZUG
$scan = scandir($location);
// return $this->render('update/index.html.twig', ["return" => [ 'zeit' => $scan]]);
$target_location = $this->getParameter('kernel.project_dir')."/public/produktbilder";
$target = scandir($target_location);
$scan = array_diff($scan, $target);
// Wenn produktbilder leer und akn_upload nicht leer -> kopiere Bilder in produktbilder
if(count($target)<3 && count($scan)>2){
foreach($scan as $scan_file){
copy($location."/".$scan_file,$target_location."/".$scan_file);
}
}
//#### DATEIKOMPRIMIERUNG
$article_files = scandir($this->getParameter('kernel.project_dir')."/public/produktbilder");
$article_files = array_diff($article_files, array('.','..','Thumbs.db'));
$valid_ext = array('png','jpeg','jpg');
foreach($article_files as $baseimage){
$location = $this->getParameter('kernel.project_dir')."/public/produktbilder/".$baseimage;
$destination = $this->getParameter('kernel.project_dir')."/public/komprimiert/".$baseimage;
$file_extension = pathinfo($location, PATHINFO_EXTENSION);
$file_extension = strtolower($file_extension);
// return $this->render('update/index.html.twig', ["return" => ['location' => $location ]]);
if(in_array($file_extension,$valid_ext)){
$info = getimagesize($location);
if ($info['mime'] == 'image/jpeg')
$image = imagecreatefromjpeg($location);
elseif ($info['mime'] == 'image/png')
$image = imagecreatefrompng($location);
imagejpeg($image, $destination, 60); // image, path, quality 1-100
unlink($this->getParameter('kernel.project_dir')."/public/produktbilder/".$baseimage);
}else{
echo "Invalid file type.";
}
}
// return $this->render('update/index.html.twig', ["return" => [ 'article' => $article_files]]);
// return $this->render('update/index.html.twig', ["return" => [ 'zeit' =>$target, 'scan' => $scan]]);
return $this->render('update/index.html.twig', ["return" => [ 'zeit' => 'done']]);
}
/**
* Importiert und updated Produkte und ihre Varianten
*
* @Route("/add/multiple_pictures", name="add_multiple_pictures")
*
* @return mixed
*/
public function add_multiple_pictures()
{
// $this->client_connect();
$conn_shopware = $this->shop_connect();
$conn_akn = $this->dump_connect();
$step_count = 3;
$sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,UpdateDatetime)
VALUES(?,?,?)";
$stmtt = $conn_shopware->prepare($sql);
$stmtt->execute(array(
"add/multiple_pictures",
"Start",
date('Y-m-d H:i:s')
));
$sql = "SELECT *
FROM storpictures
WHERE upload_done IS NULL
AND upload_failed IS NULL
-- LIMIT ".$step_count."
;";
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
$pictures = $stmt->fetchAll();
// return $this->render('update/index.html.twig', ["return" => [ 'article' => $pictures]]);
foreach($pictures as $key => $picture){
$sql = "SELECT *
FROM storages
WHERE block_advice = '".$picture["ref"]."'
OR stone_number = '".$picture["ref"]."'
AND upload_done = 1
AND upload_failed IS NULL
LIMIT 1
;";
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
$storages = $stmt->fetchAll();
// return $this->render('update/index.html.twig', ["return" => [ 'article' => $storages]]);
if(!isset($storages) || empty($storages) || count($storages) < 1){
unset($pictures[$key]);
}
}
array_splice($pictures,$step_count);
foreach($pictures as $picture){
$sql = "SELECT *
, LOWER(HEX(product_id)) as product_id
, REPLACE(JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_block_number'), '\"', '') as custom_article_block_number
, REPLACE(JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_stone_number'), '\"', '') as custom_article_stone_number
-- LOWER(HEX(id)) as id
FROM product_translation
WHERE REPLACE(JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_block_number'), '\"', '') = '".$picture["ref"]."'
OR REPLACE(JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_stone_number'), '\"', '') = '".$picture["ref"]."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$search_product = $stmt->fetchAll();
if(isset($search_product) && !empty($search_product) && count($search_product) > 0 ){
// if(isset($search_product) && !empty($search_product) && count($search_product) > 0){
foreach($search_product as $variant){
$this->add_image($picture["picture"], $variant["product_id"], true);
}
$sqlt = "UPDATE storpictures SET
upload_done = ?
WHERE id = '".$picture["id"]."';";
$stmtt = $conn_akn->prepare($sqlt);
$stmtt->execute(array(1));
}else{
$sqlt = "UPDATE storpictures SET
upload_done = ?,
upload_failed = ?
WHERE id = '".$picture["id"]."';";
$stmtt = $conn_akn->prepare($sqlt);
$stmtt->execute(array(1,1));
}
}
if(count($pictures) >= $step_count){
return $this->redirect('/add/multiple_pictures');
}
$sqlt = "UPDATE storpictures SET
upload_done = ?,
upload_failed = ?
-- WHERE stone_number = ''
WHERE (upload_done IS NULL AND upload_failed IS NULL)
;";
$stmtt = $conn_akn->prepare($sqlt);
$stmtt->execute(array( 1,1));
$sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,UpdateDatetime)
VALUES(?,?,?)";
$stmtt = $conn_shopware->prepare($sql);
$stmtt->execute(array(
"add/multiple_pictures",
"ENDE",
date('Y-m-d H:i:s')
));
$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" => [ 'multiple_pictures' => "Extrabilderupload Done"]]);
}
/**
* Generiert Crosselling Liste bei SQL-Import
*
* @return mixed
*/
public function createCrosssellingList()
{
$conn_shopware = $this->shop_connect();
$conn_akn = $this->dump_connect();
$sql = "TRUNCATE TABLE crossselling";
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
$sql = "SELECT *
, LOWER(HEX(parent_id)) as parent_id
FROM product
WHERE category_tree LIKE '%".$this->default_category_id."%'
AND parent_id IS NOT NULL
AND option_ids IS NOT NULL
GROUP BY parent_id
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$products = $stmt->fetchAll();
foreach($products as $product){
$sql = "SELECT LOWER(HEX(id)) as id
FROM property_group_option
WHERE LOWER(HEX(property_group_id)) = '".$this->color_id."'
AND LOWER(HEX(id)) IN ('".implode("', '",json_decode($product["option_ids"]))."')
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$colors = $stmt->fetchAll();
$colors = array_map('current', $colors);
foreach($colors as $color){
$sql = "INSERT INTO crossselling (parent_id, color_id)
VALUES ('".$product["parent_id"]."','".$color."')
;";
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
}
}
}
/**
* Fügt Crossselling abhängig von den Materialfarben hinzu
*
* @Route("/cross/selling", name="cross_selling")
*
* @return mixed
*/
public function cross_selling()
{
$this->client_connect();
$conn_shopware = $this->shop_connect();
$conn_akn = $this->dump_connect();
$step_count = 20;
// Für Restart einmal einkommentieren und ausführen (erfolgt automatisch bei jedem SQL_Import)
// $this->createCrosssellingList();exit;
$sql = "SELECT *
FROM crossselling
WHERE bearbeitet IS NULL
LIMIT ".$step_count."
;";
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
$sellings = $stmt->fetchAll();
foreach($sellings as $selling){
// return $this->render('update/index.html.twig', ["return" => [ 'article' => $selling ]]);
$sql = "SELECT *
, LOWER(HEX(product_stream_id)) as product_stream_id
FROM product_stream_translation
WHERE JSON_EXTRACT(product_stream_translation.custom_fields,'$.custom_color_id') = '".$selling["color_id"]."'
AND (JSON_EXTRACT(product_stream_translation.custom_fields,'$.custom_usage_id') IS NULL
OR JSON_EXTRACT(product_stream_translation.custom_fields,'$.custom_usage_id') = '' )
LIMIT 1
";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$product_stream = $stmt->fetchAll();
// return $this->render('update/index.html.twig', ["return" => [ 'article' => $product_stream ]]);
if(isset($product_stream) && !empty($product_stream) && count($product_stream) > 0){
$sql = "SELECT
LOWER(HEX(id)) AS id
FROM product_cross_selling
LEFT JOIN product_cross_selling_translation ON (product_cross_selling_translation.product_cross_selling_id = product_cross_selling.id)
WHERE LOWER(HEX(product_id)) = '".$selling["parent_id"]."'
AND name = '".$product_stream[0]["name"]."'
";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$search_stream = $stmt->fetchAll();
// return $this->render('update/index.html.twig', ["return" => [ 'farbe' => $product_stream, 'article' => $search_stream ]]);
$body = json_encode(array(
"name" => $product_stream[0]["name"],
"sortBy" => "name",
"sortDirection" => "ASC",
"limit" => 24,
"active" => true,
"productId" => $selling["parent_id"],
"type" => "productStream",
"productStreamId" => $product_stream[0]["product_stream_id"]
));
if(isset($search_stream[0]["id"]) && !empty($search_stream[0]["id"]) && $search_stream[0]["id"] != null){
try{
$post_country = $this->client->request('PATCH', '/api/product-cross-selling/'.$search_stream[0]["id"].'', array(
'allow_redirects'=>array('strict'=>true),
"headers" => $this->header,
"body" => $body
));
} 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{
$post_country = $this->client->request('POST', '/api/product-cross-selling', array(
'allow_redirects'=>array('strict'=>true),
"headers" => $this->header,
"body" => $body
));
} 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;
}
$location = array_pop( $post_country->getHeaders()['Location'] );
}
$sqlt = "UPDATE crossselling SET
bearbeitet = ?
WHERE id = ".$selling["id"].";";
$stmtt = $conn_akn->prepare($sqlt);
$stmtt->execute(array(1));
}else{
// dynamische produktegruppe für farbe nicht vorhanden
$sqlt = "UPDATE crossselling SET
bearbeitet = ?
WHERE id = ".$selling["id"].";";
$stmtt = $conn_akn->prepare($sqlt);
$stmtt->execute(array(2));
}
// return $this->render('update/index.html.twig', ["return" => [ 'article' => $selling ]]);
}
if(count($sellings) >= $step_count){
return $this->redirect('/cross/selling');
}
return $this->render('update/index.html.twig', ["return" => [ 'Crosselling' => 'Done' ]]);
}
/**
* Deaktiviert/Löscht nicht mehr vorhandene Artikel
*
* @Route("/delete/products", name="delete_products")
*
* @return mixed
*/
public function delete_products()
{
// dd("test");
$this->client_connect();
$conn_shopware = $this->shop_connect();
$conn_akn = $this->dump_connect();
$sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,UpdateDatetime)
VALUES(?,?,?)";
$stmtt = $conn_shopware->prepare($sql);
$stmtt->execute(array(
"delete/products",
"Start",
date('Y-m-d H:i:s')
));
$sql = "SELECT stone_number
FROM storages
WHERE stone_number !=''
AND stone_number IS NOT NULL
;";
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
$storages = $stmt->fetchAll();
$storages = array_map('current', $storages);
if(isset($storages) && !empty($storages) && count($storages) > 0){
// $sql = "DELETE FROM product
// WHERE product_number NOT IN ('".implode("', '",$storages)."')
// AND child_count IS NULL
// ;";
// SELEKTIERE MANUELL ANGELEGTE PRODUKTE UND FÜGE SIE AUSNAHMEN HINZU DAMIT DIESE NICHT MEHR DEAKTIVIERT WERDEN
$sql = "SELECT
product_number
FROM product_translation
INNER JOIN product ON (product.id = product_id)
WHERE LOWER(HEX(language_id)) = '2fbb5fe2e29a4d70aa5854ce7ce3e20b'
-- and name= 'test'
AND (JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_material_name') = ''
OR JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_material_name') IS NULL)
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$additional_products = $stmt->fetchAll();
$additional_products = array_map('current', $additional_products);
$storages = array_merge($storages,$additional_products);
$sql = "UPDATE product
SET active = 0
WHERE product_number NOT IN ('".implode("', '",$storages)."')
AND child_count IS NULL
;";
// $sql = "SELECT product_number
// FROM product
// WHERE product_number NOT IN ('".implode("', '",$storages)."')
// AND child_count IS NULL
// ;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$product = $stmt->fetchAll();
$product = array_map('current', $product);
// dd($product);
$sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,UpdateDatetime)
VALUES(?,?,?)";
$stmtt = $conn_shopware->prepare($sql);
$stmtt->execute(array(
"delete/products",
"ENDE",
date('Y-m-d H:i:s')
));
// dd($product);
// return $this->render('update/index.html.twig', ["return" => [ 'storages' => $storages, 'result' => $product]]);
return $this->render('update/index.html.twig', ["return" => [ 'result' => "Produkte deaktiviert"]]);
}
$sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,UpdateDatetime)
VALUES(?,?,?)";
$stmtt = $conn_shopware->prepare($sql);
$stmtt->execute(array(
"delete/products",
"ENDE",
date('Y-m-d H:i:s')
));
$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" => [ 'result' => "storages leer"]]);
}
/**
* Importiert und updated Produkte und ihre Varianten
*
* @Route("/update/products", name="update_products")
*
* @return mixed
*/
public function update_products()
{
// echo "<pre>"; var_dump(__LINE__); exit;
$this->client_connect();
$conn_shopware = $this->shop_connect();
$conn_akn = $this->dump_connect();
$step_count = 5;
$productParentId = "";
$storage_option_list=array();
$next_page=false;
$sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,UpdateDatetime)
VALUES(?,?,?)";
$stmtt = $conn_shopware->prepare($sql);
$stmtt->execute(array(
"update/products",
"Start",
date('Y-m-d H:i:s')
));
// holt alle 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
, storages.stone_number AS product_number
-- , CONCAT(storages.material_id, '_', storages.materialgroup_id) AS product_number_parent
, CONCAT(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
, storages.advice AS advice
, materials.advice AS description_advice
, COALESCE(NULLIF(materials.picture, ''), 'default_art.jpg') AS picture_parent -- select picture
, key1.key_text AS color_text
, key2.key_text AS einheit_2
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)
LEFT JOIN materialcolors ON (articles.material_id = materialcolors.mar_id AND articles.materialgroup_id = materialcolors.mgr_id )
LEFT JOIN `keys` key1 ON (materialcolors.color_id = key1.key_number AND key1.key_kind = 88)
LEFT JOIN `keys` key2 ON (storages.quantity_unit_2 = key2.key_number AND key2.key_kind = 1)
LEFT JOIN artikeldaten ON (artikeldaten.material_group = articles.materialgroup_id AND artikeldaten.material = articles.material_id)
WHERE storages.stone_number IS NOT NULL
-- AND storages.stone_number = '219102526'
-- AND articles.material_id = 'BEHA'
-- AND materials.advice !=''
AND storages.stone_number !=''
AND storages.storage != 'ZULAUF'
-- AND storages.upload_done IS NULL
AND storages.upload_done = 4
-- OR storages.upload_done = 4
-- AND articles.upload_done IS NULL
AND storages.upload_failed IS NULL
AND material_name IS NOT NULL
-- AND block_advice IS NOT NULL
-- AND block_advice != ''
ORDER BY storages.upload_done DESC, articles.id ASC
LIMIT ".$step_count."
;";
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
$articles = $stmt->fetchAll();
// holt alle Artikel ab die eine passende Materialaufschlüsselung haben
// return $this->render('update/index.html.twig', ["return" => ['article' => $articles]]);
if(count($articles)>0){
$next_page=true;
}
foreach($articles as $article){
//prüfe auf blocknummer. wenn existiert dann füge an product_number_parent an, wenn nicht continue
$blocknummer=null;
$blocknummer_zerlegt=array();
if(strpos($article["description_advice"],"\n") !== false || strpos($article["description_advice"],"\r") !== false){
$article["description_advice"] = preg_replace('/\\r\\n|\\n|\\r/', ' ', $article["description_advice"]); // \r\n in umwandeln
}
// return $this->render('update/index.html.twig', ["return" => ['article' => $article]]);
if(isset($article["block_advice"]) && !empty($article["block_advice"]) && $article["block_advice"]!=null){
preg_match('/([a-zA-Z]+)([0-9,]+)([a-zA-Z0-9,]+)/', $article["block_advice"], $blocknummer_zerlegt);
// dd($blocknummer_zerlegt);
if(strlen(end($blocknummer_zerlegt))>1){
$blocknummer = end($blocknummer_zerlegt);
}
}
if($article["materialgroup_name"] == null || empty($article["materialgroup_name"]) || $article["stone_number"] == null || empty($article["stone_number"])){
// ungültiges Material - Abbruch
$sqlt = "UPDATE storages SET
upload_done = ?,
upload_failed = ?
WHERE article_id = '".$article["article_id"]."';";
$stmtt = $conn_akn->prepare($sqlt);
$stmtt->execute(array(1,1));
continue;
}
if($blocknummer == null){
// nimm article id statt blocknummer
$article["product_number_parent"].=$article["article_id"];
}else{
$article["product_number_parent"].=$blocknummer;
}
// dd($blocknummer, $article["product_number_parent"]);
$kategorie = $this->checkCategory($article["materialgroup_name"], $article["materialgroup_id"],
$article["material_name"], $article["material_id"], $article["materials_picture"],false);
// return $this->render('update/index.html.twig', ["return" => [ 'zeit' => $article]]);
unset($productParentId);
$storage_option_list=array();
$storage_option_list = $this->get_variant_ids($article); //übergibt storage, returned formatierten array für variantenerstellung
// suche ob parent existiert
$sql = "SELECT
LOWER(HEX(id)) as id
-- , LOWER(HEX(category_id)) as category_id
FROM product
-- JOIN product_category ON (product_id = id)
-- WHERE product_number = '".$article["material_id"]."_".$article["materialgroup_id"]."_".$blocknummer."'
WHERE product_number = '".$article["product_number_parent"]."'
LIMIT 1
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$search_product = $stmt->fetchAll();
// Prüfe ob es Kategoriesynonyme gibt und füge die hinzo
$kats = $this->getAdditionalCategories($article["materialgroup_id"], $article["material_id"]);
$kategories=array();
$kategories[0]["id"]=$kategorie;
// $kategories[1]["id"]= $this->default_online_id; //ONLINELAGER ID ENTFERNT
if(isset($kats) && is_array($kats) && count($kats)>0){
foreach($kats as $key => $kat){
$kategories[$key+1]["id"]=$kat;
}
}
// return $this->render('update/index.html.twig', ["return" => [ 'kats' => $kategories, 'articles' => $article, 'article_id' => $search_product]]);
if(isset($search_product[0]["id"]) && !empty($search_product[0]["id"]) && count($search_product) > 0){
// baseartikel vorhanden, varianten überprüfen und updaten
$productParentId = $search_product[0]["id"];
}else{
// baseartikel muss erstellt werden
$ProductFlat = new \App\Controller\shopware\ProductFlat();
$ProductFlat->setActive(true);
// $ProductFlat->setCategories(array(array("id" => $kategorie)));
// if(isset($kategories) && is_array($kategories) && count($kategories) > 0){
$ProductFlat->setCategories($kategories);
// }else{
// $ProductFlat->setCategories(array(array("id" => $kategorie)));
// }
$ProductFlat->setDeliveryTimeId($this->deliverytime_id);
if($article["bestellbar"]==1){
$ProductFlat->setIsCloseout(false);
}else{
$ProductFlat->setIsCloseout(true);
}
$ProductFlat->setMarkAsTopseller(false);
$ProductFlat->setName($article["material_name"]);
$ProductFlat->setDescription($article["description_advice"]);
$ProductFlat->setPrice(array(array(
"currencyId" => $this->currency_id,
"net" => 0,
"gross" => 0,
"linked" => false
)));
$ProductFlat->setProductNumber($article["product_number_parent"]);
$ProductFlat->setPurchaseUnit(1);
$ProductFlat->setTaxId($this->tax_id);
$ProductFlat->setVisibilities(array(array(
"salesChannelId" => $this->saleschannel_id,
"visibility" => 30
)));
$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"] = $article["material_name"];
$custom_fields["custom_article_materialgroup_name"] = $article["materialgroup_name"];
$custom_fields["custom_article_picture"] = "default_art.jpg";
$ProductFlat->setCustomFields(
$custom_fields
);
$ProductFlat->setHeight($article["thickness"]);
$ProductFlat->setLength($article["length"]);
$ProductFlat->setWidth($article["width"]);
$ProductFlat->setStock(0);
// return $this->render('update/index.html.twig', ["return" => ['nummern' => $ProductFlat]]);
$poststring = 'api/product';
if( isset($article["material_name"]) && !empty($article["material_name"])){
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()) {
$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;
}
$productParentId = $post_product->getHeaders();
$productParentId = str_replace($this->base_url.$poststring.'/','',$productParentId["Location"]); // ID des erstellten produktes
$productParentId = $productParentId[0];
}
}
// Lösche Kategorien vorab zum überschreiben
$sql = "UPDATE product
SET category_ids = NULL,
category_tree = NULL
WHERE product_number = '".$article['product_number_parent']."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$sql = "DELETE
FROM product_category
WHERE LOWER(HEX(product_id)) = '".$productParentId."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
// ANFANG PATCH MAINPRODUKT
$ProductFlat = new \App\Controller\shopware\ProductFlat();
$ProductFlat->setActive(true);
// if(isset($kategories) && is_array($kategories) && count($kategories) > 0){
$ProductFlat->setCategories($kategories);
// }else{
// $ProductFlat->setCategories(array(array("id" => $kategorie)));
// }
$ProductFlat->setDeliveryTimeId($this->deliverytime_id);
if($article["bestellbar"]==1){
$ProductFlat->setIsCloseout(false);
}else{
$ProductFlat->setIsCloseout(true);
}
$ProductFlat->setMarkAsTopseller(false);
$ProductFlat->setName($article["material_name"]);
$ProductFlat->setDescription($article["description_advice"]);
$ProductFlat->setPrice(array(array(
"currencyId" => $this->currency_id,
"net" => 0,
"gross" => 0,
"linked" => false
)));
$ProductFlat->setProductNumber($article["product_number_parent"]);
$ProductFlat->setPurchaseUnit(1);
$ProductFlat->setTaxId($this->tax_id);
$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"] = $article["material_name"];
$custom_fields["custom_article_materialgroup_name"] = $article["materialgroup_name"];
if(isset($article["storages_picture"]) && !empty($article["storages_picture"]) && $article["storages_picture"] != null){
$custom_fields["custom_article_picture"] = $article["storages_picture"];
}elseif(isset($article["materials_picture"]) && !empty($article["materials_picture"]) && $article["materials_picture"] != null){
$custom_fields["custom_article_picture"] = $article["materials_picture"];
}else{
$custom_fields["custom_article_picture"] = $article["picture_parent"];
}
$ProductFlat->setCustomFields(
$custom_fields
);
$ProductFlat->setHeight($article["thickness"]);
$ProductFlat->setLength($article["length"]);
$ProductFlat->setWidth($article["width"]);
$ProductFlat->setStock(0);
if(isset($storage_option_list) && is_array($storage_option_list) && count($storage_option_list)>0){
$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
),
array(
"id" => $this->artikeldaten_innen_id,
"representation" => "box",
"expressionForListings" => false
),
array(
"id" => $this->artikeldaten_aussen_id,
"representation" => "box",
"expressionForListings" => false
)
));
}
// return $this->render('update/index.html.twig', ["return" => ['nummern' => $ProductFlat]]);
$poststring = 'api/product';
if( isset($article["material_name"]) && !empty($article["material_name"])){
try {
$post_product = $this->client->request('PATCH', '/'.$poststring.'/'.$productParentId, array(
// "debug" => true,
'allow_redirects'=>array('strict'=>true),
"headers" => $this->header,
"body" => $ProductFlat
));
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
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;
}
}
// ENDE PATCH MAINPRODUKT
// Prüfe ob Variante vorhanden ist
$sql = "SELECT
*
, LOWER(HEX(product_id)) as product_id
, HEX(product_version_id) as product_version_id
, LOWER(HEX(parent_id)) as parent_id
, HEX(language_id) as language_id
, JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_picture') as custom_article_picture
, JSON_EXTRACT(product_translation.custom_fields,'$.custom_article_picture_unavailable') as custom_article_picture_unavailable
FROM product_translation
INNER JOIN product ON (product.id = product_id)
WHERE product_number = '".$article["stone_number"]."'
AND LOWER(HEX(language_id)) = '".$this->language_id."'
LIMIT 3
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$articlesSW = $stmt->fetchAll();
// return $this->render('update/index.html.twig', ["return" => ['storages' => $articlesSW]]);
unset($farbid);
$variant_id ="";
// dd($articlesSW);
//** STORAGE ERSTELLEN
if(!isset($articlesSW) || empty($articlesSW) || count($articlesSW)<1 ){
// return $this->render('update/index.html.twig', ["return" => ['storages' => $storage_option_list]]);
// variantenartikel aktualisieren
$ProductFlat = new \App\Controller\shopware\ProductFlat();
$ProductFlat->setName($article["material_name"]);
$ProductFlat->setParentId($productParentId);
$ProductFlat->setActive(true);
$ProductFlat->setProductNumber($article["stone_number"]); //stone_number einzigartig
if(isset($article["amount_1"]) && !empty($article["amount_1"]) && isset($article["reserved_amount_1"]) && !empty($article["reserved_amount_1"]) ){
$stock = $article["amount_1"]-$article["reserved_amount_1"];
$ProductFlat->setStock(intval($stock));
}else{
$ProductFlat->setStock(intval($article["amount_1"]));
}
if($article["bestellbar"]==1){
$ProductFlat->setIsCloseout(false);
}else{
$ProductFlat->setIsCloseout(true);
}
$ProductFlat->setHeight($article["thickness"]);
$ProductFlat->setLength($article["length"]);
$ProductFlat->setWidth($article["width"]);
if(isset($article["restprice"]) && !empty(intval($article["restprice"])) && intval($article["restprice"])>0){
$base_price = $article["restprice"];
}else{
$base_price = $article["price"];
}
if(isset($stock) && !empty(intval($stock)) && intval($stock)!=0){
$price_calc = round(($base_price*$article["amount_2"])/intval($stock),2);
}else{
$price_calc = round(($base_price*$article["amount_2"]),2);
}
$ProductFlat->setPrice(array(array(
"currencyId" => $this->currency_id,
"net" => $price_calc,
"gross" => $price_calc,
"linked" => false
)));
$sol_option=array();
foreach($storage_option_list as $key => $sol){
$sol_option[$key]["id"]=$sol["optionId"];
}
// dd($sol_option);
$ProductFlat->setOptions($sol_option);
$ProductFlat->setProperties($sol_option);
if( $article["fault_id"] != 0 && !empty($article["fault_id"]) && $article["fault_corner"] != 0 && !empty($article["fault_corner"])){
//prüfe ob beides gesetzt, wenn ja definier beides, wenn nicht beides leer
$custom_fields["custom_article_fault_id"] = $article["fault_name"];
$custom_fields["custom_article_fault_corner"] = $article["fault_corner"];
$custom_fields["custom_article_fault_exists"] = 1;
}else{
$custom_fields["custom_article_fault_exists"] = 0;
}
$custom_fields=array();
$custom_fields["custom_article_id"] = $article["article_id"];
$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_surface"] = $article["surface_name"];
$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"];
if(isset($article["amount_2"]) && !empty($article["amount_2"]) && isset($article["reserved_amount_2"]) && !empty($article["reserved_amount_2"]) ){
$amount2 = $article["amount_2"]-$article["reserved_amount_2"];
$custom_fields["custom_article_amount_2"] = strval($amount2);
}else{
$custom_fields["custom_article_amount_2"] = $article["amount_2"];
}
$custom_fields["custom_article_einheit_2"] = $article["einheit_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_name_1"] = $article["article_name_1"];
$custom_fields["custom_article_name_2"] = $article["article_name_2"];
$custom_fields["custom_article_additional_advice"] = $article["additional_advice"];
if(isset($article["storages_picture"]) && !empty($article["storages_picture"]) && $article["storages_picture"] != null){
$custom_fields["custom_article_picture"] = $article["storages_picture"];
}elseif(isset($storags["materials_picture"]) && !empty($article["materials_picture"]) && $article["materials_picture"] != null){
$custom_fields["custom_article_picture"] = $article["materials_picture"];
}else{
$custom_fields["custom_article_picture"] = $article["picture_parent"];
}
$custom_fields["custom_article_price_per_unit"]=$article["price"];
$custom_fields["custom_article_price_calculation"]=$article["length"]." cm * ".$article["width"]." cm * ".$article["price"]."€ = ".$price_calc."€";
if(isset($article["color_text"]) && !empty($article["color_text"])){
$color = explode('#',$article["color_text"]);
$article["color_text"] = trim($color[0]);
$custom_fields["custom_article_material_color"] = $article["color_text"];
}
$ProductFlat->setCustomFields($custom_fields);
try {
//#* Post Variante
$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()) {
$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;
}
$location = array_pop( $post_product->getHeaders()['Location'] );
$variant_id = basename($location);
}else{
$variant_id = $articlesSW[0]["product_id"];
//vorher options und properties bereinigen
$sql = "DELETE
FROM product_option
WHERE LOWER(HEX(product_id)) = '".$variant_id."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$sql = "DELETE
FROM product_property
WHERE LOWER(HEX(product_id)) = '".$variant_id."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
//### variante updaten
$ProductFlat = new \App\Controller\shopware\ProductFlat();
$ProductFlat->setName($article["material_name"]);
$ProductFlat->setParentId($productParentId);
$ProductFlat->setActive(true);
$ProductFlat->setProductNumber($article["stone_number"]); //stone_number unique
if(isset($article["amount_1"]) && !empty($article["amount_1"]) && isset($article["reserved_amount_1"]) && !empty($article["reserved_amount_1"]) ){
$stock = $article["amount_1"]-$article["reserved_amount_1"];
$ProductFlat->setStock(intval($stock));
}else{
$ProductFlat->setStock(intval($article["amount_1"]));
}
if($article["bestellbar"]==1){
$ProductFlat->setIsCloseout(false);
}else{
$ProductFlat->setIsCloseout(true);
}
$ProductFlat->setHeight($article["thickness"]);
$ProductFlat->setLength($article["length"]);
$ProductFlat->setWidth($article["width"]);
if(isset($article["restprice"]) && !empty(intval($article["restprice"])) && intval($article["restprice"])>0){
$base_price = $article["restprice"];
}else{
$base_price = $article["price"];
}
if(isset($stock) && !empty(intval($stock)) && intval($stock)!=0){
$price_calc = round(($base_price*$article["amount_2"])/intval($stock),2);
}else{
$price_calc = round(($base_price*$article["amount_2"]),2);
}
$ProductFlat->setPrice(array(array(
"currencyId" => $this->currency_id,
"net" => $price_calc,
"gross" => $price_calc,
"linked" => false
)));
$sol_option=array();
foreach($storage_option_list as $key => $sol){
$sol_option[$key]["id"]=$sol["optionId"];
}
$ProductFlat->setOptions($sol_option);
$ProductFlat->setProperties($sol_option);
if( $article["fault_id"] != 0 && !empty($article["fault_id"]) && $article["fault_corner"] != 0 && !empty($article["fault_corner"])){
//prüfe ob beides gesetzt, wenn ja definier beides, wenn nicht beides leer
$custom_fields["custom_article_fault_id"] = $article["fault_name"];
$custom_fields["custom_article_fault_corner"] = $article["fault_corner"];
$custom_fields["custom_article_fault_exists"] = 1;
}else{
$custom_fields["custom_article_fault_exists"] = 0;
}
$custom_fields=array();
$custom_fields["custom_article_id"] = $article["article_id"];
$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_surface"] = $article["surface_name"];
$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"];
if(isset($article["amount_2"]) && !empty($article["amount_2"]) && isset($article["reserved_amount_2"]) && !empty($article["reserved_amount_2"]) ){
$amount2 = $article["amount_2"]-$article["reserved_amount_2"];
$custom_fields["custom_article_amount_2"] = strval($amount2);
}else{
$custom_fields["custom_article_amount_2"] = $article["amount_2"];
}
$custom_fields["custom_article_einheit_2"] = $article["einheit_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_name_1"] = $article["article_name_1"];
$custom_fields["custom_article_name_2"] = $article["article_name_2"];
$custom_fields["custom_article_additional_advice"] = $article["additional_advice"];
if(isset($article["storages_picture"]) && !empty($article["storages_picture"]) && $article["storages_picture"] != null){
$custom_fields["custom_article_picture"] = $article["storages_picture"];
}elseif(isset($article["materials_picture"]) && !empty($article["materials_picture"]) && $article["materials_picture"] != null){
$custom_fields["custom_article_picture"] = $article["materials_picture"];
}else{
$custom_fields["custom_article_picture"] = $article["picture_parent"];
}
if(isset($article["color_text"]) && !empty($article["color_text"])){
$color = explode('#',$article["color_text"]);
$article["color_text"] = trim($color[0]);
$custom_fields["custom_article_material_color"] = $article["color_text"];
}
$custom_fields["custom_article_price_per_unit"]=$article["price"];
$custom_fields["custom_article_price_calculation"]=$article["length"]." cm * ".$article["width"]." cm * ".$article["price"]."€ = ".$price_calc."€";
$ProductFlat->setCustomFields($custom_fields);
try {
//#* Patch Variante
$post_product = $this->client->request('PATCH', '/api/product/'.$variant_id, array(
// "debug" => true,
'allow_redirects'=>array('strict'=>true),
"headers" => $this->header,
"body" => $ProductFlat
// "form_params" => array($variant_values)
));
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
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 KEYWORDS ###################
if(isset($variant_id) && !empty($variant_id) && $variant_id != ""){
if(isset($blocknummer) && !empty($blocknummer) && $blocknummer != ""){
$keywords = [ $article['search_word'], $blocknummer ];
}else{
$keywords = [ $article['search_word'] ];
}
try {
$post_product = $this->client->request('PATCH', '/api/product/'.$variant_id, 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());
// return $this->render('update/index.html.twig', ["return" => [ 'request' => Psr7\str($e->getRequest()]]);
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 ###################
//###*** 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'], $variant_id, false);
}elseif(isset( $article['materials_picture']) && !empty( $article['materials_picture']) && $article['materials_picture'] != ""){
$this->add_image($article['materials_picture'], $variant_id, false);
}else{
$this->add_image($this->default_image_filename, $variant_id, false);
}
// product_configurator_setting matchen und option ids abgleichen + erstellen wenn nicht vorhanden
$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');
// return $this->render('update/index.html.twig', ["return" => [ 'storag' => $config_settings, 'liste' => $config_list]]);
// fehlende optionen in variantengenerator hinzufügen
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' => json_encode([
"optionId"=>$option
])
));
} catch (RequestException $e) {
// echo Psr7\str($e->getRequest());
// if ($e->hasResponse()) {
// echo Psr7\str($e->getResponse());
// }
}
}
}
// return $this->render('update/index.html.twig', ["return" => [ 'storag' => $config_settings, "optionlist" => $storage_option_list]]);
//### Setze Date bei Storages
$sqlt = "UPDATE storages SET
upload_done = ?
WHERE id = '".$article["storage_id"]."'
;";
$stmtt = $conn_akn->prepare($sqlt);
$stmtt->execute(array(
1
));
}
// return $this->render('update/index.html.twig', ["return" => [ 'storag' => "pagedone"]]);
if($next_page==true){
return $this->redirect('/update/products');
}
//### ENDE Produktimport
// exit;
//setze alle storages auf upload failed die keine stone number besitzen
$sqlt = "UPDATE storages SET
upload_done = ?,
upload_failed = ?
-- WHERE stone_number = ''
WHERE (upload_done IS NULL AND upload_failed IS NULL) OR (upload_done = 4 AND upload_failed IS NULL)
;";
$stmtt = $conn_akn->prepare($sqlt);
$stmtt->execute(array(
1,
3
));
// $sql = "SELECT
// *
// FROM pixel_dates
// WHERE FunctionName = 'update_products'
// ;";
// $stmt = $conn_shopware->prepare($sql);
// $stmt->execute();
// $date_select = $stmt->fetchAll();
// if(isset($date_select) && !empty($date_select) && count($date_select) > 0){
// $sqlt = "UPDATE pixel_dates SET
// UpdateDatetime = ?
// WHERE FunctionName = 'update_products'
// ;";
// $stmtt = $conn_shopware->prepare($sqlt);
// $stmtt->execute(array(
// date('Y-m-d H:i:s')
// ));
// }else{
// $sql = "INSERT INTO pixel_dates (FunctionName,UpdateDatetime)
// VALUES(?,?)";
// $stmtt = $conn_shopware->prepare($sql);
// $stmtt->execute(array(
// "update_products done",
// date('Y-m-d H:i:s')
// ));
// }
$sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,UpdateDatetime)
VALUES(?,?,?)";
$stmtt = $conn_shopware->prepare($sql);
$stmtt->execute(array(
"update/products",
"ENDE",
date('Y-m-d H:i:s')
));
$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" => [ 'status' => "done"]]);
}
/**
* Leert die Logs im Shopware Backend
*
* @Route("/clear/logs", name="clear_logs")
*
* @return mixed
*/
function clear_logs()
{
// $this->client_connect();
$conn_shopware = $this->shop_connect();
$sql = "TRUNCATE TABLE log_entry";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
return $this->render('update/index.html.twig', ["return" => [ 'status' => "Logs erfolgreich geleert."]]);
}
/**
* Leert die Tabellen zur Hintergrundverarbeitung von Shopware
*
* @Route("/clear/queue", name="clear_queue")
*
* @return mixed
*/
function clear_queue()
{
// $this->client_connect();
$conn_shopware = $this->shop_connect();
$sql = "TRUNCATE TABLE enqueue";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$sql = "TRUNCATE TABLE message_queue_stats";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
return $this->render('update/index.html.twig', ["return" => [ 'status' => "Queue erfolgreich geleert."]]);
}
/**
* Sortiert Kategorien in Shopware
*
* @Route("/sort/categories/{page}", name="sort_categories")
*
* @return mixed
*/
function sort_categories($page)
{
// hole alle kategorien in steine aus aller welt ab,
// hole für jede alle kategorien ab die diese als parent haben
// erstelle array aus id => name
// sortiere namen asc/desc
// update after_category_id. erste kategorie leer, rest id vom vorgänger
$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, LOWER(HEX(after_category_id)) AS after_category_id
FROM category
INNER JOIN category_translation ON (category.id = category_translation.category_id)
WHERE LOWER(HEX(parent_id)) = '".$this->default_category_id."'
AND LOWER(HEX(language_id)) = '".$this->language_id."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$categories_SELECT = $stmt->fetchAll();
$keys = array_column($categories_SELECT, 'name');
array_multisort($keys, SORT_ASC, $categories_SELECT);
// dd($categories_SELECT);
// if(isset($categories_SELECT) && is_array($categories_SELECT) && count($categories_SELECT) > 0) {
// foreach($categories_SELECT as $category) {
if(isset($categories_SELECT[$page]) && is_array($categories_SELECT[$page]) && count($categories_SELECT[$page]) > 0) {
// foreach($categories_SELECT as $category) {
// dd($category["category_id"]);
// -- WHERE LOWER(HEX(parent_id)) = '".$category["category_id"]."'
$sql = "SELECT LOWER(HEX(id)) AS category_id, category_translation.name, LOWER(HEX(after_category_id)) AS after_category_id
FROM category
INNER JOIN category_translation ON (category.id = category_translation.category_id)
WHERE LOWER(HEX(parent_id)) = '".$categories_SELECT[$page]["category_id"]."'
-- WHERE LOWER(HEX(parent_id)) = '91bb5a08283d40a3bf1ec0a4759660b1'
AND LOWER(HEX(language_id)) = '".$this->language_id."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$subcategories = $stmt->fetchAll();
$keys = array_column($subcategories, 'name');
array_multisort($keys, SORT_ASC, $subcategories);
// dd($subcategories);
//setze after ids neu
foreach($subcategories as $key => $subcategory){
$CategoryFlat = new \App\Controller\shopware\CategoryFlat();
if($key==0){
//setze id auf null
$CategoryFlat->setAfterCategoryId(null);
}else{
// setze id auf $key-1
$CategoryFlat->setAfterCategoryId($subcategories[$key-1]["category_id"]);
// var_dump("patch ".$subcategory["name"]. " folgt ".$subcategories[$key-1]["name"]);
}
// try{
$post_category = $this->client->request('PATCH', '/api/category/'.$subcategory["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());
// }
// exit;
// }
}
// dd($categories_SELECT[$page+1]);
if(isset($categories_SELECT[$page+1]) && is_array($categories_SELECT[$page+1]) && count($categories_SELECT[$page+1]) > 0){
return $this->redirect('/sort/categories/'.($page+1));
}
//sortiere parents
foreach($categories_SELECT as $key => $category){
$CategoryFlat = new \App\Controller\shopware\CategoryFlat();
if($key==0){
//setze id auf null
$CategoryFlat->setAfterCategoryId(null);
}else{
// setze id auf $key-1
$CategoryFlat->setAfterCategoryId($categories_SELECT[$key-1]["category_id"]);
// var_dump("patchober ".$subcategory["name"]. " folgt ".$subcategories[$key-1]["name"]);
}
$post_category = $this->client->request('PATCH', '/api/category/'.$category["category_id"], array(
// 'debug' => true,
'allow_redirects'=>array('strict'=>true),
"headers" => $this->header,
"body" => $CategoryFlat,
));
}
}
# CLEAR CACHE ##########################
$this->clear_cache();
return $this->render('update/index.html.twig', ["return" => [ 'status' => $subcategories, 'key' => $keys]]);
}
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("/sql_import", name="sql_import")
*
* @return mixed
*/
public function sql_import(MailerInterface $mailer) {
$this->client_connect();
$conn_akn = $this->dump_connect();
$conn_shopware = $this->shop_connect();
// $location_sql = "/var/www/vhosts/pixelproductions.de/sw6.pixelproductions.de/akn_upload/"; // DEV
// $location_sql = "/home/aknnadbt/shop.akn-natursteine.de/UPLOAD/"; // LIVE
// $location_sql = "/var/www/vhosts/akn-natursteine.de/shop.akn-natursteine.de/UPLOAD/"; // LIVE NACH UMZUG
$location_sql = "/var/www/vhosts/swconnect.akn-natursteine.de/httpdocs/public/UPLOAD/"; // LIVE NACH IONOS UMZUG
$target_location = $this->getParameter('kernel.project_dir')."/public/sql/";
$file_name = "import";
// dd("connected");
$sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,UpdateDatetime)
VALUES(?,?,?)";
$stmtt = $conn_shopware->prepare($sql);
$stmtt->execute(array(
"sql_import",
"Start",
date('Y-m-d H:i:s')
));
if( (file_exists($target_location.$file_name."_backup.sql") && file_exists($location_sql.$file_name.".sql") &&
filemtime($location_sql.$file_name.".sql") > filemtime($target_location.$file_name."_backup.sql")) ||
(!file_exists($target_location.$file_name."_backup.sql") && file_exists($location_sql.$file_name.".sql")) ){
// wenn kein backupfile existiert oder der importfile neuer als der backupfile ist importiere sql
copy($location_sql.$file_name.".sql",$target_location.$file_name.".sql");
}else{
$sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,UpdateDatetime)
VALUES(?,?,?)";
$stmtt = $conn_shopware->prepare($sql);
$stmtt->execute(array(
"sql_import",
"ENDE",
date('Y-m-d H:i:s')
));
echo "Keine (neue) Datei vorhanden!";
exit;
}
// #### copy tables for update
$sql = "TRUNCATE TABLE storages_alt";
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
$sql = "INSERT INTO storages_alt SELECT * FROM storages;";
$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 = explode(';', $sql_import);
foreach($sql_import as $key => $query) {
if( trim($query) != '') {
// dd($query);
try{
# DO SQL STUFF #################
# SKIP
if( str_starts_with( strtolower( trim( $query ) ), 'use' )) continue;
// if( str_starts_with( strtolower( trim( $query ) ), 'truncate' )) continue;
$sql = $query.';';
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
// $data = $stmt->fetchAll();
# TODO: CATCH ERRORS
} catch (RequestException $e) {
$sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,UpdateDatetime)
VALUES(?,?,?)";
$stmtt = $conn_shopware->prepare($sql);
$stmtt->execute(array(
"sql_import FEHLER AUFGETRETEN IN ZEILE ".$key." DER SQL",
"ERROR",
date('Y-m-d H:i:s')
));
$this->restore_sql();
//lade alte sql wieder rein
// echo Psr7\str($e->getRequest());
// sende errer als mailtext
// echo Psr7\str($e->getResponse());
if ($e->hasResponse()) {
$email = (new Email())
->from('mo@pixelproductions.de')
->to('mo@pixelproductions.de')
//->cc('cc@example.com')
//->bcc('bcc@example.com')
//->replyTo('fabien@example.com')
//->priority(Email::PRIORITY_HIGH)
->subject('Fehler beim SQL Import bei AKN')
->text('Es ist ein Fehler aufgetreten.')
->html('Beim SQL-Import ist ein Fehler aufgetreten. 
Wann? '.date('Y-m-d H:i:s').' (Serverzeit) 
letzte Backupdatei wird eingespielt. 
Fehler: '.Psr7\str($e->getResponse()));
$mailer->send($email);
}
exit;
}
}
}
//file umbenennen nach erfolgreichem einspielen
rename($target_location.$file_name.".sql",$target_location.$file_name."_backup.sql");
// #### check differences in tables
$sql = "SELECT stone_number
FROM storages
WHERE
(`kind`,article_id,materialgroup_id,material_id,surface_id,`length`,width,thickness,origin,stone_number,block_number,advice,storage,quantity_unit_1,quantity_unit_2,gross_length,gross_width,gross_thickness,amount_1,amount_2,reserved_amount_1,reserved_amount_2,articlegroup_id,fault_id,fault_corner,picture,restprice,price,block_advice,additional_advice)
NOT IN (SELECT
`kind`,article_id,materialgroup_id,material_id,surface_id,`length`,width,thickness,origin,stone_number,block_number,advice,storage,quantity_unit_1,quantity_unit_2,gross_length,gross_width,gross_thickness,amount_1,amount_2,reserved_amount_1,reserved_amount_2,articlegroup_id,fault_id,fault_corner,picture,restprice,price,block_advice,additional_advice
FROM storages_alt)
AND stone_number IS NOT NULL
AND stone_number !=''
;";
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
$changed_rows = $stmt->fetchAll();
// return $this->render('update/index.html.twig', ["return" => ['test' => $changed_rows ]]);
$changed_rows = array_column($changed_rows, 'stone_number');
// set priority to changed rows
$sql = "UPDATE storages
SET upload_done = 4
WHERE stone_number IN ('".implode("', '",$changed_rows)."');";
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
// #### check differences in artikeldaten
$this->createProductfinderList();
$sql = "SELECT material,material_group
FROM artikeldaten
WHERE
(material,material_group,innen_kueche,innen_fenster,innen_bad,innen_fussboden,innen_boden,aussen_boden,aussen_fassade,aussen_fenster,aussen_stufen,aussen_salz,bestellbar
)
NOT IN (SELECT
material,material_group,innen_kueche,innen_fenster,innen_bad,innen_fussboden,innen_boden,aussen_boden,aussen_fassade,aussen_fenster,aussen_stufen,aussen_salz,bestellbar
FROM artikeldaten_alt)
;";
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
$changed_rows = $stmt->fetchAll();
// return $this->render('update/index.html.twig', ["return" => ['test' => $changed_rows ]]);
foreach($changed_rows as $row){
// set priority to changed rows
$sql = "UPDATE storages
SET upload_done = 4
WHERE material_id = '".$row["material"]."'
AND materialgroup_id = '".$row["material_group"]."'
;";
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
}
}
$this->createCrosssellingList();
$sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,UpdateDatetime)
VALUES(?,?,?)";
$stmtt = $conn_shopware->prepare($sql);
$stmtt->execute(array(
"sql_import",
"ENDE",
date('Y-m-d H:i:s')
));
return $this->redirect('/excel_import');
# 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' => "SQL importiert" ]]);
}
/**
* Importiert EXCEL
*
* @Route("/excel_import", name="excel_import")
*
* @return mixed
*/
public function excel_import(MailerInterface $mailer) {
$this->client_connect();
$conn_akn = $this->dump_connect();
$conn_shopware = $this->shop_connect();
// $location_csv = "/var/www/vhosts/akn-natursteine.de/shop.akn-natursteine.de/UPLOAD/"; // LIVE NACH UMZUG
$location_csv = "/var/www/vhosts/swconnect.akn-natursteine.de/httpdocs/public/UPLOAD/"; // LIVE NACH IONOS UMZUG
$target_location = $this->getParameter('kernel.project_dir')."/public/excel/";
$file_name = "Artikeldaten";
// $filetype = ".xlsx";
$filetype = ".csv";
$sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,UpdateDatetime)
VALUES(?,?,?)";
$stmtt = $conn_shopware->prepare($sql);
$stmtt->execute(array(
"excel_import",
"Start",
date('Y-m-d H:i:s')
));
if( (file_exists($target_location.$file_name."_backup".$filetype) && file_exists($location_csv.$file_name.$filetype) &&
filemtime($location_csv.$file_name.$filetype) > filemtime($target_location.$file_name."_backup".$filetype)) ||
(!file_exists($target_location.$file_name."_backup".$filetype) && file_exists($location_csv.$file_name.$filetype)) ){
// wenn kein backupfile existiert oder der importfile neuer als der backupfile ist importiere sql
copy($location_csv.$file_name.$filetype,$target_location.$file_name.$filetype);
}else{
$sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,UpdateDatetime)
VALUES(?,?,?)";
$stmtt = $conn_shopware->prepare($sql);
$stmtt->execute(array(
"sql_import",
"ENDE",
date('Y-m-d H:i:s')
));
echo "Keine (neue) Datei vorhanden!";
exit;
}
if(file_exists($target_location.$file_name.$filetype) ){
$sql = "TRUNCATE TABLE artikeldaten";
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
$excel_file = $target_location.$file_name.$filetype;
}else{
$sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,UpdateDatetime)
VALUES(?,?,?)";
$stmtt = $conn_shopware->prepare($sql);
$stmtt->execute(array(
"sql_import",
"ENDE",
date('Y-m-d H:i:s')
));
echo "Datei nicht vorhanden!";
exit;
}
if( file_exists($excel_file)) {
$excel_import = file_get_contents($excel_file);
$lines = array_map("rtrim", explode("\n", $excel_import));
foreach($lines as $key => $query) {
if($key == 0|| $key == 1){
continue;
}
if( trim($query) != '') {
$line = explode(';',$query);
for ($i=4; $i <13 ; $i++) {
if($line[$i]=="j"){
$line[$i]=1;
}elseif($line[$i]=="n"){
$line[$i]=0;
}else{
$line[$i]=2;
}
}
if($line[17]=="x"){
$line[17]=1;
}else{
$line[17]=0;
}
try{
$sql = "INSERT INTO artikeldaten (material,material_group,innen_kueche,innen_fenster,innen_bad,innen_fussboden,innen_boden,aussen_boden,aussen_fassade,aussen_fenster,aussen_stufen,aussen_salz,bestellbar)
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)";
$stmtt = $conn_akn->prepare($sql);
$stmtt->execute(array(
$line[2],
$line[0],
$line[4],
$line[5],
$line[6],
$line[7],
$line[8],
$line[9],
$line[10],
$line[11],
$line[12],
$line[13],
$line[17]
));
# TODO: CATCH ERRORS
} catch (RequestException $e) {
$sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,UpdateDatetime)
VALUES(?,?,?)";
$stmtt = $conn_shopware->prepare($sql);
$stmtt->execute(array(
"csv_import FEHLER AUFGETRETEN IN ZEILE ".$key." DER SQL",
"ERROR",
date('Y-m-d H:i:s')
));
$this->restore_excel();
if ($e->hasResponse()) {
$email = (new Email())
->from('mo@pixelproductions.de')
->to('mo@pixelproductions.de')
//->cc('cc@example.com')
//->bcc('bcc@example.com')
//->replyTo('fabien@example.com')
//->priority(Email::PRIORITY_HIGH)
->subject('Fehler beim SQL Import bei AKN')
->text('Es ist ein Fehler aufgetreten.')
->html('Beim SQL-Import ist ein Fehler aufgetreten. 
Wann? '.date('Y-m-d H:i:s').' (Serverzeit) 
letzte Backupdatei wird eingespielt. 
Fehler: '.Psr7\str($e->getResponse()));
$mailer->send($email);
}
exit;
}
}
}
}
//file umbenennen nach erfolgreichem einspielen
rename($target_location.$file_name.$filetype,$target_location.$file_name."_backup".$filetype);
$sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,UpdateDatetime)
VALUES(?,?,?)";
$stmtt = $conn_shopware->prepare($sql);
$stmtt->execute(array(
"csv_import",
"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' => "CSV importiert" ]]);
}
/**
* Stellt den Stand der SQL wieder her
*/
public function restore_sql()
{
$this->client_connect();
$conn_akn = $this->dump_connect();
$conn_shopware = $this->shop_connect();
$sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,UpdateDatetime)
VALUES(?,?,?)";
$stmtt = $conn_shopware->prepare($sql);
$stmtt->execute(array(
"restore_sql",
"Start",
date('Y-m-d H:i:s')
));
$location = $this->getParameter('kernel.project_dir')."/public/sql/";
$file_name = "import";
// $file_name = "/import_backup.sql";
if(file_exists($location.$file_name."_backup.sql")){
$sql_import = file_get_contents($location.$file_name."_backup.sql");
$sql_import = str_replace(["\n", "\r"], "", $sql_import);
// $sql_import = str_replace("`", "", $sql_import);
$sql_import = explode(';', $sql_import);
foreach($sql_import as $key => $query) {
if( trim($query) != '') {
try{
# SKIP
if( str_starts_with( strtolower( trim( $query ) ), 'use' )) continue;
$sql = $query.';';
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
// $data = $stmt->fetchAll();
} catch (RequestException $e) {
//lade alte sql wieder rein
$sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,UpdateDatetime)
VALUES(?,?,?)";
$stmtt = $conn_shopware->prepare($sql);
$stmtt->execute(array(
"restore_sql FEHLER BEIM SQL WIEDERHERSTELLEN IN ZEILE ".$key." DER SQL",
"ERROR",
date('Y-m-d H:i:s')
));
if ($e->hasResponse()) {
// sende errer als mailtext
$nachricht = Psr7\str($e->getResponse());
$nachricht = wordwrap($nachricht, 70, "\r\n");
mail('mo@pixelproductions.de', 'AKN SQL RESTORE ERROR', $nachricht);
}
// exit;
}
}
}
// dd("no copy!");
$sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,UpdateDatetime)
VALUES(?,?,?)";
$stmtt = $conn_shopware->prepare($sql);
$stmtt->execute(array(
"restore_sql",
"ENDE",
date('Y-m-d H:i:s')
));
//file umbenennen nach erfolgreichem einspielen
copy($location.$file_name."_backup.sql",$location.$file_name.".sql");
}
}
/**
* Stellt den Stand der CSV wieder her
*/
public function restore_excel()
{
$this->client_connect();
$conn_akn = $this->dump_connect();
$conn_shopware = $this->shop_connect();
$sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,UpdateDatetime)
VALUES(?,?,?)";
$stmtt = $conn_shopware->prepare($sql);
$stmtt->execute(array(
"restore_excel",
"Start",
date('Y-m-d H:i:s')
));
$location = $this->getParameter('kernel.project_dir')."/public/excel/";
$file_name = "Artikeldaten";
$filetype = ".csv";
// $file_name = "/import_backup.sql";
if(file_exists($location.$file_name."_backup".$filetype)){
$excel_import = file_get_contents($location.$file_name."_backup".$filetype);
$lines = array_map("rtrim", explode("\n", $excel_import));
foreach($lines as $key => $query) {
if($key == 0|| $key == 1){
continue;
}
if( trim($query) != '') {
$line = explode(';',$query);
for ($i=4; $i <13 ; $i++) {
if($line[$i]=="j"){
$line[$i]=1;
}elseif($line[$i]=="n"){
$line[$i]=0;
}else{
$line[$i]=2;
}
}
try{
$sql = "INSERT INTO artikeldaten (material,material_group,innen_kueche,innen_fenster,innen_bad,innen_fussboden,innen_boden,aussen_boden,aussen_fassade,aussen_fenster,aussen_stufen,aussen_salz)
VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
$stmtt = $conn_akn->prepare($sql);
$stmtt->execute(array(
$line[2],
$line[0],
$line[4],
$line[5],
$line[6],
$line[7],
$line[8],
$line[9],
$line[10],
$line[11],
$line[12],
$line[13]
));
# TODO: CATCH ERRORS
} catch (RequestException $e) {
$sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,UpdateDatetime)
VALUES(?,?,?)";
$stmtt = $conn_shopware->prepare($sql);
$stmtt->execute(array(
"restore_excel FEHLER BEIM CSV WIEDERHERSTELLEN IN ZEILE ".$key." DER CSV",
"ERROR",
date('Y-m-d H:i:s')
));
exit;
}
}
}
// dd("no copy!");
$sql = "INSERT INTO pixel_dates (FunctionName,StartEnde,UpdateDatetime)
VALUES(?,?,?)";
$stmtt = $conn_shopware->prepare($sql);
$stmtt->execute(array(
"restore_excel",
"ENDE",
date('Y-m-d H:i:s')
));
//file umbenennen nach erfolgreichem einspielen
copy($location.$file_name."_backup".$filetype,$location.$file_name.$filetype);
}
}
/**
* Löscht alle doppelten Bildverknüpfungen in den Artikeln und behält die Neuste
*
* @Route("/clean/pictures", name="clean_pictures")
*
* @return mixed
*/
public function clean_pictures(){
$conn_shopware = $this->shop_connect();
$sql = "SELECT *, count(id) as count, max(created_at) as maxi, lower(hex(product_id)) as product_id, lower(hex(media_id)) as media_id
FROM product_media
GROUP BY product_id, media_id
ORDER BY count(id) DESC
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$articles = $stmt->fetchAll();
// return $this->render('update/index.html.twig', ["return" => ['such123e' => $articles[0] ]]);
foreach($articles as $article){
if($article["count"]>1){
$sql = "DELETE
FROM product_media
WHERE lower(hex(product_id))= '".$article["product_id"]."'
AND lower(hex(media_id))= '".$article["media_id"]."'
AND created_at != '".$article["maxi"]."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$articl = $stmt->fetchAll();
// return $this->render('update/index.html.twig', ["return" => ['su4che' => $articl ]]);
}else{
return $this->render('update/index.html.twig', ["return" => ['cleanup' => "done"]]);
}
}
return $this->render('update/index.html.twig', ["return" => ['cleanup' => "done"]]);
}
/**
* Entfernt doppelte Bildverlinkungen in Artikeln
*
* @Route("/remove/duplicate", name="remove_duplicate")
*
* @return mixed
*/
public function remove_duplicate()
{
$this->client_connect();
$conn_shopware = $this->shop_connect();
$sql = "SELECT *, count(id) as count, max(created_at) as maxi, lower(hex(product_id)) as product_id, lower(hex(media_id)) as media_id
FROM product_media
GROUP BY product_id, media_id
ORDER BY count(id) DESC
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$articles = $stmt->fetchAll();
// return $this->render('update/index.html.twig', ["return" => ['such123e' => $articles[0] ]]);
foreach($articles as $article){
if($article["count"]>1){
$sql = "DELETE
FROM product_media
WHERE lower(hex(product_id))= '".$article["product_id"]."'
AND lower(hex(media_id))= '".$article["media_id"]."'
AND created_at != '".$article["maxi"]."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$articl = $stmt->fetchAll();
// return $this->render('update/index.html.twig', ["return" => ['su4che' => $articl ]]);
}else{
var_dump("done");exit;
}
}
return $this->render('update/index.html.twig', ["return" => ['remove' => "done" ]]);
}
/**
* test
*
* @Route("/testcompare", name="testcompare")
*
* @return mixed
*/
public function testcompare()
{
$conn_shopware = $this->shop_connect();
$conn_akn = $this->dump_connect();
// $sql = "TRUNCATE TABLE storages_alt";
// $stmt = $conn_akn->prepare($sql);
// $stmt->execute();
// $sql = "INSERT INTO storages_alt SELECT * FROM storages;";
// $stmt = $conn_akn->prepare($sql);
// $stmt->execute();
// $sql = "SELECT count(id) from storages_alt;";
// $stmt = $conn_akn->prepare($sql);
// $stmt->execute();
// $count = $stmt->fetchAll();
// return $this->render('update/index.html.twig', ["return" => ['test' => $count ]]);
//*** SQL IMPORT KOMPLETT
// $sql = "SELECT
// `kind`,article_id,materialgroup_id,material_id,surface_id,`length`,width,thickness,origin,stone_number,block_number,advice,storage,quantity_unit_1,quantity_unit_2,gross_length,gross_width,gross_thickness,amount_1,amount_2,reserved_amount_1,reserved_amount_2,articlegroup_id,fault_id,fault_corner,picture,restprice,price,block_advice,additional_advice
// FROM
// (
// SELECT
// `kind`,article_id,materialgroup_id,material_id,surface_id,`length`,width,thickness,origin,stone_number,block_number,advice,storage,quantity_unit_1,quantity_unit_2,gross_length,gross_width,gross_thickness,amount_1,amount_2,reserved_amount_1,reserved_amount_2,articlegroup_id,fault_id,fault_corner,picture,restprice,price,block_advice,additional_advice
// FROM storages
// UNION ALL
// SELECT
// `kind`,article_id,materialgroup_id,material_id,surface_id,`length`,width,thickness,origin,stone_number,block_number,advice,storage,quantity_unit_1,quantity_unit_2,gross_length,gross_width,gross_thickness,amount_1,amount_2,reserved_amount_1,reserved_amount_2,articlegroup_id,fault_id,fault_corner,picture,restprice,price,block_advice,additional_advice
// FROM storages_alt
// ) storages
// GROUP BY
// `kind`,article_id,materialgroup_id,material_id,surface_id,`length`,width,thickness,origin,stone_number,block_number,advice,storage,quantity_unit_1,quantity_unit_2,gross_length,gross_width,gross_thickness,amount_1,amount_2,reserved_amount_1,reserved_amount_2,articlegroup_id,fault_id,fault_corner,picture,restprice,price,block_advice,additional_advice
// HAVING COUNT(*) = 1
// ;";
$sql = "SELECT
stone_number
FROM storages
WHERE
(`kind`,article_id,materialgroup_id,material_id,surface_id,`length`,width,thickness,origin,stone_number,block_number,advice,storage,quantity_unit_1,quantity_unit_2,gross_length,gross_width,gross_thickness,amount_1,amount_2,reserved_amount_1,reserved_amount_2,articlegroup_id,fault_id,fault_corner,picture,restprice,price,block_advice,additional_advice)
NOT IN (SELECT
`kind`,article_id,materialgroup_id,material_id,surface_id,`length`,width,thickness,origin,stone_number,block_number,advice,storage,quantity_unit_1,quantity_unit_2,gross_length,gross_width,gross_thickness,amount_1,amount_2,reserved_amount_1,reserved_amount_2,articlegroup_id,fault_id,fault_corner,picture,restprice,price,block_advice,additional_advice
FROM storages_alt)
AND stone_number IS NOT NULL
AND stone_number !=''
;";
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
$changed_rows = $stmt->fetchAll();
return $this->render('update/index.html.twig', ["return" => ['test' => $changed_rows ]]);
// $changed_rows = array_column($changed_rows, 'stone_number');
// $sql = "UPDATE storages
// SET upload_done = 4
// WHERE stone_number IN ('".implode("', '",$changed_rows)."');";
// $stmt = $conn_akn->prepare($sql);
// $stmt->execute();
// // bei produktimport nimm die datensätze zuerst, die 4 dort gesestzt haben
// $sql = "SELECT *
// FROM storages
// WHERE stone_number IN ('".implode("', '",$changed_rows)."');";
// $stmt = $conn_akn->prepare($sql);
// $stmt->execute();
// $articles = $stmt->fetchAll();
}
public function getProductFinderCategory($verwendung, $farbe, $farbname){
// nimmt verwendungs id und farb id, prüft ob kategorie im produktfinder existiert, erstellt diese wenn es nicht der Fall ist und returned ID oder Null
if(empty(trim($verwendung)) || empty(trim($farbe)) ){
return null;
}
$this->client_connect();
$conn_shopware = $this->shop_connect();
// $streamId = $this->checkProductStream($verwendung,$farbe, $farbname);
// Kategorie: muss in 999967b27c834a0d897751a9e8517f8b (Produktfinder) enthalten sein
// darf nicht 8cadf3490764413e90872546f312ee6a (Innen) oder f408046cfc9f4960926682177c055397 (außen) sein
// suche über custom felder bei den kategorien... kombination farbe und verwendung
// wenn nicht gefunden dann suche nur verwendung
// wenn gefunden erstelle kategorie mit farbe und stream und returne id der kategorie farbe+verwendung
// wenn nicht gefunden returne null
$sql = "SELECT *
, LOWER(HEX(id)) as id
, LOWER(HEX(category_id)) as category_id
, LOWER(HEX(parent_id)) as parent_id
FROM category
INNER JOIN category_translation ON (category.id = category_translation.category_id)
WHERE LOWER(HEX(language_id)) = '".$this->language_id."'
AND JSON_EXTRACT(category_translation.custom_fields,'$.custom_color_id') = '".$farbe."'
AND JSON_EXTRACT(category_translation.custom_fields,'$.custom_usage_id') = '".$verwendung."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$productCategory = $stmt->fetchAll();
// $sql = "SELECT name
// FROM category_translation
// WHERE LOWER(HEX(category_id)) = '".$productCategory[0]["parent_id"]."'
// ;";
// $stmt = $conn_shopware->prepare($sql);
// $stmt->execute();
// $parentName = $stmt->fetchAll();
$sql = "SELECT *
, LOWER(HEX(id)) as id
, LOWER(HEX(category_id)) as category_id
, LOWER(HEX(parent_id)) as parent_id
, LOWER(HEX(product_stream_id)) as product_stream_id
FROM category
INNER JOIN category_translation ON (category.id = category_translation.category_id)
WHERE LOWER(HEX(language_id)) = '".$this->language_id."'
AND JSON_EXTRACT(category_translation.custom_fields,'$.custom_usage_id') = '".$verwendung."'
AND (JSON_EXTRACT(category_translation.custom_fields,'$.custom_color_id') = '' OR JSON_EXTRACT(category_translation.custom_fields,'$.custom_color_id') IS NULL)
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$productFinderCategory = $stmt->fetchAll();
// dd($verwendung,$productFinderCategory);
$sql = "SELECT name
FROM category_translation
WHERE LOWER(HEX(category_id)) = '".$productFinderCategory[0]["parent_id"]."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$parentName = $stmt->fetchAll();
$streamId = $this->checkProductStream($verwendung,$farbe, $parentName[0]["name"].' '.$productFinderCategory[0]["name"].' '.$farbname);
if(!is_array($productCategory) || empty($productCategory) || count($productCategory) < 1){ //prüfe auf usage id
// dd($productFinderCategory);
if(is_array($productFinderCategory) && !empty($productFinderCategory) && count($productFinderCategory) > 0){ //erstelle Kategorie
$body = json_encode(array(
"name" => $farbname,
"type" => "page",
"customFields" => array(
"custom_color_id" => $farbe,
"custom_usage_id" => $verwendung
),
"cmsPageId" => $this->material_category_layout_id,
"parentId" => $productFinderCategory[0]["id"],
"productStreamId" => $streamId,
"productAssignmentType" => "product_stream"
));
try{
$post_product_stream = $this->client->request('POST', '/api/category/', array(
'allow_redirects'=>array('strict'=>true),
"headers" => $this->header,
"body" => $body
));
$location = array_pop( $post_product_stream->getHeaders()['Location'] );
return 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;
}
// $CategoryFlat = new \App\Controller\shopware\CategoryFlat();
// $CategoryFlat->setType("page");
// $CategoryFlat->setName($farbname);
// $CategoryFlat->setCustomFields(array(
// "custom_color_id" => $farbe,
// "custom_usage_id" => $verwendung
// ));
// $CategoryFlat->setCmsPageId($this->material_category_layout_id);
// $CategoryFlat->setParentId($productFinderCategory[0]["id"]);
// $CategoryFlat->setProductStreamId($streamId);
// $CategoryFlat->setProductAssignmentType("product_stream");
// $post_category = $this->client->request('POST', '/api/category', array(
// // 'debug' => true,
// 'allow_redirects'=>array('strict'=>true),
// "headers" => $this->header,
// "body" => $CategoryFlat,
// ));
// $location = array_pop( $post_category->getHeaders()['Location'] );
return basename($location);
}else{ // return null
return null;
}
// dd($mat_id);
}else{ // Kategorie existiert, returne ID
if($productCategory[0]["product_stream_id"] != $streamId){
$body = json_encode(array(
"productStreamId" => $streamId,
"productAssignmentType" => "product_stream"
));
try{
$post_product_stream = $this->client->request('PATCH', '/api/category/'.$productCategory[0]["id"], array(
'allow_redirects'=>array('strict'=>true),
"headers" => $this->header,
"body" => $body
));
$location = array_pop( $post_product_stream->getHeaders()['Location'] );
return 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;
}
// $CategoryFlat = new \App\Controller\shopware\CategoryFlat();
// $CategoryFlat->setProductStreamId($streamId);
// $CategoryFlat->setProductAssignmentType("product_stream");
// $post_category = $this->client->request('PATCH', '/api/category/'.$productCategory[0]["id"], array(
// // 'debug' => true,
// 'allow_redirects'=>array('strict'=>true),
// "headers" => $this->header,
// "body" => $CategoryFlat,
// ));
}
return $productCategory[0]["id"];
}
// bei post product stream missachten, apipatch durchführen
return null;
}
public function checkProductStream($verwendung, $farbe, $streamname){
// Prüft of product stream bereits vorhanden ist, erstellt ihn falls dies nicht der Fall ist und returned die ID oder NULL, falls es ein problem gibt
$this->client_connect();
$conn_shopware = $this->shop_connect();
$sql = "SELECT *
, LOWER(HEX(product_stream_id)) as product_stream_id
FROM product_stream_translation
WHERE JSON_EXTRACT(product_stream_translation.custom_fields,'$.custom_color_id') = '".$farbe."'
AND JSON_EXTRACT(product_stream_translation.custom_fields,'$.custom_usage_id') = '".$verwendung."'
LIMIT 1
";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$product_stream = $stmt->fetchAll();
if(isset($product_stream) && !empty($product_stream) && count($product_stream) > 0){
return $product_stream[0]["product_stream_id"];
}else{
$body = json_encode(array(
"name" => $streamname,
"filters" => array(array(
"type" => "multi",
"queries" => array(array(
"type" => "multi",
"queries" => array(array(
"type" => "equals",
"field" => "product.properties.id",
"value" => $verwendung
),
array( "type" => "multi",
"queries" => array(array(
"type" => "equals",
"field" => "product.properties.id",
"value" => $farbe
)),
"operator" => "OR"
)
),
"operator" => "AND"
)),
"operator" => "OR"
)),
"customFields" => array(
"custom_color_id" => $farbe,
"custom_usage_id" => $verwendung
),
"apiAlias" => "product_stream"
));
try{
$post_product_stream = $this->client->request('POST', '/api/product-stream/', array(
'allow_redirects'=>array('strict'=>true),
"headers" => $this->header,
"body" => $body
));
$location = array_pop( $post_product_stream->getHeaders()['Location'] );
return 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;
}
}
return NULL;
}
public function createProductfinderList(){
// erstellt Liste zum generieren der Produktfinderkategorien
$conn_shopware = $this->shop_connect();
$conn_akn = $this->dump_connect();
$sql = "TRUNCATE TABLE produktfinder_alt";
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
$sql = "INSERT INTO produktfinder_alt SELECT * FROM produktfinder;";
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
$sql = "TRUNCATE TABLE produktfinder";
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
$sql = "SELECT *
, LOWER(HEX(category_id)) as category_id
, JSON_EXTRACT(category_translation.custom_fields,'$.custom_usage_id') as verwendung
FROM category_translation
WHERE JSON_EXTRACT(category_translation.custom_fields,'$.custom_usage_id') IS NOT NULL
AND JSON_EXTRACT(category_translation.custom_fields,'$.custom_usage_id') != ''
AND (JSON_EXTRACT(category_translation.custom_fields,'$.custom_color_id') IS NULL
OR JSON_EXTRACT(category_translation.custom_fields,'$.custom_color_id') = '' )
";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$finderCategories = $stmt->fetchAll();
$sql = "SELECT
*
, LOWER(HEX(id)) as id
, LOWER(HEX(language_id)) as language_id
, LOWER(HEX(property_group_option_id)) as property_group_option_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."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$property_colors = $stmt->fetchAll();
foreach($finderCategories as $finderCategory){
foreach($property_colors as $property_color){
$sql = "INSERT INTO produktfinder (category_id,color_id,Farbname)
VALUES ('".str_replace('"','',$finderCategory["verwendung"])."','".$property_color["property_group_option_id"]."','".$property_color["name"]."')
;";
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
}
}
}
/**
* Erstellt, updated und (de)aktiviert Kategorien im Produktfinder
*
* @Route("/update/productfinder", name="update_productfinder")
*
* @return mixed
*/
public function update_productfinder()
{
$this->client_connect();
$conn_shopware = $this->shop_connect();
$conn_akn = $this->dump_connect();
// gehe alle farben mit allen kategorien durch und erstelle/prüfe kategorien
// blende alle kategorien aus die keine zugeordneten produkte haben
// return $this->render('update/index.html.twig', ["return" => [ 'article' => $finderCategories, 'color' => $property_colors]]);
$sql = "SELECT COUNT(id) as count
FROM produktfinder
;";
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
$productfinderCount = $stmt->fetchAll();
if($productfinderCount[0]["count"] == 0){
$this->createProductfinderList();
}
$sql = "SELECT *
FROM produktfinder
WHERE done IS NULL
;";
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
$productfinder = $stmt->fetchAll();
foreach($productfinder as $productfinder_row){
// bearbeite und setze done auf 1
$categoryId = $this->getProductFinderCategory($productfinder_row["category_id"], $productfinder_row["color_id"],$productfinder_row["Farbname"]);
$sql = "SELECT *
, LOWER(HEX(id)) as id
, LOWER(HEX(product_stream_id)) as product_stream_id
FROM category
WHERE LOWER(HEX(id)) = '".$categoryId."'
LIMIT 1
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$category = $stmt->fetchAll();
$streamId = $category[0]["product_stream_id"];
$sql = " SELECT COUNT(*) AS count
FROM product_stream_mapping
WHERE LOWER(HEX(product_stream_id)) = '".$streamId."'
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$stream_mapping = $stmt->fetchAll();
$CategoryFlat = new \App\Controller\shopware\CategoryFlat();
if(!isset($stream_mapping[0]["count"]) || empty($stream_mapping[0]["count"]) || $stream_mapping[0]["count"] < 1){
// deaktiviere Kategorie wenn Produktgruppe leer ist
$CategoryFlat->setActive(false);
}else{
$CategoryFlat->setActive(true);
}
try {
$post_category = $this->client->request('PATCH', '/api/category/'.$categoryId.'', array(
'allow_redirects'=>array('strict'=>true),
"headers" => $this->header,
"body" => $CategoryFlat
));
} catch (RequestException $e) {
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;
}
$sqlt = "UPDATE produktfinder SET
done = 1
WHERE id = ".$productfinder_row["id"].";";
$stmtt = $conn_akn->prepare($sqlt);
$stmtt->execute();
}
return $this->render('update/index.html.twig', ["return" => [ 'success' => true]]);
}
/**
* test
*
* @Route("/test", name="test")
*
* @return mixed
*/
public function test()
{
//komprimierungstest
// var_dump("test");exit;
$this->client_connect();
$conn_shopware = $this->shop_connect();
$conn_akn = $this->dump_connect();
// $conn_konfigurator = $this->konfig_connect();
$sql = "SELECT material,material_group
FROM artikeldaten
WHERE
(material,material_group,innen_kueche,innen_fenster,innen_bad,innen_fussboden,innen_boden,aussen_boden,aussen_fassade,aussen_fenster,aussen_stufen,aussen_salz,bestellbar
)
NOT IN (SELECT
material,material_group,innen_kueche,innen_fenster,innen_bad,innen_fussboden,innen_boden,aussen_boden,aussen_fassade,aussen_fenster,aussen_stufen,aussen_salz,bestellbar
FROM artikeldaten_alt)
;";
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
$changed_rows = $stmt->fetchAll();
return $this->render('update/index.html.twig', ["return" => ['test' => $changed_rows ]]);
foreach($changed_rows as $row){
// set priority to changed rows
$sql = "UPDATE storages
SET upload_done = 4
WHERE material_id = '".$row["material"]."'
AND materialgroup_id = '".$row["material_group"]."'
;";
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
}
// $body = json_encode(array(
// "name" => "test"
// // "sortBy" => "name",
// // "sortDirection" => "ASC",
// // "limit" => 24,
// // "active" => true
// // "type" => "productStream",
// ));
// $body = json_encode(array(
// "name" => "test",
// "filters" => array(
// array(
// "type" => "multi",
// "queries" => array(
// array(
// "type" => "multi",
// "queries" => array(
// array(
// "type" => "equals",
// "field" => "product.properties.id",
// "value" => "f00c5dcbc7e444f38e06ea9f14aaa11a" // INNEN/AUßEN XX:Ja
// ),
// array(
// "type" => "multi",
// "queries" => array(
// array(
// "type" => "equals",
// "field" => "product.properties.id",
// "value" => "8ce1035e43b84d8395788e9f8b22c2fa" //FARBE
// )
// ),
// "operator" => "OR"
// )
// ),
// "operator" => "AND"
// )
// ),
// "operator" => "OR"
// )
// ),
// "apiAlias" => "product_stream"
// ));
// try{
// $post_country = $this->client->request('POST', '/api/product-stream/', array(
// 'allow_redirects'=>array('strict'=>true),
// "headers" => $this->header,
// "body" => $body
// ));
// } 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;
// }
// try {
// $search = $this->client->request('GET', '/api/product-stream', array(
// "headers" => $this->header,
// ));
// $categories = json_decode($search->getBody()->getContents(),true);
// } 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;
// }
// try {
// $search = $this->client->request('GET', '/api/category?filter[name]=testkat', 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);
$verwendung = "f00c5dcbc7e444f38e06ea9f14aaa11a";
$farbe = "8ce1035e43b84d8395788e9f8b22c2fa";
$categories = $this->getProductFinderCategory($verwendung, $farbe, "TESTGRAU");
return $this->render('update/index.html.twig', ["return" => [ 'article' => $categories]]);
//____________________________________________________________________________
// $sql = "SELECT
// LOWER(HEX(id)) as id
// ,product_number
// ,stock
// FROM product
// ORDER BY product_number asc
// ;";
// $stmt = $conn_shopware->prepare($sql);
// $stmt->execute();
// $search_product = $stmt->fetchAll();
// $sql = "SELECT
// id
// ,stone_number
// ,amount_1
// FROM storages
// WHERE stone_number IN ('".implode("', '",array_column($search_product, 'product_number'))."')
// ORDER BY stone_number asc
// ;";
// $stmt = $conn_akn->prepare($sql);
// $stmt->execute();
// $articles = $stmt->fetchAll();
return $this->render('update/index.html.twig', ["return" => [ 'article' => $this->header]]);
try {
$search = $this->client->request('GET', '/api/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;
}
return $this->render('update/index.html.twig', ["return" => [ 'article' => $search]]);
return $this->render('update/index.html.twig', ["return" => ['test' => 'test' ]]);
$sql = "SELECT *
, articles.material_id as material_id
, articles.materialgroup_id as materialgroup_id
, storages.id as storage_id
, storages.stone_number AS product_number
, CONCAT(storages.material_id, '_', storages.materialgroup_id, '_') AS product_number_parent
, storages.picture AS storages_picture
, materials.picture AS materials_picture
, COALESCE(NULLIF(materials.picture, ''), 'default_art.jpg') AS picture_parent -- select picture
, key2.key_text AS einheit_2
FROM articles
LEFT JOIN materials ON (articles.material_id = materials.material_id AND articles.materialgroup_id = materials.materialgroup_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)
LEFT JOIN materialcolors ON (articles.material_id = materialcolors.mar_id AND articles.materialgroup_id = materialcolors.mgr_id )
LEFT JOIN `keys` key1 ON (materialcolors.color_id = key1.key_number AND key1.key_kind = 88)
LEFT JOIN `keys` key2 ON (materialcolors.color_id = key2.key_number AND key2.key_kind = 1)
WHERE storages.stone_number IS NOT NULL
AND storages.stone_number !=''
AND storages.storage != 'ZULAUF'
-- AND storages.upload_done IS NULL
AND storages.upload_failed IS NULL
AND material_name IS NOT NULL
-- AND block_advice IS NOT NULL
-- AND block_advice != ''
-- AND articles.material_id = 'NEIM'
AND articles.material_id = 'CAMI'
ORDER BY articles.id ASC
;";
$stmt = $conn_akn->prepare($sql);
$stmt->execute();
$articles = $stmt->fetchAll();
$vergleichsarray=array();
$testarray=array();
$parentarray=array();
foreach($articles as $key => $article){
$blocknummer=null;
$blocknummer_zerlegt=array();
preg_match('/([a-zA-Z]+)([0-9,]+)([a-zA-Z0-9,]+)/', $article["block_advice"], $blocknummer_zerlegt);
// var_dump($blocknummer_zerlegt);
if(strlen(end($blocknummer_zerlegt))>1){
$blocknummer = end($blocknummer_zerlegt);
}
if($blocknummer == null){
$article["product_number_parent"].=$article["article_id"];
$articles[$key]["product_number_parent"].=$article["article_id"];
}else{
$article["product_number_parent"].=$blocknummer;
$articles[$key]["product_number_parent"].=$blocknummer;
}
$sql = "SELECT
LOWER(HEX(id)) as id
,product_number
FROM product
JOIN product_category ON (product_id = id)
WHERE product_number = '".$article["product_number_parent"]."'
LIMIT 1
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$search_product = $stmt->fetchAll();
array_push($vergleichsarray,$article["product_number_parent"]);
if(count($search_product)>0 && !empty($search_product)){
// dd($search_product);
array_push($testarray,$search_product[0]["product_number"]);
$parentarray[$search_product[0]["product_number"]]=$search_product[0]["id"];
}
}
$zuErstellendeParents = array_diff($vergleichsarray, $testarray);
if(empty($zuErstellendeParents)){
//alle varianten können importiert werden
$importjson=array();
foreach($articles as $article){
// dd($article["product_number_parent"]);
unset($custom_fields);
if( $article["fault_id"] != 0 && !empty($article["fault_id"]) && $article["fault_corner"] != 0 && !empty($article["fault_corner"])){
//prüfe ob beides gesetzt, wenn ja definier beides, wenn nicht beides leer
$custom_fields["custom_article_fault_id"] = $article["fault_name"];
$custom_fields["custom_article_fault_corner"] = $article["fault_corner"];
$custom_fields["custom_article_fault_exists"] = 1;
}else{
$custom_fields["custom_article_fault_exists"] = 0;
}
$custom_fields=array();
$custom_fields["custom_article_id"] = $article["article_id"];
$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_surface"] = $article["surface_name"];
$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_einheit_2"] = $article["einheit_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_name_1"] = $article["article_name_1"];
$custom_fields["custom_article_name_2"] = $article["article_name_2"];
$custom_fields["custom_article_additional_advice"] = $article["additional_advice"];
if(isset($article["storages_picture"]) && !empty($article["storages_picture"]) && $article["storages_picture"] != null){
$custom_fields["custom_article_picture"] = $article["storages_picture"];
}elseif(isset($storags["materials_picture"]) && !empty($article["materials_picture"]) && $article["materials_picture"] != null){
$custom_fields["custom_article_picture"] = $article["materials_picture"];
}else{
$custom_fields["custom_article_picture"] = $article["picture_parent"];
}
if(isset($article["color_text"]) && !empty($article["color_text"])){
$color = explode('#',$article["color_text"]);
$article["color_text"] = trim($color[0]);
$custom_fields["custom_article_material_color"] = $article["color_text"];
}
$storage_option_list=array();
$storage_option_list = $this->get_variant_ids($article);
$sol_option=array();
foreach($storage_option_list as $key => $sol){
$sol_option[$key]["id"]=$sol["optionId"];
}
// dd($sol_option);
$variant =[
// "id"=> "393eb570026d4076a1ceaf6fe48bdd05", //patch
"name" => $article["material_name"],
"parentId" => $parentarray[$article["product_number_parent"]],
"productNumber"=> $article["stone_number"],
"stock"=> intval($article["amount_1"]),
"isCloseout"=> true,
"height" => $article["thickness"],
"length" => $article["length"],
"width"=> $article["width"],
"price"=> array(array(
"currencyId" => $this->currency_id,
"net" => round($article["price"],2),
"gross" => round($article["price"],2),
"linked" => false
)),
// TODO OPTIONS
// PROPERTIES
"options" => $sol_option,
"properties" => $sol_option,
// "options" => array(array(
// "id" => "dee3b8bac6fe48a094f7abf465a6c981",
// "group" => array(
// "id" => "8f9f42eddaa843bba8baa0ec33b2d6ed"
// )
// )),
"customFields" => $custom_fields
// array(
// "custom_article_surface" => "test",
// "custom_article_name_1" => "name",
// "custom_article_material_name" => "mati",
// "custom_article_picture" => "default_art.jpg"
// ),
// ende patch - nachfolgende nur bei parents gesetzt
// "categories"=> array(
// "data"=>array(
// "type" => "category",
// "id" => "c7446ea908604e06a8f28a6c42fd1171",
// )
// ),
// "taxId"=> "c5ddfa75e22b4889b6f628e1df0b347f",
// VISIBILITY WIRFT FEHLER WENN BEREITS GESETZT BEIM PATCH -> NUR SETZEN WENN ID LEER IST (PRODUKT NOCH NICHT EXISTIERT)
// "visibilities" => array(
// array(
// "productId" => "393eb570026d4076a1ceaf6fe48bdd05",
// "salesChannelId" => "f8c383f75225488a8f6b89b7d64d6cf2",
// "visibility" => 30
// )
// ),
// ,"children" => array($variante)
];
$sql = "SELECT
LOWER(HEX(id)) as id
,product_number
FROM product
WHERE product_number = '".$article["stone_number"]."'
LIMIT 1
;";
$stmt = $conn_shopware->prepare($sql);
$stmt->execute();
$search_variant = $stmt->fetchAll();
// var_dump($article["stone_number"]);
// var_dump($search_variant);
if(isset($search_variant[0]["id"]) && !empty($search_variant[0]["id"])){
$variant["id"]=$search_variant[0]["id"];
}
array_push($importjson,$variant);
}
try {
// dd($importjson);
$search = $this->client->request('POST', '/api/_action/sync', array(
// $search = $this->client->request('GET', '/api/media/6d98e8d9d2184172a5acc4a32095e843', array(
"debug" => true,
"headers" => $this->header,
"body" => json_encode([
array(
"action"=> "upsert",
"entity"=> "product",
// im payload mit kommas getrennt datensätze von parents mit integrierten childs angeben
"payload"=>
$importjson
// array(
// $parent
// // z.b. ,$parent2
// )
)
])
));
} 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);
return $this->render('update/index.html.twig', ["return" => ['suche' => $search_picture ]]);
}
// return $this->render('update/index.html.twig', ["return" => ['vergleich' => $vergleichsarray, 'test' => $testarray ]]);
return $this->render('update/index.html.twig', ["return" => ['fehlparents' => $zuErstellendeParents, 'parentarray' => $parentarray ]]);
// STAPELVERARBEITUNG HIER!!!!!!!
// $custom_fields["custom_article_surface"] = "test";
// $custom_fields["custom_article_name_1"] = "name";
// $custom_fields["custom_article_material_id"] = "mat";
// $custom_fields["custom_article_picture"] = "default_art.jpg";
// $variante =[
// "id"=> "192754b5c0864581b64c92a0076cdce1",
// "taxId"=> "c5ddfa75e22b4889b6f628e1df0b347f",
// "price"=> array(array(
// "currencyId" => "b7d2554b0ce847cd82f3ac9bd1c0dfca",
// "net" => 0,
// "gross" => 0,
// "linked" => false
// )),
// "productNumber"=> "1234566789",
// "stock"=> 0,
// "name" => "Test2"
// ] ;
$parent =[
"id"=> "393eb570026d4076a1ceaf6fe48bdd05", //patch
"name" => "Test",
"parentId" => "3ab3a8fd7b0046679a71d378ef334ba8",
"productNumber"=> "1234567890",
"stock"=> 0,
"isCloseout"=> true,
"height" => 9,
"length" => 10,
"width"=> 8,
"price"=> array(array(
"currencyId" => "b7d2554b0ce847cd82f3ac9bd1c0dfca",
"net" => 0,
"gross" => 0,
"linked" => false
)),
// OPTIONS
// PROPERTIES
"customFields" =>array(
"custom_article_surface" => "test",
"custom_article_name_1" => "name",
"custom_article_material_name" => "mati",
"custom_article_picture" => "default_art.jpg"
),
// ende patch - nachfolgende nur bei parents gesetzt
"categories"=> array(
"data"=>array(
"type" => "category",
"id" => "c7446ea908604e06a8f28a6c42fd1171",
)
),
"taxId"=> "c5ddfa75e22b4889b6f628e1df0b347f",
// VISIBILITY WIRFT FEHLER WENN BEREITS GESETZT BEIM PATCH -> NUR SETZEN WENN ID LEER IST (PRODUKT NOCH NICHT EXISTIERT)
// "visibilities" => array(
// array(
// "productId" => "393eb570026d4076a1ceaf6fe48bdd05",
// "salesChannelId" => "f8c383f75225488a8f6b89b7d64d6cf2",
// "visibility" => 30
// )
// ),
// ,"children" => array($variante)
];
// return $this->render('update/index.html.twig', ["return" => ['suchee' => array($parent,$variante), 'product' => $test]]);
// return $this->render('update/index.html.twig', ["return" => ['suchee' => array($parent,$variante), 'product' => $ProductFlat]]);
try {
$search = $this->client->request('POST', '/api/_action/sync', array(
// $search = $this->client->request('GET', '/api/media/6d98e8d9d2184172a5acc4a32095e843', array(
"debug" => true,
"headers" => $this->header,
"body" => json_encode([
array(
"action"=> "upsert",
"entity"=> "product",
// im payload mit kommas getrennt datensätze von parents mit integrierten childs angeben
"payload"=>
array(
$parent
// z.b. ,$parent2
)
)
])
));
} 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);
return $this->render('update/index.html.twig', ["return" => ['suche' => $search_picture ]]);
return $this->render('update/index.html.twig', ["return" => ['test' => "ende" ]]);
}
}

src/Controller/ImportController.php line 3369