def resume_perfect_warehousing_contract(): """ 还原完善仓储契约数据 """ con, curs = basic_cit() try: sql_list = [ '''delete from xdw_app.dd_charging_breach_detail dcbd where dcbd.delivery_requirements_ukid in (select dbr.delivery_requirements_ukid from xdw_app.dd_delivery_requirements dbr where dbr.charging_breach_item_ukid in (select dcbi.charging_breach_item_ukid from xdw_app.dd_charging_breach_item dcbi where dcbi.contract_ukid in (51784800000039000,51784000000033000,51788500000010000)))''', '''delete from xdw_app.dd_delivery_requirements dbr where dbr.charging_breach_item_ukid in (select dcbi.charging_breach_item_ukid from xdw_app.dd_charging_breach_item dcbi where dcbi.contract_ukid in (51784800000039000,51784000000033000,51788500000010000))''', '''delete from xdw_app.dd_charging_breach_item dcbi where dcbi.contract_ukid in (51784800000039000,51784000000033000,51788500000010000)''', '''update xdw_app.dd_contract dc set dc.contract_status = 20 where dc.contract_ukid in (51784800000039000,51784000000033000,51788500000010000)''' ] for sql in sql_list: curs.execute(sql) con.commit() close_oracle(con, curs) print('删除数据成功') except Exception as e: print('删除数据失败') print(e) close_oracle(con, curs)
def delete_unit_convert(user_id, unit_name, item_ukid='51845300000005028'): con, curs = basic_cit() # con, curs = basic_sit() try: creator_ukid = get_creator_ukid(user_id) sql2 = [ '''delete from xdw_app.BA_UNIT_CUSTOM buc where buc.unit_name='%s'and buc.creator_ukid='%s'and buc.unit_status='10' ''' % (unit_name, creator_ukid), '''delete from xdw_app.IM_TRANSFORM_UNIT iu where iu.im_transform_unit_ukid in(select itud.im_transform_unit_ukid from xdw_app.IM_TRANSFORM_UNIT_DETAIL itud where itud.item_ukid='%s')and iu.transform_status=10 ''' % item_ukid, '''delete from xdw_app.IM_TRANSFORM_UNIT_DETAIL itud where itud.item_ukid='%s' ''' % item_ukid ] for sql in sql2: curs.execute(sql) con.commit() print(sql) close_oracle(con, curs) print('删除单位和转换关系成功') except Exception as e: print('删除单位和转换关系失败') print(e) close_oracle(con, curs)
def reduction_monitor_and_manager_permissions(user_id1=1111111002, user_id2=1111111003): try: con, curs = basic_cit() sql = ''' delete from xdw_app.au_role_item auri where au_role_item_ukid in( select auri.au_role_item_ukid from xdw_app.au_role aur,xdw_app.au_role_item auri where aur.au_role_ukid =auri.au_role_ukid and aur.PARTICIPANT_UKID in ('%s','%s') and role_type in ('22','32'))''' % (user_id1, user_id2) curs.execute(sql) con.commit() sql2 = '''delete from xdw_app.au_role where PARTICIPANT_UKID in ('%s','%s') and role_type in ('22','32') ''' % (user_id1, user_id2) curs.execute(sql2) con.commit() sql3 = ['''select * from xdw_app.ts_operation tso where tso.pptr_ukid in (select ppt_relation_ukid from XDW_APP.CM_PARTICIPANT_relation where PARTICIPANT_UKID in('%s','%s')) and tso.card_type in ('MonitorAuthorizeService', 'ManagerAuthorizeService') ''' % (user_id1, user_id2), '''delete from xdw_app.ms_card where RECEIVER_ID in ('%s','%s') and card_type in ( 'MonitorAuthorizeService','ManagerAuthorizeService')''' % (user_id1, user_id2)] for sql in sql3: curs.execute(sql) con.commit() close_oracle(con, curs) print('还原监控者和管理者权限') except Exception as e: print(e)
def reduction_non_service_certification_task(user_id=10000387): # 还原非服务认证任务 try: con, curs = basic_cit() sql1 = ['''delete from xdw_app.au_role_item where au_role_ukid in(select au_role_ukid from xdw_app.au_role where participant_ukid ='%s' )''' % user_id, '''delete from xdw_app.au_role where participant_ukid ='%s' ''' % user_id] for sql in sql1: curs.execute(sql) con.commit() sql2 = ['''delete from xdw_app.ms_card where RECEIVER_ID = '%s' and card_type = 'ManagerAuthorizeService' ''' % user_id, '''delete from xdw_app.TS_OPERATION where pptr_ukid in (select ppt_relation_ukid from XDW_APP.CM_PARTICIPANT_relation where PARTICIPANT_UKID in('%s')) and card_type='ManagerAuthorizeService' ''' % user_id] for sql in sql2: curs.execute(sql) con.commit() sql3 = '''update XDW_APP.CM_PARTICIPANT cmp set status ='2' where cmp.participant_ukid ='%s' ''' % user_id curs.execute(sql3) con.commit() close_oracle(con, curs) print('还原非服务认证任务') except Exception as e: print(e)
def reduction_task(user_id, invitee_id): # 还原任务移交 try: con, curs = basic_cit() # 删除邀请表信息 creator_id = get_creator_ukid(user_id) sql = '''UPDATE xdw_app.CM_PARTICIPANT_RELATION set relation_status=20 where relation_sn =(select receiver_id from xdw_app.ms_card where card_type='RegNoService' and receiver_id in(select relation_sn from xdw_app.CM_PARTICIPANT_RELATION where PARTICIPANT_UKID='%s'and relation_status=90)) and participant_ukid ='%s' ''' % (user_id, user_id) print(user_id, invitee_id) curs.execute(sql) con.commit() sql1 = ['''delete from xdw_app.CM_PARTICIPANT_RELATION where relation_sn =(select receiver_id from xdw_app.ms_card where card_type='RegNoService' and receiver_id in(select relation_sn from xdw_app.CM_PARTICIPANT_RELATION where PARTICIPANT_UKID='%s'and relation_status=20)) and participant_ukid ='%s' ''' % (invitee_id, invitee_id), '''UPDATE xdw_app.ms_card set OWNER_BU_ID='%s' where card_type='RegNoService' and receiver_id in(select relation_sn from xdw_app.CM_PARTICIPANT_RELATION where PARTICIPANT_UKID='%s')''' % (creator_id, user_id)] for sql in sql1: curs.execute(sql) con.commit() close_oracle(con, curs) print('还原任务移交\n') except Exception as e: print(e)
def reduction_distribution_manager_and_supervisor_task(user_id=10000428): # 还原分配管理者和监控者任务 try: con, curs = basic_cit() sql1 = ['''delete from xdw_app.au_role_item auri where au_role_item_ukid in( select auri.au_role_item_ukid from xdw_app.au_role aur, xdw_app.au_role_item auri where aur.au_role_ukid = auri.au_role_ukid and aur.create_user_id = '%s' and role_type in ('21','31'))''' % user_id, '''delete from xdw_app.au_role where create_user_id ='%s' and role_type in ('21','31')''' % user_id, '''delete from xdw_app.au_role where participant_ukid ='%s' and role_type in ('1') ''' % user_id] for sql in sql1: curs.execute(sql) con.commit() sql2 = ['''update xdw_app.ts_operation set status = 0 where pptr_ukid in (select ppt_relation_ukid from XDW_APP.CM_PARTICIPANT_relation where PARTICIPANT_UKID in('%s')) and card_type in ('AllotManagerAndMonitorService') ''' % user_id, '''delete from xdw_app.ts_operation tso where tso.pptr_ukid in (select ppt_relation_ukid from XDW_APP.CM_PARTICIPANT_relation where PARTICIPANT_UKID in('%s')) and tso.card_type in ('ManagerAuthorizeService','MonitorAuthorizeService','QueryNewEmployeesService') ''' % user_id, '''update xdw_app.ms_card set status = 0 where RECEIVER_ID = '%s' and card_type = 'AllotManagerAndMonitorService' ''' % user_id, '''delete from xdw_app.ms_card where RECEIVER_ID = '%s' and card_type in( 'ManagerAuthorizeService','MonitorAuthorizeService','QueryNewEmployeesService')''' % user_id] for sql in sql2: curs.execute(sql) con.commit() close_oracle(con, curs) print('还原分配管理者和监控者任务') except Exception as e: print(e)
def setSQlDelete_common(): try: # 连接数据库配置还原注册信息,删除table中已插入的数据 con, curs = basic_cit() # 通过get到手机号查询CREATE_USER_ID sql = ''' SELECT CREATE_USER_ID FROM basic_owner.ba_user WHERE MOBILE=15268509694''' # 将结果赋值给state curs.execute(sql) stated = curs.execute(sql) result = [] for item in stated: result = item break print(result[0]) # 删除4个表中关于该用户的记录 delete_sql1 = '''delete from XDW_APP.cm_participant where CREATE_USER_ID= '%s' ''' % result delete_sql2 = '''delete from XDW_APP.cm_identify where CREATE_USER_ID= %s ''' % result delete_sql3 = '''delete from XDW_APP.Cm_Human where CREATE_USER_ID= %s ''' % result delete_sql4 = '''delete from XDW_APP.ba_user where CREATE_USER_ID= %s ''' % result # update1 = ''' UPDATE XDW_APP.RU_INVITE SET CREATE_TIME = SYSDATE, STATUS = 1, OPENID = NULL, SERVICE_TYPE = 1, AUTH_TASK_OPERATION_UKID = NULL,INVALID_TIME = SYSDATE + 1/48WHERE INVITE_CODE = 4e03116091b911e6a437fc952ffd4494''' # 执行5条sql语句 curs.execute(delete_sql1) curs.execute(delete_sql2) curs.execute(delete_sql3) curs.execute(delete_sql4) # curs.execute(update1) con.commit() con.close() except Exception as e: print(e) print("CIT_BASIC 数据库数据恢复") pass
def resume_generate_bill(demander_id=199983, supplier_id=199833): """ 还原生成账单数据 :param demander_id:需方id :param supplier_id:供方id """ con, curs = basic_cit() try: sql_list = [ '''update xdw_app.em_promotion_service_fee e set e.bill_ukid = '' where e.supplier_id='%s' and e.demander_id='%s' and e.contract_type in ('WA','TR')''' % (supplier_id, demander_id), '''delete from xdw_app.ms_card m where m.card_name in('支付','生成账单') and m.create_user_id = '%s' ''' % demander_id, '''delete from xdw_app.ts_operation m where m.operation_name in( '支付','生成账单') and m.create_user_id = '%s' ''' % demander_id, '''delete from xdw_app.ts_payment_bill tpb where tpb.participant_ukid='%s' and tpb.receive_unit = '%s' ''' % (demander_id, supplier_id), '''delete from xdw_app.em_participant_money_record epmr where (epmr.participant_ukid = '%s' and epmr.other_ppt_ukid = '%s') or (epmr.participant_ukid ='%s' and epmr.other_ppt_ukid = '%s') ''' % (supplier_id, demander_id, demander_id, supplier_id) ] for sql in sql_list: curs.execute(sql) con.commit() close_oracle(con, curs) print('还原生成账单成功') except Exception as e: print('还原生成账单失败') print(e) close_oracle(con, curs)
def get_bill_info(user_id=199885): """ 获得钱仓页面的信息 :param user_id: :return: """ get_info = {} con, curs = basic_cit() try: sql0 = '''select count(*) from xdw_app.TS_PAYMENT_BILL tpb where (tpb.participant_ukid = '%s' or tpb.receive_unit = '%s') and tpb.bill_ukid in (select bill_ukid from xdw_app.em_promotion_service_fee e) and tpb.bill_status = 0 and tpb.payment_title = '结算账单支付信息' ''' % (user_id, user_id) curs.execute(sql0) result = curs.fetchall() get_info['unliquidated_counts'] = str(result[0][0]) sql1 = '''select money_type,sum(money) from xdw_app.EM_PARTICIPANT_MONEY_RECORD e where e.money_type in ( '契约预付款','保证金') and e.status=2 and e.participant_ukid='%s' group by money_type''' % user_id curs.execute(sql1) result = curs.fetchall() print('---------------------sql1------------------------------') print('---------------------sql1------------------------------') print('---------------------sql1------------------------------') print('---------------------sql1------------------------------') print('---------------------sql1------------------------------') print('---------------------sql1------------------------------') print('---------------------sql1------------------------------') print(result) print(result) print(result) print(result) print(result) print(result) for r in result: if r[0] == '契约预付款': get_info['wallet_left_advance'] = str(r[1]) if r[0] == '保证金': get_info['wallet_left_deposit'] = str(r[1]) sql2 = '''select count(*) from xdw_app.TS_PAYMENT_BILL tpb where (tpb.participant_ukid ='%s' or tpb.receive_unit ='%s') and tpb.bill_ukid in (select bill_ukid from xdw_app.em_promotion_service_fee e) and tpb.payment_title = u'结算账单支付信息' ''' % (user_id, user_id) curs.execute(sql2) result = curs.fetchall() get_info['all_counts'] = str(result[0][0]) print(get_info) close_oracle(con, curs) return get_info except Exception as e: print('获取账单信息失败') print(e) close_oracle(con, curs)
def get_distribution_manager_and_supervisor_task_status(user_id=10000428): # 获取任务状态 try: con, curs = basic_cit() sql1 = '''select tso.status from xdw_app.ts_operation tso where tso.pptr_ukid in (select ppt_relation_ukid from XDW_APP.CM_PARTICIPANT_relation where PARTICIPANT_UKID in('%s')) and card_type = 'AllotManagerAndMonitorService' ''' % user_id curs.execute(sql1) result = curs.fetchall() close_oracle(con, curs) return result[0][0] except Exception as e: print(e)
def delete_optimize_address_book_and_invite_link_five(user1_id=None): con, curs = basic_cit() # con, curs = basic_sit() # 查询已注册状态的数据 try: sql1 = '''delete from xdw_app.au_role_item where participant_ukid = '%s' and operation_unit_ukid = '%s' ''' % user1_id curs.execute(sql1) con.commit() close_oracle(con, curs) print('选择状态更新成功') except Exception as e: print('选择状态更新失败') print(e)
def update_optimize_address_book_and_invite_link_four(): con, curs = basic_cit() # con, curs = basic_sit() # 查询已注册状态的数据 try: sql1 = '''update xdw_app.cm_participant set status =2 where ppt_name = '千' ''' curs.execute(sql1) con.commit() close_oracle(con, curs) print('认证中状态更新成功') except Exception as e: print('认证中状态更新失败') print(e)
def clear_first_sql(user_id = 10001475): """ :param user_id: :return: """ con, curs = basic_cit() try: sql0 = '''delete from xdw_app.cm_company_partner_relation a where a.create_user_id= {0}'''.format(user_id) curs.execute(sql0) con.commit() close_oracle(con, curs) except Exception as e: print('清除注册联系人数据第一页失败') close_oracle(con, curs) pass
def clear_second_sql(user_id = 10001475): """ :param user_id: :return: """ con, curs = basic_cit() try: sql0 = '''delete from cm_identify a where a.create_user_id= {0}'''.format(user_id) curs.execute(sql0) con.commit() close_oracle(con, curs) except Exception as e: print('清除注册联系人数据第2页失败') close_oracle(con, curs) pass
def restore_card(inviter_id=10000237): try: con, curs = basic_cit() sql1 = ['''update xdw_app.ms_card set status = 0 where RECEIVER_ID = '%s' and card_type = 'RegNoService' ''' % inviter_id, '''update xdw_app.TS_OPERATION set status = '0' where pptr_ukid in (select ppt_relation_ukid from XDW_APP.CM_PARTICIPANT_relation where PARTICIPANT_UKID in('%s')) and card_type='RegNoService' ''' % inviter_id] for sql in sql1: curs.execute(sql) con.commit() close_oracle(con, curs) print('还原非服务认证任务kp') except Exception as e: print(e)
def get_user_message_info(from_user_id=None): con, curs = basic_cit() try: get_data = {} sql2 = '''select count(*) FROM xdw_app.ms_user_message WHERE sender_id='%s' and receiver_id IN ('10001488','10001588') ''' % from_user_id print(sql2) curs.execute(sql2) info_data = curs.fetchall() get_data['user_message_info_no'] = info_data[0][0] print('取得消息通知成功') return get_data except Exception as e: print('取得消息通知失败') print(e) close_oracle(con, curs)
def delete_optimize_address_book_and_invite_link(): con, curs = basic_cit() # con, curs = basic_sit() # 删除邀请状态 try: sql1 = ['''delete from xdw_app.cm_participant t where t.ppt_name = '周锦' ''', '''delete from xdw_app.ru_invite t where t.invitee_name = '周锦' ''', '''delete from xdw_app.ba_user t where t.user_name = '周锦' '''] for sql in sql1: curs.execute(sql) con.commit() close_oracle(con, curs) print('邀请状态数据恢复成功') except Exception as e: print('邀请状态数据恢复失败') print(e)
def login_state(phone): # 还原非服务认证任务 try: con, curs = basic_cit() sql1 = '''select platform,login_mode,status from xdw_app.ru_login_log where MOBILE='%s' order by login_time desc''' % phone curs.execute(sql1) result = curs.fetchall() close_oracle(con, curs) print('登陆') print(result) if (result[0][0] == 1 and result[0][1] == 1 and result[0][2] == 1): return True elif (result[0][0] == 1 and result[0][1] == 1 and result[0][2] == 0): return False except Exception as e: print(e)
def reduction_task(user_id, invitee_id): # 还原任务移交 try: con, curs = basic_cit() # 删除邀请表信息 sql = '''UPDATE xdw_app.ms_card set receiver_id='%s',update_user_id='%s'where card_type='RegNoService' and receiver_id='%s' ''' % (user_id, user_id, invitee_id) curs.execute(sql) con.commit() sql1 = '''UPDATE XDW_APP.CM_PARTICIPANT_RELATION SET PARTICIPANT_UKID='%s'WHERE PARTICIPANT_UKID='%s' ''' \ % (user_id, invitee_id) curs.execute(sql1) con.commit() close_oracle(con, curs) print('还原任务移交\n') except Exception as e: print(e)
def get_forward_task_data(to_user_id=None): con, curs = basic_cit() try: result_data = {} sql1 = '''select count(*) FROM xdw_app.cm_participant_relation WHERE relation_status=20 and participant_ukid=%s '''\ % to_user_id print(sql1) curs.execute(sql1) result = curs.fetchall() result_data['forward_task_record_no'] = result[0][0] print('转发对象取得成功') return result_data except Exception as e: print('转发对象取得失败') print(e) close_oracle(con, curs)
def get_optimize_address_book_and_invite_link_two(): con, curs = basic_cit() # con, curs = basic_sit() # 查询已注册状态的数据 try: sql1 = [ '''select * from xdw_app.cm_participant t where t.ppt_name = '百合' ''', '''select * from xdw_app.ru_invite t where t.invitee_name = '百合' ''', '''select * from xdw_app.ba_user t where t.user_name = '百合' ''' ] for sql in sql1: curs.execute(sql) con.commit() close_oracle(con, curs) print('已注册状态查询成功') except Exception as e: print('已注册状态查询失败') print(e)
def restore_forward_task(from_user_id=None, to_user_id=None): con, curs = basic_cit() # con, curs = basic_sit() # 还原任务 try: # creator_ukid = get_creator_ukid(user_id) sql1 = ['''update xdw_app.cm_participant_relation set participant_ukid= 10001577 where relation_status=20 and participant_ukid=%s ''' % to_user_id, '''delete FROM xdw_app.ms_user_message where sender_id='%s' ''' % from_user_id] for sql in sql1: curs.execute(sql) con.commit() close_oracle(con, curs) print('还原任务成功') except Exception as e: print('还原任务失败') print(e) close_oracle(con, curs)
def view_permissions(participant='1111111002', role_type='32'): # 查看权限 try: con, curs = basic_cit() sql = '''select aui.item_type from xdw_app.au_item aui,xdw_app.au_role_item auri left join xdw_app.au_role aur on auri.au_role_ukid = aur.au_role_ukid where auri.au_role_ukid in (select au_role_ukid from xdw_app.au_role where PARTICIPANT_UKID ='%s') and auri.au_item_ukid = aui.au_item_ukid and auri.status = 0 and aur.role_type = '%s' order by aui.item_type'''\ % (participant, role_type) curs.execute(sql) result = curs.fetchall() close_oracle(con, curs) l1 = [] for r in result: l1.append(r[0]) return l1 except Exception as e: print(e)
def judge_manager_and_supervisor_result(user_id=10000428): # 分配成功后,查看角色和权限 try: con, curs = basic_cit() sql1 = "select role_type from xdw_app.au_role where create_user_id ='%s' and status = 1" % user_id # 获取角色 curs.execute(sql1) result = curs.fetchall() if len(result) == 2 and result[0][0] in (21, 31) and result[1][0] in (21, 31) and result[1][0] != result[0][0]: pass else: return False sql2 = '''select aui.item_type from xdw_app.au_item aui,xdw_app.au_role_item auri left join xdw_app.au_role aur on auri.au_role_ukid = aur.au_role_ukid where auri.au_role_ukid in (select au_role_ukid from xdw_app.au_role where PARTICIPANT_UKID ='%s') and auri.au_item_ukid = aui.au_item_ukid and auri.status = 0 and aur.role_type = 21 order by aui.item_type''' % user_id curs.execute(sql2) result2 = curs.fetchall() sql3 = '''select item_type from xdw_app.au_item order by item_type ''' curs.execute(sql3) result3 = curs.fetchall() if result2 == result3: # 判断管理者权限集 pass else: return False sql4 = ''' select aui.item_type from xdw_app.au_item aui,xdw_app.au_role_item auri left join xdw_app.au_role aur on auri.au_role_ukid = aur.au_role_ukid where auri.au_role_ukid in (select au_role_ukid from xdw_app.au_role where PARTICIPANT_UKID ='%s') and auri.au_item_ukid = aui.au_item_ukid and auri.status = 0 and aur.role_type = 31 order by aui.item_type''' % user_id curs.execute(sql4) result4 = curs.fetchall() close_oracle(con, curs) if result4 == result3: # 判断监控者权限集 flag = 1 else: return False if flag == 1: return True else: return False except Exception as e: print(e)