src/Controller/DefaultController.php line 2020

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") {
  490.             
  491.             // $params = $request->request;
  492.             // var_dump($params);
  493.             // exit;
  494.             // 
  495.             // $recaptchaSecret = '6LecQPMpAAAAAOx6s8_Q9LfbdubBE8G4IOOEwd4-';
  496.             // $recaptchaResponse = $_POST['g-recaptcha-response'];
  497.             // $recaptcha = new \ReCaptcha\ReCaptcha($recaptchaSecret);
  498.             // $recaptchaResult = $recaptcha->verify($recaptchaResponse);
  499.             
  500.             // if (!$recaptchaResult->isSuccess()) {
  501.             if (1 != 0) {
  502.                 echo("not valid");
  503.             } else {
  504.                 // reCaptcha validation succeeded, proceed with form processing
  505.                     
  506.                     
  507.                 $params = $request->request;
  508.                 
  509.                 $productId = $params->get("productId");
  510.                 $product = DataObject::getById($productId);
  511.                 $reference = $product->getarticleNumber();
  512.                 
  513.                 $newsletter = $params->get("newsletter");
  514.                 $iwantto = $params->get("iwantto");
  515.                 $contactoption = $params->get("contactoption");
  516.                 $branch = $params->get("branch");
  517.                 $comment = $params->get("comment");
  518.                 $salutation = $params->get("salutation");
  519.                 $title = $params->get("title");
  520.                 $firstname = $params->get("firstname");
  521.                 $lastname = $params->get("lastname");
  522.                 $email = $params->get("email");
  523.                 $phone = $params->get("phone");
  524.                 $address = $params->get("address");
  525.                 $zipcode = $params->get("zipcode");
  526.                 $state = $params->get("state");
  527.                 $country = $params->get("country");
  528.                 
  529.                 //set branch email
  530.                 switch($branch) {
  531.                     case "Wien":
  532.                         $branchemail = "wien@uhrenhuebner.at";
  533.                         break;
  534.                     case "Linz":
  535.                         $branchemail = "linz@uhrenhuebner.at";
  536.                         break;
  537.                     case "Wels":
  538.                         $branchemail = "wels@uhrenhuebner.at";
  539.                         break;
  540.                 }
  541.                 
  542.                 $inquiry = new DataObject\Inquiry();
  543.                 $inquiry->setKey(\Pimcore\Model\Element\Service::getValidKey('Anfrage-' . $email . date('d-m-y h:i:s'), 'object'));
  544.                 $inquiry->setParentId(1833);
  545.                 $inquiry->setiwantto($iwantto);
  546.                 $inquiry->setcontactoption($contactoption);
  547.                 $inquiry->setbranch($branch);
  548.                 $inquiry->setcomment($comment);
  549.                 $inquiry->setnewsletter($newsletter);
  550.                 $inquiry->setsalutation($salutation);
  551.                 $inquiry->settitle($title);
  552.                 $inquiry->setfirstname($firstname);
  553.                 $inquiry->setlastname($lastname);
  554.                 $inquiry->setemail($email);
  555.                 $inquiry->setphone($phone);
  556.                 $inquiry->setaddress($address);
  557.                 $inquiry->setzipcode($zipcode);
  558.                 $inquiry->setstate($state);
  559.                 $inquiry->setcountry($country);
  560.                 $inquiry->setProduct($product);
  561.                 $inquiry->setPublished(true);
  562.                 $inquiry->save();
  563.                 
  564.                 $salutationtext = $salutation == "Herr" ? "Sehr geehrter Herr" : "Sehr geehrte Frau";
  565.                 $productHtml = $this->getProductHtml($product);
  566.                 
  567.                 $productHtml = " " . $product->getBrand()->getName() . " - " . $product->getModel() . " ";
  568.                 
  569.                 //send User Mail
  570.                 $mail = new \Pimcore\Mail();
  571.                 $mail->addTo($email);
  572.                 //$mail->addTo("a.daum@ontime.at");
  573.                 $mail->setDocument("/Emails/inquiry-user");
  574.                 $mail->setParams([
  575.                     'productHtml' => $productHtml,
  576.                     'salutation' => $salutationtext,
  577.                     'iwantto' => $iwantto,
  578.                     'contactoption' => $contactoption,
  579.                     'branch' => $branch,
  580.                     'comment' => $comment,
  581.                     'title' => $title,
  582.                     'firstname' => $firstname,
  583.                     'lastname' => $lastname,
  584.                     'email' => $email,
  585.                     'phone' => $phone,
  586.                     'address' => $address,
  587.                     'zipcode' => $zipcode,
  588.                     'state' => $state,
  589.                     'country' => $country
  590.                 ]);
  591.                 $mail->send();
  592.                 
  593.                 //send admin Mail
  594.                 $mail2 = new \Pimcore\Mail();
  595.                 // $mail->addTo("wien@uhrenhuebner.at");
  596.                 $mail2->addTo($branchemail);
  597.                 $mail2->addTo("a.daum@ontime.at");
  598.                 $mail2->setDocument("/Emails/inquiry-admin");
  599.                 $mail2->setParams([
  600.                     'brand' => $product->getBrand()->getName(),
  601.                     'model' => $product->getModel(),
  602.                     'reference' => $reference,
  603.                     'deeplink' => '',
  604.                     'productHtml' => $productHtml,
  605.                     'salutation' => $salutationtext,
  606.                     'iwantto' => $iwantto,
  607.                     'contactoption' => $contactoption,
  608.                     'branch' => $branch,
  609.                     'comment' => $comment,
  610.                     'title' => $title,
  611.                     'firstname' => $firstname,
  612.                     'lastname' => $lastname,
  613.                     'email' => $email,
  614.                     'phone' => $phone,
  615.                     'address' => $address,
  616.                     'zipcode' => $zipcode,
  617.                     'state' => $state,
  618.                     'country' => $country
  619.                 ]);
  620.                 $mail2->send();
  621.             }
  622.         }
  623.         return [];
  624.     }
  625.     
  626.     protected static function getProductHtml($product) {
  627.         $html = '<table style="border:none;"';
  628.             $html .= '<tr>';
  629.                 $html .= '<td style="padding: 10px">';
  630.                     $html .= '<img src="https://zeit.at/'. $product->getFrontImage()->getPath() . $product->getFrontImage()->getFilename() .'" alt="' . $product->getModel() . '" style="width:100%; height: auto; max-width:200px";>';
  631.                 $html .= '</td>';
  632.                 $html .= '<td style="padding: 10px 0px;">';
  633.                     $html .= '<p>'.$product->getModel().'</p>';
  634.                 $html .= '</td>';
  635.             $html .= '</tr>';
  636.         $html .= '</table>';
  637.         
  638.         return $html;
  639.     }
  640.     
  641.     /**
  642.      * @Template
  643.      * @param Request $request
  644.      * @return array
  645.      */
  646.     public function loginAction(Request $request) {
  647.         if ($request->getMethod() === "POST") {
  648.             $params = $request->request;
  649.             $email = $params->get("email");
  650.             $password = $params->get("password");
  651.             
  652.             $user = DataObject::getByPath("/CRM/Users/" . $email);
  653.             $passwordhash = $user->getPassword();
  654.             
  655.             if(password_verify($password, $passwordhash)) {
  656.                 //echo "login good";
  657.                 $loggedInUser = $user;
  658.                 $cookie_name = "zeituser";
  659.                 $cookie_value = "4DFGHJ8765FGHJ".$user->getId()."KJHGF45678JHG4343";
  660.                 setcookie($cookie_name, $cookie_value, time() + (86400 * 7), "/"); 
  661.                 return $this->redirect("/");
  662.                 //return $this->render('/default/products.html.twig', ["loggedInUser" => $loggedIdUser]);
  663.                 exit;
  664.             } else {
  665.                 echo "login no good";
  666.             }
  667.             
  668.             exit;
  669.         }
  670.         return [];
  671.     }
  672.     
  673.     /**
  674.      * @Template
  675.      * @param Request $request
  676.      * @return array
  677.      */
  678.     public function registerAction(Request $request) {
  679.         if ($request->getMethod() === "POST") {
  680.             $params = $request->request;
  681.             
  682.             $salutation = $params->get("salutation");
  683.             $title = $params->get("title");
  684.             $firstname = $params->get("firstname");
  685.             $lastname = $params->get("lastname");
  686.             $email = $params->get("email");
  687.             $phone = $params->get("phone");
  688.             $address = $params->get("address");
  689.             $zipcode = $params->get("zipcode");
  690.             $state = $params->get("city");
  691.             $country = $params->get("country");
  692.             
  693.             $password = $params->get("password");
  694.             
  695.             $userobject = new DataObject\Customer();
  696.             $userobject->setKey(\Pimcore\Model\Element\Service::getValidKey($email, 'object'));
  697.             $userobject->setParentId(390);
  698.             
  699.             $userobject->setSalutation($salutation);
  700.             $userobject->setTitle($title);
  701.             $userobject->setFirstName($firstname);
  702.             $userobject->setLastName($lastname);
  703.             $userobject->setAddress($address);
  704.             $userobject->setZipcode($zipcode);
  705.             $userobject->setCity($state);
  706.             $userobject->setPhone($phone);
  707.             $userobject->setEmail($email);
  708.             $userobject->setUsername($email);
  709.             $userobject->setCountry($country);
  710.             
  711.             $userobject->setPassword($password);
  712.             
  713.             $userobject->setPublished(true);
  714.             $userobject->save();
  715.             
  716.             echo "saved";            
  717.             exit;
  718.         } else {
  719.             
  720.         }
  721.     }
  722.     
  723.     /**
  724.      * @Template
  725.      * @param Request $request
  726.      * @return array
  727.      */
  728.     public function logoutAction(Request $request) {
  729.         
  730.         unset($_COOKIE['zeituser']);
  731.         setcookie('zeituser', null, -1, '/'); 
  732.         
  733.         exit;
  734.         return [];
  735.     }
  736.     
  737.     /**
  738.      * @Template
  739.      * @param Request $request
  740.      * @return array
  741.      */
  742.     public function checkoutAction(Request $request) {
  743.         return [];
  744.     }
  745.     
  746.     /**
  747.      * @Template
  748.      * @param Request $request
  749.      * @return array
  750.      */
  751.     public function cartAction(Request $request) {
  752.         return [];
  753.     }
  754.     
  755.     /**
  756.      * @Template
  757.      * @param Request $request
  758.      * @return array
  759.      */
  760.     public function deleteSoldProductsAction(Request $request) {
  761.         $checkstring = date('d-m-y');
  762.             //$checkstring .= 'manual';
  763.         $products = new DataObject\Product\Listing();
  764.         $products->setCondition("updateinfo != '$checkstring' AND (manualInput != '1' OR manualInput IS NULL)");
  765.         $products = $products->load();
  766.         if(count($products) < 100) {
  767.             foreach($products as $product) {
  768.                 $product->delete();
  769.             }
  770.         }
  771.        
  772.         exit;
  773.         return [];
  774.     
  775.     }
  776.     
  777.     /**
  778.      * @Template
  779.      * @param Request $request
  780.      * @return array
  781.      */
  782.     public function createBrandDocumentsAction(Request $request) {
  783.         // $branddocument = \Pimcore\Model\Document::getByPath("/Uhren/Marken/" . "A. Lange - Söhne");
  784.         // $branddocument->setProperty("navigation_name", "text", "A. Lange & Söhne");
  785.         // $branddocument->setController("App\Controller\DefaultController::brandAction");
  786.         // $branddocument->save();
  787.         // exit;
  788.         
  789.         $brands = new DataObject\Brand\Listing();
  790.         $brands->setCondition("o_path LIKE '%Uhren%'");
  791.         $brands = $brands->load();
  792.         
  793.         foreach($brands as $brand) {
  794.             $brandkey = str_replace(["&"], ["-"], $brand->getKey());
  795.             
  796.             $branddocument = \Pimcore\Model\Document::getByPath("/Uhren/Marken/" . $brandkey);
  797.             if($branddocument) {
  798.                 
  799.             } else {
  800.                 $page = new \Pimcore\Model\Document\Page();
  801.                 $page->setKey($brandkey);
  802.                 $page->setTitle($brand->getName());
  803.                 $page->setParentId(2);
  804.                 $page->setProperty("navigation_name", "text", $brand->getName());
  805.                 $page->setController("App\Controller\DefaultController::brandAction");
  806.                 $page->setPublished(true);
  807.                 $page->save();
  808.             }
  809.         }
  810.         
  811.         exit;
  812.         return [];
  813.     }
  814.     
  815.     /**
  816.      * @Template
  817.      * @param Request $request
  818.      * @return array
  819.      */
  820.     public function createBrandFolderAction(Request $request) {
  821.         
  822.         $brands = new DataObject\Brand\Listing();
  823.         $brands->setCondition("o_path LIKE '%Uhren%'");
  824.         $brands = $brands->load();
  825.         
  826.         // $brands = $brands[0];
  827.         
  828.         //var_dump($brands);
  829.         
  830.         // foreach($brands as $brand) {
  831.         //     $brandkey = str_replace(["&"], ["-"], $brand->getKey());
  832.         //     
  833.         //     $newObject = new DataObject\Folder(); 
  834.         //     $newObject->setKey(\Pimcore\Model\Element\Service::getValidKey($brandkey, 'folder'));
  835.         //     $newObject->setParentId(61);
  836.         //     $newObject->save();
  837.         //     
  838.         // }
  839.         
  840.         // $brandkey = "Jaeger-LeCoultre";
  841.         // $folder = DataObject::getByPath("/PIM/01 Uhren/02 Produkte/" . $brandkey);
  842.         // $parentId = $folder->getId();
  843.         // var_dump($folder->getId());
  844.         
  845.         exit;
  846.         return [];
  847.     }
  848.     
  849.     /**
  850.      * @Template
  851.      * @param Request $request
  852.      * @return array
  853.      */
  854.     public function addcollectionAction(Request $request) {
  855.         
  856.         
  857.         $brands = new DataObject\Brand\Listing();
  858.         $brands->setCondition("o_path LIKE '%Uhren%'");
  859.         $brands = $brands->load();
  860.         
  861.         //$brand = DataObject\Brand::getById(85);
  862.             
  863.         foreach($brands as $brand) {
  864.             // $collections = new DataObject\Category\Listing();
  865.             // if($brand->getId() == 85) {
  866.             //     $collections->setCondition("o_path LIKE :brand", ["brand" => "%Nomos%"]);
  867.             // } else {
  868.             //     $collections->setCondition("o_path LIKE :brand", ["brand" => "%".$brand->getName()."%"]);
  869.             // }
  870.             // $collections->load();
  871.             
  872.             $collections = $brand->getCollections();
  873.             
  874.             
  875.             if($collections) {
  876.                 $collections = $collections->getChildren();
  877.             } else {
  878.                 continue;
  879.             }
  880.             
  881.             
  882.             $brandid = $brand->getId();
  883.             
  884.             foreach($collections as $coll) {
  885.                 
  886.                 $key = $coll->getKey();
  887.                 
  888.                 $products = new DataObject\Product\Listing();
  889.                 if($brand->getName() == "A. Lange & Söhne") {
  890.                     $products->setCondition("UPPER(`o_key`) LIKE \"%$key%\" AND brand__id = $brandid");
  891.                 } elseif($coll->getId() == 281) {
  892.                     $products->setCondition("`o_key` LIKE \"%$key%\" AND `o_key` NOT LIKE \"%Superocean Heritage%\" AND brand__id = $brandid");
  893.                 } elseif($coll->getId() == 285) {
  894.                     $products->setCondition("`o_key` LIKE \"% Avi %\" AND brand__id = $brandid");
  895.                 } else {
  896.                     $products->setCondition("`o_key` LIKE \"%$key%\" AND brand__id = $brandid");
  897.                 }
  898.                 
  899.                 $products->load();
  900.                 foreach($products as $product) {
  901.                     $cats = $product->getCategories();
  902.                     if($cats) {
  903.                         if (!in_array($coll, $cats, true)) {
  904.                             array_push($cats, $coll);
  905.                         } 
  906.                     } else {
  907.                         array_push($cats, $coll);
  908.                     }
  909.                    
  910.                     $product->setCategories($cats);
  911.                     $product->save();
  912.                 }
  913.                 
  914.             }
  915.             echo "Brand: " . $brand->getName() . " done <br><hr><br>";
  916.         }
  917.             exit;
  918.         
  919.         foreach($brands as $brand) {
  920.             $collections = new DataObject\Category\Listing();
  921.             $collections->setCondition("o_path LIKE :brand", ["brand" => "%".$brand->getName()."%"]);
  922.             $collections->load();
  923.         
  924.             foreach($collections as $coll) {
  925.                 $key = $coll->getKey();
  926.                 $products = new DataObject\Product\Listing();
  927.                 if($brand->getName() == "A. Lange & Söhne") {
  928.                     $products->setCondition("UPPER(`o_key`) LIKE '%$key%'");
  929.                 } else {
  930.                     $products->setCondition("`o_key` LIKE '%$key%'");
  931.                 }
  932.                 $products->load();
  933.                 foreach($products as $product) {
  934.                     $cats = $product->getCategories();
  935.                     if (!in_array($coll, $cats)) {
  936.                         array_push($cats, $coll);
  937.                     }
  938.                     $product->setCategories($cats);
  939.                     $product->save();
  940.                 }
  941.                 
  942.             }
  943.             echo "Brand: " . $brand->getName() . " done <br><hr><br>";
  944.         }
  945.         
  946.         
  947.     
  948.         exit;
  949.         return [];
  950.     }
  951.     
  952.     /**
  953.      * @Template
  954.      * @param Request $request
  955.      * @return array
  956.      */
  957.     public function testAction(Request $request) {
  958.         
  959.         ini_set('memory_limit', '1024M');
  960.         $listing = new \Pimcore\Model\Document\Listing();
  961.         $listing = $listing->load();
  962.         
  963.         foreach($listing as $doc) {
  964.             $key = $doc->getKey();
  965.             $key = str_replace(" ", "-", $key);
  966.             $doc->setKey($key);
  967.             $doc->save();
  968.         }
  969.         
  970.         exit;
  971.        //  $brands = new DataObject\Product\Listing();
  972.        // 
  973.        //  
  974.        //  $brands->setCondition("o_path LIKE '%Uhren%' AND o_modificationDate > 1667284831");
  975.        //  $brands->load();
  976.        //  
  977.        //  var_dump(count($brands));
  978.         
  979.         exit;
  980.         
  981.         return [];
  982.     }
  983.     
  984.     /**
  985.      * @Template
  986.      * @param Request $request
  987.      * @return array
  988.      */
  989.     public function importSingleProductAction(Request $request) {
  990.         $searchstring = str_replace("/", "", $request->get("searchstring"));
  991.         
  992.         
  993.         $servername = "carat-dev.cp4unapazytb.eu-central-1.rds.amazonaws.com";
  994.         $username = "atimis";
  995.         $password = "gui8kknyzG";
  996.         $dbname = "carat_100";
  997.         
  998.         // Create connection
  999.         $conn = new \mysqli($servername, $username, $password, $dbname);
  1000.         // Check connection
  1001.         if ($conn->connect_error) {
  1002.           die("Connection failed: " . $conn->connect_error);
  1003.         }
  1004.         
  1005.         $conn->set_charset("utf8mb4");
  1006.         
  1007.         $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`;";
  1008.         
  1009.         $result = $conn->query($sql);
  1010.         
  1011.         while($row = $result->fetch_assoc()) {
  1012.             $extId = $row["id"];
  1013.             
  1014.             $prObject = DataObject::getByPath("/PIM/01 Uhren/02 Produkte/" . $row["brand"] . "/" . $row["productLine"] . "-" . $row["articleNumber"]);
  1015.             
  1016.             if($prObject) {
  1017.                 $newObject = $prObject;
  1018.             } else {
  1019.                 $newObject = new DataObject\Product(); 
  1020.                 $newObject->setKey(\Pimcore\Model\Element\Service::getValidKey($row["productLine"] . "-" . $row["articleNumber"], 'object'));
  1021.                 
  1022.                 $folder = DataObject::getByPath("/PIM/01 Uhren/02 Produkte/" . $row["brand"]);
  1023.                 if($folder == null) {
  1024.                     $folder = new DataObject\Folder(); 
  1025.                     $folder->setKey(\Pimcore\Model\Element\Service::getValidKey($row["brand"], 'folder'));
  1026.                     $folder->setParentId(61);
  1027.                     $folder->save();
  1028.                 }
  1029.                 $parentId = $folder->getId();
  1030.                 
  1031.                 $newObject->setParentId($parentId);
  1032.             }
  1033.             
  1034.             $newObject->setModel($row["productLine"]);
  1035.             $newObject->setArticleNumber($row["articleNumber"]);
  1036.             $newObject->setExtId($row["id"]);
  1037.             $newObject->setPrice($row["price"]);
  1038.             //set brand
  1039.             $brand = $object = DataObject::getByPath("/PIM/01 Uhren/01 Marken/" . $row["brand"]);
  1040.             $newObject->setBrand($brand);
  1041.             
  1042.             //set specifications
  1043.             $items = new DataObject\Fieldcollection();
  1044.             $specsql = "SELECT name, value FROM `ArticleProperty` WHERE `article_id` = $extId;";
  1045.             $specresult = $conn->query($specsql);
  1046.             
  1047.             while($specrow = $specresult->fetch_assoc()) {
  1048.                 $item = new DataObject\Fieldcollection\Data\Specification();
  1049.                 
  1050.                 $item->setSpecKey($specrow["name"]);
  1051.                 $item->setSpecValue($specrow["value"]);
  1052.                 
  1053.                 $items->add($item);
  1054.             }
  1055.         
  1056.             $newObject->setSpecs($items);
  1057.             
  1058.             //set frontimage
  1059.             $imagesql = "SELECT src FROM `Image` WHERE `article_id` = $extId;";
  1060.             $imageresult = $conn->query($imagesql);
  1061.             $assetfolderpath = "/PIM/01 Uhren/02 Produkte/" . $row["brand"] . "/" . str_replace("/", "-", $row["productLine"]) . "-" . $row["articleNumber"];
  1062.             Asset\Service::createFolderByPath($assetfolderpath);
  1063.             
  1064.             $counter = 0;
  1065.             $addImages = [];
  1066.             while($imagerow = $imageresult->fetch_assoc()) {
  1067.                 $counter += 1;
  1068.                 $newAsset = new \Pimcore\Model\Asset\Image();
  1069.                 $newAsset->setFilename($imagerow["src"]);
  1070.                 $newAsset->setData(file_get_contents("https://huebner.carat.solutions/carat/filedownload?fileName=" . $imagerow["src"]));
  1071.                 $newAsset->setParent(\Pimcore\Model\Asset::getByPath($assetfolderpath));
  1072.                 
  1073.                 $checkasset = null;    
  1074.                 $checkasset = \Pimcore\Model\Asset::getByPath($assetfolderpath . "/" . $imagerow["src"]);
  1075.                 
  1076.                 if($checkasset) {
  1077.                     $newAsset = $checkasset;
  1078.                 } else {
  1079.                     $newAsset->save();
  1080.                 }
  1081.                 
  1082.                 
  1083.                 if($counter == 1) {
  1084.                     $newObject->setFrontimage($newAsset);
  1085.                 } else {
  1086.                     array_push($addImages, $newAsset);
  1087.                 }
  1088.             }
  1089.             
  1090.             if(count($addImages) > 0) {
  1091.                 $newObject->setImages(new \Pimcore\Model\DataObject\Data\ImageGallery($addImages));
  1092.             }
  1093.             
  1094.             
  1095.             //set gender
  1096.             $gendercat = DataObject::getByPath("/PIM/01 Uhren/03 Kategorien/" . $row["subcategory"]);
  1097.             $cats = [$gendercat];
  1098.             $newObject->setCategories($cats);
  1099.             
  1100.             $newObject->setPublished(true);
  1101.             $newObject->save();
  1102.             
  1103.             echo "Produkt: " . $row["productLine"] . "-" . $row["articleNumber"] . " erfolgreich importiert";
  1104.             
  1105.             //var_dump($brand);
  1106.         }
  1107.         
  1108.         $conn->close();
  1109.         exit;
  1110.     }
  1111.     
  1112.     /**
  1113.      * @Template
  1114.      * @param Request $request
  1115.      * @return array
  1116.      */
  1117.     public function addJewelleryCatsAction(Request $request) {
  1118.         $products = new DataObject\Product\Listing();
  1119.         $products->setCondition("o_path LIKE '%Schmuck%'");
  1120.         $products->load();
  1121.         $i = 0;
  1122.         
  1123.         foreach($products as $product) {
  1124.             $catstring = $product->getCatstring();
  1125.             $categories = new DataObject\Category\Listing();
  1126.             $categories->setCondition("o_path LIKE '%02 Schmuck/03 Kategorien%' AND caratvalues LIKE '%$catstring%'");
  1127.             $categories->load();
  1128.             foreach($categories as $cat) {
  1129.                 $cats = $product->getCategories();
  1130.                 if(is_array($cats)) {
  1131.                     if(!in_array($cat, $cats)) {
  1132.                         $cats[] = $cat;
  1133.                     }
  1134.                 } else {
  1135.                     if(!in_array($cat, $cats)) {
  1136.                         $cats = [];
  1137.                         $cats[] = $cat;
  1138.                     }
  1139.                 }
  1140.                 $product->setCategories($cats);
  1141.                 $product->save();
  1142.                 $i++;
  1143.             }
  1144.         }
  1145.         
  1146.         $brands = new DataObject\Brand\Listing();
  1147.         $brands->setCondition("o_path LIKE '%Schmuck%'");
  1148.         $brands->load();
  1149.         foreach($brands as $brand) {
  1150.             $collections = $brand->getCollections();
  1151.             $collections = $collections->getChildren();
  1152.             $brandid = $brand->getId();
  1153.             
  1154.             foreach($collections as $coll) {
  1155.                 $key = $coll->getKey();
  1156.                 
  1157.                 $products = new DataObject\Product\Listing();
  1158.                 $products->setCondition("`o_key` LIKE \"%$key%\" AND brand__id = $brandid AND o_path LIKE '%Schmuck%'");
  1159.                 
  1160.                 $products->load();
  1161.                 foreach($products as $product) {
  1162.                     $cats = $product->getCategories();
  1163.                     if($cats) {
  1164.                         if (!in_array($coll, $cats, true)) {
  1165.                             array_push($cats, $coll);
  1166.                         } 
  1167.                     } else {
  1168.                         array_push($cats, $coll);
  1169.                     }
  1170.                    
  1171.                     $product->setCategories($cats);
  1172.                     $product->save();
  1173.                 }
  1174.             }
  1175.         }
  1176.         
  1177.         //echo "done $i products";
  1178.         
  1179.         exit;
  1180.         return [];
  1181.     }
  1182.     
  1183.     /**
  1184.      * @Template
  1185.      * @param Request $request
  1186.      * @return array
  1187.      */
  1188.     public function importSingleJewelleryProductAction(Request $request) {
  1189.         $servername = "carat-dev.cp4unapazytb.eu-central-1.rds.amazonaws.com";
  1190.             $username = "atimis";
  1191.             $password = "gui8kknyzG";
  1192.             $dbname = "carat_100";
  1193.             
  1194.             // Create connection
  1195.             $conn = new \mysqli($servername, $username, $password, $dbname);
  1196.             // Check connection
  1197.             if ($conn->connect_error) {
  1198.               die("Connection failed: " . $conn->connect_error);
  1199.             }
  1200.             
  1201.             $conn->set_charset("utf8mb4");
  1202.             
  1203.         
  1204.             
  1205.             
  1206.             
  1207.             
  1208.             // FINAL QUERY
  1209.             
  1210.             $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'";
  1211.             
  1212.             // END FINAL QUERY
  1213.             
  1214.             
  1215.            // $sql = "SELECT * FROM Article WHERE `brand` LIKE '%Lange%' LIMIT 10;";
  1216.             
  1217.             $result = $conn->query($sql);
  1218.             
  1219.             // var_dump($result);
  1220.             // exit;
  1221.             
  1222.             while($row = $result->fetch_assoc()) {
  1223.                 $extId = $row["id"];
  1224.                 
  1225.                 $row = array_map('trim', $row);
  1226.                 
  1227.                 $key = \Pimcore\Model\Element\Service::getValidKey($row["productLine"] . "-" . $row["articleNumber"], 'object');
  1228.                 
  1229.                 $prObject = DataObject::getByPath("/PIM/02 Schmuck/02 Produkte/" . $row["brand"] . "/" . $key);
  1230.                 
  1231.                 
  1232.                 
  1233.                 if($prObject) {
  1234.                     $checkstring = date('d-m-y');
  1235.                     //$checkstring .= 'manual';
  1236.                     if($prObject->getUpdateInfo() == $checkstring) {
  1237.                         continue;
  1238.                     }
  1239.                     $newObject = $prObject;
  1240.                     $newObject->setupdateinfo($checkstring);
  1241.                 } else {
  1242.                     $newObject = new DataObject\Product(); 
  1243.                     $newObject->setKey($key);
  1244.                     $newObject->setupdateinfo($checkstring);
  1245.                     
  1246.                     // get Brand Folder ID
  1247.                     $folder = DataObject::getByPath("/PIM/02 Schmuck/02 Produkte/" . $row["brand"]);
  1248.                     
  1249.                     // if($row["brand"] == "Jaeger-LeCoultre") {
  1250.                     //     $folder = DataObject::getById(396);
  1251.                     // }
  1252.                     if($row["brand"] == "FOPE") {
  1253.                         $folder = DataObject::getById(3160);
  1254.                     }
  1255.                     if($row["brand"] == "Aldusblatt Herzog Loibner") {
  1256.                         $folder = DataObject::getById(1712);
  1257.                     }
  1258.                     
  1259.                     if($folder == null) {
  1260.                         $folder = new DataObject\Folder(); 
  1261.                         $folder->setKey(\Pimcore\Model\Element\Service::getValidKey($row["brand"], 'folder'));
  1262.                         $folder->setParentId(64);
  1263.                         $folder->save();
  1264.                     }
  1265.                     $parentId = $folder->getId();
  1266.                     
  1267.                     $newObject->setParentId($parentId);
  1268.                 }
  1269.                 
  1270.                 $newObject->setModel($row["productLine"]);
  1271.                 $newObject->setArticleNumber($row["articleNumber"]);
  1272.                 $newObject->setStockInputDate($row["stockInputDate"]);
  1273.                 $newObject->setExtId($row["id"]);
  1274.                 $newObject->setPrice($row["price"]);
  1275.                 $newObject->setCatstring($row["subcategory"]);
  1276.                 
  1277.                 //set brand
  1278.                 $brand = DataObject::getByPath("/PIM/02 Schmuck/01 Marken/" . $row["brand"]);
  1279.                 // if($row["brand"] == "Jaeger-LeCoultre") {
  1280.                 //     $brand = DataObject\Brand::getById(23);
  1281.                 // }
  1282.                 if($row["brand"] == "FOPE") {
  1283.                     $brand = DataObject::getById(3201);
  1284.                 }
  1285.                 if($row["brand"] == "Aldusblatt Herzog Loibner") {
  1286.                     $brand = DataObject::getById(99);
  1287.                 }
  1288.                 $newObject->setBrand($brand);
  1289.                 
  1290.                 //set specifications
  1291.                 $items = new DataObject\Fieldcollection();
  1292.                 $specsql = "SELECT carat, label, number, description FROM `GemItem` WHERE `article_id` = $extId;";
  1293.                 $specresult = $conn->query($specsql);
  1294.                 
  1295.                 while($specrow = $specresult->fetch_assoc()) {
  1296.                     $item = new DataObject\Fieldcollection\Data\JewellerySpecification();
  1297.                     
  1298.                     $item->setSpecKey($specrow["label"]);
  1299.                     $item->setSpecValue($specrow["carat"]);
  1300.                     $item->setSpecCount($specrow["number"]);
  1301.                     $item->setSpecDescription($specrow["description"]);                    
  1302.                     $items->add($item);
  1303.                 }
  1304.                 
  1305.                 $specsql = "SELECT name, value FROM `ArticleProperty` WHERE `article_id` = $extId;";
  1306.                 $specresult = $conn->query($specsql);
  1307.                 
  1308.                 while($specrow = $specresult->fetch_assoc()) {
  1309.                     $item = new DataObject\Fieldcollection\Data\JewellerySpecification();
  1310.                     
  1311.                     $item->setSpecKey($specrow["name"]);
  1312.                     $item->setSpecValue($specrow["value"]);
  1313.                     $item->setSpecCount(null);
  1314.                     $item->setSpecDescription(null);                    
  1315.                     $items->add($item);
  1316.                 }
  1317.         
  1318.                 $newObject->setSpecs($items);
  1319.                 
  1320.                 //set frontimage
  1321.                 $imagesql = "SELECT src FROM `Image` WHERE `article_id` = $extId;";
  1322.                 $imageresult = $conn->query($imagesql);
  1323.                 $articlenumber = str_replace("/", "-", $row["articleNumber"]);
  1324.                 $assetfolderpath = "/PIM/02 Schmuck/02 Produkte/" . $row["brand"] . "/" . str_replace(["/", '"', "+"], ["-", "", ""], $row["productLine"]) . "-" . $articlenumber;
  1325.                 $assetfolderpath = str_replace("+", "", $assetfolderpath);
  1326.                 $assetfolderpath = str_replace(".", "", $assetfolderpath);
  1327.                 $assetfolderpath = str_replace("|", "", $assetfolderpath);
  1328.                 $assetfolderpath = str_replace("ß", "ss", $assetfolderpath);
  1329.                 Asset\Service::createFolderByPath($assetfolderpath);
  1330.                 
  1331.                 $counter = 0;
  1332.                 $addImages = [];
  1333.                 while($imagerow = $imageresult->fetch_assoc()) {
  1334.                     $counter += 1;
  1335.                     $newAsset = new \Pimcore\Model\Asset\Image();
  1336.                     $newAsset->setFilename($imagerow["src"]);
  1337.                     $newAsset->setData(file_get_contents("https://huebner.carat.solutions/carat/filedownload?fileName=" . $imagerow["src"]));
  1338.                     $newAsset->setParent(\Pimcore\Model\Asset::getByPath($assetfolderpath));
  1339.                     
  1340.                     $checkasset = null;    
  1341.                     $checkasset = \Pimcore\Model\Asset::getByPath($assetfolderpath . "/" . $imagerow["src"]);
  1342.                     
  1343.                     echo "trying to save asset for " . $row["productLine"] . "-" . $row["articleNumber"] . "<br><hr><br>";
  1344.                     
  1345.                     if(strpos($imagerow["src"], "html") == false && strpos($imagerow["src"], "avif") == false && $imagerow["src"] != "7281fa99-3001-41a5-9be1-8dcfe04d997b.") {
  1346.                         if($checkasset) {
  1347.                             $checkasset->delete();
  1348.                             $newAsset->save();
  1349.                         } else {
  1350.                             $newAsset->save();
  1351.                         }
  1352.                     }
  1353.                     if($counter == 1) {
  1354.                         $newObject->setFrontimage($newAsset);
  1355.                     } else {
  1356.                         $advancedImage = new \Pimcore\Model\DataObject\Data\Hotspotimage();
  1357.                         $advancedImage->setImage($newAsset);
  1358.                         array_push($addImages, $advancedImage);
  1359.                     }
  1360.                 }
  1361.                 
  1362.                 if(count($addImages) > 0) {   
  1363.                     $newObject->setImages(new \Pimcore\Model\DataObject\Data\ImageGallery($addImages));
  1364.                 }
  1365.                 
  1366.                 //var_dump($addImages);
  1367.                 
  1368.                 
  1369.                 //set gender
  1370.                 // $gendercat = DataObject::getByPath("/PIM/01 Uhren/03 Kategorien/" . $row["subcategory"]);
  1371.                 // if(!$gendercat) {
  1372.                 //     $newcat = new DataObject\Category(); 
  1373.                 //     $newcat->setKey(\Pimcore\Model\Element\Service::getValidKey($row["subcategory"], 'object'));
  1374.                 //     $newcat->setParentId(101);
  1375.                 //     $newcat->setTitle($row["subcategory"]);
  1376.                 //     $newcat->setPublished(true);
  1377.                 //     $newcat->save();
  1378.                 //     $gendercat = $newcat;
  1379.                 // }
  1380.                 
  1381.                 // $cats = [$gendercat];
  1382.                 // $newObject->setCategories($cats);
  1383.                 
  1384.                 $newObject->setPublished(true);
  1385.                 $newObject->save();
  1386.                 
  1387.                 //var_dump($brand);
  1388.             }
  1389.             
  1390.             $conn->close();
  1391.             
  1392.             
  1393.             exit;
  1394.             return [];
  1395.     }
  1396.     
  1397.     /**
  1398.      * @Template
  1399.      * @param Request $request
  1400.      * @return array
  1401.      */
  1402.     public function updateProductAction(Request $request) {
  1403.         
  1404.         $id = str_replace("/", "", $request->get("caratid"));
  1405.         $pimId = str_replace("/", "", $request->get("pimid"));
  1406.         
  1407.         $servername = "carat-dev.cp4unapazytb.eu-central-1.rds.amazonaws.com";
  1408.         //$servername = "52.29.59.182";
  1409.         $username = "atimis";
  1410.         $password = "gui8kknyzG";
  1411.         $dbname = "carat_100";
  1412.         
  1413.         // Create connection
  1414.         $conn = new \mysqli($servername, $username, $password, $dbname);
  1415.         // Check connection
  1416.         if ($conn->connect_error) {
  1417.           die("Connection failed: " . $conn->connect_error);
  1418.         }
  1419.         
  1420.         
  1421.         $conn->set_charset("utf8mb4");
  1422.         
  1423.         // FINAL QUERY
  1424.         
  1425.         $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`";
  1426.         
  1427.         // END FINAL QUERY
  1428.         
  1429.         
  1430.        // $sql = "SELECT * FROM Article WHERE `brand` LIKE '%Lange%' LIMIT 10;";
  1431.         
  1432.         $result = $conn->query($sql);
  1433.         
  1434.         // var_dump($result);
  1435.         // exit;
  1436.         
  1437.         while($row = $result->fetch_assoc()) {
  1438.             $extId = $row["id"];
  1439.             $row = array_map('trim', $row);
  1440.             
  1441.             $watchjewel = $row["category"];
  1442.             
  1443.             if($watchjewel == "Uhr") {
  1444.                 $key = \Pimcore\Model\Element\Service::getValidKey($row["productLine"] . "-" . $row["articleNumber"], 'object');
  1445.                 
  1446.                 $prObject = DataObject::getById($pimId);
  1447.                 $checkstring = date('d-m-y');
  1448.             //$checkstring .= 'manual';
  1449.                 // $checkstring .= "-zenith";
  1450.                 if($prObject) {
  1451.                     // if($prObject->getUpdateinfo() == "291122-try1" || $prObject->getUpdateinfo() == "291122-try2" || $prObject->getUpdateinfo() == "291122-try3" || $prObject->getUpdateinfo() == "291122-try4") {
  1452.                     //     continue;
  1453.                     // }
  1454.                     
  1455.                     
  1456.                     if($prObject->getUpdateInfo() == $checkstring) {
  1457.                         //continue;
  1458.                     }
  1459.                     $newObject = $prObject;
  1460.                     $newObject->setupdateinfo($checkstring);
  1461.                 } else {
  1462.                     $newObject = new DataObject\Product(); 
  1463.                     $newObject->setKey($key);
  1464.                     $newObject->setupdateinfo($checkstring);
  1465.                     
  1466.                     // get Brand Folder ID
  1467.                     $folder = DataObject::getByPath("/PIM/01 Uhren/02 Produkte/" . $row["brand"]);
  1468.                     
  1469.                     if($row["brand"] == "Jaeger-LeCoultre") {
  1470.                         $folder = DataObject::getById(396);
  1471.                     }
  1472.                     if($row["brand"] == "IWC") {
  1473.                         $folder = DataObject::getById(962);
  1474.                     }
  1475.                     if($row["brand"] == "Nomos Glashütte") {
  1476.                         $folder = DataObject::getById(407);
  1477.                     }
  1478.                     
  1479.                     if($folder == null) {
  1480.                         $folder = new DataObject\Folder(); 
  1481.                         $folder->setKey(\Pimcore\Model\Element\Service::getValidKey($row["brand"], 'folder'));
  1482.                         $folder->setParentId(61);
  1483.                         $folder->save();
  1484.                     }
  1485.                     $parentId = $folder->getId();
  1486.                     
  1487.                     $newObject->setParentId($parentId);
  1488.                 }
  1489.                 
  1490.                 $newObject->setModel($row["productLine"]);
  1491.                 $newObject->setArticleNumber($row["articleNumber"]);
  1492.                 $newObject->setStockInputDate($row["stockInputDate"]);
  1493.                 $newObject->setExtId($row["id"]);
  1494.                 $newObject->setPrice($row["price"]);
  1495.                 
  1496.                 //set brand
  1497.                 $brand = DataObject::getByPath("/PIM/01 Uhren/01 Marken/" . $row["brand"]);
  1498.                 if($row["brand"] == "Jaeger-LeCoultre") {
  1499.                     $brand = DataObject\Brand::getById(23);
  1500.                 }
  1501.                 if($row["brand"] == "IWC") {
  1502.                     $brand = DataObject\Brand::getById(82);
  1503.                 }
  1504.                 if($row["brand"] == "Nomos Glashütte") {
  1505.                     $brand = DataObject\Brand::getById(85);
  1506.                 }
  1507.                 if($row["brand"] == "Sattler") {
  1508.                     $brand = DataObject\Brand::getById(77);
  1509.                 }
  1510.                 $newObject->setBrand($brand);
  1511.                 
  1512.                 //set specifications
  1513.                 $items = new DataObject\Fieldcollection();
  1514.                 $specsql = "SELECT name, value FROM `ArticleProperty` WHERE `article_id` = $extId;";
  1515.                 $specresult = $conn->query($specsql);
  1516.                 
  1517.                 while($specrow = $specresult->fetch_assoc()) {
  1518.                     $item = new DataObject\Fieldcollection\Data\Specification();
  1519.                     
  1520.                     $item->setSpecKey($specrow["name"]);
  1521.                     $item->setSpecValue($specrow["value"]);
  1522.                     
  1523.                     $items->add($item);
  1524.                 }
  1525.                 
  1526.                 $newObject->setSpecs($items);
  1527.                 
  1528.                 //set frontimage
  1529.                 $imagesql = "SELECT src FROM `Image` WHERE `article_id` = $extId;";
  1530.                 $imageresult = $conn->query($imagesql);
  1531.                 $articlenumber = str_replace("/", "-", $row["articleNumber"]);
  1532.                 $assetfolderpath = "/PIM/01 Uhren/02 Produkte/" . $row["brand"] . "/" . str_replace(["/", '"', "+"], ["-", "", ""], $row["productLine"]) . "-" . $articlenumber;
  1533.                 $assetfolderpath = str_replace("+", "", $assetfolderpath);
  1534.                 $assetfolderpath = str_replace(".", "", $assetfolderpath);
  1535.                 $assetfolderpath = str_replace("|", "", $assetfolderpath);
  1536.                 $assetfolderpath = str_replace("ß", "ss", $assetfolderpath);
  1537.                 Asset\Service::createFolderByPath($assetfolderpath);
  1538.                 
  1539.                 $counter = 0;
  1540.                 $addImages = [];
  1541.                 while($imagerow = $imageresult->fetch_assoc()) {
  1542.                     $counter += 1;
  1543.                     $newAsset = new \Pimcore\Model\Asset\Image();
  1544.                     $newAsset->setFilename($imagerow["src"]);
  1545.                     $newAsset->setData(file_get_contents("https://huebner.carat.solutions/carat/filedownload?fileName=" . $imagerow["src"]));
  1546.                     $newAsset->setParent(\Pimcore\Model\Asset::getByPath($assetfolderpath));
  1547.                     
  1548.                     $checkasset = null;    
  1549.                     $checkasset = \Pimcore\Model\Asset::getByPath($assetfolderpath . "/" . $imagerow["src"]);
  1550.                     
  1551.                     //echo "trying to save asset for " . $row["productLine"] . "-" . $row["articleNumber"] . "<br><hr><br>";
  1552.                     
  1553.                     if(strpos($imagerow["src"], "html") == false && strpos($imagerow["src"], "avif") == false && $imagerow["src"] != "7281fa99-3001-41a5-9be1-8dcfe04d997b.") {
  1554.                         if($checkasset) {
  1555.                            $checkasset->delete();
  1556.                            $newAsset->save();
  1557.                         } else {
  1558.                             // var_dump($newAsset->getFullPath());
  1559.                             // var_dump($assetfolderpath);
  1560.                             // var_dump(\Pimcore\Model\Asset::getByPath($assetfolderpath));
  1561.                             // exit;
  1562.                             $newAsset->save();
  1563.                         }
  1564.                     }
  1565.                     if($counter == 1) {
  1566.                         $newObject->setFrontimage($newAsset);
  1567.                     } else {
  1568.                         $advancedImage = new \Pimcore\Model\DataObject\Data\Hotspotimage();
  1569.                         $advancedImage->setImage($newAsset);
  1570.                         array_push($addImages, $advancedImage);
  1571.                     }
  1572.                 }
  1573.                 
  1574.                 if(count($addImages) > 0) {   
  1575.                     $newObject->setImages(new \Pimcore\Model\DataObject\Data\ImageGallery($addImages));
  1576.                 }
  1577.                 
  1578.                 //var_dump($addImages);
  1579.                 
  1580.                 
  1581.                 //set gender
  1582.                 $gendercat = DataObject::getByPath("/PIM/01 Uhren/03 Kategorien/" . $row["subcategory"]);
  1583.                 if(!$gendercat) {
  1584.                     $newcat = new DataObject\Category(); 
  1585.                     $newcat->setKey(\Pimcore\Model\Element\Service::getValidKey($row["subcategory"], 'object'));
  1586.                     $newcat->setParentId(101);
  1587.                     $newcat->setTitle($row["subcategory"]);
  1588.                     $newcat->setPublished(true);
  1589.                     $newcat->save();
  1590.                     $gendercat = $newcat;
  1591.                 }
  1592.                 
  1593.                 $cats = [$gendercat];
  1594.                 $newObject->setCategories($cats);
  1595.                 
  1596.                 $newObject->setPublished(true);
  1597.                 $newObject->save();
  1598.                 
  1599.                 $this->addBrandCollections($brand);
  1600.                 
  1601.             } elseif($watchjewel == "Schmuck") {
  1602.                
  1603.                 $key = \Pimcore\Model\Element\Service::getValidKey($row["productLine"] . "-" . $row["articleNumber"], 'object');
  1604.                 
  1605.                 $prObject = DataObject::getById($pimId);
  1606.                 $checkstring = date('d-m-y');
  1607.             //$checkstring .= 'manual';
  1608.                 // $checkstring .= "-zenith";
  1609.                 if($prObject) {
  1610.                     // if($prObject->getUpdateinfo() == "291122-try1" || $prObject->getUpdateinfo() == "291122-try2" || $prObject->getUpdateinfo() == "291122-try3" || $prObject->getUpdateinfo() == "291122-try4") {
  1611.                     //     continue;
  1612.                     // }
  1613.                     
  1614.                     
  1615.                     if($prObject->getUpdateInfo() == $checkstring) {
  1616.                         //continue;
  1617.                     }
  1618.                     $newObject = $prObject;
  1619.                     $newObject->setupdateinfo($checkstring);
  1620.                 } else {
  1621.                     $newObject = new DataObject\Product(); 
  1622.                     $newObject->setKey($key);
  1623.                     $newObject->setupdateinfo($checkstring);
  1624.                     
  1625.                     // get Brand Folder ID
  1626.                     $folder = DataObject::getByPath("/PIM/02 Schmuck/02 Produkte/" . $row["brand"]);
  1627.                     
  1628.                     if($folder == null) {
  1629.                         $folder = new DataObject\Folder(); 
  1630.                         $folder->setKey(\Pimcore\Model\Element\Service::getValidKey($row["brand"], 'folder'));
  1631.                         $folder->setParentId(64);
  1632.                         $folder->save();
  1633.                     }
  1634.                     $parentId = $folder->getId();
  1635.                     
  1636.                     $newObject->setParentId($parentId);
  1637.                 }
  1638.                 
  1639.                 $newObject->setModel($row["productLine"]);
  1640.                 $newObject->setArticleNumber($row["articleNumber"]);
  1641.                 $newObject->setStockInputDate($row["stockInputDate"]);
  1642.                 $newObject->setExtId($row["id"]);
  1643.                 $newObject->setPrice($row["price"]);
  1644.                 
  1645.                 //set brand
  1646.                 $brand = DataObject::getByPath("/PIM/02 Schmuck/01 Marken/" . $row["brand"]);
  1647.                 if($row["brand"] == "Jaeger-LeCoultre") {
  1648.                     $brand = DataObject\Brand::getById(23);
  1649.                 }
  1650.                 if($row["brand"] == "IWC") {
  1651.                     $brand = DataObject\Brand::getById(82);
  1652.                 }
  1653.                 if($row["brand"] == "Nomos Glashütte") {
  1654.                     $brand = DataObject\Brand::getById(85);
  1655.                 }
  1656.                 if($row["brand"] == "Sattler") {
  1657.                     $brand = DataObject\Brand::getById(77);
  1658.                 }
  1659.                 $newObject->setBrand($brand);
  1660.                 
  1661.                 //set specifications
  1662.                 $items = new DataObject\Fieldcollection();
  1663.                 $specsql = "SELECT name, value FROM `ArticleProperty` WHERE `article_id` = $extId;";
  1664.                 $specresult = $conn->query($specsql);
  1665.                 
  1666.                 while($specrow = $specresult->fetch_assoc()) {
  1667.                     $item = new DataObject\Fieldcollection\Data\Specification();
  1668.                     
  1669.                     $item->setSpecKey($specrow["name"]);
  1670.                     $item->setSpecValue($specrow["value"]);
  1671.                     
  1672.                     $items->add($item);
  1673.                 }
  1674.                 
  1675.                 $newObject->setSpecs($items);
  1676.                 
  1677.                 //set frontimage
  1678.                 $imagesql = "SELECT src FROM `Image` WHERE `article_id` = $extId;";
  1679.                 $imageresult = $conn->query($imagesql);
  1680.                 $articlenumber = str_replace("/", "-", $row["articleNumber"]);
  1681.                 $assetfolderpath = "/PIM/02 Schmuck/02 Produkte/" . $row["brand"] . "/" . str_replace(["/", '"', "+"], ["-", "", ""], $row["productLine"]) . "-" . $articlenumber;
  1682.                 $assetfolderpath = str_replace("+", "", $assetfolderpath);
  1683.                 $assetfolderpath = str_replace(".", "", $assetfolderpath);
  1684.                 $assetfolderpath = str_replace("|", "", $assetfolderpath);
  1685.                 $assetfolderpath = str_replace("ß", "ss", $assetfolderpath);
  1686.                 Asset\Service::createFolderByPath($assetfolderpath);
  1687.                 
  1688.                 $counter = 0;
  1689.                 $addImages = [];
  1690.                 while($imagerow = $imageresult->fetch_assoc()) {
  1691.                     $counter += 1;
  1692.                     $newAsset = new \Pimcore\Model\Asset\Image();
  1693.                     $newAsset->setFilename($imagerow["src"]);
  1694.                     $newAsset->setData(file_get_contents("https://huebner.carat.solutions/carat/filedownload?fileName=" . $imagerow["src"]));
  1695.                     $newAsset->setParent(\Pimcore\Model\Asset::getByPath($assetfolderpath));
  1696.                     
  1697.                     $checkasset = null;    
  1698.                     $checkasset = \Pimcore\Model\Asset::getByPath($assetfolderpath . "/" . $imagerow["src"]);
  1699.                     
  1700.                     //echo "trying to save asset for " . $row["productLine"] . "-" . $row["articleNumber"] . "<br><hr><br>";
  1701.                     
  1702.                     if(strpos($imagerow["src"], "html") == false && strpos($imagerow["src"], "avif") == false && $imagerow["src"] != "7281fa99-3001-41a5-9be1-8dcfe04d997b.") {
  1703.                         if($checkasset) {
  1704.                            $checkasset->delete();
  1705.                            $newAsset->save();
  1706.                         } else {
  1707.                             // var_dump($newAsset->getFullPath());
  1708.                             // var_dump($assetfolderpath);
  1709.                             // var_dump(\Pimcore\Model\Asset::getByPath($assetfolderpath));
  1710.                             // exit;
  1711.                             $newAsset->save();
  1712.                         }
  1713.                     }
  1714.                     if($counter == 1) {
  1715.                         $newObject->setFrontimage($newAsset);
  1716.                     } else {
  1717.                         $advancedImage = new \Pimcore\Model\DataObject\Data\Hotspotimage();
  1718.                         $advancedImage->setImage($newAsset);
  1719.                         array_push($addImages, $advancedImage);
  1720.                     }
  1721.                 }
  1722.                 
  1723.                 if(count($addImages) > 0) {   
  1724.                     $newObject->setImages(new \Pimcore\Model\DataObject\Data\ImageGallery($addImages));
  1725.                 }
  1726.                 
  1727.                 //var_dump($addImages);
  1728.                 
  1729.                 
  1730.                 //set gender
  1731.                 // $gendercat = DataObject::getByPath("/PIM/01 Uhren/03 Kategorien/" . $row["subcategory"]);
  1732.                 // if(!$gendercat) {
  1733.                 //     $newcat = new DataObject\Category(); 
  1734.                 //     $newcat->setKey(\Pimcore\Model\Element\Service::getValidKey($row["subcategory"], 'object'));
  1735.                 //     $newcat->setParentId(101);
  1736.                 //     $newcat->setTitle($row["subcategory"]);
  1737.                 //     $newcat->setPublished(true);
  1738.                 //     $newcat->save();
  1739.                 //     $gendercat = $newcat;
  1740.                 // }
  1741.                 // 
  1742.                 // $cats = [$gendercat];
  1743.                 // $newObject->setCategories($cats);
  1744.                 
  1745.                 $newObject->setPublished(true);
  1746.                 $newObject->save();
  1747.                 
  1748.                 $this->addBrandCollections($brand);
  1749.             }
  1750.             
  1751.             
  1752.             
  1753.             //var_dump($brand);
  1754.         }
  1755.         
  1756.         $conn->close();
  1757.         
  1758.         echo "success";
  1759.         
  1760.         exit;
  1761.         return [];
  1762.     }
  1763.     
  1764.     /**
  1765.      * @Template
  1766.      * @param Request $request
  1767.      * @return array
  1768.      */
  1769.     public function importAction(Request $request) {
  1770.         
  1771.         $servername = "carat-dev.cp4unapazytb.eu-central-1.rds.amazonaws.com";
  1772.         //$servername = "52.29.59.182";
  1773.         $username = "atimis";
  1774.         $password = "gui8kknyzG";
  1775.         $dbname = "carat_100";
  1776.         
  1777.         // Create connection
  1778.         $conn = new \mysqli($servername, $username, $password, $dbname);
  1779.         // Check connection
  1780.         if ($conn->connect_error) {
  1781.           die("Connection failed: " . $conn->connect_error);
  1782.         }
  1783.         
  1784.         
  1785.         $conn->set_charset("utf8mb4");
  1786.         
  1788.         
  1789.         //$sql = "SELECT * FROM Article LIMIT 10";
  1790.         
  1791.         // $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;";
  1792.         
  1793.         // $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";
  1794.         
  1795.         // $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`;";
  1796.         
  1797.         // fix " "
  1798.         
  1799.         // $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`";
  1800.         
  1801.         // BRAND QUERY
  1802.         
  1803.         // $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`";
  1804.         
  1805.         
  1806.         // FINAL QUERY
  1807.         
  1808.         $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`";
  1809.         
  1810.         // $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`";
  1811.         
  1812.         // END FINAL QUERY
  1813.         
  1814.         
  1815.        // $sql = "SELECT * FROM Article WHERE `brand` LIKE '%Lange%' LIMIT 10;";
  1816.         
  1817.         $result = $conn->query($sql);
  1818.         
  1819.         // var_dump($result);
  1820.         // exit;
  1821.         
  1822.         while($row = $result->fetch_assoc()) {
  1823.             $extId = $row["id"];
  1824.             $row = array_map('trim', $row);
  1825.   
  1826.             
  1827.             $key = \Pimcore\Model\Element\Service::getValidKey($row["productLine"] . "-" . $row["articleNumber"], 'object');
  1828.             
  1829.             $prObject = DataObject::getByPath("/PIM/01 Uhren/02 Produkte/" . $row["brand"] . "/" . $key);
  1830.             $checkstring = date('d-m-y');
  1831.             //$checkstring .= 'manual';
  1832.             // $checkstring .= "-zenith";
  1833.             if($prObject) {
  1834.                 // if($prObject->getUpdateinfo() == "291122-try1" || $prObject->getUpdateinfo() == "291122-try2" || $prObject->getUpdateinfo() == "291122-try3" || $prObject->getUpdateinfo() == "291122-try4") {
  1835.                 //     continue;
  1836.                 // }
  1837.                 
  1838.                 
  1839.                 if($prObject->getUpdateInfo() == $checkstring) {
  1840.                     continue;
  1841.                 }
  1842.                 $newObject = $prObject;
  1843.                 $newObject->setupdateinfo($checkstring);
  1844.             } else {
  1845.                 $newObject = new DataObject\Product(); 
  1846.                 $newObject->setKey($key);
  1847.                 $newObject->setupdateinfo($checkstring);
  1848.                 
  1849.                 // get Brand Folder ID
  1850.                 $folder = DataObject::getByPath("/PIM/01 Uhren/02 Produkte/" . $row["brand"]);
  1851.                 
  1852.                 if($row["brand"] == "Jaeger-LeCoultre") {
  1853.                     $folder = DataObject::getById(396);
  1854.                 }
  1855.                 if($row["brand"] == "IWC") {
  1856.                     $folder = DataObject::getById(962);
  1857.                 }
  1858.                 if($row["brand"] == "Nomos Glashütte") {
  1859.                     $folder = DataObject::getById(407);
  1860.                 }
  1861.                 
  1862.                 if($folder == null) {
  1863.                     $folder = new DataObject\Folder(); 
  1864.                     $folder->setKey(\Pimcore\Model\Element\Service::getValidKey($row["brand"], 'folder'));
  1865.                     $folder->setParentId(61);
  1866.                     $folder->save();
  1867.                 }
  1868.                 $parentId = $folder->getId();
  1869.                 
  1870.                 $newObject->setParentId($parentId);
  1871.             }
  1872.             
  1873.             $newObject->setModel($row["productLine"]);
  1874.             $newObject->setArticleNumber($row["articleNumber"]);
  1875.             $newObject->setStockInputDate($row["stockInputDate"]);
  1876.             $newObject->setExtId($row["id"]);
  1877.             $newObject->setPrice($row["price"]);
  1878.             
  1879.             //set brand
  1880.             $brand = DataObject::getByPath("/PIM/01 Uhren/01 Marken/" . $row["brand"]);
  1881.             if($row["brand"] == "Jaeger-LeCoultre") {
  1882.                 $brand = DataObject\Brand::getById(23);
  1883.             }
  1884.             if($row["brand"] == "IWC") {
  1885.                 $brand = DataObject\Brand::getById(82);
  1886.             }
  1887.             if($row["brand"] == "Nomos Glashütte") {
  1888.                 $brand = DataObject\Brand::getById(85);
  1889.             }
  1890.             if($row["brand"] == "Sattler") {
  1891.                 $brand = DataObject\Brand::getById(77);
  1892.             }
  1893.             $newObject->setBrand($brand);
  1894.             
  1895.             //set specifications
  1896.             $items = new DataObject\Fieldcollection();
  1897.             $specsql = "SELECT name, value FROM `ArticleProperty` WHERE `article_id` = $extId;";
  1898.             $specresult = $conn->query($specsql);
  1899.             
  1900.             while($specrow = $specresult->fetch_assoc()) {
  1901.                 $item = new DataObject\Fieldcollection\Data\Specification();
  1902.                 
  1903.                 $item->setSpecKey($specrow["name"]);
  1904.                 $item->setSpecValue($specrow["value"]);
  1905.                 
  1906.                 $items->add($item);
  1907.             }
  1909.             $newObject->setSpecs($items);
  1910.             
  1911.             //set frontimage
  1912.             $imagesql = "SELECT src FROM `Image` WHERE `article_id` = $extId;";
  1913.             $imageresult = $conn->query($imagesql);
  1914.             $articlenumber = str_replace("/", "-", $row["articleNumber"]);
  1915.             $assetfolderpath = "/PIM/01 Uhren/02 Produkte/" . $row["brand"] . "/" . str_replace(["/", '"', "+"], ["-", "", ""], $row["productLine"]) . "-" . $articlenumber;
  1916.             $assetfolderpath = str_replace("+", "", $assetfolderpath);
  1917.             $assetfolderpath = str_replace(".", "", $assetfolderpath);
  1918.             $assetfolderpath = str_replace("|", "", $assetfolderpath);
  1919.             $assetfolderpath = str_replace("ß", "ss", $assetfolderpath);
  1920.             Asset\Service::createFolderByPath($assetfolderpath);
  1921.             
  1922.             $counter = 0;
  1923.             $addImages = [];
  1924.             while($imagerow = $imageresult->fetch_assoc()) {
  1925.                 $counter += 1;
  1926.                 $newAsset = new \Pimcore\Model\Asset\Image();
  1927.                 $newAsset->setFilename($imagerow["src"]);
  1928.                 $newAsset->setData(file_get_contents("https://huebner.carat.solutions/carat/filedownload?fileName=" . $imagerow["src"]));
  1929.                 $newAsset->setParent(\Pimcore\Model\Asset::getByPath($assetfolderpath));
  1930.                 
  1931.                 $checkasset = null;    
  1932.                 $checkasset = \Pimcore\Model\Asset::getByPath($assetfolderpath . "/" . $imagerow["src"]);
  1933.                 
  1934.                 echo "trying to save asset for " . $row["productLine"] . "-" . $row["articleNumber"] . "<br><hr><br>";
  1935.                 
  1936.                 if(strpos($imagerow["src"], "html") == false && strpos($imagerow["src"], "htm") == false && $imagerow["src"] != "7281fa99-3001-41a5-9be1-8dcfe04d997b.") {
  1937.                     if($checkasset) {
  1938.                        $checkasset->setData(file_get_contents("https://huebner.carat.solutions/carat/filedownload?fileName=" . $imagerow["src"]));
  1939.                        $checkasset->save();
  1940.                        $newAsset = $checkasset;
  1941.                     } else {
  1942.                         // var_dump($newAsset->getFullPath());
  1943.                         // var_dump($assetfolderpath);
  1944.                         // var_dump(\Pimcore\Model\Asset::getByPath($assetfolderpath));
  1945.                         // exit;
  1946.                         $newAsset->save();
  1947.                     }
  1948.                     $newAsset->save();
  1949.                 }
  1950.                 if($counter == 1) {
  1951.                     $newObject->setFrontimage($newAsset);
  1952.                 } else {
  1953.                     $advancedImage = new \Pimcore\Model\DataObject\Data\Hotspotimage();
  1954.                     $advancedImage->setImage($newAsset);
  1955.                     array_push($addImages, $advancedImage);
  1956.                 }
  1957.             }
  1958.             
  1959.             if(count($addImages) > 0) {   
  1960.                 $newObject->setImages(new \Pimcore\Model\DataObject\Data\ImageGallery($addImages));
  1961.             }
  1962.             
  1963.             //var_dump($addImages);
  1964.             
  1965.             
  1966.             //set gender
  1967.             $gendercat = DataObject::getByPath("/PIM/01 Uhren/03 Kategorien/" . $row["subcategory"]);
  1968.             if(!$gendercat) {
  1969.                 $newcat = new DataObject\Category(); 
  1970.                 $newcat->setKey(\Pimcore\Model\Element\Service::getValidKey($row["subcategory"], 'object'));
  1971.                 $newcat->setParentId(101);
  1972.                 $newcat->setTitle($row["subcategory"]);
  1973.                 $newcat->setPublished(true);
  1974.                 $newcat->save();
  1975.                 $gendercat = $newcat;
  1976.             }
  1977.             
  1978.             $cats = [$gendercat];
  1979.             $newObject->setCategories($cats);
  1980.             
  1981.             $newObject->setPublished(true);
  1982.             $newObject->save();
  1983.             
  1984.             //var_dump($brand);
  1985.         }
  1986.         
  1987.         $conn->close();
  1988.         
  1989.         
  1990.         exit;
  1991.         return [];
  1992.     }
  1993.     
  1994.     /**
  1995.      * @Template
  1996.      * @param Request $request
  1997.      * @return array
  1998.      */
  1999.     public function categoryAction(Request $request) {
  2000.         
  2001.         
  2002.         return [];
  2003.     }
  2004.     
  2005.     
  2006.     /**
  2007.      * @Template
  2008.      * @param Request $request
  2009.      * @return array
  2010.      */
  2011.     public function homeAction(Request $request) {
  2012.         return [];
  2013.     }
  2014.     
  2015.     /**
  2016.      * @Template
  2017.      * @param Request $request
  2018.      * @return array
  2019.      */
  2020.     public function productOverviewAction(Request $request) {
  2021.         $jewellerycategory = null;
  2022.         $entries = new DataObject\Product\Listing();
  2023.         $filters = [];
  2024.         
  2025.         $page = isset($_GET["page"]) ? $_GET["page"] : null;
  2026.         if($page) {
  2027.             if($page == 1) {
  2028.                 $entries->setLimit(24);
  2029.             }
  2030.             $filters["page"] = $page;
  2031.         }
  2032.         
  2033.         $collection = isset($_GET["collection"]) ? $_GET["collection"] : null;
  2034.         if($collection) {
  2035.             $entries->addConditionParam("categories LIKE ?", "%object|$collection%", "AND");
  2036.             $filters["collection"] = $collection;
  2037.         }
  2038.         
  2039.         $type = isset($_GET["type"]) ? $_GET["type"] : null;
  2040.         if($type) {
  2041.             //$entries->addConditionParam("categories LIKE ?", "%object|$type%", "AND");
  2042.             $entries->setCondition("categories LIKE '%object|$type%'");
  2043.             $filters["type"] = $type;
  2044.         }
  2045.         
  2046.         $brand = isset($_GET["brand"]) ? $_GET["brand"] : null;
  2047.         if($brand) {
  2048.             $entries->addConditionParam("brand__id = ?", "$brand", "AND");
  2049.             $brandobject = DataObject::getById($brand);
  2050.             $brandcollections = $brandobject->getCollections() ? $brandobject->getCollections()->getChildren() : null;
  2051.             
  2052.             $filters["brand"] = $brand;
  2053.             $filters["brandCollections"] = $brandcollections;
  2054.         } 
  2055.         if (!$brand && $collection) {
  2056.             $collectionObject = DataObject::getById($collection);
  2057.                 // var_dump($collection);
  2058.                 // exit;
  2059.             
  2060.             
  2061.             $collectionpath = $collectionObject->getPath();
  2062.             // var_dump($collectionpath);
  2063.             // exit;
  2064.             if(str_contains($collectionpath, "Schmuck")) {
  2065.                 
  2066.                 $entries->addConditionParam("o_path LIKE '%Schmuck%'", "AND");
  2067.                 $jewellerycategory = $collectionObject;
  2068.             } else {
  2069.                 $brandkey = $collectionObject->getParent()->getKey();
  2070.                 
  2071.                 $brands = DataObject\Brand::getByName($brandkey);
  2072.                 $brand = $brands->load();
  2073.                 if(is_array($brand)) {
  2074.                     $brand = $brand[0];
  2075.                     $brandobject = $brand;
  2076.                     $brandcollections = $brandobject->getCollections() ? $brandobject->getCollections()->getChildren() : null;
  2077.                     
  2078.                     $filters["brand"] = $brand->getId();
  2079.                     $filters["brandCollections"] = $brandcollections;
  2080.                 } else {
  2081.                     $brand = null;
  2082.                     $filters["brand"] = null;
  2083.                     $filters["brandCollections"] = null;
  2084.                 }
  2085.             }
  2086.             
  2087.             
  2088.             
  2089.         }
  2090.         
  2091.         if(!isset($filters["brandCollections"])) {
  2092.             $brandCollections = new DataObject\Category\Listing();
  2093.             $brandCollections->setCondition("o_path LIKE '%Kollektionen%'");
  2094.             $brandCollections = $brandCollections->load();
  2095.             $filters["brandCollections"] = $brandCollections;
  2096.         }
  2097.         
  2098.         $casematerial = isset($_GET["casematerial"]) ? $_GET["casematerial"] : null;
  2099.         if($casematerial) {
  2100.             $entries->addFieldCollection("Specification", "specs");
  2101.             $entries->addConditionParam("`Specification~specs`.specKey = 'Gehäusematerial' AND `Specification~specs`.specValue LIKE ?", "%$casematerial%", "AND");
  2102.             $filters["casematerial"] = $casematerial;
  2103.         }
  2104.         
  2105.         $sort = isset($_GET["sort"]) ? $_GET["sort"] : null;
  2106.         if($sort) {
  2107.            switch($sort) {
  2108.                case "actual":
  2109.                    $entries->setOrderKey("stockInputDate");
  2110.                    $entries->setOrder("desc");
  2111.                    break;
  2112.                case "priceasc":
  2113.                    $entries->setOrderKey("price");
  2114.                    $entries->setOrder("asc");
  2115.                    break;
  2116.                 case "pricedesc":
  2117.                     $entries->setOrderKey("price");
  2118.                     $entries->setOrder("desc");
  2119.                     break;
  2120.                 case "name":
  2121.                     $entries->setOrderKey("model");
  2122.                     $entries->setOrder("asc");
  2123.                     break;
  2124.            }
  2125.            $filters["sort"] = $sort;
  2126.         } else {
  2127.             $entries->setOrderKey("stockInputDate");
  2128.             $entries->setOrder("desc");
  2129.             $filters["sort"] = "actual";
  2130.         }
  2131.         
  2132.         
  2133.         
  2134.         //var_dump($entries);
  2135.         
  2136.         $entries = $entries->load();
  2137.         
  2138.        
  2139.         
  2140.         return $this->render('/default/product_overview.html.twig', ["products" => $entries, "filters" => $filters, "jewellerycat" => $jewellerycategory]);
  2141.    
  2142.       
  2143.     }
  2144.     
  2145.     /**
  2146.      * @Template
  2147.      * @param Request $request
  2148.      * @return array
  2149.      */
  2150.     public function brandsOverviewAction(Request $request) {
  2151.         $docpath = $this->document->getFullPath();
  2152.         //var_dump($docpath);
  2153.         
  2154.         if(strpos($docpath, "Uhren") !== false) {
  2155.             $path = "Uhren";
  2156.         } else {
  2157.             $path = "Schmuck";
  2158.         }
  2159.         
  2160.         
  2161.         $entries = new DataObject\Brand\Listing();
  2162.         $entries->setCondition("o_path LIKE '%$path%' ");
  2163.         $entries->setOrderKey("o_key");
  2164.         $entries->setOrder("ASC");
  2165.         $entries->load();
  2166.         
  2167.         return $this->render('/default/brands_overview.html.twig', ["brands" => $entries]);
  2168.     }
  2169.     
  2170.     /**
  2171.      * @Template
  2172.      * @param Request $request
  2173.      * @return array
  2174.      */
  2175.     public function teaserOverviewAction(Request $request) {
  2176.         return [];
  2177.     }
  2178.     
  2179.     /**
  2180.      * @Template
  2181.      * @param Request $request
  2182.      * @return array
  2183.      */
  2184.     public function brandAction(Request $request) {
  2185.         return $this->render('/default/brand.html.twig', []);
  2186.     }
  2187.     
  2188.     /**
  2189.      * @Template
  2190.      * @param Request $request
  2191.      * @return array
  2192.      */
  2193.     public function productAction(Request $request) {
  2194.         $id = str_replace("/", "", $request->get("id"));
  2195.         
  2196.         $product = DataObject::getById($id);
  2197.         
  2198.         return $this->render('/default/product.html.twig', ["product" => $product]);
  2199.     }
  2200.     
  2201.     /**
  2202.      * @Template
  2203.      * @param Request $request
  2204.      * @return array
  2205.      */
  2206.     public function newproductAction(Request $request) {
  2207.         $key = substr($request->get("key"), 1);
  2208.         $number = substr($request->get("number"), 1);
  2209.         $id = substr($request->get("id"), 1);
  2210.         
  2211.         // var_dump($id);
  2212.         // // exit;
  2213.         
  2214.         // $product = DataObject\Product::getByArticleNumber($number);
  2215.         //     
  2216.         // $product = $product->load()[0];
  2217.         
  2218.         $product = DataObject::getById($id);
  2219.         
  2220.         return $this->render('/default/product.html.twig', ["product" => $product]);
  2221.     }
  2222.     
  2223.     /**
  2224.      * @Template
  2225.      * @param Request $request
  2226.      * @return array
  2227.      */
  2228.     public function teaserAction(Request $request) {
  2229.         $id = str_replace("/", "", $request->get("id"));
  2230.         
  2231.         $teaser = DataObject::getById($id);
  2232.         $relation = $teaser->getRelation();
  2233.         
  2234.         if($relation && $relation->getType() == "object" && $relation->getClassName() == "Category") {
  2235.             $collectionObject = $relation;
  2236.             $brandkey = $collectionObject->getParent()->getKey();
  2237.             $brands = DataObject\Brand::getByName($brandkey);
  2238.             $brand = $brands->load();
  2239.             if(is_array($brand)) {
  2240.                 $brand = $brand[0];
  2241.                 return $this->render('/default/teaser.html.twig', ["teaser" => $teaser, "brand" => $brand]);
  2242.             }
  2243.         } else {
  2244.             return $this->render('/default/teaser.html.twig', ["teaser" => $teaser]);
  2245.         }
  2246.             
  2247.         
  2248.         
  2249.        
  2250.     }
  2251.     
  2252.     private static function addBrandCollections($brand) {
  2253.         $collections = new DataObject\Category\Listing();
  2254.         if($brand->getId() == 85) {
  2255.             $collections->setCondition("o_path LIKE :brand", ["brand" => "%Nomos%"]);
  2256.         } else {
  2257.             $collections->setCondition("o_path LIKE :brand", ["brand" => "%".$brand->getName()."%"]);
  2258.         }
  2259.         $collections->load();
  2260.         
  2261.         
  2262.         
  2263.         $brandid = $brand->getId();
  2264.         
  2265.         foreach($collections as $coll) {
  2266.             
  2267.             $key = $coll->getKey();
  2268.             
  2269.             $products = new DataObject\Product\Listing();
  2270.             if($brand->getName() == "A. Lange & Söhne") {
  2271.                 $products->setCondition("UPPER(`o_key`) LIKE \"%$key%\" AND brand__id = $brandid");
  2272.             } elseif($coll->getId() == 281) {
  2273.                 $products->setCondition("`o_key` LIKE \"%$key%\" AND `o_key` NOT LIKE \"%Superocean Heritage%\" AND brand__id = $brandid");
  2274.             } elseif($coll->getId() == 285) {
  2275.                 $products->setCondition("`o_key` LIKE \"% Avi %\" AND brand__id = $brandid");
  2276.             } else {
  2277.                 $products->setCondition("`o_key` LIKE \"%$key%\" AND brand__id = $brandid");
  2278.             }
  2279.             
  2280.             $products->load();
  2281.             foreach($products as $product) {
  2282.                 $cats = $product->getCategories();
  2283.                 if($cats) {
  2284.                     if (!in_array($coll, $cats, true)) {
  2285.                         array_push($cats, $coll);
  2286.                     } 
  2287.                 } else {
  2288.                     array_push($cats, $coll);
  2289.                 }
  2290.                
  2291.                 $product->setCategories($cats);
  2292.                 $product->save();
  2293.             }
  2294.             
  2295.         }
  2296.     }
  2297.     
  2298. }