Skip to content
GitLab
Switch branch/tag
Find file BlameHistoryPermalink
sql_del_branch_info.py 56.73 KiB
Open in Web IDE
1 
# -*- encoding=utf8 -*-
2
3
4 
import pymysql
5
6 
from common.common_func import commonFuc
7 
from common.fileUtls import FileUtils
8
9
10 
class delData(object):
11
12 
    def Delete_branch_by_id(self,type=1,id='id'):
13 
        # connection = pymysql.connect(host="10.17.65.108", user="root", password="Cmic.2023", database="spd3_herp_test2",
14 
        #                              charset="utf8")
15 
        # 内网切换数据库
16 
        connection = pymysql.connect(host="10.17.65.49", port=30017, user="root", password="H-pV2d-1-N0z",
17 
                                     database="spd3_herp_test2",
18 
                                     charset="utf8")
19 
        cursor = connection.cursor()
20 
        # 删除新增的院区数据,减少垃圾数据的产生,保证脚本下次还可以正常运行,接口脚本每次都使用新增数据
21 
        # sql = "DELETE from mcms_branch_info where id = '%s';" % id
22 
        if type==1:
23 
            sql = "DELETE  from mcms_branch_info where  name  like '%东土城路院区_____';"
24 
        elif type==2:
25 
            sql = "DELETE  from mcms_branch_info where  name  like '%东土城路院区______';"
26 
        elif type==3:
27 
            sql = "DELETE from mcms_branch_info where id = '%s';" % id
28 
        else:
29 
            sql='sql'
30 
        print(sql)
31 
        cursor.execute(sql)
32 
        cursor.execute("commit;")
33 
        print('内网院区数据已删除成功')
34 
        cursor.close()
35 
        # 删除融合外网的院区信息
36 
        connection = pymysql.connect(host="10.17.65.108", user="root", password="Cmic.2023",
37 
                                     database="spd3_platform_test2",
38 
                                     charset="utf8")
39 
        cursor = connection.cursor()
40 
        # 删除新增的院区数据,减少垃圾数据的产生,保证脚本下次还可以正常运行,接口脚本每次都使用新增数据
41 
        # sql = "DELETE from mcms_branch_info where id = '%s';" % id
42 
        if type == 1:
43 
            sql = "DELETE  from mcms_branch_info where  name  like '%东土城路院区_____';"
44 
        elif type == 2:
45 
            sql = "DELETE  from mcms_branch_info where  name  like '%东土城路院区______';"
46 
        elif type == 3:
47 
            sql = "DELETE from mcms_branch_info where id = '%s';" % id
48 
        else:
49 
            sql='sql'
50 
        print(sql)
51 
        cursor.execute(sql)
52 
        cursor.execute("commit;")
53 
        print('外网院区数据已删除成功')
54 
        cursor.close()
55
56 
    def Delete_goods_change_info(self):
57 
        # connection = pymysql.connect(host="10.17.65.108", user="root", password="Cmic.2023", database="spd3_herp_test2",
58 
        #                              charset="utf8")
59 
        # 内网切换数据库
60 
        connection = pymysql.connect(host="10.17.65.49", port=30017, user="root", password="H-pV2d-1-N0z",
61 
                                     database="spd3_herp_test2",
62 
                                     charset="utf8")
63 
        cursor = connection.cursor()
64 
        # 删除产品信息修改数据
65 
        sql = "DELETE from mcms_goods_chg_main where create_user='UID-050151';"
66 
        print(sql)
67 
        cursor.execute(sql)
68 
        cursor.execute("commit;")
69 
        print('产品调价和产品信息变更数据已删除成功')
70 
        cursor.close()
7172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140
def Delete_sys_config(self): # connection = pymysql.connect(host="10.17.65.108", user="root", password="Cmic.2023", database="spd3_herp_test2", # charset="utf8") # 内网切换数据库 connection = pymysql.connect(host="10.17.65.49", port=30017, user="root", password="H-pV2d-1-N0z", database="spd3_herp_test2", charset="utf8") cursor = connection.cursor() # 删除产品信息修改数据 sql = "delete from sys_config where create_user = 'UID-050151';" print(sql) cursor.execute(sql) cursor.execute("commit;") print('系统配置信息已经删除成功 ') cursor.close() def Delete_sysrAtomDet(self): # connection = pymysql.connect(host="10.17.65.108", user="root", password="Cmic.2023", database="spd3_herp_test2", # charset="utf8") # 内网切换数据库 connection = pymysql.connect(host="10.17.65.49", port=30017, user="root", password="H-pV2d-1-N0z", database="spd3_herp_test2", charset="utf8") cursor = connection.cursor() # 删除产品信息修改数据 sql = "delete from sysr_atom_det where create_user='UID-050151';" print(sql) cursor.execute(sql) cursor.execute("commit;") print('编码规则信息已经删除成功 ') cursor.close() def Delete_sys_message_template(self): # connection = pymysql.connect(host="10.17.65.108", user="root", password="Cmic.2023", database="spd3_herp_test2", # charset="utf8") # 内网切换数据库 connection = pymysql.connect(host="10.17.65.49", port=30017, user="root", password="H-pV2d-1-N0z", database="spd3_herp_test2", charset="utf8") cursor = connection.cursor() # 删除产品信息修改数据 sql = "delete from sys_message_type where create_user='UID-050151' and template_type_name='test2';" print(sql) cursor.execute(sql) cursor.execute("commit;") print('消息通知模板类型已经删除成功 ') cursor.close() def Delete_goods_pur_template(self): # connection = pymysql.connect(host="10.17.65.108", user="root", password="Cmic.2023", database="spd3_herp_test2", # charset="utf8") # 内网切换数据库 connection = pymysql.connect(host="10.17.65.49", port=30017, user="root", password="H-pV2d-1-N0z", database="spd3_herp_test2", charset="utf8") cursor = connection.cursor() # 删除产品信息修改数据 sql = "delete from mcms_goods_pur_template where create_user ='UID-050151';" print(sql) cursor.execute(sql) cursor.execute("commit;") print('请领模板删除成功 ') cursor.close() def get_shelf_name_version(self): # connection = pymysql.connect(host="10.17.65.108", user="root", password="Cmic.2023", database="spd3_herp_test2", # charset="utf8") # 内网切换数据库 connection = pymysql.connect(host="10.17.65.49", port=30017, user="root", password="H-pV2d-1-N0z",
141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210
database="spd3_herp_test2", charset="utf8") cursor = connection.cursor() # 获取库区version sql = "select version from mcms_stock_shelf where shelf_name like '%自动化测试%';" print(sql) cursor.execute(sql) results = cursor.fetchone() str1 = str(results) new_str1 = str1.replace('(', '') new_str1 = new_str1.replace(')', '') new_str1 = new_str1.replace(',', '') print(new_str1) cursor.close() return new_str1 def Delete_probeInfo(self): # connection = pymysql.connect(host="10.17.65.108", user="root", password="Cmic.2023", database="spd3_herp_test2", # charset="utf8") # 内网切换数据库 connection = pymysql.connect(host="10.17.65.49", port=30017, user="root", password="H-pV2d-1-N0z", database="spd3_herp_test2", charset="utf8") cursor = connection.cursor() # 删除货位信息 sql = "delete from mcms_stock_shelf where shelf_no like '%自动化测试-2%';" print(sql) cursor.execute(sql) cursor.execute("commit;") print('货位信息删除成功 ') # 删除库区信息 sql = "delete from mcms_stock_shelf where shelf_name like '%自动化测试%';" print(sql) cursor.execute(sql) cursor.execute("commit;") print('库区信息删除成功 ') # 删除多余库房信息 # sql = "delete from mcms_stock_info where stock_name like '%东土城路院区_____中心库房';" # print(sql) # cursor.execute(sql) # cursor.execute("commit;") # print('多余库房信息删除成功 ') cursor.close() def Delete_probeInfo_other(self,type=1,branch_id='branch_id'): # connection = pymysql.connect(host="10.17.65.108", user="root", password="Cmic.2023", database="spd3_herp_test2", # charset="utf8") # 内网切换数据库 connection = pymysql.connect(host="10.17.65.49", port=30017, user="root", password="H-pV2d-1-N0z", database="spd3_herp_test2", charset="utf8") cursor = connection.cursor() # 删除多余库房信息 if type==1: sql = "delete from mcms_stock_info where stock_name like '%东土城路院区_____中心库房';" elif type==2: sql = "delete from mcms_stock_info where stock_name like '%东土城路院区______中心库房';" else: sql = "delete from mcms_stock_info where branch_id = '%s';" % branch_id print(sql) cursor.execute(sql) cursor.execute("commit;") print('多余库房信息删除成功 ') cursor.close() def Delete_role(self): # connection = pymysql.connect(host="10.17.65.108", user="root", password="Cmic.2023", database="spd3_herp_test2", # charset="utf8")
211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280
# 内网切换数据库 connection = pymysql.connect(host="10.17.65.49", port=30017, user="root", password="H-pV2d-1-N0z", database="spd3_herp_test2", charset="utf8") cursor = connection.cursor() # 删除角色信息 sql = "delete from sys_role where cname like '%自动化测试2%';" print(sql) cursor.execute(sql) cursor.execute("commit;") print('角色信息删除成功 ') cursor.close() def Delete_user(self): # connection = pymysql.connect(host="10.17.65.108", user="root", password="Cmic.2023", database="spd3_herp_test2", # charset="utf8") # 内网切换数据库 connection = pymysql.connect(host="10.17.65.49", port=30017, user="root", password="H-pV2d-1-N0z", database="spd3_herp_test2", charset="utf8") cursor = connection.cursor() # 删除用户信息 sql = "delete from sys_user where user_code like '%自动化测试%';" print(sql) cursor.execute(sql) cursor.execute("commit;") print('用户信息删除成功 ') cursor.close() def Delete_lcTask(self): # connection = pymysql.connect(host="10.17.65.108", user="root", password="Cmic.2023", database="spd3_herp_test2", # charset="utf8") # 内网切换数据库 connection = pymysql.connect(host="10.17.65.49", port=30017, user="root", password="H-pV2d-1-N0z", database="spd3_herp_test2", charset="utf8") cursor = connection.cursor() # 删除用户信息 sql = "delete from mcms_temporary_purchase_detail where pid in(select id from mcms_temporary_purchase where task_name like '%自动化测试%');" print(sql) cursor.execute(sql) cursor.execute("commit;") print('临采任务子表信息删除成功 ') sql = "delete from mcms_temporary_purchase where task_name like '%自动化测试%';" print(sql) cursor.execute(sql) cursor.execute("commit;") print('临采任务主表信息删除成功 ') cursor.close() def Delete_ld_directory(self): # connection = pymysql.connect(host="10.17.65.108", user="root", password="Cmic.2023", database="spd3_herp_test2", # charset="utf8") # 内网切换数据库 connection = pymysql.connect(host="10.17.65.49", port=30017, user="root", password="H-pV2d-1-N0z", database="spd3_herp_test2", charset="utf8") cursor = connection.cursor() # 删除用户信息 sql = "delete from mcms_vbp_detail where pid in(select id from mcms_vbp where project_name like '%自动化测试%');" print(sql) cursor.execute(sql) cursor.execute("commit;") print('带量项目子表信息删除成功 ') sql = "delete from mcms_vbp where project_name like '%自动化测试%';" print(sql) cursor.execute(sql) cursor.execute("commit;") print('带量项目主表信息删除成功 ') cursor.close()
281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350
def Del_goods_info(self, goodsid): # 内网的 # connection = pymysql.connect(host="10.17.65.108", user="root", password="Cmic.2023", database="spd3_herp_test2", # charset="utf8") # 内网切换数据库 connection = pymysql.connect(host="10.17.65.49", port=30017, user="root", password="H-pV2d-1-N0z", database="spd3_herp_test2", charset="utf8") cursor = connection.cursor() # 删除产品信息 sql = "delete from mcms_goods_info where id='%s';" % goodsid print(sql) cursor.execute(sql) cursor.execute("commit;") print('内网产品信息删除成功 ') cursor.close() # 外网的 connection = pymysql.connect(host="10.17.65.108", user="root", password="Cmic.2023", database="spd_ybtest_hdi", charset="utf8") cursor = connection.cursor() sql = "delete from mcms_goods_info where id='%s';" % goodsid print(sql) cursor.execute(sql) cursor.execute("commit;") print('外网产品信息删除成功 ') cursor.close() def Del_supply_relation(self): # connection = pymysql.connect(host="10.17.65.108", user="root", password="Cmic.2023", database="spd3_herp_test2", # charset="utf8") # 内网切换数据库 connection = pymysql.connect(host="10.17.65.49", port=30017, user="root", password="H-pV2d-1-N0z", database="spd3_herp_test2", charset="utf8") cursor = connection.cursor() # 删除供应关系 info = FileUtils().r_info8('b5_spd3_core_business_flow', "供货关系申请2", 'message') try: e_corpId = info['e_corpId'] sql = "delete from supply_relation where prov_id in(select id from bas_company_info where id = '%s');" % e_corpId print(sql) except: sql = "delete from supply_relation where prov_id in(select id from bas_company_info where id = 'aaaa');" print(sql) else: sql = "delete from supply_relation where prov_id in (select id from bas_company_info where cname like '%国药集团公司_____%');" cursor.execute(sql) cursor.execute("commit;") print('内网供应关系删除成功 ') cursor.close() def Del_supply_relation1(self): # connection = pymysql.connect(host="10.17.65.108", user="root", password="Cmic.2023", database="spd3_herp_test2", # charset="utf8") # 内网切换数据库 connection = pymysql.connect(host="10.17.65.49", port=30017, user="root", password="H-pV2d-1-N0z", database="spd3_herp_test2", charset="utf8") cursor = connection.cursor() # 删除供应关系 info = FileUtils().r_info8('b5_spd3_core_business_flow', "供货关系申请2", 'message') e_corpId = info['e_corpId'] sql = "delete from supply_relation where prov_id in (select id from bas_company_info where cname like '%%国药集团公司_____%%') and prov_id!='%s';"%e_corpId cursor.execute(sql) cursor.execute("commit;") print('内网供应关系删除成功 ') cursor.close() def Del_reporter_data(self,type=1,branch_id='branch_id'): connection = pymysql.connect(host="10.17.65.49", port=30017, user="root", password="H-pV2d-1-N0z",
351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420
database="spd3_report_hospitaltest2", charset="utf8") cursor = connection.cursor() if type==1: branch_id = FileUtils().r_info8('b2_herp3_bs', "院区新增", 'message')['branch_id'] sql = "delete from mcms_finance_daybook where branch_id= '%s';" % branch_id cursor.execute(sql) cursor.execute("commit;") sql = "delete from mcms_finance_psi where branch_id= '%s';" % branch_id cursor.execute(sql) cursor.execute("commit;") sql = "delete from mcms_psi_detail where branch_id= '%s';" % branch_id cursor.execute(sql) cursor.execute("commit;") sql = "delete from mcms_stockpile_image where branch_id= '%s';" % branch_id cursor.execute(sql) cursor.execute("commit;") sql = "delete from mcms_xh_month_report where branch_id= '%s';" % branch_id cursor.execute(sql) cursor.execute("commit;") print('报表数据删除成功') cursor.close() def Delete_business_Data(self,type=1,branch_id='branch_id'): # 删除业务数据，包括采购，出入库，结算等 # connection = pymysql.connect(host="10.17.65.108", user="root", password="Cmic.2023", # database="spd3_herp_test2", # charset="utf8") # 内网切换数据库 connection = pymysql.connect(host="10.17.65.49", port=30017, user="root", password="H-pV2d-1-N0z", database="spd3_herp_test2", charset="utf8") cursor = connection.cursor() if type==1: branch_id = FileUtils().r_info8('b2_herp3_bs', "院区新增", 'message')['branch_id'] hosGoodsId1 = FileUtils().r_info8("b5_spd3_core_business_flow", "产品审核信息", 'message1')['hosGoodsId'] hosGoodsId2 = FileUtils().r_info8("b5_spd3_core_business_flow", "产品审核信息", 'message2')['hosGoodsId'] hosGoodsId3 = FileUtils().r_info8("b5_spd3_core_business_flow", "产品审核信息", 'message3')['hosGoodsId'] # info = FileUtils().r_info8('b2_herp3_bs', "用户名信息", 'message3') username = info['username1'] # 条码 sql1 = "delete from mcms_pkg where branch_id='%s';" % branch_id sql2 = "delete from mcms_pkg_batch where hos_goods_id in('%s','%s','%s') and create_user !=(select id from sys_user where user_code='%s');" % ( hosGoodsId1, hosGoodsId2, hosGoodsId3,username) sql3 = "delete from hdi_barcode where branch_id='%s';" % branch_id # sql4 = "delete from cabinet_epc_info where branch_id=%s" % branch_id cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute("commit;") # 批次 sql = "delete from mcms_hos_batch where branch_id='%s';" % branch_id cursor.execute(sql) cursor.execute("commit;") # 库存 sql1 = "delete from mcms_stockpile where branch_id='%s';" % branch_id sql2 = "delete from mcms_stockpile_taking where hos_goods_id in('%s','%s','%s');" % ( hosGoodsId1, hosGoodsId2, hosGoodsId3) sql3 = "delete from mcms_stockpile_flow where branch_id='%s';" % branch_id cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3)
421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490
cursor.execute("commit;") # 采购 sql = "delete from mcms_purchase_detail where order_no in(select order_no from mcms_purchase where branch_id='%s');" % branch_id cursor.execute(sql) cursor.execute("commit;") sql = "delete from mcms_pur_plan_detail where prov_name like '%国药集团公司_____';" cursor.execute(sql) cursor.execute("commit;") sql = "delete from mcms_pur_plan where branch_id= '%s';" % branch_id cursor.execute(sql) cursor.execute("commit;") sql = "delete from mcms_purchase where branch_id= '%s';" % branch_id cursor.execute(sql) cursor.execute("commit;") dept1 = FileUtils().r_info8("b2_herp3_bs", "所有科室id", 'message6')['deptid1'] dept2 = FileUtils().r_info8("b2_herp3_bs", "所有科室id", 'message6')['deptid2'] dept3 = FileUtils().r_info8("b2_herp3_bs", "所有科室id", 'message6')['deptid3'] dept4 = FileUtils().r_info8("b2_herp3_bs", "所有科室id", 'message6')['deptid4'] # 拣货单 sql4 = "delete from mcms_pick_detail where pid in(select id from mcms_pick where branch_id='%s');" % branch_id sql1 = "delete from mcms_pick where branch_id= '%s';" % branch_id sql2 = "delete from mcms_pick_gen where dept_id in('%s','%s','%s','%s')" % (dept1, dept2, dept3, dept4) sql3 = "delete from mcms_pick_batch where hos_goods_id in('%s','%s','%s')" % ( hosGoodsId1, hosGoodsId2, hosGoodsId3) cursor.execute(sql4) cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute("commit;") # 验收单 sql1 = "delete from mcms_check where branch_id ='%s';" % branch_id sql2 = "delete from mcms_check_batch where hos_goods_id in('%s','%s','%s')" % ( hosGoodsId1, hosGoodsId2, hosGoodsId3) cursor.execute(sql1) cursor.execute(sql2) cursor.execute("commit;") # 配送单 sql1 = "delete from hdi_distr_barcode where pid in(select id FROM hdi_distr where branch_id='%s');" % branch_id sql2 = "delete from hdi_distr where branch_id='%s';" % branch_id sql3 = "delete from hdi_distr_detail where hos_goods_id in('%s','%s','%s')" % ( hosGoodsId1, hosGoodsId2, hosGoodsId3) cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute("commit;") # 单据 sql1 = "delete from mcms_psi where target_branch_id='%s';" % branch_id sql6 = "delete from mcms_psi where target_dept_name like '%国药集团公司_____';" sql2 = "delete from mcms_psi_batch where hos_goods_id in('%s','%s','%s');" % ( hosGoodsId1, hosGoodsId2, hosGoodsId3) sql3 = "delete from mcms_psi_dept where target_branch_id='%s';" % branch_id sql4 = "delete from mcms_psi_dept_batch where hos_goods_id in('%s','%s','%s');" % ( hosGoodsId1, hosGoodsId2, hosGoodsId3) sql5 = "delete from his_order where branch_id='%s';" % branch_id cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute(sql4) cursor.execute(sql5) cursor.execute(sql6) cursor.execute("commit;") # 手术通知单
491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560
sql1 = "delete from mcms_operation_apply where branch_id='%s';" % branch_id sql2 = "delete from mcms_operation_apply_list where hos_goods_id in('%s','%s','%s');" % ( hosGoodsId1, hosGoodsId2, hosGoodsId3) sql3 = "delete from mcms_operation_notice where branch_id='%s';" % branch_id sql4 = "delete from mcms_operation_notice_list where hos_goods_id in('%s','%s','%s');" % ( hosGoodsId1, hosGoodsId2, hosGoodsId3) cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute(sql4) cursor.execute("commit;") # 结算 sql1 = "delete from mcms_out_balance where branch_id='%s';" % branch_id sql2 = "delete from mcms_out_balance_detail where prov_name like '%国药集团公司_____';" sql3 = "delete from mcms_out_un_balance where branch_id='%s';" % branch_id cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute("commit;") # 发票 sql1 = "delete from mcms_invoice where prov_name like '%国药集团公司_____';" sql2 = "delete from mcms_invoice_comment where pid in(select invoice_id from mcms_invoice_ext_detail where pid in(select id from mcms_invoice_ext where prov_name like '%国药集团公司_____'));" sql3 = "delete from mcms_invoice_ext_detail where pid in(select id from mcms_invoice_ext where prov_name like '%国药集团公司_____');" sql4 = "delete from mcms_invoice_ext where prov_name like '%国药集团公司_____';" sql5 = "delete from mcms_invoice_detail where branch_id='%s';" % branch_id cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute(sql4) cursor.execute(sql5) cursor.execute("commit;") # 退货 sql1 = "delete from yb_return_batch where pid in(select id from yb_return where prov_name like '%国药集团公司_____');" sql2 = "delete from yb_return where prov_name like '%国药集团公司_____';" cursor.execute(sql1) cursor.execute(sql2) cursor.execute("commit;") # 请领单 sql1 = "delete from mcms_dept_buy_detail where pid in (select id from mcms_dept_buy where branch_id='%s');" % branch_id sql2 = "delete from mcms_dept_buy where branch_id='%s';" % branch_id sql3 = "delete from mcms_dept_buy_car where branch_id='%s';" % branch_id sql4 = "delete from mcms_dept_patient where id ='自动化测试';" cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute(sql4) cursor.execute("commit;") # 报表 sql1 = "delete from portal_card where branch_id='%s';" % branch_id sql2 = "delete from portal_chart where branch_id='%s';" % branch_id sql3 = "delete from portal_worklist where branch_id='%s';" % branch_id cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute("commit;") print('业务数据，包括采购，出入库，结算等数据删除成功') cursor.close() def Delete_business_Data_current_branch(self,type=1,branch_id='branch_id'): # 删除业务数据，包括采购，出入库，结算等 # connection = pymysql.connect(host="10.17.65.108", user="root", password="Cmic.2023", # database="spd3_herp_test2", # charset="utf8") # 内网切换数据库 connection = pymysql.connect(host="10.17.65.49", port=30017, user="root", password="H-pV2d-1-N0z", database="spd3_herp_test2", charset="utf8") cursor = connection.cursor()
561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630
if type==1: branch_id = FileUtils().r_info8('b2_herp3_bs', "院区新增", 'message')['branch_id'] hosGoodsId1 = FileUtils().r_info8("b5_spd3_core_business_flow", "产品审核信息", 'message1')['hosGoodsId'] hosGoodsId2 = FileUtils().r_info8("b5_spd3_core_business_flow", "产品审核信息", 'message2')['hosGoodsId'] hosGoodsId3 = FileUtils().r_info8("b5_spd3_core_business_flow", "产品审核信息", 'message3')['hosGoodsId'] # 包库存 sql1 = "delete from mcms_pkg where branch_id='%s';" % branch_id sql2 = "delete from mcms_pkg_batch where hos_goods_id in('%s','%s','%s');" % ( hosGoodsId1, hosGoodsId2, hosGoodsId3) sql3 = "delete from hdi_barcode where branch_id='%s';" % branch_id sql4 = "delete from cabinet_epc_info where branch_id=%s" % branch_id cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute("commit;") # 批次 sql = "delete from mcms_hos_batch where branch_id='%s';" % branch_id cursor.execute(sql) cursor.execute("commit;") # 库存 sql1 = "delete from mcms_stockpile where branch_id='%s';" % branch_id sql2 = "delete from mcms_stockpile_taking where hos_goods_id in('%s','%s','%s');" % ( hosGoodsId1, hosGoodsId2, hosGoodsId3) sql3 = "delete from mcms_stockpile_flow where branch_id='%s';" % branch_id cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute("commit;") # 采购 sql = "delete from mcms_purchase_detail where order_no in(select order_no from mcms_purchase where branch_id='%s');" % branch_id cursor.execute(sql) cursor.execute("commit;") sql = "delete from mcms_pur_plan_detail where prov_name like '%国药集团公司_____';" cursor.execute(sql) cursor.execute("commit;") sql = "delete from mcms_pur_plan where branch_id= '%s';" % branch_id cursor.execute(sql) cursor.execute("commit;") sql = "delete from mcms_purchase where branch_id= '%s';" % branch_id cursor.execute(sql) cursor.execute("commit;") dept1 = FileUtils().r_info8("b2_herp3_bs", "所有科室id", 'message6')['deptid1'] dept2 = FileUtils().r_info8("b2_herp3_bs", "所有科室id", 'message6')['deptid2'] dept3 = FileUtils().r_info8("b2_herp3_bs", "所有科室id", 'message6')['deptid3'] dept4 = FileUtils().r_info8("b2_herp3_bs", "所有科室id", 'message6')['deptid4'] # 拣货单 sql4 = "delete from mcms_pick_detail where pid in(select id from mcms_pick where branch_id='%s');" % branch_id sql1 = "delete from mcms_pick where branch_id= '%s';" % branch_id sql2 = "delete from mcms_pick_gen where dept_id in('%s','%s','%s','%s')" % (dept1, dept2, dept3, dept4) sql3 = "delete from mcms_pick_batch where hos_goods_id in('%s','%s','%s')" % ( hosGoodsId1, hosGoodsId2, hosGoodsId3) cursor.execute(sql4) cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute("commit;") # 验收单 sql1 = "delete from mcms_check where branch_id ='%s';" % branch_id sql2 = "delete from mcms_check_batch where hos_goods_id in('%s','%s','%s')" % ( hosGoodsId1, hosGoodsId2, hosGoodsId3) cursor.execute(sql1) cursor.execute(sql2) cursor.execute("commit;")
631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700
# 配送单 sql1 = "delete from hdi_distr_barcode where pid in(select id FROM hdi_distr where branch_id='%s');" % branch_id sql2 = "delete from hdi_distr where branch_id='%s';" % branch_id sql3 = "delete from hdi_distr_detail where hos_goods_id in('%s','%s','%s')" % ( hosGoodsId1, hosGoodsId2, hosGoodsId3) cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute("commit;") # 单据 sql1 = "delete from mcms_psi where target_branch_id='%s';" % branch_id sql6 = "delete from mcms_psi where target_dept_name like '%国药集团公司_____';" sql2 = "delete from mcms_psi_batch where hos_goods_id in('%s','%s','%s');" % ( hosGoodsId1, hosGoodsId2, hosGoodsId3) sql3 = "delete from mcms_psi_dept where target_branch_id='%s';" % branch_id sql4 = "delete from mcms_psi_dept_batch where hos_goods_id in('%s','%s','%s');" % ( hosGoodsId1, hosGoodsId2, hosGoodsId3) sql5 = "delete from his_order where branch_id='%s';" % branch_id cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute(sql4) cursor.execute(sql5) cursor.execute(sql6) cursor.execute("commit;") # 手术通知单 sql1 = "delete from mcms_operation_apply where branch_id='%s';" % branch_id sql2 = "delete from mcms_operation_apply_list where hos_goods_id in('%s','%s','%s');" % ( hosGoodsId1, hosGoodsId2, hosGoodsId3) sql3 = "delete from mcms_operation_notice where branch_id='%s';" % branch_id sql4 = "delete from mcms_operation_notice_list where hos_goods_id in('%s','%s','%s');" % ( hosGoodsId1, hosGoodsId2, hosGoodsId3) cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute(sql4) cursor.execute("commit;") # 结算 sql1 = "delete from mcms_out_balance where branch_id='%s';" % branch_id sql2 = "delete from mcms_out_balance_detail where prov_name like '%国药集团公司_____';" sql3 = "delete from mcms_out_un_balance where branch_id='%s';" % branch_id cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute("commit;") # 发票 sql1 = "delete from mcms_invoice where prov_name like '%国药集团公司_____';" sql2 = "delete from mcms_invoice_comment where pid in(select invoice_id from mcms_invoice_ext_detail where pid in(select id from mcms_invoice_ext where prov_name like '%国药集团公司_____'));" sql3 = "delete from mcms_invoice_ext_detail where pid in(select id from mcms_invoice_ext where prov_name like '%国药集团公司_____');" sql4 = "delete from mcms_invoice_ext where prov_name like '%国药集团公司_____';" sql5 = "delete from mcms_invoice_detail where branch_id='%s';" % branch_id cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute(sql4) cursor.execute(sql5) cursor.execute("commit;") # 退货 sql1 = "delete from yb_return_batch where pid in(select id from yb_return where prov_name like '%国药集团公司_____');" sql2 = "delete from yb_return where prov_name like '%国药集团公司_____';" cursor.execute(sql1) cursor.execute(sql2) cursor.execute("commit;") # 请领单 sql1 = "delete from mcms_dept_buy_detail where pid in (select id from mcms_dept_buy where branch_id='%s');" % branch_id sql2 = "delete from mcms_dept_buy where branch_id='%s';" % branch_id sql3 = "delete from mcms_dept_buy_car where branch_id='%s';" % branch_id
701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770
sql4 = "delete from mcms_dept_patient where id ='自动化测试';" cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute(sql4) cursor.execute("commit;") # 报表 sql1 = "delete from portal_card where branch_id='%s';" % branch_id sql2 = "delete from portal_chart where branch_id='%s';" % branch_id sql3 = "delete from portal_worklist where branch_id='%s';" % branch_id cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute("commit;") print('业务数据，包括采购，出入库，结算等数据删除成功') cursor.close() def Delete_yb_logic_stock(self): connection = pymysql.connect(host="10.17.65.108", user="root", password="Cmic.2023", database="spd3_platform_test2", charset="utf8") cursor = connection.cursor() sql1 = "delete from yb_logic_stock where logic_name like '%自动化测试_____';" print(sql1) cursor.execute(sql1) cursor.execute("commit;") print('外网边仓删除成功') cursor.close() # connection = pymysql.connect(host="10.17.65.108", user="root", password="Cmic.2023", # database="spd3_herp_test2", # charset="utf8") # 内网切换数据库 connection = pymysql.connect(host="10.17.65.49", port=30017, user="root", password="H-pV2d-1-N0z", database="spd3_herp_test2", charset="utf8") cursor = connection.cursor() branch_id = FileUtils().r_info8('b2_herp3_bs', "院区新增", 'message')['branch_id'] sql1 = "delete FROM yb_direct WHERE branch_id ='%s';" % branch_id print(sql1) cursor.execute(sql1) cursor.execute("commit;") print('内网边仓删除成功') cursor.close() def Delete_business_Data_All(self): # 获取院区数据 # connection = pymysql.connect(host="10.17.65.108", user="root", password="Cmic.2023", # database="spd3_herp_test2", # charset="utf8") # 内网切换数据库 connection = pymysql.connect(host="10.17.65.49", port=30017, user="root", password="H-pV2d-1-N0z", database="spd3_herp_test2", charset="utf8") cursor = connection.cursor() # 获取库区version branch_id = "select id from mcms_branch_info" # 条码 sql1 = "delete from mcms_pkg_batch where pkg_id in(select id from mcms_pkg where branch_id not in(%s));" % branch_id sql2 = "delete from mcms_pkg where branch_id not in(%s);" % branch_id sql3 = "delete from hdi_barcode where branch_id not in(%s);" % branch_id # sql4 = "delete from cabinet_epc_info where branch_id not in%s" % branch_id cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute("commit;") # 批次 sql = "delete from mcms_hos_batch where branch_id not in(%s);" % branch_id cursor.execute(sql)
771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840
cursor.execute("commit;") # 库存 sql1 = "delete from mcms_stockpile_taking where stockpile_id in(select id from mcms_stockpile where branch_id not in(%s));" % branch_id sql2 = "delete from mcms_stockpile where branch_id not in(%s);" % branch_id sql3 = "delete from mcms_stockpile_flow where branch_id not in(%s);" % branch_id cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute("commit;") # 采购 sql = "delete from mcms_purchase_detail where order_no in(select order_no from mcms_purchase where branch_id not in(%s));" % branch_id cursor.execute(sql) cursor.execute("commit;") sql = "delete from mcms_pur_plan_detail where pid in(select id from mcms_pur_plan where branch_id not in(%s));" % branch_id cursor.execute(sql) cursor.execute("commit;") sql = "delete from mcms_pur_plan where branch_id not in (%s);" % branch_id cursor.execute(sql) cursor.execute("commit;") sql = "delete from mcms_purchase where branch_id not in (%s);" % branch_id cursor.execute(sql) cursor.execute("commit;") # dept1 = FileUtils().r_info8("b2_herp3_bs", "所有科室id", 'message6')['deptid1'] # dept2 = FileUtils().r_info8("b2_herp3_bs", "所有科室id", 'message6')['deptid2'] # dept3 = FileUtils().r_info8("b2_herp3_bs", "所有科室id", 'message6')['deptid3'] # dept4 = FileUtils().r_info8("b2_herp3_bs", "所有科室id", 'message6')['deptid4'] # 拣货单 sql4 = "delete from mcms_pick_detail where pid in(select id from mcms_pick where branch_id not in (%s));" % branch_id sql1 = "delete from mcms_pick where branch_id not in (%s);" % branch_id sql2 = "delete from mcms_pick_gen where dept_id in(select dept_id from mcms_stock_info where branch_id not in(%s));" % branch_id sql3 = "delete from mcms_pick_batch where pid in(select id from mcms_pick where branch_id not in (select id from mcms_branch_info));" cursor.execute(sql4) cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute("commit;") # 验收单 sql1 = "delete from mcms_check_batch where pid in(select id from mcms_check where branch_id not in(select id from mcms_branch_info));" sql2 = "delete from mcms_check where branch_id not in(select id from mcms_branch_info);" cursor.execute(sql1) cursor.execute(sql2) cursor.execute("commit;") # 配送单 sql1 = "delete from hdi_distr_detail where pid in(select id from hdi_distr where branch_id not in(select id from mcms_branch_info));" sql2 = "delete from hdi_distr_barcode where pid in(select id FROM hdi_distr where branch_id not in(%s));" % branch_id sql3 = "delete from hdi_distr where branch_id not in(%s);" % branch_id cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute("commit;") # 单据 sql1 = "delete from mcms_psi_batch where pid in(select id from mcms_psi where target_branch_id not in(select id from mcms_branch_info));" sql2 = "delete from mcms_psi where target_branch_id not in(%s);" % branch_id sql3 = "delete from mcms_psi_dept_batch where pid in(select id from mcms_psi_dept where target_branch_id not in(select id from mcms_branch_info));" sql4 = "delete from mcms_psi_dept where target_branch_id not in(%s);" % branch_id sql5 = "delete from his_order where branch_id not in (%s);" % branch_id cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute(sql4) cursor.execute(sql5) cursor.execute("commit;")
841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910
# 手术通知单 sql1 = "delete from mcms_operation_apply_list where pid in(select id from mcms_operation_apply where branch_id not in (select id from mcms_branch_info));" sql2 = "delete from mcms_operation_apply where branch_id not in (%s);" % branch_id sql3 = "delete from mcms_operation_notice_list where pid in(select id from mcms_operation_notice where branch_id not in (select id from mcms_branch_info));" sql4 = "delete from mcms_operation_notice where branch_id not in(%s);" % branch_id cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute(sql4) cursor.execute("commit;") # 结算 sql1 = "delete from mcms_out_balance_detail where pid in(select id from mcms_out_balance where branch_id not in (select id from mcms_branch_info));" sql2 = "delete from mcms_out_balance where branch_id not in(%s);" % branch_id sql3 = "delete from mcms_out_un_balance where branch_id not in(%s);" % branch_id cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute("commit;") # 发票 sql1 = "delete from mcms_invoice_comment where pid in(select invoice_id from mcms_invoice_ext_detail where pid in(select id from mcms_invoice_ext where prov_name like '%国药集团公司_____'));" sql2 = "delete from mcms_invoice where prov_name like '%国药集团公司_____';" sql3 = "delete from mcms_invoice_ext_detail where pid in(select id from mcms_invoice_ext where prov_name like '%国药集团公司_____');" sql4 = "delete from mcms_invoice_ext where prov_name like '%国药集团公司_____';" sql5 = "delete from mcms_invoice_detail where branch_id not in (%s);" % branch_id # 删除发票中的垃圾数据 sql6 = " delete from mcms_invoice where id in (select pid from mcms_invoice_detail where hos_goods_id not in(select id from mcms_goods_info));" sql7 = " delete from mcms_invoice_detail where hos_goods_id not in(select id from mcms_goods_info);" cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute(sql4) cursor.execute(sql5) cursor.execute(sql6) cursor.execute(sql7) cursor.execute("commit;") # 退货 sql1 = "delete from yb_return_batch where pid in(select id from yb_return where prov_name like '%国药集团公司_____');" sql2 = "delete from yb_return where prov_name like '%国药集团公司_____';" cursor.execute(sql1) cursor.execute(sql2) cursor.execute("commit;") # 请领单 sql1 = "delete from mcms_dept_buy_detail where pid in (select id from mcms_dept_buy where branch_id not in(%s));" % branch_id sql2 = "delete from mcms_dept_buy where branch_id not in(%s);" % branch_id sql3 = "delete from mcms_dept_buy_car where branch_id not in(%s);" % branch_id sql4 = "delete from mcms_dept_patient where id ='自动化测试';" cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute(sql4) cursor.execute("commit;") # 报表 sql1 = "delete from portal_card where branch_id not in(%s);" % branch_id sql2 = "delete from portal_chart where branch_id not in(%s);" % branch_id sql3 = "delete from portal_worklist where branch_id not in(%s);" % branch_id cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute("commit;") #删除院区产品表和部门产品表多余的数据 sql1 = "delete from mcms_branch_goods_info where hos_goods_id not in(select id from mcms_goods_info);" sql2 = "delete from mcms_branch_goods_info where branch_id not in(%s);" % branch_id sql3 = "delete from mcms_dept_goods_info where hos_goods_id not in(select id from mcms_goods_info);" sql4 = "delete from mcms_dept_goods_info where branch_id not in(%s);"%branch_id cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute(sql4)
911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980
cursor.execute("commit;") print('院区产品表和部门产品表多余的数据删除成功') print('业务数据，包括采购，出入库，结算等数据删除成功') cursor.close() def Delete_business_Data_All2(self): # 外网删除发票结算数据 connection = pymysql.connect(host="10.17.65.108", user="root", password="Cmic.2023", database="spd_hdiorder_test2", charset="utf8") cursor = connection.cursor() sql1 = "delete from mcms_out_balance_detail where prov_name like '%国药集团公司_____';" sql2 = "delete from mcms_out_balance where prov_name like '%国药集团公司_____';" sql3 = "delete from mcms_invoice_comment where pid in(select id from mcms_invoice where prov_name like '%国药集团公司_____');" sql4 = "delete from mcms_invoice_detail where pid in(select id from mcms_invoice where prov_name like '%国药集团公司_____');" sql5 = "delete from mcms_invoice where prov_name like '%国药集团公司_____';" sql6 = "delete from mcms_operation_notice_list where pid in(select id from mcms_operation_notice where branch_name like '%东土城路院区_____');" sql7 = "delete from mcms_operation_notice where branch_name like '%东土城路院区_____';" sql8 = "delete from mcms_purchase_detail where pid in(select id from mcms_purchase where branch_id not in(select id from spd3_herp_test2.mcms_branch_info ));" sql9 = "delete from mcms_purchase where branch_id not in(select id from spd3_herp_test2.mcms_branch_info );" cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute(sql4) cursor.execute(sql5) cursor.execute(sql6) cursor.execute(sql7) cursor.execute(sql8) cursor.execute(sql9) cursor.execute("commit;") print('外网业务数据，包括采购，发票，结算等据删除成功') cursor.close() def Del_goods_info_all(self): # 内网的 # connection = pymysql.connect(host="10.17.65.108", user="root", password="Cmic.2023", database="spd3_herp_test2", # charset="utf8") # 内网切换数据库 connection = pymysql.connect(host="10.17.65.49", port=30017, user="root", password="H-pV2d-1-N0z", database="spd3_herp_test2", charset="utf8") cursor = connection.cursor() hosGoodsId1 = FileUtils().r_info8("b5_spd3_core_business_flow", "产品审核信息", 'message1')['hosGoodsId'] hosGoodsId2 = FileUtils().r_info8("b5_spd3_core_business_flow", "产品审核信息", 'message2')['hosGoodsId'] hosGoodsId3 = FileUtils().r_info8("b5_spd3_core_business_flow", "产品审核信息", 'message3')['hosGoodsId'] # 删除产品信息 sql1 = "delete from mcms_goods_info where goods_name like '%一次性注射器%'and goods_spec ='G-31891'and create_time < DATE_SUB(CURDATE(),INTERVAL 5 day);" sql2 = "delete from mcms_goods_info where goods_name like '%新型冠状病毒检测试剂%'and goods_spec ='G-31891'and create_time < DATE_SUB(CURDATE(),INTERVAL 5 day);" sql3 = "delete from mcms_goods_info where goods_name like '%高值牙钻机%'and goods_spec ='G-31891'and create_time < DATE_SUB(CURDATE(),INTERVAL 5 day);" #删除结算点设置中的垃圾产品数据 sql4 = "delete from supply_prov_hos_goods where hos_goods_id not in(select id from mcms_goods_info);" cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute(sql4) cursor.execute("commit;") print('内网产品信息删除成功 ') cursor.close() # 外网的 connection = pymysql.connect(host="10.17.65.108", user="root", password="Cmic.2023", database="spd_ybtest_hdi", charset="utf8") cursor = connection.cursor() sql1 = "delete from mcms_goods_info where goods_name like '%一次性注射器%'and goods_spec ='G-31891'and create_time < DATE_SUB(CURDATE(),INTERVAL 5 day);" sql2 = "delete from mcms_goods_info where goods_name like '%新型冠状病毒检测试剂%'and goods_spec ='G-31891'and create_time < DATE_SUB(CURDATE(),INTERVAL 5 day);" sql3 = "delete from mcms_goods_info where goods_name like '%高值牙钻机%'and goods_spec ='G-31891'and create_time < DATE_SUB(CURDATE(),INTERVAL 5 day);" cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute("commit;")
981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050
print('外网产品信息删除成功') cursor.close() def check_goods_info(self,hos_goods_id): # 内网的 # connection = pymysql.connect(host="10.17.65.108", user="root", password="Cmic.2023", database="spd3_herp_test2", # charset="utf8") # 内网切换数据库 connection = pymysql.connect(host="10.17.65.49", port=30017, user="root", password="H-pV2d-1-N0z", database="spd3_herp_test2", charset="utf8") cursor = connection.cursor() # 查询产品信息 sql="""SELECT hos_goods_code, hos_id, prov_id, prov_hos_goods_id, srv_id, mdm_goods_code, mdm_goods_spec_code, spd_goods_code, goods_name, goods_general_name, goods_mfrs_id, goods_mfrs_name, goods_reg_cert, goods_agent_mfrs_id, brand, short_pinyin, kind_sixtyeight_code, unit, goods_spec, goods_package, bar_code_mng, unique_code_strategy, pur_mode, sub_pur_mode, erp_code, herp_code, hrp, price, abroad_flag, made, property, mgr_level, goods_desc, charge_flag, focus_control_type, temp_flag, official_status, focus_control, storage_conditions, transport_conditions, into_cost_flag, rfid_flag, purchase_flag, tb_status, version, create_user, create_time, last_modified_user, sterilize_flag, multi_charge_flag, use_frequency, charge_unit FROM mcms_goods_info WHERE id ='%s';""" % hos_goods_id cursor.execute(sql) results = cursor.fetchall() cursor.close() # print(results) s = ''.join(map(str, results))
1051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120
# s = list(results) return s def get_orgCode(self): # connection = pymysql.connect(host="10.17.65.108", user="root", password="Cmic.2023", database="spd3_herp_test2", # charset="utf8") # 内网切换数据库 connection = pymysql.connect(host="10.17.65.49", port=30017, user="root", password="H-pV2d-1-N0z", database="spd3_herp_test2", charset="utf8") branch_id = FileUtils().r_info8('b2_herp3_bs', "院区新增", 'message')['branch_id'] cursor = connection.cursor() sql1 = "select code from sys_org where ename='设备科001' and branch_id='%s';" % branch_id cursor.execute(sql1) results = cursor.fetchall() cursor.close() # print(results) s = ''.join(map(str, results)) return s def get_orgName(self): # connection = pymysql.connect(host="10.17.65.108", user="root", password="Cmic.2023", database="spd3_herp_test2", # charset="utf8") # 内网切换数据库 connection = pymysql.connect(host="10.17.65.49", port=30017, user="root", password="H-pV2d-1-N0z", database="spd3_herp_test2", charset="utf8") branch_id = FileUtils().r_info8('b2_herp3_bs', "院区新增", 'message')['branch_id'] cursor = connection.cursor() sql1 = "select ename from sys_org where ename='设备科001' and branch_id='%s';" % branch_id cursor.execute(sql1) results = cursor.fetchall() cursor.close() # print(results) s = ''.join(map(str, results)) return s def del_mcms_stock_user(self,user_code,dept_id): # 内网的 # connection = pymysql.connect(host="10.17.65.108", user="root", password="Cmic.2023", database="spd3_herp_test2", # charset="utf8") # 内网切换数据库 connection = pymysql.connect(host="10.17.65.49", port=30017, user="root", password="H-pV2d-1-N0z", database="spd3_herp_test2", charset="utf8") cursor = connection.cursor() # 删除库区用户 sql1 = "delete from mcms_stock_user where user_code='%s' and dept_id='%s';"%(user_code,dept_id) cursor.execute(sql1) cursor.execute("commit;") print('库区人员信息删除成功 ') cursor.close() def get_branch_id_bydb(self,no_branch_id): # 内网的 # connection = pymysql.connect(host="10.17.65.108", user="root", password="Cmic.2023", database="spd3_herp_test2", # charset="utf8") # 内网切换数据库 connection = pymysql.connect(host="10.17.65.49", port=30017, user="root", password="H-pV2d-1-N0z", database="spd3_herp_test2", charset="utf8") cursor = connection.cursor() # 查询脚本创建的的院区 sql1 = "select id from mcms_branch_info where name like '%%东土城路院区%%' and id <>'%s';"%no_branch_id cursor.execute(sql1) # results = cursor.fetchone() results = cursor.fetchall() list_1=[] for i in results: str1 = str(i)
1121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145
new_str1 = str1.replace(',)', '') new_str1 = new_str1.replace('(', '') new_str1 = new_str1.replace(',', '') new_str1 = new_str1.replace("'", '') list_1.append(new_str1) # print(new_str1) cursor.close() return list_1 # delData().Delete_branch_by_id() # delData().Delete_goods_change_info() # delData().Delete_sys_config() # delData().Delete_sys_message_template() # delData().get_shelf_name_version() # delData().Delete_role() # delData().Delete_user() # delData().Del_goods_info(i) # delData().Del_supply_relation() # delData().Delete_business_Data() # delData().Del_reporter_data() # delData().Delete_yb_logic_stock() # delData().Delete_business_Data_All() # delData().Delete_business_Data_All2() # delData().check_goods_info() # delData().Del_supply_relation1()