xp-dev.com Git - namibia/blob - module/Adherence/src/Adherence/Service/Adherence.php

   1 <?php
   2
   3 namespace Adherence\Service;
   4
   5 /**
   6  * Manage Adherence data.
   7  * @author andre.fourie
   8  */
   9 class Adherence extends \Workspace\Service\DataBin
  10 {
  11
  12   /**
  13    * CRON functionality: Fetch and import AutoMate and AutoLine data.
  14    */
  15   public function cronProcess()
  16   {
  17     #-> Sources.
  18
  19     $automateFiles = array(
  20       './b4c_CJD.csv' => '/tmp/b4c_CJD.csv',
  21       './b4c_LSaker.csv' => '/tmp/b4c_LSaker.csv',
  22       './b4c_MultiFran.csv' => '/tmp/b4c_MultiFran.csv',
  23       './b4c_Toyota.csv' => '/tmp/b4c_Toyota.csv'
  24     );
  25
  26     $autolineFiles = array(
  27       './b4c_Ford.csv' => '/tmp/b4c_Ford.csv',
  28       './b4c_GM.csv' => '/tmp/b4c_GM.csv',
  29       './b4c_Honda.csv' => '/tmp/b4c_Honda.csv',
  30       './b4c_Nissan.csv' => '/tmp/b4c_Nissan.csv',
  31       './b4c_Cargo.csv' => '/tmp/b4c_Cargo.csv'
  32     );
  33     $amhgroupFiles = array(
  34       './Vehusesold.11' => '/tmp/Vehusesold.11',
  35       './Vehusesolh.11' => '/tmp/Vehusesolh.11',
  36       './Vehusesold.20' => '/tmp/Vehusesold.20'
  37     );
  38     $amhgroupFiles2 = array();
  39     // headers for the fields
  40 //                              Company number
  41 //                              Vehicle stock number
  42 //                              Vin number
  43 //                              Make
  44 //                              Model
  45 //                              Registration no
  46 //                              Colour
  47 //                              Trim
  48 //                              Odometer
  49 //                              Condition
  50 //                              M&M Code
  51 //                              Cost
  52 //                              Retail
  53     //'./Vehusestk.11'          => '/tmp/Vehusestk.11',
  54     //'./Vehusestkh.11'         => '/tmp/Vehusestkh.11',
  55     //'./Vehusestk.20'          => '/tmp/Vehusestk.20'
  56 //              );
  57
  58     $amhgroupFiles3 = array();
  59     //headers for the fields
  60 //                                                              Vehicle Stock number
  61 //                                                              Make
  62 //                                                              Model
  63 //                                                              Odometer
  64 //                                                              Vin
  65 //                                                              Registration no
  66 //                                                              Accounting month
  67 //                                                              Date sold
  68 //                                                              './vhsleb4car.11'               => '/tmp/vhsleb4car.11'
  69 //              );
  70
  71
  72
  73     $fileMap = array(
  74       '/tmp/b4c_Ford.csv' => 'Ford',
  75       '/tmp/b4c_GM.csv' => 'GM',
  76       '/tmp/b4c_Honda.csv' => 'Honda',
  77       '/tmp/b4c_Nissan.csv' => 'Nissan',
  78       '/tmp/b4c_Cargo.csv' => 'Cargo',
  79       '/tmp/b4c_CJD.csv' => 'CJD',
  80       '/tmp/b4c_LSaker.csv' => 'LSaker',
  81       '/tmp/b4c_MultiFran.csv' => 'MultiFran',
  82       '/tmp/b4c_Toyota.csv' => 'Toyota',
  83       '/tmp/Vehusestk.11' => 'Multifranchise',
  84       '/tmp/Vehusestkh.11' => 'Hyundai',
  85       '/tmp/Vehusestk.20' => 'Pearldb',
  86       '/tmp/Vehusesold.11' => 'Multifranchise',
  87       '/tmp/Vehusesolh.11' => 'Hyundai',
  88       '/tmp/Vehusesold.20' => 'Pearldb',
  89       '/tmp/vhsleb4car.11' => 'Hyundai'
  90     );
  91     $groupMap = array(
  92       // Imperial
  93       'Ford' => 1,
  94       'GM' => 1,
  95       'Honda' => 1,
  96       'Nissan' => 1,
  97       'Cargo' => 1,
  98       'CJD' => 1,
  99       'LSaker' => 1,
 100       'MultiFran' => 1,
 101       'Toyota' => 1,
 102       // AMH
 103       'Multifranchise' => 3,
 104       'Hyundai' => 3,
 105       'Pearldb' => 3,
 106       'Multifranchise' => 3,
 107       'Hyundai' => 3,
 108       'Pearldb' => 3,
 109       'Hyundai' => 3
 110     );
 111
 112     $ftpWrapper = new \Utility\Remote\Ftp(
 113       '197.242.75.234', //'127.0.0.1'
 114       'automate', 'aut0mat3@', 180, true
 115     );
 116     $ftpWrapper2 = new \Utility\Remote\Ftp(
 117       'ftps.amhgroup.net', 'bid4cars', 'rZMd0GJx', 180, true
 118     );
 119
 120     #-> Cleanup map.
 121     $filter = array(
 122       'tba',
 123       'ref',
 124       'na',
 125       'n/a',
 126       'n\\a',
 127       '#na',
 128       'vin',
 129       'eng',
 130       'reg',
 131       'none',
 132     );
 133
 134
 135     #-> Cleanup existing inported data that does not have company linked.
 136     /* $results = $this->em->createQuery(
 137       'SELECT adherence '
 138       . 'FROM \Adherence\Entity\Adherence adherence '
 139       . 'WHERE adherence.company IS NULL'
 140       )
 141       ->getResult();
 142       foreach ($results as $adherence)
 143       {
 144       $search = 'Automate' == $adherence->type
 145       ? array(
 146       'automateFile'                    => $adherence->file,
 147       'automateDealerCode'      => $adherence->dealerCode,
 148       'group'                           => $groupMap[$adherence->file]
 149       )
 150       : array(
 151       'autolineFile'                    => $adherence->file,
 152       'autolineDealerCode'      => $adherence->dealerCode,
 153       'group'                           => $groupMap[$adherence->file]
 154       );
 155
 156       $company = $this->em
 157       ->getRepository('\Company\Entity\Company')
 158       ->findOneBy($search);
 159       if (!is_null($company))
 160       {
 161       $adherence->company = $company;
 162       $this->em->flush($adherence);
 163       }
 164       } */
 165
 166
 167     #-> Import AutoMate csv
 168     $companies = array();
 169     foreach ($automateFiles as $remoteFile => $localFile)
 170     {
 171       echo "\n\nProcessing $localFile\n";
 172       $downloadOk = $ftpWrapper->downloadFile($localFile, $remoteFile, FTP_ASCII);
 173       if (!$downloadOk || !file_exists($localFile))
 174       {
 175         continue;
 176       }
 177       $companies[$localFile] = array();
 178       $csvImporter = new \Utility\Import\Csv($localFile);
 179       $csvImporter->setDelimiter(',');
 180       $found = 0;
 181       while (($record = $csvImporter->getRecord(\Utility\Import\Csv::FETCH_LAZY)) !== false)
 182       {
 183         #-> Safety checks.
 184         if ('T' != trim($record[6]) && 't' != trim($record[6]))
 185         {
 186           echo '<';
 187           continue;
 188         }
 189         $hash = md5(serialize($record));
 190         $record[7] = str_replace(' ', '', $record[7]);
 191         if (empty($record[1]) || !is_numeric(trim($record[7])))
 192         {
 193           echo "! \n";
 194           continue;
 195         }
 196
 197
 198         #-> Change date format from d-m-Y to Y-m-d.
 199         $parts = explode('/', $record[1]);
 200         $record[1] = new \DateTime($parts[2] . '-' . $parts[1] . '-' . $parts[0]);
 201         $dateFrom = new \DateTime(date('Y-m-d', mktime(1, 1, 1, $parts[1] - 4, $parts[0], $parts[2])));
 202
 203         #-> Check if this will cause duplicate.
 204         $existing = $this->em
 205           ->getRepository('\Adherence\Entity\Adherence')
 206           ->findOneBy(array(
 207           'hash' => $hash,
 208           'dateSold' => $record[1]
 209         ));
 210         if (!is_null($existing))
 211         {
 212           $found++;
 213           echo "-";
 214           continue;
 215         }
 216         else
 217         {
 218           $found = 0;
 219         }
 220
 221         #-> Log to import table.
 222         if (!isset($companies[$localFile][$record[0]]))
 223         {
 224           $companies[$localFile][$record[0]] = $this->em
 225             ->getRepository('\Company\Entity\Company')
 226             ->findOneBy(array(
 227             'automateFile' => $fileMap[$localFile],
 228             'automateDealerCode' => $record[0],
 229             'group' => $groupMap[$fileMap[$localFile]]
 230           ));
 231         }
 232         $companyId = $companies[$localFile][$record[0]];
 233         try
 234         {
 235           $entry = new \Adherence\Entity\Adherence();
 236           $entry->fromArray(array(
 237             'hash' => $hash,
 238             'source' => 'Imperial',
 239             'type' => 'Automate',
 240             'file' => $fileMap[$localFile],
 241             'dealerCode' => $record[0],
 242             'company' => $companyId,
 243             'dateSold' => $record[1],
 244             'stockNumber' => trim($record[2]),
 245             'vinNumber' => trim($record[3]),
 246             'soldTo' => trim($record[4]),
 247             'amount' => trim($record[7]),
 248             'registrationNumber' => trim($record[8]),
 249             'engineNumber' => trim($record[9]),
 250             'make' => trim($record[10])
 251           ));
 252           $this->em->persist($entry);
 253           $this->em->flush($entry);
 254           echo '.';
 255
 256
 257           #-> Match on VIN, registration or engine number.
 258           $dql = 'SELECT priceGuide.id AS pgId, stock.id AS stockId '
 259             . 'FROM \PriceGuide\Entity\PriceGuide priceGuide '
 260             . 'JOIN priceGuide.stock stock '
 261             . 'WHERE priceGuide.created BETWEEN :dateFrom AND :dateTo ';
 262           $where = array();
 263           $params = array(
 264             'dateFrom' => $dateFrom,
 265             'dateTo' => $record[1]
 266           );
 267           if ('' != $entry->vinNumber && !in_array(strtolower($entry->vinNumber), $filter))
 268           {
 269             $where[] = 'stock.vinNumber = :vinNumber';
 270             $params['vinNumber'] = $entry->vinNumber;
 271           }
 272           if ('' != $entry->registrationNumber && !in_array(strtolower($entry->registrationNumber), $filter))
 273           {
 274             $where[] = 'stock.registrationNumber = :registrationNumber';
 275             $params['registrationNumber'] = $entry->registrationNumber;
 276           }
 277           if ('' != $entry->engineNumber && !in_array(strtolower($entry->engineNumber), $filter))
 278           {
 279             $where[] = 'stock.engineNumber = :engineNumber';
 280             $params['engineNumber'] = $entry->engineNumber;
 281           }
 282           $dql .= 'AND (' . implode(' OR ', $where) . ')';
 283           $results = $this->em->createQuery($dql)
 284             ->setParameters($params)
 285             ->getArrayResult();
 286           $stockFound = array();
 287           foreach ($results as $result)
 288           {
 289             if (isset($stockFound[$result['stockId']]))
 290             {
 291               continue;
 292             }
 293             $stockFound[$result['stockId']] = true;
 294             $link = new \Adherence\Entity\AdherenceStock();
 295             $link->adherence = $entry;
 296             $link->stock = $this->em->getReference('\Stock\Entity\Stock', $result['stockId']);
 297             $this->em->persist($link);
 298             $this->em->flush($link);
 299             $link = null;
 300           }
 301
 302
 303           #-> Cleanup.
 304           $this->em->clear();
 305         } catch (\Exception $e)
 306         {
 307           echo "-----------------------\n";
 308           echo $e->getMessage() . "\n";
 309           var_dump($record);
 310           echo "\n";
 311         }
 312       }
 313       $csvImporter = null;
 314       unlink($localFile);
 315     }
 316
 317     #-> Import AutoLine csv
 318     $companies = array();
 319     foreach ($autolineFiles as $remoteFile => $localFile)
 320     {
 321       echo "\n\nProcessing $localFile\n";
 322       $downloadOk = $ftpWrapper->downloadFile($localFile, $remoteFile, FTP_ASCII);
 323       if (!$downloadOk || !file_exists($localFile))
 324       {
 325         continue;
 326       }
 327       $companies[$localFile] = array();
 328       $csvImporter = new \Utility\Import\Csv($localFile);
 329       $csvImporter->setDelimiter(',');
 330       $found = 0;
 331       $yearPrepend = '/tmp/b4c_Cargo.csv' == $localFile ? '' : '20';
 332       while (($record = $csvImporter->getRecord(\Utility\Import\Csv::FETCH_LAZY)) !== false)
 333       {
 334         #-> Safety checks.
 335         if ('/tmp/b4c_Cargo.csv' == $localFile && 'T' != trim($record[6]) && 't' != trim($record[6]))
 336         {
 337           continue;
 338         }
 339         $hash = md5(serialize($record));
 340         $record[7] = str_replace(' ', '', $record[7]);
 341         if (empty($record[1]) || !is_numeric($record[7]))
 342         {
 343           echo "! \n";
 344           continue;
 345         }
 346
 347         #-> Change date format from d-m-Y to Y-m-d.
 348         $parts = explode('/', $record[1]);
 349         $record[1] = new \DateTime($yearPrepend . $parts[2] . '-' . $parts[1] . '-' . $parts[0]);
 350         $dateFrom = new \DateTime(date('Y-m-d', mktime(1, 1, 1, $parts[1] - 4, $parts[0], $parts[2])));
 351
 352         #-> Check if this will cause duplicate.
 353         $existing = $this->em
 354           ->getRepository('\Adherence\Entity\Adherence')
 355           ->findOneBy(array(
 356           'hash' => $hash,
 357           'file' => $fileMap[$localFile],
 358           'dateSold' => $record[1]
 359         ));
 360         if (!is_null($existing))
 361         {
 362           $found++;
 363           echo "-";
 364           continue;
 365         }
 366         else
 367         {
 368           $found = 0;
 369         }
 370
 371         #-> Log to import table.
 372         if (!isset($companies[$localFile][$record[0]]))
 373         {
 374           $companies[$localFile][$record[0]] = $this->em
 375             ->getRepository('\Company\Entity\Company')
 376             ->findOneBy(array(
 377             'autolineFile' => $fileMap[$localFile],
 378             'autolineDealerCode' => $record[0],
 379             'group' => $groupMap[$fileMap[$localFile]]
 380           ));
 381         }
 382         $companyId = $companies[$localFile][$record[0]];
 383         try
 384         {
 385           $entry = new \Adherence\Entity\Adherence();
 386           $entry->fromArray(array(
 387             'hash' => $hash,
 388             'source' => 'Imperial',
 389             'type' => 'Autoline',
 390             'file' => $fileMap[$localFile],
 391             'dealerCode' => $record[0],
 392             'company' => $companyId,
 393             'dateSold' => $record[1],
 394             'stockNumber' => trim($record[2]),
 395             'vinNumber' => trim($record[3]),
 396             'soldTo' => trim($record[4]),
 397             'amount' => trim($record[7]),
 398             'registrationNumber' => trim($record[8]),
 399             'engineNumber' => trim($record[9]),
 400             'make' => trim($record[10])
 401           ));
 402           $this->em->persist($entry);
 403           $this->em->flush($entry);
 404           echo '.';
 405
 406
 407           #-> Match on VIN, registration or engine number.
 408           $dql = 'SELECT priceGuide.id AS pgId, stock.id AS stockId '
 409             . 'FROM \PriceGuide\Entity\PriceGuide priceGuide '
 410             . 'JOIN priceGuide.stock stock '
 411             . 'WHERE priceGuide.created BETWEEN :dateFrom AND :dateTo ';
 412           $where = array();
 413           $params = array(
 414             'dateFrom' => $dateFrom,
 415             'dateTo' => $record[1]
 416           );
 417           if ('' != $entry->vinNumber && !in_array(strtolower($entry->vinNumber), $filter))
 418           {
 419             $where[] = 'stock.vinNumber = :vinNumber';
 420             $params['vinNumber'] = $entry->vinNumber;
 421           }
 422           if ('' != $entry->registrationNumber && !in_array(strtolower($entry->registrationNumber), $filter))
 423           {
 424             $where[] = 'stock.registrationNumber = :registrationNumber';
 425             $params['registrationNumber'] = $entry->registrationNumber;
 426           }
 427           if ('' != $entry->engineNumber && !in_array(strtolower($entry->engineNumber), $filter))
 428           {
 429             $where[] = 'stock.engineNumber = :engineNumber';
 430             $params['engineNumber'] = $entry->engineNumber;
 431           }
 432           $dql .= 'AND (' . implode(' OR ', $where) . ')';
 433           $results = $this->em->createQuery($dql)
 434             ->setParameters($params)
 435             ->getArrayResult();
 436           $stockFound = array();
 437           foreach ($results as $result)
 438           {
 439             if (isset($stockFound[$result['stockId']]))
 440             {
 441               continue;
 442             }
 443             $stockFound[$result['stockId']] = true;
 444             $link = new \Adherence\Entity\AdherenceStock();
 445             $link->adherence = $entry;
 446             $link->stock = $this->em->getReference('\Stock\Entity\Stock', $result['stockId']);
 447             $this->em->persist($link);
 448             $this->em->flush($link);
 449             $link = null;
 450           }
 451
 452
 453           #-> Cleanup.
 454           $this->em->clear();
 455         } catch (\Exception $e)
 456         {
 457           echo "-----------------------\n";
 458           echo $e->getMessage() . "\n";
 459           var_dump($record);
 460           echo "\n";
 461         }
 462       }
 463       $csvImporter = null;
 464       unlink($localFile);
 465     }
 466
 467     #-> Import AMH Group csv
 468     $companies = array();
 469     foreach ($amhgroupFiles as $remoteFile => $localFile)
 470     {
 471       echo "\n\nProcessing $localFile\n";
 472       $downloadOk = $ftpWrapper2->downloadFile($localFile, $remoteFile, FTP_ASCII);
 473       if (!$downloadOk || !file_exists($localFile))
 474       {
 475         continue;
 476       }
 477       $companies[$localFile] = array();
 478       $csvImporter = new \Utility\Import\Csv($localFile);
 479       $csvImporter->setDelimiter(' ');
 480       $found = 0;
 481
 482       while (($record = $csvImporter->getRecord(\Utility\Import\Csv::FETCH_LAZY)) !== false)
 483       {
 484         #-> Safety checks.
 485         $hash = md5(serialize($record));
 486         if (empty($record[2]) || !is_numeric(trim($record[8])) || !is_numeric(trim($record[9])))
 487         {
 488           echo "! \n";
 489           continue;
 490         }
 491
 492         #-> Change date format from d-m-Y to Y-m-d.
 493         $parts = explode('/', $record[2]);
 494         $record[2] = new \DateTime($parts[2] . '-' . $parts[1] . '-' . $parts[0]);
 495         $dateFrom = new \DateTime(date('Y-m-d', mktime(1, 1, 1, $parts[1] - 4, $parts[0], $parts[2])));
 496
 497         #-> Check if this will cause duplicate.
 498         $existing = $this->em
 499           ->getRepository('\Adherence\Entity\Adherence')
 500           ->findOneBy(array(
 501           'hash' => $hash,
 502           'dateSold' => $record[2]
 503         ));
 504         if (!is_null($existing))
 505         {
 506           $found++;
 507           echo "-";
 508           continue;
 509         }
 510         else
 511         {
 512           $found = 0;
 513         }
 514
 515         #-> Log to import table.
 516         if (!isset($companies[$localFile][$record[0]]))
 517         {
 518           $companies[$localFile][$record[0]] = $this->em
 519             ->getRepository('\Company\Entity\Company')
 520             ->findOneBy(array(
 521             'amhgroupFile' => $fileMap[$localFile],
 522             'amhgroupDealerCode' => $record[0],
 523             'group' => $groupMap[$fileMap[$localFile]]
 524           ));
 525         }
 526         $companyId = $companies[$localFile][$record[0]];
 527
 528
 529         try
 530         {
 531           $entry = new \Adherence\Entity\Adherence();
 532           $entry->fromArray(array(
 533             'hash' => $hash,
 534             'source' => 'AMHgroup',
 535             'type' => 'AMHgroup',
 536             'file' => $fileMap[$localFile],
 537             'dealerCode' => $record[0],
 538             'company' => $companyId,
 539             'dateSold' => $record[2],
 540             'stockNumber' => trim($record[1]),
 541             'vinNumber' => trim($record[3]),
 542             'soldTo' => trim($record[7]),
 543             'amount' => trim($record[8]),
 544             'registrationNumber' => trim($record[4])
 545           ));
 546
 547           $this->em->persist($entry);
 548           $this->em->flush($entry);
 549           echo '.';
 550
 551
 552           #-> Match on VIN, registration or engine number.
 553           $dql = 'SELECT priceGuide.id AS pgId, stock.id AS stockId '
 554             . 'FROM \PriceGuide\Entity\PriceGuide priceGuide '
 555             . 'JOIN priceGuide.stock stock '
 556             . 'WHERE priceGuide.created BETWEEN :dateFrom AND :dateTo ';
 557           $where = array();
 558           $params = array(
 559             'dateFrom' => $dateFrom,
 560             'dateTo' => $record[2]
 561           );
 562           if ('' != $entry->vinNumber && !in_array(strtolower($entry->vinNumber), $filter))
 563           {
 564             $where[] = 'stock.vinNumber = :vinNumber';
 565             $params['vinNumber'] = $entry->vinNumber;
 566           }
 567           if ('' != $entry->registrationNumber && !in_array(strtolower($entry->registrationNumber), $filter))
 568           {
 569             $where[] = 'stock.registrationNumber = :registrationNumber';
 570             $params['registrationNumber'] = $entry->registrationNumber;
 571           }
 572
 573           $dql .= 'AND (' . implode(' OR ', $where) . ')';
 574           $results = $this->em->createQuery($dql)
 575             ->setParameters($params)
 576             ->getArrayResult();
 577           $stockFound = array();
 578
 579           //var_dump($results);
 580
 581           foreach ($results as $result)
 582           {
 583             if (isset($stockFound[$result['stockId']]))
 584             {
 585               continue;
 586             }
 587             $stockFound[$result['stockId']] = true;
 588             $link = new \Adherence\Entity\AdherenceStock();
 589             $link->adherence = $entry;
 590             $link->stock = $this->em->getReference('\Stock\Entity\Stock', $result['stockId']);
 591             $this->em->persist($link);
 592             $this->em->flush($link);
 593             $link = null;
 594           }
 595
 596
 597           #-> Cleanup.
 598           $this->em->clear();
 599         } catch (\Exception $e)
 600         {
 601           echo "-----------------------\n";
 602           echo $e->getMessage() . "\n";
 603           //var_dump($record);
 604           echo "\n";
 605         }
 606       }
 607       $csvImporter = null;
 608       unlink($localFile);
 609     }
 610
 611     #-> Import AMH group Files2 csv
 612     $companies = array();
 613     foreach ($amhgroupFiles2 as $remoteFile => $localFile)
 614     {
 615       echo "Processing $localFile\n";
 616       $downloadOk = $ftpWrapper2->downloadFile($localFile, $remoteFile, FTP_ASCII);
 617       if (!$downloadOk && !file_exists($localFile))
 618       {
 619         continue;
 620       }
 621
 622       $companies[$localFile] = array();
 623       $csvImporter = new \Utility\Import\Csv($localFile);
 624       $csvImporter->setDelimiter(' ');
 625       $record = $csvImporter->getRecord(\Utility\Import\Csv::FETCH_LAZY);
 626       if (false == $record)
 627       {
 628         continue;
 629       }
 630
 631       //var_dump($csvImporter->getRecord(\Utility\Import\Csv::FETCH_LAZY));
 632
 633       $found = 0;
 634       while (($record = $csvImporter->getRecord(\Utility\Import\Csv::FETCH_LAZY)) !== false)
 635       {
 636         #-> Safety checks.
 637         $hash = md5(serialize($record));
 638         if (!is_numeric(trim($record[11])) || !is_numeric(trim($record[12])))
 639         {
 640           echo "! \n";
 641           continue;
 642         }
 643
 644         // The file has no date field
 645 //                              #-> Change date format from d-m-Y to Y-m-d.
 646 //                              $parts = explode('/', $record[1]);
 647 //                              $record[1] = new \DateTime('20' . $parts[2] . '-' . $parts[1] . '-' . $parts[0]);
 648 //                                              $dateFrom = new \DateTime(date('Y-m-d', mktime(1, 1, 1, $parts[1] - 4, $parts[0], $parts[2])));
 649 //                                              #-> Check if this will cause duplicate.
 650 //                                              $existing = $this->em
 651 //                              ->getRepository('\Adherence\Entity\Adherence')
 652 //                              ->findOneBy(array(
 653 //                              'hash'          => $hash,
 654 //                                      'dateSold'      => $record[1]
 655 //                              ));
 656 //                              if (!is_null($existing))
 657 //                                              {
 658 //                              $found++;
 659 //                                                      continue;
 660 //                              }
 661 //                              else
 662 //                              {
 663 //                              $found = 0;
 664 //                              }
 665         #-> Log to import table.
 666         if (!isset($companies[$localFile][$record[0]]))
 667         {
 668           $companies[$localFile][$record[0]] = $this->em
 669             ->getRepository('\Company\Entity\Company')
 670             ->findOneBy(array(
 671             'amhgroupFile' => $fileMap[$localFile],
 672             'amhgroupDealerCode' => $record[0],
 673             'group' => $groupMap[$fileMap[$localFile]]
 674           ));
 675         }
 676         $companyId = $companies[$localFile][$record[0]];
 677         try
 678         {
 679           $entry = new \Adherence\Entity\Adherence();
 680           $entry->fromArray(array(
 681             'hash' => $hash,
 682             'source' => 'AMHgroup',
 683             'type' => 'AMHgroup',
 684             'file' => $fileMap[$localFile],
 685             'dealerCode' => $record[0],
 686             'company' => $companyId,
 687             'dateSold' => '',
 688             'stockNumber' => trim($record[1]),
 689             'vinNumber' => trim($record[2]),
 690             'soldTo' => '',
 691             'amount' => trim($record[11]),
 692             'registrationNumber' => trim($record[5]),
 693             'engineNumber' => '',
 694             'make' => trim($record[3])
 695           ));
 696
 697           var_dump($entry);
 698           $this->em->persist($entry);
 699           $this->em->flush($entry);
 700           echo '.';
 701
 702
 703           #-> Match on VIN, registration or engine number.
 704           $dql = 'SELECT priceGuide.id AS pgId, stock.id AS stockId '
 705             . 'FROM \PriceGuide\Entity\PriceGuide priceGuide '
 706             . 'JOIN priceGuide.stock stock ';
 707           //. 'WHERE priceGuide.created BETWEEN :dateFrom AND :dateTo ';
 708           $where = array();
 709           //                            $params = array(
 710           //                                    'dateFrom'      => $dateFrom,
 711           //                                    'dateTo'        => $record[1]
 712           //                                    );
 713           if ('' != $entry->vinNumber && !in_array(strtolower($entry->vinNumber), $filter))
 714           {
 715             $where[] = 'stock.vinNumber = :vinNumber';
 716             $params['vinNumber'] = $entry->vinNumber;
 717           }
 718           if ('' != $entry->registrationNumber && !in_array(strtolower($entry->registrationNumber), $filter))
 719           {
 720             $where[] = 'stock.registrationNumber = :registrationNumber';
 721             $params['registrationNumber'] = $entry->registrationNumber;
 722           }
 723
 724           $dql .= 'AND (' . implode(' OR ', $where) . ')';
 725
 726           $results = $this->em->createQuery($dql)
 727             ->setParameters($params)
 728             ->getArrayResult();
 729           $stockFound = array();
 730
 731           foreach ($results as $result)
 732           {
 733             if (isset($stockFound[$result['stockId']]))
 734             {
 735               continue;
 736             }
 737             $stockFound[$result['stockId']] = true;
 738             $link = new \Adherence\Entity\AdherenceStock();
 739             $link->adherence = $entry;
 740             $link->stock = $this->em->getReference('\Stock\Entity\Stock', $result['stockId']);
 741             $this->em->persist($link);
 742             $this->em->flush($link);
 743             $link = null;
 744           }
 745
 746
 747           #-> Cleanup.
 748           $this->em->clear();
 749         } catch (\Exception $e)
 750         {
 751           echo "-----------------------\n";
 752           echo $e->getMessage() . "\n";
 753           var_dump($record);
 754           echo "\n";
 755         }
 756       }
 757       $csvImporter = null;
 758       unlink($localFile);
 759     }
 760
 761     #-> Import AMH group Files3 csv
 762     $companies = array(); // needs the company number field
 763     foreach ($amhgroupFiles3 as $remoteFile => $localFile)
 764     {
 765       echo "Processing $localFile\n";
 766       $downloadOk = $ftpWrapper2->downloadFile($localFile, $remoteFile, FTP_ASCII);
 767       if (!$downloadOk && !file_exists($localFile))
 768       {
 769         continue;
 770       }
 771
 772       $companies[$localFile] = array();
 773       $csvImporter = new \Utility\Import\Csv($localFile);
 774       $csvImporter->setDelimiter(' ');
 775       $record = $csvImporter->getRecord(\Utility\Import\Csv::FETCH_LAZY);
 776       if (false == $record)
 777       {
 778         continue;
 779       }
 780
 781       var_dump($csvImporter->getRecord(\Utility\Import\Csv::FETCH_LAZY));
 782       break;
 783
 784       $found = 0;
 785       while (($record = $csvImporter->getRecord(\Utility\Import\Csv::FETCH_LAZY)) !== false)
 786       {
 787         #-> Safety checks.
 788         $hash = md5(serialize($record));
 789
 790         if (empty($record[7]))
 791         {
 792           echo "! \n";
 793           continue;
 794         }
 795         #-> Change date format from d-m-Y to Y-m-d.
 796         $parts = explode('/', $record[7]);
 797         $record[7] = new \DateTime('20' . $parts[2] . '-' . $parts[1] . '-' . $parts[0]);
 798         $dateFrom = new \DateTime(date('Y-m-d', mktime(1, 1, 1, $parts[1] - 4, $parts[0], $parts[2])));
 799
 800
 801         #-> Check if this will cause duplicate.
 802         $existing = $this->em
 803           ->getRepository('\Adherence\Entity\Adherence')
 804           ->findOneBy(array(
 805           'hash' => $hash,
 806           'dateSold' => $record[7]
 807         ));
 808         if (!is_null($existing))
 809         {
 810           $found++;
 811           continue;
 812         }
 813         else
 814         {
 815           $found = 0;
 816         }
 817         $test = $companies[$localFile][$record[0]];
 818         var_dump($test);
 819
 820         #-> Log to import table.
 821         if (!isset($companies[$localFile][$record[0]]))
 822         {
 823           $companies[$localFile][$record[0]] = $this->em
 824             ->getRepository('\Company\Entity\Company')
 825             ->findOneBy(array(
 826             'amhgroupFile' => $fileMap[$localFile],
 827             'amhgroupDealerCode' => $record[0],
 828             'group' => $groupMap[$fileMap[$localFile]]
 829           ));
 830         }
 831         $companyId = $companies[$localFile][$record[0]];
 832
 833         var_dump($companyId);
 834         break;
 835
 836         try
 837         {
 838           $entry = new \Adherence\Entity\Adherence();
 839           $entry->fromArray(array(
 840             'hash' => $hash,
 841             'source' => 'AMHgroup',
 842             'type' => 'AMHgroup',
 843             'file' => $fileMap[$localFile],
 844             'dealerCode' => $record[0],
 845             'company' => $companyId,
 846             'dateSold' => $record[7],
 847             'stockNumber' => '',
 848             'vinNumber' => $record[4],
 849             'soldTo' => '',
 850             'amount' => '',
 851             'registrationNumber' => $record[5],
 852             'engineNumber' => '',
 853             'make' => $record[1]
 854           ));
 855
 856           //var_dump($entry);
 857           //break;
 858
 859           $this->em->persist($entry);
 860           $this->em->flush($entry);
 861           echo '.';
 862
 863
 864           #-> Match on VIN, registration or engine number.
 865           $dql = 'SELECT priceGuide.id AS pgId, stock.id AS stockId '
 866             . 'FROM \PriceGuide\Entity\PriceGuide priceGuide '
 867             . 'JOIN priceGuide.stock stock '
 868             . 'WHERE priceGuide.created BETWEEN :dateFrom AND :dateTo ';
 869           $where = array();
 870           $params = array(
 871             'dateFrom' => $dateFrom,
 872             'dateTo' => $record[7]
 873           );
 874           if ('' != $entry->vinNumber && !in_array(strtolower($entry->vinNumber), $filter))
 875           {
 876             $where[] = 'stock.vinNumber = :vinNumber';
 877             $params['vinNumber'] = $entry->vinNumber;
 878           }
 879           if ('' != $entry->registrationNumber && !in_array(strtolower($entry->registrationNumber), $filter))
 880           {
 881             $where[] = 'stock.registrationNumber = :registrationNumber';
 882             $params['registrationNumber'] = $entry->registrationNumber;
 883           }
 884
 885           $dql .= 'AND (' . implode(' OR ', $where) . ')';
 886
 887           $results = $this->em->createQuery($dql)
 888             ->setParameters($params)
 889             ->getArrayResult();
 890           $stockFound = array();
 891
 892           var_dump($results);
 893           break;
 894
 895           foreach ($results as $result)
 896           {
 897             if (isset($stockFound[$result['stockId']]))
 898             {
 899               continue;
 900             }
 901             $stockFound[$result['stockId']] = true;
 902             $link = new \Adherence\Entity\AdherenceStock();
 903             $link->adherence = $entry;
 904             $link->stock = $this->em->getReference('\Stock\Entity\Stock', $result['stockId']);
 905             $this->em->persist($link);
 906             $this->em->flush($link);
 907             $link = null;
 908           }
 909
 910
 911           #-> Cleanup.
 912           $this->em->clear();
 913         } catch (\Exception $e)
 914         {
 915           echo "-----------------------\n";
 916           echo $e->getMessage() . "\n";
 917           var_dump($record);
 918           echo "\n";
 919         }
 920       }
 921       $csvImporter = null;
 922       unlink($localFile);
 923     }
 924   }
 925
 926   public function cronTradeSales()
 927   {
 928     $ftpWrapper = new \Utility\Remote\Ftp(
 929       'ftp.nirph.com', //'127.0.0.1'
 930       'b4c@nirph.com', 'B!d4C@r$', 180, true
 931     );
 932
 933     $localFile = '/tmp/trade_sales' . date('Y-m') . '.xlsx';
 934     $remoteFile = '/Monthly AMH Trade Sales Report/TradeSales' . date('Y-m') . '.xlsx';
 935
 936     $downloadOk = $ftpWrapper->downloadFile($localFile, $remoteFile, FTP_ASCII);
 937     if ($downloadOk && file_exists($localFile))
 938     {
 939       /** Identify the type of $inputFileName * */
 940       $inputFileType = \PHPExcel_IOFactory::identify($localFile);
 941       /** Create a new Reader of the type that has been identified * */
 942       $objReader = \PHPExcel_IOFactory::createReader($inputFileType);
 943       /** Advise the Reader that we only want to load cell data * */
 944       $objReader->setReadDataOnly(true);
 945       /** Load $inputFileName to a PHPExcel Object * */
 946       $objPHPExcel = $objReader->load($localFile);
 947
 948       $worksheetData = $objReader->listWorksheetInfo($localFile);
 949
 950       foreach ($worksheetData as $worksheet)
 951       {
 952         $objPHPExcel->setActiveSheetIndexByName($worksheet['worksheetName']);
 953         $sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, false, false);
 954
 955         if (trim($sheetData[0][0]) == 'Dealer Name')
 956         {
 957           foreach ($sheetData as $row)
 958           {
 959             if (trim($row[0]) != 'Dealer Name' && $row[0] != '')
 960             {
 961               try
 962               {
 963                 $entry = new \Adherence\Entity\TradeSales();
 964                 $entry->fromArray(array(
 965                   'dealerName' => $row[0],
 966                   'stockNumber' => $row[2],
 967                   'invoiceNumber' => $row[3],
 968                   'regNumber' => $row[4],
 969                   'vinNumber' => $row[5],
 970                   'vehicleMake' => $row[6],
 971                   'regDate' => new \DateTime(gmdate("d-m-Y H:i:s", ($row[7] - 25569) * 86400)),
 972                   'saleDate' => new \DateTime(gmdate("d-m-Y H:i:s", ($row[8] - 25569) * 86400)),
 973                   'saleType' => $row[9],
 974                   'totalCost' => $row[10],
 975                   'salesTotal' => $row[11],
 976                   'soldTo' => $row[12]
 977                 ));
 978
 979                 #-> Match on VIN
 980                 $dql = 'SELECT stock.id AS stockId '
 981                   . 'FROM \Stock\Entity\Stock stock WHERE ';
 982                 $where = $params = array();
 983                 if ('' != $row[4])
 984                 {
 985                   $where[] = 'stock.vinNumber = :vinNumber';
 986                   $params['vinNumber'] = str_replace(' ', '', $row[5]);
 987                 }
 988
 989                 $dql .= implode(' AND ', $where);
 990
 991                 $results = $this->em->createQuery($dql)
 992                   ->setParameters($params)
 993                   ->getArrayResult();
 994
 995                 if (count($results))
 996                 {
 997                   $entry->stock = $this->em->getReference('\Stock\Entity\Stock', $results[0]['stockId']);
 998                   $entry->vinMatch = true;
 999                 }
1000
1001                 #-> Match on registration number
1002                 $dql = 'SELECT stock.id AS stockId '
1003                   . 'FROM \Stock\Entity\Stock stock WHERE ';
1004                 $where = $params = array();
1005                 if ('' != $row[4])
1006                 {
1007                   $where[] = 'stock.registrationNumber = :registrationNumber';
1008                   $params['registrationNumber'] = str_replace(' ', '', $row[4]);
1009                 }
1010
1011                 $dql .= implode(' AND ', $where);
1012
1013                 $results = $this->em->createQuery($dql)
1014                   ->setParameters($params)
1015                   ->getArrayResult();
1016
1017                 if (count($results))
1018                 {
1019                   $entry->stock = $this->em->getReference('\Stock\Entity\Stock', $results[0]['stockId']);
1020                   $entry->regMatch = true;
1021                 }
1022
1023                 //var_dump($entry);
1024                 $this->em->persist($entry);
1025                 $this->em->flush($entry);
1026                 echo '.';
1027
1028               } catch (Exception $e)
1029               {
1030                 print_r($e->getMessage());
1031               }
1032             }
1033           }
1034         }
1035       }
1036     }
1037   }
1038
1039 }