php - How to integrate input from DateTimeInterface into Doctrine DBAL query builder?

I am very new to PHP and am working on a plugin for Shopware (5.6). The goal is to extend the shopware statistics by adding a new tab displaying a table of preordered items (article id, amount of preorders, $-value).

The plugin runs successfully and have been able to display the desired info, however I am unable to integrate input from the DateTimeInterface. I want the date input there to correspond with the release date of the preordered items, so I can filter for a specific timeframe and see which preordered articles are due to be delivered during that time.

The following code referrs referrs to the Shopware Controller:

Old code (working, but obviously no DateTimeInterface integration) As a result i get a table with ALL preordered items

declare(strict_types=1);


class Shopware_Controllers_Backend_CustomStatsController extends Shopware_Controllers_Backend_ExtJs {
  /**
   * calls the getPreorderSubsAction function that connects with the database and
   * delivers the content for the statistics table
   *
   * @return void
   */
  public function getPreorderSubsAction() {
    $connection = $this->container->get('dbal_connection');
    $query = $connection->createQueryBuilder();
    $query->select([
      'ps.abo',
      'smao.name',
      'ROUND(SUM(ps.preordered * ps.unit_price),2) AS preorder_value'
      ])
      ->from('vw_PreorderSubs', 'ps')
      ->join('ps', 'salt_model_abo', 'smao', 'ps.abo = smao.id')
      ->where('ps.latest_issue_esd <= NOW()')
      ->groupBy('ps.abo');

    $data = $query->execute()->fetchAll();

    $this->View()->assign([
        'success' => true,
        'data' => $data,
        'count' => count($data)
    ]);
  }
}

New code: (not working), when choosing the statistics tab it looks like the query is simply empty, since nothing can be found. But for the chosen timeframe i should be getting a list of 13 items.

<?php
declare(strict_types=1);

class Shopware_Controllers_Backend_SaltCustomStatsController extends Shopware_Controllers_Backend_ExtJs {
  /**
   * calls the getPreorderSubsAction function that connects with the database and
   * delivers the content for the statistics table
   *
   * @return void
   */
  public function getPreorderSubsAction(\DateTimeInterface $from = null, \DateTimeInterface $to = null){
    $connection = $this->container->get('dbal_connection');
    $query = $connection->createQueryBuilder($from, $to);
    $query->select([
      'ps.abo',
      'smao.name',
      'ROUND(SUM(ps.preordered * ps.unit_price),2) AS preorder_value'
      ])
      ->from('vw_PreorderSubs', 'ps')
      ->join('ps', 's_model_abo', 'smao', 'ps.abo = smao.id')
      ->where('ps.latest_issue_esd <= NOW()')
      ->andWhere('ps.order_date <= "?"')
      ->andWhere('ps.order_date >= "?"')
      ->groupBy('ps.abo')
      ->setParameter(0, $from)
      ->setParameter(1, $to)
      ;

    $data = $query->execute()->fetchAll();

    $this->View()->assign([
        'success' => true,
        'data' => $data,
        'count' => count($data)
    ]);
  }
}

Variation i tried unsuccessfully:

->from('vw_PreorderSubs', 'ps')
      ->join('ps', 's_model_abo', 'smao', 'ps.abo = smao.id')
      ->where('ps.latest_issue_esd <= NOW()')
      ->andWhere('ps.order_date between "?" and "?"')
      ->groupBy('ps.abo')
      ->setParameter(0, $from)
      ->setParameter(1, $to)
      ;

I am sure it is something obvious. Since it doesn't throw an error, the code seems to be working, as if the date input itself is correct, but there are just no results to show.

How do i get the code to accept input from the DateTimeInterface in the Shopware backend correctly and insert it into the query?

The format of the ps.order_date field is YYYY-MM-DD. `

Solution:

The solution turns out to be fairly easy: GET /backend/SaltCustomStatsController/getPreorderEbooks?_dc=1668154802232&node=root&fromDate=2022-10-11T00%3A00%3A00&toDate=2022-11-11T09%3A19%3A57&page=1&start=0&limit=25 HTTP/1.1

The selected dates from the interface are part of the GET request.

Here is how they were integrated in the code:

public function getPreorderSubsAction(){
    $connection = $this->container->get('dbal_connection');
    $query = $connection->createQueryBuilder();
    $query->select([
      'ps.abo',
      'smao.name',
      'ROUND(SUM(ps.preordered * ps.unit_price),2) AS preorder_value'
      ])
      ->from('vw_PreorderSubs', 'ps')
      ->join('ps', 's_model_abo', 'smao', 'ps.abo = smao.id')
      ->where('ps.latest_issue_esd <= NOW()')
      ->andWhere('ps.order_date between :from and :to')
      ->setParameter('from', $this->getFromDate()->format('Y-m-d H:i:s'))
      ->setParameter('to', $this->getToDate()->format('Y-m-d H:i:s'))
      ->groupBy('ps.abo');

    $data = $query->execute()->fetchAll();

    $this->View()->assign([
        'success' => true,
        'data' => $data,
        'count' => count($data)
    ]);
  }

I added helper functions from Shopware\Controllers\Backend\Analystics to define the variables:

  /**
   * helper to get the from date in the right format
   *
   * return \DateTimeInterface | fromDate
   */
  private function getFromDate()
  {
      $fromDate = $this->Request()->getParam('fromDate');
      if (empty($fromDate)) {
          $fromDate = new \DateTime();
          $fromDate = $fromDate->sub(new \DateInterval('P1M'));
      } else {
          $fromDate = new \DateTime($fromDate);
      }

      return $fromDate;
  }

  /**
   * helper to get the to date in the right format
   *
   * return DateTime | toDate
   */
  private function getToDate()
  {
      //if a to date passed, format it over the \DateTime object. Otherwise create a new date with today
      $toDate = $this->Request()->getParam('toDate');
      if (empty($toDate)) {
          $toDate = new \DateTime();
      } else {
          $toDate = new \DateTime($toDate);
      }
      //to get the right value cause 2012-02-02 is smaller than 2012-02-02 15:33:12
      $toDate = $toDate->add(new \DateInterval('P1D'));
      $toDate = $toDate->sub(new \DateInterval('PT1S'));

      return $toDate;
  }

Now everythings works as it should.