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)
