def handle(self, *args, **kwargs):
file_name = kwargs.get('file_name')
sheet_name = kwargs.get('sheet_name', None)
path = os.path.dirname(
os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
path = os.path.join(path, 'files')
file_path = os.path.join(path, file_name)
print(file_path)
# 读取excle
wb = openpyxl.load_workbook(file_path)
# 选择sheet
if sheet_name:
sh = wb[sheet_name]
# ce = sh.cell(row=1, column=1) # 读取第一行，
for cases in list(sh.rows)[0:]:
parent_name = str(cases[0].value).strip()
parent = UserParentInfo.objects.filter(
Q(username=parent_name) | Q(email=parent_name)
| Q(phone=parent_name)).only('id').first()
if not parent:
print('未找到该用户--', parent_name)
continue
recharge_count = RechargeOrder.objects.filter(
parent_user_id=parent.id,
status=RechargeOrder.PAID).count()
if not recharge_count:
print('未充值用户--', parent_name)
continue
print('已充值用户--', parent_name)
account_balance = AccountBalanceNew()
account_balance.balance = 0
account_balance.type = AccountBalanceNew.BONUS_AMOUNT
account_balance.account_class = AccountBalanceNew.NORMAL_ACCOUNT
account_balance.rate = 0
account_balance.status = 0
account_balance.parent_user_id = parent.id
account_balance.save(force_insert=True)
balance_change = BalanceChange()
balance_change.role = BalanceChange.PARENT
balance_change.user_id = parent.id
balance_change.parent_user_id = parent.id
balance_change.amount = 1
balance_change.normal_amount = 0
balance_change.reason = BalanceChange.COMPENSATION
balance_change.reference = 0
balance_change.balance_id = account_balance.id
balance_change.save(force_insert=True)
wb.close()
def first_course_record(self, sheet):
for cases in list(sheet.rows)[2:]:
adviser_name = str(cases[0].value)
refer_name = str(cases[1].value).strip()
parent_name = str(cases[2].value).strip()
create_time = cases[3].value
adviser = UserInfo.objects.filter(username=adviser_name).first()
parent = UserParentInfo.objects.filter(
Q(username=parent_name) | Q(email=parent_name)
| Q(phone=parent_name)).first()
refer = UserParentInfo.objects.filter(
Q(username=refer_name) | Q(email=refer_name)
| Q(phone=refer_name)).first()
if not refer or not parent:
print('未找到推荐人或被推荐人', adviser_name, refer_name, parent_name,
create_time)
continue
account_balance = AccountBalanceNew()
account_balance.parent_user_id = refer.id
account_balance.type = AccountBalanceNew.BONUS_AMOUNT
account_balance.account_class = AccountBalanceNew.NORMAL_ACCOUNT
account_balance.status = 0
account_balance.balance = 0
account_balance.rate = 0
account_balance.save()
balance_change = BalanceChange()
balance_change.role = BalanceChange.PARENT
balance_change.user_id = parent.id
balance_change.parent_user_id = refer.id
balance_change.reference = 0
balance_change.amount = 1
balance_change.normal_amount = 0
balance_change.reason = 19
balance_change.balance_id = account_balance.id
if adviser:
balance_change.adviser_user_id = adviser.id
balance_change.save()
def handle(self, *args, **options):
for parent_name in all_parent_names:
parent_user = UserParentInfo.objects.filter(
Q(username=parent_name) | Q(email=parent_name)
| Q(phone=parent_name)).first()
if not parent_user:
print('not found {}'.format(parent_name))
continue
balance_change = BalanceChange()
balance_change.role = BalanceChange.PARENT
balance_change.user_id = parent_user.id
balance_change.reason = BalanceChange.COMPENSATION
balance_change.amount = 1
balance_change.reference = 0
balance_change.normal_amount = 0
balance_change.parent_user_id = parent_user.id
balance_change.save()
def give_out_bonus(self, request, pk):
group_info = ActivityGroupInfo.objects.get(id=pk)
if group_info.grant_award == ActivityGroupInfo.GRANT:
return JsonResponse(code=1, msg='已发放奖励', status=status.HTTP_200_OK)
group_recharges = ActivityGroupRecharge.objects.filter(group_id=group_info.id).all()
if len(group_recharges) == 0:
return JsonResponse(code=1, msg='没有成员，不能发放奖励', status=status.HTTP_200_OK)
for group_recharge in group_recharges:
order_no = group_recharge.order_no
recharge_balance_change = BalanceChange.objects.filter(reference=order_no, parent_user_id=group_recharge.parent_user_id, reason=3).first()
if not recharge_balance_change:
logger.debug('not found recharge record where order_no={}, parent_user_id={}'.format(order_no, group_recharge.parent_user_id))
parent_user = group_recharge.parent_user
balance_change = BalanceChange()
balance_change.reason = BalanceChange.BONUS
balance_change.adviser_user_id = parent_user.adviser_user_id
balance_change.xg_user_id = parent_user.xg_user_id
balance_change.amount = group_recharge.bonus_amount
balance_change.normal_amount = 0
balance_change.reference = group_recharge.order_no
balance_change.parent_user_id = group_recharge.parent_user_id
balance_change.user_id = parent_user.id
balance_change.role = BalanceChange.PARENT
balance_change.balance_id = recharge_balance_change.balance_id
balance_change.save()
# recharge_order = RechargeOrder.objects.filter(order_no=group_recharge.order_no).first()
# recharge_order.incentive_amount = group_recharge.bonus_amount
# recharge_order.save()
group_info.grant_award = ActivityGroupInfo.GRANT
group_info.save()
cms_user = request.session.get('user')
logger.debug('give out bonus, user_id:{}, group_info_id:{}'.format(cms_user.get('id'), group_info.id))
return JsonResponse(code=0, msg='success', status=status.HTTP_200_OK)
def handle(self, *args, **kwargs):
file_name = kwargs.get('file_name')
sheet_name = kwargs.get('sheet_name', None)
path = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
path = os.path.join(path, 'files')
file_path = os.path.join(path, file_name)
print(file_path)
now_time = timezone.now()
if not sheet_name:
print('not found sheet: {}'.format(sheet_name))
return
# try:
wb = openpyxl.load_workbook(file_path)
sh = wb[sheet_name]
for cases in list(sh.rows)[2:]:
try:
print(cases[0].value)
topup_date = cases[1].value
parent_user_name = str(cases[2].value).strip()
currency = str(cases[3].value).strip().upper()
price = cases[4].value
amount = cases[5].value
topup_amount = cases[6].value
code = cases[7].value
payment_method = cases[8].value
reference = cases[9].value
remark = cases[10].value
if remark:
distinct = remark * 100
else:
distinct = None
exchange_rate = ExchangeRate.objects.filter(currency=currency, valid_end__gt=now_time, valid_start__lte=now_time).first()
if not exchange_rate:
print('not found currency: {}'.format(currency))
continue
parent_user = UserParentInfo.objects.filter(Q(username=parent_user_name)|Q(email=parent_user_name)|Q(phone=parent_user_name)).first()
if not parent_user:
print('not found user: {}'.format(parent_user_name))
continue
with transaction.atomic():
recharge_order = RechargeOrder()
recharge_order.create_time = topup_date
recharge_order.update_time = topup_date
recharge_order.parent_user_id = parent_user.id
recharge_order.recharge_type = RechargeOrder.PACKAGE
recharge_order.code = code
recharge_order.recharge_amount = amount
recharge_order.incentive_amount = 0
recharge_order.total_amount = amount
recharge_order.currency_id = exchange_rate.id
recharge_order.currency = exchange_rate.currency
recharge_order.rate = price
recharge_order.origin_total_price = price * amount
recharge_order.save_price = price * amount - topup_amount
recharge_order.discount = distinct
recharge_order.total_price = topup_amount
recharge_order.reference = reference
recharge_order.status = RechargeOrder.PAID
before_recharge_order = RechargeOrder.objects.filter(parent_user_id=parent_user.id, status=1).first()
if before_recharge_order:
recharge_order.first_recharge = RechargeOrder.NOT_FIRST_RECHARGE
else:
recharge_order.first_recharge = RechargeOrder.FIRST_RECHARGE
recharge_order.pay_type = RechargeOrder.CARD
recharge_order.save()
account_balance = AccountBalance()
account_balance.parent_user_id = parent_user.id
account_balance.type = AccountBalance.TOPUP_AMOUNT
account_balance.account_class = AccountBalance.PRIVATE_ACCOUNT
account_balance.rate = recharge_order.total_price/recharge_order.origin_total_price
account_balance.balance = 0
account_balance.status = 0
account_balance.save()
balance_change = BalanceChange()
balance_change.role = BalanceChange.PARENT
balance_change.reason = BalanceChange.TOP_UP
balance_change.user_id = parent_user.id
balance_change.amount = recharge_order.total_amount
balance_change.normal_amount = 0
balance_change.reference = recharge_order.order_no
balance_change.parent_user_id = parent_user.id
if parent_user.adviser_user_id:
balance_change.adviser_user_id = parent_user.adviser_user_id
if parent_user.xg_user_id:
balance_change.xg_user_id = parent_user.xg_user_id
balance_change.balance_id = account_balance.id
balance_change.save()
payment = Payment()
payment.order_id = recharge_order.id
payment.amount = recharge_order.total_price
payment.reference = payment_method
payment.type = recharge_order.pay_type
payment.status = 1
payment.channel = 'stripe'
payment.success_time = recharge_order.create_time
payment.save()
except Exception as e:
print(e)
print('{} error'.format(cases[0].value))
# except Exception as e:
# print(e)
# finally:
wb.close()
def handle(self, *args, **kwargs):
file_name = kwargs.get('file_name')
sheet_name = kwargs.get('sheet_name', None)
sheet_name2 = kwargs.get('sheet_name2', None)
path = os.path.dirname(
os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
path = os.path.join(path, 'files')
file_path = os.path.join(path, file_name)
print(file_path)
# 读取excle
wb = openpyxl.load_workbook(file_path)
# 选择sheet
if sheet_name:
sh = wb[sheet_name]
# ce = sh.cell(row=1, column=1) # 读取第一行，
for cases in list(sh.rows)[1:]:
adviser_name = str(cases[0].value).strip()
parent_name = str(cases[1].value).strip()
create_time = cases[2].value
adviser = UserInfo.objects.filter(
username=adviser_name).first()
parent = UserParentInfo.objects.filter(
Q(username=parent_name) | Q(email=parent_name)
| Q(phone=parent_name)).first()
if not parent:
print('未找到该用户', adviser_name, parent_name, create_time)
continue
account_balance = AccountBalanceNew()
account_balance.balance = 0
account_balance.type = AccountBalanceNew.BONUS_AMOUNT
account_balance.account_class = AccountBalanceNew.NORMAL_ACCOUNT
account_balance.rate = 0
account_balance.status = 0
account_balance.parent_user_id = parent.id
account_balance.save()
balance_change = BalanceChange()
balance_change.role = BalanceChange.PARENT
balance_change.user_id = parent.id
balance_change.parent_user_id = parent.id
balance_change.amount = 1
balance_change.normal_amount = 0
balance_change.reason = BalanceChange.COMPENSATION
balance_change.reference = 0
balance_change.balance_id = account_balance.id
if adviser:
balance_change.adviser_user_id = adviser.id
balance_change.save()
# sheet2 = wb['Sheet2']
#
if sheet_name2:
sheet2 = wb[sheet_name2]
self.first_course_record(sheet2)
wb.close()
def handle(self, *args, **kwargs):
file_name = kwargs.get('file_name')
sheet_name = kwargs.get('sheet_name', None)
sheet_name2 = kwargs.get('sheet_name2', None)
path = os.path.dirname(
os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
path = os.path.join(path, 'files')
file_path = os.path.join(path, file_name)
print(file_path)
# 读取excle
wb = openpyxl.load_workbook(file_path)
# 选择sheet
if sheet_name:
sh = wb[sheet_name]
# ce = sh.cell(row=1, column=1) # 读取第一行，
for cases in list(sh.rows)[1:]:
if not cases[0].value:
break
transfer_username = str(cases[0].value).strip()
amount = float(cases[1].value)
recipte_username = str(cases[2].value).strip()
xg_username = str(cases[4].value).strip()
create_time = timezone.now()
xg_user = UserInfo.objects.filter(username=xg_username).first()
transfer_parent = UserParentInfo.objects.filter(
Q(username=transfer_username) | Q(email=transfer_username)
| Q(phone=transfer_username)).first()
if not transfer_parent:
print('未找到转出课时用户', transfer_username)
continue
recipte_parent = UserParentInfo.objects.filter(
Q(username=recipte_username) | Q(email=recipte_username)
| Q(phone=recipte_username)).first()
if not recipte_parent:
print('未找到转入课时用户', recipte_username)
continue
transfer_parent_balance = AccountBalance.objects.filter(
parent_user_id=transfer_parent.id,
state=AccountBalance.NOT_DELETE,
status=0,
account_class=AccountBalance.NORMAL_ACCOUNT).values(
'parent_user_id').annotate(
balance_sum=Sum('balance')).values(
'parent_user_id', 'balance_sum').first()
# if transfer_parent_balance.get('balance_sum') < amount:
# print('转出账户课时余额不够', transfer_username)
# continue
transfer_data = Transfer()
transfer_data.amount = amount
transfer_data.transfer_user_id = transfer_parent.id
transfer_data.recipient_user_id = recipte_parent.id
print(
"""insert into finance_transfer(id, amount, transfer_user_id, recipient_user_id, create_time, update_time) values({},{},{},{},'{}','{}')"""
.format(2102, amount, transfer_parent.id,
recipte_parent.id, '2020-07-17 08:10:10',
'2020-07-17 08:10:10'))
account_balance = AccountBalance()
account_balance.balance = 0 - amount
account_balance.type = AccountBalance.BONUS_AMOUNT
account_balance.account_class = AccountBalance.NORMAL_ACCOUNT
account_balance.rate = 0
account_balance.status = 0
account_balance.parent_user_id = transfer_parent.id
print(
"""insert into accounts_balance(balance, type, account_class, rate, status, parent_user_id, state, create_time, update_time) values({},{},{},{},{},{},{},'{}','{}')"""
.format(account_balance.balance, account_balance.type,
account_balance.account_class,
account_balance.rate, account_balance.status,
account_balance.parent_user_id, 0,
'2020-07-17 08:10:10', '2020-07-17 08:10:10'))
recipte_account_balance = AccountBalance()
recipte_account_balance.balance = amount
recipte_account_balance.type = AccountBalance.BONUS_AMOUNT
recipte_account_balance.account_class = AccountBalance.NORMAL_ACCOUNT
recipte_account_balance.rate = 0
recipte_account_balance.status = 0
recipte_account_balance.parent_user_id = recipte_parent.id
print(
"""insert into accounts_balance(balance, type, account_class, rate, status, parent_user_id, state, create_time, update_time) values({},{},{},{},{},{},{},'{}','{}')"""
.format(recipte_account_balance.balance,
recipte_account_balance.type,
recipte_account_balance.account_class,
recipte_account_balance.rate,
recipte_account_balance.status,
recipte_account_balance.parent_user_id, 0,
'2020-07-17 08:10:10', '2020-07-17 08:10:10'))
balance_change = BalanceChange()
balance_change.role = BalanceChange.PARENT
balance_change.user_id = transfer_parent.id
balance_change.parent_user_id = transfer_parent.id
balance_change.amount = 0 - amount
balance_change.normal_amount = 0
balance_change.reason = BalanceChange.TRANSFER_OUT
balance_change.reference = 2102
balance_change.balance_id = account_balance.id
recipte_balance_change = BalanceChange()
balance_change.role = BalanceChange.PARENT
recipte_balance_change.user_id = recipte_parent.id
recipte_balance_change.parent_user_id = recipte_parent.id
recipte_balance_change.amount = amount
recipte_balance_change.normal_amount = 0
recipte_balance_change.reason = BalanceChange.TRANSFER_OUT
recipte_balance_change.reference = 2102
recipte_balance_change.balance_id = account_balance.id
if xg_user:
balance_change.xg_user_id = xg_user.id
recipte_balance_change.xg_user_id = xg_user.id
print(
"""insert into finance_balance_change(role, user_id, parent_user_id, amount, normal_amount, reason, reference, balance_id, xg_user_id, 'create_time', 'update_time') values({},{},{},{},{},{},{},{},'{}','{}')"""
.format(balance_change.role, balance_change.user_id,
balance_change.parent_user_id,
balance_change.amount,
balance_change.normal_amount,
balance_change.reason, balance_change.reference,
balance_change.balance_id,
balance_change.xg_user_id, '2020-07-17 08:10:10',
'2020-07-17 08:10:10'))
print(
"""insert into finance_balance_change(role, user_id, parent_user_id, amount, normal_amount, reason, reference, balance_id, xg_user_id, 'create_time', 'update_time') values({},{},{},{},{},{},{},{},'{}','{}')"""
.format(recipte_balance_change.role,
recipte_balance_change.user_id,
recipte_balance_change.parent_user_id,
recipte_balance_change.amount,
recipte_balance_change.normal_amount,
recipte_balance_change.reason,
recipte_balance_change.reference,
recipte_balance_change.balance_id,
recipte_balance_change.xg_user_id,
'2020-07-17 08:10:10', '2020-07-17 08:10:10'))
# sheet2 = wb['Sheet2']
#
if sheet_name2:
sheet2 = wb[sheet_name2]
self.first_course_record(sheet2)
wb.close()