src/Controller/DefaultController.php line 14

Open in your IDE?
  1. <?php
  3. namespace App\Controller;
  5. use Pimcore\Controller\FrontendController;
  6. use Symfony\Component\HttpFoundation\Request;
  7. use Sensio\Bundle\FrameworkExtraBundle\Configuration\Template;
  8. use Symfony\Component\Routing\Annotation\Route;
  10. use \Pimcore\Model\DataObject;
  12. use \Pimcore\Model\Asset;
  14. class DefaultController extends FrontendController {
  15.     /**
  16.      * @Template
  17.      * @param Request $request
  18.      * @return array
  19.      */
  20.     public function defaultAction(Request $request) {
  21.         return [];
  22.     }
  23.     
  24.     /**
  25.      * @Template
  26.      * @param Request $request
  27.      * @return array
  28.      */
  29.     public function addPriceDevAction(Request $request) {
  30.         $data = [            "37931" => "281.25",
  31.            "39017" => "195.65",
  32.            "39018" => "953.16",
  33.            "39428" => "1454.74",
  34.            "39958" => "237.00",
  35.            "40103" => "448.50",
  36.            "40275" => "360.16",
  37.            "40722" => "872.50",
  38.            "41243" => "29.65",
  39.            "41245" => "23.12",
  40.            "41578" => "682.50",
  41.            "42126" => "798.50",
  42.            "42369" => "598.00",
  43.            "42957" => "1220.00",
  44.            "43600" => "529.00",
  45.            "43672" => "329.00",
  46.            "43698" => "2188.44",
  47.            "43761" => "2200.00",
  48.            "43832" => "89.00",
  49.            "43836" => "12.50",
  50.            "43837" => "12.50",
  51.            "43838" => "12.50",
  52.            "43843" => "38.00",
  53.            "43845" => "12.50",
  54.            "43846" => "12.50",
  55.            "43848" => "25.50",
  56.            "43857" => "25.50",
  57.            "43864" => "38.00",
  58.            "43867" => "38.00",
  59.            "43868" => "38.00",
  60.            "43884" => "63.50",
  61.            "43886" => "63.50",
  62.            "43892" => "38.00",
  63.            "43896" => "893.00",
  64.            "43900" => "1590.00",
  65.            "43902" => "226.50",
  66.            "43909" => "447.00",
  67.            "44190" => "127.00",
  68.            "44194" => "89.50",
  69.            "44196" => "38.00",
  70.            "44197" => "38.00",
  71.            "44198" => "38.00",
  72.            "44199" => "38.00",
  73.            "44207" => "12.50",
  74.            "44209" => "12.50",
  75.            "44213" => "115.00",
  76.            "44217" => "138.00",
  77.            "44221" => "1589.50",
  78.            "44225" => "395.50",
  79.            "44228" => "447.00",
  80.            "44235" => "107.50",
  81.            "44615" => "127.00",
  82.            "44619" => "892.50",
  83.            "45005" => "625.14",
  84.            "45007" => "97.23",
  85.            "45186" => "627.90",
  86.            "45960" => "198.50",
  87.            "46490" => "457.76",
  88.            "46492" => "918.02",
  89.            "46497" => "984.05",
  90.            "46511" => "625.35",
  91.            "46514" => "625.35",
  92.            "46519" => "457.76",
  93.            "46522" => "38.02",
  94.            "46523" => "38.02",
  95.            "46662" => "226.50",
  96.            "46667" => "89.00",
  97.            "46688" => "202.00",
  98.            "46690" => "202.00",
  99.            "46819" => "1320.00",
  100.            "47007" => "702.50",
  101.            "47217" => "107.50",
  102.            "47221" => "227.00",
  103.            "47288" => "238.80",
  104.            "47291" => "802.20",
  105.            "47294" => "383.25",
  106.            "47332" => "958.00",
  107.            "47574" => "380.00",
  108.            "47603" => "400.50",
  109.            "47784" => "260.50",
  110.            "48302" => "700.83",
  111.            "48350" => "945.00",
  112.            "48351" => "242.50",
  113.            "48353" => "89.00",
  114.            "48471" => "526.50",
  115.            "48492" => "457.00",
  116.            "48494" => "121.00",
  117.            "48693" => "1092.00",
  118.            "48774" => "127.00",
  119.            "48775" => "108.50",
  120.            "48777" => "108.50",
  121.            "48781" => "38.00",
  122.            "48784" => "188.50",
  123.            "48791" => "458.00",
  124.            "48794" => "574.50",
  125.            "48797" => "108.50",
  126.            "48800" => "25.50",
  127.            "48801" => "38.00",
  128.            "48803" => "188.50",
  129.            "48804" => "188.50",
  130.            "48808" => "213.00",
  131.            "48815" => "127.00",
  132.            "48817" => "364.00",
  133.            "48818" => "25.50",
  134.            "48819" => "38.00",
  135.            "48821" => "188.50",
  136.            "48824" => "188.50",
  137.            "48825" => "338.50",
  138.            "48826" => "1624.50",
  139.            "48828" => "213.00",
  140.            "48843" => "458.00",
  141.            "48892" => "447.30",
  142.            "48893" => "156.45",
  143.            "48894" => "166.95",
  144.            "48895" => "250.95",
  145.            "48972" => "574.50",
  146.            "48973" => "574.50",
  147.            "49355" => "525.00",
  148.            "49358" => "1300.00",
  149.            "49409" => "473.50",
  150.            "49424" => "3907.50",
  151.            "49458" => "1875.00",
  152.            "49495" => "637.50",
  153.            "49501" => "457.00",
  154.            "49503" => "234.00",
  155.            "49504" => "296.00",
  156.            "49510" => "38.00",
  157.            "49563" => "6857.50",
  158.            "50001" => "9580.00",
  159.            "50168" => "395.00",
  160.            "50208" => "703.25",
  161.            "50237" => "435.00",
  162.            "50246" => "430.00",
  163.            "50307" => "737.50",
  164.            "50353" => "4030.00",
  165.            "50383" => "34.50",
  166.            "50386" => "172.00",
  167.            "50554" => "439.38",
  168.            "50627" => "4840.50",
  169.            "50666" => "2847.50",
  170.            "50787" => "775.00",
  171.            "50792" => "247.50",
  172.            "50793" => "247.50",
  173.            "50842" => "3075.00",
  174.            "50883" => "586.85",
  175.            "50896" => "586.85",
  176.            "51012" => "54.50",
  177.            "51015" => "54.50",
  178.            "51016" => "79.50",
  179.            "51024" => "385.00",
  180.            "51025" => "385.00",
  181.            "51084" => "1358.50",
  182.            "51085" => "837.00",
  183.            "51086" => "416.50",
  184.            "51088" => "416.50",
  185.            "51090" => "554.50",
  186.            "51092" => "554.50",
  187.            "51096" => "570.50",
  188.            "51100" => "104.00",
  189.            "51102" => "1213.00",
  190.            "51310" => "622.50",
  191.            "51399" => "664.45",
  192.            "51413" => "535.50",
  193.            "51424" => "2360.00",
  194.            "51441" => "525.00",
  195.            "51475" => "7875.00",
  196.            "51510" => "481.50",
  197.            "51675" => "1318.00",
  198.            "51696" => "1308.00",
  199.            "51740" => "1462.50",
  200.            "51741" => "1830.00",
  201.            "51744" => "1809.00",
  202.            "51751" => "2531.67",
  203.            "51800" => "562.50",
  204.            "51821" => "297.50",
  205.            "51833" => "1292.00",
  206.            "51896" => "256.91",
  207.            "51944" => "586.85",
  208.            "51946" => "520.00",
  209.            "51998" => "415.63",
  210.            "52020" => "5063.33",
  211.            "52068" => "1912.00",
  212.            "52098" => "377.50",
  213.            "52126" => "380.00",
  214.            "52323" => "526.00",
  215.            "52346" => "475.00",
  216.            "52347" => "307.50",
  217.            "52348" => "340.00",
  218.            "52506" => "363.00",
  219.            "52509" => "529.00",
  220.            "52533" => "1188.50",
  221.            "52620" => "1163.00",
  222.            "52648" => "745.00",
  223.            "52863" => "8612.50",
  224.            "52923" => "1210.50",
  225.            "52959" => "142.00",
  226.            "52960" => "257.00",
  227.            "52961" => "87.00",
  228.            "52981" => "240.00",
  229.            "52982" => "173.00",
  230.            "53015" => "848.75",
  231.            "53033" => "3300.00",
  232.            "53173" => "1674.00",
  233.            "53182" => "8325.00",
  234.            "53197" => "962.50",
  235.            "53232" => "1343.50",
  236.            "53255" => "206.00",
  237.            "53257" => "206.00",
  238.            "53258" => "227.00",
  239.            "53273" => "465.00",
  240.            "53319" => "3733.34",
  241.            "53337" => "1562.50",
  242.            "53371" => "552.50",
  243.            "53374" => "552.50",
  244.            "53382" => "3417.00",
  245.            "53383" => "1455.00",
  246.            "53384" => "700.00",
  247.            "53386" => "950.00",
  248.            "53435" => "2412.80",
  249.            "53449" => "1285.00",
  250.            "53526" => "425.00",
  251.            "53558" => "2429.00",
  252.            "53666" => "720.00",
  253.            "53721" => "802.50",
  254.            "53752" => "2647.50",
  255.            "53758" => "552.50",
  256.            "53766" => "1092.50",
  257.            "53828" => "508.00",
  258.            "53838" => "2626.67",
  259.            "53875" => "190.00",
  260.            "53877" => "211.00",
  261.            "53894" => "463.13",
  262.            "53945" => "587.00",
  263.            "54008" => "1679.50",
  264.            "54038" => "1163.00",
  265.            "54091" => "3745.83",
  266.            "54098" => "1163.00",
  267.            "54133" => "657.50",
  268.            "54383" => "1205.00",
  269.            "54404" => "2054.00",
  270.            "54413" => "859.25",
  271.            "54708" => "814.25",
  272.            "55210" => "2028.00",
  273.            "55441" => "1976.00",
  274.            "55561" => "1810.50",
  275.            "55562" => "212.00",
  276.            "55566" => "526.00",
  277.            "55567" => "204.00",
  278.            "55568" => "204.00",
  279.            "55571" => "85.00",
  280.            "55575" => "110.00",
  281.            "55576" => "435.00",
  282.            "55578" => "211.00",
  283.            "55579" => "266.00",
  284.            "55580" => "142.00",
  285.            "55581" => "135.00",
  286.            "55582" => "390.00",
  287.            "55583" => "57.50",
  288.            "55584" => "23.00",
  289.            "55585" => "34.50",
  290.            "55586" => "11.50",
  291.            "55587" => "1169.00",
  292.            "55589" => "547.50",
  293.            "55590" => "227.00",
  294.            "55591" => "227.00",
  295.            "55592" => "172.00",
  296.            "55593" => "172.00",
  297.            "55594" => "859.50",
  298.            "55595" => "1190.50",
  299.            "55966" => "6897.50",
  300.            "56807" => "5554.17",
  301.            "57032" => "2028.00",
  302.            "57174" => "1346.80",
  303.            "57299" => "2028.00",
  304.            "57762" => "216.50",
  305.            "58492" => "2951.00",
  306.            "58601" => "9403.33",
  307.            "58661" => "1924.00",
  308.            "59375" => "1924.00",
  309.            "59490" => "2041.00",
  310.            "59715" => "6665.00"
  311.            
  312.            ];
  313.         
  314.         
  315.         $servername = "carat-dev.cp4unapazytb.eu-central-1.rds.amazonaws.com";
  316.         $username = "atimis";
  317.         $password = "gui8kknyzG";
  318.         $dbname = "carat_100";
  319.         
  320.         // Create connection
  321.         $conn = new \mysqli($servername, $username, $password, $dbname);
  322.         // Check connection
  323.         if ($conn->connect_error) {
  324.           die("Connection failed: " . $conn->connect_error);
  325.         }
  326.         
  327.         $conn->set_charset("utf8mb4");
  328.         
  329.         foreach($data as $storageNumber => $price) {
  330.             echo "Number: " . $storageNumber . " Price: " . $price . "<br>";
  331.             
  332.             $sql = "INSERT INTO `PriceDevaluation` (`id`, `created`, `deleted`, `modified`, `note`, `price`, `creator_id`, `modificator_id`, `item_id`, `targetDate`)
  333.             VALUES (NULL, NULL, 0, NULL, 'Teilwert3', $price, NULL, NULL, (SELECT `id` FROM `Item` WHERE `stockNumber` = $storageNumber), '2023-06-30 00:00:00')";
  334.             
  335.             $result = $conn->query($sql);
  336.             
  337.             var_dump($result);
  338.         }
  339.         
  340.         $conn->close();
  341.         
  342.         
  343.         exit;
  344.         
  345.         return [];
  346.     }
  347.     
  348.     /**
  349.      * @Template
  350.      * @param Request $request
  351.      * @return array
  352.      */
  353.     public function videopageAction(Request $request) {
  354.         return [];
  355.     }
  356.     
  357.     /**
  358.      * @Template
  359.      * @param Request $request
  360.      * @return array
  361.      */
  362.     public function serviceAction(Request $request) {
  363.         return [];
  364.     }
  365.     
  366.     /**
  367.      * @Template
  368.      * @param Request $request
  369.      * @return array
  370.      */
  371.     public function unternehmenAction(Request $request) {
  372.         return [];
  373.     }
  374.     
  375.     /**
  376.      * @Template
  377.      * @param Request $request
  378.      * @return array
  379.      */
  380.     public function historyAction(Request $request) {
  381.         return [];
  382.     }
  383.     
  384.     /**
  385.      * @Template
  386.      * @param Request $request
  387.      * @return array
  388.      */
  389.     public function emailAction(Request $request) {
  390.         return [];
  391.     }
  392.     
  393.     /**
  394.      * @Template
  395.      * @param Request $request
  396.      * @return array
  397.      */
  398.     public function jaegerAction(Request $request) {
  399.         return [];
  400.     }
  401.     
  402.     /**
  403.      * @Template
  404.      * @param Request $request
  405.      * @return array
  406.      */
  407.     public function aktuellesAction(Request $request) {
  408.         return [];
  409.     }
  410.     
  411.     /**
  412.      * @Template
  413.      * @param Request $request
  414.      * @return array
  415.      */
  416.     public function profileAction(Request $request) {
  417.         return [];
  418.     }
  419.     
  420.     /**
  421.      * @Template
  422.      * @param Request $request
  423.      * @return array
  424.      */
  425.     public function testassetAction(Request $request) {
  426.         
  427.         // $asset = Asset::getById(247380);
  428.         // $versions = $asset->getVersions();
  429.         // 
  430.         // $previousVersion = $versions[count($versions)-3];
  431.         // 
  432.         // $oldData = $previousVersion->getData();
  433.         // 
  434.         // var_dump($previousVersion);
  435.         // exit;
  436.         // $asset->setData($oldData);
  437.         // $asset->save();
  438.         // exit;
  439.         
  440.         
  441.         $assets = new \Pimcore\Model\Asset\Listing();
  442.         $assets->setCondition("path LIKE '%/PIM/02 Schmuck/02 Produkte/%' and type = 'image' and id != 273747 and id != 280811 and id != 273482");
  443.         // $assets->setLimit(500);
  444.         // $assets->setOffset(4499);
  445.         $assets->setOrderKey("filename");
  446.         $assets->setOrder("ASC");
  447.         $assets->load();
  448.         
  449.         foreach($assets as $asset) {
  450.             $filename = $asset->getFilename();
  451.             // var_dump($filename);
  452.             //$data = file_get_contents("https://huebner.carat.solutions/carat/filedownload?fileName=" . $filename);
  453.             //var_dump($data);
  454.             
  455.             $asset->setData(file_get_contents("https://huebner.carat.solutions/carat/filedownload?fileName=" . $filename));
  456.             $asset->save();
  457.         }
  458.         
  459.          echo "done";
  460.         // $newAsset = new \Pimcore\Model\Asset\Image();
  461.         // $newAsset->setFilename($filename);
  462.         // $newAsset->setData(file_get_contents("https://huebner.carat.solutions/carat/filedownload?fileName=" . $filename));
  463.         // $newAsset->setParent(\Pimcore\Model\Asset::getByPath("/Test"));
  464.         // $newAsset->save();
  465.         
  466.         exit;
  467.         return [];
  468.     }
  469.     
  470.     /**
  471.      * @Template
  472.      * @param Request $request
  473.      * @return array
  474.      */
  475.     public function inquiryAction(Request $request) {
  476.         $productid = str_replace("/", "", $request->get("productid"));
  477.         
  478.         $product = DataObject::getById($productid);
  479.         
  480.         return $this->render('/default/inquiry.html.twig', ["product" => $product]);
  481.     }
  482.     
  483.     /**
  484.      * @Template
  485.      * @param Request $request
  486.      * @return array
  487.      */
  488.     public function sendInquiryAction(Request $request) {
  489.         if ($request->getMethod() === "POST") {
  491.                 
  492.                 
  493.             $params = $request->request;
  494.             
  495.             $productId = $params->get("productId");
  496.             $product = DataObject::getById($productId);
  497.             $reference = $product->getarticleNumber();
  498.             
  499.             $newsletter = $params->get("newsletter");
  500.             $iwantto = $params->get("iwantto");
  501.             $contactoption = $params->get("contactoption");
  502.             $branch = $params->get("branch");
  503.             $comment = $params->get("comment");
  504.             $salutation = $params->get("salutation");
  505.             $title = $params->get("title");
  506.             $firstname = $params->get("firstname");
  507.             $lastname = $params->get("lastname");
  508.             $email = $params->get("email");
  509.             $phone = $params->get("phone");
  510.             $address = $params->get("address");
  511.             $zipcode = $params->get("zipcode");
  512.             $state = $params->get("state");
  513.             $country = $params->get("country");
  514.             
  515.             //set branch email
  516.             switch($branch) {
  517.                 case "Wien":
  518.                     $branchemail = "wien@uhrenhuebner.at";
  519.                     break;
  520.                 case "Linz":
  521.                     $branchemail = "linz@uhrenhuebner.at";
  522.                     break;
  523.                 case "Wels":
  524.                     $branchemail = "wels@uhrenhuebner.at";
  525.                     break;
  526.             }
  527.             
  528.             $inquiry = new DataObject\Inquiry();
  529.             $inquiry->setKey(\Pimcore\Model\Element\Service::getValidKey('Anfrage-' . $email . date('d-m-y h:i:s'), 'object'));
  530.             $inquiry->setParentId(1833);
  531.             $inquiry->setiwantto($iwantto);
  532.             $inquiry->setcontactoption($contactoption);
  533.             $inquiry->setbranch($branch);
  534.             $inquiry->setcomment($comment);
  535.             $inquiry->setnewsletter($newsletter);
  536.             $inquiry->setsalutation($salutation);
  537.             $inquiry->settitle($title);
  538.             $inquiry->setfirstname($firstname);
  539.             $inquiry->setlastname($lastname);
  540.             $inquiry->setemail($email);
  541.             $inquiry->setphone($phone);
  542.             $inquiry->setaddress($address);
  543.             $inquiry->setzipcode($zipcode);
  544.             $inquiry->setstate($state);
  545.             $inquiry->setcountry($country);
  546.             $inquiry->setProduct($product);
  547.             $inquiry->setPublished(true);
  548.             $inquiry->save();
  549.             
  550.             $salutationtext = $salutation == "Herr" ? "Sehr geehrter Herr" : "Sehr geehrte Frau";
  551.             $productHtml = $this->getProductHtml($product);
  552.             
  553.             $productHtml = " " . $product->getBrand()->getName() . " - " . $product->getModel() . " ";
  554.             
  555.             //send User Mail
  556.             $mail = new \Pimcore\Mail();
  557.             $mail->addTo($email);
  558.             //$mail->addTo("a.daum@ontime.at");
  559.             $mail->setDocument("/Emails/inquiry-user");
  560.             $mail->setParams([
  561.                 'productHtml' => $productHtml,
  562.                 'salutation' => $salutationtext,
  563.                 'iwantto' => $iwantto,
  564.                 'contactoption' => $contactoption,
  565.                 'branch' => $branch,
  566.                 'comment' => $comment,
  567.                 'title' => $title,
  568.                 'firstname' => $firstname,
  569.                 'lastname' => $lastname,
  570.                 'email' => $email,
  571.                 'phone' => $phone,
  572.                 'address' => $address,
  573.                 'zipcode' => $zipcode,
  574.                 'state' => $state,
  575.                 'country' => $country
  576.             ]);
  577.             $mail->send();
  578.             
  579.             //send admin Mail
  580.             $mail2 = new \Pimcore\Mail();
  581.             // $mail->addTo("wien@uhrenhuebner.at");
  582.             $mail2->addTo($branchemail);
  583.             $mail2->addTo("a.daum@ontime.at");
  584.             $mail2->setDocument("/Emails/inquiry-admin");
  585.             $mail2->setParams([
  586.                 'brand' => $product->getBrand()->getName(),
  587.                 'model' => $product->getModel(),
  588.                 'reference' => $reference,
  589.                 'deeplink' => '',
  590.                 'productHtml' => $productHtml,
  591.                 'salutation' => $salutationtext,
  592.                 'iwantto' => $iwantto,
  593.                 'contactoption' => $contactoption,
  594.                 'branch' => $branch,
  595.                 'comment' => $comment,
  596.                 'title' => $title,
  597.                 'firstname' => $firstname,
  598.                 'lastname' => $lastname,
  599.                 'email' => $email,
  600.                 'phone' => $phone,
  601.                 'address' => $address,
  602.                 'zipcode' => $zipcode,
  603.                 'state' => $state,
  604.                 'country' => $country
  605.             ]);
  606.             $mail2->send();
  607.         }
  608.         return [];
  609.     }
  610.     
  611.     protected static function getProductHtml($product) {
  612.         $html = '<table style="border:none;"';
  613.             $html .= '<tr>';
  614.                 $html .= '<td style="padding: 10px">';
  615.                     $html .= '<img src="https://zeit.at/'. $product->getFrontImage()->getPath() . $product->getFrontImage()->getFilename() .'" alt="' . $product->getModel() . '" style="width:100%; height: auto; max-width:200px";>';
  616.                 $html .= '</td>';
  617.                 $html .= '<td style="padding: 10px 0px;">';
  618.                     $html .= '<p>'.$product->getModel().'</p>';
  619.                 $html .= '</td>';
  620.             $html .= '</tr>';
  621.         $html .= '</table>';
  622.         
  623.         return $html;
  624.     }
  625.     
  626.     /**
  627.      * @Template
  628.      * @param Request $request
  629.      * @return array
  630.      */
  631.     public function loginAction(Request $request) {
  632.         if ($request->getMethod() === "POST") {
  633.             $params = $request->request;
  634.             $email = $params->get("email");
  635.             $password = $params->get("password");
  636.             
  637.             $user = DataObject::getByPath("/CRM/Users/" . $email);
  638.             $passwordhash = $user->getPassword();
  639.             
  640.             if(password_verify($password, $passwordhash)) {
  641.                 //echo "login good";
  642.                 $loggedInUser = $user;
  643.                 $cookie_name = "zeituser";
  644.                 $cookie_value = "4DFGHJ8765FGHJ".$user->getId()."KJHGF45678JHG4343";
  645.                 setcookie($cookie_name, $cookie_value, time() + (86400 * 7), "/"); 
  646.                 return $this->redirect("/");
  647.                 //return $this->render('/default/products.html.twig', ["loggedInUser" => $loggedIdUser]);
  648.                 exit;
  649.             } else {
  650.                 echo "login no good";
  651.             }
  652.             
  653.             exit;
  654.         }
  655.         return [];
  656.     }
  657.     
  658.     /**
  659.      * @Template
  660.      * @param Request $request
  661.      * @return array
  662.      */
  663.     public function registerAction(Request $request) {
  664.         if ($request->getMethod() === "POST") {
  665.             $params = $request->request;
  666.             
  667.             $salutation = $params->get("salutation");
  668.             $title = $params->get("title");
  669.             $firstname = $params->get("firstname");
  670.             $lastname = $params->get("lastname");
  671.             $email = $params->get("email");
  672.             $phone = $params->get("phone");
  673.             $address = $params->get("address");
  674.             $zipcode = $params->get("zipcode");
  675.             $state = $params->get("city");
  676.             $country = $params->get("country");
  677.             
  678.             $password = $params->get("password");
  679.             
  680.             $userobject = new DataObject\Customer();
  681.             $userobject->setKey(\Pimcore\Model\Element\Service::getValidKey($email, 'object'));
  682.             $userobject->setParentId(390);
  683.             
  684.             $userobject->setSalutation($salutation);
  685.             $userobject->setTitle($title);
  686.             $userobject->setFirstName($firstname);
  687.             $userobject->setLastName($lastname);
  688.             $userobject->setAddress($address);
  689.             $userobject->setZipcode($zipcode);
  690.             $userobject->setCity($state);
  691.             $userobject->setPhone($phone);
  692.             $userobject->setEmail($email);
  693.             $userobject->setUsername($email);
  694.             $userobject->setCountry($country);
  695.             
  696.             $userobject->setPassword($password);
  697.             
  698.             $userobject->setPublished(true);
  699.             $userobject->save();
  700.             
  701.             echo "saved";            
  702.             exit;
  703.         } else {
  704.             
  705.         }
  706.     }
  707.     
  708.     /**
  709.      * @Template
  710.      * @param Request $request
  711.      * @return array
  712.      */
  713.     public function logoutAction(Request $request) {
  714.         
  715.         unset($_COOKIE['zeituser']);
  716.         setcookie('zeituser', null, -1, '/'); 
  717.         
  718.         exit;
  719.         return [];
  720.     }
  721.     
  722.     /**
  723.      * @Template
  724.      * @param Request $request
  725.      * @return array
  726.      */
  727.     public function checkoutAction(Request $request) {
  728.         return [];
  729.     }
  730.     
  731.     /**
  732.      * @Template
  733.      * @param Request $request
  734.      * @return array
  735.      */
  736.     public function cartAction(Request $request) {
  737.         return [];
  738.     }
  739.     
  740.     /**
  741.      * @Template
  742.      * @param Request $request
  743.      * @return array
  744.      */
  745.     public function deleteSoldProductsAction(Request $request) {
  746.         $checkstring = date('d-m-y');
  747.             //$checkstring .= 'manual';
  748.         $products = new DataObject\Product\Listing();
  749.         $products->setCondition("updateinfo != '$checkstring' AND (manualInput != '1' OR manualInput IS NULL)");
  750.         $products = $products->load();
  751.         if(count($products) < 100) {
  752.             foreach($products as $product) {
  753.                 $product->delete();
  754.             }
  755.         }
  756.        
  757.         exit;
  758.         return [];
  759.     
  760.     }
  761.     
  762.     /**
  763.      * @Template
  764.      * @param Request $request
  765.      * @return array
  766.      */
  767.     public function createBrandDocumentsAction(Request $request) {
  768.         // $branddocument = \Pimcore\Model\Document::getByPath("/Uhren/Marken/" . "A. Lange - Söhne");
  769.         // $branddocument->setProperty("navigation_name", "text", "A. Lange & Söhne");
  770.         // $branddocument->setController("App\Controller\DefaultController::brandAction");
  771.         // $branddocument->save();
  772.         // exit;
  773.         
  774.         $brands = new DataObject\Brand\Listing();
  775.         $brands->setCondition("o_path LIKE '%Uhren%'");
  776.         $brands = $brands->load();
  777.         
  778.         foreach($brands as $brand) {
  779.             $brandkey = str_replace(["&"], ["-"], $brand->getKey());
  780.             
  781.             $branddocument = \Pimcore\Model\Document::getByPath("/Uhren/Marken/" . $brandkey);
  782.             if($branddocument) {
  783.                 
  784.             } else {
  785.                 $page = new \Pimcore\Model\Document\Page();
  786.                 $page->setKey($brandkey);
  787.                 $page->setTitle($brand->getName());
  788.                 $page->setParentId(2);
  789.                 $page->setProperty("navigation_name", "text", $brand->getName());
  790.                 $page->setController("App\Controller\DefaultController::brandAction");
  791.                 $page->setPublished(true);
  792.                 $page->save();
  793.             }
  794.         }
  795.         
  796.         exit;
  797.         return [];
  798.     }
  799.     
  800.     /**
  801.      * @Template
  802.      * @param Request $request
  803.      * @return array
  804.      */
  805.     public function createBrandFolderAction(Request $request) {
  806.         
  807.         $brands = new DataObject\Brand\Listing();
  808.         $brands->setCondition("o_path LIKE '%Uhren%'");
  809.         $brands = $brands->load();
  810.         
  811.         // $brands = $brands[0];
  812.         
  813.         //var_dump($brands);
  814.         
  815.         // foreach($brands as $brand) {
  816.         //     $brandkey = str_replace(["&"], ["-"], $brand->getKey());
  817.         //     
  818.         //     $newObject = new DataObject\Folder(); 
  819.         //     $newObject->setKey(\Pimcore\Model\Element\Service::getValidKey($brandkey, 'folder'));
  820.         //     $newObject->setParentId(61);
  821.         //     $newObject->save();
  822.         //     
  823.         // }
  824.         
  825.         // $brandkey = "Jaeger-LeCoultre";
  826.         // $folder = DataObject::getByPath("/PIM/01 Uhren/02 Produkte/" . $brandkey);
  827.         // $parentId = $folder->getId();
  828.         // var_dump($folder->getId());
  829.         
  830.         exit;
  831.         return [];
  832.     }
  833.     
  834.     /**
  835.      * @Template
  836.      * @param Request $request
  837.      * @return array
  838.      */
  839.     public function addcollectionAction(Request $request) {
  840.         
  841.         
  842.         $brands = new DataObject\Brand\Listing();
  843.         $brands->setCondition("o_path LIKE '%Uhren%'");
  844.         $brands = $brands->load();
  845.         
  846.         //$brand = DataObject\Brand::getById(85);
  847.             
  848.         foreach($brands as $brand) {
  849.             // $collections = new DataObject\Category\Listing();
  850.             // if($brand->getId() == 85) {
  851.             //     $collections->setCondition("o_path LIKE :brand", ["brand" => "%Nomos%"]);
  852.             // } else {
  853.             //     $collections->setCondition("o_path LIKE :brand", ["brand" => "%".$brand->getName()."%"]);
  854.             // }
  855.             // $collections->load();
  856.             
  857.             $collections = $brand->getCollections();
  858.             
  859.             
  860.             if($collections) {
  861.                 $collections = $collections->getChildren();
  862.             } else {
  863.                 continue;
  864.             }
  865.             
  866.             
  867.             $brandid = $brand->getId();
  868.             
  869.             foreach($collections as $coll) {
  870.                 
  871.                 $key = $coll->getKey();
  872.                 
  873.                 $products = new DataObject\Product\Listing();
  874.                 if($brand->getName() == "A. Lange & Söhne") {
  875.                     $products->setCondition("UPPER(`o_key`) LIKE \"%$key%\" AND brand__id = $brandid");
  876.                 } elseif($coll->getId() == 281) {
  877.                     $products->setCondition("`o_key` LIKE \"%$key%\" AND `o_key` NOT LIKE \"%Superocean Heritage%\" AND brand__id = $brandid");
  878.                 } elseif($coll->getId() == 285) {
  879.                     $products->setCondition("`o_key` LIKE \"% Avi %\" AND brand__id = $brandid");
  880.                 } else {
  881.                     $products->setCondition("`o_key` LIKE \"%$key%\" AND brand__id = $brandid");
  882.                 }
  883.                 
  884.                 $products->load();
  885.                 foreach($products as $product) {
  886.                     $cats = $product->getCategories();
  887.                     if($cats) {
  888.                         if (!in_array($coll, $cats, true)) {
  889.                             array_push($cats, $coll);
  890.                         } 
  891.                     } else {
  892.                         array_push($cats, $coll);
  893.                     }
  894.                    
  895.                     $product->setCategories($cats);
  896.                     $product->save();
  897.                 }
  898.                 
  899.             }
  900.             echo "Brand: " . $brand->getName() . " done <br><hr><br>";
  901.         }
  902.             exit;
  903.         
  904.         foreach($brands as $brand) {
  905.             $collections = new DataObject\Category\Listing();
  906.             $collections->setCondition("o_path LIKE :brand", ["brand" => "%".$brand->getName()."%"]);
  907.             $collections->load();
  908.         
  909.             foreach($collections as $coll) {
  910.                 $key = $coll->getKey();
  911.                 $products = new DataObject\Product\Listing();
  912.                 if($brand->getName() == "A. Lange & Söhne") {
  913.                     $products->setCondition("UPPER(`o_key`) LIKE '%$key%'");
  914.                 } else {
  915.                     $products->setCondition("`o_key` LIKE '%$key%'");
  916.                 }
  917.                 $products->load();
  918.                 foreach($products as $product) {
  919.                     $cats = $product->getCategories();
  920.                     if (!in_array($coll, $cats)) {
  921.                         array_push($cats, $coll);
  922.                     }
  923.                     $product->setCategories($cats);
  924.                     $product->save();
  925.                 }
  926.                 
  927.             }
  928.             echo "Brand: " . $brand->getName() . " done <br><hr><br>";
  929.         }
  930.         
  931.         
  932.     
  933.         exit;
  934.         return [];
  935.     }
  936.     
  937.     /**
  938.      * @Template
  939.      * @param Request $request
  940.      * @return array
  941.      */
  942.     public function testAction(Request $request) {
  943.         
  944.         ini_set('memory_limit', '1024M');
  945.         $listing = new \Pimcore\Model\Document\Listing();
  946.         $listing = $listing->load();
  947.         
  948.         foreach($listing as $doc) {
  949.             $key = $doc->getKey();
  950.             $key = str_replace(" ", "-", $key);
  951.             $doc->setKey($key);
  952.             $doc->save();
  953.         }
  954.         
  955.         exit;
  956.        //  $brands = new DataObject\Product\Listing();
  957.        // 
  958.        //  
  959.        //  $brands->setCondition("o_path LIKE '%Uhren%' AND o_modificationDate > 1667284831");
  960.        //  $brands->load();
  961.        //  
  962.        //  var_dump(count($brands));
  963.         
  964.         exit;
  965.         
  966.         return [];
  967.     }
  968.     
  969.     /**
  970.      * @Template
  971.      * @param Request $request
  972.      * @return array
  973.      */
  974.     public function importSingleProductAction(Request $request) {
  975.         $searchstring = str_replace("/", "", $request->get("searchstring"));
  976.         
  977.         
  978.         $servername = "carat-dev.cp4unapazytb.eu-central-1.rds.amazonaws.com";
  979.         $username = "atimis";
  980.         $password = "gui8kknyzG";
  981.         $dbname = "carat_100";
  982.         
  983.         // Create connection
  984.         $conn = new \mysqli($servername, $username, $password, $dbname);
  985.         // Check connection
  986.         if ($conn->connect_error) {
  987.           die("Connection failed: " . $conn->connect_error);
  988.         }
  989.         
  990.         $conn->set_charset("utf8mb4");
  991.         
  992.         $sql = "SELECT DISTINCT article.`id`, article.`articleNumber`, article.`brand`, article.`category`, article.`productLine`, article.`subcategory`, item.`price` FROM `Article` article INNER JOIN `Item` item ON item.`article_id` = article.`id` WHERE article.articleNumber LIKE '%$searchstring%' AND article.`category` = 'Uhr' GROUP BY article.`articleNumber`;";
  993.         
  994.         $result = $conn->query($sql);
  995.         
  996.         while($row = $result->fetch_assoc()) {
  997.             $extId = $row["id"];
  998.             
  999.             $prObject = DataObject::getByPath("/PIM/01 Uhren/02 Produkte/" . $row["brand"] . "/" . $row["productLine"] . "-" . $row["articleNumber"]);
  1000.             
  1001.             if($prObject) {
  1002.                 $newObject = $prObject;
  1003.             } else {
  1004.                 $newObject = new DataObject\Product(); 
  1005.                 $newObject->setKey(\Pimcore\Model\Element\Service::getValidKey($row["productLine"] . "-" . $row["articleNumber"], 'object'));
  1006.                 
  1007.                 $folder = DataObject::getByPath("/PIM/01 Uhren/02 Produkte/" . $row["brand"]);
  1008.                 if($folder == null) {
  1009.                     $folder = new DataObject\Folder(); 
  1010.                     $folder->setKey(\Pimcore\Model\Element\Service::getValidKey($row["brand"], 'folder'));
  1011.                     $folder->setParentId(61);
  1012.                     $folder->save();
  1013.                 }
  1014.                 $parentId = $folder->getId();
  1015.                 
  1016.                 $newObject->setParentId($parentId);
  1017.             }
  1018.             
  1019.             $newObject->setModel($row["productLine"]);
  1020.             $newObject->setArticleNumber($row["articleNumber"]);
  1021.             $newObject->setExtId($row["id"]);
  1022.             $newObject->setPrice($row["price"]);
  1023.             //set brand
  1024.             $brand = $object = DataObject::getByPath("/PIM/01 Uhren/01 Marken/" . $row["brand"]);
  1025.             $newObject->setBrand($brand);
  1026.             
  1027.             //set specifications
  1028.             $items = new DataObject\Fieldcollection();
  1029.             $specsql = "SELECT name, value FROM `ArticleProperty` WHERE `article_id` = $extId;";
  1030.             $specresult = $conn->query($specsql);
  1031.             
  1032.             while($specrow = $specresult->fetch_assoc()) {
  1033.                 $item = new DataObject\Fieldcollection\Data\Specification();
  1034.                 
  1035.                 $item->setSpecKey($specrow["name"]);
  1036.                 $item->setSpecValue($specrow["value"]);
  1037.                 
  1038.                 $items->add($item);
  1039.             }
  1040.         
  1041.             $newObject->setSpecs($items);
  1042.             
  1043.             //set frontimage
  1044.             $imagesql = "SELECT src FROM `Image` WHERE `article_id` = $extId;";
  1045.             $imageresult = $conn->query($imagesql);
  1046.             $assetfolderpath = "/PIM/01 Uhren/02 Produkte/" . $row["brand"] . "/" . str_replace("/", "-", $row["productLine"]) . "-" . $row["articleNumber"];
  1047.             Asset\Service::createFolderByPath($assetfolderpath);
  1048.             
  1049.             $counter = 0;
  1050.             $addImages = [];
  1051.             while($imagerow = $imageresult->fetch_assoc()) {
  1052.                 $counter += 1;
  1053.                 $newAsset = new \Pimcore\Model\Asset\Image();
  1054.                 $newAsset->setFilename($imagerow["src"]);
  1055.                 $newAsset->setData(file_get_contents("https://huebner.carat.solutions/carat/filedownload?fileName=" . $imagerow["src"]));
  1056.                 $newAsset->setParent(\Pimcore\Model\Asset::getByPath($assetfolderpath));
  1057.                 
  1058.                 $checkasset = null;    
  1059.                 $checkasset = \Pimcore\Model\Asset::getByPath($assetfolderpath . "/" . $imagerow["src"]);
  1060.                 
  1061.                 if($checkasset) {
  1062.                     $newAsset = $checkasset;
  1063.                 } else {
  1064.                     $newAsset->save();
  1065.                 }
  1066.                 
  1067.                 
  1068.                 if($counter == 1) {
  1069.                     $newObject->setFrontimage($newAsset);
  1070.                 } else {
  1071.                     array_push($addImages, $newAsset);
  1072.                 }
  1073.             }
  1074.             
  1075.             if(count($addImages) > 0) {
  1076.                 $newObject->setImages(new \Pimcore\Model\DataObject\Data\ImageGallery($addImages));
  1077.             }
  1078.             
  1079.             
  1080.             //set gender
  1081.             $gendercat = DataObject::getByPath("/PIM/01 Uhren/03 Kategorien/" . $row["subcategory"]);
  1082.             $cats = [$gendercat];
  1083.             $newObject->setCategories($cats);
  1084.             
  1085.             $newObject->setPublished(true);
  1086.             $newObject->save();
  1087.             
  1088.             echo "Produkt: " . $row["productLine"] . "-" . $row["articleNumber"] . " erfolgreich importiert";
  1089.             
  1090.             //var_dump($brand);
  1091.         }
  1092.         
  1093.         $conn->close();
  1094.         exit;
  1095.     }
  1096.     
  1097.     /**
  1098.      * @Template
  1099.      * @param Request $request
  1100.      * @return array
  1101.      */
  1102.     public function addJewelleryCatsAction(Request $request) {
  1103.         $products = new DataObject\Product\Listing();
  1104.         $products->setCondition("o_path LIKE '%Schmuck%'");
  1105.         $products->load();
  1106.         $i = 0;
  1107.         
  1108.         foreach($products as $product) {
  1109.             $catstring = $product->getCatstring();
  1110.             $categories = new DataObject\Category\Listing();
  1111.             $categories->setCondition("o_path LIKE '%02 Schmuck/03 Kategorien%' AND caratvalues LIKE '%$catstring%'");
  1112.             $categories->load();
  1113.             foreach($categories as $cat) {
  1114.                 $cats = $product->getCategories();
  1115.                 if(is_array($cats)) {
  1116.                     if(!in_array($cat, $cats)) {
  1117.                         $cats[] = $cat;
  1118.                     }
  1119.                 } else {
  1120.                     if(!in_array($cat, $cats)) {
  1121.                         $cats = [];
  1122.                         $cats[] = $cat;
  1123.                     }
  1124.                 }
  1125.                 $product->setCategories($cats);
  1126.                 $product->save();
  1127.                 $i++;
  1128.             }
  1129.         }
  1130.         
  1131.         $brands = new DataObject\Brand\Listing();
  1132.         $brands->setCondition("o_path LIKE '%Schmuck%'");
  1133.         $brands->load();
  1134.         foreach($brands as $brand) {
  1135.             $collections = $brand->getCollections();
  1136.             $collections = $collections->getChildren();
  1137.             $brandid = $brand->getId();
  1138.             
  1139.             foreach($collections as $coll) {
  1140.                 $key = $coll->getKey();
  1141.                 
  1142.                 $products = new DataObject\Product\Listing();
  1143.                 $products->setCondition("`o_key` LIKE \"%$key%\" AND brand__id = $brandid AND o_path LIKE '%Schmuck%'");
  1144.                 
  1145.                 $products->load();
  1146.                 foreach($products as $product) {
  1147.                     $cats = $product->getCategories();
  1148.                     if($cats) {
  1149.                         if (!in_array($coll, $cats, true)) {
  1150.                             array_push($cats, $coll);
  1151.                         } 
  1152.                     } else {
  1153.                         array_push($cats, $coll);
  1154.                     }
  1155.                    
  1156.                     $product->setCategories($cats);
  1157.                     $product->save();
  1158.                 }
  1159.             }
  1160.         }
  1161.         
  1162.         //echo "done $i products";
  1163.         
  1164.         exit;
  1165.         return [];
  1166.     }
  1167.     
  1168.     /**
  1169.      * @Template
  1170.      * @param Request $request
  1171.      * @return array
  1172.      */
  1173.     public function importSingleJewelleryProductAction(Request $request) {
  1174.         $servername = "carat-dev.cp4unapazytb.eu-central-1.rds.amazonaws.com";
  1175.             $username = "atimis";
  1176.             $password = "gui8kknyzG";
  1177.             $dbname = "carat_100";
  1178.             
  1179.             // Create connection
  1180.             $conn = new \mysqli($servername, $username, $password, $dbname);
  1181.             // Check connection
  1182.             if ($conn->connect_error) {
  1183.               die("Connection failed: " . $conn->connect_error);
  1184.             }
  1185.             
  1186.             $conn->set_charset("utf8mb4");
  1187.             
  1188.         
  1189.             
  1190.             
  1191.             
  1192.             
  1193.             // FINAL QUERY
  1194.             
  1195.             $sql = "SELECT DISTINCT article.`id`, article.`articleNumber`, article.`brand`, article.`category`, article.`productLine`, article.`subcategory`, item.`price`, item.`stockInputDate` FROM `Article` article INNER JOIN `Item` item ON item.`article_id` = article.`id` WHERE item.`sold` = 0 AND article.`category` = 'Schmuck' AND item.`webshop` = 'JA'";
  1196.             
  1197.             // END FINAL QUERY
  1198.             
  1199.             
  1200.            // $sql = "SELECT * FROM Article WHERE `brand` LIKE '%Lange%' LIMIT 10;";
  1201.             
  1202.             $result = $conn->query($sql);
  1203.             
  1204.             // var_dump($result);
  1205.             // exit;
  1206.             
  1207.             while($row = $result->fetch_assoc()) {
  1208.                 $extId = $row["id"];
  1209.                 
  1210.                 $row = array_map('trim', $row);
  1211.                 
  1212.                 $key = \Pimcore\Model\Element\Service::getValidKey($row["productLine"] . "-" . $row["articleNumber"], 'object');
  1213.                 
  1214.                 $prObject = DataObject::getByPath("/PIM/02 Schmuck/02 Produkte/" . $row["brand"] . "/" . $key);
  1215.                 
  1216.                 
  1217.                 
  1218.                 if($prObject) {
  1219.                     $checkstring = date('d-m-y');
  1220.                     //$checkstring .= 'manual';
  1221.                     if($prObject->getUpdateInfo() == $checkstring) {
  1222.                         continue;
  1223.                     }
  1224.                     $newObject = $prObject;
  1225.                     $newObject->setupdateinfo($checkstring);
  1226.                 } else {
  1227.                     $newObject = new DataObject\Product(); 
  1228.                     $newObject->setKey($key);
  1229.                     $newObject->setupdateinfo($checkstring);
  1230.                     
  1231.                     // get Brand Folder ID
  1232.                     $folder = DataObject::getByPath("/PIM/02 Schmuck/02 Produkte/" . $row["brand"]);
  1233.                     
  1234.                     // if($row["brand"] == "Jaeger-LeCoultre") {
  1235.                     //     $folder = DataObject::getById(396);
  1236.                     // }
  1237.                     if($row["brand"] == "FOPE") {
  1238.                         $folder = DataObject::getById(3160);
  1239.                     }
  1240.                     if($row["brand"] == "Aldusblatt Herzog Loibner") {
  1241.                         $folder = DataObject::getById(1712);
  1242.                     }
  1243.                     
  1244.                     if($folder == null) {
  1245.                         $folder = new DataObject\Folder(); 
  1246.                         $folder->setKey(\Pimcore\Model\Element\Service::getValidKey($row["brand"], 'folder'));
  1247.                         $folder->setParentId(64);
  1248.                         $folder->save();
  1249.                     }
  1250.                     $parentId = $folder->getId();
  1251.                     
  1252.                     $newObject->setParentId($parentId);
  1253.                 }
  1254.                 
  1255.                 $newObject->setModel($row["productLine"]);
  1256.                 $newObject->setArticleNumber($row["articleNumber"]);
  1257.                 $newObject->setStockInputDate($row["stockInputDate"]);
  1258.                 $newObject->setExtId($row["id"]);
  1259.                 $newObject->setPrice($row["price"]);
  1260.                 $newObject->setCatstring($row["subcategory"]);
  1261.                 
  1262.                 //set brand
  1263.                 $brand = DataObject::getByPath("/PIM/02 Schmuck/01 Marken/" . $row["brand"]);
  1264.                 // if($row["brand"] == "Jaeger-LeCoultre") {
  1265.                 //     $brand = DataObject\Brand::getById(23);
  1266.                 // }
  1267.                 if($row["brand"] == "FOPE") {
  1268.                     $brand = DataObject::getById(3201);
  1269.                 }
  1270.                 if($row["brand"] == "Aldusblatt Herzog Loibner") {
  1271.                     $brand = DataObject::getById(99);
  1272.                 }
  1273.                 $newObject->setBrand($brand);
  1274.                 
  1275.                 //set specifications
  1276.                 $items = new DataObject\Fieldcollection();
  1277.                 $specsql = "SELECT carat, label, number, description FROM `GemItem` WHERE `article_id` = $extId;";
  1278.                 $specresult = $conn->query($specsql);
  1279.                 
  1280.                 while($specrow = $specresult->fetch_assoc()) {
  1281.                     $item = new DataObject\Fieldcollection\Data\JewellerySpecification();
  1282.                     
  1283.                     $item->setSpecKey($specrow["label"]);
  1284.                     $item->setSpecValue($specrow["carat"]);
  1285.                     $item->setSpecCount($specrow["number"]);
  1286.                     $item->setSpecDescription($specrow["description"]);                    
  1287.                     $items->add($item);
  1288.                 }
  1289.                 
  1290.                 $specsql = "SELECT name, value FROM `ArticleProperty` WHERE `article_id` = $extId;";
  1291.                 $specresult = $conn->query($specsql);
  1292.                 
  1293.                 while($specrow = $specresult->fetch_assoc()) {
  1294.                     $item = new DataObject\Fieldcollection\Data\JewellerySpecification();
  1295.                     
  1296.                     $item->setSpecKey($specrow["name"]);
  1297.                     $item->setSpecValue($specrow["value"]);
  1298.                     $item->setSpecCount(null);
  1299.                     $item->setSpecDescription(null);                    
  1300.                     $items->add($item);
  1301.                 }
  1302.         
  1303.                 $newObject->setSpecs($items);
  1304.                 
  1305.                 //set frontimage
  1306.                 $imagesql = "SELECT src FROM `Image` WHERE `article_id` = $extId;";
  1307.                 $imageresult = $conn->query($imagesql);
  1308.                 $articlenumber = str_replace("/", "-", $row["articleNumber"]);
  1309.                 $assetfolderpath = "/PIM/02 Schmuck/02 Produkte/" . $row["brand"] . "/" . str_replace(["/", '"', "+"], ["-", "", ""], $row["productLine"]) . "-" . $articlenumber;
  1310.                 $assetfolderpath = str_replace("+", "", $assetfolderpath);
  1311.                 $assetfolderpath = str_replace(".", "", $assetfolderpath);
  1312.                 $assetfolderpath = str_replace("|", "", $assetfolderpath);
  1313.                 $assetfolderpath = str_replace("ß", "ss", $assetfolderpath);
  1314.                 Asset\Service::createFolderByPath($assetfolderpath);
  1315.                 
  1316.                 $counter = 0;
  1317.                 $addImages = [];
  1318.                 while($imagerow = $imageresult->fetch_assoc()) {
  1319.                     $counter += 1;
  1320.                     $newAsset = new \Pimcore\Model\Asset\Image();
  1321.                     $newAsset->setFilename($imagerow["src"]);
  1322.                     $newAsset->setData(file_get_contents("https://huebner.carat.solutions/carat/filedownload?fileName=" . $imagerow["src"]));
  1323.                     $newAsset->setParent(\Pimcore\Model\Asset::getByPath($assetfolderpath));
  1324.                     
  1325.                     $checkasset = null;    
  1326.                     $checkasset = \Pimcore\Model\Asset::getByPath($assetfolderpath . "/" . $imagerow["src"]);
  1327.                     
  1328.                     echo "trying to save asset for " . $row["productLine"] . "-" . $row["articleNumber"] . "<br><hr><br>";
  1329.                     
  1330.                     if(strpos($imagerow["src"], "html") == false && strpos($imagerow["src"], "avif") == false && $imagerow["src"] != "7281fa99-3001-41a5-9be1-8dcfe04d997b.") {
  1331.                         if($checkasset) {
  1332.                             $checkasset->delete();
  1333.                             $newAsset->save();
  1334.                         } else {
  1335.                             $newAsset->save();
  1336.                         }
  1337.                     }
  1338.                     if($counter == 1) {
  1339.                         $newObject->setFrontimage($newAsset);
  1340.                     } else {
  1341.                         $advancedImage = new \Pimcore\Model\DataObject\Data\Hotspotimage();
  1342.                         $advancedImage->setImage($newAsset);
  1343.                         array_push($addImages, $advancedImage);
  1344.                     }
  1345.                 }
  1346.                 
  1347.                 if(count($addImages) > 0) {   
  1348.                     $newObject->setImages(new \Pimcore\Model\DataObject\Data\ImageGallery($addImages));
  1349.                 }
  1350.                 
  1351.                 //var_dump($addImages);
  1352.                 
  1353.                 
  1354.                 //set gender
  1355.                 // $gendercat = DataObject::getByPath("/PIM/01 Uhren/03 Kategorien/" . $row["subcategory"]);
  1356.                 // if(!$gendercat) {
  1357.                 //     $newcat = new DataObject\Category(); 
  1358.                 //     $newcat->setKey(\Pimcore\Model\Element\Service::getValidKey($row["subcategory"], 'object'));
  1359.                 //     $newcat->setParentId(101);
  1360.                 //     $newcat->setTitle($row["subcategory"]);
  1361.                 //     $newcat->setPublished(true);
  1362.                 //     $newcat->save();
  1363.                 //     $gendercat = $newcat;
  1364.                 // }
  1365.                 
  1366.                 // $cats = [$gendercat];
  1367.                 // $newObject->setCategories($cats);
  1368.                 
  1369.                 $newObject->setPublished(true);
  1370.                 $newObject->save();
  1371.                 
  1372.                 //var_dump($brand);
  1373.             }
  1374.             
  1375.             $conn->close();
  1376.             
  1377.             
  1378.             exit;
  1379.             return [];
  1380.     }
  1381.     
  1382.     /**
  1383.      * @Template
  1384.      * @param Request $request
  1385.      * @return array
  1386.      */
  1387.     public function updateProductAction(Request $request) {
  1388.         
  1389.         $id = str_replace("/", "", $request->get("caratid"));
  1390.         $pimId = str_replace("/", "", $request->get("pimid"));
  1391.         
  1392.         $servername = "carat-dev.cp4unapazytb.eu-central-1.rds.amazonaws.com";
  1393.         //$servername = "52.29.59.182";
  1394.         $username = "atimis";
  1395.         $password = "gui8kknyzG";
  1396.         $dbname = "carat_100";
  1397.         
  1398.         // Create connection
  1399.         $conn = new \mysqli($servername, $username, $password, $dbname);
  1400.         // Check connection
  1401.         if ($conn->connect_error) {
  1402.           die("Connection failed: " . $conn->connect_error);
  1403.         }
  1404.         
  1405.         
  1406.         $conn->set_charset("utf8mb4");
  1407.         
  1408.         // FINAL QUERY
  1409.         
  1410.         $sql = "SELECT DISTINCT article.`id`, article.`articleNumber`, article.`brand`, article.`category`, article.`productLine`, article.`subcategory`, item.`price`, item.`stockInputDate` FROM `Article` article INNER JOIN `Item` item ON item.`article_id` = article.`id` WHERE article.`id` = $id GROUP BY article.`articleNumber`";
  1411.         
  1412.         // END FINAL QUERY
  1413.         
  1414.         
  1415.        // $sql = "SELECT * FROM Article WHERE `brand` LIKE '%Lange%' LIMIT 10;";
  1416.         
  1417.         $result = $conn->query($sql);
  1418.         
  1419.         // var_dump($result);
  1420.         // exit;
  1421.         
  1422.         while($row = $result->fetch_assoc()) {
  1423.             $extId = $row["id"];
  1424.             $row = array_map('trim', $row);
  1425.             
  1426.             $watchjewel = $row["category"];
  1427.             
  1428.             if($watchjewel == "Uhr") {
  1429.                 $key = \Pimcore\Model\Element\Service::getValidKey($row["productLine"] . "-" . $row["articleNumber"], 'object');
  1430.                 
  1431.                 $prObject = DataObject::getById($pimId);
  1432.                 $checkstring = date('d-m-y');
  1433.             //$checkstring .= 'manual';
  1434.                 // $checkstring .= "-zenith";
  1435.                 if($prObject) {
  1436.                     // if($prObject->getUpdateinfo() == "291122-try1" || $prObject->getUpdateinfo() == "291122-try2" || $prObject->getUpdateinfo() == "291122-try3" || $prObject->getUpdateinfo() == "291122-try4") {
  1437.                     //     continue;
  1438.                     // }
  1439.                     
  1440.                     
  1441.                     if($prObject->getUpdateInfo() == $checkstring) {
  1442.                         //continue;
  1443.                     }
  1444.                     $newObject = $prObject;
  1445.                     $newObject->setupdateinfo($checkstring);
  1446.                 } else {
  1447.                     $newObject = new DataObject\Product(); 
  1448.                     $newObject->setKey($key);
  1449.                     $newObject->setupdateinfo($checkstring);
  1450.                     
  1451.                     // get Brand Folder ID
  1452.                     $folder = DataObject::getByPath("/PIM/01 Uhren/02 Produkte/" . $row["brand"]);
  1453.                     
  1454.                     if($row["brand"] == "Jaeger-LeCoultre") {
  1455.                         $folder = DataObject::getById(396);
  1456.                     }
  1457.                     if($row["brand"] == "IWC") {
  1458.                         $folder = DataObject::getById(962);
  1459.                     }
  1460.                     if($row["brand"] == "Nomos Glashütte") {
  1461.                         $folder = DataObject::getById(407);
  1462.                     }
  1463.                     
  1464.                     if($folder == null) {
  1465.                         $folder = new DataObject\Folder(); 
  1466.                         $folder->setKey(\Pimcore\Model\Element\Service::getValidKey($row["brand"], 'folder'));
  1467.                         $folder->setParentId(61);
  1468.                         $folder->save();
  1469.                     }
  1470.                     $parentId = $folder->getId();
  1471.                     
  1472.                     $newObject->setParentId($parentId);
  1473.                 }
  1474.                 
  1475.                 $newObject->setModel($row["productLine"]);
  1476.                 $newObject->setArticleNumber($row["articleNumber"]);
  1477.                 $newObject->setStockInputDate($row["stockInputDate"]);
  1478.                 $newObject->setExtId($row["id"]);
  1479.                 $newObject->setPrice($row["price"]);
  1480.                 
  1481.                 //set brand
  1482.                 $brand = DataObject::getByPath("/PIM/01 Uhren/01 Marken/" . $row["brand"]);
  1483.                 if($row["brand"] == "Jaeger-LeCoultre") {
  1484.                     $brand = DataObject\Brand::getById(23);
  1485.                 }
  1486.                 if($row["brand"] == "IWC") {
  1487.                     $brand = DataObject\Brand::getById(82);
  1488.                 }
  1489.                 if($row["brand"] == "Nomos Glashütte") {
  1490.                     $brand = DataObject\Brand::getById(85);
  1491.                 }
  1492.                 if($row["brand"] == "Sattler") {
  1493.                     $brand = DataObject\Brand::getById(77);
  1494.                 }
  1495.                 $newObject->setBrand($brand);
  1496.                 
  1497.                 //set specifications
  1498.                 $items = new DataObject\Fieldcollection();
  1499.                 $specsql = "SELECT name, value FROM `ArticleProperty` WHERE `article_id` = $extId;";
  1500.                 $specresult = $conn->query($specsql);
  1501.                 
  1502.                 while($specrow = $specresult->fetch_assoc()) {
  1503.                     $item = new DataObject\Fieldcollection\Data\Specification();
  1504.                     
  1505.                     $item->setSpecKey($specrow["name"]);
  1506.                     $item->setSpecValue($specrow["value"]);
  1507.                     
  1508.                     $items->add($item);
  1509.                 }
  1510.                 
  1511.                 $newObject->setSpecs($items);
  1512.                 
  1513.                 //set frontimage
  1514.                 $imagesql = "SELECT src FROM `Image` WHERE `article_id` = $extId;";
  1515.                 $imageresult = $conn->query($imagesql);
  1516.                 $articlenumber = str_replace("/", "-", $row["articleNumber"]);
  1517.                 $assetfolderpath = "/PIM/01 Uhren/02 Produkte/" . $row["brand"] . "/" . str_replace(["/", '"', "+"], ["-", "", ""], $row["productLine"]) . "-" . $articlenumber;
  1518.                 $assetfolderpath = str_replace("+", "", $assetfolderpath);
  1519.                 $assetfolderpath = str_replace(".", "", $assetfolderpath);
  1520.                 $assetfolderpath = str_replace("|", "", $assetfolderpath);
  1521.                 $assetfolderpath = str_replace("ß", "ss", $assetfolderpath);
  1522.                 Asset\Service::createFolderByPath($assetfolderpath);
  1523.                 
  1524.                 $counter = 0;
  1525.                 $addImages = [];
  1526.                 while($imagerow = $imageresult->fetch_assoc()) {
  1527.                     $counter += 1;
  1528.                     $newAsset = new \Pimcore\Model\Asset\Image();
  1529.                     $newAsset->setFilename($imagerow["src"]);
  1530.                     $newAsset->setData(file_get_contents("https://huebner.carat.solutions/carat/filedownload?fileName=" . $imagerow["src"]));
  1531.                     $newAsset->setParent(\Pimcore\Model\Asset::getByPath($assetfolderpath));
  1532.                     
  1533.                     $checkasset = null;    
  1534.                     $checkasset = \Pimcore\Model\Asset::getByPath($assetfolderpath . "/" . $imagerow["src"]);
  1535.                     
  1536.                     //echo "trying to save asset for " . $row["productLine"] . "-" . $row["articleNumber"] . "<br><hr><br>";
  1537.                     
  1538.                     if(strpos($imagerow["src"], "html") == false && strpos($imagerow["src"], "avif") == false && $imagerow["src"] != "7281fa99-3001-41a5-9be1-8dcfe04d997b.") {
  1539.                         if($checkasset) {
  1540.                            $checkasset->delete();
  1541.                            $newAsset->save();
  1542.                         } else {
  1543.                             // var_dump($newAsset->getFullPath());
  1544.                             // var_dump($assetfolderpath);
  1545.                             // var_dump(\Pimcore\Model\Asset::getByPath($assetfolderpath));
  1546.                             // exit;
  1547.                             $newAsset->save();
  1548.                         }
  1549.                     }
  1550.                     if($counter == 1) {
  1551.                         $newObject->setFrontimage($newAsset);
  1552.                     } else {
  1553.                         $advancedImage = new \Pimcore\Model\DataObject\Data\Hotspotimage();
  1554.                         $advancedImage->setImage($newAsset);
  1555.                         array_push($addImages, $advancedImage);
  1556.                     }
  1557.                 }
  1558.                 
  1559.                 if(count($addImages) > 0) {   
  1560.                     $newObject->setImages(new \Pimcore\Model\DataObject\Data\ImageGallery($addImages));
  1561.                 }
  1562.                 
  1563.                 //var_dump($addImages);
  1564.                 
  1565.                 
  1566.                 //set gender
  1567.                 $gendercat = DataObject::getByPath("/PIM/01 Uhren/03 Kategorien/" . $row["subcategory"]);
  1568.                 if(!$gendercat) {
  1569.                     $newcat = new DataObject\Category(); 
  1570.                     $newcat->setKey(\Pimcore\Model\Element\Service::getValidKey($row["subcategory"], 'object'));
  1571.                     $newcat->setParentId(101);
  1572.                     $newcat->setTitle($row["subcategory"]);
  1573.                     $newcat->setPublished(true);
  1574.                     $newcat->save();
  1575.                     $gendercat = $newcat;
  1576.                 }
  1577.                 
  1578.                 $cats = [$gendercat];
  1579.                 $newObject->setCategories($cats);
  1580.                 
  1581.                 $newObject->setPublished(true);
  1582.                 $newObject->save();
  1583.                 
  1584.                 $this->addBrandCollections($brand);
  1585.                 
  1586.             } elseif($watchjewel == "Schmuck") {
  1587.                
  1588.                 $key = \Pimcore\Model\Element\Service::getValidKey($row["productLine"] . "-" . $row["articleNumber"], 'object');
  1589.                 
  1590.                 $prObject = DataObject::getById($pimId);
  1591.                 $checkstring = date('d-m-y');
  1592.             //$checkstring .= 'manual';
  1593.                 // $checkstring .= "-zenith";
  1594.                 if($prObject) {
  1595.                     // if($prObject->getUpdateinfo() == "291122-try1" || $prObject->getUpdateinfo() == "291122-try2" || $prObject->getUpdateinfo() == "291122-try3" || $prObject->getUpdateinfo() == "291122-try4") {
  1596.                     //     continue;
  1597.                     // }
  1598.                     
  1599.                     
  1600.                     if($prObject->getUpdateInfo() == $checkstring) {
  1601.                         //continue;
  1602.                     }
  1603.                     $newObject = $prObject;
  1604.                     $newObject->setupdateinfo($checkstring);
  1605.                 } else {
  1606.                     $newObject = new DataObject\Product(); 
  1607.                     $newObject->setKey($key);
  1608.                     $newObject->setupdateinfo($checkstring);
  1609.                     
  1610.                     // get Brand Folder ID
  1611.                     $folder = DataObject::getByPath("/PIM/02 Schmuck/02 Produkte/" . $row["brand"]);
  1612.                     
  1613.                     if($folder == null) {
  1614.                         $folder = new DataObject\Folder(); 
  1615.                         $folder->setKey(\Pimcore\Model\Element\Service::getValidKey($row["brand"], 'folder'));
  1616.                         $folder->setParentId(64);
  1617.                         $folder->save();
  1618.                     }
  1619.                     $parentId = $folder->getId();
  1620.                     
  1621.                     $newObject->setParentId($parentId);
  1622.                 }
  1623.                 
  1624.                 $newObject->setModel($row["productLine"]);
  1625.                 $newObject->setArticleNumber($row["articleNumber"]);
  1626.                 $newObject->setStockInputDate($row["stockInputDate"]);
  1627.                 $newObject->setExtId($row["id"]);
  1628.                 $newObject->setPrice($row["price"]);
  1629.                 
  1630.                 //set brand
  1631.                 $brand = DataObject::getByPath("/PIM/02 Schmuck/01 Marken/" . $row["brand"]);
  1632.                 if($row["brand"] == "Jaeger-LeCoultre") {
  1633.                     $brand = DataObject\Brand::getById(23);
  1634.                 }
  1635.                 if($row["brand"] == "IWC") {
  1636.                     $brand = DataObject\Brand::getById(82);
  1637.                 }
  1638.                 if($row["brand"] == "Nomos Glashütte") {
  1639.                     $brand = DataObject\Brand::getById(85);
  1640.                 }
  1641.                 if($row["brand"] == "Sattler") {
  1642.                     $brand = DataObject\Brand::getById(77);
  1643.                 }
  1644.                 $newObject->setBrand($brand);
  1645.                 
  1646.                 //set specifications
  1647.                 $items = new DataObject\Fieldcollection();
  1648.                 $specsql = "SELECT name, value FROM `ArticleProperty` WHERE `article_id` = $extId;";
  1649.                 $specresult = $conn->query($specsql);
  1650.                 
  1651.                 while($specrow = $specresult->fetch_assoc()) {
  1652.                     $item = new DataObject\Fieldcollection\Data\Specification();
  1653.                     
  1654.                     $item->setSpecKey($specrow["name"]);
  1655.                     $item->setSpecValue($specrow["value"]);
  1656.                     
  1657.                     $items->add($item);
  1658.                 }
  1659.                 
  1660.                 $newObject->setSpecs($items);
  1661.                 
  1662.                 //set frontimage
  1663.                 $imagesql = "SELECT src FROM `Image` WHERE `article_id` = $extId;";
  1664.                 $imageresult = $conn->query($imagesql);
  1665.                 $articlenumber = str_replace("/", "-", $row["articleNumber"]);
  1666.                 $assetfolderpath = "/PIM/02 Schmuck/02 Produkte/" . $row["brand"] . "/" . str_replace(["/", '"', "+"], ["-", "", ""], $row["productLine"]) . "-" . $articlenumber;
  1667.                 $assetfolderpath = str_replace("+", "", $assetfolderpath);
  1668.                 $assetfolderpath = str_replace(".", "", $assetfolderpath);
  1669.                 $assetfolderpath = str_replace("|", "", $assetfolderpath);
  1670.                 $assetfolderpath = str_replace("ß", "ss", $assetfolderpath);
  1671.                 Asset\Service::createFolderByPath($assetfolderpath);
  1672.                 
  1673.                 $counter = 0;
  1674.                 $addImages = [];
  1675.                 while($imagerow = $imageresult->fetch_assoc()) {
  1676.                     $counter += 1;
  1677.                     $newAsset = new \Pimcore\Model\Asset\Image();
  1678.                     $newAsset->setFilename($imagerow["src"]);
  1679.                     $newAsset->setData(file_get_contents("https://huebner.carat.solutions/carat/filedownload?fileName=" . $imagerow["src"]));
  1680.                     $newAsset->setParent(\Pimcore\Model\Asset::getByPath($assetfolderpath));
  1681.                     
  1682.                     $checkasset = null;    
  1683.                     $checkasset = \Pimcore\Model\Asset::getByPath($assetfolderpath . "/" . $imagerow["src"]);
  1684.                     
  1685.                     //echo "trying to save asset for " . $row["productLine"] . "-" . $row["articleNumber"] . "<br><hr><br>";
  1686.                     
  1687.                     if(strpos($imagerow["src"], "html") == false && strpos($imagerow["src"], "avif") == false && $imagerow["src"] != "7281fa99-3001-41a5-9be1-8dcfe04d997b.") {
  1688.                         if($checkasset) {
  1689.                            $checkasset->delete();
  1690.                            $newAsset->save();
  1691.                         } else {
  1692.                             // var_dump($newAsset->getFullPath());
  1693.                             // var_dump($assetfolderpath);
  1694.                             // var_dump(\Pimcore\Model\Asset::getByPath($assetfolderpath));
  1695.                             // exit;
  1696.                             $newAsset->save();
  1697.                         }
  1698.                     }
  1699.                     if($counter == 1) {
  1700.                         $newObject->setFrontimage($newAsset);
  1701.                     } else {
  1702.                         $advancedImage = new \Pimcore\Model\DataObject\Data\Hotspotimage();
  1703.                         $advancedImage->setImage($newAsset);
  1704.                         array_push($addImages, $advancedImage);
  1705.                     }
  1706.                 }
  1707.                 
  1708.                 if(count($addImages) > 0) {   
  1709.                     $newObject->setImages(new \Pimcore\Model\DataObject\Data\ImageGallery($addImages));
  1710.                 }
  1711.                 
  1712.                 //var_dump($addImages);
  1713.                 
  1714.                 
  1715.                 //set gender
  1716.                 // $gendercat = DataObject::getByPath("/PIM/01 Uhren/03 Kategorien/" . $row["subcategory"]);
  1717.                 // if(!$gendercat) {
  1718.                 //     $newcat = new DataObject\Category(); 
  1719.                 //     $newcat->setKey(\Pimcore\Model\Element\Service::getValidKey($row["subcategory"], 'object'));
  1720.                 //     $newcat->setParentId(101);
  1721.                 //     $newcat->setTitle($row["subcategory"]);
  1722.                 //     $newcat->setPublished(true);
  1723.                 //     $newcat->save();
  1724.                 //     $gendercat = $newcat;
  1725.                 // }
  1726.                 // 
  1727.                 // $cats = [$gendercat];
  1728.                 // $newObject->setCategories($cats);
  1729.                 
  1730.                 $newObject->setPublished(true);
  1731.                 $newObject->save();
  1732.                 
  1733.                 $this->addBrandCollections($brand);
  1734.             }
  1735.             
  1736.             
  1737.             
  1738.             //var_dump($brand);
  1739.         }
  1740.         
  1741.         $conn->close();
  1742.         
  1743.         echo "success";
  1744.         
  1745.         exit;
  1746.         return [];
  1747.     }
  1748.     
  1749.     /**
  1750.      * @Template
  1751.      * @param Request $request
  1752.      * @return array
  1753.      */
  1754.     public function importAction(Request $request) {
  1755.         
  1756.         $servername = "carat-dev.cp4unapazytb.eu-central-1.rds.amazonaws.com";
  1757.         //$servername = "52.29.59.182";
  1758.         $username = "atimis";
  1759.         $password = "gui8kknyzG";
  1760.         $dbname = "carat_100";
  1761.         
  1762.         // Create connection
  1763.         $conn = new \mysqli($servername, $username, $password, $dbname);
  1764.         // Check connection
  1765.         if ($conn->connect_error) {
  1766.           die("Connection failed: " . $conn->connect_error);
  1767.         }
  1768.         
  1769.         
  1770.         $conn->set_charset("utf8mb4");
  1771.         
  1773.         
  1774.         //$sql = "SELECT * FROM Article LIMIT 10";
  1775.         
  1776.         // $sql = "SELECT DISTINCT article.`id`, article.`articleNumber`, article.`brand`, article.`category`, article.`productLine`, article.`subcategory`, item.`price` FROM `Article` article INNER JOIN `Item` item ON item.`article_id` = article.`id` WHERE article.brand = 'A. Lange & Söhne' AND item.`sold` = 0 AND article.`category` = 'Uhr' GROUP BY article.`articleNumber` LIMIT 10;";
  1777.         
  1778.         // $sql ="SELECT DISTINCT article.`id`, article.`articleNumber`, article.`brand`, article.`category`, article.`productLine`, article.`subcategory`, item.`price` FROM `Article` article INNER JOIN `Item` item ON item.`article_id` = article.`id` WHERE item.`sold` = 0 AND article.`category` = 'Uhr' AND item.`webshop` = 'JA' GROUP BY article.`articleNumber` LIMIT 100 OFFSET 1099";
  1779.         
  1780.         // $sql ="SELECT DISTINCT article.`id`, article.`articleNumber`, article.`brand`, article.`category`, article.`productLine`, article.`subcategory`, item.`price` FROM `Article` article INNER JOIN `Item` item ON item.`article_id` = article.`id` WHERE item.`sold` = 0 AND article.`category` = 'Uhr' AND item.`webshop` = 'JA' AND article.`brand` = 'L\'Epee' GROUP BY article.`articleNumber`;";
  1781.         
  1782.         // fix " "
  1783.         
  1784.         // $sql = "SELECT DISTINCT article.`id`, article.`articleNumber`, article.`brand`, article.`category`, article.`productLine`, article.`subcategory`, item.`price` FROM `Article` article INNER JOIN `Item` item ON item.`article_id` = article.`id` WHERE item.`sold` = 0 AND article.`category` = 'Uhr' AND item.`webshop` = 'JA' AND article.`id` = 7450 GROUP BY article.`articleNumber`";
  1785.         
  1786.         // BRAND QUERY
  1787.         
  1788.         // $sql = "SELECT DISTINCT article.`id`, article.`articleNumber`, article.`brand`, article.`category`, article.`productLine`, article.`subcategory`, item.`price`, item.`stockInputDate` FROM `Article` article INNER JOIN `Item` item ON item.`article_id` = article.`id` WHERE item.`sold` = 0 AND article.`category` = 'Uhr' AND item.`webshop` = 'JA' AND article.`brand` LIKE '%Glashütte Original%' GROUP BY article.`articleNumber`";
  1789.         
  1790.         
  1791.         // FINAL QUERY
  1792.         
  1793.         $sql = "SELECT DISTINCT article.`id`, article.`articleNumber`, article.`brand`, article.`category`, article.`productLine`, article.`subcategory`, item.`price`, item.`stockInputDate` FROM `Article` article INNER JOIN `Item` item ON item.`article_id` = article.`id` WHERE item.`sold` = 0 AND article.`category` = 'Uhr' AND item.`webshop` = 'JA' GROUP BY article.`articleNumber`";
  1794.         
  1795.         // $sql = "SELECT DISTINCT article.`id`, article.`articleNumber`, article.`brand`, article.`category`, article.`productLine`, article.`subcategory`, item.`price`, item.`stockInputDate` FROM `Article` article INNER JOIN `Item` item ON item.`article_id` = article.`id` WHERE item.`sold` = 0 AND article.`category` = 'Uhr' AND item.`webshop` = 'JA' AND article.`articleNumber` = 'SLGA025G' GROUP BY article.`articleNumber`";
  1796.         
  1797.         // END FINAL QUERY
  1798.         
  1799.         
  1800.        // $sql = "SELECT * FROM Article WHERE `brand` LIKE '%Lange%' LIMIT 10;";
  1801.         
  1802.         $result = $conn->query($sql);
  1803.         
  1804.         // var_dump($result);
  1805.         // exit;
  1806.         
  1807.         while($row = $result->fetch_assoc()) {
  1808.             $extId = $row["id"];
  1809.             $row = array_map('trim', $row);
  1810.   
  1811.             
  1812.             $key = \Pimcore\Model\Element\Service::getValidKey($row["productLine"] . "-" . $row["articleNumber"], 'object');
  1813.             
  1814.             $prObject = DataObject::getByPath("/PIM/01 Uhren/02 Produkte/" . $row["brand"] . "/" . $key);
  1815.             $checkstring = date('d-m-y');
  1816.             //$checkstring .= 'manual';
  1817.             // $checkstring .= "-zenith";
  1818.             if($prObject) {
  1819.                 // if($prObject->getUpdateinfo() == "291122-try1" || $prObject->getUpdateinfo() == "291122-try2" || $prObject->getUpdateinfo() == "291122-try3" || $prObject->getUpdateinfo() == "291122-try4") {
  1820.                 //     continue;
  1821.                 // }
  1822.                 
  1823.                 
  1824.                 if($prObject->getUpdateInfo() == $checkstring) {
  1825.                     continue;
  1826.                 }
  1827.                 $newObject = $prObject;
  1828.                 $newObject->setupdateinfo($checkstring);
  1829.             } else {
  1830.                 $newObject = new DataObject\Product(); 
  1831.                 $newObject->setKey($key);
  1832.                 $newObject->setupdateinfo($checkstring);
  1833.                 
  1834.                 // get Brand Folder ID
  1835.                 $folder = DataObject::getByPath("/PIM/01 Uhren/02 Produkte/" . $row["brand"]);
  1836.                 
  1837.                 if($row["brand"] == "Jaeger-LeCoultre") {
  1838.                     $folder = DataObject::getById(396);
  1839.                 }
  1840.                 if($row["brand"] == "IWC") {
  1841.                     $folder = DataObject::getById(962);
  1842.                 }
  1843.                 if($row["brand"] == "Nomos Glashütte") {
  1844.                     $folder = DataObject::getById(407);
  1845.                 }
  1846.                 
  1847.                 if($folder == null) {
  1848.                     $folder = new DataObject\Folder(); 
  1849.                     $folder->setKey(\Pimcore\Model\Element\Service::getValidKey($row["brand"], 'folder'));
  1850.                     $folder->setParentId(61);
  1851.                     $folder->save();
  1852.                 }
  1853.                 $parentId = $folder->getId();
  1854.                 
  1855.                 $newObject->setParentId($parentId);
  1856.             }
  1857.             
  1858.             $newObject->setModel($row["productLine"]);
  1859.             $newObject->setArticleNumber($row["articleNumber"]);
  1860.             $newObject->setStockInputDate($row["stockInputDate"]);
  1861.             $newObject->setExtId($row["id"]);
  1862.             $newObject->setPrice($row["price"]);
  1863.             
  1864.             //set brand
  1865.             $brand = DataObject::getByPath("/PIM/01 Uhren/01 Marken/" . $row["brand"]);
  1866.             if($row["brand"] == "Jaeger-LeCoultre") {
  1867.                 $brand = DataObject\Brand::getById(23);
  1868.             }
  1869.             if($row["brand"] == "IWC") {
  1870.                 $brand = DataObject\Brand::getById(82);
  1871.             }
  1872.             if($row["brand"] == "Nomos Glashütte") {
  1873.                 $brand = DataObject\Brand::getById(85);
  1874.             }
  1875.             if($row["brand"] == "Sattler") {
  1876.                 $brand = DataObject\Brand::getById(77);
  1877.             }
  1878.             $newObject->setBrand($brand);
  1879.             
  1880.             //set specifications
  1881.             $items = new DataObject\Fieldcollection();
  1882.             $specsql = "SELECT name, value FROM `ArticleProperty` WHERE `article_id` = $extId;";
  1883.             $specresult = $conn->query($specsql);
  1884.             
  1885.             while($specrow = $specresult->fetch_assoc()) {
  1886.                 $item = new DataObject\Fieldcollection\Data\Specification();
  1887.                 
  1888.                 $item->setSpecKey($specrow["name"]);
  1889.                 $item->setSpecValue($specrow["value"]);
  1890.                 
  1891.                 $items->add($item);
  1892.             }
  1894.             $newObject->setSpecs($items);
  1895.             
  1896.             //set frontimage
  1897.             $imagesql = "SELECT src FROM `Image` WHERE `article_id` = $extId;";
  1898.             $imageresult = $conn->query($imagesql);
  1899.             $articlenumber = str_replace("/", "-", $row["articleNumber"]);
  1900.             $assetfolderpath = "/PIM/01 Uhren/02 Produkte/" . $row["brand"] . "/" . str_replace(["/", '"', "+"], ["-", "", ""], $row["productLine"]) . "-" . $articlenumber;
  1901.             $assetfolderpath = str_replace("+", "", $assetfolderpath);
  1902.             $assetfolderpath = str_replace(".", "", $assetfolderpath);
  1903.             $assetfolderpath = str_replace("|", "", $assetfolderpath);
  1904.             $assetfolderpath = str_replace("ß", "ss", $assetfolderpath);
  1905.             Asset\Service::createFolderByPath($assetfolderpath);
  1906.             
  1907.             $counter = 0;
  1908.             $addImages = [];
  1909.             while($imagerow = $imageresult->fetch_assoc()) {
  1910.                 $counter += 1;
  1911.                 $newAsset = new \Pimcore\Model\Asset\Image();
  1912.                 $newAsset->setFilename($imagerow["src"]);
  1913.                 $newAsset->setData(file_get_contents("https://huebner.carat.solutions/carat/filedownload?fileName=" . $imagerow["src"]));
  1914.                 $newAsset->setParent(\Pimcore\Model\Asset::getByPath($assetfolderpath));
  1915.                 
  1916.                 $checkasset = null;    
  1917.                 $checkasset = \Pimcore\Model\Asset::getByPath($assetfolderpath . "/" . $imagerow["src"]);
  1918.                 
  1919.                 echo "trying to save asset for " . $row["productLine"] . "-" . $row["articleNumber"] . "<br><hr><br>";
  1920.                 
  1921.                 if(strpos($imagerow["src"], "html") == false && strpos($imagerow["src"], "htm") == false && $imagerow["src"] != "7281fa99-3001-41a5-9be1-8dcfe04d997b.") {
  1922.                     if($checkasset) {
  1923.                        $checkasset->setData(file_get_contents("https://huebner.carat.solutions/carat/filedownload?fileName=" . $imagerow["src"]));
  1924.                        $checkasset->save();
  1925.                        $newAsset = $checkasset;
  1926.                     } else {
  1927.                         // var_dump($newAsset->getFullPath());
  1928.                         // var_dump($assetfolderpath);
  1929.                         // var_dump(\Pimcore\Model\Asset::getByPath($assetfolderpath));
  1930.                         // exit;
  1931.                         $newAsset->save();
  1932.                     }
  1933.                     $newAsset->save();
  1934.                 }
  1935.                 if($counter == 1) {
  1936.                     $newObject->setFrontimage($newAsset);
  1937.                 } else {
  1938.                     $advancedImage = new \Pimcore\Model\DataObject\Data\Hotspotimage();
  1939.                     $advancedImage->setImage($newAsset);
  1940.                     array_push($addImages, $advancedImage);
  1941.                 }
  1942.             }
  1943.             
  1944.             if(count($addImages) > 0) {   
  1945.                 $newObject->setImages(new \Pimcore\Model\DataObject\Data\ImageGallery($addImages));
  1946.             }
  1947.             
  1948.             //var_dump($addImages);
  1949.             
  1950.             
  1951.             //set gender
  1952.             $gendercat = DataObject::getByPath("/PIM/01 Uhren/03 Kategorien/" . $row["subcategory"]);
  1953.             if(!$gendercat) {
  1954.                 $newcat = new DataObject\Category(); 
  1955.                 $newcat->setKey(\Pimcore\Model\Element\Service::getValidKey($row["subcategory"], 'object'));
  1956.                 $newcat->setParentId(101);
  1957.                 $newcat->setTitle($row["subcategory"]);
  1958.                 $newcat->setPublished(true);
  1959.                 $newcat->save();
  1960.                 $gendercat = $newcat;
  1961.             }
  1962.             
  1963.             $cats = [$gendercat];
  1964.             $newObject->setCategories($cats);
  1965.             
  1966.             $newObject->setPublished(true);
  1967.             $newObject->save();
  1968.             
  1969.             //var_dump($brand);
  1970.         }
  1971.         
  1972.         $conn->close();
  1973.         
  1974.         
  1975.         exit;
  1976.         return [];
  1977.     }
  1978.     
  1979.     /**
  1980.      * @Template
  1981.      * @param Request $request
  1982.      * @return array
  1983.      */
  1984.     public function categoryAction(Request $request) {
  1985.         
  1986.         
  1987.         return [];
  1988.     }
  1989.     
  1990.     
  1991.     /**
  1992.      * @Template
  1993.      * @param Request $request
  1994.      * @return array
  1995.      */
  1996.     public function homeAction(Request $request) {
  1997.         return [];
  1998.     }
  1999.     
  2000.     /**
  2001.      * @Template
  2002.      * @param Request $request
  2003.      * @return array
  2004.      */
  2005.     public function productOverviewAction(Request $request) {
  2006.         $jewellerycategory = null;
  2007.         $entries = new DataObject\Product\Listing();
  2008.         $filters = [];
  2009.         
  2010.         $page = isset($_GET["page"]) ? $_GET["page"] : null;
  2011.         if($page) {
  2012.             if($page == 1) {
  2013.                 $entries->setLimit(24);
  2014.             }
  2015.             $filters["page"] = $page;
  2016.         }
  2017.         
  2018.         $collection = isset($_GET["collection"]) ? $_GET["collection"] : null;
  2019.         if($collection) {
  2020.             $entries->addConditionParam("categories LIKE ?", "%object|$collection%", "AND");
  2021.             $filters["collection"] = $collection;
  2022.         }
  2023.         
  2024.         $type = isset($_GET["type"]) ? $_GET["type"] : null;
  2025.         if($type) {
  2026.             //$entries->addConditionParam("categories LIKE ?", "%object|$type%", "AND");
  2027.             $entries->setCondition("categories LIKE '%object|$type%'");
  2028.             $filters["type"] = $type;
  2029.         }
  2030.         
  2031.         $brand = isset($_GET["brand"]) ? $_GET["brand"] : null;
  2032.         if($brand) {
  2033.             $entries->addConditionParam("brand__id = ?", "$brand", "AND");
  2034.             $brandobject = DataObject::getById($brand);
  2035.             $brandcollections = $brandobject->getCollections() ? $brandobject->getCollections()->getChildren() : null;
  2036.             
  2037.             $filters["brand"] = $brand;
  2038.             $filters["brandCollections"] = $brandcollections;
  2039.         } 
  2040.         if (!$brand && $collection) {
  2041.             $collectionObject = DataObject::getById($collection);
  2042.                 // var_dump($collection);
  2043.                 // exit;
  2044.             
  2045.             
  2046.             $collectionpath = $collectionObject->getPath();
  2047.             // var_dump($collectionpath);
  2048.             // exit;
  2049.             if(str_contains($collectionpath, "Schmuck")) {
  2050.                 
  2051.                 $entries->addConditionParam("o_path LIKE '%Schmuck%'", "AND");
  2052.                 $jewellerycategory = $collectionObject;
  2053.             } else {
  2054.                 $brandkey = $collectionObject->getParent()->getKey();
  2055.                 
  2056.                 $brands = DataObject\Brand::getByName($brandkey);
  2057.                 $brand = $brands->load();
  2058.                 if(is_array($brand)) {
  2059.                     $brand = $brand[0];
  2060.                     $brandobject = $brand;
  2061.                     $brandcollections = $brandobject->getCollections() ? $brandobject->getCollections()->getChildren() : null;
  2062.                     
  2063.                     $filters["brand"] = $brand->getId();
  2064.                     $filters["brandCollections"] = $brandcollections;
  2065.                 } else {
  2066.                     $brand = null;
  2067.                     $filters["brand"] = null;
  2068.                     $filters["brandCollections"] = null;
  2069.                 }
  2070.             }
  2071.             
  2072.             
  2073.             
  2074.         }
  2075.         
  2076.         if(!isset($filters["brandCollections"])) {
  2077.             $brandCollections = new DataObject\Category\Listing();
  2078.             $brandCollections->setCondition("o_path LIKE '%Kollektionen%'");
  2079.             $brandCollections = $brandCollections->load();
  2080.             $filters["brandCollections"] = $brandCollections;
  2081.         }
  2082.         
  2083.         $casematerial = isset($_GET["casematerial"]) ? $_GET["casematerial"] : null;
  2084.         if($casematerial) {
  2085.             $entries->addFieldCollection("Specification", "specs");
  2086.             $entries->addConditionParam("`Specification~specs`.specKey = 'Gehäusematerial' AND `Specification~specs`.specValue LIKE ?", "%$casematerial%", "AND");
  2087.             $filters["casematerial"] = $casematerial;
  2088.         }
  2089.         
  2090.         $sort = isset($_GET["sort"]) ? $_GET["sort"] : null;
  2091.         if($sort) {
  2092.            switch($sort) {
  2093.                case "actual":
  2094.                    $entries->setOrderKey("stockInputDate");
  2095.                    $entries->setOrder("desc");
  2096.                    break;
  2097.                case "priceasc":
  2098.                    $entries->setOrderKey("price");
  2099.                    $entries->setOrder("asc");
  2100.                    break;
  2101.                 case "pricedesc":
  2102.                     $entries->setOrderKey("price");
  2103.                     $entries->setOrder("desc");
  2104.                     break;
  2105.                 case "name":
  2106.                     $entries->setOrderKey("model");
  2107.                     $entries->setOrder("asc");
  2108.                     break;
  2109.            }
  2110.            $filters["sort"] = $sort;
  2111.         } else {
  2112.             $entries->setOrderKey("stockInputDate");
  2113.             $entries->setOrder("desc");
  2114.             $filters["sort"] = "actual";
  2115.         }
  2116.         
  2117.         
  2118.         
  2119.         //var_dump($entries);
  2120.         
  2121.         $entries = $entries->load();
  2122.         
  2123.        
  2124.         
  2125.         return $this->render('/default/product_overview.html.twig', ["products" => $entries, "filters" => $filters, "jewellerycat" => $jewellerycategory]);
  2126.    
  2127.       
  2128.     }
  2129.     
  2130.     /**
  2131.      * @Template
  2132.      * @param Request $request
  2133.      * @return array
  2134.      */
  2135.     public function brandsOverviewAction(Request $request) {
  2136.         $docpath = $this->document->getFullPath();
  2137.         //var_dump($docpath);
  2138.         
  2139.         if(strpos($docpath, "Uhren") !== false) {
  2140.             $path = "Uhren";
  2141.         } else {
  2142.             $path = "Schmuck";
  2143.         }
  2144.         
  2145.         
  2146.         $entries = new DataObject\Brand\Listing();
  2147.         $entries->setCondition("o_path LIKE '%$path%' ");
  2148.         $entries->setOrderKey("o_key");
  2149.         $entries->setOrder("ASC");
  2150.         $entries->load();
  2151.         
  2152.         return $this->render('/default/brands_overview.html.twig', ["brands" => $entries]);
  2153.     }
  2154.     
  2155.     /**
  2156.      * @Template
  2157.      * @param Request $request
  2158.      * @return array
  2159.      */
  2160.     public function teaserOverviewAction(Request $request) {
  2161.         return [];
  2162.     }
  2163.     
  2164.     /**
  2165.      * @Template
  2166.      * @param Request $request
  2167.      * @return array
  2168.      */
  2169.     public function brandAction(Request $request) {
  2170.         return $this->render('/default/brand.html.twig', []);
  2171.     }
  2172.     
  2173.     /**
  2174.      * @Template
  2175.      * @param Request $request
  2176.      * @return array
  2177.      */
  2178.     public function productAction(Request $request) {
  2179.         $id = str_replace("/", "", $request->get("id"));
  2180.         
  2181.         $product = DataObject::getById($id);
  2182.         
  2183.         return $this->render('/default/product.html.twig', ["product" => $product]);
  2184.     }
  2185.     
  2186.     /**
  2187.      * @Template
  2188.      * @param Request $request
  2189.      * @return array
  2190.      */
  2191.     public function newproductAction(Request $request) {
  2192.         $key = substr($request->get("key"), 1);
  2193.         $number = substr($request->get("number"), 1);
  2194.         $id = substr($request->get("id"), 1);
  2195.         
  2196.         // var_dump($id);
  2197.         // // exit;
  2198.         
  2199.         // $product = DataObject\Product::getByArticleNumber($number);
  2200.         //     
  2201.         // $product = $product->load()[0];
  2202.         
  2203.         $product = DataObject::getById($id);
  2204.         
  2205.         return $this->render('/default/product.html.twig', ["product" => $product]);
  2206.     }
  2207.     
  2208.     /**
  2209.      * @Template
  2210.      * @param Request $request
  2211.      * @return array
  2212.      */
  2213.     public function teaserAction(Request $request) {
  2214.         $id = str_replace("/", "", $request->get("id"));
  2215.         
  2216.         $teaser = DataObject::getById($id);
  2217.         $relation = $teaser->getRelation();
  2218.         
  2219.         if($relation && $relation->getType() == "object" && $relation->getClassName() == "Category") {
  2220.             $collectionObject = $relation;
  2221.             $brandkey = $collectionObject->getParent()->getKey();
  2222.             $brands = DataObject\Brand::getByName($brandkey);
  2223.             $brand = $brands->load();
  2224.             if(is_array($brand)) {
  2225.                 $brand = $brand[0];
  2226.                 return $this->render('/default/teaser.html.twig', ["teaser" => $teaser, "brand" => $brand]);
  2227.             }
  2228.         } else {
  2229.             return $this->render('/default/teaser.html.twig', ["teaser" => $teaser]);
  2230.         }
  2231.             
  2232.         
  2233.         
  2234.        
  2235.     }
  2236.     
  2237.     private static function addBrandCollections($brand) {
  2238.         $collections = new DataObject\Category\Listing();
  2239.         if($brand->getId() == 85) {
  2240.             $collections->setCondition("o_path LIKE :brand", ["brand" => "%Nomos%"]);
  2241.         } else {
  2242.             $collections->setCondition("o_path LIKE :brand", ["brand" => "%".$brand->getName()."%"]);
  2243.         }
  2244.         $collections->load();
  2245.         
  2246.         
  2247.         
  2248.         $brandid = $brand->getId();
  2249.         
  2250.         foreach($collections as $coll) {
  2251.             
  2252.             $key = $coll->getKey();
  2253.             
  2254.             $products = new DataObject\Product\Listing();
  2255.             if($brand->getName() == "A. Lange & Söhne") {
  2256.                 $products->setCondition("UPPER(`o_key`) LIKE \"%$key%\" AND brand__id = $brandid");
  2257.             } elseif($coll->getId() == 281) {
  2258.                 $products->setCondition("`o_key` LIKE \"%$key%\" AND `o_key` NOT LIKE \"%Superocean Heritage%\" AND brand__id = $brandid");
  2259.             } elseif($coll->getId() == 285) {
  2260.                 $products->setCondition("`o_key` LIKE \"% Avi %\" AND brand__id = $brandid");
  2261.             } else {
  2262.                 $products->setCondition("`o_key` LIKE \"%$key%\" AND brand__id = $brandid");
  2263.             }
  2264.             
  2265.             $products->load();
  2266.             foreach($products as $product) {
  2267.                 $cats = $product->getCategories();
  2268.                 if($cats) {
  2269.                     if (!in_array($coll, $cats, true)) {
  2270.                         array_push($cats, $coll);
  2271.                     } 
  2272.                 } else {
  2273.                     array_push($cats, $coll);
  2274.                 }
  2275.                
  2276.                 $product->setCategories($cats);
  2277.                 $product->save();
  2278.             }
  2279.             
  2280.         }
  2281.     }
  2282.     
  2283. }