def __init__(self, interface_code, data_dict, secretkey_value):
     try:
         # 生成时间戳
         self.__time_stamp = TimeStamp.time_stamp()
         self.__data_dict = data_dict
         self.__secretkey_value = secretkey_value
         self.__interface_code = interface_code
     except Exception as e:
         logger.error(e)
         logger.exception(u"捕获到错误如下:")
예제 #2
0
def test_finance_generation(tb_userInfo, tb_userAppInfo, card_no):
    userid = str(16) + TimeStamp.time_stamp() + str(int(uuid.uuid1()))[:10]
    member_data = get_member_data(card_no[0])
    wxin_data = get_wxin_data(card_no[0])
    ##############################################################
    # 从getdata.xlsx获取到所有的数据,以此传给相应的字段
    # 通过装饰器实现多个表传参取值
    # 实现自动向tb_user_info表插入数据
    ##############################################################

    card_phone = member_data[0][1]
    card_idno = generator_const(0 - random.random() * (10955 - 10225) - 10225)
    open_id = member_data[0][3]
    wx_nickname = wxin_data[0][0]
    create_time = TimeStamp.time_stamp()
    update_time = TimeStamp.time_stamp()
    integral = tb_userInfo[0]
    delete_flag = tb_userInfo[1]
    finance_com_id = tb_userInfo[2]
    ###################################################################
    # tb_user_appl_info的相应字段
    ###################################################################
    company_detail_address = tb_userAppInfo[0]
    bank_card_name = tb_userAppInfo[1]
    bank_card_no = tb_userAppInfo[2]
    contact_name = tb_userAppInfo[3]
    contact_mobile = tb_userAppInfo[4]
    contact_relation = tb_userAppInfo[5]
    delete_flag = tb_userAppInfo[6]
    pic_front = tb_userAppInfo[7]
    pic_compress_front = tb_userAppInfo[8]
    pic_back = tb_userAppInfo[9]
    pic_compress_back = tb_userAppInfo[10]
    pic_full = tb_userAppInfo[11]
    pic_compress_full = tb_userAppInfo[12]
    province = tb_userAppInfo[13]
    city = tb_userAppInfo[14]
    area = tb_userAppInfo[15]
    company_name = tb_userAppInfo[16]
    contact_address_type = tb_userAppInfo[17]
    user_education = tb_userAppInfo[18]
    marital_status = tb_userAppInfo[19]
    company_size = tb_userAppInfo[20]
    census_reg_address = tb_userAppInfo[21]
    month_income = tb_userAppInfo[22]
    company_work_years = tb_userAppInfo[23]
    card_name = tb_userAppInfo[24]

    mysql_object_financecenter = MysqlHandler(
        mysql_user=Financecenter.MYSQL_USER.value,
        mysql_host=Financecenter.MYSQL_HOST.value,
        mysql_password=Financecenter.MYSQL_PASSWORD.value,
        mysql_db=Financecenter.MYSQL_DB.value)
    tb_user_info_dict = dict()
    tb_user_info_dict['user_id'] = userid
    tb_user_info_dict['user_name'] = card_name
    tb_user_info_dict['user_mobile'] = card_phone
    tb_user_info_dict['identity_card_no'] = card_idno
    tb_user_info_dict['weixin_open_id'] = open_id
    tb_user_info_dict['weixin_nick'] = wx_nickname
    tb_user_info_dict['create_time'] = create_time
    tb_user_info_dict['update_time'] = update_time
    tb_user_info_dict['integral'] = integral
    tb_user_info_dict['delete_flag'] = delete_flag
    tb_user_info_dict['finance_com_id'] = finance_com_id
    tb_user_info_table = "tb_user_info"
    tb_user_info_sql = mysql_object_financecenter.insert_str_generation(
        tb_user_info_table, tb_user_info_dict).encode("utf-8")
    console.debug(tb_user_info_sql)
    try:
        mysql_object_financecenter.insert_mysql_data(tb_user_info_sql)
    except:
        console.exception("tb_user_info_sql插入错误!")

    #################################################################################################
    # 自动向tb_user_appl_info表插入数据
    #
    #
    #################################################################################################
    tb_user_appl_info_dict = dict()
    tb_user_appl_info_dict['user_appl_id'] = userid
    tb_user_appl_info_dict['user_id'] = tb_user_info_dict['user_id']
    tb_user_appl_info_dict['user_name'] = card_name
    tb_user_appl_info_dict['user_mobile'] = card_phone
    tb_user_appl_info_dict['identity_card_no'] = card_idno
    tb_user_appl_info_dict['company_detail_address'] = company_detail_address
    tb_user_appl_info_dict['bank_card_name'] = bank_card_name
    tb_user_appl_info_dict['bank_card_no'] = bank_card_no
    tb_user_appl_info_dict['contact_name'] = contact_name
    tb_user_appl_info_dict['contact_mobile'] = contact_mobile
    tb_user_appl_info_dict['contact_relation'] = contact_relation
    tb_user_appl_info_dict['delete_flag'] = delete_flag
    tb_user_appl_info_dict['create_time'] = create_time
    tb_user_appl_info_dict['update_time'] = update_time
    tb_user_appl_info_dict['pic_front'] = pic_front
    tb_user_appl_info_dict['pic_compress_front'] = pic_compress_front
    tb_user_appl_info_dict['pic_back'] = pic_back
    tb_user_appl_info_dict['pic_compress_back'] = pic_compress_back
    tb_user_appl_info_dict['pic_full'] = pic_full
    tb_user_appl_info_dict['pic_compress_full'] = pic_compress_full
    tb_user_appl_info_dict['province'] = province
    tb_user_appl_info_dict['city'] = city
    tb_user_appl_info_dict['area'] = area
    tb_user_appl_info_dict['company_name'] = company_name
    tb_user_appl_info_dict['contact_address_type'] = contact_address_type
    tb_user_appl_info_dict['user_education'] = user_education
    tb_user_appl_info_dict['marital_status'] = marital_status
    tb_user_appl_info_dict['company_size'] = company_size
    tb_user_appl_info_dict['census_reg_address'] = census_reg_address
    tb_user_appl_info_dict['month_income'] = month_income
    tb_user_appl_info_dict['company_work_years'] = company_work_years
    tb_user_appl_info_dict['bank_ref_mobile'] = card_phone
    tb_user_appl_info_table = "tb_user_appl_info"
    tb_user_appl_info_sql = mysql_object_financecenter.insert_str_generation(
        tb_user_appl_info_table, tb_user_appl_info_dict).encode("utf-8")
    console.debug(tb_user_appl_info_sql)
    mysql_object_financecenter.insert_mysql_data(tb_user_appl_info_sql)

    #########################################################################
    # 自动更新tb_cardno_openid表(其中只需要更新身份证号、userid)
    ########################################################################

    tb_cardno_openid_dict = dict()
    tb_cardno_openid_dict['id_no'] = card_idno
    tb_cardno_openid_dict['user_id'] = tb_user_info_dict['user_id']
    tb_cardno_openid_table = "tb_cardno_openid"
    condition_dict = {'card_no': card_no[0]}
    condition = MysqlHandler.condition_function(condition_dict)
    tb_cardno_openid_sql = mysql_object_financecenter.update_str_generation(
        tb_cardno_openid_table, tb_cardno_openid_dict,
        condition).encode("utf-8")
    console.debug(tb_cardno_openid_sql)
    mysql_object_financecenter.update_mysql_data(tb_cardno_openid_sql)
def test_finance_generation(tb_userInfo, tb_userAppInfo, card_no):
    userid = str(16) + TimeStamp.time_stamp() + str(int(uuid.uuid1()))[:10]
    member_data = get_member_data(card_no[0])
    wxin_data = get_wxin_data(card_no[0])
    ##############################################################
    # 从getdata.xlsx获取到所有的数据,以此传给相应的字段
    # 通过装饰器实现多个表传参取值
    # 实现自动向tb_user_info表插入数据
    ##############################################################

    card_phone = member_data[0][1]
    card_idno = generator_const(0 - random.random() * (10955 - 10225) - 10225)
    open_id = member_data[0][3]
    wx_nickname = wxin_data[0][0]
    create_time = TimeStamp.time_stamp()
    update_time = TimeStamp.time_stamp()
    integral = tb_userInfo[0]
    delete_flag = tb_userInfo[1]
    finance_com_id = tb_userInfo[2]
    ###################################################################
    # tb_user_appl_info的相应字段
    ###################################################################
    company_detail_address = tb_userAppInfo[0]
    bank_card_name = tb_userAppInfo[1]
    bank_card_no = tb_userAppInfo[2]
    contact_name = tb_userAppInfo[3]
    contact_mobile = tb_userAppInfo[4]
    contact_relation = tb_userAppInfo[5]
    delete_flag = tb_userAppInfo[6]
    pic_front = tb_userAppInfo[7]
    pic_compress_front = tb_userAppInfo[8]
    pic_back = tb_userAppInfo[9]
    pic_compress_back = tb_userAppInfo[10]
    pic_full = tb_userAppInfo[11]
    pic_compress_full = tb_userAppInfo[12]
    province = tb_userAppInfo[13]
    city = tb_userAppInfo[14]
    area = tb_userAppInfo[15]
    company_name = tb_userAppInfo[16]
    contact_address_type = tb_userAppInfo[17]
    user_education = tb_userAppInfo[18]
    marital_status = tb_userAppInfo[19]
    company_size = tb_userAppInfo[20]
    census_reg_address = tb_userAppInfo[21]
    month_income = tb_userAppInfo[22]
    company_work_years = tb_userAppInfo[23]
    card_name = tb_userAppInfo[24]

    mysql_object_financecenter = MysqlHandler(mysql_user=Financecenter.MYSQL_USER.value,
                                              mysql_host=Financecenter.MYSQL_HOST.value,
                                              mysql_password=Financecenter.MYSQL_PASSWORD.value,
                                              mysql_db=Financecenter.MYSQL_DB.value)
    tb_user_info_dict = dict()
    tb_user_info_dict['user_id'] = userid
    tb_user_info_dict['user_name'] = card_name
    tb_user_info_dict['user_mobile'] = card_phone
    tb_user_info_dict['identity_card_no'] = card_idno
    tb_user_info_dict['weixin_open_id'] = open_id
    tb_user_info_dict['weixin_nick'] = wx_nickname
    tb_user_info_dict['create_time'] = create_time
    tb_user_info_dict['update_time'] = update_time
    tb_user_info_dict['integral'] = integral
    tb_user_info_dict['delete_flag'] = delete_flag
    tb_user_info_dict['finance_com_id'] = finance_com_id
    tb_user_info_table = "tb_user_info"
    tb_user_info_sql = mysql_object_financecenter.insert_str_generation(tb_user_info_table, tb_user_info_dict).encode(
        "utf-8")
    console.debug(tb_user_info_sql)
    try:
        mysql_object_financecenter.insert_mysql_data(tb_user_info_sql)
    except:
        console.exception("tb_user_info_sql插入错误!")

    #################################################################################################
    # 自动向tb_user_appl_info表插入数据
    #
    #
    #################################################################################################
    tb_user_appl_info_dict = dict()
    tb_user_appl_info_dict['user_appl_id'] = userid
    tb_user_appl_info_dict['user_id'] = tb_user_info_dict['user_id']
    tb_user_appl_info_dict['user_name'] = card_name
    tb_user_appl_info_dict['user_mobile'] = card_phone
    tb_user_appl_info_dict['identity_card_no'] = card_idno
    tb_user_appl_info_dict['company_detail_address'] = company_detail_address
    tb_user_appl_info_dict['bank_card_name'] = bank_card_name
    tb_user_appl_info_dict['bank_card_no'] = bank_card_no
    tb_user_appl_info_dict['contact_name'] = contact_name
    tb_user_appl_info_dict['contact_mobile'] = contact_mobile
    tb_user_appl_info_dict['contact_relation'] = contact_relation
    tb_user_appl_info_dict['delete_flag'] = delete_flag
    tb_user_appl_info_dict['create_time'] = create_time
    tb_user_appl_info_dict['update_time'] = update_time
    tb_user_appl_info_dict['pic_front'] = pic_front
    tb_user_appl_info_dict['pic_compress_front'] = pic_compress_front
    tb_user_appl_info_dict['pic_back'] = pic_back
    tb_user_appl_info_dict['pic_compress_back'] = pic_compress_back
    tb_user_appl_info_dict['pic_full'] = pic_full
    tb_user_appl_info_dict['pic_compress_full'] = pic_compress_full
    tb_user_appl_info_dict['province'] = province
    tb_user_appl_info_dict['city'] = city
    tb_user_appl_info_dict['area'] = area
    tb_user_appl_info_dict['company_name'] = company_name
    tb_user_appl_info_dict['contact_address_type'] = contact_address_type
    tb_user_appl_info_dict['user_education'] = user_education
    tb_user_appl_info_dict['marital_status'] = marital_status
    tb_user_appl_info_dict['company_size'] = company_size
    tb_user_appl_info_dict['census_reg_address'] = census_reg_address
    tb_user_appl_info_dict['month_income'] = month_income
    tb_user_appl_info_dict['company_work_years'] = company_work_years
    tb_user_appl_info_dict['bank_ref_mobile'] = card_phone
    tb_user_appl_info_table = "tb_user_appl_info"
    tb_user_appl_info_sql = mysql_object_financecenter.insert_str_generation(tb_user_appl_info_table,
                                                                             tb_user_appl_info_dict).encode("utf-8")
    console.debug(tb_user_appl_info_sql)
    mysql_object_financecenter.insert_mysql_data(tb_user_appl_info_sql)

    #########################################################################
    # 自动更新tb_cardno_openid表(其中只需要更新身份证号、userid)
    ########################################################################

    tb_cardno_openid_dict = dict()
    tb_cardno_openid_dict['id_no'] = card_idno
    tb_cardno_openid_dict['user_id'] = tb_user_info_dict['user_id']
    tb_cardno_openid_table = "tb_cardno_openid"
    condition_dict = {'card_no': card_no[0]}
    condition = MysqlHandler.condition_function(condition_dict)
    tb_cardno_openid_sql = mysql_object_financecenter.update_str_generation(tb_cardno_openid_table,
                                                                            tb_cardno_openid_dict, condition).encode(
        "utf-8")
    console.debug(tb_cardno_openid_sql)
    mysql_object_financecenter.update_mysql_data(tb_cardno_openid_sql)
    def output_with_excel(self, template_file, sheet_count, data_list):
        '''
        :param template_file: 模板文件路径
        :param sheet_count: 模板文件容sheet的个数,比如5个sheet页通过这个参数可以指定就用3个
        :param data_list: 数据list,其中包含sheet页list,表头list,数据list
        本方法中均使用list进行数据操作,由于模板中第一行为标题,故插入数据的行数索引从1开始
        '''
        try:
            data = xlrd.open_workbook(template_file)
            sheet_name = data.sheet_names()
            sheet_list = list()
            # 将模板文件sheet对象存入sheet_list列表
            for i in range(sheet_count):
                sheet_list.append(data.sheets()[i])
            template_data_list = list()
            # 将模板文件每个sheet的数据list存入tempLate_data_list列表
            for i_sheet in sheet_list:
                template_data_list.append(i_sheet.row_values(0))
            output_file = xlwt.Workbook()
            # 字体设定
            font = xlwt.Font()
            font.bold = True
            # 边框设定
            borders = xlwt.Borders()
            borders.left = 1
            borders.right = 1
            borders.top = 1
            borders.bottom = 1
            # 背景色设定
            backcolor = xlwt.Pattern()
            backcolor.pattern = xlwt.Pattern.SOLID_PATTERN
            backcolor.pattern_fore_colour = 5
            style = xlwt.XFStyle()
            style.font = font
            style.borders = borders
            style.pattern = backcolor
            output_file_sheet_list = list()
            # 输出文件添加sheet并给予标题,对象存入output_file_sheet_list列表
            for j in range(sheet_count):
                output_file_sheet_list.append(output_file.add_sheet(sheet_name[j], cell_overwrite_ok=True))
            # j_sheet_num为sheet页的索引
            for j_sheet_num in range(len(sheet_list)):
                # j_sheet_row_index为模板文件template_dataList的索引
                for j_sheet_row_index in range(len(template_data_list[j_sheet_num])):
                    output_file_sheet_list[j_sheet_num].write(0, j_sheet_row_index,
                                                              template_data_list[j_sheet_num][j_sheet_row_index], style)

            # 数据格式为{"sheet页数":{"列数":[当前添加的数据1,当前添加的数据2]}}
            filter_dict = dict()
            logger.debug("++++++++++++++++++++++++++++++")
            logger.debug(data_list)
            # 以追加方式添加数据
            for data_num in range(len(data_list[0])):
                logger.debug(data_num)
                logger.debug(data_list)
                # 当前sheet的索引
                data_sheet_num = int(data_list[0][data_num]) - 1
                data_row_num = 1
                data_col_num = int(data_list[1][data_num]) - 1
                logger.debug("===================================")
                logger.debug(data_sheet_num)
                logger.debug(data_col_num)
                # 判断当前sheet页数是否在filter_dict中,即当前sheet是否曾经出现过
                if str(data_sheet_num) not in filter_dict:
                    # 当前sheet页数不存在filter_dict,添加对应data_sheet_num字典
                    filter_dict[str(data_sheet_num)] = {}
                    # 增加对应列数的数据list
                    filter_dict[str(data_sheet_num)][str(data_col_num)] = list()
                    # 由于sheet页数第一次出现,故数据也一定第一次出现,所以之列列表中添加值
                    filter_dict[str(data_sheet_num)][str(data_col_num)].append(data_col_num)
                else:
                    # 当前sheet页数存在filter_dict中,判断当前列数是否在列数字典中,即当前列数是否出现过
                    if str(data_col_num) not in filter_dict[str(data_sheet_num)]:
                        # 当前列数未出现过,添加列表
                        filter_dict[str(data_sheet_num)][str(data_col_num)] = list()
                        # 向列数字典列表插数据,但插入数据的当前行数为初始值,即1
                        filter_dict[str(data_sheet_num)][str(data_col_num)].append(data_col_num)
                    else:
                        # 当前列数出现过。向列数字典列表插数据,插入数据的当前行数为总出现的次数
                        filter_dict[str(data_sheet_num)][str(data_col_num)].append(data_col_num)
                        data_row_num = len(filter_dict[str(data_sheet_num)][str(data_col_num)])
                logger.debug(filter_dict)
                data_preview_add = data_list[2][data_num]
                # 向excel中插入数据
                output_file_sheet_list[data_sheet_num].write(data_row_num, data_col_num, data_preview_add)

            output_file.save(str(TimeStamp.time_stamp()) + ".xls")


        except:
            logger.exception("发现错误")