statement_service.py

# -*- coding: UTF-8 -*-
"""
@author:Zongxi.Li
@file:statement_service.py
@time:2021/02/01
"""
import json
import pandas as pd
import numpy as np

from datetime import datetime, date
from decimal import Decimal
from app.api.engine import tamp_order_engine, tamp_user_engine, \
    tamp_fund_engine, tamp_product_engine, tamp_diagnose_app_engine, TAMP_SQL


class DateEncoder(json.JSONEncoder):
    def default(self, obj):
        if isinstance(obj, (datetime, date)):
            return obj.__str__()
        if isinstance(obj, Decimal):
            return obj.__float__()
        return json.JSONEncoder.default(self, obj)


def get_fund_name(fund):
    with TAMP_SQL(tamp_fund_engine) as tamp_fund:
        tamp_fund_session = tamp_fund.session
        sql = "SELECT fund_short_name FROM fund_info WHERE id='{}'".format(fund)
        # df = pd.read_sql(sql, con)
        cur = tamp_fund_session.execute(sql)
        data = cur.fetchall()
        df = pd.DataFrame(list(data), columns=['fund_short_name'])
        if len(df) == 0:
            with TAMP_SQL(tamp_product_engine) as tamp_product:
                tamp_product_session = tamp_product.session
                sql = "SELECT fund_short_name FROM fund_info WHERE id='{}'".format(fund)
                # df = pd.read_sql(sql, con)
                cur = tamp_product_session.execute(sql)
                data = cur.fetchall()
                df = pd.DataFrame(list(data), columns=['fund_short_name'])
            if len(df) == 0:
                with TAMP_SQL(tamp_fund_engine) as tamp_fund:
                    tamp_fund_session = tamp_fund.session
                    sql = "SELECT fund_short_name FROM tx_fund_info WHERE `id`='{}'".format(fund)
                    # df = pd.read_sql(sql, con)
                    cur = tamp_fund_session.execute(sql)
                    data = cur.fetchall()
                    df = pd.DataFrame(list(data), columns=['fund_short_name'])
                if len(df) == 0:
                    with TAMP_SQL(tamp_fund_engine) as tamp_fund:
                        tamp_fund_session = tamp_fund.session
                        sql = "SELECT fund_name FROM ifa_imported_fund_info WHERE id='{}'".format(fund)
                        # df = pd.read_sql(sql, con)
                        cur = tamp_fund_session.execute(sql)
                        data = cur.fetchall()
                        df = pd.DataFrame(list(data), columns=['fund_short_name'])

    if df.fund_short_name.count() > 0:
        return df.fund_short_name.values[0]
    else:
        return fund


def get_order_info(user_id):
    with TAMP_SQL(tamp_diagnose_app_engine) as tamp_diagnose_app:
        tamp_diagnose_app_session = tamp_diagnose_app.session
        sql_user = """select f1.id, f1.fund_id, f2.realname, f3.customer_name, f1.customer_id, f3.valueSex,f1.type,f1.order_type,f1.pay_date,f1.subscription_fee,f1.confirm_share_date,f1.confirm_share,f1.confirm_amount,f1.nav,f1.folio_name from tamp_diagnose_app.customer_order_view f1, tamp_user.user_info f2,tamp_diagnose_app.customer_view f3   where f2.id=f1.user_id and f3.id=f1.customer_id  and f1.delete_tag=0 and user_id='{}'""".format(
            user_id)
        cur = tamp_diagnose_app_session.execute(sql_user)
        data = cur.fetchall()
        order_df = pd.DataFrame(list(data),
                                columns=['order_id', 'fund_id', 'user_name', 'customer_name', 'customer_id', 'value_sex',
                                         'type', 'order_type', 'pay_date',
                                         'subscription_fee', 'confirm_share_date', 'confirm_share',
                                         'confirm_amount', 'nav', 'folio_name'])
    return order_df


def get_provision_info(user_id):
    with TAMP_SQL(tamp_order_engine) as tamp_order, TAMP_SQL(tamp_user_engine) as tamp_user:
        tamp_order_session = tamp_order.session
        # sql = """select f1.ifa_id, f1.customer_id, f1.fund_id, f1.fund_nav,
        # f1.fund_nav_date, f1.predict_date, f1.pro_date, f1.manage_predict_fee, f1.permc_exc_predict_fee, f1.permc_rem_fee,
        # f1.recommend_ifa_id, f1.recommend_fee, f1.permc_recommend_fee, f1.incentive_fee_ratio, f1.retention_shares
        # from ifa_provision_day_info as f1 where f1.ifa_id='{}'""".format(user_id)
        sql = """SELECT * FROM `ifa_provision_day_info` WHERE `ifa_id` = '{}'""".format(user_id)
        cur = tamp_order_session.execute(sql)
        data = cur.fetchall()
        provision_df = pd.DataFrame(list(data),
                                columns=['id', 'ifa_id', 'customer_id', 'order_id', 'fund_id', 'fund_nav', 'fund_nav_date',
                                         'predict_date', 'pro_date','pro_type',
                                         'manage_predict_fee', 'permc_exc_predict_fee', 'permc_rem_predict_fee',
                                         'manage_fee', 'permc_exc_fee','permc_rem_fee', 'recommend_ifa_id',
                                         'recommend_fee','permc_recommend_fee', 'incentive_fee_ratio',
                                         'deduction','retention_shares','status'])
    return provision_df


def get_end_date(year, month):
    if month == 12:
        end_date = datetime(year=year + 1, month=1, day=1)
    else:
        end_date = datetime(year=year, month=month + 1, day=1)
    return end_date


def get_month_list(quarter, year):
    if quarter == 4:
        year = year - 1
    else:
        year = year

    month_list = range((quarter - 1) * 3 + 1, quarter * 3 + 1)
    return year, month_list


def type2note(type):
    type2note_dict = {1: "买入",
                      2: "赎回",
                      4: "分红"}
    return type2note_dict.get(type)


def cal_total_amount(df):
    purchase_amount = df[df['order_type'] == 1]['confirm_amount'].sum()
    redemption_amount = df[df['order_type'] == 2]['confirm_amount'].sum()
    subscription_fee = df['subscription_fee'].sum()
    total_amount = purchase_amount - redemption_amount - subscription_fee
    return total_amount


def num2chinese(number):
    num2chinese_dict = {1: '一',
                        2: '二',
                        3: '三',
                        4: '四'}
    return num2chinese_dict.get(number)


def get_tax_rate():
    # with TAMP_SQL(tamp_order_engine) as tamp_order, TAMP_SQL(tamp_user_engine) as tamp_user:
    #     tamp_order_session = tamp_order.session
    #     # sql = """select f1.ifa_id, f1.customer_id, f1.fund_id, f1.fund_nav,
    #     # f1.fund_nav_date, f1.predict_date, f1.pro_date, f1.manage_predict_fee, f1.permc_exc_predict_fee, f1.permc_rem_fee,
    #     # f1.recommend_ifa_id, f1.recommend_fee, f1.permc_recommend_fee, f1.incentive_fee_ratio, f1.retention_shares
    #     # from ifa_provision_day_info as f1 where f1.ifa_id='{}'""".format(user_id)
    #     sql = """SELECT * FROM `ifa_provision_day_info` WHERE `ifa_id` = '{}'""".format(user_id)
    #     cur = tamp_order_session.execute(sql)
    #     data = cur.fetchall()
    #     provision_df = pd.DataFrame(list(data),
    #                             columns=['id', 'ifa_id', 'customer_id', 'fund_id', 'order_id', 'fund_nav', 'fund_nav_date',
    #                                      'predict_date', 'pro_date','pro_type',
    #                                      'manage_predict_fee', 'permc_exc_predict_fee', 'permc_rem_predict_fee',
    #                                      'manage_fee', 'permc_exc_fee','permc_rem_fee', 'recommend_ifa_id',
    #                                      'recommend_fee','permc_recommend_fee', 'incentive_fee_ratio',
    #                                      'deduction','retention_shares','status'])
    return 0


class Statement(object):
    def __init__(self, user_id, year, month):
        self.user_id = user_id
        self.year = year
        self.month = month
        self.quarter = (self.month - 1) // 3
        if self.quarter == 0:
            self.quarter = 4
        self.ifa_order, self.ifa_name, self.ifa_gender = self.order_history()
        self.ifa_provision = self.provision_history()

    def bill_month(self):
        return str(self.year) + '年' + str(self.month) + '月'

    def ifa_call(self):
        if self.ifa_gender == 0:
            ifa_gender = '女士'
        elif self.ifa_gender == 1:
            ifa_gender = '先生'
        else:
            ifa_gender = ''
        if self.ifa_name is None:
            self.ifa_name = '用户'
        return ''.join([self.ifa_name, ifa_gender])

    def order_history(self):
        ifa_order = get_order_info(self.user_id)
        try:
            ifa_name = ifa_order['user_name'].values[0]
        except:
            ifa_name = '用户'
        try:
            ifa_gender = ifa_order['value_sex'].values[0]
        except:
            ifa_gender = 2
        end_date = get_end_date(self.year, self.month)
        order_history = ifa_order[ifa_order['pay_date'] < end_date]
        # ifa_order['fund_name'] = ifa_order['fund_id'].apply(lambda x: get_fund_name(x, fund_type=2).values[0][0])
        return order_history, ifa_name, ifa_gender

    def provision_history(self):
        ifa_provision = get_provision_info(self.user_id)
        end_date = get_end_date(self.year, self.month)
        provision_history = ifa_provision[ifa_provision['predict_date'] < end_date.date()]
        return provision_history

    def process_provision(self):
        year, month_list = get_month_list(self.quarter, self.year)
        start_date = date(year=year, month=min(month_list), day=1)
        end_date = get_end_date(year, max(month_list))
        provision_quarterly = self.ifa_provision[
            (self.ifa_provision['predict_date'] >= start_date) &
            (self.ifa_provision['predict_date'] < end_date.date())]
        return provision_quarterly

    def trade_record(self):
        ifa_cus_order_info = list(self.ifa_order.groupby(self.ifa_order.customer_id))

        trade_record_data = []
        for customer in ifa_cus_order_info:
            customer_df = customer[1]
            customer_df = customer_df[customer_df['order_type']!=4]
            df_length = customer_df['fund_id'].count()
            detail = []

            for i in range(df_length):
                detail.append({'fund': get_fund_name(customer_df['fund_id'].values[i]),
                               'pay_date': np.datetime_as_string(customer_df['pay_date'].values[i], unit='D'),
                               'confirm_share_date': np.datetime_as_string(customer_df['confirm_share_date'].values[i],
                                                                           unit='D'),
                               'confirm_amount': customer_df['confirm_amount'].values[i],
                               'nav': customer_df['nav'].values[i],
                               'confirm_share': customer_df['confirm_share'].values[i],
                               'note': type2note(customer_df['order_type'].values[i])
                               })

            total_amount = cal_total_amount(customer_df)
            trade_record_data.append({'customer': customer_df['customer_name'].values[0],
                                      'total_amount': Decimal(round(float(total_amount) / 10000.0, 6)),
                                      'detail': detail})

        return trade_record_data

    def month_remain(self):
        year, month_list = get_month_list(self.quarter, self.year)
        month_remain_data = {}
        month_detail = []
        quarter_amount = 0
        for month in month_list:
            end_date = get_end_date(year, month)
            order_history = self.ifa_order[self.ifa_order['pay_date'] < end_date]
            df_length = order_history['fund_id'].count()

            if df_length > 0:
                month_amount = cal_total_amount(order_history)
            else:
                month_amount = 0

            single_month = {'month': str(month) + '月', 'month_amount': month_amount}
            quarter_amount += month_amount
            month_detail.append(single_month)

        quarter = num2chinese(self.quarter)
        month_detail.append(
            {'month': quarter + '季度月均存量', 'month_amount': Decimal(round(float(quarter_amount) / 3.0, 2))})
        month_remain_data['month_detail'] = month_detail

        bonus_policy = []
        bonus_policy.append({'month_achieve': '达到1000万以上', 'bonus_policy_item': '标准管理费分成+0.1%'})
        bonus_policy.append({'month_achieve': '达到2000万以上', 'bonus_policy_item': '标准管理费分成+0.15%'})
        bonus_policy.append({'month_achieve': '达到3000万以上', 'bonus_policy_item': '标准管理费分成+0.2%'})
        bonus_policy.append({'month_achieve': '达到4000万以上', 'bonus_policy_item': '标准管理费分成+0.25%'})
        bonus_policy.append({'month_achieve': '达到5000万以上', 'bonus_policy_item': '标准管理费分成+0.3%'})
        month_remain_data['bonus_policy'] = bonus_policy

        bonus_image = []
        bonus_image.append({'goal_value': round(float(quarter_amount)/3.0/500000.0,2), 'goal': '5000万目标'})
        bonus_image.append({'goal_value': round(float(quarter_amount)/3.0/400000.0,2), 'goal': '4000万目标'})
        bonus_image.append({'goal_value': round(float(quarter_amount)/3.0/300000.0,2), 'goal': '3000万目标'})
        bonus_image.append({'goal_value': round(float(quarter_amount)/3.0/200000.0,2), 'goal': '2000万目标'})
        bonus_image.append({'goal_value': round(float(quarter_amount)/3.0/100000.0,2), 'goal': '1000万目标'})
        month_remain_data['bonus_image'] = bonus_image
        return month_remain_data

    def subscription_fee(self):
        year, month_list = get_month_list(self.quarter, self.year)

        subscription_data = {}
        customer_detail = []
        total_before_tax = 0
        total_after_tax = 0
        start_date = datetime(year=year, month=min(month_list), day=1)
        end_date = get_end_date(year, max(month_list))
        order_quarterly = self.ifa_order[(self.ifa_order['pay_date'] >= start_date) &
                                         (self.ifa_order['pay_date'] < end_date) &
                                         (self.ifa_order['order_type'] == 1)]
        ifa_cus_order_monthly = list(order_quarterly.groupby(order_quarterly.customer_id))

        for customer in ifa_cus_order_monthly:
            customer_df = customer[1]
            df_length = customer_df['fund_id'].count()
            before_tax = 0
            after_tax = 0
            detail = []
            for i in range(df_length):
                tax_rate = get_tax_rate()
                if customer_df['subscription_fee'].values[i] is None:
                    continue

                detail.append({'fund': get_fund_name(customer_df['fund_id'].values[i]),
                               'confirm_amount': customer_df['confirm_amount'].values[i],
                               'pay_date': np.datetime_as_string(customer_df['pay_date'].values[i], unit='D'),
                               'subscription_fee': customer_df['subscription_fee'].values[i],
                               'tax': customer_df['subscription_fee'].values[i] * tax_rate
                               })

                before_tax += customer_df['subscription_fee'].values[i]
                after_tax += customer_df['subscription_fee'].values[i] * (1 - tax_rate)

            customer_detail.append({'customer': customer_df['customer_name'].values[0],
                                    'before_tax': Decimal(round(float(before_tax), 2)),
                                    'after_tax': Decimal(round(float(after_tax), 2)),
                                    'detail': detail})
            total_before_tax += before_tax
            total_after_tax += after_tax

        subscription_data['total_before_tax'] = total_before_tax
        subscription_data['total_tax'] = total_before_tax - total_after_tax
        subscription_data['total_after_tax'] = total_after_tax
        subscription_data['customer_detail'] = customer_detail

        return subscription_data, total_after_tax

    def manage_fee(self):
        manage_data = {}
        total_before_tax = 0
        total_after_tax = 0
        customer_detail = []

        provision_quarterly = self.process_provision()
        processed_provision = list(provision_quarterly.groupby([provision_quarterly.customer_id,
                                   provision_quarterly.fund_id]))

        for customer_fund in processed_provision:
            customer_id = customer_fund[0][0]
            customer_name = self.ifa_order[self.ifa_order['customer_id'] == customer_id]['customer_name'].values[0]

            customer_fund_df = customer_fund[1]
            fund_id = customer_fund_df['fund_id'].values[0]
            fund_name = get_fund_name(fund_id)

            df_length = customer_fund_df['predict_date'].count()
            before_tax = 0
            after_tax = 0
            detail = []
            for i in range(df_length):
                try:
                    tax_rate = get_tax_rate()
                except:
                    tax_rate = 0

                detail.append({'date': customer_fund_df['predict_date'].values[i].strftime('%Y-%m-%d'),
                               'hold_share': customer_fund_df['retention_shares'].values[i],
                               'nav': customer_fund_df['fund_nav'].values[i],
                               'manage_fee_day': customer_fund_df['manage_fee'].values[i]})

                before_tax += customer_fund_df['manage_fee'].values[i]
                after_tax += customer_fund_df['manage_fee'].values[i] * (1 - tax_rate)

            customer_detail.append({'customer': customer_name,
                                    'fund': fund_name,
                                    'before_tax': round(float(before_tax), 2),
                                    'after_tax': round(float(after_tax), 2),
                                    'detail': detail})
            total_before_tax += before_tax
            total_after_tax += after_tax

        manage_data['total_before_tax'] = total_before_tax
        manage_data['total_tax'] = total_before_tax - total_after_tax
        manage_data['total_after_tax'] = total_after_tax
        manage_data['customer_detail'] = customer_detail
        return manage_data, total_after_tax

    def carry(self):
        carry_data = {}
        total_before_tax = 0
        total_after_tax = 0
        customer_detail = []

        year, month_list = get_month_list(self.quarter, self.year)
        start_date = date(year=year, month=min(month_list), day=1)
        end_date = get_end_date(year, max(month_list))
        provision_quarterly = self.ifa_provision[
            (self.ifa_provision['predict_date'] >= start_date) &
            (self.ifa_provision['predict_date'] < end_date.date())]
        processed_provision = list(provision_quarterly.groupby(provision_quarterly.fund_id))

        for fund in processed_provision:

            fund_id = fund[0]
            fund_df = fund[1]
            carry_sum = fund_df['permc_exc_fee'].sum()
            if carry_sum == 0:
                continue
            try:
                fund_name = get_fund_name(fund_id)
            except:
                fund_name = fund_id

            before_tax = 0
            after_tax = 0
            detail = []
            for _, row in fund_df.iterrows():
                provision_carry = row['permc_exc_fee']
                if provision_carry == 0:
                    continue

                tax_rate = get_tax_rate()

                customer_id = row['customer_id']
                customer_name = self.ifa_order[self.ifa_order['customer_id'] == customer_id]['customer_name'].values[0]
                order_id = row['order_id']
                order_type = type2note(self.ifa_order[self.ifa_order['order_id'] == order_id]['order_type'].values[0])
                confirm_date = self.ifa_order[self.ifa_order['order_id'] == order_id]['confirm_share_date'].values[0]
                nav = self.ifa_order[self.ifa_order['order_id'] == order_id]['nav'].values[0]
                native_amount = nav
                provision_date = self.ifa_order[self.ifa_order['order_id'] == order_id]['confirm_share_date'].values[0]
                detail.append({'customer': customer_name,
                               'order_type': order_type,
                               'confirm_date': np.datetime_as_string(confirm_date, unit='D'),
                               'native_amount': native_amount,
                               'nav': nav,
                               'provision_date': np.datetime_as_string(provision_date, unit='D'),
                               'provision_carry': provision_carry}
                              )

                before_tax += provision_carry
                after_tax += provision_carry * (1 - tax_rate)

            customer_detail.append({
                                    'fund': fund_name,
                                    'before_tax': round(float(before_tax), 2),
                                    'after_tax': round(float(after_tax), 2),
                                    'detail': detail})
            total_before_tax += before_tax
            total_after_tax += after_tax

        carry_data['total_before_tax'] = total_before_tax
        carry_data['total_tax'] = total_before_tax - total_after_tax
        carry_data['total_after_tax'] = total_after_tax
        carry_data['customer_detail'] = customer_detail
        return carry_data, total_after_tax

    def incentive(self):
        incentive_data = {}
        total_before_tax = 0
        total_after_tax = 0
        customer_detail = []

        provision_quarterly = self.process_provision()
        processed_provision = list(provision_quarterly.groupby([provision_quarterly.customer_id,
                                   provision_quarterly.fund_id]))

        for customer_fund in processed_provision:
            customer_id = customer_fund[0][0]
            customer_name = self.ifa_order[self.ifa_order['customer_id'] == customer_id]['customer_name'].values[0]
            fund_id = customer_fund[0][1]
            fund_name = get_fund_name(fund_id)

            customer_fund_df = customer_fund[1]
            incentive_rate_sum = customer_fund_df['incentive_fee_ratio'].sum()
            if incentive_rate_sum == 0:
                continue
            df_length = customer_fund_df['predict_date'].count()
            before_tax = 0
            after_tax = 0
            detail = []
            for i in range(df_length):
                incentive_rate = customer_fund_df['incentive_fee_ratio'].values[i]
                if incentive_rate == 0:
                    continue

                tax_rate = get_tax_rate()

                detail.append({'date': customer_fund_df['predict_date'].values[i].strftime('%Y-%m-%d'),
                               'hold_share': customer_fund_df['retention_shares'].values[i],
                               'nav': customer_fund_df['fund_nav'].values[i],
                               'incentive_day': customer_fund_df['manage_fee'].values[i] * incentive_rate})

                before_tax += customer_fund_df['manage_fee'].values[i] * incentive_rate
                after_tax += customer_fund_df['manage_fee'].values[i] * incentive_rate * (1 - tax_rate)

            customer_detail.append({'customer': customer_name,
                                    'fund': fund_name,
                                    'before_tax': round(float(before_tax), 2),
                                    'after_tax': round(float(after_tax), 2),
                                    'detail': detail})
            total_before_tax += before_tax
            total_after_tax += after_tax

        incentive_data['total_before_tax'] = total_before_tax
        incentive_data['total_tax'] = total_before_tax - total_after_tax
        incentive_data['total_after_tax'] = total_after_tax
        incentive_data['customer_detail'] = customer_detail
        return incentive_data, total_after_tax

    def summary(self):
        summary_data = {}
        summary_data['bill_month'] = self.bill_month()
        summary_data['user_name'] = self.ifa_call()
        summary_data['quarter'] = num2chinese(self.quarter)
        summary_data['trade_record'] = self.trade_record()
        summary_data['month_remain'] = self.month_remain()
        summary_data['subscription_fee'], sub_after_tax = self.subscription_fee()
        summary_data['manage_fee'], man_after_tax = self.manage_fee()
        summary_data['carry'], carry_after_tax = self.carry()
        summary_data['incentive'], incentive_after_tax = self.incentive()
        summary_data['recommend_bonus'] = {'total_before_tax': Decimal('0'),
                                           'total_after_tax': Decimal('0'),
                                           'total_tax': Decimal('0.0'),
                                           'referral_detail':[]}

        summary_after_tax = sub_after_tax + man_after_tax + carry_after_tax + incentive_after_tax
        summary_data['summary'] = {'total_after_tax': summary_after_tax,
                                   'detail': [{'item': "已结算申购费用", 'amount': sub_after_tax, 'note': ''},
                                              {'item': "已结算管理费用", 'amount': man_after_tax, 'note': ''},
                                              {'item': "已结算业绩报酬费用", 'amount': carry_after_tax, 'note': ''},
                                              {'item': "已结算业绩激励奖金", 'amount': incentive_after_tax, 'note': ''},
                                              {'item': "已结算推荐费", 'amount': Decimal('0.00'), 'note': ''},
                                              {'item': "{}年{}季度待结算佣金".format(self.year, num2chinese(self.quarter)),
                                              'amount': Decimal('0.00'), 'note': ''}
                                              ]}
        return summary_data


if __name__ == '__main__':
    ret = Statement(user_id='USER_INFO15914346866762', year=2020, month=12).summary()
    print(json.dumps(ret, cls=DateEncoder, ensure_ascii=False))