def create_account(self, parent_user, type, account_class):
account_balance = AccountBalance()
account_balance.parent_user_id = parent_user.id
account_balance.type = type
account_balance.account_class = account_class
account_balance.rate = 0
account_balance.balance = 0
account_balance.status = 0
account_balance.save()
return account_balance
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):
parents = UserParentInfo.objects.filter().all()
normal_rate, sg_rate = self.calculate_rate()
print(normal_rate, sg_rate, '\n')
AccountBalance.objects.all().delete()
for parent in parents:
print(parent.id, '\n')
charge_balance_change = BalanceChangeNew.objects.filter(
parent_user_id=parent.id,
reason__in=(BalanceChangeNew.TOP_UP, BalanceChangeNew.BONUS,
BalanceChangeNew.TRANSFER_IN,
BalanceChangeNew.TRANSFER_OUT)).first()
if charge_balance_change:
type = AccountBalance.TOPUP_AMOUNT
balance_rate = normal_rate
else:
type = AccountBalance.BONUS_AMOUNT
balance_rate = 0
normal_balance = AccountBalance()
normal_balance.parent_user_id = parent.id
normal_balance.type = type
normal_balance.account_class = AccountBalance.NORMAL_ACCOUNT
normal_balance.balance = parent.balance + parent.bonus_balance
normal_balance.rate = balance_rate
normal_balance.save()
sg_balance = AccountBalance()
sg_balance.parent_user_id = parent.id
sg_balance.type = AccountBalance.TOPUP_AMOUNT
sg_balance.account_class = AccountBalance.PRIVATE_ACCOUNT
sg_balance.balance = parent.sg_balance
sg_balance.rate = sg_rate
sg_balance.save()
balance_changes = BalanceChangeNew.objects.filter(
parent_user_id=parent.id).all()
balance_id = normal_balance.id
commit_list = []
for balance_change in balance_changes:
if balance_change.create_time.strftime(
'%Y-%m-%d %H:%M:%S') < '2020-02-01 00:00:00':
# balance_change.balance_id = normal_balance.id
# balance_change.save()
commit_list.append((normal_balance.id, balance_change.id))
balance_id = normal_balance.id
else:
if balance_change.reason in (
BalanceChangeNew.AD_HOC,
BalanceChangeNew.ABSENCE_PENALTY,
BalanceChangeNew.NO_SHOW_COMPENSATION):
'''virtualclass是新加坡小班课的话， balance id是sg_balance的id, 其他都是普通balance id'''
vc = VirtualclassInfo.objects.select_related(
'lesson__course').select_related(
'lesson__course__course_edition').filter(
id=balance_change.reference).first()
if vc:
if vc.class_type_id == ClassType.SMALLCLASS and vc.lesson.course.course_edition.id == CourseEdition.SG and balance_change.amount in (
-1, 1):
# balance_change.balance_id = sg_balance.id
# balance_change.save()
commit_list.append(
(sg_balance.id, balance_change.id))
balance_id = sg_balance.id
# continue
elif balance_change.reason in (
BalanceChangeNew.TOP_UP, BalanceChangeNew.BONUS,
BalanceChangeNew.REFERRAL_INCENTIVE,
BalanceChangeNew.REFUND):
'''查询recharge_order, 有sg_class就是sg_balance'''
recharge_order = RechargeOrder.objects.filter(
order_no=balance_change.reference).first()
if recharge_order:
if recharge_order.currency == 'SGD' and recharge_order.rate == 30:
# balance_change.balance_id = sg_balance.id
# balance_change.save()
commit_list.append(
(sg_balance.id, balance_change.id))
balance_id = sg_balance.id
# continue
else:
commit_list.append(
(normal_balance.id, balance_change.id))
balance_id = normal_balance.id
sql = '''\nupdate finance_balance_change set balance_id={} where id={};\n'''.format(
balance_id, balance_change.id)
print(sql)
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()