symfony2: inserting data using eventListener

Question

I'm using symfony 2.3. I'm trying to generate an Invoice for every commercial every end of a month. I implemented a Listener called InvoiceListener in order to that. The problem is updateInvoice() function doesn't insert the data into invoice table. The system doesn't render errors. The doctrine debuger in displaying the query correctly.
InvoiceListener.php

class InvoiceListener {

  private $em;

  public function __construct(\Doctrine\ORM\EntityManager $em) {
      $this->em = $em;
  }

  public function process(GetResponseEvent $event) {

      $expire = '2012-01-01';
      $current_month = date("m");
      $current_year = date("y");
      if (date("d") == 1) {
          echo 'first day';
          $this->updateStatus($current_month, $current_year);
          $this->updateInvoice();
      } else {
          echo 'not first day';
          $users = $this->findByRole('ROLE_COMMERCIAL');
          // update invoice
          $this->updateInvoice($users);
      }
  }

  public function updateStatus($current_month, $current_year) {
      $queryBuilder = $this->em->createQueryBuilder();
      $queryBuilder
              ->update('Biginfo\UserBundle\Entity\User', 'u')
              ->set('u.nbrBusiness', 1)
              ->set('u.month', $current_month)
              ->set('u.year', $current_year);
      return $queryBuilder->getQuery()->getResult();
  }

  public function updateInvoice($users) {
      $queryBuilder = $this->em->createQueryBuilder();
      foreach ($users as $user) {
          $queryBuilder
                  ->update('Biginfo\AdminBundle\Entity\Invoice', 'c')
                  ->set('c.commercial', $user->getId())
                  ->set('c.month', $user->getMonth())
                  ->set('c.year', $user->getYear())
                  ->set('c.nbrBusiness', $user->getNbrBusiness());
      }
      return $queryBuilder->getQuery()->getResult();
  }

  /**
   * @param string $role
   *
   * @return array
   */
  public function findByRole($role) {
      $qb = $this->em->createQueryBuilder();
      $qb->select('u')
              ->from('Biginfo\UserBundle\Entity\User', 'u')
              ->where('u.roles LIKE :roles')
              ->setParameter('roles', '%"' . $role . '"%');

      return $qb->getQuery()->getResult();
  }

}

Answer 1

Unless I am missing something, your code will only update the same record over and over again, I don't think that's what you want. To generate new invoices, you should change it like this:

public function updateInvoice($users) {
    $queryBuilder = $this->em->createQueryBuilder();
    $queryBuilder->insert('Biginfo\AdminBundle\Entity\Invoice', 'c');
    foreach ($users as $user) {
        $queryBuilder->values(array(
            'c.commercial' => $user->getId(),
            'c.month' => $user->getMonth(),
            'c.year'=> $user->getYear(),
            'c.nbrBusiness' => $user->getNbrBusiness()
        ))
    }
    return $queryBuilder->getQuery()->getResult();
}

P.S. you should also use placeholders, see http://doctrine-dbal.readthedocs.org/en/latest/reference/query-builder.html#binding-parameters-to-placeholders

EDIT: "insert" command

The "insert" command has been added in Doctrine 2.5.0 BETA 3, so it may not be available in Symfony 2.3. An alternative to that is to use plain SQL, e.g.

public function updateInvoice($users) {
    $connection = $this->container->get('doctrine.dbal.default_connection');    
    $values = array();
    foreach ($users as $user) {
        $values[] = "(
            '" . $user->getId() . "',
            '" . $user->getMonth() . "',
            '" . $user->getYear() . "',
            '" . $user->getNbrBusiness() . "'
            )";
    }
    $sql = "INSERT INTO invoice (`commercial`, `month`, `year`, `nbrBusiness`) VALUES " . implode("," , $values);
    $statement = $connection->prepare($sql);
    $statement->execute();
}