def test_ini_class_phones_ini_write():
settings.mbplugin_root_path = 'tests\\data'
ini = store.ini('phones.ini')
phones = ini.phones()
print(f'inipath={ini.inipath}')
print(f'mbplugin_root_path={settings.mbplugin_root_path}')
expected_result1 = [('region', 'p_test1'), ('monitor', 'TRUE'),
('alias', 'Иваныч'), ('number', '9161112233'),
('balancenotchangedmorethen', '40'),
('balancechangedlessthen', '1'),
('balancelessthen', '100'), ('turnofflessthen', '1')]
expected_result2 = {
'NN': 1,
'Alias': 'Иваныч',
'Region': 'p_test1',
'Number': '9161112233',
'PhoneDescription': '',
'Monitor': 'TRUE',
'BalanceLessThen': 100.0,
'TurnOffLessThen': 1,
'BalanceNotChangedMoreThen': 40,
'BalanceChangedLessThen': 1,
'Password2': '123password'
}
assert list(ini.ini['1'].items()) == expected_result1
assert phones[('9161112233', 'p_test1')] == expected_result2
def report(self):
''' Генерирует отчет по последнему состоянию телефонов'''
reportsql = f'''select * from phones where QueryDateTime in (SELECT max(QueryDateTime) FROM Phones GROUP BY PhoneNumber,Operator order by Operator,PhoneNumber)'''
cur = self.cur.execute(reportsql)
dbheaders = list(zip(*cur.description))[0]
dbdata = cur.fetchall()
phones = store.ini('phones.ini').phones()
dbdata.sort(
key=lambda line: (phones.get(line[0:2], {}).get('NN', 999)))
# округляем float до 2х знаков
dbdata = [
tuple([(round(i, 2) if type(i) == float else i) for i in line])
for line in dbdata
]
table = [] # результат - каждая строчка словарь элементов
for line in dbdata:
row = dict(zip(dbheaders, line))
pair = (row['PhoneNumber'], row['Operator']
) # Пара PhoneNumber,Operator
row['Alias'] = phones.get(pair, {}).get('Alias', 'Unknown')
row['NN'] = phones.get(pair, {}).get('NN', 999)
row['PhoneNumberFormat1'] = row['PhoneNumberFormat2'] = row[
'PhoneNumber']
if type(row['PhoneNumber']) == str and row['PhoneNumber'].isdigit(
):
# форматирование телефонных номеров
row['PhoneNumberFormat1'] = re.sub(
r'\A(\d{3})(\d{3})(\d{4})\Z', '(\\1) \\2-\\3',
row['PhoneNumber'])
row['PhoneNumberFormat2'] = row['PhoneNumberFormat1'].replace(
' ', '')
table.append(row)
return table
def __init__(self, dbname):
self.dbname = dbname
DRV = '{Microsoft Access Driver (*.mdb)}'
self.conn = pyodbc.connect(f'DRIVER={DRV};DBQ={dbname}')
self.cur = self.conn.cursor()
rows = self.cur.execute('SELECT top 1 * FROM phones')
self.phoneheader = list(zip(*rows.description))[0]
phones_ini = store.ini('phones.ini').read()
# phones - словарь key=MBphonenumber values=[phonenumber,region]
self.phones = {v['Number']: (re.sub(r' #\d+', '', v['Number']), v['Region'])
for k, v in phones_ini.items() if k.isnumeric() and 'Monitor' in v}
def test_ini_class_mbplugin_ini_write():
ini_path = os.path.abspath('tests\\data\\mbplugin.ini')
shutil.copyfile(ini_path + '.ori', ini_path)
ini = store.ini()
ini.fn = 'mbplugin.ini'
ini.inipath = ini_path
print(f'inipath={ini.inipath}')
ini.read()
ini.ini['Options']['show_chrome'] = '0'
ini.write()
assert not filecmp.cmp(ini_path + '.ori',
ini_path) # Проверяем что файл изменился
ini.ini['Options']['show_chrome'] = '1'
ini.write()
assert filecmp.cmp(
ini_path + '.ori', ini_path
) # Проверяем идентичность первоначального и сохраненного файла
def write_result(self, plugin, login, result, commit=True):
'Записывает результат в базу'
# Делаем копию, чтобы не трогать оригинал
result2 = {k: v for k, v in result.items()}
# Исправляем поля которые в response называются не так как в базе
if type(result2['Balance']) == str:
result2['Balance'] = float(result2['Balance'])
if 'Currency' in result2: # Currency -> Currenc
result2['Currenc'] = result2['Currency']
if 'Min' in result2: # Min -> Minutes
result2['Minutes'] = result2['Min']
if 'BalExpired' in result2: # BalExpired -> BeeExpired
result2['BeeExpired'] = result2['BalExpired']
# Фильтруем только те поля, которые есть в таблице phone
line = {k: v for k, v in result2.items() if k in self.phoneheader}
# Добавляем расчетные поля и т.п.
line['Operator'] = plugin
line['PhoneNumber'] = login # PhoneNumber=PhoneNum
line['QueryDateTime'] = datetime.datetime.now().replace(microsecond=0) # no microsecond
self.cur.execute(f"select cast(julianday('now')-julianday(max(QueryDateTime)) as integer) from phones where phonenumber='{login}' and operator='{plugin}' and abs(balance-{result['Balance']})>0.02")
line['NoChangeDays'] = self.cur.fetchall()[0][0] # Дней без изм.
options_ini = store.ini('Options.ini').read()
if 'Additional' in options_ini and 'AverageDays' in options_ini['Additional']:
average_days = int(options_ini['Additional']['AverageDays'])
else:
average_days = settings.ini['Options']['average_days']
self.cur.execute(f"select {line['Balance']}-balance from phones where phonenumber='{login}' and operator='{plugin}' and QueryDateTime>date('now','-{average_days} day') and strftime('%Y%m%d', QueryDateTime)<>strftime('%Y%m%d', date('now')) order by QueryDateTime desc limit 1")
qres = self.cur.fetchall()
if qres != []:
line['BalDelta'] = round(qres[0][0], 2) # Delta (день)
self.cur.execute(f"select {line['Balance']}-balance from phones where phonenumber='{login}' and operator='{plugin}' order by QueryDateTime desc limit 1")
qres = self.cur.fetchall()
if qres != []:
line['BalDeltaQuery'] = round(qres[0][0], 2) # Delta (запрос)
self.cur.execute(f"select avg(b) from (select min(BalDelta) b from phones where phonenumber='{login}' and operator='{plugin}' and QueryDateTime>date('now','-{average_days} day') group by strftime('%Y%m%d', QueryDateTime))")
qres = self.cur.fetchall()
if qres != [] and qres[0][0] is not None:
line['RealAverage'] = round(qres[0][0], 2) # $/День(Р)
if line.get('RealAverage', 0.0) < 0:
line['CalcTurnOff'] = round(-line['Balance'] / line['RealAverage'], 2)
self.cur.execute(f'insert into phones ({",".join(line.keys())}) VALUES ({",".join(list("?"*len(line)))})', list(line.values()))
if commit:
self.conn.commit()
def test_zeroinstall():
tmp = tempfile.TemporaryDirectory()
settings.mbplugin_root_path = tmp.name
ini = store.ini()
print(f'Zeroinstall ini={os.path.abspath(ini.inipath)}')
print(
f'Zeroinstall ini exists={os.path.exists(os.path.abspath(ini.inipath))}'
)
assert os.path.exists(os.path.abspath(ini.inipath)) == False
print(f'Check {ini.read()}')
assert os.path.exists(os.path.abspath(ini.inipath)) == True
# assert re.search(chk, response1.text) is not None
ini.save_bak()
ini.ini_to_json()
ini.write()
ini.create()
assert ini.find_files_up(ini.fn) == os.path.abspath(ini.inipath)
print(f'{list(ini.ini.keys())}')
#breakpoint()
#print(f'Check {ini.ini_to_json()}')
tmp.cleanup()
def getreport(param=[]):
style = '''<style type="text/css">
table{font-family: Verdana; font-size:85%}
th {background-color: #D1D1D1}
td{white-space: nowrap;text-align: right;}
.hdr {text-align:left;color:#FFFFFF; font-weight:bold; background-color:#0E3292; padding-left:5}
.n {background-color: #FFFFE1}
.e {background-color: #FFEBEB}
.n_us {background-color: #FFFFE1; color: #808080}
.e_us {background-color: #FFEBEB; color: #808080}
.mark{color:#FF0000}
.mark_us{color:#FA6E6E}
.summ{background-color: lightgreen; color:black}
.p_n{color:#634276}
.p_r{color:#006400}
.p_b{color:#800000}
#Balance, #SpendBalance {text-align: right; font-weight:bold}
#Indication, #Alias, #KreditLimit, #PhoneDescr, #UserName, #PhoneNum, #PhoneNumber, #BalExpired, #LicSchet, #TarifPlan, #BlockStatus, #AnyString, #LastQueryTime{text-align: left}
</style>'''
template = '''
<?xml version="1.0" encoding="windows-1251" ?>
<html>
<head><title>MobileBalance</title></head>{style}
<body style="font-family: Verdana; cursor:default">
<table id="BackgroundTable">
<tr><td class="hdr">Информация о балансе телефонов - MobileBalance Mbplugin</td></tr>
<tr><td bgcolor="#808080">
<table id="InfoTable" border="0" cellpadding="2" cellspacing="1">
<tr id="header">{html_header}</tr>
{html_table}
</table>
</td></tr>
</table>
</body>
</html>'''
db = dbengine.dbengine(store.options('dbfilename'))
# номера провайдеры и логины из phones.ini
options_ini = store.ini('options.ini').read()
edBalanceLessThen = float(
options_ini['Mark']
['edBalanceLessThen']) # помечать балансы меньше чем
edTurnOffLessThen = float(
options_ini['Mark']['edTurnOffLessThen']
) # помечать когда отключение CalcTurnOff меньше чем
num_format = '' if len(param) == 0 or not param[0].isnumeric() else str(
int(param[0]))
table_format = store.options('table_format' + num_format,
default=store.options('table_format',
section='HttpServer'),
section='HttpServer')
table = db.report()
if 'Alias' not in table_format:
table_format = 'NN,Alias,' + table_format # Если старый ini то этих столбцов нет - добавляем
table = [i for i in table if i['Alias'] != 'Unknown'] # filter Unknown
table.sort(
key=lambda i: [i['NN'], i['Alias']]) # sort by NN, after by Alias
header = table_format.strip().split(',')
# классы для формата заголовка
header_class = {
'Balance': 'p_b',
'RealAverage': 'p_r',
'BalDelta': 'p_r',
'BalDeltaQuery': 'p_r',
'NoChangeDays': 'p_r',
'CalcTurnOff': 'p_r',
'MinAverage': 'p_r',
}
html_header = ''.join([
f'<th id="h{h}" class="{header_class.get(h,"p_n")}">{dbengine.PhonesHText.get(h,h)}</th>'
for h in header
])
html_table = []
for line in table:
html_line = []
for he in header:
if he not in line:
continue
el = line[he]
mark = '' # class="mark"
if he == 'Balance' and el is not None and el < edBalanceLessThen:
mark = ' class="mark" ' # Красим когда мало денег
if he == 'CalcTurnOff' and el is not None and el < edTurnOffLessThen:
mark = ' class="mark" ' # Красим когда надолго не хватит
if el is None:
el = ''
if he != 'Balance' and (el == 0.0 or el == 0):
el = ''
html_line.append(
f'<{"th" if he=="NN" else "td"} id="{he}"{mark}>{el}</td>')
html_table.append(f'<tr id="row" class="n">{"".join(html_line)}</tr>')
res = template.format(style=style,
html_header=html_header,
html_table='\n'.join(html_table))
return 'text/html', [res]