def distance(self, zip, dealerid):
sql = "select * from funnel_dealer where id='%s' limit 1" % dealerid
self.cursor.execute(sql)
result = self.cursor.fetchone()
dzip = result[common().getindex('funnel_dealer', 'fzip_id',
self.cursor)]
sql = "select * from funnel_zipcode where id= '%s' limit 1" % dzip
self.cursor.execute(sql)
result1 = self.cursor.fetchone()
dzipcode = result1[common().getindex('funnel_zipcode', 'code',
self.cursor)]
sql = "select * from funnel_zipcode_distance where zip1='%s' and zip2='%s' limit 1" % (
dzipcode, zip)
self.cursor.execute(sql)
if self.cursor.rowcount == 0:
distance = geocode(dzipcode, zip).distance()
dict = {"zip1": dzipcode, "zip2": zip, "val": distance}
common().insert('funnel_zipcode_distance', dict, self.cursor,
self.db)
return distance
else:
self.cursor.execute(sql)
result = self.cursor.fetchone()
return result[common().getindex('funnel_zipcode_distance', 'val',
self.cursor)]
def updateLead(self, customerid, sdata, type=1):
lead = self.getLeadInfo(customerid)
roi = self.getroi(sdata, type)
lead['visit'] = lead['visit'] + 1
if type == 1:
lead['sales'] = lead['sales'] + 1
if lead['last_sales_date'] is None:
lead['last_sales_date'] = roi['lastvisit']
elif lead['last_sales_date'] < roi['lastvisit']:
lead['last_sales_date'] = roi['lastvisit']
lead['grossprofit'] = float(lead['grossprofit']) + float(
roi['grossprofit'])
else:
lead['service'] = lead['service'] + 1
if lead['last_service_date'] is None:
lead['last_service_date'] = roi['lastvisit']
elif lead['last_service_date'] < roi['lastvisit']:
lead['last_service_date'] = roi['lastvisit']
lead['invoiced'] = float(lead['invoiced']) + float(roi['invoiced'])
if lead['lastvisit'] < roi['lastvisit']:
lead['lastvisit'] = roi['lastvisit']
lead['status'] = roi['status']
id = lead['id']
del lead['id']
self.connect_database(2)
common().update('funnel_customer', lead, id, 'id', self.cursor,
self.db)
self.close_database()
return id
def process(self):
if self.data['tradeins'] == 1:
make = self.data['make']
model = self.data['model']
dealer = self.data['dealer']
sql = "select * from funnel_topvehicle where make='%s' and model = '%s' and fdealer_id = '%s' limit 1" % (
make, model, self.data['dealer'])
self.cursor.execute(sql)
if self.cursor.rowcount == 0:
dict = {
"make": make,
"model": model,
"count": 1,
"fdealer_id": dealer,
}
common().insert('funnel_topvehicle', dict, self.cursor,
self.db)
else:
self.cursor.execute(sql)
result = self.cursor.fetchone()
id = result[0]
dict = {
"count":
result[common().getindex('funnel_topvehicle', 'count',
self.cursor)] + 1,
}
common().update('funnel_topvehicle', dict, id, 'id',
self.cursor, self.db)
def insertLead(self, consumerid, sdata, type=1):
self.connect_database()
sql = "select * from consumer where cid='%s' limit 1" % consumerid
self.cursor.execute(sql)
consumer = self.cursor.fetchone()
self.close_database()
tmp = {}
dic = {
"fdealer_id": self.dealerid,
"notificationcount": 0,
"consumerid": consumerid
}
tmp = dict(tmp, **dic)
tmp1 = self.getname(consumer)
tmp = dict(tmp, **tmp1)
tmp2 = self.getAddress(consumerid)
tmp = dict(tmp, **tmp2)
tmp3 = self.getmail(consumerid)
tmp = dict(tmp, **tmp3)
tmp4 = self.getroi(sdata, type)
tmp = dict(tmp, **tmp4)
tmp5 = self.getPhone(consumerid)
tmp = dict(tmp, **tmp5)
self.connect_database(2)
leadid = common().insert('funnel_customer', tmp, self.cursor, self.db)
keyid = self.hashkey(str(leadid))
common().update('funnel_customer', {"keyid": keyid}, leadid, 'id',
self.cursor, self.db)
self.close_database()
return leadid
def warranty_and_lease(self):
sql = "select * from funnel_customer_roi where fcustomer_id='%s'" % self.customerid
self.cursor.execute(sql)
results = self.cursor.fetchall()
iswarrantyexpiration = 0
isleaseexpiration = 0
for n in results:
warrantyexpiration = n[common().getindex('funnel_customer_roi',
'warrantyexpiration',
self.cursor)]
leaseexpiration = n[common().getindex('funnel_customer_roi',
'leaseexpiration',
self.cursor)]
if warrantyexpiration is not None:
dt = self.wdatecheck(warrantyexpiration)
if dt == 1:
iswarrantyexpiration = 1
if leaseexpiration is not None:
dt = self.wdatecheck(leaseexpiration)
if dt == 1:
isleaseexpiration = 1
dict = {
"iswarrantyexpiration": iswarrantyexpiration,
"isleaseexpiration": isleaseexpiration
}
common().update('funnel_customer', dict, self.customerid, 'id',
self.cursor, self.db)
def getPinnumber(self):
sql = "select * from funnel_pinmanage where type='1'"
self.cursor.execute(sql)
result = self.cursor.fetchone()
id = result[0]
pin = int(result[2]) + 1
dict = {"end":pin}
common().update('funnel_pinmanage',dict,id,'id',self.cursor,self.db)
return pin
def isrun(self):
sql = "select * from funnel_cron where id='2'"
self.cursor.execute(sql)
result = self.cursor.fetchone()
if result[2] == 0:
dict = {"c1": 1}
common().update('funnel_cron', dict, 2, 'id', self.cursor, self.db)
return True
else:
return False
def setTozipcode(self, consumerid, type=1):
self.connect_database()
sql = "select * from address where cid = %s limit 1" % consumerid
self.cursor.execute(sql)
if self.cursor.rowcount == 1:
consumeraddress = self.cursor.fetchone()
city = consumeraddress[common().getindex('address', 'city',
self.cursor)]
state = consumeraddress[common().getindex('address', 'state',
self.cursor)]
zip = consumeraddress[common().getindex('address', 'zip',
self.cursor)]
self.close_database()
self.connect_database(2)
sql = "select * from funnel_topzipcode where zip='%s' and city='%s' and state = '%s' and fdealer_id = '%s' limit 1" % (
zip, city, state, self.dealerid)
self.cursor.execute(sql)
if self.cursor.rowcount == 0:
dict = {
"zip": zip,
"city": city,
"state": state,
"count": 1,
"sales": 0,
"service": 0,
"fdealer_id": self.dealerid
}
if type == 1:
dict['sales'] = 1
else:
dict['service'] = 1
common().insert('funnel_topzipcode', dict, self.cursor,
self.db)
else:
result = self.cursor.fetchone()
id = result[0]
dict = {
"count":
result[common().getindex('funnel_topzipcode', 'count',
self.cursor)] + 1,
"sales":
result[common().getindex('funnel_topzipcode', 'sales',
self.cursor)],
"service":
result[common().getindex('funnel_topzipcode', 'service',
self.cursor)],
}
if type == 1:
dict['sales'] = dict['sales'] + 1
else:
dict['service'] = dict['service'] + 1
common().update('funnel_topzipcode', dict, id, 'id',
self.cursor, self.db)
self.close_database()
def getVehicle(self, vid):
sql = "select * from vehicle where vid='%s' limit 1" % vid
self.cursor.execute(sql)
dic = {"year": '', "make": "", "model": ""}
if self.cursor.rowcount == 1:
vehicle = self.cursor.fetchone()
dic['year'] = vehicle[common().getindex('vehicle', 'year',
self.cursor)]
dic['make'] = vehicle[common().getindex('vehicle', 'make',
self.cursor)]
dic['model'] = vehicle[common().getindex('vehicle', 'model',
self.cursor)]
return dic
def checkTrade(self, sales, leadid):
flag = False
self.connect_database()
slid = sales[common().getindex('sale', 'slid', self.cursor)]
sql = "select * from sale_trade where slid = '%s' limit 1" % slid
self.cursor.execute(sql)
if self.cursor.rowcount == 1:
tradedata = self.cursor.fetchone()
dict = {}
dict['tid'] = slid
dict['date'] = tradedata[common().getindex('sale_trade',
'daterange',
self.cursor)]
vid = tradedata[common().getindex('sale_trade', 'vid',
self.cursor)]
sql = "select * from vehicle where vid='%s' limit 1" % vid
self.cursor.execute(sql)
if self.cursor.rowcount == 1:
flag = True
vehicle = self.cursor.fetchone()
dict['year'] = vehicle[common().getindex(
'vehicle', 'year', self.cursor)]
dict['lead_id'] = leadid
self.close_database()
self.connect_database(2)
common().insert('funnel_lead_trade', dict, self.cursor,
self.db)
dict = {"istrade": 1}
common().update('funnel_lead', dict, leadid, 'id', self.cursor,
self.db)
self.close_database()
if flag:
#self.setTopVehicle(vehicle)
t = 0
def updateanalysis(self):
clist = self.campaignlist
roi = self.roidata
for n in clist:
sql = "select * from funnel_campaign where id='%s'" % n
self.cursor.execute(sql)
result = self.cursor.fetchone()
analysisid = result[common().getindex('funnel_campaign','froi_id',self.cursor)]
sql = "select * from funnel_campaign_roi_analysis where id='%s' limit 1" % analysisid
self.cursor.execute(sql)
result = self.cursor.fetchone()
dict = {}
dict["car"] = int(result[common().getindex('funnel_campaign_roi_analysis','car',self.cursor)])
dict["service"] = int(result[common().getindex('funnel_campaign_roi_analysis','service',self.cursor)])
dict["grossprofit"] = (result[common().getindex('funnel_campaign_roi_analysis','grossprofit',self.cursor)])
dict["invoiced"] = (result[common().getindex('funnel_campaign_roi_analysis','invoiced',self.cursor)])
dict["carsold"] = (result[common().getindex('funnel_campaign_roi_analysis','carsold',self.cursor)])
if int(roi["type"]) == 1:
dict["car"] = dict["car"] + 1
dict["grossprofit"] = dict["grossprofit"] + roi["grossprofit"]
dict["carsold"] = dict["carsold"] + roi["carsold"]
else:
dict["invoiced"] = dict["invoiced"] + roi["invoiced"]
dict["service"] = dict["service"] + 1
common().update('funnel_campaign_roi_analysis',dict,analysisid,'id',self.cursor,self.db)
def getcampaignId(self,entrydate,customerid):
sql = "select * from funnel_customer_campaign where fcustomer_id='%s'" % customerid
self.cursor.execute(sql)
results = self.cursor.fetchall()
campaignid = ''
for n in results:
cmpid = n[2]
sql1 = "select * from funnel_campaign where id='%s' limit 1" % cmpid
self.cursor.execute(sql1)
result = self.cursor.fetchone()
startdate = result[common().getindex('funnel_campaign','startdate',self.cursor)]
enddate = result[common().getindex('funnel_campaign','enddate',self.cursor)]
if entrydate >= startdate and entrydate <= enddate :
campaignid = cmpid
return campaignid
def getdelay(self):
customerid = self.customerid
gdata = self.gdata
if gdata['type'] == 1:
gdata['service_delay'] = 0
sql = "select * from funnel_customer_roi where type='1' and fcustomer_id='%s' order by entrydate DESC limit 1 " % self.customerid
self.cursor.execute(sql)
if self.cursor.rowcount == 0:
gdata['sales_delay'] = 0
else:
result = result = self.cursor.fetchone()
rentrydate = result[common().getindex('funnel_customer_roi',
'entrydate',
self.cursor)]
gdata['sales_delay'] = self.calculate_date_diff(
rentrydate, gdata['entrydate'])
else:
sql = "select * from funnel_customer_roi where type='2' and fcustomer_id='%s' order by entrydate DESC limit 1 " % self.customerid
self.cursor.execute(sql)
gdata['sales_delay'] = 0
if self.cursor.rowcount == 0:
gdata['service_delay'] = 0
else:
result = result = self.cursor.fetchone()
rentrydate = result[common().getindex('funnel_customer_roi',
'entrydate',
self.cursor)]
gdata['service_delay'] = self.calculate_date_diff(
rentrydate, gdata['entrydate'])
sql = "select * from funnel_customer_roi where fcustomer_id='%s' order by entrydate DESC limit 1 " % self.customerid
self.cursor.execute(sql)
if self.cursor.rowcount == 0:
gdata['delay'] = 0
else:
result = result = self.cursor.fetchone()
rentrydate = result[common().getindex('funnel_customer_roi',
'entrydate', self.cursor)]
gdata['delay'] = self.calculate_date_diff(rentrydate,
gdata['entrydate'])
self.gdata = gdata
dict = {}
if gdata['service_delay'] >= 180:
dict["islateservice"] = 1
else:
dict["islateservice"] = 0
common().update('funnel_customer', dict, self.customerid, 'id',
self.cursor, self.db)
def process(self):
sql = "delete from funnel_market_analysis_temp"
self.cursor.execute(sql)
sql = "select * from funnel_customer"
self.cursor.execute(sql)
results = self.cursor.fetchall()
dict = {
"zip": "",
"radious": "",
"city": "",
"state": "",
"dealer": "",
"sales": "",
"service": ""
}
for n in results:
dict["zip"] = n[common().getindex('funnel_customer', 'fzip_id',
self.cursor)]
dict["radious"] = self.radious(n[common().getindex(
'funnel_customer', 'distance', self.cursor)])
dict["city"] = n[common().getindex('funnel_customer', 'city',
self.cursor)]
dict["state"] = n[common().getindex('funnel_customer', 'state',
self.cursor)]
dict["dealer"] = n[common().getindex('funnel_customer',
'fdealer_id', self.cursor)]
dict["sales"] = n[common().getindex('funnel_customer', 'sales',
self.cursor)]
dict["service"] = n[common().getindex('funnel_customer', 'service',
self.cursor)]
self.analysis(dict)
def process(self):
entrydate = unicode(self.data['entrydate'])
sp = entrydate.split('-')
year = int(sp[0])
month = int(sp[1])
dealer = self.data['dealer']
sql = "select * from funnel_historical_report where year='%s' and month='%s' and type='%s' and fdealer_id='%s' limit 1" % (year,month,self.type,dealer)
self.cursor.execute(sql)
if self.cursor.rowcount == 0:
dict = {
"year" :year,
"month" :month,
"sales" :self.data['sales'],
"service" :self.data['service'],
"type" :self.type,
"revenue" :self.data['revenue'],
"fdealer_id" : dealer,
}
common().insert('funnel_historical_report',dict,self.cursor,self.db)
else:
self.cursor.execute(sql)
result = self.cursor.fetchone()
id = result[0]
dict = {
"revenue" : float(result[common().getindex('funnel_historical_report','revenue', self.cursor)]) + float(self.data['revenue']),
"sales" : result[common().getindex('funnel_historical_report','sales', self.cursor)] + self.data['sales'],
"service" : result[common().getindex('funnel_historical_report','service', self.cursor)] + self.data['service'],
}
common().update('funnel_historical_report',dict,id,'id',self.cursor,self.db)
def checktradein(self):
if self.raw['tradeins'] == 1:
tradevin = self.raw["tradein_1_vin"]
customer_number = self.raw['number']
dealerid = self.raw['dealer']
trade_entrydate = self.raw['entrydate']
sql = "select * from funnel_raw_sales where entrydate<'%s' and vehiclevin='%s' and customernumber='%s' and dealerid='%s' limit 1" % (
trade_entrydate, tradevin, customer_number, dealerid)
self.cursor.execute(sql)
if self.cursor.rowcount == 1:
gdata = self.gdata
gdata['istradein'] = 1
self.gdata = gdata
dict = {}
dict["istradecycle"] = 1
common().update('funnel_customer', dict, self.customerid, 'id',
self.cursor, self.db)
def getcampaignlist(self):
roi = self.roidata
entrydate = roi['entrydate']
cid = self.cid
sql = "select * from funnel_customer_campaign where fcustomer_id = '%s'" % cid
self.cursor.execute(sql)
results = self.cursor.fetchall()
clist = []
for n in results:
campaignid = n[2]
sql1 = "select * from funnel_campaign where id='%s' limit 1" % campaignid
self.cursor.execute(sql1)
result = self.cursor.fetchone()
startdate = result[common().getindex('funnel_campaign','startdate',self.cursor)]
enddate = result[common().getindex('funnel_campaign','enddate',self.cursor)]
if entrydate >= startdate and entrydate <= enddate:
clist.append(campaignid)
self.campaignlist = clist
def getname(self, consumer):
self.connect_database()
dict1 = {}
dict1['fname'] = self.convertstr(consumer[common().getindex(
'consumer', 'name_first', self.cursor)])
dict1['lname'] = self.convertstr(consumer[common().getindex(
'consumer', 'name_middle',
self.cursor)]) + ' ' + self.convertstr(consumer[common().getindex(
'consumer', 'name_last', self.cursor)])
dict1['birth_date'] = consumer[common().getindex(
'consumer', 'dob', self.cursor)]
if dict1['fname'] is None:
dict1['fname'] = 'Unknown'
if dict1['birth_date'] is None:
dict1['isbirthday'] = 0
else:
dict1['isbirthday'] = 1
self.close_database()
return dict1
def insertServiceRoi(self, roi, customerid):
self.connect_database()
dic = {}
dic['type'] = 2
dic['istradein'] = 0
dic['entrydate'] = roi[common().getindex('service', 'close_date',
self.cursor)]
dic['invoiced'] = roi[common().getindex('service', 'ro_amount',
self.cursor)]
dic['fservice'] = roi[common().getindex('service', 'svid',
self.cursor)]
vid = roi[common().getindex('service', 'vid', self.cursor)]
dic['fcustomer_id'] = customerid
dic['fdealer_id'] = self.dealerid
tmp = self.getVehicle(vid)
dic = dict(dic, **tmp)
self.close_database()
self.connect_database(2)
common().insert('funnel_customer_roi', dic, self.cursor, self.db)
self.close_database()
dic1 = {}
dic1['sales'] = 0
dic1['service'] = 1
dic1['revenue'] = dic['invoiced']
dic1['entrydate'] = dic['entrydate']
return dic1
def setZip(self):
sql = "select * from funnel_zipcode where code='%s' limit 1" % self.code
self.cursor.execute(sql)
if self.cursor.rowcount == 0:
lat = geocode(self.code).getlatlng()
dict = {"code": self.code, "lat": lat[0], "lng": lat[1]}
return common().insert('funnel_zipcode', dict, self.cursor,
self.db)
else:
sql = "select * from funnel_zipcode where code='%s' limit 1" % self.code
self.cursor.execute(sql)
result = self.cursor.fetchone()
return result[0]
def processbysaleservice(self, n, type=1):
self.connect_database()
if type == 1:
cosumerid = n[common().getindex('sale', 'cid', self.cursor)]
else:
cosumerid = n[common().getindex('service', 'cid', self.cursor)]
self.close_database()
if self.isConsumer(cosumerid) == False:
leadid = self.insertLead(cosumerid, n, type)
else:
leadid = self.updateLead(cosumerid, n, type)
if type == 1:
roi = self.insertSalesRoi(n, leadid)
else:
roi = self.insertServiceRoi(n, leadid)
#self.setTozipcode(cosumerid,type)
self.connect_database(2)
roi['dealer'] = self.dealerid
#hreport(self.db,self.cursor,roi,type).process()
self.close_database()
print cosumerid
print leadid
def getTrade(self, sid):
sql = "select * from sale_trade where slid = '%s' limit 1" % sid
self.cursor.execute(sql)
dic = {
"istradein": 0,
"tradedate": None,
"tyear": "",
"tmake": "",
"tmodel": ""
}
if self.cursor.rowcount == 1:
tradedata = self.cursor.fetchone()
dic['tradedate'] = tradedata[common().getindex(
'sale_trade', 'daterange', self.cursor)]
dic['istradein'] = 1
vid = tradedata[common().getindex('sale_trade', 'vid',
self.cursor)]
tmp = self.getVehicle(vid)
dic['tyear'] = tmp['year']
dic['tmake'] = tmp['make']
dic['tmodel'] = tmp['model']
return dic
def setTopVehicle(self, vehicle):
make = vehicle['tmake']
model = vehicle['tmodel']
self.connect_database(2)
sql = "select * from funnel_topvehicle where make = '%s' and model = '%s'" % (
make, model)
self.cursor.execute(sql)
if self.cursor.rowcount == 1:
vehicle = self.cursor.fetchone()
id = vehicle[0]
count = vehicle[3]
dict = {"count": count + 1}
common().update('funnel_topvehicle', dict, id, 'id', self.cursor,
self.db)
else:
dict = {
"make": make,
"model": model,
"count": 1,
"fdealer_id": self.dealerid
}
common().insert('funnel_topvehicle', dict, self.cursor, self.db)
self.close_database()
def getAddress(self, id):
self.connect_database()
sql = "select * from address where cid = '%s' limit 1" % id
self.cursor.execute(sql)
dict2 = {}
dict2['ismarketout'] = 0
if self.cursor.rowcount == 1:
consumeraddress = self.cursor.fetchone()
dict2['address'] = self.convertstr(
consumeraddress[common().getindex('address', 'address1',
self.cursor)])
dict2['city'] = self.convertstr(consumeraddress[common().getindex(
'address', 'city', self.cursor)])
dict2['state'] = self.convertstr(consumeraddress[common().getindex(
'address', 'state', self.cursor)])
zip = consumeraddress[common().getindex('address', 'zip',
self.cursor)]
self.close_database()
self.connect_database(2)
dict2['fzip_id'] = mapzip(zip, self.db, self.cursor).setZip()
dict2['distance'] = geocode(zip, self.dealerzip).distanceWrapper()
self.close_database()
if dict2['distance'] == -1:
dict2['ismarketout'] = 1
return dict2
else:
dict2 = {
"address": "",
"city": "",
"state": "",
"fzip_id": 13,
"distance": -1,
"ismarketout": 0
}
self.close_database()
return dict2
def process(self):
if self.raw['tradeins'] == 1:
csales = self.raw['id']
year = self.raw['tradein_1_year']
make = self.raw['tradein_1_make']
model = self.raw['tradein_1_model']
mileage = self.raw['tradein_1_mileage']
customer_number = self.raw['number']
dealerid = self.raw['dealer']
trade_entrydate = self.raw['entrydate']
sql = "select * from funnel_raw_sales where entrydate<'%s' and vehicleyear='%s' and vehiclemake='%s' and customernumber='%s' and dealerid='%s' limit 1" % (
trade_entrydate, year, make, customer_number, dealerid)
self.cursor.execute(sql)
if self.cursor.rowcount == 1:
result = self.cursor.fetchone()
p_entrydate = result[common().getindex('funnel_raw_sales',
'entrydate',
self.cursor)]
psales = result[0]
dict = {}
dict["year"] = year
dict["make"] = make
dict["model"] = model
dict["mileage"] = mileage
dict["sold_date"] = trade_entrydate
dict["csales"] = csales
dict["psales"] = psales
dict["customerid"] = self.customerid
dict["dealerid"] = dealerid
common().insert('funnel_customer_tradein', dict, self.cursor,
self.db)
dict = {}
dict["istradecycle"] = 1
common().update('funnel_customer', dict, self.customerid, 'id',
self.cursor, self.db)
def getLeadInfo(self, consumerid):
self.connect_database(2)
sql = "select * from funnel_customer where consumerid = '%s' and fdealer_id='%s' limit 1" % (
consumerid, self.dealerid)
self.cursor.execute(sql)
lead = self.cursor.fetchone()
lst = [
'lastvisit', 'last_sales_date', 'last_service_date', 'id', 'visit',
'sales', 'service', 'grossprofit', 'invoiced'
]
dict = {}
for n in lst:
dict[n] = lead[common().getindex('funnel_customer', n,
self.cursor)]
return dict
self.close_database()
def getzipcode(self, zip):
sql = "select * from funnel_zipcode where code= '%s' limit 1" % zip
self.cursor.execute(sql)
if self.cursor.rowcount == 0:
geo = geocode(zip)
latlng = geo.getlatlng()
dict = {}
dict['lat'] = latlng[0]
dict['lng'] = latlng[1]
dict['code'] = zip
return common().insert('funnel_zipcode', dict, self.cursor,
self.db)
else:
self.cursor.execute(sql)
result = self.cursor.fetchone()
return result[0]
def analysis(self, data):
sql = "select * from funnel_market_analysis_temp where fdealer_id='%s' and fzipcode_id='%s' and radious='%s' limit 1" % (
data['dealer'], data['zip'], data['radious'])
self.cursor.execute(sql)
dict = {}
if self.cursor.rowcount == 0:
dict["fzipcode_id"] = data["zip"]
dict["radious"] = data["radious"]
dict["customer"] = 1
dict["city"] = data["city"]
dict["state"] = data["state"]
dict["makeconquest"] = 0
dict["salesmarketshare"] = 0
dict["crossoverconquest"] = 0
dict["fdealer_id"] = data["dealer"]
dict["bns"] = 0
dict["snb"] = 0
dict["bs"] = 0
if data["service"] == 0:
dict["bns"] = 1
if data["sales"] == 0:
dict["snb"] = 1
if data["sales"] >= 1 and data["service"] >= 1:
dict["bs"] = 1
common().insert('funnel_market_analysis_temp', dict, self.cursor,
self.db)
else:
self.cursor.execute(sql)
result = self.cursor.fetchone()
id = result[0]
dict = {}
dict["customer"] = result[common().getindex(
'funnel_market_analysis_temp', 'customer', self.cursor)] + 1
if data["service"] == 0:
dict["bns"] = result[common().getindex(
'funnel_market_analysis_temp', 'bns', self.cursor)] + 1
if data["sales"] == 0:
dict["snb"] = result[common().getindex(
'funnel_market_analysis_temp', 'snb', self.cursor)] + 1
if data["sales"] >= 1 and data["service"] >= 1:
dict["bs"] = result[common().getindex(
'funnel_market_analysis_temp', 'bs', self.cursor)] + 1
common().update('funnel_market_analysis_temp', dict, id, 'id',
self.cursor, self.db)
def insertnew(self):
data = self.raw
number = self.raw['number']
dealer = self.raw['dealer']
dict = {
"barcode":self.getPinnumber(),"number":data['number'],"fname":data['fname'],'lname':data['lname'],
"address":data['address'],"city":data['city'],'state':data['state'],
"fzip_id":data['zipid'],"distance":data['distance'],'status':0,'birth_date':data['birthdate'],
"lastvisit":'0000-00-00',"lastnotification":'0000-00-00','notificationcount':0,
"visit":'0',"sales":'0','service':'0','grossprofit':0,'invoiced':0,'last_sales_date':'0000-00-00',
'last_service_date':'0000-00-00',"carsold":'0','fdealer_id':dealer,'flag1':0,
"homephone":data['homephone'],"workphone":data['workphone'],"cellphone":data['cellphone'],
"email":data['email'],"iswarrantyexpiration":0,"isemail":0,"isleaseexpiration":0,
"isbirthday":0,"isequityposition":0,"istradecycle":0,"islateservice":0,"fmedia_id":self.getmedia(),
"issms":0
}
return common().insert('funnel_customer', dict, self.cursor, self.db)
def customer_roi(self,cid):
rowid = self.raw['id']
type = self.type
if type == 1:
grossprofit = self.raw['frontgross'] + self.raw['backgross']
invoiced = 0
carsold = self.raw['cashprice']
sql = "select * from funnel_customer_roi where fcustomer_id='%s' and fsales_id='%s' limit 1" % (cid,rowid)
if int(self.raw['extendedwarrantyterm']) > 0:
warrantyexpiration = self.addmonth(self.raw['entrydate'],int(self.raw['extendedwarrantyterm']))
else:
warrantyexpiration = '0000-00-00'
if int(self.raw['leaseterm']) > 0:
leaseexpiration = self.addmonth(self.raw['entrydate'],int(self.raw['leaseterm']))
else:
leaseexpiration = '0000-00-00'
else :
grossprofit = 0
invoiced = self.raw['roamount']
carsold = 0
sql = "select * from funnel_customer_roi where fcustomer_id='%s' and fservice_id='%s' limit 1" % (cid,rowid)
warrantyexpiration = '0000-00-00'
leaseexpiration = '0000-00-00'
self.cursor.execute(sql)
list = ['','fsales_id','fservice_id']
if self.cursor.rowcount == 0:
dict = {
"type":self.type,
"warrantyexpiration":warrantyexpiration,
"leaseexpiration":leaseexpiration,
"entrydate":self.raw['entrydate'],
"grossprofit":grossprofit,
"invoiced":invoiced,
"carsold":carsold,
list[self.type]:rowid,
'fcustomer_id':cid
}
roi_id = common().insert('funnel_customer_roi',dict,self.cursor,self.db)
campaign_roi_match(self.db,self.cursor,roi_id,cid).process()
return True
else:
return False
sys.exit(1)
config = config(config_file)
config.parse_and_validate()
if config.read_error == 1:
logger.toboth("Unable to open config file for reading")
sys.exit(1)
if config.config_status == CONFIG_INVALID:
logger.toboth("Config file is having some lines in invalid format, \
please check")
sys.exit(1)
if config.valid_lines == 0:
logger.toboth("No valid lines in config file")
sys.exit(1)
common = common(logger, host, port, config, ca=ca_file, cipher=cipher_value)
ssl_config_obj_list = copy.deepcopy(config.config_obj_list)
sLib = LibTLS(debugFlag, config_obj_list = ssl_config_obj_list, comm = common)
populate_random_numbers(common, sLib)
logger.toboth("starting TLS handshake")
sLib.TCPConnect()
sLib.log("Creating ClientHello")
sLib.CreateClientHello()
sLib.log("Length of ClientHello:%s\n" % \
str(len(sLib.sslStruct['cHello'])))
sLib.HexStrDisplay("ClientHello Message",
Str2HexStr(sLib.sslStruct['cHello']))
sLib.log("Sending packet")
#!/usr.bin/python
#filename=gen_master_input_list.py
import os
import numpy
from common import *
comb_dir_fullname=[]
dirname=""
parameters_filename=""
b=common()
b.tworound()
#for key in b.comb_dir_localname:
# dirname=os.path.join(b.paramFiles_path,key)
# comb_dir_fullname.append(dirname)
# b.creatdir(dirname)
#fp=open("/home/jennyshao/Geno/src/abc.txt")
#os.mknod("/home/jennyshao/Geno/src/aaa/abc111.txt")
for i in NaNs_freq:
for j in prop_MAF:
comb_dir_localname = "NaN_%s_MAF_%s" % (str(i),str(j))
dirname=os.path.join(b.paramFiles_path,comb_dir_localname)
print "this is dirname: "+dirname
prepGmatrix_localname="G_G_NaNs_%s_prop.MAF_%s.rda" % (str(i),str(j))
for k in reps:
parameters_filename="parameters_%s_%s_%03d.R" % (str(i),str(j),k)
parameters_filename1=dirname+"/"+parameters_filename
sys.exit(1)
config = config(config_file)
config.parse_and_validate()
if config.read_error == 1:
logger.toboth("Unable to open config file for reading")
sys.exit(1)
if config.config_status == CONFIG_INVALID:
logger.toboth("Config file is having some lines in invalid format, \
please check")
sys.exit(1)
if config.valid_lines == 0:
logger.toboth("No valid lines in config file")
sys.exit(1)
common = common(logger, host, port, config)
ssl_config_obj_list = copy.deepcopy(config.config_obj_list)
sLib = LibSSL(debugFlag = 0, config_obj_list = ssl_config_obj_list, comm = common)
populate_random_numbers(common, sLib)
for cipher in range(0, 157):
sLib = LibSSL(debugFlag = 0, config_obj_list = ssl_config_obj_list, comm = common)
sLib.TCPConnect()
sLib.CreateClientHello(cipher = cipher)
sLib.SendCTPacket()
sLib.ReadServerHello()
if sLib.opn == 1:
logger.tofile("Server does not accept %s cipher" \
%(cipherSuiteList[cipher]))
else:
logger.toboth("Server accepts %s cipher" % \
#!/usr/bin/python
#filename=run.py
import os
from sjcommon import *
A=sjcommon()
from common import *
A=common()
filerpst="http://hcc-server.unl.edu/BENSON/rpstblastn"
A.download(filerpst)
os.popen("chmod 755 rpstblastn")
# run main program
Input_list=sys.argv[1]
OutBase=sys.argv[2]
cddIndex=sys.argv[3]
print "this is cddIndenx:"+ cddIndex
os.popen("mkdir "+OutBase)
filecddIndex="http://hcc-server.unl.edu/BENSON/CDD%s.tar"%(cddIndex)
downloadfile(filecddIndex)
os.popen("tar -xvf CDD%s.tar"%(cddIndex));
# os.remove("CDD%s.tar"%(cddIndex))
#********** RUN MAIN***********************
for line in fileinput.input(sys.argv[1]):
Lid.append(line)
if len(Lid) == 2:
w=open("Temp","w")
L="".join(Lid)
#!/usr/bin/python #filename=make_rundirs.py import numpy import shutil import os from common import * a=common() target_directory=rootpath+"/run_home" a.creatdir(target_directory) a.threeround() for key in a.comb_dir_localname: newkey=target_directory+"/"+key+"/"+"condor_out" a.creatdir(newkey)
