def updateIndyJobsLog():
""""""
indyJobs = API.getIndustryJobs()
valuesList = []
for job in indyJobs:
jobID = job.job_id
bpID = job.blueprint_id
activityID = job.activity_id
productTypeID = job.product_type_id
productName = utils.idName(productTypeID)
activityName = utils.activityID2Name[activityID]
bpTypeID = job.blueprint_type_id
bpName = utils.idName(bpTypeID)
runs = job.runs
cost = job.cost
startDate = job.start_date
endDate = job.end_date
installerID = job.installer_id
installerName = API.getName(installerID).name
dbRow = (jobID, bpID, bpTypeID, activityID, productTypeID,
activityName, bpName, productName, runs, cost, startDate,
endDate, installerID, installerName)
valuesList.append(dbRow)
presentJobIDs = _getPresentJobIDs()
valuesList = [x for x in valuesList if x[0] not in presentJobIDs]
if valuesList:
with utils.logDb:
utils.logDb.executemany(('INSERT INTO indyJobsLog '
'VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)'),
valuesList)
def updateMarketOrders():
""""""
marketOrders = API.getMarketOrders()
valuesList = []
for marketOrder in marketOrders:
orderID = marketOrder.order_id
stationID = marketOrder.location_id
remainingItems = marketOrder.volume_remain
typeID = marketOrder.type_id
typeName = utils.idName(typeID)
sellOrder = 0 if marketOrder.is_buy_order else 1
stationName = utils.stationName(stationID)
dbRow = (orderID,
typeID,
typeName,
remainingItems,
sellOrder,
stationID,
stationName)
valuesList.append(dbRow)
with utils.currentDb:
utils.currentDb.executemany( ('INSERT INTO MarketOrders '
'VALUES (?,?,?,?,?,?,?)')
, valuesList)
def updateMaterials():
""""""
assets = utils.currentDb.execute( (f'SELECT "typeID", "quantity" '
f'FROM "Assets" ') )
assetRows = assets.fetchall()
materialsDict = {}
for row in assetRows:
typeID = row[0]
quantity = row[1]
if typeID in materialsDict:
materialsDict[typeID] += quantity
else:
materialsDict[typeID] = quantity
valuesList = []
for typeID in materialsDict:
buildable = int(utils.buildable(typeID))
typeName = utils.idName(typeID)
dbRow = (typeID,
typeName,
materialsDict[typeID],
buildable)
valuesList.append(dbRow)
with utils.currentDb:
utils.currentDb.executemany( ('INSERT INTO AggregatedMaterials '
'VALUES (?,?,?,?)')
, valuesList)
def upgradeTransactionLog():
""""""
transactions = API.getMarketTransactions()
valuesList = []
for transaction in transactions:
transID = transaction.transaction_id
journalRefID = transaction.journal_ref_id
date = transaction.date
typeID = transaction.type_id
typeName = utils.idName(typeID)
quantity = transaction.quantity
unitPrice = transaction.unit_price
totalPrice = unitPrice * quantity
locID = transaction.location_id
isSell = int(not transaction.is_buy)
clientID = transaction.client_id
#transID, journalRefID, date, typeID, quantity, unitPrice, locationID, isSell, clientID
dbRow = (transID, journalRefID, date, typeID, typeName, quantity,
unitPrice, totalPrice, locID, isSell, clientID)
valuesList.append(dbRow)
presentTransIDs = _getPresentTransIDs()
valuesList = [x for x in valuesList if x[0] not in presentTransIDs]
if valuesList:
with utils.logDb:
utils.logDb.executemany(('INSERT INTO transactionLog '
'VALUES (?,?,?,?,?,?,?,?,?,?,?)'),
valuesList)
def _updateCachedAvgPrice(typeID, price):
"""updates the average price on the current database"""
name = utils.idName(typeID)
date = time.time()
command = (f'REPLACE into avgPrices (typeID, typeName, avgPrice, date) '
f'VALUES({typeID}, "{name}", {price}, {date})')
with utils.currentDb as database:
database.execute(command)
def updateIndustryJobs():
""""""
indyJobs = API.getIndustryJobs()
#jobid, itemid, bpid, bptypeName, runs, prodtypeID, prodTypeName endDate, status
valuesList = []
for job in indyJobs:
jobID = job.job_id
bpID = job.blueprint_id
bpTypeID = job.blueprint_type_id
bpTypeName = utils.idName(bpTypeID)
runs = job.runs
endDate = job.end_date
status = job.status
productTypeID = job.product_type_id
productTypeName = utils.idName(productTypeID)
installerID = job.installer_id
installerName = API.getName(installerID).name
activityID = job.activity_id
activityName = utils.activityID2Name[activityID]
dbRow = (jobID, #
bpID, #
bpTypeID, #
bpTypeName, #
runs, #
productTypeID, #
productTypeName, #
endDate, #
status, #
installerID, #
installerName, #
activityID, #
activityName) #
valuesList.append(dbRow)
with utils.currentDb:
utils.currentDb.executemany( ('INSERT INTO IndustryJobs '
'VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)')
, valuesList)
def updateAdjustedPrices():
""""""
adjustedPrices = API.getAdjustedPrices()
valuesList = []
for item in adjustedPrices:
typeID = item.type_id
typeName = utils.idName(typeID)
adjPrice = item.adjusted_price
avgPrice = item.average_price
dbRow = (typeID, typeName, adjPrice, avgPrice)
valuesList.append(dbRow)
with utils.currentDb:
utils.currentDb.executemany( ('INSERT INTO adjPrices '
'VALUES (?,?,?,?)')
, valuesList)
def updateAssets():
"""
take data in the form of:
{
"is_singleton": false,
"item_id": 1000000016835,
"location_flag": "Hangar",
"location_id": 60002959,
"location_type": "station",
"type_id": 3516
}
from the assets api and push the appropriate components to the database
"""
assets = API.getAssets()
valuesList = []
for item in assets:
itemName = utils.idName(item.type_id)
#check if bp ###normally this is done through singleton checks, but singletons don't work
if 'blueprint' in itemName.lower():
continue
dbRow = (item.item_id,
item.type_id,
item.is_singleton,
item.location_id,
item.location_flag,
item.location_type,
item.quantity,
itemName)
valuesList.append(dbRow)
with utils.currentDb:
utils.currentDb.executemany('INSERT INTO Assets VALUES (?,?,?,?,?,?,?,?)', valuesList)
def updateBlueprints():
"""get blueprint data in the form of
{
'item_id': 1001131739044,
'location_flag': 'CorpSAG2',
'location_id': 1025695702796,
'material_efficiency': 10,
'quantity': -1,
'runs': -1,
'time_efficiency': 20,
'type_id': 1153
}
and push it to the blueprints table
"""
blueprints = API.getBlueprints()
valuesList = []
for blueprint in blueprints:
#recover plain name
itemName = utils.idName(blueprint.type_id)
#determina if bpo, t1 copy or t2 copy ### or t3 copy
if blueprint.quantity == -1:
bpo = 1
else:
bpo = 0
bpClass = utils.bpClass(blueprint.type_id)
#determine product id and name
productID = utils.productID(blueprint.type_id)
if productID:
productName = utils.idName(productID)
else:
productID = 'NULL'
productName = 'NULL'
#determine if inventable or invented
inventable = int(utils.inventable(blueprint.type_id)) #needs to be integer not bool
inventedFromID = utils.inventedFrom(blueprint.type_id)
if not inventedFromID:
inventedFromID = 'NULL'
inventedFromName = 'NULL'
else:
inventedFromName = utils.idName(inventedFromID)
#determine if component
if bpClass == 1:
component = int(utils.component(blueprint.type_id))
#set ignore flag
dbRow = (blueprint.item_id,
blueprint.type_id,
itemName,
blueprint.location_id,
blueprint.location_flag,
bpo,
bpClass,
blueprint.material_efficiency,
blueprint.time_efficiency,
blueprint.runs,
productID,
productName,
component,
inventable,
inventedFromID,
inventedFromName )
valuesList.append(dbRow)
#itemID, typeID, typeName, class, ME, TE, runs, prodID, prodName, inventable, component,
#inventedFrom, inventedFromName
with utils.currentDb:
utils.currentDb.executemany('INSERT INTO Blueprints VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
valuesList)
def updateMaterialLog():
""""""
#getting newly updated info
materials = utils.currentDb.execute((f'SELECT "typeID", "quantity" '
f'FROM "AggregatedMaterials" '))
materialsRows = materials.fetchall()
#getting current mats
currentMatsDict = {}
for material in materialsRows:
typeID = material[0]
currentQuantity = material[1]
currentMatsDict[typeID] = currentQuantity
#checking for new materials
valuesList = []
for typeID, currentQuantity in currentMatsDict.items():
#checking previous log entry for this material
lastLogEntry = _getLastLogEntry(typeID)
if lastLogEntry:
oldBalance = lastLogEntry[1]
if oldBalance == currentMatsDict[typeID]:
continue
else:
matEntryID = 'NULL'
delta = currentQuantity - oldBalance
timestamp = time.time()
balance = currentQuantity
typeName = utils.idName(typeID)
dbRow = (timestamp, typeID, delta, balance, typeName)
valuesList.append(dbRow)
else:
matEntryID = 'NULL'
delta = currentQuantity
timestamp = time.time()
balance = currentQuantity
typeName = utils.idName(typeID)
dbRow = (timestamp, typeID, delta, balance, typeName)
valuesList.append(dbRow)
#checking for depleted materials
lastEntries = _getLastEntries()
for entry in lastEntries:
typeID = entry[0][0]
timestamp = entry[1]
balance = entry[2]
if typeID not in currentMatsDict and balance > 0:
matEntryID = 'NULL'
delta = -balance
timestamp = time.time()
balance = 0
typeName = utils.idName(typeID)
dbRow = (timestamp, typeID, delta, balance, typeName)
valuesList.append(dbRow)
#updating database
with utils.logDb:
utils.logDb.executemany(('INSERT INTO materialsLog '
'(timestamp,typeID,delta,balance,typeName)'
'VALUES (?,?,?,?,?)'), valuesList)
