guarantee-admin-api-v0.2/Modules/Company/CompanyUtils.py

import json
import random

from datetime import datetime, timedelta

from DBHelper.MongoHelperInstance import DB_GUA
from Modules.Common.CommonUtils import CommonUtils


class CompanyUtils(object):

    # 生成新的企业ID，如果该ID存在，则重新生成
    @staticmethod
    def make_new_cid():
        new_cid = CommonUtils.random_code(8)
        case = DB_GUA.find_single_column(
            "管理端",
            "企业数据",
            {"企业ID": new_cid},
            "企业ID"
        ) is not None
        while case:
            new_cid = CommonUtils.random_code(8)
        return new_cid


class ExcelParserUtil(object):

    # 转换excel日期
    @staticmethod
    def trans_date_fmt_1(days):
        if type(days) == str:  # 特殊情况：比如某些高管信息出生日期只有年份，则返回年份
            return days
        else:
            try:
                delta = timedelta(days=days)  # 将1899-12-30转化为可以计算的时间格式并加上要转化的日期戳
                _date = datetime.strptime('1899/12/30', '%Y/%m/%d') + delta
                return datetime.strftime(_date, '%Y年%m月%d日')
            except TypeError:
                return '-'

    # 转换excel日期
    @staticmethod
    def trans_date_fmt_2(days):
        delta = timedelta(days=days)
        # 将1899-12-30转化为可以计算的时间格式并加上要转化的日期戳
        _date = datetime.strptime('1899/12/30', '%Y/%m/%d') + delta
        return datetime.strftime(_date, '%Y-%m-%d')

    # 转换excel日期
    @staticmethod
    def trans_date_fmt_3(_date):
        """"""

    # 删除列表中所有''
    @staticmethod
    def list_remove_blank(_list):
        while '' in _list:
            _list.remove('')
        return _list

    # 列表中的''转换为None
    @staticmethod
    def list_to_none(_list):
        return list(map(lambda x: None if x == '' else x, _list))

    # 列表中的float类型数据都保留2位小数
    @staticmethod
    def list_decimal(_list):
        return list(map(lambda _v: round(_v, 2) if isinstance(_v, float) else _v, _list))

    # 列表中的float类型数据都转为为int类型
    @staticmethod
    def list_to_int(_list):
        return list(map(lambda _v: int(_v) if isinstance(_v, float) else _v, _list))


class ExcelSheetParser(object):

    def __init__(self, sheet):
        self._sheet = sheet

    # 解析企业信息
    def parse_sheet1(self):
        # 表头
        cols_tag = self._sheet.col_values(1)
        cols_tag.pop(0)
        cols_tag = [x.strip() for x in cols_tag]
        # 表值
        cols_val = self._sheet.col_values(2)
        cols_val.pop(0)
        # 拼接
        data = json.loads(json.dumps(dict(zip(cols_tag, cols_val))))
        return data

    # 解析资产负债表
    def parse_sheet2(self):
        return_data = []
        for i in range(2, 6):
            insert_dict = dict()
            insert_dict['报告期'] = self._sheet.col_values(i)[1]

            current_assets_tag = self._sheet.col_values(1)[3:20]
            current_assets = ExcelParserUtil.list_to_none(self._sheet.col_values(i)[3:20])
            insert_dict['流动资产'] = json.loads(json.dumps(dict(zip(current_assets_tag, current_assets))))

            non_current_assets_tag = self._sheet.col_values(1)[21:39]
            non_current_assets = ExcelParserUtil.list_to_none(self._sheet.col_values(i)[21:39])
            insert_dict['非流动资产'] = json.loads(json.dumps(dict(zip(non_current_assets_tag, non_current_assets))))

            current_liabilitiess_tag = self._sheet.col_values(1)[40:54]
            current_liabilities = ExcelParserUtil.list_to_none(self._sheet.col_values(i)[40:54])
            insert_dict['流动负债'] = json.loads(json.dumps(dict(zip(current_liabilitiess_tag, current_liabilities))))

            non_current_liabilities_tag = self._sheet.col_values(1)[55:68]
            non_current_liabilities =  ExcelParserUtil.list_to_none(self._sheet.col_values(i)[55:68])
            insert_dict['非流动负债'] = json.loads(json.dumps(dict(zip(non_current_liabilities_tag, non_current_liabilities))))

            owners_equity_tag = self._sheet.col_values(1)[69:]
            owners_equity = ExcelParserUtil.list_to_none(self._sheet.col_values(i)[69:])
            insert_dict['所有者权益'] = json.loads(json.dumps(dict(zip(owners_equity_tag, owners_equity))))

            return_data.append(insert_dict)
        return return_data

    # 解析利润表
    def parse_sheet3(self):
        cols_tag = list(map(lambda x: x.strip().replace('减：', '').replace('加：', ''), self._sheet.col_values(1)))
        cols_tag = cols_tag[2:]
        return_data = []
        for i in range(2, 6):
            list_0 = ExcelParserUtil.list_to_none(self._sheet.col_values(i)[2:])
            list_1 = ExcelParserUtil.list_decimal(list_0)
            data = json.loads(json.dumps(dict(zip(cols_tag, list_1))))
            data['报告期'] = self._sheet.col_values(i)[1]
            return_data.append(data)
        return return_data

    # 解析补充数据表
    def parse_sheet4(self):
        cols_tag = self._sheet.col_values(1)
        cols_tag = cols_tag[2:]
        return_data = []
        for i in range(2, 6):
            list_0 = ExcelParserUtil.list_to_none(self._sheet.col_values(i)[2:])
            list_1 = ExcelParserUtil.list_decimal(list_0)
            data = json.loads(json.dumps(dict(zip(cols_tag, list_1))))
            data['报告期'] = self._sheet.col_values(i)[1]
            return_data.append(data)
        return return_data

    # 解析担保信息
    def parse_sheet5(self):
        # 表头
        rows_tag = self._sheet.row_values(1)
        rows_tag.pop(0)
        length = len(self._sheet.col_values(1)[2:])
        return_data = []
        for i in range(length):
            row_val = self._sheet.row_values(i+2)
            row_val.pop(0)
            data = json.loads(json.dumps(dict(zip(rows_tag, row_val))))
            return_data.append(data)
        return return_data

    # 解析代偿信息
    def parse_sheet6(self):
        # 表头
        rows_tag = self._sheet.row_values(1)
        rows_tag.pop(0)
        length = len(self._sheet.col_values(1)[2:])
        return_data = []
        for i in range(length):
            row_val = self._sheet.row_values(i + 2)
            row_val.pop(0)
            data = json.loads(json.dumps(dict(zip(rows_tag, row_val))))
            return_data.append(data)
        return return_data

    # 解析银行授信
    def parse_sheet7(self):
        # 表头
        rows_tag = self._sheet.row_values(1)
        rows_tag.pop(0)
        length = len(self._sheet.col_values(1)[2:])
        return_data = []
        for i in range(length):
            row_val = self._sheet.row_values(i + 2)
            row_val.pop(0)
            data = json.loads(json.dumps(dict(zip(rows_tag[:4], row_val[:4]))))
            return_data.append(data)
        return return_data