def get_aggregated_jobs(self, activity=None):
"""
Retrieve a list of all the jobs related to this order aggregated by item_id.
The job activity can be filtered to display only SUPPLY jobs
"""
where = ['"order_id" = %s']
if activity is not None:
where.append('"activity" = %s')
sql = 'SELECT "item_id", SUM("runs"), "activity" FROM "industry_job"'
sql += ' WHERE ' + ' AND '.join(where)
sql += ' GROUP BY "item_id", "activity" ORDER BY "activity", "item_id";'
sql = db.fix_mysql_quotes(sql)
cursor = connection.cursor() #@UndefinedVariable
if activity is not None:
cursor.execute(sql, [self.id, activity])
else:
cursor.execute(sql, [self.id])
jobs = []
for i, r, a in cursor:
jobs.append(Job(item_id=i, runs=r, activity=a))
cursor.close()
return jobs
def get_systems_data(request, date_str):
date = datetime.strptime(date_str, DATE_PATTERN)
date = timezone.make_aware(date, timezone.utc)
next_date = date + timedelta(seconds=1)
divisions = extract_divisions(request)
show_in_space = json.loads(request.GET.get('space', 'true'))
show_in_stations = json.loads(request.GET.get('stations', 'true'))
where = []
if not show_in_space:
where.append('"stationID" < %d' % constants.MAX_STATION_ID)
if not show_in_stations:
where.append('"stationID" > %d' % constants.MAX_STATION_ID)
if divisions is not None:
s = ('%s,' * len(divisions))[:-1]
where.append('"hangarID" IN (%s)' % s)
# TODO: fix this sql into an object
sql = 'SELECT "solarSystemID", COUNT(*) AS "items", SUM("volume") AS "volume" '
sql += 'FROM "assets_assetdiff" '
sql += 'WHERE date >= %s AND date < %s '
if where: sql += ' AND ' + ' AND '.join(where)
sql += ' GROUP BY "solarSystemID";'
sql = db.fix_mysql_quotes(sql)
cursor = connection.cursor() #@UndefinedVariable
if divisions is None:
cursor.execute(sql, [date, next_date])
else:
cursor.execute(sql, [date, next_date] + list(divisions))
jstree_data = []
for solarSystemID, items, volume in cursor:
try:
system = CelestialObject.objects.get(itemID=solarSystemID)
except CelestialObject.DoesNotExist:
system = CelestialObject(itemID=solarSystemID,
itemName=str(solarSystemID),
security=0)
if system.security > 0.5:
color = 'hisec'
elif system.security > 0:
color = 'lowsec'
else:
color = 'nullsec'
jstree_data.append({
'data':
HTML_ITEM_SPAN %
(system.itemName, items, pluralize(items), volume),
'attr': {
'id': '%d_' % solarSystemID,
'rel': 'system',
'sort_key': system.itemName.lower(),
'class': 'system-%s-row' % color
},
'state':
'closed'
})
cursor.close()
return HttpResponse(json.dumps(jstree_data))
def get_aggregated_jobs(self, activity=None):
"""
Retrieve a list of all the jobs related to this order aggregated by item_id.
The job activity can be filtered to display only SUPPLY jobs
"""
where = [ '"order_id" = %s' ]
if activity is not None:
where.append('"activity" = %s')
sql = 'SELECT "item_id", SUM("runs"), "activity" FROM "industry_job"'
sql += ' WHERE ' + ' AND '.join(where)
sql += ' GROUP BY "item_id", "activity" ORDER BY "activity", "item_id";'
sql = db.fix_mysql_quotes(sql)
cursor = connection.cursor() #@UndefinedVariable
if activity is not None:
cursor.execute(sql, [self.id, activity])
else:
cursor.execute(sql, [self.id])
jobs = []
for i, r, a in cursor:
jobs.append(Job(item_id=i, runs=r, activity=a))
cursor.close()
return jobs
def get_stations_data(request, date_str, solarSystemID):
date = datetime.strptime(date_str, DATE_PATTERN)
date = timezone.make_aware(date, timezone.utc)
next_date = date + timedelta(seconds=1)
solarSystemID = int(solarSystemID)
divisions = extract_divisions(request)
show_in_space = json.loads(request.GET.get('space', 'true'))
show_in_stations = json.loads(request.GET.get('stations', 'true'))
where = []
if not show_in_space:
where.append('"stationID" < %d' % constants.MAX_STATION_ID)
if not show_in_stations:
where.append('"stationID" > %d' % constants.MAX_STATION_ID)
if divisions is not None:
s = ('%s,' * len(divisions))[:-1]
where.append('"hangarID" IN (%s)' % s)
sql = 'SELECT "stationID", MAX("flag") as "flag", COUNT(*) AS "items", SUM("volume") AS "volume" '
sql += 'FROM "assets_assetdiff" '
sql += 'WHERE "solarSystemID"=%s AND "date" >= %s AND "date" < %s '
if where: sql += ' AND ' + ' AND '.join(where)
sql += ' GROUP BY "stationID";'
sql = db.fix_mysql_quotes(sql)
cursor = connection.cursor() #@UndefinedVariable
if divisions is None:
cursor.execute(sql, [solarSystemID, date, next_date])
else:
cursor.execute(sql, [solarSystemID, date, next_date] + list(divisions))
jstree_data = []
for stationID, flag, items, volume in cursor:
if stationID < constants.MAX_STATION_ID:
# it's a real station
try:
name = CelestialObject.objects.get(itemID=stationID).itemName
except CelestialObject.DoesNotExist:
name = str(stationID)
icon = 'station'
else:
# it is an inspace anchorable array
name = Type.objects.get(typeID=flag).typeName
icon = 'array'
jstree_data.append({
'data':
HTML_ITEM_SPAN % (name, items, pluralize(items), volume),
'attr': {
'id': '%d_%d_' % (solarSystemID, stationID),
'sort_key': stationID,
'rel': icon,
'class': '%s-row' % icon
},
'state':
'closed'
})
cursor.close()
return HttpResponse(json.dumps(jstree_data))
def player_contributions(since=datetime.utcfromtimestamp(0),
until=timezone.now(),
order_by="tax_contrib",
ascending=False):
sql = PLAYER_CONTRIB_SQL + order_by + (" ASC;" if ascending else " DESC;")
sql = db.fix_mysql_quotes(sql)
return User.objects.raw(sql, [since, until])
def get_hangars_data(request, solarSystemID, closest_obj_id, stationID):
solarSystemID = int(solarSystemID)
closest_obj_id = int(closest_obj_id)
stationID = int(stationID)
divisions = extract_divisions(request)
where = []
if divisions is not None:
where.append('"hangarID" IN (%s)' % ', '.join(['%s'] * len(divisions)))
sql = 'SELECT "hangarID", COUNT(*) AS "items", SUM("volume") AS "volume" '\
'FROM "assets_asset" '\
'WHERE "solarSystemID"=%s AND "closest_object_id"=%s AND "stationID"=%s '
if where: sql += ' AND ' + ' AND '.join(where)
sql += ' GROUP BY "hangarID";'
sql = db.fix_mysql_quotes(sql)
cursor = connection.cursor() #@UndefinedVariable
if divisions is None:
cursor.execute(sql, [solarSystemID, closest_obj_id, stationID])
else:
cursor.execute(sql, [solarSystemID, closest_obj_id, stationID] +
list(divisions))
exact_volumes = Setting.get('assets_show_exact_volumes')
HANGAR = Hangar.DEFAULT_NAMES.copy()
for h in CorpHangar.objects.filter(corp=Corporation.objects.mine()):
HANGAR[h.hangar_id] = h.name
jstree_data = []
for hangarID, items, volume in cursor:
if exact_volumes:
volume = print_float(volume)
else:
volume = round_quantity(volume)
jstree_data.append({
'data':
HTML_ITEM_SPAN %
(HANGAR[hangarID], items, pluralize(items), volume),
'attr': {
'id':
'%d_%d_%d_%d_' %
(solarSystemID, closest_obj_id, stationID, hangarID),
'sort_key':
hangarID,
'rel':
'hangar',
'class':
'hangar-row'
},
'state':
'closed'
})
return HttpResponse(json.dumps(jstree_data))
def get_systems_data(request):
divisions = extract_divisions(request)
show_in_space = json.loads(request.GET.get('space', 'true'))
show_in_stations = json.loads(request.GET.get('stations', 'true'))
where = []
if not show_in_space:
where.append('"stationID" < %d' % constants.MAX_STATION_ID)
if not show_in_stations:
where.append('"stationID" > %d' % constants.MAX_STATION_ID)
if divisions is not None:
where.append('"hangarID" IN (%s)' % ', '.join(['%s'] * len(divisions)))
sql = 'SELECT "solarSystemID", COUNT(*) AS "items", SUM("volume") AS "volume" '\
'FROM "assets_asset" '
if where: sql += ' WHERE ' + ' AND '.join(where)
sql += ' GROUP BY "solarSystemID";'
sql = db.fix_mysql_quotes(sql)
cursor = connection.cursor() #@UndefinedVariable
if divisions is None:
cursor.execute(sql)
else:
cursor.execute(sql, divisions)
exact_volumes = Setting.get('assets_show_exact_volumes')
jstree_data = []
for solarSystemID, items, volume in cursor:
try:
system = CelestialObject.objects.get(itemID=solarSystemID)
except CelestialObject.DoesNotExist:
system = CelestialObject(itemID=solarSystemID, itemName=str(solarSystemID), security=0)
if system.security > 0.5:
color = 'hisec'
elif system.security > 0:
color = 'lowsec'
else:
color = 'nullsec'
if exact_volumes:
volume = print_float(volume)
else:
volume = round_quantity(volume)
jstree_data.append({
'data' : HTML_ITEM_SPAN % (system.itemName, items, pluralize(items), volume),
'attr' : {
'id' : '%d_' % solarSystemID,
'rel' : 'system',
'sort_key' : system.itemName.lower(),
'class' : 'system-%s-row' % color
},
'state' : 'closed'
})
cursor.close()
return HttpResponse(json.dumps(jstree_data))
def get_celestial_objects_data(request, solarSystemID):
solarSystemID = int(solarSystemID)
divisions = extract_divisions(request)
show_in_space = json.loads(request.GET.get('space', 'true'))
show_in_stations = json.loads(request.GET.get('stations', 'true'))
where = []
if not show_in_space:
where.append('"stationID" < %d' % constants.MAX_STATION_ID)
if not show_in_stations:
where.append('"stationID" > %d' % constants.MAX_STATION_ID)
if divisions is not None:
where.append('"hangarID" IN (%s)' % ', '.join(['%s'] * len(divisions)))
sql = 'SELECT "closest_object_id", COUNT(*), SUM("volume") '\
'FROM "assets_asset" '\
'WHERE "solarSystemID"=%s '
if where:
sql += ' AND ' + ' AND '.join(where)
sql += ' GROUP BY "closest_object_id";'
sql = db.fix_mysql_quotes(sql)
cursor = connection.cursor() #@UndefinedVariable
if divisions is None:
cursor.execute(sql, [solarSystemID])
else:
cursor.execute(sql, [solarSystemID] + list(divisions))
exact_volumes = Setting.get('assets_show_exact_volumes')
jstree_data = []
for closest_object_id, items, volume in cursor:
if closest_object_id != 0:
try:
name = CelestialObject.objects.get(itemID=closest_object_id).itemName
except CelestialObject.DoesNotExist:
name = str(closest_object_id)
else:
name = 'Stations'
if exact_volumes:
volume = print_float(volume)
else:
volume = round_quantity(volume)
jstree_data.append({
'data' : HTML_ITEM_SPAN % (name, items, pluralize(items), volume),
'attr' : {
'id' : '%d_%d_' % (solarSystemID, closest_object_id),
'sort_key' : closest_object_id,
'rel' : 'celestial',
'class' : 'celestial-row',
},
'state' : 'closed'
})
cursor.close()
return HttpResponse(json.dumps(jstree_data))
def get_stations_data(request, date_str, solarSystemID):
date = datetime.strptime(date_str, DATE_PATTERN)
date = timezone.make_aware(date, timezone.utc)
next_date = date + timedelta(seconds=1)
solarSystemID = int(solarSystemID)
divisions = extract_divisions(request)
show_in_space = json.loads(request.GET.get('space', 'true'))
show_in_stations = json.loads(request.GET.get('stations', 'true'))
where = []
if not show_in_space:
where.append('"stationID" < %d' % constants.MAX_STATION_ID)
if not show_in_stations:
where.append('"stationID" > %d' % constants.MAX_STATION_ID)
if divisions is not None:
s = ('%s,' * len(divisions))[:-1]
where.append('"hangarID" IN (%s)' % s)
sql = 'SELECT "stationID", MAX("flag") as "flag", COUNT(*) AS "items", SUM("volume") AS "volume" '
sql += 'FROM "assets_assetdiff" '
sql += 'WHERE "solarSystemID"=%s AND "date" >= %s AND "date" < %s '
if where: sql += ' AND ' + ' AND '.join(where)
sql += ' GROUP BY "stationID";'
sql = db.fix_mysql_quotes(sql)
cursor = connection.cursor() #@UndefinedVariable
if divisions is None:
cursor.execute(sql, [solarSystemID, date, next_date])
else:
cursor.execute(sql, [solarSystemID, date, next_date] + list(divisions))
jstree_data = []
for stationID, flag, items, volume in cursor:
if stationID < constants.MAX_STATION_ID:
# it's a real station
try:
name = CelestialObject.objects.get(itemID=stationID).itemName
except CelestialObject.DoesNotExist:
name = str(stationID)
icon = 'station'
else:
# it is an inspace anchorable array
name = Type.objects.get(typeID=flag).typeName
icon = 'array'
jstree_data.append({
'data' : HTML_ITEM_SPAN % (name, items, pluralize(items), volume),
'attr' : {
'id' : '%d_%d_' % (solarSystemID, stationID),
'sort_key' : stationID,
'rel' : icon,
'class' : '%s-row' % icon
},
'state' : 'closed'
})
cursor.close()
return HttpResponse(json.dumps(jstree_data))
def system_contributions(since=datetime.utcfromtimestamp(0), until=timezone.now(),
order_by="tax_contrib", ascending=False):
sql = SYSTEM_CONTRIB_SQL + order_by + (" ASC;" if ascending else " DESC;")
sql = db.fix_mysql_quotes(sql)
cursor = connection.cursor() #@UndefinedVariable
cursor.execute(sql, [since, until])
return cursor.fetchall()
def get_systems_data(request, date_str):
date = datetime.strptime(date_str, DATE_PATTERN)
date = timezone.make_aware(date, timezone.utc)
next_date = date + timedelta(seconds=1)
divisions = extract_divisions(request)
show_in_space = json.loads(request.GET.get('space', 'true'))
show_in_stations = json.loads(request.GET.get('stations', 'true'))
where = []
if not show_in_space:
where.append('"stationID" < %d' % constants.MAX_STATION_ID)
if not show_in_stations:
where.append('"stationID" > %d' % constants.MAX_STATION_ID)
if divisions is not None:
s = ('%s,' * len(divisions))[:-1]
where.append('"hangarID" IN (%s)' % s)
# TODO: fix this sql into an object
sql = 'SELECT "solarSystemID", COUNT(*) AS "items", SUM("volume") AS "volume" '
sql += 'FROM "assets_assetdiff" '
sql += 'WHERE date >= %s AND date < %s '
if where: sql += ' AND ' + ' AND '.join(where)
sql += ' GROUP BY "solarSystemID";'
sql = db.fix_mysql_quotes(sql)
cursor = connection.cursor() #@UndefinedVariable
if divisions is None:
cursor.execute(sql, [date, next_date])
else:
cursor.execute(sql, [date, next_date] + list(divisions))
jstree_data = []
for solarSystemID, items, volume in cursor:
try:
system = CelestialObject.objects.get(itemID=solarSystemID)
except CelestialObject.DoesNotExist:
system = CelestialObject(itemID=solarSystemID, itemName=str(solarSystemID), security=0)
if system.security > 0.5:
color = 'hisec'
elif system.security > 0:
color = 'lowsec'
else:
color = 'nullsec'
jstree_data.append({
'data' : HTML_ITEM_SPAN % (system.itemName, items, pluralize(items), volume),
'attr' : {
'id' : '%d_' % solarSystemID,
'rel' : 'system',
'sort_key' : system.itemName.lower(),
'class' : 'system-%s-row' % color
},
'state' : 'closed'
})
cursor.close()
return HttpResponse(json.dumps(jstree_data))
def get_members(query, first_id, last_id, search_str=None, sort_by=0, asc=True, for_csv=False):
query = query.select_related(depth=2) # improve performance
sort_col = MEMBERS_COLUMNS[sort_by]["db_field"]
# SQL hack for making a case insensitive sort
if sort_by == 0:
sort_col = sort_col + "_nocase"
sort_val = db.fix_mysql_quotes('LOWER("%s")' % MEMBERS_COLUMNS[sort_by]["db_field"])
query = query.extra(select={sort_col: sort_val})
if not asc:
sort_col = "-" + sort_col
query = query.extra(order_by=([sort_col]))
if search_str:
total_members = query.count()
search_args = Q(name__icontains=search_str) | Q(nickname__icontains=search_str)
if "DIRECTOR".startswith(search_str.upper()):
search_args = search_args | Q(accessLvl=Member.DIRECTOR_ACCESS_LVL)
query = query.filter(search_args)
filtered_members = query.count()
else:
total_members = filtered_members = query.count()
my_corp = Corporation.objects.mine()
member_list = []
if for_csv:
for member in query:
member_list.append(
[member.name, member.corp or "-", get_standing(member, my_corp), member.owner or "-", member.location]
)
else:
for member in query[first_id:last_id]:
if member.corp:
corp = '<span title="%s">%s</span>' % (member.corp, member.corp.ticker)
else:
corp = "-"
memb = [
member.permalink,
corp,
get_standing(member, my_corp),
member.owner_permalink,
member.dotlan_location,
]
member_list.append(memb)
return total_members, filtered_members, member_list
def system_contributions(since=datetime.utcfromtimestamp(0),
until=timezone.now(),
order_by="tax_contrib",
ascending=False):
sql = SYSTEM_CONTRIB_SQL + order_by + (" ASC;" if ascending else " DESC;")
sql = db.fix_mysql_quotes(sql)
cursor = connection.cursor() #@UndefinedVariable
cursor.execute(sql, [since, until])
return cursor.fetchall()
def items_data(request):
"""
Serves URL /industry/catalog/items/data/ (jQuery datatables plugin)
"""
try:
params = extract_datatable_params(request)
REQ = request.GET if request.method == 'GET' else request.POST
params.showUnavailable = REQ.get('showUnavailable', 'true') == 'true'
except:
return HttpResponseBadRequest()
query = CatalogEntry.objects.all()
query = query.annotate(blueprint_count=Count("blueprints"))
query = query.annotate(order_count=Count("order_rows"))
if not params.showUnavailable:
query = query.filter(is_available=True)
sort_col = COLUMNS[params.column][1]
# SQL hack for making a case insensitive sort
if params.column == 1:
sort_col = sort_col + "_nocase"
sort_val = db.fix_mysql_quotes('LOWER("%s")' % COLUMNS[params.column])
query = query.extra(select={sort_col: sort_val})
if not params.asc:
sort_col = "-" + sort_col
query = query.extra(order_by=([sort_col]))
if params.search:
total_items = query.count()
query = query.filter(typeName__icontains=params.search)
filtered_items = query.count()
else:
total_items = filtered_items = query.count()
items = []
for item in query[params.first_id:params.last_id]:
items.append([
item.permalink,
bool(item.is_available),
print_float(item.fixed_price),
print_float(item.production_cost),
print_float(item.public_price),
item.blueprint_count,
item.order_count,
item.typeID,
])
return datatable_ajax_data(data=items,
echo=params.sEcho,
total=total_items,
filtered=filtered_items)
def items_data(request):
"""
Serves URL /industry/catalog/items/data/ (jQuery datatables plugin)
"""
try:
params = extract_datatable_params(request)
REQ = request.GET if request.method == 'GET' else request.POST
params.showUnavailable = REQ.get('showUnavailable', 'true') == 'true'
except:
return HttpResponseBadRequest()
query = CatalogEntry.objects.all()
query = query.annotate(blueprint_count=Count("blueprints"))
query = query.annotate(order_count=Count("order_rows"))
if not params.showUnavailable:
query = query.filter(is_available=True)
sort_col = COLUMNS[params.column][1]
# SQL hack for making a case insensitive sort
if params.column == 1:
sort_col = sort_col + "_nocase"
sort_val = db.fix_mysql_quotes('LOWER("%s")' % COLUMNS[params.column])
query = query.extra(select={ sort_col : sort_val })
if not params.asc:
sort_col = "-" + sort_col
query = query.extra(order_by=([sort_col]))
if params.search:
total_items = query.count()
query = query.filter(typeName__icontains=params.search)
filtered_items = query.count()
else:
total_items = filtered_items = query.count()
items = []
for item in query[params.first_id:params.last_id]:
items.append([
item.permalink,
bool(item.is_available),
print_float(item.fixed_price),
print_float(item.production_cost),
print_float(item.public_price),
item.blueprint_count,
item.order_count,
item.typeID,
])
return datatable_ajax_data(data=items, echo=params.sEcho,
total=total_items, filtered=filtered_items)
def get_hangars_data(request, solarSystemID, closest_obj_id, stationID):
solarSystemID = int(solarSystemID)
closest_obj_id = int(closest_obj_id)
stationID = int(stationID)
divisions = extract_divisions(request)
where = []
if divisions is not None:
where.append('"hangarID" IN (%s)' % ', '.join(['%s'] * len(divisions)))
sql = 'SELECT "hangarID", COUNT(*) AS "items", SUM("volume") AS "volume" '\
'FROM "assets_asset" '\
'WHERE "solarSystemID"=%s AND "closest_object_id"=%s AND "stationID"=%s '
if where: sql += ' AND ' + ' AND '.join(where)
sql += ' GROUP BY "hangarID";'
sql = db.fix_mysql_quotes(sql)
cursor = connection.cursor() #@UndefinedVariable
if divisions is None:
cursor.execute(sql, [solarSystemID, closest_obj_id, stationID])
else:
cursor.execute(sql, [solarSystemID, closest_obj_id, stationID] + list(divisions))
exact_volumes = Setting.get('assets_show_exact_volumes')
HANGAR = Hangar.DEFAULT_NAMES.copy()
for h in CorpHangar.objects.filter(corp=Corporation.objects.mine()):
HANGAR[h.hangar_id] = h.name
jstree_data = []
for hangarID, items, volume in cursor:
if exact_volumes:
volume = print_float(volume)
else:
volume = round_quantity(volume)
jstree_data.append({
'data': HTML_ITEM_SPAN % (HANGAR[hangarID], items, pluralize(items), volume),
'attr' : {
'id' : '%d_%d_%d_%d_' % (solarSystemID, closest_obj_id, stationID, hangarID),
'sort_key' : hangarID,
'rel' : 'hangar',
'class' : 'hangar-row'
},
'state' : 'closed'
})
return HttpResponse(json.dumps(jstree_data))
def get_hangars_data(request, date_str, solarSystemID, stationID):
date = datetime.strptime(date_str, DATE_PATTERN)
date = timezone.make_aware(date, timezone.utc)
next_date = date + timedelta(seconds=1)
solarSystemID = int(solarSystemID)
stationID = int(stationID)
divisions = extract_divisions(request)
where = []
if divisions is not None:
s = ('%s,' * len(divisions))[:-1]
where.append('"hangarID" IN (%s)' % s)
sql = 'SELECT "hangarID", COUNT(*) AS "items", SUM("volume") AS "volume" '
sql += 'FROM "assets_assetdiff" '
sql += 'WHERE "solarSystemID"=%s AND "stationID"=%s AND "date" >= %s AND "date" < %s '
if where: sql += ' AND ' + ' AND '.join(where)
sql += ' GROUP BY "hangarID";'
sql = db.fix_mysql_quotes(sql)
cursor = connection.cursor() #@UndefinedVariable
if divisions is None:
cursor.execute(sql, [solarSystemID, stationID, date, next_date])
else:
cursor.execute(sql, [solarSystemID, stationID, date, next_date] +
list(divisions))
HANGAR = Hangar.DEFAULT_NAMES.copy()
for h in CorpHangar.objects.filter(corp=Corporation.objects.mine()):
HANGAR[h.hangar_id] = h.name
jstree_data = []
for hangarID, items, volume in cursor.fetchall():
jstree_data.append({
'data':
HTML_ITEM_SPAN %
(HANGAR[hangarID], items, pluralize(items), volume),
'attr': {
'id': '%d_%d_%d_' % (solarSystemID, stationID, hangarID),
'sort_key': hangarID,
'rel': 'hangar',
'class': 'hangar-row'
},
'state':
'closed'
})
return HttpResponse(json.dumps(jstree_data))
def get_hangars_data(request, date_str, solarSystemID, stationID):
date = datetime.strptime(date_str, DATE_PATTERN)
date = timezone.make_aware(date, timezone.utc)
next_date = date + timedelta(seconds=1)
solarSystemID = int(solarSystemID)
stationID = int(stationID)
divisions = extract_divisions(request)
where = []
if divisions is not None:
s = ('%s,' * len(divisions))[:-1]
where.append('"hangarID" IN (%s)' % s)
sql = 'SELECT "hangarID", COUNT(*) AS "items", SUM("volume") AS "volume" '
sql += 'FROM "assets_assetdiff" '
sql += 'WHERE "solarSystemID"=%s AND "stationID"=%s AND "date" >= %s AND "date" < %s '
if where: sql += ' AND ' + ' AND '.join(where)
sql += ' GROUP BY "hangarID";'
sql = db.fix_mysql_quotes(sql)
cursor = connection.cursor() #@UndefinedVariable
if divisions is None:
cursor.execute(sql, [solarSystemID, stationID, date, next_date])
else:
cursor.execute(sql, [solarSystemID, stationID, date, next_date] + list(divisions))
HANGAR = Hangar.DEFAULT_NAMES.copy()
for h in CorpHangar.objects.filter(corp=Corporation.objects.mine()):
HANGAR[h.hangar_id] = h.name
jstree_data = []
for hangarID, items, volume in cursor.fetchall():
jstree_data.append({
'data': HTML_ITEM_SPAN % (HANGAR[hangarID], items, pluralize(items), volume),
'attr' : {
'id' : '%d_%d_%d_' % (solarSystemID, stationID, hangarID),
'sort_key' : hangarID,
'rel' : 'hangar',
'class' : 'hangar-row'
},
'state' : 'closed'
})
return HttpResponse(json.dumps(jstree_data))
def history_data(request):
try:
params = extract_datatable_params(request)
except:
return HttpResponseBadRequest()
query = MemberDiff.objects.all()
if params.column == 0 or params.column > 3:
params.column = 3
params.asc = False
sort_col = COLUMNS[params.column]['db_field']
# SQL hack for making a case insensitive sort
if params.column in (1, 2):
sort_col = sort_col + "_nocase"
sort_val = db.fix_mysql_quotes('LOWER("%s")' % COLUMNS[params.column]['db_field'])
query = query.extra(select={ sort_col : sort_val })
if not params.asc: sort_col = "-" + sort_col
query = query.extra(order_by=([sort_col]))
if params.search:
total_members = query.count()
search_args = Q(name__icontains=params.search) | Q(nickname__icontains=params.search)
query = query.filter(search_args)
filtered_members = query.count()
else:
total_members = filtered_members = query.count()
members = []
for diff in query[params.first_id:params.last_id]:
members.append([
diff.new,
diff.permalink,
truncate_words(diff.nickname, 5),
print_time_min(diff.date)
])
return datatable_ajax_data(members, params.sEcho, total_members, filtered_members)
def player_contributions(since=datetime.utcfromtimestamp(0), until=timezone.now(),
order_by="tax_contrib", ascending=False):
sql = PLAYER_CONTRIB_SQL + order_by + (" ASC;" if ascending else " DESC;")
sql = db.fix_mysql_quotes(sql)
return User.objects.raw(sql, [since, until])
def poses_data(request):
'''
Read data when table request by Ajax.
This method takes into account search filter and segmentation table
Read the http request (GET method handling)
'''
try:
params = extract_datatable_params(request)
REQ = request.GET if request.method == 'GET' else request.POST
params.displayMode = REQ.get('displayMode', 'days')
except:
return HttpResponseBadRequest()
# Query all authorised by default except for superuser
if request.user.is_superuser:
query = POS.objects.all().select_related(depth=1)
else:
query = POS.objects.select_related(depth=1)
query = query.filter(Q(authorized_groups__isnull=True) |
Q(authorized_groups__in=request.user.groups.all()))
# Then get the database content and translate to display table
# manage the search filter
if params.search:
total_count = query.count()
query = POS.objects.filter(moon__icontains=params.search)
filtered_count = query.count()
else:
total_count = filtered_count = query.count()
sort_col = COLUMNS[params.column][2]
if params.column == 1:
# SQL hack for making a case insensitive sort
sort_col = sort_col + "_nocase"
sort_val = db.fix_mysql_quotes('LOWER("%s")' % COLUMNS[params.column][2])
query = query.extra(select={ sort_col : sort_val })
elif params.column in (5, 6):
# if sorting by fuel or stront. make a sorted list of (hoursleft|quantity,pos)
# so that we can easily present and sort the data.
pos_by_timeleft = []
if params.displayMode == 'quantities':
for pos in query:
if params.column == 5:
quantity = pos.fuel_levels.filter(type_id=pos.fuel_type_id).latest().quantity
else:
quantity = pos.fuel_levels.filter(type_id=C.STRONTIUM_CLATHRATES_TYPEID).latest().quantity
pos_by_timeleft.append( (quantity, pos, pos.state) )
else:
for pos in query:
if params.column == 5:
time_left = getFuelValue(pos, pos.fuel_type_id, 'hours_int')
else:
time_left = getFuelValue(pos, C.STRONTIUM_CLATHRATES_TYPEID, 'hours_int')
pos_by_timeleft.append( (time_left, pos, pos.state) )
if not params.asc:
pos_by_timeleft.sort(reverse=True)
else:
pos_by_timeleft.sort()
for i, item in enumerate(pos_by_timeleft):
if item[2] == 3:
pos_by_timeleft.insert(0, pos_by_timeleft.pop(i))
try:
# This will fail if sorting by fuel.
if not params.asc:
sort_col = "-" + sort_col
except TypeError:
pass
query = query.extra(order_by=('state', sort_col))
pos_table = []
if params.column < 5:
for pos in query[params.first_id:params.last_id]:
# Query into Fuel table to get last values. for the current POS
if pos.state == 3:
stront = pos.state_timestamp.strftime('%H:%M, %d %b')
else:
stront = getFuelValue(pos, C.STRONTIUM_CLATHRATES_TYPEID, params.displayMode)
pos_table.append([
pos.permalink,
pos.custom_name,
pos.type_id,
pos.state,
pos.time_until_next_cycle,
getFuelValue(pos, pos.fuel_type_id, params.displayMode),
stront,
pos.type_name,
getFuelValue(pos, pos.fuel_type_id, 'hours_int'),
])
else:
# Since its a sorted list of tuples now it needs slightly different handling
for _, pos, state in pos_by_timeleft[params.first_id:params.last_id]:
if state == 3:
stront = pos.state_timestamp.strftime('%H:%M, %d %b')
else:
stront = getFuelValue(pos, C.STRONTIUM_CLATHRATES_TYPEID, params.displayMode)
pos_table.append([
pos.permalink,
pos.custom_name,
pos.type_id,
pos.state,
pos.time_until_next_cycle,
getFuelValue(pos, pos.fuel_type_id, params.displayMode),
stront,
pos.type_name,
getFuelValue(pos, pos.fuel_type_id, 'hours_int'),
])
return datatable_ajax_data(pos_table, params.sEcho, total_count, filtered_count)
def get_members(query, first_id, last_id, search_str=None, sort_by=0, asc=True, for_csv=False):
query = query.select_related(depth=2) # improve performance
sort_col = MEMBERS_COLUMNS[sort_by]['db_field']
# SQL hack for making a case insensitive sort
if sort_by == 0:
sort_col = sort_col + "_nocase"
sort_val = db.fix_mysql_quotes('LOWER("%s")' % MEMBERS_COLUMNS[sort_by]['db_field'])
query = query.extra(select={ sort_col : sort_val })
if not asc: sort_col = "-" + sort_col
query = query.extra(order_by=([sort_col]))
if search_str:
total_members = query.count()
search_args = Q(name__icontains=search_str) | Q(nickname__icontains=search_str)
if "DIRECTOR".startswith(search_str.upper()):
search_args = search_args | Q(accessLvl=Member.DIRECTOR_ACCESS_LVL)
query = query.filter(search_args)
filtered_members = query.count()
else:
total_members = filtered_members = query.count()
member_list = []
if for_csv:
for member in query:
member_list.append([
member.name,
member.nickname,
member.corp or '-',
member.owner,
member.lastLogin,
member.dotlan_location,
member.ship or '(docked)',
member.accessLvl,
' '.join([ t.titleName for t in member.titles.all() ]),
])
else:
for member in query[first_id:last_id]:
titles = ["Titles"]
titles.extend(member.titles.values_list("titleName", flat=True))
if member.corp:
corp = '<span title="%s">%s</span>' % (member.corp, member.corp.ticker)
else:
corp = '-'
memb = [
member.permalink,
corp,
member.owner_permalink,
member.accessLvl,
print_date(member.lastLogin),
member.dotlan_location,
member.ship or '(docked)',
"|".join(titles)
]
member_list.append(memb)
return total_members, filtered_members, member_list
def get_members(query,
first_id,
last_id,
search_str=None,
sort_by=0,
asc=True,
for_csv=False):
query = query.select_related(depth=2) # improve performance
sort_col = MEMBERS_COLUMNS[sort_by]['db_field']
# SQL hack for making a case insensitive sort
if sort_by == 0:
sort_col = sort_col + "_nocase"
sort_val = db.fix_mysql_quotes('LOWER("%s")' %
MEMBERS_COLUMNS[sort_by]['db_field'])
query = query.extra(select={sort_col: sort_val})
if not asc: sort_col = "-" + sort_col
query = query.extra(order_by=([sort_col]))
if search_str:
total_members = query.count()
search_args = Q(name__icontains=search_str) | Q(
nickname__icontains=search_str)
if "DIRECTOR".startswith(search_str.upper()):
search_args = search_args | Q(accessLvl=Member.DIRECTOR_ACCESS_LVL)
query = query.filter(search_args)
filtered_members = query.count()
else:
total_members = filtered_members = query.count()
my_corp = Corporation.objects.mine()
member_list = []
if for_csv:
for member in query:
member_list.append([
member.name,
member.corp or '-',
get_standing(member, my_corp),
member.owner or '-',
member.location,
])
else:
for member in query[first_id:last_id]:
if member.corp:
corp = '<span title="%s">%s</span>' % (member.corp,
member.corp.ticker)
else:
corp = '-'
memb = [
member.permalink,
corp,
get_standing(member, my_corp),
member.owner_permalink,
member.dotlan_location,
]
member_list.append(memb)
return total_members, filtered_members, member_list
for c in ColorThreshold.objects.all().order_by("threshold"):
colorThresholds.append({"threshold": c.threshold, "color": c.color})
data = {
"scan_date": UpdateDate.get_latest(TitleComposition),
"colorThresholds": json.dumps(colorThresholds),
"columns": TITLES_COLUMNS,
"datatables_defaults": DATATABLES_DEFAULTS,
}
return render_to_response("ecm/hr/titles/titles.html", data, Ctx(request))
# ------------------------------------------------------------------------------
SQL_TITLE_MEMBERS = db.fix_mysql_quotes(
'''SELECT COUNT(*)
FROM "hr_titlemembership"
WHERE "hr_titlemembership"."title_id"="hr_title"."id"'''
)
SQL_ROLES_IN_TITLES = db.fix_mysql_quotes(
'''SELECT COUNT(*)
FROM "hr_titlecomposition"
WHERE "hr_titlecomposition"."title_id"="hr_title"."id"'''
)
@check_user_access()
@cache_page(3 * 60 * 60) # 3 hours cache
def titles_data(request):
try:
params = extract_datatable_params(request)
corp_id = request.GET.get("corp")
def get_stations_data(request, solarSystemID, closest_obj_id):
solarSystemID = int(solarSystemID)
closest_obj_id = int(closest_obj_id)
divisions = extract_divisions(request)
show_in_space = json.loads(request.GET.get('space', 'true'))
show_in_stations = json.loads(request.GET.get('stations', 'true'))
where = []
if not show_in_space:
where.append('"stationID" < %d' % constants.MAX_STATION_ID)
if not show_in_stations:
where.append('"stationID" > %d' % constants.MAX_STATION_ID)
if divisions is not None:
where.append('"hangarID" IN (%s)' % ', '.join(['%s'] * len(divisions)))
sql = 'SELECT "stationID", MAX("name"), MAX("flag"), COUNT(*), SUM("volume") '\
'FROM "assets_asset" '\
'WHERE "solarSystemID"=%s AND "closest_object_id"=%s '
if where: sql += ' AND ' + ' AND '.join(where)
sql += ' GROUP BY "stationID";'
sql = db.fix_mysql_quotes(sql)
cursor = connection.cursor() #@UndefinedVariable
if divisions is None:
cursor.execute(sql, [solarSystemID, closest_obj_id])
else:
cursor.execute(sql, [solarSystemID, closest_obj_id] + list(divisions))
exact_volumes = Setting.get('assets_show_exact_volumes')
jstree_data = []
for stationID, item_name, flag, items, volume in cursor:
if stationID < constants.MAX_STATION_ID:
# it's a real station
try:
name = CelestialObject.objects.get(itemID=stationID).itemName
except CelestialObject.DoesNotExist:
name = str(stationID)
icon = 'station'
else:
# it is an inspace anchorable array
type_name = Type.objects.get(typeID=flag).typeName
name = type_name
if item_name and type_name != item_name:
name += ' "%s"' % item_name
if constants.CONTROL_TOWERS.has_key(flag):
icon = 'pos'
else:
icon = 'array'
if exact_volumes:
volume = print_float(volume)
else:
volume = round_quantity(volume)
jstree_data.append({
'data' : HTML_ITEM_SPAN % (name, items, pluralize(items), volume),
'attr' : {
'id' : '%d_%d_%d_' % (solarSystemID, closest_obj_id, stationID),
'sort_key' : stationID,
'rel' : icon,
'class' : '%s-row' % icon
},
'state' : 'closed'
})
cursor.close()
return HttpResponse(json.dumps(jstree_data))
def get_systems_data(request):
divisions = extract_divisions(request)
show_in_space = json.loads(request.GET.get('space', 'true'))
show_in_stations = json.loads(request.GET.get('stations', 'true'))
where = []
if not show_in_space:
where.append('"stationID" < %d' % constants.MAX_STATION_ID)
if not show_in_stations:
where.append('"stationID" > %d' % constants.MAX_STATION_ID)
if divisions is not None:
where.append('"hangarID" IN (%s)' % ', '.join(['%s'] * len(divisions)))
sql = 'SELECT "solarSystemID", COUNT(*) AS "items", SUM("volume") AS "volume" '\
'FROM "assets_asset" '
if where: sql += ' WHERE ' + ' AND '.join(where)
sql += ' GROUP BY "solarSystemID";'
sql = db.fix_mysql_quotes(sql)
cursor = connection.cursor() #@UndefinedVariable
if divisions is None:
cursor.execute(sql)
else:
cursor.execute(sql, divisions)
exact_volumes = Setting.get('assets_show_exact_volumes')
jstree_data = []
for solarSystemID, items, volume in cursor:
try:
system = CelestialObject.objects.get(itemID=solarSystemID)
except CelestialObject.DoesNotExist:
system = CelestialObject(itemID=solarSystemID,
itemName=str(solarSystemID),
security=0)
if system.security > 0.5:
color = 'hisec'
elif system.security > 0:
color = 'lowsec'
else:
color = 'nullsec'
if exact_volumes:
volume = print_float(volume)
else:
volume = round_quantity(volume)
jstree_data.append({
'data':
HTML_ITEM_SPAN %
(system.itemName, items, pluralize(items), volume),
'attr': {
'id': '%d_' % solarSystemID,
'rel': 'system',
'sort_key': system.itemName.lower(),
'class': 'system-%s-row' % color
},
'state':
'closed'
})
cursor.close()
return HttpResponse(json.dumps(jstree_data))
colorThresholds = []
for c in ColorThreshold.objects.all().order_by("threshold"):
colorThresholds.append({"threshold": c.threshold, "color": c.color})
data = {
'scan_date': UpdateDate.get_latest(TitleComposition),
'colorThresholds': json.dumps(colorThresholds),
'columns': TITLES_COLUMNS,
'datatables_defaults': DATATABLES_DEFAULTS
}
return render_to_response("ecm/hr/titles/titles.html", data, Ctx(request))
#------------------------------------------------------------------------------
SQL_TITLE_MEMBERS = db.fix_mysql_quotes('''SELECT COUNT(*)
FROM "hr_titlemembership"
WHERE "hr_titlemembership"."title_id"="hr_title"."id"''')
SQL_ROLES_IN_TITLES = db.fix_mysql_quotes('''SELECT COUNT(*)
FROM "hr_titlecomposition"
WHERE "hr_titlecomposition"."title_id"="hr_title"."id"''')
@check_user_access()
@cache_page(3 * 60 * 60) # 3 hours cache
def titles_data(request):
try:
params = extract_datatable_params(request)
corp_id = request.GET.get('corp')
except KeyError:
return HttpResponseBadRequest()
def get_stations_data(request, solarSystemID, closest_obj_id):
solarSystemID = int(solarSystemID)
closest_obj_id = int(closest_obj_id)
divisions = extract_divisions(request)
show_in_space = json.loads(request.GET.get('space', 'true'))
show_in_stations = json.loads(request.GET.get('stations', 'true'))
where = []
if not show_in_space:
where.append('"stationID" < %d' % constants.MAX_STATION_ID)
if not show_in_stations:
where.append('"stationID" > %d' % constants.MAX_STATION_ID)
if divisions is not None:
where.append('"hangarID" IN (%s)' % ', '.join(['%s'] * len(divisions)))
sql = 'SELECT "stationID", MAX("name"), MAX("flag"), COUNT(*), SUM("volume") '\
'FROM "assets_asset" '\
'WHERE "solarSystemID"=%s AND "closest_object_id"=%s '
if where: sql += ' AND ' + ' AND '.join(where)
sql += ' GROUP BY "stationID";'
sql = db.fix_mysql_quotes(sql)
cursor = connection.cursor() #@UndefinedVariable
if divisions is None:
cursor.execute(sql, [solarSystemID, closest_obj_id])
else:
cursor.execute(sql, [solarSystemID, closest_obj_id] + list(divisions))
exact_volumes = Setting.get('assets_show_exact_volumes')
jstree_data = []
for stationID, item_name, flag, items, volume in cursor:
if stationID < constants.MAX_STATION_ID:
# it's a real station
try:
name = CelestialObject.objects.get(itemID=stationID).itemName
except CelestialObject.DoesNotExist:
name = str(stationID)
icon = 'station'
else:
# it is an inspace anchorable array
type_name = Type.objects.get(typeID=flag).typeName
name = type_name
if item_name and type_name != item_name:
name += ' "%s"' % item_name
if constants.CONTROL_TOWERS.has_key(flag):
icon = 'pos'
else:
icon = 'array'
if exact_volumes:
volume = print_float(volume)
else:
volume = round_quantity(volume)
jstree_data.append({
'data':
HTML_ITEM_SPAN % (name, items, pluralize(items), volume),
'attr': {
'id': '%d_%d_%d_' % (solarSystemID, closest_obj_id, stationID),
'sort_key': stationID,
'rel': icon,
'class': '%s-row' % icon
},
'state':
'closed'
})
cursor.close()
return HttpResponse(json.dumps(jstree_data))
def get_celestial_objects_data(request, solarSystemID):
solarSystemID = int(solarSystemID)
divisions = extract_divisions(request)
show_in_space = json.loads(request.GET.get('space', 'true'))
show_in_stations = json.loads(request.GET.get('stations', 'true'))
where = []
if not show_in_space:
where.append('"stationID" < %d' % constants.MAX_STATION_ID)
if not show_in_stations:
where.append('"stationID" > %d' % constants.MAX_STATION_ID)
if divisions is not None:
where.append('"hangarID" IN (%s)' % ', '.join(['%s'] * len(divisions)))
sql = 'SELECT "closest_object_id", COUNT(*), SUM("volume") '\
'FROM "assets_asset" '\
'WHERE "solarSystemID"=%s '
if where:
sql += ' AND ' + ' AND '.join(where)
sql += ' GROUP BY "closest_object_id";'
sql = db.fix_mysql_quotes(sql)
cursor = connection.cursor() #@UndefinedVariable
if divisions is None:
cursor.execute(sql, [solarSystemID])
else:
cursor.execute(sql, [solarSystemID] + list(divisions))
exact_volumes = Setting.get('assets_show_exact_volumes')
jstree_data = []
for closest_object_id, items, volume in cursor:
if closest_object_id != 0:
try:
name = CelestialObject.objects.get(
itemID=closest_object_id).itemName
except CelestialObject.DoesNotExist:
name = str(closest_object_id)
else:
name = 'Stations'
if exact_volumes:
volume = print_float(volume)
else:
volume = round_quantity(volume)
jstree_data.append({
'data':
HTML_ITEM_SPAN % (name, items, pluralize(items), volume),
'attr': {
'id': '%d_%d_' % (solarSystemID, closest_object_id),
'sort_key': closest_object_id,
'rel': 'celestial',
'class': 'celestial-row',
},
'state':
'closed'
})
cursor.close()
return HttpResponse(json.dumps(jstree_data))