def SQL_FindMonthly(sSellingPrice, sInvoice, iCashCost, sTradeIn, sPayoff, fDownCash, sFico, 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)
raise
def SQL_FindAPR(sSellingPrice, sInvoice, iCashCost, sTradeIn, sPayoff, fDownCash, sFico, sTerm):
# 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)
raise
def __init__(self):
MyDB = sqlite3.connect(cfg.FnameDB)
cur = MyDB.cursor()
MyScript = """SELECT ZIP, Income, state, ST2Offset, FICO3,
Latitude, Longitude, SalesTax, TradeInTaxCredit
FROM ZipLatLon;"""
cur.execute(MyScript)
Results = cur.fetchall()
MyDB.commit()
MyDB.close()
self.Income = {}
self.ST2 = {}
self.ST2offset = {}
self.FICO3 = {}
self.Latitude = {}
self.Longitude = {}
self.TaxRate = {}
self.TradeInTaxCredit = {}
for row in Results:
self.Income.update({row[0]: float0(row[1])/12})
self.ST2.update({row[0]: row[2]})
self.ST2offset.update({row[0]: float0(row[3])})
self.FICO3.update({row[0]: row[4]})
self.Latitude.update({row[0]: row[5]})
self.Longitude.update({row[0]: row[6]})
self.TaxRate.update({row[0]: row[7]})
self.TradeInTaxCredit.update({row[0]: row[8]})
def SQL_FindDown(sSellingPrice, sInvoice, iCashCost, sTradeIn, sPayoff, fMonthly, sFico, 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)
raise
#MySelections = VehicleArray(SessionFilterArray, SessionConsumer, MyAPR)
#MySelections0 = MySelections
def SQL_CashCost(sBestSellingPrice, sDistance, sTax):
# 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)
raise
def SQL_SalesTax(sBestSellingPrice, sTaxRate, sTradeIn, sTradeInCredit):
# 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)
raise
def SQL_Transport(sDistance):
# 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)
raise
