manyi/script/transaction/analysis.py

# -*- coding: utf-8 -*-
# !/usr/bin/env python

import datetime
import os
import sys

PROJECT_ROOT = os.path.join(os.path.abspath(os.path.split(os.path.realpath(__file__))[0] + "/.."), '..')
sys.path.insert(0, PROJECT_ROOT)

from script.base import init_env

os.environ["DJANGO_SETTINGS_MODULE"] = "configs.production"

init_env(False)

from apps.web.core.db import copy_document_classes
from apps.web.dealer.proxy import DealerIncomeProxy
from apps.web.dealer.models import Dealer

begin = datetime.datetime(2017, 1, 1, 0, 0, 0)
end = datetime.datetime(2089, 1, 1, 0, 0, 0)
check_date = datetime.datetime(2022, 2, 1, 0, 0, 0)

import simplejson as json

def get_partner_ids(dealerId):
    rv = []

    try:

        if begin < check_date:
            his_model_cls = copy_document_classes(DealerIncomeProxy, '{}_his'.format(DealerIncomeProxy.__name__),
                                                  'report_his')
            for item in his_model_cls.objects(
                    dealerIds=dealerId,
                    dateTimeAdded__gte=begin,
                    dateTimeAdded__lt=check_date if check_date < end else end
            ).only('dealerIds').limit(100):  # type: DealerIncomeProxy
                for _dealerId in item.dealerIds:
                    if str(_dealerId) == str(dealerId):
                        continue
                    elif str(_dealerId) not in rv:
                        rv.append(str(_dealerId))

        if begin > check_date:
            for item in DealerIncomeProxy.objects(
                    dealerIds=dealerId,
                    dateTimeAdded__gte=begin,
                    dateTimeAdded__lt=check_date if check_date < end else end
            ).only('dealerIds').limit(100):
                for _dealerId in item.dealerIds:
                    if str(_dealerId) == str(dealerId):
                        continue
                    elif str(_dealerId) not in rv:
                        rv.append(str(_dealerId))

        else:
            if check_date > end:
                pass
            else:
                for item in DealerIncomeProxy.objects(
                        dealerIds=dealerId,
                        dateTimeAdded__gte=begin,
                        dateTimeAdded__lt=check_date if check_date < end else end
                ).only('dealerIds').limit(100):
                    for _dealerId in item.dealerIds:
                        if str(_dealerId) == str(dealerId):
                            continue
                        elif str(_dealerId) not in rv:
                            rv.append(str(_dealerId))
    except Exception as e:
        pass

    return list(set(rv))


def read_xls(filename):
    rv = {}

    import xlrd

    book = xlrd.open_workbook(filename)

    sheet1 = book.sheet_by_index(0)

    print('row is {}'.format(sheet1.nrows))

    for _row in range(sheet1.nrows):
        diff = sheet1.cell_value(_row, 0)
        dealerId = sheet1.cell_value(_row, 1)

        if dealerId in rv:
            print('{} is dup.'.format(dealerId))

        rv[dealerId] = (diff, dealerId)

    print('row is {}'.format(len(rv.keys())))

    return rv


def write_execl(ass_dict, value_dict):
    import openpyxl

    book = openpyxl.Workbook()
    sheet = book.create_sheet(title=u'对账记录', index=0)

    row = 1

    for dealerId, assList in ass_dict.iteritems():
        sheet.cell(row, 1).value = value_dict[dealerId]
        sheet.cell(row, 2).value = dealerId

        row += 1

        for itemId in assList:
            if itemId != dealerId:
                sheet.cell(row, 1).value = value_dict[itemId]
                sheet.cell(row, 2).value = itemId

            row += 1

        sheet.cell(row, 1).value = ""
        sheet.cell(row, 2).value = ""

        row += 1

    book.save('all_data_done.xlsx')


def merge(dealerId, ass_list, ass_dict, merged, reverse_dict, merge_dict, flag):
    if dealerId in ['5b5800328732d6282c3e13a6', '5ab8ce248732d645900f5e8f']:
        pass

    merged.extend(ass_list)

    for ass_dealer_id in ass_list:
        ass_dealer_id = str(ass_dealer_id)

        if ass_dealer_id == dealerId:
            continue

        if ass_dealer_id in ass_dict:
            second_ass_list = ass_dict.pop(ass_dealer_id)

            for itemId in second_ass_list:
                itemId = str(itemId)
                if itemId != dealerId:
                    reverse_dict[itemId] = dealerId

            reverse_dict[ass_dealer_id] = dealerId

            merged.append(ass_dealer_id)

            flag['flag'] = True

            merge(dealerId, second_ass_list, ass_dict, merged, reverse_dict, merge_dict, flag)
        elif ass_dealer_id in merge_dict:
            # if ass_dealer_id == '5ab8ce248732d645900f5e8f':
            #     pass

            merge_ass_list = merge_dict.pop(ass_dealer_id)
            merged.extend(merge_ass_list)

            for itemId in merge_ass_list:
                itemId = str(itemId)
                if itemId != dealerId:
                    reverse_dict[itemId] = dealerId

            reverse_dict[ass_dealer_id] = dealerId

            merged.append(ass_dealer_id)

            flag['flag'] = True

            merge(dealerId, merge_ass_list, ass_dict, merged, reverse_dict, merge_dict, flag)
        elif ass_dealer_id in reverse_dict:
            while True:
                reverse_dealer_id = reverse_dict.pop(ass_dealer_id)
                reverse_dealer_id = str(reverse_dealer_id)

                if reverse_dealer_id == dealerId:
                    reverse_dict[ass_dealer_id] = dealerId
                    ass_dealer_id = None
                    break
                else:
                    if reverse_dealer_id in reverse_dict:
                        merged.append(ass_dealer_id)
                        reverse_dict[ass_dealer_id] = dealerId
                        ass_dealer_id = reverse_dealer_id
                    else:
                        ass_dealer_id = reverse_dealer_id
                        break

            if not ass_dealer_id:
                continue

            if ass_dealer_id in ass_dict:
                second_ass_list = ass_dict.pop(ass_dealer_id)

                for itemId in second_ass_list:
                    itemId = str(itemId)
                    if itemId != dealerId:
                        reverse_dict[itemId] = dealerId

                reverse_dict[ass_dealer_id] = dealerId

                merged.append(ass_dealer_id)

                flag['flag'] = True

                merge(dealerId, second_ass_list, ass_dict, merged, reverse_dict, merge_dict, flag)
            elif ass_dealer_id in merge_dict:
                # if ass_dealer_id == '5ab8ce248732d645900f5e8f':
                #     pass

                merge_ass_list = merge_dict.pop(ass_dealer_id)

                merged.extend(merge_ass_list)
                merged.append(ass_dealer_id)

                for itemId in merge_ass_list:
                    itemId = str(itemId)
                    if itemId != dealerId:
                        reverse_dict[itemId] = dealerId

                reverse_dict[ass_dealer_id] = dealerId

                flag['flag'] = True

                merge(dealerId, merge_ass_list, ass_dict, merged, reverse_dict, merge_dict, flag)
            else:
                raise Exception('{} not find'.format(ass_dealer_id))
        else:
            raise Exception('{} not find'.format(ass_dealer_id))


def make_org_ass():
    all_data = read_xls('all_data.xlsx')

    # all_data = read_xls(u'E:\\code\\washpayer\\script\\transaction\\all_data.xlsx')

    rv = {}

    for dealerId in list(set(all_data.keys())):
        curr_data = all_data.get(dealerId)

        rv[dealerId] = [curr_data]

        partners = get_partner_ids(dealerId)

        for partner_id in partners:
            if partner_id == dealerId:
                print('{} = {}'.format(partner_id, dealerId))
                continue

            if partner_id not in all_data:
                print('{} no data.'.format(partner_id))
            else:
                partner_data = all_data.get(partner_id)
                rv[dealerId].append(partner_data)

        print rv[dealerId]

    with open('result.txt', 'w') as f:
        f.write(json.dumps(rv))

def do_medium():
    with open("medium.txt") as f:
        all_lines = f.readlines()

        import openpyxl

        book = openpyxl.Workbook()
        sheet = book.create_sheet(title=u'对账记录', index=0)

        row = 1

        for line in all_lines:
            if line:
                tokens = line.split(';')

                value = tokens[0]
                value = float(value.replace('diff is: ', '').strip())

                dealerId = tokens[1]
                dealerId = dealerId.replace(" id = ", '').strip()

                sheet.cell(row, 1).value = value
                sheet.cell(row, 2).value = dealerId

                row += 1

        book.save('all_data.xlsx')


if __name__ == '__main__':
    # do_medium()

    # make_org_ass()
    with open('result.txt', "r") as f:
        file_contents = f.read()
        rv = json.loads(file_contents)

        value_dict = {}
        ass_dict = {}

        for dealerId, asses in rv.iteritems():
            dealerId = str(dealerId)

            ass_dict[dealerId] = []

            for ass in asses:
                other_dealer_id = str(ass[1])

                if other_dealer_id == dealerId:
                    value_dict[dealerId] = ass[0]
                else:
                    ass_dict[dealerId].append(other_dealer_id)

        print ass_dict

        reverse_dict = {}

        while True:
            flag = {'flag': False}

            merge_dict = {}

            for dealerId in list(set(ass_dict.keys())):
                dealerId = str(dealerId)

                if dealerId not in ass_dict:
                    continue

                merged = []

                ass_list = ass_dict.pop(dealerId)

                merge(dealerId, ass_list, ass_dict, merged, reverse_dict, merge_dict, flag)

                merge_dict[dealerId] = list(set(merged))

            ass_dict = merge_dict

            if not flag['flag']:
                break

        print ass_dict

        write_execl(ass_dict, value_dict)