edmunds-api.js

/**
 * Copyright © 2015 Magento. All rights reserved.
 * See COPYING.txt for license details.
 */

module.exports = function (grunt) {
    'use strict';

    var exec = require('child_process').execSync,
        glob = require('glob'),
        fs = require('fs'),
        path = require('path'),
        fst = require('../tools/fs-tools.js'),
        csv = require("fast-csv"),
        cheerio = require('cheerio'),
        json2csv = require('json2csv'),
        csv2json = require('csv2json'),
        request = require("request"),
         _ = require('underscore')._,
        Converter = require("csvtojson").Converter,
        async = require("async"),
        redis = require("redis"),
        cache = redis.createClient(),
        processLogging =false,
        debugLogging=true,
        errorLogging=true,
        log     = grunt.log.write,
        ok      = grunt.log.ok,
        error   = grunt.log.error;

   grunt.option('stack', true);  

   var formatCurrency = function (amount)
    {
    var i = parseFloat(amount);
    if(isNaN(i)) { i = 0.00; }
    var minus = '';
    if(i < 0) { minus = '-'; }
    i = Math.abs(i);
    i = parseInt((i + .005) * 100);
    i = i / 100;
    var s = new String(i);
    if(s.indexOf('.') < 0) { s += '.00'; }
    if(s.indexOf('.') == (s.length - 2)) { s += '0'; }
    s = minus + s;
    return s;
    }   

    var buildDemandData = function () {

        var dataFile = glob.sync('dev/tools/grunt/data/source/demand/carsforsale/list.html')[0],
            demandList = fst.getData(dataFile),
            html = fs.readFileSync('dev/tools/grunt/data/source/demand/carsforsale/list.html'),
            $ = cheerio.load(html),
            fields = ['rank', 'vehicle', 'view','inventory', 'ratio', 'region_price','national_price'],
            data=Array();
       
        $("#contentContainer > div.demand").each(function( index ) {

            var rankStr = $(this).find('.col-sm-2 > p ').text().trim();
            var rank = rankStr.substring( 0 , rankStr.indexOf (' of ') );

            var vehicle = $(this).find('.col-sm-3 > p ').text().trim();
            var view = $(this).find('div:nth-child(3) > p ').text().trim();
            var inventory = $(this).find('div:nth-child(4) > .v-align > a ').text().trim();

            var ratio = $(this).find('div:nth-child(5) > p ').text().trim();
            var region_price = $(this).find('div:nth-child(6) > .v-align > a:nth-child(2) ').text().trim().replace('Region: ','');
            var national_price = $(this).find('div:nth-child(6) > .v-align > a:nth-child(3) ').text().trim().replace('National: ','');

            var row = { rank:rank , 
                        vehicle: vehicle, 
                        view: view , 
                        inventory: inventory, 
                        ratio: ratio, 
                        region_price:region_price, 
                        national_price: national_price
                    };

            //console.log (row);
            data.push (row);

        });

        return {data: data, fields:fields};

    }

    var getManheimMMR = function  (vin) {

        /// TBD

    }    

    var getRunListFile = function (runListDate){
        return 'dev/tools/grunt/data/run-lists/' + runListDate + '/list.csv';
    }

    var getDemandDataFile = function (runListDate){
        return 'dev/tools/grunt/data/source/demand/carsforsale/list.csv';
    }

    var loadDemanData_array = function (callback) {
        
        var converter = new Converter({});

        converter.fromFile(getDemandDataFile(),function(err,demandData){
            callback(_(demandData).toArray());
        });

    }

    var searchDemand = function (vehicle, demandList) {

        function textMatch(text, searchWords){

            function trimByWord(sentence) {
                sentence = sentence.toLowerCase();
                // the regex should exclude mercedes-benz -> as mercedesbenz
                sentence = sentence.replace ('-','');
                return sentence.match(/\b(\w+)\b/g);
            }

            var currTest;
            var foundFlag=false,
                totalMatches=0,
                orgSeachString = searchWords,
                wordsMatched = [];

            var textWords = trimByWord (text);
            searchWords = trimByWord ( searchWords);

            // loop for every word in deman table lookup record
            for (var item in searchWords){
                foundFlag = false;

                for (var itemx in textWords)
                    if (searchWords[item] == textWords[itemx]) {
                        foundFlag = true;
                        wordsMatched.push (textWords[itemx]);
                        totalMatches++;
                    }

                // a typical searchWords is '2010 BMW 6 Series'
                // so, we expect to match the first three keys '2010' and 'BMW' and '6'
                if (!foundFlag && item < 3)
                    return false;

            }

    
            // CHECK FOR HYBRID
            // we need to theck word 'Hybrid': because '2010 BMW 325i' and  '2010 BMW 325i Hybrid' are in different lookup records
            // and we need to be sure the that matching record does not have a 'hybird' 
            // otherwise we lookup the wrong record.
            
            if ( 
                // if hybrid is not a already matched word
                wordsMatched.indexOf("Hybrid") == -1  
                && 
                // 'hybrid' exist in lookup string
                orgSeachString.search ('Hybrid') > -1 
                ) {
                    //console.log (wordsMatched, orgSeachString)
                    return false;
                }

            if (totalMatches > 2) {
                return true;
            }
            else {
                return false;
            }

        }    

        for (var lisItem in demandList) {

            if ( textMatch ( vehicle  ,  demandList[lisItem].vehicle ) ) {
                //console.log (demandList[lisItem].vehicle, ' (found in) -> ', vehicle );
                return demandList[lisItem];
            }
        }

        return null;
    }

    var prepareRunlist = function (runList) {

        for (var item in runList) {

            // BMW is written wierdly in source data
            if ( runList[item]['Make/Model'].search ('B M W') > -1 ) {
                runList[item]['Make/Model'] = runList[item]['Make/Model'].replace ('B M W', 'BMW');
                //console.log ('B M W replaced to BMW in :' + runList[item]['Make/Model'] );
            }

        }

        return _(runList).toArray() ;
    
    }

    var formatCondition = function (conditionStr) {

        if (!conditionStr)
            return null;
          
          var condition = conditionStr.substring(conditionStr.length-3);
          
          if (condition >= 4.5) {
            return 'Outstanding';
          } else if (condition >= 3.5 && condition < 4.5) {
            return 'Clean';
          } else if (condition >= 2.5 && condition < 3.5) {
            return 'Average';
          } 
          else if (condition >= 1.5 && condition < 2.5) {
            return 'Rough';
          } 
          else if (condition < 1.5) {
            return 'Damaged';
          } 
          
          // of not found; take average
          return 'Average';

    }

    var getVehicleData = function ( vehicleText, vin,  mileage,  condition, color,  top, finalCallback)  {

        var apiKey ='3vt78zdmjhubcb2ydatf62m7',
            zip='90241';

        // record
        if (processLogging)
            console.log (vehicleText, vin,  mileage,  condition, color,  top);

        function getStyleId (vehicleData) {


            var years = vehicleData.years,
                style = {};

            if (!years || !vehicleData.years || !vehicleData.years[0].styles) {
                return null;
            }

            if (years.length >1 ) {
                // @todo 
                // need improvements
                style = years [0].styles[0];

                error("  More than 1 style is available for this item: " + style );


            }else{
                style = years [0].styles[0];
            }

            if (!style)
                return null;

            if (processLogging)
                console.log ("  › Selected style:" + style.name );
            return style.id;

        }

        // Check cache
        cache.get(vin, function (err, cachedData) {

            cachedData = JSON.parse (cachedData);

            if ( cachedData && (

                    //if TMVData status is set, 
                    //there have been an issue with API lookup, 
                    //this should be renewed FORBIDDEN will be the status for failed calls
                    (cachedData.TMVData && !cachedData.TMVData.status )

                    ||

                    // API lookup is sucessfully made but a missing data returned
                    // in this case, just return the cache data to avoid another lookup
                    (cachedData.status == 'MISSINGDATA')  

                    ) 

                )  { 

                    if (processLogging)
                        ok('    Retrieved vehicle data from cache : ' + vin );

                    finalCallback(cachedData);

            } else {

                if (processLogging)
                    log('   Querying Edmunds API for: ' + vin);

                async.waterfall([
                    // VIN decode
                    function(callback) {
                        
                        request("https://api.edmunds.com/api/vehicle/v2/vins/" + vin +"?fmt=json&api_key=" + apiKey, 
                            function(error, response, vehicleData) {

                                if (vehicleData){
                                    callback(null, JSON.parse(vehicleData) );
                                }
                                else{
                                    callback({ error: 'VIN lookup failed:' + vehicleData }, null);
                                }

                            });

                    },
                    // Get pricing data for the vehicle
                    function(vehicleData, callback) {

                         // skip vehicles for not found vin
                         // we simply skip to the next item in runlist
                         // additional data will not be available
                         if (!vehicleData || vehicleData.status == 'NOT_FOUND' || vehicleData.status == 'FORBIDDEN' ) {
                             callback(null, null);
                             return;
                         }

                         // for some vehicles no style ID returns, wierdly
                        var styleId = getStyleId(vehicleData) ;
                        if (!styleId) {
                            if (errorLogging)
                                error('Style ID can not be retrieved in VIN lookup :' + vin + ' > ' + JSON.stringify(vehicleData) );

                            callback({ error: '1' }, vehicleData);
                            return;
                        }

                         if (!mileage || !_.isString(mileage) ) {
                            if (errorLogging)
                                error('Milage data can not be extracted:' + vin + ' > ' + JSON.stringify(vehicleData) );

                            callback({ error: '1' }, vehicleData);
                            return;
                        }

                        //Vehicle TMV decode
                        request("https://api.edmunds.com/v1/api/tmv/tmvservice/calculateusedtmv?" + 
                            "styleid=" + styleId + 
                            "&condition=" + condition +
                            "&mileage=" + parseInt ( mileage.replace(',','') )  +
                            //"&colorid=" +  "" +
                            "&zip=" + zip +
                            "&fmt=json&api_key=" + apiKey, 
                            function(error, response, TMVData) {


                                if (!TMVData || TMVData.status == 'NOT_FOUND' || TMVData.status == 'FORBIDDEN' ) {
                                     callback({ error: '2' }, null);
                                     return;
                                 }

                                try {
                                    TMVData = JSON.parse (TMVData);
                                    } catch(e) {
                                        if (e)
                                        TMVData = {};
                                }
                            
                              vehicleData.TMVData = TMVData;
                              callback(null, vehicleData);

                        });

                    }
                ], function (err, vehicleData) {

                    if (err && err.error == 2) {
                        finalCallback({error: err });
                        return;
                    }

                    // result now equals 'done'
                    if (vehicleData) {

                        // the data which is not enough (missing style information)
                        // we mark it in the cache, so we dont retreive again
                        if (err && err.error == 1) 
                            vehicleData.status = 'MISSINGDATA';

                        setTimeout(function() {

                            cache.set(vin, JSON.stringify (vehicleData), function(err,reply) {

                                if (err && err.error == 1) 
                                    // cache with missing data is non persistent
                                    cache.expire (vin, 10000000);

                                finalCallback(vehicleData);

                            });

                        }, 200 );
                      
                    } else {
                        finalCallback(null);
                    }
                });

            }


        });

    }

    var defaultFilter = function (item) {

        if (!item)
            return true;

        var 
            // high end luxury cars are exluded
            excludedMakes = ['BENTLEY','PORSCHE','JAGUAR','FERRARI','MASERATI','ASTON MARTIN','LAMBORGHINI','MAYBACH','MCLAREN','ROLLS-ROYCE','RAM' ],
            excludedMaxMilage = 150000 ,
            exludedMinCondition = 2.5 ,
            exludedMinYear = 2004,

            // this retail price is not dependible and comes as 'regional average' for a typical whicle 
            // we just use it for initial filtering
            // the real retail price comes from edmuns API
            // check pricingFilter to filter with actual TMV data
            exludedMaxRetailPrice = 13000,
            
            // Demand Ration
            // generally ; the average demand is 12 
            excludedMimimumDemandRatio = 13 ,
            
            // too little views shows small velocity: 
            // limited buyer population for this vehicle
            // so skip them
            excludedMinView = 40 ,

            // too little inventory MAY show this vehicle is a speciality item, 
            // rather than a quick turn over product
            // so skip the hell of them
            excludedMinInventory = 2.5 , 

            // formatting condition string
            condition = parseFloat( item.Cond[''].substring(2, 8) );

            //console.log (item.Cond );
            

        if ( ! eval(item.Ratio) > 0 ) 
            return { filtered: true, type :'No Demand data found'};
    

        if ( item['Year'] < exludedMinYear ) 
            return { filtered: true, type :'Year'};
        

        for ( var item1 in excludedMakes )
            if (item['Make/Model'].search (excludedMakes[item1]) > -1 )
                return { filtered: true, type :'Make'};

        if ( parseInt(item['Odometer']) > excludedMaxMilage )
                return { filtered: true, type :'Milage'};

        if ( condition  < exludedMinCondition )
                return { filtered: true, type :'Condition'};

        if ( eval(item.Ratio) < excludedMimimumDemandRatio )
                return { filtered: true, type :'Demand Ratio'};


        if ( parseInt(item.Regional_Price) > exludedMaxRetailPrice )
                return { filtered: true, type :'Retail Price'};
            

        if ( item['View']< excludedMinView  )
                return { filtered: true, type :'Velocity'};

        // we expect speciality vehicles should have a demand ratio x3
        if ( item.Inventory < excludedMinInventory && eval(item.Ratio) < excludedMimimumDemandRatio  * 2)
                return { filtered: true, type :'Speciality Vehicle with low demand'};


        return false;

    }

    var pricingFilter = function (item) {

        if (!item)
            return true;

        var 
            // high end luxury cars are exluded
            maxTMV= 50000 ;

        if ( ! item.Price_TMV_Retail > 0 ) 
            return { filtered: true, type :'No Pricing Data found'};

        if ( item.Price_TMV_Retail > maxTMV ) 
            return { filtered: true, type :'High TMV'};
    

        return false;

    }

    var getPrices = function (tmv, year, condition, ratio) {

        // we need previous sales data
        var targetPPVMargin = 20,
            maxBidMargin =10,
            biddingPrice =0,
            maxYear=2016,
            tmv = parseInt(tmv),
            ratio = parseInt ( eval (ratio) );


        function targetPPV(retailPrice) {

            // target margins for profit per vehicle
            // from highess price to lowest
            var PPVMargin = [
                {
                    purchasePrice: 20000 ,
                    PPVMargin: 0.10
                },
                {
                    purchasePrice: 10000 ,
                    PPVMargin: 0.15
                }
                ,
                {
                    purchasePrice: 0 ,
                    PPVMargin: 0.20
                }
            ];

            for (var item in PPVMargin) {

                if (parseInt (retailPrice)  > parseInt ( PPVMargin[item].purchasePrice ) ) {

                    return  parseInt(retailPrice) * PPVMargin[item].PPVMargin ;
                }
            }

        }

        function costs () {

            // transport and detailing are fixed for each car
            var fixedCost=120,

            // repair and recon. cost that depens of the year
            // we add up for $100 for each year
            variableCost = ( maxYear - year  + 1) * 100 ,

            // it depends on the vehicle but lets take an average
            auctionCost = 300;


            return fixedCost  + variableCost + auctionCost;
        }

        function competitivePricingAdjustment (tmv) {

            // this will be initial price discount for our retail prive
            var retaildDiscountRatio=0.03 ;

            return   tmv  - ( tmv * retaildDiscountRatio);   
        }

        function demandAdjustment(biddingPrice, ratio) {

            biddingPrice = parseInt(biddingPrice);

            var 
                // For higher demand we can increate the bid price
                demandReflectionPoint = 15,

                // we can flex the bidding margin for higher demand vehicles
                flexibleMargin =  0.10,
                maxDemandScale = 200,
                ratio = eval (ratio);

            // for high demand vehicles, 
            // we can increse the bidding price within flexibla margins
            var adjustment = biddingPrice * ratio * flexibleMargin / maxDemandScale ;


            if (ratio > demandReflectionPoint) {
                return biddingPrice + adjustment ;
            } else {
                return biddingPrice - adjustment;
            }

           return biddingPrice;

        }


        var retailPrice = competitivePricingAdjustment (tmv);
        var totalVehicleCost = retailPrice - targetPPV (retailPrice);
        var biddingPrice = totalVehicleCost - costs() ;
        var biddingPriceDemandAdjusted = demandAdjustment (biddingPrice, ratio);
        
        var prices =  {
            "TMV": parseInt(tmv),
            "Total_Cost" : parseInt(totalVehicleCost), 
            "Retail" : parseInt(retailPrice) ,
            "Bidding" : parseInt(biddingPrice), 
            "Demand_Adjusted_Bidding" : parseInt(biddingPriceDemandAdjusted)
        };
        
        //log (JSON.stringify (prices));
        return prices;

    }

    //
    // ------------------ Define tasks ------------------
    // 
    grunt.registerTask('build:data:demand', function (runListDate) {

        var demand = buildDemandData(),
            outputFile = 'dev/tools/grunt/data/source/demand/carsforsale/list.csv',
            csv = json2csv({ data: demand.data, fields: demand.fields });
        
        fs.writeFileSync(outputFile, csv);
        
    });

    grunt.registerTask('build:data:bidlist', function (runListDate) {

       ok('Creating your bid lists, bidi bidi bidi ..... ');

        var done = this.async(),
            fields = [
                // from runlist
                'Ln/Run','Year','Make/Model','Cond.','Eng /T','Top','Odometer','Color','VIN', 
                //from demand data
                'Rank', 'View', 'Inventory','Ratio',  'Regional_Price', 'National_Price',
                // from edmunds
                'Price_TMV_Retail', 'Price_Private_Party', 'Price_Trade_In', 'Price_Certified',
                //calculated
                'Total_Cost','Retail','Bidding','Demand_Adjusted_Bidding'],
            outputFileFolder = 'dev/tools/grunt/data/bid-list/',
            outputFile = outputFileFolder + runListDate + '/list.csv';
        
        loadDemanData_array(function (demand) {

            var csvtojson = new Converter({}),
                runList =  getRunListFile(runListDate),
                bidList =[],
                demandFound=0,
                pricingDataLookup=0,
                cachedAPILookup=0,
                filtered =0,
                APILookupError=0,
                demandData = demand;                   

            csvtojson.fromFile(runList,function(err,runList){

                runList = prepareRunlist (runList);

                // for every item in runlist
                // append custom data to runlist data
                var itemCount=0;
                async.whilst(
                    function () { 
                        return itemCount < runList.length - 1;
                    },
                    function (callback_loop) {

                        itemCount++;
                       var searchString = runList[itemCount]['Year'] + ' ' + runList[itemCount]['Make/Model'];

                        var itemDemandData = searchDemand (searchString, demandData);

                        // format strings 
                        runList[itemCount]['Ln/Run'] = new String( runList[itemCount]['Ln/Run'] ) ;

                        // append demand data
                        if (itemDemandData) {
                            runList[itemCount].Rank = itemDemandData.rank;
                            runList[itemCount].View = itemDemandData.view;
                            runList[itemCount].Inventory = itemDemandData.inventory;
                            runList[itemCount].Ratio = itemDemandData.ratio.replace(':','/');
                            runList[itemCount].Regional_Price = itemDemandData.region_price;
                            runList[itemCount].National_Price= itemDemandData.national_price;
                        }

                        // main filter for run-lists
                        // the items will be filtered according to purhasing criteria
                        var filterApplied=null;
                        if ( filterApplied = defaultFilter (runList[itemCount])) {
                                ok (itemCount + ' General Filter applied ('+ filterApplied.type + '), skipping: ' + searchString + ' ' +runList[itemCount]['VIN'] ) ;
                            filtered++;
                            callback_loop();
                            return;
                        }

                        //console.log (runList[item] );
                        
                        if (itemDemandData) {
                            demandFound++;
                        } else {
                            //console.log ('Demand data not found for: ' + searchString );
                        }

                        if (processLogging)
                            log (itemCount + '› ');

                        getVehicleData (
                            searchString,
                            runList[itemCount]['VIN'], 
                            runList[itemCount]['Odometer'], 
                            // wierd condition formatting
                            formatCondition (runList[itemCount].Cond['']) ,
                            runList[itemCount]['Color'], 
                            runList[itemCount]['Top'],
                            function(vehicleData) {

                                if (!vehicleData || vehicleData.error) {
                                    if (processLogging)
                                        error('   › Vehicle data not found for VIN, skiping: ' + runList[itemCount]['VIN'] +' - ' + searchString );
                                    APILookupError++;
                                    callback_loop();
                                    return;
                                }

                                if (vehicleData.status == 'MISSINGDATA') {
                                    if (errorLogging)
                                        error ('   VIN lookup is missing necessary data, skipping : ' + runList[itemCount]['VIN'] +' - ' + searchString );
                                        APILookupError++;
                                        callback_loop();
                                        return;
                                }


                                if (vehicleData && vehicleData.TMVData && vehicleData.TMVData.tmv) {

                                    pricingDataLookup++;

                                    if (vehicleData.TMVData) {

                                        var prices =  getPrices ( 
                                            vehicleData.TMVData.tmv.totalWithOptions.usedTmvRetail , 
                                            runList[itemCount]['Year'], 
                                            runList[itemCount].Condition, 
                                            runList[itemCount].Ratio 
                                        );

                                        runList[itemCount].Price_TMV_Retail = vehicleData.TMVData.tmv.totalWithOptions.usedTmvRetail ;
                                        runList[itemCount].Price_Private_Party = vehicleData.TMVData.tmv.totalWithOptions.usedPrivateParty;
                                        runList[itemCount].Price_Trade_In = vehicleData.TMVData.tmv.totalWithOptions.usedTradeIn;
                                        runList[itemCount].Price_Certified = vehicleData.TMVData.tmv.certifiedUsedPrice;

                                        runList[itemCount].Total_Cost = prices.Total_Cost;
                                        runList[itemCount].Retail = prices.Retail ;
                                        runList[itemCount].Bidding = prices.Bidding ;
                                        runList[itemCount].Demand_Adjusted_Bidding = prices.Demand_Adjusted_Bidding;

                                    }

                                    // main filter for run-lists
                                    // the items will be filtered according to purhasing criteria
                                    var pricingFilterApplied=null;
                                    if ( pricingFilterApplied = pricingFilter (runList[itemCount])) {
                                            ok (itemCount + ' Pricing Filter applied ('+ pricingFilterApplied.type + '), skipping: ' + searchString + runList[itemCount]['VIN'] ) ;
                                        filtered++;
                                        callback_loop();
                                        return;
                                    }else {
                                        // append to bidlist
                                        bidList.push(runList[itemCount]);
                                    }

                                } 
                                
                                //go to next item
                                    callback_loop();

                                }
                        );

                    },
                    function (err) {
                        
                        ok ('------ SUMMARY ---------');
                        ok ('Total scanned Runlist items: ' + runList.length ) ;
                        ok ('  Retrieved demand data for: ' + (demandFound  ) );
                        ok ('  Retrieved pricing data: ' +  pricingDataLookup );
                        ok ('  Skipped items by the filters :' + filtered ); 
                        ok ('');
                        ok ('Total created BidList items: ' + bidList.length ) ;
                        ok ('------------------------');


                        var demand = buildDemandData();
                        var csv = json2csv({ data: _(bidList).toArray()  , fields: fields });

                        // prepare folders
                        //exec ('rmdir -rf' + outputFileFolder + runListDate );
                        //exec ('mkdir '+ outputFileFolder + runListDate ) ;

                        fs.writeFileSync(outputFile, csv);

                        log('Bidlist is created at ' +  outputFileFolder + runListDate + '. Happy bidding!');

                        done();

                    }

                );

            });

        });

    });

};