def main(): logger = logging.getLogger('') logger.setLevel(logging.DEBUG) conf = site_conf() _db = DBConn(conf.items('db')) yield from _db.connect() yield from _db.execute('delete from old_rda;') yield from _db.execute('delete from rda;') rda_rus = requests.get('http://rdaward.org/rda_rus.txt').text params_old = [] params = [] lines = rda_rus.split('\r\n') re_rda_line = re.compile( r'(^[A-Z][A-Z]-\d\d)\s+[^\t]+\t*([A-Z][A-Z]-\d\d|\*\*\*)?') for line in lines: match_rda_line = re_rda_line.match(line) if match_rda_line: if match_rda_line.group(2): old = match_rda_line.group(1) new = None if match_rda_line.group( 2) == '***' else match_rda_line.group(2) params_old.append({'old': old, 'new': new}) else: params.append({'rda': match_rda_line.group(1)}) yield from _db.execute("""insert into old_rda values (%(old)s, %(new)s)""",\ params_old, progress=True) yield from _db.execute("""insert into rda values (%(rda)s)""",\ params, progress=True)
def main(): logger = logging.getLogger('') handler = logging.handlers.WatchedFileHandler('/var/log/cfmrda.qrz.log') handler.setFormatter(logging.Formatter(\ '%(asctime)s %(name)-12s %(levelname)-8s %(message)s')) logger.setLevel(logging.DEBUG) logger.addHandler(handler) handler.setLevel(logging.DEBUG) conf = site_conf() _db = DBConn(conf.items('db')) yield from _db.connect() qrzru = QRZRuLink(asyncio.get_event_loop()) pfx = Pfx('/usr/local/webcluster/cty.dat') @asyncio.coroutine def db_write(data): yield from _db.execute( """ insert into callsigns_rda (callsign, source, rda) values (%(callsign)s, 'QRZ.ru', %(rda)s)""", data) callsigns = yield from _db.execute(\ """select distinct hunter from rda_hunter""") logging.debug('callsigns list received -- ' + str(len(callsigns))) params = [] cnt = 0 ru_cnt = 0 fnd_cnt = 0 for _cs in callsigns: cnt += 1 cs_pfx = pfx.get(_cs) if cs_pfx in ['R', 'R2F', 'R9']: m_special = RE_SPECIAL.search(_cs) if m_special: continue ru_cnt += 1 data = yield from qrzru.query(_cs) if data and 'state' in data and data['state']: fnd_cnt += 1 params.append({'callsign': _cs, 'rda': data['state']}) logging.debug(_cs + ' found') if len(params) >= 100: yield from db_write(params) params = [] logging.debug('Processed ' + str(cnt) + '/' + str(ru_cnt) + '/'\ + str(fnd_cnt) + ' of ' + str(len(callsigns))) cnt += 1 logging.debug('qrz query complete')
def export_rankings(conf): """rebuild rankings table in db and export top100 to json file for web""" logging.debug('export rankings') _db = DBConn(conf.items('db')) yield from _db.connect() yield from _db.execute("select from build_rankings()") logging.debug('rankings table rebuilt') rankings = yield from _db.execute( """ select rankings_json('_rank < 104') as data """, None, False) save_json(rankings, conf.get('web', 'root') + '/json/rankings.json') logging.debug('export rankings finished')
def main(): logger = logging.getLogger('') logger.setLevel(logging.DEBUG) conf = site_conf() _db = DBConn(conf.items('db')) yield from _db.connect() with open('/var/www/adxc.test/csv/rda_old_new.csv', 'r') as f_data: params = [] for line in f_data.readlines(): fields = {} fields['old'], fields['new'] = line.strip().split(';') if fields['old'] and fields['new']: params.append(fields) yield from _db.execute("""insert into old_rda values (%(old)s, %(new)s)""",\ params, progress=True)
def export_msc(conf): """export misc db data to json file for web""" logging.debug('export misc') _db = DBConn(conf.items('db')) yield from _db.connect() data = {} data['qsoCount'] = (yield from _db.execute( """ select count(*) as qso_count from qso; """, None, False)) data['userActivity'] = (yield from _db.execute( """ select json_agg(data) from (select json_build_object('callsign', coalesce(qsl_wait.callsign, qsl_today.callsign, email.callsign), 'qslWait', qsl_wait, 'qslToday', qsl_today, 'email', email) as data from (select user_cs as callsign, count(*) as qsl_wait from cfm_qsl_qso where state is null group by user_cs) as qsl_wait full join (select user_cs as callsign, count(*) as qsl_today from cfm_qsl_qso where state group by user_cs) as qsl_today on qsl_wait.callsign = qsl_today.callsign full join (select user_cs as callsign, count(*) as email from cfm_request_qso where not sent and user_cs is not null group by user_cs) as email on coalesce(qsl_wait.callsign, qsl_today.callsign) = email.callsign order by coalesce(qsl_wait.callsign, qsl_today.callsign, email.callsign) ) as data""", None, False)) save_json(data, conf.get('web', 'root') + '/json/msc.json') logging.debug('export misc finished')
def export_recent_uploads(conf): """export 20 recent uploaded file batches to json file for web""" logging.debug('export recent uploads') _db = DBConn(conf.items('db')) yield from _db.connect() data = yield from _db.execute( """ select json_agg(json_build_object( 'activators', activators, 'rda', rda, 'uploadDate', to_char(max_tstamp, 'DD mon YYYY'), 'uploadTime', to_char(max_tstamp, 'HH24:MI'), 'uploadType', upload_type, 'uploader', uploader)) as data from (select user_cs as uploader, upload_type, max(tstamp) as max_tstamp, array_agg(id) as ids from uploads where ext_logger_id is null group by date(tstamp), user_cs, upload_type order by max_tstamp desc limit 20) as ru, lateral (select array_agg(distinct station_callsign) as activators from qso where upload_id = any(ids)) as acts, lateral (select array_agg(rda) as rda from (select json_build_object('rda', array_agg(distinct rda), 'id', upload_id) as rda from qso where upload_id = any(ids) group by upload_id) as rdas0) as rdas """, None, False) save_json(data, conf.get('web', 'root') + '/json/recent_uploads.json') logging.debug('export recent uploads finished')
def export_stat(conf): """export statistic data to json file for web""" logging.debug('export stats') _db = DBConn(conf.items('db')) yield from _db.connect() data = {} data['qso by rda'] = (yield from _db.execute( """ select json_object_agg(rda, data) as data from (select rda, json_object_agg(band, data) as data from (select rda, band, json_object_agg(mode, qso_count) as data from (select count(*) as qso_count, rda, band, mode from qso where upload_id is null or (select enabled from uploads where id=upload_id) group by mode, band, rda ) as q0 group by rda, band) as q1 group by rda) as q2 """, None, False)) for rda_data in data['qso by rda'].values(): rda_total = {'total': 0} for band_data in rda_data.values(): band_total = 0 for mode, qso_count in band_data.items(): band_total += qso_count if mode not in rda_total: rda_total[mode] = 0 rda_total[mode] += qso_count band_data['total'] = band_total rda_total['total'] += band_total rda_data['total'] = rda_total save_json(data, conf.get('web', 'root') + '/json/stat.json') logging.debug('export stats finished')
#!/usr/bin/python3 #coding=utf-8 import json from db import DBConn from conf import CONF DB = DBConn(CONF.items('db')) DB.connect() feeders_data = DB.execute(""" select id, props from devices where device_type_id = 3""") for feeder_data in feeders_data: for timer in feeder_data['props'][2]: timer.append(0) feeder_data['props'] = json.dumps(feeder_data['props']) DB.execute( """ update devices set props = %(props)s where id = %(id)s""", feeder_data)
def main(): """sends cfm requests""" start_logging('send_cfm_requests') logging.debug('start send cfm requests') conf = site_conf() secret = get_secret(conf.get('files', 'secret')) db_params = conf.items('db') _db = DBConn(db_params) yield from _db.connect() data = yield from _db.execute( """ select correspondent, correspondent_email, json_agg(json_build_object('callsign', callsign, 'stationCallsign', station_callsign, 'rda', rda, 'band', band, 'mode', mode, 'tstamp', to_char(tstamp, 'DD mon YYYY HH24:MI'), 'rcvRST', rec_rst, 'sntRST', sent_rst)) as qso from (select * from cfm_request_qso where not sent and correspondent not in (select callsign from cfm_request_blacklist)) as data group by correspondent, correspondent_email""", None, True) if not data: return sent_to = [] for row in data: token = create_token(secret, {'callsign': row['correspondent']}) link_cfm = conf.get('web', 'address') + '/#/cfm_qso/?token=' + token + \ '&callsign=' + row['correspondent'] link_blacklist = conf.get('web', 'address') +\ '/#/cfm_blacklist/?token=' + token qso_txt = format_qsos(row['qso']) text = (""" Здравствуйте, {correspondent}. Просим Вас поддержать проект CFMRDA для создания единой базы по программе диплома RDA. Вы можете подтвердить конкретные связи, которые очень важны Вашим корреспондентам, приславшим запросы или залить полностью свой лог. """ + qso_txt + """ Для подтверждения QSO зайдите на эту страницу - {link_cfm} Если указанные данные верны, поставьте отметки "Подтвердить" в каждом QSO и нажмите кнопку "OK" Было бы удобнее, если бы вы зарегистрировались на CFMRDA.ru и загрузили бы свои логи в базу данных сайта. Если Вы не хотите регистрироваться или у Вас возникли какие-то трудности при загрузке, пришлите свой лог, желательно в формате ADIF на адрес техподдержки [email protected] Спасибо. 73! Команда CFMRDA.ru Если вы не хотите в дальнейшем получать подобные запросы на подтверждение QSO, пройдите по этой ссылке - {link_blacklist} И нажмите кнопку "Не присылать мне больше запросов от CFMRDA.ru" """).format_map({'correspondent': row['correspondent'],\ 'link_cfm': link_cfm, 'link_blacklist': link_blacklist}) retries = 0 while retries < 3: if send_email(text=text,\ fr=conf.get('email', 'address'),\ to=row['correspondent_email'],\ subject="Запрос на подтверждение QSO от CFMRDA.ru"): logging.error('cfm request email sent to ' + row['correspondent']) sent_to.append(row) break else: retries += 1 yield from asyncio.sleep(10) if retries == 3: logging.error('Email delivery failed. Correspondent: ' + row['correspondent']\ + ', address: ' + row['correspondent_email']) yield from asyncio.sleep(10) logging.error('all requests were sent') if sent_to: yield from _db.execute(""" update cfm_request_qso set sent = true, status_tstamp = now() where correspondent = %(correspondent)s and not sent""",\ sent_to) logging.error('cfm_request_qso table updated') yield from _db.execute( """ update cfm_requests set tstamp = now() where callsign = %(correspondent)s; insert into cfm_requests select %(correspondent)s, now() where not exists (select 1 from cfm_requests where callsign = %(correspondent)s) """, sent_to) logging.error('cfm_requests table updated')
def main(): logger = logging.getLogger('') logger.setLevel(logging.DEBUG) conf = site_conf() _db = DBConn(conf.items('db')) yield from _db.connect() re_split = re.compile(r"\t+") re_split_date = re.compile(r"\D+") re_date = [re.compile(x) for x in [r'(\d\d\d\d)$', r'(\d?\d)\W(\d?\d?\d\d)$',\ r'(\d?\d)\D(\d?\d)\D(\d?\d?\d\d)$']] re_date_l = re.compile(r'(\d?\d)\D(\d?\d)\D(\d?\d?\d\d)') re_date_bw = re.compile(r'(\d\d\d\d)\D(\d?\d)\D(\d?\d)') def parse_date(str_val, strict=True): str_val = str_val.strip() parsed = [] if strict: for re_x in re_date: m_date = re_x.match(str_val) if m_date: grp = 1 while grp <= re_x.groups: parsed.append(m_date.group(grp)) grp += 1 else: m_date = re_date_l.search(str_val) if m_date: grp = 1 while grp < 4: parsed.append(m_date.group(grp)) grp += 1 else: m_date = re_date_bw.search(str_val) if m_date: parsed = [ m_date.group(3), m_date.group(2), m_date.group(1) ] if parsed: if len(parsed[-1]) < 4: if len(parsed[-1]) == 3: parsed = None else: if int(parsed[-1]) < 30: parsed[-1] = '20' + parsed[-1] else: parsed[-1] = '19' + parsed[-1] return parsed if parsed else None def compose_date(parsed_dt, end=False): pdt = [] for xdt in parsed_dt: pdt.append(xdt) if len(pdt) < 2: pdt.insert(0, '12' if end else '01') if len(pdt) < 3: pdt.insert(0,\ str(calendar.monthrange(int(pdt[1]), int(pdt[0]))[1]) if end\ else '01') return pdt[1] + '-' + pdt[0] + '-' + pdt[2] with open('/var/www/cfmrda-dev/DL6KVA.txt', 'r', encoding='cp437') as f_data: params = [] for line in f_data.readlines(): fields = [x.strip() for x in line.split('\t')] if fields[3] == 'DELETED': del fields[2] parsed_dt_start, parsed_dt_stop = None, None date = parse_date(fields[2]) if date: parsed_dt_start = date parsed_dt_stop = date else: if '-' in fields[2]: str_dates = fields[2].split('-') parsed_dt_stop = parse_date(str_dates[1]) if parsed_dt_stop: parsed_dt_start = re_split_date.split(str_dates[0]) if not parsed_dt_start[-1]: del parsed_dt_start[-1] while len(parsed_dt_start) < len(parsed_dt_stop): parsed_dt_start.append( parsed_dt_stop[len(parsed_dt_start)]) elif 'from' in fields[2] or 'SINCE' in fields[ 2] or 'FROM' in fields[2]: str_dt_start = fields[2].replace('from ', '').replace('SINCE ',\ '').replace('FROM ', '') parsed_dt_start = parse_date(str_dt_start) elif 'till' in fields[2]: str_dt_stop = fields[2].replace('till ', '') parsed_dt_stop = parse_date(str_dt_stop) if not (parsed_dt_start or parsed_dt_stop): date = parse_date(fields[2], False) if date: parsed_dt_start = date parsed_dt_stop = date try: dt_start = compose_date( parsed_dt_start) if parsed_dt_start else None dt_stop = compose_date(parsed_dt_stop, True) if parsed_dt_stop else None except Exception: logging.exception(fields[2]) if len(fields[1]) != 5: print(fields[1]) continue params.append({'callsign': fields[0], 'rda': fields[1], 'dt_start': dt_start,\ 'dt_stop': dt_stop,\ 'source': fields[4] if fields[4] else 'RDAWARD.org',\ 'ts': fields[5] if fields[5] else '2019-06-17'}) yield from _db.execute("""insert into callsigns_rda (callsign, rda, dt_start, dt_stop, source, ts) values (%(callsign)s, %(rda)s, %(dt_start)s, %(dt_stop)s, %(source)s, %(ts)s)""",\ params, progress=True)
def main(conf): """does the job""" db_params = conf.items('db') _db = DBConn(db_params) yield from _db.connect() loggers = yield from _db.execute( """ select id, callsign, logger, login_data, qso_count, to_char(last_updated, 'YYYY-MM-DD') as last_updated from ext_loggers where state = 0 and (last_updated is null or last_updated < now() - interval '14 days') """, None, True) if not loggers: logging.debug('No updates are due today.') return for row in loggers.values(): logger = ExtLogger(row['logger']) update_params = {} adifs = None try: adifs = logger.load(row['login_data']) logging.debug(row['callsign'] + ' ' + row['logger'] + ' data was downloaded.') except Exception: logging.exception(row['callsign'] + ' ' + row['logger'] + ' error occured') update_params['state'] = 1 if adifs: prev_uploads = yield from _db.execute( """ select id from uploads where ext_logger_id = %(id)s""", row, True) if prev_uploads: for upload_id in prev_uploads: yield from _db.remove_upload(upload_id) qso_count = 0 for adif in adifs: adif = adif.upper() qso_count += adif.count('<EOR>') parsed = load_adif(adif, 'STATION_CALLSIGN', ignore_activator=True,\ strip_callsign_flag=False) date_start, date_end = None, None sql_rda = """ select distinct rda from callsigns_rda where callsign = %(callsign)s and rda <> '***' and (dt_start is null or dt_start <= %(tstamp)s) and (dt_stop is null or dt_stop >= %(tstamp)s) """ qsos = [] with (yield from _db.pool.cursor()) as cur: for qso in parsed['qso']: yield from exec_cur(cur, sql_rda, qso) if cur.rowcount == 1: qso['rda'] = (yield from cur.fetchone())[0] qso['callsign'], qso['station_callsign'] = \ qso['station_callsign'], qso['callsign'] if not date_start or date_start > qso['tstamp']: date_start = qso['tstamp'] if not date_end or date_end < qso['tstamp']: date_end = qso['tstamp'] qsos.append(qso) if qsos: logging.debug(str(len(qsos)) + ' rda qso found.') file_hash = yield from _db.check_upload_hash( adif.encode('utf-8')) db_res = yield from _db.create_upload(\ callsign=row['callsign'],\ upload_type=row['logger'],\ date_start=date_start,\ date_end=date_end,\ file_hash=file_hash,\ activators=set([]), ext_logger_id=row['id'], qsos=qsos) logging.debug( str(db_res['qso']['ok']) + ' qso were stored in db.') update_params = {\ 'qso_count': qso_count,\ 'state': 0,\ 'last_updated': datetime.now().strftime("%Y-%m-%d")} yield from _db.param_update('ext_loggers', splice_params(row, ('id',)),\ update_params) logging.debug('logger data updated')