custom/plugins/SteincoProducts/src/Subscriber/OrderEventsSubscriber.php line 154

Open in your IDE?
  1. <?php declare(strict_types=1);
  3. namespace Schoettler\SteincoProducts\Subscriber;
  5. use Schoettler\SteincoProducts\SharedFunctions;
  6. use Symfony\Component\EventDispatcher\EventSubscriberInterface;
  7. use Shopware\Core\Framework\DataAbstractionLayer\Event\EntityWrittenEvent;
  8. use Shopware\Core\Checkout\Order\OrderEvents;
  9. use Shopware\Core\Framework\DataAbstractionLayer\EntityRepository;
  10. use Shopware\Core\System\SystemConfig\SystemConfigService;
  11. use Shopware\Core\Framework\DataAbstractionLayer\Search\Criteria;
  12. use Shopware\Core\Framework\DataAbstractionLayer\Search\Filter\EqualsFilter;
  13. use Shopware\Core\Framework\DataAbstractionLayer\Search\Filter\EqualsAnyFilter;
  15. use DateTimeImmutable;
  16. use DateTimeInterface;
  18. // Rechnung immer zuerst
  20. class OrderEventsSubscriber implements EventSubscriberInterface {
  22.     public static function getSubscribedEvents() : array 
  23.     {
  24.         // Return the events to listen to as array like this:  <event to listen to> => <method to execute>
  25.         return [
  26.             OrderEvents::ORDER_WRITTEN_EVENT => 'onOrderWritten',
  27.             'document.written' => 'onDocumentWritten'
  28.         ];
  29.     }
  31.     Private $DOK_RECHNUNG = "IRGVK";
  32.     Private $DOK_LIEFERSCHEIN = "ILSVK";
  33.     Private $DOK_GUTSCHRIFT = "IGUVK";
  34.     Private $DOK_RECHNUNGSSTORNO = "IGUVK";
  35.     Private $DOK_GUTSCHRIFTSTORNO = "IGUST";
  36.     Private $DOK_AUFTRAGBESTAETIGUNG = "IABVK";
  38.     Private $LINEBREAK = "\r\n";
  40.     private $context;
  42.     /**
  43.      * @var SystemConfigService
  44.      */
  45.     private $systemConfigService;
  47.     /**
  48.      * @var EntityRepository
  49.      */
  50.     private $customerRepository;
  52.     /**
  53.      * @var EntityRepository
  54.      */
  55.     private $orderRepository;
  57.     /**
  58.      * @var EntityRepository
  59.      */
  60.     private $documentRepository;
  62.     /**
  63.      * @var EntityRepository
  64.      */
  65.     private $propertyOptionRepository;
  67.     
  68.     /**
  69.      * @var EntityRepository
  70.      */
  71.     private $propertyRepository;
  73.     /**
  74.      * @var DocumentService
  75.      */
  76.     protected $documentService;
  78.      /**
  79.      * @var EntityRepository
  80.      */
  82.     private  $mediaRepository;
  84.     public function __construct(SystemConfigService $systemConfigService,
  85.     EntityRepository $customerRepository, EntityRepository $orderRepository,
  86.      EntityRepository $documentRepository, EntityRepository $propertyOptionRepository,
  87.      EntityRepository $propertyRepository, $docService, $mediaRepository)
  88.     {
  89.         $this->systemConfigService = $systemConfigService;
  90.         $this->customerRepository = $customerRepository;
  91.         $this->orderRepository = $orderRepository;
  92.         $this->documentRepository = $documentRepository;
  93.         $this->propertyOptionRepository = $propertyOptionRepository;
  94.         $this->propertyRepository = $propertyRepository;
  95.         $this->documentService = $docService;
  96.         $this->mediaRepository = $mediaRepository;
  97.         //$this->urlGenerator = $urlGenerator;
  98.     }
  100.     public function onOrderWritten(EntityWrittenEvent $event) 
  101.     {
  102.         //Database
  103.         $dbHost = $this->systemConfigService->get('SteincoProductsController.config.externalDatabaseHost');
  104.         $dbUser = $this->systemConfigService->get('SteincoProductsController.config.externalDatabaseUser');
  105.         $dbPass = $this->systemConfigService->get('SteincoProductsController.config.externalDatabasePassword');
  106.         $dbName = $this->systemConfigService->get('SteincoProductsController.config.externalDatabaseName');
  108.         $mysqli = new \mysqli($dbHost, $dbUser, $dbPass, $dbName);
  109.         if($mysqli->connect_errno ) {
  110.             // Log the error somehow
  111.             //dump($mysqli->connect_errno);
  112.         }  
  113.         $orderUUID = $event->getWriteResults()[0]->getPrimaryKey();
  114.         $myContext = $event->getContext();
  115.         $this->context = $myContext ?? \Shopware\Core\Framework\Context::createDefaultContext();
  117.         $orderCriteria = new Criteria([]);
  118.         $orderCriteria->addAssociation('currency')->addAssociation('billingAddress')->addFilter(new EqualsFilter('id', $orderUUID));
  120.        
  122.         $order_searchresult = $this->orderRepository->search(
  123.             $orderCriteria,
  124.            \Shopware\Core\Framework\Context::createDefaultContext());
  125.         
  126.         $order = $order_searchresult->getEntities()->getElements()[$orderUUID];
  127.         // TODO: if order is empty
  128.         $order_number = $order->getOrderNumber();
  129.         $customer_number = $order->getOrderCustomer()->getCustomerNumber();
  130.     //dump($order_searchresult, $order);
  131.         // check if the order is already present in the Shop2DWH_Erstbesteller table
  132.         $check_erstbesteller_sql = 'SELECT NOT EXISTS (SELECT * FROM Shop2DWH_Erstbesteller WHERE Kundennummer = "' 
  133.         .$customer_number .'" ) AS firstorder';
  134.         if($check_erstbesteller_query = $mysqli->query($check_erstbesteller_sql)) {
  135.             $row = mysqli_fetch_array($check_erstbesteller_query);
  136.             $no_entry_exists = $row['firstorder'];
  137.             $check_erstbesteller_query->close();
  138.             $firstorderDate = $order->getCreatedAt();
  139.             $erstbestelldatum = $firstorderDate->format('Y-m-d H:i:s');
  140.             if($no_entry_exists == 1) {
  141.                 // first order by this Customer, write to Shop2DWH_Erstbesteller
  142.                 $sql = 'INSERT INTO Shop2DWH_Erstbesteller (Kundennummer, order_id, Erstbestelldatum)' 
  143.                 .' VALUES ("' .$customer_number .'", "' .$order_number .'", "' .$erstbestelldatum .'" )';
  144.                 if(! $mysqli->query($sql)){
  145.                     //dump(mysqli_error($mysqli), $sql, $customer);
  146.                 }
  147.             }
  148.         } else {
  149.             //TODO: query failed
  150.         }
  152.     }
  154.     public function onDocumentWritten(EntityWrittenEvent $event){
  156.         SharedFunctions::dumpToLog("onDocumentWritten()");
  157.         //get DB-related data
  158.         $dbHost = $this->systemConfigService->get('SteincoProductsController.config.externalDatabaseHost');
  159.         $dbUser = $this->systemConfigService->get('SteincoProductsController.config.externalDatabaseUser');
  160.         $dbPass = $this->systemConfigService->get('SteincoProductsController.config.externalDatabasePassword');
  161.         $dbName = $this->systemConfigService->get('SteincoProductsController.config.externalDatabaseName');
  162.        
  163.         // each document should only be written once, so all data must be present at this event
  164.         $myContext = $event->getContext();
  165.         $this->context = $myContext ?? \Shopware\Core\Framework\Context::createDefaultContext();
  166.         $writeResult = $event->getWriteResults();
  167.         $writeResult = reset($writeResult);
  168.         $writeResult = $writeResult->getPayload(); 
  169.         // SharedFunctions::dumpToLog($writeResult);
  170.         $orderUUID = $writeResult['orderId'];
  171.         $orderVersionUUID = $writeResult['orderVersionId']; // probably not needed
  172.         $documentUUID = $writeResult['id'];
  174.         // get the document including type
  175.         $documentCritera = new Criteria([]);
  176.         $documentCritera->addAssociation("documentType")->addFilter(New EqualsFilter('id', $documentUUID));
  177.         $document_searchresult = $this->documentRepository->search(
  178.             $documentCritera,
  179.             $myContext
  180.         );
  181.         $document = $document_searchresult->getEntities()->getElements()[$documentUUID];
  182. //dump($document_searchresult->getEntities()->getElements()[$documentUUID],$documentUUID,$document_searchresult->getEntities()->getElements());
  183.         // we only want Rechnung, Storno, Gutschrift for the bulk of data
  184.         $documentTechnicalName = $document->getDocumentType()->getTechnicalName();
  185.         $invoice_types = ['invoice'];
  186.         $creditNote_types = ['credit_note', 'storno'];
  188.         // if the document is just created, we need to create a copy of the pdf and create a jpl-file
  189.         // pass document and order
  190. //dump("copying document");
  191.         if(!empty($orderUUID)){
  192.         $this->writeFilesToExchangeFolder($orderUUID, $document);
  193. //dump("copied document");
  194.         // delivery_note => Lieferschein
  195.         if ($documentTechnicalName == 'delivery_note'){
  196.             // delivery_note is created after invoice by design, 
  197.             // we just use it to update the Lieferschein field in existing entries
  199.             // UPDATE Shop2DWH_BelegPosition Set Lieferschein = $belegnummer WHERE BelegkopfId = $belegkopfId
  200.             // To retrieve BelegkopfId we need the invoice, which we can get using Bestellnr
  201.             $order = $this->getOrder($orderUUID);
  202.             $orderNumber = $order->getOrderNumber();
  204.             $documentCriteria = new Criteria();
  205.             $documentCriteria->addFilter(new EqualsFilter('orderId', $orderUUID));
  206.             
  207.             $documents = $this->documentRepository->search($documentCriteria, $this->context);
  208.             // loop over the docs and update Belegpositionen
  209.             $documentEntities = $documents->getEntities()->getElements();
  210.             $whereClause = ' WHERE LieferscheinNr ';
  211.             $numberOfElemets = sizeof($documentEntities);
  212. //dump("trying to update delivery-note");
  213.             If($numberOfElemets > 0){
  214.                 If ($numberOfElemets == 1) {
  215.                     $doc = reset($documentEntities);
  216.                     $whereClause .= '= "' .$doc->getConfig()['documentNumber'] .'" ';
  217.                 } elseif ($numberOfElemets > 1) {
  218.                     $whereClause .= 'IN(';
  219.                     foreach ($documentEntities as $oldDocuments) {
  220.                         $whereClause .= '"' .$oldDocuments->getConfig()['documentNumber'] .'", ';
  221.                     }
  222.                     $whereClause = substr($whereClause, 0, strlen($whereClause) - 2);
  223.                     $whereClause .= ') ';
  224.                 }
  225.                 $updateSQL = 'UPDATE Shop2DWH_Belegposition SET LieferscheinNr = "' .$writeResult['config']['documentNumber'] .'" ' .$whereClause;
  226.                 
  227.                 $mysqli = new \mysqli($dbHost, $dbUser, $dbPass, $dbName);
  228.                 if($mysqli->connect_errno ) {
  229.                     // Log the error somehow
  230.                     //dump($mysqli->connect_errno);
  231.                 }
  232.                 if(! $mysqli->query($updateSQL)){
  233.                  //dump(mysqli_error($mysqli), $updateSQL);
  234.                 }
  235.             }
  236.         }
  237.         if(in_array($documentTechnicalName, $invoice_types) || in_array($documentTechnicalName, $creditNote_types))
  238.         {
  239.             SharedFunctions::dumpToLog("Creating BelegkopfSQL");
  240.             // get the order including currency and billing-address and shipping-address
  241.             $order = $this->getOrder($orderUUID);
  242.             $zahlungsart = $this->getZahlungsart($orderUUID, $orderVersionUUID, $order->getLanguageId());
  243.             
  244.             $dummy_time = strtotime('2999-12-31');
  245.             $dummy_date = date('Y-m-d H:i:s', $dummy_time);
  246.             $bestellnummer = $order->getOrderNumber();
  247.             $belegwaehrung = $order->getCurrency()->getIsoCode();
  249.             $billing_address_country = $order->getBillingAddress()->getCountry()->getIso3();
  251.             // get necessary Data for Belegkopf and write to external DB
  253.             $belegart = in_array($documentTechnicalName, $creditNote_types)? 'GAS' : 'RAS'; //"RAS"; // possible values: RAS, GAS (Rechnungs/Gutschriftsausgang)
  254.             $belegnummer = $writeResult['config']['documentNumber'];
  255.             $belegdatum = strtotime($writeResult['config']['documentDate']);
  256.             $belegdatum = date('Y-m-d H:i:s', $belegdatum);
  257.             $leistungsdatum = $belegdatum;
  258.             $erzeugungsdatum = $writeResult['createdAt']; 
  259.             // This is a DateTimeImmutable Object
  260.             $erzeugungsdatum = $erzeugungsdatum->format('Y-m-d H:i:s');
  261.             $referenznummer = $writeResult['config']['referencedDocumentId']?? ' '; // z.B. bei Storno: Nummer der stornierten Rechnung
  262.             $personenkonto = $order->getOrderCustomer()->getCustomerNumber();
  263.             $zahlungsdienstleister_OP_ID = '';// $order->getOrderTransactions()->getCustomFileds()['swag_paypal_order_id'];//''; // Paypal Transaktions ID; bei Rechnung: Rechnungsnummer, nullable $order->getOrderTransactions()->getCustomFileds()['swag_paypal_order_id']
  264.             $transactions = $order->getTransactions()->getElements();
  265.             //we use the first
  266.             $first_transaction = reset($transactions);
  267.             if($first_transaction) {
  268.                 $custom_transactionfields = $first_transaction->getCustomFields() ?? [];
  269.                 $zahlungsdienstleister_OP_ID = array_key_exists('swag_paypal_order_id', $custom_transactionfields) ? $custom_transactionfields['swag_paypal_order_id'] : '';
  270.             }
  271.             $valutadatum = $leistungsdatum;
  272.             $ziel1 = $dummy_date;
  273.             $skonto1Betrag = $order->getAmountTotal(); //
  274.             $skonto1Prozent = "0";
  275.             $faelligAm = $belegdatum;
  276.             $land_Lieferadresse_ISO3 = $billing_address_country; // no shipping Address in Order or Document! -> because there is only one address per customer ATM, just take the billing address
  277.             $land_Rechnungsadresse_ISO3 = $billing_address_country;
  278.             $ust_id = $order->getOrderCustomer()->getVatIds()[0]?? "";
  279.             $ust_id = preg_replace('/[^a-zA-Z0-9]/', "", $ust_id); // remove white space and non-alphanumeric characters
  280.             $endbetrag = $order->getAmountTotal();
  282.             $taxes = $order->getPrice()->getCalculatedTaxes()->getElements();
  283.             // HALLO: muss es hier fix 7 und 19 sein?
  284.             $endbetrag_Steuerschluessel1 = array_key_exists(19, $taxes) ? $taxes[19]->getTax() : 0;
  285.             $endbetrag_Steuerschluessel2 = array_key_exists(7, $taxes) ? $taxes[7]->getTax() : 0;
  287.             // Credit-note type should have negative values for money-related entries
  288.             if(in_array($documentTechnicalName, $creditNote_types)){
  289.               $skonto1Betrag *= -1;
  290.               $endbetrag *= -1;
  291.               $endbetrag_Steuerschluessel1 *= -1;
  292.               $endbetrag_Steuerschluessel2 *= -1;
  293.             }
  295.             $sql_belegkopf = 'INSERT INTO Shop2DWH_Belegkopf (' 
  296.             .'`Belegart`, ' 
  297.             .'`Belegnummer`, '
  298.             .'`BestellNr`, ' 
  299.             .'`Belegwaehrung`, ' 
  300.             .'`Belegdatum`, ' 
  301.             .'`Leistungsdatum`, ' 
  302.             .'`Erzeugungsdatum`, ' 
  303.             .'`Referenznr`, '
  304.             .'`Personenkonto`, ' 
  305.             .'`ZahlungsDienstleister_OP_ID`, ' 
  306.             .'`Valutadatum`, ' 
  307.             .'`Ziel1`, ' 
  308.             .'`Skonto1Betrag`, ' 
  309.             .'`Skonto1Prozent`, ' 
  310.             .'`FaelligAm`, ' 
  311.             .'`Land_Lieferadresse_ISO3`, '
  312.             .'`Land_Rechnungsadresse_ISO3`, '
  313.             .'`UST-ID`, '
  314.             .'`Endbetrag`, '
  315.             .'`Endbetrag_Steuerschluessel1`, '
  316.             .'`Endbetrag_Steuerschluessel2`, '
  317.             .'`Zahlungsart`'
  318.              .' )'
  319.             .' VALUES ( '
  320.             .'"' .$belegart .'", '
  321.             .'"' .$belegnummer .'", '
  322.             .'"' .$bestellnummer .'", '
  323.             .'"' .$belegwaehrung .'", '
  324.             .'"' .$belegdatum .'", '
  325.             .'"' .$leistungsdatum .'", '
  326.             .'"' .$erzeugungsdatum .'", '
  327.             .'"' .$referenznummer.'", '
  328.             .'"' .$personenkonto .'", '
  329.             .'"' .$zahlungsdienstleister_OP_ID .'", '
  330.             .'"' .$valutadatum .'", '
  331.             .'"' .$ziel1 .'", '
  332.             .'"' .$skonto1Betrag .'", '
  333.             .'"' .$skonto1Prozent .'", '
  334.             .'"' .$faelligAm .'", '
  335.             .'"' .$land_Lieferadresse_ISO3 .'", '
  336.             .'"' .$land_Rechnungsadresse_ISO3 .'", '
  337.             .'"' .$ust_id .'", '
  338.             .'"' .$endbetrag .'", '
  339.             .'"' .$endbetrag_Steuerschluessel1 .'", '
  340.             .'"' .$endbetrag_Steuerschluessel2 .'", '
  341.             .'"' .$zahlungsart .'"'
  342.             .' )';
  343.             
  344.             //dump($sql_belegkopf);
  345.             $mysqli = new \mysqli($dbHost, $dbUser, $dbPass, $dbName);
  346.             if($mysqli->connect_errno ) {
  347.                 // Log the error somehow
  348.                 //dump($mysqli->connect_errno);
  349.             }
  351.             
  352.             if(! $mysqli->query($sql_belegkopf)){
  353.                //dump(mysqli_error($mysqli), $sql_belegkopf);
  354.             }
  355. //dump("Belegkopf persisted");
  356.             // retrieve the belegkopfId
  357.             $belegkopfId = '';
  358.             $sql_getBelegkopf = 'SELECT BelegkopfID AS kopfid FROM Shop2DWH_Belegkopf WHERE Belegnummer = "' .$belegnummer .'" ';
  359.             if($get_belegkopfid_query = $mysqli->query($sql_getBelegkopf)) {
  360.                 $row = mysqli_fetch_array($get_belegkopfid_query);
  361.                 // dump($row);
  362.                 $belegkopfId = $row['kopfid'];
  363.                 $get_belegkopfid_query->close();
  364.             }
  365.             $optionSearchResult = $this->getPropertyOptions_by_name_and_propertydescription('1', 'istHandelwareWebshop');
  366.             $handelswareOption = reset($optionSearchResult);
  368.             if ($handelswareOption !== false) {
  369.                 $handelswareId = $handelswareOption->getId();
  370.             } else {
  371.                 // Umgang mit dem Fall, dass keine Option gefunden wurde
  372.                 SharedFunctions::dumpToLog('Keine Handelsware-Option gefunden.');
  373.                 $handelswareId = '<->'; // oder ein anderer sinnvoller Wert
  374.             }
  375.             // get neccessary Data for Belegposition and write to external DB
  376. //dump("create Belegpositionquery");
  377.             $order_linteitems = $order->getLineItems()->getElements();
  378.             foreach($order_linteitems as $lineItem){
  379.                 $positionsnummer = $lineItem->getPosition();
  380.                 $betragNetto = $lineItem->getPrice()->getTotalPrice();
  381.                 $betragSteuer = $lineItem->getPrice()->getCalculatedTaxes()->getElements()[19]->getTax();
  382.                 $steuerschluessel = '9';
  383.                 $steuerProzent = $lineItem->getPrice()->getCalculatedTaxes()->getElements()[19]->getTaxRate();
  384.                 $positionstext = $lineItem->getDescription() ?? "";
  385.                 $lieferscheinNr = $belegnummer;
  387.                 $payload = $lineItem->getPayload();
  388.                 $artikelNr = array_key_exists('productNumber', $payload) ? $payload['productNumber'] : 'ohne';
  389.                 $menge = $lineItem->getQuantity();
  390.                 $me = 'Stück';
  391.                 
  392.                 $produktart = 'Eigenfertigung';
  393.                 if ($lineItem->getProduct()) {
  394.                     $propertyIds = $lineItem->getProduct()->getPropertyIds();
  395.                     if(in_array($handelswareId, $propertyIds)){
  396.                         $produktart = 'Handelsware';
  397.                     }
  398.                 } else {
  399.                     $produktart = 'Rabatt';
  400.                 }
  401.             /*
  402.                 Get all info about order and write to Shop2DWH_Belegkopf and Shop2DWH_Belegposition
  403.                 Belegkopf:
  404.                 Belegart, Belegnummer, BestellNr, Belegdatum, Leistungsdatum, Erzeugungsdatum, (Referenznummer), Personenkonto,
  405.                 Zahlungsdienstleister_OP_ID, Valutadatum, Ziel1, Skonto1Betrag, Skonto1Prozent, FaelligAm, Land_Lieferadresse_ISO3,
  406.                 Land_Rechnungsadresse_ISO3, UST-ID, Endbetrag, Endbetrag_Steuerschluessel1, Endbetrag_Steuerschluessel2, 
  407.                 
  408.                 Belegposition:
  409.                 *BelegkopfID int11
  410.                 PositionsNr int11
  411.                 BetragNetto decimal
  412.                 BetragSteuer decimal
  413.                 Steuerschluessel varchar: 9=> 19%, 5:0%
  414.                 SteuerProzent decimal(4,2) (???)
  415.                 PositionsText
  416.                 LieferscheinNr varchar15
  417.                 Leistungsdatum date
  418.                 ArtikelNr
  419.                 Menge int
  420.                 ME varchar(15) ???
  421.                 Produktart varchar(15) ???
  422.                  */
  423.                 // Credit-note type should have negative values for money-related entries
  424.                 if(in_array($documentTechnicalName, $creditNote_types)){
  425.                   $betragNetto *= -1;
  426.                   $betragSteuer *= -1;
  427.                 }
  429.                  $sql_belegposition = 'INSERT INTO Shop2DWH_Belegposition ('
  430.                  .' BelegkopfID , PositionsNr, BetragNetto, BetragSteuer, Steuerschluessel, '
  431.                  .'SteuerProzent, PositionsText, LieferscheinNr, Leistungsdatum, '
  432.                  .'ArtikelNr, Menge, ME, Produktart ) VALUES ( '
  433.                  .$belegkopfId .', '
  434.                  .$positionsnummer .', '
  435.                  .$betragNetto .', '
  436.                  .$betragSteuer .', '
  437.                  .'"' .$steuerschluessel .'", '
  438.                  .$steuerProzent .', '
  439.                  .'"' .$positionstext .'", '
  440.                  .'"' .$lieferscheinNr .'", '
  441.                  .'"' .$leistungsdatum .'", '
  442.                  .'"' .$artikelNr .'", '
  443.                  .$menge .', '
  444.                 .'"' .$me .'", '
  445.                 .'"' .$produktart .'" )';
  447. //dump($sql_belegposition);
  448.                 if(! $mysqli->query($sql_belegposition)){
  449.                     //dump(mysqli_error($mysqli), $sql_belegposition);
  450.                 }
  451.             
  452.             }
  453.             // add another position for shipment
  454.             $shipping_costs = $order->getShippingCosts();
  456.             $betragNetto = $shipping_costs->getTotalPrice();
  457.             $betragSteuer = $shipping_costs->getCalculatedTaxes()->getElements()[19]->getTax();
  458.             $steuerschluessel = '9';
  459.             $steuerProzent = $shipping_costs->getCalculatedTaxes()->getElements()[19]->getTaxRate();
  460.             $positionstext = 'Versandkosten';
  461.             $lieferscheinNr = $belegnummer;
  463.             $artikelNr = 'ohne';
  464.             $menge = 1;
  465.             $me = 'Stück';
  466.                 
  467.             $produktart = 'Versandkosten';
  468.             
  469.             if(in_array($documentTechnicalName, $creditNote_types)){
  470.                   $betragNetto *= -1;
  471.                   $betragSteuer *= -1;
  472.             }
  473.             
  474.             $sql_shipmentPosition = 'INSERT INTO Shop2DWH_Belegposition ('
  475.             .' BelegkopfID , PositionsNr, BetragNetto, BetragSteuer, Steuerschluessel, '
  476.             .'SteuerProzent, PositionsText, LieferscheinNr, Leistungsdatum, '
  477.             .'ArtikelNr, Menge, ME, Produktart ) VALUES ( '
  478.             .$belegkopfId .', '
  479.             .'99998' .', '
  480.             .$betragNetto .', '
  481.             .$betragSteuer .', '
  482.             .'"' .$steuerschluessel .'", '
  483.             .$steuerProzent .', '
  484.             .'"' .$positionstext .'", '
  485.             .'"' .$lieferscheinNr .'", '
  486.             .'"' .$leistungsdatum .'", '
  487.             .'"' .$artikelNr .'", '
  488.             .$menge .', '
  489.             .'"' .$me .'", '
  490.             .'"' .$produktart .'" )';
  491.             
  492.             if(! $mysqli->query($sql_shipmentPosition)){
  493.                 // TODO sql-query failed
  494.             }
  495.             //dump("added shipment");
  496.         } 
  497.         //dump("finished");
  498.         } else {
  499.             //no-op
  500.         }
  501.     }
  502.     private function getZahlungsart($orderUUID, $orderVersionUUID, $languageUUID) {
  504.         // get the payment method as "Name"
  505.         SharedFunctions::dumpToLog("getZahlungsart()");
  507.         $dbHost = $this->systemConfigService->get('SteincoProductsController.config.externalDatabaseHost');
  508.         $dbUser = $this->systemConfigService->get('SteincoProductsController.config.externalDatabaseUser');
  509.         $dbPass = $this->systemConfigService->get('SteincoProductsController.config.externalDatabasePassword');
  510.         $dbName = $this->systemConfigService->get('SteincoProductsController.config.shopDatabaseName');
  511.         $mySql = new \mysqli($dbHost, $dbUser, $dbPass, $dbName);
  512.         if ($mySql->connect_errno) {
  513.             SharedFunctions::dumpToLog('Could not connect to MySQL: ' . $mySql->connect_error);
  514.             return "";
  515.         }
  516.         $zahlungsart = "";
  517.         try {
  518.             $sql = "
  519.                 SELECT 
  520.                     COALESCE(pmt.distinguishable_name, pmt.name) AS payment_method 
  521.                 FROM 
  522.                     order_transaction ot 
  523.                 JOIN 
  524.                     payment_method pm ON ot.payment_method_id = pm.id 
  525.                 JOIN 
  526.                     payment_method_translation pmt ON pm.id = pmt.payment_method_id 
  527.                 WHERE 
  528.                     ot.order_id = UNHEX('" . $orderUUID . "') 
  529.                     AND ot.order_version_id = UNHEX('" . $orderVersionUUID . "') 
  530.                     AND pmt.language_id = UNHEX('" . $languageUUID . "') 
  531.                 LIMIT 1
  532.             ";
  533.             $result = $mySql->query($sql);
  534.             if ($result) {
  535.                 $row = $result->fetch_assoc();
  536.                 if ($row) {
  537.                     $zahlungsart = $row['payment_method'];
  538.                     SharedFunctions::dumpToLog($result);
  539.                 } else {
  540.                     SharedFunctions::dumpToLog('Keine Daten gefunden. SQL: ' . $sql);
  541.                 }
  542.             } else {
  543.                 SharedFunctions::dumpToLog('Error executing: ' . $sql);
  544.                 SharedFunctions::dumpToLog('SQL-Error: ' . $mySql->error);
  545.             }
  546.         } catch (\Exception $e) {
  547.             // Logge die Fehlermeldung in eine Datei oder verarbeite den Fehler entsprechend
  548.             SharedFunctions::dumpToLog('Abfrage der Zahlungsart fehlgeschlagen: .', $sql);
  549.             SharedFunctions::dumpToLog($e->getMessage());
  550.         }
  551.         return $zahlungsart;
  552.     }
  554.     private function getPropertyOptions_by_name_and_propertydescription($propertyoptionname, $propertydescription)
  555.     {
  556.         SharedFunctions::dumpToLog("getPropertyOptions_by_name_and_propertydescription(): [" . $propertyoptionname . "], [" . $propertydescription . "]");
  557.         $property_uuid = $this->getPropertyUUID_by_description($propertydescription);
  558.         $property_filter = new EqualsFilter('groupId', $property_uuid);
  559.         $propertyoption_filter = new EqualsFilter('name', $propertyoptionname);
  560.         $search_criteria = new Criteria([]);
  561.         $search_criteria->addFilter($property_filter);
  562.         $search_criteria->addFilter($propertyoption_filter);
  563.         $search_results = $this->propertyOptionRepository->search($search_criteria,
  564.             $this->context)->getElements();
  565.         SharedFunctions::dumpToLog($search_results);
  566.         return $search_results;
  567.     }
  569.     private function getPropertyUUID_by_description($name) {
  570.         $filter = new EqualsFilter("description", $name);
  571.         $entity = $this->propertyRepository->search((new Criteria())->addFilter($filter), 
  572.         $this->context)->first();
  573.         if($entity) {
  574.             return $entity->getId();
  575.         }
  576.         return null;
  577.     }
  579.     private function getOrder($orderUUID){
  581.         $orderCriteria = new Criteria([]);
  582.         $orderCriteria->addAssociation('currency')
  583.         ->addAssociation('billingAddress')
  584.         ->addAssociation('lineItems')
  585.         ->addAssociation('lineItems.product')
  586.         ->addAssociation('lineItems.product.properties')
  587.         ->addAssociation('transactions')
  588.         ->addAssociation('transactions.paymentMethod')
  589.         ->addAssociation('paymentMethod')
  590.         ->addAssociation('billingAddress.country')
  591.         ->addFilter(new EqualsFilter('id', $orderUUID));
  593.         $order_searchresult = $this->orderRepository->search(
  594.             $orderCriteria,
  595.         \Shopware\Core\Framework\Context::createDefaultContext());
  596.         
  597.         $order = $order_searchresult->getEntities()->getElements()[$orderUUID];
  598.         return $order;
  599.     }
  601.     // file stuff
  602.     //TODO: what data do we need?: $orderID, $config? $documentTypeName?
  603.     private function writeFilesToExchangeFolder($orderId, $document){
  604.         $orderCriteria = new Criteria([]);
  605.         $orderCriteria->addAssociation('currency')->addAssociation('billingAddress')->addAssociation('salesChannel')->addAssociation('documents')->addAssociation('createdBy')->addFilter(new EqualsFilter('id', $orderId));
  606.         $order_searchresult = $this->orderRepository->search(
  607.             $orderCriteria,
  608.            \Shopware\Core\Framework\Context::createDefaultContext());
  609.         
  610.         $order = $order_searchresult->getEntities()->getElements()[$orderId];
  611.         $this->createJPLFile($order, $document->getConfig(), $document->getConfig()['name']);
  612.         // find the file and copy to the selected directory
  613.         $this->copyCreatedFileToFileExchangeDirectory($document->getId(), $this->context);
  614.     }
  616.     // taken from SchoettlerDocumentService
  617.     Private function copyCreatedFileToFileExchangeDirectory($documentId, $context) {
  618.         $outputdirectory = $this->systemConfigService->get('SteincoProductsController.config.fileOutputPath');
  619.         $archivedirectory = $this->systemConfigService->get('SteincoProductsController.config.archivefileOutputPath');
  620.         // find the source file
  621.         //dump($documentId);
  622.         $criteria = new Criteria([$documentId]);
  623.         $criteria->addAssociation('documentMediaFile');
  624.         $criteria->addAssociation('documentType');
  626.         /** @var DocumentEntity $document */
  627.         $document = $this->documentRepository->search($criteria, $context)->get($documentId);
  628.         $generatedDocument = $this->documentService->getDocument($document, $context);
  629.         if ((!$outputdirectory == NULL) and ($outputdirectory != "") and ($generatedDocument) and $generatedDocument->getFilename())  {
  630.             if((!$archivedirectory == NULL) and ($archivedirectory != "")) {
  631.                 $archivefilename = $archivedirectory .$generatedDocument->getFilename();
  632.                 $archivefile = fopen($archivefilename, "w");
  633.                 fwrite($archivefile, $generatedDocument->getFileBlob());
  634.                 fclose($archivefile);
  635.             }
  636.             $filename = $outputdirectory .$generatedDocument->getFilename();
  637.             $file = fopen($filename, "w");
  638.             fwrite($file, $generatedDocument->getFileBlob());
  639.             fclose($file);
  640.         }
  641.     }
  643.     Private function createJPLFile($order, $config, $documentTypeName) {
  644.         $myDocType = "unbekannt";
  645.         //$shopname = $context
  646.         $outputContent = null;
  647.         switch ($documentTypeName) {
  648.         case ("invoice"):
  649.             $myDocType = $this->DOK_RECHNUNG;
  650.             $outputContent = $this->getInvoiceOutputContent($order, $config);
  651.             break;
  652.         case ("credit_note"):
  653.             $myDocType= $this->DOK_GUTSCHRIFT;
  654.             $outputContent = $this->getCreditNoteOutput($order, $config);
  655.             break;
  656.         case ("delivery_note"):
  657.             $myDocType = $this->DOK_LIEFERSCHEIN;
  658.             $outputContent = $this->getDeliveryNoteOutput($order, $config);
  659.             break;
  660.         case ("storno"):
  661.             $myDocType = $this->DOK_RECHNUNGSSTORNO;
  662.             $outputContent = $this->getStornoBillOutput($order, $config);
  663.             break;
  664.             // No types for Gutschriftstorno, Auftragbestätigung implemented (yet)
  665.         }
  668.         if (!$outputContent) {
  669.             $outputContent = "Error while creating content of this file.";
  670.         }
  671.         $archivedirectory = $this->systemConfigService->get('SteincoProductsController.config.archivefileOutputPath');
  672.         $outputdirectory = $this->systemConfigService->get('SteincoProductsController.config.fileOutputPath');
  673.         if (!empty($outputdirectory)) {
  674.             if (!empty($archivedirectory)) {
  675.                 $archivefilename = $archivedirectory .$documentTypeName ."_" .$config['documentNumber'] .".jpl";
  676.                 $archivefile = fopen($archivefilename, "a");
  677.                 fwrite($archivefile, $outputContent);
  678.                 fclose($archivefile);
  679.             }
  680.             $filename = $outputdirectory .$documentTypeName ."_" .$config['documentNumber'] .".jpl";
  681.             $file = fopen($filename, "a");
  682.             fwrite($file, $outputContent);
  683.             fclose($file);
  684.         }
  685.         // some error: could not create file because of missing outputpath
  686.     }
  688.     private function getInvoiceOutputContent($order, $config) 
  689.     {
  690.         // $shopname -should be in context
  691.         // 
  692.         $customerName = $order->getOrderCustomer()->getCompany();
  693.         if (!$customerName) {
  694.             $customerName = $order->getOrderCustomer()->getLastName() .", ". $order->getOrderCustomer()->getFirstName();
  695.         } 
  696.         $bearbeiter = "shopware";
  697.         $result = 'zeich_nr= "Shop' .$this->DOK_RECHNUNG .$config['documentNumber'] .'"' .$this->LINEBREAK
  698.             .'dokuart= "' .$this->DOK_RECHNUNG .'"'.$this->LINEBREAK
  699.             .'logi_verzeichnis = "Fr"'.$this->LINEBREAK
  700.             .'dok_dat_feld[1] = "' .$order->getOrderCustomer()->getCustomerNumber() .'"'.$this->LINEBREAK
  701.             .'dok_dat_feld[2] = "' .$customerName .'"'.$this->LINEBREAK
  702.             .'dok_dat_feld[14] = "' .$config['documentNumber'] .'"'.$this->LINEBREAK
  703.             .'dok_dat_feld[38] = "' .$bearbeiter .'"'.$this->LINEBREAK
  704.             .'dok_dat_feld[39] = "Shop"'.$this->LINEBREAK
  705.             .'dok_dat_feld[51] = "' . date("d.m.Y") .'"'.$this->LINEBREAK;
  707.            
  708.             $result .= 'dok_dat_feld_60[1] = "' .$order->getOrderNumber() .'"' .$this->LINEBREAK;
  709.         return $result;
  710.     }
  712.     Private function getCreditNoteOutput($order, $config)
  713.     {
  714.         $bearbeiter = "shopware";
  715.         $customerName = $order->getOrderCustomer()->getCompany();
  716.         if (!$customerName) {
  717.             $customerName = $order->getOrderCustomer()->getLastName() .", ". $order->getOrderCustomer()->getFirstName();
  718.         } 
  719.         $result = 'zeich_nr = "Shop' .$this->DOK_GUTSCHRIFT .$config['documentNumber'] ."'" .$this->LINEBREAK
  720.             .'dokuart = "' .$this->DOK_GUTSCHRIFT .'"' .$this->LINEBREAK
  721.             .'var_nr = 1'.$this->LINEBREAK
  722.             .'logi_verzeichnis = "Fr"' .$this->LINEBREAK
  723.             .'dok_dat_feld[1] = "' .$order->getOrderCustomer()->getCustomerNumber() .'"'.$this->LINEBREAK
  724.             .'dok_dat_feld[2] = "' .$customerName .'"'.$this->LINEBREAK
  725.             .'dok_dat_feld[15] = "' .$config['documentNumber'] .'"'.$this->LINEBREAK
  726.             .'dok_dat_feld[38] = "' .$bearbeiter .'"' .$this->LINEBREAK
  727.             .'dok_dat_feld[39] = "Shop"'.$this->LINEBREAK
  728.             .'dok_dat_feld[51] = "' . date("d.m.Y") .'"'.$this->LINEBREAK;
  729.             // multiple Orders?
  730.         $result .= 'dok_dat_feld_60[1] = "' .$order->getOrderNumber() .'"' .$this->LINEBREAK;
  731.         return $result;
  732.     }
  734.     Private function getDeliveryNoteOutput($order, $config) 
  735.     {
  736.        /*  zeich_nr = "RollenILSVK823000817"                Dokument ID: "Shop", Dokumentart, Dokument Nr
  737.         dokuart = "ILSVK"                        Dokumentart
  738.         var_nr = "1"                            Konstante
  739.         logi_verzeichnis = "Fr"                        Konstante
  740.         dok_dat_feld[1] = "1209331"                    KundenNr
  741.         dok_dat_feld[2] = "PASOTEC GmbH"                KundenName
  742.         dok_dat_feld[13] = "823000817"                    Dokument Nr
  743.         dok_dat_feld[38] = "PURSCHKES"                    Bearbeiter
  744.         dok_dat_feld[39] = "Rollen"                    Shop (=Konstante) 
  745.         dok_dat_feld[51] = "12.10.2021"                    Datum
  746.         dok_dat_feld_60[1] = "KA53131"                    Auftrag
  747.          */
  748.         $bearbeiter = "shopware";
  749.         $customerName = $order->getOrderCustomer()->getCompany();
  750.         if (!$customerName) {
  751.             $customerName = $order->getOrderCustomer()->getLastName() .", ". $order->getOrderCustomer()->getFirstName();
  752.         } 
  753.         $result = 'zeich_nr = "Shop' .$this->DOK_LIEFERSCHEIN .$config['documentNumber'] .'"' .$this->LINEBREAK
  754.         .'dokuart = "' .$this->DOK_LIEFERSCHEIN .'"' .$this->LINEBREAK
  755.         .'var_nr = 1'.$this->LINEBREAK
  756.         .'logi_verzeichnis = "Fr"' .$this->LINEBREAK
  757.         .'dok_dat_feld[1] = "' .$order->getOrderCustomer()->getCustomerNumber() .'"'.$this->LINEBREAK
  758.         .'dok_dat_feld[2] = "' .$customerName .'"'.$this->LINEBREAK
  759.         .'dok_dat_feld[13] = "' .$config['documentNumber'] .'"'.$this->LINEBREAK
  760.         .'dok_dat_feld[38] = "' .$bearbeiter .'"' .$this->LINEBREAK
  761.         .'dok_dat_feld[39] = "Shop"'.$this->LINEBREAK
  762.         .'dok_dat_feld[51] = "' . date("d.m.Y") .'"'.$this->LINEBREAK;
  763.         $result .= 'dok_dat_feld_60[1] = "' .$order->getOrderNumber() .'"' .$this->LINEBREAK;
  765.         return $result;
  766.     }
  768.     Private function getStornoBillOutput($order, $config)
  769.     {
  770.        /*  zeich_nr = "RollenIGUVK32001379"                Dokument ID: "Shop", Dokumentart, Dokument Nr
  771.           dokuart = "IGUVK"                        Dokumentart
  772.           var_nr = "1"                            Konstante
  773.           logi_verzeichnis = "Fr"                        Konstante
  774.           dok_dat_feld[1] = "1201307"                    KundenNr
  775.           dok_dat_feld[2] = "Universitätsklinikum Dresden"        KundenName    
  776.           dok_dat_feld[15] = "32001379"                    Dokument Nr
  777.            dok_dat_feld[38] = "LINDHORSTJ"                    Bearbeiter
  778.            dok_dat_feld[39] = "Rollen"                    SHOP (=Konstante) 
  779.            dok_dat_feld[51] = "12.10.2021"                    Datum
  780.            dok_dat_feld_60[1] = "KA54091"                    Auftrag
  781.             dok_dat_feld_60[2] = "KA54091"                    Auftrag
  782.            dok_dat_feld_61[1] = "00050066"                    Lieferschein
  783.            dok_dat_feld_61[2] = "00050066"                    Lieferschein
  784.  */
  785.         $bearbeiter = "shopware";
  786.         $customerName = $order->getOrderCustomer()->getCompany();
  787.         if (!$customerName) {
  788.             $customerName = $order->getOrderCustomer()->getLastName() .", ". $order->getOrderCustomer()->getFirstName();
  789.         } 
  790.         $result = 'zeich_nr = "Shop' .$this->DOK_RECHNUNGSSTORNO .$config['documentNumber'] .'"' .$this->LINEBREAK
  791.         .'dokuart = "' .$this->DOK_RECHNUNGSSTORNO .'"' .$this->LINEBREAK
  792.         .'var_nr = 1'.$this->LINEBREAK
  793.         .'logi_verzeichnis = "Fr"' .$this->LINEBREAK
  794.         .'dok_dat_feld[1] = "' .$order->getOrderCustomer()->getCustomerNumber() .'"'.$this->LINEBREAK
  795.         .'dok_dat_feld[2] = "' .$customerName .'"'.$this->LINEBREAK
  796.         .'dok_dat_feld[15] = "' .$config['documentNumber'] .'"'.$this->LINEBREAK
  797.         .'dok_dat_feld[38] = "' .$bearbeiter .'"' .$this->LINEBREAK
  798.         .'dok_dat_feld[39] = "Shop"'.$this->LINEBREAK
  799.         .'dok_dat_feld[51] = "' . date("d.m.Y") .'"'.$this->LINEBREAK;
  800.         // in shopware, a storno bill references a single invoice and thus a single order
  801.         $invoiceNr = $config['custom']['invoiceNumber'] ?? '';
  802.         $result .= 'dok_dat_feld_60[1] = "' .$order->getOrderNumber() .'"' .$this->LINEBREAK
  803.             .'dok_dat_feld_61[1] = "' .$invoiceNr .'"' .$this->LINEBREAK;
  805.         return $result;
  806.     }
  808. }