def fill_my_locator(callsign):
if (callsign not in callsigns.callsign_list()):
print("Not setup to handle callsign: {}".format(callsign))
return 1
try:
db = MySQLdb.connect(host=passwords.SQL_SERVER,
user=passwords.SQL_USERNAME,
passwd=passwords.SQL_PASSWORD,
port=passwords.SQL_PORT,
db=callsign.replace('/', '_'))
except:
print("Failed to connect to SQL database for {} on {}:{}".format(
callsign, passwords.SQL_SERVER, passwords.SQL_PORT))
return 2
c = db.cursor()
#Default locator found so update log
c.execute("UPDATE log SET MyGridSquare = %s WHERE MyGridSquare = ''",
(callsigns.locations[callsign].defaultGridsquare, ))
if (c.rowcount > 0):
print("Blank locators for {} replaced with {}".format(
callsign, callsigns.locations[callsign].defaultGridsquare))
c.execute(
"UPDATE log SET ClublogQsoUploadStatus = 'N' WHERE ClublogQsoUploadStatus = ''"
)
db.commit()
db.close()
return 0
def handle():
callsignString = ""
for callsign in callsigns.callsign_list():
callsignString += callsign.replace('/', '_') + ' '
callsignString = callsignString.rstrip() #Remove trailing space
print("Backing up databases for: " + callsignString)
cmd = "mysqldump -u {} -p{} -h {} -P {} --databases {}".format(passwords.SQL_USERNAME, passwords.SQL_PASSWORD, passwords.SQL_SERVER, passwords.SQL_PORT, callsignString)
args = cmd.split(' ')
saveFileName = "{}.gz".format(time.strftime('%d-%m-%Y', time.gmtime()))
saveFile = open(saveFileName, 'w')
dumpProcess = subprocess.Popen(args, stdout= subprocess.PIPE)
saveProcess = subprocess.Popen(["gzip"], stdin=dumpProcess.stdout, stdout=saveFile)
saveProcess.wait()
saveFile.close()
uploadProcess = subprocess.Popen(["/home/pi/Dropbox-Uploader/dropbox_uploader.sh", "upload", saveFileName, saveFileName])
uploadProcess.wait()
deleteTempFile = subprocess.Popen(["rm", saveFileName])
deleteTempFile.wait()
print("Done")
def guess_blank_dxcc(callsign):
if (callsign not in callsigns.callsign_list()):
print("Not setup to handle callsign: {}".format(callsign))
return 1
try:
db = MySQLdb.connect(host=passwords.SQL_SERVER,
user=passwords.SQL_USERNAME,
passwd=passwords.SQL_PASSWORD,
port=passwords.SQL_PORT,
db=callsign.replace('/', '_'))
except:
print("Failed to connect to SQL database for {} on {}:{}".format(
callsign, passwords.SQL_SERVER, passwords.SQL_PORT))
return 2
c = db.cursor()
c.execute("SELECT `Call` FROM log WHERE Dxcc = ''")
callsignsToUpdate = c.fetchall()
if (callsignsToUpdate == ()):
db.close()
return 0
else:
errorCode = 0
for call in callsignsToUpdate: #I know the helpage says don't iterate but there shouldn't be many
url = 'https://clublog.org/dxcc'
payload = {
'call': call[0],
'api': passwords.CLUBLOG_API_KEY,
'full': '1'
}
result = requests.get(url, payload)
if (result.status_code != 200):
print("Error downloading data from Clublog for callsign {}".
format(call))
return 5
info = json.loads(result.text)
if (info['DXCC'] == 0):
print("No DXCC found for {}".format(call[0]))
errorCode = 18
else:
print("Updating {} to Country: {}".format(
call[0], info['Name']))
c.execute(
"UPDATE log SET Dxcc = %s, Country = %s WHERE `Call` = %s",
(info['DXCC'], info['Name'].title(), call))
db.commit()
db.close()
return errorCode
def handle_everything():
try:
file = open('synctime.txt', 'r')
lastSyncTime = file.read()
file.close()
except:
lastSyncTime = "1945-01-01 00:00:00"
for callsign in callsigns.callsign_list():
#Fill in any blank dxcc fields
errorCode = guess_blank_dxcc(callsign)
if (errorCode != 0): return (errorCode, callsign)
#Fill in any QSO where my locator hasn't been specified with the default from 'callsigns.py'
errorCode = fill_my_locator(callsign)
if (errorCode != 0): return (errorCode, callsign)
#Upload any new QSOs to LoTW
errorCode = lotw_upload(callsign)
if (errorCode != 0): return (errorCode, callsign)
#Download any new QSLs from LoTW
errorCode = lotw_download(callsign, lastSyncTime)
if (errorCode != 0): return (errorCode, callsign)
#Upload any new QSOs to Clublog
errorCode = clublog_upload(callsign)
if (errorCode != 0): return (errorCode, callsign)
#Save sync time to file
syncTime = time.strftime('%Y-%m-%d %H:%M', time.gmtime())
returnTime = time.strftime('%d-%m-%Y %H:%M', time.gmtime())
print("Synchronised at " + syncTime)
file = open("synctime.txt", "w")
file.write(syncTime)
file.close()
return (0, returnTime)
def lotw_upload(callsign):
#Check we are setup to handle this callsign
if (callsign not in callsigns.callsign_list()):
print("Not setup to handle callsign: {}".format(callsign))
return 1
#Attempt to connect to MySQL database for that callsign
try:
db = MySQLdb.connect(host=passwords.SQL_SERVER,
user=passwords.SQL_USERNAME,
passwd=passwords.SQL_PASSWORD,
port=passwords.SQL_PORT,
db=callsign.replace('/', '_'))
except:
print("Failed to connect to SQL database for {} on {}:{}".format(
callsign, passwords.SQL_SERVER, passwords.SQL_PORT))
return 2
c = db.cursor()
#Deal with LotW first, each locator used must be uploaded seperately
c.execute("SELECT DISTINCT MyGridSquare FROM log WHERE LotwQslSent = 'N'")
locators = c.fetchall(
) # Locators now contains all the locators with un-uploaded QSOs
if (locators == ()):
print("Nothing to upload to LoTW for {}".format(callsign))
return 0
for locator in locators: #For each locator
print("Uploading log from {} in locator {}".format(
callsign, locator[0]))
#Get all of the QSOs that haven't been uploaded yet
c.execute(
"SELECT QsoDate, TimeOn, Freq, Band, Mode, `Call`, RstSent, RstRcvd FROM log WHERE LotwQslSent = 'N' AND MyGridSquare = %s ORDER BY QsoDate, TimeOn",
(locator[0], ))
qsos = c.fetchall()
logfile = open("/tmp/log.adi", "w")
logfile.write("<EOH>\r\n")
for qso in qsos:
logfile.write(
"<QSO_DATE:8>{} <TIME_ON:6>{} <BAND:{}>{} <MODE:{}>{} <CALL:{}>{} <RST_SENT:{}>{} <RST_RCVD:{}>{} <EOR>\r\n"
.format(qso[0], qso[1], len(qso[3]), qso[3], len(qso[4]),
qso[4], len(qso[5]), qso[5], len(qso[6]), qso[6],
len(qso[7]), qso[7]))
logfile.close()
#Get my CQ Zone, DXCC number and ITU Zone from locations file
#and write station information to LoTW config file
locatorFile = open("/home/pi/.tqsl/station_data", "w")
locatorFile.write("<StationDataFile>\n")
locatorFile.write(" <StationData name=\"test\">\n")
locatorFile.write(" <CALL>{}</CALL>\n".format(callsign))
locatorFile.write(" <CQZ>{}</CQZ>\n".format(
callsigns.locations[callsign].cqz))
locatorFile.write(" <DXCC>{}</DXCC>\n".format(
callsigns.locations[callsign].dxcc))
locatorFile.write(" <GRIDSQUARE>{}</GRIDSQUARE>\n".format(
locator[0]))
locatorFile.write(" <ITUZ>{}</ITUZ>\n".format(
callsigns.locations[callsign].itu))
locatorFile.write(" </StationData>\n")
locatorFile.write("</StationDataFile>\n")
locatorFile.close()
#Upload the ADIF to LoTW
print("\n\n")
lotwResult = subprocess.call([
"tqsl", "-a", "abort", "-d", "-l", "test", "-u", "-x",
"/tmp/log.adi"
])
print("\n\n")
#Check error code
if (lotwResult == 0):
c.execute(
"UPDATE log SET LotwQslSent = 'Y' WHERE MyGridSquare = %s AND LotwQslSent = 'N'",
(locator[0], )) #All fine, update log
db.commit()
elif (lotwResult == 1):
print("Cancelled by user")
elif (lotwResult == 2):
print("Rejected by LoTW")
elif (lotwResult == 3):
print("Unexpected Response from TQSL Server")
elif (lotwResult == 4):
print("TQSL Error")
elif (lotwResult == 5):
print("TQSLlib Error")
elif (lotwResult == 6):
print("Unable to open input file")
elif (lotwResult == 7):
print("Unable to open output file")
elif (lotwResult == 8):
print("All QSOs were duplicates or out of range")
elif (lotwResult == 9):
print("Some QSOs were duplicates or out of range")
elif (lotwResult == 10):
print("Command Syntax Error")
elif (lotwResult == 11):
print("LoTW Connection Error")
else:
print("Unknown TQSL Error, most likely Framebuffer not set")
lotwResult = 12
#If not success, cancel everything and return
if (lotwResult != 0):
return (
lotwResult + 5
) #(+2) as smaller value error codes are taken are reserved for success, callsign not found and SQL error respectively in this program
db.close()
return 0
def clublog_upload(callsign):
#Check we are setup to handle this callsign
if (callsign not in callsigns.callsign_list()):
print("Not setup to handle callsign: {}".format(callsign))
return 1
#Attempt to connect to MySQL database for that callsign
try:
db = MySQLdb.connect(host=passwords.SQL_SERVER,
user=passwords.SQL_USERNAME,
passwd=passwords.SQL_PASSWORD,
port=passwords.SQL_PORT,
db=callsign.replace('/', '_'))
except:
print("Failed to connect to SQL database for {} on {}:{}".format(
callsign, passwords.SQL_SERVER, passwords.SQL_PORT))
return 2
c = db.cursor()
c.execute(
"SELECT QsoDate, TimeOn, Freq, Band, Mode, `Call`, RstSent, RstRcvd, LotwQslRcvd, QslSent FROM log WHERE ClublogQsoUploadStatus = 'N' ORDER BY QsoDate, TimeOn"
)
qsos = c.fetchall()
if (qsos == ()):
print("Nothing to upload to Clublog for {}".format(callsign))
return 0
logfile = open("/tmp/log.adi", "w")
logfile.write("<EOH>\r\n")
for qso in qsos:
logfile.write(
"<QSO_DATE:8>{} <TIME_ON:6>{} <BAND:{}>{} <MODE:{}>{} <CALL:{}>{} <RST_SENT:{}>{} <RST_RCVD:{}>{} <LOTW_QSL_RCVD:1>{} <QSL_SENT:1>{} <EOR>\r\n"
.format(qso[0], qso[1], len(qso[3]), qso[3], len(qso[4]), qso[4],
len(qso[5]), qso[5], len(qso[6]), qso[6], len(qso[7]),
qso[7], qso[8], qso[9]))
logfile.close()
#Now have ADIF file ready for upload to Clublog
url = "https://clublog.org/putlogs.php"
files = {'file': open("/tmp/log.adi", "r")}
values = {
'email': passwords.CLUBLOG_EMAIL,
'password': passwords.CLUBLOG_APPLICATION_PASSWORD,
'callsign': callsign,
'api': passwords.CLUBLOG_API_KEY
}
request = requests.post(url, files=files, data=values)
errorCode = request.status_code
if (errorCode == 200):
print("Uploaded QSOs for {} to Clublog".format(callsign))
c.execute(
"UPDATE log SET ClublogQsoUploadStatus = 'Y' WHERE ClublogQsoUploadStatus = 'N'"
)
db.commit()
print("{} QSOs for {} successfully uploaded to Clublog".format(
c.rowcount, callsign))
return 0
else:
print("Clublog Upload for {} failed. Error {}: ".format(
callsign, errorCode, request.text))
return 5
def oqrs_download(callsign, lastSyncTime):
#Check we are setup to handle this callsign
if (callsign not in callsigns.callsign_list()):
print("Not setup to handle callsign: {}".format(callsign))
return 1
#Open MySQL database
try:
db = MySQLdb.connect(
host=passwords.SQL_SERVER,
user=passwords.SQL_USERNAME,
passwd=passwords.SQL_PASSWORD,
port=passwords.SQL_PORT,
db=callsign.replace('/', '_'),
client_flag=2
) #so rowcount returns matched rows rather than updated rows
except:
print("Failed to connect to SQL database for {} on {}:{}".format(
callsign, passwords.SQL_SERVER, passwords.SQL_PORT))
return 2
c = db.cursor()
url = "https://clublog.org/getadif.php"
year = lastSyncTime[:4]
month = lastSyncTime[5:7]
day = lastSyncTime[8:10]
values = {
'email': passwords.CLUBLOG_EMAIL,
'password': passwords.CLUBLOG_APPLICATION_PASSWORD,
'call': callsign,
'type': 'dxqsl',
'startyear': year,
'startmonth': month,
'startday': day
}
result = requests.post(url, data=values)
if (result.status_code != 200):
print(
"Error downloading data from Clublog for callsign {}".format(call))
return 5
log = result.text
logbook = adif_to_dictionary(log)
errors = 0
for qso in logbook:
c.execute("UPDATE log SET QslSent = %s, QslSentVia = %s, QslSDate = %s, Address = %s WHERE `Call` = %s AND Band = %s AND Mode = %s AND QsoDate = %s AND TimeOn LIKE %s", \
(qso['qsl_sent'], qso['qsl_sent_via'] if 'qsl_sent_via' in qso else('D' if 'address' in qso else 'B'), \
qso['qslsdate'] if 'qslsdate' in qso else '', \
(qso['call'] + ', ' + qso['address']).replace(', ', '\r\n').replace(',', '\r\n') if 'address' in qso else '', qso['call'], qso['band'], qso['mode'], qso['qso_date'], qso['time_on'][:4]+'%'))
if (c.rowcount != 1):
errors += 1
print(
"No match found for {} in QSO with {} on {} {}, {} {}".format(
callsign, qso['call'], qso['qso_date'], qso['time_on'],
qso['band'], qso['mode']))
print("{} OQRS requests downloaded for {}".format(
len(logbook) - errors, callsign))
db.commit()
db.close()
return 0
def lotw_download(callsign, lastSyncTime):
#Check we are setup to handle this callsign
if (callsign not in callsigns.callsign_list()):
print("Not setup to handle callsign: {}".format(callsign))
return 1
url = "https://lotw.arrl.org/lotwuser/lotwreport.adi"
values = {
'login': passwords.LOTW_USERNAME,
'password': passwords.LOTW_PASSWORD,
'qso_query': '1',
'qso_qsl': 'yes',
'qso_qslsince': lastSyncTime,
'qso_owncall': callsign
}
result = requests.get(url, values)
log = result.text
if (result.status_code != 200):
print("Error downloading data from LoTW")
return 3
logbook = adif_to_dictionary(log)
#Update MySQL database
try:
db = MySQLdb.connect(
host=passwords.SQL_SERVER,
user=passwords.SQL_USERNAME,
passwd=passwords.SQL_PASSWORD,
port=passwords.SQL_PORT,
db=callsign.replace('/', '_'),
client_flag=MySQLdb.constants.CLIENT.FOUND_ROWS
) #so rowcount returns matched rows rather than updated rows
except:
print("Failed to connect to SQL database for {} on {}:{}".format(
callsign, passwords.SQL_SERVER, passwords.SQL_PORT))
return 2
c = db.cursor()
errors = 0
for qso in logbook:
try: #Set Clublog upload status to no so it's re-uploaded showing confirmed
c.execute(
"UPDATE log SET LotwQslRcvd = 'Y', ClublogQsoUploadStatus = 'N' WHERE `Call` = %s AND Band = %s AND Mode = %s AND QsoDate = %s AND TimeOn LIKE %s",
(qso['call'], qso['band'], qso['mode'], qso['qso_date'],
(qso['time_on'][:-2] + '%')))
except KeyError:
print("No callsign found in record:")
print(qso)
errors += 1
if (c.rowcount != 1):
errors += 1
print('No record found in {}\'s log for {} at {} on {}, {} {}'.
format(callsign, qso['call'], qso['time_on'],
qso['qso_date'], qso['band'], qso['mode']))
print(
"{} QSLs for {} downloaded from LoTW since {}, {} records updated in log"
.format(len(logbook), callsign, lastSyncTime,
len(logbook) - errors))
db.commit()
if (errors == 0): return 0
else: return 4