def handle(self, *args, **options):
create_session = get_dash_session_maker()
s = create_session()
#by site
all_station=s.query(Station).all()
all_counter=0
for station in all_station:
tmp_sm=get_dash_session_maker(site_name=station.name)
ss=tmp_sm()
site=station.name
latest_time,earliest_time=ss.execute(
'select max(timestamp),min(timestamp) from \
fact_trans where site=:site;',{'site':site}).first()
ss.close()
#没数据
if latest_time is None:
continue
#update
station.earliest_date=earliest_time
station.latest_date=latest_time
try:
s.commit()
except Exception,e:
s.rollback()
print e
def handle(self, *args, **options):
try:
stations=sql_utils.get_all_stations()
for station in stations:
data=[]
name=station[0]
create_session = get_dash_session_maker()
s = create_session()
sql = select([ Trans.pump_id]).\
where(and_(Trans.site==name,Trans.trans_type==0)).group_by(Trans.pump_id)
results = s.execute(sql)
results = results.fetchall()
for result in results:
result=result.pump_id
if result in data:
continue
data.append(result)
#更新油枪数量和所有油枪号
ret = update(Station).where(Station.name==name).\
values(id_guns=json.dumps(data),nb_guns=len(data))
s.execute(ret)
s.commit()
except Exception, e:
print >> sys.stderr, "Exception: ", str(e)
def handle(self, *args, **options):
demo_users=['gilbarco5','gilbarco6','gilbarco7']
#demo_users=['smite']
create_session = get_dash_session_maker()
s = create_session()
#by site
all_station_names=['CN_JL_CC_GUIGU','CN_JL_CC_CT','CN_JL_CC_GUANGGU',
'CN_JL_CC_YT','CN_JL_CC_PD']
for user in demo_users:
user=s.query(User).filter_by(name=user).one()
for station_name in all_station_names:
new_user_station=UserStation(
user_id=user.id,
station=station_name
)
s.add(new_user_station)
try:
s.commit()
except Exception,e:
s.rollback()
print e
def do_job(self, job_data):
try:
args=json.loads(job_data)
#import
site=args['site']
count=0
s = get_dash_session_maker(site)()
results=s.query(Trans).filter_by(site=site).all()
for result in results:
cardnum=result.cardnum
result.payment_type=update_vip_pay(cardnum)
if count%10000==0:
s.commit()
else:
s.add(result)
count+=1
s.commit()
s.close()
except Exception,e:
with open(args['log_file'],'at') as lf:
lf.write('[%s]handle task error:%s\n===finish task===\n'%(
NowTime(),str(e)
))
lf.close()
gearman_logger.error('gearman error: '+str(e))
gearman_logger.error('stack:'+exception_stuck())
def handle(self, **options):
print 'start...'
#get file content
user_source=int(options['user_source'])
file=open(options['file'],'r')
file_content=file.read()
file.close()
#analyze
print 'load file content...'
root=ET.fromstring(file_content)
#init db session
print 'init db session....'
session=get_dash_session_maker()()
session_counter=0
#commit tools func
def submit_data_to_db():
session_counter=0
try:
session.commit()
except Exception,e:
print 'submit db transaction faild, error:',str(e)
session.rollback()
def handle(self, *args, **options):
self.site=options['site'].strip().upper()
self.s = get_dash_session_maker()
with open(options['file']) as fd:
print 'start...'
batch_counter=0
#
for row in fd:
try:
#批量提交
if batch_counter==10000:
print 'finish 10000 row'
self.s.commit()
batch_counter=0
batch_counter+=1
print batch_counter
row=row.strip(' \r\n').decode('gb2312').split(',')
ins=self.insert_record(row)
if ins is None:
continue
self.s.add(ins)
except Exception,e:
print e
self.s.commit()
self.s.close()
def handle(self, *args, **options):
# get args
location = options["location_name"].upper()
plocation = re.compile(r"^[A-Z_]+$")
plocation_desc = re.compile(u"^[\u4e00-\u9fa5a-zA-Z0-9_]{1,}$")
# check
if plocation.match(location) == None:
print "location only in A-Z_"
sys.exit(-1)
file_path = options["file"]
username = options["user"]
sm = get_dash_session_maker()
s = sm()
user = s.query(User).filter_by(name=username).one()
user_id = user.id
s.close()
site = options["site"].upper()
site_desc = get_site_desc_by_name(site)
# check
if plocation.match(site) == None:
print "site only in A-Z_"
sys.exit(-1)
if site_desc == None:
site_desc = site
args = {"need_update_station_info": False}
# save location and station
location_desc = get_location_desc_by_id(location)
if location_desc == None:
location_desc = location
locid = get_location_id(location, location_desc)
station_id, created = get_station_id(site, user_id=user_id, site_desc=site_desc, with_created_status=True)
args["need_update_station_info"] = True
# gearman 参数
args["locid"] = locid
args["station_id"] = station_id
args["site"] = site
args["site_desc"] = site_desc
args["location"] = location
args["location_desc"] = location_desc
args["log_file"] = settings.BASE_DIR + "/file/" + username + "/process.log"
args["userid"] = user_id
args["username"] = username
args["station_id"] = station_id
args["file"] = file_path
# 检查是否导入完成
args["import_flag_string"] = location + "@" + site + "@" + str(datetime.datetime.now().microsecond)
# start
result = call_command("gearman_submit_job", "import_ycshell_excel_data", json.dumps(args), foreground=False)
def handle(self, *args, **options):
session = get_dash_session_maker()()
try :
#获取等级规则
rule = settings.INTEGRAL_OPTION[settings.USE_OPTION]
#更新等级
if settings.USE_OPTION == '0000' :
try :
score_rules = rule.USER_INTEGRAL_LEVEL
#更新用户等级
users = session.query(CustomerAccount).all()
for user in users:
all_score = user.all_score
if all_score == 0 :
user.score = 0
user.score_rank = 0
continue
for score_rule in score_rules :
if all_score <= score_rule['level_rule'][1] and all_score >= score_rule['level_rule'][0] :
user.score_rank = score_rule['level_type']
break
else :
continue
session.commit()
except Exception,e:
session.rollback()
print e
print "更新用户等级失败"
print "更新用户等级成功"
elif settings.USE_OPTION == "0001" :
try :
#获取规则数据
gasoline_ratio = rule.USER_INTEGRAL_LEVEL["rule"][rule.GASOLINE]
diesel_ratio = rule.USER_INTEGRAL_LEVEL["rule"][rule.DIESEL]
#更新用户等级
users = CustomerAccount.objects.all()
for user in users:
if user.cardnum :
try :
customer = session.query(CustomerProfile).filter_by(cardnum = int(user.cardnum)).one()
except Exception,e:
continue
ratio = customer.total_fuel_amount_1 + customer.total_fuel_amount_2/(diesel_ratio/gasoline_ratio)
if ratio >= (gasoline_ratio*2) :
user.score_rank = rule.GOLD_MEMBER
user.save()
else :
user.score_rank = rule.ORDINARY_MEMBER
user.save()
else :
user.score_rank = rule.NON_MEMBER
user.save()
except Exception,e :
print e
print "更新会员等级失败"
def handle(self, *args, **options):
print 'start...'
site=options['site']
try:
price_s = get_dash_session_maker()()
if site == '' :
stations = price_s.query(Station).all()
for station in stations :
print station.name +"..."
try:
create_session_func = get_dash_session_maker(station.name)
session = create_session_func()
except:
pass
site = station.name
results = price_s.query(StationFuelType).filter_by(station=site).all()
for result in results:
price_sql = 'select price from fact_trans where site=\'%s\' and barcode=%d order by timestamp desc limit(1);'%(site,int(result.barcode))
price_results = session.execute(price_sql).fetchall()
if len(price_results) >= 1:
stmt = update(StationFuelType).where(StationFuelType.station==site).\
values(price=price_results[0].price)
price_s.execute(stmt)
price_s.commit()
else :
create_session_func = get_dash_session_maker(site)
session = create_session_func()
results = price_s.query(StationFuelType).filter_by(station=site).all()
for result in results:
price_sql = 'select price from fact_trans where site=\'%s\' and barcode=%d order by timestamp desc limit(1);'%(site,int(result.barcode))
price_results = session.execute(price_sql).fetchall()
if len(price_results) >= 1:
stmt = update(StationFuelType).where(StationFuelType.station==site).\
values(price=price_results[0].price)
price_s.execute(stmt)
price_s.commit()
price_s.close()
session.close()
except Exception,e:
print e
def handle(self, *args, **options):
create_session = get_dash_session_maker()
s = create_session()
for name in Site_loc_dic:
stmt = update(Station).where(Station.name==name).\
values(locid=Site_loc_dic[name])
try:
s.execute(stmt)
s.commit()
except Exception,e:
s.rollback()
print e
def handle(self, *args, **options):
try:
#all_user=User.objects.all()
create_session = get_dash_session_maker()
s = create_session()
sql=select([User.name]).select_from(User.__table__)
rs=s.execute(sql)
all_user=rs.fetchall()
s.close()
for user in all_user:
sql_utils.update_station_fuel_types_from_fact_trans(user.name)
except Exception, e:
print >> sys.stderr, "Exception: ", str(e)
def handle(self, *args, **options):
create_session = get_dash_session_maker()
s = create_session()
# by site
all_station = s.query(Station).all()
all_counter = 0
for station in all_station:
tmp_sm = get_dash_session_maker(site_name=station.name)
ss = tmp_sm()
limit = 100000
min_id = 0
while True:
all_item = ss.query(Trans).filter(Trans.site == station.name, Trans.id > min_id).limit(limit).all()
if len(all_item) == 0:
break
counter = 0
for item in all_item:
if item.id > min_id:
min_id = item.id
counter += 1
all_counter += 1
item.compute_sha1()
# need batch commit?
if counter % 10000 == 0:
try:
ss.commit()
except Exception, e:
ss.rollback()
print e
finally:
print "finish %s" % all_counter
def add_gcustomer__manage_group(): session = get_dash_session_maker()() user_source = 1 name = '北京中石油股份有限公司' group = GCompany( user_source = user_source, name = name, ) session.add(group) try : session.commit() except Exception,e: session.rollback() print e return
def handle(self, *args, **options):
# 建立数据会话
sitename=options['site']
create_session = get_dash_session_maker(site_name=sitename)
session = create_session()
try :
objs=session.query(models.Trans).filter_by(site=sitename).all()
# session.close()
for obj in objs :
if obj.pay > 0 and obj.pay % 50 == 0 :
session = create_session()
session.query(models.Trans).filter(models.Trans.id == obj.id).update({"pump_type":1})
session.commit()
except Exception , e:
session.rollback()
session.close()
print e
def handle(self, *args, **options):
session = get_dash_session_maker()()
goods = session.query(Trans).filter_by(trans_type = 1).all()
store_good_list = []
temp_good_list = []
for good in goods :
if not good.barcode in temp_good_list :
store_good_list.append(dict(
pos_id = good.barcode,
name = good.desc,
price = good.price
))
temp_good_list.append(good.barcode)
#写入到文件
f = open("/home/work/store_items.txt","w")
count = 0
for good in store_good_list :
f.write(str(good['pos_id']) + ":"+str(good['name']))
f.write('\n')
print str(good['pos_id']) + ":"+str(good['name'])
count = count + 1
f.write(str(count))
f.close()
print "total:" + str(count)
#更新到后台商品管理
for good in store_good_list :
try :
objs = session.query(StoreItem).filter_by(pos_id = str(good['pos_id'])).all()
if len(objs) > 0 :
print objs[0].name + "在系统中."
else :
good = StoreItem(
pos_id = good['pos_id'],
name = good['name'],
price = good['price'],
user_source=1,
source_id = 3,
img_sha1 ="9329cec35570efd186bb60df1d905b31eec66463"
)
session.add(good)
except Exception , e:
pass
def do_job(self, job_data): params = json.loads(job_data) comp_id = params['comp_id'] vcard_id = params['vcard_id'] #获取公司的用户群 session = get_dash_session_maker()() group_list = session.query(TargetAudience).filter_by(comp_id=comp_id).all() for group in group_list : try : group_user_list = json.loads(group.user_list) except Exception ,e: continue if not vcard_id in group_user_list : if check_user_associate_group(session,vcard_id,group) : group_user_list.append(vcard_id) group.user_list = json.dumps(group_user_list) try : session.commit() except Exception,e: continue else : continue
def handle(self, *args, **options):
try:
# 建立数据会话
create_session = get_dash_session_maker()
s = create_session()
#客户管理数据
# for row in big_customer_profile:
# obj=models.BigCustomerProfile(**row)
# s.add(obj)
# try:
# s.commit()
# except Exception,e:
# s.rollback()
# for row in customer_relation:
# obj=models.CustomerRelation(**row)
# s.add(obj)
# try:
# s.commit()
# except Exception,e:
# s.rollback()
# for row in customer_profile:
# row['favourite_nonfuel_products']=json.dumps(row['favourite_nonfuel_products'])
# row['fuel_products']=json.dumps(row['fuel_products'])
# row['recommended_nonfuel_products']=json.dumps(row['recommended_nonfuel_products'])
# obj=models.CustomerProfile(**row)
# s.add(obj)
# try:
# s.commit()
# except Exception,e:
# s.rollback()
# #营销活动数据
# for row in promotion:
# obj=models.Promotion(**row)
# s.add(obj)
# try:
# s.commit()
# except Exception,e:
# s.rollback()
# for row in promotion_response:
# row['user_participates']=json.dumps(row['user_participates'])
# obj=models.PromotionEffect(**row)
# s.add(obj)
# try:
# s.commit()
# except Exception,e:
# s.rollback()
#油站画像数据
for row in siteprofile:
row['top_100_customers']=json.dumps(row['top_100_customers'])
row['top_100_goods']=json.dumps(row['top_100_goods'])
row['bottom_100_goods']=json.dumps(row['bottom_100_goods'])
row['percent_dist']=json.dumps(row['percent_dist'])
obj=models.StationProfile(**row)
s.add(obj)
try:
s.commit()
except Exception,e:
s.rollback()
# #广告接入数据
# for row in advertisement:
# obj=models.Advertisement(**row)
# s.add(obj)
# try:
# s.commit()
# except Exception,e:
# s.rollback()
except Exception,e:print >> sys.stderr, "Exception: ", str(e)
def handle(self,*args,**options):
path = os.getcwd()+'/gcustomer/apps/gcustomer/management/commands/'
try:
fp = open(path+'test.txt','r')
content = fp.read()
xmldoc = minidom.parseString(content)
root = xmldoc.documentElement
groups_list = analyze_node(root,"groups")
users_list = analyze_node(root,"users")
fp.close()
group_list = []
user_list = []
favourite_products = []
groups = analyze_node(groups_list[0],'group')
#用户画像数据
customers = analyze_node(users_list[0],'user')
for group in groups:
group_id = group.getAttribute('id')
users = analyze_node(analyze_node(group,"users")[0],"user")
items = analyze_node(analyze_node(group,"items")[0],"item")
for user in users:
user_list.append(user.childNodes[0].data)
for item in items:
favourite_products.append(item.getAttribute('id'))
create_session = get_dash_session_maker()
session = create_session()
customerGroup = CustomerGroup(
id = group_id,
#系统自动创建
user_source=1,
source_id=3,
group_name = group_id,
user_list = json.dumps(user_list),
favourite_products = json.dumps(favourite_products)
)
try:
session.add(customerGroup)
session.commit()
user_list = []
favourite_products = []
except Exception,e:
session.rollback()
fav_items = []
recommend_items = []
grouped = []
for user in customers :
user_id = user.getAttribute('id')
cardnum = user.getAttribute('card')
favItems = analyze_node(analyze_node(user,"fav-items")[0],"item")
recommendItems = analyze_node(analyze_node(user,"recommend-items")[0],"item")
groupeds = analyze_node(analyze_node(user,"groups")[0],"group")
for item in favItems :
fav_items.append(item.getAttribute('id'))
for item in recommendItems :
recommend_items.append(item.getAttribute('id'))
for group in groupeds :
grouped.append(group.childNodes[0].data)
create_session = get_dash_session_maker()
session = create_session()
customer_profile = CustomerProfile(
id = user_id,
user_source = 1,
user_name = '',
cardnum = cardnum,
favourite_nonfuel_products = json.dumps(fav_items),
recommended_nonfuel_products = json.dumps(recommend_items) ,
grouped = json.dumps(grouped)
)
try :
session.add(customer_profile)
session.commit()
except Exception,e:
session.rollback()
"weight": weight,
"period": period,
"site": options["site"],
}
)
else:
assocs.append({"item_from": item_from, "item_to": item_to, "weight": weight, "period": period})
# 检查最后的结果
if len(assocs) == 0:
print "no assocs need to commit"
return
# 更新数据库
if options["site"] == None:
create_session = get_dash_session_maker()
else:
create_session = get_dash_session_maker(options["site"])
s = create_session()
try:
if on_specifield_site:
s.execute(
StationItemAssoc.__table__.delete().where(
and_(StationItemAssoc.period == period, StationItemAssoc.site == options["site"])
)
)
s.execute(StationItemAssoc.__table__.insert(), assocs)
else:
s.execute(ItemAssoc.__table__.delete().where(ItemAssoc.period == period))
s.execute(ItemAssoc.__table__.insert(), assocs)
create_session = get_dash_session_maker()
s = create_session()
s.query(UserStation).update({
UserStation.user_id: int(user.id),
UserStation.station:name
})
s.commit()
s.close()
#try :
# site.save()
except Exception, e:
print e
new_sites.append(site_dic)
create_session = get_dash_session_maker()
s = create_session()
if len(new_sites) > 0:
try:
s.execute(Station.__table__.insert(), new_sites)
s.commit()
except Exception,e:
s.rollback()
print e
s.close()
# 预设地址
locs=[]
locs.append(('ALL',0, u'全部',len(Stations)))
locs.append(('JQ', 1,u'未知', 3))
locs.append(('SQ', 2,u'未知', 3))
def handle(self, *args, **options):
ajax_logger=logging.getLogger('ajax')
print 'start...'
site=options['site']
card_file_path=options['card_dir']
all_file_path=options['all_dir']
count=0
try:
s = get_dash_session_maker(site)()
with open(card_file_path) as fd:
for row in fd:
try:
row=row.strip(' \r\n').decode('gbk').split(',')
timestamp = guess_datetime(row[2])
if timestamp==None:
timestamp = guess_datetime(row[1])
barcode = int(float(row[3]))
if barcode < 400000:
trans_type = TransType.FUEL
else:
trans_type = TransType.NON_FUEL
trans_id = int(row[0])
pump_id = int(float(row[11]))
pay = float(row[5]) if trans_type == TransType.FUEL else float(row[9])
location = 8
import hashlib
sha1=hashlib.sha1()
sha1.update(str(location))
sha1.update(site)
sha1.update(str(timestamp))
sha1.update(str(trans_type))
sha1.update(str(trans_id))
sha1.update(str(pump_id))
sha1.update(str(barcode))
sha1.update(str(pay))
sha1=sha1.hexdigest()
rm = s.query(Trans).filter_by(sha1=sha1,site=site).delete()
s.commit()
if rm>0:
count+=1
print 'success delete card item trans_id: %d' %(count)
except Exception,e:
ajax_logger.error(str(e))
print e
with open(all_file_path) as fd:
for row in fd:
try:
try:
row=row.strip(' \r\n').decode('gb2312').split(',')
except:
row=row.strip(' \r\n').decode('gbk').split(',')
row.insert(1,'0')
timestamp = guess_datetime(row[2])
if timestamp==None:
timestamp = guess_datetime(row[1])
barcode = int(float(row[3]))
if barcode < 400000:
trans_type = TransType.FUEL
else:
trans_type = TransType.NON_FUEL
trans_id = int(row[0])
pump_id = int(float(row[11]))
pay = float(row[5]) if trans_type == TransType.FUEL else float(row[9])
location = 8
import hashlib
sha1=hashlib.sha1()
sha1.update(str(location))
sha1.update(site)
sha1.update(str(timestamp))
sha1.update(str(trans_type))
sha1.update(str(trans_id))
sha1.update(str(pump_id))
sha1.update(str(barcode))
sha1.update(str(pay))
sha1=sha1.hexdigest()
s.query(Trans).filter_by(sha1=sha1,site=site).delete()
s.commit()
if rm>0:
count+=1
print 'success delete card item %d' %(count)
except Exception,e:
ajax_logger.error(str(e))
print e
def handle(self, **options):
period = int(options["period"])
start_hour, end_hour = self.PERIOD_RANGE[period]
on_specifield_site = False
# 建立数据会话
if options["site"] == None:
create_session = get_dash_session_maker()
else:
create_session = get_dash_session_maker(options["site"])
s = create_session()
# 设置待计算变量
# {user_id:{barcode:1}}
dataset = {}
# (barcode1,barcode2)
item_ids = set()
# 以客户为中心的抽样过程
def get_trans_from_sampling(trans):
# 总体样本数量
count_all = len(trans)
if count_all == 0:
return
# 抽样率=抽样目标/总体样本数量
sampling_rate = (10000 + 0.000001) / count_all
for tran in trans:
# vip卡按卡号区分用户
if tran.cardnum == PaymentType.VIP:
user_id = "%s-%s" % (tran.site, str(tran.cardnum))
# 其余以交易号来区分
else:
user_id = "%s-%s" % (tran.site, str(tran.trans_id))
# 如果此客户已经被采样过，则不用再次随机，直接命中
if dataset.has_key(user_id):
pass
# 陌生客户需要随机采样
else:
# 未命中
if should_include_under_random_probablity(sampling_rate) == False:
continue
dataset.setdefault(user_id, {})
dataset[user_id].setdefault(tran.barcode, 0)
dataset[user_id][tran.barcode] += 1
item_ids.add(tran.barcode)
# 开始采样数据
try:
starttime = time.time()
trans = []
if options["site"] == None:
sql = (
select(
[Trans.site, Trans.cardnum, Trans.trans_id, Trans.payment_type, Trans.barcode, DimDateHour.hour]
)
.select_from(Trans.__table__.join(DimDateHour.__table__))
.where(
and_(
Trans.quantity > 0, DimDateHour.hour >= start_hour, DimDateHour.hour < end_hour
) # 购买数量大于0的有效购买
)
)
trans_ret = s.execute(sql)
trans = trans_ret.fetchall()
get_trans_from_sampling(trans)
else:
on_specifield_site = True
# 第一次选取所有的油品
sql = (
select(
[Trans.site, Trans.cardnum, Trans.trans_id, Trans.payment_type, Trans.barcode, DimDateHour.hour]
)
.select_from(Trans.__table__.join(DimDateHour.__table__))
.where(
and_(
Trans.trans_type == TransType.FUEL,
Trans.site == options["site"],
Trans.quantity > 0,
DimDateHour.hour >= start_hour,
DimDateHour.hour < end_hour,
) # 购买数量大于0的有效购买
)
)
trans_ret = s.execute(sql)
trans = trans_ret.fetchall()
get_trans_from_sampling(trans)
# 取得top10
none_fuel_top10 = get_station_none_fuel_top10_by_name(options["site"])
none_fuel_top10 = [x["barcode"] for x in none_fuel_top10]
# 第二次选取top10非油品
sql = (
select(
[Trans.site, Trans.cardnum, Trans.trans_id, Trans.payment_type, Trans.barcode, DimDateHour.hour]
)
.select_from(Trans.__table__.join(DimDateHour.__table__))
.where(
and_(
Trans.trans_type == TransType.NON_FUEL,
Trans.barcode.in_(set(none_fuel_top10)),
Trans.site == options["site"],
Trans.quantity > 0,
DimDateHour.hour >= start_hour,
DimDateHour.hour < end_hour,
) # 购买数量大于0的有效购买
)
)
trans_ret = s.execute(sql)
trans = trans_ret.fetchall()
get_trans_from_sampling(trans)
# 第三次选取非油品非top10交易
sql = (
select(
[Trans.site, Trans.cardnum, Trans.trans_id, Trans.payment_type, Trans.barcode, DimDateHour.hour]
)
.select_from(Trans.__table__.join(DimDateHour.__table__))
.where(
and_(
Trans.trans_type == TransType.NON_FUEL,
~Trans.barcode.in_(set(none_fuel_top10)),
Trans.site == options["site"],
Trans.quantity > 0,
DimDateHour.hour >= start_hour,
DimDateHour.hour < end_hour,
) # 购买数量大于0的有效购买
)
)
trans_ret = s.execute(sql)
trans = trans_ret.fetchall()
get_trans_from_sampling(trans)
print "trans loaded: %s(s)" % (time.time() - starttime)
except Exception, e:
print "trans load except:%s" % str(e)
def handle(self, **options):
site='BJBJBJ'#'CN_JL_CC_PUQING'
#计算月度高峰期和月度全时段平均进站车辆数
compute_station_monthbatch(site,2013,4)
#导数据
f=open('/tmp/pq_gcustomer_trans_data.txt','w')
#建立数据会话
create_session = get_dash_session_maker(site)
s = create_session()
start_time='2014-12-01 00:00:00'
end_time='2014-12-31 23:59:59'
try:
sql = select([
Trans.site,Trans.cardnum,Trans.timestamp,Trans.trans_id,
Trans.trans_type,Trans.barcode,Trans.price,Trans.quantity,Trans.pay,
Trans.desc
]).select_from(Trans.__table__).where(
and_(Trans.cardnum>0,Trans.site==site,Trans.quantity>0,
Trans.timestamp>=start_time,Trans.timestamp<=end_time)
).order_by('trans_id asc')
trans_ret = s.execute(sql)
trans = trans_ret.fetchall()
def writeData(tmp,tmp_data):
if tmp is None or len(tmp_data)==0:
return
buy_list=[]
for data in tmp_data:
buy_list.append('%(desc)s\r%(barcode)s\r%(trans_type)s\r%(price)s\r%(quantity)s\r%(pay)s'%({
'desc':data.desc,
'barcode':data.barcode,
'trans_type':data.trans_type,
'price':data.price,
'quantity':data.quantity,
'pay':data.pay
}))
f.write('%(trans_id)s,%(timestamp)s,%(cardnum)s,{%(buying)s}\n'%({
'trans_id':tmp.trans_id,
'timestamp':tmp.timestamp.strftime('%Y-%m-%d %H:%M:%S'),
'cardnum':tmp.cardnum,
'buying':'\t'.join(buy_list)
}))
tmp=None
tmp_data=[]
for tran in trans:
if tmp is None:
tmp=tran
tmp_data.append(tran)
continue
if tmp.trans_id!=tran.trans_id:
writeData(tmp,tmp_data)
#清空
tmp=None
tmp_data=[]
else:
tmp=tran
tmp_data.append(tran)
except Exception,e:
print 'except:%s'%str(e)
def handle(self, *args, **options):
#get args
location=options['location_name'].upper()
location_desc=options['location_desc']
plocation=re.compile(r'^[A-Z_]+$')
plocation_desc=re.compile(u'^[\u4e00-\u9fa5a-zA-Z0-9_]{1,}$')
#check
if plocation.match(location)==None:
print 'location only in A-Z_'
sys.exit(-1)
if plocation_desc.match(unicode(location_desc))==None:
print 'location desc only in \u4e00-\u9fa5a-zA-Z0-9'
sys.exit(-1)
file_path=options['file']
username=options['user']
sm=get_dash_session_maker()
s=sm()
user=s.query(User).filter_by(name=username).one()
user_id=user.id
s.close()
#start
site=options['site']
site_desc=options['site_desc']
#check
if plocation.match(site)==None:
print 'site only in A-Z_'
if plocation_desc.match(unicode(site_desc))==None:
print 'site desc only in \u4e00-\u9fa5a-zA-Z0-9_'
args = {'need_update_station_info':False}
#save location and station
locid=get_location_id(location,location_desc)
station_id,created=get_station_id(site,user_id=user_id,site_desc=site_desc,
with_created_status=True)
args['need_update_station_info']=True
#gearman 参数
args['locid']=locid
args['station_id']=station_id
args["site"] = site
args['site_desc']=site_desc
args['location']=location
args['location_desc']=location_desc
args['log_file']=settings.BASE_DIR+'/file/'+username+'/process.log'
args['userid']=user_id
args['username']=username
args['file']=file_path
#检查是否导入完成
args['import_flag_string']=location+'@'+site+'@'+str(
datetime.datetime.now().microsecond)
result=call_command('gearman_submit_job', 'import_senmei_data',
json.dumps(args), foreground=False)
def handle(self, **options):
start_time='2010-12-01 00:00:00'
end_time='2015-12-31 23:59:59'
sites=[]
if options.has_key('site') and options['site']!=None :
sites.append(options['site'])
else :
# 获取该用户的所有关联site名称
station_infos=get_all_stations()
for info in station_infos:
sites.append(info[0])
user_source=1
if options.has_key('user_source') and options['user_source']!=None :
user_source=int(options['user_source'])
# 对每一个油站进行分析
for site in sites:
# 建立数据会话
create_session = get_dash_session_maker(site)
s = create_session()
# 对每个station，获取所有的用户列表
users={}
user_total_purchase_amounts={} # 记录每个用户的总金额
user_total_nonfuel_purchase_amounts={} # 记录每个用户的非油品总金额
user_total_fuel_amounts={} # 记录每个用户的加油量
user_promotion_times={} # 记录用户接受促销的次数
try:
# 遍历该站点的所有交易数据，统计每个用户的消费总金额，非油品总金额和油品数量
sql = select([
Trans.site,Trans.cardnum,Trans.timestamp,Trans.trans_id,
Trans.trans_type,Trans.barcode,Trans.price,Trans.quantity,Trans.pay,
Trans.desc,Trans.pump_type
]).select_from(Trans.__table__).where(
and_(Trans.cardnum>0,Trans.site==site,Trans.quantity>0,
Trans.timestamp>=start_time,Trans.timestamp<=end_time)
).order_by('trans_id asc')
trans_ret = s.execute(sql)
transactions = trans_ret.fetchall()
for trans in transactions:
# 更新总金额和消费次数
if users.has_key(trans.cardnum) :
users[trans.cardnum]+=1
user_total_purchase_amounts[trans.cardnum]+=trans.pay
else :
users[trans.cardnum]=1
user_total_purchase_amounts[trans.cardnum]=0
# 如果此次消费金额为０
if trans.pay==0:
if user_promotion_times.has_key(trans.cardnum) :
user_promotion_times[trans.cardnum]+=1
else :
user_promotion_times[trans.cardnum]=1
# 更新非油品总金额和油品数量
if trans.trans_type==TransType.FUEL:
if user_total_nonfuel_purchase_amounts.has_key(trans.cardnum) :
user_total_nonfuel_purchase_amounts[trans.cardnum]+=trans.pay
else :
user_total_nonfuel_purchase_amounts[trans.cardnum]=trans.pay
else :
if user_total_fuel_amounts.has_key(trans.cardnum) :
user_total_fuel_amounts[trans.cardnum]+=trans.quantity
else :
user_total_fuel_amounts[trans.cardnum]=trans.quantity
except Exception, e :
print 'exception:%s'%str(e)
# 对用户根据金额总数排序
user_array=sorted(user_total_purchase_amounts.items(), key=lambda x:x[1], reverse=True)
user_prominence={}
nb_users=len(user_array)
idx=0
# 根据用户总金额来计算用户的排名权重
for user in user_array:
user_prominence[user[0]]=100-(idx*100+0.0001)/nb_users
idx+=1
# 对每一个用户，查询其所有的交易记录，按照先后时间排序
idx=0
for user in users:
# 获取该用户的所有交易记录
sql = select([
Trans.site,Trans.cardnum,Trans.timestamp,Trans.trans_id,
Trans.trans_type,Trans.barcode,Trans.price,Trans.quantity,Trans.pay,
Trans.desc, Trans.pump_type
]).select_from(Trans.__table__).where(
and_(Trans.cardnum==user,Trans.site==site,Trans.quantity>0,
Trans.timestamp>=start_time,Trans.timestamp<=end_time)
).order_by('trans_id asc')
trans_ret = s.execute(sql)
trans_array = trans_ret.fetchall()
#初始化统计分析的结果
# 加油时间倾向 prefer_time
#0 无; 1 早; 2 中; 3 晚
prefer_time_stat={}
prefer_time_stat[1]=0
prefer_time_stat[2]=0
prefer_time_stat[3]=0
prefer_time_stat[4]=0
# 加满率 prefer_pump_type
#0 无;1 加满;2 定额
prefer_pump_type_stat={}
prefer_pump_type_stat[1]=0
prefer_pump_type_stat[2]=0
# 加油额 prefer_fuel_cost
#0 无规律;1 加很多;2 加很少;3 一般
prefer_fuel_cost_stat={}
prefer_fuel_cost_stat[1]=0
prefer_fuel_cost_stat[2]=0
prefer_fuel_cost_stat[3]=0
# 非油品销售量 prefer_nonfuel_cost
#0 无规律;1 买很多;2 买很少;3 一般
prefer_nonfuel_cost_stat={}
prefer_nonfuel_cost_stat[1]=0
prefer_nonfuel_cost_stat[2]=0
prefer_nonfuel_cost_stat[3]=0
#平均加油间隔, 平均间隔的天数，avg_charge_period
user_charge_timestamps=[]
# 对油站的影响程度 efficiency
#0 无影响 1/3;1 一般 1/3-2/3;2 严重 2/3
efficiency_stat={}
efficiency_stat[1]=0
efficiency_stat[2]=0
prev_trans_id=None
non_fuel_amount=0
fuel_amount=0
nb_transactions=0;
# 扫描该用户的所有记录
for trans in trans_array:
# 一次新的交易的开始
if trans.trans_id!=prev_trans_id :
# 用户有一次新的交易
nb_transactions+=1
# 追加加油日期
user_charge_timestamps.append(trans.timestamp)
# 更新对油站的影响程度(加油时间是否在查询得到的该站当天高峰期内)统计
# TODO
# 统计终结上一个交易的数据
if prev_trans_id!=None:
# 油品消费额
if fuel_amount<100:
prefer_fuel_cost_stat[1]+=1
elif fuel_amount<=300:
prefer_fuel_cost_stat[2]+=1
elif fuel_amount>0:
prefer_fuel_cost_stat[3]+=1
# 非油品销售额
if non_fuel_amount<100:
prefer_nonfuel_cost_stat[1]+=1
elif fuel_amount<=300:
prefer_nonfuel_cost_stat[2]+=1
elif non_fuel_amount>0:
prefer_nonfuel_cost_stat[3]+=1
# 更新消费时间统计
if trans.timestamp.hour>=5 and trans.timestamp.hour<11:
prefer_time_stat[1]+=1
elif trans.timestamp.hour>=11 and trans.timestamp.hour<17:
prefer_time_stat[2]+=1
elif trans.timestamp.hour>=17 and trans.timestamp.hour<23:
prefer_time_stat[3]+=1
else :
prefer_time_stat[4]+=1
# 更新加满率统计
if trans.trans_type==TransType.FUEL and trans.pay!=0:
if trans.pump_type==PumpType.FILLOUT:
prefer_pump_type_stat[1]+=1
else :
prefer_pump_type_stat[2]+=1
# 初始化加油额和非油品销售额
if trans.trans_type==TransType.FUEL:
fuel_amount=trans.pay
else :
non_fuel_amount=trans.pay
# 同在一次交易内的其它物品
else :
# 更新加油额和非油品销售额
if trans.trans_type==TransType.FUEL:
fuel_amount+=trans.pay
else :
non_fuel_amount+=trans.pay
prev_trans_id=trans.trans_id
# 根据统计得到消费时间倾向结果
prefer_time=0
for i in prefer_time_stat.keys():
if (prefer_time_stat[i]+0.0001)/nb_transactions>0.5:
prefer_time=i
break
# 根据统计得到加满率结果
prefer_pump_type=0
for i in prefer_pump_type_stat.keys():
if (prefer_pump_type_stat[i]+0.0001)/nb_transactions>0.6:
prefer_pump_type=i
break
# 根据统计得到加油额结果
prefer_fuel_cost=0
for i in prefer_fuel_cost_stat.keys():
if (prefer_fuel_cost_stat[i]+0.0001)/nb_transactions>0.6:
prefer_fuel_cost=i
break
# 根据统计得到非油品购买行为结果
prefer_nonfuel_cost=0
for i in prefer_nonfuel_cost_stat.keys():
if (prefer_nonfuel_cost_stat[i]+0.0001)/nb_transactions>0.6:
prefer_nonfuel_cost=i
break
# 根据统计得到平均加油间隔结果
avg_charge_period=0
if len(user_charge_timestamps)<=1:
avg_charge_period=0
else :
avg_char_period=(user_charge_timestamps[-1]-user_charge_timestamps[0]).total_seconds()/86400/nb_transactions
# 根据统计得到对油站的效率影响结果
efficiency=0
for i in efficiency_stat.keys():
if (efficiency_stat[i]+0.0001)/nb_transactions>0.6:
efficiency=i
break
# 用户最适合的促销模式 best_promotion_mode
# TODO
# 写回该用户的信息到model数据库
if user_total_fuel_amounts.has_key(user) :
total_fuel_amount=user_total_fuel_amounts[user]
else :
total_fuel_amount=0
if user_total_nonfuel_purchase_amounts.has_key(user) :
total_nonfuel_amount=user_total_nonfuel_purchase_amounts[user]
else :
total_nonfuel_amount=0
if user_total_purchase_amounts.has_key(user) :
total_purchase_amount=user_total_purchase_amounts[user]
else :
total_purchase_amount=0
# 查询用户的现有画像结果
profile_sql=select_directly([CustomerProfile.cardnum]).where(and_(CustomerProfile.cardnum==user,CustomerProfile.user_source==user_source)).label('tao-gilbarco')
rs=s.query(profile_sql)
ret=rs.first()
if ret[0]!=None:
stmt = update(CustomerProfile).where(CustomerProfile.cardnum==user).\
values(prefer_time=prefer_time,
prefer_pump_type=prefer_pump_type,
prefer_fuel_cost=prefer_fuel_cost,
prefer_nonfuel_cost=prefer_nonfuel_cost,
avg_charge_period=avg_charge_period,
efficiency=efficiency,
prominence=user_prominence[user],
total_fuel_amount=total_fuel_amount,
total_nonfuel_purchase_amount=total_nonfuel_amount,
total_purchase_amount=total_purchase_amount)
s.execute(stmt)
else:
s.execute(CustomerProfile.__table__.insert(),[
{'site':site,
'cardnum':user,
'user_source':user_source,
'prefer_time':prefer_time,
'prefer_pump_type':prefer_pump_type,
'prefer_fuel_cost':prefer_fuel_cost,
'prefer_nonfuel_cost':prefer_nonfuel_cost,
'avg_charge_period':avg_charge_period,
'efficiency':efficiency,
'prominence':user_prominence[user],
'total_fuel_amount':total_fuel_amount,
'total_nonfuel_purchase_amount':total_nonfuel_amount,
'total_purchase_amount':total_purchase_amount}])
idx+=1
if idx%1000==0:
s.commit()
s.commit()
s.close()
def aggregate(self,
session_factory=None,
measures=None,
drilldown=[],
conditions=[],
details=[],
having=None,
order=None,
limit=None,
session=None):
# choose which session to use by conditions
if session is None:
site_name = None
for cond in conditions:
if hasattr(cond, 'left') == False:
continue
if cond.left.name == 'site':
if hasattr(cond.right, 'value'):
site_name = cond.right.value
break
if session_factory != None:
session = session_factory(site_name)
else:
create_session = get_dash_session_maker(site_name) #got
self.session = create_session()
session = self.session
columns = []
if measures is None:
measures = self.measures.keys()
for c in measures:
agg = self.measures[c]
columns.append(agg.func(agg.col).label(c))
for c in details:
try:
columns.append(func.max(self.details[c]).label(c))
except:
pass
#print >> sys.stderr, 'ERROR: %s is not defined in details' % c
table_to_join = set()
drilldown_columns = []
for c in drilldown:
dim = self.dims[c]
columns.append(dim.label(c))
drilldown_columns.append(dim)
table = dim.class_.__table__
if table != self.fact_table:
table_to_join.add(table)
for condition in conditions:
if hasattr(condition, 'left'):
table = condition.left.table
if table != self.fact_table:
table_to_join.add(table)
join_expr = self.fact_table
for table in table_to_join:
join_expr = join_expr.join(table, onclause=self.joins[table])
sql = select(columns).select_from(join_expr)
if conditions:
sql = sql.where(and_(*conditions))
if drilldown_columns:
sql = sql.group_by(*drilldown_columns)
if having is not None:
sql = sql.having(having)
if order:
sql = sql.order_by(order)
if isinstance(limit, int):
sql = sql.limit(limit)
results = []
sql = sql.apply_labels()
try:
#check cache
cache_sha1 = hashlib.sha1()
sql_params_dict = sql.compile().params
from sqlalchemy.dialects import postgresql
dialect = postgresql.dialect()
raw_sql = unicode(sql.compile(dialect=dialect)) % sql_params_dict
print '\n\n%s\n\n' % raw_sql
cache_sha1.update(raw_sql)
cache_key = cache_sha1.hexdigest()
cache_value = cache.get(cache_key)
if cache_value != None:
try:
results = json.loads(cache_value)
except Exception, e:
results = []
if results == []:
rows = session.execute(sql)
for row in rows:
d = SortedDict()
for c in drilldown:
d[c] = row[c]
for c in measures:
if row[c] == None:
d[c] = 0
else:
d[c] = row[c]
for c in details:
d[c] = row[c]
results.append(d)
rows.close()
#set in cache
try:
from gflux.util import json_encoder
cache.set(cache_key,
json.dumps(results, default=json_encoder),
settings.CUBES_MEMCACHED_TIMEOUT)
except Exception, e:
pass
def handle(self, *args, **options):
#get args
location=options['location_name'].upper()
plocation=re.compile(r'^[A-Z_]+$')
plocation_desc=re.compile(u'^[\u4e00-\u9fa5a-zA-Z0-9_]{1,}$')
#check
if plocation.match(location)==None:
print 'location only in A-Z_'
sys.exit(-1)
file_dir=options['dir']
username=options['user']
sm=get_dash_session_maker()
s=sm()
user=s.query(User).filter_by(name=username).one()
user_id=user.id
s.close()
#start
for parent,dirnames,filenames in os.walk(file_dir):
#get all card file and site name
all_file=os.path.join(parent,'all.txt')
card_file=os.path.join(parent,'card.txt')
#check
if os.path.exists(all_file) and os.path.exists(card_file):
pass
else:
continue
site=options['site'].upper()
site_desc=get_site_desc_by_name(site)
if site_desc==None:
site_desc = site
#check
if plocation.match(site)==None:
print 'site only in A-Z_'
continue
args = {'need_update_station_info':False}
#save location and station
location_desc = get_location_desc_by_id(location)
if location_desc == None:
location_desc = location
locid=get_location_id(location,location_desc)
station_id,created=get_station_id(site,user_id=user_id,site_desc=site_desc,
with_created_status=True)
args['need_update_station_info']=True
#gearman 参数
args['locid']=locid
args['station_id']=station_id
args["site"] = site
args['site_desc']=site_desc
args['location']=location
args['location_desc']=location_desc
args['log_file']=settings.BASE_DIR+'/file/'+username+'/process.log'
args['userid']=user_id
args['username']=username
args['station_id']=station_id
args['all_file']=all_file
args['card_file']=card_file
#检查是否导入完成
args['import_flag_string']=location+'@'+site+'@'+str(
datetime.datetime.now().microsecond)
result=call_command('gearman_submit_job', 'import_yc_data',
json.dumps(args), foreground=False)
def aggregate(self,session_factory=None, measures=None, drilldown=[],
conditions=[], details=[],
having=None, order=None, limit=None, session=None):
# choose which session to use by conditions
if session is None:
site_name=None
for cond in conditions:
if hasattr(cond,'left')==False:
continue
if cond.left.name=='site':
if hasattr(cond.right,'value'):
site_name=cond.right.value
break
if session_factory!=None:
session=session_factory(site_name)
else:
create_session=get_dash_session_maker(site_name)#got
self.session = create_session()
session = self.session
columns = []
if measures is None:
measures = self.measures.keys()
for c in measures:
agg = self.measures[c]
columns.append(agg.func(agg.col).label(c))
for c in details:
try:
columns.append(func.max(self.details[c]).label(c))
except:
pass
#print >> sys.stderr, 'ERROR: %s is not defined in details' % c
table_to_join = set()
drilldown_columns = []
for c in drilldown:
dim = self.dims[c]
columns.append(dim.label(c))
drilldown_columns.append(dim)
table = dim.class_.__table__
if table != self.fact_table:
table_to_join.add(table)
for condition in conditions:
if hasattr(condition,'left'):
table = condition.left.table
if table != self.fact_table:
table_to_join.add(table)
join_expr = self.fact_table
for table in table_to_join:
join_expr = join_expr.join(table, onclause=self.joins[table])
sql = select(columns).select_from(join_expr)
if conditions:
sql = sql.where(and_(*conditions))
if drilldown_columns:
sql = sql.group_by(*drilldown_columns)
if having is not None:
sql = sql.having(having)
if order:
sql = sql.order_by(order)
if isinstance(limit, int):
sql = sql.limit(limit)
results = []
sql = sql.apply_labels()
try:
#check cache
cache_sha1=hashlib.sha1()
sql_params_dict=sql.compile().params
from sqlalchemy.dialects import postgresql
dialect = postgresql.dialect()
raw_sql=unicode(sql.compile(dialect=dialect))%sql_params_dict
print '\n\n%s\n\n'%raw_sql
cache_sha1.update(raw_sql)
cache_key=cache_sha1.hexdigest()
cache_value=cache.get(cache_key)
if cache_value!=None:
try:
results=json.loads(cache_value)
except Exception,e:
results=[]
if results==[]:
rows = session.execute(sql)
for row in rows:
d = SortedDict()
for c in drilldown:
d[c] = row[c]
for c in measures:
if row[c]==None:
d[c] = 0
else:
d[c] = row[c]
for c in details:
d[c] = row[c]
results.append(d)
rows.close()
#set in cache
try:
from gflux.util import json_encoder
cache.set(cache_key,json.dumps(results,default=json_encoder),settings.CUBES_MEMCACHED_TIMEOUT)
except Exception,e:
pass
def handle(self, *args, **options):
self.form=options
#取得location的所有site列表
self.sites=get_all_sites_by_location_id(int(self.form['location']))
type=options['type']
#check type
if type not in all_type_args.keys():
print 'ERROR:type not right'
return
#check args
need_args=all_type_args[type]
for need_arg in need_args:
if options[need_arg] is None:
print 'ERROR:'+type+' need arg '+need_arg
return
s_list=[]
already_shard_ids=[]
for site in self.sites:
shard_id,sm=get_dash_session_maker(site,with_shard_id=True)
if shard_id in already_shard_ids:
continue
already_shard_ids.append(shard_id)
s_list.append(sm())
if type=='pump_daily_avg_report':
conditions = self.build_conditions()
conditions.append(self.cube.d.trans_type == TransType.FUEL)
results=[]
for s in s_list:
results += self.cube.aggregate(measures=['quantity'],
drilldown=['barcode', 'month', 'day'],
conditions=conditions,
session=s)
stats = {}
categories = set()
for result in results:
barcode = result['barcode']
stats.setdefault(barcode, {})
cat = '%02d-%02d' % (result['month'], result['day'])
categories.add(cat)
stat = stats[barcode]
stat[cat] = round(result['quantity'] / self.site_count, 2)
categories = list(categories)
categories.sort()
data = {
"categories": categories,
"dataset": [],
}
for series, stat in stats.items():
opt = {"data": [], "name": get_fuel_type_name(series), "type": "area", "stacking": "normal"}
for cat in data['categories']:
opt["data"].append(stat.get(cat, 0))
data["dataset"].append(opt)
cache_key='%s_%s'%(type,'-'.join(all_type_args[type]))
for arg in all_type_args[type]:
cache_key+='_'+str(options[arg])
cache_value=json.dumps(data)
cache.set(cache_key,cache_value,settings.MEMCACHED_TIMEOUT)
elif type=='gun_pump_avg_report':
conditions = self.build_conditions()
conditions.append(self.cube.d.trans_type == TransType.FUEL)
#统计出油量，多少天数
#group by 每个站，每小时
results=[]
for s in s_list:
results += self.cube.aggregate(measures=['quantity','hours'],
drilldown=['hour', 'site'],
conditions=conditions,
session=s)
stats = {}
counter_dic={}
for result in results:
hour = result['hour']
site = result['site']
cat = '%02d:00 - %02d:00' % (hour, hour + 1)
stats.setdefault(cat, 0)
counter_dic.setdefault(cat,0)
#不同的站
#加上枪效率，出油量是在多少枪，多少天完成的，需要平均下
stats[cat]+=result['quantity'] / 40 / get_nb_guns_of_station(site) / result['hours']
counter_dic[cat]+=1
#平均到每个站
for cat in stats:
stats[cat] = round(stats[cat]/counter_dic[cat], 2)
data = {
"categories": ['%02d:00 - %02d:00' % (i, i + 1) for i in range(0, 24)],
"dataset": [],
}
opt = {"data": [], "name": "油枪平均每小时工作时间", "type": "column"}
for cat in data['categories']:
opt["data"].append(stats.get(cat, 0))
data["dataset"].append(opt)
cache_key='%s_%s'%(type,'-'.join(all_type_args[type]))
for arg in all_type_args[type]:
cache_key+='_'+str(options[arg])
cache_value=json.dumps(data)
cache.set(cache_key,cache_value,settings.MEMCACHED_TIMEOUT)
elif type=='non_fuel_sales_avg_report':
conditions = self.build_conditions()
conditions.append(self.cube.d.trans_type == TransType.NON_FUEL)
conditions.append(self.cube.d.quantity > 0)
results=[]
for s in s_list:
results += self.cube.aggregate(measures=['pay', 'hours'],
drilldown=['hour'],
conditions=conditions,
session=s)
stats = {}
for result in results:
hour = result['hour']
cat = '%02d:00 - %02d:00' % (hour, hour + 1)
stats[cat] = round(result['pay'] / (self.site_count * result['hours']), 2)
data = {
"categories": ['%02d:00 - %02d:00' % (i, i + 1) for i in range(0, 24)],
"dataset": [],
}
opt = {"data": [], "name": "非油品平均每小时销售额", "type": "column"}
for cat in data['categories']:
opt["data"].append(stats.get(cat, 0))
data["dataset"].append(opt)
cache_key='%s_%s'%(type,'-'.join(all_type_args[type]))
for arg in all_type_args[type]:
cache_key+='_'+str(options[arg])
cache_value=json.dumps(data)
cache.set(cache_key,cache_value,settings.MEMCACHED_TIMEOUT)
elif type=='non_fuel_sales_scale_report':
conditions = self.build_conditions()
conditions2 = conditions[:]
conditions2.append(self.cube.d.trans_type == TransType.NON_FUEL)
conditions2.append(self.cube.d.quantity > 0)
results=[]
for s in s_list:
results += self.cube.aggregate(measures=['trans_count', 'hours'],
drilldown=['hour'],
conditions=conditions2,
session=s)
stats2 = {}
for result in results:
hour = result['hour']
cat = '%02d:00 - %02d:00' % (hour, hour + 1)
stats2[cat] = float(result['trans_count']) / result['hours']
conditions.append(self.cube.d.trans_type == TransType.FUEL)
results=[]
for s in s_list:
results += self.cube.aggregate(measures=['trans_count', 'hours'],
drilldown=['hour'],
conditions=conditions,
session=s)
stats = {}
for result in results:
hour = result['hour']
cat = '%02d:00 - %02d:00' % (hour, hour + 1)
stats[cat] = float(result['trans_count']) / result['hours']
data = {
"categories": ['%02d:00 - %02d:00' % (i, i + 1) for i in range(0, 24)],
"dataset": [],
}
for cat in data['categories']:
total = stats.get(cat, 0) + stats2.get(cat, 0)
if total > 0:
stats[cat] = round(stats2.get(cat, 0) * 100 / total, 2)
else:
stats[cat] = 0
opt = {"data": [], "name": "非油品平均每小时交易比例", "type": "column"}
for cat in data['categories']:
opt["data"].append(stats.get(cat, 0))
data["dataset"].append(opt)
cache_key='%s_%s'%(type,'-'.join(all_type_args[type]))
for arg in all_type_args[type]:
cache_key+='_'+str(options[arg])
cache_value=json.dumps(data)
cache.set(cache_key,cache_value,settings.MEMCACHED_TIMEOUT)
elif type=='top10_non_fuel_sales_avg_report':
self.build_conditions()
location = int(options['location'])
conditions = [self.cube.d.quantity > 0,
self.cube.d.trans_type == TransType.NON_FUEL]
if location > 0:
conditions.append(self.cube.d.location == location)
results=[]
for s in s_list:
results += self.cube.aggregate(measures=['pay'],
drilldown=['barcode'],
details=['desc'],
conditions=conditions,
order='pay desc',
limit=10,
session=s)
barcodes = []
for result in results:
barcodes.append(result['barcode'])
conditions = self.build_conditions()
conditions.append(self.cube.d.trans_type == TransType.NON_FUEL)
conditions.append(self.cube.d.barcode.in_(barcodes))
conditions.append(self.cube.d.quantity > 0)
results=[]
for s in s_list:
results += self.cube.aggregate(measures=['pay'],
drilldown=['barcode', 'hour'],
details=['desc'],
conditions=conditions,
session=s)
stats = {}
descs = {}
for result in results:
barcode = result['barcode']
descs[barcode] = result['desc']
stats.setdefault(barcode, {})
stat = stats[barcode]
hour = result['hour']
cat = '%02d:00 - %02d:00' % (hour, hour + 1)
stat[cat] = round(result['pay'] / (373 * 24 * self.site_count), 2)
data = {
"categories": ['%02d:00 - %02d:00' % (i, i + 1) for i in range(0, 24)],
"dataset": [],
}
for series, stat in stats.items():
opt = {"data": [], "name": descs[series], "type": "line"}
for cat in data['categories']:
opt["data"].append(stat.get(cat, 0))
data["dataset"].append(opt)
cache_key='%s_%s'%(type,'-'.join(all_type_args[type]))
for arg in all_type_args[type]:
cache_key+='_'+str(options[arg])
cache_value=json.dumps(data)
cache.set(cache_key,cache_value,settings.MEMCACHED_TIMEOUT)
elif type=='pump_hourly_avg_report':
conditions = self.build_conditions()
conditions.append(self.cube.d.trans_type == TransType.FUEL)
results=[]
for s in s_list:
results += self.cube.aggregate(measures=['quantity','hours'],
drilldown=['barcode', 'hour'],
conditions=conditions,
session=s)
stats = {}
categories = set()
for result in results:
barcode = result['barcode']
stats.setdefault(barcode, {})
hour = result['hour']
cat = '%02d:00 - %02d:00' % (hour, hour + 1)
categories.add(cat)
stat = stats[barcode]
stat[cat] = round(result['quantity'] / (result['hours'] * self.site_count), 2)
data = {
"categories": ['%02d:00 - %02d:00' % (i, i + 1) for i in range(0, 24)],
"dataset": [],
}
for series, stat in stats.items():
opt = {"data": [], "name": get_fuel_type_name(series), "type": "area", "stacking": "normal"}
for cat in data['categories']:
opt["data"].append(stat.get(cat, 0))
data["dataset"].append(opt)
cache_key='%s_%s'%(type,'-'.join(all_type_args[type]))
for arg in all_type_args[type]:
cache_key+='_'+str(options[arg])
cache_value=json.dumps(data)
cache.set(cache_key,cache_value,settings.MEMCACHED_TIMEOUT)
elif type=="pump_monthly_avg_report":
conditions = self.build_conditions()
conditions.append(self.cube.d.trans_type == TransType.FUEL)
results=[]
for s in s_list:
s=get_dash_session_maker(site)()
results += self.cube.aggregate(measures=['quantity'],
drilldown=['barcode', 'month'],
conditions=conditions,
session=s)
stats = {}
for result in results:
barcode = result['barcode']
stats.setdefault(barcode, {})
stat = stats[barcode]
stat[result['month']] = round(result['quantity'] / self.site_count, 2)
data = {
"categories": [i for i in range(1, 13)],
"dataset": [],
}
for series, stat in stats.items():
opt = {"data": [], "name": get_fuel_type_name(series), "type": "area", "stacking": "normal"}
for cat in data['categories']:
opt["data"].append(stat.get(cat, 0))
data["dataset"].append(opt)
cache_key='%s_%s'%(type,'-'.join(all_type_args[type]))
for arg in all_type_args[type]:
cache_key+='_'+str(options[arg])
cache_value=json.dumps(data)
cache.set(cache_key,cache_value,settings.MEMCACHED_TIMEOUT)
elif type=='trans_count_avg_report':
conditions = self.build_conditions()
conditions.append(self.cube.d.trans_type == TransType.FUEL)
results=[]
for s in s_list:
s=get_dash_session_maker(site)()
results += self.cube.aggregate(measures=['trans_count','hours'],
drilldown=['hour'],
conditions=conditions,
session=s)
stats = {}
for result in results:
hour = result['hour']
cat = '%02d:00 - %02d:00' % (hour, hour + 1)
stats[cat] = int(result['trans_count'] / (self.site_count * result['hours']))
data = {
"categories": ['%02d:00 - %02d:00' % (i, i + 1) for i in range(0, 24)],
"dataset": [],
}
opt = {"data": [], "name": "进站车辆", "type": "column"}
for cat in data['categories']:
opt["data"].append(stats.get(cat, 0))
data["dataset"].append(opt)
cache_key='%s_%s'%(type,'-'.join(all_type_args[type]))
for arg in all_type_args[type]:
cache_key+='_'+str(options[arg])
cache_value=json.dumps(data)
cache.set(cache_key,cache_value,settings.MEMCACHED_TIMEOUT)
#close session
for s in s_list:
s.close()
