# A member of this class is a vehicle available for sale

# FieldNames is an ordered list of Fields describing the Vehicle

# Properties is a dictionary from each FieldName to it's value

def __init__(self, FieldNames, FieldValues):

self.Properties = {}

for i in range(len(FieldNames)):

self.Properties.update({FieldNames[i]: FieldValues[i]})

self.FieldNames = FieldNames

def __repr__(self):

Outstring = "[* "

for Property in self.FieldNames:

Outstring = Outstring + Property + ": " + \

str(self.Properties[Property]) + " "

# This is an array of all Vehciles that passed the filter

def __init__(self, FilterArray, SessionConsumer, MyAPR):

HardArray, SoftArray = \

FetchFilteredInventory(FilterArray, SessionConsumer, MyAPR)

FieldNames = GetFieldsNames("Inventory_Hard")

self.HardVehicles = []

for FetchedVehicle in HardArray:

self.HardVehicles.append(OneVehicle(FieldNames, FetchedVehicle))

self.SoftVehicles = []

for FetchedVehicle in SoftArray:

self.SoftVehicles.append(OneVehicle(FieldNames, FetchedVehicle))

def __repr__(self):

Outstring = "Hard Filtered Vehicles:\n"

for HardVehicle in self.HardVehicles:

Outstring = Outstring + str(HardVehicle) + "\n"

Outstring = Outstring + "Soft Filtered Vehicles:\n"

for SoftVehicle in self.SoftVehicles:

Outstring = Outstring + str(SoftVehicle) + "\n"

MyDB = sqlite3.connect(cfg.FnameDB)

cur = MyDB.cursor()

cur.execute("SELECT * FROM " + TableName + " LIMIT 1;")

Results = [description[0] for description in cur.description]

MyDB.commit()

MyDB.close()

# Code to serach the inventory database for matching entries

MyDB = sqlite3.connect(cfg.FnameDB)

cur = MyDB.cursor()

MyDB.create_function("SQL_BestSellingPrice", 2, SQL_BestSellingPrice)

MyDB.create_function("SQL_BestInvoicePrice", 3, SQL_BestInvoicePrice)

MyDB.create_function("SQL_Distance", 4, SQL_Distance)

MyDB.create_function("SQL_SalesTax", 4, SQL_SalesTax)

MyDB.create_function("SQL_Transport", 1, SQL_Transport)

MyDB.create_function("SQL_CashCost", 3, SQL_CashCost)

MyDB.create_function("SQL_FindAPR", 8, SQL_FindAPR)

MyDB.create_function("SQL_FindMonthly", 8, SQL_FindMonthly)

MyDB.create_function("SQL_FindDown", 8, SQL_FindDown)

MyScript1 = """

DROP TABLE IF EXISTS Inventory_Filtered1;

CREATE TABLE Inventory_Filtered1 AS

SELECT I.*,

CashCost AS Down0,

0 AS Monthly0,

DownWant AS Down1,

SQL_FindMonthly(SellingPrice, Invoice, CashCost,

TradeIn, Payoff,

DownWant, Fico, TermWant) AS Monthly1,

DownMax AS Down2,

SQL_FindMonthly(SellingPrice, Invoice, CashCost,

TradeIn, Payoff,

DownMax, Fico, TermWant) AS Monthly2,

SQL_FindDown(SellingPrice, Invoice, CashCost,

TradeIn, Payoff,

MonthlyWant, Fico, TermWant) AS Down3,

MonthlyWant AS Monthly3,

SQL_FindDown(SellingPrice, Invoice, CashCost,

TradeIn, Payoff,

MonthlyMax, Fico, TermWant) AS Down4,

MonthlyMax AS Monthly4,

0 as Down5,

SQL_FindMonthly(SellingPrice, Invoice, CashCost,

TradeIn, Payoff,

'0', Fico, TermWant) AS Monthly5

FROM

(SELECT *,

SQL_Transport(DistanceMiles) AS TransportCost,

SQL_CashCost(BestSellingPrice, DistanceMiles, SalesTax) AS CashCost

FROM

(SELECT *,

SQL_Distance(DealerLatitude, DealerLongitude,

ConsumerLatitude, ConsumerLongitude) AS DistanceMiles,

SQL_SalesTax(BestSellingPrice, TaxRate,

TradeInValue, TradeInTaxCredit) AS SalesTax

FROM

(SELECT VIN, YEAR, MAKE, Model, Trim,

Year_STD, Make_STD, Model_STD, Trim_STD,

Ext_Color_Generic AS Color,

SellingPrice, Invoice,

CAST(ABS(RANDOM() % 10000) AS TEXT) AS RandomNumber,

DealerZip,

Latitude,

Longitude,

Latitude AS DealerLatitude,

Longitude AS DealerLongitude,

SQL_BestSellingPrice(SellingPrice, Invoice) AS BestSellingPrice,

""" + \

SessionConsumer.Latitude.Value + " AS ConsumerLatitude, \n" + \

SessionConsumer.Longitude.Value + " AS ConsumerLongitude, \n" + \

SessionConsumer.TaxRate.Value + " AS TaxRate, \n\'" + \

SessionConsumer.TradeInValue.Value + "\' AS TradeInValue, \n" + \

SessionConsumer.TradeInTaxCredit.Value + " AS TradeInTaxCredit, \n\'" + \

SessionConsumer.Fico.Value + "\' AS Fico, \n" + \

SessionConsumer.UpfrontWant.Value + " AS DownWant, \n" + \

SessionConsumer.UpfrontMax.Value + " AS DownMax, \n" + \

SessionConsumer.MonthlyWant.Value + " AS MonthlyWant, \n" + \

SessionConsumer.MonthlyMax.Value + " AS MonthlyMax, \n\'" + \

SessionConsumer.TermWant.Value + "\' AS TermWant, \n\'" + \

SessionConsumer.TradeInValue.Value + "\' AS TradeIn, \n\'" + \

SessionConsumer.TradeInPayoff.Value + """\' AS Payoff

FROM Inventory_STD))) AS I\n """

HardFilter = MakeFilterSQLTable(cur, FilterArray,

"Year_Filter", "Year_In", "Year_STD", "1Year") + \

MakeFilterSQLTable(cur, FilterArray,

"Make_Filter", "Make_In", "Make_STD", "2Make") + \

MakeFilterSQLTable(cur, FilterArray,

"Model_Filter", "Model_In", "Model_STD", "3Model") + \

MakeFilterSQLTable(cur, FilterArray,

"Trim_Filter", "Trim_In", "Trim_STD", "4Trim") + \

MakeFilterSQLTable(cur, FilterArray,

"Color_Filter", "Color_In", "Color", "5Color") + \

" WHERE BestSellingPrice >= " + \

cfg.LowestValidSellingPrice + "\n"

SoftFilter = " WHERE Down4 <= DownMax and BestSellingPrice >= " + \

cfg.LowestValidSellingPrice + "\n"

HardOrder = " \n ORDER BY CashCost ASC LIMIT 25; \n"

SoftOrder = " \n ORDER BY RandomNumber LIMIT 25; \n "

HardDestination = """ DROP TABLE IF EXISTS Inventory_Hard;

CREATE TABLE Inventory_Hard AS """

SoftDestination = """ DROP TABLE IF EXISTS Inventory_Soft;

CREATE TABLE Inventory_Soft AS """

MyScript2 = """ SELECT *,

'' as APR0,

SQL_FindAPR(SellingPrice, Invoice, CashCost,

TradeIn, Payoff, Down1, Fico, TermWant) AS APR1,

SQL_FindAPR(SellingPrice, Invoice, CashCost,

TradeIn, Payoff, Down2, Fico, TermWant) AS APR2,

SQL_FindAPR(SellingPrice, Invoice, CashCost,

TradeIn, Payoff, Down3, Fico, TermWant) AS APR3,

SQL_FindAPR(SellingPrice, Invoice, CashCost,

TradeIn, Payoff, Down4, Fico, TermWant) AS APR4,

SQL_FindAPR(SellingPrice, Invoice, CashCost,

TradeIn, Payoff, Down5, Fico, TermWant) AS APR5

FROM Inventory_Filtered1 """

HardFilterScript = MyScript1 + HardFilter + HardOrder + \

HardDestination + MyScript2 + HardOrder

SoftFilterScript = MyScript1 + SoftFilter + SoftOrder + \

SoftDestination + MyScript2 + SoftOrder

print "HardFilterScript = "

print HardFilterScript

print "SoftFilterScript = "

print SoftFilterScript

cur.executescript(HardFilterScript)

cur.execute("SELECT * FROM Inventory_Hard LIMIT " +

str(cfg.SelectionsShown))

HardResults = cur.fetchall()

cur.executescript(SoftFilterScript)

cur.execute("SELECT * FROM Inventory_Soft LIMIT " +

str(cfg.SelectionsShown))

SoftResults = cur.fetchall()

MyDB.commit()

MyDB.close()

TableName, FieldName, InventoryField, FilterName):

# With an already opened SQL database (pointed to by cur),

# Create a table with one field, listing all checked filter options

cur.execute("DROP TABLE IF EXISTS " + TableName)

cur.execute("CREATE TABLE " + TableName + "(" + FieldName + " TEXT);")

AnyChecked = False

for AvailableChoice in FilterArray.Filter[FilterName].Choices:

AnyChecked = AnyChecked or \

FilterArray.Filter[FilterName].Choices[AvailableChoice]

if AnyChecked:

for AvailableChoice in FilterArray.Filter[FilterName].Choices:

if FilterArray.Filter[FilterName].Choices[AvailableChoice]:

cur.execute("INSERT INTO " + TableName + "(" + FieldName +

") VALUES (\'" + AvailableChoice + "\');")

Results = "INNER JOIN " + TableName + " ON I." + InventoryField +\

" = " + TableName + "." + FieldName + "\n"

else:

Results = ""

# Find the Geo-distance between two pairs of latitude and longitude coords

# Logic: Great Circle formula on a spherical Earth

if isNull(slat1) or isNull(slon1) or isNull(slat2) or isNull(slon2):

return ""

try:

lat1 = float(slat1)

lon1 = float(slon1)

lat2 = float(slat2)

lon2 = float(slon2)

R = 3956 # Earth Radius in miles

dLat = math.radians(lat2 - lat1) # Convert Degrees 2 Radians

dLon = math.radians(lon2 - lon1)

lat1 = math.radians(lat1)

lat2 = math.radians(lat2)

a = math.sin(dLat/2) * math.sin(dLat/2) + math.sin(dLon/2) * math.sin(dLon/2) * math.cos(lat1) * math.cos(lat2)

c = 2 * math.atan2(math.sqrt(a), math.sqrt(1-a))

Results = R * c

return str(int(round(Results)))

except ValueError:

return ""

except:

print "Unexpected error in SQL_Distance:"

print "slat1 = " + str(slat1)

print "slon1 = " + str(slon1)

print "slat2 = " + str(slat2)

print "slon2 = " + str(slon2)

# Data Cleaning process to ascertain the vehcile selling price

# Logic: If SellingPrice is clean and >0, use it

# Else, if Invoice is clean, use it,

# Else, use "0" (for lack of a better alternative)

try:

Results = float(fixZero(sSellingPrice, fixZero(sInvoice, 0)))

return int(round(Results))

except ValueError:

return 0

except:

print "Unexpected error in SQL_SalesTax:"

print "sSellingPrice = " + str(sSellingPrice)

print "sInvoice = " + str(sInvoice)

# Data Cleaning process to ascertain the vehcile Invoice price

# Logic: If Inmvoice is clean and >0, use it

# Else, if SellingPrice is clean, use it,

# Else, use CashCost (for lack of a better alternative)

try:

Results = float(fixZero(sInvoice, fixZero(sSellingPrice, iCashCost)))

return str(int(round(Results)))

except ValueError:

return ""

except:

print "Unexpected error in SQL_SalesTax:"

print "sSellingPrice = " + str(sSellingPrice)

print "sInvoice = " + str(sInvoice)

print "iCashCost = " + str(iCashCost)

# Find the actual ($ amount) of sales tax on a prospective transaction

# In some states, the sales tax applies the the entire selling price,

# whereas in others, it only applies to the difference between the

# purchase price of the new vehicle and Trade-in value

# The parameter sTradeInCredit is 0 for the former and 1 for the latter

# Logic: SalesTax = TaxRate * (SellingPrice - TradeIn * TradeInCredit)

# (SalesTax is never negative)

try:

Results = max( 0, float0(sTaxRate) * \

(float0(sBestSellingPrice) - \

float0(sTradeIn) * float0(sTradeInCredit) ) )

return str(int(round(Results)))

except ValueError:

return "0"

except:

print "Unexpected error in SQL_SalesTax:"

print "sBestSellingPrice = " + str(sBestSellingPrice)

print "sTaxRate = " + str(sTaxRate)

print "sTradeIn = " + str(sTradeIn)

print "sTradeInCredit = " + str(sTradeInCredit)

# Find the cost of transporting a vehicle between two pairs of coords

# Logic: Apply Transport Cost Model to find $ cost as function of distance

# Transport Cost Model uses two seperate straight-line fits:

# A short distance fit (under 600 miles) and a long distance one

# Global variables supply the intercept and slope for each fit

try:

Distance = float0(sDistance)

if Distance < cfg.ShippingBreakPoint:

Results = cfg.ShippingLowIntercept + cfg.ShippingLowSlope * Distance

else:

Results = cfg.ShippingHighIntercept + cfg.ShippingHighSlope * Distance

return str(int(round(Results)))

except ValueError:

return ""

except:

print "Unexpected error in SQL_Transport:"

print "sDistance = " + str(sDistance)

# Find the Cash Cost of a vehicle

# Logic: CashCost = SellingPrice + Transport + SalesTax

# Transport is computed using SQL_Transport() from coords

# SalesTax is computed using SQL_SalesTax() from price, tradeIn, Rate

try:

TransportCost = float0(fixZero(SQL_Transport(sDistance), cfg.ShippingMissing))

SalesTax = float0(sTax)

SellingPrice = float0(sBestSellingPrice)

Results = SellingPrice + TransportCost + SalesTax

return int(round(Results))

except ValueError:

return 0

except:

print "Unexpected error in SQL_CashCost:"

print "sBestSellingPrice = " + str(sBestSellingPrice)

print "sDistance = " + str(sDistance)

print "sTax = " + str(sTax)

# Find the APR for a given DownCash figure

# Logic: LoanAmount = Price - [ DownCash + TradeIn - TradeInPayoff ]

# LTV = LoanAmount / Invoice

# Use LTV and FICO to find APR in the APR table

global GlobalAPR

try:

Invoice = float(fixZero(sInvoice, fixZero(sSellingPrice, iCashCost)))

if Invoice == 0:

return "0"

TradeInNet = float0(sTradeIn) - float0(sPayoff)

DownPayment = float0(fDownCash) + TradeInNet

LoanAmount = iCashCost - DownPayment

LTV = LoanAmount / Invoice

sFico = fixNull(sFico, str(cfg.LowestFicoAnywhere))

Results = MyAPR.GetAPR(sFico, LTV, sTerm)

return str(Results)

except ValueError:

return ""

except:

print "Unexpected error in SQL_FindAPR:"

print "sSellingPrice = " + str(sSellingPrice)

print "sInvoice = " + str(sInvoice)

print "iCashCost = " + str(iCashCost)

print "sTradeIn = " + str(sTradeIn)

print "sPayoff = " + str(sPayoff)

print "fDownCash = " + str(fDownCash)

print "sFico = " + str(sFico)

print "sTerm = " + str(sTerm)

# Find required MonthlyPayments for a given DownCash figure

# Logic: LoanAmount = Price - [ DownCash + TradeIn - TradeInPayoff ]

# LTV = LoanAmount / Invoice

# Use LTV and FICO to find [Monthly%] in the M72 table

# The M72 tables incorporates the active APR grid

# Monthly% is the Monthly payment as a % of LoanAmount

# MonthlyPayment = [Monthly%] * LoanAmount

try:

Invoice = float(fixZero(sInvoice, fixZero(sSellingPrice, iCashCost)))

if Invoice == 0:

return "0"

TradeInNet = float0(sTradeIn) - float0(sPayoff)

DownPayment = float0(fDownCash) + TradeInNet

LoanAmount = iCashCost - DownPayment

LTV = LoanAmount / Invoice

sFico = fixNull(sFico, str(cfg.LowestFicoAnywhere))

Results = MyAPR.GetMonthly(sFico, LTV, sTerm) * LoanAmount

return int(round(Results))

except ValueError:

return ""

except:

print "Unexpected error in SQL_FindMonthly:"

print "sSellingPrice = " + str(sSellingPrice)

print "sInvoice = " + str(sInvoice)

print "iCashCost = " + str(iCashCost)

print "sTradeIn = " + str(sTradeIn)

print "sPayoff = " + str(sPayoff)

print "fDownCash = " + str(fDownCash)

print "sFico = " + str(sFico)

print "sTerm = " + str(sTerm)

# Find required DownCash for a given MonthlyPayment figure

# This is the reverse of SQL_DealUpfront()

# Note: This one is tricky, since APR computation requires knowing LTV,

# but LTV is driven by DownCash -- which is the solution we seek

# It is assumed that APR is monotone increasing with LTV

#

# Logic: Try each LTV granularity possibility starting with the lowest LTV

# The list length is the number of LTV granulations (=17)

# For each LTV in the list:

# Use FICO and LTV to find [Monthly%] in the M72 Table

# LoanAmount = MonthlyPayment / [%Monthly]

# ConsequentLTV = LoanAmount / Invoice

# If ConsequentLTV > LTV, not a solution; continue to next LTV

# If reached highest LTV, and still not a solution; use highest LTV

# Once a solution is found (or using highest):

# DownCash = Price - LoanAmount - [TradeIn - TradeInPayoff]

try:

Invoice = float(fixZero(sInvoice, fixZero(sSellingPrice, iCashCost)))

if Invoice == 0:

return "0"

TradeInNet = float0(sTradeIn) - float0(sPayoff)

Monthly = float0(fMonthly)

sFico = fixNull(sFico, str(cfg.LowestFicoAnywhere))

NeedSolution = True

TryLTV = MyAPR.LowLTV

while TryLTV <= MyAPR.HighLTV and NeedSolution:

LoanAmount = Monthly / MyAPR.GetMonthly(sFico, TryLTV, sTerm)

NeedSolution = (LoanAmount / Invoice) > TryLTV

TryLTV += MyAPR.StepLTV

Results = iCashCost - LoanAmount - TradeInNet

return int(round(Results))

except ValueError:

return ""

except:

print "Unexpected error in SQL_FindDown:"

print "sSellingPrice = " + str(sSellingPrice)

print "sInvoice = " + str(sInvoice)

print "iCashCost = " + str(iCashCost)

print "sTradeIn = " + str(sTradeIn)

print "sPayoff = " + str(sPayoff)

print "fMonthly = " + str(fMonthly)

print "sFico = " + str(sFico)