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
