def delete_account(con, login, inc):
"""Disables access to ML for a given AD login."""
stmt = """ UPDATE ml.bezp_kontroladostepu SET
enabled = 'F',
uwagi = '{} likwidacja konta'
WHERE LOWER(login) = LOWER('{}') """.format(inc['inc'], login)
return execute_dml(con, stmt)
def set_sim_status_bscs(con, sim, status):
"""Changes SIM status in BSCS."""
stmt = """
update storage_medium@bscs_nra sm
set sm.sm_status = '{1}'
WHERE sm.sm_serialnum IN ('{0}')
""".format(sim, status)
return execute_dml(con, stmt)
def set_imsi_status_bscs(con, imsi, status):
"""Changes IMSI status in BSCS."""
stmt = """
update port@bscs_nra p
set p.port_status = '{1}'
WHERE p.port_num IN ('{0}')
""".format(imsi, status)
return execute_dml(con, stmt)
def set_sim_status_nra(con, sim, status):
"""Changes SIM status in nRA."""
stmt = """
update karty_sim s
set S.STATUS = '{1}'
where sim in ('{0}')
""".format(sim, status)
return execute_dml(con, stmt)
def recertify_account(con, login, date, profile, inc):
"""Modifies access data - access profile and account validity date"""
stmt = """ UPDATE ml.bezp_kontroladostepu SET
enabled = 'T',
uwagi = '{inc} recertyfikacja',
expiration_date = to_date('{date}', 'YYYY-MM-DD HH24:MI:SS'),
profile = '{profile}'
WHERE LOWER(login) = LOWER('{login}') """.format(
login=login, date=date, profile=profile, inc=inc)
return execute_dml(con, stmt)
def create_account(con, login, date, profile, inc):
"""Create a new access to ML for a given AD login."""
stmt = """ INSERT INTO ml.bezp_kontroladostepu (
login, expiration_date, enabled, profile, uwagi, id, bscs_dealer_id, bscs_dealer_code, bscs_code )
SELECT '{login}', to_date('{date}', 'YYYY-MM-DD HH24:MI:SS'), 'T', '{profile}', '{inc} zalozenie konta',
(SELECT MAX(id) + 1 FROM ml.bezp_kontroladostepu), -45800, 'KTML1', 'KTML1'
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM ml.bezp_kontroladostepu WHERE enabled = 'T'
AND LOWER(login) = LOWER('{login}')) """.format(
login=login, date=date, profile=profile, inc=inc)
return execute_dml(con, stmt)
def unlock_account(con, login):
"""Unlock user account and reset password to the default value of 'centertel'."""
stmt = 'UPDATE ptk_otsa_user u SET u.password = \'xhjt1p6C4H\', u.status = \'A\', ' + \
'u.Last_Password_Change= sysdate, u.key_active =\'Y\', u.prev_failed_logins = u.failed_logins, ' + \
'u.failed_logins = 0, u.last_login = sysdate Where U.Login = \'' + login + '\''
return execute_dml(con, stmt)
def update_cart(con, trans_code, cart_code):
"""Transfer the transaction to another cart."""
stmt = 'update ptk_otsa_transaction set cart_code = \'' + cart_code + \
'\' where trans_code = \'' + trans_code + '\''
return execute_dml(con, stmt)
def fix_aac(con, trans_code, bscs_customer_id):
"""Fix contract with 'ACCOUNT ALREADY CREATED' error."""
stmt = "update ptk_otsa_transaction set bscs_customer_id = '" + str(bscs_customer_id) + \
"', new_customer = 'N' where trans_code = '" + str(trans_code) + "'"
return execute_dml(con, stmt)
def fix_pesel(con, trans_code):
"""Delete PESEL number from contract."""
stmt = 'update ptk_otsa_transaction set comptaxno = \'\', pers_comptaxno = \'\'' + \
'where trans_code = \'' + str(trans_code) + '\''
return execute_dml(con, stmt)
def fix_90100(con, trans_code):
"""Update country in the address to fix 90100 error."""
stmt = 'update ptk_otsa_trans_address set country = 18 where trans_code = \'' + str(
trans_code) + '\''
return execute_dml(con, stmt, expected_rowcount=3)
def update_contract(con, trans_code, status):
"""Update contract status."""
stmt = 'update ptk_otsa_trans_contract set status = \'' + str(status) + '\' ' + \
'where trans_code = \'' + str(trans_code) + '\''
return execute_dml(con, stmt)
def update_transaction(con, trans_code, status):
"""Update transaction status."""
stmt = 'update ptk_otsa_transaction set status = \'' + str(status) + '\' ' \
+ 'where trans_code = \'' + str(trans_code) + '\''
return execute_dml(con, stmt)
def set_trans_no(con, custcode, trans_no):
"""Sets trans_no for a given custcode."""
stmt = 'update ptk_otsa.ptk_otsa_resources set trans_no = ' + str(
trans_no) + ' where subtrans_no=0 and type=\'CUSTCODE_S\' ' \
'and value = \'' + str(custcode) + '\''
execute_dml(con, stmt)
def link_login_with_ifs(con, login):
stmt = "Insert into dicts.EXTERNAL_IDENTIFIERS_CBT Values (33771, 'OTSA', '{}')".format(
login)
return execute_dml(con, stmt)
def set_order_status(con, order_id, status):
"""Set order status for a given order_id."""
stmt = 'update rsw.rsw_zamowienia set status = ' + str(status) + ' where id_zamowienia = ' + str(order_id)
return execute_dml(con, stmt)
def cancel_order(con, ord_id):
stmt = """update om_order set ord_state = 'CANCELLED' where ord_id = '{}'""".format(
ord_id)
execute_dml(con, stmt)
