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

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();
  284.             SharedFunctions::dumpToLog("Taxes");
  285.             SharedFunctions::dumpToLog($taxes);
  287.             $endbetrag_Steuerschluessel1 = !empty($taxes) ? reset($taxes)->getTax() : 0;
  288.             SharedFunctions::dumpToLog($endbetrag_Steuerschluessel1);
  289.             $endbetrag_Steuerschluessel2 = 0;
  290.             SharedFunctions::dumpToLog($endbetrag_Steuerschluessel2);
  292.             SharedFunctions::dumpToLog("Taxes done");
  294.             // Credit-note type should have negative values for money-related entries
  295.             if(in_array($documentTechnicalName, $creditNote_types)){
  296.               $skonto1Betrag *= -1;
  297.               $endbetrag *= -1;
  298.               $endbetrag_Steuerschluessel1 *= -1;
  299.               $endbetrag_Steuerschluessel2 *= -1;
  300.             }
  302.             $sql_belegkopf = 'INSERT INTO Shop2DWH_Belegkopf (' 
  303.             .'`Belegart`, ' 
  304.             .'`Belegnummer`, '
  305.             .'`BestellNr`, ' 
  306.             .'`Belegwaehrung`, ' 
  307.             .'`Belegdatum`, ' 
  308.             .'`Leistungsdatum`, ' 
  309.             .'`Erzeugungsdatum`, ' 
  310.             .'`Referenznr`, '
  311.             .'`Personenkonto`, ' 
  312.             .'`ZahlungsDienstleister_OP_ID`, ' 
  313.             .'`Valutadatum`, ' 
  314.             .'`Ziel1`, ' 
  315.             .'`Skonto1Betrag`, ' 
  316.             .'`Skonto1Prozent`, ' 
  317.             .'`FaelligAm`, ' 
  318.             .'`Land_Lieferadresse_ISO3`, '
  319.             .'`Land_Rechnungsadresse_ISO3`, '
  320.             .'`UST-ID`, '
  321.             .'`Endbetrag`, '
  322.             .'`Endbetrag_Steuerschluessel1`, '
  323.             .'`Endbetrag_Steuerschluessel2`, '
  324.             .'`Zahlungsart`'
  325.              .' )'
  326.             .' VALUES ( '
  327.             .'"' .$belegart .'", '
  328.             .'"' .$belegnummer .'", '
  329.             .'"' .$bestellnummer .'", '
  330.             .'"' .$belegwaehrung .'", '
  331.             .'"' .$belegdatum .'", '
  332.             .'"' .$leistungsdatum .'", '
  333.             .'"' .$erzeugungsdatum .'", '
  334.             .'"' .$referenznummer.'", '
  335.             .'"' .$personenkonto .'", '
  336.             .'"' .$zahlungsdienstleister_OP_ID .'", '
  337.             .'"' .$valutadatum .'", '
  338.             .'"' .$ziel1 .'", '
  339.             .'"' .$skonto1Betrag .'", '
  340.             .'"' .$skonto1Prozent .'", '
  341.             .'"' .$faelligAm .'", '
  342.             .'"' .$land_Lieferadresse_ISO3 .'", '
  343.             .'"' .$land_Rechnungsadresse_ISO3 .'", '
  344.             .'"' .$ust_id .'", '
  345.             .'"' .$endbetrag .'", '
  346.             .'"' .$endbetrag_Steuerschluessel1 .'", '
  347.             .'"' .$endbetrag_Steuerschluessel2 .'", '
  348.             .'"' .$zahlungsart .'"'
  349.             .' )';
  350.             
  351.             //dump($sql_belegkopf);
  352.             $mysqli = new \mysqli($dbHost, $dbUser, $dbPass, $dbName);
  353.             if($mysqli->connect_errno ) {
  354.                 // Log the error somehow
  355.                 //dump($mysqli->connect_errno);
  356.             }
  358.             
  359.             if(! $mysqli->query($sql_belegkopf)){
  360.                //dump(mysqli_error($mysqli), $sql_belegkopf);
  361.             }
  362. //dump("Belegkopf persisted");
  363.             // retrieve the belegkopfId
  364.             $belegkopfId = '';
  365.             $sql_getBelegkopf = 'SELECT BelegkopfID AS kopfid FROM Shop2DWH_Belegkopf WHERE Belegnummer = "' .$belegnummer .'" ';
  366.             if($get_belegkopfid_query = $mysqli->query($sql_getBelegkopf)) {
  367.                 $row = mysqli_fetch_array($get_belegkopfid_query);
  368.                 // dump($row);
  369.                 $belegkopfId = $row['kopfid'];
  370.                 $get_belegkopfid_query->close();
  371.             }
  372.             $optionSearchResult = $this->getPropertyOptions_by_name_and_propertydescription('1', 'istHandelwareWebshop');
  373.             $handelswareOption = reset($optionSearchResult);
  375.             if ($handelswareOption !== false) {
  376.                 $handelswareId = $handelswareOption->getId();
  377.             } else {
  378.                 // Umgang mit dem Fall, dass keine Option gefunden wurde
  379.                 SharedFunctions::dumpToLog('Keine Handelsware-Option gefunden.');
  380.                 $handelswareId = '<->'; // oder ein anderer sinnvoller Wert
  381.             }
  382.             // get necessary Data for Belegposition and write to external DB
  383.             $order_linteitems = $order->getLineItems()->getElements();
  384.             foreach($order_linteitems as $lineItem){
  385.                 $positionsnummer = $lineItem->getPosition();
  386.                 $betragNetto = $lineItem->getPrice()->getTotalPrice();
  387.                 $taxes = $order->getPrice()->getCalculatedTaxes()->getElements();
  388.                 SharedFunctions::dumpToLog("=================== 1 =======================");
  389.                 SharedFunctions::dumpToLog($taxes);
  390.                 $betragSteuer = !empty($taxes) ? reset($taxes)->getTax() : 0;
  391.                 $steuerschluessel = '9';
  392.                 $steuerProzent = !empty($taxes) ? reset($taxes)->getTaxRate() : 0;
  393. ;
  394.                 $positionstext = $lineItem->getDescription() ?? "";
  395.                 $lieferscheinNr = $belegnummer;
  397.                 $payload = $lineItem->getPayload();
  398.                 $artikelNr = array_key_exists('productNumber', $payload) ? $payload['productNumber'] : 'ohne';
  399.                 $menge = $lineItem->getQuantity();
  400.                 $me = 'Stück';
  401.                 
  402.                 $produktart = 'Eigenfertigung';
  403.                 if ($lineItem->getProduct()) {
  404.                     $propertyIds = $lineItem->getProduct()->getPropertyIds();
  405.                     if(in_array($handelswareId, $propertyIds)){
  406.                         $produktart = 'Handelsware';
  407.                     }
  408.                 } else {
  409.                     $produktart = 'Rabatt';
  410.                 }
  411.             /*
  412.                 Get all info about order and write to Shop2DWH_Belegkopf and Shop2DWH_Belegposition
  413.                 Belegkopf:
  414.                 Belegart, Belegnummer, BestellNr, Belegdatum, Leistungsdatum, Erzeugungsdatum, (Referenznummer), Personenkonto,
  415.                 Zahlungsdienstleister_OP_ID, Valutadatum, Ziel1, Skonto1Betrag, Skonto1Prozent, FaelligAm, Land_Lieferadresse_ISO3,
  416.                 Land_Rechnungsadresse_ISO3, UST-ID, Endbetrag, Endbetrag_Steuerschluessel1, Endbetrag_Steuerschluessel2, 
  417.                 
  418.                 Belegposition:
  419.                 *BelegkopfID int11
  420.                 PositionsNr int11
  421.                 BetragNetto decimal
  422.                 BetragSteuer decimal
  423.                 Steuerschluessel varchar: 9=> 19%, 5:0%
  424.                 SteuerProzent decimal(4,2) (???)
  425.                 PositionsText
  426.                 LieferscheinNr varchar15
  427.                 Leistungsdatum date
  428.                 ArtikelNr
  429.                 Menge int
  430.                 ME varchar(15) ???
  431.                 Produktart varchar(15) ???
  432.                  */
  433.                 // Credit-note type should have negative values for money-related entries
  434.                 if(in_array($documentTechnicalName, $creditNote_types)){
  435.                   $betragNetto *= -1;
  436.                   $betragSteuer *= -1;
  437.                 }
  439.                  $sql_belegposition = 'INSERT INTO Shop2DWH_Belegposition ('
  440.                  .' BelegkopfID , PositionsNr, BetragNetto, BetragSteuer, Steuerschluessel, '
  441.                  .'SteuerProzent, PositionsText, LieferscheinNr, Leistungsdatum, '
  442.                  .'ArtikelNr, Menge, ME, Produktart ) VALUES ( '
  443.                  .$belegkopfId .', '
  444.                  .$positionsnummer .', '
  445.                  .$betragNetto .', '
  446.                  .$betragSteuer .', '
  447.                  .'"' .$steuerschluessel .'", '
  448.                  .$steuerProzent .', '
  449.                  .'"' .$positionstext .'", '
  450.                  .'"' .$lieferscheinNr .'", '
  451.                  .'"' .$leistungsdatum .'", '
  452.                  .'"' .$artikelNr .'", '
  453.                  .$menge .', '
  454.                 .'"' .$me .'", '
  455.                 .'"' .$produktart .'" )';
  457. //dump($sql_belegposition);
  458.                 if(! $mysqli->query($sql_belegposition)){
  459.                     //dump(mysqli_error($mysqli), $sql_belegposition);
  460.                 }
  461.             
  462.             }
  463.             // add another position for shipment
  464.             $shipping_costs = $order->getShippingCosts();
  466.             $betragNetto = $shipping_costs->getTotalPrice();
  467.             $taxes = $shipping_costs->getCalculatedTaxes()->getElements();
  468.             SharedFunctions::dumpToLog("=================== 2 =======================");
  469.             SharedFunctions::dumpToLog($taxes);
  470.             $betragSteuer = !empty($taxes) ? reset($taxes)->getTax() : 0;
  471.             $steuerProzent = !empty($taxes) ? reset($taxes)->getTaxRate() : 0;
  472.             $steuerschluessel = '9';
  473.             $positionstext = 'Versandkosten';
  474.             $lieferscheinNr = $belegnummer;
  476.             $artikelNr = 'ohne';
  477.             $menge = 1;
  478.             $me = 'Stück';
  479.                 
  480.             $produktart = 'Versandkosten';
  481.             
  482.             if(in_array($documentTechnicalName, $creditNote_types)){
  483.                   $betragNetto *= -1;
  484.                   $betragSteuer *= -1;
  485.             }
  486.             
  487.             $sql_shipmentPosition = 'INSERT INTO Shop2DWH_Belegposition ('
  488.             .' BelegkopfID , PositionsNr, BetragNetto, BetragSteuer, Steuerschluessel, '
  489.             .'SteuerProzent, PositionsText, LieferscheinNr, Leistungsdatum, '
  490.             .'ArtikelNr, Menge, ME, Produktart ) VALUES ( '
  491.             .$belegkopfId .', '
  492.             .'99998' .', '
  493.             .$betragNetto .', '
  494.             .$betragSteuer .', '
  495.             .'"' .$steuerschluessel .'", '
  496.             .$steuerProzent .', '
  497.             .'"' .$positionstext .'", '
  498.             .'"' .$lieferscheinNr .'", '
  499.             .'"' .$leistungsdatum .'", '
  500.             .'"' .$artikelNr .'", '
  501.             .$menge .', '
  502.             .'"' .$me .'", '
  503.             .'"' .$produktart .'" )';
  504.             
  505.             if(! $mysqli->query($sql_shipmentPosition)){
  506.                 // TODO sql-query failed
  507.             }
  508.             //dump("added shipment");
  509.         } 
  510.         //dump("finished");
  511.         } else {
  512.             //no-op
  513.         }
  514.     }
  515.     private function getZahlungsart($orderUUID, $orderVersionUUID, $languageUUID) {
  517.         // get the payment method as "Name"
  518.         SharedFunctions::dumpToLog("getZahlungsart()");
  520.         $dbHost = $this->systemConfigService->get('SteincoProductsController.config.externalDatabaseHost');
  521.         $dbUser = $this->systemConfigService->get('SteincoProductsController.config.externalDatabaseUser');
  522.         $dbPass = $this->systemConfigService->get('SteincoProductsController.config.externalDatabasePassword');
  523.         $dbName = $this->systemConfigService->get('SteincoProductsController.config.shopDatabaseName');
  524.         $mySql = new \mysqli($dbHost, $dbUser, $dbPass, $dbName);
  525.         if ($mySql->connect_errno) {
  526.             SharedFunctions::dumpToLog('Could not connect to MySQL: ' . $mySql->connect_error);
  527.             return "";
  528.         }
  529.         $zahlungsart = "";
  530.         try {
  531.             $sql = "
  532.                 SELECT 
  533.                     COALESCE(pmt.distinguishable_name, pmt.name) AS payment_method 
  534.                 FROM 
  535.                     order_transaction ot 
  536.                 JOIN 
  537.                     payment_method pm ON ot.payment_method_id = pm.id 
  538.                 JOIN 
  539.                     payment_method_translation pmt ON pm.id = pmt.payment_method_id 
  540.                 WHERE 
  541.                     ot.order_id = UNHEX('" . $orderUUID . "') 
  542.                     AND ot.order_version_id = UNHEX('" . $orderVersionUUID . "') 
  543.                     AND pmt.language_id = UNHEX('" . $languageUUID . "') 
  544.                 LIMIT 1
  545.             ";
  546.             $result = $mySql->query($sql);
  547.             if ($result) {
  548.                 $row = $result->fetch_assoc();
  549.                 if ($row) {
  550.                     $zahlungsart = $row['payment_method'];
  551.                     SharedFunctions::dumpToLog($result);
  552.                 } else {
  553.                     SharedFunctions::dumpToLog('Keine Daten gefunden. SQL: ' . $sql);
  554.                 }
  555.             } else {
  556.                 SharedFunctions::dumpToLog('Error executing: ' . $sql);
  557.                 SharedFunctions::dumpToLog('SQL-Error: ' . $mySql->error);
  558.             }
  559.         } catch (\Exception $e) {
  560.             // Logge die Fehlermeldung in eine Datei oder verarbeite den Fehler entsprechend
  561.             SharedFunctions::dumpToLog('Abfrage der Zahlungsart fehlgeschlagen: .', $sql);
  562.             SharedFunctions::dumpToLog($e->getMessage());
  563.         }
  564.         return $zahlungsart;
  565.     }
  567.     private function getPropertyOptions_by_name_and_propertydescription($propertyoptionname, $propertydescription)
  568.     {
  569.         SharedFunctions::dumpToLog("getPropertyOptions_by_name_and_propertydescription(): [" . $propertyoptionname . "], [" . $propertydescription . "]");
  570.         $property_uuid = $this->getPropertyUUID_by_description($propertydescription);
  571.         $property_filter = new EqualsFilter('groupId', $property_uuid);
  572.         $propertyoption_filter = new EqualsFilter('name', $propertyoptionname);
  573.         $search_criteria = new Criteria([]);
  574.         $search_criteria->addFilter($property_filter);
  575.         $search_criteria->addFilter($propertyoption_filter);
  576.         $search_results = $this->propertyOptionRepository->search($search_criteria,
  577.             $this->context)->getElements();
  578.         SharedFunctions::dumpToLog($search_results);
  579.         return $search_results;
  580.     }
  582.     private function getPropertyUUID_by_description($name) {
  583.         $filter = new EqualsFilter("description", $name);
  584.         $entity = $this->propertyRepository->search((new Criteria())->addFilter($filter), 
  585.         $this->context)->first();
  586.         if($entity) {
  587.             return $entity->getId();
  588.         }
  589.         return null;
  590.     }
  592.     private function getOrder($orderUUID){
  594.         $orderCriteria = new Criteria([]);
  595.         $orderCriteria->addAssociation('currency')
  596.         ->addAssociation('billingAddress')
  597.         ->addAssociation('lineItems')
  598.         ->addAssociation('lineItems.product')
  599.         ->addAssociation('lineItems.product.properties')
  600.         ->addAssociation('transactions')
  601.         ->addAssociation('transactions.paymentMethod')
  602.         ->addAssociation('paymentMethod')
  603.         ->addAssociation('billingAddress.country')
  604.         ->addFilter(new EqualsFilter('id', $orderUUID));
  606.         $order_searchresult = $this->orderRepository->search(
  607.             $orderCriteria,
  608.         \Shopware\Core\Framework\Context::createDefaultContext());
  609.         
  610.         $order = $order_searchresult->getEntities()->getElements()[$orderUUID];
  611.         return $order;
  612.     }
  614.     // file stuff
  615.     //TODO: what data do we need?: $orderID, $config? $documentTypeName?
  616.     private function writeFilesToExchangeFolder($orderId, $document){
  617.         $orderCriteria = new Criteria([]);
  618.         $orderCriteria->addAssociation('currency')->addAssociation('billingAddress')->addAssociation('salesChannel')->addAssociation('documents')->addAssociation('createdBy')->addFilter(new EqualsFilter('id', $orderId));
  619.         $order_searchresult = $this->orderRepository->search(
  620.             $orderCriteria,
  621.            \Shopware\Core\Framework\Context::createDefaultContext());
  622.         
  623.         $order = $order_searchresult->getEntities()->getElements()[$orderId];
  624.         $this->createJPLFile($order, $document->getConfig(), $document->getConfig()['name']);
  625.         // find the file and copy to the selected directory
  626.         $this->copyCreatedFileToFileExchangeDirectory($document->getId(), $this->context);
  627.     }
  629.     // taken from SchoettlerDocumentService
  630.     Private function copyCreatedFileToFileExchangeDirectory($documentId, $context) {
  631.         $outputdirectory = $this->systemConfigService->get('SteincoProductsController.config.fileOutputPath');
  632.         $archivedirectory = $this->systemConfigService->get('SteincoProductsController.config.archivefileOutputPath');
  633.         // find the source file
  634.         //dump($documentId);
  635.         $criteria = new Criteria([$documentId]);
  636.         $criteria->addAssociation('documentMediaFile');
  637.         $criteria->addAssociation('documentType');
  639.         /** @var DocumentEntity $document */
  640.         $document = $this->documentRepository->search($criteria, $context)->get($documentId);
  641.         $generatedDocument = $this->documentService->getDocument($document, $context);
  642.         if ((!$outputdirectory == NULL) and ($outputdirectory != "") and ($generatedDocument) and $generatedDocument->getFilename())  {
  643.             if((!$archivedirectory == NULL) and ($archivedirectory != "")) {
  644.                 $archivefilename = $archivedirectory .$generatedDocument->getFilename();
  645.                 $archivefile = fopen($archivefilename, "w");
  646.                 fwrite($archivefile, $generatedDocument->getFileBlob());
  647.                 fclose($archivefile);
  648.             }
  649.             $filename = $outputdirectory .$generatedDocument->getFilename();
  650.             $file = fopen($filename, "w");
  651.             fwrite($file, $generatedDocument->getFileBlob());
  652.             fclose($file);
  653.         }
  654.     }
  656.     Private function createJPLFile($order, $config, $documentTypeName) {
  657.         $myDocType = "unbekannt";
  658.         //$shopname = $context
  659.         $outputContent = null;
  660.         switch ($documentTypeName) {
  661.         case ("invoice"):
  662.             $myDocType = $this->DOK_RECHNUNG;
  663.             $outputContent = $this->getInvoiceOutputContent($order, $config);
  664.             break;
  665.         case ("credit_note"):
  666.             $myDocType= $this->DOK_GUTSCHRIFT;
  667.             $outputContent = $this->getCreditNoteOutput($order, $config);
  668.             break;
  669.         case ("delivery_note"):
  670.             $myDocType = $this->DOK_LIEFERSCHEIN;
  671.             $outputContent = $this->getDeliveryNoteOutput($order, $config);
  672.             break;
  673.         case ("storno"):
  674.             $myDocType = $this->DOK_RECHNUNGSSTORNO;
  675.             $outputContent = $this->getStornoBillOutput($order, $config);
  676.             break;
  677.             // No types for Gutschriftstorno, Auftragbestätigung implemented (yet)
  678.         }
  681.         if (!$outputContent) {
  682.             $outputContent = "Error while creating content of this file.";
  683.         }
  684.         $archivedirectory = $this->systemConfigService->get('SteincoProductsController.config.archivefileOutputPath');
  685.         $outputdirectory = $this->systemConfigService->get('SteincoProductsController.config.fileOutputPath');
  686.         if (!empty($outputdirectory)) {
  687.             if (!empty($archivedirectory)) {
  688.                 $archivefilename = $archivedirectory .$documentTypeName ."_" .$config['documentNumber'] .".jpl";
  689.                 $archivefile = fopen($archivefilename, "a");
  690.                 fwrite($archivefile, $outputContent);
  691.                 fclose($archivefile);
  692.             }
  693.             $filename = $outputdirectory .$documentTypeName ."_" .$config['documentNumber'] .".jpl";
  694.             $file = fopen($filename, "a");
  695.             fwrite($file, $outputContent);
  696.             fclose($file);
  697.         }
  698.         // some error: could not create file because of missing outputpath
  699.     }
  701.     private function getInvoiceOutputContent($order, $config) 
  702.     {
  703.         // $shopname -should be in context
  704.         // 
  705.         $customerName = $order->getOrderCustomer()->getCompany();
  706.         if (!$customerName) {
  707.             $customerName = $order->getOrderCustomer()->getLastName() .", ". $order->getOrderCustomer()->getFirstName();
  708.         } 
  709.         $bearbeiter = "shopware";
  710.         $result = 'zeich_nr= "Shop' .$this->DOK_RECHNUNG .$config['documentNumber'] .'"' .$this->LINEBREAK
  711.             .'dokuart= "' .$this->DOK_RECHNUNG .'"'.$this->LINEBREAK
  712.             .'logi_verzeichnis = "Fr"'.$this->LINEBREAK
  713.             .'dok_dat_feld[1] = "' .$order->getOrderCustomer()->getCustomerNumber() .'"'.$this->LINEBREAK
  714.             .'dok_dat_feld[2] = "' .$customerName .'"'.$this->LINEBREAK
  715.             .'dok_dat_feld[14] = "' .$config['documentNumber'] .'"'.$this->LINEBREAK
  716.             .'dok_dat_feld[38] = "' .$bearbeiter .'"'.$this->LINEBREAK
  717.             .'dok_dat_feld[39] = "Shop"'.$this->LINEBREAK
  718.             .'dok_dat_feld[51] = "' . date("d.m.Y") .'"'.$this->LINEBREAK;
  720.            
  721.             $result .= 'dok_dat_feld_60[1] = "' .$order->getOrderNumber() .'"' .$this->LINEBREAK;
  722.         return $result;
  723.     }
  725.     Private function getCreditNoteOutput($order, $config)
  726.     {
  727.         $bearbeiter = "shopware";
  728.         $customerName = $order->getOrderCustomer()->getCompany();
  729.         if (!$customerName) {
  730.             $customerName = $order->getOrderCustomer()->getLastName() .", ". $order->getOrderCustomer()->getFirstName();
  731.         } 
  732.         $result = 'zeich_nr = "Shop' .$this->DOK_GUTSCHRIFT .$config['documentNumber'] ."'" .$this->LINEBREAK
  733.             .'dokuart = "' .$this->DOK_GUTSCHRIFT .'"' .$this->LINEBREAK
  734.             .'var_nr = 1'.$this->LINEBREAK
  735.             .'logi_verzeichnis = "Fr"' .$this->LINEBREAK
  736.             .'dok_dat_feld[1] = "' .$order->getOrderCustomer()->getCustomerNumber() .'"'.$this->LINEBREAK
  737.             .'dok_dat_feld[2] = "' .$customerName .'"'.$this->LINEBREAK
  738.             .'dok_dat_feld[15] = "' .$config['documentNumber'] .'"'.$this->LINEBREAK
  739.             .'dok_dat_feld[38] = "' .$bearbeiter .'"' .$this->LINEBREAK
  740.             .'dok_dat_feld[39] = "Shop"'.$this->LINEBREAK
  741.             .'dok_dat_feld[51] = "' . date("d.m.Y") .'"'.$this->LINEBREAK;
  742.             // multiple Orders?
  743.         $result .= 'dok_dat_feld_60[1] = "' .$order->getOrderNumber() .'"' .$this->LINEBREAK;
  744.         return $result;
  745.     }
  747.     Private function getDeliveryNoteOutput($order, $config) 
  748.     {
  749.        /*  zeich_nr = "RollenILSVK823000817"                Dokument ID: "Shop", Dokumentart, Dokument Nr
  750.         dokuart = "ILSVK"                        Dokumentart
  751.         var_nr = "1"                            Konstante
  752.         logi_verzeichnis = "Fr"                        Konstante
  753.         dok_dat_feld[1] = "1209331"                    KundenNr
  754.         dok_dat_feld[2] = "PASOTEC GmbH"                KundenName
  755.         dok_dat_feld[13] = "823000817"                    Dokument Nr
  756.         dok_dat_feld[38] = "PURSCHKES"                    Bearbeiter
  757.         dok_dat_feld[39] = "Rollen"                    Shop (=Konstante) 
  758.         dok_dat_feld[51] = "12.10.2021"                    Datum
  759.         dok_dat_feld_60[1] = "KA53131"                    Auftrag
  760.          */
  761.         $bearbeiter = "shopware";
  762.         $customerName = $order->getOrderCustomer()->getCompany();
  763.         if (!$customerName) {
  764.             $customerName = $order->getOrderCustomer()->getLastName() .", ". $order->getOrderCustomer()->getFirstName();
  765.         } 
  766.         $result = 'zeich_nr = "Shop' .$this->DOK_LIEFERSCHEIN .$config['documentNumber'] .'"' .$this->LINEBREAK
  767.         .'dokuart = "' .$this->DOK_LIEFERSCHEIN .'"' .$this->LINEBREAK
  768.         .'var_nr = 1'.$this->LINEBREAK
  769.         .'logi_verzeichnis = "Fr"' .$this->LINEBREAK
  770.         .'dok_dat_feld[1] = "' .$order->getOrderCustomer()->getCustomerNumber() .'"'.$this->LINEBREAK
  771.         .'dok_dat_feld[2] = "' .$customerName .'"'.$this->LINEBREAK
  772.         .'dok_dat_feld[13] = "' .$config['documentNumber'] .'"'.$this->LINEBREAK
  773.         .'dok_dat_feld[38] = "' .$bearbeiter .'"' .$this->LINEBREAK
  774.         .'dok_dat_feld[39] = "Shop"'.$this->LINEBREAK
  775.         .'dok_dat_feld[51] = "' . date("d.m.Y") .'"'.$this->LINEBREAK;
  776.         $result .= 'dok_dat_feld_60[1] = "' .$order->getOrderNumber() .'"' .$this->LINEBREAK;
  778.         return $result;
  779.     }
  781.     Private function getStornoBillOutput($order, $config)
  782.     {
  783.        /*  zeich_nr = "RollenIGUVK32001379"                Dokument ID: "Shop", Dokumentart, Dokument Nr
  784.           dokuart = "IGUVK"                        Dokumentart
  785.           var_nr = "1"                            Konstante
  786.           logi_verzeichnis = "Fr"                        Konstante
  787.           dok_dat_feld[1] = "1201307"                    KundenNr
  788.           dok_dat_feld[2] = "Universitätsklinikum Dresden"        KundenName    
  789.           dok_dat_feld[15] = "32001379"                    Dokument Nr
  790.            dok_dat_feld[38] = "LINDHORSTJ"                    Bearbeiter
  791.            dok_dat_feld[39] = "Rollen"                    SHOP (=Konstante) 
  792.            dok_dat_feld[51] = "12.10.2021"                    Datum
  793.            dok_dat_feld_60[1] = "KA54091"                    Auftrag
  794.             dok_dat_feld_60[2] = "KA54091"                    Auftrag
  795.            dok_dat_feld_61[1] = "00050066"                    Lieferschein
  796.            dok_dat_feld_61[2] = "00050066"                    Lieferschein
  797.  */
  798.         $bearbeiter = "shopware";
  799.         $customerName = $order->getOrderCustomer()->getCompany();
  800.         if (!$customerName) {
  801.             $customerName = $order->getOrderCustomer()->getLastName() .", ". $order->getOrderCustomer()->getFirstName();
  802.         } 
  803.         $result = 'zeich_nr = "Shop' .$this->DOK_RECHNUNGSSTORNO .$config['documentNumber'] .'"' .$this->LINEBREAK
  804.         .'dokuart = "' .$this->DOK_RECHNUNGSSTORNO .'"' .$this->LINEBREAK
  805.         .'var_nr = 1'.$this->LINEBREAK
  806.         .'logi_verzeichnis = "Fr"' .$this->LINEBREAK
  807.         .'dok_dat_feld[1] = "' .$order->getOrderCustomer()->getCustomerNumber() .'"'.$this->LINEBREAK
  808.         .'dok_dat_feld[2] = "' .$customerName .'"'.$this->LINEBREAK
  809.         .'dok_dat_feld[15] = "' .$config['documentNumber'] .'"'.$this->LINEBREAK
  810.         .'dok_dat_feld[38] = "' .$bearbeiter .'"' .$this->LINEBREAK
  811.         .'dok_dat_feld[39] = "Shop"'.$this->LINEBREAK
  812.         .'dok_dat_feld[51] = "' . date("d.m.Y") .'"'.$this->LINEBREAK;
  813.         // in shopware, a storno bill references a single invoice and thus a single order
  814.         $invoiceNr = $config['custom']['invoiceNumber'] ?? '';
  815.         $result .= 'dok_dat_feld_60[1] = "' .$order->getOrderNumber() .'"' .$this->LINEBREAK
  816.             .'dok_dat_feld_61[1] = "' .$invoiceNr .'"' .$this->LINEBREAK;
  818.         return $result;
  819.     }
  821. }