Looking for a clean way to export data to a CSV that is previously used in a different controller ro-CodePudding

I have a page that has a form that generates a query that renders relevant information from my database: Render Example

The controller function I use creates the form using Symfony/Doctrine and then calls on a repository function to query the database based on the fields the user has supplied. I then display these results in a table in a twig file for the user to examine.

My issue is I'm fairly new to Symfony and am having an issue then creating an 'export' button where I can somehow grab the form's data that was passed in so I can re-run the repository function in a new route, OR pass the data from the previous query itself to a function in a new route in the controller where I can then turn this data into a CSV file.

I have it working in what feels like a very poor way currently, by adding hidden input fields in the twig file and then grabbing the values from each field and putting together objects in a controller function that I then turn into a CVS.

I'm looking for suggestions on a clean way to get either form data or query data from my exportAction function/route in my ServiceController into a new exportServiceAction function/route (or any other advised methods to accomplish the same goal)

Controller Function

/**
 * Creates a form for Exporting Services associated with techs
 * @Route("/export", name="service_export", methods={"GET", "POST"})
 */
public function exportActions(Request $request, EntityManagerInterface $em)
{
    $staffEntities = $em->getRepository('AppBundle:User')->buildFindByRole('ROLE_STAFF')->getQuery()->getResult();
    $staffOptions = [];
    foreach ($staffEntities as $staff) {
        $staffUsername = $staff->getUsername();
        if(!array_key_exists($staffUsername, $staffOptions)) {
            $staffOptions[$staffUsername] = $staffUsername;
        }
    }

    $campusEntities = $em->getRepository('AppBundle:Campus')->findBy([], ['name' => 'ASC']);
    $campusOptions = [];
    foreach ($campusEntities as $campus) {
        $campusName = $campus->getName();
        if (!array_key_exists($campusName, $campusOptions)) {
            $campusOptions[$campusName] = $campusName;
        }
    }

    $buildingEntities = $em->getRepository('AppBundle:Building')->findBy([], ['name' => 'ASc']);
    $buildingOptions = [];
    foreach ($buildingEntities as $building) {
        $buildingName = $building->getName();
        if (!array_key_exists($buildingName, $buildingOptions)) {
            $buildingOptions[$buildingName] = $buildingName;
        }
    }

    $roomEntities = $em->getRepository('AppBundle:Room')->findBy([], ['name' => 'ASC']);
    $roomOptions = [];
    foreach ($roomEntities as $room) {
        $roomName = $room->getName();
        if (!array_key_exists($roomName, $roomOptions)) {
            $roomOptions[$roomName] = $roomName;
        }
    }

    $options = [
        'techs' => $staffOptions,
        'campuses' => $campusOptions,
        'buildings' => $buildingOptions,
        'rooms' => $roomOptions,
    ];

    $searchForm = $this->createForm('AppBundle\Form\SearchServiceType', [], $options);

    $searchForm->handleRequest($request);
    if($searchForm->isSubmitted() && $searchForm->isValid()) {
        $data = $searchForm->getData();
        dump($data);
        $queriedAssignments = $em->getRepository('AppBundle:Service')->findForExport($data);

        if (empty($queriedAssignments)) {
            $queriedAssignments = ["errorMessage" => "No Results Found!"];
        }
    }

    return $this->render('service/export.html.twig', [
        'form' => $searchForm->createView(),
        'assignments' => $queriedAssignments ?? null,
    ]);
}

(This is where i'd like to access either the data or queriedAssignments variables to create a CSV)

Repository Function

public function findForExport(array $params = [])
{

    $em = $this->getEntityManager();

    $qb = $em->createQueryBuilder();

    $qb->select('a', 'u', 'bg', 'bsi', 'e', 's')
      ->from('AppBundle:Assignment', 'a')
      ->leftJoin('a.staff', 'u')
      ->leftJoin('a.task', 't')
      ->leftJoin('a.billingGroup', 'bg')
      ->leftJoin('bg.billingServiceItems', 'bsi')
      ->leftJoin('a.eventInstance', 'e')
      ->leftJoin('e.serviceRequest', 's')

      ->groupBy('a');

    $qb->orderBy('a.date', 'ASC')
            ->addOrderBy('a.startTime', 'ASC')
            ->addOrderBy('a.endTime', 'ASC');


    if (isset($params['startDate'])) {
      $qb->andWhere('a.date >= :startDate')
        ->setParameter('startDate', $params['startDate']);
    }
    if (isset($params['endDate'])) {
      $qb->andWhere('a.date <= :endDate')
        ->setParameter('endDate', $params['endDate']);
    }

    if ($params['promptStaff']) {
      if ($this->paramIsNotEmpty($params['techs'])) {
        $qb->andWhere('u.username IN (:techs)');
        $qb->setParameter('techs', $params['techs']);

                $qb->orderBy('u.username', 'ASC')
                    ->addOrderBy('a.date', 'ASC');
      }
    }

    if ([$params['promptLocation']]) {
      if ($this->paramIsNotEmpty($params['campusName']) || $this->paramIsNotEmpty($params['buildingName']) || $this->paramIsNotEmpty($params['roomName'])) {
        $qb->join('s.location', 'l');
      }
    }
    if ($this->paramIsNotEmpty($params['campusName'])) {
      $qb->andWhere('l.campusName IN (:campusName)');
      $qb->setParameter('campusName', $params['campusName']);
    }
    if ($this->paramIsNotEmpty($params['buildingName'])) {
      $qb->andWhere('l.buildingName IN (:buildingName)');
      $qb->setParameter('buildingName', $params['buildingName']);
    }
    if($this->paramIsNotEmpty($params['roomName'])) {
      $qb->andWhere('l.roomName IN (:roomName)');
      $qb->setParameter('roomName', $params['roomName']);
    }
        if ($this->paramIsNotEmpty($params['campusName']) || $this->paramIsNotEmpty($params['buildingName']) || $this->paramIsNotEmpty($params['roomName'])) {
            $qb->orderBy('l.campusName', 'ASC')
                ->addOrderBy('l.buildingName', 'ASC')
                ->addOrderBy('l.roomName', 'ASC')
                ->addOrderBy('a.date', 'ASC')
                ->addOrderBy('a.startTime', 'ASC')
                ->addOrderBy('a.endTime', 'ASC');
        }

    if ($this->paramIsNotEmpty($params['searchVenue'])) {
      $qb->leftJoin('s.location', 'l');
      $qb->andWhere(
        $qb->expr()->orX(
          $qb->expr()->like('l.venueName', ':searchVenue'),
          $qb->expr()->like('l.venueAddress', ':searchVenue'),
          $qb->expr()->like('l.venueCity', ':searchVenue'),
          $qb->expr()->like('l.venueState', ':searchVenue'),
          $qb->expr()->like('l.venueZipCode', ':searchVenue')
        )
        );
        $qb->setParameter('searchVenue', '%'.$params['searchVenue'].'%');

                $qb->orderBy('l.venueState', 'ASC')
                    ->addOrderBy('l.venueCity', 'ASC')
                    ->addOrderBy('l.venueName', 'ASC')
                    ->addOrderBy('a.date', 'ASC')
                    ->addOrderBy('a.startTime', 'ASC')
                    ->addOrderBy('a.endTime', 'ASC');
    }

    if ($this->paramIsNotEmpty($params['startTime'])) {
      $qb->andWhere('a.startTime >= :startTime');
      $qb->setParameter('startTime', $params['startTime']);
    }
    if ($this->paramIsNotEmpty($params['endTime'])) {
        $qb->andWhere('a.endTime <= :endTime');
        $qb->setParameter('endTime', $params['endTime']);
    }

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

protected function paramIsNotEmpty($param)
{
    return $param instanceof ArrayCollection ? !$param->isEmpty() : !empty($param);
}

Form

public function buildForm(FormBuilderInterface $builder, array $options)
{
  $builder
    ->add('startDate', DateTimeType::class, [
      'widget' => 'single_text',
      'required' => false,
      'html5' => false,
      'attr' => ['class' => 'date', 'placeholder' => 'e.g. mm/dd/yyyy', 'autocomplete' => 'off'],
      'format' => 'MM/dd/yyyy',
    ])
    ->add('endDate', DateTimeType::class, [
      'widget' => 'single_text',
      'required' => false,
      'html5' => false,
      'attr' => ['class' => 'date', 'placeholder' => 'e.g. mm/dd/yyyy', 'autocomplete' => 'off'],
      'format' => 'MM/dd/yyyy',
    ])
    ->add('searchVenue', SearchType::class, [
      'required' => false,
      'label' => 'Venue Search (Name, Address, City, State, Zip)',
      'attr' => ['placeholder' => 'Press Enter to Search']
    ])
    ->add('promptStaff', CheckboxType::class, [
      'required' => false,
      'label' => 'Filter by Staff'
    ])
    ->add('promptLocation', CheckboxType::class, [
      'required' => false,
      'label' => 'Filter by Location'
    ])
    ->add('promptTime', CheckboxType::class, [
      'required' => false,
      'label' => 'Filter by Time'
    ])
    ->add('promptVenue', CheckboxType::class, [
      'required' => false,
      'label' => 'Search Venues'
    ])
    ->add('techs', ChoiceType::class, array(
      'required' => false,
      'expanded' => true,
      'multiple' => true,
      'label' => 'Staff Username',
      'choices' => $options['techs']
    ))
    ->add('campusName', ChoiceType::class, array(
      'required' => false,
      'expanded' => true,
      'multiple' => true,
      'choices' => $options['campuses']
    ))
    ->add('buildingName', ChoiceType::class, array(
      'required' => false,
      'expanded' => true,
      'multiple' => true,
      'choices' => $options['buildings']
    ))
    ->add('roomName', ChoiceType::class, array(
      'required' => false,
      'expanded' => true,
      'multiple' => true,
      'choices' => $options['rooms']
    ))
    ->add('startTime', ChoiceType::class, array(
      'choices' => $times,
      'required' => false,
      'placeholder' => 'Please Select'
    ))
    ->add('endTime', ChoiceType::class, array(
      'choices' => $times,
      'required' => false,
      'placeholder' => 'Please Select'
    ));
}

Twig

{% block body %}
<h1>Staff Time Export</h1>
{{ form_start(form) }}
{{ form_row(form.startDate) }}
{{ form_row(form.endDate) }}
<div >
  {{ form_row(form.promptStaff) }}
  {{ form_row(form.promptLocation) }}
  {{ form_row(form.promptTime) }}
</div>
<div >
  {{ form_row(form.techs) }}
</div>
<div >
  {{ form_row(form.promptVenue)}}
  <div >
    {# dropdowns #}
    {{ form_row(form.campusName) }}
    {{ form_row(form.buildingName) }}
    {{ form_row(form.roomName) }}
  </div>
  <div >
    {{ form_row(form.searchVenue) }}
  </div>
</div>
<div >
  {{ form_row(form.startTime)}}
  {{ form_row(form.endTime)}}
</div>

<div >
    <button  type="submit">Search</button>
</div>
{{ form_end(form) }}

{% if assignments is not null and assignments is not empty and assignments.errorMessage is not defined %}
<form action="{{ path('service_export_do')}}" method="post">
  <table >
    <thead>
      <tr>
        <th>Task Description</th>
        <th>Staff Name</th>
        <th>Username</th>
        <th>Date</th>
        <th>Start Time</th>
        <th>End Time</th>
        <th>Hours</th>
        <th>Unit Cost</th>
      </tr>
    </thead>
    <tbody>
      {% for assignment in assignments %}
        <tr>
          <td><input type="hidden" name="serviceIDs[]" value="{{ assignment.id }}"></input>{{ assignment.task.description ?? 'n/a'}}</td>
          <td>{{ assignment.staff.lastname }}, {{ assignment.staff.firstname }}</td>
          <td> {{ assignment.staff.username }}</td>
          <td> {{ assignment.date|date("m/d/Y") }}</td>
          <td> {{ assignment.startTime|date("H:i:s") }}</td>
          <td> {{ assignment.endTime|date("H:i:s") }}</td>
          <td> {% for billingServiceItem in assignment.billingServiceItems %}
            {% if billingServiceItem.hours %}{% endif %}
            {{ billingServiceItem.hours }}
            {% else %}
              0
          {% endfor %}</td>
          <td> {% for billingServiceItem in assignment.billingServiceItems %}
            ${{ billingServiceItem.unitCost }}
          {% endfor %}</td>
        </tr>
      {% endfor %}
    </tbody>
  </table>
  <div >
        <button  name="export_all" type="submit">Export</button>
    </div>
</form>
{% elseif (assignments.errorMessage is defined)%}
  <h1>
    {{ assignments['errorMessage'] }}
  </h1>
{% endif %}{% endblock %}

(Here I left out all but one of the hidden input types i was using previously for legibility)

Some things I've tried based off google searches (maybe not very well):

Passing the data to the route via a route parameter:Symfony Docs on Route Parameters
Creating a private variable in the controller for the data , then accessing it via getter/setters (Yes dumb, I was desperate)
Trying to move the export button into the same form as the query and rendering it conditionally, then adding a href path to a new controller route
Somehow trying to re-render the form again and re-querying it using the same information and then turning that data into the CSV

I feel like I'm missing a very simple way to add a function and/or route here that uses the same data that the user used to query and display the table, to create a csv. Thank you in advance for any suggestions and sorry if this is is hard to understand.

CodePudding user response：

The simplest way would be to add some optional suffix in your current route like .csv. If it is set then get all data that you need and create CSV with it.

This way you don't need to create new controller and extract your duplicating code... Just add one condition and return rendered page in one case and CSV file in another.

CodePudding user response：

I had a similiar issue to be resolved. I created two routes.

One route showed the user interface including the form, a table of data and an export button. The second route was justed used to export the data. Both routes were connected to the same form. Because the selection of the user could be shared publicly without leaking any secrets, the attached form used the http verb "GET" effectivly attaching the users selection as a query string parameter to the view route. When rendering the export button, I concatenated the export routes path with the query string.

This way I was able to separate both actions into two routes and share content between them, no cookies or js needed.