刷核销机制

# 读取Excel文件的第一列内容
import pandas as pd
from datetime import datetime
import random

# 读取 Excel 文件
df = pd.read_excel('法人客户核销机制单独刷入20241228.xlsx')

# 生成十八位随机纯数字字符串
def generate_random_number_string(length):
    # 生成随机数字字符串
    random_string = ''.join([str(random.randint(1, 9)) for _ in range(length)])
    return random_string

# 调用函数生成18位随机纯数字字符串
current_id = generate_random_number_string(18)

# 生成当前时间
current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

customers = []

with open("hexiaojizhi.sql", 'w', encoding='utf-8') as file:
    print("select * from `customer`.`corporate_customer_ou_bank_info` where corporate_customer_id in "
          "( select id from `customer`.`corporate_customer` WHERE deleted = 0 and code in (")
    for index, row in df.iterrows():
        customerCode = row['客户编码']
        ouCode = row['OU代码']
        verification_mechanism = row['核销机制']  # FIRST_IN_FIRST_OUT  VERIFY_BY_ORDER

        if customerCode in customers:
            continue
        else:
            corporate_customer_change_request_id = current_id

            # 生成插入 SQL 语句，并使用当前的唯一递增 ID
            insert_sql = f"""INSERT INTO `customer`.`corporate_customer_change_request` 
            (`id`, `corporate_customer_id`, `code`, `full_name`, `type`, `legal_representative_name`, 
            `legal_representative_identity_number`, `legal_representative_phone_number`, 
            `internal_customer`, `virtual_customer`, `certification_no`, `tax_identification_number`, 
            `india_pan_card_number`, `india_small_micro_enterprise_number`,
             `taxpayer_qualification`, `listed`, `cooperation_status`, `change_request_status`, `duns_number`,
              `country`, `country_id`, `registered_address`, `registered_capital`, `establishment_date`, 
              `business_start_date`, `business_end_date`, `business_scope`, `vat_number`, `eu_business_customer_classification`, 
              `remark`, `contacts`, `attachments`, `payment_banks`, `receiver_banks`, `associated`,
               `ous`, `payment_terms_id`, `bill_to_addresses`, `create_time`, `update_time`, `suggestion`, 
               `create_by`, `update_by`, `delete_time`, `deleted`, `invoice_address`, `invoice_bank`, `province`, 
               `province_id`, `change_request_type`, `quit_reason`, `related_business_customer_ids`, 
               `case_no`, `upload_attachments`, `business_line_type`) 
               VALUES ('{corporate_customer_change_request_id}', (SELECT id FROM `customer`.`corporate_customer` WHERE code = '{customerCode}' and deleted = 0), 
               '', '', 'STATE_OWNED', NULL, NULL, NULL, b'0', b'0', '', NULL, NULL, NULL, NULL, b'0', 'NORMAL', 'PASSED', NULL, '', '', '', 
               NULL, '{current_time}', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, b'0', '',
                NULL, NULL, '{current_time}', '{current_time}', '', 'W9090316', 'W9090316', NULL, b'0', NULL, 
                NULL, NULL, NULL, 'CREATE_OR_UPDATE', NULL, NULL, NULL, NULL, NULL);"""

            print("\"" + str(customerCode) + "\",")
            customers.append(customerCode)
            file.write(insert_sql + '\n')  # 将 SQL 语句写入文件，每条 SQL 语句换行

        # 生成插入 SQL 语句，并使用当前的唯一递增 ID
        insert_sql = f"""INSERT INTO `customer`.`corporate_customer_ou_bank_info`
               (`id`, `corporate_customer_id`, `corporate_customer_change_request_id`, `ou_id`, `ou_code`, `verification_mechanism`, `create_time`, `create_by`, `update_time`, `update_by`, `deleted`) 
               VALUES 
               ('{current_id}',  (SELECT id FROM `customer`.`corporate_customer` WHERE code = '{customerCode}' and deleted = 0), 
               '{corporate_customer_change_request_id}', (SELECT ou_id FROM `base`.`t_ou` WHERE ou_name = '{ouCode}'),
               '{ouCode}', '{verification_mechanism}', '{current_time}', 'xxxxx', '{current_time}', 'xxxxx', 0);"""

        current_id = str(int(current_id) + 1)


    print("\"\"));")