CheckUpExport.php

<?php

namespace App\Controller;

use App\Entity\ApiUser;
use App\Entity\CheckUp\CheckUp;
use App\Entity\CheckUp\Question;
use Symfony\Bundle\FrameworkBundle\Controller\Controller;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\Routing\Annotation\Route;

class CheckUpExport extends Controller
{
    /**
     * @Route("/test", name="test_home")
     */
    public function test(Request $request)
    {
        /*
         * Export users CheckUp's to csv
         */
        ini_set('memory_limit', -1);
        // find by church
        // select * from user u
        // join check_up c on c.user_id = u.id
        // where u.church_name like '%bethel%'
        $final = 'id, country, city, church, gender, age, checkup date, latitude, longitude,';
        $count = 0;
        $em = $this->getDoctrine()->getManager();
        $churchUsers = $em->getRepository(ApiUser::class)
            ->createQueryBuilder('u')
            ->join('u.checkUps', 'c')
            ->getQuery()
            ->getResult();

        $result = [];
        $questions = $em->getRepository(Question::class)->findAll();
        foreach ($questions as $question) {
//            $result[$question->getId()] = 0;
            $final .= $question->getId() . ".\" " .str_replace(",", ".", $question->getText()) . "\"" . ',';
        }
        $final .= "\r\n";
        $file = fopen('/home/vkolomiiets/Public/test/CheckUp.csv', 'a+');
        fwrite($file, $final);
//        file_put_contents('/home/vkolomiiets/Public/test/test.csv', $final);
        $cnt = 0;
        /** @var ApiUser $user */
        foreach ($churchUsers as $user) {
//           $churchCheckups = $user->getCheckUps()->last();
            /** @var CheckUp $checkup */
            $checkups = $user->getCheckUps();
            foreach ($checkups as $checkup) {
                $final = '';
                if ($checkup) {
                    $count++;
                }
                if ($user->getDateOfBirth()) {
                    preg_match("/^\d{4}/", $user->getDateOfBirth(), $matches);
                    if (isset($matches[0])) {
                        $currentDate = new \DateTime("now");
                        $age = (int) $currentDate->format("Y") - (int) $matches[0];
                    } else {
                        $age = " ";
                    }
                } else {
                    $age = " ";
                }
                $final = $user->getId() . ',' . "\"{$user->getCountry()}\"". ',' . "\"{$user->getCity()}\"". "," . "\"" . str_replace("\"", "'", $user->getChurchName()) . "\"" .
                    "," . ($user->getGender() ?: " ") . "," . $age . "," . $checkup->getCreatedAt()->format('Y-m-d H:i:s') .
                    "," . ($user->getLatitude() ?: " ") . "," . ($user->getLongitude() ?: " ") . ",";

                foreach ($questions as $question) {
                    foreach ($checkup->getAnswers() as $answer) {
                        if ($question === $answer->getQuestion() ) {
//                            $result[$question->getId()] += $answer->getValue();
                            if ($question->isInverted()) {
                                $final .= ($answer->getValue() * -1) . ',';
                            } else {
                                $final .= $answer->getValue() . ',';
                            }
                        }
                    }
                }
                $final .= "\r\n";
                fwrite($file, $final);
                $cnt++;
            }
//            file_put_contents('/home/vkolomiiets/Public/test/test.csv', $final, FILE_APPEND);
//            if (50 < $cnt) {
//                exit;
//            }
        }

//        foreach ($questions as $question) {
//            $finalBethel .= round($result[$question->getId()] = $result[$question->getId()]/count($churchUsers), 2) . ',';
//        }

//        $result = [];
//        foreach ($questions as $question) {
//            $result[$question->getId()] = 0;
//        }
// save the column headers

//        fwrite($file, $final);
        fclose($file);
        //select last checkup
        //select all question
        //select all answers for this question (find avg)
        return $this->json(['finish rows: '.$cnt]);
    }
}