def query_hot_n(self, lang, hot = None):
if not hot:
hot = Setting.objects.get(pk = settings.HOT_VISUALIZATIONS).value
sql = """SELECT `ao_datastream_revisions`.`id` AS `datastream_revision_id`,
`ao_visualizations_revisions`.`id` AS `visualization_revision_id`,
`ao_datastreams`.`id` AS `datastream_id`,
`ao_visualizations`.`id` AS `visualization_id`,
`ao_visualizations_revisions`.`impl_details`,
`ao_datastream_i18n`.`title`,
`ao_datastream_i18n`.`description`,
`ao_categories_i18n`.`name` AS `category_name`,
`ao_users`.`account_id`
FROM `ao_visualizations_revisions`
INNER JOIN `ao_visualizations` ON (`ao_visualizations_revisions`.`visualization_id` = `ao_visualizations`.`id`)
INNER JOIN `ao_datastreams` ON (`ao_visualizations`.`datastream_id` = `ao_datastreams`.`id`)
INNER JOIN `ao_datastream_revisions` ON (`ao_datastreams`.`id` = `ao_datastream_revisions`.`datastream_id` AND `ao_datastream_revisions`.`status` = 3)
INNER JOIN `ao_datastream_i18n` ON (`ao_datastream_revisions`.`id` = `ao_datastream_i18n`.`datastream_revision_id`)
INNER JOIN `ao_categories` ON (`ao_datastream_revisions`.`category_id` = `ao_categories`.`id`)
INNER JOIN `ao_categories_i18n` ON (`ao_categories`.`id` = `ao_categories_i18n`.`category_id`)
INNER JOIN `ao_users` ON (`ao_visualizations`.`user_id` = `ao_users`.`id`)
WHERE `ao_visualizations_revisions`.`id` IN (
SELECT MAX(`ao_visualizations_revisions`.`id`)
FROM `ao_visualizations_revisions`
WHERE `ao_visualizations_revisions`.`visualization_id` IN ("""+ hot +""")
AND `ao_visualizations_revisions`.`status` = 3
GROUP BY `visualization_id`
)
AND `ao_categories_i18n`.`language` = %s
ORDER BY `ao_visualizations`.`id` DESC, `ao_datastreams`.`id` DESC, `ao_visualizations_revisions`.`id` DESC, `ao_datastream_revisions`.`id` DESC"""
cursor = connection.cursor()
cursor.execute(sql, (lang,))
rows = cursor.fetchall().__iter__()
row = helpers.next(rows, None)
visualizations = []
while row != None:
datastream_id = row[2]
visualization_id = row[3]
title = row[5]
permalink = reverse('chart_manager.view', kwargs={'id': visualization_id, 'slug': slugify(title)})
visualizations.append({'id' : row[0],
'sov_id' : row[1],
'impl_details' : row[4],
'title' : title,
'description' : row[6],
'category' : row[7],
'permalink' : permalink,
'account_id' : row[8]
})
while row != None and datastream_id == row[2] and visualization_id == row[3]:
row = helpers.next(rows, None)
return visualizations
def query_hot_n(self, lang, hot = None):
if not hot:
hot = Setting.objects.get(pk = settings.HOT_VISUALIZATIONS).value
sql = """SELECT `ao_datastream_revisions`.`id` AS `datastream_revision_id`,
`ao_visualizations_revisions`.`id` AS `visualization_revision_id`,
`ao_datastreams`.`id` AS `datastream_id`,
`ao_visualizations`.`id` AS `visualization_id`,
`ao_visualizations_revisions`.`impl_details`,
`ao_datastream_i18n`.`title`,
`ao_datastream_i18n`.`description`,
`ao_categories_i18n`.`name` AS `category_name`,
`ao_users`.`account_id`
FROM `ao_visualizations_revisions`
INNER JOIN `ao_visualizations` ON (`ao_visualizations_revisions`.`visualization_id` = `ao_visualizations`.`id`)
INNER JOIN `ao_datastreams` ON (`ao_visualizations`.`datastream_id` = `ao_datastreams`.`id`)
INNER JOIN `ao_datastream_revisions` ON (`ao_datastreams`.`id` = `ao_datastream_revisions`.`datastream_id` AND `ao_datastream_revisions`.`status` = 3)
INNER JOIN `ao_datastream_i18n` ON (`ao_datastream_revisions`.`id` = `ao_datastream_i18n`.`datastream_revision_id`)
INNER JOIN `ao_categories` ON (`ao_datastream_revisions`.`category_id` = `ao_categories`.`id`)
INNER JOIN `ao_categories_i18n` ON (`ao_categories`.`id` = `ao_categories_i18n`.`category_id`)
INNER JOIN `ao_users` ON (`ao_visualizations`.`user_id` = `ao_users`.`id`)
WHERE `ao_visualizations_revisions`.`id` IN (
SELECT MAX(`ao_visualizations_revisions`.`id`)
FROM `ao_visualizations_revisions`
WHERE `ao_visualizations_revisions`.`visualization_id` IN ("""+ hot +""")
AND `ao_visualizations_revisions`.`status` = 3
GROUP BY `visualization_id`
)
AND `ao_categories_i18n`.`language` = %s
ORDER BY `ao_visualizations`.`id` DESC, `ao_datastreams`.`id` DESC, `ao_visualizations_revisions`.`id` DESC, `ao_datastream_revisions`.`id` DESC"""
cursor = connection.cursor()
cursor.execute(sql, (lang,))
rows = cursor.fetchall().__iter__()
row = helpers.next(rows, None)
visualizations = []
while row != None:
datastream_id = row[2]
visualization_id = row[3]
title = row[5]
permalink = reverse('chart_manager.view', kwargs={'id': visualization_id, 'slug': slugify(title)})
visualizations.append({'id' : row[0],
'sov_id' : row[1],
'impl_details' : row[4],
'title' : title,
'description' : row[6],
'category' : row[7],
'permalink' : permalink,
'account_id' : row[8]
})
while row != None and datastream_id == row[2] and visualization_id == row[3]:
row = helpers.next(rows, None)
return visualizations
def queryByDataStreamRevisionId(self,
p_revision_id,
p_language=settings.LANGUAGE_CODE[0:2]):
parameter_id = 0
parameter_position = 1
parameter_name = 2
parameter_description = 3
l_parameter_cursor = connection.cursor()
l_parameter_cursor.execute(
"""
SELECT id
, coalesce(position, 0)
, name
, description
FROM ao_datastream_parameters
WHERE datastream_revision_id = %s
ORDER BY position
""", [p_revision_id])
l_parameters_rows = l_parameter_cursor.fetchall().__iter__()
l_parameter_row = helpers.next(l_parameters_rows, None)
l_parameters = []
while l_parameter_row != None:
l_parameter_id = l_parameter_row[parameter_id]
l_parameter_name = l_parameter_row[parameter_name]
l_parameter_description = l_parameter_row[parameter_description]
l_parameter = dict(parameter_id=l_parameter_id,
parameter_name=l_parameter_name,
parameter_description=l_parameter_description,
parameter_value=None)
l_parameters.append(l_parameter)
l_parameter_row = helpers.next(l_parameters_rows, None)
return l_parameters
def queryByDataStreamRevisionId(self, p_revision_id, p_language=settings.LANGUAGE_CODE[0:2]):
parameter_id = 0
parameter_position = 1
parameter_name = 2
parameter_description = 3
l_parameter_cursor = connection.cursor()
l_parameter_cursor.execute("""
SELECT id
, coalesce(position, 0)
, name
, description
FROM ao_datastream_parameters
WHERE datastream_revision_id = %s
ORDER BY position
""", [p_revision_id])
l_parameters_rows = l_parameter_cursor.fetchall().__iter__()
l_parameter_row = helpers.next(l_parameters_rows, None)
l_parameters = []
while l_parameter_row != None:
l_parameter_id = l_parameter_row[parameter_id]
l_parameter_name = l_parameter_row[parameter_name]
l_parameter_description = l_parameter_row[parameter_description]
l_parameter = dict(parameter_id=l_parameter_id
, parameter_name=l_parameter_name
, parameter_description=l_parameter_description
, parameter_value=None)
l_parameters.append(l_parameter)
l_parameter_row = helpers.next(l_parameters_rows, None)
return l_parameters
def getEndPointById(self, p_id):
dataset_revision_id = 0
dataset_end_point = 1
l_dataset_cursor = connection.cursor()
l_dataset_cursor.execute("""SELECT id
, end_point
FROM ao_dataset_revisions
WHERE dataset_id = %s
ORDER BY id DESC
""", [p_id])
l_datasets_rows = l_dataset_cursor.fetchall().__iter__()
l_dataset_row = helpers.next(l_datasets_rows, None)
l_dataset_revision_id = l_dataset_row[dataset_revision_id]
l_dataset_end_point = l_dataset_row[dataset_end_point]
return l_dataset_end_point, l_dataset_end_point.startswith('file://')
def getEndPointById(self, p_id):
dataset_revision_id = 0
dataset_end_point = 1
l_dataset_cursor = connection.cursor()
l_dataset_cursor.execute(
"""SELECT id
, end_point
FROM ao_dataset_revisions
WHERE dataset_id = %s
ORDER BY id DESC
""", [p_id])
l_datasets_rows = l_dataset_cursor.fetchall().__iter__()
l_dataset_row = helpers.next(l_datasets_rows, None)
l_dataset_revision_id = l_dataset_row[dataset_revision_id]
l_dataset_end_point = l_dataset_row[dataset_end_point]
return l_dataset_end_point, l_dataset_end_point.startswith('file://')
