def one_commodity_not_deliver():
con, curs = sit()
sql = ['''delete from xdw_app.im_platform_item t where t.sku_num_id in (3103351104677,3103351104877)''',
'''insert into xdw_app.im_platform_item (PLATFORM_ITEM_UKID, PLATFORM_ID, SHOP_ID, OWNER_ID, IDENTIFY_CODE,
IDENTIFY_UKID, ITEM_STATUS, PRODUCT_NUM_ID, PRODUCT_OUTER_ID, SKU_NUM_ID, SKU_OUTER_ID, PRODUCT_TITLE, SKU_NAME,
SELL_PRICE, PLATFORM_IMG_URL, BRAND_NAME, CATEGORY, CREATE_TIME, CREATE_USER_ID, UPDATE_TIME, UPDATE_USER_ID,
ITEM_COUNT)values ('51768000000008002', '10', '251627', '251627', '34143554352kz7206002110', '51768800000008007',
'30', '1489161932', '34143554352', '3103351104677', 'kz7206002110',
'宝宝蝴蝶结打底裙裤 2016秋季女童童装 儿童长裤打底裤女 kz-7206', null, '0',
'http://img03.taobao.net/bao/uploaded/i3/T1HXdXXgPSt0JxZ2.8_070458.jpg', null, null,
to_date('03-11-2016 20:10:05', 'dd-mm-yyyy hh24:mi:ss'), '199284',
to_date('03-11-2016 20:10:05', 'dd-mm-yyyy hh24:mi:ss'), null, '8888')''',
'''insert into xdw_app.im_platform_item (PLATFORM_ITEM_UKID, PLATFORM_ID, SHOP_ID, OWNER_ID, IDENTIFY_CODE,
IDENTIFY_UKID, ITEM_STATUS, PRODUCT_NUM_ID, PRODUCT_OUTER_ID, SKU_NUM_ID, SKU_OUTER_ID, PRODUCT_TITLE, SKU_NAME,
SELL_PRICE, PLATFORM_IMG_URL, BRAND_NAME, CATEGORY, CREATE_TIME, CREATE_USER_ID, UPDATE_TIME, UPDATE_USER_ID,
ITEM_COUNT)values ('51768200000009001', '10', '251627', '251627', '34143000001kz7206002111', '51768900000008006', '90',
'8888800001', '34143000001', '3103351104877', 'kz7206002111', '宝宝尿不湿 儿童长裤打底裤女 kz-7206', null, '0',
'http://taobao.net/bao/uploaded/i0.jpg', null, null, to_date('03-11-2016 20:10:05', 'dd-mm-yyyy hh24:mi:ss'), '199284',
to_date('03-11-2016 20:10:06', 'dd-mm-yyyy hh24:mi:ss'), null, '9999')
''']
for sql1 in sql:
curs.execute(sql1)
con.commit()
close_oracle(con, curs)
pass
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 delete_entity_shop(user_id=None):
try:
con, curs = sit()
sql = [ # 恢复激活实体店的输入项
'''delete from xdw_app.ms_card t where t.card_step = '1' and t.owner_bu_id= '%s' '''
% user_id,
'''delete from xdw_app.im_item_area t where t.building_ukid = (select t.sales_building_ukid
from basic_owner.ba_sales_building t where t.item_ukid =(select t.item_ukid from basic_owner.IM_ITEM t where
t.create_user_id = '%s' and t.item_type = 'PHY_STORE'))''' % user_id,
'''delete from xdw_app.IM_ITEM_ADDRESS t where t.building_ukid = (select t.sales_building_ukid
from basic_owner.ba_sales_building t where t.item_ukid =(select t.item_ukid from basic_owner.IM_ITEM t where
t.create_user_id = '%s' and t.item_type = 'PHY_STORE'))''' %
user_id,
'''delete from xdw_app.IM_ITEM t where t.create_user_id = '%s' and t.item_type = 'PHY_STORE' '''
% user_id,
'''delete from xdw_app.ba_shop t where t.create_user_id='%s' ''' %
user_id,
'''delete from xdw_app.ms_card t where t.owner_bu_id = '%s' and t.card_name = '激活实体店'
and t.card_step='3' ''' % user_id
]
for sql1 in sql:
curs.execute(sql1)
con.commit()
close_oracle(con, curs)
print('恢复实体店原始状态成功，不含杂质\n')
except Exception as e:
print(e)
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 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 delete_warehouse_card(user_id=None):
try:
con, curs = cit()
sql = [ # 恢复激活实体店的输入项
'''update xdw_app.ts_operation t set t.status = '0' where t.operation_name = '激活仓库' and
t.owner_bu_id = '%s' ''' % user_id,
'''update xdw_app.ms_card t set t.status = '0' where t.card_name = '激活仓库' and
t.owner_bu_id = '%s' ''' % user_id,
'''update xdw_app.cm_participant_relation t set t.relation_status = '20'
where t.ppt_relation_ukid =(select t.receiver_id from xdw_app.ms_card t where t.owner_bu_id = '%s' and
t.card_name = '激活仓库' )''' % user_id,
'''delete from xdw_app.im_item_area t where t.building_ukid =
(select t.warehouse_building_ukid from xdw_app.ba_warehouse_building t where t.item_ukid =
(select t.item_ukid from xdw_app.IM_ITEM t where t.create_user_id = '%s' and t.item_type = '仓库'))'''
% user_id,
'''delete from xdw_app.IM_ITEM_ADDRESS t where t.building_ukid =
(select t.warehouse_building_ukid from xdw_app.ba_warehouse_building t where t.item_ukid =
(select t.item_ukid from xdw_app.IM_ITEM t where t.create_user_id = '%s' and t.item_type = '仓库'))'''
% user_id,
'''delete from xdw_app.im_warehouse_item t where t.item_ukid =
(select t.item_ukid from xdw_app.IM_ITEM t where t.create_user_id = '%s' and t.item_type = '仓库')'''
% user_id,
'''delete from xdw_app.dd_resources_cost t where t.item_ukid =
(select t.item_ukid from xdw_app.IM_ITEM t where t.create_user_id = '%s' and t.item_type = '仓库')'''
% user_id
]
for sql1 in sql:
curs.execute(sql1)
con.commit()
close_oracle(con, curs)
print('恢复仓库原始状态成功，不含杂质\n')
except Exception as e:
print(e)
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 reduction_non_service_certification_task(user_id): # 还原非服务认证任务
try:
con, curs = basic_cit()
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 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 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=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_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 _get_long_card_position(user_id, card_name):
card_list = []
# con, curs = cit()
# con, curs = basic_sit()
if get_env_script_runs_on().lower() == 'cit':
con, curs = cit()
else:
con, curs = sit()
try:
sql = ''' select card_name from xdw_app.v_feature_card v where 1=1 and owner_bu_id =
(select h.creator_ukid from xdw_app.hm_creator_relation h where h.participant_ukid = '%s')
and receiver_id='%s' and client_show >=0 order by card_name''' % (
user_id, user_id)
curs.execute(sql)
result = curs.fetchall()
for r in result:
card_list.append(r[0])
try:
position = card_list.index(card_name) + 1
except Exception as e:
print(e, 'Get Card List fail.')
raise GetCardListFailureException
return position
except Exception as e:
print('获取长期卡片出错')
print(e)
close_oracle(con, curs)
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 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 get_test_code():
"""
获取验证码
:return:
"""
con, curs = cit()
try:
#select a.send_content from xdw_app.sa_sms a where a.create_time<trunc(sysdate+1) order by a.create_time desc
sql = [
'''
SELECT a.send_content FROM xdw_app.sa_sms a WHERE a.create_time<trunc(sysdate+1)
AND a.mobile_no='enc_23EB56B76F418687F02A9137C891FCE3'ORDER BY a.create_time DESC '''
]
for sql1 in sql:
curs.execute(sql1)
cur = curs.fetchone()
a = str(cur)
import re
line = re.findall(r"\d+\.?\d*", a)
close_oracle(con, curs)
print(line[0])
return line[0]
except Exception as e:
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 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 release_labor_demand(userid=199122):
con, curs = basic_cit_01()
try:
sql = [
''' delete from XDW_APP.dd_contract_item o where o.contract_ukid in
(select t.contract_ukid from XDW_APP.dd_contract t where t.create_user_id='%s' )'''
% userid,
'''delete from XDW_APP.RU_LABOUR_LOG m where m.create_user_id='%s' '''
% userid,
'''delete from XDW_APP.dd_unit_price n where n.create_user_id='%s' '''
% userid,
'''delete from XDW_APP.rs_labour_publish k where k.create_user_id='%s' '''
% userid,
'''update XDW_APP.dd_contract x set x.contract_status=10 where x.create_user_id='%s' '''
% userid,
'''update XDW_APP.ts_operation y set y.status=0 where y.create_user_id='%s' '''
% userid,
'''update XDW_APP.MS_CARD z set z.status=0 where z.receiver_id='%s' '''
% userid
]
for sql1 in sql:
curs.execute(sql1)
con.commit()
close_oracle(con, curs)
except Exception as e:
print(e)
close_oracle(con, curs)
print("数据恢复成功！！！")
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 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 get_new_permission_recovery(participant_ukid=None):
# 权限回收成功后，通过数量来判断
con, curs = basic_cit()
# con, curs = basic_sit()
try:
dic = {}
# creator_ukid = get_creator_ukid(user_id)
sql1 = '''select count(*) from xdw_app.au_role_item t where t.status = 0 and participant_ukid = '%s' ''' \
% participant_ukid
print(sql1)
curs.execute(sql1)
result = curs.fetchall()
dic['recovery_record_no'] = result[0][0]
sql2 = ''' select count(*) from xdw_app.au_relation t where t.status = 0 and grantee ='%s' ''' \
% participant_ukid
print(sql2)
curs.execute(sql2)
result2 = curs.fetchall()
dic['permission_record_no'] = result2[0][0]
sql3 = '''select count(*) from xdw_app.au_auth_log t where t.operation_type = 52 and grantee ='%s' '''\
% participant_ukid
print(sql3)
curs.execute(sql3)
result3 = curs.fetchall()
dic['log_no'] = result3[0][0]
return dic
except Exception as e:
print('权限收回失败')
print(e)
close_oracle(con, curs)
def commodity_none(): # 商品都未铺货
con, curs = cit()
sql = ['''delete from xdw_app.im_platform_item t where t.sku_num_id in (3103351104677,3103351104877)''']
curs.execute(sql)
con.commit()
close_oracle(con, curs)
pass
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 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 release_transport_supply(userid=199161):
try:
con, curs = basic_cit_01()
sql = [
''' delete from XDW_APP.dd_contract_item o where o.contract_ukid in
(select t.contract_ukid from XDW_APP.dd_contract t where t.create_user_id='%s' )'''
% userid,
'''delete from XDW_APP.RS_TRANSPORT_PUBLISH m where m.create_user_id='%s' '''
% userid,
'''delete from XDW_APP.dd_unit_price n where n.create_user_id='%s' '''
% userid,
'''delete from XDW_APP.ru_transport_supply_log k where k.create_user_id='%s' '''
% userid,
'''update XDW_APP.dd_contract x set x.contract_status=10 where x.create_user_id='%s' '''
% userid,
'''update XDW_APP.ts_operation y set y.status=0 where y.create_user_id='%s' '''
% userid,
'''update XDW_APP.MS_CARD z set z.status=0 where z.receiver_id='%s' '''
% userid
]
for sql1 in sql:
curs.execute(sql1)
con.commit()
close_oracle(con, curs)
except Exception as e:
print(e)
print("数据恢复成功！！！")
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 activation_depot_status(admin=None):
con, curs = sit()
sql = '''select t.client_show from xdw_app.ms_card t where t.owner_bu_id = '%s'and t.card_name = '激活仓库' ''' % admin
curs.execute(sql)
status = curs.fetchone()
print(status[0])
assert_equal(status[0], 1)
close_oracle(con, curs)
pass
def reduction_select(invitee_id): # 还原图片选中
try:
con, curs = basic_cit() # 删除邀请表信息
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_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)
