def save_item_prices(item_list): # check if we have any update to do if len(item_list['update']) > 0: update_stmt = ItemPrice.__table__.update() update_stmt = update_stmt.where( ItemPrice.item_id == db.bindparam('u_item_id')).where( ItemPrice.region_id == db.bindparam('u_region_id')) db.engine.execute(update_stmt, item_list['update'].values()) # check if we have any insert to do if len(item_list['insert']) > 0: # execute inserts db.engine.execute(ItemPrice.__table__.insert(), item_list['insert'].values()) db.session.commit()
def save_item_prices(item_list): # check if we have any update to do if len(item_list['update']) > 0: update_stmt = ItemPrice.__table__.update() update_stmt = update_stmt.where( ItemPrice.item_id == db.bindparam('u_item_id') ).where( ItemPrice.region_id == db.bindparam('u_region_id') ) db.engine.execute( update_stmt, list(item_list['update'].values()) ) # check if we have any insert to do if len(item_list['insert']) > 0: # execute inserts db.engine.execute( ItemPrice.__table__.insert(), list(item_list['insert'].values()) ) db.session.commit()
def import_solarsystem(self): """ Import solar systems from SDE to our database. CCP Table : mapSolarSystems """ added = 0 total = 0 # get all data self.sde_cursor.execute(""" SELECT solarSystemID , solarSystemName , regionID , constellationID FROM mapSolarSystems """) bulk_data = {} for row in self.sde_cursor: bulk_data[int(row[0])] = row[1:] new = [] update = [] solarsystems = SolarSystem.query.all() system_id_list = [ss.id for ss in solarsystems] # for each item from SDE, check valid data and check if it doesn't exist yet in our db for id, data in bulk_data.items(): total += 1 if not data[0] or not data[1] or not data[2]: continue item = { 'name': data[0], 'region_id': int(data[1]), 'constellation_id': int(data[2]), } if id in system_id_list: item['update_id'] = id update.append(item) else: item['id'] = id new.append(item) added += 1 # then create the new item if they exist if new: self.lb_engine.execute( SolarSystem.__table__.insert(), new ) if update: update_stmt = SolarSystem.__table__.update() update_stmt = update_stmt.where( SolarSystem.id == db.bindparam('update_id') ) db.engine.execute( update_stmt, update, ) return (added, total, "")
def import_region(self): """ Import region from SDE to our database. CCP Table : mapRegions """ added = 0 total = 0 # get all data self.sde_cursor.execute(""" SELECT mr.regionID , mr.regionName , CASE WHEN mrj.fromRegionID IS NULL THEN 1 ELSE 0 END AS IS_WH FROM mapRegions mr LEFT JOIN mapRegionJumps mrj ON mrj.fromRegionID = mr.regionID OR mrj.toRegionID = mr.regionID GROUP BY mr.regionID, mr.regionName, IS_WH """) bulk_data = {} for row in self.sde_cursor: bulk_data[int(row[0])] = row[1:] new = [] update = [] regions = Region.query.all() region_id_list = [r.id for r in regions] # for each item from SDE, check valid data and check if it doesn't exist yet in our db for id, data in bulk_data.items(): total += 1 if not data[0]: continue item = { 'name': data[0], 'wh': data[1] } if id in region_id_list: item['update_id'] = id update.append(item) else: item['id'] = id new.append(item) added += 1 # then create the new item if they exist if new: self.lb_engine.execute( Region.__table__.insert(), new ) if update: update_stmt = Region.__table__.update() update_stmt = update_stmt.where( Region.id == db.bindparam('update_id') ) db.engine.execute( update_stmt, update, ) return (added, total, "")
def import_item(self): """ Import items from SDE to our database. Add blueprint max production limit if exists CCP Table : InvTypes CCP Table : IndustryBlueprints """ added = 0 total = 0 new = [] update = [] bulk_data = {} item_list = [] # get all data, marketGroupID > 35k == dust514 self.sde_cursor.execute(""" SELECT i.typeID , i.typeName , ib.maxProductionLimit , i.marketGroupID , i.groupID , ig.categoryID , iap.typeID as manufacturing_bp , iap2.typeID as reaction_bp , i.volume FROM invTypes i LEFT JOIN industryBlueprints ib ON ib.typeID = i.typeID LEFT JOIN industryActivityProducts iap ON iap.productTypeID = i.typeID AND iap.activityID = 1 LEFT JOIN industryActivityProducts iap2 ON iap2.productTypeID = i.typeID AND iap2.activityID = 11 JOIN invGroups ig ON ig.groupID = i.groupID WHERE i.published=1 """) for row in self.sde_cursor: bulk_data[int(row[0])] = row[1:] items = Item.query.all() for item in items: item_list.append(item.id) # for each item from SDE, check valid data # and check if it doesn't exist yet in our db for id, data in bulk_data.items(): total += 1 if not data[0]: # do not change items that already exist but bugged in SDE if id in item_list: item_list.remove(id) continue else: nameFix = "Unknown Name SDE" item = { 'name': data[0] or nameFix, 'max_production_limit': int(data[1]) if data[1] else None, 'market_group_id': int(data[2]) if data[2] else None, 'group_id': int(data[3]) if data[3] else None, 'category_id': int(data[4]) if data[4] else None, 'is_from_manufacturing': (data[5] is not None), 'is_from_reaction': (data[6] is not None), 'volume': (Importer.PACKAGED.get(int(data[3]), float(data[7]))) } if id in item_list: item['update_id'] = id update.append(item) item_list.remove(id) else: item['id'] = id new.append(item) added += 1 # then create the new item if they exist if new: self.lb_engine.execute( Item.__table__.insert(), new ) if update: update_stmt = Item.__table__.update() update_stmt = update_stmt.where( Item.id == db.bindparam('update_id') ) db.engine.execute( update_stmt, update, ) db.session.commit() delete_stmt = Item.__table__.delete() delete_stmt = delete_stmt.where( Item.id == db.bindparam('delete_id') ) items_left = [] for item_id in item_list: try: db.engine.execute( delete_stmt, {'delete_id': item_id}, ) except: items_left.append(item_id) if items_left: comment = "Cannot remove items: %s" % items_left return (added, total, comment if items_left else "")
def import_solarsystem(self): """ Import solar systems from SDE to our database. CCP Table : mapSolarSystems """ added = 0 total = 0 # get all data self.sde_cursor.execute(""" SELECT solarSystemID , solarSystemName , regionID , constellationID FROM mapSolarSystems """) bulk_data = {} for row in self.sde_cursor: bulk_data[int(row[0])] = row[1:] new = [] update = [] solarsystems = SolarSystem.query.all() system_id_list = [ss.id for ss in solarsystems] # for each item from SDE, check valid data and check if it doesn't exist yet in our db for id, data in bulk_data.items(): total += 1 if not data[0] or not data[1] or not data[2]: continue item = { 'name': data[0], 'region_id': int(data[1]), 'constellation_id': int(data[2]), } if id in system_id_list: item['update_id'] = id update.append(item) else: item['id'] = id new.append(item) added += 1 # then create the new item if they exist if new: self.lb_engine.execute(SolarSystem.__table__.insert(), new) if update: update_stmt = SolarSystem.__table__.update() update_stmt = update_stmt.where( SolarSystem.id == db.bindparam('update_id')) db.engine.execute( update_stmt, update, ) return (added, total, "")
def import_region(self): """ Import region from SDE to our database. CCP Table : mapRegions """ added = 0 total = 0 # get all data self.sde_cursor.execute(""" SELECT mr.regionID , mr.regionName , CASE WHEN mrj.fromRegionID IS NULL THEN 1 ELSE 0 END AS IS_WH FROM mapRegions mr LEFT JOIN mapRegionJumps mrj ON mrj.fromRegionID = mr.regionID OR mrj.toRegionID = mr.regionID GROUP BY mr.regionID, mr.regionName, IS_WH """) bulk_data = {} for row in self.sde_cursor: bulk_data[int(row[0])] = row[1:] new = [] update = [] regions = Region.query.all() region_id_list = [r.id for r in regions] # for each item from SDE, check valid data and check if it doesn't exist yet in our db for id, data in bulk_data.items(): total += 1 if not data[0]: continue item = {'name': data[0], 'wh': data[1]} if id in region_id_list: item['update_id'] = id update.append(item) else: item['id'] = id new.append(item) added += 1 # then create the new item if they exist if new: self.lb_engine.execute(Region.__table__.insert(), new) if update: update_stmt = Region.__table__.update() update_stmt = update_stmt.where( Region.id == db.bindparam('update_id')) db.engine.execute( update_stmt, update, ) return (added, total, "")
def import_item(self): """ Import items from SDE to our database. Add blueprint max production limit if exists CCP Table : InvTypes CCP Table : IndustryBlueprints """ added = 0 total = 0 new = [] update = [] bulk_data = {} item_list = [] # get all data, marketGroupID > 35k == dust514 self.sde_cursor.execute(""" SELECT i.typeID , i.typeName , ib.maxProductionLimit , i.marketGroupID , i.groupID , ig.categoryID , iap.typeID as manufacturing_bp , iap2.typeID as reaction_bp FROM invTypes i LEFT JOIN industryBlueprints ib ON ib.typeID = i.typeID LEFT JOIN industryActivityProducts iap ON iap.productTypeID = i.typeID AND iap.activityID = 1 LEFT JOIN industryActivityProducts iap2 ON iap2.productTypeID = i.typeID AND iap2.activityID = 11 JOIN invGroups ig ON ig.groupID = i.groupID WHERE i.published=1 """) for row in self.sde_cursor: bulk_data[int(row[0])] = row[1:] items = Item.query.all() for item in items: item_list.append(item.id) # for each item from SDE, check valid data # and check if it doesn't exist yet in our db for id, data in bulk_data.items(): total += 1 if not data[0]: # do not change items that already exist but bugged in SDE if id in item_list: item_list.remove(id) continue else: nameFix = "Unknown Name SDE" item = { 'name': data[0] or nameFix, 'max_production_limit': int(data[1]) if data[1] else None, 'market_group_id': int(data[2]) if data[2] else None, 'group_id': int(data[3]) if data[3] else None, 'category_id': int(data[4]) if data[4] else None, 'is_from_manufacturing': (data[5] is not None), 'is_from_reaction': (data[6] is not None), } if id in item_list: item['update_id'] = id update.append(item) item_list.remove(id) else: item['id'] = id new.append(item) added += 1 # then create the new item if they exist if new: self.lb_engine.execute(Item.__table__.insert(), new) if update: update_stmt = Item.__table__.update() update_stmt = update_stmt.where( Item.id == db.bindparam('update_id')) db.engine.execute( update_stmt, update, ) db.session.commit() delete_stmt = Item.__table__.delete() delete_stmt = delete_stmt.where(Item.id == db.bindparam('delete_id')) items_left = [] for item_id in item_list: try: db.engine.execute( delete_stmt, {'delete_id': item_id}, ) except: items_left.append(item_id) if items_left: comment = "Cannot remove items: %s" % items_left return (added, total, comment if items_left else "")
def import_item(self): """ Import items from SDE to our database. Add blueprint max production limit if exists CCP Table : InvTypes CCP Table : IndustryBlueprints """ added = 0 total = 0 new = [] update = [] bulk_data = {} item_list = [] # get all data, marketGroupID > 35k == dust514 self.sde_cursor.execute(""" SELECT i.typeID , i.typeName , ib.maxProductionLimit , i.marketGroupID , ig.categoryID , i.marketGroupID FROM invTypes i LEFT JOIN industryBlueprints ib ON ib.typeID = i.typeID JOIN invGroups ig ON ig.groupID = i.groupID WHERE i.published=1 """) for row in self.sde_cursor: bulk_data[int(row[0])] = row[1:] items = Item.query.all() for item in items: item_list.append(item.id) # for each item from SDE, check valid data and check if it doesn't exist yet in our db for id, data in bulk_data.items(): total += 1 if not data[0]: continue item = { 'name': data[0], 'max_production_limit': int(data[1]) if data[1] else None, 'market_group_id': int(data[2]) if data[2] else None, 'category_id': int(data[3]) if data[3] else None, } if id in item_list: item['update_id'] = id update.append(item) else: item['id'] = id new.append(item) added += 1 # then create the new item if they exist if new: self.lb_engine.execute(Item.__table__.insert(), new) if update: update_stmt = Item.__table__.update() update_stmt = update_stmt.where( Item.id == db.bindparam('update_id')) db.engine.execute( update_stmt, update, ) return (added, total)