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 # 如果此次消费金额为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()