def fetch_responses(id):
    m = hashlib.md5()
    m.update(id)
    
    hash = m.hexdigest()

    report = { 'status': 'OK', 'errors': [], 'id': id,  'hash': hash, 'questions': MOBILYZE_QUESTIONS,  }
    
    if database_exists(hash) == False:
        report['status'] = 'Error'
        report['errors'].append('No such database ' + hash + ' for ID ' + id + '.')
    else:
        now = datetime.datetime.utcnow()
        start = datetime.datetime.utcfromtimestamp(0)
        
        response_table = 'undefined'
        
        total_count = 0
        
        std_devs = []
        
        id_responses = {}
#        responses = {}

        if table_exists(hash, response_table) == False:
            report['status'] = 'Error'
            report['errors'].append('No such table ' + response_table + ' for ID ' + id + '.')
        else:
            for response_field in MOBILYZE_QUESTIONS:
                values = fetch_data(hash, response_table, response_field, start, now, filter=True)
                
                for value in values:
                    if value[1] != None and str(value[1]).strip() != '':
                        key = key_for_user(id, value[0])
                        
                        value_dict = {}
                        
                        if key in id_responses:
                            value_dict = id_responses[key]
                        else:
                            id_responses[key] = value_dict
                            
                        num_values = []
                        
                        if response_field in value_dict:
                            num_values = value_dict[response_field]
                        else:
                            value_dict[response_field] = num_values
                        
                        num_values.append(float(value[1]))
                        
            for key, value_dict in id_responses.iteritems():
                for response_field,num_values in value_dict.iteritems():
                    value_dict[response_field] = numpy.mean(num_values)
                    
        return id_responses
        
    return {}
    def handle(self, *args, **options):
        count = 0
        
        admin_user = DashboardUser.objects.get(username=args[0])

        mobilyze = Study.objects.get(slug='mobilyze-2013')

        table = 'undefined'
        now = datetime.datetime.utcnow()
        start = datetime.datetime.utcfromtimestamp(0)
        
        all_values = {} # date, name, value

        for study in Study.objects.filter(slug='mobilyze-2013'):
            for id in study.participant_ids(admin_user):
                m = hashlib.md5()
                m.update(id)
    
                hash = m.hexdigest()
                
                readings = []

                try:
                    readings = all_values[id]
                except KeyError:
                    pass
                    
                if database_exists(hash):
                    if table_exists(hash, table):
                        for column in MOBILYZE_QUESTIONS:
                            if column in CATEGORICAL_QUESTIONS:
                                pass
                            else:
                                values = fetch_data(hash, table, column, start, now, filter=True)
                
                                for value in values:
                                    if True: # try:
                                        readings.append((value[0], id, column, float(value[1])))
                                    # except:
                                    
                all_values[id] = readings
                
        for k,v in all_values.iteritems():
            all_values[k].sort(key=lambda x: x[0])
                                    
        print(json.dumps(all_values, indent=2, cls=DjangoJSONEncoder))
def exponate_response(request):
    output = {}
    
    for study in Study.objects.filter(slug='exponate-2013'):
        for id in study.participant_ids(request.user, active=True):
            m = hashlib.md5()
            m.update(id)
            hash = m.hexdigest()

            output[id] = {}
            
            if database_exists(hash):
                output[id]['hash'] = hash
                
                if table_exists(hash, 'EXPONATE'):
                    rows = fetch_data(hash, 'EXPONATE', 'FEATURE_VALUE_DT_name,FEATURE_VALUE_DT_user,FEATURE_VALUE_DT_value')                    
                    output[id]['rows'] = rows
                else:
                    output[id]['error'] = 'No such table "EXPONATE".'
            else:
                output[id]['error'] = 'No such database.'

    return HttpResponse(json.dumps(output, indent=2, cls=DjangoJSONEncoder), content_type="text/plain")
def mobilyze_numeric(request):
    workbook = xlwt.Workbook()
    sheet = workbook.add_sheet('Responses')
    
    sheet.write(0, 0, 'Response ID')
    sheet.write(0, 1, 'Participant ID')
    sheet.write(0, 2, 'Response Key')
    sheet.write(0, 3, 'Response Date')
    sheet.write(0, 4, 'Response Value')
    
    table = 'undefined'
    now = datetime.datetime.utcnow()
    start = datetime.datetime.utcfromtimestamp(0)
    
    row_counter = 1

    for study in Study.objects.filter(slug='mobilyze-2013'):
        for id in study.participant_ids(request.user):
            m = hashlib.md5()
            m.update(id)
    
            hash = m.hexdigest()
            
            if database_exists(hash):
                if table_exists(hash, table):
                    for column in MOBILYZE_QUESTIONS:
                        if column in CATEGORICAL_QUESTIONS:
                            pass
                        else:
                            values = fetch_data(hash, table, column, start, now, filter=True)
                
                            for value in values:
                                sheet.write(row_counter, 0, row_counter)
                                sheet.write(row_counter, 1, id)
                                sheet.write(row_counter, 2, column.replace('FEATURE_VALUE_DT_', ''))
                                sheet.write(row_counter, 3, value[0].isoformat(' '))

                                try:
                                    sheet.write(row_counter, 4, float(value[1]))
                                except:
                                    sheet.write(row_counter, 4, value[1])
                    
                                row_counter += 1
                else:
                    values = fetch_data(hash, 'mobilyze_eav', 'FEATURE_VALUE_DT_name,FEATURE_VALUE_DT_value', start, now, filter=False)
                
                    last_name = None
                
                    for value in values:
                        name = 'FEATURE_VALUE_DT_' + value[1]
                        
                        if name in MOBILYZE_QUESTIONS and (name in CATEGORICAL_QUESTIONS) == False and last_name != name:
                            last_name = name
                            sheet.write(row_counter, 0, row_counter)
                            sheet.write(row_counter, 1, id)
                            sheet.write(row_counter, 2, value[1])
                            sheet.write(row_counter, 3, value[0].isoformat(' '))
                            
                            try:
                                sheet.write(row_counter, 4, float(value[2]))
                            except:
                                sheet.write(row_counter, 4, value[2])
                                
                            row_counter += 1
                    
    io_str = StringIO.StringIO()
    workbook.save(io_str)

    response = HttpResponse(io_str.getvalue(), content_type="application/vnd.ms-excel")
    response['Content-Disposition'] = 'attachment; filename="Mobilyze_Numeric_' + datetime.date.today().strftime('%Y%m%d') + '"'
    
    io_str.close()
    
    return response
def fetch_responses(id):
    m = hashlib.md5()
    m.update(id)
    
    hash = m.hexdigest()

    report = { 'status': 'OK', 'errors': [], 'id': id,  'hash': hash, 'questions': MOBILYZE_QUESTIONS, 'categorical': CATEGORICAL_QUESTIONS }
    
    if database_exists(hash) == False:
        report['status'] = 'Error'
        report['errors'].append('No such database ' + hash + ' for ID ' + id + '.')
    else:
        stats = {}
        group_stats = {}
        
        now = datetime.datetime.utcnow()
        start = datetime.datetime.utcfromtimestamp(0)
        
        response_table = 'undefined'
        
        total_count = 0
        
        std_devs = []

        if table_exists(hash, response_table) == False:
            report['status'] = 'Error'
            report['errors'].append('No such table ' + response_table + ' for ID ' + id + '.')
        else:
            for response_field in MOBILYZE_QUESTIONS:
                try:
                    stats[response_field]
                except KeyError:
                    stats[response_field] = {}
                    
                values = fetch_data(hash, response_table, response_field, start, now, filter=True)

                stats[response_field]['count'] = 0
                
                num_values = []

                for value in values:
                    if value[1] != None and str(value[1]).strip() != '':
                        if response_field in CATEGORICAL_QUESTIONS:
                            pass
                        else:
                            num_values.append(float(value[1]))

                        stats[response_field]['count'] += 1
                        
                if len(num_values) > 0:
                    stats[response_field]['min'] = numpy.amin(num_values)
                    stats[response_field]['max'] = numpy.amax(num_values)
                    stats[response_field]['mean'] = numpy.mean(num_values)
                    stats[response_field]['stddev'] = numpy.std(num_values)
                    
                    std_devs.append(stats[response_field]['stddev'])
        
        report['statistics'] = stats
        report['mean_std_dev'] = numpy.mean(std_devs)
        
    return report
def fetch_completion(id):
    m = hashlib.md5()
    m.update(id)
    
    hash = m.hexdigest()

    report = { 'status': 'OK', 'errors': [], 'id': id,  'hash': hash }
    
    if database_exists(hash) == False:
        report['status'] = 'Error'
        report['errors'].append('No such database ' + hash + ' for ID ' + id + '.')
    else:
        now = datetime.datetime.utcnow()
        start = datetime.datetime.utcfromtimestamp(0)
        
        response_table = 'undefined'
        
        responses = {}
        
        total_count = 0

        if table_exists(hash, response_table) == False:
            if table_exists(hash, 'mobilyze_eav') == False:
                report['status'] = 'Error'
                report['errors'].append('No such table ' + 'mobilyze_eav' + ' for ID ' + id + '.')
            else:
                last_session_date = datetime.datetime.min

                session_delta = datetime.timedelta(seconds=600)
                
                session_count = 0

                values = fetch_data(hash, 'mobilyze_eav', 'FEATURE_VALUE_DT_name', start, now)

                for value in values:
                    if value[1] != None:
                        if (value[0] - last_session_date) > session_delta:
                            session_count += 1
                            last_session_date = value[0]

#                values = fetch_data(hash, 'mobilyze_eav', 'FEATURE_VALUE_DT_name', start, now)
                
                last_value = ''
                last_name = ''

                values = fetch_data(hash, 'mobilyze_eav', 'FEATURE_VALUE_DT_name', start, now)

                for value in values:
                    if value[1] != None and value[1] != last_value and value[1] != '':
                        last_value = value[1]
                        
                        total_count += 1
                
                responses['DISTINCT_TIMES'] = session_count
        else:
            for response_field in MOBILYZE_QUESTIONS:
                values = fetch_data(hash, response_table, response_field, start, now)
                
                response_count = 0
                
                for value in values:
                    if value[1] != None:
                        response_count += 1
                
                total_count += response_count
                        
                responses[response_field] = response_count

                values = fetch_data(hash, response_table, 'eventDateTime', start, now, distinct=True)
                
                values.sort(key=lambda x: x[1])
                values.reverse()
                
                session_delta = datetime.timedelta(seconds=600)
                session_count = 0
                
                last_submit = datetime.datetime.max
                
                for value in values:
                    if (last_submit - value[1]) > session_delta:
                        session_count += 1
                        
                    last_submit = value[1]

                responses['DISTINCT_TIMES'] = session_count


        responses['TOTAL'] = total_count
        
        report['responses'] = responses
        
    return report
def fetch_status(id):
    m = hashlib.md5()
    m.update(id)
    
    hash = m.hexdigest()

    report = { 'status': 'OK', 'errors': [], 'id': id,  'hash': hash }
    
    if database_exists(hash) == False:
        report['status'] = 'Error'
        report['errors'].append('No such database ' + hash + ' for ID ' + id + '.')
    else:
        now = datetime.datetime.utcnow()
        start = now - datetime.timedelta(0, 3600 * 6)
        
        sensor_table = 'RobotHealthProbe'
        sensor_field = 'ACTIVE_RUNTIME'
        
        if table_exists(hash, sensor_table) == False:
            report['status'] = 'Error'
            report['errors'].append('No such table ' + sensor_table + ' for ID ' + id + '.')
        else:
            values = fetch_data(hash, sensor_table, sensor_field, start, now)
    
            if len(values) < 1:
                report['status'] = 'Error'
                report['errors'].append('No recent data from sensor ' + sensor_table + '.' + sensor_field + '.')
                
        values = fetch_data(hash, sensor_table, sensor_field, datetime.datetime.min, now, limit=5)
        
        try:
            report['last_sensor'] = values[-1][0]
        except:
            report['last_sensor'] = None
            
        if report['last_sensor'] != None:
            report['last_sensor'] = pytz.utc.localize(report['last_sensor'])
            report['last_sensor'] = report['last_sensor'].astimezone(timezone('US/Central'))

        response_table = 'undefined'
        response_field = 'GUID'
        
        if table_exists(hash, response_table) == False:
            if table_exists(hash, 'mobilyze_eav') == False:
                report['status'] = 'Error'
                report['errors'].append('No such table ' + 'mobilyze_eav' + ' for ID ' + id + '.')
            else:
                values = fetch_data(hash, 'mobilyze_eav', response_field, start, now)
        
                if len(values) < 1:
                    report['status'] = 'Error'
                    report['errors'].append('No recent responses from PRO.')

        else:
            values = fetch_data(hash, response_table, response_field, start, now)
    
            if len(values) < 1:
                report['status'] = 'Error'
                report['errors'].append('No recent responses from PRO.')

        values = fetch_data(hash, response_table, response_field, datetime.datetime.min, now, limit=5)

        try:
            report['last_response'] = values[-1][0]
        except:
            values = fetch_data(hash, 'mobilyze_eav', response_field, datetime.datetime.min, now, limit=5)
    
            try:
                report['last_response'] = values[-1][0]
            except:
                report['last_response'] = None

        if report['last_response'] != None:
            report['last_response'] = pytz.utc.localize(report['last_response'])
            report['last_response'] = report['last_response'].astimezone(timezone('US/Central'))

    return report