Symfony / Doctrine - query date values against existing database values

Question

I have two entities payroll week and payroll period, want to write a query that checks if the start date and end date entered by the user to the payroll period table is found in the payroll week table which is populated already and update it with that payroll period id in the database. Each classes looks like. This is what I have done thus far regarding the query.

Payrollperiod Entity:

<?php

namespace com\twcl\agripayrollBundle\Entity;

use Doctrine\ORM\Mapping as ORM;
use Doctrine\Common\Collections\ArrayCollection;

/**
 * Payrollperiod
 *
 * @ORM\Table(name="PayrollPeriod")
 * @ORM\Entity
 */
class Payrollperiod
{
    /**
     * @var integer
     *
     * @ORM\Column(name="payrollperiodid", type="integer", nullable=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $payrollperiodid;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="startDate", type="datetime", nullable=false)
     */
    private $startdate;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="endDate", type="datetime", nullable=false)
     */
    private $enddate;

    /**
     * @var integer
     *
     * @ORM\Column(name="State", type="integer", nullable=false)
     */
    private $state;

    public function getPayrollperiodid() {
        return $this->payrollperiodid;
    }

    public function getStartdate() {
        return $this->startdate;
    }

    public function getEnddate() {
        return $this->enddate;
    }

    public function getState() {
        return $this->state;
    }

    public function setPayrollperiodid($payrollperiodid) {
        $this->payrollperiodid = $payrollperiodid;
    }

    public function setStartdate(\DateTime $startdate) {
        $this->startdate = $startdate;
    }

    public function setEnddate(\DateTime $enddate) {
        $this->enddate = $enddate;
    }

    public function setState($state) {
        $this->state = $state;
    }

    /**
     * Render a payrollPeriodID as a string.
     *
     * @return string
     */
    public function __toString()
    {
        return (string) $this->getPayrollperiodid();
    }
}

Payrollweek Entity:

<?php

namespace com\twcl\agripayrollBundle\Entity;

use Doctrine\ORM\Mapping as ORM;
use Symfony\Component\Validator\Constraints as Assert;

/**
 * Payrollweek
 *
 * @ORM\Table(name="PayrollWeek", indexes={@ORM\Index(name="IDX_1B4F90669AD94696", columns={"payrollperiodid"})})
 * @ORM\Entity
 */
class Payrollweek
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer", nullable=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="startDate", type="datetime", nullable=false)
     * @Assert\Type("DateTime")
     */
    private $startdate;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="endDate", type="datetime", nullable=false)
     * @Assert\Type("DateTime")
     * @Assert\Expression("this.getStartDate() < this.getEndDate()",
     *     message="The end date must be after the start date")
     */
    private $enddate;

    /**
     * @var integer
     *
     * @ORM\Column(name="normalHours", type="integer", nullable=true)
     */
    private $normalhours;

    /**
     * @var integer
     *
     * @ORM\Column(name="numOfDays", type="integer", nullable=true)
     */
    private $numofdays;

    /**
     * @var \Payrollperiod
     *
     * @ORM\ManyToOne(targetEntity="Payrollperiod")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="payrollperiodid", referencedColumnName="id", nullable=false)
     * })
     */
    private $payrollperiodid;

    public function getId() {
        return $this->id;
    }

    public function getStartdate() {
        return $this->startdate;
    }

    public function getEnddate() {
        return $this->enddate;
    }

    public function getNormalhours() {
        return $this->normalhours;
    }

    public function getNumofdays() {
        return $this->numofdays;
    }

    public function getPayrollperiodid() {
        return $this->payrollperiodid;
    }

    public function setId($id) {
        $this->id = $id;
    }

    public function setStartdate(\DateTime $startdate) {
        $this->startdate = $startdate;
    }

    public function setEnddate(\DateTime $enddate) {
        $this->enddate = $enddate;
    }

    public function setNormalhours($normalhours) {
        $this->normalhours = $normalhours;
    }

    public function setNumofdays($numofdays) {
        $this->numofdays = $numofdays;
    }

    public function setPayrollperiodid($payrollperiodid) {
        $this->payrollperiodid = $payrollperiodid;
    }

    /** * Render StartDate and EndDate as a string.
     * * * @return string */

    public function __toString()
    {
        return (string) $this->getStartdate()->format('d-M-y').'/'.$this->getEnddate()->format('d-M-y');

    }

    public function __construct()
    {
        $this->PayrollweekType = new \Doctrine\Common\Collection\ArrayCollection();
    }
}

Payrollweek Entity Repository:

namespace com\twcl\agripayrollBundle\Entity;

use Doctrine\ORM\EntityRepository;

/**
 * PayrollweekRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class PayrollweekRepository extends EntityRepository
{
    public function payrollPeriodWeek(){
       return $this->EntityManager()->createquery(
               'select startDate and endDate from AcmeDemoBundle:Payrollweek 
               update payrollperiodid' 
               )->getresults() ;
    }
}

CHANGES:

public function createAction(Request $request)
{
    $entity = new Payrollperiod();
    $form = $this->createCreateForm($entity);
    $form->handleRequest($request);

    if ($form->isValid()) {
        $em = $this->getDoctrine()->getManager();
        $payrollweek=new Payrollweek();
        $payrollperiod=$em->getRepository('comtwclagripayrollBundle:PayrollPeriod') ->findOneBy(['startDate' => $startDate, 'endDate' => $endDate]);
        if ($payrollperiod) {
        $payrollweek->setPayrollperiod($payrollperiod);
        }
        else{
        $this->addFlash('error','ERROR! Not a valid payroll week');
         return $this->redirect($this->generateUrl('payrollperiod'));
        }

        $em->persist($payrollweek);
        $em->flush();

        //return $this->redirect($this->generateUrl('payrollperiod_show', array('payrollperiodid' => $entity->getpayrollperiodid())));
    }

    return array(
        'entity' => $entity,
        'form'   => $form->createView(),
    );
}

Answer 1

Here is an example. If your payroll period already exists that is needed for a PayrollWeek, you can build your form to return PayrollPeriods to select from instead of querying for it after the form is submitted.

In this example, Territory belongs to a Country. So the form will display a list of countries.

$builder
    ->add('name', TextType::class, [
        'label' => 'Territory Name',
    ])
    ->add('abbrev', TextType::class, [
        'label' => 'Territory Abbreviation',
    ])
    ->add('belongingCountry', EntityType::class, [
        'class'         => 'AppBundle:Address\Country',
        'choice_label'  => 'name',
        'label'         => 'Country'
    ]);

In your case, if PayrollWeek belongs to PayrollPeriod. You can do the same.

$builder
    ->add('payrollperiodid', EntityType::class, [
        'class'         => 'AppBundle:Payrollperiod',
        'choice_label'  => 'startdate'
    ])

You will have to mess with the choice_label a bit to format the display of the \DateTime as you want.

Answer 2

First you'll want to get the PayrollPeriod id, and then update your Payrollweek entity accordingly. You don't even need any special repository functions. If you were in a controller it would look like this:

$em = $this->getDoctrine()->getManager();

$payrollweek = // do whatever here to get a Payrollweek entity

$payrollperiod = $em->getRepository('agripayroll:\PayrollPeriod)
    ->findOneBy(['startDate' => $startDate, 'endDate' => $endDate]);

if ($payrollperiod) {
    $payrollweek->setPayrollperiod($payrollperiod);

    $em->persist($payrollweek);
    $em->flush();
}

You don't even need to use any custom repository functions, although it wouldn't hurt to put them in there. I'd also change your $payrollperiodid class member to $payrollperiod since it refers to the corresponding Payrollperiod entity, and not the individual foreign key integer id.