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)
