def get_off_services():
session = get_session('ekomobile')
services = get_class('service_fx')
hstr_services = get_class('hstr_service_fx')
result = []
sers = session.query(hstr_services.object_id, hstr_services.service_id).\
filter(or_(not hstr_services.deactivated,
hstr_services.deactivated > datetime.now())).\
group_by(hstr_services.service_id).all()
print('Get services list')
for rec in sers:
rapi = Rest(ctn=int(rec[0]))
ser_name = session.query(services.bee_sync).filter(
services.i_id == int(rec[1]), services).one()[0]
api_sers = rapi.get_services_list()['services']
for ser in api_sers:
if ser['name'] == ser_name:
result.append([ser_name, ser['removeInd']])
if result[-1][0] != ser_name:
warnings.warn('Kosyak, phone={}, service={}'.format(
rapi.ctn, ser_name))
print('Made {} of {}'.format(sers.index(rec) + 1, len(sers)))
session.close()
try:
ex_write(['code', 'y/n'], result,
"C:/Users/админ/Desktop/remove_services.xlsx")
except Exception:
return result
def _get_data(num=None, login=None, ban=None):
"""Method returns login, password, ban and ban_id from eko_DB
Can getting phone or/and oan
"""
session = get_session('ekomobile')
ctns = get_class('ctn')
agrees = get_class('operator_agree')
accounts = get_class('account_info')
if not num and not ban and not login:
raise InitializationError('Ни один обязательный параметр (num,ban,login) не был передан')
if num:
try:
ban_id = ctns.select(session=session, where={'msisdn': num}).operator_agree
agree = agrees.select(session=session, where={'i_id': ban_id})
except Exception:
print(num)
ban_id = session.query(ctns).filter_by(msisdn=num).first().operator_agree
agree = session.query(agrees).filter_by(i_id=ban_id).first()
elif ban:
agree = agrees.select(session=session, where={'oan': ban})
if agree is not None:
account = accounts.select(session=session, where={'operator_agree': agree.i_id,
'access_type': 1})
session.close()
if account:
return account.login, account.password, agree.oan
def get_off_services():
session = get_session("ekomobile")
services = get_class("service_fx")
hstr_services = get_class("hstr_service_fx")
result = []
sers = (
session.query(hstr_services.object_id, hstr_services.service_id)
.filter(or_(not hstr_services.deactivated, hstr_services.deactivated > datetime.now()))
.group_by(hstr_services.service_id)
.all()
)
print("Get services list")
for rec in sers:
rapi = Rest(ctn=int(rec[0]))
ser_name = session.query(services.bee_sync).filter(services.i_id == int(rec[1]), services).one()[0]
api_sers = rapi.get_services_list()["services"]
for ser in api_sers:
if ser["name"] == ser_name:
result.append([ser_name, ser["removeInd"]])
if result[-1][0] != ser_name:
warnings.warn("Kosyak, phone={}, service={}".format(rapi.ctn, ser_name))
print("Made {} of {}".format(sers.index(rec) + 1, len(sers)))
session.close()
try:
ex_write(["code", "y/n"], result, "C:/Users/админ/Desktop/remove_services.xlsx")
except Exception:
return result
def check_bills():
"""проверка наличия услуг в биллинге"""
ser = get_class("hstr_service_fx")
service_fx = get_class("service_fx")
session = get_session("ekomobile")
file = open("C:/Users/админ/Desktop/1.txt").readlines()
rez_f = []
for el in file:
phone, off, on = el.split(";")
rez_f.append({"phone": phone.rstrip(), "on": on.rstrip(), "off": off.rstrip()})
on = [[el["phone"], el["on"]] for el in rez_f if el["on"] != ""]
off = [[el["phone"], el["off"]] for el in rez_f if el["off"] != ""]
for el in on:
phone, service = el
try:
sid = session.query(service_fx.i_id).filter(service_fx.bee_sync == service.rstrip()).one()[0]
except NoResultFound:
print(el, "неверные параметры, должна быть подключена")
continue
hstr = (
session.query(ser)
.filter(
ser.object_id == phone.rstrip(),
ser.service_id == sid,
ser.activated < datetime.now(),
or_(not ser.deactivated, ser.deactivated > datetime.now()),
)
.all()
)
if len(hstr) == 0:
print(el, " не подключена, должна быть подключена")
for el in off:
phone, service = el
try:
sid = session.query(service_fx.i_id).filter(service_fx.bee_sync == service.rstrip()).one()[0]
except NoResultFound:
print(el, "неверные параметры, должна быть отключена")
continue
hstr = (
session.query(ser)
.filter(
ser.object_id == phone.rstrip(),
ser.service_id == sid,
ser.deactivated < datetime.now(),
or_(not ser.deactivated, ser.deactivated > datetime.now()),
)
.all()
)
if len(hstr) != 0:
print(el, " не отклчена, должна быть отключена")
session.close()
def check_bills():
"""проверка наличия услуг в биллинге"""
ser = get_class('hstr_service_fx')
service_fx = get_class('service_fx')
session = get_session('ekomobile')
file = open('C:/Users/админ/Desktop/1.txt').readlines()
rez_f = []
for el in file:
phone, off, on = el.split(';')
rez_f.append({
'phone': phone.rstrip(),
'on': on.rstrip(),
'off': off.rstrip()
})
on = [[el['phone'], el['on']] for el in rez_f if el['on'] != '']
off = [[el['phone'], el['off']] for el in rez_f if el['off'] != '']
for el in on:
phone, service = el
try:
sid = session.query(service_fx.i_id).filter(
service_fx.bee_sync == service.rstrip()).one()[0]
except NoResultFound:
print(el, 'неверные параметры, должна быть подключена')
continue
hstr = session.query(ser).filter(
ser.object_id == phone.rstrip(), ser.service_id == sid,
ser.activated < datetime.now(),
or_(not ser.deactivated, ser.deactivated > datetime.now())).all()
if len(hstr) == 0:
print(el, ' не подключена, должна быть подключена')
for el in off:
phone, service = el
try:
sid = session.query(service_fx.i_id).filter(
service_fx.bee_sync == service.rstrip()).one()[0]
except NoResultFound:
print(el, 'неверные параметры, должна быть отключена')
continue
hstr = session.query(ser).filter(
ser.object_id == phone.rstrip(), ser.service_id == sid,
ser.deactivated < datetime.now(),
or_(not ser.deactivated, ser.deactivated > datetime.now())).all()
if len(hstr) != 0:
print(el, ' не отклчена, должна быть отключена')
session.close()
def get_detail(beg=0):
ctn = get_class('ctn')
ses = get_session('ekomobile')
ctn_list = ses.query(ctn).filter(ctn.operator_agree.in_([404, 405])).all()
result_detail = []
null_phones = []
for phone in ctn_list[beg:]:
try:
api = Soap(ctn=phone.msisdn)
dt = api.get_current_detail()
except Exception:
print("Последний - {}, {}".format(phone.msisdn,
ctn_list.index(phone)))
print(null_phones)
return
if len(dt) == 0:
null_phones.append(phone.msisdn)
result_detail.extend(dt)
print("Ready {} of {}".format(
ctn_list.index(phone) + 1, len(ctn_list)))
print(null_phones)
ex_write(values=result_detail,
names=[
"Дата", "Исходящий", "Входящий", "Тип соединения",
"Описание звонка", "Трафик", "Стоимость", "Длительность"
],
path="/home/spicin/dt.xlsx")
def get_detail(beg=0):
ctn = get_class("ctn")
ses = get_session("ekomobile")
ctn_list = ses.query(ctn).filter(ctn.operator_agree.in_([404, 405])).all()
result_detail = []
null_phones = []
for phone in ctn_list[beg:]:
try:
api = Soap(ctn=phone.msisdn)
dt = api.get_current_detail()
except Exception:
print("Последний - {}, {}".format(phone.msisdn, ctn_list.index(phone)))
print(null_phones)
return
if len(dt) == 0:
null_phones.append(phone.msisdn)
result_detail.extend(dt)
print("Ready {} of {}".format(ctn_list.index(phone) + 1, len(ctn_list)))
print(null_phones)
ex_write(
values=result_detail,
names=[
"Дата",
"Исходящий",
"Входящий",
"Тип соединения",
"Описание звонка",
"Трафик",
"Стоимость",
"Длительность",
],
path="/home/spicin/dt.xlsx",
)
def get_mass_serv():
session = get_session('ekomobile')
rapi = Rest()
services = get_class('service_fx')
agree = get_class('operator_agree')
ctn = get_class('ctn')
btp = get_class('operator_tarif')
banlist = session.query(agree.i_id).filter(agree.moboperator == 1,
agree.payment_type == 0,
agree.discontinued == 0).all()
banlist = [el[0] for el in banlist]
ctnlist = session.query(ctn.msisdn, ctn.operator_tarif).filter(
ctn.operator_agree.in_(banlist),
ctn.status == 1).group_by(ctn.operator_tarif).all()
result = []
for el in ctnlist:
rapi.change_owner(ctn=int(el[0]))
rez = rapi.get_available_services()['availableServices']
if len(rez) == 0:
print(rapi.ctn)
for r in rez:
tarif = session.query(
btp.name).filter(btp.i_id == int(el[1])).one()[0]
try:
serv = session.query(services.i_id).filter(
services.bee_sync == r['name']).one()[0]
except NoResultFound:
serv = 'Нет в билле!'
result.append([
rapi.ctn, el[1], tarif, serv, r['name'], r['entityName'],
r['rcRate']
])
print('{} из {}'.format(ctnlist.index(el) + 1, len(ctnlist)))
names = ['Номер', 'Тариф', 'Техкод услуги', 'Название услуги', 'АП услуги']
session.close()
try:
ex_write(names, result, path='C:/Users/ГостЪ/Desktop/services.xlsx')
except ValueError:
return result
else:
return
def get_mass_serv():
session = get_session("ekomobile")
rapi = Rest()
services = get_class("service_fx")
agree = get_class("operator_agree")
ctn = get_class("ctn")
btp = get_class("operator_tarif")
banlist = (
session.query(agree.i_id).filter(agree.moboperator == 1, agree.payment_type == 0, agree.discontinued == 0).all()
)
banlist = [el[0] for el in banlist]
ctnlist = (
session.query(ctn.msisdn, ctn.operator_tarif)
.filter(ctn.operator_agree.in_(banlist), ctn.status == 1)
.group_by(ctn.operator_tarif)
.all()
)
result = []
for el in ctnlist:
rapi.change_owner(ctn=int(el[0]))
rez = rapi.get_available_services()["availableServices"]
if len(rez) == 0:
print(rapi.ctn)
for r in rez:
tarif = session.query(btp.name).filter(btp.i_id == int(el[1])).one()[0]
try:
serv = session.query(services.i_id).filter(services.bee_sync == r["name"]).one()[0]
except NoResultFound:
serv = "Нет в билле!"
result.append([rapi.ctn, el[1], tarif, serv, r["name"], r["entityName"], r["rcRate"]])
print("{} из {}".format(ctnlist.index(el) + 1, len(ctnlist)))
names = ["Номер", "Тариф", "Техкод услуги", "Название услуги", "АП услуги"]
session.close()
try:
ex_write(names, result, path="C:/Users/ГостЪ/Desktop/services.xlsx")
except ValueError:
return result
else:
return
def insert_data(classname, u_id, path=None, ctn=False, data=None, test=False):
session = get_session('ekomobile')
c_class = get_class(classname)
if test:
raise AttributeError('Unavailable')
if not path and not data:
raise AttributeError("Required file or data")
if data and not isinstance(data, list):
data = [data]
elif not data:
if input('First line - system names, second and other - values (Y/n)? '
) not in ["y,Y", ""]:
return
file = open(path)
names = [name.strip() for name in file.readline().split('\t')]
ch = False
for name in names:
if name not in c_class.__attr_list__:
print('{} is not attribute of {} class'.format(
name, classname))
ch = True
if ch:
return
data = [
line.rstrip().split('\t') for line in file.readlines()
if line.strip()
]
data = [dict(zip(names, val)) for val in data]
for row in data:
if row == '\n':
continue
to_insert = c_class(date_in=datetime.now(),
date_ch=datetime.now(),
user_id=u_id)
for key in row:
if row[key]:
setattr(to_insert, key, row[key])
session.add(to_insert)
ind = data.index(row) + 1
if ind % 100 == 0 and ind != 0:
print('Inserted {} of {}'.format(ind, len(data)))
print('Commit...')
session.commit()
if test:
objects = dict()
for name in names:
try:
objects[name] = get_class(name)
except DatabaseError:
pass
def print_names(names):
for name in names:
print(name, end="\t")
print(
'\n-------------------------------------------------------------------------------------------'
)
rows = session.query(c_class).all()
print_names(names)
for row in rows:
if rows.index(row) % 20 == 0:
print_names(names)
for name in names:
print(str(getattr(row, name)), end='\t')
print()
session.close()
print('that\'s all')
def update_objects(classname,
key,
u_id,
path='C:/Users/админ/Desktop/1.txt',
insert=False,
test=False):
"""required classname and key"""
session = get_session('ekomobile')
c_class = get_class(classname)
if input('First line - system names, second and other - values (Y/n)? '
) not in ["y", "Y", ""]:
return
if test:
raise AttributeError('Unavailable')
with open(path) as file:
names = [el.strip() for el in file.readline().split('\t')]
ch = False
for name in names:
if name not in c_class.__attr_list__:
warnings.warn('{} is not attribute of {} class'.format(
name, classname))
ch = True
if ch:
return
values = file.readlines()
for val in values:
# if it possible - making digits, else stripping
val_u = [
int(el) if el.strip().isdigit() else el.strip()
for el in val.split('\t')
]
items = dict(zip(names, val_u))
try:
try:
obj_list = session.query(c_class).filter(
getattr(c_class, key) == items[key]).all()
except Exception:
print(items.keys())
raise Exception
except NoResultFound:
if not insert:
logging.log(
logging.DEBUG,
'Classname {} with id = {} not found!'.format(
classname, items[key]))
else:
insert_data(classname, data=[items])
print("Inserted record with key {}".format(items[key]))
else:
for obj in obj_list:
for col_name in items:
# if value not null...
if items[col_name]:
setattr(obj, col_name, items[col_name])
obj.date_ch = datetime.now()
obj.user_id = u_id
session.commit()
ind = values.index(val) + 1
if ind % 100 == 0 and ind != 0:
print('Ready {} of {}'.format(
values.index(val) + 1, len(values)))
print('That\'s all')
if test:
def print_names(names):
for name in names:
print(name, end="\t")
print(
'\n-------------------------------------------------------------------------------------------'
)
rows = session.query(c_class).all()
print_names(names)
for row in rows:
if rows.index(row) % 20 == 0:
print_names(names)
for name in names:
print(str(getattr(row, name)), end='\t')
print()
session.close()
str(phone.pricePlan)
])
print('get {} of {}'.format(ctn_list.index(phone) + 1, len(ctn_list)))
ex_write(values=write_list,
names=[
'msisdn', 'status', 'status_date', 'price_plan',
'price_plan_time'
],
path='/home/spicin/as.xlsx')
if __name__ == "__main__":
login = input('Логин? ')
password = input('Пароль? ')
session = get_session('ekomobile')
user = get_class('user')
try:
u_id = session.query(user.i_id).filter_by(login=login,
password=password).one()
except NoResultFound:
input("Неверные данные для входа")
exit()
ch = int(input('Обновляем(1) или заливаем новое(2)? '))
if ch not in [1, 2]:
input('Нет такого варианта')
exit()
file = input("Введите имя файла (с .txt на конце) ")
classname = input('Имя класса? ')
if ch == 1:
key = input('К чему привязываемся? ')
update_objects(classname, key, u_id, file)
def insert_data(classname, u_id, path=None, ctn=False, data=None, test=False):
session = get_session("ekomobile")
c_class = get_class(classname)
if test:
raise AttributeError("Unavailable")
if not path and not data:
raise AttributeError("Required file or data")
if data and not isinstance(data, list):
data = [data]
elif not data:
if input("First line - system names, second and other - values (Y/n)? ") not in ["y,Y", ""]:
return
file = open(path)
names = [name.strip() for name in file.readline().split("\t")]
ch = False
for name in names:
if name not in c_class.__attr_list__:
print("{} is not attribute of {} class".format(name, classname))
ch = True
if ch:
return
data = [line.rstrip().split("\t") for line in file.readlines() if line.strip()]
data = [dict(zip(names, val)) for val in data]
for row in data:
if row == "\n":
continue
to_insert = c_class(date_in=datetime.now(), date_ch=datetime.now(), user_id=u_id)
for key in row:
if row[key]:
setattr(to_insert, key, row[key])
session.add(to_insert)
ind = data.index(row) + 1
if ind % 100 == 0 and ind != 0:
print("Inserted {} of {}".format(ind, len(data)))
print("Commit...")
session.commit()
if test:
objects = dict()
for name in names:
try:
objects[name] = get_class(name)
except DatabaseError:
pass
def print_names(names):
for name in names:
print(name, end="\t")
print("\n-------------------------------------------------------------------------------------------")
rows = session.query(c_class).all()
print_names(names)
for row in rows:
if rows.index(row) % 20 == 0:
print_names(names)
for name in names:
print(str(getattr(row, name)), end="\t")
print()
session.close()
print("that's all")
def update_objects(classname, key, u_id, path="C:/Users/админ/Desktop/1.txt", insert=False, test=False):
"""required classname and key"""
session = get_session("ekomobile")
c_class = get_class(classname)
if input("First line - system names, second and other - values (Y/n)? ") not in ["y", "Y", ""]:
return
if test:
raise AttributeError("Unavailable")
with open(path) as file:
names = [el.strip() for el in file.readline().split("\t")]
ch = False
for name in names:
if name not in c_class.__attr_list__:
warnings.warn("{} is not attribute of {} class".format(name, classname))
ch = True
if ch:
return
values = file.readlines()
for val in values:
# if it possible - making digits, else stripping
val_u = [int(el) if el.strip().isdigit() else el.strip() for el in val.split("\t")]
items = dict(zip(names, val_u))
try:
try:
obj_list = session.query(c_class).filter(getattr(c_class, key) == items[key]).all()
except Exception:
print(items.keys())
raise Exception
except NoResultFound:
if not insert:
logging.log(logging.DEBUG, "Classname {} with id = {} not found!".format(classname, items[key]))
else:
insert_data(classname, data=[items])
print("Inserted record with key {}".format(items[key]))
else:
for obj in obj_list:
for col_name in items:
# if value not null...
if items[col_name]:
setattr(obj, col_name, items[col_name])
obj.date_ch = datetime.now()
obj.user_id = u_id
session.commit()
ind = values.index(val) + 1
if ind % 100 == 0 and ind != 0:
print("Ready {} of {}".format(values.index(val) + 1, len(values)))
print("That's all")
if test:
def print_names(names):
for name in names:
print(name, end="\t")
print("\n-------------------------------------------------------------------------------------------")
rows = session.query(c_class).all()
print_names(names)
for row in rows:
if rows.index(row) % 20 == 0:
print_names(names)
for name in names:
print(str(getattr(row, name)), end="\t")
print()
session.close()
write_list = []
for phone in ctn_list:
write_list.append([str(phone.ctn[1:]), str(phone.status), str(phone.statusDate), str(phone.pricePlan)])
print("get {} of {}".format(ctn_list.index(phone) + 1, len(ctn_list)))
ex_write(
values=write_list,
names=["msisdn", "status", "status_date", "price_plan", "price_plan_time"],
path="/home/spicin/as.xlsx",
)
if __name__ == "__main__":
login = input("Логин? ")
password = input("Пароль? ")
session = get_session("ekomobile")
user = get_class("user")
try:
u_id = session.query(user.i_id).filter_by(login=login, password=password).one()
except NoResultFound:
input("Неверные данные для входа")
exit()
ch = int(input("Обновляем(1) или заливаем новое(2)? "))
if ch not in [1, 2]:
input("Нет такого варианта")
exit()
file = input("Введите имя файла (с .txt на конце) ")
classname = input("Имя класса? ")
if ch == 1:
key = input("К чему привязываемся? ")
update_objects(classname, key, u_id, file)
elif ch == 2:
