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)