/**
  * @param Site $site
  * @param Branch $branch
  * @return \Doctrine\ORM\QueryBuilder
  */
 public function findAllBySiteAndBranchQuery(Site $site, Branch $branch)
 {
     $siteIds = $site->getParentSiteIds();
     array_push($siteIds, $site->getId());
     $q = $this->createQueryBuilder('o')->innerJoin('o.branch', 'b')->where('o.site IN (:site_ids)')->andWhere('o.active = 1')->andWhere('o.deleted = 0')->andWhere('b.id = :branch_id')->setParameter('branch_id', $branch->getId())->setParameter('site_ids', $siteIds)->addOrderBy('o.weight', 'ASC')->addOrderBy('o.last_name', 'ASC');
     return $q;
 }
 /**
  * @param Branch $branch
  */
 protected function geocodeBranch(Branch $branch)
 {
     try {
         $location = $branch->getLocation();
         if (isset($location)) {
             $latitude = $location->getLatitude();
             if (empty($latitude)) {
                 $stateAbbr = '';
                 if (isset($state)) {
                     $stateAbbr = $state->getAbbreviation();
                 }
                 $geocoder = $this->container->get('geocoder.address');
                 $address = sprintf('%s %s, %s', $location->getAddress1(), $location->getCity(), $stateAbbr);
                 $geoAddress = $geocoder->getGeocodedData($address);
                 if (count($geoAddress) > 0) {
                     $location->setLatitude($geoAddress[0]['latitude']);
                     $location->setLongitude($geoAddress[0]['longitude']);
                     $this->em->persist($branch);
                     $this->em->flush();
                 }
             }
         }
     } catch (\Exception $e) {
     }
 }
 protected function importBranchesFromCsv($csvPath)
 {
     ini_set('auto_detect_line_endings', true);
     $siteId = $this->site->getId();
     $states = $this->em->getRepository('SudouxCmsLocationBundle:State')->findAll();
     $header = array('name', 'nmls_id', 'los_id', 'phone', 'fax', 'email', 'address1', 'address2', 'unit', 'city', 'state', 'zipcode', 'latitude', 'longitude', 'directions', 'description');
     $headerValid = true;
     $batchCount = 10;
     if (file_exists($csvPath)) {
         if (($handle = fopen($csvPath, "r")) !== FALSE) {
             $row = 0;
             $states = $this->em->getRepository('SudouxCmsLocationBundle:State')->findAll();
             while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
                 if ($row == 0) {
                     // validate the header
                     for ($i = 0; $i < count($data); $i++) {
                         if ($data[$i] != $header[$i]) {
                             $headerValid = false;
                             break;
                         }
                     }
                     if (!$headerValid) {
                         throw new \Exception('Csv headers are not valid. Correct format is ' . implode(',', $header));
                     }
                 } else {
                     $branch = new Branch();
                     $branch->setName($this->getCsvValue($data[0]));
                     $branch->setNmlsId($this->getCsvValue($data[1]));
                     $branch->setLosId($this->getCsvValue($data[2]));
                     $branch->setPhone($this->getCsvValue($data[3]));
                     $branch->setFax($this->getCsvValue($data[4]));
                     $branch->setEmail($this->getCsvValue($data[5]));
                     $location = new Location();
                     $location->setAddress1($this->getCsvValue($data[6]));
                     $location->setAddress2($this->getCsvValue($data[7]));
                     $location->setUnit($this->getCsvValue($data[8]));
                     $location->setCity($this->getCsvValue($data[9]));
                     foreach ($states as $state) {
                         if ($state->getAbbreviation() == trim($data[10]) || $state->getName() == trim($data[10])) {
                             $location->setState($state);
                             break;
                         }
                     }
                     $location->setZipcode($this->getCsvValue($data[11]));
                     $location->setLatitude($this->getCsvValue($data[12]));
                     $location->setLongitude($this->getCsvValue($data[13]));
                     $branch->setLocation($location);
                     $branch->setDirections($this->getCsvValue($data[14]));
                     $branch->setDescription($this->getCsvValue($data[15]));
                     $branch->setSite($this->site);
                     $this->em->persist($branch);
                     if ($row % $batchCount == 0) {
                         $this->em->flush();
                         $this->em->clear();
                         $this->site = $this->em->getRepository('SudouxCmsSiteBundle:Site')->find($siteId);
                         $states = $this->em->getRepository('SudouxCmsLocationBundle:State')->findAll();
                         $this->output->writeln(sprintf('%s rows processed', $row));
                     }
                 }
                 $row++;
             }
             $this->em->flush();
             $this->em->clear();
             $this->output->writeln(sprintf('Processing complete! %s rows processed', $row));
         }
     }
 }
 protected function importBranches()
 {
     $x = 0;
     $counter = 0;
     $zipPicRoot = $this->uploadPath . "/branches/";
     $baseRoot = $this->getContainer()->get('kernel')->getRootDir() . '/../';
     $this->output->writeln("Available Files: " . PHP_EOL);
     chdir($this->uploadPath . "/branches");
     passthru("ls *.csv");
     $csvPick = $this->dialog->ask($this->output, PHP_EOL . '</fg=yellow>Please Enter The Name Of The CSV You Want To Process (branch.csv) - ', 'branch.csv');
     $userName = $this->dialog->ask($this->output, 'Please Enter The User Name To Upload Pics As (admin)', 'admin');
     $user = $this->em->getRepository('SudouxCmsUserBundle:User')->loadUserByUsername($userName);
     if (file_exists($this->uploadPath . "/branches/" . $csvPick)) {
         $this->output->writeln("  File Found: " . PHP_EOL);
         $file = fopen($csvPick, "r");
         //$this->output->writeln(implode(" - ",fgetcsv($file)));
         print_r(fgetcsv($file));
         $picKey = $this->dialog->ask($this->output, 'Which Field(#) Do you want to key the pic off of (1)?', 1);
         while (($newBranchInfo = fgetcsv($file)) !== FALSE) {
             $newBranch = new Branch();
             $newLocation = new Location();
             $x++;
             $this->output->writeln("    Adding Branch {$x}:  " . PHP_EOL);
             $this->output->writeln(implode(" - ", $newBranchInfo));
             if (!$this->dialog->askConfirmation($this->output, PHP_EOL . "Are You Sure You Want To Add This Site?</question>?  ", false)) {
                 return;
             }
             $newBranch->setSite($this->site);
             $newBranch->setName($newBranchInfo[0]);
             $newBranch->setNmlsId($newBranchInfo[1]);
             $newBranch->setPhone($newBranchInfo[2]);
             $newBranch->setFax($newBranchInfo[3]);
             $newBranch->setEmail($newBranchInfo[4]);
             $newBranch->setLosId($newBranchInfo[6]);
             $newLocation->setAddress1($newBranchInfo[7]);
             $newLocation->setCity($newBranchInfo[8]);
             $stateLookup = $this->em->getRepository('SudouxCmsLocationBundle:State')->findByName($newBranchInfo[9]);
             // print_r($stateLookup['0']);
             $newLocation->setState($stateLookup['0']);
             $newLocation->setZipcode($newBranchInfo[10]);
             $this->em->persist($newLocation);
             $globResults = glob("{$this->uploadPath}/branches/{$newBranchInfo[$picKey]}.*");
             $ext = pathinfo($globResults[0], PATHINFO_EXTENSION);
             if ($globResults) {
                 $savePath = realpath($baseRoot) . "/web/uploads/sites/" . $this->site->getId() . "/public/";
                 $picName = "{$newBranchInfo[$picKey]}.{$ext}";
                 $uploadedPicPath = $globResults[0];
                 $MIMEtype = mime_content_type($uploadedPicPath);
                 while (file_exists($savePath . $picName)) {
                     $picName = $newBranchInfo[$picKey] . "_{$counter}." . $ext;
                     $counter++;
                     $this->output->writeln("File Name Exists - New Name: <fg=blue>{$picName}</fg=blue>");
                 }
                 $openFileName = $zipPicRoot . $picName;
                 rename($openFileName, $savePath . $picName);
                 $photo = new File();
                 $size = filesize($savePath . $picName);
                 $photo->setPath("uploads/sites/" . $this->site->getId() . "/public/" . $picName);
                 $photo->setFilesize($size);
                 $photo->setName($newBranchInfo[0]);
                 $photo->setUser($user);
                 $photo->setMimeType($MIMEtype);
                 $photo->setSite($this->site);
                 $this->em->persist($photo);
                 $newBranch->setBranchPhoto($photo);
             }
             $newBranch->setLocation($newLocation);
             $newBranch->setActive($newBranchInfo[11]);
             $newBranch->setWebsite($newBranchInfo[12]);
             $this->em->persist($newBranch);
             $this->em->flush();
         }
         fclose($file);
     }
 }