def __init__(self, config, override_table_name=None, engine_id=None):
super(IndicatorSqlAdapter, self).__init__(config)
self.engine_id = engine_id or get_engine_id(config)
self.session_helper = connection_manager.get_session_helper(self.engine_id)
self.session_context = self.session_helper.session_context
self.engine = self.session_helper.engine
self.override_table_name = override_table_name
def setUpClass(cls):
cls.session_helper = connection_manager.get_session_helper(settings.SQL_REPORTING_DATABASE_URL)
cls.engine = cls.session_helper.engine
cls.domain = create_domain(TEST_DOMAIN)
cls.region_type = LocationType.objects.create(domain=TEST_DOMAIN, name=u'Région')
cls.district_type = LocationType.objects.create(domain=TEST_DOMAIN, name=u'District')
cls.pps_type = LocationType.objects.create(domain=TEST_DOMAIN, name=u'PPS')
cls.region = Location(domain=TEST_DOMAIN, name='Test region', location_type=u'Région')
cls.region.save()
cls.district = Location(
domain=TEST_DOMAIN, name='Test district', location_type=u'District', parent=cls.region
)
cls.district.save()
cls.pps = Location(domain=TEST_DOMAIN, name='Test PPS', location_type=u'PPS', parent=cls.district)
cls.pps.save()
cls.mobile_worker = create_mobile_worker(
domain=TEST_DOMAIN, username='******', password='******', phone_number='777777'
)
cls.mobile_worker.location_id = cls.pps.get_id
cls.mobile_worker.save()
cls.product = Product(_id=u'81457658bdedd663f8b0bdadb19d8f22', name=u'ASAQ Nourisson', domain=TEST_DOMAIN)
cls.product2 = Product(
_id=u'81457658bdedd663f8b0bdadb19d83d8', name=u'ASAQ Petit Enfant', domain=TEST_DOMAIN
)
cls.product.save()
cls.product2.save()
def _load_data_from_db(self, table_name, sort_key):
engine = connection_manager.get_session_helper('default').engine
metadata = sqlalchemy.MetaData(bind=engine)
metadata.reflect(bind=engine)
table = metadata.tables[table_name]
columns = [
column.name
for column in table.columns
]
with engine.begin() as connection:
rows = connection.execute(table.select().order_by(*sort_key)).fetchall()
for row in rows:
row = list(row)
for idx, value in enumerate(row):
if isinstance(value, date):
row[idx] = value.strftime('%Y-%m-%d')
elif isinstance(value, time):
row[idx] = value.strftime("%H:%M:%S.%f").rstrip('0').rstrip('.')
elif isinstance(value, six.integer_types):
row[idx] = str(value)
elif isinstance(value, (float, Decimal)):
row[idx] = self._convert_decimal_to_string(row[idx])
elif isinstance(value, six.string_types):
row[idx] = value.encode('utf-8')
elif value is None:
row[idx] = ''
yield dict(zip(columns, row))
def handle(self, file_path, *args, **options):
domain = 'icds-cas'
data_source_id = StaticDataSourceConfiguration.get_doc_id(domain, PERSON_TABLE_ID)
config = StaticDataSourceConfiguration.by_id(data_source_id)
adapter = get_indicator_adapter(config)
session_helper = connection_manager.get_session_helper(adapter.engine_id)
person_table_name = get_table_name(domain, PERSON_TABLE_ID)
awc_location_table_name = get_table_name(domain, AWC_LOCATION_TABLE_ID)
session = session_helper.Session
with open(
os.path.join(os.path.dirname(__file__), 'sql_scripts', 'nos_of_deaths.sql'),
encoding='utf-8'
) as f:
sql_script = f.read()
rows = session.execute(
sql_script % {
'person_table_name': person_table_name,
'awc_location_table_name': awc_location_table_name
}
)
with open(file_path, 'w', encoding='utf-8') as file_object:
writer = csv.writer(file_object)
writer.writerow([
'State',
'District',
'AWC',
'Month',
'Deaths',
])
writer.writerows(rows)
def load_data_from_db(table_name):
def _convert_decimal_to_string(value):
value_str = str(value)
p = re.compile('0E-(?P<zeros>[0-9]+)')
match = p.match(value_str)
if match:
return '0.{}'.format(int(match.group('zeros')) * '0')
else:
return value_str
engine = connection_manager.get_session_helper('default').engine
metadata = sqlalchemy.MetaData(bind=engine)
metadata.reflect(bind=engine)
table = metadata.tables[table_name]
columns = [
column.name
for column in table.columns
]
with engine.begin() as connection:
for row in list(connection.execute(table.select())):
row = list(row)
for idx, value in enumerate(row):
if isinstance(value, date):
row[idx] = value.strftime('%Y-%m-%d')
elif isinstance(value, time):
row[idx] = value.strftime("%H:%M:%S.%f").rstrip('0').rstrip('.')
elif isinstance(value, six.integer_types):
row[idx] = str(value)
elif isinstance(value, (float, Decimal)):
row[idx] = _convert_decimal_to_string(row[idx])
elif six.PY2 and isinstance(value, six.text_type):
row[idx] = value.encode('utf-8')
elif value is None:
row[idx] = ''
yield dict(zip(columns, row))
def get_sql_queries(self):
qc = self.query_context()
for c in self.columns:
qc.append_column(c.view)
session_helper = connection_manager.get_session_helper(self.engine_id)
with session_helper.session_context() as session:
return qc.get_query_strings(session.connection())
def _get_data(self, start=None, limit=None):
if self.keys is not None and not self.group_by:
raise SqlReportException('Keys supplied without group_by.')
qc = self.query_context(start=start, limit=limit)
for c in self.columns:
qc.append_column(c.view)
session_helper = connection_manager.get_session_helper(self.engine_id)
with session_helper.session_context() as session:
return qc.resolve(session.connection(), self.filter_values)
def get_total_row(self):
def _clean_total_row(val, col):
if isinstance(val, numbers.Number):
return val
elif col.calculate_total:
return 0
return ''
def _get_relevant_column_ids(col, column_id_to_expanded_column_ids):
return column_id_to_expanded_column_ids.get(col.column_id, [col.column_id])
expanded_columns = get_expanded_columns(self.top_level_columns, self.config)
qc = self.query_context()
session_helper = connection_manager.get_session_helper(self.engine_id)
with session_helper.session_context() as session:
totals = qc.totals(
session.connection(),
[
column_id
for col in self.top_level_columns
for column_id in _get_relevant_column_ids(col, expanded_columns)
if col.calculate_total
],
self.filter_values
)
total_row = [
_clean_total_row(totals.get(column_id), col)
for col in self.top_level_columns for column_id in _get_relevant_column_ids(
col, expanded_columns
)
]
if total_row and total_row[0] is '':
total_row[0] = ugettext('Total')
return total_row
def get_total_records(self):
qc = self.query_context()
session_helper = connection_manager.get_session_helper(self.engine_id)
with session_helper.session_context() as session:
return qc.count(session.connection(), self.filter_values)
def session_helper(self):
return connection_manager.get_session_helper(self.adapter.engine_id,
readonly=True)
def __init__(self, config):
self.config = config
self.engine_id = get_engine_id(config)
self.session_helper = connection_manager.get_session_helper(self.engine_id)
self.engine = self.session_helper.engine
def __init__(self, config):
self.config = config
self.engine_id = get_engine_id(config)
self.session_helper = connection_manager.get_session_helper(
self.engine_id)
self.engine = self.session_helper.engine
def __init__(self, config):
super(IndicatorSqlAdapter, self).__init__(config)
self.engine_id = get_engine_id(config)
self.session_helper = connection_manager.get_session_helper(self.engine_id)
self.engine = self.session_helper.engine
def get_total_records(self):
qc = self.query_context()
session_helper = connection_manager.get_session_helper(self.engine_id,
readonly=True)
with session_helper.session_context() as session:
return qc.count(session.connection(), self.filter_values)
def get_query_strings(self):
qc = self.query_context()
session_helper = connection_manager.get_session_helper(self.engine_id,
readonly=True)
with session_helper.session_context() as session:
return qc.get_query_strings(session.connection())
def setUpModule():
if settings.USE_PARTITIONED_DATABASE:
print(
'============= WARNING: not running test setup because settings.USE_PARTITIONED_DATABASE is True.'
)
return
_call_center_domain_mock = mock.patch(
'corehq.apps.callcenter.data_source.call_center_data_source_configuration_provider'
)
_call_center_domain_mock.start()
domain = create_domain('icds-cas')
location_type = LocationType.objects.create(
domain=domain.name,
name='block',
)
SQLLocation.objects.create(domain=domain.name,
name='b1',
location_id='b1',
location_type=location_type)
awc_location_type = LocationType.objects.create(
domain=domain.name,
name='awc',
)
SQLLocation.objects.create(domain=domain.name,
name='a7',
location_id='a7',
location_type=awc_location_type)
with override_settings(SERVER_ENVIRONMENT='icds'):
configs = StaticDataSourceConfiguration.by_domain('icds-cas')
adapters = [get_indicator_adapter(config) for config in configs]
for adapter in adapters:
if adapter.config.table_id == 'static-child_health_cases':
# hack because this is in a migration
continue
adapter.build_table()
engine = connection_manager.get_session_helper(
settings.ICDS_UCR_TEST_DATABASE_ALIAS).engine
metadata = sqlalchemy.MetaData(bind=engine)
metadata.reflect(bind=engine, extend_existing=True)
path = os.path.join(os.path.dirname(__file__), 'fixtures')
for file_name in os.listdir(path):
with open(os.path.join(path, file_name)) as f:
table_name = FILE_NAME_TO_TABLE_MAPPING[file_name[:-4]]
table = metadata.tables[table_name]
postgres_copy.copy_from(f,
table,
engine,
format='csv',
null='',
header=True)
try:
move_ucr_data_into_aggregation_tables(datetime(2017, 5, 28),
intervals=2)
except AssertionError:
pass
_call_center_domain_mock.stop()
def __init__(self, config):
super(IndicatorSqlAdapter, self).__init__(config)
self.engine_id = get_engine_id(config)
self.session_helper = connection_manager.get_session_helper(
self.engine_id)
self.engine = self.session_helper.engine
from __future__ import absolute_import
from __future__ import unicode_literals
from collections import OrderedDict
import six
import sqlagg
import sqlalchemy
from sqlalchemy import select
from sqlalchemy.sql import func
from corehq.sql_db.connections import connection_manager
from corehq.apps.userreports.custom.data_source import ConfigurableReportCustomQueryProvider
session_helper = connection_manager.get_session_helper('icds-ucr')
metadata = sqlalchemy.MetaData(bind=session_helper.engine)
ChildHealthMonthlyViewAlchemy = sqlalchemy.Table('child_health_monthly_view',
metadata,
autoload=True)
class ChildHealthMonthlyUCR(ConfigurableReportCustomQueryProvider):
"""
Reasons these are needed:
- UCR needs to combine form data with case data
"""
def __init__(self, report_data_source):
self.report_data_source = report_data_source
self.helper = self.report_data_source.helper
self.helper.set_table(ChildHealthMonthlyViewAlchemy)
from __future__ import absolute_import
from __future__ import unicode_literals
from collections import OrderedDict
import sqlalchemy
from sqlalchemy.sql import func
from corehq.sql_db.connections import connection_manager
from corehq.apps.userreports.custom.data_source import ConfigurableReportCustomQueryProvider
session_helper = connection_manager.get_session_helper('icds-ucr')
metadata = sqlalchemy.MetaData(bind=session_helper.engine)
ChildHealthMonthlyViewAlchemy = sqlalchemy.Table(
'child_health_monthly_view', metadata, autoload=True
)
class ChildHealthMonthlyUCR(ConfigurableReportCustomQueryProvider):
"""
Reasons these are needed:
- UCR needs to combine form data with case data
"""
def __init__(self, report_data_source):
self.report_data_source = report_data_source
self.helper = self.report_data_source.helper
self.helper.set_table(ChildHealthMonthlyViewAlchemy)
