def make_sql(country_code, rg_name, cl_bussniss_type, n):
path = '../../Data_sql/上海医保/'
isExists = os.path.exists(path)
if not isExists:
# 如果不存在则创建目录
os.mkdir(path)
file_name = path + 'smcs_cash' + cl_bussniss_type + str(
otherStyleTime3) + '.sql'
# file_name1 = path + 'smcs_cash_detail'+str(otherStyleTime3)+'.sql'
# file_name2 = path + 'smcs_bank_account'+str(otherStyleTime3)+'.sql'
smcs_cash_file = open(file_name, 'w', encoding='utf-8')
# smcs_cash_detail_file = open(file_name1, 'w', encoding='utf-8')
# smcs_bank_account_file = open(file_name2, 'w', encoding='utf-8')
# 主表sql
str_head_zhu = "insert into smcs_cash values ("
str_head_mingxi = "insert into smcs_cash_detail values ("
str_head_bankaccount = "insert into smcs_bank_account values ("
no = 1
while (n > 0):
# 生成小写字母加数字30位
ID = ''.join(random.sample(string.ascii_lowercase + string.digits, 30))
ID2 = ''.join(random.sample(string.ascii_lowercase + string.digits,
30))
ID3 = ''.join(random.sample(string.ascii_lowercase + string.digits,
30))
CREATE_BY = ran_strx(
"赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤",
2)
CREATE_DATE = datetime.datetime.fromisoformat(
'2021-01-02 19:00:00').strftime("%Y-%m-%d %H:%M:%S")
LAST_MODIFIED_BY = ran_strx(
"赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤",
3)
LAST_MODIFIED_DATE = otherStyleTime
LAST_MODIFIED_VERSION = '1'
MESSAGE_TYPE = '1'
# APPLY_NO = ''.join(random.sample(string.ascii_lowercase + string.digits, 20))
APPLY_SUB_NO = '01'
APPLY_SUB_NO2 = '02'
# BILL_NO = ''.join(random.sample(string.ascii_lowercase + string.digits, 16))
COMMON_ACCOUNT = ran_strx("0123456789", 8)
APPLY_USER_NAME = '测试李'
APPLY_USER_CARD_ID = shenfenzhenghao.outIDcard()
MEDICAL_CARD = ran_strx("0123456789", 10)
CURRENT_YEAR_PAY = 100
ALL_YEAR_PAY = 200
PLAN_PAY = 300
ADDITIONAL_PAY = 400
OTHER_PAY = 500
REFUND_AMOUNT = round((CURRENT_YEAR_PAY + ALL_YEAR_PAY + PLAN_PAY +
ADDITIONAL_PAY + OTHER_PAY), 2)
BUSSINESS_TYPE = cl_bussniss_type
if BUSSINESS_TYPE == '03' or BUSSINESS_TYPE == '13':
MAIL_ADDRESS = '北京市海淀区用友路1号'
MAIL_ADDRESS_CODE = ran_strx("0123456789", 6)
else:
MAIL_ADDRESS = ''
MAIL_ADDRESS_CODE = ''
banktype = int(random.uniform(0, cl_banktype.__len__()))
BANK_TYPE = cl_banktype[banktype]
if BANK_TYPE == '01':
# 代办
BANK_CODE = '325290000012'
BANK_CARD_ACCOUNT = '300004259710027'
# AGENT_USER_NAME = '委托待办人''测试李'
AGENT_USER_NAME = '测试李'
else:
BANK_CODE = ran_strx("0123456789", 12)
BANK_CARD_ACCOUNT = ran_strx("0123456789", 20)
AGENT_USER_NAME = ''
#支付类型为单位集中时单位编码才有值
if BUSSINESS_TYPE == '04':
# UNIT_CODE = '206003001'
UNIT_CODE = '425054223'
UNIT_NAME = '静安区三级预付单位1'
else:
UNIT_CODE = ''
UNIT_NAME = ''
ACCEPT_ACCOUNT_NAME = ''
ACCEPT_ACCOUNT = ''
AEECPT_BANK_NAME = ''
BILL_CREATER = ''
BILL_CREATE_TIME = ''
BILL_CREATE_AREA = ''
VOUCHER_TYPE = ''
VOUCHER_STATUS = ''
VOUVHER_CREATE_TIME = ''
BILL_STATUS = ''
RES1 = ''
RES2 = ''
RES3 = ''
RES4 = ''
COUNTRY_CODE = country_code
REFUND_CATEGORY = ''
ACCOUNT_STATE = '00'
BILL_STATE = '00'
BILL_DATE = ''
HANDWORK_FLAG = ''
NOTE_NUM = ''
FAIL_REASON = ''
FUND_BATCH_NUM = ''
EXCHANGE_NUM = '123456'
REFUND_STATE = ''
WORK_FLOW_STATE = '1'
TASK_ID = ''
NODE_ID = ''
PROC_DEF_ID = ''
PROC_INST_ID = ''
CHANGE_STATUS = ''
VOU_GUID = ''
VOU_NO = ''
RG_NAME = rg_name
SERVICE_NODE_NAME = ''
SERVICE_NODE_CODE = ''
PAY_TYPE = ''
# bussniss_type 与 pay_type 对应关系
if (BUSSINESS_TYPE == '01' or BUSSINESS_TYPE == '16'):
PAY_TYPE = 1
if (BUSSINESS_TYPE == '02' or BUSSINESS_TYPE == '10'
or BUSSINESS_TYPE == '12' or BUSSINESS_TYPE == '15'):
PAY_TYPE = 2
if (BUSSINESS_TYPE == '03' or BUSSINESS_TYPE == '13'):
PAY_TYPE = 3
if (BUSSINESS_TYPE == '04'):
PAY_TYPE = 4
# 业务单据类型为10 转移接续支付时，以下三个字段才有值
ORI_ACCEPT_ACCOUNT_NAME = ''
ORI_ACCEPT_ACCOUNT = ''
ORI_ACCEPT_BANK_NAME = ''
bank_name = ['上海银行', '邮储银行', '招商银行']
if BUSSINESS_TYPE == '10':
ORI_ACCEPT_ACCOUNT_NAME = ran_strx(
"赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤",
2)
ORI_ACCEPT_ACCOUNT = ran_strx("0123456789", 32)
ORI_ACCEPT_BANK_NAME = bank_name[int(random.uniform(0, 2))]
ACCEPT_ACCOUNT_NAME = ran_strx(
"赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤",
2)
ACCEPT_ACCOUNT = ran_strx("0123456789", 32)
AEECPT_BANK_NAME = bank_name[int(random.uniform(0, 2))]
INSURANCE_TYPE = '000'
INSURANCE_NAME = '零星报销'
COMMIT_DATE = ''
PAY_TIME = ''
ADD_PAGE_NO = ''
CENTER_NAME = rg_name
CENTER_CODE = country_code
AREA_CODE = ''
area_code = [
'28', '30', '33', '34', '37', '39', '31', '43', '35', '38', '36',
'40', '41', '42', '32'
]
if BUSSINESS_TYPE == '12':
BILL_STATUS = ''
WORK_FLOW_STATE = '1'
BILL_DATE = ''
BILL_CREATE_TIME = ''
if COUNTRY_CODE == '00':
AREA_CODE = area_code[int(random.uniform(0, 1))]
elif COUNTRY_CODE == '06':
AREA_CODE = area_code[int(random.uniform(2, 5))]
elif COUNTRY_CODE == '01':
AREA_CODE = area_code[int(random.uniform(6, 6))]
elif COUNTRY_CODE == '12':
AREA_CODE = area_code[int(random.uniform(7, 7))]
elif COUNTRY_CODE == '14':
AREA_CODE = area_code[int(random.uniform(8, 8))]
elif COUNTRY_CODE == '17':
AREA_CODE = area_code[int(random.uniform(9, 9))]
elif COUNTRY_CODE == '10':
AREA_CODE = area_code[int(random.uniform(10, 10))]
elif COUNTRY_CODE == '22':
AREA_CODE = area_code[int(random.uniform(11, 13))]
else:
AREA_CODE = area_code[int(random.uniform(14, 14))] #虹口
elif BUSSINESS_TYPE == '15' or BUSSINESS_TYPE == '16':
BILL_STATUS = ''
WORK_FLOW_STATE = '1'
BILL_DATE = '201124'
BILL_CREATE_TIME = otherStyleTime
FAIL_TIMES = '0'
EXCHANGE_PLAN_STATUS = ''
EXCHANGE_PLAN_ID = ''
AUDIT_STATUS = '00'
NEW_DATE = ''
SEQ_NO = ''
APPLY_NO = 'QX' + country_code + BUSSINESS_TYPE + otherStyleTime3 + str(
n)
# APPLY_NO = country_code + BUSSINESS_TYPE +'0717'+ str(n)
# APPLY_NO = 'QX'+country_code + BUSSINESS_TYPE + str(int(time.time()))
BILL_NO = APPLY_NO + APPLY_SUB_NO
BILL_NO2 = APPLY_NO + APPLY_SUB_NO2
BILL_CREATE_NAME = ''
BILL_CREATE_DATE = ''
AUDITOR = ''
AUDIT_DATE = ''
# 明细表数据
MESSAGE_TYPE1 = '2'
SERAL_NO = ran_strx("0123456789", 13)
# bill_no与主表保持一致
BILL_NO1 = BILL_NO
PAY_TARGET = cl_pay_target[int(random.uniform(0, 7))]
HOSPITAL_LV = cl_hospital_lv[int(random.uniform(0, 7))]
MEDICAL_TYPE = cl_medical_type[int(random.uniform(0, 6))]
if BUSSINESS_TYPE == '10':
SETTLE_TYPE = '10801'
elif BUSSINESS_TYPE == '04':
SETTLE_TYPE = '70101'
elif BUSSINESS_TYPE == '15':
SETTLE_TYPE = '50001'
elif BUSSINESS_TYPE == '16':
SETTLE_TYPE = '52114'
else:
SETTLE_TYPE = BUSSINESS_TYPE
CURRENT_YEAR_AMOUNT1 = CURRENT_YEAR_PAY
ALL_YEAR_AMOUNT1 = ALL_YEAR_PAY
PLAN_PAY1 = PLAN_PAY
ADDITIONAL_PAY1 = ADDITIONAL_PAY
OTHER_PAY1 = OTHER_PAY
SELF_AMOUNT = round(random.uniform(100, 10), 2)
REFUND_AMOUNT1 = REFUND_AMOUNT
TOTAL_AMOUNT = round((SELF_AMOUNT + REFUND_AMOUNT1), 2)
HAND_WORK_DATE = ''
CHANGE_DATE = ''
BACKOUT_STATE = ''
CONFIRM_VOU_DATE = ''
CONFIRM_VOU_NO = ''
CONFIRM_VOU_GUID = ''
COMMIT_VOU_DATE = ''
COMMIT_VOU_NO = ''
COMMIT_VOU_GUID = ''
PAY_VOU_DATE = ''
PAY_VOU_NO = ''
PAY_VOU_GUID = ''
EXPORT_DATE = ''
NOTE_TYPE = ''
# FAIL_VOU_DATE = ''
# FAIL_VOU_NO = ''
# FAIL_VOU_GUID = ''
IMPORT_DATE = ''
line = str_head_zhu + '"' + ID + '", "' + CREATE_BY + '", ' + 'TO_TIMESTAMP(\'' + CREATE_DATE + '\',\'SYYYY-MM-DD HH24:MI:SS:FF6\'), ' + '"' + LAST_MODIFIED_BY + '", TO_TIMESTAMP(\'' + LAST_MODIFIED_DATE + '\',\'SYYYY-MM-DD HH24:MI:SS:FF6\'), "' + \
LAST_MODIFIED_VERSION + '", "' + MESSAGE_TYPE + '", "' + APPLY_NO + '", "' + APPLY_SUB_NO + '", "' + BILL_NO + '", "' + COMMON_ACCOUNT + '", "' + \
APPLY_USER_NAME + '", "' + APPLY_USER_CARD_ID + '", "' + MEDICAL_CARD + '", "' + str(REFUND_AMOUNT) + '", "' + str(CURRENT_YEAR_PAY) + '", "' + str(ALL_YEAR_PAY) + '", "' + \
str(PLAN_PAY) + '", "' + str(ADDITIONAL_PAY) + '", "' + str(OTHER_PAY) + '", "' + str(PAY_TYPE) + '", "' + MAIL_ADDRESS + '", "' + MAIL_ADDRESS_CODE + '", "' + BANK_TYPE + '", "' + \
BANK_CODE + '", "' + BANK_CARD_ACCOUNT + '", "' + AGENT_USER_NAME + '", "' + UNIT_CODE + '", "' + UNIT_NAME + '", "' + ACCEPT_ACCOUNT_NAME + '", "' + \
ACCEPT_ACCOUNT + '", "' + AEECPT_BANK_NAME + '", "' + BILL_CREATER + '", '+ 'TO_TIMESTAMP(\'' + BILL_CREATE_TIME + '\',\'SYYYY-MM-DD HH24:MI:SS:FF6\'),"' + BILL_CREATE_AREA + '", "' + VOUCHER_TYPE + '", "' + \
VOUCHER_STATUS + '", "' + VOUVHER_CREATE_TIME + '", "' + BILL_STATUS + '", "' + RES1 + '", "' + RES2 + '", "' + RES3 + '", "' + RES4 + '", "' + COUNTRY_CODE + '", "' + \
REFUND_CATEGORY + '", "' + ACCOUNT_STATE + '", "' + BILL_STATE + '", "' + BILL_DATE + '", "' + HANDWORK_FLAG + '", "' + NOTE_NUM + '", "' + FAIL_REASON + '", "' + \
FUND_BATCH_NUM + '", "' + ORI_ACCEPT_ACCOUNT_NAME + '", "' + ORI_ACCEPT_ACCOUNT + '", "' + ORI_ACCEPT_BANK_NAME + '", "' + EXCHANGE_NUM + '", "' + \
REFUND_STATE + '", "' + WORK_FLOW_STATE + '", "' + TASK_ID + '", "' + NODE_ID + '", "' + PROC_DEF_ID + '", "' + PROC_INST_ID + '", "' + CHANGE_STATUS + '", "' + \
VOU_GUID + '", "' + VOU_NO + '", "' + RG_NAME + '", "' + SERVICE_NODE_NAME + '", "' + SERVICE_NODE_CODE + '", "' + BUSSINESS_TYPE + '", "' + INSURANCE_TYPE + '", "' + \
INSURANCE_NAME + '", "' + COMMIT_DATE + '", "' + PAY_TIME + '", "' + ADD_PAGE_NO + '", "' + CENTER_NAME + '", "' + CENTER_CODE + '", "' + AREA_CODE + '", "' + \
FAIL_TIMES + '", "' + EXCHANGE_PLAN_STATUS + '", "' + EXCHANGE_PLAN_ID + '", "' + AUDIT_STATUS + '", "' + NEW_DATE + '", "' + SEQ_NO +'", "'+HAND_WORK_DATE +'", "'+\
CHANGE_DATE+'", "'+BACKOUT_STATE + '", "' + CONFIRM_VOU_DATE + '", "' + CONFIRM_VOU_NO + '", "' + CONFIRM_VOU_GUID + '", "' + COMMIT_VOU_DATE + '", "' + COMMIT_VOU_NO + '", "' + \
COMMIT_VOU_GUID + '", "' + PAY_VOU_DATE + '", "' + PAY_VOU_NO + '", "' + PAY_VOU_GUID + '", "' + EXPORT_DATE + '", "' + NOTE_TYPE + '", "' + IMPORT_DATE + '", "' + BILL_CREATE_NAME + '", "' + BILL_CREATE_DATE + '", "' + AUDITOR + '", "' + AUDIT_DATE +'");'
line1 = str_head_mingxi + '"' + ID + '", "' + CREATE_BY + '", ' + 'TO_TIMESTAMP(\'' + CREATE_DATE + '\',\'SYYYY-MM-DD HH24:MI:SS:FF6\'),' + ' "' + LAST_MODIFIED_BY + '"' + \
', TO_TIMESTAMP(\'' + LAST_MODIFIED_DATE + '\',\'SYYYY-MM-DD HH24:MI:SS:FF6\'), ' + '"' + LAST_MODIFIED_VERSION + '", "' + MESSAGE_TYPE1 + '", "' + SERAL_NO + '", "' + \
BILL_NO1 + '", "' + PAY_TARGET + '", "' + HOSPITAL_LV + '", "' + MEDICAL_TYPE + '", "' + SETTLE_TYPE + '", "' + \
str(TOTAL_AMOUNT) + '", "' + str(SELF_AMOUNT) + '", "' + str(REFUND_AMOUNT1) + '", "' + str(CURRENT_YEAR_AMOUNT1) + '", "' + \
str(ALL_YEAR_AMOUNT1) + '", "' + str(PLAN_PAY1) + '", "' + str(ADDITIONAL_PAY1) + '", "' + str(OTHER_PAY1) + '", "' + EXCHANGE_PLAN_ID + \
'", "' + EXCHANGE_PLAN_STATUS + '");'
#AREA_CODE = '101'
# CENTER_CODE = '黄浦区'
SERVER_NODE_CODE = ''
if BANK_TYPE == '00':
ACCOUNT_NO = ran_strx("0123456789", 32)
BANK_CODE1 = ran_strx("0123456789", 12)
else:
ACCOUNT_NO = BANK_CARD_ACCOUNT
BANK_CODE1 = BANK_CODE
# BANK_CODE = '5042'
ACCOUNT_NAME = APPLY_USER_NAME
BANK_NODE_NAME = '昌平回龙观支行'
IMPORT_DATE = otherStyleTime2
USER_CARD_NO = APPLY_USER_CARD_ID
BANK_NAME = '上海银行'
EXCHANGE_PLAN_STATUS = ''
EXCHANGE_PLAN_ID = ''
RES1 = ''
RES2 = ''
USER_CARD_TYPE = '1'
line2 = str_head_bankaccount + '"' + ID + '", "' + CREATE_BY + '", ' + 'TO_TIMESTAMP(\'' + CREATE_DATE + '\',\'SYYYY-MM-DD HH24:MI:SS:FF6\'),' + ' "' + LAST_MODIFIED_BY + '"' + \
', TO_TIMESTAMP(\'' + LAST_MODIFIED_DATE + '\',\'SYYYY-MM-DD HH24:MI:SS:FF6\'), ' + '"' + LAST_MODIFIED_VERSION + '", "' + AREA_CODE + '", "' + CENTER_CODE + '", "' + \
SERVER_NODE_CODE + '", "' + ACCOUNT_NO + '", "' + BANK_CODE1 + '", "' + ACCOUNT_NAME + '", "' + BANK_NODE_NAME + '", "' + \
IMPORT_DATE + '", "' + USER_CARD_NO + '", "' + BANK_NAME + '", "' + EXCHANGE_PLAN_STATUS + '", "' + EXCHANGE_PLAN_ID + '", "' + RES1 + '", "' + RES2 + '", "' + USER_CARD_TYPE + '");'
line = line.replace('\"', '\'')
line1 = line1.replace('\"', '\'')
line2 = line2.replace('\"', '\'')
no += 1
n -= 1
smcs_cash_file.write(line + '\n')
smcs_cash_file.write(line1 + '\n')
# smcs_cash_file.write(line2 + '\n')
if n % 2 != 0:
smcs_cash_file.write('commit;' + '\n')
smcs_cash_file.write('commit;' + '\n')
def make_sql(agency_no, ins_no, n):
path = '../../Data_sql/零星报销/'
isExists = os.path.exists(path)
if not isExists:
# 如果不存在则创建目录
os.mkdir(path)
file_name = path + 'MID_CASH' + str(otherStyleTime3) + '.sql'
# file_name_item = path + 'MID_PAY_PLAN_ITEM'+str(otherStyleTime3)+'.sql'
SMCS_CASH_file = open(file_name, 'w', encoding='utf-8')
# SMRTS_PAY_PLAN_ITEM_file = open(file_name_item, 'w', encoding='utf-8')
# 主子表sql
str_head_zhu = "insert into mid.MID_CASH values ("
str_head_item = "insert into mid.MID_CASH_ITEM values ("
no = 1
while (n > 0):
# 主表
BILL_NO = otherStyleTime4 + str(n)
BATCH_NO = 'YW' + otherStyleTime4 + str(n)
APPLY_USER_NAME = ran_strx(
"赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤",
2)
APPLY_USER_CARD_ID = shenfenzhenghao.outIDcard()
REFUND_AMOUNT = '100.00'
MEDICAL_CARD = ran_strx("0123456789", 10)
BANK_CODE = '001'
BANK_TYPE = '001'
BANK_ACCOUNT_NAME = ran_strx(
"赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤",
3)
BANK_CARD_ACCOUNT = ran_strx("0123456789", 16)
AREA_CODE = cl_country_code[agency_no]
AREA_NAME = cl_country_name[agency_no]
CENTER_CODE = cl_agency_code[agency_no]
CENTER_NAME = cl_agency_name[agency_no]
PAY_PERIOD = otherStyleTime5
# 现金-1 银行卡-0
num = int(random.uniform(0, 2))
# num = 0
BUSINESS_TYPE = cl_BUSINESS_TYPE[num]
PAY_TYPE = cl_PAY_TYPE[num]
STATUS = '0'
INSURANCE_CODE = cl_ins_code[ins_no]
INSURANCE_NAME = cl_ins_name[ins_no]
# PAY_STATUS = '0'
# 子表
# aa = ''
BATCH_NO_1 = BATCH_NO
BILL_NO_1 = BILL_NO
TOTAL_AMOUNT_1 = '110'
SETTLE_TYPE_1 = '1'
SELF_AMOUNT_1 = '160'
REFUND_AMOUNT_1 = '156'
MEDICAL_TYPE_1 = '2'
PAY_TARGET_1 = '2'
HOSPITAL_LV_1 = '2'
SETTLE_TYPE_NAME_1 = '费款项001'
bb = ''
BATCH_NO_2 = BATCH_NO
BILL_NO_2 = BILL_NO
TOTAL_AMOUNT_2 = '115'
SETTLE_TYPE_2 = '2'
SELF_AMOUNT_2 = '410'
REFUND_AMOUNT_2 = '137'
MEDICAL_TYPE_2 = '2'
PAY_TARGET_2 = '2'
HOSPITAL_LV_2 = '2'
SETTLE_TYPE_NAME_2 = '费款项002'
# line = str_head_zhu + '"' + BILL_NO + '", "' + BATCH_NO + '", "' + APPLY_USER_NAME + '", "' + APPLY_USER_CARD_ID\
# + '", "' + REFUND_AMOUNT + '", "' + MEDICAL_CARD + '", "' + BANK_CODE + '", "' + BANK_TYPE + '", "' + \
# BANK_ACCOUNT_NAME + '", "' + BANK_CARD_ACCOUNT + '", "' + AREA_CODE + '", "' + AREA_NAME + '", "' + \
# CENTER_CODE + '", "' + CENTER_NAME + '", "' + PAY_PERIOD + '", "' + BUSINESS_TYPE + '", "' + PAY_TYPE\
# + '", "' + STATUS + '", "' + INSURANCE_CODE + '", "' + INSURANCE_NAME + '", "' + PAY_STATUS + '");'
# line_item1 = str_head_item + '"' + aa + '", "' + BATCH_NO_1 + '", "' + BILL_NO_1 + '", "' + TOTAL_AMOUNT_1 + '", "' + SETTLE_TYPE_1 + '", "' + SELF_AMOUNT_1 + '", "' + REFUND_AMOUNT_1 + '", "' + MEDICAL_TYPE_1 + '", "' + PAY_TARGET_1 + '", "' + HOSPITAL_LV_1 + '", "' + SETTLE_TYPE_NAME_1 + '");'
# line_item2 = str_head_item + '"' + bb + '", "' + BATCH_NO_2 + '", "' + BILL_NO_2 + '", "' + TOTAL_AMOUNT_2 + '", "' + SETTLE_TYPE_2 + '", "' + SELF_AMOUNT_2 + '", "' + REFUND_AMOUNT_2 + '", "' + MEDICAL_TYPE_2 + '", "' + PAY_TARGET_2 + '", "' + HOSPITAL_LV_2 + '", "' + SETTLE_TYPE_NAME_2 + '");'
line = str_head_zhu + '"' + BILL_NO + '", "' + BATCH_NO + '", "' + APPLY_USER_NAME + '", "' + APPLY_USER_CARD_ID\
+ '", "' + REFUND_AMOUNT + '", "' + MEDICAL_CARD + '", "' + BANK_CODE + '", "' + BANK_TYPE + '", "' + \
BANK_ACCOUNT_NAME + '", "' + BANK_CARD_ACCOUNT + '", "' + AREA_CODE + '", "' + AREA_NAME + '", "' + \
CENTER_CODE + '", "' + CENTER_NAME + '", "' + PAY_PERIOD + '", "' + BUSINESS_TYPE + '", "' + PAY_TYPE\
+ '", "' + STATUS + '", "' + INSURANCE_CODE + '", "' + INSURANCE_NAME + '");'
line_item1 = str_head_item + '"' + BATCH_NO_1 + '", "' + BILL_NO_1 + '", "' + TOTAL_AMOUNT_1 + '", "' + SETTLE_TYPE_1 + '", "' + SELF_AMOUNT_1 + '", "' + REFUND_AMOUNT_1 + '", "' + MEDICAL_TYPE_1 + '", "' + PAY_TARGET_1 + '", "' + HOSPITAL_LV_1 + '", "' + SETTLE_TYPE_NAME_1 + '");'
line_item2 = str_head_item + '"' + BATCH_NO_2 + '", "' + BILL_NO_2 + '", "' + TOTAL_AMOUNT_2 + '", "' + SETTLE_TYPE_2 + '", "' + SELF_AMOUNT_2 + '", "' + REFUND_AMOUNT_2 + '", "' + MEDICAL_TYPE_2 + '", "' + PAY_TARGET_2 + '", "' + HOSPITAL_LV_2 + '", "' + SETTLE_TYPE_NAME_2 + '");'
line = line.replace('\"', '\'')
line_item1 = line_item1.replace('\"', '\'')
line_item2 = line_item2.replace('\"', '\'')
no += 1
n -= 1
SMCS_CASH_file.write(line + '\n')
SMCS_CASH_file.write(line_item1 + '\n')
SMCS_CASH_file.write(line_item2 + '\n')
def makeXmlFile(mainNum, detailNum, childNum):
data = ET.Element('Data') # 创建节点
tree = ET.ElementTree(data) # 创建文档
for i in range(mainNum):
Main = ET.Element('Main')
data.append(Main)
creater = ran_strx(
"赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤",
2)
moder = ran_strx(
"赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤",
2)
nowdate = otherStyleTime
yesdate = (datetime.datetime.now() +
datetime.timedelta(days=-1)).strftime("%Y-%m-%d %H:%M:%S")
amout = random.uniform(100000, 20)
amout = round(amout, 2)
# 主表
P1 = ET.Element('P1')
INSURANCE_CODE = ET.Element('INSURANCE_CODE')
INSURANCE_NAME = ET.Element('INSURANCE_NAME')
P4 = ET.Element('P4')
P6 = ET.Element('P6')
P7 = ET.Element('P7')
P8 = ET.Element('P8')
P9 = ET.Element('P9')
P10 = ET.Element('P10')
P11 = ET.Element('P11')
P12 = ET.Element('P12')
P13 = ET.Element('P13')
P14 = ET.Element('P14')
P15 = ET.Element('P15')
P16 = ET.Element('P16')
P17 = ET.Element('P17')
P18 = ET.Element('P18')
P19 = ET.Element('P19')
P27 = ET.Element('P27')
P28 = ET.Element('P28')
P29 = ET.Element('P29')
P30 = ET.Element('P30')
AAZ031 = ET.Element('AAZ031')
P23 = ET.Element('P23')
#主表赋值
P1.text = otherStyleTime6
INSURANCE_CODE.text = '110'
INSURANCE_NAME.text = '城镇企业职工基本养老保险'
P4.text = 'YW' + otherStyleTime3 + str(i)
P6.text = '34000001'
P7.text = '安徽省统筹'
P8.text = '34000001'
P9.text = '安徽省保险事业管理局'
P10.text = 'DYZFZZS'
P11.text = '待遇支付-主子孙'
P12.text = '2'
P13.text = str(amout)
P14.text = '000'
P15.text = otherStyleTime
P16.text = '1'
P17.text = '340000'
P18.text = ran_strx(
"赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤",
3)
P19.text = otherStyleTime
P27.text = '上海银行'
P28.text = '313'
P29.text = '张三'
P30.text = ran_strx("0123456789", 16)
AAZ031.text = P4.text
P23.text = ran_strx("0123456789", 10)
#主表
Main.append(P1)
Main.append(INSURANCE_CODE)
Main.append(INSURANCE_NAME)
Main.append(P4)
Main.append(P6)
Main.append(P7)
Main.append(P8)
Main.append(P9)
Main.append(P10)
Main.append(P11)
Main.append(P12)
Main.append(P13)
Main.append(P14)
Main.append(P15)
Main.append(P16)
Main.append(P17)
Main.append(P18)
Main.append(P19)
Main.append(P27)
Main.append(P28)
Main.append(P29)
Main.append(P30)
Main.append(AAZ031)
Main.append(P23)
#子表
Details = ET.Element('Details')
Main.append(Details)
for j in range(detailNum):
Detail = ET.Element('Detail')
Details.append(Detail)
detailamout = round(amout / detailNum, 2)
#子表
INSURANCE_CODE = ET.Element('INSURANCE_CODE')
INSURANCE_NAME = ET.Element('INSURANCE_NAME')
S3 = ET.Element('S3')
S4 = ET.Element('S4')
S5 = ET.Element('S5')
S6 = ET.Element('S6')
S7 = ET.Element('S7')
S8 = ET.Element('S8')
S9 = ET.Element('S9')
S10 = ET.Element('S10')
S11 = ET.Element('S11')
S12 = ET.Element('S12')
S13 = ET.Element('S13')
S14 = ET.Element('S14')
S15 = ET.Element('S15')
S16 = ET.Element('S16')
S17 = ET.Element('S17')
S18 = ET.Element('S18')
S19 = ET.Element('S19')
S93 = ET.Element('S93')
S94 = ET.Element('S94')
S95 = ET.Element('S95')
S100 = ET.Element('S100')
S81 = ET.Element('S81')
S82 = ET.Element('S82')
S83 = ET.Element('S83')
S84 = ET.Element('S84')
S85 = ET.Element('S85')
S86 = ET.Element('S86')
S87 = ET.Element('S87')
S88 = ET.Element('S88')
#子表赋值
INSURANCE_CODE.text = '110'
INSURANCE_NAME.text = '城镇企业职工基本养老保险'
S3.text = otherStyleTime6
S4.text = cl_org_unit_code[j]
S5.text = cl_org_unit_name[j]
S6.text = otherStyleTime6
S7.text = '01'
S8.text = shenfenzhenghao.outIDcard()
S9.text = '2'
S10.text = str(detailamout)
S11.text = ran_strx("0123456789", 10)
S12.text = ran_strx("0123456789", 15)
S13.text = ran_strx(
"赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤",
3)
S14.text = otherStyleTime
S15.text = otherStyleTime2
S16.text = '银行返回结果失败'
S17.text = '银行附言：收到款项'
S18.text = '1'
S19.text = otherStyleTime3 + str(i) + str(j)
S93.text = '上海银行'
S94.text = '313'
S95.text = '张三'
S100.text = P4.text
S81.text = ran_strx("0123456789", 15)
S82.text = ran_strx(
"赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤",
3)
S83.text = '313'
S84.text = '上海银行'
S85.text = '00'
S86.text = '上海区县'
S87.text = '123456123456'
S88.text = '23435462'
#加入节点
Detail.append(INSURANCE_CODE)
Detail.append(INSURANCE_NAME)
Detail.append(S3)
Detail.append(S4)
Detail.append(S5)
Detail.append(S6)
Detail.append(S7)
Detail.append(S8)
Detail.append(S9)
Detail.append(S10)
Detail.append(S11)
Detail.append(S12)
Detail.append(S13)
Detail.append(S14)
Detail.append(S15)
Detail.append(S16)
Detail.append(S17)
Detail.append(S18)
Detail.append(S19)
Detail.append(S93)
Detail.append(S94)
Detail.append(S95)
Detail.append(S100)
Detail.append(S81)
Detail.append(S82)
Detail.append(S83)
Detail.append(S84)
Detail.append(S85)
Detail.append(S86)
Detail.append(S87)
Detail.append(S88)
# 孙表
Children = ET.Element('Children')
Detail.append(Children)
for k in range(childNum):
childavgamout = round(detailamout / childNum, 2)
Child = ET.Element('Child')
Children.append(Child)
ID = ET.Element('ID')
CREATE_BY = ET.Element('CREATE_BY')
CREATE_DATE = ET.Element('CREATE_DATE')
LAST_MODIFIED_BY = ET.Element('LAST_MODIFIED_BY')
LAST_MODIFIED_DATE = ET.Element('LAST_MODIFIED_DATE')
LAST_MODIFIED_VERSION = ET.Element('LAST_MODIFIED_VERSION')
AAA036 = ET.Element('AAA036')
AAA037 = ET.Element('AAA037')
AAA085 = ET.Element('AAA085')
AAA088 = ET.Element('AAA088')
AAE002 = ET.Element('AAE002')
AAE003 = ET.Element('AAE003')
AAE019 = ET.Element('AAE019')
AAZ031 = ET.Element('AAZ031')
AAZ220 = ET.Element('AAZ220')
BIE504 = ET.Element('BIE504')
BIE505 = ET.Element('BIE505')
BIE506 = ET.Element('BIE506')
YW001 = ET.Element('YW001')
YW002 = ET.Element('YW002')
YW003 = ET.Element('YW003')
YW004 = ET.Element('YW004')
YW005 = ET.Element('YW005')
#赋值
CREATE_BY.text = ''
CREATE_BY.text = ran_strx(
"赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤",
2)
CREATE_DATE.text = otherStyleTime
LAST_MODIFIED_BY.text = ran_strx(
"赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤",
2)
LAST_MODIFIED_DATE.text = otherStyleTime
LAST_MODIFIED_VERSION.text = '1.0'
AAA036.text = cl_AAA037_code[k]
AAA037.text = cl_AAA037_name[k]
AAA085.text = '1'
AAA088.text = '1'
AAE002.text = otherStyleTime2
AAE003.text = otherStyleTime2
AAE019.text = str(childavgamout)
AAZ031.text = '2020061594350925355'
AAZ220.text = S19.text
BIE504.text = otherStyleTime
BIE505.text = '1'
BIE506.text = 'CX' + otherStyleTime4 + str(childNum)
YW001.text = ''
YW002.text = ''
YW003.text = ''
YW004.text = ''
YW005.text = ''
Child.append(ID)
Child.append(CREATE_BY)
Child.append(CREATE_DATE)
Child.append(LAST_MODIFIED_BY)
Child.append(LAST_MODIFIED_DATE)
Child.append(LAST_MODIFIED_VERSION)
Child.append(AAA036)
Child.append(AAA037)
Child.append(AAA085)
Child.append(AAA088)
Child.append(AAE002)
Child.append(AAE003)
Child.append(AAE019)
Child.append(AAZ031)
Child.append(AAZ220)
Child.append(BIE504)
Child.append(BIE505)
Child.append(BIE506)
Child.append(YW001)
Child.append(YW002)
Child.append(YW003)
Child.append(YW004)
Child.append(YW005)
# 增加换行符
__indent(data)
path = '../../Data_xml/支付计划/'
isExists = os.path.exists(path)
if not isExists:
# 如果不存在则创建目录
os.mkdir(path)
file_name = path + '待遇支付-主子孙xml文件' + str(otherStyleTime3) + '.xml'
tree.write(file_name, encoding='utf-8', xml_declaration=False)
with open(file_name, 'r+', encoding='utf-8') as f:
content = f.read()
f.seek(0, 0)
f.write('<?xml version="1.0" encoding="UTF-8"?>\n' + content)
def make_sql(country_code, rg_name, n):
path = '../../Data_sql/'
isExists = os.path.exists(path)
if not isExists:
# 如果不存在则创建目录
os.mkdir(path)
file_name = path + 'smcs_cash'+str(otherStyleTime3)+'.sql'
file_name1 = path + 'smcs_cash_detail'+str(otherStyleTime3)+'.sql'
file_name2 = path + 'smcs_bank_account'+str(otherStyleTime3)+'.sql'
smcs_cash_file = open(file_name, 'w', encoding='utf-8')
smcs_cash_detail_file = open(file_name1, 'w', encoding='utf-8')
smcs_bank_account_file = open(file_name2, 'w', encoding='utf-8')
# 主表sql
str_head_zhu = "insert into smcs_cash values ("
str_head_mingxi = "insert into smcs_cash_detail values ("
str_head_bankaccount = "insert into smcs_bank_account values ("
no = 1
while (n > 0):
ID = ''.join(random.sample(string.ascii_lowercase + string.digits, 30))
CREATE_BY = ran_strx("赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤", 2)
CREATE_DATE = otherStyleTime
LAST_MODIFIED_BY = ran_strx("赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤", 3)
LAST_MODIFIED_DATE = otherStyleTime
LAST_MODIFIED_VERSION = '1'
MESSAGE_TYPE = '1'
# APPLY_NO = ''.join(random.sample(string.ascii_lowercase + string.digits, 20))
APPLY_SUB_NO = '01'
# BILL_NO = ''.join(random.sample(string.ascii_lowercase + string.digits, 16))
COMMON_ACCOUNT = ran_strx("0123456789", 8)
APPLY_USER_NAME = ran_strx("赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤", 4)
APPLY_USER_CARD_ID = shenfenzhenghao.outIDcard()
MEDICAL_CARD = ran_strx("0123456789", 10)
CURRENT_YEAR_PAY = round(random.uniform(100, 10), 2)
ALL_YEAR_PAY = round(random.uniform(100, 10), 2)
PLAN_PAY = round(random.uniform(100, 10), 2)
ADDITIONAL_PAY = round(random.uniform(100, 10), 2)
OTHER_PAY = round(random.uniform(100, 10), 2)
REFUND_AMOUNT = round((CURRENT_YEAR_PAY + ALL_YEAR_PAY + PLAN_PAY + ADDITIONAL_PAY + OTHER_PAY), 2)
MAIL_ADDRESS = '北京市海淀区用友路1号'
MAIL_ADDRESS_CODE = ran_strx("0123456789", 6)
banktype = int(random.uniform(0, cl_banktype.__len__()))
BANK_TYPE = cl_banktype[banktype]
BANK_CODE = ran_strx("0123456789", 12)
BANK_CARD_ACCOUNT = ran_strx("0123456789", 32)
AGENT_USER_NAME = ran_strx("赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤", 3)
UNIT_CODE = '206003001'
UNIT_NAME = '静安区三级预付单位1'
ACCEPT_ACCOUNT_NAME = ''
ACCEPT_ACCOUNT = ''
AEECPT_BANK_NAME = ''
BILL_CREATER = ''
BILL_CREATE_TIME = ''
BILL_CREATE_AREA = ''
VOUCHER_TYPE = ''
VOUCHER_STATUS = ''
VOUVHER_CREATE_TIME = ''
BILL_STATUS = ''
RES1 = ''
RES2 = ''
RES3 = ''
RES4 = ''
COUNTRY_CODE = country_code
REFUND_CATEGORY = ''
ACCOUNT_STATE = '00'
BILL_STATE = '0'
BILL_DATE = ''
HANDWORK_FLAG = ''
NOTE_NUM = ''
FAIL_REASON = ''
FUND_BATCH_NUM = ''
EXCHANGE_NUM = '123456'
REFUND_STATE = ''
WORK_FLOW_STATE = ''
TASK_ID = ''
NODE_ID = ''
PROC_DEF_ID = ''
PROC_INST_ID = ''
CHANGE_STATUS = ''
VOU_GUID = ''
VOU_NO = ''
RG_NAME = rg_name
SERVICE_NODE_NAME = ''
SERVICE_NODE_CODE = ''
BUSSINESS_TYPE = cl_bussniss_type[int(random.uniform(0, 9))]
PAY_TYPE = ''
# bussniss_type 与 pay_type 对应关系
if (BUSSINESS_TYPE == '01' or BUSSINESS_TYPE == '16') :
PAY_TYPE = 1
if (BUSSINESS_TYPE == '02' or BUSSINESS_TYPE == '10' or BUSSINESS_TYPE == '12' or BUSSINESS_TYPE == '15') :
PAY_TYPE = 2
if (BUSSINESS_TYPE == '03' or BUSSINESS_TYPE == '13'):
PAY_TYPE = 3
if (BUSSINESS_TYPE == '04'):
PAY_TYPE = 4
# 业务单据类型为10 转移接续支付时，以下三个字段才有值
if BUSSINESS_TYPE == '10':
ORI_ACCEPT_ACCOUNT_NAME = ran_strx("赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤",
2)
ORI_ACCEPT_ACCOUNT = ran_strx("0123456789", 32)
ORI_ACCEPT_BANK_NAME = '中国建设银行'
INSURANCE_TYPE = '000'
INSURANCE_NAME = '零星报销'
COMMIT_DATE = ''
PAY_TIME = ''
ADD_PAGE_NO = ''
CENTER_NAME = rg_name
CENTER_CODE = country_code
AREA_CODE = ''
FAIL_TIMES = '0'
EXCHANGE_PLAN_STATUS = ''
EXCHANGE_PLAN_ID = ''
AUDIT_STATUS = '00'
NEW_DATE = ''
SEQ_NO = ''
APPLY_NO = country_code + BUSSINESS_TYPE + otherStyleTime1 + str(n)
BILL_NO = APPLY_NO + APPLY_SUB_NO
# 明细表数据
MESSAGE_TYPE1 = '2'
SERAL_NO = ran_strx("0123456789", 13)
# bill_no与主表保持一致
BILL_NO1 = BILL_NO
PAY_TARGET = cl_pay_target[int(random.uniform(0, 7))]
HOSPITAL_LV = cl_hospital_lv[int(random.uniform(0, 7))]
MEDICAL_TYPE = cl_medical_type[int(random.uniform(0, 6))]
if BUSSINESS_TYPE == '15':
SETTLE_TYPE = '2'
elif BUSSINESS_TYPE == '16':
SETTLE_TYPE = '3'
else:
SETTLE_TYPE = BUSSINESS_TYPE
CURRENT_YEAR_AMOUNT1 = CURRENT_YEAR_PAY
ALL_YEAR_AMOUNT1 = ALL_YEAR_PAY
PLAN_PAY1 = PLAN_PAY
ADDITIONAL_PAY1 = ADDITIONAL_PAY
OTHER_PAY1 = OTHER_PAY
SELF_AMOUNT = round(random.uniform(100, 10), 2)
REFUND_AMOUNT1 = REFUND_AMOUNT
TOTAL_AMOUNT = round((SELF_AMOUNT + REFUND_AMOUNT1), 2)
line = str_head_zhu + '"' + ID + '", "' + CREATE_BY + '", ' + 'TO_TIMESTAMP(\'' + CREATE_DATE + '\',\'SYYYY-MM-DD HH24:MI:SS:FF6\'), ' + '"' + LAST_MODIFIED_BY + '", TO_TIMESTAMP(\'' + LAST_MODIFIED_DATE + '\',\'SYYYY-MM-DD HH24:MI:SS:FF6\'), "' + \
LAST_MODIFIED_VERSION + '", "' + MESSAGE_TYPE + '", "' + APPLY_NO + '", "' + APPLY_SUB_NO + '", "' + BILL_NO + '", "' + COMMON_ACCOUNT + '", "' + \
APPLY_USER_NAME + '", "' + APPLY_USER_CARD_ID + '", "' + MEDICAL_CARD + '", "' + str(REFUND_AMOUNT) + '", "' + str(CURRENT_YEAR_PAY) + '", "' + str(ALL_YEAR_PAY) + '", "' + \
str(PLAN_PAY) + '", "' + str(ADDITIONAL_PAY) + '", "' + str(OTHER_PAY) + '", "' + str(PAY_TYPE) + '", "' + MAIL_ADDRESS + '", "' + MAIL_ADDRESS_CODE + '", "' + BANK_TYPE + '", "' + \
BANK_CODE + '", "' + BANK_CARD_ACCOUNT + '", "' + AGENT_USER_NAME + '", "' + UNIT_CODE + '", "' + UNIT_NAME + '", "' + ACCEPT_ACCOUNT_NAME + '", "' + \
ACCEPT_ACCOUNT + '", "' + AEECPT_BANK_NAME + '", "' + BILL_CREATER + '", "' + BILL_CREATE_TIME + '", "' + BILL_CREATE_AREA + '", "' + VOUCHER_TYPE + '", "' + \
VOUCHER_STATUS + '", "' + VOUVHER_CREATE_TIME + '", "' + BILL_STATUS + '", "' + RES1 + '", "' + RES2 + '", "' + RES3 + '", "' + RES4 + '", "' + COUNTRY_CODE + '", "' + \
REFUND_CATEGORY + '", "' + ACCOUNT_STATE + '", "' + BILL_STATE + '", "' + BILL_DATE + '", "' + HANDWORK_FLAG + '", "' + NOTE_NUM + '", "' + FAIL_REASON + '", "' + \
FUND_BATCH_NUM + '", "' + ORI_ACCEPT_ACCOUNT_NAME + '", "' + ORI_ACCEPT_ACCOUNT + '", "' + ORI_ACCEPT_BANK_NAME + '", "' + EXCHANGE_NUM + '", "' + \
REFUND_STATE + '", "' + WORK_FLOW_STATE + '", "' + TASK_ID + '", "' + NODE_ID + '", "' + PROC_DEF_ID + '", "' + PROC_INST_ID + '", "' + CHANGE_STATUS + '", "' + \
VOU_GUID + '", "' + VOU_NO + '", "' + RG_NAME + '", "' + SERVICE_NODE_NAME + '", "' + SERVICE_NODE_CODE + '", "' + BUSSINESS_TYPE + '", "' + INSURANCE_TYPE + '", "' + \
INSURANCE_NAME + '", "' + COMMIT_DATE + '", "' + PAY_TIME + '", "' + ADD_PAGE_NO + '", "' + CENTER_NAME + '", "' + CENTER_CODE + '", "' + AREA_CODE + '", "' + \
FAIL_TIMES + '", "' + EXCHANGE_PLAN_STATUS + '", "' + EXCHANGE_PLAN_ID + '", "' + AUDIT_STATUS + '", "' + NEW_DATE + '", "' + SEQ_NO + '");'
line1 = str_head_mingxi + '"' + ID + '", "' + CREATE_BY + '", ' + 'TO_TIMESTAMP(\'' + CREATE_DATE + '\',\'SYYYY-MM-DD HH24:MI:SS:FF6\'),' + ' "' + LAST_MODIFIED_BY + '"' + \
', TO_TIMESTAMP(\'' + LAST_MODIFIED_DATE + '\',\'SYYYY-MM-DD HH24:MI:SS:FF6\'), ' + '"' + LAST_MODIFIED_VERSION + '", "' + MESSAGE_TYPE1 + '", "' + SERAL_NO + '", "' + \
BILL_NO1 + '", "' + PAY_TARGET + '", "' + HOSPITAL_LV + '", "' + MEDICAL_TYPE + '", "' + SETTLE_TYPE + '", "' + \
str(TOTAL_AMOUNT) + '", "' + str(SELF_AMOUNT) + '", "' + str(REFUND_AMOUNT1) + '", "' + str(CURRENT_YEAR_AMOUNT1) + '", "' + \
str(ALL_YEAR_AMOUNT1) + '", "' + str(PLAN_PAY1) + '", "' + str(ADDITIONAL_PAY1) + '", "' + str(OTHER_PAY1) + '", "' + EXCHANGE_PLAN_ID + \
'", "' + EXCHANGE_PLAN_STATUS + '");'
AREA_CODE = '101'
# CENTER_CODE = '黄浦区'
SERVER_NODE_CODE = ''
ACCOUNT_NO = BANK_CARD_ACCOUNT
# BANK_CODE = '5042'
ACCOUNT_NAME = APPLY_USER_NAME
BANK_NODE_NAME = '昌平回龙观支行'
IMPORT_DATE = otherStyleTime2
USER_CARD_NO = APPLY_USER_CARD_ID
BANK_NAME = AEECPT_BANK_NAME
line2 = str_head_bankaccount + '"' + ID + '", "' + CREATE_BY + '", ' + 'TO_TIMESTAMP(\'' + CREATE_DATE + '\',\'SYYYY-MM-DD HH24:MI:SS:FF6\'),' + ' "' + LAST_MODIFIED_BY + '"' + \
', TO_TIMESTAMP(\'' + LAST_MODIFIED_DATE + '\',\'SYYYY-MM-DD HH24:MI:SS:FF6\'), ' + '"' + LAST_MODIFIED_VERSION + '", "' + AREA_CODE + '", "' + CENTER_CODE + '", "' + \
SERVER_NODE_CODE + '", "' + ACCOUNT_NO + '", "' + BANK_CODE + '", "' + ACCOUNT_NAME + '", "' + BANK_NODE_NAME + '", "' + \
IMPORT_DATE + '", "' + USER_CARD_NO + '", "' + BANK_NAME + '");'
line = line.replace('\"', '\'')
line1 = line1.replace('\"', '\'')
line2 = line2.replace('\"', '\'')
no += 1
n -= 1
smcs_cash_file.write(line + '\n')
smcs_cash_detail_file.write(line1 + '\n')
smcs_bank_account_file.write(line2 + '\n')
def make_sql(country_code, n):
path = '../../Data_sql/支付计划/'
isExists = os.path.exists(path)
if not isExists:
# 如果不存在则创建目录
os.mkdir(path)
file_name = path + '待遇支付-主子孙中间表' + str(otherStyleTime3) + '.sql'
SMRTS_PAY_PLAN_file = open(file_name, 'w', encoding='utf-8')
# 主子孙表sql
str_head_zhu = "insert into MID_PAY_PLAN_OLD values ("
str_head_detail = "insert into MID_PAY_PLAN_DETAIL_OLD values ("
str_head_item = "insert into MID_PAY_PLAN_ITEM_OLD values ("
no = 1
while (n > 0):
# 主表
P1 = otherStyleTime6
INSURANCE_CODE = '110'
INSURANCE_NAME = '城镇企业职工基本养老保险'
P4 = 'YW' + otherStyleTime4 + str(n)
P5 = ''
P6 = cl_country_code[country_code]
P7 = cl_country_name[country_code]
AGENCY_CODE = cl_agency_code[country_code]
AGENCY_NAME = cl_agency_name[country_code]
P10 = cl_bussnisstype_code[0]
P11 = cl_bussnisstype_name[0]
P12 = '2'
P13 = '2000.00'
P14 = '1'
P15 = otherStyleTime
P16 = '1'
P17 = cl_org_code[country_code]
P18 = ran_strx(
"赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤",
3)
P19 = otherStyleTime
status = ''
# 子表-one
o_INSURANCE_CODE = '110'
o_INSURANCE_NAME = '城镇企业职工基本养老保险'
o_S3 = otherStyleTime6
o_S4 = '999'
o_S5 = '上海市待遇支付往来单位001'
o_S6 = otherStyleTime6
o_S7 = '01'
o_S8 = shenfenzhenghao.outIDcard()
o_S9 = '2'
o_S10 = '1200.00'
o_S11 = ran_strx("0123456789", 10)
o_S12 = ran_strx("0123456789", 15)
o_S13 = ran_strx(
"赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤",
3)
o_S14 = otherStyleTime
o_S15 = otherStyleTime2
o_S16 = '银行返回结果失败'
o_S17 = '银行附言：收到款项'
o_S18 = '1'
o_S19 = otherStyleTime3 + str(n) + '01'
o_S100 = P4
o_status = ''
t_INSURANCE_CODE = '110'
t_INSURANCE_NAME = '城镇企业职工基本养老保险'
t_S3 = otherStyleTime6
t_S4 = '998'
t_S5 = '上海市待遇支付往来单位002'
t_S6 = otherStyleTime6
t_S7 = '01'
t_S8 = shenfenzhenghao.outIDcard()
t_S9 = '2'
t_S10 = '800.00'
t_S11 = ran_strx("0123456789", 10)
t_S12 = ran_strx("0123456789", 15)
t_S13 = ran_strx(
"赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤",
3)
t_S14 = otherStyleTime
t_S15 = otherStyleTime2
t_S16 = '银行返回结果失败'
t_S17 = '银行附言：收到款项'
t_S18 = '1'
t_S19 = otherStyleTime3 + str(n) + '02'
t_S100 = P4
t_status = ''
# 孙表-one
oo_AAZ220 = o_S19
oo_AAA036 = '1002133'
oo_AAA037 = '养老金'
oo_AAA085 = '1'
oo_AAA088 = '1'
oo_AAE002 = otherStyleTime2
oo_AAE003 = otherStyleTime2
oo_AAE019 = '600.00'
oo_BIE505 = '1'
oo_BIE504 = otherStyleTime
oo_BIE506 = 'CX' + otherStyleTime4 + str(n)
oo_YW001 = ''
oo_YW002 = ''
oo_YW003 = ''
oo_YW004 = ''
oo_YW005 = ''
oo_status = ''
ot_AAZ220 = o_S19
ot_AAA036 = '1002134'
ot_AAA037 = '离休金'
ot_AAA085 = '1'
ot_AAA088 = '1'
ot_AAE002 = otherStyleTime2
ot_AAE003 = otherStyleTime2
ot_AAE019 = '600.00'
ot_BIE505 = '1'
ot_BIE504 = otherStyleTime
ot_BIE506 = 'CX' + otherStyleTime4 + str(n)
ot_YW001 = ''
ot_YW002 = ''
ot_YW003 = ''
ot_YW004 = ''
ot_YW005 = ''
ot_status = ''
to_AAZ220 = t_S19
to_AAA036 = '1002133'
to_AAA037 = '养老金'
to_AAA085 = '1'
to_AAA088 = '1'
to_AAE002 = otherStyleTime2
to_AAE003 = otherStyleTime2
to_AAE019 = '400.00'
to_BIE505 = '1'
to_BIE504 = otherStyleTime
to_BIE506 = 'CX' + otherStyleTime4 + str(n)
to_YW001 = ''
to_YW002 = ''
to_YW003 = ''
to_YW004 = ''
to_YW005 = ''
to_status = ''
tt_AAZ220 = t_S19
tt_AAA036 = '1002134'
tt_AAA037 = '离休金'
tt_AAA085 = '1'
tt_AAA088 = '1'
tt_AAE002 = otherStyleTime2
tt_AAE003 = otherStyleTime2
tt_AAE019 = '400.00'
tt_BIE505 = '1'
tt_BIE504 = otherStyleTime
tt_BIE506 = 'CX' + otherStyleTime4 + str(n)
tt_YW001 = ''
tt_YW002 = ''
tt_YW003 = ''
tt_YW004 = ''
tt_YW005 = ''
tt_status = ''
line = str_head_zhu + '"' + P1 + '", "' + INSURANCE_CODE + '", "' + INSURANCE_NAME + '", "' + P4 + '", "' + P5 + '", "' + P6 + '", "' + P7 + '", "' + AGENCY_CODE + '", "' + AGENCY_NAME + '", "' + P10 + '", "' + P11 + '", "' + P12 + '", "' + P13 + '", "' + P14 + '", "' + P15 + '", "' + P16 + '", "' + P17 + '", "' + P18 + '", "' + P19 + '", "' + status + '");'
line_detail1 = str_head_detail + '"' + o_INSURANCE_CODE + '", "' + o_INSURANCE_NAME + '", "' + o_S3 + '", "' + o_S4 + '", "' + o_S5 + '", "' + o_S6 + '", "' + o_S7 + '", "' + o_S8 + '", "' + o_S9 + '", "' + o_S10 + '", "' + o_S11 + '", "' + o_S12 + '", "' + o_S13 + '", "' + o_S14 + '", "' + o_S15 + '", "' + o_S16 + '", "' + o_S17 + '", "' + o_S18 + '", "' + o_S19 + '", "' + o_S100 + '", "' + o_status + '");'
line_detail2 = str_head_detail + '"' + t_INSURANCE_CODE + '", "' + t_INSURANCE_NAME + '", "' + t_S3 + '", "' + t_S4 + '", "' + t_S5 + '", "' + t_S6 + '", "' + t_S7 + '", "' + t_S8 + '", "' + t_S9 + '", "' + t_S10 + '", "' + t_S11 + '", "' + t_S12 + '", "' + t_S13 + '", "' + t_S14 + '", "' + t_S15 + '", "' + t_S16 + '", "' + t_S17 + '", "' + t_S18 + '", "' + t_S19 + '", "' + t_S100 + '", "' + t_status + '");'
line_item11 = str_head_item + '"' + oo_AAZ220 + '", "' + oo_AAA036 + '", "' + oo_AAA037 + '", "' + oo_AAA085 + '", "' + oo_AAA088 + '", "' + oo_AAE002 + '", "' + oo_AAE003 + '", "' + oo_AAE019 + '", "' + oo_BIE505 + '", "' + oo_BIE504 + '", "' + oo_BIE506 + '", "' + oo_YW001 + '", "' + oo_YW002 + '", "' + oo_YW003 + '", "' + oo_YW004 + '", "' + oo_YW005 + '", "' + oo_status + '");'
line_item12 = str_head_item + '"' + ot_AAZ220 + '", "' + ot_AAA036 + '", "' + ot_AAA037 + '", "' + ot_AAA085 + '", "' + ot_AAA088 + '", "' + ot_AAE002 + '", "' + ot_AAE003 + '", "' + ot_AAE019 + '", "' + ot_BIE505 + '", "' + ot_BIE504 + '", "' + ot_BIE506 + '", "' + ot_YW001 + '", "' + ot_YW002 + '", "' + ot_YW003 + '", "' + ot_YW004 + '", "' + ot_YW005 + '", "' + ot_status + '");'
line_item21 = str_head_item + '"' + to_AAZ220 + '", "' + to_AAA036 + '", "' + to_AAA037 + '", "' + to_AAA085 + '", "' + to_AAA088 + '", "' + to_AAE002 + '", "' + to_AAE003 + '", "' + to_AAE019 + '", "' + to_BIE505 + '", "' + to_BIE504 + '", "' + to_BIE506 + '", "' + to_YW001 + '", "' + to_YW002 + '", "' + to_YW003 + '", "' + to_YW004 + '", "' + to_YW005 + '", "' + to_status + '");'
line_item22 = str_head_item + '"' + tt_AAZ220 + '", "' + tt_AAA036 + '", "' + tt_AAA037 + '", "' + tt_AAA085 + '", "' + tt_AAA088 + '", "' + tt_AAE002 + '", "' + tt_AAE003 + '", "' + tt_AAE019 + '", "' + tt_BIE505 + '", "' + tt_BIE504 + '", "' + tt_BIE506 + '", "' + tt_YW001 + '", "' + tt_YW002 + '", "' + tt_YW003 + '", "' + tt_YW004 + '", "' + tt_YW005 + '", "' + tt_status + '");'
line = line.replace('\"', '\'')
line_detail1 = line_detail1.replace('\"', '\'')
line_detail2 = line_detail2.replace('\"', '\'')
line_item11 = line_item11.replace('\"', '\'')
line_item12 = line_item12.replace('\"', '\'')
line_item21 = line_item21.replace('\"', '\'')
line_item22 = line_item22.replace('\"', '\'')
no += 1
n -= 1
SMRTS_PAY_PLAN_file.write(line + '\n')
SMRTS_PAY_PLAN_file.write(line_detail1 + '\n')
SMRTS_PAY_PLAN_file.write(line_detail2 + '\n')
SMRTS_PAY_PLAN_file.write(line_item11 + '\n')
SMRTS_PAY_PLAN_file.write(line_item12 + '\n')
SMRTS_PAY_PLAN_file.write(line_item21 + '\n')
SMRTS_PAY_PLAN_file.write(line_item22 + '\n')
def make_sql(country_code, rg_name, n):
path = '../Data_sql/'
isExists = os.path.exists(path)
if not isExists:
# 如果不存在则创建目录
os.mkdir(path)
file_name = path + 'smcs_cash_pos_business' + str(otherStyleTime3) + '.sql'
smcs_cash_file = open(file_name, 'w', encoding='utf-8')
# 主表sql
str_head_pos = "insert into smcs_cash_pos_business values ("
no = 1
while (n > 0):
ID = ''.join(random.sample(string.ascii_lowercase + string.digits, 30))
CREATE_BY = ran_strx(
"赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤",
2)
CREATE_DATE = otherStyleTime
LAST_MODIFIED_BY = ran_strx(
"赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤",
3)
LAST_MODIFIED_DATE = otherStyleTime
LAST_MODIFIED_VERSION = '1'
MESSAGE_TYPE = random.choice(['7', '8'])
# APPLY_NO = ''.join(random.sample(string.ascii_lowercase + string.digits, 20))
SERIAL_NO = ''.join(
random.sample(string.ascii_lowercase + string.digits, 16))
APPLY_SUB_NO = '01'
# BILL_NO = ''.join(random.sample(string.ascii_lowercase + string.digits, 16))
SETTLE_TYPE = '1'
PAY_AMOUNT = 6
PAY_DATE = otherStyleTime
PAY_YEAR = '2020'
APPLY_USER_NAME = ran_strx(
"赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤",
4)
APPLY_USER_CARD_ID = shenfenzhenghao.outIDcard()
MEDICARE_ORDER_NO = ran_strx("0123456789", 10)
OPT_COUNTY = country_code
CENSUS_COUNTY = country_code
INSURANCE_TYPE = '2'
PAY_TYPE = '1'
RES1 = ''
RES2 = ''
RES3 = ''
RES4 = ''
CENTER_CODE = country_code
CENTER_NAME = rg_name
APPLY_NO = country_code + str(n)
BILL_NO = APPLY_NO + APPLY_SUB_NO
EXCHANGE_PLAN_STATUS = ''
EXCHANGE_PLAN_ID = ''
line = str_head_pos + '"' + ID + '", "' + MESSAGE_TYPE + '", "' + SERIAL_NO + '", "' + APPLY_NO + '", "' + \
APPLY_SUB_NO + '", "' + BILL_NO + '", "' + APPLY_USER_NAME + '", "' + APPLY_USER_CARD_ID + '", "' + \
SETTLE_TYPE + '", "' + str(PAY_AMOUNT) + '", ' + 'TO_TIMESTAMP(\'' + PAY_DATE + '\',\'SYYYY-MM-DD HH24:MI:SS:FF6\'),' + ' "' + PAY_YEAR + '", "' + \
MEDICARE_ORDER_NO + '", "' + OPT_COUNTY + '", "' + CENSUS_COUNTY + '", "' + INSURANCE_TYPE + '", "' \
+ PAY_TYPE + '", "' + CREATE_BY + '", ' + 'TO_TIMESTAMP(\'' + CREATE_DATE + '\',\'SYYYY-MM-DD HH24:MI:SS:FF6\'),' + ' "' + LAST_MODIFIED_BY + '", ' + 'TO_TIMESTAMP(\'' + \
LAST_MODIFIED_DATE + '\',\'SYYYY-MM-DD HH24:MI:SS:FF6\'),' + ' "' + LAST_MODIFIED_VERSION + '", "' + RES1 + '", "' + RES2 + '", "' + \
RES3 + '", "' + RES4 + '", "' + CENTER_CODE + '", "' + CENTER_NAME + '", "' + EXCHANGE_PLAN_STATUS + '", "' + EXCHANGE_PLAN_ID + '");'
line = line.replace('\"', '\'')
no += 1
n -= 1
smcs_cash_file.write(line + '\n')
def make_sql(country_code, n):
starttime = datetime.datetime.now()
db = cx_Oracle.connect('smifcmiddledb/[email protected]:1521/orcl')
cr = db.cursor() #创建游标
no = 1
while (n > 0):
# 主表
P1 = otherStyleTime6
INSURANCE_CODE = '110'
INSURANCE_NAME = '城镇企业职工基本养老保险'
P4 = 'YW' + otherStyleTime4 + str(n)
P5 = ''
P6 = cl_country_code[country_code]
P7 = cl_country_name[country_code]
AGENCY_CODE = cl_agency_code[country_code]
AGENCY_NAME = cl_agency_name[country_code]
P10 = cl_bussnisstype_code[0]
P11 = cl_bussnisstype_name[0]
P12 = '2'
P13 = '2000.00'
# 代发户银行编码
P14 = '000'
P15 = otherStyleTime
P16 = '1'
P17 = cl_org_code[country_code]
P18 = ran_strx("赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤", 3)
P19 = otherStyleTime
status = ''
P23 = P4
sqlG = " insert into MID_PAY_PLAN_OLD(P1, INSURANCE_CODE, INSURANCE_NAME, P4, P5, P6, P7, AGENCY_CODE, AGENCY_NAME, P10, P11, P12, P13, P14, P15, P16, P17, P18, P19, status, P23) " \
" values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21) "
cr.execute(sqlG, (P1, INSURANCE_CODE, INSURANCE_NAME, P4, P5, P6, P7, AGENCY_CODE, AGENCY_NAME, P10, P11, P12, P13, P14, P15, P16, P17, P18, P19, status, P23))
# 子表-one
o_INSURANCE_CODE = '110'
o_INSURANCE_NAME = '城镇企业职工基本养老保险'
o_S3 = otherStyleTime6
o_S4 = '1111999'
o_S5 = '上海市待遇支付往来单位001'
o_S6 = otherStyleTime6
o_S7 = '01'
o_S8 = shenfenzhenghao.outIDcard()
o_S9 = '2'
o_S10 = '1200.00'
o_S11 = ran_strx("0123456789", 10)
o_S12 = ran_strx("0123456789", 15)
o_S13 = ran_strx("赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤", 3)
o_S14 = otherStyleTime
o_S15 = otherStyleTime2
o_S16 = '银行返回结果失败'
o_S17 = '银行附言：收到款项'
o_S18 = '1'
o_S19 = otherStyleTime3 + str(n)+'01'
o_S100 = P4
o_status = ''
o_S81 = ran_strx("0123456789", 15)
o_S82 = ran_strx("赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤", 3)
o_S83 = '313'
o_S84 = '上海银行'
o_S85 = '00'
o_S86 = '上海区县'
o_S87 = '123456123456'
o_S88 = '01'+P4
AGENCY_CODE =cl_agency_code[country_code]
AGENCY_NAME =cl_agency_name[country_code]
sqlo = " insert into MID_PAY_PLAN_DETAIL_OLD(INSURANCE_CODE, INSURANCE_NAME, S3, S4, S5, S6, S7, S8, S9, S10, S11, S12, S13, S14, S15, S16, S17, S18, S19, S100, status, S81, S82, S83, S84, S85, S86, S87, S88, AGENCY_CODE, AGENCY_NAME) " \
" values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31) "
cr.execute(sqlo, (o_INSURANCE_CODE, o_INSURANCE_NAME, o_S3, o_S4, o_S5, o_S6, o_S7, o_S8, o_S9, o_S10, o_S11, o_S12, o_S13, o_S14, o_S15, o_S16, o_S17, o_S18, o_S19, o_S100, o_status, o_S81, o_S82, o_S83, o_S84, o_S85, o_S86, o_S87, o_S88, AGENCY_CODE, AGENCY_NAME))
t_INSURANCE_CODE = '110'
t_INSURANCE_NAME = '城镇企业职工基本养老保险'
t_S3 = otherStyleTime6
t_S4 = '1111998'
t_S5 = '上海市待遇支付往来单位002'
t_S6 = otherStyleTime6
t_S7 = '01'
t_S8 = shenfenzhenghao.outIDcard()
t_S9 = '2'
t_S10 = '800.00'
t_S11 = ran_strx("0123456789", 10)
t_S12 = ran_strx("0123456789", 15)
t_S13 = ran_strx("赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤", 3)
t_S14 = otherStyleTime
t_S15 = otherStyleTime2
t_S16 = '银行返回结果失败'
t_S17 = '银行附言：收到款项'
t_S18 = '1'
t_S19 = otherStyleTime3 + str(n)+'02'
t_S100 = P4
t_status = ''
t_S81 = ran_strx("0123456789", 15)
t_S82 = ran_strx("赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤", 3)
t_S83 = '313'
t_S84 = '上海银行'
t_S85 = '00'
t_S86 = '上海区县'
t_S87 = '123456123456'
t_S88 = '02'+P4
AGENCY_CODE =cl_agency_code[country_code]
AGENCY_NAME =cl_agency_name[country_code]
sqlt = " insert into MID_PAY_PLAN_DETAIL_OLD(INSURANCE_CODE, INSURANCE_NAME, S3, S4, S5, S6, S7, S8, S9, S10, S11, S12, S13, S14, S15, S16, S17, S18, S19, S100, status, S81, S82, S83, S84, S85, S86, S87, S88, AGENCY_CODE, AGENCY_NAME) " \
" values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31) "
cr.execute(sqlt, (t_INSURANCE_CODE, t_INSURANCE_NAME, t_S3, t_S4, t_S5, t_S6, t_S7, t_S8, t_S9, t_S10, t_S11, t_S12, t_S13, t_S14, t_S15, t_S16, t_S17, t_S18, t_S19, t_S100, t_status, t_S81, t_S82, t_S83, t_S84, t_S85, t_S86, t_S87, t_S88, AGENCY_CODE, AGENCY_NAME))
# 孙表-one
oo_AAZ220 = o_S19
oo_AAA036 = '1002133'
oo_AAA037 = '养老金'
oo_AAA085 = '1'
oo_AAA088 = '1'
oo_AAE002 = otherStyleTime2
oo_AAE003 = otherStyleTime2
oo_AAE019 = '600.00'
oo_BIE505 = '1'
oo_BIE504 = otherStyleTime
oo_BIE506 = 'CX'+otherStyleTime4+str(n)
oo_YW001 = ''
oo_YW002 = ''
oo_YW003 = ''
oo_YW004 = ''
oo_YW005 = ''
oo_status = ''
sqloo = " insert into MID_PAY_PLAN_ITEM_OLD(AAZ220, AAA036, AAA037, AAA085, AAA088, AAE002, AAE003, AAE019, BIE505, BIE504, BIE506, YW001, YW002, YW003, YW004, YW005, status) " \
" values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17) "
cr.execute(sqloo, (oo_AAZ220, oo_AAA036, oo_AAA037, oo_AAA085, oo_AAA088, oo_AAE002, oo_AAE003, oo_AAE019, oo_BIE505, oo_BIE504, oo_BIE506, oo_YW001, oo_YW002, oo_YW003, oo_YW004, oo_YW005, oo_status))
ot_AAZ220 = o_S19
ot_AAA036 = '1002134'
ot_AAA037 = '离休金'
ot_AAA085 = '1'
ot_AAA088 = '1'
ot_AAE002 = otherStyleTime2
ot_AAE003 = otherStyleTime2
ot_AAE019 = '600.00'
ot_BIE505 = '1'
ot_BIE504 = otherStyleTime
ot_BIE506 = 'CX'+otherStyleTime4+str(n)
ot_YW001 = ''
ot_YW002 = ''
ot_YW003 = ''
ot_YW004 = ''
ot_YW005 = ''
ot_status = ''
sqlot = " insert into MID_PAY_PLAN_ITEM_OLD(AAZ220, AAA036, AAA037, AAA085, AAA088, AAE002, AAE003, AAE019, BIE505, BIE504, BIE506, YW001, YW002, YW003, YW004, YW005, status) " \
" values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17) "
cr.execute(sqlot, (ot_AAZ220, ot_AAA036, ot_AAA037, ot_AAA085, ot_AAA088, ot_AAE002, ot_AAE003, ot_AAE019, ot_BIE505, ot_BIE504, ot_BIE506, ot_YW001, ot_YW002, ot_YW003, ot_YW004, ot_YW005, ot_status))
to_AAZ220 = t_S19
to_AAA036 = '1002133'
to_AAA037 = '养老金'
to_AAA085 = '1'
to_AAA088 = '1'
to_AAE002 = otherStyleTime2
to_AAE003 = otherStyleTime2
to_AAE019 = '400.00'
to_BIE505 = '1'
to_BIE504 = otherStyleTime
to_BIE506 = 'CX'+otherStyleTime4+str(n)
to_YW001 = ''
to_YW002 = ''
to_YW003 = ''
to_YW004 = ''
to_YW005 = ''
to_status = ''
sqlto = " insert into MID_PAY_PLAN_ITEM_OLD(AAZ220, AAA036, AAA037, AAA085, AAA088, AAE002, AAE003, AAE019, BIE505, BIE504, BIE506, YW001, YW002, YW003, YW004, YW005, status) " \
" values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17) "
cr.execute(sqlto, (to_AAZ220, to_AAA036, to_AAA037, to_AAA085, to_AAA088, to_AAE002, to_AAE003, to_AAE019, to_BIE505, to_BIE504, to_BIE506, to_YW001, to_YW002, to_YW003, to_YW004, to_YW005, to_status))
tt_AAZ220 = t_S19
tt_AAA036 = '1002134'
tt_AAA037 = '离休金'
tt_AAA085 = '1'
tt_AAA088 = '1'
tt_AAE002 = otherStyleTime2
tt_AAE003 = otherStyleTime2
tt_AAE019 = '400.00'
tt_BIE505 = '1'
tt_BIE504 = otherStyleTime
tt_BIE506 = 'CX'+otherStyleTime4+str(n)
tt_YW001 = ''
tt_YW002 = ''
tt_YW003 = ''
tt_YW004 = ''
tt_YW005 = ''
tt_status = ''
sqltt = " insert into MID_PAY_PLAN_ITEM_OLD(AAZ220, AAA036, AAA037, AAA085, AAA088, AAE002, AAE003, AAE019, BIE505, BIE504, BIE506, YW001, YW002, YW003, YW004, YW005, status) " \
" values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17) "
cr.execute(sqltt, (tt_AAZ220, tt_AAA036, tt_AAA037, tt_AAA085, tt_AAA088, tt_AAE002, tt_AAE003, tt_AAE019, tt_BIE505, tt_BIE504, tt_BIE506, tt_YW001, tt_YW002, tt_YW003, tt_YW004, tt_YW005, tt_status))
no += 1
n -= 1
cr.close()
db.commit()
db.close()
endtime = datetime.datetime.now()
print("耗时H：" + str(round((endtime - starttime).seconds / 3600, 2)))
def make_sql(country_code,bus_no,summary_type,ins_no,n):
starttime = datetime.datetime.now()
# 建立数据库连接
db = pymysql.Connect(
host='10.10.66.158',##mysql服务器地址
port=3306,##mysql服务器端口号
user='******',##用户名
passwd='1',##密码
db='sifcmiddledb',##数据库名
charset='utf8'##连接编码
)
#创建游标
cr = db.cursor()
no = 1
while (n > 0):
# 主表
UNION_CODE = cl_country_code[country_code]
UNION_NAME = cl_country_name[country_code]
BATCH_NO = 'QH' + otherStyleTime3 + str(n)
YE_MONTH = otherStyleTime6
INSURANCE_CODE = cl_ins_code[ins_no]
INSURANCE_NAME = cl_ins_name[ins_no]
BUSINESS_TYPE_CODE = cl_bussnisstype_code[bus_no]
BUSINESS_TYPE_NAME = cl_bussnisstype_name[bus_no]
AGENCY_CODE = cl_agency_code[country_code]
AGENCY_NAME = cl_agency_name[country_code]
TOTAL_AMOUNT = '3333.33'
TOTAL_COUNT = '2'
# 1-支付计划 2-统支结算单
SUMMARY_TYPE = summary_type
STATUS = '0'
ROLLBACK_REASON = ''
ROLLBACK_TIME = datetime.datetime.fromisoformat('1900-01-01 00:00:00').strftime("%Y-%m-%d %H:%M:%S")
FIELD04 = '1'
# 是否主子孙 0-是 1-否
if (BUSINESS_TYPE_CODE == 'QHK10025' or BUSINESS_TYPE_CODE == 'QHK10029' or BUSINESS_TYPE_CODE == 'QHK9903'):
FIELD04 = '0'
#领导人终审时间
CWZS_TIME = datetime.datetime.fromisoformat('1900-01-01 00:00:00').strftime("%Y-%m-%d %H:%M:%S")
#领导终审人
CWZS_PEOPLE = ''
#预付金操作类型
OPE_TYPE = ''
JZ_STATUS = ''
VOU_NO = ''
VOU_STATUS = ''
VOU_DATE = ''
DATA_DEAL_STAS = '101'
sqlG = "insert into qh_smrts_pay_plan_mi values (%(DATA_DEAL_STAS)s, %(FIELD04)s, %(UNION_CODE)s, %(UNION_NAME)s, %(BATCH_NO)s, %(YE_MONTH)s, %(INSURANCE_CODE)s, %(INSURANCE_NAME)s, %(BUSINESS_TYPE_CODE)s, %(BUSINESS_TYPE_NAME)s, %(AGENCY_CODE)s, %(AGENCY_NAME)s, %(TOTAL_COUNT)s, %(TOTAL_AMOUNT)s, %(SUMMARY_TYPE)s, %(STATUS)s, %(ROLLBACK_REASON)s, %(ROLLBACK_TIME)s, %(CWZS_TIME)s, %(CWZS_PEOPLE)s, %(OPE_TYPE)s, %(JZ_STATUS)s, %(VOU_NO)s, %(VOU_STATUS)s, %(VOU_DATE)s)"
value = {
"UNION_CODE":UNION_CODE,
"UNION_NAME":UNION_NAME,
"BATCH_NO":BATCH_NO,
"YE_MONTH":YE_MONTH,
"INSURANCE_CODE":INSURANCE_CODE,
"INSURANCE_NAME":INSURANCE_NAME,
"BUSINESS_TYPE_CODE":BUSINESS_TYPE_CODE,
"BUSINESS_TYPE_NAME":BUSINESS_TYPE_NAME,
"AGENCY_CODE":AGENCY_CODE,
"AGENCY_NAME":AGENCY_NAME,
"TOTAL_COUNT":TOTAL_COUNT,
"TOTAL_AMOUNT":TOTAL_AMOUNT,
"SUMMARY_TYPE":SUMMARY_TYPE,
"STATUS":STATUS,
"ROLLBACK_REASON":ROLLBACK_REASON,
"ROLLBACK_TIME":ROLLBACK_TIME,
"FIELD04":FIELD04,
"CWZS_TIME":CWZS_TIME,
"CWZS_PEOPLE":CWZS_PEOPLE,
"OPE_TYPE":OPE_TYPE,
"JZ_STATUS":JZ_STATUS,
"VOU_NO":VOU_NO,
"VOU_STATUS":VOU_STATUS,
"VOU_DATE":VOU_DATE,
"DATA_DEAL_STAS":DATA_DEAL_STAS
}
cr.execute(sqlG, value)
#子表
BATCH_NO = BATCH_NO
YE_MONTH = otherStyleTime6
INSURANCE_CODE = cl_ins_code[ins_no]
INSURANCE_NAME = cl_ins_name[ins_no]
BUSINESS_TYPE_CODE = cl_bussnisstype_code[bus_no]
BUSINESS_TYPE_NAME = cl_bussnisstype_name[bus_no]
AGENCY_CODE = cl_agency_code[country_code]
AGENCY_NAME = cl_agency_name[country_code]
# 往来单位1
MEDICAL_CODE_1 = shenfenzhenghao.outIDcard()
MEDICAL_TYPE_1 = '1' # 1-医院 2-药店
MEDICAL_NAME_1 = ran_strx("赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤", 2)
TOTAL_AMOUNT_1 = '1111.11'
# 往来单位2
MEDICAL_CODE_2 = shenfenzhenghao.outIDcard()
MEDICAL_TYPE_2 = '2'
MEDICAL_NAME_2 = ran_strx("赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤", 3)
TOTAL_AMOUNT_2 = '2222.22'
if (BUSINESS_TYPE_CODE == 'QHK10025' or BUSINESS_TYPE_CODE == 'QHK10029' or BUSINESS_TYPE_CODE == 'QHK9903' or BUSINESS_TYPE_CODE == 'QHK10033'):
# 往来单位1
MEDICAL_CODE_1 = '2200001'
MEDICAL_TYPE_1 = '1' # 1-医院 2-药店
# MEDICAL_NAME_1 = '上海市支付计划往来单位001'
MEDICAL_NAME_1 = ''
TOTAL_AMOUNT_1 = '1111.11'
# 往来单位2
MEDICAL_CODE_2 = '2200002'
MEDICAL_TYPE_2 = '2'
MEDICAL_NAME_2 = '上海市支付计划往来单位002'
TOTAL_AMOUNT_2 = '2222.22'
STATUS = '0'
DETAIL_UNIT_NO_1 = 'QHZS1' + otherStyleTime3 + str(n)
DETAIL_UNIT_NO_2 = 'QHZS2' + otherStyleTime3 + str(n)
ROLLBACK_REASON = ''
USER_ACCOUNT_SURPLUS = ''
SETTLEMENT_COUNT = ''
RECEIVE_UNIT_NAME = '上海市支付计划往来单位001收款'
RECEIVE_UNIT_NAME_2 = '上海市支付计划往来单位002收款'
START_TIME = datetime.datetime.fromisoformat('1900-01-01 00:00:00').strftime("%Y-%m-%d %H:%M:%S")
END_TIME = datetime.datetime.fromisoformat('1900-01-01 00:00:00').strftime("%Y-%m-%d %H:%M:%S")
# 收款方网点信息
RECEIVE_BANK_BRANCK_NAME = '工商银行青海支行'
RECEIVE_BANK_BRANCK_CODE = '123456123456'
# 收款方银行信息
RECEIVE_BANK_NAME = ''
RECEIVE_BANK_CODE = '102'
# 收款方账户信息
# RECEIVE_BANK_USER_NAME = '上海市支付计划往来单位001'
# RECEIVE_BANK_USER_CODE = '656789987678001'
RECEIVE_BANK_USER_NAME = ''
RECEIVE_BANK_USER_CODE = ''
# 收款方网点信息
RECEIVE_BANK_BRANCK_NAME_2 = '中国银行青海支行'
RECEIVE_BANK_BRANCK_CODE_2 = '123456123456'
# 收款方银行信息
RECEIVE_BANK_NAME_2 = ''
RECEIVE_BANK_CODE_2 = '104'
# 收款方账户信息
RECEIVE_BANK_USER_NAME_2 = '上海市支付计划往来单位002'
RECEIVE_BANK_USER_CODE_2 = '656789987678002'
ROLLBACK_TIME = datetime.datetime.fromisoformat('1900-01-01 00:00:00').strftime("%Y-%m-%d %H:%M:%S")
# 核发类型
ISSUED_TO_TYPE = ''
if BUSINESS_TYPE_CODE == 'QHK10028':
# 个人收款
ISSUED_TO_TYPE = '11'
# 单位+机构
ISSUED_TO_TYPE = '21'
ISSUED_TO_TYPE = '31'
# 收款方类型 1-单位 2-个人
FIELD04 = '1'
if ISSUED_TO_TYPE == '11':
FIELD04 = '2'
if (BUSINESS_TYPE_CODE == "QHK10027" or BUSINESS_TYPE_CODE == "QHK10029" or BUSINESS_TYPE_CODE == "QHK10031"):
FIELD04 = '2'
# 数据处理状态
DATA_DEAL_STAS = ''
# 银行支付时间
BANK_OFFER_TIME = datetime.datetime.fromisoformat('1900-01-01 00:00:00').strftime("%Y-%m-%d %H:%M:%S")
# 支付状态
BANK_DATA_STATUS = ''
# 失败原因
FAIL_REASON = ''
# 是否财务记账
JZ_STATUS = ''
# 凭证号
VOU_NO = ''
# 扣减金额
CHARGE_AMOUNT = '12.12'
# 辅助核算代码 名称
FIELD06 = 'asd名称'
# 摘要
SUMMARY = BUSINESS_TYPE_NAME + '测试导入'
# 预留金额
ADVANCE_AMOUNT_1 = '111.11'
ADVANCE_AMOUNT_2 = '222.22'
sqlz = "insert into qh_smrts_pay_plan_detail_mi values (%(FIELD04)s, %(BATCH_NO)s, %(YE_MONTH)s, %(INSURANCE_CODE)s, %(INSURANCE_NAME)s, %(BUSINESS_TYPE_CODE)s, %(BUSINESS_TYPE_NAME)s, %(AGENCY_CODE)s, %(AGENCY_NAME)s, %(MEDICAL_CODE)s, %(MEDICAL_TYPE)s, %(MEDICAL_NAME)s, %(TOTAL_AMOUNT)s, %(STATUS)s, %(DETAIL_UNIT_NO)s, %(ROLLBACK_REASON)s, %(USER_ACCOUNT_SURPLUS)s, %(SETTLEMENT_COUNT)s, %(RECEIVE_UNIT_NAME)s, %(START_TIME)s, %(END_TIME)s, %(RECEIVE_BANK_BRANCK_NAME)s, %(RECEIVE_BANK_BRANCK_CODE)s, %(RECEIVE_BANK_NAME)s, %(RECEIVE_BANK_CODE)s, %(RECEIVE_BANK_USER_NAME)s, %(RECEIVE_BANK_USER_CODE)s, %(ROLLBACK_TIME)s, %(ISSUED_TO_TYPE)s, %(DATA_DEAL_STAS)s, %(BANK_OFFER_TIME)s, %(BANK_DATA_STATUS)s, %(FAIL_REASON)s, %(JZ_STATUS)s, %(VOU_NO)s, %(CHARGE_AMOUNT)s, %(FIELD06)s, %(SUMMARY)s, %(ADVANCE_AMOUNT)s)"
valuez1 = {
"BATCH_NO":BATCH_NO,
"YE_MONTH":YE_MONTH,
"INSURANCE_CODE":INSURANCE_CODE,
"INSURANCE_NAME":INSURANCE_NAME,
"BUSINESS_TYPE_CODE":BUSINESS_TYPE_CODE,
"BUSINESS_TYPE_NAME":BUSINESS_TYPE_NAME,
"AGENCY_CODE":AGENCY_CODE,
"AGENCY_NAME":AGENCY_NAME,
"MEDICAL_CODE":MEDICAL_CODE_1,
"MEDICAL_TYPE":MEDICAL_TYPE_1,
"MEDICAL_NAME":MEDICAL_NAME_1,
"TOTAL_AMOUNT":TOTAL_AMOUNT_1,
"STATUS":STATUS,
"DETAIL_UNIT_NO":DETAIL_UNIT_NO_1,
"ROLLBACK_REASON":ROLLBACK_REASON,
"USER_ACCOUNT_SURPLUS":USER_ACCOUNT_SURPLUS,
"SETTLEMENT_COUNT":SETTLEMENT_COUNT,
"RECEIVE_UNIT_NAME":RECEIVE_UNIT_NAME,
"START_TIME":START_TIME,
"END_TIME":END_TIME,
"RECEIVE_BANK_BRANCK_NAME":RECEIVE_BANK_BRANCK_NAME,
"RECEIVE_BANK_BRANCK_CODE":RECEIVE_BANK_BRANCK_CODE,
"RECEIVE_BANK_NAME":RECEIVE_BANK_NAME,
"RECEIVE_BANK_CODE":RECEIVE_BANK_CODE,
"RECEIVE_BANK_USER_NAME":RECEIVE_BANK_USER_NAME,
"RECEIVE_BANK_USER_CODE":RECEIVE_BANK_USER_CODE,
"ROLLBACK_TIME":ROLLBACK_TIME,
"ISSUED_TO_TYPE":ISSUED_TO_TYPE,
"FIELD04":FIELD04,
"DATA_DEAL_STAS":DATA_DEAL_STAS,
"BANK_OFFER_TIME":BANK_OFFER_TIME,
"BANK_DATA_STATUS":BANK_DATA_STATUS,
"FAIL_REASON":FAIL_REASON,
"JZ_STATUS":JZ_STATUS,
"VOU_NO":VOU_NO,
"CHARGE_AMOUNT":CHARGE_AMOUNT,
"FIELD06":FIELD06,
"SUMMARY":SUMMARY,
"ADVANCE_AMOUNT":ADVANCE_AMOUNT_1
}
valuez2 = {
"BATCH_NO":BATCH_NO,
"YE_MONTH":YE_MONTH,
"INSURANCE_CODE":INSURANCE_CODE,
"INSURANCE_NAME":INSURANCE_NAME,
"BUSINESS_TYPE_CODE":BUSINESS_TYPE_CODE,
"BUSINESS_TYPE_NAME":BUSINESS_TYPE_NAME,
"AGENCY_CODE":AGENCY_CODE,
"AGENCY_NAME":AGENCY_NAME,
"MEDICAL_CODE":MEDICAL_CODE_2,
"MEDICAL_TYPE":MEDICAL_TYPE_2,
"MEDICAL_NAME":MEDICAL_NAME_2,
"TOTAL_AMOUNT":TOTAL_AMOUNT_2,
"STATUS":STATUS,
"DETAIL_UNIT_NO":DETAIL_UNIT_NO_2,
"ROLLBACK_REASON":ROLLBACK_REASON,
"USER_ACCOUNT_SURPLUS":USER_ACCOUNT_SURPLUS,
"SETTLEMENT_COUNT":SETTLEMENT_COUNT,
"RECEIVE_UNIT_NAME":RECEIVE_UNIT_NAME_2,
"START_TIME":START_TIME,
"END_TIME":END_TIME,
"RECEIVE_BANK_BRANCK_NAME":RECEIVE_BANK_BRANCK_NAME_2,
"RECEIVE_BANK_BRANCK_CODE":RECEIVE_BANK_BRANCK_CODE_2,
"RECEIVE_BANK_NAME":RECEIVE_BANK_NAME_2,
"RECEIVE_BANK_CODE":RECEIVE_BANK_CODE_2,
"RECEIVE_BANK_USER_NAME":RECEIVE_BANK_USER_NAME_2,
"RECEIVE_BANK_USER_CODE":RECEIVE_BANK_USER_CODE_2,
"ROLLBACK_TIME":ROLLBACK_TIME,
"ISSUED_TO_TYPE":ISSUED_TO_TYPE,
"FIELD04":FIELD04,
"DATA_DEAL_STAS":DATA_DEAL_STAS,
"BANK_OFFER_TIME":BANK_OFFER_TIME,
"BANK_DATA_STATUS":BANK_DATA_STATUS,
"FAIL_REASON":FAIL_REASON,
"JZ_STATUS":JZ_STATUS,
"VOU_NO":VOU_NO,
"CHARGE_AMOUNT":CHARGE_AMOUNT,
"FIELD06":FIELD06,
"SUMMARY":SUMMARY,
"ADVANCE_AMOUNT":ADVANCE_AMOUNT_2
}
cr.execute(sqlz, valuez1)
cr.execute(sqlz, valuez2)
# 只有两定机构、零星支付有孙表数据
if (BUSINESS_TYPE_CODE == 'QHK10025' or BUSINESS_TYPE_CODE == 'QHK10029' or BUSINESS_TYPE_CODE == 'QHK9903'):
# 孙表 除两定支付外，其他没有孙表数据
DETAIL_UNIT_NO_11 = DETAIL_UNIT_NO_1
MEDICAL_TYPE_CODE_11 = cl_QH_MEDICAL_CATEGORY[int(random.uniform(0,12))]
DETAIL_UNIT_NO_12 = DETAIL_UNIT_NO_1
MEDICAL_TYPE_CODE_12 = cl_QH_MEDICAL_CATEGORY[int(random.uniform(0,12))]
DETAIL_UNIT_NO_21 = DETAIL_UNIT_NO_2
MEDICAL_TYPE_CODE_21 = cl_QH_MEDICAL_CATEGORY[int(random.uniform(0,12))]
DETAIL_UNIT_NO_22 = DETAIL_UNIT_NO_2
MEDICAL_TYPE_CODE_22 = cl_QH_MEDICAL_CATEGORY[int(random.uniform(0,12))]
PERSON_TYPE = cl_QH_PERSONNEL_CATEGORY[int(random.uniform(0,9))]
PERSON_TIME = '3'
TOTAL_AMOUNT = '2133'
HIFP_PAY = '342'
HIFMI_PAY = '22'
CVLSERV_PAY = '33'
RETIRE_PAY = '344'
ACCT_PAY = '22'
HIFDM_PAY = '23'
THEIR_AGENCY_CODE = ''
THEIR_AGENCY_NAME = ''
SHIFP_PAY = '32'
HIFOB_PAY = '4454'
DMLC_PAY = '3654'
HIFES_PAY = '4333'
OTH_PAY = '5555'
STATUS = '0'
sqls = "insert into qh_smrts_pay_plan_item_mi values (%(DETAIL_UNIT_NO)s, %(MEDICAL_TYPE_CODE)s, %(PERSON_TYPE)s, %(PERSON_TIME)s, %(TOTAL_AMOUNT)s, %(HIFP_PAY)s, %(HIFMI_PAY)s, %(CVLSERV_PAY)s, %(RETIRE_PAY)s, %(ACCT_PAY)s, %(HIFDM_PAY)s, %(THEIR_AGENCY_CODE)s, %(THEIR_AGENCY_NAME)s, %(SHIFP_PAY)s, %(HIFOB_PAY)s, %(DMLC_PAY)s, %(HIFES_PAY)s, %(OTH_PAY)s, %(STATUS)s)"
values1 = {
"DETAIL_UNIT_NO":DETAIL_UNIT_NO_11,
"MEDICAL_TYPE_CODE":MEDICAL_TYPE_CODE_11,
"PERSON_TYPE":PERSON_TYPE,
"PERSON_TIME":PERSON_TIME,
"TOTAL_AMOUNT":TOTAL_AMOUNT,
"HIFP_PAY":HIFP_PAY,
"HIFMI_PAY":HIFMI_PAY,
"CVLSERV_PAY":CVLSERV_PAY,
"RETIRE_PAY":RETIRE_PAY,
"ACCT_PAY":ACCT_PAY,
"HIFDM_PAY":HIFDM_PAY,
"THEIR_AGENCY_CODE":THEIR_AGENCY_CODE,
"THEIR_AGENCY_NAME":THEIR_AGENCY_NAME,
"SHIFP_PAY":SHIFP_PAY,
"HIFOB_PAY":HIFOB_PAY,
"DMLC_PAY":DMLC_PAY,
"HIFES_PAY":HIFES_PAY,
"OTH_PAY":OTH_PAY,
"STATUS":STATUS
}
values2 = {
"DETAIL_UNIT_NO":DETAIL_UNIT_NO_12,
"MEDICAL_TYPE_CODE":MEDICAL_TYPE_CODE_12,
"PERSON_TYPE":PERSON_TYPE,
"PERSON_TIME":PERSON_TIME,
"TOTAL_AMOUNT":TOTAL_AMOUNT,
"HIFP_PAY":HIFP_PAY,
"HIFMI_PAY":HIFMI_PAY,
"CVLSERV_PAY":CVLSERV_PAY,
"RETIRE_PAY":RETIRE_PAY,
"ACCT_PAY":ACCT_PAY,
"HIFDM_PAY":HIFDM_PAY,
"THEIR_AGENCY_CODE":THEIR_AGENCY_CODE,
"THEIR_AGENCY_NAME":THEIR_AGENCY_NAME,
"SHIFP_PAY":SHIFP_PAY,
"HIFOB_PAY":HIFOB_PAY,
"DMLC_PAY":DMLC_PAY,
"HIFES_PAY":HIFES_PAY,
"OTH_PAY":OTH_PAY,
"STATUS":STATUS
}
values11 = {
"DETAIL_UNIT_NO":DETAIL_UNIT_NO_21,
"MEDICAL_TYPE_CODE":MEDICAL_TYPE_CODE_21,
"PERSON_TYPE":PERSON_TYPE,
"PERSON_TIME":PERSON_TIME,
"TOTAL_AMOUNT":TOTAL_AMOUNT,
"HIFP_PAY":HIFP_PAY,
"HIFMI_PAY":HIFMI_PAY,
"CVLSERV_PAY":CVLSERV_PAY,
"RETIRE_PAY":RETIRE_PAY,
"ACCT_PAY":ACCT_PAY,
"HIFDM_PAY":HIFDM_PAY,
"THEIR_AGENCY_CODE":THEIR_AGENCY_CODE,
"THEIR_AGENCY_NAME":THEIR_AGENCY_NAME,
"SHIFP_PAY":SHIFP_PAY,
"HIFOB_PAY":HIFOB_PAY,
"DMLC_PAY":DMLC_PAY,
"HIFES_PAY":HIFES_PAY,
"OTH_PAY":OTH_PAY,
"STATUS":STATUS
}
values22 = {
"DETAIL_UNIT_NO":DETAIL_UNIT_NO_22,
"MEDICAL_TYPE_CODE":MEDICAL_TYPE_CODE_22,
"PERSON_TYPE":PERSON_TYPE,
"PERSON_TIME":PERSON_TIME,
"TOTAL_AMOUNT":TOTAL_AMOUNT,
"HIFP_PAY":HIFP_PAY,
"HIFMI_PAY":HIFMI_PAY,
"CVLSERV_PAY":CVLSERV_PAY,
"RETIRE_PAY":RETIRE_PAY,
"ACCT_PAY":ACCT_PAY,
"HIFDM_PAY":HIFDM_PAY,
"THEIR_AGENCY_CODE":THEIR_AGENCY_CODE,
"THEIR_AGENCY_NAME":THEIR_AGENCY_NAME,
"SHIFP_PAY":SHIFP_PAY,
"HIFOB_PAY":HIFOB_PAY,
"DMLC_PAY":DMLC_PAY,
"HIFES_PAY":HIFES_PAY,
"OTH_PAY":OTH_PAY,
"STATUS":STATUS
}
cr.execute(sqls, values1)
cr.execute(sqls, values2)
cr.execute(sqls, values11)
cr.execute(sqls, values22)
no += 1
n -= 1
cr.close()
db.commit()
db.close()
endtime = datetime.datetime.now()
print("生成时间：" + otherStyleTime)
print("耗时H：" + str(round((endtime - starttime).seconds / 3600, 2)))
