<?php

namespace Adherence\Service;

/**
 * Manage Adherence data.
 * @author andre.fourie
 */
class Adherence extends \Workspace\Service\DataBin
{

  /**
   * CRON functionality: Fetch and import AutoMate and AutoLine data.
   */
  public function cronProcess()
  {
    #-> Sources.

    $automateFiles = array(
      './b4c_CJD.csv' => '/tmp/b4c_CJD.csv',
      './b4c_LSaker.csv' => '/tmp/b4c_LSaker.csv',
      './b4c_MultiFran.csv' => '/tmp/b4c_MultiFran.csv',
      './b4c_Toyota.csv' => '/tmp/b4c_Toyota.csv'
    );

    $autolineFiles = array(
      './b4c_Ford.csv' => '/tmp/b4c_Ford.csv',
      './b4c_GM.csv' => '/tmp/b4c_GM.csv',
      './b4c_Honda.csv' => '/tmp/b4c_Honda.csv',
      './b4c_Nissan.csv' => '/tmp/b4c_Nissan.csv',
      './b4c_Cargo.csv' => '/tmp/b4c_Cargo.csv'
    );
    $amhgroupFiles = array(
      './Vehusesold.11' => '/tmp/Vehusesold.11',
      './Vehusesolh.11' => '/tmp/Vehusesolh.11',
      './Vehusesold.20' => '/tmp/Vehusesold.20'
    );
    $amhgroupFiles2 = array();
    // headers for the fields
// 				Company number
// 				Vehicle stock number
// 				Vin number
// 				Make
// 				Model
// 				Registration no
// 				Colour
// 				Trim
// 				Odometer
// 				Condition
// 				M&M Code
// 				Cost
// 				Retail
    //'./Vehusestk.11' 		=> '/tmp/Vehusestk.11',
    //'./Vehusestkh.11' 	=> '/tmp/Vehusestkh.11',
    //'./Vehusestk.20' 		=> '/tmp/Vehusestk.20'
// 		);

    $amhgroupFiles3 = array();
    //headers for the fields
// 								Vehicle Stock number
// 								Make
// 								Model
// 								Odometer
// 								Vin
// 								Registration no
// 								Accounting month
// 								Date sold
// 								'./vhsleb4car.11' 		=> '/tmp/vhsleb4car.11'
// 		);


    $fileMap = array(
      '/tmp/b4c_Ford.csv' => 'Ford',
      '/tmp/b4c_GM.csv' => 'GM',
      '/tmp/b4c_Honda.csv' => 'Honda',
      '/tmp/b4c_Nissan.csv' => 'Nissan',
      '/tmp/b4c_Cargo.csv' => 'Cargo',
      '/tmp/b4c_CJD.csv' => 'CJD',
      '/tmp/b4c_LSaker.csv' => 'LSaker',
      '/tmp/b4c_MultiFran.csv' => 'MultiFran',
      '/tmp/b4c_Toyota.csv' => 'Toyota',
      '/tmp/Vehusestk.11' => 'Multifranchise',
      '/tmp/Vehusestkh.11' => 'Hyundai',
      '/tmp/Vehusestk.20' => 'Pearldb',
      '/tmp/Vehusesold.11' => 'Multifranchise',
      '/tmp/Vehusesolh.11' => 'Hyundai',
      '/tmp/Vehusesold.20' => 'Pearldb',
      '/tmp/vhsleb4car.11' => 'Hyundai'
    );
    $groupMap = array(
      // Imperial
      'Ford' => 1,
      'GM' => 1,
      'Honda' => 1,
      'Nissan' => 1,
      'Cargo' => 1,
      'CJD' => 1,
      'LSaker' => 1,
      'MultiFran' => 1,
      'Toyota' => 1,
      // AMH
      'Multifranchise' => 3,
      'Hyundai' => 3,
      'Pearldb' => 3,
      'Multifranchise' => 3,
      'Hyundai' => 3,
      'Pearldb' => 3,
      'Hyundai' => 3
    );

    $ftpWrapper = new \Utility\Remote\Ftp(
      '197.242.75.234', //'127.0.0.1'
      'automate', 'aut0mat3@', 180, true
    );
    $ftpWrapper2 = new \Utility\Remote\Ftp(
      'ftps.amhgroup.net', 'bid4cars', 'rZMd0GJx', 180, true
    );

    #-> Cleanup map.
    $filter = array(
      'tba',
      'ref',
      'na',
      'n/a',
      'n\\a',
      '#na',
      'vin',
      'eng',
      'reg',
      'none',
    );


    #-> Cleanup existing inported data that does not have company linked.
    /* $results = $this->em->createQuery(
      'SELECT adherence '
      . 'FROM \Adherence\Entity\Adherence adherence '
      . 'WHERE adherence.company IS NULL'
      )
      ->getResult();
      foreach ($results as $adherence)
      {
      $search = 'Automate' == $adherence->type
      ? array(
      'automateFile' 			=> $adherence->file,
      'automateDealerCode' 	=> $adherence->dealerCode,
      'group' 				=> $groupMap[$adherence->file]
      )
      : array(
      'autolineFile' 			=> $adherence->file,
      'autolineDealerCode' 	=> $adherence->dealerCode,
      'group' 				=> $groupMap[$adherence->file]
      );

      $company = $this->em
      ->getRepository('\Company\Entity\Company')
      ->findOneBy($search);
      if (!is_null($company))
      {
      $adherence->company = $company;
      $this->em->flush($adherence);
      }
      } */


    #-> Import AutoMate csv
    $companies = array();
    foreach ($automateFiles as $remoteFile => $localFile)
    {
      echo "\n\nProcessing $localFile\n";
      $downloadOk = $ftpWrapper->downloadFile($localFile, $remoteFile, FTP_ASCII);
      if (!$downloadOk || !file_exists($localFile))
      {
        continue;
      }
      $companies[$localFile] = array();
      $csvImporter = new \Utility\Import\Csv($localFile);
      $csvImporter->setDelimiter(',');
      $found = 0;
      while (($record = $csvImporter->getRecord(\Utility\Import\Csv::FETCH_LAZY)) !== false)
      {
        #-> Safety checks.
        if ('T' != trim($record[6]) && 't' != trim($record[6]))
        {
          echo '<';
          continue;
        }
        $hash = md5(serialize($record));
        $record[7] = str_replace(' ', '', $record[7]);
        if (empty($record[1]) || !is_numeric(trim($record[7])))
        {
          echo "! \n";
          continue;
        }


        #-> Change date format from d-m-Y to Y-m-d.
        $parts = explode('/', $record[1]);
        $record[1] = new \DateTime($parts[2] . '-' . $parts[1] . '-' . $parts[0]);
        $dateFrom = new \DateTime(date('Y-m-d', mktime(1, 1, 1, $parts[1] - 4, $parts[0], $parts[2])));

        #-> Check if this will cause duplicate.
        $existing = $this->em
          ->getRepository('\Adherence\Entity\Adherence')
          ->findOneBy(array(
          'hash' => $hash,
          'dateSold' => $record[1]
        ));
        if (!is_null($existing))
        {
          $found++;
          echo "-";
          continue;
        }
        else
        {
          $found = 0;
        }

        #-> Log to import table.
        if (!isset($companies[$localFile][$record[0]]))
        {
          $companies[$localFile][$record[0]] = $this->em
            ->getRepository('\Company\Entity\Company')
            ->findOneBy(array(
            'automateFile' => $fileMap[$localFile],
            'automateDealerCode' => $record[0],
            'group' => $groupMap[$fileMap[$localFile]]
          ));
        }
        $companyId = $companies[$localFile][$record[0]];
        try
        {
          $entry = new \Adherence\Entity\Adherence();
          $entry->fromArray(array(
            'hash' => $hash,
            'source' => 'Imperial',
            'type' => 'Automate',
            'file' => $fileMap[$localFile],
            'dealerCode' => $record[0],
            'company' => $companyId,
            'dateSold' => $record[1],
            'stockNumber' => trim($record[2]),
            'vinNumber' => trim($record[3]),
            'soldTo' => trim($record[4]),
            'amount' => trim($record[7]),
            'registrationNumber' => trim($record[8]),
            'engineNumber' => trim($record[9]),
            'make' => trim($record[10])
          ));
          $this->em->persist($entry);
          $this->em->flush($entry);
          echo '.';


          #-> Match on VIN, registration or engine number.
          $dql = 'SELECT priceGuide.id AS pgId, stock.id AS stockId '
            . 'FROM \PriceGuide\Entity\PriceGuide priceGuide '
            . 'JOIN priceGuide.stock stock '
            . 'WHERE priceGuide.created BETWEEN :dateFrom AND :dateTo ';
          $where = array();
          $params = array(
            'dateFrom' => $dateFrom,
            'dateTo' => $record[1]
          );
          if ('' != $entry->vinNumber && !in_array(strtolower($entry->vinNumber), $filter))
          {
            $where[] = 'stock.vinNumber = :vinNumber';
            $params['vinNumber'] = $entry->vinNumber;
          }
          if ('' != $entry->registrationNumber && !in_array(strtolower($entry->registrationNumber), $filter))
          {
            $where[] = 'stock.registrationNumber = :registrationNumber';
            $params['registrationNumber'] = $entry->registrationNumber;
          }
          if ('' != $entry->engineNumber && !in_array(strtolower($entry->engineNumber), $filter))
          {
            $where[] = 'stock.engineNumber = :engineNumber';
            $params['engineNumber'] = $entry->engineNumber;
          }
          $dql .= 'AND (' . implode(' OR ', $where) . ')';
          $results = $this->em->createQuery($dql)
            ->setParameters($params)
            ->getArrayResult();
          $stockFound = array();
          foreach ($results as $result)
          {
            if (isset($stockFound[$result['stockId']]))
            {
              continue;
            }
            $stockFound[$result['stockId']] = true;
            $link = new \Adherence\Entity\AdherenceStock();
            $link->adherence = $entry;
            $link->stock = $this->em->getReference('\Stock\Entity\Stock', $result['stockId']);
            $this->em->persist($link);
            $this->em->flush($link);
            $link = null;
          }


          #-> Cleanup.
          $this->em->clear();
        } catch (\Exception $e)
        {
          echo "-----------------------\n";
          echo $e->getMessage() . "\n";
          var_dump($record);
          echo "\n";
        }
      }
      $csvImporter = null;
      unlink($localFile);
    }

    #-> Import AutoLine csv
    $companies = array();
    foreach ($autolineFiles as $remoteFile => $localFile)
    {
      echo "\n\nProcessing $localFile\n";
      $downloadOk = $ftpWrapper->downloadFile($localFile, $remoteFile, FTP_ASCII);
      if (!$downloadOk || !file_exists($localFile))
      {
        continue;
      }
      $companies[$localFile] = array();
      $csvImporter = new \Utility\Import\Csv($localFile);
      $csvImporter->setDelimiter(',');
      $found = 0;
      $yearPrepend = '/tmp/b4c_Cargo.csv' == $localFile ? '' : '20';
      while (($record = $csvImporter->getRecord(\Utility\Import\Csv::FETCH_LAZY)) !== false)
      {
        #-> Safety checks.
        if ('/tmp/b4c_Cargo.csv' == $localFile && 'T' != trim($record[6]) && 't' != trim($record[6]))
        {
          continue;
        }
        $hash = md5(serialize($record));
        $record[7] = str_replace(' ', '', $record[7]);
        if (empty($record[1]) || !is_numeric($record[7]))
        {
          echo "! \n";
          continue;
        }

        #-> Change date format from d-m-Y to Y-m-d.
        $parts = explode('/', $record[1]);
        $record[1] = new \DateTime($yearPrepend . $parts[2] . '-' . $parts[1] . '-' . $parts[0]);
        $dateFrom = new \DateTime(date('Y-m-d', mktime(1, 1, 1, $parts[1] - 4, $parts[0], $parts[2])));

        #-> Check if this will cause duplicate.
        $existing = $this->em
          ->getRepository('\Adherence\Entity\Adherence')
          ->findOneBy(array(
          'hash' => $hash,
          'file' => $fileMap[$localFile],
          'dateSold' => $record[1]
        ));
        if (!is_null($existing))
        {
          $found++;
          echo "-";
          continue;
        }
        else
        {
          $found = 0;
        }

        #-> Log to import table.
        if (!isset($companies[$localFile][$record[0]]))
        {
          $companies[$localFile][$record[0]] = $this->em
            ->getRepository('\Company\Entity\Company')
            ->findOneBy(array(
            'autolineFile' => $fileMap[$localFile],
            'autolineDealerCode' => $record[0],
            'group' => $groupMap[$fileMap[$localFile]]
          ));
        }
        $companyId = $companies[$localFile][$record[0]];
        try
        {
          $entry = new \Adherence\Entity\Adherence();
          $entry->fromArray(array(
            'hash' => $hash,
            'source' => 'Imperial',
            'type' => 'Autoline',
            'file' => $fileMap[$localFile],
            'dealerCode' => $record[0],
            'company' => $companyId,
            'dateSold' => $record[1],
            'stockNumber' => trim($record[2]),
            'vinNumber' => trim($record[3]),
            'soldTo' => trim($record[4]),
            'amount' => trim($record[7]),
            'registrationNumber' => trim($record[8]),
            'engineNumber' => trim($record[9]),
            'make' => trim($record[10])
          ));
          $this->em->persist($entry);
          $this->em->flush($entry);
          echo '.';


          #-> Match on VIN, registration or engine number.
          $dql = 'SELECT priceGuide.id AS pgId, stock.id AS stockId '
            . 'FROM \PriceGuide\Entity\PriceGuide priceGuide '
            . 'JOIN priceGuide.stock stock '
            . 'WHERE priceGuide.created BETWEEN :dateFrom AND :dateTo ';
          $where = array();
          $params = array(
            'dateFrom' => $dateFrom,
            'dateTo' => $record[1]
          );
          if ('' != $entry->vinNumber && !in_array(strtolower($entry->vinNumber), $filter))
          {
            $where[] = 'stock.vinNumber = :vinNumber';
            $params['vinNumber'] = $entry->vinNumber;
          }
          if ('' != $entry->registrationNumber && !in_array(strtolower($entry->registrationNumber), $filter))
          {
            $where[] = 'stock.registrationNumber = :registrationNumber';
            $params['registrationNumber'] = $entry->registrationNumber;
          }
          if ('' != $entry->engineNumber && !in_array(strtolower($entry->engineNumber), $filter))
          {
            $where[] = 'stock.engineNumber = :engineNumber';
            $params['engineNumber'] = $entry->engineNumber;
          }
          $dql .= 'AND (' . implode(' OR ', $where) . ')';
          $results = $this->em->createQuery($dql)
            ->setParameters($params)
            ->getArrayResult();
          $stockFound = array();
          foreach ($results as $result)
          {
            if (isset($stockFound[$result['stockId']]))
            {
              continue;
            }
            $stockFound[$result['stockId']] = true;
            $link = new \Adherence\Entity\AdherenceStock();
            $link->adherence = $entry;
            $link->stock = $this->em->getReference('\Stock\Entity\Stock', $result['stockId']);
            $this->em->persist($link);
            $this->em->flush($link);
            $link = null;
          }


          #-> Cleanup.
          $this->em->clear();
        } catch (\Exception $e)
        {
          echo "-----------------------\n";
          echo $e->getMessage() . "\n";
          var_dump($record);
          echo "\n";
        }
      }
      $csvImporter = null;
      unlink($localFile);
    }

    #-> Import AMH Group csv
    $companies = array();
    foreach ($amhgroupFiles as $remoteFile => $localFile)
    {
      echo "\n\nProcessing $localFile\n";
      $downloadOk = $ftpWrapper2->downloadFile($localFile, $remoteFile, FTP_ASCII);
      if (!$downloadOk || !file_exists($localFile))
      {
        continue;
      }
      $companies[$localFile] = array();
      $csvImporter = new \Utility\Import\Csv($localFile);
      $csvImporter->setDelimiter(' ');
      $found = 0;

      while (($record = $csvImporter->getRecord(\Utility\Import\Csv::FETCH_LAZY)) !== false)
      {
        #-> Safety checks.
        $hash = md5(serialize($record));
        if (empty($record[2]) || !is_numeric(trim($record[8])) || !is_numeric(trim($record[9])))
        {
          echo "! \n";
          continue;
        }

        #-> Change date format from d-m-Y to Y-m-d.
        $parts = explode('/', $record[2]);
        $record[2] = new \DateTime($parts[2] . '-' . $parts[1] . '-' . $parts[0]);
        $dateFrom = new \DateTime(date('Y-m-d', mktime(1, 1, 1, $parts[1] - 4, $parts[0], $parts[2])));

        #-> Check if this will cause duplicate.
        $existing = $this->em
          ->getRepository('\Adherence\Entity\Adherence')
          ->findOneBy(array(
          'hash' => $hash,
          'dateSold' => $record[2]
        ));
        if (!is_null($existing))
        {
          $found++;
          echo "-";
          continue;
        }
        else
        {
          $found = 0;
        }

        #-> Log to import table.
        if (!isset($companies[$localFile][$record[0]]))
        {
          $companies[$localFile][$record[0]] = $this->em
            ->getRepository('\Company\Entity\Company')
            ->findOneBy(array(
            'amhgroupFile' => $fileMap[$localFile],
            'amhgroupDealerCode' => $record[0],
            'group' => $groupMap[$fileMap[$localFile]]
          ));
        }
        $companyId = $companies[$localFile][$record[0]];


        try
        {
          $entry = new \Adherence\Entity\Adherence();
          $entry->fromArray(array(
            'hash' => $hash,
            'source' => 'AMHgroup',
            'type' => 'AMHgroup',
            'file' => $fileMap[$localFile],
            'dealerCode' => $record[0],
            'company' => $companyId,
            'dateSold' => $record[2],
            'stockNumber' => trim($record[1]),
            'vinNumber' => trim($record[3]),
            'soldTo' => trim($record[7]),
            'amount' => trim($record[8]),
            'registrationNumber' => trim($record[4])
          ));

          $this->em->persist($entry);
          $this->em->flush($entry);
          echo '.';


          #-> Match on VIN, registration or engine number.
          $dql = 'SELECT priceGuide.id AS pgId, stock.id AS stockId '
            . 'FROM \PriceGuide\Entity\PriceGuide priceGuide '
            . 'JOIN priceGuide.stock stock '
            . 'WHERE priceGuide.created BETWEEN :dateFrom AND :dateTo ';
          $where = array();
          $params = array(
            'dateFrom' => $dateFrom,
            'dateTo' => $record[2]
          );
          if ('' != $entry->vinNumber && !in_array(strtolower($entry->vinNumber), $filter))
          {
            $where[] = 'stock.vinNumber = :vinNumber';
            $params['vinNumber'] = $entry->vinNumber;
          }
          if ('' != $entry->registrationNumber && !in_array(strtolower($entry->registrationNumber), $filter))
          {
            $where[] = 'stock.registrationNumber = :registrationNumber';
            $params['registrationNumber'] = $entry->registrationNumber;
          }

          $dql .= 'AND (' . implode(' OR ', $where) . ')';
          $results = $this->em->createQuery($dql)
            ->setParameters($params)
            ->getArrayResult();
          $stockFound = array();

          //var_dump($results);

          foreach ($results as $result)
          {
            if (isset($stockFound[$result['stockId']]))
            {
              continue;
            }
            $stockFound[$result['stockId']] = true;
            $link = new \Adherence\Entity\AdherenceStock();
            $link->adherence = $entry;
            $link->stock = $this->em->getReference('\Stock\Entity\Stock', $result['stockId']);
            $this->em->persist($link);
            $this->em->flush($link);
            $link = null;
          }


          #-> Cleanup.
          $this->em->clear();
        } catch (\Exception $e)
        {
          echo "-----------------------\n";
          echo $e->getMessage() . "\n";
          //var_dump($record);
          echo "\n";
        }
      }
      $csvImporter = null;
      unlink($localFile);
    }

    #-> Import AMH group Files2 csv
    $companies = array();
    foreach ($amhgroupFiles2 as $remoteFile => $localFile)
    {
      echo "Processing $localFile\n";
      $downloadOk = $ftpWrapper2->downloadFile($localFile, $remoteFile, FTP_ASCII);
      if (!$downloadOk && !file_exists($localFile))
      {
        continue;
      }

      $companies[$localFile] = array();
      $csvImporter = new \Utility\Import\Csv($localFile);
      $csvImporter->setDelimiter(' ');
      $record = $csvImporter->getRecord(\Utility\Import\Csv::FETCH_LAZY);
      if (false == $record)
      {
        continue;
      }

      //var_dump($csvImporter->getRecord(\Utility\Import\Csv::FETCH_LAZY));

      $found = 0;
      while (($record = $csvImporter->getRecord(\Utility\Import\Csv::FETCH_LAZY)) !== false)
      {
        #-> Safety checks.
        $hash = md5(serialize($record));
        if (!is_numeric(trim($record[11])) || !is_numeric(trim($record[12])))
        {
          echo "! \n";
          continue;
        }

        // The file has no date field
// 				#-> Change date format from d-m-Y to Y-m-d.
// 				$parts = explode('/', $record[1]);
// 				$record[1] = new \DateTime('20' . $parts[2] . '-' . $parts[1] . '-' . $parts[0]);
// 						$dateFrom = new \DateTime(date('Y-m-d', mktime(1, 1, 1, $parts[1] - 4, $parts[0], $parts[2])));
// 						#-> Check if this will cause duplicate.
// 						$existing = $this->em
// 				->getRepository('\Adherence\Entity\Adherence')
// 				->findOneBy(array(
// 				'hash' 		=> $hash,
// 					'dateSold' 	=> $record[1]
// 				));
// 				if (!is_null($existing))
// 						{
// 				$found++;
// 							continue;
// 				}
// 				else
// 				{
// 				$found = 0;
// 				}
        #-> Log to import table.
        if (!isset($companies[$localFile][$record[0]]))
        {
          $companies[$localFile][$record[0]] = $this->em
            ->getRepository('\Company\Entity\Company')
            ->findOneBy(array(
            'amhgroupFile' => $fileMap[$localFile],
            'amhgroupDealerCode' => $record[0],
            'group' => $groupMap[$fileMap[$localFile]]
          ));
        }
        $companyId = $companies[$localFile][$record[0]];
        try
        {
          $entry = new \Adherence\Entity\Adherence();
          $entry->fromArray(array(
            'hash' => $hash,
            'source' => 'AMHgroup',
            'type' => 'AMHgroup',
            'file' => $fileMap[$localFile],
            'dealerCode' => $record[0],
            'company' => $companyId,
            'dateSold' => '',
            'stockNumber' => trim($record[1]),
            'vinNumber' => trim($record[2]),
            'soldTo' => '',
            'amount' => trim($record[11]),
            'registrationNumber' => trim($record[5]),
            'engineNumber' => '',
            'make' => trim($record[3])
          ));

          var_dump($entry);
          $this->em->persist($entry);
          $this->em->flush($entry);
          echo '.';


          #-> Match on VIN, registration or engine number.
          $dql = 'SELECT priceGuide.id AS pgId, stock.id AS stockId '
            . 'FROM \PriceGuide\Entity\PriceGuide priceGuide '
            . 'JOIN priceGuide.stock stock ';
          //. 'WHERE priceGuide.created BETWEEN :dateFrom AND :dateTo ';
          $where = array();
          // 				$params = array(
          // 					'dateFrom' 	=> $dateFrom,
          // 					'dateTo' 	=> $record[1]
          // 					);
          if ('' != $entry->vinNumber && !in_array(strtolower($entry->vinNumber), $filter))
          {
            $where[] = 'stock.vinNumber = :vinNumber';
            $params['vinNumber'] = $entry->vinNumber;
          }
          if ('' != $entry->registrationNumber && !in_array(strtolower($entry->registrationNumber), $filter))
          {
            $where[] = 'stock.registrationNumber = :registrationNumber';
            $params['registrationNumber'] = $entry->registrationNumber;
          }

          $dql .= 'AND (' . implode(' OR ', $where) . ')';

          $results = $this->em->createQuery($dql)
            ->setParameters($params)
            ->getArrayResult();
          $stockFound = array();

          foreach ($results as $result)
          {
            if (isset($stockFound[$result['stockId']]))
            {
              continue;
            }
            $stockFound[$result['stockId']] = true;
            $link = new \Adherence\Entity\AdherenceStock();
            $link->adherence = $entry;
            $link->stock = $this->em->getReference('\Stock\Entity\Stock', $result['stockId']);
            $this->em->persist($link);
            $this->em->flush($link);
            $link = null;
          }


          #-> Cleanup.
          $this->em->clear();
        } catch (\Exception $e)
        {
          echo "-----------------------\n";
          echo $e->getMessage() . "\n";
          var_dump($record);
          echo "\n";
        }
      }
      $csvImporter = null;
      unlink($localFile);
    }

    #-> Import AMH group Files3 csv
    $companies = array(); // needs the company number field
    foreach ($amhgroupFiles3 as $remoteFile => $localFile)
    {
      echo "Processing $localFile\n";
      $downloadOk = $ftpWrapper2->downloadFile($localFile, $remoteFile, FTP_ASCII);
      if (!$downloadOk && !file_exists($localFile))
      {
        continue;
      }

      $companies[$localFile] = array();
      $csvImporter = new \Utility\Import\Csv($localFile);
      $csvImporter->setDelimiter(' ');
      $record = $csvImporter->getRecord(\Utility\Import\Csv::FETCH_LAZY);
      if (false == $record)
      {
        continue;
      }

      var_dump($csvImporter->getRecord(\Utility\Import\Csv::FETCH_LAZY));
      break;

      $found = 0;
      while (($record = $csvImporter->getRecord(\Utility\Import\Csv::FETCH_LAZY)) !== false)
      {
        #-> Safety checks.
        $hash = md5(serialize($record));

        if (empty($record[7]))
        {
          echo "! \n";
          continue;
        }
        #-> Change date format from d-m-Y to Y-m-d.
        $parts = explode('/', $record[7]);
        $record[7] = new \DateTime('20' . $parts[2] . '-' . $parts[1] . '-' . $parts[0]);
        $dateFrom = new \DateTime(date('Y-m-d', mktime(1, 1, 1, $parts[1] - 4, $parts[0], $parts[2])));


        #-> Check if this will cause duplicate.
        $existing = $this->em
          ->getRepository('\Adherence\Entity\Adherence')
          ->findOneBy(array(
          'hash' => $hash,
          'dateSold' => $record[7]
        ));
        if (!is_null($existing))
        {
          $found++;
          continue;
        }
        else
        {
          $found = 0;
        }
        $test = $companies[$localFile][$record[0]];
        var_dump($test);

        #-> Log to import table.
        if (!isset($companies[$localFile][$record[0]]))
        {
          $companies[$localFile][$record[0]] = $this->em
            ->getRepository('\Company\Entity\Company')
            ->findOneBy(array(
            'amhgroupFile' => $fileMap[$localFile],
            'amhgroupDealerCode' => $record[0],
            'group' => $groupMap[$fileMap[$localFile]]
          ));
        }
        $companyId = $companies[$localFile][$record[0]];

        var_dump($companyId);
        break;

        try
        {
          $entry = new \Adherence\Entity\Adherence();
          $entry->fromArray(array(
            'hash' => $hash,
            'source' => 'AMHgroup',
            'type' => 'AMHgroup',
            'file' => $fileMap[$localFile],
            'dealerCode' => $record[0],
            'company' => $companyId,
            'dateSold' => $record[7],
            'stockNumber' => '',
            'vinNumber' => $record[4],
            'soldTo' => '',
            'amount' => '',
            'registrationNumber' => $record[5],
            'engineNumber' => '',
            'make' => $record[1]
          ));

          //var_dump($entry);
          //break;

          $this->em->persist($entry);
          $this->em->flush($entry);
          echo '.';


          #-> Match on VIN, registration or engine number.
          $dql = 'SELECT priceGuide.id AS pgId, stock.id AS stockId '
            . 'FROM \PriceGuide\Entity\PriceGuide priceGuide '
            . 'JOIN priceGuide.stock stock '
            . 'WHERE priceGuide.created BETWEEN :dateFrom AND :dateTo ';
          $where = array();
          $params = array(
            'dateFrom' => $dateFrom,
            'dateTo' => $record[7]
          );
          if ('' != $entry->vinNumber && !in_array(strtolower($entry->vinNumber), $filter))
          {
            $where[] = 'stock.vinNumber = :vinNumber';
            $params['vinNumber'] = $entry->vinNumber;
          }
          if ('' != $entry->registrationNumber && !in_array(strtolower($entry->registrationNumber), $filter))
          {
            $where[] = 'stock.registrationNumber = :registrationNumber';
            $params['registrationNumber'] = $entry->registrationNumber;
          }

          $dql .= 'AND (' . implode(' OR ', $where) . ')';

          $results = $this->em->createQuery($dql)
            ->setParameters($params)
            ->getArrayResult();
          $stockFound = array();

          var_dump($results);
          break;

          foreach ($results as $result)
          {
            if (isset($stockFound[$result['stockId']]))
            {
              continue;
            }
            $stockFound[$result['stockId']] = true;
            $link = new \Adherence\Entity\AdherenceStock();
            $link->adherence = $entry;
            $link->stock = $this->em->getReference('\Stock\Entity\Stock', $result['stockId']);
            $this->em->persist($link);
            $this->em->flush($link);
            $link = null;
          }


          #-> Cleanup.
          $this->em->clear();
        } catch (\Exception $e)
        {
          echo "-----------------------\n";
          echo $e->getMessage() . "\n";
          var_dump($record);
          echo "\n";
        }
      }
      $csvImporter = null;
      unlink($localFile);
    }
  }

  public function cronTradeSales()
  {
    $ftpWrapper = new \Utility\Remote\Ftp(
      'ftp.nirph.com', //'127.0.0.1'
      'b4c@nirph.com', 'B!d4C@r$', 180, true
    );

    $localFile = '/tmp/trade_sales' . date('Y-m') . '.xlsx';
    $remoteFile = '/Monthly AMH Trade Sales Report/TradeSales' . date('Y-m') . '.xlsx';

    $downloadOk = $ftpWrapper->downloadFile($localFile, $remoteFile, FTP_ASCII);
    if ($downloadOk && file_exists($localFile))
    {
      /** Identify the type of $inputFileName * */
      $inputFileType = \PHPExcel_IOFactory::identify($localFile);
      /** Create a new Reader of the type that has been identified * */
      $objReader = \PHPExcel_IOFactory::createReader($inputFileType);
      /** Advise the Reader that we only want to load cell data * */
      $objReader->setReadDataOnly(true);
      /** Load $inputFileName to a PHPExcel Object * */
      $objPHPExcel = $objReader->load($localFile);

      $worksheetData = $objReader->listWorksheetInfo($localFile);

      foreach ($worksheetData as $worksheet)
      {
        $objPHPExcel->setActiveSheetIndexByName($worksheet['worksheetName']);
        $sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, false, false);

        if (trim($sheetData[0][0]) == 'Dealer Name')
        {
          foreach ($sheetData as $row)
          {
            if (trim($row[0]) != 'Dealer Name' && $row[0] != '')
            {
              try
              {
                $entry = new \Adherence\Entity\TradeSales();
                $entry->fromArray(array(
                  'dealerName' => $row[0],
                  'stockNumber' => $row[2],
                  'invoiceNumber' => $row[3],
                  'regNumber' => $row[4],
                  'vinNumber' => $row[5],
                  'vehicleMake' => $row[6],
                  'regDate' => new \DateTime(gmdate("d-m-Y H:i:s", ($row[7] - 25569) * 86400)),
                  'saleDate' => new \DateTime(gmdate("d-m-Y H:i:s", ($row[8] - 25569) * 86400)),
                  'saleType' => $row[9],
                  'totalCost' => $row[10],
                  'salesTotal' => $row[11],
                  'soldTo' => $row[12]
                ));

                #-> Match on VIN
                $dql = 'SELECT stock.id AS stockId '
                  . 'FROM \Stock\Entity\Stock stock WHERE ';
                $where = $params = array();
                if ('' != $row[4])
                {
                  $where[] = 'stock.vinNumber = :vinNumber';
                  $params['vinNumber'] = str_replace(' ', '', $row[5]);
                }

                $dql .= implode(' AND ', $where);

                $results = $this->em->createQuery($dql)
                  ->setParameters($params)
                  ->getArrayResult();

                if (count($results))
                {
                  $entry->stock = $this->em->getReference('\Stock\Entity\Stock', $results[0]['stockId']);
                  $entry->vinMatch = true;
                }

                #-> Match on registration number
                $dql = 'SELECT stock.id AS stockId '
                  . 'FROM \Stock\Entity\Stock stock WHERE ';
                $where = $params = array();
                if ('' != $row[4])
                {
                  $where[] = 'stock.registrationNumber = :registrationNumber';
                  $params['registrationNumber'] = str_replace(' ', '', $row[4]);
                }

                $dql .= implode(' AND ', $where);

                $results = $this->em->createQuery($dql)
                  ->setParameters($params)
                  ->getArrayResult();

                if (count($results))
                {
                  $entry->stock = $this->em->getReference('\Stock\Entity\Stock', $results[0]['stockId']);
                  $entry->regMatch = true;
                }

                //var_dump($entry);
                $this->em->persist($entry);
                $this->em->flush($entry);
                echo '.';
                
              } catch (Exception $e)
              {
                print_r($e->getMessage());
              }
            }
          }
        }
      }
    }
  }

}