def get_new_transport_contract(user_id, contract_ukid):
# con, curs = basic_cit()
con, curs = basic_sit()
try:
dic = {}
creator_ukid = get_creator_ukid(user_id)
sql = '''select supplier_id from xdw_app.dd_contract where CONTRACT_UKID ='%s' ''' % contract_ukid
curs.execute(sql)
result = curs.fetchall()
supplier_id = result[0][0]
print(supplier_id)
print(creator_ukid)
sql2 = '''select count(*) from xdw_app.dd_contract where supplier_id ='%s' and demander_id ='%s' order by
create_time desc''' % (supplier_id, creator_ukid)
curs.execute(sql2)
result2 = curs.fetchall()
dic['new_transport_contract_no'] = result2[0][0]
sql3 = '''select count(*) from xdw_app.rs_repository where rs_type='TR' AND owner_id in ('%s','%s')'''\
% (supplier_id, creator_ukid)
curs.execute(sql3)
result3 = curs.fetchall()
dic['rs_repository_no'] = result3[0][0]
sql4 = '''select count(*) from xdw_app.ru_transport_supply_log where CONTRACT_SOURCE_UKID = '%s' AND
log_user_id = '%s' ''' % (contract_ukid, user_id)
curs.execute(sql4)
result4 = curs.fetchall()
dic['operation_record_no'] = result4[0][0]
return dic
except Exception as e:
print('获取运输契约信息失败')
print(e)
close_oracle(con, curs)
def reduction_warehouse_demand_contract():
"""
还原仓储需求契约
"""
con, curs = basic_sit()
try:
sql_list = [
'''update xdw_app.dd_contract set contract_status = 20 where contract_ukid = 51817200000010012''',
'''update xdw_app.dd_contract_item set qty = 500 where contract_ukid in (51817200000010012)
and rs_type = 'WA' ''',
'''update xdw_app.dd_contract_item set qty = 1000 where contract_ukid in (51817200000010012)
and rs_type = 'RE' ''',
'''delete from xdw_app.ru_warehouse_demand_log where CONTRACT_SOURCE_UKID in (51817200000010012)''',
'''delete from xdw_app.rs_repository where owner_id in (10001392,10001437)''',
'''delete from xdw_app.dd_contract where SUPPLIER_ID=10001392 and demander_id =10001437 '''
]
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_labor_contract(user_id=10001475,
participant_ukid=10001465,
contract_type='LA'):
# con, curs = basic_cit()
con, curs = basic_sit()
# 删除劳务契约
try:
creator_ukid = get_creator_ukid(user_id)
sql2 = [
'''delete from xdw_app.ru_labour_log where CONTRACT_SOURCE_UKID in (select CONTRACT_UKID
from xdw_app.dd_contract where CONTRACT_TYPE = '%s' and DEMANDER_ID='%s')'''
% (contract_type, participant_ukid),
'''delete from xdw_app.rs_repository where owner_id in ('%s','%s')'''
% (participant_ukid, creator_ukid),
'''delete from xdw_app.dd_contract where demander_id in (select demander_id from
xdw_app.dd_contract where CONTRACT_UKID = '51817000000010130') and supplier_id ='%s' and contract_type='%s' '''
% (user_id, contract_type)
]
for sql in sql2:
curs.execute(sql)
con.commit()
close_oracle(con, curs)
print('还原劳务契约成功')
except Exception as e:
print('还原劳务契约失败')
print(e)
close_oracle(con, curs)
def get_transport_contract_info(user_id, contract_ukid, contract_item_ukid ='51817700000010033'):
# con, curs = basic_cit()
con, curs = basic_sit()
try:
creator_ukid = get_creator_ukid(user_id)
sql = '''select ddc.CONTRACT_UKID,ddc.SUPPLIER_ID,ddc.DEMANDER_ID,ddc.contract_status,cma.BRAND,ddci.qty,
cma.DEPARTURE,cma.DESTINATIONS,ddci.start_time,ddci.end_time,ddup.SALE_PRICE,ddup.PRICE_UNIT,cma.RANGE_OF_WEIGHT,
cma.RESOURCE_NAME, cma.COLLECT_AREA from xdw_app.CM_PARTICIPANT cmp,XDW_APP.dd_contract ddc,
XDW_APP.dd_contract_item ddci,XDW_APP.RS_TRANSPORT_PUBLISH cma,XDW_APP.dd_unit_price ddup
where cmp.participant_ukid ='%s' and ddc.CONTRACT_TYPE = 'TR'and ddc.CONTRACT_ASCRIPTION in('SUPPLY')and
ddci.CONTRACT_UKID =ddc.CONTRACT_UKID and ddc.CONTRACT_UKID='%s' and ddci.CONTRACT_ITEM_UKID='%s' and
ddci.REPOSITORY_UKID = cma.RESOURCE_ukid and ddup.contract_item_ukid = ddci.CONTRACT_ITEM_UKID
Order by ddci.start_time desc''' % (creator_ukid, contract_ukid, contract_item_ukid)
curs.execute(sql)
result = curs.fetchall()
print(result)
dic= {'status': result[0][3], 'brand': result[0][4], 'qty': result[0][5], 'DEPARTURE': result[0][6],
'DESTINATIONS': result[0][7], 'start_time': result[0][8], 'end_time': result[0][9],
'SALE_PRICE': result[0][10], 'PRICE_UNIT': result[0][11], 'RANGE_OF_WEIGHT': result[0][12],
'RESOURCE_NAME': result[0][13], 'COLLECT_AREA': result[0][14]}
return dic
except Exception as e:
print('获取运输契约信息失败')
print(e)
close_oracle(con, curs)
def get_transport_contract_item_ukid(CONTRACT_UKID):
# con, curs = basic_cit()
con, curs = basic_sit()
try:
sql = '''select contract_item_ukid from xdw_app.dd_contract_item where CONTRACT_UKID =
'%s' order by create_time desc''' % (CONTRACT_UKID)
curs.execute(sql)
result = curs.fetchall()
print(result)
if len(result) >= 1:
if len(result) == 1:
contract_item_ukid = result[0][0]
return contract_item_ukid
else:
contract_item_ukid = []
for i in range(len(result)):
contract_item_ukid.append(result[i][0])
return contract_item_ukid
else:
return False
except Exception as e:
print('获取运输契约item_ukid失败')
print(e)
close_oracle(con, curs)
def delete_transport_contract(user_id=10001476,
participant_ukid=10001476,
contract_type='TR'):
# con, curs = basic_cit()
con, curs = basic_sit()
try:
creator_ukid = get_creator_ukid(user_id)
sql2 = [
'''delete from xdw_app.dd_contract where supplier_id in (select supplier_id from
xdw_app.dd_contract where CONTRACT_UKID = '51817000000009223') and demander_id ='%s' and contract_type='%s' '''
% (user_id, contract_type),
'''delete from xdw_app.rs_repository where rs_type='%s' and owner_id in ('%s', '%s') '''
% (contract_type, user_id, participant_ukid),
'''delete from xdw_app.ru_transport_supply_log where CONTRACT_SOURCE_UKID in (select CONTRACT_UKID
from xdw_app.dd_contract where CONTRACT_TYPE = '%s' and SUPPLIER_ID='%s')'''
% (contract_type, participant_ukid),
'''delete from xdw_app.ru_transport_supply_log where log_user_id = '%s'
and CONTRACT_SOURCE_UKID = '51817000000009223' ''' %
(user_id)
]
for sql in sql2:
curs.execute(sql)
con.commit()
close_oracle(con, curs)
print('还原运输契约成功')
except Exception as e:
print('还原运输契约失败')
print(e)
close_oracle(con, curs)
def get_new_labor_contract(user_id, contract_ukid=51817000000010130):
con, curs = basic_sit()
try:
dic = {}
creator_ukid = get_creator_ukid(user_id)
sql = '''select demander_id from xdw_app.dd_contract where CONTRACT_UKID ='%s' ''' % contract_ukid
curs.execute(sql)
result = curs.fetchall()
demander_id = result[0][0]
sql2 = '''select count(*) from xdw_app.dd_contract where demander_id in (select demander_id from
xdw_app.dd_contract where CONTRACT_UKID ='%s') and supplier_id ='%s' order by
create_time desc''' % (contract_ukid, creator_ukid)
curs.execute(sql2)
result2 = curs.fetchall()
dic['new_labor_contract_no'] = result2[0][0]
sql3 = '''select count(*) from xdw_app.rs_repository where owner_id in ('%s','%s')''' \
% (demander_id, creator_ukid)
curs.execute(sql3)
result3 = curs.fetchall()
dic['rs_repository_no'] = result3[0][0]
sql4 = '''select count(*) from xdw_app.ru_labour_log where CONTRACT_SOURCE_UKID = '%s' ''' \
% contract_ukid
curs.execute(sql4)
result4 = curs.fetchall()
dic['operation_record_no'] = result4[0][0]
print('获取劳务契约信息成功')
return dic
except Exception as e:
print('获取劳务契约信息失败')
print(e)
close_oracle(con, curs)
def delete_labor_contract(user_id, participant_ukid, contract_type='LA'):
# con, curs = basic_cit()
con, curs = basic_sit()
try:
creator_ukid = get_creator_ukid(user_id)
sql2 = [
'''delete from xdw_app.dd_unit_price ddup where ddup.CONTRACT_UKID in (select CONTRACT_UKID from
xdw_app.dd_contract where CONTRACT_TYPE = '%s' and DEMANDER_ID='%s')'''
% (contract_type, participant_ukid),
'''delete from xdw_app.dd_fee_amount ddfa where ddfa.CONTRACT_UKID in (select CONTRACT_UKID from
xdw_app.dd_contract where CONTRACT_TYPE = '%s' and DEMANDER_ID='%s')'''
% (contract_type, participant_ukid),
'''delete from xdw_app.ru_labour_log where CONTRACT_SOURCE_UKID in (select CONTRACT_UKID
from xdw_app.dd_contract where CONTRACT_TYPE = '%s' and DEMANDER_IDDEMANDER_ID='%s')'''
% (contract_type, participant_ukid),
'''delete from xdw_app.rs_repository where ppt_relation_ukid in ('%s','%s')'''
% (participant_ukid, creator_ukid),
'''delete from xdw_app.dd_contract_item where CONTRACT_UKID in (select CONTRACT_UKID from
xdw_app.dd_contract where CONTRACT_TYPE = '%s' and DEMANDER_ID='%s')'''
% (contract_type, participant_ukid),
'''delete from xdw_app.dd_contract where CONTRACT_TYPE = '%s' and DEMANDER_ID='%s' '''
% (contract_type, participant_ukid)
]
for sql in sql2:
curs.execute(sql)
con.commit()
close_oracle(con, curs)
print('还原劳务契约成功')
except Exception as e:
print('还原劳务契约失败')
print(e)
close_oracle(con, curs)
def reduction_task(user_id=10001503, invitee_id=10001505): # 还原任务移交
try:
# con, curs = basic_cit()
con, curs = basic_sit() # 删除邀请表信息
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 get_warehouse_contract_ukid(contract_type='RE',
participant_ukid=199145,
contract_ascription='SUPPLY'):
con, curs = basic_sit()
try:
sql = ''
if contract_ascription == 'SUPPLY':
sql = '''select CONTRACT_UKID from xdw_app.dd_contract where CONTRACT_TYPE = '%s' and
SUPPLIER_ID='%s' and contract_status = 20 order by create_time desc ''' \
% (contract_type, participant_ukid)
if contract_ascription == 'DEMAND':
sql = '''select CONTRACT_UKID from xdw_app.dd_contract where CONTRACT_TYPE = '%s' and
DEMANDER_ID='%s' and contract_status = 20 order by create_time desc '''\
% (contract_type, participant_ukid)
curs.execute(sql)
result = curs.fetchall()
if len(result) >= 1:
contract_ukid = result[0][0]
return contract_ukid
else:
return False
except Exception as e:
print('获取仓储契约ukid失败')
print(e)
close_oracle(con, curs)
def delete_transport_contract(user_id, participant_ukid, contract_type='TR'):
# con, curs = basic_cit()
con, curs = basic_sit()
try:
creator_ukid = get_creator_ukid(user_id)
sql2 = [
'''delete from xdw_app.ru_transport_supply_log where CONTRACT_SOURCE_UKID in (select CONTRACT_UKID
from xdw_app.dd_contract where CONTRACT_TYPE = '%s' and SUPPLIER_ID='%s')'''
% (contract_type, participant_ukid),
'''delete from xdw_app.rs_repository where owner_id in ('%s','%s')'''
% (participant_ukid, creator_ukid),
'''delete from xdw_app.dd_contract_item where CONTRACT_UKID in (select CONTRACT_UKID from
xdw_app.dd_contract where CONTRACT_TYPE = '%s' and SUPPLIER_ID='%s'and DEMANDER_ID='%s')'''
% (contract_type, participant_ukid, creator_ukid),
'''delete from xdw_app.dd_contract where CONTRACT_TYPE = '%s' and SUPPLIER_ID='%s' and DEMANDER_ID='%s'
and contract_ascription ='CONTRACT' ''' %
(contract_type, participant_ukid, creator_ukid)
]
for sql in sql2:
curs.execute(sql)
con.commit()
close_oracle(con, curs)
print('删除运输契约成功')
except Exception as e:
print('删除运输契约失败')
print(e)
close_oracle(con, curs)
def reduction_select(invitee_id): # 还原图片选中
try:
# con, curs = basic_cit()
con, curs = basic_sit() # 删除邀请表信息
sql = '''update xdw_app.ba_user set locked='' where user_id='%s' ''' % invitee_id
curs.execute(sql)
con.commit()
close_oracle(con, curs)
print('还原图片选中\n')
except Exception as e:
print(e)
def get_operation(user_id):
try:
# con, curs = basic_cit()
con, curs = basic_sit()
sql = '''select operation_ukid from xdw_app.ts_operation where owner_bu_id='%s'
and card_type in ('AllotManagerAndMonitorService')''' % user_id
curs.execute(sql)
result = curs.fetchall()
close_oracle(con, curs)
return result[0][0]
except Exception as e:
print('还原分配管理者和监控者任务失败')
print(e)
def restore_container():
try:
con, curs = basic_sit()
sql = [
'''update xdw_app.dm_realistic_information o set o.running_status=0 where o.item_ukid=999808
and o.sub_item_ukid=66600101'''
]
for sql1 in sql:
curs.execute(sql1)
con.commit()
close_oracle(con, curs)
except Exception as e:
print(e)
print("数据恢复成功！！！")
def assert_publish_labour():
con, curs = basic_sit()
try:
sql = ['''select o.status from xdw_app.ms_card o where o.card_type='PublishLaborDemand'order by
o.create_time desc''']
for sql1 in sql:
curs.execute(sql1)
cur = curs.fetchone()
print(cur[0])
assert cur[0]==10
close_oracle(con, curs)
except Exception as e:
print(e)
close_oracle(con, curs)
print("数据验证成功")
def reduction_d(operation_ukid):
try:
# con, curs = basic_cit()
con, curs = basic_sit()
print(operation_ukid)
operation_ukid = str(operation_ukid)
operation_ukid1 = operation_ukid + '%'
print(operation_ukid1)
sql = '''delete from xdw_app.ts_tc_relation where sn like '%s' ''' % operation_ukid1
curs.execute(sql)
con.commit()
close_oracle(con, curs)
print('还原分配管理者和监控者任务1')
except Exception as e:
print('还原分配管理者和监控者任务失败')
print(e)
def delete_business_unit(user_id=None):
# con, curs = basic_cit()
con, curs = basic_sit()
# 删除业务单位
try:
# creator_ukid = get_creator_ukid(user_id)
sql = '''delete from xdw_app.au_business_unit where create_user_id='%s' and name not in ('岁月')
''' % user_id
curs.execute(sql)
con.commit()
close_oracle(con, curs)
print('删除管理业务单位成功')
except Exception as e:
print('删除管理业务单位失败')
print(e)
close_oracle(con, curs)
def get_new_business_unit(user_id=10001474):
# 建立业务单位后，查看新生成的记录来进行判断
# con, curs = basic_cit()
con, curs = basic_sit()
try:
dic = {}
# creator_ukid = get_creator_ukid(user_id)
sql = '''select t.name from xdw_app.au_business_unit t where create_user_id = '%s' ''' % user_id
print(sql)
# print(name)
curs.execute(sql)
result1 = curs.fetchall()
print(result1)
return dic
except Exception as e:
print('获取业务单位信息失败')
print(e)
close_oracle(con, curs)
def reduction_transport_contract(contract_ukid='51817000000009223'):
"""
还原运输供应契约
"""
# con, curs = basic_cit()
con, curs = basic_sit()
try:
sql_list = ['''update xdw_app.dd_contract set contract_status=20 where contract_ukid = '%s' ''' % contract_ukid,
'''update xdw_app.dd_contract_item set qty = 3088 where contract_ukid = '%s' ''' % contract_ukid]
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 select_code(self):
"""
数据库查询快递单号
:return:
"""
self.code_list = []
con, curs = basic_sit()
try:
sql = [
'select o.identify_code from xdw_app.cm_identify_relation_package o order by o.update_time desc'
]
curs.execute(sql)
result = curs.fetchone()
for t in result:
self.code_list.append(t[0])
except Exception as e:
print(e, '查询快递单号出错')
def assert_publish_supply():
try:
con, curs = basic_sit()
sql = [
'''select o.status from xdw_app.ms_card o where o.card_type='CreateWarehouseSupply'order by
o.create_time desc'''
]
for sql1 in sql:
curs.execute(sql1)
cur = curs.fetchone()
print(cur[0])
assert cur[0] == 10
close_oracle(con, curs)
except Exception as e:
print(e)
close_oracle(con, curs)
print("数据验证成功")
def get_transport_contract_ukid(participant_ukid, contract_type='TR'):
# con, curs = basic_cit()
con, curs = basic_sit()
try:
sql = '''select CONTRACT_UKID from xdw_app.dd_contract where CONTRACT_TYPE = '%s' and
SUPPLIER_ID='%s' and CONTRACT_ASCRIPTION ='SUPPLY' order by create_time desc ''' \
% (contract_type, participant_ukid)
curs.execute(sql)
result = curs.fetchall()
print(result)
if len(result) >= 1:
contract_ukid = result[0][0]
return contract_ukid
else:
return False
except Exception as e:
print('获取运输契约ukid失败')
print(e)
close_oracle(con, curs)
def get_creator_ukid(user_id):
con, curs = basic_sit()
try:
sql1 = '''select count(*) from xdw_app.au_role where role_type = 1 and participant_ukid='%s' ''' \
% user_id
curs.execute(sql1)
result = curs.fetchall()
if result[0][0] >= 1:
return user_id
else:
sql2 = '''select creator_ukid from xdw_app.hm_creator_relation where participant_ukid='%s' ''' \
% user_id
curs.execute(sql2)
result2 = curs.fetchall()
return result2[0][0]
except Exception as e:
print('获取仓储契约信息失败')
print(e)
close_oracle(con, curs)
def get_warehouse_contract_info(supply_user_id=199145,
contract_ukid=88888002,
contract_ascription='SUPPLY'):
con, curs = basic_sit()
try:
creator_ukid = get_creator_ukid(supply_user_id)
sql = ''
if contract_ascription == 'SUPPLY':
sql = '''select ddc.contract_status,ddci.qty,ppt_name,breach_discount,ddci.start_time,ddci.end_time
from xdw_app.CM_PARTICIPANT cmp,xdw_app.dd_contract ddc,xdw_app.dd_contract_item ddci,
xdw_app.DD_WAREHOUSE_EXTRA_RESOURCE ddwer,xdw_app.dd_breach_define ddbd where cmp.participant_ukid
= '%s' and ddbd.breach_item_code=to_char(ddwer.star_level) and ddwer.contract_item_ukid in
(select CONTRACT_ITEM_UKID from xdw_app.dd_contract_item where CONTRACT_UKID ='%s') and
ddc.contract_ukid = '%s' and ddci.contract_ukid =ddc.contract_ukid''' \
% (creator_ukid, contract_ukid, contract_ukid)
if contract_ascription == 'DEMAND':
sql = '''select ddc.contract_status,ddci.qty,ppt_name,breach_discount,ddci.start_time,ddci.end_time
from xdw_app.CM_PARTICIPANT cmp,xdw_app.dd_contract ddc,xdw_app.dd_contract_item ddci,
xdw_app.DD_WAREHOUSE_EXTRA_RESOURCE ddwer,xdw_app.dd_breach_define ddbd where cmp.participant_ukid =
'%s' and ddbd.breach_item_code=to_char(ddwer.MARKET_PRICING) and ddwer.contract_item_ukid in
(select CONTRACT_ITEM_UKID from xdw_app.dd_contract_item where CONTRACT_UKID ='%s') and
ddc.contract_ukid = '%s' and ddci.contract_ukid =ddc.contract_ukid'''\
% (creator_ukid, contract_ukid, contract_ukid)
curs.execute(sql)
result = curs.fetchall()
if len(result) > 0:
dic = {
'status': result[0][0],
'qty': result[0][1],
'ppt_name': result[0][2],
'overdue_clause': result[0][3],
'start_time': result[0][4],
'end_time': result[0][5]
}
return dic
else:
print('查询无结果')
assert False
except Exception as e:
print('获取仓储契约信息失败')
print(e)
close_oracle(con, curs)
def get_labor_contract_ukid(user_id, participant_ukid, contract_type='LA'):
con, curs = basic_sit()
try:
sql = '''select CONTRACT_UKID from xdw_app.dd_contract where CONTRACT_TYPE = '%s' and
DEMANDER_ID='%s' and supplier_id='%s' order by create_time desc ''' % (
contract_type, participant_ukid, user_id)
curs.execute(sql)
result = curs.fetchall()
print(result)
if len(result) >= 1:
print(len(result))
print('获取劳务契约ukid成功')
contract_ukid = result[0][0]
return contract_ukid
else:
return False
except Exception as e:
print('获取劳务契约ukid失败')
print(e)
close_oracle(con, curs)
def assert_lock_account():
"""
验证锁定数据是否正确
:return:
"""
con, curs = basic_sit()
try:
sql = [
'''select oo.locked from xdw_app.ba_user oo where oo.user_id=10001625'''
]
for sql1 in sql:
curs.execute(sql1)
cur = curs.fetchone()
print(cur[0])
assert cur[0] == 1
close_oracle(con, curs)
except Exception as e:
print(e)
close_oracle(con, curs)
print("锁定数据验证成功")
def reduction_non_service_certification_task(user_id=10001503): # 还原非服务认证任务
try:
# con, curs = basic_cit()
con, curs = basic_sit()
sql1 = [
'''delete from xdw_app.au_role_item 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 card_type = 'ManagerAuthorizeService' and RECEIVER_ID
in (select relation_sn from xdw_app.CM_PARTICIPANT_RELATION where PARTICIPANT_UKID='%s'and relation_status=20)'''
% user_id,
'''delete from xdw_app.TS_OPERATION where pptr_ukid in (select relation_sn from xdw_app.CM_PARTICIPANT_RELATION
where PARTICIPANT_UKID='%s'and relation_status=20) and card_type='ManagerAuthorizeService' '''
% user_id,
'''delete from xdw_app.CM_PARTICIPANT_RELATION where relation_sn in'%s' '''
% user_id,
'''delete from xdw_app.HM_CREATOR_RELATION where participant_ukid ='%s' '''
% user_id,
'''delete from xdw_app.ts_operation tso where tso.pptr_ukid in (select relation_sn from
XDW_APP.CM_PARTICIPANT_relation where PARTICIPANT_UKID in('%s')) ''' %
user_id,
'''delete from xdw_app.ms_card msc where msc.receiver_id in (select relation_sn from
XDW_APP.CM_PARTICIPANT_relation where PARTICIPANT_UKID in('%s')) ''' % 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_monitor_and_manager_permissions(user_id1, user_id2):
try:
# con, curs = basic_cit()
con, curs = basic_sit()
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 = ['''delete from xdw_app.ts_operation tso where tso.pptr_ukid in (select relation_sn 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 (select cmpr.relation_sn from
xdw_app.CM_PARTICIPANT_relation cmpr where PARTICIPANT_UKID in ('%s', '%s')) and card_type in
( 'MonitorAuthorizeService','ManagerAuthorizeService')''' % (user_id1, user_id2)]
for sql in sql3:
curs.execute(sql)
con.commit()
sql4 = ['''delete from xdw_app.ts_operation tso where tso.pptr_ukid in (select relation_sn from
XDW_APP.CM_PARTICIPANT_relation where PARTICIPANT_UKID in('%s','%s')) and
tso.card_type in ('QueryNewEmployeesService', 'PublishWarehouseSupply','PublishWarehouseDemand',
'PublishLaborDemand','PublishWarehouseSupply','PublishGoodsSupply') ''' % (user_id1, user_id2),
'''delete from xdw_app.ms_card where RECEIVER_ID in (select cmpr.relation_sn from
xdw_app.CM_PARTICIPANT_relation cmpr where PARTICIPANT_UKID in ('%s', '%s')) and card_type in
('QueryNewEmployeesService', 'PublishWarehouseSupply','PublishWarehouseDemand',
'PublishLaborDemand','PublishWarehouseSupply','PublishGoodsSupply','PublishTransportSupply','ActiveStockService','PhysicalStoreService','ActiveShopService','ActiveGoodsService')''' % (user_id1, user_id2)]
for sql in sql4:
curs.execute(sql)
con.commit()
close_oracle(con, curs)
print('还原监控者和管理者权限')
except Exception as e:
print(e)
def reduction_labor_contract(contract_ukid=51817000000010130):
"""
还原劳务需求契约
"""
con, curs = basic_sit()
try:
sql_list = [
'''update xdw_app.dd_contract set contract_status=20 where contract_ukid = '%s' '''
% contract_ukid,
'''update xdw_app.dd_contract_item set qty = 2 where contract_ukid = '%s' '''
% contract_ukid
]
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_labor_contract_info(user_id, contract_ukid):
# con, curs = basic_cit()
con, curs = basic_sit()
try:
creator_ukid = get_creator_ukid(user_id)
print(creator_ukid)
sql = '''select DISTINCT ddc.CONTRACT_UKID, (select SALE_PRICE from XDW_APP.dd_unit_price ddup,
XDW_APP.dd_contract_item ddci where ddup.PRICE_UNIT='YUAN_PER_HOUR'and ddci.CONTRACT_UKID='%s'and ddup.CONTRACT_ITEM_UKID=(
select CONTRACT_ITEM_UKID from XDW_APP.dd_contract_item where CONTRACT_UKID='%s'))as hour,
(select SALE_PRICE from XDW_APP.dd_unit_price ddup,XDW_APP.dd_contract_item ddci where ddup.PRICE_UNIT='YUAN_PER_PIECE'
and ddci.CONTRACT_UKID='%s'and ddup.CONTRACT_ITEM_UKID=(select CONTRACT_ITEM_UKID from XDW_APP.dd_contract_item where
CONTRACT_UKID='%s'))as PIECE, ddc.contract_status,ddci.start_time,ddci.end_time,ddci.QTY,cma.country,cma.city,
cma.county,cma.street,cma.address,rslp.work_type,rslp.work_shift_type from XDW_APP.dd_contract ddc,
XDW_APP.dd_contract_item ddci,XDW_APP.cm_address cma,XDW_APP.dd_unit_price ddup,
XDW_APP.rs_labour_publish rslp where ddc.CONTRACT_TYPE = 'LA' and ddc.CONTRACT_ASCRIPTION in('DEMAND') and
ddci.CONTRACT_UKID =ddc.CONTRACT_UKID and ddc.CONTRACT_UKID in('%s')and ddc.demander_id='%s'and
ddci.resource_address = cma.address_ukid and ddup.contract_item_ukid = ddci.CONTRACT_ITEM_UKID and
rslp.labour_publish_ukid = ddci.REPOSITORY_UKID''' \
% (contract_ukid, contract_ukid, contract_ukid, contract_ukid, contract_ukid, creator_ukid)
curs.execute(sql)
result = curs.fetchall()
print(result)
dic = {
'hour': result[0][1],
'piece': result[0][2],
'status': result[0][3],
'start_time': result[0][4],
'end_time': result[0][5],
'qty': result[0][6],
'country': result[0][7],
'street': result[0][8],
'address': result[0][9],
'work_type': result[0][10],
'work_shift_type': result[0][11]
}
return dic
except Exception as e:
print('获取劳务契约信息失败')
print(e)
close_oracle(con, curs)