def check_t12receiver():
idb_t1 = dbtools3.dbtools("host=10.10.2.241 dbname=contracts port=5432 user=smirnov")
idb_rcv = dbtools3.dbtools("host=10.10.2.241 dbname=receiver port=5432 user=smirnov")
def is_activ(id_dev, inn, gosnum):
global count_activ
query = "SELECT * FROM recv_ts WHERE gosnum = '%s'" % gosnum
dts = idb_rcv.get_dict(query)
if dts:
if id_dev == dts['device_id']:
count_activ += 1
return dts['rem']
if dts['rem']:
count_activ += 1
return dts['device_id'], dts['rem']
return dts['device_id'], False
else: return "Not TS"
curr_tm = int(time.time())
query = "SELECT id_dev, inn, gosnum FROM t1_atts WHERE inn IN (SELECT inn FROM t1_orgs WHERE bm_ssys = 131072) AND last_tm > %s;" % (curr_tm - 172800)
rows = idb_t1.get_rows(query)
d = idb_t1.desc
for r in rows:
# break
id_dev, inn, gosnum = r
print("%18s %16s\t%s" % (r[d.index('id_dev')], r[d.index('gosnum')], r[d.index('inn')]), is_activ(id_dev, inn, gosnum))
print("len rows:", len(rows))
print("count_activ:", count_activ)
def find_actual_ts(gntss):
dbi = dbt.dbtools(ids_db['cntr'])
dict_orgs = {}
for gn in gntss:
# print(gn.upper(), end = '\t')
drts = dbi.get_dict("SELECT * FROM vtransports WHERE gosnum LIKE '%s" %
gn.upper() + "%'")
id_ts = drts.get('id_ts') if drts else None
if id_ts:
id_org = drts.get('id_org')
if id_org not in dict_orgs.keys():
dict_orgs[id_org] = []
dd = dbi.get_dict("SELECT * FROM atts WHERE autos = %s" % id_ts)
if dd:
# print("%s\tlast_date: %s\tdevice_id: %s\tuin: %s\tcode: %s" % (
# gn.upper(), dd.get('last_date') if dd.get('last_date') else "\t\t", dd.get('device_id'), dd.get('uin'), dd.get('code')))
dict_orgs[id_org].append(
"%s\tlast_date: %s\tdevice_id: %s\tuin: %s\tcode: %s" %
(gn.upper(),
dd.get('last_date') if dd.get('last_date') else "\t\t",
dd.get('device_id'), dd.get('uin'), dd.get('code')))
else:
# print(gn.upper(), "\tНет прибора.")
dict_orgs[id_org].append("%s \t%s" %
(gn.upper(), "\tНет прибора."))
else:
print(gn.upper(), "\tНет ТС")
for id_org in dict_orgs.keys():
dorg = dbi.get_dict("SELECT * FROM organizations WHERE id_org = %s" %
id_org)
print(id_org, dorg.get('inn'), dorg.get('bname'))
for s in dict_orgs[id_org]:
print(s)
def check_org():
""" Контроль наличия организаций и наличия у них ТС """
# Отсутствуют в бухгалтерии ( 1С )
query = """SELECT count(*), id_org FROM wtransports WHERE id_org IN (SELECT id_org FROM organizations WHERE inn IN (
5202007618, 5203001016, 5203001016, 5204001114, 5204012814, 5205000709, 5205004446, 5205005513, 5206001367, 5206001409, 5206001720, 5208005744,
5209004310, 5212005112, 5212007286, 5212511278, 5214006030, 5214006721, 5215000426, 5215009468, 5216001711, 5216017126, 5217003976, 5219000071,
5219000850, 5221006585, 5222013313, 5223002339, 5225001122, 5225004758, 5226000273, 5226000410, 5226011758, 5226014364, 5227005926, 5229001638,
5229009027, 5229009027, 5229009027, 5229009027, 5229009027, 5229009027, 5229009027, 5229009027, 5229009027, 5229009027, 5246045628, 5246046766,
5247015111, 5247016958, 5247048220, 5249055381, 5249057251, 5249061106, 5249066601, 5249066619, 5249076222, 5249084953, 5250039673, 5250043790,
5250046783, 5250056020, 5250057707, 5250061809, 5256060544, 5256064757, 5256083975, 5256087930, 5256132894, 5256133168, 5256135013, 5256140969,
5258029518, 5258121094, 5259115086, 5259120142, 5259131313, 5260041390, 5260076949, 5260139980, 5260148174, 5260357227, 5260381460, 5260403233,
5260410382, 5261015145, 5261113216, 5261120608, 5262035560, 5262123520, 5262288057, 5262311940, 5263049020, 5263089640, 5263111359, 5263133747,
521475244896, 521500776400, 521600283133, 522100130194, 522200043347, 522401215726, 524500120268, 524503379942, 524504481339, 524612643925,
524701328820, 524802195252, 525004789303, 525100054358, 525100866350, 525101406034, 525405675511, 525405760340, 526301541890
)) GROUP BY id_org ORDER BY id_org;"""
# Отсутствуют старой РНИС ( БД contracts )
query = """SELECT count(*), id_org FROM wtransports WHERE id_org IN (SELECT id_org FROM organizations WHERE inn IN (
5247007858, 5209004415, 5256122617, 5258082536, 5260255391, 5260279106, 5262277263
)) GROUP BY id_org ORDER BY id_org;"""
ddb = dbtools3.dbtools(
'host=10.10.2.241 dbname=contracts port=5432 user=smirnov')
print(query)
ddb.desc
rows = ddb.get_rows(query)
for c, id_org in rows:
dorg = ddb.get_dict("SELECT * FROM organizations WHERE id_org = %s" %
id_org)
print(c, id_org, dorg['inn'], dorg['bname'])
if c > 0:
tsrs = ddb.get_rows(
"SELECT id_ts, gosnum, last_date FROM wtransports WHERE id_org = %s"
% id_org)
for id_ts, gosnum, last_date in tsrs:
print("\t%s\t%s" % (gosnum, last_date))
sys.exit()
def get_grpc_state(inn=None):
import client_grpc as grpc
dbt1 = dbtools3.dbtools(DBout)
# ttt = ['А908КО152', 'О014РР52', 'О146АА52', 'О632МА52', 'Т415ХК52', 'T006', 'T009', 'T017', 'T019', 'T035', 'T051', 'T054', 'T055', 'T056', 'T057', 'T059', 'T060']
# query = "SELECT inn, t1name FROM t1_orgs"
print(
"Считаем ТС",
dbt1.execute(
"UPDATE t1_orgs SET countts = (SELECT count(inn) FROM t1_atts WHERE t1_atts.inn = t1_orgs.inn) WHERE t1_orgs.inn > 0"
))
query = "SELECT inn, t1name FROM t1_orgs WHERE countts > 0"
rows = dbt1.get_rows(query)
for inn, t1name in rows:
# print(t1name)
# continue
first_data = True
if inn:
query = "SELECT gosnum FROM t1_atts WHERE inn = %s" % inn
ttt = []
ars = dbt1.get_rows(query)
for r in ars:
ttt.append(r[0].lower())
activ_data = grpc.get_range(
ttt, dt=300) # получение телематических данных за период
if activ_data:
if first_data:
print('\n' + t1name)
first_data = False
for k in activ_data.keys():
d = activ_data[k]
print("\t%10s %s" %
(k,
time.strftime("%d-%m-%Y %T",
time.localtime(d.DeviceTime))),
d.DeviceTime,
end='\t')
print("%10.6f %10.6f" %
(d.Position.Longitude, d.Position.Latitude),
end='\t') # Course, Satellites, Speed
autod = dbt1.get_dict(
"SELECT * FROM t1_atts WHERE gosnum = '%s'" % k)
if autod:
if autod['last_tm'] and autod['last_tm'] < d.DeviceTime:
if d.Position.Longitude:
ss = ",x = %10.6f, y = %10.6f" % (
d.Position.Longitude, d.Position.Latitude)
else:
ss = ''
query = "UPDATE t1_atts SET last_date = '%s', last_tm = %s %s WHERE gosnum = '%s'" % (
time.strftime(
"%Y-%m-%d %T", time.localtime(
d.DeviceTime)), d.DeviceTime, ss, k)
print(query, dbt1.execute(query))
else:
print('Old')
else:
print("Z" * 11)
sys.exit()
def get_allts(turl=None):
""" Читать список ТС """
if turl in ['actv', 'updt']:
url = 'http://10.10.21.20:8000/v.1/messages/all/8d6fbe03e99f4b13966e622981c9a11f' # выводит актуальное число ТС
# dict_keys(['time', 'org_inn', 'vin', 'org_name', 'id', 'angle', 'speed', 'gosnumber', 'org_id', 'typets', 'idDev', 'lon', 'lat', 'date'])
else:
url = 'http://10.10.21.20:8000/v.1/vehicles_dic/8d6fbe03e99f4b13966e622981c9a11f' # выдает теперь полный список машин.
# dict_keys(['groupname', 'vin', 'id', 'name', 'idDev', 'gosnumber', 'org_name', 'org_inn', 'typets', 'createts', 'org_id', 'markats'])
print(url)
try:
response = requests.get(url)
response_json = response.json()
except:
print(response)
return
if not response_json:
return
if response_json and response_json[0].get('error'):
print("ERROR: %s %s" %
(response_json[0].get('error'), response_json[1].get('info')))
return
org_id2name = {}
j = 0
for r in response_json:
# print (r.keys()); break
org_id = r.get('org_id')
# print (r)
j += 1
# if j > 15: break
if org_id not in org_id2name.keys():
org_id2name[org_id] = {
'tname': r.get('org_name'),
'inn': r.get('org_inn'),
'ts': []
}
try:
org_id2name[org_id]['ts'].append({
'id_t1':
r.get('id'),
'gosnum':
r.get('gosnumber').upper(),
'id_dev':
r.get('idDev'),
'last_date':
r.get('date'),
'last_tm':
r.get('time')
})
except:
print(r)
print(turl, '\tLen response_json:', len(response_json), '\n')
ddb = dbtools3.dbtools(DBout)
print('#' * 33)
if turl == 'creat': create_000(ddb, org_id2name)
if turl == 'actv': create_all(ddb, org_id2name)
if turl == 'updt': update_avtv(ddb, org_id2name)
def check_gnum(gnum):
global DBID
query = "SELECT t.gosnum, a.last_date, o.inn, o.bname FROM transports t " \
"JOIN atts a ON t.id_ts = a.autos AND t.device_id = a.device_id " \
"JOIN organizations o ON t.id_org = o.id_org WHERE gosnum = '%s'" % gnum
if not DBID: DBID = dbtools3.dbtools('host=10.10.2.241 dbname=contracts port=5432 user=smirnov')
dr = DBID.get_dict(query)
if dr and dr.get('last_date'):
return [str(dr.get('last_date'))[:10], dr.get('inn'), dr.get('bname')]
# return [str(dr['last_date']), dr['inn']]
return []
def org_list(ssys):
""" Список организацие по подсистеме """
global IDB_CNTR, IDBLite
if not IDBLite: init_dbreport()
rrows = IDBLite.get_rows("SELECT inn FROM report_subs")
inns = []
for rr in rrows:
inns.append(rr[0])
if not IDB_CNTR: IDB_CNTR = dbt.dbtools(ids_db['cntr'])
# query = "SELECT id_org, inn, bname FROM organizations WHERE bm_ssys = %s AND inn IN (5223034394, 5243019838, 5206024886) ORDER BY bname" % ssys
query = "SELECT id_org, inn, bname FROM organizations WHERE bm_ssys = %s AND inn NOT IN (%s) ORDER BY bname" % (
ssys, str(inns)[1:-1] if inns else '123')
# print(query)
rows = IDB_CNTR.get_rows(query)
year = 2020
month = 5
ja = 0
for r in rows:
id_org, inn, bname = r
# print (id_org, inn, bname)
qurepp = None
car = IDB_CNTR.get_row(
"SELECT count(*) FROM wtransports WHERE id_org = %d" % id_org)
print(id_org,
inn,
bname,
car[0],
time.strftime("%D %T", time.localtime(time.time())),
sep='\t')
if car and car[0] > 0:
res = vms_get_ts(where="inn = '%s'" % inn,
nddata="nddata_202006",
fname='dd202006_%d' % inn)
if res:
H, S, A = res
print(inn, bname, car[0], H / 1000, S, A, sep='\t')
qurepp = "INSERT INTO report_subs (inn, bname, car, H, S, A, year, month) VALUES (%s, '%s', %s, %s, %s, %s, %s, %s)" % (
inn, bname, car[0], H, S, A, year, month)
else:
qurepp = "INSERT INTO report_subs (inn, bname, car, year, month) VALUES (%s, '%s', %s, %s, %s)" % (
inn, bname, car[0], year, month)
print(inn, bname, car[0], res, sep='\t')
else:
qurepp = "INSERT INTO report_subs (inn, bname, car) VALUES (%s, '%s', 0)" % (
inn, bname)
if qurepp:
print(qurepp, IDBLite.execute(qurepp))
ja += 1
if ja > 222: break
def test():
# print ('Test connections:', subprocess.check_output("cat /proc/sys/kernel/hostname", shell = True) == b'vadim\n')
# DBout = subprocess.run("cat", "/proc/sys/kernel/hostname", shell = True, stdout = subprocess.PIPE)
print(local_host, DBout, type(DBout))
dblist = [
'host=127.0.0.1 dbname=b03 port=5432 user=smirnov',
'host=10.40.25.176 dbname=vms_ws port=5432 user=vms',
DBout,
# 'host=212.193.103.20 dbname=worktime port=5432 user=smirnov',
]
for sdb in dblist:
print('\tConnect to', sdb, end='\t')
ddb = dbtools3.dbtools(sdb, 0)
print('Ok' if ddb and not ddb.last_error else ddb.last_error)
sys.exit()
def getActiveNumbers():
""" Читаем госномера машин которые ранее отправляли данные """
global DEVID_GNUMD
idb_contracts = dbt.dbtools(
'host=212.193.103.20 dbname=contracts port=5432 user=smirnov')
query = "SELECT id_dev, inn, gosnum FROM t1_atts WHERE inn IN (SELECT inn FROM t1_orgs WHERE bm_ssys = 131072) AND last_tm > 0;" ### 104 ts
# query = "SELECT id_dev, inn, gosnum FROM t1_atts WHERE inn IN (SELECT inn FROM t1_orgs WHERE bm_ssys = 131072) AND x > 0;" ### 80 ts
rows = idb_contracts.get_rows(query)
nums = []
inns = []
for id_dev, inn, gosnum in rows:
nums.append(gosnum.lower())
DEVID_GNUM[id_dev] = [gosnum, inn]
if inn not in inns: inns.append(inn)
### for k in DEVID_GNUM.keys(): print ("%22s" % k, DEVID_GNUM[k])
for i in inns:
print(i, end=', ')
print()
return nums
def vms_get_ts(**kwargs):
global IDB_VMS
nddata = kwargs.get('nddata') if kwargs.get('nddata') else "nddata_202004"
fname = kwargs.get('fname') if kwargs.get('fname') else 'nddata_202004'
where = "AND %s" % kwargs.get('where') if kwargs.get('where') else ''
order = "ORDER BY %s" % kwargs.get('order') if kwargs.get('order') else ''
query = qvms_ts % (where, order)
# print (query)
# return
IDB_VMS = dbt.dbtools(ids_db['vms'])
rows = IDB_VMS.get_rows(query)
d = IDB_VMS.desc
inn = 0
# Создать рабочую книгу в Excel:
wb = Workbook()
sheet = wb.active
cnames = [
'№ п.п',
'Гос.№',
'Марка ТС',
'Марка прибора',
'Т час',
'S км',
] # 'V пр.', 'V р.']
cwidth = [None, 14, 14, 14, None, None, None, None, None, None]
for j in range(len(cnames)):
sheet[colab(j) + '2'] = cnames[j]
if cwidth[j]: sheet.column_dimensions[colab(j)].width = cwidth[j]
for j in range(31):
sheet.column_dimensions[colab(6 + j)].width = 5
sheet[colab(6 + j) + '2'] = str(j + 1)
jr = 2
ja = 0
mndays = []
hourss = SS = 0
for r in rows:
stt = vms_calc_time(nddata, r[d.index('id')])
if inn != r[d.index('inn')]:
if TEST:
print("%s\t%s" %
(r[d.index('inn')], cntr_get_org(r[d.index('inn')])))
inn = r[d.index('inn')]
jr += 1
sheet[colab(0) + str(jr)] = inn
sheet[colab(1) + str(jr)] = cntr_get_org(inn)
ja = 0
if TEST:
print("\t%s\t%s\t%s" % (r[d.index('regnum')], r[d.index('tmarka')],
r[d.index('marka')]),
end='\t')
jr += 1
sheet[colab(1) + str(jr)] = r[d.index('regnum')]
sheet[colab(2) + str(jr)] = r[d.index('tmarka')]
sheet[colab(3) + str(jr)] = r[d.index('marka')]
ja += 1
sheet[colab(0) + str(jr)] = ja
if stt:
H = stt['dt'] // 3600
M = (stt['dt'] - H * 3600) // 60
s = stt['S'] / 1000 # ???
Vs = stt['S'] * 3.6 / stt['dt'] if stt['dt'] else 0.0
if stt['vp'] < 6. or Vs < 6.:
if TEST: print('$$$')
continue
hourss += stt['dt']
SS += stt['S']
# print(r[d.index('regnum')])
if TEST:
print("\t%4d:%02d\tS:%9.3f\tVs:%6.2f\tVp:%6.2f" %
(H, M, s, Vs, stt['vp']),
end='\t')
# print(stt['d'])
sheet[colab(4) + str(jr)] = stt['dt'] // 3600
# sheet[colab(5) + str(jr)] = "%4d.%02d" % (H, M)
sheet[colab(5) + str(jr)] = int(s) # float("%9.3f" % s)
days = stt.get('d')
if days:
mndays.append(days)
for jd in range(days[0]):
# print(days[0], len(days), jd+1, days[jd+1])
sheet[colab(6 + jd) + str(jr)] = days[jd + 1] // 1000
# sheet[colab(7) + str(jr)] = int(stt['vp']) # float("%6.2f" % stt['vp'])
# sheet[colab(8) + str(jr)] = int(Vs) # float("%6.2f" % Vs)
# sheet[colab(0) + str(jr)] = ja
else:
pass
if TEST: print()
try:
day = mndays[0][0]
adays = [0] * day
for day in range(mndays[0][0]):
for a in range(len(mndays)):
if mndays[a][day + 1] > 4000: adays[day] += 1
autos = 0
for j in range(mndays[0][0]):
sheet[colab(6 + j) + str(jr + 1)] = adays[j]
autos += adays[j]
# print(adays, autos/day)
wb.save(os.path.join(r'./tmp', fname + ".xlsx"))
return hourss, (SS / 1000), autos
except IndexError:
wb.save(os.path.join(r'./tmp', fname + ".xlsx"))
print("EXCEPT IndexError:", mndays)
def cntr_get_org(inn):
global IDB_CNTR
if not IDB_CNTR: IDB_CNTR = dbt.dbtools(ids_db['cntr'])
query = "SELECT bname FROM organizations WHERE inn = %s" % inn
row = IDB_CNTR.get_row(query)
return row[0] if row else "???"
def isconnect():
print("Connect to DataBase:")
for ddb in ids_db:
dbi = dbt.dbtools(ids_db[ddb])
print("\t%s:\t" % ddb, ids_db[ddb],
'Ok' if dbi and not dbi.last_error else 'Error')
def getQueue():
""" Читать очередь данных из Т1 и отправка БД receiver (АнтиСнег) """
global QDATAS
idb_receiver = dbt.dbtools(
'host=212.193.103.20 dbname=receiver port=5432 user=smirnov')
def get_recv_gnums():
query = "SELECT inn, gosnum, device_id, rem FROM recv_ts WHERE inn IN (SELECT inn FROM org_desc WHERE bm_ssys = 131072)"
rows = idb_receiver.get_rows(query)
recv_gnums = {}
for inn, gosnum, device_id, rem in rows:
recv_gnums[gosnum] = inn, device_id, rem
return recv_gnums
recv_gnums = get_recv_gnums()
recv_gn_list = list(recv_gnums.keys())
count_empt = 0
while not FL_BREAK:
try:
if QDATAS.empty():
time.sleep(1)
count_empt += 1
if count_empt // 900:
print("#" * 22, "\tcount_empt:", count_empt)
count_empt = 0
signal.alarm(2)
# sys.exit()
# os._exit()
continue
count_empt = 0
data = QDATAS.get()
dev_code, ggg, point = data
if ggg:
gnum, inn = ggg
else:
gnum, inn = None, None
t = point.DeviceTime
x = point.Position.Longitude
y = point.Position.Latitude
ht = point.Position.Altitude
cr = point.Position.Course
st = point.Position.Satellites
sp = point.Position.Speed
querys = []
is_new_ts = False
is_rlast = False
# print("%16s" % dev_code, t, inn, gnum, x, y, ht, cr, st, sp, end = " \t") #point.Position)
# print("%16s" % dev_code, t, inn, gnum, end = " \t")
print("%16s" % dev_code,
time.strftime(" %T %d.%m.%Y", time.localtime(t)),
inn,
gnum,
end=" \t")
if not ggg:
print("ggg:", ggg)
continue
if gnum and gnum in recv_gn_list:
rcv_inn, rcv_device_id, rcv_rem = recv_gnums[gnum]
if rcv_rem and 'T1' in rcv_rem:
print("#" * 6, rcv_inn, rcv_device_id, rcv_rem)
querys.append("DELETE FROM last_pos WHERE ida =%s" %
dev_code)
# querys.append("INSERT INTO last_pos (ida, idd, x, y, t, cr, sp, st) VALUES (%s, '%s', %s, %s, %s, %s, %s, %s)" % (dev_code, dev_code, x, y, t, cr, sp, st))
# querys.append("INSERT INTO data_pos (ida, idd, x, y, t, cr, sp, st) VALUES (%s, '%s', %s, %s, %s, %s, %s, %s)" % (dev_code, dev_code, x, y, t, cr, sp, st))
else:
rlast = idb_receiver.get_dict(
"SELECT * FROM vlast_pos WHERE gosnum = '%s'" % gnum)
code_ffff = int(dev_code)
if rlast:
is_rlast = True
dTime = (t - rlast['t'])
print("dTime: %s" % dTime)
if dTime < 3600: continue # < Часа
if code_ffff != rlast['ida']:
if dTime > 2592000: # > 30 дней 5184000: # > 60 дней
is_new_ts = True
querys.append(
"UPDATE recv_ts SET idd = 'idd%s', device_id = %s, rem = 'T1 %s' WHERE device_id = %s"
% (code_ffff, code_ffff, code_ffff,
rcv_device_id))
querys.append(
"DELETE FROM last_pos WHERE ida =%s" %
code_ffff)
else:
print("\t\tdev_code %s != " % code_ffff,
rlast['ida'])
else:
if code_ffff != rcv_device_id:
is_new_ts = True
print("UPDATE device_id")
querys.append(
"UPDATE recv_ts SET idd = 'idd%s', device_id = %s, rem = 'T1 %s' WHERE device_id = %s"
% (code_ffff, code_ffff, code_ffff,
rcv_device_id))
querys.append(
"DELETE FROM last_pos WHERE ida =%s" %
code_ffff)
else:
print("\tNOT in vlast_pos")
else:
# print("%16s" % dev_code, t, inn, gnum, x, y, ht, cr, st, sp) #point.Position)
print("\tNOT in recv_ts")
is_new_ts = True
querys.append(
"INSERT INTO recv_ts (idd, inn, gosnum, device_id, rem) VALUES ('idd%s', %s, '%s', %s, '%s')"
% (dev_code, inn, gnum, dev_code, 'T1 new'))
querys.append("DELETE FROM last_pos WHERE ida =%s" % dev_code)
if querys:
querys.append(
"INSERT INTO last_pos (ida, idd, x, y, t, cr, sp, st) VALUES (%s, '%s', %10.6f, %10.6f, %s, %s, %s, %s)"
% (dev_code, dev_code, x, y, t, cr, sp, st))
querys.append(
"INSERT INTO data_pos (ida, idd, x, y, t, cr, sp, st) VALUES (%s, '%s', %10.6f, %10.6f, %s, %s, %s, %s)"
% (dev_code, dev_code, x, y, t, cr, sp, st))
if idb_receiver.qexecute(';\n'.join(querys)):
if is_new_ts:
# recv_gnums[gosnum] = inn, dev_code, 'T1 add %s' % dev_code
recv_gnums = get_recv_gnums()
recv_gn_list = list(recv_gnums.keys())
# recv_gn_list.append(dev_code)
else:
print('is_new_ts', is_new_ts, querys[0])
except:
print("getQueue", sys.exc_info()[:2])
# print("querys", ";\n".join(querys))
print("#" * 33, "Finish getQueue")