def event_shoe_price_init(cls): to_date_range = UDatetime.date_range(cls.start, cls.end) product_ids = ['3101'] center_objs = Centers.objects.filter(status='open').exclude( bowling_event_tier=None) center_records = pd.DataFrame.from_records( center_objs.values('center_id', 'bowling_event_tier')) centers = center_records['center_id'].tolist() to_records_list = [{ 'center_id': center_id, 'product_id': product_id, 'date': date, 'DOW': DOW_choice[date.weekday()][0] } for center_id in centers for product_id in product_ids for date in to_date_range] to_records = pd.DataFrame(to_records_list) # get last price if not product_ids: return last_price_records = DataDAO.LastPrice.get_last_price(['115'], centers, cls.start.date()) if not last_price_records.empty: last_price_records = last_price_records[[ 'center_id', 'product_id', 'price' ]] last_price_records['product_id'] = '3101' to_records = to_records.join(last_price_records.set_index( ['center_id', 'product_id']), on=['center_id', 'product_id'], how='left') to_records.dropna(subset=['price'], inplace=True) # Load into database for index, row in to_records.iterrows(): center_obj = Centers.objects.get(center_id=row['center_id']) product_obj = Product.objects.get(product_id=row['product_id']) ProductPrice.objects \ .update_or_create( date=row['date'], DOW=row['DOW'], center_id=center_obj, product_id=product_obj, defaults={ 'price': round(row['price'], 2), 'perpetual': True, 'product_name': product_obj.product_name, } ) return
def create_columns_by_date_range(cls, start, end, level=1): date_range = UDatetime.date_range(start, end) columns = [] for date in date_range: if level == 1: dow = DOW_choice[date.weekday()][0] date_str = date.strftime('%Y%m%d') pattern = \ [ { 'field': date_str, 'title': '{dow}<br/>{date}'.format(dow=dow.capitalize(), date=date.strftime('%Y-%m-%d')), 'colspan': 3, 'align': 'center' } ] elif level == 2: date_str = date.strftime('%Y%m%d') pattern = \ [ { 'field': '{date}-nonprime'.format(date=date_str), 'title': 'Non-Prime', 'sortable': True, 'editable': True, 'align': 'center' }, { 'field': '{date}-prime'.format(date=date_str), 'title': 'Prime', 'sortable': True, 'editable': True, 'align': 'center' }, { 'field': '{date}-premium'.format(date=date_str), 'title': 'Premium', 'sortable': True, 'editable': True, 'align': 'center' } ] else: pattern = [] columns += pattern return columns
def product_roll_over(cls, rolling_week=2, current_user=None): start = UDatetime.now_local().date() end = start + rolling_week * timedelta(days=7) - timedelta(days=1) current_user = User.objects.get(username='******') center_ids = Centers.objects.filter(status='open').values_list( 'center_id', flat=True) # product_ids = Product.objects \ # .filter(status='active', report_type__in=cls.rollover_report_type).values_list('product_id', flat=True) # product_ids = Product.objects \ # .filter(status='active', report_type='Retail Bowling').values_list('product_id', flat=True) # product_ids = Product.objects \ # .filter(status='active', report_type='Retail Shoe').values_list('product_id', flat=True) product_ids = Product.objects \ .filter(status='active', report_type__in=['Retail Promos', 'Event Bowling', 'Event Shoe', 'Event Packages']).values_list('product_id', flat=True) # model = RetailBowlingPrice # model = RetailShoePrice model = ProductPrice product_ids = [ product_id for product_id in product_ids if product_id not in cls.product_rollover_opt_out ] # get last price last_price_records = DataDAO.LastPrice.get_last_price( product_ids, center_ids, start, perpetual_only=True) if last_price_records.empty: return last_price_records = last_price_records[[ 'center_id', 'product_id', 'price' ]] # init to_records to_date_range = UDatetime.date_range(start, end) if not to_date_range: return to_records_list = [{ 'center_id': center_id, 'product_id': product_id, 'date': date, 'DOW': DOW_choice[date.weekday()][0] } for center_id in center_ids for product_id in product_ids for date in to_date_range] to_records = pd.DataFrame(to_records_list) # get product schedule productschedule_obj = ProductSchedule.objects.filter( product_id__product_id__in=product_ids, status='active') productschedule_records = pd.DataFrame.from_records( productschedule_obj.values('product_id__product_id', 'DOW')) productschedule_records.rename( {'product_id__product_id': 'product_id'}, axis=1, inplace=True) productschedule_records.drop_duplicates(['product_id', 'DOW'], inplace=True) productschedule_records['available'] = True to_records = to_records.join(productschedule_records.set_index( ['product_id', 'DOW']), on=['product_id', 'DOW'], how='left') # Get product opt in / out productopt_records = ProductOptGet.get_productopt( product_ids, start, end, center_ids) to_records['date'] = to_records['date'].apply(lambda x: str(x)) productopt_records['date'] = productopt_records['date'].apply( lambda x: str(x)) to_records = to_records.join(productopt_records.set_index( ['center_id', 'product_id', 'date']), on=['center_id', 'product_id', 'date'], how='left') to_records = to_records[(to_records['available']) & (to_records['opt'] == 'In')] # get rollover price to_records = to_records.join(last_price_records.set_index( ['center_id', 'product_id']), on=['center_id', 'product_id'], how='left') # remove overlap from_price_obj = model.objects \ .filter( center_id__in=center_ids, product_id__in=product_ids, date__range=[start, end] ) \ .exclude(action_user=current_user) from_product_record = pd.DataFrame.from_records( from_price_obj.values('center_id', 'product_id', 'date', 'price')) if not from_product_record.empty: from_product_record.rename({'price': 'current_price'}, inplace=True, axis=1) to_records['date'] = to_records['date'].apply(lambda x: str(x)) from_product_record['date'] = from_product_record['date'].apply( lambda x: str(x)) result_record = to_records.join( from_product_record.set_index( ['center_id', 'product_id', 'date']), on=['center_id', 'product_id', 'date'], how='left', ) result_record = result_record[ result_record['current_price'].isnull()] else: result_record = to_records.copy() # remove NA price result_record = result_record[~result_record['price'].isna()] # remove rollover model.objects \ .filter( center_id__in=center_ids, product_id__in=product_ids, date__range=[start, end], action_user=current_user) \ .delete() # add rollover for index, row in result_record.iterrows(): center_obj = Centers.objects.get(center_id=row['center_id']) product_obj = Product.objects.get(product_id=row['product_id']) model.objects.create(date=row['date'], DOW=row['DOW'], center_id=center_obj, product_id=product_obj, product_name=product_obj.product_name, price=round(row['price'], 2), action_user=current_user)
def event_bowling_price_init(cls): to_date_range = UDatetime.date_range(cls.start, cls.end) product_ids = ['3001', '3002', '3003', '3004', '3005', '3006', '3007'] # remove previous records # ProductPrice.objects.filter(product_id__in=product_ids).delete() center_objs = Centers.objects.filter(status='open').exclude( bowling_event_tier=None) center_records = pd.DataFrame.from_records( center_objs.values('center_id', 'bowling_event_tier')) centers = center_records['center_id'].tolist() center_records['bowling_event_tier'] = center_records[ 'bowling_event_tier'].apply(lambda x: str(int(float(x))) if x else x) center_records.rename({'bowling_event_tier': 'tier'}, axis=1, inplace=True) tier_objs = PricingTierTable.objects.filter( product_id__report_type='Event Bowling') tier_records = pd.DataFrame.from_records( tier_objs.values('product_id', 'tier', 'price')) price_records = center_records.join(tier_records.set_index(['tier']), on=['tier'], how='left') to_records_list = [{ 'center_id': center_id, 'product_id': product_id, 'date': date, 'DOW': DOW_choice[date.weekday()][0] } for center_id in centers for product_id in product_ids for date in to_date_range] to_records = pd.DataFrame(to_records_list) # get product schedule productschedule_obj = ProductSchedule.objects.filter( product_id__product_id__in=product_ids, status='active') productschedule_records = pd.DataFrame.from_records( productschedule_obj.values('product_id__product_id', 'DOW')) productschedule_records.rename( {'product_id__product_id': 'product_id'}, axis=1, inplace=True) productschedule_records.drop_duplicates(['product_id', 'DOW'], inplace=True) productschedule_records['available'] = True to_records = to_records.join(productschedule_records.set_index( ['product_id', 'DOW']), on=['product_id', 'DOW'], how='left') to_records = to_records.where((pd.notna(to_records)), False) to_records = to_records[to_records['available']] if to_records.empty: return to_records = to_records.join(price_records.set_index( ['center_id', 'product_id']), on=['center_id', 'product_id'], how='left') # Load into database for index, row in to_records.iterrows(): center_obj = Centers.objects.get(center_id=row['center_id']) product_obj = Product.objects.get(product_id=row['product_id']) ProductPrice.objects \ .update_or_create( date=row['date'], DOW=row['DOW'], center_id=center_obj, product_id=product_obj, defaults={ 'price': round(row['price'], 2), 'perpetual': True, 'product_name': product_obj.product_name, } )
def event_packages_price_init(cls): to_date_range = UDatetime.date_range(cls.start, cls.end) product_ids = [ '3201', '3202', '3203', '3204', '3205', '3206', '3207', '3208', '3209' ] center_objs = Centers.objects.filter(status='open').exclude( bowling_event_tier=None) center_records = pd.DataFrame.from_records( center_objs.values('center_id', 'bowling_event_tier')) centers = center_records['center_id'].tolist() to_records_list = [{ 'center_id': center_id, 'product_id': product_id, 'date': date, 'DOW': DOW_choice[date.weekday()][0] } for center_id in centers for product_id in product_ids for date in to_date_range] to_records = pd.DataFrame(to_records_list) # get last price if not product_ids: return file_path = os.path.join(BASE_DIR, 'RM/Centers/sample/config/events.xlsx') price_records = pd.read_excel(file_path, 'packages') price_records = pd.melt(price_records, id_vars=['center_id'], var_name='product_id', value_name='price') price_records['product_id'] = price_records['product_id'].astype(str) price_records['center_id'] = price_records['center_id'].astype(str) to_records = to_records.join(price_records.set_index( ['center_id', 'product_id']), on=['center_id', 'product_id'], how='left') to_records.dropna(subset=['price'], inplace=True) # Load into database for index, row in to_records.iterrows(): center_obj = Centers.objects.get(center_id=row['center_id']) product_obj = Product.objects.get(product_id=row['product_id']) ProductPrice.objects \ .update_or_create( date=row['date'], DOW=row['DOW'], center_id=center_obj, product_id=product_obj, defaults={ 'price': round(row['price'], 2), 'perpetual': True, 'product_name': product_obj.product_name, } ) return