def getUserAwards(callsign):
awards = cursor2dicts( \
dxdb.execute( """
select *
from user_awards
where callsign = %s """, \
( callsign, ) ), True )
if awards:
r = {}
for item in awards:
if not r.has_key(item['award']):
r[item['award']] = {}
if item['band'] != 'N/A':
if not r[item['award']].has_key(item['value']):
r[item['award']][item['value']] = {}
if not r[item['award']][item['value']].has_key(item['band']):
r[item['award']][item['value']][item['band']] = {}
r[item['award']][item['value']][item['band']][item['mode']] = \
{ 'confirmed': item['confirmed'], \
'workedCS': item['worked_cs'],\
'cfm_paper': item['cfm_paper'], 'cfm_eqsl': item['cfm_eqsl'],\
'cfm_lotw': item['cfm_lotw'],
'cfm': item['cfm']}
else:
r[item['award']][item['value']] = \
{ 'confirmed': item['confirmed'], 'workedCS': item['worked_cs'],\
'cfm_paper': item['cfm_paper'], 'cfm_eqsl': item['cfm_eqsl'],\
'cfm_lotw': item['cfm_lotw'],
'cfm': item['cfm']}
return r
else:
return None
def joinModes( mode, modeAliases, awardsList = None ):
sql = """
select distinct callsign, award, value, band
from user_awards where mode in """ + modeAliases
if awardsList:
sql += " and award in " + awardsList
awardItems = cursor2dicts( dxdb.execute( sql ) )
if awardItems:
if isinstance( awardItems, dict ):
awardItems = ( awardItems, )
for awardItem in awardItems:
award = [ x for x in awards if x['name'] == awardItem['award'] ][0]
cfmTypes = [ 'cfm_paper', 'cfm_eqsl', 'cfm_lotw' ]
if award.has_key( 'cfmTypes' ):
cfmTypes = []
for cfmType in award['cfmTypes']:
cfmTypes.append( cfmType[1] )
sql = """select * from user_awards
where callsign = %(callsign)s and award = %(award)s
and value = %(value)s and band = %(band)s and ( mode = '""" \
+ mode + "' or mode in" + modeAliases + ")"
modeItems = cursor2dicts( dxdb.execute( sql, awardItem ) )
if isinstance(modeItems, dict):
modeItems = ( modeItems, )
awardItem['cfm'] = {}
for cfmType in cfmTypes:
awardItem['cfm'][cfmType] = False
awardItem['worked_cs'] = ''
awardItem['mode'] = mode
for modeItem in modeItems:
for cfmType in cfmTypes:
if cfmType in modeItem['cfm']:
awardItem['cfm'][cfmType] |= modeItem['cfm'][cfmType]
if not ',' in awardItem['worked_cs'] and modeItem['worked_cs']:
if len( awardItem['worked_cs'] ) == 0:
awardItem['worked_cs'] = modeItem['worked_cs']
else:
awardItem['worked_cs'] += ', ' + modeItem['worked_cs']
dxdb.paramUpdateInsert( 'user_awards', \
spliceParams( awardItem, ( 'callsign', 'award', 'value', 'band', 'mode' ) ),
spliceParams( awardItem, ( 'cfm', 'worked_cs' ) ) )
sql = """delete from user_awards where mode in""" + modeAliases
dxdb.execute( sql )
def updateSpecialLists():
slData = loadSpecialLists()
slData['Special'] = cursor2dicts(
dxdb.execute("""
select callsign, last_ts
from callsigns
where special_cs and last_ts > now() - interval '2 days';"""))
slDataJSON = json.dumps(slData, default=jsonEncodeExtra)
for dir in dirs:
with open(dir + '/specialLists.json', 'w') as fsl:
fsl.write(slDataJSON)
def sendUserData(userData, start_response):
awardsSettings = cursor2dicts( \
dxdb.execute( """
select award, track, color, settings, stats_settings, adif
from users_awards_settings
where callsign = %(callsign)s """, \
userData ), True )
lists = cursor2dicts( \
dxdb.execute( """
select id, title, track, color, full_title
from users_lists
where callsign = %(callsign)s """, \
userData ), True )
if lists:
lists = lists.values()
for list in lists:
list['items'] = cursor2dicts( \
dxdb.execute( """
select * from users_lists_items
where list_id = %(id)s """, \
list ), True )
toSend = { 'token': jwt.encode( { 'callsign': userData['callsign'] }, \
secret, algorithm='HS256' ), 'callsign': userData['callsign'], \
'email': userData['email'], \
'lastAdifLine': userData['last_adif_line'], \
'awardValueWorkedColor': userData['award_value_worked_color'], \
'awardValueConfirmedColor': userData['award_value_confirmed_color'], \
'awards': getUserAwards( userData['callsign'] ),\
'listsAwards': getUserListsAwards( userData['callsign'] ),\
'lists': lists, 'admin': userData['callsign'] in admins, \
'dxpedition': userData['dxpedition'],\
'msc': userData['misc'] }
if awardsSettings:
toSend['awardsSettings'] = {}
for item in awardsSettings:
toSend['awardsSettings'][item['award']] = item
start_response('200 OK', [('Content-Type', 'application/json')])
return json.dumps(toSend)
def exportDXpedition(env):
dxp = cursor2dicts( \
dxdb.execute( """
select * from dxpedition
where dt_end > now() - interval '1 week'
order by callsign;"""), True )
if not dxp:
dxp = []
dxpJSON = json.dumps(dxp, default=jsonEncodeExtra)
# with open( dir + '/dxpedition.json', 'w' ) as f:
# f.write( dxpJSON )
sl = loadSpecialLists()
sl['DXpedition'] = dxp
slJSON = json.dumps(sl, default=jsonEncodeExtra)
writeWebFile(slJSON, 'specialLists.json', env)
def getUserListsAwards(callsign):
awards = cursor2dicts( \
dxdb.execute( """
select users_lists_awards.*
from users_lists_awards join users_lists on
users_lists_awards.list_id = users_lists.id
where users_lists.callsign = %s """, \
( callsign, ) ), True )
if awards:
r = {}
for item in awards:
if not r.has_key(item['list_id']):
r[item['list_id']] = {}
if not r[item['list_id']].has_key(item['callsign']):
r[item['list_id']][item['callsign']] = {}
if not r[item['list_id']][item['callsign']].has_key(item['band']):
r[item['list_id']][item['callsign']][item['band']] = {}
r[item['list_id']][item['callsign']][item['band']][item['mode']] = \
{ 'workedCS': item['worked_cs'],\
'cfm_paper': item['cfm_paper'], 'cfm_eqsl': item['cfm_eqsl'],\
'cfm_lotw': item['cfm_lotw'] }
return r
else:
return None
spliceParams( awardItem, ( 'cfm', 'worked_cs' ) ) )
sql = """delete from user_awards where mode in""" + modeAliases
dxdb.execute( sql )
for ( mode, modeAliases ) in modes.iteritems():
joinModes( mode, modeAliases )
joinModes( 'DATA', dataModesFull, "( 'DXCC', 'Russia' )" )
sql = """select callsign, award, settings, stats_settings
from users_awards_settings
where award = 'DXCC'
"""
if testMode:
sql += " and callsign = 'QQQQ'"
data = cursor2dicts( dxdb.execute( sql ) )
if testMode:
data = ( data, )
for row in data:
fl = False
if row['settings']:
if row['settings'].has_key( 'modes' ) and \
[x for x in row['settings']['modes'] if x['name'] == 'RTTY' ]:
row['settings']['modes'] = \
[ x for x in row['settings']['modes'] \
if x['name'] != 'RTTY' ]
fl = True
if row['stats_settings']:
if row['stats_settings'].has_key( 'modesFilter' ) \
and row['stats_settings']['modesFilter'].has_key( 'RTTY' ):
conf = siteConf()
webRoot = conf.get( 'web', 'root' )
udv = loadJSON( webRoot + '/userMetadata.json' )
if not udv:
udv = {}
dblSql = """
select callsign, title, sum( 1 )
from users_lists
where title in ( 'DX', 'DXpedition', 'Special' )
group by callsign, title
having sum( 1 ) > 1
"""
dbls = cursor2dicts( dxdb.execute( dblSql ) )
idSql = """
select id from users_lists
where callsign = %(callsign)s and title = %(title)s
"""
updateItemsSql = """
update users_lists_items as items0 set list_id = %(id0)s
where list_id = %(id1)s and not exists
(select callsign from users_lists_items as items1
where list_id = %(id0)s and items0.callsign = items1.callsign )
"""
deleteItemsSql = """
delete from users_lists_items where list_id = %(id1)s
"""
deleteListSql = """
delete from users_lists where id = %(id1)s
def makeStr( list ):
return ', '.join( ( "'{}'".format( x ) for x in list ) )
cfmTypesDef = [ 'cfm_lotw', 'cfm_paper', 'cfm_eqsl' ]
sql = """select * from user_awards
where award = %s """ + \
( "and callsign = 'QQQQ' " if testMode else '' )
users = []
for aw in awards:
print aw['name']
awardCfm = [ type[1] for type in aw['cfmTypes'] ] if aw.has_key( 'cfmTypes' ) \
else cfmTypesDef
data = cursor2dicts( dxdb.execute( sql, ( aw['name'], ) ), False )
if data:
for record in data:
recordCfm = { cfmType: record[cfmType] if record.has_key( cfmType ) \
else False for cfmType in awardCfm }
dxdb.paramUpdate( 'user_awards', \
{ 'award': aw['name'], 'value': record['value'], \
'band': record['band'], 'mode': record['mode'], \
'callsign': record['callsign'] },
{ 'cfm': json.dumps( recordCfm ) } )
if not record['callsign'] in users:
users.append( record['callsign'] )
dxdb.commit()
umdPath = webRoot + '/userMetadata.json'
umd = loadJSON( umdPath )
if not umd:
def __init__(self, dxData=None, newSpot=False, **params):
self.isBeacon = False
self._district = None
self.region = None
self.iota = None
self.offDB = False
self.pfx = None
self.awards = {}
self.dxData = dxData
self.country = None
self.special = False
try:
self.text = params['text'].decode('utf-8',
'ignore').encode("utf-8")
except Exception as ex:
logging.error("Error decoding qso text:")
logging.error(params['text'])
logging.exception(ex)
self.text = ''
self.freq = params['freq']
self.cs = params['cs']
if '/QRP' in self.cs:
self.cs = self.cs.replace('/QRP', '')
self.qrp = True
else:
self.qrp = False
self.de = params['de']
self.lotw = self.cs in lotwData
txt = self.text.lower()
if 'ncdxf' in txt or 'beacon' in txt or 'bcn' in txt or '/B' in self.cs:
self.isBeacon = True
return
self.band = params['band'] if params.has_key('band') else None
self.mode = None
self.subMode = None
if params.has_key('mode') and params['mode']:
self.setMode(params['mode'])
if not self.mode:
print params['mode']
self.mode = params['mode']
else:
self.mode = None
if params.has_key('subMode'):
self.subMode = params['subMode']
self.detectAwardsList = params['detectAwards'] \
if params.has_key( 'detectAwards' ) else None
if not self.band and self.freq:
self.band = findDiap(DX.bands, self.freq)
if not self.band:
return
if not self.mode and self.text:
t = self.text.upper()
for (mode, aliases) in DX.modes.iteritems():
for alias in aliases:
if re.search('(^|\s)' + alias + '(\d|\s|$)', t):
self.setMode(alias)
break
if not self.mode and self.freq:
modeByMap = findDiap(DX.modesMap, self.freq)
if modeByMap:
if modeByMap == 'BCN':
self.isBeacon = True
return
self.setMode(modeByMap)
self.qrzData = False
self.inDB = False
if params.has_key('ts'):
self.inDB = True
self.ts = params['ts']
self.time = params['time']
self._district = params['district'] if params.has_key( 'state' ) \
else None
self.region = params['region'] if params.has_key( 'region' ) \
else None
self.gridsquare = params['gridsquare'] if params.has_key( 'qth' ) \
else None
self.awards = params['awards'] if params.has_key('awards') else {}
self.iota = params['iota'] if params.has_key('iota') else None
else:
self.time = params['time'][:2] + ':' + params['time'][2:4]
self.ts = time.time()
self.region = None
self.district = None
self.gridsquare = None
dxCty = None
pfx = None
slashPos = self.cs.find('/')
if self.cs.endswith( '/AM' ) or self.cs.endswith( '/MM' ) \
or self.subMode == 'PSK125':
return
if slashPos != -1:
parts = self.cs.split('/')
for part in parts:
if part in ('M', 'P', 'QRP', 'QRO'):
continue
if prefixes[0].has_key(part):
pfx = part
else:
m = DX.reTempPfx.search(part)
if m and prefixes[0].has_key(m.group(1)):
pfx = m.group(1)
if pfx:
dxCty = prefixes[0][pfx]
break
if not pfx:
if prefixes[1].has_key(self.cs):
dxCty = prefixes[1][self.cs]
else:
for c in xrange(1, len(self.cs)):
if prefixes[0].has_key(self.cs[:c]):
pfx = self.cs[:c]
dxCty = prefixes[0][self.cs[:c]]
if dxCty and pfx:
self.country = countries[ dxCty ] if countries.has_key( dxCty ) \
else None
if pfx in DX.specialPfx:
self.special = True
elif self.country == 'Russia':
m = DX.reDigitsSpecial.search(self.cs)
if m:
self.special = True
else:
m = DX.reDigitsSpecial.search(pfx)
if m:
self.special = True
else:
m = DX.reDigitsSpecial.search(self.cs[len(pfx):])
if m:
self.special = True
else:
m = DX.reLettersSpecial.search(self.cs)
if m:
self.special = True
self.pfx = dxCty
if not self.inDB:
csLookup = dxdb.getObject( 'callsigns', { 'callsign': self.cs }, \
False, True )
if csLookup:
self.inDB = True
self.region = csLookup['region']
self._district = csLookup['district']
self.gridsquare = csLookup['qth']
self.qrzData = csLookup['qrz_data_loaded']
awLookup = cursor2dicts(
dxdb.execute(
"""
select award, value, mode
from awards
where callsign = %s""", (self.cs, )), True)
if awLookup:
for i in awLookup:
#if not i['mode']:
award = findAward(i['award'])
i['mode'] = self.getAwardMode(award)
self.awards[i['award']] = \
{ 'value': i['value'], 'mode': i['mode'] }
if '#' in self.de:
self.text = (self.text.split(' ', 1))[0]
self.testLookups()
self.detectAwards()
self.updateDB()
if newSpot:
dxdb.getObject( 'spots', \
{ 'callsign': self.cs, 'time': self.time, \
'de': self.de, 'text': self.text, \
'freq': self.freq, 'band': self.band, \
'mode': self.mode, 'submode': self.subMode, \
'qrp': self.qrp, 'pfx': self.pfx }, \
True )
if award.has_key('modes'):
if row['mode'] in award['modes']:
return row['mode']
if row['subMode'] in award['modes']:
return row['subMode']
if ( r'DATA' in award['modes'] ) and row['subMode'] and \
( r'PSK' in row['subMode'] or r'JT' in row['subMode'] ):
return r'DATA'
return row['subMode'] if row['subMode'] else row['mode']
dbData = cursor2dicts(
dxdb.execute("""
select text, mode, subMode, extract( epoch from ts) as ts, time, qrp,
extract (day from ts) as day,
extract (month from ts) as month,
freq, de, pfx, spots.callsign as cs, band, special_cs as special
from spots inner join callsigns on spots.callsign = callsigns.callsign
where ts > now() - interval '24 hours'
order by ts desc
"""), True)
data = []
for row in dbData:
row['subMode'] = row.pop('submode')
awlu = cursor2dicts(
dxdb.execute(
"""
select award, value
from awards
where callsign = %s""", (row['cs'], )), True)
row['awards'] = {}
if a['name'] == awardName ][0]
if award.has_key( 'modes' ):
if row['mode'] in award['modes']:
return row['mode']
if row['subMode'] in award['modes']:
return row['subMode']
if ( r'DATA' in award['modes'] ) and row['subMode'] and \
( r'PSK' in row['subMode'] or r'JT' in row['subMode'] ):
return r'DATA'
return row['subMode'] if row['subMode'] else row['mode']
dbData = cursor2dicts( dxdb.execute( """
select text, mode, subMode, extract( epoch from ts) as ts, time, qrp,
extract (day from ts) as day,
extract (month from ts) as month,
freq, de, pfx, spots.callsign as cs, band, special_cs as special
from spots inner join callsigns on spots.callsign = callsigns.callsign
where ts > now() - interval '24 hours'
order by ts desc
""" ), True )
data = []
for row in dbData:
row['subMode'] = row.pop( 'submode' )
awlu = cursor2dicts( dxdb.execute( """
select award, value
from awards
where callsign = %s""", ( row['cs'], ) ), True )
row['awards'] = {}
if awlu:
for a in awlu:
